<?php include("parent/callMe.php"); ?>
<?php  include("sessioncheck.php"); ?>
<?php
$branch=$_SESSION['branch'];

// current month sms history code

$currentMonthDate=date("Y-m");
$currentMonth=date("m");
/*$CurrentMonthmsgs="SELECT SUM(attendance_msg) as att, SUM(result_msg) as rslt, SUM(fee_msg) as fee, SUM(other_msg) as othr FROM edu_messages_details WHERE branch_id='$branch' and msg_date like '$currentMonthDate-%' ";  
$branch_CurrentMonth_sms=$m->query($CurrentMonthmsgs);*/

// getting fee msgs
$qr_fee="SELECT SUM(msg_count) as msg_count FROM edu_messages_details WHERE branch_id='$branch' and fee_msg='1' and msg_date like '$currentMonthDate-%' ";  
$fee_sms=$m->query($qr_fee);
$fees_sms=$fee_sms[0]['msg_count'];

// getting result msgs
$qr_res="SELECT SUM(msg_count) as msg_count FROM edu_messages_details WHERE branch_id='$branch' and result_msg='1' and msg_date like '$currentMonthDate-%' ";
$result_sms=$m->query($qr_res);
$results_sms=$result_sms[0]['msg_count'];

// getting other msgs
$qr_oth="SELECT SUM(msg_count) as msg_count FROM edu_messages_details WHERE branch_id='$branch' and other_msg='1' and msg_date like '$currentMonthDate-%' ";
$other_sms=$m->query($qr_oth);
$others_sms=$other_sms[0]['msg_count'];

// getting attendance msgs
$qr_att="SELECT SUM(msg_count) as msg_count FROM edu_messages_details WHERE branch_id='$branch' and attendance_msg='1' and msg_date like '$currentMonthDate-%' ";
$att_sms=$m->query($qr_att);
$attendance_sms=$att_sms[0]['msg_count'];

$sessionID=date("Y");
$qr="SELECT SUM(add_sms) as TaditionalSms FROM eduportal_branches_SMScredit_limit WHERE month_id='$currentMonth'  and branch_id='$branch' AND session_id=".$sessionID;

$CurrentMonthAdSMS = $m->query($qr);


$totalSMSCurrentMonth=$brid[0]['monthly_credit']+$CurrentMonthAdSMS[0]['TaditionalSms'];
$totalSMSCurrentMonthUsage=$results_sms+$fees_sms+$attendance_sms+$others_sms;


// previous month sms history

$qr_ab="SELECT * FROM eduportal_branch_monthly_sms_history WHERE branch_id='$branch' order by month_id desc ";
$msgs=$m->query($qr_ab);
//echo "$qr_ab";

?>
<!DOCTYPE html>
<html lang="en">
  <head>
   
    <title>SMS Usage Histroy | EduPortal</title>
     <?php include('RootMe/css_links.php');?>
   
  </head>
  

  <body class="nav-md">
    <div class="container body">
      <div class="main_container">
        <div class="col-md-3 left_col">
     
            <?php include('RootMe/sidebar.php');?>

              <?php include('RootMe/header.php');?>
     
        <!-- page content -->
          <div class="right_col" role="main">
          <div class="row" style="background-color: white;">
           
  <section class="add_sub_agent" id="table_sub_agent">
  <div class="container">

 <div class="" style="margin-top:10px;">
      <form class="" action="monthly_sms_report.php" enctype="multipart/form-data" method="get">
        <div class="">
          <h3 style="text-align: center;color: #022a55;font-weight: bold;">SMS Usage Histroy</h3>

          <input type="hidden" value="<?php echo session_id().session_id().session_id().session_id().session_id(); ?>" name="session" />


          </span><div class="clearfix"></div> </div>
         
        </div>
      </form>
      <div class="clearfix"></div>
    </div>
    <br>
     

<div style="overflow-x:auto;">



<table id="datatable" class="table table-striped table-bordered"style="background-color: white;">
<thead>

<tr style="background-color:#022a55;color:white;font-weight: bold;">
<th></th>
<th colspan="2" style="text-align:center;">SMS Limit</th>
<th colspan="4" style="text-align:center;">SMS Usage</th>
<th colspan="3" style="text-align:center;">Total / Remaning</th>

</tr>

