﻿<!DOCTYPE html>
<html lang="en">

<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="stylesheet" href="css/bootstrap.min.css">
	<link rel="stylesheet" href="mystyle.css">

	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
	<style>

	</style>

	<title>Indentor Customer Outstanding Details</title>

</head>

<?php
// Initialize the session
session_start();

// Check if the user is logged in, if not then redirect him to login page
if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 1800)) {
	// last request was more than 30 minutes ago
	session_unset();     // unset $_SESSION variable for the run-time 
	session_destroy();   // destroy session data in storage
}
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp
if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true) {
	header("location: index.php");
	exit;
}
?>

<body>




	<div class="container col-md-10 col-sm-10 ">
		<h2 class="index1"><img class="logo" width="90" height="125" src="spblogo.jpeg"><br>Welcome to <?php echo htmlspecialchars($_SESSION["mainname"]); ?> Branch Application Login Unit : Tirunelveli</h2>
	</div>
	<div class="container-well col-6 col-md-2 col-sm-2 ">
		<h2 class="index2">
			<blink> <button type="button" class="btn btn-danger">LIVE</button></blink></u>
		</h2>



	</div>
	<div class="container  col-md-12 col-sm-12 ">
		<a href="welcome.php" class="btn btn-success ml-3">Menu</a>
		<a href="logout.php" class="btn btn-danger ml-3">Sign Out</a>

	</div>

	<center>


		<div class="container col-md-12 col-sm-12 ">
			<h3 class="style1">SESHASAYEE PAPER AND BOARDS LIMITED Tirunelveli</h3>


			<h4 class="style1">Indentor Customer Outstanding Details </h4> &nbsp;

		</div>
		<div class="container col-md-12 col-sm-12">
			<center>
				<form method="POST">

					<label for="pet" 'font-size: 12px'>From Indr</label>
					<input size="10" type="text" value="1001" name="pet" class="form-inline" id="pet" style="font-size : 15px; height:30px;width:150px">
					<label for="sut" 'font-size: 12px'>To Indr</label>
					<input size="10" type="text" value="4999" name="sut" class="form-inline" id="sut" style="font-size : 15px; height:30px;width:150px"></br></br>


					<input type="submit" value="Get Data" name="submit">
				</form>
			</center>
		</div>&nbsp;
		<div class="container col -8 col-sm-8">
			<br>
			<!-- <input type="button" id="btnExport" value="Excel" onclick="Export()" /> -->

			<button id="export">Export to excel</button>

			<input type="button" id="btnExport" value="Print" onclick="imprimir()" />
		</div>
		<div class="container col-4 col-sm-4">
			<br>
		</div>

		<div class="container col-5 col-sm-12">
			<center>
				<table id="tblCustomers" class='table table-striped table-fit table-hover table-bordered '>

					<tr>

						<TH>Indr_Code </TH>
						<TH> Indr_Nmae </TH>
						<TH> Cust_Code </TH>
						<TH> Cust_Name </TH>


						<TH> DOC </TH>
						<TH> DOC NO </TH>
						<TH> SL NO </TH>
						<TH> DOC DT </TH>

						<TH> DUE DT </TH>
						<TH> 1-30 DAYS </TH>
						<TH> 31-45 DAYS </TH>
						<TH> 46-60 DAYS </TH>
						<TH> 61-90 DAYS </TH>
						<TH> > 90 DAYS </TH>
						<TH> TOTAL </TH>
						<TH> CN ADV DEP </TH>
					</tr>

			</center>
			<?php

			if (isset($_POST['submit'])) {
				include('connaws.php');
				$pet = $_POST['pet'];
				$sut = $_POST['sut'];


				$sql = "SELECT FATBB_IND_CODE INDR_CODE,
				               MSMSA_IND_NAME INDR_NAME,
							   concat(FATBB_IND_CODE, FATBB_SUB_CODE) as CUST_CODE,
							   FATBB_SUB_CODE CUST_CODE1,
							   FAMRF_NAME CUST_NAME,
                 FATBB_DOC_NO DOC_NO,
				 fatbb_slno SLNO,
	             DATE_FORMAT(fatbb_doc_dt,'%Y/%m/%d') DOC_DT, 
                 DATE_FORMAT(fatba_due_date ,'%Y/%m/%d') DUE_DT,
				 IF (FATBB_DOC_TYPE in ('34','37','38'),(select FAMGC_ABBR from FAMGC_NTYPE where FAMGC_NOTE_CODE = FATBA_SUB_TYPE),FAMAE_ABBR) AS DOC,
				 

                  			   IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(-1 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(31-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0)  30DAYS, 
				 -- 
                 IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(30 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(46-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0)  45DAYS, 
				 -- 
                                 IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(45 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(61-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0)  60DAYS,
				 -- 

                IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(60 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(91-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0)  90DAYS,
				 -- 

                IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(90 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END,0)  GT90DAYS, 
				 -- 
				 
				 IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(-1 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(31-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0)+
                 IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(30 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(46-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0)+
                                 IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(45 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(61-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0) +

                IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(60 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                  CASE sign(91-(DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN 1 THEN 
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END ELSE 0 END,0) +

                IFNULL(
                  CASE fatbb_if_dr_cr WHEN 'D' THEN
                  CASE sign(90 - (DATEDIFF(FATBA_PROCESS_DT,fatbb_doc_dt))) WHEN -1 THEN
                 IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) ELSE 0 END ELSE 0 END,0) TOT,
				 -- 
				 
                 ifnull( CASE fatbb_if_dr_cr when 'C' THEN IFNULL(fatbb_tran_amt,0)-IFNULL(FATBB_AMT_PROCESSED,0) else 0 end,0) CNADVDEP

                 From 
				 FATBB_REPLIC_TN,
				 FATBA_REPLIC_TN,  
				  INT_MSMSA_INDTOR_TVL,
				 FAMRF_PARTY_ERD,
				 STATEINDRNEW_TVL,
				 BRANCH_USER_MAST_TVL,	
				 FAMAE_DOCUMENT	
				 
                 WHERE 
				      USERNAME = '" . $_SESSION['username'] . "' 
			     AND indr_group = BUM_GRP 
				 and indr_code = MSMSA_IND_CODE
				 AND MSMSA_IND_CODE between '$pet' and '$sut' 
				 AND MSMSA_IND_CODE = FATBB_IND_CODE 
				 AND FAMRF_PARTY_CODE = FATBB_SUB_CODE
                 AND FATBA_SEC_CODE=5  AND ifnull(FATBA_IF_CANCELLED, 'N')='N' 
                 AND ((FATBB_DOC_TYPE=11  AND FATBB_POST_STAT NOT IN ('C')) 
                 OR (FATBB_DOC_TYPE=11  AND FATBB_IF_DR_CR='D') OR FATBB_DOC_TYPE<>11) 
                 AND ((FATBB_DOC_TYPE=12 AND FATBB_POST_STAT='C') OR (FATBB_DOC_TYPE=12
                 AND DATE_FORMAT(FATBB_DOC_DT, '%d/%m/%Y') <=DATE_FORMAT('31/03/1997', '%d/%m/%Y')) OR FATBB_DOC_TYPE<>12) 
               --  AND DATE_FORMAT(FATBB_DOC_DT, '%d/%m/%Y') <='07/01/2022'
                 AND (IFNULL(FATBB_TRAN_AMT, 0)-IFNULL(FATBB_AMT_PROCESSED, 0))>0.0 
                 AND FATBB_ACC_HD IN (181401, 181409, 181419,181420) 
                 AND FATBB_DOC_NO NOT IN (2010020033,2011030867)
				and FATBB_DOC_TYPE != '54'
				 and FATBB_SUB_CODE BETWEEN 'A00000' AND 'Z99999' 
	             AND  ((FATBA_SEC_CODE=FATBB_SEC_CODE)
                 AND (FATBA_DOC_TYPE=FATBB_DOC_TYPE) 
                 AND (FATBA_DOC_NO=FATBB_DOC_NO))
                 and FATBA_DOC_TYPE = FAMAE_DOC_TYPE
                 ORDER BY 1,3";

				$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

				if ($result->num_rows > 0) {

					$data = array();

					while ($row = mysqli_fetch_assoc($result)) {
						$data[] = $row;
					} //WHILE EN DS HERE

					mysqli_close($conn);
					//echo json_encode($data);

					$icode = array();
					$iname = array();

					$ccode = array();
					$cname = array();

					$gtotal = new stdClass;
					$gtotal->A30DAYS = 0;
					$gtotal->A45DAYS = 0;
					$gtotal->A60DAYS = 0;
					$gtotal->A90DAYS = 0;
					$gtotal->GT90DAYS = 0;
					$gtotal->TOT = 0;
					$gtotal->CNADVDEP = 0;

					foreach ($data as $index => $a) {

						$ic = '';
						$in = '';

						$cc = '';
						$cn = '';

						if (!in_array($a['CUST_CODE'], $ccode)) {

							$cc = $ccode[] = $a['CUST_CODE'];

							$custtotal = new stdClass;
							$custtotal->A30DAYS = 0;
							$custtotal->A45DAYS = 0;
							$custtotal->A60DAYS = 0;
							$custtotal->A90DAYS = 0;
							$custtotal->GT90DAYS = 0;
							$custtotal->TOT = 0;
							$custtotal->CNADVDEP = 0;
						}	//customer $cc if stmt ends here

						if (!in_array($a['CUST_NAME'], $cname)) {

							$cn = $cname[] = $a['CUST_NAME'];
						}

						if (!in_array($a['INDR_CODE'], $icode)) {

							$ic = $icode[] = $a['INDR_CODE'];
							//$indrtotal -> A30DAYS=0;
						}
						if (!in_array($a['INDR_NAME'], $iname)) {

							$in = $iname[] = $a['INDR_NAME'];
						}


						$custtotal->A30DAYS += floatval($a['30DAYS']);
						$custtotal->A45DAYS += floatval($a['45DAYS']);
						$custtotal->A60DAYS += floatval($a['60DAYS']);
						$custtotal->A90DAYS += floatval($a['90DAYS']);
						$custtotal->GT90DAYS += floatval($a['GT90DAYS']);
						$custtotal->TOT += floatval($a['TOT']);
						$custtotal->CNADVDEP += floatval($a['CNADVDEP']);


						printf(
							' <tr>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td>
				 <td>%s</td> ',
							$a['INDR_CODE'],
							$a['INDR_NAME'],
							$a['CUST_CODE1'],
							$a['CUST_NAME'],

							$a['DOC'],
							$a['DOC_NO'],
							$a['SLNO'],
							$a['DOC_DT'],

							$a['DUE_DT'],
							$a['30DAYS'],
							$a['45DAYS'],
							$a['60DAYS'],
							$a['90DAYS'],
							$a['GT90DAYS'],
							$a['TOT'],
							$a['CNADVDEP']

						);


						if (($index < count($data) - 1 && $ccode[count($ccode) - 1] != $data[$index + 1]['CUST_CODE']) or
							$index == count($data) - 1
						) {

							printf('
						<tr style="background-color:#bfcce0">
							<td colspan=8>&nbsp;</td>
							<td colspan=1>Customer Total</td>
							<td>%s</td>
							<td>%s</td>
							<td>%s</td>
							<td>%s</td>
							<td>%s</td>
							<td>%s</td>
							<td>%s</td>
							
						</tr>', $custtotal->A30DAYS, $custtotal->A45DAYS, $custtotal->A60DAYS, $custtotal->A90DAYS, $custtotal->GT90DAYS, $custtotal->TOT, $custtotal->CNADVDEP);

							$indrtotal->A30DAYS += floatval($custtotal->A30DAYS);
							$indrtotal->A45DAYS += floatval($custtotal->A45DAYS);
							$indrtotal->A60DAYS += floatval($custtotal->A60DAYS);
							$indrtotal->A90DAYS += floatval($custtotal->A90DAYS);
							$indrtotal->GT90DAYS += floatval($custtotal->GT90DAYS);
							$indrtotal->TOT += floatval($custtotal->TOT);
							$indrtotal->CNADVDEP += floatval($custtotal->CNADVDEP);
						} // if for ccode ends here

						if (($index < count($data) - 1 && $icode[count($icode) - 1] != $data[$index + 1]['INDR_CODE']) or
							$index == count($data) - 1
						) {

							printf('
							<tr style="background-color:#9fb1cc">
								<td colspan=8 ></td>
								<td colspan=1>Indentor Total</td>
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
								
							</tr>', $indrtotal->A30DAYS, $indrtotal->A45DAYS, $indrtotal->A60DAYS, $indrtotal->A90DAYS, $indrtotal->GT90DAYS, $indrtotal->TOT, $indrtotal->CNADVDEP);


							$gtotal->A30DAYS += floatval($indrtotal->A30DAYS);
							$gtotal->A45DAYS += floatval($indrtotal->A45DAYS);
							$gtotal->A60DAYS += floatval($indrtotal->A60DAYS);
							$gtotal->A90DAYS += floatval($indrtotal->A90DAYS);
							$gtotal->GT90DAYS += floatval($indrtotal->GT90DAYS);
							$gtotal->TOT += floatval($indrtotal->TOT);
							$gtotal->CNADVDEP += floatval($indrtotal->CNADVDEP);

							$indrtotal->A30DAYS = 0;
							$indrtotal->A45DAYS = 0;
							$indrtotal->A60DAYS = 0;
							$indrtotal->A90DAYS = 0;
							$indrtotal->GT90DAYS = 0;
							$indrtotal->TOT = 0;
							$indrtotal->CNADVDEP = 0;
						}
					} // foreach statement ends here #8697b0

					printf(
						'
                
                <tr style="background-color:#8697b0">
                    <td colspan=8></td>
					<td colspan=1 >Grand Total</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                </tr>',
						$gtotal->A30DAYS,
						$gtotal->A45DAYS,
						$gtotal->A60DAYS,
						$gtotal->A90DAYS,
						$gtotal->GT90DAYS,
						$gtotal->TOT,
						$gtotal->CNADVDEP
					);
				} //  NUM OF ROWS IF STATEMENT ENDS HERE 
				else {
					echo "NO DATA AVAILABLE FOR THIS REPORT!";
				}
			}

			?>




			</table>
		</div>
		<div class="container  col-md-12 col-sm-12 ">
			<a href="welcome.php" class="btn btn-success ml-3">Menu</a>
			<a href="logout.php" class="btn btn-danger ml-3">Sign Out</a>

		</div>

	</center>
	<footer>
		<div class="footer-bottom col-md-12 col-sm-12">
			<div class="container ">
				<div class="copyright">© 2020-<?php echo date("Y"); ?> Seshasayee Paper and Boards Limited</div>
			</div>
		</div>
	</footer>


	<script>
		window.onscroll = function() {
			myFunction()
		};

		var header = document.getElementById("myHeader");
		var sticky = header.offsetTop;

		function myFunction() {
			if (window.pageYOffset > sticky) {
				header.classList.add("sticky");
			} else {
				header.classList.remove("sticky");
			}
		}
	</script>

	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
	<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
	<script src="js/table2excel.js" type="text/javascript"></script>
	<script type="text/javascript">
		function Export() {
			$("#tblCustomers").table2excel({
				filename: "Table.xls"
			});
		}
	</script>


	<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
	<script src="links/table2excel.js" type="text/javascript"></script>
	<script>
		var tblCustomers = new Table2Excel();

		document.getElementById('export').addEventListener('click', function() {
			tblCustomers.export(document.querySelectorAll('table'));
		});
	</script>

	<script type="text/javascript">
		function imprimir() {
			var divToPrint = document.getElementById("tblCustomers");
			newWin = window.open("");
			newWin.document.write(divToPrint.outerHTML);
			newWin.print();
			newWin.close();
		}
	</script>

</body>

</html>