C#, SQLite - MMEX 거래 내역 가져오기

MMEX 1.7.0 기준

SQLite SELECT LEFT JOIN

CHECKINGACCOUNT_V1 테이블에 거래 내역이 기록되는데, 참조 데이터는 IDentity 값이 저장되므로 SELECT로 데이터를 불러올 때는 모두 합쳐서 가져와야 내용을 알 수 있다.

WITH WST as (
     SELECT TRANSID, PCATEG.CATEGNAME || '{CATEG_DELIMITER}' || CATEG.CATEGNAME as CATEGFULLNAME
     FROM SPLITTRANSACTIONS_V1 A
     LEFT JOIN CATEGORY_V1 CATEG ON A.`CATEGID` = CATEG.CATEGID
     LEFT JOIN CATEGORY_V1 PCATEG ON CATEG.`PARENTID` = PCATEG.CATEGID
),
WTAG as (
     SELECT REFID, TAGS.TAGNAME as TAGNAME
     FROM TAGLINK_V1 A
     LEFT JOIN TAG_V1 TAGS ON A.`TAGID` = TAGS.TAGID
)

SELECT
    A.*,
    ACC.ACCOUNTNAME,
    TOACC.ACCOUNTNAME AS TOACCOUNTNAME,
    Case
        When TRANSCODE ='Transfer' Then '> ' || TOACC.ACCOUNTNAME
        Else PAYEE.PAYEENAME
    End as PAYEENAME,
    Case
        When TRANSCODE != 'Deposit' Then A.TRANSAMOUNT
    End as WITHDRAWAL, 
    Case
        When TRANSCODE = 'Deposit' Then A.TRANSAMOUNT
    End as DEPOSIT, 
    CATEG.PARENTID,
    CATEG.CATEGNAME,
    PCATEG.CATEGNAME AS PCATEGNAME,
    Case
        When Length(GROUPCATEG) > 0 Then GROUPCATEG
        When Length(PCATEG.CATEGNAME) > 0 Then PCATEG.CATEGNAME || '{CATEG_DELIMITER}' || CATEG.CATEGNAME
        Else CATEG.CATEGNAME
    End as CATEGFULLNAME,
    GROUPTAG
FROM   CHECKINGACCOUNT_V1 A    
    LEFT JOIN ACCOUNTLIST_V1 ACC ON A.`ACCOUNTID` = ACC.ACCOUNTID
    LEFT JOIN ACCOUNTLIST_V1 TOACC ON A.`TOACCOUNTID` = TOACC.ACCOUNTID
    LEFT JOIN PAYEE_V1 PAYEE ON A.`PAYEEID` = PAYEE.PAYEEID
    LEFT JOIN CATEGORY_V1 CATEG ON A.`CATEGID` = CATEG.CATEGID
    LEFT JOIN CATEGORY_V1 PCATEG ON CATEG.`PARENTID` = PCATEG.CATEGID
    LEFT JOIN (SELECT TRANSID, ' * ' || GROUP_CONCAT(CATEGFULLNAME, ', ') AS GROUPCATEG FROM WST GROUP BY TRANSID) ST ON A.TRANSID = ST.TRANSID
    LEFT JOIN (SELECT REFID, GROUP_CONCAT(TAGNAME, ', ') AS GROUPTAG FROM WTAG GROUP BY REFID) TAGS ON A.TRANSID = TAGS.REFID

LEFT OUTER JOIN과 LEFT JOIN은 같다. AccessDB에서는 LEFT OUTER JOIN으로 사용한다.

CATEG 필드가 두개인 이유는, 범주(Category) 항목은 상위-하위 로 구분되기 때문이다.

이렇게 등록된 범주인데, 각 범주는 별개의 레코드이다.

상위는 PCATEGNAME, 하위는 CATEGNAME 필드를 가져와야 아래처럼 하나의 문자열로 합쳐서 보여줄 수 있다.

상위 > 하위

---

C# SQLite 쿼리 분석

위에서 WHERE 문이 없는 이유는 BindingSource에 필터기능이 있기 때문이다.
메모리 상에서 처리되는 실시간 필터이기 때문에 DB 쿼리보다 매우 빠르다.
(레코드가 수 만개 이상이라면 범위를 적용하는게 좋겠지만, 아직 그런 자료가 없다)

C# 가계부는 using System.Data.SQLite; 참조를 사용한다. NuGet에서 설치.

쿼리 데이터를 DataTable로 반환하는 메서드

internal DataTable GetListDateBetween(DateTime dateTime1, DateTime dateTime2)
{
    DataTable dt = null;
    try
    {
        SQLiteParameter[] @params = new SQLiteParameter[]
        {
            new SQLiteParameter("@TRANSDATE1", DbType.Date)
            {
                Value = dateTime1
            },
            new SQLiteParameter("@TRANSDATE2", DbType.Date)
            {
                Value = dateTime2
            },
        };
        dt = SQLiteHelper.DataAdapterSelectCommand(ListAllDateBetween, CommandType.Text, @params);
    }
    catch (Exception ex)
    {
        XtraMessageBox.Show(ex.Message);
    }
    return dt;
}

Access는 파라미터 문자가 ?로 변환되어 순서만 중요했지만, SQLite는 파라미터 이름이 그대로 전달 되므로 틀리면 안 된다.

쿼리 후, 데이터를 DataTable에 채우는 메서드

public static DataTable DataAdapterSelectCommand(string cmdSqlstr, CommandType cmdType, SQLiteParameter[] @params)
{
    DataTable table = null;

    using (SQLiteConnection DBConn = new SQLiteConnection(DBCONNECT_STRING))
    {
        try
        {
            DBConn.Open();
        }
        catch (Exception ex)
        {
            XtraMessageBox.Show(ex.Message);
        }

        using (SQLiteCommand cmd = SharedField.DBConn.CreateCommand())
        {
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdSqlstr;
            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(@params);
            cmd.CommandTimeout = 30;

            using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
            {
                table = new DataTable();
                try
                {
                    da.Fill(table);
                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show(ex.Message);
                }
            }
        }
    }
    return table;
}

SELECT 문은 SQLiteTransaction를 사용할 필요 없이 곧바로 쿼리한다.