function selectTrain()
{
$conn = tools::connectDatabase();
$data = array();
$ID = array();
$employeeCount = array();
$result = mysqli_query($conn, "SELECT * FROM Training");
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
array_push($ID, $row[0]);
}
for ($x = 0; $x < count($ID); $x++) {
$newID = $ID[$x];
$employeeCount[$newID] = 0;
}
$newResult = mysqli_query($conn, "SELECT * FROM EmployeeTraining");
while ($newRow = mysqli_fetch_array($newResult)) {
for ($i = 0; $i < count($ID); $i++) {
if ($ID[$i] == $newRow['TrainingID']) {
$newID = $ID[$i];
$employeeCount[$newID]++;
}
}
}
for ($z = 0; $z < count($data); $z++) {
$tempID = $data[$z]['TrainingID'];
$data[$z]['count'] = $employeeCount[$tempID];
}
echo json_encode($data);
}
function selectAuthority($authority)
{
$conn = tools::connectDatabase();
$allData = array();
$employeeID = array();
$result = mysqli_query($conn, "SELECT RoleID,RoleName FROM Roles WHERE {$authority}='1' ");
while ($row = mysqli_fetch_array($result)) {
array_push($allData, array('roleID' => $row['RoleID'], 'roleName' => $row['RoleName']));
}
for ($i = 0; $i < count($allData); $i++) {
$roleID = $allData[$i]["roleID"];
$result = mysqli_query($conn, "SELECT EmployeeID FROM UserRole WHERE RoleID='{$roleID}'");
while ($row = mysqli_fetch_array($result)) {
array_push($employeeID, $row['EmployeeID']);
}
}
$employee = array();
for ($i = 0; $i < count($employeeID); $i++) {
$id = $employeeID[$i];
$result = mysqli_query($conn, "SELECT EmployeeID,EmployeeName,DepartmentID,Title From Employees WHERE EmployeeID='{$id}'");
while ($row = mysqli_fetch_array($result)) {
array_push($employee, $row);
}
}
array_push($allData, $employee);
echo json_encode($allData);
}
function addRole($roleName, $roleAuthority)
{
$conn = tools::connectDatabase();
$roleID = tools::getMaxRoleID() + 1;
mysqli_query($conn, "INSERT INTO Roles(RoleID,RoleName,Auth_Authority,Auth_Info,Auth_Job,Auth_Attendance,Auth_Training,Auth_Evaluation,Auth_RP,Auth_Role,Auth_Encrypt) VALUES ('{$roleID}','{$roleName}','{$roleAuthority['0']}','{$roleAuthority['1']}','{$roleAuthority['2']}','{$roleAuthority['3']}','{$roleAuthority['4']}','{$roleAuthority['5']}','{$roleAuthority['6']}','{$roleAuthority['7']}','{$roleAuthority['8']}')");
echo "success";
}
function getEmployeeInformation($userID)
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT * FROM Employees WHERE EmployeeID='{$userID}'");
while ($row = mysqli_fetch_array($result)) {
echo json_encode($row);
}
}
function getEditReward()
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT * FROM RewardsAndPunishments");
$data = array();
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
echo json_encode($data);
}
/**
* Created by PhpStorm.
* User: lenovo
* Date: 2015/11/17
* Time: 11:57
*/
function getModifyEmployee()
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT EmployeeID,EmployeeName From Employees");
$data = array();
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
echo json_encode($data);
}
function getEditEvaluation()
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT * FROM EvaluationProjects");
$data = array();
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
echo json_encode($data);
}
function editTrain($trainingID, $startDate, $endDate, $trainType, $trainDescription)
{
$conn = tools::connectDatabase();
try {
mysqli_query($conn, "UPDATE Training SET BeginDate='{$startDate}',EndDate='{$endDate}',TrainingType='{$trainType}',Description='{$trainDescription}' WHERE TrainingID='{$trainingID}' ");
} catch (Exception $e) {
$e->getMessage();
}
echo "success";
}
function checkModifyEmployee($userID)
{
$conn = tools::connectDatabase();
$data = array();
$result = mysqli_query($conn, "SELECT * FROM Employees WHERE EmployeeID='{$userID}' ");
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
echo json_encode($data);
}
function setDeleteEmployee($userID)
{
$conn = tools::connectDatabase();
try {
mysqli_query($conn, "DELETE FROM Employees WHERE EmployeeID={$userID}");
} catch (Exception $e) {
echo $e->getMessage();
}
echo "success";
}
function getSelectRole()
{
$conn = tools::connectDatabase();
$data = array();
$result = mysqli_query($conn, "SELECT RoleID,RoleName FROM Roles");
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
echo json_encode($data);
}
function addEvaluation($ID, $name)
{
$conn = tools::connectDatabase();
try {
mysqli_query($conn, "INSERT INTO EvaluationProjects(RP_ID,ProjectName) VALUES({$ID},{$name})");
} catch (Exception $e) {
echo $e->getMessage();
}
echo "success";
}
function selectEmployee($sql)
{
$conn = tools::connectDatabase();
$data = array();
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
echo json_encode($data);
}
function setModifyEmployee($userID, $employeeName, $sex, $phone, $birthDay, $degreeID, $departmentID, $title, $salary, $employeeTypeID, $hireDate)
{
$conn = tools::connectDatabase();
try {
mysqli_query($conn, "UPDATE Employees SET EmployeeName='{$employeeName}',SEX='{$sex}',Phone='{$phone}',BirthDay='{$birthDay}',DegreeID='{$degreeID}',DepartmentID='{$departmentID}',Title='{$title}',Salary='{$salary}',EmployeeTypeID='{$employeeTypeID}',HireDate='{$hireDate}' WHERE EmployeeID='{$userID}' ");
} catch (Exception $e) {
echo $e->getMessage();
}
echo "success";
}
function addEmployee($name, $sex, $phone, $birthDay, $degreeID, $departmentID, $title, $salary, $employeeTypeID, $hireDate)
{
$conn = tools::connectDatabase();
$ID = tools::getMaxEmployeeID() + 1;
try {
mysqli_query($conn, "INSERT INTO Employees(EmployeeID,EmployeeName,Sex,BirthDay,Phone,DegreeID,HireDate,EmployeeTypeID,DepartmentID,Title,Salary,ManagerID) VALUES ('{$ID}','{$name}','{$sex}','{$birthDay}','{$phone}','{$degreeID}','{$hireDate}','{$employeeTypeID}','{$departmentID}','{$title}','{$salary}','53130301'); ");
} catch (Exception $e) {
echo $e->getMessage();
}
echo "success";
}
function setAddTrain($type, $employee)
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT * FROM EmployeeTraining WHERE EmployeeID='{$employee}' AND TrainingID='{$type}'");
$row = mysqli_fetch_array($result);
if ($row) {
echo "failed";
} else {
mysqli_query($conn, "INSERT INTO EmployeeTraining(TrainingID,EmployeeID) VALUES('{$type}','{$employee}')");
echo "success";
}
}
function editAccount($userID, $oldPassword, $newPassword)
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT Password FROM Users WHERE EmployeeID='{$userID}'");
$row = mysqli_fetch_array($result);
if ($row['Password'] == $oldPassword) {
mysqli_query($conn, "UPDATE Users SET Password='******' WHERE EmployeeID='{$userID}' ");
echo "success";
} else {
echo "failed";
}
}
function setAddReward($userID, $date, $getResult)
{
$conn = tools::connectDatabase();
$maxID = tools::getMaxRPID() + 1;
$result = mysqli_query($conn, "SELECT * FROM RewardsAndPunishments WHERE EmployeeID='{$userID}' AND Date='{$date}'");
$row = mysqli_fetch_array($result);
if ($row) {
echo "failed";
} else {
mysqli_query($conn, "INSERT INTO RewardsAndPunishments(RP_ID,EmployeeID,Type,Date) VALUES('{$maxID}','{$userID}','{$getResult}','{$date}')");
echo "success";
}
}
function getAttendanceInformation($userID, $year, $month)
{
$conn = tools::connectDatabase();
$data = array();
$result = mysqli_query($conn, "SELECT * FROM Attendances WHERE EmployeeID='{$userID}'");
while ($row = mysqli_fetch_array($result)) {
$temp = explode('-', $row['Date']);
if ($temp[0] == $year && $temp[1] == $month) {
array_push($data, $row);
}
}
echo json_encode($data);
}
function addEvaluation($type, $employee, $date, $getResult)
{
$conn = tools::connectDatabase();
$maxID = tools::getMaxEvaluationID() + 1;
$result = mysqli_query($conn, "SELECT * FROM Evaluations WHERE EmployeeID='{$employee}' AND EvaluationProjectID='{$type}' AND Date='{$date}'");
$row = mysqli_fetch_array($result);
if ($row) {
echo "failed";
} else {
mysqli_query($conn, "INSERT INTO Evaluations(EvaluationID,EmployeeID,EvaluationProjectID,Date,Result) VALUES('{$maxID}','{$employee}','{$type}','{$date}','{$getResult}')");
echo "success";
}
}
function setEditAttendance($userID, $data)
{
$conn = tools::connectDatabase();
for ($i = 0; $i < count($data); $i++) {
try {
$date = $data[$i][0];
$status = $data[$i][1];
mysqli_query($conn, "UPDATE Attendances SET StatusID='{$status}' WHERE EmployeeID='{$userID}' AND Date='{$date}'");
} catch (Exception $e) {
echo $e->getMessage();
}
}
echo "success";
}
function setEditReward($RP_ID, $employeeID, $type, $date)
{
$conn = tools::connectDatabase();
for ($i = 0; $i < count($RP_ID); $i++) {
$RP = $RP_ID[$i];
$Employee = $employeeID[$i];
$Type = $type[$i];
$Date = $date[$i];
echo "{$RP} {$Employee} {$Type} {$Date} \n ";
echo "UPDATE RewardsAndPunishments SET Type={$Type},Date={$Date} WHERE RP_ID={$RP} \n";
mysqli_query($conn, "UPDATE RewardsAndPunishments SET Type={$type[$i]},Date='{$date[$i]}' WHERE RP_ID={$RP_ID[$i]})");
}
mysqli_query($conn, "UPDATE RewardsAndPunishments SET Type=4,Date='2015-11-26' WHERE RP_ID=9");
}
function setEvaluation($data)
{
$conn = tools::connectDatabase();
try {
for ($i = 0; $i < count($data); $i++) {
$projectName = $data[$i]['projectName'];
$projectID = $data[$i]['projectID'];
mysqli_query($conn, "UPDATE EvaluationProjects SET ProjectName='{$projectName}' WHERE EP_ID='{$projectID}'");
}
} catch (Exception $e) {
echo $e->getMessage();
}
echo "success";
}
function setEditAuthority($employeeID, $data)
{
$conn = tools::connectDatabase();
for ($i = 0; $i < count($data); $i++) {
if ($data[$i]['value'] == 1) {
$roleID = $data[$i]['roleID'];
$result = mysqli_query($conn, "SELECT * FROM UserRole WHERE EmployeeID='{$employeeID}' AND RoleID='{$roleID}'");
$row = mysqli_fetch_array($result);
if (!$row) {
mysqli_query($conn, "INSERT INTO UserRole(EmployeeID,RoleID) VALUES('{$employeeID}','{$roleID}')");
}
}
}
}
function checkLogin($userID, $userPassword)
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT Password from Users WHERE EmployeeID='{$userID}'");
$row = mysqli_fetch_array($result);
if ($row) {
if ($userPassword == $row['Password']) {
return 'login success';
} else {
return "password not match";
}
} else {
return "not exist the userName";
}
}
function findPassword($employeeID, $phone)
{
$conn = tools::connectDatabase();
$result = mysqli_query($conn, "SELECT Phone from Employees WHERE EmployeeID='{$employeeID}'");
$row = mysqli_fetch_array($result);
if ($row) {
if ($phone == $row['Phone']) {
echo 'best match';
} else {
echo "phone not match";
}
} else {
echo "not exist the employeeID";
}
}
function setEditRole($roleID, $authority)
{
$conn = tools::connectDatabase();
$roleAuthority = $authority[0];
$roleInfo = $authority[1];
$roleJob = $authority[2];
$roleAttendance = $authority[3];
$roleTraining = $authority[4];
$roleEvaluation = $authority[5];
$roleRP = $authority[6];
$roleRole = $authority[7];
$roleEncrypt = $authority[8];
mysqli_query($conn, "UPDATE Roles SET Auth_Authority='{$roleAuthority}',Auth_Info='{$roleInfo}',Auth_Job='{$roleJob}',Auth_Attendance='{$roleAttendance}',Auth_Training='{$roleTraining}',Auth_Evaluation='{$roleEvaluation}',Auth_RP='{$roleRP}',Auth_Role='{$roleRole}',Auth_Encrypt='{$roleEncrypt}' WHERE RoleID={$roleID}");
echo "success";
}
function getSelectEvaluation($year, $month)
{
$conn = tools::connectDatabase();
$data = array();
$result = mysqli_query($conn, "SELECT * FROM Evaluations");
while ($row = mysqli_fetch_array($result)) {
$temp = explode('-', $row['Date']);
if ($year == $temp[0] && $month == $temp[1]) {
array_push($data, $row);
}
}
$data1 = array();
$result1 = mysqli_query($conn, "SELECT * FROM EvaluationProjects");
while ($row1 = mysqli_fetch_array($result1)) {
array_push($data1, $row1);
}
$data2 = array();
$result2 = mysqli_query($conn, "SELECT * FROM Employees");
while ($row2 = mysqli_fetch_array($result2)) {
array_push($data2, $row2);
}
for ($i = 0; $i < count($data); $i++) {
for ($x = 0; $x < count($data1); $x++) {
$temp1 = $data[$i]['EvaluationProjectID'];
$temp2 = $data1[$x]['EP_ID'];
if ($temp1 == $temp2) {
$data[$i]['projectName'] = $data1[$x]['ProjectName'];
}
}
}
for ($i = 0; $i < count($data); $i++) {
for ($x = 0; $x < count($data2); $x++) {
$temp1 = $data[$i]['EmployeeID'];
$temp2 = $data2[$x]['EmployeeID'];
if ($temp1 == $temp2) {
$data[$i]['employeeName'] = $data2[$x]['EmployeeName'];
}
}
}
echo json_encode($data);
}
function addAttendance($userID, $date, $status)
{
$conn = tools::connectDatabase();
$check = 'bad';
$data = array();
$result = mysqli_query($conn, "SELECT * FROM Attendances WHERE EmployeeID={$userID}");
while ($row = mysqli_fetch_array($result)) {
array_push($data, $row['Date']);
}
for ($i = 0; $i < count($data); $i++) {
if ($data[$i] == $date) {
$check = "good";
}
}
if ($check == "good") {
echo "failed";
} else {
mysqli_query($conn, "INSERT INTO Attendances(EmployeeID,StatusID,Date) VALUES('{$userID}','{$status}','{$date}') ");
echo "success";
}
}
function checkSelectRole($roleName)
{
$data = array();
$data1 = array();
$data2 = array();
$conn = tools::connectDatabase();
$result1 = mysqli_query($conn, "SELECT * FROM Roles WHERE RoleID='{$roleName}' ");
$row1 = mysqli_fetch_array($result1);
array_push($data, $row1);
$result2 = mysqli_query($conn, "SELECT EmployeeID FROM UserRole WHERE RoleID='{$roleName}'");
while ($row2 = mysqli_fetch_array($result2)) {
array_push($data1, $row2['EmployeeID']);
}
for ($i = 0; $i < count($data1); $i++) {
$employeeID = $data1[$i];
$result3 = mysqli_query($conn, "SELECT * FROM Employees WHERE EmployeeID='{$employeeID}'");
$row3 = mysqli_fetch_array($result3);
array_push($data2, $row3);
}
array_push($data, $data2);
echo json_encode($data);
}
