I have three SQL queries that I need to run to get Header/Detail/Summary records

kat35601 · June 15, 2017

I have three SQL queries that I need to run to get Header/Detail/Summary records but I am not sure how to do it. Right now I can run them and get the all the Header record and Then all the Detail records and all the Summary records But I need it to be one Header record then all the detail that go with that header and then the summary that goes with that header and detail

<html>
<head>
</head>
<body>
<?php

include 'viarables.php';


$connect = odbc_connect("removed");
if (!$connect) {
    exit("Connection Failed: " . $connect);
}
/*Header Record */

$result = odbc_exec($connect, $sql);
if (!$result) {
    exit("Error in SQL");
}

/*$my_file    = date('m-d-Y_hia') . '.CO';*/
$my_file    = 'cit_invoice.di';
$handle = fopen($my_file, 'w+') or die('Cannot open file:  ' . $my_file);
while ($row = odbc_fetch_array($result)) {

    $record_type      = $row['record_type'];
    $cust_num         = $row['arpCustomerOrganizationID'];
    $name             = $row['cmoname'];
    $address          = $row['cmoAddressLine2'];
    $city             = $row['cmocity'];
    $state            = $row['cmoState'];
    $postal           = $row['cmoPostCode'];
    $phone            = $row['cmoPhoneNumber'];
    $invoice_id       = $row['arpARInvoiceID'];
    $email            = $row['cmoEMailAddress'];
    $fax              = $row['cmoFaxNumber'];
    $shpName          = $row['cmlName'];
    $shpAddressLine2 = $row['cmlAddressLine2'];
    $shpAddressLine1  = $row['cmlAddressLine1'];
    $shpCity          = $row['cmlCity'];
    $shpState         = $row['cmlState'];
    $shpPostCode      = $row['cmlPostCode'];
    $shpCountry       = $row['cmlCountry'];

    
    $shpPostCode = $row['cmlPostCode'];
    $cust_count=count($row['arpCustomerOrganizationID']);
    
    
  $addresst =trim($address);
    
    fprintf($handle, "%-4s%-1s%-1s%-15s%-8s%-5s%-30.30s%-30.30s%-30.30s%-17.17s%-2.2s%-10.10s%17.17s%-15.15s%-15.15s%-9.9s%-40.40s%-30.30s%-30.30s%-30.30s%-17.17s%-2.2s%-10.10s%-17.17s\n", $cit, $trade_style, $record_type, $cust_num, $invoice_id, $space7, $name, $addresst, $address2, $city, $state, $postal, $country, $phone, $fax , $duns, $email, $shpName, $shpAddressLine2, $shpAddressLine1, $shpCity, $shpState, $shpPostCode, $shpCountry);
    
    $mycount = $mycount + 1;
    

/*Detail Record */

$results = odbc_exec($connect, $sql2);
if (!$results) {
    exit("Error in SQL");
}


while ($row = odbc_fetch_array($results)) {

    $record_ty = $row['record_type'];
    $cust      = $row['arpCustomerOrganizationID'];
    $invoice_id = $row['arpARInvoiceID'];
    $line_num  = $row['arlARInvoiceLineID'];
    $inv_qty   = $row['qty'];
    $uom       = $row['arlUnitOfMeasure'];
    $price     = $row['price'];
    $desc      = $row['arlPartShortDescription'];

    
    fprintf($handle,"%-4s%-1s%-1s%-15s%-8d%07d%05d%010d%-2s%-17s%-2s%-20s%-20s%-20s%-20s%-30s%-30s%-20s%-20s\n", $cit, $trade_style, $record_ty, $cust, $invoice_id, $space6, $line_num, $inv_qty, $uom, $price, $pricecode, $upc, $catalognum, $vendornum, $ean, $desc, $desc2, $color, $size);
    
    



 $credit_invoice_total=0;
$invoice_total=0;
    $credit_tot=0;
    $inv_tot=0;
    $cust_tot=0;
    $credit_count=0;
    $assignment_num=0;
    $zero=0;
    $filler=' ';
    $client='214L';
    $record='S';
    $invoice_tot='0';
    
    /*Summary Records*/
    
$myresults = odbc_exec($connect, $sql3);
if (!$myresults) {
    exit("Error in SQL");
}
while ($row = odbc_fetch_array($myresults)) {
    
    

    $record_type                = $row['record_type'];
    $arpCustomerOrganizationID  = $row['arpCustomerOrganizationID'];
    $invoice_id = $row['arpARInvoiceID'];
     $inv_qty                    = $row['inv_qty'];
    $arpInvoiceBalanceBase      = $row['arpInvoiceBalanceBase'];
    $ship                       = $row['ship'];
    $ship1                      = $row['ship'];
    $arpPaymentTermID           = '030';
    $pay_terms_desc             = $row['pay_terms_desc'];
    $arpFullInvoiceSubtotalBase = $row['arpFullInvoiceSubtotalBase'];
    $arpARInvoiceLocationID     = $row['arpARInvoiceLocationID'];
    $arpOrderDate               = $row['arpOrderDate'];
    $arlDiscountPercent = $row['arlDiscountPercent'];
     $arpFreightAmountBase       = $row['arpFreightAmountBase'];
    $arpShippingMethodID        = $row['arpShippingMethodID'];
    $UARPTOTALBOXCOUNT          = $row['UARPTOTALBOXCOUNT'];
    

    
    fprintf($handle,"%-4s%-1s%-1s%-15s%-8d%-7s%05d%010d%-6s%-6s%-6s%-6s%3s%-30s%-10s%-5s%-22s%-6s%-6s%-1s%-1s%-10s%-8s%-7s%-10s%-10s%-10s%-10s%-15s%-30s%-2s%-6s\n", $cit, $trade_style, $record_type, $arpCustomerOrganizationID, $invoice_id, $filler, $inv_qty, $arpInvoiceBalanceBase*100, $ship, $ship, $date_shipped, $blanks, $arpPaymentTermID, $pay_terms_desc, $discount, $arpARInvoiceLocationID, $po, $arpOrderDate, $cust_dept, $risk, $discount_type, $arlDiscountPercent, $credit_memo, $filler, $arpFreightAmountBase, $tax, $other_charges, $allowance, $vendorID, $arpShippingMethodID, $spc, $UARPTOTALBOXCOUNT);
    
 if( $record_type  =='C') {
     $credit_count=count($row['arpARInvoiceID']);
 } 
    
     if( $record_type  =='D') {
     $invoice_count=count($row['arpARInvoiceID']);
 } 
    
    $cust_tot=count( $row['arpCustomerOrganizationID']);
  if( $record_type  =='C'){
    $credit_invoice_total += $row['arpInvoiceBalanceBase'];
    } 
    if ( $record_type  =='D'){
$invoice_total += $row['arpInvoiceBalanceBase'];
        }
   $credit_tot = number_format($credit_invoice_total,2);
    $inv_tot = number_format($invoice_total,2);
    echo "<table><tr>";
    echo "<th>CustID</th>";
    echo "<th>invoiceId</th>";
    echo "<th>Amount</th>";
    echo "<tr><td>$arpCustomerOrganizationID </td>";
    echo "<td> $record_type </td>";
    echo "<td> $arpARInvoiceID </td>";
    echo "<td> $arpInvoiceBalanceBase</td></tr>";
   
}}}
    $tomtotal=$invoice_total*100;
    $assignment_num=date('dH');
