/// <summary>대표 상품 조회</summary>
public List<BmItemProduct> GetItemProductList(BmItemProduct pDataRq)
{
try
{
#region SetQuery
StringBuilder sbQuery = new StringBuilder(@"SELECT 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");
if (pDataRq.Seq > 0)
sbQuery.AppendLine(" AND IP.Seq = @Seq");
if (!string.IsNullOrEmpty(pDataRq.ItemProductId))
sbQuery.AppendLine(" AND IP.ItemProductId = @ItemProductId");
sbQuery.AppendLine(@" GROUP BY IP.Seq
,IP.ItemProductId
,IP.ItemProductName
,IP.Descript
,IP.Img
,IP.Registrant
,IP.RegDt
,IP.Modifyer
,IP.ModifyDt
ORDER BY IP.RegDt");
#endregion SetQuery
List<BmItemProduct> result = new List<BmItemProduct>();
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sbQuery.ToString();
#region Set Parameters
if (pDataRq.Seq > 0)
cmd.Parameters.Add("@Seq", SqlDbType.Int, 0).Value = pDataRq.Seq;
if (string.IsNullOrEmpty(pDataRq.ItemProductId) == false)
cmd.Parameters.Add("@ItemProductId", SqlDbType.VarChar, 20).Value = pDataRq.ItemProductId;
#endregion Set Parameters
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Load(reader);
result = ConvertToBmItemProduct(dt);
}
reader.Dispose();
cmd.Dispose();
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>대표 상품 수정</summary>
public bool UpdateItemProduct(BmItemProduct pDataRq)
{
try
{
using (SqlConn = new SqlConnection(ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
try
{
SqlConn.Open();
var result = dac.UpdateItemProduct(pDataRq);
scope.Complete();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlConn.Dispose();
}
}
}
}
catch (Exception ex)
{
WriteLog("Exception", ex.Message);
return false;
}
}
/// <summary>대표 상품 삭제</summary>
public bool DeleteItemProduct(BmItemProduct pDataRq)
{
try
{
#region SetQuery
string strQuery = @"DELETE tbItemProduct WHERE Seq = @Seq";
#endregion SetQuery
int result = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strQuery.ToString();
#region Set Parameters
cmd.Parameters.Add("@Seq", SqlDbType.Int, 0).Value = pDataRq.Seq;
#endregion Set Parameters
result = cmd.ExecuteNonQuery();
cmd.Dispose();
if (result == 1)
{
WriteHistory(pDataRq, HistoryCommandType.DELETE);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>대표상품 Convert DataTable -> BmItemProduct</summary>
private List<BmItemProduct> ConvertToBmItemProduct(DataTable dt)
{
List<BmItemProduct> list = new List<BmItemProduct>();
foreach (DataRow dr in dt.Rows)
{
BmItemProduct data = new BmItemProduct();
data.Seq = (int)dr["Seq"];
data.ItemProductId = dr["ItemProductId"].ToString();
data.ItemProductName = dr["ItemProductName"].ToString();
data.Descript = dr["Descript"].ToString();
data.Registrant = dr["Registrant"].ToString();
data.RegDt = (DateTime)dr["RegDt"];
data.Modifyer = dr["Modifyer"].ToString();
data.ModifyDt = string.IsNullOrEmpty(dr["ModifyDt"].ToString()) ? null : (DateTime?)dr["ModifyDt"];
data.ItemCount = (int)dr["ItemCount"];
data.ProductQuantity = (int)dr["ProductQuantity"];
data.ProductSalesCount = (int)dr["ProductSalesCount"];
data.ProductSalesPrice = Convert.ToDouble(dr["ProductSalesPrice"].ToString());
list.Add(data);
}
return list;
}
/// <summary>대표 상품 수정</summary>
public bool UpdateItemProduct(BmItemProduct pDataRq)
{
try
{
#region SetQuery
string strQuery = @"UPDATE tbItemProduct SET
ItemProductName = @ItemProductName,
Descript = @Descript,
Modifyer = @Modifyer,
ModifyDt = dbo.getkordate()
WHERE
Seq = @Seq";
#endregion SetQuery
int result = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strQuery.ToString();
#region Set Parameters
cmd.Parameters.Add("@Seq", SqlDbType.Int, 0).Value = pDataRq.Seq;
cmd.Parameters.Add("@ItemProductName", SqlDbType.VarChar, 100).Value = pDataRq.ItemProductName;
cmd.Parameters.Add("@Descript", SqlDbType.VarChar, 300).Value = pDataRq.Descript;
cmd.Parameters.Add("@Modifyer", SqlDbType.VarChar, 20).Value = pDataRq.Modifyer;
#endregion Set Parameters
result = cmd.ExecuteNonQuery();
cmd.Dispose();
if (result == 1)
{
WriteHistory(pDataRq, HistoryCommandType.UPDATE);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>대표 상품 등록</summary>
public bool InsertItemProduct(BmItemProduct pDataRq)
{
try
{
#region SetQuery
string strQuery = @"Insert Into tbItemProduct
(ItemProductId
, ItemProductName
, Descript
, Registrant
)
Values
(@ItemProductId
, @ItemProductName
, @Descript
, @Registrant)
SELECT @@Identity";
#endregion SetQuery
int result = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strQuery.ToString();
#region Set Parameters
cmd.Parameters.Add("@ItemProductId1", SqlDbType.VarChar, 20).Value = pDataRq.ItemProductId;
cmd.Parameters.Add("@ItemProductName", SqlDbType.VarChar, 100).Value = pDataRq.ItemProductName;
cmd.Parameters.Add("@Descript", SqlDbType.VarChar, 300).Value = pDataRq.Descript;
cmd.Parameters.Add("@Registrant", SqlDbType.VarChar, 20).Value = pDataRq.Registrant;
#endregion Set Parameters
result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Dispose();
if (result > 0)
{
pDataRq.Seq = result;
WriteHistory(pDataRq, HistoryCommandType.INSERT);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
public BmItemProductPagingRq()
{
Item = new BmItemProduct();
Paging = new BmPaging();
}