<?php
require_once("../config/config.php");

function unixstamp($excelDateTime) {
    $d = floor($excelDateTime); // seconds since 1900
    $t = $excelDateTime - $d;
    return ($d > 0) ? ( $d - 25569 ) * 86400 + $t * 86400 : $t * 86400;
}

//////////////// after hitting upload button
@extract($_POST);
if ($_POST['Submit'] == "Upload") {
    //// initialization
    $flag1 = TRUE;
    mysqli_autocommit($link1, false);
    $error_msg = "";

    if ($_FILES["attchfile"]["name"]) {
        require_once "../includes/simplexlsx.class.php";
        $xlsx = new SimpleXLSX($_FILES['attchfile']['tmp_name']);
        $billing_data = mysqli_fetch_assoc(mysqli_query($link1, "SELECT from_location,to_location,po_no FROM `billing_master` WHERE challan_no='" . $_POST['challan_no'] . "'"));
        list($cols) = $xlsx->dimension();
        foreach ($xlsx->rows() as $k => $r) {
            if ($k == 0 || $k == 1)
                continue; // skip first row 
            for ($i = 0; $i < count($k); $i++) {

                /// check excel row data

                if ($r[0] == '' && $r[1] == '' && $r[2] == '' && $r[4] == '') {
                    
                } else if ($r[0] == "EOF") {
                    $eof = "1";
                } else {

                    // Make Variable for each element of excel
                    $partcode = "";
                    $imei1 = "";
                    $imei2 = "";
                    $msg_partcode = "";
                    $msg_imei = "";
                    $smg = "";
                    $froml = $billing_data['from_location'];
                    $tol = $billing_data['to_location'];
                    $owner_no = $billing_data['to_location'];
                    $po_no = $billing_data['po_no'];
                    $flag = "Y";
                    // check partcode 
                    $part_code = mysqli_fetch_assoc(mysqli_query($link1, "select prod_code from billing_model_data where prod_code='" . $r[0] . "'"));
                    if ($part_code['prod_code'] != "") {
                        $partcode = $r[0];
                    } else {
                        $msg_partcode = "Product code not exit!";
                        header("Location:invoiceUploadImei.php?challan_no=" . $_POST['challan_no'] . "&msg=$msg_partcode" . $pagenav);
                        exit;
                    }

                   // check imei1 and imei2 	
					$imeidata = mysqli_fetch_assoc(mysqli_query($link1, "select id,owner_code from billing_imei_data where (imei1='" . $r[1] . "' or imei2='" . $r[1] . "' or imei1='" . $r[2] . "' or imei2='" . $r[2] . "')  and prod_code ='" . $partcode . "'   order by id desc"));
				
					if ($imeidata['id'] != "" && $imeidata['owner_code']=$froml) {
                        $imei1 = "" . $r[1];
             		   $imei2 = "" . $r[2];
                    } else {
                        $msg_imei = "IMEI Does not exist in database!";
                        header("Location:invoiceUploadImei.php?challan_no=" . $_POST['challan_no'] . "&msg=$msg_imei" . $pagenav);
                        exit;
                    }

                    // check imei1 and imei2  at prod_code

                    if ($partcode != "" && $imei1 != "" && $imei2 != "") {		
                        $owner_code1 =mysqli_query($link1, "select owner_code from billing_imei_data where prod_code='" . $partcode . "' and imei1='" . $r[1] . "' or  imei2='" . $r[2] . "' order by id desc");	
							$row1 = mysqli_fetch_array($owner_code1);								
                       			 	if(rtrim($row1['owner_code']," ")==$froml){		               
                            // insert imei into billing_imei_data and billing_imei
                  			$sql = "INSERT INTO billing_imei_data set from_location='" . $froml . "',to_location='" . $tol . "',owner_code='" . $owner_no . "',prod_code='" . $partcode . "',doc_no='" . $_POST['challan_no'] . "',imei1=" . $imei1 . ",imei2='" . $imei2 . "',flag='" . $flag . "'";
				
                         $result = mysqli_query($link1, $sql);                       
                            //// check if query is not executed
                            if (!$result) {
                                $flag1 = FALSE;
                                $msg = "2";
                            }else{
                                $msg='1';
                            }
                        } 
						else {
						echo "ffff";
                            $msg = '2';
                        }
						
                    } else {
                        $msg = '2';
                    }
                }
            }
        }
        if ($msg == '1') {
            ///// check both master and data query are successfully executed
            if ($flag1) {
                // move file into folder
                move_uploaded_file($_FILES["attchfile"]["tmp_name"], "../upload/bill_upload/" . $now . $_FILES["attchfile"]["name"]);
                $f_name = $now . $_FILES["attchfile"]["name"];
                // insert into upload file data
                mysqli_query($link1, "update billing_master set file_name='" . $f_name . "',imei_attach='Y' where challan_no='" . $_POST['challan_no'] . "'");
                mysqli_commit($link1);
                $msg = "File is uploaded Successfully!";
                header("Location:corporateInvoiceList.php?msg=$msg" . $pagenav);
                exit;
            } else {
                mysqli_rollback($link1);
                $msg = "File is not uploaded Properly.IMEI already exit!";
            }
        } elseif ($msg == '2') {
            $msg = "File is not uploaded Properly.IMEI not exit!";
            header("Location:invoiceUploadImei.php?challan_no=" . $_POST['challan_no'] . "&msg=$msg" . $pagenav);
           exit;
        } 
         mysqli_close($link1);
    }
}

