<?php
$pgMod="vehicle_wise_detail";
$pgAct="view";
$monthsList = 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'
); 
?>
<style>.top-bar-minimize{display:none;}
thead, tfoot {display: table-header-group;}
.top-bar+.well,.accordion .accordion-heading+.accordion-body{display: inline-block !important;width: 100% !important;overflow: inherit !important;} 
.dynamic{display:none}
table td {
padding: 6px !important;
}
.data{border-right: 3px solid #04606C;}
legend{line-height: 16px;font-weight:100;}
</style>
<form method="get" action="">
    <input type="hidden" name="src" value="Transport" />
    <input type="hidden" name="page_id" value="<?php echo $pgMod; ?>" />
	<div class="row-fluid">
        <div class="span12">
            <div class="well no-padding">
                <div class="controls controls-row">
                    <div class="control-group span3">
                        
                        <div class="controls span3">
                                <select name="fmonth" id="fmonth">
                                    <option value="">Month</option>
                                 <?php
                                    for($m = 1;$m <= 12; $m++){ 
                                        $newDate=date("Y")."-".$m."-1";
                                        $month =  date("F",strtotime($newDate));//."(".($ij+3).")";
                                        //$month =  date("F", mktime(0, 0, 0, $m)); 
                                        $selected="";if($_GET["fmonth"]==$m)$selected=" selected ";
                                        echo "<option $selected value='$m'>$month</option>";
                                    }
                                 ?> 
                                </select>
                            </div>
                    </div>

         <!--           <div class="control-group span3">-->
         <!--               <div class="controls span3">-->
         <!--                       <select name="class" id="class">-->
									<!--<option value="">Class</option>-->
                                    <?php /*
										$class=$db->getRows("select * from class where  school_id='".$_SESSION["admin"]["school_id"]."'");
										foreach($class as $cla){
											?>
											<option <?php if($cla["class"]==$_GET["class"])echo 'selected';?> value="<?php echo $cla["class"] ?>">
												<?php echo $cla["class"] ?>
                                            </option>
											<?php
										}
									*/ ?>
								<!--</select>-->
        <!--                    </div>-->
        <!--            </div>-->

                    <div class="control-group span3">
                    
                          <div class="controls span3">
                                <select name="old_school" id="old_school">
                                	<option value="">Select BUS</option>
									<?php 
										$location=$db->getRows("select * from bus_detail where school_id='".$_SESSION["admin"]["school_id"]."'");
										if(is_array($location) && count($location)>0){
											foreach($location as $loc){
												?>
												<option <?php if($loc["id"]==$_GET["old_school"])echo 'selected';?> value="<?php echo $loc["id"] ?>">
													<?php echo $loc["name"] ?>
												</option>
												<?php
											}
										}
									?>
								</select>
                            </div>
                    </div>

                    <div class="control-group span3">
                    <button type="submit" name="vehicle_report" class="btn-classic margin">Get Report</button>
                    <!--<button type="submit" name="bus_detail" class="btn-classic margin">Bus Report</button>-->
                    <!--<button type="submit" name="teacher_detail" class="btn-classic margin">Teacher Report</button>                    -->
                    <!--<button type="submit" name="daily_att" class="btn-classic margin">Bus Attendance</button>-->
                   
                    </div>

                    <div class="control-group span2">
                      <button type="button" onclick="printDiv('printDiv')" class="btn btn-danger">Print</button>
                      </div>

                </div>
            </div>
        </div>
    </div>
</form>
<div id="printDiv">
<?php
if(isset($_REQUEST['vehicle_report'])){
if(isset($_GET["old_school"]) && trim($_GET["old_school"])!=""){
	
	$where="";
	if(isset($_GET["class"]) && $_GET["class"]!="") $where="and class='".$_GET["class"]."'";
	
	$overAllTotal=0;$overAlltution=0;$overAllTransport=0;$overAllhotel=0;$overAllOther=0;$overAllFine=0;$overAllextra=array();$overAllNewextra=0;
	
	$aryStudentList=$db->getRows("select * from student where old_school='".$_GET["old_school"]."' and  school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1 and issue_TC=0 $where order by class,name");
	//echo $db->getLastQuery();exit;
	
	$month=$_GET["fmonth"];$m=0;
	
	  if($month<4){
		$m=($month+9);
	  }
	  else{
		$m=$month-3;
	  }
	  //echo $m;
	if(is_array($aryStudentList) && count($aryStudentList)>0){
		?>
		<h5><p style="float:right">Vechicle No : <?php echo $db->getVal("select name from bus_detail where id='".$_GET["old_school"]."' and school_id='".$_SESSION["admin"]["school_id"]."'");?>(
	<?php	echo ($_SESSION['admin']['session']);?>)
		</p>	<p>Transport dues list Vechicle no Wise :</p></h5>
    
        <?php $aryList=$db->getRow("select * from bus_detail where id='".$_GET["old_school"]."' and school_id='".$_SESSION["admin"]["school_id"]."'")?>
        <button class="btn btn-danger" onclick="exportTableToExcel('tblData', 'Get Report')" style="float:right;margin-top:10px;">Excel</button>
        <table class="table" style="border: 2px solid red" cellpadding="0" cellspacing="0">
        <tr>
        <td>Driver Name</td><td class="data"><?php echo $db->getVal("select name from staff where idno='".$aryList['driver_name']."' "); ?></td>
               
        <td>Driver No</td><td class="data"><?php echo $aryList['driver_no']; ?> </td>
         
          <td>Conductor Name</td><td class="data"><?php echo $aryList['conductor_name']; ?> </td>
           <td>Conductor No</td><td class="data"><?php echo $aryList['conductor_no']; ?> </td>
        </tr>
        <tr>
        	<td>Route</td><td class="data"><?php echo $db->getVal("select name from location where id='".$aryList['route']."' "); ?> </td>
           <td>Vehicle No</td><td class="data"><?php echo $aryList['vehicle_no']; ?> </td>
           <td>Vehicle Type</td><td class="data"><?php echo $aryList['vehicle_type']; ?> </td>
           <td>Incharge name</td><td class="data"><?php echo $aryList['incharge_name']; ?> </td>

        </tr>
         <tr>
           <td>Mileage</td><td class="data"><?php echo $aryList['mileage']; ?> </td>
           <td>K.M./day Type</td><td class="data"><?php echo $aryList['km_per_day']; ?> </td>
           <td>Vehicle Capacity</td><td class="data"><?php echo $aryList['no_of_stduent']; ?> </td>
           <td>Incharge No.</td><td class="data"><?php echo $aryList['incharge_no']; ?> </td>
        </tr>
        </table>
		<table cellpadding="0" cellspacing="0" class="table" border="1" id="tblData">
			<thead>
			<tr>
            	<th>S.No</td>
				<th>Student Name</td>
				<th>Father Name</th>
				<th>Reg.No</th>
				<th>Class</th>
				<th>Roll No.</th>
				<th>Contact no</th>
				<th>PickUp Point</th>
			   <?php
			   if(is_array($monthsList) && count($monthsList)>0){
				for($i=1;$i<=$m;$i++){?>
						<th width="0.5%"><?php echo $monthsList[$i]; ?></th><?php
					}
			   }
			   ?>
			   <th>Total</th>
			</tr>
			</thead>
			<tbody>
				<?php $i=0;$j=0; $columntotal = [];
				foreach($aryStudentList as $fStudent){$i++;$total=0;$j++;
					?>
					<tr >
                    	<td><?php echo $j; ?></td>
						<td><?php echo $fStudent["name"] ?></td>
						<td><?php echo $fStudent["fname"] ?></td>
                        <td><?php echo $fStudent["admissionno"] ?></td>
                        <td><?php echo $fStudent["class"] ?></td>
                        <td><?php echo $fStudent["fincome"] ?></td>
                        <td><?php echo $fStudent["mobile"] ?></td>
                        <td>
                            <?php echo $db->getVal("select root from bus where id='$fStudent[bus]' "); ?>
                            
                        </td>
						<?php
						   if(is_array($monthsList) && count($monthsList)>0){
                            $rowtotal = 0;
							for($i = 1; $i <= $m; $i++){
							    
							    $installTution=$db->getRow("select b".$i.",bp".$i." from installment where idno='".$fStudent["idno"]."'");

            					if(is_array($installTution) && count($installTution)>0){
            						if($installTution["bp$i"]=="" || $installTution["bp$i"]==0){
            							$trasnport=$installTution["b$i"];
            						}
            						else    {
                    				if($m==$i)
                    				{
                        				$trasnport=$db->getVal("select (b".$m."-COALESCE(bp".$m.",0)) from installment where  idno='".$fStudent["idno"]."' ");
                    				}
                                    else    {
                            	             $trasnport=0;	
                                            }
            							}
            					}   else    {
            						$trasnport="";
            					}
            					
            					$rowtotal+=$trasnport;
                                $columntotal[$i] += $trasnport;
								?>
								<td>
    								<?php
    								if($trasnport=="0")echo "<lable style='color:red'>paid</label>";elseif($trasnport=="") echo "<lable style='color:red'>X</label>";else echo $trasnport;
    								 ?>
								 </td>
								 <?php
							}
						   }
						 ?>
						 <td class="MyTotalLeft"><?php echo $rowtotal; $total_comp+=$rowtotal;?></td>
					</tr>
					<?php
				}
				?>
			</tbody>
			<tfoot>
				<tr>
					<td colspan="<?php echo 7;?>">Gross Total</td>
					<?php for($i=1; $i<=$m; $i++){
                			    echo "<td>$columntotal[$i]</td>";
                			} ?>
				   <td><?php echo $total_comp; ?></td>
				</tr>
				<!--<tr>-->
				<!--   <td colspan="<?php echo $m+7;?>">Driver Salary</td>-->
					
				<!--   <td><input style="width:50px;" value="" type="textbox" name="driver"/> </td>-->
				<!--</tr>-->
			</tfoot>
		</table>

        Print date : <?php echo date("d-M-Y") ?>
		<?php
	}else{
		//echo $db->getErMsg().$db->getLastQuery();
	}
 }  }
 elseif(isset($_REQUEST['daily_att'])){
if(isset($_GET["old_school"]) && trim($_GET["old_school"])!=""){
	$overAllTotal=0;$overAlltution=0;$overAllTransport=0;$overAllhotel=0;$overAllOther=0;$overAllFine=0;$overAllextra=array();$overAllNewextra=0;
	$aryStudentList=$db->getRows("select * from student where old_school='".$_GET["old_school"]."' and  school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1 and issue_TC=0 order by class,name");
	
	$month=$_GET["fmonth"];$m=0;
	  if($month<4){
		$m=($month+9);
	  }
	  else{
		$m=$month-3;
	  }
	  //echo $m;
	if(is_array($aryStudentList) && count($aryStudentList)>0){
		?>
		<h5><p style="float:right">Vechicle No : <?php echo $db->getVal("select name from bus_detail where id='".$_GET["old_school"]."' and school_id='".$_SESSION["admin"]["school_id"]."'");echo $db->getErMsg(); ?></p>	<p>Transport Attendance list Vechicle no Wise :</p></h5>
    <?php $aryList=$db->getRow("select * from bus_detail where id='".$_GET["old_school"]."' and school_id='".$_SESSION["admin"]["school_id"]."'")?>
    <button class="btn btn-danger" onclick="exportTableToExcel('tblData', 'Bus Attendance')" style="float:right;margin:10px;">Excel</button>
        <table class="table" style="border: 2px solid red" cellpadding="0" cellspacing="0">
        <tr>
        <td>Driver Name</td><td class="data"><?php echo $db->getVal("select name from staff where idno='".$aryList['driver_name']."' "); ?></td>
               
        <td>Driver No</td><td class="data"><?php echo $aryList['driver_no']; ?> </td>
         
          <td>Conductor Name</td><td class="data"><?php echo $aryList['conductor_name']; ?> </td>
           <td>Conductor No</td><td class="data"><?php echo $aryList['conductor_no']; ?> </td>
        </tr> 
        <tr>
        	<td>Route</td><td class="data"><?php echo $db->getVal("select name from location where id='".$aryList['route']."' "); ?> </td>
           <td>Vehicle No</td><td class="data"><?php echo $aryList['vehicle_no']; ?> </td>
           <td>Vehicle Type</td><td class="data"><?php echo $aryList['vehicle_type']; ?> </td>
           <td>Incharge name</td><td class="data"><?php echo $aryList['incharge_name']; ?> </td>

        </tr>
         <tr>
           <td>Mileage</td><td class="data"><?php echo $aryList['mileage']; ?> </td>
           <td>K.M./day Type</td><td class="data"><?php echo $aryList['km_per_day']; ?> </td>
           <td>No of student</td><td class="data"><?php echo $aryList['no_of_stduent']; ?> </td>
           <td>Incharge No.</td><td class="data"><?php echo $aryList['incharge_no']; ?> </td>
        </tr>
        </table>
               	<table cellpadding="0" cellspacing="0" class="table" border="1" id="tblData">
			<thead>
			<tr>
            	<th width="1%">S.No</td>
				<th width="50%">Student Name</td>
				<th  width="50%">Father Name</th>
				<th width="2%">Contact no</th>
                <th width="2%">Short Add.</th>
				<th width="2%">Class</th>
			   <?php  
			  	for($i=1;$i<=31;$i++){
					?><th><?php echo $i ?></th><?php
				}
			   ?>
			</tr>
			</thead>
			<tbody>
				<?php $ij=0;
				foreach($aryStudentList as $fStudent){$ij++;
					?>
					<tr>
                    	<td><?php echo $ij; ?></td>
						<td><?php echo $fStudent["name"] ?></td>
						<td><?php echo $fStudent["fname"] ?></td>
						<td><?php echo $fStudent["mobile"] ?></td>
                        <td><?php echo $fStudent["mincome"] ?></td>
						<td><?php echo $fStudent["class"] ?></td>
						<?php
						   for($i=1;$i<=31;$i++){
					?><th><?php echo " "; ?></th><?php
				}
						 ?>
					</tr>
					<?php
				}
				?>
			</tbody>
			
		</table>

        Print date : <?php echo date("d-M-Y") ?>
		<?php
	}else{
		//echo $db->getErMsg().$db->getLastQuery();
	}
 }  }
 if(isset($_REQUEST['teacher_detail'])){
if(isset($_GET["old_school"]) && trim($_GET["old_school"])!=""){
	
	
	$aryStudentList=$db->getRows("select * from staff where medium='".$_GET["old_school"]."' and  school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1 order by name");
	//echo $db->getLastQuery();exit;

	if(is_array($aryStudentList) && count($aryStudentList)>0){
		?>
		<h5><p style="float:right">Vechicle No : <?php echo $db->getVal("select name from bus_detail where id='".$_GET["old_school"]."' and school_id='".$_SESSION["admin"]["school_id"]."'");echo $db->getErMsg(); ?></p>	<p>Transport dues list Vechicle no Wise :</p></h5>
        <?php $aryList=$db->getRow("select * from bus_detail where id='".$_GET["old_school"]."' and school_id='".$_SESSION["admin"]["school_id"]."'")?>
        <button class="btn btn-danger" onclick="exportTableToExcel('tblData', 'Bus Teacher Report')" style="float:right;margin-top:10px;">Excel</button>
        <table class="table" style="border: 2px solid red" cellpadding="0" cellspacing="0">
        <tr>
        <td>Driver Name</td><td class="data"><?php echo $aryList['driver_name']; ?></td>
               
        <td>Driver No</td><td class="data"><?php echo $aryList['driver_no']; ?> </td>
         
          <td>Conductor Name</td><td class="data"><?php echo $aryList['conductor_name']; ?> </td>
           <td>Conductor No</td><td class="data"><?php echo $aryList['conductor_no']; ?> </td>
        </tr>
        <tr>
        	<td>Route</td><td class="data"><?php echo $aryList['root']; ?> </td>
           <td>Vehicle No</td><td class="data"><?php echo $aryList['vehicle_no']; ?> </td>
           <td>Vehicle Type</td><td class="data"><?php echo $aryList['vehicle_type']; ?> </td>
           <td>Vehicle Scholar No</td><td class="data"><?php echo $aryList['vehicle_sch_no']; ?> </td>
        </tr>
         <tr>
        	<td>Category</td><td class="data"><?php echo $aryList['category']; ?> </td>
           <td>Mileage</td><td class="data"><?php echo $aryList['miladge']; ?> </td>
           <td>K.M./day Type</td><td class="data"><?php echo $aryList['km_per_day']; ?> </td>
           <td>No of student</td><td class="data"><?php echo $aryList['no_of_stduent']; ?> </td>
        </tr>
        </table>
		<table cellpadding="0" cellspacing="0" class="table" border="1" id="tblData">
			<thead>
			<tr>
            	<th width="1%">S.No</td>
				<th >Teacher Name</td>
				<th >Father Name</th>
				<th >Contact no</th>
				<th >Address</th>
                		  
			</tr>
			</thead>
			<tbody>
				<?php $i=0;
				foreach($aryStudentList as $fStudent){$i++;$total=0;
					?>
					<tr>
                    	<td><?php echo $i; ?></td>
						<td><?php echo $fStudent["name"] ?></td>
						<td><?php echo $fStudent["fname"] ?></td>
						<td><?php echo $fStudent["mobile"] ?></td>
						<td><?php echo $fStudent["address"] ?></td>
					</tr>
					<?php } ?>
			</tbody>
			
		</table>

        Print date : <?php echo date("d-M-Y") ?>
		<?php
	}else{
		//echo $db->getErMsg().$db->getLastQuery();
	}
 }
}
elseif(isset($_REQUEST['bus_detail'])){ 
    
    $wherebus = "";
    if($_GET['old_school']!=''){
        $wherebus = " and id='$_GET[old_school]' ";
    }
    
 $aryList=$db->getRows("select * from bus_detail where school_id='".$_SESSION["admin"]["school_id"]."' $wherebus ")
  ?> <h5><p style="text-align:center">Total Transport Report</p></h5>
    <button class="btn btn-danger" onclick="exportTableToExcel('tblData', 'Bus Report')" style="float:right;margin:10px;">Excel</button>
        <table cellpadding="0" cellspacing="0" class="table" border="1" id="tblData">
			<thead>
			<tr>
            	<th>S.No</th>
				<th>Bus Name</th>
				<th>Driver Name</th>
				<th>Mobile</th>
				<!--<th>No. of boys</th>-->
                <!--<th>No. of girls</th>-->
                <!--<th>No. of Teacher</th>-->
                <th>Total student</th>
                <th>No. of seats</th>
                <th>Remaining seats</th>
			</tr>
			</thead>
			<tbody>
				<?php $i=0;$total_r=$tot_boys=$tot_girls=$tot_teacher=$tot_student=$tot_seats=0;
				foreach($aryList as $arr){$i++;
					$slist=$db->getRows("Select * from student where old_school ='".$arr['id']."'and school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1 and issue_TC=0 order by name");
				// 	$tlist=$db->getRows("Select * from staff where medium ='".$arr['id']."' and school_id='".$_SESSION["admin"]["school_id"]."' and session='".$_SESSION["admin"]["session"]."' and status=1");
				// 	$tcount=0;
				// 	if(is_array($tlist)&& count($tlist)!=""){
				// 		foreach($tlist as $s){
				// 			$tcount++;
				// 			}
				// 		}
				// 		$tot_teacher+=$tcount;
					$mcount=0;$fcount=0;$total=0;
					if(is_array($slist)&& count($slist)!=""){
						foreach($slist as $s){
						if($s['gender']=="male")
							$mcount++;
						elseif($s['gender']=="female")
							$fcount++;
							$total++;	
						} 
					}$total_s=$total;$total+=$tcount;
					$tot_boys+=$mcount;$tot_girls+=$fcount;$tot_student+=$total_s;
					$remain=$arr['no_of_stduent']-$total;
					$total_r+=$remain;$tot_seats+=$arr['no_of_stduent'];?>
					<tr>
                    	<td><?php echo $i; ?></td>
						<td><?php echo $arr["name"] ?></td>
						<td><?php echo $arr["driver_name"] ?></td>
						<td><?php echo $arr["driver_no"] ?></td>
						<!--<td><?php echo $mcount; ?></td>-->
                        <!--<td><?php echo $fcount; ?></td>-->
                        <!--<td><?php echo $tcount; ?></td>-->
                        <td><?php echo $total_s; ?></td>
                        <td><?php echo $arr["no_of_stduent"] ?></td>
                        <td><?php echo $remain; ?></td>
						
					</tr>
					<?php
				}
				?>
                <tr>
                	<td colspan="4">Gross Total</td>
                    <!--<td ><?php echo $tot_boys; ?></td>-->
                    <!--<td><?php echo $tot_girls; ?></td>-->
                    <!--<td><?php echo $tot_teacher; ?></td>-->
                    <td><?php echo $tot_student; ?></td>
                    <td><?php echo $tot_seats; ?></td>
                    <td><?php echo $total_r; ?></td>
                </tr>
			</tbody>
			
		</table>

        Print date : <?php echo date("d-M-Y") ?>
<?php }else{
	echo "Please select date First";
}
//echo $db->getErMsg()
?>
</div>


<script>
    function exportTableToExcel(tableID, filename = '',e){
    
    var tableType = document.getElementById('tblData').getAttribute("data-tblname");

    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(tableID);
    var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');

    // Specify file name
    filename = filename?filename+'.xls':'excel_data.xls';
    
    // Create download link element
    downloadLink = document.createElement("a");
    
    document.body.appendChild(downloadLink);
    
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        // Create a link to the file
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
    
        // Setting the file name
        downloadLink.download = filename;
        
        //triggering the function
        downloadLink.click();
    }
    e.preventDefault();
}
</script>