0

Sir, I have these PHP codes

while($res = sqlsrv_fetch_array($result)) {
echo "<tr>";
    $sno=$sno+1;
        echo "<td align='center'>$sno</td>";    
        echo "<td align='center'>".($res['acc_code'])."</td>";  
        $partycode=($res['acc_code']);
        $query1 = "select code,desc1 from master where code='".$partycode."'";
        $result1 = sqlsrv_query($con,$query1) or die ("Error". sqlsrv_errors($con)) ;  
        while($res1 = sqlsrv_fetch_array($result1)) {
        echo "<td align='left' class='color1'>".($res1['desc1'])."</td>";
        }
        echo "<td align='right'>".($res['dr_amount'] != 0 ? number_format(abs($res['dr_amount'])):'')."</td>";  
        echo "<td align='right'>".($res['cr_amount'] != 0 ? number_format(abs($res['cr_amount'])):'')."</td>";  

}

The query generates this result
Capture2.PNG

But I need this result
Capture2_-_Copy.PNG

I mean the sum of all 7 digit code to 4 digit codes like shown in red square.

Please help

4
Contributors
32
Replies
281
Views
8 Months
Discussion Span
Last Post by phphp
Featured Replies
  • 1
    diafol 3,669   8 Months Ago

    OK think I know what you want, but the calc in the image seems wrong. Also what's the first sql query? Looks like you may be repeating it in the loop. So include your code from the strt of the first query Read More

  • 1
    diafol 3,669   8 Months Ago

    My take on it would be this: $query = "SELECT data.acc_code, master.desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount FROM ( SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master UNION ALL SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '$t_date1' UNION ALL SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE … Read More

  • 2
    diafol 3,669   8 Months Ago

    Looks OK to me so far. Could do with a tweak maybe. Here are my suggestions: //Preparing amounts while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) { $code = (string)$res['acc_code']; $output[$code] = $res; if(strlen($code) == 7){ $cut = substr($code,0,4); if(isset($output[$cut])){ $output[$cut]['my_amount'] += $output[$code]['my_amount']; } } } //Quick Test $r = array_slice($res, 0, 20, true); … Read More

  • 1
    diafol 3,669   8 Months Ago

    Ok now you have progress of a sort. Go into the loop for adding data to the four digit record and investigate. Am busy until tomorrow. Read More

  • 1
    diafol 3,669   8 Months Ago

    Well from your var dump, you can see that the acc_code is set to 7 characters for some reason, even though they contain no chars, 1 char, 2 chars etc. You'll need to trim in this case. I stupidly assumed that your acc_code field was an integer field, not varchar. … Read More

1

OK think I know what you want, but the calc in the image seems wrong.
Also what's the first sql query? Looks like you may be repeating it in the loop. So include your code from the strt of the first query

0

Thanks for reply.
Sir #diafol, here are complete codes including first query

// data comes from 11 tables,

$query = "SELECT
data.acc_code, sum(data.dr_amount)as debit1,sum(data.cr_amount)as credit1,
(case when sum(data.dr_amount) > sum(data.cr_amount)  then sum(data.dr_amount) - sum(data.cr_amount) end) as dr_amount,
(case when sum(data.dr_amount) < sum(data.cr_amount)  then sum(data.dr_amount) - sum(data.cr_amount) end) as cr_amount
from (
SELECT acc_code,open_dr as dr_amount,open_cr as cr_amount from master
union all
SELECT acc_code,dr_amount,cr_amount from vouchers where  date <= '".$t_date1 ."'
union all
SELECT acc_code,dr_amount,cr_amount from cashsalp where  date <=   '".$t_date1 ."'
union all
SELECT acc_code,dr_amount,cr_amount from crsalp where  date <=   '".$t_date1 ."'
union all
SELECT acc_code,dr_amount,cr_amount from brsalp where  date <=   '".$t_date1 ."'
union all
SELECT acc_code,dr_amount,cr_amount from mrsalp where  date <=   '".$t_date1 ."'
union all
SELECT  acc_code,dr_amount,cr_amount from orsalp where  date <=   '".$t_date1 ."'
union all
SELECT acc_code, dr_amount, cr_amount from frsalp where  date <=   '".$t_date1 ."'
union all
SELECT acc_code, dr_amount,cr_amount from Srsalp where  date <=   '".$t_date1 ."'
union all
SELECT acc_code, dr_amount, cr_amount from cashprp where  date <=  '".$t_date1 ."'
union all
SELECT acc_code,dr_amount,cr_amount from crprp where  date <=   '".$t_date1 ."'
) as data
group by data.acc_code
order by data.acc_code";

