Exemplo n.º 1
0
function VerifyItemDueDate($itemdue, $i, $Errors, $db)
{
    $sql = 'select confvalue from config where confname="' . DefaultDateFormat . '"';
    $result = api_DB_query($sql, $db);
    $myrow = DB_fetch_array($result);
    $DateFormat = $myrow[0];
    if (strstr($itemdue, '/')) {
        $DateArray = explode('/', $itemdue);
    } elseif (strstr($itemdue, '.')) {
        $DateArray = explode('.', $itemdue);
    }
    if ($DateFormat == 'd/m/Y') {
        $Day = $DateArray[0];
        $Month = $DateArray[1];
        $Year = $DateArray[2];
    } elseif ($DateFormat == 'm/d/Y') {
        $Day = $DateArray[1];
        $Month = $DateArray[0];
        $Year = $DateArray[2];
    } elseif ($DateFormat == 'Y/m/d') {
        $Day = $DateArray[2];
        $Month = $DateArray[1];
        $Year = $DateArray[0];
    } elseif ($DateFormat == 'd.m.Y') {
        $Day = $DateArray[0];
        $Month = $DateArray[1];
        $Year = $DateArray[2];
    }
    if (!checkdate(intval($Month), intval($Day), intval($Year))) {
        $Errors[$i] = InvalidItemDueDate;
    }
    return $Errors;
}
function AllocateTrans($AllocDetails, $User, $Password)
{
    /* This function is quite specific and probably not generally useful
     * It only attempts to allocate a receipt or credit note sent to invoices that have a customerref equal to the value sent
     *
     * The first parameter  AllocDetails is an associative array containing:
     * AllocDetails['debtorno']
     * AllocDetails['type']
     * AllocDetails['transno']
     * AllocDetails['customerref']
     */
    $Errors = array();
    $db = db($User, $Password);
    if (gettype($db) == 'integer') {
        $Errors[0] = NoAuthorisation;
        return $Errors;
    }
    $Errors = VerifyDebtorExists($AllocDetails['debtorno'], sizeof($Errors), $Errors, $db);
    /*Get the outstanding amount to allocate (all amounts in FX) from the transaction*/
    if ($AllocDetails['type'] != '11' and $AllocDetails['type'] != 12) {
        $Errors[] = MustBeReceiptOrCreditNote;
    }
    $SQL = "SELECT id,\n\t\t\t\t\trate,\n\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS lefttoalloc\n\t\t\t\tFROM debtortrans\n\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\tAND type='" . $AllocDetails['type'] . "'\n\t\t\t\tAND transno='" . $AllocDetails['transno'] . "'";
    $Result = api_DB_query($SQL, $db);
    $LeftToAllocRow = DB_fetch_array($Result);
    if (DB_num_rows($Result) == 0) {
        $Errors[0] = NoTransactionToAllocate;
        return $Errors;
    }
    if ($LeftToAllocRow['lefttoalloc'] < 0) {
        /* negative if it is a positive receipt to allocate against invoices */
        /*Now look for invoices with the same customerref to allocate to */
        $SQL = "SELECT id,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS outstanding\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\t\tAND type=10\n\t\t\t\t\tAND reference='" . $AllocDetails['customerref'] . "'\n\t\t\t\t\tAND ovamount+ovgst+ovdiscount-alloc >0";
        $Result = api_DB_query($SQL, $db);
        if (DB_num_rows($Result) == 0) {
            $Errors[0] = NoTransactionToAllocate;
            return $Errors;
        }
        /* The receipts will always be smaller than the invoice as there can be several receipts to make up payment for a single invocie - but not vice-versa - that's why there is no point iterating over the possibilities  - in the words of McCloud "There can be only one"*/
        $OSInvRow = DB_fetch_array($Result);
        if ($OSInvRow['rate'] == $LeftToAllocRow['rate'] and $OSInvRow['outstanding'] > 0) {
            if ($OSInvRow['outstanding'] + $LeftToAllocRow['lefttoalloc'] >= 0) {
                /*We can allocate the whole amount of the credit/receipt */
                $AllocateAmount = -$LeftToAllocRow['lefttoalloc'];
            } else {
                /*We can only allocate the rest of the invoice outstanding */
                $AllocateAmount = $OSInvRow['outstanding'];
            }
            DB_Txn_Begin($db);
            /*Now insert the allocation records */
            $SQL = "INSERT INTO custallocns (amt,\n\t\t\t\t\t\t\t\t\t\t\t\tdatealloc,\n\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocfrom,\n\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocto)\n\t\t\t\t\t\t\t\t\tVALUE('" . $AllocateAmount . "',\n\t\t\t\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $LeftToAllocRow['id'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OSInvRow['id'] . "')";
            $Result = api_DB_query($SQL, $db, '', '', true);
            /*Now update the allocated amounts in the debtortrans for both transactions */
            $SQL = "UPDATE debtortrans SET alloc=alloc-" . $AllocateAmount . "\n\t\t\t\t\t\tWHERE id = '" . $LeftToAllocRow['id'] . "'";
            $Result = api_DB_query($SQL, $db, '', '', true);
            $SQL = "UPDATE debtortrans SET alloc=alloc+" . $AllocateAmount . "\n\t\t\t\t\t\tWHERE id = '" . $OSInvRow['id'] . "'";
            $Result = api_DB_query($SQL, $db, '', '', true);
        }
        /*end if the exchange rates are the same so no diff on exchange */
        /*end if it is a normal allocation of receipt to invoice*/
    } elseif ($LeftToAllocRow['lefttoalloc'] > 0) {
        /* it is a payment - negative receipt - already checked type=12 need to find credit note to allocate to
        	  Now look for credit notes  type 11 with the same customerref to allocate to */
        $SQL = "SELECT id,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS outstanding\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\t\tAND type=11\n\t\t\t\t\tAND reference='" . $AllocDetails['customerref'] . "'\n\t\t\t\t\tAND ovamount+ovgst+ovdiscount-alloc < -0.005";
        $Result = api_DB_query($SQL, $db);
        if (DB_num_rows($Result) == 0) {
            // then we need to cast the net further afield and look for normal receipts to allocate to
            $SQL = "SELECT id,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS outstanding\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\t\tAND type=12\n\t\t\t\t\tAND reference='" . $AllocDetails['customerref'] . "'\n\t\t\t\t\tAND ovamount+ovgst+ovdiscount-alloc < 0";
            $Result = api_DB_query($SQL, $db);
        }
        if (DB_num_rows($Result) == 0) {
            //then no trans to allocate this to
            $Errors[0] = 'no transactions to allocate this to';
            return $Errors;
        }
        $LeftToAllocate = $LeftToAllocRow['lefttoalloc'];
        DB_Txn_Begin($db);
        /* there could be several receipts to allocate against ... loop through until $LefToAllocate is exhausted */
        while ($OSCreditRow = DB_fetch_array($Result)) {
            if ($OSCreditRow['rate'] == $LeftToAllocRow['rate'] and $LeftToAllocate > 0) {
                if ($OSCreditRow['outstanding'] + $LeftToAllocate <= 0) {
                    /*We can allocate the whole amount of the receipt */
                    $AllocateAmount = $LeftToAllocate;
                    $LeftToAllocate = 0;
                } else {
                    /*We can only allocate the rest of the invoice outstanding */
                    $AllocateAmount = $OSCreditRow['outstanding'];
                    $LeftToAllocate += $OSCreditRow['outstanding'];
                }
                /*Now insert the allocation records */
                $SQL = "INSERT INTO custallocns (amt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdatealloc,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocfrom,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocto)\n\t\t\t\t\t\t\t\t\t\tVALUE('" . $AllocateAmount . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OSCreditRow['id'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $LeftToAllocRow['id'] . "')";
                $Result = api_DB_query($SQL, $db, '', '', true);
                /*Now update the allocated amounts in the debtortrans for both transactions */
                $SQL = "UPDATE debtortrans SET alloc=alloc+" . $AllocateAmount . "\n\t\t\t\t\t\t\tWHERE id = '" . $LeftToAllocRow['id'] . "'";
                $Result = api_DB_query($SQL, $db, '', '', true);
                $SQL = "UPDATE debtortrans SET alloc=alloc-" . $AllocateAmount . "\n\t\t\t\t\t\t\tWHERE id = '" . $OSCreditRow['id'] . "'";
                $Result = api_DB_query($SQL, $db, '', '', true);
            }
        }
        //end loop around potential positive receipts not fully allocated already
    }
    if (sizeof($Errors) == 0) {
        $Result = DB_Txn_Commit($db);
        $Errors[0] = 0;
    } else {
        $Result = DB_Txn_Rollback($db);
    }
    return $Errors;
}
Exemplo n.º 3
0
function GetCustomerBranchCodes($DebtorNumber, $user, $password)
{
    $Errors = array();
    $db = db($user, $password);
    if (gettype($db) == 'integer') {
        $Errors[0] = NoAuthorisation;
        return $Errors;
    }
    $sql = "SELECT branchcode FROM custbranch\n                WHERE debtorno = '" . $DebtorNumber . "'";
    $result = api_DB_query($sql);
    if (DB_error_no() != 0) {
        $Errors[0] = DatabaseUpdateFailed;
    } else {
        $Errors[0] = 0;
        // Signal data may follow.
        while ($myrow = DB_fetch_row($result)) {
            $Errors[] = $myrow[0];
        }
    }
    return $Errors;
}
Exemplo n.º 4
0
function GetCurrentPeriod(&$db)
{
    $TransDate = time();
    //The current date to find the period for
    /* Find the unix timestamp of the last period end date in periods table */
    $sql = "SELECT MAX(lastdate_in_period), MAX(periodno) from periods";
    $result = DB_query($sql, $db);
    $myrow = DB_fetch_row($result);
    if (is_null($myrow[0])) {
        $InsertFirstPeriodResult = api_DB_query("INSERT INTO periods VALUES (0,'" . Date('Y-m-d', mktime(0, 0, 0, Date('m') + 1, 0, Date('Y'))) . "')", $db);
        $InsertFirstPeriodResult = api_DB_query("INSERT INTO periods VALUES (1,'" . Date('Y-m-d', mktime(0, 0, 0, Date('m') + 2, 0, Date('Y'))) . "')", $db);
        $LastPeriod = 1;
        $LastPeriodEnd = mktime(0, 0, 0, Date('m') + 2, 0, Date('Y'));
    } else {
        $Date_Array = explode('-', $myrow[0]);
        $LastPeriodEnd = mktime(0, 0, 0, $Date_Array[1] + 1, 0, (int) $Date_Array[0]);
        $LastPeriod = $myrow[1];
    }
    /* Find the unix timestamp of the first period end date in periods table */
    $sql = "SELECT MIN(lastdate_in_period), MIN(periodno) from periods";
    $result = api_DB_query($sql, $db);
    $myrow = DB_fetch_row($result);
    $Date_Array = explode('-', $myrow[0]);
    $FirstPeriodEnd = mktime(0, 0, 0, $Date_Array[1], 0, (int) $Date_Array[0]);
    $FirstPeriod = $myrow[1];
    /* If the period number doesn't exist */
    if (!PeriodExists($TransDate, $db)) {
        /* if the transaction is after the last period */
        if ($TransDate > $LastPeriodEnd) {
            $PeriodEnd = mktime(0, 0, 0, Date('m', $TransDate) + 1, 0, Date('Y', $TransDate));
            while ($PeriodEnd >= $LastPeriodEnd) {
                if (Date('m', $LastPeriodEnd) <= 13) {
                    $LastPeriodEnd = mktime(0, 0, 0, Date('m', $LastPeriodEnd) + 2, 0, Date('Y', $LastPeriodEnd));
                } else {
                    $LastPeriodEnd = mktime(0, 0, 0, 2, 0, Date('Y', $LastPeriodEnd) + 1);
                }
                $LastPeriod++;
                CreatePeriod($LastPeriod, $LastPeriodEnd, $db);
            }
        } else {
            /* The transaction is before the first period */
            $PeriodEnd = mktime(0, 0, 0, Date('m', $TransDate), 0, Date('Y', $TransDate));
            $Period = $FirstPeriod - 1;
            while ($FirstPeriodEnd > $PeriodEnd) {
                CreatePeriod($Period, $FirstPeriodEnd, $db);
                $Period--;
                if (Date('m', $FirstPeriodEnd) > 0) {
                    $FirstPeriodEnd = mktime(0, 0, 0, Date('m', $FirstPeriodEnd), 0, Date('Y', $FirstPeriodEnd));
                } else {
                    $FirstPeriodEnd = mktime(0, 0, 0, 13, 0, Date('Y', $FirstPeriodEnd));
                }
            }
        }
    } else {
        if (!PeriodExists(mktime(0, 0, 0, Date('m', $TransDate) + 1, Date('d', $TransDate), Date('Y', $TransDate)), $db)) {
            /* Make sure the following months period exists */
            $sql = "SELECT MAX(lastdate_in_period), MAX(periodno) from periods";
            $result = DB_query($sql, $db);
            $myrow = DB_fetch_row($result);
            $Date_Array = explode('-', $myrow[0]);
            $LastPeriodEnd = mktime(0, 0, 0, $Date_Array[1] + 2, 0, (int) $Date_Array[0]);
            $LastPeriod = $myrow[1];
            CreatePeriod($LastPeriod + 1, $LastPeriodEnd, $db);
        }
    }
    /* Now return the period number of the transaction */
    $MonthAfterTransDate = Mktime(0, 0, 0, Date('m', $TransDate) + 1, Date('d', $TransDate), Date('Y', $TransDate));
    $GetPrdSQL = "SELECT periodno\n\t\t\t\t\t\tFROM periods\n\t\t\t\t\t\tWHERE lastdate_in_period < '" . Date('Y-m-d', $MonthAfterTransDate) . "'\n\t\t\t\t\t\tAND lastdate_in_period >= '" . Date('Y-m-d', $TransDate) . "'";
    $ErrMsg = _('An error occurred in retrieving the period number');
    $GetPrdResult = DB_query($GetPrdSQL, $db, $ErrMsg);
    $myrow = DB_fetch_row($GetPrdResult);
    return $myrow[0];
}