CheongBuk_MegaPark_Key_Management/CheongBuk_MegaPark_Key_Management/DB.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;

namespace Cheongbuk_MegaPark_Key_Management
{
    internal class DB
    {
        private SQLiteConnection _sqLiteConnection;
        private SQLiteCommand _sqLiteCommand;
        private SQLiteDataReader _sqLiteDataReader;
        private static readonly string CONNECTION_STRING = $"Data Source={Setting.DbLocation}; Version=3; Integrated Security=true;";

        public DB()
        {
            CreateDatabaseIfNotExist();
        }

        private SQLiteConnection OpenConnection()
        {
            _sqLiteConnection = new SQLiteConnection(CONNECTION_STRING);
            _sqLiteConnection.Open();

            return _sqLiteConnection;
        }

        private void CreateDatabaseIfNotExist()
        {
            if (!File.Exists(Setting.DbLocation))
            {
                SQLiteConnection.CreateFile(Setting.DbLocation);
                _sqLiteConnection = OpenConnection();

                var createKeyTable = @"
                        CREATE TABLE Key(KeyCode INTEGER NOT NULL, Activation NUMERIC NOT NULL,PRIMARY KEY(KeyCode));

                        CREATE TABLE ProfitData (ID INTEGER NOT NULL UNIQUE,
                        KeyCode INTEGER NOT NULL,
                        Category INTEGER NOT NULL,
                        StartDateTime TEXT NOT NULL,
                        People INTEGER NOT NULL,
                        EndDateTime INTEGER NOT NULL,
                        UseTime INTEGER,
                        ProfitBase INTEGER NOT NULL,
                        ProfitAdd INTEGER,
                        ProfitDiscount INTEGER,
                        DeleteData INTEGER DEFAULT 0,
                        PRIMARY KEY(ID AUTOINCREMENT));

                        CREATE TABLE RentData (KeyCode INTEGER NOT NULL UNIQUE, Category INTEGER NOT NULL, StartDateTime TEXT NOT NULL, People INTEGER NOT NULL);";

                using (var command = _sqLiteConnection.CreateCommand())
                {
                    command.CommandText = createKeyTable;
                    var excute = command.ExecuteNonQuery();
                }
            }
            else
            {
                OpenConnection();
            }
        }

        public void LoadData()
        {
            // RentData Load
            Setting.RentDatas = new List<RentData>();
            var query = "Select * FROM RentData";
            _sqLiteCommand = new SQLiteCommand(query, _sqLiteConnection);
            _sqLiteDataReader = _sqLiteCommand.ExecuteReader();

            while (_sqLiteDataReader.Read())
            {
                var rentData = new RentData();

                int.TryParse(_sqLiteDataReader["KeyCode"].ToString(), out rentData.KeyCode);
                int.TryParse(_sqLiteDataReader["Category"].ToString(), out rentData.Category);
                rentData.StartDateTime = Convert.ToDateTime(_sqLiteDataReader["StartDateTime"]);
                int.TryParse(_sqLiteDataReader["People"].ToString(), out rentData.People);
                Setting.RentDatas.Add(rentData);
            }

            _sqLiteDataReader.Close();
            var startDateTime = DateTime.Now;
            var endDateTime = DateTime.Now.AddDays(1);

            query = "Select * FROM ProfitData Where EndDateTime >= '" + startDateTime.Year.ToString("0000") + "-" +
                    startDateTime.Month.ToString("00") +
                    "-" + startDateTime.Day.ToString("00") + "' And EndDateTime < '" +
                    endDateTime.Year.ToString("0000") + "-" + endDateTime.Month.ToString("00") +
                    "-" + endDateTime.Day.ToString("00") + "' And DeleteData = 0";

            _sqLiteCommand = new SQLiteCommand(query, _sqLiteConnection);
            _sqLiteDataReader = _sqLiteCommand.ExecuteReader();

            while (_sqLiteDataReader.Read())
            {
                var profitData = new ProfitData();
                int.TryParse(_sqLiteDataReader["ID"].ToString(), out profitData.Id);
                int.TryParse(_sqLiteDataReader["KeyCode"].ToString(), out profitData.KeyCode);
                int.TryParse(_sqLiteDataReader["Category"].ToString(), out profitData.Category);
                profitData.StartDateTime = Convert.ToDateTime(_sqLiteDataReader["StartDateTime"]);
                int.TryParse(_sqLiteDataReader["People"].ToString(), out profitData.People);
                profitData.EndDateTime = Convert.ToDateTime(_sqLiteDataReader["EndDateTime"]);
                int.TryParse(_sqLiteDataReader["UseTime"].ToString(), out profitData.UseTime);
                int.TryParse(_sqLiteDataReader["ProfitBase"].ToString(), out profitData.ProfitBase);
                int.TryParse(_sqLiteDataReader["ProfitAdd"].ToString(), out profitData.ProfitAdd);
                int.TryParse(_sqLiteDataReader["ProfitDiscount"].ToString(), out profitData.ProfitDiscount);
                Setting.ProfitDatas.Add(profitData);
            }

            _sqLiteDataReader.Close();
        }

        public void InsertRent(RentData rentData)
        {
            var query = "Insert INTO RentData VALUES(" + rentData.KeyCode + "," + rentData.Category + ", '" +
                        rentData.StartDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'," + rentData.People + ")";

            _sqLiteCommand = new SQLiteCommand(query, _sqLiteConnection);
            _sqLiteCommand.ExecuteNonQuery();
        }

        public int ReturnRent(ProfitData profitData)
        {
            var query =
                "Insert INTO ProfitData (KeyCode, Category, StartDateTime, People, EndDateTime, UseTime, ProfitBase, ProfitAdd, ProfitDiscount) VALUES(" +
                profitData.KeyCode + "," + profitData.Category + ", '" +
                profitData.StartDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "', " +
                profitData.People + ", '" +
                profitData.EndDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "', " +
                profitData.UseTime + ", " +
                profitData.ProfitBase + ", " +
                profitData.ProfitAdd + ", " +
                profitData.ProfitDiscount + "); SELECT last_insert_rowid()";

            _sqLiteCommand = new SQLiteCommand(query, _sqLiteConnection);
            _sqLiteDataReader = _sqLiteCommand.ExecuteReader();
            var id = 0;

            while (_sqLiteDataReader.Read())
                int.TryParse(_sqLiteDataReader[0].ToString(), out id);

            query = "Delete From RentData Where KeyCode=" + profitData.KeyCode;
            _sqLiteCommand = new SQLiteCommand(query, _sqLiteConnection);
            _sqLiteCommand.ExecuteNonQuery();
            return id;
        }

        public void DeleteProfitData(int id)
        {
            var query = "Update ProfitData Set DeleteData=1 Where Id=" + id;
            _sqLiteCommand = new SQLiteCommand(query, _sqLiteConnection);
            _sqLiteCommand.ExecuteNonQuery();
        }
    }
}