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

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

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

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

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

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

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

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

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

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

                        list.Add(m);
                    }
                }
            }

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

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

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

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

                var result = cn.Execute(sql);

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

        #endregion

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

            return list;
        }

        #endregion

        #region 平台余额

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

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

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

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

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

            }

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


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

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

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

                return list;
            }
        }

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

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

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

                return obj;
            }
        }

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

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

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

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

            }

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

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

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

                return list;
            }

        }

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

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

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

                return cn.QueryFirstOrDefault<dc_platform_balance>(sql);
            }
        }

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

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

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

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

            }
        }

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

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

            return "";
        }

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

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

                return obj;
            }
        }

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

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

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

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

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

        }

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

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

                    m.withdraw_amount = balance;

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

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

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

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

        #endregion

        #region 平台编码+名称

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

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

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

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

        }

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

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

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

        #endregion

        #region 资产负债表子表

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

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

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

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

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

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

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

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

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

                return list.AsList();
            }
        }

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

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

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

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

                return list.ToList();
            }
        }

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

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

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

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

                return list.ToList();
            }
        }

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

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

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

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

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

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

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

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

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

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

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

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


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

                return list.ToList();
            }
        }

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


                var sql = "select sum(end_amount) from dc_supplier_transaction where day='" + day.ToString("yyyy-MM-dd") + "' ";// and pay_company_value=" + payid;
                var logissql = "select sum(end_amount) from dc_logistics_supplier_transaction where day='" + day.ToString("yyyy-MM-dd") + "' ";

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

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

                var list = new List<decimal>();

                var obj1 = cn.QueryFirstOrDefault<decimal?>(sql);
                var obj2 = cn.QueryFirstOrDefault<decimal?>(logissql);



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

                return list;
            }
        }

        /// <summary>
        /// 预付款明细||应付账款明细
        /// </summary>
        /// <param name="day"></param>
        /// <param name="payid"></param>
        /// <param name="repay">是否预付款，1：预付款，0：应付款</param>
        /// <returns></returns>
        public List<dc_supplier_transaction> ListPrePay(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_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 (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="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;
            }
        }

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

        #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

    }
}
