ageing report in php

[maideen]

hi.

I need to generate the ageing report like 

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

<?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

 

<?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>
  <!--
      <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

 

 

[Barand]

 

BETWEEN '$datefrom' - 60 AND '$datefrom' - 31

 

should be

 

 

BETWEEN '$datefrom' - INTERVAL 60 DAY AND '$datefrom' - INTERVAL 31 DAY

 

and alter others accordingly. (Assuming your dates are held as type DATE and you are using yyyy-mm-dd for input date too)

[maideen]

hi. 

still no results

 

pls help me

 

maideen

[Barand]

What format are your dates?

[maideen]

Hi

 

date format m-d-y

 

pls

 

maideen

[Barand]

Make your dates type DATE in the database (format yyyy-mm-dd) and your input dates in the same format and then you stand a chance of getting it to work.

 

When you've done that you can use date arithmetic, comparisons and MySql date and time functions.

[maideen]

thank you Mr.Guru

 

I will try first and let you know

 

maideen

[maideen]

Hi all

 

Thank you very much for supporting by giving suggestion

 

I have solved by using Store procedure in Mssql server. Here it is... 

 

USE [CMSHCK]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[php_usp_AgeingReportNew]

@dtCurrent datetime

AS

BEGIN

 

-- if @dtCurrent = '' begin

-- Set @dtCurrent = GetDate()

-- end

 

select code as MVCode, Name as MVName, sum(Balance) Month30

into #Month30

from   dbo.AgeingReport

where DocDate >= DateAdd("d", -30, (@dtCurrent))  

and DocDate <  @dtCurrent

group by code, Name

 

select code as MVCode, Name as MVName, sum(Balance) Month60

into #Month60

from   dbo.AgeingReport

where DocDate >= DateAdd("d", -60, (@dtCurrent))  

and DocDate <  DateAdd("d", -30, (@dtCurrent)) 

group by code, Name

 

select code as MVCode, Name as MVName, sum(Balance) Month90

into #Month90

from   dbo.AgeingReport

where DocDate >= DateAdd("d", -90, (@dtCurrent))  

and DocDate <  DateAdd("d", -60, (@dtCurrent)) 

group by code, Name

 

select code as MVCode, Name as MVName, sum(Balance) Month120

into #Month120

from   dbo.AgeingReport

where DocDate >= DateAdd("d", -120, (@dtCurrent))  

and DocDate <  DateAdd("d", -90, (@dtCurrent)) 

group by code, Name

 

select code as MVCode, Name as MVName, sum(Balance) Month150

into #Month150

from   dbo.AgeingReport

where DocDate >= DateAdd("d", -150, (@dtCurrent))  

and DocDate <  DateAdd("d", -120, (@dtCurrent)) 

group by code, Name

 

select code as MVCode, Name as MVName, sum(Balance) Month180

into #Month180

from   dbo.AgeingReport

where DocDate >= DateAdd("d", -9999, (@dtCurrent))  

and DocDate <  DateAdd("d", -150, (@dtCurrent)) 

group by code, Name

 

select X.MVCODE, X.MVNAME, SUM(Days30) Days30, SUM(Days60) Days60, SUM(Days90) Days90, 

SUM(Days120) Days120, SUM(Days150) Days150, SUM(Days180) Days180 FROM (

select MVCode, MVname, Month30 as 'Days30' , '0' as 'Days60' , '0' as 'Days90' , '0' as 'Days120' , '0' as 'Days150' , '0' as 'Days180' from #Month30

union

select MVCode, MVname, '0' as 'Days30' , Month60 'Days60' , '0' as 'Days90' ,'0' as 'Days120' , '0' as 'Days150' , '0' as 'Days180' from #Month60

union

select MVCode, MVname, '0' as 'Days30' , '0' as 'Days60' , Month90 as 'Days90', '0' as 'Days120' , '0' as 'Days150' , '0' as 'Days180' from #Month90

union

select MVCode, MVname, '0' as 'Days30' , '0' as 'Days60' , '0' as 'Days90' , Month120 as 'Days120' , '0' as 'Days150' , '0' as 'Days180' from #Month120

union

select MVCode, MVname, '0' as 'Days30' , '0' as 'Days60' , '0' as 'Days90' , '0' as 'Days120' ,Month150 as 'Days150', '0' as 'Days180' from #Month150

union

select MVCode, MVname, '0' as 'Days30' , '0' as 'Days60' , '0' as 'Days90' , '0' as 'Days120' ,'0' as 'Days150' ,Month180 as 'Days180' from #Month180

) X

GROUP BY MVCODE, MVNAME

order by MVCODE

 

drop table #Month30 

drop table #Month60 

drop table #Month90

drop table #Month120

drop table #Month150

drop table #Month180

 

 

END

 

 

Thank you very much