/Models/ItemsRepository.cs

//
//  ExpenseRepository.cs
//
//  Author:
//       Joe Merkel <merkeljoe9@gmail.com>
//
//  Copyright (c) 2018 Joe Merkel
//
//  This program is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  (at your option) any later version.
//
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License
//  along with this program.  If not, see <http://www.gnu.org/licenses/>.
using System;
using System.Collections.Generic;
using GameOfLife.Entities;
using T4R.Data;

namespace GameOfLife.Models
{
    public class ItemsRepository
    {
        public Items Get(DBConnection conn, int studentId)
        {
            Items result = null;
            int cashOnHand = -1;
            List<Item> items = null;
            List<Dictionary<string, object>> rs = null;

            string query;

            switch (conn.DriverClass)
            {
                case "mysql":
                    query =
@"SELECT i.*
FROM  item i 
WHERE i.user_id = ? AND NOT removed;";
                    rs = conn.ExecuteQuery(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), studentId));

                    break;
                case "postgresql":
                    query =
@"SELECT i.*
FROM  item i 
WHERE i.user_id = :1 AND NOT removed;";
                    rs = conn.ExecuteQuery(query, new ParamType(typeof(int), studentId));

                    break;
                case "sqlserver":
                    query =
@"SELECT i.*
FROM  item i 
WHERE i.user_id = @1 AND NOT removed;";
                    rs = conn.ExecuteQuery(query, new ParamType(typeof(int), studentId));

                    break;
                default:
                    throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
            }

            foreach (Dictionary<string, object> row in rs)
            {
                object id;
                object student_id;
                object amount;
                object name;
                object price;

                row.TryGetValue("item_id", out id);
                row.TryGetValue("user_id", out student_id);
                row.TryGetValue("amount", out amount);
                row.TryGetValue("name", out name);
                row.TryGetValue("price", out price);

                if(items == null)
                {
                    items = new List<Item>();
                }

                if(id != null)
                {
                    Item item = new Item((int)id, (int)student_id, (int)amount, (string)name, (int)price);
                    items.Add(item);
                }
            }

            BankRepository bank = new BankRepository();
            cashOnHand = (bank.Get(conn, studentId) ?? new Bank(studentId, 0, 0, 0)).cash_on_hand;

            if (items != null)
            {
                result = new Items(studentId, cashOnHand, items.ToArray());
            }
            else
            {
                result = new Items(studentId, cashOnHand, new Item[0]);
            }

            return result;
        }

        public bool Add(DBConnection conn, int studentId, Item item)
        {
            bool result = false;

            string query;

            switch (conn.DriverClass)
            {
                case "mysql":
                    query =
@"INSERT INTO item (user_id, amount, name, price)
VALUES (?, ?, ?, ?);";
                    result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price)) > 0);

                    break;
                case "postgresql":
                    query =
@"INSERT INTO item (user_id, amount, name, price)
VALUES (:1, :2, :3, :4);";
                    result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price)) > 0);

                    break;
                case "sqlserver":
                    query =
@"INSERT INTO item (user_id, amount, name, price)
VALUES (@1, @2, @3, @4);";
                    result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price)) > 0);

                    break;
                default:
                    throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
            }

            return result;
        }

        public bool Edit(DBConnection conn, int studentId, int itemId, Item item)
        {
            bool result = false;

            string query;

            switch (conn.DriverClass)
            {
                case "mysql":
                    query =
@"UPDATE item SET amount=?, name=?, price=?
WHERE user_id = ? AND item_id = ?;";
                    result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price), new ParamType(typeof(int), studentId), new ParamType(typeof(int), itemId)) > 0);

                    break;
                case "postgresql":
                    query =
@"UPDATE item SET amount=:1, name=:2, price=:3
WHERE user_id = :4 AND item_id = :5;";
                    result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price), new ParamType(typeof(int), studentId), new ParamType(typeof(int), itemId)) > 0);

                    break;
                case "sqlserver":
                    query =
@"UPDATE item SET amount=@1, name=@2, price=@3
WHERE user_id = @4 AND item_id = @5;";
                    result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price), new ParamType(typeof(int), studentId), new ParamType(typeof(int), itemId)) > 0);

                    break;
                default:
                    throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
            }

            return result;
        }

        public bool Delete(DBConnection conn, int studentId, int itemId)
        {
            bool result = false;

            string query;

            // See if the loan already exists.
            if (itemId > 0)
            {
                switch (conn.DriverClass)
                {
                    case "mysql":
                        query =
@"UPDATE item
SET removed = 1
WHERE user_id = ? AND item_id = ?;";
                        result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId),
                                                     new ParamType(typeof(int), itemId))) > 0;

                        break;
                    case "postgresql":
                        query =
@"UPDATE item
SET removed = 1
WHERE user_id = :1 AND item_id = :2;";
                        result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId),
                                                     new ParamType(typeof(int), itemId))) > 0;

                        break;
                    case "sqlserver":
                        query =
@"UPDATE item
SET removed = 1
WHERE user_id = @1 AND item_id = @2;";
                        result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId),
                                                     new ParamType(typeof(int), itemId))) > 0;
                        break;
                    default:
                        throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
                }
            }

            return result;
        }
    }
}