sales/includes/db/recurrent_invoices_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12
  13 function add_recurrent_invoice($description, $order_no, $debtor_no, $group_no, $days, $monthly,
  14         $begin, $end)
  15 {
  16         begin_transaction(__FUNCTION__, func_get_args());
  17         $sql = "INSERT INTO ".TB_PREF."recurrent_invoices (description, order_no, debtor_no,
  18                 group_no, days, monthly, begin, end, last_sent) VALUES (".db_escape($description) . ", "
  19                 .db_escape($order_no).", ".db_escape($debtor_no).", "
  20                 .db_escape($group_no).", ".$days.", ".$monthly.", '"
  21                 .date2sql($begin)."', '".date2sql($end)."', '0000-00-00')";
  22         db_query($sql,"The recurrent invoice could not be added");
  23         commit_transaction();
  24 }
  25
  26 function update_recurrent_invoice($selected_id, $description, $order_no, $debtor_no, $group_no, $days, $monthly,
  27         $begin, $end)
  28 {
  29         begin_transaction(__FUNCTION__, func_get_args());
  30         $sql = "UPDATE ".TB_PREF."recurrent_invoices SET
  31                 description=".db_escape($description).",
  32                 order_no=".db_escape($order_no).",
  33                 debtor_no=".db_escape($debtor_no).",
  34                 group_no=".db_escape($group_no).",
  35                 days=".$days.",
  36                 monthly=".$monthly.",
  37                 begin='".date2sql($begin)."',
  38                 end='".date2sql($end)."'
  39                 WHERE id = ".db_escape($selected_id);
  40         db_query($sql,"The recurrent invoice could not be updated");
  41         commit_transaction();
  42 }
  43
  44 function update_last_sent_recurrent_invoice($id, $date)
  45 {
  46         begin_transaction(__FUNCTION__, func_get_args());
  47         $date = date2sql($date);
  48         $sql = "UPDATE ".TB_PREF."recurrent_invoices SET last_sent='$date' WHERE id=".db_escape($id);
  49
  50         db_query($sql,"The recurrent invoice could not be updated");
  51         commit_transaction();
  52 }
  53
  54 function delete_recurrent_invoice($selected_id)
  55 {
  56         begin_transaction(__FUNCTION__, func_get_args());
  57         $sql="DELETE FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id);
  58         db_query($sql,"could not delete recurrent invoice");
  59         commit_transaction();
  60 }
  61
  62 function get_recurrent_invoices($date=null)
  63 {
  64         // last_sent stores end date of last generated invoice _plus_ 1 (or is empty)
  65         if ($date)
  66         {
  67                 $date = date2sql($date);
  68                 // we can issue invoice after or at last day of covered period
  69                 $sql = "SELECT *, DATE_ADD(DATE_ADD(IF(`last_sent`='0000-00-00', `begin`, `last_sent`), INTERVAL `monthly` MONTH), INTERVAL `days` DAY) <= '$date'
  70                         AND last_sent < DATE_ADD(`end`, INTERVAL 1 DAY) as overdue";
  71         } else
  72                 $sql = "SELECT * ";
  73
  74         $sql .= " FROM ".TB_PREF."recurrent_invoices ORDER BY description, group_no, debtor_no";
  75
  76         return db_query($sql,"could not get recurrent invoices");
  77 }
  78
  79 function get_recurrent_invoice($selected_id)
  80 {
  81         $sql = "SELECT * FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id);
  82
  83         $result = db_query($sql,"could not get recurrent invoice");
  84         return db_fetch($result);
  85 }
  86
  87 function check_recurrent_invoice_description($description, $id=null)
  88 {
  89         $sql = "SELECT count(*) FROM ".TB_PREF."recurrent_invoices WHERE description=".db_escape($description);
  90
  91         if (isset($id))
  92                 $sql .= " AND id<>".db_escape($id);
  93
  94         $result = db_query($sql,"could not check recurrent invoice");
  95         $row = db_fetch($result);
  96         return !$row[0];
  97 }
  98
  99 function recurrent_invoice_ready($selected_id, $date)
 100 {
 101         $date = date2sql($date);
 102
 103         $sql = "SELECT DATE_ADD(DATE_ADD(IF(`last_sent`='0000-00-00', `begin`, `last_sent`), INTERVAL `monthly` MONTH), INTERVAL `days` DAY) <= '$date'
 104                 AND last_sent < DATE_ADD(`end`, INTERVAL 1 DAY) as overdue
 105                 FROM ".TB_PREF."recurrent_invoices WHERE
 106                 id=".db_escape($selected_id);
 107
 108         $result = db_query($sql,"could not get recurrent invoice");
 109         $ret =  db_fetch($result);
 110
 111         return $ret['overdue'];
 112 }
 113 /*
 114         Return number of invoices generated by recurrent invoice.
 115 */
 116 function recurrent_invoice_count($id)
 117 {
 118
 119         $sql1 = "SELECT branch.*
 120                 FROM ".TB_PREF."recurrent_invoices rec
 121                         LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive
 122                         LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive
 123                 WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
 124
 125         $sql2 = "SELECT branch.*
 126                 FROM ".TB_PREF."recurrent_invoices rec
 127                         LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive
 128                         LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive
 129                 WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
 130
 131         $sql = "SELECT count(*) FROM ($sql1 UNION $sql2) a";
 132         $result = db_fetch(db_query($sql, "cannot count recurrent invoices"));
 133
 134         return $result[0];
 135 }
 136
 137 function check_recurrent_invoice_prices($id)
 138 {
 139         $errors = 0;
 140         $inv = get_recurrent_invoice($id);
 141
 142         $sql1 = "SELECT debtor.curr_code
 143                 FROM ".TB_PREF."recurrent_invoices rec
 144                         LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive
 145                         LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive
 146                 WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
 147
 148         $sql2 = "SELECT debtor.curr_code
 149                 FROM ".TB_PREF."recurrent_invoices rec
 150                         LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive
 151                         LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive
 152                 WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
 153
 154         $sql = "SELECT distinct * FROM ($sql1 UNION $sql2) a";
 155
 156         $currs = db_query($sql, "cannot count recurrent invoices");
 157
 158         $template = get_sales_order_header($inv['order_no'], ST_SALESORDER);
 159
 160         while ($curr = db_fetch($currs))
 161         {
 162                 $details = get_sales_order_details($inv['order_no'], ST_SALESORDER);
 163                 while($line = db_fetch($details))
 164                         if ($curr['curr_code'] != $template['curr_code'] && !get_price($line['stk_code'], $curr['curr_code'], $template['sales_type_id']))
 165                         {
 166                                 display_error(sprintf(_("Unknown %s price for '%s' in pricelist '%s'"), $curr['curr_code'], $line['stk_code'], $template['sales_type']));
 167                                 $errors++;
 168                         }
 169         }
 170         return $errors;
 171 }
 172
 173 /*
 174         Returns true when sales order is suitable to be pattern for recurrent invoice.
 175 */
 176 function check_sales_order_type($order_no)
 177 {
 178         $myrow = get_sales_order_header($order_no, ST_SALESORDER);
 179
 180         return !$myrow['prepaid'] && ! $myrow['cash_sale'];
 181 }
 182
 183 function create_template_invoice($customer_id, $branch_id, $order_no, $tmpl_no, $date, $from, $to, $memo)
 184 {
 185         global $Refs;
 186
 187         update_last_sent_recurrent_invoice($tmpl_no, $to);
 188
 189         $doc = new Cart(ST_SALESORDER, array($order_no));
 190
 191         get_customer_details_to_order($doc, $customer_id, $branch_id);
 192
 193         $doc->trans_type = ST_SALESORDER;
 194         $doc->trans_no = 0;
 195         $doc->document_date = $date;
 196
 197         $doc->due_date = get_invoice_duedate($doc->payment, $doc->document_date);
 198
 199         $doc->reference = $Refs->get_next($doc->trans_type, null, array('customer' => $customer_id, 'branch' => $branch_id,
 200                 'date' => $date));
 201         $doc->Comments = $memo;
 202
 203         foreach ($doc->line_items as $line_no=>$item) {
 204                 $line = &$doc->line_items[$line_no];
 205                 $new_price = get_price($line->stock_id, $doc->customer_currency,
 206                         $doc->sales_type, $doc->price_factor, $doc->document_date);
 207                 if ($new_price != 0)    // use template price if no price is currently set for the item.
 208                         $line->price = $new_price;
 209         }
 210         $cart = $doc;
 211         $cart->trans_type = ST_SALESINVOICE;
 212         $cart->reference = $Refs->get_next($cart->trans_type);
 213
 214         $invno = $cart->write(1);
 215
 216         return $invno;
 217 }
 218