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

namespace Bailun.DC.Services
{
    public class FinanceReportServices
    {
        #region 资产负债表

        /// <summary>
        /// 获取资产负债表
        /// </summary>
        /// <param name="type">类型，1：日，2：周，3：月，4：年</param>
        /// <param name="date">日期</param>
        /// <param name="paycompayvalue">付款主体id</param>
        /// <returns></returns>
        public List<dc_balance_sheet> GetBalanceSheet(int type, DateTime date, int paycompayvalue)
        {
            var count = 6;
            var dt = new List<string>();
            var currentDay = DateTime.Parse(date.ToShortDateString());
            dt.Add(currentDay.ToString("yyyy-MM-dd"));

            for (var i = 1; i <= count; i++)
            {
                var d = currentDay;
                if (type == 2)
                {
                    d = currentDay.AddDays(-(i * 7));
                }
                else if (type == 3)
                {
                    d = currentDay.AddMonths(-i);
                }
                else if (type == 4)
                {
                    d = currentDay.AddYears(-i);
                }
                else
                {
                    d = currentDay.AddDays(-i);
                }

                dt.Add(d.ToString("yyyy-MM-dd"));
            }

            var str = string.Join("','", dt);

            var sql = "select * from dc_balance_sheet where company_value=" + paycompayvalue + " and statistical_time in ('" + str + "')  order by statistical_time";
            var list = new List<dc_balance_sheet>();
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                list = cn.Query<dc_balance_sheet>(sql).AsList();
            }

            if (list.Count < dt.Count)
            {
                if (list.Count == 0)
                {
                    list = new List<dc_balance_sheet>();
                }

                foreach (var item in dt)
                {
                    var m = list.Where(a => a.statistical_time.ToString("yyyy-MM-dd") == item).FirstOrDefault();
                    if (m == null)
                    {
                        m = new dc_balance_sheet()
                        {
                            accounts_payable = 0,
                            accounts_receivable = 0,
                            advance_received = 0,
                            cl_count = 0,
                            cl_dividends_payable = 0,
                            cl_notes_payable = 0,
                            cl_oneyear_noncurrent_liabilities = 0,
                            cl_other_liabilities = 0,
                            cl_payable_interest = 0,
                            cl_trading_financ_liabilities = 0,
                            company_name = "",
                            company_value = paycompayvalue,
                            count_assets = 0,
                            count_liabilities = 0,
                            count_liabilities_and_oe = 0,
                            fa_bill_receivable = 0,
                            fa_count = 0,
                            fa_dividends_receivable = 0,
                            fa_interest_receivable = 0,
                            fa_oneyear_noncurrent_assets = 0,
                            fa_other_assets = 0,
                            fa_trading_finan_asset = 0,
                            fixed_assets = 0,
                            inventory_amount = 0,
                            long_term_accounts_payable = 0,
                            monetary_fund = 0,
                            nca_avai_for_sale_fassets = 0,
                            nca_construction_in_process = 0,
                            nca_count = 0,
                            nca_deferred_tax_assets = 0,
                            nca_development_expenditure = 0,
                            nca_disposal_of_fixed_assets = 0,
                            nca_engineering_material = 0,
                            nca_goodwill = 0,
                            nca_heldon_investment = 0,
                            nca_intangible_assets = 0,
                            nca_investment_property = 0,
                            nca_longterm_equity_investment = 0,
                            nca_longterm_receivable = 0,
                            nca_longterm_unamortized_expenses = 0,
                            nca_oil_and_gas_assets = 0,
                            nca_other_assets = 0,
                            nca_productive_biological_asset = 0,
                            ncl_anticipation_liabilities = 0,
                            ncl_bonds_payable = 0,
                            ncl_count = 0,
                            ncl_deferred_income_tax_liabilities = 0,
                            ncl_longterm_loan = 0,
                            ncl_other_liabilities = 0,
                            ncl_special_payable = 0,
                            oe_capital_reserve = 0,
                            oe_count = 0,
                            oe_inventory_amount = 0,
                            oe_paicl_up_capital = 0,
                            oe_surplus_public_accumulation = 0,
                            oe_undistributed_profit = 0,
                            other_accounts_payable = 0,
                            other_accounts_receivable = 0,
                            payable_remuneration = 0,
                            prepayment = 0,
                            short_term_borrow = 0,
                            statistical_time = DateTime.Parse(item),
                            taxes_payable = 0,
                            update_time = DateTime.Now,

                            bank_account_balance = 0,
                            withdraw_amount = 0,
                            platform_balance = 0,
                            logistics_prepayment = 0,
                            finished_prepayment = 0,
                            semi_prepayment = 0,

                            fba_inventory_amount = 0,
                            not_fba_inventory_amount = 0,
                            fba_transfer_amount = 0,
                            not_fba_transfer_amount = 0,
                            semi_inventory_amount = 0,
                            semi_transfer_amount = 0,

                            logistics_accounts_payable=0,
                            finished_accounts_payable=0,
                            semi_accounts_payable=0,

                        };

                        list.Add(m);
                    }
                }
            }

            return list.OrderBy(a => a.statistical_time).ToList();
        }

