#!/usr/bin/php
<?php   
/*----------------------------------------------------------------------------*/
// Copyright (C) 2009-2010  Paul Yasi (citrusdb.org)
// This script will email a receipt to customers who have the automatic receipt
// field marked Yes in their billing record
//
// This script should be put into the cron to be run nightly
//
/*----------------------------------------------------------------------------*/

// set the email address that this message comes from
$from_email = "yourname@example.com";

$subject = "Receipt for Internet Service";

// Includes
include('./include/config.inc.php');
include('./include/database.inc.php');
include("$lang");
include('./include/billing.inc.php');
include('./include/citrus_base.php');
include('./include/support.inc.php');

// Select records that paid today and requested a receipt from the database
$query = "SELECT b.id b_id, b.contact_email, b.name, b.company, ".
  "b.account_number, ph.id ph_id, ph.billing_amount, ph.creation_date, ".
  "ph.payment_type, ph.creditcard_number, ph.check_number ".
  "FROM billing b ".
  "LEFT JOIN customer c on c.account_number = b.account_number ".
  "LEFT JOIN payment_history ph ON ph.billing_id = b.id ".
  "WHERE b.automatic_receipt = 'y' AND ph.creation_date = CURRENT_DATE ".
  "AND ph.billing_amount > 0";

$DB->SetFetchMode(ADODB_FETCH_ASSOC);
$receiptresult = $DB->Execute($query) or die ("automatic_receipt select for today failed");

// go through each receipt recipient
while ($myreceiptresult = $receiptresult->FetchRow()) {
  
  // initialize the message body to be empty
  $message = "";

  $paymentid = $myreceiptresult['ph_id'];
  $amount = $myreceiptresult['billing_amount'];
  $billingid = $myreceiptresult['b_id'];
  $payment_date = $myreceiptresult['creation_date'];
  $payment_type = $myreceiptresult['payment_type'];
  $creditcard_number = $myreceiptresult['creditcard_number'];
  $check_number = $myreceiptresult['check_number'];
  
  /*-------------------------------------------------------------------------*/
  // print paid_amounts from billing_details
  /*-------------------------------------------------------------------------*/
  
  $query = "SELECT * FROM billing b ".
    "LEFT JOIN general g ON g.id = b.organization_id ".
    "WHERE b.id = $billingid";
  $DB->SetFetchMode(ADODB_FETCH_ASSOC);
  $result = $DB->Execute($query) or die ("billing table Query Failed");
  $myresult = $result->fields;
  $billing_name = $myresult['name'];
  $billing_company = $myresult['company'];
  $billing_street = $myresult['street'];
  $billing_city = $myresult['city'];
  $billing_state = $myresult['state'];
  $billing_zip = $myresult['zip'];
  $billing_account_number = $myresult['account_number'];
  $billing_email = $myresult['contact_email'];
  $org_name = $myresult['org_name'];
  
  $message .= "$org_name\n".
    "$l_paymentreceipt\n".
    "$l_accountnumber: $billing_account_number\n\n".
    "$billing_name\n".
    "$billing_company\n".
    "$billing_street\n".
    "$billing_city $billing_state $billing_zip\n\n";
  
  $human_date = humandate($payment_date, $lang);

  

  if ($payment_type == "creditcard") {    
    // wipe out the middle of the card number
    $length = strlen($creditcard_number);
    $firstdigit = substr($creditcard_number, 0,1);
    $lastfour = substr($creditcard_number, -4);
    $creditcard_number = "$firstdigit" . "***********" . "$lastfour";
    
    $message .= "$l_paid with $payment_type ($creditcard_number), ".
      "$amount on $human_date for:\n";
  } else {
    $message .= "$l_paid with $payment_type (number: $check_number), ".
      "$amount on $human_date for:\n";    
  }
  
  // get the resulting list of services that have payment applied with
  // the matching payment_history_id
  //$DB->debug = true;
  
  $query = "SELECT bd.original_invoice_number, bd.paid_amount,".
    "bh.from_date, bh.to_date, ".
    "bd.billed_amount, ms.service_description, tr.description FROM ".
    "billing_details bd ".
    "LEFT JOIN user_services us ON us.id = bd.user_services_id ".
    "LEFT JOIN master_services ms ON ms.id = us.master_service_id ".
    "LEFT JOIN taxed_services ts ON ts.id = bd.taxed_services_id ".
    "LEFT JOIN tax_rates tr ON tr.id = ts.tax_rate_id ".
    "LEFT JOIN billing_history bh ON bh.id = bd.original_invoice_number ".
    "WHERE bd.payment_history_id = '$paymentid' ORDER BY bd.taxed_services_id";
  $DB->SetFetchMode(ADODB_FETCH_ASSOC);
  $result = $DB->Execute($query) or die ("Receipt Query Failed");
  
  while ($myresult = $result->FetchRow()) {
    $invoice = $myresult['original_invoice_number'];
    $description = $myresult['service_description'];
    $tax_description = $myresult['description'];
    $from_date = humandate($myresult['from_date'],$lang);
    $to_date = humandate($myresult['to_date'],$lang);
    $paid_amount = sprintf("%.2f",$myresult['paid_amount']);
    $billed_amount = sprintf("%.2f",$myresult['billed_amount']);  
    
    $owed_amount = sprintf("%.2f",$billed_amount - $paid_amount);
    
    if ($tax_description) {
      // print the tax as description instead
      $message .= "$invoice\t   $tax_description\t$paid_amount\n";
    } else {
      $message .= "$invoice\t$description ($from_date $l_to $to_date)\t$paid_amount\n";
    }
  }
  echo "\n";
  echo "$message";
  // send the email message for this user
  $headers = "From: $from_email \n";
  mail ($billing_email, $subject, $message, $headers);

 } // end while myreceipt result

?>
