.-.-.-.-.-

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

class Bill_report extends CI_Controller
{

function __construct()
    {
        parent::__construct();
        // isLoggedIn();
        $this->load->model('bill_view/Bill_report_model');
        $this->load->model('reports/Reports_model');
        $this->load->model('fees_type/Fees_type_model');
        $this->load->model('academic/Academic_model');
        $this->load->model('admission/Admission_model');
        $this->load->model('bill_view/Bill_view_model');
        $this->load->model('balance_sheet_report/Balance_sheet_model');
        // $this->load->model('course/Course_model');

}
    public function Bill_report()
    {

if (hasPermission('report_billing')) {

$session_college_id = $this->session->userdata('college_id');
            $session_course_id = $this->session->userdata('course_id');
            $session_academic_id = $this->session->userdata('academic_id');

$college = $this->Academic_model->getCollege()->result_array();

$academic_year = $this->Academic_model->getAcademicYear()->result_array();
            $course_name = $this->Academic_model->getCourse()->result_array();

$page_data['page_title'] = 'Bill Report';
            $page_data['page_name'] = 'admin/reports/bill_reports';
            $page_data['course_name'] = $course_name;
            $page_data['college'] = $college;
            $page_data['academic_year'] = $academic_year;

$this->load->view('admin/template', $page_data);

// }else{

//     set_alert('warning', "Please select the course and academic year.");
            //     redirect(base_url('bill_report/bill_report'));

// }
        } else {

redirect(base_url('bill_report/bill_report'));
        }
    }

public function getBillReportsDetails()
    {
        $from_date = $this->input->post('from_date');
        $to_date = $this->input->post('to_date');
        $type = $this->input->post('type');
        $course_id = $this->input->post('course_id');
        $academic_year_id = $this->input->post('academic_year_id');
        $fee_type = $this->input->post('fee_type');

$transHistory = $this->Bill_report_model->getBillReportDetails($from_date, $to_date, $type, $course_id, $academic_year_id, $fee_type);
        echo json_encode(array('des' => $transHistory, 'f_d'));
    }

public function getAcademic()
    {
        $id = $this->input->post('id');

$res = $this->Academic_model->getCourseById($id);

echo $res;
    }

public function bill_reports_excel()

{

//   $check_val = $this->input->post('status');
        $from_date = $_GET['from_date'];
        $to_date = $_GET['to_date'];
        $course_id = $_GET['course_id'];
        $academic_year_id = $_GET['academic_year_id'];
        $fee_type = $_GET['fee_type'];

$lists = $this->Bill_report_model->getReportsDetails($from_date, $to_date, $course_id, $academic_year_id, $fee_type)->result_array();

$exceldata = array();
        $total_overall_bal = 0;

foreach ($lists as $list) {
            $row = array();

$student_name_english = $list['student_name_english'];
            $admission_id = $list['admission_id'];
            $bill_date = date('d-m-Y', strtotime($list['bill_date']));
            $bill_no = $list['bill_no'];
            $academic = $this->session->userdata('academic_id');
            $curr_year = date("Y");

$academic_year = $this->Admission_model->get_academicyear($academic)->row_array();

$from_academic_year = $academic_year['academic_from_year'];

$tot_amt = $list['tot_amt'];
           
            $ex_row = array();

$ex_row[] = $bill_date;
            $ex_row[] = $bill_no;
            $ex_row[] = $student_name_english;
            $ex_row[] = sprintf('%.2f', $tot_amt);
            $total_overall_bal += sprintf('%.2f', $tot_amt);

$exceldata[] =  $ex_row;
        }

$excel_report_title = "Bill Report";
        $spreadsheet = new Spreadsheet();

//Set document properties
        $spreadsheet->getProperties()->setCreator("Technogenesis")
            ->setLastModifiedBy("Technogenesis")
            ->setTitle("Bill Report")
            ->setSubject("Bill Report")
            ->setDescription("Bill Report")
            ->setKeywords("Bill Report")
            ->setCategory("Bill Report");

$spreadsheet->getProperties()->setCreator("technogenesis.in")
            ->setLastModifiedBy("Technognesis");

$spreadsheet->setActiveSheetIndex(0);

$sheet = $spreadsheet->getActiveSheet();

$sheet->setTitle($excel_report_title);

$sheet->setCellValue('A2', 'Date');
        $sheet->setCellValue('B2', 'Bill No');
        $sheet->setCellValue('C2', 'Student Name');
        // $sheet->setCellValue('D2', 'Total');
        $sheet->setCellValue('C1', 'Total Balance');
        $sheet->setCellValue('D1', $total_overall_bal);

$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
        $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
        $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
        $conditional1->addCondition('0');
        $conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
        $conditional1->getStyle()->getFont()->setBold(true);

$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('B2')->getConditionalStyles();
        $conditional1->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        //make the font become bold
        $conditional1->getStyle('A2')->getFont()->setBold(true);
        $conditional1->getStyle('A1')->getFont()->setBold(true);
        $conditional1->getStyle('B1')->getFont()->setBold(true);
        $conditional1->getStyle('C3')->getFont()->setBold(true);
        $conditional1->getStyle('D3')->getFont()->setBold(true);
        $conditional1->getStyle('E3')->getFont()->setBold(true);
        $conditional1->getStyle('F3')->getFont()->setBold(true);
        $conditional1->getStyle('A3')->getFont()->setSize(16);
        $conditional1->getStyle('A3')->getFill()->getStartColor()->setARGB('#333');

//make the font become bold
        $sheet->getStyle('A1')->getFont()->setBold(true);
        $sheet->getStyle('A1')->getFont()->setSize(16);
        $sheet->getStyle('A1')->getFill()->getStartColor()->setARGB('#333');

for ($col = ord('A1'); $col <= ord('H1'); $col++) { //set column dimension
            $sheet->getColumnDimension(chr($col))->setAutoSize(true);
        }
        $sheet->fromArray($exceldata, null, 'A3');

$writer = new Csv($spreadsheet);

$filename = "Bill Report_";

public function bill_reports_excels()
    {

$academic = $this->session->userdata('academic_id');
        $course = $this->session->userdata('course_id');
        $college = $this->session->userdata('college_id');

$college = $this->Academic_model->getCollege()->result_array();
        $academic_year = $this->Academic_model->getAcademicYear()->result_array();
        $course_name = $this->Bill_report_model->getCourseName()->result_array();

$lists = $this->Bill_report_model->getbillreport($course_name, $academic_year)->result_array();

$exceldata = array();
        $i = 1;
        foreach ($lists as $list) {
            $row = array();
            $name = $list['student_name_english'];
            $date_birth = date('d-m-Y', strtotime($list['Date']));
            $bill_no = $list['bill_no'];
            $total_amount = $list['tot_amt'];

$ex_row = array();

$ex_row[] = $i;
            $ex_row[] = $name;
            $ex_row[] = $date_birth;
            $ex_row[] = $bill_no;
            $ex_row[] = $total_amount;
            $exceldata[] =  $ex_row;

$i++;
        }

$excel_report_title = "Bill Reports";
        $spreadsheet = new Spreadsheet();

//Set document properties
        $spreadsheet->getProperties()->setCreator("Technogenesis")
            ->setLastModifiedBy("Technogenesis")
            ->setTitle("Bill Reports Details")
            ->setSubject("Bill Reports Details")
            ->setDescription("Bill Reports Details")
            ->setKeywords("Bill Reports Details")
            ->setCategory("Bill Reports Details");

$spreadsheet->getProperties()->setCreator("technogenesis.in")
            ->setLastModifiedBy("Technogenesis");

$spreadsheet->setActiveSheetIndex(0);

$sheet = $spreadsheet->getActiveSheet();

$sheet->setTitle($excel_report_title);

$sheet->setCellValue('A1', 'S.No');
        $sheet->setCellValue('B1', 'Name in English');
        $sheet->setCellValue('C1', 'Date');
        $sheet->setCellValue('D1', 'Bill No');
        $sheet->setCellValue('E1', 'Total Amount');

for ($col = ord('A'); $col <= ord('H'); $col++) { //set column dimension
            //$sheet->getColumnDimension(chr($col))->setAutoSize(true);
            //$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        }
        // print_r($exceldata);
        $sheet->fromArray($exceldata, null, 'A2');

$writer = new Csv($spreadsheet);
        $filename = "Bill Reports_" . $course_study . "_" . $academic_year;

header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '.csv"');
        header('Cache-Control: max-age=0');
        // alert();
        $writer->save('php://output'); // download file 
        // exit;
        // ($sheet) ? true : false ;
        // echo  json_encode(array('id' => $ad_id));  
        $result = true;
    }
}

/var/www/yapwebtv.com/../../www/krcollege.co/application/controllers/Bill_report.php