UserController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using cppAPI.Models;
using Microsoft.AspNetCore.Cors;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Authorization;
using cppAPI.Controllers;
using System.Data;
using System.Data.Common;
using static cppAPI.Modules.SharedFunctions;

namespace FDCoreAPI.Controllers
{
    [EnableCors("CorsPolicy")]

    [Route("api/[controller]")]
    public class UserController : Controller
    {
        private readonly CPP_TOOLContext _context;

        public UserController(CPP_TOOLContext context)
        {
            _context = context;
        }

        [HttpGet]
        [Authorize]
        public object Get()
        {
            try
            {
                var user = from u in _context.TbUser
                           select new
                           {
                               u.CreationDate,
                               u.Email,
                               u.FirstLoginDate,
                               u.FirstName,
                               u.IsActive,
                               u.LastLoginDate,
                               u.LastName,
                               u.PlanerName,
                               u.Qnr,
                               u.Token,
                               u.TelefonNumber,
                               u.UniqueName,
                               u.Userid,
                               UserName = u.FirstName + ' ' + u.LastName,
                           };
                return user;
            }
            catch (Exception ex)
            {
                return new { status = 500, message = ex.Message };
            }
        }

        [HttpGet]
        [Authorize]
        [Route("assignedUser")]
        public object assignedUser(int groupid)
        {
            try
            {
                string strSQL = "SELECT t.userid, t.uniqueName, t.lastName, t.firstName, t.email, "
                    + "t.planerName, t.qnr, t.telefonNumber, t.token, "
                    + "t.creationDate, t.firstLoginDate, t.lastLoginDate, t.isActive, t.firstName + ' ' + t.lastName AS userName, "
                    + "CONVERT(bit, CASE WHEN(SELECT COUNT(GroupID) FROM tbUserGroupMapping WHERE GroupId = " + groupid.ToString() + " AND UserID = t.UserID) > 0 THEN 1 ELSE 0 END) AS isAssigned "
                    + "FROM tb_User t;";

                var con = _context.Database.GetDbConnection();
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSQL;

                    DbDataReader dr = cmd.ExecuteReader();
                    List<object> user = new List<object>();

                    string errMessage = "";
                    if (dr.HasRows)
                    {
                        errMessage = GetListFromReader(dr, ref user);
                    }

                    if (errMessage == "")
                    {
                        return user;
                    }
                    else
                    {
                        return new { status = 500, message = errMessage };
                    }
                }
            }
            catch (Exception ex)
            {
                return new { status = 500, message = ex.Message };
            }

        }


        [HttpGet]
        [Authorize]
        [Route("byGroupId")]
        public object byGroupId(int groupID)
        {
            try
            {
                string strSQL = "SELECT tb_User.UserId, tb_User.UniqueName, tb_User.LastName, tb_User.FirstName, tb_User.Email, "
                                     + "tb_User.CreationDate, tb_User.FirstLoginDate, tb_User.LastLoginDate, tb_User.IsActive, tb_User.FirstName + ' ' + tb_User.LastName AS UserName "
                              + "FROM tb_User INNER JOIN tbUserGroupMapping ON tb_User.UserID = tbUserGroupMapping.UserID "
                              + "WHERE tb_User.IsActive = 1 AND tbUserGroupMapping.GroupID = " + groupID.ToString() + ";";

                var con = _context.Database.GetDbConnection();
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSQL;

                    DbDataReader dr = cmd.ExecuteReader();
                    List<object> user = new List<object>();

                    string errMessage = "";
                    if (dr.HasRows)
                    {
                        errMessage = GetListFromReader(dr, ref user);
                    }

                    if (errMessage == "")
                    {
                        return user;
                    }
                    else
                    {
                        return new { status = 500, message = errMessage };
                    }
                }
            }
            catch (Exception ex)
            {
                return new { status = 500, message = ex.Message };
            }
        }

        // GET api/values/5
        [HttpGet]
        [Authorize]
        [Route("byEMail")]
        public object byEMail(string Email)
        {
            try
            {
                string strSQL = "SELECT tb_User.UserID, tb_User.UniqueName, tb_User.LastName, tb_User.FirstName, tb_User.EMail, tb_User.CreationDate, tb_User.FirstLoginDate, tb_User.LastLoginDate, tb_User.IsActive, tb_User.FirstName + ' ' + tb_User.LastName AS UserName, "
                    + "dbo.ConcatUserGroups(tb_User.UserID) as UserGroups "
                    + "FROM tb_User "
                    + "WHERE tb_User.EMail='" + Email + "';";

                var con = _context.Database.GetDbConnection();
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSQL;

                    DbDataReader dr = cmd.ExecuteReader();
                    List<object> user = new List<object>();

                    string errMessage = "";
                    if (dr.HasRows)
                    {
                        errMessage = GetListFromReader(dr, ref user);
                    }

                    if (errMessage == "")
                    {
                        return user;
                    }
                    else
                    {
                        return new { status = 500, message = errMessage };
                    }
                }
            }
            catch (Exception ex)
            {
                return new { status = 500, message = ex.Message };
            }
        }

        [HttpGet("{bmwPlanner}")]
        [Authorize]
        [Route("PlanerAnalysis")]
        public object PlanerAnalysis(string bmwPlanner)
        {
            try
            {
                string strSQL = "SELECT "
                    + "(SELECT '" + bmwPlanner + "') AS bmwPlanner, "
                    + "(SELECT dbo.GetPlannerSuppliers('" + bmwPlanner + "')) AS plannerSuppliers, "
                    + "(SELECT dbo.GetPlannerActiveSuppliers('" + bmwPlanner + "')) AS plannerActiveSuppliers, "
                    + "(SELECT dbo.GetPlannerOpenTickets('" + bmwPlanner + "')) AS plannerOpenTickets, "
                    + "(SELECT dbo.GetPlannerRestingTime('" + bmwPlanner + "')) AS plannerRestingTime, "
                    + "(SELECT dbo.GetPlannerClosedTickets('" + bmwPlanner + "')) AS plannerClosedTickets, "
                    + "(SELECT dbo.GetPlannerSuppliersIncomplete('" + bmwPlanner + "')) AS plannerSuppliersIncomplete, "
                    + "(SELECT dbo.GetPlannerRedFlags('" + bmwPlanner + "')) AS flgRedCount, "
                    + "(SELECT dbo.GetPlannerBlueFlags('" + bmwPlanner + "')) AS flgBlueCount, "
                    + "(SELECT dbo.GetPlannerGreenFlags('" + bmwPlanner + "')) AS flgGreenCount, "
                    + "(SELECT dbo.GetPlannerYellowFlags('" + bmwPlanner + "')) AS flgYellowCount, "
                    + "(SELECT dbo.GetPlannerPurpleFlags('" + bmwPlanner + "')) AS flgPurpleCount ";


                var con = _context.Database.GetDbConnection();
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSQL;
                    cmd.CommandTimeout = 900000;

                    DbDataReader dr = cmd.ExecuteReader();
                    List<object> analysis = new List<object>();

                    string errMessage = "";
                    if (dr.HasRows)
                    {
                        errMessage = GetListFromReader(dr, ref analysis);
                    }

                    if (errMessage == "")
                    {
                        return analysis;
                    }
                    else
                    {
                        return new { status = 500, message = errMessage };
                    }
                }
            }
            catch (Exception ex)
            {
                return new { status = 500, message = ex.Message };
            }
        }


        // POST api/values
        [HttpPost]
        [Authorize]
        public object Post([FromBody]TbUser user)
        {
            var response = new object();
            try
            {
                var exists = _context.TbUser.AsNoTracking().FirstOrDefault(u => u.UniqueName == user.UniqueName || u.PlanerName == user.PlanerName || u.Qnr == user.Qnr);

                if (exists != null)
                {
                    response = new { ststus = 250, message = "Der Unique Name '" + user.UniqueName + "' oder Planer '" + user.PlanerName + "' oder Q-Nummer '" + user.Qnr + "' wurden bereist verwendet!" };
                } else
                {
                    _context.TbUser.Add(user);
                    _context.SaveChanges();
                    int userId = user.Userid;

                    response = new { status = 200, message = "OK", id = userId };
                }
            }
            catch (Exception ex)
            {
                response = new { status = 500, message = ex.Message };
            }
            return response;
        }


        [HttpPost]
        [Authorize]
        [Route("SetPassword")]
        public object SetPassword([FromBody]TbUserCredentials userCredentials)
        {
            var response = new object();
            try
            {
                string salt = null;
                string UserPassword = userCredentials.Password;
                var hashedPassword = SharedFunctions.CreateHashString(UserPassword, out salt);

                userCredentials.Password = hashedPassword;
                userCredentials.Salt = salt;

                _context.TbUserCredentials.Add(userCredentials);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                response = new { status = 500, message = ex.Message };
            }

            return response;
        }


        [HttpPut]
        [Authorize]
        [Route("ChangePassword")]

        public object ChangePassword([FromBody]TbUserCredentials credentials)
        {
            var response = new object();
            try
            {
                string salt = null;
                string UserPassword = credentials.Password;
                var hashedPassword = SharedFunctions.CreateHashString(UserPassword, out salt);

                TbUserCredentials cred = _context.TbUserCredentials.SingleOrDefault(uc => uc.Userid == credentials.Userid);
                cred.Password = hashedPassword;
                cred.Salt = salt;

                _context.TbUserCredentials.Update(cred);
                _context.SaveChanges();

                response = new { status = 200, message = "OK" };


            }
            catch (Exception ex)
            {
                response = new { status = 500, message = ex.Message };
            }

            return response;
        }



        // PUT api/values/5
        [HttpPut]
        [Authorize]
        public object Put([FromBody]TbUser user)
        {
            var response = new object();
            try
            {
                var exists = _context.TbUser.AsNoTracking().FirstOrDefault(u => u.Userid != user.Userid
                            && (u.UniqueName == user.UniqueName || u.PlanerName == user.PlanerName || u.Qnr == user.Qnr));

                if (exists != null)
                {
                    response = new { ststus = 250, message = "Der Unique Name '" + user.UniqueName + "' oder Planer '" + user.PlanerName + "' oder Q-Nummer '" + user.Qnr + "' wurden bereist verwendet!" };
                } else
                {
                    _context.TbUser.Update(user);
                    _context.SaveChanges();

                    response = new { status = 200, message = "OK" };
                }
            }
            catch (Exception ex)
            {
                response = new { status = 500, message = ex.Message };
            }

            return response;
        }


        // DELETE api/values/5
        [HttpDelete("{id}")]
        [Authorize]
        public object Delete(int id)
        {
            var response = new object();
            try
            {
                TbUser userToDelete = _context.TbUser.FirstOrDefault(u => u.Userid == id);
                TbUserCredentials credToDelete = _context.TbUserCredentials.FirstOrDefault(u => u.Userid == id);
                TbUserGroupMapping[] mappingToDelete = _context.TbUserGroupMapping.Where(u => u.UserId == id).ToArray();

                if (mappingToDelete.Length > 0)
                {
                    _context.TbUserGroupMapping.RemoveRange(mappingToDelete);
                }
                if (credToDelete != null)
                {
                    _context.TbUserCredentials.Remove(credToDelete);
                }
                if (userToDelete != null)
                {
                    _context.TbUser.Remove(userToDelete);
                }

                _context.SaveChanges();

                response = new { status = 200, message = "Löschen erfolgreich" };
            }
            catch (Exception e)
            {
                response = new { status = 500, message = e.Message };
            }
            return response;
        }

        [HttpPost]
        [Authorize]
        [Route("setUserSettings")]

        public object SetUserSettings([FromBody] TbUserSettings UserSettings)
        {
            var response = new object();
            var settingId = 0;
            try
            {
                if(UserSettings.Settingid == 0)
                {
                    _context.TbUserSettings.Add(UserSettings);
                }
                else{
                    _context.TbUserSettings.Update(UserSettings);
                }
                _context.SaveChanges();
                settingId = UserSettings.Settingid;
                response = new { status = 200, message = "Speichern der Settings erfolgreich", settingId = settingId };
            }
            catch (Exception e)
            {
                response = new { status = 500, message = e.Message };
            }

            return response;
        }

        [HttpGet]
        [Authorize]
        [Route("getUserSettings")]

        public object GetUserSettings(int userID, string settingName)
        {
            var response = new object();
            try
            {
                var userSettings = _context.TbUserSettings.Where(u => u.Userid == userID && u.SettingName == settingName).ToList();
                response = new { status = 200, userSettings = userSettings };
            }
            catch (Exception e)
            {
                response = new { status = 500, message = e.Message };
            }


            return response;
        }

    }
}