<?php 
/**
 * Month_locking Page Controller
 * @category  Controller
 */
class Month_lockingController extends SecureController{
	function __construct(){
		parent::__construct();
		$this->tablename = "month_locking";
	}
	/**
     * List page records
     * @param $fieldname (filter record by a field) 
     * @param $fieldvalue (filter field value)
     * @return BaseView
     */
	function index($fieldname = null , $fieldvalue = null){
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$fields = array("id", 
			"month", 
			"year", 
			"locked");
		$pagination = $this->get_pagination(MAX_RECORD_COUNT); // get current pagination e.g array(page_number, page_limit)
		//search table record
		if(!empty($request->search)){
			$text = trim($request->search); 
			$search_condition = "(
				month_locking.id LIKE ? OR 
				month_locking.month LIKE ? OR 
				month_locking.year LIKE ? OR 
				month_locking.locked LIKE ?
			)";
			$search_params = array(
				"%$text%","%$text%","%$text%","%$text%"
			);
			//setting search conditions
			$db->where($search_condition, $search_params);
			 //template to use when ajax search
			$this->view->search_template = "month_locking/search.php";
		}
		if(!empty($request->orderby)){
			$orderby = $request->orderby;
			$ordertype = (!empty($request->ordertype) ? $request->ordertype : ORDER_TYPE);
			$db->orderBy($orderby, $ordertype);
		}
		else{
			$db->orderBy("month_locking.id", ORDER_TYPE);
		}
		if($fieldname){
			$db->where($fieldname , $fieldvalue); //filter by a single field name
		}
		$tc = $db->withTotalCount();
		$records = $db->get($tablename, $pagination, $fields);
		$records_count = count($records);
		$total_records = intval($tc->totalCount);
		$page_limit = $pagination[1];
		$total_pages = ceil($total_records / $page_limit);
		$data = new stdClass;
		$data->records = $records;
		$data->record_count = $records_count;
		$data->total_records = $total_records;
		$data->total_page = $total_pages;
		if($db->getLastError()){
			$this->set_page_error();
		}
		$page_title = $this->view->page_title = "Month Locking";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		$this->render_view("month_locking/list.php", $data); //render the full page
	}
	/**
     * View record detail 
	 * @param $rec_id (select record by table primary key) 
     * @param $value value (select record by value of field name(rec_id))
     * @return BaseView
     */
	function view($rec_id = null, $value = null){
		$request = $this->request;
		$db = $this->GetModel();
		$rec_id = $this->rec_id = urldecode($rec_id);
		$tablename = $this->tablename;
		$fields = array("id", 
			"month", 
			"year", 
			"locked");
		if($value){
			$db->where($rec_id, urldecode($value)); //select record based on field name
		}
		else{
			$db->where("month_locking.id", $rec_id);; //select record based on primary key
		}
		$record = $db->getOne($tablename, $fields );
		if($record){
			$page_title = $this->view->page_title = "View  Month Locking";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		}
		else{
			if($db->getLastError()){
				$this->set_page_error();
			}
			else{
				$this->set_page_error("No record found");
			}
		}
		return $this->render_view("month_locking/view.php", $record);
	}
	/**
     * Insert new record to the database table
	 * @param $formdata array() from $_POST
     * @return BaseView
     */
	function add($formdata = null){
		if($formdata){
			$db = $this->GetModel();
			$tablename = $this->tablename;
			$request = $this->request;
			//fillable fields
			$fields = $this->fields = array("month","year");
			$postdata = $this->format_request_data($formdata);
			$this->rules_array = array(
				'month' => 'required',
				'year' => 'required|numeric|max_numeric,2100|min_numeric,1900',
			);
			$this->sanitize_array = array(
				'month' => 'sanitize_string',
				'year' => 'sanitize_string',
			);
			$this->filter_vals = true; //set whether to remove empty fields
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			if($this->validated()){
		# Statement to execute before adding record
		$monthNum   = $modeldata['month'];
$dateObj    = DateTime::createFromFormat('!m', $monthNum);
$month_year = $dateObj->format('F')."-".$modeldata['year'];
//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET NEW PORTFOLIO IDS @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
$fields = array('empid', 'new_portfolio');
$db->where("status", "READY MIGRATION");
$db->where("effective_month", $modeldata['month']);
$db->where("effective_year", $modeldata['year']);
$arr = $db->get("user_profile_reposition", $fields);
foreach($arr as $k){
$table_data = array(
    "portfolio_id" => $k["new_portfolio"] 
);
$db->where("id", $k['empid']);
$bool = $db->update("employee_details", $table_data);
$table_data = array(
    "portid" => $k["new_portfolio"] 
);
$db->where("empid", $k['empid']);
$bool = $db->update("multiple_values", $table_data);
$table_data = array(
    "portfolio_id" => $k["new_portfolio"] 
);
$db->where("employee_name", $k['empid']);
$bool = $db->update("variable_values", $table_data);
}
//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET NEW PORTFOLIO IDS @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
		# End of before add statement
				$rec_id = $this->rec_id = $db->insert($tablename, $modeldata);
				if($rec_id){
		# Statement to execute after adding record
         $basic_pay_type = "constant";
        // error_reporting(0);
        $total_days_in_month = cal_days_in_month(CAL_GREGORIAN, $modeldata["month"], $modeldata["year"]);
        date_default_timezone_set('Asia/Kolkata');
        $total_days_in_month_global = $total_days_in_month;
        $fields = array("id");
        $arr2 = $db->get("portfolio", 99999, $fields);
        foreach ($arr2 as $x) {
            $pid = $x['id'];
            $total_days_in_month = $total_days_in_month_global;
            //get 30 or 31 month
            //@@@@@@@@@@@@@@@@@@@@@ LOGIC PRESERVED @@@@@@@@@@@@@@@@@@@
            //$db->where("pid",  $pid);
            //$db->where("allowance_or_deductions ", 'no_of_days');
            //$value               = $db->getValue("portfolio_configurations", "value");
            // if($value==""||is_null($value)){
            //    $total_days_in_month = $total_days_in_month_global;
            //}else{
            //    $total_days_in_month = $value;
            //}
            //@@@@@@@@@@@@@@@@@@@@@ LOGIC PRESERVED ENDS @@@@@@@@@@@@@@@@@@@
            //GET FROM CUSTOM CALENDER
            $db->where("month ", $modeldata["month"]);
            $db->where("year ", $modeldata["year"]);
            $value = $db->getValue("custom_calender", "days");
            if ($value == "" || is_null($value)) {
                $total_days_in_month = $total_days_in_month_global;
            } else {
                $total_days_in_month = $value;
            }
            $salary_data = [];
            //get employee in specific id
            $fields = array('id', 'employee_name', 'basic_pay'); //$db->where("fieldname", "fieldvalue");//admin if needed
            $db->where("portfolio_id", $pid);
            $arr = $db->get("employee_details", 999999, $fields);
            foreach ($arr as $u) {
                $muldata = [];
                $emp_name = $u['employee_name'];
                $emp_id = $u['id'];
                $basic_pay = $u['basic_pay'];
                $db->where("empid", $emp_id);
                $suspension_type = $db->getValue("suspension", "suspension_type");
                $db->where("empid", $emp_id);
                $value = $db->getValue("suspension", "date_of_suspension");
                if (is_null($value) || $value == "") {
                    $is_suspended = false;
                    $db->where("empid", $emp_id);
                    $value = $db->getValue("suspension_salary", "sum(value)");
                    if (is_null($value) || $value == "") {
                        $suspension_allowance = 0;
                    } else {
                        $suspension_allowance = $value;
                    }
                } else {
                    $is_suspended = true;
                    $now = time(); // or your date as well
                    $your_date = strtotime($value);
                    $datediff = $now - $your_date;
                    $days_suspended = round($datediff / (60 * 60 * 24));
                    $db->where("empid", $emp_id);
                    $value = $db->getValue("suspension", "suspension_type");
                    //query the database and return a single row
                    $fields = array('prct');
                    $db->where("after_days<$days_suspended");
                    $db->orderby("after_days", "DESC");
                    $record = $db->getOne("suspension_configuration", $fields);
                    $suspension_prct = $record['prct'];
                    /*   $db->where("after_days>$days_suspended");
                    $db->where("suspension_type ", $value');
                    $suspension_prct = $db->getValue("suspension_configuration", "prct");  */
                    if (is_null($suspension_prct) || $suspension_prct == "") {
                        $suspension_prct = 0;
                    }
                    $suspension_allowance = 0;
                }
                //get absent days
                $db->where("month", $modeldata["month"]);
                $db->where("year", $modeldata["year"]);
                $db->where("user_id", $emp_id);
                $value = $db->getValue("absent_count", "absent_days");
                $absent_days = is_null($value) ? 0 : $value;
                $absent_days = $absent_days == "" ? 0 : $absent_days;
                // main logic starts
                $netsalary = 0;
                $netallow = 0;
                $netdeduct = 0;
                $tmpdata = [];
                if($total_days_in_month== $absent_days){
                    $tmpzero = true;
                    }else{
                         $tmpzero = false;
                    }
                //@@@@@@@@@@@@@@@@@@@ SUSPENSION ALLOWANCE @@@@@@@@@@@@@@@
                if ($suspension_allowance <> 0) {
                    $netallow+= round($suspension_allowance, 5);
                    $tmpvalue = round($suspension_allowance, 5);
                    $allowance_ded = "allowance";
                    if ($allowance_ded <> "") {
                        $tmpdata[] = ["id" => 10000000, "colname" => "$suspension_type Allowance", "value" => round($tmpvalue, 5), "ad" => $allowance_ded];
                        $db->where("empid", $emp_id);
                        $bool = $db->delete("suspension_salary");
                    }
                }
                //get basic pay of the portfolio
                //$db->where("allowance_or_deductions", $basic_pay_type);
                //$db->where("pid", $pid);
                //$basic_pay = $db->getValue("portfolio_configurations", "value");
                $basic_pay_after_absent = round(($basic_pay / $total_days_in_month) * ($total_days_in_month - $absent_days), 5);
                //loop through all the rows
                $fields = array('*');
                $db->where("pid", $pid);
                $db->orderby("id", "ASC");
                $configs = $db->get("portfolio_configurations", 999999, $fields);
                foreach ($configs as $config) {
                    $tmpvalue = 0;
                    $allowance_ded = "";
                    if ($config['allowance_or_deductions'] == "constant") {
                        if ($config['col_name'] == "Basic Pay") {
                            $tmpvalue = $basic_pay;
                        } elseif ($config['col_name'] == "Fixed_Present_Days") {
                            $tmpvalue = ($total_days_in_month - $absent_days);
                        } elseif ($config['col_name'] == "Fixed_Month_Days") {
                            $tmpvalue = ($total_days_in_month);
                        } else {
                            $tmpvalue = 0;
                        }
                    }
                    if ($config['allowance_or_deductions'] == "basic_pay_after_absent") {
                        $tmpvalue = $basic_pay_after_absent;
                        $netallow+= round($basic_pay_after_absent, 5);
                        $allowance_ded = "allowance";
                    }
                    if ($config['calculation_type'] == "plain_value") {
                        if($tmpvalue>0){
                        }else{
                            $tmpvalue = round($config['value'], 5);
                            $allowance_ded = "a";
                            if ($config['allowance_or_deductions'] == "allowance" && $config['show_col']=="yes") {
                               if($config['minimum_value']>0){
                               $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                                }
                 if( $tmpzero ){ $tmpvalue = 0; }
                                $netallow+= round($tmpvalue, 5);
                                $allowance_ded = "allowance";
                            }
                            if ($config['allowance_or_deductions'] == "deductions" && $config['show_col']=="yes") {
                                $tmpvalue = round($config['value'], 5);
                                 if($config['minimum_value']>0){
                                   $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                                    }
                               if( $tmpzero ){ $tmpvalue = 0; }
                                $netdeduct+= round($tmpvalue, 5);
                                $allowance_ded = "deductions";
                            }
                        }
                    }
                    if ($config['calculation_type'] == "prct") {
                        $prct_on = 0;
                        $prct_value_1 = 0;
                        $prct_value_2 = 0;
                        if ($config['prct_value_1'] <> "") {
                            //get type
                            foreach ($configs as $keyc) {
                                if ($keyc['col_name'] == $config['prct_value_1']) {
                                    $tmp_tmp_value = 0;
                                    foreach ($tmpdata as $k) {
                                        if ($k['id'] == $keyc['id']) {
                                            $tmp_tmp_value = $k['value'];
                                        }
                                    }
                                    $prct_value_1 = round($tmp_tmp_value, 5); //set value
                                }
                            }
                        }
                        if ($config['prct_value_2'] <> "") {
                            //get type
                            foreach ($configs as $keyc) {
                                if ($keyc['col_name'] == $config['prct_value_2']) {
                                    $tmp_tmp_value = 0;
                                    foreach ($tmpdata as $k) {
                                        if ($k['id'] == $keyc['id']) {
                                            $tmp_tmp_value = $k['value'];
                                        }
                                    }
                                    $prct_value_2 = round($tmp_tmp_value, 5); //set value
                                }
                            }
                        }
                        if ($config['prct_value_2'] == "") {
                            $prct_on+= round($prct_value_1, 5);
                        } elseif ($config['prct_value_operation'] <> "") {
                            if ($config['prct_value_operation'] == "Add") {
                                $prct_on+= round(($prct_value_1 + $prct_value_2), 5);
                            } else {
                                $prct_on+= round(($prct_value_1 - $prct_value_2), 5);
                            }
                        }
                        if ($config['allowance_or_deductions'] == "allowance") {
                            $tmpvalue = round(($config['value'] / 100) * $prct_on, 5);
                               if($config['minimum_value']>0){
                    $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                    }
                 if( $tmpzero ){ $tmpvalue = 0; }
                            $netallow+= round($tmpvalue, 5);
                            $allowance_ded = "allowance";
                        }
                        if ($config['allowance_or_deductions'] == "deductions") {
                            $tmpvalue = round(($config['value'] / 100) * $prct_on, 5);
                            if($config['minimum_value']>0){
                    $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                    }
                 if( $tmpzero ){ $tmpvalue = 0; }
                            $netdeduct+= round($tmpvalue, 5);
                            $allowance_ded = "deductions";
                        }
                    }
                    if ($config['calculation_type'] == "mul_or_divide") {
                        $prct_on = 0;
                        $prct_value_1 = 0;
                        $prct_value_2 = 0;
                        if ($config['prct_value_1'] <> "") {
                            //get type
                            foreach ($configs as $keyc) {
                                if ($keyc['col_name'] == $config['prct_value_1']) {
                                    $tmp_tmp_value = 0;
                                    foreach ($tmpdata as $k) {
                                        if ($k['id'] == $keyc['id']) {
                                            $tmp_tmp_value = $k['value'];
                                        }
                                    }
                                    $prct_value_1 = round($tmp_tmp_value, 5); //set value
                                }
                            }
                        }
                        if ($config['prct_value_2'] <> "") {
                            //get type
                            foreach ($configs as $keyc) {
                                if ($keyc['col_name'] == $config['prct_value_2']) {
                                    $tmp_tmp_value = 0;
                                    foreach ($tmpdata as $k) {
                                        if ($k['id'] == $keyc['id']) {
                                            $tmp_tmp_value = $k['value'];
                                        }
                                    }
                                    $prct_value_2 = round($tmp_tmp_value, 5); //set value
                                }
                            }
                        }
                        if ($config['prct_value_2'] == "") {
                            $prct_on+= round($prct_value_1, 5);
                        } elseif ($config['prct_value_operation'] <> "") {
                            if ($config['prct_value_operation'] == "Multiply") {
                                $prct_on+= round(($prct_value_1 * $prct_value_2), 5);
                            } else {
                                if($prct_value_2==0){
                                    $prct_on+=0;
                                }else{
                                    $prct_on+= round(($prct_value_1 / $prct_value_2), 5);
                                }
                            }
                        }
                        $tmpvalue = round($prct_on, 5);
                        $allowance_ded = "a";
                        if( $tmpzero ){ $tmpvalue = 0; }
                        if ($config['allowance_or_deductions'] == "allowance" && $config['show_col'] == "yes") {
                            $netallow+= round($tmpvalue, 5);
                            $allowance_ded = "allowance";
                        }
                        if ($config['allowance_or_deductions'] == "deductions" && $config['show_col'] == "yes") {
                            $netdeduct+= round($tmpvalue, 5); 
                            $allowance_ded = "deductions";
                        }
                    }
                    if ($config['calculation_type'] == "variable") {
                        $db->where("portfolio_id", $pid);
                        $db->where("colname", $config['col_name']);
                        $db->where("employee_name", $emp_id);
                        $tmvalue = $db->getValue("variable_values", "value");
                        $tmvalue = is_null($tmvalue) ? 0 : $tmvalue;
                        $tmvalue = $tmvalue == "" ? 0 : $tmvalue;
                        if( $tmpzero ){ $tmvalue = 0; }
                        if ($config['allowance_or_deductions'] == "allowance") {
                            $netallow+= round($tmvalue, 5);
                            $tmpvalue = round($tmvalue, 5);
                            $allowance_ded = "allowance";
                        }
                        if ($config['allowance_or_deductions'] == "deductions") {
                            $netdeduct+= round($tmvalue, 5);
                            $tmpvalue = round($tmvalue, 5);
                            $allowance_ded = "deductions";
                        }
                    }
                    if ($config['calculation_type'] == "multivariable") {
                        $db->where("portid", $pid);
                        $db->where("col", $config['col_name']);
                        $db->where("empid", $emp_id);
                        $tmvalues = $db->getValue("multiple_values", "SUM(amount)");
                        $tmvalues = is_null($tmvalues) ? 0 : $tmvalues;
                        $tmvalues = $tmvalues == "" ? 0 : $tmvalues;
                        $db->where("portid", $pid);
                        $db->where("col", $config['col_name']);
                        $db->where("empid", $emp_id);
                        $datamuls = $db->get("multiple_values", 99999999, "*");
                        $tmpdatas = [];
                        $founds = false;
                        foreach ($datamuls as $ksys) {
                            $founds = true;
                            $tmpdatas[] = ["col" => $config['col_name'], "show_col" => $config['show_col'], "pos" => $config['position'], "amount" => $ksys['amount'], "detailno" => $ksys['detailno']];
                        }
                        if ($founds) {
                            $muldata[] = ["col" => $config['col_name'], "data" => $tmpdatas];
                        }
                        if ($config['allowance_or_deductions'] == "allowance") {
                            $tmpvalue = round($tmvalues, 5);
                        if($config['minimum_value']>0){
                    $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                    }
                 if( $tmpzero ){ $tmpvalue = 0; }
                            $netallow+= round($tmpvalue, 5);
                            $allowance_ded = "allowance";
                        }
                        if ($config['allowance_or_deductions'] == "deductions") {
                            $tmpvalue = round($tmvalues, 5);
                        if($config['minimum_value']>0){
                    $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                    }
                 if( $tmpzero ){ $tmpvalue = 0; }
                            $netdeduct+= round($tmpvalue, 5);
                            $allowance_ded = "deductions";
                        }
                    }
                    // echo "<br><br><br>id ".$config['id']." - ".$config['allowance_or_deductions']." - ".($netallow - $netdeduct);
                    if ($allowance_ded <> ""||$config['allowance_or_deductions']=="constant") {
                    if($config['minimum_value']>0){
                    $tmpvalue = max($tmpvalue,round($config['minimum_value'],5));
                    }
                      if( $tmpzero ){ $tmpvalue = 0; }   
                        $tmpdata[] = ["id" => $config['id'], "pos" => $config['position'], "show_col" => $config['show_col'], "colname" => $config['col_name'], "value" => round($tmpvalue, 5), "ad" => $allowance_ded];
                //    print_r($tmpdata);
                }
                }
                // exit();
                if ($is_suspended) {
                    $net_salarytt = $netallow - $netdeduct;
                    $deduce = $net_salarytt * ($suspension_prct / 100);
                    $netdeduct+= $deduce;
                    $table_data = array("empid" => $emp_id, "month" => $modeldata["month"], "year" => $modeldata["year"], "value" => round($deduce, 5));
                    $tmpvalue = round($deduce, 5);
                    $allowance_ded = "deductions";
                    if ($allowance_ded <> "") {
                        $tmpdata[] = ["id" => 10000000, "pos" => $config['position'], "show_col" => $config['show_col'], "colname" => "$suspension_type Deduction", "value" => round($tmpvalue, 5), "ad" => $allowance_ded];
                    }
                    $db->insert("suspension_salary", $table_data);
                }
            error_reporting(0);
                // exit();
                array_multisort(array_column($tmpdata, "ad"), SORT_ASC, array_column($tmpdata, "pos"), SORT_ASC, array_column($tmpdata, "id"), SORT_ASC, $tmpdata);
                $Employee_Name_L        = str_replace(" ", "_", $db->rawQueryValue("SELECT  user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Employee_Name' ")[0] );
                $Employee_Designation_L = str_replace(" ", "_", $db->rawQueryValue("SELECT user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Employee_Designation' ")[0] );
                $Basic_Pay_L            = str_replace(" ", "_", $db->rawQueryValue("SELECT  user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Basic_Pay' ")[0] );
                $Absent_Days_L          = str_replace(" ", "_", $db->rawQueryValue("SELECT user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Absent_Days' ")[0] );
                $Net_Total_Allowance_L  = str_replace(" ", "_", $db->rawQueryValue("SELECT user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Net_Total_Allowance' ")[0]);
                $Net_Total_Decuctions_L = str_replace(" ", "_", $db->rawQueryValue("SELECT user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Net_Total_Decuctions' ")[0] );
                $Total_Salary_L         = str_replace(" ", "_", $db->rawQueryValue("SELECT user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Total_Salary' ")[0] );
                $Present_Days_L         = str_replace(" ", "_", $db->rawQueryValue("SELECT user_value FROM report_lables WHERE report = 'SalarySheet' and labelname='Present_Days' ")[0] );
                $cols                   = "$Employee_Name_L,$Basic_Pay_L,$Present_Days_L,$Absent_Days_L";
                $uploadjson             = [];
                $uploadjson[]           = ["col" => "$Employee_Name_L", "value" => $emp_name, "ad" => ""];
                $uploadjson[]           = ["col" => "$Basic_Pay_L", "value" => ($basic_pay), "ad" => ""];
                $uploadjson[]           = ["col" => "$Absent_Days_L", "value" => ($absent_days), "ad" => ""];
                $uploadjson[]           = ["col" => "$Present_Days_L", "value" => ($total_days_in_month - $absent_days), "ad" => ""];
                foreach ($tmpdata as $t) {
                    if ($cols == "$Employee_Name_L,$Basic_Pay_L,$Present_Days_L,$Absent_Days_L" && $t['show_col'] == "yes") {
                        $cols.= "," . str_replace(" ", "_", $t["colname"]);
                        $prev = "allowance";
                        if ($t['ad'] <> "allowance") {
                            $cols.= ",$Net_Total_Allowance_L";
                            $uploadjson[] = ["col" => "$Net_Total_Allowance_L", "value" => $netallow, "ad" => ""];
                        }
                        $uploadjson[] = ["col" => str_replace(" ", "_", $t["colname"]), "value" => round($t['value'], 5), "ad" => $t['ad']];
                    } elseif ($t['show_col'] == "yes") {
                        if ($prev <> $t['ad']) {
                            $cols.= ",$Net_Total_Allowance_L," . $t["colname"];
                            $uploadjson[] = ["col" => "$Net_Total_Allowance_L", "value" => $netallow, "ad" => ""];
                            $prev = $t['ad'];
                            $uploadjson[] = ["col" => str_replace(" ", "_", $t["colname"]), "value" => round($t['value'], 5), "ad" => $t['ad']];
                        } else {
                            $cols.= "," . str_replace(" ", "_", $t["colname"]);
                            $prev = $t['ad'];
                            $uploadjson[] = ["col" => str_replace(" ", "_", $t["colname"]), "value" => round($t['value'], 5), "ad" => $t['ad']];
                        }
                    }
                }
                if ($prev == "allowance") {
                    $cols.= ",$Net_Total_Allowance_L";
                    $uploadjson[] = ["col" => "$Net_Total_Allowance_L", "value" => $netallow, "ad" => ""];
                }
                $cols.= ",$Net_Total_Decuctions_L,$Total_Salary_L";
                $uploadjson[] = ["col" => "$Net_Total_Decuctions_L", "value" => $netdeduct, "ad" => ""];
                $uploadjson[] = ["col" => "$Total_Salary_L", "value" => ($netallow - $netdeduct), "ad" => ""];
                //  print_r(json_encode($uploadjson)) ;
                //  exit();
                $uploadjsontxt = json_encode($uploadjson, true);
                $table_data = array("user_id" => $emp_id, "month" => $modeldata["month"], "year" => $modeldata["year"], "name_of_emp" => $emp_name, "json_data" => $uploadjsontxt, "json_multi_data" => json_encode($muldata, true), "cols" => $cols, "pid" => $pid, "total_salary" => round(($netallow - $netdeduct), 5));
                $db->insert("salary_data", $table_data);
            }
        }
		# End of after add statement
					$this->set_flash_msg("Record added successfully", "success");
					return	$this->redirect("month_locking");
				}
				else{
					$this->set_page_error();
				}
			}
		}
		$page_title = $this->view->page_title = "Add New Month Locking";
		$this->render_view("month_locking/add.php");
	}
	/**
     * Update table record with formdata
	 * @param $rec_id (select record by table primary key)
	 * @param $formdata array() from $_POST
     * @return array
     */
	function edit($rec_id = null, $formdata = null){
		$request = $this->request;
		$db = $this->GetModel();
		$this->rec_id = $rec_id;
		$tablename = $this->tablename;
		 //editable fields
		$fields = $this->fields = array("id","month","year");
		if($formdata){
			$postdata = $this->format_request_data($formdata);
			$this->rules_array = array(
				'month' => 'required',
				'year' => 'required|numeric|max_numeric,2100|min_numeric,1900',
			);
			$this->sanitize_array = array(
				'month' => 'sanitize_string',
				'year' => 'sanitize_string',
			);
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			if($this->validated()){
				$db->where("month_locking.id", $rec_id);;
				$bool = $db->update($tablename, $modeldata);
				$numRows = $db->getRowCount(); //number of affected rows. 0 = no record field updated
				if($bool && $numRows){
					$this->set_flash_msg("Record updated successfully", "success");
					return $this->redirect("month_locking");
				}
				else{
					if($db->getLastError()){
						$this->set_page_error();
					}
					elseif(!$numRows){
						//not an error, but no record was updated
						$page_error = "No record updated";
						$this->set_page_error($page_error);
						$this->set_flash_msg($page_error, "warning");
						return	$this->redirect("month_locking");
					}
				}
			}
		}
		$db->where("month_locking.id", $rec_id);;
		$data = $db->getOne($tablename, $fields);
		$page_title = $this->view->page_title = "Edit  Month Locking";
		if(!$data){
			$this->set_page_error();
		}
		return $this->render_view("month_locking/edit.php", $data);
	}
	/**
     * Update single field
	 * @param $rec_id (select record by table primary key)
	 * @param $formdata array() from $_POST
     * @return array
     */
	function editfield($rec_id = null, $formdata = null){
		$db = $this->GetModel();
		$this->rec_id = $rec_id;
		$tablename = $this->tablename;
		//editable fields
		$fields = $this->fields = array("id","month","year");
		$page_error = null;
		if($formdata){
			$postdata = array();
			$fieldname = $formdata['name'];
			$fieldvalue = $formdata['value'];
			$postdata[$fieldname] = $fieldvalue;
			$postdata = $this->format_request_data($postdata);
			$this->rules_array = array(
				'month' => 'required',
				'year' => 'required|numeric|max_numeric,2100|min_numeric,1900',
			);
			$this->sanitize_array = array(
				'month' => 'sanitize_string',
				'year' => 'sanitize_string',
			);
			$this->filter_rules = true; //filter validation rules by excluding fields not in the formdata
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			if($this->validated()){
				$db->where("month_locking.id", $rec_id);;
				$bool = $db->update($tablename, $modeldata);
				$numRows = $db->getRowCount();
				if($bool && $numRows){
					return render_json(
						array(
							'num_rows' =>$numRows,
							'rec_id' =>$rec_id,
						)
					);
				}
				else{
					if($db->getLastError()){
						$page_error = $db->getLastError();
					}
					elseif(!$numRows){
						$page_error = "No record updated";
					}
					render_error($page_error);
				}
			}
			else{
				render_error($this->view->page_error);
			}
		}
		return null;
	}
	/**
     * Delete record from the database
	 * Support multi delete by separating record id by comma.
     * @return BaseView
     */
	function delete($rec_id = null){
		Csrf::cross_check();
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$this->rec_id = $rec_id;
		//form multiple delete, split record id separated by comma into array
		$arr_rec_id = array_map('trim', explode(",", $rec_id));
	#Statement to execute before delete record
	   //query the database and return a single row
   $fields    = array('year','month'); 
   $db->where("id",$rec_id);
   $modeldata = $db->getOne("month_locking", $fields);
   $db->where("month", $modeldata["month"]);
    $db->where("year", $modeldata["year"]);
$bool = $db->delete("salary_data");
   $db->where("month", $modeldata["month"]);
    $db->where("year", $modeldata["year"]);
$bool = $db->delete("suspension_salary");
	# End of before delete statement
		$db->where("month_locking.id", $arr_rec_id, "in");
		$bool = $db->delete($tablename);
		if($bool){
			$this->set_flash_msg("Record deleted successfully", "success");
		}
		elseif($db->getLastError()){
			$page_error = $db->getLastError();
			$this->set_flash_msg($page_error, "danger");
		}
		return	$this->redirect("month_locking");
	}
	/**
     * Insert new record to the database table
	 * @param $formdata array() from $_POST
     * @return BaseView
     */
	function add2($formdata = null){
		if($formdata){
			$db = $this->GetModel();
			$tablename = $this->tablename;
			$request = $this->request;
			//fillable fields
			$fields = $this->fields = array("month","year","locked");
			$postdata = $this->format_request_data($formdata);
			$this->rules_array = array(
				'month' => 'required',
				'year' => 'required',
				'locked' => 'required',
			);
			$this->sanitize_array = array(
				'month' => 'sanitize_string',
				'year' => 'sanitize_string',
				'locked' => 'sanitize_string',
			);
			$this->filter_vals = true; //set whether to remove empty fields
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			if($this->validated()){
				$rec_id = $this->rec_id = $db->insert($tablename, $modeldata);
				if($rec_id){
					$this->set_flash_msg("Record added successfully", "success");
					return	$this->redirect("month_locking");
				}
				else{
					$this->set_page_error();
				}
			}
		}
		$page_title = $this->view->page_title = "Add New Month Locking";
		$this->render_view("month_locking/add2.php");
	}
}
