Thanks, that'll get me started! -- Darren Wiebe Aleph Communications [email protected]
John Locke wrote: > -------- Original Message -------- > Subject: Re: [Ledger-smb-devel] API to insert an AR Transaction > From: Darren Wiebe <[email protected]> > To: Development discussion for LedgerSMB > <[email protected]> > Date: Tue 08 Sep 2009 07:34:18 PM PDT > >> Do you have any sample code you'd like to share? >> >> Darren Wiebe >> [email protected] >> >> >> > 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, > ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
