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;
            }
        }

    }
}
