using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.ExcelModel;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using Microsoft.Extensions.Primitives;
using System.Collections;
using System.Data;
using System.Text;
/***************************************************************************************************
*
* 更新人：sitong.dong
* 描述：采购单管理
* 修改时间：2022.06.22
* 修改日志：系统迭代升级
*
**************************************************************************************************/
namespace Estsh.Core.Repositories
{
///
/// 数据访问类
///
public class CycleCountManageRepository : BaseRepository, ICycleCountManageRepository
{
public CycleCountManageRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
{
}
#region 成员方法
///
/// 盘点单
///
public Hashtable getCheckStockByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
DynamicParameters Params = new DynamicParameters();
Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@GroupColumn", "");
Params.Add("@Table", " wms_check_stock a (nolock) left join sys_enum b on a.order_status=b.enum_value and b.enum_type='wms_check_stock_order_status' left join sys_enum c on a.order_TYPE=c.enum_value and c.enum_type='wms_check_stock_order_type' ");
Params.Add("@Column", " a.ruid,a.order_no,a.part_no,a.erp_warehouse,a.order_type,a.order_status,c.enum_desc as order_type_desc,b.enum_desc as order_status_desc,a.is_obvious,a.enabled,a.factory_id,a.factory_code,a.create_time ");
Params.Add("@PageSize", PageSize);
Params.Add("@CurrentPage", PageIndex);
Params.Add("@Condition", strWhere);
Params.Add("@OrderColumn", "a.create_time desc");
Params.Add("@Group", 0);
List dataList = dbConn.Query("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
result.Add("dataList", dataList);
result.Add("totalCount", Params.Get("@TotalCount"));
return result;
}
}
///
/// 盘点单明细
///
public List getCheckStockListByPage(int PageSize, int PageIndex, string strWhere)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" select a.*,b.enum_desc as sn_status_desc,c.enum_desc as trans_status_desc,cp.enum_desc as up_trans_status_desc from wms_check_stock_list a (nolock) " +
" LEFT JOIN sys_stock k (nolock) on a.carton_no = k.carton_no LEFT JOIN dbo.sys_enum b ON b.enum_type = 'sys_stock_status' AND k.status = b.enum_value " +
" LEFT JOIN dbo.sys_enum c ON c.enum_type = 'wms_check_stock_list_trans_status' AND a.trans_status = c.enum_value " +
" LEFT JOIN dbo.sys_enum cp ON cp.enum_type = 'wms_check_stock_list_trans_status' AND a.up_trans_status = cp.enum_value ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
public List getSearchCheckStockList(string strWhere)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" select a.*,b.enum_desc as sn_status_desc,c.enum_desc as trans_status_desc,cp.enum_desc as up_trans_status_desc from wms_check_stock_list a (nolock) " +
" LEFT JOIN sys_stock k (nolock) on a.carton_no = k.carton_no LEFT JOIN dbo.sys_enum b ON b.enum_type = 'sys_stock_status' AND k.status = b.enum_value " +
" LEFT JOIN dbo.sys_enum c ON c.enum_type = 'wms_check_stock_list_trans_status' AND a.trans_status = c.enum_value " +
" LEFT JOIN dbo.sys_enum cp ON cp.enum_type = 'wms_check_stock_list_trans_status' AND a.up_trans_status = cp.enum_value ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
//盘点单零件号
public List getCheckStockPartByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select a.ruid,a.order_no,a.part_id,a.part_no,a.part_spec,a.factory_id,a.factory_code,a.enabled from wms_check_stock_part a ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
//盘点单存储区
public List getCheckStockZoneByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select a.ruid,a.order_no,a.zone_id,a.zone_name,a.factory_id,a.factory_code,a.enabled from wms_check_stock_zone a ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
//盘点单库位
public List getCheckStockLocateByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select a.ruid,a.order_no,a.locate_id,a.locate_name,a.factory_id,a.factory_code,a.enabled from wms_check_stock_locate a ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
//盘点单范围
public List getCheckStockRangeByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select a.ruid,a.order_no,a.zone_id,a.zone_name,a.locate_id,a.locate_name,a.part_id,a.part_no,a.part_spec,a.factory_id,a.factory_code,a.enabled from wms_check_stock_range a ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
public List getCheckStockByOrderNo(string orderNo)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select * from wms_check_stock (nolock) where order_no='" + orderNo + "'";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetMultiErpwarehouse(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select distinct erp_warehouse as id,0 as parentId , erp_warehouse as name from sys_zone where enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetMultiPartNo(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select distinct part_no as id,0 as parentId , part_no as name from sys_part (nolock) where part_type=0 and enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetMultiWarehouse(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select distinct warehouse_id as id,0 as parentId , warehouse_name as name from sys_warehouse where enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetMultiZone(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select distinct zone_id as id,0 as parentId , zone_name as name from sys_zone where enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetMultiLocate(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select distinct locate_id as id,0 as parentId , locate_name as name from sys_locate where enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetErpwarehouse(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String strSql = "select distinct erp_warehouse as [key] , erp_warehouse as value from sys_zone where enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(strSql).ToList();
return result;
}
}
public List GetMoveOrderType(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum where enum_type='wms_move_order_type' and enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(sql).ToList();
return result;
}
}
public List GetCheckStockType(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum where enum_type='wms_check_stock_order_type' and enabled='Y' and factory_Id=" + factoryId + " ";
List result = dbConn.Query(sql).ToList();
return result;
}
}
public List GetCheckStockStatus(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum where enum_type='wms_check_stock_order_status' and enabled='Y' and factory_Id=" + factoryId + " order by enum_value";
List result = dbConn.Query(sql).ToList();
return result;
}
}
///
/// 获取下拉框菜单数据 这里显示的是待添加的厂区信息，厂区名称
///
///
public List getSelectFactory()
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select factory_id as [value],factory_name as [key] from sys_factory where Enabled = 'Y'");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectWarehouse(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select warehouse_desc as [value],warehouse_desc as [key] from sys_warehouse where Enabled = 'Y' and factory_Id=" + factoryId + " ");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectWarehouse(string warehouseid)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from sys_warehouse where Enabled = 'Y' and warehouse_id='" + warehouseid + "'");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectZone(string zoneid)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from sys_zone where Enabled = 'Y' and zone_id='" + zoneid + "'");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectLocate(string locateid)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from sys_locate where Enabled = 'Y' and locate_id='" + locateid + "'");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectZone(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select zone_id as [value],zone_name as [key] from sys_zone where Enabled = 'Y' and factory_Id=" + factoryId + " ");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectVendor(int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select vendor_id as [value],vendor_name as [key] from sys_Vendor where Enabled = 'Y'and factory_Id=" + factoryId + " ");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List getSelectVendor(string vendorId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from sys_Vendor where Enabled = 'Y' and vendor_id='" + vendorId + "'");
List result = dbConn.Query(strSql.ToString()).ToList();
return result;
}
}
public List GetPart(int type, int factoryId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
string sql = "SELECT part_no as [value],part_no as [key] FROM sys_part (NOLOCK) WHERE enabled='Y' and factory_Id=" + factoryId + " ORDER BY part_no";
DynamicParameters param = new DynamicParameters();
param.Add("@part_type", type);
List result = dbConn.Query(sql, param).ToList();
return result;
}
}
public List GetPart(int type, string partId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
string sql = "SELECT * FROM sys_part (NOLOCK) WHERE enabled='Y' and part_id=" + partId + " ORDER BY part_no";
DynamicParameters param = new DynamicParameters();
param.Add("@part_type", type);
List result = dbConn.Query(sql, param).ToList();
return result;
}
}
//根据库区查询零件号
public List getPartNoByZoneIds(string zoneIds)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
List result = new List();
if (!string.IsNullOrEmpty(zoneIds))
{
string sql = "select distinct part_id,part_no,part_spec from sys_stock (nolock) where enabled='Y' and zone_id in (" + zoneIds + ") and part_id <>0 ORDER BY part_no";
result = dbConn.Query(sql).ToList();
}
return result;
}
}
//根据库区查询零件号
public List getPartNoByLocateIds(string locateIds)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
List result = new List();
if (!string.IsNullOrEmpty(locateIds))
{
string sql = "select distinct part_id,part_no,part_spec from sys_stock (nolock) where enabled='Y' and locate_id in (" + locateIds + ") and part_id <>0 ORDER BY part_no";
result = dbConn.Query(sql).ToList();
}
return result;
}
}
public List getPartNoByZoneIdsOrLocateIds(string zoneIds, string locateIds)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
List result = new List();
if (!string.IsNullOrEmpty(zoneIds))
{
string sql = "select distinct part_id,part_no,part_spec from sys_stock (nolock) where enabled='Y' and zone_id in (" + zoneIds + ") and part_id <>0 ORDER BY part_no";
result = dbConn.Query(sql).ToList();
}
else if (!string.IsNullOrEmpty(locateIds))
{
string sql = "select distinct part_id,part_no,part_spec from sys_stock (nolock) where enabled='Y' and locate_id in (" + locateIds + ") and part_id <>0 ORDER BY part_no";
result = dbConn.Query(sql).ToList();
}
return result;
}
}
//根据库区查询零件号
public List getPartNoByZoneId(string zoneId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
List result = new List();
if (!string.IsNullOrEmpty(zoneId))
{
string sql = "select distinct part_id,part_no,part_spec from sys_stock (nolock) where zone_id in (" + zoneId + ") and part_id <>0 ORDER BY part_no";
result = dbConn.Query(sql).ToList();
}
return result;
}
}
//根据库区查询零件号
public List getPartNoByLocateId(string locateId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
List result = new List();
if (!string.IsNullOrEmpty(locateId))
{
string sql = "select distinct part_id,part_no,part_spec from sys_stock (nolock) where locate_id in (" + locateId + ") and part_id <>0 ORDER BY part_no";
result = dbConn.Query(sql).ToList();
}
return result;
}
}
public Hashtable GetPartByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
DynamicParameters Params = new DynamicParameters();
Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@GroupColumn", "");
Params.Add("@Table", " sys_part a ");
Params.Add("@Column", " a.part_id,a.part_no,a.part_spec");
Params.Add("@PageSize", PageSize);
Params.Add("@CurrentPage", PageIndex);
Params.Add("@Condition", strWhere);
Params.Add("@OrderColumn", OrderBy);
Params.Add("@Group", 0);
List dataList = dbConn.Query("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
string sql = "SELECT * FROM sys_part a (NOLOCK) WHERE " + strWhere + " and enabled='Y' ORDER BY part_no";
List partlists = dbConn.Query(sql).ToList();
result.Add("dataList", partlists);
result.Add("totalCount", Params.Get("@TotalCount"));
return result;
}
}
public Hashtable GetZoneByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
DynamicParameters Params = new DynamicParameters();
Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@GroupColumn", "");
Params.Add("@Table", " sys_zone a ");
Params.Add("@Column", " a.zone_id,zone_name,zone_desc");
Params.Add("@PageSize", PageSize);
Params.Add("@CurrentPage", PageIndex);
Params.Add("@Condition", strWhere);
Params.Add("@OrderColumn", OrderBy);
Params.Add("@Group", 0);
List dataList = dbConn.Query("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
string sql = "SELECT * FROM sys_zone (NOLOCK) a WHERE " + strWhere + " and enabled='Y' ORDER BY zone_name";
List zonelists = dbConn.Query(sql).ToList();
result.Add("dataList", zonelists);
result.Add("totalCount", Params.Get("@TotalCount"));
return result;
}
}
public Hashtable GetLocateByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
DynamicParameters Params = new DynamicParameters();
Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
Params.Add("@GroupColumn", "");
Params.Add("@Table", " sys_locate a ");
Params.Add("@Column", " a.locate_id,locate_name,locate_desc");
Params.Add("@PageSize", PageSize);
Params.Add("@CurrentPage", PageIndex);
Params.Add("@Condition", strWhere);
Params.Add("@OrderColumn", OrderBy);
Params.Add("@Group", 0);
List dataList = dbConn.Query("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
string sql = "SELECT * FROM sys_locate (NOLOCK) a WHERE " + strWhere + " and enabled='Y' ORDER BY locate_name";
List locatelists = dbConn.Query(sql).ToList();
result.Add("dataList", locatelists);
result.Add("totalCount", Params.Get("@TotalCount"));
return result;
}
}
public List GetPartList(int type, string partNo)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
string sql = "SELECT * FROM sys_part (NOLOCK) WHERE enabled='Y' and part_no='" + partNo + "' ORDER BY part_no";
List result = dbConn.Query(sql).ToList();
return result;
}
}
///
/// 获取订单编号
///
///
public string GetOrderNo()
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
DynamicParameters parameter = new DynamicParameters();
parameter.Add("@orderNo", null, DbType.String, ParameterDirection.Output, 255);
List depts = dbConn.Query("sys_create_sapo_no", parameter, commandType: CommandType.StoredProcedure).ToList();
return parameter.Get("@orderNo");
}
}
///
/// 插入菜单数据
///
///
///
public bool saveCycleCountManage(WmsCheckStock checkStockParams, List checkStockPartParams, List checkStockZoneParams, List checkStockLocateParams, List checkStockRangeParams)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
string orderNo = GetOrderNo();
checkStockParams.OrderNo = orderNo;
List sqlLists = new List();
List parameters = new List();
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock(order_no,part_no,erp_warehouse,order_type,order_status,is_obvious,enabled,factory_id,factory_code,create_userid,create_time,guid) ");
SqlStringBuilder.Append(" VALUES(@orderNo,@partNo,@erpWarehouse,@orderType,@orderStatus,@isObvious,@enabled,@factoryId,@factoryCode,@createUserid ");
SqlStringBuilder.Append(" ,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
sqlLists.Add(SqlStringBuilder.ToString());
DynamicParameters dynamic = new DynamicParameters();
dynamic.Add("@orderNo", checkStockParams.OrderNo);
dynamic.Add("@partNo", checkStockParams.PartNo);
dynamic.Add("@erpWarehouse", checkStockParams.ErpWarehouse);
dynamic.Add("@orderType", checkStockParams.OrderType);
dynamic.Add("@orderStatus", 10);
dynamic.Add("@isObvious", checkStockParams.IsObvious);
dynamic.Add("@enabled", "Y");
dynamic.Add("@factoryId", checkStockParams.FactoryId);
dynamic.Add("@factoryCode", checkStockParams.FactoryCode);
dynamic.Add("@createUserid", checkStockParams.CreateUserId);
parameters.Add(dynamic);
//int recCheckStock = dbConn.Execute(SqlStringBuilder.ToString(), checkStockParams);
for (int i = 0; i < checkStockPartParams.Count; i++)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_part(order_no,part_id,part_no,part_spec,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
SqlStringBuilder.Append(" VALUES(@orderNo,@partId,@partNo,@partSpec,@factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderNo", orderNo);
dynamic.Add("@partId", checkStockPartParams[i].PartId);
dynamic.Add("@partNo", checkStockPartParams[i].PartNo);
dynamic.Add("@partSpec", checkStockPartParams[i].PartSpec);
dynamic.Add("@factoryId", checkStockPartParams[i].FactoryId);
dynamic.Add("@factoryCode", checkStockPartParams[i].FactoryCode);
dynamic.Add("@enabled", "Y");
dynamic.Add("@createUserid", checkStockPartParams[i].CreateUserId);
parameters.Add(dynamic);
}
//int recCheckStockPart = dbConn.Execute(SqlStringBuilder.ToString(), checkStockPartParams);
for (int i = 0; i < checkStockZoneParams.Count; i++)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_zone(order_no,zone_id,zone_name,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
SqlStringBuilder.Append(" VALUES(@orderNo,@zoneId,@zoneName,@factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderNo", orderNo);
dynamic.Add("@zoneId", checkStockZoneParams[i].ZoneId);
dynamic.Add("@zoneName", checkStockZoneParams[i].ZoneName);
dynamic.Add("@factoryId", checkStockZoneParams[i].FactoryId);
dynamic.Add("@factoryCode", checkStockZoneParams[i].FactoryCode);
dynamic.Add("@enabled", "Y");
dynamic.Add("@createUserid", checkStockZoneParams[i].CreateUserId);
parameters.Add(dynamic);
}
//int recCheckStockZone = dbConn.Execute(SqlStringBuilder.ToString(), checkStockZoneParams);
for (int i = 0; i < checkStockLocateParams.Count; i++)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_locate(order_no,locate_id,locate_name,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
SqlStringBuilder.Append(" VALUES(@orderNo,@locateId,@locateName,@factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderNo", orderNo);
dynamic.Add("@locateId", checkStockLocateParams[i].LocateId);
dynamic.Add("@locateName", checkStockLocateParams[i].LocateName);
dynamic.Add("@factoryId", checkStockLocateParams[i].FactoryId);
dynamic.Add("@factoryCode", checkStockLocateParams[i].FactoryCode);
dynamic.Add("@enabled", "Y");
dynamic.Add("@createUserid", checkStockLocateParams[i].CreateUserId);
parameters.Add(dynamic);
}
//int recCheckStockLocate = dbConn.Execute(SqlStringBuilder.ToString(), checkStockLocateParams);
for (int i = 0; i < checkStockRangeParams.Count; i++)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_range(order_no,zone_id,zone_name,locate_id,locate_name,part_id,part_no,part_spec,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
SqlStringBuilder.Append(" VALUES(@orderNo,@zoneId,@zoneName,@locateId,@locateName,@partId,@partNo,@partSpec,@factoryId,@factoryCode,'Y',@createUserid ");
SqlStringBuilder.Append(" ,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderNo", orderNo);
dynamic.Add("@zoneId", checkStockRangeParams[i].ZoneId);
dynamic.Add("@zoneName", checkStockRangeParams[i].ZoneName);
dynamic.Add("@locateId", checkStockRangeParams[i].LocateId);
dynamic.Add("@locateName", checkStockRangeParams[i].LocateName);
dynamic.Add("@partId", checkStockRangeParams[i].PartId);
dynamic.Add("@partNo", checkStockRangeParams[i].PartNo);
dynamic.Add("@partSpec", checkStockRangeParams[i].PartSpec);
dynamic.Add("@factoryId", checkStockRangeParams[i].FactoryId);
dynamic.Add("@factoryCode", checkStockRangeParams[i].FactoryCode);
dynamic.Add("@enabled", "Y");
dynamic.Add("@createUserid", checkStockRangeParams[i].CreateUserId);
parameters.Add(dynamic);
}
//int recCheckStockRange = dbConn.Execute(SqlStringBuilder.ToString(), checkStockRangeParams);
bool flag = ExecuteTransaction(sqlLists, parameters);
return flag;
}
}
public bool ExecuteTransaction(List sqlStrings, List parameterList, int? commandTimeout = null)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
bool isClosed = dbConn.State == ConnectionState.Closed;
if (isClosed)
{
dbConn.Open();
}
//开启事务
using (var transaction = dbConn.BeginTransaction())
{
int tempnum = 0;
string strsql = "";
try
{
bool successCount = true;
if (parameterList == null || parameterList.Count == 0)
{
for (int i = 0; i < sqlStrings.Count; i++)
{
tempnum = i;
strsql = sqlStrings[i];
dbConn.Execute(sqlStrings[i], null, transaction, commandTimeout);
}
}
else
{
for (int i = 0; i < sqlStrings.Count; i++)
{
dbConn.Execute(sqlStrings[i], parameterList[i], transaction, commandTimeout);
tempnum = i;
}
}
//提交事务
transaction.Commit();
return true;
}
catch (Exception ex)
{
//回滚事务
transaction.Rollback();
dbConn.Close();
dbConn.Dispose();
throw ex;
}
finally
{
dbConn.Close();
dbConn.Dispose();
}
}
}
}
//整单解冻调差
public Hashtable onAdjustment(String orderNo, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
List sqlLists = new List();
List orderNoList = orderNo.Split(",").ToList();
for (int i = 0; i < orderNoList.Count; i++)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" UPDATE dbo.wms_check_stock SET order_status = 50,update_userid=" + userId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) ");
SqlStringBuilder.Append(" WHERE order_no = '" + orderNoList[i] + "'; ");
sqlLists.Add(SqlStringBuilder.ToString());
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select a.*,b.erp_warehouse as erp_warehouse,c.erp_warehouse as dest_erp_warehouse,l.locate_type from [dbo].[wms_check_stock_list] a left join sys_zone b on a.zone_id=b.zone_id left join sys_zone c on a.dest_zone_id=c.zone_id " +
" LEFT JOIN dbo.sys_locate l ON a.dest_locate_id=l.locate_id" +
" where order_no ='" + orderNoList[i] + "' and a.fact_qty!=0 ");
List sysStocks = dbConn.Query(SqlStringBuilder.ToString()).ToList();
//解冻调差，只解冻调差已盘点的条码
for (int j = 0; j < sysStocks.Count; j++)
{
int statusNew = 50;//已入库
if (sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.BASICS_LOCATE ||
sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.MANEUVER_LOCATE ||
sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.COMBINATION_LOCATE)
{
statusNew = (int)WmsEnumUtil.StockStatus.INSTOCKED;//已入库
}
else if (sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.LINE_LOCATE)//线边库位
{
statusNew = (int)WmsEnumUtil.StockStatus.ONLINED;
}
else if (sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.NC_LOCATE)//NC库位
{
statusNew = (int)WmsEnumUtil.StockStatus.NC_INSTOCK;
}
decimal? factQty = sysStocks[j].FactQty == null ? 0 : sysStocks[j].FactQty;
int? destWarehouseId = sysStocks[j].DestWarehouseId == null ? 0 : sysStocks[j].DestWarehouseId;
int? destZoneId = sysStocks[j].DestZoneId == null ? 0 : sysStocks[j].DestZoneId;
int? destLocateId = sysStocks[j].DestLocateId == null ? 0 : sysStocks[j].DestLocateId;
//零件号为空，是台车，查询最后一个台车解冻
if (string.IsNullOrEmpty(sysStocks[j].PartNo))
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select top 1 * from sys_stock (nolock) where carton_no ='" + sysStocks[j].CartonNo + "' order by create_time desc ");
List stockLists = dbConn.Query(SqlStringBuilder.ToString()).ToList();
if (stockLists.Count > 0)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" UPDATE sys_stock SET enabled='Y' ,update_userid=" + userId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) ");
SqlStringBuilder.Append(" WHERE ruid = " + stockLists[0].Ruid + "; ");
sqlLists.Add(SqlStringBuilder.ToString());
}
}
else
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("select * from dbo.sys_stock (nolock) where carton_no='" + sysStocks[j].CartonNo + "' ");
SysStock stocks = dbConn.Query(SqlStringBuilder.ToString()).FirstOrDefault();
if (stocks != null)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" UPDATE sys_stock SET qty=" + factQty + ",status=" + statusNew + "" +
",warehouse_id=" + destWarehouseId + ",warehouse_name='" + sysStocks[j].DestWarehouseName + "'" +
",zone_id=" + destZoneId + ",zone_name='" + sysStocks[j].DestZoneName + "'" +
",locate_id=" + destLocateId + ",locate_name='" + sysStocks[j].DestLocateName + "'" +
",erp_warehouse='" + sysStocks[j].DestErpWarehouse + "'," +
"enabled='Y',update_userid=" + userId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) ");
SqlStringBuilder.Append(" WHERE carton_no = '" + sysStocks[j].CartonNo + "'; ");
sqlLists.Add(SqlStringBuilder.ToString());
}
else
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" INSERT INTO dbo.sys_stock(vendor_id,vendor_code,carton_no,carton_type,part_id,part_no,part_spec,lot_no,status,qty,snp_qty,locate_id,locate_name,ref_order_no ");
SqlStringBuilder.Append(" ,warehouse_id,warehouse_name,zone_id,zone_name,erp_warehouse,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
SqlStringBuilder.Append(" VALUES('0','','" + sysStocks[j].CartonNo + "',0,'" + sysStocks[j].PartId + "','" + sysStocks[j].PartNo + "','" + sysStocks[j].PartSpec + "',''," + statusNew + "," + factQty + "," + factQty + " ");
SqlStringBuilder.Append(" , '" + sysStocks[j].DestLocateId + "','" + sysStocks[j].DestLocateName + "','','" + sysStocks[j].DestWarehouseId + "','" + sysStocks[j].DestWarehouseName + "','" + sysStocks[j].DestZoneId + "','" + sysStocks[j].DestZoneName + "','" + sysStocks[j].DestErpWarehouse + "' ");
SqlStringBuilder.Append(" ,'" + sysStocks[j].FactoryId + "','" + sysStocks[j].FactoryCode + "','Y','" + userId + "',CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
sqlLists.Add(SqlStringBuilder.ToString());
}
}
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.sys_stock_trans ( ");
SqlStringBuilder.Append(" trans_code, carton_no, part_id, ");
SqlStringBuilder.Append(" part_no, part_spec, src_locate_id, ");
SqlStringBuilder.Append(" src_locate_name, dest_locate_id, dest_locate_name, ");
SqlStringBuilder.Append(" old_qty, new_qty, trans_qty, ");
SqlStringBuilder.Append(" old_status, new_status, unit, ");
SqlStringBuilder.Append(" factory_id, factory_code, ");
SqlStringBuilder.Append(" src_erp_warehouse, dest_erp_warehouse, ");
SqlStringBuilder.Append(" src_warehouse_id, src_warehouse_name, ");
SqlStringBuilder.Append(" dest_warehouse_id, dest_warehouse_name, ");
SqlStringBuilder.Append(" src_zone_id, src_zone_name, ");
SqlStringBuilder.Append(" dest_zone_id, dest_zone_name, enabled, create_userid, ");
SqlStringBuilder.Append(" create_time, update_userid, update_time, guid,ref_order_no) ");
SqlStringBuilder.Append("VALUES ");
SqlStringBuilder.Append("( " + (int)WmsEnumUtil.TransType.CS_UPDATE + ", ");//盘点更新
SqlStringBuilder.Append(" '" + sysStocks[j].CartonNo + "', ");
SqlStringBuilder.Append(" " + sysStocks[j].PartId + ", ");
SqlStringBuilder.Append(" '" + sysStocks[j].PartNo + "', ");
SqlStringBuilder.Append(" N'" + sysStocks[j].PartSpec + "', ");
SqlStringBuilder.Append(" " + sysStocks[j].LocateId == null ? 0 : sysStocks[j].LocateId + ", ");//源库位
SqlStringBuilder.Append(" '" + sysStocks[j].LocateName + "', ");
SqlStringBuilder.Append(" " + sysStocks[j].DestLocateId == null ? 0 : sysStocks[j].DestLocateId + ", ");//目标库位
SqlStringBuilder.Append(" '" + sysStocks[j].DestLocateName + "', ");
SqlStringBuilder.Append(" " + sysStocks[j].Qty + ",");
SqlStringBuilder.Append(" " + sysStocks[j].FactQty + ",");
SqlStringBuilder.Append(" " + sysStocks[j].FactQty + ",");
SqlStringBuilder.Append(" '" + sysStocks[j].SnStatus + "', ");
if (sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.BASICS_LOCATE ||
sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.MANEUVER_LOCATE ||
sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.COMBINATION_LOCATE)
{
SqlStringBuilder.Append(" '" + (int)WmsEnumUtil.StockStatus.INSTOCKED + "', "); //已入库
}
else if (sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.LINE_LOCATE)//线边库位
{
SqlStringBuilder.Append(" '" + (int)WmsEnumUtil.StockStatus.ONLINED + "', "); //已上线
}
else if (sysStocks[j].LocateType == (int)WmsEnumUtil.LocateType.NC_LOCATE)//NC库位
{
SqlStringBuilder.Append(" '" + (int)WmsEnumUtil.StockStatus.NC_INSTOCK + "', "); //NC入库
}
else
{
SqlStringBuilder.Append(" '" + (int)WmsEnumUtil.StockStatus.INSTOCKED + "', "); //已入库
}
SqlStringBuilder.Append(" '" + sysStocks[j].Unit + "', ");
SqlStringBuilder.Append(" " + sysStocks[j].FactoryId + ", ");
SqlStringBuilder.Append(" '" + sysStocks[j].FactoryCode + "',");
SqlStringBuilder.Append(" '" + sysStocks[j].ErpWarehouse + "',");
SqlStringBuilder.Append(" '" + sysStocks[j].DestErpWarehouse + "', ");
int? warehouseIdTran = sysStocks[j].WarehouseId == null ? 0 : sysStocks[j].WarehouseId;
SqlStringBuilder.Append(" " + warehouseIdTran + ", ");
SqlStringBuilder.Append(" '" + sysStocks[j].WarehouseName + "', ");
int? destWarehouseIdTran = sysStocks[j].DestWarehouseId == null ? 0 : sysStocks[j].DestWarehouseId;
SqlStringBuilder.Append(" " + destWarehouseIdTran + ", ");
SqlStringBuilder.Append(" '" + sysStocks[j].DestWarehouseName + "', ");
int? zoneIdTran = sysStocks[j].ZoneId == null ? 0 : sysStocks[j].ZoneId;
SqlStringBuilder.Append(" " + zoneIdTran + ", ");
SqlStringBuilder.Append(" '" + sysStocks[j].ZoneName + "', ");
int? destZoneIdTran = sysStocks[j].DestZoneId == null ? 0 : sysStocks[j].DestZoneId;
SqlStringBuilder.Append(" " + destZoneIdTran + ", ");
SqlStringBuilder.Append(" '" + sysStocks[j].DestZoneName + "', ");
SqlStringBuilder.Append(" 'Y', ");
SqlStringBuilder.Append(" " + userId + ", ");
SqlStringBuilder.Append(" CONVERT(VARCHAR(20),GETDATE(),21), ");
SqlStringBuilder.Append(" " + userId + ", ");
SqlStringBuilder.Append(" CONVERT(VARCHAR(20),GETDATE(),21), ");
SqlStringBuilder.Append(" NEWID() , ");
SqlStringBuilder.Append(" '" + sysStocks[j].OrderNo + "' ");
SqlStringBuilder.Append(" ) ; ");
sqlLists.Add(SqlStringBuilder.ToString());
}
}
bool flag = ExecuteTransaction(sqlLists, null);
if (flag == true)
{
result.Add("status", 1);
result.Add("message", "整单解冻调差成功");
}
else
{
result.Add("status", 0);
result.Add("message", "整单解冻调差失败");
}
return result;
}
}
///
/// 获得调差指令
///
///
///
///
public List GetSAPDIFO(String orderNo, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" select * from SAP_DIFO where IBLNR='" + orderNo + "' and STAFLG='N' ");
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
public SysBase GetSAPDIFOByBase()
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" select * from sys_base where param_name='盘点调差指令' and enabled='Y' ");
SysBase result = dbConn.QueryFirstOrDefault(SqlStringBuilder.ToString());
return result;
}
}
//整单解冻不调差
public Hashtable onNoAdjustment(String orderNos, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
List sqlLists = new List();
List parametersList = new List();
StringBuilder query = new StringBuilder(1024);
DynamicParameters parameters = new DynamicParameters();
List orderNoList = orderNos.Split(",").ToList();
foreach (var orderNo in orderNoList)
{
query.Clear();
query.Append(" UPDATE dbo.wms_check_stock SET order_status = @orderStatus,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
query.Append(" WHERE order_no = @orderNo ");
parameters = new DynamicParameters();
parameters.Add("@orderNo", orderNo);
parameters.Add("@orderStatus", 55);
parameters.Add("@updateUserid", userId);
sqlLists.Add(query.ToString());
parametersList.Add(parameters);
query.Clear();
query.Append("UPDATE ss2 SET ss2.enabled = 'Y',ss2.update_userid = @updateUserid,ss2.update_time = CONVERT(varchar(50), GETDATE(), 21) ");
query.Append("FROM sys_stock ss2 ");
query.Append("JOIN ( SELECT max(ss.ruid) AS ruid FROM sys_stock ss ");
query.Append(" JOIN wms_check_stock_list wcsl ON ss.carton_no = wcsl.carton_no ");
query.Append(" WHERE wcsl.order_no = @orderNo ");
query.Append(" AND ss.enabled = 'N' and wcsl.trans_status !=50 ");
query.Append(" GROUP BY ss.carton_no ) b ON ss2.ruid = b.ruid ");
query.Append("WHERE ss2.enabled = 'N' ");
parameters = new DynamicParameters();
parameters.Add("@orderNo", orderNo);
parameters.Add("@updateUserid", userId);
sqlLists.Add(query.ToString());
parametersList.Add(parameters);
query.Clear();
query.Append("INSERT INTO sys_stock_trans ");
query.Append(" (trans_code, carton_no, part_id, part_no, part_spec, src_locate_id, src_locate_name, dest_locate_id, dest_locate_name, ");
query.Append(" old_qty, new_qty, trans_qty, old_status, new_status, unit, factory_id, factory_code, ");
query.Append(" src_erp_warehouse, dest_erp_warehouse, src_warehouse_id, src_warehouse_name, dest_warehouse_id, dest_warehouse_name, ");
query.Append(" src_zone_id, src_zone_name, dest_zone_id, dest_zone_name, create_userid, ref_order_no) ");
query.Append("select @transCode,a.carton_no, a.part_id ,a.part_no ,a.part_spec ,a.locate_id ,a.locate_name ,a.locate_id, a.locate_name, ");
query.Append(" a.qty ,a.qty ,a.qty ,ss.status ,ss.status ,ss.unit ,a.factory_id ,a.factory_code , ");
query.Append(" ss.erp_warehouse ,ss.erp_warehouse ,a.warehouse_id ,a.warehouse_name ,a.warehouse_id ,a.warehouse_name , ");
query.Append(" a.zone_id ,a.zone_name ,a.zone_id ,a.zone_name, @updateUserid, a.order_no ");
query.Append("from wms_check_stock_list a ");
query.Append("join sys_stock ss on a.carton_no = ss.carton_no ");
query.Append("where order_no =@orderNo ");
parameters = new DynamicParameters();
parameters.Add("@orderNo", orderNo);
parameters.Add("@updateUserid", userId);
parameters.Add("@transCode", (int)WmsEnumUtil.TransType.CS_UPDATE);
sqlLists.Add(query.ToString());
parametersList.Add(parameters);
//StringBuilder SqlStringBuilder = new StringBuilder(1024);
//SqlStringBuilder.Append(" UPDATE dbo.wms_check_stock SET order_status = @orderStatus,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
//SqlStringBuilder.Append(" WHERE order_no = @orderNo; ");
//sqlLists.Add(SqlStringBuilder.ToString());
//dynamic = new DynamicParameters();
//dynamic.Add("@orderNo", orderNoList[i]);
//dynamic.Add("@orderStatus", 55);
//dynamic.Add("@updateUserid", userId);
//parameters.Add(dynamic);
//SqlStringBuilder = new StringBuilder(1024);
//SqlStringBuilder.Append("select a.*,b.erp_warehouse as erp_warehouse,c.erp_warehouse as dest_erp_warehouse from [dbo].[wms_check_stock_list] a left join sys_zone b on a.zone_id=b.zone_id left join sys_zone c on a.dest_zone_id=c.zone_id " +
// " where order_no ='" + orderNoList[i] + "' ");
//List sysStocks = dbConn.Query(SqlStringBuilder.ToString()).ToList();
////解冻不调差，全部解冻
//for (int j = 0; j < sysStocks.Count; j++)
//{
// //零件号为空，是台车，查询最后一个台车解冻
// if (string.IsNullOrEmpty(sysStocks[j].PartNo))
// {
// SqlStringBuilder = new StringBuilder(1024);
// SqlStringBuilder.Append("select top 1 * from sys_stock (nolock) where carton_no ='" + sysStocks[j].CartonNo + "' order by create_time desc ");
// List stockLists = dbConn.Query(SqlStringBuilder.ToString()).ToList();
// if (stockLists.Count > 0)
// {
// SqlStringBuilder = new StringBuilder(1024);
// SqlStringBuilder.Append(" UPDATE sys_stock SET enabled='Y' ,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
// SqlStringBuilder.Append(" WHERE ruid = @ruid; ");
// sqlLists.Add(SqlStringBuilder.ToString());
// dynamic = new DynamicParameters();
// dynamic.Add("@ruid", stockLists[0].Ruid);
// dynamic.Add("@updateUserid", userId);
// parameters.Add(dynamic);
// }
// }
// else//条码正常解冻
// {
// SqlStringBuilder = new StringBuilder(1024);
// SqlStringBuilder.Append(" UPDATE sys_stock SET enabled='Y' ,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
// SqlStringBuilder.Append(" WHERE carton_no = @cartonNo; ");
// sqlLists.Add(SqlStringBuilder.ToString());
// dynamic = new DynamicParameters();
// dynamic.Add("@cartonNo", sysStocks[j].CartonNo);
// dynamic.Add("@updateUserid", userId);
// parameters.Add(dynamic);
// }
// SqlStringBuilder = new StringBuilder(1024);
// SqlStringBuilder.Append("INSERT INTO dbo.sys_stock_trans ( ");
// SqlStringBuilder.Append(" trans_code, carton_no, part_id, ");
// SqlStringBuilder.Append(" part_no, part_spec, src_locate_id, ");
// SqlStringBuilder.Append(" src_locate_name, dest_locate_id, dest_locate_name, ");
// SqlStringBuilder.Append(" old_qty, new_qty, trans_qty, ");
// SqlStringBuilder.Append(" old_status, new_status, unit, ");
// SqlStringBuilder.Append(" factory_id, factory_code, ");
// SqlStringBuilder.Append(" src_erp_warehouse, dest_erp_warehouse, ");
// SqlStringBuilder.Append(" src_warehouse_id, src_warehouse_name, ");
// SqlStringBuilder.Append(" dest_warehouse_id, dest_warehouse_name, ");
// SqlStringBuilder.Append(" src_zone_id, src_zone_name, ");
// SqlStringBuilder.Append(" dest_zone_id, dest_zone_name, enabled, create_userid, ");
// SqlStringBuilder.Append(" create_time, update_userid, update_time, guid,ref_order_no) ");
// SqlStringBuilder.Append("VALUES ");
// SqlStringBuilder.Append("( '270', ");//盘点更新
// SqlStringBuilder.Append(" '" + sysStocks[j].CartonNo + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].PartId + ", ");
// SqlStringBuilder.Append(" '" + sysStocks[j].PartNo + "', ");
// SqlStringBuilder.Append(" N'" + sysStocks[j].PartSpec + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].LocateId == null ? 0 : sysStocks[j].LocateId + ", ");//源库位
// SqlStringBuilder.Append(" '" + sysStocks[j].LocateName + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].LocateId == null ? 0 : sysStocks[j].LocateId + ", ");//目标库位
// SqlStringBuilder.Append(" '" + sysStocks[j].LocateName + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].Qty + ",");
// SqlStringBuilder.Append(" " + sysStocks[j].Qty + ",");
// SqlStringBuilder.Append(" " + sysStocks[j].Qty + ",");
// SqlStringBuilder.Append(" '" + sysStocks[j].SnStatus + "', ");
// SqlStringBuilder.Append(" '" + sysStocks[j].SnStatus + "', ");
// SqlStringBuilder.Append(" '" + sysStocks[j].Unit + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].FactoryId + ", ");
// SqlStringBuilder.Append(" '" + sysStocks[j].FactoryCode + "',");
// SqlStringBuilder.Append(" '" + sysStocks[j].ErpWarehouse + "',");
// SqlStringBuilder.Append(" '" + sysStocks[j].DestErpWarehouse + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].WarehouseId == null ? 0 : sysStocks[j].WarehouseId + ", ");
// SqlStringBuilder.Append(" '" + sysStocks[j].WarehouseName + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].WarehouseId == null ? 0 : sysStocks[j].WarehouseId + ", ");
// SqlStringBuilder.Append(" '" + sysStocks[j].WarehouseName + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].ZoneId == null ? 0 : sysStocks[j].ZoneId + ", ");
// SqlStringBuilder.Append(" '" + sysStocks[j].ZoneName + "', ");
// SqlStringBuilder.Append(" " + sysStocks[j].ZoneId == null ? 0 : sysStocks[j].ZoneId + ", ");
// SqlStringBuilder.Append(" '" + sysStocks[j].ZoneName + "', ");
// SqlStringBuilder.Append(" 'Y', ");
// SqlStringBuilder.Append(" " + userId + ", ");
// SqlStringBuilder.Append(" CONVERT(VARCHAR(20),GETDATE(),21), ");
// SqlStringBuilder.Append(" " + userId + ", ");
// SqlStringBuilder.Append(" CONVERT(VARCHAR(20),GETDATE(),21), ");
// SqlStringBuilder.Append(" NEWID() ,");
// SqlStringBuilder.Append(" '" + sysStocks[j].OrderNo + "' ");
// SqlStringBuilder.Append(" ) ; ");
// sqlLists.Add(SqlStringBuilder.ToString());
// dynamic = new DynamicParameters();
// dynamic.Add("@cartonNo", sysStocks[j].CartonNo);
// parameters.Add(dynamic);
//}
}
bool flag = ExecuteTransaction(sqlLists, parametersList);
if (flag == true)
{
result.Add("status", 1);
result.Add("message", "整单解冻不调差执行成功");
}
else
{
result.Add("status", 0);
result.Add("message", "整单解冻不调差执行失败");
}
return result;
}
}
//开始盘点
public Hashtable onBeginCycle(String orderNo, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
List sqlLists = new List();
List parameters = new List();
DynamicParameters dynamic = new DynamicParameters();
List orderNoList = orderNo.Split(",").ToList();
for (int m = 0; m < orderNoList.Count; m++)
{
String sqlStringType = " SELECT * FROM dbo.wms_check_stock (nolock) where order_no='" + orderNoList[m] + "'";
List wmsChecks = dbConn.Query(sqlStringType).ToList();
if (wmsChecks.Count > 0)
{
if (wmsChecks[0].OrderType != "10")
{
result.Add("status", 0);
result.Add("message", "开启盘点失败,盘点单" + orderNoList[m] + "不处于创建状态！");
return result;
}
}
String strSql = " SELECT * FROM dbo.wms_check_stock_range (nolock) where order_no='" + orderNoList[m] + "'";
List checkStockRanges = dbConn.Query(strSql).ToList();
List checkStockListParams = new List();
#region 逐条更改
//for (int i = 0; i < checkStockRanges.Count; i++)
//{
// //开始盘点，状态未冻结的条码
// List sysStocksInfo = new List();
// if (string.IsNullOrEmpty(checkStockRanges[i].PartNo))//零件号为空
// {
// if (checkStockRanges[i].ZoneId != 0)
// {
// string sql = "select * from sys_stock (nolock) where zone_id='" + checkStockRanges[i].ZoneId + "' and zone_name='" + checkStockRanges[i].ZoneName + "' and enabled='Y' and factory_code='" + wmsChecks[0].FactoryCode + "' ";
// sysStocksInfo = dbConn.Query(sql).ToList();
// }
// else
// {
// string sql = "select * from sys_stock (nolock) where locate_id='" + checkStockRanges[i].LocateId + "' and locate_name='" + checkStockRanges[i].LocateName + "' and enabled='Y' and factory_code='" + wmsChecks[0].FactoryCode + "' ";
// sysStocksInfo = dbConn.Query(sql).ToList();
// }
// }
// else
// {
// if (checkStockRanges[i].ZoneId != 0)
// {
// string sql = "select * from sys_stock (nolock) where zone_id='" + checkStockRanges[i].ZoneId + "' and zone_name='" + checkStockRanges[i].ZoneName + "' and part_no='" + checkStockRanges[i].PartNo + "' and enabled='Y' and factory_code='" + wmsChecks[0].FactoryCode + "'";
// sysStocksInfo = dbConn.Query(sql).ToList();
// }
// else if (checkStockRanges[i].LocateId != 0)
// {
// string sql = "select * from sys_stock (nolock) where locate_id='" + checkStockRanges[i].LocateId + "' and locate_name='" + checkStockRanges[i].LocateName + "' and part_no='" + checkStockRanges[i].PartNo + "' and enabled='Y' and factory_code='" + wmsChecks[0].FactoryCode + "'";
// sysStocksInfo = dbConn.Query(sql).ToList();
// }
// else
// {
// string sql = "select * from sys_stock (nolock) where part_no='" + checkStockRanges[i].PartNo + "' and enabled='Y' and factory_code='" + wmsChecks[0].FactoryCode + "'";
// sysStocksInfo = dbConn.Query(sql).ToList();
// }
// }
// for (int k = 0; k < sysStocksInfo.Count; k++)
// {
// if (sysStocksInfo[k].Status != (int)WmsEnumUtil.StockStatus.REJECTION)
// {
// WmsCheckStockList checkStockListInfo = new WmsCheckStockList();
// checkStockListInfo.OrderNo = orderNoList[m];
// checkStockListInfo.WarehouseId = sysStocksInfo[k].WarehouseId;
// checkStockListInfo.WarehouseName = sysStocksInfo[k].WarehouseName;
// checkStockListInfo.ZoneId = sysStocksInfo[k].ZoneId;
// checkStockListInfo.ZoneName = sysStocksInfo[k].ZoneName;
// checkStockListInfo.LocateId = sysStocksInfo[k].LocateId;
// checkStockListInfo.LocateName = sysStocksInfo[k].LocateName;
// checkStockListInfo.PartId = sysStocksInfo[k].PartId;
// checkStockListInfo.PartNo = sysStocksInfo[k].PartNo;
// checkStockListInfo.PartSpec = sysStocksInfo[k].PartSpec;
// checkStockListInfo.CartonNo = sysStocksInfo[k].CartonNo;
// checkStockListInfo.Qty = sysStocksInfo[k].Qty;
// checkStockListInfo.FactQty = 0;
// checkStockListInfo.Unit = sysStocksInfo[k].Unit;
// checkStockListInfo.SnStatus = sysStocksInfo[k].Status.ToString();
// checkStockListInfo.TransStatus = 10;
// checkStockListInfo.FactoryId = sysStocksInfo[k].FactoryId;
// checkStockListInfo.FactoryCode = sysStocksInfo[k].FactoryCode;
// checkStockListInfo.CreateUserId = Convert.ToInt32(userId);
// checkStockListParams.Add(checkStockListInfo);
// }
// if (wmsChecks[0].OrderType == "10")
// {
// string updateStatus = "update sys_stock set enabled='N',update_userid=@updateUserid , update_time = CONVERT(varchar(50), GETDATE(), 21) where ruid=@ruid";
// sqlLists.Add(updateStatus);
// dynamic = new DynamicParameters();
// dynamic.Add("@updateUserid", userId);
// dynamic.Add("@ruid", sysStocksInfo[k].Ruid);
// parameters.Add(dynamic);
// int updateStatusresult = dbConn.Execute(updateStatus);
// }
// }
//}
//for (int i = 0; i < checkStockListParams.Count; i++)
//{
// StringBuilder SqlStringBuilder = new StringBuilder(1024);
// SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_list(order_no,warehouse_id,warehouse_name,zone_id,zone_name,locate_id,locate_name,part_id,part_no,part_spec,carton_no,qty,fact_qty,unit,sn_status,trans_status,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
// SqlStringBuilder.Append(" VALUES(@orderNo,@warehouseId,@warehouseName,@zoneId,@zoneName,@locateId,@locateName,@partId,@partNo,@partSpec,@cartonNo,@qty,@factQty,@unit,@snStatus,@transStatus, ");
// SqlStringBuilder.Append(" @factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
// sqlLists.Add(SqlStringBuilder.ToString());
// dynamic = new DynamicParameters();
// dynamic.Add("@orderNo", checkStockListParams[i].OrderNo);
// dynamic.Add("@warehouseId", checkStockListParams[i].WarehouseId);
// dynamic.Add("@warehouseName", checkStockListParams[i].WarehouseName);
// dynamic.Add("@zoneId", checkStockListParams[i].ZoneId);
// dynamic.Add("@zoneName", checkStockListParams[i].ZoneName);
// dynamic.Add("@locateId", checkStockListParams[i].LocateId);
// dynamic.Add("@locateName", checkStockListParams[i].LocateName);
// dynamic.Add("@partId", checkStockListParams[i].PartId);
// dynamic.Add("@partNo", checkStockListParams[i].PartNo);
// dynamic.Add("@partSpec", checkStockListParams[i].PartSpec);
// dynamic.Add("@cartonNo", checkStockListParams[i].CartonNo);
// dynamic.Add("@qty", checkStockListParams[i].Qty);
// dynamic.Add("@factQty", checkStockListParams[i].FactQty);
// dynamic.Add("@unit", checkStockListParams[i].Unit);
// dynamic.Add("@snStatus", checkStockListParams[i].SnStatus);
// dynamic.Add("@transStatus", checkStockListParams[i].TransStatus);
// dynamic.Add("@factoryId", checkStockListParams[i].FactoryId);
// dynamic.Add("@factoryCode", checkStockListParams[i].FactoryCode);
// dynamic.Add("@createUserid", checkStockListParams[i].CreateUserId);
// parameters.Add(dynamic);
//}
#endregion
#region 批量更改
for (int i = 0; i < checkStockRanges.Count; i++)
{
//开始盘点，状态未冻结的条码 写入盘点清单表
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_list(order_no,warehouse_id,warehouse_name,zone_id,zone_name,locate_id,locate_name,part_id,part_no,part_spec,carton_no,qty,fact_qty,unit,sn_status,trans_status,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
SqlStringBuilder.Append("select @orderNo as order_no,warehouse_id,warehouse_name,zone_id,zone_name,locate_id,locate_name,part_id,part_no,part_spec,carton_no,qty,0,unit,Status,10,Factory_id,Factory_code,enabled,@updateUserid as create_userid,CONVERT(varchar(50), GETDATE(), 21) as create_time,NEWID() as guid from sys_stock (nolock) as aaa where aaa.enabled='Y' and aaa.factory_code=@factoryCode and ( aaa.status=@status40 or aaa.status=@status50 or aaa.status=@status60 or aaa.status=@status70 or aaa.status=@status80 or aaa.status=@status90 ) ");
if (checkStockRanges[i].ZoneId != 0)
SqlStringBuilder.Append(" and aaa.zone_id=@zoneId and aaa.zone_name=@zoneName ");
if (checkStockRanges[i].LocateId != 0)
SqlStringBuilder.Append(" and aaa.locate_id=@locateId and aaa.locate_name=@locateName ");
if (!string.IsNullOrEmpty(checkStockRanges[i].PartNo))
SqlStringBuilder.Append(" and aaa.part_no=@partNo ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@updateUserid", userId);
dynamic.Add("@orderNo", orderNoList[m]);
dynamic.Add("@zoneId", checkStockRanges[i].ZoneId);
dynamic.Add("@zoneName", checkStockRanges[i].ZoneName);
dynamic.Add("@locateId", checkStockRanges[i].LocateId);
dynamic.Add("@locateName", checkStockRanges[i].LocateName);
dynamic.Add("@partNo", string.IsNullOrEmpty(checkStockRanges[i].PartNo) ? "" : checkStockRanges[i].PartNo);
dynamic.Add("@factoryCode", wmsChecks[0].FactoryCode);
dynamic.Add("@status40", (int)WmsEnumUtil.StockStatus.WAITING_INSTOCK);
dynamic.Add("@status50", (int)WmsEnumUtil.StockStatus.INSTOCKED);
dynamic.Add("@status60", (int)WmsEnumUtil.StockStatus.BATCHED);
dynamic.Add("@status70", (int)WmsEnumUtil.StockStatus.ONLINED);
dynamic.Add("@status80", (int)WmsEnumUtil.StockStatus.NC_QUARANTINE);
dynamic.Add("@status90", (int)WmsEnumUtil.StockStatus.NC_INSTOCK);
parameters.Add(dynamic);
#region 记录条码冻结事务
//记录条码冻结事务
SqlStringBuilder = new StringBuilder();
SqlStringBuilder.Append(" INSERT INTO dbo.sys_stock_trans ");
SqlStringBuilder.Append(@" select @trans_code as trans_code,carton_no,part_id,part_no,part_spec,locate_id as src_locate_id,locate_name as src_locate_name,locate_id as dest_locate_id,
locate_name as dest_locate_name, qty as old_qty, qty as new_qty, qty as trans_qty, status as old_status, status as new_status, qms_status as old_qms_status, qms_status as new_qms_status,
unit, factory_id, factory_code, erp_warehouse as src_erp_warehouse, erp_warehouse as dest_erp_warehouse, warehouse_id as src_warehouse_id, warehouse_name as src_warehouse_name,
warehouse_id as dest_warehouse_id, warehouse_name as dest_warehouse_name, zone_id as src_zone_id, zone_name as src_zone_name, zone_id as dest_zone_id, zone_name as dest_zone_name,
enabled, @updateUserid, CONVERT(varchar(50), GETDATE(), 21) as update_time, create_userid, CONVERT(varchar(50), GETDATE(), 21) as create_time, NEWID() guid, null, @ref_order_no
from sys_stock ");
SqlStringBuilder.Append(" where enabled='Y' and factory_code=@factory_code and ( status=@status40 or status=@status50 or status=@status60 or status=@status70 or status=@status80 or status=@status90 ) ");
if (checkStockRanges[i].ZoneId != 0)
SqlStringBuilder.Append(" and zone_id=@zoneId and zone_name=@zoneName ");
if (checkStockRanges[i].LocateId != 0)
SqlStringBuilder.Append(" and locate_id=@locateId and locate_name=@locateName ");
if (!string.IsNullOrEmpty(checkStockRanges[i].PartNo))
SqlStringBuilder.Append(" and part_no=@partNo ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@trans_code", (int)WmsEnumUtil.TransType.CS_FREEZE);
dynamic.Add("@updateUserid", userId);
dynamic.Add("@zoneId", checkStockRanges[i].ZoneId);
dynamic.Add("@zoneName", checkStockRanges[i].ZoneName);
dynamic.Add("@locateId", checkStockRanges[i].LocateId);
dynamic.Add("@locateName", checkStockRanges[i].LocateName);
dynamic.Add("@partNo", string.IsNullOrEmpty(checkStockRanges[i].PartNo) ? "" : checkStockRanges[i].PartNo);
dynamic.Add("@factory_code", wmsChecks[0].FactoryCode);
dynamic.Add("@ref_order_no", orderNo);
dynamic.Add("@status40", (int)WmsEnumUtil.StockStatus.WAITING_INSTOCK);
dynamic.Add("@status50", (int)WmsEnumUtil.StockStatus.INSTOCKED);
dynamic.Add("@status60", (int)WmsEnumUtil.StockStatus.BATCHED);
dynamic.Add("@status70", (int)WmsEnumUtil.StockStatus.ONLINED);
dynamic.Add("@status80", (int)WmsEnumUtil.StockStatus.NC_QUARANTINE);
dynamic.Add("@status90", (int)WmsEnumUtil.StockStatus.NC_INSTOCK);
parameters.Add(dynamic);
#endregion
#region 将条码冻结
//将条码冻结
SqlStringBuilder = new StringBuilder();
SqlStringBuilder.Append("update a set a.enabled='N',a.update_userid=@updateUserid , a.update_time = CONVERT(varchar(50), GETDATE(), 21) from sys_stock a,sys_stock b where a.ruid=b.ruid and b.enabled='Y' and b.factory_code=@factory_code and ( b.status=@status40 or b.status=@status50 or b.status=@status60 or b.status=@status70 or b.status=@status80 or b.status=@status90 ) ");
if (checkStockRanges[i].ZoneId != 0)
SqlStringBuilder.Append(" and b.zone_id=@zoneId and b.zone_name=@zoneName ");
if (checkStockRanges[i].LocateId != 0)
SqlStringBuilder.Append(" and b.locate_id=@locateId and b.locate_name=@locateName ");
if (!string.IsNullOrEmpty(checkStockRanges[i].PartNo))
SqlStringBuilder.Append(" and b.part_no=@partNo ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@updateUserid", userId);
dynamic.Add("@zoneId", checkStockRanges[i].ZoneId);
dynamic.Add("@zoneName", checkStockRanges[i].ZoneName);
dynamic.Add("@locateId", checkStockRanges[i].LocateId);
dynamic.Add("@locateName", checkStockRanges[i].LocateName);
dynamic.Add("@partNo", string.IsNullOrEmpty(checkStockRanges[i].PartNo) ? "" : checkStockRanges[i].PartNo);
dynamic.Add("@factory_code", wmsChecks[0].FactoryCode);
dynamic.Add("@status40", (int)WmsEnumUtil.StockStatus.WAITING_INSTOCK);
dynamic.Add("@status50", (int)WmsEnumUtil.StockStatus.INSTOCKED);
dynamic.Add("@status60", (int)WmsEnumUtil.StockStatus.BATCHED);
dynamic.Add("@status70", (int)WmsEnumUtil.StockStatus.ONLINED);
dynamic.Add("@status80", (int)WmsEnumUtil.StockStatus.NC_QUARANTINE);
dynamic.Add("@status90", (int)WmsEnumUtil.StockStatus.NC_INSTOCK);
parameters.Add(dynamic);
#endregion
}
#endregion
string updateOrderStatus = "update wms_check_stock set order_status=@orderStatus ,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) where order_no=@orderNo ";
sqlLists.Add(updateOrderStatus);
dynamic = new DynamicParameters();
dynamic.Add("@orderStatus", 20);
dynamic.Add("@updateUserid", userId);
dynamic.Add("@orderNo", orderNoList[m]);
parameters.Add(dynamic);
}
bool flag = ExecuteTransaction(sqlLists, parameters);
if (flag == true)
{
result.Add("status", 1);
result.Add("message", "开启盘点成功");
}
else
{
result.Add("status", 0);
result.Add("message", "开启盘点失败");
}
return result;
}
}
public Hashtable onCloseCycle(string orderno, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
List sqlLists = new List();
List parameters = new List();
DynamicParameters dynamic = new DynamicParameters();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
List orders = orderno.Split(",").ToList();
for (int i = 0; i < orders.Count; i++)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" UPDATE dbo.wms_check_stock SET order_status = @orderStatus,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
SqlStringBuilder.Append(" WHERE order_no = @orderNo; ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderStatus", 60);
dynamic.Add("@updateUserid", userId);
dynamic.Add("@orderNo", orders[i]);
parameters.Add(dynamic);
}
bool flag = ExecuteTransaction(sqlLists, parameters);
if (flag == true)
{
result.Add("status", 1);
result.Add("message", "盘点指令关闭完成");
}
else
{
result.Add("status", 0);
result.Add("message", "盘点指令关闭失败");
}
return result;
}
}
//完成盘点
public Hashtable onCompleteCycle(string orderno, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
List sqlStrings = new List();
List parameterList = new List();
DynamicParameters dynamic = new DynamicParameters();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
List orderNos = orderno.Split(",").ToList();
for (int i = 0; i < orderNos.Count; i++)
{
string sql = "select * from sap_cyco where IBLNR = @orderNo";
List sapCycos = dbConn.Query(sql, new { orderNO = orderNos[i] }).ToList();//判断接口接收单号，回传盘点结果
if (sapCycos.Count > 0)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" SELECT ISNULL(CONVERT(INT, SUM(b.fact_qty)),0) fact_qty,c.matnr as part_no,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI,b.unit ");
SqlStringBuilder.Append(" FROM dbo.sap_cyco c ");
SqlStringBuilder.Append(" LEFT JOIN dbo.wms_check_stock a ON c.IBLNR=a.order_no ");
SqlStringBuilder.Append(" LEFT JOIN dbo.wms_check_stock_list b ON a.order_no=b.order_no and c.matnr=b.part_no ");
SqlStringBuilder.Append(" WHERE a.order_no = @orderNo ");
SqlStringBuilder.Append(" and a.order_status in ('20','30') and a.enabled='Y' ");
SqlStringBuilder.Append(" GROUP BY a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.matnr,c.ZEILI,b.unit ");
List checkStocks = dbConn.Query(SqlStringBuilder.ToString(), new { orderNo = orderNos[i] }).ToList();
if (checkStocks.Count > 0)
{
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.WMS_CYCR(IBLNR,ZEILI,MATNR,WERKS,LGORT,MENGE,MEINS,GJAHR ");
SqlStringBuilder.Append(" ,SID,RECTIM,SYNFLG,GUID) ");
SqlStringBuilder.Append(" VALUES(@IBLNR,@ZEILI,@MATNR,@WERKS,@LGORT,@MENGE,@MEINS,@GJAHR ");
SqlStringBuilder.Append(" ,@SID,@RECTIM,@SYNFLG,@GUID) ");
string sid = Guid.NewGuid().ToString().Replace("-", "");
for (int j = 0; j < checkStocks.Count; j++)
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@IBLNR", checkStocks[j].OrderNo);//盘点凭证号
parameters.Add("@ZEILI", checkStocks[j].ZEILI);//盘点单行号
parameters.Add("@MATNR", checkStocks[j].PartNo);//物料编码
parameters.Add("@WERKS", checkStocks[j].FactoryCode);//工厂
parameters.Add("@LGORT", checkStocks[j].ErpWarehouse);//库存地点
parameters.Add("@MENGE", checkStocks[j].FactQty);//盘点数量
parameters.Add("@MEINS", checkStocks[j].Unit);//计量单位
parameters.Add("@GJAHR", DateTime.Now.ToString("yyyy"));
parameters.Add("@SID", sid);
parameters.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
parameters.Add("@SYNFLG", "N");
parameters.Add("@GUID", Guid.NewGuid().ToString());
sqlStrings.Add(SqlStringBuilder.ToString());
parameterList.Add(parameters);
}
}
}
SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" UPDATE dbo.wms_check_stock SET order_status = @orderStatus,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
SqlStringBuilder.Append(" WHERE order_no = @orderNo; ");
sqlStrings.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderStatus", 40);
dynamic.Add("@updateUserid", userId);
dynamic.Add("@orderNo", orderNos[i]);
parameterList.Add(dynamic);
}
bool flag = ExecuteTransaction(sqlStrings, parameterList);
if (flag == true)
{
result.Add("status", 1);
result.Add("message", "完成盘点执行成功");
}
else
{
result.Add("status", 0);
result.Add("message", "完成盘点执行失败");
}
return result;
}
}
//复盘
public Hashtable onReplay(string orderNo, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
Hashtable result = new Hashtable();
List sqlLists = new List();
List parameters = new List();
DynamicParameters dynamic = new DynamicParameters();
List orderNoList = orderNo.Split(",").ToList();
for (int i = 0; i < orderNoList.Count; i++)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("update wms_check_stock_list set up_trans_status=trans_status,trans_status='10',up_fact_qty=fact_qty,fact_qty='0' ");
SqlStringBuilder.Append(",up_dest_warehouse_id=dest_warehouse_id,dest_warehouse_id='0',up_dest_warehouse_name=dest_warehouse_name,dest_warehouse_name='' ");
SqlStringBuilder.Append(",up_dest_zone_id=dest_zone_id,dest_zone_id='0',up_dest_zone_name=dest_zone_name,dest_zone_name='' ");
SqlStringBuilder.Append(",up_dest_locate_id=dest_locate_id,dest_locate_id='0',up_dest_locate_name=dest_locate_name,dest_locate_name='' ");
SqlStringBuilder.Append(",update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) ");
SqlStringBuilder.Append("where order_no=@orderNo ");
sqlLists.Add(SqlStringBuilder.ToString());
dynamic = new DynamicParameters();
dynamic.Add("@orderNo", orderNoList[i]);
dynamic.Add("@updateUserid", userId);
parameters.Add(dynamic);
string updateOrderStatus = "update wms_check_stock set order_status=@orderStatus ,update_userid=@updateUserid, update_time = CONVERT(varchar(50), GETDATE(), 21) where order_no=@orderNo";
sqlLists.Add(updateOrderStatus);
dynamic = new DynamicParameters();
dynamic.Add("@orderStatus", 20);
dynamic.Add("@updateUserid", userId);
dynamic.Add("@orderNo", orderNoList[i]);
parameters.Add(dynamic);
}
bool flag = ExecuteTransaction(sqlLists, parameters);
if (flag == true)
{
result.Add("status", 1);
result.Add("message", "复盘执行成功");
}
else
{
result.Add("status", 0);
result.Add("message", "复盘执行失败");
}
return result;
}
}
public int onClose(String ids, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String delStr = "update wms_check_stock set order_status='60',update_userid=" + userId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE ruid in (" + ids + ")";
int result = dbConn.Execute(delStr);
return result;
}
}
//启用
public int EnableData(String ids, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String delStr = "update wms_check_stock set Enabled='Y',update_userid=" + userId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE ruid in (" + ids + ")";
int result = dbConn.Execute(delStr);
return result;
}
}
//禁用
public int DisableData(String ids, string userId)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
String delStr = "update wms_check_stock set Enabled='N',update_userid=" + userId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE ruid in (" + ids + ")";
int result = dbConn.Execute(delStr);
return result;
}
}
public List getCheckStockListByExportData(string strWhere)
{
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
{
dbConn.Open();
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" select a.*,b.enum_desc as sn_status_desc,c.enum_desc as trans_status_desc,cp.enum_desc as up_trans_status_desc from wms_check_stock_list a (nolock) " +
" LEFT JOIN sys_stock k (nolock) on a.carton_no = k.carton_no LEFT JOIN dbo.sys_enum b ON b.enum_type = 'sys_stock_status' AND k.status = b.enum_value " +
" LEFT JOIN dbo.sys_enum c ON c.enum_type = 'wms_check_stock_list_trans_status' AND a.trans_status = c.enum_value " +
" LEFT JOIN dbo.sys_enum cp ON cp.enum_type = 'wms_check_stock_list_trans_status' AND a.up_trans_status = cp.enum_value ");
SqlStringBuilder.Append(" where " + strWhere);
List result = dbConn.Query(SqlStringBuilder.ToString()).ToList();
return result;
}
}
#endregion 成员方法
}
}