<?php
ini_set('max_execution_time', 700); //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";

               

                $custid = "";

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

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

                }

                $msoac = "";

               

                 $name = "";

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

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

                }


                

                
                 $vcno = "0";

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

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

                }

                 $stb = "0";

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

                    $stb = mysqli_real_escape_string($con,$Row[2]);
                    //$stb=substr($stb, 0,16);

                }

                 $phone = "0";

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

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

                }

                $area = "0.00";

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

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

                }


                $lineman="uids";

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

                    //$lpayamt = mysqli_real_escape_string($con,$Row[8]);
                    $lineman=mysqli_real_escape_string($con,$Row[6]);   

                }


               


               

              // echo $name;
                $month="SEP";
                $year='2022';

                

                if (!empty($name)) {







                  $checkc=f(q("SELECT * FROM adduser WHERE custid='$custid'"));

                  $getarea=f(q("select * from tbl_area where arname='$area'"));
                  //$getpack=f(q("SELECT * FROM tbl_pack WHERE pname=''"))
                  $arid=$getarea['arid'];

                  if($getarea['arname']==''){

                    $inscom=mysqli_query($con,"INSERT INTO tbl_area SET arname='$area'");
                    $arid=mysqli_insert_id($con);
                    
                  }else{

                     $arid=$getarea['arid'];
                  }


                  $getline=f(q("select * from tbl_login where l_type=2 AND l_name='$lineman'"));
                  //$getpack=f(q("SELECT * FROM tbl_pack WHERE pname=''"))
                  $lineid=$getline['l_id'];
                //   if($lineid==''){

                //     $q=mysqli_query($con,"INSERT INTO tbl_login SET ")
                //   }

                  
                    $comp='GANAPATI CABLE & BROADBAND SERVICE';



                  $getmso=f(q("select * from tbl_mso where m_name='$mso'"));
                  //$getpack=f(q("SELECT * FROM tbl_pack WHERE pname=''"))
                 

                  if($getmso['m_name']==''){

                    $inscom=mysqli_query($con,"INSERT INTO tbl_mso SET m_name='$mso'");
                    $mid=mysqli_insert_id($con);
                    
                  }else{

                     $mid=$getmso['m_id'];
                  }

                  $getcomp=f(q("select * from tbl_company where c_name='$comp'"));
                  //$getpack=f(q("SELECT * FROM tbl_pack WHERE pname=''"))
                 

                  if($getcomp['c_name']==''){

                    $inscom=mysqli_query($con,"INSERT INTO tbl_company SET c_name='$comp',c_phone='1111111',c_mobile='111111',c_email='abc@gmail.com',c_emailalt='na',c_address='Kolkata',isgst='1'");
                    $compid=mysqli_insert_id($con);
                    
                  }else{

                     $compid=$getcomp['c_id'];
                  }








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

                     

                    $query = "INSERT INTO
                                     `adduser`
                                 SET

                             `name`='$name',

                             `custid`='$custid',

                             `address`='$address',

                             `phone`='$phone',
                             area_id='$arid',
                             area_name='$area',
                             price='$price',vc='$vcno',stb='$stb',pay_ch='$pay',is_cable=1,mnth='$month',yrs='$year',pexpiry='$enddt',actv=1,lpaydt='$lpaydt',lpayamt='$lpayamt',mso='$mid',sdhd='$stype',boxdate='$sdate',msoacno='$msoac',prevdue='$pdue',com_id='$compid'";

                   //echo $query;

                   // $result = mysqli_query($con,$query);
                   // $maxid=mysqli_insert_id($con);
                    if($result){

                        if($phone==''){

                            $phone='123456';

                        }
                         mysqli_query($con,"INSERT INTO tbl_login SET l_pass=md5('$password'),l_uid=md5('$luname'),l_name='$name',l_read='$luname',l_type='3',l_act='1',user_id='$maxid'");
                        }else{

                            $dupli=$dupli."<br>".$query;
                        }

                    


                  }else{



                    $query="UPDATE adduser SET phone='$phone',vc='$vcno',stb='$stb',area_id='$arid',line_ids='$lineid' WHERE custid='$custid'";
                    
                    echo $query;

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

                   





                  }

                

                    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='subscriber.php'> << back</a>"; } ?></div>

    

            <?php //echo $dupli?>





</body>

</html>