// Filename   : db.cpp
// Last Change: 02-Aug-2013.
//

#include "db.h"
#include "wx/wxsqlite3.h"

/* $BHoJ]HV$GHoJ]81<T>pJs$r<hF@(B */
wxString GetHhsInfoByHhsNo( wxString hhsno )
{
    wxString name, addr;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxSQLite3Database hhsdb;
    hhsdb.Open( gszFile );

    wxSQLite3Statement stmt = hhsdb.PrepareStatement("SELECT name, addr FROM hhs_master WHERE hhsno = ?");
    stmt.Bind( 1, hhsno );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();
    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            name = q.GetString(0);
            addr = q.GetString(1);
        }
    }
    stmt.Finalize();
    hhsdb.Close();

    if ( name.IsEmpty() ) {
        return wxEmptyString;
    }
    else {
        return name + wxT("_") + addr;
    }
}

// $B;aL>%+%J$GHoJ]81<T>pJs$r8!:w(B
wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
{
    wxArrayString data;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxSQLite3Database hhsdb;
    hhsdb.Open( gszFile );

    wxString sql = wxT( "SELECT hhsno, kana, name, birth, addr FROM hhs_master WHERE kana = ? ORDER BY kana, birth;" ); 
    //if ( fuzzy ) ;//*****

    wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
    stmt.Bind( 1, kana );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        wxString str;
        while ( q.NextRow() ) {
            str = q.GetString(0);
            for ( int i=1; i<5; i++ ) {
                str +=  "_" + q.GetString(i);
            }
            data.Add( str );
        }
    }
    stmt.Finalize();
    hhsdb.Close();

    return data;
}

/* $BHoJ]81<THV9f$+$i%U%!%$%k%Q%9$r<hF@(B */
wxArrayString GetPathByHhsNo( wxString hhsno )
{
    wxArrayString date_path;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, path FROM ccn WHERE hhsno = ? ORDER BY date DESC");
    stmt.Bind( 1, hhsno );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        wxString str;
        while ( q.NextRow() ) {
            str = q.GetString(0) + "_" + q.GetString(1);
            date_path.Add( str );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return date_path;
}

/* $B9g5DBN3+:EF|$r<hF@(B */
wxArrayString GetCcnDate( void ) 
{
    wxArrayString date_cnt;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc");
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    wxString str;
    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            str = q.GetString(0) + "_" + q.GetString(1);
            date_cnt.Add( str );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return date_cnt;
}

/* $BF|IU$+$i?3::2q$r<hF@(B */
wxArrayString GetCcnByDate( wxString date ) 
{
    wxArrayString ccn_cnt;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT ccn, count(*) FROM ccn WHERE date = ? GROUP BY ccn");
    stmt.Bind( 1, date );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    wxString str;
    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            str = q.GetString(0) + "_" + q.GetString(1);
            ccn_cnt.Add( str );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return ccn_cnt;
}

/* $B9g5DBN$+$iHoJ]81<THV9f$r<hF@(B */
wxArrayString GetHhsNoByCcn( wxString ccn, wxString date ) 
{
    wxArrayString hhsno;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno");
    stmt.Bind( 1, ccn );
    stmt.Bind( 2, date );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            hhsno.Add( q.GetString(0) );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return hhsno;
}

/* $B%$%s%G%C%/%9$r99?7(B */
void UpdateIndex( wxArrayString paths )
{
    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    wxRegEx regex( wxT("^.+(20[0-9]{2})([01][0-9])([0-3][0-9]).(.+).(0[1238]{8})$") );
    wxSQLite3Statement stmt = ccndb.PrepareStatement("INSERT OR REPLACE INTO ccn VALUES( ?, ?, ?, ? )");
    wxString date, ccn, hhsno;

    for ( int i=0; i<paths.GetCount(); i++ ) {
        date  = paths[i];
        ccn   = paths[i];
        hhsno = paths[i];
        regex.ReplaceAll( &date, wxT("\\1-\\2-\\3") );
        regex.ReplaceAll( &date, wxT("\\4") );
        regex.ReplaceAll( &date, wxT("\\5") );
        stmt.Bind( 1, date );
        stmt.Bind( 2, ccn );
        stmt.Bind( 3, hhsno );
        stmt.Bind( 4, paths[i] );
        stmt.ExecuteQuery();
        stmt.Finalize();
    }

    ccndb.Close();
}