public static void Insert(List <OrderSortEntity> list)
{
if (list == null || list.Count == 0)
{
return;
}
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
string sql = "INSERT INTO wm_order_sort(VEHICLE_NO, BILL_NO, IN_VEHICLE_SORT, PIECES_QTY, Attri1) " +
"VALUES('{0}', '{1}', {2}, {3}, {4});";
foreach (OrderSortEntity item in list)
{
map.Execute(string.Format("DELETE FROM WM_ORDER_SORT WHERE BILL_NO = '{0}' AND Attri1 = {1}", item.BillNo, item.Attri1), null, trans);
map.Execute(string.Format(sql, item.VehicleNo, item.BillNo, item.InVehicleSort, item.PiecesQty, item.Attri1), null, trans);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
public static int CreateSingleTransfer(string creator, string warehouseCode, string skuCode, string fromLocation, decimal qty)
{
string sql = @"INSERT INTO WM_TRANS_HEADER(BILL_STATE, CREATE_DATE, CREATOR, WH_CODE, BILL_TYPE) VALUES('150', NOW(), @Creator, @WarehouseCode, '161');
SELECT LAST_INSERT_ID();";
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
int result = -1;
try
{
object objHeader = map.ExecuteScalar <object>(sql, new { Creator = creator, WarehouseCode = warehouseCode });
sql = @"INSERT INTO WM_TRANS_DETAIL(BILL_ID, SKU_CODE, SOURCE_LC_CODE, TARGET_LC_CODE, QTY, TRANS_QTY, CREATE_DATE)
VALUES(@HeaderID, @SkuCode, @FromLocation, NULL, @Qty, 0, NOW()) ";
result = map.Execute(sql, new
{
HeaderID = objHeader,
SkuCode = skuCode,
FromLocation = fromLocation,
Qty = qty
});
result = ConvertUtil.ToInt(objHeader);
}
catch
{
result = -1;
trans.Rollback();
}
return(result);
}
public static void ChangeInstoreTask(int taskID, string userCode)
{
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
string sql = string.Format(
@"INSERT INTO TASK_HISTORY(ID, TASK_TYPE, BILL_ID, USER_CODE, QTY, CREATE_DATE, CREATOR, CLOSE_DATE, CONFIRM_DATE)
SELECT ID, TASK_TYPE, BILL_ID, USER_CODE, QTY, CREATE_DATE, CREATOR, NOW(), CONFIRM_DATE
FROM TASKS WHERE ID = {0}" , taskID);
map.Execute(sql);
sql = string.Format(
@"INSERT INTO TASKS(TASK_TYPE, BILL_ID, USER_CODE, QTY, CREATE_DATE, CREATOR, CONFIRM_DATE)
SELECT TASK_TYPE, BILL_ID, '{1}', QTY, NOW(), CREATOR, NOW()
FROM TASKS WHERE ID = {0}" , taskID, userCode);
map.Execute(sql);
sql = string.Format(
@"DELETE FROM TASKS WHERE ID = {0} ", taskID);
map.Execute(sql);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 删除一张退货单据及其明细
/// </summary>
public int DeleteReturnBill(int headerID)
{
string sqlDetail = "DELETE FROM wm_crn_detail WHERE BILL_ID = @HeaderID";
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
int rtn = map.Execute(sqlDetail, new { HeaderID = headerID });
if (rtn < 0)
{
trans.Rollback();
return(rtn);
}
string sqlHeader = "DELETE FROM WM_CRN_HEADER WHERE BILL_ID = @HeaderID";
rtn = map.Execute(sqlHeader, new { HeaderID = headerID });
if (rtn < 0)
{
trans.Rollback();
return(rtn);
}
else
{
trans.Commit();
}
return(rtn);
}
/// <summary>
/// 删除装车任务
/// </summary>
/// <param name="list"></param>
/// <param name="userCode">操作人员</param>
public static void DeleteLoadingTask(List <TaskEntity> list, string userCode)
{
if (list == null || list.Count == 0)
{
return;
}
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
string delSql = "DELETE FROM TASKS WHERE ID = {0}";
foreach (TaskEntity item in list)
{
map.Execute(string.Format(delSql, item.TaskID), null, trans);
// 插入日志信息
LogDal.Insert(ELogType.操作任务, userCode, ConvertUtil.ToString(item.BillID), item.TaskName, "任务池管理(新)", item.UserCode);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
public void ImportLocation(DataTable tHeader, string userName)
{
string LocationCode, LocationDesc, ZoneCode = string.Empty;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
DynamicParameters parmsHeader = new DynamicParameters();
parmsHeader.Add("LOCATION_CODE");
parmsHeader.Add("LOCATION_DESC");
parmsHeader.Add("ZONE_CODE");
parmsHeader.AddOut("RET", DbType.Int32);
try
{
foreach (DataRow row in tHeader.Rows)
{
LocationCode = ConvertUtil.ToString(row["货位编号"]);
LocationDesc = ConvertUtil.ToString(row["货位描述"]);
ZoneCode = ConvertUtil.ToString(row["所属货区"]);
if (string.IsNullOrEmpty(LocationCode))
{
throw new Exception("货位编号不能为空。");
}
if (string.IsNullOrEmpty(ZoneCode))
{
throw new Exception("所属货区不能为空。");
}
parmsHeader.Set("LOCATION_CODE", LocationCode);
parmsHeader.Set("LOCATION_DESC", LocationDesc);
parmsHeader.Set("ZONE_CODE", ZoneCode);
map.Execute("P_LOCATION_IMPORT", parmsHeader, trans, CommandType.StoredProcedure);
int billID = parmsHeader.Get <int>("RET");
if (billID == -1)
{
row["导入结果"] = "略过";
}
else
{
row["导入结果"] = "成功";
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 反审：不能是已经开始收货的，必须是自己审核的，不能反审别人审核通过的
/// </summary>
/// <param name="billID"></param>
/// <param name="userName"></param>
/// <returns></returns>
public string CancelCheck(List <AsnBodyEntity> focusedHeaders, string userName, string userCode, out AsnBodyEntity errHeader)
{
errHeader = null;
DynamicParameters parms = new DynamicParameters();
parms.Add("P_BILL_ID");
parms.Add("P_USER_NAME", userName);
parms.Add("P_USER_CODE", userCode);
parms.AddOut("P_RET_VAL", DbType.String, 2);
IMapper map = DatabaseInstance.Instance();
IDbTransaction tran = map.BeginTransaction();
try
{
string result = string.Empty;
foreach (AsnBodyEntity header in focusedHeaders)
{
parms.Set("P_BILL_ID", header.BillID);
//map.Execute("P_PO_CANCEL_FIRST_APPROVE", parms, tran, CommandType.StoredProcedure);
result = parms.Get <string>("P_RET_VAL");
if (result != "1")
{
errHeader = header;
break;
}
}
if (result == "1")
{
tran.Commit();
}
else
{
tran.Rollback();
}
return(result);
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
/// <summary>
/// 收货完成
/// </summary>
/// <param name="focusedHeaders"></param>
/// <param name="userName">操作者姓名，用于记录日志</param>
/// <param name="errHeader"></param>
/// <returns>Y:成功；其他：失败</returns>
public string ReceivedComplete(List <AsnBodyEntity> focusedHeaders, string userName, string authUserCode)
{
string result = "";
DynamicParameters parms = new DynamicParameters();
parms.Add("V_BILL_ID");
parms.Add("V_USER_NAME", userName);
parms.Add("V_AUTH_USER_CODE", authUserCode);
parms.AddOut("V_RESULT", DbType.String, 3000);
IMapper map = DatabaseInstance.Instance();
IDbTransaction tran = map.BeginTransaction();
try
{
foreach (AsnBodyEntity header in focusedHeaders)
{
parms.Set("V_BILL_ID", header.BillID);
map.Execute("P_ASN_CLOSE", parms, tran, CommandType.StoredProcedure);
result = parms.Get <string>("V_RESULT");
if (result != "Y")
{
break;
}
}
if (result == "Y")
{
tran.Commit();
}
else
{
tran.Rollback();
}
return(result);
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
/// <summary>
/// 删除角色
/// </summary>
/// <param name="roleId"></param>
/// <returns>0：不允许删除；1：成功</returns>
public int DeleteRole(int roleId)
{
RoleEntity re = GetRoleInfo(roleId);
if (re.AllowEdit == "N")
{
return(0);
}
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
map.Execute("delete from USER_ROLE where ROLE_ID = @RoleId", new { RoleId = roleId }, trans);
map.Execute("delete from MODULE_ROLE where ROLE_ID = @RoleId", new { RoleId = roleId }, trans);
map.Execute("delete from ROLES where ROLE_ID = @RoleId", new { RoleId = roleId }, trans);
trans.Commit();
return(1);
}
public static void Insert(OrderSortEntity entity)
{
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
string insertSql = "INSERT INTO wm_order_sort(VEHICLE_NO, BILL_NO, IN_VEHICLE_SORT, PIECES_QTY, Attri1) " +
"VALUES('{0}', '{1}', {2}, {3}, {4});";
string deleteSql = "DELETE FROM WM_ORDER_SORT WHERE BILL_NO = '{0}' AND Attri1 = {1}";
map.Execute(string.Format(deleteSql, entity.BillNo, entity.Attri1), null, trans);
map.Execute(string.Format(insertSql, entity.VehicleNo, entity.BillNo, entity.InVehicleSort, entity.PiecesQty, entity.Attri1), null, trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 改变任务
/// </summary>
/// <param name="userCode"></param>
/// <param name="dic"></param>
/// <param name="entity"></param>
/// <returns></returns>
public string TaskChange(string userCode, Dictionary <string, int> dic, TaskEntity entity)
{
IMapper map = DatabaseInstance.Instance();
string sql = "";
if (entity.TaskType == "143")//拣货任务 更改直接更新update就可以了
{
foreach (string str in dic.Keys)
{
sql += String.Format("UPDATE tasks t SET t.USER_CODE='{0}' WHERE t.ID={1};", str, entity.TaskID);
}
}
else
{
sql = String.Format("delete from tasks where ID={0};", entity.TaskID);
string sql2 = "INSERT INTO tasks(TASK_TYPE ,BILL_ID ,USER_CODE ,QTY ,CREATE_DATE ,CREATOR ,TASK_DESC ) "
+ "VALUES( '{0}' ,{1} ,'{2}' ,{3} ,NOW() ,'{4}' ,'{5}' );";
foreach (string str in dic.Keys)
{
sql += String.Format(sql2, entity.TaskType, entity.BillID, str, dic[str], userCode, entity.TaskDesc);
}
}
string result = "";
IDbTransaction trans = map.BeginTransaction();
try
{
result = map.Execute(sql).ToString();
trans.Commit();
result = "Y";
}
catch (Exception ex)
{
trans.Rollback();
result = ex.Message;
}
return(result);
}
/// <summary>
/// 改变用户的任务优先级
/// </summary>
/// <param name="taskList">任务列表</param>
/// <returns></returns>
public static int ChangeUserTaskLevel(List <TaskEntity> taskList)
{
int result = -1;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
foreach (TaskEntity item in taskList)
{
result += map.Execute(
@"UPDATE USER_ROLE R set R.ATTRI1 = @Attri, R.Attri2 = @Enable
WHERE R.USER_CODE = @UserCode AND R.ROLE_ID = @RoleID ",
new { Attri = item.UserAttri, UserCode = item.UserCode, RoleID = item.RoleID, Enable = item.RoleEnabled });
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
return(result);
}
/// <summary>
/// 越库入库确认操作
/// </summary>
/// <param name="entity">越库入库实体</param>
/// <returns></returns>
public string ExecuteCrossInstore(int billID, string location, List <ASNDetailEntity> details, string userName)
{
string errMsg = "";
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
DynamicParameters parms = new DynamicParameters();
parms.Add("BILL_ID", billID);
parms.Add("USER_NAME", userName);
parms.AddOut("RET_VAL", DbType.Int32);
map.Execute("P_ASN_CROSS_INSTRORE", parms, trans, CommandType.StoredProcedure);
int retVal = parms.Get <int>("RET_VAL");
if (retVal == -1)
{
trans.Rollback();
errMsg = "未查到该单据，该单据可能已经被其他人删除。";
}
else if (retVal == -2)
{
trans.Rollback();
errMsg = "单据的状态必须为等待验收才能做越库收货操作。";
}
else if (retVal == -3)
{
trans.Rollback();
errMsg = "单据的收货方式必须为越库，请重新确认单据信息无误后重试。";
}
else
{
parms = new DynamicParameters();
parms.Add("DETAIL_ID");
parms.Add("PUT_QTY");
parms.Add("DUE_DATE");
parms.Add("LOT_NO");
parms.Add("STATUS");
parms.Add("LOCATION", location);
parms.Add("USER_NAME", userName);
parms.AddOut("RET_VAL", DbType.Int32);
foreach (ASNDetailEntity detail in details)
{
parms.Set("DETAIL_ID", detail.DetailID);
parms.Set("PUT_QTY", detail.PutawayQty);
parms.Set("DUE_DATE", detail.ExpDate);
parms.Set("LOT_NO", detail.BatchNO);
parms.Set("STATUS", SysCodeConstant.SEQ_STATUS_QUALIFIED);
map.Execute("P_ASN_CROSS_INSTRORE_DETAIL", parms, trans, CommandType.StoredProcedure);
retVal = parms.Get <int>("RET_VAL");
if (retVal == -1)
{
errMsg = "未查到该单据，该单据可能已经被其他人删除。";
trans.Rollback();
}
}
trans.Commit();
}
return(errMsg);
}
/// <summary>
/// 保存编辑的采购单
/// </summary>
/// <param name="bill"></param>
/// <param name="creator">创建者姓名</param>
/// <returns>-1：单据状态不允许编辑；0：更新表头时失败；1：成功</returns>
public string SaveBill(AsnBodyEntity bill, string creator, out string errMsg)
{
//记录详细的错误，例如具体是哪个物料不存在了等等
errMsg = string.Empty;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
DynamicParameters parms = new DynamicParameters();
parms.Add("P_BILL_ID", bill.BillID);
parms.Add("P_ORG_CODE", bill.OrgCode);
parms.Add("P_BILL_TYPE", bill.BillType);
parms.Add("P_INSTORE_TYPE", bill.InstoreType);
parms.Add("P_PO_NO", bill.OriginalBillNO);
parms.Add("P_CONTRACT_NO", bill.ContractNO);
parms.Add("P_SALES", bill.Sales);
parms.Add("P_SUPPLIER", bill.SupplierCode);
parms.Add("P_REMARK", bill.Remark);
parms.Add("P_CREATOR", creator);
parms.AddOut("P_NEW_BILL_ID", DbType.String, 50);
parms.AddOut("P_RET_VAL", DbType.String, 2);
//先写入主表
map.Execute("P_ASN_SAVE_HEADER", parms, trans, CommandType.StoredProcedure);
//获取返回值，只有1表示成功
string retVal = parms.Get <string>("P_RET_VAL");
if (retVal != "1")
{
trans.Rollback();
return(retVal);
}
//保存明细
int newBillID = parms.Get <int>("P_NEW_BILL_ID");
parms = new DynamicParameters();
parms.Add("P_BILL_ID", newBillID);
parms.Add("P_MTL_CODE");
parms.Add("P_QTY");
parms.Add("P_PRICE");
parms.Add("P_LOT_NO");
parms.Add("P_EXP_DATE");
parms.Add("P_REMARK");
parms.AddOut("P_RET_VAL", DbType.String, 2);
//再写明细
foreach (PODetailEntity line in bill.Details)
{
parms.Set("P_MTL_CODE", line.MaterialCode);
parms.Set("P_QTY", line.PlanQty);
//parms.Set("P_PRICE", line.Price);
parms.Set("P_LOT_NO", line.BatchNO);
parms.Set("P_EXP_DATE", line.ExpDate);
parms.Set("P_REMARK", line.Remark);
map.Execute("P_ASN_SAVE_DETAIL", parms, trans, CommandType.StoredProcedure);
retVal = parms.Get <string>("P_RET_VAL");
if (retVal != "1")
{
if (retVal == "11")
{
errMsg = line.MaterialCode;
}
trans.Rollback();
break;
}
}
if (retVal == "1")
{
trans.Commit();
bill.BillState = BillStateConst.ASN_STATE_CODE_NOT_ARRIVE;
bill.BillStateDesc = BillStateConst.ASN_STATE_DESC_NOT_ARRIVE;
bill.BillID = newBillID;
}
return(retVal);
}
catch (Exception ex) //只能是insert语句产生了异常，先回滚再抛出异常信息
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 导入销售单据，
/// </summary>
/// <param name="tOriginal"></param>
/// <param name="tHeader"></param>
/// <param name="asnType"></param>
/// <param name="userCode"></param>
/// <returns></returns>
public void ImportSO(DataTable tOriginal, DataTable tHeader, string userName)
{
//写原始表、主表和明细
string billNO = string.Empty, supplier, supplierName, remark, contractNO,
salesMan, material, materialName, batchNO, dueDate, warehouse, brand,
billType, baseEntry, baseLine, baseType, QCNumber, ShCompany, ShTel,
Consignee, ShAddress, shipNO, creator, cardBZ, dlryRequire, foNumber;
DateTime createDate;
decimal qty = 0, price = 0;
int rowNO = 0;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
//"DocEntry1", "Object", "SlpCode1", "CardCode1", "CardName1", "QCNumber",
//"shcompany", "consignee", "shtel", "shaddress", "WhsCode1", "Warehouse", "DocDate1", "BaseType1", "BaseEntry1", "BaseLine1"};
DynamicParameters parmsHeader = new DynamicParameters();
parmsHeader.Add("BILL_NO");
parmsHeader.Add("WAREHOUSE");
parmsHeader.Add("DOC_NO"); //BaseEntry1
parmsHeader.Add("DOC_TYPE"); //Object
parmsHeader.Add("SALES_MAN"); //SlpCode1
parmsHeader.Add("CUSTOMER"); //CardCode1
parmsHeader.Add("CUSTOMER_NAME"); //CardName1
parmsHeader.Add("CONTRACT_NO"); //NumAtCard1
parmsHeader.Add("BASE_TYPE"); //基础凭证类型BaseType1
parmsHeader.Add("BASE_ENTRY"); //基础凭证单号BaseEntry1
parmsHeader.Add("BASE_LINE"); //基础凭证行号BaseLine1
parmsHeader.Add("SH_ADDRESS"); //收货人地址shaddress
parmsHeader.Add("SH_TEL"); //收货电话shtel
parmsHeader.Add("CONSIGNEE"); //收货人consignee
parmsHeader.Add("SH_COMPANY"); //收货单位shcompany
parmsHeader.Add("CREATE_DATE"); //凭证日期（建单日期）DocDate1
parmsHeader.Add("QC_NUMBER"); //QCNumber
parmsHeader.Add("REMARK"); //Comments
parmsHeader.Add("SHIP_NO"); //DHLnumber
parmsHeader.Add("CREATOR"); //USER
parmsHeader.Add("CARDBZ"); //CARDBZ，客户备注
parmsHeader.Add("DELIVERY_REQUIRE"); //Deliveryrequire，发货要求
parmsHeader.Add("USER_NAME", userName);
parmsHeader.AddOut("BILL_ID", DbType.Int32);
//"DocEntry1", "LineNum1", "ItemCode1", "Dscription1", "Brand", "Quantity1", "PriceAfVAT", "DistNumber1", "ExpDate1"
DynamicParameters parmsDetail = new DynamicParameters();
parmsDetail.Add("BILL_ID");
parmsDetail.Add("BILL_NO");
parmsDetail.Add("ROW_NO"); //LineNum1
parmsDetail.Add("MATERIAL"); //ItemCode1
parmsDetail.Add("MATERIAL_NAME"); //Dscription1
parmsDetail.Add("BRAND"); //Brand
parmsDetail.Add("BATCH_NO"); //DistNumber1，需要转换
parmsDetail.Add("DUE_DATE"); //ExpDate1，需要去除.
parmsDetail.Add("QTY"); //Quantity1
parmsDetail.Add("PRICE"); //PriceAfVAT
parmsDetail.Add("DOC_TYPE"); //这是表头的单据类型，也要传递到明细的存储过程中，用来确定明细是入或是出
parmsDetail.Add("CONTRACT_NO"); //SaleContNo
parmsDetail.Add("FO_NUM"); //Fonumber
parmsDetail.AddOut("RET_VAL", DbType.Int32);
try
{
foreach (DataRow row in tHeader.Rows)
{
billNO = ConvertUtil.ToString(row["DocEntry1"]);
billType = ConvertUtil.ToString(row["Object"]);
warehouse = ConvertUtil.ToString(row["Warehouse"]);
supplier = ConvertUtil.ToString(row["CardCode1"]);
supplierName = ConvertUtil.ToString(row["CardName1"]);
contractNO = ConvertUtil.ToString(row["NumAtCard1"]);
QCNumber = ConvertUtil.ToString(row["QCNumber"]);
salesMan = ConvertUtil.ToString(row["SlpCode1"]);
createDate = ConvertUtil.ToDatetime(row["DocDate1"]);
baseEntry = ConvertUtil.ToString(row["BaseEntry1"]);
baseLine = ConvertUtil.ToString(row["BaseLine1"]);
baseType = ConvertUtil.ToString(row["BaseType1"]);
ShCompany = ConvertUtil.ToString(row["shcompany"]);
Consignee = ConvertUtil.ToString(row["consignee"]);
ShTel = ConvertUtil.ToString(row["shtel"]);
ShAddress = ConvertUtil.ToString(row["shaddress"]);
remark = ConvertUtil.ToString(row["Comments1"]);
shipNO = ConvertUtil.ToString(row["DHLnumber"]);
creator = ConvertUtil.ToString(row["USER"]);
cardBZ = ConvertUtil.ToString(row["CARDBZ"]);
dlryRequire = ConvertUtil.ToString(row["Deliveryrequire"]);
//单据编号、实体库、供应商、名称、过账日期、基础凭证类型、基础凭证单号均不能为空
if (string.IsNullOrEmpty(billNO))
{
throw new Exception("单据编号不能为空。");
}
if (string.IsNullOrEmpty(warehouse))
{
throw new Exception("实体库不能为空。");
}
if (string.IsNullOrEmpty(supplier))
{
throw new Exception("客户/供应商不能为空。");
}
if (string.IsNullOrEmpty(supplierName))
{
throw new Exception("名称不能为空。");
}
if (string.IsNullOrEmpty(billType))
{
throw new Exception("基础凭证类型不能为空。");
}
if (string.IsNullOrEmpty(baseEntry))
{
throw new Exception("基础凭证单号不能为空。");
}
if (string.IsNullOrEmpty(baseLine))
{
baseLine = "0";
}
parmsHeader.Set("BILL_NO", billNO);
parmsHeader.Set("WAREHOUSE", warehouse);
parmsHeader.Set("DOC_NO", baseEntry);
parmsHeader.Set("DOC_TYPE", billType);
parmsHeader.Set("SALES_MAN", salesMan);
parmsHeader.Set("CUSTOMER", supplier);
parmsHeader.Set("CUSTOMER_NAME", supplierName);
parmsHeader.Set("CONTRACT_NO", contractNO);
parmsHeader.Set("BASE_LINE", baseLine);
parmsHeader.Set("BASE_TYPE", baseType);
parmsHeader.Set("BASE_ENTRY", baseEntry);
parmsHeader.Set("SH_COMPANY", ShCompany);
parmsHeader.Set("SH_ADDRESS", ShAddress);
parmsHeader.Set("SH_TEL", ShTel);
parmsHeader.Set("CONSIGNEE", Consignee);
parmsHeader.Set("CREATE_DATE", createDate);
parmsHeader.Set("QC_NUMBER", QCNumber);
parmsHeader.Set("REMARK", remark);
parmsHeader.Set("SHIP_NO", shipNO);
parmsHeader.Set("CREATOR", creator);
parmsHeader.Set("CARDBZ", cardBZ);
parmsHeader.Set("DELIVERY_REQUIRE", dlryRequire);
map.Execute("P_EXCEL_IMPORT_BILL", parmsHeader, trans, CommandType.StoredProcedure);
int billID = parmsHeader.Get <int>("BILL_ID");
if (billID == -1)
{
row["导入结果"] = "略过";
continue;
}
else if (billID == -2)
{
throw new Exception("基础凭证类型在WMS中未找到匹配，导入取消。");
}
else if (billID == -3)
{
throw new Exception("实体库在WMS中未找到匹配，导入取消。");
}
DataRow[] rows = tOriginal.Select(string.Format("DocEntry1 = '{0}'", billNO));
foreach (DataRow rowDetail in rows)
{
//行号需要转换，如果为空，转换为0
string _rowNO = ConvertUtil.ToString(rowDetail["LineNum1"]);
if (string.IsNullOrEmpty(_rowNO))
{
rowNO = 0;
}
else
{
rowNO = ConvertUtil.ToInt(_rowNO);
}
material = ConvertUtil.ToString(rowDetail["ItemCode1"]);
materialName = ConvertUtil.ToString(rowDetail["Dscription1"]);
brand = ConvertUtil.ToString(rowDetail["Brand"]);
batchNO = ConvertUtil.ToString(rowDetail["DistNumber1"]);
dueDate = ConvertUtil.ToString(rowDetail["ExpDate1"]);
string _price = ConvertUtil.ToString(rowDetail["PriceAfVAT"]);
if (string.IsNullOrEmpty(_price))
{
price = 0;
}
else
{
price = ConvertUtil.ToDecimal(_price.Replace("\"", ""));
}
qty = ConvertUtil.ToDecimal(rowDetail["Quantity1"]);
contractNO = ConvertUtil.ToString(rowDetail["SaleContNo"]);
foNumber = ConvertUtil.ToString(rowDetail["Fonumber"]);
if (string.IsNullOrEmpty(material))
{
throw new Exception(string.Format("单据编号为{0}的行物料号为空。", billNO));
}
if (qty <= 0)
{
throw new Exception(string.Format("单据编号为{0}的行数量小于等于0。", billNO));
}
//批次需要转换为批号，批次有三种形式：LOTNO[]2014ABC0405[]赵峰，3254361[]2014BD-05，空，转为批号应该为：空，3254361，空
if (!string.IsNullOrEmpty(batchNO))
{
if (batchNO.StartsWith("LOTNO"))
{
batchNO = "";
}
else if (batchNO.IndexOf('[') > 0)
{
batchNO = batchNO.Substring(0, batchNO.IndexOf('['));
}
else if (batchNO.IndexOf('-') > 0)
{
batchNO = batchNO.Substring(0, batchNO.IndexOf('-'));
}
else
{
batchNO = "";
}
}
//有效日期需要去除.
if (!string.IsNullOrEmpty(dueDate))
{
dueDate = dueDate.Replace(".", "");
}
parmsDetail.Set("BILL_ID", billID);
parmsDetail.Set("BILL_NO", billNO);
parmsDetail.Set("ROW_NO", rowNO);
parmsDetail.Set("DOC_TYPE", billType);
parmsDetail.Set("MATERIAL", material);
parmsDetail.Set("MATERIAL_NAME", materialName);
parmsDetail.Set("BRAND", brand);
parmsDetail.Set("BATCH_NO", batchNO);
parmsDetail.Set("DUE_DATE", dueDate);
parmsDetail.Set("QTY", qty);
parmsDetail.Set("CONTRACT_NO", contractNO);
parmsDetail.Set("FO_NUM", foNumber);
parmsDetail.Set("PRICE", price.ToString()); //excel文件中没有给价格，先用0填充
map.Execute("P_EXCEL_IMPORT_DETAIL", parmsDetail, trans, CommandType.StoredProcedure);
int result = parmsDetail.Get <int>("RET_VAL");
if (result == -1)
{
trans.Rollback();
throw new Exception(string.Format("未找到单据的表头信息，无法导入明细，单据编号为“{0}”，导入取消。", billNO));
}
}
row["导入结果"] = "成功";
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 保存编辑的采购单
/// </summary>
/// <param name="bill"></param>
/// <param name="creator">创建者姓名</param>
/// <returns>-1：单据状态不允许编辑；0：更新表头时失败；1：成功</returns>
public List <int> SaveBill(string billType, string remark, string whCode,
string creator, List <StockTransEntity> details)
{
List <int> billIdList = new List <int>();
string billState = string.Empty;
//if (billID > 0)
// billState = GetBillState(billID);
////单据已保存
//if (!string.IsNullOrEmpty(billState))
// throw new Exception("单据已保存。");
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
List <StockTransEntity> listCase1 = details.FindAll((item) => { return(item.IsCase == 1); });
List <StockTransEntity> listCase2 = details.FindAll((item) => { return(item.IsCase != 1); });
if (listCase1.Count > 0) // 创建整货补货任务
{
string sql = string.Format("INSERT INTO WM_TRANS_HEADER(BILL_STATE, REMARK, CREATE_DATE, CREATOR, WH_CODE, BILL_TYPE) " +
"VALUES('150', '{0}', NOW(), '{1}', '{2}', '{3}')", remark, creator, whCode, billType);
//先写入主表
map.Execute(sql, null, trans);
//没有异常肯定是成功了
//先获取新生成的单号，然后保存明细
int billId = map.GetAutoIncreasementID("WM_TRANS_HEADER", "ID");
sql = "INSERT INTO WM_TRANS_DETAIL(BILL_ID, SKU_CODE, SOURCE_LC_CODE, TARGET_LC_CODE, QTY, TRANS_QTY, CREATE_DATE) "
+ "VALUES({0}, '{1}', '{2}', '{3}', {4}, 0, NOW())";
//再写明细
foreach (StockTransEntity line in listCase1)
{
string sqlDetail = string.Format(sql, billId, line.Material, line.Location, line.TargetLocation, line.TransferQty);
map.Execute(sqlDetail, null, trans);
}
billIdList.Add(billId);
}
if (listCase2.Count > 0)// 创建非整货（散货）补货任务
{
string sql = string.Format("INSERT INTO WM_TRANS_HEADER(BILL_STATE, REMARK, CREATE_DATE, CREATOR, WH_CODE, BILL_TYPE) " +
"VALUES('150', '{0}', NOW(), '{1}', '{2}', '{3}')", remark, creator, whCode, billType);
//先写入主表
map.Execute(sql, null, trans);
//没有异常肯定是成功了
//先获取新生成的单号，然后保存明细
int billId = map.GetAutoIncreasementID("WM_TRANS_HEADER", "ID");
sql = "INSERT INTO WM_TRANS_DETAIL(BILL_ID, SKU_CODE, SOURCE_LC_CODE, TARGET_LC_CODE, QTY, TRANS_QTY, CREATE_DATE) "
+ "VALUES({0}, '{1}', '{2}', '{3}', {4}, 0, NOW())";
//再写明细
foreach (StockTransEntity line in listCase2)
{
string sqlDetail = string.Format(sql, billId, line.Material, line.Location, line.TargetLocation, line.TransferQty);
map.Execute(sqlDetail, null, trans);
}
billIdList.Add(billId);
}
trans.Commit();
}
catch (Exception ex) //只能是insert语句产生了异常，先回滚再抛出异常信息
{
trans.Rollback();
throw ex;
}
return(billIdList);
}
/// <summary>
/// 保存手工创建的退货单
/// </summary>
/// <param name="bill"></param>
/// <param name="creator">zxq</param>
/// <returns>0：更新表头时失败；1：成功</returns>
public string SaveReturnBill(SOHeaderEntity soHeader, List <ReturnDetailEntity> lstBill, string whCode, DateTime retDate, string retDriver,
string handPerson, string remark, decimal retAmount, string creator, string returnReason, string returnRemark, decimal crnAmount, out string errMsg)
{
errMsg = string.Empty;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
DynamicParameters parms = new DynamicParameters();
parms.Add("V_SO_BILL_ID", soHeader.BillID);
parms.Add("V_BILL_NO", "T0" + whCode + soHeader.BillNO);
parms.Add("V_RETURN_AMOUNT", retAmount);
parms.Add("V_CRN_AMOUNT", crnAmount);
parms.Add("V_RETURN_DATE", retDate);
parms.Add("V_RETURN_DRIVER", retDriver);
parms.Add("V_HAND_PERSON", handPerson);
parms.Add("V_WMSREMARK", remark);
parms.Add("V_USER_CODE", creator);
parms.Add("V_RETURN_REASON", returnReason);
parms.Add("V_RETURN_REMARK", returnRemark);
parms.AddOut("V_NEW_ID", DbType.Int32, 11);
parms.AddOut("V_RESULT", DbType.String, 2);
//先写入主表
map.Execute("P_CRN_SAVE_HEADER", parms, trans, CommandType.StoredProcedure);
//获取返回值，只有1表示成功
string retVal = parms.Get <string>("V_RESULT");
if (retVal != "1")
{
trans.Rollback();
return(retVal);
}
int newID = parms.Get <Int32>("V_NEW_ID");
//保存明细
parms = new DynamicParameters();
parms.Add("V_SO_ID");
parms.Add("V_HEADER_ID");
parms.Add("V_QTY");
parms.Add("V_UM_CODE");
parms.AddOut("V_RESULT", DbType.String, 2);
//再写明细
foreach (ReturnDetailEntity line in lstBill)
{
//退货数量是0的不记录进退货明细
if (line.ReturnQty == 0)
{
continue;
}
parms.Set("V_SO_ID", line.SoDetailID);
parms.Set("V_HEADER_ID", newID);
parms.Set("V_QTY", line.ReturnQty);
parms.Set("V_UM_CODE", line.ReturnUnitCode);
map.Execute("P_CRN_SAVE_DETAIL", parms, trans, CommandType.StoredProcedure);
retVal = parms.Get <string>("V_RESULT");
if (retVal != "1")
{
if (retVal == "11")
{
errMsg = line.MaterialCode;
}
trans.Rollback();
break;
}
}
if (retVal == "1")
{
trans.Commit();
}
return(retVal);
}
catch (Exception ex) //只能是insert语句产生了异常，先回滚再抛出异常信息
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 保存编辑的采购单
/// </summary>
/// <param name="bill"></param>
/// <param name="commitNow">false：存为草稿；true：直接提交</param>
/// <param name="creator">创建者姓名</param>
/// <returns>-1：单据状态不允许编辑；0：更新表头时失败；1：成功</returns>
public string SaveBill(POBodyEntity bill, bool commitNow, string creator, out string errMsg)
{
//记录详细的错误，例如具体是哪个物料不存在了等等
errMsg = string.Empty;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
try
{
DynamicParameters parms = new DynamicParameters();
parms.Add("P_BILL_ID", bill.BillID);
parms.Add("P_ORG_CODE", bill.OrgCode);
//直接提交还是另存为草稿
if (commitNow)
{
parms.Add("P_BILL_STATE", BillStateConst.PO_STATE_CODE_COMMITED);
}
else
{
parms.Add("P_BILL_STATE", BillStateConst.PO_STATE_CODE_DRAFT);
}
parms.Add("P_SOURCE_BILL_ID", bill.SourceBillID);
parms.Add("P_SALES", bill.Sales);
parms.Add("P_SUPPLIER", bill.Supplier);
parms.Add("P_BILL_TYPE", bill.BillType);
parms.Add("P_CONTRACT_NO", bill.ContractNO);
parms.Add("P_REMARK", bill.Remark);
parms.Add("P_PO_STR1", bill.PO_STR1);
parms.Add("P_PO_STR2", bill.PO_STR2);
parms.Add("P_PO_STR3", bill.PO_STR3);
parms.Add("P_PO_STR4", bill.PO_STR4);
parms.Add("P_PO_NUM1", bill.PO_NUM1);
parms.Add("P_PO_NUM2", bill.PO_NUM2);
parms.Add("P_PO_DATE1", bill.PO_DATE1);
parms.Add("P_PO_DATE2", bill.PO_DATE2);
parms.Add("P_CREATOR", creator);
parms.AddOut("P_NEW_BILL_ID", DbType.String, 50);
parms.AddOut("P_RET_VAL", DbType.String, 2);
//先写入主表
map.Execute("P_PO_SAVE_HEADER", parms, trans, CommandType.StoredProcedure);
//获取返回值，只有1表示成功
string retVal = parms.Get <string>("P_RET_VAL");
if (retVal != "1")
{
trans.Rollback();
return(retVal);
}
//保存明细
string newBillID = parms.Get <string>("P_NEW_BILL_ID");
parms = new DynamicParameters();
parms.Add("P_BILL_ID", newBillID);
parms.Add("P_MTL_CODE");
parms.Add("P_QTY");
parms.Add("P_PRICE");
parms.Add("P_REMARK");
parms.AddOut("P_RET_VAL", DbType.String, 2);
//再写明细
foreach (PODetailEntity line in bill.Details)
{
parms.Set("P_MTL_CODE", line.MaterialCode);
parms.Set("P_QTY", line.PlanQty);
parms.Set("P_PRICE", line.Price);
parms.Set("P_REMARK", line.Remark);
map.Execute("P_PO_SAVE_DETAIL", parms, trans, CommandType.StoredProcedure);
retVal = parms.Get <string>("P_RET_VAL");
if (retVal != "1")
{
if (retVal == "11")
{
errMsg = line.MaterialCode;
}
trans.Rollback();
break;
}
}
if (retVal == "1")
{
trans.Commit();
if (commitNow)
{
bill.BillState = BillStateConst.PO_STATE_CODE_COMMITED;
bill.BillStateDesc = BillStateConst.PO_STATE_DESC_COMMITED;
}
else
{
bill.BillState = BillStateConst.PO_STATE_CODE_DRAFT;
bill.BillStateDesc = BillStateConst.PO_STATE_DESC_DRAFT;
}
bill.BillID = newBillID;
}
return(retVal);
}
catch (Exception ex) //只能是insert语句产生了异常，先回滚再抛出异常信息
{
trans.Rollback();
throw ex;
}
}
/// <summary>
/// 导入Excel数据，生成ASN
/// </summary>
/// <param name="tOriginal"></param>
/// <param name="tHeader"></param>
/// <param name="asnType"></param>
/// <param name="userCode"></param>
/// <returns></returns>
public void ImportASN(DataTable tOriginal, DataTable tHeader, string userName)
{
//写原始表、主表和明细
string billNO = string.Empty, supplier, supplierName, remark, contractNO,
salesMan, material, materialName, batchNO, dueDate, warehouse,
docType, docNO, Fonumber, unit, baseLine;
DateTime createDate;
decimal qty = 0, quan_qty, unqual_qty, price;
int rowNO = 0;
IMapper map = DatabaseInstance.Instance();
IDbTransaction trans = map.BeginTransaction();
DynamicParameters parmsHeader = new DynamicParameters();
parmsHeader.Add("BILL_NO");
parmsHeader.Add("WAREHOUSE");
parmsHeader.Add("DOC_NO");
parmsHeader.Add("DOC_TYPE");
parmsHeader.Add("SALES_MAN");
parmsHeader.Add("SUPPLIER");
parmsHeader.Add("SUPPLIER_NAME");
parmsHeader.Add("CONTRACT_NO"); //明细行有个合同编号，表头有个合同号
parmsHeader.Add("BASE_LINE"); //基础凭证行号
parmsHeader.Add("CREATE_DATE");
parmsHeader.Add("REMARK");
parmsHeader.Add("USER_NAME", userName);
parmsHeader.AddOut("BILL_ID", DbType.Int32);
DynamicParameters parmsDetail = new DynamicParameters();
parmsDetail.Add("BILL_ID");
parmsDetail.Add("BILL_NO");
parmsDetail.Add("ROW_NO");
parmsDetail.Add("MATERIAL");
parmsDetail.Add("MATERIAL_NAME");
parmsDetail.Add("UNIT");
parmsDetail.Add("SUPPLIER");
parmsDetail.Add("BATCH_NO");
parmsDetail.Add("DUE_DATE");
parmsDetail.Add("QTY");
parmsDetail.Add("QUAL_QTY");
parmsDetail.Add("UNQUAL_QTY");
parmsDetail.Add("PRICE");
parmsDetail.Add("CONTRACT_NO"); //明细行有个合同编号，表头有个合同号
parmsDetail.Add("FO_NUMBER"); //外商单据编号
parmsDetail.AddOut("RET_VAL", DbType.Int32);
try
{
foreach (DataRow row in tHeader.Rows)
{
billNO = ConvertUtil.ToString(row["DocEntry"]);
warehouse = ConvertUtil.ToString(row["Warehouse"]);
supplier = ConvertUtil.ToString(row["CardCode"]);
supplierName = ConvertUtil.ToString(row["CardName"]);
contractNO = ConvertUtil.ToString(row["NumAtCard"]);
remark = ConvertUtil.ToString(row["Comments"]);
salesMan = ConvertUtil.ToString(row["RprtName"]);
createDate = ConvertUtil.ToDatetime(row["DocDate"]);
docType = ConvertUtil.ToString(row["Object"]);
docNO = ConvertUtil.ToString(row["BaseEntry"]);
baseLine = ConvertUtil.ToString(row["BaseLine"]);
//单据编号、实体库、供应商、名称、过账日期、基础凭证类型、基础凭证单号均不能为空
if (string.IsNullOrEmpty(billNO))
{
throw new Exception("单据编号不能为空。");
}
if (string.IsNullOrEmpty(warehouse))
{
throw new Exception("实体库不能为空。");
}
if (string.IsNullOrEmpty(supplier))
{
throw new Exception("供应商不能为空。");
}
if (string.IsNullOrEmpty(supplierName))
{
throw new Exception("名称不能为空。");
}
if (string.IsNullOrEmpty(docType))
{
throw new Exception("基础凭证类型不能为空。");
}
if (string.IsNullOrEmpty(docNO))
{
throw new Exception("基础凭证单号不能为空。");
}
parmsHeader.Set("BILL_NO", billNO);
parmsHeader.Set("WAREHOUSE", warehouse);
parmsHeader.Set("DOC_NO", docNO);
parmsHeader.Set("DOC_TYPE", docType);
parmsHeader.Set("SALES_MAN", salesMan);
parmsHeader.Set("SUPPLIER", supplier);
parmsHeader.Set("SUPPLIER_NAME", supplierName);
parmsHeader.Set("CONTRACT_NO", contractNO);
parmsHeader.Set("BASE_LINE", baseLine);
parmsHeader.Set("CREATE_DATE", createDate);
parmsHeader.Set("REMARK", remark);
map.Execute("P_ASN_IMPORT_HEADER", parmsHeader, trans, CommandType.StoredProcedure);
int billID = parmsHeader.Get <int>("BILL_ID");
if (billID == -1)
{
row["导入结果"] = "略过";
continue;
}
else if (billID == -2)
{
throw new Exception("基础凭证类型在WMS中未找到匹配，导入取消。");
}
else if (billID == -3)
{
throw new Exception("实体库在WMS中未找到匹配，导入取消。");
}
DataRow[] rows = tOriginal.Select(string.Format("DocEntry = '{0}'", billNO));
foreach (DataRow rowDetail in rows)
{
rowNO = ConvertUtil.ToInt(rowDetail["LineNum"]);
material = ConvertUtil.ToString(rowDetail["ItemValue"]);
materialName = ConvertUtil.ToString(rowDetail["Dscription"]);
batchNO = ConvertUtil.ToString(rowDetail["SupplierNum"]);
dueDate = ConvertUtil.ToString(rowDetail["EffectDate"]);
qty = ConvertUtil.ToDecimal(rowDetail["Quantity"]);
quan_qty = 0;
unqual_qty = 0;
contractNO = ConvertUtil.ToString(rowDetail["NumAtCardLine"]);
Fonumber = ConvertUtil.ToString(rowDetail["Fonumber"]);
unit = "";
if (string.IsNullOrEmpty(material))
{
throw new Exception(string.Format("单据编号为{0}的行物料号为空。", billNO));
}
if (qty <= 0)
{
throw new Exception(string.Format("单据编号为{0}的行数量小于等于0。", billNO));
}
//有效日期需要去除.
if (!string.IsNullOrEmpty(dueDate))
{
dueDate = dueDate.Replace(".", "");
}
parmsDetail.Set("BILL_ID", billID);
parmsDetail.Set("BILL_NO", billNO);
parmsDetail.Set("ROW_NO", rowNO);
parmsDetail.Set("MATERIAL", material);
parmsDetail.Set("MATERIAL_NAME", materialName);
parmsDetail.Set("UNIT", unit);
parmsDetail.Set("SUPPLIER", supplier);
parmsDetail.Set("BATCH_NO", batchNO);
parmsDetail.Set("DUE_DATE", dueDate);
parmsDetail.Set("QTY", qty);
parmsDetail.Set("QUAL_QTY", quan_qty);
parmsDetail.Set("UNQUAL_QTY", unqual_qty);
parmsDetail.Set("PRICE", null); //excel文件中没有给价格，先用0填充
parmsDetail.Set("CONTRACT_NO", contractNO);
parmsDetail.Set("FO_NUMBER", Fonumber);
map.Execute("P_ASN_IMPORT_DETAIL", parmsDetail, trans, CommandType.StoredProcedure);
int result = parmsDetail.Get <int>("RET_VAL");
if (result == -1)
{
trans.Rollback();
throw new Exception(string.Format("未找到单据的表头信息，无法导入明细，单据编号为“{0}”，导入取消。", billNO));
}
}
row["导入结果"] = "成功";
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
