public void InsertRecord(RateEntity re)
{
// To do: insert into databse
if (re.item_no == 0)
{
re.weight_break = "0";
}
if (re.item_no == 1)
{
re.weight_break = "1";
}
if (re.elt_account_number > 0 && re.item_no >= 0 && re.rate_type > 0 && re.airline != null &&
re.origin_port != null && re.dest_port != null && re.kg_lb != null &&
re.item_no != null && re.weight_break != null)
{
//IF NOT EXISTS (SELECT * FROM all_rate_table WHERE EmailItemID="
//+ re.EmailItemID + " AND rate_type=" + re.rate_type
//+ " AND airline=" + re.airline + " AND item_no=" + re.item_no
//+ ")
string insertCommand = @"IF NOT EXISTS
(SELECT * FROM all_rate_table WHERE elt_account_number="
+ re.elt_account_number + " AND rate_type=" + re.rate_type
+ " AND origin_port='" + re.origin_port
+ "' AND dest_port='" + re.dest_port
+ "' AND kg_lb='" + re.kg_lb
+ "' AND airline=" + re.airline
+ " AND item_no=" + re.item_no
+ ") INSERT INTO all_rate_table VALUES("
+ re.elt_account_number + ","
+ re.item_no + ","
+ re.rate_type + ",0,"
+ re.customer_no + ",'"
+ re.airline + "','"
+ re.origin_port + "','"
+ re.dest_port + "',"
+ re.weight_break + ","
+ re.rate + ",'"
+ re.kg_lb + "',"
+ re.share + ",null,"
+ re.fl_rate + ","
+ re.sec_rate + ",'"
+ re.include_fl_rate + "','"
+ re.include_sec_rate + "')";
this.DataTransaction(insertCommand);
}
}
// update keys: EmailItemID, rate_type, origin_port, dest_port, kg_lb, airline, item_no
public void UpdateRecord(RateEntity re)
{
// To do: Update databse
string updateCommand = "";
if (re.item_no == 0)
{
re.weight_break = "0";
}
if (re.item_no == 1)
{
re.weight_break = "1";
}
if (re.item_no >= 0)
{
updateCommand = @"UPDATE all_rate_table SET "
+ " weight_break=" + re.weight_break + ","
+ " rate=" + re.rate
+ " WHERE elt_account_number=" + re.elt_account_number
+ " AND rate_type=" + re.rate_type
+ " AND customer_no=" + re.customer_no
+ " AND origin_port='" + re.origin_port + "'"
+ " AND dest_port='" + re.dest_port + "'"
+ " AND kg_lb='" + re.kg_lb + "'"
+ " AND airline='" + re.airline + "'"
+ " AND item_no=" + re.item_no;
}
else
{
updateCommand = @"UPDATE all_rate_table SET "
+ " share=" + re.share + ","
+ " fl_rate=" + re.fl_rate + ","
+ " sec_rate=" + re.sec_rate + ","
+ " include_fl_rate='" + re.include_fl_rate + "',"
+ " include_sec_rate='" + re.include_sec_rate + "'"
+ " WHERE elt_account_number=" + re.elt_account_number
+ " AND rate_type=" + re.rate_type
+ " AND customer_no=" + re.customer_no
+ " AND origin_port='" + re.origin_port + "'"
+ " AND dest_port='" + re.dest_port + "'"
+ " AND kg_lb='" + re.kg_lb + "'"
+ " AND airline='" + re.airline + "'";
}
this.DataTransaction(updateCommand);
}
public void DeleteRecord(RateEntity re)
{
// To do: delete from databse
string deleteCommand = @"DELETE FROM all_rate_table "
+ " WHERE elt_account_number=" + re.elt_account_number
+ " AND rate_type=" + re.rate_type
+ " AND origin_port='" + re.origin_port + "'"
+ " AND dest_port='" + re.dest_port + "'"
+ " AND kg_lb='" + re.kg_lb + "'";
if (re.airline != null)
{
deleteCommand = deleteCommand + " AND airline='" + re.airline + "'";
}
if (re.item_no >= 0)
{
deleteCommand = deleteCommand + " AND item_no=" + re.item_no;
}
this.DataTransaction(deleteCommand);
}
