DbRESTFul/src/Tools/Wicture.DbRESTFul.PMT/Repositories/DefaultCsiRepository.cs

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Wicture.DbRESTFul.Infrastructure.Repository;
using Wicture.DbRESTFul.PMT.Models;
using Wicture.DbRESTFul.PMT.Scaffolding;

namespace Wicture.DbRESTFul.PMT.Repositories
{
    public class DefaultCsiRepository : DbRESTFulRepository
    {
        public async Task<object> CheckExistName(JToken param)
        {
            var apiData = param["apiData"].ToObject<List<ACIData>>();
            var tableName = string.Join("-", apiData.Select(s => s.TableName).Distinct().ToArray());
            var methods = param.Value<JArray>("selectedMethods").ToObject<List<Methods>>();
            var csiNames = new List<string>();
            foreach (var item in methods)
            {
                csiNames.Add($"{item.Title}_{tableName}");
            }
            return await base.InvokeAsync("CheckCSINames", new { csiNames, projectId = param.Value<int>("projectId") });
        }

        public object LoadTablesForProject(JToken param)
        {
            var connectionString = param.Value<string>("connectionString");
            var projectId = param.Value<int>("projectId");
            var helper = new ScaffoldingHelper(connectionString, projectId);
            var table = helper.ListTables().Select(t => new { title = t.name, key = 1, children = t.columns });
            return table;
        }

