Jump to content

procedure not working

prabhu3705

By prabhu3705
in MySQL Help

 Share

Recommended Posts

prabhu3705 Newbie

prabhu3705

Posted
Posted 
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `emp_reg`.`calc`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
DECLARE earres[] INT UNSIGNED DEFAULT 0;
    DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT empinfo.emp_code,empinfo.basic,empinfo.designation_id,empinfo.client_code,empinfo.branch_code,

attendancemaster.earning_duty,attendancemaster.weekly_off,attendancemaster.total_work_days,
attendancemaster.other_duty,attendancemaster.incentive_duty,attendancemaster.month_days,
attendancemaster.month,attendancemaster.year,

empearnings.e_amount1,empearnings.e_amount4,empearnings.e_amount9,empearnings.e_amount10,
empearnings.e_amount11,empearnings.e_amount16,empearnings.e_amount17,empearnings.d_amount1,
empearnings.d_amount2,empearnings.d_amount3,empearnings.d_amount4,empearnings.d_amount5,
empearnings.d_amount6,empearnings.d_amount7,

netsalary.salary_type,netsalary.gross_earning,netsalary.total_deduction,netsalary.net_salary,
netsalary.payment_mode,netsalary.transaction_id,netsalary.transaction_date,netsalary.pay_date,

clientallowancemaster.e_head10,clientallowancemaster.percent_value10,clientallowancemaster.fixed_value10,
clientallowancemaster.e_head11,clientallowancemaster.percent_value11,clientallowancemaster.fixed_value11,
clientallowancemaster.e_head13,clientallowancemaster.percent_value13,clientallowancemaster.fixed_value13,
clientallowancemaster.e_head15,clientallowancemaster.percent_value15,clientallowancemaster.fixed_Value15,
clientallowancemaster.e_head16,clientallowancemaster.percent_value16,clientallowancemaster.fixed_Value16,
clientallowancemaster.e_head3,clientallowancemaster.percent_value3,clientallowancemaster.fixed_Value3,
clientallowancemaster.e_head5,clientallowancemaster.percent_value5,clientallowancemaster.fixed_Value5,
clientallowancemaster.e_head6,clientallowancemaster.percent_value6,clientallowancemaster.fixed_Value6, 

empearnings.e_amount3,empearnings.e_amount5,empearnings.e_amount6,

clientmaster.state_code,clientmaster.esic_deduction,clientmaster.pt_deduction,

actualsalary.basic, actualsalary.hra,actualsalary.medical_allowance,actualsalary.conveyance,
actualsalary.incentive,actualsalary.uniform_allow,actualsalary.washing_allow,actualsalary.bonus,
actualsalary.leave_wage,actualsalary.xman_allow,actualsalary.other_allow,actualsalary.special_allow,
actualsalary.gross_salary,actualsalary.epf,actualsalary.pt,actualsalary.esic,actualsalary.mess_ration,
actualsalary.coupon,actualsalary.uniform,actualsalary.advance,actualsalary.total_deduction,
actualsalary.net_salary

FROM empinfo,attendancemaster,empearnings,netsalary,clientallowancemaster,clientmaster,actualsalary

WHERE empinfo.emp_code=attendancemaster.emp_code && empinfo.emp_code=empearnings.emp_code &&
empinfo.emp_code=netsalary.emp_code && empinfo.client_code=clientallowancemaster.client_code && 
empinfo.designation_id=clientallowancemaster.designation_id && 
empinfo.client_code=clientallowancemaster.client_code && 
clientallowancemaster.client_code=clientmaster.client_code &&
actualsalary.emp_code=empinfo.emp_code;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO earres[];
    UNTIL done  = 1 END REPEAT;
    CLOSE cur;
while ($a_earres=($ear_res))
//Earning Head

$v_amt1=($a_earres[1] * $a_earres[7])/$a_earres[10];//calculates basic (ear1) AS per basic * no. of WORK days formula: basic *
$v_amt2=$a_earres[13] * 0.0833;//calculates bonus (ear17) WHERE .0833=8.33%
$v_amt3=(($a_earres[13] * 15)/$a_earres[10])/12;//calculates LEAVE wage (ear9) FOR 15 days per MONTH WHERE 					//15 IS days,$a_earres[10] IS MONTH days & 12 IS no. of months IN a YEAR

