File: /home/cafsindia/rms.cafsjobs.com/bk_rmsfiles/application/controllers/Table_delete.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Table_delete extends Base_controller{
public function __construct(){
parent::__construct('table_delete');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
public function index(){
$module_info_qry = 'select * from cw_modules where trans_status = 1 and show_module = 1 and module_type != "DEV" and sort < 800 ORDER BY sort asc';
$module_info = $this->db->query("CALL sp_a_run ('SELECT','$module_info_qry')");
$module_result = $module_info->result();
$module_info->next_result();
$module_list[""] = "---- Module Name ----";
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;
$this->load->view("table_delete/manage",$data);
}
// NEHA EDIT START 25MARCH2020 (table backup functionality)
public function process_delete(){
$module_id = $this->input->post('module_id');
$table_name = "cw_".$module_id;
if($module_id === "employees"){
//employees table
$employees_bk = "bk_cw_employees".date('YMd')."";
$exist_employees_table = "bk_cw_employees";
$show_table = "SHOW TABLES LIKE \"$exist_employees_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$employees_qry = "CREATE TABLE IF NOT EXISTS ".$employees_bk." LIKE cw_employees;";
$this->db->query("CALL sp_a_run ('RUN','$employees_qry')");
$employees_copy_qry="INSERT ".$employees_bk." SELECT * FROM cw_employees;";
$this->db->query("CALL sp_a_run ('RUN','$employees_copy_qry')");
$delete_qry = "delete from cw_employees where `cw_employees`.`prime_employees_id` != 1";
$this->db->query("CALL sp_a_run ('RUN','$delete_qry')");
$update_qry = "ALTER TABLE `cw_employees` AUTO_INCREMENT=2";
$this->db->query("CALL sp_a_run ('UPDATE','$update_qry')");
//Grant table
$grants_bk = "bk_cw_grants".date('YMd')."";
$exist_grants_table = "bk_cw_grants";
$show_table = "SHOW TABLES LIKE \"$exist_grants_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$grants_qry = "CREATE TABLE IF NOT EXISTS ".$grants_bk." LIKE cw_grants;";
$this->db->query("CALL sp_a_run ('RUN','$grants_qry')");
$grants_copy_qry ="INSERT ".$grants_bk." SELECT * FROM cw_grants;";
$this->db->query("CALL sp_a_run ('RUN','$grants_copy_qry')");
$delete_grants_qry = "delete from cw_grants where `cw_grants`.`prime_employees_id` != 1";
$this->db->query("CALL sp_a_run ('RUN','$delete_grants_qry')");
$sts = true;
}elseif($module_id === "monthly_input"){
$monthly_input_bk = "bk_cw_monthly_input".date('YMd')."";
$exist_monthly_input_table = "bk_cw_monthly_input";
$show_table = "SHOW TABLES LIKE \"$exist_monthly_input_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$monthly_input_qry = "CREATE TABLE IF NOT EXISTS ".$monthly_input_bk." LIKE cw_monthly_input;";
$this->db->query("CALL sp_a_run ('RUN','$monthly_input_qry')");
$monthly_input_copy_qry ="INSERT ".$monthly_input_bk." SELECT * FROM cw_monthly_input;";
$this->db->query("CALL sp_a_run ('RUN','$monthly_input_copy_qry')");
$delete_qry = "truncate cw_monthly_input";
$this->db->query("CALL sp_a_run ('RUN','$delete_qry')");
$sts = true;
}elseif($module_id === "process_payroll"){
//Transaction table
$process_payroll_bk = "bk_cw_transactions".date('YMd')."";
$exist_transaction_table = "bk_cw_transactions";
$show_table = "SHOW TABLES LIKE \"$exist_transaction_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$process_payroll_qry = "CREATE TABLE IF NOT EXISTS ".$process_payroll_bk." LIKE cw_transactions;";
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_qry')");
$process_payroll_copy_qry ="INSERT ".$process_payroll_bk." SELECT * FROM cw_transactions;";
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_copy_qry')");
$delete_qry = "truncate cw_transactions";
$this->db->query("CALL sp_a_run ('RUN','$delete_qry')");
$sts = true;
}elseif($module_id === "increment"){
//Increment table
$increment_bk = "bk_cw_increment".date('YMd')."";
$exist_increment_table = "bk_cw_increment";
$show_table = "SHOW TABLES LIKE \"$exist_increment_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$increment_qry = "CREATE TABLE IF NOT EXISTS ".$increment_bk." LIKE cw_increment;";
$this->db->query("CALL sp_a_run ('RUN','$increment_qry')");
$increment_copy_qry ="INSERT ".$increment_bk." SELECT * FROM cw_increment;";
$this->db->query("CALL sp_a_run ('RUN','$increment_copy_qry')");
$delete_qry = "truncate cw_increment";
$this->db->query("CALL sp_a_run ('RUN','$delete_qry')");
//Arrears table
$arrears_bk = "bk_cw_arrears".date('YMd')."";
$exist_arrears_table = "bk_cw_arrears";
$show_table = "SHOW TABLES LIKE \"$exist_arrears_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$arrears_qry = "CREATE TABLE IF NOT EXISTS ".$arrears_bk." LIKE cw_arrears;";
$this->db->query("CALL sp_a_run ('RUN','$arrears_qry')");
$arrears_copy_qry ="INSERT ".$arrears_bk." SELECT * FROM cw_arrears;";
$this->db->query("CALL sp_a_run ('RUN','$arrears_copy_qry')");
$delete_arr_qry = "truncate cw_arrears";
$this->db->query("CALL sp_a_run ('RUN','$delete_arr_qry')");
//Arrears cumulative table
$cumulate_bk = "bk_cw_arrear_cumulative".date('YMd')."";
$exist_cumulate_table = "bk_cw_arrear_cumulative";
$show_table = "SHOW TABLES LIKE \"$exist_cumulate_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$cumulate_qry = "CREATE TABLE IF NOT EXISTS ".$cumulate_bk." LIKE cw_arrear_cumulative;";
$this->db->query("CALL sp_a_run ('RUN','$cumulate_qry')");
$cumulate_copy_qry ="INSERT ".$cumulate_bk." SELECT * FROM cw_arrear_cumulative;";
$this->db->query("CALL sp_a_run ('RUN','$cumulate_copy_qry')");
$delete_cumulate_qry = "truncate cw_arrear_cumulative";
$this->db->query("CALL sp_a_run ('RUN','$delete_cumulate_qry')");
$sts = true;
}else{
if($table_name){
if($this->db->table_exists($table_name)){
$table_name_bk = "bk_$table_name".date('YMd')."";
$exist_table = "bk_$table_name";
$show_table = "SHOW TABLES LIKE \"$exist_table%\"";
$query = $this->db->query("CALL sp_a_run ('RUN','$show_table')");
$result = $query->num_rows();
$query->next_result();
if($result > 0){
$result= $query->row();
foreach($result as $value){
$bk_table_name = $value;
}
$drop_qry = "DROP table $bk_table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$rslt->next_result();
}
$create_qry= "CREATE TABLE IF NOT EXISTS ".$table_name_bk." LIKE ".$table_name.";";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$create_qry')");
$copy_qry="INSERT ".$table_name_bk." SELECT * FROM ".$table_name.";";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$copy_qry')");
$delete_qry = "truncate $table_name";
$rslt =$this->db->query("CALL sp_a_run ('RUN','$delete_qry')");
$sts = true;
}else{
$sts = false;
}
}
}
if($sts){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}
// NEHA EDIT END 26MARCH2020
}
?>