private void map(CouponRequestInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.CustomerSysNo = Util.TrimIntNull(tempdr["CustomerSysNo"]); oParam.CouponCode = Util.TrimNull(tempdr["CouponCode"]); oParam.RequestUserSysNo = Util.TrimIntNull(tempdr["RequestUserSysNo"]); oParam.RequestTime = Util.TrimDateNull(tempdr["RequestTime"]); oParam.AuditUserSysNo = Util.TrimIntNull(tempdr["AuditUserSysNo"]); oParam.AuditTime = Util.TrimDateNull(tempdr["AuditTime"]); oParam.SOSysNo = Util.TrimIntNull(tempdr["SOSysNo"]); oParam.BatchNo = Util.TrimIntNull(tempdr["BatchNo"]); oParam.Note = Util.TrimNull(tempdr["Note"]); oParam.Status = Util.TrimIntNull(tempdr["Status"]); oParam.EXECSql = Util.TrimNull(tempdr["EXECSql"]); }
/// <summary> /// 批量申请优惠券 /// </summary> /// <param name="batchNo">批次号</param> /// <param name="alist">申请优惠券的CustomerSysNo列表</param> /// <param name="note">备注</param> /// <param name="requestUserSysNo">申请操作人</param> /// <param name="execsqlstr">查询批量客户的sql语句,没有传"",且一批客户中数据库只存一次execsql</param> public void RequestCouponBatch(int batchNo, ArrayList alist, string note, int requestUserSysNo,string execsqlstr) { int count = alist.Count; if (count <= 0) throw new BizException("没有需要申请优惠券的客户"); string sql = @" SELECT TOP " + count + @" Coupon.CouponCode FROM Coupon WHERE Coupon.Status = " + (int)AppEnum.CouponStatus.Activation + @" AND Coupon.UseCustomerSysNo IS NULL AND Coupon.BatchNo = " + batchNo + @" AND NOT EXISTS ( SELECT CouponRequest.CouponCode FROM CouponRequest WHERE CouponRequest.CouponCode = Coupon.CouponCode AND CouponRequest.Status <> " + (int)AppEnum.CouponRequestStatus.Abandon + @" ) ORDER BY Coupon.SysNo "; DataSet ds = SqlHelper.ExecuteDataSet(sql); if (!Util.HasMoreRow(ds)) throw new BizException("当前批次没有可以使用的优惠券"); int couponcount = ds.Tables[0].Rows.Count; if (couponcount < count) throw new BizException("当前申请优惠券的数量是 " + count + " 张,但系统只有 " + couponcount + " 张可以使用,请先补充该批次可以使用的优惠券" + (count - couponcount) + " 张后再操作"); TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { for (int i = 0; i < count; i++) { CouponRequestInfo oInfo = new CouponRequestInfo(); oInfo.CustomerSysNo = Util.TrimIntNull(alist[i].ToString()); oInfo.CouponCode = ds.Tables[0].Rows[i][0].ToString(); oInfo.RequestUserSysNo = requestUserSysNo; oInfo.RequestTime = DateTime.Now; oInfo.BatchNo = batchNo; oInfo.Note = note; oInfo.Status = (int)AppEnum.CouponRequestStatus.Origin; if (i == 0) oInfo.EXECSql = execsqlstr; CouponManager.GetInstance().InsertCouponRequest(oInfo); } scope.Complete(); } }
public void UpdateCouponRequest(CouponRequestInfo paramInfo) { if (1 != new CouponRequestDac().Update(paramInfo)) throw new BizException("更新客户优惠券申请记录失败"); }
/// <summary> /// 单个申请优惠券 /// </summary> /// <param name="batchNo"></param> /// <param name="customerSysNo"></param> /// <param name="soSysNo"></param> /// <param name="?"></param> public void RequestCoupon(int batchNo, int customerSysNo, int requestUserSysNo, int soSysNo, string note) { CouponRequestInfo oInfo = new CouponRequestInfo(); oInfo.CustomerSysNo = customerSysNo; string couponcode = RequestCouponCode(batchNo); if (couponcode == "") throw new BizException("获取优惠券密码失败"); oInfo.CouponCode = couponcode; oInfo.RequestUserSysNo = requestUserSysNo; oInfo.RequestTime = DateTime.Now; oInfo.BatchNo = batchNo; oInfo.SOSysNo = soSysNo; oInfo.Note = note; oInfo.Status = (int)AppEnum.CouponRequestStatus.Origin; InsertCouponRequest(oInfo); }
public CouponRequestInfo LoadCouponRequest(int SysNo) { string sql = "select * from CouponRequest where sysno = " + SysNo; DataSet ds = SqlHelper.ExecuteDataSet(sql); if (!Util.HasMoreRow(ds)) return null; CouponRequestInfo oInfo = new CouponRequestInfo(); map(oInfo, ds.Tables[0].Rows[0]); return oInfo; }
public void InsertCouponRequest(CouponRequestInfo paramInfo) { if (1 != new CouponRequestDac().Insert(paramInfo)) throw new BizException("插入客户优惠券申请记录失败"); }
public int Insert(CouponRequestInfo oParam) { string sql = @"INSERT INTO CouponRequest ( CustomerSysNo, CouponCode, RequestUserSysNo, RequestTime, AuditUserSysNo, AuditTime, SOSysNo, BatchNo, Note, Status, EXECSql ) VALUES ( @CustomerSysNo, @CouponCode, @RequestUserSysNo, @RequestTime, @AuditUserSysNo, @AuditTime, @SOSysNo, @BatchNo, @Note, @Status, @EXECSql );set @SysNo = SCOPE_IDENTITY();"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4); SqlParameter paramCustomerSysNo = new SqlParameter("@CustomerSysNo", SqlDbType.Int, 4); SqlParameter paramCouponCode = new SqlParameter("@CouponCode", SqlDbType.NVarChar, 20); SqlParameter paramRequestUserSysNo = new SqlParameter("@RequestUserSysNo", SqlDbType.Int, 4); SqlParameter paramRequestTime = new SqlParameter("@RequestTime", SqlDbType.DateTime); SqlParameter paramAuditUserSysNo = new SqlParameter("@AuditUserSysNo", SqlDbType.Int, 4); SqlParameter paramAuditTime = new SqlParameter("@AuditTime", SqlDbType.DateTime); SqlParameter paramSOSysNo = new SqlParameter("@SOSysNo", SqlDbType.Int, 4); SqlParameter paramBatchNo = new SqlParameter("@BatchNo", SqlDbType.Int, 4); SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar, 200); SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4); SqlParameter paramEXECSql = new SqlParameter("@EXECSql", SqlDbType.Text, 0); paramSysNo.Direction = ParameterDirection.Output; if (oParam.CustomerSysNo != AppConst.IntNull) paramCustomerSysNo.Value = oParam.CustomerSysNo; else paramCustomerSysNo.Value = System.DBNull.Value; if (oParam.CouponCode != AppConst.StringNull) paramCouponCode.Value = oParam.CouponCode; else paramCouponCode.Value = System.DBNull.Value; if (oParam.RequestUserSysNo != AppConst.IntNull) paramRequestUserSysNo.Value = oParam.RequestUserSysNo; else paramRequestUserSysNo.Value = System.DBNull.Value; if (oParam.RequestTime != AppConst.DateTimeNull) paramRequestTime.Value = oParam.RequestTime; else paramRequestTime.Value = System.DBNull.Value; if (oParam.AuditUserSysNo != AppConst.IntNull) paramAuditUserSysNo.Value = oParam.AuditUserSysNo; else paramAuditUserSysNo.Value = System.DBNull.Value; if (oParam.AuditTime != AppConst.DateTimeNull) paramAuditTime.Value = oParam.AuditTime; else paramAuditTime.Value = System.DBNull.Value; if (oParam.SOSysNo != AppConst.IntNull) paramSOSysNo.Value = oParam.SOSysNo; else paramSOSysNo.Value = System.DBNull.Value; if (oParam.BatchNo != AppConst.IntNull) paramBatchNo.Value = oParam.BatchNo; else paramBatchNo.Value = System.DBNull.Value; if (oParam.Note != AppConst.StringNull) paramNote.Value = oParam.Note; else paramNote.Value = System.DBNull.Value; if (oParam.Status != AppConst.IntNull) paramStatus.Value = oParam.Status; else paramStatus.Value = System.DBNull.Value; if (oParam.EXECSql != AppConst.StringNull) paramEXECSql.Value = oParam.EXECSql; else paramEXECSql.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramCustomerSysNo); cmd.Parameters.Add(paramCouponCode); cmd.Parameters.Add(paramRequestUserSysNo); cmd.Parameters.Add(paramRequestTime); cmd.Parameters.Add(paramAuditUserSysNo); cmd.Parameters.Add(paramAuditTime); cmd.Parameters.Add(paramSOSysNo); cmd.Parameters.Add(paramBatchNo); cmd.Parameters.Add(paramNote); cmd.Parameters.Add(paramStatus); cmd.Parameters.Add(paramEXECSql); return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo); }
public int Update(CouponRequestInfo oParam) { string sql = @"UPDATE CouponRequest SET CustomerSysNo=@CustomerSysNo, CouponCode=@CouponCode, RequestUserSysNo=@RequestUserSysNo, RequestTime=@RequestTime, AuditUserSysNo=@AuditUserSysNo, AuditTime=@AuditTime, SOSysNo=@SOSysNo, BatchNo=@BatchNo, Note=@Note, Status=@Status, EXECSql=@EXECSql WHERE SysNo=@SysNo"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4); SqlParameter paramCustomerSysNo = new SqlParameter("@CustomerSysNo", SqlDbType.Int, 4); SqlParameter paramCouponCode = new SqlParameter("@CouponCode", SqlDbType.NVarChar, 20); SqlParameter paramRequestUserSysNo = new SqlParameter("@RequestUserSysNo", SqlDbType.Int, 4); SqlParameter paramRequestTime = new SqlParameter("@RequestTime", SqlDbType.DateTime); SqlParameter paramAuditUserSysNo = new SqlParameter("@AuditUserSysNo", SqlDbType.Int, 4); SqlParameter paramAuditTime = new SqlParameter("@AuditTime", SqlDbType.DateTime); SqlParameter paramSOSysNo = new SqlParameter("@SOSysNo", SqlDbType.Int, 4); SqlParameter paramBatchNo = new SqlParameter("@BatchNo", SqlDbType.Int, 4); SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar, 200); SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4); SqlParameter paramEXECSql = new SqlParameter("@EXECSql", SqlDbType.Text, 0); if (oParam.SysNo != AppConst.IntNull) paramSysNo.Value = oParam.SysNo; else paramSysNo.Value = System.DBNull.Value; if (oParam.CustomerSysNo != AppConst.IntNull) paramCustomerSysNo.Value = oParam.CustomerSysNo; else paramCustomerSysNo.Value = System.DBNull.Value; if (oParam.CouponCode != AppConst.StringNull) paramCouponCode.Value = oParam.CouponCode; else paramCouponCode.Value = System.DBNull.Value; if (oParam.RequestUserSysNo != AppConst.IntNull) paramRequestUserSysNo.Value = oParam.RequestUserSysNo; else paramRequestUserSysNo.Value = System.DBNull.Value; if (oParam.RequestTime != AppConst.DateTimeNull) paramRequestTime.Value = oParam.RequestTime; else paramRequestTime.Value = System.DBNull.Value; if (oParam.AuditUserSysNo != AppConst.IntNull) paramAuditUserSysNo.Value = oParam.AuditUserSysNo; else paramAuditUserSysNo.Value = System.DBNull.Value; if (oParam.AuditTime != AppConst.DateTimeNull) paramAuditTime.Value = oParam.AuditTime; else paramAuditTime.Value = System.DBNull.Value; if (oParam.SOSysNo != AppConst.IntNull) paramSOSysNo.Value = oParam.SOSysNo; else paramSOSysNo.Value = System.DBNull.Value; if (oParam.BatchNo != AppConst.IntNull) paramBatchNo.Value = oParam.BatchNo; else paramBatchNo.Value = System.DBNull.Value; if (oParam.Note != AppConst.StringNull) paramNote.Value = oParam.Note; else paramNote.Value = System.DBNull.Value; if (oParam.Status != AppConst.IntNull) paramStatus.Value = oParam.Status; else paramStatus.Value = System.DBNull.Value; if (oParam.EXECSql != AppConst.StringNull) paramEXECSql.Value = oParam.EXECSql; else paramEXECSql.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramCustomerSysNo); cmd.Parameters.Add(paramCouponCode); cmd.Parameters.Add(paramRequestUserSysNo); cmd.Parameters.Add(paramRequestTime); cmd.Parameters.Add(paramAuditUserSysNo); cmd.Parameters.Add(paramAuditTime); cmd.Parameters.Add(paramSOSysNo); cmd.Parameters.Add(paramBatchNo); cmd.Parameters.Add(paramNote); cmd.Parameters.Add(paramStatus); cmd.Parameters.Add(paramEXECSql); return SqlHelper.ExecuteNonQuery(cmd); }