You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
496 lines
26 KiB
C#
496 lines
26 KiB
C#
using Dapper;
|
|
using Estsh.Core.Base;
|
|
using Estsh.Core.Dapper;
|
|
using Estsh.Core.Models;
|
|
using Estsh.Core.Repositories;
|
|
using Estsh.Core.Wms.IRepositories;
|
|
using Microsoft.Extensions.Primitives;
|
|
using System.Data;
|
|
using System.Text;
|
|
using static Estsh.Core.Model.EnumUtil.WmsEnumUtil;
|
|
|
|
namespace Estsh.Core.Wms.Repositories
|
|
{
|
|
/// <summary>
|
|
/// 盘点
|
|
/// </summary>
|
|
public class CycleCountRepository : BaseRepository<BaseEntity>, ICycleCountRepository
|
|
{
|
|
public CycleCountRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
{
|
|
|
|
}
|
|
/// <summary>
|
|
/// 获取盘点单据信息
|
|
/// </summary>
|
|
/// <param name="orderNo"></param>
|
|
/// <returns></returns>
|
|
public List<WmsCheckStock> GetCycleCountOrderList(string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
SetObjectDetail rfInfo = new SetObjectDetail();
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append("SELECT a.order_no,c.enum_desc AS order_type_desc,a.create_userid, ");
|
|
SqlStringBuilder.Append("a.create_time,d.emp_no,a.factory_code FROM dbo.wms_check_stock a (nolock) ");
|
|
SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND c.enum_type='wms_check_stock_order_type' ");
|
|
SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id ");
|
|
SqlStringBuilder.Append("WHERE a.order_no like '%" + orderNo + "%' and a.order_status in ('20','30') and a.enabled='Y' ");
|
|
List<WmsCheckStock> wmsChecks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
|
|
return wmsChecks;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取盘点单据信息通过订单号
|
|
/// </summary>
|
|
/// <param name="orderNo"></param>
|
|
/// <returns></returns>
|
|
public List<WmsCheckStock> GetCycleCountOrderListByOrderNo(string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
SetObjectDetail rfInfo = new SetObjectDetail();
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append("SELECT a.order_no,c.enum_desc AS order_type_desc,b.carton_no,CONVERT(INT, b.qty) qty,CONVERT(INT, b.fact_qty) fact_qty,b.part_no ,a.create_userid,b.locate_id,b.locate_name, ");
|
|
SqlStringBuilder.Append("a.create_time,b.zone_id,b.zone_name,d.emp_no,a.factory_code,p.part_spec FROM dbo.wms_check_stock a (nolock) ");
|
|
SqlStringBuilder.Append("LEFT JOIN dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no ");
|
|
SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND c.enum_type='wms_check_stock_order_type' ");
|
|
SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id ");
|
|
SqlStringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
|
|
SqlStringBuilder.Append("WHERE a.order_no = '" + orderNo + "' and a.order_status in ('20','30') and a.enabled='Y' ");
|
|
|
|
List<WmsCheckStock> wmsChecks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
|
|
return wmsChecks;
|
|
}
|
|
}
|
|
//根据库位名称获取库位信息
|
|
public SysLocate GetLocateByLocateName(string locateName)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM dbo.sys_locate (nolock) WHERE locate_name = '" + locateName + "'");
|
|
SysLocate sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).FirstOrDefault();
|
|
return sysLocates;
|
|
}
|
|
}
|
|
|
|
//获取盘点范围
|
|
public WmsCheckStockRange GetWmsCheckStockRangeByLocateName(string orderNo, string locateName, string zoneName)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and locate_name!='' and enabled='Y' ");
|
|
WmsCheckStockRange wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
|
|
if (wmsCheckStockLocate != null)
|
|
{
|
|
stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and locate_name='" + locateName + "' and enabled='Y' ");
|
|
wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
|
|
}
|
|
else
|
|
{
|
|
stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and zone_name='" + zoneName + "' and enabled='Y' ");
|
|
wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
|
|
}
|
|
return wmsCheckStockLocate;
|
|
}
|
|
}
|
|
|
|
//根据库位获取应盘和实盘数据
|
|
public List<WmsCheckStock> GetWmsCheckStockQtyFactByLocateName(string orderNo, string locateName)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" SELECT ISNULL(m.qty, 0) qty, ISNULL(n.fact_qty, 0) AS fact_qty,isnull(m.part_no,n.part_no) part_no,isnull(m.part_spec,n.part_spec) part_spec FROM ");
|
|
SqlStringBuilder.Append(" (SELECT CONVERT(INT, SUM(b.qty)) qty,b.part_no ,p.part_spec FROM dbo.wms_check_stock a (nolock) ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no and b.carton_no not in (select rack_no from wms_rack)");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
|
|
SqlStringBuilder.Append(" WHERE a.order_no = @orderNo and b.locate_name=@locateName and a.order_status in ('20','30') and a.enabled='Y' GROUP BY b.part_no ,p.part_spec) AS m ");
|
|
SqlStringBuilder.Append(" FULL JOIN ");
|
|
SqlStringBuilder.Append(" (SELECT CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,p.part_spec FROM dbo.wms_check_stock a (nolock) ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no and b.carton_no not in (select rack_no from wms_rack)");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
|
|
SqlStringBuilder.Append(" WHERE a.order_no = @orderNo and b.dest_locate_name=@locateName and a.order_status in ('20','30') and a.enabled='Y' GROUP BY b.part_no ,p.part_spec) AS n ");
|
|
SqlStringBuilder.Append(" ON n.part_no = m.part_no ");
|
|
|
|
//StringBuilder query = new StringBuilder(1024);
|
|
//query.Append("SELECT sum(wcsl.qty) AS qty,sum(wcsl.fact_qty) AS fact_qty ,wcsl.part_no,sp.part_spec FROM wms_check_stock_list wcsl (nolock) ");
|
|
//query.Append("JOIN wms_check_stock wcs (nolock) ON wcsl.order_no = wcs.order_no ");
|
|
//query.Append("JOIN sys_part sp (nolock) ON wcsl.part_id = sp.part_id ");
|
|
//query.Append("WHERE wcsl.order_no = @orderNo ");
|
|
//query.Append(" AND (wcsl.dest_locate_name=@locateName OR wcsl.locate_name = @locateName) ");
|
|
//query.Append(" AND (wcs.order_status = 20 OR wcs.order_status = 30) ");
|
|
//query.Append(" AND wcsl.enabled = 'Y' ");
|
|
//query.Append(" AND wcsl.carton_no NOT IN (SELECT rack_no FROM wms_rack) ");
|
|
//query.Append("GROUP BY wcsl.part_no,sp.part_spec ");
|
|
|
|
|
|
List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString(),new { orderNo = orderNo, locateName = locateName }).ToList();
|
|
return checkStocks;
|
|
}
|
|
}
|
|
|
|
//获取完成盘点数据
|
|
public List<WmsCheckStock> GetFinishCycleCountData(string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" SELECT CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI FROM dbo.wms_check_stock a (nolock) ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sap_cyco c ON c.IBLNR=b.order_no and c.matnr=b.part_no ");
|
|
SqlStringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' ");
|
|
SqlStringBuilder.Append(" and a.order_status <> '40' and a.enabled='Y' GROUP BY b.part_no,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI ");
|
|
List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
|
|
return checkStocks;
|
|
}
|
|
}
|
|
|
|
//获取零件号信息
|
|
public SysPart GetPartNOInfoByPartNo(string partNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" SELECT * from sys_part (nolock) where part_no='" + partNo + "' ");
|
|
SysPart sysParts = dbConn.Query<SysPart>(SqlStringBuilder.ToString()).FirstOrDefault();
|
|
return sysParts;
|
|
}
|
|
}
|
|
|
|
//根据判断是不是台车
|
|
public List<WmsRack> GetRackInfo(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("select * from wms_rack (nolock) where rack_no='" + cartonNo + "' ");
|
|
List<WmsRack> sysStocks = dbConn.Query<WmsRack>(stringBuilder.ToString()).ToList();
|
|
return sysStocks;
|
|
}
|
|
}
|
|
|
|
//根据条码判断是不是台车
|
|
public List<SysStock> GetStockByCaronNoTro(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE carton_no='" + cartonNo + "' and carton_type=1 ");
|
|
List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
|
|
return sysStocks;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
//根据台车条码列表
|
|
public List<SysStock> GetStockByCaronNoTroList(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" select b.*,a.rack_no from wms_rack_package a (nolock) inner join sys_stock b (nolock) ");
|
|
SqlStringBuilder.Append(" on a.carton_no=b.carton_no and a.enabled='Y' where a.rack_no='" + cartonNo + "' ");
|
|
|
|
List<SysStock> sysStocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
|
|
return sysStocks;
|
|
}
|
|
}
|
|
//根据成品号获取库存条码
|
|
public List<SysStock> GetStockByTrayNo(string trayNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE tray_no=@trayNo");
|
|
List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString(),new { trayNo = trayNo}).ToList();
|
|
return sysStocks;
|
|
}
|
|
}
|
|
//根据条码获取库存条码
|
|
public List<SysStock> GetStockByCaronNo(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE carton_no = @cartonNo");
|
|
List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString(),new { cartonNo = cartonNo}).ToList();
|
|
return sysStocks;
|
|
}
|
|
}
|
|
//判断盘点单范围库区是否存在零件号
|
|
public List<WmsCheckStockRange> GetWmsCheckStockRangeByIsExsistPartNo(string orderNo, string locateName, string zoneName, string partNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("SELECT* FROM dbo.wms_check_stock_range (nolock) WHERE order_no = @orderNo and enabled='Y' ");
|
|
List<WmsCheckStockRange> wmsCheckStocks = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString(),new { orderNo = orderNo}).ToList();
|
|
|
|
//if (locateName != "" && locateName != null)
|
|
//{
|
|
// stringBuilder.Append(" and locate_name='" + locateName + " '");
|
|
//}
|
|
//if (zoneName != "" && zoneName != null)
|
|
//{
|
|
// stringBuilder.Append(" and zone_name='" + zoneName + "' ");
|
|
//}
|
|
//if (zoneName != "" && zoneName != null)
|
|
//{
|
|
// stringBuilder.Append(" and zone_name='" + zoneName + "' ");
|
|
//}
|
|
//stringBuilder.Append(" and part_no='" + partNo + "' ");
|
|
//List<WmsCheckStockRange> wmsCheckStocks = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).ToList();
|
|
return wmsCheckStocks;
|
|
}
|
|
}
|
|
public List<WmsCheckStockList> GetWmsCheckStockListByRackNoExist(string orderNo, string rackNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" select * from wms_check_stock_list a (nolock) where a.order_no='" + orderNo + "' and a.carton_no='" + rackNo + "' ");
|
|
|
|
List<WmsCheckStockList> wmsCheckStocks = dbConn.Query<WmsCheckStockList>(SqlStringBuilder.ToString()).ToList();
|
|
return wmsCheckStocks;
|
|
}
|
|
}
|
|
|
|
//判断台车条码是否已盘
|
|
public List<WmsCheckStockList> GetWmsCheckStockListByRackNo(string orderNo, string rackNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" select * from wms_check_stock_list a (nolock) inner join wms_rack_package b (nolock) on a.carton_no=b.carton_no and b.enabled='Y' where a.order_no='" + orderNo + "' and b.rack_no='" + rackNo + "' and a.trans_status!='10' ");
|
|
|
|
List<WmsCheckStockList> wmsCheckStocks = dbConn.Query<WmsCheckStockList>(SqlStringBuilder.ToString()).ToList();
|
|
return wmsCheckStocks;
|
|
}
|
|
}
|
|
|
|
//根据条码查询盘点单数据
|
|
public List<WmsCheckStock> GetWmsCheckStockByCartonNo(string orderNo, string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder(1024);
|
|
stringBuilder.Append("SELECT a.order_no,c.enum_desc AS order_type_desc,CONVERT(INT, b.qty) qty,CONVERT(INT, b.fact_qty) fact_qty,b.part_no ,a.create_userid,b.locate_id,b.locate_name, ");
|
|
stringBuilder.Append("a.create_time,b.zone_id,b.zone_name,d.emp_no,a.factory_code,a.order_status,p.part_spec,a.is_obvious,b.trans_status FROM dbo.wms_check_stock a (nolock) ");
|
|
stringBuilder.Append("LEFT JOIN dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no ");
|
|
stringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND c.enum_type='wms_check_stock_order_type' ");
|
|
stringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id ");
|
|
stringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
|
|
stringBuilder.Append("WHERE a.order_no ='" + orderNo + "' AND b.carton_no='" + cartonNo + "' and a.enabled='Y' ");
|
|
List<WmsCheckStock> wmsCheckStocks = dbConn.Query<WmsCheckStock>(stringBuilder.ToString()).ToList();
|
|
return wmsCheckStocks;
|
|
}
|
|
}
|
|
|
|
//根据盘点单号获取盘点单
|
|
public List<WmsCheckStock> GetWmsCheckStockByCartonNo(string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder(1024);
|
|
stringBuilder.Append("SELECT * FROM dbo.wms_check_stock (nolock) ");
|
|
stringBuilder.Append("WHERE order_no ='" + orderNo + "'");
|
|
List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(stringBuilder.ToString()).ToList();
|
|
return checkStocks;
|
|
}
|
|
}
|
|
//新增盘点单明细数据
|
|
public int InsertCheckStockListInfo(WmsCheckStockList checkStockList)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
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,dest_warehouse_id,dest_warehouse_name,dest_zone_id,dest_zone_name,dest_locate_id,dest_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,@destwarehouseId,@destwarehouseName,@destzoneId,@destzoneName,@destlocateId,@destlocateName,@partId,@partNo,@partSpec,@cartonNo,@qty,@factQty,@unit,@snStatus,@transStatus, ");
|
|
SqlStringBuilder.Append(" @factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
|
|
int recCheckStockListNum = dbConn.Execute(SqlStringBuilder.ToString(), checkStockList);
|
|
return recCheckStockListNum;
|
|
}
|
|
}
|
|
|
|
//修改盘点单状态
|
|
public int UpdateCheckStockOrderStatus(string orderNo, string loginId)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder(1024);
|
|
stringBuilder.Append("UPDATE dbo.wms_check_stock SET order_status='40',update_userid=" + loginId + ",update_time=CONVERT(VARCHAR(20),GETDATE(),21) WHERE order_no='" + orderNo + "';");
|
|
int refUpdateStatu = dbConn.Execute(stringBuilder.ToString());
|
|
return refUpdateStatu;
|
|
}
|
|
}
|
|
|
|
//获取台车盘点零件明细
|
|
public List<WmsCheckStockList> GetCheckStockListByRackPartNo(string orderNo, string trolleyNo, string partNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(" select * from wms_check_stock_list a (nolock) left join wms_rack_package b (nolock) on a.carton_no=b.carton_no where a.order_no='" + orderNo + "' and a.rack_no='" + trolleyNo + "' and a.part_no='" + partNo + "' and trans_status!='10' ");
|
|
List<WmsCheckStockList> checkStocks = dbConn.Query<WmsCheckStockList>(SqlStringBuilder.ToString()).ToList();
|
|
return checkStocks;
|
|
}
|
|
}
|
|
|
|
public List<SysStock> CheckCartonNoByRackNo(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append("select * from sys_stock as a (nolock) where carton_no=@carton_no ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@carton_no", cartonNo);
|
|
|
|
return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取此盘点单中箱条码的数据
|
|
/// </summary>
|
|
/// <param name="orderNo"></param>
|
|
/// <param name="cartonNo"></param>
|
|
/// <returns></returns>
|
|
public List<WmsCheckStockList> CheckRackListByCartonNo(string orderNo, string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append("select * from wms_check_stock_list as a (nolock) where order_no=@order_no and carton_no=@carton_no ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_no", orderNo);
|
|
parameters.Add("@carton_no", cartonNo);
|
|
|
|
return dbConn.Query<WmsCheckStockList>(sql.ToString(), parameters).ToList();
|
|
}
|
|
}
|
|
|
|
public List<SysStock> GetStockByRackNo(string rackNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
StringBuilder query = new StringBuilder(1024);
|
|
query.Append("SELECT ss.* from sys_stock ss ");
|
|
query.Append("left join wms_rack_package wrp on ss.carton_no = wrp.carton_no ");
|
|
query.Append("where (wrp.rack_no = @rackNo ");
|
|
query.Append("and wrp.enabled = 'Y' ) ");
|
|
query.Append("UNION ");
|
|
query.Append("SELECT * from sys_stock where ruid = ");
|
|
query.Append("(SELECT MAX(ruid) from sys_stock where carton_no = @rackNo) ");
|
|
|
|
return dbConn.Query<SysStock>(query.ToString(), new { rackNo = rackNo }).ToList();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取订单编号
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public string GetOrderNo(string stockOrder, string p)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
DynamicParameters list = new DynamicParameters();
|
|
list.Add("@order_type", stockOrder);
|
|
list.Add("@order_prefix", p);
|
|
list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);
|
|
|
|
var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
|
|
string result = list.Get<string>("@order_no");
|
|
return result;
|
|
}
|
|
}
|
|
|
|
|
|
//事务批量执行添加、修改
|
|
public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
if (dbConn.State == ConnectionState.Closed)
|
|
{
|
|
dbConn.Open();
|
|
}
|
|
//执行事务
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
if (parameterList == null || parameterList.Count == 0)
|
|
{
|
|
try
|
|
{
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
{
|
|
dbConn.Execute(sqlStrings[i], null, transaction);
|
|
}
|
|
transaction.Commit();
|
|
return 1;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
transaction.Rollback();
|
|
return 0;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
try
|
|
{
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
{
|
|
dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
|
|
}
|
|
transaction.Commit();
|
|
return 1;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
transaction.Rollback();
|
|
return 0;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|