using System;
using System.Collections.Generic;
using System.Text;

using Bailun.DC.Models;
using Bailun.DC.DB;
using Dapper;
using MySql.Data.MySqlClient;
using System.Linq;
using Bailun.DC.Models.Orders;

namespace Bailun.DC.Services
{
    /// <summary>
    /// 物流相关服务
    /// </summary>
    public class LogisticsServices
    {
        #region 往来报表

        /// <summary>
        /// 获取未对账金额
        /// </summary>
        /// <returns></returns>
        public decimal NoStatisticAmount()
        {
            var sql = $@"select sum(t1.merchant_shipment_cost_cny) as merchant_shipment_cost_cny from dc_base_logistics t1 where t1.diff_state_type=0 and t1.company_name!='广州嘉和货运代理有限公司' and t1.merchant_reconciliation_time>'{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd HH:mm:ss")}'";

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

                var result = cn.QueryFirstOrDefault<decimal?>(sql);

                return result ?? 0;
            }

        }

        /// <summary>
        /// 物流供应商往来统计报表
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="start">起始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="suppliername">供应商名称</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_daily_logistics_supplier_transaction> ListSupplierTransaction(DateTime start, DateTime end, string suppliername)
        {
            var sqlparam = new DynamicParameters();
            
            var sql = "select t1.suppliername,sum(t1.amount_happen) amount_happen,sum(t1.amount_pay) amount_pay,sum(t1.amount_receipt) amount_receipt,sum(t1.amount_other) as amount_other," +
                "(select st.amount_end from dc_daily_logistics_supplier_transaction st where max(t1.id) = st.id)AS amount_end " +
                " from dc_daily_logistics_supplier_transaction t1 ";
            sql += $" where t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<='{end.ToString("yyyy-MM-dd")}' ";

            //var sqlcount = $"select count(0) from (select suppliername from dc_daily_logistics_supplier_transaction t1 where t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<='{end.ToString("yyyy-MM-dd")}'";

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

                //sqlcount += " and t1.suppliername=@suppliername";
            }
            
            sql += " group by t1.suppliername ";

            //sqlcount += " group by t1.suppliername) t0 ";

            //if (!string.IsNullOrEmpty(request.sort))
            //{
            //    sql += " order by t1."+request.sort+" "+request.order;
            //}

            //var sql_start_end = $"left join dc_daily_logistics_supplier_transaction t1 on t1.suppliername=t0.suppliername and t1.day='{start.ToString("yyyy-MM-dd")}'";

            if (string.IsNullOrEmpty(suppliername))
            {
                sql += $@"union all
                            select t1.suppliername,t1.amount_happen,t1.amount_pay,t1.amount_receipt,t1.amount_other,t1.amount_end from dc_daily_logistics_supplier_transaction t1 where t1.id in (
                            select max(t0.id) from dc_daily_logistics_supplier_transaction t0 where t0.day<'{start.ToString("yyyy-MM-dd")}' 
                            and t0.suppliername in (select t1.suppliername from (
                            select suppliername from dc_daily_logistics_supplier_transaction group by suppliername) t1
                            left join (select suppliername from dc_daily_logistics_supplier_transaction where day>='{start.ToString("yyyy-MM-dd")}' and day<='{end.ToString("yyyy-MM-dd")}' group by suppliername) t2 on t1.suppliername=t2.suppliername
                            where t2.suppliername is null)
                            group by t0.suppliername)";
            }


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

                var obj = cn.Query<dc_daily_logistics_supplier_transaction>(sql, sqlparam).ToList();

                //获取期初
                sql = $@"select t1.amount_end,t1.suppliername from dc_daily_logistics_supplier_transaction t1 where t1.id in (
                        select max(id) as id from dc_daily_logistics_supplier_transaction where day<'{start.ToString("yyyy-MM-dd")}' group by suppliername)";

                var objStart = cn.Query<dc_daily_logistics_supplier_transaction>(sql);
                foreach (var item in obj)
                {
                    var o = objStart.Where(a => a.suppliername == item.suppliername).FirstOrDefault();
                    if (o != null)
                    {
                        item.amount_start = o.amount_end;
                    }
                }

                return obj;
            }
        }


        /// <summary>
        /// 物流供应商往来统计报表汇总
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="suppliername"></param>
        /// <returns></returns>
        public dc_daily_logistics_supplier_transaction ListSupplierTransactionCount(DateTime start, DateTime end, string suppliername)
        {
            var sqlparam = new DynamicParameters();

            var sql = "select sum(t1.amount_happen) amount_happen,sum(t1.amount_pay) amount_pay,sum(t1.amount_receipt) amount_receipt,sum(t1.amount_other) as amount_other" +
                " from dc_daily_logistics_supplier_transaction t1 ";
            var sqlwhere = $" where t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<='{end.ToString("yyyy-MM-dd")}' ";

            if (!string.IsNullOrEmpty(suppliername))
            {
                sqlwhere += " and t1.suppliername=@suppliername";
                sqlparam.Add("suppliername", suppliername);

            }

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

                var obj = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql+sqlwhere,sqlparam);

                //获取满足条件的供应商列表
                sql = "select suppliername from dc_daily_logistics_supplier_transaction t1 ";

                var listsupplier = cn.Query<string>(sql + sqlwhere + " group by suppliername",sqlparam);


                //获取期初
                sql = $@"select sum(t1.amount_end) as amount_end from dc_daily_logistics_supplier_transaction t1 where t1.suppliername in ('{string.Join("','",listsupplier)}') and t1.id in (
                        select max(id) as id from dc_daily_logistics_supplier_transaction where day<'{start.ToString("yyyy-MM-dd")}' group by suppliername)";

                var objStart = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);

                if (obj != null && objStart != null)
                {
                    obj.amount_start = objStart.amount_end;
                }

                //获取期末
                sql = $@"select sum(t1.amount_end) as amount_end from dc_daily_logistics_supplier_transaction t1 where t1.suppliername in ('{string.Join("','", listsupplier)}') and t1.id in (
                        select max(id) as id from dc_daily_logistics_supplier_transaction where day>='{start.ToString("yyyy-MM-dd")}' and day<='{end.ToString("yyyy-MM-dd")}' group by suppliername)";
                var objEnd = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);

                if (obj != null && objEnd != null)
                {
                    obj.amount_end = objEnd.amount_end;
                }
            

                return obj;
            }
        }

        /// <summary>
        /// 更新往来的其他金额
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="name">供应商名称</param>
        /// <param name="amount">调整金额</param>
        /// <param name="uid">当前用户ID</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string UpdateSupplierTrancation(DateTime day, string name, decimal amount,int uid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select * from dc_daily_logistics_supplier_transaction where suppliername='{name}' and day='{day.ToString("yyyy-MM-dd")}'";
                var obj = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);

                if (obj == null)
                {
                    sql = $@"select * from dc_daily_logistics_supplier_transaction where id = (
                            select max(t0.id) from dc_daily_logistics_supplier_transaction t0 where t0.day<'{day.ToString("yyyy-MM-dd")}' and t0.suppliername='{name}' group by t0.suppliername)";
                    obj = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);
                }

                if (obj != null)
                {
                    obj.amount_other = amount;
                    obj.amount_end = obj.amount_start + obj.amount_happen - obj.amount_pay + System.Math.Abs(obj.amount_receipt) + obj.amount_other;

                    cn.Execute("update dc_daily_logistics_supplier_transaction set amount_other=" + obj.amount_other + ",amount_end=" + obj.amount_end + ",lastupdatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +"',lastupdateuserid=" + uid + ",lastupdateusername='" + username + "' where id=" + obj.id);

                    return UpdateSupplierTrancationAmountEnd(name, obj.amount_end, obj.day, cn, uid, username);
                }


                return "";
            }
        }

        /// <summary>
        /// 更新物流供应商往来的期末
        /// </summary>
        /// <returns></returns>
        private string UpdateSupplierTrancationAmountEnd(string suppliername,decimal amount_start,DateTime day,MySqlConnection cn, int uid, string username)
        {
            try
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select * from dc_daily_logistics_supplier_transaction where suppliername='{suppliername}' and day='{day.AddDays(1).ToString("yyyy-MM-dd")}'";
                var obj = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);
                if (obj != null)
                {
                    obj.amount_start = amount_start;
                    obj.amount_end = obj.amount_start + obj.amount_happen - obj.amount_pay + System.Math.Abs(obj.amount_receipt) + obj.amount_other;
                    cn.Execute("update dc_daily_logistics_supplier_transaction set amount_start="+obj.amount_start+",amount_other=" + obj.amount_other + ",amount_end=" + obj.amount_end + ",lastupdatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',lastupdateuserid=" + uid + ",lastupdateusername='" + username + "' where id=" + obj.id);

                    amount_start = obj.amount_end;

                }

                if (day.AddDays(2) < DateTime.Now)
                {
                    UpdateSupplierTrancationAmountEnd(suppliername, amount_start, day.AddDays(1), cn, uid, username);
                }

                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            
        }

        #endregion

        /// <summary>
        /// 物流供应商列表
        /// </summary>
        /// <returns></returns>
        public List<string> ListSupplier()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<string>("select suppliername from dc_daily_logistics_supplier_transaction group by suppliername").ToList();

                return obj;
            }
        }

        /// <summary>
        /// 获取物流供应商往来统计的配置
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="supplier"></param>
        /// <param name="type">类型，1：需合并的，2：需删除不统计的</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_logistics_supplier_config> ListLogisticsSupplierConfig(BtTableParameter parameter,string supplier,int type,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select * from dc_logistics_supplier_config where delstatus=0 and type={type} ";

                if(!string.IsNullOrEmpty(supplier))
                {
                    sql += $" and (suppliername like '%{supplier}%' or parentsuppliername like '%{supplier}%') ";
                }

                sql += " order by lastupdatetime desc";

                var list = cn.Page<dc_logistics_supplier_config>(parameter.pageIndex, parameter.limit, sql, ref total).ToList();

                return list;
            }

        }

        /// <summary>
        /// 设置物流供应商的往来配置
        /// </summary>
        /// <param name="supplier">供应商名称</param>
        /// <param name="parentsupplier">父级供应商（type=1必须传值）</param>
        /// <param name="type">类型，1：需合并的，2：需删除不统计的</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string SaveLogisticsSupplierConfig(string supplier, string parentsupplier,int type,int uid,string username)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var sql = $@"select * from dc_logistics_supplier_config where type={type} and suppliername='{supplier}' and parentsuppliername='{parentsupplier}'";

                    var obj = cn.QueryFirstOrDefault<dc_logistics_supplier_config>(sql);

                    if (obj == null)
                    {
                        obj = new dc_logistics_supplier_config
                        {
                            lastupdatetime = DateTime.Now,
                        };
                    }

                    obj.lastupdateuserid = uid;
                    obj.lastupdateusername = username;
                    obj.suppliername = supplier;
                    obj.parentsuppliername = parentsupplier;

                    obj.lastupdatetime = DateTime.Now;
                    obj.lastupdateuserid = uid;
                    obj.lastupdateusername = username;

                    obj.type = type;
                    obj.delstatus = 0;

                    if (obj.id > 0)
                    {
                        cn.Update<dc_logistics_supplier_config>(obj);
                    }
                    else
                    {
                        cn.Insert<dc_logistics_supplier_config>(obj);
                    }


                    return "";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            
        }

        /// <summary>
        /// 删除供应商配置
        /// </summary>
        /// <param name="id"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string DelLogisticsSupplierConfig(int id,int uid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirstOrDefault<dc_logistics_supplier_config>("select * from dc_logistics_supplier_config where id=" + id);

                if(obj==null)
                {
                    return "找不到该配置";
                }
                else
                {
                    obj.delstatus = 1;
                    obj.lastupdatetime = DateTime.Now;
                    obj.lastupdateuserid = uid;
                    obj.lastupdateusername = username;

                    cn.Update<dc_logistics_supplier_config>(obj);
                }

                return "";
            }
        }


        /// <summary>
        /// 重新计算物流供应商往来
        /// </summary>
        /// <param name="day">日期</param>
        public void LogisticsSupplierTransaction(DateTime day)
        {
            //业务逻辑
            var listHappen = new List<Models.dc_base_logistics>();
            var listPay = new List<dc_base_logistics>();
            var listRecevice = new List<dc_base_logistics>();

            var list = new List<dc_daily_logistics_supplier_transaction>();


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

                
                //发生额
                var sql = $"select t1.company_name,sum(t1.amount_rmb) as merchant_shipment_cost_cny from dc_base_logistics t1 where ((t1.reconciliation_type='跟踪号对账' and t1.diff_state_type!=0) or (t1.reconciliation_type='单独费用')) and t1.merchant_reconciliation_time>='{day.ToString("yyyy-MM-dd")}' and t1.merchant_reconciliation_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' and t1.company_name!='广州嘉和货运代理有限公司' group by t1.company_name";
                listHappen = cn.Query<Models.dc_base_logistics>(sql).AsList();

                //付款额
                sql = $@"select sum(cashier_paymoneyrmb) as merchant_shipment_cost_cny,tradeb_bjectname as company_name from dc_base_finance_cashier t1 where t1.cashier_status=1 and t1.cashier_type=1 and t1.type_name='销售费用/物流费' and t1.sourcecode='newCost' and t1.cashier_time>='{day.ToString("yyyy-MM-dd")}' and t1.cashier_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' group by tradeb_bjectname";
                listPay = cn.Query<dc_base_logistics>(sql).AsList();

                //收款额
                sql = $@"select sum(cashier_paymoneyrmb) as merchant_shipment_cost_cny,tradeb_bjectname as company_name from dc_base_finance_cashier t1 where t1.cashier_status=1 and t1.cashier_type=2 and t1.type_name='销售费用/物流费' and t1.sourcecode='newCost' and t1.cashier_time>='{day.ToString("yyyy-MM-dd")}' and t1.cashier_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' group by tradeb_bjectname";
                listRecevice = cn.Query<dc_base_logistics>(sql).AsList();


                var listSupplier = new List<string>();
                listSupplier.AddRange(listHappen.Select(a => a.company_name));
                listSupplier.AddRange(listPay.Select(a => a.company_name));
                listSupplier.AddRange(listRecevice.Select(a => a.company_name));

                listSupplier = listSupplier.Distinct().ToList();

                foreach (var item in listSupplier)
                {
                    var m = new dc_daily_logistics_supplier_transaction()
                    {
                        amount_end = 0,
                        amount_happen = 0,
                        amount_other = 0,
                        amount_pay = 0,
                        amount_receipt = 0,
                        amount_start = 0,

                        createtime = DateTime.Now,
                        day = day,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = 0,
                        lastupdateusername = "admin",
                        supplierid = 0,
                        suppliername = item,

                    };

                    var objHappen = listHappen.Where(a => a.company_name == item).FirstOrDefault();
                    if (objHappen != null)
                    {
                        m.amount_happen = objHappen.merchant_shipment_cost_cny;
                    }

                    var objPay = listPay.Where(a => a.company_name == item).FirstOrDefault();
                    if (objPay != null)
                    {
                        m.amount_pay = objPay.merchant_shipment_cost_cny;
                    }

                    var objReceive = listRecevice.Where(a => a.company_name == item).FirstOrDefault();
                    if (objReceive != null)
                    {
                        m.amount_receipt = objReceive.merchant_shipment_cost_cny;
                    }

                    list.Add(m);
                }

            }

            Save(list);

        }

        private void Save(List<dc_daily_logistics_supplier_transaction> list)
        {
            if (list.Count == 0)
            {
                return;
            }

            var listSupplierConfig = new List<dc_logistics_supplier_config>();

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

                listSupplierConfig = cn.Query<dc_logistics_supplier_config>("select * from dc_logistics_supplier_config where delstatus=0").ToList();
            }


            var _sql = "insert dc_daily_logistics_supplier_transaction (day,supplierid,suppliername,amount_start,amount_happen,amount_pay,amount_receipt,amount_other,amount_end,createtime,lastupdatetime,lastupdateuserid,lastupdateusername) values ";
            var s = "";


            //增加新处理逻辑  读取配置表
            var listMerge = listSupplierConfig.Where(a => a.type == 1).ToList().GroupBy(a => a.parentsuppliername);   //需合并的
            var listDelete = listSupplierConfig.Where(a => a.type == 2).ToList();  //需删除的

            //需清理的供应商列表
            var tempSuppliers = new List<string>();

            //合并处理数据
            foreach (var item in listMerge)
            {
                tempSuppliers.AddRange(item.Select(a => a.suppliername));

                var listObj = list.Where(a => a.suppliername == item.Key || item.Select(b => b.suppliername).Contains(a.suppliername)).ToList();
                if (listObj.Count > 0)
                {
                    var newObj = new dc_daily_logistics_supplier_transaction()
                    {
                        amount_happen = listObj.Count() > 0 ? listObj.Sum(a => a.amount_happen) : 0,
                        amount_pay = listObj.Count() > 0 ? listObj.Sum(a => a.amount_pay) : 0,
                        amount_receipt = listObj.Count() > 0 ? listObj.Sum(a => a.amount_receipt) : 0,
                        amount_other = listObj.Count() > 0 ? listObj.Sum(a => a.amount_other) : 0,

                        suppliername = item.Key,
                        supplierid = 0,
                        amount_end = 0,
                        amount_start = 0,
                        createtime = DateTime.Now,
                        day = list.FirstOrDefault().day,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = 0,
                        lastupdateusername = "admin"
                    };

                    for (var i = 0; i < listObj.Count; i++)
                    {
                        list.Remove(listObj[i]);
                    }

                    list.Add(newObj);
                }

            }


            //删除数据
            foreach (var item in listDelete)
            {
                tempSuppliers.Add(item.suppliername);
                var objList = list.Where(a => a.suppliername == item.suppliername).ToList();
                if(objList.Count>0)
                {
                    for (var i = 0; i < objList.Count;i++)
                    {
                        list.Remove(objList[i]);
                    }
                }
                
            }


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

                //删除之前已经生成并且不需要显示的数据
                

                cn.Execute($"delete from dc_daily_logistics_supplier_transaction where day='{list.FirstOrDefault().day.ToString("yyyy-MM-dd")}' and suppliername in ('{string.Join("','", tempSuppliers)}')");
                


                foreach (var item in list)
                {

                    //获取期初
                    var sql = $"select * from dc_daily_logistics_supplier_transaction where suppliername='{item.suppliername}' and day<'{item.day.ToString("yyyy-MM-dd")}' order by day desc limit 1";
                    var objStart = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);
                    if (objStart != null)
                    {
                        item.amount_start = objStart.amount_end;
                    }

                    //查看是否有本期数据
                    sql = $"select * from dc_daily_logistics_supplier_transaction where suppliername='{item.suppliername}' and day='{item.day.ToString("yyyy-MM-dd")}'";
                    var obj = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);
                    if (obj != null)
                    {
                        item.amount_other = obj.amount_other;
                    }

                    cn.Execute($"delete from dc_daily_logistics_supplier_transaction where day='{item.day.ToString("yyyy-MM-dd")}' and suppliername='{item.suppliername}'");

                    //期末 = 期初+发生额-付款额+收款额（绝对值）+其他金额（手工导入数据）
                    item.amount_end = item.amount_start + item.amount_happen - item.amount_pay + System.Math.Abs(item.amount_receipt) + item.amount_other;

                    s += ($"('{item.day.ToString("yyyy-MM-dd")}',{item.supplierid},'{item.suppliername}',{item.amount_start},{item.amount_happen},{item.amount_pay},{item.amount_receipt},{item.amount_other},{item.amount_end},'{item.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.lastupdatetime.ToString("yyyy-MM-dd HH:mm:ss")}',{item.lastupdateuserid},'{item.lastupdateusername}'),");
                }

                if (s.Length > 0)
                {
                    s = s.Substring(0, s.Length - 1);

                    cn.Execute(_sql + s);
                }
            }
        }


    }
}
