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.
1124 lines
65 KiB
C#
1124 lines
65 KiB
C#
using Dapper;
|
|
using Estsh.Core.Base;
|
|
using Estsh.Core.Dapper;
|
|
using Estsh.Core.Wms.IRepositories;
|
|
using Estsh.Core.Repositories;
|
|
using System.Data;
|
|
using System.Text;
|
|
using Estsh.Core.Models;
|
|
using Estsh.Core.Model.EnumUtil;
|
|
|
|
namespace Estsh.Core.Wms.Repositories
|
|
{
|
|
public class InpdlineRepository : BaseRepository<BaseEntity>, IInpdlineRepository
|
|
{
|
|
public InpdlineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
{
|
|
|
|
}
|
|
|
|
//领料
|
|
public List<WmsMoveHeader> CheckMoveHeader(string onLine, string cartonno)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
List<WmsMoveHeader> WmsQcDetail = new List<WmsMoveHeader>();
|
|
if (cartonno.Trim() != "")
|
|
{
|
|
List<SysStock> stocks = GetCartonInfoByRK(cartonno);
|
|
if (stocks.Count > 0)
|
|
{
|
|
//是箱条码 用简码查一次
|
|
getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join
|
|
(select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' ");
|
|
getqcSQL.Append(" and part_spec=@part_spec ");
|
|
getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no
|
|
where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty ");
|
|
getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time ");
|
|
parameters = new DynamicParameters();
|
|
parameters.Add("@part_spec", stocks[0].PartSpec);
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
if (WmsQcDetail.Count > 0)
|
|
return WmsQcDetail;
|
|
|
|
//是箱条码 用零件号查一次
|
|
getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join
|
|
(select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' ");
|
|
getqcSQL.Append(" and part_no=@part_no ");
|
|
getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no
|
|
where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty ");
|
|
getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time ");
|
|
parameters = new DynamicParameters();
|
|
parameters.Add("@part_no", stocks[0].PartNo);
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
return WmsQcDetail;
|
|
}
|
|
else
|
|
{
|
|
//不是箱条码 当简码查一次
|
|
getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join
|
|
(select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' ");
|
|
getqcSQL.Append(" and part_spec=@part_no ");
|
|
getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no
|
|
where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty ");
|
|
getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time ");
|
|
parameters = new DynamicParameters();
|
|
parameters.Add("@part_no", cartonno);
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
|
|
WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
return WmsQcDetail;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join
|
|
(select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' ");
|
|
getqcSQL.Append("");
|
|
getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no
|
|
where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty ");
|
|
getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time ");
|
|
parameters = new DynamicParameters();
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
|
|
WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
return WmsQcDetail;
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
//领料上线
|
|
public List<WmsMoveHeader> CheckOnlineMoveHeader(string onLine, string carton_no)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,b.out_qty,b.dest_zone_name from wms_move_header as a (nolock)
|
|
left join (select order_no,sum(out_qty) out_qty,dest_zone_name from wms_move_detail group by order_no,dest_zone_name) as b on a.order_no=b.order_no
|
|
");
|
|
getqcSQL.Append(" where a.order_status in (@CREATE,@PROCESSING,@BATCHOVER) and a.order_type=@order_type and ref_order_no!='成品下线大件拉动' and a.wrong_sign in ('0','2') and a.enabled='Y' ) as ccc ");
|
|
if (carton_no != "")
|
|
{
|
|
getqcSQL.Append("where order_no = (select order_no from wms_move_sn where carton_no = '" + carton_no + "' )");
|
|
}
|
|
getqcSQL.Append(" order by case when out_qty>0 then 0 else 1 end, create_time ");
|
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", 0);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
parameters.Add("@BATCHOVER", (int)WmsEnumUtil.MoveOrderStatus.BATCHOVER);
|
|
|
|
List<WmsMoveHeader> WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
|
|
return WmsQcDetail;
|
|
}
|
|
}
|
|
|
|
//大件配料
|
|
public List<WmsMoveHeader> CheckBulkyMoveHeader(string onLine)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join
|
|
(select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' group by order_no,dest_zone_name ) as b on a.order_no=b.order_no
|
|
where a.order_status in (@CREATE,@PROCESSING) and a.order_type=@order_type and a.enabled='Y' and ref_order_no='成品下线大件拉动' and a.wrong_sign in ('0','2') ) as ccc where qty > pick_qty
|
|
order by case when order_status='30' then 0 else 1 end, create_time ");
|
|
// getqcSQL.Append(@"select * from ( select distinct a.*,b.dest_zone_name from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no=b.order_no
|
|
//where a.order_status in (@CREATE,@PROCESSING) and a.order_type=@order_type and a.enabled='Y' and ref_order_no='成品下线大件拉动' ) as ccc order by case when order_status='30' then 0 else 1 end, update_time ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
|
|
List<WmsMoveHeader> WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
return WmsQcDetail;
|
|
}
|
|
}
|
|
|
|
//大件领料上线
|
|
public List<WmsMoveHeader> CheckBulkyOnlineMoveHeader(string onLine, string carton_no)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from (
|
|
select a.*,b.out_qty,b.dest_zone_name from wms_move_header as a (nolock)
|
|
left join (select order_no,sum(out_qty) out_qty,dest_zone_name from wms_move_detail group by order_no,dest_zone_name) as b on a.order_no=b.order_no ");
|
|
getqcSQL.Append(" where a.order_status in (@CREATE,@PROCESSING,@BATCHOVER) and a.order_type=@order_type and ref_order_no='成品下线大件拉动' and a.enabled='Y' ) as ccc ");
|
|
if (carton_no != "")
|
|
{
|
|
getqcSQL.Append("where order_no = (select order_no from wms_move_sn where carton_no in (select carton_no from wms_rack_package where enabled='Y' and rack_no=@carton_no) )");
|
|
}
|
|
|
|
getqcSQL.Append(" order by case when out_qty>0 then 0 else 1 end, create_time ");
|
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK);
|
|
parameters.Add("@CREATE", 0);
|
|
parameters.Add("@carton_no", carton_no);
|
|
parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
|
|
parameters.Add("@BATCHOVER", (int)WmsEnumUtil.MoveOrderStatus.BATCHOVER);
|
|
|
|
|
|
List<WmsMoveHeader> WmsQcDetail = dbConn.Query<WmsMoveHeader>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
return WmsQcDetail;
|
|
}
|
|
}
|
|
|
|
public List<WmsMoveDetail> CheckMoveDetail(string orderNo, int status)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,t.part_spec2,(select top 1 locate_name from sys_stock (nolock) where part_id=a.part_id and status=@status and enabled='Y' and locate_name != '' and locate_name is not null order by create_time) as recommend ,(select top 1 vendor_name from sys_stock as b left join sys_vendor as c on b.vendor_id = c.vendor_id where b.part_id = a.part_id and vendor_name is not null and vendor_name !='' ) as vendor_name from wms_move_detail as a (nolock) left join sys_part as t (nolock) on a.part_id=t.part_id where a.order_no=@order_no and a.enabled='Y' ) as ccc order by recommend");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_no", orderNo);
|
|
parameters.Add("@status", status);
|
|
List<WmsMoveDetail> WmsMoveDetail = dbConn.Query<WmsMoveDetail>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
return WmsMoveDetail;
|
|
}
|
|
}
|
|
public SysStock GetCartonInfo(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder stringBuilder = new StringBuilder(1024);
|
|
stringBuilder.Append("SELECT a.*,b.enum_name as stock_status FROM sys_stock a (nolock) left join sys_enum b (nolock) on a.status=b.enum_value and b.enum_type = 'sys_stock_status' WHERE carton_no=@carton_no");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@carton_no", cartonNo);
|
|
|
|
return dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString(), parameters);
|
|
}
|
|
}
|
|
|
|
public List<WmsRackPackage> GetCarrierByName(string rackNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder stringBuilder = new StringBuilder();
|
|
stringBuilder.Append("select a.* from wms_rack_package a (nolock) left join sys_stock s (nolock) on a.carton_no=s.carton_no where a.rack_no= '" + rackNo + "' and a.enabled='Y' and s.enabled='Y'");
|
|
List<WmsRackPackage> sysLocates = dbConn.Query<WmsRackPackage>(stringBuilder.ToString()).ToList();
|
|
return sysLocates;
|
|
}
|
|
}
|
|
|
|
|
|
public WmsRack GetWmsRackInfo(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append("select * from [dbo].[wms_rack] where rack_no=@carton_no ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@carton_no", cartonNo);
|
|
|
|
return dbConn.QueryFirstOrDefault<WmsRack>(sql.ToString(), parameters);
|
|
}
|
|
}
|
|
|
|
public List<WmsMoveSn> GetMoveSN(string order_no)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append("select * from wms_move_sn (nolock) where order_no=@order_no and enabled='Y' ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_no", order_no);
|
|
|
|
return dbConn.Query<WmsMoveSn>(sql.ToString(), parameters).ToList();
|
|
}
|
|
}
|
|
|
|
public List<SysStock> GetPartByFIFO(string part_id)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append(@"select * from sys_stock (nolock) where part_id=@part_id and status=@status and enabled='Y'
|
|
and create_time=(select min(create_time) from sys_stock (nolock) where part_id=@part_id and status=@status and enabled='Y' )");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@part_id", part_id);
|
|
parameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
|
|
//List<SysStock> dt = dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
|
|
return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
|
|
}
|
|
}
|
|
|
|
|
|
public string BatchingCompleted(string loginId, string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder updateString = new StringBuilder(1024);
|
|
updateString.Append(@"update wms_move_header set batching_status=1,update_userid=@update_userid,update_time=@update_time where order_no=@order_no");
|
|
DynamicParameters updateStringparameters = new DynamicParameters();
|
|
updateStringparameters.Add("@order_no", orderNo);
|
|
updateStringparameters.Add("@update_userid", loginId);
|
|
updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
int rec = dbConn.Execute(updateString.ToString(), updateStringparameters);
|
|
if (rec > 0)
|
|
{
|
|
return "OK";
|
|
}
|
|
else
|
|
{
|
|
return "操作失败，请重新尝试！";
|
|
}
|
|
}
|
|
}
|
|
|
|
//上料
|
|
public List<SysLocate> GetLocateInfo(string locate_name)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
StringBuilder LocateInfoSQL = new StringBuilder(1024);
|
|
LocateInfoSQL.Append(@"select B.erp_warehouse,A.* from sys_locate AS A (nolock) LEFT JOIN sys_zone AS B (nolock) ON A.zone_id=B.zone_id where A.locate_name=@locate_name and A.enabled='Y' and B.enabled='Y' ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@locate_name", locate_name);
|
|
List<SysLocate> LocateInfo = dbConn.Query<SysLocate>(LocateInfoSQL.ToString(), parameters).ToList();
|
|
|
|
return LocateInfo;
|
|
}
|
|
}
|
|
|
|
public List<SysLocatePart> GetLocatePart(string locate_name)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
StringBuilder LocateInfoSQL = new StringBuilder(1024);
|
|
LocateInfoSQL.Append(@" select * from sys_locate_part where locate_name=@locate_name ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@locate_name", locate_name);
|
|
List<SysLocatePart> LocateInfo = dbConn.Query<SysLocatePart>(LocateInfoSQL.ToString(), parameters).ToList();
|
|
|
|
return LocateInfo;
|
|
}
|
|
}
|
|
|
|
public List<WmsMoveDetail> CheckMoveDetail2(string orderNo, int status, string carton_no)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select * from ( select a.*,t.part_spec2,(select top 1 locate_name from sys_locate_part (nolock) where part_id=a.part_id and enabled='Y' and locate_name != '' and locate_name is not null order by create_time) as recommend ,(select top 1 vendor_name from sys_stock as b (nolock) left join sys_vendor as c (nolock) on b.vendor_id = c.vendor_id where b.part_no = a.part_no and vendor_name is not null and vendor_name !='') as vendor_name from wms_move_detail as a (nolock) left join sys_part as t (nolock) on a.part_no=t.part_no where a.order_no=@order_no and a.enabled='Y' ) as ccc ");
|
|
if (carton_no != "")
|
|
{
|
|
StringBuilder GetLocateSQL = new StringBuilder(1024);
|
|
GetLocateSQL.Append(@" select top 1 * from sys_stock (nolock) where carton_no = @carton_no and enabled='Y' ");
|
|
DynamicParameters parameters2 = new DynamicParameters();
|
|
parameters2.Add("@carton_no", carton_no);
|
|
List<SysStock> getStocks = dbConn.Query<SysStock>(GetLocateSQL.ToString(), parameters2).ToList();
|
|
|
|
getqcSQL.Append("order by case when part_no='" + getStocks[0].PartNo + "' then 0 else 1 end, recommend ");
|
|
}
|
|
else
|
|
{
|
|
getqcSQL.Append("order by recommend ");
|
|
}
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_no", orderNo);
|
|
List<WmsMoveDetail> WmsMoveDetail = dbConn.Query<WmsMoveDetail>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
return WmsMoveDetail;
|
|
}
|
|
}
|
|
|
|
public List<SysStock> GetLocateCapacity(string locate_id)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
StringBuilder GetLocateSQL = new StringBuilder(1024);
|
|
GetLocateSQL.Append(@" select isnull(sum(qty),0) qty from sys_stock (nolock) where locate_id=@locate_id and status = @status and enabled='Y' ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@locate_id", locate_id);
|
|
parameters.Add("@status", (int)WmsEnumUtil.StockStatus.ONLINED);
|
|
List<SysStock> LocateInfo = dbConn.Query<SysStock>(GetLocateSQL.ToString(), parameters).ToList();
|
|
|
|
return LocateInfo;
|
|
}
|
|
}
|
|
|
|
public string UpdteStatusOnLine(string loginId, string orderNo, SysStock Stock, List<SysLocate> locate)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
List<string> sqlStrings = new List<string>();
|
|
List<DynamicParameters> parameterList = new List<DynamicParameters>();
|
|
|
|
//修改移库明细表 已收数,
|
|
StringBuilder updateString2 = new StringBuilder(1024);
|
|
updateString2.Append(@"update wms_move_detail set out_qty = out_qty + @out_qty,update_userid=@update_userid,update_time=@update_time,item_status = case when pick_qty = out_qty + @out_qty then @item_status else item_status end where order_no=@order_no and part_id=@part_id ");
|
|
DynamicParameters updateStringparameters2 = new DynamicParameters();
|
|
updateStringparameters2.Add("@order_no", orderNo);
|
|
updateStringparameters2.Add("@part_id", Stock.PartId);
|
|
updateStringparameters2.Add("@out_qty", Stock.Qty);
|
|
updateStringparameters2.Add("@item_status", (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED);
|
|
updateStringparameters2.Add("@update_userid", loginId);
|
|
updateStringparameters2.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
sqlStrings.Add(updateString2.ToString());
|
|
parameterList.Add(updateStringparameters2);
|
|
|
|
//修改移库条码表 40 已上线
|
|
StringBuilder updateString = new StringBuilder(1024);
|
|
updateString.Append(" update wms_move_sn set status=@status,update_userid=@update_userid,update_time=@update_time where carton_no=@carton_no ");
|
|
DynamicParameters updateStringparameters = new DynamicParameters();
|
|
updateStringparameters.Add("@status", (int)WmsEnumUtil.MoveOrderSnStatus.ONLINED);
|
|
updateStringparameters.Add("@carton_no", Stock.CartonNo);
|
|
updateStringparameters.Add("@update_userid", loginId);
|
|
updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
sqlStrings.Add(updateString.ToString());
|
|
parameterList.Add(updateStringparameters);
|
|
|
|
//修改条码表 70 已上线
|
|
StringBuilder updateString5 = new StringBuilder(1024);
|
|
updateString5.Append("update sys_stock set status=@status,zone_id=@zone_id,zone_name=@zone_name,erp_warehouse=@erp_warehouse,update_userid=@update_userid,update_time=@update_time,locate_id=@locate_id,locate_name=@locate_name where carton_no= @carton_no ");
|
|
DynamicParameters updateStringparameters5 = new DynamicParameters();
|
|
updateStringparameters5.Add("@carton_no", Stock.CartonNo);
|
|
updateStringparameters5.Add("@status", (int)WmsEnumUtil.StockStatus.ONLINED);
|
|
updateStringparameters5.Add("@locate_id", locate[0].LocateId);
|
|
updateStringparameters5.Add("@locate_name", locate[0].LocateName);
|
|
updateStringparameters5.Add("@zone_id", locate[0].ZoneId);
|
|
updateStringparameters5.Add("@zone_name", locate[0].ZoneName);
|
|
updateStringparameters5.Add("@erp_warehouse", locate[0].ErpWarehouse);
|
|
updateStringparameters5.Add("@update_userid", loginId);
|
|
updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
sqlStrings.Add(updateString5.ToString());
|
|
parameterList.Add(updateStringparameters5);
|
|
|
|
|
|
//新增 sys_stock_trans 事务表
|
|
StringBuilder updateString4 = new StringBuilder(1024);
|
|
updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans]
|
|
([trans_code]
|
|
,[carton_no]
|
|
,[part_id]
|
|
,[part_no]
|
|
,[part_spec]
|
|
,[src_locate_id]
|
|
,[src_locate_name]
|
|
,[dest_locate_id]
|
|
,[dest_locate_name]
|
|
,[old_qty]
|
|
,[new_qty]
|
|
,[trans_qty]
|
|
,[old_status]
|
|
,[new_status]
|
|
,[old_qms_status]
|
|
,[new_qms_status]
|
|
,[unit]
|
|
,[factory_id]
|
|
,[factory_code]
|
|
,[src_erp_warehouse]
|
|
,[dest_erp_warehouse]
|
|
,[src_warehouse_id]
|
|
,[src_warehouse_name]
|
|
,[dest_warehouse_id]
|
|
,[dest_warehouse_name]
|
|
,[src_zone_id]
|
|
,[src_zone_name]
|
|
,[dest_zone_id]
|
|
,[dest_zone_name]
|
|
,[enabled]
|
|
,[update_userid]
|
|
,[update_time]
|
|
,[create_userid]
|
|
,[create_time]
|
|
,[guid],ref_order_no)
|
|
VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty
|
|
,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid,@ref_order_no )");
|
|
|
|
DynamicParameters updateStringparameters4 = new DynamicParameters();
|
|
updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.PICK_ONLINE);
|
|
updateStringparameters4.Add("@carton_no", Stock.CartonNo);
|
|
updateStringparameters4.Add("@part_id", Stock.PartId);
|
|
updateStringparameters4.Add("@part_no", Stock.PartNo);
|
|
updateStringparameters4.Add("@part_spec", Stock.PartSpec);
|
|
updateStringparameters4.Add("@src_locate_id", Stock.LocateId);
|
|
updateStringparameters4.Add("@src_locate_name", Stock.LocateName);
|
|
updateStringparameters4.Add("@dest_locate_id", locate[0].LocateId);
|
|
updateStringparameters4.Add("@dest_locate_name", locate[0].LocateName);
|
|
updateStringparameters4.Add("@old_qty", Stock.Qty);
|
|
updateStringparameters4.Add("@new_qty", Stock.Qty);
|
|
updateStringparameters4.Add("@trans_qty", Stock.Qty);
|
|
updateStringparameters4.Add("@old_status", Stock.Status);
|
|
updateStringparameters4.Add("@new_status", (int)WmsEnumUtil.StockStatus.ONLINED);
|
|
updateStringparameters4.Add("@old_qms_status", Stock.QmsStatus);
|
|
updateStringparameters4.Add("@new_qms_status", Stock.QmsStatus);
|
|
updateStringparameters4.Add("@unit", Stock.Unit);
|
|
updateStringparameters4.Add("@factory_id", Stock.FactoryId);
|
|
updateStringparameters4.Add("@factory_code", Stock.FactoryCode);
|
|
updateStringparameters4.Add("@src_erp_warehouse", Stock.ErpWarehouse);
|
|
updateStringparameters4.Add("@dest_erp_warehouse", Stock.ErpWarehouse);
|
|
updateStringparameters4.Add("@src_warehouse_id", Stock.WarehouseId);
|
|
updateStringparameters4.Add("@src_warehouse_name", Stock.WarehouseName);
|
|
updateStringparameters4.Add("@dest_warehouse_id", Stock.WarehouseId);
|
|
updateStringparameters4.Add("@dest_warehouse_name", Stock.WarehouseName);
|
|
updateStringparameters4.Add("@src_zone_id", Stock.ZoneId);
|
|
updateStringparameters4.Add("@src_zone_name", Stock.ZoneName);
|
|
updateStringparameters4.Add("@dest_zone_id", Stock.ZoneId);
|
|
updateStringparameters4.Add("@dest_zone_name", Stock.ZoneName);
|
|
updateStringparameters4.Add("@enabled", Stock.Enabled);
|
|
updateStringparameters4.Add("@update_userid", loginId);
|
|
updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters4.Add("@create_userid", loginId);
|
|
updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters4.Add("@guid", Guid.NewGuid());
|
|
updateStringparameters4.Add("@ref_order_no", orderNo);
|
|
|
|
sqlStrings.Add(updateString4.ToString());
|
|
parameterList.Add(updateStringparameters4);
|
|
|
|
//执行事务
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
|
bool successCount = true;
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
{
|
|
if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
|
|
{
|
|
successCount = false;
|
|
}
|
|
}
|
|
if (successCount)
|
|
{
|
|
transaction.Commit();
|
|
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select a.ref_order_no,b.*,t.part_spec2 from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no=b.order_no left join sys_part as t (nolock) on b.part_no=t.part_no where a.order_no=@order_no and b.enabled='Y'");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_no", orderNo);
|
|
List<WmsMoveDetail> WmsMoveDetail = dbConn.Query<WmsMoveDetail>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
if (WmsMoveDetail.Sum(a => a.ItemStatus) == (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED * WmsMoveDetail.Count)
|
|
{
|
|
List<string> sqlStrings2 = new List<string>();
|
|
List<DynamicParameters> parameterList2 = new List<DynamicParameters>();
|
|
//修改移库条码表 40 已上线
|
|
StringBuilder updateString6 = new StringBuilder(1024);
|
|
updateString6.Append(" update wms_move_header set order_status =@order_status ,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ; ");
|
|
DynamicParameters updateStringparameters6 = new DynamicParameters();
|
|
updateStringparameters6.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.COMPLETED);
|
|
updateStringparameters6.Add("@order_no", orderNo);
|
|
updateStringparameters6.Add("@update_userid", loginId);
|
|
updateStringparameters6.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
sqlStrings2.Add(updateString6.ToString());
|
|
parameterList2.Add(updateStringparameters6);
|
|
IDbTransaction transaction2 = dbConn.BeginTransaction();
|
|
bool successCount2 = true;
|
|
for (int i = 0; i < sqlStrings2.Count; i++)
|
|
{
|
|
if (dbConn.Execute(sqlStrings2[i], parameterList2[i], transaction2) < 0)
|
|
{
|
|
successCount2 = false;
|
|
}
|
|
}
|
|
if (successCount2)
|
|
{
|
|
transaction2.Commit();
|
|
}
|
|
else
|
|
{
|
|
transaction.Rollback();
|
|
}
|
|
}
|
|
|
|
return "OK";
|
|
}
|
|
else
|
|
{
|
|
transaction.Rollback();
|
|
return "操作失败，请重新尝试！";
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
public List<WmsRack> GetRkInfos(string RKNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"SELECT * FROM [wms_rack] (nolock) where rack_no=@rack_no ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@rack_no", RKNo);
|
|
|
|
List<WmsRack> WmsRack = dbConn.Query<WmsRack>(getqcSQL.ToString(), parameters).ToList();
|
|
|
|
return WmsRack;
|
|
}
|
|
}
|
|
|
|
public List<SysStock> GetRackPackage(string RKNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
StringBuilder RackPackageSQL = new StringBuilder(1024);
|
|
RackPackageSQL.Append(@"SELECT b.* FROM wms_rack_package as a (nolock) left join sys_stock as b (nolock) on a.carton_no=b.carton_no and a.enabled='Y' where a.rack_no=@rack_no and a.enabled = 'Y' and b.enabled = 'Y' order by qty ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@rack_no", RKNo);
|
|
|
|
List<SysStock> WmsRackPackage = dbConn.Query<SysStock>(RackPackageSQL.ToString(), parameters).ToList();
|
|
|
|
return WmsRackPackage;
|
|
}
|
|
}
|
|
|
|
public List<SysStock> GetCartonInfoByRK(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append("select * from sys_stock (nolock) where carton_no=@carton_no and enabled='Y' ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@carton_no", cartonNo);
|
|
|
|
//List<SysStock> dt = dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
|
|
return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
|
|
}
|
|
}
|
|
|
|
public List<SysStock> GetMaterialInfo(string cartonNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
sql.Append("select * from sys_stock (nolock) where carton_no= @carton_no ");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@carton_no", cartonNo);
|
|
|
|
return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
|
|
}
|
|
}
|
|
public List<SysStock> CheckMaterialSplit(List<SysStock> oldStock, int splitNum, string loginId, int splitCount)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
|
|
List<string> sqlStrings = new List<string>();
|
|
List<DynamicParameters> parameterList = new List<DynamicParameters>();
|
|
|
|
decimal oldQty = oldStock[0].Qty;
|
|
decimal newQty = oldStock[0].Qty - splitNum;
|
|
|
|
//更新条码表，数量
|
|
StringBuilder updateString = new StringBuilder(1024);
|
|
updateString.Append("update sys_stock set qty=@qty where carton_no=@carton_no ");
|
|
DynamicParameters updateStringparameters = new DynamicParameters();
|
|
updateStringparameters.Add("@qty", newQty);
|
|
updateStringparameters.Add("@carton_no", oldStock[0].CartonNo);
|
|
updateStringparameters.Add("@update_userid", loginId);
|
|
updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
sqlStrings.Add(updateString.ToString());
|
|
parameterList.Add(updateStringparameters);
|
|
|
|
|
|
//新增 sys_stock_trans 事务表
|
|
#region sys_stock_trans 事务表
|
|
|
|
StringBuilder updateString4 = new StringBuilder(1024);
|
|
updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans]
|
|
([trans_code]
|
|
,[carton_no]
|
|
,[part_id]
|
|
,[part_no]
|
|
,[part_spec]
|
|
,[src_locate_id]
|
|
,[src_locate_name]
|
|
,[dest_locate_id]
|
|
,[dest_locate_name]
|
|
,[old_qty]
|
|
,[new_qty]
|
|
,[trans_qty]
|
|
,[old_status]
|
|
,[new_status]
|
|
,[old_qms_status]
|
|
,[new_qms_status]
|
|
,[unit]
|
|
,[factory_id]
|
|
,[factory_code]
|
|
,[src_erp_warehouse]
|
|
,[dest_erp_warehouse]
|
|
,[src_warehouse_id]
|
|
,[src_warehouse_name]
|
|
,[dest_warehouse_id]
|
|
,[dest_warehouse_name]
|
|
,[src_zone_id]
|
|
,[src_zone_name]
|
|
,[dest_zone_id]
|
|
,[dest_zone_name]
|
|
,[enabled]
|
|
,[update_userid]
|
|
,[update_time]
|
|
,[create_userid]
|
|
,[create_time]
|
|
,[guid])
|
|
VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty
|
|
,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");
|
|
|
|
DynamicParameters updateStringparameters4 = new DynamicParameters();
|
|
updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.PART_SPLIT);
|
|
updateStringparameters4.Add("@carton_no", oldStock[0].CartonNo);
|
|
updateStringparameters4.Add("@part_id", oldStock[0].PartId);
|
|
updateStringparameters4.Add("@part_no", oldStock[0].PartNo);
|
|
updateStringparameters4.Add("@part_spec", oldStock[0].PartSpec);
|
|
updateStringparameters4.Add("@src_locate_id", oldStock[0].LocateId);
|
|
updateStringparameters4.Add("@src_locate_name", oldStock[0].LocateName);
|
|
updateStringparameters4.Add("@dest_locate_id", oldStock[0].LocateId);
|
|
updateStringparameters4.Add("@dest_locate_name", oldStock[0].LocateName);
|
|
updateStringparameters4.Add("@old_qty", oldQty);
|
|
updateStringparameters4.Add("@new_qty", newQty);
|
|
updateStringparameters4.Add("@trans_qty", oldStock[0].Qty);
|
|
updateStringparameters4.Add("@old_status", oldStock[0].Status);
|
|
updateStringparameters4.Add("@new_status", oldStock[0].Status);
|
|
updateStringparameters4.Add("@old_qms_status", oldStock[0].QmsStatus);
|
|
updateStringparameters4.Add("@new_qms_status", oldStock[0].QmsStatus);
|
|
updateStringparameters4.Add("@unit", oldStock[0].Unit);
|
|
updateStringparameters4.Add("@factory_id", oldStock[0].FactoryId);
|
|
updateStringparameters4.Add("@factory_code", oldStock[0].FactoryCode);
|
|
updateStringparameters4.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse);
|
|
updateStringparameters4.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse);
|
|
updateStringparameters4.Add("@src_warehouse_id", oldStock[0].WarehouseId);
|
|
updateStringparameters4.Add("@src_warehouse_name", oldStock[0].WarehouseName);
|
|
updateStringparameters4.Add("@dest_warehouse_id", oldStock[0].WarehouseId);
|
|
updateStringparameters4.Add("@dest_warehouse_name", oldStock[0].WarehouseName);
|
|
updateStringparameters4.Add("@src_zone_id", oldStock[0].ZoneId);
|
|
updateStringparameters4.Add("@src_zone_name", oldStock[0].ZoneName);
|
|
updateStringparameters4.Add("@dest_zone_id", oldStock[0].ZoneId);
|
|
updateStringparameters4.Add("@dest_zone_name", oldStock[0].ZoneName);
|
|
updateStringparameters4.Add("@enabled", oldStock[0].Enabled);
|
|
updateStringparameters4.Add("@update_userid", loginId);
|
|
updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters4.Add("@create_userid", loginId);
|
|
updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters4.Add("@guid", Guid.NewGuid());
|
|
|
|
sqlStrings.Add(updateString4.ToString());
|
|
parameterList.Add(updateStringparameters4);
|
|
#endregion
|
|
|
|
oldStock[0].CartonNo = checkCartonNo(oldStock[0].CartonNo, splitCount);
|
|
|
|
oldStock[0].Qty = splitNum;
|
|
//插入拆分出的箱条码
|
|
#region 插入拆分出的箱条码
|
|
|
|
StringBuilder updateString3 = new StringBuilder(1024);
|
|
updateString3.Append(@" INSERT INTO [dbo].[sys_stock]
|
|
([vendor_id]
|
|
,[vendor_code]
|
|
,[carton_no]
|
|
,[part_id]
|
|
,[part_no]
|
|
,[part_spec]
|
|
,[lot_no]
|
|
,[fix_lot_no]
|
|
,[status]
|
|
,[qty]
|
|
,[snp_qty]
|
|
,[locate_id]
|
|
,[locate_name]
|
|
,[group_no]
|
|
,[erp_warehouse]
|
|
,[date_code]
|
|
,[qms_status]
|
|
,[ref_order_no]
|
|
,[unit]
|
|
,[dock]
|
|
,[warehouse_id]
|
|
,[warehouse_name]
|
|
,[zone_id]
|
|
,[zone_name]
|
|
,[printed]
|
|
,[print_time]
|
|
,[remark]
|
|
,[factory_id]
|
|
,[factory_code]
|
|
,[enabled]
|
|
,[create_userid]
|
|
,[create_time]
|
|
,[update_userid]
|
|
,[update_time]
|
|
,[guid])
|
|
VALUES(@vendor_id,@vendor_code,@carton_no,@part_id,@part_no,@part_spec,@lot_no,@fix_lot_no,@status,@qty,@snp_qty
|
|
,@locate_id,@locate_name,@group_no,@erp_warehouse,@date_code,@qms_status,@ref_order_no,@unit,@dock,@warehouse_id,@warehouse_name,@zone_id,@zone_name
|
|
,@printed,@print_time,@remark,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid )");
|
|
|
|
DynamicParameters updateStringparameters3 = new DynamicParameters();
|
|
updateStringparameters3.Add("@vendor_id", oldStock[0].VendorId);
|
|
updateStringparameters3.Add("@vendor_code", oldStock[0].VendorName);
|
|
updateStringparameters3.Add("@carton_no", oldStock[0].CartonNo);
|
|
updateStringparameters3.Add("@part_id", oldStock[0].PartId);
|
|
updateStringparameters3.Add("@part_no", oldStock[0].PartNo);
|
|
updateStringparameters3.Add("@part_spec", oldStock[0].PartSpec);
|
|
updateStringparameters3.Add("@lot_no", oldStock[0].LotNo);
|
|
updateStringparameters3.Add("@fix_lot_no", oldStock[0].FixLotNo);
|
|
updateStringparameters3.Add("@status", oldStock[0].Status);
|
|
updateStringparameters3.Add("@qty", splitNum);
|
|
updateStringparameters3.Add("@snp_qty", oldStock[0].SnpQty);
|
|
updateStringparameters3.Add("@locate_id", oldStock[0].LocateId);
|
|
updateStringparameters3.Add("@locate_name", oldStock[0].LocateName);
|
|
updateStringparameters3.Add("@group_no", oldStock[0].GroupNo);
|
|
updateStringparameters3.Add("@erp_warehouse", oldStock[0].ErpWarehouse);
|
|
updateStringparameters3.Add("@date_code", oldStock[0].DateCode);
|
|
updateStringparameters3.Add("@qms_status", oldStock[0].QmsStatus);
|
|
updateStringparameters3.Add("@ref_order_no", oldStock[0].RefOrderNo);
|
|
updateStringparameters3.Add("@unit", oldStock[0].Unit);
|
|
updateStringparameters3.Add("@dock", oldStock[0].Dock);
|
|
updateStringparameters3.Add("@warehouse_id", oldStock[0].WarehouseId);
|
|
updateStringparameters3.Add("@warehouse_name", oldStock[0].WarehouseName);
|
|
updateStringparameters3.Add("@zone_id", oldStock[0].ZoneId);
|
|
updateStringparameters3.Add("@zone_name", oldStock[0].ZoneName);
|
|
updateStringparameters3.Add("@printed", oldStock[0].Printed);
|
|
updateStringparameters3.Add("@print_time", oldStock[0].PrintTime);
|
|
updateStringparameters3.Add("@remark", oldStock[0].Remark);
|
|
updateStringparameters3.Add("@factory_id", oldStock[0].FactoryId);
|
|
updateStringparameters3.Add("@factory_code", oldStock[0].FactoryCode);
|
|
updateStringparameters3.Add("@enabled", oldStock[0].Enabled);
|
|
updateStringparameters3.Add("@update_userid", loginId);
|
|
updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters3.Add("@create_userid", oldStock[0].CreateUserId);
|
|
updateStringparameters3.Add("@create_time", oldStock[0].CreateTime);
|
|
updateStringparameters3.Add("@guid", Guid.NewGuid());
|
|
|
|
sqlStrings.Add(updateString3.ToString());
|
|
parameterList.Add(updateStringparameters3);
|
|
#endregion
|
|
|
|
//新增 sys_stock_trans 事务表
|
|
#region sys_stock_trans 事务表
|
|
|
|
StringBuilder updateString5 = new StringBuilder(1024);
|
|
updateString5.Append(@" INSERT INTO[dbo].[sys_stock_trans]
|
|
([trans_code]
|
|
,[carton_no]
|
|
,[part_id]
|
|
,[part_no]
|
|
,[part_spec]
|
|
,[src_locate_id]
|
|
,[src_locate_name]
|
|
,[dest_locate_id]
|
|
,[dest_locate_name]
|
|
,[old_qty]
|
|
,[new_qty]
|
|
,[trans_qty]
|
|
,[old_status]
|
|
,[new_status]
|
|
,[old_qms_status]
|
|
,[new_qms_status]
|
|
,[unit]
|
|
,[factory_id]
|
|
,[factory_code]
|
|
,[src_erp_warehouse]
|
|
,[dest_erp_warehouse]
|
|
,[src_warehouse_id]
|
|
,[src_warehouse_name]
|
|
,[dest_warehouse_id]
|
|
,[dest_warehouse_name]
|
|
,[src_zone_id]
|
|
,[src_zone_name]
|
|
,[dest_zone_id]
|
|
,[dest_zone_name]
|
|
,[enabled]
|
|
,[update_userid]
|
|
,[update_time]
|
|
,[create_userid]
|
|
,[create_time]
|
|
,[guid])
|
|
VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty
|
|
,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");
|
|
|
|
DynamicParameters updateStringparameters5 = new DynamicParameters();
|
|
updateStringparameters5.Add("@trans_code", (int)WmsEnumUtil.TransType.PART_SPLIT);
|
|
updateStringparameters5.Add("@carton_no", oldStock[0].CartonNo);
|
|
updateStringparameters5.Add("@part_id", oldStock[0].PartId);
|
|
updateStringparameters5.Add("@part_no", oldStock[0].PartNo);
|
|
updateStringparameters5.Add("@part_spec", oldStock[0].PartSpec);
|
|
updateStringparameters5.Add("@src_locate_id", oldStock[0].LocateId);
|
|
updateStringparameters5.Add("@src_locate_name", oldStock[0].LocateName);
|
|
updateStringparameters5.Add("@dest_locate_id", oldStock[0].LocateId);
|
|
updateStringparameters5.Add("@dest_locate_name", oldStock[0].LocateName);
|
|
updateStringparameters5.Add("@old_qty", 0);
|
|
updateStringparameters5.Add("@new_qty", splitNum);
|
|
updateStringparameters5.Add("@trans_qty", oldStock[0].Qty);
|
|
updateStringparameters5.Add("@old_status", oldStock[0].Status);
|
|
updateStringparameters5.Add("@new_status", oldStock[0].Status);
|
|
updateStringparameters5.Add("@old_qms_status", oldStock[0].QmsStatus);
|
|
updateStringparameters5.Add("@new_qms_status", oldStock[0].QmsStatus);
|
|
updateStringparameters5.Add("@unit", oldStock[0].Unit);
|
|
updateStringparameters5.Add("@factory_id", oldStock[0].FactoryId);
|
|
updateStringparameters5.Add("@factory_code", oldStock[0].FactoryCode);
|
|
updateStringparameters5.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse);
|
|
updateStringparameters5.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse);
|
|
updateStringparameters5.Add("@src_warehouse_id", oldStock[0].WarehouseId);
|
|
updateStringparameters5.Add("@src_warehouse_name", oldStock[0].WarehouseName);
|
|
updateStringparameters5.Add("@dest_warehouse_id", oldStock[0].WarehouseId);
|
|
updateStringparameters5.Add("@dest_warehouse_name", oldStock[0].WarehouseName);
|
|
updateStringparameters5.Add("@src_zone_id", oldStock[0].ZoneId);
|
|
updateStringparameters5.Add("@src_zone_name", oldStock[0].ZoneName);
|
|
updateStringparameters5.Add("@dest_zone_id", oldStock[0].ZoneId);
|
|
updateStringparameters5.Add("@dest_zone_name", oldStock[0].ZoneName);
|
|
updateStringparameters5.Add("@enabled", oldStock[0].Enabled);
|
|
updateStringparameters5.Add("@update_userid", loginId);
|
|
updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters5.Add("@create_userid", loginId);
|
|
updateStringparameters5.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
updateStringparameters5.Add("@guid", Guid.NewGuid());
|
|
|
|
sqlStrings.Add(updateString5.ToString());
|
|
parameterList.Add(updateStringparameters5);
|
|
#endregion
|
|
|
|
|
|
//执行事务
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
|
bool successCount = true;
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
{
|
|
if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
|
|
{
|
|
successCount = false;
|
|
}
|
|
}
|
|
if (successCount)
|
|
{
|
|
transaction.Commit();
|
|
return oldStock;
|
|
}
|
|
else
|
|
{
|
|
transaction.Rollback();
|
|
return null;
|
|
}
|
|
}
|
|
}
|
|
public string checkCartonNo(string carton, int splitCount)
|
|
{
|
|
List<SysStock> MaterialInfo = GetMaterialInfo(carton + "-" + splitCount);
|
|
if (MaterialInfo.Count > 0)
|
|
{
|
|
//有重复
|
|
return checkCartonNo(carton, splitCount + 1);
|
|
}
|
|
else
|
|
{
|
|
return carton + "-" + splitCount;
|
|
}
|
|
}
|
|
|
|
public string SetMoveOrderClose(string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
dbConn.Open();
|
|
string result = string.Empty;
|
|
|
|
List<string> sqlLists = new List<string>();
|
|
List<DynamicParameters> parameters = new List<DynamicParameters>();
|
|
DynamicParameters dynamic = new DynamicParameters();
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
|
string sqldetailcheck = "SELECT * FROM wms_move_detail (NOLOCK) WHERE order_no ='" + orderNo + "' and pick_qty!=out_qty";
|
|
List<WmsMoveDetail> wmsMoves = dbConn.Query<WmsMoveDetail>(sqldetailcheck).ToList();
|
|
if (wmsMoves.Count > 0)
|
|
{
|
|
return "操作数小于配料数不能关闭单据，请检查！";
|
|
}
|
|
|
|
|
|
SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append(@"update sys_locate_part set is_safety_pull= 0 where safety_pull_order=@order_no ");
|
|
sqlLists.Add(SqlStringBuilder.ToString());
|
|
dynamic = new DynamicParameters();
|
|
dynamic.Add("@order_no", orderNo);
|
|
parameters.Add(dynamic);
|
|
|
|
|
|
SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append("update wms_move_header set order_status='60',update_userid=@updateUserid,update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE order_no=@order_no");
|
|
sqlLists.Add(SqlStringBuilder.ToString());
|
|
dynamic = new DynamicParameters();
|
|
dynamic.Add("@updateUserid", 0);
|
|
dynamic.Add("@order_no", orderNo);
|
|
parameters.Add(dynamic);
|
|
|
|
|
|
SqlStringBuilder = new StringBuilder(1024);
|
|
SqlStringBuilder.Append("update wms_move_detail set item_status='100',update_userid=@updateUserid,update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE order_no=@order_no");
|
|
sqlLists.Add(SqlStringBuilder.ToString());
|
|
dynamic = new DynamicParameters();
|
|
dynamic.Add("@updateUserid", 0);
|
|
dynamic.Add("@order_no", orderNo);
|
|
parameters.Add(dynamic);
|
|
|
|
bool flag = ExecuteTransaction(sqlLists, parameters);
|
|
if (flag)
|
|
{
|
|
return "OK";
|
|
}
|
|
else
|
|
{
|
|
return "操作失败，请重新尝试！";
|
|
}
|
|
}
|
|
}
|
|
//根据单据号更新单据主表
|
|
public int UpdateMoveDetailByOrderNo(string orderNo, string loginId)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
if (dbConn.State == ConnectionState.Closed)
|
|
{
|
|
dbConn.Open();
|
|
}
|
|
StringBuilder updateString6 = new StringBuilder(1024);
|
|
updateString6.Append(" update wms_move_header set order_status =@order_status ,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ; ");
|
|
DynamicParameters updateStringparameters6 = new DynamicParameters();
|
|
updateStringparameters6.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.COMPLETED);
|
|
updateStringparameters6.Add("@order_no", orderNo);
|
|
updateStringparameters6.Add("@update_userid", loginId);
|
|
updateStringparameters6.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
int retNum = dbConn.Execute(updateString6.ToString(), updateStringparameters6);
|
|
return retNum;
|
|
}
|
|
}
|
|
|
|
|
|
//根据单据号获取明细数据
|
|
public List<WmsMoveDetail> GetWmsMoveDetailByOrderNo(string orderNo)
|
|
{
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
{
|
|
if (dbConn.State == ConnectionState.Closed)
|
|
{
|
|
dbConn.Open();
|
|
}
|
|
StringBuilder getqcSQL = new StringBuilder(1024);
|
|
getqcSQL.Append(@"select a.ref_order_no,b.*,t.part_spec2 from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no=b.order_no left join sys_part as t (nolock) on b.part_no=t.part_no where a.order_no=@order_no and b.enabled='Y'");
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
parameters.Add("@order_no", orderNo);
|
|
List<WmsMoveDetail> WmsMoveDetail = dbConn.Query<WmsMoveDetail>(getqcSQL.ToString(), parameters).ToList();
|
|
return WmsMoveDetail;
|
|
}
|
|
}
|
|
|
|
|
|
//事务批量执行添加、修改
|
|
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;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|