if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_getFullPaper_Car]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_getFullPaper_Car]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_getFullPaper_Car2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_getFullPaper_Car2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_getFullPaper_Sar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_getFullPaper_Sar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_getFullPaper_Sar2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_getFullPaper_Sar2]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE [dbo].[usp_getFullPaper_Car] 
	@EduYear smallint,
	@UnivID varchar(5),
	@FacID varchar(5),
	@ObjID int

AS

BEGIN

--FIXED @ObjID
DECLARE @NewObjID int
SET @NewObjID = (SELECT TOP 1 ObjID FROM CHEQA_Objective WHERE UnivID=@UnivID AND FacID=@FacID AND EduYear=@EduYear AND ObjIndex=@ObjID)
IF NOT @NewObjID IS NULL
	SET @ObjID = @NewObjID
--------------------------Table ข้อมูลองค์ประกอบ ตัวบ่งชี้ เป้าหมาย คะแนนประเมิน
SELECT   dbo.CHEQA_Objective.ObjIndex, dbo.CHEQA_Objective.ObjName, dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_KPI.KpiName, 
                      dbo.CHEQA_SAR.CarKpiTarget AS KpiTarget, dbo.CHEQA_SAR.CarKpiNextTarget AS KpiNextTarget, ISNULL(dbo.CHEQA_SAR.KpiScore, 0) AS SarScore, 
                      dbo.CHEQA_SAR.AfterFormulaBase AS FormulaBase, dbo.CHEQA_SAR.AfterFormulaDiv AS FormulaDiv, dbo.CHEQA_SAR.KpiValues AS KpiValues, 
                      CASE WHEN IsNull(IsPass, 0) = 0 THEN 'ไม่บรรลุ' WHEN IsPass = 1 THEN 'บรรลุ' END AS IsPass, dbo.CHEQA_KPI.KpiScore, 
                      dbo.CHEQA_KpiType.KpiTypeName, 
                      dbo.CHEQA_SAR.CarScore, dbo.CHEQA_SAR.CarComment, dbo.CHEQA_SAR.AfterKpiValues AS CarResult
FROM         dbo.CHEQA_KPI INNER JOIN
                      dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID INNER JOIN
                      dbo.CHEQA_Objective ON dbo.CHEQA_KPI.ObjID = dbo.CHEQA_Objective.ObjID INNER JOIN
                      dbo.CHEQA_SAR ON dbo.CHEQA_KpiSetting.KpiID = dbo.CHEQA_SAR.KpiID INNER JOIN
                      dbo.CHEQA_KpiType ON dbo.CHEQA_KPI.KpiTypeID = dbo.CHEQA_KpiType.KpiTypeID
WHERE     (dbo.CHEQA_SAR.UnivID = @UnivID) AND (dbo.CHEQA_SAR.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND 
                      (dbo.CHEQA_KpiSetting.UnivID = @UnivID) AND (dbo.CHEQA_KpiSetting.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.ObjID = @ObjID) AND 
                      (dbo.CHEQA_SAR.EduYear = @EduYear) AND (dbo.CHEQA_KPI.KpiID IN
                          (SELECT     KpiID
                            FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                            WHERE      (EduYear = @EduYear) AND (UnivID = 00000)))
ORDER BY CAST(LEFT(dbo.CHEQA_KPI.KpiIndex, CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex) - 1) AS float), CAST(RIGHT(dbo.CHEQA_KPI.KpiIndex, 
                      LEN(dbo.CHEQA_KPI.KpiIndex) - CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex)) AS float)

-------Table เกณฑ์มาตรฐาน
SELECT   dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_CommonDataSet.IndexID, dbo.CHEQA_CommonDataSet.CdsName, 
                      dbo.CHEQA_CommonDataSet.CdsType
