HK_Database.cpp

Go to the documentation of this file.

/*******************************************************************/
#include "../headers/Database.h"
using namespace std;
 
HK_Database::HK_Database(){}
 
HK_Database::HK_Database(string Path){HK_DBPath = Path;}
 
HK_Database::~HK_Database(){}
 
bool HK_Database::CheckInfo()
{
    //See If Database Contains Info Table
    if(!Execute("SELECT * FROM Info;"))
        return false;
 
    HK_Info Info;
    
    if( Info.GetVersion() != TableData[0].GetValue("Value") || 
        Info.GetPlatform() != TableData[1].GetValue("Value")
    )
        return false;
 
    return true;
}
 
bool HK_Database::CloseDB(){return sqlite3_close(HK_DB);}
 
bool HK_Database::CreateDB()
{
    //Use HK_Date To Keep Samples Current
    HK_Date SD,CD,LD,HD;
    string SQL;
    
    CD.AddDays(10);
    LD.AddDays(18);
    HD.AddDays(21);
    
    //Batch Table
    Execute("CREATE TABLE Batches( ID INT PRIMARY KEY, BatchName TEXT,BatchEggs INT, BatchType VARCHAR(50), BatchSDate VARCHAR(12),BatchC1Date VARCHAR(12), BatchTDate VARCHAR(12), BatchHDate VARCHAR(12),FertileEggs INT, ChicksHatched INT, BatchComments TEXT, BatchBreed TEXT,BatchGroup TEXT,Incubator VARCHAR(50),IsReminded INT, AdvEnabled INT); ");
    //Insert A Batch
    
    Execute("INSERT INTO Batches VALUES(0, 'Sample Batch',6, 'Chicken', '"+SD.ToSortable("/")+"','"+CD.ToSortable("/")+"','"+LD.ToSortable("/")+"','"+HD.ToSortable("/")+"',5,4,'Sample Comment','White Rock','None','Default',0,0);");
 
    //Reminder Table
    Execute("CREATE TABLE Reminders( ID INT PRIMARY KEY,RemindName VARCHAR(50),RemindDate VARCHAR(12), RemindText TEXT,IsReminded INT );");
    Execute("INSERT INTO Reminders VALUES(0,'Sample Reminder','"+SD.ToSortable("/")+"','This Is a Sample Comment',0);");
 
    //Schedule Table
    Execute("CREATE TABLE Schedules(ID INT PRIMARY KEY,SchName VARCHAR(50), SchDate varchar(12));");
    Execute("INSERT INTO Schedules VALUES(0,'Sample Hatch Date','"+HD.ToSortable("/")+"');");
 
    //Types Table
    Execute("CREATE TABLE Types(ID INT PRIMARY KEY,TypeName VARCHAR(50), TypeDays INT);");
    Execute("INSERT INTO Types VALUES(0,'Chicken',21);");
    Execute("INSERT INTO Types VALUES(1,'Turkey',28);");
    Execute("INSERT INTO Types VALUES(2,'Duck',28);");
    Execute("INSERT INTO Types VALUES(3,'Dove',14);");
    Execute("INSERT INTO Types VALUES(4,'Pheasant',24);");
    Execute("INSERT INTO Types VALUES(5,'Bobwhite Quail',23);");
    Execute("INSERT INTO Types VALUES(6,'Coturnix Quail',17);");
    Execute("INSERT INTO Types VALUES(7,'Goose',28);");
    Execute("INSERT INTO Types VALUES(8,'Swan',35);");
    Execute("INSERT INTO Types VALUES(9,'Ostrich',42);");
    Execute("INSERT INTO Types VALUES(10,'Guinea Fowl',28);");
 
    //Breeds Table
    Execute("CREATE TABLE Breeds(ID INT PRIMARY KEY,BreedName VARCHAR(50), BreedType VARCHAR(50), BreedDays INT);");
    Execute("INSERT INTO Breeds VALUES(0,'White Rock','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(1,'Rhode Island Red','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(2,'New Hampshire','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(3,'Black Australorp','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(4,'Barred Rock','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(5,'Delaware','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(6,'Columbian Wyandotte','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(7,'Buckeye','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(8,'Dominique','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(9,'Black Jersey Giant','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(10,'White Jersey Giant','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(11,'Buff Orpington','Chicken',21);");
    Execute("INSERT INTO Breeds VALUES(12,'Royal Palm','Turkey',28);");
    Execute("INSERT INTO Breeds VALUES(13,'Standard Bronze','Turkey',28);");
    Execute("INSERT INTO Breeds VALUES(14,'Blue Slate','Turkey',28);");
    Execute("INSERT INTO Breeds VALUES(15,'Bourbon Red','Turkey',28);");
    Execute("INSERT INTO Breeds VALUES(16,'Narragansett','Turkey',28);");
    Execute("INSERT INTO Breeds VALUES(17,'Black Spanish','Turkey',28);");
    Execute("INSERT INTO Breeds VALUES(18,'White African','Guinea Fowl',28);");
    Execute("INSERT INTO Breeds VALUES(19,'Royal Purple','Guinea Fowl',28);");
    Execute("INSERT INTO Breeds VALUES(20,'Lavendar','Guinea Fowl',28);");
    Execute("INSERT INTO Breeds VALUES(21,'Pearl Grey','Guinea Fowl',28);");
    Execute("INSERT INTO Breeds VALUES(22,'White Pekin','Duck',28);");
    Execute("INSERT INTO Breeds VALUES(23,'Ruen Ducks','Duck',28);");
    Execute("INSERT INTO Breeds VALUES(24,'White Chinese','Goose',28);");
    Execute("INSERT INTO Breeds VALUES(25,'African Goose','Goose',28);"); 
    
    //Groups Table
    Execute("CREATE TABLE Groups( ID INT PRIMARY KEY,GroupName VARCHAR(50),GroupComments TEXT);");
    Execute("INSERT INTO Groups VALUES(0,'None','This Is The Default Group');");
    
    //Notifications Table
    Execute("CREATE TABLE Notifications( ID INT PRIMARY KEY,NotifyName VARCHAR(50),NotifyDate VARCHAR(12), NotifyDescription TEXT, IsNotified INT);");
    
    // Advanced Table
    Execute("CREATE TABLE Advanced( ID INT PRIMARY KEY, NumOfEggs INT,Unit INT, Days INT, Tag1 TEXT, Tag2 TEXT, Tag3 TEXT,Fertile TEXT,Pipped TEXT, Result TEXT, ResultDate TEXT,EggComments TEXT, DayWeight TEXT, DayComment TEXT);");
    
    //Settings Table
    Execute("CREATE TABLE Settings( ID INT PRIMARY KEY, Key TEXT, Value TEXT);");
    Execute("INSERT INTO Settings VALUES(0,'TillCandle','12');");
    Execute("INSERT INTO Settings VALUES(1,'BeforeHatch','3');");
    Execute("INSERT INTO Settings VALUES(2,'ShowSplash','0');");
    Execute("INSERT INTO Settings VALUES(3,'UseGenNotify','0');");
    Execute("INSERT INTO Settings VALUES(4,'OpenOnClick','0');");
    Execute("INSERT INTO Settings VALUES(5,'OpenOnDismiss','1');");
    Execute("INSERT INTO Settings VALUES(6,'BatchSortState','0');");
    Execute("INSERT INTO Settings VALUES(7,'HideHatched','0');");
    Execute("INSERT INTO Settings VALUES(8,'Tag1','Size,Small,Medium,Large');");
    Execute("INSERT INTO Settings VALUES(9,'Tag2','Shape,Pointed,Round');");
    Execute("INSERT INTO Settings VALUES(10,'Tag3','Color,Brown,Light Brown,Dark Brown,White,Green,Blue,Light,Dark');");
    Execute("INSERT INTO Settings VALUES(11,'ColorRed','181,255,0,255,0,217,0,217,0,255,0');");
    Execute("INSERT INTO Settings VALUES(12,'ColorGreen','170,255,0,255,0,208,0,208,0,255,0');");
    Execute("INSERT INTO Settings VALUES(13,'ColorBlue','150,255,0,255,0,192,0,192,0,255,0');");
    Execute("INSERT INTO Settings VALUES(14,'Incubators','Default');");
    Execute("INSERT INTO Settings VALUES(15,'WeightUnit','0');");
    Execute("INSERT INTO Settings VALUES(16,'CalTextSize','18');");
    Execute("INSERT INTO Settings VALUES(17,'LanguageXML','');");
    Execute("INSERT INTO Settings VALUES(18,'ShowDaysLeft','0');");
    Execute("INSERT INTO Settings VALUES(19,'DateMode','0');");
    //Info Table
    
    HK_Info Info;
    
    Execute("CREATE TABLE Info( ID INT PRIMARY KEY, Key TEXT, Value TEXT);");
    Execute("INSERT INTO Info VALUES(0,'Version','"+ Info.GetVersion() +"');");
    Execute("INSERT INTO Info VALUES(1,'Platform','Desktop');");
 
    return true;
}
 
