Hi Guys
I wrote this little PHP script to delete transaction data (only) from my company while I setup FrontAccounting. It leaves you customers, chart of accounts intact, but does remove all transactional info. I've tried to pick the most relevant table, if you know of others that should be cleared, post them here and I'll update. If you want to do this yourself, just add them to the end of the script file.
WARNING WARNING WARNING!!!
Know what you are doing before you run this script. It will permanently delete transaction data!!!! If you don't know what this means, or what it's for DON'T run it.
Here's the PHP file - save it with an ending .php and run from command line.
// START CODE
<?php
// Clear out all frontaccounting transactions
// Leaves customers/bank accounts alone, but removes all transaction items
// BE CAREFUL YOU WILL LOSE YOUR TRANSACTION DATA IF YOU RUN THIS SCRIPT
// BACKUP BEFORE YOU RUN IT!!!
// IF YOU DON'T KNOW EXACTLY WHAT YOUR ARE DOING, DON'T RUN THIS SCRIPT
// ask for input
fwrite(STDOUT, "Enter your MySQL FrontAccounting database name: ");
// get input
$db = trim(fgets(STDIN));
fwrite(STDOUT, "Enter your Company Number eg. 1, 2 etc: ");
// get input
$company_number = trim(fgets(STDIN));
// ask for input
fwrite(STDOUT, "Enter your MySQL host (usually localhost): ");
// get input
$host = trim(fgets(STDIN));
fwrite(STDOUT, "Enter your MySQL user id: ");
// get input
$userid = trim(fgets(STDIN));
fwrite(STDOUT, "Enter your MySQL password: ");
// get input
$pword = trim(fgets(STDIN));
// Confirmation - must be Y in capitals, or I stop right here.
fwrite(STDOUT, "You are going to clear the FrontAccounting transactions for database : $db company number : $company_number\n" . "Are you absolutely sure you want to do this? (Y/N)");
$confirm = trim(fgets(STDIN));
if ($confirm!="Y") {
echo "OK...aborting\n";
exit();
}
$conn = mysql_connect($host,$userid,$password); //<---enter your host, user id and password for MySQL here
if ($conn==null) {
echo "Could not connect to MySQL with the host/username/password you provided. Try again.\n";
exit();
}
$tbllist = array();
// Here's the magic - read the end of this file into an array that contains the table names
// you want to clear.
$tbllist = split("\n",file_get_contents(__FILE__, NULL, NULL, __COMPILER_HALT_OFFSET__));
// Remove first entry - it's just a carriage return.
unset($tbllist[0]);
// Process each table clearing it.
foreach ($tbllist as $tbl) {
if (substr($tbl,0,1)!="#") run_delete_qry($tbl);
}
echo "Finished clearing transaction tables\n";
exit();
// A function to clear data from a table you specify
function run_delete_qry($tblname) {
global $db;
global $conn;
global $company_number;
$sql = "delete from " . $company_number . "_" . $tblname ;
$result = mysql_db_query($db,$sql);
if ($result!=1) {
echo "Warning: SQL statement " . $sql . " failed\n";
echo "with an error message of " . mysql_errno() . mysql_error(mysql_errno());
mysql_close($conn);
return;
}
echo "Cleared " . $company_number . "_" . $tblname . "\n";
}
__HALT_COMPILER();
# Tables you want to clear go here
# Comments start with the pound sign
gl_trans
bank_trans
debtor_trans
debtor_trans_details
debtor_trans_tax_details
purch_orders
purch_order_details
sales_orders
sales_order_details
wo_issues
wo_issue_items
wo_manufacture
wo_requirements
supp_invoice_items
supp_invoice_tax_items
supp_allocations
grn_batch
grn_items
.