Checking in mysql database the data before compute the deduction

newphpcoder · #1 (**permalink**) 11-22-11, 22:54

Good day!

I have table in my database which I could check the range of compensatin,monthly salary credit, monthly contribution.

Now i am in the process of analyzing how can I get the monthly contribution of employee it is depend on their basic pay.

I have this code for getting the basic pay of employee:

PHP Code:


			
 $sql = "SELECT em.EMP_NO, w.RATE, e.Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em,  $PAYROLL.earnings e WHERE w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' AND e.EMP_NO = em.EMP_NO";

  $RsEarnings = $conn2->Execute($sql);



  $Rate      = trim($RsEarnings->fields['RATE']);

  $Hours      = trim($RsEarnings->fields['Hours']);



  $Hours = substr($Hours, 0, 5);

  $Hours = str_replace(':', '.', $Hours);

  

 $Amount = $_POST["Amount"];

 $Amount = mysql_real_escape_string($Amount); 

 

 $Amount = round(($Hours/8)* $Rate, 2); 





  $smarty->assign('Rate', $Rate);

  $smarty->assign('Hours', $Hours);

  $smarty->assign('Amount', $Amount);

the Amount is the Basic Pay and it is only display the basic pay not yet save in the database.

and I have table deduction that has fields:
RangeCompensation varchar
MonthlySalaryCredit varchar
MonthlyContribution varchar

Example data:
1000 - 1249.99 1000 33.30
1250 - 1749.99 1500 50.00
1750 - 2249.99 2000 66.70 and so on.....

The deduction is base on the Amount, what should be my query or condition to check if what would be his monthlydeduction.

For example Amount or Basic pay is 5422.5

Where should I base is it in MonthlySalaryCredit? or should separate the rangecompensation into two then i will based on that? to get his monthly deduction

Thank you so much...

it-iss.com · #2 (**permalink**) 11-23-11, 02:41

Firstly, any numeric values should be stored as numeric to make it easier to determine the level of compensation. Secondly I would change your compensation table structure as follows:

RangeCompensationMin decimal(10,2)
RangeCompensationMax decimal(10,2)
MonthlySalaryCredit decimal(10,2)
MonthlyContribution decimal(10,2)

Then with the Amount search for an entry that it BETWEEN RangeCompensationMin AND RangeCompensationMax to get the row that you are looking for. Also the hours and previous entries also appear to be stored as strings. Perhaps if you show us the structure of both the tables we might be able to help you more. A single query should be able to calculate this if the structure of the tables is correct.

newphpcoder · #3 (**permalink**) 11-23-11, 02:48

the Amount is not yet been save...it only displayed...uhmmm what should be the datatype of Amount? decimal?

Okay... I will try your suggestion...

Thank you

newphpcoder · #4 (**permalink**) 11-23-11, 03:07

Now I change my table for deduction...

Can give me a sample query to check if the amount is between the range?the amount is not yet save..How can I check it?

How about if I will check in MonthlySalaryCredit???

newphpcoder · #5 (**permalink**) 11-23-11, 03:10

Now I have this code...I only base in Ref_no to check if what EmployeeShare would deduct to him:

PHP Code:


			
<?php

  $sql = "SELECT em.EMP_NO, w.RATE, e.Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em,  $PAYROLL.earnings e WHERE w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' AND e.EMP_NO = em.EMP_NO";

  $RsEarnings = $conn2->Execute($sql);



  $Rate      = trim($RsEarnings->fields['RATE']);

  $Hours      = trim($RsEarnings->fields['Hours']);



  $Hours = substr($Hours, 0, 5);

  $Hours = str_replace(':', '.', $Hours);

  

 $Amount = $_POST["Amount"];

 $Amount = mysql_real_escape_string($Amount); 

 



$sql = "SELECT s.Ref_No, s.RangeCompensationMin, s.RangeCompensationMax, s.MonthlySalaryCredit, s.EmployeeShare

        FROM $PAYROLL.sss_ded s, $ADODB_DB.employment em 

        WHERE em.EMP_ID = '$currentEmpID'";

$rsSSS = $conn2->Execute($sql);



 $Ref_No = $rsSSS->fields["Ref_No"];  

if ($Ref_No == 1) {

     $SSS = (80.70 / 2);

}

elseif ($Ref_No == 2) {

     $SSS = (50.00 / 2);

}

elseif ($Ref_No == 3) {

     $SSS = (66.70 / 2);

}

elseif ($Ref_No == 4) {

     $SSS = (83.30 / 2);

}

elseif ($Ref_No == 5) {

     $SSS = (100.00 / 2);

}

elseif ($Ref_No == 6) {

     $SSS = (116.70 / 2);

}

elseif ($Ref_No == 7) {

     $SSS = (133.30 / 2);

}

elseif ($Ref_No == 8) {

     $SSS = (150.00 / 2);

}

elseif ($Ref_No == 9) {

     $SSS = (166.70 / 2);

}

elseif ($Ref_No == 10) {

     $SSS = (183.30 / 2);

}

else {

    $SSS = 0;

}

?>

But the problem here is only Ref_No = 1 only...so if condition only satisfied...

newphpcoder · #6 (**permalink**) 11-30-11, 21:16

Now I resolved my problem by manually coding of formula in php not from in database. but i need to get the range from the database.

I have this field in my table to check the range and the amount of deduction.

Ref_No
From_Range
To_Range
Salary_Credit
Employee_Share

this is my sample code from php:

PHP Code:


			
if ($TotEarn >= 1000 && $TotEarn <= 1249.99 ) { //, $TotEarn is not yet save in database it is internally computed in php code,  From_Range = 1000 To_Range = 1249.99  

    $SSS = (33.30); // Employee_Share = 33.30

}

elseif ($TotEarn >= 1250 && $TotEarn <= 1749.99) {

    $SSS = (50.00);

}

else

{

$SSS = 0;

}

I don't know how can I get the data from database to check the range and get the employee share based on their total eranings.

Thank you so much...

newphpcoder · #7 (**permalink**) 11-30-11, 22:37

i tried this query:

PHP Code:


			
$sql = "SELECT em.EMP_ID, s.Ref_No, s.From_Range, s.To_Range, s.Salary_Credit, s.Employee_Share 

        FROM $PAYROLL.sss s , $ADODB_DB.employment em  

        WHERE em.EMP_ID = '$currentEmpID'"; 

$rsSSS = $conn2->Execute($sql); 



$Ref_No = $rsSSS->fields['Ref_No']; 

$EMP_ID = $rsSSS->fields['EMP_ID']; 



var_dump($Ref_No);

var_dump($EMP_ID);

but still the Ref_No always has a value of 1 and I don't know waht can i put in my condition statement to check if the totearn is between the range then the employee_share is equal to designated value.

Thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech