반응형
USE [ERPMES]
GO
/****** Object: StoredProcedure [dbo].[USP_SOURCE_GEN3] Script Date: 2025-07-15 오후 5:17:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[USP_SOURCE_GEN3]
(
@PROC_NAME NVARCHAR(128) -- 예: 'dbo.USP_GET_ORDER'
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @paramText NVARCHAR(MAX) = ''
DECLARE @object_id INT = OBJECT_ID(@PROC_NAME);
SELECT
c.name AS COLUMN_NAME
, MAX(CAST(ep.value AS NVARCHAR(MAX))) AS COMMENT
INTO #COMMENTS
FROM
sys.columns c
LEFT JOIN
sys.extended_properties ep
ON ep.major_id = c.object_id
AND ep.minor_id = c.column_id
AND ep.name = 'MS_Description'
WHERE ep.value IS NOT NULL
GROUP BY
c.name;
--select * From #COMMENTS WHERE COLUMN_NAME = 'CUST_LABEL_ID'
SET @paramText = ''
PRINT N'===== IN 프로퍼티 파라미터 정보 =====';
PRINT 'public string USER_ID { get; set; } = Atomus.Config.Client.GetAttribute("Account.USER_ID").ToString(); '
SELECT @paramText = @paramText +
'public ' +
CASE WHEN (t.name) = 'numeric' THEN 'decimal '
WHEN (t.name) = 'datetime2'
OR (t.name) = 'datetime'
OR (t.name) = 'date'
OR CHARINDEX('_DATE', t.name) > 0 THEN 'DateTime '
ELSE 'string '
END +
+ REPLACE(p.name,'@','') + ' { get; set; } //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = REPLACE(p.name,'@','') ),'')
+ CHAR(13)
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE p.object_id = @object_id;
PRINT @paramText
SET @paramText = ''
PRINT N'===== OUT 프로퍼티 파라미터 정보 =====';
SELECT
@paramText = @paramText +
'public ' +
CASE WHEN LEFT(system_type_name, 7) = 'numeric' THEN 'decimal'
WHEN LEFT(system_type_name, 4) = 'date' THEN 'DateTime'
ELSE 'string'
END + ' ' +
+ REPLACE(name,'@','') + ' { get; set; } //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = name ),'') + CHAR(13)
FROM sys.dm_exec_describe_first_result_set_for_object(@object_id, NULL)
WHERE error_number IS NULL;
PRINT @paramText
SET @paramText = ''
PRINT N'===== SetGrid 정보 =====' + CONVERT(NVARCHAR, @object_id);
DECLARE cur CURSOR FOR
SELECT
'gridAgent.AddColumn(100, ColumnVisible.True, EditAble.False, Filter.False, Merge.False, Sort.Automatic, null, ' +
CASE
WHEN LEFT(system_type_name, 7) = 'numeric' AND CHARINDEX('_ID', name) > 0 THEN'Alignment.MiddleCenter'
WHEN LEFT(system_type_name, 7) = 'numeric' AND CHARINDEX('_NO', name) > 0 THEN'Alignment.MiddleCenter'
WHEN LEFT(system_type_name, 7) = 'numeric' THEN 'Alignment.MiddleRight'
WHEN LEFT(system_type_name, 4) = 'date' THEN 'Alignment.MiddleCenter'
WHEN CHARINDEX('_NM', name) > 0 OR CHARINDEX('MAKTX', name) > 0
OR CHARINDEX('_RMK', name) > 0 OR CHARINDEX('REMARK', name) > 0 THEN 'Alignment.MiddleLeft'
ELSE 'Alignment.MiddleCenter'
END + ' ' +
', '+
CASE
WHEN LEFT(system_type_name, 7) = 'numeric' AND CHARINDEX('_ID', name) > 0 THEN 'string.Empty'
WHEN LEFT(system_type_name, 7) = 'numeric' AND CHARINDEX('_NO', name) > 0 THEN 'string.Empty'
WHEN LEFT(system_type_name, 7) = 'numeric' THEN '"###,###,###,##0.000"'
WHEN LEFT(system_type_name, 4) = 'date' THEN '"yyyy-MM-dd"'
--WHEN RIGHT(c.name, 3) = '_DT' THEN '"yyyy-MM-dd"'
WHEN CHARINDEX('_NM', name) > 0 OR CHARINDEX('MAKTX', name) > 0
OR CHARINDEX('_RMK', name) > 0 OR CHARINDEX('REMARK', name) > 0 THEN 'string.Empty'
ELSE 'string.Empty'
END
+ ', "' +
name + '", "' +
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = ISNULL(name,'') ),'')
+'"); //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = ISNULL(name,'') ),'')
FROM sys.dm_exec_describe_first_result_set_for_object(@object_id, NULL)
WHERE error_number IS NULL;
OPEN cur;
FETCH NEXT FROM cur INTO @paramText;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @paramText
FETCH NEXT FROM cur INTO @paramText;
END
CLOSE cur;
DEALLOCATE cur;
SET @paramText = ''
PRINT N'===== IN 모델 매핑 정보 =====';
SELECT @paramText = @paramText +
'model.' + REPLACE(p.name,'@','') + ' = ' + REPLACE(p.name,'@','') +
CASE WHEN (t.name) = 'numeric' THEN '.EditValue.SafeDecimal(); //'
WHEN (t.name) = 'datetime2'
OR (t.name) = 'datetime'
OR (t.name) = 'date'
OR CHARINDEX('_DATE', p.name) > 0 THEN '.EditValue.GetDateString(); //'
ELSE '.EditValue.SafeString(); //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = REPLACE(p.name,'@','') ),'')
END + CHAR(13)
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE p.object_id = @object_id;
PRINT @paramText
SET @paramText = ''
PRINT N'===== IN 모델 매핑 정보2 =====';
SELECT @paramText = @paramText +
'model.' + REPLACE(p.name,'@','') + ' = datarow["' + REPLACE(p.name,'@','') +
CASE WHEN (t.name) = 'numeric' THEN '"].SafeDecimal(); //'
WHEN (t.name) = 'datetime2'
OR (t.name) = 'datetime'
OR (t.name) = 'date'
OR CHARINDEX('_DATE', p.name) > 0 THEN '"].GetDateString(); //'
ELSE '"].SafeString(); //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = REPLACE(p.name,'@','') ),'')
END + CHAR(13)
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE p.object_id = @object_id;
PRINT @paramText
SET @paramText = ''
PRINT N'===== IN 컨트롤러 매핑 정보 =====';
SELECT @paramText = @paramText +
'serviceDataSet["0"].AddParameter("' + REPLACE(p.name,'@','') +
CASE WHEN (t.name) = 'numeric'
OR CHARINDEX('_QTY', p.name) > 0 THEN '", DbType.Decimal); //'
WHEN (t.name) = 'datetime2'
OR (t.name) = 'datetime'
OR (t.name) = 'date'
--OR CHARINDEX('_DATE', p.name) > 0
THEN '", DbType.DateTime); //'
ELSE '", DbType.NVarChar); //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = REPLACE(p.name,'@','') ),'')
END + CHAR(13)
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE p.object_id = @object_id;
PRINT @paramText
PRINT 'serviceDataSet["0"].NewRow();';
SET @paramText = ''
SELECT @paramText = @paramText +
'serviceDataSet["0"].SetValue("' + REPLACE(p.name,'@','') + '", model.' + REPLACE(p.name,'@','') +
CASE WHEN (t.name) = 'numeric' THEN '.SafeDecimal()); //'
WHEN (t.name) = 'datetime2'
OR (t.name) = 'datetime'
OR (t.name) = 'date'
OR CHARINDEX('_DATE', p.name) > 0 THEN '.GetDateString()); //'
ELSE '.SafeString()); //'
+
ISNULL((SELECT TOP 1 C.COMMENT FROM #COMMENTS C WHERE ISNULL(C.COLUMN_NAME,'') = REPLACE(p.name,'@','') ),'')
END + CHAR(13)
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE p.object_id = @object_id;
PRINT @paramText
END