$v_amt4=($a_earres[1] * $a_earres[8])/$a_earres[10];//calculates incentives(ear4)
$v_amt5=$a_earres[17];	//calculates special allowance (ear11) AS per basic earning * percent_value11
$v_amt6=$a_earres[18]; //calculates uniform allowance (ear16) AS per basic earning * percent_value16
$v_amt7=$a_earres[16]; //calculates X-man allowance(ear10) AS per basic earning * percent_value10
$v_amt20=$a_earres[59]; //calculates HRA AS per basic earning * percent_value3
$v_amt21=$a_earres[60]; //calculates Conveyance AS per basic earning * percent_value5
$v_amt22=$a_earres[61]; //calculates Medical Allowance AS per basic earning * percent_value6

//Deduction Head
$v_amt8=($a_earres[1] * $a_earres[5] * 0.12) / 26; //calculates epf (ded1) AS per basic earning * 12%

$v_amt12=$a_earres[21]; //calculates the esi deduction 1.75% of (basic + specialallowance)
$v_amt13=$a_earres[22]; //calculates pt (ded3) AS per gross earning FIXED pt IS decided BY govt.
$v_amt14=$a_earres[23]; //calculates manually the input VALUE of advance amount(ded4) 
$v_amt15=$a_earres[24]; //calculates manually the input VALUE of mess/ration amount (ded5)
$v_amt16=$a_earres[25]; //calculates manually the input VALUE of coupon amount (ded6)
$v_amt17=$a_earres[26]; //calculates manually the input VALUE of uniform deduction amount (ded7)

//Total Earnings (FIELD IN Net Salary TABLE)

$v_amt9= $v_amt1 + $v_amt2 + $v_amt3 + $v_amt4 + $v_amt5 + $v_amt6 + $v_amt7; //calculates gross earning

//Tota Deduction (FIELD IN Net Salary TABLE)
$v_amt10= $v_amt8 + $v_amt12 + $v_amt13 + $v_amt14 + $v_amt15 + $v_amt16 + $v_amt17;//calculates total deduction

//Net Salary
$v_amt11= $v_amt9 - $v_amt10; //calculates net salary of an employee

//Total WORK Days
$v_amt18=$a_earres[5] + $a_earres[6]; //calculates total WORK days=earning duty + weekly off

//Total Duty
$v_amt19=$a_earres[7] + $a_earres[8]; //calculates total duty=total WORK days + other duty

//Calculation FOR Actual Salary TABLE 

$v_amt23=($a_earres[65]+$a_earres[66]+$a_earres[67]+$a_earres[68]+$a_earres[69]+$a_earres[70]+$a_earres[71]+$a_earres[72]+$a_earres[73]+$a_earres[74]+$a_earres[75]+$a_earres[76]);//calculates gross salary

$v_amt24=($a_earres[78]+$a_earres[79]+$a_earres[80]+$a_earres[81]+$a_earres[82]+$a_earres[83]+$a_earres[84]);//calculates total deduction

$v_amt25=($v_amt23-$v_amt24);//calculates net salary

//Calculation FOR calculating Special allowance,Uniform,X-man allowance,HRA,Conveyance & Medical allowance

IF ($a_earres[2]==1)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
ELSEIF ($a_earres[2]==3)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
ELSEIF ($a_earres[2]==4)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
ELSEIF ($a_earres[2]==5)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
ELSEIF ($a_earres[2]==6)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
ELSEIF ($a_earres[2]==10)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
ELSEIF ($a_earres[2]==11)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}

//Calculation FOR calculating Professional Tax (PT) AS per state_code AND client_code

IF($a_earres[64]==1)
{
	IF($a_earres[62]==1)
	{
		IF($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=10;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=40;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=80;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=100;
		}						

	}
	ELSEIF($a_earres[62]==2)
	{
		IF($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=40;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=80;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=150;
		}						

	}
		ELSEIF($a_earres[62]==3)
	{	
		IF($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=60;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=120;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=150;
		}						
	}
	ELSEIF($a_earres[62]==4)
	{	
		IF($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=80;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=150;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=200;
		}						
	}
	ELSEIF($a_earres[62]==5)
	{	
		IF($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=80;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=150;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=200;
		}						
	}
	ELSEIF($a_earres[62]==6)
	{	IF($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=80;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=150;
		}
		ELSEIF ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=200;
		}						
	}
}

