Jump to content

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

kat35601

By kat35601
in PHP Coding Help

Recommended Posts

kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

i guess my problem is to take what I have that works great as long as I run it for one invoice at a time and have to rewrite it. It took me a long time to get the spacing just right in the  fprintfs.

You would think that it would be easy to add a little flow control. But anyway.

 

so i am going to create an associative array from the array I get when I run my query. 

 

when you say "generate a normal detail row of output for current invoice/cust" this will be the fprintf that have updated form the associative array right and I will of course do this for all of the functions.

 and that should do it?

Link to comment
Share on other sites
ginerjm Prolific Member

ginerjm

Posted
Posted

I GAVE YOU THE FLOW CONTROL!

 

You already have an associative array from your query!!!

 

 

Yes - your printf is the output line - with alterations for the $row values instead of individual vars. You could move them of course to make it a less error-prone exercise.

Link to comment
Share on other sites
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

am I on the right track

<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'];
    $last_cust = 'xyz';
    $last_inv = 'xyz';


    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');


    $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 = '';


    //  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);
}
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

    // Invoice Summary
    //  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( $cit, $filler, $record, $cust_tot, $invoice_count, $credit_count, $invoice_total , $credit_invoice_total , $assignment_num, $assignment_date, $zero)
{
    global $output_var;
    //  generate the output line you need for a customer header

    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);

    return;
}
function ShowInvHeader($cit, $trade_style, $record_type, $arpCustomerOrganizationID, $invoice_id, $filler, $inv_qty, $arpInvoiceBalanceBase , $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)
{
    global $output_var;
    //  generate the output line you need for an invoice header

    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);

    return;
}

   //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>
Link to comment
Share on other sites
ginerjm Prolific Member

ginerjm

Posted
Posted

I expected to see MY code with your printf statements inserted. What is all that code you JUST POSTED that you were ALREADY told was inappropriate and unnecessary?

 

You never corrected me when I said that all of your records were the same. Apparently they are not judging by the presence of a record_type field.

 

YOu are not really a programmer, are you? You seem to just like to cut & paste stuff and then ask if it is right. Where in h... is cust_id defined, except in my code?

 

STOP COPYING QUERY RESULTS (IE, $ROW[]) INTO TEMPORARY PHP VARIABLES. IT IS A WASTE OF RESOURCES AND CODING TIME (IE, TYPING) AND SERVES ABSOLUTELY NO PURPOSE. WHY CAN'T YOU JUST USE THE $ROW VARIABLES????

JUST LOOK AT ALL THE CODE YOU HAVE CREATED FOR NO GOOD REASON.

 

As a programmer you should think. Why do all that effort at the beginning of your script only to check for a db connection later on and aborting it if it fails. DO IT FIRST!

Plus - do all the php work FIRST. You put out some brief html code at the beginning. Why??? You are not even sending any output to the client, are you?

 

As I previously said - you should alter your printf statements to output the $row values so that you don't have to carry the current (unnecessary) temp vars in the function's stack.

 

When (and if) you ever become an even mediocre programmer you will finally see what a waster of time and effort you currently are.

 

Most important - you altered my code from what I wrote for absolutely no reason. I do not think there was a single line of my post that needed your expert opinion added to it. Start over with my code - after you have fixed your own code as suggested by forum members already. You have messed up my sample complete.

Link to comment
Share on other sites
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

The record type is for the folks I upload the file to credit invoice or debit invoice.

 

and WOW I am sorry that I am not an expert programmer if I knew what i was doing I would not have ask.

 

and if you don't want to help someone then why are you posting.

 

I don't understand, you ask a question because you don't know and when you don't know you jump on them.

 

You have a good day.

Link to comment
Share on other sites
ginerjm Prolific Member

ginerjm

Posted
Posted

My frustration with you is you don't listen to what people are telling you. You also do things that are uncalled for like changing my code from what I gave you. Why did you move the initial setting of my totals to be inside the loop? Do you even have a clue of how to program? I don't think so.

 

You were told to do things that you haven't even attempted to do yet that are silly and wasteful. Go do them!

 

Put my code back the way I presented it to you and THEN ADD YOUR CORRECTED CODE TO IT WITHOUT CHANGING ANY OF MY CODE. That will be your test of becoming a programmer.

 

Oh - and if you don't want to become a programmer, just let us know so that we can stop trying to help you. Afterall, this forum is for programmers and not hobbyists who think that working on a PHP project is actually nothing more than that.

 

Sorry if I have hurt your feelings but someday you may actually look back and realize what you have done here and how deserving you were of my response.

Link to comment
Share on other sites
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted 
<?php

$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';
$arpPaymentTermID = '030';
/**
 * Created by PhpStorm.
 * User: tom
 * Date: 6/20/17
 * Time: 12:53 PM
 */
$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 = '';

$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')

";

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


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