?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title><?= siteTitle ?></title>
        <script src="../js/jquery.min.js"></script>
        <link href="../css/font-awesome.min.css" rel="stylesheet">
        <link href="../css/abc.css" rel="stylesheet">
        <script src="../js/bootstrap.min.js"></script>
        <link href="../css/abc2.css" rel="stylesheet">
        <link rel="stylesheet" href="../css/bootstrap.min.css">
        <link rel="stylesheet" href="../css/bootstrap-select.min.css">
        <script src="../js/bootstrap-select.min.js"></script>
        <link rel="stylesheet" href="../css/jquery.dataTables.min.css">
        <script type="text/javascript" src="../js/jquery.dataTables.min.js"></script>
        <script>
            $(document).ready(function() {
                $('#myTable').dataTable();
            });
            $(document).ready(function() {
                $("#frm1").validate();
            });
            // When the document is ready
            $(document).ready(function() {
                $('#billdate').datepicker({
                    format: "yyyy-mm-dd",
                    //startDate: "<?= $row['sale_date'] ?>",
                    //endDate: "<?= $today ?>",
                    todayHighlight: true,
                    autoclose: true
                });
            });
        </script>
        <script src="../js/frmvalidate.js"></script>
        <script type="text/javascript" src="../js/jquery.validate.js"></script>
        <script type="text/javascript" src="../js/common_js.js"></script>
        <link rel="stylesheet" href="../css/datepicker.css">
        <script src="../js/jquery-1.10.1.min.js"></script>
        <script src="../js/bootstrap-datepicker.js"></script>
        <script src="../js/fileupload.js"></script>
    </head>
    <body>
        <div class="container-fluid">
            <div class="row content">
                <?php
                include("../includes/leftnav2.php");
                ?>
                <div class="col-sm-9 tab-pane fade in active" id="home">
                    <h2 align="center"><i class="fa fa-upload"></i> Upload IMEI</h2><div style="display:inline-block;float:right"><a href="../templates/UPLOAD_IMEI.xlsx" title="Download Excel Template"><img src="../img/template.png" title="Download Excel Template"/></a></div><br/>
                    <div class="form-group"  id="page-wrap" style="margin-left:10px;">
                        <?php if ($_REQUEST['msg']) { ?><br>
                            <h4 align="center" style="color:#FF0000"><?= $_REQUEST['msg'] ?></h4>
                        <?php } ?>    
                        <form  name="frm1"  id="frm1" class="form-horizontal" action="" method="post"  enctype="multipart/form-data">
                            <div class="form-group">
                                <div class="col-md-12"><label class="col-md-4 control-label">Attach File<span class="red_small">*</span></label>
                                    <div class="col-md-4">
                                        <div class="input-group">
                                            <label class="input-group-btn">
                                                <span class="btn btn-primary">
                                                    Browse&hellip; <input type="file" name="attchfile" class="form-control required" required style="display:none;" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
                                                </span>
                                            </label>
                                            <input type="text" class="form-control" name="billfile"  id="billfile" readonly>
                                            <input type="hidden" name="challan_no" value="<?= $_GET['challan_no'] ?>">
                                        </div>
                                    </div>
                                    <div class="col-md-4" align="right"><span class="red_small">NOTE: Attach only <strong>.xlsx (Excel Workbook)</strong> file</span></div>
                                </div>
                            </div>
                            <div class="form-group">
                                <div class="col-md-12" align="center">
                                    <input title="Back" class="btn btn-primary" value="Back" onClick="window.location.href = 'corporateInvoiceList.php?<?= $pagenav ?>'" type="button">&nbsp;&nbsp;&nbsp;
                                    <input type="submit" class="btn btn-primary" name="Submit" id="save" value="Upload" title="" <?php if ($_POST['Submit'] == 'Update') { ?>disabled<?php } ?>>
                                </div>
                            </div> 
                        </form>
                    </div>
                </div>
            </div>
        </div>
        <?php
        include("../includes/footer.php");
        include("../includes/connection_close.php");
        ?>
    </body>
</html>