From f4e4bc5a2ff9f97a20b3a62265926a4c677163f9 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 13 May 2015 14:28:16 +0200 Subject: [PATCH] Improved recurrent orders feature: added invoice date and memo edition, additional checks for proper nvoice generation. --- includes/data_checks.inc | 16 ++ sales/create_recurrent_invoices.php | 239 +++++++++++++------- sales/includes/db/recurrent_invoices_db.inc | 108 ++++++++- sales/inquiry/sales_orders_view.php | 3 +- sales/manage/recurrent_invoices.php | 37 ++- 5 files changed, 306 insertions(+), 97 deletions(-) diff --git a/includes/data_checks.inc b/includes/data_checks.inc index 7243e43f..8449b5dd 100644 --- a/includes/data_checks.inc +++ b/includes/data_checks.inc @@ -495,6 +495,22 @@ function check_is_closed($type, $type_no, $msg=null) } } +function check_db_has_template_orders($msg) +{ + $sql = "SELECT sorder.order_no + FROM ".TB_PREF."sales_orders as sorder," + .TB_PREF."sales_order_details as line + WHERE sorder.order_no = line.order_no AND sorder.type = 1 + GROUP BY line.order_no"; + + if (!check_empty_result($sql)) + { + display_error($msg, true); + end_page(); + exit; + } +} + function check_deferred_income_act($msg) { global $path_to_root; diff --git a/sales/create_recurrent_invoices.php b/sales/create_recurrent_invoices.php index de93bd5c..f7704e5a 100644 --- a/sales/create_recurrent_invoices.php +++ b/sales/create_recurrent_invoices.php @@ -25,41 +25,39 @@ if (user_use_date_picker()) page(_($help_context = "Create and Print Recurrent Invoices"), false, false, "", $js); -function create_recurrent_invoices($customer_id, $branch_id, $order_no, $tmpl_no, $date, $from, $to) +function create_recurrent_invoices($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->document_date = $date; $doc->due_date = get_invoice_duedate($doc->payment, $doc->document_date); + $doc->reference = $Refs->get_next($doc->trans_type, null, array('customer' => $customer_id, 'branch' => $branch_id, 'date' => $date)); - if ($doc->Comments != "") - $doc->Comments .= "\n"; - $doc->Comments .= sprintf(_("Recurrent Invoice covers period %s - %s."), $from, add_days($to, -1)); + $doc->Comments = $memo; foreach ($doc->line_items as $line_no=>$item) { $line = &$doc->line_items[$line_no]; - $line->price = get_price($line->stock_id, $doc->customer_currency, + $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, null, array('customer' => $customer_id, 'branch' => $branch_id, - 'date' => $date)); + $cart->reference = $Refs->get_next($cart->trans_type); + $cart->payment_terms['cash_sale'] = false; // no way to register cash payment with recurrent invoice at once $invno = $cart->write(1); - if ($invno == -1) - { - display_error(_("The entered reference is already in use.")); - display_footer_exit(); - } - update_last_sent_recurrent_invoice($tmpl_no, $to); + return $invno; } @@ -69,112 +67,181 @@ function calculate_from($myrow) $from = sql2date($myrow["begin"]); else $from = sql2date($myrow["last_sent"]); - return $from; + return $from; } -if (!isset($_POST['date'])) { - $_POST['date'] = Today(); +function calculate_next($myrow) +{ + if ($myrow["last_sent"] == '0000-00-00') + $next = sql2date($myrow["begin"]); + else + $next = sql2date($myrow["last_sent"]); + $next = add_months($next, $myrow['monthly']); + $next = add_days($next, $myrow['days']); + return add_days($next,-1); +} + +$id = find_submit("confirmed"); +if ($id != -1 && is_date_closed($_POST['trans_date'])) +{ + display_error(_("The entered date is out of fiscal year or is closed for further data entry.")); + set_focus('trans_date'); + $_POST['create'.$id] = 1; //re-display current page + $id = -1; } -$id = find_submit("create"); if ($id != -1) { + /* + whole invoiced time is + invoices are issued _after_ invoiced period is gone, eg: + begin 1.1 + end 31.3 + period: invoice ready for issue since: + 1.1-31.1 - 1.2 + 1.2-28.2 - 1.3 + 1.3-31.3 - 1.4 + In example above, when end is set to 1.4 will generate additional invoice on 1.5 ! + */ + $Ajax->activate('_page_body'); - $date = $_POST['date']; - if (is_date_in_fiscalyear($date)) + $from = get_post('from'); + $to = get_post('to'); + $memo = get_post('memo'); + $date = $_POST['trans_date']; + $myrow = get_recurrent_invoice($id); + + $invs = array(); + if (recurrent_invoice_ready($id, $date)) { - $invs = array(); - $myrow = get_recurrent_invoice($id); - $from = calculate_from($myrow); - $to = add_months($from, $myrow['monthly']); - $to = add_days($to, $myrow['days']); - if ($myrow['debtor_no'] == 0) - { - $cust = get_cust_branches_from_group($myrow['group_no']); - while ($row = db_fetch($cust)) + begin_transaction(); + + if ($myrow['debtor_no'] == 0) { - $invs[] = create_recurrent_invoices($row['debtor_no'], $row['branch_code'], $myrow['order_no'], $myrow['id'], - $date, $from, $to); - } - } - else - { - $invs[] = create_recurrent_invoices($myrow['debtor_no'], $myrow['group_no'], $myrow['order_no'], $myrow['id'], - $date, $from, $to); - } - if (count($invs) > 0) - { - $min = min($invs); - $max = max($invs); - } - else - $min = $max = 0; - display_notification(sprintf(_("%s recurrent invoice(s) created, # %s - # %s."), count($invs), $min, $max)); - if (count($invs) > 0) - { - $ar = array('PARAM_0' => $min."-".ST_SALESINVOICE, 'PARAM_1' => $max."-".ST_SALESINVOICE, 'PARAM_2' => "", - 'PARAM_3' => 0, 'PARAM_4' => 0, 'PARAM_5' => "", 'PARAM_6' => user_def_print_orientation()); - display_note(print_link(sprintf(_("&Print Recurrent Invoices # %s - # %s"), $min, $max), 107, $ar), 0, 1); - $ar['PARAM_3'] = 1; // email - display_note(print_link(sprintf(_("&Email Recurrent Invoices # %s - # %s"), $min, $max), 107, $ar), 0, 1); - } + $cust = get_cust_branches_from_group($myrow['group_no']); + while ($row = db_fetch($cust)) + { + $invs[] = create_recurrent_invoices($row['debtor_no'], $row['branch_code'], $myrow['order_no'], $myrow['id'], + $date, $from, $to, $memo); + } + } + else + { + $invs[] = create_recurrent_invoices($myrow['debtor_no'], $myrow['group_no'], $myrow['order_no'], $myrow['id'], + $date, $from, $to, $memo); + } + commit_transaction(); + } + if (count($invs) > 0) + { + $min = min($invs); + $max = max($invs); + } + else + $min = $max = 0; + display_notification(sprintf(_("%s recurrent invoice(s) created, # %s - # %s."), count($invs), $min, $max)); + if (count($invs) > 0) + { + $ar = array('PARAM_0' => $min."-".ST_SALESINVOICE, 'PARAM_1' => $max."-".ST_SALESINVOICE, 'PARAM_2' => "", + 'PARAM_3' => 0, 'PARAM_4' => 0, 'PARAM_5' => "", 'PARAM_6' => user_def_print_orientation()); + display_note(print_link(sprintf(_("&Print Recurrent Invoices # %s - # %s"), $min, $max), 107, $ar), 0, 1); + $ar['PARAM_6'] = 1; // orygina³ + $ar['PARAM_3'] = 1; // email + display_note(print_link(sprintf(_("&Email Recurrent Invoices # %s - # %s"), $min, $max), 107, $ar), 0, 1); } - else - display_error(_("The entered date is out of fiscal year or is closed for further data entry.")); } -$result = get_recurrent_invoices(); -start_form(); -start_table(TABLESTYLE_NOBORDER); -start_row(); -date_cells(_("Invoice date:"), 'date', ''); -end_row(); -end_table(); +$id = find_submit('create'); +if ($id != -1) +{ + $Ajax->activate('_page_body'); + $date = Today(); + $myrow = get_recurrent_invoice($id); + $from = calculate_from($myrow); + $to = add_months($from, $myrow['monthly']); + $to = add_days($to, $myrow['days']); + + if (!is_date_in_fiscalyear($date)) + display_error(_("The entered date is out of fiscal year or is closed for further data entry.")); + elseif (!date1_greater_date2(add_days(Today(), 1), $to)) + display_error(_("Recurrent invoice cannot be generated before last day of covered period.")); + elseif (check_recurrent_invoice_prices($id)) + display_error(_("Recurrent invoices cannot be generated because some items have no price defined in customer currency.")); + elseif (!check_sales_order_type($myrow['order_no'])) + display_error(_("Recurrent invoices cannot be generated because selected sales order template uses prepayment sales terms. Change payment terms and try again.")); + else { + $count = recurrent_invoice_count($id); + + $_POST['trans_date'] = $to; + start_form(); + start_table(TABLESTYLE, "width=50%"); + label_row(_('Description:'), $myrow["description"]); + label_row(_('Template:'), get_customer_trans_view_str(30, $myrow["order_no"])); + label_row(_('Number of invoices:'), $count); + date_row(_('Invoice date:'), 'trans_date'); + text_row(_('Invoice notice:'), 'memo', sprintf(_("Recurrent Invoice covers period %s - %s."), $from, add_days($to, -1)), + 100, 100); + end_table(); + hidden('from', $from, true); + hidden('to', $to, true); + br(); + submit_center_first('confirmed'.$id, _('Create'), _('Create recurrent invoices'), false, ICON_OK); + submit_center_last('cancel', _('Cancel'), _('Return to recurrent invoices'), false, ICON_ESCAPE); + submit_js_confirm("do_create".$id, sprintf(_("You are about to issue %s invoices.\n Do you want to continue?"), $count)); + end_form(); + + display_footer_exit(); + } +} +else +{ +$result = get_recurrent_invoices(Today()); -start_table(TABLESTYLE, "width='70%'"); -$th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Days"),_("Monthly"),_("Begin"),_("End"),_("Last Created"),""); +start_form(); +start_table(TABLESTYLE, "width=70%"); +$th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Days"),_("Monthly"),_("Begin"),_("End"),_("Next invoice"),""); table_header($th); $k = 0; -$today = add_days($_POST['date'], 1); $due = false; while ($myrow = db_fetch($result)) { - $begin = sql2date($myrow["begin"]); - $end = sql2date($myrow["end"]); - $last_sent = calculate_from($myrow); - $due_date = add_months($last_sent, $myrow['monthly']); - $due_date = add_days($due_date, $myrow['days']); - - $overdue = date1_greater_date2($today, $due_date) && date1_greater_date2($today, $begin) - && date1_greater_date2($end, $today); - if ($overdue) + if ($myrow['overdue']) { start_row("class='overduebg'"); $due = true; - } - else + } + else alt_table_row_color($k); - + label_cell($myrow["description"]); label_cell(get_customer_trans_view_str(30, $myrow["order_no"])); if ($myrow["debtor_no"] == 0) { label_cell(""); + label_cell(get_sales_group_name($myrow["group_no"])); - } + } else { label_cell(get_customer_name($myrow["debtor_no"])); label_cell(get_branch_name($myrow['group_no'])); - } + } label_cell($myrow["days"]); label_cell($myrow['monthly']); - label_cell($begin); - label_cell($end); - label_cell(($myrow['last_sent']=="0000-00-00")?"":$last_sent); - if ($overdue) - button_cell("create".$myrow["id"], _("Create Invoices"), "", ICON_DOC); + label_cell(sql2date($myrow['begin']), "align='center'"); + label_cell(sql2date($myrow['end']), "align='center'"); + label_cell(calculate_next($myrow), "align='center'"); + if ($myrow['overdue']) + { + $count = recurrent_invoice_count($myrow['id']); + if ($count) + { + button_cell("create".$myrow["id"], sprintf(_("Create %s Invoice(s)"), $count), "", ICON_DOC, 'process'); + } else { + label_cell(''); + } + } else label_cell(""); end_row(); @@ -187,5 +254,5 @@ else display_note(_("No recurrent invoices are due."), 1, 0); br(); - +} end_page(); diff --git a/sales/includes/db/recurrent_invoices_db.inc b/sales/includes/db/recurrent_invoices_db.inc index ba173f33..a80aac07 100644 --- a/sales/includes/db/recurrent_invoices_db.inc +++ b/sales/includes/db/recurrent_invoices_db.inc @@ -41,6 +41,7 @@ function update_last_sent_recurrent_invoice($id, $date) { $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"); } @@ -50,9 +51,20 @@ function delete_recurrent_invoice($selected_id) db_query($sql,"could not delete recurrent invoice"); } -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 +75,95 @@ 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']; +} diff --git a/sales/inquiry/sales_orders_view.php b/sales/inquiry/sales_orders_view.php index 35d71c64..676d6ea0 100644 --- a/sales/inquiry/sales_orders_view.php +++ b/sales/inquiry/sales_orders_view.php @@ -149,7 +149,8 @@ function order_link($row) function tmpl_checkbox($row) { global $trans_type, $page_nested; - if ($trans_type == ST_SALESQUOTE) + + if ($trans_type == ST_SALESQUOTE || !check_sales_order_type($row['order_no'])) return ''; if ($page_nested) diff --git a/sales/manage/recurrent_invoices.php b/sales/manage/recurrent_invoices.php index 8a08a6d1..55c99864 100644 --- a/sales/manage/recurrent_invoices.php +++ b/sales/manage/recurrent_invoices.php @@ -23,6 +23,9 @@ if (user_use_date_picker()) page(_($help_context = "Recurrent Invoices"), false, false, "", $js); +check_db_has_template_orders(_("There is no template order in database. + You have to create at least one sales order marked as template to be able to define recurrent invoices.")); + simple_page_mode(true); if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') @@ -30,11 +33,14 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $input_error = 0; - if (strlen($_POST['order_no']) == 0) + if (!get_post('group_no')) { $input_error = 1; - display_error(_("No order has been selected as a template for recurrent invoicing.")); - set_focus('order_no'); + if (get_post('debtor_no')) + display_error(_("This customer has no branches. Please define at least one branch for this customer first.")); + else + display_error(_("There are no tax groups defined in the system. At least one tax group is required before proceeding.")); + set_focus('debtor_no'); } if (strlen($_POST['description']) == 0) { @@ -42,12 +48,20 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') display_error(_("The invoice description cannot be empty.")); set_focus('description'); } - if (!is_date($_POST['begin'])) { + if (!check_recurrent_invoice_description($_POST['description'], $selected_id)) + { + $input_error = 1; + display_error(_("This recurrent invoice description is already in use.")); + set_focus('description'); + } + if (!is_date($_POST['begin'])) + { $input_error = 1; display_error(_("The entered date is invalid.")); set_focus('begin'); } - if (!is_date($_POST['end'])) { + if (!is_date($_POST['end'])) + { $input_error = 1; display_error(_("The entered date is invalid.")); set_focus('end'); @@ -57,6 +71,12 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') display_error(_("The entered date is invalid.")); set_focus('last_sent'); } + if (!$_POST['days'] && !$_POST['monthly']) + { + $input_error = 1; + display_error(_("No recurence interval has been entered.")); + set_focus('days'); + } if ($input_error != 1) { @@ -75,7 +95,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $note = _('New recurrent invoice has been added'); } - display_notification($note); + display_notification($note); $Mode = 'RESET'; } } @@ -177,7 +197,7 @@ customer_list_row(_("Customer:"), 'debtor_no', null, " ", true); if ($_POST['debtor_no'] > 0) customer_branches_list_row(_("Branch:"), $_POST['debtor_no'], 'group_no', null, false); else - sales_groups_list_row(_("Sales Group:"), 'group_no', null, " "); + sales_groups_list_row(_("Sales Group:"), 'group_no', null); small_amount_row(_("Days:"), 'days', 0, null, null, 0); @@ -187,7 +207,7 @@ date_row(_("Begin:"), 'begin'); date_row(_("End:"), 'end', null, null, 0, 0, 5); -if ($selected_id != -1 && $_POST['last_sent'] != "") +if ($selected_id != -1 && @$_POST['last_sent'] != "") date_row(_("Last Created"), 'last_sent'); end_table(1); @@ -197,3 +217,4 @@ submit_add_or_update_center($selected_id == -1, '', 'both'); end_form(); end_page(); +?> -- 2.30.2