Notes

USE [gisdb]
GO
/****** Object: StoredProcedure [drr].[WS_CBMS_PROJECT] Script Date: 19/6/2562 17:17:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Arweemas U.
-- Create date: 14/6/2019
-- Description: for webservice CBMS Project
-- =============================================
ALTER PROCEDURE [drr].[WS_CBMS_PROJECT]
-- Add the parameters for the stored procedure here
@PI_MODE nvarchar(250) = NULL, -- Mode
@PI_PROJECT_ID nvarchar(250) = NULL,-- รหัสโครงการ
@PI_PROJECT_NAME nvarchar(50) = NULL, -- ชื่อโครงการ
@PI_PERCENT_TASK nvarchar(50) = NULL, -- ร้อยละของการดำเนินการ
@PI_PERCENT_DISBURSE nvarchar(50) = NULL, -- ร้อยละของการเบิกจ่าย
@PI_DELAY_TASK nvarchar(50) = NULL, --สถานะการล่าช้าของการดำเนินงาน
@PI_DELAY_DISBURSE nvarchar(50) = NULL, -- สถานะการล่าช้าของการเบิกจ่าย
@PI_LATTITUDE nvarchar(50) = NULL, --ละติจูด
@PI_LONGTITUDE nvarchar(50) = NULL, --ลองจิจูด


@PO_STATUS int output,
@PO_STATUS_MSG nvarchar(4000) output,
@PO_TOTAL int output
AS
BEGIN
BEGIN TRANSACTION SAVEDATA;
SET @PO_STATUS = 1;
SET @PO_STATUS_MSG = '';
DECLARE @V_ROLE_ID NVARCHAR(10);
DECLARE @V_MARKET_ID NVARCHAR(250);
DECLARE @V_MAXOID INT = (SELECT MAX(OBJECTID) FROM CBMS_PROJECT);
DECLARE @G geometry;
SET @G = geometry::Point( @PI_LONGTITUDE,@PI_LATTITUDE, 4326)
BEGIN TRY
IF @PI_MODE ='A'
BEGIN
--Insert GIS---
INSERT INTO CBMS_PROJECT (
OBJECTID,
Project_id,
Project_name,
Percent_Task,
Percent_Disburse,
Delay_Task,
Delay_Disburse,
Lattitude,
Longtitude,
SHAPE)
VALUES (
@V_MAXOID+1,
@PI_PROJECT_ID,
@PI_PROJECT_NAME,
@PI_PERCENT_TASK,
@PI_PERCENT_DISBURSE,
@PI_DELAY_TASK,
@PI_DELAY_DISBURSE,
@PI_LATTITUDE,
@PI_LONGTITUDE,
@G

);
END
ELSE IF @PI_MODE ='E'
BEGIN
UPDATE CBMS_PROJECT
SET
PROJECT_NAME = @PI_PROJECT_NAME,
PERCENT_TASK = @PI_PERCENT_TASK,
PERCENT_DISBURSE = @PI_PERCENT_DISBURSE,
DELAY_TASK = @PI_DELAY_TASK,
DELAY_DISBURSE = @PI_DELAY_DISBURSE,
LATTITUDE = @PI_LATTITUDE,
LONGTITUDE = @PI_LONGTITUDE,
SHAPE = @G
WHERE PROJECT_ID = @PI_PROJECT_ID;
END
ELSE IF @PI_MODE ='D'
BEGIN
DELETE CBMS_PROJECT WHERE PROJECT_ID = @PI_PROJECT_ID;
END


IF @@TRANCOUNT > 0
COMMIT TRANSACTION SAVEDATA;
END TRY
BEGIN CATCH
SET @PO_STATUS = 0;
SET @PO_STATUS_MSG = CAST(ERROR_NUMBER()AS Varchar(10))+' : '+
ERROR_MESSAGE() +' SP_NAME = '+
ERROR_PROCEDURE() +' LINE NO. '+
CAST(ERROR_LINE()AS Varchar(10));

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION SAVEDATA;

END CATCH
END