using AutoTurnOver.Models;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using AutoTurnOver.Models.ApiDto;
using MySql.Data.MySqlClient;
using System.Text;
using AutoTurnOver.Utility;
using AutoTurnOver.Models.Report;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 
    /// </summary>
    public class dc_base_full_link_wait_order_dao : connectionHelper
    {

        public static void ResetDatas()
        {
            if (DateTime.Now.Hour < 8)
            {
                return;
            }
            List<dc_base_full_link_wait_order_temp> datas = new List<dc_base_full_link_wait_order_temp>();
            datas.AddRange(GetPurchaseOrder());
            datas.AddRange(GetTransferOrder());
            var tempOrders = GetTransferTempOrder();
            datas.AddRange(tempOrders);
            datas.AddRange(GetTransferPlanOrder(tempOrders));
            var conn = MyMySqlConnection._connection;

            conn.Execute(" TRUNCATE table dc_base_full_link_wait_order_temp; ");
            foreach (var item in datas)
            {
                item.update_date = DateTime.Now;
                conn.Insert(item);
            }
            _connection.Execute(@"
alter table dc_base_full_link_wait_order rename dc_base_full_link_wait_orderTemp; 
alter table dc_base_full_link_wait_order_temp rename dc_base_full_link_wait_order; 
alter table dc_base_full_link_wait_orderTemp rename dc_base_full_link_wait_order_temp; 
truncate table dc_base_full_link_wait_order_temp;   
");
        }
        /// <summary>
        /// 查询采购在途订单
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_full_link_wait_order_temp> GetPurchaseOrder(string bailun_sku = null, List<string> purchase_ids = null)
        {
            List<dc_base_full_link_wait_order_temp> datas = new List<dc_base_full_link_wait_order_temp>();
            DynamicParameters parameters = new DynamicParameters();
            var sql = @"select 
t1.bailun_sku as 'bailun_sku',
t1.warehouse_code as 'warehouse_code',
CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code',
t1.purchase_id as 'purchase_no',
t1.create_time as 'purchase_create_time',
t1.estimated_arrival_time as 'purchase_arrivaldate',
t3.last_signtime as 'purchase_sign_time',
max(t2.create_time) as 'purchase_put_time',
t1.`count` as 'purchase_quantity',
t3.quantity_arrival as 'purchase_quantity_arrival',
ifnull( sum(t2.`count`),0)  as 'purchase_quantity_inbound',
t1.`count`  -  ifnull( sum(t2.`count`),0) as 'purchase_quantity_not_inbound',
 (case when t1.transport_type=1 then '铁路运输' when t1.transport_type=2 then '海运' when t1.transport_type=3 then '空运' else '' end ) as 'transfer_transporttype',
t1.buy_status as 'purchase_status'
from dc_base_purchase as t1
            left join dc_base_purchase_inbound t2 on t1.bailun_sku = t2.bailun_sku and t1.parent_id = t2.parent_id
            left join dc_base_purchase_details t3 on t1.bailun_sku = t3.bailun_sku and t1.purchase_id = t3.purchase_id
              where 1=1
              ";
            if (string.IsNullOrWhiteSpace(bailun_sku))
            {
                sql += @" and t1.buy_status in (0, 1, 2, 3, 7, 9)
                and t1.has_delete = 0
                and t1.`count` > 0
                and (t1.`count` > t2.`count` or t2.`count` is null)  ";
            }
            else
            {
                sql += " and t1.bailun_sku=@bailun_sku and t1.purchase_id in @purchase_ids ";
                parameters.Add("bailun_sku", bailun_sku);
                parameters.Add("purchase_ids", purchase_ids);
            }
            sql += "  group by t1.id ";
            datas = _connection.Query<dc_base_full_link_wait_order_temp>(sql, parameters).ToList();
           if (string.IsNullOrWhiteSpace(bailun_sku))
            {
                datas= datas.Where(s => s.purchase_quantity_not_inbound > 0).ToList();
            }
                
            return datas;
        }

        /// <summary>
        /// 查询调拨在途订单
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_full_link_wait_order_temp> GetTransferOrder()
        {
            List<dc_base_full_link_wait_order_temp> datas = new List<dc_base_full_link_wait_order_temp>();
            var sql = @"select 
t2.sku as 'bailun_sku',
t1.targetwareno as 'warehouse_code',
CONCAT(t2.sku,t1.targetwareno) as 'bailun_sku_warehouse_code',
t1.creationtime as 'transfer_create_time',
(t2.deliverycount - t2.storagecount) as 'transfer_quantity_not_inbound',
t1.transporttype as 'transfer_transporttype',
t1.logisticsname as 'transfer_logistics_name',
t1.logisticscode as 'transfer_logistics_code',
(case when t1.expectarrivaltime>'1991-01-01' then t1.expectarrivaltime else null end) as 'transfer_expectarrivaltime',
t2.deliverycount as 'transfer_quantity',
t2.storagecount as 'transfer_put_quantity',
t1.`code` as 'transfer_no',
t1.purchasecode as 'purchase_no'
from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid =t2.dataid
where t1.stateid !=5 and t2.deliverycount > t2.storagecount and t1.isdeleted=0 and t1.isexception=0
 ";
            var temp_datas = _connection.Query<dc_base_full_link_wait_order_temp>(sql).ToList().Where(s => s.transfer_quantity_not_inbound > 0).ToList();

            foreach (var item in temp_datas)
            {
                if (!string.IsNullOrWhiteSpace(item.purchase_no))
                {
                    // 匹配采购单
                  var purchaseOrders =   GetPurchaseOrder(item.bailun_sku, item.purchase_no.Split(' ').ToList());
                    if(purchaseOrders!=null && purchaseOrders.Count >= 1)
                    {
                        foreach (var purchaseItem in purchaseOrders)
                        {
                            var newItem = item.ToJson().ToObj<dc_base_full_link_wait_order_temp>();
                            newItem.purchase_arrivaldate = purchaseItem.purchase_arrivaldate;
                            newItem.purchase_create_time = purchaseItem.purchase_create_time;
                            newItem.purchase_no = purchaseItem.purchase_no;
                            newItem.purchase_put_time = purchaseItem.purchase_put_time;
                            newItem.purchase_quantity = purchaseItem.purchase_quantity;
                            newItem.purchase_quantity_arrival = purchaseItem.purchase_quantity_arrival;
                            newItem.purchase_quantity_inbound = purchaseItem.purchase_quantity_inbound;
                            newItem.purchase_quantity_not_inbound = purchaseItem.purchase_quantity_not_inbound;
                            newItem.purchase_sign_time = purchaseItem.purchase_sign_time;
                            newItem.purchase_status = purchaseItem.purchase_status;
                            datas.Add(newItem);
                        }
                    }
                    else
                    {
                        datas.Add(item);
                    }
                }
                else
                {
                    datas.Add(item);
                }
            }

            return datas;
        }

        /// <summary>
        /// 查询调拨 临时暂存数据
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_full_link_wait_order_temp> GetTransferPlanOrder(List<dc_base_full_link_wait_order_temp> tempOrderDatas)
        {
            List<dc_base_full_link_wait_order_temp> datas = new List<dc_base_full_link_wait_order_temp>();
            var sql = @"select 
t1.purchase_id as 'purchase_no',
t1.bailun_sku as 'bailun_sku',
t1.warehouse_code as 'warehouse_code',
CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code',
t1.count as 'transfer_plan_quantity',
t1.transfer_plan_order_id as 'transfer_plan_no'
from dc_base_transfer_plan as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where  t1.`status`=1 and t2.hq_type not IN ('国内仓') and t1.deleted=0
 ";
            var temp_datas = _connection.Query<dc_base_full_link_wait_order_temp>(sql).ToList().Where(s => s.transfer_plan_quantity > 0).ToList();

            // 剔除临时数据跟暂存数据
            temp_datas = temp_datas.Where(s=> !tempOrderDatas.Any(v=>v.bailun_sku_warehouse_code==s.bailun_sku_warehouse_code 
            && (s.transfer_plan_no==v.transfer_temp_purchase_no|| s.transfer_plan_no == v.transfer_temp_schedule_no))).ToList();

            foreach (var item in temp_datas)
            {
                if (!string.IsNullOrWhiteSpace(item.purchase_no))
                {
                    // 匹配采购单
                    var purchaseOrders = GetPurchaseOrder(item.bailun_sku, item.purchase_no.Split(' ').ToList());
                    if (purchaseOrders != null && purchaseOrders.Count >= 1)
                    {
                        foreach (var purchaseItem in purchaseOrders)
                        {
                            var newItem = item.ToJson().ToObj<dc_base_full_link_wait_order_temp>();
                            newItem.purchase_arrivaldate = purchaseItem.purchase_arrivaldate;
                            newItem.purchase_create_time = purchaseItem.purchase_create_time;
                            newItem.purchase_no = purchaseItem.purchase_no;
                            newItem.purchase_put_time = purchaseItem.purchase_put_time;
                            newItem.purchase_quantity = purchaseItem.purchase_quantity;
                            newItem.purchase_quantity_arrival = purchaseItem.purchase_quantity_arrival;
                            newItem.purchase_quantity_inbound = purchaseItem.purchase_quantity_inbound;
                            newItem.purchase_quantity_not_inbound = purchaseItem.purchase_quantity_not_inbound;
                            newItem.purchase_sign_time = purchaseItem.purchase_sign_time;
                            newItem.purchase_status = purchaseItem.purchase_status;
                            datas.Add(newItem);
                        }
                    }
                    else
                    {
                        datas.Add(item);
                    }
                }
                else
                {
                    datas.Add(item);
                }
            }

            return datas;
        }
        /// <summary>
        /// 查询调拨 临时暂存数据
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_full_link_wait_order_temp> GetTransferTempOrder()
        {
            List<dc_base_full_link_wait_order_temp> datas = new List<dc_base_full_link_wait_order_temp>();
            var sql = @"select 
t1.purchase_code as 'purchase_no', 
t1.sku as 'bailun_sku',
t1.target_ware_no as 'warehouse_code' ,
CONCAT(t1.sku,t1.target_ware_no) as 'bailun_sku_warehouse_code',
(case when t1.create_state=0 then  t1.product_num else 0 end) as 'transfer_temp_purchase_quantity',
(case when t1.create_state=1 then  t1.temp_delivery_count else 0 end) as 'transfer_temp_schedule_quantity',
(case when t1.create_state=0 then  t1.code else '' end) as 'transfer_temp_purchase_no',
(case when t1.create_state=1 then  t1.code else '' end) as 'transfer_temp_schedule_no'
from dc_base_trans_temp_schedule as t1 
left join dc_base_warehouse as t2 on t1.target_ware_no = t2.warehouse_code
where  t1.is_delete=0 and t1.is_temp_data=1 and t1.is_exception=0 and t2.hq_type in ('fba仓') and t1.create_state in (0,1)
 ";
            var temp_datas = _connection.Query<dc_base_full_link_wait_order_temp>(sql).ToList().Where(s => s.transfer_temp_purchase_quantity > 0 || s.transfer_temp_schedule_quantity > 0).ToList();

            foreach (var item in temp_datas)
            {
                if (!string.IsNullOrWhiteSpace(item.purchase_no))
                {
                    // 匹配采购单
                    var purchaseOrders = GetPurchaseOrder(item.bailun_sku, item.purchase_no.Split(' ').ToList());
                    if (purchaseOrders != null && purchaseOrders.Count >= 1)
                    {
                        foreach (var purchaseItem in purchaseOrders)
                        {
                            var newItem = item.ToJson().ToObj<dc_base_full_link_wait_order_temp>();
                            newItem.purchase_arrivaldate = purchaseItem.purchase_arrivaldate;
                            newItem.purchase_create_time = purchaseItem.purchase_create_time;
                            newItem.purchase_no = purchaseItem.purchase_no;
                            newItem.purchase_put_time = purchaseItem.purchase_put_time;
                            newItem.purchase_quantity = purchaseItem.purchase_quantity;
                            newItem.purchase_quantity_arrival = purchaseItem.purchase_quantity_arrival;
                            newItem.purchase_quantity_inbound = purchaseItem.purchase_quantity_inbound;
                            newItem.purchase_quantity_not_inbound = purchaseItem.purchase_quantity_not_inbound;
                            newItem.purchase_sign_time = purchaseItem.purchase_sign_time;
                            newItem.purchase_status = purchaseItem.purchase_status;
                            datas.Add(newItem);
                        }
                    }
                    else
                    {
                        datas.Add(item);
                    }
                }
                else
                {
                    datas.Add(item);
                }
            }

            return datas;
        }

        /// <summary>
        /// 全链路在途订单
        /// </summary>
        /// <param name="search_data"></param>
        /// <param name="categoryModels"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_base_full_link_wait_order_dto> FullLinkWaitPage(dc_base_full_link_search_dto search_data, List<bailun_category_new_dto> categoryModels, ref int total)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (search_data.isSum || search_data.is_warehouse_sum)
            {
                sql = @"
select 
sum(ifnull(t1.purchase_quantity,0)) as 'purchase_quantity',
sum(ifnull(t1.purchase_quantity_arrival,0)) as 'purchase_quantity_arrival',
sum(ifnull(t1.purchase_quantity_inbound,0)) as 'purchase_quantity_inbound',
sum(ifnull(t1.purchase_quantity_not_inbound,0)) as 'purchase_quantity_not_inbound',
sum(ifnull(t1.transfer_quantity_not_inbound,0)) as 'transfer_quantity_not_inbound',
sum(ifnull(t1.transfer_quantity,0)) as 'transfer_quantity',
sum(ifnull(t1.transfer_put_quantity,0)) as 'transfer_put_quantity'


from dc_base_full_link_wait_order as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
";
                if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
                {
                    sql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
                }
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t9  on t1.bailun_sku = t9.bailun_sku ";
                }
                if (search_data.monitor_status != null)
                {
                    sql += " left join dc_auto_config_sku_warehouse as t10 on t1.bailun_sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
                }
                sql += " where  1=1  ";
            }
            else
            {
                sql = @" 
select 
t1.*,
t2.warehouse_name as 'warehouse_name',
t3.sku_title_cn as 'product_title',
t3.buyer_name as 'buyer_name',
t3.product_type_desc as 'product_type_desc',
t4.usable_stock as 'stock',
t6.history_sevenday_sales as 'history_sevenday_sales',
t6.history_fourteenday_sales as 'history_fourteenday_sales',
t6.history_thirtyday_sales as 'history_thirtyday_sales',
t7.type as 'forecast_type',
t8.`status` as 'prod_status',
t8.distribution_quantity as 'prod_distribution_quantity',
( t8.order_quantity - t8.distribution_quantity) as 'prod_not_distribution_quantity',
t8.prod_quantity as 'prod_quantity',
t11.expectarrivaltime as 'lms_expectarrivaltime',
t11.sign_time as 'lms_sign_time',
t11.put_time as 'lms_put_time',
t11.collect_time as 'lms_collect_time',
t11.flight_time as 'lms_flight_time',
t11.transport_time as 'lms_transport_time',
t11.ascan_time as 'lms_ascan_time',
t11.arrival_time as 'lms_arrival_time',
t11.clearance_time as 'lms_clearance_time',
t11.sail_time as 'lms_sail_time',
t11.node_date as 'lms_node_date',
t11.node_name as 'lms_node_name',
t11.transfer_no as 'lms_transfer_no',
t11.trackingno as 'lms_trackingno'

from dc_base_full_link_wait_order as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_base_stock as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_mid_transit as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_auto_turnover as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join dc_auto_forecast_fluctuation as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_base_prod_order  as t8 on t1.bailun_sku = t8.bailun_sku and t1.purchase_no = t8.purchase_no
left join dc_lms_order_transfer_current_node as t11 on t1.transfer_no = t11.system_order_no and t1.bailun_sku = t11.sku


";
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t9  on t1.bailun_sku = t9.bailun_sku ";
                }
                if (search_data.monitor_status != null)
                {
                    sql += " left join dc_auto_config_sku_warehouse as t10 on t1.bailun_sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
                }
                sql += " where  1=1 ";
            }


            var countSql = @" 
 select 
