public JsonResult Select(int pageSize, int pageNumber, bool?isEnabled, int?visible, string branchName,
string batchGuid, int sort)
{
SerchThirdPartyMallModel serch = new SerchThirdPartyMallModel()
{
PageNumber = pageNumber,
PageSize = pageSize,
BatchName = branchName,
IsEnabled = isEnabled,
Visible = visible,
Sort = sort
};
if (!string.IsNullOrWhiteSpace(batchGuid))
{
serch.BatchGuid = new Guid(batchGuid);
}
else
{
serch.BatchGuid = null;
}
var list = ThirdPartyMallConfigManage.SelectThirdMall(serch);
return(Json(list));
}
/// <summary>
/// 根据总行数
/// </summary>
/// <param name="batchId"></param>
/// <returns></returns>
public static int SelectCout(SerchThirdPartyMallModel serchMall)
{
string sql1 = @" SELECT COUNT(1)
FROM ( SELECT a.PKID ,
a.IsEnabled ,
a.Sort ,
a.ImageUrl ,
a.Description ,
a.StartDateTime ,
a.EndDateTime ,
B.BatchGuid ,
B.BatchName ,
B.BatchQty ,
B.StockQty ,
B.LimitQty ,
B.Instructions ,
CASE WHEN ( a.StartDateTime < GETDATE()
AND a.EndDateTime > GETDATE()
AND B.StockQty > 0
) THEN 0
ELSE 1
END AS Visible
FROM Configuration.dbo.ThirdPartyMallConfig AS a WITH ( NOLOCK )
JOIN Configuration..ThirdPartyCodeBatchConfig AS B WITH ( NOLOCK ) ON B.BatchGuid = a.BatchGuid
WHERE ( @IsEnabled IS NULL
OR a.IsEnabled = @IsEnabled
)
AND ( @BatchName IS NULL
OR B.BatchName LIKE '%' + @BatchName + '%'
)
AND ( @BatchGuid IS NULL
OR B.BatchGuid = @BatchGuid
)
) T
WHERE (@Visible IS NULL
OR T.Visible = @Visible)
";
var sqlParameters = new[]
{
new SqlParameter("@IsEnabled", serchMall.IsEnabled),
new SqlParameter("@Visible", serchMall.Visible),
new SqlParameter("@BatchName", serchMall.BatchName),
new SqlParameter("@BatchGuid", serchMall.BatchGuid)
};
return((int)SqlHelper.ExecuteScalar(Connection, CommandType.Text, sql1, sqlParameters));
}
/// <summary>
/// 搜索三方商城记录
/// </summary>
/// <returns></returns>
public static List <ThirdPartyMallModel> SelectThirdMall(SerchThirdPartyMallModel serchMall)
{
try
{
return(DalThirdPartyMall.SelectThirdMall(serchMall));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new ThirdPartyMallConfigException(1, "SelectThirdMall", ex);
Logger.Log(Level.Error, exception, "SelectThirdMall");
throw ex;
}
}
/// <summary>
/// 搜索三方商城记录
/// </summary>
/// <returns></returns>
public static List <ThirdPartyMallModel> SelectThirdMall(SerchThirdPartyMallModel serchMall)
{
string sql1 = @"SELECT B.PKID ,B.UpdateDateTime,
B.IsEnabled ,
B.Sort ,
B.ImageUrl ,
B.Description ,
B.StartDateTime ,
B.EndDateTime ,
B.BatchGuid ,
B.BatchName ,
B.BatchQty ,
B.StockQty ,
B.LimitQty ,
B.Instructions ,
B.Visible
FROM ( SELECT T.PKID ,
T.IsEnabled ,
T.Sort ,
T.ImageUrl ,
T.Description ,
T.StartDateTime ,
T.EndDateTime ,
T.BatchGuid ,
T.BatchName ,
T.BatchQty ,
T.StockQty ,
T.LimitQty ,
T.Instructions ,T.UpdateDateTime,
T.Visible,
";
string sql2 = @" FROM ( SELECT a.PKID ,
a.IsEnabled ,
a.Sort ,
a.ImageUrl ,
a.Description ,
a.StartDateTime ,
a.EndDateTime ,
B.BatchGuid ,
B.BatchName ,
B.BatchQty ,
B.StockQty ,
B.LimitQty ,
B.Instructions ,a.UpdateDateTime,
CASE WHEN ( a.StartDateTime < GETDATE()
AND a.EndDateTime > GETDATE()
AND B.StockQty > 0
) THEN 0
ELSE 1
END AS Visible
FROM Configuration.dbo.ThirdPartyMallConfig AS a
WITH ( NOLOCK )
JOIN Configuration..ThirdPartyCodeBatchConfig
AS B WITH ( NOLOCK ) ON B.BatchGuid = a.BatchGuid
WHERE ( @IsEnabled IS NULL
OR a.IsEnabled = @IsEnabled
)
AND ( @BatchName IS NULL
OR B.BatchName LIKE '%' + @BatchName
+ '%'
)
AND ( @BatchGuid IS NULL
OR B.BatchGuid = @BatchGuid
)
) T
WHERE ( @Visible IS NULL
OR T.Visible = @Visible
)
) AS B
WHERE B.RowNumber BETWEEN ( ( @PageNumber - 1 ) * @PageSize + 1 )
AND ( @PageNumber * @PageSize );";
string sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.UpdateDateTime DESC ) AS RowNumber ";
#region 排序方式
switch (serchMall.Sort)
{
case 10: //批次数量升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.BatchQty ) AS RowNumber ";
break;
case 11: //批次数量降序
sqlSort = @"ROW_NUMBER() OVER ( ORDER BY T.BatchQty DESC ) AS RowNumber";
break;
case 20: //库存数量升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.StockQty ) AS RowNumber ";
break;
case 21: //库存数量降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.StockQty DESC ) AS RowNumber ";
break;
case 30: //兑换开始日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.StartDateTime ) AS RowNumber ";
break;
case 31: //兑换开始日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.StartDateTime DESC ) AS RowNumber ";
break;
case 40: //兑换结束日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.EndDateTime ) AS RowNumber ";
break;
case 41: //兑换结束日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.EndDateTime DESC ) AS RowNumber "; break;
case 50: //兑换结束日期升序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.Sort ) AS RowNumber ";
break;
case 51: //兑换结束日期降序
sqlSort = @" ROW_NUMBER() OVER ( ORDER BY T.Sort DESC ) AS RowNumber ";
break;
}
#endregion
var sqlParameters = new[]
{
new SqlParameter("@PageSize", serchMall.PageSize),
new SqlParameter("@PageNumber", serchMall.PageNumber),
new SqlParameter("@IsEnabled", serchMall.IsEnabled),
new SqlParameter("@Visible", serchMall.Visible),
new SqlParameter("@BatchName", serchMall.BatchName),
new SqlParameter("@BatchGuid", serchMall.BatchGuid)
};
return
(SqlHelper.ExecuteDataTable(Connfig, CommandType.Text, sql1 + sqlSort + sql2, sqlParameters)
.ConvertTo <ThirdPartyMallModel>()
.ToList());
}