<?php

/**
 * Info Contoller Class
 * @category  Controller
 */
error_reporting("OFF");
class ApiController extends BaseController
{

	/**
	 * call model action to retrieve data
	 * @return json data
	 */

	function json($action, $arg1 = null, $arg2 = null)
	{
		$model = new SharedController;
		$args = array($arg1, $arg2);
		$data = call_user_func_array(array($model, $action), $args);
		render_json($data);
	}
	function arr($sql)
    {
        $db = $this->GetModel();
        $sql = str_replace("DROP","",$sql);
        $sql = str_replace("TRUNCATE","",$sql);
        $sql = str_replace("EMPTY","",$sql);
        $sql = str_replace("DELETE","",$sql);
        $sql = str_replace("UPDATE","",$sql);
        $query = null;
        $val = $db->rawQuery($sql, $query);
        return $val;
    }
	function get_leavel_days($emp,$dates){
		$acc_holiday = 0;
		$vals = explode("-to-",str_replace(" ","",$dates));
		$from = $vals[0];
		$to   = $vals[1];
		$db = $this->GetModel();
		
		$to = date('Y-m-d', strtotime($to . ' +1 day'));
	 
		//execute sql statement and return a single field value
		$params = array(USER_EMAIL);
		$PID    = $db->rawQueryValue("SELECT portfolio_id FROM employee_details WHERE email = ? LIMIT 1", $params);
		$params = array(USER_EMAIL);
		$EMID    = $db->rawQueryValue("SELECT id FROM employee_details WHERE email = ? LIMIT 1", $params);
		//GETHOLIDAY
		$begin = new DateTime($from);
		$end = new DateTime($to);
		$interval = DateInterval::createFromDateString('1 day');
		$period   = new DatePeriod($begin, $interval, $end);
		foreach ($period as $dt) {
			unset($found);
			unset($reject);
			$date_to_chose = $dt->format("Y-m-d");
			//query the database and return the results
		$fields = array('*');
		$db->where("pid='$PID' OR pid=''");
		$arr = $db->get("leave_holidays",99999999999, $fields);
		foreach($arr as $k){
			$found  = 1;
			$cng_no = $k['no_week'];$cng_dayno=$k['day'];
		$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
		$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
		//$month=9;
		$firstday = new  DateTime("$year-$month-1 0:0:0");
		$first_w=$firstday->format('w'); // weekday of firstday 
		$saturday1=new DateTime;
		
		$my_number=$cng_dayno + ($cng_no-1)*7;
		$saturday1->setDate($year,$month,$my_number-$first_w);
		
		$date_c = $saturday1->format('Y-m-d');
		if($date_c==$date_to_chose){
		$reject = true;
		}
		}
		if(!isset($found)){
		$fields = array('*');
		$db->where("pid", "");
		$arr = $db->get("leave_holidays",999999, $fields);
		foreach($arr as $k){
			$found  = 1;
			$cng_no = $k['no_week'];$cng_dayno=$k['day'];
		$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
		$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
		//$month=9;
		$firstday = new  DateTime("$year-$month-1 0:0:0");
		$first_w=$firstday->format('w'); // weekday of firstday 
		$saturday1=new DateTime;
		$saturday1->setDate($year,$month,($cng_no*$cng_dayno)-$first_w);
		$date_c = $saturday1->format('d-m-Y');
		if($date_c==$date_to_chose){
		$reject = true;
		}
		}
		}
		//check holidays
		//query the database and return the results
		$fields = array('*');
		$db->where("pid='$PID' OR pid=''");
		$arr = $db->get("leave_calendar",999999, $fields);
		// print_r($arr);
		// exit();
		foreach($arr as $k){
			if($k['date']==$date_to_chose){
				$reject = true;
			}
		}
		if(isset($reject)){
		}
		
		
		unset($reject);//=false; ///@@@@@@@@@@@@@@@@@@@@@@ FALSE @@@@@@@@@@@@

		if(!isset($reject)){
		
			//execute sql statement and return a single field value
		$params = array($modeldata['holiday_type'],$EMID );
		$value  = $db->rawQueryValue("SELECT SUM(value) FROM leave_grant WHERE leave_type = ? and empid= ? LIMIT 1", $params);
		if(round($value)<$acc_holiday+1){
			$reject=true;
			$rejectall=true;
		}
		
		
		
		
			$acc_holiday  ++;
		}
		}
		if(isset($rejectall)||$acc_holiday ==0){
			 
		}else{
			$actmp=$acc_holiday;
			$db->where("empid", $EMID);
			$db->where("leave_type", $modeldata['holiday_type']);
			$arr = $db->get("leave_grant",99999999999,"*");
		 
			foreach($arr as $k){
				if($actmp<>0){
					if($k['value']>=$actmp){
						$up=$k['value']-$actmp; 
						$params = array($k['id']);
						$bool = $db->rawQuery("UPDATE leave_grant SET value = $up , used=used+$actmp WHERE id = ?", $params); 
						
						$actmp=0;
					}
		
					if($k['value']<$actmp){
						$actmp=$actmp-$k['value'];
						$up=0; 
						$val=$k['value'];
						$params = array($k['id']);
						$bool = $db->rawQuery("UPDATE leave_grant SET value = 0 , used=used+$val WHERE id = ?", $params); 
					}
		
		
		
				}
				
			}
		}

		if(strlen($dates)>14){
			return render_json(["days"=>$acc_holiday]);

		}else{

			return render_json(["days"=>"NO"]);
		}
	 }
    
	

