<![CDATA[FrontAccounting forum — Utility to clear the transaction tables while testing - here's how]]> 2010-01-26T16:40:22Z PunBB https://frontaccounting.com/punbb/viewtopic.php?id=426 <![CDATA[Re: Utility to clear the transaction tables while testing - here's how]]> Thanks P2409! Saved me a lot of time and effort using your script. Thought I would post an update to your work. I'm using version 2.2.4 so I've added a few tables to your list and corrected some errors (including the use of the wrong password variable in the connection function).

<?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,$pword); //<---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
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
trans_tax_details
supp_allocations
grn_batch
grn_items
audit_trail
voided
refs
comments

]]> https://frontaccounting.com/punbb/profile.php?id=622 2010-01-26T16:40:22Z https://frontaccounting.com/punbb/viewtopic.php?pid=4558#p4558 <![CDATA[Re: Utility to clear the transaction tables while testing - here's how]]> Hi p2409,

I tried using your script to clear up the transactions but I get an error:

"Parse error: parse error in E:\inetpub\path..to..script.php on line 79"

Line 79 is in the section with the table list.

We're using 2.1 beta and php 4

Just wondering if there's I can do to solve this because it would be a handy script.

Thanks!

]]> https://frontaccounting.com/punbb/profile.php?id=247 2009-03-10T02:14:21Z https://frontaccounting.com/punbb/viewtopic.php?pid=1938#p1938 <![CDATA[Re: Utility to clear the transaction tables while testing - here's how]]> Nice, thanks for sharing. For this purpose I've been restoring a clean database but this is a good idea as an alternative.

Kind regards.

]]> https://frontaccounting.com/punbb/profile.php?id=247 2008-12-31T04:06:11Z https://frontaccounting.com/punbb/viewtopic.php?pid=1595#p1595 <![CDATA[Utility to clear the transaction tables while testing - here's how]]> 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

]]> https://frontaccounting.com/punbb/profile.php?id=267 2008-12-17T06:36:08Z https://frontaccounting.com/punbb/viewtopic.php?pid=1508#p1508