USE [ChiefmesNew]
GO
/****** Object: StoredProcedure [dbo].[st_MES_InsertIntoSalaryManage] Script Date: 12/29/2015 09:36:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************
-- Author: lxf
-- Create date: 2012-12-08 10:25:00.00
-- Update:fsq
-- Update Date:2010.06.30
-- update:hz
-- udpate date:2015-07-01
-- Function:生成工资
-- exec st_MES_InsertIntoSalaryManage '2015-12-20 08:30:00','2015-12-29 20:30:00'
***********************************************/
ALTER PROCEDURE [dbo].[st_MES_InsertIntoSalaryManage]
( @BeginDate varchar(19),
@EndDate varchar(19)
)
WITH
EXECUTE AS CALLER
AS
BEGIN
DECLARE @Mo varchar(50), @DispatchNo varchar(50),@ItemNo varchar(50),@charType char(1),@DispatchPrior float,
@CardStartDate varchar(20),@CardEndDate varchar(20),@CardID varchar(20),@EmpID varchar(20)
declare @tran_error int;
set @tran_error = 0;
declare @bcCode varchar(20),@name varchar(20), @btime varchar(20),@etime varchar(20)
IF object_id('tempdb..#bc') is not NULL drop table #bC
CREATE TABLE #bC
(
bccode varchar(20),
name varchar(20),
begintime varchar(20),
endtime varchar(20)
)
declare curBc cursor for select bccode,name, begintime,endtime from MES_BCDetail
open curBc
fetch next from curBc into @bcCode,@name,@btime,@etime
while @@FETCH_STATUS=0
begin
if @btime<@etime
begin
--insert into #bC(bccode,name,begintime,endtime) values(@bcCode,@name,@btime,@etime)
--班次结束时间减一秒 2014.7.23 hz
insert into #bC(bccode,name,begintime,endtime)
values(@bcCode,@name,@btime+':00',CONVERT(NVARCHAR(12),dateadd(ss,-1,cast(convert(char(10),GETDATE(),121) + ' ' + @etime as datetime)),108))
end
else
begin
--insert into #bC(bccode,name,begintime,endtime) values(@bcCode,@name,@btime,'23:59')
--insert into #bC(bccode,name,begintime,endtime) values(@bcCode,@name,'00:00',@etime)
--班次结束时间减一秒 2014.7.23 hz
insert into #bC(bccode,name,begintime,endtime) values(@bcCode,@name,@btime+':00','23:59:59')
insert into #bC(bccode,name,begintime,endtime) values(@bcCode,@name,'00:00:00',CONVERT(NVARCHAR(12),dateadd(ss,-1,cast(convert(char(10),GETDATE(),121) + ' ' + @etime as datetime)),108))
end
fetch next from curBc into @bcCode,@name,@btime,@etime
end
close curBc
deallocate curBc
select * from #bC
IF object_id('tempdb..#tmpCardDetail') is not NULL drop table #tmpCardDetail
CREATE TABLE #tmpCardDetail
(
EmpID varchar(20),
EmpName varchar(20),
CardID varchar(20),
DispatchNo varchar(50),
ProcCode varchar(20),
ProcName varchar(50),
StaCode varchar(20),
StaName varchar(20),
MachineNo varchar(20),
MouldNo varchar(50),
ItemNO varchar(20),
ProdQty int,
BeginDate datetime,
EndDate datetime,
ItemName varchar(500),
MachineWage decimal(8, 2),
TimeWage decimal(8, 2),
StopTime decimal(18, 2),
CheckWage decimal(8, 2),
JobWage decimal(8, 2),
ServiceWage decimal(8, 2),
OtherWage decimal(8, 2),
AdjustWage decimal(8, 2),
WageFlag int,
Remark varchar(200),
CreateMan nvarchar(200),
CreateTime datetime,
UpdateMan nvarchar(200),
UpdateTime datetime,
PostName varchar(50),
BCGroup varchar(20),
BC varchar(20),
Price decimal(18, 2),
Wage decimal(18, 2),
BcBeginTime datetime,
BcEndTime datetime,
DiffHour decimal(18, 2),
MoneyPreHour decimal(18, 2),
EmpNub int,
DispatchPrior float,
MO varchar(20),
iswork int
)
--begin tran
--根据打卡记录查询工作记录
DECLARE CUR_GetDONo CURSOR FOR
SELECT DISTINCT do.mo, cd.DispatchNo,cd.DispatchPrior
FROM Mes_CardDetail CD
join Mes_DispatchOrder do
on cd.dispatchno=do.dispatchno and cd.dispatchPrior=do.dispatchPrior
--WHERE CD.CardType = 34 AND CD.statusID = 0 and cd.dispatchno not like '%tmp%'
WHERE CD.CardType = 34 and cd.dispatchno not like '%tmp%'
AND (CD.startDate IS NOT NULL AND CD.EndDate IS NOT NULL)
and ((convert(char(16),CD.startdate,121)>=@BeginDate and convert(char(16),CD.startdate,121)<=@EndDate )
or (convert(char(16),CD.enddate,121) >=@BeginDate and convert(char(16),CD.enddate,121) <=@EndDate))
OPEN CUR_GetDONo
FETCH NEXT FROM CUR_GetDONo INTO @Mo,@DispatchNo,@DispatchPrior
WHILE @@FETCH_STATUS = 0
BEGIN
--判断工作的机台类型
select @charType=isnull(m.MachineClsId,'1') from Mes_dispatchorder d left join mes_machine m on d.MachineNO=m.MachineNO where DispatchNO=@DispatchNo
and dispatchPrior=@DispatchPrior
--print @charType
IF (@charType = '1')
BEGIN
--print @DispatchNo
--print @DispatchPrior
---=====注塑工资=======
--INSERT INTO MES_PayDetail(EmpName,PostName,BCGroup,Bc,empid,CardID, DispatchNo, MachineNo, MouldNo, itemno,ItemName,
-- ProdQty, BeginDate, EndDate, MachineWage,TimeWage,StopTime, CreateTime, WageFlag,Proccode,stacode,Price,Wage)
insert into #tmpCardDetail(EmpName,PostName,BCGroup,Bc,iswork,empid,CardID, DispatchNo, MachineNo, MouldNo, itemno,ItemName,
ProdQty, BeginDate, EndDate, MachineWage,TimeWage,StopTime, CreateTime, WageFlag,Proccode,stacode,Price,Wage,DiffHour,
MoneyPreHour,DispatchPrior,MO,EmpNub,BcBeginTime,BcEndTime)
--select EmpNameCN,WorkName,BCGroup,Bc,empid,CardID,DispatchNo,MachineNo,MouldNo,itemno,ItemName
-- ,Num,StartDate,EndDate,isnull(((Num*1.0)/(case when EmpNub=0 then 1 else EmpNub end))*Price,0.00) MachineWage ,TimeWage,StopTime, GetDate(), 1,Proccode,stacode,Price,
-- (isnull((Num/(case when EmpNub=0 then 1 else EmpNub end))*Price,0.00)+isnull(MoneyPreHour*DiffHour,0.00)) Wage
-- from (
select EmpNameCN,WorkName,DeptName BCGroup,Bc,iswork,
empid,isnull(CardID,'') CardID,DispatchNo,isnull(MachineNo,'') MachineNo,
MouldNo,itemno,ItemName,0 ProdQty,
StartDate,EndDate, 0 MachineWage ,isnull(MoneyPreHour*DiffHour,0) TimeWage,
StopTime, GetDate() CreateTime, 1 WageFlag,
Proccode,stacode,
Price,0 Wage,DiffHour,MoneyPreHour,a.DispatchPrior,a.MO,
0 EmpNub,
convert(varchar(19),(case when Bc='白班' then CONVERT(varchar(11),a.StartDate,120)+(select begintime from #bC where name=Bc)
else CONVERT(varchar(11),a.StartDate,120)+(select top 1 begintime from #bC where name=Bc)
end),
120) BcBeginTime,
convert(varchar(19),(case when Bc='白班' then CONVERT(varchar(11),a.StartDate,120)+(select endtime from #bC where name=Bc)
else CONVERT(varchar(11),dateadd(day,1,convert(datetime,a.StartDate)),120)+(select top 1 endtime from #bC where name=Bc order by endtime)
end),
120) BcEndTime
from (
select EmpNameCN=case when e.EmpNameCN<>'' then e.EmpNameCN else '' end,WorkName=l.Lan_CN,d.DeptName,
(select Name from #bC
where dateadd(n,10,cd.StartDate) between convert(char(10),cd.StartDate,120)+' '+begintime and convert(char(10),cd.StartDate,120)+ ' '+endtime
) as Bc,
e.iswork,
cd.StartEmpID empid,posName,StartcardID as CardID,DO.DispatchNo,DO.MachineNo,DO.MouldNo,DO.itemno,i.itemname
,0 Num
,cd.StartDate,cd.EndDate,DO.MO,DO.DispatchPrior
--,dbo.FN_GetStopTime(DO.MO,DO.DispatchNo,do.proccode,do.DispatchPrior,cd.StartDate,cd.EndDate) as StopTime,datediff(ss,cd.StartDate,cd.EndDate)/3600.0 DiffHour, isnull(SC.MoneyPreHour,0) as MoneyPreHour
,0 StopTime,datediff(ss,cd.StartDate,cd.EndDate)/3600.0 DiffHour, isnull(SC.MoneyPreHour,0) as MoneyPreHour
,ISNULL (DO.StandCycle, 1) as StandCycle
,ISNULL (do.SocketNum, 1) as SocketNum
,isnull(MD.MouldSoaked,1) AS Mould_Soaked
,isnull(MD.MouldFixture,1) as Mould_Fixture
,do.Multinum,
case isnull(i.standemployee,1) when 0 then 1 else isnull(i.standemployee,1) end standemployee,
cd.Proccode,cd.stacode
--,(select top 1 pd.Price from GuangHongWMS.dbo.WMS_PriceDetail pd where pd.ProductNo=DO.ItemNO order by pd.ID desc) Price
,
(select top 1 pd.Price from ChiefWMS.dbo.WMS_PriceDetail pd where pd.ProductNo=DO.ItemNO order by pd.ID desc) Price
from Mes_CardDetail cd
join V_DISPATCHORDER DO ON DO.DispatchNO = CD.DispatchNO and cd.DispatchPrior=do.DispatchPrior
left join mes_MouldDetail MD ON DO.MouldNo = MD.MouldNo
left join mes_Machine MS ON DO.MachineNo = MS.MachineNo
left join MES_PayConfigTime SC ON MS.MachineClsId = SC.worktype
left join mes_item i on i.itemno=do.itemno
left join mes_employee e on e.EmpID=cd.StartEmpID
left join mes_position p on p.poscode=e.poscode
left join sys_department d on d.deptcode=e.deptcode
/*Add by zhuss 2014-08-29*/
left join Sys_PubCode c on convert(varchar(10),cd.WorkType)=c.FieldValue and c.FieldName like '%ddl_JobType%'
left join Sys_Language l on c.KeyName=l.KeyName
WHERE cd.DispatchNO=@DispatchNo and cd.DispatchPrior = @DispatchPrior
and CD.CardType = 34 --AND CD.statusID = 0
AND (CD.startDate IS NOT NULL AND CD.EndDate IS NOT NULL) AND ((convert(char(16),CD.startdate,121)>=@BeginDate and convert(char(16),CD.startdate,121)<=@EndDate )
or (convert(char(16),cd.enddate,121) >=@BeginDate and convert(char(16),cd.enddate,121) <=@EndDate))
) a
--)b
-- declare @tEmpID varchar(20),@tCardID varchar(20),@tDispatchNo varchar(50),@tMachineNo varchar(20),@tItemNo varchar(20),@tBc varchar(20),@tBeginDate datetime
-- declare tmpCur1 cursor for
-- select EmpID,Bc,CardID, DispatchNo, MachineNo, ItemNo,BeginDate from #tmpCardDetail
--open tmpCur1
--fetch next from tmpCur1 into @tEmpID,@tBc,@tCardID,@tDispatchNo,@tMachineNo,@tItemNo,@tBeginDate
--while @@fetch_status=0
--begin
-- if exists(select * from MES_PayDetail where EmpID=@tEmpID and BC=@tBc and CardID=@tCardID
-- and DispatchNo=@tDispatchNo and MachineNo=@tMachineNo and ItemNO=@tItemNo
-- and CONVERT(varchar(20),BeginDate,120)=CONVERT(varchar(20),@tBeginDate,120))
-- begin
-- delete MES_PayDetail where EmpID=@tEmpID and BC=@tBc and CardID=@tCardID
-- and DispatchNo=@tDispatchNo and MachineNo=@tMachineNo and ItemNO=@tItemNo
-- and CONVERT(varchar(20),BeginDate,120)=CONVERT(varchar(20),@tBeginDate,120)
-- end
-- fetch next from tmpCur1 into @tEmpID,@tBc,@tCardID,@tDispatchNo,@tMachineNo,@tItemNo,@tBeginDate
--end
--deallocate tmpCur1
set @tran_error = @tran_error + @@error;
--UPDATE Mes_CardDetail SET StatusID = 1 WHERE CardType = 34 AND DispatchNo=@DispatchNo and DispatchPrior=@DispatchPrior AND StatusID = 0
-- AND (StartDate IS NOT NULL AND EndDate IS NOT NULL)
-- and ((convert(char(16),startdate,121)>=@BeginDate and convert(char(16),startdate,121)<=@EndDate )
-- or (convert(char(16),enddate,121) >=@BeginDate and convert(char(16),enddate,121) <=@EndDate))
-- set @tran_error = @tran_error + @@error;
END
ELSE IF (@charType= '2')
BEGIN
--=======植毛工资====
INSERT INTO MES_PayDetail(EmpName, CardID, DispatchNo, MachineNo, MouldNo, itemno, ProdQty, BeginDate, EndDate, MachineWage, CreateTime, WageFlag,EmpID)
select EmployeeName_CN,CardID,DispatchNo,MACHINENO,MOULDNO,itemno,Num,startDATE,ENDDATE
,CONVERT(DECIMAL(10,2),(Num/((3600/STANDCYCLE)*DiffHour*GoodSocketNum)*DiffHour)* 5.0) as MachineWage
,GetDate(), 1,EmpID
from (
select dbo.FN_GetEmpNameByID(CD.StartCardID) EmployeeName_CN,CD.StartCardID as CardID,EmpID,DO.DispatchNo,CI.MACHINENO, DO.MOULDNO, DO.itemno,
dbo.FN_GetProdQty(CD.MO, cd.DispatchNo, do.ItemNo, '',cd.DispatchPrior,CD.startDate,CD.EndDate) Num , CD.startDATE, CD.ENDDATE
,ISNULL (DO.StandCycle, 1) as StandCycle,DATEDIFF(s, CD.startdate, CD.ENDDATE)/3600.0 as DiffHour
,ISNULL ((select FieldValue from dbo.MES_ItemExt where ItemNO='OutNum'), 1) as GoodSocketNum
FROM Mes_CARDDETAIL CD
left join mes_DISPATCHORDER DO ON DO.DispatchNO = CD.DispatchNO
left join mes_Communication CI on CD.ClientIp=CI.IPAddr
left JOIN mes_item MD ON DO.ItemNo = MD.ItemNo
left join mes_employee e on e.EmpID=cd.StartEmpID
WHERE cd.DispatchNO=@DispatchNo and cd.DispatchPrior = @DispatchPrior AND CD.CardType =14 AND CD.STATUSID =0 and MD.ItemClsID=2
AND (cd.startDate IS NOT NULL AND cd.EndDate IS NOT NULL)
and ((convert(char(16),CD.Startdate,121)>=@BeginDate and convert(char(16),CD.startdate,121)<=@EndDate )
or (convert(char(16),CD.enddate,121) >=@BeginDate and convert(char(16),CD.enddate,121) <=@EndDate))
) a
set @tran_error = @tran_error + @@error;
UPDATE Mes_CardDetail SET StatusID= 1 WHERE CardType=14 and DispatchNo = @DispatchNo and DispatchPrior=@DispatchPrior AND StatusID = 0
AND (startDate IS NOT NULL AND EndDate IS NOT NULL)
and ((convert(char(16),startdate,121)>=@BeginDate and convert(char(16),startdate,121)<=@EndDate )
or (convert(char(16),enddate,121) >=@BeginDate and convert(char(16),enddate,121) <=@EndDate))
set @tran_error = @tran_error + @@error;
END
FETCH NEXT FROM CUR_GetDONo INTO @Mo,@DispatchNo,@DispatchPrior
END
--declare @tEmpID varchar(20),@tCardID varchar(20),@tDispatchNo varchar(50),@tMachineNo varchar(20),@tItemNo varchar(20),
--@tBc varchar(20),@tBeginDate datetime,@tNum int,@tBcBeginTime datetime,@tBcEndTime datetime,@tProcCode varchar(20),@tDispatchPrior float,@tMo varchar(20)
--declare tmpCur1 cursor for
-- select EmpID,Bc,CardID, DispatchNo, MachineNo, ItemNo,BeginDate,BcBeginTime,BcEndTime,ProcCode,DispatchPrior,MO from #tmpCardDetail
--open tmpCur1
--fetch next from tmpCur1 into @tEmpID,@tBc,@tCardID,@tDispatchNo,@tMachineNo,@tItemNo,@tBeginDate,@tBcBeginTime,@tBcEndTime,@tProcCode,@tDispatchPrior,@tMo
--while @@fetch_status=0
--begin
-- select @tNum=count(*) from (select distinct StartCardID from Mes_CardDetail cd1 where cd1.MachineNo=@tMachineNo and cd1.CardType in (33,34)
-- and convert(varchar(19),cd1.StartDate,120) between convert(varchar(19),@tBcBeginTime,120) and convert(varchar(19),@tBcEndTime,120)) a
-- update #tmpCardDetail set EmpNub=@tNum,ProdQty=dbo.FN_GetGoodQty(@tMo, @tDispatchNo,@tItemNo,@tProcCode,@tDispatchPrior, convert(varchar(19),@tBcBeginTime,120), convert(varchar(19),@tBcEndTime,120))
-- where MachineNo=@tMachineNo and DispatchNo=@tDispatchNo and BC=@tBc and EmpID=@tEmpID and ItemNO=@tItemNo
-- fetch next from tmpCur1 into @tEmpID,@tBc,@tCardID,@tDispatchNo,@tMachineNo,@tItemNo,@tBeginDate,@tBcBeginTime,@tBcEndTime,@tProcCode,@tDispatchPrior,@tMo
--end
--deallocate tmpCur1
--update #tmpCardDetail set MachineWage=isnull(((ProdQty*1.0)/(case when EmpNub=0 then 1 else EmpNub end))*Price,0.00)
--update #tmpCardDetail set Wage=MachineWage+TimeWage
--把游标处理的事情批量处理
UPDATE b SET b.ProdQty = dbo.FN_GetGoodQty(b.Mo, b.DispatchNo, b.ItemNo, b.ProcCode,
b.DispatchPrior,
CONVERT(VARCHAR(19), b.BcBeginTime, 120),
CONVERT(VARCHAR(19), b.BcEndTime, 120)) ,
EmpNub = ( SELECT COUNT(*)
FROM ( SELECT DISTINCT
cd1.StartCardID
FROM Mes_CardDetail cd1
WHERE cd1.MachineNo = b.MachineNo
and ((cd1.CardType=34 and cd1.StartDate >=dateadd(mi,-30,convert(datetime,b.BcBeginTime))
and cd1.StartDate<=b.BcEndTime
and EndDate>b.BcBeginTime and EndDate<dateadd(mi,30,convert(datetime,b.BcEndTime))))
) a
)
from #tmpCardDetail b
UPDATE #tmpCardDetail
SET MachineWage = ISNULL(((ProdQty * 1.0)/( CASE WHEN EmpNub = 0 THEN 1 ELSE EmpNub END)) * Price,0.00)
UPDATE #tmpCardDetail
SET Wage = MachineWage + TimeWage
--删除已计算过的员工工资
delete a from MES_PayDetail a inner join #tmpCardDetail b on a.EmpID=b.EmpID and a.BC=b.BC and a.CardID=b.CardID
and a.DispatchNo=b.DispatchNo and a.MachineNo=b.MachineNo and a.ItemNO=b.ItemNO
and CONVERT(varchar(20),a.BeginDate,120)=CONVERT(varchar(20),b.BeginDate,120)
--插入员工工资记录
INSERT INTO MES_PayDetail(EmpName,PostName,BCGroup,Bc,empid,CardID, DispatchNo, MachineNo, MouldNo, itemno,ItemName,
ProdQty, BeginDate, EndDate, MachineWage,TimeWage,StopTime, CreateTime, WageFlag,Proccode,stacode,Price,Wage,EmpNub,Mo,ArgProdQty,IsWork)
select EmpName,PostName,BCGroup,Bc,empid,CardID, DispatchNo, MachineNo, MouldNo, itemno,ItemName,
ProdQty, BeginDate, EndDate, MachineWage,TimeWage,StopTime, CreateTime, WageFlag,Proccode,stacode,Price,Wage,CASE WHEN EmpNub = 0 THEN 1 ELSE EmpNub END,MO,( ProdQty * 1.0 )/( CASE WHEN EmpNub = 0 THEN 1
ELSE EmpNub
END ),iswork from #tmpCardDetail
CLOSE CUR_GetDONo
DEALLOCATE CUR_GetDONo
--if @tran_error > 0
--begin
--rollback tran
--end
--else
--begin
--commit tran
--end
END
------------------------------------------------------------------------------
select * from MES_BCDetail
--1002540858 10025408580001
select top 100 * from Mes_CardDetail
where DispatchNo='10025408580001' and StartEmpID='851076'
order by CreateTime desc