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 = DateTime.Parse(currentDay.AddMonths((1-i)).ToString("yyyy-MM") + "-01").AddDays(-1);

                     //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,

                            amount_borrowed = 0,

                            purchase_pro_amount = 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 ? "" : "保存失败，请重试！";
            }
        }

        /// <summary>
        /// 获取物流供应商的应付款
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public dc_daily_lg_waitpay GetLogisticsWaitPay(DateTime date)
        {
            var sql = "select * from dc_daily_lg_waitpay where day='"+date.ToString("yyyy-MM-dd")+"'";

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

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

                return obj;
            }

        }

        /// <summary>
        /// 更新物流供应商应付账款的数据
        /// </summary>
        /// <param name="id">应付账款id</param>
        /// <param name="type">更改类型，1：修改发生调整额，2：修改付款调整额，3：修改发生额</param>
        /// <param name="amount">本次修改的金额</param>
        /// <param name="userid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string UpdateLogisticsWaitPay(int id, int type, decimal amount,int userid,string username)
        {
            //期末余额 = 期初余额 + 发生额 + 发生额调整 - 付款额 - 付款额调整

            var sql = "select * from dc_daily_lg_waitpay where id="+id;

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

                var obj = cn.QueryFirstOrDefault<dc_daily_lg_waitpay>(sql);
                if (obj == null)
                {
                    return "没有找到该记录。";
                }

                if (type == 1)
                {
                    obj.amount_happen_adjust = amount;
                }
                else if (type == 2)
                {
                    obj.amount_payed_adjust = amount;
                }
                else if (type == 3)
                {
                    obj.amount_happen = amount;
                }

                obj.amount_end = obj.amount_start + obj.amount_happen + obj.amount_happen_adjust - obj.amount_payed - obj.amount_payed_adjust;

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

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

                UpdateLogisticsEndAmount(obj.day, obj.amount_end);

                //同步更新该日期后面的期初和期末
                UpdateLogisticsWaitPay(obj.day,userid,username,null);

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

        /// <summary>
        /// 更新后面数据的期初和期末
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="userid">用户id</param>
        /// <param name="username">用户名称</param>
        /// <param name="cn"></param>
        public void UpdateLogisticsWaitPay(DateTime day,int userid,string username,MySqlConnection cn)
        {
            if (day > DateTime.Now)
            {
                return ;
            }

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

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

                var obj = cn.QueryFirstOrDefault<dc_daily_lg_waitpay>(sql);
                if (obj == null)
                {
                    return;
                }

                sql = "select * from dc_daily_lg_waitpay where day='" + day.AddDays(-1).ToString("yyyy-MM-dd") + "'";
                var objPre = cn.QueryFirstOrDefault<dc_daily_lg_waitpay>(sql);
                if (objPre == null)
                {
                    return;
                }

                obj.amount_start = objPre.amount_end;
                obj.amount_end = obj.amount_start + obj.amount_happen + obj.amount_happen_adjust - obj.amount_payed - obj.amount_payed_adjust;

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

                cn.Update(obj);

                UpdateLogisticsEndAmount(obj.day, obj.amount_end);

                UpdateLogisticsWaitPay(obj.day.AddDays(1),userid,username,cn);

                cn.Close();
                cn.Dispose();
            }
            catch (Exception ex)
            {
                
            }
        }

        /// <summary>
        /// 更新物流供应商的付款金额
        /// </summary>
        /// <param name="day">日期</param>
        /// <returns></returns>
        public string UpdateLogisticPayed(DateTime day)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                //已付款
                var sqlpay = $"select sum(if (cashier_type = 2,-cashier_paymoneyrmb,cashier_paymoneyrmb)) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type in (1, 2) and cashier_status = 1 and sourcecode in ('newCost', 'oldbuy') and type_name = '销售费用/物流费' and cashier_time>='{day.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{day.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'";
                var hl_amount_pay = cn.QueryFirstOrDefault<decimal?>(sqlpay, null, null, 2 * 60) ?? 0;

                //更新每日发生额 头程物流和尾程物流的付款额
                cn.Execute("update dc_daily_happen_amount set hl_amount_pay="+hl_amount_pay+ ",tl_amount_pay="+hl_amount_pay+ " where day='"+day.ToString("yyyy-MM-dd")+"';");

                var result = AddLogisticsWaitPay(day, hl_amount_pay);

                return result;
            }
        }

        /// <summary>
        /// 增加物流供应商应付账款记录
        /// </summary>
        /// <param name="date">日期</param>
        /// <param name="payamount">付款额</param>
        /// <returns></returns>
        public string AddLogisticsWaitPay(DateTime date,decimal payamount)
        {
            try
            {
                var sql = "select * from dc_daily_lg_waitpay where day='" + date.ToString("yyyy-MM-dd") + "'";
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }

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

                    if (obj != null)
                    {
                        obj.amount_payed = payamount;
                        obj.amount_end = obj.amount_start + obj.amount_happen + obj.amount_happen_adjust - obj.amount_payed - obj.amount_payed_adjust;
                        obj.lastupdatetime = DateTime.Now;
                        obj.lastupdateuserid = 0;
                        obj.lastupdateusername = "admin";
                        cn.Update<dc_daily_lg_waitpay>(obj);
                    }
                    else
                    {
                        sql = "select * from dc_daily_lg_waitpay where day='" + date.AddDays(-1).ToString("yyyy-MM-dd") + "'";

                        var objPre = cn.QueryFirstOrDefault<dc_daily_lg_waitpay>(sql);

                        obj = new dc_daily_lg_waitpay
                        {
                            amount_start = (objPre != null ? objPre.amount_end : 0),
                            amount_happen = 0,
                            amount_happen_adjust = 0,
                            amount_payed = payamount,
                            amount_payed_adjust = 0,

                            createtime = DateTime.Now,
                            lastupdatetime = DateTime.Now,
                            lastupdateuserid = 0,
                            lastupdateusername = "admin",
                            day = DateTime.Parse(date.ToString("yyyy-MM-dd"))
                        };

                        obj.amount_end = obj.amount_start + obj.amount_happen + obj.amount_happen_adjust - obj.amount_payed - obj.amount_payed_adjust;
                        cn.Insert<dc_daily_lg_waitpay>(obj);
                    }

                    UpdateLogisticsWaitPay(date, 0, "admin", cn);
                    UpdateLogisticsEndAmount(date, obj.amount_end);

                }

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

        /// <summary>
        /// 更新资产负债表的物流应付款额
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="amount">期末金额</param>
        /// <returns></returns>
        public string UpdateLogisticsEndAmount(DateTime day,decimal amount)
        {
            var url = "http://fee.bailuntec.com/balanceSheet/updateSheetLogisticsSupplier?date="+day.ToString("yyyy-MM-dd")+"&logisticsPayableSum="+amount;

            var result = Common.HttpHelper.NetHelper.Request(url);

            var json = Newtonsoft.Json.Linq.JObject.Parse(result);
            if (json["success"].ToString().ToLower() == "true")
            {
                return "";
            }
            else
            {
                return json["message"].ToString();
            }
        }

        /// <summary>
        /// 更新资产负债表的物流应付款额
        /// </summary>
        /// <param name="day">日期</param>
        /// <param name="amount">金额</param>
        /// <param name="paramname">参数名</param>
        /// <returns></returns>
        public string UpdateLogisticsEndAmount(DateTime day, decimal amount,string paramname)
        {
            var url = "http://fee.bailuntec.com/balanceSheet/updateSheetLogisticsSupplier?date=" + day.ToString("yyyy-MM-dd") + "&"+ paramname+"=" + amount;

            var result = Common.HttpHelper.NetHelper.Request(url);

            var json = Newtonsoft.Json.Linq.JObject.Parse(result);
            if (json["success"].ToString().ToLower() == "true")
            {
                return "";
            }
            else
            {
                return json["message"].ToString();
            }
        }

        /// <summary>
        /// 获取固定时间的资产负债表明细
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<dc_balance_sheet> ListBalanceSheet(DateTime start, DateTime end)
        {

            var dt = new List<string>();
            dt.Add(start.ToString("yyyy-MM-dd"));
            dt.Add(end.ToString("yyyy-MM-dd"));

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

                var sql = $"select * from dc_balance_sheet where statistical_time in ('{start.ToString("yyyy-MM-dd")}','{end.ToString("yyyy-MM-dd")}') order by statistical_time";
                var list = new List<dc_balance_sheet>();
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

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

                if (list.Count < 2)
                {
                    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 = 0,
                                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,

                                amount_borrowed = 0

                            };

                            list.Add(m);
                        }
                    }
                }

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

            }
        }




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


        /// <summary>
        /// 管理成本列表（从数据中心表获取数据）
        /// </summary>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <returns></returns>
        public List<Models.dc_base_finance_managecost> ListFinanceManageCost(DateTime start,DateTime end,string feesupertype,string feesubtype,string departmentname, int? paycompanyid)
        {
            var sqlparam = new DynamicParameters();
            var list = new List<Models.dc_base_finance_managecost>();

            var sql = $"select * from dc_base_finance_managecost where pay_time>='{start.ToString("yyyy-MM-dd")}' and pay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

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

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

            if (!string.IsNullOrEmpty(departmentname))
            {
                if (departmentname == "四千里主体")
                {
                    //72, 67, 58, 54, 78
                    sql += " and company_value in (72, 67, 58, 54, 78)";
                }
                else
                {
                    sql += " and department_name=@department_name";
                    sqlparam.Add("department_name", departmentname);
                }
                
            }


            if (paycompanyid.HasValue && paycompanyid.Value > 0)
            {
                if (paycompanyid.Value == 53)
                {
                    sql += " and company_value in (53,59,60,61,66)";
                }
                else if (paycompanyid.Value == 54)  //四千里
                {
                    sql += " and company_value in (72, 67, 58, 54, 78)";
                }
                else
                {
                    sql += " and company_value=" + paycompanyid.Value;
                }
            }

            

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

                list = cn.Query<dc_base_finance_managecost>(sql,sqlparam,null,true,2*60).ToList();

                foreach(var item in list)
                {
                    if(item.manage_cost_type==2)
                    {
                        item.amount = -(item.amount);
                        item.amount_rmb = -(item.amount_rmb);
                    }
                }

                return list.ToList();
            }
        }

        /// <summary>
        /// 付现管理列表
        /// </summary>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="ismanager">是否管理成本</param>
        /// <returns></returns>
        public List<Models.dc_base_finance_fee> ListFinanceFee(DateTime start, DateTime end, string feesupertype, string feesubtype, string departmentname, int? paycompanyid,int? ismanager)
        {
            var sqlparam = new DynamicParameters();
            var list = new List<Models.dc_base_finance_fee>();

            var sql = $"select * from dc_base_finance_fee where cost_status=4 and (is_lend is null or is_lend=1 or (is_lend=2 and lend_balance>0) or (is_lend=2 and cost_form=1)) and pay_time>='{start.ToString("yyyy-MM-dd")}' and pay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

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

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

            if (!string.IsNullOrEmpty(departmentname))
            {
                if (departmentname == "四千里主体")
                {
                    //72, 67, 58, 54, 78
                    sql += " and company_value in (72, 67, 58, 54, 78)";
                }
                else
                {
                    sql += " and department_name=@department_name";
                    sqlparam.Add("department_name", departmentname);
                }
            }


            if (paycompanyid.HasValue && paycompanyid.Value > 0)
            {
                if (paycompanyid.Value == 53)
                {
                    sql += " and company_value in (53,59,60,61,66)";
                }
                else if (paycompanyid.Value == 54)  //四千里
                {
                    sql += " and company_value in (72, 67, 58, 54, 78)";
                }
                else
                {
                    sql += " and company_value=" + paycompanyid.Value;
                }
            }

            if(ismanager.HasValue)
            {
                sql += " and is_manage_cost="+ismanager.Value;
            }



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

                list = cn.Query<dc_base_finance_fee>(sql, sqlparam, null, true, 2 * 60).ToList();

                foreach (var item in list)
                {
                    if (item.cost_form == 2)
                    {
                        item.amount = -(item.amount);
                        item.amount_rmb = -(item.amount_rmb);
                    }

                    //借支单待还余额
                    if(item.cost_form==3)
                    {
                        if(item.is_lend.HasValue && item.is_lend.Value==2 && ((item.lend_balance??0)>0))  //归还金额
                        {
                            item.amount = -(item.amount);
                            item.amount_rmb = -(item.amount_rmb);
                        }
                        
                    }
                }

                return list.ToList();
            }
        }


        /// <summary>
        /// 获取管理成本的利息支出
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="paycompanyid"></param>
        /// <returns></returns>
        public List<dc_base_finance_interest> ListFinanceInterest(DateTime start, DateTime end, int? paycompanyid)
        {
            var sql = $"select * from dc_base_finance_interest where actual_repay_time>='{start.ToString("yyyy-MM-dd")}' and actual_repay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

            if (paycompanyid.HasValue && paycompanyid.Value > 0)
            {
                sql += " and company_val="+paycompanyid.Value;
            }

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

                var list = cn.Query<dc_base_finance_interest>(sql).ToList();

                return list;
            }

        }

        /// <summary>
        /// 手续费
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="paycompanyid"></param>
        /// <returns></returns>
        public List<dc_base_finance_handlefee> ListFinanceHandleFee(DateTime start, DateTime end, int? paycompanyid)
        {
            var sql = $"select * from dc_base_finance_handlefee where subject_value_from in (1,2,3,5,7,8,11,46,48,50,53,59,60,61,66,69) and has_delete=0 and create_time>='{start.ToString("yyyy-MM-dd")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

            if (paycompanyid.HasValue && paycompanyid.Value > 0)
            {
                sql += " and subject_value_from=" + paycompanyid.Value;
            }

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

                var list = cn.Query<dc_base_finance_handlefee>(sql).ToList();

                return list;
            }
        }


        /// <summary>
        /// 获取资产负债表可视化任务数据
        /// </summary>
        /// <param name="day">日期</param>
        /// <returns></returns>
        public List<mBalanceSheetTask> ListBalanceSheetTask(DateTime day)
        {
            var list = new List<mBalanceSheetTask>();

            var sql = "";

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

                //平台提现在途
                sql = $"select count(id) from dc_withdraw_on_way where statistical_time='{day.ToString("yyyy-MM-dd")}'";

                var result = cn.QueryFirstOrDefault<int>(sql);
                list.Add(new mBalanceSheetTask {
                    name = "平台提现在途",
                    runtime = "00:00",
                    status = result>0?"成功":"异常"
                });


                //第三方平台余额
                sql = $"select * from dc_balance_sheet where statistical_time='{day.ToString("yyyy-MM-dd")}'";
                var objBalanceSheet = cn.QueryFirstOrDefault<dc_balance_sheet>(sql);
                list.Add(new mBalanceSheetTask {
                    name = "第三方平台余额",
                    runtime = "00:00",
                    status = objBalanceSheet!=null && objBalanceSheet.platform_balance.HasValue ? "成功":"异常"
                });


                //银行帐号余额
                list.Add(new mBalanceSheetTask
                {
                    name = "银行帐号余额",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.bank_account_balance.HasValue ? "成功" : "异常"
                });

                //应收账款
                list.Add(new mBalanceSheetTask
                {
                    name = "应收账款",
                    runtime = "01:21",
                    status = objBalanceSheet != null && objBalanceSheet.accounts_receivable!=0 ? "成功" : "异常"
                });

                //预付成品供应商
                list.Add(new mBalanceSheetTask
                {
                    name = "预付成品供应商",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.finished_prepayment.HasValue ? "成功" : "异常"
                });

                //预付半成品供应商
                list.Add(new mBalanceSheetTask
                {
                    name = "预付半成品供应商",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_prepayment.HasValue ? "成功" : "异常"
                });

                //其他应收款
                list.Add(new mBalanceSheetTask
                {
                    name = "其他应收款",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.other_accounts_receivable>0 ? "成功" : "异常"
                });

                //借支金额
                list.Add(new mBalanceSheetTask
                {
                    name = "借支金额",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.amount_borrowed > 0 ? "成功" : "异常"
                });

                //存货
                list.Add(new mBalanceSheetTask
                {
                    name = "存货",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.inventory_amount > 0 ? "成功" : "异常"
                });

                //FBA仓在库
                list.Add(new mBalanceSheetTask
                {
                    name = "FBA仓在库",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.fba_inventory_amount > 0 ? "成功" : "异常"
                });


                //非FBA仓在库
                list.Add(new mBalanceSheetTask
                {
                    name = "非FBA仓在库",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.not_fba_inventory_amount > 0 ? "成功" : "异常"
                });

                //FBA在途(调拨不包含计划)
                list.Add(new mBalanceSheetTask
                {
                    name = "FBA在途(调拨不包含计划)",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.fba_transfer_allot_amount > 0 ? "成功" : "异常"
                });

                //FBA在途(调拨计划)
                list.Add(new mBalanceSheetTask
                {
                    name = "FBA在途(调拨计划)",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.fba_transfer_plan_amount > 0 ? "成功" : "异常"
                });

                //非FBA在途(调拨不包含计划)
                list.Add(new mBalanceSheetTask
                {
                    name = "非FBA在途(调拨不包含计划)",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.not_fba_transfer_allot_amount > 0 ? "成功" : "异常"
                });

                //非FBA在途(调拨计划)
                list.Add(new mBalanceSheetTask
                {
                    name = "非FBA在途(调拨计划)",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.not_fba_transfer_plan_amount > 0 ? "成功" : "异常"
                });

                //半成品在库
                list.Add(new mBalanceSheetTask
                {
                    name = "半成品在库",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_inventory_amount > 0 ? "成功" : "异常"
                });

                //百伦半成品在库
                list.Add(new mBalanceSheetTask
                {
                    name = "百伦半成品在库",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_bailun_inventory_amount > 0 ? "成功" : "异常"
                });

                //哈倪曼半成品在库
                list.Add(new mBalanceSheetTask
                {
                    name = "哈倪曼半成品在库",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_hani_inventory_amount > 0 ? "成功" : "异常"
                });

                //生产库存
                list.Add(new mBalanceSheetTask
                {
                    name = "生产库存",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_prod_inventory_amount > 0 ? "成功" : "异常"
                });

                //存货半成品在途
                list.Add(new mBalanceSheetTask
                {
                    name = "存货半成品在途",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_transfer_amount > 0 ? "成功" : "异常"
                });

                //百伦半成品在途
                list.Add(new mBalanceSheetTask
                {
                    name = "百伦半成品在途",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_bailun_transfer_amount > 0 ? "成功" : "异常"
                });


                //哈倪曼半成品在途
                list.Add(new mBalanceSheetTask
                {
                    name = "哈倪曼半成品在途",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.semi_hani_transfer_amount > 0 ? "成功" : "异常"
                });

                
                //短期借款
                list.Add(new mBalanceSheetTask
                {
                    name = "短期借款",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.short_term_borrow > 0 ? "成功" : "异常"
                });

                //应付账款
                list.Add(new mBalanceSheetTask
                {
                    name = "应付账款",
                    runtime = "00:00",
                    status = objBalanceSheet != null && objBalanceSheet.accounts_payable!=0 ? "成功" : "异常"
                });

                //其他应付款
                list.Add(new mBalanceSheetTask
                {
                    name = "其他应付款",
                    runtime = "00:00",
                    status = objBalanceSheet != null? "成功" : "异常"
                });
                
            }


            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 platformname, 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;
            //    }

            //}

            var sql = "select platform_name as platformname,platform_name as platformcode,count(id) as account,currency,sum(totalamount) as amount,sum(availableamount) as userable_amount,sum(frozenmoney) as freeze_amount,currencyrate as exchangerate,sum(totalamount_rmb) as amount_rmb,sum(availableamount_rmb) as userable_amount_rmb,sum(frozenmoney_rmb) as freeze_amount_rmb,day from dc_daily_platform_account_balance where day='" + day.ToString("yyyy-MM-dd") + "' ";

            var sqlparam = new DynamicParameters();
            if (!string.IsNullOrEmpty(platformname))
            {
                sql += " and platform_name=@platformname ";
                sqlparam.Add("platformname", platformname);
            }

            sql += " group by platform_name,currency,day";

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

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

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

            }

            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,sqlparam).AsList();

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

                return list;
            }
        }

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

            if (!string.IsNullOrWhiteSpace(platformname))
            {
                sql += " and platform_name='" + platformname + "' ";
            }
            
            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 platformname,string currency, int page, int pagesize, string order, string sort, ref int total)
        {
            var sql = "select t1.id,t1.day,t1.account,t1.platform_name as platformcode,t1.platform_name as platformname,t1.currency,t1.currencyrate as exchangerate,t1.availableamount as userable_amount,t1.availableamount_rmb as userable_amount_rmb,t1.frozenmoney as freeze_amount,t1.frozenmoney_rmb as freeze_amount_rmb,t1.totalamount as amount,t1.totalamount_rmb as amount_rmb,t1.day as puttime from dc_daily_platform_account_balance t1 where t1.day='" + day.ToString("yyyy-MM-dd") + "'";
            string countsql = "select count(id) from dc_daily_platform_account_balance where day='" + day + "'";


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

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

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


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

            }

            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>(countsql,null,null,2*60);

                return list;
            }

        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <returns></returns>
        public List<string> ListAccountBalancePlatform()
        {
            var sql = $@"select platform_name from dc_daily_platform_account_balance group by platform_name";

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

                return cn.Query<string>(sql).ToList();
            }
        }
        

        /// <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 account='{item.account}' and 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.amount < 0 || item.amount_sales<0 || item.before_balance<0||item.userable_amount<0||item.withdraw_amount<0)
                        //{
                        //    return "金额必须大于0";
                        //}


                        if (m == null)
                        {
                            m = new dc_platform_balance
                            {
                                day = item.day,
                                account = item.account,
                                amount = item.amount,
                                amount_rmb = item.amount_rmb,
                                amount_sales = 0,
                                freeze_amount = item.freeze_amount,
                                freeze_amount_rmb = item.freeze_amount_rmb,

                                fromsource = "",
                                before_balance = 0,
                                createtime = DateTime.Now,
                                createuserid = item.createuserid,
                                createusername = item.createusername,
                                currency = item.currency,
                                delstatus = 0,

                                end_balance = item.amount_rmb,
                                exchangerate = item.exchangerate,
                                notes = item.notes,
                                platformcode = item.platformcode,
                                platformname = item.platformname,
                                puttime = item.puttime,

                                paycompanyid = item.paycompanyid,
                                saleday = null,
                                userable_amount = item.userable_amount,
                                userable_amount_rmb = item.userable_amount_rmb,
                                withdraw_amount = 0,

                            };
                        }
                        else
                        {
                            m.amount = item.amount;
                            m.amount_rmb = item.amount_rmb;
                            m.amount_sales = 0;
                            m.before_balance = 0;

                            m.end_balance = item.amount_rmb;
                            m.exchangerate = item.exchangerate;
                            m.freeze_amount = item.freeze_amount;
                            m.freeze_amount_rmb = item.freeze_amount_rmb;
                            m.fromsource = item.fromsource;
                            m.notes = item.notes;

                            m.paycompanyid = item.paycompanyid;
                            m.platformcode = item.platformcode;
                            m.platformname = item.platformname;
                            m.puttime = item.puttime;
                            m.saleday = null;
                            m.userable_amount = item.userable_amount;
                            m.userable_amount_rmb = item.userable_amount_rmb;
                            m.withdraw_amount = 0;
                        }



                        if (item.id <= 0)
                        {

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

                    if (list.Count > 0)
                    {
                        var sql = $@"select sum(t1.amount_rmb) as amount_rmb from dc_platform_balance t1 
                        join (
                        select max(t1.`day`) day,platformname from dc_platform_balance t1 where t1.delstatus=0 and t1.`day`>='2020-01-01' and t1.`day`<='{list.FirstOrDefault().day.ToString("yyyy-MM-dd")}' group by t1.platformname) t2 on t1.platformname=t2.platformname and t1.day=t2.day";

                        var objAmount = cn.QueryFirstOrDefault<decimal?>(sql);
                        UpdateLogisticsEndAmount(list.FirstOrDefault().day, objAmount ?? 0, "platformBalanceUpdate");
                    }
                    


                    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,string account)
        {
            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 account='{account}' and 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 hq_type!='耗材仓' and statistical_time='" + day.ToString("yyyy-MM-dd") + "'"); //and 2=" + payid);

                var FBABalance_OnWay = cn.QueryFirstOrDefault<decimal?>("select sum(transfer_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(transfer_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 hq_type!='耗材仓' 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="request">分页信息</param>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_semi_stock_prod_daily> ListProductionStock(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_stock_prod_daily where warehouse_name!='哈倪蔓美甲灌装车间' and record_time='" + day.ToString("yyyy-MM-dd") + "'";
                if (!string.IsNullOrEmpty(request.sort))
                {
                    sql += " order by " + request.sort + " " + request.order;
                }

                var obj = cn.Page<dc_semi_stock_prod_daily>(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;
                }

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

                    return list.ToList();
                }
                else
                {
                    var list = cn.Query<dc_short_term_borrow>(sql);// 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();
            }
        }

        /// <summary>
        /// 获取借支单明细
        /// </summary>
        /// <param name="request"></param>
        /// <param name="date"></param>
        /// <param name="companyvalue"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_daily_fee> ListBorrowAmount(BtTableParameter request, DateTime date, int? companyvalue, ref int total)
        {
            //排除4k的主体
            var sql = "select * from dc_daily_fee where company_name not in ('香港四千里数据科技有限公司','YOUNGTOP INTERNATIONAL LIMITED','深圳市四千里数据科技有限公司','4Kmiles tec Limited','广州四千里数据科技有限公司','（英国）4kmiles technologies ltd') and  cost_status>-1 and recoed_time='" + date.ToString("yyyy-MM-dd")+"'";

            if (companyvalue.HasValue && companyvalue.Value>0)
            {
                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;
                }

                if (request.limit == 0)
                {
                    var obj = cn.Query<dc_daily_fee>(sql);

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

                    return obj.AsList();
                }
               
            }

        }


        public dc_daily_fee ListBorrowAmountCount(DateTime date, int? companyvalue)
        {
            //只取百伦主体的
            var sql = "select sum((amount - had_pay + ifnull(compensate, 0)) * to_rmb_rate) amount from dc_daily_fee where company_name not in ('香港四千里数据科技有限公司','YOUNGTOP INTERNATIONAL LIMITED','深圳市四千里数据科技有限公司','4Kmiles tec Limited','广州四千里数据科技有限公司','（英国）4kmiles technologies ltd') and  cost_status>-1 and recoed_time='" + date.ToString("yyyy-MM-dd") + "'";

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

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

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

                return obj;
            }
        }


        /// <summary>
        /// 获取应收账款明细汇总金额
        /// </summary>
        /// <param name="date">日期</param>
        /// <param name="companyvalue">付款主体</param>
        /// <returns></returns>
        public Tuple<decimal, decimal> ReceivablesCount(DateTime date)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select sum(case when (platform != 'Ebay' and platform != 'shopify') then amount_end else -amount_end end) as amount from dc_daily_receivable where day = '{date.ToString("yyyy-MM-dd")}'";
                var objRec = cn.QueryFirstOrDefault<decimal?>(sql);

                //sql = $"select sum((amount - ifnull(had_pay, 0) + ifnull(compensate, 0)) * to_rmb_rate) from dc_daily_fee where recoed_time = '{date.ToString("yyyy-MM-dd")}' and company_value in (53,3,5,60,66,59,50,8,1,61,2,70)";
                //var objBorr = cn.QueryFirstOrDefault<decimal?>(sql);

                var objHander = new BalanceSheetServices().GetCount("accounts_receivable_import", date);
                
                return new Tuple<decimal, decimal>(objRec ?? 0, objHander);
            }
        }

        /// <summary>
        /// 获取平台应收账款明细
        /// </summary>
        /// <param name="request"></param>
        /// <param name="date"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_daily_receivable> ListReceivables(BtTableParameter request, DateTime date,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select (case when (platform != 'Ebay' and platform != 'shopify') then amount_end else -amount_end end) as amount_end,day,platform from dc_daily_receivable where day = '{date.ToString("yyyy-MM-dd")}'";

                var obj = cn.Page<dc_daily_receivable>(request.pageIndex, request.limit, sql, ref total, null, null, 2 * 60).ToList();

                return obj;
            }
        }

        /// <summary>
        /// 借支单明细
        /// </summary>
        /// <param name="request"></param>
        /// <param name="date"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_daily_fee> ListBorrow(BtTableParameter request, DateTime date, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select cost_no,company_name,create_username,((amount - ifnull(had_pay, 0) + ifnull(compensate, 0)) * to_rmb_rate) as amount,recoed_time from dc_daily_fee where is_lend=1 and lend_balance>0 and recoed_time = '{date.ToString("yyyy-MM-dd")}' and company_value not in (78,72,71,67,58,54)";

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

                return obj;
            }
        }

        /// <summary>
        /// 获取生产在途库存明细
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="date">统计日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<mPurchaseOnWay> ListProductOnway(BtTableParameter request,DateTime date,ref int total)
        {
            var sql = $@"select dc0.unit_price,dc0.quantity_purchase,dc0.bailun_sku,dc0.warehouse_code,t3.warehouse_name,dc1.buyer_name,dc0.record_time from dc_daily_transit dc0 join dc_base_sku dc1 on dc0.bailun_sku = dc1.bailun_sku and dc1.buyer_name in ('张莹霞', '张莹霞1') left join dc_base_warehouse t3 on t3.warehouse_code=dc0.warehouse_code where dc0.record_time = '{date.ToString("yyyy-MM-dd")}' and dc0.quantity_purchase>0";

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

                if (request.limit > 0)
                {
                    var obj = cn.Page<mPurchaseOnWay>(request.pageIndex, request.limit, sql, ref total);


                    return obj.ToList();
                }
                else
                {
                    var obj = cn.Query<mPurchaseOnWay>(sql);

                    return obj.ToList();
                }
            }
        }

        /// <summary>
        /// 自产采购发货在途明细
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="date">统计日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_daily_purchase_semi_deliveronway> ListSelfProductShippingOnway(BtTableParameter request, DateTime date, ref int total)
        {
            var sql = $@"select t1.*,t2.buyer_name from dc_daily_purchase_semi_deliveronway t1 
                        join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        where t1.day='{date.ToString("yyyy-MM-dd")}'";

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

                if (request.limit > 0)
                {
                    var obj = cn.Page<dc_daily_purchase_semi_deliveronway>(request.pageIndex, request.limit, sql, ref total);


                    return obj.ToList();
                }
                else
                {
                    var obj = cn.Query<dc_daily_purchase_semi_deliveronway>(sql);

                    return obj.ToList();
                }
            }
        }



        #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,if(cashier_type=2,-paymoney_rmb,paymoney_rmb) paymoney_rmb,if(cashier_type=2,-cashier_paymoneyrmb,cashier_paymoneyrmb) cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type in (1,2) 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(if(cashier_type=2,-paymoney_rmb,paymoney_rmb)) paymoney_rmb,sum(if(cashier_type=2,-cashier_paymoneyrmb,cashier_paymoneyrmb)) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type in (1,2) 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,if(cashier_type=2,-paymoney_rmb,paymoney_rmb) paymoney_rmb,if(cashier_type=2,-cashier_paymoneyrmb,cashier_paymoneyrmb) cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type in (1,2) 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;
            }
        }

        /// <summary>
        /// 获取物流费列表
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public List<Models.Api.mLogisticFee> ListLogisticFee(string url)
        {
            var list = new List<Models.Api.mLogisticFee>();
            var result = Common.HttpHelper.NetHelper.Request(url);
            if (!string.IsNullOrEmpty(result))
            {
                var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.Api.ListLogisticFee>(result);
                if (obj != null)
                {
                    list = obj.data;
                }
            }

            return list;
        }

        /// <summary>
        /// 物流费用
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<Models.dc_base_finance_logistics> ListLogisticFee(DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();
            var list = new List<Models.dc_base_finance_logistics>();

            var sql = $"select * from dc_base_finance_logistics where pay_time>='{start.ToString("yyyy-MM-dd")}' and pay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

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

                list = cn.Query<dc_base_finance_logistics>(sql, sqlparam, null, true, 2 * 60).ToList();
                return list.ToList();
            }
        }


        #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_logistics*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_logistics > 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_logistics*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_logistics > 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_logistics*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 进销存

        #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="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">期初日期</param>
        /// <param name="end">期末日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_semi_daily_purchase_sell_stock> ListSemiWHPurchaseSellStock(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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_daily_purchase_sell_stock ListSemiWHPurchaseSellStockCount(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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_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_semi_daily_purchase_sell_stock> ListSemiSkuPurchaseSellStock(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_semi_daily_purchase_sell_stock t1 
                        ";
            var sqlcount = $@"select count(*) from (select distinct t1.bailun_sku,t1.warehouse_code from dc_semi_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_semi_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_semi_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_semi_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_semi_daily_purchase_sell_stock>(sql + " limit " + ((parameter.pageIndex - 1) * parameter.limit + "," + parameter.limit), sqlparam, null, true, 2 * 60);

                return obj.ToList();
            }
        }

        #endregion

        #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 "找不到该月份的利润数据。";
                }

                #region 赋值

                if (m.cost_sale_.HasValue)
                {
                    obj.cost_sale = (obj.cost_sale - obj.cost_sale_) + m.cost_sale_.Value;

                    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 = (obj.ls_head_cost - obj.ls_head_cost_) + m.ls_head_cost_.Value;

                    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 = (obj.ls_tail_cost - obj.ls_tail_cost_) + m.ls_tail_cost_.Value;

                    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.manager_cost_meijiadian.HasValue)
                {
                    obj.manager_cost_meijiadian = m.manager_cost_meijiadian.Value;
                }

                //Add by Allan at 20200923,for 增加管理成本-杨杉、管理成本-信荟蓝
                if(m.manager_cost_yangshan.HasValue)
                {
                    obj.manager_cost_yangshan = m.manager_cost_yangshan.Value;
                }
                if(m.manager_cost_xinhuilan.HasValue)
                {
                    obj.manager_cost_xinhuilan = m.manager_cost_xinhuilan.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 = obj.pt_platform_ad_subscribe_fee - obj.pt_platform_ad_subscribe_fee_ + m.pt_platform_ad_subscribe_fee_.Value;

                    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 = obj.pt_platform_logistics_fee - obj.pt_platform_logistics_fee_ + m.pt_platform_logistics_fee_.Value;

                    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_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_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.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_meijiadian+obj.manager_cost_yangshan+obj.manager_cost_xinhuilan;

                //管理成本合计=直接管理成本+销售相关的管理成本
                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;

                #endregion

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

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

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

                var sql_update = "update dc_month_sales_profit set ";
                var obj = cn.QueryFirstOrDefault<dc_month_sales_profit>("select * from dc_month_sales_profit where isedit=1 and month='" + m.month + "'");

                if(obj==null)
                {
                    obj = new dc_month_sales_profit {
                        month = m.month,
                        amount_sales = 0,
                        amount_sales_dianzi = 0,
                        amount_sales_fuzhuang = 0,
                        amount_sales_jiaju = 0,
                        amount_sales_jingyou = 0,
                        amount_sales_meirongmj = 0,

                        actual_profit = 0,
                        amount_withdraw = 0,
                        fee_platform_and_refund = 0,
                        fee_fba = 0,
                        fee_finance = 0,
                        fee_logistics_direct = 0,
                        fee_logistics_first = 0,
                        fee_logistics_tail = 0,
                        fee_logistics_storage = 0,
                        fee_paycash_sales = 0,
                        fee_platform = 0,
                        fee_refund = 0,
                        fee_sales_count = 0,
                        fee_storage = 0,
                        fee_storage_incidentals = 0,

                        fee_ad = 0,


                        cost_fuzhuang = 0,
                        cost = 0,
                        cost_dianzi = 0,
                        cost_jiaju = 0,
                        cost_jingyou = 0,
                        cost_meirongmj = 0,
                        createtime = DateTime.Now,
                        managercost_chengpincang = 0,
                        managercost_count = 0,
                        managecost_bl_gz = 0,
                        managecost_bl_xg = 0,
                        managecost_meijia = 0,
                        managercost_yangshan = 0,
                        managercost_dizhi = 0,
                        managercost_meijiashengchan = 0,

                        dividend = 0,
                        incoming_non_operating = 0,
                        incoming_other = 0,
                        incoming_refundtax = 0,
                        isedit = 1,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = uid,
                        lastupdateusername = username,
                        profit_sales = 0,
                        pay_non_operating = 0,
                        rate_profit_sales = 0,
                        profit = 0,
                        profit_balance = 0,

                        rate_profit = 0,
                        rate_profit_actual = 0,
                        amount_sales_other = 0,
                        cost_other = 0,
                        managercost_xinhuilan = 0,

                        impairment_assets = 0,
                    };
                }

                #region 赋值


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

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

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

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

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

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

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

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

                if(m.cost_meirongmj.HasValue)
                {
                    obj.cost_meirongmj = m.cost_meirongmj.Value;
                }
                
                if(m.cost_fuzhuang.HasValue)
                {
                    obj.cost_fuzhuang = m.cost_fuzhuang.Value;
                }
                
                if(m.cost_other.HasValue)
                {
                    obj.cost_other = m.cost_other.Value;
                }

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

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

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

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

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

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

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

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

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


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

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


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

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

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

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

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

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

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


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

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

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

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

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

                if(m.impairment_assets.HasValue)
                {
                    obj.impairment_assets = m.impairment_assets;
                }


                //销售额=家居用品类+服装类+电子产品类+精油类+美容美甲类+其他
                obj.amount_sales = obj.amount_sales_dianzi + obj.amount_sales_fuzhuang + obj.amount_sales_jiaju + obj.amount_sales_jingyou + obj.amount_sales_meirongmj+obj.amount_sales_other;


                //产品成本=家居用品类+服装类+电子产品类+精油类+美容美甲类+其他类型成本
                obj.cost = obj.cost_dianzi + obj.cost_fuzhuang + obj.cost_jiaju + obj.cost_jingyou + obj.cost_meirongmj+obj.cost_other;

                //平台扣费及退款
                obj.fee_platform_and_refund = obj.fee_platform + obj.fee_fba + obj.fee_refund+obj.fee_ad;

                //物流仓储费用 = 头程运输+直邮物流费+尾程物流费+海外仓仓储+海外仓杂费
                obj.fee_logistics_storage = obj.fee_logistics_first + obj.fee_logistics_direct+obj.fee_logistics_tail + obj.fee_storage+ obj.fee_storage_incidentals;

                //销售费用合计=平台扣费及退款+物流仓储费用+付现销售费用
                obj.fee_sales_count = obj.fee_platform_and_refund + obj.fee_logistics_storage + obj.fee_paycash_sales;

                //销售利润=销售额-产品成本-销售费用合计
                obj.profit_sales = obj.amount_sales - obj.cost - obj.fee_sales_count;

                //销售毛利率=销售利润/销售额
                obj.rate_profit_sales = obj.amount_sales > 0 ? obj.profit_sales / obj.amount_sales : 0;

                //管理成本=香港百伦+广州百伦+美容美甲店+杨杉+信荟蓝+成品仓+迪致+广州美甲生产仓
                obj.managercost_count = obj.managecost_bl_gz + obj.managecost_bl_xg + obj.managecost_meijia + obj.managercost_chengpincang + obj.managercost_xinhuilan + obj.managercost_yangshan+obj.managercost_dizhi+obj.managercost_meijiashengchan;

                //营业利润=销售利润-管理成本合计-财务费用
                obj.profit = obj.profit_sales - obj.managercost_count - obj.fee_finance;

                //营业毛利率=营业利润/销售额
                obj.rate_profit = obj.amount_sales > 0 ? obj.profit / obj.amount_sales : 0;

                //实际利润=营业利润+其他收入+营业外收入-营业外支出-资产减值
                obj.actual_profit = obj.profit + obj.incoming_other + obj.incoming_non_operating - obj.pay_non_operating-(obj.impairment_assets??0);

                //利润结余=实际利润-分红
                obj.profit_balance = obj.actual_profit - obj.dividend;

                //净利率 = 利润结余/销售额
                obj.rate_profit_actual = obj.amount_sales > 0 ? obj.profit_balance / obj.amount_sales : 0;

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

                #endregion

                if (obj.id>0)
                {
                    var result = cn.Update(obj);
                    return result > 0 ? "" : "更新失败，请重试";
                }
                else
                {
                    var result = cn.Insert(obj);
                    return (result ?? 0) > 0 ? "" : "编辑失败";
                }
                
            }
        }

        /// <summary>
        /// 月度利润报表 数据说明
        /// </summary>
        /// <param name="start">开始月份</param>
        /// <param name="end">结束月份</param>
        /// <returns></returns>
        public List<dc_month_sale_profit_description> ListMonthSaleProfitDescription(string start, string end)
        {
            var sql = $"select * from dc_month_sale_profit_description 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_description>(sql, null, null, true, 2 * 60);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 更新数据描述
        /// </summary>
        /// <param name="m"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string SaveMonthSaleProfitDescription(mMonthSaleProfitDesc_Input m, int uid, string username)
        {
            var sql = $"select * from dc_month_sale_profit_description where month='{m.month}'";

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

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

                if (obj == null)
                {
                    obj = new dc_month_sale_profit_description {
                        month = m.month,
                        createtime = DateTime.Now,
                        
                        actual_profit = "",
                        amount_sale = "",
                        balance_of_profit = "",
                        business_profit = "",
                        business_profit_rate = "",
                        cost_sale = "",
                        direct_manager_cost = "",
                        finance_fee = "",
                        handlingcharges = "",
                        ls_count = "",
                        ls_head_cost = "",
                        ls_oversea_storage = "",
                        ls_platform_operation_fee = "",


                        ls_tail_cost = "",
                        manager_cost_count = "",
                        manager_cost_finish_wh = "",
                        manager_cost_gzbailun = "",
                        manager_cost_hkbailun = "",
                        manager_cost_meijiadian = "",

                        manager_cost_yangshan = "",
                        manager_cost_xinhuilan = "",

                        sale_manager_cost = "",
                        sale_fee_count = "",
                        sale_profit = "",
                        sale_profit_rate = "",
                        participation_in_profit = "",
                        payfor_platform_related_fee = "",


                        pt_count = "",
                        pt_ebay_postingfee = "",
                        pt_incoming = "",
                        pt_paypal_fee = "",
                        pt_platformfee = "",
                        pt_platform_ad_subscribe_fee = "",
                        pt_platform_logistics_fee = "",
                        pt_platform_storage_fee = "",
                        pt_refund_amount = "",
                        nonbusiness_pay = "",

                        nonbusiness_income = "",
                        other_incoming = "",
                        
                    };
                }

                if (obj.actual_profit != m.actual_profit && m.actual_profit!=null)
                {
                    obj.actual_profit = m.actual_profit;
                }

                else if (obj.amount_sale != m.amount_sale && m.amount_sale != null) {
                    obj.amount_sale = m.amount_sale;
                }
                
                else if(obj.balance_of_profit != m.balance_of_profit && m.balance_of_profit != null)
                {
                    obj.balance_of_profit = m.balance_of_profit;
                }

                else if (obj.business_profit != m.business_profit_rate && m.business_profit_rate != null) {
                    obj.business_profit = m.business_profit_rate;
                }

                else if (obj.cost_sale != m.cost_sale && m.cost_sale != null) {
                    obj.cost_sale = m.cost_sale;
                }

                else if (obj.direct_manager_cost != m.direct_manager_cost && m.direct_manager_cost != null) {
                    obj.direct_manager_cost = m.direct_manager_cost;
                }

                else if (obj.finance_fee != m.finance_fee && m.finance_fee != null) {
                    obj.finance_fee = m.finance_fee;
                }

                else if (obj.handlingcharges != m.handlingcharges && m.handlingcharges != null) {
                    obj.handlingcharges = m.handlingcharges;
                }
                
                else if (obj.ls_count != m.ls_count && m.ls_count != null) {
                    obj.ls_count = m.ls_count;
                }

                else if (obj.ls_head_cost != m.ls_head_cost && m.ls_head_cost != null) {
                    obj.ls_head_cost = m.ls_head_cost;
                }
                

                else if (obj.ls_oversea_storage != m.ls_oversea_storage && m.ls_oversea_storage != null)
                {
                    obj.ls_oversea_storage = m.ls_oversea_storage;
                }
                

                else if (obj.ls_platform_operation_fee != m.ls_platform_operation_fee && m.ls_platform_operation_fee != null)
                {
                    obj.ls_platform_operation_fee = m.ls_platform_operation_fee;
                }
                

                else if (obj.ls_tail_cost != m.ls_tail_cost && m.ls_tail_cost != null)
                {
                    obj.ls_tail_cost = m.ls_tail_cost;
                }
                

                else if (obj.manager_cost_count != m.manager_cost_count && m.manager_cost_count != null)
                {
                    obj.manager_cost_count = m.manager_cost_count;
                }
                

                else if (obj.manager_cost_finish_wh != m.manager_cost_finish_wh && m.manager_cost_finish_wh != null)
                {
                    obj.manager_cost_finish_wh = m.manager_cost_finish_wh;
                }
                

                else if (obj.manager_cost_gzbailun != m.manager_cost_gzbailun && m.manager_cost_gzbailun != null)
                {
                    obj.manager_cost_gzbailun = m.manager_cost_gzbailun;
                }
                

                else if (obj.manager_cost_hkbailun != m.manager_cost_hkbailun && m.manager_cost_hkbailun != null)
                {
                    obj.manager_cost_hkbailun = m.manager_cost_hkbailun;
                }
                

                else if (obj.manager_cost_meijiadian != m.manager_cost_meijiadian && m.manager_cost_meijiadian != null)
                {
                    obj.manager_cost_meijiadian = m.manager_cost_meijiadian;
                }

                else if(obj.manager_cost_yangshan!=m.manager_cost_yangshan && m.manager_cost_yangshan!=null)
                {
                    obj.manager_cost_yangshan = m.manager_cost_yangshan;
                }

                else if(obj.manager_cost_xinhuilan!=m.manager_cost_xinhuilan && m.manager_cost_xinhuilan!=null)
                {
                    obj.manager_cost_xinhuilan = m.manager_cost_xinhuilan;
                }
                

                else if (obj.nonbusiness_income != m.nonbusiness_income && m.nonbusiness_income != null)
                {
                    obj.nonbusiness_income = m.nonbusiness_income;
                }
                
                else if (obj.nonbusiness_pay != m.nonbusiness_pay && m.nonbusiness_pay != null)
                {
                    obj.nonbusiness_pay = m.nonbusiness_pay;
                }
                

                else if (obj.other_incoming != m.other_incoming && m.other_incoming != null)
                {
                    obj.other_incoming = m.other_incoming;
                }
                
                else if (obj.participation_in_profit != m.participation_in_profit && m.participation_in_profit != null)
                {
                    obj.participation_in_profit = m.participation_in_profit;
                }
                
                else if (obj.payfor_platform_related_fee != m.payfor_platform_related_fee && m.payfor_platform_related_fee != null)
                {
                    obj.payfor_platform_related_fee = m.payfor_platform_related_fee;
                }
                
                else if (obj.pt_count != m.pt_count && m.pt_count != null)
                {
                    obj.pt_count = m.pt_count;
                }
                
                else if (obj.pt_ebay_postingfee != m.pt_ebay_postingfee && m.pt_ebay_postingfee != null)
                {
                    obj.pt_ebay_postingfee = m.pt_ebay_postingfee;
                }
                
                else if (obj.pt_incoming != m.pt_incoming && m.pt_incoming != null)
                {
                    obj.pt_incoming = m.pt_incoming;
                }
                
                else if (obj.pt_paypal_fee != m.pt_paypal_fee && m.pt_paypal_fee != null)
                {
                    obj.pt_paypal_fee = m.pt_paypal_fee;
                }
                
                else if (obj.pt_platformfee != m.pt_platformfee && m.pt_platformfee != null)
                {
                    obj.pt_platformfee = m.pt_platformfee;
                }
                

                else if (obj.pt_platform_ad_subscribe_fee != m.pt_platform_ad_subscribe_fee && m.pt_platform_ad_subscribe_fee != null)
                {
                    obj.pt_platform_ad_subscribe_fee = m.pt_platform_ad_subscribe_fee;
                }
                
                else if (obj.pt_platform_logistics_fee != m.pt_platform_logistics_fee && m.pt_platform_logistics_fee != null)
                {
                    obj.pt_platform_logistics_fee = m.pt_platform_logistics_fee;
                }
                
                else if (obj.pt_platform_storage_fee != m.pt_platform_storage_fee && m.pt_platform_storage_fee != null)
                {
                    obj.pt_platform_storage_fee = m.pt_platform_storage_fee;
                }
                
                else if (obj.pt_refund_amount != m.pt_refund_amount && m.pt_refund_amount != null)
                {
                    obj.pt_refund_amount = m.pt_refund_amount;
                }
                
                else if (obj.sale_fee_count != m.sale_fee_count && m.sale_fee_count != null)
                {
                    obj.sale_fee_count = m.sale_fee_count;
                }
                
                else if (obj.sale_manager_cost != m.sale_profit && m.sale_profit != null)
                {
                    obj.sale_manager_cost = m.sale_profit;
                }
                
                else if (obj.sale_profit_rate != m.sale_profit_rate && m.sale_profit_rate != null)
                {
                    obj.sale_profit_rate = m.sale_profit_rate;
                }

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

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

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

        /// <summary>
        /// 新月度利润报表 数据说明
        /// </summary>
        /// <param name="start">开始月份</param>
        /// <param name="end">结束月份</param>
        /// <returns></returns>
        public List<dc_month_sales_profit_description> ListMonthSaleProfitNewDescription(string start, string end)
        {
            var sql = $"select * from dc_month_sales_profit_description 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_sales_profit_description>(sql, null, null, true, 2 * 60);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 更新新月销售利润数据描述
        /// </summary>
        /// <param name="m"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string SaveMonthSaleProfitNewDescription(mMonthSalesProfitDesc_Input m, int uid, string username)
        {
            var sql = $"select * from dc_month_sales_profit_description where month='{m.month}'";

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

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

                if (obj == null)
                {
                    obj = new dc_month_sales_profit_description
                    {
                        month = m.month,
                        createtime = DateTime.Now,

                        actual_profit = "",
                        amount_sales = "",
                        amount_sales_dianzi = "",
                        amount_sales_fuzhuang="",
                        amount_sales_jiaju="",
                        amount_sales_jingyou="",
                        amount_sales_meirongmj="",
                        amount_sales_other="",
                        amount_withdraw="",
                        
                        fee_ad = "",
                        fee_fba = "",
                        fee_finance = "",
                        fee_logistics_direct = "",
                        fee_logistics_first = "",
                        fee_logistics_storage="",
                        fee_logistics_tail="",
                        fee_paycash_sales = "",
                        fee_platform = "",
                        fee_platform_and_refund="",
                        fee_refund = "",
                        fee_sales_count = "",
                        fee_storage = "",
                        fee_storage_incidentals = "",
                        
                        cost = "",
                        cost_fuzhuang = "",
                        cost_dianzi = "",
                        cost_jiaju = "",
                        cost_jingyou = "",
                        cost_meirongmj = "",
                        cost_other = "",

                        managercost_chengpincang = "",
                        managecost_bl_gz = "",
                        managecost_bl_xg = "",
                        managecost_meijia = "",
                        managercost_count = "",
                        managercost_dizhi = "",
                        managercost_meijiashengchan = "",
                        managercost_xinhuilan = "",
                        managercost_yangshan = "",
                        
                        pay_non_operating = "",
                        profit = "",
                        profit_balance = "",
                        profit_sales = "",
                        
                        rate_profit = "",
                        rate_profit_actual = "",
                        rate_profit_sales = "",
                        
                        impairment_assets = "",
                        incoming_non_operating = "",
                        incoming_other = "",
                        incoming_refundtax = "",

                        dividend = "",

                    };
                }

                #region 赋值

                if (obj.actual_profit != m.actual_profit && m.actual_profit != null)
                {
                    obj.actual_profit = m.actual_profit;
                }
                else if(obj.actual_profit != m.actual_profit && m.actual_profit != null)
                {
                    obj.actual_profit = m.actual_profit;
                }
                else if (obj.amount_sales != m.amount_sales && m.amount_sales != null)
                {
                    obj.amount_sales = m.amount_sales;
                }
                else if (obj.amount_sales_dianzi != m.amount_sales_dianzi && m.amount_sales_dianzi != null)
                {
                    obj.amount_sales_dianzi = m.amount_sales_dianzi;
                }
                else if (obj.amount_sales_fuzhuang != m.amount_sales_fuzhuang && m.amount_sales_fuzhuang != null)
                {
                    obj.amount_sales_fuzhuang = m.amount_sales_fuzhuang;
                }
                else if (obj.amount_sales_jiaju != m.amount_sales_jiaju && m.amount_sales_jiaju != null)
                {
                    obj.amount_sales_jiaju = m.amount_sales_jiaju;
                }
                else if (obj.amount_sales_jingyou != m.amount_sales_jingyou && m.amount_sales_jingyou != null)
                {
                    obj.amount_sales_jingyou = m.amount_sales_jingyou;
                }
                else if (obj.amount_sales_meirongmj != m.amount_sales_meirongmj && m.amount_sales_meirongmj != null)
                {
                    obj.amount_sales_meirongmj = m.amount_sales_meirongmj;
                }
                else if (obj.amount_sales_other != m.amount_sales_other && m.amount_sales_other != null)
                {
                    obj.amount_sales_other = m.amount_sales_other;
                }
                else if (obj.amount_withdraw != m.amount_withdraw && m.amount_withdraw != null)
                {
                    obj.amount_withdraw = m.amount_withdraw;
                }
                else if (obj.fee_ad != m.fee_ad && m.fee_ad != null)
                {
                    obj.fee_ad = m.fee_ad;
                }
                else if (obj.fee_fba != m.fee_fba && m.fee_fba != null)
                {
                    obj.fee_fba = m.fee_fba;
                }
                else if (obj.fee_finance != m.fee_finance && m.fee_finance != null)
                {
                    obj.fee_finance = m.fee_finance;
                }
                else if (obj.fee_logistics_direct != m.fee_logistics_direct && m.fee_logistics_direct != null)
                {
                    obj.fee_logistics_direct = m.fee_logistics_direct;
                }
                else if (obj.fee_logistics_first != m.fee_logistics_first && m.fee_logistics_first != null)
                {
                    obj.fee_logistics_first = m.fee_logistics_first;
                }
                else if (obj.fee_logistics_storage != m.fee_logistics_storage && m.fee_logistics_storage != null)
                {
                    obj.fee_logistics_storage = m.fee_logistics_storage;
                }
                else if (obj.fee_logistics_tail != m.fee_logistics_tail && m.fee_logistics_tail != null)
                {
                    obj.fee_logistics_tail = m.fee_logistics_tail;
                }
                else if (obj.fee_paycash_sales != m.fee_paycash_sales && m.fee_paycash_sales != null)
                {
                    obj.fee_paycash_sales = m.fee_paycash_sales;
                }
                else if (obj.fee_platform != m.fee_platform && m.fee_platform != null)
                {
                    obj.fee_platform = m.fee_platform;
                }
                else if (obj.fee_platform_and_refund != m.fee_platform_and_refund && m.fee_platform_and_refund != null)
                {
                    obj.fee_platform_and_refund = m.fee_platform_and_refund;
                }
                else if (obj.fee_refund != m.fee_refund && m.fee_refund != null)
                {
                    obj.fee_refund = m.fee_refund;
                }
                else if (obj.fee_sales_count != m.fee_sales_count && m.fee_sales_count != null)
                {
                    obj.fee_sales_count = m.fee_sales_count;
                }
                else if (obj.fee_storage != m.fee_storage && m.fee_storage != null)
                {
                    obj.fee_storage = m.fee_storage;
                }
                else if (obj.fee_storage_incidentals != m.fee_storage_incidentals && m.fee_storage_incidentals != null)
                {
                    obj.fee_storage_incidentals = m.fee_storage_incidentals;
                }
                else if (obj.cost != m.cost && m.cost != null)
                {
                    obj.cost = m.cost;
                }
                else if (obj.cost_fuzhuang != m.cost_fuzhuang && m.cost_fuzhuang != null)
                {
                    obj.cost_fuzhuang = m.cost_fuzhuang;
                }
                else if (obj.cost_dianzi != m.cost_dianzi && m.cost_dianzi != null)
                {
                    obj.cost_dianzi = m.cost_dianzi;
                }
                else if (obj.cost_jiaju != m.cost_jiaju && m.cost_jiaju != null)
                {
                    obj.cost_jiaju = m.cost_jiaju;
                }
                else if (obj.cost_jingyou != m.cost_jingyou && m.cost_jingyou != null)
                {
                    obj.cost_jingyou = m.cost_jingyou;
                }
                else if (obj.cost_meirongmj != m.cost_meirongmj && m.cost_meirongmj != null)
                {
                    obj.cost_meirongmj = m.cost_meirongmj;
                }
                else if (obj.cost_other != m.cost_other && m.cost_other != null)
                {
                    obj.cost_other = m.cost_other;
                }
                else if (obj.managercost_chengpincang != m.managercost_chengpincang && m.managercost_chengpincang != null)
                {
                    obj.managercost_chengpincang = m.managercost_chengpincang;
                }
                else if (obj.managecost_bl_gz != m.managecost_bl_gz && m.managecost_bl_gz != null)
                {
                    obj.managecost_bl_gz = m.managecost_bl_gz;
                }
                else if (obj.managecost_bl_xg != m.managecost_bl_xg && m.managecost_bl_xg != null)
                {
                    obj.managecost_bl_xg = m.managecost_bl_xg;
                }
                else if (obj.managecost_meijia != m.managecost_meijia && m.managecost_meijia != null)
                {
                    obj.managecost_meijia = m.managecost_meijia;
                }
                else if (obj.managercost_count != m.managercost_count && m.managercost_count != null)
                {
                    obj.managercost_count = m.managercost_count;
                }
                else if (obj.managercost_dizhi != m.managercost_dizhi && m.managercost_dizhi != null)
                {
                    obj.managercost_dizhi = m.managercost_dizhi;
                }
                else if (obj.managercost_meijiashengchan != m.managercost_meijiashengchan && m.managercost_meijiashengchan != null)
                {
                    obj.managercost_meijiashengchan = m.managercost_meijiashengchan;
                }
                else if (obj.managercost_xinhuilan != m.managercost_xinhuilan && m.managercost_xinhuilan != null)
                {
                    obj.managercost_xinhuilan = m.managercost_xinhuilan;
                }
                else if (obj.managercost_yangshan != m.managercost_yangshan && m.managercost_yangshan != null)
                {
                    obj.managercost_yangshan = m.managercost_yangshan;
                }
                else if (obj.pay_non_operating != m.pay_non_operating && m.pay_non_operating != null)
                {
                    obj.pay_non_operating = m.pay_non_operating;
                }
                else if (obj.profit != m.profit && m.profit != null)
                {
                    obj.profit = m.profit;
                }
                else if (obj.profit_balance != m.profit_balance && m.profit_balance != null)
                {
                    obj.profit_balance = m.profit_balance;
                }
                else if (obj.profit_sales != m.profit_sales && m.profit_sales != null)
                {
                    obj.profit_sales = m.profit_sales;
                }
                else if (obj.rate_profit != m.rate_profit && m.rate_profit != null)
                {
                    obj.rate_profit = m.rate_profit;
                }
                else if (obj.rate_profit_actual != m.rate_profit_actual && m.rate_profit_actual != null)
                {
                    obj.rate_profit_actual = m.rate_profit_actual;
                }
                else if (obj.rate_profit_sales != m.rate_profit_sales && m.rate_profit_sales != null)
                {
                    obj.rate_profit_sales = m.rate_profit_sales;
                }
                else if (obj.impairment_assets != m.impairment_assets && m.impairment_assets != null)
                {
                    obj.impairment_assets = m.impairment_assets;
                }
                else if (obj.incoming_non_operating != m.incoming_non_operating && m.incoming_non_operating != null)
                {
                    obj.incoming_non_operating = m.incoming_non_operating;
                }
                else if (obj.incoming_other != m.incoming_other && m.incoming_other != null)
                {
                    obj.incoming_other = m.incoming_other;
                }
                else if (obj.incoming_refundtax != m.incoming_refundtax && m.incoming_refundtax != null)
                {
                    obj.incoming_refundtax = m.incoming_refundtax;
                }
                else if (obj.dividend != m.dividend && m.dividend != null)
                {
                    obj.dividend = m.dividend;
                }

                #endregion

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

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

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


        /// <summary>
        /// 获取月销售利润明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="month">月份</param>
        /// <param name="colval">列名</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_month_sale_profit_details> ListMonthSaleProfitDetail(BtTableParameter parameter, string month, string colval, ref int total,int isnew=0)
        {
            var sql = "select * from dc_month_sale_profit_details where delstatus=0 and isnew="+isnew+" ";
            var sqlparam = new DynamicParameters();

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

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


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

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

                return obj.ToList();

            }

        }

        /// <summary>
        /// 获取月销售利润明细 总计
        /// </summary>
        /// <param name="month">月份</param>
        /// <param name="colval">列名</param>
        /// <returns></returns>
        public dc_month_sale_profit_details ListMonthSaleProfitDetailCount(string month, string colval, int isnew = 0)
        {
            var sql = "select sum(amount) amount from dc_month_sale_profit_details where delstatus=0 and isnew=" + isnew + " ";
            var sqlparam = new DynamicParameters();

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

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

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

                var obj = cn.QueryFirstOrDefault<dc_month_sale_profit_details>(sql,sqlparam);

                return obj;

            }
        }


        /// <summary>
        /// 保存月销售利润明细信息
        /// </summary>
        /// <param name="list"></param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string SaveMonthSaleProfitDetail(List<dc_month_sale_profit_details> list, int uid, string username)
        {
            if (list.Count <= 0)
            {
                return "没有可保存的记录";
            }
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    foreach (var item in list)
                    {
                        item.createtime = DateTime.Now;
                        item.createuserid = uid;
                        item.createusername = username;

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

                        item.delstatus = 0;

                        cn.Insert<dc_month_sale_profit_details>(item);
                    }

                }

                return "";
            }
            catch (Exception ex)
            {
                return "出现异常，异常信息："+ex.Message;
            }
        }

        /// <summary>
        /// 删除明细记录
        /// </summary>
        /// <param name="id">月销售利润明细id</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string DelMonthSaleProfitDetail(int id,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_details>("select * from dc_month_sale_profit_details where delstatus=0 and id=" + id);

                if (obj == null)
                {
                    return "删除失败，找不到该记录。";
                }

                var result = cn.Execute("update dc_month_sale_profit_details set delstatus=1,lastupdatetime='"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+ "',lastupdateuserid="+uid+ ",lastupdateusername='"+username+"' where id="+id);

                if (result > 0)
                {
                    return "";
                }
                else
                {
                    return "删除失败，请重试！";
                }
            }
        }

        /// <summary>
        /// 删除明细记录
        /// </summary>
        /// <param name="id">月销售利润明细id</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string DelMonthSaleProfitDetailBatch(List<int> id, 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_details>("select * from dc_month_sale_profit_details where delstatus=0 and id=" + id);

                //if (obj == null)
                //{
                //    return "删除失败，找不到该记录。";
                //}

                var result = cn.Execute("update dc_month_sale_profit_details set delstatus=1,lastupdatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',lastupdateuserid=" + uid + ",lastupdateusername='" + username + "' where id in (" + string.Join(",",id)+")");

                if (result > 0)
                {
                    return "";
                }
                else
                {
                    return "删除失败，请重试！";
                }
            }
        }

        #endregion

        #region 新月度利润报表

        /// <summary>
        /// 月度利润报表
        /// </summary>
        /// <param name="start">开始月份</param>
        /// <param name="end">结束月份</param>
        /// <returns></returns>
        public List<dc_month_sales_profit> ListMonthSaleProfitNew(string start, string end)
        {
            var sql = $"select * from dc_month_sales_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_read))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }
                }

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

                return obj.ToList();
            }
        }

        /// <summary>
        /// 4K月度利润报表
        /// </summary>
        /// <param name="start">开始月份</param>
        /// <param name="end">结束月份</param>
        /// <returns></returns>
        public List<dc_month_sales_profit_4k> ListMonthSaleProfit_4K(string start, string end)
        {
            var sql = $"select * from dc_month_sales_profit_4k 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_read))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    if (cn.State == ConnectionState.Closed)
                    {
                        cn.Open();
                    }
                }

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

                return obj.ToList();
            }
        }


        /// <summary>
        /// 更新销售利润报表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string UpdateMonthSaleProfit_4K(mMonthSaleProfit4K_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_sales_profit_4k>("select * from dc_month_sales_profit_4k where isedit=1 and month='" + m.month + "'");

                if (obj == null)
                {
                    obj = new dc_month_sales_profit_4k
                    {
                        incoming_operating = 0,
                        incoming_operating_dsp = 0,
                        incoming_operating_fansservice = 0,
                        incoming_operating_mh = 0,
                        incoming_operating_other = 0,
                        incoming_operating_saas = 0,
                        incoming_operating_test = 0,
                        incoming_operating_tp = 0,
                        incoming_out_operating = 0,

                        cost_operating_investment = 0,
                        cost_operating_investment_in = 0,
                        cost_operating = 0,
                        cost_operating_assets_impairment = 0,
                        cost_operating_dspchannel = 0,
                        cost_operating_dspsign = 0,
                        cost_operating_exchange = 0,
                        cost_operating_fair_value_change = 0
                        , cost_operating_finance = 0,
                        cost_operating_it = 0,
                        cost_operating_manager = 0,
                        cost_operating_mh = 0,
                        cost_operating_sales = 0,
                        cost_operating_taxes = 0,
                        cost_operating_tp = 0,
                        cost_out_operating = 0,

                        createtime = DateTime.Now,
                        fee_income_tax = 0,
                        isedit = 1,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = uid,
                        lastupdateusername = username,
                        loss_ondisposal_noncurrent_assets = 0,

                        month = m.month,
                        profit = 0,
                        profit_operating = 0,
                        profit_retained = 0,
                    };
                }

                #region 赋值

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

                //营业收入=SAAS套餐收入+测评收入+DSP服务收入+MH代理收入+代运营收入+粉丝系统服务收入+其他收入
                m.incoming_operating = m.incoming_operating_saas + m.incoming_operating_test + m.incoming_operating_dsp + m.incoming_operating_mh + m.incoming_operating_tp + m.incoming_operating_fansservice + m.incoming_operating_other;

                //营业成本=IT人工成本+DSP渠道成本+DSP签约成本+代运营成本+MH客户成本
                m.cost_operating = m.cost_operating_it + m.cost_operating_dspchannel + m.cost_operating_dspsign + m.cost_operating_tp + m.cost_operating_mh;

                //营业利润=营业收入-营业成本-营业税金及附加-销售费用-管理费用-财务费用
                m.profit_operating = m.incoming_operating - m.cost_operating - m.cost_operating_taxes - m.cost_operating_sales - m.cost_operating_manager - m.cost_operating_finance;

                //利润总额=营业利润+营业外利润-营业务支出
                m.profit = m.profit_operating + m.incoming_out_operating - m.cost_out_operating;

                //净利润=利润总额-所得税费用
                m.profit_retained = m.profit - m.fee_income_tax;

                #endregion

                if (obj.id > 0)
                {
                    var result = cn.Update<dc_month_sales_profit_4k>(obj);
                    return result > 0 ? "" : "更新失败，请重试";
                }
                else
                {
                    var result = cn.Insert<dc_month_sales_profit_4k>(obj);
                    return (result ?? 0) > 0 ? "" : "编辑失败";
                }

            }
        }

        /// <summary>
        /// 获取月销售利润报表的管理成本订单明细
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public List<dc_base_finance_fee> ListMonthProfitFeeOrderDetail(BtTableParameter parameter,DateTime start,DateTime end,string col,ref int total)
        {
            var sql = $"select * from dc_base_finance_fee where cost_status=4 and (is_lend is null or is_lend=1 or (is_lend=2 and lend_balance>0) or (is_lend=2 and cost_form=1)) and pay_time>='{start.ToString("yyyy-MM-dd")}' and pay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";


            if (col.Trim() == "fee_paycash_sales")  //付现 fee_paycash_sales
            {
                sql += @" and (company_name='香港百伦科技有限公司' and type_name != '物流费' && type_name!= '销售费用/物流费') ";
            }

            else if (col.Trim() == "managecost_bl_gz")  //广州百伦
            {
                sql += " and (company_name='广州百伦供应链科技有限公司' and type_name!='出口退税款') ";
            }

            else if (col.Trim() == "managecost_meijia")  //美甲
            {
                //歌戈儿
                sql += " and (company_name like '%歌戈儿%' and (type_name!='销售收入' and type_name!='出口退税款')) ";
            }

            else if (col.Trim() == "managercost_chengpincang")  //成品仓
            {
                sql += " and (company_name in ('广州电子服装仓','阳山仓') and type_name!='出口退税款') ";
            }

            else if (col.Trim() == "managercost_yangshan") //扬杉
            {
                sql += " and (company_name like '%扬杉%'  and type_name!='出口退税款') ";
            }

            else if (col.Trim() == "managercost_xinhuilan") //信荟蓝
            {
                sql += " and (company_name like '%信荟蓝%'  and type_name!='出口退税款') ";
            }

            else if (col.Trim() == "managercost_meijiashengchan") //美甲生产仓
            {
                sql += " and (company_name like '%美甲生产仓%'  and type_name!='出口退税款') ";
            }

            else if (col.Trim() == "managercost_dizhi") //迪致美容
            {
                sql += " and (company_name like '%迪致美容%'  and type_name!='出口退税款') ";
            }

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

                if(parameter.limit>0)
                {
                    var obj = cn.Page<dc_base_finance_fee>(parameter.pageIndex, parameter.limit, sql, ref total);

                    foreach (var item in obj)
                    {
                        if (item.cost_form == 2)
                        {
                            item.amount = -(item.amount);
                            item.amount_rmb = -(item.amount_rmb);
                        }

                        //借支单待还余额
                        if (item.cost_form == 3)
                        {
                            if (item.is_lend.HasValue && item.is_lend.Value == 2 && ((item.lend_balance ?? 0) > 0))  //归还金额
                            {
                                item.amount = -(item.amount);
                                item.amount_rmb = -(item.amount_rmb);
                            }

                        }
                    }

                    return obj.ToList();
                }
                else
                {
                    var obj = cn.Query<dc_base_finance_fee>(sql);

                    foreach (var item in obj)
                    {
                        if (item.cost_form == 2)
                        {
                            item.amount = -(item.amount);
                            item.amount_rmb = -(item.amount_rmb);
                        }

                        //借支单待还余额
                        if (item.cost_form == 3)
                        {
                            if (item.is_lend.HasValue && item.is_lend.Value == 2 && ((item.lend_balance ?? 0) > 0))  //归还金额
                            {
                                item.amount = -(item.amount);
                                item.amount_rmb = -(item.amount_rmb);
                            }

                        }
                    }

                    return obj.ToList();
                }
            }
        }

        /// <summary>
        /// 获取订单明细信息
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="month"></param>
        /// <param name="financecategory"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_month_sales_profit_orderdetail> ListMonthProfitOrderDetail(BtTableParameter parameter,string month,string financecategory,ref int total)
        {
            var sql = $"select t1.*,t2.origin_order_id,t2.bailun_order_id from dc_month_sales_profit_orderdetail t1 join dc_base_oms_sku t2 on t1.orderskuid=t2.id where t1.month='{month}' ";

            if (!string.IsNullOrEmpty(financecategory) && financecategory != "其他")
            {
                sql += $" and t1.financecategoryname like '{financecategory.Replace("产品", "")}%'";
            }
            else if (financecategory == "其他")
            {
                sql += $" and t1.financecategoryname ='' ";
            }

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

                if(parameter.limit>0)
                {
                    var obj = cn.Page<dc_month_sales_profit_orderdetail>(parameter.pageIndex, parameter.limit, sql, ref total);
                    return obj.ToList();
                }
                else
                {
                    return cn.Query<dc_month_sales_profit_orderdetail>(sql).ToList();
                }

            }
        }


        #endregion

        #region 应收账款

        /// <summary>
        /// 平台应收账款明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="platform">平台类型</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_daily_receivable> ListReceivable(BtTableParameter parameter,string platform,DateTime start, DateTime end,int type,ref int total)
        {
            var sql = $"select max(id) id,platform,sum(amount_sale_pay) as amount_sale_pay,sum(amount_shipping) amount_shipping,sum(amount_refund) amount_refund,sum(amount_sale_shipping) amount_sale_shipping,sum(amount_platformfee) amount_platformfee,sum(amount_incoming) amount_incoming,sum(amount_other) amount_other,max(note) note from dc_daily_receivable where day>='{start.ToString("yyyy-MM-dd")}' and day<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";
            var sqlparam = new DynamicParameters();

            var sql_start = $"select * from dc_daily_receivable where day='{start.ToString("yyyy-MM-dd")}'";
            var sql_end = $"select * from dc_daily_receivable where day='{end.ToString("yyyy-MM-dd")}'";

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

            if (type == 1)  //Ebay
            {
                sql += " and platform in ('Ebay','shopify','Etsy') ";
                sql_start += " and platform in ('Ebay','shopify','Etsy') ";
                sql_end += " and platform in ('Ebay','shopify','Etsy') ";
            }
            else if (type == 2) //非Ebay
            {
                sql += " and platform!='Ebay' and platform!='shopify' and platform!='Etsy' ";
                sql_start += " and platform!='Ebay' and platform!='shopify' and platform!='Etsy' ";
                sql_end += " and platform!='Ebay' and platform!='shopify' and platform!='Etsy' ";
            }

            sql += " group by platform";

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

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

                if (obj.Count > 0)
                {
                    //期初
                    var listStart = cn.Query<dc_daily_receivable>(sql_start);   
                    //期末
                    var listEnd = cn.Query<dc_daily_receivable>(sql_end);


                    foreach (var item in obj)
                    {
                        var objStart = listStart.Where(a => a.platform == item.platform).FirstOrDefault();
                        if (objStart != null)
                        {
                            item.amount_start = objStart.amount_start;
                        }

                        var objEnd = listEnd.Where(a => a.platform == item.platform).FirstOrDefault();
                        if (objEnd != null)
                        {
                            item.amount_end = objEnd.amount_end;
                        }
                    }

                }


                return obj;
            }
        }

        /// <summary>
        /// 平台应收账款汇总
        /// </summary>
        /// <param name="platform">平台类型</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public dc_daily_receivable ListReceivableCount(string platform, DateTime start, DateTime end,int type)
        {
            var sql = $"select platform,sum(amount_sale_pay) amount_sale_pay,sum(amount_shipping) amount_shipping,sum(amount_refund) amount_refund,sum(amount_sale_shipping) amount_sale_shipping,sum(amount_platformfee) amount_platformfee,sum(amount_incoming) amount_incoming,sum(amount_other) amount_other from dc_daily_receivable where day>='{start.ToString("yyyy-MM-dd")}' and day<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";
            var sqlparam = new DynamicParameters();

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

            if (type == 1)  //Ebay
            {
                sql += " and platform in('Ebay','shopify','Etsy') ";
            }
            else if (type == 2) //非Ebay
            {
                sql += " and platform!='Ebay' and platform!='shopify' and platform!='Etsy' "; ;
            }

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

                var obj = cn.QueryFirstOrDefault<dc_daily_receivable>(sql, sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取应收账款的期初和期末合计
        /// </summary>
        /// <param name="platform"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public dc_daily_receivable ListReceivableStartEnd(string platform, DateTime start, DateTime end, int type)
        {
            var sqlparam = new DynamicParameters();
            var sql = $@" amount_start from dc_daily_receivable where 1=1 ";

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

            if (type == 1)  //Ebay
            {
                sql += " and platform in ('Ebay','shopify','Etsy') ";
            }
            else if (type == 2) //非Ebay
            {
                sql += " and platform!='Ebay' and platform!='shopify' and platform!='Etsy' ";
            }

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

                var amount_start = cn.QueryFirstOrDefault<decimal?>("select sum(amount_start)" + sql+" and day='"+start.ToString("yyyy-MM-dd")+"'", sqlparam)??0;
                var amount_end = cn.QueryFirstOrDefault<decimal?>("select sum(amount_end)" + sql + " and day='" + end.ToString("yyyy-MM-dd") + "'", sqlparam)??0;

                return new dc_daily_receivable {
                    amount_start = amount_start,
                    amount_end = amount_end
                };
            }

        }

        /// <summary>
        /// 修改应收款其他金额
        /// </summary>
        /// <param name="id"></param>
        /// <param name="amount">调整金额</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <param name="type">1:其他金额，2:放款金额</param>
        /// <returns></returns>
        public string UpdateReceivable(int id,decimal amount,int uid,string username,int type)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if(cn.State== ConnectionState.Closed)
                {
                    cn.Open();
                }

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

                if (obj == null)
                {
                    return "找不到该记录。";
                }

                if (type == 1)
                {
                    obj.amount_other = amount;
                }
                else if(type==2)
                {
                    obj.amount_incoming = amount;
                }
                

                if (obj.platform.ToLower() == "ebay" || obj.platform.ToLower() == "shopify" || obj.platform.ToLower() == "etsy")
                {
                    //期初预收余额+本期收款（订单付款金额）-发货金额-退款（未发货的）+其他
                    obj.amount_end = obj.amount_start + obj.amount_sale_pay - obj.amount_shipping - obj.amount_refund + obj.amount_other;
                }
                else
                {
                    
                    obj.amount_end = obj.amount_start + obj.amount_sale_shipping - obj.amount_platformfee - obj.amount_incoming - obj.amount_refund + obj.amount_other;
                }
                

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

                var result = cn.Update(obj);

                if (result>0)
                {
                    UpdateLogisticsEndAmount(obj.day, ReceivablesCount(obj.day).Item1, "accountsReceivableUpdate");

                    UpdateReceivableEndAmount(obj, cn, uid, username);
                }

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

        /// <summary>
        /// 修改应收款备注
        /// </summary>
        /// <param name="id"></param>
        /// <param name="note">备注</param>
        /// <returns></returns>
        public string UpdateReceivableNote(int id, string note, int uid, string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

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

                if (obj == null)
                {
                    return "找不到该记录。";
                }
                
                obj.note = note;

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

                var result = cn.Update(obj);
                
                return result > 0 ? "" : "更新失败，请重试！";
            }
        }



        /// <summary>
        /// 更新应收账款期末金额
        /// </summary>
        /// <param name="m"></param>
        /// <param name="cn"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        private void UpdateReceivableEndAmount(dc_daily_receivable m,MySqlConnection cn,int uid,string username)
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }

            var sql = $"select * from dc_daily_receivable where day='{m.day.AddDays(1).ToString("yyyy-MM-dd")}' and platform='{m.platform}'";

            var obj = cn.QueryFirstOrDefault<dc_daily_receivable>(sql);
            if (obj != null)
            {
                //Ebay预收账款=期初预收余额+本期收款（订单付款金额）-发货金额-退款（未发货的）
                if (obj.platform.ToLower() == "ebay" || obj.platform.ToLower() == "shopify" || obj.platform.ToLower()=="etsy")
                {

                    obj.amount_start = m.amount_end;
                    obj.amount_end = obj.amount_start + obj.amount_sale_pay - obj.amount_shipping - obj.amount_refund+obj.amount_other;
                    obj.lastupdatetime = DateTime.Now;
                    obj.lastupdateuserid = uid;
                    obj.lastupdateusername = username;

                    cn.Update<dc_daily_receivable>(obj);
                }
                //非Ebay   应收账款=期初应收余额+本期销售收入-平台扣费-放款金额-退款金额+其他
                else
                {
                    obj.amount_start = m.amount_end;
                    obj.amount_end = obj.amount_start + obj.amount_sale_shipping - obj.amount_platformfee - obj.amount_incoming - obj.amount_refund + obj.amount_other;
                    obj.lastupdatetime = DateTime.Now;
                    obj.lastupdateuserid = uid;
                    obj.lastupdateusername = username;
                    cn.Update<dc_daily_receivable>(obj);
                }

                UpdateLogisticsEndAmount(obj.day, ReceivablesCount(obj.day).Item1, "accountsReceivableUpdate");

                UpdateReceivableEndAmount(obj, cn, uid, username);

            }

        }



        #endregion

        #region 进销存不合理报告分析

        /// <summary>
        /// 进销存差异汇总
        /// </summary>
        /// <param name="month">月份</param>
        /// <param name="type">是否筛选增加了或者减少了，1：增加了，2：减少了</param>
        /// <returns></returns>
        public List<dc_mid_month_sale_putin> ListMonthSalePutin(string month,int? type)
        {
            var sql = $@"select t1.warehouse_type,t1.category_simple_name,sum(t1.count_outbound) count_outbound,sum(t1.count_outbound*t1.buyprice) amount_outbound,sum(t1.count_putin) count_putin,sum(t1.count_putin*t1.buyprice) amount_putin,sum(t1.count_putin-t1.count_outbound) count_diff,sum((t1.count_putin-t1.count_outbound)*t1.buyprice) amount_diff  from dc_mid_month_sale_putin t1 where t1.`month`='{month}'
                        ";
            if (type.HasValue)
            {
                if (type.Value == 1)
                {
                    sql += " and 0<t1.count_putin-t1.count_outbound";
                }
                else if (type.Value == 2)
                {
                    sql += " and 0>t1.count_putin-t1.count_outbound";
                }
            }

            if (type.HasValue && (type.Value == 1 || type.Value == 2))
            {
                sql += " group by t1.warehouse_type,category_simple_name";
            }
            else
            {
                sql += " group by t1.warehouse_type";
            }

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

                var list = cn.Query<dc_mid_month_sale_putin>(sql, null, null, true, 2 * 60).ToList();

                return list;
            }
        }

        /// <summary>
        /// 进销存差异明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="month">月份，必填</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="type">是否筛选增加了或者减少了，1：增加了，2：减少了</param>
        /// <param name="total">符合条件的总数</param>
        /// <returns></returns>
        public List<dc_mid_month_sale_putin_report> ListMonthSalePutinDetail(BtTableParameter parameter, string month, string warehousetype, string warehouse, int? type,int? isclear,int? isnew,int? isaims,ref int total,string category, int isreasonable=-1)
        {
            var sqlparam = new DynamicParameters();
            var sql = $@"select t1.warehouse_type,t1.warehouse_code,t1.warehouse_name,t1.bailun_sku,t1.count_putin,(t1.count_putin*t1.buyprice) amount_putin,t1.count_outbound,(t1.count_outbound*t1.buyprice) amount_outbound,(t1.count_putin-t1.count_outbound) count_diff,((t1.count_putin-t1.count_outbound)*t1.buyprice) amount_diff,t2.is_clear,t2.is_new,t2.is_aimsorder,t2.count_usable_stock,t2.avg_sevenday_sales,t2.avg_fourteenday_sales,t2.avg_thirtyday_sales,t2.avg_sales,t2.sale_putin_id,t2.avg_weighting_day_sales,t1.category_simple_name,t2.is_reasonable from dc_mid_month_sale_putin t1
                        left join dc_mid_month_sale_putin_report t2 on t1.id=t2.sale_putin_id
                        where t1.`month`='{month}'";

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

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

            if (type.HasValue)
            {
                if (type.Value == 1)
                {
                    sql += " and 0<t1.count_putin-t1.count_outbound";
                }
                else if (type.Value == 2)
                {
                    sql += " and 0>t1.count_putin-t1.count_outbound";
                }
            }

            if (isclear.HasValue)
            {
                if (isclear.Value == 1)
                {
                    sql += " and t2.is_clear=1";
                }
                else
                {
                    sql += " and t2.is_clear!=1";
                }
            }

            if (isnew.HasValue)
            {
                if (isnew.Value == 1)
                {
                    sql += " and t2.is_new=1";
                }
                else
                {
                    sql += " and t2.is_new!=1";
                }
            }

            if (isaims.HasValue)
            {
                if (isaims.Value == 1)
                {
                    sql += " and t2.is_aimsorder=1";
                }
                else
                {
                    sql += " and t2.is_aimsorder!=1";
                }
            }

            if (isreasonable == 0)   //不合理
            {
                sql += " and t2.is_reasonable=0 ";
            }
            else if (isreasonable == 1)  //合理
            {
                sql += " and t2.is_reasonable=1 ";
            }

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


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

                if (parameter.pageIndex > 0 && parameter.limit > 0)
                {
                    var obj = cn.Page<dc_mid_month_sale_putin_report>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam).ToList();

                    return obj;
                }
                else
                {
                    var obj = cn.Query<dc_mid_month_sale_putin_report>(sql,sqlparam).ToList();

                    return obj;
                }
            }
        }

        /// <summary>
        /// 进销存差异明细统计
        /// </summary>
        /// <param name="month">月份，必填</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="type">是否筛选增加了或者减少了，1：增加了，2：减少了</param>
        /// <returns></returns>
        public dc_mid_month_sale_putin_report ListMonthSalePutinDetailCount(string month, string warehousetype, string warehouse, int? type, int? isclear, int? isnew, int? isaims, string category, int isreasonable = -1)
        {
            var sqlparam = new DynamicParameters();
            var sql = $@"select sum(t1.count_putin) count_putin,sum(t1.count_putin*t1.buyprice) amount_putin,sum(t1.count_outbound) count_outbound,sum(t1.count_outbound*t1.buyprice) amount_outbound,sum(t1.count_putin-t1.count_outbound) count_diff,sum((t1.count_putin-t1.count_outbound)*t1.buyprice) amount_diff,sum(t2.count_usable_stock) count_usable_stock from dc_mid_month_sale_putin t1
                        left join dc_mid_month_sale_putin_report t2 on t1.id=t2.sale_putin_id                        
                        where t1.`month`='{month}'";

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

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

            if (type.HasValue)
            {
                if (type.Value == 1)
                {
                    sql += " and 0<t1.count_putin-t1.count_outbound";
                }
                else if (type.Value == 2)
                {
                    sql += " and 0>t1.count_putin-t1.count_outbound";
                }
            }

            if (isclear.HasValue)
            {
                if (isclear.Value == 1)
                {
                    sql += " and t2.is_clear=1";
                }
                else
                {
                    sql += " and t2.is_clear!=1";
                }
            }

            if (isnew.HasValue)
            {
                if (isnew.Value == 1)
                {
                    sql += " and t2.is_new=1";
                }
                else
                {
                    sql += " and t2.is_new!=1";
                }
            }

            if (isaims.HasValue)
            {
                if (isaims.Value == 1)
                {
                    sql += " and t2.is_aimsorder=1";
                }
                else
                {
                    sql += " and t2.is_aimsorder!=1";
                }
            }

            if (isreasonable == 0)   //不合理
            {
                sql += " and t2.is_reasonable=0 ";
            }
            else if (isreasonable == 1)  //合理
            {
                sql += " and t2.is_reasonable=1 ";
            }

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

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

                var obj = cn.QueryFirstOrDefault<dc_mid_month_sale_putin_report>(sql,sqlparam);

                return obj;
            }
        }

        #endregion

        #region 现金流量表 采购支出明细

        /// <summary>
        /// 采购支出明细
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_base_finance_cashier> ListPurchaseCost(DateTime start,DateTime end,int ispay=1)
        {
            var sql = "";

            if (ispay == 1)
            {
                sql = $"select code,detail_name,type_name,companymain_name_from,cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_status=1 and cashier_type=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 ((sourcecode in ('Buy','SemiFinishedProduct') and companymain_value_from !=14 and companymain_value_from!=48) or (tradeb_bjectname in ('广州哈倪蔓生物科技有限公司','广州拉古娜生物科技有限公司','广州美甲生产仓') and companymain_value_from in (1,2,5,7,8) and sourcecode in ('newCost','IncomeAndExpenditure')))";

            }
            else
            {
                sql = $"select code,detail_name,type_name,companymain_name_from,cashier_paymoneyrmb,cashier_time from dc_base_finance_cashier where cashier_type=2 and sourcecode='Buy'  and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and companymain_value_from !=14 and companymain_value_from!=48";
            }


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

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

                return obj;
            }
        }
        

        #endregion

        #region 资产负债表两期数据对比明细

        public List<dc_withdraw_on_way> BSS_WithdrawOnWay(BtTableParameter request, DateTime start, DateTime end,ref int total)
        {

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

                var sql = $@"select t0.*,t1.amount_rmb,t1.statistical_time,t2.amount_rmb as amount_rmb2,t2.statistical_time as statistical_time2 from (
                            select tb.* from (
                            select t1.sale_platform,t1.platform,t1.pay_account,t1.bank_card_user,t1.bank_name from dc_withdraw_on_way t1 where t1.statistical_time='{start.ToString("yyyy-MM-dd")}'
                            union all 
                            select t2.sale_platform,,t2.platform,t2.pay_account,t2.bank_card_user,t1.bank_name from dc_withdraw_on_way t2 where t2.statistical_time='{end.ToString("yyyy-MM-dd")}'
                            ) tb group by tb.bank_card_user,tb.pay_account,tb.sale_platform,tb.platform
                            ) t0
                            left join dc_withdraw_on_way t1 on t1.sale_platform=t0.sale_platform and t1.platform=t0.platform and t1.pay_account=t0.pay_account and t1.bank_card_user = t0.bank_card_user and t1.statistical_time='{start.ToString("yyyy-MM-dd")}'
                            left join dc_withdraw_on_way t2 on t2.sale_platform=t0.sale_platform and t2.platform=t0.platform and t2.pay_account=t0.pay_account and t2.bank_card_user = t0.bank_card_user and t2.statistical_time='{end.ToString("yyyy-MM-dd")}'";

                var sqlcount = $@"select count(0) from (
                                select * from (
                                select t1.sale_platform,t1.platform,t1.pay_account,t1.bank_card_user,t1.bank_name from dc_withdraw_on_way t1 where t1.statistical_time='{start.ToString("yyyy-MM-dd")}'
                                union all 
                                select t2.sale_platform,,t2.platform,t2.pay_account,t2.bank_card_user,t1.bank_name from dc_withdraw_on_way t2 where t2.statistical_time='{end.ToString("yyyy-MM-dd")}'
                                ) tb group by tb.bank_card_user,tb.pay_account,tb.sale_platform,tb.platform
                                ) t0";


                var list = cn.Page<dc_withdraw_on_way>(request.pageIndex, request.limit, sql, ref total,null,sqlcount); 

                return list.AsList();
            }

        }

        #endregion

        #region 4k资产负债表

        /// <summary>
        /// 获取4k的资产负债表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<dc_balance_sheet_4k> ListBalanceSheet4K(DateTime start,DateTime end)
        {
            var sql = $"select * from dc_balance_sheet_4k where day>='{start.ToString("yyyy-MM-dd")}' and day<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

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

                return cn.Query<dc_balance_sheet_4k>(sql).ToList();
            }

        }

        /// <summary>
        /// 更新4k的资产负债表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string SaveBalanceSheet4K(mBalanceSheet4K_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_balance_sheet_4k>("select * from dc_balance_sheet_4k where isedit=1 and day='" + m.day.ToString("yyyy-MM-dd") + "'");

                if (obj == null)
                {
                    obj = new dc_balance_sheet_4k
                    {
                        day = m.day,
                        cl_count = 0,
                        cl_dividends_payable = 0,
                        cl_oneyear_noncurrent_liabilities = 0,
                        cl_other_liabilities = 0,
                        cl_payable_interest = 0,
                        count_assets = 0,
                        count_liabilities = 0,
                        count_liabilities_and_oe = 0,
                        createtime = DateTime.Now,
                        fa_count = 0,
                        nca_count = 0,
                        ncl_count = 0,
                        oe_capital_reserve = 0,
                        oe_count = 0,
                        oe_paicl_up_capital = 0,
                        oe_parentcompany_count = 0,
                        accounts_payable = 0,
                        accounts_receivable = 0,
                        advance_received = 0,
                        fixed_assets = 0,
                        fa_interest_receivable = 0,


                        isedit = 1,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = uid,
                        lastupdateusername = username,
                        ncl_longterm_loan = 0,
                        ncl_other_liabilities = 0,


                        monetary_fund = 0,
                        oe_minority_equity = 0,
                        oe_surplus_public_accumulation = 0,
                        oe_undistributed_profit = 0,
                        other_accounts_payable = 0,
                        other_accounts_receivable = 0,

                        payable_remuneration = 0,
                        short_term_borrow = 0,
                        prepayment = 0,
                        taxes_payable = 0,
                    };
                }

                #region 赋值

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

                //流动资产合计=货币资金+应收账款+预付款项+应收利息+其他应收款
                obj.fa_count = obj.monetary_fund + obj.accounts_receivable + obj.prepayment + obj.fa_interest_receivable + obj.other_accounts_receivable;

                //非流动资产合计=固定资产
                obj.nca_count = obj.fixed_assets;

                //资产总计=流动资产合计+非流动资产合计
                obj.count_assets = obj.fa_count + obj.nca_count;

                //流动负债合计=短期借款+应付账款+预收款项+应付职工薪酬+应交税费+应付利息+应付股利+其他应付款+一年内到期的非流动负债+其他流动负债
                obj.cl_count = obj.short_term_borrow + obj.accounts_payable + obj.monetary_fund + obj.advance_received + obj.payable_remuneration + obj.taxes_payable + obj.cl_payable_interest + obj.cl_dividends_payable + obj.other_accounts_payable + obj.cl_oneyear_noncurrent_liabilities + obj.cl_other_liabilities;

                //非流动负债合计=长期借款+其他非流动负债
                obj.ncl_count = obj.ncl_longterm_loan + obj.ncl_other_liabilities;

                //负债合计=流动负债合计+非流动负债合计
                obj.count_liabilities = obj.cl_count + obj.ncl_count;

                //归属于母公司所有者权益合计=实收资本+资本公积+盈余公积+未分配利润
                obj.oe_parentcompany_count = obj.oe_paicl_up_capital + obj.oe_capital_reserve + obj.oe_surplus_public_accumulation + obj.oe_undistributed_profit;

                //所有者权益合计= 少数股东权益
                obj.oe_count = obj.oe_minority_equity;

                //负债和所有者权益总计=负债合计+所有者权益合计
                obj.count_liabilities_and_oe = obj.count_liabilities + obj.oe_count;

                #endregion

                if (obj.id > 0)
                {
                    var result = cn.Update<dc_balance_sheet_4k>(obj);
                    return result > 0 ? "" : "更新失败，请重试";
                }
                else
                {
                    var result = cn.Insert<dc_balance_sheet_4k>(obj);
                    return (result ?? 0) > 0 ? "" : "编辑失败";
                }


            }
        }

        #endregion

        #region OMS发货收入

        /// <summary>
        /// oms发货收入列表
        /// </summary>
        /// <param name="month"></param>
        /// <param name="isFBA">是否FBA，true 是</param>
        /// <returns></returns>
        public List<dc_month_shipping_incoming> ListMonthShippingIncoming(string month,bool isFBA=false)
        {
            var sql = $"select * from dc_month_shipping_incoming where month='{month}' and amount>0";

            sql += " and platform_type "+(isFBA?"=":"!=")+"'FBA'";

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

                var list = cn.Query<dc_month_shipping_incoming>(sql, null, null, true, 2 * 60).ToList();

                return list;
            }
        }
        #endregion
    }
}
