ageing report in php

maideen · May 25, 2013

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 · May 25, 2013

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 · May 25, 2013

hi.

still no results

pls help me

maideen

Barand · May 25, 2013

What format are your dates?

maideen · May 26, 2013

Hi

date format m-d-y

pls

maideen

Barand · May 26, 2013

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 · May 27, 2013

thank you Mr.Guru

I will try first and let you know

maideen

maideen · May 27, 2013

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

Sign In

ageing report in php

Recommended Posts

maideen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

maideen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

maideen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

maideen

Link to comment

Share on other sites

maideen

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information