$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;  

while($res = sqlsrv_fetch_array($result)) {
echo "<tr>";
if ($res['dr_amount']!= 0 or $res['cr_amount']!= 0) {

    $sno=$sno+1;
        echo "<td align='center'>$sno</td>";    
        echo "<td align='center'>".($res['acc_code'])."</td>";

        $partycode=($res['acc_code']);

        $query1 = "select code,desc1 from master where code='".$partycode."'";
        $result1 = sqlsrv_query($con,$query1) or die ("Error". sqlsrv_errors($con)) ;  
        while($res1 = sqlsrv_fetch_array($result1)) {
        echo "<td align='left' class='color1'>".($res1['desc1'])."</td>";
        }

                echo "<td align='right'>".($res['dr_amount'] != 0 ? number_format(abs($res['dr_amount'])):'')."</td>";  
        echo "<td align='right'>".($res['cr_amount'] != 0 ? number_format(abs($res['cr_amount'])):'')."</td>";  

}

echo "</tr>";
}
1

My take on it would be this:

$query = "SELECT data.acc_code, master.desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount
FROM (
  SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM crsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM brsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM mrsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT  acc_code, dr_amount, cr_amount FROM orsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM frsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM Srsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM cashprp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM crprp WHERE date <= '$t_date1'
) AS data
    LEFT JOIN master ON data.acc_code = master.code
    GROUP BY data.acc_code
    ORDER BY data.acc_code";

$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;

$output = [];
$sno = 1;
$stringOutput = '';

//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {

    $debitAmt = 0; $creditAmt = 0;
    $code = (string)$res['code'];
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }

}

//Formatting
foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}

?>

<table>
    <thead>
        <!-- whatever -->
    </thead>
    <tbody>
        <?=$stringOutput?>
    </tbody>
</table>

Unfortunately it uses two loops, but getting SQL to do the adding data to a previous record is a bit wonky.
All-in-one query which gets the description of the as well.
Not tested.

0

Sir many thank for helping,
I replaced code with acc_code in this section

//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {
    $debitAmt = 0; $creditAmt = 0;
    $code = (string)$res['code'];
    $output[$code] = $res;
    if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }
}

Now the above section is like this

while($res = sqlsrv_fetch_array($result)) {
    $debitAmt = 0; $creditAmt = 0;
    $acc_code = (string)$res['acc_code'];
    $output[$acc_code] = $res;
    if(strlen($acc_code) == 7){
        $cut = substr($acc_code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }
    }
   }

Becuase the actual field name in query is acc_code

But When I run php, there is neither error message nor result.

anil.png

when I use this command

var_dump($output);

then it reply as

Untitled3.png

Please

Edited by phphp: reference

2

Looks OK to me so far. Could do with a tweak maybe. Here are my suggestions:

//Preparing amounts
while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    $code = (string)$res['acc_code'];
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }

}

//Quick Test
$r = array_slice($res, 0, 20, true);
echo "<pre>";
print_r($r);
echo "</pre>";

//Formatting
foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}

Remember that the data is contained in the $stringOutput variable. It is not going to display to the screen until you specifically echo it somewhere -prefereably in your html table. The quick test, limits the array to 20 rows, so that they can be read easily. Also change the sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC) - you probably don't need the numerical index too.

Edited by diafol

0

Thanks sir for writting this advice

Remember that the data is contained in the $stringOutput variable. It is not going to display to the screen until you specifically echo it somewhere -prefereably in your html table.

At the end of following cods I used this line

<?=$stringOutput?>

Now data is showing in relevant fields but the amount in 4 digit rows is still empty.
Capture.JPG

Here are complete codes set, ma be I am doing somthing wrong, please have a look .

       <?php

global $mybalance;
$mybalance=0;
$mydrcr='';

