ID); $r = oci_execute($sqlarr); oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW); if ($data->STAGE == $rows_main[0]["APPROVAL_POS"]) { //Approve or Reject $sql = "UPDATE ytycustom.ple_approval_stages SET action = :ACT, action_date = SYSDATE WHERE header_id = :ID AND stage_pos = :STAGE_POS AND approver = :APPROVER AND action IS NULL"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':ACT', $data->ACT); oci_bind_by_name($sqlarr, ':STAGE_POS', $data->STAGE); oci_bind_by_name($sqlarr, ':APPROVER', $data->USERID); oci_bind_by_name($sqlarr, ':ID', $data->ID); $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr); $errorMsg = json_encode($e['message']); goto finalStep; } //Remove all other records with the same stage and id if ($data->STAGE != 5) { $sql = "DELETE ytycustom.ple_approval_stages WHERE header_id = :ID AND stage_pos = :STAGE_POS AND action IS NULL"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':STAGE_POS', $data->STAGE); oci_bind_by_name($sqlarr, ':ID', $data->ID); $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr); $errorMsg = json_encode($e['message']); goto finalStep; } } if ($data->ACT == "APPROVED") { $sql = "UPDATE ytycustom.ple_header SET stage_pos = case when stage_pos = 5 then 5 else stage_pos+1 end, modified_date = sysdate WHERE ID = :ID"; } else if ($data->ACT == "REJECTED") { $sql = "UPDATE ytycustom.ple_header SET stage_pos = -1, modified_date = sysdate WHERE ID = :ID"; } $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':ID', $data->ID); $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr); $errorMsg = json_encode($e['message']); goto finalStep; } //Current Approver $sql = "SELECT employee_name FROM production.employee_master WHERE emp_id = :emp_id"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':emp_id', $data->USERID); $r = oci_execute($sqlarr); oci_fetch_all($sqlarr, $rows_current_apprv, null, null, OCI_FETCHSTATEMENT_BY_ROW); //Email $sql = "SELECT distinct a.approver, b.email_id FROM ytycustom.ple_approval_stages a, production.employee_master b WHERE a.approver = b.emp_id AND a.header_id = :ID"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':ID', $data->ID); $r = oci_execute($sqlarr); oci_fetch_all($sqlarr, $rows_approver, null, null, OCI_FETCHSTATEMENT_BY_ROW); $mail = new PHPMailer(true); try { //Server settings //$mail->SMTPDebug = SMTP::DEBUG_SERVER; $mail->isSMTP(); $mail->Host = 'email-smtp.us-east-1.amazonaws.com'; $mail->SMTPAuth = true; $mail->Username = 'AKIA4TM64HI4LPHXMK6L'; $mail->Password = 'BFAufGeMYsb8sgH5Ta5VaTR/0omvCn6Ev3qFp+5wG83+'; $mail->SMTPSecure = 'ssl'; //PHPMailer::ENCRYPTION_SMTPS; $mail->Port = 465; $mail->isHTML(true); $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow'); //Approver foreach ($rows_approver as $dt) { $mail->addAddress($dt["EMAIL_ID"]); } $mail->addAddress("bijaya.agrawalla@ytygroup.com.my"); // Request by AG $mail->addBcc("reyndo.frenliedo@ytygroup.com.my"); //Content $mail->Subject = 'PLE request has been '.$data->ACT.' on Stage '.$data->STAGE.' (ID: '.$data->ID.')'; $mail->Body = '

Dear All,

A PLE request for customer '.$rows_main[0]["CUSTOMER"].' has been '.$data->ACT.' by '.$rows_current_apprv[0]["EMPLOYEE_NAME"].' on Stage '.$data->STAGE.' with the detail below:

SKU '.$rows_main[0]["SKU"].'
Colour '.$rows_main[0]["GLOVE_COLOUR"].'
Type '.$rows_main[0]["GLOVE_TYPE"].'
Target Weight (g) '.$rows_main[0]["TARGET_WEIGHT"].'
Length (mm) '.$rows_main[0]["GLOVE_LENGTH"].'
Size '.$rows_main[0]["GLOVE_SIZE"].'
Surface / Textured '.$rows_main[0]["GLOVE_SURFACE"].'
Donning '.$rows_main[0]["GLOVE_DONNING"].'
Special Features '.$rows_main[0]["SPECIAL_FEATURES"].'

Thanks.

'; $sendStatus = $mail->send(); } catch (Exception $e) { $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}"; } //Next Stage Email if ($data->ACT == "APPROVED" && $data->STAGE != 5) { $sql = "SELECT DISTINCT b.email_id FROM ytycustom.ple_access a, production.employee_master b WHERE a.emp_id = b.emp_id AND a.access_stage != 0 AND a.access_stage <= :stage"; $nextStg = $data->STAGE+1; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':stage', $nextStg); $r = oci_execute($sqlarr); oci_fetch_all($sqlarr, $rows_next, null, null, OCI_FETCHSTATEMENT_BY_ROW); $mail = new PHPMailer(true); try { //Server settings //$mail->SMTPDebug = SMTP::DEBUG_SERVER; $mail->isSMTP(); $mail->Host = 'email-smtp.us-east-1.amazonaws.com'; $mail->SMTPAuth = true; $mail->Username = 'AKIA4TM64HI4LPHXMK6L'; $mail->Password = 'BFAufGeMYsb8sgH5Ta5VaTR/0omvCn6Ev3qFp+5wG83+'; $mail->SMTPSecure = 'ssl'; //PHPMailer::ENCRYPTION_SMTPS; $mail->Port = 465; $mail->isHTML(true); $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow'); //Approver foreach ($rows_next as $dt) { $mail->addAddress($dt["EMAIL_ID"]); } $mail->addBcc("reyndo.frenliedo@ytygroup.com.my"); //Content $mail->Subject = 'PLE request has reached Stage '.($data->STAGE+1).' (ID: '.$data->ID.')'; $mail->Body = '

