Revision history for HOWTO
Deletions:
Additions:
====How To Modify InvoiceLayout====
Deletions:
webERP produces general ledger transactions to record when the Purchase Order or Sales Invoice is created.
<Phil> **webERP does not handle cash based tax reporting** </Phil>
Currently the GST (Goods and Services Tax, a type of Sales Tax) Reporting module, will calulate the amounts of tax inputs and outputs based on accrued transactions, NOT allocated transactions. ie. Reporting will be based on an accrual method.
For Australian GST reporting, some businesses are recommended to use a "Cash Basis" reporting system. Under the cash method, income is not counted until cash (or any payment) is actually received, and expenses are not counted until actually paid.
In a nutshell, the two methods differ only in the timing of when transactions, including sales and purchases, are credited or debited to your accounts.
The report to modify is Tax.php
(<Phil> - **NOTE: This would probably not be acceptable to the ATO since under a cash basis GST is payable based on all revenue received this statement only shows the tax on invoices that are fully paid. Also the date of the invoice is not relevant under a payments basis so the logic of this SQL is not appropriate to a cash basis of accounting this is really not reliable**</Phil>)
The lines to change are:
change this statement from
%%
$SQL = 'SELECT debtortrans.transno,
debtortrans.type,
systypes.typename,
debtortrans.trandate,
debtortrans.debtorno,
debtorsmaster.name,
debtortrans.branchcode,
debtortrans.order_,
(ovamount+ovfreight)/rate AS netamount,
ovfreight/rate AS freightamount,
ovgst/rate AS tax
FROM debtortrans
INNER JOIN debtorsmaster ON debtortrans.debtorno=debtorsmaster.debtorno
INNER JOIN systypes ON debtortrans.type=systypes.typeid
WHERE debtortrans.prd >= ' . ($_POST['ToPeriod'] - $_POST['NoOfPeriods'] + 1) . '
AND debtortrans.prd <= ' . $_POST['ToPeriod'] . '
AND (debtortrans.type=10 OR debtortrans.type=11)
ORDER BY debtortrans.id';
%%
change to
%%
$SQL = 'SELECT debtortrans.transno,
debtortrans.type,
systypes.typename,
debtortrans.trandate,
debtortrans.debtorno,
debtorsmaster.name,
debtortrans.branchcode,
debtortrans.order_,
(ovamount+ovfreight)/rate AS netamount,
ovfreight/rate AS freightamount,
ovgst/rate AS tax
FROM debtortrans
INNER JOIN debtorsmaster ON debtortrans.debtorno=debtorsmaster.debtorno
INNER JOIN systypes ON debtortrans.type=systypes.typeid
WHERE debtortrans.prd >= ' . ($_POST['ToPeriod'] - $_POST['NoOfPeriods'] + 1) . '
AND debtortrans.prd <= ' . $_POST['ToPeriod'] . '
AND (debtortrans.type=10 OR debtortrans.type=11)
AND debtortrans.alloc = (debtortrans.ovamount+debtortrans.ovgst)
ORDER BY debtortrans.id';
%%
change this statement from
%%
$SQL = "SELECT supptrans.type,
supptrans.suppreference,
systypes.typename,
supptrans.trandate,
suppliers.suppname,
supptrans.ovamount/supptrans.rate AS netamount,
supptrans.ovgst/supptrans.rate AS taxamt
FROM supptrans
INNER JOIN suppliers ON supptrans.supplierno=suppliers.supplierid
INNER JOIN systypes ON supptrans.type=systypes.typeid
WHERE supptrans.trandate >= '" . $StartDateSQL . "'
AND supptrans.trandate <= '" . FormatDateForSQL($PeriodEnd) . "'
AND (supptrans.type=20 OR supptrans.type=21)
ORDER BY supptrans.id";
%%
change to
%%
$SQL = "SELECT supptrans.type,
supptrans.suppreference,
systypes.typename,
supptrans.trandate,
suppliers.suppname,
supptrans.ovamount/supptrans.rate AS netamount,
supptrans.ovgst/supptrans.rate AS taxamt
FROM supptrans
INNER JOIN suppliers ON supptrans.supplierno=suppliers.supplierid
INNER JOIN systypes ON supptrans.type=systypes.typeid
WHERE supptrans.trandate >= '" . $StartDateSQL . "'
AND supptrans.trandate <= '" . FormatDateForSQL($PeriodEnd) . "'
AND (supptrans.type=20 OR supptrans.type=21)
AND supptrans.alloc = (supptrans.ovamount+supptrans.ovgst)
ORDER BY supptrans.id";
%%
Now the GST report will exclude transactions where the money has not been allocated (ie. received or paid), and the GST reports will be "cash based".
====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 "; ?>
<? echo $date; ?>
<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>
<?php echo $result_ar['coyname']; ?></td>
<?php echo $result_ar['typename']; ?></td>
<td><?php echo $result_ar['transno']; ?></td>
<td><?php echo $result_ar['name']; ?></td>
<td><?php echo $result_ar['invoicedate']; ?></td>
<td>
<?php echo $result_ar['balance']; ?>
</td>
</tr>
<?php
$i+=1;
}
?>
</table>
</p>
</body>
</html>
<?
$body = ob_get_contents();
ob_end_clean();
$eol="\n";
# To Email Address
$emailaddress="someone@somewhere.com, someoneelse@somewherelse.com";
# Message Subject
$emailsubject="Outstanding Invoices - ".date("jS M Y H:i:s");
# Message Body
$headers .= 'From: Weberp <weberp@someserver.com>'.$eol;
$headers .= 'Reply-To: Weberp <weberp@someserver.com>'.$eol;
$headers .= 'Return-Path: Weberp <weberp@someserver.com>'.$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);
?>
%%
Additions:
Note:There are some restrictions on the components that can be used in creating bills of material for assemblies. With assemblies - where the components are automatically issued on invoicing - the components cannot be serialised or batch controlled since this would require specification of the serial numbers/batch/lots sold and therefore defeats the automatic issue of components. Items that contain serialised or batch controlled components must be manufactured first where the component serial numbers/batch/lots can be specified in a logical way.
Deletions:
Additions:
Note:There are some restrictions on creating bills of material for kit-sets and assemblies. With assemblies - where the components are automatically issued on invoicing - the components cannot be serialised or batch controlled since this would require specification of the serial numbers/batch/lots sold and therefore defeats the automatic issue of components. Items that contain serialised or batch controlled components must be manufactured first where the component serial numbers/batch/lots can be specified in a logical way.
Additions:
You can even set up bills of material to come into effect from some future date and retain bills of material that were in place then phased out - this is useful for traceability etc. This is done by selecting effective to and from dates - a component is effective as a component's effective from date is prior to today and its effective to date is after today.
Additions:
You can add as many or as few components as you like. For each component you need to select the work centre where the component is added and the quantity of the component required to make one of the parent item (the parent item is the item being made/manufactured).
Additions:
{{image class="center" alt="BOM" title="Bill of Material Entry" url="images/BOM.jpg"}}
Deletions:
Additions:
====HOWTO Setup a Bill Of Material (BOM)====
~1. First you need to ensure that all the components of the item are defined as items in webERP using the
~Inventory tab -> Add A New Item
~The component items can be purchased or manufactured but not assemblies or kit-sets
~2. Once all the components are defined you then need to set up an item as manufactured, assembly or kit-set that you want to set up your BOM for.
~3. Setup a work centre where the work required in manufacutring or assembling your item will take place.
~Manufacturing tab -> Work Centre
~The work centre is associated with an inventory or factory location - even assemblies and kit-sets need to have a work centre but this can be set up as a default and the work centre is not actually used in the case of assemblies or kit-sets.
~4. Go into Manufacturing tab -> Bills Of Material (under the Maintenance section of the main menu and select the item that you wish to create the BOM for.
{{image class="center" alt="BOM" title="Bill of Material Entry" url="images/BOM.JPG"}}
~1. First you need to ensure that all the components of the item are defined as items in webERP using the
~Inventory tab -> Add A New Item
~The component items can be purchased or manufactured but not assemblies or kit-sets
~2. Once all the components are defined you then need to set up an item as manufactured, assembly or kit-set that you want to set up your BOM for.
~3. Setup a work centre where the work required in manufacutring or assembling your item will take place.
~Manufacturing tab -> Work Centre
~The work centre is associated with an inventory or factory location - even assemblies and kit-sets need to have a work centre but this can be set up as a default and the work centre is not actually used in the case of assemblies or kit-sets.
~4. Go into Manufacturing tab -> Bills Of Material (under the Maintenance section of the main menu and select the item that you wish to create the BOM for.
{{image class="center" alt="BOM" title="Bill of Material Entry" url="images/BOM.JPG"}}
Additions:
=====Various Guides for General Use=====
Deletions:
{{TOC}}
%%