09-04-2012, 11:16 PM,
|
|
Soujiro
Member
|
Posts: 18
Threads: 9
Joined: Jan 2012
|
|
Function GetPeriod
I was trying to add the ability to open/close periods to the system and then discover that the GetPeriod function it's currently lacking the ability to check if a valid result is returned or not. If the last sql in the function
Code: $GetPrdSQL = "SELECT periodno
FROM periods
WHERE lastdate_in_period < '" . Date('Y-m-d', $MonthAfterTransDate) . "'
AND lastdate_in_period >= '" . Date('Y-m-d', $TransDate) . "'";
returns 0 rows the return value of the GetPeriod Function is 0 wich is wrong
|
|
09-11-2012, 01:52 PM,
|
|
phil
Administrator
|
Posts: 1,239
Threads: 14
Joined: Jan 2012
|
|
RE: Function GetPeriod
This function creates periods if it is necessary to do so...
from includes/DateFunctions.inc
Code: function GetPeriod ($TransDate, &$db, $UseProhibit=true) {
/* Convert the transaction date into a unix time stamp.*/
if (mb_strpos($TransDate,'/')) {
$Date_Array = explode('/',$TransDate);
} elseif (mb_strpos ($TransDate,'-')) {
$Date_Array = explode('-',$TransDate);
} elseif (mb_strpos ($TransDate,'.')) {
$Date_Array = explode('.',$TransDate);
}
if (($_SESSION['DefaultDateFormat']=='d/m/Y') or ($_SESSION['DefaultDateFormat']=='d.m.Y')){
$TransDate = mktime(0,0,0,$Date_Array[1],$Date_Array[0],$Date_Array[2]);
} elseif ($_SESSION['DefaultDateFormat']=='m/d/Y'){
$TransDate = mktime(0,0,0,$Date_Array[0],$Date_Array[1],$Date_Array[2]);
} elseif ($_SESSION['DefaultDateFormat']=='Y/m/d'){
$TransDate = mktime(0,0,0,$Date_Array[1],$Date_Array[2],$Date_Array[0]);
}
if (Is_Date(ConvertSQLDate($_SESSION['ProhibitPostingsBefore'])) AND $UseProhibit){ //then the ProhibitPostingsBefore configuration is set
$Date_Array = explode('-', $_SESSION['ProhibitPostingsBefore']);
$ProhibitPostingsBefore = mktime(0,0,0,$Date_Array[1],$Date_Array[2],$Date_Array[0]);
/* If transaction date is in a closed period use the month end of that period */
if ($TransDate < $ProhibitPostingsBefore) {
$TransDate = $ProhibitPostingsBefore;
}
}
/* 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 = DB_query("INSERT INTO periods VALUES (0,'" . Date('Y-m-d',mktime(0,0,0,Date('m')+1,0,Date('Y'))) . "')",$db,_('Could not insert first period'));
$InsertFirstPeriodResult = DB_query("INSERT INTO periods VALUES (1,'" . Date('Y-m-d',mktime(0,0,0,Date('m')+2,0,Date('Y'))) . "')",$db,_('Could not insert second period'));
$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 = 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
FROM periods
WHERE lastdate_in_period < '" . Date('Y-m-d', $MonthAfterTransDate) . "'
AND 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];
}
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
|
|
|