Example #1
0
function db($user, $password)
{
    $_SESSION['UserID'] = $user;
    $sql = "SELECT userid,\n\t\t\t\t\t\taccesslevel\n\t\t\t\tFROM www_users\n\t\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\t\tAND (password='******'\n\t\t\t\tOR  password='******')";
    $Auth_Result = DB_query($sql, $_SESSION['db']);
    $myrow = DB_fetch_row($Auth_Result);
    if (DB_num_rows($Auth_Result) > 0) {
        $sql = 'SELECT tokenid FROM securitygroups
					WHERE secroleid =  ' . $_SESSION['AccessLevel'];
        $Sec_Result = DB_query($sql, $db);
        $_SESSION['AllowedPageSecurityTokens'] = array();
        if (DB_num_rows($Sec_Result) == 0) {
            return NoAuthorisation;
        } else {
            $i = 0;
            while ($myrow = DB_fetch_row($Sec_Result)) {
                $_SESSION['AllowedPageSecurityTokens'][$i] = $myrow[0];
                $i++;
            }
        }
        return $_SESSION['db'];
    } else {
        return NoAuthorisation;
    }
}
 function studentsRegisteredForSubject($subject, $period, $stream, $db)
 {
     $sql = "SELECT COUNT(*) FROM registered_students WHERE subject_id='{$subject}' AND period_id='{$period}' AND class_id='{$stream}'";
     $result = DB_query($sql, $db);
     $myrow = DB_fetch_row($result);
     return $myrow[0];
 }
function VerifyAreaCodeDoesntExist($AreaCode, $i, $Errors, $db)
{
    $Searchsql = "SELECT COUNT(areacode)\n\t\t\t\t\t FROM areas\n\t\t\t\t\t  WHERE areacode='" . $AreaCode . "'";
    $SearchResult = DB_query($Searchsql);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] > 0) {
        $Errors[$i] = AreaCodeNotSetup;
    }
    return $Errors;
}
function VerifyTaxProvinceId($TaxProvinceId, $i, $Errors, $db)
{
    $Searchsql = "SELECT COUNT(taxprovinceid)\n\t\t\t\t\t\tFROM taxprovinces\n\t\t\t\t\t\tWHERE taxprovinceid='" . $TaxProvinceId . "'";
    $SearchResult = DB_query($Searchsql);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] == 0) {
        $Errors[$i] = TaxProvinceIdNotSetup;
    }
    return $Errors;
}
Example #5
0
function VerifyTaxProvinceId($TaxProvinceId, $i, $Errors, $db)
{
    $Searchsql = 'SELECT COUNT(taxprovinceid) FROM taxprovinces WHERE taxprovinceid="' . $TaxProvinceId . '"';
    $SearchResult = DB_query($Searchsql, $db);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] == 0) {
        $Errors[$i] = TaxProvinceIdNotSetup;
    }
    return $Errors;
}
Example #6
0
 function studentTakesSubject($student, $subject, $period, $db)
 {
     $studentTakesThisSubject = 0;
     $sql = "SELECT student_id FROM registered_students WHERE subject_id='{$subject}' AND period_id='{$period}' AND student_id='{$student}'";
     $result = DB_query($sql, $db);
     $num = DB_fetch_row($result);
     if ($num[0] > 0) {
         $studentTakesThisSubject = 1;
     }
     return $studentTakesThisSubject;
 }