        public async Task<object> CreateCsis(JToken param)
        {
            var projectId = param.Value<int>("projectId");
            var methods = param.Value<JArray>("selectedMethods").ToObject<List<Methods>>();
            var module = param["module"].ToObject<Module>();
            var apiData = param["apiData"].ToObject<List<ACIData>>();
            var helper = new ScaffoldingHelper(param.Value<string>("connectionString"), param.Value<int>("projectId"));
            var tableNames = apiData.Select(s => s.TableName).Distinct().ToArray();
            var tables = helper.ListTables().Where(w => (tableNames.Contains(w.name)));
            var allColumns = new List<DefaultACIColumn>();
            tables.ForEach(f =>
            {
                var columns = new List<DefaultACIColumn>();
                f.columns.ForEach(cf =>
                {
                    if (apiData.Where(w => w.TableName == f.name).Select(s => s.ColumnName).Contains(cf.name))
                        columns.Add(cf);
                });
                allColumns.AddRange(columns);
            });

            using (var cnn = ConnectionManager.GetConnection(false))
            {
                cnn.Open();
                var transaction = cnn.BeginTransaction();
                try
                {
                    int databaseType = (await InvokeSingleOrDefaultAsync<dynamic>("GetProjectDbType", new { projectId }, cnn)).databaseType;
                    foreach (var item in methods)
                    {
                        var data = this.ParamIntegration(projectId, module, item.Title, string.Join("-", tableNames), allColumns, databaseType);
                        await base.InvokeAsync("CreateCSI", data, cnn, transaction);
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
            return null;
        }

        #region Private Methods
        private object ParamIntegration(int projectId, Module module, Method method, string tableName, IReadOnlyCollection<DefaultACIColumn> columns, int databaseType = 0)
        {
            var result = new CsiCreateData();
            var allColumnNames = columns.Select(c => c.name);
            switch (method)
            {
                case Method.Get:
                    {
                        var primaryKey = string.Join(", ", columns.Where(w => (w.primary_key)).Select(c => c.name));

                        result.code = $"SELECT {string.Join(", ", allColumnNames)} FROM { tableName } WHERE { primaryKey } = @{ primaryKey };";
                        result.rawCode = $"SELECT {string.Join(", \n\t", allColumnNames)} \nFROM { tableName } \nWHERE { primaryKey } = @{ primaryKey };";
                        result.name = $"Get_{ tableName }";
                        result.module = module.ModuleName;
                        result.moduleId = module.ModuleId;
                        result.projectId = projectId;
                        result.queryOnly = true;
                        result.requiredTransaction = false;
                        result.resultSet = "S";
                    }
                    break;
                case Method.List:
                    {
                        result.name = $"List_{ tableName }";
                        result.module = module.ModuleName;
                        result.moduleId = module.ModuleId;
                        result.projectId = projectId;
                        result.queryOnly = true;
                        result.requiredTransaction = false;
                        result.resultSet = "M";

                        // MYSQL 0
                        if (databaseType == 0)
                        {
                            result.code = $"SELECT {string.Join(", ", allColumnNames)} FROM { tableName } WHERE 1 = 1 LIMIT @pageStart, @pageSize;";
                            result.rawCode = $"SELECT {string.Join(", \n\t", allColumnNames)} \nFROM { tableName } \nWHERE 1 = 1 \nLIMIT @pageStart, @pageSize;";
                            result.middleWares = "{\"pagination\":{\"size\":\"pageSize\",\"count\":\"totalCount\",\"page\":\"pageIndex\"}}";
                        }

                        // SQLSERVER 1
                        else
                        {
                            var primaryKey = string.Join(", ", columns.Where(w => (w.primary_key)).Select(c => c.name));

                            result.code = $"SELECT Top @pageSize * FROM(SELECT ROW_NUMBER() OVER(ORDER BY t.{ primaryKey }) AS RowNumber, t.* FROM { tableName } t) P WHERE RowNumber > ((@pageIndex - 1) * @pageSize);";
                            result.rawCode = $"SELECT Top @pageSize * FROM\n\t(SELECT ROW_NUMBER() OVER(ORDER BY t.{ primaryKey }) AS RowNumber, t.* FROM { tableName } t) P \nWHERE RowNumber > ((@pageIndex - 1) * @pageSize);";
                            result.middleWares = JsonConvert.SerializeObject(new
                            {
                                pagination = new
                                {
                                    size = "pageSize",
                                    count = "totalCount",
                                    page = "pageIndex"
                                },
                                replace = new object[]
                                {
                                    "pageSize"
                                }
                            });
                        }
                    }
                    break;
                case Method.Create:
                    {
                        var columnNames = string.Join(", ", columns.Where(w => (!w.primary_key)).Select(c => c.name));
                        var columnValues = string.Join(", ", columns.Where(w => (!w.primary_key)).Select(c => "@" + c.name));
                        var rawColumnNames = string.Join(", \n\t", columns.Where(w => (!w.primary_key)).Select(c => c.name));
                        var rawColumnValues = string.Join(", \n\t", columns.Where(w => (!w.primary_key)).Select(c => "@" + c.name));

                        result.name = $"Create_{ tableName }";
                        result.module = module.ModuleName;
                        result.moduleId = module.ModuleId;
                        result.projectId = projectId;
                        result.queryOnly = false;
                        result.requiredTransaction = true;
                        result.resultSet = "S";

                        // MYSQL 0
                        if (databaseType == 0)
                        {
                            result.code = $"INSERT INTO { tableName }({ columnNames }) VALUES ({ columnValues });SELECT LAST_INSERT_ID() AS id;";
                            result.rawCode = $"INSERT INTO { tableName }(\n\t{ rawColumnNames }) \nVALUES (\n\t{ rawColumnValues });\nSELECT LAST_INSERT_ID() AS id;";
                        }

                        // SQLSERVER 1
                        else
                        {
                            result.code = $"INSERT INTO { tableName }({ columnNames }) VALUES ({ columnValues });SELECT @@IDENTITY AS id;";
                            result.rawCode = $"INSERT INTO { tableName }(\n\t{ rawColumnNames }) \nVALUES (\n\t{ rawColumnValues });\nSELECT @@IDENTITY AS id;";
                        }
                    }
                    break;
                case Method.Update:
                    {
                        var primaryKey = string.Join(", ", columns.Where(w => (w.primary_key)).Select(c => c.name));
                        var nameValues = string.Join(", ", columns.Where(w => (!w.primary_key)).Select(c => $"{c.name} = @{c.name}"));
                        var rawNameValues = string.Join(", ", columns.Where(w => (!w.primary_key)).Select(c => $"\n\t{c.name} = @{c.name}"));

                        result.name = $"Update_{ tableName }";
                        result.module = module.ModuleName;
                        result.moduleId = module.ModuleId;
                        result.projectId = projectId;
                        result.queryOnly = false;
                        result.requiredTransaction = true;
                        result.resultSet = "S";

                        // MYSQL 0
                        if (databaseType == 0)
                        {
                            result.code = $"UPDATE { tableName } SET { nameValues } WHERE { primaryKey } = @{ primaryKey }; SELECT ROW_COUNT() AS count;";
                            result.rawCode = $"Update { tableName } SET { rawNameValues } \nWHERE { primaryKey } = @{ primaryKey }; \nSELECT ROW_COUNT() AS count;";
                        }

                        // SQLSERVER 1
                        else
                        {
                            result.code = $"UPDATE { tableName } SET { nameValues } WHERE { primaryKey } = @{ primaryKey }; SELECT @@ROWCOUNT AS count;";
                            result.rawCode = $"Update { tableName } SET { rawNameValues } \nWHERE { primaryKey } = @{ primaryKey }; \nSELECT @@ROWCOUNT AS count;";
                           
                        }
                    }
                    break;
                case Method.Delete:
                    {
                        var primaryKey = string.Join(", ", columns.Where(w => (w.primary_key)).Select(c => c.name));

                        result.name = $"Delete_{ tableName }";
                        result.module = module.ModuleName;
                        result.moduleId = module.ModuleId;
                        result.projectId = projectId;
                        result.queryOnly = false;
                        result.requiredTransaction = true;
                        result.resultSet = "S";

                        // MYSQL 0
                        if (databaseType == 0)
                        {
                            result.code = $"DELETE FROM { tableName } WHERE { primaryKey } = @{ primaryKey }; SELECT ROW_COUNT() AS count;";
                            result.rawCode = $"DELETE FROM { tableName } \nWHERE { primaryKey } = @{ primaryKey }; \nSELECT ROW_COUNT() AS count;";
                        }

                        // SQLSERVER 1
                        else
                        {
                            result.code = $"DELETE FROM { tableName } WHERE { primaryKey } = @{ primaryKey }; SELECT @@ROWCOUNT AS count;";
                            result.rawCode = $"DELETE FROM { tableName } \nWHERE { primaryKey } = @{ primaryKey }; \nSELECT @@ROWCOUNT AS count;";
                        }
                    }
                    break;
                default:
                    throw new ArgumentOutOfRangeException(nameof(method), method, null);
            }
            return result;
        }
        #endregion
    }
}