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

namespace Bailun.DC.Services
{
    public class OrdersServices
    {
        #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_base_oms_order> ListPlatformCost(BtTableParameter parameter,string platform,DateTime? start,DateTime? end,ref int total)
        {
            var sql = "select * from dc_base_oms_order where bailun_order_status!='Canceled' ";
            var sqlparam = new DynamicParameters();

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

            if (start.HasValue)
            {
                sql += " and paid_time>=@paidtimestart";
                sqlparam.Add("paidtimestart", start.Value.ToString("yyyy-MM-dd"));
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paidtimeend";
                sqlparam.Add("paidtimeend", end.Value.AddDays(1).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_base_oms_order>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam);

                return obj.AsList();
            }
        }

        public dc_base_oms_order ListPlatformCostCount(string platform, DateTime? start, DateTime? end)
        {
            var sql = "select sum(amount_sales*seller_order_exchange_rate) amount_sales,sum(cost_platform_fee*seller_order_exchange_rate) as cost_platform_fee,sum(cost_fba_fee*seller_order_exchange_rate) cost_fba_fee,sum(cost_promotion*seller_order_exchange_rate) cost_promotion,sum(cost_paypal_fee*seller_order_exchange_rate) cost_paypal_fee,sum(cost_tail) cost_tail,sum(cost_handle_bailun) cost_handle_bailun from dc_base_oms_order where bailun_order_status!='Canceled' ";
            var sqlparam = new DynamicParameters();

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

            if (start.HasValue)
            {
                sql += " and paid_time>=@paidtimestart";
                sqlparam.Add("paidtimestart", start.Value.ToString("yyyy-MM-dd"));
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paidtimeend";
                sqlparam.Add("paidtimeend", end.Value.AddDays(1).ToString("yyyy-MM-dd"));
            }

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

                var obj = cn.QueryFirst<dc_base_oms_order>(sql, sqlparam);

                return obj;
            }
        }

        #endregion

        #region 亚马逊

        /// <summary>
        /// 获取亚马逊的销售统计数据（非FBA）
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="account">销售帐号</param>
        /// <param name="website">站点</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.mAmazonStatistics> ListAmazonStatistics(BtTableParameter parameter, string account, string website, DateTime start, DateTime end,string warehoursetype,string warehousecode, ref int total)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select platform_type,seller_account,website,order_currency,sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'amount_product',sum(cost_product*bailun_sku_quantity_shipped) 'cost_product',sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'platform_fee',sum(cost_first*bailun_sku_quantity_shipped) 'head_fee',count(t1.bailun_order_id) 'order_count',sum(cost_total*bailun_sku_quantity_shipped) 'cost_count',sum(cost_tail*bailun_sku_quantity_shipped) 'nofba_logisticsfee',sum(profit_total*bailun_sku_quantity_shipped) 'profit_count',(sum(profit_total*bailun_sku_quantity_shipped)/sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)) 'profit_rate',sum(amount_prepaid) amount_prepaid,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) amount_refund  from dc_base_oms_sku t1";
            var presql = "select platform_type,seller_account,website,order_currency,sum(amount_prepaid) as amount_prepaid from dc_base_oms_sku t1 ";

            if (!string.IsNullOrEmpty(warehoursetype))
            {
                sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_type_ex=@warehoursetype";
                presql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_type_ex=@warehoursetype";
                sqlparam.Add("warehoursetype", warehoursetype);
            }
            if (!string.IsNullOrEmpty(warehousecode))
            {
                if (!string.IsNullOrEmpty(warehoursetype))
                {
                    sql += " and t2.warehouse_code=@warehouse_code";
                    presql += " and t2.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }
                else
                {
                    sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_code=@warehouse_code";
                    presql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }
            }
            
            var fromsql = " where paid_time>='" + start.ToString("yyyy-MM-dd") + "' and paid_time<'" + end.AddDays(1).ToString("yyyy-MM-dd") + "' and platform_type='Amazon' and bailun_order_status!='Canceled'  ";

            if (!string.IsNullOrWhiteSpace(account))
            {
                fromsql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(website))
            {
                fromsql += " and website=@website";
                sqlparam.Add("website", website);
            }

            var prefromsql = fromsql;
            fromsql += "  and bailun_sku_quantity_shipped>0";
            fromsql += " group by seller_account,website,order_currency,platform_type";

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                fromsql += " order by "+parameter.sort +" "+parameter.order;
            }

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

                var obj = cn.Page<Models.Orders.mAmazonStatistics>(parameter.pageIndex, parameter.limit, sql+fromsql, ref total, sqlparam);

                //另外计算预收款金额
                var list = obj.AsList();
                prefromsql+= "  and bailun_sku_quantity_shipped<=0";
                prefromsql += " and seller_account in ('" + string.Join("','", list.Select(a => a.seller_account)) + "') and website in ('" + string.Join("','", list.Select(a => a.website)) + "') and order_currency in ('" + string.Join("','", list.Select(a => a.order_currency)) + "')";
                prefromsql += "  group by seller_account,website,order_currency,platform_type";

                var obj1 = cn.Query<Models.Orders.mAmazonStatistics>(presql+prefromsql,sqlparam);

                foreach (var item in list)
                {
                    var temp = obj1.Where(a => a.seller_account == item.seller_account && a.website == item.website && a.order_currency == item.order_currency && a.platform_type == item.platform_type).FirstOrDefault();
                    if (temp != null)
                    {
                        item.amount_prepaid = temp.amount_prepaid;
                    }
                }

                return list;
            }

        }

        /// <summary>
        /// 获取亚马逊的销售统计数据（非FBA） 统计
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="account">销售帐号</param>
        /// <param name="website">站点</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public Models.Orders.mAmazonStatistics ListAmazonStatisticsCount(string account, string website, DateTime start, DateTime end,string warehoursetype,string warehousecode)
        {
            var sqlparam = new DynamicParameters();

            var presql = "select sum(amount_prepaid) as amount_prepaid from dc_base_oms_sku t1 ";
            var sql = "select sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'amount_product',sum(cost_product*bailun_sku_quantity_shipped) 'cost_product',sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'platform_fee',sum(cost_first*bailun_sku_quantity_shipped) 'head_fee',count(t1.id) 'order_count',sum(cost_total*bailun_sku_quantity_shipped) 'cost_count',sum(cost_tail*bailun_sku_quantity_shipped) 'nofba_logisticsfee',sum(profit_total*bailun_sku_quantity_shipped) 'profit_count',(sum(profit_total*bailun_sku_quantity_shipped)/sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)) 'profit_rate',sum(amount_prepaid) amount_prepaid,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) amount_refund  from dc_base_oms_sku t1 ";

            if (!string.IsNullOrEmpty(warehoursetype))
            {
                sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_type_ex=@warehoursetype";
                presql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_type_ex=@warehoursetype";
                sqlparam.Add("warehoursetype", warehoursetype);
            }
            if (!string.IsNullOrEmpty(warehousecode))
            {
                if (!string.IsNullOrEmpty(warehoursetype))
                {
                    sql += " and t2.warehouse_code=@warehouse_code";
                    presql += " and t2.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }
                else
                {
                    sql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_code=@warehouse_code";
                    presql += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }
            }

            var fromsql = " where paid_time>='" + start.ToString("yyyy-MM-dd") + "' and paid_time<'" + end.AddDays(1).ToString("yyyy-MM-dd") + "' and platform_type='Amazon' and bailun_order_status!='Canceled'  ";

            if (!string.IsNullOrWhiteSpace(account))
            {
                fromsql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(website))
            {
                fromsql += " and website=@website";
                sqlparam.Add("website", website);
            }

            var prefromsql = fromsql;
            fromsql += "  and bailun_sku_quantity_shipped>0";
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                try
                {
                    var obj = cn.QueryFirst<Models.Orders.mAmazonStatistics>(sql+fromsql, sqlparam);

                    prefromsql += "  and bailun_sku_quantity_shipped<=0";
                    var obj1 = cn.QueryFirst<Models.Orders.mAmazonStatistics>(presql + prefromsql, sqlparam);
                    if (obj1 != null && obj!=null)
                    {
                        obj.amount_prepaid = obj1.amount_prepaid;
                    }

                    return obj;
                }
                catch (Exception ex)
                {
                    return new Models.Orders.mAmazonStatistics();
                }
                
            }

        }

        /// <summary>
        /// 获取亚马逊的销售统计数据（FBA）
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="account">销售帐号</param>
        /// <param name="website">站点</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.mAmazonStatistics> ListFBAStatistics(BtTableParameter parameter, string account, string website, DateTime start, DateTime end, ref int total)
        {

            var sqlparam = new DynamicParameters();

            var sql = @"select platform_type,seller_account,website,sum(amount_product*seller_order_exchange_rate) 'amount_product',sum(cost_product) 'cost_product',sum(cost_platform_fee*seller_order_exchange_rate) 'platform_fee',sum(cost_first) 'head_fee',count(id) 'order_count',sum(cost_total) 'cost_count',sum(cost_shipping) 'nofba_logisticsfee',sum(profit_total) 'profit_count',(sum(profit_total)/sum(amount_total*seller_order_exchange_rate)) 'profit_rate',sum(amount_prepaid) amount_prepaid,sum(amount_refund*seller_order_exchange_rate) amount_refund,sum(cost_fba_fee*seller_order_exchange_rate) cost_fba_fee,sum(cost_paypal_fee*seller_order_exchange_rate) cost_paypal_fee,sum(cost_package) cost_package  from dc_base_oms_order where paid_time>='" + start.ToString("yyyy-MM-dd") + "' and paid_time<'" + end.AddDays(1).ToString("yyyy-MM-dd") + "' and platform_type='FBA' and bailun_order_status!='Canceled' ";

            if (!string.IsNullOrWhiteSpace(account))
            {
                sql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

            sql += " group by seller_account,website,order_currency,platform_type";

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

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

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

                return obj.AsList();
            }

        }

        /// <summary>
        /// 获取亚马逊的销售统计数据（FBA） 统计
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="account">销售帐号</param>
        /// <param name="website">站点</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        public Models.Orders.mAmazonStatistics ListFBAStatisticsCount(string account, string website, DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select sum(amount_product*seller_order_exchange_rate) 'amount_product',sum(cost_product) 'cost_product',sum(cost_platform_fee*seller_order_exchange_rate) 'platform_fee',sum(cost_first) 'head_fee',count(id) 'order_count',sum(cost_total) 'cost_count',sum(cost_shipping) 'nofba_logisticsfee',sum(profit_total) 'profit_count',(sum(profit_total)/sum(amount_product*seller_order_exchange_rate)) 'profit_rate',sum(amount_prepaid) amount_prepaid,sum(amount_refund*seller_order_exchange_rate) amount_refund,sum(cost_fba_fee*seller_order_exchange_rate) cost_fba_fee,sum(cost_paypal_fee*seller_order_exchange_rate) cost_paypal_fee,sum(cost_package) cost_package   from dc_base_oms_order where paid_time>='" + start.ToString("yyyy-MM-dd") + "' and paid_time<'" + end.AddDays(1).ToString("yyyy-MM-dd") + "' and platform_type='FBA' and bailun_order_status!='Canceled' ";

            if (!string.IsNullOrWhiteSpace(account))
            {
                sql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

                var obj = cn.QueryFirst<Models.Orders.mAmazonStatistics>(sql, sqlparam);

                return obj;
            }

        }


        /// <summary>
        /// 订单流水
        /// </summary>
        /// <param name="parameter">页面信息</param>
        /// <param name="platform">所属平台</param>
        /// <param name="website">站点</param>
        /// <param name="account">帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">平台单号</param>
        /// <param name="sku">sku编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_order> ListOrders(BtTableParameter parameter, string platform, string website, string account, DateTime? start, DateTime? end, string orderno, string sku, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select t1.origin_order_id,t1.platform_type,t1.website,t1.seller_account,t1.platform_order_type,t1.bailun_order_status,t1.bailun_payment_status,t1.bailun_shipping_status,t1.order_currency,t1.amount_total,t1.amount_shipping,t1.amount_product,t1.amount_adjustment,t1.cost_promotion,t1.cost_platform_fee,t1.cost_product,t1.cost_first,t1.cost_package,t1.cost_fba_fee,t1.cost_total,(t1.profit_total-t1.cost_first) profit_total,t1.create_time,t1.order_update_time from dc_base_oms_order t1";

            var strwhere = " where t1.bailun_order_status!='Canceled' ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (platform.ToUpper() == "AMAZON")
                {
                    strwhere += " and t1.platform_type in ('Amazon','FBA')";
                    sqlparam.Add("platform_type", platform);
                }
                else
                {
                    strwhere += " and t1.platform_type=@platform_type";
                    sqlparam.Add("platform_type", platform);
                }
            }

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

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and t1.seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                strwhere += " and t1.origin_order_id=@origin_order_id";
                sqlparam.Add("origin_order_id", orderno);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " join dc_base_oms_sku t2 on t1.origin_order_id = t2.origin_order_id and t2.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (start.HasValue)
            {
                strwhere += " and t1.paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                strwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                strwhere += " order by t1." + parameter.sort+" "+parameter.order;
            }


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

                var obj = cn.Page<Models.Orders.dc_base_oms_order>(parameter.pageIndex, parameter.limit, sql + strwhere, ref total, sqlparam);
                var list = obj.AsList();
                var arr = list.Select(p => p.origin_order_id);
                sql = "select origin_order_id,sum(bailun_sku_quantity_ordered) as bailun_sku_quantity_ordered from dc_base_oms_sku where bailun_order_status!='Canceled' and origin_order_id in ('" + string.Join("','", arr) + "') group by origin_order_id ";

                var skucounts = cn.Query<Models.Orders.dc_base_oms_sku>(sql);
                foreach (var item in list)
                {
                    var m = skucounts.FirstOrDefault(a => a.origin_order_id == item.origin_order_id);
                    item.salecount = m != null ? m.bailun_sku_quantity_ordered : 0;
                }

                return obj.AsList();
            }

        }

        /// <summary>
        /// 订单流水 统计
        /// </summary>
        /// <param name="parameter">页面信息</param>
        /// <param name="platform">所属平台</param>
        /// <param name="website">站点</param>
        /// <param name="account">帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">平台单号</param>
        /// <param name="sku">sku编码</param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_order ListOrdersCount(string platform, string website, string account, DateTime? start, DateTime? end, string orderno, string sku)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select sum(t1.amount_total) amount_total,sum(t1.amount_shipping) amount_shipping,sum(t1.amount_product) amount_product,sum(t1.amount_adjustment) amount_adjustment,sum(t1.cost_promotion) cost_promotion,sum(t1.cost_platform_fee) cost_platform_fee,sum(t1.cost_product) cost_product,sum(t1.cost_first) cost_first,sum(t1.cost_package) cost_package,sum(t1.cost_fba_fee) cost_fba_fee,sum(t1.cost_total) cost_total,sum(t1.profit_total-t1.cost_first) profit_total from dc_base_oms_order t1";

            var strwhere = " where t1.bailun_order_status!='Canceled' ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (platform.ToUpper() == "AMAZON")
                {
                    strwhere += " and t1.platform_type in ('Amazon','FBA')";
                    sqlparam.Add("platform_type", platform);
                }
                else
                {
                    strwhere += " and t1.platform_type=@platform_type";
                    sqlparam.Add("platform_type", platform);
                }
            }

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

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and t1.seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                strwhere += " and t1.origin_order_id=@origin_order_id";
                sqlparam.Add("origin_order_id", orderno);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " join dc_base_oms_sku t2 on t1.origin_order_id = t2.origin_order_id and t2.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (start.HasValue)
            {
                strwhere += " and t1.paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                strwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }


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

                try
                {
                    var obj = cn.QueryFirst<Models.Orders.dc_base_oms_order>(sql + strwhere, sqlparam);
                    return obj;

                }
                catch (Exception ex)
                {
                    return new Models.Orders.dc_base_oms_order();
                }
                
            }
        }

        
        /// <summary>
        /// 平台财务费用
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="platform">平台类型</param>
        /// <param name="feetype">费用类型</param>
        /// <param name="feecharacter">费用性质</param>
        /// <param name="account">销售帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">订单号</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_sku> ListFinanceFee(BtTableParameter parameter,string platform, int feetype, int feecharacter, string account, DateTime? start, DateTime? end, string orderno, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select origin_order_id,platform_type,website,seller_account,platform_order_type,bailun_sku,amount_product,cost_first,amount_shipping,cost_promotion,cost_shipping,cost_fba_fee,cost_platform_fee,cost_package,amount_total,cost_total+cost_shipping as cost_total,paid_time,seller_order_exchange_rate,finance_order_exchange_rate from dc_base_oms_sku where bailun_order_status!='Canceled'";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (platform.ToUpper() == "AMAZON")
                {
                    sql += " and platform_type in ('Amazon','FBA')";
                    sqlparam.Add("platform_type", platform);
                }
                else
                {
                    sql += " and platform_type=@platform_type";
                    sqlparam.Add("platform_type", platform);
                }
            }

            if (!string.IsNullOrWhiteSpace(account))
            {
                sql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

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

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

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

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

                return obj.AsList();
            }
        }

        /// <summary>
        /// 平台财务费用 统计
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="platform">平台类型</param>
        /// <param name="feetype">费用类型</param>
        /// <param name="feecharacter">费用性质</param>
        /// <param name="account">销售帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">订单号</param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_sku ListFinanceFeeCount(string platform, int feetype, int feecharacter, string account, DateTime? start, DateTime? end, string orderno)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select sum(amount_product) amount_product,sum(cost_first) cost_first,sum(amount_shipping) amount_shipping,sum(cost_promotion) cost_promotion,sum(cost_shipping) cost_shipping,sum(cost_fba_fee) cost_fba_fee,sum(cost_platform_fee) cost_platform_fee,sum(cost_package) cost_package,sum(amount_total) amount_total,sum(cost_total+cost_shipping) as cost_total from dc_base_oms_sku where bailun_order_status!='Canceled'";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (platform.ToUpper() == "AMAZON")
                {
                    sql += " and platform_type in ('Amazon','FBA')";
                    sqlparam.Add("platform_type", platform);
                }
                else
                {
                    sql += " and platform_type=@platform_type";
                    sqlparam.Add("platform_type", platform);
                }
            }

            if (!string.IsNullOrWhiteSpace(account))
            {
                sql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

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

                try
                {
                    var obj = cn.QueryFirst<Models.Orders.dc_base_oms_sku>(sql, sqlparam);
                    return obj;
                }
                catch (Exception ex)
                {
                    return new Models.Orders.dc_base_oms_sku();
                }
                
            }
        }

        /// <summary>
        /// FBA费用异常订单
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="account"></param>
        /// <param name="website"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="orderno"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<Models.Orders.mFBAAbnormalOrder> ListFbaAbnormalOrder(BtTableParameter parameter, string account, string website, DateTime? start, DateTime? end, string orderno, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var strwhere = "select t1.origin_order_id,t1.platform_type,t1.website,t1.seller_account,t1.bailun_sku,t1.order_currency,t1.cost_fba_fee,(t1.fba_fee_rmb*t1.seller_order_exchange_rate) fba_fee_rmb,t2.average_fba_fee,t2.min_fba_fee,t2.max_fba_fee,t1.create_time,t1.seller_order_exchange_rate from dc_base_oms_sku t1 join dc_mid_fba_fee_avg t2 on t1.bailun_sku=t2.bailun_sku and t1.website=t2.website where  t1.bailun_order_status!='Canceled' and t1.platform_type in ('Amazon','FBA') and t1.has_fba_exception=1";

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

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and t1.seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                strwhere += " and t1.origin_order_id=@origin_order_id";
                sqlparam.Add("origin_order_id", orderno);
            }

            if (start.HasValue)
            {
                strwhere += " and t1.paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                strwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                strwhere += " order by " + parameter.sort;

                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    strwhere += (" " + parameter.order);
                }
            }

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

                var obj = cn.Page<Models.Orders.mFBAAbnormalOrder>(parameter.pageIndex, parameter.limit, strwhere, ref total, sqlparam);

                return obj.AsList();
            }

        }

        public List<Models.Orders.dc_base_oms_order> ListPlatformFeeAbnormal(BtTableParameter parameter, string platform, string account, string website, string orderno, DateTime? start, DateTime? end, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var strwhere = "select origin_order_id,bailun_order_id,seller_account,amount_product,cost_platform_fee,(cost_platform_fee/amount_product) platform_fee_rate,platform_type,website,create_time from dc_base_oms_order where bailun_order_status!='Canceled' and has_platform_exception=1 ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (platform.ToUpper() == "AMAZON")
                {
                    strwhere += " and platform_type in ('Amazon','FBA')";
                    sqlparam.Add("platform_type", platform);
                }
                else
                {
                    strwhere += " and platform_type=@platform_type";
                    sqlparam.Add("platform_type", platform);
                }
            }


            if (!string.IsNullOrWhiteSpace(website))
            {
                strwhere += " and website=@website";
                sqlparam.Add("website", website);
            }

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                strwhere += " and origin_order_id=@origin_order_id";
                sqlparam.Add("origin_order_id", orderno);
            }

            if (start.HasValue)
            {
                strwhere += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                strwhere += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                strwhere += " order by " + parameter.sort;

                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    strwhere += (" " + parameter.order);
                }
            }

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

                var obj = cn.Page<Models.Orders.dc_base_oms_order>(parameter.pageIndex, parameter.limit, strwhere, ref total, sqlparam);

                return obj.AsList();
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="platform"></param>
        /// <param name="account"></param>
        /// <param name="website"></param>
        /// <param name="orderno"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_order ListPlatformFeeAbnormalCount(string platform, string account, string website, string orderno, DateTime? start, DateTime? end)
        {
            var sqlparam = new DynamicParameters();
            var strwhere = "select sum(amount_product) amount_product,sum(cost_platform_fee) cost_platform_fee from dc_base_oms_order where bailun_order_status!='Canceled' and has_platform_exception=1 ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (platform.ToUpper() == "AMAZON")
                {
                    strwhere += " and platform_type in ('Amazon','FBA')";
                    sqlparam.Add("platform_type", platform);
                }
                else
                {
                    strwhere += " and platform_type=@platform_type";
                    sqlparam.Add("platform_type", platform);
                }
            }


            if (!string.IsNullOrWhiteSpace(website))
            {
                strwhere += " and website=@website";
                sqlparam.Add("website", website);
            }

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                strwhere += " and origin_order_id=@origin_order_id";
                sqlparam.Add("origin_order_id", orderno);
            }

            if (start.HasValue)
            {
                strwhere += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (end.HasValue)
            {
                strwhere += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirst<Models.Orders.dc_base_oms_order>(strwhere, sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取平台利润
        /// </summary>
        /// <param name="platform">平台类型</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_sku> ListPlatformProfit(string platform, DateTime? start, DateTime? end)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select platform_type, sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) amount_sales,count(bailun_order_id) order_count,sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) cost_platform_fee,(sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)/count(bailun_order_id)) customerprice,sum(profit_total*bailun_sku_quantity_shipped) profit_total,(sum(profit_total*bailun_sku_quantity_shipped)/sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)) profitrate,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) amount_refund,sum(cost_first*bailun_sku_quantity_shipped) cost_first,sum(cost_tail*bailun_sku_quantity_shipped) cost_tail,sum(cost_handle_bailun*bailun_sku_quantity_shipped) cost_handle_bailun,sum(cost_handle_platform*bailun_sku_quantity_shipped) cost_handle_platform  from dc_base_oms_sku where bailun_order_status!='Canceled' and bailun_sku_quantity_shipped>0";
            var presql = "select platform_type,sum(amount_prepaid) amount_prepaid from dc_base_oms_sku where bailun_order_status!='Canceled' and amount_prepaid>0 ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                sql += " and platform_type=@platform_type";
                presql += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }
            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                presql += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                presql += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

            sql += " group by platform_type";
            presql += " group by platform_type";

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

                var obj = cn.Query<Models.Orders.dc_base_oms_sku>(sql, sqlparam);

                //增加预收款金额
                var obj1 = cn.Query<Models.Orders.dc_base_oms_sku>(presql, sqlparam);

                foreach (var item in obj)
                {
                    var temp = obj1.Where(a => a.platform_type == item.platform_type).FirstOrDefault();
                    if (temp != null)
                    {
                        item.amount_prepaid = temp.amount_prepaid;
                    }
                }

                return obj.AsList();
            }

        }

        /// <summary>
        /// 获取平台利润 按站点
        /// </summary>
        /// <param name="platform"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="parameter"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<Models.Orders.mPlatformProfit> ListPlatformProfitByWebSite(string platform, DateTime? start, DateTime? end, BtTableParameter parameter, ref int total)
        {
            var sqlparam = new DynamicParameters();

            var precolumn = "select website,platform_type as platform,sum(amount_prepaid*seller_order_exchange_rate) amount_prepaid  from dc_base_oms_sku where bailun_order_status!='Canceled' ";
            var sql = "select platform_type as platform,website,sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) saleamount,count(bailun_order_id) ordercount,sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) platformfee,(sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)/count(bailun_order_id)) customerprice,sum(profit_total*bailun_sku_quantity_shipped) profit,(sum(profit_total)/sum(amount_sales*seller_order_exchange_rate)) profitrate,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) returnamount,(sum(amount_refund)/sum(amount_sales)) returnamountrate,sum(cost_first*bailun_sku_quantity_shipped) cost_first,sum(cost_tail*bailun_sku_quantity_shipped) cost_tail,sum(cost_handle_bailun*bailun_sku_quantity_shipped) cost_handle_bailun,sum(cost_handle_platform*bailun_sku_quantity_shipped) cost_handle_platform  from dc_base_oms_sku where bailun_order_status!='Canceled' and bailun_sku_quantity_shipped>0";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                sql += " and platform_type=@platform_type";
                precolumn += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }
            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                precolumn += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                precolumn += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

            sql += " group by website,platform_type";

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

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

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

                var list = obj.AsList();
                precolumn += "  and amount_prepaid>0";
                precolumn += " and website in ('" + string.Join("','", list.Select(a => a.website)) + "') and platform_type in ('" + string.Join("','", list.Select(a => a.platform).Distinct()) + "')";
                precolumn += "  group by website,platform_type";

                var obj1 = cn.Query<Models.Orders.mPlatformProfit>(precolumn, sqlparam);

                foreach (var item in list)
                {
                    var temp = obj1.Where(a => a.website == item.website && a.platform == item.platform).FirstOrDefault();
                    if (temp != null)
                    {
                        item.amount_prepaid = temp.amount_prepaid;
                    }
                }

                return list;
            }
        }

        /// <summary>
        /// 获取平台利润 按站点 统计
        /// </summary>
        /// <param name="platform"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="parameter"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public Models.Orders.mPlatformProfit ListPlatformProfitByWebSiteCount(string platform, DateTime? start, DateTime? end, BtTableParameter parameter, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) saleamount,count(bailun_order_id) ordercount,sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) platformfee,(sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)/count(bailun_order_id)) customerprice,sum(profit_total*bailun_sku_quantity_shipped) profit,(sum(profit_total)/sum(amount_sales*seller_order_exchange_rate)) profitrate,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) returnamount,(sum(amount_refund)/sum(amount_sales)) returnamountrate,sum(cost_first*bailun_sku_quantity_shipped) cost_first,sum(cost_tail*bailun_sku_quantity_shipped) cost_tail,sum(cost_handle_bailun*bailun_sku_quantity_shipped) cost_handle_bailun,sum(cost_handle_platform*bailun_sku_quantity_shipped) cost_handle_platform  from dc_base_oms_sku where bailun_order_status!='Canceled' and bailun_sku_quantity_shipped>0";
            var precolumn = "select sum(amount_prepaid*seller_order_exchange_rate) amount_prepaid  from dc_base_oms_sku where bailun_order_status!='Canceled' ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                sql += " and platform_type=@platform_type";
                precolumn += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }
            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                precolumn += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                precolumn += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

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

                var obj = cn.QueryFirst<Models.Orders.mPlatformProfit>(sql, sqlparam);

                var obj1 = cn.QueryFirst<Models.Orders.mPlatformProfit>(precolumn, sqlparam);

                if (obj != null && obj1 != null)
                {
                    obj.amount_prepaid = obj1.amount_prepaid;
                }

                return obj;
            }
        }

        /// <summary>
        /// 利润统计订单明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="platform">平台信息</param>
        /// <param name="website">站点信息</param>
        /// <param name="account">销售帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">百伦单号</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_order> ListPlatformProfitOrders(BtTableParameter parameter, string platform, string website,string account, DateTime? start, DateTime? end, string orderno, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select platform_type,website,origin_order_id,bailun_order_id,seller_account,order_currency,sum(amount_sales*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_total,sum(amount_product*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_product,sum(amount_shipping*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_shipping,sum(amount_tax*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_tax,sum(amount_adjustment*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_adjustment,sum(amount_gift_wrap*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_gift_wrap,sum(amount_refund*bailun_sku_quantity_shipped*seller_order_exchange_rate) amount_refund,sum(cost_total*bailun_sku_quantity_shipped) cost_total,sum(cost_promotion*bailun_sku_quantity_shipped*seller_order_exchange_rate) cost_promotion,sum(cost_platform_fee*bailun_sku_quantity_shipped*seller_order_exchange_rate) cost_platform_fee,sum(cost_product*bailun_sku_quantity_shipped) cost_product,sum(cost_shipping*bailun_sku_quantity_shipped) cost_shipping,sum(cost_package*bailun_sku_quantity_shipped*seller_order_exchange_rate) cost_package,sum(cost_fba_fee*bailun_sku_quantity_shipped*seller_order_exchange_rate) cost_fba_fee,sum(cost_paypal_fee*bailun_sku_quantity_shipped*seller_order_exchange_rate) cost_paypal_fee,sum(cost_refund_commisson*bailun_sku_quantity_shipped*seller_order_exchange_rate) cost_refund_commisson,sum(cost_handle_bailun*bailun_sku_quantity_shipped) cost_handle_bailun,sum(cost_handle_platform*bailun_sku_quantity_shipped) cost_handle_platform,sum(cost_tail*bailun_sku_quantity_shipped) cost_tail,sum(cost_first*bailun_sku_quantity_shipped) cost_first,sum(profit_total*bailun_sku_quantity_shipped) profit_total,(sum(profit_total)/sum(amount_sales)) profit_rate,create_time,paid_time,seller_order_exchange_rate,seller_other_exchange_rate,sum(amount_prepaid*seller_order_exchange_rate) amount_prepaid from dc_base_oms_sku where bailun_order_status!='Canceled' and bailun_sku_quantity_shipped>0";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                sql += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }
            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }
            if (!string.IsNullOrEmpty(website))
            {
                sql += " and website=@website";
                sqlparam.Add("website", website);
            }
            if (!string.IsNullOrEmpty(orderno))
            {
                sql += " and bailun_order_id=@bailun_order_id";
                sqlparam.Add("bailun_order_id", orderno);
            }
            if (!string.IsNullOrEmpty(account))
            {
                sql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            sql += " group by bailun_order_id ";

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

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

                var obj = cn.Query<Models.Orders.dc_base_oms_order>(sql, sqlparam); //cn.Page<Models.Orders.dc_base_oms_order>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam);

                return obj.AsList();
            }

        }

        /// <summary>
        /// 利润统计订单明细 统计
        /// </summary>
        /// <param name="platform"></param>
        /// <param name="website"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="orderno"></param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_order ListPlatformProfitOrdersCount(string platform, string website,string account, DateTime? start, DateTime? end, string orderno)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select sum(amount_total*seller_order_exchange_rate) amount_total,sum(amount_product*seller_order_exchange_rate) amount_product,sum(amount_shipping*seller_order_exchange_rate) amount_shipping,sum(amount_tax*seller_order_exchange_rate) amount_tax,sum(amount_adjustment*seller_order_exchange_rate) amount_adjustment,sum(amount_gift_wrap*seller_order_exchange_rate) amount_gift_wrap,sum(amount_refund*seller_order_exchange_rate) amount_refund,sum(cost_total) cost_total,sum(cost_promotion*seller_order_exchange_rate) cost_promotion,sum(cost_platform_fee*seller_order_exchange_rate) cost_platform_fee,sum(cost_product) cost_product,sum(cost_shipping) cost_shipping,sum(cost_package) cost_package,sum(cost_fba_fee*seller_order_exchange_rate) cost_fba_fee,sum(cost_paypal_fee*seller_order_exchange_rate) cost_paypal_fee,sum(cost_refund_commisson*seller_order_exchange_rate) cost_refund_commisson,sum(cost_handle_bailun) cost_handle_bailun,sum(cost_handle_platform) cost_handle_platform,sum(cost_tail) cost_tail,sum(cost_first) cost_first,sum(profit_total) profit_total,sum(amount_prepaid) amount_prepaid from dc_base_oms_order where bailun_order_status!='Canceled' ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                sql += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }
            if (start.HasValue)
            {
                sql += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }
            if (!string.IsNullOrEmpty(website))
            {
                sql += " and website=@website";
                sqlparam.Add("website", website);
            }
            if (!string.IsNullOrEmpty(orderno))
            {
                sql += " and bailun_order_id=@bailun_order_id";
                sqlparam.Add("bailun_order_id", orderno);
            }
            if (!string.IsNullOrEmpty(account))
            {
                sql += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

                var obj = cn.QueryFirst<Models.Orders.dc_base_oms_order>(sql,sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 销售订单
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehouse">仓库信息</param>
        /// <param name="seller">销售帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">百伦订单号</param>
        /// <param name="sku">sku编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <param name="isFBA">是否FBA订单</param>
        /// <returns></returns>
        public List<Models.Orders.mSkuOrder> ListSkuOrder(BtTableParameter parameter, string warehousetype, string warehouse, string seller, DateTime? start, DateTime? end, string orderno, string sku,string orderstatus,string shippingstatus, ref int total,int isFBA)
        {
            var sqlparam = new DynamicParameters();
            var sqlwhere = "";
            var sql = @"select t1.transaction_id,t1.bailun_sku,t2.sku_image,t2.category_name,t2.sku_title_en,t2.sku_title_cn,t1.amount_product/t1.bailun_sku_quantity_ordered 'price',t1.bailun_sku_quantity_ordered,t1.amount_product,t1.order_currency,t1.platform_type,t1.website,t1.seller_account,t1.seller_name,t1.bailun_order_id,t1.origin_order_id,t1.bailun_order_status,t1.warehouse_name,t1.shipping_status,t1.create_time,t1.order_update_time,t1.gmt_modified from dc_base_oms_sku t1
                                left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                                left join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                                ";

            sqlwhere += " where t1.bailun_order_status != 'Canceled' ";

            sqlwhere += " and t1.platform_type"+(isFBA==1?"":"!")+"='FBA'";

            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sqlwhere += " and t3.hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);
            }

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

            if (!string.IsNullOrWhiteSpace(seller))
            {
                sqlwhere += " and t1.seller_name=@seller_name";
                sqlparam.Add("seller_name", seller);
            }

            if (start.HasValue)
            {
                sqlwhere += " and t1.paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sqlwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value);
            }
            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sqlwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value);
            }
            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sqlwhere += " and t1.bailun_order_id=@bailun_order_id";
                sqlparam.Add("bailun_order_id", orderno);
            }
            if (!string.IsNullOrWhiteSpace(sku))
            {
                sqlwhere += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (!string.IsNullOrWhiteSpace(orderstatus))
            {
                sqlwhere += " and t1.bailun_order_status=@bailun_order_status";
                sqlparam.Add("bailun_order_status", orderstatus);
            }
            if (!string.IsNullOrWhiteSpace(shippingstatus))
            {
                sqlwhere += " and t1.shipping_status=@shipping_status";
                sqlparam.Add("shipping_status", shippingstatus);
            }

            if (!string.IsNullOrEmpty(parameter.sort))
            {
                sqlwhere += " order by t1." + parameter.sort;

                if (!string.IsNullOrEmpty(parameter.order))
                {
                    sqlwhere += " " + parameter.order;
                }
            }
            else
            {
                sqlwhere += " order by t1.paid_time desc";
            }

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

                var obj = cn.Page<Models.Orders.mSkuOrder>(parameter.pageIndex, parameter.limit, sql+ sqlwhere, ref total, sqlparam, "select count(t1.origin_order_id) from dc_base_oms_sku t1 left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku left join dc_base_warehouse t3 on t1.warehouse_code = t3.warehouse_code" + sqlwhere);

                return obj.AsList();
            }

        }

        /// <summary>
        /// 销售订单 统计
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehouse">仓库信息</param>
        /// <param name="seller">销售帐号</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="orderno">百伦订单号</param>
        /// <param name="sku">sku编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <param name="isFBA">是否FBA订单</param>
        /// <returns></returns>
        public Models.Orders.mSkuOrder ListSkuOrderCount(string warehousetype, string warehouse, string seller, DateTime? start, DateTime? end, string orderno, string sku, string orderstatus, string shippingstatus, int isFBA)
        {
            var sqlparam = new DynamicParameters();
            var sqlwhere = "";
            var sql = @"select sum(t1.bailun_sku_quantity_ordered) bailun_sku_quantity_ordered,sum(t1.amount_product) amount_product from dc_base_oms_sku t1
                                left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                                left join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                                ";

            sqlwhere += " where t1.bailun_order_status != 'Canceled' ";

            sqlwhere += " and t1.platform_type" + (isFBA == 1 ? "" : "!") + "='FBA'";

            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sqlwhere += " and t3.hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);
            }

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

            if (!string.IsNullOrWhiteSpace(seller))
            {
                sqlwhere += " and t1.seller_name=@seller_name";
                sqlparam.Add("seller_name", seller);
            }

            if (start.HasValue)
            {
                sqlwhere += " and t1.paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }
            if (end.HasValue)
            {
                sqlwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value);
            }
            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sqlwhere += " and t1.paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value);
            }
            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sqlwhere += " and t1.bailun_order_id=@bailun_order_id";
                sqlparam.Add("bailun_order_id", orderno);
            }
            if (!string.IsNullOrWhiteSpace(sku))
            {
                sqlwhere += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (!string.IsNullOrWhiteSpace(orderstatus))
            {
                sqlwhere += " and t1.bailun_order_status=@bailun_order_status";
                sqlparam.Add("bailun_order_status", orderstatus);
            }
            if (!string.IsNullOrWhiteSpace(shippingstatus))
            {
                sqlwhere += " and t1.shipping_status=@shipping_status";
                sqlparam.Add("shipping_status", shippingstatus);
            }

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

                try
                {
                    var obj = cn.QueryFirst<Models.Orders.mSkuOrder>(sql + sqlwhere, sqlparam);

                    return obj;
                }
                catch (Exception ex)
                {
                    return new Models.Orders.mSkuOrder();
                }
                
            }
        }

        /// <summary>
        /// 订单汇总 sku维度
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="platform">平台</param>
        /// <param name="account">销售帐号</param>
        /// <param name="website">站点</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="sku">sku编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Orders.mSkuSaleCount> ListSkuSaleCount(BtTableParameter parameter, string platform, string account, string website, string warehouse, DateTime? start, DateTime? end, string sku, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var strwhere = "";
            var sql = "select platform_type,bailun_sku,warehouse_name,count(origin_order_id) ordercount,sum(bailun_sku_quantity_ordered) count,sum(bailun_sku_quantity_shipped) shippingcount from dc_base_oms_sku where bailun_order_status!='Canceled' and warehouse_code!='' ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                strwhere += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }
            
            if (!string.IsNullOrWhiteSpace(website))
            {
                strwhere += " and website=@website";
                sqlparam.Add("website", website);
            }

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (start.HasValue)
            {
                strwhere += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                strwhere += " and bailun_sku>=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (end.HasValue)
            {
                strwhere += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }

            strwhere += "  group by platform_type,bailun_sku,warehouse_name";

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                strwhere += " order by " + parameter.sort;

                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    strwhere += (" " + parameter.order);
                }
            }



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

                var obj = cn.Page<Models.Orders.mSkuSaleCount>(parameter.pageIndex, parameter.limit, sql+strwhere, ref total, sqlparam);

                return obj.AsList();
            }
            
        }

        /// <summary>
        /// 订单汇总 sku维度 统计
        /// </summary>
        /// <param name="platform">分页信息</param>
        /// <param name="account">平台</param>
        /// <param name="website">销售帐号</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="sku">sku编码</param>
        /// <returns></returns>
        public Models.Orders.mSkuSaleCount ListSkuSaleCounts(string platform, string account, string website, string warehouse, DateTime? start, DateTime? end, string sku)
        {
            var sqlparam = new DynamicParameters();
            var strwhere = "";
            var sql = "select count(origin_order_id) ordercount,sum(bailun_sku_quantity_ordered) count,sum(bailun_sku_quantity_shipped) shippingcount from dc_base_oms_sku where bailun_order_status!='Canceled' and warehouse_code!='' ";

            if (!string.IsNullOrWhiteSpace(platform))
            {
                strwhere += " and platform_type=@platform_type";
                sqlparam.Add("platform_type", platform);
            }

            if (!string.IsNullOrWhiteSpace(website))
            {
                strwhere += " and website=@website";
                sqlparam.Add("website", website);
            }

            if (!string.IsNullOrWhiteSpace(account))
            {
                strwhere += " and seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

            if (start.HasValue)
            {
                strwhere += " and paid_time>=@paid_time_start";
                sqlparam.Add("paid_time_start", start.Value);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                strwhere += " and bailun_sku>=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (end.HasValue)
            {
                strwhere += " and paid_time<@paid_time_end";
                sqlparam.Add("paid_time_end", end.Value.AddDays(1));
            }


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

                try
                {
                    var obj = cn.QueryFirst<Models.Orders.mSkuSaleCount>(sql + strwhere, sqlparam);

                    return obj;
                }
                catch (Exception ex)
                {
                    return new Models.Orders.mSkuSaleCount();
                }
                
            }

        }

        #endregion

        #region Ebay

        /// <summary>
        /// Ebay 销售统计记录
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="website">站点信息</param>
        /// <param name="account">帐号信息</param>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <param name="warehousetype">发货仓库类型</param>
        /// <param name="warehousecode">发货仓库编码</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<Models.Orders.dc_base_oms_order> ListEbayStatistics(BtTableParameter parameter, string website, string account, DateTime? start, DateTime? end, string warehousetype, string warehousecode, ref int total)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select platform_type,seller_account,sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'amount_product',sum(cost_paypal_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'cost_paypal_fee',sum(cost_product*bailun_sku_quantity_shipped) 'cost_product',sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'cost_platform_fee',sum(cost_first*bailun_sku_quantity_shipped) 'cost_first',count(t1.bailun_order_id) 'order_count',sum(cost_total*bailun_sku_quantity_shipped) 'cost_total',sum(cost_tail*bailun_sku_quantity_shipped) 'cost_tail',sum(profit_total*bailun_sku_quantity_shipped) 'profit_total',(sum(profit_total*bailun_sku_quantity_shipped)/sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)) 'profit_rate',sum(amount_prepaid) amount_prepaid,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) amount_refund,sum(cost_handle_bailun*bailun_sku_quantity_shipped) cost_handle_bailun  from dc_base_oms_sku t1";
            var presql = "select platform_type,seller_account,website,order_currency,sum(amount_prepaid) as amount_prepaid from dc_base_oms_sku t1 ";

            //var sql = @"select t1.platform_type,t1.seller_account,t1.website,sum(t1.amount_product*t1.seller_order_exchange_rate) 'amount_product',sum(t1.cost_product) 'cost_product',sum(t1.cost_platform_fee*t1.seller_other_exchange_rate) 'cost_platform_fee',sum(t1.cost_first) 'cost_first',count(t1.id) 'order_count',sum(t1.cost_total) 'cost_total',sum(t1.cost_shipping) 'cost_shipping',sum(t1.profit_total) 'profit_total',(sum(t1.profit_total)/sum(t1.amount_total*t1.seller_order_exchange_rate)) 'profit_rate',sum(t1.amount_prepaid) amount_prepaid,sum(t1.amount_refund*t1.seller_order_exchange_rate) amount_refund from dc_base_oms_order t1";
            var sqlwhere = " where  platform_type='Ebay' and bailun_order_status!='Canceled' ";

            if (!string.IsNullOrEmpty(warehousetype) || (!string.IsNullOrEmpty(warehousetype) && !string.IsNullOrEmpty(warehousecode)))
            {
                sql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id " + (!string.IsNullOrEmpty(warehousecode) ? " and t2.warehouse_code=@warehousecode" : "");
                sql += " join  dc_base_warehouse t3 on t2.warehouse_code =t3.warehouse_code and hq_type=@warehousetype";

                presql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id " + (!string.IsNullOrEmpty(warehousecode) ? " and t2.warehouse_code=@warehousecode" : "");
                presql += " join  dc_base_warehouse t3 on t2.warehouse_code =t3.warehouse_code and hq_type=@warehousetype";
                
                sqlparam.Add("warehousetype", warehousetype);
                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sqlparam.Add("warehousecode", warehousecode);
                }
            }
            else if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id and t2.warehouse_code=@warehousecode";
                presql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id and t2.warehouse_code=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }
            

            if (!string.IsNullOrWhiteSpace(account))
            {
                sqlwhere += " and t1.seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

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

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

            var prefromsql = sqlwhere;
            sqlwhere += "  and bailun_sku_quantity_shipped>0";
            sqlwhere += " group by t1.seller_account,t1.platform_type";

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sqlwhere += " order by " + parameter.sort + " " + parameter.order;
            }

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

                var obj = cn.Page<Models.Orders.dc_base_oms_order>(parameter.pageIndex, parameter.limit, sql+sqlwhere, ref total, sqlparam);

                //另外计算预收款金额
                var list = obj.AsList();
                prefromsql += "  and bailun_sku_quantity_shipped<=0";
                prefromsql += " and seller_account in ('" + string.Join("','", list.Select(a => a.seller_account)) + "')";
                prefromsql += "  group by seller_account,platform_type";

                var obj1 = cn.Query<Models.Orders.dc_base_oms_order>(presql + prefromsql, sqlparam);

                foreach (var item in list)
                {
                    var temp = obj1.Where(a => a.seller_account == item.seller_account && a.website == item.website && a.order_currency == item.order_currency && a.platform_type == item.platform_type).FirstOrDefault();
                    if (temp != null)
                    {
                        item.amount_prepaid = temp.amount_prepaid;
                    }
                }
                
                return obj.AsList();
            }
            
        }


        /// <summary>
        /// Ebay 销售统计记录 统计
        /// </summary>
        /// <param name="website"></param>
        /// <param name="account"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="warehousetype"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public Models.Orders.dc_base_oms_order ListEbayStatisticsCount(string website, string account, DateTime? start, DateTime? end, string warehousetype, string warehousecode)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'amount_product',sum(cost_paypal_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'cost_paypal_fee',sum(cost_product*bailun_sku_quantity_shipped) 'cost_product',sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_shipped) 'cost_platform_fee',sum(cost_first*bailun_sku_quantity_shipped) 'cost_first',count(t1.bailun_order_id) 'order_count',sum(cost_total*bailun_sku_quantity_shipped) 'cost_total',sum(cost_tail*bailun_sku_quantity_shipped) 'cost_tail',sum(profit_total*bailun_sku_quantity_shipped) 'profit_total',(sum(profit_total*bailun_sku_quantity_shipped)/sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_shipped)) 'profit_rate',sum(amount_prepaid) amount_prepaid,sum(amount_refund*seller_order_exchange_rate*bailun_sku_quantity_shipped) amount_refund,sum(cost_handle_bailun*bailun_sku_quantity_shipped) cost_handle_bailun  from dc_base_oms_sku t1";
            var presql = "select platform_type,seller_account,website,order_currency,sum(amount_prepaid) as amount_prepaid from dc_base_oms_sku t1 ";

            //var sql = @"select t1.platform_type,t1.seller_account,t1.website,sum(t1.amount_product*t1.seller_order_exchange_rate) 'amount_product',sum(t1.cost_product) 'cost_product',sum(t1.cost_platform_fee*t1.seller_other_exchange_rate) 'cost_platform_fee',sum(t1.cost_first) 'cost_first',count(t1.id) 'order_count',sum(t1.cost_total) 'cost_total',sum(t1.cost_shipping) 'cost_shipping',sum(t1.profit_total) 'profit_total',(sum(t1.profit_total)/sum(t1.amount_total*t1.seller_order_exchange_rate)) 'profit_rate',sum(t1.amount_prepaid) amount_prepaid,sum(t1.amount_refund*t1.seller_order_exchange_rate) amount_refund from dc_base_oms_order t1";
            var sqlwhere = " where  platform_type='Ebay' and bailun_order_status!='Canceled' ";

            if (!string.IsNullOrEmpty(warehousetype) || (!string.IsNullOrEmpty(warehousetype) && !string.IsNullOrEmpty(warehousecode)))
            {
                sql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id " + (!string.IsNullOrEmpty(warehousecode) ? " and t2.warehouse_code=@warehousecode" : "");
                sql += " join  dc_base_warehouse t3 on t2.warehouse_code =t3.warehouse_code and hq_type=@warehousetype";

                presql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id " + (!string.IsNullOrEmpty(warehousecode) ? " and t2.warehouse_code=@warehousecode" : "");
                presql += " join  dc_base_warehouse t3 on t2.warehouse_code =t3.warehouse_code and hq_type=@warehousetype";

                sqlparam.Add("warehousetype", warehousetype);
                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sqlparam.Add("warehousecode", warehousecode);
                }
            }
            else if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id and t2.warehouse_code=@warehousecode";
                presql += " join  dc_base_oms_pick t2 on t1.origin_order_id=t2.origin_order_id and t2.warehouse_code=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }


            if (!string.IsNullOrWhiteSpace(account))
            {
                sqlwhere += " and t1.seller_account=@seller_account";
                sqlparam.Add("seller_account", account);
            }

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

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

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

            var prefromsql = sqlwhere;
            sqlwhere += "  and bailun_sku_quantity_shipped>0";
            //sqlwhere += " group by t1.seller_account,t1.platform_type";

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

                var obj = cn.QueryFirstOrDefault<Models.Orders.dc_base_oms_order>(sql + sqlwhere, sqlparam);

                //另外计算预收款金额
                prefromsql += "  and amount_prepaid>0";
                prefromsql += " ";

                var obj1 = cn.QueryFirstOrDefault<Models.Orders.dc_base_oms_order>(presql + prefromsql, sqlparam);

                if (obj!=null && obj1 != null)
                {
                    obj.amount_prepaid = obj1.amount_prepaid;
                }

                return obj;
            }
        }

        /// <summary>
        /// 获取Ebay UK站点 平邮发货名字
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<Models.Ebay.ebayUKLogistics> ListEbayUKLogistics(DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();
            var sql = $@"select t2.seller_account,t1.* from (select t1.origin_order_id,t1.tracking_order_id,shipping_status,warehouse_code,logistics_order_id,logistics_order_name from dc_base_oms_pick t1
                                join dc_ebay_report_uk_config t4 on t1.logistics_order_id=t4.config_value and t4.type>1
                                where t1.shipping_status='TotalShipping' and t1.shipping_time>='{start.ToString("yyyy-MM-dd")}' and t1.shipping_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'
                                group by t1.origin_order_id,t1.tracking_order_id,shipping_status,warehouse_code,logistics_order_id,logistics_order_name
                                ) t1
                                join dc_base_oms_order t2 on t1.origin_order_id=t2.origin_order_id and t2.website='uk'  and t2.platform_type='Ebay'
                                join dc_ebay_report_uk_config t3 on t2.seller_account=t3.config_value and t3.type=1";

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

                var list = cn.Query<Models.Ebay.ebayUKLogistics>(sql).AsList();

                return list;
            }

        }

        /// <summary>
        /// 获取Ebay UK站点 配置信息
        /// </summary>
        /// <returns></returns>
        public List<Models.Ebay.dc_ebay_report_uk_config> ListEbayReportUKConfig()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                return cn.Query<Models.Ebay.dc_ebay_report_uk_config>("select * from dc_ebay_report_uk_config where type>0").AsList();
            }
        }

        #endregion
    }

}
