public void SplitComMaterial(int stockID, int qty, List <MaterialEntity> materials)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parms = new DynamicParameters();
parms.Add("STOCK_ID", stockID);
parms.Add("MATERIAL", null, DbType.String, ParameterDirection.Input, 30);
parms.Add("QTY", qty);
parms.AddOut("RET_VAL", DbType.Int32);
foreach (MaterialEntity material in materials)
{
parms.Set("MATERIAL", material.MaterialCode);
map.Execute("P_TOOL_SPLIT_MATERIAL_TEMP", parms, CommandType.StoredProcedure);
}
//将原库存清除
parms = new DynamicParameters();
parms.Add("STOCK_ID", stockID);
parms.Add("QTY", qty);
parms.AddOut("RET_VAL", DbType.Int32);
map.Execute("P_TOOL_DELETE_STOCK", parms, CommandType.StoredProcedure);
}
/// <summary>
/// 获取任务详情
/// </summary>
/// <param name="taskID"></param>
/// <returns></returns>
public DataTable GetTaskDetail(int taskID)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("@V_TASK_ID", taskID);
p.AddOut("@V_RESULT_MSG", DbType.String);
return(map.LoadTable("P_TASK_GET_DETAIL", p, CommandType.StoredProcedure));
}
public string CloseReturn(int billID)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("V_BILLID", billID);
p.AddOut("V_RESULT", DbType.String);
map.Execute("P_CRN_CLOSE", p, CommandType.StoredProcedure);
return(p.Get <string>("V_RESULT"));
}
/// <summary>
/// 检查当前托盘是否符合预期的 称重装车顺序
/// </summary>
public int CheckTuopanIsExpect(string ctCode)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters paras = new DynamicParameters();
paras.Add("V_CT_CODE", ctCode);
paras.AddOut("V_RESULT", DbType.Int32);
map.Execute("P_SO_CONTAINER_WEIGHT_EXPECT", paras, CommandType.StoredProcedure);
return(paras.Get <int>("V_RESULT"));
}
public int CloseTask(TaskEntity entity)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("@V_TASK_ID", entity.TaskID);
p.AddOut("@V_RESULT", DbType.Int32);
map.Execute("P_SO_CLOSE_PICK_TASK_HAND", p, CommandType.StoredProcedure);
return(p.Get <Int32>("@V_RESULT"));
}
/// <summary>
/// 退货单位及其数量维护
/// </summary>
/// <param name="billID">退货单ID</param>
/// <returns></returns>
public static string Tools_Return_Modify(int billID)
{
DynamicParameters pram = new DynamicParameters();
pram.Add("V_BILL_ID", billID);
pram.AddOut("V_MSG", DbType.String, 100);
IMapper map = DatabaseInstance.Instance();
map.Execute("P_TOOL_MODIFY_RETURN_BILL", pram, CommandType.StoredProcedure);
return(pram.Get <string>("V_MSG"));
}
/// <summary>
/// 获取任务对应的订单状态
/// </summary>
/// <param name="taskType"></param>
/// <param name="taskID"></param>
/// <returns></returns>
public string TaskState(string taskType, int taskID)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("@V_TASK_TYPE", taskType);
p.Add("@V_TASK_ID", taskID);
p.AddOut("@V_RESULT_MSG", DbType.String);
map.Execute("P_TASK_GET_STATE", p, CommandType.StoredProcedure);
return(p.Get <string>("@V_RESULT_MSG"));
}
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;
}
}
public static string CreateLoadingTask(string billIDs, string shipNo)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parm = new DynamicParameters();
parm.Add("V_BILL_IDS", billIDs);
parm.Add("V_SHIP_NO", shipNo);
parm.AddOut("V_ERR_MSG", DbType.String, 500);
map.ExecuteScalar <string>("P_SO_CREATE_LOADING_TASK", parm, null, CommandType.StoredProcedure);
return(parm.Get <string>("V_ERR_MSG"));
}
public static string CreateTask(int billID, string taskType)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parm = new DynamicParameters();
parm.Add("v_bill_id", billID);
parm.Add("v_task_type", taskType);
parm.AddOut("v_msg", DbType.String, 30);
map.ExecuteScalar <string>("P_SO_AUTO_TASK_CREATE", parm, null, CommandType.StoredProcedure);
return(parm.Get <string>("v_msg"));
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <param name="warehouse"></param>
/// <returns>1表示成功，-1表示未找到最小单位</returns>
public int ExecuteStock(int id, string warehouse)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters dyna = new DynamicParameters();
dyna.Add("V_ID", id);
dyna.Add("V_WAREHOUSE", warehouse);
dyna.AddOut("V_RESULT", DbType.Int32);
map.Execute("P_COUNT_STOCK", dyna, CommandType.StoredProcedure);
return(dyna.Get <int>("V_RESULT"));
}
/// <summary>
/// 考勤登记
/// </summary>
/// <param name="userID"></param>
/// <param name="onlineType"></param>
/// <returns>1:成功；-1：员工号不存在; -2:密码错误</returns>
public static int LoginRegister(string userID, string onlineType, string userPwd)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("V_USER_CODE", userID);
p.Add("V_USER_PWD", userPwd);
p.Add("V_ONLINE_TYPE", onlineType);
p.AddOut("V_RESULT", DbType.Int32);
return(map.Execute("P_SYS_LOGIN_REGISTER", p, CommandType.StoredProcedure));
}
/// <summary>
///
/// </summary>
/// <param name="billID">单据编号</param>
/// <param name="taskType">任务类型（要干什么）：盘点（140)、移库(141)、上架(142)、下架(143)、补货任务(144)</param>
/// <returns>0:参数没设置对；1：成功；-1：尚未上班，无签到记录；-2：需要配置角色；-3：未查到单据；-4：单据状态不对</returns>
public static string Schedule(int billID, string taskType)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("@V_BILL_ID", billID);
p.Add("@V_TASK_TYPE", taskType);
p.AddOut("@V_RESULT", DbType.String);
map.Execute("P_TASK_SCHEDULE", p, CommandType.StoredProcedure);
return(p.Get <string>("@V_RESULT"));
}
/// <summary>
/// 完成盘点，关闭
/// </summary>
/// <param name="billID"></param>
/// <param name="userName"></param>
/// <returns></returns>
public int CompleteBill(int billID, string userName)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters P = new DynamicParameters();
P.Add("V_BILL_ID", billID);
P.Add("V_USER_NAME", userName);
P.AddOut("V_RESULT", DbType.Int32);
map.Execute("P_COUNT_COMPLETE", P, CommandType.StoredProcedure);
return(P.Get <int>("V_RESULT"));
}
/// <summary>
///
/// </summary>
/// <param name="cardNO"></param>
/// <param name="creator"></param>
/// <returns>1:取消成功；-1：未关联任何单据，已将送货牌还原为初始状态；-2：已开始收货，无法取消</returns>
public int CancelVechile(string cardNO, string creator)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parms = new DynamicParameters();
parms.Add("V_CARD_NO", cardNO);
parms.Add("V_CREATOR", creator);
parms.AddOut("V_RESULT", DbType.Int32);
map.Execute("P_ASN_CANCEL_VEHICLE", parms, CommandType.StoredProcedure);
return(parms.Get <int>("V_RESULT"));
}
/// <summary>
/// 越库收货确认
/// </summary>
public string SaveOverStock(PODetailEntity entity, string targetLocCode, string whCode)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters param = new DynamicParameters();
param.Add("V_DETAIL_ID", entity.DetailID);
param.Add("V_PUT_QTY", entity.PutQty);
param.Add("V_TARGET_LC_CODE", targetLocCode);
param.Add("V_WH_CODE", whCode);
param.AddOut("V_RESULT_MSG", DbType.String);
map.Execute("P_ASN_OVERSTOCK_SAVE", param, CommandType.StoredProcedure);
return(param.Get <string>("V_RESULT_MSG"));
}
/// <summary>
/// 更新单据状态为收货完成
/// 检查：1、单据状态必须为正在验收
/// 2、组分料拆分后的每一行验收数量必须相等
/// </summary>
/// <param name="billID"></param>
/// <param name="userCode"></param>
/// <returns>0：成功；-1：状态不是验收完成或正在上架 -2：验收数量必须等于上架数量 -3：组分料每一行验收数量不相等</returns>
public int SetStatusToPutawayComplete(int billID, string userCode)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parms = new DynamicParameters();
parms.Add("BILL_ID", billID);
parms.Add("USER_NAME", userCode);
parms.AddOut("RET_VAL", DbType.Int32);
map.Execute("P_ASN_PUTAWAY_COMPLETE", parms, CommandType.StoredProcedure);
return(parms.Get <int>("RET_VAL"));
}
public int RestoreBill(int billID, string billNO)
{
DynamicParameters parms = new DynamicParameters();
parms.Add("BILL_ID", billID);
parms.Add("BILL_NO", billNO);
parms.AddOut("RET_VAL", DbType.Int32);
IMapper map = DatabaseInstance.Instance();
map.Execute("P_SO_RESTORE", parms, CommandType.StoredProcedure);
return(parms.Get <int>("RET_VAL"));
}
/// <summary>
/// 生成叫号信息
/// </summary>
/// <param name="callType"></param>
/// <param name="billNO"></param>
/// <param name="description"></param>
/// <param name="taskID">没有写-1</param>
/// <returns></returns>
public string CreateCalling(string callType, string billNO, string description, string userCode, int taskID)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parms = new DynamicParameters();
parms.Add("V_CALL_TYPE", callType);
parms.Add("V_CALL_BILL_NO", billNO);
parms.Add("V_DESCRIPTION", description);
parms.Add("V_USER_CODE", userCode);
parms.Add("V_TASK_ID", taskID);
parms.AddOut("V_MSG", DbType.String);
map.Execute("P_SCREEN_CALLING_INSERT", parms, CommandType.StoredProcedure);
return(parms.Get <string>("V_MSG"));
}
/// <summary>
/// 删除一个ASN单据，会自动将记录转移到历史表中
/// </summary>
/// <param name="billID"></param>
/// <param name="billNO"></param>
/// <param name="userName"></param>
public int DeleteASN(int billID, string billNO, string userName)
{
DynamicParameters parms = new DynamicParameters();
parms.Add("BILL_ID", billID);
parms.Add("BILL_NO", billNO);
parms.Add("USER_NAME", userName);
parms.AddOut("RET_VAL", DbType.Int32);
IMapper map = DatabaseInstance.Instance();
map.Execute("P_ASN_DELETE", parms, CommandType.StoredProcedure);
return(parms.Get <int>("RET_VAL"));
}
/// <summary>
/// 生成清点任务，复核任务，上架任务
/// </summary>
/// <param name="billlNO"></param>
/// <param name="userQD"></param>
/// <param name="userCheck"></param>
/// <param name="userPutaway"></param>
/// <param name="creator"></param>
/// <returns></returns>
public int CreateAsnPlan(int billlNO, string userQD, string userCheck, string userPutaway, string cardNO, string creator)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters p = new DynamicParameters();
p.Add("V_BILL_NO", billlNO);
p.Add("V_USERQD", userQD);
p.Add("V_USERCHECK", userCheck);
p.Add("V_USERPUTAWAY", userPutaway);
p.Add("V_CREATOR", creator);
p.Add("V_CARD_NO", cardNO);
p.AddOut("V_RESULT", DbType.Int32);
map.Execute("P_ASN_CREATE_PLAN", p, CommandType.StoredProcedure);
return(p.Get <int>("V_RESULT"));
}
/// <summary>
///
/// </summary>
/// <param name="billNO"></param>
/// <param name="cardNO"></param>
/// <param name="driver"></param>
/// <param name="contact"></param>
/// <param name="vehicleNO"></param>
/// <param name="creator"></param>
/// <returns>-1: 送货牌不存在；-2：送货牌在使用中；-3：单据状态不是等待到货</returns>
public int CreateVechile(int billNO, string cardNO, string driver, string contact, string vehicleNO, string creator)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parms = new DynamicParameters();
parms.Add("V_BILL_ID", billNO);
parms.Add("V_CARD_NO", cardNO);
parms.Add("V_DRIVER", driver);
parms.Add("V_CONTACT", contact);
parms.Add("V_VEHICLE_NO", vehicleNO);
parms.Add("V_CREATOR", creator);
parms.AddOut("V_RESULT", DbType.Int32);
map.Execute("P_ASN_SAVE_VEHICLE", parms, CommandType.StoredProcedure);
return(parms.Get <int>("V_RESULT"));
}
/// <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="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="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="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>
/// 导入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;
}
}
/// <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;
}
}