$query = "SELECT data.acc_code,max(master.desc1)as desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount
FROM (
SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM crsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM brsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM mrsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT  acc_code, dr_amount, cr_amount FROM orsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM frsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM Srsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM cashprp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM crprp WHERE date <= '".$t_date1."'
) AS data
LEFT JOIN master ON data.acc_code = master.code
GROUP BY data.acc_code
ORDER BY data.acc_code";
$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;  
//var_dump($result);
$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;
$output = [];
$sno = 1;
$stringOutput = '';
//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {
    $debitAmt = 0;
    $creditAmt = 0;
    $acc_code = (string)$res['acc_code'];
    $output[$acc_code] = $res;
    if(strlen($acc_code) == 7){
        $cut = substr($acc_code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }
    }
}
////Quick Test
//$r = array_slice($res, 0, 20, true);
//echo "<pre>";
//print_r($r);
//echo "</pre>";
//var_dump($output);
//Formatting
foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));
    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}

?>

<!--=============================================================================================
// Printing
//=============================================================================================-->
   <table>
    <thead align="center">
    <tr>
    <td width="10%">S.No</td>
    <td width="10%">Code</td>
    <td width="45%" align="left">Title of Account</td>
    <td width="15%">Debit</td>
    <td width="15%">Credit</td>
    </tr>
    </thead>
    <tbody>
    <?=$stringOutput?>
</tbody>
</table>
1

Ok now you have progress of a sort. Go into the loop for adding data to the four digit record and investigate. Am busy until tomorrow.

0

Sir,

I used this command

var_dump($output);

But 4 digit code still nat have values as shown in image

Capture.PNG

Please

1

Well from your var dump, you can see that the acc_code is set to 7 characters for some reason, even though they contain no chars, 1 char, 2 chars etc. You'll need to trim in this case. I stupidly assumed that your acc_code field was an integer field, not varchar. If you insist on using varchar for your integer data, then you just need this:

$code = trim($res['acc_code']); //instead of (string)$res['acc_code'];
$output[$code] = $res;

This may cause issues if you the trim() function returns an empty string, as in the first record - why do you have an empty record there?

Edited by diafol

0

Thanks Thanks sir #diafol

with your help I modified some code and know result is ok
I removed empty rows with adding this line

    if($dr>0 or $cr>0)  {   

The final code are here

       <?php

global $mybalance;
$mybalance=0;
$mydrcr='';

$query = "SELECT data.acc_code,max(master.desc1)as desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount
FROM (
SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM crsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM brsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM mrsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT  acc_code, dr_amount, cr_amount FROM orsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM frsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM Srsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM cashprp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM crprp WHERE date <= '".$t_date1."'
) AS data
LEFT JOIN master ON data.acc_code = master.code
GROUP BY data.acc_code
ORDER BY data.acc_code";
$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;  

$output = [];
$sno = 1;
$stringOutput = '';
//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {

    $debitAmt = 0;
    $creditAmt = 0;
    $acc_code = trim($res['acc_code']); //instead of (string)$res['acc_code'];
    $output[$acc_code] = $res;

        $cut = substr(trim($acc_code),0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];

        }

        $cut = substr(trim($acc_code),0,2);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }

        $cut = substr(trim($acc_code),0,1);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }

}

//Formatting
foreach($output as $v){

    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    if($dr>0 or $cr>0)  {   

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
    }
}
?>

<!--=============================================================================================
// Printing
//=============================================================================================-->
<br />
   <table>
    <thead align="center">
    <tr>
    <td width="10%">S.No</td>
    <td width="10%">Code</td>
    <td width="45%">Title of Account</td>
    <td width="15%">Debit</td>
    <td width="15%">Credit</td>
    </tr>
    </thead>
    <tbody>
    <?=$stringOutput?>
</tbody>
</table>
</div>

But now there is a last request to calculate sum of all 4 digit codes like shown in image
I mean how to extract required date from

<?=$stringOutput?>

or with some other method.

aa.PNG

acc_code is varchar.

Please

Edited by phphp

1

This is another layer of complexity not expected. I'm assuming that the totals (debit and credit) as tota of 4-digit codes are simply the sums of seven digit acc_codes (as calculated previously)? Unfortunately this may require a slightly different approach.

How about you have a go and see what you get. I believe I've "given" you a lot of help thus far, so attempt a solution yourself and if you run into an issue, post back.

0

SIR #diafol

I wrote a long query to get the sum of all 4 digit codes,
The result is ok.
Please review if you can make it SHORTER.

