--创建时间2006-08-03
--创建人 csj
slope查询
--根据slope查询页面录入的查询条件,获得满足该条件的记录
/*
[0]: "R-RMI-ST-30000C00010-20060928"
[1]: ""
[2]: ""
[3]: ""
[4]: ""
[5]: ""
[6]: "-1"
[7]: "-1"
[8]: ""
[9]: ""
select * from inspection where insp_no ='R-RMI-ST-30000C00010-20070904'
select * from inspection where insp_no like 'R-RMI-ST-30000C00010-%'
*/
ALTER PROCEDURE prc_SearchSlope
@INSP_NO VARCHAR(50)='R-RMI-ST-30000C00010-20061114',
@INSP_DATE_FROM VARCHAR(25)='',
@INSP_DATE_TO VARCHAR(25)='',
@SUB_DATE_FROM VARCHAR(25)='',
@SUB_DATE_TO VARCHAR(25)='',
@LOCATION VARCHAR(255)='',
@HYD_REG_NO VARCHAR(255)='',
@AI_ID VARCHAR(10)='',--界面上的ITEM
@ACTION_CAT CHAR(1)='',
--@CTL_NO VARCHAR(10)='',
@SUBMIT VARCHAR(1)=''
AS
DECLARE @SQL VARCHAR(4000)
DECLARE @DEF VARCHAR(1000)
DECLARE @ISEXIST VARCHAR(1)
SET @ISEXIST = 'N'
SET @DEF = 'INNER JOIN (SELECT DISTINCT INSP_NO FROM View_S_DEFECT_Auto WHERE 1=1 '
SET @SQL = 'SELECT
A.INSP_NO,
D.DISTRICT,
E.HYD_NO AS ''HYD_REG_NO'',
E.SLOPE_NO,
E.LOCATION,
A.INSPECTION_DATE,
A.SSA_SUB_DATE AS ''SUB_DATE'',
F.INSPECTOR_NAME,
C.WEATHER,
sde.GetDefectCount(A.INSP_NO,''View_S_DEFECT_Auto'') AS DEFECTS FROM INSPECTION A JOIN SI B ON A.INSP_NO = B.INSP_NO
LEFT OUTER JOIN ( SELECT FIELDVALUE_ID,TABLE_FIELDVALUE AS ''WEATHER'' FROM EMMSCODE WHERE EMMSTABLENAME = ''WEATHER_ALL'') C ON A.WEATHER = C.FIELDVALUE_ID
LEFT OUTER JOIN ( SELECT FIELDVALUE_ID,TABLE_FIELDVALUE AS ''DISTRICT'' FROM EMMSCODE WHERE EMMSTABLENAME = ''DIS_ALLTABLE'') D ON A.DISTRICT_ID = D.FIELDVALUE_ID
LEFT OUTER JOIN SLOPE_NO E ON B.HYD_REG_NO = E.HYD_NO
LEFT OUTER JOIN INSPECTOR F ON A.INSPECTED_BY = F.INSPECTOR_ID '
IF(@AI_ID IS NOT NULL AND @AI_ID <> '')
BEGIN
SET @ISEXIST = 'Y'
SET @DEF = @DEF + ' AND AI_ID ='+@AI_ID+''
END
IF(@ACTION_CAT IS NOT NULL AND @ACTION_CAT <> '' )
BEGIN
SET @ISEXIST = 'Y'
SET @DEF = @DEF + ' AND ACTION_CAT = '''+@ACTION_CAT+''''
END
SET @DEF = @DEF + ') CC ON CC.INSP_NO = A.INSP_NO '
IF(@ISEXIST = 'Y')
SET @SQL=@SQL + @DEF
SET @SQL = @SQL + ' WHERE 1=1 '
IF(@INSP_NO is not null and @INSP_NO <> '')
set @SQL = @SQL + ' AND A.INSP_NO LIKE '''+@INSP_NO+'%'''
IF(@INSP_DATE_FROM IS NOT NULL AND @INSP_DATE_FROM <> '')
SET @SQL = @SQL + ' AND DATEDIFF(DAY,A.INSPECTION_DATE,'''+@INSP_DATE_FROM+''')<=0'
IF(@INSP_DATE_TO IS NOT NULL AND @INSP_DATE_TO<>'')
SET @SQL = @SQL + ' AND DATEDIFF(DAY,A.INSPECTION_DATE,'''+@INSP_DATE_TO+''')>=0'
IF(@SUB_DATE_FROM IS NOT NULL AND @SUB_DATE_FROM <> '')
SET @SQL = @SQL + ' AND DATEDIFF(DAY,A.SSA_SUB_DATE,'''+@SUB_DATE_FROM+''')<=0'
IF(@SUB_DATE_TO IS NOT NULL AND @SUB_DATE_TO<>'')
SET @SQL = @SQL + ' AND DATEDIFF(DAY,A.SSA_SUB_DATE,'''+@SUB_DATE_TO+''')>=0'
IF(@LOCATION IS NOT NULL AND @LOCATION <> '')
SET @SQL = @SQL + ' AND A.LOCATION LIKE ''%'+@LOCATION+'%'''
IF(@HYD_REG_NO IS NOT NULL AND @HYD_REG_NO <> '')
SET @SQL = @SQL + ' AND B.HYD_REG_NO ='''+@HYD_REG_NO+''''
--IF(@CTL_NO IS NOT NULL AND @CTL_NO<>'')
-- SET @SQL=@SQL +' AND E.CTL_NO='+@CTL_NO
IF(@SUBMIT = '1')
SET @SQL = @SQL + ' AND A.SSA_SUB_DATE IS NOT NULL AND A.SSA_SUB_DATE <> '''' '
--PRINT (@SQL)
EXEC (@SQL)
--select TABLE_FIELDVALUE FROM EMMSCODE WHERE EMMSTABLENAME = 'DISTRICT'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO