<?php
defined('BASEPATH') OR exit('No direct script access allowed');
date_default_timezone_set('Asia/Kolkata');
class Reports extends CI_Controller {
    
    public function __construct()
    {
        parent::__construct();
        
        $this->load->library('session');
        
        $this->load->library('excel');
        $this->load->library('form_validation');
        $this->load->helper('form');
        $this->load->helper('url');
        $this->load->helper('html');
        $this->load->helper('sms');
        $this->load->model('admin_db');   
        
        $action_method = $this->router->fetch_method();
        if(!is_numeric($this->session->userdata('admin_id')) &&  !($action_method=='index' || $action_method=='admin_login')){
            redirect(base_url());
            exit;
        }
        
    }

    public function index()
    {
        $this->load->view('login/header');
        $this->load->view('login/index');
        $this->load->view('login/footer');
    }
    
    public function reports()
    {
        $data['title'] = 'Reports';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/reports', $data);
        $this->load->view('dashboard/footer');
    }

    public function dxb_sales_report()
    {
        $data['title'] = 'DXB Sales Report';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/dxb_sales_report', $data);
        $this->load->view('dashboard/footer');
    }

    public function view_dxb_sales_report()
    {
        $from_date = $this->input->post('from_date');
        $to_date = $this->input->post('to_date');
        header('Content-Type: application/json');
        
        $sales_details = $this->admin_db->get_dxb_sales_details_for_reports($from_date, $to_date);
        
        echo json_encode($sales_details);
    }
    
    public function download_dxb_sales_report() 
    {
        $from_date = $this->input->get('from_date');
        $to_date = $this->input->get('to_date');
        // create file name
        $fileName = 'DXB Sales Report - '.$from_date.' - '.$to_date.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $sales_details = $this->admin_db->get_dxb_sales_details_for_reports($from_date, $to_date);        
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'INVOICE NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', 'SALE TYPE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'CUSTOMER');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'ITEM');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'SALES STATUS');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'GROSS WEIGHT'); 
        $objPHPExcel->getActiveSheet()->SetCellValue('H2', 'GOLD PRICE 24K');   
        $objPHPExcel->getActiveSheet()->SetCellValue('I2', 'SELLING RATE');   
        $objPHPExcel->getActiveSheet()->SetCellValue('J2', 'BILLED AMOUNT');        
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );
        

        $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:J2")->getFont()->setBold( true );
        
