function enter_data($_POST, $_FILES = "")
{
extract($_POST);
if ($_FILES != "") {
$importfile = tempnam("/tmp", "cubitimport_");
$file = fopen($_FILES["compfile"]["tmp_name"], "r");
if ($file == false) {
return "<li class='err'>Cannot read file.</li>" . select_file();
}
db_conn('cubit');
$Sl = "DROP TABLE import_data";
$Ri = @db_exec_safe($Sl);
$Sl = "\n\t\t\tCREATE TABLE import_data (\n\t\t\t\tid serial,\n\t\t\t\tdes1 varchar, des2 varchar,\n\t\t\t\tdes3 varchar, des4 varchar,\n\t\t\t\tdes5 varchar, des6 varchar,\n\t\t\t\tdes7 varchar, des8 varchar,\n\t\t\t\tdes9 varchar, des10 varchar,\n\t\t\t\tdes11 varchar, des12 varchar,\n\t\t\t\tdes13 varchar, des14 varchar,\n\t\t\t\tdes15 varchar, des16 varchar\n\t\t\t)";
$Ri = @db_exec($Sl);
$Sl = "DELETE FROM import_data";
$Ri = db_exec($Sl) or errDie("Unable to clear import table");
while (!feof($file)) {
$data = safe(fgets($file, 4096));
$datas = explode(",", $data);
if (!isset($datas[1])) {
continue;
}
$temp = explode('/', $datas['0']);
$valtemp = $temp['0'];
$datas['0'] = str_pad($valtemp, 4, "0") . "/" . $temp['1'];
$datas[2] = sprint(abs($datas[2]));
$datas[3] = sprint(abs($datas[3]));
$Sl = "\n\t\t\t\tINSERT INTO import_data (\n\t\t\t\t\tdes1, des2, des3, des4\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$datas['0']}', '{$datas['1']}', '{$datas['2']}', '{$datas['3']}'\n\t\t\t\t)";
$Rl = db_exec($Sl) or errDie("Unable to insert data.");
}
fclose($file);
}
global $_SESSION;
$out = "\n\t\t<h3>Trial Balance Import</h3>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='enter2'>\n\t\t\t<input type='hidden' name='login' value='1'>\n\t\t\t<input type='hidden' name='div' value='{$_SESSION['USER_DIV']}'>\n\t\t\t<input type='hidden' name='login_user' value='{$_SESSION['USER_NAME']}'>\n\t\t\t<input type='hidden' name='login_pass' value='{$_SESSION['USER_PASS']}'>\n\t\t\t<input type='hidden' name='code' value='{$_SESSION['code']}'>\n\t\t\t<input type='hidden' name='comp' value='{$_SESSION['comp']}'>\n\t\t\t<input type='hidden' name='noroute' value='1'>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' class='err'># Indicates an account (Number) that already exists</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' class='err'>! Indicates an account (Number) that is a duplicate of\n\t\t\t\t\tanother account number in the import file.</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' class='err'>In order to avoid errors, ensure that account numbers\n\t\t\t\t\tare in the correct format</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' class='err'>Remember to select the accounts to link to in the lists provided.\n\t\t\t\t\tIf the account you wish to link with does not exist, you can specify that the account\n\t\t\t\t\tshould be created. Do this by selecting one of the following options:<br />\n\t\t\t\t\t1. New Income Account, will create a new Income account<br />\n\t\t\t\t\t2. New Balance Account, will create and new Balance Sheet account<br />\n\t\t\t\t\t3. New Expense Account, will create a new Expense account.\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR . "\n\t\t\t<tr bgcolor='" . bgcolorc(1) . "'>\n\t\t\t\t<th>Period to import into:</th>\n\t\t\t\t<td>" . finMonList("prd", PRD_DB) . "</td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR . "\n\t\t\t<tr>\n\t\t\t\t<th>Acc No</th>\n\t\t\t\t<th>Account Name</th>\n\t\t\t\t<th>Debit</th>\n\t\t\t\t<th>Credit</th>\n\t\t\t\t<th>Select Account to link to</th>\n\t\t\t</tr>";
$accsql = new dbSelect("accounts", "core", array("order" => "accname"));
$accsql->run();
$acclist = array();
while ($ai = $accsql->fetch_array()) {
$acclist[$ai["accname"]] = $ai["accid"];
}
db_conn('cubit');
$Sl = "SELECT * FROM import_data ORDER BY des1";
$Ri = db_exec($Sl);
$i = 0;
$tot_debit = 0;
$tot_credit = 0;
db_conn('core');
$newacc_selections = array();
while ($fd = pg_fetch_array($Ri)) {
$fid = $fd['id'];
$bgcolor = bgcolor($i);
$Accounts = "\n\t\t<select name='accounts[{$fid}]' id='accounts[{$fid}]'>\n\t\t<optgroup label='Create New Account'>\n\t\t\t<option value='n1'>New Income Account</option>\n\t\t\t<option value='n2'>New Expense Account</option>\n\t\t\t<option value='n3'>New Balance Account</option>\n\t\t</optgroup>\n\t\t<optgroup label='Use Existing Account'>";
$has_sel = false;
foreach ($acclist as $accname => $accid) {
$sel = "";
if (!$has_sel) {
$m1 = isset($accounts[$fid]) && $accounts[$fid] == $accid;
$m2 = $accname == $fd['des2'] && !isset($accounts[$fid]);
$m3 = !empty($fd["des2"]) && stristr($accname, $fd['des2']) && !isset($accounts[$fid]) && !isset($acclist[$fd["des2"]]);
if ($m1 || $m2 || $m3) {
$sel = "selected";
$has_sel = true;
}
}
$Accounts .= "<option value='{$accid}' {$sel}>{$accname}</option>";
}
$Accounts .= "\n\t\t</optgroup>\n\t\t</select>";
list($var1, $var2) = explode('/', $fd['des1']);
if (!$has_sel) {
if ($var1 >= MIN_INC && $var1 <= MAX_INC) {
$newacc_selections[$fid] = "n1";
} else {
if ($var1 >= MIN_EXP && $var1 <= MAX_EXP) {
$newacc_selections[$fid] = "n2";
} else {
if ($var1 >= MIN_BAL && $var1 <= MAX_BAL) {
$newacc_selections[$fid] = "n3";
}
}
}
}
db_conn('core');
$get_check = "SELECT * FROM accounts WHERE topacc = '{$var1}' AND accnum = '{$var2}'";
$run_check = db_exec($get_check) or errDie("Unable to get account check");
if (pg_numrows($run_check) < 1) {
$showerr = "";
} else {
$showerr = "<b class='err'>#</b>";
}
$check_dup = new dbSelect("import_data", "cubit", grp(m("where", "des1='{$fd['des1']}' AND id!='{$fd['id']}'")));
$check_dup->run();
if ($check_dup->num_rows() > 0) {
$showerr .= "<b class='err'>!</b>";
}
$out .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$showerr} {$fd['des1']}</td>\n\t\t\t\t<td>{$fd['des2']}</td>\n\t\t\t\t<td>{$fd['des3']}</td>\n\t\t\t\t<td>{$fd['des4']}</td>\n\t\t\t\t<td>{$Accounts}</td>\n\t\t\t</tr>";
$tot_debit += $fd['des3'];
$tot_credit += $fd['des4'];
}
$bgcolor = bgcolor($i);
$tot_debit = sprint($tot_debit);
$tot_credit = sprint($tot_credit);
$out .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='2'>Total</td>\n\t\t\t<td align='right'>{$tot_debit}</td>\n\t\t\t<td align='right'>{$tot_credit}</td>\n\t\t</tr>";
if ($tot_debit == $tot_credit) {
$out .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='3' align='right'><input type='submit' value='Continue »'></td>\n\t\t\t</tr>";
} else {
$out .= "\n\t\t\t<li class='err'>The Total debit and credit are not the same. You cannot import a trial balance that doesnt balance.<br>\n\t\t\tPlease check the file you are trying to import, the total debits and total credits should be the same.<br>\n\t\t\tPlease fix the file and try again.</li>";
}
$out .= "\n\t\t</form>\n\t\t</table>\n\t\t<script>";
foreach ($newacc_selections as $fid => $pval) {
$out .= "l = document.getElementById('accounts[{$fid}]'); l.value='{$pval}';";
}
$out .= "\n\t</script>";
return $out;
}
function recreateAudit()
{
// Copy the audit DB
db_conn("cubit");
$sql = "ALTER SCHEMA audit RENAME TO \"" . YR_NAME . "_audit\"";
$Rslt = @db_exec_safe($sql);
// Create a new audit
$sql = "CREATE SCHEMA audit";
$rslt = @db_exec_safe($sql);
db_conn("audit");
// create the new audit database
if (!($fd = fopen("../dumping/sql/audit.sql", "r"))) {
errDie("Audit database template file not found. Please\n\t\t\tcontact Cubit to obtain this a file (TMPL_YR_CLOSE).");
}
$lcount = 0;
while (!feof($fd)) {
$line = "";
$pc = "";
// read all the characters into line until end of query
while (($c = fgetc($fd)) !== false) {
if ($c == "\n") {
// line is finished, blank or comment, break
if (strlen(trim($line)) < 1 || preg_match("/^--/", trim($line)) || $pc == ";") {
break;
}
}
$line .= $c;
if ($c != " " && $c != "\n") {
$pc = $c;
}
}
$line = trim($line);
++$lcount;
if (empty($line) || preg_match("/^--/", $line)) {
continue;
}
@db_exec_safe($line);
}
fclose($fd);
}
