File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Master_details_export.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Master_details_export extends Action_controller{
public function __construct(){
parent::__construct('master_details_export');
// $this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$role_info = $this->db->query("CALL sp_a_run ('SELECT','select * from `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$process_role[""] = "---- Select Role ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$process_role[$role_id] = $category_name;
}
$data['process_role'] = $process_role;
$module_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_modules` where menu_id not in (1,2) and show_module = 1 and module_type !=\'DEV\' order by module_id asc')");
$module_result = $module_data->result();
$module_data->next_result();
$module_list[""] = "---- Select Module ----";
foreach($module_result as $module){
$module_id = $module->module_id;
$module_name = $module->module_name;
$module_list[$module_id] = $module_name;
}
$data['module_list'] = $module_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function emp_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$search_term = $this->input->post_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$emp_id = $rslt->prime_employees_id ;
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function rowset_export(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$category = $this->input->post('process_role');
$emp_code = $this->input->post('process_emp_code');
$master_details = $this->input->post('master_details');
$process_type = $this->input->post('process_type');
if((int)$process_type === 2){
$role = " and role = $category";
}else
if((int)$process_type === 3){
$role = "";
}
$emp_qry = '';
if((int)$process_type === 1){
$emp_qry = " employee_code = $emp_code and ";
}else{
$emp_code_qry = 'select GROUP_CONCAT(employee_code) as employee_code from cw_employees where trans_status= 1 '.$role;
$emp_code_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_code_qry')");
$emp_code_result = $emp_code_data->result();
$emp_code_data->next_result();
$emp_code = $emp_code_result[0]->employee_code;
$emp_code = str_replace(",",'","',$emp_code);
$emp_code = '"'.$emp_code.'"';
$emp_qry = " employee_code in ($emp_code) and ";
}
$master_info_qry = 'select * from cw_form_view_setting where trans_status = 1 and prime_view_module_id = "'.$master_details.'" and form_view_type =3 and form_view_show=1';
$master_info_data = $this->db->query("CALL sp_a_run ('SELECT','$master_info_qry')");
$master_info_result = $master_info_data->result();
$master_info_data->next_result();
$master_info_rows = $master_info_data->num_rows();
if((int)$master_info_rows > 0){
foreach($master_info_result as $master_rslt){
$view_id = $master_rslt->prime_form_view_id;
$form_name = $master_rslt->form_view_label_name;
$table_name = $master_details."_".$form_name;
$row_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$table_prime_id = "prime_".$master_details."_id";
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "'.$master_details.'" and input_for = "'.$view_id.'" and table_show = "1" and trans_status = "1" order by abs(field_sort)';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$join_qry = "";
if($master_details == "employees"){
$select_query = " employee_code,emp_name,";
$join_qry = " inner join cw_employees on cw_employees.prime_employees_id = $table_name.$table_prime_id";
}else{
$select_query = "";
}
$row_head = array();
$master_data = array();
$row_head[0] = array("Employee Code","Employee Name");
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if((int)$field_type === 13){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
$row_head[0][] = $view_name;
}
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$final_qry = "select $select_query from $table_name $pick_query $join_qry " .' where '.$emp_qry.' '.$table_name.'.trans_status = "1" order by abs('.$table_name.'.'.$row_prime_id.') asc';
$row_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_result = $row_data->result();
$row_data->next_result();
$row_result = json_decode(json_encode($row_result), True);
$final_data[$form_name] = array_merge($row_head,$row_result);
}
$excel_rslt = $this->generate_excel($final_data);
if($excel_rslt){
echo json_encode(array('success' => true, 'excel_path' => $excel_rslt));
}else{
echo json_encode(array('success' => false, 'message' => "No data available"));
}
}else{
echo json_encode(array('success' => false, 'message' => "No rowset data available"));
}
}
public function generate_excel($final_data){
require_once APPPATH."/third_party/PHPExcel.php";
$excel = new PHPExcel();
$i=0;
foreach($final_data as $final_key => $final_value){
if($i>0){
$excel->createSheet($i);
}
$excel->setActiveSheetIndex($i)->setTitle($final_key);
$styleArray = array(
'font' => array('bold' => true),
'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,)
);
$total_row = count($final_value);
$total_colums = count($final_value[0]) - 1;
$cell_text = $this->getNameFromNumber($total_colums);
$second_end = $cell_text."1";
$excel->getActiveSheet()->getStyle("A1:$second_end")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN);
$excel->getActiveSheet()->getStyle("A1:$second_end")->getFont()->getColor()->setARGB('FFFFFFFF');
$excel->getActiveSheet()->getStyle("A1:$second_end")->applyFromArray($styleArray);
$excel->getActiveSheet()->fromArray($final_value, null, 'A1');
$i++;
}
$excel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="master_info.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel, 'Excel5');
$objWriter->save('./excel_write/master_info.xls');
$excel_path = './excel_write/master_info.xls';
return $excel_path;
}
public function getNameFromNumber($num){
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
}
?>