ikan Uploader :
Directory : /var/www/html/chalkbank.com/cronjobs/internshipCron/
Upload File : |
| Current File : /var/www/html/chalkbank.com/cronjobs/internshipCron/internshipNewUpdateInsertCron_2202.php |
<?php
error_reporting(E_ALL);
date_default_timezone_set("Asia/Calcutta");
ini_set('display_errors', '0');
define('DB_SERVER', "admissions.cluster-custom-c8m5zlhotfbm.ap-south-1.rds.amazonaws.com");
define('DB_USERNAME', "shardezone_user");
define('DB_PASSWORD', "7se%;pr*/A24AyTw");
define('DB_DATABASE', "shardaac_ezone_2022_db");
define('COMPANY', "ShardaTech pvt ltd.");
//exit('Sorry Try Again!!!!!');
/* Connect to MySQL and select the database. */
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
if (!$conn) {
mysqli_connect_error();
}
$ADMITTERM = '2202';
//echo 'Try later...'; die;
// return false;
mysqli_set_charset($conn, "utf8");
$tbl_name = 'PS_S_PRD_INST_VW';
if(!empty($tbl_name))
{
$post = [
'username' => 'ATTEST',
'password' => 'TFsgt^I8',
//'col' => 'EMPLID',
'conditions' => serialize(array('ADMIT_TERM' => $ADMITTERM)),
'num_rows' => '100000',
'table' => $tbl_name
];
$resultsArray = '';
$resultsArray = json_decode(getAPIResponse($post));
echo 'Total Count =>'.count($resultsArray);
//echo '<br/>';
//echo count($resultsArray);
echo '<pre>';
//print_r($resultsArray);
//echo '</pre>'; die();
// Get Career List
$count=0;
$k=0;
$i=0;
$j=0;
$m=0;
$n=0;
$b=0;
$c=0;
$q=0;
$r=0;
$s=0;
$g=0;
$f=0;
$customArray = [];
$customArrayNew = [];
// Fetch the results of the query
$countRows = count($resultsArray);
//die;
try {
if($countRows>1){
foreach($resultsArray as $row){
//print_r($row->NAME_FORMAL); die;
$g++;
$custom = serialize($row);
$fullname = trim($row->NAME_FORMAL);
$system_id = trim($row->EMPLID);
$acad_group = trim($row->ACAD_GROUP);
$prog_dept = trim($row->SU_REP_PRG_DEPT);
$program_name = trim($row->SU_REP_PRG_DESCR);
$acad_career = trim($row->ACAD_CAREER);
$program_code = trim($row->SU_REP_PRG_CD);
$catalog_desc = trim($row->DESCR);
$catalog_nbr = trim($row->CATALOG_NBR);
$class_nbr = trim($row->CLASS_NBR);
$csr_id = trim($row->CRSE_ID);
$admit_term = trim($row->ADMIT_TERM);
$role = trim($row->INSTR_ROLE);
$faculty_id = trim($row->INSTRUCTOR_ID);
//echo $school_name = $val;
//echo '<br/>';
// Check Data
$d1 = "SELECT * from tbl_internships WHERE student_name = '".$fullname."' AND system_id='".$system_id."' AND school='".$acad_group."' AND department='".$prog_dept."' AND program_name='".$program_name."' AND acad_career='".$acad_career."' AND program_code='".$program_code."' AND catalog_desc='".$catalog_desc."' AND catalog_nbr='".$catalog_nbr."' AND class_nbr='".$class_nbr."' AND csr_id='".$csr_id."' AND admit_term='".$admit_term."' AND instr_role='".$role."' AND faculty_id='".$faculty_id."' AND status='1'";
//print_r($d1);die;
$resultinternships = mysqli_query($conn, $d1);
$numrow = mysqli_num_rows($resultinternships);
if($numrow>0){
$m++; // No change
}else{
$f++; // total change
$d2 = "SELECT * from tbl_internships WHERE system_id='".$system_id."' AND catalog_nbr='".$catalog_nbr."' AND status='1'";
$resultinternships2 = mysqli_query($conn, $d2);
$numrow2 = mysqli_num_rows($resultinternships2);
if($numrow2>0){
$k++; // same system id and catalog nbr
$d3 = "SELECT * from tbl_internships WHERE system_id='".$system_id."' AND student_name='".$fullname."' AND catalog_nbr='".$catalog_nbr."' AND faculty_id='".$faculty_id."' AND class_nbr='".$class_nbr."' AND csr_id='".$csr_id."' AND status='1'";
$resultinternships3 = mysqli_query($conn, $d3);
$numrow3 = mysqli_num_rows($resultinternships3);
if($numrow3>0){
$n++;
//update school and department
$query1 = "UPDATE tbl_internships SET school ='".$acad_group."',department='".$prog_dept."',modifiedon='".date('Y-m-d H:i:s')."' WHERE system_id='".$system_id."' AND student_name='".$fullname."' AND catalog_nbr='".$catalog_nbr."' AND faculty_id='".$faculty_id."' AND class_nbr='".$class_nbr."' AND csr_id='".$csr_id."' AND status='1'";
if(mysqli_query($conn, $query1)){
$b++; //total updated
}else{
$c++; //not updated
}
}else{
//insert - either faculty id change or class_nbr change
$q++;
$query2 = 'INSERT INTO tbl_internships SET student_name ="'.$fullname.'" , system_id="'.$system_id.'", school="'.$acad_group.'", department="'.$prog_dept.'", program_code="'.$program_code.'", program_name="'.$program_name.'", class_nbr="'.$class_nbr.'",instr_role="'.$role.'", faculty_id="'.$faculty_id.'", admit_term="'.$admit_term.'", acad_career="'.$acad_career.'", csr_id="'.$csr_id.'", catalog_desc="'.$catalog_desc.'", academic_year="2022-2023",catalog_nbr="'.$catalog_nbr.'",created="'.date('Y-m-d H:i:s').'"';
if(mysqli_query($conn, $query2)){
$r++; //faculty or class_nbr change new entry
}else{
$s++; //not inserted - faculty or class nbr new entry
$customArrayNew[] = [ 'fullname' => trim($row->NAME_FORMAL),
'system_id' => trim($row->EMPLID),
'acad_group' => trim($row->ACAD_GROUP),
'prog_dept' => trim($row->SU_REP_PRG_DEPT),
'program_name' => trim($row->SU_REP_PRG_DESCR),
'acad_career' => trim($row->ACAD_CAREER),
'program_code' => trim($row->SU_REP_PRG_CD),
'catalog_desc' => trim($row->DESCR),
'catalog_nbr' => trim($row->CATALOG_NBR),
'class_nbr' => trim($row->CLASS_NBR),
'csr_id' => trim($row->CRSE_ID),
'admit_term' => trim($row->ADMIT_TERM),
'role' => trim($row->INSTR_ROLE),
'faculty_id' => trim($row->INSTRUCTOR_ID)];
}
}
}else{
//system id and catalog nbr does not exist so insert
$query = 'INSERT INTO tbl_internships SET student_name ="'.$fullname.'" , system_id="'.$system_id.'", school="'.$acad_group.'", department="'.$prog_dept.'", program_code="'.$program_code.'", program_name="'.$program_name.'", class_nbr="'.$class_nbr.'",instr_role="'.$role.'", faculty_id="'.$faculty_id.'", admit_term="'.$admit_term.'", acad_career="'.$acad_career.'", csr_id="'.$csr_id.'", catalog_desc="'.$catalog_desc.'", academic_year="2022-2023",catalog_nbr="'.$catalog_nbr.'",created="'.date('Y-m-d H:i:s').'"';
//echo $query; die;
if(mysqli_query($conn, $query)){
$i++; //total new insert
}else{
$j++; // not inserted
$customArray[] = [ 'fullname' => trim($row->NAME_FORMAL),
'system_id' => trim($row->EMPLID),
'acad_group' => trim($row->ACAD_GROUP),
'prog_dept' => trim($row->SU_REP_PRG_DEPT),
'program_name' => trim($row->SU_REP_PRG_DESCR),
'acad_career' => trim($row->ACAD_CAREER),
'program_code' => trim($row->SU_REP_PRG_CD),
'catalog_desc' => trim($row->DESCR),
'catalog_nbr' => trim($row->CATALOG_NBR),
'class_nbr' => trim($row->CLASS_NBR),
'csr_id' => trim($row->CRSE_ID),
'admit_term' => trim($row->ADMIT_TERM),
'role' => trim($row->INSTR_ROLE),
'faculty_id' => trim($row->INSTRUCTOR_ID)];
}
}
}
}
// Send Alert Email
echo $msg = 'For internship data '.$ADMITTERM.' Terms and Other details i.e. No change => '.$m.' and Total Insert => '.$i.' and Not inserted => '.$j.' and Total=> '.$g.' and Updated => '.$b.' and Not Updated=> '.$c.' and Faculty or class_nbr change insert => '.$r.' and Faculty or class_nbr change Not insert => '.$s.' and Total Faculty change to be inserted => '.$q.' and Total change => '.$f.' and Total to be updated => '.$n.' and Same system id and catalog nbr => '.$k;
sendAlertEmail($msg);
echo '<br>';
print_r($customArray);
echo '<pre>';
print_r($customArrayNew);
exit();
}
} catch (Exception $e) {
//die("Connection Error");
}
} else {
echo 'Invalid Request';
exit;
}
/*
* Function : getschoolID
* Description : send request and get response in JSON format
* Date: 19 Oct 2020
* Created By: Amit Verma
*/
function getSchoolID($school_code,$conn)
{
if(!empty($school_code)){
$sql = "select id,school_name from tbl_schools where school_code ='".$school_code."' AND `is_deleted` = '0' AND `status` = '1'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
print_r($row); die;
return $row['id'];
}
}
/*
* Function : getDepartmentID
* Description : send request and get response in JSON format
* Date: 19 Oct 2020
* Created By: Amit Verma
*/
function getDepartmentID($department_code,$conn)
{
if(!empty($department_code)){
$sql = "select id,department_name from tbl_departments where department_code ='".$department_code."' AND `is_deleted` = '0' AND `status` = '1'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
return $row['id'];
}
}
/*
* Function : getAPIResponse
* Description : send request and get response in JSON format
* Date: 19 Oct 2020
* Created By: Amit Verma
*/
function getAPIResponse($post)
{
$url = 'https://slotbooking.sharda.ac.in/mentorapi/getCommonDetails';
if (!empty($url) && !empty($post)) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($post));
$response = curl_exec($ch);
}
//print_r($response); die;
return $response;
}
/*
* Function : sendAlertEmail
*/
function sendAlertEmail($msg)
{
//$to_emails = 'puneet.gujarati@sharda.ac.in,amit.verma@shardatech.org,ajay.sharma@shardatech.org';
$to_emails = 'amit.verma@shardatech.org,rehbar.mehdi@shardatech.org';
$title = 'Ezone Internship Data Cron';
$subject = 'Successful Execution:'.$title.' - '.date('Y-m-d H:i:s');
$message = emailTemplate($title, $msg);
$resp = send_email_pepipost($to_emails, $subject, $message);
return $resp;
}
/*
* Function : send_email_pepipost
* Description : Send Email
*/
function send_email_pepipost($to_emails, $subject, $message, $fromname='', $fromemail='', $replyto=''){
$fromname=$fromname?$fromname:'Ezone Cronjob Alert';
$fromemail=$fromemail?$fromemail:'ezone@shardauniversity.com'; //Change This Email ID;
$replyto=$replyto?$replyto:'noreply@sharda.ac.in'; //Change the Email ID;
//$to_emails = 'amit.verma@shardatech.org';
if(!$to_emails){
return;
}
if(is_string($to_emails)){
$to_emails=explode(",", $to_emails);
}
foreach($to_emails as $to){
$d=array (
'personalizations' => array (0 => array ('recipient' => $to)),
'from' => array ('fromEmail' => $fromemail, 'fromName' => $fromname),
'replyToId'=>$replyto,
'subject' => $subject,
'content' => $message,
);
$email_jason_data=json_encode($d);
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_URL => "https://api.pepipost.com/v2/sendEmail",
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => "",
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 30,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => "POST",
CURLOPT_POSTFIELDS => $email_jason_data,
CURLOPT_HTTPHEADER => array(
"api_key: c77184012dcf9bd5cd1886b4e0a2bb89",
// "api_key: aab3f77715e90569034f0c6e5d912714",
"content-type: application/json"
),
));
$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);
if ($err) {
echo "cURL Error #:" . $err;
}
// else {
// echo $response;
// }
// die;
}
//EOF
//EOF
}
/*
* Function : emailTemplate
*
*/
function emailTemplate($title='Student Records Cronjob',$msg)
{
$template='';
$template.='<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<title>Sharda E-Zone</title>
<style type="text/css">
#outlook a {padding:0;}
body{width:100% !important; -webkit-text-size-adjust:100%; -ms-text-size-adjust:100%; margin:0; padding:0;}
.ExternalClass {width:100%;}
.ExternalClass, .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td, .ExternalClass div {line-height: 100%;}
#backgroundTable {margin:0; padding:0; width:100% !important; line-height: 100% !important;}
img {outline:none; text-decoration:none;border:none; -ms-interpolation-mode: bicubic;}
a img {border:none;}
.image_fix {display:block;}
p {margin: 0px 0px !important;}
table td {border-collapse: collapse;}
table { border-collapse:collapse; mso-table-lspace:0pt; mso-table-rspace:0pt; }
a {color: #1d83aa;text-decoration: none;text-decoration:none!important;}
/*STYLES*/
table[class=full] { width: 100%; clear: both; }
/*IPAD STYLES*/
@media only screen and (max-width: 640px) {
a[href^="tel"], a[href^="sms"] {
text-decoration: none;
color: #33b9ff;
pointer-events: none;
cursor: default;
}
.mobile_link a[href^="tel"], .mobile_link a[href^="sms"] {
text-decoration: default;
color: #33b9ff !important;
pointer-events: auto;
cursor: default;
}
table[class=devicewidth] {width: 440px!important;text-align:center!important;}
table[class=devicewidth] td{text-align:center!important;}
table[class=devicewidth] td.left{text-align:left!important;}
table[class=devicewidthinner] {width: 420px!important;text-align:center!important;}
img[class=banner] {width: 440px!important;height:177px!important;}
img[class=colimg2] {width: 440px!important;height:177px!important;}
}
/*IPHONE STYLES*/
@media only screen and (max-width: 480px) {
a[href^="tel"], a[href^="sms"] {
text-decoration: none;
color: #ffffff; /* or whatever your want */
pointer-events: none;
cursor: default;
}
.mobile_link a[href^="tel"], .mobile_link a[href^="sms"] {
text-decoration: default;
color: #ffffff !important;
pointer-events: auto;
cursor: default;
}
table[class=devicewidth] {width: 350px!important;text-align:center!important;}
table[class=devicewidthinner] {width: 350px!important;text-align:center!important;}
img[class=banner] {width: 350px!important;height:140px!important;}
img[class=colimg2] {width: 350px!important;height:140px!important;}
td[class="padding-top15"]{padding-top:15px!important;}
.full-width{ width:100% !important; text-align:center !important; }
.full-width img{ margin:0 auto !important; }
}
@media only screen and (min-width:481px) and (max-width:599px) {
table[class=wrapper] {
width: 100% !important;
}
table[class=main_table] {
width: 100% !important;
}
td[class=pad_side] {
padding-left: 14px !important;
padding-right: 14px !important;
}
td[class=hide], br[class=hide] {
display: none !important;
}
img[class=full_img] {
width: 100% !important;
height: auto !important;
}
td[class=text], td[class=black], td[class=black2], td[class=red], td[class=white], td[class=white1], td[class=white2], td[class=grey] {
text-align: center !important;
}
td[class=pad_bottom] {
padding-bottom: 20px !important;
}
td[class=pad_top] {
padding-top: 20px !important;
}
td[class=fix_height] {
height: 20px !important;
}
td[class=video] img {
width: 100% !important;
height: auto !important;
}
}
@media only screen and (max-width:480px) {
table[class=wrapper] {
width: 100% !important;
}
table[class=main_table] {
width: 100% !important;
}
td[class=pad_side] {
padding-left: 14px !important;
padding-right: 14px !important;
}
td[class=hide], br[class=hide] {
display: none !important;
}
img[class=full_img] {
width: 100% !important;
height: auto !important;
}
td[class=text], td[class=black], td[class=black2], td[class=red], td[class=white], td[class=white1], td[class=white2], td[class=grey] {
text-align: center !important;
}
td[class=pad_bottom] {
padding-bottom: 20px !important;
}
td[class=pad_top] {
padding-top: 20px !important;
}
td[class=fix_height] {
height: 20px !important;
}
td[class=video] img {
width: 100% !important;
height: auto !important;
}
}
</style>
</head>
<body>
<!-- Start of header -->
<table width="100%" bgcolor="#ffffff" cellpadding="0" cellspacing="0" border="0" id="backgroundTable" st-sortable="header">
<tbody>
<tr>
<td align="center">
<table width="650" cellpadding="0" cellspacing="0" border="0" align="center" class="devicewidth">
<tbody>
<tr>
<td width="100%" align="center">
<table bgcolor="#fff" width="650" cellpadding="0" cellspacing="0" border="0" align="center" class="devicewidth" style="border-left: 1px solid #e1e1e1; border-right: 1px solid #e1e1e1">
<tbody>
<!-- Spacing -->
<tr>
<td height="5" style="font-size:1px; line-height:1px; mso-line-height-rule: exactly;"> </td>
</tr>
<!-- Spacing -->
<tr>
<td>
<!-- logo -->
<table width="100" align="left" border="0" cellpadding="0" cellspacing="0" class="devicewidth">
<tbody>
<tr>
<td width="160" height="60" align="center">
<div class="imgpop" align="center"><a target="_blank" href="http://ezone.sharda.ac.in/" title="Sharda E-Zone"><img src="https://www.sharda.ac.in/attachments/site_logo/logo22.png" alt="Sharda E-Zone" border="0" height="50" style="display:block; border:none; outline:none; text-decoration:none;" /></a></div> </td>
</tr>
</tbody>
</table>
<!-- end of logo -->
</td>
</tr>
<!-- Spacing -->
<tr>
<td height="5" style="font-size:1px; line-height:1px; mso-line-height-rule: exactly;"> </td>
</tr>
<!-- Spacing -->
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<!-- End of Header -->
<table width="650" bgcolor="#fff" border="0" cellspacing="0" cellpadding="0" class="wrapper" align="center" mc:repeatable mc:variant="Right IMAGE + Left(TITLE + DESC + READ MORE)" style="table-layout:fixed;font-family:Arial, sans-serif; border-left: 1px solid #e1e1e1; border-right: 1px solid #e1e1e1; border-bottom: 1px solid #e1e1e1">
<tr>
<td height="15" style="line-height:1px;font-size:1px;"> </td>
</tr>
<tr>
<td class="pad_side">
<table width="100%" border="0" cellspacing="0" cellpadding="0" align="left" class="wrapper">
<tr>
<td align="center" valign="top">
<table width="90%" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td height="1" style="line-height:1px;font-size:1px;" class="fix_height"> </td>
</tr>
<tr>
<td valign="top">
<div class="padding" style="padding: 0 0;font-size:14px;color:#414141;text-align:left;line-height:22px;">
<p style="text-transform:uppercase;font-size:18px;padding:5px 0 15px 0;font-weight:bold;">Dear All,</p>
<p>We are pleased to inform you that the cronjob, <b>'.$title.'</b>, executed successfully as scheduled. This email serves as confirmation that the task was completed without any issues.</p>
</div>
<div class="padding" style="font-size:14px;color:#000000;text-align:left;line-height:22px;">
<p style="padding: 10px 0;"> Here are the details of the successful execution:<br/><br/>
Cronjob Name: '.$title.'<br/>
Execution Date/Time: '.date("Y-m-d H:i:s").'<br/>
Task Description: '.$msg.'<br/>
Server/Environment: Ezone </p>
</div>
<div class="padding" style="font-size:14px;color:#414141;text-align:left;line-height:22px;"><p style="padding: 10px 0">The successful completion of this cronjob signifies that the desired operations have been carried out as intended, ensuring the smooth functioning of our systems and processes.</p>
<p style="padding: 10px 0">If you have any questions or concerns regarding this cronjob or its execution, please feel free to reach out to us. We are here to assist you and provide any necessary information.</p>
<p style="padding: 10px 0">Thank you for your attention, and please don\'t hesitate to contact us if you require further assistance.</p>
</div>
<div class="padding" style="font-size:14px;color:#414141;text-align:left;line-height:22px;padding-top:20px;">
<p>Best regards,<br/>
Team Ezone </p>
</div>
</td>
</tr>
<tr>
<td height="30"></td>
</tr>
<tr>
<td align="center" style="font-weight: bold; border: 1px solid #ebebeb; padding: 4px 10px;font-size: 16px;">Visit Website to Explore <a href="https://ezone.sharda.ac.in/ezone-2022/"><span style="color: #00a3e4">https://ezone.sharda.ac.in</span></a></td>
</tr>
<tr>
<td height="15" style="line-height:1px;font-size:1px;" class="fix_height"> </td>
</tr>
</table>
</td>
<td width="0" class="hide"> </td>
</tr>
</table>
</td>
</tr>
</table>
<!-- end of footer -->
</body>
</html>';
return $template;
}
?>
Kontol Shell Bypass