using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Dapper;
using Bailun.DC.Models;
using System.Linq;
using Microsoft.Extensions.Hosting;
using System.Threading;
using System.Threading.Tasks;

namespace Bailun.DC.SkuDailyPurchaseAndSales
{
    public class Services : BackgroundService
    {
        private Timer _timer;

        protected override Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _timer = new Timer(DoWork, null, TimeSpan.Zero, TimeSpan.FromMinutes(1));
            return Task.CompletedTask;
        }

        private void DoWork(object state)
        {
            try
            {
                var now = DateTime.Now;
                if (now.Hour == 5 && now.Minute == 16)
                {
                    Console.WriteLine("开始计算服务 " + now.ToString("yyyy-MM-dd HH:mm:ss"));
                    Init(DateTime.Parse(now.AddDays(-1).ToShortDateString()), DateTime.Parse(now.ToShortDateString()));
                    Console.WriteLine("计算结束 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else if (now.Hour % 8 == 0 && now.Minute == 3)
                {
                    var start = DateTime.Parse(now.AddMonths(-1).ToString("yyyy-MM-dd"));
                    while (start.AddDays(1) < DateTime.Now)
                    {
                        Console.WriteLine("重跑历史数据 "+start.ToString("yyyy-MM-dd"));
                        new Services().Init(start, start.AddDays(1));
                        start = start.AddDays(1);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public void Init(DateTime start,DateTime end)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    #region 成品
                    //成品

                    //先保存采购的
                    var sql = $@"select t1.warehouse_code,t3.warehouse_name,t1.bailun_sku,sum(t1.count) count,sum(t1.count*t1.price) amount from dc_base_purchase t1 
                        left join dc_base_warehouse t3 on t1.warehouse_code = t3.warehouse_code
                        where t1.has_delete=0 and t1.status!=-1 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
                        group by t1.warehouse_code,t1.bailun_sku";

                    var objPurchase = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    //再保存卖出的
                    sql = $@"select t1.warehouse_code,t3.warehouse_name,t1.bailun_sku,sum(t1.bailun_sku_quantity_ordered) count,sum(t1.bailun_sku_quantity_ordered*t2.unit_price) amount from dc_base_oms_sku t1
                        left join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        left join dc_base_warehouse t3 on t1.warehouse_code = t3.warehouse_code
                        where t1.has_delete=0 and t1.has_scalp=0 and t1.bailun_payment_status!='Canceled' and t1.paid_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.paid_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.bailun_order_status!='Canceled' and t1.warehouse_code!=''
                        group by t1.warehouse_code,t1.bailun_sku";

                    var objSales = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    //获取入库的
                    sql = $@"select t1.*,t2.warehouse_name from (select tb.warehouse_code,tb.bailun_sku,sum(tb.count) count,sum(tb.count*t4.unit_price) amount from (
                            select t1.warehouse_code,t1.warehouse_name,t1.bailun_sku,t1.count from dc_base_purchase_inbound t1
                            join dc_base_purchase t2 on t1.purchase_id=t2.purchase_id and t1.bailun_sku=t2.bailun_sku and t2.has_transfer=0 and t2.`status`>0
                            where t1.`status`=1 and t1.update_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.update_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
                            union all
                            select warehouse_code,warehouse_name,bailun_sku,count from dc_base_transfer_inbound t1 where t1.deleted=0 and t1.count>0 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
                            ) tb
                            left join dc_base_sku t4 on tb.bailun_sku=t4.bailun_sku
                            group by tb.warehouse_code,tb.bailun_sku) t1
                            left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code";
                    var objPutin = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    //发货的  自发货+FBA的
                    sql = $@"select tb.warehouse_code,tb.warehouse_name,tb.bailun_sku,sum(count) count,sum(amount) amount from (select t1.warehouse_code,t3.warehouse_name,t1.bailun_sku,sum(t1.quantity_shipped) count,sum(t1.quantity_shipped*t2.unit_price) amount from dc_base_oms_pick t1
                        left join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        left join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                        where t1.company_id=1 and t1.has_delete=0 and t1.shipping_status='TotalShipping' and t1.shipping_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
                        group by t1.warehouse_code,t1.bailun_sku
                        union all
                        select t1.warehouse_code,t3.warehouse_name,t1.bailun_sku,sum(t1.bailun_sku_quantity_ordered) count,sum(t1.bailun_sku_quantity_ordered*t2.unit_price) amount from dc_base_oms_sku t1 
                        left join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        left join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                        where t1.company_id=1 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.bailun_order_status!='Canceled' and t1.has_scalp=0 and t1.has_delete=0 and t1.has_cancle=0 and platform_type='FBA'
                        group by t1.warehouse_code,t1.bailun_sku) tb
                        group by tb.bailun_sku,tb.warehouse_code";
                    var objShipping = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    //调拨出库
                    sql = $@"select t1.source_warehouse_code as warehouse_code,t3.warehouse_name,t1.bailun_sku,t1.count,t1.count*t2.unit_price from dc_base_transfer_complete t1
                        left join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        left join dc_base_warehouse t3 on t1.source_warehouse_code=t3.warehouse_code
                        where t1.create_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time < '{end.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.deleted = 0
                        group by t1.bailun_sku,t1.source_warehouse_code";
                    var objAllot = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    objShipping.AddRange(objAllot);

                    var listSkuWarehouse = objPurchase.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList();

                    listSkuWarehouse.AddRange(objSales.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList());

                    listSkuWarehouse.AddRange(objPutin.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList());

                    listSkuWarehouse.AddRange(objShipping.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList());


                    listSkuWarehouse = listSkuWarehouse.Distinct().ToList();

                    var tempList = new List<SkuWarehouse>();
                    foreach (var item in listSkuWarehouse)
                    {
                        if (tempList.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).Count() <= 0)
                        {
                            tempList.Add(new SkuWarehouse
                            {
                                bailun_sku = item.bailun_sku,
                                warehouse_code = item.warehouse_code,
                                warehouse_name = item.warehouse_name
                            });
                        }
                    }

                    var list = new List<dc_daily_purchase_sales>();

                    foreach (var item in tempList)
                    {
                        sql = $@"select t1.warehouse_code,t2.warehouse_name,t1.bailun_sku,t1.usable_stock,(t1.usable_stock*t3.unit_price) as stockAmount from dc_base_stock t1 left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code left join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku where t1.bailun_sku='{item.bailun_sku}' and t1.warehouse_code='{item.warehouse_code}'";
                        var objStock = cn.QueryFirstOrDefault<Models.mStock>(sql, null, null, 5 * 60);


                        sql = $@"select t1.warehouse_code,t1.bailun_sku,t1.usable_stock,(t1.usable_stock*t1.unit_price) as stockAmount from dc_daily_stock t1 where t1.record_time='{start.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.bailun_sku='{item.bailun_sku}' and t1.warehouse_code='{item.warehouse_code}'";
                        var objPreStock = cn.QueryFirstOrDefault<Models.mStock>(sql, null, null, 5 * 60);

                        var m = new dc_daily_purchase_sales
                        {
                            day = DateTime.Parse(start.ToShortDateString()),
                            createtime = DateTime.Now,
                            bailun_sku = item.bailun_sku,
                            warehouse_code = item.warehouse_code,
                            warehouse_name = item.warehouse_name,

                            amount_purchase = 0,
                            amount_putin = 0,
                            amount_sales = 0,
                            count_purchase = 0,
                            count_putin = 0,
                            count_sales = 0,
                            difference_amount = 0,
                            difference_count = 0,
                            diff_putin_amount = 0,
                            diff_putin_count = 0,
                            stock_start = 0,
                            stockamount_start = 0,
                            isfinish = 1,
                        };

                        //期末
                        //var objEnd = objStock.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objStock != null)
                        {
                            m.stock_end = objStock.usable_stock;
                            m.stockamount_end = objStock.stockAmount;
                        }

                        //期初
                        //var objPre = objPreStock.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objPreStock != null)
                        {
                            m.stock_start = objPreStock.usable_stock;
                            m.stockamount_start = objPreStock.stockAmount;
                        }

                        //采购下单数
                        var objP = objPurchase.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objP != null)
                        {
                            m.count_purchase = objP.count;
                            m.amount_purchase = objP.amount;
                        }

                        //卖出数
                        var objS = objSales.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objS != null)
                        {
                            m.count_sales = objS.count;
                            m.amount_sales = objS.amount;
                        }

                        //入库数
                        var objPI = objPutin.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objPI != null)
                        {
                            m.count_putin = objPI.count;
                            m.amount_putin = objPI.amount;
                        }