Example #7
0
function DisplayBOMItems($UltimateParent, $Parent, $Component, $Level, $db)
{
    global $ParentMBflag;
    // Modified by POPAD&T
    $sql = "SELECT bom.component,\n\t\t\t\tstockmaster.description,\n\t\t\t\tlocations.locationname,\n\t\t\t\tworkcentres.description,\n\t\t\t\tbom.quantity,\n\t\t\t\tbom.effectiveafter,\n\t\t\t\tbom.effectiveto,\n\t\t\t\tstockmaster.mbflag,\n\t\t\t\tbom.autoissue,\n\t\t\t\tstockmaster.controlled,\n\t\t\t\tlocstock.quantity AS qoh,\n\t\t\t\tstockmaster.decimalplaces\n\t\t\tFROM bom,\n\t\t\t\tstockmaster,\n\t\t\t\tlocations,\n\t\t\t\tworkcentres,\n\t\t\t\tlocstock\n\t\t\tWHERE bom.component='" . $Component . "'\n\t\t\tAND bom.parent = '" . $Parent . "'\n\t\t\tAND bom.component=stockmaster.stockid\n\t\t\tAND bom.loccode = locations.loccode\n\t\t\tAND locstock.loccode=bom.loccode\n\t\t\tAND bom.component = locstock.stockid\n\t\t\tAND bom.workcentreadded=workcentres.code\n\t\t\tAND stockmaster.stockid=bom.component";
    $ErrMsg = _('Could not retrieve the BOM components because');
    $DbgMsg = _('The SQL used to retrieve the components was');
    $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
    //echo $TableHeader;
    $RowCounter = 0;
    while ($myrow = DB_fetch_row($result)) {
        $Level1 = str_repeat('- ', $Level - 1) . $Level;
        if ($myrow[7] == 'B' or $myrow[7] == 'K' or $myrow[7] == 'D') {
            $DrillText = '%s%s';
            $DrillLink = '<div class="centre">' . _('No lower levels') . '</div>';
            $DrillID = '';
        } else {
            $DrillText = '<a href="%s&Select=%s">' . _('Drill Down');
            $DrillLink = htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?';
            $DrillID = $myrow[0];
        }
        if ($ParentMBflag != 'M' and $ParentMBflag != 'G') {
            $AutoIssue = _('N/A');
        } elseif ($myrow[9] == 0 and $myrow[8] == 1) {
            //autoissue and not controlled
            $AutoIssue = _('Yes');
        } elseif ($myrow[9] == 0) {
            $AutoIssue = _('No');
        } else {
            $AutoIssue = _('N/A');
        }
        if ($myrow[7] == 'D' or $myrow[7] == 'K' or $myrow[7] == 'A' or $myrow[7] == 'G') {
            $QuantityOnHand = _('N/A');
        } else {
            $QuantityOnHand = number_format($myrow[10], $myrow[11]);
        }
        printf('<td>%s</td>
				<td>%s</td>
			    <td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td class="number">%s</td>
				<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td class="number">%s</td>
				<td><a href="%sSelect=%s&SelectedComponent=%s">' . _('Edit') . '</a></td>
				<td>' . $DrillText . '</a></td>
				 <td><a href="%sSelect=%s&SelectedComponent=%s&delete=1&ReSelect=%s">' . _('Delete') . '</a></td>
				 </tr>', $Level1, $myrow[0], $myrow[1], $myrow[2], $myrow[3], $myrow[4], ConvertSQLDate($myrow[5]), ConvertSQLDate($myrow[6]), $AutoIssue, $QuantityOnHand, htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?', $Parent, $myrow[0], $DrillLink, $DrillID, htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?', $Parent, $myrow[0], $UltimateParent);
    }
    //END WHILE LIST LOOP
}
Example #8
0
function VerifyAreaCodeDoesntExist($AreaCode, $i, $Errors, $db)
{
    $Searchsql = 'SELECT COUNT(areacode)
					 FROM areas
					  WHERE areacode="' . $AreaCode . '"';
    $SearchResult = DB_query($Searchsql, $db);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] > 0) {
        $Errors[$i] = AreaCodeNotSetup;
    }
    return $Errors;
}
Example #9
0
function backup_tables($host, $user, $pass, $tables = '*', $db)
{
    //get all of the tables
    if ($tables == '*') {
        $tables = array();
        $result = DB_query('SHOW TABLES', $db);
        while ($row = DB_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }
    //cycle through
    foreach ($tables as $table) {
        $result = DB_query('SELECT * FROM ' . $table, $db);
        $num_fields = DB_num_fields($result);
        $num_rows = DB_num_rows($result);
        $return .= 'DROP TABLE IF EXISTS ' . $table . ';';
        $row2 = DB_fetch_row(DB_query('SHOW CREATE TABLE ' . $table, $db));
        $return .= "\n\n" . $row2[1] . ";\n\n";
        $return .= 'INSERT INTO ' . $table . ' VALUES';
        for ($i = 0; $i < $num_fields; $i++) {
            $last = 0;
            while ($row = DB_fetch_row($result)) {
                $last = $last + 1;
                $return .= '(';
                for ($j = 0; $j < $num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n", "\\n", $row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j < $num_fields - 1 and isset($row[$j])) {
                        $return .= ',';
                    }
                }
                if ($last == $num_rows) {
                    $return .= ");\n";
                } else {
                    $return .= "),";
                }
            }
        }
        $return .= "\n\n\n";
    }
    //save file
    $handle = fopen('db-backup-' . time() . '-' . md5(implode(',', $tables)) . '.sql', 'w+');
    fwrite($handle, $return);
    fclose($handle);
    prnMsg(_(' back up successful'), 'success');
}
Example #10
0
function db($user, $password)
{
    $_SESSION['UserID'] = $user;
    $sql = "SELECT userid\n\t\t\tFROM www_users\n\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\tAND (password='******'\n\t\t\tOR  password='******')";
    $Auth_Result = DB_query($sql, $_SESSION['db']);
    $myrow = DB_fetch_row($Auth_Result);
    if (DB_num_rows($Auth_Result) > 0) {
        return $_SESSION['db'];
    } else {
        return NoAuthorisation;
    }
}
function GetExpiryDate($StockID, $LocCode, $BundleRef)
{
    global $db;
    $SQL = "SELECT expirationdate \n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid = '" . $StockID . "'\n\t\t\t\tAND loccode = '" . $LocCode . "'\n\t\t\t\tAND serialno = '" . $BundleRef . "'";
    $Result = DB_query($SQL);
    if (DB_num_rows($Result) == 0) {
        return '0000-00-00';
    } else {
        $myrow = DB_fetch_row($Result);
        return ConvertSQLDate($myrow[0]);
    }
}
Example #12
0
function ValidBundleRef($StockID, $LocCode, $BundleRef)
{
    global $db;
    $SQL = "SELECT quantity\n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid='" . $StockID . "'\n\t\t\t\tAND loccode ='" . $LocCode . "'\n\t\t\t\tAND serialno='" . $BundleRef . "'";
    $Result = DB_query($SQL, $db);
    if (DB_num_rows($Result) == 0) {
        return 0;
    } else {
        $myrow = DB_fetch_row($Result);
        return $myrow[0];
        /*The quantity in the bundle */
    }
}
Example #13
0
function CheckForRecursiveGroup($ParentGroupName, $GroupName, $db)
{
    $ErrMsg = _('An error occurred in retrieving the account groups of the parent account group during the check for recursion');
    $DbgMsg = _('The SQL that was used to retrieve the account groups of the parent account group and that failed in the process was');
    do {
        $sql = "SELECT parentgroupname FROM accountgroups WHERE groupname='" . $GroupName . "'";
        $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
        $myrow = DB_fetch_row($result);
        if ($ParentGroupName == $myrow[0]) {
            return true;
        }
        $GroupName = $myrow[0];
    } while ($myrow[0] != '');
    return false;
}
 function Offer($Supplier)
 {
     /*Constructor function initialises a new purchase offer object */
     global $db;
     $this->LineItems = array();
     $this->total = 0;
     $this->LinesOnOffer = 0;
     $this->SupplierID = $Supplier;
     $sql = "SELECT suppname,\n\t\t\t\t\temail,\n\t\t\t\t\tcurrcode\n\t\t\t\tFROM suppliers\n\t\t\t\tWHERE supplierid='" . $this->SupplierID . "'";
     $result = DB_query($sql, $db);
     $myrow = DB_fetch_row($result);
     $this->SupplierName = $myrow[0];
     $this->EmailAddress = $myrow[1];
     $this->CurrCode = $myrow[2];
 }
