134 lines
3.6 KiB
Transact-SQL
134 lines
3.6 KiB
Transact-SQL
-- dbo.ep_Vyroba_ZapisDoPlanu
|
|
CREATE PROCEDURE dbo.ep_Vyroba_ZapisDoPlanu
|
|
@dilec INT=NULL,
|
|
@idZakazModif INT=NULL,
|
|
@datum NVARCHAR(15)=N'',
|
|
@hodinaOd TINYINT=0,
|
|
@pocetKs NUMERIC(19,6)=0.0,
|
|
@pocetKA NUMERIC(19,6)=0.0,
|
|
@pocetPal NUMERIC(19,6)=0.0,
|
|
@datumPlanKonec DATETIME=NULL OUT,
|
|
@errMsg NVARCHAR(500)=N'' OUT
|
|
AS
|
|
|
|
-- HD Consulting Strakonice, T. Buzin
|
|
-- !! proceduru neupravujte, bude stejne pregenerovana pri startu pluginu pro planovani
|
|
|
|
SET NOCOUNT ON
|
|
|
|
DECLARE
|
|
@idPlan INT,
|
|
@datumPlanStart DATETIME,
|
|
@planHodin NUMERIC(19,6),
|
|
@radaPlanu NVARCHAR(10),
|
|
@mjEvid NVARCHAR(10),
|
|
@mnoz NUMERIC(19,6),
|
|
@mnozKs NUMERIC(19,6)
|
|
|
|
DECLARE
|
|
@kodZakazModif NVARCHAR(20),
|
|
@idStroj INT,
|
|
@ksZaHod NUMERIC(19,6),
|
|
@ksVKA NUMERIC(19,6)
|
|
|
|
|
|
|
|
IF (@dilec IS NULL)
|
|
RETURN
|
|
|
|
SET @pocetKs = ISNULL(@pocetKs, 0)
|
|
SET @pocetKA = ISNULL(@pocetKA, 0)
|
|
SET @pocetPal = ISNULL(@pocetPal, 0)
|
|
|
|
IF (@pocetKs=0) AND (@pocetKA=0) AND (@pocetPal=0)
|
|
RETURN
|
|
|
|
BEGIN TRY
|
|
SET @datum = TRIM(ISNULL(@datum, N''))
|
|
SET @hodinaOd = ISNULL(@hodinaOd, 0)
|
|
IF (@datum=N'')
|
|
SET @datumPlanStart = GETDATE()
|
|
ELSE
|
|
SET @datumPlanStart = ISNULL( TRY_CONVERT(DATETIME, @datum), GETDATE())
|
|
SET @datumPlanStart = DATEADD(hour, @hodinaOd, dbo.hf_TruncDate(@datumPlanStart) )
|
|
END TRY
|
|
BEGIN CATCH
|
|
SET @datumPlanStart = DATEADD(hour, @hodinaOd, dbo.hf_TruncDate(GETDATE()) )
|
|
END CATCH
|
|
|
|
|
|
SET @idStroj = NULL
|
|
SET @ksZaHod = 0
|
|
SET @kodZakazModif = (SELECT Kod FROM dbo.TabZakazModif WHERE ID=@idZakazModif)
|
|
IF (@kodZakazModif IS NOT NULL)
|
|
BEGIN
|
|
SET @idStroj = (SELECT ID FROM dbo.TabCisStroju WHERE Kod=LEFT(@kodZakazModif,10) AND Blokovano=0)
|
|
IF (@idStroj IS NOT NULL)
|
|
SET @ksZaHod = ISNULL( (SELECT _KapacitaKsZaHod FROM dbo.TabCisStroju_EXT WHERE ID=@idStroj), 0)
|
|
END
|
|
|
|
|
|
|
|
SET @mnoz = 0
|
|
SET @mnozKs = 0
|
|
SET @ksVKA = 0
|
|
|
|
IF (@pocetKs>0)
|
|
SET @mnozKs = @pocetKs
|
|
|
|
SELECT @mjEvid=MJEvidence FROM dbo.TabKmenZbozi WHERE ID=@dilec
|
|
IF (@mjEvid=N'KA')
|
|
BEGIN
|
|
SET @ksVKA = ISNULL( (SELECT PocetOdvozene FROM dbo.TabMJZbozi WHERE IDKmenZbozi=@dilec AND LOWER(KodMJ1)=N'KA' AND LOWER(KodMJ2)=N'ks'), 1)
|
|
IF (@pocetKA>0)
|
|
BEGIN
|
|
SET @mnoz = @pocetKA
|
|
SET @mnozKs = @mnoz * @ksVKA
|
|
END
|
|
END
|
|
|
|
IF (@mnoz=0)
|
|
BEGIN
|
|
IF (@pocetKs>0) AND (@mjEvid=N'KA')
|
|
SET @mnoz = CEILING(@pocetKs / @ksVKA)
|
|
IF (@pocetPal>0) AND (@mjEvid=N'KA')
|
|
BEGIN
|
|
SET @mnoz = CEILING(@pocetPal * ISNULL( (SELECT PocetHlavni FROM dbo.TabMJZbozi WHERE IDKmenZbozi=@dilec AND LOWER(KodMJ1)=N'ka' AND LOWER(KodMJ2)=N'pal'), 0))
|
|
SET @mnozKs = @mnoz * @ksVKA
|
|
END
|
|
END -- mnoz=0, najdi prepocet ks a pal
|
|
|
|
|
|
IF (@datumPlanKonec IS NULL)
|
|
BEGIN
|
|
SET @planHodin = 0
|
|
IF (@ksZaHod<>0)
|
|
SET @planHodin = ROUND(@mnozKs/@ksZaHod, 2)
|
|
IF (@planHodin=0)
|
|
EXEC dbo.hp_GetPlanovanyTerminDokonceniVyroby @IDFinal=@dilec, @IDZakazModif=@idZakazModif, @Mnozstvi=@mnoz,
|
|
@DatumZahajeni=@datumPlanStart, @DatumUkonceni=@datumPlanKonec OUT
|
|
ELSE
|
|
SET @datumPlanKonec=DATEADD(minute, @planHodin*60.0, @datumPlanStart)
|
|
END
|
|
|
|
|
|
SET @radaPlanu=(SELECT ISNULL(pkz.RadaVyrPlanu,sz.ParD_RadaVyrPlanu) FROM dbo.TabKmenZbozi k
|
|
INNER JOIN dbo.TabParKmZ pkz ON (pkz.IDKmenZbozi=k.ID)
|
|
INNER JOIN dbo.TabSkupinyZbozi sz ON (sz.SkupZbo=k.SkupZbo)
|
|
WHERE k.ID=@dilec)
|
|
|
|
SET @idPlan=0
|
|
IF (@radaPlanu IS NOT NULL) AND (@mnoz>0)
|
|
BEGIN
|
|
BEGIN TRY
|
|
EXEC @idPlan=dbo.hp_NewVyrobniPlan @Rada=@radaPlanu, @IDDilce=@dilec, @IDZakazModif=@idZakazModif, @mnozstvi=@mnoz, @PlanUkonceni=@datumPlanKonec
|
|
END TRY
|
|
BEGIN CATCH
|
|
SET @errMsg=ERROR_MESSAGE()
|
|
SET @idPlan=0
|
|
END CATCH
|
|
END
|
|
|
|
RETURN @idPlan
|
|
|