public static bool Update(ProductPrice productPrice)
{
try
{
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
conn.Open();
var sql = @"UPDATE product_price
set end_date =@end_date,
unit_price=@unit_price,
modified_at=CURRENT_TIMESTAMP,
modified_by=@modified_by
WHERE product_code = @product_code
And start_date = @start_date";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("product_code", productPrice.Product.ProductCode);
cmd.Parameters.AddWithValue("start_date", productPrice.StartDate);
cmd.Parameters.AddWithValue("end_date", productPrice.EndDate);
cmd.Parameters.AddWithValue("unit_price", productPrice.UnitPrice);
cmd.Parameters.AddWithValue("modified_by", productPrice.ModifiedBy);
var affRow = cmd.ExecuteNonQuery();
}
return(true);
}
catch (Exception)
{
throw;
}
}
//public static ProductPrice GetPriceList(string productCode, DateTime priceDate)
//{
// try
// {
// using (var conn = new MySqlConnection(Globals.CONN_STR))
// {
// conn.Open();
// var sql = "";
// sql = @"select COALESCE(unit_price) as unit_price
// from product_price p
// where start_date <=@start_date
// and end_date >=@end_date
// and product_code =@product_code
// order by end_date asc LIMIT 1 ";
// var cmd = new MySqlCommand(sql, conn);
// cmd.Parameters.AddWithValue("product_code", productCode);
// cmd.Parameters.AddWithValue("start_date", priceDate);
// cmd.Parameters.AddWithValue("end_date", priceDate);
// var da = new MySqlDataAdapter(cmd);
// var ds = new DataSet();
// da.Fill(ds);
// if (ds.Tables[0].Rows.Count > 0)
// {
// return new ProductPrice
// {
// UnitPrice = (decimal)ds.Tables[0].Rows[0]["unit_price"],
// };
// }
// else
// {
// return new ProductPrice
// {
// UnitPrice = 0,
// };
// }
// }
// }
// catch (Exception)
// {
// throw;
// }
//}
public static bool Insert(ProductPrice productPrice)
{
try
{
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
conn.Open();
var sql = @"INSERT INTO product_price
(product_code, start_date, end_date,
unit_price, create_by)
VALUES
(@product_code, @start_date, @end_date,
@unit_price, @create_by) ";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("product_code", productPrice.Product.ProductCode);
cmd.Parameters.AddWithValue("start_date", productPrice.StartDate);
cmd.Parameters.AddWithValue("end_date", productPrice.EndDate);
cmd.Parameters.AddWithValue("unit_price", productPrice.UnitPrice);
cmd.Parameters.AddWithValue("create_by", productPrice.CreateBy);
var affRow = cmd.ExecuteNonQuery();
}
return(true);
}
catch (Exception)
{
throw;
}
}
