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
My code is below. But Nothing is appear. Pls if anyone knows pls help me
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;
}
?>
index.php
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();
?>
searchform.html.php
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';
?>
Pls help me
maideen
