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