Example #15
0
function CheckForRecursiveGroup($ParentGroupName, $GroupName, $db)
{
    /* returns true ie 1 if the group contains the parent group as a child group
    ie the parent group results in a recursive group structure otherwise false ie 0 */
    $ErrMsg = _('An error occurred in retrieving the account groups of the parent account group during the check for recursion');
    $DbgMsg = _('The SQL that was used to retrieve the account groups of the parent account group and that failed in the process was');
    do {
        $sql = "SELECT parentgroupname\n\t\t\t\tFROM accountgroups\n\t\t\t\tWHERE groupname='" . $GroupName . "'";
        $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
        $myrow = DB_fetch_row($result);
        if ($ParentGroupName == $myrow[0]) {
            return true;
        }
        $GroupName = $myrow[0];
    } while ($myrow[0] != '');
    return false;
}
Example #16
0
function tep_get_subjects_marks($subject_id, $student_id, $class, $year, $term, $calendar_id, $db)
{
    $sql = "select COUNT(mp.exam_type_id) as no_of_cats from studentsmarks sm\n\tINNER JOIN markingperiods mp ON mp.id=sm.exam_mode\n\tINNER JOIN registered_students rs ON rs.id=sm.calendar_id\n\tWHERE sm.student_id='{$student_id}'\n\tAND rs.subject_id='{$subject_id}'\n\tAND rs.year='{$year}'\n\tAND rs.term='{$term}'";
    $result = DB_query($sql, $db);
    $row = DB_fetch_row($result);
    $num_of_exams_mode = $row[0];
    $sql = "select SUM(sm.marks) as cat_marks from studentsmarks sm\n\tINNER JOIN markingperiods mp ON mp.id=sm.exam_mode\n\tINNER JOIN registered_students rs ON rs.id=sm.calendar_id\n\tINNER JOIN subjects sub ON sub.id=rs.subject_id\n\tWHERE  sm.student_id='{$student_id}'\n\tAND rs.subject_id='{$subject_id}'\n\tAND rs.year='{$year}'\n\tAND rs.term='{$term}'";
    $result = DB_query($sql, $db);
    $row = DB_fetch_row($result);
    $marks = $row[0];
    if ($num_of_exams_mode > 0) {
        $average_marks = $marks / $num_of_exams_mode;
    } else {
        $average_marks = '';
    }
    return $average_marks;
}
    prnMsg(_('The selected period from is actually after the period to') . '. ' . _('Please re-select the reporting period'), 'error');
    unset($_POST['FromPeriod']);
    unset($_POST['ToPeriod']);
}
if (!isset($_POST['FromPeriod']) or !isset($_POST['ToPeriod'])) {
    /*Show a form to allow input of criteria for TB to show */
    echo '<table><tr><td>' . _('Select Period From') . ':</td><td><select name="FromPeriod">';
    $sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno";
    $Periods = DB_query($sql);
    while ($myrow = DB_fetch_array($Periods, $db)) {
        echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
    }
    echo '</select></td></tr>';
    $sql = "SELECT MAX(periodno) FROM periods";
    $MaxPrd = DB_query($sql);
    $MaxPrdrow = DB_fetch_row($MaxPrd);
    $DefaultToPeriod = (int) ($MaxPrdrow[0] - 1);
    echo '<tr><td>' . _('Select Period To') . ':</td><td><select name="ToPeriod">';
    $RetResult = DB_data_seek($Periods, 0);
    while ($myrow = DB_fetch_array($Periods, $db)) {
        if ($myrow['periodno'] == $DefaultToPeriod) {
            echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
        } else {
            echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
        }
    }
    echo '</select></td></tr></table>';
    echo '<div class="centre"><input type="submit" name="recalc" value="' . _('Do the Recalculation') . '" /></div>
        </div>
        </form>';
} else {
Example #18
0
function VerifyOrderHeaderExists($orderno, $i, $Errors, $db)
{
    $Searchsql = 'SELECT COUNT(orderno)
					 FROM salesorders
					  WHERE orderno="' . $orderno . '"';
    $SearchResult = api_DB_query($Searchsql, $db);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] == 0) {
        $Errors[$i] = OrderHeaderNotSetup;
    }
    return $Errors;
}
Example #19
0
    } else {
        $pdf->OutputD($_SESSION['DatabaseName'] . '_GL_Balance_Sheet_' . date('Y-m-d') . '.pdf');
        $pdf->__destruct();
    }
    exit;
} else {
    $ViewTopic = 'GeneralLedger';
    $BookMark = 'BalanceSheet';
    include 'includes/header.inc';
    echo '<form onSubmit="return VerifyForm(this);" method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
    echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
    echo '<input type="hidden" name="BalancePeriodEnd" value="' . $_POST['BalancePeriodEnd'] . '" />';
    $RetainedEarningsAct = $_SESSION['CompanyRecord']['retainedearnings'];
    $sql = "SELECT lastdate_in_period FROM periods WHERE periodno='" . $_POST['BalancePeriodEnd'] . "'";
    $PrdResult = DB_query($sql, $db);
    $myrow = DB_fetch_row($PrdResult);
    $BalanceDate = ConvertSQLDate($myrow[0]);
    /*Calculate B/Fwd retained earnings */
    $SQL = "SELECT Sum(CASE WHEN chartdetails.period='" . $_POST['BalancePeriodEnd'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS accumprofitbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . ($_POST['BalancePeriodEnd'] - 12) . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lyaccumprofitbfwd\n\t\tFROM chartmaster INNER JOIN accountgroups\n\t\tON chartmaster.group_ = accountgroups.groupname INNER JOIN chartdetails\n\t\tON chartmaster.accountcode= chartdetails.accountcode\n\t\tWHERE accountgroups.pandl=1";
    $AccumProfitResult = DB_query($SQL, $db, _('The accumulated profits brought forward could not be calculated by the SQL because'));
    $AccumProfitRow = DB_fetch_array($AccumProfitResult);
    /*should only be one row returned */
    $SQL = "SELECT accountgroups.sectioninaccounts,\n\t\t\taccountgroups.groupname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\tchartdetails.accountcode,\n\t\t\tchartmaster.accountname,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['BalancePeriodEnd'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS balancecfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . ($_POST['BalancePeriodEnd'] - 12) . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lybalancecfwd\n\t\tFROM chartmaster INNER JOIN accountgroups\n\t\tON chartmaster.group_ = accountgroups.groupname INNER JOIN chartdetails\n\t\tON chartmaster.accountcode= chartdetails.accountcode\n\t\tWHERE accountgroups.pandl=0\n\t\tGROUP BY accountgroups.groupname,\n\t\t\tchartdetails.accountcode,\n\t\t\tchartmaster.accountname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.sectioninaccounts\n\t\tORDER BY accountgroups.sectioninaccounts,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.groupname,\n\t\t\tchartdetails.accountcode";
    $AccountsResult = DB_query($SQL, $db, _('No general ledger accounts were returned by the SQL because'));
    echo '<p class="page_title_text noPrint" ><img src="' . $RootPath . '/css/' . $Theme . '/images/preview.gif" title="' . _('HTML View') . '" alt="' . _('HTML View') . '" /> ' . _('HTML View') . '</p>';
    echo '<div class="invoice">
			<table class="selection" summary="' . _('HTML View') . '">
			<tr>
				<th colspan="6">
					<h2>' . _('Balance Sheet as at') . ' ' . $BalanceDate . '
					<img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" class="PrintIcon noPrint" title="' . _('Print') . '" alt="' . _('Print') . '" onclick="window.print();" />
Example #20
0
    $_SESSION['SuppTrans']->ExRate = $myrow['exrate'];
    $_SESSION['SuppTrans']->TaxGroup = $myrow['taxgroupid'];
    $_SESSION['SuppTrans']->TaxGroupDescription = $myrow['taxgroupdescription'];
    if ($myrow['daysbeforedue'] == 0) {
        $_SESSION['SuppTrans']->Terms = '1' . $myrow['dayinfollowingmonth'];
    } else {
        $_SESSION['SuppTrans']->Terms = '0' . $myrow['daysbeforedue'];
    }
    $_SESSION['SuppTrans']->SupplierID = $_GET['SupplierID'];
    $LocalTaxProvinceResult = DB_query("SELECT taxprovinceid \n\t\t\t\t\t\tFROM locations \n\t\t\t\t\t\tWHERE loccode = '" . $_SESSION['UserStockLocation'] . "'", $db);
    if (DB_num_rows($LocalTaxProvinceResult) == 0) {
        prnMsg(_('The tax province associated with your user account has not been set up in this database. Tax calculations are based on the tax group of the supplier and the tax province of the user entering the invoice. The system administrator should redefine your account with a valid default stocking location and this location should refer to a valid tax provincce'), 'error');
        include 'includes/footer.inc';
        exit;
    }
    $LocalTaxProvinceRow = DB_fetch_row($LocalTaxProvinceResult);
    $_SESSION['SuppTrans']->LocalTaxProvince = $LocalTaxProvinceRow[0];
    $_SESSION['SuppTrans']->GetTaxes();
    $_SESSION['SuppTrans']->GLLink_Creditors = $_SESSION['CompanyRecord']['gllink_creditors'];
    $_SESSION['SuppTrans']->GRNAct = $_SESSION['CompanyRecord']['grnact'];
    $_SESSION['SuppTrans']->CreditorsAct = $_SESSION['CompanyRecord']['creditorsact'];
    $_SESSION['SuppTrans']->InvoiceOrCredit = 'Credit Note';
} elseif (!isset($_SESSION['SuppTrans'])) {
    prnMsg(_('To enter a supplier credit note the supplier must first be selected from the supplier selection screen'), 'warn');
    echo "<BR><A HREF='{$rootpath}/SelectSupplier.php?" . SID . "'>" . _('Select A Supplier to Enter an Credit Note For') . '</A>';
    exit;
    /*It all stops here if there aint no supplier selected */
}
/* Set the session variables to the posted data from the form if the page has called itself */
if (isset($_POST['ExRate'])) {
    $_SESSION['SuppTrans']->ExRate = $_POST['ExRate'];
 }
 // Find the quantity on purchase orders
 $sql = "SELECT SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd)*purchorderdetails.conversionfactor AS dem\r\n\t\t\t\t FROM purchorderdetails LEFT JOIN purchorders\r\n\t\t\t\t\tON purchorderdetails.orderno=purchorders.orderno\r\n\t\t\t\t WHERE purchorderdetails.completed=0\r\n\t\t\t\t AND purchorders.status<>'Cancelled'\r\n\t\t\t\t AND purchorders.status<>'Rejected'\r\n\t\t\t\t AND purchorders.status<>'Completed'\r\n\t\t\t\tAND purchorderdetails.itemcode='" . $myrow['stockid'] . "'";
 $ErrMsg = _('The order details for this product cannot be retrieved because');
 $PurchResult = DB_query($sql, $db, $ErrMsg);
 $PurchRow = DB_fetch_row($PurchResult);
 if ($PurchRow[0] != null) {
     $PurchQty = $PurchRow[0];
 } else {
     $PurchQty = 0;
 }
 // Find the quantity on works orders
 $sql = "SELECT SUM(woitems.qtyreqd - woitems.qtyrecd) AS dedm\r\n\t\t\t   FROM woitems\r\n\t\t\t   WHERE stockid='" . $myrow['stockid'] . "'";
 $ErrMsg = _('The order details for this product cannot be retrieved because');
 $WoResult = DB_query($sql, $db, $ErrMsg);
 $WoRow = DB_fetch_row($WoResult);
 if ($WoRow[0] != null) {
     $WoQty = $WoRow[0];
 } else {
     $WoQty = 0;
 }
 if ($k == 1) {
     echo '<tr class="EvenTableRows">';
     $k = 0;
 } else {
     echo '<tr class="OddTableRows">';
     $k = 1;
 }
 $OnOrder = $PurchQty + $WoQty;
 $Available = $QOH - $DemandQty + $OnOrder;
 echo '<td>' . $myrow['stockid'] . '</td>
     $InputError = 1;
     prnMsg(_('The date this price is be in effect to must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
     if (Date1GreaterThanDate2($_POST['StartDate'], $_POST['EndDate'])) {
         $InputError = 1;
         prnMsg(_('The end date is expected to be after the start date, enter an end date after the start date for this price'), 'error');
     }
 }
 if (Is_Date($_POST['EndDate'])) {
     $SQLEndDate = FormatDateForSQL($_POST['EndDate']);
 }
 if (Is_Date($_POST['StartDate'])) {
     $SQLStartDate = FormatDateForSQL($_POST['StartDate']);
 }
 $sql = "SELECT COUNT(salestype)\r\n\t\t\t\tFROM pricematrix\r\n\t\t\tWHERE stockid='" . $StockID . "'\r\n\t\t\tAND startdate='" . $SQLStartDate . "'\r\n\t\t\tAND enddate='" . $SQLEndDate . "'\r\n\t\t        AND salestype='" . $_POST['TypeAbbrev'] . "'\r\n\t\t\tAND currabrev='" . $_POST['currabrev'] . "'\r\n\t\t\tAND quantitybreak='" . $_POST['quantitybreak'] . "'";
 $result = DB_query($sql);
 $myrow = DB_fetch_row($result);
 if ($myrow[0] != 0 and !isset($_POST['OldTypeAbbrev']) and !isset($_POST['OldCurrAbrev'])) {
     prnMsg(_('This price has already been entered. To change it you should edit it'), 'warn');
     $InputError = 1;
 }
 if (isset($_POST['OldTypeAbbrev']) and isset($_POST['OldCurrAbrev']) and mb_strlen($StockID) > 1 and $InputError != 1) {
     /* Update existing prices */
     $sql = "UPDATE pricematrix SET \r\n\t\t\t\t\tsalestype='" . $_POST['SalesType'] . "',\r\n\t\t\t\t\tcurrabrev='" . $_POST['CurrAbrev'] . "',\r\n\t\t\t\t\tprice='" . filter_number_format($_POST['Price']) . "',\r\n\t\t\t\t\tstartdate='" . $SQLStartDate . "',\r\n\t\t\t\t\tenddate='" . $SQLEndDate . "',\r\n\t\t\t\t\tquantitybreak='" . filter_number_format($_POST['QuantityBreak']) . "'\r\n\t\t\t\tWHERE stockid='" . $StockID . "'\r\n\t\t\t\tAND startdate='" . $_POST['OldStartDate'] . "'\r\n\t\t\t\tAND enddate='" . $_POST['OldEndDate'] . "'\r\n\t\t\t\tAND salestype='" . $_POST['OldTypeAbbrev'] . "'\r\n\t\t\t\tAND currabrev='" . $_POST['OldCurrAbrev'] . "'\r\n\t\t\t\tAND quantitybreak='" . filter_number_format($_POST['OldQuantityBreak']) . "'";
     $ErrMsg = _('Could not be update the existing prices');
     $result = DB_query($sql, $ErrMsg);
     ReSequenceEffectiveDates($StockID, $_POST['SalesType'], $_POST['CurrAbrev'], $_POST['QuantityBreak'], $db);
     prnMsg(_('The price has been updated'), 'success');
 } elseif ($InputError != 1) {
     /* actions to take once the user has clicked the submit button
     	ie the page has called itself with some user input */
     $sql = "INSERT INTO pricematrix (salestype,\r\n\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\tquantitybreak,\r\n\t\t\t\t\t\t\tprice,\r\n\t\t\t\t\t\t\tcurrabrev,\r\n\t\t\t\t\t\t\tstartdate,\r\n\t\t\t\t\t\t\tenddate)\r\n\t\t\t\t\tVALUES('" . $_POST['SalesType'] . "',\r\n\t\t\t\t\t\t'" . $_POST['StockID'] . "',\r\n\t\t\t\t\t\t'" . filter_number_format($_POST['QuantityBreak']) . "',\r\n\t\t\t\t\t\t'" . filter_number_format($_POST['Price']) . "',\r\n\t\t\t\t\t\t'" . $_POST['CurrAbrev'] . "',\r\n\t\t\t\t\t\t'" . $SQLStartDate . "',\r\n\t\t\t\t\t\t'" . $SQLEndDate . "')";
Example #23
0
}
echo '<br><table><tr>
	<td>' . _('Deliver To') . ':</td>
	<td><input type=text size=42 maxlength=40 name="DeliverTo" value="' . html_entity_decode($_SESSION['Items' . $identifier]->DeliverTo) . '"></td>
</tr>';
echo '<tr>
	<td>' . _('Deliver from the warehouse at') . ':</td>
	<td><select name="Location">';
if ($_SESSION['Items' . $identifier]->Location == '' or !isset($_SESSION['Items' . $identifier]->Location)) {
    $_SESSION['Items' . $identifier]->Location = $DefaultStockLocation;
}
$ErrMsg = _('The stock locations could not be retrieved');
$DbgMsg = _('SQL used to retrieve the stock locations was') . ':';
$StkLocsResult = DB_query('SELECT locationname,loccode
					FROM locations', $db, $ErrMsg, $DbgMsg);
while ($myrow = DB_fetch_row($StkLocsResult)) {
    if ($_SESSION['Items' . $identifier]->Location == $myrow[1]) {
        echo '<option selected value="' . $myrow[1] . '">' . $myrow[0];
    } else {
        echo '<option value="' . $myrow[1] . '">' . $myrow[0];
    }
}
echo '</select></td></tr>';
// Set the default date to earliest possible date if not set already
if (!isset($_SESSION['Items' . $identifier]->DeliveryDate)) {
    $_SESSION['Items' . $identifier]->DeliveryDate = Date($_SESSION['DefaultDateFormat'], $EarliestDispatch);
}
if (!isset($_SESSION['Items' . $identifier]->QuoteDate)) {
    $_SESSION['Items' . $identifier]->QuoteDate = Date($_SESSION['DefaultDateFormat'], $EarliestDispatch);
}
if (!isset($_SESSION['Items' . $identifier]->ConfirmedDate)) {
Example #24
0
        echo "<td class=\"visible\">" . $myrow[0] . "</td>";
        echo "<td class=\"visible\">" . $myrow[1] . "</td></tr>";
    }
    //end of foreach scheduled subjects
    $sql = "SELECT er.rule FROM academic_year_remarks ayr \n\t\tINNER JOIN exam_rules er ON er.id=ayr.comment_id\n\t\tWHERE student_id= '" . $student_id . "'\n\t\tAND academic_year_id='" . $_SESSION['period'] . "'";
    $result = DB_query($sql, $db);
    $myrow = DB_fetch_row($result);
    $comment = $myrow[0];
    echo "<tr><td class=\"visible\"> <font color='blue' size='2'><b>" . _('Remark :') . "</b></font></td><td colspan='3' class=\"visible\">" . $comment . "</td></tr>";
    $sql = "SELECT sub.subject_name FROM subjects sub \n\t\tINNER JOIN fails ON fails.course_id=sub.id\n\t\tWHERE fails.student_id= '" . $student_id . "'\n\t\tAND fails.period_id='" . $_SESSION['period'] . "'";
    $result = DB_query($sql, $db);
    if (DB_fetch_row($result) > 0) {
        echo "<tr><td class=\"visible\"><font color='red' size='4'>" . _('Reseat the following courses :') . "</font></td><td colspan='3' class=\"visible\"></td></tr>";
        $sql = "SELECT sub.subject_name FROM subjects sub \n\t\tINNER JOIN fails ON fails.course_id=sub.id\n\t\tWHERE fails.student_id= '" . $student_id . "'\n\t\tAND fails.period_id='" . $_SESSION['period'] . "'";
        $result = DB_query($sql, $db);
        while ($myrow = DB_fetch_row($result)) {
            echo "<tr><td class=\"visible\"></td><td colspan='3' class=\"visible\">" . $myrow[0] . "</td></tr>";
        }
    }
    echo "</table><br>";
} else {
    /*The option to print PDF was not hit */
    include 'includes/session.inc';
    $title = _('Manage Students');
    include 'includes/header.inc';
    echo '<FORM METHOD="POST" ACTION="' . $_SERVER['PHP_SELF'] . '?' . SID . '">';
    echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
    echo '<CENTER><TABLE class="selection"><TR><TD class="visible">' . _('Academic Year:') . '</TD><TD class="visible"><SELECT Name="period_id">';
    $result = DB_query('SELECT id,year FROM years', $db);
    while ($myrow = DB_fetch_array($result)) {
        if ($myrow['id'] == $_POST['period_id']) {
Example #25
0
    exit;
}
if (isset($_POST['UpdateRates'])) {
    $TaxRatesResult = DB_query("SELECT taxauthrates.taxcatid,\n\t\t\t\t\t\t\t\t\t\ttaxauthrates.taxrate,\n\t\t\t\t\t\t\t\t\t\ttaxauthrates.dispatchtaxprovince\n\t\t\t\t\t\t\t\tFROM taxauthrates\n\t\t\t\t\t\t\t\tWHERE taxauthrates.taxauthority='" . $TaxAuthority . "'", $db);
    while ($myrow = DB_fetch_array($TaxRatesResult)) {
        $sql = "UPDATE taxauthrates SET taxrate=" . filter_number_format($_POST[$myrow['dispatchtaxprovince'] . '_' . $myrow['taxcatid']]) / 100 . "\n\t\t\t\t\t\tWHERE taxcatid = '" . $myrow['taxcatid'] . "'\n\t\t\t\t\t\tAND dispatchtaxprovince = '" . $myrow['dispatchtaxprovince'] . "'\n\t\t\t\t\t\tAND taxauthority = '" . $TaxAuthority . "'";
        DB_query($sql, $db);
    }
    prnMsg(_('All rates updated successfully'), 'info');
}
/* end of update code
 */
/*Display updated rates
 */
$TaxAuthDetail = DB_query("SELECT description\n\t\t\t\t\t\t\tFROM taxauthorities WHERE taxid='" . $TaxAuthority . "'", $db);
$myrow = DB_fetch_row($TaxAuthDetail);
echo '<form onSubmit="return VerifyForm(this);" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" class="noPrint">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<input type="hidden" name="TaxAuthority" value="' . $TaxAuthority . '" />';
$TaxRatesResult = DB_query("SELECT taxauthrates.taxcatid,\n\t\t\t\t\t\t\t\t\ttaxcategories.taxcatname,\n\t\t\t\t\t\t\t\t\ttaxauthrates.taxrate,\n\t\t\t\t\t\t\t\t\ttaxauthrates.dispatchtaxprovince,\n\t\t\t\t\t\t\t\t\ttaxprovinces.taxprovincename\n\t\t\t\t\t\t\tFROM taxauthrates INNER JOIN taxauthorities\n\t\t\t\t\t\t\tON taxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\t\t\tINNER JOIN taxprovinces\n\t\t\t\t\t\t\tON taxauthrates.dispatchtaxprovince= taxprovinces.taxprovinceid\n\t\t\t\t\t\t\tINNER JOIN taxcategories\n\t\t\t\t\t\t\tON taxauthrates.taxcatid=taxcategories.taxcatid\n\t\t\t\t\t\t\tWHERE taxauthrates.taxauthority='" . $TaxAuthority . "'\n\t\t\t\t\t\t\tORDER BY taxauthrates.dispatchtaxprovince,\n\t\t\t\t\t\t\ttaxauthrates.taxcatid", $db);
if (isset($_SESSION['FirstStart'])) {
    echo '<div class="page_help_text">' . _('As this is the first time that the system has been used, you must first create a tax authority.') . '<br />' . _('For help, click on the help icon in the top right') . '<br />' . _('Once you have filled in all the details, click on the button at the bottom of the screen') . '</div>';
}
if (DB_num_rows($TaxRatesResult) > 0) {
    echo '<table class="selection">';
    echo '<tr>
			<th colspan="3"><h3>' . _('Update') . ' ' . $myrow[0] . ' ' . _('Rates') . '</h3></th>
		</tr>
		<tr>
			<th class="SortableColumn">' . _('Deliveries From') . '<br />' . _('Tax Province') . '</th>
			<th class="SortableColumn">' . _('Tax Category') . '</th>
 }
 $sql = "SELECT materialcost, labourcost, overheadcost, decimalplaces FROM stockmaster WHERE stockid='" . $StockID . "'";
 $result = DB_query($sql, $db);
 $myrow = DB_fetch_array($result);
 $StandardCost = $myrow['materialcost'] + $myrow['labourcost'] + $myrow['overheadcost'];
 $DecimalPlaces = $myrow['decimalplaces'];
 $Narrative = _('Issue') . ' ' . $Quantity . ' ' . _('of') . ' ' . $StockID . ' ' . _('to department') . ' ' . $Department . ' ' . _('from') . ' ' . $Location;
 $AdjustmentNumber = GetNextTransNo(17, $db);
 $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
 $SQLAdjustmentDate = FormatDateForSQL(Date($_SESSION['DefaultDateFormat']));
 $Result = DB_Txn_Begin($db);
 // Need to get the current location quantity will need it later for the stock movement
 $SQL = "SELECT locstock.quantity\n\t\t\t\t\tFROM locstock\n\t\t\t\t\tWHERE locstock.stockid='" . $StockID . "'\n\t\t\t\t\t\tAND loccode= '" . $Location . "'";
 $Result = DB_query($SQL, $db);
 if (DB_num_rows($Result) == 1) {
     $LocQtyRow = DB_fetch_row($Result);
     $QtyOnHandPrior = $LocQtyRow[0];
 } else {
     // There must actually be some error this should never happen
     $QtyOnHandPrior = 0;
 }
 if ($_SESSION['ProhibitNegativeStock'] == 0 or $_SESSION['ProhibitNegativeStock'] == 1 and $QtyOnHandPrior >= $Quantity) {
     $SQL = "INSERT INTO stockmoves (\n\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t'" . $StockID . "',\n\t\t\t\t\t\t\t\t\t17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $Location . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . -$Quantity . "',\n\t\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $Quantity) . "'\n\t\t\t\t\t\t\t\t)";
     $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record cannot be inserted because');
     $DbgMsg = _('The following SQL to insert the stock movement record was used');
     $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
     /*Get the ID of the StockMove... */
     $StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
     $SQL = "UPDATE stockrequestitems\n\t\t\t\t\t\tSET qtydelivered=qtydelivered+" . $Quantity . "\n\t\t\t\t\t\tWHERE dispatchid='" . $RequestID . "'\n\t\t\t\t\t\t\tAND dispatchitemsid='" . $LineID . "'";
     $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
     $DbgMsg = _('The following SQL to update the stock record was used');
Example #27
0
    $_POST['CategoryID'] = '';
}
$sql = "SELECT stkcatpropid,\n                    label,\n                    controltype,\n                    defaultvalue,\n                    numericvalue,\n                    minimumvalue,\n                    maximumvalue\n            FROM stockcatproperties\n            WHERE categoryid ='" . $_POST['CategoryID'] . "'\n            AND reqatsalesorder =0\n            ORDER BY stkcatpropid";
$PropertiesResult = DB_query($sql, $db);
$PropertyCounter = 0;
$PropertyWidth = array();
if (DB_num_rows($PropertiesResult) > 0) {
    echo '<br />
        <table class="selection">';
    echo '<tr>
                <th colspan="2">' . _('Item Category Properties') . '</th>
            </tr>';
    while ($PropertyRow = DB_fetch_array($PropertiesResult)) {
        if (isset($_POST['StockID']) && !empty($_POST['StockID'])) {
            $PropValResult = DB_query("SELECT value FROM\n                                            stockitemproperties\n                                            WHERE stockid='" . $_POST['StockID'] . "'\n                                            AND stkcatpropid ='" . $PropertyRow['stkcatpropid'] . "'", $db);
            $PropValRow = DB_fetch_row($PropValResult);
            $PropertyValue = $PropValRow[0];
        } else {
            $PropertyValue = '';
        }
        echo '<tr>
                    <td>';
        echo '<input type="hidden" name="PropID' . $PropertyCounter . '" value="' . $PropertyRow['stkcatpropid'] . '" />';
        echo '<input type="hidden" name="PropNumeric' . $PropertyCounter . '" value="' . $PropertyRow['numericvalue'] . '" />';
        echo $PropertyRow['label'] . '</td>

                    <td>';
        switch ($PropertyRow['controltype']) {
            case 0:
                //textbox
                if ($PropertyRow['numericvalue'] == 1) {
while ($myrow = DB_fetch_array($Result)) {
    $SQL2 = "SELECT SUM((ovamount+ovgst)/rate)\n\t\t\tFROM debtortrans\n\t\t\tWHERE type = 12\n\t\t\tAND transno = '" . $myrow['typeno'] . "'";
    $Result2 = DB_query($SQL2, $db);
    $myrow2 = DB_fetch_row($Result2);
    if ($myrow2[0] + $myrow['amount'] == 0) {
        echo '<br />' . _('Receipt') . ' ' . $myrow['typeno'] . " : ";
        echo '<div style="color:red">' . $myrow['amount'] . ' ' . _('in GL but found') . ' ' . $myrow2[0] . ' ' . _('in debtorstrans') . '</div>';
    }
}
echo '<br /><br />' . _('Check for orphan Receipts') . '<br />';
$SQL = "SELECT transno FROM debtortrans WHERE type = 12";
$Result = DB_query($SQL, $db);
while ($myrow = DB_fetch_array($Result)) {
    $SQL2 = "SELECT amount FROM gltrans WHERE type = 12 AND typeno = '" . $myrow['transno'] . "'";
    $Result2 = DB_query($SQL2, $db);
    $myrow2 = DB_fetch_row($Result2);
    if (!$myrow2[0]) {
        echo '<br />' . _('Receipt') . ' ' . $myrow['transno'] . " : ";
        echo '<div style="color:red">' . $myrow['transno'] . ' ' . _('not found in GL') . "</div>";
    }
}
echo '<br /><br />' . _('Check for orphan Sales Orders') . '<br />';
$SQL = "SELECT orderno, orddate FROM salesorders";
$Result = DB_query($SQL, $db);
while ($myrow = DB_fetch_array($Result)) {
    $SQL2 = "SELECT transno,\n\t\t\t\t\torder_,\n\t\t\t\t\ttrandate\n\t\t\t\tFROM debtortrans\n\t\t\t\tWHERE type = 10\n\t\t\t\tAND order_ = '" . $myrow['orderno'] . "'";
    $Result2 = DB_query($SQL2, $db);
    if (DB_num_rows($Result2) == 0) {
        echo '<br />' . _('Sales Order') . ' ' . $myrow['orderno'] . ' : ';
        echo '<div style="color:red">' . _('Has no Invoice') . '</div>';
    }
         $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
         /* now insert the serial stock movement */
         $SQL = "INSERT INTO stockserialmoves (stockmoveno,\r\n\t\t\t\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\t\t\t\tserialno,\r\n\t\t\t\t\t\t\t\t\t\tmoveqty)\r\n\t\t\t\t\t\tVALUES (" . $StkMoveNo . ",\r\n\t\t\t\t\t\t\t'" . $OrderLine->StockID . "',\r\n\t\t\t\t\t\t\t'" . $Item->BundleRef . "',\r\n\t\t\t\t\t\t\t" . -$Item->BundleQty . ")";
         $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The serial stock movement record could not be inserted because');
         $DbgMsg = _('The following SQL to insert the serial stock movement records was used');
         $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
     }
     /* foreach controlled item in the serialitems array */
 }
 /*end if the orderline is a controlled item */
 /*Insert Sales Analysis records */
 $SQL = "SELECT COUNT(*),\r\n\t\t\t\t\tsalesanalysis.stockid,\r\n\t\t\t\t\tsalesanalysis.stkcategory,\r\n\t\t\t\t\tsalesanalysis.cust,\r\n\t\t\t\t\tsalesanalysis.custbranch,\r\n\t\t\t\t\tsalesanalysis.area,\r\n\t\t\t\t\tsalesanalysis.periodno,\r\n\t\t\t\t\tsalesanalysis.typeabbrev,\r\n\t\t\t\t\tsalesanalysis.salesperson\r\n\t\t\t\tFROM salesanalysis,\r\n\t\t\t\t\tcustbranch,\r\n\t\t\t\t\tstockmaster\r\n\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\r\n\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\r\n\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\r\n\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\r\n\t\t\t\tAND salesanalysis.area=custbranch.area\r\n\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\r\n\t\t\t\tAND salesanalysis.typeabbrev ='" . $_SESSION['Items']->DefaultSalesType . "'\r\n\t\t\t\tAND salesanalysis.periodno=" . $PeriodNo . "\r\n\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $_SESSION['Items']->DebtorNo . "'\r\n\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $_SESSION['Items']->Branch . "'\r\n\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $OrderLine->StockID . "'\r\n\t\t\t\tAND salesanalysis.budgetoractual=1\r\n\t\t\t\tGROUP BY salesanalysis.stockid,\r\n\t\t\t\t\tsalesanalysis.stkcategory,\r\n\t\t\t\t\tsalesanalysis.cust,\r\n\t\t\t\t\tsalesanalysis.custbranch,\r\n\t\t\t\t\tsalesanalysis.area,\r\n\t\t\t\t\tsalesanalysis.periodno,\r\n\t\t\t\t\tsalesanalysis.typeabbrev,\r\n\t\t\t\t\tsalesanalysis.salesperson";
 $ErrMsg = _('The count of existing Sales analysis records could not run because');
 $DbgMsg = '<br>' . _('SQL to count the no of sales analysis records');
 $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
 $myrow = DB_fetch_row($Result);
 if ($myrow[0] > 0) {
     /*Update the existing record that already exists */
     $SQL = "UPDATE salesanalysis\r\n\t\t\t\t\tSET amt=amt+" . $OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . ",\r\n\t\t\t\t\tcost=cost+" . $OrderLine->StandardCost * $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\tqty=qty +" . $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\tdisc=disc+" . $OrderLine->DiscountPercent * $OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[5] . "'\r\n\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[8] . "'\r\n\t\t\t\t\tAND typeabbrev ='" . $_SESSION['Items']->DefaultSalesType . "'\r\n\t\t\t\t\tAND periodno = " . $PeriodNo . "\r\n\t\t\t\t\tAND cust " . LIKE . " '" . $_SESSION['Items']->DebtorNo . "'\r\n\t\t\t\t\tAND custbranch " . LIKE . " '" . $_SESSION['Items']->Branch . "'\r\n\t\t\t\t\tAND stockid " . LIKE . " '" . $OrderLine->StockID . "'\r\n\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[2] . "'\r\n\t\t\t\t\tAND budgetoractual=1";
 } else {
     /* insert a new sales analysis record */
     $SQL = "INSERT INTO salesanalysis (\r\n\t\t\t\t\t\ttypeabbrev,\r\n\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\tamt,\r\n\t\t\t\t\t\tcost,\r\n\t\t\t\t\t\tcust,\r\n\t\t\t\t\t\tcustbranch,\r\n\t\t\t\t\t\tqty,\r\n\t\t\t\t\t\tdisc,\r\n\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\tarea,\r\n\t\t\t\t\t\tbudgetoractual,\r\n\t\t\t\t\t\tsalesperson,\r\n\t\t\t\t\t\tstkcategory\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\tSELECT '" . $_SESSION['Items']->DefaultSalesType . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . ",\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost * $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t\t" . $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t" . $OrderLine->DiscountPercent * $OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . ",\r\n\t\t\t\t\t\t'" . $OrderLine->StockID . "',\r\n\t\t\t\t\t\tcustbranch.area,\r\n\t\t\t\t\t\t1,\r\n\t\t\t\t\t\tcustbranch.salesman,\r\n\t\t\t\t\t\tstockmaster.categoryid\r\n\t\t\t\t\tFROM stockmaster,\r\n\t\t\t\t\t\tcustbranch\r\n\t\t\t\t\tWHERE stockmaster.stockid = '" . $OrderLine->StockID . "'\r\n\t\t\t\t\tAND custbranch.debtorno = '" . $_SESSION['Items']->DebtorNo . "'\r\n\t\t\t\t\tAND custbranch.branchcode='" . $_SESSION['Items']->Branch . "'";
 }
 $ErrMsg = _('Sales analysis record could not be added or updated because');
 $DbgMsg = _('The following SQL to insert the sales analysis record was used');
 $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
 /* If GLLink_Stock then insert GLTrans to credit stock and debit cost of sales at standard cost*/
 if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $OrderLine->StandardCost != 0) {
     /*first the cost of sales entry*/
     $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . GetCOGSGLAccount($Area, $OrderLine->StockID, $_SESSION['Items']->DefaultSalesType, $db) . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->StandardCost . "',\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost * $OrderLine->QtyDispatched . "\r\n\t\t\t\t\t)";
     $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because');
<?php

/* $Id: DebtorsAtPeriodEnd.php 6338 2013-09-28 05:10:46Z daintree $*/
include 'includes/session.inc';
if (isset($_POST['PrintPDF']) and isset($_POST['FromCriteria']) and mb_strlen($_POST['FromCriteria']) >= 1 and isset($_POST['ToCriteria']) and mb_strlen($_POST['ToCriteria']) >= 1) {
    include 'includes/PDFStarter.php';
    $pdf->addInfo('Title', _('Customer Balance Listing'));
    $pdf->addInfo('Subject', _('Customer Balances'));
    $FontSize = 12;
    $PageNumber = 0;
    $line_height = 12;
    /*Get the date of the last day in the period selected */
    $SQL = "SELECT lastdate_in_period FROM periods WHERE periodno = '" . $_POST['PeriodEnd'] . "'";
    $PeriodEndResult = DB_query($SQL, $db, _('Could not get the date of the last day in the period selected'));
    $PeriodRow = DB_fetch_row($PeriodEndResult);
    $PeriodEndDate = ConvertSQLDate($PeriodRow[0]);
    /*Now figure out the aged analysis for the customer range under review */
    $SQL = "SELECT debtorsmaster.debtorno,\n\t\t\t\t\tdebtorsmaster.name,\n\t\t  \t\t\tcurrencies.currency,\n\t\t  \t\t\tcurrencies.decimalplaces,\n\t\t\t\t\tSUM((debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)/debtortrans.rate) AS balance,\n\t\t\t\t\tSUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) AS fxbalance,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\t(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount)/debtortrans.rate ELSE 0 END) AS afterdatetrans,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "'\n\t\t\t\t\t\tAND (debtortrans.type=11 OR debtortrans.type=12) THEN\n\t\t\t\t\t\tdebtortrans.diffonexch ELSE 0 END) AS afterdatediffonexch,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\tdebtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount ELSE 0 END\n\t\t\t\t\t) AS fxafterdatetrans\n\t\t\tFROM debtorsmaster INNER JOIN currencies\n\t\t\tON debtorsmaster.currcode = currencies.currabrev\n\t\t\tINNER JOIN debtortrans\n\t\t\tON debtorsmaster.debtorno = debtortrans.debtorno\n\t\t\tWHERE debtorsmaster.debtorno >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND debtorsmaster.debtorno <= '" . $_POST['ToCriteria'] . "'\n\t\t\tGROUP BY debtorsmaster.debtorno,\n\t\t\t\tdebtorsmaster.name,\n\t\t\t\tcurrencies.currency,\n\t\t\t\tcurrencies.decimalplaces";
    $CustomerResult = DB_query($SQL, $db, '', '', false, false);
    if (DB_error_no($db) != 0) {
        $Title = _('Customer Balances') . ' - ' . _('Problem Report');
        include 'includes/header.inc';
        prnMsg(_('The customer details could not be retrieved by the SQL because') . DB_error_msg($db), 'error');
        echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
        if ($debug == 1) {
            echo '<br />' . $SQL;
        }
        include 'includes/footer.inc';
        exit;
    }
    if (DB_num_rows($CustomerResult) == 0) {