auth->check_module_permissions('billing');
$app->uses('tpl');
$app->tpl->newTemplate('form.tpl.htm');
$app->tpl->setInclude('content_tpl', 'templates/send_invoice_reminder.htm');
$msg = '';
$error = '';
$page_back_link = 'billing/send_invoice_reminder.php';
//* load language file
$lng_file = 'lib/lang/'.$_SESSION['s']['language'].'_send_invoice_reminder.lng';
include($lng_file);
if(isset($_GET['action']) && $_GET['action'] == 'send') {
$email_template_id = intval($_POST['email_template_id']);
if($email_template_id == 0) $error .= $app->lng('No Email template selected.').'
';
if(count($_POST['invoice_id']) == 0) $error .= $app->lng('No pending invoices selcted.').'
';
if($error == '' && is_array($_POST['invoice_id'])) {
//* Get the email template
$email_template = $app->db->queryOneRecord("SELECT * FROM invoice_message_template WHERE invoice_message_template_id = $email_template_id");
//* Send the reminders by email.
$app->uses('functions');
foreach($_POST['invoice_id'] as $invoice_id) {
$invoice_id = intval($invoice_id);
//* Get the invoice, client and company
/*
$sql = "SELECT invoice_company.email as company_email,invoice.invoice_number,invoice.idhash,client.email as client_email,invoice.invoice_number,client.contact_name,client.company_name
FROM invoice_company,invoice,client
WHERE invoice_company.invoice_company_id = invoice.invoice_company_id AND client.client_id = invoice.client_id AND invoice_id = $invoice_id";
$rec = $app->db->queryOneRecord($sql);
*/
$invoice = $app->db->queryOneRecord('SELECT * FROM invoice WHERE invoice_id = '.$invoice_id);
$check = $app->db->queryOneRecord("SELECT * FROM invoice WHERE invoice_number = '" . $app->db->quote($invoice['invoice_number']) . "' AND invoice_type = 'reminder' ORDER BY reminder_step DESC");
$next_step = 1;
if(is_array($check)) $next_step = $check['reminder_step'] + 1;
$client = $app->db->queryOneRecord('SELECT * FROM client WHERE client_id = '.$invoice['client_id']);
//$invoice_client_settings = $app->db->queryOneRecord('SELECT * FROM invoice_client_settings WHERE client_id = '.$invoice['client_id']);
$company = $app->db->queryOneRecord('SELECT * FROM invoice_company WHERE invoice_company_id = '.$invoice['invoice_company_id']);
//* Duplicate the invoice to a reminder
if(!is_array($invoice)) continue;
if($next_step > $company['reminder_steps']) continue;
$reminder = $invoice;
unset($reminder['invoice_id']);
$reminder['invoice_type'] = 'reminder';
$reminder['reminder_step'] = $next_step;
if($company['reminder_last_payment_terms'] && $company['reminder_steps'] == $invoice['reminder_step']) $reminder['payment_terms'] = $company['reminder_last_payment_terms'];
elseif($company['reminder_payment_terms']) $reminder['payment_terms'] = $company['reminder_payment_terms'];
$reminder['status_printed'] = 'n';
$reminder['status_sent'] = 'y';
$reminder['status_paid'] = 'n';
$reminder['invoice_date'] = date('Y-m-d');
$sql = _get_sql_insert('invoice',$reminder);
$app->db->query($sql);
$new_invoice_id = $app->db->insertID();
//* Add duplicates for all records of the old invoice to the new invoice
$invoice_items = $app->db->queryAllRecords('SELECT * FROM invoice_item WHERE invoice_id = '.$invoice_id);
if(is_array($invoice_items)) {
foreach($invoice_items as $invoice_item) {
unset($invoice_item['invoice_item_id']);
$invoice_item['invoice_id'] = $new_invoice_id;
$sql = _get_sql_insert('invoice_item',$invoice_item);
$app->db->query($sql);
}
}
if($reminder['reminder_step'] == $company['reminder_fee_step']) {
$reminder_fee = 0;
if(isset($company['reminder_fee'])) $reminder_fee = $company['reminder_fee'];
// add the reminder fee
if($reminder_fee > 0) {
$invoice_item = array('invoice_id' => $new_invoice_id,
'quantity' => 1,
'price' => $reminder_fee,
'vat' => '0',
'description' => $wb['reminder_item_description_txt']);
$sql = _get_sql_insert('invoice_item',$invoice_item);
$app->db->query($sql);
}
}
$app->db->query("UPDATE invoice SET status_reminded = 'y', status_paid = 'n' WHERE invoice_number = '".$app->db->quote($invoice['invoice_number'])."'");
// Calculate the invoice amount
$tmp = $app->db->queryOneRecord("SELECT sum(quantity*price+(quantity*price*(vat/100))) as amount FROM invoice_item WHERE invoice_id = ".$new_invoice_id);
$reminder_amount = $tmp['amount'];
// Create the PDF invoice
$invoice_pdf_template = $company['invoice_pdf_template'];
$pdf_invoice_classname = 'PDFInvoice_'.$invoice_pdf_template;
if(!class_exists('PDFInvoice_'.$invoice_pdf_template)) {
include('templates/pdf_invoice/'.$invoice_pdf_template.'/make_pdf_invoice.php');
}
$pdf=new $pdf_invoice_classname();
$pdf->AliasNbPages();
$pdf->createInvoice($new_invoice_id);
$pdf_content = $pdf->Output('doc.pdf','S');
$app->uses('billing_functions');
$reminder_filename = $app->billing_functions->get_invoice_filename($pdf->invoice['invoice_number'], $reminder['reminder_step']);
if(is_file($reminder_filename)) unlink($reminder_filename);
file_put_contents($reminder_filename,$pdf_content);
unset($pdf);
// Update the tstaus of the reminder
$app->db->query("UPDATE invoice SET status_printed = 'y', invoice_amount = $reminder_amount WHERE invoice_id = ".$new_invoice_id);
$to = trim($client['email']);
$from_name = trim($company['sender_name']);
$from = trim($company['sender_email']);
if(!$from) $from = trim($company['email']);
$bcc = trim($company['bcc_email']);
//* Replace placeholders {INVOICE_NUMBER}, {CONTACT_NAME}, {COMPANY_NAME}
$text_message = str_replace("\r\n","\n",$email_template['message']);
/*
$email_message = str_replace('{INVOICE_NUMBER}',$rec['invoice_number'],$email_message);
$email_message = str_replace('{CONTACT_NAME}',$rec['contact_name'],$email_message);
$email_message = str_replace('{COMPANY_NAME}',$rec['company_name'],$email_message);
*/
$subject = $email_template['subject'];
/*
$email_subject = str_replace('{INVOICE_NUMBER}',$rec['invoice_number'],$email_subject);
$email_subject = str_replace('{CONTACT_NAME}',$rec['contact_name'],$email_subject);
$email_subject = str_replace('{COMPANY_NAME}',$rec['company_name'],$email_subject);
*/
$invoice_settings = $app->db->queryOneRecord("SELECT * FROM invoice_settings WHERE invoice_settings_id = 1");
if(isset($invoice_settings['invoice_pay_link']) && $invoice_settings['invoice_pay_link'] != '') {
$invoice_pay_link = $invoice_settings['invoice_pay_link'].$invoice['idhash'];
} else {
$ispconfig_url = $app->functions->get_ispconfig_url();
$invoice_pay_link = $ispconfig_url.'/billing/payments/pay.php?id='.$invoice['idhash'];
}
//$text_message = str_replace('{INVOICE_PAY_LINK}',$invoice_pay_link,$text_message);
$tmp = explode('-',$invoice['invoice_date']);
$invoice_date = date($app->lng('conf_format_dateshort'),mktime(0, 0, 0, $tmp[1], $tmp[2], $tmp[0]));
$trans = array( '{REMINDER_STEP}' => $reminder['reminder_step'],
'{INVOICE_NUMBER}' => $invoice['invoice_number'],
'{INVOICE_AMOUNT}' => $app->functions->currency_format($invoice['invoice_amount'], 'client'),
'{INVOICE_CURRENCY}' => $invoice_settings['currency'],
'{INVOICE_DATE}' => $invoice_date,
'{INVOICE_PAY_LINK}' => $invoice_pay_link,
'{CONTACT_NAME}' => $client['contact_name'],
'{COMPANY_NAME}' => $client['company_name'],
'{CLIENT_CONTACT_NAME}' => $client['contact_name'],
'{CLIENT_COMPANY_NAME}' => $client['company_name'],
'{CLIENT_COMPANY_ID}' => $client['company_id'],
'{CLIENT_CUSTOMER_NO}' => $client['customer_no'],
'{CLIENT_VAT_ID}' => $client['vat_id'],
'{CLIENT_STREET}' => $client['street'],
'{CLIENT_ZIP}' => $client['zip'],
'{CLIENT_CITY}' => $client['city'],
'{CLIENT_STATE}' => $client['state'],
'{CLIENT_COUNTRY}' => $client['country'],
'{CLIENT_TELEPHONE}' => $client['telephone'],
'{CLIENT_MOBILE}' => $client['mobile'],
'{CLIENT_FAX}' => $client['fax'],
'{CLIENT_EMAIL}' => $client['email'],
'{CLIENT_INTERNET}' => $client['internet'],
'{CLIENT_ICQ}' => $client['icq'],
'{CLIENT_NOTES}' => $client['notes'],
'{CLIENT_BANKACCOUNT_OWNER}' => $client['bank_account_owner'],
'{CLIENT_BANKACCOUNT_NO}' => $client['bank_account_number'],
'{CLIENT_BANK_CODE}' => $client['bank_code'],
'{CLIENT_BANK_NAME}' => $client['bank_name'],
'{CLIENT_BANKACCOUNT_IBAN}' => $client['bank_account_iban'],
'{CLIENT_BANKACCOUNT_SWIFT}' => $client['bank_account_swift'],
'{ISSUER_COMPANY_NAME}' => $company['company_name'],
'{ISSUER_COMPANY_NAME_SHORT}' => $company['company_name_short'],
'{ISSUER_CONTACT_NAME}' => $company['contact_name'],
'{ISSUER_CEO_NAME}' => $company['ceo_name'],
'{ISSUER_VAT_ID}' => $company['vat_id'],
'{ISSUER_TAX_ID}' => $company['tax_id'],
'{ISSUER_COMPANY_REGISTER}' => $company['company_register'],
'{ISSUER_STREET}' => $company['street'],
'{ISSUER_ZIP}' => $company['zip'],
'{ISSUER_CITY}' => $company['city'],
'{ISSUER_STATE}' => $company['state'],
'{ISSUER_COUNTRY}' => $company['country'],
'{ISSUER_TELEPHONE}' => $company['telephone'],
'{ISSUER_FAX}' => $company['fax'],
'{ISSUER_EMAIL}' => $company['email'],
'{ISSUER_INTERNET}' => $company['internet'],
'{ISSUER_BANKACCOUNT_OWNER}' => $company['bank_account_owner'],
'{ISSUER_BANKACCOUNT_NO}' => $company['bank_account_number'],
'{ISSUER_BANK_CODE}' => $company['bank_code'],
'{ISSUER_BANK_NAME}' => $company['bank_name'],
'{ISSUER_BANKACCOUNT_IBAN}' => $company['bank_account_iban'],
'{ISSUER_BANKACCOUNT_SWIFT}' => $company['bank_account_swift'],
);
$text_message = strtr($text_message, $trans);
$subject = strtr($subject, $trans);
//* Send email
if($to != '') {
$app->functions->mail($to, $subject, $text_message, $from, $reminder_filename, 'application/pdf', '', '', $bcc, $from_name);
// somehow (for me) bcc recipients do not really work, so I send a second mail to the bcc
$app->functions->mail($bcc, $subject, $text_message, $from, $reminder_filename, 'application/pdf', '', '', $bcc, $from_name);
$msg .= 'Sent reminder for invoice '.$invoice['invoice_number'].' to '.$to.'
';
} else {
$error .= 'No email address pecified for '.$invoice['invoice_number'].' client '.$client['contact_name'].' :: '.$client['company_name'].'
';
}
}
}
} else {
//* Get all payment terms
$payment_terms = array();
$tmp_payment_terms = $app->db->queryAllRecords("SELECT invoice_payment_term_id AS id,due_days FROM invoice_payment_term");
foreach($tmp_payment_terms as $tmp) {
$key = $tmp['id'];
$payment_terms[$key] = $tmp['due_days'];
}
unset($tmp_payment_terms);
unset($tmp);
unset($key);
$max_steps = array();
$tmp_max_step = $app->db->queryAllRecords('SELECT invoice_company_id as id, reminder_steps FROM invoice_company');
foreach($tmp_max_step as $tmp) {
$key = $tmp['id'];
$max_steps[$key] = $tmp['reminder_steps'];
}
unset($tmp_max_step);
unset($tmp);
unset($key);
//* Select all unpaid invoices
$sql = "SELECT * FROM client, invoice WHERE client.client_id = invoice.client_id AND invoice.status_printed = 'y' AND invoice.status_paid = 'n' ORDER BY invoice.invoice_date";
$result = $app->db->queryAllRecords($sql);
$now_tstamp = time();
$invoices_overdue = array();
$invoices = array();
if(is_array($result)) {
reset($result);
foreach($result as $invoice) {
// why this foreach?
// reminders have the same invoice number, so only the most recent invoice or reminder is kept in the array
$invoices[$invoice['invoice_number']] = $invoice;
}
unset($result);
foreach($invoices as $invoice) {
//* Check if onvoice is overdue
if($invoice['reminder_step'] >= $max_steps[$invoice['invoice_company_id']]) continue; // no more reminders!
$invoice_date_parts = explode('-',$invoice['invoice_date']);
$payment_terms_id = $invoice['payment_terms'];
$due_date_tstamp = mktime(0, 0, 0, $invoice_date_parts[1] , (int)$invoice_date_parts[2]+$payment_terms[$payment_terms_id], (int)$invoice_date_parts[0]);
$invoice_date_tstamp = mktime(0, 0, 0, $invoice_date_parts[1] , (int)$invoice_date_parts[2], (int)$invoice_date_parts[0]);
if($due_date_tstamp + (3600*24) < $now_tstamp) {
$invoices_overdue[] = array('invoice_date' => date($app->lng('conf_format_dateshort'),$invoice_date_tstamp),
'due_date' => date($app->lng('conf_format_dateshort'),$due_date_tstamp),
'next_reminder_step' => $invoice['reminder_step'] + 1,
'invoice_number' => $invoice['invoice_number'],
'invoice_id' => $invoice['invoice_id'],
'contact_name' => $invoice['contact_name'],
'company_name' => $invoice['company_name']
);
}
}
}
$app->tpl->setLoop('invoices',$invoices_overdue);
if(count($invoices_overdue) > 0) {
$app->tpl->setVar('show_list',1);
} else {
$msg .= $app->lng('No invoices are overdue.');
$page_back_link = 'billing/reminder_list.php';
}
//* Insert reminder email template list
$sql = "SELECT invoice_message_template_id, template_name FROM invoice_message_template WHERE template_type = 'reminder'";
$records = $app->db->queryAllRecords($sql);
$email_template_select = "";
if(is_array($records)) {
foreach( $records as $rec) {
//$selected = ($ip["ip_address"] == $this->dataRecord["ip_address"])?'SELECTED':'';
$email_template_select .= "\r\n";
}
}
$app->tpl->setVar("email_template_id",$email_template_select);
unset($rec);
unset($records);
}
$app->tpl->setVar('page_back_link',$page_back_link);
$app->tpl->setVar('msg',$msg);
$app->tpl->setVar('error',$error);
$app->tpl->setVar($wb);
$app->tpl_defaults();
$app->tpl->pparse();
// Helper function to create sql insert statements based on a array
function _get_sql_insert($table_name,$fields) {
global $app;
$key_string = '';
$value_string = '';
foreach($fields as $key => $val) {
$key_string .= $key.',';
$value_string .= "'".$app->db->quote($val)."',";
}
$key_string = substr($key_string,0,-1);
$value_string = substr($value_string,0,-1);
$sql = 'INSERT INTO '.$table_name.' ('.$key_string.') VALUES ('.$value_string.')';
return $sql;
}
?>