        /// <summary>
        /// 更新资产负债表单个字段的值
        /// </summary>
        /// <param name="id"></param>
        /// <param name="c">字段名称</param>
        /// <param name="val">值</param>
        /// <returns></returns>
        public string UpdateBalanceSheet(int id, string c, decimal val)
        {
            if (c.IndexOf("-") >= 0 || c.IndexOf("/") >= 0 || c.IndexOf("exce") >= 0)
            {
                return "更新失败，请重试！";
            }

            var sql = "update dc_balance_sheet set " + c + "=" + val + " where id=" + id;

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

                var result = cn.Execute(sql);

                return result > 0 ? "" : "保存失败，请重试！";
            }
        }

        #endregion

        #region 管理成本
        /// <summary>
        /// 获取管理成本明细
        /// </summary>
        /// <param name="url">访问地址</param>
        /// <returns></returns>
        public List<Models.Api.mOtherFee> ListOtherCost(string url)
        {
            var list = new List<Models.Api.mOtherFee>();
            var result = Common.HttpHelper.NetHelper.Request(url);
            if (!string.IsNullOrEmpty(result))
            {
                var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.Api.mListOtherFee>(result);
                if (obj != null)
                {
                    list = obj.data;
                }
            }

            return list;
        }

        /// <summary>
        /// 管理成本--利息支出    
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public List<Models.Api.mInterestExpense> ListInterestExpense(string url)
        {
            var list = new List<Models.Api.mInterestExpense>();

            var result = Common.HttpHelper.NetHelper.Request(url);
            if (!string.IsNullOrEmpty(result))
            {
                var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.Api.mInterestExpense>>(result);
                if (obj.Count > 0)
                {
                    list = obj;
                }
            }

            return list;
        }


        #endregion

        #region 平台余额

        /// <summary>
        /// 获取平台账号余额汇总
        /// </summary>
        /// <param name="m"></param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_platform_balance> ListCount(DateTime day, string platformcode, int page, int pagesize, string order, string sort, ref int total)
        {
            var sql = "select  id,platformcode,platformname,day,count(id) as account,currency,sum(amount) as amount,sum(userable_amount) as userable_amount,sum(freeze_amount) as freeze_amount,exchangerate,sum(amount_rmb) as amount_rmb,sum(userable_amount_rmb) as userable_amount_rmb,sum(freeze_amount_rmb) as freeze_amount_rmb,puttime from dc_platform_balance where delstatus=0 and day='" + day.ToString("yyyy-MM-dd") + "' ";

            if (!string.IsNullOrWhiteSpace(platformcode))
            {
                sql += " and platformcode='" + platformcode + "' ";
            }

            sql += " group by platformcode,currency,puttime";

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

                if (!string.IsNullOrWhiteSpace(sort))
                {
                    sql += " " + sort;
                }

            }

            string countsql = sql;
            sql += " limit " + (page - 1) * pagesize + "," + pagesize;


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

                var list = cn.Query<dc_platform_balance>(sql).AsList();

                total = cn.ExecuteScalar<int>("select count(1) from (" + countsql + ") as tb");

                return list;
            }
        }

        /// <summary>
        /// 获取平台账号余额汇总 统计
        /// </summary>
        /// <returns></returns>
        public dc_platform_balance ListCounts(DateTime day, string platformcode)
        {
            var sql = "select  count(id) as account,currency,sum(amount) as amount,sum(userable_amount) as userable_amount,sum(freeze_amount) as freeze_amount,exchangerate,sum(amount_rmb) as amount_rmb,sum(userable_amount_rmb) as userable_amount_rmb,sum(freeze_amount_rmb) as freeze_amount_rmb from dc_platform_balance where delstatus=0 and day='" + day.ToString("yyyy-MM-dd") + "' ";

            if (!string.IsNullOrWhiteSpace(platformcode))
            {
                sql += " and platformcode='" + platformcode + "' ";
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirst<dc_platform_balance>(sql);

                return obj;
            }
        }

        /// <summary>
        /// 获取平台下的帐号余额列表
        /// </summary>
        /// <param name="day"></param>
        /// <param name="platformcode"></param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_platform_balance> List(DateTime day, string platformcode, int page, int pagesize, string order, string sort, ref int total)
        {
            var sql = "select * from dc_platform_balance where delstatus=0 and day='" + day + "'";

            if (!string.IsNullOrEmpty(platformcode))
            {
                sql += " and platformcode='" + platformcode + "'";
            }

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

                if (!string.IsNullOrWhiteSpace(sort))
                {
                    sql += " " + sort;
                }

            }

            string countsql = sql;
            sql += " limit " + (page - 1) * pagesize + "," + pagesize;

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

                var list = cn.Query<dc_platform_balance>(sql,null,null,true,2*60).AsList();
                total = cn.ExecuteScalar<int>("select count(1) from (" + countsql + ") as tb",null,null,2*60);

                return list;
            }

        }

        /// <summary>
        /// 平台余额统计
        /// </summary>
        /// <param name="day"></param>
        /// <param name="platformcode"></param>
        /// <returns></returns>
        public dc_platform_balance ListSum(DateTime day, string platformcode)
        {
            var sql = "select sum(amount) as amount,sum(userable_amount) as userable_amount,sum(freeze_amount) as freeze_amount,sum(amount_rmb) as amount_rmb,sum(userable_amount_rmb) as userable_amount_rmb,sum(freeze_amount_rmb) as freeze_amount_rmb from dc_platform_balance where delstatus=0 and day='" + day + "'";

            if (!string.IsNullOrEmpty(platformcode))
            {
                sql += " and platformcode='" + platformcode + "'";
            }

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

                return cn.QueryFirstOrDefault<dc_platform_balance>(sql,null,null,2*60);
            }
        }

        /// <summary>
        /// 批量插入平台账号余额
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public string AddPlatformBalances(List<dc_platform_balance> list)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                try
                {
                    foreach (var item in list)
                    {
                        var m = cn.QueryFirstOrDefault<dc_platform_balance>($"select * from dc_platform_balance  where platformcode='{item.platformcode}' and currency='{item.currency}'  and delstatus=0 and `day` <'{item.day.ToString("yyyy-MM-dd")}' order by day desc limit 1");
                            
                        if (item.platformcode.ToUpper() != "EBAY" && item.platformcode.ToUpper() != "ALIEXPRESS")
                        {
                            if (m != null && m.userable_amount == item.userable_amount)
                            {
                                //item.amount = m.amount + item.amount_sales - item.withdraw_amount;  //本期期末=上一期期末+本期销售额-本期提现额
                                //item.amount_rmb = item.amount * item.exchangerate;
                                item.before_balance = m.end_balance;
                                item.end_balance = m.end_balance + item.amount_sales - item.withdraw_amount;
                            }
                            else if (m == null || (m != null && m.userable_amount != item.userable_amount))
                            {
                                //item.amount = item.userable_amount + item.amount_sales - item.withdraw_amount;
                                //item.amount_rmb = item.amount * item.exchangerate;
                                item.before_balance = item.userable_amount;
                                item.end_balance = item.userable_amount + item.amount_sales - item.withdraw_amount;
                            }
                        }
                        else
                        {
                            //item.amount = item.userable_amount;
                            //item.amount_rmb = item.userable_amount * item.exchangerate;
                            item.before_balance = item.userable_amount;
                            item.end_balance = item.userable_amount;
                        }
                        
                        if (item.id <= 0)
                        {

                            cn.Insert<dc_platform_balance>(item);
                        }
                        else
                        {
                            cn.Update<dc_platform_balance>(item);
                        }
                        
                    }

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

            }
        }

        public string ListPlatformBalance(DateTime start)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<dc_platform_balance>("select * from dc_platform_balance where delstatus=0 and day>='"+start.ToString("yyyy-MM-dd")+"'  order by day ").AsList();
                AddPlatformBalances(list);
            }

            return "";
        }

        /// <summary>
        /// 获取平台余额详情
        /// </summary>
        /// <param name="platform">平台编码</param>
        /// <param name="currency">币种</param>
        /// <param name="day">销售日期</param>
        /// <returns></returns>
        public dc_platform_balance GetPlatformBalance(string platform, string currency, DateTime day)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirstOrDefault<dc_platform_balance>($"select * from dc_platform_balance where platformcode='{platform}' and currency='{currency}' and day='{day.ToString("yyyy-MM-dd")}' and delstatus=0");

                return obj;
            }
        }

        /// <summary>
        /// 删除账号余额记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string DelPlatformBalance(int id)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var m = cn.QueryFirstOrDefault<dc_platform_balance>("select * from dc_platform_balance where id=" + id);
                    if (m == null)
                    {
                        return "找不到该记录。";
                    }

                    var sql = "update dc_platform_balance set delstatus=1 where id=" + id;
                    var result = cn.Execute(sql);
                    if (result > 0)
                    {
                        return "";
                    }

                    return "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {

                return "提交异常" + ex.Message;
            }

        }

        /// <summary>
        /// 更新平台余额的提现金额
        /// </summary>
        /// <param name="id"></param>
        /// <param name="balance"></param>
        /// <returns></returns>
        public string UpdatePlatformBalance(int id, decimal balance)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var m = cn.QueryFirstOrDefault<dc_platform_balance>("select * from dc_platform_balance where id=" + id);
                    if (m == null)
                    {
                        return "找不到该记录。";
                    }

                    m.withdraw_amount = balance;

                    m.amount = m.amount_sales + m.before_balance - m.withdraw_amount;
                    m.amount_rmb = m.amount * m.exchangerate;
                    m.userable_amount = m.amount;
                    m.userable_amount_rmb = m.amount_rmb;

                    var sql = "update dc_platform_balance set withdraw_amount=" + balance + ",amount=" + m.amount + ",amount_rmb=" + m.amount_rmb + ",userable_amount=" + m.userable_amount + ",userable_amount_rmb=" + m.userable_amount_rmb + " where id=" + id;
                    var result = cn.Execute(sql);
                    if (result > 0)
                    {
                        return "";
                    }

                    return "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {

                return "提交异常" + ex.Message;
            }
        }

        #endregion

        #region 平台编码+名称

        /// <summary>
        /// 根据平台名称 获取平台信息
        /// </summary>
        /// <param name="name">平台名称</param>
        /// <returns></returns>
        public dc_platform_and_site GetPlatformByName(string name)
        {
            var sql = "select * from dc_platform_and_site where  type=1 and  english_name=@english_name";

            var param = new DynamicParameters();
            param.Add("english_name", name);

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

                return cn.QueryFirstOrDefault<dc_platform_and_site>(sql, param);
            }

        }

        /// <summary>
        /// 获取平台列表
        /// </summary>
        /// <returns></returns>
        public List<dc_platform_and_site> ListPlatforms()
        {
            var sql = "select * from dc_platform_and_site where type=1 ";

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

                return cn.Query<dc_platform_and_site>(sql).AsList();
            }
        }

        #endregion

        #region 资产负债表子表

        /// <summary>
        /// 获取货币资金汇总
        /// </summary>
        /// <param name="date">统计日期</param>
        /// <param name="paycompanyid">付款主体</param>
        /// <returns>平台提现在途，第三方平台余额，主体银行余额</returns>
        public Tuple<decimal, decimal, decimal> GetMonetaryFundCount(DateTime date, int paycompanyid)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                //平台提现在途
                var onwayAmount = cn.QueryFirstOrDefault<decimal?>("select sum(amount_rmb) from dc_withdraw_on_way where statistical_time='" + date.ToString("yyyy-MM-dd") + "'");// and company_value=" + paycompanyid + " group by company_value");

                //第三方平台余额
                var platformBalance = cn.QueryFirstOrDefault<decimal?>("select sum(case platformcode when 'Ebay' then userable_amount_rmb when 'Aliexpress' then userable_amount_rmb else (before_balance + amount_sales - withdraw_amount) * exchangerate end ) balance from dc_platform_balance where day ='" + date.ToString("yyyy-MM-dd") + "' and delstatus=0  ");// and paycompanyid=" + paycompanyid + " group by paycompanyid");

                //主体银行余额
                var bankBalance = cn.QueryFirstOrDefault<decimal?>("select sum(balance_rmb) from dc_bank_account_balance where statistical_time='" + date.ToString("yyyy-MM-dd") + "'");//and company_value=" + paycompanyid + " group by company_value");

                return new Tuple<decimal, decimal, decimal>(onwayAmount??0, platformBalance??0, bankBalance??0);
            }
        }

        /// <summary>
        /// 获取提现在途列表
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="paycompanyId">付款主体</param>
        /// <returns></returns>
        public List<dc_withdraw_on_way> ListWithDrawOnWay(BtTableParameter request,DateTime day, int paycompanyId,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_withdraw_on_way where statistical_time='" + day.ToString("yyyy-MM-dd") + "' ";

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

                var list = cn.Page<dc_withdraw_on_way>(request.pageIndex,request.limit,sql,ref total); // and company_value=" + paycompanyId*/).AsList();

                return list.AsList();
            }
        }

        /// <summary>
        /// 获取第三方平台余额
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="paycompanyid">付款主体id</param>
        /// <returns></returns>
        public List<dc_platform_balance> ListPlatFormBalance(BtTableParameter request,DateTime day, int paycompanyid,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_platform_balance where day='" + day.ToString("yyyy-MM-dd") + "' and delstatus=0 ";

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

                var list = cn.Page<dc_platform_balance>(request.pageIndex,request.limit,sql,ref total);// and paycompanyid=" + paycompanyid);

                return list.ToList();
            }
        }

        /// <summary>
        /// 获取主体银行余额
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="paycompanyid">付款主体id</param>
        /// <returns></returns>
        public List<dc_bank_account_balance> ListBankBalance(BtTableParameter request,DateTime day, int paycompanyid,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_bank_account_balance where statistical_time='" + day.ToString("yyyy-MM-dd") + "' ";

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

                var list = cn.Page<dc_bank_account_balance>(request.pageIndex,request.limit,sql,ref total);// and company_value=" + paycompanyid);

                return list.ToList();
            }
        }

        /// <summary>
        /// 获取存货 FBA和非FBA的库存余额
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="payid">付款主体id</param>
        /// <returns></returns>
        public Tuple<decimal, decimal, decimal, decimal,decimal,decimal> ListInventoryBalanceCount(DateTime day, int payid)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var FBABalance = cn.QueryFirstOrDefault<decimal?>("select sum(balance_rmb) from dc_inventory_balance where is_transfer=0 and hq_type='FBA仓' and statistical_time='" + day.ToString("yyyy-MM-dd") +"'"); // +and 2=" + payid);
                var NoFBABalance = cn.QueryFirstOrDefault<decimal?>("select sum(balance_rmb) from dc_inventory_balance where is_transfer=0 and hq_type!='FBA仓' and statistical_time='" + day.ToString("yyyy-MM-dd") + "'"); //and 2=" + payid);

                var FBABalance_OnWay = cn.QueryFirstOrDefault<decimal?>("select sum(balance_rmb) from dc_inventory_balance where is_transfer=1 and hq_type='FBA仓' and statistical_time='" + day.ToString("yyyy-MM-dd") + "'"); //and 2=" + payid);
                var NoFBABalance_OnWay = cn.QueryFirstOrDefault<decimal?>("select sum(balance_rmb) from dc_inventory_balance where is_transfer=1 and hq_type!='FBA仓' and statistical_time='" + day.ToString("yyyy-MM-dd") + "'"); //and 2=" + payid);

                var SemiFinishBalance = cn.QueryFirstOrDefault<decimal?>("select sum(inventory_amount) from dc_semi_daily_stock where day='" + day.ToString("yyyy-MM-dd")+"'");

                var SemiTransferBalance = cn.QueryFirstOrDefault<decimal?>("select sum(unit_price*transfer_quantity) from dc_semi_daily_transfer where day='" + day.ToString("yyyy-MM-dd") + "'");

                var result = new Tuple<decimal, decimal, decimal, decimal, decimal,decimal>(FBABalance ?? 0, NoFBABalance ?? 0, FBABalance_OnWay ?? 0, NoFBABalance_OnWay ?? 0,SemiFinishBalance??0,SemiTransferBalance??0);
                return result;
            }
        }

        /// <summary>
        /// 获取存货明细
        /// </summary>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="t">0:FBA在库明细，1：非FBA在库明细，3：FBA在途明细，4：非FBA在途明细</param>
        /// <returns></returns>
        public List<dc_inventory_balance> ListInventoryBalance(BtTableParameter request,DateTime day, int payid, int t,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_inventory_balance where statistical_time='" + day.ToString("yyyy-MM-dd") + "' ";// and 2=" + payid;

                if (t == 0) //FBA在库明细
                {
                    sql += " and hq_type='FBA仓' and is_transfer=0";
                }
                else if (t == 1) //非FBA在库明细
                {
                    sql += " and hq_type!='FBA仓' and is_transfer=0";
                }
                else if (t == 3) //FBA在途明细
                {
                    sql += " and hq_type='FBA仓' and is_transfer=1";
                }
                else if (t == 4) //非FBA在途明细
                {
                    sql += " and hq_type!='FBA仓' and is_transfer=1";
                }

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

                var list = cn.Page<dc_inventory_balance>(request.pageIndex,request.limit,sql,ref total);
                return list.ToList();
            }
        }

        /// <summary>
        /// 获取半成品每日库存
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="day">统计日期</param>
        /// <param name="payid">付款主体id</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_semi_daily_stock> ListSemiFinishDailyStock(BtTableParameter request, DateTime day, int payid, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_semi_daily_stock where day='"+day.ToString("yyyy-MM-dd")+"'";

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

                var obj = cn.Page<dc_semi_daily_stock>(request.pageIndex, request.limit, sql, ref total);
                return obj.ToList();
            }
        }

        /// <summary>
        /// 半成品在途库存
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="day">统计日期</param>
        /// <param name="payid">付款主体id</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_semi_daily_transfer> ListSemiTransferStock(BtTableParameter request, DateTime day, int payid, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_semi_daily_transfer where day='"+day.ToString("yyyy-MM-dd")+"'";
                if (!string.IsNullOrEmpty(request.sort))
                {
                    sql += " order by "+request.sort+" "+request.order;
                }

                var obj = cn.Page<dc_semi_daily_transfer>(request.pageIndex, request.limit, sql, ref total);
                return obj.AsList();
            }
        }

        /// <summary>
        /// 获取短期借款明细
        /// </summary>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <returns></returns>
        public List<dc_short_term_borrow> ListShorBorrow(BtTableParameter request,DateTime day, int payid,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_short_term_borrow where statistical_time='" + day.ToString("yyyy-MM-dd") + "'";

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


                var list = cn.Page<dc_short_term_borrow>(request.pageIndex,request.limit,sql,ref total);// and 1=" + payid);  //短期借款都指定广州百伦的

                return list.ToList();
            }
        }

        /// <summary>
        ///  预付款总计（分商品供应商和物流供应商）
        /// </summary>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="repay">是否预付款，1：预付款，0：应付款</param>
        /// <returns></returns>
        public List<decimal> ListPrePayCount(DateTime day, int payid, int prepay)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlfinish = "select sum(end_amount) from dc_supplier_transaction where types=2 and is_finish=1 and day='" + day.ToString("yyyy-MM-dd") + "' "; //成品
                var sqlsemifinish = "select sum(end_amount) from dc_supplier_transaction where types=2 and is_finish=0 and day='" + day.ToString("yyyy-MM-dd") + "' "; //半成品
                var logissql = "select sum(end_amount) from dc_logistics_supplier_transaction where day='" + day.ToString("yyyy-MM-dd") + "' ";

                if (prepay == 1)
                {
                    sqlfinish += " and end_amount<0";
                    sqlsemifinish += " and end_amount<0";
                    logissql += " and end_amount<0";
                }
                else if (prepay == 0)
                {
                    sqlfinish += " and end_amount>0";
                    sqlsemifinish += " and end_amount>0";
                    logissql += " and end_amount>0";
                }


                var list = new List<decimal>();

                var obj1 = cn.QueryFirstOrDefault<decimal?>(sqlfinish);
                var obj2 = cn.QueryFirstOrDefault<decimal?>(sqlsemifinish);
                var obj3 = cn.QueryFirstOrDefault<decimal?>(logissql);



                list.Add((prepay==1?-1:1)*(obj1??0));
                list.Add((prepay == 1 ? -1 : 1) * (obj2 ??0));
                list.Add((prepay == 1 ? -1 : 1) * (obj3 ?? 0));

                return list;
            }
        }

        /// <summary>
        /// 预付款明细||应付账款明细
        /// </summary>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="repay">是否预付款，1：预付款，0：应付款</param>
        /// <param name="finish">是否成品，0：半成品，1：成品</param>
        /// <returns></returns>
        public List<dc_supplier_transaction> ListPrePay(BtTableParameter request,DateTime day, int payid, int prepay,int? finish,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_supplier_transaction where types=2 and day='" + day.ToString("yyyy-MM-dd") + "' ";// and pay_company_value=" + payid;
                if (prepay == 1)
                {
                    sql += " and end_amount<0";
                }
                else if (prepay == 0)
                {
                    sql += " and end_amount>0";
                }

                if (finish.HasValue)
                {
                    sql += " and is_finish="+finish.Value;
                }

                //if (payid == 2)  //香港百伦的取一级供应商数据
                //{
                //    sql += " and types=1";
                //}
                //else
                //{
                //    sql += " and types=2";
                //}

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

                var list = cn.Page<dc_supplier_transaction>(request.pageIndex,request.limit,sql,ref total);  //只取二级供应商的数据

                return list.ToList();
            }
        }

        /// <summary>
        /// (预付款明细||应付账款明细)按付款主体统计
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="prepay"></param>
        /// <param name="finish"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_supplier_transaction> ListPrePayGroup(BtTableParameter parameter, DateTime day, int payid, int prepay, int? finish, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                //付款主体，借方发生额，贷方发生额，日期   a.putting_amount + a.express_amount + a.adjust_amount - a.return_amount - a.return_express
                var sql = "select pay_company_name,sum(putting_amount) putting_amount,sum(pay_amount) pay_amount,day,sum(express_amount) express_amount,sum(adjust_amount) adjust_amount,sum(return_amount) return_amount,sum(return_express) return_express,sum(receive_amount) receive_amount from dc_supplier_transaction where types=2 and day='" + day.ToString("yyyy-MM-dd") + "' ";// and pay_company_value=" + payid;
                if (prepay == 1)
                {
                    sql += " and end_amount<0";
                }
                else if (prepay == 0)
                {
                    sql += " and end_amount>0";
                }

                if (finish.HasValue)
                {
                    sql += " and is_finish=" + finish.Value;
                }

                //if (payid == 2)  //香港百伦的取一级供应商数据
                //{
                //    sql += " and types=1";
                //}
                //else
                //{
                //    sql += " and types=2";
                //}

                sql += " group by pay_company_value ";

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

                var list = cn.Page<dc_supplier_transaction>(parameter.pageIndex, parameter.limit, sql, ref total);  //只取二级供应商的数据

                return list.ToList();
            }
        }

        /// <summary>
        /// 物流供应商 预付款明细||应付账款明细
        /// </summary>
        /// <param name="request"></param>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="prepay">是否预付款，1：预付款，0：应付款</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_logistics_supplier_transaction> ListPrePayLogistics(BtTableParameter request, DateTime day, int payid, int prepay, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_logistics_supplier_transaction where day='" + day.ToString("yyyy-MM-dd") + "' ";// and pay_company_value=" + payid;
                if (prepay == 1)
                {
                    sql += " and end_amount<0";
                }
                else if (prepay == 0)
                {
                    sql += " and end_amount>0";
                }

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

                try
                {
                    var list = cn.Page<dc_logistics_supplier_transaction>(request.pageIndex, request.limit, sql, ref total);
                    return list.ToList();
                }
                catch (Exception ex)
                {

                    throw;
                }
                

                
            }
        }

        /// <summary>
        /// 其他费用，应收和应付费用明细
        /// </summary>
        /// <param name="day">统计日期</param>
        /// <param name="payid">付款主体id</param>
        /// <param name="t">1:应收，2：应付</param>
        /// <returns></returns>
        public List<dc_other_cost> ListOtherCost(BtTableParameter request,DateTime day, int payid, int t,ref int total)
        {
            var sql = "select * from dc_other_cost where day='" + day.ToString("yyyy-MM-dd") + "'  and type=" + t; // + " and company_value = " + payid ;

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

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

                var list = cn.Page<dc_other_cost>(request.pageIndex,request.limit,sql,ref total).AsList();
                return list;
            }
        }

        /// <summary>
        /// 固定资产列表
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="date">统计日期</param>
        /// <param name="paycompanyid">付款主体id</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_fixed_assets_detail> ListFixedAssets(BtTableParameter request, DateTime date, int paycompanyid,int? companyvalue,ref int total)
        {
            var sql = "select * from dc_fixed_assets_detail where day='"+date.ToString("yyyy-MM-dd")+"'";

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

                if (companyvalue.HasValue)
                {
                    sql += " and company_value="+companyvalue.Value;
                }


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

                var obj = cn.Page<dc_fixed_assets_detail>(request.pageIndex, request.limit, sql, ref total);

                return obj.AsList();
            }


        }

        /// <summary>
        /// 固定资产二级明细表
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="date">统计日期</param>
        /// <param name="companyvalue">公司主体id</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<mFixedAssetsDetail> ListFixedAssetsDetails(BtTableParameter request,DateTime date,int? companyvalue,ref int total)
        {
            var sql = "select buyno,pay_time,skuname,amount,0 'creditamount',day from dc_base_buy where day='" + date.ToString("yyyy-MM-dd")+"'";

            if (companyvalue.HasValue)
            {
                sql += " and company_value="+companyvalue.Value;
            }

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

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

                var obj = cn.Query<mFixedAssetsDetail>(sql);

                total = obj.Count();

                return obj.AsList();
            }
        }

        #endregion

        #region 付款主体

        /// <summary>
        /// 获取付款主体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public dc_company_main_body GetPayCompany(int id)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var m = cn.QueryFirstOrDefault<dc_company_main_body>("select * from dc_company_main_body where company_value=" + id);

                return m;
            }
        }

        /// <summary>
        /// 获取付款主体 
        /// </summary>
        /// <param name="name">付款主体名称</param>
        /// <returns></returns>
        public dc_company_main_body GetPayCompanyByName(string name)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var m = cn.QueryFirstOrDefault<dc_company_main_body>("select * from dc_company_main_body where company_name = '"+name+"'");
                return m;
            }
        }

        #endregion

        #region 每日平台销售额（小平台）

        /// <summary>
        /// 每日平台销售额 统计分页
        /// </summary>
        /// <param name="day">记录日期</param>
        /// <param name="platformcode">平台编码</param>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="order">排序字段</param>
        /// <param name="sort">升序或降序</param>
        /// <param name="total">总记录数</param>
        /// <returns></returns>
        public List<dc_daily_platformamout> ListDailyPlatformAmountCount(DateTime day, string platformcode, int page, int pagesize, string order, string sort, ref int total)
        {
            var sql = "select day,count(id) as account,platformcode,platformname,currency,sum(put_amount) as put_amount,sum(put_amount_rmb) as put_amount_rmb,sum(freeze_amount) as freeze_amount,sum(freeze_amount_rmb) as freeze_amount_rmb,put_amount_time from dc_daily_platformamout where delstatus=0 and day='" + day.ToString("yyyy-MM-dd") + "' ";

            if (!string.IsNullOrWhiteSpace(platformcode))
            {
                sql += " and platformcode='" + platformcode + "'";
            }

            sql += " group by platformcode,currency,put_amount_time";

            string countsql = sql;
            sql += " limit " + (page - 1) * pagesize + "," + pagesize;

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

                var list = cn.Query<dc_daily_platformamout>(sql).AsList();

                total = cn.ExecuteScalar<int>("select count(1) from (" + countsql + ") as tb");

                return list;
            }

        }

        /// <summary>
        /// 每日平台销售列表 分页
        /// </summary>
        /// <param name="day">记录日期</param>
        /// <param name="platformcode">平台编码</param>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="order">排序字段</param>
        /// <param name="sort">升序或降序</param>
        /// <param name="total">符合条件的总记录数</param>
        /// <returns></returns>
        public List<dc_daily_platformamout> ListDailyPlatformAmount(DateTime day, string platformcode, int page, int pagesize, string order, string sort, ref int total)
        {
            var sql = "select * from dc_daily_platformamout where delstatus=0  and day='" + day.ToString("yyyy-MM-dd") + "' ";

            if (!string.IsNullOrWhiteSpace(platformcode))
            {
                sql += " and platformcode='" + platformcode + "'";
            }

            sql += " order by id";

            string countsql = sql;
            sql += " limit " + (page - 1) * pagesize + "," + pagesize;

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

                var list = cn.Query<dc_daily_platformamout>(sql).AsList();

                total = cn.ExecuteScalar<int>("select count(1) from (" + countsql + ") as tb");

                return list;
            }

        }

        /// <summary>
        /// 批量插入每日销售额
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public string AddDailySale(List<dc_daily_platformamout> list)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                try
                {
                    foreach (var item in list)
                    {
                        cn.Insert<dc_daily_platformamout>(item);
                    }

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

            }
        }

        /// <summary>
        /// 删除每日销售额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string DeleteDailySale(int id)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var m = cn.QueryFirstOrDefault<dc_platform_balance>("select * from dc_daily_platformamout where id=" + id);
                    if (m == null)
                    {
                        return "找不到该记录。";
                    }

                    var sql = "update dc_daily_platformamout set delstatus=1 where id=" + id;
                    var result = cn.Execute(sql);
                    if (result > 0)
                    {
                        return "";
                    }

                    return "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {

                return "提交异常" + ex.Message;
            }
        }

        #endregion

        #region 发生额

        /// <summary>
        /// 获取发生额
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_daily_happen_amount> ListHappenAmount(DateTime start, DateTime end)
        {
            var sql = $"select * from dc_daily_happen_amount where day>='{start.ToString("yyyy-MM-dd")}' and day<'{end.AddDays(1).ToString("yyyy-MM-dd")}' order by day ";

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

                return cn.Query<dc_daily_happen_amount>(sql, null, null, true, 2 * 60).AsList();
            }
        }

        #region 头程物流

        /// <summary>
        /// 获取物流头程付款额明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_base_finance_cashier> ListHeadLogisticPay(BtTableParameter parameter,DateTime start, DateTime end,ref int total)
        {
            var sql = $"select code,detail_name,type_name,companymain_name_from,paymoney_rmb,cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and sourcecode in ('newCost','oldbuy') and type_name='销售费用/物流费' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'"; ;

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

                var obj = cn.Page<dc_base_finance_cashier>(parameter.pageIndex,parameter.limit,sql,ref total,null,"",2*60).AsList();

                return obj;
            }
        }

        /// <summary>
        /// 获取物流头程付款额明细汇总
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public dc_base_finance_cashier ListHeadLogisticPayCount(DateTime start, DateTime end)
        {
            var sql = $"select sum(paymoney_rmb) paymoney_rmb,sum(cashier_paymoneyrmb) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and sourcecode in ('newCost','oldbuy') and type_name='销售费用/物流费' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'"; ;

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

                var obj = cn.QueryFirstOrDefault<dc_base_finance_cashier>(sql, null, null, 2 * 60);

                return obj;
            }
        }

        /// <summary>
        /// 获取物流头程付款额明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<mCashier> ListHeadLogisticPay(DateTime start, DateTime end)
        {
            try
            {
                var sql = $"select code,detail_name,type_name,companymain_name_from,paymoney_rmb,cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and sourcecode in ('newCost','oldbuy') and type_name='销售费用/物流费' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'"; ;

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

                    var obj = cn.Query<mCashier>(sql, null, null, 2 * 60).AsList();

                    return obj;
                }
            }
            catch (Exception ex)
            {
                return new List<mCashier>();
            }
            
        }



        /// <summary>
        /// 获取物流头程发生额明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_base_cost_first> ListHeadLogisticHappen(DateTime start, DateTime end)
        {
            var sql = $@"select DISTINCT channel_order_id,box_id,transfer_order_id,cost_first,operation_time from dc_base_cost_first where operation_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and operation_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' order by operation_time";

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

                var obj = cn.Query<dc_base_cost_first>(sql, null, null, true, 2 * 60).AsList();

                return obj;
            }
        }

        /// <summary>
        /// 获取物流头程的销售额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_sku> ListHeadLogisticSale(BtTableParameter parameter, DateTime start, DateTime end,ref int total)
        {
            var sql = $"select platform_type,origin_order_id,bailun_sku,(bailun_sku_quantity_ordered*cost_first) cost_first,create_time from dc_base_oms_sku where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and bailun_order_status!='Canceled' and cost_first>0 and has_scalp=0 and has_delete=0 and has_innersale=0 and company_id=1 order by create_time";

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

                var obj = new List<Models.Orders.dc_base_oms_sku>();

                if (parameter.limit == 0)
                {
                    obj = cn.Query<Models.Orders.dc_base_oms_sku>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<Models.Orders.dc_base_oms_sku>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }

                return obj;
            }
        }

        /// <summary>
        /// 获取物流头程费销售额总计
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_sku ListHeadLogisticSaleCount(DateTime start, DateTime end)
        {
            var sql = $"select sum(bailun_sku_quantity_ordered*cost_first) cost_first from dc_base_oms_sku where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and bailun_order_status!='Canceled' and cost_first>0 and has_scalp=0 and has_delete=0 and has_innersale=0 and company_id=1";

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

                var obj = cn.QueryFirstOrDefault<Models.Orders.dc_base_oms_sku>(sql,null, null,2 * 60);

                return obj;
            }
        }


        #endregion

        #region 尾程物流

        /// <summary>
        /// 发生额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_oms_pick> ListTailLogisticHappen(BtTableParameter parameter, DateTime start, DateTime end, ref int total)
        {
            var sql = $@"select t1.origin_order_id,pick_order_id,tracking_order_id,t1.bailun_sku,(t2.cost_tail*t1.quantity_shipped) cost_tail,shipping_time from dc_base_oms_pick t1 
                            join dc_base_oms_sku t2 on t1.bailun_sku = t2.bailun_sku and t1.origin_order_id = t2.origin_order_id and t2.bailun_order_status != 'Canceled' and t2.has_scalp = 0 and t2.has_delete = 0 and t2.has_innersale = 0 and t2.company_id = 1 and t2.cost_tail > 0
                            where t1.shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time < '{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and t1.has_delete = 0 and t1.company_id = 1 and t1.shipping_status = 'TotalShipping'";

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

                var obj = new List<dc_base_oms_pick>();

                if (parameter.limit == 0)
                {
                    obj = cn.Query<dc_base_oms_pick>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<dc_base_oms_pick>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }
                
                return obj;
            }
        }

        /// <summary>
        /// 发生额明细汇总
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public dc_base_oms_pick ListTailLogisticHappenCount(DateTime start, DateTime end)
        {
            var sql = $@"select sum(t2.cost_tail*t1.quantity_shipped) cost_tail from dc_base_oms_pick t1 
                            join dc_base_oms_sku t2 on t1.bailun_sku = t2.bailun_sku and t1.origin_order_id = t2.origin_order_id and t2.bailun_order_status != 'Canceled' and t2.has_scalp = 0 and t2.has_delete = 0 and t2.has_innersale = 0 and t2.company_id = 1 and t2.cost_tail > 0
                            where t1.shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time < '{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and t1.has_delete = 0 and t1.company_id = 1 and t1.shipping_status = 'TotalShipping'";

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

                var obj = cn.QueryFirstOrDefault<dc_base_oms_pick>(sql, null, null, 2 * 60);

                return obj;
            }
        }

        /// <summary>
        /// 销售额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_sku> ListTailLogisticSale(BtTableParameter parameter, DateTime start, DateTime end, ref int total)
        {
            var sql = $"select platform_type,origin_order_id,bailun_sku,(cost_tail*bailun_sku_quantity_ordered) as cost_tail,paid_time from dc_base_oms_sku t1 where paid_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and paid_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and bailun_order_status!='Canceled' and has_scalp=0 and has_delete=0 and has_innersale=0 and company_id=1 and cost_logistics>0";

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

                var obj = new List<Models.Orders.dc_base_oms_sku>();
                if (parameter.limit == 0)
                {
                    obj = cn.Query<Models.Orders.dc_base_oms_sku>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<Models.Orders.dc_base_oms_sku>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }
                
                return obj;
            }
        }

        /// <summary>
        /// 销售额明细汇总
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_sku ListTailLogisticSaleCount(DateTime start, DateTime end)
        {
            var sql = $"select sum(cost_logistics*bailun_sku_quantity_ordered) as cost_logistics from dc_base_oms_sku t1 where paid_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and paid_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and bailun_order_status!='Canceled' and has_scalp=0 and has_delete=0 and has_innersale=0 and company_id=1 and cost_logistics>0";

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

                var obj = cn.QueryFirstOrDefault<Models.Orders.dc_base_oms_sku>(sql, null, null, 2 * 60);
                return obj;
            }
        }


        #endregion

        #region 一级供应商

        /// <summary>
        /// 付款额明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_base_finance_cashier> ListFirstSupplierPay(BtTableParameter parameter,DateTime start, DateTime end,ref int total)
        {
            var sql = $"select code,detail_name,type_name,companymain_name_from,paymoney_rmb,cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and sourcecode ='Buy' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'";

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

                var obj = new List<dc_base_finance_cashier>();

                if (parameter.limit == 0)
                {
                    obj = cn.Query<dc_base_finance_cashier>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<dc_base_finance_cashier>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }
                
                return obj;
            }
        }

        /// <summary>
        /// 付款明细汇总
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public dc_base_finance_cashier ListFirstSupplierPayCount(DateTime start, DateTime end)
        {
            var sql = $"select sum(paymoney_rmb) paymoney_rmb,sum(cashier_paymoneyrmb) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and sourcecode ='Buy' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'";

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

                var obj = cn.QueryFirstOrDefault<dc_base_finance_cashier>(sql, null, null, 2 * 60);
                return obj;
            }
        }

        /// <summary>
        /// 发生额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_purchase_details> ListFirstSupplierHappen(BtTableParameter parameter,DateTime start,DateTime end,ref int total)
        {
            var sql = $"select purchase_id,bailun_sku,quantity_purchase,unit_price,(unit_price*quantity_purchase) amount,create_time from dc_base_purchase_details where status>-1 and create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and ispush=0"; ;

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

                var obj = new List<dc_base_purchase_details>();

                if (parameter.limit == 0)
                {
                    obj = cn.Query<dc_base_purchase_details>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<dc_base_purchase_details>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }

                return obj;
            }
        }

        /// <summary>
        /// 发生额汇总
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public dc_base_purchase_details ListFirstSupplierHappenCount(DateTime start, DateTime end)
        {
            var sql = $"select sum(unit_price*quantity_purchase) amount from dc_base_purchase_details where status>-1 and create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and ispush=0"; ;

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

                var obj = cn.QueryFirstOrDefault<dc_base_purchase_details>(sql, null, null, 2 * 60);
                return obj;
            }
        }

        /// <summary>
        /// 销售额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_oms_pick> ListFirstSupplierSale(BtTableParameter parameter,DateTime start,DateTime end,ref int total)
        {
            var sql = $@"select t1.origin_order_id,pick_order_id,tracking_order_id,t1.bailun_sku,t1.quantity_shipped,t2.bailun_sku_unit_price,(t1.quantity_shipped*t2.bailun_sku_unit_price) amount_sale_count,shipping_time from 
                            (select quantity_shipped, origin_order_id, bailun_sku,shipping_time,pick_order_id,tracking_order_id from dc_base_oms_pick where has_delete = 0 and shipping_status = 'TotalShipping' and shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and shipping_time < '{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}') t1
                            join dc_base_oms_sku t2 on t1.origin_order_id = t2.origin_order_id and t1.bailun_sku = t2.bailun_sku and t2.has_delete = 0";

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

                var obj = new List<dc_base_oms_pick>();
                if (parameter.limit == 0)
                {
                    obj = cn.Query<dc_base_oms_pick>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<dc_base_oms_pick>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }
                
                return obj;
            }
        }

        /// <summary>
        /// 销售额明细汇总
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public dc_base_oms_pick ListFirstSupplierSaleCount(DateTime start, DateTime end)
        {
            var sql = $@"select sum(t1.quantity_shipped*t2.bailun_sku_unit_price) amount_sale_count from 
                            (select quantity_shipped, origin_order_id, bailun_sku from dc_base_oms_pick where has_delete = 0 and shipping_status = 'TotalShipping' and shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and shipping_time < '{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}') t1
                            join dc_base_oms_sku t2 on t1.origin_order_id = t2.origin_order_id and t1.bailun_sku = t2.bailun_sku and t2.has_delete = 0";

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

                var obj = cn.QueryFirstOrDefault<dc_base_oms_pick>(sql, null, null, 2 * 60);
                return obj;
            }
        }

        #endregion

        #region 二级供应商

        /// <summary>
        /// 付款额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_finance_cashier> ListSecondSupplierPay(BtTableParameter parameter, DateTime start, DateTime end, ref int total)
        {
            var sql = $@"select code,detail_name,type_name,companymain_name_from,paymoney_rmb,cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and companymain_value_from in (1,2) and tradeb_bjectname in ('广州哈倪蔓生物科技有限公司','广州拉古娜生物科技有限公司','HONGKONG KULE INTERNATIONAL TRADING LIMITED','LEIKESAER INFORMATION SERVICE LIMITED','广州市花都区花城滋柔贸易商行')";

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

                var obj = new List<dc_base_finance_cashier>();

                if (parameter.limit == 0)
                {
                    obj = cn.Query<dc_base_finance_cashier>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<dc_base_finance_cashier>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }

                return obj;
            }
        }

        /// <summary>
        /// 付款额明细汇总
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public dc_base_finance_cashier ListSecondSupplierPayCount(DateTime start, DateTime end)
        {
            var sql = $@"select sum(cashier_paymoneyrmb) as cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and companymain_value_from in (1,2) and tradeb_bjectname in ('广州哈倪蔓生物科技有限公司','广州拉古娜生物科技有限公司','HONGKONG KULE INTERNATIONAL TRADING LIMITED','LEIKESAER INFORMATION SERVICE LIMITED','广州市花都区花城滋柔贸易商行')";

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

                var obj = cn.QueryFirstOrDefault<dc_base_finance_cashier>(sql, null, null, 2 * 60);
                return obj;
            }
        }

        /// <summary>
        /// 发生额明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_semi_purchase_info> ListSecondSupplierHappen(BtTableParameter parameter, DateTime start, DateTime end, ref int total)
        {
            var sql = $@"select purchase_id,bailun_sku,quantity_purchase,unit_price,(unit_price*quantity_purchase) amount,create_time from dc_semi_purchase_info where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and status>-1";

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

                var obj = new List<dc_semi_purchase_info>();
                if (parameter.limit == 0)
                {
                    obj = cn.Query<dc_semi_purchase_info>(sql, null, null, true, 2 * 60).AsList();
                }
                else
                {
                    obj = cn.Page<dc_semi_purchase_info>(parameter.pageIndex, parameter.limit, sql, ref total, null, "", 2 * 60).AsList();
                }

                return obj;
            }
        }

        /// <summary>
        /// 发生额明细汇总
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public dc_semi_purchase_info ListSecondSupplierHappenCount(DateTime start, DateTime end)
        {
            var sql = $@"select sum(quantity_purchase*unit_price) amount from dc_semi_purchase_info where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and status>-1";

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

                var obj = cn.QueryFirstOrDefault<dc_semi_purchase_info>(sql, null, null, 2 * 60);
                return obj;
            }
        }

        #endregion


        #endregion

        #region Paypal收入分析

        /// <summary>
        /// 保存Paypal明细
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public string SaveEbayPayPalInfo(List<dc_base_income_ebay_paypal> list)
        {
            if (list.Count > 0)
            {
                var sql = "insert dc_base_income_ebay_paypal (account_name,record_time,time_zone,name,type,status,currency,gross,fee,net,from_email,to_email,transaction_id,shipping_address,address_status,item_title,item_id,shipping_handling_amount,insurance_amount,sales_tax,option_name1,option_value1,option_name2,option_value2,reference_txn_id,invoice_number,custom_number,quantity,receipt_id,balance,address_line1,address_line2,town,province,postal_code,country,contact_phone_number,subject,note,country_code,balance_impact,createtime,createuserid,createusername,company_id) values ";
                var str = " ";

                var index = 1;
                var count = list.Count;
                var pagesize = 3000;

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

                    var trans = cn.BeginTransaction();
                    
                    try
                    {
                        foreach (var item in list)
                        {
                            str += $"('{item.account_name}','{item.record_time.ToString("yyyy-MM-dd HH:mm:ss")}','{item.time_zone}','{item.name.Replace("'","").Replace("\\","")}','{item.type}','{item.status}','{item.currency}','{item.gross}','{item.fee}','{item.net}','{item.from_email.Replace("'","")}','{item.to_email}','{item.transaction_id}','{item.shipping_address.Replace("'","")}','{item.address_status}','{item.item_title.Replace("'", "")}','{item.item_id}','{item.shipping_handling_amount}','{item.insurance_amount}','{item.sales_tax}','{item.option_name1}','{item.option_value1}','{item.option_name2}','{item.option_value2}','{item.reference_txn_id}','{item.invoice_number}','{item.custom_number}','{item.quantity}','{item.receipt_id}','{item.balance}','{item.address_line1.Replace("'", "")}','{item.address_line2.Replace("'", "")}','{item.town.Replace("'", "")}','{item.province.Replace("'", "")}','{item.postal_code}','{item.country}','{item.contact_phone_number.Replace("'", "")}','{item.subject.Replace("'", "")}','{item.note.Replace("'", "")}','{item.country_code}','{item.balance_impact}','{item.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.createuserid}','{item.createusername}',{item.company_id}),";
                            
                            if (index % pagesize == 0 || index == count)
                            {
                                str = str.Substring(0, str.Length - 1);
                                cn.Execute(sql + str);

                                str = "";
                            }

                            index++;
                        }

                        trans.Commit();
                        trans.Dispose();

                        cn.Dispose();

                        return "";
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();

                        cn.Dispose();
                        return ex.Message;
                    }
                }
                
            }

            return "";
        }

        /// <summary>
        /// 删除Paypal明细
        /// </summary>
        /// <param name="account">帐号</param>
        /// <param name="start">报告开始时间</param>
        /// <param name="end">报告结束时间</param>
        /// <returns></returns>
        public string DelEbayPayPalInfo(string account, DateTime start, DateTime end)
        {
            try
            {
                var sql = "delete dc_base_income_ebay_paypal where account_name=@account and record_time>=@start and record_time<@end";
                var sqlparam = new DynamicParameters();

                sqlparam.Add("account", account);
                sqlparam.Add("start", start);
                sqlparam.Add("end", end.AddDays(1));

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

                    cn.Execute(sql, sqlparam);
                }

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

        /// <summary>
        /// Paypal明细列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="account">帐号</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_income_ebay_paypal> ListPaypalIncome(BtTableParameter parameter, DateTime start, DateTime end, string account, ref int total)
        {
            var sql = "select record_time,account_name,time_zone,type,status,currency,gross,fee,net,from_email,to_email,transaction_id,shipping_handling_amount,insurance_amount,sales_tax,custom_number,quantity,balance,country_code,balance_impact,createtime from dc_base_income_ebay_paypal ";
            var sqlwhere = " where 1=1 ";
            var sqlparam = new DynamicParameters();

            if (!string.IsNullOrEmpty(account))
            {
                sqlwhere += " and account_name=@account";
                sqlparam.Add("account", account);
            }

            sqlwhere += " and record_time>=@start";
            sqlparam.Add("start", start);

            sqlwhere += " and record_time<@end";
            sqlparam.Add("end", end.AddDays(1));

            var strSort = " ";
            if (!string.IsNullOrEmpty(parameter.sort))
            {
                strSort += " order by @sorts @orders";
                sqlparam.Add("sorts", parameter.sort);
                sqlparam.Add("orders", parameter.order);
            }


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

                try
                {
                    var obj = cn.Page<dc_base_income_ebay_paypal>(parameter.pageIndex, parameter.limit, sql + sqlwhere + strSort, ref total, sqlparam, "select count(id) from dc_base_income_ebay_paypal " + sqlwhere, 2 * 60);

                    return obj.AsList();
                }
                catch (Exception ex)
                {

                    return new List<dc_base_income_ebay_paypal>();
                }
            
            }
        }

        /// <summary>
        /// Paypal明细列表汇总
        /// </summary>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="account">帐号</param>
        /// <returns></returns>
        public dc_base_income_ebay_paypal ListPaypalIncomeCount(DateTime start, DateTime end, string account)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select sum(gross) gross,sum(fee) fee,sum(net) net,sum(shipping_handling_amount) shipping_handling_amount,sum(insurance_amount) insurance_amount,sum(sales_tax) sales_tax,sum(quantity) quantity,sum(balance) balance from dc_base_income_ebay_paypal where 1=1 ";
            
            if (!string.IsNullOrEmpty(account))
            {
                sql += " and account_name=@account";
                sqlparam.Add("account", account);
            }

            sql += " and record_time>=@start";
            sqlparam.Add("start", start);

            sql += " and record_time<@end";
            sqlparam.Add("end", end.AddDays(1));

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

                var obj = cn.QueryFirstOrDefault<dc_base_income_ebay_paypal>(sql, sqlparam, null, 2 * 60);
                return obj;
            }
        }

        /// <summary>
        /// 分析paypal明细，并出分录数据
        /// </summary>
        /// <param name="account">帐号</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public string Analyze_PaypalIncoming(string account, DateTime start, DateTime end)
        {
            try
            {
                var sql = "";

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

                    // 1、预收款  'eBay竞拍付款','移动支付','普通付款'   【正数】
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and balance_impact = '贷记' and account_name = '{account}' and type in ('eBay竞拍付款', '移动支付', '普通付款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objAdvanceReceive_p = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    // 1、预收款  'eBay竞拍付款','移动支付','普通付款'   【负数】
                    sql = $@"select currency, sum(-gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and balance_impact = '借记' and account_name = '{account}' and type in ('eBay竞拍付款', '移动支付', '普通付款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objAdvanceReceive_n = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--2、临时冻结   '冻结余额以调查争议'
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and account_name = '{account}' and type in ('冻结余额以调查争议') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objFreeze = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //付款撤销 负数
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('付款撤销') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0
group by currency";
                    var objPaymentCancel_n = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);


                    //付款撤销 正数
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('付款撤销') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross>0
group by currency";
                    var objPaymentCancel_p = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);


                    //退单
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('退单') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0
group by currency";
                    var objChargeback = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //退单撤销
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('退单撤销') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross>0
group by currency";
                    var objCharebackCancel = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--3、争议补偿
                    sql = $@"select currency,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and account_name = '{account}' and type in ('取消冻结以解决争议') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objCompensationDispute = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--4、已发放退款 负数
                    sql = $@"select currency,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('付款退款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0
group by currency";
                    var objRefundIssued_n = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--4、已发放退款 正数
                    sql = $@"select currency,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('付款退款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross>0
group by currency";
                    var objRefundIssued_p = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--5、在线发送的付款
                    //-- 已付款项
                    sql = $@"select currency,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('普通付款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0 and balance_impact = '借记' and gross<0
group by currency";
                    var objPayment = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--第三方偿付
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('第三方偿付') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objThirdPartyReimbursement = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //-- 集中付款 分正数和负数，如果是正数则是其他帐号转进来的，如果是负数则是本帐号转出的
                    sql = $@"select currency,from_email as account,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('集中付款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross>0
group by currency,from_email";
                    var objCentralizedPayment_p = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //-- 集中付款 --负数
                    sql = $@"select currency,sum(gross) gross,to_email as account from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('集中付款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0
group by currency,to_email";
                    var objCentralizedPayment_n = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);


                    //--快速结账付款
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('快速结账付款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objExpressPayment = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--费用
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('费用','服务费') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objFee = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--6、转账和提现
                    //-- 币种兑换 普通币种兑换 --负数
                    sql = $@"select currency,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('普通币种兑换') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0
group by currency";
                    var objCurrencyConvert_n = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //-- 币种兑换 普通币种兑换 --正数
                    sql = $@"select currency,sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('普通币种兑换') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross>0
group by currency";
                    var objCurrencyConvert_p = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);


                    //币种兑换 用户发起的币种兑换  --负数
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('用户发起的币种兑换') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0
group by currency";
                    var objCurrencyConvertByCus_n = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //币种兑换 用户发起的币种兑换  --正数
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('用户发起的币种兑换') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross>0
group by currency";
                    var objCurrencyConvertByCus_p = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);

                    //--提现
                    sql = $@"select currency, sum(gross) gross from dc_base_income_ebay_paypal where company_id = 1 and account_name = '{account}' and type in ('普通提现') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