                        //发货数
                        var objShip = objShipping.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objShip != null)
                        {
                            m.count_shipping = objShip.count;
                            m.amount_shipping = objShip.amount;
                        }

                        m.difference_count = m.count_purchase - m.count_sales;
                        m.difference_amount = m.amount_purchase - m.amount_sales;
                        m.diff_putin_count = m.count_putin - m.count_shipping;
                        m.diff_putin_amount = m.amount_putin - m.amount_shipping;

                        list.Add(m);
                    }

                    #endregion

                    #region 原料

                    //采购
                    sql = $@"select sum(quantity_purchase) as count,sum(quantity_purchase*unit_price) as amount,bailun_sku,warehouse_from_code as warehouse_code,warehouse_from_name as warehouse_name from dc_semi_purchase_info where detail_delstatus=0 and create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and `status`>-1 and deliver_name !='广州哈倪蔓生物科技有限公司' and deliver_name!='广州市花都区花城市象贸易商行-滋柔' and deliver_name!='LEIKESAER INFORMATION SERVICE LIMITED'
                        group by bailun_sku,warehouse_from_code";
                    var objSemiPurchase = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();


                    //卖出
                    sql = $@"select sum(sku_quantity) as count,sum(sku_quantity*sku_unit_price) as amount,sku as bailun_sku,warehouse_id as warehouse_code,warehouse_name from dc_semi_order where state!=1 and create_date>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_date<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' group by sku,warehouse_id";
                    var objSemiSales = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    //入库
                    sql = $@"select t1.sku_code as bailun_sku,t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,t1.log_quantity as count,(t1.log_quantity*t2.price) amount from dc_semi_stock_log t1
                        left join (select t1.sku_code,t1.price from dc_semi_supplier_offer_log t1
                        inner join (select sku_code,max(update_time) update_time from dc_semi_supplier_offer_log where  sku_code in (select sku_code from dc_semi_stock_log where update_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and update_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and log_type_id=2) group by sku_code,update_time) t2 on t1.sku_code=t2.sku_code and t1.update_time=t2.update_time) t2 on t1.sku_code=t2.sku_code
                        where t1.update_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.update_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and log_type_id=2";
                    var objSemiPutin = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                    //出库 =(生产消耗-取消生产)
                    sql = $@"select t1.material_code as bailun_sku,t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,sum(t1.stock) as count,sum(t1.stock*t1.new_price) amount from dc_semi_stock_report t1 
                        where t1.is_zichang=0 and t1.stock_type=3 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' group by t1.material_code,t1.warehouse_id;";
                    var objSemiProduction = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();  //生产消耗

                    sql = $@"select t1.material_code as bailun_sku,t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,sum(t1.stock) as count,sum(t1.stock*t1.new_price) amount from dc_semi_stock_report t1 
                        where t1.is_zichang=0 and t1.stock_type=5 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' group by t1.material_code,t1.warehouse_id;";
                    var objSemiCancelProduction = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();  //取消生产

                    //生产消耗-取消生产
                    foreach (var item in objSemiProduction)
                    {
                        var temp = objSemiCancelProduction.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (temp != null)
                        {
                            item.count -= temp.count;
                            item.amount -= temp.amount;
                        }
                    }

                    listSkuWarehouse = objSemiPurchase.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList();

                    listSkuWarehouse.AddRange(objSemiSales.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }));

                    listSkuWarehouse.AddRange(objSemiPutin.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList());

                    listSkuWarehouse.AddRange(objSemiProduction.Select(a => new
                    {
                        a.bailun_sku,
                        a.warehouse_code,
                        a.warehouse_name
                    }).ToList());

                    listSkuWarehouse = listSkuWarehouse.Distinct().ToList();

                    tempList = new List<SkuWarehouse>();
                    foreach (var item in listSkuWarehouse)
                    {
                        if (tempList.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).Count() <= 0)
                        {
                            tempList.Add(new SkuWarehouse
                            {
                                bailun_sku = item.bailun_sku,
                                warehouse_code = item.warehouse_code,
                                warehouse_name = item.warehouse_name
                            });
                        }
                    }

                    var listSemi = new List<dc_daily_purchase_sales>();
                    foreach (var item in tempList)
                    {
                        sql = $@"select t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,t1.sku_code as bailun_sku,t1.final_stock as usable_stock,(t1.final_stock*t2.price) as stockAmount from dc_semi_stock_log t1 join dc_semi_supplier_offer_log t2 on t1.sku_code=t2.sku_code where t1.sku_code='{item.bailun_sku}' and t1.warehouse_id='{item.warehouse_code}' order by t2.update_time desc";
                        var objStock = cn.QueryFirstOrDefault<Models.mStock>(sql, null, null, 5 * 60);


                        sql = $@"select t1.warehouse_id warehouse_code,t1.warehouse_name,t1.sku_code bailun_sku,t1.stock_quantity usable_stock,(t1.stock_quantity*t1.price) as stockAmount from dc_semi_daily_stock t1 where t1.day='{start.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.sku_code='{item.bailun_sku}' and t1.warehouse_id='{item.warehouse_code}'";
                        var objPreStock = cn.QueryFirstOrDefault<Models.mStock>(sql, null, null, 5 * 60);

                        var m = new dc_daily_purchase_sales
                        {
                            day = DateTime.Parse(start.ToShortDateString()),
                            createtime = DateTime.Now,
                            bailun_sku = item.bailun_sku,
                            warehouse_code = item.warehouse_code,
                            warehouse_name = item.warehouse_name,

                            amount_purchase = 0,
                            amount_putin = 0,
                            amount_sales = 0,
                            count_purchase = 0,
                            count_putin = 0,
                            count_sales = 0,
                            difference_amount = 0,
                            difference_count = 0,
                            diff_putin_amount = 0,
                            diff_putin_count = 0,
                            stock_start = 0,
                            stockamount_start = 0,
                            isfinish = 0,
                        };

                        //期末
                        //var objEnd = objStock.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objStock != null)
                        {
                            m.stock_end = objStock.usable_stock;
                            m.stockamount_end = objStock.stockAmount;
                        }

                        //期初
                        //var objPre = objPreStock.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objPreStock != null)
                        {
                            m.stock_start = objPreStock.usable_stock;
                            m.stockamount_start = objPreStock.stockAmount;
                        }

                        //采购下单数
                        var objP = objSemiPurchase.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objP != null)
                        {
                            m.count_purchase = objP.count;
                            m.amount_purchase = objP.amount;
                        }

                        //卖出数
                        var objS = objSemiSales.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objS != null)
                        {
                            m.count_sales = objS.count;
                            m.amount_sales = objS.amount;
                        }

                        //入库数
                        var objPI = objSemiPutin.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objPI != null)
                        {
                            m.count_putin = objPI.count;
                            m.amount_putin = objPI.amount;
                        }

                        //发货数
                        var objShip = objSemiProduction.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (objShip != null)
                        {
                            m.count_shipping = objShip.count;
                            m.amount_shipping = objShip.amount;
                        }

                        m.difference_count = m.count_purchase - m.count_sales;
                        m.difference_amount = m.amount_purchase - m.amount_sales;
                        m.diff_putin_count = m.count_putin - m.count_shipping;
                        m.diff_putin_amount = m.amount_putin - m.amount_shipping;

                        list.Add(m);
                    }

                    #endregion

                    if (list.Count > 0)
                    {
                        Save(cn, list, DateTime.Parse(start.ToString("yyyy-MM-dd")));
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write("出现异常：" + ex.Message);
            }

        }

        public List<dc_daily_purchase_sales> ListSemi(DateTime start, DateTime end)
        {
            var sql = "";

            var list = new List<dc_daily_purchase_sales>();

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

                #region 原料

                //采购
                sql = $@"select sum(quantity_purchase) as count,sum(quantity_purchase*unit_price) as amount,bailun_sku,warehouse_from_code as warehouse_code,warehouse_from_name as warehouse_name from dc_semi_purchase_info where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and `status`>-1
                        group by bailun_sku,warehouse_from_code";
                var objSemiPurchase = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();


                //卖出
                sql = $@"select sum(sku_quantity) as count,sum(sku_quantity*sku_unit_price) as amount,sku as bailun_sku,warehouse_id as warehouse_code,warehouse_name from dc_semi_order where state!=1 and create_date>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_date<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' group by sku,warehouse_id";
                var objSemiSales = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                //入库
                sql = $@"select t1.sku_code as bailun_sku,t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,t1.log_quantity as count,(t1.log_quantity*t2.price) amount from dc_semi_stock_log t1
                        left join (select t1.sku_code,t1.price from dc_semi_supplier_offer_log t1
                        inner join (select sku_code,max(update_time) update_time from dc_semi_supplier_offer_log where  sku_code in (select sku_code from dc_semi_stock_log where update_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and update_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and log_type_id=2) group by sku_code,update_time) t2 on t1.sku_code=t2.sku_code and t1.update_time=t2.update_time) t2 on t1.sku_code=t2.sku_code
                        where t1.update_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.update_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and log_type_id=2";
                var objSemiPutin = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();

                //出库 =(生产消耗-取消生产)
                sql = $@"select t1.material_code as bailun_sku,t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,sum(t1.stock) as count,sum(t1.stock*t1.new_price) amount from dc_semi_stock_report t1 
                        where t1.stock_type=3 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' group by t1.material_code,t1.warehouse_id;";
                var objSemiProduction = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();  //生产消耗

                sql = $@"select t1.material_code as bailun_sku,t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,sum(t1.stock) as count,sum(t1.stock*t1.new_price) amount from dc_semi_stock_report t1 
                        where t1.stock_type=5 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' group by t1.material_code,t1.warehouse_id;";
                var objSemiCancelProduction = cn.Query<Models.mSkuCount>(sql, null, null, true, 2 * 60).AsList();  //取消生产

                //生产消耗-取消生产
                foreach (var item in objSemiProduction)
                {
                    var temp = objSemiCancelProduction.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (temp != null)
                    {
                        item.count -= temp.count;
                        item.amount -= temp.amount;
                    }
                }

                var listSkuWarehouse = objSemiPurchase.Select(a => new
                {
                    a.bailun_sku,
                    a.warehouse_code,
                    a.warehouse_name
                }).ToList();

                listSkuWarehouse.AddRange(objSemiSales.Select(a => new {
                    a.bailun_sku,
                    a.warehouse_code,
                    a.warehouse_name
                }));

                listSkuWarehouse.AddRange(objSemiPutin.Select(a => new
                {
                    a.bailun_sku,
                    a.warehouse_code,
                    a.warehouse_name
                }).ToList());

                listSkuWarehouse.AddRange(objSemiProduction.Select(a => new
                {
                    a.bailun_sku,
                    a.warehouse_code,
                    a.warehouse_name
                }).ToList());

                listSkuWarehouse = listSkuWarehouse.Distinct().ToList();

                var tempList = new List<SkuWarehouse>();
                foreach (var item in listSkuWarehouse)
                {
                    if (tempList.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).Count() <= 0)
                    {
                        tempList.Add(new SkuWarehouse
                        {
                            bailun_sku = item.bailun_sku,
                            warehouse_code = item.warehouse_code,
                            warehouse_name = item.warehouse_name
                        });
                    }
                }

                var listSemi = new List<dc_daily_purchase_sales>();
                foreach (var item in tempList)
                {
                    sql = $@"select t1.warehouse_id as warehouse_code,t1.warehouse_name as warehouse_name,t1.sku_code as bailun_sku,t1.final_stock as usable_stock,(t1.final_stock*t2.price) as stockAmount from dc_semi_stock_log t1 join dc_semi_supplier_offer_log t2 on t1.sku_code=t2.sku_code where t1.sku_code='{item.bailun_sku}' and t1.warehouse_id='{item.warehouse_code}' order by t2.update_time desc";
                    var objStock = cn.QueryFirstOrDefault<Models.mStock>(sql, null, null, 5 * 60);


                    sql = $@"select t1.warehouse_id warehouse_code,t1.warehouse_name,t1.sku_code bailun_sku,t1.stock_quantity usable_stock,(t1.stock_quantity*t1.price) as stockAmount from dc_semi_daily_stock t1 where t1.day='{start.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.sku_code='{item.bailun_sku}' and t1.warehouse_id='{item.warehouse_code}'";
                    var objPreStock = cn.QueryFirstOrDefault<Models.mStock>(sql, null, null, 5 * 60);

                    var m = new dc_daily_purchase_sales
                    {
                        day = DateTime.Parse(start.ToShortDateString()),
                        createtime = DateTime.Now,
                        bailun_sku = item.bailun_sku,
                        warehouse_code = item.warehouse_code,
                        warehouse_name = item.warehouse_name,

                        amount_purchase = 0,
                        amount_putin = 0,
                        amount_sales = 0,
                        count_purchase = 0,
                        count_putin = 0,
                        count_sales = 0,
                        difference_amount = 0,
                        difference_count = 0,
                        diff_putin_amount = 0,
                        diff_putin_count = 0,
                        stock_start = 0,
                        stockamount_start = 0,
                        isfinish = 0,
                    };

                    //期末
                    //var objEnd = objStock.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (objStock != null)
                    {
                        m.stock_end = objStock.usable_stock;
                        m.stockamount_end = objStock.stockAmount;
                    }

                    //期初
                    //var objPre = objPreStock.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (objPreStock != null)
                    {
                        m.stock_start = objPreStock.usable_stock;
                        m.stockamount_start = objPreStock.stockAmount;
                    }

                    //采购下单数
                    var objP = objSemiPurchase.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (objP != null)
                    {
                        m.count_purchase = objP.count;
                        m.amount_purchase = objP.amount;
                    }

                    //卖出数
                    var objS = objSemiSales.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (objS != null)
                    {
                        m.count_sales = objS.count;
                        m.amount_sales = objS.amount;
                    }

                    //入库数
                    var objPI = objSemiPutin.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (objPI != null)
                    {
                        m.count_putin = objPI.count;
                        m.amount_putin = objPI.amount;
                    }

                    //发货数
                    var objShip = objSemiProduction.Where(a => a.bailun_sku.ToLower() == item.bailun_sku.ToLower() && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                    if (objShip != null)
                    {
                        m.count_shipping = objShip.count;
                        m.amount_shipping = objShip.amount;
                    }

                    m.difference_count = m.count_purchase - m.count_sales;
                    m.difference_amount = m.amount_purchase - m.amount_sales;
                    m.diff_putin_count = m.count_putin - m.count_shipping;
                    m.diff_putin_amount = m.amount_putin - m.amount_shipping;

                    list.Add(m);
                }

                #endregion

            }

            return list;
        }

        public string Save(MySqlConnection cn,List<dc_daily_purchase_sales> list,DateTime day)
        {
            if (cn.State == System.Data.ConnectionState.Closed)
            {
                cn.Open();
            }
            
            cn.Execute("delete from dc_daily_purchase_sales where day='" + day.ToString("yyyy-MM-dd") + "'");

            var sql = "insert dc_daily_purchase_sales (day,warehouse_code,warehouse_name,bailun_sku,count_sales,amount_sales,count_purchase,amount_purchase,difference_count,difference_amount,createtime,stock_start,stockamount_start,stock_end,stockamount_end,count_putin,amount_putin,diff_putin_count,diff_putin_amount,count_shipping,amount_shipping,isfinish) values ";
            var str = "";
            var index = 0;
            var pagesize = 5000;


            while (index < list.Count)
            {
                var m = list[index];
                str += $"('{m.day.ToString("yyyy-MM-dd")}','{m.warehouse_code}','{m.warehouse_name}','{m.bailun_sku}',{m.count_sales},'{m.amount_sales}','{m.count_purchase}','{m.amount_purchase}','{m.difference_count}','{m.difference_amount}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{m.stock_start},{m.stockamount_start},{m.stock_end},{m.stockamount_end},{m.count_putin},{m.amount_putin},{m.diff_putin_count},{m.diff_putin_amount},{m.count_shipping},{m.amount_shipping},{m.isfinish}),";

                if ((index + 1) % pagesize == 0)
                {
                    str = str.Substring(0, str.Length - 1);

                    cn.Execute(sql + str, null, null, 2 * 60);

                    str = "";
                }
                else if (index + 1 == list.Count)
                {
                    str = str.Substring(0, str.Length - 1);

                    cn.Execute(sql + str, null, null, 2 * 60);

                    str = "";
                }

                index++;
            }

            return "";
        }


    }

    class SkuWarehouse
    {
        public string bailun_sku { get; set; }

        public string warehouse_code { get; set; }

        public string warehouse_name { get; set; }
    }
}
