Jump to content


Photo

ageing report in php

ageing php reports

Best Answer maideen, 26 May 2013 - 10:49 PM

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
Go to the full post


  • Please log in to reply
7 replies to this topic

#1 maideen

maideen

    Advanced Member

  • Members
  • PipPipPip
  • 31 posts

Posted 24 May 2013 - 11:54 PM

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
 

 



#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,223 posts
  • LocationCheshire, UK

Posted 25 May 2013 - 05:44 AM

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)


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 maideen

maideen

    Advanced Member

  • Members
  • PipPipPip
  • 31 posts

Posted 25 May 2013 - 11:20 AM

hi. 

still no results

 

pls help me

 

maideen



#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,223 posts
  • LocationCheshire, UK

Posted 25 May 2013 - 12:11 PM

What format are your dates?


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 maideen

maideen

    Advanced Member

  • Members
  • PipPipPip
  • 31 posts

Posted 25 May 2013 - 07:32 PM

Hi

 

date format m-d-y

 

pls

 

maideen



#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,223 posts
  • LocationCheshire, UK

Posted 25 May 2013 - 07:43 PM

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.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 maideen

maideen

    Advanced Member

  • Members
  • PipPipPip
  • 31 posts

Posted 26 May 2013 - 10:15 PM

thank you Mr.Guru

 

I will try first and let you know

 

maideen



#8 maideen

maideen

    Advanced Member

  • Members
  • PipPipPip
  • 31 posts

Posted 26 May 2013 - 10:49 PM   Best Answer

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com