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 DataGrdVuAdminQueriesLoad(string argumentString)
        {
            string mysqlSelectQuery = null;

            mysqlSelectQuery = "SELECT `queryid`, `place`, `fromdate`, `todate`, `querystartdate`, `name`, `querycurrentstate` " +
                               "FROM `queries` WHERE ";
            if (string.Equals(argumentString, "ITINERARY"))
            {
                mysqlSelectQuery += "`querycurrentstate` >= " + Properties.Resources.queryStageDoneByUser +
                                    " AND `querycurrentstate` <= " + Properties.Resources.queryStageMailed;
            }
            else if (string.Equals(argumentString, "FINALIZE OFFER"))
            {
                mysqlSelectQuery += "`querycurrentstate` = " + Properties.Resources.queryStageMailed;
            }
            else if (string.Equals(argumentString, "UPDATE ACCEPTED OFFER"))
            {
                mysqlSelectQuery += "`querycurrentstate` >= " + Properties.Resources.queryStageDealDone +
                                    " AND `querycurrentstate` <= " + Properties.Resources.queryStageVoucherCompleted;
            }
            else if (string.Equals(argumentString, "VOUCHERS"))
            {
                mysqlSelectQuery += "`querycurrentstate` >= " + Properties.Resources.queryStageVoucherCompleted;
            }
            else if (string.Equals(argumentString, "VIEW ALL"))
            {
                mysqlSelectQuery += "`querycurrentstate` != 0";
            }
            else if (string.Equals(argumentString, "PAYMENT UPDATE"))
            {
                mysqlSelectQuery += "`querycurrentstate` != 0";
            }
            else
            {
                MessageBox.Show("Wrong method invoked");
                return;
            }

            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(mysqlSelectQuery, frmMysqlConnection);

            try
            {
                mySqlDataAdapter.Fill(queryDataset, "ASSIGNED_QUERIES");
                DataGrdVuAdminQueries.Rows.Clear();
                if (queryDataset != null)
                {
                    foreach (DataRow item in queryDataset.Tables["ASSIGNED_QUERIES"].Rows)
                    {
                        int index = DataGrdVuAdminQueries.Rows.Add();
                        DataGrdVuAdminQueries.Rows[index].Cells["QueryId"].Value      = item["queryid"].ToString();
                        DataGrdVuAdminQueries.Rows[index].Cells["FromDate"].Value     = item["fromdate"].ToString();
                        DataGrdVuAdminQueries.Rows[index].Cells["Name"].Value         = item["name"].ToString();
                        DataGrdVuAdminQueries.Rows[index].Cells["QueryStage"].Value   = item["querycurrentstate"].ToString() + " ( " + MyPdfDocuments.PrintCurrentQueryStage(Convert.ToInt32(item["querycurrentstate"])) + " )";
                        DataGrdVuAdminQueries.Rows[index].Cells["ToDate"].Value       = item["todate"].ToString();
                        DataGrdVuAdminQueries.Rows[index].Cells["Location"].Value     = item["place"].ToString();
                        DataGrdVuAdminQueries.Rows[index].Cells["AssignedDate"].Value = item["querystartdate"].ToString();
                    }
                }
                DataGrdVuAdminQueries.ClearSelection();
            }
            catch (Exception errQuery)
            {
                MessageBox.Show("Error in executing command because : " + errQuery.Message);
            }
            try
            {
                frmMysqlConnection.Close();
            }
            catch (Exception errConnClose)
            {
                MessageBox.Show("Error in Closing mysql connection because : " + errConnClose.Message);
            }
        }
