using HH.WMS.Common;
using HH.WMS.Entitys.Common;
/********************************************************************************

** auth： DBS

** date： 2019/1/17 16:59:55

** desc： 尚未编写描述

** Ver.:  V1.0.0

*********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HH.WMS.DAL.AllQuery
{
    public class ReportDal : DapperBaseDAL
    {
        #region 获取KPI报表
        /// <summary>
        /// 获取KPI报表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="total">out 总行数</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] created by 2019/1/3</History>
        public System.Data.DataTable GetKpiList(Entitys.Common.SearchModel searchModel, out long total)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_CREATOR_BY = Util.ToStringInput(searchModel.SearchCondition.CN_S_USER_NAME),
                CN_T_START = Util.ToStringInput(searchModel.SearchCondition.CN_T_START),
                CN_T_END = Util.ToStringInput(searchModel.SearchCondition.CN_T_END)
            };
            string whereSql = "";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";
                whereSql += " CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "' ";
            }
            if (!string.IsNullOrEmpty(v.CN_S_CREATOR_BY))
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";
                whereSql += " CN_S_CREATOR_BY='" + v.CN_S_CREATOR_BY + "' ";
            }
            if (!string.IsNullOrEmpty(v.CN_T_START))
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";
                whereSql += " CN_T_CREATE >=CONVERT(datetime,'" + v.CN_T_START + "-01') ";
            }
            if (!string.IsNullOrEmpty(v.CN_T_END))
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";
                whereSql += " CN_T_CREATE < DATEADD(MONTH,1,CONVERT(datetime,'" + v.CN_T_END + "-01')) ";
            }
            if (whereSql.Length > 0)
                whereSql = " where " + whereSql;
            string sql = @"(select ISNULL(user1,ISNULL(user2,user3)) userName ,ISNULL(create1,ISNULL(create2,create3)) createTime,upQty,ISNULL(upQtyPer,0) upQtyPer,downQty,ISNULL(downQtyPer,0) downQtyPer,recheckQty,ISNULL(recheckQtyPer,0) recheckQtyPer from 
                            ( select CN_S_CREATOR_BY user1,CONVERT(VarChar(7), CN_T_CREATE, 120) create1,SUM(CN_F_QUANTITY) upQty,
                            upQtyPer = cast(SUM(CN_F_QUANTITY)*1.0/(select SUM(CN_F_QUANTITY) from TN_WM_UP_HISTORY " + whereSql + @") as decimal(28,2))*100 from TN_WM_UP_HISTORY 
                            " + whereSql + @" 
                            group by  CN_S_CREATOR_BY ,CONVERT(VarChar(7), CN_T_CREATE, 120)) a
                            full join  
                            ( select CN_S_CREATOR_BY user2,CONVERT(VarChar(7), CN_T_CREATE, 120) create2,SUM(CN_F_QUANTITY) downQty,
                            downQtyPer = cast(SUM(CN_F_QUANTITY)*1.0/(select SUM(CN_F_QUANTITY) from tn_wm_sorting_result " + whereSql + @") as decimal(28,2))*100 from tn_wm_sorting_result
                            " + whereSql + @" 
                             group by  CN_S_CREATOR_BY,CONVERT(VarChar(7), CN_T_CREATE, 120)) b
                             on a.create1=b.create2 and a.user1=b.user2
                            full join 
                            ( select CN_S_CREATOR_BY user3,CONVERT(VarChar(7), CN_T_CREATE, 120) create3,SUM(CN_F_QUANTITY) recheckQty,
                            recheckQtyPer = cast(SUM(CN_F_QUANTITY)*1.0/(select SUM(CN_F_QUANTITY) from TN_WM_ORDER_SORTING_REL " + whereSql + @") as decimal(28,2))*100 from TN_WM_ORDER_SORTING_REL
                            " + whereSql + @" 
                            group by  CN_S_CREATOR_BY,CONVERT(VarChar(7), CN_T_CREATE, 120)) c 
                            on a.create1=c.create3 and a.user1=c.user3) t ";
            return ExecutePagingData(sql, searchModel.PageIndex, searchModel.PageSize, out total, " order by createTime", "");
        }

        public DataTable GetStockCompareReport(SearchModel searchModel, out long total)
        {

            var condition = searchModel.SearchCondition;
            string whereSql = "";
            if (!string.IsNullOrEmpty(condition.CN_T_START.ToString()))
            {
                whereSql = whereSql + " and CN_T_COMARE_DATE >= '" + Util.ToStringInput(condition.CN_T_START) + " 00:00:00'";
            }
            if (!string.IsNullOrEmpty(condition.CN_T_END.ToString()))
            {
                whereSql = whereSql + " and CN_T_COMARE_DATE <= '" + Util.ToStringInput(condition.CN_T_END) + " 23:59:59'";
            }

            string sql = @"(select * from TN_WM_STOCKQTY_COMPARE WHERE 1=1 " + whereSql + @") t";
            return ExecutePagingData(sql, searchModel.PageIndex, searchModel.PageSize, out total, " order by CN_T_COMARE_DATE", "");

        }
        #endregion

        #region 物流公司统计
        /// <summary>
        /// 物流公司统计
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable GetLogisticsReport(SearchModel searchModel, out long total)
        {
            var v = new
            {
                LogisticsFlag = Util.ToStringInput(searchModel.SearchCondition.LogisticsFlag),
                StartTime = Util.ToStringInput(searchModel.SearchCondition.StartTime),
                EndTime = Util.ToStringInput(searchModel.SearchCondition.EndTime),
            };

            string where = string.Empty;
            if (!string.IsNullOrEmpty(v.LogisticsFlag))
            {
                where += " and CN_S_LOGISTICS_FLAG='" + v.LogisticsFlag + "'";
            }
            if (!string.IsNullOrEmpty(v.StartTime))
            {
                where += " and CN_T_CREATE>=CONVERT(datetime,'" + v.StartTime + "-01') ";
            }
            if (!string.IsNullOrEmpty(v.EndTime))
            {
                where += " and CN_T_CREATE<CONVERT(datetime,'" + v.EndTime + "-01') ";
            }

            string sql = @"(select CN_S_LOGISTICS_FLAG,CN_S_LOGISTICS_NAME,
                           sum(case when CN_S_STATE='已完成' then 1 else 0 end) DeliverGoodsNum,
                           sum(case when CN_S_STATE<>'已完成' then 1 else 0 end) StayNum,
                           CONVERT(VarChar(7), CN_T_CREATE, 120) CN_T_CREATE
                           from tn_wm_out_mst where (CN_S_LOGISTICS_FLAG IS NOT NULL AND CN_S_LOGISTICS_FLAG<>'') 
                           " + where + @"
                           group by CN_S_LOGISTICS_FLAG,CN_S_LOGISTICS_NAME,CONVERT(VarChar(7), CN_T_CREATE, 120) ) t";
            return ExecutePagingData(sql, searchModel.PageIndex, searchModel.PageSize, out total, " order by CN_T_CREATE", "");
        }
        #endregion

        #region 物流出库详情
        /// <summary>
        /// 物流出库详情
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public DataTable GetLogisticsDetail(SearchModel searchModel)
        {
            var v = new
            {
                LogisticsFlag = Util.ToStringInput(searchModel.SearchCondition.LogisticsFlag),
                StartTime = Util.ToStringInput(searchModel.SearchCondition.StartTime),
                EndTime = Util.ToStringInput(searchModel.SearchCondition.EndTime),
            };
            string where = string.Empty;
            if (!string.IsNullOrEmpty(v.LogisticsFlag))
            {
                where += " and CN_S_LOGISTICS_FLAG='" + v.LogisticsFlag + "'";
            }
            if (!string.IsNullOrEmpty(v.StartTime))
            {
                where += " and CN_T_CREATE>=CONVERT(datetime,'" + v.StartTime + "-01') ";
            }
            if (!string.IsNullOrEmpty(v.EndTime))
            {
                where += " and CN_T_CREATE<CONVERT(datetime,'" + v.EndTime + "-01') ";
            }
            string sql = @"SELECT 
                           CN_S_LOGISTICS_FLAG,
                           CN_S_LOGISTICS_NAME,
                           CN_S_WAYBILL_NO,
                           CN_S_CUSTOMER_NAME,
                           CN_S_RECEIVER_NAME,
                           CN_S_DETAIL_ADDRESS,
                           CN_T_CREATE,
                           ISNULL((SELECT COUNT(1) FROM TN_WM_B_OUT_PACK P WHERE
                           EXISTS(SELECT * FROM  TN_WM_B_BOX_DTL WHERE P.CN_S_PACKING_CODE=CN_S_PACKING_CODE ) AND CN_S_OP_NO =T.CN_S_OP_NO
                           GROUP by CN_S_OP_NO),0) PackNum
                           FROM TN_WM_OUT_MST T WHERE  (CN_S_LOGISTICS_FLAG IS NOT NULL AND CN_S_LOGISTICS_FLAG<>'')  " + where;
            return ExecuteDataTable(sql);
        }
        #endregion
    }
}