using System;
using System.Collections.Generic;
using System.Text;
using Bailun.DC.Models;
using Bailun.DC.DB;
using Dapper;
using MySql.Data.MySqlClient;

namespace Bailun.DC.Services
{
    public class PurchaseServices
    {
        /// <summary>
        /// 采购单列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="sku">sku编码</param>
        /// <param name="no">采购单号</param>
        /// <param name="status">采购单状态</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Purchase.mPurchase> ListPurchase(BtTableParameter parameter, string warehouse, string sku, string no, int? status, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select t1.purchase_id,t1.`buy_status` ,t1.bailun_sku,t2.sku_image,t2.category_name,t2.sku_title_en,t2.sku_title_cn,t1.price,t1.count,(t1.price*t1.count) amount,t2.currency,t1.has_transfer,t1.supplier_name,t2.buyer_name,t1.warehouse_name as warehouse_name_from,t1.warehouse_name warehouse_name_to,t1.create_time,t1.update_time,t1.gmt_modified 
                                from dc_base_purchase t1
                                left  join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                                where t1.has_delete = 0";

            if (!string.IsNullOrEmpty(warehouse))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (!string.IsNullOrWhiteSpace(no))
            {
                sql += " and t1.purchase_id=@purchase_id";
                sqlparam.Add("purchase_id", no);
            }

            if (status.HasValue)
            {
                sql += " and t1.status=" + status.Value;
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sql += " order by " + parameter.sort;

                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    sql += (" " + parameter.order);
                }
            }
            else
            {
                sql += " order by create_time desc";
            }

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<Models.Purchase.mPurchase>(parameter.pageIndex, parameter.limit, sql, ref total,sqlparam);

                return obj.AsList();
            }

        }

        public Models.Purchase.mPurchase ListPurchaseCount(string warehouse, string sku, string no, int? status)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select sum(t1.count) count,sum(t1.price*t1.count) amount 
                                from dc_base_purchase t1
                                left  join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                                where t1.has_delete = 0";

            if (!string.IsNullOrEmpty(warehouse))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (!string.IsNullOrWhiteSpace(no))
            {
                sql += " and t1.purchase_id=@purchase_id";
                sqlparam.Add("purchase_id", no);
            }

            if (status.HasValue)
            {
                sql += " and t1.status=" + status.Value;
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirst<Models.Purchase.mPurchase>(sql,sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 根据时间段获取采购单明细
        /// </summary>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="suppliername">供应商名称</param>
        /// <returns></returns>
        public List<Models.dc_base_purchase_details> ListPurchaseDetail(int page,int pagesize,DateTime start,DateTime end,string suppliername)
        {
            var sql = $@"select * from dc_base_purchase_details where gmt_modified>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and gmt_modified<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
            if (!string.IsNullOrWhiteSpace(suppliername))
            {
                sql += $" and deliver_name='{suppliername}'";
            }

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_base_purchase_details>(sql + " limit " + ((page - 1) * pagesize) + "," + pagesize, null, null, true, 2 * 60).AsList();

                return obj;
            }
        }

        /// <summary>
        /// 获取采购单的明细
        /// </summary>
        /// <param name="start">创建开始时间</param>
        /// <param name="end">创建结束时间</param>
        /// <returns></returns>
        public List<dc_base_purchase> ListPurchase(DateTime start, DateTime end)
        {
            var sql = $@"select t1.*,category_simple_name from dc_base_purchase t1 
                        join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        where t1.has_delete=0 and t1.status!=-1 and t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                return cn.Query<dc_base_purchase>(sql).AsList();

            }
        }

        /// <summary>
        /// 获取半成品采购单的明细
        /// </summary>
        /// <param name="start">创建开始时间</param>
        /// <param name="end">创建结束时间</param>
        /// <returns></returns>
        public List<dc_semi_purchase_info> ListSemiPurchase(DateTime start, DateTime end)
        {
            var sql = $@"select * from dc_semi_purchase_info t1 where detail_delstatus=0 and  `status`>-1 and deliver_name !='广州哈倪蔓生物科技有限公司' and deliver_name!='广州市花都区花城市象贸易商行-滋柔' and deliver_name!='LEIKESAER INFORMATION SERVICE LIMITED' and purchase_id like 'P%' and t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                return cn.Query<dc_semi_purchase_info>(sql).AsList();

            }
        }

    }
}