$assignment_date=date('mdy');
 echo "<td> $credit_tot </td>";
     echo "<td>  $inv_tot </td>";
    echo "<td>$assignment_date</td>";
    echo "<td>$assignment_num</td>";
     echo "<td> $tomtotal</td>";
   
 


fprintf($handle, "%-4s%-1s%-1s%06d%06d%06d%012d%012d%-4s%-6s%-1s\n", $cit, $filler, $record, $cust_tot, $invoice_count ,$credit_count,$invoice_total*100,  $credit_invoice_total*100, $assignment_num,$assignment_date ,$zero);


fprintf($handle, "%-4s%-1s%-1s%06d%06d%06d%012d%012d%-6s",$fillwith9,$fill1,$rectype, $cust_tot,$invoice_count,$credit_count,$invoice_total*100, $credit_invoice_total*100,$today);

This is the SQL queries from the include file.

<?php
/**
 * Created by PhpStorm.
 * User: tom
 * Date: 6/14/17
 * Time: 2:25 PM
 */
$cit='214L';
$trade_style='';
$filler=' ';
$price_code='EA';
$upc=' ';
$cusr_part_number=' ';
$vendor_style=' ';
$european_number=' ';
$color=' ';
$size=' ';
$date_shipped=' ';
$blanks=' ';
$discount=' ';
$po=' ';
$cust_dept=' ';
$risk=' ';
$discount_type=' ';
$tax=' ';
$other_charges=' ';
$allowance=' ';
$vendorID=' ';
$spc=' ';
$duns=' ';
$fillwith9="9999";
$fill1=" ";
$rectype="T";
$credit_memo=' ';
$price_code = 'EA';
$space1     = ' ';
$space6     = ' ';
$space12    = ' ';
$pricecode  = 'PE';
$catalognum = ' ';
$vendornum  = ' ';
$ean        = ' ';
$upc        = ' ';
$desc2      = ' ';
$today      = date("mdy");
$gr_total = 0;
$gr_count = 0;
$mycount    = 0;
$address2   = ' ';
$space7     = ' ';
$country    = 'USA';
$invoice_id=0;

$sql = "

select 

'N' as record_type
,arpCustomerOrganizationID 
,arpARInvoiceID
,cmoname
,cmoAddressLine2
,cmoAddressLine1
,cmocity 
,cmoState 
,cmoPostCode 
,cmoCountry 
,cmoPhoneNumber 
,cmoFaxNumber
,cmoEMailAddress
,cmlName
,cmlAddressLine2
,cmlAddressLine1
,cmlCity
,cmlState
,cmlPostCode
,cmlCountry






 from M1_KF.dbo.ARInvoices

left join  M1_KF.dbo.Organizations on cmoOrganizationID=arpCustomerOrganizationID
left join M1_KF.dbo.OrganizationLocations on cmlOrganizationID=arpCustomerOrganizationID and cmlLocationID=arpARInvoiceLocationID
where arpPaymentTermID  in('cn30','ctn30')

