File: /home/cafsindia/hrms_cafsinfotech_in/application/controllers/Input_import_setup.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require_once("Form_setting.php");
class Input_import_setup extends Action_controller{
public function __construct(){
parent::__construct('input_import_setup');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['module_sts'] = (int)$this->module_sts;
$data['quick_link'] = $this->quick_link;
$data['pick_list'] = $this->pick_list;
$data['form_info'] = $this->form_info;
$data['table_head'] = $this->table_head;
$data['fliter_list'] = $this->fliter_list;
$data['freeze_list'] = $this->freeze_list;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//FUNCTION FOR INSERT A FORM SETTING AND ALL OTHER TABLES
public function excel_file_import(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$import_type = (int)$this->input->post("import_type");
$file_path = $this->input->post("file_path");
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
include($filename);
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE IN ARRAY
$cat_tab_arr = $this->cat_qry_struct_function();
//select for module name get
$select_columns = 'cw_modules.module_id,cw_modules.module_name';
$table_name = 'cw_modules';
$table_join = '';
$table_where = 'cw_modules.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$module_tab_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$module_exist_arr = array_reduce($module_tab_rslt, function($result, $arr) {
$result[$arr['module_id']] = $arr['module_id'];
return $result;
}, array());
//select for module name get
$select_columns = 'TABLE_NAME as table_info,COLUMN_NAME as column_info';
$table_name = '`INFORMATION_SCHEMA`.`COLUMNS`';
$table_join = '';
$table_where = '`TABLE_SCHEMA`="'.$this->config->item("db_name").'" AND COLUMN_NAME NOT IN ("trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status")';
//FUNCTION USED FOR CREATE A QRY
$table_name_col_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$table_name_col_arr = array_reduce($table_name_col_rslt, function($result, $arr) {
$result[$arr['table_info']][$arr['column_info']] = $arr['column_info'];
return $result;
}, array());
//select query for get a form view setting tab result
$select_columns = '*';
$table_name = 'cw_form_view_setting';
$table_join = '';
$table_where = 'cw_form_view_setting.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$form_view_tab_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$form_view_tab_arr = array_reduce($form_view_tab_rslt, function($result, $arr) {
$result[$arr['form_view_heading']] = $arr;
return $result;
}, array());
$field_type_array = array(""=>"---- Field Type ----",1=>"Text",2=>"Decimals",3=>"Integer",4=>"Date",5=>"Picklist",6=>"Checkbox",7=>"Multi Picklist",8=>"Summary Box",9=>"Auto Complete Box",10=>"File Upload Box",11=>"Mobile Number",12=>"Email",13=>"Date & Time",14=>"Read Only",15=>"Time");
$transaction_list = array(""=> "--- Transaction Type ----","1" => "Basic Information","2" => "Earning Information","3" => "Deduction Information","4" => "None");
$formula_type_arr = array(""=> "--- Formula Type ----","1" => "Earnings","2" => "Deductions");
$formula_mode_arr = array(""=> "--- Formula Mode ----","1" => "Direct Input","2" => "Formula Input","3" => "Condition Input");
//select query for get a earnings and deductions input(for file)
$select_columns = 'cw_form_view_setting.form_view_heading as form_name,cw_form_setting.label_name';
$table_name = 'cw_form_setting';
$table_join = 'inner join cw_form_view_setting on cw_form_view_setting.prime_form_view_id = cw_form_setting.input_for';
$table_where = 'cw_form_view_setting.form_view_label_name in ("earnings","deductions") and cw_form_view_setting.trans_status = 1 and cw_form_setting.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$ear_ded_formula_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$ear_ded_formula_arr = array_reduce($ear_ded_formula_rslt, function($result, $arr) {
$result[$arr['form_name']][$arr['label_name']] = $arr['label_name'];
return $result;
}, array());
$sheet = $excel_obj->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highest_column = $sheet->getHighestColumn();
$worksheetTitle = $sheet->getTitle();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highest_column);
$key_data = array();
$status_array = array();
$err_column_array = array();
$sts = true;
//CONDITION AND FUNCTION FOR PAYROLL FORMULA INSERT
if($import_type === 2){
$pay_formula_exist_arr = $this->pay_formula_exist_check($ear_ded_formula_arr,$cat_tab_arr,$formula_type_arr,$formula_mode_arr,$sheet,$highestRow,$highestColumnIndex,$key_data,$status_array,$err_column_array);
$err_column_count = count($pay_formula_exist_arr);
//ERROR CHECK AND THRUOGH CONDITION
if((int)$err_column_count > 0){
echo json_encode(array('success'=>false,'message'=>"Data Not Imported",'table_info'=>$pay_formula_exist_arr));
exit(0);
}else{
$formula_ins_qry_val = "";
for ($row = 1; $row <= $highestRow; $row++) {
$table_data = array();
$key_val_data = array();
$col_data = array();
$status_info = array();
$status_info["Excel Row"] = $row;
$error_info = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$col_data[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$formula_for = str_replace("_"," ",$key_val_data['formula_for']);
$formula_type = ucwords(str_replace("_"," ",$key_val_data['formula_type']));
$formula_mode = ucwords(str_replace("_"," ",$key_val_data['formula_mode']));
$out_column = strtolower(str_replace(" ","_",$key_val_data['out_column']));
$payroll_formula = $key_val_data['payroll_formula'];
$round_value = $key_val_data['round_value'];
$formula_order = $key_val_data['formula_order'];
$order_by = $key_val_data['order_by'];
$fandf_only = $key_val_data['fandf_only'];
$category_id = (int)$cat_tab_arr[$formula_for];
$formula_type_id = array_search($formula_type,$formula_type_arr);
$formula_mode_id = array_search($formula_mode,$formula_mode_arr);
//INSERT QRY VALUES BUILD FUNCTION
$formula_ins_qry_val .= '("'.$category_id.'","'.$formula_type_id.'","'.$out_column.'","'.$payroll_formula.'","'.$formula_mode_id.'","'.$round_value.'","'.$formula_order.'","'.$fandf_only.'","'.$logged_id.'","'.$today_date.'"),';
$status_info['Status'] = "Payroll Formula are Created Successfully..!";
$status_array[] = $status_info;
}
}
$formula_ins_qry_val = rtrim($formula_ins_qry_val,',');
$ins_pay_formula_qry = 'INSERT INTO cw_payroll_formula (formula_for,formula_type,out_column,payroll_formula,formula_mode,round_value,formula_order,fandf_only,trans_created_by,trans_created_date) VALUE '.$formula_ins_qry_val;
$ins_pay_formula_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_pay_formula_qry')");
$ins_pay_formula_rslt = $ins_pay_formula_info->result();
$ins_pay_formula_info->next_result();
$ins_id = (int)$ins_pay_formula_rslt[0]->ins_id;
if($ins_id){
echo json_encode(array('success'=>true,'message'=>"Successfully File Imported",'table_info'=>$status_array));
exit(0);
}
}
}else
if($import_type === 1){
//FUNCTION FOR A LABEL NAME EXIST CHECK FUNCTION
$label_name_error_arr = $this->label_name_exist($form_view_tab_arr,$field_type_array,$sheet,$highestRow,$highestColumnIndex,$key_data,$err_column_array);
$err_column_count = count($label_name_error_arr);
//ERROR CHECK AND THRUOGH CONDITION
if((int)$err_column_count > 0){
echo json_encode(array('success'=>false,'message'=>"Data Not Imported",'table_info'=>$label_name_error_arr));
exit(0);
}else{
$picklist_exist_arr = $this->picklist_exist_check($table_name_col_arr,$module_exist_arr,$form_view_tab_arr,$field_type_array,$sheet,$highestRow,$highestColumnIndex,$key_data,$err_column_array);
$err_column_count = count($picklist_exist_arr);
//ERROR CHECK AND THRUOGH CONDITION
if((int)$err_column_count > 0){
echo json_encode(array('success'=>false,'message'=>"Data Not Imported",'table_info'=>$picklist_exist_arr));
exit(0);
}else{
//loop start
for ($row = 1; $row <= $highestRow; $row++) {
$table_data = array();
$key_val_data = array();
$col_data = array();
$status_info = array();
$status_info["Excel Row"] = $row;
$error_info = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$col_data[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
if(!empty($key_val_data)){
$input_for = ucwords(str_replace("_"," ",$key_val_data['input_for']));
$field_for_arr = explode(",",$key_val_data['field_for']);
// $field_for = $key_val_data['field_for'];
$field_type = ucwords(str_replace("_"," ",$key_val_data['field_type']));
$label_name = strtolower(str_replace(" ","_",$key_val_data['label_name']));
$view_name = ucwords(str_replace("_"," ",$key_val_data['view_name']));
$short_name = ucwords(str_replace("_"," ",$key_val_data['view_name']));
$default_value = "0";
$picklist_table = $key_val_data['picklist_table'];
$picklist_column = $key_val_data['picklist_column'];
$picklist_value = $key_val_data['picklist_value'];
$show_value = $key_val_data['field_show'];
$table_show_val = $key_val_data['table_show'];
$transaction_type = $key_val_data['transaction_type'];
$gross_check_val = (int)$key_val_data['gross_check'];
$taxable_check_val = (int)$key_val_data['taxable_check'];
$month_check_val = (int)$key_val_data['month_check'];
$payroll_check_val = (int)$key_val_data['payroll_check'];
$increment_check_val = (int)$key_val_data['increment_check'];
$arrear_pf_check_val = (int)$key_val_data['arrear_pf_check'];
$fandf_check_val = (int)$key_val_data['fandf_check'];
$deduction_check_val = (int)$key_val_data['deduction_check'];
$loan_check_val = (int)$key_val_data['loan_check'];
$uniform_check_val = (int)$key_val_data['uniform_check'];
$utilities_set_val = (int)$key_val_data['utilities_set'];
$report_set_val = (int)$key_val_data['report_set'];
$master_rep_set_val = 0;
if((int)$show_value === 1){
$master_rep_set_val = 1;
}
$earn_month_check_val = 0;
$earn_payroll_check_val = 0;
$deduction_month_check_val = 0;
$ded_payroll_check_val = 0;
$benefit_check_val = 0;
if($input_for){
$module_form_arr = $form_view_tab_arr[$input_for];
$prime_module_id = $module_form_arr['prime_view_module_id'];
$form_input_for = $module_form_arr['prime_form_view_id'];
$input_view_type = $module_form_arr['form_view_type'];
$form_view_label_name = $module_form_arr['form_view_label_name'];
//for to check a valid category and to get category id and also to insert a new category
if(!empty($field_for_arr)){
$field_for_arr = array_combine($field_for_arr,$field_for_arr);
$cat_diff_arr = array_diff_key($field_for_arr,$cat_tab_arr);
//IF IMPORT NEW CATEGORY IN BEFORE INSERT A CATEGORY TABLE AFTER GET
if(!empty($cat_diff_arr)){
//FOR BUILD A SINGLE AND MULTI INSERT QUERY VALUE USING IMPLODE
$cat_ins_val = implode('","'.$logged_id.'","'.$today_date.'"),("',$cat_diff_arr);
$ins_cat_qry = 'insert into cw_category (category_name,trans_created_by,trans_created_date) value ("'.$cat_ins_val.'","'.$logged_id.'","'.$today_date.'")';
$ins_cat_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_cat_qry')");
$ins_cat_rslt = $ins_cat_info->result();
$ins_cat_info->next_result();
$ins_id = $ins_cat_rslt[0]->ins_id;
if((int)$ins_id){
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE IN ARRAY
$cat_tab_arr = $this->cat_qry_struct_function();
}
}
$prime_cat_id = implode(',',array_intersect_key($cat_tab_arr,$field_for_arr));
$field_type_id = array_search($field_type,$field_type_array);
$trans_type_id = array_search($transaction_type,$transaction_list);
$text_type_id = '';
if($field_type_id === 1){
$text_type_id = 1;
}
if((int)$trans_type_id === 1 || (int)$trans_type_id === 2){
if($month_check_val){
$earn_month_check_val = 1;
}
if($payroll_check_val){
$earn_payroll_check_val = 1;
}
$deduction_check_val = 0;
$loan_check_val = 0;
$uniform_check_val = 0;
}else
if((int)$trans_type_id === 3){
if($month_check_val){
$deduction_month_check_val = 1;
}
if($payroll_check_val){
$ded_payroll_check_val = 1;
}
$arrear_pf_check_val = 0;
$increment_check_val = 0;
$taxable_check_val = 0;
}else
if((int)$trans_type_id === 3){
$gross_check_val = 0;
$gross_check_val = 0;
$arrear_pf_check_val = 0;
$increment_check_val = 0;
$taxable_check_val = 0;
$fandf_check_val = 0;
$loan_check_val = 0;
$uniform_check_val = 0;
}
//field decimal input default empty only add for int field
$field_decimals = "";
$field_length = "";
$date_type = "";
$picklist_type = 0;
$pickdisplay_value = "";
$picklist_import = "";
if((int)$field_type_id === 5){
//FUNCTION FOR INSERT A NEW PIKCLIST MODULE AND TABLE
if(!$picklist_table){
$picklist_module_add = $this->picklist_module_create($prime_module_id,$label_name,$prime_cat_id,$picklist_value);
if((int)$picklist_module_add){
$picklist_type = 1;
$picklist_column = "prime_".$label_name."_id,$label_name";
$picklist_table = "cw_$label_name";
$pickdisplay_value = "$label_name";
$picklist_import = 2;
}
}else
if($picklist_table && $picklist_column){
$picklist_col_arr = explode(",",$picklist_column);
$picklist_type = 1;
$pickdisplay_value = '"'.$picklist_col_arr[1].'"';
$picklist_import = 2;
}
}else
if((int)$field_type_id === 1){
$field_length = 100;
}else
if((int)$field_type_id === 2){
$field_decimals = 2;
$default_value = "0.00";
}else
if((int)$field_type_id === 3){
$field_length = 10;
}else
if((int)$field_type_id === 4){
$date_type = 1;
$default_value = NULL;
}
if((int)$table_show_val === 1){
$show_value = 1;
}
//TABLE ARRAY BUILD FUNCTION
$table_data = array(
'prime_form_id' => 0,
'prime_module_id' => $prime_module_id,
'input_view_type' => $input_view_type,
'input_for' => $form_input_for,
'field_type' => $field_type_id,
'label_name' => $label_name,
'view_name' => $view_name,
'short_name' => $short_name,
'field_length' => $field_length,
'text_type' => $text_type_id,
'date_type' => $date_type,
'field_decimals' => $field_decimals,
'pick_list_type' => $picklist_type,
'pick_list' => $picklist_column,
'pick_table' => $picklist_table,
'pick_list_import' => $picklist_import,
'field_isdefault' => 1,
'default_value' => $default_value,
'mandatory_field' => 0,
'upd_mandatory_field' => 0,
'unique_field' => 0,
'field_show' => $show_value,
'table_show' => $table_show_val,
'search_show' => 0,
'field_for' => $prime_cat_id,
'pick_display_value' => $pickdisplay_value,
'upload_extension' => '',
'upload_file_size' => 500,
'file_type' => '',
'transaction_type' => $trans_type_id,
'gross_check' => $gross_check_val,
'taxable_check' => $taxable_check_val,
'earn_month_check' => $earn_month_check_val,
'earn_payroll_check' => $earn_payroll_check_val,
'ded_payroll_check' => $ded_payroll_check_val,
'benefit_check' => $benefit_check_val,
'increment_check' => $increment_check_val,
'arrear_pf_check' => $arrear_pf_check_val,
'fandf_check' => $fandf_check_val,
'deduction_check' => $deduction_check_val,
'deduction_month_check' => $deduction_month_check_val,
'loan_check' => $loan_check_val,
'uniform_check' => $uniform_check_val,
'edit_read' => 0,
'picklist_data' => 0,
'duplicate_data' => 0,
'table_sort' => 0,
);
if((int)$table_show_val === 1){
$check_sort_query = "SELECT max(table_sort) as table_sort FROM cw_form_setting WHERE prime_module_id = \"$prime_module_id\" AND input_view_type IN (\"1\",\"2\") AND table_show = \"1\" AND trans_status = \"1\"";
$check_sort_query = $this->db->query("CALL sp_a_run ('SELECT','$check_sort_query')");
$check_sort_info = $check_sort_query->row();
$check_sort_query->next_result();
$table_sort = (int)$check_sort_info->table_sort;
if($table_sort > 0){
$table_data['table_sort'] = $table_sort + 1;
}
}
$table_data = json_encode($table_data);
if((int)$prime_form_id === 0){
$count_info = $this->db->query("CALL sp_form_setting_crud ('SORT_COUNT', '$table_data','$logged_id')");
$count_result = $count_info->result();
$count_info->next_result();
$table_data = json_decode($table_data,true);
$field_sort = (int)$count_result[0]->sort_count + 1;
$table_data['field_sort'] = $field_sort;
$table_data = json_encode($table_data);
}
$can_process = true;
if((int)$prime_form_id === 0){
$exist_query = 'SELECT count(*) as exist_rslt FROM `cw_form_setting` where prime_module_id = "'.$prime_module_id.'" and label_name = "'.$label_name.'"';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
if((int)$exist_result[0]->exist_rslt !== 0){
$can_process = false;
}
}
if($can_process){
$info = $this->db->query("CALL sp_form_setting_crud ('SAVE', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
if($this->save_table($prime_module_id,$input_view_type,$form_input_for,$form_view_label_name)){
//condition and codes for insert or update a utilities setting
if($utilities_set_val === 1 || $month_check_val === 1){
//function for insert or update a utilities settings details
$util_rslt = $this->utilities_ins_upd_function($utilities_set_val,$month_check_val,$prime_module_id,$label_name,$logged_id,$today_date);
}
//function for report setting import condition
if($report_set_val === 1 || $master_rep_set_val === 1){
//function for insert or update a utilities settings details
$report_rslt = $this->report_ins_upd_function($report_set_val,$master_rep_set_val,$prime_module_id,$prime_cat_id,$label_name,$logged_id,$today_date);
}
if($gross_check_val === 1 || $deduction_check_val === 1){
// function for insert or update a utilities settings details
$report_rslt = $this->payslip_gross_col_upd($prime_module_id,$label_name,$logged_id,$today_date);
}
$status_info['Status'] = "Form Inputs are Created";
$status_array[] = $status_info;
}else{
$error_info["Excel Row"] = $row;
$error_info['Status'] = "Please Check it.! Don't Unable to Process Your Request..!";
$err_column_array[] = $error_info;
}
}else{
$error_info["Excel Row"] = "D$row";
$error_info['Status'] = "For Input name ($label_name) already Exist..!";
$err_column_array[] = $error_info;
}
}else{
$error_info["Excel Row"] = "B$row";
$error_info['Status'] = "Please Check it.! Category Should not Empty..!!";
$err_column_array[] = $error_info;
}
}else{
$error_info["Excel Row"] = "A$row";
$error_info['Status'] = "Please Check it.!Input_for Column Should not Empty..!";
$err_column_array[] = $error_info;
}
}else{
$error_info["Excel Row"] = "$row";
$error_info['Status'] = "Please Map a Valid Excel Data for the Excle Row($row)..!";
$err_column_array[] = $error_info;
}
}
}
$err_column_count = count($err_column_array);
if((int)$err_column_count > 0){
echo json_encode(array('success'=>false,'message'=>"Data Not Imported",'table_info'=>$err_column_array));
}else{
echo json_encode(array('success'=>true,'message'=>"Successfully File Imported",'table_info'=>$status_array));
}
}
}
}
}
//PAYSLIP EARNINGS AND DEDUCTION GROSS COLUMN ARE UPD FROM DEFAULT PAYSLIP
public function payslip_gross_col_upd($prime_module_id,$label_name,$logged_id,$today_date){
$select_columns = '*';
$table_name = 'cw_print_block';
$table_join = '';
$table_where = 'cw_print_block.print_block_module_id = "'.$prime_module_id.'" and cw_print_block.trans_status = 1';
$print_column_name = "";
//FUNCTION USED FOR CREATE A QRY
$print_block_col_map = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$print_block_id = $print_block_col_map[0]['prime_print_block_id'];
$print_column_name = $print_block_col_map[0]['print_block_column'];
if($print_column_name){
if (strpos($print_column_name, 'cw_'.$prime_module_id.'.employee_code') === false) {
$print_column_name .= ',cw_'.$prime_module_id.'.employee_code';
}
}else{
if(strpos($print_column_name, 'cw_'.$prime_module_id.'.employee_code') === false) {
$print_column_name = 'cw_'.$prime_module_id.'.employee_code';
}
}
if (strpos($print_column_name, 'cw_'.$prime_module_id.'.emp_name') === false) {
$print_column_name .= ',cw_'.$prime_module_id.'.emp_name';
}
if (strpos($print_column_name, 'cw_'.$prime_module_id.'.role') === false) {
$print_column_name .= ',cw_'.$prime_module_id.'.role';
}
if(strpos($print_column_name, 'cw_'.$prime_module_id.'.date_of_joining') === false) {
$print_column_name .= ',cw_'.$prime_module_id.'.date_of_joining';
}
if(strpos($print_column_name, 'cw_transactions.department') === false) {
$print_column_name .= ',cw_transactions.department';
}
if(strpos($print_column_name, 'cw_transactions.designation') === false) {
$print_column_name .= ',cw_transactions.designation';
}
if(strpos($print_column_name, 'cw_transactions.bank_name') === false) {
$print_column_name .= ',cw_transactions.bank_name';
}
if(strpos($print_column_name, 'cw_transactions.bank_account_number') === false) {
$print_column_name .= ',cw_transactions.bank_account_number';
}
if(strpos($print_column_name, 'cw_transactions.uan_number') === false) {
$print_column_name .= ',cw_transactions.uan_number';
}
if(strpos($print_column_name, 'cw_transactions.'.$label_name) === false) {
$print_column_name .= ',cw_transactions.'.$label_name;
}
//FUNCTION FOR UPDATE PRINT BLOCK COLUMNS FOR DEFAULT EMPLOYEE PAYSLIP
$upd_payslip_print_rslt = $this->upd_payslip_print_details($prime_module_id,$print_column_name,$logged_id,$today_date);
if($upd_payslip_print_rslt){
$sts = true;
}
}
public function upd_payslip_print_details($prime_module_id,$print_column_name,$logged_id,$today_date){
$upd_payslip_print_qry = 'UPDATE cw_print_block SET print_block_column = "'.$print_column_name.'",trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'" WHERE cw_print_block.print_block_module_id = "'. $prime_module_id .'" and cw_print_block.trans_status = 1';
$upd_payslip_print_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_payslip_print_qry')");
return $upd_payslip_print_rslt;
}
public function pay_formula_exist_check($ear_ded_formula_arr,$cat_tab_arr,$formula_type_arr,$formula_mode_arr,$sheet,$highestRow,$highestColumnIndex,$key_data,$status_array,$err_column_array){
//select query for get a form view setting tab result
$select_columns = '*';
$table_name = 'cw_payroll_formula';
$table_join = '';
$table_where = 'cw_payroll_formula.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$pay_formula_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$pay_formula_arr = array_reduce($pay_formula_rslt, function($result, $arr) {
$result[$arr['formula_for']] = $arr['out_column'];
return $result;
}, array());
for ($row = 1; $row <= $highestRow; $row++) {
$table_data = array();
$key_val_data = array();
$col_data = array();
$error_info = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$col_data[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$formula_for = str_replace("_"," ",$key_val_data['formula_for']);
$formula_type = ucwords(str_replace("_"," ",$key_val_data['formula_type']));
$formula_mode = ucwords(str_replace("_"," ",$key_val_data['formula_mode']));
$out_column = strtolower(str_replace(" ","_",$key_val_data['out_column']));
$payroll_formula = $key_val_data['payroll_formula'];
$round_value = $key_val_data['round_value'];
$formula_order = $key_val_data['formula_order'];
$order_by = $key_val_data['order_by'];
$category_id = (int)$cat_tab_arr[$formula_for];
$formula_type_id = array_search($formula_type,$formula_type_arr);
$formula_mode_id = array_search($formula_mode,$formula_mode_arr);
//check from form setting label name related input for
$form_ear_ded_input = $ear_ded_formula_arr[$formula_type][$out_column];
if(!$category_id){
$error_info["Excel Row"] = "A$row";
$error_info['Status'] = "Invalid Category($formula_for) Added..!Please Check..!";
$err_column_array[] = $error_info;
}else
if($pay_formula_arr[$category_id][$out_column]){
$error_info["Excel Row"] = "A$row";
$error_info['Status'] = "Already this $out_column Exist for this $formula_for Please Check..!";
$err_column_array[] = $error_info;
}else
if(!(int)$formula_type_id){
$error_info["Excel Row"] = "B$row";
$error_info['Status'] = "Invalid Formula Type($formula_type) Added..!Please Check..!";
$err_column_array[] = $error_info;
}else
if(!(int)$formula_mode_id){
$error_info["Excel Row"] = "E$row";
$error_info['Status'] = "Invalid Formula Mode($formula_mode) Added..!Please Check..!";
$err_column_array[] = $error_info;
}else
if(!$form_ear_ded_input){
$error_info["Excel Row"] = "B$row And C$row";
$error_info['Status'] = "Invalid Formula Type($formula_type) and Out Column($out_column) Added..!Please Check..!";
$err_column_array[] = $error_info;
}
}
}
return $err_column_array;
}
//UTILITIES INSERT UPDATE FUNCTION
public function utilities_ins_upd_function($utilities_set_val,$month_check_val,$prime_module_id,$label_name,$logged_id,$today_date){
$sts = false;
$module_imp_upd_qry = 'UPDATE cw_modules SET import_module = "1" WHERE cw_modules.module_id in ("'.$prime_module_id.'","monthly_input") and cw_modules.trans_status = 1';
$module_imp_upd_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$module_imp_upd_qry')");
if($module_imp_upd_rslt){
//select query for get a form setting database show field details label name
$show_field_name_rslt = $this->get_master_show_field_details($prime_module_id);
$db_show_field_name = $show_field_name_rslt[0]['label_name'];
//CONDITION FOR INSERT AND UPDATE DEFAULT MODULE IMPORT EXCEL DETAILS
if($utilities_set_val === 1){
$import_excel_name = ucwords($prime_module_id)." Import";
//select already exist import excel details
$imp_excel_name_ext = $this->select_import_excel_details($import_excel_name);
$excel_name_id = (int)$imp_excel_name_ext[0]['prime_excel_format_id'];
$excel_column_name = $imp_excel_name_ext[0]['excel_column_name'];
if(!$excel_name_id){
$action = "insert";
$excel_column_name = $db_show_field_name.','.$label_name;
//insert excel import qry fucntion
$ins_excel_name_rslt = $this->ins_upd_import_excel_details($action,$prime_module_id,$import_excel_name,$excel_column_name,$excel_name_id,$logged_id,$today_date);
$insert_id = (int)$ins_excel_name_rslt[0]->ins_id;
if($insert_id){
$sts = true;
}
}else{
$action = "update";
$excel_column_name .= ','.$label_name;
//insert excel import qry fucntion
$upd_excel_name_rslt = $this->ins_upd_import_excel_details($action,$prime_module_id,$import_excel_name,$excel_column_name,$excel_name_id,$logged_id,$today_date);
if($upd_excel_name_rslt){
$sts = true;
}
}
}//CONDITION FOR INSERT AND UPDATE MONTHLY INPUT IMPORT EXCEL DETAILS
if($month_check_val === 1){
$prime_module_id = "monthly_input";
$mon_imp_excel_name = "Monthly Input Import";
//select already exist import excel details
$imp_excel_name_ext = $this->select_import_excel_details($mon_imp_excel_name);
$excel_name_id = (int)$imp_excel_name_ext[0]['prime_excel_format_id'];
$excel_column_name = $imp_excel_name_ext[0]['excel_column_name'];
if(!$excel_name_id){
$action = "insert";
$excel_column_name = 'employee_code,'.$label_name.'';
//insert excel import qry fucntion
$ins_excel_name_rslt = $this->ins_upd_import_excel_details($action,$prime_module_id,$mon_imp_excel_name,$excel_column_name,$excel_name_id,$logged_id,$today_date);
$insert_id = (int)$ins_excel_name_rslt[0]->ins_id;
if($insert_id){
$sts = true;
}
}else{
$action = "update";
if (strpos($excel_column_name, 'employee_code') !== false) {
$excel_column_name .= ','.$label_name;
}else{
if($excel_column_name){
$excel_column_name .= ',employee_code,'.$label_name;
}else{
$excel_column_name = 'employee_code,'.$label_name;
}
}
//insert excel import qry fucntion
$upd_excel_name_rslt = $this->ins_upd_import_excel_details($action,$prime_module_id,$mon_imp_excel_name,$excel_column_name,$excel_name_id,$logged_id,$today_date);
if($upd_excel_name_rslt){
$sts = true;
}
}
}
return $sts;
}
}
public function select_import_excel_details($import_excel_name){
$select_columns = '*';
$table_name = 'cw_util_excel_format';
$table_join = '';
$table_where = 'cw_util_excel_format.excel_name = "'.$import_excel_name.'" and cw_util_excel_format.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$imp_excel_name_ext = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
return $imp_excel_name_ext;
}
public function ins_upd_import_excel_details($action,$prime_module_id,$import_excel_name,$excel_column_name,$excel_name_id,$logged_id,$today_date){
if($action === "insert"){
$ins_excel_name_qry = 'INSERT INTO cw_util_excel_format (excel_module_id,import_type,excel_name,excel_table_name,excel_column_name,exist_column_name,trans_created_by,trans_created_date) VALUES ("'.$prime_module_id.'","1","'.$import_excel_name.'","cw_'.$prime_module_id.'","'.$excel_column_name.'","employee_code","'.$logged_id.'","'.$today_date.'")';
$ins_excel_name_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_excel_name_qry')");
$ins_excel_name_rslt = $ins_excel_name_info->result();
$ins_excel_name_info->next_result();
return $ins_excel_name_rslt;
}else
if($action === "update"){
$upd_excel_name_qry = 'UPDATE cw_util_excel_format SET excel_column_name = "'.$excel_column_name.'",trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'" WHERE cw_util_excel_format.prime_excel_format_id = "'. $excel_name_id .'" and cw_util_excel_format.trans_status = 1';
$upd_excel_name_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_excel_name_qry')");
return $upd_excel_name_rslt;
}
}
//select show field details from form setting
public function get_master_show_field_details($prime_module_id){
$select_columns = 'GROUP_CONCAT(cw_form_setting.label_name) as label_name';
$table_name = 'cw_form_setting';
$table_join = '';
$table_where = 'cw_form_setting.prime_module_id = "'.$prime_module_id.'"and (cw_form_setting.input_for in (1,32,33,34,49) or cw_form_setting.mandatory_field = 1) and cw_form_setting.input_view_type in (1,2) and cw_form_setting.field_show = 1 and cw_form_setting.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$show_field_name_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
return $show_field_name_rslt;
}
public function report_ins_upd_function($report_set_val,$master_rep_set_val,$prime_module_id,$prime_cat_id,$label_name,$logged_id,$today_date){
$sts = false;
$payroll_report_name = ucwords($prime_module_id)." Payroll Report";
$master_report_name = "All Employess Master Details";
//select query for get a menu name for report show
$pay_rep_main_menu_id = (int)$report_main_menu[0]['prime_menu_id'];
$select_columns = 'cw_main_menu.prime_menu_id';
$table_name = 'cw_main_menu';
$table_join = '';
$table_where = 'cw_main_menu.menu_name = "Payroll" and cw_main_menu.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$report_main_menu = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$report_main_menu_id = (int)$report_main_menu[0]['prime_menu_id'];
//select query for get a form setting database show field details label name
$show_field_name_rslt = $this->get_master_show_field_details($prime_module_id);
$db_show_field_name = $show_field_name_rslt[0]['label_name'];
$report_table_name = 'cw_'.$prime_module_id;
//CONDITION FOR INSERT AND UPDATE A PAYROLL REPORT DETAILS TO REPORT SETTING
if($report_set_val === 1){
$report_table_name = 'cw_transactions';
$date_filter = 1;
$date_column = 'cw_transactions.process_month';
//function for get a payroll report exist details
$report_name_ext = $this->select_report_details($payroll_report_name);
$report_name_id = (int)$report_name_ext[0]['prime_report_setting_id'];
$report_table_column = $report_name_ext[0]['table_column'];
if(!$report_name_id){
$report_table_column = 'cw_employees.role,cw_employees.emp_name,cw_employees.employee_code,'.$report_table_name.'.'.$label_name;
$action = "insert";
$report_table_name = "cw_employees,cw_transactions";
//insert report qry fucntion
$ins_rep_detail_rslt = $this->ins_upd_pay_report_details($action,$report_main_menu_id,$payroll_report_name,$report_table_name,$report_table_column,$report_name_id,$date_filter,$date_column,$logged_id,$today_date);
$insert_id = (int)$ins_rep_detail_rslt[0]->ins_id;
if($insert_id){
$ins_report_join_qry = 'INSERT INTO cw_report_table (join_for,line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,trans_created_by,trans_created_date) VALUES ("'.$insert_id.'","cw_employees","cw_employees.employee_code","inner","cw_transactions","cw_transactions.employee_code","1","'.$logged_id.'","'.$today_date.'")';
$ins_report_join_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_report_join_qry')");
$ins_report_join_rslt = $ins_report_join_info->result();
$ins_report_join_info->next_result();
$this->save_table_view($insert_id);
$sts = true;
}
}else{
$action = "update";
if($report_table_column){
if (strpos($report_table_column, 'cw_employees.role') === false) {
$report_table_column .= ',cw_employees.role';
}
}else{
if (strpos($report_table_column, 'cw_employees.role') === false) {
$report_table_column = 'cw_employees.role';
}
}
if (strpos($report_table_column, 'cw_employees.employee_code') === false) {
$report_table_column .= ',cw_employees.employee_code';
}
if (strpos($report_table_column, 'cw_employees.emp_name') === false) {
$report_table_column .= ',cw_employees.emp_name';
}
if (strpos($report_table_column, 'cw_employees.date_of_joining') === false) {
$report_table_column .= ',cw_employees.date_of_joining';
}
if(strpos($report_table_column, 'cw_transactions.'.$label_name) === false) {
$report_table_column .= ',cw_transactions.'.$label_name;
}
$report_table_name = "cw_employees,cw_transactions";
$upd_rep_detail_rslt = $this->ins_upd_pay_report_details($action,$report_main_menu_id,$payroll_report_name,$report_table_name,$report_table_column,$report_name_id,$date_filter,$date_column,$logged_id,$today_date);
if($upd_rep_detail_rslt){
$this->save_table_view($report_name_id);
$sts = true;
}
}
}//CONDITION FOR INSERT AND UPDATE MASTER DEFAULT DETAILS TO REPORT SETTING
if($master_rep_set_val === 1){
$report_table_name = 'cw_'.$prime_module_id;
$date_filter = 2;
$date_column = '';
//function for get a payroll report exist details
$report_name_ext = $this->select_report_details($master_report_name);
$report_name_id = (int)$report_name_ext[0]['prime_report_setting_id'];
$report_table_column = $report_name_ext[0]['table_column'];
$db_show_field_arr = explode(',',$db_show_field_name);
$db_show_field_name = implode(",$report_table_name.",$db_show_field_arr);
if(!$report_name_id){
$report_table_column = $report_table_name.'.'.$db_show_field_name;
if(strpos($report_table_column, $report_table_name.'.'.$label_name) === false) {
$report_table_column = $report_table_name.'.'.$db_show_field_name.','.$report_table_name.'.'.$label_name;
}
$action = "insert";
//insert report qry fucntion
$ins_rep_detail_rslt = $this->ins_upd_pay_report_details($action,$report_main_menu_id,$master_report_name,$report_table_name,$report_table_column,$report_name_id,$date_filter,$date_column,$logged_id,$today_date);
$insert_id = (int)$ins_rep_detail_rslt[0]->ins_id;
if($insert_id){
$this->save_table_view($insert_id);
$sts = true;
}
}else{
$action = "update";
if(strpos($report_table_column, $report_table_name.'.'.$label_name) === false) {
$report_table_column .= ','.$report_table_name.'.'.$label_name;
}
$upd_rep_detail_rslt = $this->ins_upd_pay_report_details($action,$report_main_menu_id,$master_report_name,$report_table_name,$report_table_column,$report_name_id,$date_filter,$date_column,$logged_id,$today_date);
if($upd_rep_detail_rslt){
$this->save_table_view($report_name_id);
$sts = true;
}
}
}
return $sts;
// }
}
public function select_report_details($report_name){
//select query for get a form view setting tab result
$select_columns = '*';
$table_name = 'cw_report_setting';
$table_join = '';
$table_where = 'cw_report_setting.report_name = "'.$report_name.'" and cw_report_setting.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$imp_excel_name_ext = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
return $imp_excel_name_ext;
}
public function ins_upd_pay_report_details($action,$report_main_menu_id,$report_name,$report_table_name,$report_table_column,$report_name_id,$date_filter,$date_column,$logged_id,$today_date){
if($action === "insert"){
$ins_report_detail_qry = 'INSERT INTO cw_report_setting (report_menu,report_name,report_for,table_info,table_column,date_filter,date_column,menu_type,trans_created_by,trans_created_date) VALUES ("'.$report_main_menu_id.'","'.$report_name.'","1","'.$report_table_name.'","'.$report_table_column.'","'.$date_filter.'","'.$date_column.'","2","'.$logged_id.'","'.$today_date.'")';
$ins_report_detail_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_report_detail_qry')");
$ins_report_detail_rslt = $ins_report_detail_info->result();
$ins_report_detail_info->next_result();
return $ins_report_detail_rslt;
}else
if($action === "update"){
$upd_report_name_qry = 'UPDATE cw_report_setting SET cw_report_setting.table_column = "'.$report_table_column.'",trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'" WHERE cw_report_setting.prime_report_setting_id = "'. $report_name_id .'" and cw_report_setting.trans_status = 1';
$upd_rep_detail_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_report_name_qry')");
return $upd_rep_detail_rslt;
}
}
//SAVE REPORT TABLE SORT VIEW /*SVK EDIT*/
public function save_table_view($report_id){
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d H:i:s");
$total_column = array();
$get_colums_info = 'select * from cw_report_setting WHERE prime_report_setting_id = "'.$report_id.'" and trans_status = 1';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$report_name = $colums_result[0]->report_name;
$report_for = $colums_result[0]->report_for;
$table_column = $colums_result[0]->table_column;
$table_column = explode(",",$table_column);
$add_colums_info = 'select GROUP_CONCAT(add_name) as colum_name from cw_report_add_column WHERE report_id = "'.$report_id.'" and trans_status = 1';
$add_colums_info = $this->db->query("CALL sp_a_run ('SELECT','$add_colums_info')");
$add_colums_result = $add_colums_info->result();
$add_colums_info->next_result();
$new_column = $add_colums_result[0]->colum_name;
if(!empty($new_column)){
$new_column = str_replace(",",",cw_transactions.",$new_column);
$new_column = "cw_transactions.".$new_column;
$new_column = explode(",",$new_column);
$total_column = array_merge($table_column,$new_column);
}else{
$total_column = $table_column;
}
$count = count($total_column);
$j=1;
$exits_count_qry = 'select GROUP_CONCAT(CONCAT_WS(".",module_column,table_column)) as colum_name from cw_report_table_view where report_id = "'.$report_id.'" and trans_status = 1';
$exits_count_info = $this->db->query("CALL sp_a_run ('SELECT','$exits_count_qry')");
$exits_count_result = $exits_count_info->row();
$exits_count_info->next_result();
$exit_col = $exits_count_result->colum_name;
if(!empty($exit_col)){
$exit_col = explode(",",$exit_col);
}else{
$exit_col = array();
}
$exit_count = count($exit_col);
$dlt_str = "";
for($i=0;$i<$exit_count;$i++){
$col_name = explode(".",$exit_col[$i]);
$module_column = 'cw_'.$col_name[0];
$col_name = $col_name[1];
$tbl_col = $module_column.'.'.$col_name;
if(!in_array($tbl_col, $total_column)){
$dlt_str .= str_replace('cw_','',$tbl_col).",";
}
}
$dlt_str = rtrim($dlt_str,',');
$module_column_rpl = '((module_column ="'.$dlt_str;
$module_column_rpl = str_replace(',','") or (module_column ="',$module_column_rpl);
$module_column_rpl = str_replace('.','" and table_column ="',$module_column_rpl).'"))';
$check_column = '';
if(empty($exit_col)){
for($i=0;$i<$count;$i++){
$col_name = explode(".",$total_column[$i]);
$module_column = str_replace('cw_','',$col_name[0]);
$col_name = $col_name[1];
$k = "";
//condition for change a table short to last in table
if($col_name === "net_pay"){
$k = $j;
$j = 998;
}else
if($col_name === "total_earnings"){
$k = $j;
$j = 999;
}else
if($col_name === "total_deductions"){
$k = $j;
$j = 1000;
}
$report_qry = 'INSERT INTO cw_report_table_view (report_id,report_name, report_for,module_column,table_column,table_sort,trans_created_by, trans_created_date) VALUES ("'.$report_id.'","'.$report_name.'","'.$report_for.'","'.$module_column.'","'.$col_name.'","'.$j.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$report_qry')");
if($col_name === "net_pay"){
$j = $k;
}else
if($col_name === "total_earnings"){
$j = $k;
}else
if($col_name === "total_deductions"){
$j = $k;
}
$j++;
$check_column .= "\"$col_name\",";
}
}else{
for($i=0;$i<$count;$i++){
$col_name = explode(".",$total_column[$i]);
$module_column = str_replace('cw_','',$col_name[0]);
$col_name = $col_name[1];
$tbl_col = $module_column.'.'.$col_name;
if(!in_array($tbl_col, $exit_col)){
$exits_count_qry = 'select count(*) as rlst_count from cw_report_table_view where report_id = "'.$report_id.'" and trans_status = 1';
$exits_count_info = $this->db->query("CALL sp_a_run ('SELECT','$exits_count_qry')");
$exits_count_result = $exits_count_info->row();
$exits_count_info->next_result();
$rlst_count = (int)$exits_count_result->rlst_count + 1;
$report_qry = 'INSERT INTO cw_report_table_view (report_id,report_name, report_for,module_column,table_column,table_sort,trans_created_by, trans_created_date) VALUES ("'.$report_id.'","'.$report_name.'","'.$report_for.'","'.$module_column.'","'.$col_name.'","'.$rlst_count.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$report_qry')");
}
$check_column .= "\"$col_name\",";
}
}
$check_column = rtrim($check_column,',');
if($check_column){
$upd_qry = 'UPDATE cw_report_table_view SET trans_status = 0 where trans_status = 1 and '.$module_column_rpl.'';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$upd_qry = "ALTER TABLE cw_report_table_view AUTO_INCREMENT = $count";
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
}
}
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE IN ARRAY
public function cat_qry_struct_function(){
$cat_tab_arr = array();
$select_columns = 'cw_category.prime_category_id,cw_category.category_name';
$table_name = 'cw_category';
$table_join = '';
$table_where = 'cw_category.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$cat_tab_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$cat_tab_arr = array_reduce($cat_tab_rslt, function($result, $arr) {
$result[$arr['category_name']] = $arr['prime_category_id'];
return $result;
}, array());
return $cat_tab_arr;
}
public function query_build_function($select_columns,$table_name,$table_join,$table_where){
$table_qry_detail = 'select '.$select_columns.' from '.$table_name.' '.$table_join.' where '.$table_where.'';
$table_qry_info = $this->db->query("CALL sp_a_run ('SELECT','$table_qry_detail')");
$table_qry_rslt = $table_qry_info->result_array();
$table_qry_info->next_result();
return $table_qry_rslt;
}
public function picklist_exist_check($table_name_col_arr,$module_exist_arr,$form_view_tab_arr,$field_type_array,$sheet,$highestRow,$highestColumnIndex,$key_data,$err_column_array){
for ($row = 1; $row <= $highestRow; $row++) {
$table_data = array();
$key_val_data = array();
$col_data = array();
$error_info = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$col_data[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$input_for = ucwords(str_replace("_"," ",$key_val_data['input_for']));
$label_name = strtolower(str_replace(" ","_",$key_val_data['label_name']));
$field_type = $key_val_data['field_type'];
$picklist_table = $key_val_data['picklist_table'];
$picklist_column = $key_val_data['picklist_column'];
$picklist_value = $key_val_data['picklist_value'];
$picklist_col_arr = explode(",",$picklist_column);
$picklist_col_arr = array_combine($picklist_col_arr,$picklist_col_arr);
if($field_type === "Picklist"){
/* echo "<pre>:hello";
print_r($module_exist_arr); */
if(($picklist_table && !$picklist_column) || (!$picklist_table && $picklist_column)){
$error_info["Excel Row"] = "F$row And G$row";
$error_info['Status'] = "Picklist Table and Picklist Column both are should Empty..! or both are should not Empty..!";
$err_column_array[] = $error_info;
}else
if($picklist_table && $picklist_value){
$error_info["Excel Row"] = "F$row";
$error_info['Status'] = "If You are Create a New Picklist then Picklist Table Column should Empty..!";
$err_column_array[] = $error_info;
}else{
//for old picklist table check validation
if($picklist_table && $picklist_column){
if($table_name_col_arr[$picklist_table]){
$pick_table_col_arr = $table_name_col_arr[$picklist_table];
$error_col_name = array_diff_key($picklist_col_arr,$pick_table_col_arr);
if($error_col_name){
$error_info["Excel Row"] = "G$row";
$error_info['Status'] = "Invalid Picklist Column Name you are Choosed..!Please Check Picklist Column..!";
$err_column_array[] = $error_info;
}else
if(count($picklist_col_arr) < 2){
$error_info["Excel Row"] = "G$row";
$error_info['Status'] = "Don't Map one column Name for Picklist Column..!Please Check to add a Two Picklist Column..!";
$err_column_array[] = $error_info;
}
}else{
$error_info["Excel Row"] = "F$row";
$error_info['Status'] = "Picklist Table Name($picklist_table) is not Exit in our Database..!Please Check..!";
$err_column_array[] = $error_info;
}
}else//for new picklist table check validation for both picklist value condition(empty & insert)
if($picklist_value || ($picklist_table && $picklist_column && !$picklist_value)){
$check_table_name = "cw_$label_name";
if($table_name_col_arr[$check_table_name]){
$error_info["Excel Row"] = "D$row";
$error_info['Status'] = "For In New Picklist Same Table Name ($label_name) Already Exist Please Change your Label Name";
$err_column_array[] = $error_info;
}
}
}
}
}
}
return $err_column_array;
}
public function picklist_module_create($prime_module_id,$label_name,$prime_cat_id,$picklist_value){
$err_column_array = array();
$module_id = strtolower(str_replace(" ","_",$label_name));
$module_name = ucwords(str_replace("_"," ",$label_name));
$rights_to = "1,2";
$module_for = $prime_cat_id;
$quicklink = '';
$show_module = 1;
$import_module = 0;
$pdf_module = 0;
$module_type = "dynamic";
$menu_type = '';
$upload_menu_icon = '';
$custom_module = 0;
$sms_setting = 0;
$email_setting = 0;
$left_freeze = 3;
$right_freeze = 3;
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
//SELECT QUERY FOR CHECK A MODULE NAME EXIST
$select_columns = 'count(*) as rslt_count';
$table_name = 'cw_modules';
$table_join = '';
$table_where = 'cw_modules.module_id = "'.$module_id.'"';
//FUNCTION USED FOR CREATE A QRY
$module_exist_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
if((int)$module_exist_rslt['rslt_count'] === 0){
//SELECT QUERY FOR GET A MAIN MENU ID NAME
$select_columns = 'cw_main_menu.prime_menu_id';
$table_name = 'cw_main_menu';
$table_join = '';
$table_where = 'cw_main_menu.trans_status = 1 and cw_main_menu.menu_name = "Payroll"';
//FUNCTION USED FOR CREATE A QRY
$main_menu_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$map_menu_to = (int)$main_menu_rslt[0]['prime_menu_id'];
//SELECT QUERY FOR GET A SUB MENU ID NAME
$select_columns = 'cw_sub_menu.prime_sub_menu_id';
$table_name = 'cw_sub_menu';
$table_join = 'inner join cw_main_menu on cw_main_menu.prime_menu_id = cw_sub_menu.map_main_menu';
$table_where = 'cw_sub_menu.trans_status = 1 and cw_main_menu.menu_name = "Payroll" and cw_sub_menu.sub_menu_name = "Popup Data"';
//FUNCTION USED FOR CREATE A QRY
$sub_menu_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$sub_map_menu_to = (int)$sub_menu_rslt[0]['prime_sub_menu_id'];
//SELECT QUERY FOR GET A MODULE SORT ORDER NUMBER
$select_columns = 'count(*) as rslt_count';
$table_name = 'cw_modules';
$table_join = '';
$table_where = 'cw_modules.trans_status = 1 and cw_modules.menu_id not in (1,2)';
//FUNCTION USED FOR CREATE A QRY
$sort_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$sort_order = (int)$sort_rslt[0]['rslt_count'] + 1;
//modules insert qry
$module_qry = 'INSERT INTO cw_modules (sort,module_id,module_name,module_type,menu_id,sub_menu_id,module_for,rights_to,show_module,quicklink,import_module,pdf_module,custom_module,sms_setting,email_setting,left_freeze,right_freeze,menu_type,menu_icon,trans_created_by, trans_created_date) VALUES ("'.$sort_order.'","'.$module_id.'","'.$module_name.'","'.$module_type.'","'.$map_menu_to.'","'.$sub_map_menu_to.'","'.$module_for.'","'.$rights_to.'","'.$show_module.'","'.$quicklink.'","'.$import_module.'","'.$pdf_module.'","'.$custom_module.'","'.$sms_setting.'","'.$email_setting.'","3","3","'.$menu_type.'","'.$upload_menu_icon.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$module_qry')");
//permission insert qry
$permissions_qry = 'INSERT INTO cw_permissions (permission_id, module_id) VALUES ("'.$module_id.'","'.$module_id.'")';
$this->db->query("CALL sp_a_run ('RUN','$permissions_qry')");
//grants insert qry
$grants_qry = 'INSERT INTO cw_grants (permission_id, prime_employees_id, access_add, access_update,access_delete,access_search,access_export,access_import) VALUES ("'.$module_id.'","1","1","1","1","1","1","1")';
$this->db->query("CALL sp_a_run ('RUN','$grants_qry')");
$this->creat_file_structure($module_id);
//FORM VIEW SETTING INSERT CONDITION AND FUNCTION
$form_view_label_name = $module_id.'_details';
$form_view_heading = ucwords(str_replace("_"," ",$form_view_label_name));
$form_view_data = array(
'prime_form_view_id' => 0,
'prime_view_module_id' => $module_id,
'form_view_type' => 2,
'form_view_type_mode' => 0,
'form_view_label_name' => $form_view_label_name,
'form_view_heading' => $form_view_heading,
'form_view_for' => $prime_cat_id,
'form_view_show' => 1,
);
$form_view_data = json_encode($form_view_data);
$count_info = $this->db->query("CALL sp_form_view_setting_crud ('SORT_COUNT', '$form_view_data','$logged_id')");
$count_result = $count_info->result();
$count_info->next_result();
$form_view_data = json_decode($form_view_data,true);
$field_sort = (int)$count_result[0]->sort_count + 1;
$form_view_data['form_view_sort'] = $field_sort;
$form_view_data = json_encode($form_view_data);
//INSERT A FORM VIEW DETAILS TO FORM VIEW SETTING MODULE
$info = $this->db->query("CALL sp_form_view_setting_crud ('SAVE', '$form_view_data','$logged_id')");
$result = $info->result();
$info->next_result();
if((int)$result){
//FORM SETTING INSERT CONDITION AND FUNCTION
//PICKLIST TABLE CREATE FUNCTION
$select_columns = 'cw_form_view_setting.prime_form_view_id';
$table_name = 'cw_form_view_setting';
$table_join = '';
$table_where = 'cw_form_view_setting.trans_status = 1 and cw_form_view_setting.prime_view_module_id = "'.$module_id.'" and cw_form_view_setting.form_view_label_name = "'.$form_view_label_name.'"';
//FUNCTION USED FOR CREATE A QRY
$form_input_for_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$form_input_for = (int)$form_input_for_rslt[0]['prime_form_view_id'];
$input_view_type = 1;
//FORM SETTING INSERT ARRAY BUILD COND
$table_data = array(
'prime_form_id' => 0,
'prime_module_id' => $module_id,
'input_view_type' => $input_view_type,
'input_for' => $form_input_for,
'field_type' => 1,
'label_name' => $module_id,
'view_name' => $module_name,
'short_name' => $module_name,
'field_length' => '100',
'text_type' => 1,
'date_type' => '',
'field_decimals' => '',
'pick_list_type' => NULL,
'pick_list_import' => NULL,
'field_isdefault' => 1,
'default_value' => 0,
'mandatory_field' => 1,
'upd_mandatory_field' => 0,
'unique_field' => 0,
'field_show' => 1,
'table_show' => 1,
'search_show' => 0,
'field_for' => $prime_cat_id,
'pick_display_value' => NULL,
'upload_extension' => '',
'upload_file_size' => 500,
'file_type' => '',
'transaction_type' => 4,
'gross_check' => 0,
'taxable_check' => 0,
'earn_month_check' => 0,
'earn_payroll_check' => 0,
'ded_payroll_check' => 0,
'benefit_check' => 0,
'increment_check' => 0,
'arrear_pf_check' => 0,
'fandf_check' => 0,
'deduction_check' => 0,
'deduction_month_check' => 0,
'loan_check' => 0,
'uniform_check' => 0,
'edit_read' => 0,
'picklist_data' => 0,
'duplicate_data' => 0,
'field_sort' => 1,
'table_sort' => 1,
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_form_setting_crud ('SAVE', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
if($this->save_table($module_id,$input_view_type,$form_input_for,$form_view_label_name)){
if($picklist_value){
$picklist_val_arr = explode(',',$picklist_value);
//AFTER CREATE A PICKLIST TABLE THEN WE INSERT PICKLIST ROW VALUES
$picklist_val = implode('","'.$logged_id.'","'.$date.'"),("',$picklist_val_arr);
//INSERT QRY FOR NEW PICKLIST TABLE
$ins_picklist_qry = 'insert into cw_'.$module_id.'('.$module_id.',trans_created_by,trans_created_date) value ("'.$picklist_val.'","'.$logged_id.'","'.$date.'")';
$ins_picklist_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_picklist_qry')");
$ins_picklist_rslt = $ins_picklist_info->result();
$ins_picklist_info->next_result();
$ins_id = $ins_picklist_rslt[0]->ins_id;
if((int)$ins_id){
return true;
}
}else{
return true;
}
}
}
}else{
$error_info["Excel Row"] = "D$row";
$error_info['Status'] = "For In Picklist Same Module Name ($label_name) Already Exist..! Please Change your Label Name";
$err_column_array[] = $error_info;
return $err_column_array;
}
}
public function label_name_exist($form_view_tab_arr,$field_type_array,$sheet,$highestRow,$highestColumnIndex,$key_data,$err_column_array){
for ($row = 1; $row <= $highestRow; $row++) {
$table_data = array();
$key_val_data = array();
$col_data = array();
$error_info = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$col_data[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$input_for = ucwords(str_replace("_"," ",$key_val_data['input_for']));
$label_name = strtolower(str_replace(" ","_",$key_val_data['label_name']));
$field_type = ucwords(str_replace("_"," ",$key_val_data['field_type']));
$field_type_id = array_search($field_type,$field_type_array);
if($input_for){
if(!$field_type_id){
$error_info["Excel Row"] = "C$row";
$error_info['Status'] = "Please Choose Valid Field Type ($field_type)..!";
$err_column_array[] = $error_info;
}else{
$module_form_arr = $form_view_tab_arr[$input_for];
if(!empty($module_form_arr)){
$prime_module_id = $module_form_arr['prime_view_module_id'];
//FUNCTION FOR CHECK A MYSQL RESERVED KEYWORDS FOR LABEL NAME AND ALSO CHECK A LABEL NAME EXIST
$label_sts_arr = $this->check_reserved_words($label_name,$prime_module_id);
if(!empty($label_sts_arr)){
$sts = $label_sts_arr['success'];
$message = $label_sts_arr['message'];
if($sts === "false"){
$error_info["Excel Row"] = "D$row";
$error_info['Status'] = $message;
$err_column_array[] = $error_info;
}
}
}else{
$error_info["Excel Row"] = "A$row";
$error_info['Status'] = "Please Check it.!Invalid Data Present in Input_for Column ..!";
$err_column_array[] = $error_info;
}
}
}else{
$error_info["Excel Row"] = "A$row";
$error_info['Status'] = "Please Check it.!Input_for Column Should not Empty..!";
$err_column_array[] = $error_info;
}
}
}
return $err_column_array;
}
//18JUNE2019 Update keywords and label name
//checking reserved word and keywords check label name
public function check_reserved_words($label_name,$prime_module_id){
$sts_array = array();
$reserved_qry = 'select count(*) as exist_count from `cw_reserved_words` where reserved_word = "'.$label_name.'"';
$reserved_info = $this->db->query("CALL sp_a_run ('SELECT','$reserved_qry')");
$reserved_result = $reserved_info->result();
$reserved_info->next_result();
$reserved_count = $reserved_result[0]->exist_count;
if((int)$reserved_count > 0){
$sts_array = (array('success' => "false",'message' =>"Mysql Reserved Keywords not Allowed to Lable Name for($label_name)"));
return $sts_array;
}else{
$label_name = str_replace(" ","_",$label_name);
$label_name_exist_qry = 'select count(*) as label_name_exist from `cw_form_view_setting` where prime_view_module_id = "'.$prime_module_id.'" and form_view_label_name = "'.$label_name.'" and trans_status = 1';
$label_name_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$label_name_exist_qry')");
$label_name_exist_rslt = $label_name_exist_info->result();
$label_name_exist_info->next_result();
$label_name_count = $label_name_exist_rslt[0]->label_name_exist;
$label_name_qry = 'SELECT count(*) as label_name_counts FROM cw_form_setting WHERE prime_module_id = "'.$prime_module_id.'" and label_name = "'.$label_name.'" and trans_status = 1';
$label_name_info = $this->db->query("CALL sp_a_run ('SELECT','$label_name_qry')");
$label_name_rslt = $label_name_info->result();
$label_name_info->next_result();
$label_name_count_exist = $label_name_rslt[0]->label_name_counts;
if((int)$label_name_count > 0){
$sts_array = (array('success' => "false",'message' =>"For Label Name($label_name) Already Exist..!"));
return $sts_array;
}else{
if($label_name_count_exist >0){
$sts_array = (array('success' => "false",'message' =>"For Label Name($label_name) Already Exist..!"));
return $sts_array;
}else{
$sts_array = (array('success' => "true",'message' =>"Procced..!"));
return $sts_array;
}
}
}
}
function creat_file_structure($module_id){
$ucfirst = ucfirst($module_id);
$strtolower = strtolower($module_id);
$controller_file_name = $ucfirst.".php";
$controller_file_name = $ucfirst.".php";
$controller_file = file_get_contents('module_creation/controllers.php', true);
$controller_file = str_replace("@MODULE_NAME@",$ucfirst, $controller_file);
$controller_file = str_replace("@MODULE_NAME_CONSTRUCT@",$strtolower, $controller_file);
$oldmask = umask(0);
fopen("./application/controllers/$controller_file_name", "w");
file_put_contents("./application/controllers/$controller_file_name",$controller_file);
if(!file_exists("./application/views/$strtolower")) {
mkdir("./application/views/$strtolower", 0777, true);
}
$form_file = file_get_contents('module_creation/form.php', true);
fopen("./application/views/$strtolower/form.php", "w");
file_put_contents("./application/views/$strtolower/form.php",$form_file);
$import_file = file_get_contents('module_creation/import.php', true);
fopen("./application/views/$strtolower/import.php", "w");
file_put_contents("./application/views/$strtolower/import.php",$import_file);
$manage_file = file_get_contents('module_creation/manage.php', true);
fopen("./application/views/$strtolower/manage.php", "w");
file_put_contents("./application/views/$strtolower/manage.php",$manage_file);
$print_file = file_get_contents('module_creation/print.php', true);
fopen("./application/views/$strtolower/print.php", "w");
file_put_contents("./application/views/$strtolower/print.php",$print_file);
umask($oldmask);
return true;
}
public function save_table($prime_module_id,$input_view_type,$input_for,$form_view_label_name){
if(!$prime_module_id){
return false;
}
if((int)$input_view_type === 3){
$table_data = array( 'prime_module_id' => $prime_module_id ,'input_view_type' => '3');
$table_data = json_encode($table_data);
$table_name = $prime_module_id."_".$form_view_label_name;
return $this->save_rowset_table($table_data,$input_for,$table_name);
}else{
$table_data = array( 'prime_module_id' => $prime_module_id ,'input_view_type' => '1');
$table_data = json_encode($table_data);
return $this->save_common_table($table_data);
}
}
// SAVE COMMON PRIME AND CUSTOM TABLE
public function save_common_table($table_data){
$created_on = date("Y-m-d H:i:s");
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
$db_name = $this->config->item("db_name");
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL",15=>"time NULL DEFAULT NULL");
$prime_table_name = $this->db->dbprefix($prime_module_id);
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = '$prime_table_name'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$prime_table_col = "";
if((int)$table_count === 1){
$prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = '$prime_table_name' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$prime_table_col = $prime_table_query->result_array();
}
$prime_id = "prime_".$prime_module_id."_id";
$prime_line = "$prime_id int(11) NOT NULL AUTO_INCREMENT,";
$prime_line_alt = "CHANGE $prime_id $prime_id int(11) NOT NULL AUTO_INCREMENT,";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$upd_mandatory_field = $setting->upd_mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
$loan_check = $setting->loan_check;
$date_type = $setting->date_type;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if((int)$field_isdefault === 1){
if($prime_table_col){
$prime_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($prime_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$prime_uniq = " ADD UNIQUE($label_name),";
}
}else{
$result_key = array_keys($prime_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(!empty($result_key)){
$prime_uniq = "drop INDEX $label_name,";
}
}
if(array_search($label_name, array_column($prime_table_col, 'col_name'))){
$prime_line_alt .= "CHANGE $label_name $label_name $data_type, $prime_uniq";
}else{
$last_col = end($prime_table_col);
$prime_last_col = $last_col['col_name'];
$prime_line_alt .= "ADD $label_name $data_type AFTER $prime_last_col, $prime_uniq";
}
}else{
$prime_uniq = "";
if((int)$unique_field === 1){
$prime_uniq = " UNIQUE($label_name),";
}
$prime_line .= "$label_name $data_type, $prime_uniq ";
}
}
}
$prime_line_alt = rtrim($prime_line_alt,", ");
$prime_line_alt = rtrim($prime_line_alt,",");
$prime_line .= "trans_created_by INT(11) NULL DEFAULT '0', trans_created_date DATETIME NULL DEFAULT NULL, trans_updated_by INT(11) NULL DEFAULT '0', trans_updated_date DATETIME NULL DEFAULT NULL, trans_deleted_by INT(11) NULL DEFAULT '0', trans_deleted_date DATETIME NULL DEFAULT NULL,trans_status INT(11) NULL DEFAULT '1',PRIMARY KEY (`$prime_id`)";
//CREATE AND ALTER TABLE
$prime_table_query = "CREATE TABLE IF NOT EXISTS $prime_table_name($prime_line)";
if((int)$table_count === 0){
$prime_table_query = "CREATE TABLE IF NOT EXISTS $prime_table_name($prime_line)";
$this->db->query($prime_table_query);
$this->update_picklist($prime_module_id);
$this->save_transactions_table($table_data);
$this->save_transactions_dlt_table($table_data);
$this->save_monthly_table($table_data);
$this->save_ind_tax_table($table_data);
$this->save_custom_table($table_data);
// $this->save_custom_log_table($table_data);
return true;
}else{
$prime_table_query_alt = "ALTER TABLE $prime_table_name $prime_line_alt";
$this->db->query($prime_table_query_alt);
$this->update_picklist($prime_module_id);
$this->save_transactions_dlt_table($table_data);
$this->save_transactions_table($table_data);
$this->save_monthly_table($table_data);
$this->save_ind_tax_table($table_data);
$this->save_custom_table($table_data);
return true;
}
}
// SAVE ROWSET TABLE
public function save_rowset_table($table_data,$view_input_for,$table_name){
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$db_name = $this->config->item("db_name");
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL",15=>"time NULL DEFAULT NULL");
$row_table_name = $this->db->dbprefix($table_name);
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = '$row_table_name'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$row_table_col = "";
if((int)$table_count === 1){
$row_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = '$row_table_name' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$row_table_col = $row_table_query->result_array();
}
$rowset_line = "";
$rowset_line_alt = "";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_for = (int)$setting->input_for;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$upd_mandatory_field = $setting->upd_mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
$date_type = $setting->date_type;
if((int)$view_input_for === (int)$input_for){
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if($row_table_col){
$prime_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($row_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$prime_uniq = " ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($row_table_col, 'col_name'))){
$rowset_line_alt .= "CHANGE $label_name $label_name $data_type, $prime_uniq";
}else{
$last_col = end($row_table_col);
$prime_last_col = $last_col['col_name'];
$rowset_line_alt .= "ADD $label_name $data_type AFTER $prime_last_col, $prime_uniq";
}
}else{
$prime_uniq = "";
if((int)$unique_field === 1){
$prime_uniq = " UNIQUE($label_name),";
}
$rowset_line .= "$label_name $data_type, $prime_uniq ";
}
}
}
$rowset_line = rtrim($rowset_line,", ");
$rowset_line = rtrim($rowset_line,",");
$rowset_line_alt = rtrim($rowset_line_alt,", ");
$rowset_line_alt = rtrim($rowset_line_alt,",");
//CREATE AND ALTER TABLE
if((int)$table_count === 0){
if($rowset_line){
$row_table_query = "CREATE TABLE IF NOT EXISTS $row_table_name($rowset_line)";
$this->db->query($row_table_query);
$this->update_picklist($prime_module_id);
}
$this->save_custom_rowset_table($table_data,$view_input_for,$table_name);
return true;
}else{
$row_table_query_alt = "ALTER TABLE $row_table_name $rowset_line_alt";
if($rowset_line_alt){
$this->db->query($row_table_query_alt);
$this->update_picklist($prime_module_id);
}
$this->save_custom_rowset_table($table_data,$view_input_for,$table_name);
return true;
}
}
// CUSTOM PICK LIST TABLE
public function update_picklist($prime_module_id){
if(!$prime_module_id){
return false;
}
$pick_query = $this->db->query("SELECT * FROM cw_form_setting where FIND_IN_SET (field_type,'5,7') and pick_list_type = '2' and prime_module_id = '$prime_module_id'");
$pick_rslt = $pick_query->result();
foreach($pick_rslt as $pick){
$label_name = $pick->label_name;
$prime_form_id = $pick->prime_form_id;
$pick_list = $pick->pick_list;
$pick_table = $pick->pick_table;
$pick_list_array = explode(",",$pick_list);
//Changed by Sathish BSK on 06Feb2020
$count_query = $this->db->query("SELECT MAX(CAST(SUBSTRING(pick_table,8) AS UNSIGNED)) as ct_count FROM cw_form_setting where FIND_IN_SET (field_type,'5,7') and pick_list_type = '2' and pick_table != ''");
$count_rslt = $count_query->result();
$ct_count = $count_rslt[0]->ct_count;
if($ct_count > 0){
$ct_count = $ct_count+ 1;
}else{
$ct_count = 1;
}
if($pick_table){
$table_value = $pick_table."_value";
$table_status = $pick_table."_status";
$check_name = $this->db->dbprefix("zct_");
// Update required only for custom tabel
if(strpos($pick_table, $check_name) !== false) {
$this->db->query("UPDATE $pick_table SET $table_status = '0'");
$table_qry = 'UPDATE '.$pick_table.' SET '.$table_status.' = "0"';
foreach($pick_list_array as $list){
$exist_query = $this->db->query("SELECT count(*) as exist_count FROM $pick_table where $table_value = '$list'");
$exist_rslt = $exist_query->result();
if((int)$exist_rslt[0]->exist_count === 0){
$this->db->query("INSERT INTO $pick_table ($table_value) VALUES ('$list')");
$table_qry = 'INSERT INTO '.$pick_table.' ('.$table_value.') VALUES ("'.$list.'")';
}else{
$this->db->query("UPDATE $pick_table SET $table_status = '1' WHERE FIND_IN_SET($table_value, '$pick_list')");
$table_qry = 'UPDATE '.$pick_table.' SET '.$table_status.' = "1" WHERE FIND_IN_SET('.$table_value.',"'.$pick_list.'")';
}
}
}
}else{
$table_name = "zct_$ct_count";
$table_name = $this->db->dbprefix($table_name);
$table_id = $table_name."_id";
$table_value = $table_name."_value";
$table_status = $table_name."_status";
$tabel_col = "$table_id int(11) NOT NULL AUTO_INCREMENT,$table_value varchar(150) NULL DEFAULT '0',$table_status INT(11) NULL DEFAULT '1', PRIMARY KEY (`$table_id`)";
$table_query = "CREATE TABLE IF NOT EXISTS $table_name($tabel_col)";
if($this->db->query($table_query)){
foreach($pick_list_array as $list){
$this->db->query("INSERT INTO $table_name ($table_value) VALUES ('$list')");
$table_qry = 'INSERT INTO '.$table_name.' ('.$table_value.') VALUES ("'.$list.'")';
}
}
$this->db->query("UPDATE cw_form_setting SET pick_table = '$table_name' WHERE prime_form_id = '$prime_form_id'");
$table_qry = 'UPDATE cw_form_setting SET pick_table = "'.$table_name.'" WHERE prime_form_id = "'.$prime_form_id.'"';
}
}
}
/* ==============================================================*/
/* ===================== DYNAMIC TBALE - END ====================*/
/* ==============================================================*/
/* ==============================================================*/
/* =========== TRANSACTION TABLE SAVE DATA - START ==============*/
/* ==============================================================*/
//Start date 24-12-2018 Jaffer
public function save_transactions_table($table_data){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
$db_name = $this->config->item("db_name");
//only employees table based transaction table
if($prime_module_id != "employees"){
return false;
}
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL");
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = 'cw_transactions'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$trans_table_col = "";
if((int)$table_count === 1){
$trans_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_transactions' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$trans_table_col = $trans_table_query->result_array();
}
$trans_line = "";
$trans_line_alt = "";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$upd_mandatory_field = $setting->upd_mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
$transaction_type = $setting->transaction_type;
$loan_check = $setting->loan_check;
$date_type = $setting->date_type;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if(((int)$transaction_type === 1) || ((int)$transaction_type === 2) || ((int)$transaction_type === 3)){
if($trans_table_col){
$trans_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($trans_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$trans_uniq = "";//" ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($trans_table_col, 'col_name'))){
$trans_line_alt .= "CHANGE $label_name $label_name $data_type, $trans_uniq";
if((int)$loan_check === 1){
$trans_line_alt .= "CHANGE $label_name"."_total"." $label_name"."_total decimal(15,2) NULL DEFAULT '0' AFTER $label_name,CHANGE $label_name"."_installments"." $label_name"."_installments decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_total ,CHANGE $label_name"."_instal_count"." $label_name"."_instal_count decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_installments,CHANGE $label_name"."_balance"." $label_name"."_balance decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_instal_count,";
}
}else{
$last_col = end($trans_table_col);
$trans_last_col = $last_col['col_name'];
$trans_line_alt .= "ADD $label_name $data_type AFTER $trans_last_col, $trans_uniq";
if((int)$loan_check === 1){ // For Create Loan Fields
$trans_line_alt .= "ADD $label_name"."_total"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name,ADD $label_name"."_installments"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_total,ADD $label_name"."_instal_count"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_installments,ADD $label_name"."_balance"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_instal_count,";
}
}
}else{
$trans_uniq = "";
if((int)$unique_field === 1){
$trans_uniq = "";//" UNIQUE($label_name),";
}
$trans_line .= "$label_name $data_type, $trans_uniq ";
}
}
}
$trans_line = rtrim($trans_line,", ");
$trans_line = rtrim($trans_line,",");
$trans_line_alt = rtrim($trans_line_alt,", ");
$trans_line_alt = rtrim($trans_line_alt,",");
//ADD AND CHANGE ALTER TABLE
if((int)$table_count === 0){
if($trans_line){
$trans_table_query = "CREATE TABLE IF NOT EXISTS cw_transactions($trans_line)";
$this->db->query($trans_table_query);
}
return true;
}else{
if($trans_line_alt){
$trans_table_query_alt = "ALTER TABLE cw_transactions $trans_line_alt";
$this->db->query($trans_table_query_alt);
}
return true;
}
}
//Deleted Transaction Data BSK - 13NOV2020
public function save_transactions_dlt_table($table_data){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
$db_name = $this->config->item("db_name");
//only employees table based transaction table
if($prime_module_id != "employees"){
return false;
}
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL");
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = 'cw_transactions'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$trans_table_col = "";
if((int)$table_count === 1){
$trans_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_transactions_dlt' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$trans_table_col = $trans_table_query->result_array();
}
$trans_line = "";
$trans_line_alt = "";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$upd_mandatory_field = $setting->upd_mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
$transaction_type = $setting->transaction_type;
$loan_check = $setting->loan_check;
$date_type = $setting->date_type;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if(((int)$transaction_type === 1) || ((int)$transaction_type === 2) || ((int)$transaction_type === 3)){
if($trans_table_col){
$trans_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($trans_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$trans_uniq = "";//" ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($trans_table_col, 'col_name'))){
$trans_line_alt .= "CHANGE $label_name $label_name $data_type, $trans_uniq";
if((int)$loan_check === 1){
$trans_line_alt .= "CHANGE $label_name"."_total"." $label_name"."_total decimal(15,2) NULL DEFAULT '0' AFTER $label_name,CHANGE $label_name"."_installments"." $label_name"."_installments decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_total ,CHANGE $label_name"."_instal_count"." $label_name"."_instal_count decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_installments,CHANGE $label_name"."_balance"." $label_name"."_balance decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_instal_count,";
}
}else{
$last_col = end($trans_table_col);
$trans_last_col = $last_col['col_name'];
$trans_line_alt .= "ADD $label_name $data_type AFTER $trans_last_col, $trans_uniq";
if((int)$loan_check === 1){ // For Create Loan Fields
$trans_line_alt .= "ADD $label_name"."_total"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name,ADD $label_name"."_installments"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_total,ADD $label_name"."_instal_count"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_installments,ADD $label_name"."_balance"." decimal(15,2) NULL DEFAULT '0' AFTER $label_name"."_instal_count,";
}
}
}else{
$trans_uniq = "";
if((int)$unique_field === 1){
$trans_uniq = "";//" UNIQUE($label_name),";
}
$trans_line .= "$label_name $data_type, $trans_uniq ";
}
}
}
$trans_line = rtrim($trans_line,", ");
$trans_line = rtrim($trans_line,",");
$trans_line_alt = rtrim($trans_line_alt,", ");
$trans_line_alt = rtrim($trans_line_alt,",");
//ADD AND CHANGE ALTER TABLE
if((int)$table_count === 0){
if($trans_line){
$trans_table_query = "CREATE TABLE IF NOT EXISTS cw_transactions_dlt($trans_line)";
$this->db->query($trans_table_query);
}
return true;
}else{
if($trans_line_alt){
$trans_table_query_alt = "ALTER TABLE cw_transactions_dlt $trans_line_alt";
$this->db->query($trans_table_query_alt);
}
return true;
}
}
/* ==============================================================*/
/* =========== TRANSACTION TABLE SAVE DATA - END ================*/
/* ==============================================================*/
/* ==============================================================*/
/* =========== MONTHLY TABLE SAVE DATA - START ==================*/
/* ==============================================================*/
//Start date 27-12-2018 Jaffer
public function save_monthly_table($table_data){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
$db_name = $this->config->item("db_name");
//only monthly input checked based monthly table
if($prime_module_id != "employees"){
return false;
}
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL");
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = 'cw_monthly_input'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$month_table_col = "";
if((int)$table_count === 1){
$month_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_monthly_input' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$month_table_col = $month_table_query->result_array();
}
$month_line = "";
$month_line_alt = "";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$upd_mandatory_field = $setting->upd_mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
$earn_month_check = $setting->earn_month_check;
$deduction_month_check = $setting->deduction_month_check;
$loan_check = $setting->loan_check;
$date_type = $setting->date_type;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if(((int)$earn_month_check === 1) || ((int)$deduction_month_check === 1)){
if($month_table_col){
$month_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($month_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$month_uniq = "";//" ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($month_table_col, 'col_name'))){
$month_line_alt .= "CHANGE $label_name $label_name $data_type, $month_uniq";
}else{
$last_col = end($month_table_col);
$month_last_col = $last_col['col_name'];
$month_line_alt .= "ADD $label_name $data_type AFTER $month_last_col, $month_uniq";
}
}else{
$month_uniq = "";
if((int)$unique_field === 1){
$month_uniq = "";//" UNIQUE($label_name),";
}
$month_line .= "$label_name $data_type, $month_uniq ";
}
}
}
$month_line = rtrim($month_line,", ");
$month_line = rtrim($month_line,",");
$month_line_alt = rtrim($month_line_alt,", ");
$month_line_alt = rtrim($month_line_alt,",");
//ADD AND CHANGE ALTER TABLE
if((int)$table_count === 0){
if($month_line){
$month_table_query = "CREATE TABLE IF NOT EXISTS cw_monthly_input($month_line)";
$this->db->query($month_table_query);
}
return true;
}else{
if($month_line_alt){
$month_table_query_alt = "ALTER TABLE cw_monthly_input $month_line_alt";
$this->db->query($month_table_query_alt);
}
return true;
}
}
/* ==============================================================*/
/* =========== MONTHLY TABLE SAVE DATA - END ====================*/
/* ==============================================================*/
//21SEP2019 TO TAX CALCULATOR
/* ==============================================================*/
/* =========== INDIVIDUAL TABLE DATA - START ==================*/
/* ==============================================================*/
public function save_ind_tax_table($table_data){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
$db_name = $this->config->item("db_name");
//only monthly input checked based monthly table
if($prime_module_id != "employees"){
return false;
}
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL");
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = 'cw_tax_ind_cal'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$tax_table_col = "";
if((int)$table_count === 1){
$tax_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_tax_ind_cal' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$tax_table_col = $tax_table_query->result_array();
}
$tax_line = "";
$tax_line_alt = "";
foreach($form_setting as $setting){
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$taxable_check = $setting->taxable_check;
$date_type = $setting->date_type;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if((int)$taxable_check === 1){
if($tax_table_col){
if(array_search($label_name, array_column($tax_table_col, 'col_name'))){
$tax_line_alt .= "CHANGE $label_name $label_name $data_type,";
}else{
$last_col = end($tax_table_col);
$tax_last_col = $last_col['col_name'];
$tax_line_alt .= "ADD $label_name $data_type AFTER $tax_last_col,";
}
}else{
$tax_line .= "$label_name $data_type,";
}
}
}
$tax_line = rtrim($tax_line,", ");
$tax_line = rtrim($tax_line,",");
$tax_line_alt = rtrim($tax_line_alt,", ");
$tax_line_alt = rtrim($tax_line_alt,",");
//ADD AND CHANGE ALTER TABLE
if((int)$table_count === 0){
if($tax_line){
$tax_table_query = "CREATE TABLE IF NOT EXISTS cw_tax_ind_cal($tax_line)";
$this->db->query($tax_table_query);
}
return true;
}else{
if($tax_line_alt){
$tax_table_query_alt = "ALTER TABLE cw_tax_ind_cal $tax_line_alt";
$this->db->query($tax_table_query_alt);
}
return true;
}
}
/* ==============================================================*/
/* =========== INDIVIDUAL TAX TABLE SAVE DATA - END =============*/
/* ==============================================================*/
/* ==============================================================*/
/* =========== INDIVIDUAL TAX TABLE SAVE DATA - END =============*/
/* ==============================================================*/
/* ==============================================================*/
/* =========== CUSTOM ENROLMENT TABLE DATA - START =============*/
/* ==============================================================*/
//SAVE CUSTOM TABLE AFTER EMPLOYEE MODULE 25JAN2020
public function save_custom_table($table_data){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
if($prime_module_id != "employees"){
return false;
}
$db_name = $this->config->item("db_name");
$info = $this->db->query("select * from cw_form_setting where prime_module_id = 'employees' and input_view_type in (1,2) and trans_status = '1' order by prime_form_id");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL");
$prime_table_name = "cw_custom_".$prime_module_id;
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = '$prime_table_name'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$prime_table_col = "";
if((int)$table_count === 1){
$prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = '$prime_table_name' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$prime_table_col = $prime_table_query->result_array();
}
$prime_id = "prime_custom_".$prime_module_id."_id";
$prime_line = "$prime_id int(11) NOT NULL AUTO_INCREMENT,";
$prime_line_alt = "CHANGE $prime_id $prime_id int(11) NOT NULL AUTO_INCREMENT,";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$date_type = $setting->date_type;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$data_type = $field_type_array[$field_type];
}else
if((int)$date_type === 2){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}else
if((int)$date_type === 3){
$data_type = "varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'";
}
}else{
$data_type = $field_type_array[$field_type];
}
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if((int)$field_isdefault === 1){
if($prime_table_col){
if(array_search($label_name, array_column($prime_table_col, 'col_name'))){
$prime_line_alt .= "CHANGE $label_name $label_name $data_type,";
}else{
$last_col = end($prime_table_col);
$prime_last_col = $last_col['col_name'];
$prime_line_alt .= "ADD $label_name $data_type AFTER $prime_last_col";
}
}else{
$prime_line .= "$label_name $data_type,";
}
}
}
$prime_line_alt = rtrim($prime_line_alt,", ");
$prime_line_alt = rtrim($prime_line_alt,",");
$prime_line .= "trans_created_by INT(11) NULL DEFAULT '0', trans_created_date DATETIME NULL DEFAULT NULL, trans_updated_by INT(11) NULL DEFAULT '0', trans_updated_date DATETIME NULL DEFAULT NULL, trans_deleted_by INT(11) NULL DEFAULT '0', trans_deleted_date DATETIME NULL DEFAULT NULL,trans_status INT(11) NULL DEFAULT '1',PRIMARY KEY (`$prime_id`)";
//CREATE AND ALTER TABLE
if((int)$table_count === 0){
$prime_table_query = "CREATE TABLE IF NOT EXISTS $prime_table_name($prime_line)";
$this->db->query($prime_table_query);
return true;
}else{
$prime_table_query_alt = "ALTER TABLE $prime_table_name $prime_line_alt";
$this->db->query($prime_table_query_alt);
return true;
}
}
// SAVE CUSTOM ROWSET TABLE
public function save_custom_rowset_table($table_data,$view_input_for,$table_name){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
if($prime_module_id != "employees"){
return false;
}
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$db_name = $this->config->item("db_name");
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL",11=>"varchar(@LENGTH) NULL DEFAULT NULL",12=>"varchar(@LENGTH) NULL DEFAULT NULL",13=>"datetime NULL DEFAULT NULL",14=>"varchar(@LENGTH) NULL DEFAULT NULL");
$row_table_name = "cw_custom_".$table_name;
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='$db_name' AND table_name = '$row_table_name'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$row_table_col = "";
if((int)$table_count === 1){
$row_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = '$row_table_name' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status')");
$row_table_col = $row_table_query->result_array();
}
$rowset_line = "";
$rowset_line_alt = "";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_for = (int)$setting->input_for;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$field_sort = $setting->field_sort;
if((int)$view_input_for === (int)$input_for){
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
$data_type = $field_type_array[$field_type];
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if($row_table_col){
if(array_search($label_name, array_column($row_table_col, 'col_name'))){
$rowset_line_alt .= "CHANGE $label_name $label_name $data_type,";
}else{
$last_col = end($row_table_col);
$prime_last_col = $last_col['col_name'];
$rowset_line_alt .= "ADD $label_name $data_type AFTER $prime_last_col,";
}
}else{
$rowset_line .= "$label_name $data_type,";
}
}
}
$rowset_line = rtrim($rowset_line,", ");
$rowset_line = rtrim($rowset_line,",");
$rowset_line_alt = rtrim($rowset_line_alt,", ");
$rowset_line_alt = rtrim($rowset_line_alt,",");
//CREATE AND ALTER TABLE
if((int)$table_count === 0){
$row_table_query = "CREATE TABLE IF NOT EXISTS $row_table_name($rowset_line)";
$this->db->query($row_table_query);
return true;
}else{
$row_table_query_alt = "ALTER TABLE $row_table_name $rowset_line_alt";
$this->db->query($row_table_query_alt);
return true;
}
}
public function excel($import_type){
//require_once APPPATH."/third_party/PHPExcel.php";
// require_once APPPATH."/controllers/php_excel/PHPExcel.php";;
$obj = new Spreadsheet();
$excel_name = "input_import_setup";
if((int)$import_type === 1){
//Set the first row as the header row
$arrayData = ['input_for','field_for','field_type','label_name','view_name','picklist_table',' picklist_column','picklist_value','field_show','table_show','transaction_type','gross_check',' taxable_check','month_check','payroll_check','increment_check','arrear_pf_check','fandf_check','deduction_check','loan_check','utilities_set','report_set'];
$obj->getActiveSheet()->fromArray($arrayData);
}else
if((int)$import_type === 2){
$excel_name = "formula_import_setup";
//Set the first row as the header row
$arrayData = ['formula_for','formula_type','out_column','payroll_formula','formula_mode','round_value','formula_order','order_by','fandf_only'];
$obj->getActiveSheet()->fromArray($arrayData);
}
ob_end_clean();
// Rename worksheet name
$filename= $excel_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
ob_end_clean();
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
// $writer = new Xlsx($spreadsheet);
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $excelOutput));
}
}
?>