bool HK_Database::CreateBackup(string Path)
{
    HK_File File;
 
    if(!File.CopyFile(HK_DBPath, Path))
        return false;
    return true;
}
 
string HK_Database::GetErrors() {return sqlite3_errmsg(HK_DB);}
 
vector<HK_KeyValue> HK_Database::GetData() {return TableData;}
 
bool HK_Database::OpenDB()
{
    return sqlite3_open(HK_DBPath.c_str(), &HK_DB);
}
 
HK_Batches HK_Database::ReadBatches(int Sort )
{
    HK_Batches HK_DBBatches;
    string SQL;    
    switch(Sort) {
        case 0: {
            SQL = "SELECT * from Batches;";
            break;
        }
        case 1: {
            SQL = "SELECT * from Batches ORDER BY BatchHDate DESC;";
            break;
        }
        case 2: {
            SQL = "SELECT * from Batches ORDER BY BatchSDate DESC;";
            break;
        }
        case 3: {
            SQL = "SELECT * from Batches ORDER BY BatchType;";
            break;
        }
        case 4: {
            SQL = "SELECT * from Batches ORDER BY BatchBreed;";
            break;
        }
        default: {
            SQL = "SELECT * from Batches;";
            break;
        }
    }
    
    if(!Execute(SQL))
        return HK_DBBatches;
    
    HK_KeyValue Data;
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
 
        HK_Date Start(Data.GetValue("BatchSDate"));
        HK_Date Candle(Data.GetValue("BatchC1Date"));
        HK_Date Lock(Data.GetValue("BatchTDate"));
        HK_Date Hatch(Data.GetValue("BatchHDate"));
 
        HK_DBBatches.Add(
            Data.GetInt("ID"),
            Data.GetValue("BatchName"),
            Data.GetValue("BatchType"),
            Data.GetValue("BatchBreed"),
            Data.GetValue("BatchGroup"),
            Data.GetValue("BatchComments"),
            Data.GetValue("Incubator"),
            Data.GetInt("BatchEggs"),
            Data.GetInt("FertileEggs"),
            Data.GetInt("ChicksHatched"),
            Start,
            Candle,
            Lock,
            Hatch,
            Data.GetInt("IsReminded"),
            Data.GetInt("AdvEnabled")
        );
    }
    
    //Scan Advanced Data As Well
    if(!Execute("SELECT * from Advanced;"))
        return HK_DBBatches;
        
    //Declare Some Temp Variables
    vector<string> Tag1,Tag2,Tag3;
    vector<string> Fertile,Pipped; 
    vector<string> Result,ResultDate;
    vector<string> Comments;        
    vector<string> DayWeight, DayComment,Buffer;
    
    HK_Egg Egg;
    HK_Date Date;
    HK_Weight Weight;
    HK_Advanced Adv;
    
    for(unsigned int B = 0; B < TableData.size(); B++) {
        Data = TableData[B];
        
        Adv.SetID(Data.GetInt("ID"));
        Adv.SetUnit(Data.GetInt("Unit"));
        Adv.SetDays(Data.GetInt("Days"));
        
        Tag1 = SplitStrings(Data.GetValue("Tag1"),"&");
        Tag2 = SplitStrings(Data.GetValue("Tag2"),"&");
        Tag3 = SplitStrings(Data.GetValue("Tag3"),"&");
        Fertile = SplitStrings(Data.GetValue("Fertile"),"/");
        Pipped = SplitStrings(Data.GetValue("Pipped"),"/"); 
        Result = SplitStrings(Data.GetValue("Result"),"/");
        ResultDate = SplitStrings(Data.GetValue("ResultDate"),"&");
        Comments = SplitStrings(Data.GetValue("EggComments"),"&");  
        DayWeight = SplitStrings(Data.GetValue("DayWeight"),"&");
        DayComment = SplitStrings(Data.GetValue("DayComment"),"&");
        
        for(unsigned int A = 0; A < Tag1.size();A++) {
            Egg.SetTag1(Tag1[A]);
            Egg.SetTag2(Tag2[A]);
            Egg.SetTag3(Tag3[A]);
            Egg.SetFertile(stoi(Fertile[A]));
            Egg.SetPipped(stoi(Pipped[A]));
            Egg.SetResult(stoi(Result[A]));
            
            //Split Result Dates
            Buffer = SplitStrings(ResultDate[A], "/");
            Date.SetDate(stoi(Buffer[2]),stoi(Buffer[1]),stoi(Buffer[0]));
            Egg.SetDate(Date);
            
            Egg.SetComments(Comments[A]);
            
            //DayWeight
            Egg.ClearDayData();
            Buffer = SplitStrings(DayWeight[A], "/");
            
            unsigned int C;
            
            for(C = 0; C < Buffer.size(); C++) {
                Weight.Set(Buffer[C]);
                Egg.AddDayWeight(Weight);
            }
            //DayComments
            Buffer = SplitStrings(DayComment[A], "|");
            for(C = 0; C < Buffer.size(); C++)
                Egg.AddDayComments(Buffer[C]);
            Adv.AddEgg(Egg);
        }
        //And Add To Batches
        if(!HK_DBBatches.AddAdvanced(Adv))
            cout<<"Log: No Matching Batch For Advanced Entry!"<<endl;
        
        Adv.Clear();
    }
    return HK_DBBatches;
}
 