";

$sql2 = "
  
select  


'I' as record_type
,arpCustomerOrganizationID
,arpARInvoiceID
,arlARInvoiceLineID
,cast(arlInvoiceQuantity as numeric(10,0)) as qty
,arlUnitOfMeasure
,cast(arlUnitPriceBase as numeric(10,2)) as price
,arlPartID
,arlPartShortDescription

<?php
/**
 * Created by PhpStorm.
 * User: tom
 * Date: 6/14/17
 * Time: 2:25 PM
 */
$cit='removed';
$trade_style='';
$filler=' ';
$price_code='EA';
$upc=' ';
$cusr_part_number=' ';
$vendor_style=' ';
$european_number=' ';
$color=' ';
$size=' ';
$date_shipped=' ';
$blanks=' ';
$discount=' ';
$po=' ';
$cust_dept=' ';
$risk=' ';
$discount_type=' ';
$tax=' ';
$other_charges=' ';
$allowance=' ';
$vendorID=' ';
$spc=' ';
$duns=' ';
$fillwith9="9999";
$fill1=" ";
$rectype="T";
$credit_memo=' ';
$price_code = 'EA';
$space1     = ' ';
$space6     = ' ';
$space12    = ' ';
$pricecode  = 'PE';
$catalognum = ' ';
$vendornum  = ' ';
$ean        = ' ';
$upc        = ' ';
$desc2      = ' ';
$today      = date("mdy");
$gr_total = 0;
$gr_count = 0;
$mycount    = 0;
$address2   = ' ';
$space7     = ' ';
$country    = 'USA';
$invoice_id=0;

$sql = "

select 

'N' as record_type
,arpCustomerOrganizationID 
,arpARInvoiceID
,cmoname
,cmoAddressLine2
,cmoAddressLine1
,cmocity 
,cmoState 
,cmoPostCode 
,cmoCountry 
,cmoPhoneNumber 
,cmoFaxNumber
,cmoEMailAddress
,cmlName
,cmlAddressLine2
,cmlAddressLine1
,cmlCity
,cmlState
,cmlPostCode
,cmlCountry






 from M1_KF.dbo.ARInvoices

left join  M1_KF.dbo.Organizations on cmoOrganizationID=arpCustomerOrganizationID
left join M1_KF.dbo.OrganizationLocations on cmlOrganizationID=arpCustomerOrganizationID and cmlLocationID=arpARInvoiceLocationID
where arpPaymentTermID  in('cn30','ctn30')

";

$sql2 = "
  
select  


'I' as record_type
,arpCustomerOrganizationID
,arpARInvoiceID
,arlARInvoiceLineID
,cast(arlInvoiceQuantity as numeric(10,0)) as qty
,arlUnitOfMeasure
,cast(arlUnitPriceBase as numeric(10,2)) as price
,arlPartID
,arlPartShortDescription


from M1_KF.dbo.ARInvoices
left join M1_KF.dbo.ARInvoiceLines on arlARInvoiceID=arpARInvoiceID
where arpARInvoiceID='$invoice_id' 
 ";
$sql3 = "select  distinct


case when arpInvoiceType=2 then 'C' else 'D' end as record_type
,arpCustomerOrganizationID
,arpARInvoiceID
,(select sum(cast(arlInvoiceQuantity as INT)) as invoice_qty from M1_KF.dbo.ARInvoiceLines where arlARInvoiceID=arpARInvoiceID) inv_qty
,arpInvoiceBalanceBase
,REPLACE(CONVERT(VARCHAR(10), arpInvoiceDate, 1), '/', '') as ship
,REPLACE(CONVERT(VARCHAR(10), arpInvoiceDate, 1), '/', '') as ship
,arpPaymentTermID
,case when arpPaymentTermID='CTN30' then '2%10 Net 30' else 'Net 30' end as pay_terms_desc
,arpFullInvoiceSubtotalBase
,arpARInvoiceLocationID
,REPLACE(CONVERT(VARCHAR(10), arpOrderDate, 1), '/', '') as arpOrderDate
,arlDiscountPercent
,arpFreightAmountBase
,arpShippingMethodID
,UARPTOTALBOXCOUNT
from M1_KF.dbo.ARInvoices
left join
 M1_KF.dbo.ARInvoiceLines on arlARInvoiceID=arpARInvoiceID

 where arpARInvoiceID='$invoice_id'
";
from M1_KF.dbo.ARInvoices
left join M1_KF.dbo.ARInvoiceLines on arlARInvoiceID=arpARInvoiceID
where arpARInvoiceID='$invoice_id' 
 ";
$sql3 = "select  distinct


