8 replies to this topic

[newphpcoder]

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:

 $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...

---------- Post added at 06:51 AM ---------- Previous post was at 06:50 AM ----------

I tried this code: and I add Ref_No in my table

$sql = "SELECT em.EMP_ID, em.EMP_NO, s.Ref_No s.RangeCompensation, s.MonthlySalaryCredit, s.EmployeeShare

        FROM $PAYROLL.sss s, $ADODB_DB.employmet 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;

} 



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

and the result is:

the $SSS has always a value of 40.35, that's means that only the if statement was read...

What's wrong in my syntax?

Thank you

[wim DC]

You'll need double "=" or you're assigning, instead of comparing

[Orjan]

Your last code is a perfect situation for using a switch statement instead of so many if:s. Here, I only took your first three scenarios, but I hope you get the point...

switch ($Ref_No) {
case 1: 
   $SSS = (80.70 / 2);
   break;
case 2: 
   $SSS = (50.00 / 2);
   break;
case 3: 
   $SSS = (66.70 / 2);
   break;
default:
   $SSS = 0;
}

[newphpcoder]

I think my problem is in my query because the $Ref_No only equal to 1, that's why only the first statement was satisfied.


Thank you

[wim DC]

No, it's not 1. You turn it into 1 yourself.

[LEFT][COLOR=#007700][FONT=monospace]if ([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]$Ref_No [/FONT][/COLOR][COLOR=#007700][FONT=monospace]= [/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]1[/FONT][/COLOR][COLOR=#007700][FONT=monospace]){ [/FONT][/COLOR][/LEFT]

Here you say $Ref_No becomes 1.

[LEFT][COLOR=#007700][FONT=monospace]if ([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]$Ref_No [/FONT][/COLOR][COLOR=#007700][FONT=monospace]== [/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]1[/FONT][/COLOR][COLOR=#007700][FONT=monospace]){ [/FONT][/COLOR][/LEFT]

And here's how to check if $Ref_No is 1.


By the way, it's slightly weird you add +16.7 all the time, except 3 times. Make sure there's no miscalculations ;)

50,00    
66,70     16,70
83,30     16,60
100,00    16,70
116,70    16,70
133,30    16,60
150,00    16,70
166,70    16,70
183,30    16,60

[RhetoricalRuvim]

Yeah, some languages are different from others. MySQL, for example, uses the '=' operator to check whether things are equal. Use the same in C/C++/JavaScript/PHP/etc., and you'll end up assigning the value on the right to the reference on the left.

Also, the following statement will always resolve to true, and therefore would always be executed, outputting "something..." :

if ($number= 8){ 

  print "something..."; 

} 

The 'if' statement tests to see if the value (actually code) in the parentheses resolves to true or not, but first the code within the parentheses needs to be executed. PHP would execute the code, assigning 8 to $number, and since the return from the code (which is $number, or 8) is not 0, nor false, nor "", nor undefined, etc., it would resolve into true; for that reason, the block of code immediately after the conditional statement's parentheses will most inevitably be executed.

[newphpcoder]

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:

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...

Edited by newphpcoder, 30 November 2011 - 06:12 PM.

[newphpcoder]

i tried this query:

$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

[newphpcoder]

I tried this 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' GROUP BY s.Ref_No"; 

 

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


if ($rsSSS === false) die("failed");

    while (!$rsSSS->EOF) {

       for ($i=0, $max=$rsSSS->FieldCount(); $i < $max; $i++)

       

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

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

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

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

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

       if ($Ref_No == 1 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range ) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 2 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 3 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 4 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 5 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 6 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 7 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 8 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 9 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 10 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 11 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 12 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 13 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        elseif ($Ref_No == 14 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {

            $SSS = $Employee_Share;

        }

        else {

            $SSS = 0;

} 

       $rsSSS->MoveNext();

}

but the output is 0,

I attach my database for reference

Attached Files

•  payroll 20111201 1516.zip   1.1K   0 downloads