HOWTO Create a Christmas / New Year / Holiday mailing list for top Customers

This is just some SQL to generate a mailing list of your top customers for the year. You can use it to send a Thank You card. Remember to modify to suit your circumstances.

SELECT debtortrans.debtorno, 
debtorsmaster.name, 
debtorsmaster.address1, 
debtorsmaster.address2, 
debtorsmaster.address3, 
debtorsmaster.address4, 
debtorsmaster.address5, 
debtorsmaster.address6, 
sum( debtortrans.ovamount ) as amount
FROM debtortrans, debtorsmaster
WHERE year( trandate ) =2006
AND debtortrans.debtorno = debtorsmaster.debtorno
AND debtortrans.type =10
GROUP BY debtorno
HAVING sum( debtortrans.ovamount ) >1000
ORDER BY amount DESC
LIMIT 0 , 30

HOWTO Send HTML Formated Emails containing WebERP queries, example of Debtor Balance Report

This is just a hacked up script to send a formatted email containing a list of all Debtors (customers) that have outstanding invoices with your company. You can send this to your boss, accounts people etc. Feel free to clean-up and optimse.

You can execute it via cron, something like:

# Send WebERP Debtor reports
30 07 * * 1 php /var/www/html/weberp/EmailDebtors_script.php

<?
ob_start();
$date = date("D M d Y H:i:s");

?>
<html>
<head>
<title>Accounting System - Confidential</title>
<link rel="stylesheet" href="http://whatever.com/style.css" type="text/css">
</head>
<body>
<p><img src=http://www.someserver/someimage.jpg width=200 height=40></img></p>
<p>WebERP Accounting System
<br>
<? echo " Server Date and Time is "; ?>

<br>
<? 
echo "Report generated from {$_SERVER['REMOTE_ADDR']}"; 
?>
</p>
<?
$connection = mysql_connect (localhost, USER,PASSWORD) or 
 die("Error connecting to database");
mysql_select_db (DATABASENAME, $connection) or die("Error selecting database");
?>
<p>
The following Invoices are still unpaid.
</p>
<p>
<table width="100%">
<tr class="row"><td>Company</td><td>Type</td><td>Number</td>
<td>Account Name</td><td>Issue Date</td><td>Balance</td></tr>
<?php
$result = mysql_query("SELECT companies.coyname, systypes.typename, 
debtortrans.transno, debtorsmaster.name, 
date_format(debtortrans.trandate,'%D %b %y') as invoicedate, 
(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight +
debtortrans.ovdiscount - debtortrans.alloc) as balance
FROM debtorsmaster, paymentterms, debtortrans, systypes, companies
WHERE systypes.typeid = debtortrans.type
AND debtorsmaster.paymentterms = paymentterms.termsindicator
AND debtorsmaster.debtorno = debtortrans.debtorno
AND ABS(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + 
debtortrans.ovdiscount - debtortrans.alloc)>0.1");
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){
?>
<tr <?php if($i%2 == 1){
echo "class='body2'";
}else{
echo "class='body1'";}?>>
<td>










</table>
</p>
</body>
</html>
<?
$body = ob_get_contents();
ob_end_clean();
$eol="\n";
# To Email Address
$emailaddress="
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 , 
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ";
# Message Subject
$emailsubject="Outstanding Invoices - ".date("jS M Y H:i:s");
# Message Body
$headers .= 'From: Weberp <
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 >'.$eol;
$headers .= 'Reply-To: Weberp <
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 >'.$eol;
$headers .= 'Return-Path: Weberp <
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 >'.$eol;  
  // these two to set reply address
$headers .= "X-Mailer: PHP v".phpversion().$eol;      
  // These two to help avoid spam-filters
# Boundry for marking the split & Multitype Headers
$mime_boundary=md5(time());
$headers .= 'MIME-Version: 1.0'.$eol;
$headers .= "Content-Type: text/html; charset=iso-8859-1".$eol;
$headers .= "Content-Transfer-Encoding: 8bit".$eol;
$msg = "";
$msg .= $body.$eol.$eol;
# Finished
#$msg .= "--".$mime_boundary."--".$eol.$eol; 
  // finish with two eol's for better security. see Injection.
# SEND THE EMAIL
  mail($emailaddress, $emailsubject, $msg, $headers);
?>

< Prev		Next >

Logic Instinct