VLEX/COMMONTOOL/Service/App_Data/CommonRepository.cs

using Dapper;
using Microsoft.AspNetCore.Http.Features;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Security.Claims;
using System.Security.Principal;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using WK_KLI_LAPS_COMMONTOOL_Service.Models;
using WK_KLI_LAPS_COMMONTOOL_Service.Models.Master;
using WK_KLI_LAPS_Service.Models.Configuration;

namespace WK_KLI_LAPS_COMMONTOOL_Service.App_Data
{
    public class CommonRepository
    {
        useractivitylogModel userlogmodel;

        public string connectlinux()
        {
            string fileName = @"/efs/connectlinux.txt";

            try
            {
                // Check if file already exists. If yes, delete it.     
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                bool isWindows = System.Runtime.InteropServices.RuntimeInformation
                                               .IsOSPlatform(OSPlatform.Windows);
                // Create a new file     
                using (FileStream fs = File.Create(fileName))
                {
                    // Add some text to file    
                    Byte[] title = new UTF8Encoding(true).GetBytes("Log file");
                    fs.Write(title, 0, title.Length);
                    byte[] author = new UTF8Encoding(true).GetBytes("From ACRN");
                    fs.Write(author, 0, author.Length);
                }

                // Open the stream and read it back.    
                using (StreamReader sr = File.OpenText(fileName))
                {
                    string s = "";
                    while ((s = sr.ReadLine()) != null)
                    {
                        Console.WriteLine(s);
                    }
                }
            }
            catch (Exception Ex)
            {
                ErrorLog(Ex);
                Console.WriteLine(Ex.ToString());
            }
            return fileName;
        }
        public IEnumerable<servermasterModel> getServerInfo()
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                var param = new DynamicParameters();
                const string query = @"public.spservermasterselect";
                param.Add("servermasterid_p", dbType: DbType.Int32, value: null, direction: ParameterDirection.Input);
                return dbConnection.Query<servermasterModel>(query, param, commandType: CommandType.StoredProcedure);
            }
        }
        public string getfilesavelocation(int id)
        {
            try
            {
                bool isWindows = System.Runtime.InteropServices.RuntimeInformation
                                             .IsOSPlatform(OSPlatform.Windows);

                IEnumerable<servermasterModel> sm = getServerInfo1(id).ToList();



                string root_path = sm.First().serverpath.ToString();
                string ostype = sm.First().ostype.ToString();
                // string servertype = obj["servertype"].ToString();
                if (isWindows)
                {
                    if (ostype == "Windows")
                    {
                        return root_path;
                    }

                }
                else
                {
                    if (ostype == "Linux")
                    {
                        return root_path;
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorLog(ex);
            }
            return "";
        }

        public void ErrorLog(Exception ex)
        {
            try
            {
                //string filePath = @"C:\Error.txt";
                //string a = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
                //DriveInfo cDrive = new DriveInfo(System.Environment.CurrentDirectory);
                //string b = cDrive.RootDirectory.ToString();
                //string c = AppDomain.CurrentDomain.BaseDirectory;
                string d = System.IO.Directory.GetCurrentDirectory();
                //  File.WriteAllText(d, sb.ToString());
                using (StreamWriter writer = new StreamWriter(d + "\\error.log.txt", true))
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("------------------------Error Log start----------------------------");
                    sb.AppendLine("Date:-" + DateTime.Now.ToString());
                    sb.AppendLine("Application Name :" + ex.Source);
                    sb.AppendLine("Error Message :" + ex.Message);
                    sb.AppendLine("Method Name :" + ex.StackTrace);
                    sb.AppendLine("------------------------Error Log End----------------------------");
                    writer.WriteLine(sb.ToString());
                    writer.Close();
                    // tw.WriteLine("The next line!");
                };
            }
            catch (Exception)
            {

            }
            //System.IO.File.WriteAllText(d+"\\error.log.txt", sb.ToString());
        }

        //Common function to Execute Stored procedure to select records with out mode
        public object ExecuteSP_ReturnListwithoutmode(string spname, string inputjsonstring)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                var param = new DynamicParameters();
                string query = spname;
                param.Add("inputjsonstr", inputjsonstring);
                return dbConnection.Query<string>(query, param, commandType: CommandType.StoredProcedure);
            }
        }

        public class PushBackResult
        {
            public long out_transactionid { get; set; }
            public long out_jobworkflowid { get; set; }
        }
        public object ExecuteSP_ReturnListwithoutmodeText(string spname, string inputjsonstring)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                var param = new DynamicParameters();
                string query = "SELECT * FROM public.sppushback(@inputjsonstr::json)";
                param.Add("inputjsonstr", inputjsonstring);
                return dbConnection.Query<PushBackResult>(query, param, commandType: CommandType.Text).FirstOrDefault();
            }
        }

        //Common function to Execute Stored procedure to select records
        public object ExecuteSP_ReturnList(string spname, string inputjsonstring, string mode)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                var param = new DynamicParameters();
                string query = spname;
                param.Add("inputjsonstr", inputjsonstring);
                param.Add("mode_p", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                return dbConnection.Query<string>(query, param, commandType: CommandType.StoredProcedure);
            }
        }

        //Common function to Execute Stored procedure for insert update and delete 
        public int ExecuteSP_ReturnInt(string spname, object data, string mode, string loglabel)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                string input = JsonConvert.SerializeObject(data);
                int result = 0;

                string query = spname;
                var param = new DynamicParameters();
                param.Add("inputjsonstr", dbType: DbType.String, value: input, direction: ParameterDirection.Input);
                param.Add("mode_p", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                result = dbConnection.Query<int>(query, param, commandType: CommandType.StoredProcedure).First();

                //code to add user activity log
                if (result != 0)
                {
                    userlogmodel = new useractivitylogModel();
                    userlogmodel.useraction = loglabel + result;
                    userlogmodel.description = loglabel + result;
                    useractivityAdd(userlogmodel);
                }
                return result;
            }
        }
        public void useractivityAdd(useractivitylogModel userlog)
        {

            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                var claimsIdentity = WK_KLI_LAPS_COMMONTOOL_Service.App_Data.AppContext.Current.User.Identity as ClaimsIdentity;
                var userId = claimsIdentity.FindFirst(ClaimTypes.Name)?.Value;
                //var accessToken = LotusNotesService.App_Data.AppContext.Current.Request.Headers["Authorization"];

                userlog.userid = Convert.ToInt32(userId);
                userlog.ipaddress = WK_KLI_LAPS_COMMONTOOL_Service.App_Data.AppContext.Current.Features.Get<IHttpConnectionFeature>()?.RemoteIpAddress?.ToString();
                string output = JsonConvert.SerializeObject(userlog);
                DynamicParameters param = new DynamicParameters();
                param.Add("inputjsonstr", output);
                param.Add("mode_p", "insert");
                dbConnection.Open();
                dbConnection.Execute("spuseractivitylog", param, commandType: CommandType.StoredProcedure);
            }

        }

        //Check the given name is exists or not
        public bool CheckExists(string spname, string mode, object inputjsonval)
        {
            bool result = false;
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                string input = JsonConvert.SerializeObject(inputjsonval);
                var param = new DynamicParameters();
                string query = spname;
                param.Add("inputjsonstr", input);
                param.Add("mode_p", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                var _list = dbConnection.Query<string>(query, param, commandType: CommandType.StoredProcedure).ToList();

                if (_list.Count() > 0 && _list.ToList()[0] != null)
                    result = true;
                return result;
            }
        }

        public bool CheckExistsWithMessage(string spname, string mode, object inputjsonval, ref string message)
        {
            bool result = false;
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {

                dbConnection.Open();
                string input = JsonConvert.SerializeObject(inputjsonval);
                var param = new DynamicParameters();
                string query = spname;
                param.Add("inputjsonstr", input);
                param.Add("mode_p", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                var _list = dbConnection.Query<string>(query, param, commandType: CommandType.StoredProcedure).ToList();

                if (_list.Count() > 0 && _list.ToList()[0] != null)
                {
                    message = _list[0];

                    JObject obj = JObject.Parse(_list[0]);
                    string msg = (string)obj["message"];
                    if (msg.ToString().Trim().Length > 0)
                        result = true;
                }
                return result;
            }
        }
        public object FindByID(int id)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                DynamicParameters param = new DynamicParameters();
                var user = new { userid_p = id };
                var jsonstr = JsonConvert.SerializeObject(user);
                param.Add("inputjsonstr", jsonstr);
                param.Add("mode_p", "getbyid");
                dbConnection.Open();
                return dbConnection.Query<string>("spusermasterselect", param, commandType: CommandType.StoredProcedure);
            }
        }
        public object FindByIdForUser(int id)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                DynamicParameters param = new DynamicParameters();
                var user = new { userid_p = id };
                var jsonstr = JsonConvert.SerializeObject(user);
                param.Add("inputjsonstr", jsonstr);
                param.Add("mode_p", "getbyid");
                dbConnection.Open();
                return dbConnection.Query<object>("spusermasterselect", param, commandType: CommandType.StoredProcedure);

            }
        }
        public object ExecuteSP_ReturnList1(string spname, string inputjsonstring, string mode)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                var param = new DynamicParameters();
                string query = spname;
                param.Add("inputjsonstr", inputjsonstring);
                param.Add("mode_p", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                var list = dbConnection.Query<string>(query, param, commandType: CommandType.StoredProcedure).ToList();
                return list[0];
            }
        }
        //public object ExecuteSP_ReturnList1_(string spname, string inputjsonstring)
        //{
        //    using (IDbConnection dbConnection = ConnectionBase.Connection)
        //    {
        //        dbConnection.Open();
        //        var param = new DynamicParameters();
        //        string query = spname;
        //        param.Add("inputjsonstr", inputjsonstring);
        //        param.Add("mode_p", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
        //        var list = dbConnection.Query<string>(query, param, commandType: CommandType.StoredProcedure).ToList();
        //        return list[0];
        //    }
        //}
        public IEnumerable<servermasterModel> getServerInfo1(int id)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                var param = new DynamicParameters();

                const string query = @"public.spusrserverpathinfo";
                param.Add("p_tranid", dbType: DbType.Int32, value: id, direction: ParameterDirection.Input);
                param.Add("p_lotid", dbType: DbType.Int32, value: null, direction: ParameterDirection.Input);
                param.Add("p_option", dbType: DbType.String, value: "serverinfo_job_tranid", direction: ParameterDirection.Input);
                return dbConnection.Query<servermasterModel>(query, param, commandType: CommandType.StoredProcedure);

            }
        }
        public string GetServerPath(string transactionid, string mode, bool createdir = false)
        {
            string serverpath = string.Empty;
            try
            {
                using (IDbConnection dbConnection = ConnectionBase.Connection)
                {
                    bool isWindows = System.Runtime.InteropServices.RuntimeInformation.IsOSPlatform(OSPlatform.Windows);
                    if (isWindows)
                    {
                        dbConnection.Open();
                        var param = new DynamicParameters();
                        const string query = @"public.spusrserverpathinfo";
                        param.Add("p_tranid", dbType: DbType.Int32, value: Convert.ToInt32(transactionid), direction: ParameterDirection.Input);
                        param.Add("p_lotid", dbType: DbType.Int32, value: null, direction: ParameterDirection.Input);
                        param.Add("p_option", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                        param.Add("p_ostype", dbType: DbType.String, value: "Windows", direction: ParameterDirection.Input);
                        IEnumerable<servermasterModel> sm = dbConnection.Query<servermasterModel>(query, param, commandType: CommandType.StoredProcedure);
                        if (createdir)
                        {
                            serverpath = FormatPath(sm.First().serverpath.ToString());
                            Directory.CreateDirectory(Path.Combine(serverpath, "IN"));
                            Directory.CreateDirectory(Path.Combine(serverpath, "OUT"));
                        }
                        else
                        {
                            serverpath = FormatPath(sm.First().serverpath.ToString());
                        }
                    }
                    else
                    {
                        dbConnection.Open();
                        var param = new DynamicParameters();
                        const string query = @"public.spusrserverpathinfo";
                        param.Add("p_tranid", dbType: DbType.Int32, value: Convert.ToInt32(transactionid), direction: ParameterDirection.Input);
                        param.Add("p_lotid", dbType: DbType.Int32, value: null, direction: ParameterDirection.Input);
                        param.Add("p_option", dbType: DbType.String, value: mode, direction: ParameterDirection.Input);
                        param.Add("p_ostype", dbType: DbType.String, value: "Linux", direction: ParameterDirection.Input);
                        IEnumerable<servermasterModel> sm = dbConnection.Query<servermasterModel>(query, param, commandType: CommandType.StoredProcedure);
                        if (createdir)
                        {
                            serverpath = sm.First().serverpath.ToString();
                            Directory.CreateDirectory(Path.Combine(serverpath, "IN"));
                            Directory.CreateDirectory(Path.Combine(serverpath, "OUT"));
                        }
                        else
                        {
                            serverpath = sm.First().serverpath.ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var data_input = new
                {
                    errormessage = "Error: " + ex.Message,
                    createdby = 0,
                    controller = "UploaderApi: Downloader"
                };
                addlog(data_input, "errorlog");
                return serverpath;
            }
            return serverpath;
        }
        public int addlog(object data, string mastername)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {
                dbConnection.Open();
                string input = JsonConvert.SerializeObject(data);
                int result = 0;
                string query = @"public.sp" + mastername;
                var param = new DynamicParameters();
                param.Add("inputjsonstr", dbType: DbType.String, value: input, direction: ParameterDirection.Input);
                param.Add("mode_p", dbType: DbType.String, value: "insert", direction: ParameterDirection.Input);
                result = dbConnection.Query<int>(query, param, commandType: CommandType.StoredProcedure).First();
                return result;
            }
        }
        public lotmasterModel ExecuteSP_ReturnListForLot(string spname, string inputjsonstring, string mode)
        {
            using (IDbConnection dbConnection = ConnectionBase.Connection)
            {

                bool isWindows = System.Runtime.InteropServices.RuntimeInformation.IsOSPlatform(OSPlatform.Windows);
                if (isWindows)
                {

                    dbConnection.Open();
                    var param = new DynamicParameters();
                    string query = spname;
                    var ds = JsonConvert.DeserializeObject<lotmasterModel>(inputjsonstring);
                    param.Add("p_tranid", dbType: DbType.Int32, value: ds.transactionid, direction: ParameterDirection.Input);
                    param.Add("p_lotid", dbType: DbType.Int32, value: ds.lotid, direction: ParameterDirection.Input);
                    param.Add("p_option", dbType: DbType.String, value: ds.option, direction: ParameterDirection.Input);
                    param.Add("p_ostype", dbType: DbType.String, value: "Windows", direction: ParameterDirection.Input);
                    return dbConnection.Query<lotmasterModel>(query, param, commandType: CommandType.StoredProcedure).FirstOrDefault();
                }
                else
                {
                    dbConnection.Open();
                    var param = new DynamicParameters();
                    string query = spname;
                    var ds = JsonConvert.DeserializeObject<lotmasterModel>(inputjsonstring);
                    param.Add("p_tranid", dbType: DbType.Int32, value: ds.transactionid, direction: ParameterDirection.Input);
                    param.Add("p_lotid", dbType: DbType.Int32, value: ds.lotid, direction: ParameterDirection.Input);
                    param.Add("p_option", dbType: DbType.String, value: ds.option, direction: ParameterDirection.Input);
                    param.Add("p_ostype", dbType: DbType.String, value: "Linux", direction: ParameterDirection.Input);
                    return dbConnection.Query<lotmasterModel>(query, param, commandType: CommandType.StoredProcedure).FirstOrDefault();
                }
            }
        }
        private string FormatPath(string inputpath)
        {
            try
            {
                inputpath = inputpath.Replace("/", "\\");
                inputpath = inputpath.Replace("\\\\", "");
                inputpath = System.Text.RegularExpressions.Regex.Replace(inputpath, @"\\\\", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            }
            catch (Exception ex)
            {
                var data_input = new
                {
                    errormessage = "Error: " + ex.Message,
                    createdby = 0,
                    controller = "UploaderApi: Downloader"
                };
                addlog(data_input, "errorlog");
            }
            return string.Concat("\\\\", inputpath);
        }
        public class GrantFolderPermission : IDisposable
        {
            //  ServerInfo info = new ServerInfo();
            private bool id;
            Impartunate impartunate = null;
            CommonRepository oCommonRepository = new CommonRepository();
            public GrantFolderPermission(string pdefid, string transactionid = "", string lotid = "", string jobid = "")
            {
                var ds = new lotmasterModel();

                //var data = new
                //{
                //    transactionid = transactionid,
                //    lotid = 0,
                //    option = "logininfo"
                //};

                if (transactionid != "")
                {
                    var data = new
                    {
                        transactionid = transactionid,
                        lotid = 0,
                        option = "logininfo"
                    };
                    string inputstr = JsonConvert.SerializeObject(data);
                    ds = oCommonRepository.ExecuteSP_ReturnListForLot("spusrserverpathinfo", inputstr, "logininfo");
                }
                else if (pdefid != "")
                {
                    var data = new
                    {
                        transactionid = pdefid,
                        lotid = 0,
                        option = "serverinfo_projid"
                    };
                    string inputstr = JsonConvert.SerializeObject(data);
                    ds = oCommonRepository.ExecuteSP_ReturnListForLot("spusrserverpathinfo", inputstr, "serverinfo_projid");
                }

                else if (lotid != "")
                {
                    var data = new
                    {
                        transactionid = 0,
                        lotid = lotid,
                        option = "loginlotinfo"
                    };
                    string inputstr = JsonConvert.SerializeObject(data);
                    ds = oCommonRepository.ExecuteSP_ReturnListForLot("spusrserverpathinfo", inputstr, "loginlotinfo");
                }
                else if (jobid != "")
                {
                    var data = new
                    {
                        transactionid = jobid,
                        lotid = 0,
                        option = "serverinfo_job"
                    };
                    string inputstr = JsonConvert.SerializeObject(data);
                    ds = oCommonRepository.ExecuteSP_ReturnListForLot("spusrserverpathinfo", inputstr, "loginlotinfo");
                }
                // ds = info.GetDataTable("usr_server_path_info", info.GetNpgsqlParameter("0", Convert.ToInt32(lotid), "loginlotinfo"));
                // else if (jobid != "")
                // ds = info.GetDataTable("usr_server_path_info", info.GetNpgsqlParameterjob("0", "0", Convert.ToInt32(jobid), "loginjobinfo"));

                /*string query = @"select  lt.transactionid, sm.ipaddress, sm.username, sm.password, sm.domain  from lot_transaction as lt 
                                Left join lot_master lm on lt.lotid=lm.lotid 
                                left join job_master jm on lm.jobid = jm.jobid
                                left join project_definition pd on jm.projectdefinitionid = pd.projectdefinitionid
                                left join server_master sm on pd.fileserver = sm.serverid where lt.transactionid = '" + transid + "'";*/

                //using (DataSet ds = info.GetDataSet(query))
                //{
                if (ds != null)
                {

                    string username = ds.username;
                    string password = ds.password;
                    string domain = ds.domain;
                    impartunate = new Impartunate(username, password, domain);
                    impartunate.AllowAccesstoServer();
                }
                //}
            }

            public Impartunate GetImpartunate()
            {
                return impartunate;
            }

            public void Dispose()
            {
                if (id == true)
                    return;
                impartunate.RemoveServerAccess();
                impartunate = null;
                id = true;
                GC.SuppressFinalize(this);
            }

        }


        public class Impartunate
        {
            #region Impersonating User

            #region Impersonation
            static IntPtr tokenHandle;
            // static WindowsImpersonationContext impersonatedUser;
            private string impersUsername { get; set; }
            private string impersPwd { get; set; }
            private string impersDomain { get; set; }
            static WindowsIdentity newId;
            #endregion

            public Impartunate(string username, string password, string domain)
            {
                impersUsername = username;
                impersPwd = password;
                impersDomain = domain;
            }


            // public static WindowsImpersonationContext oImpersonatedUser;
            [DllImport("advapi32.dll", SetLastError = true)]
            private static extern bool LogonUser(string sUsername, string sDomain, string sPassword, int iLogonType, int iLogonProvider, ref IntPtr oToken);

            [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
            private static extern bool CloseHandle(IntPtr oHandle);

            //public bool AllowAccesstoServer()
            public WindowsIdentity AllowAccesstoServer()
            {
                try
                {
                    tokenHandle = IntPtr.Zero;
                    bool Result = LogonUser(impersUsername, impersDomain, impersPwd, 2, 0, ref tokenHandle);
                    if (Result)
                    {
                        newId = new WindowsIdentity(tokenHandle);
                        //impersonatedUser = newId.Impersonate();
                        //return true;
                        return newId;
                    }
                    else
                    {
                        //return false;
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    //return false;
                    return null;
                }
            }

            public void RemoveServerAccess()
            {
                try
                {
                    //if (impersonatedUser != null)
                    //{
                    //    impersonatedUser.Undo();
                    //}
                    if (tokenHandle != IntPtr.Zero)
                    {
                        CloseHandle(tokenHandle);
                    }
                }
                catch (Exception ex) { }
            }

            private void ImpersonateUser()
            {
                System.IO.FileInfo localFileLastModified;
                System.IO.FileInfo serverFileLastModified;
                if (AllowAccesstoServer() == null)
                {
                    RemoveServerAccess();
                }
            }

            #endregion
        }
    }
}