<?php
include("../db.php");
if($_REQUEST["type"]=="addexpance_data"){
	$where="";
	//if(isset($_REQUEST["class"]))$where=" AND class='".$_REQUEST["class"]."'";
	$sql_limit="SELECT * FROM ".TRANSECTION." where  type='withdraw' and school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."'";
	$aryList=$db->getRows($sql_limit);
	$aryPgAct=array("src"=>"Account", "page_id"=>"addexpance","action"=>"add");
	if(is_array($aryList) && count($aryList)>0)
	{
		$aryData='{ "aaData": [
		';
	    $i=0;
		foreach($aryList as $iList)
		{
		    $payment_mode = "";
		    if($iList['payment_mode'] == "Cheque/DD"){
		        $payment_mode = "$iList[payment_mode] - $iList[cheque_no]"; 
		    } else {
		        $payment_mode = "$iList[payment_mode]";
		    }
			$aryPgAct["id"]=$iList['id'];
			$i++;
			$aryData.='["'.($iList['tfor']).'",';
			$aryData.='"'.($iList['amount']).'",';
			$aryData.='"'.($iList['type']).'",';
			$aryData.='"'.($payment_mode).'",';
			$aryData.='"'.($iList['udate']).'",';
			$aryData.='"';
			$aryPgAct['action']="edit";
			$aryData.='<a href=\''.URL_ADMIN_HOME.getQueryString($aryPgAct).'\'><button type=\'button\' class=\'btn btn-info\'>Edit</button></a>';
			$aryPgAct['action']="delete";
			$aryData.='<a href=\'javascript:;\' onclick=\'del(\"'.URL_ADMIN_HOME.getQueryString($aryPgAct).'\")\' class=\'confirmation\'><button type=\'button\' class=\'btn btn-danger\'>Delete</button></a>';						$aryData.='<a href=\'javascript:;\' onclick=\'checkRecept(\"'.$aryPgAct[id].'\")\'  class=\'btn btn-danger\'>Recepit</a>';
			$aryData.='"]';
			if($i!=count($aryList))$aryData.=",";
		 }
		 $aryData.="] }";	
		 echo $aryData;	
	}else{
		echo $db->getLastQuery().$db->getErMsg();
	}
			
}


else if($_REQUEST["type"]=="modeincome_data"){
	$where="";
	//if(isset($_REQUEST["class"]))$where=" AND class='".$_REQUEST["class"]."'";
	$sql_limit="SELECT * FROM ".TRANSECTION." where  type='mode_income' and school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."'";
	$aryList=$db->getRows($sql_limit);
	$aryPgAct=array("src"=>"Account", "page_id"=>"mode","action"=>"add");
	if(is_array($aryList) && count($aryList)>0)
	{
		$aryData='{ "aaData": [
		';
	    $i=0;
		foreach($aryList as $iList)
		{
			$aryPgAct["id"]=$iList['id'];
			$i++;
			$aryData.='["'.($iList['tfor']).'",';
			$aryData.='"'.($iList['amount']).'",';
			$aryData.='"'.($iList['type']).'",';
			$aryData.='"'.($iList['udate']).'",';
			$aryData.='"';
			$aryPgAct['action']="edit";
			$aryData.='<a href=\''.URL_ADMIN_HOME.getQueryString($aryPgAct).'\'><button type=\'button\' class=\'btn btn-info\'>Edit</button></a>';
			$aryPgAct['action']="delete";
			$aryData.='<a href=\'javascript:;\' onclick=\'del(\"'.URL_ADMIN_HOME.getQueryString($aryPgAct).'\")\' class=\'confirmation\'><button type=\'button\' class=\'btn btn-danger\'>Delete</button></a>';						$aryData.='<a href=\'javascript:;\' onclick=\'checkRecept(\"'.$aryPgAct[id].'\")\'  class=\'btn btn-danger\'>Recepit</a>';
			$aryData.='"]';
			if($i!=count($aryList))$aryData.=",";
		 }
		 $aryData.="] }";	
		 echo $aryData;	
	}else{
		echo $db->getLastQuery().$db->getErMsg();
	}
			
}

