I need to generate the ageing report like
Code: Select all
Code Name 30days 60days 90days 120days
0001 abc 500.85 675.90 100.78 56.90
0002 def 123.00 0.00 21.00 67.09
connection.inc.php
Code: Select all
<?php
try {
$hostname = "server"; //host
$dbname = "database"; //db name
$username = "user"; // username like 'sa'
$pw = "password"; // password for the user
$dbh = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
}
catch (PDOException $e)
{
echo "not connected " . $e->getMessage() . "\n";
file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
exit;
}
?>
Code: Select all
<?php
include_once '../inc/connection.inc.php';
include 'searchform.html.php';
?>
<?php
/*
if (isset($_POST['datefrom']) && $_POST['datefrom'] != "" )
{
$datefrom = $_POST["datefrom"];
$stmt = $dbh->query("SELECT Code,Name,
SUM(CASE WHEN DocDate >= '$datefrom' - 30 THEN Balance ELSE 0 END) AS d30,
SUM(CASE WHEN DocDate BETWEEN '$datefrom' - 60 AND '$datefrom' - 31 THEN Balance ELSE 0 END) AS d60,
SUM(CASE WHEN DocDate < '$datefrom' - 60 THEN Balance ELSE 0 END) AS above61,
SUM(Balance) AS total_outstanding FROM AgeingReport GROUP BY Code");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
}
include 'view.html.php';
exit();
*/
?>
<?php
if (isset($_POST['datefrom']) && $_POST['datefrom'] != "" )
{
$datefrom = $_POST["datefrom"];
$stmt=$dbh->prepare(" SELECT Code, Name, DATEDIFF('$datefrom', DocDate) AS days_past_due,
SUM(IF(days_past_due = 0, Balance, 0) As curent),
SUM(IF(days_past_due BETWEEN 1 AND 30, Balance, 0) As d30),
SUM(IF(days_past_due BETWEEN 31 AND 60, Balance, 0) As d60),
SUM(IF(days_past_due BETWEEN 61 AND 90, Balance, 0) As d90),
SUM(IF(days_past_due > 90, Balance, 0) As d90above)
FROM AgeingReport GROUP BY Code");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
}
include 'view.html.php';
exit();
?>
Code: Select all
<?php
include '../templete/header.php';
?>
<div>
<h6>Ageing Report</h6>
</div>
<form action="" method="post">
<table class="tdtable" id="tdtable">
<tr>
<td>As on:</td>
<td><input type="text" id="datepicker1" name="datefrom" /></td>
</tr>
</table>
<div>
<input type="submit" value="Search">
</div>
</form>
view.html.php
Code: Select all
<?php //include '../templete/header.php'; ?>
<table width="100%" align="center" cellpadding="4" cellspacing="1" class=tbl_table">
<tr>
<td class="tbl_header">MV CODE</td>
<td class="tbl_header">MV NAME</td>
<td class="tbl_header">Current</td>
<td class="tbl_header">30-days</td>
<td class="tbl_header">60-days</td>
<td class="tbl_header">90-days</td>
<td class="tbl_header">90-days above</td>
</tr>
<?php
if(isset($stmt))
{
while($row = $stmt->fetch())
{?>
<tr>
<td class="tbl_content"><?php echo $row['Code'];?></td>
<td class="tbl_content"><?php echo $row['Name'];?></td>
<td class="tbl_content"><?php echo $row['SONo'];?></td>
<td class="tbl_content_right"><?php echo number_format(current,2) ;?></td>
<td class="tbl_content_right"><?php echo number_format(d30,2) ;?></td>
<td class="tbl_content_right"><?php echo number_format(d60,2) ;?></td>
<td class="tbl_content_right"><?php echo number_format(d90,2) ;?></td>
<td class="tbl_content_right"><?php echo number_format(d90above,2) ;?></td>
<!--
<td>
<a href="view?=<?php echo $row['SVCode'];?>">View</a> |
<a href="edit?=<?php echo $row['SVCode'];?>">Edit</a> |
<a href="delete?=<?php echo $row['SVCode'];?>">Delete</a>
</td>
-->
</tr>
<?php
//$balamt+=$row['BalAmt'];
// $balqty+=$row['BalQty'];
// $rtnqty+=$row['RTNQty'];
// $qty+=$row['Qty'];
}}?>
<tr><td colspan="9"><hr /></tr>
<tr>
<!--
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td class="tbl_content_total"> <?php echo number_format($qty);?></td>
<td class="tbl_content_total"> <?php echo number_format($rtnqty);?></td>
<td class="tbl_content_total"> <?php echo number_format($balqty);?></td>
<td class="tbl_content_total"> <?php echo number_format($balamt,2);?></td>
-->
</tr>
</table>
<?php unset($dbh); unset($stmt); ?>
<?php
include '../templete/footer.php';
?>
maideen