//calculation FOR calculating ESIC AS per state_code, client_Code & ESIC deducted zone


IF($a_earres[63]==1)
{
	IF ($a_earres[62]==1)
	{
	$v_amt12=($a_earres[13] + $a_earres[17]) * 0.0175;	
	}
	ELSEIF ($a_earres[63]==0)
	{
 	$v_amt12=0;
	}						
}


update attendancemaster set total_work_days=$v_amt18 where emp_code=$a_earres[0];
update attendancemaster set total_duty=$v_amt19 where emp_code=$a_earres[0]; 

update empearnings set e_amount1=$v_amt1 where emp_code=$a_earres[0];
update empearnings set e_amount3=$v_amt20 where emp_code=$a_earres[0];
update empearnings set e_amount4=$v_amt4 where emp_code=$a_earres[0];
update empearnings set e_amount5=$v_amt21 where emp_code=$a_earres[0];
update empearnings set e_amount6=$v_amt22 where emp_code=$a_earres[0];
update empearnings set e_amount9=$v_amt3 where emp_code=$a_earres[0];
update empearnings set e_amount10=$v_amt7 where emp_code=$a_earres[0];
update empearnings set e_amount11=$v_amt5 where emp_code=$a_earres[0];
update empearnings set e_amount16=$v_amt6 where emp_code=$a_earres[0];
update empearnings set e_amount17=$v_amt2 where emp_code=$a_earres[0];

update empearnings set d_amount1=$v_amt8 where emp_code=$a_earres[0];
update empearnings set d_amount2=$v_amt12 where emp_code=$a_earres[0];
update empearnings set d_amount3=$v_amt13 where emp_code=$a_earres[0];
update empearnings set d_amount4=$v_amt14 where emp_code=$a_earres[0];
update empearnings set d_amount5=$v_amt15 where emp_code=$a_earres[0];
update empearnings set d_amount6=$v_amt16 where emp_code=$a_earres[0];
update empearnings set d_amount7=$v_amt17 where emp_code=$a_earres[0];

update netsalary set gross_earning=$v_amt9 where emp_code=$a_earres[0];
update netsalary set total_deduction=$v_amt10 where emp_code=$a_earres[0];

update actualsalary set gross_salary=$v_amt23 where emp_code=$a_earres[0];
update actualsalary set total_deduction=$v_amt24 where emp_code=$a_earres[0];
update actualsalary set net_salary=$v_amt25 where emp_code=$a_earres[0];
    END$$

DELIMITER ;

Link to comment
https://forums.phpfreaks.com/topic/263381-procedure-not-working/
Share on other sites
prabhu3705 Newbie

prabhu3705

Posted
  • Author
Posted

hey friend

 

<?php

include("connect.php")


$ear_res=mysql_query("select empinfo.emp_code,empinfo.basic,empinfo.designation_id,empinfo.client_code,empinfo.branch_code,

attendancemaster.earning_duty,attendancemaster.weekly_off,attendancemaster.total_work_days,
attendancemaster.other_duty,attendancemaster.incentive_duty,attendancemaster.month_days,
attendancemaster.month,attendancemaster.year,

empearnings.e_amount1,empearnings.e_amount4,empearnings.e_amount9,empearnings.e_amount10,
empearnings.e_amount11,empearnings.e_amount16,empearnings.e_amount17,empearnings.d_amount1,
empearnings.d_amount2,empearnings.d_amount3,empearnings.d_amount4,empearnings.d_amount5,
empearnings.d_amount6,empearnings.d_amount7,

netsalary.salary_type,netsalary.gross_earning,netsalary.total_deduction,netsalary.net_salary,
netsalary.payment_mode,netsalary.transaction_id,netsalary.transaction_date,netsalary.pay_date,

clientallowancemaster.e_head10,clientallowancemaster.percent_value10,clientallowancemaster.fixed_value10,
clientallowancemaster.e_head11,clientallowancemaster.percent_value11,clientallowancemaster.fixed_value11,
clientallowancemaster.e_head13,clientallowancemaster.percent_value13,clientallowancemaster.fixed_value13,
clientallowancemaster.e_head15,clientallowancemaster.percent_value15,clientallowancemaster.fixed_Value15,
clientallowancemaster.e_head16,clientallowancemaster.percent_value16,clientallowancemaster.fixed_Value16,
clientallowancemaster.e_head3,clientallowancemaster.percent_value3,clientallowancemaster.fixed_Value3,
clientallowancemaster.e_head5,clientallowancemaster.percent_value5,clientallowancemaster.fixed_Value5,
clientallowancemaster.e_head6,clientallowancemaster.percent_value6,clientallowancemaster.fixed_Value6, 

empearnings.e_amount3,empearnings.e_amount5,empearnings.e_amount6,

clientmaster.state_code,clientmaster.esic_deduction,clientmaster.pt_deduction,

actualsalary.basic, actualsalary.hra,actualsalary.medical_allowance,actualsalary.conveyance,
actualsalary.incentive,actualsalary.uniform_allow,actualsalary.washing_allow,actualsalary.bonus,
actualsalary.leave_wage,actualsalary.xman_allow,actualsalary.other_allow,actualsalary.special_allow,
actualsalary.gross_salary,actualsalary.epf,actualsalary.pt,actualsalary.esic,actualsalary.mess_ration,
actualsalary.coupon,actualsalary.uniform,actualsalary.advance,actualsalary.total_deduction,
actualsalary.net_salary

from empinfo,attendancemaster,empearnings,netsalary,clientallowancemaster,clientmaster,actualsalary

where empinfo.emp_code=attendancemaster.emp_code && empinfo.emp_code=empearnings.emp_code && empinfo.emp_code=netsalary.emp_code && empinfo.client_code=clientallowancemaster.client_code && empinfo.designation_id=clientallowancemaster.designation_id && empinfo.client_code=clientallowancemaster.client_code && clientallowancemaster.client_code=clientmaster.client_code && actualsalary.emp_code=empinfo.emp_code") or die("errors");


while ($a_earres=mysql_fetch_array($ear_res))
{

//Earning Head

$v_amt1=($a_earres[1] * $a_earres[7])/$a_earres[10];//calculates basic (ear1) as per basic * no. of work days
$v_amt2=$a_earres[13] * 0.0833;//calculates bonus (ear17) where .0833=8.33%
$v_amt3=(($a_earres[13] * 15)/$a_earres[10])/12;//calculates leave wage (ear9) for 15 days per month where 					//15 is days,$a_earres[10] is month days & 12 is no. of months in a year

$v_amt4=($a_earres[1] * $a_earres[8])/$a_earres[10];//calculates incentives(ear4)
$v_amt5=$a_earres[17];	//calculates special allowance (ear11) as per basic earning * percent_value11
$v_amt6=$a_earres[18]; //calculates uniform allowance (ear16) as per basic earning * percent_value16
$v_amt7=$a_earres[16]; //calculates x-man allowance(ear10) as per basic earning * percent_value10
$v_amt20=$a_earres[59]; //calculates HRA as per basic earning * percent_value3
$v_amt21=$a_earres[60]; //calculates Conveyance as per basic earning * percent_value5
$v_amt22=$a_earres[61]; //calculates Medical Allowance as per basic earning * percent_value6

//Deduction Head
$v_amt8=($a_earres[1] * $a_earres[5] * 0.12) / 26; //calculates epf (ded1) as per basic earning * 12%

$v_amt12=$a_earres[21]; //calculates the esi deduction 1.75% of (basic + specialallowance)
$v_amt13=$a_earres[22]; //calculates pt (ded3) as per gross earning fixed pt is decided by govt.
$v_amt14=$a_earres[23]; //calculates manually the input value of advance amount(ded4) 
$v_amt15=$a_earres[24]; //calculates manually the input value of mess/ration amount (ded5)
$v_amt16=$a_earres[25]; //calculates manually the input value of coupon amount (ded6)
$v_amt17=$a_earres[26]; //calculates manually the input value of uniform deduction amount (ded7)

//Total Earnings (Field in Net Salary Table)

$v_amt9= $v_amt1 + $v_amt2 + $v_amt3 + $v_amt4 + $v_amt5 + $v_amt6 + $v_amt7; //calculates gross earning

//Tota Deduction (Field in Net Salary Table)
$v_amt10= $v_amt8 + $v_amt12 + $v_amt13 + $v_amt14 + $v_amt15 + $v_amt16 + $v_amt17;//calculates total deduction

//Net Salary
$v_amt11= $v_amt9 - $v_amt10; //calculates net salary of an employee

//Total Work Days
$v_amt18=$a_earres[5] + $a_earres[6]; //calculates total work days=earning duty + weekly off

//Total Duty
$v_amt19=$a_earres[7] + $a_earres[8]; //calculates total duty=total work days + other duty

//Calculation for Actual Salary Table 

$v_amt23=($a_earres[65]+$a_earres[66]+$a_earres[67]+$a_earres[68]+$a_earres[69]+$a_earres[70]+$a_earres[71]+$a_earres[72]+$a_earres[73]+$a_earres[74]+$a_earres[75]+$a_earres[76]);//calculates gross salary

$v_amt24=($a_earres[78]+$a_earres[79]+$a_earres[80]+$a_earres[81]+$a_earres[82]+$a_earres[83]+$a_earres[84]);//calculates total deduction

$v_amt25=($v_amt23-$v_amt24);//calculates net salary

//Calculation for calculating Special allowance,Uniform,X-man allowance,HRA,Conveyance & Medical allowance

if ($a_earres[2]==1)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
elseif ($a_earres[2]==3)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
elseif ($a_earres[2]==4)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
elseif ($a_earres[2]==5)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
elseif ($a_earres[2]==6)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
elseif ($a_earres[2]==10)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}
elseif ($a_earres[2]==11)
{
	$v_amt5=$a_earres[13] * $a_earres[39];
	$v_amt6=$a_earres[13] * $a_earres[48];
	$v_amt7=$a_earres[13] * $a_earres[36];
	$v_amt20=$a_earres[13] * $a_earres[51];
	$v_amt21=$a_earres[13] * $a_earres[54];
	$v_amt22=$a_earres[13] * $a_earres[57];
}

//Calculation for calculating Professional Tax (PT) as per state_code and client_code

if($a_earres[64]==1)
{
	if($a_earres[62]==1)
	{
		if($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=10;
		}
		elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=40;
		}
		elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=80;
		}
		elseif ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=100;
		}						

	}
	elseif($a_earres[62]==2)
	{
		if($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=40;
		}
		elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=80;
		}
		elseif ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=150;
		}						

	}
		elseif($a_earres[62]==3)
	{	
		if($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=60;
		}
		elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=120;
		}
		elseif ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=150;
		}						
	}
	elseif($a_earres[62]==4)
	{	
		if($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=80;
		}
		elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=150;
		}
		elseif ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=200;
		}						
	}
	elseif($a_earres[62]==5)
	{	
		if($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=80;
		}
		elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=150;
		}
		elseif ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=200;
		}						
	}
	elseif($a_earres[62]==6)
	{	if($a_earres[13] + $a_earres[18]<3000)  
		{
			 $v_amt13=0;		
		}
		elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
		{ 
			 $v_amt13=20;
		}
		elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
		{ 
			 $v_amt13=80;
		}
		elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
		{ 
			 $v_amt13=150;
		}
		elseif ($a_earres[13] + $a_earres[18]>=12000)
		{
			 $v_amt13=200;
		}						
	}
}