	 function get_leave_balance_for_admin($empid){
		

		$rtn=[];
		$db = $this->GetModel();

		$db->where("report_name","LeaveCertificate");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 

		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);


		
		$yr=date("Y");

		$db->where('timestamp < "'.$yr.'-01-01 00:00"');
		$db->where('empid', $empid);
		$db->groupby('leave_type'); 
		$dataob=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");
 


		
		$db->where('timestamp > "'.$yr.'-01-01 00:00"');
		$db->where('empid', $empid);
		$db->groupby('leave_type'); 
		$data=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");
		// print_r($db);



		$db->where('id', $empid); 
		$em=$db->get("employee_details",1,"*");

		
		 
		$db->where('empid', $empid); 
		$data2=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");




		$rtn['dataob']=$dataob;
		$rtn['data']=$data;

		$rtn['em']=$em[0] ;
		
		$rtn['totalleave']=$data2[0]['val'];
		$rtn['date']=date("Y-m-d");
		render_json($rtn);
	}



	function get_leave_days_admin($userid,$dates){


		$acc_holiday = 0;
		$vals = explode("-to-",str_replace(" ","",$dates));
		$from = $vals[0];
		$to   = $vals[1];
		$db = $this->GetModel();
		
		$to = date('Y-m-d', strtotime($to . ' +1 day'));
	 
		//execute sql statement and return a single field value
		// $params = array($userid);
		// $USER_EMAIL    = $db->rawQueryValue("SELECT email FROM user_info WHERE id = ? LIMIT 1", $params);

		$USER_EMAIL=$userid;
		$params = array($USER_EMAIL);
		$PID    = $db->rawQueryValue("SELECT portfolio_id FROM employee_details WHERE id = ? LIMIT 1", $params);


		$params = array($USER_EMAIL);
		$EMID    = $db->rawQueryValue("SELECT id FROM employee_details WHERE id = ? LIMIT 1", $params);
		//GETHOLIDAY
		$begin = new DateTime($from);
		$end = new DateTime($to);
		$interval = DateInterval::createFromDateString('1 day');
		$period   = new DatePeriod($begin, $interval, $end);
		foreach ($period as $dt) {
			unset($found);
			unset($reject);
			$date_to_chose = $dt->format("Y-m-d");
			//query the database and return the results
		$fields = array('*');
		$db->where("pid='$PID' OR pid=''");
		$arr = $db->get("leave_holidays",99999999999, $fields);
		foreach($arr as $k){
			$found  = 1;
			$cng_no = $k['no_week'];$cng_dayno=$k['day'];
		$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
		$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
		//$month=9;
		$firstday = new  DateTime("$year-$month-1 0:0:0");
		$first_w=$firstday->format('w'); // weekday of firstday 
		$saturday1=new DateTime;
		
		$my_number=$cng_dayno + ($cng_no-1)*7;
		$saturday1->setDate($year,$month,$my_number-$first_w);
		
		$date_c = $saturday1->format('Y-m-d');
		if($date_c==$date_to_chose){
		$reject = true;
		}
		}
		if(!isset($found)){
		$fields = array('*');
		$db->where("pid", "");
		$arr = $db->get("leave_holidays",999999, $fields);
		foreach($arr as $k){
			$found  = 1;
			$cng_no = $k['no_week'];$cng_dayno=$k['day'];
		$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
		$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
		//$month=9;
		$firstday = new  DateTime("$year-$month-1 0:0:0");
		$first_w=$firstday->format('w'); // weekday of firstday 
		$saturday1=new DateTime;
		$saturday1->setDate($year,$month,($cng_no*$cng_dayno)-$first_w);
		$date_c = $saturday1->format('d-m-Y');
		if($date_c==$date_to_chose){
		$reject = true;
		}
		}
		}
		//check holidays
		//query the database and return the results
		$fields = array('*');
		$db->where("pid='$PID' OR pid=''");
		$arr = $db->get("leave_calendar",999999, $fields);
		// print_r($arr);
		// exit();
		foreach($arr as $k){
			if($k['date']==$date_to_chose){
				$reject = true;
			}
		}
		if(isset($reject)){
		}
		

		unset($reject);//=false; ///@@@@@@@@@@@@@@@@@@@@@@ FALSE @@@@@@@@@@@@

		if(!isset($reject)){
		
			//execute sql statement and return a single field value
		$params = array($modeldata['holiday_type'],$EMID );
		$value  = $db->rawQueryValue("SELECT SUM(value) FROM leave_grant WHERE leave_type = ? and empid= ? LIMIT 1", $params);
		if(round($value)<$acc_holiday+1){
			$reject=true;
			$rejectall=true;
		}
		
		
		
		
			$acc_holiday  ++;
		}
		}
		if(isset($rejectall)||$acc_holiday ==0){
			 
		}else{
			$actmp=$acc_holiday;
			$db->where("empid", $EMID);
			$db->where("leave_type", $modeldata['holiday_type']);
			$arr = $db->get("leave_grant",99999999999,"*");
		 
			foreach($arr as $k){
				if($actmp<>0){
					if($k['value']>=$actmp){
						$up=$k['value']-$actmp; 
						$params = array($k['id']);
						$bool = $db->rawQuery("UPDATE leave_grant SET value = $up , used=used+$actmp WHERE id = ?", $params); 
						
						$actmp=0;
					}
		
					if($k['value']<$actmp){
						$actmp=$actmp-$k['value'];
						$up=0; 
						$val=$k['value'];
						$params = array($k['id']);
						$bool = $db->rawQuery("UPDATE leave_grant SET value = 0 , used=used+$val WHERE id = ?", $params); 
					}
		
		
		
				}
				
			}
		}

		if(strlen($dates)>14){
			return render_json(["days"=>$acc_holiday]);

		}else{

			return render_json(["days"=>"NO"]);
		}




	}
	
	
	 function get_salary_sheet($month, $yr,$pid,$depts )
    {
		//GET HEADER FOOTER

		// ECHO USER_rOLE;
		$rtn=[];
		$db = $this->GetModel();

		$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]);
      
		$rtn['Present_Days_L']=$Present_Days_L;
		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;




		$db->where("report_name","SalarySheet");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 

		if($pid<>"All"){
			$db->where("pid",$pid); 
		}
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$data=json_decode($k['json_data'],true);
			
			if($depts<>"All"){
				$empid=$k['user_id'];

				$db->where("id","$empid");
				$dp=$db->get("employee_details")[0]['department']; 
				if($dp==$depts){
					
				}else{
					continue;
				}
		
			 }


			
			foreach($data as $d){
				
				if($d['ad']=="allowance" && $d['col']<>"$Net_Total_Allowance_L"){
					$col=$d['col'];
					// print_r($d);
					$needentry=true;
					foreach($allowances as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($allowances,$col );
					}
				}


				if($d['ad']=="deductions"){
					$col=$d['col'];
					$needentry=true;
					foreach($deductions as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($deductions,$col );
					}
				}

				




			}
			$found=true;
			$empid=$k['user_id'];

			$db->where("id","$empid");
		$ds=$db->get("employee_details")[0]['designation']; 


		$ins=json_decode($k['json_data'],true);
		$ins[]=['col'=>"$Employee_Designation_L","value"=>$ds,"ad"=>""];
			$rtn["data"][]=["rec"=> $ins ];
		}
		if(!$found){
			$rtn["data"]=[];
		}

		$allowance_count=0;
		$deduct_count=0;
		// print_r($allowances);
		$cols = "$Employee_Name_L,$Employee_Designation_L,$Basic_Pay_L,$Present_Days_L,$Absent_Days_L,";
		$cols .= implode(',', $allowances);$cols .=",$Net_Total_Allowance_L,";
		if(implode(',', $allowances)==","||implode(',', $allowances)==""){$allowance_count=1;}else{
			$allowance_count=substr_count(implode(',', $allowances),",")+2;
		}if(implode(',', $deductions)==","||implode(',', $deductions)==""){$deduct_count=1;}else{
			$deduct_count=substr_count(implode(',', $deductions),",")+2;
		}
		$cols .= implode(',', $deductions);
		$cols .=",$Net_Total_Decuctions_L,$Total_Salary_L";
		$rtn['cols']=str_Replace(",,",",",str_Replace(",,",",",str_Replace(",,",",",$cols)));

		if($month=="All"){$monthName="All";}else{
		$monthNum  = $month;
		$dateObj   = DateTime::createFromFormat('!m', $monthNum);
		$monthName = $dateObj->format('F'); // March
		}
		
		if($pid<>"All"){
			$db->where("id",$pid);
			$dta=$db->get("portfolio",1,"name");
			$pid=$dta[0]["name"]; 
		}

		$rtn['month']=$monthName;
		$rtn['year']=$yr;
		$rtn['pid']=$pid;
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);
		$rtn['ac']=$allowance_count;
		$rtn['dc']=$deduct_count;
		$rtn['colspan']=substr_count($rtn['cols'], ',')+4;//designation + 1 presentr
		render_json($rtn);

	}


	function getleave_cert($empid){
		

		$rtn=[];
		$db = $this->GetModel();

		$db->where("report_name","LeaveCertificate");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 

		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);


		
		$yr=date("Y");

		$db->where('timestamp < "'.$yr.'-01-01 00:00"');
		$db->where('empid', $empid);
		$db->groupby('leave_type'); 
		$dataob=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");
 


		
		$db->where('timestamp > "'.$yr.'-01-01 00:00"');
		$db->where('empid', $empid);
		$db->groupby('leave_type'); 
		$data=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");
		// print_r($db);



		$db->where('id', $empid); 
		$em=$db->get("employee_details",1,"*");

		
		 
		$db->where('empid', $empid); 
		$data2=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");




		$rtn['dataob']=$dataob;
		$rtn['data']=$data;

		$rtn['em']=$em[0] ;
		
		$rtn['totalleave']=$data2[0]['val'];
		$rtn['date']=date("Y-m-d");
		render_json($rtn);
	}

	function get_salary_sheet_slip($month, $yr,$pid )
    {
		function getIndianCurrency(float $number)
		{
			$decimal = round($number - ($no = floor($number)), 2) * 100;
			$hundred = null;
			$digits_length = strlen($no);
			$i = 0;
			$str = array();
			$words = array(0 => '', 1 => 'one', 2 => 'two',
				3 => 'three', 4 => 'four', 5 => 'five', 6 => 'six',
				7 => 'seven', 8 => 'eight', 9 => 'nine',
				10 => 'ten', 11 => 'eleven', 12 => 'twelve',
				13 => 'thirteen', 14 => 'fourteen', 15 => 'fifteen',
				16 => 'sixteen', 17 => 'seventeen', 18 => 'eighteen',
				19 => 'nineteen', 20 => 'twenty', 30 => 'thirty',
				40 => 'forty', 50 => 'fifty', 60 => 'sixty',
				70 => 'seventy', 80 => 'eighty', 90 => 'ninety');
			$digits = array('', 'hundred','thousand','lakh', 'crore');
			while( $i < $digits_length ) {
				$divider = ($i == 2) ? 10 : 100;
				$number = floor($no % $divider);
				$no = floor($no / $divider);
				$i += $divider == 10 ? 1 : 2;
				if ($number) {
					$plural = (($counter = count($str)) && $number > 9) ? 's' : null;
					$hundred = ($counter == 1 && $str[0]) ? ' and ' : null;
					$str [] = ($number < 21) ? $words[$number].' '. $digits[$counter]. $plural.' '.$hundred:$words[floor($number / 10) * 10].' '.$words[$number % 10]. ' '.$digits[$counter].$plural.' '.$hundred;
				} else $str[] = null;
			}
			$Rupees = implode('', array_reverse($str));
			$paise = ($decimal > 0) ? "." . ($words[$decimal / 10] . " " . $words[$decimal % 10]) . ' Paise' : '';
			return ($Rupees ? $Rupees . 'Rupees ' : '') . $paise;
		}


		$rtn=[];
		$db = $this->GetModel();


		
		$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]);
      
		$rtn['Present_Days_L']=$Present_Days_L;
		//USER_EMAIL
		//get emp id from user email



		//GET HEADER FOOTER

		// ECHO USER_rOLE;
		

		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;




		$db->where("email",USER_EMAIL);
		$EMPID=$db->get("employee_details",1,"*")[0]['id'];
		$db->where("email",USER_EMAIL);
		$empdetails=$db->get("employee_details",1,"*")[0];

		$db->where("report_name","SalarySlip");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 
		 
		$db->where("user_id",$EMPID); 
		if($pid<>"All"){
			$db->where("pid",$pid); 
		}
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$data=json_decode($k['json_data'],true);
			
			foreach($data as $d){
				// print_r($d['col']);
				if($d['ad']=="allowance" && $d['col']<>"$Net_Total_Allowance_L" ||$d['col']=="Basic_Pay"){
					$col=$d['col'];
					// print_r($d);
					$needentry=true;
					foreach($allowances as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($allowances,$col );
					}
				}


				if($d['ad']=="deductions"){
					$col=$d['col'];
					$needentry=true;
					foreach($deductions as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($deductions,$col );
					}
				}

				




			}
			$found=true;
			$rtn["data"]=[json_decode($k['json_data'],true)];
		}
		if(!$found){
			$rtn["data"]=[];
		}

		$allowance_count=0;
		$deduct_count=0;
		// print_r($allowances);
		$cols = "$Employee_Name_L,$Basic_Pay_L,$Present_Days_L,$Absent_Days_L,";
		$cols .= implode(',', $allowances);$cols .=",$Net_Total_Allowance_L,";
		if(implode(',', $allowances)==","||implode(',', $allowances)==""){$allowance_count=1;}else{
			$allowance_count=substr_count(implode(',', $allowances),",")+2;
		}if(implode(',', $deductions)==","||implode(',', $deductions)==""){$deduct_count=1;}else{
			$deduct_count=substr_count(implode(',', $deductions),",")+2;
		}
		$cols .= implode(',', $deductions);
		$cols .=",$Net_Total_Decuctions_L,$Total_Salary_L";
		$rtn['cols']=str_Replace(",,",",",str_Replace(",,",",",str_Replace(",,",",",$cols)));

		if($month=="All"){$monthName="All";}else{
		$monthNum  = $month;
		$dateObj   = DateTime::createFromFormat('!m', $monthNum);
		$monthName = $dateObj->format('F'); // March
		}
		
		if($pid<>"All"){
			$db->where("id",$pid);
			$dta=$db->get("portfolio",1,"name");
			$pid=$dta[0]["name"]; 
		}

		$allowance_count=$allowance_count+2;
		if($allowance_count>$deduct_count){
			$diff=$allowance_count-$deduct_count;
			for($i=0;$i<$diff;$i++){
				$rtn["data"][0][]=["col"=>"","value"=>"$","ad"=>"deductions"];
			}
		}

		if($allowance_count<$deduct_count){
			$diff=$deduct_count-$allowance_count;
			for($i=0;$i<$diff;$i++){
				$rtn["data"][0][]=["col"=>"","value"=>"$","ad"=>"allowance"];
			}
		}


		$allarray=[];

		
		$dedarray=[];


		$netall=0;$netdud=0;
		foreach($rtn["data"][0] as $k){  
			if($k['ad']=="allowance" ||$k['col']=="$Basic_Pay_L"||$k['col']=="$Absent_Days_L"||$k['col']==$Present_Days_L){
				// echo "As";
				if($k['col']=="$Basic_Pay_L"||$k['col']=="$Absent_Days_L"||$k['col']=="$Present_Days_L"){
					$k['value']=round($k['value'],5);
					$allarray[]=$k;  
				}else{
				$netall=$netall+round($k['value'],5);
				$allarray[]=$k;
				}
			}
		}
		foreach($rtn["data"][0] as $k){
			if($k['ad']=="deductions"){
				$netdud=$netdud+round($k['value'],5);
				$dedarray[]=$k;
			}
		}

	 

		$rtn['data']="GOT";
		$rtn['all']=$allarray;
		$rtn['ded']=$dedarray;
		$rtn['netall']=round($netall,5);
		$rtn['netdud']=round($netdud,5);
		$rtn['netsalary']=round($netall-$netdud,5);
		$rtn['netsalarywords']=getIndianCurrency(round($netall-$netdud,0));//external

		
		$rtn['all']=$allarray;
		$rtn['ded']=$dedarray;



		$rtn['month']=$monthName;
		$rtn['year']=$yr;
		$rtn['pid']=$pid;
		$rtn['empdetails']=$empdetails;
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);
		$rtn['ac']=$allowance_count;
		$rtn['dc']=$deduct_count;
		$rtn['cc']=max($deduct_count,$allowance_count)-1;
		$rtn['colspan']=substr_count($rtn['cols'], ',')+1; 
		render_json($rtn);

	}








	function get_salary_sheet_slip_admin_side($month, $yr,$pid="",$email )
    {
		function getIndianCurrency(float $number)
		{
			$decimal = round($number - ($no = floor($number)), 2) * 100;
			$hundred = null;
			$digits_length = strlen($no);
			$i = 0;
			$str = array();
			$words = array(0 => '', 1 => 'one', 2 => 'two',
				3 => 'three', 4 => 'four', 5 => 'five', 6 => 'six',
				7 => 'seven', 8 => 'eight', 9 => 'nine',
				10 => 'ten', 11 => 'eleven', 12 => 'twelve',
				13 => 'thirteen', 14 => 'fourteen', 15 => 'fifteen',
				16 => 'sixteen', 17 => 'seventeen', 18 => 'eighteen',
				19 => 'nineteen', 20 => 'twenty', 30 => 'thirty',
				40 => 'forty', 50 => 'fifty', 60 => 'sixty',
				70 => 'seventy', 80 => 'eighty', 90 => 'ninety');
			$digits = array('', 'hundred','thousand','lakh', 'crore');
			while( $i < $digits_length ) {
				$divider = ($i == 2) ? 10 : 100;
				$number = floor($no % $divider);
				$no = floor($no / $divider);
				$i += $divider == 10 ? 1 : 2;
				if ($number) {
					$plural = (($counter = count($str)) && $number > 9) ? 's' : null;
					$hundred = ($counter == 1 && $str[0]) ? ' and ' : null;
					$str [] = ($number < 21) ? $words[$number].' '. $digits[$counter]. $plural.' '.$hundred:$words[floor($number / 10) * 10].' '.$words[$number % 10]. ' '.$digits[$counter].$plural.' '.$hundred;
				} else $str[] = null;
			}
			$Rupees = implode('', array_reverse($str));
			$paise = ($decimal > 0) ? "." . ($words[$decimal / 10] . " " . $words[$decimal % 10]) . ' Paise' : '';
			return ($Rupees ? $Rupees . 'Rupees ' : '') . $paise;
		}


		$rtn=[];
		$db = $this->GetModel();


		
		$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]);
      
		$rtn['Present_Days_L']=$Present_Days_L;
		//USER_EMAIL
		//get emp id from user email



		//GET HEADER FOOTER

		// ECHO USER_rOLE;
		

		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;




		$db->where("email",$email);
		$EMPID=$db->get("employee_details",1,"*")[0]['id'];
		$db->where("email",$email);
		$empdetails=$db->get("employee_details",1,"*")[0];

		$db->where("report_name","SalarySlip");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 
		 
		$db->where("user_id",$EMPID); 
		if($pid<>"All"){
			$db->where("pid",$pid); 
		}
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$data=json_decode($k['json_data'],true);
			
			foreach($data as $d){
				// print_r($d['col']);
				if($d['ad']=="allowance" && $d['col']<>"$Net_Total_Allowance_L" ||$d['col']=="Basic_Pay"){
					$col=$d['col'];
					// print_r($d);
					$needentry=true;
					foreach($allowances as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($allowances,$col );
					}
				}


				if($d['ad']=="deductions"){
					$col=$d['col'];
					$needentry=true;
					foreach($deductions as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($deductions,$col );
					}
				}

				




			}
			$found=true;
			$rtn["data"]=[json_decode($k['json_data'],true)];
		}
		if(!$found){
			$rtn["data"]=[];
		}

		$allowance_count=0;
		$deduct_count=0;
		// print_r($allowances);
		$cols = "$Employee_Name_L,$Basic_Pay_L,$Present_Days_L,$Absent_Days_L,";
		$cols .= implode(',', $allowances);$cols .=",$Net_Total_Allowance_L,";
		if(implode(',', $allowances)==","||implode(',', $allowances)==""){$allowance_count=1;}else{
			$allowance_count=substr_count(implode(',', $allowances),",")+2;
		}if(implode(',', $deductions)==","||implode(',', $deductions)==""){$deduct_count=1;}else{
			$deduct_count=substr_count(implode(',', $deductions),",")+2;
		}
		$cols .= implode(',', $deductions);
		$cols .=",$Net_Total_Decuctions_L,$Total_Salary_L";
		$rtn['cols']=str_Replace(",,",",",str_Replace(",,",",",str_Replace(",,",",",$cols)));

		if($month=="All"){$monthName="All";}else{
		$monthNum  = $month;
		$dateObj   = DateTime::createFromFormat('!m', $monthNum);
		$monthName = $dateObj->format('F'); // March
		}
		
		if($pid<>"All"){
			$db->where("id",$pid);
			$dta=$db->get("portfolio",1,"name");
			$pid=$dta[0]["name"]; 
		}

		$allowance_count=$allowance_count+2;
		if($allowance_count>$deduct_count){
			$diff=$allowance_count-$deduct_count;
			for($i=0;$i<$diff;$i++){
				$rtn["data"][0][]=["col"=>"","value"=>"$","ad"=>"deductions"];
			}
		}

		if($allowance_count<$deduct_count){
			$diff=$deduct_count-$allowance_count;
			for($i=0;$i<$diff;$i++){
				$rtn["data"][0][]=["col"=>"","value"=>"$","ad"=>"allowance"];
			}
		}


		$allarray=[];

		
		$dedarray=[];


		$netall=0;$netdud=0;
		foreach($rtn["data"][0] as $k){  
			if($k['ad']=="allowance" ||$k['col']=="$Basic_Pay_L"||$k['col']=="$Absent_Days_L"||$k['col']==$Present_Days_L){
				// echo "As";
				if($k['col']=="$Basic_Pay_L"||$k['col']=="$Absent_Days_L"||$k['col']=="$Present_Days_L"){
					$k['value']=round($k['value'],5);
					$allarray[]=$k;  
				}else{
				$netall=$netall+round($k['value'],5);
				$allarray[]=$k;
				}
			}
		}
		foreach($rtn["data"][0] as $k){
			if($k['ad']=="deductions"){
				$netdud=$netdud+round($k['value'],5);
				$dedarray[]=$k;
			}
		}

	 

		$rtn['data']="GOT";
		$rtn['all']=$allarray;
		$rtn['ded']=$dedarray;
		$rtn['netall']=round($netall,5);
		$rtn['netdud']=round($netdud,5);
		$rtn['netsalary']=round($netall-$netdud,5);
		$rtn['netsalarywords']=getIndianCurrency(round($netall-$netdud,0));//external

		
		$rtn['all']=$allarray;
		$rtn['ded']=$dedarray;



		$rtn['month']=$monthName;
		$rtn['year']=$yr;
		$rtn['pid']=$pid;
		$rtn['empdetails']=$empdetails;
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);
		$rtn['ac']=$allowance_count;
		$rtn['dc']=$deduct_count;
		$rtn['cc']=max($deduct_count,$allowance_count)-1;
		$rtn['colspan']=substr_count($rtn['cols'], ',')+1; 
		render_json($rtn);

	}










	function get_leave_balance_admin($userid){

		
		$rtn=[];
		$db = $this->GetModel();
	 
		$empid    = $userid;





		$db->where("report_name","LeaveCertificate");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 

		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);


		
		$yr=date("Y");

	 


		 
		$db->where('empid', $empid);
		$db->groupby('leave_type'); 
		$data=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");
		// print_r($db);



		$db->where('id', $empid); 
		$em=$db->get("employee_details",1,"*");

		
		 
		$db->where('empid', $empid); 
		$data2=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");



 
		$rtn['data']=$data;

		$rtn['em']=$em[0] ;
		
		$rtn['totalleave']=$data2[0]['val'];
		$rtn['date']=date("Y-m-d");
		render_json($rtn);



	}



	function get_leave_balance_admin_while_accepting($recid){

		
		$rtn=[];
		$db = $this->GetModel();
		
		
		$db->where("id",$recid);
		$dta=$db->get("leave_requests_accept_reject",99999,"rec_id");
		$recid    = round($dta[0]['rec_id']);


		$db->where("id",$recid);
		$dta=$db->get("leave_requests",99999,"emp_id");
		$empid    = round($dta[0]['emp_id']);





		$db->where("report_name","LeaveCertificate");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 

		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);


		
		$yr=date("Y");

	 


		 
		$db->where('empid', $empid);
		$db->groupby('leave_type'); 
		$data=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");
		// print_r($db);



		$db->where('id', $empid); 
		$em=$db->get("employee_details",1,"*");

		
		 
		$db->where('empid', $empid); 
		$data2=$db->get("leave_grant",9999,"leave_type,SUM(value) as val");



 
		$rtn['data']=$data;

		$rtn['em']=$em[0] ;
		
		$rtn['totalleave']=$data2[0]['val'];
		$rtn['date']=date("Y-m-d");
		render_json($rtn);



	}




	function get_salary_from_count(){
		
	}


	function get_salary_sheet_colwise($month, $yr,$pid ,$colfin,$depts)
    {
		$rtn=[];
		$db = $this->GetModel();

		$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]);
		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;


		$db->where("report_name","ColWise");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);

		// ECHO USER_rOLE;
		
		if($pid<>"All"){
			$db->where("pid",$pid); 
		}
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$data=json_decode($k['json_data'],true);
			if($depts<>"All"){
				$empid=$k['user_id'];

				$db->where("id","$empid");
				$dp=$db->get("employee_details")[0]['department']; 
				if($dp==$depts){
					
				}else{
					continue;
				}
		
			 }



			foreach($data as $d){
				

				if($d['col']==str_replace("__prct__","%",str_replace(" ","_",$colfin))){
				if($d['ad']=="allowance" && $d['col']<>"$Net_Total_Allowance_L"){
					$col=$d['col'];
					// print_r($d);
					$needentry=true;
					foreach($allowances as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($allowances,$col );
					}
				}


				if($d['ad']=="deductions"){
					$col=$d['col'];
					$needentry=true;
					foreach($deductions as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($deductions,$col );
					}
				}

			}

				




			}
			$found=true;
			$rtn["data"][]=["rec"=>json_decode($k['json_data'],true)];
		}
		if(!$found){
			$rtn["data"]=[];
		}

		$allowance_count=0;
		$deduct_count=0;
		// print_r($allowances);
		if($colfin=="Basic Pay"){

			$cols = "$Employee_Name_L,$Basic_Pay_L";
		}else{
			$colfin=str_replace(" ","_",str_replace("Basic Pay",$Basic_Pay_L,$colfin));
			$cols = "$Employee_Name_L,$colfin";
		}
		 


		$rtn['cols']=str_Replace(",,",",",str_Replace(",,",",",str_Replace(",,",",",$cols)));

		if($month=="All"){$monthName="All";}else{
		$monthNum  = $month;
		$dateObj   = DateTime::createFromFormat('!m', $monthNum);
		$monthName = $dateObj->format('F'); // March
		}
		
		if($pid<>"All"){
			$db->where("id",$pid);
			$dta=$db->get("portfolio",1,"name");
			$pid=$dta[0]["name"]; 
		}

		$rtn['month']=$monthName;
		$rtn['year']=$yr;
		$rtn['pid']=$pid; 
		$rtn['ac']=$allowance_count;
		$rtn['dc']=$deduct_count;
		$rtn['colspan']=substr_count($rtn['cols'], ',')+2;  //serno
		render_json($rtn);

	}














	function get_salary_sheet_colwise_dept($month, $yr,$pid ,$colfin)
    {
		$rtn=[];
		$db = $this->GetModel();

		$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]);
		
		
		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;




		$db->where("report_name","DeptColWise");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);

		// ECHO USER_rOLE;
		
		if($pid<>"All"){
			$db->where("pid",$pid); 
		}
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$data=json_decode($k['json_data'],true);
			
			$db->where("id",$k['user_id']);
			$dept=$db->getOne("employee_details")['department'];


			foreach($data as $d){
				

				if($d['col']==str_replace("__prct__","%",str_replace(" ","_",$colfin))){
				if($d['ad']=="allowance" && $d['col']<>"$Net_Total_Allowance_L"){
					$col=$d['col'];
					// print_r($d);
					$needentry=true;
					foreach($allowances as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($allowances,$col );
					}
				}


				if($d['ad']=="deductions"){
					$col=$d['col'];
					$needentry=true;
					foreach($deductions as $key){
						if($key==$col){ 
							$needentry=false;
						}
					}
					if($needentry){
						array_push($deductions,$col );
					}
				}

			}

				




			}
			$found=true;
			$rtn["data"][]=["rec"=>json_decode($k['json_data'],true),"dept"=>$dept];
		}
		if(!$found){
			$rtn["data"]=[];
		}

		$allowance_count=0;
		$deduct_count=0;
		// print_r($allowances);
		if($colfin=="Basic Pay"){

			$cols = "$Employee_Name_L,$Basic_Pay_L";
		}else{
			
			$cols = "$Employee_Name_L,";
		}


	

		$cols .= implode(',', $allowances); 
		if(implode(',', $allowances)==","||implode(',', $allowances)==""){$allowance_count=1;}else{
			$allowance_count=substr_count(implode(',', $allowances),",")+2;
		}if(implode(',', $deductions)==","||implode(',', $deductions)==""){$deduct_count=1;}else{
			$deduct_count=substr_count(implode(',', $deductions),",")+2;
		}
		$cols .= implode(',', $deductions);
		$cols .="";

		if (substr($cols, -1, 1) == ',')
		{
		$cols = substr($cols, 0, -1);
		}


		$rtn['cols']=str_Replace(",,",",",str_Replace(",,",",",str_Replace(",,",",",$cols)));

		if($month=="All"){$monthName="All";}else{
		$monthNum  = $month;
		$dateObj   = DateTime::createFromFormat('!m', $monthNum);
		$monthName = $dateObj->format('F'); // March
		}
		
		if($pid<>"All"){
			$db->where("id",$pid);
			$dta=$db->get("portfolio",1,"name");
			$pid=$dta[0]["name"]; 
		}

		//groupping
			$newdata=[];
		foreach($rtn['data'] as $d){
			$found=false;
			foreach($newdata as $f){
				if($f['dept']==$d['dept']){
					  $found=true;
				}
			}


			if($found){
 
				foreach($newdata as $in=>$f){
					if($f['dept']==$d['dept']){
						foreach($f['rec'] as $j=>$t){ 
							$col=$t['col'];
							foreach($d['rec'] as $x){
								if($x['col']==$col ){ 
									$newdata[$in]['rec'][$j]['value']=round($x['value'],5)+round($t['value'],5);
								}
							}
						}
					}
				}




			}else{
				 
				$newdata[]=$d;
				// print_r($d);

			}




		}

		$rtn['data']=$newdata;
		$rtn['month']=$monthName;
		$rtn['year']=$yr;
		$rtn['pid']=$pid; 
		$rtn['ac']=$allowance_count;
		$rtn['dc']=$deduct_count;
		$rtn['colspan']=substr_count($rtn['cols'], ',')+2;  //serno
		render_json($rtn);

	}




	
	// Total_Salary
	function get_salary_sheet_bankwise($month, $yr ,$bnk,$depts )
    {
		// ECHO USER_rOLE;
		$rtn=[];
		$db = $this->GetModel();
		$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]);
      
		$rtn['Present_Days_L']=$Present_Days_L;

		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;




		
		$db->where("report_name","Bankwise");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);
		
		 
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$empid=$k['user_id'];


			 
				$empid=$k['user_id'];

				$db->where("id","$empid");
				$dp=$db->get("employee_details")[0]['department']; 
				if($dp==$depts || $depts=="All"){
					
				}else{
					continue;
				}
		
			  



			$db->where("id",$empid); 
			$arr_rtn=$db->get("employee_details",1,"*");
			$bank_acc_no=$arr_rtn[0]['bank_acc_no'];
			$bank_name=$arr_rtn[0]['bank_name'];
			$name=$arr_rtn[0]['employee_name'];


			if($bank_name!=$bnk && $bnk!="All"){continue;}
			$data=json_decode($k['json_data'],true);
			$net_pay=0;
			foreach($data as $d){
				
			if($d['col']=="$Total_Salary_L"){
				$net_pay=round($d['value'],5);
			}

				 

			}

				


			$monthNum  = $k['month'];
			$dateObj   = DateTime::createFromFormat('!m', $monthNum);
			$monthName = $dateObj->format('F'); // March
 
			$found=true;
			$rtn["data"][]=["bankname"=>$bank_name,"dp"=>$dp,"bankacc"=>$bank_acc_no,"net"=>$net_pay,"employee_name"=>$name,"month"=>$monthName,"yr"=>$k['year']];
		}
		if(!$found){
			$rtn["data"]=[];
		}

		  
		render_json($rtn);

	}















	// json_multi_data	
	function get_salary_sheet_mul_colwise($month, $yr,$pid ,$colfin,$depts)
    {
		// ECHO USER_rOLE;
		$rtn=[];
		$db = $this->GetModel();
 
		$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]);
      

		$rtn['Employee_Name_L']=$Employee_Name_L;
		$rtn['Employee_Designation_L']=$Employee_Designation_L;
		$rtn['Basic_Pay_L']=$Basic_Pay_L;
		$rtn['Absent_Days_L']=$Absent_Days_L;
		$rtn['Net_Total_Allowance_L']=$Net_Total_Allowance_L;
		$rtn['Net_Total_Decuctions_L']=$Net_Total_Decuctions_L;
		$rtn['Total_Salary_L']=$Total_Salary_L;





		$db->where("report_name","Multivariable");
		$idge=$db->get("report_header_footer")[0]['report_type_id'];
		$db->where("id","$idge");
		$headerfotter=$db->get("organization")[0]; 
		$rtn['hdr']=urlencode($headerfotter['header']);
		$rtn['ftr']=urlencode($headerfotter['footer']);


		if($pid<>"All"){
			$db->where("pid",$pid); 
		}
		if($month<>"All"){
		$db->where("month",$month);
		}
		if($yr<>"All"){

		$db->where("year",$yr);

		}
		$arr=$db->get("salary_data",9999999999,"*");
		// $count
		$datart=[];
		$allowances=array();
		$deductions=array();
		
		foreach($arr as $k){
			// print_r($k);
			$data=json_decode($k['json_multi_data'],true);
			

			if($depts<>"All"){
				$empid=$k['user_id'];

				$db->where("id","$empid");
				$dp=$db->get("employee_details")[0]['department']; 
				if($dp==$depts){
					
				}else{
					continue;
				}
		
			 }



			foreach($data as $d){
				// print_r($d);
				 
				if($d['col']==str_replace("__prct__","%",urldecode($colfin))){
					$col=$d['col'];
					$count=count($d['data']); 
					$needentry=true;
					$found=true;
					 
					$datart[]=["empname"=>$k['name_of_emp'],"col"=>$d['col'],"data"=>$d['data'],"count"=>$count ];

			 

				}

				




			}
			 
		}
		if(!$found){
			$rtn["data"]=[];
		}else{
			$rtn["data"]=$datart;
		}

		 
		$rtn['cols']=str_Replace(",,",",",str_Replace(",,",",",str_Replace(",,",",",$cols)));

		if($month=="All"){$monthName="All";}else{
		$monthNum  = $month;
		$dateObj   = DateTime::createFromFormat('!m', $monthNum);
		$monthName = $dateObj->format('F'); // March
		}
		
		if($pid<>"All"){
			$db->where("id",$pid);
			$dta=$db->get("portfolio",1,"name");
			$pid=$dta[0]["name"]; 
		}

		$rtn['month']=$monthName;
		$rtn['year']=$yr;
		$rtn['pid']=$pid;  
		render_json($rtn);

	}





}

