Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Checking in mysql database the data before compute the deduction

    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($Hours05);
      
    $Hours str_replace(':''.'$Hours);
      
     
    $Amount $_POST["Amount"];
     
    $Amount mysql_real_escape_string($Amount); 
     
     
    $Amount round(($Hours/8)* $Rate2); 


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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    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

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    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???

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    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($Hours05);
      
    $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...


  6. #6
    Join Date
    Dec 2010
    Posts
    136
    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...

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •