projects / fa-stable.git / blobdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | inline | side by side
[0004216] Print Work Orders: database error fixed when voided WO is in selected range.
[fa-stable.git] / reporting / rep103.php
diff --git a/reporting/rep103.php b/reporting/rep103.php
index e68027fdcc9993cb20888840bff906c9d608803d..271d6818c8453e8b8943c4c0d4a7f2dbcc41d525 100644 (file)
--- a/reporting/rep103.php
+++ b/reporting/rep103.php
@@ -1,15 +1,15 @@
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.
-       Released under the terms of the GNU General Public License, GPL, 
-       as published by the Free Software Foundation, either version 3 
+       Released under the terms of the GNU General Public License, GPL,
+       as published by the Free Software Foundation, either version 3
        of the License, or (at your option) any later version.
     This program is distributed in the hope that it will be useful,
     but WITHOUT ANY WARRANTY; without even the implied warranty of
        of the License, or (at your option) any later version.
     This program is distributed in the hope that it will be useful,
     but WITHOUT ANY WARRANTY; without even the implied warranty of
-    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
+    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
-$page_security = 2;
+$page_security = 'SA_CUSTBULKREP';
 // ----------------------------------------------------------------
 // $ Revision: 2.0 $
 // Creator:    Joe Hunt
 // ----------------------------------------------------------------
 // $ Revision: 2.0 $
 // Creator:    Joe Hunt
@@ -27,62 +27,73 @@ include_once($path_to_root . "/gl/includes/gl_db.inc");
 
 print_customer_details_listing();
 
 
 print_customer_details_listing();
 
-function get_customer_details_for_report($area=0, $salesid=0) 
+function get_customer_details_for_report($area=0, $salesid=0)
 {
 {
-       $sql = "SELECT ".TB_PREF."debtors_master.debtor_no,
-                       ".TB_PREF."debtors_master.name,
-                       ".TB_PREF."debtors_master.address,
-                       ".TB_PREF."sales_types.sales_type,
-                       ".TB_PREF."cust_branch.branch_code,
-                       ".TB_PREF."cust_branch.br_name,
-                       ".TB_PREF."cust_branch.br_address,
-                       ".TB_PREF."cust_branch.contact_name,
-                       ".TB_PREF."cust_branch.phone,
-                       ".TB_PREF."cust_branch.fax,
-                       ".TB_PREF."cust_branch.email,
-                       ".TB_PREF."cust_branch.area,
-                       ".TB_PREF."cust_branch.salesman,
-                       ".TB_PREF."areas.description,
-                       ".TB_PREF."salesman.salesman_name
-               FROM ".TB_PREF."debtors_master 
-               INNER JOIN ".TB_PREF."cust_branch
-                       ON ".TB_PREF."debtors_master.debtor_no=".TB_PREF."cust_branch.debtor_no
-               INNER JOIN ".TB_PREF."sales_types
-                       ON ".TB_PREF."debtors_master.sales_type=".TB_PREF."sales_types.id
-               INNER JOIN ".TB_PREF."areas
-                       ON ".TB_PREF."cust_branch.area = ".TB_PREF."areas.area_code
-               INNER JOIN ".TB_PREF."salesman
-                       ON ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code";
+       $sql = "SELECT debtor.debtor_no,
+                       debtor.name,
+                       debtor.address,
+                       debtor.curr_code,
+                       debtor.dimension_id,
+                       debtor.dimension2_id,
+                       debtor.notes,
+                       pricelist.sales_type,
+                       branch.branch_code,
+                       branch.br_name,
+                       branch.br_address,
+                       branch.br_post_address,
+                       branch.area,
+                       branch.salesman,
+                       area.description,
+                       salesman.salesman_name
+               FROM ".TB_PREF."debtors_master debtor
+               INNER JOIN ".TB_PREF."cust_branch branch ON debtor.debtor_no=branch.debtor_no
+               INNER JOIN ".TB_PREF."sales_types pricelist     ON debtor.sales_type=pricelist.id
+               INNER JOIN ".TB_PREF."areas area ON branch.area = area.area_code
+               INNER JOIN ".TB_PREF."salesman salesman ON branch.salesman=salesman.salesman_code
+               WHERE debtor.inactive = 0";
        if ($area != 0)
        {
                if ($salesid != 0)
        if ($area != 0)
        {
                if ($salesid != 0)
-                       $sql .= " WHERE ".TB_PREF."salesman.salesman_code=".db_escape($salesid)." 
-                               AND ".TB_PREF."areas.area_code=".db_escape($area);
-               else            
-                       $sql .= " WHERE ".TB_PREF."areas.area_code=".db_escape($area);
+                       $sql .= " AND salesman.salesman_code=".db_escape($salesid)."
+                               AND area.area_code=".db_escape($area);
+               else
+                       $sql .= " AND area.area_code=".db_escape($area);
        }
        elseif ($salesid != 0)
        }
        elseif ($salesid != 0)
-               $sql .= " WHERE ".TB_PREF."salesman.salesman_code=".db_escape($salesid);
+               $sql .= " AND salesman.salesman_code=".db_escape($salesid);
        $sql .= " ORDER BY description,
        $sql .= " ORDER BY description,
-                       ".TB_PREF."salesman.salesman_name,
-                       ".TB_PREF."debtors_master.debtor_no,
-                       ".TB_PREF."cust_branch.branch_code";
-                                       
+                       salesman.salesman_name,
+                       debtor.debtor_no,
+                       branch.branch_code";
+
     return db_query($sql,"No transactions were returned");
 }
 
     return db_query($sql,"No transactions were returned");
 }
 
-                                       
+function get_contacts_for_branch($branch)
+{
+       $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type 
+               FROM ".TB_PREF."crm_persons p,"
+                       .TB_PREF."crm_contacts r
+               WHERE r.person_id=p.id AND r.type='cust_branch' 
+                       AND r.entity_id=".db_escape($branch);
+       $res = db_query($sql, "can't retrieve branch contacts");
+       $results = array();
+       while($contact = db_fetch($res))
+               $results[] = $contact;
+       return $results;
+}
+
 function getTransactions($debtorno, $branchcode, $date)
 {
        $date = date2sql($date);
 
        $sql = "SELECT SUM((ov_amount+ov_freight+ov_discount)*rate) AS Turnover
                FROM ".TB_PREF."debtor_trans
 function getTransactions($debtorno, $branchcode, $date)
 {
        $date = date2sql($date);
 
        $sql = "SELECT SUM((ov_amount+ov_freight+ov_discount)*rate) AS Turnover
                FROM ".TB_PREF."debtor_trans
-               WHERE debtor_no='$debtorno'
-               AND branch_code='$branchcode'
-               AND (type=10 or type=11)
+               WHERE debtor_no=".db_escape($debtorno)."
+               AND branch_code=".db_escape($branchcode)."
+               AND (type=".ST_SALESINVOICE." OR type=".ST_CUSTCREDIT.")
                AND tran_date >='$date'";
                AND tran_date >='$date'";
-               
+
     $result = db_query($sql,"No transactions were returned");
 
        $row = db_fetch_row($result);
     $result = db_query($sql,"No transactions were returned");
 
        $row = db_fetch_row($result);
@@ -101,17 +112,19 @@ function print_customer_details_listing()
     $more = $_POST['PARAM_3'];
     $less = $_POST['PARAM_4'];
     $comments = $_POST['PARAM_5'];
     $more = $_POST['PARAM_3'];
     $less = $_POST['PARAM_4'];
     $comments = $_POST['PARAM_5'];
-       $destination = $_POST['PARAM_6'];
+       $orientation = $_POST['PARAM_6'];
+       $destination = $_POST['PARAM_7'];
        if ($destination)
                include_once($path_to_root . "/reporting/includes/excel_report.inc");
        else
                include_once($path_to_root . "/reporting/includes/pdf_report.inc");
        if ($destination)
                include_once($path_to_root . "/reporting/includes/excel_report.inc");
        else
                include_once($path_to_root . "/reporting/includes/pdf_report.inc");
-    
+
+       $orientation = ($orientation ? 'L' : 'P');
     $dec = 0;
 
     $dec = 0;
 
-       if ($area == reserved_words::get_all_numeric())
+       if ($area == ALL_NUMERIC)
                $area = 0;
                $area = 0;
-       if ($folk == reserved_words::get_all_numeric())
+       if ($folk == ALL_NUMERIC)
                $folk = 0;
 
        if ($area == 0)
                $folk = 0;
 
        if ($area == 0)
@@ -130,11 +143,11 @@ function print_customer_details_listing()
                $lessstr = _('Less than ') . number_format2($less, $dec);
        else
                $lessstr = '';
                $lessstr = _('Less than ') . number_format2($less, $dec);
        else
                $lessstr = '';
-       
-       $more = (double)$more;  
+
+       $more = (double)$more;
        $less = (double)$less;
 
        $less = (double)$less;
 
-       $cols = array(0, 150, 300, 400, 550);
+       $cols = array(0, 150, 300, 425, 550);
 
        $headers = array(_('Customer Postal Address'), _('Price/Turnover'),     _('Branch Contact Information'),
                _('Branch Delivery Address'));
 
        $headers = array(_('Customer Postal Address'), _('Price/Turnover'),     _('Branch Contact Information'),
                _('Branch Delivery Address'));
@@ -145,19 +158,21 @@ function print_customer_details_listing()
                                    1 => array('text' => _('Activity Since'),   'from' => $from,                'to' => ''),
                                    2 => array('text' => _('Sales Areas'),              'from' => $sarea,               'to' => ''),
                                    3 => array('text' => _('Sales Folk'),               'from' => $salesfolk,   'to' => ''),
                                    1 => array('text' => _('Activity Since'),   'from' => $from,                'to' => ''),
                                    2 => array('text' => _('Sales Areas'),              'from' => $sarea,               'to' => ''),
                                    3 => array('text' => _('Sales Folk'),               'from' => $salesfolk,   'to' => ''),
-                                   4 => array('text' => _('Activity'),                 'from' => $morestr,     'to' => $lessstr));
+                                   4 => array('text' => _('Activity'),                 'from' => $morestr,     'to' => $lessstr . " " . get_company_pref("curr_default")));
 
 
-    $rep = new FrontReport(_('Customer Details Listing'), "CustomerDetailsListing", user_pagesize());
+    $rep = new FrontReport(_('Customer Details Listing'), "CustomerDetailsListing", user_pagesize(), 9, $orientation);
+    if ($orientation == 'L')
+       recalculate_cols($cols);
 
     $rep->Font();
     $rep->Info($params, $cols, $headers, $aligns);
 
     $rep->Font();
     $rep->Info($params, $cols, $headers, $aligns);
