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; } }