function func_add_update_addndetails($R, $EmployeeID)
{
$DetailID = 0;
#################------------------------- BEGIN INSERT LEGAL DETAIL RECORD -----------------------------####################
$Query = "SELECT DetailID FROM vcc_addndetails WHERE EmployeeID=" . $EmployeeID . ";";
eqi($Query, $rs);
$Output = mfai($rs);
if (intval($Output['DetailID']) <= 0 || $Output['DetailID'] == NULL) {
$SQLQuery = "INSERT INTO vcc_addndetails(\n\t\t`ADDropOffDate`,\n\t\t`ADShippingVendor`,\n\t\t`ADTrackingNumber`,\n\t\t`ADDateDelivered`,\n\t\t`ADDeliveredTime`,\n\t\t`ADUndeliverable`,\n\t\t`ADReason`,\n\t\t`ADFU_QTNP`,\n\t\t`ADFU_QTE`,\n\t\t`ADFU_SOC`,\n\t\t`ADFU_QTNP_Date`,\n\t\t`ADFU_QTE_Date`,\n\t\t`ADFU_SOC_Date`,\n\t\t`EmployeeID`,\n\t\t`CreatedDate`) ";
$SQLQuery .= "VALUES(STR_TO_DATE('{$R['aDropOffDate']}','%m/%d/%Y'),'" . htmlentities($R[aShippingVendor]) . "','" . htmlentities($R[aTrackingNumber]) . "'" . ",STR_TO_DATE('{$R['aDateDelievered']}','%m/%d/%Y'),'{$R['aTimeDelievered']}','{$R['aUnbeliverable']}'" . ",'" . htmlentities($R[aReason]) . "','{$R['aquesNP']}','{$R['aQuesEmp']}','{$R['aQA']}'" . ",STR_TO_DATE('{$R['aquesNPDate']}','%m/%d/%Y'),STR_TO_DATE('{$R['aQuesEmpDate']}','%m/%d/%Y')" . ",STR_TO_DATE('{$R['aQADate']}','%m/%d/%Y'),{$EmployeeID},NOW());";
$DetailID = eii($SQLQuery);
} else {
if (intval($Output['DetailID']) > 0) {
$UPDATESQLCOMMAND = "UPDATE vcc_addndetails SET\n\t\t`ADDropOffDate`=STR_TO_DATE('{$R['aDropOffDate']}','%m/%d/%Y'),\n\t\t`ADShippingVendor`='" . htmlentities($R[aShippingVendor]) . "',\n\t\t`ADTrackingNumber`='" . htmlentities($R[aTrackingNumber]) . "',\n\t\t`ADDateDelivered`=STR_TO_DATE('{$R['aDateDelievered']}','%m/%d/%Y'),\n\t\t`ADDeliveredTime`='{$R['aTimeDelievered']}',\n\t\t`ADUndeliverable`='{$R['aUnbeliverable']}',\n\t\t`ADReason`='" . htmlentities($R[aReason]) . "',\n\t\t`ADFU_QTNP`='{$R['aquesNP']}',\n\t\t`ADFU_QTE`='{$R['aQuesEmp']}',\n\t\t`ADFU_SOC`='{$R['aQA']}',\n\t\t`ADFU_QTNP_Date`=STR_TO_DATE('{$R['aquesNPDate']}','%m/%d/%Y'),\n\t\t`ADFU_QTE_Date`=STR_TO_DATE('{$R['aQuesEmpDate']}','%m/%d/%Y'),\n\t\t`ADFU_SOC_Date`=STR_TO_DATE('{$R['aQADate']}','%m/%d/%Y'),\n\t\t`EmployeeID`= {$EmployeeID},\n `LastUpdatedDate` = NOW()\n WHERE DetailID = {$Output['DetailID']};";
$DetailID = eqi($UPDATESQLCOMMAND, $rs);
}
}
if ($DetailID > 0 and $EmployeeID > 0) {
if ($R[aQA] == true || $R[aQA] == 1 and $R[aQADate] != "" and $R[aQADate] <= date('m/d/Y')) {
$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 'QA Completed.',{$EmployeeID},NOW());";
eii($noteQuery);
}
}
return $DetailID;
}
function get_non_profit_list($table, $empLocation, $sel_id, &$option, $con = "", $show_select = 1, $select_option = '----- BLANK ----', $optimalDistance = 50.0)
{
$R = 6371;
$ss = 0;
$theta1 = deg2rad($empLocation['ILatitude']);
$SQL = "SELECT * FROM {$table} {$con} ";
eqi($SQL, $rs);
while ($data = mfai($rs)) {
$theta2 = deg2rad($data['latitude']);
$changeTheta = deg2rad($data['latitude'] - $empLocation['ILatitude']);
$changeDelta = deg2rad($data['longitude'] - $empLocation['ILongitude']);
$a = sin($changeTheta / 2) * sin($changeTheta / 2) + cos($theta1) * cos($theta2) * sin($changeTheta / 2) * sin($changeDelta / 2);
$c = 2 * atan2(sqrt($a), sqrt(1 - $a));
$distance = $R * $c;
if (round($distance, 2) > $optimalDistance) {
if ($data[NonProfitID] != $sel_id) {
continue;
}
}
if ($data[NonProfitID] == $sel_id) {
$sel = "selected";
$ss = 1;
}
$option .= "<option value=\"{$data['NonProfitID']}\" {$sel}>" . $data[NonProfit] . " - " . round($distance, 2) . "</option>";
$sel = "";
}
if ($ss == 0 and $show_select == 1) {
$option = "<option value=\"0\" selected>" . $select_option . "</option>" . $option;
}
}
function export_report($msg = "", $error_msg = '')
{
global $SERVER_PATH, $SignIN;
$R = DIN_ALL($_REQUEST);
if (intval($R['preference']) == 1) {
$filename = "AssignmentReport_" . date('Ymd') . ".xls";
} else {
$filename = "PlacementReport_" . date('Ymd') . ".xls";
}
header("Content-Disposition: attachment; filename=\"{$filename}\"");
header("Content-Type: application/vnd.ms-excel");
if (($_SESSION['UserID'] <= 0 || $_SESSION['UserID'] == "") && $_SESSION['http_agent'] != $_SERVER['HTTP_USER_AGENT']) {
header("Location:" . $SignIN);
die;
} else {
if (intval($R['preference']) == 1) {
$SQL = "SELECT VCCCaseNumber,CONCAT(IFirstName,' ',ILastName) AS EmployeeName,DATE_FORMAT(InjuryDate,'%m/%d/%Y') AS InjuryDate,\n\t\t\t\tDATE_FORMAT(NPEarliestStartDate,'%m/%d/%Y') AS NPStartDate,GIEmployer AS EmployerName,\n\t\t\t\tDATE_FORMAT(PreCallDate,'%m/%d/%Y') AS PreCallDate,DATE_FORMAT(FollowUpDate,'%m/%d/%Y') AS FollowUpDate\n\t\t\t\tFROM vcc_iemployee VCE LEFT JOIN vcc_employer VCEP ON VCE.IEmployeeID = VCEP.EmployeeID\n\t\t\t\tLEFT JOIN vcc_initials VCI ON VCI.InitialID = VCE.PersonAssignedID\n\t\t\t\tWHERE CloseDate IS NOT NULL AND CloseDate <> STR_TO_DATE(now(),'%Y-%m-%d') \n\t\t\t\tAND CloseDate <> STR_TO_DATE('1970-01-01','%Y-%m-%d') AND VCI.LoginID = " . $R['userID'] . ";";
} else {
$SQL = "SELECT VCCCaseNumber,CONCAT(IFirstName,' ',ILastName) AS EmployeeName,GIEmployer AS EmployerName,\n\t\t\t\tICity AS EmployeeCity,IZip AS EmployeeZipCode,VS.State AS EmployeeState\n FROM vcc_iemployee VCE LEFT JOIN vcc_employer VCEP ON VCE.IEmployeeID = VCEP.EmployeeID\n\t\t\t\tLEFT JOIN vcc_initials VCI ON VCI.InitialID = VCE.PersonAssignedID \n\t\t\t\tLEFT JOIN vcc_states VS ON VS.StateID = VCE.IStateID \n\t\t\t\tWHERE CloseDate IS NOT NULL AND CloseDate <> STR_TO_DATE(now(),'%Y-%m-%d') \n\t\t\t\tAND CloseDate <> STR_TO_DATE('1970-01-01','%Y-%m-%d') AND VCI.LoginID = " . $R['userID'] . ";";
}
eqi($SQL, $rs);
while ($data = mfai($rs)) {
if (!$flag) {
// display field/column names as first row
echo implode("\t", array_keys($data)) . "\r\n";
$flag = true;
}
// escape tab characters
$str = preg_replace("/\t/", "\\t", $str);
// escape new lines
$str = preg_replace("/\r?\n/", "\\n", $str);
array_walk($data, 'cleanData');
echo implode("\t", array_values($data)) . "\r\n";
}
}
exit;
}
function do_advancedSearch($msg = "", $error_msg = "")
{
global $SERVER_PATH, $SignIN;
$R = DIN_ALL($_REQUEST);
$String = "";
$total = 0;
if (($_SESSION['UserID'] <= 0 || $_SESSION['UserID'] == "") && $_SESSION['http_agent'] != $_SERVER['HTTP_USER_AGENT']) {
header("Location:" . $SignIN);
die;
}
$Condition = search_condition($R[SearchID]);
if ($R[search] != NULL || $R[search] != "") {
$SQLQuery = "SELECT EMP.IEmployeeID,EMPR.EmployerID,EMP.VCCCaseNumber,CONCAT(EMP.IFirstName,' ',EMP.ILastName) AS EmployeeName\n\t\t\t\t\t,EMP.IEmailAddress,EMPR.GIEmployer \n\t\t\t\t\tFROM vcc_iemployee EMP \n\t\t\t\t\tLEFT JOIN vcc_employer EMPR ON EMP.IEmployeeID = EMPR.EmployeeID \n\t\t\t\t\tLEFT JOIN vcc_npdetails NP ON EMP.IEmployeeID = NP.EmployeeID \n\t\t\t\t\tWHERE 1 {$Condition}\n\t\t\t\t\tORDER BY EMP.IEmployeeID;";
//echo $SQLQuery;
$total = eqi($SQLQuery, $rs);
if (intval($total) > 0) {
while ($data = mfai($rs)) {
if (intval($_SESSION[IsAdmin]) == 1) {
$String .= '<div class="row-fluid portfolio-block">
<div class="span3 portfolio-text">
<img src="assets/img/profile/portfolio/logo_azteca.jpg" alt="" />
<div class="portfolio-text-info">
<h4>Employee Name</h4>
<p>' . $data[EmployeeName] . '</p>
</div>
</div>
<div class="span5">
<div class="portfolio-info">
VCC CASE NUMBER
<span>' . $data[VCCCaseNumber] . '</span>
</div>
<div class="portfolio-info">
EMPLOYER NAME
<p>' . $data[GIEmployer] . '</p>
</div>
<div class="portfolio-info">
EMPLOYEE EMAIL ADDRESS
<p>' . $data[IEmailAddress] . '</p>
</div>
</div>
<div class="span2 portfolio-btn">
<a href="' . $SERVER_PATH . 'View-Employee-Detail/' . $data[IEmployeeID] . '" class="btn bigicn-only" ><span>View/Edit</span></a>
</div>
<div class="span2 portfolio-btn">
<a onClick="deleteRecord(' . $data[IEmployeeID] . ',2);" class="btn bigicn-only"><span>Delete Record</span></a>
</div>
</div>';
} else {
$String .= '<div class="row-fluid portfolio-block">
<div class="span3 portfolio-text">
<img src="assets/img/profile/portfolio/logo_azteca.jpg" alt="" />
<div class="portfolio-text-info">
<h4>Employee Name</h4>
<p>' . $data[EmployeeName] . '</p>
</div>
</div>
<div class="span7">
<div class="portfolio-info">
VCC CASE NUMBER
<span>' . $data[VCCCaseNumber] . '</span>
</div>
<div class="portfolio-info">
EMPLOYER NAME
<p>' . $data[GIEmployer] . '</p>
</div>
<div class="portfolio-info">
EMPLOYEE EMAIL ADDRESS
<p>' . $data[IEmailAddress] . '</p>
</div>
</div>
<div class="span2 portfolio-btn">
<a href="' . $SERVER_PATH . 'View-Employee-Detail/' . $data[IEmployeeID] . '" class="btn bigicn-only" ><span>View/Edit</span></a>
</div>
</div>';
}
}
} else {
$String = '<div class="span10 portfolio-text">
<div class="portfolio-text-info">
<h4>No Record Found</h4>
</div>
</div>';
}
}
echo $String;
}