FROM         dbo.CHEQA_KPI INNER JOIN
                      dbo.CHEQA_KPIAssess ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KPIAssess.KpiID INNER JOIN
                      dbo.CHEQA_CommonDataSet ON dbo.CHEQA_KPIAssess.CdsID = dbo.CHEQA_CommonDataSet.CdsID INNER JOIN
                      dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID
WHERE     (dbo.CHEQA_KPI.KpiID IN
                          (SELECT     KpiID
                            FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                            WHERE      (EduYear = @EduYear) AND (UnivID = 00000))) AND (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND (dbo.CHEQA_KpiSetting.UnivID = @UnivID) 
                      AND (dbo.CHEQA_KpiSetting.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.ObjID = @ObjID) AND (CHEQA_KPIAssess.EduYear=@EduYear) AND (CHEQA_CommonDataSet.EduYear = @EduYear)
ORDER BY CAST(LEFT(dbo.CHEQA_KPI.KpiIndex, CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex) - 1) AS float), CAST(RIGHT(dbo.CHEQA_KPI.KpiIndex, 
                      LEN(dbo.CHEQA_KPI.KpiIndex) - CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex)) AS float), dbo.CHEQA_CommonDataSet.IndexID 


---------Table ผลดำเนินงานและหลักฐานรายตัวบ่งชี้
SELECT      dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_KpiAssessResult.ItemResult, dbo.CHEQA_KpiAssessResult.ItemReff, 
                      dbo.CHEQA_KpiAssessResult.CdsID
FROM         dbo.CHEQA_KPI INNER JOIN
                      dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID INNER JOIN
                      dbo.CHEQA_KpiAssessResult ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiAssessResult.KpiID
WHERE     (dbo.CHEQA_KPI.KpiID IN
                          (SELECT     KpiID
                            FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                            WHERE      (EduYear = @EduYear) AND (UnivID = 00000))) AND (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND 
                      (dbo.CHEQA_KpiSetting.UnivID = @UnivID) AND (dbo.CHEQA_KpiSetting.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.ObjID = @ObjID) AND 
                      (dbo.CHEQA_KpiAssessResult.EduYear = @EduYear) AND (dbo.CHEQA_KpiAssessResult.UnivID = @UnivID) AND 
                      (dbo.CHEQA_KpiAssessResult.FacID = @FacID) AND (dbo.CHEQA_KpiAssessResult.CdsID = 0 OR
                      dbo.CHEQA_KpiAssessResult.CdsID IN
                          (SELECT     CdsID
                            FROM          dbo.CHEQA_CommonDataSetActivity
                            WHERE      (EduYear = @Eduyear) AND (UnivID = @UnivID) AND (FacID = @FacID) AND (AfterCdsValues = 1)))
ORDER BY CAST(LEFT(dbo.CHEQA_KPI.KpiIndex, CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex) - 1) AS float), CAST(RIGHT(dbo.CHEQA_KPI.KpiIndex, 
                      LEN(dbo.CHEQA_KPI.KpiIndex) - CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex)) AS float) ,dbo.CHEQA_KpiAssessResult.CdsID
---------Table ผลการประเมินรายองค์ประกอบ
SELECT     CarStrength, CarWeakness, CarComment
FROM         dbo.CHEQA_SarReport
WHERE     (Eduyear = @EduYear) AND (UnivID = @UnivID) AND (FacID = @FacID) AND (ObjID = @ObjID)
END










GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE  PROCEDURE [dbo].[usp_getFullPaper_Car2] 
	@EduYear smallint,
	@UnivID varchar(5),
	@FacID varchar(5),
	@ObjID int

AS
--FIXED @ObjID
DECLARE @NewObjID int
SET @NewObjID = (SELECT TOP 1 ObjID FROM CHEQA_Objective WHERE UnivID=@UnivID AND FacID=@FacID AND EduYear=@EduYear AND ObjIndex=@ObjID)
IF NOT @NewObjID IS NULL
	SET @ObjID = @NewObjID
