<?php

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[0])) {

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

                }
                $acno = "";

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

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

                }

                $prevdue = "";

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

                    $prevdue = round(mysqli_real_escape_string($con,$Row[11]),2);

                }

                $amtpaid = "";

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

                    $amtpaid = mysqli_real_escape_string($con,$Row[13]);

                }

                $paydt = "";

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

                    $paydt = mysqli_real_escape_string($con,$Row[16]);
                    //$paydt=date("Y-m-d",strtotime($paydt));
                        if($paydt=='null'){

                            $paydt=date('Y-m-d');
                        }

                        echo "Pay date".$paydt;

                }

                $paymd = "";

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

                    $paymd = mysqli_real_escape_string($con,$Row[17]);

                }

                $mnth = "";

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

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

                }

                $yrs = "";

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

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

                }


                $linemanname = "";

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

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

                }


                

                

                if (!empty($acno)) {


                    //echo "Inserted";

                    $lnarid=$_POST['lnarid'];
                    $strtdt=$paydt;
                    $enddt=$_POST['enddt'];
                    // $mnth=$_POST['mnth'];
                    // $yrs=$_POST['yrs'];
                    $custid=$acno;	
                    $csadded=$_SESSION['murgibhai'];	
                    $txnid = substr(hash('sha256', mt_rand() . microtime()), 0, 20);
                    $postpaid=0;
                    //$amtpaid=$_POST['amtpaid'];
                    $getline=f(q("SELECT * FROM tbl_login WHERE l_name='$linemanname' AND l_type=2"));
                    $lineman=$getline['l_id'];
                    //$getlog=f(q("SELECT * FROM tbl_login WHERE l_id='$lineman'"));
                    $ltype=$getline['l_type'];
                    $paymodes=$paymd;
                    $disc= 0;
                    $bl_remarks='na';
                    $linemanuid=$getline['l_uid'];
            
                    
                    
            
                    $checkexpiry=f(q("SELECT * FROM adduser WHERE custid='$custid'"));
            
                    $care=$checkexpiry['area_id'];
                    $blcid=$checkexpiry['id'];






            $getmax=f(q("SELECT MAX(blid) as maxid FROM cbill WHERE bl_cid='$blcid' AND bl_due>0"));
			
                    //echo "SELECT MAX(blid) as maxid FROM cbill WHERE bl_cid='$blcid' AND bl_due>0";
			$maxid=$getmax['maxid'];
			$blid=$maxid;
			$getbilld=f(q("SELECT * FROM cbill WHERE blid='$blid'"));
			$prepay=$getbilld['bl_payamt'];
			$totpay=floatval($prepay)+floatval($amtpaid);
            $bl_amt=floatval($prevdue)+floatval($getbilld['bl_amt']);
			$dues=round((floatval($prevdue)+floatval($getbilld['bl_amt']))-floatval($totpay),2);
                    //echo $blid;

					if(floatval($blid)>0){
						$getbils=f(q("SELECT * FROM cbill WHERE blid='$blid'"));
						$txnid=$getbils['bl_trn'];
       //echo "UPDATE cbill SET bl_amt='$bl_amt',bl_payamt='$totpay',bl_stat=1,bl_due='$dues',bl_paymd='$paymodes',bl_paydt='$strtdt',bl_disc='$disc',bl_remarks='$bl_remarks',bl_prevdue='$prevdue' WHERE blid='$blid'";
		$result=mysqli_query($con,"UPDATE cbill SET bl_amt='$bl_amt',bl_payamt='$totpay',bl_stat=1,bl_due='$dues',bl_paymd='$paymodes',bl_paydt='$strtdt',bl_disc='$disc',bl_remarks='$bl_remarks',bl_prevdue='$prevdue' WHERE blid='$blid'");

			
			if($result){

				$gstval='18';
						$gstamt=floatval($amtpaid)*(18/118);
				$inspay=mysqli_query($con,"INSERT INTO tbl_billpay SET pay_date='$strtdt',pay_amt='$amtpaid',pay_bill='$blid',pay_by='$paymodes',pay_user='$linemanuid',pay_rmrk='$bl_remarks'");			
				$instrn=mysqli_query($con,"INSERT INTO tbl_trans SET trn_ac='$blcid',trn_name='$checkexpiry[name]',trn_amt='$amtpaid',trn_date='$strtdt',trn_type='MONTHLY BILL',trn_gst=1,trn_grate='18',trn_gamt='$gstamt',trn_memo='Bill Amount received for $checkexpiry[name]',trn_billid='$blid'");
				
				
			}




                  

                

                    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>