<?php
session_start();
include('../include/connection.php');
include('../include/app_config.php');
include('../lib/app_stat.php');
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include '../classes/PHPExcel/IOFactory.php';

if(isset($_GET['download']))
{
    switch ($_GET['download'])
    {
        case 'complaints':
                $objPHPExcel = new \PHPExcel();
                // Set properties
                $objPHPExcel->getProperties()->setCreator("IWIN")
                ->setLastModifiedBy("IWIN")
                ->setTitle("IWIN Complaints List")
                ->setSubject("IWIN Complaints List")
                ->setDescription("IWIN Complaints List, Generated by IWIN.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("IWIN Complaints List");
                $objPHPExcel->getActiveSheet()->setTitle('IWIN Complaints List');
            
                
                $objPHPExcel->getActiveSheet()->setCellValue('A2', 'S/N');
                $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Complainant');
                $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Date');
                $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Category');
                $objPHPExcel->getActiveSheet()->setCellValue('E2', 'DISCO');
                $objPHPExcel->getActiveSheet()->setCellValue('F2', 'LGA');
                $objPHPExcel->getActiveSheet()->setCellValue('G2', 'State');
                $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Status');
            
               
                $filter = array();
                $sql = "select c.*,u.firstname,u.lastname,cc.name as categoryname,lg.name as lganame,dd.name as lgdisco,st.name as statename from complaints c 
                        left outer join users u on c.user_id=u.id
                        left outer join complaint_category cc on c.category_id=cc.id
                        left outer join client_details cd on c.user_id=cd.user_id 
                        left outer join localgovt lg on cd.lga_id=lg.id 
                        left outer join state st on cd.state_id=st.id
                        left outer join discos dd on lg.disco_id=dd.id";
                $conditions = array();
                $fields=array();
                if(isset($_GET['state']) && $_GET['state'] !="") {
                    $conditions[] = "cd.state_id=:state";
                    $fields[':state']=$_GET['state'];
                    $filter[] = GetStateName($_GET['state'], $con);
                }
                
                if(isset($_GET['lga']) && $_GET['lga'] !="") {
                    $conditions[] = "cd.lga_id=:lga";
                    $fields[':lga']=$_GET['lga'];
                    $filter[] = GetLgaName($_GET['lga'], $con);
                }
                
                if(isset($_GET['disco']) && $_GET['disco'] !="") {
                    $conditions[] = "u.disco_id=:disco";
                    $fields[':disco']=$_GET['disco'];
                    $filter[] = GetDiscoName($_GET['disco'], $con);
                }
                if(isset($_GET['status']) && $_GET['status'] !="") {
                    $conditions[] = "c.status=:status";
                    $fields[':status']=$_GET['status'];
                    $filter[] = GetComplaintStatus($_GET['status']);
                }
                if(isset($_GET['category']) && $_GET['category'] !="") {
                    $conditions[] = "c.category_id=:category";
                    $fields[':category']=$_GET['category'];
                    $filter[] = GetComplaintCategoryName($_GET['category'], $con);
                }
                if($_SESSION['user_role'] == DISCO_USER_KEY)
                {
                    $conditions[] = "lg.disco_id=:disco";
                    $fields[':disco']=$_SESSION['disco_id'];
                    $filter[] = GetDiscoName($_SESSION['disco_id'], $con);
                }
                if (count($conditions) > 0) {
                    $sql .= " WHERE " . implode(' AND ', $conditions);
                }
                $sql .= " order by id DESC";
                $r=$con->select_query($sql,$fields);
                $sn=1;
                $i = 3;
                foreach($r as $value)
                {
                    if($value['status']==RESOLVED_STATUS)
                                        {
                                            $status="Resolved";
                                        }
                                        else if($value['status'] == ACCEPTED_STATUS)
                                        {
                                            $status="Closed";
                                        }
                                        else if($value['status'] == PENDING_STATUS)
                                        {
                                            $status="Pending";
                                        }
               
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$i, $sn);
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$i, $value['firstname'].' '.$value['lastname']);
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$i, $value['date_created']);
                    $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3,$i, $value['categoryname']);
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$i, $value['lgdisco']);
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$i, $value['lganame']);
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$i, $value['statename']);
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,$i, $status);
            
                    $i++;
                    $sn++;
                }
                $filtertext = "";
                $header_title = strtoupper('Complaints');
                if(!empty($filter))
                {
                    $filtertext = implode(' > ', $filter);
                    $header_title = strtoupper('Complaints ('.$filtertext.')');
                }
                $objPHPExcel->getActiveSheet()->setCellValue('A1',$header_title);
                $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
                $header = 'A1:H1';
                $sheet = $objPHPExcel->getSheet(0);
                $sheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e1e1e1');
                $style = array(
                    'font' => array('bold' => true),
                    'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
                );
                $sheet->getStyle('A1:H1')->applyFromArray($style);
            
                $header = 'A2:H2';
                $sheet = $objPHPExcel->getSheet(0);
                $sheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('119fff');
                $style = array(
                    'font' => array('bold' => true),
                    'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
                );
                $sheet->getStyle($header)->applyFromArray($style);
                for ($col = ord('a1'); $col <= ord('h1'); $col++)
                {
                    $sheet->getColumnDimension(chr($col))->setAutoSize(true);
                }
                $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            
                // If you want to output e.g. a PDF file, simply do:
                //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
                //header('Content-type: application/vnd.ms-excel');
                $filename = '../complaints_report.xlsx';
                    $objWriter->save($filename);
                    echo $filename;
            
            break;
            
        case 'customers':
            $objPHPExcel = new \PHPExcel();
            // Set properties
            $objPHPExcel->getProperties()->setCreator("IWIN")
            ->setLastModifiedBy("IWIN")
            ->setTitle("IWIN Customers List")
            ->setSubject("IWIN Customers List")
            ->setDescription("IWIN Customers List, Generated by IWIN.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("IWIN Customers List");
            $objPHPExcel->getActiveSheet()->setTitle('IWIN Customers List');
            
            
            $objPHPExcel->getActiveSheet()->setCellValue('A2', 'S/N');
            $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Name');
            $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Phone');
            $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Email');
            $objPHPExcel->getActiveSheet()->setCellValue('E2', 'DISCO');
            $objPHPExcel->getActiveSheet()->setCellValue('F2', 'LGA');
            $objPHPExcel->getActiveSheet()->setCellValue('G2', 'State');
            $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Meter No.');
            
             
            $filter = array();
            $sql = "select c.*,u.firstname,u.lastname,u.email,lg.name as lganame,dd.name as lgdisco,st.name as statename from client_details c
                    inner join users u on c.user_id=u.id
                    left outer join localgovt lg on c.lga_id=lg.id 
                    left outer join state st on c.state_id=st.id
                    left outer join discos dd on lg.disco_id=dd.id";
            $conditions = array();
            $fields=array();
            if(isset($_GET['state']) && $_GET['state'] !="") {
                $conditions[] = "c.state_id=:state";
                $fields[':state']=$_GET['state'];
            }
            
            if(isset($_GET['lga']) && $_GET['lga'] !="") {
                $conditions[] = "c.lga_id=:lga";
                $fields[':lga']=$_GET['lga'];
            }
            
            if(isset($_GET['disco']) && $_GET['disco'] !="") {
                $conditions[] = "lg.disco_id=:disco";
                $fields[':disco']=$_GET['disco'];
            }
            if($_SESSION['user_role'] == DISCO_USER_KEY)
            {
                $conditions[] = "lg.disco_id=:disco";
                $fields[':disco']=$_SESSION['disco_id'];
                $filter[] = GetDiscoName($_SESSION['disco_id'], $con);
            }
            if (count($conditions) > 0) {
                $sql .= " WHERE " . implode(' AND ', $conditions). " order by id DESC";
            }
            $r=$con->select_query($sql,$fields);
            $sn=1;
            $i = 3;
            foreach($r as $value)
            {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$i, $sn);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$i, $value['firstname'].' '.$value['lastname']);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$i, $value['phone']);
                $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3,$i, $value['email']);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$i, $value['lgdisco']);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$i, $value['lganame']);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$i, $value['statename']);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,$i, $value['meternumber']);
            
                $i++;
                $sn++;
            }
            $filtertext = "";
            $header_title = strtoupper('Customers');
                if(!empty($filter))
                {
                    $filtertext = implode(' > ', $filter);
                    $header_title = strtoupper('Customers ('.$filtertext.')');
                }
            $objPHPExcel->getActiveSheet()->setCellValue('A1',$header_title);
            $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
            $header = 'A1:H1';
            $sheet = $objPHPExcel->getSheet(0);
            $sheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e1e1e1');
            $style = array(
                'font' => array('bold' => true),
                'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
            );
            $sheet->getStyle('A1:H1')->applyFromArray($style);
            
            $header = 'A2:H2';
            $sheet = $objPHPExcel->getSheet(0);
            $sheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('119fff');
            $style = array(
                'font' => array('bold' => true),
                'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
            );
            $sheet->getStyle($header)->applyFromArray($style);
            for ($col = ord('a1'); $col <= ord('h1'); $col++)
            {
            $sheet->getColumnDimension(chr($col))->setAutoSize(true);
            }
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            
            // If you want to output e.g. a PDF file, simply do:
                //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
            //header('Content-type: application/vnd.ms-excel');
            $filename = '../customers_report.xlsx';
            $objWriter->save($filename);
            echo $filename;
            break;
            
        case 'summary':
            $objPHPExcel = new \PHPExcel();
            // Set properties
            $objPHPExcel->getProperties()->setCreator("IWIN")
            ->setLastModifiedBy("IWIN")
            ->setTitle("IWIN Summary Report")
            ->setSubject("IWIN Summary Report")
            ->setDescription("IWIN Summary Report, Generated by IWIN.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("IWIN Summary Report");
            $objPHPExcel->getActiveSheet()->setTitle('IWIN Summary Report');
            
            
            $objPHPExcel->getActiveSheet()->setCellValue('A2', 'S/N');
            $objPHPExcel->getActiveSheet()->setCellValue('B2', 'DISCO');
            $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Total Complaints');
            $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Resolved');
            $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Pending');
            $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Closed');
            $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Total Prompt');
            $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Total Slow');
            $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Total Recurring');
            $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Average Response Time');
            
            $filter = array();
            if(!empty($_GET['category']))
            {
                $filter[] = GetComplaintCategoryName($_GET['category'], $con);
            }
            if(!empty($_GET['startdate']))
            {
                $filter[] = $_GET['startdate'];
            }
            if(!empty($_GET['enddate']))
            {
                $filter[] = $_GET['enddate'];
            }
            
            $sql = "select id,name from discos order by name";
            $q=$con->select_query($sql);
            $sn=1;
            
            $i = 3;
            foreach($q as $r)
            {
                $totalcomplaint = 0;
                
                $sql = "select c.id,c.date_created,c.resolved_date,service_rating,status from complaints c inner join client_details cd on c.user_id=cd.user_id inner join localgovt l on cd.lga_id=l.id where l.disco_id=:id";
                $field = array(':id'=>$r['id']);
                if(!empty($_GET['category']))
                {
                    $sql.=" AND category_id=:cat";
                    $field = array(':id'=>$r['id'], ':cat'=>$_GET['category']);
                }
                
                $all = $con->select_query($sql,$field);
                $resolvedcomplaint = 0;
                $pendingcomplaint = 0;
                $closedcomplaint = 0;
                $recurno = 0;
                $slowno = 0;
                $promptno = 0;
                $totaldays = 0;

                $j = 0;
                foreach ($all as $row)
                {
                    $row_exist = true;
                    $date_created = date('d-m-Y', strtotime($row['date_created']));
                    if(!empty($_GET['startdate']))
                    {
                        if(strtotime($date_created) < strtotime($_GET['startdate']))
                        {
                            unset($all[$j]);
                            $row_exist = false;
                        }
                    }
                    if(!empty($_GET['enddate']))
                    {
                        if(strtotime($date_created) > strtotime($_GET['enddate']))
                        {
                            unset($all[$j]);
                            $row_exist = false;
                        }
                    }
                    if($row_exist)
                    {
                        if($row['status'] == RESOLVED_STATUS)
                            $resolvedcomplaint += 1;
                        if($row['status'] == PENDING_STATUS)
                            $pendingcomplaint += 1;
                        if($row['status'] == ACCEPTED_STATUS)
                            $closedcomplaint += 1;
                        if($row['service_rating'] == PROMPT_SERVICE_RATING)
                            $promptno += 1;
                        if($row['service_rating'] == SLOW_SERVICE_RATING)
                            $slowno += 1;
                        if($row['service_rating'] == RECURRING_PROBLEM_RATING)
                            $recurno += 1;
                
                        if(!empty($row['resolved_date']) && !empty($row['date_created']))
                        {
                            $diff = strtotime($row['resolved_date']) - strtotime($row['date_created']);
                            $totaldays += round($diff / (60 * 60 * 24));
                        }
                        else
                        {
                            $totaldays+=0;
                        }
                    }
                    $j++;
                }
                $totalcomplaint = count($all);
                $avg_response_time = $totalcomplaint != 0 ? $totaldays/$totalcomplaint : 0;
                
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$i, $sn);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$i, $r['name']);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$i, $totalcomplaint);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,$i, $resolvedcomplaint);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$i, $pendingcomplaint);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$i, $closedcomplaint);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$i, $promptno);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,$i, $slowno);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8,$i, $recurno);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9,$i, $avg_response_time.' days');
            
                $i++;
                $sn++;
            }
            $filtertext = "";
            $header_title = strtoupper('Complaints Summary Report');
            if(!empty($filter))
            {
                $filtertext = implode(' - ', $filter);
                $header_title = strtoupper('Complaints Summary Report ('.$filtertext.')');
            }
            $objPHPExcel->getActiveSheet()->setCellValue('A1',$header_title);
            $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
            $header = 'A1:J1';
            $sheet = $objPHPExcel->getSheet(0);
            $sheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e1e1e1');
            $style = array(
                'font' => array('bold' => true),
                'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
            );
            $sheet->getStyle('A1:J1')->applyFromArray($style);
            
            $header = 'A2:J2';
            $sheet = $objPHPExcel->getSheet(0);
            $sheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('119fff');
            $style = array(
                'font' => array('bold' => true),
                'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
            );
            $sheet->getStyle($header)->applyFromArray($style);
            for ($col = ord('a1'); $col <= ord('j1'); $col++)
            {
            $sheet->getColumnDimension(chr($col))->setAutoSize(true);
            }
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            
            // If you want to output e.g. a PDF file, simply do:
            //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
            //header('Content-type: application/vnd.ms-excel');
            $filename = '../complaint_summary_report.xlsx';
            $objWriter->save($filename);
            echo $filename;
            break;
    }
}