HK_Storage HK_Database::ReadBreeds()
{
    HK_Storage Storage;
    HK_KeyValue Data;
 
    if(!Execute("SELECT * from Breeds ORDER BY BreedType;"))
        return Storage;
    
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        Storage.Add(
            Data.GetValue("BreedName"),
            Data.GetInt("ID"),
            Data.GetValue("BreedType"),
            Data.GetInt("BreedDays")
        );
    }
    return Storage;
}
 
HK_Storage HK_Database::ReadGroups()
{
    HK_Storage Storage;
    HK_KeyValue Data;
 
    if(!Execute("SELECT * from Groups;"))
        return Storage;
    
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        Storage.Add(
            Data.GetValue("GroupName"),
            Data.GetInt("ID"),
            Data.GetValue("GroupComments")
        );
    }
    return Storage;
}
 
HK_Notifications HK_Database::ReadNotify()
{
    HK_Notifications Storage;
    HK_KeyValue Data;
    
    if(!Execute("SELECT * from Notifications ORDER BY NotifyDate DESC;"))
        return Storage;
    
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        HK_Date Date(Data.GetValue("NotifyDate"));
        Storage.Add(
            Data.GetValue("NotifyName"),
            Data.GetInt("ID"),
            Date,
            Data.GetValue("NotifyDescription"),
            Data.GetInt("IsNotified")
        );
    }
    return Storage;
}
 
