Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Function GetPeriod
09-04-2012, 11:16 PM,
#1
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
Reply
09-11-2012, 01:52 PM,
#2
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
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)