format("Y-m-d");
//https://www.youtube.com/watch?v=gNH6NNyhJLk
//https://www.youtube.com/watch?v=EYYZFRRdR6A PDF with MySQL database
$count = 0;
require ('./fpdf.php');
class PDF extends FPDF{
function Header(){
$this->Image('../images/ahg_logo.png',10,5, 25,10, 'PNG','wwww.autohubgroup.com');
}
/* function Footer(){
$this->SetY(-25);
$this->Image('../images/ahg_logo.png');
} */
}
$myUID = $_GET['s'];
$unitID = $_GET['e'];
$sourceID = $_GET['s'];
$sem = $_GET['sm'];
$nme = $_GET['n'];
$rptYear = $_GET['ye'];
if($sem == 1){
$semester = "1st Semester";
}else if($sem == 2){
$semester = "2nd Semester";
}else{
$semester = "1st & 2nd Semester";
}
$unitNamen = $nme . " - Scorecard" ." (". $semester." of ". $rptYear .")";
include_once("../../../cfg/conn.php");
include ("../functions/users.php");
$roleID=0;
if(isset($_GET['s'])){
$empID = $_GET['e'];
$sourceID = $_GET['s'];
$sem = $_GET['sm'];
$nme = $_GET['n'];
$rptYear = $_GET['ye'];
//START OF CHECKPOINT===============================================================================
//START OF CHECKPOINT===============================================================================
//==========CHECK IF USER EXIST IN THE RECORD AND MATCH ALL INFORMATION IN THE LICNK =========
$chkUser = "SELECT * FROM `pms_employee_data` WHERE `id`='$sourceID' AND `deleted` = 0";
$qCU=mysqli_query($conn,$chkUser);
$countUser = mysqli_num_rows($qCU);
if($countUser == 0){
echo " 
![]()
";
echo " Access Denied
";
echo "
Sorry, you are not authorized to access this page.
Please contact your system support for assistance.
Thank you!
";
exit();
}else{
while($uow=mysqli_fetch_array($qCU)){
$uowaccStat = $uow['account_status'];
$uowdeleted = $uow['deleted'];
}
}
if($uowaccStat == 0){
echo " ![]()
";
echo " Access Denied
";
echo "
Sorry, you are not authorized to access this page.
Please contact your system support for assistance.
Thank you!
";
exit();
}
if($uowdeleted == 1){
echo " ![]()
";
echo " Access Denied
";
echo "
Sorry, you are not authorized to access this page.
Please contact your system support for assistance.
Thank you!
";
exit();
}
//==========CHECK IF EMPLOYEE EXIST IN RECORD AND MATCH ALL INFORMATION IN THE LICNK =========
$chkPoint = "SELECT `last_name`, `first_name`,`account_status`,`deleted` FROM `pms_employee_data` WHERE `id`='$empID'";
$qChk=mysqli_query($conn,$chkPoint);
$ckhRow = mysqli_num_rows($qChk);
If($ckhRow > 0){
while($row=mysqli_fetch_array($qChk)){
$lastName = $row['last_name'];
$firstName = $row['first_name'];
$accStat = $row['account_status'];
$deleted = $row['deleted'];
}
}else{
echo " ![]()
";
echo " Access Denied
";
echo "
Sorry, you are not authorized to access this page.
Please contact your system support for assistance.
Thank you!
";
exit();
}
//END OF CHECKPOINT===============================================================================
//END OF CHECKPOINT===============================================================================
}
$getName = "SELECT * FROM pms_employee_data WHERE id = $myUID LIMIT 1";
$getMyName = mysqli_query($conn,$getName);
while($nRow = mysqli_fetch_array($getMyName)){
$fname=$nRow['first_name'];
$lname=$nRow['last_name'];
$compID=$nRow['comp_id'];
$myFName = $fname ." ". $lname;
}
$compName = getCompanyNameSub($compID)[0];
//CREATE NEW TEMPORARY TABLE TO STORE DATA FROM MAR_EMP_DATA FOR LATER PROCESSING OF SCORECARD BY DEPT.
//STEPS:
//1. GET ALL DATA FROM MAR_EMP_DATA USING DEPT. ID
//2. GET POSITION KRA/KPI AND STORE TO NEW TABLE
//3. GET SECTION KRA/KPI AND STORE TO NEW TABLE
//4. GET DEPT. KRA/KPI AND STSORE TO NEW TABLE
//5. QUERY THE NEW TABLE TO EXTRACT THE REPORT GATHERED FROM STEP 1-4
//DROP existing Taable
$drop ="DROP TABLE IF EXISTS `temp_scorecard_dept`";
if ($conn->query($drop) === TRUE) {
//echo "New record created successfully";
} else {
//echo "Error: " . $sql . "
" . $conn->error;
}
//CREATE NEW EMPTY TABLE
$sql = "CREATE TABLE IF NOT EXISTS `temp_scorecard_dept`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`dept_id` int(11) NOT NULL,
`section_id` int(11) NOT NULL,
`position_id` int(11) NOT NULL,
`mar_id` int(11) NOT NULL,
`kra_id_dept` int(11) NOT NULL,
`kra_id_section` int(11) NOT NULL,
`kra_id_position` int(11) NOT NULL,
`kpi_id_dept` int(11) NOT NULL,
`kpi_id_section` int(11) NOT NULL,
`kpi_id_position` int(11) NOT NULL,
`kra_weight` int(11) NOT NULL,
`kpi_weight` int(11) NOT NULL,
`kra_name` varchar(150) NOT NULL,
`kpi_name` varchar(150) NOT NULL,
`kra_target` varchar(7) NOT NULL,
`kpi_target` varchar(7) NOT NULL,
`kpi_actual_score` varchar(7) NOT NULL,
`kpi_achievement_rate` varchar(7) NOT NULL,
`kra_averange_score` varchar(7) NOT NULL,
`kra_weighted_points` varchar(7) NOT NULL,
PRIMARY KEY (`id`))";
if ($conn->query($sql) === TRUE) {
//echo "New record created successfully";
} else {
//echo "Error: " . $sql . "
" . $conn->error;
}
//QUERY MAR_EMP_DATA TO FETCH INFO
if($sem == 0){
$emp = "SELECT * FROM `mar_emp_data` WHERE `department_id`='$unitID' AND `deleted` =0 AND `mar_year_data`='$rptYear' ORDER BY `kra_id` ASC";
}else{
$emp = "SELECT * FROM `mar_emp_data` WHERE `department_id`='$unitID' AND `deleted` =0 AND `mar_year_data`='$rptYear' AND `mar_semester_data` = '$sem' ORDER BY `kra_id` ASC";
}
$qEmp = mysqli_query($conn,$emp);
while($row = mysqli_fetch_array($qEmp)){
$marID = $row['id'];
$empID = $row['emp_rec_id'];
$marPCPID = $row['pcp_id'];
$marEmployeeKRAid = $row['kra_id']; // EMPLOYEE KRA
$marEmployeeKPIid = $row['kpi_id']; // EMPLOYEE KRA
$marWeight = $row['mar_weight'];
$marTarget = $row['mar_target'];
$marActual=$row['mar_actual'];
$marScore = $row['my_score'];
$marNR = $row['mar_numerical_rating'];
$positionID = $row['position'];
$posKRA = "SELECT `position_kra_id`,`top_kpi_id`,`id` FROM `kpi_employee` WHERE `id`='$marEmployeeKPIid' LIMIT 1";
$qPosKRA = mysqli_query($conn,$posKRA);
while($pka = mysqli_fetch_array($qPosKRA)){
$employeeKRA = $pka['kra_id']; // position kra id 145
$positionKPIid = $pka['top_kpi_id']; // position kpi id 1
}
$posKRA = "SELECT `top_kpi_id`,`kra_id`,`id` FROM `kpi_position` WHERE `id`='$positionKPIid' LIMIT 1";
$qPosKRA = mysqli_query($conn,$posKRA);
while($pks = mysqli_fetch_array($qPosKRA)){
$positionKRAid = $pks['kra_id']; // position kra id 3
$sectionKPIid = $pks['top_kpi_id'];// position kpi id 1
$positionID= $pks['id'];
}
$secKRA = "SELECT `kra_id`,`top_kpi_id`,`id` FROM `kpi_unit` WHERE `id`='$sectionKPIid' LIMIT 1";
$qSec = mysqli_query($conn,$secKRA);
while($sec = mysqli_fetch_array($qSec)){
$sectionKRAid = $sec['kra_id']; //1
$deptKPIid = $sec['top_kpi_id']; //1
$sectionID = $sec['id'];
}
$secKPI = "SELECT `kra_id` FROM `kpi_department` WHERE `id`='$deptKPIid' LIMIT 1";
$qPos = mysqli_query($conn,$secKPI);
while($dpt = mysqli_fetch_array($qPos)){
$deptKRAid = $dpt['kra_id']; //1
$divisionKPIid = $dpt['top_kpi_id']; //1
}
$kraName = getDeptKraName($deptKRAid)[0];
$kpiName = getDeptKpiData($deptKPIid)[0];
$kpiTarget = getDeptKpiData($deptKPIid)[1];
$DeptKpiWeight = getDeptKpiData($deptKPIid)[2];
$DeptKraWeight = getDeptKraName($deptKRAid)[1];
$sqlInsert = "INSERT INTO temp_scorecard_dept (
`id`,
`dept_id`,
`section_id`,
`position_id`,
`mar_id`,
`kra_id_dept`,
`kra_id_section`,
`kra_id_position`,
`kpi_id_dept`,
`kpi_id_section`,
`kpi_id_position`,
`kra_weight`,
`kpi_weight`,
`kra_name`,
`kpi_name`,
`kpi_target`,
`kpi_actual_score`,
`kpi_achievement_rate`
)VALUES(
'',
'$unitID',
'$sectionID',
'$positionID',
'$marID',
'$deptKRAid',
'$sectionKRAid',
'$positionKRAid',
'$deptKPIid',
'$sectionKPIid',
'$positionKPIid',
'$DeptKraWeight',
'$DeptKpiWeight',
'$kraName',
'$kpiName',
'$kpiTarget',
'$marActual',
'$marScore')";
if ($conn->query($sqlInsert) === TRUE) {
//echo "New record created successfully";
} else {
//echo "Error: " . $sql . "
" . $conn->error;
}
}
//START OF QUERY FROM NEWLY CREATED TABLE
//$sql ="SELECT * FROM `kra_position_tbl` WHERE `pms_unit_id`='$unitID' AND `deleted`=0 ORDER BY `kra_name` ASC";
$sqlList ="SELECT * FROM ops_data_department WHERE `pms_record_id` = '$unitID' ORDER BY `ops_kra` ASC";
//set margins
$pdf = new PDF();
$pdf->SetMargins(10,20,10);
$pdf->AddPage('L'); //L = Land Scape, P = Portrait
//set column size in array format
$width_cell = array(91,15,85,15,15,18,18,18);
$height_cell= 7;
//set Image
//$pdf->Image('../images/ahg_logo.png',10,5, 25,10, 'PNG','wwww.autohubgroup.com');
//background color
$pdf->SetFillColor(255,255,255); //use for report title bgcolor
$pdf->SetFont('Arial','B',10 );
$pdf->MultiCell(0,5,$compName,0,1,0,"L",true); //1,6,"",0,1,0,'L',$fill
$pdf->SetFont('Arial','',10 );
$pdf->MultiCell(0,5,$unitNamen,0,1,0,"L",true);
$pdf->MultiCell(0,5,"",0,1,0,"L",true);
$pdf->SetFont('Arial','',8);
$pdf->SetFillColor(0,0,0,90); //use for header bgcolor
$pdf->SetTextColor(255,255,255,90);
$pdf->SetLineWidth(0);
$pdf->SetDrawColor(0,0,25);
//header start
$pdf->Cell($width_cell[0],$height_cell,'KRA NAME',1,0,'L',true);
$pdf->Cell($width_cell[1],$height_cell,'WEIGHT',1,0,'L',true);
$pdf->Cell($width_cell[2],$height_cell,'KPI NAME',1,0,'L',true);
$pdf->Cell($width_cell[3],$height_cell,'TARGET',1,0,'L',true);
$pdf->Cell($width_cell[4],$height_cell,'ACTUAL',1,0,'L',true);
$pdf->Cell($width_cell[5],$height_cell,'ACHV. RATE',1,0,'L',true);
$pdf->Cell($width_cell[6],$height_cell,'AVG. SCORE',1,0,'L',true);
$pdf->Cell($width_cell[7],$height_cell,'W. POINTS',1,0,'L',true);
$pdf->SetFillColor(255,255,255,0); //use for header bgcolor
$pdf->SetTextColor(0,0,0,90);
$fill=false;
//$pdf->Cell(400,200,'Hello Arnel');
$getData = mysqli_query($conn,$sqlList );
$statRow = mysqli_num_rows($getData);
$kraTitle = '';
$currentKRAid =0;
$achvRateTotal =0;
$wPoint=0;
$wPointTotal=0;
$kraWeightPercentageTotal=0;
$MykpiTarget_ex=0;
$achvRateTotalByKRADivisor=0;
$achvRateTotalByKRA=0;
$totalWP = 0;
$totalWeight = 0;
$count =0;
$kraWeightTotal=0;
while($drow = mysqli_fetch_array($getData)){
$kpiRow = 0;
$count = $count + 1;
$QdeptKRAid = $drow['ops_kra'];
$QdepKPIAid = $drow['ops_kpi'];
$deptKRAName = getDeptKraName($QdeptKRAid)[0];
$deptKRAWeight = getDeptKraName($QdeptKRAid)[1];
$deptKPIName = getDeptKpiData($QdepKPIAid)[0];
$deptKPITaget = getDeptKpiData($QdepKPIAid)[1];
if (preg_match("/%/",$deptKPITaget)) {
$percent ="%";
$pcpKPITargetEx = explode("%",$deptKPITaget);
$pcpKPITargetPercent = $pcpKPITargetEx[0];
}else{
$percent ="";
$pcpKPITargetPercent = $deptKPITaget;
}
if (preg_match("/%/",$deptKRAWeight)) {
$kraWeightpercent = explode("%",$deptKRAWeight);
$kraWeight = $kraWeightpercent[0] /100;
$kraWeightT = $kraWeightpercent[0];
}else{
$kraWeight = getDeptKraName($QdeptKRAid)[1] / 100;
$kraWeightT = $kraWeightpercent[0];
}
$pdf->MultiCell(0,$height_cell,"",0,1,0,'L',false); //setup row padding
if($kraTitle == $QdeptKRAid){
$pdf->Cell($width_cell[0],$height_cell,"",1,0,'L',$fill);
$pdf->Cell($width_cell[1],$height_cell,"",1,0,'L',$fill);
}else{
$pdf->Cell($width_cell[0],$height_cell,$deptKRAName,1,0,'L',$fill);
$pdf->Cell($width_cell[1],$height_cell,$deptKRAWeight,1,0,'C',$fill);
$kraWeightTotal = $kraWeightTotal + $kraWeightT;
}
$pdf->Cell($width_cell[2],$height_cell,$deptKPIName,1,0,'L',$fill);
$pdf->Cell($width_cell[3],$height_cell,$deptKPITaget,1,0,'C',$fill);
//GET SCORE FROM temp_scorecard_dept TABLE
$score = "SELECT AVG(`kpi_actual_score`) AS 'kpiScore' FROM `temp_scorecard_dept` WHERE `kra_id_dept`='$QdeptKRAid' AND `kpi_id_dept`='$QdepKPIAid' LIMIT 1";
$qScore = mysqli_query($conn,$score);
while($scr = mysqli_fetch_array($qScore)){
$myScorePrint = number_format(($scr['kpiScore']),2,'.','');
$myScore = $scr['kpiScore'];
}
$scoreCNT = "SELECT * FROM `ops_data_department` WHERE `ops_kra`='$QdeptKRAid'";
$qKpiC = mysqli_query($conn,$scoreCNT);
while($nt = mysqli_fetch_array($qKpiC)){
$kpiRow = $kpiRow + 1;
}
$pdf->Cell($width_cell[4],$height_cell,$myScorePrint."".$percent,1,0,'C',$fill);
$achvRate = (($myScore/$pcpKPITargetPercent) * 100);
$achvRatePrint= number_format((($myScore/$pcpKPITargetPercent)* 100),2,'.','');
$achvRateTotal = $achvRateTotal + $achvRate;
$pdf->Cell($width_cell[5],$height_cell,$achvRatePrint."".$percent,1,0,'C',$fill);
if($count == $kpiRow){
$achvRateTotalFinal = number_format(($achvRateTotal / $kpiRow),2,'.','');
$pdf->Cell($width_cell[6],$height_cell, $achvRateTotalFinal,1,0,'C',$fill); //achvRateTotalFinal
$wPoint = number_format(($kraWeight * $achvRateTotalFinal),3,'.','');
$pdf->Cell($width_cell[7],$height_cell,$wPoint,1,0,'C',$fill);
$wPointTotal = number_format(($wPointTotal + $wPoint),3,'.','');
$count =0;
$kpiRow = 0;
$achvRateTotal=0;
}else{
$pdf->Cell($width_cell[6],$height_cell,"",1,0,'C',$fill);
$pdf->Cell($width_cell[7],$height_cell,"",1,0,'C',$fill);
}
$fill =!$fill; // use code to put alternate color between rows
$kraTitle = $QdeptKRAid;
}
$pdf->SetFillColor(0,0,0,90); //use for header bgcolor
$pdf->SetTextColor(255,255,255,10);
$pdf->MultiCell(1,$height_cell,"",0,1,0,'L',false); //setup row padding
$pdf->Cell($width_cell[0],$height_cell,'TOTAL',1,0,'L',true);
$pdf->Cell($width_cell[1],$height_cell,$kraWeightTotal."%",1,0,'C',true);
$pdf->Cell($width_cell[2],$height_cell,'',1,0,'L',true);
$pdf->Cell($width_cell[3],$height_cell,'',1,0,'L',true);
$pdf->Cell($width_cell[4],$height_cell,'',1,0,'L',true);
$pdf->Cell($width_cell[5],$height_cell,'',1,0,'L',true);
$pdf->Cell($width_cell[6],$height_cell,'',1,0,'L',true);
$pdf->Cell($width_cell[7],$height_cell,$wPointTotal,1,0,'C',true);
$pdf->SetFillColor(255,255,255,10); //use for header bgcolor
$pdf->SetTextColor(0,0,0,90);
$pdf->MultiCell(1,25,"",0,'L',false); //setup row padding
$pdf->Cell(100,0,"_____________________________",0,1,'L',false);
$pdf->Cell(100,8,$myFName .' / '. $prfDateX ,0,0,'L',false);
$pdf->Output('my_file.php','I');
?>