<?php
/*
Copyright (c) 2010, Till Brehm, projektfarm Gmbh
All rights reserved.
*/

require_once('../../lib/config.inc.php');
require_once('../../lib/app.inc.php');

//* Check permissions for module
$app->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.').'<br />';
	if(count($_POST['invoice_id']) == 0) $error .= $app->lng('No pending invoices selcted.').'<br />';
	
	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.'<br />';
			} else {
				$error .= 'No email address pecified for '.$invoice['invoice_number'].' client '.$client['contact_name'].' :: '.$client['company_name'].'<br />';
			}
		}
	}
} 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 = "<option value='0'>- Select template -</option>";
	if(is_array($records)) {
		foreach( $records as $rec) {
			//$selected = ($ip["ip_address"] == $this->dataRecord["ip_address"])?'SELECTED':'';
			$email_template_select .= "<option value='$rec[invoice_message_template_id]'>$rec[template_name]</option>\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;
}


?>