elseif($_REQUEST["type"]=="total_amount_dues"){

    $monthsList1 = array(
        1=>'Apr',
        2=>'May',
        3=>'Jun',
        4=>'Jul ',
        5=>'Aug',
        6=>'Sep',
        7=>'Oct',
        8=>'Nov',
        9=>'Dec',
    	10=>'Jan',
        11=>'Feb',
        12=>'Mar'
    );

		$overAllTotal=0;$overAlltution=0;$overAllTransport=0;$overAllhotel=0;$overAllOther=0;$overAllOther_hostel=0;$overAllFine=0;$overAllextra=array();					
		$overAllNewextra=0; $totalstrength = 0; $totalamount = 0;

    $_GET['class']      =   $_REQUEST['cls'];
    $_GET['section']    =   $_REQUEST['section'];
    $_GET['fmonth']     =   $_REQUEST['fmonth'];
    	if($_GET['class']!=""){
    	    $classAry = $db->getRows("select class from class where class='$_GET[class]' ");
    	}   else {
    	    $classAry = $db->getRows("select class from class");
    	}

		$aryFeeType=$db->getRows("select distinct(type) from extrafee_type where school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1 order by type");
		
		$m=0;
		if(isset($_GET['fmonth']) && $_GET['fmonth']!=0){
			$month=$_GET["fmonth"];
			  if($month<4){
				$m=($month+9);
			  }
			  else{
				$m=$month-3;
			  }
		}
		else{
			$m=12;
		}

        ?>

        <div class="well no-padding divclass" >
        <?php foreach($classAry as $class){
                $secAry = array();
                $cls = $class['class'];
            if($_GET['section']!=""){
                $secAry[] = $_GET['section'];
            } else {
                $sections = $db->getRows("select name from section ");
                foreach($sections as $sec){
                    $totalstu = $db->getVal("select count(idno) from student where class='$cls' and section='$sec[name]' and session='".$_SESSION["admin"]["session"]."' and status='1' ");
                    if($totalstu > 0){
                        $secAry[] = $sec["name"];
                    }
                } 
            }

            $wheresection = '';        $sections = '';
                
            foreach($secAry as $sec){
                $secstu = $db->getRows("select * from student where class='$cls' and section='$sec' and session='".$_SESSION["admin"]["session"]."' and status='1' ");

                if(count($secstu) > 0 ){
                ?>
            <div class="row-fluid mydiv">
                <table class='table' id="tblData" border="1" cellspacing="0">
    				<thead>
        				<tr>
                        	<!--<th class=""><input  onchange="checkAll()" class="check" type="checkbox" name="check[]" value="" id="checkboxa" /></th>-->
                        	<th>S.No</th>
        					<th>Student Name</th>
                            <th>Father Name</th>
                            <th>Reg. no</th>
        					<th>Class</th>
                            <th>Roll No.</th>
        					<th>Contact No</th>
                       <th>Total</th>
        			</tr>
        			</thead>
        			<tbody>
    	                <?php //$allstudents = $db->getRows("select * from student where class='$cls' and section='$sec' and session='".$_SESSION["admin"]["session"]."' and status=1 ");
        	                $aryStudentList=$db->getRows("select * from student where class='$cls' and section='$sec' and school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1 and issue_TC=0 order by class,section");

                            $i=0;$total_comp=0;
                			for($j=1; $j<=$m; $j++){
            						$md[]=$j;
            					}
        
        					 $mdf= implode(',',$md);
        
                            $monthlycolumn = [];
                            $otherfeecolumn = [];
        					$backduescolumn = [];
        					$hostelothercolumn = [];
        					$totalothercolumn = [];
        					$finecolumn = [];

                        foreach($aryStudentList as $fStudent){
					$mn=$m;

						$tution=0;$trasnport=0;$hostel=0;$otherExtra=0;$total=0;$fine=0;$total_full=0;
						$j=0;$other=0;$k=0;$total_extra=0;$count=0;
							$m1=$m+1;			
							foreach($monthsList1 as $key=>$val){$count++;

							if($mn===0){
							
							}
							else{ $mn--;

						$installTution=$db->getRow("select i".$key.",ip".$key." from installment where idno='".$fStudent["idno"]."'");
						if(is_array($installTution) && count($installTution)>0){
						    $tution = $installTution["i$key"]-$installTution["ip$key"];
						}else{
							$tution="";
						}
						$overAlltution=$tution;

						$installTution=$db->getRow("select b".$key.",bp".$key." from installment where idno='".$fStudent["idno"]."'");
						if(is_array($installTution) && count($installTution)>0){
						    $trasnport = $installTution["b$key"]-$installTution["bp$key"];
						}else{
							$trasnport="";
						}
						$overAllTransport=$trasnport;

						$installTution=$db->getRow("select i".$key.",ip".$key." from hostelinstallment where idno='".$fStudent["idno"]."'");
						if(is_array($installTution) && count($installTution)>0){
						    $hostel = $installTution["i$key"] - $installTution["ip$key"];
						} else{
							$hostel="";
						}
						$overAllhotel=$hostel;

						$extrasum=array();
						$extrafeesingle = 0;
						if(is_array($aryFeeType) && count($aryFeeType)>0){
							foreach($aryFeeType as $ftype){  
								$sums=0;
								$Ftypeid=$db->getVal("select id from extrafee_type where type='".$ftype["type"]."' and class='".$fStudent["class"]."'");
						$installTution=$db->getRow("select fee,pay from extrafee where type='".$Ftypeid."' and pid= 'p".$key."' and idno='".$fStudent["idno"]."'");

								$no=array();
								if(is_array($installTution) && count($installTution)>0){
								    $sums = $installTution["fee"]-$installTution["pay"];
								}else{
									$sums="";
								}
								$extrasum[]=$sums;
								$total_extra+=$sums;
								$tp=$ftype["type"];
								$overAllextra[$tp]+=$sums;
								$extrafeesingle+=$sums;
							}
						}
                        $monthlycolumn[$key] +=$tution+$trasnport+$extrafeesingle; 
						
						 $overAllNewextra=0;
						for($ij=0; $ij<count($aryFeeType); $ij++){
						 $overAllNewextra+=$extrasum[$ij];
						}

						  $total=0;
						if($overAlltution=="0"&&$overAllTransport==0&&$overAllhotel==0&&$overAllNewextra==0)
						 {$total==0;}
						elseif($overAlltution==""&&$overAllTransport==""&&$overAllhotel==""&&$overAllNewextra=="")
						{$total="";}
						 else{ $total=$overAlltution+$overAllTransport+$overAllhotel+$overAllNewextra;}
			
			$total_full=$total_full+$total;
			 }}
						$other2=0; $c = 0;
                    $install=$db->getRows("select * from otherfee_structure where $whereclass school_id='".$_SESSION["admin"]["school_id"]."' and (otype = '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' GROUP BY name");
                    foreach($install as $ins){ $c++;
                        $over = "";
                            $exists = $db->getRow("select * from otherfee_structure where name='$ins[name]' and class='".$fStudent["class"]."' and  school_id='".$_SESSION["admin"]["school_id"]."' and (otype =  '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' ");
						    if($exists > 1){
    						    $fee = $exists['fee'];
    						    $paid = $db->getVal("select sum(amount) from reff_receipt where idno=".$fStudent['idno']." and type='$exists[name]' and session='".$_SESSION["admin"]["session"]."' ");

    						    $discount= $db->getVal("SELECT sum(discount) from otherfee_discount  WHERE  idno='$fStudent[idno]' and other_id='$exists[id]'   and session='".$_SESSION["admin"]["session"]."' "); 

    						    if($fStudent['stype']=="old" && $exists['otype']=="new"){
								    $over="";
    							}
    						   elseif($fStudent['stype']=="new" && $exists['otype']=="old"){
    								$over="";
    							}
    							else {
    							    $over = $exists['fee'] - $discount - $paid;
        							$other2+=$over;
    							}

						    }
						    else {
						        $over = "";
						    }
                        $otherfeecolumn[$c]+= $over;
						?>
						<!--<td> <?php //if($over=="0")echo "<lable style='color:red'>paid</label>";elseif($over=="") echo "<lable style='color:red'>X</label>"; else echo $over; ?></td>-->
						<?php
					}
					$total_full+=$other2;

					$install=$db->getRow("select * from otherfee_structure where otype ='".$fStudent['idno']."' and class='".$fStudent['class']."' and name like 'b%' and  school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."'");
					
									
					$discount= $db->getVal("SELECT sum(discount) from otherfee_discount  WHERE  idno='$fStudent[idno]' and class='".$fStudent['class']."' and other_id='$install[id]'   and session='".$_SESSION["admin"]["session"]."' ");
					
					$install2=$db->getRow("select id from otherfee_structure where otype ='".$fStudent['idno']."' and name like 'Latefine%' and  school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."'");
					
					$discount2= $db->getVal("SELECT COALESCE(sum(discount),0) from otherfee_discount  WHERE  idno='$fStudent[idno]' and other_id in(select id from otherfee_structure where otype ='".$fStudent['idno']."' and name like 'Latefine%' and  school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."')   and session='".$_SESSION["admin"]["session"]."' ");

							$dues=0;
							if(is_array($install) && count($install)>0){
								$installTution=$db->getRows("select * from installment_fine where idno=".$fStudent['idno']." and reasion = '".$install['name']."'"); 
                                								
								
								if(is_array($installTution) && count($installTution)>0){
									$total_dues=0;
									foreach($installTution as $ins){
										$total_dues+=$ins['fine'];
									}
									if($install['fee']==$total_dues)
										$dues=0;
									else $dues=$install['fee']-$total_dues-$discount;	
									   
									
									$installTution_remain=$db->getVal("select sum(fine) from installment_fine where idno=".$fStudent['idno']." and reasion like 'remain%' ");
											$dues=$dues-$installTution_remain;
								}
							  else 		
									$dues=$install['fee']-$discount;
							}
							else
								$dues="";
							$alldues+=$dues;
							
							$total_full+=$dues;
						$backduescolumn[0]+=$dues;	

						$install=$db->getRows("select * from otherfee_structure where class='".$fStudent['class']."' and otype ='".$fStudent['idno']."' and name like 'Latefine%' and month <= '".$m."' and school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."'");
							$dues=0;$total_dues=0;$fine_t=0;
							if(is_array($install) && count($install)>0){
								foreach($install as $ins){
									$fine_t+=$ins["fee"];
								$installTution=$db->getRows("select * from installment_fine where idno=".$fStudent['idno']." and reasion = '".$ins['name']."'");  
								      if(is_array($installTution) && count($installTution)>0){
											foreach($installTution as $ins){
												$total_dues+=$ins['fine'];
											}
									   }
									  else
										$dues=$ins['fee'];
								}
										if($fine_t==$total_dues)
												$dues=0;
								else $dues=$fine_t-$total_dues;
						    }
							else
								$dues="";
							$alldues+=$dues;
							
							$total_full+=$dues-$discount2;
						
                        $install2=$db->getRow("SELECT otype,GROUP_CONCAT(id) as id FROM `otherfee_structure` where otype='".$fStudent['idno']."'  and class='".$fStudent['class']."' and name like 'Latefine%'");

						$dis3= $db->getVal("SELECT sum(discount) from otherfee_discount  WHERE  idno='$fStudent[idno]' and class='".$fStudent['class']."' and other_id in($install2[id])   and session='".$_SESSION["admin"]["session"]."' ");

                        $to=$db->getVal("select sum(fee) from otherfee_structure where otype='".$fStudent['idno']."' and class='".$fStudent['class']."' and name!='Back_Dues' and name NOT like 'Latefine%' and month in ($mdf) and session='".$_SESSION["admin"]["session"]."'");
                        $to1=$db->getVal("SELECT sum(amount) FROM reff_receipt WHERE idno='".$fStudent['idno']."' and session='".$_SESSION["admin"]["session"]."' and type in (SELECT name FROM otherfee_structure WHERE otype='".$fStudent['idno']."' and class='".$fStudent['class']."' and name!='Back_Dues' and name NOT like 'Latefine%' and month in ($mdf))");
                        $dis1=$db->getVal("SELECT sum(discount) FROM otherfee_discount WHERE idno='".$fStudent['idno']."' and class='".$fStudent['class']."' and session='".$_SESSION["admin"]["session"]."' and other_id in (SELECT id FROM otherfee_structure WHERE otype='".$fStudent['idno']."' and class='".$fStudent['class']."' and name!='Back_Dues' and name NOT like 'Latefine%' and month in ($mdf))");

                        $t2=$to-$to1-$dis1;
                        $totalothercolumn[0]+=$t2;

    					if($dues=="0"){
                            $finetotal=$dues;
    					}
    					else
    					{
    					    $finetotal=$dues-$dis3;
    					}
    					$finecolumn[0]+=$finetotal;

    					$other2_hostel=0; $b = 0;
                        $install=$db->getRows("select * from hostel_otherfee_structure where $whereclass school_id='".$_SESSION["admin"]["school_id"]."' and (otype = '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' GROUP BY name");
                        foreach($install as $ins){ $b++;
                            $over_hostel = "";
                                $exists = $db->getRow("select * from hostel_otherfee_structure where name='$ins[name]' and class='".$fStudent["class"]."' and  school_id='".$_SESSION["admin"]["school_id"]."' and (otype =  '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' ");
    						    if($exists > 1){
        						    $fee = $exists['fee'];
        						    $paid = $db->getVal("select sum(amount) from reff_receipt where idno=".$fStudent['idno']." and type='$exists[name]' and session='".$_SESSION["admin"]["session"]."' ");
    
        						    $discount= $db->getVal("SELECT sum(discount) from hostelotherfee_discount  WHERE  idno='$fStudent[idno]' and other_id='$exists[id]'   and session='".$_SESSION["admin"]["session"]."' "); 
    
        						    if($fStudent['htype']=="old" && $exists['otype']=="new"){
    								    $over_hostel="";
        							}
        						   elseif($fStudent['htype']=="new" && $exists['otype']=="old"){
        								$over_hostel="";
        							}
        							 elseif($fStudent['htype']==""){
        								$over_hostel="";
        							}
        							else {
        							    $over_hostel = $exists['fee'] - $discount - $paid;
            							$other2_hostel+=$over_hostel;
        							}
    						    }
    						    else {
    						        $over_hostel = "";
    						    }
    					}
    					$total_full+=$other2_hostel;
					
					if($total_full+$t2 > 0){ $i++; $totalstrength++;
					?>
                    <tr>
                		<!--<td class=""><input class="checkboxes" type="checkbox" name="sid" value="<?php echo $fStudent["idno"];?>" id="cat<?php echo $fStudent["mobile"]; ?>" /></td>-->
                    	<td id="newid<?php echo $i ?>"><?php echo $i ?></td>
						<td class="name<?php echo $fStudent["idno"];?>"><?php echo $fStudent["name"] ?></td>
                        <td><?php echo $fStudent["fname"] ?></td>
                        <td><?php echo $fStudent["admissionno"] ?></td>
						<td><?php echo $fStudent["class"]." - ".$fStudent["section"]  ?></td>
                        <td><?php echo $fStudent["fincome"];?></td>
						<td><?php echo $fStudent["mobile"]; ?></td>
                    <td class="MyTotalLeft left<?php echo $fStudent['idno'] ?>"><?php echo $total_full+$t2; $totalamount+=$total_full+$t2; ?></td>
            </tr>
			<?php } $total_comp+=$total_full+$t2; 
			} ?>

        			</tbody>
                </table>
            </div>
            <style>
                @media print {
                .mydiv {page-break-after: always;}
                }
            </style>
                <?php
                
                    
                }
            }

        } ?>

            <label style="float:left;">Total Students: <?php echo $totalstrength; ?></label>
            <label style="float:right;">Total Dues: <?php echo $totalamount; ?></label>
        </div>

        <?php
	
}

?>