//calculation for calculating ESIC as per state_code, client_Code & ESIC deducted zone


if($a_earres[63]==1)
{
	if ($a_earres[62]==1)
	{
	$v_amt12=($a_earres[13] + $a_earres[17]) * 0.0175;	
	}
	elseif ($a_earres[63]==0)
	{
 	$v_amt12=0;
	}						
}


/*	if ($a_earres[13] + $a_earres[18]<3000)  
{
	 $v_amt13=0;

}
elseif ($a_earres[13] + $a_earres[18]>=3000 && $a_earres[13] + $a_earres[18]<=5999) 
{ 
	 $v_amt13=20;
}
elseif ($a_earres[13] + $a_earres[18]>=6000 && $a_earres[13] + $a_earres[18]<=8999)
{ 
	 $v_amt13=80;
}
elseif ($a_earres[13] + $a_earres[18]<=9000 && $a_earres[13] + $a_earres[18]<=11999)
{ 
	 $v_amt13=150;
}
elseif ($a_earres[13] + $a_earres[18]>=12000)
{
	 $v_amt13=200;
}
*/
/*
mysql_query("update (empearnings set e_amount1=$v_amt1,e_amount17=$v_amt2,e_amount9=$v_amt3,e_amount4=$v_amt4,e_amount11=$v_amt5,e_amount16=$v_amt6,e_amount10=$v_amt7,d_amount1=$v_amt8,d_amount=$v_amt12,d_amount3=$v_amt13,d_amount4=$v_amt14,d_amount5=$v_amt15,d_amount6=$v_amt16,d_amount7=$v_amt17,e_amount3=$v_amt20,e_amount5=$v_amt21,e_amount6=$v_amt22),(netsalary set gross_earning=$v_amt9,total_deduction=$v_amt10,net_salary=$v_amt11),(attendancemaster set total_work_days=$v_amt18,total_duty=$v_amt19),(actualsalary set gross_salary=$v_amt23,total_deduction=$v_amt24,net_salary=$v_amt25) where emp_code=$a_earres[0]");

*/