Dear All,

Please be notified a PLE request for customer '.$rows_main[0]["CUSTOMER"].' now in Stage '.($data->STAGE+1).' with the detail below:

SKU '.$rows_main[0]["SKU"].'
Colour '.$rows_main[0]["GLOVE_COLOUR"].'
Type '.$rows_main[0]["GLOVE_TYPE"].'
Target Weight (g) '.$rows_main[0]["TARGET_WEIGHT"].'
Length (mm) '.$rows_main[0]["GLOVE_LENGTH"].'
Size '.$rows_main[0]["GLOVE_SIZE"].'
Surface / Textured '.$rows_main[0]["GLOVE_SURFACE"].'
Donning '.$rows_main[0]["GLOVE_DONNING"].'
Special Features '.$rows_main[0]["SPECIAL_FEATURES"].'

Please click this link to view the PLE.

Thanks.

'; $sendStatus = $mail->send(); } catch (Exception $e) { $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}"; } } else { //Finally Approved $sql = "SELECT COUNT(1) ctr_master FROM ytycustom.ple_approval_master WHERE stage_pos = '5'"; $sqlarr = oci_parse($dbcon, $sql); $r = oci_execute($sqlarr); while ($row = oci_fetch_array($sqlarr)) { $masterApprv = $row['CTR_MASTER']; } $sql = "SELECT COUNT(1) CTR FROM ytycustom.ple_approval_stages WHERE header_id = :ID AND stage_pos = 5 AND action = 'APPROVED'"; $sqlarr = oci_parse($dbcon, $sql); oci_bind_by_name($sqlarr, ':ID', $data->ID); $r = oci_execute($sqlarr); while ($row = oci_fetch_array($sqlarr)) { $doneApprv = $row['CTR']; } if ($masterApprv != "0" && $doneApprv == $masterApprv) { $sql = "SELECT DISTINCT b.email_id FROM ytycustom.ple_access a, production.employee_master b WHERE a.emp_id = b.emp_id --AND a.access_stage != 0 AND a.access_stage <= 5 UNION SELECT DISTINCT b.email_id FROM ytycustom.ple_approval_stages a, production.employee_master b WHERE a.approver = b.emp_id AND a.header_id = :ID"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':ID', $data->ID); $r = oci_execute($sqlarr); oci_fetch_all($sqlarr, $rows_all, null, null, OCI_FETCHSTATEMENT_BY_ROW); $mail = new PHPMailer(true); try { //Server settings //$mail->SMTPDebug = SMTP::DEBUG_SERVER; $mail->isSMTP(); $mail->Host = 'email-smtp.us-east-1.amazonaws.com'; $mail->SMTPAuth = true; $mail->Username = 'AKIA4TM64HI4LPHXMK6L'; $mail->Password = 'BFAufGeMYsb8sgH5Ta5VaTR/0omvCn6Ev3qFp+5wG83+'; $mail->SMTPSecure = 'ssl'; //PHPMailer::ENCRYPTION_SMTPS; $mail->Port = 465; $mail->isHTML(true); $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow'); //Approver foreach ($rows_all as $dt) { $mail->addAddress($dt["EMAIL_ID"]); } $mail->addBcc("reyndo.frenliedo@ytygroup.com.my"); //Content $mail->Subject = 'PLE request is finally Approved on all stages (ID: '.$data->ID.')'; $mail->Body = '

Dear All,

Please be notified a PLE request for customer '.$rows_main[0]["CUSTOMER"].' is finally Approved on all stages with the detail below:

SKU '.$rows_main[0]["SKU"].'
Colour '.$rows_main[0]["GLOVE_COLOUR"].'
Type '.$rows_main[0]["GLOVE_TYPE"].'
Target Weight (g) '.$rows_main[0]["TARGET_WEIGHT"].'
Length (mm) '.$rows_main[0]["GLOVE_LENGTH"].'
Size '.$rows_main[0]["GLOVE_SIZE"].'
Surface / Textured '.$rows_main[0]["GLOVE_SURFACE"].'
Donning '.$rows_main[0]["GLOVE_DONNING"].'
Special Features '.$rows_main[0]["SPECIAL_FEATURES"].'

Thanks.

'; $sendStatus = $mail->send(); } catch (Exception $e) { $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}"; } } } } else { $errorMsg = "The action already taken for this PLE."; } } finalStep: $result = []; if ($errorMsg == "") { oci_commit($dbcon); $result[] = [ "message" => "Success", "id" => $newid ]; } else { oci_rollback($dbcon); $result[] = [ "message" => $errorMsg, "id" => "" ]; } print json_encode($result); ?>