You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.
Is it possible to automate the Peachtree Accounting Export script so that the .csv files are created daily without the user logging in? I'd ideally like it to run at a particular time, so I could set our accounting software to import the data shortly afterwards. Is it possible to set this up using crontab, so no user intervention is required? If so, how?
Thank you!
Offline
Hi
You would need to add a php file to private dir /apps/gbu0/GBU/tasks_daily their should be a file in that directory that you can us as an example.
Offline
Has anybody looked into creating an automatic peachtree, quickbooks, or general csv export using a cron job? If it's been done, does anybody have any guidance with the code work? Thank you! Would this be as simple as adding the peachtree.php file from the ECOM_EXPORT to the tasks_daily folder or is it more involved than this?
Last edited by kreinbrink (01-04-2011 13:56:48)
Offline
Quite a bit more involved than that.
Offline
Are you looking to use this in v7? I've adapted a script to automatically export the peachtree export files from a server cron job to work in v7. It doesn't use the built-in cron system but instead we call it directly from our web server.
It works by creating a new namespace which allows us to call the export using a url.
It was some months ago but I can dig it out if it would be of use?!?
Simon
Offline
Yes we are currently using V7. If you could dig it out, that would be great! I'd love to see how it works. We are trying to simplify our order import process and that would be a huge step in the right direction.
Offline
OK, hopefully this should get you up and running...
Go to
Click and insert the following in SQL Statement:
INSERT INTO core_namespaces (id, app, namespace, name, pointer, active, interface, type, groupallow, hreftype, backendmenudisp, disprecback, becommon, demoallow) VALUES ("ecom.exportpeachtree", "ecom", "exportpeachtree", "Export Peachtree Data - Process", "ECOM::ECOM_peachtree::exec()", "1", "FrontEnd", "CGI", "*", "SSL", "0", "0", "0", "0")
In create a new file called
File contents:
<?php // +------------------------------------------------------------------+ // | Class Definition | // +------------------------------------------------------------------+ class ECOM_peachtree extends ECOM { var $class = 'ECOM_peachtree'; var $version = '7.0.0'; var $cerror; var $CORE_DB; var $CORE_File; var $CORE_Display; var $CORE_Form; // +------------------------------------------------------------------+ // | Constructor Function | // +------------------------------------------------------------------+ function ECOM_peachtree () { // +-- // | This is the class constructor function. // +-- if ($this->debug) {$this->debugger("constructor: Accessed.");} // +-- // | Quick object load: CORE_DB // +-- $this->CORE_DB =& $this->quick_object('CORE_DB','core','CORE_DB_1'); if ($this->IsError($this->CORE_DB)) {$this->cerror = $this->CORE_DB; return;} // +-- // | Quick object load: CORE_Display // +-- $this->CORE_Display =& $this->quick_object('CORE_Display','core'); if ($this->IsError($this->CORE_Display)) {$this->cerror = $this->CORE_Display; return;} // +-- // | Quick object load: CORE_File // +-- $this->CORE_File =& $this->quick_object('CORE_File','core'); if ($this->IsError($this->CORE_File)) {$this->cerror = $this->CORE_File; return;} // +-- // | Quick object load: CORE_Form // +-- $this->CORE_Form =& $this->quick_object('CORE_Form','core'); if ($this->IsError($this->CORE_Form)) {$this->cerror = $this->CORE_Form; return;} // +-- // | Return $this. // +-- return; } // End of function. function exec () { // +-- // | This function runs the export and redirects back to the original // | namespace (if the export was not done for some reason), or to // | the downloads page with a confirmation message. // +-- $file1 = 'peachtree-customers.csv'; $file2 = 'peachtree-items.csv'; $file3 = 'peachtree-orders.csv'; // +-- // | Set up our data needed for the export based on settings. // +-- $expsett = array('ptacctrec' => 1, 'ptincitem' => 1, 'ptincship' => 1, 'ptinctaxs' => 1, 'ptinctaxc' => 1, 'ptexpsale' => 1, 'ptexpdisc' => 1, 'ptpayment' => 1, 'ptincitemt' => 1, 'ptincshipt' => 1, 'ptinctaxst' => 1, 'ptinctaxct' => 1, 'ptexpsalet' => 1, 'ptexpdisct' => 1, 'ptpaymentt' => 1, 'ptincshipd' => 1, 'ptinctaxsd' => 1, 'ptinctaxcd' => 1, 'ptexpsaled' => 1, 'ptexpdiscd' => 1, 'ptpaymentd' => 1); foreach ($expsett as $key => $data) { $expsett[$key] = $this->globals('core_settings.ecom.' . $key); } // End of foreach statement. // +-- // | Figure out if we have any order that have not been exported which // | need to be exported. // +-- $table = 'ecom_orders'; $where = "status=" . $this->CORE_DB->quote('C') . " AND postordpt<>"; $where .= $this->CORE_DB->quote(1); $count = $this->CORE_DB->sql_count(array('table' => $table, 'where' => $where)); if (($this->IsError($count)) || (empty($count))) {$count = 0;} // +-- // | Return if we have no orders to export. // +-- if (empty($count)) { if ($this->debug) {$this->debugger("doform: No information to export. Returning.");} return $this->return_wmessage('expnodata'); } // End of if statement. // +-- // | If we're still here, let's grab the order information. // +-- $table = 'ecom_orders'; $columns = $this->CORE_DB->table_column_string($table); if ($this->IsError($columns)) {return $this->return_wmessage('experror');} $sql = "SELECT {$columns} FROM {$table} WHERE "; $sql .= "status=" . $this->CORE_DB->quote('C') . " AND postordpt<>"; $sql .= $this->CORE_DB->quote(1); $orders = $this->CORE_DB->sql_do(array('sql' => $sql, 'table' => $table, 'order' => array('id' => 'ASC'))); if ($this->IsError($orders)) {return $this->return_wmessage('experror');} if (empty($orders)) {return $this->return_wmessage('experror');} // +-- // | Now make a list of order ids we need to use to pull items from the // | orderitems table. // +-- $orders_string = ''; foreach ($orders as $num => $order) { $orders_string .= 'orderid=' . $this->CORE_DB->quote($order['id']) . ' OR '; } // End of foreach statement. if (preg_match('/ OR $/',$orders_string)) {$orders_string = rtrim($orders_string,' OR ');} if ($this->IsError($orders_string)) {return $this->return_wmessage('experror');} // +-- // | Pull data from the order items table for each order. // +-- $table = 'ecom_orderitems'; $columns = $this->CORE_DB->table_column_string($table); if ($this->IsError($columns)) {return $this->return_wmessage('experror');} $sql = "SELECT {$columns} FROM {$table} WHERE " . $orders_string; $orderitems = $this->CORE_DB->sql_do(array('sql' => $sql, 'table' => $table, 'order' => array('id' => 'ASC'))); if ($this->IsError($orderitems)) {return $this->return_wmessage('experror');} if (empty($orderitems)) {return $this->return_wmessage('experror');} // +-- // | Now combine the arrays. // +-- $ordcomb = array(); foreach ($orders as $num => $order) { $ordcomb[$order['id']]['order'] = $order; } // End of foreach statement. foreach ($orderitems as $num => $orderitems) { $ordcomb[$orderitems['orderid']]['items'][] = $orderitems; } // End of foreach statement. // +-- // | Define our end of line character. // +-- $eol = $this->globals('core.eol'); // +-- // | Begin creating the export files. // +-- $export_file1 = "\"CustID\",\"CustName\",\"Contact\",\"BAddress1\",\"BAddress2\",\"BCity\",\"BState\",\"BZipcode\","; $export_file1 .= "\"BCountry\",\"BTaxCode\",\"SName\",\"SAddress1\",\"SAddress2\",\"SCity\",\"SState\",\"SZipcode\","; $export_file1 .= "\"SCountry\",\"STaxCode\",\"CustType\",\"Phone1\",\"Phone2\",\"Fax\",\"Email\",\"Rep\",\"SalesAccount\","; $export_file1 .= "\"PriceLevel\",\"Terms\"" . $eol; $export_file2 = "\"Item ID\",\"Item Description\",\"Item Class\",\"Inactive\",\"Item Type\",\"G/L Sales Account\","; $export_file2 .= "\"Item Tax Type\"" . $eol; $export_file3 = "\"CustID\",\"CustName\",\"SalesOrderNo\",\"Date\",\"DropShip\",\"SName\",\"SAddress1\",\"SAddress2\","; $export_file3 .= "\"SCity\",\"SState\",\"SZipcode\",\"SCountry\",\"PONumber\",\"ShipVia\",\"DiscAmt\",\"Terms\",\"Rep\","; $export_file3 .= "\"ARAccount\",\"ARAmount\",\"SalesCode\",\"InvoiceNote\",\"PrintAfter\",\"DistLines\",\"Quantity\","; $export_file3 .= "\"Item\",\"Description\",\"ItemAccount\",\"Price\",\"TaxType\",\"LineAmount\",\"TaxAuthority\"" . $eol; // +-- // | Loop through the data and create the file. // +-- foreach ($ordcomb as $id => $xorder) { // +-- // | Create quick references. // +-- $order = $xorder['order']; $items = $xorder['items']; // +-- // | Quote everything for insertion. // +-- $keys = array_keys($order); foreach ($keys as $num => $key) {$order[$key] = $this->ptquote($order[$key]);} // +-- // | Quote everything for insertion. // +-- foreach ($items as $num => $item) { $keys = array_keys($item); foreach ($keys as $xnum => $xkey) {$items[$num][$xkey] = $this->ptquote($item[$xkey]);} } // End of foreach statement. // +-- // | Set our Dist lines count. We set to 1 initially because the order total // | counts as a line. // +-- $dist_lines = 1; // +-- // | Some formatted info we need. // +-- $order['datestamp'] = $order['datemonth'] . '/' . $order['dateday'] . '/' . $order['dateyear']; // +-- // | Do the customer information export. // +-- $export_file1 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['fname']} {$order['lname']}\","; $export_file1 .= "\"{$order['addone']}\",\"{$order['addtwo']}\",\"{$order['city']}\",\"{$order['stateprov']}\","; $export_file1 .= "\"{$order['postalcode']}\",\"{$order['country']}\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\","; $export_file1 .= "\"{$order['phone']}\",\"\",\"\",\"{$order['email']}\",\"\",\"{$expsett['ptincitem']}\",\"0\",\"FALSE\"" . $eol; // +-- // | Increment our $dist_lines variable for a number of things... // +-- foreach ($items as $num => $item) {$dist_lines++;} if ($order['shiptotal'] > 0) {$dist_lines++;} if ($order['taxsptotal'] > 0) {$dist_lines++;} if ($order['taxctotal'] > 0) {$dist_lines++;} if ($order['taxcreftotal'] > 0) {$dist_lines++;} if ($order['custsaletotal'] > 0) {$dist_lines++;} if ($order['custsurchargetotal'] > 0) {$dist_lines++;} if ($order['disctotal'] > 0) {$dist_lines++;} // +-- // | Now handle the order information export. // +-- $order['shipmethod'] = ''; foreach ($items as $num => $item) { $item_string = 'Item: ' . $item['itemnum'] . ' - ' . $item['itemname'] . '; Quantity: ' . $item['itemquan']; if (!(empty($item['itemopts']))) {$item_string .= '; ' . $item['itemopts'];} $priceper = $item['ordertotal'] / $item['itemquan']; $priceper = $this->price_round($priceper); $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$item['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"$dist_lines\",\"{$item['itemquan']}\",\"{$item['itemnum']}\",\"{$item_string}\",\"{$expsett['ptincitem']}\","; $export_file3 .= "\"{$priceper}\",\"{$expsett['ptincitemt']}\",\"-{$item['ordertotal']}\",\"\"" . $eol; if (empty($order['shipmethod'])) {$order['shipmethod'] = $item['shipmethod'];} } // End of foreach statement. // +-- // | Handle the state/province tax total. // +-- if ($order['taxsptotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptinctaxsd']}\",\"{$expsett['ptinctaxs']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptinctaxst']}\",\"-{$order['taxsptotal']}\",\"\"" . $eol; } // End of if statement. // +-- // | Handle the country tax total. // +-- if ($order['taxctotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptinctaxcd']}\",\"{$expsett['ptinctaxc']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptinctaxct']}\",\"-{$order['taxctotal']}\",\"\"" . $eol; } // End of if statement. // +-- // | Handle the country tax refund total. // +-- if ($order['taxcreftotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"VAT Refund\",\"23100\",\"0.00\","; $export_file3 .= "\"25\",\"{$order['taxcreftotal']}\",\"\"" . $eol; } // End of if statement. // +-- // | Handle the custom sale total. // +-- if ($order['custsaletotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptexpsaled']}\",\"{$expsett['ptexpsale']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptexpsalet']}\",\"{$order['custsaletotal']}\",\"\"" . $eol; } // End of if statement. // +-- // | Handle the custom surcharge total. // +-- if ($order['custsurchargetotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptincsurcd']}\",\"{$expsett['ptincsurc']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptincsurct']}\",\"-{$order['cutsurchargetotal']}\",\"\"" . $eol; } // End of if statement. // +-- // | Handle the discount total. // +-- if ($order['disctotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptexpdiscd']}\",\"{$expsett['ptexpdisc']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptexpdisct']}\",\"{$order['disctotal']}\",\"\"" . $eol; } // End of if statement. // +-- // | Handle the order total. // +-- $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptpaymentd']}\",\"{$expsett['ptpayment']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptpaymentt']}\",\"{$order['ordertotal']}\",\"\"" . $eol; // +-- // | Handle the shipping total. // +-- if ($order['shiptotal'] > 0) { $export_file3 .= "\"{$order['id']}\",\"{$order['fname']} {$order['lname']}\",\"{$order['id']}\",\"{$order['datestamp']}\","; $export_file3 .= "\"FALSE\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"{$order['id']}\",\"{$order['shipmethod']}\",\"0\","; $export_file3 .= "\"Prepaid\",\"\",\"{$expsett['ptacctrec']}\",\"{$order['ordertotal']}\",\"\",\"\",\"FALSE\","; $export_file3 .= "\"{$dist_lines}\",\"0\",\"\",\"{$expsett['ptincshipd']}\",\"{$expsett['ptincship']}\",\"0.00\","; $export_file3 .= "\"{$expsett['ptincshipt']}\",\"-{$order['shiptotal']}\",\"\"" . $eol; } // End of if statement. } // End of foreach statement. // +-- // | Lastly, handle the product information export. // +-- $table = 'ecom_prod'; $sql = "SELECT name, prodnum, xcat FROM {$table}"; $products = $this->CORE_DB->sql_do(array('sql' => $sql, 'table' => $table, 'order' => array('id' => 'ASC'))); if ((!($this->IsError($products))) && (!(empty($products)))) { foreach ($products as $num => $row) { $row['name'] = $this->ptquote($row['name']); $row['prodnum'] = $this->ptquote($row['prodnum']); $row['xcat'] = $this->ptquote($row['xcat']); $xcat = $this->make_list($row['xcat']); if (empty($xcat[0])) {$xcat[0] = 'Not Available';} $export_file2 .= "\"{$row['prodnum']}\",\"{$row['name']}\",\"0\",\"FALSE\",\"{$xcat[0]}\",\"{$expsett['ptincitem']}\",\"{$expsett['ptincitemt']}\"" . $eol; } // End of foreach statement. } // End of if statement. // +-- // | Write out export file 1 and handle errors. // +-- $filename = $this->globals('core.path_public') . '/downloads/' . $file1; $result = $this->CORE_File->write(array('name' => $filename, 'data' => $export_file1)); if ($this->IsError($result)) { if ($this->debug) {$this->debugger("doform: Unable to write export information to file. Returning.");} return $this->return_wmessage('expnodata'); } // End of if statement. // +-- // | Write out export file 2 and handle errors. // +-- $filename = $this->globals('core.path_public') . '/downloads/' . $file2; $result = $this->CORE_File->write(array('name' => $filename, 'data' => $export_file2)); if ($this->IsError($result)) { if ($this->debug) {$this->debugger("doform: Unable to write export information to file. Returning.");} return $this->return_wmessage('expnodata'); } // End of if statement. // +-- // | Write out export file 3 and handle errors. // +-- $filename = $this->globals('core.path_public') . '/downloads/' . $file3; $result = $this->CORE_File->write(array('name' => $filename, 'data' => $export_file3)); if ($this->IsError($result)) { if ($this->debug) {$this->debugger("doform: Unable to write export information to file. Returning.");} return $this->return_wmessage('expnodata'); } // End of if statement. // +-- // | Update all of our orders that we process so they're marked and // | we don't export them again. // +-- $table = 'ecom_orders'; $data = array('postordpt' => 1); $where = "status=" . $this->CORE_DB->quote('C'); $update_clause = $this->CORE_DB->clause_update(array('table' => $table, 'data' => $data)); if (!($this->IsError($update_clause))) { $sql = $update_clause . ' WHERE ' . $where; $this->CORE_DB->sql_do(array('sql' => $sql, 'table' => $table)); } // End of if statement. // +-- // | Return true. // +-- return 1; } // End of function. // +------------------------------------------------------------------+ // | Function: ptquote | // +------------------------------------------------------------------+ function ptquote ($string = '') { // +-- // | This function quotes a string for insertion into a Peachtree // | export file. // +-- return preg_replace('/\"/','',$string); } // End of function. // +------------------------------------------------------------------+ // | End of Class | // +------------------------------------------------------------------+ } // End of class. // +------------------------------------------------------------------+ // | End Of File | // +------------------------------------------------------------------+ ?>
You should now be able to go to and the peachtree export should run - putting the 3 files in the downloads directory.
We then run a cron job from our server to set this off at a specific time each day. Another php script then transfers it to an internal server where it is imported into our accounting system.
cron
curl http://www.yourdomain.com/index.php?app=ecom\&ns=exportpeachtree
Offline
Thanks a lot, this looks like exactly what we need! I am going to give it a whirl this weekend when I have some down time to test it out. I will report back on the details.
Offline