338 lines
15 KiB
PHP
Executable File
338 lines
15 KiB
PHP
Executable File
<?php
|
|
/**
|
|
* Date: 11/9/17
|
|
* Time: 5:28 PM
|
|
*/
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
|
|
class Reports_model extends CI_Model
|
|
{
|
|
public function status($bat=null,$br=null,$u=null){
|
|
|
|
$sql = "select @s:=@s+1 sl,sms.sid as ssid,std.name as name,std.father as father,br.bname as bname,ifnull(b.batch,'-') as batch,std.phone as phone,sms.cid as scid,sfp.bdate as bdate,sms.date as sdate,list.status as status,u.uname as uname,cm.course as course,sms.sem as sem,sfs.batch as fbatch,sfs.cid as fcid,cfd.syear as syear,sum(sfs.cf) as crsfee,sum(sfs.stf) as stf,sum(sfs.others) as others,(sum(sfs.cf) + sum(sfs.stf) + sum(sfs.others)) as total,ifnull(sfp.bamount,0) as bamount,(sum(sfs.cf) + (sum(sfs.stf) + sum(sfs.others)) - ifnull(sfp.bamount,0)) as balance
|
|
from (SELECT @s:= 0) AS s,
|
|
(SELECT StudentID as sid,CourseID as cid,ListCode as lcode,BranchCode as branch,SDate as date,Sem as sem FROM T_StudyMaterialStatus
|
|
group by sid,sem) as sms
|
|
left join
|
|
(SELECT FeesID as fid,BatchCode as batch,StudentID as sid,CourseID as cid,FeesType as ftype,sum(CourseFees) as cf,Sum(STFOrWR) as stf,sum(Others) as others FROM T_Students_Fees_Status
|
|
group by sid,batch) as sfs on sfs.sid=sms.sid
|
|
left join
|
|
(SELECT StudentID as sid,FeesId as fid,BillDate as bdate,sum(BillAmount) as bamount
|
|
FROM T_Students_Fees_PaidDetails
|
|
group by sid,fid,bdate) as sfp on sfp.sid=sfs.sid and sfp.fid=sfs.fid
|
|
left join
|
|
(SELECT ID as cid,CourseID as ccid,FeesType as ftype,Sem_Year as syear FROM T_Course_Fees_Details) as cfd
|
|
on cfd.cid=sms.cid
|
|
left join
|
|
(SELECT CourseID as cid,UniversityID as uid,CourseName as course FROM T_CourseMaster) as cm
|
|
on cm.cid=sfs.cid
|
|
left join
|
|
(SELECT UniversityID as uid,UniversityName as uname FROM T_UniversityMaster) as u
|
|
on u.uid=cm.uid
|
|
left join
|
|
(SELECT ListCode as lcode,ListName as status FROM T_PickListDetails) as list
|
|
on list.lcode=sms.lcode
|
|
left join
|
|
(SELECT StudentID as sid,MobileNumber as phone,FirstName as name,Fathername as father FROM T_StudentDetails) as std on std.sid=sms.sid
|
|
left join
|
|
(SELECT BatchCode as bcode,BatchName as batch FROM T_BatchMaster) as b on b.bcode=sfs.batch
|
|
left join
|
|
(SELECT BranchCode as brcode,BranchName as bname FROM T_BranchMaster) as br on br.brcode=sms.branch
|
|
where std.name != 'null'
|
|
";
|
|
if ($br!= ''){
|
|
|
|
$sql.=" and sms.branch = '".$br."'";
|
|
|
|
}
|
|
if ($bat!= ''){
|
|
|
|
$sql.=" and b.bcode = '".$bat."'";
|
|
|
|
}
|
|
if ($u!= ''){
|
|
|
|
$sql.=" and u.uid = '".$u."'";
|
|
|
|
}
|
|
$sql.=" group by ssid,sem,fbatch";
|
|
$sql.=" order by sl";
|
|
$leadDet=$this->db->query($sql);
|
|
$statusDetails = $leadDet->result();
|
|
if (count($statusDetails) > 0) {
|
|
$results['statusList'] = true;
|
|
$results['statusr'] = $statusDetails;
|
|
} else {
|
|
$results['statusList'] = false;
|
|
$results['message'] = 'No record found';
|
|
}
|
|
|
|
return $results;
|
|
|
|
}
|
|
public function batch(){
|
|
|
|
|
|
$sql = "SELECT BatchCode as batch,BatchName as bname FROM T_BatchMaster
|
|
group by batch;";
|
|
$b=$this->db->query($sql);
|
|
$batch = $b->result();
|
|
if (count($batch) > 0) {
|
|
$results['batchlist'] = true;
|
|
$results['batch'] = $batch;
|
|
} else {
|
|
$results['batchlist'] = false;
|
|
$results['message'] = 'No record found';
|
|
}
|
|
$sql = "SELECT BranchCode as brcode,BranchName as bname FROM T_BranchMaster";
|
|
$b=$this->db->query($sql);
|
|
$batch = $b->result();
|
|
if (count($batch) > 0) {
|
|
$results['branchlist'] = true;
|
|
$results['branch'] = $batch;
|
|
} else {
|
|
$results['branchlist'] = false;
|
|
$results['message'] = 'No record found';
|
|
}
|
|
$sql = "SELECT UniversityID as uid,UniversityName as uname FROM T_UniversityMaster";
|
|
$b=$this->db->query($sql);
|
|
$batch = $b->result();
|
|
if (count($batch) > 0) {
|
|
$results['univlist'] = true;
|
|
$results['univ'] = $batch;
|
|
} else {
|
|
$results['univlist'] = false;
|
|
$results['message'] = 'No record found';
|
|
}
|
|
return $results;
|
|
|
|
}
|
|
public function mstatus($bat=null,$br=null,$u=null){
|
|
|
|
$sql = "select @s:=@s+1 sl,sms.sid as ssid,std.name as name,std.father as father,br.bname as bname,ifnull(b.batch,'-') as batch,std.phone as phone,sms.cid as scid,sfp.bdate as bdate,list.status as status,u.uname as uname,cm.course as course,sms.sem as sem,sms.cdate as cdate,sfs.batch as fbatch,sfs.cid as fcid,cfd.syear as syear,sum(sfs.cf) as crsfee,sum(sfs.stf) as stf,sum(sfs.others) as others,(sum(sfs.cf) + sum(sfs.stf) + sum(sfs.others)) as total,ifnull(sfp.bamount,0) as bamount,(sum(sfs.cf) + (sum(sfs.stf) + sum(sfs.others)) - ifnull(sfp.bamount,0)) as balance
|
|
from
|
|
(SELECT StudentID as sid,CourseID as cid,BranchCode as branch,CDate as cdate,ListCode as lcode,Comments as cmts,Sem as sem FROM T_CertificationStatus) as sms
|
|
left join
|
|
(SELECT FeesID as fid,BatchCode as batch,StudentID as sid,CourseID as cid,FeesType as ftype,sum(CourseFees) as cf,Sum(STFOrWR) as stf,sum(Others) as others FROM T_Students_Fees_Status
|
|
group by sid,batch) as sfs on sfs.sid=sms.sid
|
|
left join
|
|
(SELECT StudentID as sid,FeesId as fid,BillDate as bdate,sum(BillAmount) as bamount
|
|
FROM T_Students_Fees_PaidDetails, (SELECT @s:= 0) AS s
|
|
group by sid,fid,bdate) as sfp on sfp.sid=sfs.sid and sfp.fid=sfs.fid
|
|
left join
|
|
(SELECT ID as cid,CourseID as ccid,FeesType as ftype,Sem_Year as syear FROM T_Course_Fees_Details) as cfd
|
|
on cfd.cid=sms.cid
|
|
left join
|
|
(SELECT CourseID as cid,UniversityID as uid,CourseName as course FROM T_CourseMaster) as cm
|
|
on cm.cid=sfs.cid
|
|
left join
|
|
(SELECT UniversityID as uid,UniversityName as uname FROM T_UniversityMaster) as u
|
|
on u.uid=cm.uid
|
|
left join
|
|
(SELECT ListCode as lcode,ListName as status FROM T_PickListDetails) as list
|
|
on list.lcode=sms.lcode
|
|
left join
|
|
(SELECT StudentID as sid,MobileNumber as phone,FirstName as name,Fathername as father FROM T_StudentDetails) as std on std.sid=sms.sid
|
|
left join
|
|
(SELECT BatchCode as bcode,BatchName as batch FROM T_BatchMaster) as b on b.bcode=sfs.batch
|
|
left join
|
|
(SELECT BranchCode as brcode,BranchName as bname FROM T_BranchMaster) as br on br.brcode=sms.branch
|
|
where std.name != 'null'
|
|
";
|
|
if ($br!= ''){
|
|
|
|
$sql.=" and sms.branch = '".$br."'";
|
|
|
|
}
|
|
if ($bat!= ''){
|
|
|
|
$sql.=" and b.bcode = '".$bat."'";
|
|
|
|
}
|
|
if ($u!= ''){
|
|
|
|
$sql.=" and u.uid = '".$u."'";
|
|
|
|
}
|
|
$sql.=" group by ssid,sem,fbatch";
|
|
$sql.=" order by sl";
|
|
$leadDet=$this->db->query($sql);
|
|
$mstatusDetails = $leadDet->result();
|
|
if (count($mstatusDetails) > 0) {
|
|
$results['mstatusList'] = true;
|
|
$results['mstatusr'] = $mstatusDetails;
|
|
} else {
|
|
$results['mstatusList'] = false;
|
|
$results['mmessage'] = 'No record found';
|
|
}
|
|
|
|
return $results;
|
|
|
|
}
|
|
public function certificate_status($bat=null,$br=null,$u=null){
|
|
|
|
$sql = "select @s:=@s+1 sl,sms.sid as ssid,std.name as name,std.father as father,br.bname as bname,ifnull(b.batch,'-') as batch,std.phone as phone,sms.cid as scid,sfp.bdate as bdate,list.status as status,u.uname as uname,cm.course as course,sms.cert_name as cert_name,sms.sem as sem,sms.cdate as cdate,sfs.batch as fbatch,sfs.cid as fcid,cfd.syear as syear,sum(sfs.cf) as crsfee,sum(sfs.stf) as stf,sum(sfs.others) as others,(sum(sfs.cf) + sum(sfs.stf) + sum(sfs.others)) as total,ifnull(sfp.bamount,0) as bamount,(sum(sfs.cf) + (sum(sfs.stf) + sum(sfs.others)) - ifnull(sfp.bamount,0)) as balance
|
|
from
|
|
(SELECT StudentID as sid,CourseID as cid,CertificationType as cert_name,BranchCode as branch,CDate as cdate,ListCode as lcode,Comments as cmts,Sem as sem FROM T_CertificationStatus) as sms
|
|
left join
|
|
(SELECT FeesID as fid,BatchCode as batch,StudentID as sid,CourseID as cid,FeesType as ftype,sum(CourseFees) as cf,Sum(STFOrWR) as stf,sum(Others) as others FROM T_Students_Fees_Status
|
|
group by sid,batch) as sfs on sfs.sid=sms.sid
|
|
left join
|
|
(SELECT StudentID as sid,FeesId as fid,BillDate as bdate,sum(BillAmount) as bamount
|
|
FROM T_Students_Fees_PaidDetails, (SELECT @s:= 0) AS s
|
|
group by sid,fid,bdate) as sfp on sfp.sid=sfs.sid and sfp.fid=sfs.fid
|
|
left join
|
|
(SELECT ID as cid,CourseID as ccid,FeesType as ftype,Sem_Year as syear FROM T_Course_Fees_Details) as cfd
|
|
on cfd.cid=sms.cid
|
|
left join
|
|
(SELECT CourseID as cid,UniversityID as uid,CourseName as course FROM T_CourseMaster) as cm
|
|
on cm.cid=sfs.cid
|
|
left join
|
|
(SELECT UniversityID as uid,UniversityName as uname FROM T_UniversityMaster) as u
|
|
on u.uid=cm.uid
|
|
left join
|
|
(SELECT ListCode as lcode,ListName as status FROM T_PickListDetails) as list
|
|
on list.lcode=sms.lcode
|
|
left join
|
|
(SELECT StudentID as sid,MobileNumber as phone,FirstName as name,Fathername as father FROM T_StudentDetails) as std on std.sid=sms.sid
|
|
left join
|
|
(SELECT BatchCode as bcode,BatchName as batch FROM T_BatchMaster) as b on b.bcode=sfs.batch
|
|
left join
|
|
(SELECT BranchCode as brcode,BranchName as bname FROM T_BranchMaster) as br on br.brcode=sms.branch
|
|
where std.name != 'null'
|
|
";
|
|
if ($br!= ''){
|
|
|
|
$sql.=" and sms.branch = '".$br."'";
|
|
|
|
}
|
|
if ($bat!= ''){
|
|
|
|
$sql.=" and b.bcode = '".$bat."'";
|
|
|
|
}
|
|
if ($u!= ''){
|
|
|
|
$sql.=" and u.uid = '".$u."'";
|
|
|
|
}
|
|
$sql.=" group by ssid,sem,fbatch";
|
|
$sql.=" order by sl";
|
|
$leadDet=$this->db->query($sql);
|
|
$cert_statusDetails = $leadDet->result();
|
|
if (count($cert_statusDetails) > 0) {
|
|
$results['cert_statusList'] = true;
|
|
$results['cert_status'] = $cert_statusDetails;
|
|
} else {
|
|
$results['cert_statusList'] = false;
|
|
$results['cert_message'] = 'No record found';
|
|
}
|
|
|
|
return $results;
|
|
|
|
}
|
|
public function mark_cert_status($bat=null,$br=null,$u=null,$from=null,$to=null){
|
|
|
|
$sql = "select @s:=@s+1 sl,sms.sid as ssid,std.name as name,std.father as father,br.bname as bname,ifnull(b.batch,'-') as batch,std.phone as phone,sms.cmts as cmts,sms.cid as scid,sfp.bdate as bdate,list.status as status,u.uname as uname,cm.course as course,sms.cert_name as cert_name,sms.sem as sem,sms.cdate as cdate,sfs.batch as fbatch,sfs.cid as fcid,cfd.syear as syear,sum(sfs.cf) as crsfee,sum(sfs.stf) as stf,sum(sfs.others) as others,(sum(sfs.cf) + sum(sfs.stf) + sum(sfs.others)) as total,ifnull(sfp.bamount,0) as bamount,(sum(sfs.cf) + (sum(sfs.stf) + sum(sfs.others)) - ifnull(sfp.bamount,0)) as balance
|
|
from
|
|
(SELECT StudentID as sid,CourseID as cid,CertificationType as cert_name,BranchCode as branch,CDate as cdate,ListCode as lcode,Comments as cmts,Sem as sem FROM T_CertificationStatus) as sms
|
|
left join
|
|
(SELECT FeesID as fid,BatchCode as batch,StudentID as sid,CourseID as cid,FeesType as ftype,sum(CourseFees) as cf,Sum(STFOrWR) as stf,sum(Others) as others FROM T_Students_Fees_Status
|
|
group by sid,batch) as sfs on sfs.sid=sms.sid
|
|
left join
|
|
(SELECT StudentID as sid,FeesId as fid,BillDate as bdate,sum(BillAmount) as bamount
|
|
FROM T_Students_Fees_PaidDetails, (SELECT @s:= 0) AS s
|
|
group by sid,fid,bdate) as sfp on sfp.sid=sfs.sid and sfp.fid=sfs.fid
|
|
left join
|
|
(SELECT ID as cid,CourseID as ccid,FeesType as ftype,Sem_Year as syear FROM T_Course_Fees_Details) as cfd
|
|
on cfd.cid=sms.cid
|
|
left join
|
|
(SELECT CourseID as cid,UniversityID as uid,CourseName as course FROM T_CourseMaster) as cm
|
|
on cm.cid=sfs.cid
|
|
left join
|
|
(SELECT UniversityID as uid,UniversityName as uname FROM T_UniversityMaster) as u
|
|
on u.uid=cm.uid
|
|
left join
|
|
(SELECT ListCode as lcode,ListName as status FROM T_PickListDetails) as list
|
|
on list.lcode=sms.lcode
|
|
left join
|
|
(SELECT StudentID as sid,MobileNumber as phone,FirstName as name,Fathername as father FROM T_StudentDetails) as std on std.sid=sms.sid
|
|
left join
|
|
(SELECT BatchCode as bcode,BatchName as batch FROM T_BatchMaster) as b on b.bcode=sfs.batch
|
|
left join
|
|
(SELECT BranchCode as brcode,BranchName as bname FROM T_BranchMaster) as br on br.brcode=sms.branch
|
|
where std.name != 'null'
|
|
";
|
|
if ($br!= ''){
|
|
|
|
$sql.=" and sms.branch = '".$br."'";
|
|
|
|
}
|
|
if ($bat!= ''){
|
|
|
|
$sql.=" and b.bcode = '".$bat."'";
|
|
|
|
}
|
|
if ($u!= ''){
|
|
|
|
$sql.=" and u.uid = '".$u."'";
|
|
|
|
}
|
|
if ($from and $to != ''){
|
|
$fromd= date("Y-m-d",strtotime($from));
|
|
$tod=date("Y-m-d",strtotime($to));
|
|
|
|
$sql.="and sms.cdate >= '".$fromd."'
|
|
and sms.cdate <= '".$tod."'";
|
|
|
|
}
|
|
$sql.=" group by ssid,sem,fbatch";
|
|
$sql.=" order by sl";
|
|
|
|
$leadDet=$this->db->query($sql);
|
|
$mcert_statusDetails = $leadDet->result();
|
|
if (count($mcert_statusDetails) > 0) {
|
|
$results['markcert_statusList'] = true;
|
|
$results['markcert_status'] = $mcert_statusDetails;
|
|
} else {
|
|
$results['markcert_statusList'] = false;
|
|
$results['markcert_message'] = 'No record found';
|
|
}
|
|
|
|
return $results;
|
|
|
|
}
|
|
public function expense($from=null,$to=null){
|
|
|
|
$sql = "SELECT exp.TypeName as exp_name,sum(dbm.Amount) as amount FROM T_Income_Outcome_Master exp
|
|
join T_DayBookMaster dbm on dbm.Type=exp.ID
|
|
where dbm.Name = 'I001'
|
|
";
|
|
|
|
if ($from and $to != ''){
|
|
$fromd= date("d-m-Y",strtotime($from));
|
|
$tod=date("d-m-Y",strtotime($to));
|
|
|
|
$sql.="and dbm.Date >= '".$fromd."'
|
|
and dbm.Date <= '".$tod."'";
|
|
|
|
}
|
|
$sql.=" group by exp_name";
|
|
|
|
|
|
$leadDet=$this->db->query($sql);
|
|
$expense = $leadDet->result();
|
|
if (count($expense) > 0) {
|
|
$results['expense_list'] = true;
|
|
$results['expense'] = $expense;
|
|
} else {
|
|
$results['expense_list'] = false;
|
|
$results['expense_message'] = 'No record found';
|
|
}
|
|
|
|
return $results;
|
|
|
|
}
|
|
|
|
|
|
} |