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

namespace Bailun.DC.Services
{
    /// <summary>
    /// 导出服务
    /// </summary>
    public class ExportServices
    {
        #region 入库

        /// <summary>
        /// 采购入库明细
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<mPurchasePutin> ListPurchasePutin(DateTime start,DateTime end,int companyid,string skucategoryids)
        {
            var sql = $@"select t1.warehouse_code,t1.warehouse_name,t1.bailun_sku,t1.count,t1.update_time,inbound_id as no,t1.purchase_id,t2.price from dc_base_purchase_inbound t1
                            join dc_base_purchase t2 on t1.purchase_id = t2.purchase_id and t1.bailun_sku = t2.bailun_sku and t2.has_transfer = 0 and t2.`status`> 0
                            ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    sql += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        sql += " and t4.category_simple_id in (" + s + ") ";
                    }
                }
            }

            sql += $@" where t1.`status`= 1 and t1.update_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.update_time < '{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'";

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

                var obj = cn.Query<mPurchasePutin>(sql, null, null, true, 2 * 60).AsList();
                return obj;
            }

        }

        /// <summary>
        /// 调拨入库明细
        /// </summary>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="companyid">公司id</param>
        /// <returns></returns>
        public List<mAllotPutin> ListAllotPutin(DateTime start, DateTime end, int companyid,string skucategoryids)
        {
            var sql = $@"select t1.warehouse_code,t2.warehouse_name,t1.bailun_sku,t1.count,t1.transfer_order_id,t1.create_time from dc_base_transfer_inbound t1 
                        join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    sql += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        sql += " and t4.category_simple_id in (" + s + ") ";
                    }
                }
            }

            sql += $@" where t1.deleted=0 and t1.count>0 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' -- and company_id={companyid}";

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

                var obj = cn.Query<mAllotPutin>(sql, null, null, true, 2 * 60).AsList();
                return obj;
            }

        }



        #endregion

        #region 发货

        /// <summary>
        /// 发货明细
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="companyid"></param>
        /// <returns></returns>
        public List<mShipping> ListShipping(DateTime start, DateTime end, int companyid,string skucategoryids)
        {
            var sql = $@"select t1.origin_order_id,t1.pick_order_id,t1.logistics_order_id,t1.shipping_time,t1.warehouse_code,t1.warehouse_name,t1.bailun_sku,t1.quantity_shipped,(t1.quantity_shipped*t2.unit_price) amount from dc_base_oms_pick t1
                        join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                        ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        sql += " and t2.category_simple_id in (" + s + ") ";
                    }
                }
            }

            sql += $@" where t1.company_id={companyid} and t1.has_delete=0 and t1.shipping_status='TotalShipping' and t1.shipping_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'";

            sql += $@" union all
                        select t1.origin_order_id,t1.bailun_order_id as pick_order_id,'' as logistics_order_id,t1.create_time as shipping_time,t1.warehouse_code,t1.warehouse_name,t1.bailun_sku,t1.bailun_sku_quantity_ordered as quantity_shipped,(t1.bailun_sku_quantity_ordered*t1.bailun_sku_unit_price) as amount from dc_base_oms_sku t1 ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {

                    sql += " join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku";
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        sql += " and t2.category_simple_id in (" + s + ") ";
                    }
                }
            }

            sql += $@" where t1.company_id={companyid} and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and t1.bailun_order_status!='Canceled' and t1.has_scalp=0 and t1.has_delete=0 and t1.has_cancle=0 and platform_type='FBA'";

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

                var obj = cn.Query<mShipping>(sql, null, null, true, 2 * 60).AsList();

                return obj;
            }

        }

        #endregion

        #region 采购下单

        /// <summary>
        /// 获取采购下单明细
        /// </summary>
        /// <param name="start">创建开始时间</param>
        /// <param name="end">创建结束时间</param>
        /// <param name="companyid">公司id</param>
        /// <returns></returns>
        public List<mPurchase> ListPurchase(DateTime start, DateTime end, int companyid,string skucategoryids)
        {
            var sql = $@"select t1.warehouse_code,t3.warehouse_name,t1.bailun_sku,t1.count count,(t1.count*t1.price) amount,t1.price,t1.purchase_id,t1.create_time from dc_base_purchase t1 
                        join dc_base_warehouse t3 on t1.warehouse_code = t3.warehouse_code
                        
                        ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    sql += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        sql += " and t4.category_simple_id in (" + s + ") ";
                    }
                }
            }

            sql += $@" where t1.has_delete=0 and t1.status!=-1 and t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'";

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

                var obj = cn.Query<mPurchase>(sql, null, null, true, 2 * 60).AsList();
                return obj;
            }

        }

        #endregion

        #region 半成品采购下单

        /// <summary>
        /// 获取半成品采购下单明细
        /// </summary>
        /// <param name="start">创建开始时间</param>
        /// <param name="end">创建结束时间</param>
        /// <param name="companyid">公司id</param>
        /// <returns></returns>
        public List<mPurchase> ListSemiPurchase(DateTime start, DateTime end, int companyid)
        {
            var sql = $@"select t1.warehouse_from_code as warehouse_code,t1.warehouse_from_name as warehouse_name,t1.bailun_sku,t1.quantity_purchase as count,(quantity_purchase*unit_price) as amount,t1.unit_price as price,t1.purchase_id,t1.create_time from dc_semi_purchase_info t1 where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' and `status`>-1";
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<mPurchase>(sql, null, null, true, 2 * 60).AsList();
                return obj;
            }
        }

        #endregion

    }
}
