/// <summary>등록 상품 삭제</summary>
public bool DeleteItem(BmItem pDataRq)
{
try
{
using (SqlConn = new SqlConnection(ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
try
{
SqlConn.Open();
var result = dac.DeleteItem(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 DeleteItem(BmItem pDataRq)
{
try
{
#region SetQuery
string strQuery = @"DELETE tbItem 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>등록 상품 등록</summary>
public bool InsertItem(BmItem pDataRq)
{
try
{
#region SetQuery
string strQuery = @"INSERT INTO tbItem
(ItemId
, ItemProductSeq
, ItemName
, Descript
, Price
, State
, Registrant)
Values
(@ItemId
, @ItemProductSeq
, @ItemName
, @Descript
, @Price
, @State
, @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("@ItemId", SqlDbType.VarChar, 20).Value = pDataRq.ItemId;
cmd.Parameters.Add("@ItemProductSeq", SqlDbType.Int, 0).Value = pDataRq.ItemProductSeq;
cmd.Parameters.Add("@ItemName", SqlDbType.VarChar, 100).Value = pDataRq.ItemName;
cmd.Parameters.Add("@Descript", SqlDbType.VarChar, 300).Value = pDataRq.Descript;
cmd.Parameters.Add("@Price", SqlDbType.Decimal, 0).Value = pDataRq.Price;
cmd.Parameters.Add("@State", SqlDbType.Int, 0).Value = pDataRq.State;
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;
}
}
/// <summary>등록 상품 조회</summary>
public List<BmItem> GetItemList(BmItem pDataRq)
{
try
{
#region SetQuery
StringBuilder sbQuery = new StringBuilder(@"SELECT I.*
, ISNULL(SUM(IQ.Quantity), 0) AS 'ItemQuantity'
, ISNULL(SUM(IQ.SalesCount), 0) AS 'ItemSalesCount'
, ISNULL(SUM(I.Price * IQ.SalesCount), 0) AS 'ItemSalesPrice'
FROM tbItem I
LEFT JOIN tbItemQuantity IQ ON I.Seq = IQ.ItemSeq
WHERE 1=1");
if (pDataRq.Seq > 0)
sbQuery.AppendLine(" AND I.Seq = @Seq");
if (!string.IsNullOrEmpty(pDataRq.ItemId))
sbQuery.AppendLine(" AND I.ItemId = @ItemId");
if (pDataRq.ItemProductSeq > 0)
sbQuery.AppendLine(" AND I.ItemProductSeq = @ItemProductSeq");
sbQuery.AppendLine(@"GROUP BY I.Seq
, I.ItemId
, I.ItemProductSeq
, I.ItemName
, I.Descript
, I.Price
, I.State
, I.Registrant
, I.RegDt
, I.Modifyer
, I.ModifyDt
ORDER BY I.ItemProductSeq, I.RegDt");
#endregion SetQuery
List<BmItem> result = new List<BmItem>();
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.ItemId) == false)
cmd.Parameters.Add("@ItemId", SqlDbType.VarChar, 20).Value = pDataRq.ItemId;
if (pDataRq.Seq > 0)
cmd.Parameters.Add("@ItemProductSeq", SqlDbType.Int, 0).Value = pDataRq.ItemProductSeq;
#endregion Set Parameters
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Load(reader);
result = ConvertToBmItem(dt);
}
reader.Dispose();
cmd.Dispose();
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>등록상품 Convert DataTable -> BmItem</summary>
private List<BmItem> ConvertToBmItem(DataTable dt)
{
List<BmItem> list = new List<BmItem>();
foreach (DataRow dr in dt.Rows)
{
BmItem data = new BmItem();
data.Seq = (int)dr["Seq"];
data.ItemId = dr["ItemId"].ToString();
data.ItemProductSeq = (int)dr["ItemProductSeq"];
data.ItemName = dr["ItemName"].ToString();
data.Descript = dr["Descript"].ToString();
data.State = (int)dr["State"];
data.Price = Convert.ToDouble(dr["Price"].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.ItemQuantity = (int)dr["ItemQuantity"];
data.ItemSalesCount = (int)dr["ItemSalesCount"];
data.ItemSalesPrice = Convert.ToDouble(dr["ItemSalesPrice"].ToString());
list.Add(data);
}
return list;
}
/// <summary>등록 상품 수정</summary>
public bool UpdateItem(BmItem pDataRq)
{
try
{
#region SetQuery
string strQuery = @"UPDATE tbItem SET
ItemProductSeq = @ItemProductSeq,
ItemName = @ItemName,
Descript = @Descript,
Price = @Price,
State = @State,
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("@ItemProductSeq", SqlDbType.Int, 0).Value = pDataRq.ItemProductSeq;
cmd.Parameters.Add("@ItemName", SqlDbType.VarChar, 100).Value = pDataRq.ItemName;
cmd.Parameters.Add("@Descript", SqlDbType.VarChar, 300).Value = pDataRq.Descript;
cmd.Parameters.Add("@Price", SqlDbType.Decimal, 0).Value = pDataRq.Price;
cmd.Parameters.Add("@State", SqlDbType.Int, 0).Value = pDataRq.State;
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;
}
}
public BmItemPkgRs()
{
Item = new BmItem();
QuantityList = new BmItemQuantityPagingRs();
ImgList = new BmItemImgPagingRs();
}
public BmItemPagingRq()
{
Item = new BmItem();
Paging = new BmPaging();
}