using PoJun.Dapper;
using ASHermed.SinoInterface.Api.IRepository.Test;
using ASHermed.SinoInterface.Entity.Test;
using ASHermed.SinoInterface.Repository.MySql;
using ASHermed.SinoInterface.Tools;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ASHermed.SinoInterface.Api.IRepository;
using ASHermed.SinoInterface.Api.ContractModel.Coupon.Model;
using ASHermed.SinoInterface.Api.ContractModel.App;

namespace ASHermed.SinoInterface.Api.MySqlRepository
{

    public class DicCouponRepository : BaseRepositoryToASHermed_SinoInterface<Dic_Coupon>, IDicCouponRepository
    {
        public List<Dic_Coupon> SelectInId(string ids)
        {
            var couponIds = ids.Split(',');

            if (!couponIds.Any())
            {
                return null;
            }

            var parms = new Dictionary<string, object>
            {
                {"@couponId", couponIds}
            };
            var selectSql = @"select ID, COUPON_TYPE, CN_COUPON_NAME, EN_COUPON_NAME, CN_IMAGE, EN_IMAGE, PRICE,
               PERIOD_OF_VALIDITY,PERIOD_VALIDITY_UNIT,END_VALIDITY_DATE, TOTAL_COUNT, PER_COUNT,
               SINGLE_PERSON_LIMITED_UNIT,TIMES_LIMIT,EVERY_USING,CN_TEXT_ST,EN_TEXT_ST,CN_TEXT_ND,EN_TEXT_ND,
               STATUS, CREATE_USER,CREATE_TIME,
               UPDATE_TIME,REMARK,CN_DESCRIPTION,EN_DESCRIPTION
                from dic_coupon
                where ID in @couponId";
            var result = Query<Dic_Coupon>(selectSql, parms);
            return result.ToList();
        }

        public Dic_Coupon SelectByPrimaryKey(int id)
        {
            var parms = new Dictionary<string, object>();
            parms.Add("@id", id);
            var selectSql = @"select ID, COUPON_TYPE, CN_COUPON_NAME, EN_COUPON_NAME, CN_IMAGE, EN_IMAGE, PRICE,
                                PERIOD_OF_VALIDITY,PERIOD_VALIDITY_UNIT,END_VALIDITY_DATE, TOTAL_COUNT, PER_COUNT,
                                SINGLE_PERSON_LIMITED_UNIT,TIMES_LIMIT,EVERY_USING,CN_TEXT_ST,EN_TEXT_ST,CN_TEXT_ND,EN_TEXT_ND,
                                STATUS, CREATE_USER,CREATE_TIME,
                                UPDATE_TIME,REMARK,CN_DESCRIPTION,EN_DESCRIPTION
                                from dic_coupon
                                where ID = @id";
            var result = Query<Dic_Coupon>(selectSql, parms);
            return result.FirstOrDefault();
        }

        public List<ReceivedCoupon> SelectAllReceivedCoupon()
        {
            var parms = new Dictionary<string, object>();
            var selectSql = @"select a.ID,
               a.CREATE_TIME as CreateTime,
               a.VALIDITY_START_DATE as ValidityStartDate,
               a.RECEIPTOR_PHONE as ReceiptorPhone,
               d.NICK_NAME as ReceiptorName,
               a.REDEEM_CODE as RedeemCode,
               a.TIMES,
               a.`STATUS`,
               a.VALIDITY_END_DATE as ValidityEndDate,
               b.COUPON_TYPE as CouponType,
               b.CN_COUPON_NAME as CnCouponName,
               b.EN_COUPON_NAME as EnCouponName,
               b.PRICE,
               b.PERIOD_OF_VALIDITY as PeriodOfValidity,
               b.CN_IMAGE as CnImage,
               b.EN_IMAGE as EnImage,
               a.SHARING_PHONE as SharingPhone,
               c.NICK_NAME  as SharingUserName,
               b.ID   AS CouponId,
               a.VALIDITY_END_DATE < DATE_FORMAT(NOW(), '%Y-%m-%d') as IsExpired,
               b.CN_TEXT_ST as CnTextSt,
               b.EN_TEXT_ST as EnTextSt,
               b.CN_TEXT_ND as CnTextNd,
               b.EN_TEXT_ND as EnTextNd
        FROM pub_coupon_flow a
                 INNER JOIN dic_coupon b ON a.COUPON_ID = b.ID
            AND b.`STATUS` != 1
                 LEFT JOIN pub_user_info c ON c.PHONE = a.SHARING_PHONE
            AND c.IS_DELETED =
                FALSE
                 INNER JOIN pub_user_info d ON d.PHONE = a.RECEIPTOR_PHONE
            AND d.IS_DELETED =
                FALSE
                 JOIN (SELECT max(id) AS id FROM pub_coupon_flow WHERE `STATUS` != 1 GROUP BY REDEEM_CODE) e
                      ON a.id = e.id
        where a.`STATUS` != 1
        order by a.CREATE_TIME desc";
            var result = Query<ReceivedCoupon>(selectSql, null);
            return result.ToList();
        }

        public List<ReceivedCoupon> SelectReceivedCouponByPhone(string phone, int fID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" select a.ID, a.CREATE_TIME as CreateTime, a.VALIDITY_START_DATE as ValidityStartDate, a.RECEIPTOR_PHONE as ReceiptorPhone, d.NICK_NAME ReceiptorName,
                                a.REDEEM_CODE as RedeemCode,a.TIMES,a.`STATUS`,
                                a.VALIDITY_END_DATE as ValidityEndDate, b.COUPON_TYPE as CouponType, b.CN_COUPON_NAME as CNCouponName, b.EN_COUPON_NAME as ENCouponName, b.PRICE, b.PERIOD_OF_VALIDITY as PeriodOfValidity,
                                b.CN_IMAGE as CNImage, b.EN_IMAGE as ENImage, a.SHARING_PHONE as SharingPhone, c.NICK_NAME SharingUserName, b.ID AS CouponId,
                                a.VALIDITY_END_DATE < DATE_FORMAT(NOW(),'%Y-%m-%d') as IsExpired,
                                b.CN_TEXT_ST as CNTextSt,
                                b.EN_TEXT_ST as ENTextSt,
                                b.CN_TEXT_ND as CNTextNd,
                                b.EN_TEXT_ND as ENTextNd,
                                b.CN_DESCRIPTION as CNDescription,
                                b.EN_DESCRIPTION as ENDescription
                                FROM
                                pub_coupon_flow a
                                INNER JOIN dic_coupon b ON a.COUPON_ID = b.ID
                                AND b.`STATUS` != 1
                                LEFT JOIN pub_user_info c ON c.PHONE = a.SHARING_PHONE
                                AND c.IS_DELETED =
                                FALSE INNER JOIN pub_user_info d ON d.PHONE = a.RECEIPTOR_PHONE
                                AND d.IS_DELETED =
                                FALSE JOIN ( SELECT max( id ) AS id FROM pub_coupon_flow WHERE `STATUS` != 1 GROUP BY REDEEM_CODE ) e ON a.id =
                                e.id
                                where a.`STATUS` != 1
                                and a.RECEIPTOR_PHONE = @phone ");

            if (fID > 0)
            {
                sql.Append(" and a.id = @id");
            }
            return Query<ReceivedCoupon>(sql.ToString(), new { phone = phone, id = fID }).ToList();
        }
    }


}
