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

namespace Bailun.DC.Services.DataWareHouse
{
    public class PlatformOrderServices
    {
        /// <summary>
        /// 获取平台站点信息
        /// </summary>
        /// <param name="platform"></param>
        /// <returns></returns>
        public List<string> ListPlatformSite(string platform)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_DW))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select t1.website from flowing_sales t1 where t1.platform='{platform}' group by t1.website";

                return cn.Query<string>(sql).Where(a=>!string.IsNullOrEmpty(a)).ToList();
            }
        }

        /// <summary>
        /// 获取平台销售账单流水
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="platform">平台类型</param>
        /// <param name="website">站点</param>
        /// <param name="account">销售帐号</param>
        /// <param name="month">月份</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.DataWareHouse.flowing_sales> List(int page, string platform, string website, string account,DateTime? start,DateTime? end,string orderno,ref int total,int pagesize)
        {
            var sql = "select * from flowing_sales t1 where 1=1";
            var sqlparam = new DynamicParameters();

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

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

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

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

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

            if (start.HasValue)
            {
                sql += $" and t1.datatime>='{start.Value.ToString("yyyy-MM-dd")}'";
            }

            if (end.HasValue)
            {
                sql += $" and t1.datatime<'{end.Value.AddDays(1).ToString("yyyy-MM-dd")}'";
            }

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

                if (pagesize > 0)
                {
                    var obj = cn.Page<Models.DataWareHouse.flowing_sales>(page, pagesize, sql, ref total, sqlparam);

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

        }

        /// <summary>
        /// 获取销售平台流水的模版数据
        /// </summary>
        /// <param name="platform"></param>
        /// <param name="website"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Models.DataWareHouse.flowing_sales Get(string platform,string website,DateTime? start,DateTime? end)
        {
            var sql = "select * from flowing_sales where 1=1";
            var sqlParam = new DynamicParameters();

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

            if(start.HasValue)
            {
                sql += $" and datatime>='{start.Value.ToString("yyyy-MM-dd")}'";
            }
            if(end.HasValue)
            {
                sql += $" and datatime<'{end.Value.AddDays(1).ToString("yyyy-MM-dd")}'";
            }

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

                var obj = cn.QueryFirstOrDefault<Models.DataWareHouse.flowing_sales>(sql,sqlParam);

                return obj;
            }
        }

        /// <summary>
        /// 保存流水信息
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public string InsertOrderBilling(List<Models.DataWareHouse.flowing_sales> list)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_DW))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

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

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