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

namespace Bailun.DC.Services
{
    public class BalanceSheetServices
    {

        #region 资产负债表

        /// <summary>
        /// 获取资产负债表导入表格的汇总金额
        /// </summary>
        /// <param name="type">金额类型</param>
        /// <param name="day">统计日期</param>
        /// <returns></returns>
        public decimal GetCount(string type, DateTime day)
        {
            var sql = $"select sum(amount) amount from dc_balance_sheet_subdetail where type='{type}' and day='{day.ToString("yyyy-MM-dd")}'";

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

                return cn.QueryFirstOrDefault<decimal?>(sql)??0;

            }

        }

        /// <summary>
        /// 获取资产负债表导入表格的列表
        /// </summary>
        /// <param name="type"></param>
        /// <param name="day"></param>
        /// <returns></returns>
        public List<dc_balance_sheet_subdetail> List(string type, DateTime day,BtTableParameter parameter,ref int total)
        {
            var sql = $"select * from dc_balance_sheet_subdetail where type='{type}' and day='{day.ToString("yyyy-MM-dd")}'";

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

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

                return obj;
            }
        }

        /// <summary>
        /// 新增资产负债表导入表格数据
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Add(dc_balance_sheet_subdetail m)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var result = cn.Insert<dc_balance_sheet_subdetail>(m);

                    return result ?? 0;
                }
            }
            catch (Exception ex)
            {

                return 0;
            }
            
        }

        public int AddRang(List<dc_balance_sheet_subdetail> list)
        {
            var result = 0;
            foreach (var item in list)
            {
                result += Add(item);
            }

            return result;
        }

        #endregion


        #region 新资产负债表

        /// <summary>
        /// 获取资产负债表
        /// </summary>
        /// <param name="type">类型，1：日，2：周，3：月，4：年</param>
        /// <param name="date">日期</param>
        /// <param name="paycompayvalue">付款主体id</param>
        /// <param name="tbtype">0:default,1:result,2:modify,</param>
        /// <returns></returns>
        public List<dc_balance_sheet_new_result> ListBalanceSheetNew(int type, DateTime start,DateTime end, int paycompayvalue,int tbtype=1)
        {
            var tbname = "dc_balance_sheet_new_result";
            if(tbtype==0)
            {
                tbname = "dc_balance_sheet_new";
            }
            else if(tbtype == 2)
            {
                tbname = "dc_balance_sheet_new_modify";
            }

            
            var sql = "select * from "+ tbname + " where company_value=" + paycompayvalue + " and statistical_time >='"+start.ToString("yyyy-MM-dd")+ "' and statistical_time<'"+end.AddDays(1).ToString("yyyy-MM-dd")+"'  order by statistical_time";
            var list = new List<dc_balance_sheet_new_result>();
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

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

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

        public List<dc_balance_sheet_new_result> GetBalanceSheetNew(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_new_result where company_value=" + paycompayvalue + " and statistical_time in ('" + str + "')  order by statistical_time";
            var list = new List<dc_balance_sheet_new_result>();
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

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

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

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


        #endregion

    }
}
