using HH.WMS.Common;
using HH.WMS.DAL.Basic;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Common;
using HH.WMS.Entitys.wxys;
using Newtonsoft.Json;
/********************************************************************************

** auth： DBS

** date： 2018/11/27 14:39:53

** 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 InventoryDal : DapperBaseDAL
    {
        #region 查询出入库业务
        /// <summary>
        /// 查询出入库业务
        /// </summary>
        /// <param name="pageIndex">分页索引</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="total">out 总行数</param>
        /// <param name="where">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        /// <History>[Hanhe(dbs)] created 2018/12/2</History>
        public DataTable GetOrderBussList(int pageIndex, int pageSize, out long total, object where = null, string orderBy = "")
        {
            string sql = @"(SELECT INOUT='IN',CN_S_STOCK_CODE,CN_S_OP_TYPE,a.CN_S_OP_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,
                            b.CN_S_LOT_NO,CN_F_QUANTITY,a.CN_S_OWNER,CN_F_PLAN_MONEY,CN_F_PLAN_PRICE,CN_F_ACTUAL_MONEY,CN_F_ACTUAL_PRICE,CN_S_MODEL,
                            CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,b.CN_T_CREATE,b.CN_S_CREATOR_BY
                            FROM   TN_WM_INCREASE_INVENTORY_DTL a join TN_WM_INCREASE_INVENTORY_MST b on a.CN_S_OP_NO=b.CN_S_OP_NO
                            union all
                            SELECT INOUT='OUT',CN_S_STOCK_CODE,CN_S_OP_TYPE,a.CN_S_OP_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,a.CN_S_LOT_CODE,CN_F_QUANTITY,CN_S_OWNER,CN_F_PLAN_MONEY
                            ,CN_F_PLAN_PRICE,CN_F_SALE_MONEY,CN_F_SALE_PRICE,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,b.CN_T_CREATE,b.CN_S_CREATOR_BY
                            FROM TN_WM_REDUCE_INVENTORY_DTL a join TN_WM_REDUCE_INVENTORY_MST b on a.CN_S_OP_NO=b.CN_S_OP_NO) as a " + where;
            return ExecutePagingData(sql, pageIndex, pageSize, out total, orderBy, where);
        }
        #endregion

        #region 汇总指定日期内的出入库业务量
        /// <summary>
        /// 汇总指定日期内的出入库业务量
        /// </summary>
        /// <param name="day">天数</param>
        /// <returns></returns>
        /// <History>[Hanhe(dbs)] created 2018-12-21</History>
        public DataTable GetBussLine(int day)
        {
            string startDate = DateTime.Now.AddDays(-(day + 1)).ToShortDateString();
            string endDate = DateTime.Now.AddDays(1).ToShortDateString();
            string sql = @"select 'type'='IN',CN_S_OP_TYPE,CONVERT(varchar(100),CN_T_CREATE, 23) CN_T_CREATE,COUNT(CN_GUID) qty from TN_WM_INCREASE_INVENTORY_MST where CN_T_CREATE >  CONVERT(datetime,'" + startDate + "') and CN_T_CREATE < CONVERT(datetime,'" + endDate + @"') group by CN_S_OP_TYPE,CONVERT(varchar(100),CN_T_CREATE, 23)
                           union all
                           select 'type'='OUT',CN_S_OP_TYPE,CONVERT(varchar(100),CN_T_CREATE, 23) CN_T_CREATE,COUNT(CN_GUID) qty from TN_WM_REDUCE_INVENTORY_MST where CN_T_CREATE >  CONVERT(datetime,'" + startDate + "') and CN_T_CREATE < CONVERT(datetime,'" + endDate + @"') group by CN_S_OP_TYPE,CONVERT(varchar(100),CN_T_CREATE, 23)";

            return ExecuteDataTable(sql);
        }
        #endregion

        #region 获取货位的历史上下架记录
        /// <summary>
        /// 获取货位的历史上下架记录
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="total">out 总行数</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] CREATED BY 2018-12-03</History>
        public DataTable GetLocationHistory(Entitys.Common.SearchModel searchModel, out long total)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
                CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
                CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL),
                CN_S_FROM_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
                CN_S_PRODUCTION_BATCH = Util.ToStringInput(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
                CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
                CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
                CN_S_TRAY_CODE = Util.ToString(searchModel.SearchCondition.CN_S_TRAY_CODE), 
                CN_S_LOT_NO = Util.ToString(searchModel.SearchCondition.CN_S_LOT_NO),
                PageIndex = Util.ToInt(searchModel.PageIndex),
                PageSize = Util.ToInt(searchModel.PageSize)
            };
            string sqlWhere = " WHERE 1=1 ";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE)) //仓库编码
            {
                sqlWhere += " AND CN_S_STOCK_CODE = '" + v.CN_S_STOCK_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE)) //仓库编码
            {
                sqlWhere += " AND CN_S_LOCATION_CODE = '" + v.CN_S_LOCATION_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_FROM_NO)) //物料编码
            {
                sqlWhere += " AND CN_S_FROM_NO = '" + v.CN_S_FROM_NO + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE)) //物料编码
            {
                sqlWhere += " AND CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME)) //物料名称
            {
                sqlWhere += " AND CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOT_NO)) //指令批号
            {
                sqlWhere += " AND CN_S_LOT_NO like '%" + v.CN_S_LOT_NO + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_MODEL)) //规格
            {
                sqlWhere += " AND CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_T_START))
            {
                sqlWhere += " AND convert(date,b.CN_T_CREATE) >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "'";
            }

            if (!string.IsNullOrEmpty(v.CN_T_END))
            {
                sqlWhere += " AND convert(date,b.CN_T_CREATE) <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_TRAY_CODE)) //托盘码
            {
                sqlWhere += " AND CN_S_TRAY_CODE like '%" + v.CN_S_TRAY_CODE + "%'";
            }
            string orderBy = " order by CN_T_CREATE desc";

            string sql = @"(SELECT 'type'='+',
                        CN_S_STOCK_CODE,CN_S_STOCK_AREA,
	                      CN_S_OP_FROM
	                      ,CN_S_FROM_NO
	                      ,CN_S_LOCATION_CODE
                          ,CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_ITEM_STATE
                          ,CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,CN_S_CREATOR_BY
                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
                        FROM TN_WM_UP_HISTORY b " + sqlWhere + @"
                    union all
                        SELECT 'type'='-',
                        CN_S_STOCK_CODE,CN_S_STOCK_AREA,
	                      CN_S_OP_FROM
	                      ,CN_S_FROM_NO
	                      ,CN_S_LOCATION_CODE
                          ,CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_ITEM_STATE
                          ,CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,CN_S_CREATOR_BY
                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
                        FROM TN_WM_DOWN_HISTORY b  " + sqlWhere + ") as t ";
            return ExecutePagingData(sql, v.PageIndex, v.PageSize, out total, orderBy, "");
        }
        #endregion
        #region 获取货位的历史上下架记录
        /// <summary>
        /// 获取货位的历史上下架记录
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="total">out 总行数</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] CREATED BY 2018-12-03</History>
        public DataTable GetLocationHistoryJx(Entitys.Common.SearchModel searchModel, out long total)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
                CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
                CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL),
                CN_S_FROM_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
                CN_S_OP_TYPE = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_TYPE),
                CN_S_PRODUCTION_BATCH = Util.ToStringInput(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
                CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
                CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
                CN_S_TRAY_CODE = Util.ToString(searchModel.SearchCondition.CN_S_TRAY_CODE),
                CN_S_LOT_NO = Util.ToString(searchModel.SearchCondition.CN_S_LOT_NO),
                PageIndex = Util.ToInt(searchModel.PageIndex),
                PageSize = Util.ToInt(searchModel.PageSize)
            };
            string sqlWhere = " WHERE 1=1 ";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE)) //仓库编码
            {
                sqlWhere += " AND CN_S_STOCK_CODE = '" + v.CN_S_STOCK_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE)) //仓库编码
            {
                sqlWhere += " AND CN_S_LOCATION_CODE = '" + v.CN_S_LOCATION_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_FROM_NO)) //物料编码
            {
                sqlWhere += " AND CN_S_FROM_NO = '" + v.CN_S_FROM_NO + "'";
            }
            //if (!string.IsNullOrEmpty(v.CN_S_OP_TYPE)) //操作类型
            //{
            //    sqlWhere += " AND CN_S_OP_TYPE = '" + v.CN_S_OP_TYPE + "'";
            //}
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE)) //物料编码
            {
                sqlWhere += " AND CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME)) //物料名称
            {
                sqlWhere += " AND CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOT_NO)) //指令批号
            {
                sqlWhere += " AND CN_S_LOT_NO like '%" + v.CN_S_LOT_NO + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_MODEL)) //规格
            {
                sqlWhere += " AND CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_T_START))
            {
                sqlWhere += " AND convert(date,b.CN_T_CREATE) >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "'";
            }

            if (!string.IsNullOrEmpty(v.CN_T_END))
            {
                sqlWhere += " AND convert(date,b.CN_T_CREATE) <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_TRAY_CODE)) //托盘码
            {
                sqlWhere += " AND CN_S_TRAY_CODE like '%" + v.CN_S_TRAY_CODE + "%'";
            }
            string orderBy = " order by CN_T_CREATE desc";
            string sql = string.Empty;
            if(v.CN_S_OP_TYPE == "")
            {
                sql = @"(SELECT 'type'='+',
                        CN_S_STOCK_CODE,CN_S_STOCK_AREA,
	                      CN_S_OP_FROM
	                      ,CN_S_FROM_NO
                          ,'入库' AS CN_S_OP_TYPE
	                      ,CN_S_LOCATION_CODE
                          ,CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_ITEM_STATE
                          ,CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,CN_S_CREATOR_BY
                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
                        FROM TN_WM_UP_HISTORY b " + sqlWhere + @"
                    union all
                        SELECT 'type'='-',
                        CN_S_STOCK_CODE,CN_S_STOCK_AREA,
	                      CN_S_OP_FROM
	                      ,CN_S_FROM_NO
                          ,'出库' AS CN_S_OP_TYPE
	                      ,CN_S_LOCATION_CODE
                          ,CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_ITEM_STATE
                          ,CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,CN_S_CREATOR_BY
                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
                        FROM TN_WM_DOWN_HISTORY b  " + sqlWhere + ") as t ";
            }
            else if (v.CN_S_OP_TYPE == "入库")
            {
                sql = @"(SELECT 'type'='+',
                        CN_S_STOCK_CODE,CN_S_STOCK_AREA,
	                      CN_S_OP_FROM
	                      ,CN_S_FROM_NO
                          ,'入库' AS CN_S_OP_TYPE
	                      ,CN_S_LOCATION_CODE
                          ,CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_ITEM_STATE
                          ,CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,CN_S_CREATOR_BY
                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
                        FROM TN_WM_UP_HISTORY b " + sqlWhere +  ") as t ";
            }
            else
            {
                sql = @"(
                        SELECT 'type'='-',
                          CN_S_STOCK_CODE,CN_S_STOCK_AREA,
	                      CN_S_OP_FROM
	                      ,CN_S_FROM_NO
                          ,'出库' AS CN_S_OP_TYPE
	                      ,CN_S_LOCATION_CODE
                          ,CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_ITEM_STATE
                          ,CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,CN_S_CREATOR_BY
                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
                        FROM TN_WM_DOWN_HISTORY b  " + sqlWhere + ") as t ";
            }
           
            return ExecutePagingData(sql, v.PageIndex, v.PageSize, out total, orderBy, "");
        }
        #endregion

        public DataTable GetInventorySummary(Entitys.Common.SearchModel searchModel, out long total)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
                CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
                CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL),
                CN_S_FROM_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
                CN_S_PRODUCTION_BATCH = Util.ToStringInput(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
                CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
                CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
                CN_S_TRAY_CODE = Util.ToString(searchModel.SearchCondition.CN_S_TRAY_CODE),
                CN_S_EXT2 = Util.ToString(searchModel.SearchCondition.CN_S_EXT2),
                PageIndex = Util.ToInt(searchModel.PageIndex),
                PageSize = Util.ToInt(searchModel.PageSize)
            };
            string sqlWhere = " WHERE 1=1 ";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE)) //仓库编码
            {
                sqlWhere += " AND CN_S_STOCK_CODE = '" + v.CN_S_STOCK_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_EXT2)) //凭证性质
            {
                sqlWhere += " AND CN_S_EXT2 = '" + v.CN_S_EXT2 + "'";
            }
            //if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE)) //仓库编码
            //{
            //    sqlWhere += " AND CN_S_LOCATION_CODE = '" + v.CN_S_LOCATION_CODE + "'";
            //}
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE)) //物料编码
            {
                sqlWhere += " AND CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME)) //物料名称
            {
                sqlWhere += " AND CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_MODEL)) //规格
            {
                sqlWhere += " AND CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_T_START))
            {
                sqlWhere += " AND convert(date,A.CN_T_CREATE) >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "'";
            }

            if (!string.IsNullOrEmpty(v.CN_T_END))
            {
                sqlWhere += " AND convert(date,A.CN_T_CREATE) <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "'";
            }
            string orderBy = " order by CN_S_ITEM_CODE desc";

            string sql = @"(SELECT 
                                A.CN_S_STOCK_CODE,A.CN_S_EXT2,A.CN_S_ITEM_CODE,A.CN_S_ITEM_NAME,SUM(A.CN_F_PACKING_QTY) AS CN_F_PACKING_QTY,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO 
                            FROM  
                                (SELECT CN_S_STOCK_CODE,CN_S_EXT2,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_F_QUANTITY,CN_F_PACKING_QTY,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO,CN_T_CREATE FROM TN_WM_UP_HISTORY WHERE CN_S_EXT2 IS NOT NULL AND CN_S_EXT2!=''
                            UNION ALL
                                SELECT CN_S_STOCK_CODE,CN_S_EXT2,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_F_QUANTITY,CN_F_PACKING_QTY,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO,CN_T_CREATE FROM TN_WM_DOWN_HISTORY WHERE CN_S_EXT2 IS NOT NULL AND CN_S_EXT2!='' ) A " + sqlWhere +
                            @"GROUP BY
                                CN_S_EXT2,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_STOCK_CODE,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO) as t ";
            return ExecutePagingData(sql, v.PageIndex, v.PageSize, out total, orderBy, "");
        }

        public DataTable ExportInventorySummary(Entitys.Common.SearchModel searchModel)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
                CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
                CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL),
                CN_S_FROM_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
                CN_S_PRODUCTION_BATCH = Util.ToStringInput(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
                CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
                CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
                CN_S_TRAY_CODE = Util.ToString(searchModel.SearchCondition.CN_S_TRAY_CODE),
                CN_S_EXT2 = Util.ToString(searchModel.SearchCondition.CN_S_EXT2)
            };
            string sqlWhere = " WHERE 1=1 ";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE)) //仓库编码
            {
                sqlWhere += " AND CN_S_STOCK_CODE = '" + v.CN_S_STOCK_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_EXT2)) //凭证性质
            {
                sqlWhere += " AND CN_S_EXT2 = '" + v.CN_S_EXT2 + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE)) //物料编码
            {
                sqlWhere += " AND CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME)) //物料名称
            {
                sqlWhere += " AND CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_MODEL)) //规格
            {
                sqlWhere += " AND CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_T_START))
            {
                sqlWhere += " AND convert(date,A.CN_T_CREATE) >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "'";
            }

            if (!string.IsNullOrEmpty(v.CN_T_END))
            {
                sqlWhere += " AND convert(date,A.CN_T_CREATE) <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "'";
            }
            string orderBy = " order by CN_S_ITEM_CODE desc";

            string sql = @" SELECT 
                                A.CN_S_STOCK_CODE,A.CN_S_EXT2,A.CN_S_ITEM_CODE,A.CN_S_ITEM_NAME,SUM(A.CN_F_PACKING_QTY) AS CN_F_PACKING_QTY,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO 
                            FROM  
                                (SELECT CN_S_STOCK_CODE,CN_S_EXT2,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_F_QUANTITY,CN_F_PACKING_QTY,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO,CN_T_CREATE FROM TN_WM_UP_HISTORY WHERE CN_S_EXT2 IS NOT NULL AND CN_S_EXT2!='' 
                            UNION ALL
                                SELECT CN_S_STOCK_CODE,CN_S_EXT2,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_F_QUANTITY,CN_F_PACKING_QTY,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO,CN_T_CREATE FROM TN_WM_DOWN_HISTORY WHERE CN_S_EXT2 IS NOT NULL AND CN_S_EXT2!='' ) A " + sqlWhere +
                            @"GROUP BY
                                CN_S_EXT2,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_STOCK_CODE,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,CN_S_LOT_NO ";
            return ExecuteDataTable(sql);
        }

        public DataTable GetHistory(SearchModel searchModel)
        {
            var orderType = Util.ToString(searchModel.SearchCondition.CN_S_ORDER_TYPE);
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
                CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
                CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL),
                CN_S_FROM_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
                CN_S_PRODUCTION_BATCH = Util.ToStringInput(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
                CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
                CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
                CN_S_TRAY_CODE = Util.ToString(searchModel.SearchCondition.CN_S_TRAY_CODE),
                CN_S_ORDER_NO = Util.ToString(searchModel.SearchCondition.CN_S_ORDER_NO),
                PageIndex = 1,//Util.ToInt(searchModel.PageIndex),
                PageSize = 100//Util.ToInt(searchModel.PageSize)
            };
            string sqlWhere = " WHERE 1=1 ";

            //汇总
            //select CN_S_ITEM_CODE,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,CN_S_STOCK_CODE  
            //,CONVERT(varchar(100), CN_T_CREATE, 23) AS CN_T_CREATE
            //,CN_S_TRAY_CODE,CN_S_LOCATION_CODE,CN_S_ITEM_NAME,CN_S_MODEL
            //,CN_S_FROM_NO,CN_S_PRODUCTION_BATCH,CN_S_OP_FROM,CN_S_LOT_NO,CN_S_CREATOR_BY
            //from TN_WM_UP_HISTORY 
            //GROUP BY CN_S_ITEM_CODE,CN_S_STOCK_CODE,CONVERT(varchar(100), CN_T_CREATE, 23),CN_S_TRAY_CODE,CN_S_LOCATION_CODE
            //,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_FROM_NO,CN_S_PRODUCTION_BATCH,CN_S_OP_FROM,CN_S_LOT_NO,CN_S_CREATOR_BY

            if (!string.IsNullOrEmpty(v.CN_S_ORDER_NO)) //单据号
            {
                sqlWhere += " AND CN_S_FROM_NO like '%" + v.CN_S_ORDER_NO + "%'";
            }

            if (!string.IsNullOrEmpty(v.CN_S_TRAY_CODE)) //托盘
            {
                sqlWhere += " AND CN_S_TRAY_CODE like '%" + v.CN_S_TRAY_CODE + "%'";
            }

            if (!string.IsNullOrEmpty(v.CN_T_START)) //开始时间
            {
                sqlWhere += " AND CN_T_CREATE>='" + v.CN_T_START + "'";
            }

            if (!string.IsNullOrEmpty(v.CN_T_END)) //结束时间
            {
                var end = ((DateTime)Convert.ToDateTime(v.CN_T_END)).AddDays(1);
                sqlWhere += " AND CN_T_CREATE<='" + end + "'";
            }

            string groupBy = @" GROUP BY CN_S_ITEM_CODE
                                ,CN_S_STOCK_CODE
                                ,CONVERT(varchar(100), CN_T_CREATE, 23)
                                ,CN_S_TRAY_CODE
                                ,CN_S_LOCATION_CODE
                                ,CN_S_ITEM_NAME
                                ,CN_S_MODEL
                                ,CN_S_FROM_NO
                                ,CN_S_PRODUCTION_BATCH
                                ,CN_S_OP_FROM
                                ,CN_S_LOT_NO
                                ,CN_S_CREATOR_BY
                                ,CN_S_EXT2,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT ";
            string orderBy = "order by CN_T_CREATE desc";

            string upSql = @" SELECT SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_PACKING_QTY) AS CN_F_PACKING_QTY
                                ,CN_S_ITEM_CODE
                                ,CN_S_STOCK_CODE  
                                ,CONVERT(varchar(100), CN_T_CREATE, 23) AS CN_T_CREATE
                                ,CN_S_TRAY_CODE
                                ,CN_S_LOCATION_CODE
                                ,CN_S_ITEM_NAME,CN_S_MODEL
                                ,CN_S_FROM_NO
                                ,CN_S_PRODUCTION_BATCH
                                ,CN_S_OP_FROM
                                ,CN_S_LOT_NO
                                ,CN_S_CREATOR_BY
                                ,CN_S_EXT2,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT
                            FROM TN_WM_UP_HISTORY ";
//                        @"SELECT 
//                            CN_GUID,
//                        ROW_NUMBER() OVER (ORDER BY CN_GUID ASC) AS CN_N_ROW_NO
//                          ,'type'='+'
//                          ,CN_S_STOCK_CODE
//                          ,CN_S_STOCK_AREA
//                          ,CN_S_OP_FROM
//	                      ,CN_S_FROM_NO
//	                      ,CN_S_LOCATION_CODE
//                          ,CN_S_TRAY_CODE
//                          ,CN_S_OWNER
//                          ,CN_S_ITEM_CODE
//                          ,CN_S_ITEM_NAME
//                          ,CN_S_MODEL
//                          ,CN_S_FIGURE_NO
//                          ,CN_S_ITEM_STATE
//                          ,CN_F_QUANTITY
//                          ,CN_S_MEASURE_UNIT
//                          ,CN_S_PRODUCTION_BATCH
//                          ,CN_S_CREATOR_BY
//                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY
//                          ,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
//                        FROM TN_WM_UP_HISTORY";
            string downSql = @" SELECT SUM(CN_F_PACKING_QTY) AS CN_F_QUANTITY,SUM(CN_F_PACKING_QTY) AS CN_F_PACKING_QTY
                                ,CN_S_ITEM_CODE
                                ,CN_S_STOCK_CODE  
                                ,CONVERT(varchar(100), CN_T_CREATE, 23) AS CN_T_CREATE
                                ,CN_S_TRAY_CODE
                                ,CN_S_LOCATION_CODE
                                ,CN_S_ITEM_NAME,CN_S_MODEL
                                ,CN_S_FROM_NO
                                ,CN_S_PRODUCTION_BATCH
                                ,CN_S_OP_FROM
                                ,CN_S_LOT_NO
                                ,CN_S_CREATOR_BY
                                ,CN_S_EXT2,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT
                            FROM TN_WM_DOWN_HISTORY ";
//                @"SELECT 
//                            CN_GUID,
//                        ROW_NUMBER() OVER (ORDER BY CN_GUID ASC) AS CN_N_ROW_NO,  
//                        'type'='-',
//                        CN_S_STOCK_CODE,CN_S_STOCK_AREA,
//	                      CN_S_OP_FROM
//	                      ,CN_S_FROM_NO
//	                      ,CN_S_LOCATION_CODE
//                          ,CN_S_TRAY_CODE
//                          ,CN_S_OWNER
//                          ,CN_S_ITEM_CODE
//                          ,CN_S_ITEM_NAME
//                          ,CN_S_MODEL
//                          ,CN_S_FIGURE_NO
//                          ,CN_S_ITEM_STATE
//                          ,CN_F_QUANTITY
//                          ,CN_S_MEASURE_UNIT
//                          ,CN_S_PRODUCTION_BATCH
//                          ,CN_S_CREATOR_BY
//                          ,CN_T_CREATE,ISNULL(CN_F_PACKING_QTY,0) CN_F_PACKING_QTY,CN_S_EXT1,CN_S_EXT2,CN_S_LOT_NO
//                        FROM TN_WM_DOWN_HISTORY";
            var sql = "";

            if (orderType == "产品入库单")
            {
                sql = upSql + sqlWhere + " AND CN_S_EXT2='正常入库' ";
            }
            else if (orderType == "生产领料单")
            {
                sql = downSql + sqlWhere + " AND CN_S_EXT2='正常出库' ";
            }
            else if (orderType == "退库单")
            {
                sql = upSql + sqlWhere + " AND CN_S_EXT2='退料入库' ";
            }
            else if (orderType == "其他出库单")
            {
                sql = downSql + sqlWhere + " AND CN_S_EXT2='其他出库' ";
            }
            else if (orderType == "其他入库单")
            {
                sql = upSql + sqlWhere + " AND CN_S_EXT2='其他入库' ";
            }
            else
            {
                sql = upSql;
            }

            return ExecuteDataTable(sql + groupBy + orderBy);
        }

        public OperateResult SetPrintNo(dynamic data)
        {
            //string guids = Util.ToString(data.guids);
            string orderNo = Util.ToString(data.orderNo);
            string orderType = Util.ToString(data.orderType);
            string userName = Util.ToString(data.userName);
            string workCenter = Util.ToString(data.workCenter);
            List<SetOrderPrintEntity> orderPrints = JsonConvert.DeserializeObject<List<SetOrderPrintEntity>>(Util.ToString(data.setOrderPrints));
            
            var sql = "";
            foreach (var orderPrint in orderPrints)
            {
                if (orderType == "产品入库单")
                {
                    sql += string.Format(@" UPDATE TN_WM_UP_HISTORY SET CN_S_FROM_NO='{0}',
                                            CN_T_MODIFY='{1}',
                                            CN_S_MODIFY='{2}', 
                                            CN_S_MODIFY_BY='{2}',
                                            CN_S_PRODUCTION_BATCH='{3}' 
                                            WHERE CN_S_ITEM_CODE='{4}' AND CN_S_LOCATION_CODE='{5}' AND CN_S_LOT_NO='{6}' AND CN_S_FIGURE_NO='{7}' "
                        , orderNo, DateTime.Now, userName, workCenter, orderPrint.CN_S_ITEM_CODE
                        , orderPrint.CN_S_LOCATION_CODE, orderPrint.CN_S_LOT_NO, orderPrint.CN_S_FIGURE_NO);
                }
                else if (orderType == "生产领料单")
                {
                    sql += string.Format(@" UPDATE TN_WM_DOWN_HISTORY SET CN_S_FROM_NO='{0}',
                                            CN_T_MODIFY='{1}',
                                            CN_S_MODIFY='{2}', 
                                            CN_S_MODIFY_BY='{2}',
                                            CN_S_PRODUCTION_BATCH='{3}'  
                                            WHERE CN_S_ITEM_CODE='{4}' AND CN_S_LOCATION_CODE='{5}' AND CN_S_LOT_NO='{6}' AND CN_S_FIGURE_NO='{7}' "
                        , orderNo, DateTime.Now, userName, workCenter, orderPrint.CN_S_ITEM_CODE
                        , orderPrint.CN_S_LOCATION_CODE, orderPrint.CN_S_LOT_NO, orderPrint.CN_S_FIGURE_NO);
                }
                else if (orderType == "退库单")
                {
                    sql += string.Format(@" UPDATE TN_WM_UP_HISTORY SET CN_S_FROM_NO='{0}',
                                            CN_T_MODIFY='{1}',
                                            CN_S_MODIFY='{2}', 
                                            CN_S_MODIFY_BY='{2}',
                                            CN_S_PRODUCTION_BATCH='{3}' 
                                            WHERE CN_S_ITEM_CODE='{4}' AND CN_S_LOCATION_CODE='{5}' AND CN_S_LOT_NO='{6}' AND CN_S_FIGURE_NO='{7}' "
                        , orderNo, DateTime.Now, userName, workCenter, orderPrint.CN_S_ITEM_CODE
                        , orderPrint.CN_S_LOCATION_CODE, orderPrint.CN_S_LOT_NO, orderPrint.CN_S_FIGURE_NO);
                }
                else if (orderType == "其他出库单")
                {
                    sql += string.Format(@" UPDATE TN_WM_DOWN_HISTORY SET CN_S_FROM_NO='{0}',
                                            CN_T_MODIFY='{1}',
                                            CN_S_MODIFY='{2}', 
                                            CN_S_MODIFY_BY='{2}',
                                            CN_S_PRODUCTION_BATCH='{3}' 
                                            WHERE CN_S_ITEM_CODE='{4}' AND CN_S_LOCATION_CODE='{5}' AND CN_S_LOT_NO='{6}' AND CN_S_FIGURE_NO='{7}' "
                        , orderNo, DateTime.Now, userName, workCenter, orderPrint.CN_S_ITEM_CODE
                        , orderPrint.CN_S_LOCATION_CODE, orderPrint.CN_S_LOT_NO, orderPrint.CN_S_FIGURE_NO);
                }
                else if (orderType == "其他入库单")
                {
                    sql += string.Format(@" UPDATE TN_WM_UP_HISTORY SET CN_S_FROM_NO='{0}',
                                            CN_T_MODIFY='{1}',
                                            CN_S_MODIFY='{2}', 
                                            CN_S_MODIFY_BY='{2}',
                                            CN_S_PRODUCTION_BATCH='{3}' 
                                            WHERE CN_S_ITEM_CODE='{4}' AND CN_S_LOCATION_CODE='{5}' AND CN_S_LOT_NO='{6}' AND CN_S_FIGURE_NO='{7}' "
                        , orderNo, DateTime.Now, userName, workCenter, orderPrint.CN_S_ITEM_CODE
                        , orderPrint.CN_S_LOCATION_CODE, orderPrint.CN_S_LOT_NO, orderPrint.CN_S_FIGURE_NO);
                }
                else
                {

                }
            }
            

            
            return ExecuteTranSql(sql, null, null);

        }

        public OperateResult CancelPrintNo(string orderNo, string orderType)
        {
            var sql = "";

            if (orderType == "产品入库单")
            {
                sql = string.Format(@" UPDATE TN_WM_UP_HISTORY SET CN_S_FROM_NO='',CN_S_PRODUCTION_BATCH='' WHERE CN_S_FROM_NO = '{0}' ", orderNo);
            }
            else if (orderType == "生产领料单")
            {
                sql = string.Format(@" UPDATE TN_WM_DOWN_HISTORY SET CN_S_FROM_NO='',CN_S_PRODUCTION_BATCH='' WHERE CN_S_FROM_NO = '{0}' ", orderNo);
            }
            else if (orderType == "退库单")
            {
                sql = string.Format(@" UPDATE TN_WM_UP_HISTORY SET CN_S_FROM_NO='',CN_S_PRODUCTION_BATCH='' WHERE CN_S_FROM_NO = '{0}' ", orderNo);
            }
            else if (orderType == "其他出库单")
            {
                sql = string.Format(@" UPDATE TN_WM_DOWN_HISTORY SET CN_S_FROM_NO='',CN_S_PRODUCTION_BATCH='' WHERE CN_S_FROM_NO = '{0}' ", orderNo);
            }
            else if (orderType == "其他入库单")
            {
                sql = string.Format(@" UPDATE TN_WM_UP_HISTORY SET CN_S_FROM_NO='',CN_S_PRODUCTION_BATCH='' WHERE CN_S_FROM_NO = '{0}' ", orderNo);
            }
            else
            {

            }

            return ExecuteTranSql(sql, null, null);
        }

        //        public DataTable HistoryLine(Entitys.Common.SearchModel searchModel)
        //        {
        //            string sql = @"select ISNULL(a.CN_T_CREATE,b.CN_T_CREATE) INTIME,ISNULL(b.CN_T_CREATE,b.CN_T_CREATE) OUTTIME,ISNULL(a.INQTY,0) INQTY,ISNULL(b.OUTQTY,0) OUTQTY from (SELECT  CONVERT(date,CN_T_CREATE) CN_T_CREATE,SUM(CN_F_QUANTITY)INQTY
        //                                FROM TN_WM_UP_HISTORY group by CONVERT(date,CN_T_CREATE)) a
        //                                full join (
        //                                SELECT 
        //                                CONVERT(date,b.CN_T_CREATE) CN_T_CREATE,SUM(CN_F_QUANTITY) OUTQTY
        //                                FROM tn_wm_sorting_result b join tn_wm_sorting_list c on b.CN_S_SORTING_NO=c.CN_S_SORTING_NO
        //                                group by CONVERT(date,b.CN_T_CREATE)) b 
        //                                on (a.CN_T_CREATE=b.CN_T_CREATE )";
        //            return ExecuteDataTable(sql);
        //        }


        #region 查询盘点报表
        /// <summary>
        /// 查询盘点报表
        /// </summary>
        /// <param name="pageIndex">分页索引</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="where">条件</param>
        /// <param name="total">out 总行数</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
        public DataTable GetCheckResultDtl(int pageIndex, int pageSize, string where, out long total)
        {

            string sql = @"(SELECT a.CN_S_OP_NO
                              ,a.CN_S_STOCK_CODE
                              ,CN_S_LOCATION_CODE
                              ,CN_S_TRAY_CODE
                              ,CN_S_ITEM_CODE
                              ,CN_S_ITEM_NAME
                              ,CN_S_ITEM_STATE
                              ,CN_S_OWNER
                              ,CN_S_LOT_NO
                              ,CN_S_MODEL
                              ,CN_S_FIGURE_NO
                              ,CN_F_QUANTITY
                              ,CN_F_CHECK_QTY
                              ,CN_S_MEASURE_UNIT
                              ,CN_S_ISWIN
                              ,CN_S_OPERATOR
                              ,CN_T_OPERATE
                              ,CN_S_PRODUCTION_BATCH
                              ,a.CN_S_CREATOR_BY
                              ,a.CN_T_CREATE
                          FROM tn_wm_check_list a join tn_wm_check_mst b on a.CN_S_OP_NO= b.CN_S_OP_NO
                          where b.CN_S_STATE='已完成' " + where + ") a";
            string order = " order by CN_S_OP_NO desc ";
            return ExecutePagingData(sql, pageIndex, pageSize, out total, order, "");
        }
        #endregion

        #region 物料盘点汇总
        /// <summary>
        /// 物料盘点汇总
        /// </summary>
        /// <param name="pageIndex">分页索引</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="where">条件</param>
        /// <param name="total">out 总行数</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
        public DataTable GetItemCheckResultDtl(int pageIndex, int pageSize, string where, out long total)
        {

            string sql = @"(SELECT 
                          b.CN_S_STOCK_CODE
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_ITEM_STATE
                          ,CN_S_OWNER
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH
                          ,SUM(CN_F_QUANTITY) CN_F_QUANTITY
                          ,SUM(CN_F_CHECK_QTY) CN_F_CHECK_QTY
                      FROM tn_wm_check_list a join tn_wm_check_mst b on a.CN_S_OP_NO= b.CN_S_OP_NO
                      where b.CN_S_STATE='已完成' " + where + @"
                      group by b.CN_S_STOCK_CODE
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_ITEM_STATE
                          ,CN_S_OWNER
                          ,CN_S_MODEL
                          ,CN_S_FIGURE_NO
                          ,CN_S_MEASURE_UNIT
                          ,CN_S_PRODUCTION_BATCH) a";
            string order = " order by CN_S_ITEM_CODE desc ";
            return ExecutePagingData(sql, pageIndex, pageSize, out total, order, "");
        }
        #endregion

        #region 查询货位库存
        /// <summary>
        /// 查询货位库存
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="total">out 总行数</param>
        /// <param name="export">是否导出</param>
        /// <returns></returns>
        /// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
        public DataTable GetLocationInventoryList(SearchModel searchModel, out long total, bool export)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
                CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
                CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL),
                CN_S_PRODUCTION_BATCH = Util.ToStringInput(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
                CN_S_STATE = Util.ToString(searchModel.SearchCondition.CN_S_STATE),
                CN_S_OWNER = Util.ToStringInput(searchModel.SearchCondition.CN_S_OWNER),
                CN_S_UNIQUE_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_UNIQUE_CODE),
                CN_S_LOT_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOT_NO),
                CN_S_TRAY_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_TRAY_CODE),
            };
            string sql = @" 
                            SELECT 
                            CN_S_STOCK_CODE,CN_S_STOCK_AREA,
                          a.CN_S_TRAY_CODE
                          ,CN_S_OWNER
                          ,CN_S_ITEM_CODE
                          ,CN_S_ITEM_NAME
                          ,CN_S_MODEL
                          ,CN_S_ITEM_STATE
                          ,b.CN_F_QUANTITY
                          ,CN_S_MEASURE_UNIT
                          ,b.CN_S_PRODUCTION_BATCH
                          ,b.CN_S_LOT_NO,
                          c.CN_S_LOCATION_CODE,CN_F_ALLOC_QTY,b.CN_S_UNIQUE_CODE,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,CN_F_PLANNED_QTY='0.0'
                      FROM tn_wm_b_tray_location c left join TN_WM_B_TRAY_ITEM_MST a on c.CN_S_TRAY_CODE=a.CN_S_TRAY_CODE join 
                        TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID ";
            string strWhere = "";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_AREA))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_STOCK_AREA='" + v.CN_S_STOCK_AREA + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_PRODUCTION_BATCH))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_PRODUCTION_BATCH='" + v.CN_S_PRODUCTION_BATCH + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOT_NO))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_LOT_NO like '%" + v.CN_S_LOT_NO + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_MODEL))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_STATE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_STATE='" + v.CN_S_STATE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_OWNER))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_OWNER='" + v.CN_S_OWNER + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_LOCATION_CODE like '%" + v.CN_S_LOCATION_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_UNIQUE_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_UNIQUE_CODE like '%" + v.CN_S_UNIQUE_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_TRAY_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " a.CN_S_TRAY_CODE like '%" + v.CN_S_TRAY_CODE + "%'";
            }
            if (strWhere.Length > 0)
                sql = sql + " where " + strWhere;
            //sql = "(" + sql + ") t";
            string order = " order by CN_S_LOCATION_CODE desc ";
            if (!export)
                return ExecutePagingData("(" + sql + ") t", searchModel.PageIndex, searchModel.PageSize, out total, order, "");
            else
            {
                total = 0;//无效
                return ExecuteDataTable(sql, "");
            }
        }

        public DataTable GetLocationInventoryListJx(SearchModel searchModel, out long total, bool export)
        {
            var v = new
            {
                CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
                CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
                CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
                CN_S_TRAY_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_TRAY_CODE),
            };
            string sql = @" 
                            SELECT 
                            *
                      FROM tn_wm_b_tray_location  ";
            string strWhere = "";
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_STOCK_AREA))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_STOCK_AREA='" + v.CN_S_STOCK_AREA + "'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_LOCATION_CODE like '%" + v.CN_S_LOCATION_CODE + "%'";
            }
            if (!string.IsNullOrEmpty(v.CN_S_TRAY_CODE))
            {
                if (strWhere.Length > 0)
                    strWhere += " and ";
                strWhere += " CN_S_TRAY_CODE like '%" + v.CN_S_TRAY_CODE + "%'";
            }
            if (strWhere.Length > 0)
                sql = sql + " where " + strWhere;
            //sql = "(" + sql + ") t";
            string order = " order by CN_S_LOCATION_CODE desc ";
            if (!export)
                return ExecutePagingData("(" + sql + ") t", searchModel.PageIndex, searchModel.PageSize, out total, order, "");
            else
            {
                total = 0;//无效
                return ExecuteDataTable(sql, "");
            }
        }
        #endregion

        #region 更新记录
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <returns></returns>
        public List<TN_WM_VERSION_LOGEntity> GetVersionLog()
        {
            string sql = @"SELECT * FROM TN_WM_VERSION_LOG ORDER BY CN_S_VERSION DESC, CN_T_UPDATE DESC,CN_N_ORDER";
            return ExecuteQuery<TN_WM_VERSION_LOGEntity>(sql);
        }
        #endregion
    }
}