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);
}
