public static DataTable GetStorageInitailTableBycondition(string BatchNo,StorageInitailModel model, string timeStart, string timeEnd, string orderby) { try { return StorageInitailInfoDBHelper.GetStorageInitailTableBycondition(BatchNo,model, timeStart, timeEnd, orderby); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 获取期初库存和期初库存详细信息 /// </summary> /// <returns>DataTable</returns> public static DataTable GetStorageInitailDetailInfo(StorageInitailModel model) { try { return StorageInitailInfoDBHelper.GetStorageInitailDetailInfo(model); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 查询期初库存列表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetStorageInitailTableBycondition(string BatchNo,string EFIndex,string EFDesc,StorageInitailModel model, string timeStart, string timeEnd, int pageIndex, int pageCount, string ord, ref int TotalCount) { try { return StorageInitailInfoDBHelper.GetStorageInitailTableBycondition(BatchNo,EFIndex, EFDesc, model, timeStart, timeEnd, pageIndex, pageCount, ord, ref TotalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 期初库存插入(插入期初库存和期初库存详细) /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertStorageInitail(StorageInitailModel model, List<StorageInitailDetailModel> modelList,Hashtable ht, out int IndexIDentity) { IndexIDentity = 0; //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //定义返回变量 bool isSucc = false; /* * 定义日志内容变量 * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库 * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件 */ //获取公司代码 string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; //执行删除操作 try { //执行更新 isSucc = StorageInitailInfoDBHelper.InsertStorageInitail(model, modelList,ht, out IndexIDentity); } catch (Exception ex) { //输出日志 WriteSystemLog(userInfo, ex); } //定义变量 string remark; //成功时 if (isSucc) { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } else { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //操作日志 LogInfoModel logModel = InitLogInfo(model.InNo); //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空 logModel.Element = ConstUtil.LOG_PROCESS_INSERT; //设置操作成功标识 logModel.Remark = remark; //登陆日志 LogDBHelper.InsertLog(logModel); return isSucc; }
/// <summary> /// 保存时基本信息参数设置 /// </summary> /// <param name="comm"></param> /// <param name="model"></param> private static void SetSaveParameter(SqlCommand comm, StorageInitailModel model) { if (model.ID != null && model.ID != "") { comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID ", model.ID));//自动生成 } else { comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator ", model.Creator));//制单人 } //@CompanyCD,@InNo,@Title,@StorageID,@Executor,@EnterDate, //@Remark,@Creator,@CreateDate,@BillStatus,getdate(),@ModifiedUserID,@DeptID,@Summary,@TotalPrice,@CountTotal comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD ", model.CompanyCD));//公司编码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@InNo ", model.InNo));//入库单编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title ", model.Title));//主题 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID ", model.StorageID)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Executor ", model.Executor));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterDate ", model.EnterDate));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark ", model.Remark));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate ", model.CreateDate));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus ", model.BillStatus));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID ", model.ModifiedUserID));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID ", model.DeptID));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@Summary ", model.Summary));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalPrice ", model.TotalPrice));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal ", model.CountTotal));// }
public static bool CancelCloseBill(StorageInitailModel model) { StringBuilder sql = new StringBuilder(); sql.AppendLine(" UPDATE officedba.StorageInitail SET"); sql.AppendLine(" Closer = NULL,"); sql.AppendLine(" CloseDate = NULL,"); sql.AppendLine(" BillStatus = 2,"); sql.AppendLine(" ModifiedUserID = @ModifiedUserID,"); sql.AppendLine(" ModifiedDate = getdate()"); sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID)); return SqlHelper.ExecuteTransWithCommand(comm); }
public static bool ConfirmBill(StorageInitailModel model) { ArrayList lstConfirm = new ArrayList(); StringBuilder sql = new StringBuilder(); sql.AppendLine(" UPDATE officedba.StorageInitail SET"); sql.AppendLine(" Confirmor = @Confirmor,"); sql.AppendLine(" confirmDate = getdate(),"); sql.AppendLine(" BillStatus = 2,"); sql.AppendLine(" ModifiedUserID = @ModifiedUserID,"); sql.AppendLine(" ModifiedDate = getdate()"); sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID)); lstConfirm.Add(comm); List<StorageInitailDetailModel> modelList = new List<StorageInitailDetailModel>(); string sqlSele = "select a.ID,a.CompanyCD, a.ProductID,b.StorageID,a.BatchNo,a.InNo BillNo,a.UnitPrice Price,Convert(varchar(10),b.EnterDate,23) HappenDate," + " a.Remark, a.ProductCount from officedba.StorageInitailDetail a left join" + " officedba.StorageInitail b on a.InNo=b.InNo and a.CompanyCD = b.CompanyCD where a.CompanyCD='" + model.CompanyCD + "' and a.InNo=(select InNo from officedba.StorageInitail where ID=" + model.ID + ")"; DataTable dt = SqlHelper.ExecuteSql(sqlSele); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { StorageInitailDetailModel modelDetail = new StorageInitailDetailModel(); StorageAccountModel StorageAccountM = new StorageAccountModel(); if (dt.Rows[i]["ProductID"].ToString() != "") { modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString(); StorageAccountM.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString()); } if (dt.Rows[i]["ProductCount"].ToString() != "") { modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString(); StorageAccountM.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString()); StorageAccountM.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString()); } if (dt.Rows[i]["StorageID"].ToString() != "") { model.StorageID = dt.Rows[i]["StorageID"].ToString(); StorageAccountM.StorageID = Convert.ToInt32(dt.Rows[i]["StorageID"].ToString()); } //插入流水账表 StorageAccountM.CompanyCD = dt.Rows[i]["CompanyCD"].ToString(); StorageAccountM.BillType = 1; if (dt.Rows[i]["BatchNo"].ToString() != "") { modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString(); StorageAccountM.BatchNo = dt.Rows[i]["BatchNo"].ToString(); } modelList.Add(modelDetail); StorageAccountM.BillNo = dt.Rows[i]["BillNo"].ToString(); StorageAccountM.Price = Convert.ToDecimal(dt.Rows[i]["Price"].ToString()); StorageAccountM.HappenDate = Convert.ToDateTime(dt.Rows[i]["HappenDate"].ToString()); StorageAccountM.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; StorageAccountM.PageUrl = "../Office/StorageManager/StorageInitailAdd.aspx"; StorageAccountM.ReMark = dt.Rows[i]["Remark"].ToString(); SqlCommand commSA = new SqlCommand(); commSA = StorageAccountDBHelper.InsertStorageAccountCommand(StorageAccountM, "0"); lstConfirm.Add(commSA); } } if (modelList != null && modelList.Count > 0)//明细不为空的时候 { for (int i = 0; i < modelList.Count; i++) { SqlCommand commPD = new SqlCommand(); if (Exists(modelList[i].BatchNo,model.StorageID, modelList[i].ProductID, model.CompanyCD)) { commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model, true); } else { commPD = InsertStorageProduct(modelList[i].BatchNo,modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model.CompanyCD); } lstConfirm.Add(commPD); } } return SqlHelper.ExecuteTransWithArrayList(lstConfirm); }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(StorageInitailModel model, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.StorageInitail 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 InNo = @InNo"; cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD); cmd.Parameters.AddWithValue("@InNo", model.InNo); cmd.CommandText = strSql; } catch (Exception) { } }
public static bool UpdateStorageInitail(StorageInitailModel model, List<StorageInitailDetailModel> modelList,Hashtable ht) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.StorageInitail set "); strSql.Append("Title=@Title,"); strSql.Append("StorageID=@StorageID,"); strSql.Append("Executor=@Executor,"); strSql.Append("EnterDate=@EnterDate,"); strSql.Append("Remark=@Remark,"); strSql.Append("BillStatus=@BillStatus,"); strSql.Append("ModifiedDate=getdate(),"); strSql.Append("ModifiedUserID=@ModifiedUserID,"); strSql.Append("DeptID=@DeptID,"); strSql.Append("Summary=@Summary,"); strSql.Append("TotalPrice=@TotalPrice,"); strSql.Append("CountTotal=@CountTotal"); strSql.Append(" where ID=@ID "); SqlCommand comm = new SqlCommand(strSql.ToString()); SetSaveParameter(comm, model); ArrayList lstUpdate = new ArrayList(); lstUpdate.Add(comm);//数组加入插入基表的command #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, ht, cmd); if (ht.Count > 0) lstUpdate.Add(cmd); #endregion //先删掉明细表中对应单据的所有数据 string delDetail = "delete from officedba.StorageInitailDetail where CompanyCD='" + model.CompanyCD + "' and InNo='" + model.InNo + "'"; SqlCommand commdel = new SqlCommand(delDetail); lstUpdate.Add(commdel); if (modelList != null && modelList.Count > 0)//明细不为空的时候 { StringBuilder strSqlDetail = new StringBuilder(); strSqlDetail.Append("insert into officedba.StorageInitailDetail("); strSqlDetail.Append("InNo,SortNo,ProductID,UnitPrice,ProductCount,TotalPrice,Remark,ModifiedDate,ModifiedUserID,CompanyCD,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)"); strSqlDetail.Append(" values ("); strSqlDetail.Append("@InNo,@SortNo,@ProductID,@UnitPrice,@ProductCount,@TotalPrice,@Remark,getdate(),@ModifiedUserID,@CompanyCD,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)"); strSqlDetail.Append(";select @@IDENTITY"); for (int i = 0; i < modelList.Count; i++) { SqlCommand commDetail = new SqlCommand(); commDetail.CommandText = strSqlDetail.ToString(); EditInitailDetailInfo(commDetail, modelList[i]); lstUpdate.Add(commDetail);//循环加入数组(重新获取页面上明细数据) } } return SqlHelper.ExecuteTransWithArrayList(lstUpdate); }
/// <summary> /// 添加期初入库单信息及其详细信息 /// </summary> /// <returns>DataTable</returns> public static bool InsertStorageInitail(StorageInitailModel model, List<StorageInitailDetailModel> modelList,Hashtable ht, out int IndexIDentity) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine("insert into officedba.StorageInitail("); strSql.AppendLine("CompanyCD,InNo,Title,StorageID,Executor,EnterDate,Remark,Creator,CreateDate,BillStatus,ModifiedDate,ModifiedUserID,DeptID,Summary,TotalPrice,CountTotal)"); strSql.AppendLine(" values ("); strSql.AppendLine("@CompanyCD,@InNo,@Title,@StorageID,@Executor,@EnterDate,@Remark,@Creator,getdate(),@BillStatus,getdate(),@ModifiedUserID,@DeptID,@Summary,@TotalPrice,@CountTotal)"); strSql.AppendLine("set @IndexID = @@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.CommandText = strSql.ToString(); SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int); IndexID.Direction = ParameterDirection.Output; comm.Parameters.Add(IndexID); SetSaveParameter(comm, model); ArrayList lstInsert = new ArrayList(); lstInsert.Add(comm);//数组加入插入基表的command #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, ht, cmd); if (ht.Count > 0) lstInsert.Add(cmd); #endregion if (modelList != null && modelList.Count > 0)//明细不为空的时候 { //插入期初入库明细 StringBuilder strSqlDetail = new StringBuilder(); strSqlDetail.Append("insert into officedba.StorageInitailDetail("); strSqlDetail.Append("InNo,SortNo,ProductID,UnitPrice,ProductCount,TotalPrice,Remark,ModifiedDate,ModifiedUserID,CompanyCD,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)"); strSqlDetail.Append(" values ("); strSqlDetail.Append("@InNo,@SortNo,@ProductID,@UnitPrice,@ProductCount,@TotalPrice,@Remark,getdate(),@ModifiedUserID,@CompanyCD,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)"); strSqlDetail.Append(";select @@IDENTITY"); for (int i = 0; i < modelList.Count; i++) { SqlCommand commDetail = new SqlCommand(); commDetail.CommandText = strSqlDetail.ToString(); EditInitailDetailInfo(commDetail, modelList[i]); lstInsert.Add(commDetail);//循环加入数组(重新获取页面上明细数据) } } bool result = SqlHelper.ExecuteTransWithArrayList(lstInsert); if (result) { IndexIDentity = int.Parse(((SqlCommand)lstInsert[0]).Parameters["@IndexID"].Value.ToString()); } else { IndexIDentity = 0; } return result; }
public static DataTable GetStorageInitailDetailInfo(StorageInitailModel model) { //string[] sql = new string[2]; //查询期初入库详细信息 StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT a.ID "); sql.AppendLine(",a.InNo, b.BatchNo "); sql.AppendLine(",a.Title "); sql.AppendLine(",a.StorageID "); sql.AppendLine(",s.StorageName "); sql.AppendLine(",a.Executor "); sql.AppendLine(",a.ExtField1 "); sql.AppendLine(",a.ExtField2 "); sql.AppendLine(",a.ExtField3 "); sql.AppendLine(",a.ExtField4 "); sql.AppendLine(",a.ExtField5 "); sql.AppendLine(",a.ExtField6 "); sql.AppendLine(",a.ExtField7 "); sql.AppendLine(",a.ExtField8 "); sql.AppendLine(",a.ExtField9 "); sql.AppendLine(",a.ExtField10 "); sql.AppendLine(",ISNULL(f.EmployeeName,'') as ExecutorName "); sql.AppendLine(",case when a.EnterDate Is NULL then '' else CONVERT(VARCHAR(10),a.EnterDate, 21) end AS EnterDate "); sql.AppendLine(",ISNULL(a.Remark,'') as Remark "); sql.AppendLine(",a.Creator "); sql.AppendLine(",ISNULL(g.EmployeeName,'') as CreatorName "); sql.AppendLine(",case when a.CreateDate Is NULL then '' else CONVERT(VARCHAR(10),a.CreateDate, 21) end AS CreateDate "); sql.AppendLine(",a.Confirmor "); sql.AppendLine(",ISNULL(h.EmployeeName,'') as ConfirmorName "); sql.AppendLine(",case when a.ConfirmDate Is NULL then '' else CONVERT(VARCHAR(10),a.ConfirmDate, 21) end AS ConfirmDate "); sql.AppendLine(",a.Closer "); sql.AppendLine(",ISNULL(i.EmployeeName,'') as CloserName "); sql.AppendLine(",case when a.CloseDate Is NULL then '' else CONVERT(VARCHAR(10),a.CloseDate, 21) end AS CloseDate "); sql.AppendLine(",case when a.ModifiedDate Is NULL then '' else CONVERT(VARCHAR(10),a.ModifiedDate, 21) end AS ModifiedDate "); sql.AppendLine(",a.ModifiedUserID as ModifiedUserName "); sql.AppendLine(",a.BillStatus "); sql.AppendLine(",case a.BillStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'"); sql.AppendLine("when '4' then '手工结单' when '5' then '自动结单' else '' end as BillStatusName "); sql.AppendLine(",a.DeptID "); sql.AppendLine(",ISNULL(j.DeptName,'') as DeptName "); sql.AppendLine(",ISNULL(a.Summary,'') as Summary "); sql.AppendLine(",ISNULL(a.TotalPrice,'0') as A_TotalPrice "); sql.AppendLine(",ISNULL(a.CountTotal,'0') as CountTotal "); sql.AppendLine(",b.ID as DetailID "); sql.AppendLine(",b.ProductID "); sql.AppendLine(",b.SortNo "); sql.AppendLine(",ISNULL(c.ProdNo,'') as ProductNo "); sql.AppendLine(",ISNULL(c.ProductName,'') as ProductName "); sql.AppendLine(",ISNULL(q.CodeName,'') as UnitID "); sql.AppendLine(",ISNULL(c.Specification,'') as Specification "); sql.AppendLine(",ISNULL(b.UnitPrice,0) as UnitPrice "); sql.AppendLine(",ISNULL(b.TotalPrice,'0') as B_TotalPrice "); sql.AppendLine(",ISNULL(b.ProductCount,0) as ProductCount "); sql.AppendLine(",ISNULL(b.Remark,'') as DetaiRemark,b.TotalPrice"); sql.AppendLine(",b.UsedUnitID as UsedUnitID,z.CodeName UsedUnitName "); sql.AppendLine(", b.UsedUnitCount as UsedUnitCount "); sql.AppendLine(",ISNULL(b.UsedPrice,0) as UsedPrice "); sql.AppendLine(",b.ExRate as ExRate "); sql.AppendLine(",c.IsBatchNo "); sql.AppendLine("FROM officedba.StorageInitail a "); sql.AppendLine("left join officedba.StorageInitailDetail b on a.InNo=b.InNo and a.CompanyCD=b.CompanyCD "); sql.AppendLine("left join officedba.ProductInfo c on c.ID=b.ProductID "); sql.AppendLine("left join officedba.EmployeeInfo f on a.Executor=f.ID "); sql.AppendLine("left join officedba.EmployeeInfo g on a.Creator=g.ID "); sql.AppendLine("left join officedba.EmployeeInfo h on a.Confirmor=h.ID "); sql.AppendLine("left join officedba.EmployeeInfo i on a.Closer=i.ID "); sql.AppendLine("left join officedba.DeptInfo j on a.DeptID=j.ID "); sql.AppendLine("left join officedba.CodeUnitType q on q.ID=c.UnitID "); sql.AppendLine("left join officedba.StorageInfo s on s.ID=a.StorageID "); sql.AppendLine("left join officedba.CodeUnitType z on z.ID=b.UsedUnitID "); sql.AppendLine("left join (select w.UserID,x.EmployeeName from officedba.UserInfo w ,officedba.EmployeeInfo x where w.EmployeeID =x.ID) m "); sql.AppendLine(" on a.ModifiedUserID=m.UserID "); sql.AppendLine(" where a.CompanyCD='" + model.CompanyCD + "' and a.id=" + model.ID); return SqlHelper.ExecuteSql(sql.ToString()); //SortNo,InNo,ProductID,UnitID,UnitPrice,TotalPrice,Remark //查询标准工序下的工艺明细列表信息 // sql[1] = "select * from officedba.StorageInitailDetail a inner join officedba.StorageInitail b on b.InNo=a.InNo and b.CompanyCD='" + model.CompanyCD; //return SqlHelper.ExecuteSql(sql.ToString()); }
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_Init; StorageInitailModel OutSellM_ = new StorageInitailModel(); OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; OutSellM_.ID = this.intMrpID.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"}, { "入库单编号", "InNo"}, { "入库单主题 ", "Title"}, { "仓库", "StorageName" }, { "入库部门 ", "DeptName" }, { "入库人", "ExecutorName"}, { "入库时间", "EnterDate"}, { "摘要 ", "Summary"}, { "批次", "BatchNo"}, { "入库数量合计 ", "CountTotal"}, { "入库金额合计 ", "A_TotalPrice"}, { "制单人", "CreatorName"}, { "制单日期", "CreateDate"}, { "单据状态", "BillStatusName"}, { "确认人", "ConfirmorName"}, { "确认日期", "ConfirmDate"}, { "结单人", "CloserName"}, { "结单日期", "CloseDate"}, { "最后更新人", "ModifiedUserName"}, { "最后更新日期", "ModifiedDate"}, { "备注", "Remark"}, }; if (HiddenMoreUnit.Value == "true") { aDetail = new string[,] { { "序号", "SortNo"}, { "物品编号", "ProductNo"}, { "物品名称", "ProductName" }, { "规格", "Specification" }, { "基本单位", "UnitID"}, { "基本数量", "ProductCount"}, { "单位", "UsedUnitName"}, //++ { "数量", "UsedUnitCount"}, //++ { "入库单价 ", "UnitPrice"}, { "入库金额 ", "B_TotalPrice"}, { "备注", "DetaiRemark"}, }; } else { aDetail = new string[,] { { "序号", "SortNo"}, { "物品编号", "ProductNo"}, { "物品名称", "ProductName" }, { "规格", "Specification" }, { "单位", "UnitID"}, { "数量", "ProductCount"}, { "入库单价 ", "UnitPrice"}, { "入库金额 ", "B_TotalPrice"}, { "备注", "DetaiRemark"}, }; } #region 1.扩展属性 DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageInitail"); 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(); } } } } #endregion DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model); DataTable dtMain = StorageInitailBus.GetStorageInitailDetailInfo(OutSellM_); // DataTable dtDetail = MRPBus.GetMRPDetailInfo(modelMRP); 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 { isSeted.Value = "0"; strBaseFields = "InNo|Title|StorageName|DeptName|ExecutorName|EnterDate|Summary|BatchNo|CountTotal|A_TotalPrice" + "|CreatorName|CreateDate|BillStatusName|ConfirmorName|ConfirmDate|CloserName|CloseDate" + "|ModifiedUserName|ModifiedDate|Remark" + "|ExtField1|ExtField2|ExtField3|ExtField4|ExtField5|ExtField6|ExtField7|ExtField8|ExtField9|ExtField10"; if (HiddenMoreUnit.Value == "True") { strDetailFields = "SortNo|ProductNo|ProductName|Specification|UnitID|ProductCount|UsedUnitName|UsedUnitCount|UnitPrice|B_TotalPrice|DetaiRemark"; } else { strDetailFields = "SortNo|ProductNo|ProductName|Specification|UnitID|ProductCount|UnitPrice|B_TotalPrice|DetaiRemark"; } } #region 主表信息 if (!string.IsNullOrEmpty(strBaseFields)) { tableBase.InnerHtml = WritePrintPageTable("期初库存录入单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMain, dtMain, true); } #endregion #region 明细信息 if (!string.IsNullOrEmpty(strDetailFields)) { tableDetail.InnerHtml = WritePrintPageTable("期初库存录入单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMain, dtMain, false); } #endregion }
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_BORROW; StorageInitailModel OutSellM_ = new StorageInitailModel(); OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; OutSellM_.ID = this.intMrpID.ToString(); /*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/ string[,] aBase = { { "{ExtField1}", "ExtField1"}, { "{ExtField2}", "ExtField2"}, { "{ExtField3}", "ExtField3"}, { "{ExtField4}", "ExtField4"}, { "{ExtField5}", "ExtField5"}, { "{ExtField6}", "ExtField6"}, { "{ExtField7}", "ExtField7"}, { "{ExtField8}", "ExtField8"}, { "{ExtField9}", "ExtField9"}, { "{ExtField10}", "ExtField10"}, { "借货单编号", "BorrowNo"}, { "借货单主题 ", "Title"}, { "借货人", "BorrowerText" }, { "借货部门", "DeptName" }, { "借货原因", "Reason"}, { "借货日期", "BorrowDate"}, { "借出仓库 ", "SotorageName"}, { "出库日期 ", "OutDate"}, { "出库人 ", "OuterText"}, { "借出部门", "OutDeptName"}, { "摘要", "Summary"}, { "借货数量合计 ", "CountTotal"}, { "借货金额合计 ", "TotalPrice"}, { "制单人", "CreatorText"}, { "制单日期", "CreateDate"}, { "单据状态", "BillStatusText"}, { "确认人", "ConfirmorText"}, { "确认日期", "ConfirmDate"}, { "结单人", "CloserText"}, { "结单日期", "CloseDate"}, { "最后更新人", "ModifiedUserID"}, { "最后更新日期", "ModifiedDate"}, { "备注", "Remark"}, }; string[,] aDetail = null; if (!UserInfo.IsMoreUnit) aDetail = new string[,]{ { "序号", "SortNo"}, { "物品编号", "ProdNo"}, { "物品名称", "ProductName" }, {"批次","BatchNo"}, { "规格", "Specification" }, { "单位", "CodeName"}, { "现有存量", "UseCount"}, { "借出单价 ", "UnitPrice"}, { "借货数量 ", "ProductCount"}, { "借货金额 ", "TotalPrice"}, { "预计返还日期 ", "ReturnDate"}, { "预计返还数量 ", "ReturnCount"}, { "已返还数量 ", "RealReturnCount"}, { "备注", "Remark"}, }; else aDetail = new string[,] { { "序号", "SortNo"}, { "物品编号", "ProdNo"}, { "物品名称", "ProductName" }, {"批次","BatchNo"}, { "规格", "Specification" }, { "基本单位", "CodeName"}, {"基本数量","ProductCount"}, {"单位","UsedUnitName"}, { "现有存量", "UseCount"}, { "借出单价 ", "UsedPrice"}, { "借货数量 ", "UsedUnitCount"}, { "借货金额 ", "TotalPrice"}, { "预计返还日期 ", "ReturnDate"}, { "预计返还数量 ", "ReturnCount"}, { "已返还数量 ", "RealReturnCount"}, { "备注", "Remark"}, }; #region 1.扩展属性 int countExt = 0; DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageBorrow"); 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 string No = Request.QueryString["No"].ToString(); DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model); DataTable dtMRP = StorageBorrowBus.GetStorageBorrowInfo(intMrpID); DataTable dtDetail = StorageBorrowBus.GetStorageBorrowDetail(OutSellM_.CompanyCD, No); string strBaseFields = ""; string strDetailFields = ""; if (dbPrint.Rows.Count > 0) { #region 设置过打印模板设置时 直接取出表里设置的值 isSeted.Value = "1"; strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString(); strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString(); #endregion } 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 2.主表信息 if (!string.IsNullOrEmpty(strBaseFields)) { tableBase.InnerHtml = WritePrintPageTable("借货申请单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, true); } #endregion #region 3.明细信息 if (!string.IsNullOrEmpty(strDetailFields)) { tableDetail.InnerHtml = WritePrintPageTable("借货申请单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, false); } #endregion }
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_TRANSFER; StorageInitailModel OutSellM_ = new StorageInitailModel(); OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; OutSellM_.ID = this.intMrpID.ToString(); /*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/ string[,] aBase = { { "{ExtField1}", "ExtField1"}, { "{ExtField2}", "ExtField2"}, { "{ExtField3}", "ExtField3"}, { "{ExtField4}", "ExtField4"}, { "{ExtField5}", "ExtField5"}, { "{ExtField6}", "ExtField6"}, { "{ExtField7}", "ExtField7"}, { "{ExtField8}", "ExtField8"}, { "{ExtField9}", "ExtField9"}, { "{ExtField10}", "ExtField10"}, { "调拨单编号", "TransferNo"}, { "调拨单主题", "Title"}, { "调拨申请人", "ApplyUserIDName" }, { "要货部门", "ApplyDeptIDName" }, { "调入仓库", "InStorageName"}, { "要求到货日期", "RequireInDate"}, { "调拨原因 ", "Reason"}, { "调货部门 ", "OutDeptIDName"}, { "调出仓库 ", "OutStorageName"}, { "业务状态", "BusiStatusText"}, { "摘要", "Summary"}, { "调拨数量合计 ", "TransferCount"}, { "调拨金额合计 ", "TransferPrice"}, { "调拨费用 ", "TransferFeeSum"}, { "制单人", "CreatorName"}, { "制单日期", "CreateDate"}, { "单据状态", "BillStatusText"}, { "确认人", "ConfirmorName"}, { "确认日期", "ConfirmDate"}, { "结单人", "CloserName"}, { "结单日期", "CloseDate"}, { "最后更新人", "ModifiedUserID"}, { "最后更新日期", "ModifiedDate"}, { "备注", "Remark"}, }; string[,] aDetail =null; if(!UserInfo.IsMoreUnit) aDetail= new string[,] { { "序号", "SortNo"}, { "物品编号", "ProdNo"}, { "物品名称", "ProductName" }, {"批次","BatchNo"}, { "规格", "Specification" }, { "单位", "UnitName"}, { "调拨单价", "TranPrice"}, { "调拨数量 ", "TranCount"}, { "调拨金额 ", "TranPriceTotal"}, { "已出库数量 ", "OutCount"}, { "已入库数量 ", "InCount"}, { "备注 ", "Remark"}, }; else aDetail = new string[,] { { "序号", "SortNo"}, { "物品编号", "ProdNo"}, { "物品名称", "ProductName" }, {"批次","BatchNo"}, { "规格", "Specification" }, { "基本单位", "UnitName"}, {"基本数量","TranCount"}, { "单位", "UsedUnitName"}, { "调拨单价", "TranPrice"}, { "调拨数量 ", "UsedUnitCount"}, { "调拨金额 ", "TranPriceTotal"}, { "已出库数量 ", "OutCount"}, { "已入库数量 ", "InCount"}, { "备注 ", "Remark"}, }; #region 1.扩展属性 int countExt = 0; DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageTransfer"); 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 string No = Request.QueryString["No"].ToString(); DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model); XBase.Model.Office.StorageManager.StorageTransfer st = new XBase.Model.Office.StorageManager.StorageTransfer(); st.ID = intMrpID; XBase.Model.Office.StorageManager.StorageTransferDetail std = new XBase.Model.Office.StorageManager.StorageTransferDetail(); std.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; std.TransferNo = No; /*读取数据*/ DataTable dtMRP = XBase.Business.Office.StorageManager.StorageTransferBus.GetStorageTransferInfoPrint(st); DataTable dtDetail = XBase.Business.Office.StorageManager.StorageTransferBus.GetStorageTransferDetailInfo(std); string strBaseFields = ""; string strDetailFields = ""; if (dbPrint.Rows.Count > 0) { #region 设置过打印模板设置时 直接取出表里设置的值 isSeted.Value = "1"; strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString(); strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString(); #endregion } 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 2.主表信息 if (!string.IsNullOrEmpty(strBaseFields)) { tableBase.InnerHtml = WritePrintPageTable("库存调拨单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, true); } #endregion #region 3.明细信息 if (!string.IsNullOrEmpty(strDetailFields)) { tableDetail.InnerHtml = WritePrintPageTable("库存调拨单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, false); } #endregion }
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_ADJUST; StorageInitailModel OutSellM_ = new StorageInitailModel(); OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; OutSellM_.ID = this.intMrpID.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"}, { "单据编号", "AdjustNo"}, { "单据标题", "Title"}, { "经办人", "Executor" }, { "调整部门", "DeptID" }, { "调整仓库", "StorageID"}, { "调整原因", "ReasonType"}, { "调整日期 ", "AdjustDate"}, { "摘要", "Summary"}, { "调整数量合计 ", "CountTotal"}, { "调整金额合计 ", "TotalPrice"}, { "制单人", "Creator"}, { "制单日期", "CreateDate"}, { "单据状态", "BillStatus"}, { "确认人", "Confirmor"}, { "确认日期", "ConfirmDate"}, { "结单人", "Closer"}, { "结单日期", "CloseDate"}, { "最后更新人", "ModifiedUserID"}, { "最后更新日期", "ModifiedDate"}, { "备注", "Remark"}, }; if (HiddenMoreUnit.Value == "True") { aDetail = new string[,]{ { "物品编号", "ID"}, { "物品名称", "ProductID" }, { "批次", "BatchNo" }, { "单位", "UsedUnitName"}, { "基本单位", "UnitID" }, { "基本数量", "AdjustCount" }, { "调整类型", "AdjustType"}, { "调整数量 ", "UsedUnitCount"}, { "成本单价 ", "UsedPrice"}, { "调整金额 ", "CostPriceTotal"}, { "备注 ", "Remark"}, }; } else { aDetail = new string[,]{ { "物品编号", "ID"}, { "物品名称", "ProductID" }, { "批次", "BatchNo" }, { "单位", "UnitID"}, { "调整类型", "AdjustType"}, { "调整数量 ", "AdjustCount"}, { "成本单价 ", "CostPrice"}, { "调整金额 ", "CostPriceTotal"}, { "备注 ", "Remark"}, }; } #region 1.扩展属性 int countExt = 0; DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageAdjust"); 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 dtMRP = XBase.Business.Office.StorageManager.StorageAdjustBus.GetAdjustInfo(intMrpID); DataTable dtDetail = XBase.Business.Office.StorageManager.StorageAdjustBus.GetAdjustDetailInfo(intMrpID); string strBaseFields = ""; string strDetailFields = ""; if (dbPrint.Rows.Count > 0) { #region 设置过打印模板设置时 直接取出表里设置的值 isSeted.Value = "1"; strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString(); strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString(); #endregion } 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 2.主表信息 if (!string.IsNullOrEmpty(strBaseFields)) { tableBase.InnerHtml = WritePrintPageTable("日常调整单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, true); } #endregion #region 3.明细信息 if (!string.IsNullOrEmpty(strDetailFields)) { tableDetail.InnerHtml = WritePrintPageTable("日常调整单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, false); } #endregion }
public static SqlCommand updateStorageProduct(string BatchNo, string ProductID, string StorageID, string ProductNum, StorageInitailModel model, bool flag) { //true 表示入库增加分仓存量数据 StringBuilder strSql = new StringBuilder(); if (flag == true) { strSql.AppendLine("update officedba.StorageProduct set "); strSql.AppendLine("ProductCount=ISNULL(ProductCount,0)+@ProductNum "); strSql.AppendLine(" where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD"); if (!string.IsNullOrEmpty(BatchNo)) { strSql.Append(" and BatchNo =@BatchNo "); } else { strSql.Append(" and (BatchNo is null or BatchNo='') "); } } //否则 表示(入库减少)分仓存量数据(修改的时候) else { strSql.AppendLine("update officedba.StorageProduct set "); strSql.AppendLine("ProductCount=ISNULL(ProductCount,0)-@ProductNum "); strSql.AppendLine(" where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD"); if (!string.IsNullOrEmpty(BatchNo)) { strSql.Append(" and BatchNo =@BatchNo "); } else { strSql.Append(" and (BatchNo is null or BatchNo='') "); } } SqlCommand commRePD = new SqlCommand(); commRePD.CommandText = strSql.ToString(); commRePD.Parameters.AddWithValue("@ProductNum", decimal.Parse(ProductNum)); commRePD.Parameters.AddWithValue("@StorageID", StorageID); commRePD.Parameters.AddWithValue("@ProductID", ProductID); commRePD.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); if (!string.IsNullOrEmpty(BatchNo)) { commRePD.Parameters.AddWithValue("@BatchNo", BatchNo); } return commRePD; }
protected void btnImport_Click(object sender, ImageClickEventArgs e) { StorageInitailModel model = new StorageInitailModel(); string EnterDateStart = string.Empty; string EnterDateEnd = string.Empty; model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; model.InNo = txtInNo.Value; model.Title = txtTitle.Value; model.DeptID = txtDeptID.Value; model.StorageID = sltStorageID.SelectedValue; model.Executor = txtExecutorID.Value; model.BillStatus = sltBillStatus.Value; EnterDateStart = txtEnterDateStart.Value; EnterDateEnd = txtEnterDateEnd.Value; string BatchNo = txtBatchNo.Value; string orderBy = txtorderBy.Value; if (!string.IsNullOrEmpty(orderBy)) { if (orderBy.Split('_')[1] == "a") { orderBy = orderBy.Split('_')[0] + " asc"; } else { orderBy = orderBy.Split('_')[0] + " desc"; } } DataTable dt = StorageInitailBus.GetStorageInitailTableBycondition(BatchNo,model, EnterDateStart, EnterDateEnd, orderBy); OutputToExecl.ExportToTableFormat(this, dt, new string[] { "单据编号", "单据主题", "仓库", "入库部门", "入库人", "入库日期", "入库数量", "入库金额", "单据状态" }, new string[] { "InNo", "Title", "StorageName", "DeptName", "Executor", "EnterDate", "CountTotal", "TotalPrice", "BillStatus" }, "期初库存录入列表"); }
/// <summary> /// 查询期初库存录入信息 /// </summary> /// <returns>DataTable</returns> public static DataTable GetStorageInitailTableBycondition(string BatchNo,string EFIndex,string EFDesc,StorageInitailModel model, string timeStart, string timeEnd, int pageIndex, int pageCount, string ord, ref int TotalCount) { StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT DISTINCT a.ID"); sql.AppendLine(" ,ISNULL(a.InNo,'') AS InNo"); sql.AppendLine(",ISNULL(a.Title,'') AS Title"); sql.AppendLine(" ,ISNULL(b.StorageName,'') AS StorageName"); sql.AppendLine(" ,ISNULL(c.EmployeeName,'') AS Executor"); sql.AppendLine(" ,ISNULL(d.DeptName,'') AS DeptName"); sql.AppendLine(",case when a.EnterDate Is NULL then '' else CONVERT(VARCHAR(10),a.EnterDate, 21) end AS EnterDate"); sql.AppendLine(",case a.billStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'"); sql.AppendLine(" when '4' then '手工结单' when '5' then '自动结单' end as BillStatus"); sql.AppendLine(" ,ISNULL(a.CountTotal,0) AS CountTotal"); sql.AppendLine(" ,ISNULL(a.TotalPrice,0) AS TotalPrice"); sql.AppendLine(" FROM officedba.StorageInitail a "); sql.AppendLine(" left join officedba.EmployeeInfo c on a.Executor = c.ID"); sql.AppendLine(" left join officedba.DeptInfo d on a.DeptID = d.ID"); sql.AppendLine(" left join officedba.StorageInfo b on a.StorageID = b.ID"); sql.AppendLine(" left join officedba.UserInfo h on a.ModifiedUserID=h.UserID "); sql.AppendLine(" left join officedba.StorageInitailDetail i on i.InNo = a.InNo "); sql.AppendLine("where a.CompanyCD=@CompanyCD"); //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); if (!string.IsNullOrEmpty(BatchNo)) { sql.AppendLine(" and i.BatchNo like '%'+ @BatchNo +'%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } if (!string.IsNullOrEmpty(model.InNo)) { sql.AppendLine(" and a.InNo like '%'+ @InNo +'%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@InNo", model.InNo)); } if (!string.IsNullOrEmpty(model.Title)) { sql.AppendLine(" and a.Title like '%'+ @Title +'%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); } if (!string.IsNullOrEmpty(model.StorageID)) { sql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } if (!string.IsNullOrEmpty(model.DeptID)) { sql.AppendLine(" and a.DeptID=@DeptID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID)); } if (!string.IsNullOrEmpty(model.Executor)) { sql.AppendLine(" and a.Executor=@Executor"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Executor", model.Executor)); } if (!string.IsNullOrEmpty(timeStart)) { sql.AppendLine(" and a.EnterDate>=@timeStart"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@timeStart", timeStart)); } if (!string.IsNullOrEmpty(timeEnd)) { sql.AppendLine(" and a.EnterDate<=@timeEnd"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@timeEnd", timeEnd)); } if (!string.IsNullOrEmpty(model.BillStatus)) { sql.AppendLine(" and a.BillStatus=@BillStatus"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus)); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { sql.AppendLine(" and a.ExtField" + EFIndex + " like @EFDesc "); comm.Parameters.Add(SqlHelper.GetParameter("@EFDesc", "%" + EFDesc + "%")); } comm.CommandText = sql.ToString(); return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount); }