ReadSBAktieTrans/LoadStockDbFromTrans/StocksProviders/DapperStocksProvider.cs

using Dapper;
using LoadStockDbFromTrans.DbConnections;
using LoadStockDbFromTrans.models;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LoadStockDbFromTrans.StocksProviders
{
    public class DapperStocksProvider : IDapperStocksProvider
    {
        private const string GET_ALL_STOCKS_SQL = @"
            SELECT *
            FROM Stocks";

        private const string ADD_NEW_STOCKMEMBER = @"
            INSERT INTO STOCKS (
                StockId,
                StockExtId,
                BuyValue,
                BuyDate,
                ActValue,
                ActDate,
                ActAmount,
                SoldValue,
                SoldDate,
                Comment,
                PostAmount,
                SoldStockPrice
            )
            VALUES (
                @StockId,
                @StockExtId,
                @BuyValue,
                @BuyDate,
                @ActValue,
                @ActDate,
                @ActAmount,
                @SoldValue,
                @SoldDate,
                @Comment,
                @PostAmount,
                @SoldStockPrice
            )";

        private const string UPDATE_STOCKMEMBER = @"
            UPDATE Stocks
               SET StockId = @StockId,
                   StockExtId = @StockExtId,
                   BuyValue = @BuyValue,
                   BuyDate = @BuyDate,
                   ActValue = @ActValue,
                   ActDate = @ActDate,
                   ActAmount = @ActAmount,
                   SoldValue = @SoldValue,
                   SoldDate = @SoldDate,
                   Comment = @Comment,
                   PostAmount = @PostAmount,
                   SoldStockPrice = @SoldStockPrice
             WHERE Id = @Id";

        private const string GET_STOCKS_BY_STOCKID = @"
            SELECT *
            FROM Stocks
            WHERE StockId = @StockId 
            AND BuyDate <= @BuyDate
            ORDER BY BuyDate DESC";

        private const string GET_STOCKS_BY_STOCKIDPART = @"
            SELECT *
            FROM Stocks
            WHERE Substring(StockId,1,@Length) = @StockIdPart 
            AND BuyDate <= @BuyDate
            ORDER BY BuyDate DESC";

        private const string ADD_TO_STOCKNAME = @"
            Insert into StockNames (StockNameWeb, StockNameBank)
            values ( @StockId, @StockId)";

        private const string REMOVE_STOCKNAMES = @"
            Delete 
            from StockNames";

        private const string GET_WEB_STOCKNAMES = @"
            select distinct StockId 
            from StocksOld";

        private const string UPDATE_STOCKNAME_BANK = @"
            UPDATE StockNames
               SET StockNameBank = @StockNameBank
             WHERE StockNameWeb = @StockNameWeb";

        private const string GET_WEB_STOCKNAM_FULL = @"
            SELECT *
            FROM StockNames";

        private const string WEB_STOCKNAMES_EXISTS = @"
            SELECT CAST(
                CASE WHEN EXISTS (
                    SELECT 1 FROM StockNames WHERE StockNameWeb = @StockNameWeb)
                THEN 1 ELSE 0 END as BIT)";

        private const string DELETE_STOCK_BY_ID = @"
            SELECT *
            FROM Stocks
            WHERE Id = @Id";

        private readonly SqliteDbConnectionFactory _sqliteDbConnectionFactory;
        private readonly ILogger<DapperStocksProvider> _log;

        public DapperStocksProvider(SqliteDbConnectionFactory sqliteDbConnectionFactory, ILogger<DapperStocksProvider> log)
        {
            _sqliteDbConnectionFactory = sqliteDbConnectionFactory;
            _log = log;
        }
        public async Task AddStock(StockMember stockMember)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                _log.LogInformation("ADD_NEW_STOCKMEMBER");

                object parameters = new
                {
                    StockId = stockMember.StockId,
                    StockExtId = stockMember.StockExtId,
                    BuyValue = stockMember.BuyValue,
                    BuyDate = stockMember.BuyDate,
                    ActValue = stockMember.ActValue,
                    ActDate = stockMember.ActDate,
                    ActAmount = stockMember.ActAmount,
                    SoldValue = stockMember.SoldValue,
                    SoldDate = stockMember.SoldDate,
                    Comment = stockMember.Comment,
                    PostAmount = stockMember.PostAmount,
                    SoldStockPrice = stockMember.SoldStockPrice
                };

                await database.ExecuteAsync(ADD_NEW_STOCKMEMBER, parameters);
            }
        }
        public async Task AddWebStockNames(string stockName)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                _log.LogInformation("ADD_TO_STOCKNAME");
                object parameters = new
                {
                    StockId = stockName
                };
                await database.ExecuteAsync(ADD_TO_STOCKNAME, parameters);
            }
        }
        public async Task CleanStockNames()
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                _log.LogInformation("REMOVE_NEW_STOCKNAME");
                await database.ExecuteAsync(REMOVE_STOCKNAMES);
            }
        }
        public async Task<IEnumerable<StockMember>> GetAllStocks()
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                _log.LogInformation(GET_ALL_STOCKS_SQL);
                IEnumerable<StockMember> stockMembers = await database.QueryAsync<StockMember>(GET_ALL_STOCKS_SQL);
                return stockMembers.ToList();
            }
        }
        public async Task<IEnumerable<string>> GetStockNames()
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                _log.LogInformation("GET_WEB_STOCKNAMES");

                IEnumerable<string> stockNames = await database.QueryAsync<string>(GET_WEB_STOCKNAMES);
                return stockNames.ToList();
            }
        }
        public async Task<IEnumerable<StockMember>> GetStocksByStockId(string stockId, DateTime Affärsdag)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                // Obs ta hänsyn till köpdatum
                _log.LogInformation("GET_STOCKS_BY_STOCKID");

                object parameters = new
                {
                    StockId = stockId,
                    BuyDate = Affärsdag
                };

                IEnumerable<StockMember> stockMembers = await database.QueryAsync<StockMember>(GET_STOCKS_BY_STOCKID, parameters);
                return stockMembers.ToList();
            }
        }

        public async Task<IEnumerable<StockMember>> GetStocksByStockIdPart(string stockIdPart, DateTime Affärsdag)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                // Obs ta hänsyn till köpdatum
                _log.LogInformation("GET_STOCKS_BY_STOCKIDPART");
                var tmpstr = stockIdPart.Substring(0, stockIdPart.IndexOf(" "));
                object parameters = new
                {
                    Length = tmpstr.Length,
                    StockIdPart = tmpstr,
                    BuyDate = Affärsdag
                };

                IEnumerable<StockMember> stockMembers = await database.QueryAsync<StockMember>(GET_STOCKS_BY_STOCKIDPART, parameters);
                return stockMembers.ToList();
            }
        }
        public async Task UpdateStock(StockMember stockMember)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {

                object parameters = new
                {
                    Id = stockMember.Id,
                    StockId = stockMember.StockId,
                    StockExtId = stockMember.StockExtId,
                    BuyValue = stockMember.BuyValue,
                    BuyDate = stockMember.BuyDate,
                    ActValue = stockMember.ActValue,
                    ActDate = stockMember.ActDate,
                    ActAmount = stockMember.ActAmount,
                    SoldValue = stockMember.SoldValue,
                    SoldDate = stockMember.SoldDate,
                    Comment = stockMember.Comment,
                    PostAmount = stockMember.PostAmount,
                    SoldStockPrice = stockMember.SoldStockPrice
                };

                _log.LogInformation($"UPDATE_STOCKMEMBER {stockMember.Id} = {stockMember.StockId}");

                await database.ExecuteAsync(UPDATE_STOCKMEMBER, parameters);
            }
        }
        public async Task UpdateStockName(string stockNameBank, string stockNameWeb)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {

                var exists = await database.ExecuteScalarAsync<bool>(WEB_STOCKNAMES_EXISTS, new { StockNameWeb = stockNameWeb });

                if (!exists)
                {
                    await AddWebStockNames(stockNameWeb);
                }

                object parameters = new
                {
                    StockNameBank = stockNameBank,
                    StockNameWeb = stockNameWeb,
                };

                _log.LogInformation($"UPDATE_STOCKNAME_BANK {stockNameBank} , {stockNameWeb}");

                await database.ExecuteAsync(UPDATE_STOCKNAME_BANK, parameters);
            }
        }

        public async Task<IEnumerable<StockNames>> GetStockNameTable()
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                _log.LogInformation("GET_WEB_STOCKNAMES");

                IEnumerable<StockNames> stockNames = await database.QueryAsync<StockNames>(GET_WEB_STOCKNAM_FULL);
                return stockNames.ToList();
            }
        }

        public async Task RemoveStockById(int id)
        {
            using (IDbConnection database = _sqliteDbConnectionFactory.Connect())
            {
                // Obs ta hänsyn till köpdatum
                _log.LogInformation("DELETE_STOCK_BY_ID");

                await database.ExecuteAsync(DELETE_STOCK_BY_ID, new { Id = id });
            }
        }

    }
}