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