ageing report

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

Post Reply
maideen
New php-forum User
New php-forum User
Posts: 27
Joined: Mon Mar 07, 2011 11:38 pm

ageing report

Post by maideen » Fri May 24, 2013 8:52 pm

hi.
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

johnj
php-forum Super User
php-forum Super User
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: ageing report

Post by johnj » Sat May 25, 2013 1:05 am

did you check if your sql query is producing results?

maideen
New php-forum User
New php-forum User
Posts: 27
Joined: Mon Mar 07, 2011 11:38 pm

Re: ageing report

Post by maideen » Sat May 25, 2013 8:26 am

Hi

I could not find whether the producing

maidene

johnj
php-forum Super User
php-forum Super User
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: ageing report

Post by johnj » Sat May 25, 2013 10:54 pm

display the sql, copy and run it from a client like phpmyadmin - this is how you decide if the sql is producing the results that you want.

maideen
New php-forum User
New php-forum User
Posts: 27
Joined: Mon Mar 07, 2011 11:38 pm

Re: ageing report

Post by maideen » Sun May 26, 2013 7:44 pm

Hi

I have solved by using store procedure in mssql server.

It is this

Code: Select all

[code]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
[/code]

Thank you all to post the suggestion

Thank you very much

Maideen

Post Reply

Who is online

Users browsing this forum: No registered users and 7 guests