-    $rep->Header();
+    $rep->NewPage();
 
        $result = get_customer_details_for_report($area, $folk);
 
        $result = get_customer_details_for_report($area, $folk);
-       
+
        $carea = '';
        $sman = '';
        $carea = '';
        $sman = '';
-       while ($myrow=db_fetch($result)) 
+       while ($myrow=db_fetch($result))
        {
                $printcustomer = true;
                if ($more != '' || $less != '')
        {
                $printcustomer = true;
                if ($more != '' || $less != '')
@@ -167,25 +182,26 @@ function print_customer_details_listing()
                                $printcustomer = false;
                        if ($less != 0.0 && $turnover >= (double)$less)
                                $printcustomer = false;
                                $printcustomer = false;
                        if ($less != 0.0 && $turnover >= (double)$less)
                                $printcustomer = false;
-               }       
+               }
                if ($printcustomer)
                {
                if ($printcustomer)
                {
+                       $newrow = 0;
                        if ($carea != $myrow['description'])
                        {
                                $rep->fontSize += 2;
                                $rep->NewLine(2, 7);
                        if ($carea != $myrow['description'])
                        {
                                $rep->fontSize += 2;
                                $rep->NewLine(2, 7);
-                               $rep->Font('bold');     
+                               $rep->Font('bold');
                                $rep->TextCol(0, 3,     _('Customers in') . " " . $myrow['description']);
                                $carea = $myrow['description'];
                                $rep->fontSize -= 2;
                                $rep->Font();
                                $rep->NewLine();
                                $rep->TextCol(0, 3,     _('Customers in') . " " . $myrow['description']);
                                $carea = $myrow['description'];
                                $rep->fontSize -= 2;
                                $rep->Font();
                                $rep->NewLine();
-                       }       
+                       }
                        if ($sman != $myrow['salesman_name'])
                        {
                                $rep->fontSize += 2;
                                $rep->NewLine(1, 7);
                        if ($sman != $myrow['salesman_name'])
                        {
                                $rep->fontSize += 2;
                                $rep->NewLine(1, 7);
-                               $rep->Font('bold');     
+                               $rep->Font('bold');
                                $rep->TextCol(0, 3,     $myrow['salesman_name']);
                                $sman = $myrow['salesman_name'];
                                $rep->fontSize -= 2;
                                $rep->TextCol(0, 3,     $myrow['salesman_name']);
                                $sman = $myrow['salesman_name'];
                                $rep->fontSize -= 2;
@@ -193,28 +209,75 @@ function print_customer_details_listing()
                                $rep->NewLine();
                        }
                        $rep->NewLine();
                                $rep->NewLine();
                        }
                        $rep->NewLine();
+                       // Here starts the new report lines
+                       $contacts = get_contacts_for_branch($myrow['branch_code']);
                        $rep->TextCol(0, 1,     $myrow['name']);
                        $rep->TextCol(0, 1,     $myrow['name']);
+                       $rep->TextCol(1, 2,     _('Price List') . ": " . $myrow['sales_type']);
+                       $rep->TextCol(2, 3,     $myrow['br_name']);
+                       $rep->NewLine();
                        $adr = Explode("\n", $myrow['address']);
                        $adr = Explode("\n", $myrow['address']);
+                       if ($myrow['br_post_address'] == '')
+                               $adr2 = Explode("\n", $myrow['br_address']);
+                       else
+                               $adr2 = Explode("\n", $myrow['br_post_address']);
                        $count1 = count($adr);
                        $count1 = count($adr);
-                       for ($i = 0; $i < $count1; $i++)
-                               $rep->TextCol(0, 1, $adr[$i], 0, ($i + 1) * $rep->lineHeight);
-                       $count1++;              
-                       $rep->TextCol(1, 2,     _('Price List') . ": " . $myrow['sales_type']);
+                       $count2 = count($adr2);
+                       $count1 = max($count1, $count2);
+                       $count1 = max($count1, 4); 
+                       if (isset($adr[0]))
+                               $rep->TextCol(0, 1, $adr[0]);
+                       $rep->TextCol(1, 2,     _('Currency') . ": " . $myrow['curr_code']);
+                       if (isset($contacts[0]))
+                               $rep->TextCol(2, 3, $contacts[0]['name']. " " .$contacts[0]['name2']);
+                       if (isset($adr2[0]))    
+                               $rep->TextCol(3, 4, $adr2[0]);
+                       $rep->NewLine();
+                       if (isset($adr[1]))
+                               $rep->TextCol(0, 1, $adr[1]);
+                       if ($myrow['dimension_id'] != 0)
+                       {
+                               $dim = get_dimension($myrow['dimension_id']);
+                               $rep->TextCol(1, 2,     _('Dimension') . ": " . $dim['name']);
+                       }               
+                       if (isset($contacts[0]))
+                               $rep->TextCol(2, 3, _('Ph') . ": " . $contacts[0]['phone']);
+                       if (isset($adr2[1]))
+                               $rep->TextCol(3, 4, $adr2[1]);
+                       $rep->NewLine();
+                       if (isset($adr[2]))
+                               $rep->TextCol(0, 1, $adr[2]);
+                       if ($myrow['dimension2_id'] != 0)
+                       {
+                               $dim = get_dimension($myrow['dimension2_id']);
+                               $rep->TextCol(1, 2,     _('Dimension') . " 2: " . $dim['name']);
+                       }       
+                       if ($myrow['notes'] != '')
+                       {
+                               $oldrow = $rep->row;
+                               $rep->NewLine();
+                               $rep->TextColLines(1, 2, _("General Notes:")." ".$myrow['notes'], -2);
+                               $newrow = $rep->row;
+                               $rep->row = $oldrow;
+                       }       
+                       if (isset($contacts[0]))
+                               $rep->TextCol(2, 3, _('Fax') . ": " . $contacts[0]['fax']);
+                       if (isset($adr2[2]))
+                               $rep->TextCol(3, 4, $adr2[2]);
                        if ($more != 0.0 || $less != 0.0)
                        if ($more != 0.0 || $less != 0.0)
-                               $rep->TextCol(1, 2,     _('Turnover') . ": " . number_format2($turnover, $dec), 0, $rep->lineHeight);
-                       $rep->TextCol(2, 3,     $myrow['br_name']);
-                       $rep->TextCol(2, 3, $myrow['contact_name'], 0, $rep->lineHeight);
-                       $rep->TextCol(2, 3, _('Ph') . ": " . $myrow['phone'], 0, 2 * $rep->lineHeight);
-                       $rep->TextCol(2, 3, _('Fax') . ": " . $myrow['fax'], 0, 3 * $rep->lineHeight);
-                       $adr = Explode("\n", $myrow['br_address']);
-                       $count2 = count($adr);
-                       for ($i = 0; $i < $count2; $i++)
-                               $rep->TextCol(3, 4, $adr[$i], 0, ($i + 1) * $rep->lineHeight);
-                       $rep->TextCol(3, 4, $myrow['email'], 0, ($count2 + 1) * $rep->lineHeight);
-                       $count2++;
-                       $count1 = Max($count1, $count2);
-                       $count1 = Max($count1, 4);
-                       $rep->NewLine($count1); 
+                               $rep->TextCol(1, 2,     _('Turnover') . ": " . number_format2($turnover, $dec));
+                       for ($i = 3; $i < $count1; $i++)
+                       {
+                               $rep->NewLine();
+                               if (isset($adr[$i]))
+                                       $rep->TextCol(0, 1, $adr[$i]);
+                               if ($i == 3 && isset($contacts[0]) && isset($contacts[0]['email']))     
+                                       $rep->TextCol(2, 3, _('Email') . ": " . $contacts[0]['email']);
+                               if (isset($adr2[$i]))
+                                       $rep->TextCol(3, 4, $adr2[$i]);
+                       }       
+                       if ($newrow != 0 && $newrow < $rep->row)
+                               $rep->row = $newrow;
+                       $rep->NewLine();
                        $rep->Line($rep->row + 8);
                        $rep->NewLine(0, 3);
                }
                        $rep->Line($rep->row + 8);
                        $rep->NewLine(0, 3);
                }
@@ -222,4 +285,3 @@ function print_customer_details_listing()
     $rep->End();
 }
 
     $rep->End();
 }
 
-?>
\ No newline at end of file
FrontAccounting stable branch