Location: PHPKode > projects > NOLA > arinvoicestateview.php
<?
  require_once('includes/defines.php');
  require_once('includes/adodb/adodb.inc.php');
  require_once('includes/functions.php');
  ADOLoadCode(DB_TYPE);
  $conn = &ADONewConnection();
  $conn->PConnect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);

    /*************** arinvoicestateview.php
    This script writes ar invoices to an PDF file.
    *****************/
    $timestamp =  time();
    $today=date("Y-m-d", $timestamp);

/*** Get a random number to use for the file name  ***/
mt_srand((double)microtime() * 1000000);
$rand_nbr = mt_rand();
if (!isset($font)) $font="Times-Roman";
if (!isset($basesize)) $basesize="16";

/*** Begin our pdf file  ***/
$filename=IMAGE_UPLOAD_DIR."tempinv$rand_nbr.pdf";
$pdf = pdf_new();
if (!pdf_open_file($pdf, "")) {
    print error;
    exit;
};


//begin pdf header info
pdf_set_parameter($pdf, "warning", "true");
pdf_set_info($pdf, "Creator", "arinvoicestateview.php");
pdf_set_info($pdf, "Author", "NOLA Software");
pdf_set_info($pdf, "Title", "Invoice Statements");


unset($invstr);
unset($companystr);
unset($daysstr);
if ($inv) $invstr.=' and arinvoice.duedate<=NOW()';
if ($customerid) {
    $recordSet = $conn->Execute("select companyid from customer where id=".sqlprep($customerid));
    if ($recordSet&&!$recordSet->EOF) $obcompanyid=$recordSet->fields[0];
    if ($obcompanyid) $companystr=' and arinvoice.orderbycompanyid='.sqlprep($obcompanyid);
};
if ($invdays) $daysstr.=' and arinvoice.invoicedate<=DATE_SUB('.sqlprep($today).', INTERVAL '.sqlprep($invdays).' DAY)';
$recordSet = $conn->Execute("select distinct customer.id, customer.companyid, company.companyname, customer.billtoattnname, company.address1, company.address2, company.city, company.state, company.zip from arinvoice,customer,company where customer.companyid=arinvoice.orderbycompanyid and customer.companyid=company.id and arinvoice.status<2 and arinvoice.cancel=0".$companystr.$invstr.$daysstr.' order by customer.id');
if (!$recordSet||$recordSet->EOF) die(texterror('No matching invoices found.'));
$recordSet2 = $conn->Execute("select customer.id, arinvoice.invoicenumber, arinvoice.orderbycompanyid,arinvoice.invoicetotal,arinvoice.invoicedate,arinvoice.accruedinterest from arinvoice left join customer on customer.companyid=arinvoice.orderbycompanyid where arinvoice.status<2 and arinvoice.status>=0 and arinvoice.cancel=0".$companystr.' order by customer.id, arinvoice.invoicedate');
if (!$recordSet2||$recordSet2->EOF) die(texterror('No matching invoices found.'));
$recordSet3 = $conn->Execute("select customer.id, arinvoice.invoicenumber, arinvoicepaymentdetail.amount, arinvoicepaymentdetail.datereceived from arinvoice,arinvoicepaymentdetail left join customer on customer.companyid=arinvoice.orderbycompanyid where arinvoice.id=arinvoicepaymentdetail.invoiceid and arinvoice.status<2 and arinvoice.status>=0 and arinvoice.cancel=0".$companystr.' order by customer.id, arinvoice.invoicedate');

