Setup bảng tạm trong CSDL
Mỗi bảng tạm yêu cầu phải có 4 cột sau (nếu dùng mọi option default):
| Cột | Kiểu | Mục đích |
| RowCondition | JSON (trong mariadb là LONGTEXT) | Lưu phần where trong query, sử dụng |
| ItemID | VARCHAR/CHAR | Dùng default làm id của từng hàng, không nhất thiết phải làm PRIMARY KEY nếu có ý định viết id động |
| ParentID | VARCHAR/CHAR | Dùng để xác định hàng cha của hàng con. Nếu không dùng các tính năng như sum tổng, |
| Static | TINYINT (1/0) | Dùng để xác định các hàng có lấy dữ liệu từ bảng dữ liệu không |
Đối với điều kiện cột hoặc viết trực tiếp điều kiện IF() trong các cột I1, I2, ... hoặc dùng ColumnConditions trong ReportFrame (có thể phối hợp với BeforeQuery)
1. Static
Cột Static (dịch: tĩnh) quyết định tính "tĩnh" của hàng, đúng hơn mà nói nó chỉ dựa trên tính phụ thuộc của hàng vào dữ liệu: tức là đối với các hàng cần phải lên mà giá trị của nó hoặc chỉ cần lên tĩnh (lên 1 hàng trống không) hoặc chỉ phụ thuộc vào các hàng khác mà không phụ thuộc bảng lọc trước.
Các hàng phải/nên đánh tĩnh bao gồm:
- Hàng tĩnh không lên dữ liệu nhưng bắt buộc phải có trong các báo cáo (các hàng tiêu đề chẳng hạn)
- Hàng tính bằng tổng của các hàng khác (SumTree)
- Hàng tính bằng công thức của khác hàng khác ($evalAll)
2. ItemID/ ParentID
Đây là 2 cột bắt buộc phải có, bởi hầu như mọi quá trình trong ReportFrame đều dựa trên 2 cột này, cột ItemID không nhất thiết phải để UNIQUE nếu như cần sinh động ItemID, song lưu ý khi sinh động ItemID sẽ bị mất tính năng truyền điều kiện từ cha là ItemID động xuống con (bởi trong quá trình chạy, ReportFrame không lấy dữ liệu trước rồi mới truyền điều kiện).
3. RowCondition
Cách setup điều kiện RowCondition
Cột RowCondition hay còn gọi là điều kiện hàng, quyết định câu lệnh where cũng như 1 số thủ tục/phép toán trên bảng. Trước hết nó phải chuẩn đúng theo dạng JSON, nó xây dựng câu lệnh where giống với Mongoose (nhưng nên nhớ rằng bản chất của nó không phải là Mongoose). Cú pháp của nó thường là như sau:
{
"<tên-cột>": {
"$<toán-tử>": <giá-trị>
},
//các điều kiện khác
}Thí dụ chẳng hạn: để xây câu lệnh WHERE Age >= 18 AND Country = "USA", Ta viết như sau:
{
"Age": {
"$gte": 18
},
"Country": {
"$eq": "USA"
}
}Trong trường hợp của dấu '=', ta có thể bỏ "$eq" đi, do đó, câu lệnh trên có thể viết gọn thành
{
"Age": {
"$gte": 18
},
"Country": "USA"
}Nhiều điều kiện dùng chung 1 cột có thể bỏ chung 1 chỗ, chẳng hạn câu lệnh WHERE Age BETWEEN 18 AND 26 Có thể viết thành
{
"Age": {
"$gte": 18,
"$lte": 26
}
}Lưu ý: Các <tên-cột> đều lấy từ bảng dữ liệu, hãy rất cẩn thận khi viết tên cột từ bảng dữ liệu sang!
Hiện tại, chúng ta đang hỗ trợ các phép toán tử sau:
| Toán tử | Yêu cầu giá trị | VD Câu lệnh sinh ra | Note |
| $eq | Số/Xâu | WHERE AGE = val | |
| $gte | Số/Xâu | WHERE AGE >= val | |
| $lte | Số/Xâu | WHERE AGE <= val | |
| $lt | Số/Xâu | WHERE AGE < val | |
| $gt | Số/Xâu | WHERE AGE > val | |
| $ne | Số/Xâu | WHERE AGE != val | |
| $in | Mảng giá trị | WHERE AGE IN (1, 2, 3, ...) | |
| $nin | Mảng giá trị | WHERE AGE NOT IN (1, 2, 3, ...) | |
| $like | Xâu | WHERE AGE LIKE '%cond%' | |
| $regex | Xâu | WHERE AGE REGEXP 'cond' | |
| $exists | Số (1/0) | WHERE AGE IS NOT NULL | Nếu giá trị = 1 thì yêu cầu cột khác null, 0 thì ngược lại |
| $lein3 | Mảng giá trị | WHERE LEFT(AGE, 3) IN (1, 2, 3, ..) | Bởi nhiều truy vấn yêu cầu tài khoản/khoản mục kiểm tra bắt đầu với abcxyz, các câu lệnh ở dưới đây được viết ngắn trc |
| $lein4 | Mảng giá trị | WHERE LEFT(AGE, 4) IN (1, 2, 3, ..) | |
| $nlein3 | Mảng giá trị | WHERE LEFT(AGE, 3) NOT IN (1, 2, 3, ..) | |
| $nlein4 | Mảng giá trị | WHERE LEFT(AGE, 4) NOT IN (1, 2, 3, ..) |
Để có thể thêm các phép toán logic như OR hoặc NOT, Cú pháp của nó như sau:
{
"$or": {
//các điều kiện 1 (cond-1)
},
"$not": {
//các điều kiện 2 (cond-2)
},
"$and": {
//các điều kiện 3 (cond-3)
}
}Mặc định nếu không viết gì, thì điều kiện sẽ là AND, như vậy, câu lệnh trên sẽ được viết như sau:
WHERE (<cond-1> OR <cond-1> OR ...) AND NOT (<cond-2> ... ) AND (<cond-3> AND ....)Trong trường hợp cần nest các điều AND và OR vào với nhau, ta dùng
{
"$or": [
{"$and": {<cond-1>}},
{"$and": {<cond-1>}},
],
}
//tương tự như vậy
{
"$and": [
{"$or": {<cond-1>}},
{"$or": {<cond-1>}},
],
}
Một ví dụ tổng thể tổ hợp của những gì ở trên:
{
"$or": {
"ECNo": {
"$like": "2005101%"
},
"$and": {
"ECNo": "2005207",
"ECVal": {
"$gte": 1,
"$lte": 6
}
}
},
"AccountNo": {
"$like": "632%"
}
}Câu lệnh WHERE mà nó sinh ra là:
WHERE
(
`ECNo` LIKE '2005101%'
OR
(`ECNo` = 2005207 AND `ECVal` >= 1 AND `ECVal` <= 6)
)
AND `AccountNo` LIKE '632%' Để thêm điều kiện kết hợp hoặc xử lý dữ liệu sau điều kiện WHERE: GROUP BY, HAVING, ORDER BY, LIMIT, ta dùng điều kiện tương tự ở trên, nhưng không thêm dấu $ trước đấy
| Format | Yêu cầu giá trị | Ví dụ |
| "groupBy": [<cột 1>, ....] | Dữ liệu dạng mảng | "groupBy": ["ExpenseID"] |
| "orderBy": [<cột 1>, ....] | Dữ liệu dạng mảng | "orderBy": ["ExpenseID"] |
| "having": "<điều kiện>" | Dạng xâu trong điều kiện SQL | "having": "I1 != 0" |
| "limit": <number> | Số nguyên dương | "limit": 10 |
Các tính năng đặc biệt của RowCondition
1. $cascade và $>cascade
Trước hết, ReportFrame có hỗ trợ truyền điều kiện hàng cha xuống hàng con, để lấy ví dụ, xem đặc tả sau:
Theo quan sát: Các hàng dấu '+' đều yêu cầu:
- PS Nợ 421x (tức PostType = 1 AND AccountNo LIKE '421%')
- Yêu cầu nó phải thuộc 1 trong 5 trong 5 năm ngày trên (tức PostDate IN ('YYYY-01-01',....))
ReportFrame hỗ trợ 2 cách truyền điều kiện từ cha đến con: $cascade và $>cascade, cú pháp của nó trong RowCondition như sau:
{
"$cascade": {
//các điều kiện truyền xuống
},
//các điều kiện khác trong hàng
}Lấy ví dụ trên,
Sự khác biệt đơn giản giữa $cascade và $>cascade là sự trao đổi giữa sức mạnh và tốc độ giữa 2 cái: $cascade có thể điều kiện từ cha xuống cháu, trong khi $>cascade chỉ có thể điều kiện xuống con trực tiếp của nó:
Một vấn đề về tốc độ nghiễm nhiên xảy ra là khi $cascade sẽ lâu hơn $>cascade do phải xét từ đến cháu, hơn nữa $cascade truyền cho toàn bộ cây báo cáo nên phải cẩn thận khi gán ItemID và ReportID khi truyền điều kiện:
Note: $cascade và $>cascade hiện chưa mix được với nhau, nếu cả 2 cái cùng ở trong 1 RowCondition thì ngẫu nhiên cái nào đi trước sẽ được xét trước. Hơn nữa, cả 2 đều chỉ truyền điều kiện xuống cho con/cháu của nó chứ không phải chính bản thân nó. Do đó nếu như có quan hệ cha/con mà cả cha và con đều chung điều kiện X thì vẫn phải viết X trong cả $cascade và điều kiện ở hàng cha.
2. $eval và $evalAll
Để việc tính toán giữa các hàng dễ dàng và thuận tiện hơn RowCondition hỗ trợ thêm $evalAll, cú pháp của nó như sau:
{
"$eval": "<công-thức-sql>"
}Trong đó, các hàng được viết là #<giá-trị-ItemID>
Trong đó, <công-thức-sql> là biểu thức sql mà trong đó các hàng khác được đánh dấu bằng dấu # kết hợp với các phép toán SQL thông thường. Các hàng được đánh dấu theo ItemID sẽ được truyền vào công thức để tính tất cả giá trị các cột I (và DynCol_ nếu như dùng động cột).
Thí dụ, giả sử ta có hàng ItemID = I.4 có RowCondition được viết như sau:
{
"$evalAll": "#I.1 - #I.2 + #I.3"
}Thì tất cả các cột I1, I2, ... trong hàng I.4 đó sẽ tính tương ứng với công thức lấy hàng I.1 trừ hàng I.2 rồi cộng I.3,
Một bất lợi khá là rõ ràng đó là $evalAll không thể lấy các ItemID động, do đó với các trường hợp như vậy các lập trình viên vẫn cần phải tự viết code để cập nhật các giá trị.
Lưu ý: tính năng chưa test nhiều (khi sử dụng, hãy báo lại người chịu trách nhiệm bảo trì để sửa lỗi gặp phải)
$eval: ngược lại tính công thức theo ô thay vì theo hàng, công thức của nó tương tự như trên, nhưng thêm 1 điều kiện đó là ItemID không được phép có dấu '.' trong đó. Các ô được viết theo công thức #<tên-hàng>.<tên cột> và công thức phải thuộc dạng:
{
"$eval": "<tên-cột-1> = <công-thức-sql-1>, <tên-cột-2> = <công-thức-sql-2>, ..."
}Lấy ví dụ chẳng hạn
{
"$eval": "I1 = #I#01.I1 - #I#02.I2 + #I#03.I3"
}Đồng thời một lưu ý đo là: $eval và $evalAll không dùng chung được trong cùng 1 hàng (bởi không có thứ tự rõ ràng trong JSON)
3. Ít nhất: $>0
Đối với các hàng cần hiện ít nhất 1 hàng kể cả có dữ liệu hay không ta dùng "$>0": 1. Điều này đảm bảo luôn có 1 hàng dữ liệu trong các trường hợp:
- Sử dụng động cột
- Hàng không dùng hàm aggerate nhưng đặc tả vẫn yêu cầu hiện ít nhất 1 lên (rất hiếm gặp)
{
"$>0": 1
}4. Cách viết công thức ở cột khác
Đối với các cột khác, kiểu dữ liệu nên để tương ứng với giá trị của cột đó ngoại trừ các cột giá trị số ra
Công thức nói chung ở mỗi ô hoặc được điền tĩnh hoặc viết dưới dạng công thức {<công-thức-sql>}
Như nhìn ở đây, các cột ItemID, TableID, ItemNo, ... đều đang lấy tĩnh các giá trị. Riêng cột ItemName thì sẽ hoạt động như sau:
- Đối với các hàng không kết thúc và bắt đầu bằng dấu { và dấu } sẽ in tĩnh như bình thường
- Ngược lại, đối với các hàng có thì sẽ được xử lý tương ứng với công thức sql ở đó, chẳng hạn dòng A#03#01#01 sẽ in các tài khoản ngân hàng qua {BankAccount} và như để ý ở A#01#03#01 công thức có thể nối với xâu tĩnh qua CONCAT
Riêng đối với các cột in giá trị số (I1, I2, ...., I10), có thêm 1 giá trị đặc biệt: SumTree
Ngoài những {SUM(LCAmount)} dùng để tính tổng như ở dưới đây, SumTree dùng để tính tổng của cây bóng cây, đơn giản mà nói: dựa trên ItemID và ParentID nó sẽ tự động tính hai cái sau: độ sâu của hàng đó trên cây báo báo và tự động tính tổng của hàng đó dựa trên các hàng con của nó. Nó là nâng cấp của IjReport::Formula bởi tự động hóa việc phải thủ công gán Level. Xong hiện tại nó cũng có nhược điểm sau: Hiện tại tại thời điểm viết ReportFrame không hỗ trợ cây đa rễ. Có nghĩa là nếu 1 hàng có nhiều 1 cha thì sẽ không được tính tổng lên cả hai hàng cha, thậm chí mà nói tại thời gian viết chưa cài đặt tính năng sao cho 1 hàng có thể có nhiều cha.
5. Viết tắt
ReportFrame hỗ trợ viết tắt lại 1 số từ khóa gọn hơn thí dụ chẳng hạn {sPSCO} ở đây là tổng phát sinh có, nó được viết lại thành
SUM(LCAmount * (1 - PostType)) hay SUM(IF(PostType < 0, -LCAmount, 0))
Bảng tại thời gian viết của nó như sau:
| Viết tắt | Viết lại thành | Ý nghĩa |
| PSNO | LCAmount * (2 - PostType) | Phát sinh nợ |
| PSCO | LCAmount * (1 - PostType) | Phát sinh có |
| sPSNO | SUM(LCAmount * (2 - PostType)) | Tổng phát sinh nợ |
| sPSCO | SUM(LCAmount * (1 - PostType)) | Tổng phát sinh có |
| DUNO | GREATEST(SUM(LCAmount), 0) | Dư nợ |
| DUNOTK | IF(BalanceType = 1 OR (BalanceType != 2 AND SUM(LCAmount) > 0), SUM(LCAmount), 0) | Dư nợ theo tính chất tài khoản |
| DUCO | GREATEST(-SUM(LCAmount), 0) | Dư có |
| DUCOTK | IF(BalanceType = 2 OR (BalanceType != 1 AND SUM(LCAmount) < 0), -SUM(LCAmount), 0) | Dư có theo tính chất tài khoản |
| RNUM | ROW_NUMBER() OVER (ORDER BY SELECT 0) | Đánh thứ tự (ngẫu nhiên) |
Ngoài ra ta còn có các từ khóa khác phụ thuộc tham số truyền vào %yyyy: năm hiện tại của bộ lọc
| Viết tắt | Viết lại thành | Ý nghĩa |
| DUDK | SUM(IF(PostDate < '%yyyy-01-01', LCAmount, 0)) | Dư đầu kỳ |
| DUNODK | GREATEST(SUM(IF(PostDate < '%yyyy-01-01', LCAmount, 0)), 0) | Dư nợ đầu kỳ |
| DUCODK | GREATEST(-SUM(IF(PostDate < '%yyyy-01-01', LCAmount, 0)), 0) | Dư có đầu kỳ |
| LUYKEDN | SUM(IF(PostDate >= '%yyyy-01-01', LCAmount, 0)) | Lũy kế từ đầu năm |
No Comments