<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Report_Model extends MY_Model {
function __construct() {
parent::__construct();
}
public function get_income_report($school_id, $academic_year_id, $group_by, $date_from, $date_to){
$group_by_sql = '';
$group_by_field = '';
if($group_by && $group_by == 'daily'){
$group_by_sql .= " GROUP BY T.payment_date ORDER BY T.payment_date ASC";
$group_by_field .= ", DATE_FORMAT(T.payment_date, '%b %d, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'monthly'){
$group_by_sql .= " GROUP BY MONTH(T.payment_date), YEAR(I.date) ORDER BY I.date ASC";
$group_by_field .= ", DATE_FORMAT(T.payment_date, '%M, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'yearly'){
$group_by_sql .= " GROUP BY I.academic_year_id ORDER BY I.academic_year_id ASC";
$group_by_field .= ", DATE_FORMAT(T.payment_date, '%Y') AS group_by_field";
}elseif($group_by && $group_by == 'income_by'){
$group_by_sql .= " GROUP BY T.payment_method ORDER BY T.payment_method ASC";
$group_by_field .= ", T.payment_method AS group_by_field";
}
$sql = "SELECT I.*, SUM(T.amount) AS total_amount, T.payment_date, AY.session_year $group_by_field
FROM invoices AS I
LEFT JOIN transactions AS T ON T.invoice_id = I.id
LEFT JOIN academic_years AS AY ON AY.id = I.academic_year_id
WHERE I.status = 1 AND T.amount > 0";
if($date_from != '' && $date_to != ''){
$sql .= " AND I.date >= '$date_from' AND I.date <= '$date_to' ";
}
if($date_from != '' && $date_to == ''){
$sql .= "I.date >= '$date_from'";
}
if($academic_year_id){
$sql .= " AND I.academic_year_id = '$academic_year_id'";
}
if($school_id){
$sql .= " AND I.school_id = '$school_id'";
}
$sql .= $group_by_sql;
return $this->db->query($sql)->result();
}
public function get_expenditure_report($school_id, $academic_year_id, $group_by, $date_from, $date_to){
$group_by_sql = '';
$group_by_field = '';
if($group_by && $group_by == 'expenditure_head'){
$group_by_sql .= " GROUP BY H.title ORDER BY H.title ASC";
$group_by_field .= ", H.title AS group_by_field";
}elseif($group_by && $group_by == 'daily'){
$group_by_sql .= " GROUP BY E.date ORDER BY E.date ASC";
$group_by_field .= ", DATE_FORMAT(E.date, '%b %d, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'monthly'){
$group_by_sql .= " GROUP BY MONTH(E.date), YEAR(E.date) ORDER BY E.date ASC";
$group_by_field .= ", DATE_FORMAT(E.date, '%M, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'yearly'){
$group_by_sql .= " GROUP BY E.academic_year_id ORDER BY E.academic_year_id ASC";
$group_by_field .= ", DATE_FORMAT(E.date, '%Y') AS group_by_field";
}elseif($group_by && $group_by == 'expenditure_by'){
$group_by_sql .= " GROUP BY E.expenditure_via ORDER BY E.expenditure_via ASC";
$group_by_field .= ", E.expenditure_via AS group_by_field";
}
$sql = "SELECT E.*, SUM(E.amount) AS total_amount, H.title AS head, AY.session_year $group_by_field
FROM expenditures AS E
LEFT JOIN expenditure_heads AS H ON H.id = E.expenditure_head_id
LEFT JOIN academic_years AS AY ON AY.id = E.academic_year_id
WHERE E.status = 1 ";
if($date_from != '' && $date_to != ''){
$sql .= " AND E.date >= '$date_from' AND E.date <= '$date_to' ";
}
if($date_from != '' && $date_to == ''){
$sql .= "E.date >= '$date_from'";
}
if($academic_year_id){
$sql .= " AND E.academic_year_id = '$academic_year_id'";
}
if($school_id){
$sql .= " AND E.school_id = '$school_id'";
}
$sql .= $group_by_sql;
return $this->db->query($sql)->result();
}
public function get_invoice_report($school_id, $academic_year_id, $group_by, $date_from, $date_to){
$group_by_sql = '';
$group_by_field = '';
if($group_by && $group_by == 'daily'){
$group_by_sql .= " GROUP BY I.date ORDER BY I.date ASC";
$group_by_field .= ", DATE_FORMAT(I.date, '%b %d, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'monthly'){
$group_by_sql .= " GROUP BY MONTH(I.date), YEAR(I.date) ORDER BY I.date ASC";
$group_by_field .= ", DATE_FORMAT(I.date, '%b, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'yearly'){
$group_by_sql .= " GROUP BY I.academic_year_id ORDER BY I.academic_year_id ASC";
$group_by_field .= ", DATE_FORMAT(I.date, '%Y') AS group_by_field";
}elseif($group_by && $group_by == 'class'){
$group_by_sql .= " GROUP BY I.class_id ORDER BY I.class_id ASC";
$group_by_field .= ", C.name AS group_by_field";
}elseif($group_by && $group_by == 'paid_status'){
$group_by_sql .= " GROUP BY I.paid_status ORDER BY I.paid_status ASC";
$group_by_field .= ", I.paid_status AS group_by_field";
}
$sql = "SELECT I.*, SUM(I.net_amount) AS total_amount, SUM(I.discount) AS total_discount, AY.session_year $group_by_field
FROM invoices AS I
LEFT JOIN academic_years AS AY ON AY.id = I.academic_year_id
LEFT JOIN classes AS C ON C.id = I.class_id
WHERE I.status = 1 AND I.invoice_type != 'income'";
if($date_from != '' && $date_to != ''){
$sql .= " AND I.date >= '$date_from' AND I.date <= '$date_to' ";
}
if($date_from != '' && $date_to == ''){
$sql .= "I.date >= '$date_from'";
}
if($academic_year_id){
$sql .= " AND I.academic_year_id = '$academic_year_id'";
}
if($school_id){
$sql .= " AND I.school_id = '$school_id'";
}
$sql .= $group_by_sql;
return $this->db->query($sql)->result();
}
public function get_expenditure_by_date($school_id, $date){
$sql = "SELECT SUM(E.amount) AS total_amount
FROM expenditures AS E
WHERE E.date = '$date' AND E.school_id = '$school_id' GROUP BY E.date ASC";
$exp = $this->db->query($sql)->row();
return isset($exp->total_amount) ? $exp->total_amount: 0;
}
public function get_income_by_date($school_id, $date){
$sql = "SELECT SUM(I.net_amount) AS total_amount
FROM invoices AS I
WHERE I.date = '$date' AND I.school_id = '$school_id' GROUP BY I.date ASC";
$income= $this->db->query($sql)->row();
return isset($income->total_amount) ? $income->total_amount: 0;
}
public function get_library_report($school_id, $academic_year_id, $group_by, $date_from, $date_to){
$group_by_sql = '';
$group_by_field = '';
if($group_by && $group_by == 'daily'){
$group_by_sql .= " GROUP BY BI.issue_date ORDER BY BI.issue_date ASC";
$group_by_field .= ", DATE_FORMAT(BI.issue_date, '%b %d, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'monthly'){
$group_by_sql .= " GROUP BY MONTH(BI.issue_date), YEAR(BI.issue_date) ORDER BY BI.issue_date ASC";
$group_by_field .= ", DATE_FORMAT(BI.issue_date, '%M, %Y') AS group_by_field";
}elseif($group_by && $group_by == 'yearly'){
$group_by_sql .= " GROUP BY BI.academic_year_id ORDER BY BI.academic_year_id ASC";
$group_by_field .= ", DATE_FORMAT(BI.issue_date, '%Y') AS group_by_field";
}elseif($group_by && $group_by == 'class'){
$group_by_sql .= " GROUP BY C.name ORDER BY C.name ASC";
$group_by_field .= ", C.name AS group_by_field";
}
$sql = "SELECT BI.*, COUNT(BI.id) AS total_issue, SUM(BI.is_returned) AS total_returned, AY.session_year $group_by_field
FROM book_issues AS BI
LEFT JOIN library_members AS LM ON LM.id = BI.library_member_id
LEFT JOIN students AS S ON S.user_id = LM.user_id
LEFT JOIN enrollments AS E ON E.student_id = S.id
LEFT JOIN classes AS C ON C.id = E.class_id
LEFT JOIN academic_years AS AY ON AY.id = BI.academic_year_id
WHERE BI.status = 1 ";
if($date_from != '' && $date_to != ''){
$sql .= " AND BI.issue_date >= '$date_from' AND BI.return_date <= '$date_to' ";
}
if($date_from != '' && $date_to == ''){
$sql .= "BI.issue_date >= '$date_from'";
}
if($academic_year_id){
$sql .= " AND BI.academic_year_id = '$academic_year_id'";
}
if($academic_year_id){
$sql .= " AND E.academic_year_id = '$academic_year_id'";
}
if($school_id){
$sql .= " AND BI.school_id = '$school_id'";
}
$sql .= $group_by_sql;
return $this->db->query($sql)->result();
}
public function get_student_list($school_id, $academic_year_id, $class_id, $section_id = null){
$this->db->select('E.roll_no, S.id, S.name');
$this->db->from('enrollments AS E');
$this->db->join('students AS S', 'S.id = E.student_id', 'left');
$this->db->where('E.academic_year_id', $academic_year_id);
$this->db->where('E.class_id', $class_id);
if($section_id){
$this->db->where('E.section_id', $section_id);
}
$this->db->where('E.school_id', $school_id);
$this->db->where('S.status_type', 'regular');
return $this->db->get()->result();
}
public function get_student_report($school_id, $academic_year_id, $group_by){
$group_by_sql = '';
$group_by_field = '';
$sql_plus = '';
if($group_by && $group_by == 'gender'){
$group_by_sql .= " GROUP BY C.name ORDER BY C.id ASC";
$group_by_field .= ", C.name AS group_by_field";
}elseif($group_by && $group_by == 'vehicle'){
$group_by_sql .= " GROUP BY C.name ORDER BY C.id ASC";
$group_by_field .= ", C.name AS group_by_field";
$sql_plus .= " AND S.is_transport_member = '1'";
}elseif($group_by && $group_by == 'library'){
$group_by_sql .= " GROUP BY C.name ORDER BY C.id ASC";
$group_by_field .= ", C.name AS group_by_field";
$sql_plus .= " AND S.is_library_member = '1'";
}elseif($group_by && $group_by == 'hostel'){
$group_by_sql .= " GROUP BY C.name ORDER BY C.id ASC";
$group_by_field .= ", C.name AS group_by_field";
$sql_plus .= " AND S.is_hostel_member = '1'";
}elseif($group_by && $group_by == 'class'){
$group_by_sql .= " GROUP BY C.name ORDER BY C.id ASC";
$group_by_field .= ", C.name AS group_by_field";
}
$sql = "SELECT S.id, COUNT(S.id) AS total, C.id as class_id, E.academic_year_id, AY.session_year $group_by_field
FROM students AS S
LEFT JOIN enrollments AS E ON E.student_id = S.id
LEFT JOIN classes AS C ON C.id = E.class_id
LEFT JOIN academic_years AS AY ON AY.id = E.academic_year_id
WHERE S.status = 1 ";
if($academic_year_id){
$sql_plus .= " AND E.academic_year_id = '$academic_year_id'";
}
if($school_id){
$sql_plus .= " AND E.school_id = '$school_id'";
}
$sql .= $sql_plus;
$sql .= $group_by_sql;
return $this->db->query($sql)->result();
}
public function get_student_by_gender($school_id, $group_by, $class_id, $academic_year_id, $gender){
$extra = '';
if($group_by == 'vehicle'){
$extra = "AND S.is_transport_member = '1'";
}
if($group_by == 'library'){
$extra = "AND S.is_library_member = '1'";
}
if($group_by == 'hostel'){
$extra = "AND S.is_hostel_member = '1'";
}
$sql = "SELECT COUNT(S.id) AS total
FROM students AS S
LEFT JOIN enrollments AS E ON E.student_id = S.id
LEFT JOIN classes AS C ON C.id = E.class_id
WHERE S.status = 1 AND S.gender = '$gender'
AND E.class_id = '$class_id'
AND E.school_id = '$school_id'
$extra
AND E.academic_year_id = '$academic_year_id'";
return $this->db->query($sql)->row()->total;
}
public function get_student_invoice_report($school_id, $academic_year_id, $class_id, $user_id){
$this->db->select('I.*, SUM(T.amount) AS paid_amount, C.name AS class_name, ST.name AS student, AY.session_year');
$this->db->from('invoices AS I');
$this->db->join('transactions AS T', 'T.invoice_id = I.id', 'left');
$this->db->join('students AS ST', 'ST.user_id = I.user_id', 'left');
$this->db->join('classes AS C', 'C.id = I.class_id', 'left');
$this->db->join('academic_years AS AY', 'AY.id = I.academic_year_id', 'left');
if($school_id != ''){
$this->db->where('I.school_id', $school_id);
}
if($class_id != ''){
$this->db->where('I.class_id', $class_id);
}
if($user_id != ''){
$this->db->where('I.user_id', $user_id);
}
if($academic_year_id){
$this->db->where('I.academic_year_id', $academic_year_id);
}
$this->db->group_by('I.id', 'DESC');
return $this->db->get()->result();
}
public function get_student_activity_report($school_id, $academic_year_id, $class_id, $student_id){
$this->db->select('SA.*, C.name AS class_name, ST.name, S.name AS section, AY.session_year');
$this->db->from('student_activities AS SA');
$this->db->join('students AS ST', 'ST.id = SA.student_id', 'left');
$this->db->join('classes AS C', 'C.id = SA.class_id', 'left');
$this->db->join('sections AS S', 'S.id = SA.section_id', 'left');
$this->db->join('academic_years AS AY', 'AY.id = SA.academic_year_id', 'left');
if($school_id != ''){
$this->db->where('SA.school_id', $school_id);
}
if($class_id != ''){
$this->db->where('SA.class_id', $class_id);
}
if($student_id != ''){
$this->db->where('SA.student_id', $student_id);
}
if($academic_year_id){
$this->db->where('SA.academic_year_id', $academic_year_id);
}
return $this->db->get()->result();
}
public function get_payroll_report($school_id,$academic_year_id, $group_by, $payment_to, $month){
$group_by_sql = '';
$group_by_field = '';
if($group_by && $group_by == 'salary_type'){
$group_by_sql .= " GROUP BY SP.salary_type ORDER BY SP.salary_type ASC";
$group_by_field .= ", SP.salary_type AS group_by_field";
}elseif($group_by && $group_by == 'payment_to'){
$group_by_sql .= " GROUP BY SP.payment_to ORDER BY SP.payment_to ASC";
$group_by_field .= ", SP.payment_to AS group_by_field";
$group_by_field .= ", SP.payment_to AS group_by_field";
}elseif($group_by && $group_by == 'month'){
$group_by_sql .= " GROUP BY SP.salary_month ORDER BY SP.salary_month ASC";
$group_by_field .= ", SP.salary_month AS group_by_field";
}elseif($group_by && $group_by == 'yearly'){
$group_by_sql .= " GROUP BY SP.academic_year_id ORDER BY SP.academic_year_id ASC";
$group_by_field .= ", DATE_FORMAT(SP.salary_month, '%Y') AS group_by_field";
}elseif($group_by && $group_by == 'expenditure_by'){
$group_by_sql .= " GROUP BY SP.payment_method ORDER BY SP.payment_method ASC";
$group_by_field .= ", SP.payment_method AS group_by_field";
}
$sql = "SELECT SP.id, SUM(SP.net_salary) AS total_amount, AY.session_year $group_by_field
FROM salary_payments AS SP
LEFT JOIN academic_years AS AY ON AY.id = SP.academic_year_id
WHERE SP.status = 1 ";
if($academic_year_id){
$sql .= " AND SP.academic_year_id = '$academic_year_id'";
}
if($month){
$sql .= " AND SP.salary_month = '$month'";
}
if($school_id){
$sql .= " AND SP.school_id = '$school_id'";
}
if($payment_to){
$sql .= " AND SP.payment_to = '$payment_to'";
}
$sql .= $group_by_sql;
return $this->db->query($sql)->result();
}
public function get_student_due_fee_report($school_id, $academic_year_id, $class_id, $user_id){
$this->db->select('I.*, SUM(T.amount) AS paid_amount, C.name AS class_name, ST.name AS student, AY.session_year');
$this->db->from('invoices AS I');
$this->db->join('transactions AS T', 'T.invoice_id = I.id', 'left');
$this->db->join('students AS ST', 'ST.user_id = I.user_id', 'left');
$this->db->join('classes AS C', 'C.id = I.class_id', 'left');
$this->db->join('academic_years AS AY', 'AY.id = I.academic_year_id', 'left');
if($school_id != ''){
$this->db->where('I.school_id', $school_id);
}
if($class_id != ''){
$this->db->where('I.class_id', $class_id);
}
if($user_id != ''){
$this->db->where('I.user_id', $user_id);
}
if($academic_year_id){
$this->db->where('I.academic_year_id', $academic_year_id);
}
$this->db->where('I.paid_status !=', 'paid');
$this->db->group_by('I.id', 'DESC');
return $this->db->get()->result();
}
public function get_student_fee_collection_report($school_id, $academic_year_id, $class_id, $user_id, $date_from, $date_to){
$this->db->select('T.*, T.note,ST.name AS student, C.name AS class_name, AY.session_year');
$this->db->from('transactions AS T');
$this->db->join('invoices AS I', 'I.id = T.invoice_id', 'left');
$this->db->join('students AS ST', 'ST.user_id = I.user_id', 'left');
$this->db->join('classes AS C', 'C.id = I.class_id', 'left');
$this->db->join('academic_years AS AY', 'AY.id = T.academic_year_id', 'left');
if($school_id){
$this->db->where('T.school_id', $school_id);
}
if($date_from != '' && $date_to != ''){
$this->db->where('T.payment_date >=', $date_from);
$this->db->where('T.payment_date <=', $date_to);
}
if($date_from != '' && $date_to == ''){
$this->db->where('T.payment_date >=', $date_from);
}
if($academic_year_id){
$this->db->where('T.academic_year_id', $academic_year_id);
}
if($class_id != ''){
$this->db->where('I.class_id', $class_id);
}
if($user_id != ''){
$this->db->where('I.user_id', $user_id);
}
return $this->db->get()->result();
}
public function get_transaction_report($school_id, $academic_year_id, $date_from, $date_to){
$this->db->select('T.*, T.note, AY.session_year');
$this->db->from('transactions AS T');
$this->db->join('invoices AS I', 'I.id = T.invoice_id', 'left');
$this->db->join('academic_years AS AY', 'AY.id = T.academic_year_id', 'left');
if($school_id){
$this->db->where('T.school_id', $school_id);
}
if($date_from != '' && $date_to != ''){
$this->db->where('T.payment_date >=', $date_from);
$this->db->where('T.payment_date <=', $date_to);
}
if($date_from != '' && $date_to == ''){
$this->db->where('T.payment_date >=', $date_from);
}
if($academic_year_id){
$this->db->where('T.academic_year_id', $academic_year_id);
}
$this->db->order_by('T.payment_date', 'ASC');
return $this->db->get()->result();
}
public function get_debit_by_date($school_id, $date){
$this->db->select('E.amount AS debit, E.note, E.note, H.title AS head');
$this->db->from('expenditures AS E');
$this->db->join('expenditure_heads AS H', 'H.id = E.expenditure_head_id', 'left');
$this->db->where('E.school_id', $school_id);
$this->db->where('E.date', $date);
return $this->db->get()->result();
}
public function get_credit_by_date($school_id, $date){
$this->db->select('T.amount as credit, T.note, I.invoice_type');
$this->db->from('transactions AS T');
$this->db->join('invoices AS I', 'I.id = T.invoice_id', 'left');
$this->db->where('T.school_id', $school_id);
$this->db->where('T.payment_date', $date);
return $this->db->get()->result();
}
public function get_student_examresult_report($school_id, $academic_year_id, $class_id, $section_id){
$this->db->select('FR.*, G.name AS grade, E.roll_no, ST.name AS student, C.name AS class_name, S.name AS section, AY.session_year');
$this->db->from('final_results AS FR');
$this->db->join('enrollments AS E', 'E.student_id = FR.student_id', 'left');
$this->db->join('students AS ST', 'ST.id = E.student_id', 'left');
$this->db->join('classes AS C', 'C.id = E.class_id', 'left');
$this->db->join('sections AS S', 'S.id = E.section_id', 'left');
$this->db->join('academic_years AS AY', 'AY.id = E.academic_year_id', 'left');
$this->db->join('grades AS G', 'G.id = FR.grade_id', 'left');
if($school_id){
$this->db->where('E.school_id', $school_id);
}
if($academic_year_id){
$this->db->where('E.academic_year_id', $academic_year_id);
}
if($class_id != ''){
$this->db->where('E.class_id', $class_id);
}
if($section_id != ''){
$this->db->where('E.section_id', $section_id);
}
$this->db->order_by('FR.avg_grade_point', 'DESC');
return $this->db->get()->result();
}
}