using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;

public partial class newbusiness_display_NewBusinessAll : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            //get cookie value for name and admin status
            //If no cookie exists, redirect user to the login page
            if (Request.Cookies["UserInfo"] == null)
                Response.Redirect("login.aspx");

            //If the loggedIn value of the cookie is not true, redirect user to the login page
            if (Request.Cookies["UserInfo"]["loggedIn"] != "true")
                Response.Redirect("login.aspx");

            //Get cookie values
            string name = Request.Cookies["UserInfo"]["Name"].ToString();

	    //TODO: Validate Cookie Data

            createTable(name);
        }
        catch (Exception)
        {
            //Put error code here
        }
    }
    //Create PDF document with sql data written to a table
    protected void createTable(string value1)
    {
        try
        {
            int numColumns = 9; //Initialize the number of columns
            int count = 0;      //Initializa a counter
            double totalPremium = 0;    //Initialize the running total for the Premium

            MemoryStream m = new MemoryStream();    //Initialize a memory stream object

            //Create a landscape document with the specified margins (l,r, t, b)
            Document myDoc = new Document(PageSize.A4.Rotate(), 25, 25, 50, 20);    

            Response.ContentType = "application/pdf";   //Specify the content type of the document to be pdf
            PdfWriter.GetInstance(myDoc, m);    //Create an instance of the document

            //Create page footer
            Chunk myFooter = new Chunk(DateTime.Now.ToShortDateString() + "      " + "Page " + (myDoc.PageNumber + 1),
                FontFactory.GetFont(FontFactory.HELVETICA_OBLIQUE, 8, new Color(46, 84, 141)));
            HeaderFooter footer = new HeaderFooter(new Phrase(myFooter), false);    //Create a footer object with the chunk data
            footer.Border = Rectangle.NO_BORDER;    //Specify no border around the footer
            footer.Alignment = Element.ALIGN_RIGHT; //Specify the footer text alignment
            myDoc.Footer = footer;                  //Set the document's footer to the footer object

            myDoc.Open();   //Open the document

            string[] rowData = new string[9];   //Initialize a string array of 9 elements
            string[] headers = {"Department", "PRM", "Insured", "Company", "Effecive Date", "Line",
                                   "Premium", "Producer", "New/Rewrite"};   //Create an array for the column headers

            DataSet myDS = GetData(value1);   //Call GetData function to get a data set from the SQL database
            DataTableReader myReader = myDS.Tables["Records"].CreateDataReader();  //Create a data reader from the Records table from myDS

            PdfPTable dataTable = new PdfPTable(numColumns);    //Create a table with 9 columns
            dataTable.DefaultCell.Padding = 0;                  //Set table cell padding to zero
            float[] headerwidths = { 10, 15, 15, 13, 8, 10, 10, 10, 9 };     //Specify column widths (in percents)
            dataTable.SetWidths(headerwidths);                              //Set the widths for the columns
            dataTable.WidthPercentage = 100;        //Set the table width percentage to 100

            dataTable.DefaultCell.BorderWidth = 0;  //Specify no table border
            dataTable.DefaultCell.HorizontalAlignment = Element.ALIGN_LEFT; //Set cell text to left align

            //Loop thru the header array
            for (int i = 0; i < numColumns; i++)
            {
                //Create a chunck and format the array element
                Chunk myData = new Chunk(headers[i], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11, new Color(46, 84, 141)));
                Phrase myPhrase = new Phrase(myData);   //Create a phrase from the chunk
                dataTable.AddCell(myPhrase);            //Insert phrase into table
                dataTable.DefaultCell.PaddingBottom = 10;   //Set the bottom cell padding to 10
            }
            dataTable.HeaderRows = 1;
            dataTable.DefaultCell.PaddingBottom = 3;    //Set the cell padding from now on to 3

            //While there are records in the dataset
            while (myReader.Read())
            {
                string name = myReader["LName"].ToString() + ", " + myReader["FName"].ToString();   //Format the insured's name
                DateTime myDate = DateTime.Parse(myReader["eDate"].ToString());     //Create a date abject
                string formattedDate = myDate.ToString("MM/dd/yy");             //Format the date
                totalPremium += Double.Parse(myReader["Premium"].ToString());       //Add premium amount to a running total

                //Alternate row colors
                if (count % 2 == 0)
                    dataTable.DefaultCell.BackgroundColor = new iTextSharp.text.Color(200, 213, 232);
                else
                    dataTable.DefaultCell.BackgroundColor = new iTextSharp.text.Color(255, 255, 255);

                //Add each record into an array
                rowData[0] = myReader["Department"].ToString();
                rowData[1] = myReader["PRM"].ToString();
                rowData[2] = name;
                rowData[3] = myReader["Company"].ToString();
                rowData[4] = formattedDate;
                rowData[5] = myReader["LOB"].ToString();
                rowData[6] = String.Format("{0:C}", myReader["Premium"]);
                rowData[7] = myReader["Producer"].ToString();
                rowData[8] = myReader["NR"].ToString();

                //Loop thru the array and write the contents to the table
                for (int j = 0; j < numColumns; j++)
                    dataTable.AddCell(rowData[j]);
                count++;    //Increment the counter
            }
            //Add premium total to table
            Chunk myPremium = new Chunk("Total Sales: " + String.Format("{0:C}", totalPremium),
                FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11, new Color(46, 84, 141)));   //Create chunk for formatting
            Phrase myTotalPremium = new Phrase(myPremium);  //Create Phrase from chunk
            dataTable.DefaultCell.Colspan = 9;  //Specify the row's colspan
            dataTable.DefaultCell.HorizontalAlignment = Element.ALIGN_RIGHT;    //Specify text alignment
            dataTable.DefaultCell.PaddingTop = 10;  //Set the cell padding
            dataTable.DefaultCell.BackgroundColor = new iTextSharp.text.Color(255, 255, 255);   //Set the cell background color
            dataTable.AddCell(myTotalPremium);  //Add cell to table

            string reportTitle = "Personal Lines New Business Report";  //Create report title
            Chunk title = new Chunk(reportTitle, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 14,
                new Color(46, 84, 141)));   //Format report title
            myDoc.Add(title);   //Add the title to the document
            myDoc.Add(new Paragraph(" "));  //Add a blank line to the document
            myDoc.Add(dataTable);   //Add the table to the document

            myDoc.Close();  //Close the document
            Response.OutputStream.Write(m.GetBuffer(), 0, m.GetBuffer().Length);    //Write the document to the output stream
            Response.OutputStream.Flush();  //Flush the output stream
            Response.OutputStream.Close();  //Close the output stream
        }
        catch (Exception)
        {
            //Put error code here
        }
    }
    //Returns a dataset from an MSSQL database
    protected DataSet GetData(string value1)
    {
        string errMsg = "";

        try
        {
            //SQL connection string
            SqlConnection conn = new SqlConnection("YourConnectionStringHere");

            //Open database connection
            conn.Open();
            string sql = "";

            sql = "SELECT * FROM TableName WHERE ColumnName = '" + value1 + "' ORDER BY ColumnName DESC";

            SqlCommand sqlQry = new SqlCommand(sql, conn);      //Create a SQL Command object
            sqlQry.CommandTimeout = 30;     //Set the timeout of the object

            //Create a Data Adapter
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = sqlQry;   //Select the SQL command for the data adapter

            //Create Data Set
            DataSet sqlDS = new DataSet();
            sqlDA.Fill(sqlDS, "Records");   //Fill the dataset with the table "Records"

            conn.Close();   //Close the DB connection

            return sqlDS;
        }
        catch (SqlException x)
        {
            //Put error code here
            return null;
        }
    }
}