X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Frecurrent_invoices_db.inc;h=7aca37cf46f21723a8537e682effbe926b4de997;hb=7618d9d62ee39a932f56565a4ce25d965bf53462;hp=ba173f3336596ba9b511a2c5359f7f0f0c0457d9;hpb=5b8f4c4b4aa8cf996bc071f116bfce1273200fa2;p=fa-stable.git diff --git a/sales/includes/db/recurrent_invoices_db.inc b/sales/includes/db/recurrent_invoices_db.inc index ba173f33..7aca37cf 100644 --- a/sales/includes/db/recurrent_invoices_db.inc +++ b/sales/includes/db/recurrent_invoices_db.inc @@ -13,17 +13,20 @@ function add_recurrent_invoice($description, $order_no, $debtor_no, $group_no, $days, $monthly, $begin, $end) { + begin_transaction(__FUNCTION__, func_get_args()); $sql = "INSERT INTO ".TB_PREF."recurrent_invoices (description, order_no, debtor_no, group_no, days, monthly, begin, end, last_sent) VALUES (".db_escape($description) . ", " .db_escape($order_no).", ".db_escape($debtor_no).", " .db_escape($group_no).", ".$days.", ".$monthly.", '" .date2sql($begin)."', '".date2sql($end)."', '0000-00-00')"; db_query($sql,"The recurrent invoice could not be added"); + commit_transaction(); } function update_recurrent_invoice($selected_id, $description, $order_no, $debtor_no, $group_no, $days, $monthly, $begin, $end) { + begin_transaction(__FUNCTION__, func_get_args()); $sql = "UPDATE ".TB_PREF."recurrent_invoices SET description=".db_escape($description).", order_no=".db_escape($order_no).", @@ -35,24 +38,41 @@ function update_recurrent_invoice($selected_id, $description, $order_no, $debtor end='".date2sql($end)."' WHERE id = ".db_escape($selected_id); db_query($sql,"The recurrent invoice could not be updated"); + commit_transaction(); } function update_last_sent_recurrent_invoice($id, $date) { + begin_transaction(__FUNCTION__, func_get_args()); $date = date2sql($date); $sql = "UPDATE ".TB_PREF."recurrent_invoices SET last_sent='$date' WHERE id=".db_escape($id); + db_query($sql,"The recurrent invoice could not be updated"); + commit_transaction(); } function delete_recurrent_invoice($selected_id) { + begin_transaction(__FUNCTION__, func_get_args()); $sql="DELETE FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id); db_query($sql,"could not delete recurrent invoice"); + commit_transaction(); } -function get_recurrent_invoices() +function get_recurrent_invoices($date=null) { - $sql = "SELECT * FROM ".TB_PREF."recurrent_invoices ORDER BY description, group_no, debtor_no"; + // last_sent stores end date of last generated invoice _plus_ 1 (or is empty) + if ($date) + { + $date = date2sql($date); + // we can issue invoice after or at last day of covered period + $sql = "SELECT *, DATE_ADD(DATE_ADD(IF(`last_sent`='0000-00-00', `begin`, `last_sent`), INTERVAL `monthly` MONTH), INTERVAL `days` DAY) <= '$date' + AND last_sent < DATE_ADD(`end`, INTERVAL 1 DAY) as overdue"; + } else + $sql = "SELECT * "; + + $sql .= " FROM ".TB_PREF."recurrent_invoices ORDER BY description, group_no, debtor_no"; + return db_query($sql,"could not get recurrent invoices"); } @@ -63,3 +83,136 @@ function get_recurrent_invoice($selected_id) $result = db_query($sql,"could not get recurrent invoice"); return db_fetch($result); } + +function check_recurrent_invoice_description($description, $id=null) +{ + $sql = "SELECT count(*) FROM ".TB_PREF."recurrent_invoices WHERE description=".db_escape($description); + + if (isset($id)) + $sql .= " AND id<>".db_escape($id); + + $result = db_query($sql,"could not check recurrent invoice"); + $row = db_fetch($result); + return !$row[0]; +} + +function recurrent_invoice_ready($selected_id, $date) +{ + $date = date2sql($date); + + $sql = "SELECT DATE_ADD(DATE_ADD(IF(`last_sent`='0000-00-00', `begin`, `last_sent`), INTERVAL `monthly` MONTH), INTERVAL `days` DAY) <= '$date' + AND last_sent < DATE_ADD(`end`, INTERVAL 1 DAY) as overdue + FROM ".TB_PREF."recurrent_invoices WHERE + id=".db_escape($selected_id); + + $result = db_query($sql,"could not get recurrent invoice"); + $ret = db_fetch($result); + + return $ret['overdue']; +} +/* + Return number of invoices generated by recurrent invoice. +*/ +function recurrent_invoice_count($id) +{ + + $sql1 = "SELECT branch.* + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive + LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive + WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); + + $sql2 = "SELECT branch.* + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive + LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive + WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); + + $sql = "SELECT count(*) FROM ($sql1 UNION $sql2) a"; + $result = db_fetch(db_query($sql, "cannot count recurrent invoices")); + + return $result[0]; +} + +function check_recurrent_invoice_prices($id) +{ + $errors = 0; + $inv = get_recurrent_invoice($id); + + $sql1 = "SELECT debtor.curr_code + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive + LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive + WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); + + $sql2 = "SELECT debtor.curr_code + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive + LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive + WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); + + $sql = "SELECT distinct * FROM ($sql1 UNION $sql2) a"; + + $currs = db_query($sql, "cannot count recurrent invoices"); + + $template = get_sales_order_header($inv['order_no'], ST_SALESORDER); + + while ($curr = db_fetch($currs)) + { + $details = get_sales_order_details($inv['order_no'], ST_SALESORDER); + while($line = db_fetch($details)) + if ($curr['curr_code'] != $template['curr_code'] && !get_price($line['stk_code'], $curr['curr_code'], $template['sales_type_id'])) + { + display_error(sprintf(_("Unknown %s price for '%s' in pricelist '%s'"), $curr['curr_code'], $line['stk_code'], $template['sales_type'])); + $errors++; + } + } + return $errors; +} + +/* + Returns true when sales order is suitable to be pattern for recurrent invoice. +*/ +function check_sales_order_type($order_no) +{ + $myrow = get_sales_order_header($order_no, ST_SALESORDER); + + return !$myrow['prepaid'] && ! $myrow['cash_sale']; +} + +function create_template_invoice($customer_id, $branch_id, $order_no, $tmpl_no, $date, $from, $to, $memo) +{ + global $Refs; + + update_last_sent_recurrent_invoice($tmpl_no, $to); + + $doc = new Cart(ST_SALESORDER, array($order_no)); + + get_customer_details_to_order($doc, $customer_id, $branch_id); + + $doc->trans_type = ST_SALESORDER; + $doc->trans_no = 0; + $doc->document_date = $date; + + $doc->due_date = get_payment_due_date($doc->payment, $doc->document_date); + + $doc->reference = $Refs->get_next($doc->trans_type, null, array('customer' => $customer_id, 'branch' => $branch_id, + 'date' => $date)); + $doc->Comments = $memo; + + foreach ($doc->line_items as $line_no=>$item) { + $line = &$doc->line_items[$line_no]; + $new_price = get_price($line->stock_id, $doc->customer_currency, + $doc->sales_type, $doc->price_factor, $doc->document_date); + if ($new_price != 0) // use template price if no price is currently set for the item. + $line->price = $new_price; + } + $cart = $doc; + $cart->trans_type = ST_SALESINVOICE; + $cart->reference = $Refs->get_next($cart->trans_type); + + $invno = $cart->write(1); + + return $invno; +} +