webadmin.php

/var/www/(Del)pathways.org.hk/MIS20140127/old20140414/student_invoice/invoice_search.php

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <?php
        require_once '../include/head.php';
        require_once '../include/checkuser.php';
        require_once '../include/Nav_bar.php';
        include_once '../include/DBConnect.php';
        if (isset($_POST['generate1']) && $_POST['generate1'] == 'Generate') {
            //$root_id = $_POST['root_id']; //this is root id
            $sch_id = $_POST['sch_id'];
            $invoice_date = $_POST['invoice_date'];
            $event_code = $_POST['event_code'];
            $total_price = $_POST['total_price'];
            $total_discount = $_POST['total_discount'];
            $total_amount = $_POST['total_amount'];
            $funding_type = $_POST['funding_type'];
            $desc = $_POST['desc'];

            $curr_yr = date("Y");
            $curr_mon = date("M");
            if ($curr_mon < 9) {
                $curr_yr--;
            }
            $curr_yr = substr($curr_yr, 2);

            $query_no_of_invocie = "Select count(*) as no_of_invoice from invoice";
            $result_no_of_invoice = $dbh->query($query_no_of_invocie);
            $result_no_of_invoice->setFetchMode(PDO::FETCH_OBJ);
            $no_of_invoice = $result_no_of_invoice->fetch()->no_of_invoice;
            $no_of_invoice++;
            $prog_code = substr($event_code, 0, 2);
            if ($no_of_invoice < 10)
                $temp_code = '000';
            else if ($no_of_invoice < 100)
                $temp_code = '00';
            else if ($no_of_invoice < 1000)
                $temp_code = '0';
            $invoice_code = "KL" . $prog_code . $curr_yr . $temp_code . $no_of_invoice;

            echo "<script>alert('Invoice Code :" . $invoice_code . "')</script>";

            $query = "Insert into invoice set invoice_code='$invoice_code',root_id='0',createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),actived=1,deleted=0,
                school_id='$sch_id',event_code='$event_code',invoice_date='$invoice_date',funding_type='$funding_type',total_original_fee='$total_price',
                    total_discount_fee='$total_discount',total_amount='$total_amount',description='$desc'";
            $result = $dbh->query($query);
            if (!$result) {
                echo '<script>alert("Error2.1 .")</script>';
            }
            $query_get_invoice_id = "Select Max(invoice_id) as i_id from invoice where school_id='$sch_id' and event_code='$event_code' and deleted=0";
            $result_get_invoice_id = $dbh->query($query_get_invoice_id);
            $result_get_invoice_id->setFetchMode(PDO::FETCH_OBJ);
            $invoice_id = $result_get_invoice_id->fetch()->i_id;
            $query3 = "Update invoice set root_id='$invoice_id' where invoice_id='$invoice_id'";
            $dbh->query($query3);

            if (isset($_POST['activity_id'])) {
                for ($j = 0; $j < count($_POST['activity_id']); $j++) {
                    $activity_id = $_POST['activity_id'][$j];
                    //$price = $_POST['price'][$j];
                    //$discount = $_POST['discount'][$j];
                    $amount = $_POST['amount'][$j];
                    $hr = $_POST['hours'][$j];
                    if (isset($_POST['is_charge'][$j]))
                        $is_charge = 0;
                    else
                        $is_charge = 1;
                    $query1 = "Insert into invoice_line set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),
                    actived=1,deleted=0,invoice_id='$invoice_id',activity_id='$activity_id',amount='$amount',
                        hour='$hr',subsidy_percent='0',is_charge='$is_charge',remarks=''";

                    $result1 = $dbh->query($query1);
                    if (!$result1) {
                        echo '<script>alert("Error1 .")</script>';
                    }

                    $query2 = "Update activity_stu_list set invoice_gened=1 where activity_id='$activity_id' 
                    and deleted=0";
                    $result2 = $dbh->query($query2);
                    if (!$result2) {
                        echo '<script>alert("Error2 .")</script>';
                    }
                }
            }
            if (isset($_POST['activity_id'])) {
                $no_of_act = count($_POST['activity_id']);
            } else {
                $no_of_act = 0;
            }
            $i = $_POST['i'];
            $i-=$no_of_act;
            for ($j = 0; $j < $i; $j++) {
                $lesson_id = $_POST['lesson_id'][$j];
                $j+=$no_of_act;
                $amount = $_POST['amount'][$j];
                $hr = $_POST['hours'][$j];
                if (isset($_POST['is_charge'][$j])) {
                    $is_charge = 0;
                } else {
                    $is_charge = 1;
                }
                $query1 = "Insert into invoice_line set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),
                    actived=1,deleted=0,invoice_id='$invoice_id',lesson_id='$lesson_id',amount='$amount',
                        hour='$hr',subsidy_percent='0',is_charge='$is_charge',remarks=''";

                $result1 = $dbh->query($query1);
                if (!$result1) {
                    echo '<script>alert("Error1 .")</script>';
                }

                $query2 = "Update lesson_attendance set invoice_gened=1 where lesson_id='$lesson_id' 
                    and deleted=0";
                $result2 = $dbh->query($query2);

                if (!$result2) {
                    echo '<script>alert("Error2 .")</script>';
                }
                $j-=$no_of_act;
            }
            echo '<script>alert("Generate invoice successful.")</script>';
            echo("<script>location.href ='invoice.php';</script>");
        }

        if (isset($_POST['generate']) && $_POST['generate'] == 'Generate') {
            $root_id = $_POST['root_id']; //this is root id
            $invoice_date = $_POST['invoice_date'];
            $event_code = $_POST['event_code'];
            $total_price = $_POST['total_price'];
            $total_discount = $_POST['total_discount'];
            $total_amount = $_POST['total_amount'];
            $funding_type = $_POST['funding_type'];

            $curr_yr = date("Y");
            $curr_mon = date("M");
            if ($curr_mon < 9) {
                $curr_yr--;
            }
            $curr_yr = substr($curr_yr, 2);
            $query_no_of_invocie = "Select count(*) as no_of_invoice from invoice";
            $result_no_of_invoice = $dbh->query($query_no_of_invocie);
            $result_no_of_invoice->setFetchMode(PDO::FETCH_OBJ);
            $no_of_invoice = $result_no_of_invoice->fetch()->no_of_invoice;
            $no_of_invoice++;
            $prog_code = substr($event_code, 0, 2);
            if ($no_of_invoice < 10)
                $temp_code = '000';
            else if ($no_of_invoice < 100)
                $temp_code = '00';
            else if ($no_of_invoice < 1000)
                $temp_code = '0';
            $invoice_code = "KL" . $prog_code . $curr_yr . $temp_code . $no_of_invoice;

            echo "<script>alert('Invoice Code :" . $invoice_code . "')</script>";

            //invoice data 
            $query = "Insert into invoice set invoice_code='$invoice_code',root_id='0',createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),actived=1,deleted=0,
        stu_linking_id='$root_id',event_code='$event_code',invoice_date='$invoice_date',funding_type='$funding_type',total_original_fee='$total_price',
        total_discount_fee='$total_discount',total_amount='$total_amount'";
            $result = $dbh->query($query);
            if (!$result) {
                echo '<script>alert("Error1 .")</script>';
            }
            $query_get_invoice_id = "Select Max(invoice_id) as i_id from invoice where stu_linking_id='$root_id' and event_code='$event_code'  and deleted=0";
            $result_get_invoice_id = $dbh->query($query_get_invoice_id);
            $result_get_invoice_id->setFetchMode(PDO::FETCH_OBJ);
            $invoice_id = $result_get_invoice_id->fetch()->i_id;
            $query3 = "Update invoice set root_id='$invoice_id' where invoice_id='$invoice_id'";
            $dbh->query($query3);
            //insert invoice here

            if (isset($_POST['activity_id'])) {
                for ($j = 0; $j < count($_POST['activity_id']); $j++) {
                    $activity_id = $_POST['activity_id'][$j];
                    $fee_type = $_POST['fee_type'][$j];
                    $price = $_POST['price'][$j];
                    $attendance = $_POST['attendance'][$j];
                    $discount = $_POST['discount'][$j];
                    $amount = $_POST['amount'][$j];
                    $hr = $_POST['hours'][$j];
                    if (isset($_POST['is_charge'][$j]))
                        $is_charge = 0;
                    else
                        $is_charge = 1;
                    $type = $_POST['type'][$j];
                    $type_rate = $_POST['type_rate'][$j];
                    if ($type == 1) {
                        $query1 = "Insert into invoice_line set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),
        actived=1,deleted=0,invoice_id='$invoice_id',activity_id='$activity_id',event_programme_fee_type='$fee_type',
        discount_type='$type',original_fee='$price',discounted_fee='$discount',amount='$amount',
        hour='$hr',subsidy_percent='$type_rate',is_charge='$is_charge',remarks=''";
                    } else {
                        $query1 = "Insert into invoice_line set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),
        actived=1,deleted=0,invoice_id='$invoice_id',lesson_id='$lesson_id',event_programme_fee_type='$fee_type',
        discount_type='$type',original_fee='$price',discounted_fee='$discount',amount='$amount',
        hour='$hr',subsidy_percent='0',is_charge='$is_charge',remarks=''";
                    }
                    $result1 = $dbh->query($query1);
                    if (!$result1) {
                        echo '<script>alert("Error1 .")</script>';
                    }
                    $query2 = "Update activity_stu_list set invoice_gened=1 where activity_id='$activity_id' 
        and stu_linking_id='$root_id'";
                    $result2 = $dbh->query($query2);
                    if (!$result2) {
                        echo '<script>alert("Error2 .")</script>';
                    }
                }
            }
            if (isset($_POST['activity_id'])) {
                $no_of_act = count($_POST['activity_id']);
            } else {
                $no_of_act = 0;
            }
            $i = $_POST['i'];
            $i-=$no_of_act;
            for ($j = 0; $j < $i; $j++) {
                $lesson_id = $_POST['lesson_id'][$j];
                $attendance = $_POST['attendance'][$j];
                $fee_type = $_POST['fee_type'][$j];
                $j+=$no_of_act;
                $price = $_POST['price'][$j];
                $discount = $_POST['discount'][$j];
                $amount = $_POST['amount'][$j];
                $hr = $_POST['hours'][$j];
                if (isset($_POST['is_charge'][$j]))
                    $is_charge = 0;
                else
                    $is_charge = 1;
                $type = $_POST['type'][$j];
                $type_rate = $_POST['type_rate'][$j];
                if ($type == 1) {
                    $query1 = "Insert into invoice_line set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),
        actived=1,deleted=0,invoice_id='$invoice_id',lesson_id='$lesson_id',event_programme_fee_type='$fee_type',
        discount_type='$type',original_fee='$price',discounted_fee='$discount',amount='$amount',
        hour='$hr',subsidy_percent='$type_rate',is_charge='$is_charge',remarks=''";
                } else {
                    $query1 = "Insert into invoice_line set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),
        actived=1,deleted=0,invoice_id='$invoice_id',lesson_id='$lesson_id',event_programme_fee_type='$fee_type',
        discount_type='$type',original_fee='$price',discounted_fee='$discount',amount='$amount',
        hour='$hr',subsidy_percent='0',is_charge='$is_charge',remarks=''";
                }
                $result1 = $dbh->query($query1);
                if (!$result1) {
                    echo '<script>alert("Error1 .")</script>';
                }
                $query2 = "Update lesson_attendance set invoice_gened=1 where lesson_id='$lesson_id' 
        and stu_linking_id='$root_id'";
                $result2 = $dbh->query($query2);
                if (!$result2) {
                    echo '<script>alert("Error2 .")</script>';
                }
                $j-=$no_of_act;
            }
            echo '<script>alert("Generate invoice successful.")</script>';
            echo("<script>location.href = 'invoice.php';</script>");
        }
        ?>
        <script>
            function cal_total() {
                var i = $('#i').val();
                var total_amount = 0;
                for (var j = 0; j < i; j++) {
                    //var obj=getElementById('lesson_'+j);
                    //if (obj.checked) {
                    var temp_amount = parseFloat($('#amount_' + j).val());
                    total_amount += temp_amount;
                    //}
                }
                $('#total_amount').val(total_amount);
            }
            ;
            function cal_discount() {
                var i = $('#i').val();
                var total_discount = 0;
                for (var j = 0; j < i; j++) {
                    //var obj=getElementById('lesson_'+j);
                    //if (obj.checked) {
                    var temp_discount = parseFloat($('#discount_' + j).val());
                    total_discount += temp_discount;
                    //}
                }
                $('#total_discount').val(total_discount);
            }
            ;
            $(function() {
                $('.date-picker').datetimepicker({pickTime: false, format: 'yyyy-MM-dd'});
            });
            function change_discount(obj) {
                //alert(obj.id);
                var target_id = obj.id;
                var target_i = target_id.split('_')[1];

                $.ajax({
                    type: "POST",
                    url: "ajax_invoice.php",
                    data: {
                        type: $('#type_' + target_i).val(),
                        linking_id: $('#linking_id').val()
                    },
                    cache: false,
                    success: function(html) {
                        if ($('#type_' + target_i).val() == 1) {//subsidy
                            $('#typerate_' + target_i).val(html);
                            var fee = $('#price_' + target_i).val() * (1 - (html / 100));//fee=amount wrong naming in the very first beginning
                            var discount = $('#price_' + target_i).val() * ((html / 100));
                            $('#amount_' + target_i).val(fee);
                            $('#discount_' + target_i).val(discount);
                            $('#typerate_' + target_i).attr("readonly", true);
                            cal_total();
                            cal_discount();
                        }
                        else {
                            if ($('#type_' + target_i).val() == 2) {//package_discount
                                $('#typerate_' + target_i).val(html);
                                $('#discount_' + target_i).val(html);
                                var fee = $('#price_' + target_i).val() - html;
                                $('#amount_' + target_i).val(fee);
                                $('#typerate_' + target_i).attr("readonly", true);
                                cal_total();
                                cal_discount();
                            }
                        }
                    }
                });
            }
            function in_sch_change_amount(obj) {
                var target_id = obj.id;
                var target_i = target_id.split('_')[1];
                var fee = $('#temp_' + target_i).val();//fee=amount wrong naming in the very first beginning
                fee = parseFloat(fee);
                $('#amount_' + target_i).val(fee);
                cal_total();
            }
            function not_charge(obj) {
                var target_id = obj.id;
                var target_i = target_id.split('_')[1];
                if (obj.checked) {
                    $('#typerate_' + target_i).val(0);
                    $('#discount_' + target_i).val(0);
                    $('#amount_' + target_i).val(0);
                    $('#type_' + target_i).attr('disabled', true);
                    cal_total();
                    cal_discount();
                }
                else {
                    change_discount(obj);
                    $('#type_' + target_i).attr('disabled', false);
                }
            }
            ;
            function in_school_not_charge(obj) {
                var target_id = obj.id;
                var target_i = target_id.split('_')[1];
                if (obj.checked) {
                    $('#amount_' + target_i).val(0);
                    cal_total();
                }
                else {
                    in_sch_change_amount(obj);
                    cal_total();
                }
            }
            ;
            function funding(obj) {
                var target_id = obj.id;
                var target_i = target_id.split('_')[1];
                var type_rate = $('#typerate_' + target_i).val();
                var price = $('#price_' + target_i).val();
                var fee = price - type_rate;//typerate_
                $('#discount_' + target_i).val(type_rate);
                $('#amount_' + target_i).val(fee);
                cal_total();
                cal_discount();
            }
            ;
            function selected(obj) {
                var target_id = obj.id;
                var target_i = target_id.split('_')[1];
                if (obj.checked == true) {
                    /*if ($('#is_charge_' + target_i).checked == true) {
                     $('#type_' + target_i).attr('disabled', false);
                     $('#charge_' + target_i).attr('disabled', false);
                     //change_discount(obj);
                     $('#discount_' + target_i).val(0);
                     $('#amount_' + target_i).val(0);
                     cal_total();
                     cal_discount();
                     }
                     else {*/
                    var charge = document.getElementById('charge_' + target_i);
                    $('#type_' + target_i).attr('disabled', false);
                    $('#charge_' + target_i).attr('disabled', false);
                    if (charge.checked==true) {
                        $('#typerate_' + target_i).val(0);
                        $('#discount_' + target_i).val(0);
                        $('#amount_' + target_i).val(0);
                    }
                    else {
                        change_discount(obj);
                    }
                    cal_total();
                    cal_discount();
                    //}
                }
                else {
                    $('#type_' + target_i).attr('disabled', true);
                    $('#charge_' + target_i).attr('disabled', true);
                    $('#discount_' + target_i).val(0);
                    $('#amount_' + target_i).val(0);
                    cal_total();
                    cal_discount();
                }
            }
            $(document).ready(function() {
                $('#start_date').datetimepicker().on('changeDate', function(ev) {
                    date = new Date($('#start_date_input').val());
                    $('#end_date').datetimepicker('setStartDate', date);
                    $('#end_date').datetimepicker('setDate', date);
                    $('#end_date').datetimepicker('show');
                    $('#start_date').datetimepicker('hide');
                    $('#end_date').datetimepicker('remove');
                });
                $('#end_date').datetimepicker().on('changeDate', function(ev) {
                    $('#end_date').datetimepicker('hide');
                });
            });
        </script>
    </head>
    <body>
        <!-- line between nav bar and content -->
        <div class="text-right">
            <ul class="breadcrumb">
                <li class="active"></li>
            </ul>
        </div>
        <!-- line between nav bar and content -->
        <div class="container-fluid pathways-container">
            <?php
            if (isset($_POST['select1']) && $_POST['select1'] == "Select") {
                //in-school invoice
                $percent = 0;
                $id = $_POST['id'];
                $temp_id = explode("|", $id);
                $sch_id = $temp_id[1];
                $event_id = $temp_id[0];
                $from_date = $_POST['from_date'];
                $to_date = $_POST['to_date'];
                ?>
                <form action="" method="POST" class="form-inline">
                    <table border="0" class="table table-striped table-bordered table-hover table-condensed" width="100%">
                        <?php
                        $query = "Select DATE_FORMAT(l.date, '%W') as weekday,e.event_id,e.event_code,e.school_id,l.lesson_id,l.start_time,l.end_time,l.date,stu.first_name,stu.last_name,stu.linking_id,stu.root_id,stu.New_stu_ID as id,e.programme_fee_type,e.program_id,e.subject_id,e.primary_individual_price,e.primary_group_price,e.secondary_individual_price,e.secondary_group_price,
                                    e.primary_package_price,e.secondary_package_price,g.grade,la.attendance ,sch.school_id,sch.sch_name,sch.sch_ch_name,e.program_id,e.subject_id,sub.sub_name,prog.program_name
                                    from event as e, lesson_attendance as la,student_grade as sg,grade as g, school as sch,subject as sub,program as prog,
                                    student as stu,lesson as l,event_lesson as el where e.event_id=el.event_id and sub.subject_id=e.subject_id and prog.program_id=e.program_id and
                                    el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id and stu.root_id=la.stu_linking_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                                    and sch.school_id=e.school_id and e.deleted=0 and e.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                                    and l.deleted=0 and l.actived=1 and el.deleted=0 and el.actived=1 and la.invoice_gened=0 and e.event_id='$event_id' and e.school_id='$sch_id' and l.date>='$from_date' and l.date<='$to_date' group by l.lesson_id";
                        $result = $dbh->query($query); //get invoice data
                        if ($result) {
                            $result->setFetchMode(PDO::FETCH_OBJ);
                            $total_price = 0.0;
                            $total_discount = 0.0;
                            $total_amount = 0.0;
                            $total_hr = 0.0;
                            $i = 0;

                            while ($row = $result->fetch()) {
                                if ($i == 0) {
                                    ?>
                                    <tr>
                                        <td colspan="6"><center><h3>INVOICE</h3></center></td>
                                    </tr>
                                    <tr>
                                        <td>Bill To:</td>
                                        <td colspan="2">
                                            <input type="text" name="bill_to" required="true" value="<?= $row->sch_name ?>" readonly="true">
                                            <input type="hidden" name="sch_id" id="sch_id" value="<?= $row->school_id ?>">
                                        </td>
                                        <td>Invoice Date:</td>
                                        <td colspan="3">
                                            <div class="input-append date-picker">
                                                <input type="text" name="invoice_date" class="dateISO" required='true'>
                                                <span class="add-on">
                                                    <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                </span>
                                            </div>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>Program-Subject</td>
                                        <td colspan="2"><input type="text" name="prog_sub" value="<?= $row->program_name . '-' . $row->sub_name ?>" readonly="true"></td>
                                        <td>Event Code:</td>
                                        <td colspan="3"><input type="text" name="event_code" value="<?= $row->event_code ?>" readonly="true"></td>
                                    </tr>
                                    <tr>
                                        <td colspan="1">Funding Type</td>
                                        <td colspan="6">
                                            <select name="funding_type">
                                                <option value="0">No Funding</option>
                                                <?php
                                                $query_funding = "Select funding_id,funding_type from funding where deleted=0 and actived=1";
                                                $result_funding = $dbh->query($query_funding);
                                                $result_funding->setFetchMode(PDO::FETCH_OBJ);
                                                while ($row_funding = $result_funding->fetch()) {
                                                    ?>
                                                    <option value="<?= $row_funding->funding_id ?>"><?= $row_funding->funding_type ?></option>
                                                    <?php
                                                }
                                                ?>
                                            </select>
                                        </td>
                                    </tr>
                                    <tr>
                                        <th>Date</th>
                                        <th>Time from</th>
                                        <th>Time to</th>
                                        <th>Hour(s)</th>
                                        <th>Not Charge?</th>
                                        <th>Amount($)</th>
                                    </tr>
                                    <?php
                                    //activity
                                    $query_activity = "Select DATE_FORMAT(act.date, '%W') as weekday,e.event_id,act.activity_id,act.start_time,act.end_time,act.payment,act.date
                                    from activity as act,activity_stu_list as act_stu,event as e, lesson_attendance as la,student_grade as sg,grade as g, school as sch,subject as sub,program as prog,
                                    student as stu,lesson as l,event_lesson as el where act.activity_id=act_stu.activity_id and e.event_id=act.event_id and e.event_id=el.event_id and sub.subject_id=e.subject_id and prog.program_id=e.program_id and
                                    el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id and stu.root_id=la.stu_linking_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                                    and sch.school_id=e.school_id and act.deleted=0 and act.actived=1 and e.deleted=0 and e.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                                    and l.deleted=0 and l.actived=1 and el.deleted=0 and el.actived=1 and act_stu.invoice_gened=0 and la.invoice_gened=0 and e.event_id='$event_id' and e.school_id='$sch_id' and l.date>='$from_date' and l.date<='$to_date' group by act.activity_id";
                                    $result_activity = $dbh->query($query_activity);
                                    $result_activity->setFetchMode(PDO::FETCH_OBJ);
                                    while ($row_activity = $result_activity->fetch()) {
                                        $query_get_stu = "Select count(*) as no_of_stu from activity_stu_list where activity_id='$row_activity->activity_id'";
                                        $result_get_stu = $dbh->query($query_get_stu);
                                        $result_get_stu->setFetchMode(PDO::FETCH_OBJ);
                                        $no_of_stu = $result_get_stu->fetch()->no_of_stu;
                                        //cal amount and hr

                                        $hour = (round((strtotime($row->end_time) - strtotime($row->start_time)) / 3600, 2));
                                        $price = $no_of_stu * $row_activity->payment * $hour;
                                        $amount = $price;
                                        $total_price += $price;
                                        $total_amount += $amount;
                                        $total_hr+=$hour;
                                        ?>
                                        <tr>
                                            <td><input type="hidden" name="activity_id[]" id="activity_<?= $i ?>" value="<?= $row_activity->activity_id ?>" onclick="//selected(this);">
                                                <?= $row_activity->date . '(' . $row_activity->weekday . ')' ?><input type="hidden" name="date[]" value="<?= $row_activity->date ?>"></td>
                                            <td><?= $row_activity->start_time ?><input type="hidden" name="start_time[]" value="<?= $row_activity->start_time ?>"></td>
                                            <td><?= $row_activity->end_time ?><input type="hidden" name="end_time[]" value="<?= $row_activity->end_time ?>"></td>
                                            <td><?= $hour ?><input type="hidden" name="hours[]" value="<?= $hour ?>"></td>
                                            <td><input type="checkbox" name="is_charge[]" id="charge_<?= $i ?>" onclick="in_school_not_charge(this);" value="1"></td>
                                            <td>
                                                <input type="text" name="amount[]" id='amount_<?= $i ?>' value="<?= $amount ?>" readonly="true">
                                                <input type="hidden" name="temp_amount[]" id="temp_<?= $i ?>"  value="<?= $amount ?>">
                                            </td>
                                        </tr>
                                        <?php
                                        $i++;
                                    }
                                }
                                $query4 = "Select Max(invoice_id) as i from invoice";
                                $result4 = $dbh->query($query4);
                                $result4->setFetchMode(PDO::FETCH_OBJ);
                                $row4 = $result4->fetch();
                                $query5 = "Select count(*) as no_of_stu from lesson_attendance where lesson_id ='$row->lesson_id' and deleted=0 and actived=1 group by lesson_id";
                                $result5 = $dbh->query($query5);
                                $result5->setFetchMode(PDO::FETCH_OBJ);
                                $row5 = $result5->fetch();

                                $hour = (round((strtotime($row->end_time) - strtotime($row->start_time)) / 3600, 2));
                                $price = $row->primary_individual_price * $hour;
                                $amount = $price;
                                $total_price += $price;
                                $total_amount += $amount;
                                $total_hr+=$hour;
                                ?>
                                <tr>
                                    <td><input type="hidden" name="lesson_id[]" id="lesson_<?= $i ?>" value="<?= $row->lesson_id ?>" checked>
                                        <?= $row->date . '(' . $row->weekday . ')' ?><input type="hidden" name="date[]" value="<?= $row->date ?>"></td>
                                    <td><?= $row->start_time ?><input type="hidden" name="start_time[]" value="<?= $row->start_time ?>"></td>
                                    <td><?= $row->end_time ?><input type="hidden" name="end_time[]" value="<?= $row->end_time ?>"></td>
                                    <td><?= $hour ?><input type="hidden" name="hours[]" value="<?= $hour ?>"></td>
                                    <td><input type="checkbox" name="is_charge[]" id="charge_<?= $i ?>" onclick="in_school_not_charge(this);" value="1"></td>
                                    <td>
                                        <input type="text" name="amount[]" id='amount_<?= $i ?>' value="<?= $amount ?>" readonly="true">
                                        <input type="hidden" name="temp_amount[]" id="temp_<?= $i ?>"  value="<?= $amount ?>">
                                    </td>
                                </tr>
                                <?php
                                $i++;
                            }//end while loop
                            ?>
                            <tr>
                                <td>&nbsp;</td>
                                <td>&nbsp;</td>
                                <td>
                                    Total price:<?= $total_price ?><input type="hidden" name="total_price" value='<?= $total_price ?>'>
                                    <input type='hidden' name='total_discount' id='total_discount' value='<?= $total_discount ?>'>
                                </td>
                                <td>Total hours:<input type="text" name="desc" readonly="true" value="<?= $total_hr ?> hour(s)"></td>
                                <td>&nbsp;</td>
                                <td>
                                    Total amount:<br><input type='text' name='total_amount' id='total_amount' value='<?= $total_amount ?>' readonly='true'>
                                    <input type="hidden" name="i" id="i" value="<?= $i ?>">
                                </td>
                            </tr>
                        </table>
                        <input type="button" value="Cancel" class="btn" onclick="window.history.go(-1);"/>
                        <input type="submit" name="generate1" value="Generate" class="btn">
                    </form>
                    <?php
                }//end result's if
            } else {
                if (isset($_POST['select']) && $_POST['select'] == "Select") {
                    $percent = 0;
                    $id = $_POST['id'];
                    $temp_id = explode("|", $id);
                    $stu_id = $temp_id[1];
                    $event_id = $temp_id[0];
                    $from_date = $_POST['from_date'];
                    $to_date = $_POST['to_date'];
                    //echo $from_date.'              '.$to_date;
                    ?>
                    <form action="" method="POST" class="form-inline">
                        <table border="0" class="table table-striped table-bordered table-hover table-condensed" width="100%">
                            <?php
                            $query = "Select DATE_FORMAT(l.date, '%W') as weekday,e.event_id,e.event_code,l.lesson_id,l.start_time,l.end_time,l.date,stu.first_name,
                        stu.last_name,stu.linking_id,stu.root_id, e.programme_fee_type,e.program_id,e.subject_id,sub.sub_name,prog.program_name,
                        e.primary_individual_price,e.primary_group_price,e.secondary_individual_price,e.secondary_group_price,
                        e.primary_package_price,e.secondary_package_price,g.grade,la.attendance,la.package_discount,la.is_charge
                        from event as e, lesson_attendance as la,student_grade as sg,grade as g,subject as sub,program as prog,
                        student as stu,lesson as l,event_lesson as el where e.event_id=el.event_id and
                        el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id and stu.root_id=la.stu_linking_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                        and sub.subject_id=e.subject_id and prog.program_id=e.program_id and e.deleted=0 and e.actived=1 and sg.deleted=0 and sg.actived=1 and g.deleted=0 and g.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                        and l.deleted=0 and l.actived=1 and el.deleted=0 and el.actived=1 and stu.linking_id='$stu_id' and l.date>='$from_date' and l.date<='$to_date' and e.event_id='$event_id'";
                            $result = $dbh->query($query); //get invoice data
                            if ($result) {
                                $result->setFetchMode(PDO::FETCH_OBJ);
                                $total_price = 0.0;
                                $total_discount = 0.0;
                                $total_amount = 0.0;
                                $total_hr=0;
                                $i = 0;

                                while ($row = $result->fetch()) {
                                    $query1 = "Select ss.stu_subsidy_id,s.percent as sum_p from student_subsidy as ss, subsidy as s where ss.subsidy_id = s.subsidy_id and ss.deleted=0 and s.deleted=0
                            and s.actived=1 and ss.actived=1 and ss.stu_subsidy_id='$stu_id'";
                                    $result1 = $dbh->query($query1); //get percent
                                    if ($result1->rowcount() == 0) {
                                        $percent = 0;
                                    } else {
                                        $result1->setFetchMode(PDO::FETCH_OBJ);
                                        $row1 = $result1->fetch();
                                        $percent = $row1->sum_p;
                                    }
                                    if ($i == 0) {
                                        ?>
                                        <tr>
                                            <td colspan="14"><center><h3>INVOICE</h3></center></td>
                                        </tr>
                                        <tr>
                                            <td colspan="4">Bill To:</td>
                                            <td colspan="3">
                                                <input type="text" name="bill_to" required="true" value="<?= $row->last_name . ' ' . $row->first_name ?>" readonly="true">
                                                <input type="hidden" name="root_id" id="root_id" value="<?= $row->root_id ?>">
                                                <input type="hidden" name="linking_id" id="linking_id" value="<?= $stu_id ?>">
                                            </td>
                                            <td colspan="2">Invoice Date:</td>
                                            <td colspan="5">
                                                <div class="input-append date-picker">
                                                    <input type="text" name="invoice_date" class="dateISO" required='true'>
                                                    <span class="add-on">
                                                        <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                    </span>
                                                </div>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td colspan="4">Program-Subject</td>
                                            <td colspan="3"><input type="text" name="prog_sub" value="<?= $row->program_name . '-' . $row->sub_name ?>" readonly="true"></td>
                                            <td colspan="2">Event Code:</td>
                                            <td colspan="5"><input type="text" name="event_code" value="<?= $row->event_code ?>" readonly="true"></td>
                                        </tr>
                                        <tr>
                                            <td colspan="4">Funding Type</td>
                                            <td colspan="13">
                                                <select name="funding_type">
                                                    <option value="0">No Funding</option>
                                                    <?php
                                                    $query_funding = "Select funding_id,funding_type from funding where deleted=0 and actived=1";
                                                    $result_funding = $dbh->query($query_funding);
                                                    $result_funding->setFetchMode(PDO::FETCH_OBJ);
                                                    while ($row_funding = $result_funding->fetch()) {
                                                        ?>
                                                        <option value="<?= $row_funding->funding_id ?>"><?= $row_funding->funding_type ?></option>
                                                        <?php
                                                    }
                                                    ?>
                                                </select>
                                            </td>
                                        </tr>
                                        <tr>
                                            <th>&nbsp;</th>
                                            <th>Date</th>
                                            <th>Time from</th>
                                            <th>Time to</th>
                                            <th>Hour(s)</th>
                                            <th>Fee type</th>
                                            <th>Price($)</th>
                                            <th>Attendance</th>
                                            <th>Subsidy(%)/Package discount($)</th>
                                            <th>&nbsp;</th>
                                            <th>Discount($)</th>
                                            <th>Not Charge?</th>
                                            <th>Amount($)</th>
                                        </tr>
                                        <?php
                                        //activity
                                        $query_activity = "Select DATE_FORMAT(act.date, '%W') as weekday,e.event_id,act.activity_id,act.start_time,act.end_time,act.payment,act.date
                        from activity as act,activity_stu_list as act_stu,event as e, lesson_attendance as la,student_grade as sg,grade as g,subject as sub,program as prog,
                        student as stu,lesson as l,event_lesson as el where act_stu.activity_id=act.activity_id and act.event_id=e.event_id and e.event_id=el.event_id and
                        el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id and stu.root_id=la.stu_linking_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                        and sub.subject_id=e.subject_id and prog.program_id=e.program_id and e.deleted=0 and e.actived=1 and act.deleted=0 and act.actived=1 and sg.deleted=0 and sg.actived=1 and g.deleted=0 and g.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                        and l.deleted=0 and l.actived=1 and el.deleted=0 and el.actived=1 and act_stu.invoice_gened=0 and act_stu.stu_linking_id='$row->root_id' and stu.linking_id='$stu_id' and l.date>='$from_date' and l.date<='$to_date' and e.event_id='$event_id' group by act_stu.stu_linking_id";
                                        $result_activity = $dbh->query($query_activity);
                                        $result_activity->setFetchMode(PDO::FETCH_OBJ);
                                        while ($row_activity = $result_activity->fetch()) {
                                            //$query_get_stu = "Select count(*) as no_of_stu from activity_stu_list where activity_id='$row_activity->activity_id'";
                                            //$result_get_stu = $dbh->query($query_get_stu);
                                            //$result_get_stu->setFetchMode(PDO::FETCH_OBJ);
                                            //$no_of_stu = $result_get_stu->fetch()->no_of_stu;
                                            //cal amount and hr
                                            $hour = (round((strtotime($row_activity->end_time) - strtotime($row_activity->start_time)) / 3600, 2));
                                            $price = $row_activity->payment * $hour;
                                            $amount = ($price * (1 - ((double) $percent / 100)));
                                            $discount = $price - ($amount);
                                            $total_price +=$price;
                                            $total_discount +=$discount;
                                            $total_amount +=$amount;
                                            $total_hr+=$hour;
                                            ?>
                                            <tr>
                                                <td><input type="checkbox" name="activity_id[]" id="activity_<?= $i ?>" value="<?= $row_activity->activity_id ?>" checked onclick="selected(this);"></td>
                                                <td><?= $row_activity->date . '(' . $row_activity->weekday . ')' ?><input type="hidden" name="date[]" value="<?= $row_activity->date ?>"></td>
                                                <td><?= $row_activity->start_time ?><input type="hidden" name="start_time[]" value="<?= $row_activity->start_time ?>"></td>
                                                <td><?= $row_activity->end_time ?><input type="hidden" name="end_time[]" value="<?= $row_activity->end_time ?>"></td>
                                                <td><?= $hour ?><input type="hidden" name="hours[]" value="<?= $hour ?>"></td>
                                                <td>Assessment/Test</td>
                                                <td><?= $price ?><input type="hidden" name="price[]" id='price_<?= $i ?>' value="<?= $price ?>"></td>
                                                <td>/</td><!--attendance-->
                                                <td>
                                                    <select name="type[]" style="width:100%;" id="type_<?= $i ?>" onchange="change_discount(this);" style="">
                                                        <option value="1" selected>Subsidy</option>
                                                        <option value="2">Package discount</option>
                                                        <!--<option value="3">Funding</option>-->
                                                    </select>
                                                </td>
                                                <td><input type="text" style="width:60px;" name="type_rate[]" id="typerate_<?= $i ?>" value="<?= $percent ?>" readonly='true' onblur="funding(this);"></td>
                                                <td><input type="text" style="width:90%;" name="discount[]" id="discount_<?= $i ?>" value="<?= $discount ?>" readonly='true'></td>
                                                <td><input type="checkbox" name="is_charge[]" id="charge_<?= $i ?>" onclick="not_charge(this);" value="1"></td>
                                                <td><input type="text" style="width:90%;" name="amount[]" id='amount_<?= $i ?>' value="<?= $amount ?>" readonly="true"></td>
                                            </tr>
                                            <?php
                                            $i++;
                                        }
                                    }
                                    /* $query2 = "Insert into invoice set createby=0,createdate=Now(),lastupby=0,lastupdate=Now(),actived=1,deleted=0,
                                      stu_linking_id='$row->root_id',code='$row->event_code',remarks=''";
                                      $result2 = $dbh->query($query2); */
                                    $query4 = "Select Max(invoice_id) as i from invoice where deleted=0";
                                    $result4 = $dbh->query($query4);
                                    $result4->setFetchMode(PDO::FETCH_OBJ);
                                    $row4 = $result4->fetch();
                                    $query5 = "Select count(*) as no_of_stu from lesson_attendance where lesson_id ='$row->lesson_id' and deleted=0 and actived=1 group by lesson_id";
                                    $result5 = $dbh->query($query5);
                                    $result5->setFetchMode(PDO::FETCH_OBJ);
                                    $row5 = $result5->fetch();

                                    //while ($row5 = $result5->fetch()) {
                                    $price = 0.0;
                                    $discount = 0.0;
                                    $amount = 0.0;
                                    if ($row->programme_fee_type != 3) {//3=free
                                        if ($row->programme_fee_type == 1) {//1=hour , 2=package
                                            if ($row5->no_of_stu > 1) {//group hr
                                                switch ($row->grade) {
                                                    case 'K2':
                                                    case 'K3':
                                                    case 'P1':
                                                    case 'P2':
                                                    case 'P3':
                                                    case 'P4':
                                                    case 'P5':
                                                    case 'P6':
                                                    case 'Y1':
                                                    case 'Y2':
                                                    case 'Y3':
                                                    case 'Y4':
                                                    case 'Y5':
                                                    case 'Y6':
                                                        $price+=$row->primary_group_price;
                                                        break;
                                                    case 'S1':
                                                    case 'S2':
                                                    case 'S3':
                                                    case 'S4':
                                                    case 'S5':
                                                    case 'S6':
                                                    case 'Y7':
                                                    case 'Y8':
                                                    case 'Y9':
                                                    case 'Y10':
                                                    case 'Y11':
                                                    case 'Y12':
                                                        $price+=$row->secondary_group_price;
                                                        break;
                                                }
                                            } else {
                                                switch ($row->grade) {//induva hr
                                                    case 'K2':
                                                    case 'K3':
                                                    case 'P1':
                                                    case 'P2':
                                                    case 'P3':
                                                    case 'P4':
                                                    case 'P5':
                                                    case 'P6':
                                                    case 'Y1':
                                                    case 'Y2':
                                                    case 'Y3':
                                                    case 'Y4':
                                                    case 'Y5':
                                                    case 'Y6':
                                                        $price+=$row->primary_individual_price;
                                                        break;
                                                    case 'S1':
                                                    case 'S2':
                                                    case 'S3':
                                                    case 'S4':
                                                    case 'S5':
                                                    case 'S6':
                                                    case 'Y7':
                                                    case 'Y8':
                                                    case 'Y9':
                                                    case 'Y10':
                                                    case 'Y11':
                                                    case 'Y12':
                                                        $price+=$row->secondary_individual_price;
                                                        break;
                                                }
                                            }
                                            $price = $price * (round((strtotime($row->end_time) - strtotime($row->start_time)) / 3600, 2));
                                            $amount = ($price * (1 - ((double) $percent / 100)));
                                            $discount = $price - ($amount);
                                        } else {//package
                                            switch ($row->grade) {
                                                case 'K2':
                                                case 'K3':
                                                case 'P1':
                                                case 'P2':
                                                case 'P3':
                                                case 'P4':
                                                case 'P5':
                                                case 'P6':
                                                case 'Y1':
                                                case 'Y2':
                                                case 'Y3':
                                                case 'Y4':
                                                case 'Y5':
                                                case 'Y6':
                                                    $price+=$row->primary_package_price;
                                                    break;
                                                case 'S1':
                                                case 'S2':
                                                case 'S3':
                                                case 'S4':
                                                case 'S5':
                                                case 'S6':
                                                case 'Y7':
                                                case 'Y8':
                                                case 'Y9':
                                                case 'Y10':
                                                case 'Y11':
                                                case 'Y12':
                                                    $price+=$row->secondary_package_price;
                                                    break;
                                            }
                                            $amount = (($price) * (1.0 - ((double) $percent / 100)));
                                            $discount = $price - ($amount);
                                        }
                                    }
                                    (double) $total_price +=$price;
                                    (double) $total_discount +=$discount;
                                    (double) $total_amount +=$amount;
                                    $total_hr+=(round((strtotime($row->end_time) - strtotime($row->start_time)) / 3600, 2));
                                    /* echo "<script>alert('".$price."')</script>";
                                      echo "<script>alert('".$discount."')</script>";
                                      echo "<script>alert('".$fee."')</script>"; */
                                    ?>
                                    <tr>
                                        <td><input type="checkbox" name="lesson_id[]" id="lesson_<?= $i ?>" value="<?= $row->lesson_id ?>" checked onclick="selected(this);"></td>
                                        <td><?= $row->date . '(' . $row->weekday . ')' ?><input type="hidden" name="date[]" value="<?= $row->date ?>"></td>
                                        <td><?= $row->start_time ?><input type="hidden" name="start_time[]" value="<?= $row->start_time ?>"></td>
                                        <td><?= $row->end_time ?><input type="hidden" name="end_time[]" value="<?= $row->end_time ?>"></td>
                                        <td><?= (round((strtotime($row->end_time) - strtotime($row->start_time)) / 3600, 2)) ?><input type="hidden" name="hours[]" value="<?= (round((strtotime($row->end_time) - strtotime($row->start_time)) / 3600, 2)) ?>"></td>
                                        <td>
                                            <?php
                                            if ($row->programme_fee_type == 1) {
                                                echo 'Hourly' . '<input type="hidden" name="fee_type[]" value="1">';
                                            } else {
                                                if ($row->programme_fee_type == 2) {
                                                    echo 'Package' . '<input type="hidden" name="fee_type[]" value="2">';
                                                } else {
                                                    echo 'Free' . '<input type="hidden" name="fee_type[]" value="3">';
                                                }
                                            }
                                            ?>
                                        </td>
                                        <td><?= $price ?><input type="hidden" name="price[]" id='price_<?= $i ?>' value="<?= $price ?>"></td>
                                        <td>
                                            <?php
                                            if ($row->attendance == 1) {
                                                echo 'Present';
                                                echo '<input type="hidden" name="attendance[]" value="1">';
                                            } else {
                                                echo 'Absent';
                                                echo '<input type="hidden" name="attendance[]" value="0">';
                                            }
                                            ?>
                                        </td>
                                        <td>
                                            <select name="type[]" style="width:100%;" id="type_<?= $i ?>" onchange="change_discount(this);">
                                                <option value="1" selected>Subsidy</option>
                                                <option value="2">Package discount</option>
                                                <!--<option value="3">Funding</option>-->
                                            </select>
                                        </td>
                                        <td><input type="text" style="width:60px;" name="type_rate[]" id="typerate_<?= $i ?>" value="<?= $percent ?>" readonly='true' onblur="funding(this);"></td>
                                        <td><input type="text" style="width:90%;" name="discount[]" id="discount_<?= $i ?>" value="<?= $discount ?>" readonly='true'></td>
                                        <td width="10%"><input type="checkbox" name="is_charge[]" id="charge_<?= $i ?>" onclick="not_charge(this);" value="1"></td>
                                        <td><input type="text" style="width:90%;" name="amount[]" id='amount_<?= $i ?>' value="<?= $amount ?>" readonly="true"></td>
                                    </tr>
                                    <?php
                                    $i++;
                                }
                            } else {
                                echo '<script>alert("GGGGGGGGGGGg")</script>';
                            }
                            ?>
                            <tr>
                                <td>&nbsp;</td>
                                <td>&nbsp;</td>
                                <td>&nbsp;</td>
                                <td>&nbsp;</td>
                                <td>Total hours:<?=$total_hr?></td>
                                <td>&nbsp;</td>
                                <td>Total price:<?= $total_price ?><input type="hidden" name="total_price" value='<?= $total_price ?>'></td>
                                <td>&nbsp;</td>
                                <td>&nbsp;</td>
                                <td>&nbsp;</td>
                                <td>Total discount:<input type='text' style="width:90%;" name='total_discount' id='total_discount' value='<?= $total_discount ?>' readonly='true'></td>
                                <td>&nbsp;</td>
                                <td>
                                    Total amount:<input type='text' style="width:90%;" name='total_amount' id='total_amount' value='<?= $total_amount ?>' readonly='true'>
                                    <input type="hidden" name="i" id="i" value="<?= $i ?>">
                                </td>
                            </tr>
                        </table>
                        <input type="button" value="Cancel" class="btn" onclick="window.history.go(-1);"/>
                        <input type="submit" name="generate" value="Generate" class="btn">
                    </form>
                    <?php
                } else {
                    ?>
                    <div>
                        <table border="0" width="100%">
                            <tr>
                                <td><h2>Generate Invoice</h2></td>
                            </tr>
                        </table>
                    </div>
                    <form action="" method="POST" class="form-inline">
                        <div class="pathways-search">
                            <table border="0">
                                <tr>
                                    <?php
                                    if (isset($_POST['search']) && $_POST['search'] == "Search In-House") {
                                        $from_date = $_POST['from_date'];
                                        $to_date = $_POST['to_date'];
                                        //$yr = $_POST['sem_yr'];
                                        ?>
                                                                                                                                                                                                <!--<td width="20%">
                                                                                                                                                                                                    Semaster_Year
                                                                                                                                                                                                    <select name="sem_yr">
                                        <?php
                                        $query12 = "Select Sem_yr as yr from student group by Sem_yr";
                                        $result12 = $dbh->query($query12);
                                        $result12->setFetchMode(PDO::FETCH_OBJ);
                                        while ($row12 = $result12->fetch()) {
                                            if ($row12->yr == $yr)
                                                print '<option value="' . $row12->yr . '" selected>' . $row12->yr . '</option>';
                                            else
                                                print '<option value="' . $row12->yr . '">' . $row12->yr . '</option>';
                                        }
                                        ?>
                                                                                                                                                                                                    </select>
                                                                                                                                                                                                </td>-->
                                        <td>Date From
                                            <div class="input-append date-picker" id="start_date">
                                                <input type="text" name="from_date" id="start_date_input" class="dateISO" required="true" value="<?= $from_date ?>">
                                                <span class="add-on">
                                                    <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                </span>
                                            </div>
                                        </td>
                                        <td>Date To
                                            <div class="input-append date-picker" id="end_date">
                                                <input type="text" name="to_date" class="dateISO" required="true" value="<?= $to_date ?>">
                                                <span class="add-on">
                                                    <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                </span>
                                            </div>
                                        </td>
                                        <?php
                                    }
                                    else {
                                        if (isset($_POST['search_sch']) && $_POST['search_sch'] == "Search In-School") {
                                            $from_date = $_POST['from_date'];
                                            $to_date = $_POST['to_date'];
                                            //$yr = $_POST['sem_yr'];
                                            ?>
                                                                                                                                                                                                <!--<td width="20%">
                                                                                                                                                                                                    Semaster_Year
                                                                                                                                                                                                    <select name="sem_yr">
                                            <?php
                                            /* $query12 = "Select Sem_yr as yr from student group by Sem_yr";
                                              $result12 = $dbh->query($query12);
                                              $result12->setFetchMode(PDO::FETCH_OBJ);
                                              while ($row12 = $result12->fetch()) {
                                              if ($row12->yr == $yr)
                                              print '<option value="' . $row12->yr . '" selected>' . $row12->yr . '</option>';
                                              else
                                              print '<option value="' . $row12->yr . '">' . $row12->yr . '</option>';
                                              } */
                                            ?>
                                                                                                                                                                                                    </select>
                                                                                                                                                                                                </td>-->
                                            <td>Date From
                                                <div class="input-append date-picker" id="start_date">
                                                    <input type="text" name="from_date" id="start_date_input" class="dateISO" required="true" value="<?= $from_date ?>">
                                                    <span class="add-on">
                                                        <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                    </span>
                                                </div>
                                            </td>
                                            <td>Date To
                                                <div class="input-append date-picker" id="end_date">
                                                    <input type="text" name="to_date" class="dateISO" required="true" value="<?= $to_date ?>">
                                                    <span class="add-on">
                                                        <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                    </span>
                                                </div>
                                            </td>
                                            <?php
                                        } else {
                                            ?>
                                                <!--<td width="20%">
                                                    Semaster_Year
                                                    <select name="sem_yr">
                                            <?php
                                            /* $curr_yr = date("Y");
                                              $curr_mon = date("M");
                                              echo "<script>alert('.$curr_yr.' '.$curr_mon.')</script>";
                                              $query12 = "Select Sem_yr as yr from student group by Sem_yr";
                                              $result12 = $dbh->query($query12);
                                              $result12->setFetchMode(PDO::FETCH_OBJ);
                                              while ($row12 = $result12->fetch()) {
                                              $year1 = explode("-", $row12->yr)[0];
                                              $year2 = explode("-", $row12->yr)[1];
                                              if ($curr_yr == $year1 && $curr_mon >= 9) {
                                              print '<option value="' . $row12->yr . '"selected>' . $row12->yr . '</option>';
                                              } else {
                                              if ($curr_yr == $year2 && $curr_mon < 9) {
                                              print '<option value="' . $row12->yr . '"selected>' . $row12->yr . '</option>';
                                              } else
                                              print '<option value="' . $row12->yr . '">' . $row12->yr . '</option>';
                                              }
                                              } */
                                            ?>
                                                    </select>
                                                </td>-->
                                            <td>
                                                Date From
                                                <div class="input-append date-picker" id="start_date">
                                                    <input type="text" name="from_date" id="start_date_input" class="dateISO" required="true">
                                                    <span class="add-on">
                                                        <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                    </span>
                                                </div>
                                            </td>
                                            <td>
                                                Date To
                                                <div class="input-append date-picker" id="end_date">
                                                    <input type="text" name="to_date" class="dateISO" required="true">
                                                    <span class="add-on">
                                                        <i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
                                                    </span>
                                                </div>
                                            </td>
                                            <?php
                                        }
                                    }
                                    ?>
                                    <td><input type="submit" name="search" class="btn" value="Search In-House"></td>
                                    <td><input type="submit" name="search_sch" class="btn" value="Search In-School"></td>
                                </tr>
                            </table>
                        </div>
                        <table border="0" class="table table-striped table-bordered table-hover table-condensed">
                            <thead>
                                <tr>
                                    <th>&nbsp;</th>
                                    <th>Name</th>
                                    <th>Event Code</th>
                                </tr>
                            </thead>
                            <?php
                            if (isset($_POST['search']) && $_POST['search'] == "Search In-House") {
                                $from_date = $_POST['from_date'];
                                $to_date = $_POST['to_date'];
                                //$yr = $_POST['sem_yr'];
                                $query = "Select e.event_id,e.event_code,e.school_id,l.lesson_id,l.start_time,l.end_time,l.date,stu.first_name,stu.last_name,stu.linking_id,stu.root_id,stu.New_stu_ID as id,e.programme_fee_type,e.program_id,e.subject_id,e.primary_individual_price,e.primary_group_price,e.secondary_individual_price,e.secondary_group_price,
                        e.primary_package_price,e.secondary_package_price,g.grade,la.attendance 
                        from event as e, lesson_attendance as la,student_grade as sg,grade as g,
                        student as stu,lesson as l,event_lesson as el where e.event_id=el.event_id and
                        el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id and stu.root_id=la.stu_linking_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                        and e.deleted=0 and e.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                        and l.deleted=0 and l.actived=1 and el.deleted=0 and el.actived=1 and la.invoice_gened=0 and e.school_id=0 and l.date>='$from_date' and l.date<='$to_date' group by stu.linking_id,e.event_code";
                                /* Select e.event_id,e.event_code,ss.subsidy_id,s.subsidy_type,s.percent,l.lesson_id,l.start_time,l.end_time,l.date,stu.first_name,stu.last_name,stu.linking_id,stu.root_id,
                                  e.programme_fee_type,e.program_id,e.subject_id,e.primary_individual_price,e.primary_group_price,e.secondary_individual_price,e.secondary_group_price,
                                  e.primary_package_price,e.secondary_package_price,e.is_free,g.grade,la.attendance from event as e, lesson_attendance as la,student_grade as sg,grade as g,
                                  student as stu,student_subsidy as ss,subsidy as s,lesson as l,event_lesson as el where e.event_id=el.event_id and
                                  el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and
                                  stu.linking_id=ss.stu_subsidy_id and ss.subsidy_id=s.Subsidy_ID and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                                  and e.deleted=0 and e.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                                  and ss.deleted=0 and ss.actived=1 and s.deleted=0 and s.actived=1 and l.deleted=0 and l.actived=1
                                  and el.deleted=0 and el.actived=1 and l.date>='$from_date' and l.date<='$to_date' and stu.New_stu_ID='$stu_id' and stu.Sem_yr='$yr'
                                  group by e.event_id"; */
                                /*
                                  Select e.event_id,e.event_code,l.lesson_id,l.start_time,l.end_time,l.date, e.programme_fee_type,e.program_id,e.subject_id,e.primary_individual_price,e.primary_group_price,e.secondary_individual_price,e.secondary_group_price,
                                  e.primary_package_price,e.secondary_package_price
                                  from event as e,lesson as l,event_lesson as el
                                  where e.event_id=el.event_id and el.lesson_id=l.lesson_id
                                 */
                                $result = $dbh->query($query); //get invoice data
                                $result->setFetchMode(PDO::FETCH_OBJ);
                                if ($result->rowcount() == 0) {
                                    print '<tr>';
                                    print '<td colspan="3"><b>No records</b></td>';
                                    print '</tr>';
                                } else {
                                    while ($row = $result->fetch()) {
                                        //if ($row->school_id == 0) {
                                        ?>
                                        <tr>
                                            <td>
                                                <input type="radio" name="id" value="<?= $row->event_id . '|' . $row->linking_id ?>" checked>
                                            </td>
                                            <td><?= $row->last_name . " " . $row->first_name . "(" . $row->id . ")" ?></td>
                                            <td><?= $row->event_code ?></td>
                                        </tr>
                                        <?php
                                        //}
                                    }
                                }
                                echo '</table><input type="submit" name="select" class="btn" value="Select">';
                            } else {
                                if (isset($_POST['search_sch']) && $_POST['search_sch'] == "Search In-School") {
                                    $from_date = $_POST['from_date'];
                                    $to_date = $_POST['to_date'];
                                    //$yr = $_POST['sem_yr'];
                                    $query = "(Select e.event_id,e.event_code,e.school_id,l.lesson_id,l.start_time,l.end_time,l.date,stu.first_name,stu.last_name,stu.linking_id,stu.root_id,stu.New_stu_ID as id,e.programme_fee_type,e.program_id,e.subject_id,e.primary_individual_price,e.primary_group_price,e.secondary_individual_price,e.secondary_group_price,
                                    e.primary_package_price,e.secondary_package_price,g.grade,la.attendance ,sch.school_id,sch.sch_name,sch.sch_ch_name
                                    from event as e, lesson_attendance as la,student_grade as sg,grade as g, school as sch,
                                    student as stu,lesson as l,event_lesson as el where e.event_id=el.event_id and
                                    el.lesson_id=l.lesson_id and l.lesson_id=la.lesson_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id and stu.root_id=la.stu_linking_id and sg.student_grade_id=stu.linking_id and sg.grade_id=g.grade_id
                                    and sch.school_id=e.school_id and e.deleted=0 and e.actived=1 and la.deleted=0 and la.actived=1 and stu.deleted=0 and stu.actived=1
                                    and l.deleted=0 and l.actived=1 and el.deleted=0 and el.actived=1 and la.invoice_gened=0 and e.school_id<>0 and l.date>='$from_date' and l.date<='$to_date' group by e.event_code)";
                                    $result = $dbh->query($query);
                                    $result->setFetchMode(PDO::FETCH_OBJ);
                                    if ($result->rowcount() == 0) {
                                        print '<tr>';
                                        print '<td colspan="3"><b>No records</b></td>';
                                        print '</tr>';
                                    } else {
                                        while ($row = $result->fetch()) {
                                            //if ($row->school_id != 0) {
                                            ?>
                                            <tr>
                                                <td>
                                                    <input type="radio" name="id" value="<?= $row->event_id . '|' . $row->school_id ?>" checked>
                                                </td>
                                                <td><?= $row->sch_ch_name . "(" . $row->sch_name . ")" ?></td>
                                                <td><?= $row->event_code ?></td>
                                            </tr>
                                            <?php
                                            //}
                                        }
                                    }
                                    echo '</table><input type="submit" name="select1" class="btn" value="Select">';
                                }
                            }
                            ?>
                    </form>
                    <?php
                }
            }
            ?>
        </div>
    </body>
</html>