mysql_query("update attendancemaster set total_work_days=$v_amt18 where emp_code=$a_earres[0]");
mysql_query("update attendancemaster set total_duty=$v_amt19 where emp_code=$a_earres[0]"); 

mysql_query("update empearnings set e_amount1=$v_amt1 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount3=$v_amt20 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount4=$v_amt4 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount5=$v_amt21 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount6=$v_amt22 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount9=$v_amt3 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount10=$v_amt7 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount11=$v_amt5 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount16=$v_amt6 where emp_code=$a_earres[0]");
mysql_query("update empearnings set e_amount17=$v_amt2 where emp_code=$a_earres[0]");

mysql_query("update empearnings set d_amount1=$v_amt8 where emp_code=$a_earres[0]");
mysql_query("update empearnings set d_amount2=$v_amt12 where emp_code=$a_earres[0]");
mysql_query("update empearnings set d_amount3=$v_amt13 where emp_code=$a_earres[0]");
mysql_query("update empearnings set d_amount4=$v_amt14 where emp_code=$a_earres[0]");
mysql_query("update empearnings set d_amount5=$v_amt15 where emp_code=$a_earres[0]");
mysql_query("update empearnings set d_amount6=$v_amt16 where emp_code=$a_earres[0]");
mysql_query("update empearnings set d_amount7=$v_amt17 where emp_code=$a_earres[0]");

mysql_query("update netsalary set gross_earning=$v_amt9 where emp_code=$a_earres[0]");
mysql_query("update netsalary set total_deduction=$v_amt10 where emp_code=$a_earres[0]");

mysql_query("update actualsalary set gross_salary=$v_amt23 where emp_code=$a_earres[0]");
mysql_query("update actualsalary set total_deduction=$v_amt24 where emp_code=$a_earres[0]");
mysql_query("update actualsalary set net_salary=$v_amt25 where emp_code=$a_earres[0]");

}

echo $v_amt1. "<br>";//calculates ear1 from empearnings table (basic earning)
echo $v_amt2. "<br>";//calculates ear17 from empearnings table (bonus)
echo $v_amt3. "<br>";//calculates ear9 from empearnings table (leave)
echo $v_amt4. "<br>";//calculates ear4 from empearnings table (incentive)
echo $v_amt5. "<br>";//calculates ear11 from empearnings table (special allowance)
echo $v_amt6. "<br>";//calculates ear16 from empearnings table (uniform allowance)
echo $v_amt7. "<br>";//calculates ear10 from empearnings table (x-man allowance)
echo $v_amt8. "<br>";//calculates ded1 from empearnings table (epf deduction)
echo $v_amt9. "<br>";//calculates (gross_earning) from netsalary table
echo $v_amt10. "<br>";//calculates (total_deduction) from netsalary table
echo $v_amt11. "<br>";//calculates (net_salary from) netsalary table
echo $v_amt12. "<br>";//calculates ded2 from empearnings table (esic deduction)
echo $v_amt13. "<br>";//calculates ded3 from empearnings table (PT deduction)
echo $v_amt14. "<br>";//calculates ded4 from empearnings table (advance deduction)
echo $v_amt15. "<br>";//calculates ded5 from empearnings table (mess/ration deduction)
echo $v_amt16. "<br>";//calculates ded6 from empearnings table (coupon deduction)
echo $v_amt17. "<br>";//calculates ded7 from empearnings table (uniform deduction)
echo $v_amt18. "<br>";//calculates (total_work_days) from attendancemaster table
echo $v_amt19. "<br>";//calculates (total_duty) from attendancemaster table
echo $v_amt20. "<br>";//calculates ear3 from empearnings table (HRA)
echo $v_amt21. "<br>";//calculates ear5 from empearnings table (Conveyance)
echo $v_amt22. "<br>";//calculates ear6 from empearnings table (Medical Allowance)
echo $v_amt23. "<br>";//calculates gross_salary from actualsalary table 
echo $v_amt24. "<br>";//calculates total_deduction from actualsalary table
echo $v_amt25. "<br>";//calculates net_salary from actualsalary table