case when arpInvoiceType=2 then 'C' else 'D' end as record_type
,arpCustomerOrganizationID
,arpARInvoiceID
,(select sum(cast(arlInvoiceQuantity as INT)) as invoice_qty from M1_KF.dbo.ARInvoiceLines where arlARInvoiceID=arpARInvoiceID) inv_qty
,arpInvoiceBalanceBase
,REPLACE(CONVERT(VARCHAR(10), arpInvoiceDate, 1), '/', '') as ship
,REPLACE(CONVERT(VARCHAR(10), arpInvoiceDate, 1), '/', '') as ship
,arpPaymentTermID
,case when arpPaymentTermID='CTN30' then '2%10 Net 30' else 'Net 30' end as pay_terms_desc
,arpFullInvoiceSubtotalBase
,arpARInvoiceLocationID
,REPLACE(CONVERT(VARCHAR(10), arpOrderDate, 1), '/', '') as arpOrderDate
,arlDiscountPercent
,arpFreightAmountBase
,arpShippingMethodID
,UARPTOTALBOXCOUNT
from M1_KF.dbo.ARInvoices
left join
 M1_KF.dbo.ARInvoiceLines on arlARInvoiceID=arpARInvoiceID

 where arpARInvoiceID='$invoice_id'
";

ginerjm · June 15, 2017

Not being able to make sense of the stuff you posted I'll just ask:

Are you doing 3 queries just so that you can output proper headers and details and totals for your report? If so this is not necessary. Just do the query that retrieves everything (all the details as well as the headers) and then use php logic to check when the headers change inside your loop and handle them accordingly. That's the normal way to do this kind of thing. As for the summaries, create php vars for each needed total and do the accumulations as you process each record, re-initializing them if you also need sub-totals for separate groups. You can do that.

kat35601 · June 16, 2017

Yes, I created the three Queries to get the Header, Detail, and Summary. I will change the query and post back because I do not no how to get the while loop to work. I will post back Thanks

ginerjm · June 16, 2017

In the loop you always check the new record values against the previous record's ones - the keys that are the headers that you want to separate your report sections on. If the key value(s) have changed, you output any sub-totals for the just-finished section, and re-init them. Then you output any new headers for the new 'keys'. Finally you output the new records' details. It gets a little more complex if you have multiple headers/breaks in your report but the concept is the same.

This is a pretty basic element of reporting data in any language.

kat35601 · June 16, 2017

Where to begin?

<html>
<head>
</head>
<body>
<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$cit = '1234';
$trade_style = '';
$filler = ' ';
$price_code = 'EA';
$upc = ' ';
$cusr_part_number = ' ';
$vendor_style = ' ';
$european_number = ' ';
$color = ' ';
$size = ' ';
$date_shipped = ' ';
$blanks = ' ';
$discount = ' ';
$po = ' ';
$cust_dept = ' ';
$risk = ' ';
$discount_type = ' ';
$tax = ' ';
$other_charges = ' ';
$allowance = ' ';
$vendorID = ' ';
$spc = ' ';
$duns = ' ';
$fillwith9 = "9999";
$fill1 = " ";
$rectype = "T";
$credit_memo = ' ';
$price_code = 'EA';
$space1 = ' ';
$space6 = ' ';
$space12 = ' ';
$pricecode = 'PE';
$catalognum = ' ';
$vendornum = ' ';
$ean = ' ';
$upc = ' ';
$desc2 = ' ';
$today = date("mdy");
$gr_total = 0;
$gr_count = 0;
$mycount = 0;
$address2 = ' ';
$space7 = ' ';
$country = 'USA';
$invoice_id = 0;
$credit_invoice_total = 0;
$invoice_total = 0;
$invoice_tot = 0;
$invoice_count=0;
$sql = "
SELECT 


arpCustomerOrganizationID 
,arpARInvoiceID
,cmoPhoneNumber 
,cmoFaxNumber
,cmoEMailAddress
,cmoName
,cmoAddressLine2
,cmoAddressLine1
,cmoCity
,cmoState
,cmoPostCode
,cmoCountry
,cmlName
,cmlAddressLine2
,cmlAddressLine1
,cmlCity
,cmlState
,cmlPostCode
,cmlCountry
,arpCustomerOrganizationID
,arlARInvoiceLineID
,cast(arlInvoiceQuantity AS NUMERIC(10,0)) AS qty
,arlUnitOfMeasure
,cast(arlUnitPriceBase AS NUMERIC(10,2)) AS price
,arlPartID
,arlPartShortDescription
,CASE WHEN arpInvoiceType=2 THEN 'C' ELSE 'D' END AS record_type
,arpCustomerOrganizationID
,arpARInvoiceID
,(SELECT sum(cast(arlInvoiceQuantity AS INT)) AS invoice_qty FROM M1_KF.dbo.ARInvoiceLines WHERE arlARInvoiceID=arpARInvoiceID) inv_qty
,arpInvoiceBalanceBase
,REPLACE(CONVERT(VARCHAR(10), arpInvoiceDate, 1), '/', '') AS ship
,REPLACE(CONVERT(VARCHAR(10), arpInvoiceDate, 1), '/', '') AS ship
,arpPaymentTermID
,CASE WHEN arpPaymentTermID='CTN30' THEN '2%10 Net 30' ELSE 'Net 30' END AS pay_terms_desc
,arpFullInvoiceSubtotalBase
,arpARInvoiceLocationID
,REPLACE(CONVERT(VARCHAR(10), arpOrderDate, 1), '/', '') AS arpOrderDate
,arlDiscountPercent
,arpFreightAmountBase
,arpShippingMethodID
,UARPTOTALBOXCOUNT




 FROM M1_KF.dbo.ARInvoices
 LEFT JOIN M1_KF.dbo.ARInvoiceLines ON arlARInvoiceID=arpARInvoiceID