---------Table สรุปผู้บริหาร appendixb
SELECT     SelfOverAll
FROM         CHEQA_Overall
WHERE     (EduYear = @EduYear) AND (UnivID = @UnivID) AND 
                      (FacID = @FacID)

---------Table สรุป summary
--SELECT     CarObjResult, CarStdResult, CarViewReult,CarBestPractice
SELECT SarResultAll
FROM         CHEQA_Overall
WHERE     (EduYear = @EduYear) AND (UnivID = @UnivID) AND 
                      (FacID = @FacID)



------------Table  ภาคผนวกคณะกรรมการประเมินคุณภาพการศึกษาภายใน

SELECT     CHEQA_Objective.ObjIndex, CHEQA_SarReport.CarStrength, CHEQA_SarReport.CarWeakness, CHEQA_SarReport.CarComment, 
                      CHEQA_Overall.SelfOverAll, CHEQA_Overall.CommiteeOverAll, CHEQA_Overall.Assessor, CHEQA_Overall.CarIntro, CHEQA_Overall.CarObjResult, 
                      CHEQA_Overall.CarStdResult, CHEQA_Overall.CarViewReult, CHEQA_Overall.CarBestPractice, CHEQA_Overall.Method1, 
                      CHEQA_Overall.Method2
FROM         CHEQA_SarReport INNER JOIN
                      CHEQA_Overall ON CHEQA_SarReport.Eduyear = CHEQA_Overall.EduYear AND CHEQA_SarReport.UnivID = CHEQA_Overall.UnivID AND 
                      CHEQA_SarReport.FacID = CHEQA_Overall.FacID INNER JOIN
                      CHEQA_Objective ON CHEQA_SarReport.ObjID = CHEQA_Objective.ObjID
WHERE     (CHEQA_Overall.EduYear = @EduYear) AND (CHEQA_Overall.UnivID = @UnivID) AND (CHEQA_Overall.FacID =@FacID)
ORDER BY CHEQA_Objective.ObjIndex

---------Table ผลการประเมินรายองค์ประกอบ
SELECT     CarStrength, CarWeakness, CarComment
FROM         CHEQA_SarReport
WHERE     (EduYear = @EduYear) AND (UnivID = @UnivID) AND 
                      (FacID = @FacID) AND (ObjID=@ObjID)








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE  PROCEDURE [dbo].[usp_getFullPaper_Sar]
	@EduYear smallint,
	@UnivID varchar(5),
	@FacID varchar(5),
	@ObjID int
AS
BEGIN 
--FIXED @ObjID
DECLARE @NewObjID int
SET @NewObjID = (SELECT TOP 1 ObjID FROM CHEQA_Objective WHERE UnivID=@UnivID AND FacID=@FacID AND EduYear=@EduYear AND ObjIndex=@ObjID)
IF NOT @NewObjID IS NULL
	SET @ObjID = @NewObjID


--------------------------Table ข้อมูลองค์ประกอบ ตัวบ่งชี้ เป้าหมาย คะแนนประเมิน
SELECT     dbo.CHEQA_Objective.ObjIndex, dbo.CHEQA_Objective.ObjName, dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_KPI.KpiName, 
                      dbo.CHEQA_SAR.KpiTarget, dbo.CHEQA_SAR.KpiNextTarget, ISNULL(dbo.CHEQA_SAR.KpiScore, 0) AS SarScore, dbo.CHEQA_SAR.FormulaBase, 
                      dbo.CHEQA_SAR.FormulaDiv, dbo.CHEQA_SAR.KpiValues, CASE WHEN IsNull(Ispass, 0) 
                      = 0 THEN 'ไม่บรรลุ' WHEN IsPass = 1 THEN 'บรรลุ' END AS IsPass, dbo.CHEQA_KPI.KpiScore, dbo.CHEQA_KpiType.KpiTypeName, 
                      dbo.CHEQA_SAR.CarScore, dbo.CHEQA_SAR.CarComment, dbo.CHEQA_SAR.AfterKpiValues AS CarResult
