function func_add_update_employee($R, $EmployeeID, $IsAdmin)
{
if (is_array($R[iLanguagesKnown])) {
$LanguageSpoken = implode(",", $R[iLanguagesKnown]);
} else {
$LanguageSpoken = "";
}
$LonAndLat = curlGeoInformation($R[iAddress], $R[iCity], $R[iState]);
get_row_con_info("vcc_iemployee", "WHERE IEmployeeID=" . $EmployeeID, "CloseDate", $Employee);
if (intval($EmployeeID) == 0 || $EmployeeID == "") {
$SQL = "SELECT MAX(SUBSTRING(VCCCaseNumber,4,LENGTH(VCCCaseNumber)))+1 AS VCCCaseNumber FROM vcc_iemployee;";
eqi($SQL, $rs);
$OUTPUT = mfai($rs);
if ($OUTPUT['VCCCaseNumber'] != NULL) {
$VCCCaseNumber = strval(date('y') . "-" . strval(str_pad($OUTPUT['VCCCaseNumber'], 5, '0', STR_PAD_LEFT)));
} else {
$VCCCaseNumber = strval(date('y') . "-00001");
}
#########################------------------------- BEGIN INSERT INJURED EMPLOYEE DETAIL RECORD -----------------------------########################
$SQLQuery = "INSERT INTO vcc_iemployee(\n `VCCCaseNumber`,\n `VCCEmployeeCode`,\n `IFirstName`,\n `ILastName`,\n\t\t`IAppartmentNumber`,\n `IAddress`,\n `IEmailAddress`,\n `IAge`,\n `IGender`,\n `IOccupation`,\n `IPhone`,\n `IMobile`,\n `ICity`,\n `IStateID`,\n `IZip`,\n `LanguagesSpoken`,\n `NPEarliestStartDate`,\n `InjuryDate`,\n `InjuryType`,\n `ClaimNumber`,\n `StateJuridiction`,\n `AWWRate`,\n `CompRate`,\n `ALDRateHourly`,\n `ALDRateWeekly`,\n `RestrictionDate`,\n `IPhysician`,\n `MedRestriction`,\n `NextDAppointment`,\n `PIWHoursWorked`,\n `TransportationIssue`,\n `TransIssueType`,\n `RefreralDate`,\n `ReOpenDate`,\n `PersonAssignedID`,\n `DateAssigned`,\n `PreCallDate`,\n `FollowUpDate`,\n `ILatitude`,\n `ILongitude`,\n `CloseDate`,\n `CreatedDate`)";
$SQLQuery .= " VALUES('{$VCCCaseNumber}','{$R['VCCEmployeeCode']}','" . htmlentities(ucfirst($R[iFirstName])) . "','" . htmlentities(ucfirst($R[iLastName])) . "'" . ",'" . htmlentities(ucfirst($R[iAprtNum])) . "','" . htmlentities(ucfirst($R[iAddress])) . "','{$R['iEmail']}'" . ",'{$R['iAge']}','{$R['iGender']}','{$R['iOccupation']}','{$R['iPhone']}','{$R['iMobile']}','" . ucfirst($R[iCity]) . "'" . ",'{$R['iState']}','{$R['iZip']}','{$LanguageSpoken}'" . ",STR_TO_DATE('{$R['npEarliestDate']}','%m/%d/%Y'),STR_TO_DATE('{$R['iDateOfInjury']}','%m/%d/%Y')" . ",'" . htmlentities($R[iInjuryType]) . "','{$R['iClaimNumber']}','{$R['iStateJurd']}','{$R['npAWWRate']}'" . ",'{$R['npCompRate']}','{$R['npHourRateALD']}','{$R['npWeeklyALDRate']}',STR_TO_DATE('{$R['npDateOfRestriction']}','%m/%d/%Y')" . ",'{$R['npByPhysician']}','{$R['npMedicalRest']}',STR_TO_DATE('{$R['npNextDrApptDate']}','%m/%d/%Y')" . ",'{$R['iPreInjWeeklyHrsWorked']}','{$R['npTransIssues']}','{$R['npTransIssueType']}'" . ",STR_TO_DATE('{$R['npReferralDate']}','%m/%d/%Y'),STR_TO_DATE('{$R['npReOpenDate']}','%m/%d/%Y')" . ",'{$R['npPersonAssigned']}',NOW(),STR_TO_DATE('{$R['npPreCallDate']}','%m/%d/%Y')" . ",STR_TO_DATE('{$R['npFollowupDate']}','%m/%d/%Y'),'" . $LonAndLat[0]['lat'] . "','" . $LonAndLat[0]['lon'] . "'," . "STR_TO_DATE('{$R['npCloseDate']}','%m/%d/%Y'),NOW());";
$EmployeeID = eii($SQLQuery);
if (intval($EmployeeID) > 0) {
if (!intval($R[npPersonAssigned]) > 0) {
$R[npPersonAssigned] = 14;
}
$noteQuery = "INSERT INTO vcc_notes(\n\t\t\t\t\t\t`InitialID`,\n\t\t\t\t\t\t`NoteDescription`,\n\t\t\t\t\t\t`EmployeeID`,\n\t\t\t\t\t\t`IsAdmin`,\n\t\t\t\t\t\t`CreatedDate`)\n\t\t\t\t\t\t VALUES(\n\t\t\t\t\t\t(SELECT InitialID FROM vcc_initials WHERE LoginID={$_SESSION['UserID']}),'Employee has beed added',{$EmployeeID},{$IsAdmin},NOW());";
eii($noteQuery);
$subject = "New VCC Referral VCC Case # : " . $VCCCaseNumber;
$message = "You have been assigned a new referral VCC Case # : " . $VCCCaseNumber . " for Employee '" . htmlentities(ucfirst($R[iLastName])) . "','" . htmlentities(ucfirst($R[iFirstName])) . "'";
send_mail_new("VCC MANAGEMENT", "*****@*****.**", "VARNER CLAIMS CONSULTING", "*****@*****.**", $subject, $message, "", "", "", "*****@*****.**");
}
} else {
if (intval($EmployeeID) > 0) {
$UPDATESQLCOMMAND = "UPDATE vcc_iemployee SET\n `VCCCaseNumber` = '{$R['VCCCaseNumber']}',\n `VCCEmployeeCode` = '{$R['VCCEmployeeCode']}',\n `IFirstName` = '" . htmlentities(ucfirst($R[iFirstName])) . "',\n `ILastName` = '" . htmlentities(ucfirst($R[iLastName])) . "',\n\t\t`IAppartmentNumber` = '" . htmlentities(ucfirst($R[iAprtNum])) . "',\n `IAddress`='" . htmlentities(ucfirst($R[iAddress])) . "',\n `IEmailAddress`= '{$R['iEmail']}',\n `IAge` = '{$R['iAge']}',\n `IGender` = '{$R['iGender']}',\n `IOccupation` = '{$R['iOccupation']}',\n `IPhone` = '{$R['iPhone']}',\n `IMobile` = '{$R['iMobile']}',\n `ICity` = '" . htmlentities(ucfirst($R[iCity])) . "',\n `IStateID` = '{$R['iState']}',\n `IZip` = '{$R['iZip']}',\n `LanguagesSpoken` = '{$LanguageSpoken}',\n `NPEarliestStartDate` = STR_TO_DATE('{$R['npEarliestDate']}','%m/%d/%Y'),\n `InjuryDate` = STR_TO_DATE('{$R['iDateOfInjury']}','%m/%d/%Y'),\n `InjuryType` = '" . htmlentities($R[iInjuryType]) . "',\n `ClaimNumber` = '{$R['iClaimNumber']}',\n `StateJuridiction` = '{$R['iStateJurd']}',\n `AWWRate` = '{$R['npAWWRate']}',\n `CompRate` = '{$R['npCompRate']}',\n `ALDRateHourly` = '{$R['npHourRateALD']}',\n `ALDRateWeekly` = '{$R['npWeeklyALDRate']}',\n `RestrictionDate` = STR_TO_DATE('{$R['npDateOfRestriction']}','%m/%d/%Y'),\n `IPhysician` = '{$R['npByPhysician']}',\n `MedRestriction` = '{$R['npMedicalRest']}',\n `NextDAppointment` = STR_TO_DATE('{$R['npNextDrApptDate']}','%m/%d/%Y'),\n `PIWHoursWorked` = '{$R['iPreInjWeeklyHrsWorked']}',\n `TransportationIssue` = '{$R['npTransIssues']}',\n `TransIssueType` = '{$R['npTransIssueType']}',\n `RefreralDate` = STR_TO_DATE('{$R['npReferralDate']}','%m/%d/%Y'),\n `ReOpenDate` = STR_TO_DATE('{$R['npReOpenDate']}','%m/%d/%Y'),\n `PersonAssignedID` = '{$R['npPersonAssigned']}',\n `DateAssigned` = NOW(),\n `PreCallDate` = STR_TO_DATE('{$R['npPreCallDate']}','%m/%d/%Y'),\n `FollowUpDate` = STR_TO_DATE('{$R['npFollowupDate']}','%m/%d/%Y'),\n `ILatitude`='" . $LonAndLat[0]['lat'] . "',\n `ILongitude`='" . $LonAndLat[0]['lon'] . "',\n `CloseDate` = STR_TO_DATE('{$R['npCloseDate']}','%m/%d/%Y'),\n `LastUpdatedDate` = NOW()\n WHERE IEmployeeID = {$EmployeeID};";
eqi($UPDATESQLCOMMAND, $rs);
}
}
if ($EmployeeID > 0) {
$EarlierDate = DateTime::createFromFormat('Y-m-d', $Employee[CloseDate]);
$LatestDate = DateTime::createFromFormat('m/d/Y', $R[npCloseDate]);
if ($R[npCloseDate] != "" and $EarlierDate < $LatestDate) {
$noteQuery = "INSERT INTO vcc_notes(\n\t\t\t\t\t\t`InitialID`,\n\t\t\t\t\t\t`NoteDescription`,\n\t\t\t\t\t\t`EmployeeID`,\n\t\t\t\t\t\t`CreatedDate`)\n\t\t\t\t\t\t VALUES(\n\t\t\t\t\t\t (SELECT InitialID FROM vcc_initials WHERE LoginID={$_SESSION['UserID']}),\n\t\t\t\t\t\t 'Case Closed.',{$EmployeeID},NOW());";
eii($noteQuery);
}
}
return $EmployeeID;
}
function update_password()
{
global $SERVER_PATH, $SiteName;
$R = DIN_ALL($_REQUEST);
$currentTime = time();
if (get_row_con_info("vcc_resetpassword", "WHERE UniqueKey='{$R['fp_key']}' AND CAST(ValidUpto AS UNSIGNED INT) > {$currentTime}", "", $data)) {
$pass_mail = $R[password];
$pass = md5($R[password]);
$SQL = "UPDATE vcc_users_login SET Password='******' WHERE LoginID='{$data['LoginID']}'";
eqi($SQL, $rs);
$subject = $SiteName . " Password Assistance";
$message = "<br>Your new password for login at " . $SERVER_PATH . " is listed below:<br>\n <br>Password: {$pass_mail} <br>\n <br>To login go to: <a href=" . $SERVER_PATH . "Login>{$SERVER_PATH}</a><br>";
$SQL = "UPDATE vcc_resetpassword SET ValidUpto=0 WHERE LoginID =" . $data[LoginID] . ";";
eqi($SQL, $rs);
get_row_con_info("vcc_users_login", "where LoginID='{$data['LoginID']}'", "", $login);
send_mail_new("{$login['Name']}", $login[EmailAddress], "", "", $subject, $message, "", "", "", "*****@*****.**");
header("Location:" . $SERVER_PATH . "Login");
} else {
header("Location:" . $SERVER_PATH . "Link-Expired");
}
}
function assign_mail()
{
global $SERVER_PATH, $SignIN;
$R = DIN_ALL($_REQUEST);
if (($_SESSION['UserID'] <= 0 || $_SESSION['UserID'] == "") && $_SESSION['http_agent'] != $_SERVER['HTTP_USER_AGENT']) {
header("Location:" . $SignIN);
die;
} else {
if (intval($R['PersonID']) > 0 && intval($R['EmployeeID']) > 0) {
$Query = "UPDATE vcc_iemployee SET PersonAssignedID = {$R['PersonID']},PresetContentID = {$R['PresetContentID']} WHERE IEmployeeID = " . $R['EmployeeID'];
eqi($Query, $rs);
if (get_row_con_info("vcc_initials VCI LEFT JOIN vcc_iemployee VCE ON VCE.PersonAssignedID = VCI.InitialID LEFT JOIN vcc_placement_status VPS ON VCE.PresetContentID = VPS.PlacementID", "WHERE VCE.IEmployeeID = {$R['EmployeeID']}", "VCCCaseNumber,Placement,IFirstName,ILastName,FullName,EmailAddress", $Initials)) {
$subject = "New VCC Referral VCC Case # : " . $Initials[VCCCaseNumber];
$message = "You have been assigned a new referral VCC Case # : " . $Initials[VCCCaseNumber] . " for Employee {$Initials['ILastName']},{$Initials['IFirstName']} with {$Initials['Placement']}";
send_mail_new($Initials[FullName], $Initials[EmailAddress], "VARNER CLAIMS CONSULTING", "*****@*****.**", $subject, $message, "", "", "", "*****@*****.**");
$sqlQuery = "INSERT INTO vcc_notes(\n `InitialID`,\n `NoteDescription`,\n `EmployeeID`,\n `CreatedDate`)\n VALUES(\n\t\t\t\t\t\t\t\t\t (SELECT InitialID FROM vcc_initials WHERE LoginID={$_SESSION['UserID']}),'{$Initials['EmailAddress']} has been assigned for VCC Case # : {$Initials['VCCCaseNumber']} with {$Initials['Placement']}',{$R['EmployeeID']},NOW());";
eii($sqlQuery);
echo "mail sent";
}
} else {
echo "select an appropriate value";
}
}
}
