|
|
|
Key Daily Statistics
{This Example generates Key daily statistics from your database and either displays it on your screen or emails it to nominated recipients
The KPI’s contained in this script are split into 6 sections
1 = Job Order Statistics
2 = Sales Orders Statistics
3 = POS Statistics
4 = Invoice Statistics
5 = Debtor Statistics
6 = Inventory and WIP Statistics
You can define an unlimited number of Email recipients and, against each recipient, nominate which of the above sections they are allowed to see.
Other points regarding this script.
1. You can run the script and have the results returned to your screen. This useful if you wish to view the results when amending the KPIs. This is the method currently set in the script below.
2. As scripts can be run from the CMD line on your PC this script can be scheduled to automatically run at any time day or night. In this example it is assumed that it will be run after midnight and shows statistics from the previous day. }
var {Variable section}
EmailList: TStringlist;
TheKPIOptions,KPINumber,DailySalesOrderCount,DailyJobOrderCount: String;
x,y,z,TheOptionsLength: Integer;
TheUserEmail,TheEmailBody,DailySalesOrderValue,CurrentDebtorBalance : String;
CurrentStockValue, TheSQL, TheAgingID, AgingMethod: string;
Period1Caption,Period2Caption,Period3Caption,Period4Caption,Period5Caption: string;
PeriodACaption,PeriodBCaption,OverdueSalesValue,DailyPaymentsIn: string;
Period1DebtorBalance, Period2DebtorBalance, Period3DebtorBalance: string;
Period4DebtorBalance, Period5DebtorBalance, TotalDebtorBalance: string;
DailyDepositsTaken,DailyAvgSalesOrderValue: string;
DailyJobOrderValue,DailyAvgJobOrderValue: string;
CurrentAssemblyWIP,CurrentJobWIP: string;
DailySalesInvoiceValue,DailyAvgSalesInvoiceValue,DailySalesInvoiceCount: string;
DailyPOSValue,DailyAvgPOSValue,DailyPOSCount: string;
TheCompanyName: string;
AgingRun : boolean;
Const {Constant section}
{You will need to change the Email Host name and the sender address}
TheEmailHostName = 'mail.development-x.com';
TheEmailSenderAddress = 'KPI@development-x.com';
TheEmailSubject = 'Ostendo Key Daily Statistics for ' + datetostr(date - 1);
{The constant below allows testing of this script without emailing
If set to False a popup message will display for each email recipient
rather than actually emailing, otherwise if it is True emails will be
sent to each recipient}
EmailTheResult = false;
procedure CreateKPIEmailsAndSend;
begin
{Email Section with KPI routines per recepient}
try
EmailList := TStringList.Create;
{All email recepients are defined based on the follwoing structure.
The first field is the email address with a colon (:) seperating it
from the Daily statistic groups required for that recepient. Each
statistic group number is seperated by a comma (,). An example of
the syntax is below. Many recepients with varying statistic group
options can be defined}
EmailList.add('info@development-x.com:1,2,3,4,5,6'); {All Groups}
EmailList.add('sales@development-x.com:2,4,5,6'); {Typical distribution Groups}
{The group numbers are as follows:
1 = Job Order Statistics
2 = Sales Orders Statistics
3 = POS Statistics
4 = Invoice Statistics
5 = Debtor Statistics
6 = Inventory and WIP Statistics}
AgingRun := false;
for x := 0 to EmailList.count - 1 do
begin
TheUserEmail := ParseString(EmailList.strings[x], ':', 0);
TheKPIOptions := ParseString(EmailList.strings[x], ':', 1);
for z := 0 to NoOfKPIS do
begin
KPINumber := ParseString(TheKPIOptions, ',', z);
case KPINumber of
'1': {Job Order Statistics}
begin
TheEmailBody := TheEmailBody + #13 + #13 + 'Job Order Statistics';
TheEmailBody := TheEmailBody + #13 + '===================================';
if GetSQLResult('select count(sysuniqueid) from JobHeader where (orderdate + 1)= ''now''') = null then
begin
DailyJobOrderCount := '0';
end
else
begin
DailyJobOrderCount := GetSQLResult('select count(sysuniqueid) from JobHeader where (orderdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Job Order Count: ' + DailyJobOrderCount;
if GetSQLResult('select avg(ORIGINALORDERAMOUNT) from JobHeader where (orderdate + 1)= ''now''') = null then
begin
DailyAvgJobOrderValue := '0';
end
else
begin
DailyAvgJobOrderValue := GetSQLResult('select avg(ORIGINALORDERAMOUNT) from JobHeader where (orderdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Average Order Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyAvgJobOrderValue));
if GetSQLResult('select sum(ORIGINALORDERAMOUNT) from JobHeader where (orderdate + 1)= ''now''') = null then
begin
DailyJobOrderValue := '0';
end
else
begin
DailyJobOrderValue := GetSQLResult('select sum(ORIGINALORDERAMOUNT) from JobHeader where (orderdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Job Order Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyJobOrderValue));
end;
'2': {Sales Order Statistics}
begin
TheEmailBody := TheEmailBody + #13 + #13 + 'Sales Order Statistics';
TheEmailBody := TheEmailBody + #13 + '===================================';
if GetSQLResult('select count(sysuniqueid) from SalesHeader where (orderdate + 1)= ''now''') = null then
begin
DailySalesOrderCount := '0';
end
else
begin
DailySalesOrderCount := GetSQLResult('select count(sysuniqueid) from SalesHeader where (orderdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Sales Order Count: ' + DailySalesOrderCount;
if GetSQLResult('select avg(ORIGINALORDERAMOUNT) from SalesHeader where (orderdate + 1)= ''now''') = null then
begin
DailyAvgSalesOrderValue := '0';
end
else
begin
DailyAvgSalesOrderValue := GetSQLResult('select avg(ORIGINALORDERAMOUNT) from SalesHeader where (orderdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Average Order Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyAvgSalesOrderValue));
if GetSQLResult('select sum(ORIGINALORDERAMOUNT) from SalesHeader where (orderdate + 1)= ''now''') = null then
begin
DailySalesOrderValue := '0';
end
else
begin
DailySalesOrderValue := GetSQLResult('select sum(ORIGINALORDERAMOUNT) from SalesHeader where (orderdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Sales Order Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailySalesOrderValue));
if GetSQLResult('select sum(REMAININGQTY * ORDERUNITPRICE) from SalesLines,SalesHeader where (salesLines.Requireddate + 1) <= ''now'' and SalesLines.Linestatus = ''Open'' and SalesLines.OrderNumber = SalesHeader.Ordernumber and SalesHeader.orderstatus <> ''Closed'' and SalesHeader.orderstatus <> ''Lost'' and SalesHeader.orderstatus <> ''Quote'' and SalesHeader.orderstatus <> ''Planned''') = null then
begin
OverdueSalesValue := '0';
end
else
begin
OverdueSalesValue := GetSQLResult('select sum(REMAININGQTY * ORDERUNITPRICE) from SalesLines,SalesHeader where (salesLines.Requireddate + 1) <= ''now'' and SalesLines.Linestatus = ''Open'' and SalesLines.OrderNumber = SalesHeader.Ordernumber and SalesHeader.orderstatus <> ''Closed'' and SalesHeader.orderstatus <> ''Lost'' and SalesHeader.orderstatus <> ''Quote'' and SalesHeader.orderstatus <> ''Planned''');
end;
TheEmailBody := TheEmailBody + #13 + ' Overdue Sales Value: ' + FormatFloat('$###,###,##0.00',strtofloat(OverdueSalesValue));
end;
'3': {POS Statistics}
begin
TheEmailBody := TheEmailBody + #13 + #13 + 'POS Statistics';
TheEmailBody := TheEmailBody + #13 + '===================================';
if GetSQLResult('select count(sysuniqueid) from POSHeader where (saledate + 1)= ''now'' and (SALESTATUS = ''Invoiced'' or SALESTATUS = ''OnHold'')') = null then
begin
DailyPOSCount := '0';
end
else
begin
DailyPOSCount := GetSQLResult('select count(sysuniqueid) from POSHeader where (saledate + 1)= ''now'' and (SALESTATUS = ''Invoiced'' or SALESTATUS = ''OnHold'')');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily POS Count: ' + DailyPOSCount;
if GetSQLResult('select avg(SALENETTAMOUNT) from POSHeader where (saledate + 1)= ''now'' and (SALESTATUS = ''Invoiced'' or SALESTATUS = ''OnHold'')') = null then
begin
DailyAvgPOSValue := '0';
end
else
begin
DailyAvgPOSValue := GetSQLResult('select avg(SALENETTAMOUNT) from POSHeader where (saledate + 1)= ''now'' and (SALESTATUS = ''Invoiced'' or SALESTATUS = ''OnHold'')');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Average POS Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyAvgPOSValue));
if GetSQLResult('select sum(SALENETTAMOUNT) from POSHeader where (saledate + 1)= ''now'' and (SALESTATUS = ''Invoiced'' or SALESTATUS = ''OnHold'')') = null then
begin
DailyPOSValue := '0';
end
else
begin
DailyPOSValue := GetSQLResult('select sum(SALENETTAMOUNT) from POSHeader where (saledate + 1)= ''now'' and (SALESTATUS = ''Invoiced'' or SALESTATUS = ''OnHold'')');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily POS Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyPOSValue));
end;
'4': {Invoice Statistics}
begin
TheEmailBody := TheEmailBody + #13 + #13 + 'Sales Invoice Statistics';
TheEmailBody := TheEmailBody + #13 + '===================================';
if GetSQLResult('select count(sysuniqueid) from SalesInvoiceHeader where (invoicedate + 1)= ''now''') = null then
begin
DailySalesInvoiceCount := '0';
end
else
begin
DailySalesInvoiceCount := GetSQLResult('select count(sysuniqueid) from SalesInvoiceHeader where (invoicedate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Sales Invoice Count: ' + DailySalesInvoiceCount;
if GetSQLResult('select avg(INVOICENETTAMOUNT) from SalesInvoiceHeader where (invoicedate + 1)= ''now''') = null then
begin
DailyAvgSalesInvoiceValue := '0';
end
else
begin
DailyAvgSalesInvoiceValue := GetSQLResult('select avg(INVOICENETTAMOUNT) from SalesInvoiceHeader where (invoicedate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Average Invoice Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyAvgSalesInvoiceValue));
if GetSQLResult('select sum(INVOICENETTAMOUNT) from SalesInvoiceHeader where (invoicedate + 1)= ''now''') = null then
begin
DailySalesInvoiceValue := '0';
end
else
begin
DailySalesInvoiceValue := GetSQLResult('select sum(INVOICENETTAMOUNT) from SalesInvoiceHeader where (invoicedate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Sales Invoice Value: ' + FormatFloat('$###,###,##0.00',strtofloat(DailySalesInvoiceValue));
end;
'5': {Debtor Statistics}
begin
TheEmailBody := TheEmailBody + #13 + #13 + 'Debtor and Aging Statistics';
TheEmailBody := TheEmailBody + #13 + '===================================';
if GetSQLResult('select sum(PAYMENTAMOUNT) from CustomerPayments where (paymentdate + 1)= ''now'' and PAYMENTSTYLE = ''Received Payment'' ') = null then
begin
DailyPaymentsIn := '0';
end
else
begin
DailyPaymentsIn := GetSQLResult('select sum(PAYMENTAMOUNT) from CustomerPayments where (paymentdate + 1)= ''now'' and PAYMENTSTYLE = ''Received Payment'' ');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Payments In: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyPaymentsIn));
if GetSQLResult('select sum(DEPOSITAMOUNT) from CustomerDeposits where (depositdate + 1)= ''now''') = null then
begin
DailyDepositsTaken := '0';
end
else
begin
DailyDepositsTaken := GetSQLResult('select sum(DEPOSITAMOUNT) from CustomerDeposits where (depositdate + 1)= ''now''');
end;
TheEmailBody := TheEmailBody + #13 + ' Daily Deposits Taken: ' + FormatFloat('$###,###,##0.00',strtofloat(DailyDepositsTaken));
if (AgingRun = False) then
begin
executeSQL('delete from CustomerAging');
executeSQL('insert into CustomerAging (AGINGASATDATE) values ((cast(''NOW'' as date) -1))');
TheAgingID := GetSQLResult('select max(sysuniqueid) from CustomerAging');
executeSQL('execute procedure CREATE_CUSTOMERAGING (cast(''NOW'' as date) -1) ,' + TheAgingID + ',0,'''','''','''','''',''''');
{Get the Aging Headings}
AgingMethod := GetSQLResult('select CALCULATEDFROM from AGINGPERIODS');
if (AgingMethod = 'Monthly') then
begin
Period1Caption := GetSQLResult('select MONTHLYCAPTIONONE from CustomerAging');
Period2Caption := GetSQLResult('select MONTHLYCAPTIONTWO from CustomerAging');
Period3Caption := GetSQLResult('select MONTHLYCAPTIONTHREE from CustomerAging');
Period4Caption := GetSQLResult('select MONTHLYCAPTIONFOUR from CustomerAging');
Period5Caption := GetSQLResult('select MONTHLYCAPTIONFIVE from CustomerAging');
end
else
begin
Period1Caption := 'Current';
Period2Caption := GetSQLResult('select PERIOD2 from AGINGPERIODS');
Period2Caption := '1 to ' + Period2Caption;
PeriodACaption := GetSQLResult('select PERIOD2 from AGINGPERIODS');
PeriodBCaption := GetSQLResult('select PERIOD3 from AGINGPERIODS');
Period3Caption := PeriodACaption + ' to ' + PeriodBCaption;
PeriodACaption := GetSQLResult('select PERIOD3 from AGINGPERIODS');
PeriodBCaption := GetSQLResult('select PERIOD4 from AGINGPERIODS');
Period4Caption := PeriodACaption + ' to ' + PeriodBCaption;
PeriodACaption := GetSQLResult('select PERIOD4 from AGINGPERIODS');
Period5Caption := PeriodACaption + '+';
end;
end;
AgingRun := True;
if GetSQLResult('select sum(TOTALBALANCE) from CUSTOMERAGINGHEADER') = null then
begin
TotalDebtorBalance := '0';
end
else
begin
TotalDebtorBalance := GetSQLResult('select sum(TOTALBALANCE) from CUSTOMERAGINGHEADER');
end;
TheEmailBody := TheEmailBody + #13 + ' Total Debtor Balance: ' + FormatFloat('$###,###,##0.00',strtofloat(TotalDebtorBalance));
if GetSQLResult('select sum(CURRENTBALANCE) from CUSTOMERAGINGHEADER') = null then
begin
Period1DebtorBalance := '0';
end
else
begin
Period1DebtorBalance := GetSQLResult('select sum(CURRENTBALANCE) from CUSTOMERAGINGHEADER');
end;
TheEmailBody := TheEmailBody + #13 + ' * ' + Period1Caption + ' Debtor Balance: ' + FormatFloat('$###,###,##0.00',strtofloat(Period1DebtorBalance));
if GetSQLResult('select sum(PERIOD1BALANCE) from CUSTOMERAGINGHEADER') = null then
begin
Period2DebtorBalance := '0';
end
else
begin
Period2DebtorBalance := GetSQLResult('select sum(PERIOD1BALANCE) from CUSTOMERAGINGHEADER');
end;
TheEmailBody := TheEmailBody + #13 + ' * ' + Period2Caption + ' Debtor Balance: ' + FormatFloat('$###,###,##0.00',strtofloat(Period2DebtorBalance));
if GetSQLResult('select sum(PERIOD2BALANCE) from CUSTOMERAGINGHEADER') = null then
begin
Period3DebtorBalance := '0';
end
else
begin
Period3DebtorBalance := GetSQLResult('select sum(PERIOD2BALANCE) from CUSTOMERAGINGHEADER');
end;
TheEmailBody := TheEmailBody + #13 + ' * ' + Period3Caption + ' Debtor Balance: ' + FormatFloat('$###,###,##0.00',strtofloat(Period3DebtorBalance));
if GetSQLResult('select sum(PERIOD3BALANCE) from CUSTOMERAGINGHEADER') = null then
begin
Period4DebtorBalance := '0';
end
else
begin
Period4DebtorBalance := GetSQLResult('select sum(PERIOD3BALANCE) from CUSTOMERAGINGHEADER');
end;
TheEmailBody := TheEmailBody + #13 + ' * ' + Period4Caption + ' Debtor Balance: ' + FormatFloat('$###,###,##0.00',strtofloat(Period4DebtorBalance));
if GetSQLResult('select sum(PERIOD4BALANCE) from CUSTOMERAGINGHEADER') = null then
begin
Period5DebtorBalance := '0';
end
else
begin
Period5DebtorBalance := GetSQLResult('select sum(PERIOD4BALANCE) from CUSTOMERAGINGHEADER');
end;
TheEmailBody := TheEmailBody + #13 + ' * ' + Period5Caption + ' Debtor Balance: ' + FormatFloat('$###,###,##0.00',strtofloat(Period5DebtorBalance));
end;
'6': {Inventory and WIP Statistics}
begin
TheEmailBody := TheEmailBody + #13 + #13 + 'Inventory and WIP Statistics';
TheEmailBody := TheEmailBody + #13 + '===================================';
TheSQL := 'select sum(inventory.inventoryqty * itemmaster.averagecost * itemunits.conversionfactor)' +
' from WAREHOUSEMASTER, INVENTORY,ITEMMASTER,ITEMUNITS' +
' where INVENTORY.warehousecode = warehousemaster.warehousecode' +
' and Itemmaster.itemcode = inventory.itemcode and ' +
' itemunits.itemcode = itemmaster.itemcode and ' +
' itemunits.tounit = inventory.inventoryunit ';
if GetSQLResult(TheSQL) = null then
begin
CurrentStockValue := '0';
end
else
begin
CurrentStockValue := GetSQLResult(TheSQL);
end;
TheEmailBody := TheEmailBody + #13 + ' Current Stock Value: ' + FormatFloat('$###,###,##0.00',strtofloat(CurrentStockValue));
TheSQL := 'select sum(currentwipvalue)' +
' from JOBHEADER' +
' where orderstatus <> ''Closed''';
if GetSQLResult(TheSQL) = null then
begin
CurrentJobWIP := '0';
end
else
begin
CurrentJobWIP := GetSQLResult(TheSQL);
end;
TheEmailBody := TheEmailBody + #13 + ' Current Job WIP Value: ' + FormatFloat('$###,###,##0.00',strtofloat(CurrentJobWIP));
TheSQL := 'select sum(wipvalue)' +
' from ASSEMBLYHEADER' +
' where orderstatus <> ''Closed''';
if GetSQLResult(TheSQL) = null then
begin
CurrentAssemblyWIP := '0';
end
else
begin
CurrentAssemblyWIP := GetSQLResult(TheSQL);
end;
TheEmailBody := TheEmailBody + #13 + ' Current Assembly WIP Value: ' + FormatFloat('$###,###,##0.00',strtofloat(CurrentAssemblyWIP));
end;
end;
end;
{Footer Note}
TheEmailBody := TheEmailBody + #13 + #13 + 'Statistics were generated at: ' + datetimetostr(now) + ' for ' + datetostr(date-1);
if EmailTheResult = true then
begin
SendEmailMessage(TheEmailHostName,TheEmailSenderAddress,TheUserEmail,TheEmailSubject, TheEmailBody);
TheEmailBody := '';
end
else
begin
showmessage(TheUserEmail + ':' + TheEmailSubject + ':' + TheEmailBody);
TheEmailBody := '';
end;
end;
finally
EmailList.free;
end;
end;
function NoOfKPIS:integer;
var NumberOfKPIs: integer;
begin
TheOptionsLength := length(TheKPIOptions);
NumberOfKPIs := 0;
for y := 1 to TheOptionsLength do
begin
if (copy(TheKPIOptions,y,1)) = ',' then
begin
NumberOfKPIs := NumberOfKPIs + 1;
end;
end;
Result := NumberOfKPIs;
end;
begin
CreateKPIEmailsAndSend;
end.