USE [shub_bwpk] GO /****** Object: StoredProcedure [dbo].[Berufsliste] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Berufsliste]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[Berufsliste] GO /****** Object: StoredProcedure [dbo].[get_offene_plaetze] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_offene_plaetze]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[get_offene_plaetze] GO /****** Object: StoredProcedure [dbo].[nicht_zuget_berufe] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nicht_zuget_berufe]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[nicht_zuget_berufe] GO /****** Object: StoredProcedure [dbo].[sp_anmeldetalon] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_anmeldetalon]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_anmeldetalon] GO /****** Object: StoredProcedure [dbo].[sp_lehrerdaten] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_lehrerdaten]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_lehrerdaten] GO /****** Object: StoredProcedure [dbo].[verify_klasse] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[verify_klasse]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[verify_klasse] GO /****** Object: StoredProcedure [dbo].[verify_schueler] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[verify_schueler]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[verify_schueler] GO /****** Object: StoredProcedure [dbo].[zuteilen] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zuteilen]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[zuteilen] GO /****** Object: StoredProcedure [dbo].[zuteilung_durchfuehren] Script Date: 12/25/2012 14:35:14 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zuteilung_durchfuehren]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[zuteilung_durchfuehren] GO USE [shub_bwpk] GO /****** Object: StoredProcedure [dbo].[Berufsliste] Script Date: 12/25/2012 14:35:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[Berufsliste] AS BEGIN delete from tmpberufstatistik; insert into tmpberufstatistik (berufnr, klassentypnr, anzahl_angebot, Anzahl_Prio1, Anzahl_Prio2, Anzahl_Prio3, Anzahl_Prio4, Anzahl_Prio5) SELECT dbo.Beruf.Berufnr, dbo.FirmaBeruf.KlasseNr, SUM(dbo.FirmaBeruf.Anzahl),0,0,0,0,0 FROM dbo.Beruf INNER JOIN dbo.FirmaBeruf ON dbo.Beruf.Berufnr = dbo.FirmaBeruf.Berufnr WHERE (dbo.FirmaBeruf.Aktiv = 1) GROUP BY dbo.Beruf.Berufnr, dbo.Beruf.Bezeichnung, dbo.FirmaBeruf.KlasseNr update tmpberufstatistik set anzahl_angebot = 0 where anzahl_angebot is null; update TmpBerufStatistik set Anzahl_Prio1 = (SELECT COUNT(dbo.SchuelerBeruf.SchuelerBerufNr) FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr WHERE dbo.SchuelerBeruf.Prioritaet = 1 AND tmpberufstatistik.berufnr = schuelerberuf.berufnr and dbo.SchuelerBeruf.Aktiv = 1 AND dbo.Schueler.KlassenTyp = tmpberufstatistik.klassentypnr) update TmpBerufStatistik set Anzahl_Prio2 = (SELECT COUNT(dbo.SchuelerBeruf.SchuelerBerufNr) FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr WHERE dbo.SchuelerBeruf.Prioritaet = 2 AND tmpberufstatistik.berufnr = schuelerberuf.berufnr and dbo.SchuelerBeruf.Aktiv = 1 AND dbo.Schueler.KlassenTyp = tmpberufstatistik.klassentypnr) update TmpBerufStatistik set Anzahl_Prio3 = (SELECT COUNT(dbo.SchuelerBeruf.SchuelerBerufNr) FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr WHERE dbo.SchuelerBeruf.Prioritaet = 3 AND tmpberufstatistik.berufnr = schuelerberuf.berufnr and dbo.SchuelerBeruf.Aktiv = 1 AND dbo.Schueler.KlassenTyp = tmpberufstatistik.klassentypnr) update TmpBerufStatistik set Anzahl_Prio4 = (SELECT COUNT(dbo.SchuelerBeruf.SchuelerBerufNr) FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr WHERE dbo.SchuelerBeruf.Prioritaet = 4 AND tmpberufstatistik.berufnr = schuelerberuf.berufnr and dbo.SchuelerBeruf.Aktiv = 1 AND dbo.Schueler.KlassenTyp = tmpberufstatistik.klassentypnr) update TmpBerufStatistik set Anzahl_Prio4 = (SELECT COUNT(dbo.SchuelerBeruf.SchuelerBerufNr) FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr WHERE dbo.SchuelerBeruf.Prioritaet >4 AND tmpberufstatistik.berufnr = schuelerberuf.berufnr and dbo.SchuelerBeruf.Aktiv = 1 AND dbo.Schueler.KlassenTyp = tmpberufstatistik.klassentypnr) --update tmpberufstatistik set anzahl_prio1 = (select count(schuelerberuf.schuelerberufnr) --from schuelerberuf --where tmpberufstatistik.berufnr = schuelerberuf.berufnr and schuelerberuf.aktiv=1 and schuelerberuf.prioritaet=1); --update tmpberufstatistik set anzahl_prio2 = (select count(schuelerberuf.schuelerberufnr) --from schuelerberuf --where tmpberufstatistik.berufnr = schuelerberuf.berufnr and schuelerberuf.aktiv=1 and schuelerberuf.prioritaet=2); --update tmpberufstatistik set anzahl_prio3 = (select count(schuelerberuf.schuelerberufnr) --from schuelerberuf --where tmpberufstatistik.berufnr = schuelerberuf.berufnr and schuelerberuf.aktiv=1 and schuelerberuf.prioritaet=3); --update tmpberufstatistik set anzahl_prio4 = (select count(schuelerberuf.schuelerberufnr) --from schuelerberuf --where tmpberufstatistik.berufnr = schuelerberuf.berufnr and schuelerberuf.aktiv=1 and schuelerberuf.prioritaet=4); --update tmpberufstatistik set anzahl_prio5 = (select count(schuelerberuf.schuelerberufnr) --from schuelerberuf --where tmpberufstatistik.berufnr = schuelerberuf.berufnr and schuelerberuf.aktiv=1 and schuelerberuf.prioritaet>4); --SELECT -- TmpBerufStatistik.Berufnr, -- Beruf.Bezeichnung, -- TmpBerufStatistik.Anzahl_Angebot, -- TmpBerufStatistik.Anzahl_Prio1, -- TmpBerufStatistik.Anzahl_Prio2, -- TmpBerufStatistik.Anzahl_Prio3, -- TmpBerufStatistik.Anzahl_Prio4, -- TmpBerufStatistik.Anzahl_Prio5, -- (anzahl_prio2 + anzahl_prio3 + anzahl_prio4 + anzahl_prio5) AS Prio_2_bis_5, -- anzahl_angebot - (anzahl_prio1 + anzahl_prio2 + anzahl_prio3 + anzahl_prio4 + anzahl_prio5) AS Differenz --FROM -- TmpBerufStatistik -- INNER JOIN Beruf ON (TmpBerufStatistik.Berufnr = Beruf.Berufnr) --order by beruf.bezeichnung; --END declare @bnr int declare @bez varchar(255) declare @z int set @z=1 declare xc cursor for select distinct dbo.TmpBerufStatistik.berufnr, dbo.beruf.bezeichnung from tmpBerufstatistik INNER JOIN dbo.Beruf ON dbo.TmpBerufStatistik.Berufnr = dbo.Beruf.Berufnr order by dbo.beruf.bezeichnung open xc fetch next from xc into @bnr, @bez while @@FETCH_STATUS=0 begin update TmpBerufStatistik set nr=@z where berufnr=@bnr set @z=@z+1 fetch next from xc into @bnr, @bez end close xc deallocate xc SELECT TOP (100) PERCENT dbo.TmpBerufStatistik.nr, dbo.TmpBerufStatistik.Berufnr, dbo.Beruf.Bezeichnung, dbo.web_klasse.bezeichnung AS Expr1, dbo.TmpBerufStatistik.Anzahl_Angebot, dbo.TmpBerufStatistik.Anzahl_Prio1, dbo.TmpBerufStatistik.Anzahl_Prio2, dbo.TmpBerufStatistik.Anzahl_Prio3, dbo.TmpBerufStatistik.Anzahl_Prio4, dbo.TmpBerufStatistik.Anzahl_Prio5, dbo.TmpBerufStatistik.Anzahl_Prio2 + dbo.TmpBerufStatistik.Anzahl_Prio3 + dbo.TmpBerufStatistik.Anzahl_Prio4 + dbo.TmpBerufStatistik.Anzahl_Prio5 AS Prio_2_bis_5, dbo.TmpBerufStatistik.Anzahl_Angebot - (dbo.TmpBerufStatistik.Anzahl_Prio1 + dbo.TmpBerufStatistik.Anzahl_Prio2 + dbo.TmpBerufStatistik.Anzahl_Prio3 + dbo.TmpBerufStatistik.Anzahl_Prio4 + dbo.TmpBerufStatistik.Anzahl_Prio5) AS Differenz, dbo.web_klasse.klassenr FROM dbo.TmpBerufStatistik INNER JOIN dbo.Beruf ON dbo.TmpBerufStatistik.Berufnr = dbo.Beruf.Berufnr INNER JOIN dbo.web_klasse ON dbo.TmpBerufStatistik.klassentypnr = dbo.web_klasse.klassenr ORDER BY dbo.Beruf.Bezeichnung, dbo.web_klasse.klassenr end GO /****** Object: StoredProcedure [dbo].[get_offene_plaetze] Script Date: 12/25/2012 14:35:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[get_offene_plaetze] @berufnr int, @klassentyp int as BEGIN SELECT dbo.FirmaBeruf.FirmaBerufNr, dbo.Firma.FirmaNr, dbo.Firma.NameZ1, SUM(dbo.FirmaBeruf.Anzahl) AS Anzahl, dbo.Zeiten.Bezeichnung, dbo.FirmaBeruf.Berufnr, dbo.Zeiten.ZeitNr into #tmp1 FROM dbo.Firma INNER JOIN dbo.FirmaBeruf ON dbo.Firma.FirmaNr = dbo.FirmaBeruf.FirmaNr INNER JOIN dbo.Zeiten ON dbo.FirmaBeruf.ZeitNr = dbo.Zeiten.ZeitNr WHERE (dbo.Firma.Aktiv = 1) AND (dbo.FirmaBeruf.Aktiv = 1) and FirmaBeruf.Berufnr=@berufnr and dbo.FirmaBeruf.KlasseNr>=@klassentyp GROUP BY dbo.Firma.FirmaNr, dbo.Firma.NameZ1, dbo.Zeiten.Bezeichnung, dbo.FirmaBeruf.Berufnr, dbo.Zeiten.ZeitNr, dbo.firmaberuf.firmaberufnr declare @count int declare @fnr int declare @znr int declare xc cursor for select firmanr, zeitnr from #tmp1 open xc fetch next from xc into @fnr, @znr while @@FETCH_STATUS=0 begin SELECT @count=COUNT(dbo.Zuteilung.EintragNr) FROM dbo.Zuteilung INNER JOIN dbo.FirmaBeruf ON dbo.Zuteilung.FirmaBerufNr = dbo.FirmaBeruf.FirmaBerufNr WHERE (dbo.FirmaBeruf.FirmaNr = @fnr) AND (dbo.Zuteilung.Zeitnr = @znr) AND (dbo.Zuteilung.aktiv = 1) and dbo.firmaberuf.aktiv=1 update #tmp1 set anzahl=(anzahl-@count) where firmanr=@fnr and zeitnr=@znr fetch next from xc into @fnr, @znr end close xc deallocate xc select FirmaBerufNr, NameZ1, Bezeichnung as Zeit, Anzahl from #tmp1 where Anzahl> 0 drop table #tmp1 END GO /****** Object: StoredProcedure [dbo].[nicht_zuget_berufe] Script Date: 12/25/2012 14:35:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[nicht_zuget_berufe] AS BEGIN declare @anzahl_zuteilungen int select @anzahl_zuteilungen=inhalt from Optionen where Optionnr=9 --delete from Zuteilung where aktiv=0 SELECT dbo.Schueler.SchuelerNr, dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname AS Schueler, dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname AS Lehrer, dbo.Beruf.Bezeichnung AS Beruf, dbo.SchuelerBeruf.Prioritaet, 0 as Typ, SchuelerBeruf.SchuelerBerufNr, prio1=0 into #tmp1 FROM SchuelerBeruf INNER JOIN Schueler ON (SchuelerBeruf.SchuelerNr = Schueler.SchuelerNr) INNER JOIN Klasse ON (Schueler.KlasseNr = Klasse.KlasseNr) INNER JOIN Beruf ON (SchuelerBeruf.BerufNr = Beruf.Berufnr) INNER JOIN dbo.web_Lehrer ON dbo.Klasse.Lehrernr = dbo.web_Lehrer.LehrerNr WHERE schuelerberuf.aktiv = 1 AND beruf.aktiv = 1 AND schueler.aktiv = 1 AND schuelerberuf.SchuelerBerufNr NOT IN (SELECT zuteilung.schuelerberufnr FROM zuteilung where aktiv=1) GROUP BY dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname, dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname, dbo.Beruf.Bezeichnung, dbo.SchuelerBeruf.Prioritaet, dbo.Schueler.SchuelerNr ,SchuelerBeruf.SchuelerBerufNr Insert into #tmp1 SELECT dbo.Schueler.SchuelerNr, dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname AS Schueler, dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname AS Lehrer, dbo.Beruf.Bezeichnung AS Beruf, dbo.SchuelerBeruf.Prioritaet, 0 as Typ, SchuelerBeruf.SchuelerBerufNr,0 FROM SchuelerBeruf INNER JOIN Schueler ON (SchuelerBeruf.SchuelerNr = Schueler.SchuelerNr) INNER JOIN Klasse ON (Schueler.KlasseNr = Klasse.KlasseNr) INNER JOIN Beruf ON (SchuelerBeruf.BerufNr = Beruf.Berufnr) INNER JOIN dbo.web_Lehrer ON dbo.Klasse.Lehrernr = dbo.web_Lehrer.LehrerNr WHERE schuelerberuf.aktiv = 1 AND beruf.aktiv = 1 AND schueler.aktiv = 1 AND schuelerberuf.SchuelerBerufNr NOT IN (SELECT zuteilung.schuelerberufnr FROM zuteilung where aktiv=1) and SchuelerBeruf.SchuelerBerufNr not in (select SchuelerBerufNr from #tmp1) GROUP BY dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname, dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname, dbo.Beruf.Bezeichnung, dbo.SchuelerBeruf.Prioritaet, dbo.Schueler.SchuelerNr ,SchuelerBeruf.SchuelerBerufNr update #tmp1 set prio1=2 update #tmp1 set prio1=1 where prioritaet=1 SELECT count(Zuteilung.EintragNr) AS Counter, #tmp1.SchuelerNr, #tmp1.Schueler, #tmp1.Lehrer, #tmp1.Beruf, #tmp1.Prioritaet, #tmp1.typ, #tmp1.prio1 as Gruppe FROM SchuelerBeruf INNER JOIN Zuteilung ON (SchuelerBeruf.SchuelerBerufNr = Zuteilung.SchuelerBerufNr) INNER JOIN #tmp1 ON (SchuelerBeruf.SchuelerNr = #tmp1.SchuelerNr) where zuteilung.aktiv=1 GROUP BY #tmp1.SchuelerNr, #tmp1.Schueler, #tmp1.Lehrer, #tmp1.Beruf, #tmp1.Prioritaet, #tmp1.typ, #tmp1.prio1 HAVING count(Zuteilung.EintragNr) <> @anzahl_zuteilungen or #tmp1.Prioritaet=1 --SELECT dbo.Schueler.SchuelerNr, dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname AS Schueler, -- dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname AS Lehrer, dbo.Beruf.Bezeichnung AS Beruf, dbo.SchuelerBeruf.Prioritaet, 0 as Typ --into #tmp1 FROM dbo.SchuelerBeruf INNER JOIN -- dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr INNER JOIN -- dbo.Klasse ON dbo.Schueler.KlasseNr = dbo.Klasse.KlasseNr INNER JOIN -- dbo.Beruf ON dbo.SchuelerBeruf.BerufNr = dbo.Beruf.Berufnr INNER JOIN -- dbo.web_Lehrer ON dbo.Klasse.Lehrernr = dbo.web_Lehrer.LehrerNr --WHERE (dbo.SchuelerBeruf.Prioritaet = 1 and dbo.SchuelerBeruf.Aktiv = 1) AND (dbo.Beruf.Aktiv = 1) AND (dbo.Schueler.Aktiv = 1) AND (dbo.SchuelerBeruf.SchuelerBerufNr NOT IN -- (SELECT SchuelerBerufNr -- FROM dbo.Zuteilung where aktiv=1)) --GROUP BY dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname, dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname, dbo.Beruf.Bezeichnung, dbo.SchuelerBeruf.Prioritaet, -- dbo.Schueler.SchuelerNr --insert into #tmp1 --SELECT dbo.Schueler.SchuelerNr, dbo.Schueler.Name + ' ' + dbo.Schueler.Vorname AS Schueler, -- dbo.web_Lehrer.Name + ' ' + dbo.web_Lehrer.Vorname AS Lehrer, dbo.Beruf.Bezeichnung AS Beruf, dbo.SchuelerBeruf.Prioritaet, 1 as Typ -- FROM dbo.SchuelerBeruf INNER JOIN -- dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr INNER JOIN -- dbo.Klasse ON dbo.Schueler.KlasseNr = dbo.Klasse.KlasseNr INNER JOIN -- dbo.Beruf ON dbo.SchuelerBeruf.BerufNr = dbo.Beruf.Berufnr INNER JOIN -- dbo.web_Lehrer ON dbo.Klasse.Lehrernr = dbo.web_Lehrer.LehrerNr --WHERE (dbo.SchuelerBeruf.Prioritaet <> 1 and dbo.SchuelerBeruf.Aktiv = 1) AND (dbo.Beruf.Aktiv = 1) AND (dbo.Schueler.Aktiv = 1) AND (dbo.SchuelerBeruf.SchuelerBerufNr NOT IN -- (SELECT SchuelerBerufNr -- FROM dbo.Zuteilung where aktiv=1 )) and dbo.schuelerberuf.SchuelerNr not in (select SchuelerNr from #tmp1) --select * from #tmp1 order by typ, schueler --return --SELECT -- count(Zuteilung.EintragNr) AS Counter, -- #tmp1.SchuelerNr, -- #tmp1.Schueler, -- #tmp1.Lehrer, -- #tmp1.Beruf, -- #tmp1.Prioritaet --FROM -- SchuelerBeruf -- INNER JOIN Zuteilung ON (SchuelerBeruf.SchuelerBerufNr = Zuteilung.SchuelerBerufNr) -- INNER JOIN #tmp1 ON (SchuelerBeruf.SchuelerNr = #tmp1.SchuelerNr) -- --where zuteilung.aktiv=1 --GROUP BY -- #tmp1.SchuelerNr, -- #tmp1.Schueler, -- #tmp1.Lehrer, -- #tmp1.Beruf, -- #tmp1.Prioritaet --HAVING -- count(Zuteilung.EintragNr) <> 3; -- END drop table #tmp1 end GO /****** Object: StoredProcedure [dbo].[sp_anmeldetalon] Script Date: 12/25/2012 14:35:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[sp_anmeldetalon] AS BEGIN SELECT DISTINCT TOP (100) PERCENT dbo.Beruf.Berufnr, dbo.Beruf.Bezeichnung, dbo.Beruf.Anmerkung, dbo.Beruf.Lehrjahre, dbo.get_option(8) AS Textblock, MAX(dbo.FirmaBeruf.KlasseNr) AS Klassenr,' ' as Klassentyp into #tmp1 FROM dbo.FirmaBeruf INNER JOIN dbo.Beruf ON dbo.FirmaBeruf.Berufnr = dbo.Beruf.Berufnr GROUP BY dbo.Beruf.Berufnr, dbo.Beruf.Bezeichnung, dbo.Beruf.Anmerkung, dbo.Beruf.Lehrjahre declare @b int declare @k int declare @bez varchar(255) declare xc cursor for select berufnr, klassenr from #tmp1 open xc fetch next from xc into @b, @k while @@FETCH_STATUS=0 begin select @bez=bezeichnung from web_klasse where klassenr=@k update #tmp1 set klassentyp=@bez where berufnr=@b fetch next from xc into @b, @k end close xc deallocate xc select * from #tmp1 order by bezeichnung END GO /****** Object: StoredProcedure [dbo].[sp_lehrerdaten] Script Date: 12/25/2012 14:35:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[sp_lehrerdaten] @lehrernr int = 0 AS BEGIN if @lehrernr=0 begin SELECT TOP (100) PERCENT dbo.web_Lehrer.LehrerNr, dbo.web_Lehrer.Anrede, dbo.web_Lehrer.Name, dbo.web_Lehrer.Vorname, dbo.web_Lehrer.Telefon, dbo.web_Lehrer.email, dbo.web_Lehrer.natel, dbo.Schulhaus.SchulhausNr, dbo.Schulhaus.Bezeichnung, dbo.Schulhaus.Strasse, dbo.Schulhaus.PLZ, dbo.Schulhaus.Ort, dbo.Schulhaus.Telefon AS Schulhaustelefon, dbo.Klasse.KlasseNr, dbo.Klasse.Bezeichnung AS Klasse, dbo.Schueler.SchuelerNr, dbo.Schueler.Name AS Schuelername, dbo.Schueler.Vorname AS Schuelervorname, dbo.Schueler.Bemerkung, dbo.web_klasse.bezeichnung AS Schultyp, dbo.SchuelerBeruf.SchuelerBerufNr, dbo.SchuelerBeruf.SchuelerNr AS Schuelerberufschuelernr, dbo.Beruf.Bezeichnung AS Schuelerberuf, dbo.SchuelerBeruf.Prioritaet, dbo.SchuelerBeruf.Aktiv, dbo.SchuelerBeruf.Bemerkung AS Berufbemerkung into #tmp2 FROM dbo.web_klasse INNER JOIN dbo.web_Lehrer INNER JOIN dbo.web_user ON dbo.web_Lehrer.LehrerNr = dbo.web_user.Lehrernr INNER JOIN dbo.Schulhaus ON dbo.web_Lehrer.schulhausnr = dbo.Schulhaus.SchulhausNr INNER JOIN dbo.Klasse ON dbo.web_Lehrer.LehrerNr = dbo.Klasse.Lehrernr INNER JOIN dbo.Schueler ON dbo.Klasse.KlasseNr = dbo.Schueler.KlasseNr ON dbo.web_klasse.klassenr = dbo.Schueler.KlassenTyp LEFT OUTER JOIN dbo.Beruf INNER JOIN dbo.SchuelerBeruf ON dbo.Beruf.Berufnr = dbo.SchuelerBeruf.BerufNr ON dbo.Schueler.SchuelerNr = dbo.SchuelerBeruf.SchuelerNr WHERE (dbo.Schueler.Aktiv = 1) AND (dbo.SchuelerBeruf.Aktiv = 1) insert #tmp2 SELECT TOP (100) PERCENT dbo.web_Lehrer.LehrerNr, dbo.web_Lehrer.Anrede, dbo.web_Lehrer.Name, dbo.web_Lehrer.Vorname, dbo.web_Lehrer.Telefon, dbo.web_Lehrer.email, dbo.web_Lehrer.natel, dbo.Schulhaus.SchulhausNr, dbo.Schulhaus.Bezeichnung, dbo.Schulhaus.Strasse, dbo.Schulhaus.PLZ, dbo.Schulhaus.Ort, dbo.Schulhaus.Telefon AS Schulhaustelefon, dbo.Klasse.KlasseNr, dbo.Klasse.Bezeichnung AS Klasse, dbo.Schueler.SchuelerNr, dbo.Schueler.Name AS Schuelername, dbo.Schueler.Vorname AS Schuelervorname, dbo.Schueler.Bemerkung, dbo.web_klasse.bezeichnung AS Schultyp, 0 as SchuelerBerufNr, 0 as Schuelerberufschuelernr, '' as Schuelerberuf, 0 as Prioritaet, 1 as Aktiv, '' AS Berufbemerkung FROM dbo.web_klasse INNER JOIN dbo.web_Lehrer INNER JOIN dbo.web_user ON dbo.web_Lehrer.LehrerNr = dbo.web_user.Lehrernr INNER JOIN dbo.Schulhaus ON dbo.web_Lehrer.schulhausnr = dbo.Schulhaus.SchulhausNr INNER JOIN dbo.Klasse ON dbo.web_Lehrer.LehrerNr = dbo.Klasse.Lehrernr INNER JOIN dbo.Schueler ON dbo.Klasse.KlasseNr = dbo.Schueler.KlasseNr ON dbo.web_klasse.klassenr = dbo.Schueler.KlassenTyp WHERE (dbo.Schueler.Aktiv = 1) and dbo.Schueler.SchuelerNr not in (select SchuelerNr from #tmp2) select * from #tmp2 ORDER BY Klasse, Schuelername, Schuelervorname, Prioritaet drop table #tmp2 end else begin SELECT TOP (100) PERCENT dbo.web_Lehrer.LehrerNr, dbo.web_Lehrer.Anrede, dbo.web_Lehrer.Name, dbo.web_Lehrer.Vorname, dbo.web_Lehrer.Telefon, dbo.web_Lehrer.email, dbo.web_Lehrer.natel, dbo.Schulhaus.SchulhausNr, dbo.Schulhaus.Bezeichnung, dbo.Schulhaus.Strasse, dbo.Schulhaus.PLZ, dbo.Schulhaus.Ort, dbo.Schulhaus.Telefon AS Schulhaustelefon, dbo.Klasse.KlasseNr, dbo.Klasse.Bezeichnung AS Klasse, dbo.Schueler.SchuelerNr, dbo.Schueler.Name AS Schuelername, dbo.Schueler.Vorname AS Schuelervorname, dbo.Schueler.Bemerkung, dbo.web_klasse.bezeichnung AS Schultyp, dbo.SchuelerBeruf.SchuelerBerufNr, dbo.SchuelerBeruf.SchuelerNr AS Schuelerberufschuelernr, dbo.Beruf.Bezeichnung AS Schuelerberuf, dbo.SchuelerBeruf.Prioritaet, dbo.SchuelerBeruf.Aktiv, dbo.SchuelerBeruf.Bemerkung AS Berufbemerkung into #tmp1 FROM dbo.web_klasse INNER JOIN dbo.web_Lehrer INNER JOIN dbo.web_user ON dbo.web_Lehrer.LehrerNr = dbo.web_user.Lehrernr INNER JOIN dbo.Schulhaus ON dbo.web_Lehrer.schulhausnr = dbo.Schulhaus.SchulhausNr INNER JOIN dbo.Klasse ON dbo.web_Lehrer.LehrerNr = dbo.Klasse.Lehrernr INNER JOIN dbo.Schueler ON dbo.Klasse.KlasseNr = dbo.Schueler.KlasseNr ON dbo.web_klasse.klassenr = dbo.Schueler.KlassenTyp LEFT OUTER JOIN dbo.Beruf INNER JOIN dbo.SchuelerBeruf ON dbo.Beruf.Berufnr = dbo.SchuelerBeruf.BerufNr ON dbo.Schueler.SchuelerNr = dbo.SchuelerBeruf.SchuelerNr WHERE dbo.web_Lehrer.LehrerNr=@lehrernr and (dbo.Schueler.Aktiv = 1) AND (dbo.SchuelerBeruf.Aktiv = 1) insert #tmp1 SELECT TOP (100) PERCENT dbo.web_Lehrer.LehrerNr, dbo.web_Lehrer.Anrede, dbo.web_Lehrer.Name, dbo.web_Lehrer.Vorname, dbo.web_Lehrer.Telefon, dbo.web_Lehrer.email, dbo.web_Lehrer.natel, dbo.Schulhaus.SchulhausNr, dbo.Schulhaus.Bezeichnung, dbo.Schulhaus.Strasse, dbo.Schulhaus.PLZ, dbo.Schulhaus.Ort, dbo.Schulhaus.Telefon AS Schulhaustelefon, dbo.Klasse.KlasseNr, dbo.Klasse.Bezeichnung AS Klasse, dbo.Schueler.SchuelerNr, dbo.Schueler.Name AS Schuelername, dbo.Schueler.Vorname AS Schuelervorname, dbo.Schueler.Bemerkung, dbo.web_klasse.bezeichnung AS Schultyp, 0 as SchuelerBerufNr, 0 as Schuelerberufschuelernr, '' as Schuelerberuf, 0 as Prioritaet, 1 as Aktiv, '' AS Berufbemerkung FROM dbo.web_klasse INNER JOIN dbo.web_Lehrer INNER JOIN dbo.web_user ON dbo.web_Lehrer.LehrerNr = dbo.web_user.Lehrernr INNER JOIN dbo.Schulhaus ON dbo.web_Lehrer.schulhausnr = dbo.Schulhaus.SchulhausNr INNER JOIN dbo.Klasse ON dbo.web_Lehrer.LehrerNr = dbo.Klasse.Lehrernr INNER JOIN dbo.Schueler ON dbo.Klasse.KlasseNr = dbo.Schueler.KlasseNr ON dbo.web_klasse.klassenr = dbo.Schueler.KlassenTyp WHERE dbo.web_Lehrer.LehrerNr=@lehrernr and (dbo.Schueler.Aktiv = 1) and dbo.Schueler.SchuelerNr not in (select SchuelerNr from #tmp1) select * from #tmp1 ORDER BY Klasse, Schuelername, Schuelervorname, Prioritaet drop table #tmp1 end end GO /****** Object: StoredProcedure [dbo].[verify_klasse] Script Date: 12/25/2012 14:35:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[verify_klasse] @klassenr int AS BEGIN declare @c1 int declare @c2 int select @c1=klassengroesse from Klasse where KlasseNr = @klassenr select @c2=COUNT(KlasseNr) from Schueler where KlasseNr=@klassenr and Aktiv=1 if @c1 = @c2 select 'Klasse komplett erfasst' if @c1 < @c2 select 'Zu viele Schüler erfasst' if @c1 > @c2 select rtrim(ltrim(CONVERT(varchar,@c2))) + ' von '+ rtrim(ltrim(CONVERT(varchar,@c1)))+' Schüler erfasst' END GO /****** Object: StoredProcedure [dbo].[verify_schueler] Script Date: 12/25/2012 14:35:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[verify_schueler] @schuelernr int AS BEGIN declare @prio1 varchar(20) declare @anzBerufe int declare @counter int declare @result varchar(1048) select @prio1=inhalt from optionen where optionnr=5 select @anzBerufe=inhalt from Optionen where Optionnr=4 set @result='' if @prio1='Ja' begin select @counter=COUNT(*) from SchuelerBeruf where Aktiv=1 and Prioritaet=1 and schuelernr=@schuelernr if @counter<1 begin if @result<>'' set @result=@result+'
' set @result=@result+'- Keine Berufswahl mit Priorität 1' end if @counter>1 begin if @result<>'' set @result=@result+'
' set @result=@result+'- Mehrere Berufe mit Priorität 1' end end print @anzberufe if @anzBerufe>0 begin select @counter=COUNT(berufnr) from SchuelerBeruf where Aktiv=1 and SchuelerNr=@schuelernr if @counter>@anzBerufe begin if @result<>'' set @result=@result+'
' set @result=@result+'- Mehr als ' + ltrim(str(@anzberufe))+ ' Berufe wurden ausgewählt.' end if @counter<@anzBerufe begin if @result<>'' set @result=@result+'
' set @result=@result+'- Weniger als ' + ltrim(str(@anzberufe))+ ' unterschiedliche Berufe wurden ausgewählt.' end end if @result='' set @result='Schüler vollständig erfasst.' else set @result='Folgende Fehler sind vorhanden:
'+@result select @result as resultat END GO /****** Object: StoredProcedure [dbo].[zuteilen] Script Date: 12/25/2012 14:35:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[zuteilen] @ischuelerberufnr int, @ischuelernr int, @isberufnr int,@ischuelerklassenr int, @iprioritaet int, @izugeteilt int AS BEGIN declare @firmaberufnr int, @zeitnr int, @ansprechpartnernr int declare @rc int declare @i int declare @doit int declare @anzZuteilungen int select @anzZuteilungen=inhalt from Optionen where Optionnr=9 set @i=0 set @doit=0 declare xc cursor for select firmaberufnr, zeitnr, Ansprechpartnernr from #firma where berufnr=@isberufnr and zugeteilt < anzahl and klassenr >= @ischuelerklassenr open xc fetch next from xc into @firmaberufnr, @zeitnr, @ansprechpartnernr while @@FETCH_STATUS=0 begin set @i=@i+1 select @rc=COUNT(*) from Zuteilung where SchuelerNr=@ischuelernr and Zeitnr=@zeitnr if @rc=0 begin set @doit=1 break end fetch next from xc into @firmaberufnr, @zeitnr, @ansprechpartnernr end close xc deallocate xc if @i=0 return if @doit=0 return select @rc=COUNT(*) from Zuteilung where SchuelerNr=@ischuelernr if @rc>@anzZuteilungen return insert Zuteilung(FirmaBerufNr, SchuelerBerufNr,SchuelerNr,Zeitnr, Ansprechpartnernr,Prioritaet, aktiv, erstellt_am, mutiert_am,Bemerkung) values (@firmaberufnr,@ischuelerberufnr,@ischuelernr,@zeitnr,@ansprechpartnernr, @iprioritaet, 1,GETDATE(),GETDATE(),'') update #firma set zugeteilt=zugeteilt+1 where firmaberufnr=@firmaberufnr END GO /****** Object: StoredProcedure [dbo].[zuteilung_durchfuehren] Script Date: 12/25/2012 14:35:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[zuteilung_durchfuehren] @typ int=0 AS BEGIN -- Zuteilungen löschen truncate table zuteilung -- TempSchuler CREATE TABLE #Schueler( schuelerberufnr int, schuelernr int, berufnr int, Prioritaet int, Klassenr int, Zugeteilt int) ON [default] CREATE TABLE #Firma( Firmaberufnr int, Berufnr int, Zeitnr int, Klassenr int, Ansprechpartnernr int, Zugeteilt int, Anzahl int) ON [default] declare @firmaberufnr int, @berufnr int, @zeitnr int, @klassenr int, @zuget int declare @schuelerberufnr int, @schuelernr int, @sberufnr int, @schuelerklassenr int, @prioritaet int, @zugeteilt int insert into #Firma select firmaberufnr, berufnr, zeitnr, klassenr, Ansprechpartnernr, 0 as Zugegeilt, Anzahl from firmaberuf where aktiv=1 order by berufnr, zeitnr, firmanr if @typ=0 begin insert into #schueler SELECT dbo.SchuelerBeruf.SchuelerBerufNr, dbo.SchuelerBeruf.SchuelerNr, dbo.SchuelerBeruf.BerufNr, dbo.SchuelerBeruf.Prioritaet, dbo.Schueler.KlassenTyp, 0 FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr where SchuelerBeruf.Aktiv=1 and Schueler.Aktiv=1 and prioritaet=1 order by berufnr insert into #schueler SELECT dbo.SchuelerBeruf.SchuelerBerufNr, dbo.SchuelerBeruf.SchuelerNr, dbo.SchuelerBeruf.BerufNr, dbo.SchuelerBeruf.Prioritaet, dbo.Schueler.KlassenTyp, 0 FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr where SchuelerBeruf.Aktiv=1 and Schueler.Aktiv=1 and prioritaet<>1 order by NEWID() end else begin insert into #schueler SELECT dbo.SchuelerBeruf.SchuelerBerufNr, dbo.SchuelerBeruf.SchuelerNr, dbo.SchuelerBeruf.BerufNr, dbo.SchuelerBeruf.Prioritaet, dbo.Schueler.KlassenTyp, 0 FROM dbo.SchuelerBeruf INNER JOIN dbo.Schueler ON dbo.SchuelerBeruf.SchuelerNr = dbo.Schueler.SchuelerNr where SchuelerBeruf.Aktiv=1 and Schueler.Aktiv=1 order by prioritaet, berufnr end -- Prio 1 abarbeiten declare cschuler cursor for select schuelerberufnr, schuelernr, berufnr, klassenr, prioritaet, zugeteilt from #schueler where prioritaet=1 open cschuler fetch next from cschuler into @schuelerberufnr, @schuelernr, @sberufnr,@schuelerklassenr, @prioritaet, @zugeteilt while @@FETCH_STATUS=0 begin print @schuelerberufnr exec dbo.zuteilen @schuelerberufnr, @schuelernr, @sberufnr,@schuelerklassenr, @prioritaet, @zugeteilt fetch next from cschuler into @schuelerberufnr, @schuelernr, @sberufnr,@schuelerklassenr, @prioritaet, @zugeteilt end close cschuler deallocate cschuler -- Prio <> -1 abarbeiten declare cschuler cursor for select schuelerberufnr, schuelernr, berufnr, klassenr, prioritaet, zugeteilt from #schueler where prioritaet<>1 open cschuler fetch next from cschuler into @schuelerberufnr, @schuelernr, @sberufnr,@schuelerklassenr, @prioritaet, @zugeteilt while @@FETCH_STATUS=0 begin exec dbo.zuteilen @schuelerberufnr, @schuelernr, @sberufnr,@schuelerklassenr, @prioritaet, @zugeteilt fetch next from cschuler into @schuelerberufnr, @schuelernr, @sberufnr,@schuelerklassenr, @prioritaet, @zugeteilt end close cschuler deallocate cschuler --declare xc cursor for -- select firmaberufnr, berufnr, zeitnr, klassenr from #firma order by berufnr, zeitnr, firmanr --open xc --fetch next from xc into @firmaberufnr, @berufnr, @zeitnr, @klassenr, @zuget --while @@FETCH_STATUS=0 begin -- fetch next from xc into @firmaberufnr, @berufnr, @zeitnr, @klassenr,@zuget --end --close xc --deallocate xc drop table #firma drop table #schueler END GO