connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// File upload handling
if (isset($_FILES['excelFile'])) {
$file = $_FILES['excelFile']['tmp_name'];
$category = $_POST['textdate'];
echo $category;
// Load the spreadsheet
$spreadsheet = IOFactory::load($file);
$sheetNames = $spreadsheet->getSheetNames(); // Get all sheet names
// Prepare the SQL statement
// $stmt = $conn->prepare("INSERT INTO users (sheet_name, name, email) VALUES (?, ?, ?)");
$mmonth=substr($category,0,2);
$myear=substr($category,2,4);
$md=0;
if ($mmonth === "01" || $mmonth === "03" || $mmonth === "05" || $mmonth === "07" || $mmonth === "08" || $mmonth === "10" || $mmonth === "12") {
$mm = 37;
$md=31;
}
if ($mmonth === "04" || $mmonth === "06" || $mmonth === "09" || $mmonth === "11") {
$mm = 36;
$md=30;
}
if ($mmonth === "02") {
$mm = ($myear % 4 === 0) ? 35 : 34; // Leap year check
$md = ($myear % 4 === 0) ? 29 : 28; // Leap year check
}
$sdt=$myear.'-'.$mmonth.'-'.'01';
$ldt=$myear.'-'.$mmonth.'-'.$md;
$sql="delete from ATTENEMP.excel_table where tran_date between '$sdt' and '$ldt'";
$conn->query($sql);
// Loop through each sheet
foreach ($sheetNames as $index => $sheetName) {
$sheet = $spreadsheet->getSheet($index);
$rows = $sheet->toArray();
echo "Sheet Name: " . $sheetName . "
";
// Skip the first row (header)
array_shift($rows);
// print_r($rows); // or use var_dump($rows);
$r = 0; // Initialize row counter
$pd=1;
$sql="INSERT INTO ATTENEMP.excel_table (sheet_name, tran_date, open_bale,open_weight, recv_bale,recv_weight,
issue_bale,issue_weight, close_bale,close_weight) values ";
// Insert data row by row
$norows = count($rows);
if ($norows>20) {
$rw1=$rows[7][1];
echo '1st rows val='.$rw1."
";
$fn=0;
if ($rw1==1) {
foreach ($rows as $row) {
if ($r >= 7 && $r <= $mm) {
$mnull=$row[1];
$name = isset($row[0]) ? $row[0] : 'N/A';
$email = isset($row[1]) ? $row[1] : 0;
if ($email==0) {
$email=$pd+1;
}
echo $email;
$da=strval($email);
if (strlen($da)==1) {
$da='0'.$da;
}
$sheetname= $sheetName;
$tdate= $myear.'-'.$mmonth.'-'.$da;
$opbal = isset($row[3]) && is_numeric($row[3]) ? $row[3] : 0;
$opwt= isset($row[4]) && is_numeric($row[4]) ? $row[4] : 0;
$recvbal= isset($row[6]) ? $row[6] : 0;
$recvwt= isset($row[7]) ? $row[7] : 0;
$issuebal=isset($row[10]) ? $row[10] : 0;
$issuewt=isset($row[11]) ? $row[11] : 0;
$closbal=isset($row[14]) ? $row[14] : 0;
$closewt=isset($row[15]) ? $row[15] : 0;
$issuebal= isset($issuebal) && is_numeric($issuebal) ? $issuebal : 0;
$issuewt=isset($issuewt) && is_numeric($issuewt) ? $issuewt : 0;
$issuewt = isset($issuewt) && $issuewt !== '#REF!' ? $issuewt : 0;
$recvwt = isset($recvwt) && $recvwt !== '#REF!' ? $recvwt : 0;
$closbal = isset($closbal) && $closbal !== '#REF!' ? $closbal : 0;
$closewt = isset($closewt) && $closewt !== '#REF!' ? $closewt : 0;
$fn++;
echo $tdate.'-'.$opbal;
$pd=$email;
$sql=$sql."('$sheetname','$tdate',$opbal,$opwt,$recvbal,$recvwt,$issuebal,$issuewt,$closbal,$closewt),";
echo "Row: $r - Name: $mnull, Email: $email. mdate : $tdate
";
}
$r++; // Correct increment
}
if ($fn>0) {
$newsql = substr($sql, 0, -1);
echo $newsql;
$conn->query($newsql);
}
}
}
}
echo "Data imported successfully!";
$conn->close();
echo "";
}
?>