/// <summary> /// 修改物品档案 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool UpdateProductPriceInfo(ProductPriceChangeModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; try { bool succ = false; LogInfoModel logModel = InitLogInfo(model.ChangeNo); logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; succ = ProductPriceChangeDBHelper.UpdateProductPriceInfo(model); if (!succ) { logModel.Remark = ConstUtil.LOG_PROCESS_FAILED; } else { logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS; } LogDBHelper.InsertLog(logModel); return(succ); } catch (Exception ex) { WriteSystemLog(userInfo, ex); return(false); } }
/// <summary> /// 设置参数 /// </summary> /// <param name="comm"></param> /// <param name="model"></param> private static void SetSaveParameter(SqlCommand comm, ProductPriceChangeModel model, bool flag) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//公司代码 if (!flag) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeNo", model.ChangeNo)); } else { comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", Convert.ToString(model.ID))); } comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", model.ProductID)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardSell", model.StandardSell)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellTax", model.SellTax)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardSellNew", model.StandardSellNew)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellTaxNew", model.SellTaxNew)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeDate", model.ChangeDate)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Chenger", model.Chenger)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.CreateDate)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaxRate", model.TaxRate)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Discount", model.Discount)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaxRateNew", model.TaxRateNew)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DiscountNew", model.DiscountNew)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", model.ModifiedDate)); //最后更新日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); //最后更新用户ID(对应操作用户U serID) }
/// <summary> /// 查询获取物品信息 /// </summary> /// <param name="Model"></param> /// <returns></returns> public static DataTable GetProductPriceInfo(ProductPriceChangeModel Model, string Starttime, string Endtime, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return(ProductPriceChangeDBHelper.GetProductPriceInfo(Model, Starttime, Endtime, pageIndex, pageCount, OrderBy, ref totalCount)); } catch (Exception ex) { return(null); throw ex; } }
protected void btnImport_Click(object sender, ImageClickEventArgs e) { ProductPriceChangeModel model = new ProductPriceChangeModel(); model.ChangeNo = txt_ChangeNo.Value; model.Title = txt_Title.Value; model.ProductID = hf_ProductID.Value; model.Chenger = txtChenger.Value; string OpenDate = txtOpenDate.Value; string CloseDate = txtCloseDate.Value; int totalCount = 0; model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; DataTable dt = ProductPriceChangeBus.GetProductPriceInfo(model, OpenDate, CloseDate, 1, 1000000, "ID desc", ref totalCount); foreach (DataRow row in dt.Rows) { if (row["BillStatus"].ToString() == "1") { row["BillStatus"] = "制单"; } if (row["BillStatus"].ToString() == "5") { row["BillStatus"] = "结单"; } } //导出标题 string headerTitle = "变更单编号|变更单主题|物品编号|物品名称|调整后销项率|调整后去税价|调整后含税价|调整后折扣|申请日期|确认日期|单据状态"; //string headerTitle = "建档日期|启用状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "ChangeNo|Title|ProductID|ProductName|TaxRateNew|StandardSellNew|SellTaxNew|DiscountNew|ChangeDate|ConfirmDate|BillStatus"; //string columnFiled = "CreateDate|strUsedStatus"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, " 物品售价变更单"); }
/// <summary> /// 修改物品档案 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool UpdateProductPriceInfo(ProductPriceChangeModel model) { StringBuilder sql = new StringBuilder(); sql.AppendLine("UPDATE officedba.ProductPriceChange "); sql.AppendLine(" SET "); sql.AppendLine(" Title = @Title "); sql.AppendLine(" ,ProductID = @ProductID "); sql.AppendLine(" ,StandardSell = @StandardSell "); sql.AppendLine(" ,SellTax = @SellTax "); sql.AppendLine(" ,StandardSellNew = @StandardSellNew "); sql.AppendLine(" ,SellTaxNew = @SellTaxNew "); sql.AppendLine(" ,ChangeDate = @ChangeDate "); sql.AppendLine(" ,Chenger = @Chenger "); sql.AppendLine(" ,Remark = @Remark "); sql.AppendLine(" ,BillStatus = @BillStatus "); sql.AppendLine(" ,Creator = @Creator "); sql.AppendLine(" ,CreateDate = @CreateDate "); sql.AppendLine(" ,Confirmor = @Confirmor "); sql.AppendLine(" ,ModifiedDate = @ModifiedDate "); sql.AppendLine(" ,TaxRate = @TaxRate"); sql.AppendLine(" ,TaxRateNew = @TaxRateNew"); sql.AppendLine(" ,Discount = @Discount"); sql.AppendLine(" ,DiscountNew = @DiscountNew"); sql.AppendLine(" ,ModifiedUserID = @ModifiedUserID"); sql.AppendLine(" WHERE ID=@ID and CompanyCD=@CompanyCD "); //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); SetSaveParameter(comm, model, true);//其他参数 //执行更新并设置更新结果 bool result = false; result = SqlHelper.ExecuteTransWithCommand(comm); return(result); }
/// <summary> /// 查询物品信息 /// </summary> /// <param name="Model"></param> /// <returns></returns> public static DataTable GetProductPriceInfo(ProductPriceChangeModel model, string starttime, string endtime, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("SELECT a.ID "); searchSql.AppendLine(" ,a.ChangeNo "); searchSql.AppendLine(" ,a.CompanyCD "); searchSql.AppendLine(" ,a.Title "); searchSql.AppendLine(" ,isnull(b.ProdNo,'')as ProductID "); searchSql.AppendLine(" ,isnull(Convert(numeric(14," + userInfo.SelPoint + "),a.StandardSell),0)as StandardSell"); searchSql.AppendLine(" ,isnull(Convert(numeric(14," + userInfo.SelPoint + "),a.SellTax),0)as SellTax "); searchSql.AppendLine(" ,isnull(Convert(numeric(14," + userInfo.SelPoint + "),a.StandardSellNew),0)as StandardSellNew "); searchSql.AppendLine(" ,isnull(Convert(numeric(14," + userInfo.SelPoint + "),a.TaxRateNew),0)as TaxRateNew "); searchSql.AppendLine(" ,isnull(Convert(numeric(12," + userInfo.SelPoint + "),a.DiscountNew),0)as DiscountNew "); searchSql.AppendLine(" ,isnull(Convert(numeric(12," + userInfo.SelPoint + "),a.Discount),0)as Discount "); searchSql.AppendLine(" ,isnull(Convert(numeric(14," + userInfo.SelPoint + "),a.TaxRate),0)as TaxRate "); searchSql.AppendLine(" ,isnull(Convert(numeric(14," + userInfo.SelPoint + "),a.SellTaxNew),0) as SellTaxNew "); searchSql.AppendLine(" ,isnull( CONVERT(CHAR(19), a.ChangeDate, 120),'') as ChangeDate "); searchSql.AppendLine(" ,isnull(a.Chenger,'')as Chenger "); searchSql.AppendLine(" ,isnull(a.Remark,'')as Remark "); searchSql.AppendLine(" ,isnull(a.BillStatus,'') as BillStatus "); searchSql.AppendLine(" ,isnull(a.Creator,'')as Creator "); searchSql.AppendLine(" ,isnull( CONVERT(CHAR(19), a.CreateDate, 120),'') as CreateDate "); searchSql.AppendLine(" ,isnull(a.Confirmor,'')as Confirmor "); searchSql.AppendLine(" ,isnull( CONVERT(CHAR(19), a.ConfirmDate, 120),'') as ConfirmDate "); searchSql.AppendLine(" ,isnull(b.ProductName,'')as ProductName "); searchSql.AppendLine(" ,isnull(c.EmployeeName,'') as ConfirmName"); searchSql.AppendLine(" FROM officedba.ProductPriceChange as a "); searchSql.AppendLine("left join officedba.ProductInfo as b on a.ProductID=b.ID and a.CompanyCD=b.CompanyCD "); searchSql.AppendLine("left join officedba.EmployeeInfo as c on a.Confirmor=c.ID and a.CompanyCD=c.CompanyCD "); searchSql.AppendLine(" where a.CompanyCD=@CompanyCD "); //#endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); ////公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //编号 if (!string.IsNullOrEmpty(model.ChangeNo)) { searchSql.AppendLine(" and a.ChangeNo LIKE @ChangeNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeNo", "%" + model.ChangeNo + "%")); } //名称 if (!string.IsNullOrEmpty(model.Title)) { searchSql.AppendLine(" AND a.Title LIKE @Title "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + model.Title + "%")); } if (!string.IsNullOrEmpty(model.ProductID)) { string ID = model.ProductID; string allID = ""; System.Text.StringBuilder sb = new System.Text.StringBuilder(); string[] IdS = null; ID = ID.Substring(0, ID.Length); IdS = ID.Split(','); for (int i = 0; i < IdS.Length; i++) { IdS[i] = "'" + IdS[i] + "'"; sb.Append(IdS[i]); } allID = sb.ToString().Replace("''", "','"); searchSql.AppendLine(" AND a.ProductID in ("+ allID + ") "); } if (!string.IsNullOrEmpty(model.Chenger)) { string ID = model.Chenger; string allID = ""; System.Text.StringBuilder sb = new System.Text.StringBuilder(); string[] IdS = null; ID = ID.Substring(0, ID.Length); IdS = ID.Split(','); for (int i = 0; i < IdS.Length; i++) { IdS[i] = "'" + IdS[i] + "'"; sb.Append(IdS[i]); } allID = sb.ToString().Replace("''", "','"); searchSql.AppendLine(" AND a.Chenger in ("+ allID + ") "); } //时间 if (!string.IsNullOrEmpty(starttime)) { searchSql.AppendLine(" AND a.ChangeDate >= @starttime "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@starttime", starttime)); } if (!string.IsNullOrEmpty(endtime)) { searchSql.AppendLine(" AND a.ChangeDate<=@endtime"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@endtime", endtime)); } comm.CommandText = searchSql.ToString(); return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount)); }
/// <summary> /// 插入物品档案信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertProductPriceInfo(ProductPriceChangeModel model, out string ID) { //SQL拼写 StringBuilder sql = new StringBuilder(); sql.AppendLine("INSERT INTO officedba.ProductPriceChange"); sql.AppendLine(" (ChangeNo "); sql.AppendLine(" ,CompanyCD "); sql.AppendLine(" ,Title "); sql.AppendLine(" ,ProductID "); sql.AppendLine(" ,StandardSell "); sql.AppendLine(" ,SellTax "); sql.AppendLine(" ,StandardSellNew "); sql.AppendLine(" ,SellTaxNew "); sql.AppendLine(" ,ChangeDate "); sql.AppendLine(" ,Chenger "); sql.AppendLine(" ,Remark "); sql.AppendLine(" ,BillStatus "); sql.AppendLine(" ,Creator "); sql.AppendLine(" ,CreateDate "); sql.AppendLine(" ,Confirmor "); sql.AppendLine(" ,ModifiedDate "); sql.AppendLine(" ,TaxRate "); sql.AppendLine(" ,Discount "); sql.AppendLine(" ,TaxRateNew "); sql.AppendLine(" ,DiscountNew "); sql.AppendLine(" ,ModifiedUserID) "); sql.AppendLine(" VALUES "); sql.AppendLine(" (@ChangeNo "); sql.AppendLine(" ,@CompanyCD "); sql.AppendLine(" ,@Title "); sql.AppendLine(" ,@ProductID "); sql.AppendLine(" ,@StandardSell "); sql.AppendLine(" ,@SellTax "); sql.AppendLine(" ,@StandardSellNew "); sql.AppendLine(" ,@SellTaxNew "); sql.AppendLine(" ,@ChangeDate "); sql.AppendLine(" ,@Chenger "); sql.AppendLine(" ,@Remark "); sql.AppendLine(" ,@BillStatus "); sql.AppendLine(" ,@Creator "); sql.AppendLine(" ,@CreateDate "); sql.AppendLine(" ,@Confirmor "); sql.AppendLine(" ,@ModifiedDate "); sql.AppendLine(" ,@TaxRate "); sql.AppendLine(" ,@Discount "); sql.AppendLine(" ,@TaxRateNew "); sql.AppendLine(" ,@DiscountNew "); sql.AppendLine(" ,@ModifiedUserID ) "); sql.AppendLine(" SET @ID= @@IDENTITY "); //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); //设置存储过程名 comm.CommandText = sql.ToString(); //设置保存的参数 SetSaveParameter(comm, model, false); //添加返回参数 comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); //执行登陆操作 bool isSucc = SqlHelper.ExecuteTransWithCommand(comm); //设置ID //model.ID = int.Parse(comm.Parameters["@ProdID"].Value); ID = comm.Parameters["@ID"].Value.ToString(); return(isSucc); }