<?php session_start();
include('../accesscheck2.php');
require_once('../Connections/courier.php'); 
require_once '../functions/functions.php';
mysql_select_db($database_courier, $courier);

if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
    {
      if (PHP_VERSION < 6) {
        $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
    }

    $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

    switch ($theType) {
        case "text":
        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
        break;    
        case "long":
        case "int":
        $theValue = ($theValue != "") ? intval($theValue) : "NULL";
        break;
        case "double":
        $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
        break;
        case "date":
        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
        break;
        case "defined":
        $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
        break;
    }
    return $theValue;
}
}

$start_date = $_GET['start_date'];
$end_date = $_GET['end_date'];
$payment_methods = $_GET['payment_method'];
$payment_statuss = $_GET['payment_status'];
list($report_type, $report_type_txt) = explode("@@@", $_GET['report_type']);

if (count($payment_methods) > 0) {
    foreach ($payment_methods as $payment_method) {
        $payment_qry .= "mode_of_payment='$payment_method' OR ";
        $payment_method_txt .= $payment_method.',';
    }
    $payment_qry = substr($payment_qry, 0, -4);
    $payment_method_txt = substr($payment_method_txt, 0,-1);
}

if (count($payment_statuss) > 0) {
    foreach ($payment_statuss as $payment_status) {
        if($report_type == 'rpmm'){
            $payment_sts_qry .= "pay_by='$payment_status' OR ";
            $payment_method_txt .= $payment_status.',';
        }else{
            $payment_sts_qry .= "payment_status='$payment_status' OR ";
        }
    }
    $payment_sts_qry = substr($payment_sts_qry, 0, -4);
}

if($report_type=='dsr'){
  $report_qry = mysql_query("SELECT * FROM claims_details WHERE date_added_short BETWEEN '$start_date' AND '$end_date' AND ($payment_qry) AND ($payment_sts_qry) ORDER BY date_added,mode_of_payment");
  $total_count = mysql_num_rows($report_qry);
} else if($report_type=='ssr'){
    $report_qry = mysql_query("SELECT *,sum(qty*unit_price) as total_amount FROM claims_details WHERE date_added_short BETWEEN '$start_date' AND '$end_date' AND ($payment_qry) AND ($payment_sts_qry) GROUP BY sub_item_service_type ORDER BY date_added");
    $total_count = mysql_num_rows($report_qry);
} else if($report_type=='ssrbonc'){
  $report_qry = mysql_query("SELECT p.id,surname,othernames,p.card_number,tel,email,sum(qty*unit_price) AS total_amount FROM patient_information p,claims_details c WHERE c.date_added_short BETWEEN '$start_date' AND '$end_date' AND ($payment_qry) AND ($payment_sts_qry) AND p.card_number=c.patient_no GROUP BY p.id ORDER BY c.date_added,mode_of_payment");
  $total_count = mysql_num_rows($report_qry);  
} else if($report_type=='ssrboncv'){
  $report_qry = mysql_query("SELECT p.id,surname,othernames,p.card_number,tel,email,process_claim_no,date_of_consultation,sum(qty*unit_price) AS total_amount FROM patient_information p,claims_details c WHERE c.date_added_short BETWEEN '$start_date' AND '$end_date' AND ($payment_qry) AND ($payment_sts_qry) AND p.card_number=c.patient_no GROUP BY process_claim_no ORDER BY date_of_consultation");
  $total_count = mysql_num_rows($report_qry);  
} else if($report_type=='clov'){
  $report_qry = mysql_query("SELECT p.id,surname,othernames,p.card_number,tel,email,process_claim_no,date_of_consultation,sum(qty*unit_price) AS total_amount FROM patient_information p,claims_details c WHERE c.date_added_short BETWEEN '$start_date' AND '$end_date' AND p.card_number=c.patient_no GROUP BY process_claim_no ORDER BY date_of_consultation");
  $total_count = mysql_num_rows($report_qry); 
} else if($report_type == 'rpmm'){
  $report_qry = mysql_query("SELECT c.item,c.unit_price,c.qty,c.process_claim_no,pay_by,date_of_consultation,surname,othernames,p.card_number,tel,email,(qty*unit_price) AS total_amount FROM patient_information p,claims_details c, cashier_payments ch WHERE ch.receiptno=c.receipt_number AND c.date_added_short BETWEEN '$start_date' AND '$end_date' AND ($payment_sts_qry) AND c.payment_status='Paid' AND p.card_number=c.patient_no AND p.card_number=ch.patient_no AND ch.patient_no=c.patient_no ORDER BY c.date_added,mode_of_payment");
  $total_count = mysql_num_rows($report_qry);
}

$settings = mysql_fetch_assoc(mysql_query("SELECT * FROM settings WHERE VAT_NHIL = 'exclusive'"));
$VATNHIL_ty =  (($settings['VAT']+$settings['NHIL'])/100);

?>


<head>
    <meta charset="utf-8" />
    <title> Report Generation </title>
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta content="width=device-width, initial-scale=0" name="viewport" />
    <meta content="" name="description" />
    <meta content="" name="author" />
    <link href="http://fonts.googleapis.com/css?family=Open+Sans:400,300,600,700&subset=all" rel="stylesheet" type="text/css" media="all" />
    <link href="../assets/global/plugins/bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css" media="all"/>
</head>
<style type="text/css" media="all">
    body{
        margin: 0;
    }
    .text-danger strong {
      color: #9f181c;
  }
  .text-danger {
      color: #9f181c;
  }
  .receipt-main {
     background: #ffffff none repeat scroll 0 0;
     /*border-bottom: 12px solid #333333;*/
     border-top: 12px solid #9f181c;
     /*margin-top: 50px;*/
     margin-bottom: 50px;
     padding: 40px 30px !important;
     position: relative;
     box-shadow: 0 1px 21px #acacac;
     color: #333333;
     font-family: open sans;
 }
 .receipt-main p {
     color: #333333;
     font-family: open sans;
     line-height: 1.42857;
 }
 .receipt-footer h1 {
     font-size: 15px;
     font-weight: 400 !important;
     margin: 0 !important;
 }
 .receipt-main::after {
     background: #414143 none repeat scroll 0 0;
     content: "";
     height: 5px;
     left: 0;
     position: absolute;
     right: 0;
     top: -13px;
 }
 .receipt-main thead {
     background: #414143 none repeat scroll 0 0;
 }
 .receipt-main thead th {
     color:#fff;
 }
 .receipt-main .amount {
    text-align: right
}
.receipt-right h5 {
 font-size: 16px;
 font-weight: bold;
 margin: 0 0 7px 0;
}
.receipt-right p {
 font-size: 12px;
 margin: 0px;
}
.receipt-right p i {
 text-align: center;
 width: 18px;
}
.receipt-main td {
 padding: 9px 20px !important;
}
.receipt-main th {
 padding: 12px 15px !important;
}
.receipt-main td {
 font-size: 13px;
 font-weight: initial !important;
}
.receipt-main td p:last-child {
 margin: 0;
 padding: 0;
}	
.receipt-main td h2 {
 font-size: 20px;
 font-weight: 900;
 margin: 0;
 text-transform: uppercase;
}
.receipt-header-mid .receipt-left h1 {
 font-weight: 100;
 margin: 34px 0 0;
 text-align: right;
 text-transform: uppercase;
}
.receipt-header-mid {
 margin: 24px 0;
 overflow: hidden;
}

#container {
 background-color: #dcdcdc;
}
</style>