LEFT JOIN  M1_KF.dbo.Organizations ON cmoOrganizationID=arpCustomerOrganizationID
LEFT JOIN M1_KF.dbo.OrganizationLocations ON cmlOrganizationID=arpCustomerOrganizationID AND cmlLocationID=arpARInvoiceLocationID
WHERE arpPaymentTermID  IN('cn30','ctn30')

";


$connect = odbc_connect("removed");
if (!$connect) {
    exit("Connection Failed: " . $connect);
}


$result = odbc_exec($connect, $sql);
if (!$result) {
    exit("Error in SQL");
}


$my_file = 'cit_invoice.di';
$handle = fopen($my_file, 'w+') or die('Cannot open file:  ' . $my_file);
while ($row = odbc_fetch_array($result)) {

    $record_type = $row['record_type'];
    $cust_num = $row['arpCustomerOrganizationID'];
    $name = $row['cmoName'];
    $address = $row['cmoAddressLine2'];
    $city = $row['cmoCity'];
    $state = $row['cmoState'];
    $postal = $row['cmoPostCode'];
    $phone = $row['cmoPhoneNumber'];
    $invoice_id = $row['arpARInvoiceID'];
    $email = $row['cmoEMailAddress'];
    $fax = $row['cmoFaxNumber'];
    $shpName = $row['cmlName'];
    $shpAddressLine2 = $row['cmlAddressLine2'];
    $shpAddressLine1 = $row['cmlAddressLine1'];
    $shpCity = $row['cmlCity'];
    $shpState = $row['cmlState'];
    $shpPostCode = $row['cmlPostCode'];
    $shpCountry = $row['cmlCountry'];
    $shpPostCode = $row['cmlPostCode'];
    $cust_count = count($row['arpCustomerOrganizationID']);
    $addresst = trim($address);
    $mycount = $mycount + 1;
    $record_ty = $row['record_type'];
    $cust = $row['arpCustomerOrganizationID'];
    $invoice_id = $row['arpARInvoiceID'];
    $line_num = $row['arlARInvoiceLineID'];
    $inv_qty = $row['qty'];
    $uom = $row['arlUnitOfMeasure'];
    $price = $row['price'];
    $desc = $row['arlPartShortDescription'];
    $record_type = $row['record_type'];
    $arpCustomerOrganizationID = $row['arpCustomerOrganizationID'];
    $invoice_id = $row['arpARInvoiceID'];
    $inv_qty = $row['inv_qty'];
    $arpInvoiceBalanceBase = $row['arpInvoiceBalanceBase'];
    $ship = $row['ship'];
    $ship1 = $row['ship'];
    $arpPaymentTermID = '030';
    $pay_terms_desc = $row['pay_terms_desc'];
    $arpFullInvoiceSubtotalBase = $row['arpFullInvoiceSubtotalBase'];
    $arpARInvoiceLocationID = $row['arpARInvoiceLocationID'];
    $arpOrderDate = $row['arpOrderDate'];
    $arlDiscountPercent = $row['arlDiscountPercent'];
    $arpFreightAmountBase = $row['arpFreightAmountBase'];
    $arpShippingMethodID = $row['arpShippingMethodID'];
    $UARPTOTALBOXCOUNT = $row['UARPTOTALBOXCOUNT'];

    /*Customer*/
    fprintf($handle, "%-4s%-1s%-1s%-15s%-8s%-5s%-30.30s%-30.30s%-30.30s%-17.17s%-2.2s%-10.10s%17.17s%-15.15s%-15.15s%-9.9s%-40.40s%-30.30s%-30.30s%-30.30s%-17.17s%-2.2s%-10.10s%-17.17s\n", $cit, $trade_style, $record_type, $cust_num, $invoice_id, $space7, $name, $addresst, $address2, $city, $state, $postal, $country, $phone, $fax, $duns, $email, $shpName, $shpAddressLine2, $shpAddressLine1, $shpCity, $shpState, $shpPostCode, $shpCountry);
    /*Invoice*/
    fprintf($handle, "%-4s%-1s%-1s%-15s%-8d%07d%05d%010d%-2s%-17s%-2s%-20s%-20s%-20s%-20s%-30s%-30s%-20s%-20s\n", $cit, $trade_style, $record_ty, $cust, $invoice_id, $space6, $line_num, $inv_qty, $uom, $price, $pricecode, $upc, $catalognum, $vendornum, $ean, $desc, $desc2, $color, $size);
    /*Invoice Summary*/
    fprintf($handle, "%-4s%-1s%-1s%-15s%-8d%-7s%05d%010d%-6s%-6s%-6s%-6s%3s%-30s%-10s%-5s%-22s%-6s%-6s%-1s%-1s%-10s%-8s%-7s%-10s%-10s%-10s%-10s%-15s%-30s%-2s%-6s\n", $cit, $trade_style, $record_type, $arpCustomerOrganizationID, $invoice_id, $filler, $inv_qty, $arpInvoiceBalanceBase * 100, $ship, $ship, $date_shipped, $blanks, $arpPaymentTermID, $pay_terms_desc, $discount, $arpARInvoiceLocationID, $po, $arpOrderDate, $cust_dept, $risk, $discount_type, $arlDiscountPercent, $credit_memo, $filler, $arpFreightAmountBase, $tax, $other_charges, $allowance, $vendorID, $arpShippingMethodID, $spc, $UARPTOTALBOXCOUNT);

    if ($record_type == 'C') {
        $credit_count = count($row['arpARInvoiceID']);
    }

    if ($record_type == 'D') {
        $invoice_count = count($row['arpARInvoiceID']);
    }

    $cust_tot = count($row['arpCustomerOrganizationID']);
    if ($record_type == 'C') {
        $credit_invoice_total += $row['arpInvoiceBalanceBase'];
    }
    if ($record_type == 'D') {
        $invoice_total += $row['arpInvoiceBalanceBase'];
    }
    $credit_tot = number_format($credit_invoice_total, 2);
    $inv_tot = number_format($invoice_total, 2);

}
    $tomtotal = $invoice_total * 100;
    $assignment_num = date('dH');
$assignment_date = date('mdy');

   
 
/*Customer Summary*/
fprintf($handle, "%-4s%-1s%-1s%06d%06d%06d%012d%012d%-4s%-6s%-1s\n", $cit, $filler, $record, $cust_tot, $invoice_count, $credit_count, $invoice_total * 100, $credit_invoice_total * 100, $assignment_num, $assignment_date, $zero);

/* Transmission Summary*/
fprintf($handle, "%-4s%-1s%-1s%06d%06d%06d%012d%012d%-6s", $fillwith9, $fill1, $rectype, $cust_tot, $invoice_count, $credit_count, $invoice_total * 100, $credit_invoice_total * 100, $today);
    

fclose($handle);

?>
</body>
</html>

ginerjm · June 16, 2017

Split up your html and php

Make an html structure that has the headers and static tags and the ending tags and stick a php var or two in the middle (wrapped in the div and table tags you want to use) to represent the TR/TD tags of your table.

Then begin the script by getting the form input and doing the query and running the loop that builds that php var with the TR/TD tags only. That way you don't have to bollux up your php code with intermittent html other than the start and end tags for the rows. Don't use built-in styling - use css only.

Are you actually just outputting a text line instead of an html table?

kat35601 · June 16, 2017

I want have any TR/TD I am not putting anything out to the screen just to the file with the fprintf

ginerjm · June 17, 2017

You DON'T have an TR/TD?

So you just want to write a text file - same things apply as for outputting headers and details and summaries. So what do you want to know from your post 'Where to Begin?'?

gizmola · June 17, 2017

Goodness, start by NOT doing this type of thing:

    $record_type = $row['record_type'];
    $cust_num = $row['arpCustomerOrganizationID'];
    $name = $row['cmoName'];
    $address = $row['cmoAddressLine2'];
    $city = $row['cmoCity'];
    $state = $row['cmoState'];
    $postal = $row['cmoPostCode'];
    $phone = $row['cmoPhoneNumber'];
    $invoice_id = $row['arpARInvoiceID'];
    $email = $row['cmoEMailAddress'];
    $fax = $row['cmoFaxNumber'];
    //etc

A variable is a variable. Why are you copying every column in a row into a similarly named variable? You should be using those variables directly! There is no reason to make another copy of them all with different names.

kat35601 · June 19, 2017

I don't know that's why I was asking. I need to have a header record for each customer and a invoice detailed record then a invoice summary then a summary for the customer then after all for the customers are done I need a summary for all the records. What I can't figure out is how to write a while loop that says for each customer and each invoice fprintf the detail then once you are done fprintf the summary and then go to the next customer and do it again.

ginerjm · June 19, 2017

Can I ask - why do you only want to output this as a plain old text file that has no real formatting to make it a PRESENTABLE report? You are worried about doing these 'fancy' things (headers, totals,etc.) yet the eventual result is definitely NOT fancy. Why a text file that nobody can do anything with? Why not present it as an html table that the user can call up onscreen? Or if you must have a paper-related doc, learn how to use FPDF to generate a PDF file that is definitely better structured than a text file. (This is a whole new learning curve so you may not want to tackle that at this time.)

As I described before - in your loop you CHECK for new headers and put out whatever header line you need, as well as totals for the previous grouping if there was one. It's all in your PHP logic. You have to write the algorithm to recognize the changes in your data as they occur. Remember - your data is sorted in the proper order so all you have to do is LOOK at the data as you loop thru it.

kat35601 · June 19, 2017

The text file is uploaded to a company that has to have it formatted just as it is. I worked really hard on getting the file formatted like they wanted. I can upload one invoice at a time and it works fine. But I need to be able to do it in batches.

How do check for new headers??

That algorithm is what eludes me.

ginerjm · June 19, 2017

the algorithm is something you have to write. Re-read my post that described this and think about it. The way I do it is to keep track of the last key value and then check the new record against that value to see if I have a new break (new invoice) in the data. Then output the last key's sums and reset them to zero. If you have multiple keys/levels, then you have to check multiple "last" keys to see if a secondary break/level has changed. Remember - you have to do this from lowest level to highest. When you do the output of a summary and begin a new 'key' you can also output a new row to show the 'new' value. When done processing the 'break', don't forget to output the new detail row that you just read before looping.

