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

        #endregion

    }
}
