* A mysql table called asalmas, with he Take5 Acounting structure.
* tested with php 5.3.18 and Sage 50 accounts 2013.
*/
require_once 'config.inc';
require_once 'funcs.inc';
// common functions
// configuration
$srcTable = 'SALES_LEDGER';
$srcKeyname = 'ACCOUNT_REF';
$destTable = 'asalmas';
$destKeyName = 'ACCOUNT';
// Connect to Sage and grab the source table data
$fields = array();
$results = array();
$debug = 0;
sage_getTable($srcTable, $debug, $fields, $results);
//print_r($fields); // to show the field structure
//print_r($results); // to print out the data per record
//print_r($results[0]); // print first row
// 1. Connect to he destination
$myconn = mysql_connect($mysql['host'], $mysql['user'], $mysql['pass']);
if (!$myconn) {
die("Error connecting to the MySQL database: " . $mysql_error());
}
if (!mysql_select_db($mysql['dbname'], $myconn)) {
die("Error selecting the database: " . $mysql_error());
}
// 2. Loop through the data and update the target
for ($i = 0; $i <= sizeof($results) - 1; $i++) {
if ($results[$i]['RECORD_DELETED'] > 0) {
// ignore these
$tables = array('sales_ledger' => 'ACCOUNT_REF', 'purchase_ledger' => 'ACCOUNT_REF', 'nominal_ledger' => 'ACCOUNT_REF', 'tax_code' => 'TAX_CODE_ID', 'CURRENCY' => 'NUMBER', 'COUNTRY_CODE' => 'CODE');
//print_r($tables);
// Step 2: loop though each table
if (!empty($tables)) {
foreach ($tables as $currentTable => $srcKeyname) {
echo date("H:i:s") . " Table {$currentTable} ";
fwrite($logf, date("H:i:s") . " Table {$currentTable} ");
if ($debug > 0) {
echo " with key {$srcKeyname} ";
}
$destKeyName = $srcKeyname;
$destTable = $currentTable;
$fields = array();
$results = array();
// Step 2a: Connect to Sage and grab the source table data
sage_getTable($currentTable, $debug, $fields, $results);
//print_r($fields); // to show the field structure
//print_r($results); // to print out the data per record
//print_r($results[0]); // print first row
// Step 2b: SQL to create the table, if it does not exist
$sql = "CREATE TABLE IF NOT EXISTS " . $currentTable . " (\n";
//$sql.= " " . $mysql['idfield'] . " BIGINT(20) NOT NULL auto_increment, ";
foreach ($fields as $key => $value) {
$sql .= " " . mysql_real_escape_string($key) . " ";
if ($value == "VARCHAR") {
$sql .= "VARCHAR(255)";
} elseif ($value == "INTEGER") {
$sql .= "BIGINT(20)";
} elseif ($value == "DATE") {
$sql .= "date";
} else {
