SQL Store Procedure 的寫法**************
USE [TempDB]
GO
/****** Object: StoredProcedure [dbo].[JOB_GVReport] Script Date: 2018/1/30 上午 09:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
--
-- exec [dbo].[JOB_GVReport] 'IOAAdminReport.aspx','GVDaily','107006','AND Convert(char(8),ApplyDat,112) >=''20161127'' AND Convert(char(8),ApplyDat,112) <=''20170524'''
-- 報表類別:GVDaily 進件報表
-- GVExpiry 逾期報表
-- =============================================
ALTER PROCEDURE [dbo].[JOB_GVReport]
(
@Prog VARCHAR(50), --程式來源
@ReportType VARCHAR(50), --報表類別
@UpdEmpID VARCHAR(10), --查詢人員
@SQLWHERE VARCHAR(Max), --畫面上查詢的條件
@ApplyType VARCHAR(20) = 'Public'
)
AS
Declare
@ExecuteTime datetime, /*Batch執行時間*/
@ErrorSave int,
@ErrMsg char(500), /*記錄錯誤訊息*/
@ERROR int,
@ROWCOUNT int,
@ERROR_MESSAGE nvarchar(4000),
@SQL varchar(max)
Select @ExecuteTime = (Select Convert(char(19),getdate(),20) as ExecuteTime)
print '********************************************************'
print '** JOB_GVReport Strat Process：' + convert(char(23),getdate(),21)
print '********************************************************'
Set NoCount ON
------------------------------------------------------------------------------------------------------
SET @SQL = 'EXEC JOB_GVReport @Prog=' + @Prog + ' @ReportType=' + @ReportType + ' @UpdEmpID=' + @UpdEmpID + ' @SQLWHERE=' + @SQLWHERE + '@ApplyType=' + @ApplyType
INSERT INTO [dbo].[Sys_Log] ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
SELECT getdate(),'','JOB_GVReport',@Prog,'REPORT','Start',@ReportType,0,0,'',@SQL,@UpdEmpID
/***************************
GVDaily 進件報表
****************************/
IF (@ReportType = 'GVDaily')
BEGIN
--LEFT(CustID,5)+ replicate(''X'',len(CustID)-5) 客戶編號 LEFT(CustName,1) + ''O'' + RIGHT(CustName,1) 客戶姓名
SET @SQL = 'SELECT Para3.ParaText 速別,CaseNo 案件編號,Convert(CHAR(19),ApplyDat,120) 進件日期,isnull(CaseCurrFlowStepName,'''') 案件狀態,Para2.ParaText 資料來源 '
SET @SQL += ' ,dbo.fn_FUZZY_ID(5,0,0,''X'',CustID) 客戶編號 ,dbo.fn_FUZZY_ID(1,0,1,''O'',CustName) 客戶姓名 '
SET @SQL += ' ,CampaignName 活動名稱,Type.TypeName 轉介類別 ,Category.CategoryName 轉介商品,Subject 主旨,isnull(ResponseName,'''') 結案代碼,Convert(CHAR(19),CloseDat,120) 結案日期'
SET @SQL += ' ,dbo.fn_GetEmpName(CreEmpID) 建立者 '
SET @SQL += ' FROM Tbl_CaseMain '
SET @SQL += ' LEFT JOIN Sys_Parameter Para1 on Para1.ParaType = ''' + @ApplyType + ''' and Para1.ParaKind =''ChlID'' and Para1.ParaValue = ChlID '
SET @SQL += ' LEFT JOIN Sys_Parameter Para2 on Para2.ParaType = ''' + @ApplyType + ''' and Para2.ParaKind =''Attribute'' and Para2.ParaValue = Attribute '
SET @SQL += ' LEFT JOIN Sys_Parameter Para3 on Para3.ParaType = ''' + @ApplyType + ''' and Para3.ParaKind =''Priority'' and Para3.ParaValue = Priority '
--SET @SQL += ' LEFT JOIN Sys_Parameter Para4 on Para4.ParaType = ''' + @ApplyType + ''' and Para4.ParaKind =''Contact'' and Para4.ParaValue = Contact '
SET @SQL += ' LEFT JOIN Sys_Type Type on Type.TypeID = Type '
SET @SQL += ' LEFT JOIN Sys_Category Category on Category.TypeID = Type and Category.CategoryID = Category '
SET @SQL += ' LEFT JOIN (select ResponseID ResponseID1,ResponseName from Sys_Response ) Response on Response.ResponseID1 = Tbl_CaseMain.ResponseID '
SET @SQL += ' WHERE 1=1 AND isnull(CaseCurrFlowStepID,'''') <> '''' ' + @SQLWHERE
SET @SQL += ' ORDER BY Para3.Sort,CaseNo '
print @SQL
Set @ErrMsg = 'QUERY ' + CAST(@ReportType as varchar) + ' ERROR'
END
-------------------------------------------------------------------------------------------------------------
/***************************
GVExpiry 逾期報表
****************************/
IF (@ReportType = 'GVExpiry')
BEGIN
SET @SQL = ' SELECT 到期日, 逾期天數,速別,案件編號,進件日期,案件狀態,資料來源,客戶編號,客戶姓名,是否回電,活動名稱,轉介類別,轉介商品,主旨,建立者 FROM ( '
SET @SQL += ' SELECT Convert(CHAR(8),Dateadd(D,cast(Para3.ParaDefine as int),ApplyDat),112) 到期日,isnull(CaseCurrFlowStepName,'''') 案件狀態 '
SET @SQL += ' ,dbo.fn_GetExpireDat(CaseNo) 逾期天數 '
SET @SQL += ' ,Para3.ParaText 速別,CaseNo 案件編號,Convert(CHAR(19),ApplyDat,120) 進件日期,Para1.ParaText 前端系統,Para2.ParaText 資料來源,ClientIP '
SET @SQL += ' ,dbo.fn_FUZZY_ID(5,0,0,''X'',CustID) 客戶編號 ,dbo.fn_FUZZY_ID(1,0,1,''O'',CustName) 客戶姓名 '
SET @SQL += ' ,Para4.ParaText 是否回電,CampaignName 活動名稱,Type.TypeName 轉介類別 ,Category.CategoryName 轉介商品,Subject 主旨,dbo.fn_GetEmpName(CreEmpID) 建立者'
SET @SQL += ' ,Para3.Sort '
SET @SQL += ' FROM Tbl_CaseMain '
SET @SQL += ' LEFT JOIN Sys_Parameter Para1 on Para1.ParaType = ''' + @ApplyType + ''' and Para1.ParaKind =''ChlID'' and Para1.ParaValue = ChlID '
SET @SQL += ' LEFT JOIN Sys_Parameter Para2 on Para2.ParaType = ''' + @ApplyType + ''' and Para2.ParaKind =''Attribute'' and Para2.ParaValue = Attribute '
SET @SQL += ' INNER JOIN Sys_Parameter Para3 on Para3.ParaType = ''' + @ApplyType + ''' and Para3.ParaKind =''Priority'' and Para3.ParaValue = Priority '
SET @SQL += ' LEFT JOIN Sys_Parameter Para4 on Para4.ParaType = ''' + @ApplyType + ''' and Para4.ParaKind =''Contact'' and Para4.ParaValue = Contact '
SET @SQL += ' LEFT JOIN Sys_Type Type on Type.TypeID = Type '
SET @SQL += ' LEFT JOIN Sys_Category Category on Category.TypeID = Type and Category.CategoryID = Category '
SET @SQL += ' WHERE 1=1 AND isnull(CaseCurrFlowStepID,'''') <> '''' AND isnull(CloseDat,'''') = '''' AND Convert(CHAR(8),Dateadd(D,cast(Para3.ParaDefine as int),ApplyDat),112) < Convert(CHAR(8),getdate(),112) ' + @SQLWHERE
SET @SQL += ' ) a ORDER BY Sort,逾期天數 desc '
print @SQL
Set @ErrMsg = 'QUERY ' + CAST(@ReportType as varchar) + ' ERROR'
END
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
SET @ERROR = @@ERROR
SET @ROWCOUNT = @@ROWCOUNT
SET @ERROR_MESSAGE = ERROR_MESSAGE()
GOTO err_Control
END CATCH
-------------------------------------------------------------------------------------------------------------
INSERT INTO [dbo].[Sys_Log] ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
SELECT getdate(),'','JOB_GVReport',@Prog,'REPORT','End',@ReportType,0,0,'',@SQL,@UpdEmpID
Set NoCount off
Return 0
err_Control:
Set NoCount off
INSERT INTO [dbo].[Sys_Log] ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
SELECT getdate(),'','JOB_GVReport',@Prog,'REPORT','Err',@ReportType,0,0, @ErrMsg,@SQL + '[' + cast(@ERROR_MESSAGE as char) + ']' ,@UpdEmpID
print '*** STEP ' + cast( @ReportType as varchar)+ '處理失敗-- JOB_GVReport Fail ***' + @ErrMsg
print 'SystemDate = ' + cast(@ExecuteTime as char) + ',' + 'ErrorSave = ' + cast(@ErrorSave as char)
Return 1
-------------------------------------------------------------------------------------------------------------------------
.NET的寫法
**呼叫的Function
ucGridMain.DataSource = CFPFunction.GetGVReport(ProgID, ReportType, oUser.UserID, SQLWhere, "Public");
if (ReportType == "GVTxnLog")
{
ucGridMain.HeaderStyle.Font.Size = FontUnit.Point(10); // FontUnit.Medium;
ucGridMain.RowStyle.Font.Size = FontUnit.Point(10);
ucGridMain.AlternatingRowStyle.Font.Size = FontUnit.Point(10);
}
else
{
ucGridMain.HeaderStyle.Font.Size = FontUnit.Point(11); // FontUnit.Medium;
ucGridMain.RowStyle.Font.Size = FontUnit.Point(11);
ucGridMain.AlternatingRowStyle.Font.Size = FontUnit.Point(11);
}
ucGridMain.DataBind();
-------------------------------------------------------------------------------------------------------------------------
呼叫的Function
public class CFPFunction
{
//設定程式使用的變數
static UserInfo oUser;
static string _TempDBName= "TempDB";
public CFPFunction()
{
//
// TODO: 在此加入建構函式的程式碼
//
}
//報表使用
public static DataTable GetGVReport(string ProgID, string ReportType, string UpdEmpID, string SQLWhere, string ApplyType)
{
oUser = UserInfo.getUserInfo();
DbHelper db = new DbHelper(_TempDBName);
DataTable dt = new DataTable();
string strSQL = string.Empty;
CommandHelper sb = db.CreateCommandHelper();
sb.Reset();
sb.AppendStatement("EXEC JOB_GVReport '" + ProgID + "','" + ReportType + "','" + UpdEmpID + "','" + SQLWhere + "','" + ApplyType + "'" );
return dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0];
}
}
留言列表
