<?php
session_start();
ob_start();
include("../../pidbco/pidbjoin.php");
$sujpath="./";
require_once 'excel_reader2.php';


if(isset($_POST['frasubmit'])){

$excell = $_FILES['sname']['name'];

                    $rand1=md5(uniqid());
				    $extension = explode('.', $_FILES['sname']['name']);
					$extension = $extension[count($extension)-1];
					$photo1 = $rand1 . '.' . $extension;
					$tmp_photo = $_FILES['sname']['tmp_name'];
	                move_uploaded_file($tmp_photo, 'Bill/'.$photo1);


$data = new Spreadsheet_Excel_Reader('Bill/'.$photo1);
//$data = new Spreadsheet_Excel_Reader("example.xls");
//echo "Total Sheets in this xls file: ".count($data->sheets)."<br /><br />";

$html="<table border='1'>";
for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
{	
	if(count($data->sheets[$i]['cells'])>0) // checking sheet not empty
	{
		//echo "Sheet $i:<br /><br />Total rows in sheet $i  ".count($data->sheets[$i]['cells'])."<br />";
		for($j=2;$j<=count($data->sheets[$i]['cells']);$j++) // loop used to get each row of the sheet
		{ 
			$html.="<tr>";
			for($k=1;$k<=count($data->sheets[$i]['cells'][$j]);$k++) // This loop is created to get data in a table format.
			{
				$html.="<td>";
				$html.=$data->sheets[$i]['cells'][$j][$k];
				$html.="</td>";
			}
			$data->sheets[$i]['cells'][$j][1];
			$dom1 = str_replace("'", "", $data->sheets[$i]['cells'][$j][1]);
			$dom2 = $data->sheets[$i]['cells'][$j][2];
			$domine =$dom1.$dom2;
			//$regdate = $data->sheets[$i]['cells'][$j][3];
			$regdate2 = $data->sheets[$i]['cells'][$j][3];
			$exp = explode("/",$regdate2);
			$mon = $exp[1];
	        $regdate = $exp[2].'/'.$mon.'/'.$exp[0];   
			
			
			//$renewal = $data->sheets[$i]['cells'][$j][4];
			$renewal2 = $data->sheets[$i]['cells'][$j][4];
			$exp1 = explode("/",$renewal2);
			$mon1 = $exp1[1];
		    $renewal = $exp1[2].'/'.$mon1.'/'.$exp1[0]; 
			
			//$intim_dat = $data->sheets[$i]['cells'][$j][5];
			$intim_dat = $data->sheets[$i]['cells'][$j][5];
			$exp2 = explode("/",$intim_dat);
			$mon2 = $exp2[1];
		    $intim_dat = $exp2[2].'/'.$mon2.'/'.$exp2[0]; 
			
			
			$dom_cost = $data->sheets[$i]['cells'][$j][6];
			$remarks = str_replace("'", "", $data->sheets[$i]['cells'][$j][7]);
			$company = str_replace("'", "", $data->sheets[$i]['cells'][$j][8]);
			
			
			$contact = str_replace("'", "", $data->sheets[$i]['cells'][$j][9]);
			$mob = $data->sheets[$i]['cells'][$j][10];
			$email = $data->sheets[$i]['cells'][$j][11];
			$country = $data->sheets[$i]['cells'][$j][12];
			$state = $data->sheets[$i]['cells'][$j][13];
			$dist = $data->sheets[$i]['cells'][$j][14];
			$s_regdate = $data->sheets[$i]['cells'][$j][15];
			
			$s_expdat = $data->sheets[$i]['cells'][$j][16];
			$exp5 = explode("/",$s_expdat);
			$mon3 = $exp5[1];
			$s_expdat = $exp5[2].'/'.$mon3.'/'.$exp5[0];  
			
			$s_space = $data->sheets[$i]['cells'][$j][17];
			$s_rate = $data->sheets[$i]['cells'][$j][18];
			$server_rate = $s_space * $s_rate;  
			$s_remarks = str_replace("'", "", $data->sheets[$i]['cells'][$j][19]);
			
			$sq2 = mysqli_query($con,"SELECT MAX(`cid`) AS mxid FROM `p_customers2`");
            $rs2 = mysqli_fetch_array($sq2);
            $cust_id = $rs2['mxid']+1;
			
			$dat = date('Y-m-d');	
			
			$sq1 = mysqli_query($con, "INSERT INTO `p_customers2` (`cidentity` ,`cname` ,`ccontact` ,`cmob` ,`cemail` ,`ccountry` ,`cstate` ,
`cdistrict` ,`cstatus` ,`cdate`) VALUES ('$cust_id','$company', '$contact', '$mob', '$email', '$country', '$state', '$dist', '1', '$dat')");
			
			
			
			$sq4 = mysqli_query($con, "INSERT INTO `p_domain2` ( `dcust`, `d_domain`, `d_regdate`, `d_renewel`, `d_intimation`, `d_amnt`, `d_remarks`)
			 VALUES ('$cust_id', '$domine', '$regdate', '$renewal', '$intim_dat', '$dom_cost', '$remarks')");
			
			$sq5 = mysqli_query($con, "INSERT INTO `p_server2` (`s_cust`, `s_start`, `s_end`, `s_amnt`, `s_remarks`) 
			VALUES ('$cust_id', '$regdate', '$s_expdat', '$server_rate', '$s_remarks');");
			
			
			
			$html.="</tr>";
		}
	}
	
}

$html.="</table>";

$pageaddress1=$_SERVER['HTTP_REFERER'];
echo '<script type="text/javascript">alert("Student Data Uploaded Successfully");window.location.href="'.$pageaddress1.'";</script>';
 exit;

				
}
?>



  <form name="form2" id="form2" action="" method="post" enctype="multipart/form-data">	
               	                    <div class="col-lg-12">
                                       <div class="form-group">
                                         <input type="file" id="sname" name="sname" >
                                       </div>
                                    </div>
                                    <div class="col-lg-12">
                                       <div class="form-group">
                                          <input type="submit" class="btn btn-primary" name="frasubmit" id="frasubmit" value="Submit" style=" float: right;"  />
                                       </div>
                                   </div>
                                </form>