private void metroButton2_Click(object sender, EventArgs e)
{
string ConStr = "Database = " + metroTextBox3.Text.Trim() + ";User ID=inetsoft;Password = etl5boxes; server = " + metroTextBox3.Text.Trim().ToLower() + "." + metroTextBox3.Text.Trim().ToLower() + ".ei:50000; Max Pool Size=100;Persist security info =False;Pooling = True ";
string Query = @"Select
sh.SHPMNT_REF,
ship.NAME,
ship.ADDR_LINE1,
ship.ADDR_LINE2,
CONCAT(CONCAT(ship.CITY, ','), ship.COUNTRY),
cons.NAME,
cons.ADDR_LINE1,
cons.ADDR_LINE2,
CONCAT(CONCAT(cons.CITY, ','), cons.COUNTRY),
hdr.ORIGIN_NAME_AWB,
hdr.DESTIN_NAME_AWB,
hdr.FLIGHT_TO_AWB_1,
hdr.FLIGHT_BY_AWB_1,
hdr.CHRG_CODE_FRGHT_1,
hdr.CHRG_CODE_OTHER_1,
sh.PCS_GRS,
CAST(ROUND(sh.WGT_GRS_2 / 10000, 0) AS DECIMAL(18, 1)),
CAST(ROUND(sh.WGT_CHRG_2 / 10000, 0) AS DECIMAL(18, 1)),
hdr.FLIGHT_NUM_AWB_1,
hdr.HNDL_INFO_AWB_1,
hdr.HNDL_INFO_AWB_2,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( '!',concat(concat(itm.REMARKS_AWB_PFX,' '),itm.REMARKS_AWB)))) as VARCHAR(1024)), 3),
sl.MAWB_BL_NO
From
EXPORT.SHPMNT_HDR sh
inner join IASDB.SHIP_LOG sl on sl.INVOICE_NO = sh.SHPMNT_REF
inner join HELPDB.CLIENT ship on ship.CLIENT_NO = sh.SHIPPER_ID
inner join HELPDB.CLIENT cons on cons.CLIENT_NO = sh.CONSIGN_ID
inner join EXPORT.AIR_FRGHT_HDR hdr on hdr.INVOICE_REF = sh.SHPMNT_REF
inner join EXPORT.AIR_FRGHT_ITEM itm on itm.CARRIER_REF = hdr.CARRIER_REF
where sh.SHPMNT_REF = '" + metroTextBox1.Text.Trim() + @"'
Group by
ship.NAME,
ship.ADDR_LINE1,
ship.ADDR_LINE2,
ship.CITY,
ship.COUNTRY,
sh.SHPMNT_REF,
cons.NAME,
cons.ADDR_LINE1,
cons.ADDR_LINE2,
cons.city,
cons.COUNTRY,
CAST(ROUND(sh.WGT_GRS_2 / 10000, 0) AS DECIMAL(18, 1)),
CAST(ROUND(sh.WGT_CHRG_2 / 10000, 0) AS DECIMAL(18, 1)),
hdr.ORIGIN_NAME_AWB,
hdr.DESTIN_NAME_AWB,
hdr.FLIGHT_TO_AWB_1,
hdr.FLIGHT_BY_AWB_1,
hdr.CHRG_CODE_FRGHT_1,
hdr.CHRG_CODE_OTHER_1,
hdr.FLIGHT_NUM_AWB_1,
sh.PCS_GRS,
hdr.HNDL_INFO_AWB_1,
hdr.HNDL_INFO_AWB_2,
sl.MAWB_BL_NO
";
using (DB2Connection myconnection = new DB2Connection(ConStr))
{
myconnection.Open();
DB2Command cmd = myconnection.CreateCommand();
cmd.CommandText = Query;
DB2DataReader rd = cmd.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
//richTextBox1.Text = richTextBox1.Text + rd.GetValue(0).ToString() + " " + rd.GetValue(1).ToString();
Excel.Application app = new Excel.Application();
//string filepath = @"HAWBTemp.xlsx";
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + "/HAWBTemplate.xlsx", ReadOnly: false, Editable: true);
Excel.Worksheet worksheet = workbook.Worksheets.Item[1];
//worksheet.Cells[5, 7].Value = rd.GetValue(1).ToString();
//Shipper Update
worksheet.Range["D1"].Value = rd.GetValue(22).ToString();
worksheet.Range["N1"].Value = rd.GetValue(0).ToString();
worksheet.Range["M51"].Value = rd.GetValue(0).ToString();
worksheet.Range["B3"].Value = rd.GetValue(1).ToString();
worksheet.Range["B4"].Value = rd.GetValue(2).ToString();
worksheet.Range["B5"].Value = rd.GetValue(3).ToString();
worksheet.Range["B6"].Value = rd.GetValue(4).ToString();
//Consignee Update
worksheet.Range["B8"].Value = rd.GetValue(5).ToString();
worksheet.Range["B9"].Value = rd.GetValue(6).ToString();
worksheet.Range["B10"].Value = rd.GetValue(7).ToString();
worksheet.Range["B11"].Value = rd.GetValue(8).ToString();
//Origin-Destination
worksheet.Range["B18"].Value = rd.GetValue(9).ToString();
worksheet.Range["B21"].Value = rd.GetValue(10).ToString();
//Dest-First Flight
worksheet.Range["B19"].Value = rd.GetValue(11).ToString();
worksheet.Range["C19"].Value = rd.GetValue(12).ToString();
//Prepaid-Collect
if (rd.GetValue(13).ToString() == "P")
{
worksheet.Range["K19"].Value = " P";
}
else
{
worksheet.Range["K19"].Value = " C";
}
if (rd.GetValue(14).ToString() == "P")
{
worksheet.Range["L19"].Value = "P";
}
else
{
worksheet.Range["L19"].Value = " C";
}
//Weight-Pieces
worksheet.Range["B27"].Value = rd.GetValue(15).ToString();
worksheet.Range["C27"].Value = rd.GetValue(16).ToString();
worksheet.Range["G27"].Value = rd.GetValue(17).ToString();
worksheet.Range["B37"].Value = rd.GetValue(15).ToString();
worksheet.Range["C37"].Value = rd.GetValue(16).ToString();
//Flight
worksheet.Range["F21"].Value = rd.GetValue(18).ToString();
//Handling Information
worksheet.Range["C23"].Value = rd.GetValue(19).ToString();
worksheet.Range["C24"].Value = rd.GetValue(20).ToString();
worksheet.Range["O46"].Value = metroTextBox2.Text;
//Data
DateTime dt = DateTime.Now;
worksheet.Range["I49"].Value = dt.ToShortDateString();
//Description
String description = rd.GetValue(21).ToString();
List <string> descResult = description.Split('!').ToList();
descResult = descResult.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList();
//richTextBox1.Text = richTextBox1.Text + descResult[0] +"///////" + descResult[1]+ "//////" + descResult[2];
for (int i = descResult.Count; i <= 7; i++)
{
descResult.Add("");
}
worksheet.Range["B28"].Value = descResult[0];
worksheet.Range["B29"].Value = descResult[1];
worksheet.Range["B30"].Value = descResult[2];
worksheet.Range["B31"].Value = descResult[3];
worksheet.Range["B32"].Value = descResult[4];
worksheet.Range["B33"].Value = descResult[5];
worksheet.Range["B34"].Value = descResult[6];
worksheet.Range["B35"].Value = descResult[7];
/*String remarks = rd.GetValue(22).ToString();
* List<string> remarksResult = remarks.Split('!').ToList();
* remarksResult = remarksResult.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList();
* for (int i = remarksResult.Count; i <= 7; i++)
* {
* remarksResult.Add("");
* }
*
* MetroMessageBox.Show(this,remarksResult[1]);
* MetroMessageBox.Show(this, remarksResult[2]);
* MetroMessageBox.Show(this, remarksResult[3]);
* MetroMessageBox.Show(this, remarksResult[4]);
* MetroMessageBox.Show(this, remarksResult[5]);
*
* worksheet.Range["M28"].Value = remarksResult[0];
* worksheet.Range["M29"].Value = remarksResult[1];
* worksheet.Range["M30"].Value = remarksResult[2];
* worksheet.Range["M31"].Value = remarksResult[3];
* worksheet.Range["M32"].Value = remarksResult[4];
* worksheet.Range["M33"].Value = remarksResult[5];
* worksheet.Range["M34"].Value = remarksResult[6];
* worksheet.Range["M35"].Value = remarksResult[7];*/
workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "/" + metroTextBox1.Text.Trim() + ".pdf");
app.Application.ActiveWorkbook.Save();
workbook.Close(0);
app.Application.Quit();
app.Quit();
MetroFramework.MetroMessageBox.Show(this, "House Created!");
}
}
}
}
public override bool GetBoolean(int ordinal)
{
var result = source.GetValue(ordinal);
return(Convert.ToBoolean(result));
}