mainScreen.api.js

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(
                `SELECT type_id, sum(price) as price 
                FROM money 
                WHERE date BETWEEN "${getDateStr(start)}" and "${getDateStr(end)}" 
                GROUP BY type_id ;`
            );
            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) => {
            const [txn, results] = await tx.executeSql(`
            SELECT money.money_id as id, 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)}"
            `);
            const temp = [];
            for (let i = 0; i < results.rows.length; i++) {
                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
                    })
                }
            }
            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) => {
            const [txn, results] = await tx.executeSql("SELECT type_id, sum(price) as price from money group by type_id");
            for (let i = 0; i < results.rows.length; i++) {
                if (results.rows.item(i).type_id === 2) {
                    total -= results.rows.item(i).price
                } else {
                    total += results.rows.item(i).price
                }
            }
        })
        await db.transaction(async (tx) => {
            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`);
            for (let i = 0; i < results.rows.length; i++) {
                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 })
            }
        })
        resolve({ total: total, assets_total: temp });
    })
};

const weekApi = {
    getWeeklyData,
    getData,
    getTotalData,
}

export default weekApi;