File: /home/mostafedeg/public_html/erp/models/mysql/ext/EmployeeMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'employee'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2014-03-09 11:49
*/
class EmployeeMySqlExtDAO extends EmployeeMySqlDAO {
public function queryAllExt() {
$sql = 'SELECT * FROM employee where employeeDele=0 ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//query employees with no user group to show in add.html of userCTRL
public function queryEmployeesWithNOusergroup() {
$sql = 'SELECT * FROM employee WHERE employeeDele=0 AND employeeId NOT IN
(SELECT employeeId FROM user WHERE userDele=0)';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//query employee assigned to user and group specified by employeeId
//and also other employees who haven't assigned to usergroups yet
//to show in edit.html of userCTRL
public function queryEmployees($employeeId) {
$sql = 'SELECT employeeId, employeeName FROM employee WHERE employeeDele=0 AND (employeeId=' . $employeeId . '
OR employeeId NOT IN
(SELECT employeeId FROM user WHERE userDele=0))';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllemployee($queryString = '') {
$sql = 'SELECT employee.* FROM employee
' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllemployeeworker() {
$sql = 'SELECT employee.* FROM employee
where isworker=0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllemployeebycondation($conditions, $queryString = '') {
$sql = 'SELECT employee.* FROM employee
where conditions =' . $conditions . ' ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllwithLimit($startpoint, $perpage) {
$sql = 'SELECT employee.* FROM employee
order by employeeDate desc, employeeId desc limit ' . $startpoint . ',' . $perpage . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllwithtname($name) {
$sql = 'SELECT employee.* FROM employee
where employeeName like "%' . $name . '%" ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllwithLimitname($startpoint, $perpage, $name) {
$sql = 'SELECT branch.branchname , employee.* FROM employee join branch
on branch.branchid=employee.branchId where employeeDele=0 and employeeName like "%' . $name . '%" order by employeeDate desc, employeeId desc limit ' . $startpoint . ',' . $perpage . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateEmployee($employee) {
$sql = 'UPDATE employee SET employeeDate = ?, conditions = ?, userId = ? WHERE employeeId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($employee->employeeDate);
$sqlQuery->setNumber($employee->conditions);
$sqlQuery->setNumber($employee->userId);
$sqlQuery->setNumber($employee->employeeId);
return $this->executeUpdate($sqlQuery);
}
public function queryAllemployeeSalary($from, $end) {
//select employees which were employed before choosen month.
$sql = 'SELECT `employeeId` ,employeeName
FROM employee
where `employeeid` NOT IN (
SELECT employeeid FROM `salaryreport` where salarymonthdate = "' . $from . '" and salaryreport.conditions =0 )
AND conditions=0
AND employeeDate <= "' . $end . '" order by employeeId desc ';
//print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllemployeeSalarylimated($startpoint, $perpage, $start, $end, $branchId) {
//print($startpoint);
//print($perpage);
$sql = 'SELECT `employeeId` FROM employee WHERE branchId=' . $branchId . ' AND `employeeid` NOT
IN (
SELECT employeeid FROM `salaryreport` where salarymonthdate >= "' . $start . '" and salarymonthdate <= "' . $end . '" )
and employeeDele=0 order by employeeId desc limit ' . $startpoint . ',' . $perpage . '';
// print($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getall($id) {
$sql = 'SELECT employee.`employeeName` ,
employee.employeeBouns,
employee.empdept,
employee.`employeeSalary` ,
employee.`employeeId` ,
(SELECT SUM( `employeepersonnelvalue` )
FROM `employeepersonnel`
WHERE `employeeid` =' . $id . '
AND (`employeepersonneltype` =1 or `employeepersonneltype` =13)
and conditions=0 and done=0
) as Reward,
(SELECT SUM( `employeepersonnelvalue` )
FROM `employeepersonnel`
WHERE `employeeid` =' . $id . '
AND `employeepersonneltype` =2
and conditions=0 and done=0
) as Discount,
(SELECT SUM( `employeepersonnelvalue` )
FROM `employeepersonnel`
WHERE `employeeid` =' . $id . '
AND `employeepersonneltype` =3
and conditions=0 and done=0
) as Withdraw
,
(SELECT SUM( `employeepersonnelvalue` )
FROM `employeepersonnel`
WHERE `employeeid` =' . $id . '
AND (`employeepersonneltype` =5 or `employeepersonneltype` =9)
and conditions=0 and done=0
) as Absence
,
(SELECT SUM( `employeepersonnelvalue` )
FROM `employeepersonnel`
WHERE `employeeid` =' . $id . '
AND `employeepersonneltype` =7
and conditions=0 and done=0
) as Repayment
,
(SELECT SUM( `employeepersonnelvalue` )
FROM `employeepersonnel`
WHERE `employeeid` =' . $id . '
AND `employeepersonneltype` =11
and conditions=0 and done=0
) as permission
FROM employee
WHERE employee.`employeeId` =' . $id . '
and employee.conditions=0
GROUP BY employee.`employeeId`';
//print("<br>".$sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getrow($sqlQuery);
}
public function queryEmpIdInEmployeePersonnel($id) {
$sql = 'SELECT employeeid FROM employeepersonnel WHERE employeeid=' . $id . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
/* public function queryEmpIdInSalaryReport($id)
{
$sql = 'SELECT employeeid FROM salaryreport WHERE employeeid='.$id.'';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
*/
public function queryEmpIdInUser($id) {
$sql = 'SELECT employeeId FROM user WHERE userDele=0 AND employeeId=' . $id . '';
//print($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getEmpIdWithFingerId($empFingerId) {
$sql = 'SELECT employeeId FROM employee WHERE empFingerId = ' . $empFingerId . ' ';
//print($sql);
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function getEmpIdWithRFID($empRFID) {
$sql = 'SELECT employeeId FROM employee WHERE empRFID = ' . $empRFID . ' ';
//print($sql);
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function updatedept($employee) {
$sql = 'UPDATE employee SET empdept = ? WHERE employeeId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($employee->empdept);
$sqlQuery->setNumber($employee->employeeId);
return $this->executeUpdate($sqlQuery);
}
public function queryAllSimple($queryString) {
$sql = 'SELECT employeeId, employeeName,employeePhone,employeeSalary,minuteLateCost,employeeBOD FROM employee WHERE conditions=0 ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllQueryString($queryString, $day) {
$sql = 'SELECT *
FROM employee
join employeeclosedayhistory on (employeeclosedayhistory.employeeid = employee.employeeId and employeeclosedayhistory.day = "' . $day . '")
' . $queryString . ' ';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getEmployeeCount($queryString = '') {
$sql = 'SELECT count(*) FROM employee where 1 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllString($startDate, $endDate, $queryString = '', $isAbsent = 0) {
$sql = 'SELECT employee.*,employeegroup.name as groupName,employeesubgroup.name as supgroupName,employeegroup.employeeattendancesystemid,employeeattendancesystemweek.attendanceTime,employeeattendancesystemweek.departureTime
,employeeclosedayhistory.sysdate
FROM employee join employeegroup on employeegroup.id = employee.employeegroupid
join employeesubgroup on employeesubgroup.id = employee.employeesubgroupid
join employeeattendancesystem on employeeattendancesystem.id = employeegroup.employeeattendancesystemid
join employeeattendancesystemweek on (employeeattendancesystem.id = employeeattendancesystemweek.employeeattendancesystem_id AND employeeattendancesystemweek.employee_id = employee.employeeId)
join (SELECT employeeclosedayhistory.sysdate,employeeclosedayhistory.employeeid,employeeclosedayhistory.isAbsent
FROM employeeclosedayhistory where employeeclosedayhistory.isAbsent = ' . $isAbsent . ' group by employeeclosedayhistory.employeeid ORDER BY id DESC limit 1) employeeclosedayhistory
on employeeclosedayhistory.employeeid = employee.employeeId
where employeeDate >= "' . $startDate . '" AND employeeDate <= "' . $endDate . '" ' . $queryString;
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllByIdAndDate($queryString, $isAbsent) {
$sql = 'SELECT employee.*,employeegroup.name as groupName,employeesubgroup.name as supgroupName,employeegroup.employeeattendancesystemid,employeeattendancesystemweek.attendanceTime,employeeattendancesystemweek.departureTime
,employeeclosedayhistory.sysdate,employeeclosedayhistory.isAbsent
FROM employee join employeegroup on employeegroup.id = employee.employeegroupid
join employeesubgroup on employeesubgroup.id = employee.employeesubgroupid
join employeeattendancesystem on employeeattendancesystem.id = employeegroup.employeeattendancesystemid
join employeeattendancesystemweek on (employeeattendancesystem.id = employeeattendancesystemweek.employeeattendancesystem_id AND employeeattendancesystemweek.employee_id = employee.employeeId)
join (SELECT employeeclosedayhistory.sysdate,employeeclosedayhistory.employeeid,employeeclosedayhistory.isAbsent
FROM employeeclosedayhistory where employeeclosedayhistory.isAbsent = ' . $isAbsent . ' group by employeeclosedayhistory.employeeid ORDER BY id DESC limit 1) employeeclosedayhistory
on employeeclosedayhistory.employeeid = employee.employeeId
where 1 ' . $queryString . '
';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllReportAbsent($queryString) {
$sql = 'SELECT *,employeegroup.name as groupName,employeesubgroup.name as supgroupName,
employeegroup.employeeattendancesystemid
FROM employee join employeegroup on employeegroup.id = employee.employeegroupid
join employeesubgroup on employeesubgroup.id = employee.employeesubgroupid
join employeeattendancesystem on employeeattendancesystem.id = employeegroup.employeeattendancesystemid
where -1 ' . $queryString . '
';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllFromdayhistoty($id) {
$sql = 'SELECT * FROM employeeclosedayhistory where employeeid = ' . $id . ' AND isAbsent = 1';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllwithtnameAndQuerystring($name, $queryString) {
$sql = 'SELECT employee.* FROM employee
where employeeName like "%' . $name . '%" ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllemployeeSalaryWithQueryString($from, $end, $queryString = '') {
//select employees which were employed before choosen month.
$sql = 'SELECT `employeeId`,employeeSalary,employeeName,employeeName,treeId,treeId2,socialInsurance,workTax
FROM employee
where `employeeid` NOT IN (
SELECT employeeid FROM `salaryreport` where salarymonthdate = "' . $from . '" and salaryreport.conditions =0 )
AND conditions=0
AND employeeDate <= "' . $end . '" ' . $queryString . ' order by employeeId desc ';
//print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}