Location: PHPKode > projects > TEMS -Time and Expense Management System > tems/times/DbObj.php
<?php

/* * *******************************************************************************
 * TES is a Time and Expense Management program developed by
 * Initechs, LLC. Copyright (C) 2009 - 2010 Initechs LLC.
 *
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License version 3 as published by the
 * Free Software Foundation with the addition of the following permission added
 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
 * IN WHICH THE COPYRIGHT IS OWNED BY INITECHS, INITECHS DISCLAIMS THE WARRANTY
 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, see http://www.gnu.org/licenses or write to the Free
 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 *
 * You can contact Initechs headquarters at 1841 Piedmont Road, Suite 301,
 * Marietta, GA, USA. or at email address hide@address.com
 *
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU General Public License version 3.
 *
 * In accordance with Section 7(b) of the GNU General Public License version 3,
 * these Appropriate Legal Notices must retain the display od the "Initechs" logo.
 * If the display of the logo is not reasonably feasible for technical reasons,
 * the Appropriate Legal Notices must display the words "Powered by Initechs".

 * ****************************************************************************** */

$basedir = dirname(__FILE__) . '/..';
require_once("$basedir/baseclass/DBCommonFunctions.php");
require_once("$basedir/times/config.php");
require_once("$basedir/invoices/DbObj.php");
require_once("$basedir/baseclass/GetAuthorization.php");

class TimeData extends DBCommonFunctions {

