Modified the code to work with the transactions. Hopefully it'll work XD.
Code: Select all
<?php
include $_SERVER['DOCUMENT_ROOT'] . "/include.php";
head();
echo "<Body>
Processing Audit Trail.<br/>";
if ($_FILES['file']['type'] == "text/comma-separated-values")
{
//generate Tax desc - Tax Code and Tax Value
$TaxCode = array();
$TaxValue = array();
$result = mysql_query("SELECT * FROM `bookkeeping`.`TaxCodes`
WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
$rows = mysql_num_rows($result);
for($j=0;$j<$rows;$j++)
{
$row = mysql_fetch_array($result);
$TaxCode["T".$row['TCode']] = $row['Desc'];
}
//Generate Nominal Code lists
$Nominal = array();
$result = mysql_query("SELECT * FROM `bookkeeping`.`sageCodes`
WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
$rows = mysql_num_rows($result);
for($j=0;$j<$rows;$j++)
{
$row = mysql_fetch_array($result);
$Nominal[$row['Code']] = $row['Desc'];
}
//Generate Bank Accounts
$Banks = array();
$result = mysql_query("SELECT * FROM `bookkeeping`.`BankAccounts`
WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'");
$rows = mysql_num_rows($result);
for($j=0;$j<$rows;$j++)
{
$row = mysql_fetch_array($result);
$Banks[$row['SAGE']] = $row['ACC_NAME'];
}
$File = fopen($_FILES["file"]["tmp_name"], 'r');
$query = "";
$error = "";
$lines = 0;
$errors = 0;
$ignored = 0;
$processed = 0;
$query = mysql_query("BEGIN;");
while (!feof($File)) {
$Error = false;
$line = fgetcsv($File);
$lines++;
if(in_array($line[1],array("PI","BP","CP","SI","BR","CR")))
{
$Date = date("Y-m-d",ukstrtotime(($line[2])));
$Payee = mysql_real_escape_string($line[3]);
$Type = $Nominal[$line[4]];
$Dept = mysql_real_escape_string($line[5]);
$Ref = mysql_real_escape_string($line[6]);
$Desc = mysql_real_escape_string($line[7]);
$Amount = number_format(mysql_real_escape_string($line[8]),2,'.','');
$Vat = number_format(mysql_real_escape_string($line[9]),2,'.','');
$Tax = $TaxCode[$line[10]];
if($Payee == ""){
$error .= "Line: " . $lines . " Invalid Payee<br/>";
$Error = true;
}
if($Type == ""){
$error .= "Line: " . $lines . " Invalid Nominal Code<br/>";
$Error = true;
}
if($Amount == 0){
$error .= "Line: " . $lines . " Amount Cannot Be Zero<br/>";
$Error = true;
}
if($Tax == ""){
$error .= "Line: " . $lines . " Invalid Tax Code<br/>";
$Error = true;
}
if($Error){
$errors++;
continue;
}
if($line[1]=="PI")//Purchse Invoice
{
$supplier = mysql_num_rows(mysql_query("SELECT * FROM `bookkeeping`.`Suppliers`
WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'
AND `Ref` = '$Payee'"));
if($supplier>0){
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'PI','$Date','$Payee','$Ref','1')");
$pID = mysql_insert_id();
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
$processed++;
}
else
{
$error .= "Error on line " .$lines. ": Supplier ". $Payee." has not be referenced.<br>";
$errors++;
}
}
else if($line[1]=="BP")//Purchase Invoice WO
{
$Payee = $Banks[$Payee];
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'BP','$Date','$Desc','$Ref','1')");
$pID = mysql_insert_id();
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchasePaid` VALUES
('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
$processed++;
}
else if($line[1]=="CP")//Petty Cash
{
$Payee = $Banks[$Payee];
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseInvoices` VALUES
('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'CP','$Date','$Desc','$Ref','1')");
$pID = mysql_insert_id();
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchaseRecord` VALUES
('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
$query = mysql_query( "INSERT INTO `bookkeeping`.`PurchasePaid` VALUES
('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
$processed++;
}
if($line[1]=="SI")//Sales Invoice
{
$customer = mysql_num_rows(mysql_query("SELECT * FROM `bookkeeping`.`Customers`
WHERE `CCODE` = '" . $_SESSION['CCODE'] . "'
AND `Ref` = '$Payee'"));
if($customer>0){
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'SI','$Date','$Payee','$Ref','1')");
$pID = mysql_insert_id();
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
$processed++;
}
else
{
$error .= "Error on line " .$lines. ": Customer ". $Payee." has not be referenced.<br>";
$errors++;
}
}
else if($line[1]=="BR")//Sales Invoice WO
{
$Payee = $Banks[$Payee];
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'BR','$Date','$Desc','$Ref','1')");
$pID = mysql_insert_id();
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesPaid` VALUES
('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
$processed++;
}
else if($line[1]=="CR")//Cash Takings
{
$Payee = $Banks[$Payee];
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesInvoices` VALUES
('" . $_SESSION['CCODE']. "','$Dept','',CURDATE(),'CR','$Date','$Desc','$Ref','1')");
$pID = mysql_insert_id();
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesRecord` VALUES
('" . $_SESSION['CCODE']. "','$Dept','$pID','','$Type','$Desc','$Tax','$Amount','$Vat')");
$query = mysql_query( "INSERT INTO `bookkeeping`.`SalesPaid` VALUES
('".$_SESSION['CCODE']."','$Dept','$pID','','$Date','$Payee','','$Total','$Desc',0)");
$processed++;
}
}
else
$ignored++;
}
if($errors == 0)
{
echo "No Errors Found. Audit Trail Has Been Processed."
."<br>Lines: " . $lines
."<br>Processed: " . $processed
."<br>Ignored: " . $ignored;
$query = mysql_query("COMMIT;");
}else{
echo "<br>Lines: "
. $lines . "<br>Processed: "
. $processed . "<br>Ignored: "
. $ignored . "<br>Errors: "
. $errors . "<br>"
. $error;
$query = mysql_query("ROLLBACK;");
}
}
else
echo "Invalid file type. Please use";
?>
</body></html>
Tested: Didnt rollback and where was defently an error.