group by currency";
                    var objWithDraw = cn.Query<mPaypalIncome>(sql, null, null, true, 2 * 60);



                    //已收款项
                    if (objAdvanceReceive_p.Count() > 0)
                    {
                        foreach (var item in objAdvanceReceive_p)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                amount = item.gross,
                                createtime = DateTime.Now,
                                balance_impact = "借",
                                currency = item.currency,

                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                                account_subject = "预收货款",
                                note = "其他货币资金",
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                amount = item.gross,
                                createtime = DateTime.Now,
                                balance_impact = "贷",
                                currency = item.currency,

                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                                account_subject = "预收货款",
                                note = "预收账款",
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //临时冻结
                    if (objFreeze.Count() > 0)
                    {
                        foreach (var item in objFreeze)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "临时冻结",
                                amount = Math.Abs(item.gross),
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "预收账款",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "临时冻结",
                                amount = Math.Abs(item.gross),
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //付款撤销 负数


                    //付款撤销 正数
                    if (objPaymentCancel_p.Count() > 0)
                    {
                        foreach (var item in objPaymentCancel_p)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "撤销正数",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "撤销正数",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "营业外收入",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //退单
                    if (objChargeback.Count() > 0)
                    {
                        foreach (var item in objChargeback)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "退单结算",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "营业外支出",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "退单结算",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //退单撤销
                    //if (objCharebackCancel.Count() > 0)
                    //{
                    //    foreach (var item in objCharebackCancel)
                    //    {
                    //        var m1 = new dc_mid_incoming_paypal
                    //        {
                    //            account_subject = "退单撤销",
                    //            amount = item.gross,
                    //            balance_impact = "借",
                    //            createtime = DateTime.Now,
                    //            currency = item.currency,

                    //            note = "其他货币资金",
                    //            paypal_account = account,
                    //            platform_bank = "paypal",
                    //            receive_account = account,
                    //            recordtime = start.ToString("yyyy-MM")
                    //        };

                    //        var m2 = new dc_mid_incoming_paypal
                    //        {
                    //            account_subject = "退单撤销",
                    //            amount = item.gross,
                    //            balance_impact = "贷",
                    //            createtime = DateTime.Now,
                    //            currency = item.currency,

                    //            note = "营业外收入",
                    //            paypal_account = account,
                    //            platform_bank = "",
                    //            receive_account = account,
                    //            recordtime = start.ToString("yyyy-MM")
                    //        };

                    //        cn.Insert<dc_mid_incoming_paypal>(m1);
                    //        cn.Insert<dc_mid_incoming_paypal>(m2);
                    //    }
                    //}

                    //争议补偿
                    if (objCompensationDispute.Count() > 0)
                    {
                        foreach (var item in objCompensationDispute)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "更新为撤销",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "更新为撤销",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "预收账款",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //已发放退款 --负数
                    if (objRefundIssued_n.Count() > 0)
                    {
                        //获取退款明细
                        var listreturn = cn.Query<dc_base_income_ebay_paypal>($"select * from dc_base_income_ebay_paypal where company_id = 1 and `status`= '已完成' and account_name = '{account}' and type in ('付款退款') and record_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and record_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and gross<0");

                        var listShippingStatus = new OrdersServices().CheckShipStatusByTranId(listreturn.Select(a => a.reference_txn_id).ToList());

                        foreach (var item in listShippingStatus)
                        {
                            //匹配退款金额
                            var temp = listreturn.Where(a => a.reference_txn_id == item.transaction_id).FirstOrDefault();
                            if (temp != null)
                            {
                                item.amount_return = temp.gross;
                            }
                        }

                        //匹配币种

                        foreach (var item in objRefundIssued_n)
                        {
                            var listShippingStatusCurrency = listShippingStatus.Where(a => a.currency == item.currency);

                            //主营业务收入 - 销售折扣与折让
                            //退款金额小于等于发货金额，冲营业收入
                            var amount_incoming = listShippingStatusCurrency.Where(a => a.sales_amount_sku >= a.amount_return).Sum(a => a.amount_return);
                            //退款金额大于发货金额，发货金额冲预收
                            //var amount


                            //预收账款
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "退款和撤销",
                                amount = Math.Abs(item.gross),
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "预收账款",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            //其他货币资金
                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "退款和撤销",
                                amount = Math.Abs(item.gross),
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他币种资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //已发放退款 --正数
                    if (objRefundIssued_p.Count() > 0)
                    {
                        foreach (var item in objRefundIssued_p)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "退款正数和撤销正数",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "ebay",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "退款正数和撤销正数",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "营业外收入",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //在线发送的付款
                    //已付款项
                    if (objPayment.Count() > 0)
                    {
                        foreach (var item in objPayment)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "已付款项",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "主营业务收入-销售折扣与折让",
                                paypal_account = account,
                                platform_bank = "ebay",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "已付款项",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //第三方偿付
                    if (objThirdPartyReimbursement.Count() > 0)
                    {
                        foreach (var item in objThirdPartyReimbursement)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "第三方偿付",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "营业外支出",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "已付款项",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //集中付款 -- 正数
                    if (objCentralizedPayment_p.Count() > 0)
                    {
                        foreach (var item in objCentralizedPayment_p)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = item.account + "转" + account,
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = item.account + "转" + account,
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = item.account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //集中付款 -- 负数
                    if (objCentralizedPayment_n.Count() > 0)
                    {
                        foreach (var item in objCentralizedPayment_n)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = account + "转" + item.account,
                                amount = Math.Abs(item.gross),
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = item.account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = account + "转" + item.account,
                                amount = Math.Abs(item.gross),
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);

                        }
                    }

                    //快速结账付款
                    if (objExpressPayment.Count() > 0)
                    {
                        foreach (var item in objExpressPayment)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "在线发送的付款",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "销售费用-佣金及平台费",
                                paypal_account = account,
                                platform_bank = "ebay",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "在线发送的付款",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    var objPayEbayFee = objFee.ToList();
                    objPayEbayFee.AddRange(objAdvanceReceive_n);

                    //支付ebay费用
                    if (objPayEbayFee.Count() > 0)
                    {
                        foreach (var item in objPayEbayFee)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "支付ebay费用",
                                amount = item.gross,
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "销售费用 - 佣金及平台费",
                                paypal_account = account,
                                platform_bank = "ebay",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "支付ebay费用",
                                amount = item.gross,
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }

                    //币种兑换 --负数
                    if (objCurrencyConvert_n.Count() > 0)
                    {
                        foreach (var item in objCurrencyConvert_n)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "币种兑换",
                                amount = Math.Abs(item.gross),
                                balance_impact = (item.gross < 0 ? "贷" : "借"),
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };
                        }
                    }

                    //币种兑换 --正数
                    if (objCurrencyConvert_p.Count() > 0)
                    {
                        foreach (var item in objCurrencyConvert_p)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "币种兑换",
                                amount = Math.Abs(item.gross),
                                balance_impact = (item.gross < 0 ? "贷" : "借"),
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                        }
                    }

                    //用户发起的币种兑换 --负数
                    if (objCurrencyConvertByCus_n.Count() > 0)
                    {
                        foreach (var item in objCurrencyConvertByCus_n)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "币种兑换",
                                amount = Math.Abs(item.gross),
                                balance_impact = (item.gross < 0 ? "贷" : "借"),
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };
                            cn.Insert<dc_mid_incoming_paypal>(m1);
                        }
                    }

                    //提现
                    if (objWithDraw.Count() > 0)
                    {
                        foreach (var item in objWithDraw)
                        {
                            var m1 = new dc_mid_incoming_paypal
                            {
                                account_subject = "提现",
                                amount = Math.Abs(item.gross),
                                balance_impact = "借",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "银行存款",
                                paypal_account = account,
                                platform_bank = "恒生银行",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM"),
                            };

                            var m2 = new dc_mid_incoming_paypal
                            {
                                account_subject = "提现",
                                amount = Math.Abs(item.gross),
                                balance_impact = "贷",
                                createtime = DateTime.Now,
                                currency = item.currency,

                                note = "其他货币资金",
                                paypal_account = account,
                                platform_bank = "paypal",
                                receive_account = account,
                                recordtime = start.ToString("yyyy-MM")
                            };

                            cn.Insert<dc_mid_incoming_paypal>(m1);
                            cn.Insert<dc_mid_incoming_paypal>(m2);
                        }
                    }
                }

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

        /// <summary>
        /// 获取Paypal分录明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="month">月份</param>
        /// <param name="account">帐号</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_mid_incoming_paypal> ListPaypalIncoming(BtTableParameter parameter, string month, string account,string currency,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select * from dc_mid_incoming_paypal where recordtime='"+month+"'";

            if (!string.IsNullOrEmpty(account))
            {
                sql += " and paypal_account=@account";
                sqlparam.Add("account", account);
            }

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


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

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

                return obj.ToList();
            }
        }



        #endregion

        #region 跨境电商现金流分析

        /// <summary>
        /// 每日总收支明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_daily_pay_income> ListDailyPayIncoming(DateTime start, DateTime end)
        {
            var sql = $"select * from dc_daily_pay_income where record_time>='{start.ToString("yyyy-MM-dd")}' and record_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}' order by record_time desc";

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

                var obj = cn.Query<dc_daily_pay_income>(sql);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 每日现金流动态分析明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_daily_companymoneystatus> ListDailyCompanyMoney(DateTime start, DateTime end)
        {
            var sql = $"select * from dc_daily_companymoneystatus where record_time>='{start.ToString("yyyy-MM-dd")}' and record_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}' order by record_time desc";

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

                var obj = cn.Query<dc_daily_companymoneystatus>(sql);

                return obj.ToList();
            }
        }

        #endregion

        #region 进销存

        /// <summary>
        /// 仓库进销存
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">期初日期</param>
        /// <param name="end">期末日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_daily_purchase_sell_stock> ListWHPurchaseSellStock(BtTableParameter parameter, string warehousetype, string warehouse, DateTime start, DateTime end,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sqlcount = $@"select count(DISTINCT t1.warehouse_code) from dc_daily_purchase_sell_stock t1 ";
            var sql = $@"select t1.warehouse_code,t1.warehouse_name,sum(t2.end_onway_count) start_onway_count,sum(t2.end_stock_count) start_stock_count,sum(t2.end_onway_amount) start_onway_amount,sum(t2.end_stock_amount) start_stock_amount,sum(t3.end_onway_count) end_onway_count,sum(t3.end_stock_count) end_stock_count,sum(t3.end_onway_amount) end_onway_amount,sum(t3.end_stock_amount) end_stock_amount,t4.end_stock_amount as amount,t4.end_stock_count as count,sum(if(t3.end_stock_count is null,0,t3.end_stock_count)-if(t2.end_stock_count is null,0,t2.end_stock_count)) diff_stock_count,sum(if(t3.end_stock_amount is null,0,t3.end_stock_amount)-if(t2.end_stock_amount is null,0,t2.end_stock_amount)) diff_stock_amount,sum(if(t3.end_onway_count is null,0,t3.end_onway_count)-if(t2.end_onway_count is null,0,t2.end_onway_count)) diff_onway_count,sum(if(t3.end_onway_amount is null,0,t3.end_onway_amount)-if(t2.end_onway_amount is null,0,t2.end_onway_amount)) diff_onway_amount from (select DISTINCT t1.bailun_sku,t1.warehouse_code,t1.warehouse_name from dc_daily_purchase_sell_stock t1 
                        ";

            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='"+warehousetype+"'";
                sqlcount += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='"+warehousetype+"'";
                //sqlparam.Add("hq_type", warehousetype);

                if (!string.IsNullOrEmpty(warehouse))
                {
                    sql += " and t2.warehouse_code='"+warehouse+"'";
                    sqlcount += "  and t2.warehouse_code='"+warehouse+"'";
                    //sqlparam.Add("warehouse", warehouse);
                }
            }

            if (!string.IsNullOrEmpty(warehouse))
            {
                sql += $@" where t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}') t1
                        left join dc_daily_purchase_sell_stock t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code and t2.record_time='{start.ToString("yyyy-MM-dd")}' and t2.warehouse_code='{warehouse}'
                        left join dc_daily_purchase_sell_stock t3 on t1.bailun_sku=t3.bailun_sku and t1.warehouse_code=t3.warehouse_code and t3.record_time='{end.ToString("yyyy-MM-dd")}' and t3.warehouse_code='{warehouse}'";
            }
            else
            {
                sql += $@" where t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}') t1
                        left join dc_daily_purchase_sell_stock t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code and t2.record_time='{start.ToString("yyyy-MM-dd")}'
                        left join dc_daily_purchase_sell_stock t3 on t1.bailun_sku=t3.bailun_sku and t1.warehouse_code=t3.warehouse_code and t3.record_time='{end.ToString("yyyy-MM-dd")}'";
            }

            sql += $"left join (select t01.bailun_sku,t01.warehouse_code,sum((t01.end_stock_count-t01.start_stock_count)+(t01.end_onway_count-t01.start_onway_count)) end_stock_count,sum((t01.end_stock_amount-t01.start_stock_amount)+(t01.end_onway_amount-t01.start_onway_amount)) end_stock_amount from dc_daily_purchase_sell_stock t01 ";
            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " join dc_base_warehouse t02 on t01.warehouse_code=t02.warehouse_code and t02.hq_type='" + warehousetype + "'";

                if (!string.IsNullOrEmpty(warehouse))
                {
                    sql += " and t02.warehouse_code='" + warehouse + "'";
                }
            }
            sql += $" where t01.record_time>='{start.ToString("yyyy-MM-dd")}' and t01.record_time<='{end.ToString("yyyy-MM-dd")}' group by t01.bailun_sku,t01.warehouse_code) t4 on t1.bailun_sku=t4.bailun_sku and t4.warehouse_code=t1.warehouse_code";

            sql += " group by t1.warehouse_code ";

            sqlcount += $" where t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}'";

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

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

                try
                {
                    var obj = cn.Page<dc_daily_purchase_sell_stock>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam, sqlcount, 2 * 60);
                    return obj.ToList();
                }
                catch (Exception ex)
                {

                    throw ex;
                }
                
            }
        }

        /// <summary>
        /// 仓库进销存总计
        /// </summary>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">期初日期</param>
        /// <param name="end">期末日期</param>
        /// <returns></returns>
        public dc_daily_purchase_sell_stock ListWHPurchaseSellStockCount(string warehousetype, string warehouse, DateTime start, DateTime end,string sku=null)
        {
            var sqlparam = new DynamicParameters();
            var sql = $@"select sum(t2.end_onway_count) start_onway_count,sum(t2.end_stock_count) start_stock_count,sum(t2.end_onway_amount) start_onway_amount,sum(t2.end_stock_amount) start_stock_amount,sum(t3.end_onway_count) end_onway_count,sum(t3.end_stock_count) end_stock_count,sum(t3.end_onway_amount) end_onway_amount,sum(t3.end_stock_amount) end_stock_amount,sum(t4.end_stock_amount) as amount,sum(t4.end_stock_count) as count,sum(t3.end_stock_count-t2.end_stock_count) diff_stock_count,sum(t3.end_stock_amount-t2.end_stock_amount) diff_stock_amount,sum(t3.end_onway_count-t2.end_onway_count) diff_onway_count,sum(t3.end_onway_amount-t2.end_onway_amount) diff_onway_amount from (select DISTINCT t1.bailun_sku,t1.warehouse_code,t1.warehouse_name from dc_daily_purchase_sell_stock t1 
                        ";

            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='"+warehousetype+"'";
                //sqlparam.Add("hq_type", warehousetype);

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

            if (!string.IsNullOrEmpty(warehouse))
            {
                sql += $@" where (t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}') {(!string.IsNullOrEmpty(sku)?" and t1.bailun_sku='"+sku+"'":"")}) t1
                        left join dc_daily_purchase_sell_stock t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code and t2.record_time='{start.ToString("yyyy-MM-dd")}' and t2.warehouse_code='{warehouse}'
                        left join dc_daily_purchase_sell_stock t3 on t1.bailun_sku=t3.bailun_sku and t1.warehouse_code=t3.warehouse_code and t3.record_time='{end.ToString("yyyy-MM-dd")}' and t2.warehouse_code='{warehouse}'";
            }
            else
            {
                sql += $@" where (t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}'){(!string.IsNullOrEmpty(sku) ? " and t1.bailun_sku='" + sku + "'" : "")}) t1
                        left join dc_daily_purchase_sell_stock t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code and t2.record_time='{start.ToString("yyyy-MM-dd")}'
                        left join dc_daily_purchase_sell_stock t3 on t1.bailun_sku=t3.bailun_sku and t1.warehouse_code=t3.warehouse_code and t3.record_time='{end.ToString("yyyy-MM-dd")}'";
            }

            sql += $"left join (select t01.bailun_sku,t01.warehouse_code,sum((t01.end_stock_count-t01.start_stock_count)+(t01.end_onway_count-t01.start_onway_count)) end_stock_count,sum((t01.end_stock_amount-t01.start_stock_amount)+(t01.end_onway_amount-t01.start_onway_amount)) end_stock_amount from dc_daily_purchase_sell_stock t01 ";
            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " join dc_base_warehouse t02 on t01.warehouse_code=t02.warehouse_code and t02.hq_type='" + warehousetype + "'";

                if (!string.IsNullOrEmpty(warehouse))
                {
                    sql += " and t02.warehouse_code='" + warehouse + "'";
                }
            }
            sql += $" where (t01.record_time>='{start.ToString("yyyy-MM-dd")}' and t01.record_time<='{end.ToString("yyyy-MM-dd")}') {(!string.IsNullOrEmpty(sku) ? " and t01.bailun_sku='" + sku + "'" : "")} group by t01.bailun_sku,t01.warehouse_code) t4 on t1.bailun_sku=t4.bailun_sku and t4.warehouse_code=t1.warehouse_code";

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

                var obj = cn.QueryFirstOrDefault<dc_daily_purchase_sell_stock>(sql, sqlparam, null, 2 * 60);

                return obj;
            }
        }

        /// <summary>
        /// Sku进销存列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="sku">sku编码</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">期初日期</param>
        /// <param name="end">期末日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_daily_purchase_sell_stock> ListSkuPurchaseSellStock(BtTableParameter parameter, string sku, string warehousetype, string warehouse, DateTime start, DateTime end,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = $@"select t1.bailun_sku,t4.sku_title_cn,t1.warehouse_code,t1.warehouse_name,t2.end_onway_count as start_onway_count,t2.end_stock_count as start_stock_count,t2.end_onway_amount as start_onway_amount,t2.end_stock_amount as start_stock_amount,t3.end_onway_count,t3.end_stock_count,t3.end_onway_amount,t3.end_stock_amount,(if(t3.end_stock_count is null,0,t3.end_stock_count)-if(t2.end_stock_count is null,0,t2.end_stock_count)) diff_stock_count,(if(t3.end_stock_amount is null,0,t3.end_stock_amount)-if(t2.end_stock_amount is null,0,t2.end_stock_amount)) diff_stock_amount,(if(t3.end_onway_count is null,0,t3.end_onway_count)-if(t2.end_onway_count is null,0,t2.end_onway_count)) diff_onway_count,(if(t3.end_onway_amount is null,0,t3.end_onway_amount)-if(t2.end_onway_amount is null,0,t2.end_onway_amount)) diff_onway_amount,t5.end_stock_amount amount,t5.end_stock_count as count from 
                        (select DISTINCT t1.bailun_sku,t1.warehouse_code,t1.warehouse_name from dc_daily_purchase_sell_stock t1 
                        ";
            var sqlcount = $@"select count(*) from (select distinct t1.bailun_sku,t1.warehouse_code from dc_daily_purchase_sell_stock t1 ";

            if (!string.IsNullOrEmpty(warehousetype))
            {
                if (!string.IsNullOrEmpty(warehousetype))
                {
                    sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type=@hq_type";
                    sqlcount += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='"+warehousetype+"'";
                    sqlparam.Add("hq_type", warehousetype);

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

            sql += $" where (t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}') ";

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

            sql += ") t1";
            sql += $@"  left join dc_daily_purchase_sell_stock t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code and t2.record_time='{start.ToString("yyyy-MM-dd")}'
                        left join dc_daily_purchase_sell_stock t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code and t3.record_time = '{end.ToString("yyyy-MM-dd")}'
                        left join dc_base_sku t4 on t1.bailun_sku = t4.bailun_sku ";

            sql += $"left join (select t01.bailun_sku,t01.warehouse_code,sum((t01.end_stock_count-t01.start_stock_count)+(t01.end_onway_count-t01.start_onway_count)) end_stock_count,sum((t01.end_stock_amount-t01.start_stock_amount)+(t01.end_onway_amount-t01.start_onway_amount)) end_stock_amount from dc_daily_purchase_sell_stock t01 ";
            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " join dc_base_warehouse t02 on t01.warehouse_code=t02.warehouse_code and t02.hq_type='" + warehousetype + "'";

                if (!string.IsNullOrEmpty(warehouse))
                {
                    sql += " and t02.warehouse_code='" + warehouse + "'";
                }
            }
            sql += $" where (t01.record_time>='{start.ToString("yyyy-MM-dd")}' and t01.record_time<='{end.ToString("yyyy-MM-dd")}') {(!string.IsNullOrEmpty(sku) ? " and t01.bailun_sku='" + sku + "'" : "")} group by t01.bailun_sku,t01.warehouse_code) t5 on t1.bailun_sku=t5.bailun_sku and t5.warehouse_code=t1.warehouse_code";


            sqlcount += $" where t1.record_time='{start.ToString("yyyy-MM-dd")}' or t1.record_time='{end.ToString("yyyy-MM-dd")}' {(!string.IsNullOrEmpty(sku) ? " and t1.bailun_sku='" + sku + "'" : "")}) as tb";

            if (!string.IsNullOrEmpty(parameter.sort))
            {
                var tb = "";
                if (parameter.sort.IndexOf("start") >= 0)
                {
                    tb = "t2.";
                }
                else if (parameter.sort.IndexOf("end") >= 0)
                {
                    tb = "t3.";
                }

                sql += " order by "+tb+parameter.sort+" "+parameter.order;
            }

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

                total = cn.ExecuteScalar<int>(sqlcount,null, null, 2 * 60);

                var obj = cn.Query<dc_daily_purchase_sell_stock>(sql+" limit "+((parameter.pageIndex-1)*parameter.limit+","+parameter.limit), sqlparam, null,true, 2 * 60);

                return obj.ToList();
            }
        }

        #endregion


        #region 充值记录

        /// <summary>
        /// 速卖通充值记录
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="account">帐号名称</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_finance_aliexpress_recharge> ListAliExpressCharge(BtTableParameter parameter, string account, DateTime? start, DateTime? end,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = $"select * from dc_base_finance_aliexpress_recharge where 1=1 ";

            if (!string.IsNullOrEmpty(account))
            {
                sql += " and accountname=@account";
                sqlparam.Add("account", account);
            }

            if (start.HasValue)
            {
                sql += " and record_time>=@start";
                sqlparam.Add("start", start.Value.ToString("yyyy-MM-dd"));
            }

            if (end.HasValue)
            {
                sql += " and record_time<@end";
                sqlparam.Add("end", end.Value.AddDays(1).ToString("yyyy-MM-dd"));
            }

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

                var obj = cn.Page<dc_base_finance_aliexpress_recharge>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam, "", 2 * 60);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 速卖通充值记录汇总
        /// </summary>
        /// <param name="account">帐号信息</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public dc_base_finance_aliexpress_recharge ListAliExpressChargeCount(string account, DateTime? start, DateTime? end)
        {
            var sqlparam = new DynamicParameters();
            var sql = $"select sum(amount) amount from dc_base_finance_aliexpress_recharge where 1=1 ";

            if (!string.IsNullOrEmpty(account))
            {
                sql += " and accountname=@account";
                sqlparam.Add("account", account);
            }

            if (start.HasValue)
            {
                sql += " and record_time>=@start";
                sqlparam.Add("start", start.Value.ToString("yyyy-MM-dd"));
            }

            if (end.HasValue)
            {
                sql += " and record_time<@end";
                sqlparam.Add("end", end.Value.AddDays(1).ToString("yyyy-MM-dd"));
            }

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

                var obj = cn.QueryFirstOrDefault<dc_base_finance_aliexpress_recharge>(sql,sqlparam,null, 2 * 60);

                return obj;
            }
        }

        /// <summary>
        /// 保存速卖通的充值记录
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public string SaveAliExpressCharge(List<dc_base_finance_aliexpress_recharge> list)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    foreach (var item in list)
                    {
                        cn.Insert<dc_base_finance_aliexpress_recharge>(item);
                    }
                }

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

        }

        #endregion

        #region 月度利润报表

        /// <summary>
        /// 月度利润报表
        /// </summary>
        /// <param name="start">开始月份</param>
        /// <param name="end">结束月份</param>
        /// <returns></returns>
        public List<dc_month_sale_profit> ListMonthSaleProfit(string start, string end)
        {
            var sql = $"select * from dc_month_sale_profit where month>='{start}' ";

            if (!string.IsNullOrEmpty(end))
            {
                sql += (" and month<='"+end+"'");
            }

            sql += " order by month desc limit 6";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }
                }

                var obj = cn.Query<dc_month_sale_profit>(sql, null, null, true, 2 * 60);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 更新月度利润报表
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public string UpdateMonthSaleProfit(mMonthSaleProfit_Input m,int uid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirstOrDefault<dc_month_sale_profit>("select * from dc_month_sale_profit where month='" + m.month + "'");
                if (obj == null)
                {
                    return "找不到该月份的利润数据。";
                }

                if (m.cost_sale_.HasValue)
                {
                    obj.cost_sale_ = m.cost_sale_.Value;
                }

                if (m.finance_fee.HasValue)
                {
                    obj.finance_fee = m.finance_fee.Value;
                }

                if (m.handlingcharges.HasValue)
                {
                    obj.handlingcharges = m.handlingcharges.Value;
                }

                if (m.ls_head_cost_.HasValue)
                {
                    obj.ls_head_cost_ = m.ls_head_cost_.Value;
                }

                if (m.ls_oversea_storage.HasValue)
                {
                    obj.ls_oversea_storage = m.ls_oversea_storage.Value;
                }

                if (m.ls_tail_cost_.HasValue)
                {
                    obj.ls_tail_cost_ = m.ls_tail_cost_.Value;
                }

                if (m.manager_cost_finish_wh.HasValue)
                {
                    obj.manager_cost_finish_wh = m.manager_cost_finish_wh.Value;
                }

                if (m.manager_cost_gzbailun.HasValue)
                {
                    obj.manager_cost_gzbailun = m.manager_cost_gzbailun.Value;
                }

                if (m.manager_cost_hkbailun.HasValue)
                {
                    obj.manager_cost_hkbailun = m.manager_cost_hkbailun.Value;
                }

                if (m.nonbusiness_income.HasValue)
                {
                    obj.nonbusiness_income = m.nonbusiness_income.Value;
                }

                if(m.nonbusiness_pay.HasValue)
                {
                    obj.nonbusiness_pay = m.nonbusiness_pay.Value;
                }

                if (m.other_incoming.HasValue)
                {
                    obj.other_incoming = m.other_incoming.Value;
                }

                if (m.participation_in_profit.HasValue)
                {
                    obj.participation_in_profit = m.participation_in_profit.Value;
                }

                if (m.payfor_platform_related_fee.HasValue)
                {
                    obj.payfor_platform_related_fee = m.payfor_platform_related_fee.Value;
                }

                if (m.pt_platform_ad_subscribe_fee_.HasValue)
                {
                    obj.pt_platform_ad_subscribe_fee_ = m.pt_platform_ad_subscribe_fee_.Value;
                }

                if (m.pt_platform_logistics_fee_.HasValue)
                {
                    obj.pt_platform_logistics_fee_ = m.pt_platform_logistics_fee_.Value;
                }

                if(m.pt_platform_storage_fee.HasValue)
                {
                    obj.pt_platform_storage_fee = m.pt_platform_storage_fee.Value;
                }

                //平台扣费及退款=平台费+ebay刊登费+paypal费用+平台物流费+广告及宣传费|订阅费+平台仓储费+退款
                obj.pt_count = obj.pt_platformfee + obj.pt_ebay_postingfee + obj.pt_paypal_fee + (obj.pt_platform_logistics_fee + obj.pt_platform_logistics_fee_) + (obj.pt_platform_ad_subscribe_fee + obj.pt_platform_ad_subscribe_fee_) + obj.pt_platform_storage_fee + obj.pt_refund_amount;

                //回款=销售额-平台扣费及退款
                obj.pt_incoming = obj.amount_sale - obj.pt_count;

                //物流仓储费用=头程运输+尾程物流+海外仓的仓储费+平台操作费
                obj.ls_count = (obj.ls_head_cost + obj.ls_head_cost_) + (obj.ls_tail_cost + obj.ls_tail_cost_) + obj.ls_oversea_storage + obj.ls_platform_operation_fee;

                //销售费用合计=平台扣费及退款+物流仓储费用
                obj.sale_fee_count = obj.pt_count + obj.ls_count;

                //销售利润=销售额-成本-销售费用合计
                obj.sale_profit = obj.amount_sale - (obj.cost_sale + obj.cost_sale_) - obj.sale_fee_count;

                //销售毛利率 = 销售利润/销售额
                obj.sale_profit_rate = obj.sale_profit / obj.amount_sale;

                //销售相关的管理成本=处理费+直接支付的与销售平台相关费用
                obj.sale_manager_cost = obj.handlingcharges + obj.payfor_platform_related_fee;

                //直接管理成本=管理成本香港百伦+管理成本广州百伦+管理成本成品仓+财务费用
                obj.direct_manager_cost = obj.manager_cost_hkbailun + obj.manager_cost_gzbailun + obj.manager_cost_finish_wh+obj.finance_fee;

                //管理成本合计=直接管理成本+销售相关的管理成本
                obj.manager_cost_count = obj.direct_manager_cost + obj.sale_manager_cost;

                //营业利润=销售利润-管理成本合计
                obj.business_profit = obj.sale_profit - obj.manager_cost_count;

                //营业毛利率=营业利润/销售额
                obj.business_profit_rate = obj.business_profit / obj.amount_sale;

                //实际利润=营业利润+其他收入+营业外收入-营业外支出
                obj.actual_profit = obj.business_profit + obj.other_incoming + obj.nonbusiness_income - obj.nonbusiness_pay;

                //利润结余=实际利润-分红
                obj.balance_of_profit = obj.actual_profit - obj.participation_in_profit;

                obj.lastupdatetime = DateTime.Now;

                var result = cn.Update<dc_month_sale_profit>(obj);

                return result > 0 ? "" : "更新失败，请重试";
            }
        }

        #endregion

    }
}
