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
|
|
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_QUANTITY) AS CN_F_QUANTITY,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_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_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_QUANTITY) AS CN_F_QUANTITY,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_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_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
|
,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_QUANTITY) AS CN_F_QUANTITY
|
,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)
|
};
|
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,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 (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
|
}
|
}
|
