 |
Приложение 11 – отчетные формы
|
|
|
|
CREATE FUNCTION PlanProverki (@DAT DATE)
RETURNS @Result TABLE (
NameWaterSource VARCHAR (10),
NameStreet VARCHAR(40),
Building VARCHAR(10),
NameDefect VARCHAR(100),
RepairTime INT,
DateInspection DATE)
AS
BEGIN
declare @IDDP TABLE (
IDWaterSource INT,
DateP DATE)
INSERT INTO @IDDP
SELECT
WS.IDWaterSource,
MAX(DateInspection) [DP]
FROM WaterSource WS INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
GROUP BY
WS.IDWaterSource
INSERT INTO @Result
SELECT
WS.NameWaterSource [Название],
S.NameStreet [Улица],
WS.Building [Дом],
D.NameDefect [Неисправность],
D.RepairTime [Время на устранение],
I.DateInspection [Датапроверки]
FROM WaterSource WS INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
INNER JOIN @IDDP IDDP
ON IDDP.IDWaterSource=WS.IDWaterSource
WHERE ((IDDP.DateP=I.DateInspection) AND (D.NameDefect='исправен') AND (DATEADD(dd,30,IDDP.DateP)<=@DAT))
INSERT INTO @Result
SELECT
WS.NameWaterSource [Название],
S.NameStreet [Улица],
WS.Building [Дом],
D.NameDefect [Неисправность],
D.RepairTime [Время на устранение],
I.DateInspection [Датапроверки]
FROM WaterSource WS INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
INNER JOIN @IDDP IDDP
ON IDDP.IDWaterSource=WS.IDWaterSource
INNER JOIN Letter L
ON L.IDLetter=TD.IDLetter
WHERE ((IDDP.DateP=I.DateInspection) AND (D.NameDefect!='исправен') AND (L.DateNextInspection<=@DAT))
RETURN;
END
CREATE PROC PPlanProverki(@Dat DATE)
AS
SELECT
NameWaterSource [Название],
NameStreet [Улица],
Building [Дом],
NameDefect [Неисправность],
RepairTime [Время на устранение],
DateInspection [Дата предыдущей проверки]
FROM PlanProverki(@Dat)
EXEC PPlanProverki '21-01-2012'
CREATE FUNCTION LetterToOrganization (@WS INT)
RETURNS
@Result TABLE (
NameWaterSource VARCHAR (10),
NameStreet VARCHAR(40),
Building VARCHAR(10),
NameDefect VARCHAR(100),
RepairTime INT,
DateInspection DATE)
AS
BEGIN
declare @temp1 TABLE (
IDD INT IDENTITY,
IDWaterSource INT,
DateP DATE,
IDDef INT,
Def VARCHAR(100))
INSERT INTO @temp1
SELECT
WS.IDWaterSource,
I.DateInspection,
D.IDDefect,
D.NameDefect
FROM WaterSource WS INNER JOIN Street S
|
|
|
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
WHERE WS.IDWaterSource=@WS
declare @temp2 TABLE (
IDD INT,
IDWaterSource INT,
DateP DATE,
IDDef INT,
Def VARCHAR(100))
INSERT INTO @temp2
SELECT top 2 *
FROM @temp1
order by IDD desc
IF ((SELECT DISTINCT COUNT (Def) FROM @temp2 WHERE Def!='исправен')=1)
BEGIN
INSERT INTO @Result
SELECT
WS.NameWaterSource [Название],
S.NameStreet [Улица],
WS.Building [Дом],
D.NameDefect [Неисправность],
D.RepairTime [Время на устранение],
I.DateInspection [Датапроверки]
FROM WaterSource WS INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
INNER JOIN @temp2 t2
ON t2.IDDef=D.IDDefect
WHERE D.IDDefect=(SELECT MAX(IDDef) FROM @temp2)
END;
RETURN;
END
CREATE PROC PLetterToOrganization(@WS INT)
AS
SELECT
NameWaterSource [Название],
NameStreet [Улица],
Building [Дом],
NameDefect [Неисправность],
RepairTime [Время на устранение],
DateInspection [Датапроверки]
FROM LetterToOrganization(@WS)
EXEC PLetterToOrganization 1
CREATE FUNCTION LetterToGPN (@WS INT)
RETURNS
@Result TABLE (
NameWaterSource VARCHAR (10),
NameStreet VARCHAR(40),
Building VARCHAR(10),
NameOrg VARCHAR(50))
AS
BEGIN
declare @temp1 TABLE (
IDD INT IDENTITY,
IDWaterSource INT,
DateP DATE,
IDDef INT,
Def VARCHAR(100))
INSERT INTO @temp1
SELECT
WS.IDWaterSource,
I.DateInspection,
D.IDDefect,
D.NameDefect
FROM WaterSource WS INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
WHERE WS.IDWaterSource=@WS
declare @temp2 TABLE (
IDD INT,
IDWaterSource INT,
DateP DATE,
IDDef INT,
Def VARCHAR(100))
INSERT INTO @temp2
SELECT top 2 *
FROM @temp1
order by IDD desc
IF ((SELECT DISTINCT COUNT (Def) FROM @temp2 WHERE Def!='исправен')>1)
BEGIN
INSERT INTO @Result
SELECT
WS.NameWaterSource [Название],
S.NameStreet [Улица],
WS.Building [Дом],
O.NameOrganization [Названиеорганизации]
FROM WaterSource WS INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON TD.IDInspection=I.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
INNER JOIN @temp2 t2
ON t2.IDDef=D.IDDefect
INNER JOIN Organization O
ON WS.IDOrganization=O.IDOrganization
WHERE D.IDDefect=(SELECT MAX(IDDef) FROM @temp2)
END;
RETURN;
END
CREATE PROC PLetterToGosPN(@WS INT)
AS
SELECT
NameWaterSource [Название],
NameStreet [Улица],
Building [Дом],
NameOrg [Название организации]
FROM LetterToGPN(@WS)
CREATE PROC PWSFind(@NStreet VARCHAR(40), @Build VARCHAR(10))
|
|
|
AS
SELECT
WS.IDWaterSource [ID],
NameWaterSource [Название],
NameStreet [Улица],
Building [Дом],
NameOrganization [Организация],
NameDefect [Неисправность],
DateInspection [Датапроверки]
FROM WaterSource WS INNER JOIN Hydrant H
ON WS.IDWaterSource=H.IDWaterSource
INNER JOIN Street S
ON WS.IDStreet=S.IDStreet
INNER JOIN Organization O
ON WS.IDOrganization=O.IDOrganization
INNER JOIN Inspection I
ON WS.IDWaterSource=I.IDWaterSource
INNER JOIN TestDefect TD
ON I.IDInspection=TD.IDInspection
INNER JOIN Defect D
ON D.IDDefect=TD.IDDefect
WHERE NameStreet LIKE @NStreet AND Building=@Build
EXEC PWSFind 'Стахановская','40'
|
|
|
