<?php

		// CHECK IF NUMERIC THE MIN VALUE OF CENTERS ON PID TO DO PROPER SHORT USINS IS_NUMERIC
		include('r_dbc.php');
		$sql = "SELECT min(substr(code_center,1,1)) as min_cc from centers where pid = '".$pid."'";
		$result = mysqli_query($conn, $sql);
		if (mysqli_num_rows($result) > 0) {
			  while($row = mysqli_fetch_assoc($result)) {
				$min_cc 	= $row["min_cc"];		
				}
		} 
		mysqli_close($conn);
		
		// VARIABLE FOR CORRECT SORT ODRDER ON GROUP
			if (is_numeric($min_cc) == 1) 
			{
				$var_zz = "";
			}
			else
			{
				$var_zz = "Z";
			}

// DATA IMPORT

include('r_dbc.php');

$sql = "insert into ccreport (repid,pid,ordr,ordr2,code_center,desc_center)
		SELECT DISTINCT ".$repid.",".$pid.",x.ordr,x.ordr2,x.code_center,x.desc_center
		from (
		SELECT '01' as ordr,r.code_center as ordr2,r.code_center,g.desc_center
		from ccmanager r, centers g
		where r.code_center = g.code_center
		and r.pid = g.pid
		and g.pid = ".$pid."
		and r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'
		".$sql01."		
		".$user_cc3."		
		".$user_ct3."		
		group by r.code_center
		UNION 
		SELECT '02' as ordr,concat(c.isgroup,'.9999') as ordr2,c.isgroup as code_center,concat('TOTAL ',g.desc_center) as desc_center
		from ccmanager r, centers c, centers g
		where r.code_center = c.code_center
		and r.pid = c.pid
		and c.isgroup = g.code_center
		and r.pid = g.pid
		and g.pid = ".$pid."		
		and r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'
		".$sql01."		
		".$user_cc3."		
		".$user_ct3."		
		group by c.isgroup
		UNION 
		SELECT '03' as ordr,concat(c.code_center_group,'.9999') as ordr2,c.code_center_group as code_center,concat('TOTAL ',g.desc_center_group) as desc_center
		from ccmanager r, centers c, centers_groups g
		where r.code_center = c.code_center
		and r.pid = c.pid
		and c.code_center_group = g.code_center_group
		and r.pid = g.pid
		and g.pid = ".$pid."		
		and r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'
		".$sql01."		
		".$user_cc3."		
		".$user_ct3."		
		group by c.code_center_group		
		UNION 
		SELECT '04' as ordr,'Z999.9999' as ordr2,'TOTAL' as code_center,'' as desc_center
		from ccmanager r
		where r.pid = ".$pid."		
		and r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'
		".$sql01."		
		".$user_cc3."				
		".$user_ct3."				
		) as x
		group by x.ordr,x.ordr2,x.code_center,x.desc_center
		order by x.ordr2";
		
		$result = mysqli_query($conn, $sql);

mysqli_close($conn);

?>

<?php
// ORDER SETUP
include('r_dbc.php');
	$sqlupd_code = "update ccreport a 
					set a.code_center_group = (select concat(x.code_center_group,'.',x.code_center,'.01')
													from centers x
													where x.code_center = a.code_center
													and x.pid = '".$pid."')
					where a.repid = '".$repid."'";
					// echo $sqlupd_code;
	$resultupd_code = mysqli_query($conn, $sqlupd_code);
	mysqli_close($conn);

include('r_dbc.php');
	$sqlupd_code = "update ccreport a 
					set a.code_center_group = (select concat(x.code_center_group,'.',x.code_center,'.','".$var_zz."','9999')
													from centers x
													where x.code_center = a.code_center
													and x.isgroup = 'GRP1'
													and x.pid = '".$pid."')
					where a.repid = '".$repid."' and code_center in (select code_center from centers where pid = '".$pid."' and isgroup = 'GRP1')";
					// echo $sqlupd_code;
	$resultupd_code = mysqli_query($conn, $sqlupd_code);
	mysqli_close($conn);

	
include('r_dbc.php');
	$sqlupd_code = "update ccreport a 
					set a.code_center_group = (select concat(x.code_center_group,'.','".$var_zz."','9999')
													from centers_groups x
													where x.code_center_group = a.code_center
													and x.pid = '".$pid."')
					where a.repid = '".$repid."' and code_center_group is null";
					// echo $sqlupd_code;
	$resultupd_code = mysqli_query($conn, $sqlupd_code);
	mysqli_close($conn);





		
		// CHECK IS NUMERIC FOR CODE_CENTER WHERE IT IS NULL
		if (is_numeric($min_cc) == 1) {
		include('r_dbc.php');
			$sqlupd_code = "update ccreport a 	set a.code_center_group = '99999999999' 
							where a.repid = '".$repid."' and code_center_group is null";
							// echo $sqlupd_code;
			$resultupd_code = mysqli_query($conn, $sqlupd_code);
			mysqli_close($conn);
		} else {
		include('r_dbc.php');
			$sqlupd_code = "update ccreport a 	set a.code_center_group = 'Z99999999999' 
							where a.repid = '".$repid."' and code_center_group is null";
							// echo $sqlupd_code;
			$resultupd_code = mysqli_query($conn, $sqlupd_code);
			mysqli_close($conn);	
		}
		// END OF IS_NUMERIC	
			
			
	include('r_dbc.php');																	
	$sqlupd_code = "update ccreport set ordr2 = code_center_group where repid = '".$repid."'";
					// echo $sqlupd_code;
	$resultupd_code = mysqli_query($conn, $sqlupd_code);
	mysqli_close($conn);		


?>


<?php	

// DATA UPDATE

