Report branch customers

File: <unsaved>
01: ---   \reporting\rep115ORIGINAL.php
02: +++\reporting\rep115.php
03: @@ -29,7 +29,7 @@
04:  
05:  print_customer_balances();
06:  
07: -function get_open_balance($debtorno, $to)
08: +function get_open_balance($debtorno, $branchcode, $to)
09:  {
10:      if ($to)
11:          $to = date2sql($to);
12: @@ -43,17 +43,18 @@
13:      $sql .= "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) *
14:          (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding
15:          FROM ".TB_PREF."debtor_trans t
16: -        WHERE t.debtor_no = ".db_escape($debtorno)
17: +        WHERE t.debtor_no = ".db_escape($debtorno)."
18: +        AND t.branch_code=".db_escape($branchcode)
19:          ." AND t.type <> ".ST_CUSTDELIVERY;
20:      if ($to)
21:          $sql .= " AND t.tran_date < '$to'";
22: -    $sql .= " GROUP BY debtor_no";
23: +    // $sql .= " GROUP BY debtor_no";
24:  
25:      $result = db_query($sql,"No transactions were returned");
26:      return db_fetch($result);
27:  }
28:  
29: -function get_transactions($debtorno, $from, $to, $only_rec)
30: +function get_transactions($debtorno, $branchcode, $from, $to, $only_rec)
31:  {
32:      $from = date2sql($from);
33:      $to = date2sql($to);
34: @@ -82,7 +83,8 @@
35:          LEFT JOIN $allocated_to ON alloc_to.trans_type = trans.type AND alloc_to.trans_no = trans.trans_no
36:          WHERE trans.tran_date >= '$from'
37:          AND trans.tran_date <= '$to'
38: -        AND trans.debtor_no = ".db_escape($debtorno);
39: +        AND trans.debtor_no = ".db_escape($debtorno)."
40: +        AND trans.branch_code=".db_escape($branchcode);
41:      $sql .= " AND trans.type <> ".ST_CUSTDELIVERY;
42:      $sql .= " AND ISNULL(voided.id)
43:          ORDER BY trans.tran_date ";
44: @@ -158,7 +160,7 @@
45:      $cols = array(0, 100, 130, 190, 250, 320, 385, 450, 515);
46:      //$cols = array(0, 70, 140, 180, 230, 270, 350, 445, 495, 555);
47:  
48: -    $headers = array(_('Name'), '', '', _('Open Balance'), _('Debit'), _('Credit'), '', _('Balance'));
49: +    $headers = array(_('Name'), '', _('Branch'), _('Open Balance'), _('Debit'), _('Credit'), '', _('Balance'));
50:  
51:      $aligns = array('left', 'left', 'left', 'right', 'right', 'right', 'right', 'right');
52:  
53: @@ -179,7 +181,7 @@
54:  
55:      $grandtotal = array(0,0,0,0);
56:  
57: -    $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, name, curr_code FROM ".TB_PREF."debtors_master
58: +    $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, name, br_name, branch_code, curr_code FROM ".TB_PREF."debtors_master
59:          INNER JOIN ".TB_PREF."cust_branch
60:          ON ".TB_PREF."debtors_master.debtor_no=".TB_PREF."cust_branch.debtor_no
61:          INNER JOIN ".TB_PREF."areas
62: @@ -204,7 +206,7 @@
63:          $sql .= " WHERE ".TB_PREF."salesman.salesman_code=".db_escape($folk);
64:      }
65:  
66: -    $sql .= " GROUP BY ".TB_PREF."debtors_master.debtor_no ORDER BY name";
67: +    // $sql .= " GROUP BY ".TB_PREF."debtors_master.debtor_no ORDER BY name";
68:  
69:      $result = db_query($sql, "The customers could not be retrieved");
70:  
71: @@ -215,7 +217,7 @@
72:  
73:          $accumulate = 0;
74:          $rate = $convert ? get_exchange_rate_from_home_currency($myrow['curr_code'], Today()) : 1;
75: -        $bal = get_open_balance($myrow['debtor_no'], $from, $convert);
76: +        $bal = get_open_balance($myrow['debtor_no'], $myrow['branch_code'], $from, $convert);
77:          $init[0] = $init[1] = 0.0;
78:          $init[0] = round2(abs($bal['charges'] * $rate), $dec);
79:          $init[1] = round2(Abs($bal['credits'] * $rate), $dec);
80: @@ -223,7 +225,7 @@
81:          $init[3] = $init[0] - $init[1];
82:          $accumulate += $init[3];
83:  
84: -        $res = get_transactions($myrow['debtor_no'], $from, $to, false);
85: +        $res = get_transactions($myrow['debtor_no'], $myrow['branch_code'], $from, $to, false);
86:  
87:          $total = array(0,0,0,0);
88:          for ($i = 0; $i < 4; $i++)
89: @@ -279,6 +281,7 @@
90:          }
91:          if ($no_zeros && $total[3] == 0.0 && $curr_db == 0.0 && $curr_cr == 0.0) continue;
92:          $rep->TextCol(0, 2, $myrow['name']);
93: +        $rep->TextCol(2, 3, $myrow['br_name']);
94:          $rep->AmountCol(3, 4, $total[3] + $curr_cr - $curr_db, $dec);
95:          $rep->AmountCol(4, 5, $curr_db, $dec);
96:          $rep->AmountCol(5, 6, $curr_cr, $dec);