<?php
ini_set('max_execution_time', 1200); //300 seconds = 5 minutes
include "top.php";

require_once('excel/php-excel-reader/excel_reader2.php');

require_once('excel/SpreadsheetReader.php');



if (isset($_POST["import"]))

{

    

   // $users=$_SESSION['bigapple']; 

  $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

  

  if(in_array($_FILES["file"]["type"],$allowedFileType)){



        $targetPath = 'image/'.$_FILES['file']['name'];

        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

        

        $Reader = new SpreadsheetReader($targetPath);

        

        $sheetCount = count($Reader->sheets());

        echo $sheetCount;

        for($i=0;$i<$sheetCount;$i++)

        {

            

            $Reader->ChangeSheet($i);

            

            foreach ($Reader as $Row)

            {

                //echo "Hello";

                $name = "";

                if(isset($Row[1])) {

                    $name = mysqli_real_escape_string($con,$Row[1]);

                }

                $bdate = "";

                if(isset($Row[2])) {

                    $bdate = mysqli_real_escape_string($con,$Row[2]);

                }
                 $acno = "";

                if(isset($Row[3])) {

                    $acno = mysqli_real_escape_string($con,$Row[3]);

                }

                
                 $stdate = "";

                if(isset($Row[4])) {

                    $stdate = mysqli_real_escape_string($con,$Row[4]);

                }

                 $enddate = "";

                if(isset($Row[5])) {

                    $enddate = mysqli_real_escape_string($con,$Row[5]);

                }

                $chp = "";

                if(isset($Row[5])) {

                    $chp = mysqli_real_escape_string($con,$Row[5]);

                }

                $month = "";

                if(isset($Row[6])) {

                    $month = mysqli_real_escape_string($con,$Row[6]);

                }

                $year = "";

                if(isset($Row[7])) {

                    $year = mysqli_real_escape_string($con,$Row[7]);

                }

                 $total = "";

                if(isset($Row[8])) {

                    $total = mysqli_real_escape_string($con,$Row[8]);

                }

                 $apaid = "";

                if(isset($Row[9])) {

                    $apaid = mysqli_real_escape_string($con,$Row[9]);

                }

                 $pdt = "";

                if(isset($Row[10])) {

                    $pdt = mysqli_real_escape_string($con,$Row[10]);

                }


                 $dues = "";

                if(isset($Row[11])) {

                    $dues = mysqli_real_escape_string($con,$Row[11]);

                }

              // echo $name;
                // $month="Oct";
                // $year='2020';

                

                if (!empty($name)) {
                 // echo "SELECT * FROM adduser WHERE custid='$acno'";

                  $checkexpiry=f(q("SELECT * FROM adduser WHERE custid='$acno'"));
                  $getmax=f(q("SELECT MAX(blid) as maxid FROM cbill"));
          

                  $maxid=$getmax['maxid'];
                  $maxid=floatval($maxid)+1;
                  $custid=$checkexpiry['id'];
                  $bldt=$bdate;
                  $currentb=$total;
                  $amtpaid=$apaid;
                  $bl_paydt=$pdt;
                  $txnid = substr(hash('sha256', mt_rand() . microtime()), 0, 20); 
                  $csadded=$_SESSION['murgibhai']; 
                  $expirydate=$enddate;

                  //$dues=
                 $sumdue=f(q("SELECT bl_amt FROM cbill WHERE bl_cid='$custid'  ORDER BY blid DESC LIMIT 1"));
                  $tdue=$sumdue['bl_amt'];

                  $getdues=f(q("SELECT bl_payamt FROM cbill WHERE bl_cid='$custid'  ORDER BY blid DESC LIMIT 1"));
                  $totpay=$getdues['bl_payamt'];


                  $tdue=floatval($tdue)-floatval($totpay);

                  if($tdue==''){
                    $tdue=0;
                  }
                  if($checkexpiry['is_cable']=='1'){

                    $bl_tp='1';
                  }else{

                    $bl_tp='0';
                  }


                  $checkc=f(q("SELECT * FROM tbl_pack WHERE pname='$name'"));

                  //$getarea=f(q("select * from tbl_area where arname='$area'"));



                  if($checkc['pname']==''){

                    if($bl_paydt=='.'){

                      $bl_paydt='2020-01-01';

                      $query="INSERT INTO cbill SET bl_cid='$custid',bl_dt='$bldt',bl_pid='0',bl_no='$maxid',bl_cname='$checkexpiry[name]',bl_address='$checkexpiry[address]',bl_phone='$checkexpiry[phone]',bl_paych='$checkexpiry[pay_ch]',bl_amt='$currentb',bl_payamt='$amtpaid',bl_stat='0',bl_paydt='$bl_paydt',bl_paymd='CASH',bl_trn='$txnid',bl_payuid='NA',bl_due='$dues',bl_prevdue='$tdue',bl_user='$csadded',bl_expiry='$expirydate',bl_tp='$bl_tp',bl_month='$month',bl_year='$year',bl_stdt='$stdate'";


                    }else{

                        $query="INSERT INTO cbill SET bl_cid='$custid',bl_dt='$bldt',bl_pid='0',bl_no='$maxid',bl_cname='$checkexpiry[name]',bl_address='$checkexpiry[address]',bl_phone='$checkexpiry[phone]',bl_paych='$checkexpiry[pay_ch]',bl_amt='$currentb',bl_payamt='$amtpaid',bl_stat='1',bl_paydt='$bl_paydt',bl_paymd='CASH',bl_trn='$txnid',bl_payuid='NA',bl_due='$dues',bl_prevdue='$tdue',bl_user='$csadded',bl_expiry='$expirydate',bl_tp='$bl_tp',bl_month='$month',bl_year='$year',bl_stdt='$stdate'";
                    }

                   

                   //echo $query;

                    $result = mysqli_query($con,$query);

                    if($result){

                        $upcom=mysqli_query($con,"UPDATE adduser SET mnths='$month',yrs='$year' WHERE id='$custid'");

                    }

                  }

                

                    if (! empty($result)) {

                        $type = "success";

                        $message = "Excel Data Imported into the Database";

                    } else {

                        $type = "error";

                        $message = "Problem in Importing Excel Data".$result;

                    }

                }

             }

        

         }

  }

  else

  { 

        $type = "error";

        $message = "Invalid File Type. Upload Excel File.";

  }

}