It's an effort. One has to do this over and over in programming so you may as well start doing the hard work now.

kat35601 · June 19, 2017

I just can't seem to get started I have watched all the videos I could find on loops, looked a the php cookbook but I still can not come up with it. Which I think should work like this.

"while loop" as long as $cust_num stays the same

then do invoice "while loop" as long as $invoice_id stays the same

then do invoice/customer summary then go to next $cust_num and do it again until there is no more to do.

is the while loop what I am looking for. how to you check for the change in $cust_num?

Edited June 19, 2017 by kat35601

Jacques1 · June 20, 2017

If you're not able to render the rows directly, simply don't do it. Turn the result set into a more convenient structure which you can actually work with, then render that.

I cannot make sense of your descriptions, but you should understand the data well enough to choose a good format. Putting the rows into this structure will be easy, rendering your own structure is also easy. So no need for complex loops.

kat35601 · June 20, 2017

I ask for help because I don't know how to do it.How do I put the rows into this structure?

Thanks

ginerjm · June 20, 2017

This is how I would do it.

This loop is set up for handling totals at the invoice level and the customer level with final Grand totals for the report.

It is also setup to handle two totals for each.

If you need a third (or more) level, you'll have to follow my example. Same for the number of summaries you need.

Read carefully and UNDERSTAND it before you go further.

The global '$output_var' is the destination for all of your output, whether it is the handle to an output file or the

container to store html code for a table or whatever.

PS - this is un-tested so beware of any possible typos.

$inv_tots['sum1'] = 0;
$inv_tots['sum2'] = 0;

$cust_tots['sum1'] = 0;
$cust_tots['sum2'] = 0;

$grand_tots['sum1'] = 0;
$grand_tots['sum2'] = 0;

$last_cust = 'xyz';
$last_inv = 'xyz';
//
$output_var = '';
//
//  loop on ALL query result records which are all identical in format
while($row = fetch query results row using associative array)
{
	//  do initial headings for first cust/inv
	if ($last_cust == 'xyz')
	{
		ShowCustHeader($row);
		$last_cust = $row['cust_id'];
	}
	if ($last_inv == 'xyz')
	{
		ShowInvHeader($row);
		$last_inv = $row['invoice_id'];
	}
	//************
	// process records now
	if ($row['cust_id'] == $last_cust)
	{
		if ($row['invoice_id'] <> $last_inv)
		{
			// same cust; new invoice
			//  finish off last invoice and accum totals for cust
			ShowInvTotals($last_inv, $inv_tots, $cust_tots);
			//  show new header
			ShowInvHeader($row);
		}
	}
	else	//  cust id has changed
	{
		//  new customer 
		//  finish off last cust last inv and accum totals
		ShowInvTotals($last_inv, $inv_tots, $cust_tots);
		//  show cust totals and accum grand (report) totals
		ShowCustTotals($last_cust, $cust_tots, $grand_tots)
		//  show new headers
		ShowCustHeader($row);
		ShowInvHeader($row);
	}
	//  now output the current detail record line
	ShowDetail($row, $inv_tots);
	$last_inv = $row['invoice_id'];
	$last_cust = $row['cust_id'];
}	
//   show final totals for last inv and last cust
ShowInvTotals($last_inv, $inv_tots, $cust_tots);
ShowCustTotals($last_cust, $cust_tots, $grand_tots);
ShowGrandTotals($grand_tots);
//*********************
//*********************
//  All done with report of query results
//  handle your $output_var now.
//*********************
//*********************
//
//****************************
//****************************
//****************************
//***************************
function ShowDetail($row, &$inv_tots)
{
        global $output_var;
	//  generate a normal detail row of output for current invoice/cust

	//  accum invoice totals
	$inv_tots['sum1'] += $row['value1'];
	$inv_tots['sum2'] += $row['value2'];
	return;
}
function ShowInvTotals($last_inv, &$inv_tots, &$cust_tots)
{
        global $output_var;
	//  generate the output line for an invoice total 

	//  accum the inv totals to the cust totals
	$cust_tots['sum1'] += $inv_tots['sum1'];
	$cust_tots['sum2'] += $inv_tots['sum2'];
	//  reset invoice tots
	$inv_tots['sum1'] = 0;
	$inv_tots['sum2'] = 0;
	return;
}
function ShowCustTotals($last_cust, &$cust_tots, &$grand_tots)
{
        global $output_var;

	//  generate the output line for an customer total 

	//  accum the cust totals to the grand totals
	$grand_tots['sum1'] += $cust_tots['sum1'];
	$grand_tots['sum2'] += $cust_tots['sum2'];
	//  reset cust tots
	$cust_tots['sum1'] = 0;
	$cust_tots['sum2'] = 0;
	return;
}
function ShowCustHeader($row)
{
        global $output_var;
	//  generate the output line you need for a customer header
	
	return;
}
function ShowInvHeader($row)
{
        global $output_var;
	//  generate the output line you need for an invoice header
	
	return;
}

