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

namespace Bailun.DC.Services
{
    /// <summary>
    /// 库存相关服务
    /// </summary>
    public class InventoryServices
    {

        /// <summary>
        /// Sku库存分析报表列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="sku">sku编码</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="month">月份</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Stock.mSkuStockAnalyze> ListSkuStockAnalyze(BtTableParameter parameter,string sku,string warehouse,DateTime? month,ref int total)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select t0.warehouse_code,t0.warehouse_name,t0.bailun_sku,t0.quantity_begin_month_inventory,t0.quantity_purchase_transit,t0.quantity_transfer_transit,t0.quantity_purchase_inbound,t0.quantity_purchase_inbound*t0.purchase_price as purchase_inbound_amount,t0.quantity_transfer_inbound,t0.quantity_transfer_inbound*t0.purchase_price as transfer_inbound_amount,t0.quantity_damaged,t0.quantity_overflow,t0.quantity_sku_changed,t0.quantity_sales,t0.quantity_end_month_inventory,t0.turnover_rate,t0.turnover_date,t0.inv_age_0to1_month,t0.inv_age_1to3_month,t0.inv_age_3to6_month,t0.inv_age_6to12_month,t0.inv_age_1to2_year,t0.inv_age_2to3_year,t0.inv_age_gt3_year,t0.quantity_instant_inventory,t0.quantity_instant_inventory*t0.purchase_price as  instant_inventory_amount,t0.month_total_exports,t0.month_total_exports*t0.purchase_price as exportamount,t0.quantity_inner_purchase,t0.purchase_price ";

            var sqlwhere = @"from (
                                            select* from dc_month_inventory where warehouse_name <> ''  group by warehouse_code,bailun_sku having MAX(record_time)
                                        ) as t0  where 1 = 1 ";


            if (!string.IsNullOrEmpty(sku))
            {
                sqlwhere += (" and t0.bailun_sku=@sku");
                sqlparam.Add("sku", sku);
            }

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

            if (month.HasValue)
            {
                var monthEnd = month.Value.AddMonths(1);

                sqlwhere += (" and t0.record_time>=@monthstart and t0.record_time<@monthend");
                sqlparam.Add("monthstart", month.Value);
                sqlparam.Add("monthend", monthEnd);
            }

            if (!string.IsNullOrEmpty(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.Stock.mSkuStockAnalyze>(parameter.pageIndex, parameter.limit, sql + sqlwhere, ref total, sqlparam, "select count(t0.id) "+sqlwhere);

                return obj.AsList();

            }
        }

        /// <summary>
        /// Sku库存分析报表 统计
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public Models.Stock.mSkuStockAnalyze ListSkuStockAnalyzeCount(string sku,string warehouse,DateTime? month)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select sum(t0.quantity_purchase_transit) quantity_purchase_transit,sum(t0.quantity_transfer_transit) quantity_transfer_transit,sum(t0.quantity_purchase_inbound) quantity_purchase_inbound,sum(t0.quantity_purchase_inbound*t0.purchase_price) as purchase_inbound_amount,sum(t0.quantity_transfer_inbound) quantity_transfer_inbound,sum(t0.quantity_transfer_inbound*t0.purchase_price) as transfer_inbound_amount,sum(t0.quantity_damaged) as quantity_damaged,sum(t0.quantity_overflow) quantity_overflow,sum(t0.quantity_sku_changed) quantity_sku_changed,sum(t0.quantity_sales) quantity_sales,t0.turnover_rate,t0.turnover_date,sum(t0.inv_age_0to1_month) inv_age_0to1_month,(t0.inv_age_1to3_month) inv_age_1to3_month,sum(t0.inv_age_3to6_month) inv_age_3to6_month,sum(t0.inv_age_6to12_month) inv_age_6to12_month,sum(t0.inv_age_1to2_year) inv_age_1to2_year,sum(t0.inv_age_2to3_year),sum(t0.inv_age_gt3_year) inv_age_gt3_year,sum(t0.quantity_instant_inventory) quantity_instant_inventory,sum(t0.quantity_instant_inventory*t0.purchase_price) as  instant_inventory_amount,sum(t0.month_total_exports) month_total_exports,sum(t0.month_total_exports*t0.purchase_price) as exportamount,sum(t0.quantity_inner_purchase) quantity_inner_purchase ";

            var sqlwhere = @"from (
                                            select* from dc_month_inventory where warehouse_name <> ''  group by warehouse_code,bailun_sku having MAX(record_time)
                                        ) as t0  where 1 = 1 ";


            if (!string.IsNullOrEmpty(sku))
            {
                sqlwhere += (" and t0.bailun_sku=@sku");
                sqlparam.Add("sku", sku);
            }

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

            if (month.HasValue)
            {
                var monthEnd = month.Value.AddMonths(1);

                sqlwhere += (" and t0.record_time>=@monthstart and t0.record_time<@monthend");
                sqlparam.Add("monthstart", month.Value);
                sqlparam.Add("monthend", monthEnd);
            }

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

                try
                {
                    var obj = cn.QueryFirst<Models.Stock.mSkuStockAnalyze>(sql + sqlwhere, sqlparam, null, 20);

                    return obj;
                }
                catch (Exception ex)
                {

                    return new mSkuStockAnalyze();
                }
                

            }

        }

    }
}