FROM         dbo.CHEQA_KPI INNER JOIN
                      dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID INNER JOIN
                      dbo.CHEQA_Objective ON dbo.CHEQA_KPI.ObjID = dbo.CHEQA_Objective.ObjID INNER JOIN
                      dbo.CHEQA_SAR ON dbo.CHEQA_KpiSetting.KpiID = dbo.CHEQA_SAR.KpiID INNER JOIN
                      dbo.CHEQA_KpiType ON dbo.CHEQA_KPI.KpiTypeID = dbo.CHEQA_KpiType.KpiTypeID
WHERE     (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND (dbo.CHEQA_KpiSetting.UnivID = @UnivID) AND (dbo.CHEQA_KpiSetting.FacID = @FacID) AND 
                      (dbo.CHEQA_KpiSetting.ObjID = @ObjID) AND (dbo.CHEQA_SAR.EduYear = @EduYear) AND (dbo.CHEQA_SAR.UnivID = @UnivID) AND 
                      (dbo.CHEQA_SAR.FacID = @FacID) AND (dbo.CHEQA_KPI.KpiID IN
                          (SELECT     KpiID
                            FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                            WHERE      (EduYear = @EduYear) AND ((UnivID = 00000) OR (UnivID = @UnivID))))
ORDER BY CAST(LEFT(dbo.CHEQA_KPI.KpiIndex, CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex) - 1) AS float), CAST(RIGHT(dbo.CHEQA_KPI.KpiIndex, 
                      LEN(dbo.CHEQA_KPI.KpiIndex) - CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex)) AS float)

-------Table เกณฑ์มาตรฐาน
SELECT   dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_CommonDataSet.IndexID, dbo.CHEQA_CommonDataSet.CdsName, 
                      dbo.CHEQA_CommonDataSet.CdsType
FROM         dbo.CHEQA_KPI INNER JOIN
                      dbo.CHEQA_KPIAssess ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KPIAssess.KpiID INNER JOIN
                      dbo.CHEQA_CommonDataSet ON dbo.CHEQA_KPIAssess.CdsID = dbo.CHEQA_CommonDataSet.CdsID INNER JOIN
                      dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID
WHERE     (dbo.CHEQA_KPI.KpiID IN
                          (SELECT     KpiID
                            FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                            WHERE      (EduYear = @EduYear) AND ((UnivID = 00000) OR (UnivID = @UnivID)))) AND (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND (dbo.CHEQA_KpiSetting.UnivID = @UnivID)
                      AND (dbo.CHEQA_KpiSetting.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.ObjID = @ObjID) AND (CHEQA_KPIAssess.EduYear=@EduYear) AND (CHEQA_CommonDataSet.EduYear = @EduYear)
ORDER BY CAST(LEFT(dbo.CHEQA_KPI.KpiIndex, CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex) - 1) AS float), CAST(RIGHT(dbo.CHEQA_KPI.KpiIndex, 
                      LEN(dbo.CHEQA_KPI.KpiIndex) - CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex)) AS float), dbo.CHEQA_CommonDataSet.IndexID 

---------Table ผลดำเนินงานและหลักฐานรายตัวบ่งชี้
SELECT      dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_KpiAssessResult.ItemResult, dbo.CHEQA_KpiAssessResult.ItemReff
FROM         dbo.CHEQA_KPI INNER JOIN
                      dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID INNER JOIN
                      dbo.CHEQA_KpiAssessResult ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiAssessResult.KpiID
