public static string SetStorageBorrow(StorageBorrow sborrow, List<StorageBorrowDetail> detailsList, Hashtable ht)
{
//定义返回变量
string Result = string.Empty;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//获取当前用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//执行操作
try
{
//执行操作
Result = XBase.Data.Office.StorageManager.StorageBorrowDBHelper.SetBorrowStorage(sborrow, detailsList,ht);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (Result.Split('|')[0] == "1")
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
//操作日志
LogInfoModel logModel = InitLogInfo(sborrow.BorrowNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return Result;
}
protected void imgOutput_Click(object sender, ImageClickEventArgs e)
{
StorageBorrow borrow = new StorageBorrow();
borrow.BorrowNo = string.IsNullOrEmpty(tboxBorrowNo.Value.Trim())?string.Empty:("%"+tboxBorrowNo.Value.Trim()+"%");
borrow.Title = string.IsNullOrEmpty(tboxTitle.Value.Trim()) ? string.Empty : ("%" +tboxTitle.Value.Trim()+ "%");
borrow.Borrower = Convert.ToInt32(string.IsNullOrEmpty(txtBorrower.Value.Trim())?"-1":txtBorrower.Value.Trim());
borrow.DeptID = Convert.ToInt32(string.IsNullOrEmpty(txtBorrowDeptID.Value.Trim())?"-1":txtBorrowDeptID.Value.Trim());
borrow.OutDeptID = Convert.ToInt32(string.IsNullOrEmpty(txtOutDept.Value.Trim())?"-1":txtOutDept.Value.Trim());
borrow.StorageID = Convert.ToInt32(string.IsNullOrEmpty(ddlDepot.SelectedItem.Value)?"-1":ddlDepot.SelectedItem.Value);
borrow.Transactor = Convert.ToInt32(string.IsNullOrEmpty(txtTransactor.Value.Trim())?"-1":txtTransactor.Value.Trim());
borrow.BillStatus = string.IsNullOrEmpty(ddlBillStatus.SelectedItem.Value)?string.Empty:ddlBillStatus.SelectedItem.Value;
borrow.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
DateTime start = Convert.ToDateTime(string.IsNullOrEmpty(tboxBorrowStartTime.Text.Trim()) ? DateTime.MinValue.ToString() : tboxBorrowStartTime.Text.Trim());
DateTime end = Convert.ToDateTime(string.IsNullOrEmpty(tboxBorrowEndTime.Text.Trim()) ? DateTime.MinValue.ToString() : tboxBorrowEndTime.Text.Trim());
int SubmitStatus = Convert.ToInt32(string.IsNullOrEmpty(ddlSubmitStatus.SelectedItem.Value)?"-1":ddlSubmitStatus.SelectedItem.Value);
string orderString =(string.IsNullOrEmpty(txtOrderBy.Value.Trim()) ? string.Empty : txtOrderBy.Value.Trim());//排序
string order = "DESC";//排序：升序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "ID";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_a"))
{
order = "ASC";//排序：降序
}
//扩展属性
string EFIndex = Request.QueryString["EFIndex"];
string EFDesc = Request.QueryString["EFDesc"];
GetBillExAttrControl1.ExtIndex = EFIndex;
GetBillExAttrControl1.ExtValue = EFDesc;
GetBillExAttrControl1.SetExtControlValue();
orderBy = orderBy + " " + order;
DataTable dt = XBase.Business.Office.StorageManager.StorageBorrowBus.GetStorageList(EFIndex,EFDesc,borrow, orderBy, start, end, SubmitStatus);
foreach (DataRow row in dt.Rows)
{
if (row["FlowStatus"] == null || row["FlowStatus"].ToString() == "")
row["FlowStatus"] = "";
else if (row["FlowStatus"].ToString() == "1")
row["FlowStatus"] = "待审批";
else if (row["FlowStatus"].ToString() == "2")
row["FlowStatus"] = "审批中";
else if (row["FlowStatus"].ToString() == "3")
row["FlowStatus"] = "审批通过";
else if (row["FlowStatus"].ToString() == "4")
row["FlowStatus"] = "审批不通过";
else if (row["FlowStatus"].ToString() == "5")
row["FlowStatus"] = "撤销审批";
}
OutputToExecl.ExportToTableFormat(this,dt,
new string[]{"借货单编号","借货单主题","借货部门","借货人","被借部门","被借仓库","借货数量","借货金额","出库人","出库时间","单据状态","审批状态"},
new string[] { "BorrowNo", "Title", "DeptID", "Borrower", "OutDeptID", "StorageID", "CountTotal", "TotalPrice", "Transactor", "OutDate", "BillStatus", "FlowStatus" },
"借货单列表");
}
public static bool CancelConfirm(StorageBorrow borrow)
{
List<SqlCommand> SqlList = new List<SqlCommand>();
#region 重置单据为初始状态
StringBuilder strSql = new StringBuilder();
strSql.Append("update officedba.StorageBorrow set ");
strSql.Append("BillStatus=@BillStatus,");
strSql.Append("ModifiedDate=@ModifiedDate,");
strSql.Append("ModifiedUserID=@ModifiedUserID ");
strSql.Append(" where ID=@ID ");
SqlParameter[] parameters = {
new SqlParameter("@ID", SqlDbType.Int),
new SqlParameter("@BillStatus", SqlDbType.Char, 1),
new SqlParameter("@ModifiedDate", SqlDbType.DateTime),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar, 50),
};
parameters[0].Value = borrow.ID;
parameters[1].Value = borrow.BillStatus;
parameters[2].Value = borrow.ModifiedDate;
parameters[3].Value = borrow.ModifiedUserID;
#endregion
SqlList.Add(SqlHelper.GetNewSqlCommond(strSql.ToString(), parameters));
#region 查询单据明细
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT * FROM officedba.StorageBorrowDetail ");
sbQuery.Append(" WHERE CompanyCD=@CompanyCD AND BorrowNo=@BorrowNo ");
SqlParameter[] Para ={
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@BorrowNo",SqlDbType.VarChar)
};
Para[0].Value = borrow.CompanyCD;
Para[1].Value = borrow.BorrowNo;
DataTable dtDetail = SqlHelper.ExecuteSql(sbQuery.ToString(), Para);
#endregion
#region 回写库存数据
foreach (DataRow row in dtDetail.Rows)
{
#region 构造库存移动SQL字符串
bool isBatch = false;
if (row["BatchNo"] != null || row["BatchNo"].ToString().ToLower() != "nobatch" || row["BatchNo"].ToString() != "")
isBatch = true;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("UPDATE officedba.StorageProduct SET ");
sbSql.Append(" OutCount=OutCount-@BorrowCount");
//借出量（减少）
sbSql.Append(" WHERE CompanyCD=@CompanyCD AND ProductID=@ProductID AND StorageID=@StorageID " + (isBatch ? " AND BatchNo=@BatchNo " : string.Empty));
SqlParameter[] sqlParams = isBatch ? new SqlParameter[5] : new SqlParameter[4];
int index = 0;
sqlParams[index++] = SqlHelper.GetParameter("@BorrowCount", row["ProductCount"].ToString());
sqlParams[index++] = SqlHelper.GetParameter("@CompanyCD", row["CompanyCD"].ToString());
sqlParams[index++] = SqlHelper.GetParameter("@ProductID", row["ProductID"].ToString());
sqlParams[index++] = SqlHelper.GetParameter("@StorageID", borrow.StorageID);
if (isBatch)
sqlParams[index++] = SqlHelper.GetParameter("@BatchNo", row["BatchNo"].ToString());
SqlList.Add(SqlHelper.GetNewSqlCommond(sbSql.ToString(), sqlParams));
#endregion
}
#endregion
IList<SqlCommand> tempList = Data.Common.FlowDBHelper.GetCancelConfirmSqlCommond(borrow.CompanyCD, Convert.ToInt32(ConstUtil.BILL_TYPEFLAG_STORAGE), Convert.ToInt32(ConstUtil.BILL_TYPECODE_STORAGE_BORROW), borrow.ID, borrow.ModifiedUserID);
foreach (SqlCommand scmd in tempList)
{
SqlList.Add(scmd);
}
return SqlHelper.ExecuteTransWithCollections(SqlList);
}
/*不分页*/
public static DataTable GetStorageList(string EFIndex, string EFDesc, StorageBorrow borrow, string OrderBy, DateTime StartTime, DateTime EndTime, int SubmitStatus)
{
StringBuilder sbSql = new StringBuilder();
string Submit = string.Empty;
if (SubmitStatus > 0)
Submit = " AND FlowStatus=" + SubmitStatus.ToString();
else if (SubmitStatus == 0)
Submit = " AND FlowStatus is null ";
sbSql.Append(" SELECT * FROM ");
sbSql.Append(" (SELECT S.ID, S.CompanyCD, S.BorrowNo, S.Title, (select e.EmployeeName from officedba.EmployeeInfo as e where e.ID=S.Borrower ) as Borrower, (SELECT D.DeptName FROM officedba.DeptInfo as d where d.ID=S.DeptID) AS DeptID , S.BorrowDate, S.ReasonType, (SELECT OD.DeptName FROM officedba.DeptInfo as OD where OD.ID=S.OutDeptID) AS OutDeptID, (select si.StorageName from officedba.StorageInfo as si where si.ID= S.StorageID) AS StorageID, S.OutDate, (select te.EmployeeName from officedba.EmployeeInfo as te where te.ID=S.Transactor ) AS Transactor,S.Creator, S.CreateDate,(CASE S.BillStatus WHEN '1' THEN '制单' when '2' THEN '执行' when '3' THEN '变更' when '4' THEN '手工结单' when '5' then '自动结单' end) as BillStatus,( select top 1 fi.FlowStatus from officedba.FlowInstance AS FI where S.ID=FI.BillID AND fi.CompanyCD=S.CompanyCD AND fi.BillTypeCode=" + XBase.Common.ConstUtil.BILL_TYPECODE_STORAGE_BORROW.ToString() + " AND fi.BillTypeFlag=" + XBase.Common.ConstUtil.BILL_TYPEFLAG_STORAGE.ToString() + " order by FI.ModifiedDate DESC) as FlowStatus,S.Confirmor, S.ConfirmDate, S.Closer, S.CloseDate, S.ModifiedDate, S.ModifiedUserID,S.TotalPrice,S.CountTotal FROM officedba.StorageBorrow AS S WHERE s.CompanyCD=@CompanyCD ");
Hashtable htPara = new Hashtable();
if (!string.IsNullOrEmpty(borrow.BorrowNo.Trim()) && borrow.BorrowNo.Trim() != "")
htPara.Add("BorrowNo", borrow.BorrowNo);
if (!string.IsNullOrEmpty(borrow.Title.Trim()) && borrow.Title != "")
htPara.Add("Title", borrow.Title);
if (borrow.Borrower > 0)
htPara.Add("Borrower", borrow.Borrower);
if (borrow.DeptID > 0)
htPara.Add("DeptID", borrow.DeptID);
if (borrow.OutDeptID > 0)
htPara.Add("OutDeptID", borrow.OutDeptID);
if (borrow.StorageID > 0)
htPara.Add("StorageID", borrow.StorageID);
if (borrow.Transactor > 0)
htPara.Add("Transactor", borrow.Transactor);
if (StartTime > DateTime.MinValue)
htPara.Add("StartTime", StartTime);
if (EndTime > DateTime.MinValue)
htPara.Add("EndTime", EndTime);
if (!string.IsNullOrEmpty(borrow.BillStatus) && borrow.BillStatus.Trim() != "" && borrow.BillStatus != "-1")
htPara.Add("BillStatus", borrow.BillStatus);
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
htPara.Add("ExtField", EFDesc);
//sql += " and s.ExtField" + EFIndex + " like @EFDesc ";
//comm.Parameters.Add(SqlHelper.GetParameter("@EFDesc", "%" + EFDesc + "%"));
}
htPara.Add("CompanyCD", borrow.CompanyCD);
SqlParameter[] Paras = new SqlParameter[htPara.Count];
int index = 0;
foreach (string key in htPara.Keys)
{
switch (key)
{
case "BorrowNo":
sbSql.Append(" AND S.BorrowNo like @" + key);
break;
case "Title":
sbSql.Append(" AND S.Title like @" + key);
break;
case "Borrower":
sbSql.Append(" AND S.Borrower =@" + key);
break;
case "DeptID":
sbSql.Append(" AND S.DeptID=@" + key);
break;
case "OutDeptID":
sbSql.Append(" AND S.OutDeptID=@" + key);
break;
case "StorageID":
sbSql.Append(" AND S.StorageID=@" + key);
break;
case "Transactor":
sbSql.Append(" AND S.Transactor=@" + key);
break;
case "StartTime":
sbSql.Append(" AND S.BorrowDate>=@" + key);
break;
case "EndTime":
sbSql.Append(" AND S.BorrowDate<=@" + key);
break;
case "BillStatus":
sbSql.Append(" AND S.BillStatus=@" + key);
break;
case "ExtField":
sbSql.Append(" AND s.ExtField" + EFIndex + " like @" + EFDesc);
break;
case "CompanyCD":
break;
}
Paras[index] = new SqlParameter("@" + key, SqlDbType.VarChar);
Paras[index].Value = htPara[key];
index++;
}
sbSql.Append(" ) AS temp");
sbSql.Append(" WHERE 1=1 " + Submit + "ORDER BY " + OrderBy);
return SqlHelper.ExecuteSql(sbSql.ToString(), Paras);
//return SqlHelper.ExecuteSql(sbSql.ToString(),Paras);
}
public static string GetBillStatus(StorageBorrow borrow)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT BillStatus FROM officedba.StorageBorrow ");
strSql.Append(" WHERE ID=@ID");
SqlParameter[] Para = { new SqlParameter("@ID", SqlDbType.Int) };
Para[0].Value = borrow.ID;
DataTable dt = SqlHelper.ExecuteSql(strSql.ToString(), Para);
if (dt != null && dt.Rows.Count > 0)
return dt.Rows[0]["BillStatus"].ToString();
return string.Empty;
}
public static void SetStorageInfo(StorageBorrow borrow)
{
#region 构造字符串查询明细表
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM officedba.StorageBorrowDetail ");
strSql.Append(" WHERE CompanyCD=@CompanyCD AND BorrowNo=@BorrowNo ");
SqlParameter[] Para ={
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@BorrowNo",SqlDbType.VarChar)
};
Para[0].Value = borrow.CompanyCD;
Para[1].Value = borrow.BorrowNo;
DataTable dt = SqlHelper.ExecuteSql(strSql.ToString(), Para);
List<SqlCommand> cmdList = new List<SqlCommand>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
#region 构造库存移动SQL字符串
StringBuilder sbSql = new StringBuilder();
//验证 明细中是否存入批次
bool IsBatch = false;
if (row["BatchNo"] != null && row["BatchNo"].ToString() != string.Empty && row["BatchNo"].ToString().ToLower() != "nobatch")
IsBatch = true;
//构造查询字符串
sbSql.Append("UPDATE officedba.StorageProduct SET ");
sbSql.Append(" OutCount=isnull(OutCount,0)+@BorrowCount");
//借出量（增加）
sbSql.Append(" WHERE CompanyCD=@CompanyCD AND ProductID=@ProductID AND StorageID=@StorageID " + (IsBatch ? "AND BatchNo=@BatchNo" : string.Empty));
SqlParameter[] sqlParams = null;
sqlParams = IsBatch ? new SqlParameter[5] : new SqlParameter[4];
int index = 0;
sqlParams[index++] = SqlHelper.GetParameter("@BorrowCount", row["ProductCount"].ToString());
sqlParams[index++] = SqlHelper.GetParameter("@CompanyCD", row["CompanyCD"].ToString());
sqlParams[index++] = SqlHelper.GetParameter("@ProductID", row["ProductID"].ToString());
sqlParams[index++] = SqlHelper.GetParameter("@StorageID", borrow.StorageID);
if (IsBatch)
sqlParams[index++] = SqlHelper.GetParameter("@BatchNo", row["BatchNo"].ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sbSql.ToString();
cmd.Parameters.AddRange(sqlParams);
cmdList.Add(cmd);
#endregion
}
SqlHelper.ExecuteTransWithCollections(cmdList);
}
#endregion
}
public static string UpdateStorageBorrow(StorageBorrow sborrow, List<StorageBorrowDetail> borrowlist, Hashtable ht)
{
List<SqlCommand> SqlList = new List<SqlCommand>();
#region 构造SQL字符串
StringBuilder strSql = new StringBuilder();
strSql.Append("update officedba.StorageBorrow set ");
strSql.Append("CompanyCD=@CompanyCD,");
strSql.Append("BorrowNo=@BorrowNo,");
strSql.Append("Title=@Title,");
strSql.Append("Borrower=@Borrower,");
strSql.Append("DeptID=@DeptID,");
strSql.Append("BorrowDate=@BorrowDate,");
strSql.Append("ReasonType=@ReasonType,");
strSql.Append("OutDeptID=@OutDeptID,");
strSql.Append("StorageID=@StorageID,");
strSql.Append("OutDate=@OutDate,");
strSql.Append("Transactor=@Transactor,");
strSql.Append("TotalPrice=@TotalPrice,");
strSql.Append("CountTotal=@CountTotal,");
strSql.Append("Summary=@Summary,");
strSql.Append("Remark=@Remark,");
strSql.Append("BillStatus=@BillStatus ");
strSql.Append(" where ID=@ID ");
strSql.Append(" ; select @ID ,@@ROWCOUNT as result");
SqlParameter[] parameters = {
new SqlParameter("@ID", SqlDbType.Int,4),
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@BorrowNo", SqlDbType.VarChar,50),
new SqlParameter("@Title", SqlDbType.VarChar,100),
new SqlParameter("@Borrower", SqlDbType.Int,4),
new SqlParameter("@DeptID", SqlDbType.Int,4),
new SqlParameter("@BorrowDate", SqlDbType.DateTime),
new SqlParameter("@ReasonType", SqlDbType.Int,4),
new SqlParameter("@OutDeptID", SqlDbType.Int,4),
new SqlParameter("@StorageID", SqlDbType.Int,4),
new SqlParameter("@OutDate", SqlDbType.DateTime),
new SqlParameter("@Transactor", SqlDbType.Int,4),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@CountTotal", SqlDbType.Decimal,9),
new SqlParameter("@Summary", SqlDbType.VarChar,200),
new SqlParameter("@Remark", SqlDbType.VarChar,800),
new SqlParameter("@BillStatus", SqlDbType.Char,1),
};
parameters[0].Value = sborrow.ID;
parameters[1].Value = sborrow.CompanyCD;
parameters[2].Value = sborrow.BorrowNo;
parameters[3].Value = sborrow.Title;
parameters[4].Value = sborrow.Borrower;
parameters[5].Value = sborrow.DeptID;
parameters[6].Value = sborrow.BorrowDate;
parameters[7].Value = sborrow.ReasonType;
parameters[8].Value = sborrow.OutDeptID;
parameters[9].Value = sborrow.StorageID;
parameters[10].Value = sborrow.OutDate;
parameters[11].Value = sborrow.Transactor;
parameters[12].Value = sborrow.TotalPrice;
parameters[13].Value = sborrow.CountTotal;
parameters[14].Value = sborrow.Summary;
parameters[15].Value = sborrow.Remark;
parameters[16].Value = sborrow.BillStatus;
#endregion
SqlList.Add(SqlHelper.GetNewSqlCommond(strSql.ToString(), parameters));
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(sborrow, ht, cmd);
if (ht.Count > 0)
SqlList.Add(cmd);
#endregion
#region 删除明细
StringBuilder strDelSql = new StringBuilder();
strDelSql.Append("delete officedba.StorageBorrowDetail ");
strDelSql.Append(" where CompanyCD=@CompanyCD AND BorrowNo=@BorrowNo ");
SqlParameter[] delparameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar),
new SqlParameter("@BorrowNo",SqlDbType.VarChar)
};
delparameters[0].Value = sborrow.CompanyCD;
delparameters[1].Value = sborrow.BorrowNo;
#endregion
SqlList.Add(SqlHelper.GetNewSqlCommond(strDelSql.ToString(), delparameters));
#region 构造明细字符串
foreach (StorageBorrowDetail borrow in borrowlist)
{
StringBuilder strSubSql = new StringBuilder();
strSubSql.Append("insert into officedba.StorageBorrowDetail (");
strSubSql.Append("CompanyCD,BorrowNo,SortNo,ProductID,UnitID,UnitPrice,ProductCount,TotalPrice,ReturnDate,ReturnCount,Remark,ModifiedDate,ModifiedUserID,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)");
strSubSql.Append(" values (");
strSubSql.Append("@CompanyCD,@BorrowNo,@SortNo,@ProductID,@UnitID,@UnitPrice,@ProductCount,@TotalPrice,@ReturnDate,@ReturnCount,@Remark,@ModifiedDate,@ModifiedUserID,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)");
strSubSql.Append(" select @@IDENTITY");
SqlParameter[] subparameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@BorrowNo", SqlDbType.VarChar,50),
new SqlParameter("@SortNo", SqlDbType.Int,4),
new SqlParameter("@ProductID", SqlDbType.VarChar,20),
new SqlParameter("@UnitID", SqlDbType.Int,4),
new SqlParameter("@UnitPrice", SqlDbType.Decimal,9),
new SqlParameter("@ProductCount", SqlDbType.Decimal,9),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@ReturnDate", SqlDbType.DateTime),
new SqlParameter("@ReturnCount", SqlDbType.Decimal,9),
new SqlParameter("@Remark", SqlDbType.VarChar,200),
new SqlParameter("@ModifiedDate", SqlDbType.DateTime),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,50),
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("@BatchNo",SqlDbType.VarChar,50)};
subparameters[0].Value = borrow.CompanyCD;
subparameters[1].Value = borrow.BorrowNo;
subparameters[2].Value = borrow.SortNo;
subparameters[3].Value = borrow.ProductID;
subparameters[4].Value = borrow.UnitID;
subparameters[5].Value = borrow.UnitPrice;
subparameters[6].Value = borrow.ProductCount;
subparameters[7].Value = borrow.TotalPrice;
subparameters[8].Value = borrow.ReturnDate;
subparameters[9].Value = borrow.ReturnCount;
subparameters[10].Value = borrow.Remark;
subparameters[11].Value = borrow.ModifiedDate;
subparameters[12].Value = borrow.ModifiedUserID;
subparameters[13].Value = borrow.UsedUnitID;
subparameters[14].Value = borrow.UsedUnitCount;
subparameters[15].Value = borrow.UsedPrice;
subparameters[16].Value = borrow.ExRate;
subparameters[17].Value = borrow.BatchNo;
SqlList.Add(SqlHelper.GetNewSqlCommond(strSubSql.ToString(), subparameters));
}
#endregion
bool res = SqlHelper.ExecuteTransWithCollections(SqlList);
if (res)
return "1|";
else
return "0|";
}
public static string SetBorrowStorage(StorageBorrow sborrow, List<StorageBorrowDetail> borrowlist, Hashtable ht)
{
List<SqlCommand> SqlList = new List<SqlCommand>();
#region 构造查询字符串
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.StorageBorrow (");
strSql.Append("CompanyCD,BorrowNo,Title,Borrower,DeptID,BorrowDate,ReasonType,OutDeptID,StorageID,OutDate,Transactor,TotalPrice,CountTotal,Summary,Remark,Creator,CreateDate,BillStatus,ModifiedDate,ModifiedUserID)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@BorrowNo,@Title,@Borrower,@DeptID,@BorrowDate,@ReasonType,@OutDeptID,@StorageID,@OutDate,@Transactor,@TotalPrice,@CountTotal,@Summary,@Remark,@Creator,@CreateDate,@BillStatus,@ModifiedDate,@ModifiedUserID)");
strSql.Append(";select @ID=@@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@BorrowNo", SqlDbType.VarChar,50),
new SqlParameter("@Title", SqlDbType.VarChar,100),
new SqlParameter("@Borrower", SqlDbType.Int,4),
new SqlParameter("@DeptID", SqlDbType.Int,4),
new SqlParameter("@BorrowDate", SqlDbType.DateTime),
new SqlParameter("@ReasonType", SqlDbType.Int,4),
new SqlParameter("@OutDeptID", SqlDbType.Int,4),
new SqlParameter("@StorageID", SqlDbType.Int,4),
new SqlParameter("@OutDate", SqlDbType.DateTime),
new SqlParameter("@Transactor", SqlDbType.Int,4),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@CountTotal", SqlDbType.Decimal,9),
new SqlParameter("@Summary", SqlDbType.VarChar,200),
new SqlParameter("@Remark", SqlDbType.VarChar,800),
new SqlParameter("@Creator", SqlDbType.Int,4),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@BillStatus", SqlDbType.Char,1),
new SqlParameter("@ModifiedDate", SqlDbType.DateTime),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,50),
new SqlParameter("@ID",SqlDbType.Int)};
parameters[0].Value = sborrow.CompanyCD;
parameters[1].Value = sborrow.BorrowNo;
parameters[2].Value = sborrow.Title;
parameters[3].Value = sborrow.Borrower;
parameters[4].Value = sborrow.DeptID;
parameters[5].Value = sborrow.BorrowDate;
parameters[6].Value = sborrow.ReasonType;
parameters[7].Value = sborrow.OutDeptID;
parameters[8].Value = sborrow.StorageID;
parameters[9].Value = sborrow.OutDate;
parameters[10].Value = sborrow.Transactor;
parameters[11].Value = sborrow.TotalPrice;
parameters[12].Value = sborrow.CountTotal;
parameters[13].Value = sborrow.Summary;
parameters[14].Value = sborrow.Remark;
parameters[15].Value = sborrow.Creator;
parameters[16].Value = sborrow.CreateDate;
parameters[17].Value = sborrow.BillStatus;
parameters[18].Value = sborrow.ModifiedDate;
parameters[19].Value = sborrow.ModifiedUserID;
parameters[20].Direction = ParameterDirection.Output;
#endregion
SqlList.Add(SqlHelper.GetNewSqlCommond(strSql.ToString(), parameters));
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(sborrow, ht, cmd);
if (ht.Count > 0)
SqlList.Add(cmd);
#endregion
#region 构造明细字符串
foreach (StorageBorrowDetail borrow in borrowlist)
{
#region 构造SQL字符
StringBuilder strSubSql = new StringBuilder();
strSubSql.Append("insert into officedba.StorageBorrowDetail (");
strSubSql.Append("CompanyCD,BorrowNo,SortNo,ProductID,UnitID,UnitPrice,ProductCount,TotalPrice,ReturnDate,ReturnCount,Remark,ModifiedDate,ModifiedUserID,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)");
strSubSql.Append(" values (");
strSubSql.Append("@CompanyCD,@BorrowNo,@SortNo,@ProductID,@UnitID,@UnitPrice,@ProductCount,@TotalPrice,@ReturnDate,@ReturnCount,@Remark,@ModifiedDate,@ModifiedUserID,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)");
strSubSql.Append(" select @@IDENTITY");
SqlParameter[] sqlParams = new SqlParameter[18];
int index = 0;
sqlParams[index++] = SqlHelper.GetParameter("@CompanyCD", borrow.CompanyCD);
sqlParams[index++] = SqlHelper.GetParameter("@BorrowNo", borrow.BorrowNo);
sqlParams[index++] = SqlHelper.GetParameter("@SortNo", borrow.SortNo);
sqlParams[index++] = SqlHelper.GetParameter("@ProductID", borrow.ProductID);
sqlParams[index++] = SqlHelper.GetParameter("@UnitID", borrow.UnitID);
sqlParams[index++] = SqlHelper.GetParameter("@UnitPrice", borrow.UnitPrice);
sqlParams[index++] = SqlHelper.GetParameter("@ProductCount", borrow.ProductCount);
sqlParams[index++] = SqlHelper.GetParameter("@TotalPrice", borrow.TotalPrice);
sqlParams[index++] = SqlHelper.GetParameter("@ReturnDate", borrow.ReturnDate);
sqlParams[index++] = SqlHelper.GetParameter("@ReturnCount", borrow.ReturnCount);
sqlParams[index++] = SqlHelper.GetParameter("@Remark", borrow.Remark);
sqlParams[index++] = SqlHelper.GetParameter("@ModifiedDate", borrow.ModifiedDate);
sqlParams[index++] = SqlHelper.GetParameter("@ModifiedUserID", borrow.ModifiedUserID);
sqlParams[index++] = SqlHelper.GetParameter("@UsedUnitID", borrow.UsedUnitID);
sqlParams[index++] = SqlHelper.GetParameter("@UsedUnitCount", borrow.UsedUnitCount);
sqlParams[index++] = SqlHelper.GetParameter("@UsedPrice", borrow.UsedPrice);
sqlParams[index++] = SqlHelper.GetParameter("@ExRate", borrow.ExRate);
sqlParams[index++] = SqlHelper.GetParameter("@BatchNo", borrow.BatchNo);
SqlList.Add(SqlHelper.GetNewSqlCommond(strSubSql.ToString(), sqlParams));
#endregion
}
#endregion
bool res = SqlHelper.ExecuteTransWithCollections(SqlList);
if (res)
return "1|" + SqlList[0].Parameters["@ID"].Value.ToString();
else
return "0|";
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(StorageBorrow model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.StorageBorrow 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 BorrowNo = @BorrowNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@BorrowNo", model.BorrowNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
public static bool CancelConfirm(StorageBorrow borrow)
{
//定义返回变量
bool res = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//获取当前用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//执行操作
try
{
//执行操作
res = XBase.Data.Office.StorageManager.StorageBorrowDBHelper.CancelConfirm(borrow);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (res)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
//操作日志
LogInfoModel logModel = InitLogInfo(borrow.BorrowNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_UNCONFIRM;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return res;
}
/*不分页*/
public static DataTable GetStorageList(string EFIndex, string EFDesc, StorageBorrow borrow, string OrderBy, DateTime StartTime, DateTime EndTime, int SubmitStatus)
{
return XBase.Data.Office.StorageManager.StorageBorrowDBHelper.GetStorageList( EFIndex, EFDesc,borrow,OrderBy,StartTime, EndTime, SubmitStatus);
}
public static DataTable GetStorageList(string EFIndex, string EFDesc,int PageIndex, int PageSzie, string OrderBy, ref int TotalCount, StorageBorrow borrow, DateTime StartTime, DateTime EndTime, int SubmitStatus)
{
return XBase.Data.Office.StorageManager.StorageBorrowDBHelper.GetStorageList(EFIndex, EFDesc,PageIndex, PageSzie, OrderBy, ref TotalCount, borrow, StartTime, EndTime, SubmitStatus);
}