private void BtnPrintVouchersAndTickets_Click(object sender, EventArgs e)
{
Button button = sender as Button;
bool closeForm = true;
if ((button == BtnPrintHotelVouchers) || (button == BtnPrintBoth))
{
//MessageBox.Show("Button pressed = " + button.Text, "PRINTING HOTEL VOUCHERS", MessageBoxButtons.OK, MessageBoxIcon.Information);
/* open connection */
try
{
mySqlConnection.Open();
}
catch (Exception errConnOpen)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnOpen.Message);
return;
}
/* now execute query to obtain data from hotel booking table
* and fill t in dataset
*/
string mySqlQueryString = "SELECT DISTINCT `idhotelinfo` FROM `hotelbookinginfo` WHERE `queryid` = '" + queryId + "' ORDER BY `idhotelinfo`";
DataSet dataset = new DataSet();
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlQueryString, mySqlConnection);
mySqlDataAdapter.Fill(dataset, "TABLE_HOTEL_ID");
if ((dataset == null) || (dataset.Tables["TABLE_HOTEL_ID"].Rows.Count < 1))
{
MessageBox.Show("No Hotel information present for QueryId = " + queryId, "No Data Present", MessageBoxButtons.OK, MessageBoxIcon.Warning);
try
{
mySqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnClose.Message);
}
return;
}
saveFileDialogItinerary.Title = "HOTEL VOUCHER FILE NAME";
if (saveFileDialogItinerary.ShowDialog() == DialogResult.OK)
{
/* file selected */
Debug.WriteLine("OUT FILE SELECTED : " + saveFileDialogItinerary.FileName);
}
else
{
Debug.WriteLine("File not selected thus returning");
try
{
mySqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnClose.Message);
}
return;
}
string imagePath = saveFileDialogItinerary.FileName + "logo.png";
Properties.Resources.ExcursionHolidaysSmallLogo.Save(imagePath);
Document document = new Document();
document.Info.Title = "HOTEL VOUCHER FOR " + queryId;
/* now cange the style of the document */
MyPdfDocuments.DefineStyles(document);
/* add section to document */
Section section = document.AddSection();
/* add hotel booking details.
* add different page for different hotel
*/
for (int pageNumber = 0; pageNumber < dataset.Tables["TABLE_HOTEL_ID"].Rows.Count; pageNumber++)
{
mySqlQueryString = "SELECT * FROM `hotelbookinginfo` WHERE `queryid` = '" + queryId + "' AND idhotelinfo = " + dataset.Tables["TABLE_HOTEL_ID"].Rows[pageNumber]["idhotelinfo"].ToString();
mySqlDataAdapter = new MySqlDataAdapter(mySqlQueryString, mySqlConnection);
string tableNameHotelRates = "TABLE_HOTEL_ID_" + pageNumber.ToString();
mySqlDataAdapter.Fill(dataset, tableNameHotelRates);
mySqlQueryString = "SELECT `hoteladdress`, `hotelname` FROM `hotelinfo` WHERE `idhotelinfo` = " + dataset.Tables["TABLE_HOTEL_ID"].Rows[pageNumber]["idhotelinfo"].ToString();
mySqlDataAdapter = new MySqlDataAdapter(mySqlQueryString, mySqlConnection);
string tableNameHotelAddress = "HOTEL_ADDRESS_" + pageNumber.ToString();
mySqlDataAdapter.Fill(dataset, tableNameHotelAddress);
if (dataset.Tables[tableNameHotelRates].Rows.Count < 1)
{
continue;
}
if (pageNumber > 0)
{
section.AddPageBreak();
}
Paragraph paragraph = section.AddParagraph("HOTEL VOUCHER", "Heading1");
/* enter reference details in tabular form */
Table table = null;
Column column = null;
int columnCount = 0; // 1;
double columnWidth = 0; // (21.0 - 2.0) / columnCount;
Row row = null;
int rowsCount = 0;
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 3;
columnWidth = (21.0 - 2.0) / columnCount;
for (int index = 0; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
}
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Reference No", "Heading2");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("Confirmed By", "Heading2");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Hotel Confirmation No", "Heading2");
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText(queryId, "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["confirmby"].ToString(), "Heading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["idconfirmation"].ToString(), "Heading3");
/* Add contant for the table information */
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
/* add hotel address and agency address in table */
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
columnCount = 2;
columnWidth = (21.0 - 2.0) / columnCount;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Left;
column = table.AddColumn(Unit.FromCentimeter(1.2));
column.Format.Alignment = ParagraphAlignment.Left;
columnCount++;
column = table.AddColumn(Unit.FromCentimeter(columnWidth - 1.2));
column.Format.Alignment = ParagraphAlignment.Left;
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Hotel Address Details", "Heading2");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("Agency Address Details", "Heading2");
row.Cells[1].MergeRight = 1;
row = table.AddRow();
rowsCount++;
//MyPdfDocuments.WriteHdrContentParagraph(row.Cells[0], dataset.Tables[tableNameHotelAddress].Rows[0]["hotelname"].ToString(), dataset.Tables[tableNameHotelAddress].Rows[0]["hoteladdress"].ToString());
paragraph = row.Cells[0].AddParagraph();
paragraph.Format.RightIndent = "1.5cm";
paragraph.AddFormattedText(dataset.Tables[tableNameHotelAddress].Rows[0]["hotelname"].ToString() + "\n", "Heading3");
paragraph.AddFormattedText(dataset.Tables[tableNameHotelAddress].Rows[0]["hoteladdress"].ToString());
MigraDoc.DocumentObjectModel.Shapes.Image image = row.Cells[1].AddImage(imagePath);
image.Width = "1cm";
//image.Left = "-2.4cm";
MyPdfDocuments.WriteAgencyAddressDetails(row.Cells[2]);
/* Add contant for the table information */
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 6;
columnWidth = (21.0 - 2.0) / columnCount;
for (int index = 1; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth + 0.45));
column.Format.Alignment = ParagraphAlignment.Left;
}
column = table.AddColumn(Unit.FromCentimeter(columnWidth - (0.45 * 5)));
column.Format.Alignment = ParagraphAlignment.Left;
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Lead Passenger Name", "Heading2");
row.Cells[0].MergeRight = 1;
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["leadpassangername"].ToString(), "Heading3");
row.Cells[2].MergeRight = 3;
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Check In Date", "Heading2");
paragraph = row.Cells[1].AddParagraph();
//paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["checkindate"].ToString(), "Heading3");
paragraph.AddFormattedText(DateTime.Parse(dataset.Tables[tableNameHotelRates].Rows[0]["checkindate"].ToString()).ToString("yyyy - MM - dd"), "Heading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Check Out Date", "Heading2");
paragraph = row.Cells[3].AddParagraph();
//paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["checkoutdate"].ToString(), "Heading3");
paragraph.AddFormattedText(DateTime.Parse(dataset.Tables[tableNameHotelRates].Rows[0]["checkoutdate"].ToString()).ToString("yyyy - MM - dd"), "Heading3");
paragraph = row.Cells[4].AddParagraph();
paragraph.AddFormattedText("No of Nights", "Heading2");
paragraph = row.Cells[5].AddParagraph();
double noOfnights = (DateTime.Parse(dataset.Tables[tableNameHotelRates].Rows[0]["checkoutdate"].ToString()) -
DateTime.Parse(dataset.Tables[tableNameHotelRates].Rows[0]["checkindate"].ToString())).TotalDays;
paragraph.AddFormattedText(Convert.ToInt32(noOfnights).ToString(), "Heading3");
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Pick Time", "Heading2");
paragraph = row.Cells[1].AddParagraph();
//paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["checkindate"].ToString(), "Heading3");
paragraph.AddFormattedText(DateTime.Parse(dataset.Tables[tableNameHotelRates].Rows[0]["checkindate"].ToString()).ToString("HH:mm"), "Heading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Drop Time", "Heading2");
paragraph = row.Cells[3].AddParagraph();
//paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[0]["checkoutdate"].ToString(), "Heading3");
paragraph.AddFormattedText(DateTime.Parse(dataset.Tables[tableNameHotelRates].Rows[0]["checkoutdate"].ToString()).ToString("HH:mm"), "Heading3");
row.Cells[4].MergeRight = 1;
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 6;
columnWidth = (21.0 - 2.0) / columnCount;
column = table.AddColumn(Unit.FromCentimeter(1.5));
column.Format.Alignment = ParagraphAlignment.Center;
column = table.AddColumn(Unit.FromCentimeter(2 * columnWidth - 1.5));
column.Format.Alignment = ParagraphAlignment.Center;
for (int index = 2; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
}
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
//row.Shading.Color = Colors.LightSlateGray;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("S. No", "Heading2");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("Room Type", "Heading2");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Meal Plan", "Heading2");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText("No of Rooms", "Heading2");
paragraph = row.Cells[4].AddParagraph();
paragraph.AddFormattedText("No of Guests", "Heading2");
paragraph = row.Cells[5].AddParagraph();
paragraph.AddFormattedText("Guests Type", "Heading2");
for (int roomCounter = 0; roomCounter < dataset.Tables[tableNameHotelRates].Rows.Count; roomCounter++)
{
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText((roomCounter + 1).ToString(), "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["roomtype"].ToString(), "Heading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["mealplan"].ToString(), "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countroom"].ToString(), "Heading3");
paragraph = row.Cells[4].AddParagraph();
int numberOfGuest = Convert.ToInt32(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countadults"]) + Convert.ToInt32(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countchildren"]);
paragraph.AddFormattedText(numberOfGuest.ToString(), "Heading3");
paragraph = row.Cells[5].AddParagraph();
string guestType = "Adults(" + dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countadults"].ToString() + ")";
if (Convert.ToInt32(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countchildren"]) > 0)
{
guestType = guestType + "\nChildren(" + dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countchildren"].ToString() + ")";
}
if (Convert.ToInt32(dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countinfants"]) > 0)
{
guestType = guestType + "\nBabies(" + dataset.Tables[tableNameHotelRates].Rows[roomCounter]["countinfants"].ToString() + ")";
}
paragraph.AddFormattedText(guestType, "Heading3");
}
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
MyPdfDocuments.WriteHotelVoucherStaticDetails(section);
}
PdfDocumentRenderer renderer = new PdfDocumentRenderer(true, PdfSharp.Pdf.PdfFontEmbedding.Always);
try
{
/* try to save file */
renderer.Document = document;
renderer.RenderDocument();
renderer.PdfDocument.Save(saveFileDialogItinerary.FileName);
renderer.PdfDocument.Close();
Process.Start(saveFileDialogItinerary.FileName);
}
catch (Exception errSave)
{
MessageBox.Show("Error in saving file " + saveFileDialogItinerary.FileName + " because " + errSave.Message);
Debug.WriteLine("Error in saving file " + saveFileDialogItinerary.FileName + " because " + errSave.Message);
closeForm = false;
}
finally
{
System.IO.File.Delete(imagePath);
}
/* now close connection */
try
{
mySqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnClose.Message);
}
if (closeForm)
{
Debug.WriteLine("File saved: " + saveFileDialogItinerary.FileName);
}
else
{
return;
}
}
if ((button == BtnPrintFlightTickets) || (button == BtnPrintBoth))
{
//MessageBox.Show("Button pressed = " + button.Text, "PRINTING FLIGHT TICKETS", MessageBoxButtons.OK, MessageBoxIcon.Information);
/* open connection */
try
{
mySqlConnection.Open();
}
catch (Exception errConnOpen)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnOpen.Message);
return;
}
/* now execute query to obtain data from hotel booking table
* and fill t in dataset
*/
string mySqlQueryString = "SELECT DISTINCT `pnr` FROM `flightbookinginfo` WHERE `queryid` = '" + queryId + "' ORDER BY `pnr`";
DataSet dataset = new DataSet();
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlQueryString, mySqlConnection);
mySqlDataAdapter.Fill(dataset, "TABLE_FLIGHT_PNR");
if ((dataset == null) || (dataset.Tables["TABLE_FLIGHT_PNR"].Rows.Count < 1))
{
MessageBox.Show("No Flight information present for QueryId = " + queryId, "No Data Present", MessageBoxButtons.OK, MessageBoxIcon.Warning);
try
{
mySqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnClose.Message);
}
return;
}
saveFileDialogItinerary.Title = "FLIGHT TICKET FILE NAME";
if (saveFileDialogItinerary.ShowDialog() == DialogResult.OK)
{
/* file selected */
Debug.WriteLine("OUT FILE SELECTED : " + saveFileDialogItinerary.FileName);
}
else
{
Debug.WriteLine("File not selected thus returning");
try
{
mySqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnClose.Message);
}
return;
}
string imagePath = saveFileDialogItinerary.FileName + "logo.png";
Properties.Resources.ExcursionHolidaysSmallLogo.Save(imagePath);
Document document = new Document();
document.Info.Title = "FLIGHT TICKET FOR " + queryId;
/* Ask for printing price on ticket */
bool printPrice = false;
DialogResult dialogResult = MessageBox.Show("Flight price details needs to be printed in the flight voucher?", "PRINT PRICE ON VOUCHER", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
printPrice = true;
}
else
{
Debug.WriteLine("Price not printing in the ticket");
}
/* now cange the style of the document */
MyPdfDocuments.DefineStyles(document);
/* add section to document */
Section section = document.AddSection();
for (int pageNumber = 0; pageNumber < dataset.Tables["TABLE_FLIGHT_PNR"].Rows.Count; pageNumber++)
{
mySqlQueryString = "SELECT * FROM `flightbookinginfo` WHERE `queryid` = '" + queryId + "' AND `pnr` = '" + dataset.Tables["TABLE_FLIGHT_PNR"].Rows[pageNumber]["pnr"].ToString() + "'";
mySqlDataAdapter = new MySqlDataAdapter(mySqlQueryString, mySqlConnection);
string tableFlightInfo = "TABLE_FLIGHT_ID_" + pageNumber.ToString();
mySqlDataAdapter.Fill(dataset, tableFlightInfo);
if (dataset.Tables[tableFlightInfo].Rows.Count < 1)
{
continue;
}
if (pageNumber > 0)
{
section.AddPageBreak();
}
Paragraph paragraph;
Table table = null;
Column column = null;
int columnCount = 0; // 1;
double columnWidth = 0; // (21.0 - 2.0) / columnCount;
Row row = null;
int rowsCount = 0;
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 3;
columnWidth = (21.0 - 2.0) / columnCount;
column = table.AddColumn(Unit.FromCentimeter(1.2));
column.Format.Alignment = ParagraphAlignment.Left;
columnCount++;
column = table.AddColumn(Unit.FromCentimeter(columnWidth - 1.2));
column.Format.Alignment = ParagraphAlignment.Left;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Right;
row = table.AddRow();
rowsCount++;
MigraDoc.DocumentObjectModel.Shapes.Image image = row.Cells[0].AddImage(imagePath);
image.Width = "1cm";
MyPdfDocuments.WriteAgencyAddressDetails(row.Cells[1]);
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("E - Ticket\nTICKETED", "Heading2");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText("PNR: " + dataset.Tables[tableFlightInfo].Rows[0]["pnr"].ToString() +
" \nIssued Date: " + DateTime.Parse(dataset.Tables[tableFlightInfo].Rows[0]["issuedate"].ToString()).ToString("ddd dd MM yyyy") +
"", "Normal");
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.None, 0, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 3;
columnWidth = (21.0 - 2.0) / columnCount;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Left;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Right;
row = table.AddRow();
rowsCount++;
row.Shading.Color = Colors.AliceBlue;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Passanger Name", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("Ticket Number", "Heading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Frequent flyer no.", "Heading3");
string[] persons = dataset.Tables[tableFlightInfo].Rows[0]["passangername"].ToString().Split(',');
string[] tickets = dataset.Tables[tableFlightInfo].Rows[0]["ticketnumber"].ToString().Split(',');
string[] ffys = dataset.Tables[tableFlightInfo].Rows[0]["ffy"].ToString().Split(',');
for (int index = 0; index < persons.Length; index++)
{
if (string.Equals(persons[index], ""))
{
continue;
}
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText(persons[index]);
paragraph = row.Cells[1].AddParagraph();
try
{
paragraph.AddFormattedText(tickets[index]);
}
catch (Exception errindex)
{
MessageBox.Show("Error : " + errindex.Message, "Error in index", MessageBoxButtons.OK, MessageBoxIcon.Warning);
paragraph.AddFormattedText("");
}
paragraph = row.Cells[2].AddParagraph();
try
{
paragraph.AddFormattedText(ffys[index]);
}
catch (Exception errindex)
{
MessageBox.Show("Error : " + errindex.Message, "Error in index", MessageBoxButtons.OK, MessageBoxIcon.Warning);
paragraph.AddFormattedText("-");
}
}
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.None, 0.75, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 4;
columnWidth = (21.0 - 2.0) / columnCount;
for (int index = 0; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Left;
}
row = table.AddRow();
row.Shading.Color = Colors.AliceBlue;
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Flight", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("Departure", "Heading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Arrival", "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText("Status", "Heading3");
for (int flightCounter = 0; flightCounter < dataset.Tables[tableFlightInfo].Rows.Count; flightCounter++)
{
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[flightCounter]["flightinfo"].ToString());
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[flightCounter]["departureinfo"].ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[flightCounter]["arrivalinfo"].ToString());
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[flightCounter]["statusinfo"].ToString());
}
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.None, 0.75, Colors.SkyBlue);
rowsCount = columnCount = 0;
section.AddParagraph("\n", "Normal");
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 4;
columnWidth = (21.0 - 2.0) / columnCount;
for (int index = 0; index < 2; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth + 1.0));
column.Format.Alignment = ParagraphAlignment.Left;
}
for (int index = 2; index < 4; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth - 1.0));
column.Format.Alignment = ParagraphAlignment.Right;
}
if (printPrice)
{
row = table.AddRow();
rowsCount++;
row.Shading.Color = Colors.AliceBlue;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Payment Details", "Heading3");
row.Cells[0].MergeRight = 3;
}
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("This is an Electronic ticket. Please carry a positive identification for Check in.");
paragraph.Format.Font.Bold = true;
row.Cells[0].MergeRight = 1;
if (printPrice)
{
int totalPrice = Convert.ToInt32(dataset.Tables[tableFlightInfo].Rows[0]["amountfare"]) +
Convert.ToInt32(dataset.Tables[tableFlightInfo].Rows[0]["amountgst"]) +
Convert.ToInt32(dataset.Tables[tableFlightInfo].Rows[0]["amountsurcharge"]);
row.Cells[0].MergeDown = 3;
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Fare", "Normal");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[0]["amountfare"].ToString(), "Normal");
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("K3/GST", "Normal");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[0]["amountgst"].ToString(), "Normal");
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Fee & Surcharge", "Normal");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(dataset.Tables[tableFlightInfo].Rows[0]["amountsurcharge"].ToString(), "Normal");
row = table.AddRow();
rowsCount++;
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Total Amount");
paragraph.Format.Font.Bold = true;
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(totalPrice.ToString());
paragraph.Format.Font.Bold = true;
}
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.None, 0.5, Colors.SkyBlue);
rowsCount = columnCount = 0;
paragraph = section.AddParagraph("\n\n\n", "Normal");
/* ADD TERMINAL DETAILS HERE */
//paragraph = section.AddParagraph("<TERMINAL DETAILS TYPED HERE XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX>");
//paragraph.Format.Font.Color = Colors.Red;
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
table.Borders.Color = Colors.SkyBlue;
columnCount = 1;
columnWidth = (21.0 - 2.0) / columnCount;
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Left;
row = table.AddRow();
rowsCount++;
MyPdfDocuments.WriteFlightVoucherNote(row.Cells[0]);
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 0, Colors.SkyBlue);
}
PdfDocumentRenderer renderer = new PdfDocumentRenderer(true, PdfSharp.Pdf.PdfFontEmbedding.Always);
try
{
/* try to save file */
renderer.Document = document;
renderer.RenderDocument();
renderer.PdfDocument.Save(saveFileDialogItinerary.FileName);
renderer.PdfDocument.Close();
Process.Start(saveFileDialogItinerary.FileName);
Debug.WriteLine("File saved: " + saveFileDialogItinerary.FileName);
}
catch (Exception errSave)
{
MessageBox.Show("Error in saving file " + saveFileDialogItinerary.FileName + " because " + errSave.Message);
Debug.WriteLine("Error in saving file " + saveFileDialogItinerary.FileName + " because " + errSave.Message);
closeForm = false;
}
finally
{
System.IO.File.Delete(imagePath);
}
try
{
mySqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnClose.Message);
}
}
if (closeForm)
{
Close();
}
}
private void DataGrdVuAdminQueries_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (DataGrdVuAdminQueries.Rows.Count > 0)
{
/* Open new Form of Working and pass queryId to the working */
try
{
Debug.WriteLine("Generating VOUCHERS FOR : \n" +
DataGrdVuAdminQueries.SelectedRows[0].Cells["AssignedDate"].Value.ToString() + "\n" +
DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "\n" +
DataGrdVuAdminQueries.SelectedRows[0].Cells["Location"].Value.ToString() + "\n" +
DataGrdVuAdminQueries.SelectedRows[0].Cells["fromDate"].Value.ToString() + "\n" +
DataGrdVuAdminQueries.SelectedRows[0].Cells["toDate"].Value.ToString() + "\n");
}
catch (Exception errSelectedIndex)
{
Debug.WriteLine("No rows selected because : " + errSelectedIndex.Message);
return;
}
if (string.Equals(frmArgStr, "VOUCHERS"))
{
FrmVouchersOptionsPage newFrmPage = new FrmVouchersOptionsPage(DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString());
Hide();
newFrmPage.ShowDialog();
Show();
}
else if (string.Equals(frmArgStr, "FINALIZE OFFER"))
{
FrmFinalizeQueryPage newFrmPage = new FrmFinalizeQueryPage(DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString());
Hide();
newFrmPage.ShowDialog();
Show();
}
else if (string.Equals(frmArgStr, "UPDATE ACCEPTED OFFER"))
{
FrmFinalizeQueryPage newFrmPage = new FrmFinalizeQueryPage(DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString());
Hide();
newFrmPage.ShowDialog();
Show();
}
else if (string.Equals(frmArgStr, "VIEW ALL"))
{
Debug.WriteLine("VIEW ALL QUERY SELECTED Thus nothing to do\n");
}
else if (string.Equals(frmArgStr, "PAYMENT UPDATE"))
{
//MessageBox.Show("UPDATING PAYMENT FOR : "+ DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "\n");
FrmPaymentUpdatePage newFrmPage = new FrmPaymentUpdatePage(DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString());
Hide();
newFrmPage.ShowDialog();
Show();
}
else if (string.Equals(frmArgStr, "ITINERARY"))
{
if (saveFileDialogItinerary.ShowDialog() == DialogResult.OK)
{
/* file selected */
Debug.WriteLine("OUT FILE SELECTED : " + saveFileDialogItinerary.FileName);
}
else
{
Debug.WriteLine("File not selected thus returning");
return;
}
/* get all information from database regarding this queryid*/
string mysqlQueryString = "SELECT * FROM `queries` WHERE `queryid` = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "'";
try
{
frmMysqlConnection.Open();
}
catch (Exception errConnOpen)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnOpen.Message);
return;
}
DataSet queryDataset = new DataSet();
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mysqlQueryString, frmMysqlConnection);
mySqlDataAdapter.Fill(queryDataset, "SELECTED_QUERY");
mysqlQueryString = "SELECT * FROM `queryworkingday` WHERE `queryid` = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "'";
mySqlDataAdapter.SelectCommand.CommandText = mysqlQueryString;
mySqlDataAdapter.Fill(queryDataset, "QUERY_DAY_INFO");
mysqlQueryString = "SELECT * FROM `queryworkinghotel` WHERE `queryid` = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "'";
mySqlDataAdapter.SelectCommand.CommandText = mysqlQueryString;
mySqlDataAdapter.Fill(queryDataset, "QUERY_HOTEL_INFO");
mysqlQueryString = "SELECT * FROM `queryworkingflight` WHERE `queryid` = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "'";
mySqlDataAdapter.SelectCommand.CommandText = mysqlQueryString;
mySqlDataAdapter.Fill(queryDataset, "QUERY_FLIGHT_INFO");
mysqlQueryString = "SELECT * FROM `queryworkingtravel` WHERE `queryid` = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "'";
mySqlDataAdapter.SelectCommand.CommandText = mysqlQueryString;
mySqlDataAdapter.Fill(queryDataset, "QUERY_TRAVEL_INFO");
mysqlQueryString = "select `T1`.idhotelinfo, `T2`.hotelrating, `T1`.hotelcity , `T1`.hotelname, `T2`.roomtype " +
"from `hotelinfo` as `T1` inner join `queryworkinghotel` as `T2` on `T1`.`idhotelinfo` = `T2`.`idhotelinfo` " +
"WHERE `T2`.`queryid` = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "' " +
"order by `T1`.hotelcity";
mySqlDataAdapter.SelectCommand.CommandText = mysqlQueryString;
mySqlDataAdapter.Fill(queryDataset, "HOTEL_USED_INFO");
/* GENERATE PDF ITINERARY OF THE SELECTED QUERY */
double gstRate = Convert.ToDouble(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["gstrate"]);
gstRate = gstRate / 100;
double profitMargin = Convert.ToDouble(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["profitmargin"]);
profitMargin = profitMargin / 100;
double usdRate = Convert.ToDouble(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["usdrate"]);
double multiplyFactor = (1.0 + gstRate) * (1.0 + profitMargin);
string currency = "";
if (usdRate > 0)
{
currency = "(USD)";
}
else
{
currency = "(INR)";
usdRate = 1;
}
multiplyFactor = multiplyFactor / usdRate;
Debug.WriteLine("GST RATE = " + gstRate.ToString() + " and PROFIT MARGIN = " + profitMargin.ToString() + " and USD rate = " + usdRate.ToString() + "AND MULTIPLY FACTOR = " + multiplyFactor.ToString());
Document document = new Document();
document.Info.Title = "ITINERARY FOR " + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString();
document.Info.Author = "PANJADOTCOM";
/* now cange the style of the document */
MyPdfDocuments.DefineStyles(document);
/* add section to document */
Section section = document.AddSection();
/* now add image at the top of the page */
Table table = null;
Column column = null;
double columnWidth;
int columnCount = 2;
Row row = null;
int rowsCount = 0;
int personCount = 0;
string imagePath = saveFileDialogItinerary.FileName + "logo.png";
Properties.Resources.ExcursionHolidaysLogo.Save(imagePath);
table = section.AddTable();
table.Borders.Visible = false;
table.Borders.Width = 0.75;
column = table.AddColumn(Unit.FromCentimeter(3));
column.Format.Alignment = ParagraphAlignment.Left;
column = table.AddColumn(Unit.FromCentimeter(16));
column.Format.Alignment = ParagraphAlignment.Right;
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
MigraDoc.DocumentObjectModel.Shapes.Image image = row.Cells[0].AddImage(imagePath);
image.Width = "3cm";
MyPdfDocuments.WriteAgencyAddressDetails(row.Cells[1], 22, 10);
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.Transparent);
rowsCount = columnCount = 0;
/* now add summary of the tour in tabular form */
string fileContent;
fileContent = "SUMMARY";
Paragraph paragraph = section.AddParagraph(fileContent, "Heading2");
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.5;
table.Borders.Color = Colors.SkyBlue;
columnCount = 4;
columnWidth = (21.0 - 2.0) / columnCount;
for (int index = 0; index < columnCount; index += 2)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth - 1));
column.Format.Alignment = ParagraphAlignment.Left;
column = table.AddColumn(Unit.FromCentimeter(columnWidth + 1));
column.Format.Alignment = ParagraphAlignment.Left;
}
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Tour Reference", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Customer Name", "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["name"].ToString());
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Tour Name", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["place"].ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("No of Days", "Heading3");
int noOfnights = Convert.ToInt32((DateTime.Parse(DataGrdVuAdminQueries.SelectedRows[0].Cells["toDate"].Value.ToString()) -
DateTime.Parse(DataGrdVuAdminQueries.SelectedRows[0].Cells["fromDate"].Value.ToString())).TotalDays);
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(noOfnights.ToString() + " Nights and " + (noOfnights + 1).ToString() + " Days");
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("No of Person", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["adults"].ToString() + " Adult, " +
queryDataset.Tables["SELECTED_QUERY"].Rows[0]["children"].ToString() + " Child and " +
queryDataset.Tables["SELECTED_QUERY"].Rows[0]["babies"].ToString() + " Infant");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("No of Room", "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["roomcount"].ToString());
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Meals", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["meal"].ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Our Courtesy", "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText("1 Water botel per day");
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Vehicle", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["vehicalcategory"].ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Train/Flight", "Heading3");
paragraph = row.Cells[3].AddParagraph();
if (queryDataset.Tables["QUERY_FLIGHT_INFO"].Rows.Count > 0)
{
paragraph.AddFormattedText("YES");
}
else
{
paragraph.AddFormattedText("NO");
}
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Arrive", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["arrivalcity"].ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Departure", "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["departurecity"].ToString());
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("Tour Start", "Heading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["fromdate"].ToString());
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("Tour End", "Heading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["todate"].ToString());
/*row = table.AddRow();
* rowsCount++;
* row.VerticalAlignment = VerticalAlignment.Center;
* paragraph = row.Cells[0].AddParagraph();
* paragraph.AddFormattedText("Guide", "Heading3");
* paragraph = row.Cells[1].AddParagraph();
* paragraph.AddFormattedText("As per itinerary");
* paragraph = row.Cells[2].AddParagraph();
* paragraph.AddFormattedText("Validity", "Heading3");
* paragraph = row.Cells[3].AddParagraph();
* paragraph.AddFormattedText("One month before tour start date");*/
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 0.5, Colors.SkyBlue);
rowsCount = columnCount = 0;
/* Add itinerary in the section below */
fileContent = "ITINERARY";
paragraph = section.AddParagraph(fileContent, "Heading2");
double amount = 0;
double monumentCost = 0;
/* add day wise narration */
string tourIncContent = "";
string tourNoteContent = "";
foreach (DataRow item in queryDataset.Tables["QUERY_DAY_INFO"].Rows)
{
string hdr = "Day " + item["dayno"].ToString() + ": " + item["narrationhdr"].ToString() + "";
string content = item["narration"].ToString() + "";
if (!string.Equals(item["tourinclusions"].ToString(), ""))
{
tourIncContent = item["tourinclusions"].ToString();
}
if (!string.Equals(item["notes"].ToString(), ""))
{
tourNoteContent = item["notes"].ToString();
}
MyPdfDocuments.WriteHdrContentParagraph(section, hdr, content);
amount = amount + Convert.ToDouble(item["additionalcost"]);
amount = amount + Convert.ToDouble(item["guidecost"]);
monumentCost = monumentCost + Convert.ToDouble(item["monumentcost"]);
}
foreach (DataRow item in queryDataset.Tables["QUERY_TRAVEL_INFO"].Rows)
{
amount = amount + (Convert.ToDouble(item["pricepercar"]) * Convert.ToDouble(item["carcount"]));
}
personCount = Convert.ToInt32(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["adults"].ToString())
+ Convert.ToInt32(queryDataset.Tables["SELECTED_QUERY"].Rows[0]["children"].ToString());
/* add gst rate and profitmargin in amount first. */
Int32 extraAmountPerPerson = Convert.ToInt32(amount / personCount);
extraAmountPerPerson = extraAmountPerPerson + Convert.ToInt32(monumentCost);
if (personCount == 1)
{
columnCount = 2;
}
else if (personCount == 2)
{
columnCount = 3;
}
else if (personCount > 2)
{
columnCount = 5;
}
else
{
columnCount = 0;
}
rowsCount = 0;
if (columnCount > 0)
{
paragraph = section.AddParagraph("PER PERSON RATES " + currency, "Heading3");
columnWidth = (21.0 - 2.0) / columnCount;
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.75;
for (int index = 0; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
}
row = table.AddRow();
rowsCount++;
row.Shading.Color = Colors.RoyalBlue;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("CATEGORY", "CellHeading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("SINGLE", "CellHeading3");
if (columnCount > 2)
{
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("DOUBLE SHARING", "CellHeading3");
}
if (columnCount > 3)
{
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText("EXT ADULT/CWB", "CellHeading3");
paragraph = row.Cells[4].AddParagraph();
paragraph.AddFormattedText("CNB", "Heading3");
}
var hotelRateMatrix = new Int32[4, 4] {
{ 0, 0, 0, 0 }, { 0, 0, 0, 0 }, { 0, 0, 0, 0 }, { 0, 0, 0, 0 }
};
foreach (DataRow item in queryDataset.Tables["QUERY_HOTEL_INFO"].Rows)
{
if (string.Equals(item["hotelrating"].ToString(), "STANDARD"))
{
hotelRateMatrix[0, 0]++;
hotelRateMatrix[0, 1] += Convert.ToInt32(item["singlebedprice"]);
hotelRateMatrix[0, 2] += (Convert.ToInt32(item["doublebedprice"]) / 2);
hotelRateMatrix[0, 3] += Convert.ToInt32(item["extrabedprice"]);
}
else if (string.Equals(item["hotelrating"].ToString(), "DELUXE"))
{
hotelRateMatrix[1, 0]++;
hotelRateMatrix[1, 1] += Convert.ToInt32(item["singlebedprice"]);
hotelRateMatrix[1, 2] += (Convert.ToInt32(item["doublebedprice"]) / 2);
hotelRateMatrix[1, 3] += Convert.ToInt32(item["extrabedprice"]);
}
else if (string.Equals(item["hotelrating"].ToString(), "SUPERIOR"))
{
hotelRateMatrix[2, 0]++;
hotelRateMatrix[2, 1] += Convert.ToInt32(item["singlebedprice"]);
hotelRateMatrix[2, 2] += (Convert.ToInt32(item["doublebedprice"]) / 2);
hotelRateMatrix[2, 3] += Convert.ToInt32(item["extrabedprice"]);
}
else if (string.Equals(item["hotelrating"].ToString(), "LUXORY"))
{
hotelRateMatrix[3, 0]++;
hotelRateMatrix[3, 1] += Convert.ToInt32(item["singlebedprice"]);
hotelRateMatrix[3, 2] += (Convert.ToInt32(item["doublebedprice"]) / 2);
hotelRateMatrix[3, 3] += Convert.ToInt32(item["extrabedprice"]);
}
}
for (int index = 0; index < 4; index++)
{
if (hotelRateMatrix[index, 0] > 0)
{
string hotelRating = "";
switch (index)
{
case 0:
hotelRating = "STANDARD";
break;
case 1:
hotelRating = "DELUXE";
break;
case 2:
hotelRating = "SUPERIOR";
break;
case 3:
hotelRating = "LUXORY";
break;
default:
hotelRating = "UNKNOWN";
break;
}
row = table.AddRow();
rowsCount++;
/*if ((rowsCount % 2) == 0)
* {
* row.Shading.Color = Colors.PaleTurquoise;
* }
* else
* {
* row.Shading.Color = Colors.PapayaWhip;
* }*/
row.Cells[0].AddParagraph(hotelRating);
hotelRateMatrix[index, 1] += Convert.ToInt32(extraAmountPerPerson);
amount = Convert.ToDouble(hotelRateMatrix[index, 1]) * multiplyFactor;
row.Cells[1].AddParagraph(Convert.ToInt32(amount).ToString());
if (columnCount > 2)
{
hotelRateMatrix[index, 2] += Convert.ToInt32(extraAmountPerPerson);
amount = Convert.ToDouble(hotelRateMatrix[index, 2]) * multiplyFactor;
row.Cells[2].AddParagraph(Convert.ToInt32(amount).ToString());
}
if (columnCount > 3)
{
hotelRateMatrix[index, 3] += Convert.ToInt32(extraAmountPerPerson);
amount = Convert.ToDouble(hotelRateMatrix[index, 3]) * multiplyFactor;
row.Cells[3].AddParagraph(Convert.ToInt32(amount).ToString());
amount = Convert.ToDouble(extraAmountPerPerson) * multiplyFactor;
row.Cells[4].AddParagraph(Convert.ToInt32(amount).ToString());
}
}
}
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.Black);
}
/* add hotel rates in tabular form */
int hotelRowsCount = rowsCount;
/* now change cloumn count to rows count;*/
var hotelusedMatrix = new int[4, 4] {
{ 0, 0, 0, 0 }, { 0, 0, 0, 0 }, { 0, 0, 0, 0 }, { 0, 0, 0, 0 }
};
foreach (DataRow item in queryDataset.Tables["HOTEL_USED_INFO"].Rows)
{
if (string.Equals(item["hotelrating"].ToString(), "STANDARD"))
{
hotelusedMatrix[0, 0]++;
}
else if (string.Equals(item["hotelrating"].ToString(), "DELUXE"))
{
hotelusedMatrix[1, 0]++;
}
else if (string.Equals(item["hotelrating"].ToString(), "SUPERIOR"))
{
hotelusedMatrix[2, 0]++;
}
else if (string.Equals(item["hotelrating"].ToString(), "LUXORY"))
{
hotelusedMatrix[3, 0]++;
}
}
if (hotelRowsCount > 0)
{
columnCount = hotelRowsCount;
rowsCount = 0;
int columnIndex = 0;
string lastCity = "";
DataTable dataTable = new DataTable();
dataTable.Clear();
DataColumn dataColumn = dataTable.Columns.Add("ZERO");
DataRow dataRow = null;//dataTable.NewRow();
//dataRow["ZERO"] = "";
foreach (DataRow item in queryDataset.Tables["HOTEL_USED_INFO"].Rows)
{
if (rowsCount == 0)
{
paragraph = section.AddParagraph("HOTEL INFORMATION", "Heading3");
columnWidth = (21.0 - 2.0) / columnCount;
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.75;
for (int index = 0; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
}
row = table.AddRow();
rowsCount++;
columnIndex = 1;
row.Shading.Color = Colors.RoyalBlue;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("HOTEL USED", "CellHeading3");
if (hotelusedMatrix[0, 0] > 0)
{
paragraph = row.Cells[columnIndex].AddParagraph();
paragraph.AddFormattedText("STANDARD", "CellHeading3");
hotelusedMatrix[0, 1] = columnIndex;
columnIndex++;
}
if (hotelusedMatrix[1, 0] > 0)
{
paragraph = row.Cells[columnIndex].AddParagraph();
paragraph.AddFormattedText("DELUXE", "CellHeading3");
hotelusedMatrix[1, 1] = columnIndex;
columnIndex++;
}
if (hotelusedMatrix[2, 0] > 0)
{
paragraph = row.Cells[columnIndex].AddParagraph();
paragraph.AddFormattedText("SUPERIOR", "CellHeading3");
hotelusedMatrix[2, 1] = columnIndex;
columnIndex++;
}
if (hotelusedMatrix[3, 0] > 0)
{
paragraph = row.Cells[columnIndex].AddParagraph();
paragraph.AddFormattedText("LUXORY", "CellHeading3");
hotelusedMatrix[3, 1] = columnIndex;
}
}
if (!lastCity.Equals(item["hotelcity"].ToString()))
{
row = table.AddRow();
rowsCount++;
row.VerticalAlignment = VerticalAlignment.Center;
row.Cells[0].AddParagraph("IN " + item["hotelcity"].ToString());
lastCity = item["hotelcity"].ToString();
dataTable.Columns.Add(lastCity);
}
columnIndex = 0;
if (string.Equals(item["hotelrating"].ToString(), "STANDARD"))
{
columnIndex = hotelusedMatrix[0, 1];
}
else if (string.Equals(item["hotelrating"].ToString(), "DELUXE"))
{
columnIndex = hotelusedMatrix[1, 1];
}
else if (string.Equals(item["hotelrating"].ToString(), "SUPERIOR"))
{
columnIndex = hotelusedMatrix[2, 1];
}
else if (string.Equals(item["hotelrating"].ToString(), "LUXORY"))
{
columnIndex = hotelusedMatrix[3, 1];
}
bool addHotelEntry = true;
for (int index = 0; index < dataTable.Rows.Count; index++)
{
if (dataTable.Rows[index][lastCity].ToString().Equals(item["hotelname"].ToString() + "(" + item["roomtype"].ToString() + ")"))
{
addHotelEntry = false;
//MessageBox.Show(item["hotelname"].ToString() + "(" + item["roomtype"].ToString() + ") FOUND" );
break;
}
}
if (addHotelEntry)
{
row.Cells[columnIndex].AddParagraph(item["hotelname"].ToString() + "(" + item["roomtype"].ToString() + ")");
dataRow = dataTable.NewRow();
dataRow[lastCity] = item["hotelname"].ToString() + "(" + item["roomtype"].ToString() + ")";
dataTable.Rows.Add(dataRow);
}
}
if (rowsCount > 0)
{
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.Black);
}
dataTable.Clear();
}
/* add flight information in the itenary */
rowsCount = 0;
amount = 0;
foreach (DataRow item in queryDataset.Tables["QUERY_FLIGHT_INFO"].Rows)
{
if (rowsCount == 0)
{
paragraph = section.AddParagraph("AIR/TRAIN FARE " + currency + " PER PERSON EXTRA", "Heading3");
columnCount = 5;
columnWidth = (21.0 - 2.0) / columnCount;
table = section.AddTable();
table.Borders.Visible = true;
table.Borders.Width = 0.75;
for (int index = 0; index < columnCount; index++)
{
column = table.AddColumn(Unit.FromCentimeter(columnWidth));
column.Format.Alignment = ParagraphAlignment.Center;
}
row = table.AddRow();
rowsCount++;
row.Shading.Color = Colors.RoyalBlue;
paragraph = row.Cells[0].AddParagraph();
paragraph.AddFormattedText("DATE", "CellHeading3");
paragraph = row.Cells[1].AddParagraph();
paragraph.AddFormattedText("FROM", "CellHeading3");
paragraph = row.Cells[2].AddParagraph();
paragraph.AddFormattedText("TO", "CellHeading3");
paragraph = row.Cells[3].AddParagraph();
paragraph.AddFormattedText("FLIGHT/TRAIN", "CellHeading3");
paragraph = row.Cells[4].AddParagraph();
paragraph.AddFormattedText("AMOUNT", "CellHeading3");
}
row = table.AddRow();
rowsCount++;
/*if ((rowsCount % 2) == 0)
* {
* row.Shading.Color = Colors.PaleTurquoise;
* }
* else
* {
* row.Shading.Color = Colors.PapayaWhip;
* }*/
row.Cells[0].AddParagraph(item["date"].ToString());
row.Cells[0].VerticalAlignment = VerticalAlignment.Center;
row.Cells[1].AddParagraph(item["fromcity"].ToString().ToUpper());
row.Cells[1].VerticalAlignment = VerticalAlignment.Center;
row.Cells[2].AddParagraph(item["tocity"].ToString().ToUpper());
row.Cells[2].VerticalAlignment = VerticalAlignment.Center;
row.Cells[3].AddParagraph(item["flightnumber"].ToString().ToUpper());
row.Cells[3].VerticalAlignment = VerticalAlignment.Center;
amount = amount + Convert.ToDouble(item["rateperticket"].ToString());
}
if (rowsCount > 0)
{
table.Rows[1].Cells[4].MergeDown = rowsCount - 2;
amount = amount / usdRate;
table.Rows[1].Cells[4].AddParagraph(Convert.ToInt32(amount).ToString());
table.Rows[1].Cells[4].Shading.Color = Colors.WhiteSmoke;
table.Rows[1].Cells[4].VerticalAlignment = VerticalAlignment.Center;
table.SetEdge(0, 0, columnCount, rowsCount, Edge.Box, MigraDoc.DocumentObjectModel.BorderStyle.Single, 1.5, Colors.Black);
}
/* add notes in the document */
MyPdfDocuments.WriteItineraryLastStaticDetails(section, tourIncContent, tourNoteContent);
//////////////////////////////////////////////////////////////////////////////////////////////////
PdfDocumentRenderer renderer = new PdfDocumentRenderer(true, PdfSharp.Pdf.PdfFontEmbedding.Always);
try
{
/* try to save file */
renderer.Document = document;
renderer.RenderDocument();
renderer.PdfDocument.Save(saveFileDialogItinerary.FileName);
renderer.PdfDocument.Close();
Process.Start(saveFileDialogItinerary.FileName);
}
catch (Exception errSave)
{
MessageBox.Show("Error in saving file " + saveFileDialogItinerary.FileName + " because " + errSave.Message);
Debug.WriteLine("Error in saving file " + saveFileDialogItinerary.FileName + " because " + errSave.Message);
}
finally
{
System.IO.File.Delete(imagePath);
}
/*/////////////////////////////PDF OF ITENARY CREATED AND SAVED//////////////////////////////////////////////////*/
MySqlCommand mySqlCommand = frmMysqlConnection.CreateCommand();
MySqlTransaction mySqlTransaction = frmMysqlConnection.BeginTransaction();
mySqlCommand.Connection = frmMysqlConnection;
mySqlCommand.Transaction = mySqlTransaction;
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.CommandText = "UPDATE `queries` SET " +
"`querylastupdatetime` = NOW(), " +
"`querycurrentstate` = " + Properties.Resources.queryStageMailed + " " +
"WHERE " +
"queryid = '" + DataGrdVuAdminQueries.SelectedRows[0].Cells["QueryId"].Value.ToString() + "'";
mySqlCommand.Prepare();
try
{
mySqlCommand.ExecuteNonQuery();
mySqlTransaction.Commit();
}
catch (Exception errquery)
{
MessageBox.Show("Error while executing insert query because:\n" + errquery.Message);
}
try
{
frmMysqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in Closing mysql connection because : " + errConnClose.Message);
}
}
else
{
MessageBox.Show("Wrong argument passed", "Wrong argument", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("Please load Queries First", "Load Queries", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}