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;
}
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;
}
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];
}