<div class="container-fluid">
	<div class="row">
        <div class="receipt-main col-xs-12 col-sm-12 col-md-12">
            <div class="row">
             <div class="receipt-header">
               <div class="col-xs-6 col-sm-6 col-md-6">
                  <div class="receipt-left">
                     <img class="img-responsive" src="../images/app_pics/rns_logo.jpeg" style="width: 71px;">
                 </div>
             </div>
             <div class="col-xs-6 col-sm-6 col-md-6 text-right">
              <div class="receipt-right">
                 <h5>ROBERT AND SONS LTD.</h5>
                 <p>+233034789/09678909 <i class="fa fa-phone"></i></p>
                 <p>INFO@ROBERTANDSON.COM <i class="fa fa-envelope-o"></i></p>
                 <p>ADABRAKA BRANCH <i class="fa fa-location-arrow"></i></p>
             </div>
         </div>
     </div>
 </div>

 <div class="row">
    <div class="receipt-header receipt-header-mid">
       <div class="col-xs-8 col-sm-8 col-md-8 text-left">
          <div class="receipt-right">
            <h3> <?php echo strtoupper($report_type_txt); ?> </h3>
            <h4 class="text-danger"> DATE RANGE :  <?php echo date('jS M, Y',  strtotime($start_date));?> - <?php echo date('jS M, Y',  strtotime($end_date)); ?> </h4>
            <h5 class="text-danger"> PAYMENT MODE :  <?php if( $report_type == 'clov'){echo 'ALL MODES';}else{ echo strtoupper($payment_method_txt);} ?> </h5>
        </div>
    </div>
    <div class="col-xs-4 col-sm-4 col-md-4">
      <div class="receipt-left">
         <h1>Report</h1>
     </div>
 </div>
</div>
</div>

