[wiaflos-devel] COMMIT - r224 - in trunk: . soap/Plugins wiaflos/client/cmdline wiaflos/server
svn at linuxrulz.org
svn at linuxrulz.org
Sun Aug 10 17:20:42 GMT 2008
Author: nkukard
Date: 2008-08-10 17:20:42 +0000 (Sun, 10 Aug 2008)
New Revision: 224
Modified:
trunk/REQUIREMENTS
trunk/TODO
trunk/soap/Plugins/Clients.pm
trunk/wiaflos/client/cmdline/Clients.pm
trunk/wiaflos/server/Clients.pm
Log:
* Added support for client account transactions
* Updated requirements
* Updated TODO
Modified: trunk/REQUIREMENTS
===================================================================
--- trunk/REQUIREMENTS 2008-08-10 17:19:31 UTC (rev 223)
+++ trunk/REQUIREMENTS 2008-08-10 17:20:42 UTC (rev 224)
@@ -1,3 +1,6 @@
+perl-DBI
+perl-DBD-MySQL or perl-DBD-SQLite
+
perl-SOAP-Lite
perl-Net-Server
perl-Cache-FastMmap
Modified: trunk/TODO
===================================================================
--- trunk/TODO 2008-08-10 17:19:31 UTC (rev 223)
+++ trunk/TODO 2008-08-10 17:20:42 UTC (rev 224)
@@ -6,6 +6,10 @@
------------------------------------------------------------
+* Suppliers & Clients
+- Migrate from raw GL transactions to Supplier/Client specific transactions
+ ie. client_account_transactions & supplier_account_transactions
+
* Suppliers
- Don't allow deletion if transactions exist
Modified: trunk/soap/Plugins/Clients.pm
===================================================================
--- trunk/soap/Plugins/Clients.pm 2008-08-10 17:19:31 UTC (rev 223)
+++ trunk/soap/Plugins/Clients.pm 2008-08-10 17:20:42 UTC (rev 224)
@@ -52,6 +52,12 @@
Auth::aclAdd('Clients','removeClient','Clients/Remove');
+ Auth::aclAdd('Clients','createAccountTransaction','Clients/GL/Add');
+ Auth::aclAdd('Clients','getAccountTransactions','Clients/GL/List');
+ Auth::aclAdd('Clients','getAccountTransaction','Clients/GL/Get');
+ Auth::aclAdd('Clients','postAccountTransaction','Clients/GL/Post');
+ Auth::aclAdd('Clients','getAccountTransactionAllocations','Clients/GL/Get');
+
Auth::aclAdd('Clients','getClientGLAccountEntries','Clients/GL/Show');
}
@@ -460,7 +466,8 @@
# @li TransactionDate Transaction date
# @li Amount Amount
# @li TransactionReference Transaction reference
-sub getClientGLAccountEntries {
+sub getClientGLAccountEntries
+{
my (undef,$data) = @_;
@@ -468,7 +475,6 @@
return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Code' invalid");
}
-
my $authInfo = Auth::sessionGetData();
# Grab and sanitize data
@@ -501,9 +507,258 @@
}
+# @fn createAccountTransaction($data)
+# Create client GL account transaction
+#
+# @param data
+# @li Code - Client Code
+# @li Number - Transaction number, ie. TRN/xxyyzz
+# @li Reference - GL account entry reference
+# @li GLAccountNumber - GL account number to post this transaction to
+# @li Date -Date of transaction
+# @li Amount - Transaction amount
+sub createAccountTransaction
+{
+ my (undef,$data) = @_;
+ # Check params
+ if (!defined($data->{'Code'}) || $data->{'Code'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Code' invalid");
+ }
+ if (!defined($data->{'Number'}) || $data->{'Number'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Number' invalid");
+ }
+ if (!defined($data->{'Reference'}) || $data->{'Reference'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Reference' invalid");
+ }
+
+ if (!defined($data->{'GLAccountNumber'}) || $data->{'GLAccountNumber'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'GLAccountNumber' invalid");
+ }
+
+ if (!defined($data->{'Date'}) || $data->{'Date'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Date' invalid");
+ }
+
+ if (!defined($data->{'Amount'}) || $data->{'Amount'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Amount' invalid");
+ }
+
+ my $authInfo = Auth::sessionGetData();
+
+ # Do transaction
+ my $tmp;
+ $tmp->{'Code'} = $data->{'Code'};
+ $tmp->{'Number'} = $data->{'Number'};
+ $tmp->{'Reference'} = $data->{'Reference'};
+ $tmp->{'GLAccountNumber'} = $data->{'GLAccountNumber'};
+ $tmp->{'Date'} = $data->{'Date'};
+ $tmp->{'Amount'} = $data->{'Amount'};
+ my $res = wiaflos::server::Clients::createAccountTransaction($tmp);
+ if ($res < 1) {
+ return SOAPResponse(RES_ERROR,$res,wiaflos::server::Clients::Error());
+ }
+
+ return SOAPResponse(RES_OK,$res);
+}
+
+
+# @fn getAccountTransactions($data)
+# Return an array of account transactions
+#
+# @param data Hash with following elements
+# @li Code - Client code
+# @li Type - Optional type, 'open' or 'all'
+#
+# @return Array ref of hash refs, described below
+# @li ID - Transaction ID
+# @li ClientID - Client ID
+# @li Number - Transaction number
+# @li Reference - Transaction reference
+# @li GLAccountID - GL account ID
+# @li TransactionDate - Transaction date
+# @li Amount - Transaction amount
+# @li GLTransactionID - GL transaction ID
+# @li Posted - Posted, boolean
+# @li Closed - Closed, boolean
+sub getAccountTransactions
+{
+ my (undef,$data) = @_;
+
+
+ if (!defined($data->{'Code'}) || $data->{'Code'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Code' invalid");
+ }
+
+ my $authInfo = Auth::sessionGetData();
+
+ # Grab and sanitize data
+ my $detail;
+ $detail->{'Code'} = $data->{'Code'};
+ $detail->{'Type'} = $data->{'Type'};
+ my $rawData = wiaflos::server::Clients::getAccountTransactions($detail);
+ if (ref $rawData ne "ARRAY") {
+ return SOAPResponse(RES_ERROR,$rawData,wiaflos::server::Clients::Error());
+ }
+
+ # Build result
+ my @data;
+ foreach my $item (@{$rawData}) {
+ my $tmpItem;
+
+ $tmpItem->{'ID'} = $item->{'ID'};
+ $tmpItem->{'ClientID'} = $item->{'ClientID'};
+ $tmpItem->{'Number'} = $item->{'Number'};
+ $tmpItem->{'Reference'} = $item->{'Reference'};
+ $tmpItem->{'GLAccountID'} = $item->{'GLAccountID'};
+ $tmpItem->{'TransactionDate'} = $item->{'TransactionDate'};
+ $tmpItem->{'Amount'} = $item->{'Amount'};
+ $tmpItem->{'GLTransactionID'} = $item->{'GLTransactionID'};
+ $tmpItem->{'Posted'} = $item->{'Posted'};
+ $tmpItem->{'Closed'} = $item->{'Closed'};
+
+ push(@data,$tmpItem);
+ }
+
+ return SOAPResponse(RES_OK,\@data);
+}
+
+
+# @fn getAccountTransaction($data)
+# Retrun an account transaction
+#
+# @param data Hash ref with the below attributes
+# @li ID - Optional transaction ID
+# @li Number - Optional transaction number
+#
+# @return Hash refs, described below
+# @li ID - Transaction ID
+# @li ClientID - Client ID
+# @li Number - Transaction number
+# @li Reference - Transaction reference
+# @li GLAccountID - GL account ID
+# @li TransactionDate - Transaction date
+# @li Amount - Transaction amount
+# @li GLTransactionID - GL transaction ID
+# @li Posted - Posted, boolean
+# @li Closed - Closed, boolean
+sub getAccountTransaction
+{
+ my (undef,$data) = @_;
+ my $authInfo = Auth::sessionGetData();
+
+
+ # Check params
+ if (!defined($data->{'ID'}) && !defined($data->{'Number'})) {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'ID' or 'Number' must be provided");
+ }
+
+ my $detail;
+ $detail->{'ID'} = $data->{'ID'};
+ $detail->{'Number'} = $data->{'Number'};
+
+ # Grab and sanitize data
+ my $rawData = wiaflos::server::Clients::getAccountTransaction($detail);
+ if (ref $rawData ne "HASH") {
+ return SOAPResponse(RES_ERROR,$rawData,wiaflos::server::Clients::Error());
+ }
+
+ # Build result
+ my $tmpItem;
+
+ $tmpItem->{'ID'} = $rawData->{'ID'};
+ $tmpItem->{'ClientID'} = $rawData->{'ClientID'};
+ $tmpItem->{'Number'} = $rawData->{'Number'};
+ $tmpItem->{'Reference'} = $rawData->{'Reference'};
+ $tmpItem->{'GLAccountID'} = $rawData->{'GLAccountID'};
+ $tmpItem->{'TransactionDate'} = $rawData->{'TransactionDate'};
+ $tmpItem->{'Amount'} = $rawData->{'Amount'};
+ $tmpItem->{'GLTransactionID'} = $rawData->{'GLTransactionID'};
+ $tmpItem->{'Posted'} = $rawData->{'Posted'};
+ $tmpItem->{'Closed'} = $rawData->{'Closed'};
+
+ return SOAPResponse(RES_OK,$tmpItem);
+}
+
+
+# @fn postAccountTransaction($data)
+# Post an account transaction
+#
+# @param data Hash with the below elements...
+# @li Number - Transaction number
+#
+# @return 0 on success, -1 on error
+sub postAccountTransaction
+{
+ my (undef,$data) = @_;
+
+
+ # Check params
+ if (!defined($data->{'Number'}) || $data->{'Number'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Number' invalid");
+ }
+
+ # Do transaction
+ my $detail;
+ $detail->{'Number'} = $data->{'Number'};
+ my $res = wiaflos::server::Clients::postAccountTransaction($detail);
+ if ($res < 0) {
+ return SOAPResponse(RES_ERROR,$res,wiaflos::server::Clients::Error());
+ }
+
+ return SOAPResponse(RES_OK,$res);
+}
+
+
+# @fn getAccountTransactionAllocations($data)
+# Return an array of account transaction allocations
+#
+# @param data Hash with the below elements...
+# @li Number - Account transaction number
+#
+# @return Array ref of hash refs
+# @li ID - Account transaction ID
+# @li Amount - Allocation amount
+# @li CreditNoteID - Credit note ID to link to
+# @li ReceiptAllocationID - Receipt allocation ID to link to
+sub getAccountTransactionAllocations
+{
+ my (undef,$data) = @_;
+
+
+ if (!defined($data->{'Number'}) || $data->{'Number'} eq "") {
+ return SOAPResponse(RES_ERROR,ERR_S_PARAM,"Parameter 'Number' invalid");
+ }
+
+ my $authInfo = Auth::sessionGetData();
+
+ # Grab and sanitize data
+ my $tmp;
+ $tmp->{'Number'} = $data->{'Number'};
+ my $rawData = wiaflos::server::Clients::getAccountTransactionAllocations($tmp);
+ if (ref $rawData ne "ARRAY") {
+ return SOAPResponse(RES_ERROR,$rawData,wiaflos::server::Clients::Error());
+ }
+
+ # Build result
+ my @data = ();
+ foreach my $item (@{$rawData}) {
+ my $tmpItem;
+
+ $tmpItem->{'ID'} = $item->{'ID'};
+ $tmpItem->{'Amount'} = $item->{'Amount'};
+ $tmpItem->{'CreditNoteID'} = $item->{'CreditNoteID'};
+ $tmpItem->{'ReceiptAllocationID'} = $item->{'ReceiptAllocationID'};
+
+ push(@data,$tmpItem);
+ }
+
+ return SOAPResponse(RES_OK,\@data);
+}
+
+
1;
# vim: ts=4
Modified: trunk/wiaflos/client/cmdline/Clients.pm
===================================================================
--- trunk/wiaflos/client/cmdline/Clients.pm 2008-08-10 17:19:31 UTC (rev 223)
+++ trunk/wiaflos/client/cmdline/Clients.pm 2008-08-10 17:20:42 UTC (rev 224)
@@ -91,9 +91,37 @@
MenuItem => "ShowGL",
Regex => "showgl",
Desc => "Show GL account entries",
- Help => 'showGL client="<supplier code>" [start="<start date>"] [end="<end date>"] [balance-brought-forward="<y|n>"]',
+ Help => 'showGL client="<client code>" [start="<start date>"] [end="<end date>"] [balance-brought-forward="<y|n>"]',
Function => \&showGLAccountEntries,
},
+ {
+ MenuItem => "CreateTransaction",
+ Regex => "createtransaction",
+ Desc => "Create client GL account transaction",
+ Help => 'createTransaction client="<client code>" number="<transaction number>" reference="<transaction reference>" account="<GL account>" date="<transaction date>" amount="<amount>"',
+ Function => \&createTransaction,
+ },
+ {
+ MenuItem => "PostTransaction",
+ Regex => "postTransaction",
+ Desc => "Post client GL transaction",
+ Help => 'postTransaction transaction="<transaction number>"',
+ Function => \&postTransaction,
+ },
+ {
+ MenuItem => "ShowTransactions",
+ Regex => "showTransactions",
+ Desc => "Show client GL account transactions",
+ Help => 'showTransactions client="<client code>" [type="all"|"open"]',
+ Function => \&showTransactions,
+ },
+ {
+ MenuItem => "ShowTransactionAllocations",
+ Regex => "showTransactionAllocations",
+ Desc => "Show client GL account transaction allocations",
+ Help => 'showTransactionAllocations transaction="<transaction code>"',
+ Function => \&showTransactionAllocations,
+ },
],
},
],
@@ -441,9 +469,179 @@
}
+# @li Code - Client Code
+# @li Number - Transaction number, ie. TRN/xxyyzz
+# @li Reference - GL account entry reference
+# @li GLAccountNumber - GL account number to post this transaction to
+# @li Date -Date of transaction
+# @li Amount - Transaction amount
+# Create client
+sub createTransaction
+{
+ my ($OUT, at args) = @_;
+
+
+ my $parms = parseArgs(@args);
+ if (!defined($parms->{'client'})) {
+ print($OUT " => ERROR: Parameter 'client' not defined\n");
+ return ERR_C_PARAM;
+ }
+ if (!defined($parms->{'number'})) {
+ print($OUT " => ERROR: Parameter 'number' not defined\n");
+ return ERR_C_PARAM;
+ }
+ if (!defined($parms->{'reference'})) {
+ print($OUT " => ERROR: Parameter 'reference' not defined\n");
+ return ERR_C_PARAM;
+ }
+ if (!defined($parms->{'account'})) {
+ print($OUT " => ERROR: Parameter 'account' not defined\n");
+ return ERR_C_PARAM;
+ }
+
+ if (!defined($parms->{'date'})) {
+ print($OUT " => ERROR: Parameter 'date' not defined\n");
+ return ERR_C_PARAM;
+ }
+
+ if (!defined($parms->{'amount'})) {
+ print($OUT " => ERROR: Parameter 'amount' not defined\n");
+ return ERR_C_PARAM;
+ }
+
+
+ my $detail;
+ $detail->{'Code'} = $parms->{'client'};
+ $detail->{'Number'} = $parms->{'number'};
+ $detail->{'Reference'} = $parms->{'reference'};
+ $detail->{'GLAccountNumber'} = $parms->{'account'};
+ $detail->{'Date'} = $parms->{'date'};
+ $detail->{'Amount'} = $parms->{'amount'};
+ my $res = soapCall($OUT,"Clients","createAccountTransaction",$detail);
+ if ($res->{'Result'} != RES_OK) {
+ soapDebug($OUT,$res);
+ return $res->{'Result'};
+ }
+
+ return $res->{'Data'};
+}
+
+
+# Show acocunt transactions
+sub showTransactions
+{
+ my ($OUT, at args) = @_;
+
+
+ my $parms = parseArgs(@args);
+
+ if (!defined($parms->{'client'})) {
+ print($OUT " => ERROR: Parameter 'client' not defined\n");
+ return ERR_C_PARAM;
+ }
+ my $detail;
+ $detail->{'Code'} = $parms->{'client'};
+ $detail->{'Type'} = $parms->{'type'};
+ my $res = soapCall($OUT,"Clients","getAccountTransactions",$detail);
+
+ if ($res->{'Result'} == RES_OK) {
+ print $OUT swrite(<<'END', "ID", "Number", "Date", "Reference", "Amount","Posted","Closed");
++===========+===================+=============+=========================================+==============+========+========+
+| @|||||||| | @|||||||||||||||| | @|||||||||| | @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< | @>>>>>>>>>>> | @||||| | @||||| |
++===========+===================+=============+=========================================+==============+========+========+
+END
+ # Sort data
+ my @sorted = sort {$a->{'TransactionDate'} cmp $b->{'TransactionDate'}} @{$res->{'Data'}};
+
+ my $balance = Math::BigFloat->new(0);
+
+ foreach my $entry (@sorted) {
+ $balance->badd($entry->{'Amount'});
+ print $OUT swrite(<<'END', $entry->{'ID'}, $entry->{'Number'}, $entry->{'TransactionDate'}, $entry->{'Reference'}, sprintf('%8.2f',$entry->{'Amount'}),$entry->{'Posted'},$entry->{'Closed'});
+| @<<<<<<<< | @<<<<<<<<<<<<<<<< | @<<<<<<<<<< | @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< | @<<<<<<<<<<< | @||||| | @||||| |
+END
+ }
+ print $OUT swrite(<<'END');
++===========+===================+=============+=========================================+==============+========+========+
+END
+ } else {
+ soapDebug($OUT,$res);
+ }
+
+ return $res->{'Result'};
+}
+
+
+# Post transaction
+sub postTransaction
+{
+ my ($OUT, at args) = @_;
+
+
+ my $parms = parseArgs(@args);
+
+ if (!defined($parms->{'transaction'})) {
+ print($OUT " => ERROR: Parameter 'transaction' not defined\n");
+ return ERR_C_PARAM;
+ }
+
+ my $detail;
+ $detail->{'Number'} = $parms->{'transaction'};
+ my $res = soapCall($OUT,"Clients","postAccountTransaction",$detail);
+ if ($res->{'Result'} != RES_OK) {
+ soapDebug($OUT,$res);
+ }
+
+ return $res->{'Result'};
+}
+
+
+# Show transaction allocations
+sub showTransactionAllocations
+{
+ my ($OUT, at args) = @_;
+
+
+ my $parms = parseArgs(@args);
+
+ if (!defined($parms->{'transaction'})) {
+ print($OUT " => ERROR: Parameter 'client' not defined\n");
+ return ERR_C_PARAM;
+ }
+ my $detail;
+ $detail->{'Number'} = $parms->{'transaction'};
+ my $res = soapCall($OUT,"Clients","getAccountTransactionAllocations",$detail);
+
+ if ($res->{'Result'} == RES_OK) {
+ print $OUT swrite(<<'END', "ID", "Amount", "CreditNoteID", "ReceiptAllocationID");
++===========+===============+=============+============+
+| @|||||||| | @|||||||||||| | @|||||||||| | @||||||||| |
++===========+===============+=============+============+
+END
+ # Sort data
+ my $balance = Math::BigFloat->new(0);
+ foreach my $entry (@{$res->{'Data'}}) {
+ $balance->badd($entry->{'Amount'});
+ print $OUT swrite(<<'END', $entry->{'ID'}, sprintf('%.2f',$entry->{'Amount'}), "", $entry->{'ReceiptAllocationID'});
+| @|||||||| | @>>>>>>>>>>>> | @|||||||||| | @||||||||| |
++===========+===============+=============+============+
+END
+ }
+ } else {
+ soapDebug($OUT,$res);
+ }
+
+ return $res->{'Result'};
+}
+
+
+
+
+
+
1;
# vim: ts=4
Modified: trunk/wiaflos/server/Clients.pm
===================================================================
--- trunk/wiaflos/server/Clients.pm 2008-08-10 17:19:31 UTC (rev 223)
+++ trunk/wiaflos/server/Clients.pm 2008-08-10 17:20:42 UTC (rev 224)
@@ -157,7 +157,80 @@
}
+# Check if transaction number exists
+sub transactionNumberExists
+{
+ my $number = shift;
+
+ # Sanitize
+ $number = uc($number);
+ $number =~ s#^TRN/##;
+
+ # Select transaction count
+ my $rows = DBSelectNumResults("FROM client_account_transactions WHERE Number = ".DBQuote($number));
+ if (!defined($rows)) {
+ setError(wiaflos::server::dblayer::Error());
+ return ERR_DB;
+ }
+
+ return $rows > 0 ? 1 : 0;
+}
+
+
+# Return transaction ID from number
+sub getTransactionIDFromNumber
+{
+ my $number = shift;
+
+
+ # Sanitize
+ $number = uc($number);
+ $number =~ s#^TRN/##;
+
+ # Check cache
+ my ($cache_res,$cache) = cacheGetKeyPair('ClientAccountTransaction/Number-to-ID',$number);
+ if ($cache_res != RES_OK) {
+ setError(wiaflos::server::cache::Error());
+ return $cache_res;
+ }
+ return $cache if (defined($cache));
+
+ # Select transaction
+ my $sth = DBSelect("
+ SELECT
+ ID
+ FROM
+ client_account_transactions
+ WHERE
+ Number = ".DBQuote($number)."
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ return ERR_DB;
+ }
+
+ my $row = $sth->fetchrow_hashref();
+ DBFreeRes($sth);
+
+ # Check we got a result
+ if (!defined($row)) {
+ setError("Error finding transaction '$number'");
+ return ERR_NOTFOUND;
+ }
+
+ # Cache this
+ $cache_res = cacheStoreKeyPair('ClientAccountTransaction/Number-to-ID',$number,$row->{'ID'});
+ if ($cache_res != RES_OK) {
+ setError(wiaflos::server::cache::Error());
+ return $cache_res;
+ }
+
+ return $row->{'ID'};
+}
+
+
+
# Backend function to build item hash
sub sanitizeRawItem
{
@@ -180,6 +253,37 @@
}
+# Backend function to build item hash
+sub sanitizeRawAccountTransactionItem
+{
+ my $rawData = shift;
+
+
+ my $item;
+
+ $item->{'ID'} = $rawData->{'ID'};
+
+ $item->{'ClientID'} = $rawData->{'ClientID'};
+
+ $item->{'Number'} = "TRN/".uc($rawData->{'Number'});
+
+ $item->{'Reference'} = $rawData->{'Reference'};
+
+ $item->{'GLAccountID'} = $rawData->{'GLAccountID'};
+
+ $item->{'TransactionDate'} = $rawData->{'TransactionDate'};
+ $item->{'Amount'} = $rawData->{'Amount'};
+
+ $item->{'GLTransactionID'} = $rawData->{'GLTransactionID'};
+ $item->{'Posted'} = defined($rawData->{'GLTransactionID'}) ? 1 : 0;
+
+ $item->{'Closed'} = $rawData->{'Closed'};
+
+ return $item;
+}
+
+
+
# Return a hash containing the client
# Optional:
# ID - Client id
@@ -461,7 +565,7 @@
# Link in client address
my $sth = DBDo("
- INSERT INTO client_addrs
+ INSERT INTO client_addresses
(ClientID,Type,Address)
VALUES
(
@@ -476,7 +580,7 @@
}
# Grab last ID
- my $ID = DBLastInsertID("client_addrs","ID");
+ my $ID = DBLastInsertID("client_addresses","ID");
return $ID;
}
@@ -550,7 +654,7 @@
# Link in client address
my $sth = DBDo("
- INSERT INTO client_phnums
+ INSERT INTO client_phone_numbers
(ClientID,Type,Number$extraCols)
VALUES
(
@@ -566,7 +670,7 @@
}
# Grab last ID
- my $ID = DBLastInsertID("client_phnums","ID");
+ my $ID = DBLastInsertID("client_phone_numbers","ID");
return $ID;
}
@@ -620,7 +724,7 @@
# Link in client address
my $sth = DBDo("
- INSERT INTO client_email
+ INSERT INTO client_email_addresses
(ClientID,Type,Address)
VALUES
(
@@ -635,7 +739,7 @@
}
# Grab last ID
- my $ID = DBLastInsertID("client_email","ID");
+ my $ID = DBLastInsertID("client_email_addresses","ID");
return $ID;
}
@@ -675,11 +779,11 @@
# Return list of addresses
my $sth = DBSelect("
SELECT
- client_addrs.ID, client_addrs.ClientID, client_addrs.Type, client_addrs.Address
+ client_addresses.ID, client_addresses.ClientID, client_addresses.Type, client_addresses.Address
FROM
- client_addrs
+ client_addresses
WHERE
- client_addrs.ClientID = ".DBQuote($clientID)."
+ client_addresses.ClientID = ".DBQuote($clientID)."
");
if (!$sth) {
setError(wiaflos::server::dblayer::Error());
@@ -746,11 +850,11 @@
# Return list of addresses
my $sth = DBSelect("
SELECT
- client_email.ID, client_email.ClientID, client_email.Type, client_email.Address
+ client_email_addresses.ID, client_email_addresses.ClientID, client_email_addresses.Type, client_email_addresses.Address
FROM
- client_email
+ client_email_addresses
WHERE
- client_email.ClientID = ".DBQuote($clientID)."
+ client_email_addresses.ClientID = ".DBQuote($clientID)."
");
if (!$sth) {
setError(wiaflos::server::dblayer::Error());
@@ -817,11 +921,11 @@
# Return list of numbers
my $sth = DBSelect("
SELECT
- client_phnums.ID, client_phnums.ClientID, client_phnums.Type, client_phnums.Number
+ client_phone_numbers.ID, client_phone_numbers.ClientID, client_phone_numbers.Type, client_phone_numbers.Number
FROM
- client_phnums
+ client_phone_numbers
WHERE
- client_phnums.ClientID = ".DBQuote($clientID)."
+ client_phone_numbers.ClientID = ".DBQuote($clientID)."
");
if (!$sth) {
setError(wiaflos::server::dblayer::Error());
@@ -880,20 +984,27 @@
DBBegin();
# Remove client
- my $sth = DBDo("DELETE FROM client_addrs WHERE ClientID = ".DBQuote($client->{'ID'})."");
+ my $sth = DBDo("DELETE FROM client_addresses WHERE ClientID = ".DBQuote($client->{'ID'})."");
if (!$sth) {
setError(wiaflos::server::dblayer::Error());
DBRollback();
return ERR_DB;
}
- $sth = DBDo("DELETE FROM client_email WHERE ClientID = ".DBQuote($client->{'ID'})."");
+ $sth = DBDo("DELETE FROM client_phone_numbers WHERE ClientID = ".DBQuote($client->{'ID'})."");
if (!$sth) {
setError(wiaflos::server::dblayer::Error());
DBRollback();
return ERR_DB;
}
+ $sth = DBDo("DELETE FROM client_email_addresses WHERE ClientID = ".DBQuote($client->{'ID'})."");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ DBRollback();
+ return ERR_DB;
+ }
+
$sth = DBDo("DELETE FROM clients WHERE ID = ".DBQuote($client->{'ID'})."");
if (!$sth) {
setError(wiaflos::server::dblayer::Error());
@@ -955,7 +1066,529 @@
}
+# @fn createAccountTransaction($data)
+# Create client GL account transaction
+#
+# @param data
+# @li Code - Client Code
+# @li Number - Transaction number, ie. TRN/xxyyzz
+# @li Reference - GL account entry reference
+# @li GLAccountNumber - GL account number to post this transaction to
+# @li Date -Date of transaction
+# @li Amount - Transaction amount
+sub createAccountTransaction
+{
+ my ($data) = @_;
+ # Verify receipt number
+ if (!defined($data->{'Number'}) || $data->{'Number'} eq "") {
+ setError("No (or invalid) transaction number provided");
+ return ERR_PARAM;
+ }
+ (my $transactionNumber = uc($data->{'Number'})) =~ s#^TRN/##;
+
+ # Verify client code
+ if (!defined($data->{'Code'}) || $data->{'Code'} eq "") {
+ setError("No (or invalid) client code provided for client transaction");
+ return ERR_PARAM;
+ }
+
+ # Verify GL account
+ if (!defined($data->{'GLAccountNumber'}) || $data->{'GLAccountNumber'} eq "") {
+ setError("No (or invalid) GL account provided for client transaction");
+ return ERR_PARAM;
+ }
+
+ # Verify date
+ if (!defined($data->{'Date'}) || $data->{'Date'} eq "") {
+ setError("No (or invalid) date provided for client transaction");
+ return ERR_PARAM;
+ }
+
+ # Verify reference
+ if (!defined($data->{'Reference'}) || $data->{'Reference'} eq "") {
+ setError("No (or invalid) reference provided for client transaction");
+ return ERR_PARAM;
+ }
+
+ # Amount
+ if (!defined($data->{'Amount'}) || $data->{'Amount'} eq "") {
+ setError("No (or invalid) amount account provided for client transaction");
+ return ERR_PARAM;
+ }
+
+ # Check if client exists
+ my $clientID = wiaflos::server::Clients::getClientIDFromCode($data->{'Code'});
+ if ($clientID < 1) {
+ setError(wiaflos::server::Clients::Error());
+ return $clientID;
+ }
+
+ # Check GL account exists
+ my $GLAccountID = wiaflos::server::GL::getGLAccountIDFromNumber($data->{'GLAccountNumber'});
+ if ($GLAccountID < 1) {
+ setError(wiaflos::server::GL::Error());
+ return $GLAccountID;
+ }
+
+ # Check for conflicts
+ if (my $res = transactionNumberExists($transactionNumber)) {
+ setError("Transaction number '$transactionNumber' already exists");
+ return ERR_CONFLICT;
+ }
+
+ # Create transaction
+ my $sth = DBDo("
+ INSERT INTO client_account_transactions
+ (ClientID,Number,Reference,GLAccountID,TransactionDate,Amount,Closed)
+ VALUES
+ (
+ ".DBQuote($clientID).",
+ ".DBQuote($transactionNumber).",
+ ".DBQuote($data->{'Reference'}).",
+ ".DBQuote($GLAccountID).",
+ ".DBQuote($data->{'Date'}).",
+ ".DBQuote($data->{'Amount'}).",
+ 0
+ )
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ return ERR_DB;
+ }
+
+ # Grab last ID
+ my $ID = DBLastInsertID("client_account_transactions","ID");
+
+ return $ID;
+}
+
+
+# @fn getAccountTransactions($data)
+# Return an array of account transactions
+#
+# @param data Hash with following elements
+# @li Code - Client code
+# @li Type - Optional type, 'open' or 'all'
+#
+# @return Array ref of hash refs, @see sanitizeRawAccountTransactionItem
+sub getAccountTransactions
+{
+ my ($data) = @_;
+
+ # Verify client code
+ if (!defined($data->{'Code'}) || $data->{'Code'} eq "") {
+ setError("No (or invalid) client code provided");
+ return ERR_PARAM;
+ }
+
+ my $type = defined($data->{'Type'}) ? $data->{'Type'} : "open";
+
+ # Check if client exists & pull
+ my $tmp;
+ $tmp->{'Code'} = $data->{'Code'};
+ my $client = getClient($tmp);
+ if (ref $client ne "HASH") {
+ setError(Error());
+ return $client;
+ }
+
+ # Return list of transactions
+ my $sth = DBSelect("
+ SELECT
+ ID, ClientID,
+ Number, Reference, GLAccountID, TransactionDate, Amount,
+ GLTransactionID,
+ Closed
+ FROM
+ client_account_transactions
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ return ERR_DB;
+ }
+
+ # Fetch rows
+ my @transactions = ();
+ while (my $row = $sth->fetchrow_hashref()) {
+ # Check what kind of transactions do we want
+ if (($type eq "open") && $row->{'Closed'} eq "0") {
+ push(@transactions,sanitizeRawAccountTransactionItem($row));
+ } elsif ($type eq "all") {
+ push(@transactions,sanitizeRawAccountTransactionItem($row));
+ }
+ }
+
+ DBFreeRes($sth);
+
+ return \@transactions;
+}
+
+
+
+# @fn getAccountTransaction($data)
+# Return an array of account transactions
+#
+# @param data Hash with following elements
+# @li ID - Transaction ID
+# @li Number - Transaction number
+#
+# @return Hash ref, @see sanitizeRawAccountTransactionItem
+sub getAccountTransaction
+{
+ my ($data) = @_;
+
+
+ my $transactionID;
+
+ # Check which 'mode' we operating in
+ if (!defined($data->{'ID'}) || $data->{'ID'} < 1) {
+ # Verify transaction number
+ if (!defined($data->{'Number'}) || $data->{'Number'} eq "") {
+ setError("No (or invalid) transaction number provided");
+ return ERR_PARAM;
+ }
+
+ # Check if transaction exists
+ if (($transactionID = getTransactionIDFromNumber($data->{'Number'})) < 1) {
+ setError(Error());
+ return $transactionID;
+ }
+ } else {
+ $transactionID = $data->{'ID'};
+ }
+
+ # Verify transaction ID
+ if (!$transactionID || $transactionID < 1) {
+ setError("No (or invalid) transaction number/id provided");
+ return ERR_PARAM;
+ }
+
+ # Return list of transactions
+ my $sth = DBSelect("
+ SELECT
+ ID, ClientID,
+ Number, Reference, GLAccountID, TransactionDate, Amount,
+ GLTransactionID,
+ Closed
+ FROM
+ client_account_transactions
+ WHERE
+ ID = ".DBQuote($transactionID)."
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ return ERR_DB;
+ }
+
+ # Fetch row
+ my $row = $sth->fetchrow_hashref();
+
+ DBFreeRes($sth);
+
+ return sanitizeRawAccountTransactionItem($row);
+}
+
+
+# @fn postAccountTransaction($data)
+# Post an account transaction
+#
+# @param data Hash with the below elements...
+# @li Number - Transaction number
+#
+# @return 0 on success, -1 on error
+sub postAccountTransaction
+{
+ my ($data) = @_;
+
+
+ my $tmp;
+
+ # Grab transaction
+ $tmp = undef;
+ $tmp->{'Number'} = $data->{'Number'};
+ my $transaction = getAccountTransaction($tmp);
+ if (ref $transaction ne "HASH") {
+ setError(Error());
+ return $transaction;
+ }
+
+ # Make sure transaction is not posted
+ if ($transaction->{'Posted'} eq "1") {
+ setError("Account transaction '".$transaction->{'ID'}."' already posted");
+ return ERR_POSTED;
+ }
+
+ # Pull in client
+ $tmp = undef;
+ $tmp->{'ID'} = $transaction->{'ClientID'};
+ my $client = wiaflos::server::Clients::getClient($tmp);
+ if (ref $client ne "HASH") {
+ setError(wiaflos::server::Clients::Error());
+ return $client;
+ }
+
+ DBBegin();
+
+ # Create transaction
+ $tmp = undef;
+ $tmp->{'Date'} = $transaction->{'TransactionDate'};
+ $tmp->{'Reference'} = sprintf("Transaction: %s (%s)",$transaction->{'Number'},$transaction->{'Reference'});
+ my $GLTransactionID = wiaflos::server::GL::createGLTransaction($tmp);
+ if ($GLTransactionID < 1) {
+ setError(wiaflos::server::GL::Error());
+ DBRollback();
+ return $GLTransactionID;
+ }
+
+ # Pull in amount
+ my $transValue = Math::BigFloat->new($transaction->{'Amount'});
+
+ # Link from GL
+ $tmp = undef;
+ $tmp->{'ID'} = $GLTransactionID;
+ $tmp->{'GLAccountID'} = $transaction->{'GLAccountID'};
+ $tmp->{'Amount'} = $transValue->bstr();
+ if ((my $res = wiaflos::server::GL::linkGLTransaction($tmp)) < 1) {
+ setError(wiaflos::server::GL::Error());
+ DBRollback();
+ return $res;
+ }
+
+ # Negate for other side
+ $transValue->bmul(-1);
+
+ # Link to client GL account
+ $tmp = undef;
+ $tmp->{'ID'} = $GLTransactionID;
+ $tmp->{'GLAccountID'} = $client->{'GLAccountID'};
+ $tmp->{'Amount'} = $transValue->bstr();
+ if ((my $res = wiaflos::server::GL::linkGLTransaction($tmp)) < 1) {
+ setError(wiaflos::server::GL::Error());
+ DBRollback();
+ return $res;
+ }
+
+ # Post transaction
+ my $sth = DBDo("
+ UPDATE
+ client_account_transactions
+ SET
+ GLTransactionID = ".DBQuote($GLTransactionID)."
+ WHERE
+ ID = ".DBQuote($transaction->{'ID'})."
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ DBRollback();
+ return ERR_DB;
+ }
+
+ # Post transaction
+ $tmp = undef;
+ $tmp->{'ID'} = $GLTransactionID;
+ if ((my $res = wiaflos::server::GL::postGLTransaction($tmp)) != 0) {
+ setError(wiaflos::server::GL::Error());
+ DBRollback();
+ return $res;
+ }
+
+ DBCommit();
+
+ return RES_OK;
+}
+
+
+# @fn linkAccountTransactionAllocation($data)
+# Link an allocation to the account transaction
+#
+# @param data Hash with the below elements...
+# @li ID - Account transaction ID
+# @li Number - Account transaction number
+# @li Amount - Allocation amount
+# @li CreditNoteID - Credit note ID to link to
+# @li ReceiptAllocationID - Receipt allocation ID to link to
+sub linkAccountTransactionAllocation
+{
+ my ($data) = @_;
+
+
+ my @extraCols = ();
+ my @extraData = ();
+
+
+ # Grab transaction
+ my $tmp;
+ $tmp->{'ID'} = $data->{'ID'};
+ $tmp->{'Number'} = $data->{'Number'};
+ my $transaction = getAccountTransaction($tmp);
+ if (ref $transaction ne "HASH") {
+ setError(Error());
+ return $transaction;
+ }
+
+ # Grab transactions
+ $tmp = undef;
+ $tmp->{'ID'} = $transaction->{'ID'};
+ my $transactions = getAccountTransactionAllocations($tmp);
+ if (ref $transactions ne "ARRAY") {
+ setError(Error());
+ return $transactions;
+ }
+
+ # Check the transaction total vs. the transaction transactions
+ my $transactionBalance = Math::BigFloat->new($transaction->{'Amount'});
+ foreach my $item (@{$transactions}) {
+ $transactionBalance->badd($item->{'Amount'});
+ }
+
+ # And the current transaction
+ $transactionBalance->badd($data->{'Amount'});
+
+
+ DBBegin();
+
+ # If transaction balances out, transaction is now closed
+ if ($transaction->{'Closed'} eq "0" && ($transactionBalance->is_zero() || $transactionBalance->is_neg())) {
+ my $sth = DBDo("
+ UPDATE
+ client_account_transactions
+ SET
+ Closed = 1
+ WHERE
+ ID = ".DBQuote($transaction->{'ID'})."
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ DBRollback();
+ return ERR_DB;
+ }
+ }
+
+ # Check if this is a credit note
+ if ($data->{'CreditNoteID'}) {
+ push(@extraCols,'CreditNoteID');
+ push(@extraData,DBQuote($data->{'CreditNoteID'}));
+
+ # Its a receipt allocation
+ } elsif ($data->{'ReceiptAllocationID'}) {
+ push(@extraCols,'ReceiptAllocationID');
+ push(@extraData,DBQuote($data->{'ReceiptAllocationID'}));
+ }
+
+ # Pull in extra tmp
+ my $extraCols = "";
+ my $extraData = "";
+ if (@extraCols > 0 && @extraData > 0) {
+ $extraCols .= ',' . join(',', at extraCols);
+ $extraData .= ',' . join(',', at extraData);
+ }
+
+ # Add transaction allocation
+ my $sth = DBDo("
+ INSERT INTO client_account_transaction_allocations
+ (AccountTransactionID,Amount$extraCols)
+ VALUES
+ (
+ ".DBQuote($transaction->{'ID'}).",
+ ".DBQuote($data->{'Amount'})."
+ $extraData
+ )
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ DBRollback();
+ return ERR_DB;
+ }
+
+ # Grab last ID
+ my $ID = DBLastInsertID("client_account_transaction_allocations","ID");
+
+ DBCommit();
+
+ return $ID;
+}
+
+
+# @fn getAccountTransactionAllocations($data)
+# Return an array of account transaction allocations
+#
+# @param data Hash with the below elements...
+# @li ID - Account transaction ID
+# @li Number - Account transaction number
+#
+# @return Array ref of hash refs
+# @li ID - Account transaction ID
+# @li Amount - Allocation amount
+# @li CreditNoteID - Credit note ID to link to
+# @li ReceiptAllocationID - Receipt allocation ID to link to
+sub getAccountTransactionAllocations
+{
+ my ($detail) = @_;
+
+
+ # Grab transaction
+ my $data;
+ $data->{'ID'} = $detail->{'ID'};
+ $data->{'Number'} = $detail->{'Number'};
+ my $transaction = getAccountTransaction($data);
+ if (ref $transaction ne "HASH") {
+ setError(Error());
+ return $transaction;
+ }
+
+ # Return list of account transaction allocations
+ my $sth = DBSelect("
+ SELECT
+ ID, Amount,
+
+ ReceiptAllocationID
+
+ FROM
+ client_account_transaction_allocations
+ WHERE
+ AccountTransactionID = ".DBQuote($transaction->{'ID'})."
+ ");
+ if (!$sth) {
+ setError(wiaflos::server::dblayer::Error());
+ return ERR_DB;
+ }
+
+ # Fetch rows
+ my @allocations = ();
+ while (my $row = $sth->fetchrow_hashref()) {
+ my $item;
+
+ $item->{'ID'} = $row->{'ID'};
+
+ $item->{'Amount'} = $row->{'Amount'};
+
+ # If its a receipt, pull in all the details
+ if (defined($row->{'ReceiptAllocationID'})) {
+ $item->{'ReceiptAllocationID'} = $row->{'ReceiptAllocationID'};
+
+ # If its a credit note pull in all the details for that
+ } elsif (defined($row->{'CreditNoteID'})) {
+ $item->{'CreditNoteID'} = $row->{'CreditNoteID'};
+ }
+
+
+ push(@allocations,$item);
+ }
+
+ DBFreeRes($sth);
+
+ return \@allocations;
+}
+
+
+
+
+
+
+
+
+
1;
# vim: ts=4
More information about the wiaflos-devel
mailing list