File: /home/cafsindia/hrms_patroniss_com/timeoffice/timeoffice_model.php
<?php
error_reporting(E_ERROR | E_PARSE);
session_start();
include('../app/dbconnect.php');
require('../phpmailer/class.phpmailer.php');
require('../application/libraries/dompdf/autoload.inc.php');
use Dompdf\Dompdf;
class timeoffice_model extends dbconnect{
Protected $smtp_server;
Protected $port_no;
Protected $user_name;
Protected $user_password;
Protected $hr_mail;
Protected $cc_mail;
public function __construct() {
$this->open_db();
$get_email_qry = 'select * from cw_mail_configurations where trans_status = 1 and mail_status = 1';
$email_info = $this->runQuery("$get_email_qry");
$email_result = $this->result($email_info);
$this->smtp_server = $email_result[0]->smtp_server;
$this->port_no = $email_result[0]->port_no;
$this->user_name = $email_result[0]->mail_username;
$this->user_password = $email_result[0]->mail_password;
$this->hr_mail = $email_result[0]->sender_email;
$this->cc_mail = $email_result[0]->bcc;
}
//expire day get from settings
public function get_expire_info(){
$get_date_expire_qry = 'select expire_day from cw_expire_day where trans_status = 1';
$get_date_expire_info = $this->runQuery("$get_date_expire_qry");
$get_date_expire_result = $this->result($get_date_expire_info);
$expire_day = $get_date_expire_result[0]->expire_day;
return $expire_day;
}
//collect details
public function get_unpunch($unpunched_date,$manage_emp_code){
$get_unpunch_qry = 'select *,cw_designation.designation as designation from dailyunpunch inner join cw_employees on cw_employees.thumb_no = dailyunpunch.dcode inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where dailyunpunch.att_date = "'.$unpunched_date.'" and leave_reporting= "'.$manage_emp_code.'" order by tl_report asc';
//echo $get_unpunch_qry; die;
$unpunched_info = $this->runQuery("$get_unpunch_qry");
$unpunchec_result = $this->result($unpunched_info);
$_SESSION['manger_code'] = $manage_emp_code;
$_SESSION['unpunched_date'] = $unpunched_date;
return $unpunchec_result;
}
//CHECK ALREADY UPDATED
public function check_unpunched_data($unpunched_date,$manage_emp_code){
$get_unpunch_qry = 'select cw_dailyunpunch.att_date,cw_dailyunpunch.empcode,cw_employees.emp_name,cw_leave_status.leave_status as manager_status from cw_dailyunpunch inner join cw_employees on cw_employees.thumb_no = cw_dailyunpunch.dcode join cw_leave_status on cw_leave_status.leave_value = cw_dailyunpunch.manager_status where cw_dailyunpunch.att_date = "'.$unpunched_date.'" and cw_employees.leave_reporting= "'.$manage_emp_code.'"';
$unpunched_info = $this->runQuery("$get_unpunch_qry");
$unpunchec_result = $this->result($unpunched_info);
return $unpunchec_result;
}
//Get Employee Data
public function get_emp_data(){
$emp_code_qry = 'select * from cw_employees where trans_status =1';
$emp_code_data = $this->runQuery("$emp_code_qry");
$emp_code_data_result = $this->result($emp_code_data);
$emp_code_data_result = json_decode(json_encode($emp_code_data_result), true);
$emp_array = array_reduce($emp_code_data_result, function ($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
return $emp_array;
}
public function get_email_data(){
$get_leaveinfo_qry = 'select cw_employees.emp_name,cw_dailyunpunch.empcode,cw_dailyunpunch.att_date,cw_leave_status.leave_status as manager_status,cw_dailyunpunch.manager_status as status,cw_employees.personal_email_id from cw_dailyunpunch inner join cw_employees on cw_employees.thumb_no = cw_dailyunpunch.dcode join cw_leave_status on cw_leave_status.leave_value = cw_dailyunpunch.manager_status where att_date = "'.$_SESSION['unpunched_date'].'" and cw_dailyunpunch.trans_created_by= "'.$_SESSION['manger_code'].'" group by cw_dailyunpunch.empcode';
$leaveinfo_info = $this->runQuery("$get_leaveinfo_qry");
$leaveinfo_result = $this->result($leaveinfo_info);
//Get All Employees data
$emp_code_qry = 'select * from cw_employees where trans_status =1';
$emp_code_data = $this->runQuery("$emp_code_qry");
$emp_code_data_result = $this->result($emp_code_data);
$emp_code_data_result = json_decode(json_encode($emp_code_data_result), true);
$emp_array = array_reduce($emp_code_data_result, function ($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
//Manger Mail id collections
/*$get_manager_mail_qry = 'select company_email_id from cw_employees where employee_code = "'.$_SESSION['manger_code'].'" and trans_status =1';
$manager_mail_info = $this->runQuery("$get_manager_mail_qry");
$manager_mail_result = $this->result($manager_mail_info);
$manger_mail_id = $manager_mail_result[0]->company_email_id;*/
$manger_mail_id = $emp_array[$_SESSION['manger_code']]['company_email_id'];
$table_data = '';
$check_status = array();
if($leaveinfo_result){
if($this->check_print_design_exists()){
$tr_line ='';
$tr_email_line ='';
$insert_values ='';
foreach ($leaveinfo_result as $leave_info){
$emp_name = $leave_info->emp_name;
$emp_code = $leave_info->empcode;
$leave_status = $leave_info->manager_status;
$leave_date = $leave_info->att_date;
$status = $leave_info->status;
//echo"BSK $leave_status ::
if($status === 'R' || $status === 'A' || $status === 'TS' || $status === 'TWS'){
$result_info = $this->sent_emp_mail($leave_info->emp_name,$leave_info->empcode,$leave_info->personal_email_id,$leave_info->status,$manger_mail_id,$leave_status,$emp_array,$_SESSION['unpunched_date']);
$this->update_emp_status($emp_code,$status,$emp_array[$_SESSION['manger_code']]['emp_name']);
}else
if($status === 'MC'){
$result_info = $this->sent_emp_mail($leave_info->emp_name,$leave_info->empcode,$leave_info->personal_email_id,$leave_info->status,$manger_mail_id,$leave_status,$emp_array,$_SESSION['unpunched_date']);
}else{
$result_info['sts'] = false;
$result_info['table_info'] = "<tr><td>$emp_code</td><td style='color:Red;text-align:center;'>-</td><td>$leave_status</td><td style='color:green;'>Successfully Updated</td></tr>";
}
/*if($status === 'R' || $status === 'A' || $status === 'TS' || $status === 'TWS' || $status === 'MC'){
$result_info = $this->sent_emp_mail($leave_info->emp_name,$leave_info->empcode,$leave_info->personal_email_id,$leave_info->status,$manger_mail_id,$leave_status,$emp_array,$_SESSION['unpunched_date']);
$this->update_emp_status($emp_code,$status,$emp_array[$_SESSION['manger_code']]['emp_name']);
}
if($status === 'MC'){
$result_info = $this->sent_emp_mail($leave_info->emp_name,$leave_info->empcode,$leave_info->personal_email_id,$leave_info->status,$manger_mail_id,$leave_status,$emp_array,$_SESSION['unpunched_date']);
}else{
$result_info['sts'] = false;
$result_info['table_info'] = "<tr><td>$emp_code</td><td style='color:Red;text-align:center;'>-</td><td>$leave_status</td><td style='color:green;'>Successfully Updated</td></tr>";
}*/
$tr_email_line .= $result_info['table_info'];
if($result_info['sts']){
$insert_values .= $result_info['insert_values'];
}
$tr_line .="<tr><td>$emp_code</td><td>$emp_name</td><td>$leave_status</td><td>$leave_date</td></tr>";
}
if($insert_values !== ''){
$insert_values = rtrim($insert_values,',');
$email_log_query = 'INSERT into cw_time_office_mail_log (employee_code,mail_id,status,message) values '.$insert_values.'';
$this->runQuery("$email_log_query");
}
$table_data = "<table class='table table-striped table-bordered'>
<thead>
<tr>
<th>Employee Code</th>
<th>Employee Name</th>
<th>Manger Status</th>
<th>Unpunched Date</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
$tr_email_line .= $this->call_email($manger_mail_id,$table_data,$emp_array);
$table_email_data = "<table class='table table-striped table-bordered'>
<thead>
<tr>
<th>Employee Code</th>
<th>Send To</th>
<th>Manger Status</th>
<th>Message</th>
</tr>
</thead>
$tr_email_line
</table>";
return array('sts' => true,'table_info'=>$table_email_data);
}else{
return array('sts' => false,'table_info'=>$table_email_data);
}
}else{
return array('sts' => true,'table_info' => '<table><tr><td colspan="3" style="color:red;">No Data Found</td></tr></table>');
}
}
public function sent_emp_mail($emp_name,$empcode,$personal_email_id,$status,$manger_mail_id,$leave_status,$emp_array,$unpunched_date){
$return_info = array();
$get_email_qry = 'SELECT * FROM `cw_leave_mail` where FIND_IN_SET("'.$status.'",leave_type) and trans_status = 1';
$email_info = $this->runQuery("$get_email_qry");
$email_result = $this->result($email_info);
if($email_result){
$style = "green";
$message = "Mail Send Successfully";
$mail_to = $email_result[0]->mail_to;
$mail_cc = explode(",",$email_result[0]->mail_cc);
$mail_subject = $email_result[0]->mail_subject;
$mail_content = $email_result[0]->mail_content;
$preg_match = preg_match_all('#\@(.*?)\@#', $mail_content, $match);
foreach($match[1] as $for_rslt){
$find_value = "@$for_rslt@";
if($for_rslt === "resignation_date"){
$for_value = date('d-M-Y',strtotime($unpunched_date));
}else
if($for_rslt === "manager_report"){
$manager_report = $emp_array[$empcode]['manager_report'];
$for_value = $emp_array[$manager_report]['emp_name'];
}else{
$for_value = $emp_array[$empcode][$for_rslt];
}
$mail_content = str_replace($find_value,$for_value,$mail_content);
}
$manager_report = $emp_array[$emp_array[$empcode]['manager_report']]['company_email_id'];
$branch_hr = $emp_array[$emp_array[$empcode]['branch_hr']]['company_email_id'];
$smtp_server = $this->smtp_server;
$port_no = $this->port_no;
$user_name = $this->user_name;
$user_password = $this->user_password;
$sender_name = $this->sender_name;
$mail = new PHPMailer();
//$mail->SMTPDebug = 3;
$mail->IsSMTP();
$mail->Host = $smtp_server; // Your SMTP PArameter
$mail->Port = $port_no; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $user_name; // Your Email Address
$mail->Password = $user_password; // Your Password
$mail->SMTPSecure = 'tls'; // Check Your Server's Connections for TLS or SSL
$mail->From = $user_name;//$manger_mail_id;
$mail->FromName = $sender_name;
$mail->AddAddress($personal_email_id);
$sender_mail .= ",".$personal_email_id;
$add_content = "";
if($status === 'R' || $status === 'A' || $status === 'TS' || $status === 'TWS'){
$add_content = "$empcode -";
if($status === 'TS'){
$print_type = 4;
}else
if($status === 'TWS'){
$print_type = 5;
}else
if($status === 'A'){
$print_type = 6;
}else
if($status === 'R'){
$print_type = 7;
}
$path_name = $this->load_print_data($emp_array[$empcode]['role'],$empcode,$print_type,$empcode,$unpunched_date);
if(file_exists($path_name)){
$mail->addAttachment($path_name);
}
}
$mail->IsHTML(true);
$mail->Subject = $add_content.$mail_subject;
$mail->Body = $mail_content;
if($mail_to){
$mail->AddCC($mail_to);
$sender_mail .= ",".$mail_to;
}
if((int)count($mail_cc) > 0){
foreach ($mail_cc as $bcc_key => $bcc_mails){
$mail->AddCC($bcc_mails);
$sender_mail .= ",".$bcc_mails;
}
if($manager_report){
$mail->AddCC($manager_report);
$sender_mail .= ",".$manager_report;
}
if($branch_hr){
$mail->AddCC($branch_hr);
$sender_mail .= ",".$branch_hr;
}
}
if(!$mail = $mail->Send()){
$message = "Mail Not Send";
$style = "red";
}
$return_info['sts'] = true;
$return_info['table_info'] = "<tr><td>$empcode</td><td>$sender_mail</td><td>$leave_status</td><td style='color:$style;'>$message</td></tr>";
$return_info['insert_values'] = "('$empcode','$sender_mail','$leave_status','$message'),";
return $return_info;
}else{
$return_info['sts'] = false;
$return_info['table_info'] = "<tr><td>$empcode</td><td style='color:Red;text-align:center;'>-</td><td>$leave_status</td><td style='color:red;'>Leave Status Content is empty</td></tr>";
return $return_info;
}
}
public function call_email($manger_mail_id,$table_data,$emp_array){
//$manger_mail_id = 'vasanthkumar@cafsinfotech.in';
$hr_mail = '';
if($manger_mail_id){
$smtp_server = $this->smtp_server;
$port_no = $this->port_no;
$user_name = $this->user_name;
$user_password = $this->user_password;
$hr_mail = $this->hr_mail;
$sender_name = $this->sender_name;
//$hr_mail = 'vasanthkumar@cafsinfotech.in';
$mail = new PHPMailer();
$mail->IsSMTP();
$mail->Host = $smtp_server; // Your SMTP PArameter
$mail->Port = $port_no; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $user_name; // Your Email Address
$mail->Password = $user_password; // Your Password
$mail->SMTPSecure = 'tls'; // Check Your Server's Connections for TLS or SSL
$mail->From = $manger_mail_id;
$mail->FromName = $sender_name;
$mail->AddAddress($hr_mail);
$mail->IsHTML(true);
$mail->Subject="Unpunched Info";
$mail->Body = $table_data;
if($mail=$mail->Send()){
$message = 'Mail Send Successfully';
}else{
$message = 'Mail Not Send';
}
$insert_log_values = "('ALL','$hr_mail','0','$message')";
$email_log_query = 'INSERT into cw_time_office_mail_log (employee_code,mail_id,status,message) values '.$insert_log_values.'';
$this->runQuery("$email_log_query");
return "<tr><td>All</td><td>$manger_mail_id</td><td colspan='2' style='color:green;'>$message</td></tr>";
}else{
return "<tr><td>All</td><td style='color:Red;text-align:center;'>-</td><td colspan='2' style='color:Red;text-align:center;'>Manage Mail Id Is Empty</td></tr>";
}
}
public function get_leave_status(){
$leave_status_qry = 'select prime_leave_status_id,leave_status,leave_value from cw_leave_status where trans_status =1';
$leave_status_info = $this->runQuery("$leave_status_qry");
$leave_status_result = $this->result($leave_status_info);
$return_data = "<option value=''>--- Select any one---</option>";
foreach ($leave_status_result as $leave_info){
$return_data .= "<option value='".$leave_info->leave_value."'>$leave_info->leave_status</option>";
}
return $return_data;
}
public function update_emp_status($emp_code,$leave_status,$managername){
if($this->check_termination_status($emp_code)){
if($leave_status === 'R'){
$separation_type = 5;
$separation_reason = "Resigned";
}else
if($leave_status === 'A'){
$separation_type = 6;
$separation_reason = "Abscond";
}else
if($leave_status === 'TS' || $leave_status === 'TWS'){
$separation_type = 7;
$separation_reason = "Terminated";
}
$notice_period_query = "SELECT notice_period FROM `cw_notice_period` where trans_status = 1";
$notice_period_query = $this->runQuery("$notice_period_query");
$notice_period_info = $this->result($notice_period_query);
$notice_period = $notice_period_info[0]->notice_period;
$date = $_SESSION['unpunched_date'];
$separation_reason = "$separation_reason marked based on automated time office mail from $managername date ".date('d-m-Y',strtotime($date));
$resignation_date = date('Y-m-d',strtotime($date.'+'.$notice_period. 'days'));
$created_on = date("Y-m-d h:i:s");
$prime_upd_query = 'termination_status = "1",separation_type = "'. $separation_type .'",resignation_date = "'. $date .'",separation_reason = "'. $separation_reason .'",last_working_date = "'. $resignation_date .'",trans_updated_by = "'. $_SESSION['manger_code'] .'",trans_updated_date = "'.$created_on.'"';
$update_emp_query = "update cw_employees set $prime_upd_query where employee_code = '$emp_code'";
$this->runQuery("$update_emp_query");
}
}
public function check_termination_status($emp_code){
$check_query = "select count(*) as counts from cw_employees where trans_status =1 and employee_code = $emp_code and termination_status = 1";
$check_query = $this->runQuery("$check_query");
$check_info = $this->result($check_query);
$counts = (int)$check_info[0]->counts;
if($counts === 0){
return true;
}else{
return false;
}
}
public function check_print_design_exists(){
$design_query = 'SELECT prime_print_info_id,print_design,print_info_for,print_info_module_id,print_type,print_info_name FROM cw_print_design inner join cw_print_info on cw_print_info.prime_print_info_id = cw_print_design.print_design_for WHERE print_info_module_id = "employees" and cw_print_design.trans_status = 1 and cw_print_info.print_type IN ("4","5","6","7")';
$design_query = $this->runQuery("$design_query");
$design_result = $this->result($design_query);
if(count($design_result) === 4){
return true;
}else{
return false;
}
}
public function load_print_data($category,$emp_code,$print_type,$view_id,$unpunched_date){
$design_query = 'SELECT prime_print_info_id,print_design,print_info_for,print_info_module_id,print_type,print_info_name FROM cw_print_design inner join cw_print_info on cw_print_info.prime_print_info_id = cw_print_design.print_design_for WHERE FIND_IN_SET("'.$category.'",print_info_for) and print_info_module_id = "employees" and cw_print_design.trans_status = 1 and cw_print_info.print_type = "'.$print_type.'"';
$design_query = $this->runQuery("$design_query");
$design_result = $this->result($design_query);
$print_doc_id = $design_result[0]->prime_print_info_id;
$print_design = $design_result[0]->print_design;
$print_type = $design_result[0]->print_type;
$style = "<style>
table{
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
tbody {
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
td, th {
border: 1px solid #000 !important;
font-size: 13px !important;
}
td.fr-thick,th.fr-thick {
border-width: 2px !important;
}
table.fr-dashed-borders td, table.fr-dashed-borders th {
border-style: dashed !important;
}
</style>";
$print_design = $style."".$print_design;
$print_design = str_replace('~','"',$print_design);
$block_qry = 'select * from cw_print_block where print_block_for = "'.$print_doc_id.'" and trans_status = 1';
$block_data = $this->runQuery("$block_qry");
$block_result = $this->result($block_data);
foreach($block_result as $block){
$prime_print_block_id = $block->prime_print_block_id;
$print_block_name = $block->print_block_name;
$print_block_type = (int)$block->print_block_type;
$print_block_table = $block->print_block_table;
$print_block_column = $block->print_block_column;
$suppressed_data = $block->suppressed_data;
$cumulative_data = $block->cumulative_data;
$table_qry = 'select * from cw_print_table where print_table_for_id = "'.$prime_print_block_id.'" and trans_status = 1';
$table_data = $this->runQuery("$table_qry");
$table_result = $this->result($table_data);
$line_table_query = "";
$cutome_table_check = array('transactions'=>'cw_transactions');
foreach($table_result as $table){
$line_prime_table = $table->line_prime_table;
$line_prime_col = $table->line_prime_col;
$line_join_type = $table->line_join_type;
$line_join_table = $table->line_join_table;
$line_join_col = $table->line_join_col;
$line_sort = $table->line_sort;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$cf_id = "prime_".$module_name."_cf_id";
$cf_table_name = "cw_".$module_name."_cf";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
$join_cf_id = "prime_".$join_module_name."_cf_id";
$join_cf_table_name = "cw_".$join_module_name."_cf";
if((int)$line_sort === 1){
if($cutome_table_check[$module_name]){
$line_prime_table = " $line_prime_table ";
}else{
$line_prime_table = " $line_prime_table inner join $cf_table_name on $line_prime_table.$prime_id = $cf_table_name.$prime_id ";
}
if($cutome_table_check[$join_module_name]){
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col";
}else{
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col inner join $join_cf_table_name on $line_join_table.$join_prime_id = $join_cf_table_name.$join_prime_id ";
}
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
if($cutome_table_check[$join_module_name]){
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col inner join $join_cf_table_name on $line_join_table.$join_prime_id = $join_cf_table_name.$join_prime_id ";
}
}
}
if(!$line_table_query){
$module_name = str_replace("cw_","",$print_block_table);
$prime_id = "prime_".$module_name."_id";
$cf_id = "prime_".$module_name."_cf_id";
$cf_table_name = "cw_".$module_name."_cf";
$line_table_query = " $print_block_table ";
}
if(!$print_block_column){
$print_block_column = "*";
}else{
$select_query = "";
$select_ytd_query = "";
$pick_query = "";
$map_column = explode(",",$print_block_column);
foreach($map_column as $table_column){
$map_column = explode(".",$table_column);
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if($control_name === "transactions"){
$control_name = "employees";
}
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "'.$control_name.'" and label_name = "'.$column.'" and trans_status = "1"';
$form_data = $this->runQuery("$form_qry");
$form_result = $this->result($form_data);
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_ytd_query .= "sum($table_name.$label_name) as $label_ytd, ";
$select_query .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_table);
foreach($where_trans_info as $trans_info){
if($trans_info === "cw_transactions"){
$select_query .= "cw_transactions.transactions_month , ";
}
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_qry = 'select * from cw_print_table_where where where_for_id = "'.$prime_print_block_id.'" and trans_status = 1';
$where_data = $this->runQuery("$where_qry");
$where_result = $this->result($where_data);
$where_condition = "";
if($where_result){
$where_condition = str_replace('^','"',$where_result[0]->where_condition);
$where_condition = str_replace('@logged_id@',$view_id,$where_condition);
$session_date_list = array("logged_DMY"=>"d-m-Y","logged_YMD"=>"Y-m-d","logged_MY"=>"m-Y","logged_YM"=>"Y-m","logged_Y"=>"Y");
$session_query = 'select session_value from cw_session_value where session_for = 1 and trans_status = "1"';
$session_data = $this->runQuery("$session_query");
$session_result = $this->result($session_data);
foreach($session_result as $rslt){
$session_value = $rslt->session_value;
if($session_value !== "access_data"){
$exist_val = "@".$session_value."@";
if($session_date_list[$session_value]){
$date_formate = $session_date_list[$session_value];
$saved_session_val = date($date_formate);
}else{
$saved_session_val = $this->session->userdata($session_value);
}
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
}else{
$where_condition = " and cw_employees.employee_code = $emp_code";
}
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
if((int)$cumulative_data === 1){
$start_fin_date = $this->financial_info[0]->start_date;
$start_fin_date = date('m-Y',strtotime($start_fin_date));
$end_fin_date = $this->financial_info[0]->end_date;
$end_fin_date = date('m-Y',strtotime($end_fin_date));
$select_ytd_query = rtrim($select_ytd_query,',');
$select_ytd_query = rtrim($select_ytd_query,' , ');
$where_ytd_condition = ' and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") >= date_format(str_to_date("'.$start_fin_date.'", "%m-%Y"), "%Y-%m") and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") <= date_format(str_to_date("'.$end_fin_date.'", "%m-%Y"), "%Y-%m")';
$final_ytd_qry = "select $select_ytd_query from $line_table_query $pick_query where $where_trans $where_condition $where_ytd_condition";
$final_ytd_data = $this->runQuery("$final_ytd_qry");
$final_ytd_result = $this->result($final_ytd_data);
foreach($final_ytd_result as $ytd_rslt){
$map_column = explode(",",$print_block_column);
foreach($map_column as $table_column){
$map_column = explode(".",$table_column);
$ytd_column = $map_column[1]."_ytd";
$ytd_value = $ytd_rslt->$ytd_column;
$replace_ytd_val = "@".$ytd_column."@";
$print_design = str_replace($replace_ytd_val,$ytd_value,$print_design);
}
}
}
$final_qry = "select $select_query from ".$line_table_query." $pick_query where $where_trans $where_condition";
$final_data = $this->runQuery("$final_qry");
$final_result = $this->result($final_data);
$tr_line = "";
$th_line = "";
$count = 0;
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","DFY"=>"d F Y","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
$split_qry = 'select * from cw_print_split where trans_status = 1 and split_table_info ="'.$print_doc_id.'"';
$split_data = $this->runQuery("$split_qry");
$split_result = $this->result($split_data);
$split_array = array();
foreach($split_result as $split){
$split_info = $split->split_info;
$split_colum = $split->split_colum;
$split_array[$split_colum] = $split_info;
}
$map_column = explode(",",$print_block_column);
if($final_result){
$data['print_sts'] = true;
foreach($final_result as $rslt){
$count++;
$td_line = "";
foreach($map_column as $table_column){
$map_column = explode(".",$table_column);
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
//amount number is changed to in words for net pays--07SEP2019
if($column == 'net_pay'){
$value = $rslt->$column;
$print_design = str_replace($replace_val,$value,$print_design);
$net_pay_val = $value;
$net_pay_words = $this->numbertowords($net_pay_val);
$net_pay_words = strtoupper($net_pay_words);
$print_design = str_replace("@net_pay_words@",$net_pay_words,$print_design);
}else
if($column == 'employee_name'){
$value = ucwords($rslt->$column);
$print_design = str_replace($replace_val,$value,$print_design);
}else
if($column == 'reporting_person'){
$value = ucwords($rslt->$column);
$print_design = str_replace($replace_val,$value,$print_design);
}else
if($column == 'salary'){
$value = $rslt->$column;
$print_design = str_replace($replace_val,$value,$print_design);
$salary_val = $value;
$salary_words = $this->numbertowords($salary_val);
$salary_words = ucwords($salary_words);
$print_design = str_replace("@salary_words@",$salary_words,$print_design);
}
if(isset($split_array[$replace_val])){
//Process split informtion
$process_function = $split_array[$replace_val];
if((int)$process_function === 1){
$transactions_month = $final_result[0]->transactions_month;
$employee_code = $final_result[0]->employee_code;
$loan_info = $this->get_loan_value($transactions_month,$employee_code);
$print_design = str_replace($replace_val,$loan_info,$print_design);
}
}else{
if($print_block_type === 1){
$print_design = str_replace($replace_val,$value,$print_design);
foreach($assign_date_formate_list as $key=>$formate){
if($column == 'transactions_month'){//transactions month static updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$value = date('Y-m-d',strtotime("01-".$rslt->$column));
$date_value = date_create($value);
$replace_value = strtoupper(date_format($date_value,$formate));
$print_design = str_replace($replace_val,$replace_value,$print_design);
}else{//not static month updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
$date_value = date_create($value);
$replace_value = date_format($date_value,$formate);
$print_design = str_replace($replace_val,$replace_value,$print_design);
}
}
}else
if($print_block_type === 2){
$td_line .= "<td style='text-align:center;'>$value</td>";
}
if($count === 1){
$head_name = ucwords(str_replace("_"," ",$column));
$th_line .= "<th style='text-align:center;'>$head_name</th>";
}
}
}
if($print_block_type === 2){
if($count === 1){
$th_line = "$th_line";
}
$tr_line .= "<tr>$td_line</tr>";
}
}
if($print_block_type === 2){
$table_list = "<table style='width:100%;'><thead>$th_line</thead><tbody>$tr_line</tbody></table>";
$replce_block = "@".strtolower(str_replace(" ","_",$print_block_name))."@";
$print_design = str_replace($replce_block,$table_list,$print_design);
}
}
}
$unpunched_date_dis=date("d-M-Y",strtotime($unpunched_date));
$print_design = str_replace("<br>","",$print_design);
$print_design = str_replace("@today_date@",$unpunched_date_dis,$print_design);
$table_data = "<!DOCTYPE html><html> <body>".$print_design."</body></html>";
$pdf = new DOMPDF(array('enable_remote' => true));
$pdf->loadHtml($table_data);
$pdf->render();
$output = $pdf->output();
$design_name = strtolower(str_replace(" ","_",$design_result[0]->print_info_name));
$folder = "./time_office_mail/".$design_name;
//Check Folder Exist
if (!file_exists($folder)){
mkdir($folder, 0777, true);
}
//Check File Exist
if(file_exists($folder."/".$emp_code.".pdf")){
unlink($folder."/".$emp_code.".pdf");
}
file_put_contents($folder."/".$emp_code.".pdf" , $output);
chmod($folder."/".$emp_code.".pdf", 0777);
return $folder."/".$emp_code.".pdf";
}
public function get_loan_value($process_month,$employee_code){
$process_month = explode("-",$process_month);
$loan_month = $process_month[0];
$loan_year = $process_month[1];
$loan_qry = 'select emp_code,install_amount,cw_loan_type.loan_type from cw_loan_installment inner join cw_loan_type on cw_loan_type.prime_loan_type_id = cw_loan_installment.loan_type where cw_loan_installment.trans_status = 1 and cw_loan_installment.emp_code ="'.$employee_code.'" and cw_loan_installment.install_year ="'.$process_month.'"';
$loan_data = $this->runQuery("$loan_qry");
$loan_result = $this->result($loan_data);
$loan_tr = "";
foreach($loan_result as $loan){
$loan_type = $loan->loan_type;
$install_amount = $loan->install_amount;
$loan_tr .= "<tr>
<td style='width:77%;'>$loan_type</td>
<td>$install_amount</td>
</tr>";
}
if($loan_tr !== ""){
$loan_tr = "<table style='width:100%'>
$loan_tr
</table>";
}
return $loan_tr;
}
//number to words changed in payslip
public function numbertowords($number){
$no = round($number);
$point = round($number - $no, 2) * 100;
$hundred = null;
$digits_1 = strlen($no);
$i = 0;
$str = array();
$words = array('0' => '', '1' => 'One', '2' => 'Two',
'3' => 'Three', '4' => 'Four', '5' => 'Five', '6' => 'Six',
'7' => 'Seven', '8' => 'Eight', '9' => 'Nine',
'10' => 'Ten', '11' => 'Eleven', '12' => 'Twelve',
'13' => 'Thirteen', '14' => 'Fourteen',
'15' => 'Fifteen', '16' => 'Sixteen', '17' => 'Seventeen',
'18' => 'Eighteen', '19' =>'Nineteen', '20' => 'Twenty',
'30' => 'Thirty', '40' => 'Forty', '50' => 'Fifty',
'60' => 'Sixty', '70' => 'Seventy',
'80' => 'Eighty', '90' => 'Ninety');
$digits = array('', 'Hundred', 'Thousand', 'Lakh', 'Crore');
while ($i < $digits_1) {
$divider = ($i == 2) ? 10 : 100;
$number = floor($no % $divider);
$no = floor($no / $divider);
$i += ($divider == 10) ? 1 : 2;
if ($number) {
$plural = (($counter = count($str)) && $number > 9) ? '' : null;
$hundred = ($counter == 1 && $str[0]) ? ' and ' : null;
$str [] = ($number < 21) ? $words[$number] .
" " . $digits[$counter] . $plural . " " . $hundred
:
$words[floor($number / 10) * 10]
. " " . $words[$number % 10] . " "
. $digits[$counter] . $plural . " " . $hundred;
} else $str[] = null;
}
$str = array_reverse($str);
$result = implode('', $str);
$points = ($point) ?
"." . $words[$point / 10] . " " .
$words[$point = $point % 10] : '';
return $result;
}
}
?>