protected void GetRateList()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
string rateSQL, rateDetailSQL;
if (lstRateType.SelectedValue == "1")
{
rateSQL = @"SELECT distinct b.dba_name,b.carrier_code,a.airline,a.share FROM all_rate_table a
LEFT OUTER JOIN (select distinct dba_name,carrier_code,elt_account_number from organization) b
on (a.elt_account_number=b.elt_account_number
and a.airline=b.carrier_code) WHERE a.elt_account_number="
+ elt_account_number + " AND a.rate_type=" + lstRateType.SelectedValue
+ " AND a.origin_port=N'" + txtFrom.Text + "' AND a.dest_port=N'" + txtTo.Text
+ "' AND a.agent_no=" + lstCustomer.SelectedValue + " AND (b.carrier_code=" + txtCarrierCode.Text + " OR a.airline=-1)";
rateDetailSQL = @"SELECT airline,weight_break=case item_no when 0 then 'Min($)' when 1 then '+Min'
else cast(weight_break as NVARCHAR) end,kg_lb,rate,fl_rate,sec_rate FROM all_rate_table WHERE elt_account_number="
+ elt_account_number + " AND rate_type=" + lstRateType.SelectedValue
+ " AND origin_port=N'" + txtFrom.Text + "' AND dest_port=N'" + txtTo.Text
+ "' AND agent_no=" + lstCustomer.SelectedValue + " ORDER BY item_no";
}
else if (lstRateType.SelectedValue == "5" || lstRateType.SelectedValue == "3")
{
rateSQL = @"SELECT distinct b.dba_name,b.carrier_code,a.airline,a.share FROM all_rate_table a
LEFT OUTER JOIN (select distinct dba_name,carrier_code,elt_account_number from organization) b
on (a.elt_account_number=b.elt_account_number
and a.airline=b.carrier_code) WHERE a.elt_account_number="
+ elt_account_number + " AND a.rate_type=" + lstRateType.SelectedValue
+ " AND a.origin_port=N'" + txtFrom.Text + "' AND a.dest_port=N'" + txtTo.Text
+ "' AND (b.carrier_code=" + txtCarrierCode.Text + " OR a.airline=-1)";
rateDetailSQL = @"SELECT airline,weight_break=case item_no when 0 then 'Min($)' when 1 then '+Min'
else cast(weight_break as NVARCHAR) end,kg_lb,rate,fl_rate,sec_rate FROM all_rate_table WHERE elt_account_number="
+ elt_account_number + " AND rate_type=" + lstRateType.SelectedValue
+ " AND origin_port=N'" + txtFrom.Text + "' AND dest_port=N'" + txtTo.Text
+ "' ORDER BY item_no";
}
else if (lstRateType.SelectedValue == "4")
{
rateSQL = @"SELECT distinct b.dba_name,b.carrier_code,a.airline,a.share FROM all_rate_table a
LEFT OUTER JOIN (select distinct dba_name,carrier_code,elt_account_number from organization) b
on (a.elt_account_number=b.elt_account_number
and a.airline=b.carrier_code) WHERE a.elt_account_number="
+ elt_account_number + " AND a.rate_type=" + lstRateType.SelectedValue
+ " AND a.origin_port=N'" + txtFrom.Text + "' AND a.dest_port=N'" + txtTo.Text
+ "' AND a.customer_no=" + lstCustomer.SelectedValue + " AND (b.carrier_code=" + txtCarrierCode.Text + " OR a.airline=-1)";
rateDetailSQL = @"SELECT airline,weight_break=case item_no when 0 then 'Min($)' when 1 then '+Min'
else cast(weight_break as NVARCHAR) end,kg_lb,rate,fl_rate,sec_rate FROM all_rate_table WHERE elt_account_number="
+ elt_account_number + " AND rate_type=" + lstRateType.SelectedValue
+ " AND origin_port=N'" + txtFrom.Text + "' AND dest_port=N'" + txtTo.Text
+ "' AND customer_no=" + lstCustomer.SelectedValue + " ORDER BY item_no";
}
else
{
return;
}
feData.AddToDataSet("Rate", rateSQL);
feData.AddToDataSet("RateDetail", rateDetailSQL);
feData.AddRelation("RateRelation", "Rate", "airline", "RateDetail", "airline");
UltraWebGrid1.DataSource = feData;
UltraWebGrid1.DataBind();
UltraWebGrid1.ExpandAll(true);
}