Example #1
0
        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));
        }
Example #3
0
 /// <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());
        }