public int SavePromotion(Promotion promotion)
{
PromotionDL promotionDL=new PromotionDL();
if (promotion.PromotionID > 0)
return promotionDL.UpdatePromotion(promotion);
else
return promotionDL.SavePromotion(promotion);
}
public Promotion GetPromotion(int promotionID)
{
Promotion promotion = null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT promotionID, name, value, imageUrl, showOnFirstPage, dateFrom, dateTo FROM promotion WHERE promotionID=@promotionID", objConn))
{
objConn.Open();
objComm.Parameters.Add("@promotionID", SqlDbType.Int).Value = promotionID;
using (SqlDataReader reader = objComm.ExecuteReader())
{
while (reader.Read())
promotion = new Promotion(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2), reader.GetString(3), 0, reader.GetBoolean(4), reader.GetDateTime(5), reader.GetDateTime(6));
}
}
}
return promotion;
}
public Promotion GetPromotion(string url)
{
Promotion promotion = null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT promotionID, name, value, imageUrl, showOnFirstPage, dateFrom, dateTo, url FROM promotion WHERE url = @url", objConn))
{
objConn.Open();
objComm.Parameters.Add("@url", SqlDbType.NVarChar, 50).Value = url;
using (SqlDataReader reader = objComm.ExecuteReader())
{
while (reader.Read())
promotion = new Promotion(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2), reader.GetString(3), 0, reader.GetBoolean(4), Common.ConvertToLocalTime(reader.GetDateTime(5)), Common.ConvertToLocalTime(reader.GetDateTime(6)), reader.GetString(7));
}
}
}
return promotion;
}
public int SavePromotion(Promotion promotion)
{
int promotionID = 0;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("INSERT INTO promotion (name, value, imageUrl, showOnFirstPage, dateFrom, dateTo) VALUES (@name, @value, @imageUrl, @showOnFirstPage, @dateFrom, @dateTo); SELECT SCOPE_IDENTITY()", objConn))
{
objConn.Open();
objComm.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = promotion.Name;
objComm.Parameters.Add("@value", SqlDbType.Float).Value = promotion.Value;
objComm.Parameters.Add("@imageUrl", SqlDbType.NVarChar, 50).Value = promotion.ImageUrl;
objComm.Parameters.Add("@showOnFirstPage", SqlDbType.Bit).Value = promotion.ShowOnFirstPage;
objComm.Parameters.Add("@dateFrom", SqlDbType.Date).Value = promotion.DateFrom;
objComm.Parameters.Add("@dateTo", SqlDbType.Date).Value = promotion.DateTo;
promotionID = int.Parse(objComm.ExecuteScalar().ToString());
}
}
return promotionID;
}
public int UpdatePromotion(Promotion promotion)
{
int status = 0;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("UPDATE promotion SET name=@name, value=@value, imageUrl=@imageUrl, showOnFirstPage=@showOnFirstPage, dateFrom=@dateFrom, dateTo=@dateTo WHERE promotionID=@promotionID", objConn))
{
objConn.Open();
objComm.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = promotion.Name;
objComm.Parameters.Add("@value", SqlDbType.Float).Value = promotion.Value;
objComm.Parameters.Add("@imageUrl", SqlDbType.NVarChar, 50).Value = promotion.ImageUrl;
objComm.Parameters.Add("@promotionID", SqlDbType.Int).Value = promotion.PromotionID;
objComm.Parameters.Add("@showOnFirstPage", SqlDbType.Bit).Value = promotion.ShowOnFirstPage;
objComm.Parameters.Add("@dateFrom", SqlDbType.Date).Value = promotion.DateFrom;
objComm.Parameters.Add("@dateTo", SqlDbType.Date).Value = promotion.DateTo;
status = objComm.ExecuteNonQuery();
}
}
return status;
}
private void savePromotion()
{
try
{
Promotion promotion = new Promotion();
promotion.Name = txtName.Text;
promotion.Value = double.Parse(txtValue.Text);
promotion.ImageUrl = txtImageUrl.Text;
promotion.ShowOnFirstPage = chkShowOnFirstPage.Checked;
promotion.DateFrom = DateTime.Parse(txtDateFrom.Text);
promotion.DateTo = DateTime.Parse(txtDateTo.Text);
if (lblPromotionID.Value != string.Empty)
promotion.PromotionID = int.Parse(lblPromotionID.Value);
PromotionBL promotionBL = new PromotionBL();
promotionBL.SavePromotion(promotion);
}
catch (BLException ex)
{
setStatus(ex.Message, System.Drawing.Color.Red, true);
}
}
private int saveProductPromotion(Promotion promotion, int productID)
{
int status = 0;
DeleteProductPromotions(productID);
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("INSERT INTO promotionProduct (promotionID, productID, price) VALUES (@promotionID, @productID, @price)", objConn))
{
objConn.Open();
objComm.Parameters.Add("@promotionID", SqlDbType.Int).Value = promotion.PromotionID;
objComm.Parameters.Add("@productID", SqlDbType.Int).Value = productID;
objComm.Parameters.Add("@price", SqlDbType.Float).Value = promotion.Price;
status = objComm.ExecuteNonQuery();
}
}
return status;
}
private Promotion getPromotions(int productID)
{
Promotion promotion = null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT promotion.promotionID, price, imageUrl FROM promotionProduct INNER JOIN promotion ON promotionProduct.promotionID=promotion.promotionID WHERE productID=@productID AND promotion.dateFrom<=GETDATE() AND promotion.dateTo>=GETDATE()", objConn))
{
objConn.Open();
objComm.Parameters.Add("@productID", SqlDbType.Int).Value = productID;
using (SqlDataReader reader = objComm.ExecuteReader())
{
while (reader.Read())
{
promotion = new Promotion();
promotion.PromotionID = reader.GetInt32(0);
promotion.Price = reader.GetDouble(1);
promotion.ImageUrl = reader.GetString(2);
}
}
}
}
return promotion;
}
