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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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+'<br />'
         set @result=@result+'- Keine Berufswahl mit Priorität 1'
      end
      if @counter>1 begin
         if @result<>'' set @result=@result+'<br />'
         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+'<br />'
         set @result=@result+'- Mehr als ' + ltrim(str(@anzberufe))+ ' Berufe wurden ausgewählt.'
      end
      if @counter<@anzBerufe begin
         if @result<>'' set @result=@result+'<br />'
         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:<br />'+@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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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