?>

 

 

i hv converted this php file to perform same as that of mysql procedure to do the calculation as fast as possible...

 

i hv followed all the syntax and made the procedure...

 

errors are:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[] INT UNSIGNED DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FO' at line 10

 

 

how can i use array inside procedure ihv to store 64 diffrent int values to perform calclulation and result is need to be stored inside diffrent tables of ma database...

 

please help

awjudd Member

awjudd

Posted
Posted

Yet again you neglected to use code tags.

 

In the database there is no such thing as an array, that is the definition of a table.  If you need to pass in 64 ints, then you could pass it in as a comma separated list and then change that into a table and then JOIN against it.

 

~awjudd

cpd Member

cpd

Posted
Posted

The obvious thing to say is your trying to implement features of PHP (and other programming languages) into SQL. SQL is a language in its infancy and as such does not support anywhere near what other languages do; it also never will as its purpose is different to that of standard programming languages.

 

Taking the code from a mysql_query execution and placing it in a stored procedure does not necessarily make it more efficient or faster, in-fact I find it hard to believe it will do either. Techniques such as indexing are what will make your queries more efficient.

smoseley Newbie

smoseley

Posted
Posted

You can use a temporary table in mysql similarly to an array, but I doubt you need one.  Your code is unnecessarily complex, bordering on ridiculous.

 

Your database design is obscure, abstract, and denormalized beyond logic.  It's a hot mess.  Consider redesigning it.

awjudd Member

awjudd

Posted
Posted

SQL is a language in its infancy and as such does not support anywhere near what other languages do

 

This tells me that you do not know the sheer power of SQL.  Yes you can't do stuff that a functional programming language does, but that is for a specific reason.  It isn't supposed to be able to.  SQL is used to get at and manipulate data, NOT anything else.

 

Taking the code from a mysql_query execution and placing it in a stored procedure does not necessarily make it more efficient or faster, in-fact I find it hard to believe it will do either.

 

It only allows the query optimizer to know exactly what is going on before hand so that it can pre-determine the most optimal way of getting the information being requested.

 

~awjudd

smoseley Newbie

smoseley

Posted
Posted

SQL is a language in its infancy and as such does not support anywhere near what other languages do

 

Right there you just lost all respect I had for you (if I ever had any) . Do you even know how old SQL is?

 

Yea. SQL is very mature.

Don't know if I can agree with that.  "Mature" is not an indication of age, but of developmental maturation.

 

SQL is still evolving:  ANSI SQL-86, 89, 92, 1999, 2003, 2008, 2011

 

 

cpd Member

cpd

Posted
Posted

Perhaps I wasn't specific enough. What this chap is trying to do in SQL is still in its infancy. Its very similar to T-SQL which is also in its infancy, hence why I drew that conclusion. MySQL does not use T-SQL however, it mimics a lot of what SQL Server does E.g. Try statements can be replicated with handlers in MySQL, therefore making it similar and correct me if I'm wrong, but is this chap not trying to use handlers (may be wrong as I've never used them)?

 

Stored procedures are gradually being used more and more to write logical actions and this is often done in an SQL Server with T-SQL. Once again, if you look very closely, you will note he's tried to take his logic and put it in the MySQL Server with shed loads of if statements and what not.... see my point?

 

Try thinking outside the box before loosing respect for someone.

smoseley Newbie

smoseley

Posted
Posted

Stored procedures are gradually being used more and more to write logical actions and this is often done in an SQL Server with T-SQL.

That's not a new thing.  MS developers often put a lot of logic in sprocs.  I was an MS developer 10 years ago, and that was the case back back then, too.

cpd Member

cpd

Posted
Posted

Really? I was under the impression its only found its feet within the last 5 years or so and the whole "middle-tier" thing will now begin getting pushed out as security at the SQL Server level is getting increasingly better and the logic can now be carried out at the SQL Server level?

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.