Jacques1 · June 20, 2017

How do I put the rows into this structure?

What structure? Did you even read the reply? Because your question makes absolutely no sense.

I said that's it's your job to design a data structure. You know the data, you know how the output is supposed to look like, and you know what's most convenient for you. So you're the one who needs to tell us which structure you want.

When you've done that, then we can talk about putting the data into your structure. But right now, there isn't even a structure, because you haven't done anything yet -- as far as I'm aware.

ginerjm · June 20, 2017

Hmmm...

OP,

I gave you the loop that will process your query results. No comment? Or can't you make sense of it?

kat35601 · June 21, 2017

I am going through your post right now. Thanks

kat35601 · June 21, 2017

I looked at your post and I don't think I can translate that into what I need/have.

I have all the output as needed, the query works fine the fprintf's work fine.

The problem is when I run the page it comes out with all the customer name and address records together then all the invoice information(line Items) together and then the invoice summary information and then the customer summary and then the transmission summary.

        /*Customer*/
        fprintf($handle, "%-4s%-1s%-1s%-15s%-8s%-5s%-30.30s%-30.30s%-30.30s%-17.17s%-2.2s%-10.10s%17.17s%-15.15s%-15.15s%-9.9s%-40.40s%-30.30s%-30.30s%-30.30s%-17.17s%-2.2s%-10.10s%-17.17s\n", $cit, $trade_style, $record_type, $cust_num, $invoice_id, $space7, $name, $addresst, $address2, $city, $state, $postal, $country, $phone, $fax, $duns, $email, $shpName, $shpAddressLine2, $shpAddressLine1, $shpCity, $shpState, $shpPostCode, $shpCountry);
        /*Invoice*/
        fprintf($handle, "%-4s%-1s%-1s%-15s%-8d%07d%05d%010d%-2s%-17s%-2s%-20s%-20s%-20s%-20s%-30s%-30s%-20s%-20s\n", $cit, $trade_style, $record_ty, $cust, $invoice_id, $space6, $line_num, $inv_qty, $uom, $price, $pricecode, $upc, $catalognum, $vendornum, $ean, $desc, $desc2, $color, $size);
        /*Invoice Summary*/
        fprintf($handle, "%-4s%-1s%-1s%-15s%-8d%-7s%05d%010d%-6s%-6s%-6s%-6s%3s%-30s%-10s%-5s%-22s%-6s%-6s%-1s%-1s%-10s%-8s%-7s%-10s%-10s%-10s%-10s%-15s%-30s%-2s%-6s\n", $cit, $trade_style, $record_type, $arpCustomerOrganizationID, $invoice_id, $filler, $inv_qty, $arpInvoiceBalanceBase * 100, $ship, $ship, $date_shipped, $blanks, $arpPaymentTermID, $pay_terms_desc, $discount, $arpARInvoiceLocationID, $po, $arpOrderDate, $cust_dept, $risk, $discount_type, $arlDiscountPercent, $credit_memo, $filler, $arpFreightAmountBase, $tax, $other_charges, $allowance, $vendorID, $arpShippingMethodID, $spc, $UARPTOTALBOXCOUNT);
        /*Customer Summary*/
        fprintf($handle, "%-4s%-1s%-1s%06d%06d%06d%012d%012d%-4s%-6s%-1s\n", $cit, $filler, $record, $cust_tot, $invoice_count, $credit_count, $invoice_total * 100, $credit_invoice_total * 100, $assignment_num, $assignment_date, $zero);
        /* Transmission Summary*/
        fprintf($handle, "%-4s%-1s%-1s%06d%06d%06d%012d%012d%-6s", $fillwith9, $fill1, $rectype, $cust_tot, $invoice_count, $credit_count, $invoice_total * 100, $credit_invoice_total * 100, $today);

Below would be correct output example

D name/address

I line item

N invoice summary record

D name/address

I line item

N invoice summary record

D name/address

I line item

N invoice summary record

S assignment total

T transmission total

ginerjm · June 21, 2017

My sample works regardless of what your current query results look like. That is why I provided no sample of the actual output - I left it for you to stick in the appropriate output lines. It simply does the things that YOU need while looping thru those same records.

Look at it again. If you don't understand what it is doing I suggest that your php skills aren't up to this task.

IF you decide that my code doesn't work for you, you have no idea what you are doing. All of the records in your query results are identical. The logic simply decides when to insert the needed summary lines and when to print the actual details. That's all.

PS - What 'translate' do you think needs to be done? All you have to do is substitute the particular variable names in for the ones I made up. About 2 of them.

Actually - 4 of them.

PPS - My code will generate EXACTLY the output that you last sample proposed to show, including multiple invoices for the same customer which your sample does not show.

Edited June 21, 2017 by ginerjm

kat35601 · June 21, 2017

you are correct my php skills are not up to the task that's the reason I ask so I can learn. thank you for teaching me.

ginerjm · June 21, 2017

So - does the code I've given you make any sense to you? If not you need to start at the beginning and read a book.

kat35601 · June 21, 2017

I'll go read a book thanks for the help.

Sign In

I have three SQL queries that I need to run to get Header/Detail/Summary records

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information