<?php
$query = "SELECT distinct(LEFT(data.acc_code,4)) as acc_code,
sum(isnull(data.dr_amount,0))as dr_amount1,
sum(isnull(data.cr_amount,0))as  cr_amount1,
(case when sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0))>0  then sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0)) end) as dr_amount,
(case when sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0))<0  then sum(isnull(data.cr_amount,0)) + sum(isnull(data.dr_amount,0)) end) as cr_amount
   from (
SELECT max(acc_code)as acc_code,
(case when sum(open_dr) > sum(open_cr)  then sum(open_dr) - sum(open_cr) end) as dr_amount,
(case when sum(open_dr) < sum(open_cr)  then sum(open_dr) - sum(open_cr) end) as cr_amount
 from master where  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, 
(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
 from vouchers  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, 
(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from cashsalp   where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code,
(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from crsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from brsalp  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from mrsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from orsalp     where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code,  (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from frsalp  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code,  (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from Srsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from cashprp   where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from crprp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4))
 as data
where  len(acc_code)=7  group by left(acc_code,4)";

$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;  

$sumdr=0;
$sumcr=0;

while($res = sqlsrv_fetch_array($result)) {
$sumdr=$sumdr+($res['dr_amount']);
$sumcr=$sumcr+($res['cr_amount']);
}

echo "<td></td>";
echo "<td></td>";
echo "<td align='Center'><span style='color:brown;font-size:14px;font-weight:bolder;'>Total</span></td>";
echo "<td align='right'>".number_format($sumdr)."</td>";
echo "<td align='right'>".number_format(abs($sumcr))."</td>";

echo " Total of 4 digits Codes   =       "      .number_format($sumdr).  "  |   "    .number_format(abs($sumcr));

?>

 ![Untitled.png](/attachments/large/4/d2fa16a6e4c88a29a39a4e94dc2bacf6.png "align-center") 
Attachments Untitled.png 65.3 KB
1

This may help - minimal changes from previous version I posted:

$query = "SELECT data.acc_code, master.desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount
FROM (
  SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM crsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM brsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM mrsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT  acc_code, dr_amount, cr_amount FROM orsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM frsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM Srsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM cashprp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM crprp WHERE date <= '$t_date1'
) AS data
    LEFT JOIN master ON data.acc_code = master.code
    GROUP BY data.acc_code
    ORDER BY data.acc_code";

$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;

$output = [];
$sno = 1;
$stringOutput = '';

$bigDebit = 0; //add this
$bigCredit = 0; // add this

//Preparing amounts
while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    $code = trim($res['acc_code']);
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);

        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
            if($output[$code]['my_amount'] > 0)$bigCredit += $output[$code]['my_amount']; //add this
            if($output[$code]['my_amount'] < 0)$bigDebit += -$output[$code]['my_amount']; //add this
        }
    }

}

foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}
?>

<?=$bigDebit . '|' . $bigCredit?> <!--add this-->
<table>
    <thead>
        <!-- whatever -->
    </thead>
    <tbody>
        <?=$stringOutput?>
    </tbody>
</table>
0

Thanks Sir #diafol

I tested it in manay ways, but this command is SUMMING data of all 7 digit codes instead of 4 digits.

if($output[$acc_code]['my_amount'] > 0)$bigCredit += $output[$acc_code]['my_amount']; //add this
if($output[$acc_code]['my_amount'] < 0)$bigDebit += -$output[$acc_code]['my_amount']; //add this

I think the command needs to add the reference of all 4 digit codes.

Thanks again

Edited by phphp

0

Check my logic: four char codes are the sum of seven char codes. You state the totals are the sums of four char codes. Does it not follow that the totals therefore are the sums of seven char codes?

0

Check my logic: four char codes are the sum of seven char codes. You state the totals are the sums of four char codes. Does it not follow that the totals therefore are the sums of seven char codes?

Sir, Four char codes are NOT the sum of seven char codes.
The data in 4 char codes comes after applying (credit-debit) with all 7 digit codes like

Code        Debit   Credit
1101               100
1101001     900     100
1101002     600     1000
1101003     700     1200

1102                  150
1102001             150

1201          250   
1201001              450
1201002              500
1201003     1200    

Please see attachment also

Untitled.png

This part of codes display the total of all 7 digit codes. (3400 for above data But I need 250)

 if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
            if($output[$code]['my_amount'] > 0)$bigCredit += $output[$code]['my_amount']; //add this
            if($output[$code]['my_amount'] < 0)$bigDebit += -$output[$code]['my_amount']; //add this
        }
    }

Thanks

Edited by phphp

1

Maybe this:

$query = "SELECT data.acc_code, master.desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount
FROM (
  SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM crsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM brsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM mrsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT  acc_code, dr_amount, cr_amount FROM orsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM frsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM Srsalp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM cashprp WHERE date <= '$t_date1'
    UNION ALL
  SELECT acc_code, dr_amount, cr_amount FROM crprp WHERE date <= '$t_date1'
) AS data
    LEFT JOIN master ON data.acc_code = master.code
    GROUP BY data.acc_code
    ORDER BY data.acc_code";

$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;

$output = [];
$sno = 1;
$stringOutput = '';

//Preparing amounts
while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    $code = trim($res['acc_code']);
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);

        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }

}

$bigDebit = 0;
$bigCredit = 0;

foreach($output as $k=>$v){
    $cr = '';
    $dr = '';

    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    if( strlen($k) == 4 ){
        if($v['my_amount'] > 0) $bigCredit += number_format($v['my_amount']);
        if($v['my_amount'] < 0) $bigDebit += number_format(abs($v['my_amount']));
    }

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}
?>

<?=$bigDebit . '|' . $bigCredit?> <!--add this-->
<table>
    <thead>
        <!-- whatever -->
    </thead>
    <tbody>
        <?=$stringOutput?>
    </tbody>
</table>

Be interesting to see if your big query is quicker than the two loop method I posted. I generally try to keep the SQL as simple as possible, but that's not always the appropriate solution.

Edited by diafol

0

Thanks but many thanks my Great Professor sir #diafol,

I used this line your codes

  echo $k.'|'.$v['my_amount']."<br>";

Result
Untitled.png

That wast enough to ensure that query is getting the sum of all four digit codes but the result was ZERO.
Then I changed these line

if( strlen($k) == 4 ){
        if($v['my_amount'] > 0) $bigCredit += number_format($v['my_amount']);
        if($v['my_amount'] < 0) $bigDebit += number_format(abs($v['my_amount']));
    }

with

if( strlen($k) == 4 ){
        if($v['my_amount'] > 0) $bigCredit +=($v['my_amount']);
        if($v['my_amount'] < 0) $bigDebit += (abs($v['my_amount']));
    }

The problem was in numbr_format function

After removing the function, the result is amazingly correct.

I learnt many new new techniques in this thread with your kind help.

Now, if you do not mind, this is last request,
I need some more help about following portion

<table>
    <thead align="center">
    <tr>
    <td width="10%">S.No</td>
    <td width="10%">Code</td>
    <td width="45%">Title of Account</td>
    <td width="15%">Debit</td>
    <td width="15%">Credit</td>
    </tr>
    </thead>
    <tbody>
    <?=$stringOutput?>

</tbody>
</table>

The data appears in table as

Untitled.png

Is it possible to change bgcolor of all rows those have four digit codes like this

Untitled.png

In simple case where we can detect row number, I use these codes

$i=0;

while($res = sqlsrv_fetch_array($result)) {

     if($i % 2 == 0) 
    {
      $bgcolor= "#b8d1f3";
   }
   else 
  {
     $bgcolor= "#dae5f4";
  }
   $i++;

echo "<tr bgcolor='$bgcolor'>";

But do not know how to use codes like above with

<?=$stringOutput?>

1000% Thanks

Edited by phphp

1

Ah. Yes number format would cause issues! You can use

$stringOutput .= '<tr>';

To:

$stringOutput .= ( strlen($k) == 4 ) )? '<tr class="feature-row">' : '<tr>';

Then you can use css on .feature-row{}

0

Sir, I have these codes now

// $stringOutput .= '<tr>'; // commented this line
      $stringOutput .= "(strlen($k)==4))? '<tr class='mycolor'>':'<tr>'";    // added this line
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;

and code of mycolor class

.mycolor {
    color:#87ACE7;
    background-color:#9FF0BF;
}

But it shows this result instead of row format

Untitled.png

Thanks

Edited by phphp

0

Sir, Done like this
Untitled.png

But it says:

Untitled.png

May be I am doing something wrong.

Thanks

0

Beleive me sir both lines present same result shown in image

$stringOutput .= "(strlen($k==4))? '<tr class='mycolor'>':'<tr>'";  
$stringOutput .= "(strlen($k)==4)? '<tr class='mycolor'>':'<tr>'";  

Untitled.png

Edited by phphp

0

You didn.t take off the double quotes. Try

$stringOutput .= (strlen($k==4))? '<tr class='mycolor'>':'<tr>';
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.