<tr>
<th width="10%">Month/Year</th>
<th width="10%">Monthly Limit</th>
<th width="10%">Extra SMS</th>
<th width="10%">Result</th>
<th width="10%">Fee</th>
<th width="10%">Attendance</th>
<th width="10%">Other's</th>
<th width="10%">Total SMS</th>
<th width="10%">Total Used</th>
<th width="10%">SMS Rem.</th>                         


</tr>
</thead>
<tbody>

<tr>
<td style="text-align:center;font-size: 12px;"><?php echo date("F")." / ".date("Y");?></td>
<td><?php echo $brid[0]['monthly_credit'];?></td>
<td><?php echo $CurrentMonthAdSMS[0]['TaditionalSms'];?></td>
<td><?php echo $results_sms;?></td>
<td><?php echo $fees_sms;?></td>
<td><?php echo $attendance_sms;?></td>
<td><?php echo $others_sms;?></td> 
<td><?php echo $totalSMSCurrentMonth;?></td>
<td><?php echo $totalSMSCurrentMonthUsage;?></td>
<td><?php echo $totalSMSCurrentMonth-$totalSMSCurrentMonthUsage;?></td>

</tr>
                        
<?php  $count=1;

foreach($msgs as $msg){


$m->where("Month_Id", $msg['month_id']);
$months=$m->get("eduportal_month_config");


$totalMonthlyLimt+=$msg['monthly_sms_limit'];
$totalAdditionalSMS+=$msg['additional_sms'];
$totalResultSMS+=$msg['rslt_sms'];
$totalFeeSMS+=$msg['fee_sms'];
$totalAttSMS+=$msg['att_sms'];
$totalOtherSMS+=$msg['other_sms'];

$MonthlyTotalSMS=$msg['monthly_sms_limit']+$msg['additional_sms'];
$MonthlyUsedSMS=$msg['rslt_sms']+$msg['fee_sms']+$msg['att_sms']+$msg['other_sms'];


$AllMonthTotalSMS+=$msg['monthly_sms_limit']+$msg['additional_sms'];
$AllMonthUsedSMS+=$msg['rslt_sms']+$msg['fee_sms']+$msg['att_sms']+$msg['other_sms'];

?>

<tr>
<td style="text-align:center;font-size: 12px;"><?php echo $months[0]['Month_Name']." / ".$msg['year'];?></td>
<td><?php echo $msg['monthly_sms_limit'];?></td>
<td><?php echo $msg['additional_sms'];?></td>
<td><?php echo $msg['rslt_sms'];?></td>
<td><?php echo $msg['fee_sms'];?></td>
<td><?php echo $msg['att_sms'];?></td>
<td><?php echo $msg['other_sms'];?></td> 
<td><?php echo $MonthlyTotalSMS;?></td>
<td><?php echo $MonthlyUsedSMS;?></td>
<td><?php echo $MonthlyTotalSMS-$MonthlyUsedSMS;?></td>

</tr>
<?php } ?>

<tr style="background-color: #022a55; color: white; font-weight: bold;">
  <td style="text-align: center;">Total</td>
  <td><?php echo $totalMonthlyLimt+$brid[0]['monthly_credit'];?></td>
  <td><?php echo $totalAdditionalSMS+$CurrentMonthAdSMS[0]['TaditionalSms'];?></td>
  <td><?php echo $totalResultSMS+$results_sms;?></td>
  <td><?php echo $totalFeeSMS+$fees_sms;?></td>
  <td><?php echo $totalAttSMS+$attendance_sms;?></td>
  <td><?php echo $totalOtherSMS+$others_sms;?></td>
  <td><?php echo $AllMonthTotalSMS+$totalSMSCurrentMonth;?></td>
  <td><?php echo $AllMonthUsedSMS+$totalSMSCurrentMonthUsage;?></td>
  <td><?php echo ($AllMonthTotalSMS+$totalSMSCurrentMonth)-($AllMonthUsedSMS+$totalSMSCurrentMonthUsage);?></td>
</tr>
                      

                      </tbody>
                    </table>
</div>

    <br><br>


  </div>
  <div class="clearfix"></div>
</section>
    

           
          </div>
        </div>

            </div>
          </div>
        </div>
        <!-- /page content -->
  
 <?php include('RootMe/js_linksscript.php');?>

  
  </body>
</html>
