protected void Page_Init(object sender, EventArgs e)
{
// Naviagtor assigned
//WebNavBar1.GridID = UltraWebGrid1.UniqueID;
// FreightEasyData Initialized
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("Ports", "SELECT port_code + ' - ' + port_desc as port_name,port_code FROM port WHERE elt_account_number=" + elt_account_number + " AND ISNULL(port_id,'')<>'' ORDER BY port_desc");
feData.AddToDataSet("Countries", "SELECT * FROM country_code WHERE elt_account_number=" + elt_account_number + " order by country_name");
feData.AddToDataSet("MOT", "SELECT code_id,code_desc FROM aes_codes WHERE code_type='Transport Code' ORDER BY code_id");
feData.AddToDataSet("Carriers", "SELECT ISNULL(carrier_id,'') AS carrier_code,LEFT(dba_name,22) AS carrier_name FROM organization WHERE is_carrier='Y' AND ISNULL(carrier_id,'') <> '' AND ISNULL(carrier_code,'')<>'' AND elt_account_number=" + elt_account_number + " ORDER BY dba_name");
// Form initialize
POE.DataSource = feData.Tables["Ports"];
POE.DataTextField = "port_name";
POE.DataValueField = "port_code";
POE.DataBind();
POE.Items.Insert(0, new ListItem("", ""));
POU.DataSource = feData.Tables["Ports"];
POU.DataTextField = "port_name";
POU.DataValueField = "port_code";
POU.DataBind();
POU.Items.Insert(0, new ListItem("", ""));
COD.DataSource = feData.Tables["Countries"];
COD.DataTextField = "country_name";
COD.DataValueField = "country_code";
COD.DataBind();
COD.Items.Insert(0, new ListItem("", ""));
MOT.DataSource = feData.Tables["MOT"];
MOT.DataTextField = "code_desc";
MOT.DataValueField = "code_id";
MOT.DataBind();
MOT.Items.Insert(0, new ListItem("", ""));
lstCarrier.DataSource = feData.Tables["Carriers"];
lstCarrier.DataTextField = "carrier_name";
lstCarrier.DataValueField = "carrier_code";
lstCarrier.DataBind();
lstCarrier.Items.Insert(0, new ListItem("", ""));
hFileType.Value = move_type;
// New AES
if (aes_id == "" || aes_id == "0")
{
labelPageTitle.Text = "New AES Form";
trInfo1.Visible = false;
trInfo2.Visible = false;
}
// Saved AES
else
{
labelPageTitle.Text = "Edit AES Form";
}
}
private FreightEasy.DataManager.FreightEasyData SetDataList()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("Ports", "SELECT port_code + ' - ' + port_desc as port_name,port_code FROM port WHERE elt_account_number=" + elt_account_number + " AND ISNULL(port_id,'')<>'' ORDER BY port_desc");
feData.AddToDataSet("Countries", "SELECT * FROM country_code WHERE elt_account_number=" + elt_account_number + " order by country_name");
feData.AddToDataSet("MOT", "SELECT code_id,code_desc FROM aes_codes WHERE code_type='Transport Code' ORDER BY code_id");
feData.AddToDataSet("Carriers", "SELECT ISNULL(carrier_id,'') AS carrier_code,LEFT(dba_name,22) AS carrier_name FROM organization WHERE is_carrier='Y' AND ISNULL(carrier_id,'') <> '' AND ISNULL(carrier_code,'')<>'' AND elt_account_number=" + elt_account_number + " ORDER BY dba_name");
feData.AddToDataSet("ScheduleB", "SELECT * FROM scheduleB WHERE elt_account_number=" + elt_account_number + " ORDER BY description");
// Session["AES_DATA"] = feData;
return(feData);
}
protected void LoadPorts()
{
FEData.AddToDataSet("Ports", "SELECT port_code + ' - ' + port_desc as port_name,port_code FROM port WHERE elt_account_number=" + elt_account_number + " ORDER BY port_desc");
OriginPortSelect.DataSource = FEData.Tables["Ports"].DefaultView;
OriginPortSelect.DataTextField = "port_name";
OriginPortSelect.DataValueField = "port_code";
OriginPortSelect.DataBind();
OriginPortSelect.Items.Insert(0, new ListItem("", ""));
DestPortSelect.DataSource = FEData.Tables["Ports"].DefaultView;
DestPortSelect.DataTextField = "port_name";
DestPortSelect.DataValueField = "port_code";
DestPortSelect.DataBind();
DestPortSelect.Items.Insert(0, new ListItem("", ""));
}
protected void Page_Init(object sender, EventArgs e)
{
elt_account_number = Request.Cookies["CurrentUserInfo"]["elt_account_number"];
user_id = Request.Cookies["CurrentUserInfo"]["user_id"];
user_right = Request.Cookies["CurrentUserInfo"]["user_right"];
login_name = Request.Cookies["CurrentUserInfo"]["login_name"];
string sb_id = Request.Params["SBID"];
hOrgID.Value = Request.Params["OrgID"];
if (sb_id == null || sb_id == "")
{
labelTitle.Text = "ADD NEW SCHEDULE B ITEM";
}
else
{
labelTitle.Text = "EDIT SCHEDULE B ITEM";
hSBID.Value = sb_id;
}
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("ExportCode", "SELECT code_id,LEFT(code_id+'-'+CAST(code_desc AS NVARCHAR),32) AS code_desc FROM aes_codes WHERE code_type='Export Code' ORDER BY code_id");
feData.AddToDataSet("LicenseType", "SELECT code_id,LEFT(code_id+'-'+CAST(code_desc AS NVARCHAR),32) AS code_desc FROM aes_codes WHERE code_type='License Code' ORDER BY code_id");
feData.AddToDataSet("UOMCode", "SELECT code_id,LEFT(CAST(code_desc AS NVARCHAR),32) AS code_desc FROM aes_codes WHERE code_type='UOM' ORDER BY code_desc");
lstSBUnit1.DataSource = feData.Tables["UOMCode"];
lstSBUnit1.DataTextField = "code_desc";
lstSBUnit1.DataValueField = "code_id";
lstSBUnit1.DataBind();
lstSBUnit1.Items.Insert(0, new ListItem("", ""));
lstSBUnit2.DataSource = feData.Tables["UOMCode"];
lstSBUnit2.DataTextField = "code_desc";
lstSBUnit2.DataValueField = "code_id";
lstSBUnit2.DataBind();
lstSBUnit2.Items.Insert(0, new ListItem("", ""));
lstExportCode.DataSource = feData.Tables["ExportCode"];
lstExportCode.DataTextField = "code_desc";
lstExportCode.DataValueField = "code_id";
lstExportCode.DataBind();
lstExportCode.Items.Insert(0, new ListItem("", ""));
lstLicenseType.DataSource = feData.Tables["LicenseType"];
lstLicenseType.DataTextField = "code_desc";
lstLicenseType.DataValueField = "code_id";
lstLicenseType.DataBind();
lstLicenseType.Items.Insert(0, new ListItem("", ""));
}
protected void AddNewAES()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
ArrayList tranStrAL = new ArrayList();
string tranStr = "";
tranStr = "SET ANSI_WARNINGS OFF";
tranStrAL.Add(tranStr);
tranStr = @"INSERT INTO aes_master(elt_account_number,party_to_transaction,export_date,tran_ref_no,consignee_acct,inter_consignee_acct,origin_state,dest_country,tran_method,export_port,unloading_port,carrier_id_code,export_carrier,shipment_ref_no,entry_no,hazardous_materials,route_export_tran,in_bond_type,in_bond_no,ftz,last_modified,shipper_acct,file_type,house_num,master_num)
VALUES(" + elt_account_number + ",N'" + RCC.SelectedValue + "',N'" + EDA.Text + "',N'" + BN.Text + "'," + MakeNullCase(hConsigneeAcct.Value) + "," + MakeNullCase(hInterConsigneeAcct.Value) + ",N'" + ST.SelectedValue + "',N'" + COD.SelectedValue + "',N'" + MOT.SelectedValue + "',N'" + POE.SelectedValue + "',N'" + POU.SelectedValue
+ "',N'" + lstCarrier.SelectedValue + "',N'" + lstCarrier.Items[lstCarrier.SelectedIndex].Text + "',N'" + SRN.Text + "','',N'" + HAZ.SelectedValue + "',N'" + RT.SelectedValue + "',N'" + IBT.SelectedValue + "',N'" + IBN.Text + "',N'" + FTZ.Text + "',GETDATE()," + MakeNullCase(hShipperAcct.Value) + ",N'" + hFileType.Value + "',N'" + txtHouse.Text + "',N'" + txtMaster.Text + "')";
tranStrAL.Add(tranStr);
// AES item insert loop using grid entries
for (int i = 0; i < UltraWebGrid1.Rows.Count; i++)
{
if (UltraWebGrid1.Rows[i].Cells[3].Value == null)
{
UltraWebGrid1.Rows[i].Cells[3].Value = "0";
}
if (UltraWebGrid1.Rows[i].Cells[5].Value == null)
{
UltraWebGrid1.Rows[i].Cells[5].Value = "0";
}
if (UltraWebGrid1.Rows[i].Cells[7].Value == null)
{
UltraWebGrid1.Rows[i].Cells[7].Value = "0";
}
if (UltraWebGrid1.Rows[i].Cells[8].Value == null)
{
UltraWebGrid1.Rows[i].Cells[8].Value = "0";
}
tranStr = @"INSERT INTO aes_detail(elt_account_number,item_no,dfm,b_number,item_desc,b_qty1,unit1,b_qty2,unit2,gross_weight,item_value,export_code,license_type,license_number,eccn,vin_type,vin,vc_title,vc_state,aes_id)
VALUES(" + elt_account_number + "," + i + ",N'" + UltraWebGrid1.Rows[i].Cells[0].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[1].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[1].Text
+ "'," + UltraWebGrid1.Rows[i].Cells[3].Value + ",N'" + UltraWebGrid1.Rows[i].Cells[4].Value + "'," + UltraWebGrid1.Rows[i].Cells[5].Value + ",N'" + UltraWebGrid1.Rows[i].Cells[6].Value
+ "'," + UltraWebGrid1.Rows[i].Cells[7].Value + "," + UltraWebGrid1.Rows[i].Cells[8].Value + ",N'" + UltraWebGrid1.Rows[i].Cells[9].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[10].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[11].Value
+ "',N'" + UltraWebGrid1.Rows[i].Cells[12].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[13].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[14].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[15].Value + "',N'" + UltraWebGrid1.Rows[i].Cells[16].Value + "',IDENT_CURRENT('aes_master'))";
tranStrAL.Add(tranStr);
tranStr = "SET ANSI_WARNINGS ON";
tranStrAL.Add(tranStr);
}
if (!feData.DataTransactions((string[])tranStrAL.ToArray(typeof(string))))
{
txtResultBox.Text = feData.GetLastTransactionError();
txtResultBox.Visible = true;
}
else
{
feData.AddToDataSet("AESInfo", "SELECT * FROM aes_master WHERE elt_account_number=" + elt_account_number + " AND shipment_ref_no=N'" + SRN.Text + "'AND file_type='AE'");
if (feData.Tables["AESInfo"].Rows.Count > 0)
{
Response.Redirect("EditAES.aspx?AESID=" + feData.Tables["AESInfo"].Rows[0]["auto_uid"].ToString());
}
}
}
protected void LoadAllData(string OrgID)
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("OrgInfo", "SELECT * FROM organization WHERE elt_account_number=" + elt_account_number + " AND org_account_number=" + OrgID);
if (feData.Tables["OrgInfo"].Rows.Count > 0)
{
DataRow drOrg = feData.Tables["OrgInfo"].Rows[0];
txtDBA.Text = drOrg["dba_name"].ToString();
txtLegalName.Text = drOrg["business_legal_name"].ToString();
txtAddress1.Text = drOrg["business_address"].ToString();
txtAddress2.Text = drOrg["business_address2"].ToString();
txtCity.Text = drOrg["business_city"].ToString();
txtState.Text = drOrg["business_state"].ToString();
txtZip.Text = drOrg["business_zip"].ToString();
lstCountry.SelectedValue = drOrg["b_country_code"].ToString();
txtFirstName.Text = drOrg["owner_fname"].ToString();
txtMidName.Text = drOrg["owner_mname"].ToString();
txtLastName.Text = drOrg["owner_lname"].ToString();
txtPhone.Text = FormatStringNumberOnly(drOrg["business_phone"].ToString());
txtFax.Text = FormatStringNumberOnly(drOrg["business_fax"].ToString());
txtCell.Text = FormatStringNumberOnly(drOrg["owner_phone"].ToString());
txtEmail.Text = drOrg["owner_email"].ToString();
txtTaxID.Text = drOrg["business_fed_taxid"].ToString();
}
}
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
string sqlTxt = "";
if (hOrgID.Value == "" || hOrgID.Value == "0")
{
sqlTxt = "SELECT * FROM ig_schedule_b WHERE elt_account_number=" + elt_account_number + " AND sb_id=" + ListBox1.SelectedValue;
}
else
{
sqlTxt = "SELECT * FROM scheduleB WHERE elt_account_number=" + elt_account_number + " AND auto_uid=" + ListBox1.SelectedValue;
}
feData.AddToDataSet("scheduleBInfo", sqlTxt);
if (feData.Tables["scheduleBInfo"].Rows.Count > 0)
{
DataRow dtTmp = feData.Tables["scheduleBInfo"].Rows[0];
hSBCode.Value = dtTmp["sb"].ToString();
hUnit1.Value = dtTmp["sb_unit1"].ToString();
hUnit2.Value = dtTmp["sb_unit2"].ToString();
hDesc.Value = dtTmp["description"].ToString();
hExportCode.Value = dtTmp["export_code"].ToString();
hLicenseType.Value = dtTmp["license_type"].ToString();
hECCN.Value = dtTmp["eccn"].ToString();
}
}
protected void Page_Init(object sender, EventArgs e)
{
elt_account_number = Request.Cookies["CurrentUserInfo"]["elt_account_number"];
user_id = Request.Cookies["CurrentUserInfo"]["user_id"];
user_right = Request.Cookies["CurrentUserInfo"]["user_right"];
login_name = Request.Cookies["CurrentUserInfo"]["login_name"];
org_id = Request.Params["orgID"].ToString();
if (org_id == "" || org_id == "0")
{
labelTitle.Text = "ADD NEW COMPANY";
org_id = "";
}
else
{
labelTitle.Text = "EDIT COMPANY";
}
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("Countries", "SELECT * FROM country_code WHERE elt_account_number=" + elt_account_number + " ORDER BY country_name");
lstCountry.DataSource = feData.Tables["Countries"];
lstCountry.DataTextField = "country_name";
lstCountry.DataValueField = "country_code";
lstCountry.DataBind();
lstCountry.Items.Insert(0, new ListItem("", ""));
}
protected void Load_Booking_Confirm(string bc_uid)
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("BookingConfirm", "select * from booking_confirm where elt_account_number="
+ elt_account_number + " and auto_uid=" + bc_uid + "");
DataTable dt = feData.Tables["BookingConfirm"];
this.hSearchNum.Value = dt.Rows[0]["auto_uid"].ToString();
this.spn_prefix_select.Visible = false;
this.td_bill_select.Visible = false;
if (dt.Rows[0]["document_date"] != DBNull.Value)
{
this.txt_document_date.Text = Convert.ToDateTime(dt.Rows[0]["document_date"]).ToShortDateString();
}
this.txt_booking_no.Text = dt.Rows[0]["booking_no"].ToString();
this.lstSearchNum.Text = dt.Rows[0]["bc_no"].ToString();
this.hf_booking_no.Value = dt.Rows[0]["booking_no"].ToString();
this.hf_master_no.Value = dt.Rows[0]["master_no"].ToString();
this.hf_house_no.Value = dt.Rows[0]["house_no"].ToString();
this.hf_sub_house_no.Value = dt.Rows[0]["sub_house_no"].ToString();
this.hShipperAcct.Value = dt.Rows[0]["shipper_acct"].ToString();
this.lstShipperName.Text = dt.Rows[0]["shipper_name"].ToString();
this.txtShipperInfo.Text = dt.Rows[0]["shipper_info"].ToString();
this.hConsigneeAcct.Value = dt.Rows[0]["deliver_to_acct"].ToString();
this.lstConsigneeName.Text = dt.Rows[0]["deliver_to_name"].ToString();
this.txtConsigneeInfo.Text = dt.Rows[0]["deliver_to_info"].ToString();
this.txt_cut_off_date.Text = dt.Rows[0]["cut_off_date"].ToString();
this.txt_export_reference.Text = dt.Rows[0]["export_reference"].ToString();
this.hCarrierAcct.Value = dt.Rows[0]["carrier_acct"].ToString();
this.lstCarrierName.Text = dt.Rows[0]["carrier_name"].ToString();
this.txt_carrier_no.Text = dt.Rows[0]["carrier_no"].ToString();
this.txt_place_of_receipt.Text = dt.Rows[0]["place_of_receipt"].ToString();
this.txt_place_of_delivery.Text = dt.Rows[0]["place_of_delivery"].ToString();
this.ddl_origin_port.SelectedValue = dt.Rows[0]["dep_port"].ToString();
this.ddl_dest_port.SelectedValue = dt.Rows[0]["arr_port"].ToString();
if (dt.Rows[0]["eta"] != DBNull.Value)
{
this.txt_eta_date.Text = Convert.ToDateTime(dt.Rows[0]["eta"]).ToShortDateString();
}
if (dt.Rows[0]["etd"] != DBNull.Value)
{
this.txt_etd_date.Text = Convert.ToDateTime(dt.Rows[0]["etd"]).ToShortDateString();
}
this.txt_type_of_move.Text = dt.Rows[0]["move_type"].ToString();
this.hAgentAcct.Value = dt.Rows[0]["dest_agent_acct"].ToString();
this.lstAgentName.Text = dt.Rows[0]["dest_agent_name"].ToString();
this.txtAgentInfo.Text = dt.Rows[0]["dest_agent_info"].ToString();
this.hEmptyPickupAcct.Value = dt.Rows[0]["empty_container_pick_up_acct"].ToString();
this.lstEmptyPickupName.Text = dt.Rows[0]["empty_container_pick_up_name"].ToString();
this.txtEmptyPickupInfo.Text = dt.Rows[0]["empty_container_pick_up_info"].ToString();
this.rbl_dangerous_good.SelectedValue = dt.Rows[0]["dangerous"].ToString();
this.txt_quantity.Text = dt.Rows[0]["quantity"].ToString();
this.ddl_quantity_unit.SelectedValue = dt.Rows[0]["quantity_unit"].ToString();
this.txt_description.Text = dt.Rows[0]["item_desc"].ToString();
this.txt_weight.Text = dt.Rows[0]["gross_weight"].ToString();
this.ddl_weight_scale.SelectedValue = dt.Rows[0]["weight_scale"].ToString();
this.txt_dimension.Text = dt.Rows[0]["dimension"].ToString();
this.ddl_dimension_scale.SelectedValue = dt.Rows[0]["dimension_scale"].ToString();
this.txt_remark.Text = dt.Rows[0]["remark"].ToString();
}
protected void LoadScheduleBList()
{
string sqlTxt = "";
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
if (hOrgID.Value == "" || hOrgID.Value == "0")
{
sqlTxt = "SELECT auto_uid AS sb_id,[description] FROM scheduleB WHERE elt_account_number="
+ elt_account_number + " ORDER BY [description]";
}
else
{
sqlTxt = "IF EXISTS (SELECT * FROM ig_schedule_b WHERE elt_account_number="
+ elt_account_number + " AND org_account_number=" + hOrgID.Value + ")\n"
+ "SELECT sb_id,[description],org_account_number FROM ig_schedule_b WHERE elt_account_number="
+ elt_account_number + " AND org_account_number=" + hOrgID.Value + " ORDER BY [description]\n"
+ "ELSE\n"
+ "SELECT auto_uid AS sb_id,[description] FROM scheduleB WHERE elt_account_number=" + elt_account_number + " ORDER BY [description]";
}
feData.AddToDataSet("ScheduleB", sqlTxt);
ListBox1.DataSource = feData.Tables["ScheduleB"];
ListBox1.DataTextField = "description";
ListBox1.DataValueField = "sb_id";
ListBox1.DataBind();
}
protected void AddNewAES()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
ArrayList tranStrAL = new ArrayList();
string tranStr = "";
tranStr = "SET ANSI_WARNINGS OFF";
tranStrAL.Add(tranStr);
tranStr = @"INSERT INTO aes_master(elt_account_number,party_to_transaction,export_date,tran_ref_no,consignee_acct,inter_consignee_acct,origin_state,dest_country,tran_method,export_port,unloading_port,carrier_id_code,export_carrier,shipment_ref_no,entry_no,hazardous_materials,route_export_tran,in_bond_type,in_bond_no,ftz,last_modified,shipper_acct,file_type,house_num,master_num)
VALUES(" + elt_account_number + ",N'" + RCC.SelectedValue + "',N'" + EDA.Text + "',N'" + BN.Text + "'," + MakeNullCase(hConsigneeAcct.Value) + "," + MakeNullCase(hInterConsigneeAcct.Value) + ",N'" + ST.SelectedValue + "',N'" + COD.SelectedValue + "',N'" + MOT.SelectedValue + "',N'" + POE.SelectedValue + "',N'" + POU.SelectedValue
+ "',N'" + lstCarrier.SelectedValue + "',N'" + lstCarrier.Items[lstCarrier.SelectedIndex].Text + "',N'" + SRN.Text + "','',N'" + HAZ.SelectedValue + "',N'" + RT.SelectedValue + "',N'" + IBT.SelectedValue + "',N'" + IBN.Text + "',N'" + FTZ.Text + "',GETDATE()," + MakeNullCase(hShipperAcct.Value) + ",N'" + hFileType.Value + "',N'" + txtHouse.Text + "',N'" + txtMaster.Text + "')";
tranStrAL.Add(tranStr);
if (!feData.DataTransactions((string[])tranStrAL.ToArray(typeof(string))))
{
txtResultBox.Text = feData.GetLastTransactionError();
txtResultBox.Visible = true;
}
else
{
feData.AddToDataSet("AESInfo", "SELECT * FROM aes_master WHERE elt_account_number=" + elt_account_number + " AND shipment_ref_no=N'" + SRN.Text + "' AND file_type='OE'");
if (feData.Tables["AESInfo"].Rows.Count > 0)
{
SaveLineItems(Convert.ToInt32(feData.Tables["AESInfo"].Rows[0]["auto_uid"]));
Response.Redirect("EditOceanAES.aspx?AESID=" + feData.Tables["AESInfo"].Rows[0]["auto_uid"].ToString());
}
}
}
protected void SetFreightEasyData(GridView control, string txtSQL)
{
// gvHouseResult.Clear();
FEData = new FreightEasy.DataManager.FreightEasyData();
FEData.AddToDataSet("SearchList", txtSQL);
control.DataSource = FEData.Tables["SearchList"].DefaultView;
control.DataBind();
txtResultBox.Text = FEData.Tables["SearchList"].Rows.Count.ToString() + " Records Found.";
btnExcelExport.Visible = true;
}
protected void ListBox1_Load_Data()
{
FreightEasy.DataManager.FreightEasyData FEData = new FreightEasy.DataManager.FreightEasyData();
FEData.AddToDataSet("SetupSteps", "select * from setup_master order by seq_id");
DataTable tmpDt = FEData.Tables["SetupSteps"];
ListBox1.DataSource = tmpDt;
ListBox1.DataTextField = tmpDt.Columns["title"].ToString();
ListBox1.DataValueField = tmpDt.Columns["page_id"].ToString();
ListBox1.DataBind();
}
protected void Page_Init(object sender, EventArgs e)
{
elt_account_number = Request.Cookies["CurrentUserInfo"]["elt_account_number"];
user_id = Request.Cookies["CurrentUserInfo"]["user_id"];
user_right = Request.Cookies["CurrentUserInfo"]["user_right"];
login_name = Request.Cookies["CurrentUserInfo"]["login_name"];
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("Banks", "SELECT * FROM gl WHERE elt_account_number=" + elt_account_number + " AND gl_account_type='Cash in Bank'");
lstBanks.DataSource = feData.Tables["Banks"];
lstBanks.DataTextField = "gl_account_desc";
lstBanks.DataValueField = "gl_account_number";
lstBanks.DataBind();
feData.AddToDataSet("Revenues", "SELECT * FROM gl WHERE elt_account_number=" + elt_account_number + " AND gl_account_type='Other Revenue'");
lstRevenues.DataSource = feData.Tables["Revenues"];
lstRevenues.DataTextField = "gl_account_desc";
lstRevenues.DataValueField = "gl_account_number";
lstRevenues.DataBind();
}
protected void SetFreightEasyData(string txtSQL)
{
UltraWebGrid1.Clear();
FEData = new FreightEasy.DataManager.FreightEasyData();
FEData.AddToDataSet("SearchList", txtSQL);
UltraWebGrid1.DataSource = FEData.Tables["SearchList"].DefaultView;
UltraWebGrid1.DataBind();
UltraWebGrid1.DisplayLayout.Pager.CurrentPageIndex = 1;
txtResultBox.Text = FEData.Tables["SearchList"].Rows.Count.ToString() + " Records Found.";
btnExcelExport.Visible = true;
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Email")
{
FreightEasy.DataManager.FreightEasyData fe = new FreightEasy.DataManager.FreightEasyData();
fe.AddToDataSet("SetupSession", "SELECT * FROM setup_session WHERE sid='" + e.CommandArgument.ToString() + "'");
DataRow dr = fe.Tables["SetupSession"].Rows[0];
string sid = dr["sid"].ToString();
string email = dr["email"].ToString();
string password = dr["password"].ToString();
string elt_account_number = dr["elt_account_number"].ToString();
string body = @"
<html>
<head>
<title>FreightEasy Online Application (Setup Information)</title>
<style type='text/css'>
.style3 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 9px;}
</style>
</head>
<body>
<p class='style3'><strong>FreightEasy Online Application (Setup Information)</strong></p>
<p />
<table class='style3' width='100%' border='0' cellspacing='0' cellpadding='0'>
<tr>
<td colspan='2'>
<a href='http://" + http_host + @"/IFF_MAIN/Authentication/SetupLogin.aspx?sid=" + sid + @"'>click to login</a>
<p />Email Address: " + email + @"
<p />Password: "******"</td>
</tr>
<tr>
<td align='left' valign='bottom'>
<span class='style3'>This message was sent by E-LOGISTICS TECHNOLOGY. (<a href='mailto:[email protected]'>[email protected]</a>)</span>
</td>
<td align='right' valign='top'>
<a href='http://www.e-logitech.net target='_blank'><img src='http://www.e-logitech.net/elt_email/images/powered_logo.gif' width='123' height='50' border='0' /></a>
</td>
</tr>
</table>
</body>
</html>";
MailMessage message = new MailMessage("*****@*****.**", email, "FreightEasy Online Application (Setup Information)", body);
message.IsBodyHtml = true;
SmtpClient client = new SmtpClient("localhost", 25);
client.Send(message);
}
}
protected void CheckExistingAES()
{
if (AESID == "" || AESID == "0")
{
string aesFindSQL = @"SELECT * FROM aes_master WHERE elt_account_number=" + elt_account_number
+ " AND file_type='OE' AND house_num=N'" + txtHouse.Text + "' AND master_num=N'" + txtMaster.Text + "'";
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("AESEntity", aesFindSQL);
if (feData.Tables["AESEntity"].Rows.Count > 0)
{
DataRow drTmp = feData.Tables["AESEntity"].Rows[0];
AESID = drTmp["auto_uid"].ToString();
}
}
}
protected string GetAgentInfo()
{
string agent_info = "";
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("AgentInfo", "SELECT * FROM agent WHERE elt_account_number=" + elt_account_number);
if (feData.Tables["AgentInfo"].Rows.Count > 0)
{
DataRow dr = feData.Tables["AgentInfo"].Rows[0];
agent_info = dr["dba_name"] + "\n" + dr["business_address"] + "\n" + dr["business_city"] + "," +
dr["business_state"] + " " + dr["business_zip"] + " " + dr["business_country"];
}
return(agent_info);
}
protected void Insert_Booking_Confirm()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
ArrayList tranStrAL = new ArrayList();
string tranStr = "";
tranStr = "SET ANSI_WARNINGS OFF";
tranStrAL.Add(tranStr);
tranStr = @"INSERT INTO booking_confirm(elt_account_number,document_date,air_ocean,bc_no,booking_no,master_no,house_no,sub_house_no,shipper_acct,
shipper_name,shipper_info,deliver_to_acct,deliver_to_name,deliver_to_info,cut_off_date,export_reference,carrier_acct,carrier_name,carrier_no,
place_of_receipt,place_of_delivery,dep_port,arr_port,eta,etd,move_type,dest_agent_acct,dest_agent_name,dest_agent_info,empty_container_pick_up_acct,
empty_container_pick_up_name,empty_container_pick_up_info,dangerous,quantity,quantity_unit,item_desc,gross_weight,weight_scale,dimension,dimension_scale,remark)
VALUES(" + elt_account_number + "," + MakeDateString(this.txt_document_date.Text) + ",'O',N'" + this.ddl_prefix.Items[this.ddl_prefix.SelectedIndex].Text
+ "-" + this.ddl_prefix.SelectedValue + "',N'" + this.txt_booking_no.Text + "',N'" + this.hf_master_no.Value + "',N'" + this.hf_house_no.Value
+ "',N'" + this.hf_sub_house_no.Value + "'," + MakeNullCase(this.hShipperAcct.Value) + ",N'" + this.lstShipperName.Text
+ "',N'" + this.txtShipperInfo.Text + "'," + MakeNullCase(this.hConsigneeAcct.Value) + ",N'" + this.lstConsigneeName.Text
+ "',N'" + this.txtConsigneeInfo.Text + "',N'" + this.txt_cut_off_date.Text + "',N'" + this.txt_export_reference.Text
+ "'," + MakeNullCase(this.hCarrierAcct.Value) + ",N'" + this.lstCarrierName.Text + "',N'" + this.txt_carrier_no.Text
+ "',N'" + this.txt_place_of_receipt.Text + "',N'" + this.txt_place_of_delivery.Text + "',N'" + this.ddl_origin_port.SelectedValue
+ "',N'" + this.ddl_dest_port.SelectedValue + "'," + MakeDateString(this.txt_eta_date.Text)
+ "," + MakeDateString(this.txt_etd_date.Text) + ",N'" + this.txt_type_of_move.Text
+ "'," + MakeNullCase(this.hAgentAcct.Value) + ",N'" + this.lstAgentName.Text + "',N'" + this.txtAgentInfo.Text
+ "'," + MakeNullCase(this.hEmptyPickupAcct.Value) + ",N'" + this.lstEmptyPickupName.Text + "',N'" + this.txtEmptyPickupInfo.Text
+ "',N'" + this.rbl_dangerous_good.SelectedValue + "'," + MakeNullCase(this.txt_quantity.Text) + ",N'" + this.ddl_quantity_unit.SelectedValue + "',N'" + this.txt_description.Text
+ "'," + MakeNullCase(this.txt_weight.Text) + ",N'" + this.ddl_weight_scale.SelectedValue + "'," + MakeNullCase(this.txt_dimension.Text)
+ ",N'" + this.ddl_dimension_scale.SelectedValue + "',N'" + this.txt_remark.Text + "')";
tranStrAL.Add(tranStr);
tranStr = "UPDATE user_prefix SET next_no=next_no+1 WHERE elt_account_number=" + elt_account_number
+ "AND type='BC' AND prefix=N'" + this.ddl_prefix.Items[this.ddl_prefix.SelectedIndex].Text + "'";
tranStrAL.Add(tranStr);
tranStr = "SET ANSI_WARNINGS ON";
tranStrAL.Add(tranStr);
if (!feData.DataTransactions((string[])tranStrAL.ToArray(typeof(string))))
{
Response.Write(feData.GetLastTransactionError());
}
else
{
feData.AddToDataSet("BookingConfirmNew", "SELECT * FROM booking_confirm WHERE auto_uid=IDENT_CURRENT('booking_confirm')");
Load_Booking_Confirm(feData.Tables["BookingConfirmNew"].Rows[0]["auto_uid"].ToString());
}
}
protected void LoadAllData()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("SBInfo", "SELECT * FROM scheduleB WHERE elt_account_number=" + elt_account_number + " AND auto_uid=" + hSBID.Value);
if (feData.Tables["SBInfo"].Rows.Count > 0)
{
DataRow drSB = feData.Tables["SBInfo"].Rows[0];
txtSBCode.Text = drSB["sb"].ToString();
txtItemDesc.Text = drSB["description"].ToString();
lstSBUnit1.SelectedValue = drSB["sb_unit1"].ToString();
lstSBUnit2.SelectedValue = drSB["sb_unit2"].ToString();
lstExportCode.SelectedValue = drSB["export_code"].ToString();
lstLicenseType.SelectedValue = drSB["license_type"].ToString();
txtECCN.Text = drSB["eccn"].ToString();
}
}
protected void Load_Ports()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("Ports", "select * from port where elt_account_number=" + elt_account_number);
ddl_dest_port.DataSource = feData.Tables["Ports"];
ddl_dest_port.DataTextField = "port_desc";
ddl_dest_port.DataValueField = "port_code";
ddl_dest_port.DataBind();
ddl_dest_port.Items.Insert(0, new ListItem("", ""));
ddl_origin_port.DataSource = feData.Tables["Ports"];
ddl_origin_port.DataTextField = "port_desc";
ddl_origin_port.DataValueField = "port_code";
ddl_origin_port.DataBind();
ddl_origin_port.Items.Insert(0, new ListItem("", ""));
}
protected void Page_Init(object sender, EventArgs e)
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("Countries", "SELECT DISTINCT country_name,country_code FROM all_country_code ORDER BY country_name");
ddCountry1.DataSource = feData.Tables["Countries"];
ddCountry1.DataTextField = "country_name";
ddCountry1.DataValueField = "country_code";
ddCountry1.DataBind();
ddCountry1.Items.Insert(0, new ListItem("", ""));
ddCountry2.DataSource = feData.Tables["Countries"];
ddCountry2.DataTextField = "country_name";
ddCountry2.DataValueField = "country_code";
ddCountry2.DataBind();
ddCountry2.Items.Insert(0, new ListItem("", ""));
}
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
FreightEasy.DataManager.FreightEasyData FEData = new FreightEasy.DataManager.FreightEasyData();
FEData.AddToDataSet("PageEntry", "select * from setup_master where page_id=" + ListBox1.SelectedValue);
DataTable tmpDt = FEData.Tables["PageEntry"];
if (tmpDt != null)
{
hSeqId.Value = tmpDt.Rows[0]["seq_id"].ToString();
hPageId.Value = tmpDt.Rows[0]["page_id"].ToString();
txtPageTitle.Text = tmpDt.Rows[0]["title"].ToString();
txtPageURL.Text = tmpDt.Rows[0]["setup_url"].ToString();
txtValidURL.Text = tmpDt.Rows[0]["valid_url"].ToString();
txtRemark.Text = tmpDt.Rows[0]["remark"].ToString();
lstSetupType.SelectedValue = tmpDt.Rows[0]["setup_type"].ToString();
}
}
protected void Load_Prefix()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("PrefixTable", "select * from user_prefix where elt_account_number=" + elt_account_number
+ " and type='BC'");
ddl_prefix.DataSource = feData.Tables["PrefixTable"];
ddl_prefix.DataTextField = "prefix";
ddl_prefix.DataValueField = "next_no";
ddl_prefix.DataBind();
if (feData.Tables["PrefixTable"].Rows.Count == 0)
{
string script = "<script type='text/javascript'>no_prefix_alert();</script>";
this.ClientScript.RegisterClientScriptBlock(this.GetType(), "", script);
}
else
{
lbl_bc_no.Text = ddl_prefix.SelectedValue;
}
}
protected void UltraWebGrid1_InitializeLayout(object sender, LayoutEventArgs e)
{
UltraWebGrid1.Bands[0].Columns.FromKey("gl_account_balance").Hidden = true;
UltraGridColumn GLMasterType = UltraWebGrid1.Bands[0].Columns.FromKey("gl_master_type");
UltraGridColumn GLAccountType = UltraWebGrid1.Bands[0].Columns.FromKey("gl_account_type");
GLAccountType.Type = ColumnType.DropDownList;
// start getting gl types
FreightEasy.DataManager.FreightEasyData glTypes = new FreightEasy.DataManager.FreightEasyData();
glTypes.AddToDataSet("GLTypes", "select * from gl_reference");
DataTable GLTypeTable = glTypes.Tables["GLTypes"];
// end of getting gl types
GLAccountType.ValueList.DataSource = GLTypeTable;
GLAccountType.ValueList.DisplayMember = GLTypeTable.Columns["gl_type"].ToString();
GLAccountType.ValueList.ValueMember = GLTypeTable.Columns["gl_type"].ToString();
GLAccountType.ValueList.DataBind();
GLAccountType.ValueList.Style.Font.Size = FontUnit.Point(8);
}
protected void btnPDFExport_Click(object sender, ImageClickEventArgs e)
{
ReportDocument rd = new ReportDocument();
string rptFile = Server.MapPath("/IFF_MAIN/CrystalReportResources/rpt/AESList.rpt");
string xsdFile = Server.MapPath("/IFF_MAIN/CrystalReportResources/xsd/AESList.xsd");
AESListDS = new FreightEasy.DataManager.FreightEasyData();
string selectSQL = @"SELECT a.*, b.dba_name AS consignee_name, c.dba_name AS inter_consignee_name
FROM aes_master a LEFT OUTER JOIN organization b ON (a.elt_account_number=b.elt_account_number AND a.consignee_acct=b.org_account_number)
LEFT OUTER JOIN organization c ON (a.elt_account_number=c.elt_account_number AND a.inter_consignee_acct=c.org_account_number)
WHERE a.elt_account_number=" + elt_account_number + " AND ISNULL(file_type,'')='AE' ORDER BY a.last_modified desc";
AESListDS.AddToDataSet("AESList", selectSQL);
try
{
rd.Load(rptFile, OpenReportMethod.OpenReportByTempCopy);
rd.SetDataSource(AESListDS.Copy());
AESListDS.WriteXmlSchema(xsdFile);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Type", "application/pdf");
Response.AddHeader("Content-disposition", "attachment;filename=AESList.pdf");
MemoryStream oStream = (MemoryStream)rd.ExportToStream(ExportFormatType.PortableDocFormat);
Response.BinaryWrite(oStream.ToArray());
}
catch { }
finally
{
rd.Close();
Response.Flush();
Response.End();
}
}
protected void LoadGeneralJournalEntry()
{
entry_no = "";
if (Request.Params["EntryNo"] != null)
{
entry_no = Request.Params["EntryNo"].ToString();
hEntryNo.Value = entry_no;
btnDeleteTop.Visible = true;
btnDeleteBot.Visible = true;
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
feData.AddToDataSet("EntryInfo", "SELECT a.*,b.dba_name FROM general_journal_entry a LEFT OUTER JOIN organization b ON (a.elt_account_number=b.elt_account_number AND a.org_acct=b.org_account_number) WHERE a.elt_account_number=" + elt_account_number + " AND a.entry_no=" + entry_no + " ORDER BY credit");
if (feData.Tables["EntryInfo"].Rows.Count == 2)
{
DataRow drTmp = feData.Tables["EntryInfo"].Rows[0];
lstVendorName.Text = drTmp["dba_name"].ToString();
hVendorAcct.Value = drTmp["org_acct"].ToString();
lstBanks.SelectedValue = drTmp["gl_account_number"].ToString();
lstRevenues.SelectedValue = feData.Tables["EntryInfo"].Rows[1]["gl_account_number"].ToString();
txtAmount.Text = drTmp["debit"].ToString();
txtMemo.Text = drTmp["memo"].ToString();
}
}
}
protected void GetShipOutData()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
string txtSQL = "SELECT * FROM warehouse_shipout WHERE elt_account_number="
+ elt_account_number + " AND so_num=N'" + SONum + "'";
feData.AddToDataSet("ShipOut", txtSQL);
if (feData.Tables["ShipOut"].Rows.Count > 0)
{
DataRow drTmp = feData.Tables["ShipOut"].Rows[0];
txtSONum.Text = drTmp["so_num"].ToString();
if (drTmp["customer_acct"].ToString() != "")
{
txtSQL = "SELECT * FROM organization WHERE elt_account_number="
+ elt_account_number + " AND org_account_number=" + drTmp["customer_acct"].ToString();
feData.AddToDataSet("ShipperInfo", txtSQL);
if (feData.Tables["ShipperInfo"].Rows.Count > 0)
{
DataRow drShipperTmp = feData.Tables["ShipperInfo"].Rows[0];
txtShipper.Text = drShipperTmp["dba_name"].ToString();
}
}
if (drTmp["consignee_acct"].ToString() != "")
{
txtSQL = "SELECT * FROM organization WHERE elt_account_number="
+ elt_account_number + " AND org_account_number=" + drTmp["consignee_acct"].ToString();
feData.AddToDataSet("ConsigneeInfo", txtSQL);
if (feData.Tables["ConsigneeInfo"].Rows.Count > 0)
{
DataRow drConsigneeTmp = feData.Tables["ConsigneeInfo"].Rows[0];
txtConsignee.Text = drConsigneeTmp["dba_name"].ToString();
}
}
txtSQL = "SELECT * FROM warehouse_receipt a LEFT OUTER JOIN warehouse_history b "
+ "ON (a.elt_account_number=b.elt_account_number AND a.wr_num=b.wr_num) "
+ "WHERE a.elt_account_number=" + elt_account_number + " AND b.so_num=N'"
+ drTmp["so_num"].ToString() + "' AND history_type='Ship-out Made'";
feData.AddToDataSet("WarehouseReceipts", txtSQL);
int item_qty = 0;
for (int i = 0; i < feData.Tables["WarehouseReceipts"].Rows.Count; i++)
{
DataRow drWRTmp = feData.Tables["WarehouseReceipts"].Rows[i];
if (drWRTmp["item_desc"].ToString() != "")
{
txtItemDesc.Text = txtItemDesc.Text + drWRTmp["item_desc"].ToString() + "\n";
}
if (drWRTmp["handling_info"].ToString() != "")
{
txtHandling.Text = txtHandling.Text + drWRTmp["handling_info"].ToString() + "\n";
}
item_qty = item_qty + int.Parse(drWRTmp["item_piece_shipout"].ToString());
}
txtItemQty.Text = item_qty.ToString();
}
}
protected void InitializeAll()
{
FreightEasy.DataManager.FreightEasyData feData = new FreightEasy.DataManager.FreightEasyData();
string CustomersSQL = "";
DataRow drTmp = null;
// Consolidated House Item
if (txtHAWB.Text != "")
{
CustomersSQL = @"SELECT a.org_account_number,CASE WHEN isnull(a.class_code,'')='' THEN a.dba_name ELSE a.dba_name + '[' + RTRIM(LTRIM(isnull(a.class_code,''))) + ']' END AS dba_name
FROM organization a LEFT OUTER JOIN hawb_master b ON (a.elt_account_number=b.elt_account_number AND a.org_account_number=b.agent_no) WHERE a.elt_account_number=" + elt_account_number +
" AND b.mawb_num=N'" + txtMAWB.Text + "' GROUP BY a.org_account_number,a.dba_name,a.class_code";
feData.AddToDataSet("Customers", CustomersSQL);
if (feData.Tables["Customers"].Rows.Count > 0)
{
lstCustomer.DataSource = feData.Tables["Customers"];
lstCustomer.DataTextField = "dba_name";
lstCustomer.DataValueField = "org_account_number";
lstCustomer.DataBind();
lstCustomer.Items.Insert(0, new ListItem("", ""));
}
feData.AddToDataSet("HouseInfo", "SELECT * FROM hawb_master WHERE elt_account_number=" + elt_account_number + " AND mawb_num=N'" + txtMAWB.Text + "' AND hawb_num=N'" + txtHAWB.Text + "'");
if (feData.Tables["HouseInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["HouseInfo"].Rows[0];
lstCustomer.SelectedValue = drTmp["agent_no"].ToString();
txtWeight.Text = drTmp["total_chargeable_weight"].ToString();
txtWeightScale.Text = drTmp["weight_scale"].ToString();
}
// Other Charge
if (txtItem.Text != "")
{
feData.AddToDataSet("ItemInfo", "SELECT * FROM hawb_other_charge WHERE elt_account_number=" + elt_account_number + " AND hawb_num=N'" + txtHAWB.Text + "' AND tran_no=" + txtItem.Text);
if (feData.Tables["ItemInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["ItemInfo"].Rows[0];
txtItemName.Text = drTmp["charge_desc"].ToString();
txtItemAmt.Text = drTmp["amt_hawb"].ToString();
lstCostItem.Text = drTmp["cost_desc"].ToString();
hCostItem.Value = drTmp["cost_code"].ToString();
txtCostAmt.Text = drTmp["cost_amt"].ToString();
}
}
// Weight Charge
else
{
feData.AddToDataSet("ItemInfo", "SELECT * FROM hawb_weight_charge WHERE elt_account_number=" + elt_account_number + " AND hawb_num=N'" + txtHAWB.Text + "'");
if (feData.Tables["ItemInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["ItemInfo"].Rows[0];
txtItemName.Text = "Freight Charge";
txtItemAmt.Text = drTmp["total_charge"].ToString();
lstCostItem.Text = drTmp["cost_desc"].ToString();
hCostItem.Value = drTmp["cost_code"].ToString();
txtCostAmt.Text = drTmp["cost_amt"].ToString();
}
}
}
// Direct Shipment
else
{
CustomersSQL = @"SELECT a.org_account_number,CASE WHEN isnull(a.class_code,'')='' THEN a.dba_name ELSE a.dba_name + '[' + RTRIM(LTRIM(isnull(a.class_code,''))) + ']' END AS dba_name
FROM organization a LEFT OUTER JOIN mawb_master b ON (a.elt_account_number=b.elt_account_number AND a.org_account_number=b.shipper_account_number) WHERE a.elt_account_number=" + elt_account_number +
" AND b.mawb_num=N'" + txtMAWB.Text + "' GROUP BY a.org_account_number,a.dba_name,a.class_code";
feData.AddToDataSet("Customers", CustomersSQL);
if (feData.Tables["Customers"].Rows.Count > 0)
{
lstCustomer.DataSource = feData.Tables["Customers"];
lstCustomer.DataTextField = "dba_name";
lstCustomer.DataValueField = "org_account_number";
lstCustomer.DataBind();
lstCustomer.Items.Insert(0, new ListItem("", ""));
lstCustomer.SelectedIndex = 1;
}
feData.AddToDataSet("MasterInfo", "SELECT * FROM mawb_master WHERE elt_account_number=" + elt_account_number + " AND mawb_num=N'" + txtMAWB.Text + "' AND mawb_num=N'" + txtMAWB.Text + "'");
if (feData.Tables["MasterInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["MasterInfo"].Rows[0];
txtWeight.Text = drTmp["total_chargeable_weight"].ToString();
txtWeightScale.Text = drTmp["weight_scale"].ToString();
}
// Other Charge
if (txtItem.Text != "")
{
feData.AddToDataSet("ItemInfo", "SELECT * FROM mawb_other_charge WHERE elt_account_number=" + elt_account_number + " AND mawb_num=N'" + txtMAWB.Text + "' AND tran_no=" + txtItem.Text);
if (feData.Tables["ItemInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["ItemInfo"].Rows[0];
txtItemName.Text = drTmp["charge_desc"].ToString();
txtItemAmt.Text = drTmp["amt_mawb"].ToString();
lstCostItem.Text = drTmp["cost_desc"].ToString();
hCostItem.Value = drTmp["cost_code"].ToString();
txtCostAmt.Text = drTmp["cost_amt"].ToString();
}
}
// Weight Charge
else
{
feData.AddToDataSet("ItemInfo", "SELECT * FROM mawb_weight_charge WHERE elt_account_number=" + elt_account_number + " AND mawb_num=N'" + txtMAWB.Text + "'");
if (feData.Tables["ItemInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["ItemInfo"].Rows[0];
txtItemName.Text = "Freight Charge";
txtItemAmt.Text = drTmp["total_charge"].ToString();
lstCostItem.Text = drTmp["cost_desc"].ToString();
hCostItem.Value = drTmp["cost_code"].ToString();
txtCostAmt.Text = drTmp["cost_amt"].ToString();
}
}
}
feData.AddToDataSet("BookingInfo", "SELECT origin_port_id,dest_port_id,carrier_code,carrier_desc FROM mawb_number WHERE elt_account_number=" + elt_account_number + " AND mawb_no=N'" + txtMAWB.Text + "'");
if (feData.Tables["BookingInfo"].Rows.Count > 0)
{
drTmp = feData.Tables["BookingInfo"].Rows[0];
txtFrom.Text = drTmp["origin_port_id"].ToString();
txtTo.Text = drTmp["dest_port_id"].ToString();
txtCarrierCode.Text = drTmp["carrier_code"].ToString();
txtCarrierDesc.Text = drTmp["carrier_desc"].ToString();
}
}
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);
}