    public function createRow($us, $projid, $taskid, $wdate, $tdesc, $tcomment, $billhrs, $nbhrs, $locn) {
        global $times_status_array;
        $weekendday = $_SESSION['company']['weekendday'];

        if ($us == ''
                or $projid == ''
                or $taskid == ''
                or $wdate == '')
            throw new iInvalidArgumentException();

        if ($billhrs == '')
            $billhrs = 0;
        if ($nbhrs == '')
            $nbhrs = 0;

        if (($billhrs + $nbhrs) == 0) {
            throw new iBLError('billablehours', 'er0087');
        }

        if (!isValidDate($wdate, 'ymd'))
            throw new iInvalidDataException();

        $sus = $this->escapeString($us);
        $sprojid = $this->escapeString($projid);
        $staskid = $this->escapeString($taskid);
        $stdesc = $this->escapeString($tdesc);
        $stcomment = $this->escapeString($tcomment);
        $snbhrs = $this->escapeString($nbhrs);
        $sbillhrs = $this->escapeString($billhrs);
        $slocn = $this->escapeString($locn);
        $swdate = $this->escapeString($wdate);
        $user = loggedUserID();

        $where = " users_id= '$sus' and workdate = '$swdate' ";
        $Data = new TimeData();
        $othernbhours = $Data->getColumnSum('times', 'billablehours', $where);
        $otherbhours = $Data->getColumnSum('times', 'nonbillablehours', $where);
        if (($sbillhrs + $snbhrs + $otherbhours + $othernbhours) > 24) {
            throw new iBLError('nocategory', 'er0058');
        }

        $DbObj = new dbObj();
        $RowData = $DbObj->fetchRow('projects', 'projects_id', $sprojid);
        if ($RowData['status'] <> '10')
            throw new iBLError('projects_id', 'er0043');

        $billToAccountId = $RowData['billtoaccount_id'];

        $RowData = $DbObj->fetchRow('accounts', 'accounts_id', $RowData['accounts_id']);
        if ($RowData['status'] <> '10')
            throw new iBLError('projects_id', 'er0044');

        $RowData = $DbObj->fetchRow('tasks', 'tasks_id', $staskid);
        if ($RowData['status'] <> '10')
            throw new iBLError('projects_id', 'er0018');

        $whereclause = "projects_id = '$sprojid' and tasks_id ='$staskid'";
        $RowData = $DbObj->fetchRowbyWhereClause(projects_tasks, $whereclause);
        if ($RowData == null)
            throw new iBLError('tasks_id', 'er0018');

        $whereclause = "projects_id = '$sprojid' and users_id = '$sus'";
        $RowData = $DbObj->fetchRowbyWhereClause(users_projects_roles, $whereclause);
        if ($RowData == null)
            throw new iBLError('users_id', 'er0019');

        $userError = $this->chkUserGroup($sus);
        if ($userError <> '')
            throw new iBLError('users_id', $userError);

        $query = "select pr.effective_date as effdt,
						 pr.rate as rate
					from projects_roles pr, users_projects_roles upr
				   where pr.projects_id = upr.projects_id
					 and pr.roles_id = upr.roles_id
					 and upr.users_id = '$sus'
					 and upr.projects_id = '$sprojid'
					 and pr.effective_date <= '$swdate'
				order by pr.effective_date desc";
        $dataset = $DbObj->getDatabyQuery($query);
        if (!isset($dataset[0]['effdt']))
            throw new iBLError('workdate', 'er0076');

        $rate = $dataset[0]['rate'];
        $wedate = getWeekEndDate($swdate, $weekendday);

        $this->beginTransaction();

        $Invoicehdr = new InvoiceData();
        $invoiceid = $Invoicehdr->createOpenInvoice($billToAccountId);
        try {
            $query = "INSERT INTO times (users_id, projects_id, tasks_id, workdate, weekenddate, description,
                                comments, billablehours, nonbillablehours, location, status, rate, invoices_id,
                                createat, createby, changeby )
                        VALUES('$sus', '$sprojid', '$staskid', '$swdate','$wedate', '$stdesc',
                                '$stcomment', '$sbillhrs', '$snbhrs', '$slocn', '10', $rate, '$invoiceid',
                                now(), '$user', '$user')";

            $conn = $this->getConnection();
            $conn->query($query);
            $recid = $conn->insert_id;
            $this->chkQueryError($conn, $query);
            $this->commitTransaction();
            return $recid;
        } catch (Exception $e) {
            $this->rollbackTransaction();
            throw $e;
        }
    }

    public function updateRow($uid, $us, $projid, $taskid, $wdate, $tdesc, $tcomment, $billhrs, $nbhrs, $locn, $status) {

        global $times_status_array;
        $weekendday = $_SESSION['company']['weekendday'];

        if ($us == ''
             or $projid == ''
             or $taskid == ''
             or $wdate == ''
             or $status == '')
                throw new iInvalidArgumentException();

        if (!isValidDate($wdate, 'ymd'))
            throw new iInvalidDataException();

        if (!isset($status))
            throw new iInvalidDataException();

        if (!array_key_exists($status, $times_status_array))
            throw new iInvalidDataException();

        if (($billhrs + $nbhrs) == 0)
            throw new iBLError('billablehours', 'er0087');

        $sus = $this->escapeString($us);
        $sprojid = $this->escapeString($projid);
        $staskid = $this->escapeString($taskid);
        $stdesc = $this->escapeString($tdesc);
        $stcomment = $this->escapeString($tcomment);
        $sbillhrs = $this->escapeString($billhrs);
        $snbhrs = $this->escapeString($nbhrs);
        $slocn = $this->escapeString($locn);
        $swdate = $this->escapeString($wdate);
        $sstatus = $this->escapeString($status);
        $user = loggedUserID();
        $wedate = getWeekEndDate($swdate, $weekendday);

        $where = " users_id= '$sus' and workdate = '$swdate' and uid <> '$uid'";
        $Data = new TimeData();
        $othernbhours = $Data->getColumnSum('times', 'billablehours', $where);
        $otherbhours = $Data->getColumnSum('times', 'nonbillablehours', $where);
        if (($sbillhrs + $snbhrs + $otherbhours + $othernbhours) > 24)
            throw new iBLError('nocategory', 'er0058');

        $DbObj = new dbObj();
        $RowData = $DbObj->fetchRow('projects', 'projects_id', $sprojid);
        if ($RowData['status'] <> '10')
            throw new iBLError('projects_id', 'er0043');

        $billToAccountId = $RowData['billtoaccount_id'];

        $RowData = $DbObj->fetchRow('accounts', 'accounts_id', $RowData['accounts_id']);
        if ($RowData['status'] <> '10')
            throw new iBLError('projects_id', 'er0044');

        $RowData = $DbObj->fetchRow('tasks', 'tasks_id', $staskid);
        if ($RowData['status'] <> '10')
            throw new iBLError('tasks_id', 'er0018');

        $whereclause = "projects_id = '$sprojid' and tasks_id ='$staskid'";
        $RowData = $DbObj->fetchRowbyWhereClause(projects_tasks, $whereclause);
        if ($RowData == null)
            throw new iBLError('tasks_id', 'er0018');

        $whereclause = "projects_id = '$sprojid' and users_id = '$sus'";
        $RowData = $DbObj->fetchRowbyWhereClause(users_projects_roles, $whereclause);
        if ($RowData == null)
            throw new iBLError('users_id', 'er0019');

        $userError = $this->chkUserGroup($sus);
        if ($userError <> '')
            throw new iBLError('users_id', $userError);

        $where = "uid = '$uid' ";
        $TR = $DbObj->fetchRowbyWhereClause('times', $where);

        if ($TR['status'] >= '30')
            throw new iBLError('nocategory', 'er0072');

        $query = "select pr.effective_date as effdt,
				pr.rate as rate
		from projects_roles pr, users_projects_roles upr
                where pr.projects_id = upr.projects_id
                      and pr.roles_id = upr.roles_id
                      and upr.users_id = '$sus'
                      and upr.projects_id = '$sprojid'
                      and pr.effective_date <= '$swdate'
		order by pr.effective_date desc";

        $dataset = $DbObj->getDatabyQuery($query);
        if (!isset($dataset[0]['effdt']))
            throw new iBLError('workdate', 'er0076');

        $rate = $dataset[0]['rate'];

        $this->beginTransaction();
        try {
            $query = "UPDATE times
			 SET
                             users_id = '$sus',
                             projects_id = '$sprojid',
                             tasks_id = '$staskid',
                             workdate ='$swdate',
                             weekenddate ='$wedate',
                             rate = '$rate',
                             description ='$stdesc',
                             comments ='$stcomment',
                             nonbillablehours ='$snbhrs',
                             billablehours ='$sbillhrs',
                             location = '$slocn',
                             status = '$sstatus',
                             changeby = '$user'
                       WHERE uid = '$uid'";

            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            $this->commitTransaction();
        } catch (Exception $e) {
            $this->rollbackTransaction();
            throw $e;
        }
    }

    public function deleteRow($table, $uid) {
        if ($table == ''
                or $uid == '')
            throw new iInvalidArgumentException();

        $suid = $this->escapeString($uid);

        $DbObj = new dbObj();

        $where = "uid = '$suid' ";
        $TR = $DbObj->fetchRowbyWhereClause('times', $where);

        if ($TR['status'] >= '30')
            throw new iBLError('nocategory', 'er0071');

        $userError = $this->chkUserGroup($TR['users_id']);
        if ($userError <> '')
            throw new iBLError('users_id', $userError);

        parent::deleteRow($table, $uid);
    }

    public function updateTimeStatus($us, $weekenddate, $newStatus) {

        global $times_status_array;

        if ($us == ''
                or $weekenddate == '')
            throw new iInvalidArgumentException();

        if (!isValidDate($weekenddate, 'ymd'))
            throw new iInvalidDataException();

        $sus = $this->escapeString($us);
        $swdate = $this->escapeString($weekenddate);
        $user = loggedUserID();

        $a_date = explode('-', $swdate);
        $mn = $a_date[1];
        $dy = $a_date[2];
        $yr = $a_date[0];
        $ToDate = mktime(0, 0, 0, $mn, $dy, $yr);
        $FromDate = $ToDate - (7 * 24 * 60 * 60); //Subtract 7 days
        $Todate = strftime("%Y%m%d", $ToDate);
        $Fromdate = strftime("%Y%m%d", $FromDate);

        $this->beginTransaction();
        try {
            If ($newStatus == '20')
                $query = "UPDATE times
                            SET
				users_id = '$sus',
				submitdate ='$swdate',
				status = '20',
				changeby = '$user'
			WHERE workdate >= $Fromdate 
                            and workdate <= $Todate
                            and status = '10'
                            and users_id = '$sus' ";

            If ($newStatus == '30')
                $query = "UPDATE times
                            SET
				users_id = '$sus',
				approvedate ='$swdate',
				status = '30',
				changeby = '$user'
			WHERE workdate >= $Fromdate
                            and workdate <= $Todate
                            and status = '20'
                            and users_id = '$sus' ";

            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            $this->commitTransaction();
        } catch (Exception $e) {
            $this->rollbackTransaction();
            throw $e;
        }
    }

    public function HoldTime($uid) {
        $suid = $this->escapeString($uid);

        $DbObj = new dbObj();
        $RowData = $DbObj->fetchRow('times', 'uid', $suid);

        if ($RowData['status'] >= '30')     // If Time is already approved, cannot hold.
            throw new iBLError('status', 'er0055');

        $userError = $this->chkUserGroup($RowData['users_id']);
        if ($userError <> '')
            throw new iBLError('users_id', $userError);

        $this->UpdateStatusByUid($suid, '80');
    }

    public function ReleaseTime($uid) {
        $suid = $this->escapeString($uid);

        $DbObj = new dbObj();
        $RowData = $DbObj->fetchRow('times', 'uid', $suid);

        if ($RowData['status'] <> '80') // If Time is not held, cannot be released.
            throw new iBLError('status', 'er0070');

        $userError = $this->chkUserGroup($RowData['users_id']);
        if ($userError <> '')
            throw new iBLError('users_id', $userError);

        $this->UpdateStatusByUid($suid, '10');
    }

    public function UpdateStatusByUid($uid, $newStatus) {
        $suid = $this->escapeString($uid);
        $user = loggedUserID();

        $this->beginTransaction();
        try {
            $query = "UPDATE times
			SET
                             status = $newStatus,
                             changeby = '$user'
			WHERE uid ='$suid' ";

            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            $this->commitTransaction();
        } catch (Exception $e) {
            $this->rollbackTransaction();
            throw $e;
        }
    }

    public function countHours($sumVariable, $where='') {
        $query = "SELECT sum($sumVariable) FROM times";
        if ($where <> '')
            $query .= " WHERE $where";

        $conn = $this->getConnection();
        $results = $conn->query($query);
        $this->chkQueryError($conn, $query);

        $row = $results->fetch_array();
        $results->close();
        return $row[0];
    }

    public function getStatus($uid) {
        $query = "SELECT status FROM times";
        $query .= " WHERE uid =$uid";

        $conn = $this->getConnection();
        $results = $conn->query($query);
        $this->chkQueryError($conn, $query);

        $row = $results->fetch_array();
        $results->close();
        return $row[0];
    }

    private function chkUserGroup($sus) {
        $user = loggedUserID();

        $DbObj = new dbObj();
        $RowData = $DbObj->fetchRow('users', 'users_id', $sus);
        if ($RowData['status'] <> '10')
            return ('er0013');

        $usergroup = $RowData['usergroup'];

        if ((strtolower($user) <> 'admin')
                and ($user <> $sus)) {
            $RowData = $DbObj->fetchRow('users', 'users_id', $user);
            if (($RowData['usergroup'] == '')
                    or ($RowData['usergroup'] <> $usergroup))
                return ('er0080');
        }

        return;
    }

    public function getEligibleusers($filter) {
        if ($filter == '')
            $filter = '*';

        $loggedinUser = loggedUserID();

        if (($filter == '*')
                and (strtolower($loggedinUser) == 'admin'))
            return '';

        $where = '';

        // Include or list Current User
        if (($filter == '10')
                or ($filter == '20')
                or ($filter == '*'))
            $where .= "users_id = '$loggedinUser'";

        // Include or list all users who are having same user group
        //Get User group First
        if (($filter == '20')
                or ($filter == '*')) {
            $DbObj = new dbObj();
            $query = "select usergroup
			from users
                       where users_id = '$loggedinUser' ";

            $dataset = $DbObj->getDatabyQuery($query);
            if ($dataset <> null)
                $usergroup = $dataset[0]['usergroup'];

            if ($usergroup <> null) {
                $DbObj = new dbObj();
                $query = "select users_id
                            from users
                           where usergroup = '$usergroup' ";

                $dataset = $DbObj->getDatabyQuery($query);
                if ($dataset <> null)
                    foreach ($dataset as $dataIndex => $dataRow)
                        $where .= " or users_id = '{$dataset[$dataIndex]['users_id']}'";
            }
        }


        // Include or list all users who are reporting to current user
        if (($filter == '30')
                or ($filter == '*')) {
            $DbObj = new dbObj();
            $query = "select users_id
			from users
                       where reportto = '$loggedinUser' ";

            $dataset = $DbObj->getDatabyQuery($query);
            if ($dataset <> null) {
                foreach ($dataset as $dataIndex => $dataRow) {
                    if ($where <> null)
                        $where .= " or ";
                    $where .= "users_id = '{$dataset[$dataIndex]['users_id']}'";
                }
            }
        }

        if ($where <> '')
            $where = "($where)";

        return $where;
    }

}

?>
Return current item: TEMS -Time and Expense Management System