主程式
SQLReport.exe 為系統主程式, 所有的報表產生作業皆利用此程式產生, 透過簡單的參數傳入即可, 請參考詳細介紹。
命令列工具 > 安裝目錄 > SQLReport.exe
運作流程圖
參數說明
-p 專案目錄 範例 SQLReport.exe -p C:\銷售日報表作業
如未指定時, 將逐一走訪專案目錄下所有的專案資料夾:
-v 報表參數 範例 SQLReport.exe -p C:\銷售日報表作業 -v vendorid=711;reportdate=20230818 執行此專案報表作業時, 傳遞 vendorid 及 reportdate 二參數供輸入參數使用, 可作為 SQL的條件或任何替換參數作業。
(配合 batch 檔的撰寫來完成參數準備作業, 但我們建議由 SQL/JavaScript 方式來生成參數較為彈性)
報表專案建置
SQL資料來源(DataSource)
注意事項 1 在產製報表前, 請先設定好你的資料來源(自行新增), 並完成「測試連線」成功。 注意事項 2 系統一律使用ODBC連接資料庫, 各家ODBC實作與實際資料庫有些許不同, 故注意語法的可用性。
變數型(單筆Record) 可多個 檔案格式 : [DB][資料來源名稱] 用途說明.SQL
說明 變數除了自動替換Excel中的 ${變數} 使用外, 同時成為「全域」變數供後續使用。(包含可用文件, 如 MAIL 及 SQL)
範例 [DB][SQLReportDB] CURRENT_TIMESTAMP.SQL
檔案內容 將自動替換回傳的變數名稱, 此例為 ${CURRENT_TS} 及 ${REPORT_DATE},
--注意只能回傳一筆記錄
--SQLITE ODBC 須一個FROM資料表方能查詢, 故使用PARAMS_DETAIL
SELECT
datetime('now') as CURRENT_TS ,
SUBSTR(datetime('now'),1,10) as REPORT_DATE
FROM PARAMS_DETAIL WHERE 1=1 limit 1;
--SQLITE ODBC 須一個FROM資料表方能查詢, 故使用PARAMS_DETAIL
SELECT
datetime('now') as CURRENT_TS ,
SUBSTR(datetime('now'),1,10) as REPORT_DATE
FROM PARAMS_DETAIL WHERE 1=1 limit 1;
插入型區塊(可多筆Record) 可多個 檔案格式 : [DB][資料來源名稱][#參數] 用途說明.SQL
範例 [DB][SQLReportDB][#ROWS] SQLReport Sample.SQL
檔案內容 此SQL產出的資料將替換(逐筆插入) Excel 檔中的 #{ROWS} 變數
--此表由 SQLReportDB 提供
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE='Common' limit 8;
區塊變數 可使用{變數.欄位}取值代入後續SQL處理(以字串及逗號分隔提供)
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE='Common' limit 8;
替換型區塊(可多筆Record) 可多個 檔案格式 : [DB][資料來源名稱][&參數] 用途說明.SQL
範例 [DB][SQLReportDB][&ROWS] SQLReport Sample.SQL
檔案內容 此SQL產出的資料將替換(逐筆取代) Excel 檔中的 &{ROWS} 變數
--此表由 SQLReportDB 提供
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE='Common' limit 8;
區塊變數 可使用{變數.欄位}取值代入後續SQL處理(以字串及逗號分隔提供)
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE='Common' limit 8;
[DB][DS] 全域變數代換通則 任何SQL執行結果皆會被儲存供後續使用
說明 SQL 語句可以取得已完成SQL結果當成它的輸入值
使用方式 二個通用方式 {變數} , {變數.欄位} , 以利資料串接作業
--使用單筆資料變數代入
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE='{欄位名稱}';
--使用多筆資料變數代入
-- {ALLDATA} Excel 套表, 同時可利用以下的語法代入下一個SQL重覆使用
[DS][DS][#ALLDATA] 查詢你多筆資料.SQL
SELECT AREA FROM MYSHOP_ALLDATA; --可取出所有的店面的區域
--假設上面的SQL資料為 「高雄 及台北」二筆, 下方即可將「{#ALLDATA.AREA}」替換為「 '高雄', '台北'」資料
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE in ({#ALLDATA.AREA});
-- 此區塊型多筆資料{變數.欄位}使用時期可能的限制問題, 例如最常見的是「最大SQL長度限制」。
注意 區塊變數僅限用於 SQL 使用。
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE='{欄位名稱}';
--使用多筆資料變數代入
-- {ALLDATA} Excel 套表, 同時可利用以下的語法代入下一個SQL重覆使用
[DS][DS][#ALLDATA] 查詢你多筆資料.SQL
SELECT AREA FROM MYSHOP_ALLDATA; --可取出所有的店面的區域
--假設上面的SQL資料為 「高雄 及台北」二筆, 下方即可將「{#ALLDATA.AREA}」替換為「 '高雄', '台北'」資料
SELECT VALUE1 as VarKey, VALUE2 as VarValue
FROM PARAMS_DETAIL WHERE REFCODE in ({#ALLDATA.AREA});
-- 此區塊型多筆資料{變數.欄位}使用時期可能的限制問題, 例如最常見的是「最大SQL長度限制」。
[JS] 支援 JavaScript 串接程式 檔案格式 : [JS] 用途說明.js
範例 [JS]console.js
檔案內容 目前 javascript 支援版本(ECMAScript 2016)
// config 及 console 功能測試
console.info('取得全域變數 CURRENTDATE:' + config.get('CURRENTDATE') + '' CURRENTTIME:'' + config.get('CURRENTTIME'));
config.set('mykey', 'myvalue');
console.log('取得自行設定值(mykey) : ' + config.get('mykey'));更多函式庫 請參閱 [javascript 函式庫] 專頁說明。
JS/SQL 執行順序 「名稱」排序即為執行順序
說明 我們依照檔案順序進行執行, 故一般可用編號來命名進行, 即可簡易定義腳本執行順序。
範例
[DB][SQLReportDB] 1. CURRENT_TIMESTAMP.SQL
[DB][SQLReportDB] 2. 利用上述所產生的變數進行查詢.SQL
當然你也可以利用以下方式進行跨資料庫順序定義, 中間也可安插 JS 腳本來處理事務
1. [DB][SQLReportDB] CURRENT_TIMESTAMP.SQL
2. [JS] 利用javascript來互動後取得資料.js
3. [DB][SQLReportDB] 利用上述所產生的變數進行查詢.SQL
(目前跨資料來源排序名稱還是會納入排序, 請自行判斷)
[DB][SQLReportDB] 2. 利用上述所產生的變數進行查詢.SQL
當然你也可以利用以下方式進行跨資料庫順序定義, 中間也可安插 JS 腳本來處理事務
1. [DB][SQLReportDB] CURRENT_TIMESTAMP.SQL
2. [JS] 利用javascript來互動後取得資料.js
3. [DB][SQLReportDB] 利用上述所產生的變數進行查詢.SQL
(目前跨資料來源排序名稱還是會納入排序, 請自行判斷)
EXCEL報表樣版檔 可多個 檔案格式 : 用途說明.xlsx (只認附檔名為.xlsx, 允許大小為0的空文件以忽略套表作業)。
範例 SQLReport Sample.xlsx
檔案內容 此excel檔相關變數將被替換(包含&{ROW}, #{ROWS}, ${CURRENT_TS}})
PDF 支援 PDF 轉換, 只要在 xlsx 檔案前面加上 [PDF] 識別字, 同時支援PDF加密功能, 請設定 PDFPASSWORD 變數即可。
郵件樣版檔 檔案格式 : [MAIL] 主旨 : 郵件說明 (可使用{變數}代入SQL結果或外部參數)
範例 [MAIL] SQLReport Sample - {REPORT_DATE}
檔案內容
此郵件為 SQLReport 報表產生器展示, 附件為 SQLReport 參數表, 敬請查收。
<br>
這是報表日期 : {CURRENTDATE} (系統內部參數)
<br>
這是報表日期 : {CURRENTDATE} (系統內部參數)
郵件收件者
請修改「Mail Settings」後, 並確認你的寄件伺服器是否正常, 記得執行「測試寄件信箱」確認。
範例 [MAILTO] yourmail@gmail.com
專案報表產出後將寄給 yourmail@gmail.com 此收件者。
收件者群組檔 可多個 檔案格式 : [MAILGROUP] 收件群組說明
範例 [MAILGROUP] 收信者群組
檔案內容
receiver_mail1@gmail.com
receiver_mail2@gmail.com
receiver_mail2@gmail.com
資料來源收件者群組 可多個 檔案格式 : [DB][資料來源名稱][MAILGROUP] 收件群組說明
範例[DB][SQLReportDB][MAILGROUP] GET MAIL DATA.SQL
檔案內容
--取回此專案的email清單
SELECT VALUE2 as EMAIL FROM PARAMS_DETAIL WHERE REFCODE='MailGroup' AND VALUE1='sample';
SELECT VALUE2 as EMAIL FROM PARAMS_DETAIL WHERE REFCODE='MailGroup' AND VALUE1='sample';
多份報表作業
範例[DB][SQLReportDB][FOREACH] ALL PRODUCT .SQL
檔案內容
--取回所有產品的PID, 逐一進行報表產製作業, PID每個產品編號, 為每一個PID產製一份報表
SELECT PID FROM PRODUCT;
SELECT PID FROM PRODUCT;
常用全域變數
CURRENTSHORTDATE 取得當前日期 (格式 : yyyyMMdd)
CURRENTDATE 取得當前日期 (格式 : yyyy-MM-dd) CURRENTTIME 取得當前時間 (格式 : HHmmss) TAIPEIDATE 取得當前台北時間(民國年) (格式 : YYYMMDD) PROJECT_HOME 取得專案目錄路徑 EXE_PATH 取得執行目錄路徑 EXE_NAME 取得執行檔檔名 USER_HOME 取得使用者 HOME __self_name 取得目前的 js 腳本名稱(限 js 使用 {} 取值)
工作排程器(動作設定)
開始位置 請設定為 SQLReport 安裝目錄
執行程式 請瀏覽至 安裝目錄 > SQLReport.exe 新增引數(專案參數) -p 專案目錄路徑 -v 參數1=值1;參數2=值2 執行時間 請依需求自行設定
系統日誌(log)
儲放位置1 請瀏覽 安裝目錄 > logs 目錄 > 以日期為單位記錄
儲放位置2 請瀏覽 專案目錄 > logs 目錄 > 以日期為單位記錄注意 日誌僅會儲存六個月, 超過六個月的日誌會自動刪除, 請自行備份。