?>



<!DOCTYPE html>

<html>    

<head>

<style>    

body {

	font-family: Arial;

	width: 550px;

}



.outer-container {

	background: #F0F0F0;

	border: #e0dfdf 1px solid;

	padding: 40px 20px;

	border-radius: 2px;

}



.btn-submit {

	background: #333;

	border: #1d1d1d 1px solid;

    border-radius: 2px;

	color: #f0f0f0;

	cursor: pointer;

    padding: 5px 20px;

    font-size:0.9em;

}



.tutorial-table {

    margin-top: 40px;

    font-size: 0.8em;

	border-collapse: collapse;

	width: 100%;

}



.tutorial-table th {

    background: #f0f0f0;

    border-bottom: 1px solid #dddddd;

	padding: 8px;

	text-align: left;

}



.tutorial-table td {

    background: #FFF;

	border-bottom: 1px solid #dddddd;

	padding: 8px;

	text-align: left;

}



#response {

    padding: 10px;

    margin-top: 10px;

    border-radius: 2px;

    display:none;

}



.success {

    background: #c7efd9;

    border: #bbe2cd 1px solid;

}



.error {

    background: #fbcfcf;

    border: #f3c6c7 1px solid;

}



div#response.display-block {

    display: block;

}

</style>

</head>



<body>

    <h2>Import Users Via Excel File</h2>

    

    <div class="outer-container">

        <form action="" method="post"

            name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">

            <div>

                <label>Choose Excel

                    File</label> <input type="file" name="file"

                    id="file" accept=".xls,.xlsx">

                <button type="submit" id="submit" name="import"

                    class="btn-submit">Import</button>

        

            </div>

        

        </form>

        

    </div>

    <div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; echo "<a href='index.php'> << back</a>"; } ?></div>

    

         





</body>

</html>