/// <summary> /// 销售汇报 插入主表信息 /// </summary> /// <param name="sellrptModel"></param> /// <param name="tran"></param> private static int InsertSellReport(SellReportModel sellrptModel, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine("insert into officedba.SellReport("); strSql.AppendLine("CompanyCD,SellDept,productID,productName,price,sellNum,sellPrice,createdate,memo)"); strSql.AppendLine(" values ("); strSql.AppendLine("@CompanyCD,@SellDept,@productID,@productName,@price,@sellNum,@sellPrice,@createdate,@memo)"); strSql.AppendLine(";set @Id=@@IDENTITY"); SqlParameter[] param = { new SqlParameter("@CompanyCD",sellrptModel.CompanyCD), new SqlParameter("@SellDept",sellrptModel.SellDept), new SqlParameter("@productID",sellrptModel.ProductID), new SqlParameter("@productName",sellrptModel.ProductName), new SqlParameter("@price",sellrptModel.Price), new SqlParameter("@sellNum",sellrptModel.SellNum), new SqlParameter("@sellPrice",sellrptModel.SellPrice), new SqlParameter("@createdate",sellrptModel.CreateDate), new SqlParameter("@memo",sellrptModel.Memo), new SqlParameter("@Id",SqlDbType.Int,6) }; param[9].Direction = ParameterDirection.Output; foreach (SqlParameter para in param) { if (para.Value == null) { para.Value = DBNull.Value; } } SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); int Id = Convert.ToInt32(param[9].Value); return Id; }
public static bool Insert(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg) { bool isSucc = false;//是否添加成功 int billID = 0; strMsg = ""; TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { billID = InsertSellReport(sellrptModel, tran); InsertSellReportDetail(sellRptDetailModellList,billID, tran); tran.Commit(); isSucc = true; strMsg = "保存成功!|"+billID; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } return isSucc; }
/// <summary> /// 更新销售发汇报 /// </summary> /// <returns></returns> public static bool UpdateSellReport(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg) { return SellProductReportDBHepler.UpdateSellReport(sellrptModel, sellRptDetailModellList, out strMsg); }
/// <summary> /// 获取销售汇报列表 /// </summary> /// <param name="sellrptModel">sellrptModel实体</param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable GetSellRptList(SellReportModel sellrptModel, DateTime? CreateDate1, int pageIndex, int pageCount, string ord, ref int TotalCount) { return SellProductReportDBHepler.GetSellRptList(sellrptModel,CreateDate1, pageIndex, pageCount, ord, ref TotalCount); }
/// <summary> /// 获取销售汇报列表 /// </summary> /// <param name="sellrptModel">sellrptModel实体</param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable GetSellRptList(SellReportModel sellrptModel,DateTime? CreateDate1, int pageIndex, int pageCount, string ord, ref int TotalCount) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine(" select a.id,a.SellDept,a.productID,a.productName,"); strSql.AppendLine(" convert(decimal(22," + sellrptModel.SelPointLen + "),a.price) as price,"); strSql.AppendLine(" convert(decimal(22," + sellrptModel.SelPointLen + "),a.sellNum) as sellNum,"); strSql.AppendLine(" convert(decimal(22," + sellrptModel.SelPointLen + "),a.sellPrice) as sellPrice,"); strSql.AppendLine(" convert(varchar(10),a.createdate,120) as createdate,dbo.getSellerList(a.id," + int.Parse(sellrptModel.SelPointLen) + ") as SellerRate, "); strSql.AppendLine(" d.DeptName as SellDeptName"); strSql.AppendLine(" from officedba.SellReport a"); strSql.AppendLine(" left join officedba.DeptInfo d on d.ID=a.SellDept"); strSql.AppendLine(" where a.CompanyCD=@CompanyCD"); ArrayList arr = new ArrayList(); arr.Add(new SqlParameter("@CompanyCD", sellrptModel.CompanyCD)); if (!string.IsNullOrEmpty(sellrptModel.SellDept.ToString())) { strSql.AppendLine(" and a.SellDept=@DeptID"); arr.Add(new SqlParameter("@DeptID", sellrptModel.SellDept)); } if (!string.IsNullOrEmpty(sellrptModel.CreateDate.ToString())) { strSql.AppendLine(" and a.createdate>=@CreateDate"); arr.Add(new SqlParameter("@CreateDate", sellrptModel.CreateDate)); } if (!string.IsNullOrEmpty(CreateDate1.ToString())) { strSql.AppendLine(" and a.createdate<dateadd(day,1,@CreateDate1)"); arr.Add(new SqlParameter("@CreateDate1", CreateDate1)); } return SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref TotalCount); }
/// <summary> /// 更新主表数据 /// </summary> /// <param name="sellBackModel"></param> /// <param name="tran"></param> private static void UpdateSellReportMain(SellReportModel sellrptModel, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine("update officedba.SellReport set "); strSql.AppendLine("SellDept=@SellDept,"); strSql.AppendLine("productID=@productID,"); strSql.AppendLine("productName=@productName,"); strSql.AppendLine("price=@price,"); strSql.AppendLine("sellNum=@sellNum,"); strSql.AppendLine("sellPrice=@sellPrice,"); strSql.AppendLine("createdate=@createdate,"); strSql.AppendLine("memo=@memo "); strSql.AppendLine(" where CompanyCD=@CompanyCD and ID=@ID "); SqlParameter[] param = { new SqlParameter("@ID",sellrptModel.ID.ToString()), new SqlParameter("@CompanyCD",sellrptModel.CompanyCD), new SqlParameter("@SellDept",sellrptModel.SellDept), new SqlParameter("@productID",sellrptModel.ProductID), new SqlParameter("@productName",sellrptModel.ProductName), new SqlParameter("@price",sellrptModel.Price), new SqlParameter("@sellNum",sellrptModel.SellNum), new SqlParameter("@sellPrice",sellrptModel.SellPrice), new SqlParameter("@createdate",sellrptModel.CreateDate), new SqlParameter("@memo",sellrptModel.Memo) }; foreach (SqlParameter para in param) { if (para.Value == null) { para.Value = DBNull.Value; } } SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); }
/// <summary> /// 更新销售发汇报 /// </summary> /// <returns></returns> public static bool UpdateSellReport(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; string strSql = "delete from officedba.sellerRate where sellreportID=@sellreportID and CompanyCD=@CompanyCD"; SqlParameter[] paras = { new SqlParameter("@sellreportID", sellrptModel.ID), new SqlParameter("@CompanyCD", sellrptModel.CompanyCD) }; TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { UpdateSellReportMain(sellrptModel, tran); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras); InsertSellReportDetail(sellRptDetailModellList,sellrptModel.ID, tran); tran.Commit(); strMsg = "保存成功!"; isSucc = true; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } return isSucc; }