/// <summary>대표 상품 Paging 조회</summary> public BmItemProductPagingRs GetItemProductPagingList(BmItemProductPagingRq pDataRq) { try { using (SqlConn = new SqlConnection(ConnectionString)) { using (TransactionScope scope = new TransactionScope()) { try { SqlConn.Open(); var result = dac.GetItemProductPagingList(pDataRq); scope.Complete(); return result; } catch (Exception ex) { throw ex; } finally { SqlConn.Dispose(); } } } } catch (Exception ex) { WriteLog("Exception", ex.Message); throw; } }
/// <summary>대표 상품 Paging 조회</summary> public BmItemProductPagingRs GetItemProductPagingList(BmItemProductPagingRq pDataRq) { try { #region SetQuery StringBuilder sbQuery = new StringBuilder(@"SELECT COUNT(Seq) AS 'TotalRowCnt' FROM tbItemProduct IP WHERE 1=1 --@@IP.ItemProductId --@@IP.ItemProductName SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY IP.RegDt DESC) AS 'RowNum' , IP.* , COUNT(DISTINCT(I.Seq)) AS 'ItemCount' , ISNULL(SUM(IQ.Quantity), 0) AS 'ProductQuantity' , ISNULL(SUM(IQ.SalesCount), 0) AS 'ProductSalesCount' , ISNULL(SUM(I.Price * IQ.SalesCount), 0) AS 'ProductSalesPrice' FROM tbItemProduct IP LEFT JOIN tbItem I ON IP.Seq = I.ItemProductSeq LEFT JOIN tbItemQuantity IQ ON I.Seq = IQ.ItemSeq WHERE 1=1 --@@IP.ItemProductId --@@IP.ItemProductName GROUP BY IP.Seq ,IP.ItemProductId ,IP.ItemProductName ,IP.Descript ,IP.Img ,IP.Registrant ,IP.RegDt ,IP.Modifyer ,IP.ModifyDt ) A WHERE 1=1"); if (string.IsNullOrEmpty(pDataRq.Item.ItemProductId) == false) sbQuery = sbQuery.Replace("--@@IP.ItemProductId", " AND IP.ItemProductId LIKE '%' + @ItemProductId + '%'"); if (string.IsNullOrEmpty(pDataRq.Item.ItemProductName) == false) sbQuery = sbQuery.Replace("--@@IP.ItemProductName", " AND IP.ItemProductName LIKE '%' + @ItemProductName + '%'"); sbQuery.AppendLine(" AND RowNum BETWEEN (@PageSize * @CurPage) + 1 AND ((@PageSize * @CurPage) + @PageSize)"); #endregion SetQuery BmItemProductPagingRs result = new BmItemProductPagingRs(); SqlCommand cmd = new SqlCommand(); cmd.Connection = SqlConn; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = sbQuery.ToString(); #region Set Parameters cmd.Parameters.Add("@PageSize", SqlDbType.Int, 0).Value = pDataRq.Paging.PageSize; cmd.Parameters.Add("@CurPage", SqlDbType.Int, 0).Value = pDataRq.Paging.CurPage; if (string.IsNullOrEmpty(pDataRq.Item.ItemProductId) == false) cmd.Parameters.Add("@ItemProductId", SqlDbType.VarChar, 20).Value = pDataRq.Item.ItemProductId; if (string.IsNullOrEmpty(pDataRq.Item.ItemProductName) == false) cmd.Parameters.Add("@ItemProductName", SqlDbType.VarChar, 100).Value = pDataRq.Item.ItemProductName; #endregion Set Parameters SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count == 1) { result.TotalCount = Convert.ToInt32(ds.Tables[0].Rows[0]["TotalRowCnt"].ToString()); if (result.TotalCount > 0 && ds.Tables[1].Rows.Count > 0) { result.List = ConvertToBmItemProduct(ds.Tables[1]); } } da.Dispose(); cmd.Dispose(); return result; } catch (Exception ex) { throw ex; } }