/// <summary>
/// 分页获取兑换码配置
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public Tuple <int, List <RedemptionConfig> > GetRedemptionCodeConfigs(SearchRedemptionConfigRequest request)
{
List <RedemptionConfig> list = null;
int total = 0;
try
{
var result = DbTuhuGrouponScopeReadManager.Execute(conn => DALUnivRedemptionCode.SelectRedemptionCodeConfigs(conn, request));
total = result.Item1;
list = result.Item2;
}
catch (Exception ex)
{
Logger.Error(nameof(GetRedemptionCodeConfigs), ex);
}
return(Tuple.Create(total, list ?? new List <RedemptionConfig>()));
}
/// <summary>
/// 分页获取
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ActionResult GetRedemptionCodeConfigs(SearchRedemptionConfigRequest request)
{
request = request ?? new SearchRedemptionConfigRequest();
GenerateType type;
if (!Enum.TryParse(request.GenerateType, out type) || type == GenerateType.None)
{
return(Json(new
{
status = false,
data = new List <RedemptionConfig>(),
total = 0,
msg = "查询参数有误"
}, JsonRequestBehavior.AllowGet));
}
request.GenerateType = type.ToString();
request.PageIndex = request.PageIndex < 1 ? 1 : request.PageIndex;
request.PageSize = request.PageSize < 1 ? 1 : request.PageSize;
var result = manager.GetRedemptionCodeConfigs(request);
return(Json(new { status = true, data = result.Item2, total = result.Item1 }, JsonRequestBehavior.AllowGet));
}
public ActionResult GetRedemptionCodeRecords(SearchRedemptionConfigRequest request)
{
request = request ?? new SearchRedemptionConfigRequest();
request.PageIndex = request.PageIndex <= 0 ? 1 : request.PageIndex;
request.PageSize = request.PageSize <= 0 ? 10 : request.PageSize;
switch (request.SettlementMethod?.ToLower())
{
case "all":
request.SettlementMethod = null;
break;
case "none":
request.SettlementMethod = string.Empty;
break;
case "batchpresettled":
request.SettlementMethod = BATCH_PRESETTLED;
break;
}
var result = manager.GetRedemptionCodeRecords(request);
var total = result.Item1;
var data = result.Item2?.Select(x => new
{
x.ConfigId,
x.Name,
x.Num,
x.RedemptionConfigId,
x.CreateUser,
x.BatchCode,
x.CooperateName,
x.Status,
CreateTime = x.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
StartTime = x.CreateTime.ToString("yyyy-MM-dd"),
EndTime = x.CreateTime.AddDays(x.EffectiveDay).ToString("yyyy-MM-dd")
});
return(Json(new { status = data != null, total, data }, JsonRequestBehavior.AllowGet));
}
public Tuple <int, List <RedemptionCodeRecordResult> > GetRedemptionCodeRecords(SearchRedemptionConfigRequest request)
{
int total = 0;
List <RedemptionCodeRecordResult> list = null;
try
{
var result = DbTuhuGrouponScopeReadManager.Execute(conn => DALUnivRedemptionCode.SelectRedemptionCodeRecords(conn, request));
var batchCodes = result.Item2?.Select(t => t.BatchCode);
if (batchCodes != null && batchCodes.Any())
{
var batchCodeStatus = DbTuhuGrouponScopeReadManager.Execute(conn =>
DALUnivRedemptionCode.SelectRedemptionBatchStatus(conn, batchCodes, new { BatchCode = "", Status = "" }));
if (batchCodeStatus != null && batchCodeStatus.Any())
{
result.Item2.ForEach(b =>
{
b.Status = batchCodeStatus.FirstOrDefault(s => s.BatchCode == b.BatchCode)?.Status;
});
}
}
total = result.Item1;
list = result.Item2?.ToList();
}
catch (Exception ex)
{
Logger.Error(ex.Message, ex);
}
return(Tuple.Create(total, list ?? new List <RedemptionCodeRecordResult>()));
}
public static Tuple <int, IEnumerable <RedemptionCodeRecordResult> > SelectRedemptionCodeRecords(
SqlConnection conn, SearchRedemptionConfigRequest request)
{
const string sql = @"SELECT @Total = COUNT(1)
FROM ( SELECT t.RedemptionConfigId ,
t.BatchCode ,
t.CreateUser ,
t.CreateTime ,
COUNT(1) AS Num
FROM Tuhu_groupon..RedemptionCodeRecord AS t WITH ( NOLOCK )
GROUP BY t.RedemptionConfigId ,
t.BatchCode ,
t.CreateUser ,
t.CreateTime
) AS a
INNER JOIN Tuhu_groupon..RedemptionConfig AS b WITH ( NOLOCK ) ON a.RedemptionConfigId = b.ConfigId
INNER JOIN Tuhu_groupon..MrCooperateUserConfig AS mcuc WITH ( NOLOCK ) ON mcuc.PKID = b.CooperateId
WHERE b.GenerateType = @GenerateType
AND ( @SettlementMethod IS NULL
OR b.SettlementMethod = @SettlementMethod
)
AND ( @CooperateId <= 0
OR b.CooperateId = @CooperateId
)
AND ( @CodeTypeConfigId <= 0
OR EXISTS ( SELECT 1
FROM Tuhu_groupon..RedeemMrCodeConfig AS mr
WITH ( NOLOCK )
WHERE mr.CodeTypeConfigId = @CodeTypeConfigId
AND b.ConfigId = mr.RedemptionConfigId )
);
SELECT a.RedemptionConfigId ,
a.BatchCode ,
a.CreateUser ,
a.CreateTime ,
a.Num ,
b.CooperateId ,
b.Name ,
mcuc.CooperateName ,
b.EffectiveDay
FROM ( SELECT t.RedemptionConfigId ,
t.BatchCode ,
t.CreateUser ,
t.CreateTime ,
COUNT(1) AS Num
FROM Tuhu_groupon..RedemptionCodeRecord AS t WITH ( NOLOCK )
GROUP BY t.RedemptionConfigId ,
t.BatchCode ,
t.CreateUser ,
t.CreateTime
) AS a
INNER JOIN Tuhu_groupon..RedemptionConfig AS b WITH ( NOLOCK ) ON a.RedemptionConfigId = b.ConfigId
INNER JOIN Tuhu_groupon..MrCooperateUserConfig AS mcuc WITH ( NOLOCK ) ON mcuc.PKID = b.CooperateId
WHERE GenerateType = @GenerateType
AND ( @SettlementMethod IS NULL
OR b.SettlementMethod = @SettlementMethod
)
AND ( @CooperateId <= 0
OR b.CooperateId = @CooperateId
)
AND ( @CodeTypeConfigId <= 0
OR EXISTS ( SELECT 1
FROM Tuhu_groupon..RedeemMrCodeConfig AS mr
WITH ( NOLOCK )
WHERE mr.CodeTypeConfigId = @CodeTypeConfigId
AND b.ConfigId = mr.RedemptionConfigId )
)
ORDER BY a.CreateTime DESC
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS
ONLY;";
var parameters = new DynamicParameters();
parameters.Add("@GenerateType", request.GenerateType);
parameters.Add("@CooperateId", request.CooperateId);
parameters.Add("@CodeTypeConfigId", request.CodeTypeConfigId);
parameters.Add("@SettlementMethod", request.SettlementMethod);
parameters.Add("@PageIndex", request.PageIndex);
parameters.Add("@PageSize", request.PageSize);
parameters.Add("@Total", dbType: DbType.Int32, direction: ParameterDirection.Output);
var list = conn.Query <RedemptionCodeRecordResult>(sql, parameters, commandType: CommandType.Text);
var total = parameters.Get <int>("@Total");
return(Tuple.Create(total, list));
}
/// <summary>
/// 分页获取配置
/// </summary>
/// <param name="conn"></param>
/// <param name="request"></param>
/// <returns></returns>
public static Tuple <int, List <RedemptionConfig> > SelectRedemptionCodeConfigs(SqlConnection conn, SearchRedemptionConfigRequest request)
{
var sql = @"SELECT @Total = COUNT(1)
FROM Tuhu_groupon..RedemptionConfig AS rc WITH ( NOLOCK )
INNER JOIN Tuhu_groupon..MrCooperateUserConfig AS mcuc WITH ( NOLOCK ) ON rc.CooperateId = mcuc.PKID
WHERE rc.GenerateType = @GenerateType
AND ( @CooperateId <= 0
OR mcuc.PKID = @CooperateId
)
AND ( CASE WHEN @SettlementMethodType = 0 THEN 1
WHEN @SettlementMethodType = 1
AND ( rc.SettlementMethod IS NULL
OR rc.SettlementMethod = N''
) THEN 1
WHEN @SettlementMethodType = 2
AND rc.SettlementMethod = N'BatchPreSettled' THEN 1
ELSE 0
END ) = 1
AND ( @CodeTypeConfigId <= 0
OR EXISTS ( SELECT 1
FROM Tuhu_groupon..RedeemMrCodeConfig AS rmcc
WITH ( NOLOCK )
WHERE rmcc.CodeTypeConfigId = @CodeTypeConfigId
AND rc.ConfigId = rmcc.RedemptionConfigId )
);
SELECT rc.ConfigId ,
mcuc.CooperateName ,
rc.Name ,
rc.EffectiveDay ,
rc.AtLeastNum ,
rc.SettlementMethod ,
rc.GenerateType ,
rc.Description ,
rc.IsActive ,
rc.CreateUser ,
( SELECT COUNT(1)
FROM Tuhu_groupon..RedemptionCodeRecord AS rcr WITH ( NOLOCK )
WHERE rcr.RedemptionConfigId = rc.ConfigId
) AS SumQuantity ,
rc.AuditStatus ,
rc.Auditor,
rc.GroupId
FROM Tuhu_groupon..RedemptionConfig AS rc WITH ( NOLOCK )
INNER JOIN Tuhu_groupon..MrCooperateUserConfig AS mcuc WITH ( NOLOCK ) ON rc.CooperateId = mcuc.PKID
WHERE rc.GenerateType = @GenerateType
AND ( @CooperateId <= 0
OR mcuc.PKID = @CooperateId
)
AND ( CASE WHEN @SettlementMethodType = 0 THEN 1
WHEN @SettlementMethodType = 1
AND ( rc.SettlementMethod IS NULL
OR rc.SettlementMethod = N''
) THEN 1
WHEN @SettlementMethodType = 2
AND rc.SettlementMethod = N'BatchPreSettled' THEN 1
ELSE 0
END ) = 1
AND ( @CodeTypeConfigId <= 0
OR EXISTS ( SELECT 1
FROM Tuhu_groupon..RedeemMrCodeConfig AS rmcc
WITH ( NOLOCK )
WHERE rmcc.CodeTypeConfigId = @CodeTypeConfigId
AND rc.ConfigId = rmcc.RedemptionConfigId )
)
ORDER BY rc.PKID DESC
OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
var parameters = new DynamicParameters();
parameters.Add("@CooperateId", request.CooperateId);
parameters.Add("@CodeTypeConfigId", request.CodeTypeConfigId);
parameters.Add("@SettlementMethodType", request.SettlementMethodType);
parameters.Add("@GenerateType", request.GenerateType);
parameters.Add("@Total", dbType: DbType.Int32, direction: ParameterDirection.Output);
parameters.Add("@Skip", (request.PageIndex - 1) * request.PageSize);
parameters.Add("@Take", request.PageSize);
var result = conn.Query <RedemptionConfig>(sql, parameters,
commandType: CommandType.Text)?.ToList() ?? new List <RedemptionConfig>();
var total = parameters.Get <int>("@Total");
return(Tuple.Create(total, result));
}