/// <summary>
/// 查询总行数
/// </summary>
/// <returns></returns>
public static int SelectCount(SerchElement serchElement)
{
string sql = @"SELECT COUNT(1)
FROM Configuration.dbo.ThirdPartyCodeBatchConfig WITH ( NOLOCK )
WHERE ( @BatchGuid IS NULL--批次ID
OR @BatchGuid = BatchGuid
)
AND ( @BatchName IS NULL--批次名称
OR BatchName LIKE '%' + @BatchName + '%'
)
AND ( @Creator IS NULL--创建人
OR Creator LIKE '%' + @Creator + '%'
)
AND ( @Modifier IS NULL--最近更新人
OR Modifier LIKE '%' + @Modifier + '%'
);";
var sqlParameters = new[]
{
new SqlParameter("@PageSize", serchElement.PageSize),
new SqlParameter("@PageNumber", serchElement.PageNumber),
new SqlParameter("@BatchGuid", serchElement.BatchGuid),
new SqlParameter("@Instructions", serchElement.Instructions),
new SqlParameter("@BatchName", serchElement.BatchName),
new SqlParameter("@Creator", serchElement.Creator),
new SqlParameter("@Modifier", serchElement.Modifier)
};
return(Convert.ToInt32(SqlHelper.ExecuteScalar(Connection, CommandType.Text, sql, sqlParameters)));
}
/// <summary>
/// 搜索兑换码批次信息
/// </summary>
/// <returns></returns>
public static List <ThirdPartyCodeBatch> SelectBatches(SerchElement serchElement)
{
try
{
return(DalThirdPartyExchangeCode.SelectBatches(serchElement));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new ThirdPartyExchangerCodeException(1, "SelectBatches", ex);
Logger.Log(Level.Error, exception, "SelectBatches");
throw ex;
}
}
public ActionResult SelectCout(int pageSize, int pageNumber, int sort, string branchId, string branchName,
string user, string call)
{
SerchElement serch = new SerchElement()
{
PageNumber = pageNumber,
PageSize = pageSize,
BatchName = branchName
};
if (!string.IsNullOrWhiteSpace(branchId))
{
serch.BatchGuid = new Guid(branchId);
}
serch.Creator = user;
serch.Modifier = call;
serch.Sort = sort;
//serch.Status = select;
var result = ThirdPartyExchangeCodeManage.SelectCount(serch);
return(Json(new { msg = result }));;
}
/// <summary>
/// 搜索兑换码批次信息
/// </summary>
/// <returns></returns>
public static List <ThirdPartyCodeBatch> SelectBatches(SerchElement serchElement)
{
string sql1 = @"SELECT PKID ,
BatchGuid ,
BatchName ,
LimitQty ,
BatchQty ,
StockQty ,
StartDateTime ,
EndDateTime ,
Creator ,
CreateDateTime ,
Modifier ,
UpdateDateTime
FROM ( SELECT PKID ,BatchGuid ,
BatchName ,
LimitQty ,
BatchQty ,
StockQty ,
StartDateTime ,
EndDateTime ,
Creator ,
CreateDateTime ,
Modifier ,
UpdateDateTime ,
";
string sql2 = @"FROM Configuration.dbo.ThirdPartyCodeBatchConfig WITH ( NOLOCK )
WHERE ( @BatchGuid IS NULL--批次ID
OR @BatchGuid = BatchGuid
)
AND ( @BatchName IS NULL--批次名称
OR BatchName LIKE '%' + @BatchName + '%'
)
AND ( @Creator IS NULL--创建人
OR Creator LIKE '%' + @Creator + '%'
)
AND ( @Modifier IS NULL--最近更新人
OR Modifier LIKE '%' + @Modifier + '%'
)
) AS T
WHERE T.RowNumber BETWEEN ( ( @PageNumber - 1 ) * @PageSize + 1 )
AND ( @PageNumber * @PageSize ) ;";
string sqlSort = @" ROW_NUMBER() OVER ( ORDER BY UpdateDateTime DESC ) AS RowNumber ";
#region 排序方式
switch (serchElement.Sort)
{
case 10: //批次数量升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY BatchQty ) AS RowNumber ";
break;
case 11: //批次数量降序
sqlSort = @"ROW_NUMBER() OVER ( ORDER BY BatchQty DESC ) AS RowNumber ";
break;
case 20: //库存数量升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY StockQty ) AS RowNumber ";
break;
case 21: //库存数量降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY StockQty DESC ) AS RowNumber ";
break;
case 30: //兑换开始日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY StartDateTime ) AS RowNumber ";
break;
case 31: //兑换开始日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY StartDateTime DESC ) AS RowNumber ";
break;
case 40: //兑换结束日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY EndDateTime ) AS RowNumber ";
break;
case 41: //兑换结束日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY EndDateTime DESC) AS RowNumber ";
break;
case 50: //创建日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY CreateDateTime ) AS RowNumber ";
break;
case 51: //创建日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY CreateDateTime DESC ) AS RowNumber ";
break;
case 60: //更新日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY UpdateDateTime ) AS RowNumber ";
break;
case 61: //更新日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY UpdateDateTime DESC) AS RowNumber ";
break;
}
#endregion
var sqlParameters = new[]
{
new SqlParameter("@PageSize", serchElement.PageSize),
new SqlParameter("@PageNumber", serchElement.PageNumber),
new SqlParameter("@BatchGuid", serchElement.BatchGuid),
new SqlParameter("@Instructions", serchElement.Instructions),
new SqlParameter("@BatchName", serchElement.BatchName),
new SqlParameter("@Creator", serchElement.Creator),
new SqlParameter("@Modifier", serchElement.Modifier)
};
return
(SqlHelper.ExecuteDataTable(Connfig, CommandType.Text, sql1 + sqlSort + sql2, sqlParameters)
.ConvertTo <ThirdPartyCodeBatch>()
.ToList());
}
