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;
//GET OpsPlan Header ID
$ops = "SELECT `id` FROM `ops_department_level` WHERE `coverage_year`='$rptYear' AND `deleted`=0 AND `record_id`='$unitID'";
$qOps = mysqli_query($conn,$ops);
$opsID=0;
while($op =mysqli_fetch_array($qOps)){
$opsID = $op['id'];
}
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_division`";
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_division`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`division_id` int(11) NOT NULL,
`department_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_div` int(11) NOT NULL,
`kpi_id_div` 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 `division_id`='$unitID' AND `deleted` =0 AND `mar_year_data`='$rptYear' ORDER BY `kra_id` ASC";
}else{
$emp = "SELECT * FROM `mar_emp_data` WHERE `division_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'];
$marPositionKRAid = $row['kra_id'];
$marPositionKPIid = $row['kpi_id'];
$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 `top_kpi_id`,`kra_id`,`id` FROM `kpi_position` WHERE `id`='$marPositionKPIid' LIMIT 1";
$qPosKRA = mysqli_query($conn,$posKRA);
while($pka = mysqli_fetch_array($qPosKRA)){
$unitKPIid = $pka['top_kpi_id']; // position kra id
$positionID = $pka['id'];
}
$secKRA = "SELECT `kra_id`,`top_kpi_id`,`id` FROM `kpi_unit` WHERE `id`='$unitKPIid' LIMIT 1";
$qSec = mysqli_query($conn,$secKRA);
while($sec = mysqli_fetch_array($qSec)){
$unitKRAid = $sec['kra_id'];
$deptKPIid = $sec['top_kpi_id'];
$sectionID = $sec['id'];
}
$secKPI = "SELECT `top_kpi_id`,`kra_id`,`id` FROM `kpi_department` WHERE `id`='$deptKPIid' LIMIT 1";
$qPos = mysqli_query($conn,$secKPI);
while($dpt = mysqli_fetch_array($qPos)){
$deptKRAid = $dpt['kra_id'];
$divKPIid = $dpt['top_kpi_id'];
$deptID = $dpt['id'];
}
$divKPI = "SELECT `top_kpi_id`,`kra_id`,`id` FROM `kpi_childdivision` WHERE `id`='$divKPIid' LIMIT 1";
$qPos = mysqli_query($conn,$divKPI);
while($div = mysqli_fetch_array($qPos)){
$divKRAid = $div['kra_id'];
$divMainKPIid = $div['top_kpi_id'];
$divisionID = $div['id'];
}
$kraName = getDivisionKraName($divKRAid)[0];
$kpiName = getDivisionKpiName($divMainKPIid)[0];
$kpiTarget = getDivisionKpiName($divMainKPIid)[1];
$DeptKpiWeight = getDivisionKpiName($divMainKPIid)[2];
$DeptKraWeight = getDivisionKraName($divKRAid)[1];
$sqlInsert = "INSERT INTO temp_scorecard_division (
`id`,
`division_id`,
`department_id`,
`section_id`,
`position_id`,
`mar_id`,
`kra_id_div`,
`kpi_id_div`,
`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',
'$deptID',
'$sectionID',
'$positionID',
'$marID',
'$divKRAid',
'$divMainKPIid',
'$deptKRAid',
'$unitKRAid',
'$marPositionKRAid',
'$deptKPIid',
'$unitKPIid',
'$marPositionKPIid',
'$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 temp_scorecard_division WHERE 1 GROUP BY `kra_id_div` ORDER BY kra_id_div 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,0,true);
$pdf->SetFont('Arial','',10 );
$pdf->MultiCell(0,5,$unitNamen,0,0,true);
$pdf->MultiCell(0,5,"",0,0,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 = '';
$achvRateTotal =0;
$wPoint=0;
$wPointTotal=0;
$kraWeightPercentageTotal=0;
$MykpiTarget_ex=0;
$achvRateTotalByKRADivisor=0;
$achvRateTotalByKRA=0;
$totalWP = 0;
$totalWeight = 0;
while($drow = mysqli_fetch_array($getData)){
$count = 0;
$kraID= $drow['kra_id_div'];
$kpiID= $drow['kpi_id_div'];
$MykpiTarget = $drow['kpi_target'];
$myTargetX = explode('%',$MykpiTarget);
$myClTarget =$myTargetX[0];
$perKRAWeight = explode("%",$drow['kra_weight']);
$totalWeight = $totalWeight + $perKRAWeight[0];
$pKra = $perKRAWeight[0] / 100;
//$$MykpiTarget_ex = explode('%', $drow['kpi_target']);
//$myKpiClrTarget = $MykpiTarget_ex[0];s
$kpiCount = "SELECT COUNT(1) FROM `kpi_childdivision` WHERE `kra_id`='$kraID' AND `deleted`=0";
$qCount = mysqli_query($conn,$kpiCount); //ERROR
$kpiRow = mysqli_num_rows($qCount);
$count = $count + 1;
$marActual = "SELECT AVG(`kpi_actual_score`) AS 'kpiAVGScore' FROM `temp_scorecard_division` WHERE `kra_id_div`='$kraID' AND `kpi_id_div`='$kpiID' LIMIT 1";
$qActual = mysqli_query($conn,$marActual);
while($ac=mysqli_fetch_array($qActual)){
$myAvgScore=($ac['kpiAVGScore']);
$myAvgScorePrint=number_format(($ac['kpiAVGScore']),2,'.','');
}
$achvRate = number_format((($myClTarget / 100) * $myAvgScore),2,'.','');
$achvRateEX = ($myClTarget / 100) * $myAvgScore;
$pdf->MultiCell(0,$height_cell,"",0,'P',false); //setup row padding
if($kraTitle == $kraID){
$pdf->Cell($width_cell[0],$height_cell,"",1,0,'L',$fill);
$kraWeightPrint='';
}else{
$pdf->Cell($width_cell[0],$height_cell,$drow['kra_name'],1,0,'L',$fill);
$kraWeightPrint = $drow['kra_weight'];
}
$achvRateTotalByKRA = $achvRateTotalByKRA + $achvRateEX;
$achvRateTotalByKRADivisor = $achvRateTotalByKRA;
$pdf->Cell($width_cell[1],$height_cell,$drow['kra_weight'],1,0,'C',$fill);
$pdf->Cell($width_cell[2],$height_cell,$drow['kpi_name'],1,0,'L',$fill);
$pdf->Cell($width_cell[3],$height_cell,$MykpiTarget,1,0,'C',$fill);
$pdf->Cell($width_cell[4],$height_cell,$myAvgScorePrint."%",1,0,'C',$fill);
$pdf->Cell($width_cell[5],$height_cell,$achvRate."%",1,0,'C',$fill);
if($count == $kpiRow){
$pdf->Cell($width_cell[6],$height_cell,$achvRateTotalByKRA."%",1,0,'C',$fill);
$kraWP = number_format(($pKra * $achvRateTotalByKRADivisor),2,'.','') ;
$kraWPRate = $pKra * $achvRateTotalByKRADivisor;
$totalWP = $totalWP + $kraWPRate;
$pdf->Cell($width_cell[7],$height_cell,$kraWP ."%",1,0,'C',$fill);
$achvRateTotalByKRA=0;
$achvRateTotalByKRADivisor=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=$kraID;
}
$pdf->SetFillColor(0,0,0,90); //use for header bgcolor
$pdf->SetTextColor(255,255,255,10);
$pdf->MultiCell(1,$height_cell,"",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,$totalWeight."%",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,$totalWP."%",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');
?>