MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE acc_transactions (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  debit DECIMAL(15, 2) NOT NULL,
  credit DECIMAL(15, 2) NOT NULL,
  transaction_date DATETIME NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO acc_transactions(id, debit, credit, transaction_date) VALUES
(1, 187321970.48, 175143561.74, '2019-12-31 00:00:00'),
(19, 13500.00, 13500.00, '2020-04-29 00:00:00'),
(20, 13500.00, 13500.00, '2020-04-29 00:00:00'),
(21, 195500.00, 195500.00, '2020-05-28 00:00:00'),
(22, 85000.00, 85000.00, '2020-05-05 00:00:00'),
(24, 85000.00, 85000.00, '2020-05-05 00:00:00'),
(25, 100000.00, 100000.00, '2020-05-05 00:00:00'),
(26, 3000000.00, 3000000.00, '2020-05-06 00:00:00'),
(27, 1105500.00, 1105500.00, '2020-05-06 00:00:00');
SELECT * FROM acc_transactions;



id
debit
credit
transaction_date


1
187321970.48
175143561.74
2019-12-31 00:00:00

19
13500.00
13500.00
2020-04-29 00:00:00

20
13500.00
13500.00
2020-04-29 00:00:00

21
195500.00
195500.00
2020-05-28 00:00:00

22
85000.00
85000.00
2020-05-05 00:00:00

24
85000.00
85000.00
2020-05-05 00:00:00

25
100000.00
100000.00
2020-05-05 00:00:00

26
3000000.00
3000000.00
2020-05-06 00:00:00

27
1105500.00
1105500.00
2020-05-06 00:00:00


CREATE TABLE acc_account_transactions (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  account_no INT(11) NOT NULL,
  transaction_id INT(11) NOT NULL,
  debit_balance DECIMAL(15, 2) NOT NULL,
  credit_balance DECIMAL(15, 2) NOT NULL,
  opn_flag INT(11) NOT NULL,
  close_flag INT(11) NOT NULL,
  note VARCHAR(191) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO acc_account_transactions(id, account_no, transaction_id, debit_balance, credit_balance, opn_flag, close_flag, note) VALUES
(1, 13, 1, 2578.19, 0.00, 1, 0, 'OPN'),
(2, 14, 19, 33458.43, 0.00, 1, 0, 'OPN'),
(3, 15, 20, 18506.34, 0.00, 1, 0, 'OPN'),
(4, 12, 21, 946.20, 0.00, 1, 0, 'OPN'),
(5, 13, 22, 90364.77, 0.00, 1, 0, 'OPN'),
(6, 14, 24, 0.00, 0.00, 1, 0, 'OPN'),
(7, 15, 25, 0.00, 0.00, 1, 0, 'OPN'),
(8, 11, 26, 0.00, 0.00, 1, 0, 'OPN'),
(9, 12, 27, 1327.00, 0.00, 1, 0, 'OPN'),
(10,13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(11,14, 1, 0.00, 0.00, 1, 0, 'OPN'),
(12,15, 1, 0.00, 0.00, 1, 0, 'OPN'),
(14, 1, 1, 983661.00, 0.00, 1, 0, 'OPN'),
(15, 2, 1, 87667.00, 0.00, 1, 0, 'OPN'),
(16, 3, 19, 16272.00, 0.00, 1, 0, 'OPN'),
(17, 4, 20, 0.00, 0.00, 1, 0, 'OPN'),
(18, 5, 21, 15400.50, 0.00, 1, 0, 'OPN'),
(19, 6, 22, 5632.69, 0.00, 1, 0, 'OPN'),
(20, 7, 24, 5938.50, 0.00, 1, 0, 'OPN'),
(21, 8, 25, 0.00, 0.00, 1, 0, 'OPN'),
(22, 9, 26, 0.00, 0.00, 1, 0, 'OPN'),
(23, 1, 27, 68996993.00, 0.00, 1, 0, 'OPN'),
(24, 2, 1, 0.00, 12142237.42, 1, 0, 'OPN'),
(25, 3, 1, 49999085.00, 0.00, 1, 0, 'OPN'),
(26, 4, 1, 1333813.00, 0.00, 1, 0, 'OPN'),
(27, 5, 1, 0.00, 378980.86, 1, 0, 'OPN'),
(28, 6, 1, 37536.00, 0.00, 1, 0, 'OPN'),
(29, 7, 1, 310794.00, 0.00, 1, 0, 'OPN'),
(30, 8, 1, 52966.00, 0.00, 1, 0, 'OPN'),
(31, 9, 1, 166103.00, 0.00, 1, 0, 'OPN'),
(32, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(33, 13, 1, 73825.00, 0.00, 1, 0, 'OPN'),
(34, 13, 1, 4050.00, 0.00, 1, 0, 'OPN'),
(35, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(36, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(37, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(38, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(39, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(40, 13, 1, 0.00, 0.00, 1, 0, 'OPN'),
(41, 15, 1, 4420119.00, 0.00, 1, 0, 'OPN'),
(42, 15, 1, 0.00, 0.00, 1, 0, 'OPN'),
(43, 15, 19, 0.00, 0.00, 1, 0, 'OPN'),
(44, 15, 20, 0.00, 0.00, 1, 0, 'OPN'),
(45, 15, 21, 1674.89, 0.00, 1, 0, 'OPN'),
(46, 2, 22, 10429.71, 0.00, 1, 0, 'OPN'),
(47, 2, 24, 9635.12, 0.00, 1, 0, 'OPN'),
(48, 2, 25, 7628.51, 0.00, 1, 0, 'OPN'),
(49, 3, 26, 37500.00, 0.00, 1, 0, 'OPN'),
(50, 3, 27, 0.00, 0.00, 1, 0, 'OPN'),
(51, 3, 1, 151300.00, 0.00, 1, 0, 'OPN');




SELECT * FROM acc_account_transactions;


id
account_no
transaction_id
debit_balance
credit_balance
opn_flag
close_flag
note


1
13
1
2578.19
0.00
1
0
OPN

2
14
19
33458.43
0.00
1
0
OPN

3
15
20
18506.34
0.00
1
0
OPN

4
12
21
946.20
0.00
1
0
OPN

5
13
22
90364.77
0.00
1
0
OPN

6
14
24
0.00
0.00
1
0
OPN

7
15
25
0.00
0.00
1
0
OPN

8
11
26
0.00
0.00
1
0
OPN

9
12
27
1327.00
0.00
1
0
OPN

10
13
1
0.00
0.00
1
0
OPN

11
14
1
0.00
0.00
1
0
OPN

12
15
1
0.00
0.00
1
0
OPN

14
1
1
983661.00
0.00
1
0
OPN

15
2
1
87667.00
0.00
1
0
OPN

16
3
19
16272.00
0.00
1
0
OPN

17
4
20
0.00
0.00
1
0
OPN

18
5
21
15400.50
0.00
1
0
OPN

19
6
22
5632.69
0.00
1
0
OPN

20
7
24
5938.50
0.00
1
0
OPN

21
8
25
0.00
0.00
1
0
OPN

22
9
26
0.00
0.00
1
0
OPN

23
1
27
68996993.00
0.00
1
0
OPN

24
2
1
0.00
12142237.42
1
0
OPN

25
3
1
49999085.00
0.00
1
0
OPN

26
4
1
1333813.00
0.00
1
0
OPN

27
5
1
0.00
378980.86
1
0
OPN

28
6
1
37536.00
0.00
1
0
OPN

29
7
1
310794.00
0.00
1
0
OPN

30
8
1
52966.00
0.00
1
0
OPN

31
9
1
166103.00
0.00
1
0
OPN

32
13
1
0.00
0.00
1
0
OPN

33
13
1
73825.00
0.00
1
0
OPN

34
13
1
4050.00
0.00
1
0
OPN

35
13
1
0.00
0.00
1
0
OPN

36
13
1
0.00
0.00
1
0
OPN

37
13
1
0.00
0.00
1
0
OPN

38
13
1
0.00
0.00
1
0
OPN

39
13
1
0.00
0.00
1
0
OPN

40
13
1
0.00
0.00
1
0
OPN

41
15
1
4420119.00
0.00
1
0
OPN

42
15
1
0.00
0.00
1
0
OPN

43
15
19
0.00
0.00
1
0
OPN

44
15
20
0.00
0.00
1
0
OPN

45
15
21
1674.89
0.00
1
0
OPN

46
2
22
10429.71
0.00
1
0
OPN

47
2
24
9635.12
0.00
1
0
OPN

48
2
25
7628.51
0.00
1
0
OPN

49
3
26
37500.00
0.00
1
0
OPN

50
3
27
0.00
0.00
1
0
OPN

51
3
1
151300.00
0.00
1
0
OPN


CREATE TABLE acc_accounts (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_account_number VARCHAR(191) NOT NULL,
  name LONGTEXT BINARY CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(name)),
  account_type INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO acc_accounts(id, user_account_number, name, account_type) VALUES
(1, '001', '{"ar": "الموجودات المتداولة", "en": "Current Assets"}', 1),
(2, '002', '{"ar": "الموجودات غير المتداولة", "en": "Non Current Assets"}', 2),
(3, '003', '{"ar": "المطلوبات المتداولة", "en": "Current Liabilitues"}', 4),
(4, '004', '{"ar": "المطلوبات الغير متداولة", "en": "Non Current Libilities"}', 5),
(5, '005', '{"ar": "حقوق الملكية", "en": "Owners Equity"}', 6),
(6, '006', '{"ar": "الايرادات", "en": "Revenues"}', 7),
(7, '007', '{"ar": "تكلفة الايرادات", "en": "Cost of Revenues"}', 8),
(8, '008', '{"ar": "مصاريف ادارية وعمومية", "en": "General and administrative expenses"}', 9),
(9, '009', '{"ar": "مصاريف بيع وتسويق", "en": "Marketing and sales expenses"}', 1),
(10, '010', '{"ar": "ايرادات أخرى", "en": "Other Revenues"}', 1),
(11, '011', '{"ar": "مخصصات زكاة وضرائب", "en": "Zakat and taxation"}', 1),
(12, '001-001', '{"ar": "البنوك", "en": "Banks"}', 1),
(13, '001-001-001', '{"ar": "البنك السعودي الفرنسي", "en": "Banque Saudi Fransi"}', 1),
(14, '001-001-002', '{"ar": "البنك العربي الوطني", "en": "Arab National Bank"}', 1),
(15, '001-001-003', '{"ar": "مصرف الراجحي حساب رقم 085 - الخدمات", "en": "Rajhi Bank Services"}', 1);




SELECT * FROM acc_accounts;


id
user_account_number
name
account_type


1
001
{"ar": "الموجودات المتداولة", "en": "Current Assets"}
1

2
002
{"ar": "الموجودات غير المتداولة", "en": "Non Current Assets"}
2

3
003
{"ar": "المطلوبات المتداولة", "en": "Current Liabilitues"}
4

4
004
{"ar": "المطلوبات الغير متداولة", "en": "Non Current Libilities"}
5

5
005
{"ar": "حقوق الملكية", "en": "Owners Equity"}
6

6
006
{"ar": "الايرادات", "en": "Revenues"}
7

7
007
{"ar": "تكلفة الايرادات", "en": "Cost of Revenues"}
8

8
008
{"ar": "مصاريف ادارية وعمومية", "en": "General and administrative expenses"}
9

9
009
{"ar": "مصاريف بيع وتسويق", "en": "Marketing and sales expenses"}
1

10
010
{"ar": "ايرادات أخرى", "en": "Other Revenues"}
1

11
011
{"ar": "مخصصات زكاة وضرائب", "en": "Zakat and taxation"}
1

12
001-001
{"ar": "البنوك", "en": "Banks"}
1

13
001-001-001
{"ar": "البنك السعودي الفرنسي", "en": "Banque Saudi Fransi"}
1

14
001-001-002
{"ar": "البنك العربي الوطني", "en": "Arab National Bank"}
1

15
001-001-003
{"ar": "مصرف الراجحي حساب رقم 085 - الخدمات", "en": "Rajhi Bank Services"}
1


CREATE TABLE acc_finacial_periods (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name LONGTEXT BINARY CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(name)),
  startdate DATE NOT NULL,
  enddate DATE NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO acc_finacial_periods(id, name, startdate, enddate) VALUES
(3, '{"ar": "الفترة المالية 2019 2020", "en": "f period 2019 2020"}', '2020-01-01', '2021-01-31');



SELECT * FROM acc_finacial_periods;


id
name
startdate
enddate


3
{"ar": "الفترة المالية 2019 2020", "en": "f period 2019 2020"}
2020-01-01
2021-01-31


SELECT 
    acc_account_transactions.id,
    account_no,
    account_type,
    transaction_id,
    DATE_FORMAT(acc_transactions.transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(acc_finacial_periods.id, - 1),
    (@current_period_id:=IF(IFNULL(acc_finacial_periods.id, - 1) <> @current_period_id,
        IFNULL(acc_finacial_periods.id, - 1),
        @current_period_id)) current_period_id,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no,        
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(acc_finacial_periods.id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal
        ,

    (@running_balance:=@running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) AS running_balance
FROM
    (SELECT 
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r,
    (SELECT * FROM acc_account_transactions ORDER BY account_no,id LIMIT 18446744073709551615 ) acc_account_transactions
        INNER JOIN
    acc_transactions ON acc_transactions.id = acc_account_transactions.transaction_id
        INNER JOIN
    acc_accounts ON acc_accounts.id = acc_account_transactions.account_no
        LEFT JOIN
    acc_finacial_periods ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no , acc_account_transactions.id


id
account_no
account_type
transaction_id
transaction_date
debit_balance
credit_balance
IFNULL(acc_finacial_periods.id, - 1)
current_period_id
curr_account_no
prev_runnng_bal
running_balance


14
1
1
1
31/12/2019
983661.00
0.00
-1
-1
15
113370325.57
114353986.57

23
1
1
27
06/05/2020
68996993.00
0.00
3
3
15
254714.66
69251707.66

15
2
2
1
31/12/2019
87667.00
0.00
-1
-1
15
113282658.57
113370325.57

24
2
2
1
31/12/2019
0.00
12142237.42
-1
-1
15
125424895.99
113282658.57

46
2
2
22
05/05/2020
10429.71
0.00
3
3
15
182255.82
192685.53

47
2
2
24
05/05/2020
9635.12
0.00
3
3
15
198624.03
208259.15

48
2
2
25
05/05/2020
7628.51
0.00
3
3
15
208259.15
215887.66

16
3
4
19
29/04/2020
16272.00
0.00
3
3
15
33458.43
49730.43

25
3
4
1
31/12/2019
49999085.00
0.00
-1
-1
15
75425810.99
125424895.99

49
3
4
26
06/05/2020
37500.00
0.00
3
3
15
215887.66
253387.66

50
3
4
27
06/05/2020
0.00
0.00
3
3
15
254714.66
254714.66

51
3
4
1
31/12/2019
151300.00
0.00
-1
-1
15
75274510.99
75425810.99

17
4
5
20
29/04/2020
0.00
0.00
3
3
15
68236.77
68236.77

26
4
5
1
31/12/2019
1333813.00
0.00
-1
-1
15
73940697.99
75274510.99

18
5
6
21
28/05/2020
15400.50
0.00
3
3
15
70857.86
86258.36

27
5
6
1
31/12/2019
0.00
378980.86
-1
-1
15
74319678.85
73940697.99

19
6
7
22
05/05/2020
5632.69
0.00
3
3
15
176623.13
182255.82

28
6
7
1
31/12/2019
37536.00
0.00
-1
-1
15
74282142.85
74319678.85

20
7
8
24
05/05/2020
5938.50
0.00
3
3
15
192685.53
198624.03

29
7
8
1
31/12/2019
310794.00
0.00
-1
-1
15
73971348.85
74282142.85

21
8
9
25
05/05/2020
0.00
0.00
3
3
15
208259.15
208259.15

30
8
9
1
31/12/2019
52966.00
0.00
-1
-1
15
73918382.85
73971348.85

22
9
1
26
06/05/2020
0.00
0.00
3
3
15
215887.66
215887.66

31
9
1
1
31/12/2019
166103.00
0.00
-1
-1
15
73752279.85
73918382.85

8
11
1
26
06/05/2020
0.00
0.00
3
3
15
215887.66
215887.66

4
12
1
21
28/05/2020
946.20
0.00
3
3
15
69911.66
70857.86

9
12
1
27
06/05/2020
1327.00
0.00
3
3
15
253387.66
254714.66

1
13
1
1
31/12/2019
2578.19
0.00
-1
-1
15
73749701.66
73752279.85

5
13
1
22
05/05/2020
90364.77
0.00
3
3
15
86258.36
176623.13

10
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73749701.66
73749701.66

32
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73749701.66
73749701.66

33
13
1
1
31/12/2019
73825.00
0.00
-1
-1
15
73675876.66
73749701.66

34
13
1
1
31/12/2019
4050.00
0.00
-1
-1
15
73671826.66
73675876.66

35
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

36
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

37
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

38
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

39
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

40
13
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

2
14
1
19
29/04/2020
33458.43
0.00
3
3
15
0
33458.43

6
14
1
24
05/05/2020
0.00
0.00
3
3
15
192685.53
192685.53

11
14
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

3
15
1
20
29/04/2020
18506.34
0.00
3
3
15
49730.43
68236.77

7
15
1
25
05/05/2020
0.00
0.00
3
3
15
208259.15
208259.15

12
15
1
1
31/12/2019
0.00
0.00
-1
-1
15
73671826.66
73671826.66

41
15
1
1
31/12/2019
4420119.00
0.00
-1
-1
15
69251707.66
73671826.66

42
15
1
1
31/12/2019
0.00
0.00
-1
-1
15
69251707.66
69251707.66

43
15
1
19
29/04/2020
0.00
0.00
3
3
15
0
0

44
15
1
20
29/04/2020
0.00
0.00
3
3
15
49730.43
49730.43

45
15
1
21
28/05/2020
1674.89
0.00
3
3
15
68236.77
69911.66


SELECT  id,account_no,account_type,transaction_id,
    DATE_FORMAT(transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(afp_id, - 1),
    (@current_period_id:=IF(IFNULL(afp_id, - 1) <> @current_period_id,
        IFNULL(afp_id, - 1),
        @current_period_id)) current_period_id,        
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(afp_id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal
        ,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no
        ,

    (@running_balance:=@running_balance 
    + (debit_balance - credit_balance)) AS running_balance
FROM

    (SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM 
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1
    ,(SELECT 
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r


id
account_no
account_type
transaction_id
transaction_date
debit_balance
credit_balance
IFNULL(afp_id, - 1)
current_period_id
prev_runnng_bal
curr_account_no
running_balance


14
1
1
1
31/12/2019
983661.00
0.00
-1
-1
0
1
983661

23
1
1
27
06/05/2020
68996993.00
0.00
3
3
983661
1
69980654

15
2
2
1
31/12/2019
87667.00
0.00
-1
-1
0
2
87667

24
2
2
1
31/12/2019
0.00
12142237.42
-1
-1
87667
2
-12054570.42

46
2
2
22
05/05/2020
10429.71
0.00
3
3
-12054570.42
2
-12044140.709999999

47
2
2
24
05/05/2020
9635.12
0.00
3
3
-12044140.709999999
2
-12034505.59

48
2
2
25
05/05/2020
7628.51
0.00
3
3
-12034505.59
2
-12026877.08

16
3
4
19
29/04/2020
16272.00
0.00
3
3
0
3
16272

25
3
4
1
31/12/2019
49999085.00
0.00
-1
-1
16272
3
50015357

49
3
4
26
06/05/2020
37500.00
0.00
3
3
50015357
3
50052857

50
3
4
27
06/05/2020
0.00
0.00
3
3
50052857
3
50052857

51
3
4
1
31/12/2019
151300.00
0.00
-1
-1
50052857
3
50204157

17
4
5
20
29/04/2020
0.00
0.00
3
3
0
4
0

26
4
5
1
31/12/2019
1333813.00
0.00
-1
-1
0
4
1333813

18
5
6
21
28/05/2020
15400.50
0.00
3
3
0
5
15400.5

27
5
6
1
31/12/2019
0.00
378980.86
-1
-1
15400.5
5
-363580.36

19
6
7
22
05/05/2020
5632.69
0.00
3
3
0
6
5632.69

28
6
7
1
31/12/2019
37536.00
0.00
-1
-1
5632.69
6
43168.69

20
7
8
24
05/05/2020
5938.50
0.00
3
3
0
7
5938.5

29
7
8
1
31/12/2019
310794.00
0.00
-1
-1
5938.5
7
316732.5

21
8
9
25
05/05/2020
0.00
0.00
3
3
0
8
0

30
8
9
1
31/12/2019
52966.00
0.00
-1
-1
0
8
52966

22
9
1
26
06/05/2020
0.00
0.00
3
3
0
9
0

31
9
1
1
31/12/2019
166103.00
0.00
-1
-1
0
9
166103

8
11
1
26
06/05/2020
0.00
0.00
3
3
0
11
0

4
12
1
21
28/05/2020
946.20
0.00
3
3
0
12
946.2

9
12
1
27
06/05/2020
1327.00
0.00
3
3
946.2
12
2273.2

1
13
1
1
31/12/2019
2578.19
0.00
-1
-1
0
13
2578.19

5
13
1
22
05/05/2020
90364.77
0.00
3
3
2578.19
13
92942.96

10
13
1
1
31/12/2019
0.00
0.00
-1
-1
92942.96
13
92942.96

32
13
1
1
31/12/2019
0.00
0.00
-1
-1
92942.96
13
92942.96

33
13
1
1
31/12/2019
73825.00
0.00
-1
-1
92942.96
13
166767.96000000002

34
13
1
1
31/12/2019
4050.00
0.00
-1
-1
166767.96000000002
13
170817.96000000002

35
13
1
1
31/12/2019
0.00
0.00
-1
-1
170817.96000000002
13
170817.96000000002

36
13
1
1
31/12/2019
0.00
0.00
-1
-1
170817.96000000002
13
170817.96000000002

37
13
1
1
31/12/2019
0.00
0.00
-1
-1
170817.96000000002
13
170817.96000000002

38
13
1
1
31/12/2019
0.00
0.00
-1
-1
170817.96000000002
13
170817.96000000002

39
13
1
1
31/12/2019
0.00
0.00
-1
-1
170817.96000000002
13
170817.96000000002

40
13
1
1
31/12/2019
0.00
0.00
-1
-1
170817.96000000002
13
170817.96000000002

2
14
1
19
29/04/2020
33458.43
0.00
3
3
0
14
33458.43

6
14
1
24
05/05/2020
0.00
0.00
3
3
33458.43
14
33458.43

11
14
1
1
31/12/2019
0.00
0.00
-1
-1
33458.43
14
33458.43

3
15
1
20
29/04/2020
18506.34
0.00
3
3
0
15
18506.34

7
15
1
25
05/05/2020
0.00
0.00
3
3
18506.34
15
18506.34

12
15
1
1
31/12/2019
0.00
0.00
-1
-1
18506.34
15
18506.34

41
15
1
1
31/12/2019
4420119.00
0.00
-1
-1
18506.34
15
4438625.34

42
15
1
1
31/12/2019
0.00
0.00
-1
-1
4438625.34
15
4438625.34

43
15
1
19
29/04/2020
0.00
0.00
3
3
4438625.34
15
4438625.34

44
15
1
20
29/04/2020
0.00
0.00
3
3
4438625.34
15
4438625.34

45
15
1
21
28/05/2020
1674.89
0.00
3
3
4438625.34
15
4440300.2299999995

id	debit	credit	transaction_date
1	187321970.48	175143561.74	2019-12-31 00:00:00
19	13500.00	13500.00	2020-04-29 00:00:00
20	13500.00	13500.00	2020-04-29 00:00:00
21	195500.00	195500.00	2020-05-28 00:00:00
22	85000.00	85000.00	2020-05-05 00:00:00
24	85000.00	85000.00	2020-05-05 00:00:00
25	100000.00	100000.00	2020-05-05 00:00:00
26	3000000.00	3000000.00	2020-05-06 00:00:00
27	1105500.00	1105500.00	2020-05-06 00:00:00

id	account_no	transaction_id	debit_balance	credit_balance	opn_flag	note
1	13	1	2578.19	0.00	1	OPN
2	14	19	33458.43	0.00	1	OPN
3	15	20	18506.34	0.00	1	OPN
4	12	21	946.20	0.00	1	OPN
5	13	22	90364.77	0.00	1	OPN
6	14	24	0.00	0.00	1	OPN
7	15	25	0.00	0.00	1	OPN
8	11	26	0.00	0.00	1	OPN
9	12	27	1327.00	0.00	1	OPN
10	13	1	0.00	0.00	1	OPN
11	14	1	0.00	0.00	1	OPN
12	15	1	0.00	0.00	1	OPN
14	1	1	983661.00	0.00	1	OPN
15	2	1	87667.00	0.00	1	OPN
16	3	19	16272.00	0.00	1	OPN
17	4	20	0.00	0.00	1	OPN
18	5	21	15400.50	0.00	1	OPN
19	6	22	5632.69	0.00	1	OPN
20	7	24	5938.50	0.00	1	OPN
21	8	25	0.00	0.00	1	OPN
22	9	26	0.00	0.00	1	OPN
23	1	27	68996993.00	0.00	1	OPN
24	2	1	0.00	12142237.42	1	OPN
25	3	1	49999085.00	0.00	1	OPN
26	4	1	1333813.00	0.00	1	OPN
27	5	1	0.00	378980.86	1	OPN
28	6	1	37536.00	0.00	1	OPN
29	7	1	310794.00	0.00	1	OPN
30	8	1	52966.00	0.00	1	OPN
31	9	1	166103.00	0.00	1	OPN
32	13	1	0.00	0.00	1	OPN
33	13	1	73825.00	0.00	1	OPN
34	13	1	4050.00	0.00	1	OPN
35	13	1	0.00	0.00	1	OPN
36	13	1	0.00	0.00	1	OPN
37	13	1	0.00	0.00	1	OPN
38	13	1	0.00	0.00	1	OPN
39	13	1	0.00	0.00	1	OPN
40	13	1	0.00	0.00	1	OPN
41	15	1	4420119.00	0.00	1	OPN
42	15	1	0.00	0.00	1	OPN
43	15	19	0.00	0.00	1	OPN
44	15	20	0.00	0.00	1	OPN
45	15	21	1674.89	0.00	1	OPN
46	2	22	10429.71	0.00	1	OPN
47	2	24	9635.12	0.00	1	OPN
48	2	25	7628.51	0.00	1	OPN
49	3	26	37500.00	0.00	1	OPN
50	3	27	0.00	0.00	1	OPN
51	3	1	151300.00	0.00	1	OPN

id	user_account_number	name	account_type
1	001	{"ar": "الموجودات المتداولة", "en": "Current Assets"}	1
2	002	{"ar": "الموجودات غير المتداولة", "en": "Non Current Assets"}	2
3	003	{"ar": "المطلوبات المتداولة", "en": "Current Liabilitues"}	4
4	004	{"ar": "المطلوبات الغير متداولة", "en": "Non Current Libilities"}	5
5	005	{"ar": "حقوق الملكية", "en": "Owners Equity"}	6
6	006	{"ar": "الايرادات", "en": "Revenues"}	7
7	007	{"ar": "تكلفة الايرادات", "en": "Cost of Revenues"}	8
8	008	{"ar": "مصاريف ادارية وعمومية", "en": "General and administrative expenses"}	9
9	009	{"ar": "مصاريف بيع وتسويق", "en": "Marketing and sales expenses"}	1
10	010	{"ar": "ايرادات أخرى", "en": "Other Revenues"}	1
11	011	{"ar": "مخصصات زكاة وضرائب", "en": "Zakat and taxation"}	1
12	001-001	{"ar": "البنوك", "en": "Banks"}	1
13	001-001-001	{"ar": "البنك السعودي الفرنسي", "en": "Banque Saudi Fransi"}	1
14	001-001-002	{"ar": "البنك العربي الوطني", "en": "Arab National Bank"}	1
15	001-001-003	{"ar": "مصرف الراجحي حساب رقم 085 - الخدمات", "en": "Rajhi Bank Services"}	1

id	account_no	account_type	transaction_id	transaction_date	debit_balance	credit_balance	IFNULL(acc_finacial_periods.id, - 1)	current_period_id	curr_account_no	prev_runnng_bal	running_balance
14	1	1	1	31/12/2019	983661.00	0.00	-1	-1	15	113370325.57	114353986.57
23	1	1	27	06/05/2020	68996993.00	0.00	3	3	15	254714.66	69251707.66
15	2	2	1	31/12/2019	87667.00	0.00	-1	-1	15	113282658.57	113370325.57
24	2	2	1	31/12/2019	0.00	12142237.42	-1	-1	15	125424895.99	113282658.57
46	2	2	22	05/05/2020	10429.71	0.00	3	3	15	182255.82	192685.53
47	2	2	24	05/05/2020	9635.12	0.00	3	3	15	198624.03	208259.15
48	2	2	25	05/05/2020	7628.51	0.00	3	3	15	208259.15	215887.66
16	3	4	19	29/04/2020	16272.00	0.00	3	3	15	33458.43	49730.43
25	3	4	1	31/12/2019	49999085.00	0.00	-1	-1	15	75425810.99	125424895.99
49	3	4	26	06/05/2020	37500.00	0.00	3	3	15	215887.66	253387.66
50	3	4	27	06/05/2020	0.00	0.00	3	3	15	254714.66	254714.66
51	3	4	1	31/12/2019	151300.00	0.00	-1	-1	15	75274510.99	75425810.99
17	4	5	20	29/04/2020	0.00	0.00	3	3	15	68236.77	68236.77
26	4	5	1	31/12/2019	1333813.00	0.00	-1	-1	15	73940697.99	75274510.99
18	5	6	21	28/05/2020	15400.50	0.00	3	3	15	70857.86	86258.36
27	5	6	1	31/12/2019	0.00	378980.86	-1	-1	15	74319678.85	73940697.99
19	6	7	22	05/05/2020	5632.69	0.00	3	3	15	176623.13	182255.82
28	6	7	1	31/12/2019	37536.00	0.00	-1	-1	15	74282142.85	74319678.85
20	7	8	24	05/05/2020	5938.50	0.00	3	3	15	192685.53	198624.03
29	7	8	1	31/12/2019	310794.00	0.00	-1	-1	15	73971348.85	74282142.85
21	8	9	25	05/05/2020	0.00	0.00	3	3	15	208259.15	208259.15
30	8	9	1	31/12/2019	52966.00	0.00	-1	-1	15	73918382.85	73971348.85
22	9	1	26	06/05/2020	0.00	0.00	3	3	15	215887.66	215887.66
31	9	1	1	31/12/2019	166103.00	0.00	-1	-1	15	73752279.85	73918382.85
8	11	1	26	06/05/2020	0.00	0.00	3	3	15	215887.66	215887.66
4	12	1	21	28/05/2020	946.20	0.00	3	3	15	69911.66	70857.86
9	12	1	27	06/05/2020	1327.00	0.00	3	3	15	253387.66	254714.66
1	13	1	1	31/12/2019	2578.19	0.00	-1	-1	15	73749701.66	73752279.85
5	13	1	22	05/05/2020	90364.77	0.00	3	3	15	86258.36	176623.13
10	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73749701.66	73749701.66
32	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73749701.66	73749701.66
33	13	1	1	31/12/2019	73825.00	0.00	-1	-1	15	73675876.66	73749701.66
34	13	1	1	31/12/2019	4050.00	0.00	-1	-1	15	73671826.66	73675876.66
35	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
36	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
37	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
38	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
39	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
40	13	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
2	14	1	19	29/04/2020	33458.43	0.00	3	3	15	0	33458.43
6	14	1	24	05/05/2020	0.00	0.00	3	3	15	192685.53	192685.53
11	14	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
3	15	1	20	29/04/2020	18506.34	0.00	3	3	15	49730.43	68236.77
7	15	1	25	05/05/2020	0.00	0.00	3	3	15	208259.15	208259.15
12	15	1	1	31/12/2019	0.00	0.00	-1	-1	15	73671826.66	73671826.66
41	15	1	1	31/12/2019	4420119.00	0.00	-1	-1	15	69251707.66	73671826.66
42	15	1	1	31/12/2019	0.00	0.00	-1	-1	15	69251707.66	69251707.66
43	15	1	19	29/04/2020	0.00	0.00	3	3	15	0	0
44	15	1	20	29/04/2020	0.00	0.00	3	3	15	49730.43	49730.43
45	15	1	21	28/05/2020	1674.89	0.00	3	3	15	68236.77	69911.66