For the first tab, without any filter, the total sum of "Cash" column is -24510 (showing in red) which is right.
However if you filter the "Received by" column and choose "Nicole Chan", the total sum of "Cash" become 12,200. The value is correct which is a positive number but it is incorrectly showing as red. Please advise. Thanks.
SQL code below:
select 'dbr.tab', 'Detail payment';
select 'dbr.tab', 'Daily summary by Fee type';
select 'dbr.tab', 'Daily summary by Pay type';
select 'dbr.tab', 'Daily summary by Teacher';
select 'dbr.tab', 'Daily summary by Classroom';
select 'dbr.text', '數博研習中心 (天盛分校)';
select 'DBR.crosstab' as 'DBR.crosstab', 'Pay type' as 'Pay type';
select 'DBR.hsum' as 'DBR.hsum', 'value' as 'value';
select 'DBR.sum' as 'DBR.sum', 'value' as 'value';
select 'dbr.search', 1;
select 'dbr.summary.text', 'Received by', 'Total';
select 'dbr.subtitle', 'Payment Received History - Detail payment';
select 'DBR.text' as 'DBR.text', 'Payment record inputted from May-30, 2022 to May-30, 2022 (According to entry date 根據輸入日期)' as 'concat(\'Payment record inputted from \',date_format(From_date,\'%b-%d, %Y\') ,\' to \', date_format(To_date,\'%b-%d, %Y\') ,\' (According to \', if(as_datetype=\'E\',\'entry date 根據輸入日期)\',\'pay date 根據付款日期)\' ))';
select 'dbr.css', '.content {zoom: 0.7;}';
select 'dbr.column.filter', 'Pay date', 'select';
select 'dbr.column.filter', 'Center', 'select';
select 'dbr.column.filter', 'Student', 'text';
select 'dbr.column.filter', 'Name', 'select';
select 'dbr.column.filter', 'Enrollment/Sales remarks', 'text';
select 'dbr.column.filter', 'Account code', 'select';
select 'dbr.column.filter', 'Received by', 'select';
select 'dbr.column.filter', 'Course/Product', 'select';
select 'dbr.column.filter', 'From', 'select';
select 'dbr.column.filter', 'To', 'select';
select 'dbr.column.filter', 'Payment remarks', 'text';
select 'dbr.column.filter', 'Receipt remarks', 'text';
select 'dbr.footerstyle', ' border: 1px solid gray;';
select 'dbr.colstyle', 'Pay Ref', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'Entry time', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'Student', '[white-space:pre-wrap; border: 1px solid gray;max-width:300px;]';
select 'dbr.colstyle', 'ID', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'Course/Product', '[white-space:pre-wrap; border: 1px solid gray;max-width:300px;max-width:300px;]';
select 'dbr.colstyle', 'Name', '[white-space:pre-wrap; border: 1px solid gray;max-width:300px;max-width:300px;]';
select 'dbr.colstyle', 'Class no.', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'Payment remarks', '[white-space:pre-wrap; border: 1px solid gray; max-width:200px;max-width:200px;]';
select 'dbr.colstyle', 'Receipt remarks', '[white-space:pre-wrap; border: 1px solid gray; max-width:100px;max-width:100px;]';
select 'dbr.colstyle', 'Enrollment/Sales remarks', '[white-space:pre-wrap; border: 1px solid gray;max-width:300px;max-width:300px;]';
select 'dbr.colstyle', 'Account code', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'Pay date', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'From', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'To', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.colstyle', 'Received by', '[white-space:pre; border: 1px solid gray;]';
select 'dbr.hidecolumn', 'Date';
select 'dbr.colstyle', '[value]', '[border: 1px solid gray;white-space:pre;color: black;]%.2f;[border: 1px solid gray;color: black;]%.2f;[border: 1px solid gray;color: red;]%.2f';
select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.export.options', 'paper_size', 'A4';
select 'dbr.export.options', 'zoom_scale', 80;
select 'dbr.wkhtmltopdf', '--margin-left 5 --margin-right 5 --margin-bottom 5 ';
select 'dbr.crosstab.col', 'Cash', 'Cheque', 'Transfer';
select '4579877' as 'Pay Ref', cast('2022-05-30' as date) as 'Pay date', cast('2022-05-30 18:17:29' as datetime) as 'Entry time', '陳天瀾 CHAN TIN LAN' as 'Student', 'C2129' as 'ID', 'MR' as 'Course/Product', 'MATH 數學' as 'Name', '5' as 'Class no.', '2022-06-01' as 'From', '2022-06-29' as 'To', '' as 'Payment remarks', '' as 'Receipt remarks', '' as 'Enrollment/Sales remarks', null as 'Account code', 'NICOLE CHAN' as 'Received by', 'Cash' as 'Pay type', 1125.0 as '[value]', cast('2022-05-30' as date) as 'Date'
union all
select '4579876', cast('2022-05-30' as date), cast('2022-05-30 18:17:24' as datetime), '陳天瀾 CHAN TIN LAN', 'C2129', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 900.0, cast('2022-05-30' as date)
union all
select '4579862', cast('2022-05-30' as date), cast('2022-05-30 18:02:17' as datetime), '黃柏翹 WONG PAK KIU', 'C2164', 'NMIC', 'MISCELLANEOUS CHARGE 學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 500.0, cast('2022-05-30' as date)
union all
select '4579861', cast('2022-05-30' as date), cast('2022-05-30 18:02:13' as datetime), '黃柏翹 WONG PAK KIU', 'C2164', 'NREF', 'REGISTRATION FEE 註冊費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 80.0, cast('2022-05-30' as date)
union all
select '4579860', cast('2022-05-30' as date), cast('2022-05-30 18:02:07' as datetime), '黃柏翹 WONG PAK KIU', 'C2164', 'MR', 'MATH 數學', '5', '2022-06-01', '2022-06-29', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 1125.0, cast('2022-05-30' as date)
union all
select '4579859', cast('2022-05-30' as date), cast('2022-05-30 17:58:35' as datetime), '余嘉銘 YU KA MING', 'C1916', 'MR', 'MATH 數學', '4', '2022-06-04', '2022-06-25', '', '', '', null, 'NICOLE CHAN', 'Cash', 850.0, cast('2022-05-30' as date)
union all
select '4579858', cast('2022-05-30' as date), cast('2022-05-30 17:58:21' as datetime), '余嘉鴻 YU KA HUNG KENNY', 'C1346', 'MR', 'MATH 數學', '4', '2022-06-04', '2022-06-25', '', '', '', null, 'NICOLE CHAN', 'Cash', 900.0, cast('2022-05-30' as date)
union all
select '4579857', cast('2022-05-30' as date), cast('2022-05-30 17:58:15' as datetime), '余嘉鴻 YU KA HUNG KENNY', 'C1346', 'DRO-A', 'REFERRAL (EXISTING STUDENT) 轉介紹優惠(現讀生適用)', '0', '', '', '', '', 'C1916 余嘉銘', null, 'NICOLE CHAN', 'Cash', -300.0, cast('2022-05-30' as date)
union all
select '4579844', cast('2022-05-30' as date), cast('2022-05-30 17:47:59' as datetime), '李泰皓 LI CYRO TITAN', 'C1905', 'MR', 'MATH 數學', '5', '2022-06-01', '2022-06-29', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 1125.0, cast('2022-05-30' as date)
union all
select '4579843', cast('2022-05-30' as date), cast('2022-05-30 17:47:55' as datetime), '李泰皓 LI CYRO TITAN', 'C1905', 'DEC50', 'E-COUPON $50 $50 電子現金券', '0', '', '', '', '', '數博即時賞 (有限期:2022年6月1日至2
022年12月31日)', null, 'NICOLE CHAN', 'Cash', -50.0, cast('2022-05-30' as date)
union all
select '4579835', cast('2022-05-30' as date), cast('2022-05-30 17:41:18' as datetime), '湯子穎 TONG TSZ WING', 'C1655', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'AlipayHK', 900.0, cast('2022-05-30' as date)
union all
select '4579834', cast('2022-05-30' as date), cast('2022-05-30 17:41:11' as datetime), '湯子穎 TONG TSZ WING', 'C1655', 'DEC50', 'E-COUPON $50 $50 電子現金券', '0', '', '', '', '', '數博即時賞 (有限期:2022年6月1日至2
022年12月31日)', null, 'NICOLE CHAN', 'AlipayHK', -50.0, cast('2022-05-30' as date)
union all
select '4579830', cast('2022-05-30' as date), cast('2022-05-30 17:35:45' as datetime), '陳洛礽 CHAN LOK YING', 'C0924', 'RFMC', 'REFUND MISCELLANEOUS CHARGE 退回學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'Cash', -500.0, cast('2022-05-30' as date)
union all
select '4579829', cast('2022-05-30' as date), cast('2022-05-30 17:35:42' as datetime), '陳洛礽 CHAN LOK YING', 'C0924', 'RFVD', 'REFUND VISUALIZER DEPOSIT 退回實物投影機按金', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'Cash', -500.0, cast('2022-05-30' as date)
union all
select '4579758', cast('2022-05-30' as date), cast('2022-05-30 16:35:19' as datetime), '許澤峰 HUI CHAK FUNG, ANSON', 'C1438', 'MR', 'MATH 數學', '4', '2022-05-30', '2022-06-24', '', '', '', null, 'NICOLE CHAN', 'Cash', 900.0, cast('2022-05-30' as date)
union all
select '4579757', cast('2022-05-30' as date), cast('2022-05-30 16:35:14' as datetime), '許澤峰 HUI CHAK FUNG, ANSON', 'C1438', 'RFMC', 'REFUND MISCELLANEOUS CHARGE 退回學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'Cash', -500.0, cast('2022-05-30' as date)
union all
select '4579755', cast('2022-05-30' as date), cast('2022-05-30 16:33:26' as datetime), '楊晉熙 YEUNG CHUN HEI', 'C1129', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 850.0, cast('2022-05-30' as date)
union all
select '4579753', cast('2022-05-30' as date), cast('2022-05-30 16:29:47' as datetime), '陳熙尤 CHAN HEI YAU', 'C0930', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 900.0, cast('2022-05-30' as date)
union all
select '4579746', cast('2022-05-30' as date), cast('2022-05-30 16:23:57' as datetime), '黃玥 WONG YUET Janice', 'C2133', 'MR', 'MATH 數學', '2', '2022-06-02', '2022-06-09', '', '', '', null, 'NICOLE CHAN', 'Cash', 425.0, cast('2022-05-30' as date)
union all
select '4579745', cast('2022-05-30' as date), cast('2022-05-30 16:23:50' as datetime), '黃玥 WONG YUET Janice', 'C2133', 'RFMC', 'REFUND MISCELLANEOUS CHARGE 退回學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'Cash', -500.0, cast('2022-05-30' as date)
union all
select '4579743', cast('2022-05-30' as date), cast('2022-05-30 16:22:19' as datetime), '歐陽文宥 AU YEUNG MAN YAU', 'C2082', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 850.0, cast('2022-05-30' as date)
union all
select '4579704', cast('2022-05-30' as date), cast('2022-05-30 15:38:49' as datetime), '林宥成 LAM YAU SHING', 'C1708', 'MR', 'MATH 數學', '3', '2022-05-16', '2022-05-30', '', '', '', null, 'NICOLE CHAN', 'Cash', 675.0, cast('2022-05-30' as date)
union all
select '4579703', cast('2022-05-30' as date), cast('2022-05-30 15:38:44' as datetime), '林宥成 LAM YAU SHING', 'C1708', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 900.0, cast('2022-05-30' as date)
union all
select '4579668', cast('2022-05-30' as date), cast('2022-05-30 14:58:08' as datetime), '吳依霖 NG YEE LAM', 'C2103', 'MR', 'MATH 數學', '5', '2022-05-30', '2022-06-27', '', '', '', null, 'NICOLE CHAN', 'Cash', 1125.0, cast('2022-05-30' as date)
union all
select '4579667', cast('2022-05-30' as date), cast('2022-05-30 14:58:03' as datetime), '吳依霖 NG YEE LAM', 'C2103', 'DEC50', 'E-COUPON $50 $50 電子現金券', '0', '', '', '', '', '$50Cash Coupon no.1028
41', null, 'NICOLE CHAN', 'Cash', -50.0, cast('2022-05-30' as date)
union all
select '4579666', cast('2022-05-30' as date), cast('2022-05-30 14:55:10' as datetime), '盧梓嫣 LO TSZ YIN', 'C2044', 'MR', 'MATH 數學', '4', '2022-06-09', '2022-06-30', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 850.0, cast('2022-05-30' as date)
union all
select '4579634', cast('2022-05-30' as date), cast('2022-05-30 14:29:40' as datetime), 'TSS ', 'C0006', 'DEC1', 'E-COUPON $1 $1 電子現金券', '0', '', '', '', 'CAROL 5月工資', 'CAROL 5月工資 ', null, 'STEPHANIE HEUNG', 'Cash', -1500.0, cast('2022-05-30' as date)
union all
select '4579629', cast('2022-05-30' as date), cast('2022-05-30 14:24:27' as datetime), '李靖瑜 LEE CHING YU', 'C2162', 'MR', 'MATH 數學', '3', '2022-06-10', '2022-06-24', '', '', '', null, 'NICOLE CHAN', 'FPS', 600.0, cast('2022-05-30' as date)
union all
select '4579628', cast('2022-05-30' as date), cast('2022-05-30 14:24:23' as datetime), '李靖瑜 LEE CHING YU', 'C2162', 'NREF', 'REGISTRATION FEE 註冊費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'FPS', 80.0, cast('2022-05-30' as date)
union all
select '4579627', cast('2022-05-30' as date), cast('2022-05-30 14:24:19' as datetime), '李靖瑜 LEE CHING YU', 'C2162', 'NMIC', 'MISCELLANEOUS CHARGE 學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'FPS', 500.0, cast('2022-05-30' as date)
union all
select '4579626', cast('2022-05-30' as date), cast('2022-05-30 14:24:07' as datetime), '李卓廷 LEE CHEUK TING', 'C2163', 'NREF', 'REGISTRATION FEE 註冊費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'FPS', 80.0, cast('2022-05-30' as date)
union all
select '4579625', cast('2022-05-30' as date), cast('2022-05-30 14:23:40' as datetime), '李卓廷 LEE CHEUK TING', 'C2163', 'NMIC', 'MISCELLANEOUS CHARGE 學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'FPS', 500.0, cast('2022-05-30' as date)
union all
select '4579624', cast('2022-05-30' as date), cast('2022-05-30 14:23:31' as datetime), '李卓廷 LEE CHEUK TING', 'C2163', 'MR', 'MATH 數學', '3', '2022-06-10', '2022-06-24', '', '', '', null, 'NICOLE CHAN', 'FPS', 600.0, cast('2022-05-30' as date)
union all
select '4579621', cast('2022-05-30' as date), cast('2022-05-30 14:14:21' as datetime), '古雅喬 KU NGA KIU', 'C2101', 'RFMC', 'REFUND MISCELLANEOUS CHARGE 退回學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'Cash', -500.0, cast('2022-05-30' as date)
union all
select '4579605', cast('2022-05-30' as date), cast('2022-05-30 14:02:58' as datetime), '張靖浵 CHEUNG CHING TUNG，MINNIE', 'C2137', 'MR', 'MATH 數學', '5', '2022-06-02', '2022-06-30', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 1000.0, cast('2022-05-30' as date)
union all
select '4579604', cast('2022-05-30' as date), cast('2022-05-30 14:02:54' as datetime), '張靖浵 CHEUNG CHING TUNG，MINNIE', 'C2137', 'DRO-B', 'REFERRAL (NEW STUDENT) 轉介紹優惠(新生適用)', '0', '', '', '', '', 'C1731 張瑋誠', null, 'NICOLE CHAN', 'Cash', -200.0, cast('2022-05-30' as date)
union all
select '4579597', cast('2022-05-30' as date), cast('2022-05-30 13:39:40' as datetime), '袁卓楓 YUEN CHEUK FUNG', 'C2045', 'RFMC', 'REFUND MISCELLANEOUS CHARGE 退回學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'Cash', -500.0, cast('2022-05-30' as date)
union all
select '4579596', cast('2022-05-30' as date), cast('2022-05-30 13:39:36' as datetime), '袁卓楓 YUEN CHEUK FUNG', 'C2045', 'MR', 'MATH 數學', '4', '2022-06-06', '2022-06-27', '', '', '', null, 'NICOLE CHAN', 'Cash', 850.0, cast('2022-05-30' as date)
union all
select '4579584', cast('2022-05-30' as date), cast('2022-05-30 12:27:05' as datetime), '郭可兒 KWOK HO YEE CHLOE', 'C1852', 'MR', 'MATH 數學', '7', '2022-06-08', '2022-06-29', '', '', 'Batch renewed on 自動續報時
間2022-05-30 04:32:59', null, 'NICOLE CHAN', 'Cash', 1575.0, cast('2022-05-30' as date)
union all
select '4579583', cast('2022-05-30' as date), cast('2022-05-30 12:23:59' as datetime), 'TSS ', 'C0006', 'DEC1', 'E-COUPON $1 $1 電子現金券', '0', '', '', '', 'AMY 5月工資 153 X $70', 'AMY 5月工資 153 X $70', null, 'STEPHANIE HEUNG', 'Cash', -10710.0, cast('2022-05-30' as date)
union all
select '4579573', cast('2022-05-30' as date), cast('2022-05-30 12:04:23' as datetime), 'TSS ', 'C0006', 'DEC1', 'E-COUPON $1 $1 電子現金券', '0', '', '', '', 'CLEANER 5月工資 ', 'CLEANER 5月工資 ', null, 'STEPHANIE HEUNG', 'Cash', -2800.0, cast('2022-05-30' as date)
union all
select '4579572', cast('2022-05-30' as date), cast('2022-05-30 12:03:26' as datetime), 'TSS ', 'C0006', 'DEC1', 'E-COUPON $1 $1 電子現金券', '0', '', '', '', 'RENEE 5月工資 ', 'RENEE 5月工資 ', null, 'STEPHANIE HEUNG', 'Cash', -8500.0, cast('2022-05-30' as date)
union all
select '4579567', cast('2022-05-30' as date), cast('2022-05-30 12:01:02' as datetime), 'TSS ', 'C0006', 'DEC1', 'E-COUPON $1 $1 電子現金券', '0', '', '', '', 'NICOLE 5月工資 ', 'NICOLE 5月工資 ', null, 'STEPHANIE HEUNG', 'Cash', -13200.0, cast('2022-05-30' as date)
union all
select '4579564', cast('2022-05-30' as date), cast('2022-05-30 11:56:59' as datetime), '張曦而 CHEUNG HEI YEE HAYLEY', 'C2161', 'NMIC', 'MISCELLANEOUS CHARGE 學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 500.0, cast('2022-05-30' as date)
union all
select '4579563', cast('2022-05-30' as date), cast('2022-05-30 11:56:55' as datetime), '張曦而 CHEUNG HEI YEE HAYLEY', 'C2161', 'NREF', 'REGISTRATION FEE 註冊費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 80.0, cast('2022-05-30' as date)
union all
select '4579561', cast('2022-05-30' as date), cast('2022-05-30 11:56:48' as datetime), '張曦而 CHEUNG HEI YEE HAYLEY', 'C2161', 'MR', 'MATH 數學', '5', '2022-06-02', '2022-06-30', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 1000.0, cast('2022-05-30' as date)
union all
select '4579553', cast('2022-05-30' as date), cast('2022-05-30 11:20:57' as datetime), '李倀嶠 LI CHUN KIU', 'C2160', 'NREF', 'REGISTRATION FEE 註冊費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 80.0, cast('2022-05-30' as date)
union all
select '4579552', cast('2022-05-30' as date), cast('2022-05-30 11:20:54' as datetime), '李倀嶠 LI CHUN KIU', 'C2160', 'NMIC', 'MISCELLANEOUS CHARGE 學雜費', '0', '', '', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 500.0, cast('2022-05-30' as date)
union all
select '4579551', cast('2022-05-30' as date), cast('2022-05-30 11:20:46' as datetime), '李倀嶠 LI CHUN KIU', 'C2160', 'MR', 'MATH 數學', '5', '2022-05-30', '2022-06-27', '', '', '', null, 'NICOLE CHAN', 'AlipayHK', 1062.5, cast('2022-05-30' as date);
select 'dbr.tab.next';
select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.text', '數博研習中心 (天盛分校)';
select 'DBR.text' as 'DBR.text', 'Payment record inputted from May-30, 2022 to May-30, 2022 (According to entry date 根據輸入日期)' as 'concat(\'Payment record inputted from \',date_format(From_date,\'%b-%d, %Y\') ,\' to \', date_format(To_date,\'%b-%d, %Y\') ,\' (According to \', if(as_datetype=\'E\',\'entry date 根據輸入日期)\',\'pay date 根據付款日期)\' ))';
select 'DBR.text' as 'DBR.text', 'To define summer course or related product, put the keyword \'Summer\' in the remarks of the course or product setup' as 'To define summer course or related product, put the keyword \'Summer\' in the remarks of the course or product setup', 'comment' as 'comment';
select 'dbr.subtitle', 'Payment Received History - Daily Summary by Fee Type';
select 'dbr.summary.text', 'Date', 'Total';
select 'DBR.sum' as 'DBR.sum', 'Course fee' as 'Course fee', 'Product fee' as 'Product fee', 'Total' as 'Total';
select 'dbr.search', 1;
select 'dbr.hdr', 'Date';
select 'dbr.summary.options', 'limit_summary_level', 0;
select 'dbr.summary.options', 'skip_single_line_summary';
select 'dbr.crosstab', 'Summer/Non-Summer';
select 'dbr.column.filter', 'Date', 'select';
select cast('2022-05-30' as date) as 'Date', 'Non-summer' as 'Summer/Non-Summer', -16372.5 as 'Total', 21087.5 as 'Course fee', -37460.0 as 'Product fee';
select 'dbr.tab.next';
select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.export.options', 'paper_size', 'A4';
select 'dbr.export.options', 'zoom_scale', 50;
select 'dbr.text', '數博研習中心 (天盛分校)';
select 'DBR.text' as 'DBR.text', 'Payment record inputted from May-30, 2022 to May-30, 2022 (According to entry date 根據輸入日期)' as 'concat(\'Payment record inputted from \',date_format(From_date,\'%b-%d, %Y\') ,\' to \', date_format(To_date,\'%b-%d, %Y\') ,\' (According to \', if(as_datetype=\'E\',\'entry date 根據輸入日期)\',\'pay date 根據付款日期)\' ))';
select 'dbr.search', 1;
select 'dbr.subtitle', 'Payment Received History - Daily Summary by Pay Type';
select 'dbr.crosstab', 'Pay type';
select 'dbr.column.filter', 'Date', 'select';
select 'dbr.sum', '[value]';
select 'dbr.crosstab.col', 'Total';
select cast('2022-05-30' as date) as 'Date', 'Total' as 'Pay type', -16372.5 as '[value]'
union all
select cast('2022-05-30' as date), 'FPS', 2360.0
union all
select cast('2022-05-30' as date), 'Cash', -24510.0
union all
select cast('2022-05-30' as date), 'AlipayHK', 5777.5;
select 'dbr.tab.next';
select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.export.options', 'paper_size', 'A4';
select 'dbr.export.options', 'zoom_scale', 50;
select 'dbr.text', '數博研習中心 (天盛分校)';
select 'DBR.text' as 'DBR.text', 'Payment record inputted from May-30, 2022 to May-30, 2022 (According to entry date 根據輸入日期)' as 'concat(\'Payment record inputted from \',date_format(From_date,\'%b-%d, %Y\') ,\' to \', date_format(To_date,\'%b-%d, %Y\') ,\' (According to \', if(as_datetype=\'E\',\'entry date 根據輸入日期)\',\'pay date 根據付款日期)\' ))';
select 'dbr.search', 1;
select 'dbr.subtitle', 'Payment Received History - Daily Summary by Teacher';
select 'dbr.crosstab', 'Teacher (1st class teacher at pay time)';
select 'dbr.column.filter', 'Date', 'select';
select 'dbr.sum', '[value]';
select 'dbr.crosstab.col', 'Total';
select cast('2022-05-30' as date) as 'Date', 'WING KWONG LAU' as 'Teacher (1st class teacher at pay time)', 1912.5 as '[value]'
union all
select cast('2022-05-30' as date), 'Total', 21087.5
union all
select cast('2022-05-30' as date), 'STEPHANIE HEUNG', 850.0
union all
select cast('2022-05-30' as date), 'RENEE NGAI', 1000.0
union all
select cast('2022-05-30' as date), 'MICHAEL LAM', 900.0
union all
select cast('2022-05-30' as date), 'LOK MAN CHAN', 4675.0
union all
select cast('2022-05-30' as date), 'KEVIN LEW', 1575.0
union all
select cast('2022-05-30' as date), 'JOE HO ', 5125.0
union all
select cast('2022-05-30' as date), 'HO CIRCLE', 2850.0
union all
select cast('2022-05-30' as date), null, 2200.0;
select 'dbr.tab.next';
select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.export.options', 'paper_size', 'A4';
select 'dbr.export.options', 'zoom_scale', 50;
select 'dbr.text', '數博研習中心 (天盛分校)';
select 'DBR.text' as 'DBR.text', 'Payment record inputted from May-30, 2022 to May-30, 2022 (According to entry date 根據輸入日期)' as 'concat(\'Payment record inputted from \',date_format(From_date,\'%b-%d, %Y\') ,\' to \', date_format(To_date,\'%b-%d, %Y\') ,\' (According to \', if(as_datetype=\'E\',\'entry date 根據輸入日期)\',\'pay date 根據付款日期)\' ))';
select 'dbr.search', 1;
select 'dbr.subtitle', 'Payment Received History - Daily Summary by Classroom';
select 'dbr.crosstab', 'Classroom (1st class classroom at pay time)';
select 'dbr.column.filter', 'Date', 'select';
select 'dbr.sum', '[value]';
select 'dbr.crosstab.col', 'Total';
select cast('2022-05-30' as date) as 'Date', 'Total' as 'Classroom (1st class classroom at pay time)', 21087.5 as '[value]'
union all
select cast('2022-05-30' as date), 'TBD', 21087.5;