include('r_dbc.php');
$sqlmid1 = "select mid,delta_col,delta_calc,delta_pm,expenses
			from ccmanager_id 
			where pid='".$pid."' 
			order by 1";

$resultmid1 = mysqli_query($conn, $sqlmid1);

if (mysqli_num_rows($resultmid1) > 0) {
      while($rowmid1 = mysqli_fetch_assoc($resultmid1)) {

		$mid1			= $rowmid1["mid"];
		$delta_col1		= $rowmid1["delta_col"];		
		$delta_calc1	= $rowmid1["delta_calc"];				
		$delta_pm1		= $rowmid1["delta_pm"];						
		$expenses1		= $rowmid1["expenses"];						
		$colx			= "a.col".$mid1;

			
			if ($delta_col1 == 0) {
				$sqlrep = "update ccreport a 
					set ".$colx." = (select sum(r.rab_number)
					from ccmanager r
					where r.mid 		= '".$mid1."'
					and r.pid 			= '".$pid."'
					and r.code_center	= a.code_center
					and r.rab_date >= '".$min_datec."' 
					and r.rab_date <= '".$max_datec."'
					".$user_cc3."				
					".$user_ct3."						
					".$sql01.")
					where a.repid = '".$repid."'";
			
			//	echo $sqlrep;
			//	echo "<br><br>";
				$resultsum = mysqli_query($conn, $sqlrep);


				
				$sqlrep = "update ccreport a 
					set ".$colx." = (select sum(r.rab_number)
					from ccmanager r
					where r.mid 		= '".$mid1."'
					and r.pid 			= '".$pid."'
					and r.code_center in (select code_center from centers where pid = '".$pid."' and isgroup = a.code_center)
					and r.rab_date >= '".$min_datec."' 
					and r.rab_date <= '".$max_datec."'
					".$user_cc3."				
					".$user_ct3."						
					".$sql01.")
					where a.repid = '".$repid."'
					and ".$colx." is null";
			
			//	echo $sqlrep;
			//	echo "<br><br>";
				$resultsum = mysqli_query($conn, $sqlrep);				



				
				$sqlrep = "update ccreport a 
					set ".$colx." = (select sum(r.rab_number)
					from ccmanager r
					where r.mid 		= '".$mid1."'
					and r.pid 			= '".$pid."'
					and r.code_center in (select code_center from centers where pid = '".$pid."' and code_center_group = a.code_center)
					and r.rab_date >= '".$min_datec."' 
					and r.rab_date <= '".$max_datec."'
					".$user_cc3."				
					".$user_ct3."						
					".$sql01.")
					where a.repid = '".$repid."'
					and ".$colx." is null";
			
			//	echo $sqlrep;
			//	echo "<br><br>";
				$resultsum = mysqli_query($conn, $sqlrep);				
				
				$sqlrep = "update ccreport a 
					set ".$colx." = (select sum(r.rab_number)
					from ccmanager r
					where r.mid 		= '".$mid1."'
					and r.pid 			= '".$pid."'
					and r.rab_date >= '".$min_datec."' 
					and r.rab_date <= '".$max_datec."'
					".$user_cc3."				
					".$user_ct3."						
					".$sql01.")
					where a.repid = '".$repid."'
					and ".$colx." is null
					and a.code_center = 'TOTAL'";
			
			//	echo $sqlrep;
			//	echo "<br><br>";
				$resultsum = mysqli_query($conn, $sqlrep);								
				
			}
			
			if ($delta_col1 == 1) {

				$calc1	= substr($delta_calc1,0,1);
				$calc2	= substr($delta_calc1,2,2);
				//echo "<br><br>";
				//echo $delta_calc1;				
				//echo "<br><br>";
				//echo $calc1;
				//echo "<br><br>";
				//echo $calc2;	
				//echo "<br><br>";
				//echo $delta_pm1;					
				
				$colx	= "a.col".$mid1;
				$colx1 	= "a.col".$calc1;
				
				$colxp	= "a.col_perc".$mid1;				

				if ($delta_pm1 == "M") { $delta_sign = "-"; $colx2 	= "a.col".$calc2; }
				if ($delta_pm1 == "P") { $delta_sign = "+"; $colx2 	= "a.col".$calc2; }
				if ($delta_pm1 == "E") { $delta_sign = "+"; $colx2 	= 0;}				

				$sqlrep = "update ccreport a set ".$colx." = IFNULL(".$colx1.",0) ".$delta_sign." IFNULL(".$colx2.",0) where a.repid = '".$repid."'";
				$resultsum = mysqli_query($conn, $sqlrep);
				
				$sqlrep = "update ccreport a set ".$colxp." = ".$colx." / ".$colx1." where a.repid = '".$repid."'";
				$resultsum = mysqli_query($conn, $sqlrep);
				
				$sqlrep = "update ccreport a set ".$colxp." =  ".$colxp."*100 where a.repid = '".$repid."'";
				$resultsum = mysqli_query($conn, $sqlrep);

				
				
			}
			
			// EARNED VALUE CHECK
			if ($expenses1 == 2) {

				$colx	= "a.col".$mid1;
				$colx1 	= "a.col".$calc1;
				
				$colxp	= "a.col_perc".$mid1;				

				$sqlrep = "update ccreport a set ".$colxp." = ".$colx." / ".$colx1." where a.repid = '".$repid."'";
				$resultsum = mysqli_query($conn, $sqlrep);
				
				$sqlrep = "update ccreport a set ".$colxp." =  ".$colxp."*100 where a.repid = '".$repid."'";
				$resultsum = mysqli_query($conn, $sqlrep);

				
				
			}			
			

		}
}
mysqli_close($conn);

$midmax = $mid1;

?>