public static string StorageCheckUpdate(StorageCheck sc,Hashtable ht, List<StorageCheckDetail> scdList) { //return "1|"; //定义返回变量 string res = string.Empty; /* * 定义日志内容变量 * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库 * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件 */ //获取当前用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //执行操作 try { //执行操作 res = XBase.Data.Office.StorageManager.StorageCheckDBHelper.StorageCheckUpdate(sc,ht, scdList); } 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(sc.CheckNo); //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空 logModel.Element = ConstUtil.LOG_PROCESS_INSERT; ; //设置操作成功标识 logModel.Remark = remark; //登陆日志 LogDBHelper.InsertLog(logModel); return res; }
/*打印SQL*/ public static DataTable StorageCheckInfoPrint(StorageCheck sc) { return XBase.Data.Office.StorageManager.StorageCheckDBHelper.StorageCheckInfoPrint(sc); }
/*打印SQL*/ public static DataTable GetStorageCheckDetailPrint(StorageCheck sc) { return XBase.Data.Office.StorageManager.StorageCheckDBHelper.GetStorageCheckDetailPrint(sc); }
/*打印SQL*/ public static DataTable GetStorageCheckDetailPrint(StorageCheck sc) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine(" SELECT scd.SortNo, scd.ProductID, scd.UnitID, scd.NowCount, scd.CheckCount, scd.DiffCount, "); strSql.AppendLine(" (CASE scd.DiffType WHEN '0' THEN '正常' WHEN '1' THEN '盘盈' WHEN '2' THEN '盘亏' END) AS DiffTypeText, "); strSql.AppendLine(" (SELECT CodeName "); strSql.AppendLine(" FROM officedba.CodeUnitType AS u "); strSql.AppendLine(" WHERE (scd.UnitID = ID)) AS UnitName, scd.DiffType, scd.Remark, scd.ModifiedDate, scd.ModifiedUserID, p.ProdNo, p.ProductName, "); strSql.AppendLine(" p.Specification, p.StandardCost "); strSql.AppendLine(" FROM officedba.StorageCheckDetail AS scd LEFT OUTER JOIN "); strSql.AppendLine(" officedba.ProductInfo AS p ON scd.ProductID = p.ID "); strSql.AppendLine("WHERE scd.CompanyCD=@CompanyCD AND scd.CheckNo=@CheckNo"); SqlParameter[] Paras = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@CheckNo",SqlDbType.VarChar) }; Paras[0].Value = sc.CompanyCD; Paras[1].Value = sc.CheckNo; return SqlHelper.ExecuteSql(strSql.ToString(), Paras); }
protected void LoadPrintInfo() { PrintParameterSettingModel model = new PrintParameterSettingModel(); model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_STORAGE); model.PrintTypeFlag = ConstUtil.PRINTBILL_TYPEFLAG_STORAGECHECK; StorageCheck StorageCheckM_ = new StorageCheck(); StorageCheckM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; StorageCheckM_.ID = this.intMrpID; StorageCheckM_.CheckNo = hidno.Value.ToString(); /*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/ string[,] aDetail; string[,] aBase = { { "{ExtField1}", "ExtField1"}, { "{ExtField2}", "ExtField2"}, { "{ExtField3}", "ExtField3"}, { "{ExtField4}", "ExtField4"}, { "{ExtField5}", "ExtField5"}, { "{ExtField6}", "ExtField6"}, { "{ExtField7}", "ExtField7"}, { "{ExtField8}", "ExtField8"}, { "{ExtField9}", "ExtField9"}, { "{ExtField10}", "ExtField10"}, { "盘点单编号", "CheckNo"}, { "盘点单主题", "Title"}, { "盘点仓库", "StorageName" }, { "盘点类型", "CheckTypeName" }, { "盘点部门", "DeptName"}, { "盘点开始日期", "CheckStartDate"}, { "盘点结束日期", "CheckEndDate"}, { "库存调整人", "CheckUserName"}, { "经办人", "TransactorName"}, { "库存调整日期", "CheckDate"}, { "现有存量合计", "NowCount"}, { "实盘量合计", "CheckCount"}, { "差异量合计", "DiffCount"}, { "现存金额", "NowMoney"}, { "实盘金额", "CheckMoney"}, { "差异金额", "DiffMoney"}, { "制单人", "CreatorName"}, { "制单日期", "CreateDate"}, { "确认人", "ConfirmorName"}, { "确认日期", "ConfirmDate"}, { "结单人", "CloserName"}, { "结单日期", "CloseDate"}, { "最后更新人", "ModifiedUserID"}, { "最后更新日期", "ModifiedDate"}, { "单据状态", "BillStatusName"}, { "备注", "Remark"}, { "摘要", "Summary"}, }; if (HiddenMoreUnit.Value == "True") { aDetail = new string[,]{ { "序号", "SortNo"}, { "物品编号", "ProdNo"}, { "物品名称", "ProductName" }, { "批次", "BatchNo" }, { "规格", "Specification" }, { "基本单位", "UnitName" }, { "基本数量", "CheckCount" }, { "单位", "UsedUnitName"}, { "成本单价", "UsedPrice"}, { "现存数量", "NowCount"}, { "实盘数量", "UsedUnitCount"}, { "盈亏类型", "DiffTypeName"}, { "差异量", "DiffCount"}, { "备注", "Remark"}, }; } else { aDetail = new string[,]{ { "序号", "SortNo"}, { "物品编号", "ProdNo"}, { "物品名称", "ProductName" }, { "批次", "BatchNo" }, { "规格", "Specification" }, { "单位", "UnitName"}, { "成本单价", "StandardCost"}, { "现存数量", "NowCount"}, { "实盘数量", "CheckCount"}, { "盈亏类型", "DiffTypeName"}, { "差异量", "DiffCount"}, { "备注", "Remark"}, }; } #region 1.扩展属性 int countExt = 0; DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageCheck"); if (dtExtTable.Rows.Count > 0) { for (int i = 0; i < dtExtTable.Rows.Count; i++) { for (int x = 0; x < (aBase.Length / 2) - 15; x++) { if (x == i) { aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString(); countExt++; } } } } #endregion DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model); DataTable dtMain = StorageCheckBus.StorageCheckGet(StorageCheckM_); DataTable dtDetail = StorageCheckBus.GetStorageCheckDetail(StorageCheckM_); string strBaseFields = ""; string strDetailFields = ""; if (dbPrint.Rows.Count > 0) { isSeted.Value = "1"; strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString(); strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString(); } else { #region 未设置过打印模板设置 默认显示所有的 isSeted.Value = "0"; /*未设置过打印模板设置时,默认显示的字段 基本信息字段*/ for (int m = 10; m < aBase.Length / 2; m++) { strBaseFields = strBaseFields + aBase[m, 1] + "|"; } /*未设置过打印模板设置时,默认显示的字段 基本信息字段+扩展信息字段*/ if (countExt > 0) { for (int i = 0; i < countExt; i++) { strBaseFields = strBaseFields + "ExtField" + (i + 1) + "|"; } } /*未设置过打印模板设置时,默认显示的字段 明细信息字段*/ for (int n = 0; n < aDetail.Length / 2; n++) { strDetailFields = strDetailFields + aDetail[n, 1] + "|"; } #endregion } #region 主表信息 if (!string.IsNullOrEmpty(strBaseFields)) { tableBase.InnerHtml = WritePrintPageTable("期末盘点单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMain, dtDetail, true); } #endregion #region 明细信息 if (!string.IsNullOrEmpty(strDetailFields)) { tableDetail.InnerHtml = WritePrintPageTable("期末盘点单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMain, dtDetail, false); } #endregion }
public static string StorageCheck(StorageCheck sc) { #region 读取盘点单信息 StringBuilder strMainSql = new StringBuilder(); strMainSql.Append("SELECT * FROM officedba.StorageCheck WHERE ID=@ID"); SqlParameter[] MainParas = { new SqlParameter("@ID", SqlDbType.Int) }; MainParas[0].Value = sc.ID; DataTable dtStorage = SqlHelper.ExecuteSql(strMainSql.ToString(), MainParas); DataRow MainRow = dtStorage.Rows[0]; #endregion #region 读取明细 StringBuilder strSql = new StringBuilder(); strSql.Append("select a.*,ISNULL(b.StandardCost,0)StandardCost from officedba.StorageCheckDetail a "); strSql.Append("left outer join officedba.productinfo b "); strSql.Append("on a.ProductID=b.id where a.CompanyCD=@CompanyCD and a.CheckNo=@CheckNo "); SqlParameter[] Paras = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@CheckNo",SqlDbType.VarChar) }; Paras[0].Value = sc.CompanyCD; Paras[1].Value = MainRow["CheckNo"]; DataTable dtDetail = SqlHelper.ExecuteSql(strSql.ToString(), Paras); #endregion ArrayList SqlCmdList = new ArrayList(); #region 更新盘点单 库存调整人及调整时间 StringBuilder SqlMain = new StringBuilder(); SqlMain.Append("UPDATE officedba.StorageCheck SET CheckUserID=@CheckUserID,CheckDate=@CheckDate"); SqlMain.Append(" WHERE ID=@ID "); SqlParameter[] sPara = { new SqlParameter("@CheckUserID",SqlDbType.Int), new SqlParameter("@CheckDate",SqlDbType.DateTime), new SqlParameter("@ID",SqlDbType.Int) }; sPara[0].Value = sc.CheckUserID; sPara[1].Value = sc.CloseDate; sPara[2].Value = sc.ID; SqlCommand sqlcmd = new SqlCommand(); sqlcmd.CommandText = SqlMain.ToString(); sqlcmd.Parameters.AddRange(sPara); SqlCmdList.Add(sqlcmd); #endregion foreach (DataRow row in dtDetail.Rows) { StringBuilder strSubSql = new StringBuilder(); strSubSql.Append("UPDATE officedba.StorageProduct SET"); strSubSql.Append(" ProductCount=@ProductCount "); strSubSql.Append(" WHERE CompanyCD=@CompanyCD AND "); strSubSql.Append(" StorageID=@StorageID AND "); strSubSql.Append(" ProductID=@ProductID"); if (row["BatchNo"].ToString() != "") strSubSql.Append(" and BatchNo='" + row["BatchNo"].ToString().Trim() + "' "); else strSubSql.Append(" and BatchNo is null "); SqlParameter[] SubParas = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@StorageID",SqlDbType.Int), new SqlParameter("@ProductID",SqlDbType.Int), new SqlParameter("@ProductCount",SqlDbType.Decimal) }; SubParas[0].Value = row["CompanyCD"]; SubParas[1].Value = MainRow["StorageID"]; SubParas[2].Value = row["ProductID"]; SubParas[3].Value = row["CheckCount"]; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.CommandText = strSubSql.ToString(); sqlCmd.Parameters.AddRange(SubParas); SqlCmdList.Add(sqlCmd); #region 操作库存流水账 StorageAccountModel AccountM_ = new StorageAccountModel(); AccountM_.BatchNo = row["BatchNo"].ToString(); AccountM_.BillNo = row["CheckNo"].ToString(); AccountM_.BillType = 15; AccountM_.CompanyCD = row["CompanyCD"].ToString(); AccountM_.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; AccountM_.HappenCount = Convert.ToDecimal(row["CheckCount"].ToString()); AccountM_.HappenDate = System.DateTime.Now; AccountM_.PageUrl = "../Office/StorageManager/StorageCheckSave.aspx"; AccountM_.Price = Convert.ToDecimal(row["StandardCost"].ToString()); AccountM_.ProductCount = Convert.ToDecimal(row["CheckCount"].ToString()); AccountM_.ProductID = Convert.ToInt32(row["ProductID"].ToString()); AccountM_.StorageID = Convert.ToInt32(dtStorage.Rows[0]["StorageID"].ToString()); #region sql StringBuilder sql = new StringBuilder(); sql.AppendLine("INSERT INTO officedba.StorageAccount"); sql.AppendLine(" (CompanyCD"); sql.AppendLine(" ,BillType"); sql.AppendLine(" ,ProductID"); sql.AppendLine(" ,StorageID"); sql.AppendLine(" ,BatchNo"); sql.AppendLine(" ,BillNo"); sql.AppendLine(" ,HappenDate"); // sql.AppendLine(" ,HappenCount"); sql.AppendLine(" ,ProductCount"); sql.AppendLine(" ,Creator"); sql.AppendLine(" ,Price"); sql.AppendLine(" ,PageUrl)"); sql.AppendLine(" VALUES"); sql.AppendLine(" (@CompanyCD"); sql.AppendLine(" ,@BillType"); sql.AppendLine(" ,@ProductID"); sql.AppendLine(" ,@StorageID"); sql.AppendLine(" ,@BatchNo"); sql.AppendLine(" ,@BillNo"); sql.AppendLine(" ,getdate()"); //sql.AppendLine(" ,@HappenCount"); sql.AppendLine(" ,@ProductCount"); sql.AppendLine(" ,@Creator"); sql.AppendLine(" ,@Price"); sql.AppendLine(" ,@PageUrl)"); SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", AccountM_.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillType", AccountM_.BillType.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", AccountM_.ProductID.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", AccountM_.StorageID.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", AccountM_.BatchNo)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillNo", AccountM_.BillNo)); //comm.Parameters.Add(SqlHelper.GetParameterFromString("@HappenCount", model.HappenCount.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount", AccountM_.ProductCount.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", AccountM_.Creator.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Price", AccountM_.Price.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PageUrl", AccountM_.PageUrl)); #endregion //SqlCommand AccountCom_ = StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"1"); SqlCmdList.Add(comm); #endregion } bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList); if (result) return "0"; else return "1"; }
public static DataTable GetStorageCheckDetail(StorageCheck sc) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select scd.SortNo,scd.ProductID,ISNULL(p.IsBatchNo,'')IsBatchNo,ISNULL(scd.BatchNo,'')BatchNo,ISNULL(scd.UsedUnitID,0)UsedUnitID,ISNULL(scd.UsedUnitCount,0.00)UsedUnitCount,ISNULL(scd.UsedPrice,0.00)UsedPrice,ISNULL(scd.ExRate,0.00)ExRate,"); strSql.Append("scd.UnitID,scd.NowCount,scd.CheckCount,scd.DiffCount,"); strSql.Append("(SELECT u.CodeName from officedba.CodeUnitType as u where scd.UnitID=u.ID) AS UnitName,"); strSql.Append("(SELECT ii.CodeName from officedba.CodeUnitType as ii where scd.UsedUnitID=ii.ID) AS UsedUnitName,"); strSql.Append("scd.DiffType,case scd.DiffType when '0' then '正常' when '1' then '盘盈' when '2' then '盘亏' else '' end DiffTypeName,scd.Remark,scd.ModifiedDate,scd.ModifiedUserID,"); strSql.Append("p.ProdNo,p.ProductName,p.Specification,isnull(p.StandardCost,0) as StandardCost "); strSql.Append("from officedba.StorageCheckDetail as scd "); strSql.Append("LEFT JOIN officedba.ProductInfo AS p on scd.ProductID=P.ID "); strSql.Append("WHERE scd.CompanyCD=@CompanyCD AND scd.CheckNo=@CheckNo"); SqlParameter[] Paras = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@CheckNo",SqlDbType.VarChar) }; Paras[0].Value = sc.CompanyCD; Paras[1].Value = sc.CheckNo; return SqlHelper.ExecuteSql(strSql.ToString(), Paras); }
/*打印SQL*/ public static DataTable StorageCheckInfoPrint(StorageCheck sc) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine(" SELECT ID, CompanyCD, CheckNo, Title, CheckStartDate, CheckEndDate, CheckUserID, CheckDate, StorageID, "); strSql.AppendLine(" (case sc.CheckType when 0 then '' else (select ISNULL(ct.TypeName,'') from officedba.CodePublicType ct where sc.CheckType=ct.ID) end) as CheckTypeText,--查的是公共分类表 "); strSql.AppendLine(" (CASE sc.BillStatus WHEN '1' THEN '制单' WHEN '2' THEN '执行' WHEN '4' THEN '手工结单' WHEN '5' THEN '自动结单' END) AS BillStatusText, "); strSql.AppendLine(" (SELECT StorageName "); strSql.AppendLine(" FROM officedba.StorageInfo AS si "); strSql.AppendLine(" WHERE (ID = sc.StorageID)) AS StorageName, DeptID, "); strSql.AppendLine(" (SELECT DeptName "); strSql.AppendLine(" FROM officedba.DeptInfo AS di "); strSql.AppendLine(" WHERE (ID = sc.DeptID)) AS DeptName, CheckType, Transactor, "); strSql.AppendLine(" (SELECT EmployeeName "); strSql.AppendLine(" FROM officedba.EmployeeInfo AS ei "); strSql.AppendLine(" WHERE (ID = sc.Transactor)) AS TransactorName, NowCount, CheckCount, DiffCount, NowMoney, CheckMoney, DiffMoney, Summary, Remark, "); strSql.AppendLine(" Attachment, Creator, "); strSql.AppendLine(" (SELECT EmployeeName "); strSql.AppendLine(" FROM officedba.EmployeeInfo AS ei1 "); strSql.AppendLine(" WHERE (ID = sc.Creator)) AS CreatorName, CreateDate, BillStatus, CheckUserID AS Expr1, "); strSql.AppendLine(" (SELECT EmployeeName "); strSql.AppendLine(" FROM officedba.EmployeeInfo AS ei2 "); strSql.AppendLine(" WHERE (ID = sc.CheckUserID)) AS CheckUserName, CheckDate AS Expr2, Confirmor, "); strSql.AppendLine(" (SELECT EmployeeName "); strSql.AppendLine(" FROM officedba.EmployeeInfo AS ei3 "); strSql.AppendLine(" WHERE (ID = sc.Confirmor)) AS ConfirmorName, ConfirmDate, Closer, "); strSql.AppendLine(" (SELECT EmployeeName "); strSql.AppendLine(" FROM officedba.EmployeeInfo AS ei4 "); strSql.AppendLine(" WHERE (ID = sc.Closer)) AS CloserName, CloseDate, ModifiedDate, ModifiedUserID "); strSql.AppendLine(" FROM officedba.StorageCheck AS sc "); strSql.Append(" where sc.ID=@ID"); SqlParameter[] Paras = { new SqlParameter("@ID", SqlDbType.Int) }; Paras[0].Value = sc.ID; return SqlHelper.ExecuteSql(strSql.ToString(), Paras); }
public static DataTable StorageCheckGet(StorageCheck sc) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT sc.ID,sc.CompanyCD,sc.CheckNo,sc.Title,Convert(varchar(10),sc.CheckStartDate,120)CheckStartDate,Convert(varchar(10),sc.CheckEndDate,120)CheckEndDate,sc.CheckUserID, Convert(varchar(10),sc.CheckDate,120)CheckDate,"); strSql.Append("sc.StorageID,ISNULL(sc.ExtField1,'')ExtField1,ISNULL(sc.ExtField2,'')ExtField2,ISNULL(sc.ExtField3,'')ExtField3,ISNULL(sc.ExtField4,'')ExtField4,ISNULL(sc.ExtField5,'')ExtField5,ISNULL(sc.ExtField6,'')ExtField6,ISNULL(sc.ExtField7,'')ExtField7,ISNULL(sc.ExtField8,'')ExtField8,ISNULL(sc.ExtField9,'')ExtField9,ISNULL(sc.ExtField10,'')ExtField10, "); strSql.Append(" (select si.StorageName from officedba.StorageInfo as si where si.ID=sc.StorageID ) AS StorageName,"); strSql.Append(" sc.DeptID,"); strSql.Append(" (SELECT di.DeptName from officedba.DeptInfo as di where di.ID=sc.DeptID) AS DeptName,"); strSql.Append(" sc.CheckType,sc.Transactor,"); strSql.Append(" (select x.TypeName from officedba.CodePublicType as x where x.ID=sc.CheckType ) AS CheckTypeName,");//CheckTypeName strSql.Append(" (select ei.EmployeeName from officedba.EmployeeInfo as ei where ei.ID=sc.Transactor) as TransactorName,"); strSql.Append(" sc.NowCount,sc.CheckCount,sc.DiffCount,sc.NowMoney,sc.CheckMoney,sc.DiffMoney,sc.Summary,sc.Remark,sc.Attachment,"); strSql.Append(" sc.Creator,"); strSql.Append(" (select ei1.EmployeeName from officedba.EmployeeInfo as ei1 where ei1.ID=sc.Creator) as CreatorName,"); strSql.Append(" Convert(varchar(10),sc.CreateDate,120)CreateDate,sc.BillStatus,sc.CheckUserID,"); strSql.Append("case sc.BillStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'"); strSql.Append("when '4' then '手工结单' when '5' then '自动结单' else '' end as BillStatusName, "); strSql.Append(" (select ei2.EmployeeName from officedba.EmployeeInfo as ei2 where ei2.ID=sc.CheckUserID) as CheckUserName,"); strSql.Append(" Convert(varchar(10),sc.CheckDate,120)CheckDate,sc.Confirmor,"); strSql.Append(" (select ei3.EmployeeName from officedba.EmployeeInfo as ei3 where ei3.ID=sc.Confirmor) as ConfirmorName,"); strSql.Append(" Convert(varchar(10),sc.ConfirmDate,120)ConfirmDate,sc.Closer,"); strSql.Append(" (select ei4.EmployeeName from officedba.EmployeeInfo as ei4 where ei4.ID=sc.Closer) as CloserName,"); strSql.Append(" Convert(varchar(10),sc.CloseDate,120)CloseDate,Convert(varchar(10),sc.ModifiedDate,120)ModifiedDate,sc.ModifiedUserID"); strSql.Append(" FROM officedba.StorageCheck AS sc"); strSql.Append(" where sc.ID=@ID"); SqlParameter[] Paras = { new SqlParameter("@ID", SqlDbType.Int) }; Paras[0].Value = sc.ID; return SqlHelper.ExecuteSql(strSql.ToString(), Paras); }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(StorageCheck sc, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.StorageCheck 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 CheckNo = @CheckNo"; cmd.Parameters.AddWithValue("@CompanyCD", sc.CompanyCD); cmd.Parameters.AddWithValue("@CheckNo", sc.CheckNo); cmd.CommandText = strSql; } catch (Exception) { } }
public static string StorageCheckUpdate(StorageCheck sc, Hashtable htExtAttr, List<StorageCheckDetail> scdList) { #region 盘点单 StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.StorageCheck set "); strSql.Append("Title=@Title,"); strSql.Append("CheckStartDate=@CheckStartDate,"); strSql.Append("CheckEndDate=@CheckEndDate,"); strSql.Append("StorageID=@StorageID,"); strSql.Append("DeptID=@DeptID,"); strSql.Append("CheckType=@CheckType,"); strSql.Append("Transactor=@Transactor,"); strSql.Append("NowCount=@NowCount,"); strSql.Append("CheckCount=@CheckCount,"); strSql.Append("DiffCount=@DiffCount,"); strSql.Append("NowMoney=@NowMoney,"); strSql.Append("CheckMoney=@CheckMoney,"); strSql.Append("DiffMoney=@DiffMoney,"); strSql.Append("Summary=@Summary,"); strSql.Append("Remark=@Remark,"); strSql.Append("Attachment=@Attachment,"); strSql.Append("BillStatus=@BillStatus,"); strSql.Append("ModifiedDate=@ModifiedDate,"); strSql.Append("ModifiedUserID=@ModifiedUserID"); strSql.Append(" where ID=@ID "); SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int,4), new SqlParameter("@Title", SqlDbType.VarChar,100), new SqlParameter("@CheckStartDate", SqlDbType.DateTime), new SqlParameter("@CheckEndDate", SqlDbType.DateTime), new SqlParameter("@StorageID", SqlDbType.Int,4), new SqlParameter("@DeptID", SqlDbType.Int,4), new SqlParameter("@CheckType", SqlDbType.Int,4), new SqlParameter("@Transactor", SqlDbType.Int,4), new SqlParameter("@NowCount", SqlDbType.Decimal,9), new SqlParameter("@CheckCount", SqlDbType.Decimal,9), new SqlParameter("@DiffCount", SqlDbType.Decimal,9), new SqlParameter("@NowMoney", SqlDbType.Decimal,9), new SqlParameter("@CheckMoney", SqlDbType.Decimal,9), new SqlParameter("@DiffMoney", SqlDbType.Decimal,9), new SqlParameter("@Summary", SqlDbType.VarChar,200), new SqlParameter("@Remark", SqlDbType.VarChar,800), new SqlParameter("@Attachment", SqlDbType.VarChar,100), new SqlParameter("@BillStatus", SqlDbType.Char,1), new SqlParameter("@ModifiedDate", SqlDbType.DateTime), new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,50)}; parameters[0].Value = sc.ID; parameters[1].Value = sc.Title; parameters[2].Value = sc.CheckStartkDate; parameters[3].Value = sc.CheckEndDate; parameters[4].Value = sc.StorageID; parameters[5].Value = sc.DeptID; parameters[6].Value = sc.CheckType; parameters[7].Value = sc.Transactor; parameters[8].Value = sc.NowCount; parameters[9].Value = sc.CheckCount; parameters[10].Value = sc.DiffCount; parameters[11].Value = sc.NowMoney; parameters[12].Value = sc.CheckMoney; parameters[13].Value = sc.DiffMoney; parameters[14].Value = sc.Summary; parameters[15].Value = sc.Remark; parameters[16].Value = sc.Attachment; parameters[17].Value = sc.BillStatus; parameters[18].Value = sc.ModifiedDate; parameters[19].Value = sc.ModifiedUserID; #endregion SqlCommand sqlMainCmd = new SqlCommand(); sqlMainCmd.CommandText = strSql.ToString(); sqlMainCmd.Parameters.AddRange(parameters); ArrayList SqlCmdList = new ArrayList(); SqlCmdList.Add(sqlMainCmd); #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(sc, htExtAttr, cmd); if (htExtAttr.Count > 0) SqlCmdList.Add(cmd); #endregion #region 明细 StringBuilder strDel = new StringBuilder(); strDel.Append("DELETE officedba.StorageCheckDetail where CompanyCD=@CompanyCD AND CheckNo=@CheckNo"); SqlParameter[] delParas = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@CheckNo",SqlDbType.VarChar) }; delParas[0].Value = sc.CompanyCD; delParas[1].Value = sc.CheckNo; SqlCommand sqlDelCmd = new SqlCommand(); sqlDelCmd.CommandText = strDel.ToString(); sqlDelCmd.Parameters.AddRange(delParas); SqlCmdList.Add(sqlDelCmd); foreach (StorageCheckDetail scd in scdList) { StringBuilder strSubSql = new StringBuilder(); strSubSql.Append("insert into officedba.StorageCheckDetail("); strSubSql.Append("CompanyCD,CheckNo,SortNo,ProductID,UnitID,NowCount,CheckCount,DiffCount,DiffType,Remark,ModifiedDate,ModifiedUserID,BatchNo,UsedUnitID,UsedUnitCount,UsedPrice,ExRate)"); strSubSql.Append(" values ("); strSubSql.Append("@CompanyCD,@CheckNo,@SortNo,@ProductID,@UnitID,@NowCount,@CheckCount,@DiffCount,@DiffType,@Remark,@ModifiedDate,@ModifiedUserID,@BatchNo,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate)"); strSubSql.Append(";select @@IDENTITY"); SqlParameter[] subParas = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@CheckNo", SqlDbType.VarChar,50), new SqlParameter("@SortNo", SqlDbType.Int,4), new SqlParameter("@ProductID", SqlDbType.Int,4), new SqlParameter("@UnitID", SqlDbType.Int,4),//基本单位 new SqlParameter("@NowCount", SqlDbType.Decimal,9), new SqlParameter("@CheckCount", SqlDbType.Decimal,9),//基本数量 new SqlParameter("@DiffCount", SqlDbType.Decimal,9), new SqlParameter("@DiffType", SqlDbType.Char,1), new SqlParameter("@Remark", SqlDbType.VarChar,800), new SqlParameter("@ModifiedDate", SqlDbType.DateTime), new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,50), new SqlParameter("@BatchNo", 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)//比率 }; subParas[0].Value = scd.CompanyCD; subParas[1].Value = scd.CheckNo; subParas[2].Value = scd.SortNo; subParas[3].Value = scd.ProductID; if (scd.UnitID != null) subParas[4].Value = scd.UnitID; else subParas[4].Value = DBNull.Value; subParas[5].Value = scd.NowCount; subParas[6].Value = scd.CheckCount; subParas[7].Value = scd.DiffCount; subParas[8].Value = scd.DiffType; subParas[9].Value = scd.Remark; subParas[10].Value = scd.ModifiedDate; subParas[11].Value = scd.ModifiedUserID; if (scd.BatchNo != null) subParas[12].Value = scd.BatchNo; else subParas[12].Value = DBNull.Value; if (scd.UsedUnitID != null) subParas[13].Value = scd.UsedUnitID; else subParas[13].Value = DBNull.Value; if (scd.UsedUnitCount != null) subParas[14].Value = scd.UsedUnitCount; else subParas[14].Value = DBNull.Value; if (scd.UsedPrice != null) subParas[15].Value = scd.UsedPrice; else subParas[15].Value = DBNull.Value; if (scd.ExRate != null) subParas[16].Value = scd.ExRate; else subParas[16].Value = DBNull.Value; SqlCommand sqlSubCmd = new SqlCommand(); sqlSubCmd.CommandText = strSubSql.ToString(); sqlSubCmd.Parameters.AddRange(subParas); SqlCmdList.Add(sqlSubCmd); } #endregion bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList); if (result) return "1|"; else return "3|"; }
public static string StorageCheckAdd(StorageCheck sc, Hashtable htExtAttr, List<StorageCheckDetail> scdList) { #region 插入盘点单 StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.StorageCheck("); strSql.Append("CompanyCD,CheckNo,Title,CheckStartDate,CheckEndDate,StorageID,DeptID,CheckType,Transactor,NowCount,CheckCount,DiffCount,NowMoney,CheckMoney,DiffMoney,Summary,Remark,Attachment,Creator,CreateDate,ModifiedDate,ModifiedUserID,BillStatus)"); strSql.Append(" values ("); strSql.Append("@CompanyCD,@CheckNo,@Title,@CheckStartDate,@CheckEndDate,@StorageID,@DeptID,@CheckType,@Transactor,@NowCount,@CheckCount,@DiffCount,@NowMoney,@CheckMoney,@DiffMoney,@Summary,@Remark,@Attachment,@Creator,@CreateDate,@ModifiedDate,@ModifiedUserID,@BillStatus)"); strSql.Append(";select @ID=@@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@CheckNo", SqlDbType.VarChar,50), new SqlParameter("@Title", SqlDbType.VarChar,100), new SqlParameter("@CheckStartDate", SqlDbType.DateTime), new SqlParameter("@CheckEndDate", SqlDbType.DateTime), new SqlParameter("@StorageID", SqlDbType.Int,4), new SqlParameter("@DeptID", SqlDbType.Int,4), new SqlParameter("@CheckType", SqlDbType.Int,4), new SqlParameter("@Transactor", SqlDbType.Int,4), new SqlParameter("@NowCount", SqlDbType.Decimal,9), new SqlParameter("@CheckCount", SqlDbType.Decimal,9), new SqlParameter("@DiffCount", SqlDbType.Decimal,9), new SqlParameter("@NowMoney", SqlDbType.Decimal,9), new SqlParameter("@CheckMoney", SqlDbType.Decimal,9), new SqlParameter("@DiffMoney", SqlDbType.Decimal,9), new SqlParameter("@Summary", SqlDbType.VarChar,200), new SqlParameter("@Remark", SqlDbType.VarChar,800), new SqlParameter("@Attachment", SqlDbType.VarChar,100), new SqlParameter("@Creator", SqlDbType.Int,4), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifiedDate", SqlDbType.DateTime), new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,50), new SqlParameter("@ID",SqlDbType.Int), new SqlParameter("@BillStatus",SqlDbType.VarChar) }; parameters[0].Value = sc.CompanyCD; parameters[1].Value = sc.CheckNo; parameters[2].Value = sc.Title; parameters[3].Value = sc.CheckStartkDate; parameters[4].Value = sc.CheckEndDate; parameters[5].Value = sc.StorageID; parameters[6].Value = sc.DeptID; parameters[7].Value = sc.CheckType; parameters[8].Value = sc.Transactor; parameters[9].Value = sc.NowCount; parameters[10].Value = sc.CheckCount; parameters[11].Value = sc.DiffCount; parameters[12].Value = sc.NowMoney; parameters[13].Value = sc.CheckMoney; parameters[14].Value = sc.DiffMoney; parameters[15].Value = sc.Summary; parameters[16].Value = sc.Remark; parameters[17].Value = sc.Attachment; parameters[18].Value = sc.Creator; parameters[19].Value = sc.CreateDate; parameters[20].Value = sc.ModifiedDate; parameters[21].Value = sc.ModifiedUserID; parameters[22].Direction = ParameterDirection.Output; parameters[23].Value = sc.BillStatus; #endregion SqlCommand SqlMainCmd = new SqlCommand(); SqlMainCmd.CommandText = strSql.ToString(); SqlMainCmd.Parameters.AddRange(parameters); ArrayList SqlCmdList = new ArrayList(); SqlCmdList.Add(SqlMainCmd); #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(sc, htExtAttr, cmd); if (htExtAttr.Count > 0) SqlCmdList.Add(cmd); #endregion #region 盘点单明细 foreach (StorageCheckDetail scd in scdList) { StringBuilder strSubSql = new StringBuilder(); strSubSql.Append("insert into officedba.StorageCheckDetail("); strSubSql.Append("CompanyCD,CheckNo,SortNo,ProductID,UnitID,NowCount,CheckCount,DiffCount,DiffType,Remark,ModifiedDate,ModifiedUserID,BatchNo,UsedUnitID,UsedUnitCount,UsedPrice,ExRate)"); strSubSql.Append(" values ("); strSubSql.Append("@CompanyCD,@CheckNo,@SortNo,@ProductID,@UnitID,@NowCount,@CheckCount,@DiffCount,@DiffType,@Remark,@ModifiedDate,@ModifiedUserID,@BatchNo,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate)"); strSubSql.Append(";select @@IDENTITY"); SqlParameter[] subParas = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@CheckNo", SqlDbType.VarChar,50), new SqlParameter("@SortNo", SqlDbType.Int,4), new SqlParameter("@ProductID", SqlDbType.Int,4), new SqlParameter("@UnitID", SqlDbType.Int,4),//基本单位 new SqlParameter("@NowCount", SqlDbType.Decimal,9), new SqlParameter("@CheckCount", SqlDbType.Decimal,9),//基本数量 new SqlParameter("@DiffCount", SqlDbType.Decimal,9), new SqlParameter("@DiffType", SqlDbType.Char,1), new SqlParameter("@Remark", SqlDbType.VarChar,800), new SqlParameter("@ModifiedDate", SqlDbType.DateTime), new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,50), new SqlParameter("@BatchNo", 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)//比率 }; subParas[0].Value = scd.CompanyCD; subParas[1].Value = scd.CheckNo; subParas[2].Value = scd.SortNo; subParas[3].Value = scd.ProductID; if (scd.UnitID != null) subParas[4].Value = scd.UnitID; else subParas[4].Value = DBNull.Value; subParas[5].Value = scd.NowCount; subParas[6].Value = scd.CheckCount; subParas[7].Value = scd.DiffCount; subParas[8].Value = scd.DiffType; subParas[9].Value = scd.Remark; subParas[10].Value = scd.ModifiedDate; subParas[11].Value = scd.ModifiedUserID; if (scd.BatchNo != null) subParas[12].Value = scd.BatchNo; else subParas[12].Value = DBNull.Value; if (scd.UsedUnitID != null) subParas[13].Value = scd.UsedUnitID; else subParas[13].Value = DBNull.Value; if (scd.UsedUnitCount != null) subParas[14].Value = scd.UsedUnitCount; else subParas[14].Value = DBNull.Value; if (scd.UsedPrice != null) subParas[15].Value = scd.UsedPrice; else subParas[15].Value = DBNull.Value; if (scd.ExRate != null) subParas[16].Value = scd.ExRate; else subParas[16].Value = DBNull.Value; SqlCommand sqlSubCmd = new SqlCommand(); sqlSubCmd.CommandText = strSubSql.ToString(); sqlSubCmd.Parameters.AddRange(subParas); SqlCmdList.Add(sqlSubCmd); } #endregion bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList); if (result) { return "1|" + ((SqlCommand)SqlCmdList[0]).Parameters["@ID"].Value.ToString() + "#" + sc.CheckNo; } else return "3|保存数据失败"; }