[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: API to insert an AR Transaction
- Subject: Re: API to insert an AR Transaction
- From: John Locke <..hidden..>
- Date: Wed, 09 Sep 2009 09:06:43 -0700
-------- Original Message --------
Subject: Re: [Ledger-smb-devel] API to insert an AR Transaction
From: Darren Wiebe <..hidden..>
To: Development discussion for LedgerSMB
<..hidden..>
Date: Tue 08 Sep 2009 07:34:18 PM PDT
> Do you have any sample code you'd like to share?
>
> Darren Wiebe
> ..hidden..
>
>
Well, ok...
This is pretty ugly, but it works. This is a PHP class I originally
wrote to post invoices and data to SQL-Ledger before the fork. It still
works with LSMB 1.2.18. Below the class I'll add an example of using it
beneath that...
Note that when you post a form, if you omit any fields that have data,
LSMB will delete data in those fields. To work around this, first get an
item with all of its data, then update that data, then post it back.
<?php
class data_sql_ledger extends fl_data {
/* array of object types this object can support */
var $source = 'customer';
var $valid_obj_types = array('account','invoice','customer','vendor');
var $phptype = 'pgsql';
/* key is account object property
value is corresponding SL column/key name
*/
//var $account_map;
var $invoice_map = array(
'id'=>'id',
'customer_id'=>'customer_id',
'number'=>'invnumber',
'total'=>'amount',
'paid'=>'paid',
'date'=>'transdate',
'date_due'=>'duedate',
'notes'=>'notes',
'fl_pending'=>'fl_pending'
);
var $where_invoice_sql;
var $orderby_invoice_sql = ' ORDER BY duedate DESC ';
// note: we must include all SQL-Ledger fields here, because
otherwise they'll get dropped on update
var $account_map = array(
//'customernumber'=>'key',
'name'=>'name',
'finance_email'=>'email',
'finance_name'=>'contact',
'notes'=>'notes',
'sl_enddate'=>'enddate',
'sl_startdate'=>'startdate',
'address'=>'address1',
'address2'=>'address2',
'city'=>'city',
'state'=>'state',
'zip'=>'zipcode',
'country'=>'country',
'phone'=>'phone',
'fax'=>'fax',
'cc'=>'cc',
'bcc'=>'bcc',
'credit_limit'=>'creditlimit',
'terms'=>'terms',
'discount'=>'discount',
'taxnumber'=>'taxnumber',
'sic_code'=>'sic_code',
'bic'=>'bic',
'iban'=>'iban',
'curr'=>'curr'
);
var $customer_map = array(
'customernumber'=>'customernumber',
'name'=>'name',
'finance_email'=>'email',
'finance_name'=>'contact',
'notes'=>'notes',
'sl_enddate'=>'enddate',
'sl_startdate'=>'startdate',
'address'=>'address1',
'address2'=>'address2',
'city'=>'city',
'state'=>'state',
'zip'=>'zipcode',
'country'=>'country',
'phone'=>'phone',
'fax'=>'fax',
'cc'=>'cc',
'bcc'=>'bcc',
'credit_limit'=>'creditlimit',
'terms'=>'terms',
'discount'=>'discount',
'taxnumber'=>'taxnumber',
'sic_code'=>'sic_code',
'bic'=>'bic',
'iban'=>'iban',
'curr'=>'curr'
);
var $vendor_map = array(
'vendornumber'=>'vendornumber',
'name'=>'name',
'finance_email'=>'email',
'finance_name'=>'contact',
'notes'=>'notes',
'sl_enddate'=>'enddate',
'sl_startdate'=>'startdate',
'address'=>'address1',
'address2'=>'address2',
'city'=>'city',
'state'=>'state',
'zip'=>'zipcode',
'country'=>'country',
'phone'=>'phone',
'fax'=>'fax',
'cc'=>'cc',
'bcc'=>'bcc',
'credit_limit'=>'creditlimit',
'terms'=>'terms',
'discount'=>'discount',
'taxnumber'=>'taxnumber',
'sic_code'=>'sic_code',
'bic'=>'bic',
'iban'=>'iban',
'curr'=>'curr'
);
/* additional where clause for all account queries -- replace values
in constructor */
var $where_account_sql ;
var $order_customer_sql = ' name ASC ';
var $order_vendor_sql = ' name ASC ';
var $order_account_sql = ' name ASC ';
var $name_field = 'name';
var $map_col = 'customer_id';
var $module = 'customer';
var $key_name = 'id';
/* constructor function, should set $source, instantiate a db handle
if apropriate */
function __construct ($args)
{
// need to set up $this->account_defaults for inserting via CLI,
// $this->tax_locations
global $registry;
$this->new_account_defaults =
$registry['sql_ledger']['new_account_defaults'];
$this->tax_locations =
$registry['sql_ledger']['new_account_tax_locations'];
parent::__construct($args);
// set up account query where clause and map
$this->account_map = $this->source == 'vendor' ? $this->vendor_map :
$this->customer_map;
$date = date('Y-m-d');
// set up customer sql for invoice lookup
$this->where_account_sql = $this->where_customer_sql = "
(startdate IS NULL OR startdate <= '$date')
AND (enddate IS NULL OR enddate > '$date') ";
if (preg_match('/^https?/',$this->url,$match)){
$this->api = 'http';
} else {
$this->api = 'cli';
}
}
/* update an item with $item_id
handles both adding and updating object
Return true on success, false on failure
Used for vendors/customers
*/
function save (&$map_id,&$object,$arr= null) {
// Right now, we're assuming the cli api, and going to write
directly to the command line interface.
// supports using CURL to interact with different server.
if ($map_id > 0){
$this->loadUpdate($object,$update,$arr);
// for LSMB/SL, we need to update all fields. Discard
previous update and get all fields with a value.
$update = $this->saveFields($object);
// $update is now assoc array of foreign column names =>
values, ready for DB::AutoExecute.
// Now find out if the record exists...
$idcol = $this->get_key_name($this->source);
$id = fl_db::map($map_id,$this);
// $db = $this->get_db();
$get_args = $this->new_account_defaults;
$get_args = array_merge($get_args, $update);
// LSMB requires these on all access. We pass in http auth
here, for now...
$get_args['action']='save';
$get_args['db']=$this->source;
$get_args['path']='bin';
$get_args['login'] = $object->_user->id;
$get_args['password'] = $_SERVER['PHP_AUTH_PW'];
if (empty($get_args['password'])){
throw new auriga_exception('You must log in on this
screen, no password in http',401);
}
if ($id){
$get_args[$idcol] = $id;
}
foreach ($this->tax_locations as $location=>$key) {
if (strcasecmp($get_args['state'],$location)===0) {
$get_args[$key] = "1";
}
}
// update and save are identical, except for retrieving the
account id.
$path = $this->url;
switch ($object->module_id) {
case 'account':
if ($this->api == 'cli') {
$command = implode_with_key($get_args);
$this->result = `cd $path; ./ct.pl "$command"`;
}else {
$this->result = $this->post($get_args,'/ct.pl');
}
// check for id in account object
if (empty($id)) {
$result = array();
$this->load(array('name'=>$get_args['name']),$result,$object->module_id);
$id = $result[$this->map_col];
}
return $id;
}
} else {
throw new auriga_exception( $object->module_id . " creating
new items not supported in SQL Ledger.",501);
}
}
/*
@param object $subscription -> object with these properties:
->foreignAccount
->lineitem
->itemprice
->qty
->charge
->type
->item
->expires
->period
->size
->account (name)
->SO
->department
->invoicenote
@return array of invoice details to record/post
*/
function getInvoiceData($subscription){
$data = array(
'action'=>'update',
//'db'=$this->source;
'path'=>'bin/mozilla',
'login' => $this->spec['webuser'],
'password' => $this->spec['webpw'],
'type'=>'invoice',
'terms'=>'10',
'customer'=>$subscription->account,
'customer_id'=>$subscription->foreignAccount,
'ordnumber'=>$subscription->SO,
'AR'=>'1200--Accounts Receivables',
'department'=>'Hosting--10979',//$subscription->department,
// TODO: Array of valid values? "Hosting--10979"
'forex'=>'0',
'currency'=>'USD',
'partnumber_1'=>$subscription->lineitem, // description_1
'qty_1'=>$subscription->qty,
'sellprice_1'=>$subscription->itemprice,
'notes'=>$subscription->item. ' - '. $subscription->invoicenote,
'transdate'=>date('m-d-Y'),
'callback'=>'is.pl?action=add&type=invoice',
'rowcount'=>'1'
);
// try without: ordnumber, employee, defaultcurrency, duedate,
transdate
$html = $this->post($data,'/is.pl');
$this->mergeResponse($html,$data);
//debug_msg($data);
return $data;
}
/*
@param array $post - data returned from above, modified with any action
@param object $subscription - object containing subscription
Posts an invoice, returns the response data...
*/
function postInvoice($post,$subscription){
$post['action']='post';
$html=$this->post($post,'/is.pl');
debug_msg($html);
$this->mergeResponse($html,$data);
//debug_msg($data);
//print_r($data);
return $data;
}
/* Find invoice
@param array $data Post data, containing transdate,
path/login/password, customer, customer_id at a minimum
@param object $subcription if necessary
*/
function findInvoice(&$data,$subscription){
$post = array(
'action'=>'continue',
'path'=>'bin/mozilla',
'login' => $this->spec['webuser'],
'password' => $this->spec['webpw'],
'nextsub'=>'transactions',
'summary'=>'1',
'l_transdate'=>'Y',
'l_name'=>'Y',
'open'=>'Y',
'l_invnumber'=>'Y',
'l_amount'=>'Y',
'l_paid'=>'Y',
'customer_id'=>$data['customer_id'],
'transdatefrom'=>$data['transdate'],
'transdateto'=>$data['transdate']
);
$html = $this->post($post,'/ar.pl');
$pattern = '%<a.href="is.pl.*?id=(\d*)[^>]*>(\d*)%sx';
preg_match_all($pattern,$html,$match);
if (count($match[0])==0){
debug_msg($html);
debug_msg($data);
throw new auriga_exception('No invoice found!',404);
}
$i = count($match[0])-1; // assume the last one is the one we're
looking for
$data['id'] = $match[1][$i];
$data['invnumber'] = $match[2][$i];
return array('id'=>$match[1][$i],'invnumber'=>$match[2][$i]);
}
/*
@param array $data Array containing id, login, password at a minimum
@param object $subscription
*/
function getInvoice(&$data,$subscription=null){
//
$post = array(
'login' => $this->spec['webuser'],
'password' => $this->spec['webpw'],
'path'=>'bin/mozilla',
'id'=>$data['id'],
'action'=>'edit'
);
$post = array_merge($data,$post);
$html = $this->post($post,'/is.pl');
$this->mergeResponse($html,$data);
if (empty($data['transdate'])){
$data['transdate']=$data['oldtransdate']; // bug workaround:
transdate is not quoted in response
}
}
function sendInvoice($data,$subscription = null){
// data should have all invoice form data...
$data['action'] = 'e_mail';
$html = $this->post($data,'/is.pl');
$this->mergeResponse($html,$result);
// now to do the actual send...
$result['action']='continue';
if ($subscription != null){
$result['message'] = $subscription->message;
}
if (!defined('LEDGER_NOMAIL')){
$html = $this->post($result,'/is.pl');
}
debug_msg($html);
}
function post($post,$script){
$ch = curl_init();
curl_setopt($ch,CURLOPT_RETURNTRANSFER,true);
curl_setopt($ch,CURLOPT_URL,$this->url.$script); // invoice URL
curl_setopt($ch,CURLOPT_POST,true);
$command = implode_with_key($post);
curl_setopt($ch,CURLOPT_POSTFIELDS,$command);
// do actual post...
$html = curl_exec($ch);
if(curl_errno($ch))
{
debug_msg($this->url.$script);
throw new auriga_exception('Curl error: ' . curl_error($ch));
}
return $html;
}
/* @param string $html -> raw html response, collect all input values
@param array $data -> existing array to populate with input values
*/
function mergeResponse($html,&$data){
$pattern =
'%<input[^>]*(name|value)="([^"]*)"[^>]*(name|value)="([^"]*)"[^>]*>%sx';
if (preg_match_all($pattern,$html,$match)){
for ($i=0;$i<count($match[0]);$i++){
// [1] - name
// [2] - value
if (!empty($match[4][$i])){
$data[$match[2][$i]]=$match[4][$i];
}
}
//debug_msg($match);
} else {
throw new auriga_exception('No form details returned from
Ledger. Bad request?',404);
}
// now textarea
$txtpattern =
'%<textarea[^>]*name="([^"]*)"[^>]*>(.*?)</textarea>%sx';
preg_match_all($txtpattern,$html,$match);
for ($i=0;$i<count($match[0]);$i++){
// [1] - name
// [2] - value
if (!empty($match[2][$i])){
$data[$match[1][$i]]=$match[2][$i];
}
}
//debug_msg($match);
// finally, selects
$selectpattern='%<select.*?name="(.*?)".*?(</select>|<option[^>]*value="([^"]*)"[^>]*selected[^>]*>)%sx';
$match = array();
preg_match_all($selectpattern,$html,$match);
for ($i=0;$i<count($match[0]);$i++){
// [1] - name
// [2] - value
if (!empty($match[3][$i])){
$data[$match[1][$i]]=$match[3][$i];
}
}
//debug_msg($match);
}
}
?>
... so that's the class I put together for retrieving/adding invoices,
vendors and customers. You'll need to adapt this to your own code, since
it depends on other classes in my system. The key sequence to use this:
1. getInvoiceData, with data that maps to LSMB fields - this is like
going to the AR -> Create Invoice screen.
2. postInvoice, with data scraped out of getInvoiceData - this is like
pressing Post on the invoice
3. findInvoice -> searches for the invoice just created, gets the
invoice key
4. getInvoice -> loads the actual data from LSMB
5. sendInvoice -> uses the LSMB email field to send to the client.
It should be relatively easy to adapt to create AR transactions instead
of invoices--you'll probably have a different script to call, and
account numbers instead of part numbers +qty.
Here's an example of using it that automatically creates an invoice and
sends it to the customer via e-mail if there are no errors:
$subscription = new subscription($user);
$subscription->loadResult($sub);
$subscription->loadResult($json->decode($sub['xml']));
if
(empty($subscription->lineitem)||empty($subscription->itemprice)){
$subscription->notify_unbilled();
} else {
$subscription->message="Thank you for your business! You
can pay online and view reports at
https://intranet.freelock.com/auriga/show_invoice.php. Enter your email
address in the box and click Create Account and the system will send you
a new password.";
$subscription->create_invoice($plugin);
if (is_array($subscription->invoicedata) &&
$subscription->invoicedata['id']>0){
$plugin->sendInvoice($subscription->invoicedata,$subscription);
$subscription->notify('sent');
$subscription->update(); // save new expires...
}
}
... in the above code, $plugin is an instance of the
data_sql_ledger_class, and the subscription class creates an object to
manipulate data as desired. The key method here is create_invoice:
/* generate a single invoice from a subscription
... needs to interact with ledger module to populate data
*/
function create_invoice($plugin){
// get foreign account id
debug_msg('Creating invoice for subId: '.$this->id);
$foreign = fl_db::foreign($this->account_fk,$plugin,'account');
if (!$foreign){
throw new auriga_exception('Missing Finance foreign key!
SubId:'.$this->id,500);
}
debug_msg('native account:'.$this->account_fk.' foreign:'.$foreign);
$this->foreignAccount = $foreign;
// check for SO:
if ($this->SO){
$this->update_order($plugin);
}
// set $end to current expires, $expires to interval
$this->end = $this->expires;
$this->expires = $this->addInterval();
// merge data into invoicenote
$this->invoicenote = $this->parseNote();
// get department
// get line item data
// get AR account
// get any other totals
try {
$invoice = $plugin->getInvoiceData($this);
} catch (Exception $e){
$this->notify_unbilled($e);
return;
}
// post
$return = $plugin->postInvoice($invoice,$this);
// attach foreign invoice number to $this
$return = $plugin->findInvoice($invoice,$subscription);
$plugin->getInvoice($invoice,$subscription);
debug_msg($invoice);
$this->invoicedata = $invoice;
$this->notify('posted');
}
Cheers,
--
John Locke
http://freelock.com