﻿using System;
using System.Data.OleDb;

namespace ParadiseFuneralServices.Classes
{
    class Transaction
    {

        public Transaction()
        {
            code = GenerateCode();
            datetime = DateTime.Today;
        }

        public long id { get; set; }
        public DateTime datetime { get; set; }
        public string code { get; set; }
        public string fullname { get; set; }
        public string address { get; set; }

        public static Transaction getByID(long id)
        {
            Transaction transaction = new Transaction();
            string SQL = "SELECT TOP 1 * FROM [transactions] WHERE [id]=@id";
            try
            {
                using (OleDbConnection con = Offline.GetConnection())
                {
                    OleDbCommand cmd = new OleDbCommand(SQL, con);
                    cmd.Parameters.AddWithValue("@id", id);
                    con.Open();
                    using (OleDbDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            transaction.id = Convert.ToInt64(rdr["id"]);
                            transaction.code = Convert.ToString(rdr["code"]);
                            transaction.datetime = Convert.ToDateTime(rdr["datetime"]);
                            transaction.fullname = Convert.ToString(rdr["fullname"]);
                            transaction.address = Convert.ToString(rdr["address"]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
            return transaction;
        }

        public string GenerateCode()
        {
            int code = 0;
            string SQL = "SELECT MAX([code]) AS 'code' FROM [transactions]";
            try
            {
                using (OleDbConnection con = Offline.GetConnection())
                {
                    OleDbCommand cmd = new OleDbCommand(SQL, con);
                    con.Open();
                    using (OleDbDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Int32.TryParse(Convert.ToString(rdr["code"]), out code);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
            code++;
            string result = code.ToString();
            while (result.Length < 5)
            {
                result = "0" + result;
            }
            return result;
        }

        public bool Save()
        {
            if (id > 0) return update();
            else return insert();
        }

        private bool insert()
        {
            bool result = false;
            string SQL = "INSERT INTO [transactions] ([code], [datetime], [fullname], [address]) VALUES (@code, @datetime, @fullname, @address)";
            try
            {
                using (OleDbConnection con = Offline.GetConnection())
                {
                    OleDbCommand cmd = new OleDbCommand(SQL, con);
                    cmd.Parameters.AddWithValue("@code", code);
                    cmd.Parameters.AddWithValue("@datetime", datetime);
                    cmd.Parameters.AddWithValue("@fullname", fullname);
                    cmd.Parameters.AddWithValue("@address", address);
                    con.Open();
                    result = cmd.ExecuteNonQuery() > 0;
                    if (result)
                    {
                        cmd.CommandText = "SELECT @@IDENTITY";
                        id = Convert.ToInt64(cmd.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
            return result;
        }

        private bool update()
        {
            bool result = false;
            string SQL = "UPDATE [transactions] SET [code]=@code, [datetime]=@datetime, [fullname]=@fullname, [address]=@address WHERE [id]=@id";
            try
            {
                using (OleDbConnection con = Offline.GetConnection())
                {
                    OleDbCommand cmd = new OleDbCommand(SQL, con);
                    cmd.Parameters.AddWithValue("@code", code);
                    cmd.Parameters.AddWithValue("@datetime", datetime);
                    cmd.Parameters.AddWithValue("@fullname", fullname);
                    cmd.Parameters.AddWithValue("@address", address);
                    cmd.Parameters.AddWithValue("@id", id);
                    con.Open();
                    result = cmd.ExecuteNonQuery() > 0;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
            return result;
        }

        public override string ToString()
        {
            string output = string.Format(
                            "TransactionID: {0} " + Environment.NewLine +
                            "Code: {1} " + Environment.NewLine +
                            "DateTime: {2} " + Environment.NewLine +
                            "fullname: {3} " + Environment.NewLine +
                            "address: {4}.",
                            id, code, datetime, fullname, address);
            return output;
        }

    }
}