while (!$recordSet->EOF) {
        $customerid=$recordSet->fields[0];
        unset($custamount);
        /*** Here's where we write to our PDF file  ***/
        pdf_begin_page($pdf, 612, 792);  //8.5x11
        if (strlen($companyname)<30) {
            pdf_set_font($pdf, $font, $basesize+8, "host");
        } else {
            pdf_set_font($pdf, $font, $basesize+6, "host");
        };
        pdf_show_xy($pdf, 'Statement From '.$companyname, 54, 736);
        pdf_set_font($pdf, $font, $basesize-2, "host");
        pdf_show_xy($pdf, 'Date: '.chop(createtime('m/d/Y')), 428, 684);
        if ($recordSet->fields[0]) pdf_continue_text($pdf, 'Account: '.chop($recordSet->fields[0])); //customerid
        pdf_show_xy($pdf, chop($recordSet->fields[2]), 126, 684); //customername
        if ($recordSet->fields[3]) pdf_continue_text($pdf, 'Attn: '.chop($recordSet->fields[3])); //attn
        if ($recordSet->fields[4]) pdf_continue_text($pdf, chop($recordSet->fields[4])); //addr1
        if ($recordSet->fields[5]) pdf_continue_text($pdf, chop($recordSet->fields[5])); //addr2
        if ($recordSet->fields[6]||$recordSet->fields[7]||$recordSet->fields[8]) pdf_continue_text($pdf, chop($recordSet->fields[6]).', '.chop($recordSet->fields[7]).' '.chop($recordSet->fields[8])); //city,state,zip

        pdf_set_font($pdf, $font, $basesize, "host");
        pdf_setcolor($pdf, 'fill', 'gray', 0.75); //set color to 30% gray
        pdf_rect ($pdf, 54, 561, 72, 15);
        pdf_rect ($pdf, 126, 561, 360, 15);
        pdf_rect ($pdf, 486, 561, 72, 15);
        pdf_fill_stroke ($pdf);

        pdf_setcolor($pdf, 'fill', 'gray', 0); //set color to black
        pdf_set_font($pdf, $font, $basesize-1, "host");
        unset($width);
        $width=pdf_stringwidth($pdf, 'Date');
        pdf_show_xy($pdf, 'Date', (126+54-$width)/2, 565); //date
        unset($width);
        $width=pdf_stringwidth($pdf, 'Charges and Credits');
        pdf_show_xy($pdf, 'Charges and Credits', (486+126-$width)/2, 565); //charges
        unset($width);
        $width=pdf_stringwidth($pdf, 'Amount');
        pdf_show_xy($pdf, 'Amount', (558+486-$width)/2, 565); //amount

        pdf_set_font($pdf, $font, $basesize-2, "host");
        $i=1;
        while ($customerid==$recordSet2->fields[0]||$customerid==$recordSet3->fields[0]) {
            unset($customerid2);
            unset($entrydate2);
            if (!$recordSet2->EOF) {
                $customerid2=$recordSet2->fields[0];
                $entrydate2=$recordSet2->fields[4]; //invoice date
                $amount2=$recordSet2->fields[3];
            };
            unset($customerid3);
            unset($entrydate3);
            if (!$recordSet3->EOF) {
                $customerid3=$recordSet3->fields[0];
                $entrydate3=$recordSet3->fields[3]; //payment date
                $amount3=$recordSet3->fields[2];
            };
            unset($amount);
            unset($entrydate);
            unset($desc);
            if ($customerid==$customerid2&&$customerid==$customerid3) {   //payment and invoice for customer
                if ($entrydate2>$entrydate3) {  //invoice date after payment date then print payment next
                    $entrydate=$entrydate3;
                    $amount=$amount3.'CR';
                    $custamount-=$amount3;
                    $desc='Payment - Invoice #'.$recordSet3->fields[1].' - Thank you!';
                    $recordSet3->MoveNext();
                } else { //print invoice info.
                    $entrydate=$entrydate2;
                    $amount=$amount2;
                    $custamount+=$amount2;
                    $interest+=$recordSet2->fields[5];
                    $desc='Invoice #'.$recordSet2->fields[1];
                    $recordSet2->MoveNext();
                };
            } elseif ($customerid==$customerid2) { //only invoice for customer
                    $entrydate=$entrydate2;
                    $amount=$amount2;
                    $custamount+=$amount2;
                    $interest+=$recordSet2->fields[5];
                    $desc='Invoice #'.$recordSet2->fields[1];
                    $recordSet2->MoveNext();
            } elseif ($customerid==$customerid3) { //only payment for customer
                    $entrydate=$entrydate3;
                    $amount=$amount3.'CR';
                    $custamount-=$amount3;
                    $desc='Payment - Invoice #'.$recordSet3->fields[1].' - Thank you!';
                    $recordSet3->MoveNext();
            };
            if ($amount) {
                pdf_rect ($pdf, 54, 561-(15*$i), 72, 15);
                pdf_rect ($pdf, 126, 561-(15*$i), 360, 15);
                pdf_rect ($pdf, 486, 561-(15*$i), 72, 15);
                pdf_stroke ($pdf);
                unset($width);
                $width=pdf_stringwidth($pdf, $entrydate);
                pdf_show_xy($pdf, $entrydate, (126+54-$width)/2, 565-(15*$i)); //date
                unset($width);
                $width=pdf_stringwidth($pdf, $desc);
                pdf_show_xy($pdf, $desc, 130, 565-(15*$i)); //charges
                unset($width);
                $width=pdf_stringwidth($pdf, CURRENCY_SYMBOL.$amount);
                pdf_show_xy($pdf, CURRENCY_SYMBOL.$amount, 554-$width, 565-(15*$i)); //amount
                $i++;
            };
        };
        if ($interest) {
            $width=pdf_stringwidth($pdf, 'Interest: ');
            pdf_show_xy($pdf, 'Interest: ', 486-$width, 565-(15*$i)); //charges
            pdf_rect ($pdf, 486, 561-(15*$i), 72, 15);
            pdf_stroke ($pdf);
            unset($width);
            $width=pdf_stringwidth($pdf, CURRENCY_SYMBOL.num_format($interest,PREFERRED_DECIMAL_PLACES));
            pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($interest,PREFERRED_DECIMAL_PLACES), 554-$width, 565-(15*$i)); //amount
            $i++;
        };
        unset($width);
        if ($custamount+$interest<0) {
            $desc='Credit: ';
        } else {
            $desc='Balance Due: ';
        };
        $width=pdf_stringwidth($pdf, $desc);
        pdf_show_xy($pdf, $desc, 486-$width, 565-(15*$i)); //charges
        pdf_rect ($pdf, 486, 561-(15*$i), 72, 15);
        pdf_stroke ($pdf);
        unset($width);
        $width=pdf_stringwidth($pdf, CURRENCY_SYMBOL.num_format($custamount+$interest,PREFERRED_DECIMAL_PLACES));
        pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($custamount+$interest,PREFERRED_DECIMAL_PLACES), 554-$width, 565-(15*$i)); //amount
        $i++;

        pdf_set_font($pdf, $font, $basesize-2, "host");
        unset($width);
        $width=pdf_stringwidth($pdf, 'Current Balance: ');
        pdf_show_xy($pdf, 'Current Balance: ', 450-$width, 628); //balance
        unset($width);
        $width=pdf_stringwidth($pdf, 'Amount Enclosed: ');
        pdf_show_xy($pdf, 'Amount Enclosed: ', 450-$width, 610); //amount
        pdf_rect ($pdf, 450, 624, 108, 18);
        pdf_rect ($pdf, 450, 606, 108, 18);
        pdf_stroke ($pdf);
        pdf_set_font($pdf, $font, $basesize, "host");
        unset($width2);
        $width2=pdf_stringwidth($pdf, CURRENCY_SYMBOL.num_format($custamount+$interest,PREFERRED_DECIMAL_PLACES));
        pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($custamount+$interest,PREFERRED_DECIMAL_PLACES), 446+$width-$width2, 628); //balance
        pdf_show_xy($pdf, CURRENCY_SYMBOL, 454, 610); //amount

        $timestamp=time();
        $date_time_array=getdate($timestamp);
        $hours=$date_time_array["hours"];
        $minutes=$date_time_array["minutes"];
        $seconds=$date_time_array["seconds"];
        $month=$date_time_array["mon"];
        $day=$date_time_array["mday"];
        $year=$date_time_array["year"];
        $timestamp=mktime($hour, $minute, $second, $month, $day, $year);
        $today=date("Y-m-d", $timestamp);
        $timestamp=mktime($hour, $minute, $second, $month, $day-31, $year);
        $monthago=date("Y-m-d", $timestamp);
        $timestamp=mktime($hour, $minute, $second, $month, $day-61, $year);
        $twomonthago=date("Y-m-d", $timestamp);
        $timestamp=mktime($hour, $minute, $second, $month, $day-91, $year);
        $threemonthago=date("Y-m-d", $timestamp);
        $timestamp=mktime($hour, $minute, $second, $month, $day-121, $year);
        $fourmonthago=date("Y-m-d", $timestamp);
        $recordSet4 = &$conn->Execute('select sum(arinvoice.invoicetotal)-sum(arinvoicepaymentdetail.amount) from arinvoice left join arinvoicepaymentdetail on arinvoice.id=arinvoicepaymentdetail.invoiceid where arinvoice.orderbycompanyid='.sqlprep($recordSet->fields[1]).' and arinvoice.invoicedate>'.sqlprep($monthago).' and arinvoice.status<2 and arinvoice.cancel=0');
        if ($recordSet4&&!$recordSet4->EOF) if ($recordSet4->fields[0]) $current=$recordSet4->fields[0];
        $recordSet4 = &$conn->Execute('select sum(arinvoice.invoicetotal)-sum(arinvoicepaymentdetail.amount) from arinvoice left join arinvoicepaymentdetail on arinvoice.id=arinvoicepaymentdetail.invoiceid where arinvoice.orderbycompanyid='.sqlprep($recordSet->fields[1]).' and arinvoice.invoicedate>'.sqlprep($twomonthago).' and arinvoice.invoicedate<='.sqlprep($monthago).' and arinvoice.status<2 and arinvoice.cancel=0');
        if ($recordSet4&&!$recordSet4->EOF) if ($recordSet4->fields[0]) $monthagototal=$recordSet4->fields[0];
        $recordSet4 = &$conn->Execute('select sum(arinvoice.invoicetotal)-sum(arinvoicepaymentdetail.amount) from arinvoice left join arinvoicepaymentdetail on arinvoice.id=arinvoicepaymentdetail.invoiceid where arinvoice.orderbycompanyid='.sqlprep($recordSet->fields[1]).' and arinvoice.invoicedate>'.sqlprep($threemonthago).' and arinvoice.invoicedate<='.sqlprep($twomonthago).' and arinvoice.status<2 and arinvoice.cancel=0');
        if ($recordSet4&&!$recordSet4->EOF) if ($recordSet4->fields[0]) $twomonthagototal=$recordSet4->fields[0];
        $recordSet4 = &$conn->Execute('select sum(arinvoice.invoicetotal)-sum(arinvoicepaymentdetail.amount) from arinvoice left join arinvoicepaymentdetail on arinvoice.id=arinvoicepaymentdetail.invoiceid where arinvoice.orderbycompanyid='.sqlprep($recordSet->fields[1]).' and arinvoice.invoicedate>'.sqlprep($fourmonthago).' and arinvoice.invoicedate<='.sqlprep($threemonthago).' and arinvoice.status<2 and arinvoice.cancel=0');
        if ($recordSet4&&!$recordSet4->EOF) if ($recordSet4->fields[0]) $threemonthagototal=$recordSet4->fields[0];
        $recordSet4 = &$conn->Execute('select sum(arinvoice.invoicetotal)-sum(arinvoicepaymentdetail.amount) from arinvoice left join arinvoicepaymentdetail on arinvoice.id=arinvoicepaymentdetail.invoiceid where arinvoice.orderbycompanyid='.sqlprep($recordSet->fields[1]).' and arinvoice.invoicedate<='.sqlprep($fourmonthago).' and arinvoice.status<2 and arinvoice.cancel=0');
        if ($recordSet4&&!$recordSet4->EOF) if ($recordSet4->fields[0]) $overthreemonthagototal=$recordSet4->fields[0];

        if ($current||$monthagototal||$twomonthagototal||$threemonthagototal||$overthreemonthtotal) {
            pdf_show_xy($pdf, 'Current', 72, 72);
            pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($current+$interest,PREFERRED_DECIMAL_PLACES), 72, 52);
        };
        if ($monthagototal||$twomonthagototal||$threemonthagototal||$overthreemonthtotal) {
            pdf_show_xy($pdf, '30 Days', 172, 72);
            pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($monthagototal,PREFERRED_DECIMAL_PLACES), 172, 52);
        };
        if ($twomonthagototal||$threemonthagototal||$overthreemonthtotal) {
            pdf_show_xy($pdf, '60 Days', 272, 72);
            pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($twomonthagototal,PREFERRED_DECIMAL_PLACES), 272, 52);
        };
        if ($threemonthagototal||$overthreemonthtotal) {
            pdf_show_xy($pdf, '90 Days', 372, 72);
            pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($threemonthagototal,PREFERRED_DECIMAL_PLACES), 372, 52);
        };
        if ($overmonthagototal) {
            pdf_show_xy($pdf, '120 + Days', 472, 72);
            pdf_show_xy($pdf, CURRENCY_SYMBOL.num_format($overthreemonthagototal,PREFERRED_DECIMAL_PLACES), 172, 52);
        };
        unset($addrstr);
        $recordSet4 = &$conn->Execute('select address1, address2, city, state, zip from gencompany where id='.sqlprep($active_company));
        if ($recordSet4&&!$recordSet4->EOF) {
            $addrstr.=$recordSet4->fields[0].' ';
            if ($recordSet4->fields[1]) $addrstr.=$recordSet4->fields[1].' ';
            $addrstr.=$recordSet4->fields[2].', '.$recordSet4->fields[3].' '.$recordSet4->fields[4];
        };
        pdf_set_font($pdf, $font, $basesize-6, "host");
        $width=pdf_stringwidth($pdf, 'Please remit to: '.$companyname.' '.$addrstr);
        pdf_show_xy($pdf, 'Please remit to: '.$companyname.' '.$addrstr, (612-$width)/2, 24);

        pdf_end_page($pdf);
        $recordSet->MoveNext();
};

/*  And now refresh to the populated PDF file  */
pdf_close($pdf);
$buf = pdf_get_buffer($pdf);
$len = strlen($buf);
$fp = fopen ($filename, "w");
fputs($fp,$buf,$len);
fclose($fp);
pdf_delete($pdf);
echo '<META HTTP-EQUIV="Refresh" CONTENT="0;URL=http://'.getenv(SERVER_NAME).'/'.$filename.'">';
?>
Return current item: NOLA