WHERE     (dbo.CHEQA_KPI.KpiID IN
                          (SELECT     KpiID
                            FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                            WHERE      (EduYear = @EduYear) AND ((UnivID = 00000) OR (UnivID = @UnivID)))) AND (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND (dbo.CHEQA_KpiSetting.UnivID = @UnivID) 
                      AND (dbo.CHEQA_KpiSetting.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.ObjID = @ObjID) AND (dbo.CHEQA_KpiAssessResult.EduYear = @EduYear) AND 
                      (dbo.CHEQA_KpiAssessResult.UnivID = @UnivID) AND (dbo.CHEQA_KpiAssessResult.FacID = @FacID)
ORDER BY CAST(LEFT(dbo.CHEQA_KPI.KpiIndex, CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex) - 1) AS float), CAST(RIGHT(dbo.CHEQA_KPI.KpiIndex, 
                      LEN(dbo.CHEQA_KPI.KpiIndex) - CHARINDEX('.', dbo.CHEQA_KPI.KpiIndex)) AS float) ,dbo.CHEQA_KpiAssessResult.CdsID
---------Table ผลการประเมินรายองค์ประกอบ
SELECT     SarStrength as CarStrength, SarWeakness as CarWeakness, SarComment as CarComment
FROM         dbo.CHEQA_SarReport
WHERE     (Eduyear = @EduYear) AND (UnivID = @UnivID) AND (FacID = @FacID) AND (ObjID = @ObjID)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE  PROCEDURE [dbo].[usp_getFullPaper_Sar2] 
	@EduYear smallint,
	@UnivID varchar(5),
	@FacID varchar(5),
	@ObjID int

AS

--FIXED @ObjID
DECLARE @NewObjID int
SET @NewObjID = (SELECT TOP 1 ObjID FROM CHEQA_Objective WHERE UnivID=@UnivID AND FacID=@FacID AND EduYear=@EduYear AND ObjIndex=@ObjID)
IF NOT @NewObjID IS NULL
	SET @ObjID = @NewObjID

---------Table สรุปผู้บริหาร appendixb
SELECT     SelfOverAll
FROM         CHEQA_Overall
WHERE     (EduYear = @EduYear) AND (UnivID = @UnivID) AND 
                      (FacID = @FacID)

---------Table สรุป summary
--SELECT     CarObjResult, CarStdResult, CarViewReult,CarBestPractice
SELECT SarResultAll
FROM         CHEQA_Overall
WHERE     (EduYear = @EduYear) AND (UnivID = @UnivID) AND 
                      (FacID = @FacID)



------------Table  ภาคผนวกคณะกรรมการประเมินคุณภาพการศึกษาภายใน

SELECT     CHEQA_Objective.ObjIndex, CHEQA_SarReport.CarStrength, CHEQA_SarReport.CarWeakness, CHEQA_SarReport.CarComment, 
                      CHEQA_Overall.SelfOverAll, CHEQA_Overall.CommiteeOverAll, CHEQA_Overall.Assessor, CHEQA_Overall.CarIntro, CHEQA_Overall.CarObjResult, 
                      CHEQA_Overall.CarStdResult, CHEQA_Overall.CarViewReult, CHEQA_Overall.CarBestPractice, CHEQA_Overall.Method1, 
                      CHEQA_Overall.Method2
FROM         CHEQA_SarReport INNER JOIN
                      CHEQA_Overall ON CHEQA_SarReport.Eduyear = CHEQA_Overall.EduYear AND CHEQA_SarReport.UnivID = CHEQA_Overall.UnivID AND 
                      CHEQA_SarReport.FacID = CHEQA_Overall.FacID INNER JOIN
                      CHEQA_Objective ON CHEQA_SarReport.ObjID = CHEQA_Objective.ObjID
WHERE     (CHEQA_Overall.EduYear = @EduYear) AND (CHEQA_Overall.UnivID = @UnivID) AND (CHEQA_Overall.FacID =@FacID)
ORDER BY CHEQA_Objective.ObjIndex

---------Table ผลการประเมินรายองค์ประกอบ
SELECT     SarStrength as CarStrength, SarWeakness as CarWeakness, SarComment as CarComment
FROM         CHEQA_SarReport
WHERE     (EduYear = @EduYear) AND (UnivID = @UnivID) AND 
                      (FacID = @FacID) AND (ObjID=@ObjID)








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO