Untitled

<!DOCTYPE html">
<html>
  <head>
    <meta charset=utf-8" />
        <title>Generate Reports</title>

      <link rel="stylesheet" type="text/css" href="style.css" media="screen" />

        <!--Display the Criteria screen only if Title Org Codes Report (rpt3) is selected-->
        <script type="text/javascript">
            function showForm() {
                var selopt = document.getElementById("selReport").value;

                if (selopt === "rpt3") {
                    document.getElementById("criteria").style.display = "block";
                }

                else {
                    document.getElementById("criteria").style.display = "none";
                }

            }
      </script>

  </head>
  <body>
    <div id="wrapper">

                <div id="content">

                    <div id="reports" class="center">
                        <h3> Generate Reports: </h3>

                        <!--Display the list of reports-->
                        <form id="frm1" name="frm1"  method="post" action="Reports.php">
                            <!-- Excel, PDF, View buttons -->
                            <input type="submit" value="Preview Report" id="view" name="view">
                            <input type="submit" value="Export to Excel" id="excel" name="excel">
                            <input type="submit" value="Publish as PDF" id="pdf" name="pdf">
                            <br><br><br>

                            Select a Report:
                            <select id="selReport" name="selReport" onclick="showForm();">
                                <option></option>
                                <option value="rpt1">Units/Ranges Summary</option>
                                <option value="rpt2">Divisions Table</option>
                                <option value="rpt3">Title Codes</option>
                            </select>


                            <!--Creates the criteria drop down menu-->
                            <div id="criteria" style="display:none">
                                <br><br><h3>Selection Criteria for Reports:</h3>
                                    Title File Status:
                                    <select name="selCriteria" id="selCriteria" onchange="showForm();">
                                        <option></option>
                                        <option value="active">Active</option>
                                        <option value="inactive">Inactive</option>
                                        <option value="all">All</option>
                                    </select>
                            </div>  <!--end criteria -->

                        </form> <!-- end frm1  -->

                     </div> <!-- end #reports -->

                </div> <!-- end #content -->

    </div> <!-- end #wrapper -->
</body>
</html>

<?php
    //Get the user selection and put into variables
    $varRpt = $_POST['selReport'];
    $varCrit = $_POST['selCriteria'];

    // require the PHPExcel classes
    require 'PHPExcel/Classes/PHPExcel.php';

    // PHPExcel_Writer_Excel2007
    require 'PHPExcel/Classes/PHPExcel/Writer/Excel2007.php';

    //-------------------------------
    // Connect to the MySQL database
    //-------------------------------
    $dbhost = "localhost";
    $dbuser = "root";
    $dbpass = "*******";
    $dbname = "test";

    mysql_connect($dbhost,$dbuser,$dbpass)or die ("Could not connect to mysql because ".mysql_error());

    mysql_select_db($dbname)or die("Unable to select database" .mysql_error());

    //-------------------------------------
    // Figure out which report to generate
    //-------------------------------------

        if ($varRpt == "rpt1")
        {
            $query =  "SELECT a.div_id, a.unit_id, b.unit_beg, b.unit_end, a.title_org, c.long_desc, c.short_desc FROM depunits a LEFT OUTER JOIN unitRanges b ON ( a.unit_id )= ( b.unit_id )  LEFT OUTER JOIN orgcodes c ON ( a.title_org )= ( c.l1l5_id ) ORDER BY a.div_id, a.unit_id" ;

            //Column headings
            $headings = array('Div_id','Unit Id','Unit Begin','Unit End','Title Org','Long Desc','Short Desc');

            // Sheet name
            $title = "Summary Report Units/Ranges";

            // Name of the saved excel file
            $filename = "Rpt1_" . date('Ymd') . ".xls";
        }
        else
            if ($varRpt == "rpt2")
                {

                    $query = "SELECT alldiv_id, div_id, L1l2_id, L2_id, L1l3_id, L2l3_id, Exec_beg, Exec_end, Csa_id, Area_id, Area_Desc, Short_Desc, Long_Desc FROM divisions WHERE avail_ind='Y' AND active_ind='Y' ORDER BY alldiv_id ";

                    // Column Labels
                    $headings = array('All','Div','L1L2','L2','L1L3','L2L3','Exec Begin','Exec End','CSA','Area Id','Area Desc','Short Desc','Long Desc');

                    // Report Title
                    $title = "Divisions Table";

                    // name of the saved excel file
                    $filename = "Rpt2_" . date('Ymd') . ".xls";

                } // end $varRpt == "rpt2"

                else
                    if ($varRpt == "rpt3")
                    {
                        //Column heading
                        $headings = array('Title Code','Short Title','Long Title','Status');

                        // Report title
                        $title = "Title Codes";

                        // Name of the saved file
                        $filename = "Rpt3_" . date('Ymd') . ".xls";

                        if ($varCrit == "active")
                        {
                            $query = "SELECT L2l5, Stitl, Ltitl, Status FROM Tl2l5 WHERE UPPER(TRIM(status))= 'A' ORDER BY L2l5";
                        }
                        else
                            if ($varCrit == "inactive")
                            {
                                $query = "SELECT L2l5, Stitl, Ltitl, Status FROM Tl2l5 WHERE UPPER(TRIM(status))= 'I' ORDER BY L2l5";
                            }
                            else
                                if ($varCrit == "all")
                                {
                                    $query = "SELECT L2l5, Stitl, Ltitl, Status FROM Tl2l5 ORDER BY L2l5";
                                }
                    }


//-----------------------------------------
// Insert data into Excel Report template
//-----------------------------------------

    // Create a new PHPExcel object
    $objPHPExcel = new PHPExcel();


    // Sheet name
    $objPHPExcel->getActiveSheet()->setTitle('sheet1');

    // Set the default font type (Arial) and the size (10)
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);

    // Set the column headings to row 3 and data to start on row 4
    $rowHeadStart = 3;
    $rowDataStart = 4;

    //Merge Cells for the report titles
    $objPHPExcel->getActiveSheet()->mergeCells("A1:T1"); // Report Title
    $objPHPExcel->getActiveSheet()->mergeCells("A2:T2"); // Date

    //Set Cell Text
    $objPHPExcel->getActiveSheet()->setCellValue("A1", $title);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', date("m/d/Y"));

    //Make Report title bold
    $objPHPExcel->getActiveSheet()->getStyle("A1:T1")->applyFromArray(array("font" => array( "bold" => true)));
    //Make the date italicized
    $objPHPExcel->getActiveSheet()->getStyle("A2:T2")->applyFromArray(array("font" => array( "italic" => true)));
    //Make Column headers bold
    $objPHPExcel->getActiveSheet()->getStyle("A3:T3")->applyFromArray(array("font" => array( "bold" => true)));


    //---------------------------------------------
    // Loop through to display the column headings
    //---------------------------------------------
    $col = 'A';
    foreach($headings as $heading)
    {
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowHeadStart,$heading);
       $col++;
    } // end $headings as $heading

    //-------------------------------------------------
    // Loop through the result set to display the data
    //-------------------------------------------------
    while ($row = mysql_fetch_row($result))
    {
       $col = 'A';
       foreach($row as $cell)
       {
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowDataStart,$cell);
          $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
          $col++;

       } // end $row as $cell
       $rowDataStart++;


       //-----------------------
       // Page/Cell Formatting
       //-----------------------
       //Set font size for the main report title
       $objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setSize(16);

       $objPHPExcel->setActiveSheetIndex(0);

       // Left align the entire document
       $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

       // Set the page orientation to landscape
       $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

    } //end $row = mysql_fetch_row($result)


    // Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="0teste.xlsx"');
    header('Cache-Control: max-age=0');

   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
   $objWriter->save('php://output');


    exit();

 ?>