HK_Storage HK_Database::ReadRemind()
{
    HK_Storage Storage;
    HK_KeyValue Data;
    
    if(!Execute("SELECT * from Reminders ORDER BY RemindDate DESC;"))
        return Storage;
 
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        HK_Date Date(Data.GetValue("RemindDate"));
        Storage.Add(
            Data.GetValue("RemindName"),
            Data.GetInt("ID"),
            Date,
            Data.GetValue("RemindText"),
            Data.GetInt("IsReminded")
        );
    }
    return Storage;
}
 
HK_Storage HK_Database::ReadSchedules()
{
    HK_Storage Storage;
    HK_KeyValue Data;
 
    if(!Execute("SELECT * from Schedules ORDER BY SchDate DESC;"))
        return Storage;
    
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        HK_Date Date(Data.GetValue("SchDate"));
        Storage.Add(
            Data.GetValue("SchName"),
            Data.GetInt("ID"),
            Date
        );
    }
    return Storage;
}
 
HK_Storage HK_Database::ReadTypes()
{
    HK_Storage Storage;
    HK_KeyValue Data;
    
    if(!Execute("SELECT * from Types ORDER BY ID;"))
        return Storage;
    
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        Storage.Add(
            Data.GetValue("TypeName"),
            Data.GetInt("ID"),
            Data.GetInt("TypeDays")
        );
    }
    return Storage;
}
 
