mainScreen.api.js 6.06 KB
Newer Older
Soo Hyun Kim's avatar
Soo Hyun Kim committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import { DEBUG, enablePromise } from 'react-native-sqlite-storage';
import getDb from './moneyDB'
import { getDateStr } from '../utils/dateFunction';

DEBUG(true);
enablePromise(true);

const getWeeklyData = async (start, end) => {
    const db = await getDb();
    return new Promise(async (resolve, reject) => {
        const temp = [];
        let input = 0;
        let output = 0;
        await db.transaction(async (tx) => {
            const [txn, results] = await tx.executeSql(
                `select date, group_concat(type_id, '|') as type_id
                from (SELECT date, type_id 
                from money 
                where date BETWEEN "${getDateStr(start)}" and "${getDateStr(end)}" 
                group by date, type_id ) group by date`
            );

            for (let i = 0; i < results.rows.length; i++) {
                const tempDate = results.rows.item(i).date;
                let tempType_id = [];
                if (results.rows.item(i).type_id) {
                    tempType_id = results.rows.item(i).type_id.split('|');
                }
                temp.push({ date: tempDate, type_id: tempType_id })
            }
        })
        await db.transaction(async (tx) => {
            const [txn2, res] = await tx.executeSql(
Soo Hyun Kim's avatar
Soo Hyun Kim committed
34
35
36
37
                `SELECT type_id, sum(price) as price 
                FROM money 
                WHERE date BETWEEN "${getDateStr(start)}" and "${getDateStr(end)}" 
                GROUP BY type_id ;`
Soo Hyun Kim's avatar
Soo Hyun Kim committed
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
            );
            for (let i = 0; i < res.rows.length; i++) {
                if (res.rows.item(i).type_id === 1) {
                    input = res.rows.item(i).price;
                } else if (res.rows.item(i).type_id === 2) {
                    output = res.rows.item(i).price;
                }
            }
        })
        resolve({ data: temp, input: input, output: output })
    })
}

const getData = async (findDate) => {
    const db = await getDb();
    return new Promise((resolve, reject) => {
        db.transaction(async (tx) => {
Soo Hyun Kim's avatar
Soo Hyun Kim committed
55
56
57
58
59
60
61
            const [txn, results] = await tx.executeSql(`
            SELECT money.type_id, category_name, assets_name, contents, price 
            FROM money 
            inner JOIN categories on money.category_id = categories.category_id 
            inner JOIN assets_type on money.assets_id = assets_type.assets_id 
            WHERE date="${getDateStr(findDate)}"
            `);
Soo Hyun Kim's avatar
Soo Hyun Kim committed
62
63
            const temp = [];
            for (let i = 0; i < results.rows.length; i++) {
Soo Hyun Kim's avatar
Soo Hyun Kim committed
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
                if (results.rows.item(i).type_id === 3) {
                    let asset_type = results.rows.item(i).assets_name;
                    let deposit_asset = results.rows.item(i).assets_name;
                    let price = 0;
                    if (results.rows.item(i).price > 0) {
                        deposit_asset = results.rows.item(i).assets_name;
                        asset_type = results.rows.item(i + 1).assets_name;
                        price = results.rows.item(i).price;
                    } else {
                        asset_type = results.rows.item(i).assets_name;
                        deposit_asset = results.rows.item(i + 1).assets_name;
                        price = -results.rows.item(i).price;
                    }
                    temp.push({
                        id: results.rows.item(i).id,
                        category: results.rows.item(i).category_name,
                        asset: asset_type,
                        deposit_asset: deposit_asset,
                        contents: results.rows.item(i).contents,
                        price: price,
                        type: results.rows.item(i).type_id
                    })
                    i++;
                } else {
                    temp.push({
                        id: results.rows.item(i).id,
                        category: results.rows.item(i).category_name,
                        contents: results.rows.item(i).contents,
                        price: results.rows.item(i).price,
                        type: results.rows.item(i).type_id
                    })
                }
Soo Hyun Kim's avatar
Soo Hyun Kim committed
96
97
98
99
100
101
102
103
104
105
106
107
            }
            resolve(temp);
        })
    })
};

const getTotalData = async () => {
    const db = await getDb();
    return new Promise(async (resolve, reject) => {
        let total = 0;
        const temp = [];
        await db.transaction(async (tx) => {
Soo Hyun Kim's avatar
Soo Hyun Kim committed
108
            const [txn, results] = await tx.executeSql("SELECT type_id, sum(price) as price from money group by type_id");
Soo Hyun Kim's avatar
Soo Hyun Kim committed
109
            for (let i = 0; i < results.rows.length; i++) {
Soo Hyun Kim's avatar
Soo Hyun Kim committed
110
111
112
113
114
                if (results.rows.item(i).type_id === 2) {
                    total -= results.rows.item(i).price
                } else {
                    total += results.rows.item(i).price
                }
Soo Hyun Kim's avatar
Soo Hyun Kim committed
115
116
117
            }
        })
        await db.transaction(async (tx) => {
Soo Hyun Kim's avatar
Soo Hyun Kim committed
118
119
120
121
122
123
124
            const [txn, results] = await tx.executeSql(`
            select id, group_concat(type_id, '|') as type_id, assets_name, group_concat(price, '|') as price
            from (SELECT money.assets_id as id, type_id, assets_name, sum(price) as price 
                from money 
                left JOIN assets_type on money.assets_id = assets_type.assets_id
                group by money.assets_id, type_id) 
            group by id`);
Soo Hyun Kim's avatar
Soo Hyun Kim committed
125
            for (let i = 0; i < results.rows.length; i++) {
Soo Hyun Kim's avatar
Soo Hyun Kim committed
126
127
128
129
130
131
132
133
134
135
136
                let tempAssetTotal = 0;
                const type_id_array = results.rows.item(i).type_id.split('|');
                const price_array = results.rows.item(i).price.split('|');
                for (let j = 0; j < type_id_array.length; j++) {
                    if (type_id_array[j] === '2') {
                        tempAssetTotal -= Number(price_array[j])
                    } else {
                        tempAssetTotal += Number(price_array[j])
                    }
                }
                temp.push({ id: results.rows.item(i).id, name: results.rows.item(i).assets_name, price: tempAssetTotal })
Soo Hyun Kim's avatar
Soo Hyun Kim committed
137
138
            }
        })
Soo Hyun Kim's avatar
Soo Hyun Kim committed
139
        resolve({ total: total, assets_total: temp });
Soo Hyun Kim's avatar
Soo Hyun Kim committed
140
141
142
143
144
145
146
147
148
149
    })
};

const weekApi = {
    getWeeklyData,
    getData,
    getTotalData,
}

export default weekApi;