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; } }