HK_KeyValue HK_Database::ReadSettings()
{
    HK_KeyValue Storage;
    HK_KeyValue Data;
    
    if(!Execute("SELECT * from Settings ORDER BY ID;"))
        return Storage;
    
    for(unsigned int A = 0; A < TableData.size(); A++) {
        Data = TableData[A];
        Storage.Add(
            Data.GetValue("Key"),
            Data.GetValue("Value")
        );
    }
    return Storage;
}
 
void HK_Database::SetPath(string Path){HK_DBPath = Path;}
 
bool HK_Database::Execute(string SQL)
{
    //Check For Empty Statment
    if(SQL.empty())
        return true;
    
    if(OpenDB()) {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(HK_DB));
       return false;
    }
    
    sqlite3_stmt *Stmt = NULL;
    int Result = sqlite3_prepare_v2(HK_DB,SQL.c_str(),-1,&Stmt,NULL);
    
    if(Result != SQLITE_OK)
        return false;
    
    int Rows = 0, Columns = 0, Selected = 0, Type;
    HK_KeyValue RowData;
    
    //Reset TableData
    TableData.clear();
    
    Result = sqlite3_step(Stmt); 
    while (Result != SQLITE_DONE && Result != SQLITE_OK) { 
        Rows++; 
        Columns = sqlite3_column_count(Stmt); 
        
        for (Selected = 0; Selected < Columns; Selected++) { 
            Type = sqlite3_column_type(Stmt, Selected); 
            const char * ColumnName = sqlite3_column_name(Stmt, Selected);
 
            if (Type == SQLITE_INTEGER) {
                RowData.Add(
                    ColumnName,
                    to_string(sqlite3_column_int(Stmt, Selected))
                );
            } 
            else if (Type == SQLITE_FLOAT) { 
                RowData.Add(
                    ColumnName,
                    to_string(sqlite3_column_double(Stmt, Selected))
                );
            } 
            else if (Type == SQLITE_TEXT) { 
                RowData.Add(
                    ColumnName,
                    (const char *) (sqlite3_column_text(Stmt, Selected))
                );
            }
            else 
                printf("Not Matching Int Float Or Text"); 
        } 
        
        TableData.push_back(RowData);
        RowData.Clear();
        Result = sqlite3_step(Stmt); 
    } 
    
    Result = sqlite3_finalize(Stmt);
    if(Result != SQLITE_OK)
        return false;
    
    CloseDB();
    
    return true;
}
 
bool HK_Database::Execute(vector<string> SQL)
{
    for(unsigned int A = 0; A < SQL.size(); A++) {
        if(!Execute(SQL[A]))
            return false;
    }
    return true;
}
 
 
vector<string> HK_Database::SplitStrings(string In, string Split)
{
    vector<string> OutArray;
    string Hold;
        
    for(unsigned int a = 0; a < In.length(); a++) {
        if(In[a] == Split[0]) {
            OutArray.push_back(Hold); 
            Hold.clear();
            continue;
        }
        Hold.push_back(In[a]);
    }
    OutArray.push_back(Hold);
    return OutArray;
}