127 lines
3.9 KiB
Transact-SQL
127 lines
3.9 KiB
Transact-SQL
IF OBJECT_ID('dbo.sp_CsengetesiRendOrakTorles') IS NOT NULL BEGIN
|
|
DROP PROCEDURE dbo.sp_CsengetesiRendOrakTorles
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_CsengetesiRendOrakTorles]
|
|
@TanevId int,
|
|
@xml xml
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @torlendoCsengetesiRendek TABLE (id int);
|
|
|
|
INSERT INTO @torlendoCsengetesiRendek
|
|
SELECT
|
|
sor.value('.', 'int') AS id
|
|
FROM @xml.nodes('/CsengetesiRendOraTorles/IdLista/Id') as sorok(sor)
|
|
|
|
SELECT
|
|
ID oraId,
|
|
csengrendOraId,
|
|
newCsengrendOraId,
|
|
newOraKezdete,
|
|
newOraVege,
|
|
ISNULL(newOraszam, 0) newOraszam,
|
|
newCsengrendId,
|
|
IsTanora,
|
|
UpdateVegeDate,
|
|
UpdateKezdeteDate,
|
|
UpdateType
|
|
INTO #TEMPmodositandoOrak
|
|
from (
|
|
select kapcsolodoOrak.ID,
|
|
kapcsolodoOrak.C_CSENGETESIRENDID,
|
|
kapcsolodoOrak.C_CSENGETESIRENDORAID csengrendOraId,
|
|
kapcsolodoOrak.C_ORASZAM,
|
|
alapcsora.C_KEZDETE,
|
|
alapcsora.C_VEGE,
|
|
kapcsolodoOrak.C_ORAKEZDETE,
|
|
kapcsolodoOrak.C_ORAVEGE,
|
|
kapcsolodoOrak.Tanora IsTanora,
|
|
csengrend.ID newCsengrendOraId,
|
|
csengrend.C_CSENGETESIRENDID newCsengrendId,
|
|
csengrend.C_KEZDETE newOraKezdete,
|
|
csengrend.C_VEGE newOraVege,
|
|
csengrend.C_ORASZAM newOraszam
|
|
,IIF(kapcsolodoOrak.Tanora = 0 and cast(alapcsora.C_VEGE as time) = cast(kapcsolodoOrak.C_ORAVEGE as time) and csengrend.C_VEGE is not null, 1, 0) UpdateVegeDate
|
|
,IIF(kapcsolodoOrak.Tanora = 0 and cast(alapcsora.C_KEZDETE as time) = cast(kapcsolodoOrak.C_ORAKEZDETE as time) and csengrend.C_KEZDETE is not null, 1, 0) UpdateKezdeteDate
|
|
,IIF(csengrend.ID is null, 1, 0) UpdateType
|
|
from (
|
|
select
|
|
x.ID,
|
|
C_CSENGETESIRENDID,
|
|
C_CSENGETESIRENDORAID,
|
|
C_ORASZAM,
|
|
C_ORAKEZDETE,
|
|
C_ORAVEGE,
|
|
Tanora
|
|
from(
|
|
select ID,
|
|
C_CSENGETESIRENDID,
|
|
C_CSENGETESIRENDORAID,
|
|
C_ORASZAM,
|
|
C_ORAKEZDETE,
|
|
C_ORAVEGE,
|
|
0 Tanora
|
|
from T_ORARENDIORA_OSSZES
|
|
union
|
|
select ID,
|
|
C_CSENGETESIRENDID,
|
|
C_CSENGETESIRENDORAID,
|
|
C_ORASZAM,
|
|
C_ORAKEZDETE,
|
|
C_ORAVEGE,
|
|
1 Tanora
|
|
from T_TANITASIORA_OSSZES
|
|
) x
|
|
inner join @torlendoCsengetesiRendek torlendoIdk on torlendoIdk.id = x.C_CSENGETESIRENDORAID
|
|
) kapcsolodoOrak
|
|
left join (
|
|
select
|
|
ID,
|
|
C_KEZDETE,
|
|
C_VEGE
|
|
from T_CSENGETESIRENDORA_OSSZES) alapcsora on alapcsora.ID = kapcsolodoOrak.C_CSENGETESIRENDORAID
|
|
left join (select csora.ID,
|
|
csora.C_CSENGETESIRENDID,
|
|
csora.C_KEZDETE,
|
|
csora.C_VEGE,
|
|
csora.C_ORASZAM
|
|
from T_CSENGETESIRENDORA_OSSZES csora
|
|
inner join T_CSENGETESIREND_OSSZES on csora.C_CSENGETESIRENDID = T_CSENGETESIREND_OSSZES.ID And T_CSENGETESIREND_OSSZES.C_AKTIV = 'T'
|
|
where csora.C_TANEVID = @TanevId)csengrend on csengrend.C_ORASZAM = kapcsolodoOrak.C_ORASZAM and csengrend.ID != kapcsolodoOrak.C_CSENGETESIRENDORAID
|
|
)t
|
|
|
|
UPDATE o
|
|
SET
|
|
o.C_ORAVEGE = IIF(temp.UpdateVegeDate = 1,
|
|
DATEADD(HOUR, datepart(hour, temp.newOraVege),DATEADD(MINUTE,datepart(minute, temp.newOraVege),CAST(CAST(o.C_ORAVEGE AS DATE) as datetime))),
|
|
o.C_ORAVEGE),
|
|
o.C_ORAKEZDETE = IIF(temp.UpdateKezdeteDate = 1,
|
|
DATEADD(HOUR, datepart(hour, temp.newOraVege),DATEADD(MINUTE,datepart(minute, temp.newOraVege),CAST(CAST(o.C_ORAKEZDETE AS DATE) as datetime))),
|
|
o.C_ORAKEZDETE),
|
|
o.C_CSENGETESIRENDID = temp.newCsengrendId,
|
|
o.C_CSENGETESIRENDORAID = temp.newCsengrendOraId,
|
|
o.C_ORASZAM = temp.newOraszam
|
|
from T_ORARENDIORA_OSSZES o
|
|
inner join #TEMPmodositandoOrak temp on temp.csengrendOraId = o.C_CSENGETESIRENDORAID and temp.IsTanora = 0
|
|
|
|
UPDATE t
|
|
SET
|
|
t.C_CSENGETESIRENDID = temp.newCsengrendId,
|
|
t.C_CSENGETESIRENDORAID = temp.newCsengrendOraId,
|
|
t.C_ORASZAM = temp.newOraszam
|
|
from T_TANITASIORA_OSSZES t
|
|
inner join #TEMPmodositandoOrak temp on temp.csengrendOraId = t.C_CSENGETESIRENDORAID and temp.IsTanora = 1
|
|
|
|
DELETE csro
|
|
FROM T_CSENGETESIRENDORA_OSSZES csro
|
|
inner join @torlendoCsengetesiRendek torlendoIdk on torlendoIdk.id = csro.ID
|
|
|
|
DROP TABLE #TEMPmodositandoOrak
|
|
|
|
END |