Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

ageing report

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

ageing report

Postby 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
maideen
New php-forum User
New php-forum User
 
Posts: 27
Joined: Mon Mar 07, 2011 11:38 pm

Re: ageing report

Postby johnj » Sat May 25, 2013 1:05 am

did you check if your sql query is producing results?
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: ageing report

Postby maideen » Sat May 25, 2013 8:26 am

Hi

I could not find whether the producing

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

Re: ageing report

Postby 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.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: ageing report

Postby 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
maideen
New php-forum User
New php-forum User
 
Posts: 27
Joined: Mon Mar 07, 2011 11:38 pm


Return to PHP coding => General

Who is online

Users browsing this forum: Bing [Bot] and 4 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.