示例#3
0
        private void DataGrdVuUserQueriesLoad()
        {
            string mysqlSelectQuery = null;
            bool   isColourEnable   = false;

            DataGrdVuUserQueries.Rows.Clear();
            if (radioButtonWorkingItinary.Checked)
            {
                mysqlSelectQuery = "SELECT `queryid`, `querycurrentstate`, `place`, `fromdate`, `todate`, `querystartdate` " +
                                   "FROM `queries` WHERE " +
                                   "`userid` = " + frmUserId.ToString() + " ";
                mysqlSelectQuery = mysqlSelectQuery +
                                   "AND `querycurrentstate` = " + Properties.Resources.queryStageGenerated;
                isColourEnable = true;
            }
            else if (radioButtonCompletedItinerary.Checked)
            {
                mysqlSelectQuery = "SELECT `queryid`, `querycurrentstate`, `place`, `fromdate`, `todate`, `querystartdate` " +
                                   "FROM `queries` WHERE " +
                                   "`userid` = " + frmUserId.ToString() + " ";
                mysqlSelectQuery = mysqlSelectQuery +
                                   "AND `querycurrentstate` > " + Properties.Resources.queryStageGenerated + " " +
                                   "AND `querycurrentstate` < " + Properties.Resources.queryStageRejected;
                isColourEnable = false;
            }
            else if (radioButtonWorkingVouchers.Checked)
            {
                mysqlSelectQuery = "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
                                   "FROM `queries` as `T1` inner join `finalizedqueries` as `T2` on `T1`.`queryid` = `T2`.`queryid` " +
                                   "WHERE " +
                                   "`T2`.`userid` = " + frmUserId.ToString() + " " +
                                   "AND `T1`.`querycurrentstate` > " + Properties.Resources.queryStageRejected + " " +
                                   "AND `T1`.`querycurrentstate` < " + Properties.Resources.queryStageVoucherCompleted;
                isColourEnable = true;
            }
            else if (radioButtonCompletedBooking.Checked)
            {
                mysqlSelectQuery = "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
                                   "FROM `queries` as `T1` inner join `finalizedqueries` as `T2` on `T1`.`queryid` = `T2`.`queryid` " +
                                   "WHERE " +
                                   "`T2`.`userid` = " + frmUserId.ToString() + " " +
                                   "AND `T1`.`querycurrentstate` = " + Properties.Resources.queryStageVoucherCompleted;
                isColourEnable = false;
            }
            else if (radioButtonAllQueries.Checked)
            {
                mysqlSelectQuery = "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
                                   "FROM `queries` as `T1` inner join `finalizedqueries` as `T2` " +
                                   "on `T1`.`queryid` = `T2`.`queryid` " +
                                   "WHERE " +
                                   "`T2`.`userid` = " + frmUserId.ToString() + " " +
                                   "UNION " +
                                   "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
                                   "FROM `queries` as `T1` " +
                                   "WHERE " +
                                   "`T1`.`userid` = " + frmUserId.ToString() + " ";
                isColourEnable = false;
            }
            else
            {
                MessageBox.Show("No Option is selected");
                return;
            }

            try
            {
                frmUserMysqlConnection.Open();
            }
            catch (Exception errConnOpen)
            {
                MessageBox.Show("Error in opening mysql connection because : " + errConnOpen.Message);
            }

            DataSet          queryDataset     = new DataSet();
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mysqlSelectQuery, frmUserMysqlConnection);

            try
            {
                mySqlDataAdapter.Fill(queryDataset, "ASSIGNED_QUERIES");
                if (queryDataset != null)
                {
                    foreach (DataRow item in queryDataset.Tables["ASSIGNED_QUERIES"].Rows)
                    {
                        int index = DataGrdVuUserQueries.Rows.Add();
                        DataGrdVuUserQueries.Rows[index].Cells["QueryId"].Value      = item["queryid"].ToString();
                        DataGrdVuUserQueries.Rows[index].Cells["QueryState"].Value   = item["querycurrentstate"].ToString() + " ( " + MyPdfDocuments.PrintCurrentQueryStage(Convert.ToInt32(item["querycurrentstate"])) + " )";
                        DataGrdVuUserQueries.Rows[index].Cells["fromDate"].Value     = item["fromdate"].ToString();
                        DataGrdVuUserQueries.Rows[index].Cells["toDate"].Value       = item["todate"].ToString();
                        DataGrdVuUserQueries.Rows[index].Cells["Location"].Value     = item["place"].ToString();
                        DataGrdVuUserQueries.Rows[index].Cells["AssignedDate"].Value = item["querystartdate"].ToString();
                        double noOfdays = (DateTime.Today - DateTime.Parse(item["querystartdate"].ToString())).TotalDays;
                        if (isColourEnable)
                        {
                            if (noOfdays > 2)
                            {
                                DataGrdVuUserQueries.Rows[index].DefaultCellStyle.BackColor = Color.Red;
                            }
                            else if (noOfdays > 1)
                            {
                                DataGrdVuUserQueries.Rows[index].DefaultCellStyle.BackColor = Color.Yellow;
                            }
                        }
                    }
                }
                DataGrdVuUserQueries.Sort(QueryState, ListSortDirection.Ascending);
                DataGrdVuUserQueries.ClearSelection();
            }
            catch (Exception errQuery)
            {
                MessageBox.Show("Error in executing command because : " + errQuery.Message);
            }
            try
            {
                frmUserMysqlConnection.Close();
            }
            catch (Exception errConnClose)
            {
                MessageBox.Show("Error in Closing mysql connection because : " + errConnClose.Message);
            }
        }
        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);
            }
        }