$result = odbc_exec($connect, $sql);
if (!$result) {
    exit("Error in SQL");
}
//  loop on ALL query result records which are all identical in format
while($row = $row = odbc_fetch_array($result))
{
    //  do initial headings for first cust/inv
    if ($last_cust == 'xyz')
    {
        ShowCustHeader($row);
        $last_cust = $row['arpCustomerOrganizationID'];
    }
    if ($last_inv == 'xyz')
    {
        ShowInvHeader($row);
        $last_inv = $row['arpARInvoiceID'];
    }
    //************
    // process records now
    if ($row['arpCustomerOrganizationID'] == $last_cust)
    {
        if ($row['arpARInvoiceID'] <> $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['arpARInvoiceID'];
    $last_cust = $row['arpCustomerOrganizationID'];
}
//   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['arpInvoiceBalanceBase'];
    $inv_tots['sum2'] += $row['arpInvoiceBalanceBase'];
    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
    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, $tradestyle, $row['record_type'], $row['arpCustomerOrganizationID'], $row['arpARInvoiceID'], $space7, $row['cmoName'],$row['cmoAddressLine2'],$row['cmoAddressLine2'], $row['cmoCity'], $row['cmoState'], $row['cmoPostCode'], $country, $row['cmoPhoneNumber'], $row['cmoFaxNumber'], $duns, $row['cmoEMailAddress'], $row['cmlName'], $row['cmlAddressLine2'], $row['cmlAddressLine1'], $row['cmlCity'], $row['cmlState'], $row['cmlPostCode'], $shpCountry);

    return;
}
function ShowInvHeader($row)
{
    global $output_var;
    //  generate the output line you need for an invoice header

    return;
}

fclose($handle);

?>

I am working on just the customer header but variables like $cit and $handle show undefined why?

Link to comment
Share on other sites
ginerjm Prolific Member

ginerjm

Posted
Posted

Again - why have you moved my totals initiation up to some totally unrelated spot in your code? It s/b right where I place it so that somebody else reading thijs code can make sense of it.

 

Have you read anything on variable scope?  In PHP variables defined in a function are only good IN that function.  If you use a variable in a function that is not defined there, then you need to either pass it in as an argument, or declare it 'global'.  Many prefer not to use global, but personally I use it when I have a var that is going to be used in multiple functions and I don't want to have to pass it in every time.  

 

At the beginning of your code you define $cit as 1234.  What are all those other vars that you are setting to a space good for?  GET RID OF THEM!!!!!!!!  STOP USING THEM!!!!!!!!

Link to comment
Share on other sites
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

I got it working I need to add a few more functions but I will not have time to work on it again until Monday. Thank you for your help.

<?php

$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';
$arpPaymentTermID = '030';
/**
 * Created by PhpStorm.
 * User: tom
 * Date: 6/20/17
 * Time: 12:53 PM
 */


$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')

";

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


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


$result = odbc_exec($connect, $sql);
if (!$result) {
    exit("Error in SQL");
}
$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 = $row = odbc_fetch_array($result))
{
    //  do initial headings for first cust/inv
    if ($last_cust == 'xyz')
    {
        ShowCustHeader($row);
        $last_cust = $row['arpCustomerOrganizationID'];
    }
    if ($last_inv == 'xyz')
    {
        ShowInvHeader($row);
        $last_inv = $row['arpARInvoiceID'];
    }
    //************
    // process records now
    if ($row['arpCustomerOrganizationID'] == $last_cust)
    {
        if ($row['arpARInvoiceID'] <> $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['arpARInvoiceID'];
    $last_cust = $row['arpCustomerOrganizationID'];
}
//   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['arpInvoiceBalanceBase'];
    $inv_tots['sum2'] += $row['arpInvoiceBalanceBase'];
    return;
}
function ShowInvTotals($last_inv, &$inv_tots, &$cust_tots)
{
    global $output_var;

    $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;
    global $handle;
    global $cit;
    global $tradestyle;
    global $space7;
    global $country;
    global $shpCountry;
    global $duns;

    //  generate the output line you need for a customer header
    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, $tradestyle, $row['record_type'], $row['arpCustomerOrganizationID'], $row['arpARInvoiceID'], $space7, $row['cmoName'],$row['cmoAddressLine2'],$row['cmoAddressLine2'], $row['cmoCity'], $row['cmoState'], $row['cmoPostCode'], $country, $row['cmoPhoneNumber'], $row['cmoFaxNumber'], $duns, $row['cmoEMailAddress'], $row['cmlName'], $row['cmlAddressLine2'], $row['cmlAddressLine1'], $row['cmlCity'], $row['cmlState'], $row['cmlPostCode'], $shpCountry);

    return;
}
function ShowInvHeader($row)
{
    global $output_var;
    global $handle;
    global $cit;
    global $tradestyle;
    global $upc, $catalognum, $vendornum, $color, $size,$space6,$ean;
    
    fprintf($handle, "%-4s%-1s%-1s%-15s%-8d%07d%05d%010d%-2s%-17s%-2s%-20s%-20s%-20s%-20s%-30s%-30s%-20s%-20s\n", $cit, $tradestyle, $row['record_type'], $row['arpCustomerOrganizationID'],  $row['arpARInvoiceID'], $space6, $row['arlARInvoiceLineID'],$row['qty'], $row['arlUnitOfMeasure'], $row['price'], $row['price'], $upc, $catalognum, $vendornum, $ean, $row['arlPartShortDescription'], $row['arlPartShortDescription'], $color, $size);
    //  accum the inv totals to the cust totals

    return;
}

fclose($handle);

?>
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.