public static string StorageReturnUpdate(XBase.Model.Office.StorageManager.StorageReturn sr, List <XBase.Model.Office.StorageManager.StorageReturnDetail> srdList, Hashtable ht) { // return "1|"; //定义返回变量 string res = string.Empty; /* * 定义日志内容变量 * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库 * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件 */ //获取当前用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //执行操作 try { //执行操作 res = XBase.Data.Office.StorageManager.StorageReturnDBHelper.StorageReturnUpdate(sr, srdList, ht); } catch (Exception ex) { //输出日志 WriteSystemLog(userInfo, ex); } //定义变量 string remark; //成功时 if (res.Split('|')[0] == "1") { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } else { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //操作日志 LogInfoModel logModel = InitLogInfo(sr.ReturnNo); //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空 logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //设置操作成功标识 logModel.Remark = remark; //登陆日志 LogDBHelper.InsertLog(logModel); return(res); }
public static string StorageReturnAdd(StorageReturn sr, List<StorageReturnDetail> srdList, Hashtable ht) { if (!StorageReturnDetail(sr.ReturnNo, sr.CompanyCD)) return "1|该借货单货单据编号已经存在"; #region 构造字符串 StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.StorageReturn("); strSql.Append("CompanyCD,ReturnNo,Title,FromType,FromBillID,StorageID,DeptID,ReturnPerson,ReturnDate,TotalPrice,CountTotal,Summary,Transactor,Remark,Creator,CreateDate,BillStatus)"); strSql.Append(" values ("); strSql.Append("@CompanyCD,@ReturnNo,@Title,@FromType,@FromBillID,@StorageID,@DeptID,@ReturnPerson,@ReturnDate,@TotalPrice,@CountTotal,@Summary,@Transactor,@Remark,@Creator,@CreateDate,@BillStatus)"); strSql.Append(";select @ID=@@IDENTITY "); SqlParameter[] parameters = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@ReturnNo", SqlDbType.VarChar,50), new SqlParameter("@Title", SqlDbType.VarChar,100), new SqlParameter("@FromType", SqlDbType.Char,1), new SqlParameter("@FromBillID", SqlDbType.Int,4), new SqlParameter("@StorageID", SqlDbType.Int,4), new SqlParameter("@DeptID", SqlDbType.Int,4), new SqlParameter("@ReturnPerson", SqlDbType.Int,4), new SqlParameter("@ReturnDate", SqlDbType.DateTime), new SqlParameter("@TotalPrice", SqlDbType.Decimal,9), new SqlParameter("@CountTotal", SqlDbType.Decimal,9), new SqlParameter("@Summary", SqlDbType.VarChar,200), new SqlParameter("@Transactor", SqlDbType.Int,4), new SqlParameter("@Remark", SqlDbType.VarChar,800), new SqlParameter("@Creator",SqlDbType.Int), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@BillStatus", SqlDbType.Char,1), new SqlParameter("@ID",SqlDbType.Int)}; parameters[0].Value = sr.CompanyCD; parameters[1].Value = sr.ReturnNo; parameters[2].Value = sr.Title; parameters[3].Value = sr.FromType; parameters[4].Value = sr.FromBillID; parameters[5].Value = sr.StorageID; parameters[6].Value = sr.DeptID; parameters[7].Value = sr.ReturnPerson; parameters[8].Value = sr.ReturnDate; parameters[9].Value = sr.TotalPrice; parameters[10].Value = sr.CountTotal; parameters[11].Value = sr.Summary; parameters[12].Value = sr.Transactor; parameters[13].Value = sr.Remark; parameters[14].Value = sr.Creator; parameters[15].Value = sr.CreateDate; parameters[16].Value = sr.BillStatus; parameters[17].Direction = ParameterDirection.Output; #endregion SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Parameters.AddRange(parameters); sqlCmd.CommandText = strSql.ToString(); ArrayList SqlCmdList = new ArrayList(); SqlCmdList.Add(sqlCmd); #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(sr, ht, cmd); if (ht.Count > 0) SqlCmdList.Add(cmd); #endregion #region 构造明细字符串 if (srdList != null && srdList.Count > 0) { foreach (StorageReturnDetail model in srdList) { #region 构造字符串 StringBuilder strSqlDetail = new StringBuilder(); strSqlDetail.Append("insert into officedba.StorageReturnDetail("); strSqlDetail.Append("CompanyCD,ReturnNo,SortNo,ProductID,ProductName,UnitID,ProductCount,ReturnCount,UnitPrice,TotalPrice,FromType,FromBillID,FromLineNo,Remark,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)"); strSqlDetail.Append(" values ("); strSqlDetail.Append("@CompanyCD,@ReturnNo,@SortNo,@ProductID,@ProductName,@UnitID,@ProductCount,@ReturnCount,@UnitPrice,@TotalPrice,@FromType,@FromBillID,@FromLineNo,@Remark,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)"); strSql.Append(";select @ID= @@IDENTITY"); SqlParameter[] paras = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@ReturnNo", SqlDbType.VarChar,50), new SqlParameter("@SortNo", SqlDbType.Int,4), new SqlParameter("@ProductID", SqlDbType.Int,4), new SqlParameter("@ProductName", SqlDbType.VarChar,100), new SqlParameter("@UnitID", SqlDbType.Int,4), new SqlParameter("@ProductCount", SqlDbType.Decimal,9), new SqlParameter("@ReturnCount", SqlDbType.Decimal,9), new SqlParameter("@UnitPrice", SqlDbType.Decimal,9), new SqlParameter("@TotalPrice", SqlDbType.Decimal,9), new SqlParameter("@FromType", SqlDbType.Char,1), new SqlParameter("@FromBillID", SqlDbType.Int,4), new SqlParameter("@FromLineNo", SqlDbType.Int,4), new SqlParameter("@Remark", SqlDbType.VarChar,100), new SqlParameter("@UsedUnitID", SqlDbType.Int,4), new SqlParameter("@UsedUnitCount", SqlDbType.Decimal,9), new SqlParameter("@UsedPrice", SqlDbType.Decimal,9), new SqlParameter("@ExRate", SqlDbType.Decimal,9), new SqlParameter("@ID",SqlDbType.Int), new SqlParameter("@BatchNo",SqlDbType.VarChar) }; paras[0].Value = model.CompanyCD; paras[1].Value = model.ReturnNo; paras[2].Value = model.SortNo; paras[3].Value = model.ProductID; paras[4].Value = model.ProductName; if (model.UnitID != null) paras[5].Value = model.UnitID; else paras[5].Value = DBNull.Value; paras[6].Value = model.ProductCount; paras[7].Value = model.ReturnCount; paras[8].Value = model.UnitPrice; paras[9].Value = model.TotalPrice; paras[10].Value = model.FromType; paras[11].Value = model.FromBillID; paras[12].Value = model.FromLineNo; paras[13].Value = model.Remark; paras[14].Value = model.UsedUnitID; paras[15].Value = model.UsedUnitCount; paras[16].Value = model.UsedPrice; paras[17].Value = model.ExRate; paras[18].Direction = ParameterDirection.Output; paras[19].Value = model.BatchNo; #endregion SqlCommand sqlCmdDetail = new SqlCommand(); sqlCmdDetail.CommandText = strSqlDetail.ToString(); sqlCmdDetail.Parameters.AddRange(paras); SqlCmdList.Add(sqlCmdDetail); } } #endregion bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList); if (result) { string ID = string.Empty; ID = ((SqlCommand)SqlCmdList[0]).Parameters["@ID"].Value.ToString(); return "2|" + ID + "#" + sr.ReturnNo; } else return "3|保存数据失败"; }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(StorageReturn model, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.StorageReturn set "; foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例 { strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ","; cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim()); } int iLength = strSql.Length - 1; strSql = strSql.Substring(0, iLength); strSql += " where CompanyCD = @CompanyCD AND ReturnNo = @ReturnNo"; cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD); cmd.Parameters.AddWithValue("@ReturnNo", model.ReturnNo); cmd.CommandText = strSql; } catch (Exception) { } }
public static bool UpdateStatus(StorageReturn sr, string stype) { /*********************************** * stype 1:确认 2:结单 3:取消结单 * *********************************/ StringBuilder sbUpdate = new StringBuilder(); sbUpdate.Append("UPDATE officedba.StorageReturn SET "); if (stype == "1") //确认 { #region 构造更新状态SQL sbUpdate.Append(" BillStatus=@BillStatus ,Confirmor=@Confirmor,ConfirmDate=@ConfirmDate "); sbUpdate.Append(", ModifiedDate=@ModifiedDate,ModifiedUserID=@ModifiedUserID "); sbUpdate.Append(" WHERE ID=@ID"); SqlParameter[] Paras ={ new SqlParameter("@BillStatus",SqlDbType.VarChar), new SqlParameter("@Confirmor",SqlDbType.Int), new SqlParameter("@ConfirmDate",SqlDbType.DateTime), new SqlParameter("@ID",SqlDbType.Int), new SqlParameter("@ModifiedDate",SqlDbType.DateTime), new SqlParameter("@ModifiedUserID",SqlDbType.VarChar) }; Paras[0].Value = sr.BillStatus; Paras[1].Value = sr.Confirmor; Paras[2].Value = sr.ConfirmDate; Paras[3].Value = sr.ID; Paras[4].Value = sr.ModifiedDate; Paras[5].Value = sr.ModifiedUserID; #endregion ArrayList SqlCmdList = new ArrayList(); SqlCommand srSqlCmd = new SqlCommand(); srSqlCmd.CommandText = sbUpdate.ToString(); srSqlCmd.Parameters.AddRange(Paras); SqlCmdList.Add(srSqlCmd); #region 构造 业务逻辑 实现SQL StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT srd.*,(SELECT sr.StorageID FROM officedba.StorageReturn as sr WHERE sr.ID=@ID ) AS StorageID FROM officedba.StorageReturnDetail as srd "); sbSql.Append(" WHERE srd.CompanyCD=@CompanyCD AND srd.ReturnNo=@ReturnNo"); SqlParameter[] sPara = { new SqlParameter("@ID",SqlDbType.Int), new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@ReturnNo",SqlDbType.VarChar), }; sPara[0].Value = sr.ID; sPara[1].Value = sr.CompanyCD; sPara[2].Value = sr.ReturnNo; DataTable dt = SqlHelper.ExecuteSql(sbSql.ToString(), sPara); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { bool isBatchNo = false; if (row["BatchNo"] != null || row["BatchNo"].ToString().ToLower() != "nobatch" || row["BatchNo"].ToString() != "") isBatchNo = true; /*更新被借仓库 更新 现有存量(现有库存不变)+ 借出量- */ StringBuilder SubSql = new StringBuilder(); SubSql.Append("UPDATE officedba.StorageProduct SET OutCount=isnull(OutCount,0)-@ProductCount "); SubSql.Append(" WHERE CompanyCD=@CompanyCD AND StorageID=@StorageID AND ProductID=@ProductID " + (isBatchNo ? "AND BatchNo=@BatchNo" : string.Empty)); SqlParameter[] subSqlParams = isBatchNo ? new SqlParameter[5] : new SqlParameter[4]; int index = 0; subSqlParams[index++] = SqlHelper.GetParameter("@ProductCount", row["ReturnCount"].ToString()); subSqlParams[index++] = SqlHelper.GetParameter("@CompanyCD", row["CompanyCD"].ToString()); subSqlParams[index++] = SqlHelper.GetParameter("@StorageID", row["StorageID"].ToString()); subSqlParams[index++] = SqlHelper.GetParameter("@ProductID", row["ProductID"].ToString()); if (isBatchNo) subSqlParams[index++] = SqlHelper.GetParameter("@BatchNo", row["BatchNo"].ToString()); SqlCommand subSqlCmd = new SqlCommand(); subSqlCmd.CommandText = SubSql.ToString(); subSqlCmd.Parameters.AddRange(subSqlParams); SqlCmdList.Add(subSqlCmd); /*更新借货单明细中的 已返还数量+*/ StringBuilder sbUpdateBorrowDetail = new StringBuilder(); sbUpdateBorrowDetail.Append(" UPDATE officedba.StorageBorrowDetail set RealReturnCount=isnull(RealReturnCount,0)+@ReturnCount "); sbUpdateBorrowDetail.Append(" WHERE CompanyCD=@CompanyCD AND SortNo=@SortNo AND BorrowNo=(SELECT sb.BorrowNo FROM officedba.StorageBorrow as sb WHERE sb.ID=@BorrowID )"); SqlParameter[] updateSqlParas = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@SortNo",SqlDbType.Int), new SqlParameter("@BorrowID",SqlDbType.Int), new SqlParameter("@ReturnCount",SqlDbType.Decimal)}; updateSqlParas[0].Value = row["CompanyCD"].ToString(); updateSqlParas[1].Value = row["FromLineNo"].ToString(); updateSqlParas[2].Value = row["FromBillID"].ToString(); updateSqlParas[3].Value = row["ReturnCount"].ToString(); SqlCmdList.Add(SqlHelper.GetNewSqlCommond(sbUpdateBorrowDetail.ToString(), updateSqlParas)); } bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList); return result; } else return false; #endregion } else if (stype == "2") //结单 { sbUpdate.Append("BillStatus=@BillStatus,Closer=@Closer,CloseDate=@CloseDate "); sbUpdate.Append(", ModifiedDate=@ModifiedDate,ModifiedUserID=@ModifiedUserID "); sbUpdate.Append(" WHERE ID=@ID "); SqlParameter[] Paras = { new SqlParameter("@BillStatus",SqlDbType.VarChar), new SqlParameter("@Closer",SqlDbType.Int), new SqlParameter("@CloseDate",SqlDbType.DateTime), new SqlParameter("@ModifiedDate",SqlDbType.DateTime), new SqlParameter("@ModifiedUserID",SqlDbType.VarChar), new SqlParameter("@ID",SqlDbType.Int) }; Paras[0].Value = sr.BillStatus; Paras[1].Value = sr.Closer; Paras[2].Value = sr.CloseDate; Paras[3].Value = sr.ModifiedDate; Paras[4].Value = sr.ModifiedUserID; Paras[5].Value = sr.ID; SqlCommand sqlcmd = new SqlCommand(); sqlcmd.Parameters.AddRange(Paras); sqlcmd.CommandText = sbUpdate.ToString(); ArrayList sqlcmdlist = new ArrayList(); sqlcmdlist.Add(sqlcmd); return SqlHelper.ExecuteTransWithArrayList(sqlcmdlist); } else if (stype == "3") //取消结单 { sbUpdate.Append("BillStatus=@BillStatus, ModifiedDate=@ModifiedDate,ModifiedUserID=@ModifiedUserID "); sbUpdate.Append(" WHERE ID=@ID"); SqlParameter[] Paras = { new SqlParameter("@BillStatus",SqlDbType.VarChar), new SqlParameter("@ModifiedDate",SqlDbType.DateTime), new SqlParameter("@ModifiedUserID",SqlDbType.VarChar), new SqlParameter("@ID",SqlDbType.Int)}; Paras[0].Value = sr.BillStatus; Paras[1].Value = sr.ModifiedDate; Paras[2].Value = sr.ModifiedUserID; Paras[3].Value = sr.ID; SqlCommand sqlcmd = new SqlCommand(); sqlcmd.Parameters.AddRange(Paras); sqlcmd.CommandText = sbUpdate.ToString(); ArrayList sqlcmdlist = new ArrayList(); sqlcmdlist.Add(sqlcmd); return SqlHelper.ExecuteTransWithArrayList(sqlcmdlist); } else return false; }