<?php if($report_type=='dsr'){ ?>
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>SN</th>
                <th>DATE TIME</th>
                <th>ITEM NAME</th>
                <th>ITEM TYPE</th>
                <th>PAYMENT MODE</th>
                <th>STATUS</th>
                <th class="amount">PRICE</th>
                <th class="amount">QTY</th>
                <th class="amount">AMOUNT</th>
                <th class="amount">VAT EXCL</th>
                <th class="amount">VAT/NHIL</th>
                <th class="amount">DISCOUNT</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            if($total_count > 0){
                $i=1;
                //$vat_figuare = 0.03;
                while($row = mysql_fetch_assoc($report_qry)){ ?>
                    <tr>
                        <td class=""> <?php echo $i;?> </td>
                        <td class=""> <?php echo date('d-m-Y g:ia',  strtotime($row['date_added']));?> </td>
                        <td class=""> <?php echo $row['item'];?> </td>
                        <td class=""> <?php echo $row['item_service'];?> </td>
                        <td><i class="fa fa-inr"></i> <?php echo ucwords($row['mode_of_payment']);?> </td>
                        <td><i class="fa fa-inr"></i> <?php echo $row['payment_status'];?> </td>
                        <td class="amount"><i class="fa fa-inr"></i> <?php echo number_format($row['unit_price'],2);?> </td>
                        <td class="amount"><i class="fa fa-inr"></i> <?php echo $row['qty']; ?> </td>


                        <td class="amount"><i class="fa fa-inr"></i> <?php echo number_format($row['qty']*$row['unit_price'],2);
                         $total_amount = $total_amount + ($row['qty']*$row['unit_price']); ?> </td>


                        <td class="amount"> <?php
                        if($row['item_service_type'] == 'Optical Consultation'){
                         $vat_figuare = 0;
                        }else{
                            $vat_figuare = $VATNHIL_ty;
                        }
                         $vatnhil = $vat_figuare*($row['qty']*$row['unit_price']); $vatexl = ($row['qty']*$row['unit_price'])-$vatnhil;  
                         
                         echo number_format($vatexl,2) ; $vatexcl_total = $vatexcl_total+$vatexl;?>
                         
                         </td>


                        <td class="amount"> <?php echo number_format($vatnhil,2) ; $vatnhil_total = $vatnhil_total+$vatnhil;  ?> </td>
                        <td class="amount"><i class="fa fa-inr"></i> <?php 
                        $discount_info = getDiscountAllowed($row['process_claim_no'], $row['discount_id']);echo $discount_info; $discount_total=$discount_total+$discount_info;?></td>
                    </tr>
                    <?php $i++; }  ?>
                    <tr>
                        <td class="text-right" colspan="8"><h2><strong>Total </strong></h2></td>
                        <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($total_amount, 2); ?> </strong></h2></td>
                        <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($vatexcl_total, 2); ?> </strong></h2></td>
                        <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($vatnhil_total, 2); ?> </strong></h2></td>
                        <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($discount_total, 2); ?> </strong></h2></td>
                    </tr>
                <?php } else{ ?>
                    <tr><td colspan="9" style="text-align: center;">No Records Found</td></tr>
                <?php } ?>
            </tbody>
        </table>
    <?php } 

    if($report_type=='ssr'){ ?>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>SN</th>
                    <th>DATE TIME</th>
                    <th>ITEM NAME</th>
                    <th>ITEM TYPE</th>
                    <th class="amount">AMOUNT</th>
                    <th class="amount">VAT EXCL</th>
                    <th class="amount">VAT/NHIL</th>
                    <th class="amount">DISCOUNT</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                if($total_count > 0){
                    $i=1;
                    while($row = mysql_fetch_assoc($report_qry)){ ?>
                        <tr>
                            <td class=""> <?php echo $i;?> </td>
                            <td class=""> <?php echo date('d-m-Y g:ia',  strtotime($row['date_added']));?> </td>
                            <td class=""> <?php echo $row['sub_item_service_type'];?> </td>
                            <td class=""> <?php echo $row['item_service'];?> </td>
                            <td class="amount"><i class="fa fa-inr"></i> <?php echo number_format($row['total_amount'],2); $total_amount = $total_amount + ($row['total_amount']);?> </td>
                            <td class="amount"> <?php $vatnhil = 0.03*($row['total_amount']); $vatexl = ($row['total_amount'])-$vatnhil;  echo number_format($vatexl,2) ; $vatexcl_total = $vatexcl_total+$vatexl;?></td>
                            <td class="amount"> <?php echo number_format($vatnhil,2) ; $vatnhil_total = $vatnhil_total+$vatnhil;  ?> </td>
                            <td class="amount"><i class="fa fa-inr"></i> <?php 
                            $discount_info = getDiscountAllowed($row['process_claim_no'], $row['discount_id']);echo $discount_info; $discount_total=$discount_total+$discount_info;?></td>
                        </tr>
                        <?php $i++; }  ?>
                        <tr>
                            <td class="text-right" colspan="4"><h2><strong>Total </strong></h2></td>
                            <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($total_amount, 2); ?> </strong></h2></td>
                            <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($vatexcl_total, 2); ?> </strong></h2></td>
                            <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($vatnhil_total, 2); ?> </strong></h2></td>
                            <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($discount_total, 2); ?> </strong></h2></td>
                        </tr>
                    <?php } else{ ?>
                        <tr><td colspan="9" style="text-align: center;">No Records Found</td></tr>
                    <?php } ?>
                </tbody>
            </table>
        <?php }                

        else if($report_type=='ssrbonc'){ ?>
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>SN</th>
                        <th>Patient No</th>
                        <th>Surname</th>
                        <th>First Name</th>
                        <th>Tel Number</th>
                        <th>Email</th>
                        <th class="amount">Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    if($total_count > 0){
                        $i=1;
                        while($row = mysql_fetch_assoc($report_qry)){ ?>
                            <tr>
                                <td class=""> <?php echo $i;?> </td>
                                <td class=""> <?php echo $row['card_number'];?> </td>
                                <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['surname']);?> </td>
                                <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['othernames']);?> </td>
                                <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['tel']);?> </td>
                                <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['email']);?> </td>
                                <td class="amount"><i class="fa fa-inr"></i> <?php echo number_format($row['total_amount'],2);  $total_amount = $total_amount + $row['total_amount'];?> </td>
                            </tr>
                            <?php $i++; }  ?>
                            <tr>
                                <td class="text-right" colspan="6"><h2><strong>Total </strong></h2></td>
                                <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($total_amount, 2); ?> </strong></h2></td>
                            </tr>
                        <?php }else{ ?>
                            <tr><td colspan="8" style="text-align: center;">No Records Found</td></tr>
                        <?php } ?>
                    </tbody>
                </table>
            <?php }
            else if($report_type=='ssrboncv' || $report_type == 'clov'){ ?>
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>SN</th>
                            <th>Consultation Date</th>
                            <th>Patient No</th>
                            <th>Surname</th>
                            <th>First Name</th>
                            <th>Visit No.</th>
                            <th>Tel Number</th>
                            <th>Email</th>
                            <?php if($_SESSION['security'] != 'MEDICAL OFFICER'){ ?>
                                <th class="amount">Total Amount</th>
                            <?php } ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php 
                        if($total_count > 0){
                            $i=1;
                            while($row = mysql_fetch_assoc($report_qry)){ ?>
                                <tr>
                                    <td class=""> <?php echo $i;?> </td>
                                    <td class=""> <?php echo date('jS M,Y',  strtotime($row['date_of_consultation']));?> </td>
                                    <td class=""> <?php echo $row['card_number'];?> </td>
                                    <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['title']).' '. strtoupper($row['surname']);?> </td>
                                    <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['othernames']);?> </td>
                                    <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['process_claim_no']);?> </td>
                                    <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['tel']);?> </td>
                                    <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['email']);?> </td>
                                    <?php if($_SESSION['security'] != 'MEDICAL OFFICER'){ ?>
                                        <td class="amount"><i class="fa fa-inr"></i> <?php echo number_format($row['total_amount'],2);  $total_amount = $total_amount + $row['total_amount'];?> </td>
                                    <?php } ?>
                                </tr>
                                <?php $i++; }  ?>
                                <?php if($_SESSION['security'] != 'MEDICAL OFFICER'){ ?>
                                    <tr>
                                        <td class="text-right" colspan="8"><h2><strong>Total </strong></h2></td>
                                        <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($total_amount, 2); ?> </strong></h2></td>
                                    </tr>
                                <?php } ?>
                            <?php }else{ ?>
                                <tr><td colspan="8" style="text-align: center;">No Records Found</td></tr>
                            <?php } ?>
                        </tbody>
                    </table>
                <?php } 
                else if($report_type=='rpmm'){ ?>
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th>SN</th>
                                <th>Consultation Date</th>
                                <th>Patient No</th>
                                <th>Full Name</th>
                                <th>Visit No.</th>
                                <th>Payment Mode</th>
                                <th>Item Name</th>
                                <th>Unit Price</th>
                                <th>Qty</th>
                                <th class="amount">Total Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            if($total_count > 0){
                                $i=1;
                                while($row = mysql_fetch_assoc($report_qry)){ ?>
                                    <tr>
                                        <td class=""> <?php echo $i;?> </td>
                                        <td class=""> <?php echo date('jS M,Y',  strtotime($row['date_of_consultation']));?> </td>
                                        <td class=""> <?php echo $row['card_number'];?> </td>
                                        <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['title']).' '. strtoupper($row['surname'].' '.$row['othernames']);?> </td>
                                        <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['process_claim_no']);?> </td>
                                        <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['pay_by']);?> </td>
                                        <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['item']);?> </td>
                                        <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['unit_price']);?> </td>
                                        <td><i class="fa fa-inr"></i> <?php echo strtoupper($row['qty']);?> </td>
                                        <td class="amount"><i class="fa fa-inr"></i> <?php echo number_format($row['total_amount'],2);  $total_amount = $total_amount + $row['total_amount'];?> </td>
                                    </tr>
                                    <?php $i++; }  ?>
                                    <tr>
                                        <td class="text-right" colspan="9"><h2><strong>Total </strong></h2></td>
                                        <td class="text-left text-danger amount"><h2><strong><i class="fa fa-inr"></i>  <?php echo number_format($total_amount, 2); ?> </strong></h2></td>
                                    </tr>
                                <?php }else{ ?>
                                    <tr><td colspan="8" style="text-align: center;">No Records Found</td></tr>
                                <?php } ?>
                            </tbody>
                        </table>
                    <?php } ?>


                </div>    
            </div>
        </div>