1
from dc_base_full_link_wait_order as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_lms_order_transfer_current_node as t11 on t1.transfer_no = t11.system_order_no and t1.bailun_sku = t11.sku

";
            if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
            {
                countSql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (search_data.has_tort != null)
            {
                countSql += " left join dc_base_tort as t9  on t1.bailun_sku = t9.bailun_sku ";
            }
            if (search_data.monitor_status != null)
            {
                countSql += " left join dc_auto_config_sku_warehouse as t10 on t1.bailun_sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
            }

            countSql += " where  1=1 ";


            if (search_data.f_start_date != null)
            {
                sql += " and t4.`first_inbound_date`>=@f_start_date ";
                countSql += " and t4.`first_inbound_date`>=@f_start_date ";
                parameters.Add("f_start_date", search_data.f_start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (search_data.f_end_date != null)
            {
                sql += " and t4.`first_inbound_date`<=@f_end_date ";
                countSql += " and t4.`first_inbound_date`<=@f_end_date ";
                parameters.Add("f_end_date", search_data.f_end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (search_data.start_date != null && search_data.end_date != null)
            {
                sql += "  and t4.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                countSql += "  and t4.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                parameters.Add("start_date", search_data.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("end_date", search_data.end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (categoryModels != null && categoryModels.Count >= 1)
            {
                sql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
                countSql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
            }
            if (!string.IsNullOrWhiteSpace(search_data.buyer_name))
            {
                sql += " and t3.buyer_name = @buyer_name ";
                countSql += " and t3.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", search_data.buyer_name);
            }
            if (!string.IsNullOrWhiteSpace(search_data.order_no))
            {
                sql += " and t1.transfer_no = @order_no ";
                countSql += " and t1.transfer_no= @order_no ";
                parameters.Add("order_no", search_data.order_no);
            }
            if (!string.IsNullOrWhiteSpace(search_data.order_no2))
            {
                sql += " and t1.purchase_no = @order_no2 ";
                countSql += " and t1.purchase_no= @order_no2 ";
                parameters.Add("order_no2", search_data.order_no2);
            }
            if (search_data.product_type > 0)
            {
                sql += " and t3.product_type = @product_type ";
                countSql += " and t3.product_type = @product_type ";
                parameters.Add("product_type", search_data.product_type);
            }
            if (search_data.monitor_status == 1)
            {
                sql += " and t10.`status`=1 ";
                countSql += " and t10.`status`=1 ";
            }
            if (search_data.monitor_status == 0)
            {
                sql += " and ( t10.`status`=0 or t10.`status` is null ) ";
                countSql += " and ( t10.`status`=0 or t10.`status` is null ) ";
            }
            if (search_data.has_tort != null)
            {
                if (search_data.has_tort == 0)
                {
                    sql += " and t9.id is null ";
                    countSql += " and t9.id is null ";
                }
                else if (search_data.has_tort == 1)
                {
                    sql += " and t9.id is not null ";
                    countSql += " and t9.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and t2.hq_type = @hq_type ";
                countSql += " and t2.hq_type = @hq_type ";

                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and t2.area_id = @area_id ";
                countSql += " and t2.area_id = @area_id ";

                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.searchType > 0 && !string.IsNullOrWhiteSpace(search_data.key_words))
            {
                switch (search_data.searchType)
                {
                    case 1:
                        sql += " and t1.bailun_sku in @bailun_skus ";
                        countSql += " and t1.bailun_sku in @bailun_skus ";
                        parameters.Add("bailun_skus", search_data.key_words.Split(','));
                        break;
                    case 2:
                        sql += " and t1.bailun_sku like @bailun_sku ";
                        countSql += " and t1.bailun_sku like @bailun_sku ";

                        parameters.Add("bailun_sku", $"%{search_data.key_words}%");
                        break;
                    case 3:
                        sql += " and t3.product_inner_code like @product_inner_code ";
                        countSql += " and t3.product_inner_code like @product_inner_code ";

                        parameters.Add("product_inner_code", $"%{search_data.key_words}%");
                        break;
                    case 4:
                        sql += " and t3.sku_title_cn like @sku_title_cn ";
                        countSql += " and t3.sku_title_cn like @sku_title_cn ";

                        parameters.Add("sku_title_cn", $"%{search_data.key_words}%");
                        break;
                    case 5:
                        sql += " and t3.product_inner_code = @product_inner_code ";
                        countSql += " and t3.product_inner_code = @product_inner_code ";
                        parameters.Add("product_inner_code", $"{search_data.key_words}");
                        break;
                }

            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.supplier_name))
            {
                sql += " and t3.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", search_data.supplier_name);
            }
            if (search_data.isSum)
            {
                total = 0;
            }
            else
            {
                sql += "  group by t1.`purchase_no`,t1.transfer_no,t1.bailun_sku,t11.transfer_no ";
                countSql += "  group by t1.`purchase_no`,t1.transfer_no,t1.bailun_sku,t11.transfer_no ";
                if (search_data.limit > 1000)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
                }



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

                    if (!string.IsNullOrEmpty(search_data.order))
                    {
                        sql += " " + search_data.order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + search_data.offset + "," + search_data.limit;
            }
            var obj = _connection.Query<dc_base_full_link_wait_order_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            foreach (var item in obj)
            {
                item.history_sevenday_sales = (item.history_sevenday_sales ?? 0).Round1();
                item.history_fourteenday_sales = (item.history_fourteenday_sales ?? 0).Round1();
                item.history_thirtyday_sales = (item.history_thirtyday_sales ?? 0).Round1();
            }
            return obj;
        }


    }
}