        foreach(range('A','J') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_gross_weight = 0;
        $tot_billed_amount = 0;
        if($sales_details){
            foreach ($sales_details as $element) {
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['date']);
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['invoice_num']);
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, ($element['sale_type'] == 'Credit Sale')?('FIXED'):('UN FIXED'));
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['customer_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['item_name']);
                
                
                if($element["status"] == 1){
                    $sale_status = 'Payment Completed';
                }
                if($element["status"] == 2){
                    $sale_status = 'Payment Incomplete';
                }
                if($element["status"] == 3){
                    $sale_status = 'Payment Pending';
                }
                if($element["status"] == 4){
                    $sale_status = 'Pending Approval';
                }
                if($element["status"] == 5){
                    $sale_status = 'Rejected';
                }
                if($element["status"] == 6){
                    $sale_status = 'Deleted';
                }
                
                $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $sale_status);
                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['actual_grams']);
                $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, ($element['24k_rate'])?(number_format($element['24k_rate'],0,'.',',')):(''));
                $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, number_format($element['selling_rate'],0,'.',','));
                $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $element['amount']);
                $rowCount++;
                
                $tot_gross_weight += $element['actual_grams'];
                $tot_billed_amount += $element['amount'];
            }
        }
        
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', number_format($tot_gross_weight,3,'.',','));
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', number_format($tot_billed_amount,2,'.',','));
        
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function box_sales_report()
    {
        $data['title'] = 'BOX Sales Report';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/box_sales_report', $data);
        $this->load->view('dashboard/footer');
    }
    
    public function download_box_sales_report() 
    {
        $from_date = $this->input->get('from_date');
        $to_date = $this->input->get('to_date');
        // create file name
        $fileName = 'BOX Sales Report - '.$from_date.' - '.$to_date.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $sales_details = $this->admin_db->get_box_sales_details_for_reports($from_date, $to_date);
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'INVOICE NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', 'SALE TYPE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'CUSTOMER');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'ITEM');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'SALES STATUS');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'GROSS WEIGHT');  
        $objPHPExcel->getActiveSheet()->SetCellValue('H2', 'VALUE LKR');        
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );

        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:H2")->getFont()->setBold( true );
        
        foreach(range('A','H') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_gross_weight = 0;
        $tot_billed_amount = 0;
        if($sales_details){
            foreach ($sales_details as $element) {
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['date']);
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['invoice_num']);
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, ($element['sale_type'] == 'Credit Sale')?('FIXED'):('UN FIXED'));
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['customer_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['item_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['actual_grams']);
                $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $element['amount']);
                $rowCount++;
                
                $tot_gross_weight += $element['actual_grams'];
                $tot_billed_amount += $element['amount'];
            }
        }
        
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', number_format($tot_gross_weight,3,'.',','));
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', number_format($tot_billed_amount,2,'.',','));
        
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function dxb_debtors_detailed_report()
    {
        $data['title'] = 'DXB Debtors Detailed Report';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/dxb_debtors_detailed_report', $data);
        $this->load->view('dashboard/footer');
    }
    
    public function download_dxb_debtors_detailed_report() 
    {
        $date_as_of = $this->input->get('date_as_of');
        // create file name
        $fileName = 'DXB Debtors Detailed Report - '.$date_as_of.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $sales_details = $this->admin_db->get_dxb_debtors_details_for_reports($date_as_of);
        
//        echo '<pre>'. print_r($sales_details,1). '</pre>'; exit;
                
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'Ref. No.');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', "Party's Name");
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'Pending LKR Amount');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'Pending PG Amount');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'Due on');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'Overdue by days');      
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );

        $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("D2:D1000")->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->getStyle("E2:E1000")->getFont()->setBold( true );
        
        foreach(range('A','G') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        foreach ($sales_details as $elements) {
            
            $style = array(
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                )
            );

            $objPHPExcel->getActiveSheet()->getStyle("C")->applyFromArray($style);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $rowCount)->getFont()->setBold( true );
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $elements[0]['customer_name']);
            $rowCount++;
            
            $tot_credit_sale = 0.00;
            $tot_gold_sale = 0.00;
            $ref_no = '';
            foreach($elements as $element){
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['date']);
                
                if($element['sale_category'] == 'Normal'){
                    $ref_no = $element['invoice_num'];
                }else if($element['sale_category'] == 'Old Debts'){
                    $ref_no = $element['description'];
                }
                
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $ref_no);
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, '');
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, ($element['sale_type'] == 'Credit Sale')?(number_format($element['balance_amount'],2,'.','')):(''));
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, ($element['sale_type'] == 'Gold Sale')?(number_format($element['balance_amount'],2,'.','')):(''));
                $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, date('Y-m-d', strtotime($element['date']. ' +14 day')));
                
                $due_date = date_create(date('Y-m-d', strtotime($element['date']. ' +14 day')));
                $today_date = date_create($date_as_of);
                
                // calculates the difference between DateTime objects 
                $interval = date_diff($due_date, $today_date); 
                
                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $interval->format('%R%a'));
                $rowCount++;
            
                if($element['sale_type'] == 'Credit Sale'){
                    $tot_credit_sale += $element['balance_amount'];
                }else if($element['sale_type'] == 'Gold Sale'){
                    $tot_gold_sale += $element['balance_amount'];
                }
            
            }
            
            $objPHPExcel->getActiveSheet()->getStyle('D' . $rowCount .':E' . $rowCount)->getBorders()
            ->getTop()
            ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $rowCount .':E' . $rowCount)->getBorders()
            ->getBottom()
            ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, number_format($tot_credit_sale,2,'.',''));
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, number_format($tot_gold_sale,2,'.',''));
            $rowCount++;
            
        }
        
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function dxb_debtors_aging_report()
    {
        $data['title'] = 'DXB Debtors Aging Report';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/dxb_debtors_aging_report', $data);
        $this->load->view('dashboard/footer');
    }
    
    public function download_dxb_debtors_aging_report() 
    {
        $date_as_of = $this->input->get('date_as_of');
        $sale_type = $this->input->get('sale_type');
        
        if($sale_type == 'Credit Sale'){
            $report_type = 'LKR';
        }elseif($sale_type == 'Gold Sale'){
            $report_type = 'Pure Gold';
        }
            
        $one_week = date('Y-m-d',strtotime($date_as_of . "-7 days"));
        $two_weeks = date('Y-m-d',strtotime($date_as_of . "-14 days"));
        $four_weeks = date('Y-m-d',strtotime($date_as_of . "-30 days"));
        
        // create file name
        $fileName = 'BOX Debtors Aging Report - '.$date_as_of.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $sales_details = $this->admin_db->get_dxb_debtors_aging_for_reports($sale_type, $date_as_of, $one_week, $two_weeks, $four_weeks);
        
//        echo '<pre>'. print_r($sales_details,1). '</pre>'; exit;
        
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'CUSTOMER NAME');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', '0-7 Days');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', '8-14 Days');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', '15-30 Days');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'ABOVE 30 Days');       
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );

        $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:E2")->getFont()->setBold( true );
        
        foreach(range('A','E') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_0_7 = 0.00;
        $tot_8_14 = 0.00;
        $tot_15_30 = 0.00;
        $tot_above_30 = 0.00;
        $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Debtors Aging Report - ' . $report_type . ' | From - ' . $date_as_of);
        if($sales_details){
            foreach ($sales_details as $element) {
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['customer_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, number_format($element['0-7'],0,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, number_format($element['8-14'],0,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, number_format($element['15-30'],0,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, number_format($element['above_30'],0,'.',''));
                $rowCount++;
                
                $tot_0_7 += $element['0-7'];
                $tot_8_14 += $element['8-14'];
                $tot_15_30 += $element['15-30'];
                $tot_above_30 += $element['above_30'];
            }
            
            $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount .':E' . $rowCount)->getBorders()
                ->getTop()
                ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            
            if($sale_type == 'Credit Sale'){
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, number_format($tot_0_7,2,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, number_format($tot_8_14,2,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, number_format($tot_15_30,2,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, number_format($tot_above_30,2,'.',''));
            }elseif($sale_type == 'Gold Sale'){
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, number_format($tot_0_7,3,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, number_format($tot_8_14,3,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, number_format($tot_15_30,3,'.',''));
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, number_format($tot_above_30,3,'.',''));
            }
        
        }
        
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function dxb_cash_logs_report()
    {
        $data['title'] = 'DXB Cash Logs Report';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/dxb_cash_logs_report', $data);
        $this->load->view('dashboard/footer');
    }
    
    public function download_dxb_cash_logs_report() 
    {
        $from_date = $this->input->get('from_date');
        $to_date = $this->input->get('to_date');
        // create file name
        $fileName = 'DXB Cash Logs Report - '.$from_date.' - '.$to_date.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $sales_details = $this->admin_db->get_dxb_cash_logs_for_reports($from_date, $to_date);
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'ACTION');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', 'DESCRIPTION');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', '+ AMOUNT');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', '- AMOUNT');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'ACTION_BY');       
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );
        

        $objPHPExcel->getActiveSheet()->getStyle('A2:F2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:F2")->getFont()->setBold( true );
        
        foreach(range('A','F') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_plus_amount = 0;
        $tot_minus_amount = 0;
        foreach ($sales_details as $element) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['action_date']);
            $action_type = str_replace("_"," ",$element["item_type"]);            
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, ucwords($action_type));
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['description']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, ($element['action_type'] == 'Credited')?($element['amount']):(''));
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, ($element['action_type'] == 'Debited')?($element['amount']):(''));
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $element["contact_name"].' - '.$element["user_privilege"]);
            $rowCount++;

            if($element['action_type'] == 'Credited'){
                $tot_plus_amount += $element['amount'];
            }elseif($element['action_type'] == 'Debited'){
                $tot_minus_amount += $element['amount'];
            }
            
        }
        
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', number_format($tot_plus_amount,2,'.',','));
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', number_format($tot_minus_amount,2,'.',','));
        
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function sales_return_report()
    {
        $data['title'] = 'Sales Return Report';
        
        $data['active'] = 'active';
        $data['inactive'] = '';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/sales_return_report', $data);
        $this->load->view('dashboard/footer');
    }
    
    public function download_sales_return_report() 
    {
        $from_date = $this->input->get('from_date');
        $to_date = $this->input->get('to_date');
        // create file name
        $fileName = 'Sales Return Report - '.$from_date.' - '.$to_date.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $dxb_sales_returns = $this->admin_db->dxb_sales_return_report($from_date, $to_date);
        $box_sales_returns = $this->admin_db->box_sales_return_report($from_date, $to_date);

        $objPHPExcel = new PHPExcel();

        // Add new sheet
        $objWorkSheet = $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'INVOICE NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', 'SALE TYPE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'CUSTOMER');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'ITEM');   
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'SALES STATUS');    
        $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'GROSS WEIGHT');    
        $objPHPExcel->getActiveSheet()->SetCellValue('H2', 'GOLD PRICE 24K');    
        $objPHPExcel->getActiveSheet()->SetCellValue('I2', 'SELLING RATE');    
        $objPHPExcel->getActiveSheet()->SetCellValue('J2', 'BILLED AMOUNT');          
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );
        

        $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:J2")->getFont()->setBold( true );
        
        foreach(range('A','J') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_amount = 0;
        foreach ($box_sales_returns as $element) {

            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['date']);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['invoice_num']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['sale_type']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['customer_name']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['item_name']);

            if($element["sale_status"] == 1){
                $lbl_sale_status = 'Payment Completed';
            }
            if($element["sale_status"] == 2){
                $lbl_sale_status = 'Payment Incomplete';
            }
            if($element["sale_status"] == 3){
                $lbl_sale_status = 'Payment Pending';
            }
            if($element["sale_status"] == 4){
                $lbl_sale_status = 'Pending Approval';
            }
            if($element["sale_status"] == 5){
                $lbl_sale_status = 'Rejected';
            }
            if($element["sale_status"] == 6){
                $lbl_sale_status = 'Deleted';
            }

            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $lbl_sale_status);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['sales_grams']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $element['24k_rate']);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $element['selling_rate']);
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $element['amount']);

            $rowCount++;
            
        }

        $objPHPExcel->getActiveSheet()->setTitle('Sales Return - BOX');

        $objPHPExcel->setActiveSheetIndex(1);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'INVOICE NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', 'SALE TYPE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'CUSTOMER');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'ITEM');   
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'SALES STATUS');    
        $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'GROSS WEIGHT');    
        $objPHPExcel->getActiveSheet()->SetCellValue('H2', 'GOLD PRICE 24K');    
        $objPHPExcel->getActiveSheet()->SetCellValue('I2', 'SELLING RATE');    
        $objPHPExcel->getActiveSheet()->SetCellValue('J2', 'BILLED AMOUNT');          
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );
        

        $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:J2")->getFont()->setBold( true );
        
        foreach(range('A','J') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_amount = 0;
        foreach ($dxb_sales_returns as $element) {

            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['date']);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['invoice_num']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['sale_type']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['customer_name']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['item_name']);

            if($element["sale_status"] == 1){
                $lbl_sale_status = 'Payment Completed';
            }
            if($element["sale_status"] == 2){
                $lbl_sale_status = 'Payment Incomplete';
            }
            if($element["sale_status"] == 3){
                $lbl_sale_status = 'Payment Pending';
            }
            if($element["sale_status"] == 4){
                $lbl_sale_status = 'Pending Approval';
            }
            if($element["sale_status"] == 5){
                $lbl_sale_status = 'Rejected';
            }
            if($element["sale_status"] == 6){
                $lbl_sale_status = 'Deleted';
            }

            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $lbl_sale_status);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['sales_grams']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $element['24k_rate']);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $element['selling_rate']);
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $element['amount']);

            $rowCount++;
            
        }

        $objPHPExcel->getActiveSheet()->setTitle('Sales Return - DXB');
                
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function twofourk_sales_report()
    {
        $data['title'] = '24K Sales Report';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/24k_sales_report', $data);
        $this->load->view('dashboard/footer');
    }
    
    public function download_24k_sales_report() 
    {
        $from_date = $this->input->get('from_date');
        $to_date = $this->input->get('to_date');
        // create file name
        $fileName = '24K Sales Report - '.$from_date.' - '.$to_date.' - '.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $sales_details = $this->admin_db->get_24k_sales_details_for_reports($from_date, $to_date);
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', 'DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', 'INVOICE NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2', 'SALE TYPE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'CUSTOMER');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'ITEM');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'GROSS WEIGHT');  
        $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'GOLD PRICE 24K');  
        $objPHPExcel->getActiveSheet()->SetCellValue('H2', 'BILLED AMOUNT');        
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_MEDIUM
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );

        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->applyFromArray($styleArray);
        unset($styleArray);
        
        $objPHPExcel->getActiveSheet()->getStyle("A2:H2")->getFont()->setBold( true );
        
        foreach(range('A','H') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        
        // set Row
        $rowCount = 3;
        $tot_gross_weight = 0;
        $tot_billed_amount = 0;
        if($sales_details){
            foreach ($sales_details as $element) {
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['date']);
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['invoice_num']);
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, ($element['sale_type'] == 'Credit Sale')?('FIXED'):('UN FIXED'));
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['customer_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['item_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $element['gold_amount']);
                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['24k_rate']);
                $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $element['total_amount']);
                $rowCount++;
                
                $tot_gold_amount += $element['gold_amount'];
                $tot_total_amount += $element['total_amount'];
            }
        }
        
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', number_format($tot_gold_amount,3,'.',','));
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', number_format($tot_total_amount,2,'.',','));
        
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
        redirect(HTTP_UPLOAD_IMPORT_PATH . 'sales_reports/' . $fileName);
    }

    public function dxb_sales_receipts_report()
    {
        $data['title'] = 'DXB Sales Receipts Report';
        
        $data['fixing_history'] = 'active';
        $data['gold_receipts'] = '';
        $data['lkr_receipts'] = '';
        
        $this->load->view('dashboard/header');
        $this->load->view('dashboard/left_nav', $data);
        $this->load->view('reports/dxb_sales_receipts_report', $data);
        $this->load->view('dashboard/footer');
    }
             
}
