存取過程
SQL 存取過程
-- 找出要計算薪資的員工並找出他們的基本/月結資料
select @sql='select a.id,a.empno,a.sex,a.workyeardate,a.enterdate,a.leavedate,
isnull(cast(dbo.fgetemporgid(a.id,'''+@edate+''') as uniqueidentifier),a.workorgid) as workorgid,a.workid,
a.senior,a.titleid,a.jobid,a.grade,a.gradelevel,a.indirect,a.labortype,a.hiretype,b.centerid,b.dependents,
isnull(cast(dbo.fsalaryadj(a.id,''paytype'','''+@edate+''') as uniqueidentifier),b.paytype) as paytype,
b.paymethod,b.bankid,b.bankaccount,b.allowadvpay,b.bankid2,b.bankaccount2,b.bankrate,b.bankamount
from corpemployee a inner join corpempsalary b on a.id=b.eid
where a.enterdate<='''+@edate+''''
if ((@enterdate1='' or @enterdate1 is null) and (@enterdate2='' or @enterdate2 is null))
select @sql=@sql+' and (a.enterdate is null or a.enterdate<='''+@edate+''')'
if ((@leavedate1='' or @leavedate1 is null) and (@leavedate2='' or @leavedate2 is null))
select @sql=@sql+' and (a.leavedate is null or a.leavedate>='''+@sdate+''')'
if (@enterdate1<>'' and @enterdate1 is not null)
select @sql=@sql+' and a.enterdate>='''+@enterdate1+''''
if (@leavedate1<>'' and @leavedate1 is not null)
select @sql=@sql+' and a.leavedate>='''+@leavedate1+''''
if (@enterdate2<>'' and @enterdate2 is not null)
select @sql=@sql+' and a.enterdate<='''+@enterdate2+''''
if (@leavedate2<>'' and @leavedate2 is not null)
select @sql=@sql+' and a.leavedate<='''+@leavedate1+''''
if (@empno<>'' and @empno is not null)
select @sql=@sql+' and (a.empno like '''+@empno+'%'' or a.name like ''%'+@empno+'%'' or a.name1 like ''%'+@empno+'%'')'
if (@removeflag='0')
select @sql=@sql+' and a.id not in (select eid from corpempwage where pcid='''+@pcid+''' and manual=''Y'')'
if (@removeflag='2')
select @sql=@sql+' and a.id not in (select eid from corpempwage where pcid='''+@pcid+''')'
if (@issummary='N')
select @sql=@sql+' and b.allowadvpay=''Y'''
select @sql='insert #tempempinfo2 (eid,empno,sex,workyeardate,enterdate,leavedate,workorgid,workid,senior,titleid,jobid,grade,gradelevel,indirect,labortype,hiretype,centerid,dependents,paytype,paymethod,bankid,bankaccount,allowadvpay,late,latetime,early,earlytime,absence,absencetime,worktime,bankid2,bankaccount2,bankrate,bankamount)
select a.id,a.empno,a.sex,a.workyeardate,a.enterdate,a.leavedate,a.workorgid,isnull(c.workid,a.workid),
a.senior,a.titleid,a.jobid,a.grade,a.gradelevel,isnull(c.indirect,a.indirect),a.labortype,a.hiretype,
a.centerid,a.dependents,a.paytype,a.paymethod,a.bankid,a.bankaccount,a.allowadvpay,
d.late,d.latetime,d.early,d.earlytime,d.absence,d.absencetime,d.worktime,a.bankid2,a.bankaccount2,a.bankrate,a.bankamount
from ('+@sql+') a
inner join corporganization b on a.workorgid=b.id
left join corpworkdaily c on a.id=c.eid and c.wdate=(select max(wdate) from corpworkdaily where eid=c.eid and wdate<='''+@edate+''')
left join corpworkmonthly d on a.id=d.eid and d.pcid='''+@pcid+'''
where a.paytype='''+@paytype+''''
if (@companyid<>'' and @companyid is not null)
select @sql=@sql+' and b.pids like ''%'+@companyid+'%'''
if (@deptid<>'' and @deptid is not null)
select @sql=@sql+' and a.workorgid='''+@deptid+''''
if (@authval<>'' and @authval is not null)
select @sql=@sql+' and '+@authval
--insert tempempinfo2 values (substring(@sql,1,200),substring(@sql,201,200),substring(@sql,401,200),substring(@sql,601,200),substring(@sql,801,200),substring(@sql,1001,200),substring(@sql,1201,200),substring(@sql,1401,200),substring(@sql,1601,200),substring(@sql,1801,200),substring(@sql,2001,200),substring(@sql,2201,200))
exec (@sql)
ALTER PROCEDURE [dbo].[pprfixed]
@pcid varchar(50),
@smoothenterprise_id varchar(50),
@errmsg varchar(200) OUTPUT
AS
declare
@ssdate datetime,
@sedate datetime,
@timesdate datetime,
@timeedate datetime,
@xtimesdate datetime,
@xtimeedate datetime,
@nonleaveday smallint,
@ppday smallint,
@daypermonth smallint,
@wagesource varchar(50),
@eid uniqueidentifier,
@empsdate datetime,
@empedate datetime,
@emptimesdate datetime,
@emptimeedate datetime,
@wid varchar(50),
@sdate datetime,
@edate datetime,
@defaultamount decimal(7,0),
@amount int,
@suspenddays smallint,
@timestamp datetime
select @ssdate=sdate,@sedate=edate,@ppday=datediff(d,@ssdate,@sedate)+1 from corppaycontrol where id=@pcid
select @wagesource=id,@timestamp=getdate() from corpcode where ctype='wagesource' and code='00'
select @timesdate=timesdate,@timeedate=timeedate,@nonleaveday= datediff(d,@timesdate,@timeedate)+1 from CORPPAYCONTROL where id=@pcid
declare fixedcurs cursor for
select eid
, case when enterdate<@ssdate then @ssdate else enterdate end
, case when (leavedate is null or leavedate>@sedate) then @sedate else leavedate end
, case when enterdate<@timesdate then @timesdate else enterdate end
, case when (leavedate is null or leavedate>@timeedate) then @timeedate else leavedate end
from #tempempinfo
open fixedcurs
fetch next from fixedcurs into @eid,@empsdate,@empedate,@emptimesdate,@emptimeedate
while @@fetch_status=0
begin
declare wcurs cursor for
select wid
, case when sdate<@empsdate then @empsdate else sdate end
, case when (edate is null or edate>@empedate) then @empedate else edate end
, case when sdate<@emptimesdate then @emptimesdate else sdate end
, case when (edate is null or edate>@emptimeedate) then @emptimeedate else edate end
, amount
from corpempsalaryitem
where eid=@eid
and sdate<=@sedate
and (edate is null or edate>=@ssdate)
and amount is not null
order by wid,sdate
open wcurs
fetch next from wcurs into @wid,@sdate,@edate,@xtimesdate,@xtimeedate,@defaultamount
while @@fetch_status=0
begin
if @wid='7A28C3CD-BC23-4099-9E4D-E8678A5AA4F4'
begin
--if @edate>@timeedate
--begin
-- set @edate=@timeedate
--end
--if @sdate>@timesdate
--begin
-- set @sdate=@timesdate
--end
--select @sdate = @xtimesdate, @edate = @xtimeedate, @ssdate = @timesdate, @sedate = @timeedate, @daypermonth=@nonleaveday
select @sdate = @xtimesdate, @edate = @xtimeedate, @daypermonth=@nonleaveday
if @sdate<=@edate
begin
select @suspenddays=isnull( (select sum(datediff(d, (case when sdate<@sdate then @sdate else sdate end),
(case when returndate is null or returndate>@edate then dateadd(d,1,@edate) else returndate end)))
from corpemplayoff where eid=@eid and sdate<=edate and (returndate is null or returndate>@sdate)),0)
-- select @amount=case when (@sdate=@ssdate and @edate=@sedate and @suspenddays=0) then @defaultamount
select @amount=case when (@sdate=@timesdate and @edate=@timeedate and @suspenddays=0) then @defaultamount
else round((@defaultamount/@daypermonth)*(datediff(d,@sdate,@edate)+1-@suspenddays),0) end
if exists (select eid from corpempwageitem where eid=@eid and pcid=@pcid and wid=@wid and wagesource=@wagesource)
update corpempwageitem set defaultamount=@defaultamount,amount=amount+@amount where eid=@eid and pcid=@pcid and wid=@wid and wagesource=@wagesource
else
insert corpempwageitem (eid,pcid,wid,wagesource,defaultamount,amount,inituid,initdate,modifyuid,modifydate) values (@eid,@pcid,@wid,@wagesource,@defaultamount,@amount,@smoothenterprise_id,@timestamp,@smoothenterprise_id,@timestamp)
end
end
if @wid<>'7A28C3CD-BC23-4099-9E4D-E8678A5AA4F4'
begin
set @daypermonth=@ppday
select @suspenddays=isnull( (select sum(datediff(d, (case when sdate<@sdate then @sdate else sdate end),
(case when returndate is null or returndate>@edate then dateadd(d,1,@edate) else returndate end)))
from corpemplayoff where eid=@eid and sdate<=@edate and (returndate is null or returndate>@sdate)),0)
select @amount=case when (@sdate=@ssdate and @edate=@sedate and @suspenddays=0) then @defaultamount
else round((@defaultamount/@daypermonth)*(datediff(d,@sdate,@edate)+1-@suspenddays),0) end
if exists (select eid from corpempwageitem where eid=@eid and pcid=@pcid and wid=@wid and wagesource=@wagesource)
update corpempwageitem set defaultamount=@defaultamount,amount=amount+@amount where eid=@eid and pcid=@pcid and wid=@wid and wagesource=@wagesource
else
insert corpempwageitem (eid,pcid,wid,wagesource,defaultamount,amount,inituid,initdate,modifyuid,modifydate) values (@eid,@pcid,@wid,@wagesource,@defaultamount,@amount,@smoothenterprise_id,@timestamp,@smoothenterprise_id,@timestamp)
end
fetch next from wcurs into @wid,@sdate,@edate,@xtimesdate,@xtimeedate,@defaultamount
end
close wcurs
deallocate wcurs
fetch next from fixedcurs into @eid,@empsdate,@empedate,@emptimesdate,@emptimeedate
end
close fixedcurs
deallocate fixedcurs
-------------------------------
ORACLE
create or replace procedure EMS_MER_STOCKOUT(msid in varchar2,
mtid in varchar2,
mnum in number,
moutno in varchar2,
muserid in varchar2,
musername in varchar2,
mbu in varchar2,
mbigdept in varchar2,
mdept in varchar2,
mempno in varchar2,
mempname in varchar2,
mMANAGE_ID in varchar2,
mremark in varchar2,
mstockid in varchar2,
mifbad in varchar2,
strResult out varchar2
) is
--sid, tid, num, outno, userid, username, bu, bigdept, dept, empno, empname,PRICE,PID,MANAGE_ID
out_id WMS_MATERIALOUT.Outid%type;
tempNum number;
subNum number;
mpid WMS_MATERIALOUT.Pid%type;
price WMS_MATERIALOUT.Price%type;
out_num number;
tnum number;
TotalNum number;--畐い计秖
begin
-- 莉程穝OUTID
--2007/1/26 畐摸 (ㄓ耞琌传┪祇)
--2006/12/21 だ恨瞶
declare
cursor MyId is
SELECT WMSMETOUT.NEXTVAL id FROM DUAL;
begin
open MyId;
fetch MyId
into out_id;
close MyId;
end;
-- 莉畐い计秖
declare
cursor Total is
select sum(num) num from wms_materiallot where num > 0 and sid=msid and STOCKID=mstockid ;
begin
open Total;
fetch Total
into TotalNum;
close Total;
end;
out_num :=mnum;
if out_num <= TotalNum then
begin
---础wms_meterialoutlist
insert into wms_meterialoutlist (num, outno, outid,stockid)
values (out_num, moutno, out_id,mstockid);
commit;
----搭畐计秖
update wms_material set num =num- out_num where sid = msid and STOCKID=mstockid;
commit;
--础灿兜
-- 莉讽玡SID┮ΤΤ计秖у腹獺,畐ら戳抖
declare
cursor MyNum is
select num,pid,price from wms_materiallot where num > 0 and sid=msid and STOCKID=mstockid order by RECORDDATE,num asc ;
begin
open MyNum;
--
while out_num > 0 loop
fetch MyNum
into tempNum,mpid,price;
tnum :=out_num;
out_num :=out_num-tempNum;
subNum:=tempNum;
if out_num > 0 then
begin
--穝赣у腹计秖
update wms_materiallot set num =num-subNum where pid = mpid and STOCKID=mstockid;
commit;
/*update wms_materiallot set num =num-7 where pid = '20060714001-006';
commit;*/
--础–у腹畐獺
insert into wms_materialout(sid, tid, num, outno, userid, username, bu, bigdept, dept, empno, empname,PRICE,PID,MANAGE_ID,outid,remark,stockid,ifbad)
values(msid,mtid,tempNum,moutno,muserid, musername, mbu, mbigdept, mdept, mempno, mempname,price,mpid,mMANAGE_ID,out_id,mremark,mstockid,mifbad);
commit;
end;
else
begin
insert into wms_materialout(sid, tid, num, outno, userid, username, bu, bigdept, dept, empno, empname,PRICE,PID,MANAGE_ID,outid,remark,stockid,ifbad)
values(msid,mtid,tnum,moutno,muserid, musername, mbu, mbigdept, mdept, mempno, mempname,price,mpid,mMANAGE_ID,out_id,mremark,mstockid,mifbad);
commit;
update wms_materiallot set num =num-tnum where pid = mpid and STOCKID=mstockid;
commit;
end;
end if;
end loop;
close MyNum;
end;
strResult := '1';
end;
else
strResult := '-1';
end if;
Exception
When others then
rollback;
end EMS_MER_STOCKOUT;
浙公网安备 33010602011771号