hi
I tried to generate ageing reports in php. But I could not get result and even error.
it is my code. Pls help me
connection.inc.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 found " . $e->getMessage() . "\n";
file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
exit;
}
index.php
<?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
<?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
<?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>
</tr>
<?php
}}?>
<tr><td colspan="9"><hr /></tr>
<tr>
</tr>
</table>
<?php unset($dbh); unset($stmt); ?>
<?php
include '../templete/footer.php';
?>
Pls help me
Maideen
