using System;
using Npgsql;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Linq;
using System.IO;
using System.Runtime.InteropServices;
using System.Security.Principal;

namespace CUP_POD_Mail_Reader
{
    public class FileDownloadingDBOperations
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connectStr"].ToString());
        NpgsqlConnection postgrescon = new NpgsqlConnection(ConfigurationManager.AppSettings["npgconn"].ToString());

        public bool InsertDownloadFileData(DownloadedFile filedata)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("insert into Downloaded_File_Data values(@filename,@filesize,@downloadtime,@download_status)", conn);
                string download_status = filedata.DownloadStatus ? "Success" : "Fail";
                cmd.Parameters.AddWithValue("@filename", filedata.FileName);                
                cmd.Parameters.AddWithValue("@download_status", download_status);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                byte result = Convert.ToByte(cmd.ExecuteNonQuery());
                bool insertstatus = result == 1 ? true : false;
                conn.Close();
                return insertstatus;
            }
            catch (Exception ex)
            {
                conn.Close();
                Console.WriteLine(ex.Message + " at " + ex.StackTrace);
                throw;
            }
        }

        public void insertMessageId(MessageId msg)
        {
            try
            {
                conn.Open();
                string qry = "INSERT INTO [dbo].[MessagereadTime] ([MessageId],[ReadTime],[projectcode],[Status],[Subject],[environment]) " +
                    "VALUES (@msgid,@readtime,@projectcode,@status,@subject,@environment)";
                //SqlCommand cmd = new SqlCommand("insert into MessagereadTime values(@msgid,@readtime,@projectcode,@status,@subject,@environment)", conn);
                SqlCommand cmd = new SqlCommand(qry, conn);
                cmd.Parameters.AddWithValue("@msgid", msg.MailId);
                cmd.Parameters.AddWithValue("@readtime", msg.MailReadDate);
                cmd.Parameters.AddWithValue("@projectcode", msg.ProjectCode);
                int readStatus = msg.Status == true ? 1 : 0;
                cmd.Parameters.AddWithValue("@status", readStatus);
                cmd.Parameters.AddWithValue("@subject", msg.Subject);
                cmd.Parameters.AddWithValue("@environment", msg.Environment);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                int result = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                conn.Close();
                Console.WriteLine(ex.Message + " at " + ex.StackTrace);
                throw;
            }
        }

        public bool GetMessageId(string msgid, string projectCode)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select count(*) from MessagereadTime where MessageId=@msgid and projectcode=@projectcode", conn);
                cmd.Parameters.AddWithValue("@msgid", msgid);
                cmd.Parameters.AddWithValue("@projectcode", projectCode);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                byte result = Convert.ToByte(cmd.ExecuteScalar());
                bool msgstatus = result == 1 ? true : false;
                conn.Close();
                return msgstatus;
            }
            catch (Exception ex)
            {
                conn.Close();
                Console.WriteLine(ex.Message + " at " + ex.StackTrace);
                throw;
            }
        }

       public List<string> GetCCList(string projectCode)
        {
            string UserData_details = string.Empty;
            List<string> cclist = new List<string>();
            DataTable ccUserdata = new DataTable();
            FileDownloadingDBOperations dbOperations = new FileDownloadingDBOperations();
            try
            {
                UserData_details = @"select * from tblccaddresslist where projectCode='" + projectCode + "' and active=1";
                ccUserdata = dbOperations.getdatatable(UserData_details);
                if (ccUserdata.Rows.Count > 0)
                {
                    foreach (DataRow data in ccUserdata.Rows)
                    {
                        if (!cclist.Any(x => x.Contains(data["ccaddress"].ToString())))
                        {
                            cclist.Add(data["ccaddress"].ToString());
                        }
                    }
                }
                return cclist;
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message + " at " + ex.StackTrace);
                return cclist;
                throw;
            }
        }

       public DataTable GetCredentials(string projectCode, string environment)
        {
            string UserData_details = string.Empty;
            DataTable Userdata = new DataTable();
            FileDownloadingDBOperations dbOperations = new FileDownloadingDBOperations();
            try
            {
                UserData_details = @"SELECT * from project_linked_ftp where projectCode='" + projectCode + "' and environement='" + environment + "' and active=1";
                Userdata = dbOperations.getdatatable(UserData_details);
                return Userdata;
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message + " at " + ex.StackTrace);
                return Userdata;
                throw;
            }

        }
        public DataTable GetToAddress(string projectCode)
        {
            //conn.Open();
            //SqlCommand cmd = new SqlCommand("select *  from ToAddress where ProjectCode=@projectid", conn);
            //cmd.Parameters.AddWithValue("@projectid", projectCode);
            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();
            //da.Fill(ds);
            //conn.Close();
            //return ds.Tables[0];
            //postgrescon.Open();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand("select fs.*,um.emailid from ftp_settings fs inner join tblprojectdefinition pd on pd.projectdefinitionid = fs.projectdefid inner join tblusermaster um on um.userid = pd.projectmanager where pd.project_subtitle= @projectid", postgrescon);
                cmd.Parameters.AddWithValue("@projectid", projectCode);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                Console.WriteLine(ex.Message + " at " + ex.StackTrace);
                throw;
            }
        }

        //public DataTable GetServerPath(string projectCode, string environment)
        //{
        //    try
        //    {
        //        conn.Open();
        //        SqlCommand cmd = new SqlCommand("select * from serverpath where ProjectCode=@projectid and Environment=@environment", conn);
        //        cmd.Parameters.AddWithValue("@projectid", projectCode);
        //        cmd.Parameters.AddWithValue("@environment", environment);
        //        SqlDataAdapter da = new SqlDataAdapter(cmd);
        //        DataSet ds = new DataSet();
        //        da.Fill(ds);
        //        conn.Close();
        //        return ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        conn.Close();
        //        Console.WriteLine(ex.Message + " at " + ex.StackTrace);
        //        throw;
        //    }
        //}
        public DataTable getdatatable(string Qry, List<NpgsqlParameter> paramet = null)
        {
            // NpgsqlConnection postgrescon = new NpgsqlConnection(ConfigurationManager.AppSettings[eGlob.environment].ToString());
            NpgsqlConnection postgrescon = new NpgsqlConnection(ConfigurationManager.AppSettings["npgconn"].ToString());
            DataTable dtable = new DataTable();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(Qry, postgrescon);
                if (paramet != null)
                {
                    foreach (NpgsqlParameter sqlpara in paramet)
                        cmd.Parameters.Add(sqlpara);
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    cmd.CommandType = CommandType.Text;
                }
                NpgsqlDataAdapter adp = new NpgsqlDataAdapter(cmd);
                adp.Fill(dtable);
            }
            catch (Exception ex) { string a = ex.Message; }
            return dtable;
        }
        public static string getscallervalue(string Qry, List<NpgsqlParameter> paramet = null)
        {
            NpgsqlConnection postgrescon = new NpgsqlConnection(ConfigurationManager.AppSettings["npgconn"].ToString());
            //DataTable dtable = new DataTable();
            try
            {
                postgrescon.Open();
                NpgsqlCommand cmd = new NpgsqlCommand(Qry, postgrescon);
                if (paramet != null)
                {
                    foreach (NpgsqlParameter sqlpara in paramet)
                        cmd.Parameters.Add(sqlpara);
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    cmd.CommandType = CommandType.Text;
                }
                string USRole = (string)cmd.ExecuteScalar();
                postgrescon.Close();
                return USRole;
                //NpgsqlDataAdapter adp = new NpgsqlDataAdapter(cmd);
                //adp.Fill(dtable);
            }
            catch (Exception ex)
            {
                string a = ex.Message;
                postgrescon.Close();
            }
            return null;
        }
        public static object insertdataScalar(string qry, List<NpgsqlParameter> parameters = null)
        {
            string connectionString = ConfigurationManager.AppSettings["npgconn"].ToString();

            using (var postgrescon = new NpgsqlConnection(connectionString))
            {
                try
                {
                    postgrescon.Open();
                    using (var cmd = new NpgsqlCommand(qry, postgrescon))
                    {
                        if (parameters != null && parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();

                            foreach (var p in parameters)
                            {
                                // Clone parameter to avoid "already belongs to a collection" issue
                                var clonedParam = new NpgsqlParameter(p.ParameterName, p.Value)
                                {
                                    NpgsqlDbType = p.NpgsqlDbType
                                };
                                cmd.Parameters.Add(clonedParam);
                            }

                            cmd.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            cmd.CommandType = CommandType.Text;
                        }

                        var obj = cmd.ExecuteScalar();
                        return obj;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("❌ Database Error: " + ex.Message);
                    return null;
                }
            }
        }



        public string GetServerPath(string transactionid, int lotid, bool createdir = false)
        {
            string serverpath = string.Empty;
            try
            {
                DataTable ds = getdatatable("usr_server_path_info", GetNpgsqlParameter(transactionid, lotid, "correction"));
                if (createdir)
                {
                    if (ds.Rows.Count > 0)
                    {
                        string username = ds.Rows[0]["username"].ToString();
                        string password = ds.Rows[0]["password"].ToString();
                        string domain = ds.Rows[0]["domain"].ToString();
                        serverpath = FormatPath(ds.Rows[0]["serverpath"].ToString());
                        Impartunate impart = new Impartunate(username, password, domain);
                        if (impart.AllowAccesstoServer())
                        {
                            Directory.CreateDirectory(Path.Combine(serverpath, "IN"));
                            Directory.CreateDirectory(Path.Combine(serverpath, "OUT"));
                        }
                        impart.RemoveServerAccess();
                    }
                }
                else
                {
                    serverpath = FormatPath(ds.Rows[0]["serverpath"].ToString()); // GetPath(query);
                }
            }
            catch //(Exception ex)
            {
            }
            return serverpath;
        }

        public string GetServerPath(string transactionid, bool createdir = false, string serverinfo = "")
        {
            string serverpath = string.Empty;
            try
            {
                DataTable ds = new DataTable();
                if (serverinfo.Equals("serverinfo_cum"))
                    ds = getdatatable("usr_server_path_info", GetNpgsqlParameter(transactionid, serverinfo));
                else
                    ds = getdatatable("usr_server_path_info", GetNpgsqlParameter(transactionid, 0, serverinfo));
                if (createdir)
                {
                    if (ds.Rows.Count > 0)
                    {

                        string username = ds.Rows[0]["username"].ToString();
                        string password = ds.Rows[0]["password"].ToString();
                        string domain = ds.Rows[0]["domain"].ToString();
                        serverpath = FormatPath(ds.Rows[0]["serverpath"].ToString());
                        if (username != "" && password != "")
                        {
                            Impartunate impart = new Impartunate(username, password, domain);
                            if (impart.AllowAccesstoServer())
                            {
                                if (!Directory.Exists(Path.Combine(serverpath, "IN")))
                                    Directory.CreateDirectory(Path.Combine(serverpath, "IN"));
                                if (!Directory.Exists(Path.Combine(serverpath, "OUT")))
                                    Directory.CreateDirectory(Path.Combine(serverpath, "OUT"));
                            }
                            impart.RemoveServerAccess();
                        }
                        else
                        {

                            if (!Directory.Exists(Path.Combine(serverpath, "IN")))
                                Directory.CreateDirectory(Path.Combine(serverpath, "IN"));
                            if (!Directory.Exists(Path.Combine(serverpath, "OUT")))
                                Directory.CreateDirectory(Path.Combine(serverpath, "OUT"));
                        }
                    }
                }
                else
                {
                    serverpath = FormatPath(ds.Rows[0]["serverpath"].ToString()); // GetPath(query);
                }
            }
            catch //(Exception ex)
            {
                return serverpath;
            }
            return serverpath;
        }
        public class modcommon
        {
            public static string ns(object val)
            {
                try
                {
                    return val.ToString();
                }
                catch (Exception ex)
                {
                    string a = ex.Message;
                    return string.Empty;
                }
            }
            public static Int32 nz(object val)
            {
                try
                {
                    if (val != null)
                        return Int32.Parse(val.ToString());
                    else
                        return 0;
                }
                catch (Exception ex)
                {
                    string a = ex.Message;
                    return 0;
                }
            }
        }
        public static int fncreatejob(string projectdefid, string jobtitle, string jobn,string datefolder)
        {
            int jobid = 0;
            string isbnvalue = string.Empty;
            //DateTime currentDate = DateTime.Now;
            //if (jobn == "0")
            //{
            //    jobn = "1";
            //}
            //int number = int.Parse(jobn); // Convert string to int
            //string formattedNumber = number.ToString("D3"); // Format with leading zeros (3 digits)
            //Console.WriteLine("Formatted Number: " + formattedNumber);
            string jobname =  jobn;
            FileDownloadingDBOperations dbOperation = new FileDownloadingDBOperations();
            try
            {
                List<Npgsql.NpgsqlParameter> para = new List<Npgsql.NpgsqlParameter>();
                para.Add(new Npgsql.NpgsqlParameter { ParameterName = "@pid", Value = projectdefid });
                DataTable data = dbOperation.getdatatable(string.Format("select * from tblprojectdefinition where projectdefinitionid={0} and active=1", projectdefid), null);
                if (data.Rows.Count > 0)
                {
                    DataTable dtJobExist = dbOperation.getdatatable(@"select * from tbljobmaster where lower(title) = lower('" + jobtitle.Trim() + "') and projectdefinitionid = " + projectdefid, null);

                    if (dtJobExist.Rows.Count == 0)
                    {
                        isbnvalue = Regex.Match(jobtitle, @"^([^><-]*)-", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline).Groups[1].Value;
                        para = new List<Npgsql.NpgsqlParameter>();
                        para.Add(new Npgsql.NpgsqlParameter { ParameterName = "@pdid", Value = projectdefid });
                        para.Add(new Npgsql.NpgsqlParameter { ParameterName = "@pid", Value = data.Rows[0]["projectid"].ToString() });

                        string sJobName = string.Empty;
                        List<NpgsqlParameter> paramet1 = new List<NpgsqlParameter>();
                        //int cnt = modcommon.nz(db1.insertdataScalar(db, string.Format("select count(*) from job_master where projectdefinitionid={0}  and projectid={1}", projectdefid, data.Rows[0]["projectid"].ToString()), paramet1)) + 1;
                        int cnt = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(string.Format("select count(*) from tbljobmaster where projectdefinitionid={0}  and projectid={1}", projectdefid, data.Rows[0]["projectid"].ToString()))) + 1;
                        sJobName = string.Format("{0}{1}", data.Rows[0]["jobno_prefix"].ToString().Replace("\"", string.Empty), cnt.ToString().PadLeft(Convert.ToInt32(data.Rows[0]["jobno_length"].ToString()), '0'));
                        sJobName = String.Concat(sJobName,"_", jobname);
                        string sQry = "insert_job_details";
                        //string filename = string.Empty;
                        //filename = ",,";
                        List<NpgsqlParameter> paramet = new List<NpgsqlParameter>();
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.Int32, ParameterName = "p_projectdefinitionid", Value = projectdefid });
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.String, ParameterName = "p_jobno", Value = sJobName });
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.String, ParameterName = "p_title", Value = jobtitle });
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.Guid, ParameterName = "p_jobkey", Value = Guid.NewGuid() });
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.Int32, ParameterName = "p_projectid", Value = data.Rows[0]["projectid"].ToString() });
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.String, ParameterName = "p_filename", Value = datefolder });
                        paramet.Add(new NpgsqlParameter() { DbType = DbType.String, ParameterName = "p_sapno", Value = isbnvalue });
                        jobid = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(sQry, paramet));

                    }
                    else
                    {
                        jobid = -1;
                    }
                }
            }
            catch (Exception ex)
            {
                jobid = 0;
                return jobid;
            }
            return jobid;
        }

        public static bool fncreatejobconfiguration(string jbid, string pdid)
        {
            try
            {

                //jbid="3318";
                FileDownloadingDBOperations dbOperation = new FileDownloadingDBOperations();
                List<Npgsql.NpgsqlParameter> para = new List<Npgsql.NpgsqlParameter>();
                List<Npgsql.NpgsqlParameter> para1 = new List<Npgsql.NpgsqlParameter>();
                para.Add(new Npgsql.NpgsqlParameter { ParameterName = "@pid", Value = pdid });
                DataTable proflow = dbOperation.getdatatable(string.Format("select * from tblprojectworkflowdefinition where projectdefinitionid={0} and active=1", pdid), null);
                if (proflow.Rows.Count > 0)
                {
                    proflow.AsEnumerable().ToList<DataRow>().ForEach(item =>
                    {
                        para = new List<Npgsql.NpgsqlParameter>();
                        para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_pwdid", DbType = DbType.Int64, Value = item["pwdid"].ToString() });
                        para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_jobid", DbType = DbType.Int32, Value = jbid });
                        string sQry = @"insert_jobworkflow_details";
                        int jwid = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(sQry, para));
                        DataTable inoutfile = dbOperation.getdatatable(string.Format("select * from tblinoutfiles where projectworkflowid={0} and active=1", item["pwdid"].ToString()), null);
                        if (inoutfile.Rows.Count > 0)
                        {
                            para = new List<Npgsql.NpgsqlParameter>();
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_jobworkflowid", DbType = DbType.Int32, Value = jwid });
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_projectworkflowid", DbType = DbType.Int32, Value = item["pwdid"].ToString() });
                            sQry = "insert_inout_files_details";
                            FileDownloadingDBOperations.insertdataScalar(sQry, para);
                        }
                    });
                    para = new List<Npgsql.NpgsqlParameter>();
                    para.Add(new Npgsql.NpgsqlParameter { ParameterName = "@pid", Value = pdid });
                    proflow = dbOperation.getdatatable(string.Format(@"select jwm.jobid,jwm.jobworkflowid,ns.jobworkflowid as nextstage,ps.jobworkflowid as parallelstage,bs.jobworkflowid as movebackstage, rs.jobworkflowid as resetstage  from tbljobworkflowdefinition jwm 
                     left join tbljobworkflowdefinition ns on jwm.nextstage = ns.stageid and ns.jobid = {0}
                    left join tbljobworkflowdefinition ps on jwm.parallelstage = ps.stageid and ps.jobid = {0}
                    left join tbljobworkflowdefinition bs on jwm.movebackstage = bs.stageid and bs.jobid = {0}
                    left join tbljobworkflowdefinition rs on jwm.resetstage = rs.stageid and rs.jobid = {0}
                    where jwm.jobid = {0} and jwm.active = 1 order by jwm.stageorder ", jbid), null);

                    if (proflow.Rows.Count > 0)
                    {
                        proflow.AsEnumerable().ToList<DataRow>().ForEach(item =>
                        {
                            para = new List<Npgsql.NpgsqlParameter>();
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_jobworkflowid", DbType = DbType.Int64, Value = item["jobworkflowid"].ToString() });
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_nextstage", DbType = DbType.Int64, Value = item["nextstage"].ToString() });
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_parallelstage", DbType = DbType.Int64, Value = item["parallelstage"].ToString() });
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_movebackstage", DbType = DbType.Int64, Value = item["movebackstage"].ToString() });
                            para.Add(new Npgsql.NpgsqlParameter { ParameterName = "p_resetstage", DbType = DbType.Int64, Value = item["resetstage"].ToString() });
                            string sQry = string.Format(@"update tbljobworkflowdefinition set nextstage={0},parallelstage={1},movebackstage={2},resetstage={3} where jobworkflowid={4}", string.IsNullOrEmpty(item["nextstage"].ToString()) ? "null" : item["nextstage"].ToString(), string.IsNullOrEmpty(item["parallelstage"].ToString()) ? "null" : item["parallelstage"].ToString(), string.IsNullOrEmpty(item["movebackstage"].ToString()) ? "null" : item["movebackstage"].ToString(), string.IsNullOrEmpty(item["resetstage"].ToString()) ? "null" : item["resetstage"].ToString(), item["jobworkflowid"].ToString());
                            FileDownloadingDBOperations.insertdataScalar(sQry, null);
                        });
                    }

                }
            }
            catch (Exception ex) { }
            return true;
        }
        public static int fncreatlot(string jobid, string filenamechange, String countno,int pagecount)
        {
            int cnt = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(string.Format("select count(*) from tbllotmaster where jobid={0} and active=1", jobid), null));
            string lotname = String.Concat(Path.GetFileName(filenamechange), "_", countno);
            string sQry = "insert_lot_details";
            List<NpgsqlParameter> paramet = new List<NpgsqlParameter>();
            paramet.Add(new NpgsqlParameter() { DbType = DbType.Int16, ParameterName = "p_jobid", Value = jobid });
            paramet.Add(new NpgsqlParameter() { DbType = DbType.String, ParameterName = "p_title", Value = filenamechange });
            paramet.Add(new NpgsqlParameter() { DbType = DbType.Int16, ParameterName = "p_sequence", Value = cnt });
            paramet.Add(new NpgsqlParameter() { DbType = DbType.Int16, ParameterName = "p_totalunits", Value = pagecount });
            paramet.Add(new NpgsqlParameter() { DbType = DbType.Guid, ParameterName = "p_lotkey", Value = Guid.NewGuid() });
            paramet.Add(new NpgsqlParameter() { DbType = DbType.String, ParameterName = "p_filename", Value = filenamechange });
            paramet.Add(new NpgsqlParameter() { DbType = DbType.DateTime, ParameterName = "p_createddate", Value = DateTime.Now });
            int lotid = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(sQry, paramet));
            return lotid;
        }
        public static int InsertLotTrasaction(long lotid,string option="")
        {
            FileDownloadingDBOperations dbOperation = new FileDownloadingDBOperations();
            int tranid = 0;
            try
            {
                string sqry = string.Format(@"select jwm.jobworkflowid,'R' as lotstatus,jwm.nextstage,1 active,null batchid,lm.createdby,lm.createdon from tbllotbatch lm 
left join tbljobworkflowdefinition jwm on jwm.jobid = lm.jobid and lm.jobid = jwm.jobid  where lm.batchid = {0} and jwm.stageorder = 1", lotid);
                DataTable data = dbOperation.getdatatable(sqry, null);
                if (data.Rows.Count > 0 && option!="nexttraind")
                {
                    string sQry = "insert_lot_transaction_details_batch_only";
                    List<NpgsqlParameter> paramet = new List<NpgsqlParameter>();
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Int64, ParameterName = "p_jobworkflowid", Value = data.Rows[0]["jobworkflowid"].ToString() });
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Int64, ParameterName = "p_lotid", Value = lotid });
             
                    tranid = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(sQry, paramet));
                }

                if (data.Rows.Count > 0 && option=="nexttraind")
                {
                    string sQry = "insert_lot_transaction_details_batch_only";
                    List<NpgsqlParameter> paramet = new List<NpgsqlParameter>();
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Int64, ParameterName = "p_jobworkflowid", Value = data.Rows[0]["nextstage"].ToString() });
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Int64, ParameterName = "p_lotid", Value = lotid });

                    tranid = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(sQry, paramet));
                }




            }
            catch (Exception ex)
            {
                return 0;
            }
            return tranid;
        }

        public static int InsertLotTrasaction1(long lotid)
        {
            FileDownloadingDBOperations dbOperation = new FileDownloadingDBOperations();
            int tranid = 0;
            try
            {
                string sqry = string.Format(@"select jwm.jobworkflowid,lm.lotid,'R' as lotstatus,1 active,null batchid,lm.lotkey,lm.createdby,lm.createdby,lm.createdon from tbllotmaster lm 
left join tbljobworkflowdefinition jwm on jwm.jobid = lm.jobid and lm.jobid = jwm.jobid  where lm.lotid = {0} and jwm.stageorder = 2", lotid);
                DataTable data = dbOperation.getdatatable(sqry, null);
                if (data.Rows.Count > 0)
                {
                    string sQry = "insert_lot_transaction_details";
                    List<NpgsqlParameter> paramet = new List<NpgsqlParameter>();
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Int64, ParameterName = "p_jobworkflowid", Value = data.Rows[0]["jobworkflowid"].ToString() });
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Int64, ParameterName = "p_lotid", Value = lotid });
                    paramet.Add(new NpgsqlParameter() { DbType = DbType.Guid, ParameterName = "p_lotkey", Value = Guid.Parse(data.Rows[0]["lotkey"].ToString()) });
                    tranid = modcommon.nz(FileDownloadingDBOperations.insertdataScalar(sQry, paramet));
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
            return tranid;
        }

        public static int insertdataNonQuery(string Qry, List<NpgsqlParameter> paramet = null)
        {
            int NonEx_Val = -1;
            NpgsqlConnection postgrescon = new NpgsqlConnection(ConfigurationManager.AppSettings["npgconn"].ToString());
            try
            {
                postgrescon.Open();
                NpgsqlCommand cmd = new NpgsqlCommand(Qry, postgrescon);
                if (paramet != null)
                {
                    foreach (NpgsqlParameter sqlpara in paramet)
                        cmd.Parameters.Add(sqlpara);
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    cmd.CommandType = CommandType.Text;
                }
                NonEx_Val = cmd.ExecuteNonQuery();
                postgrescon.Close();
                return NonEx_Val;
            }
            catch (Exception ex)
            {
                string a = ex.Message;
                postgrescon.Close();
                return NonEx_Val;
            }
        }

        public class GrantFolderPermission : IDisposable
        {
            FileDownloadingDBOperations info = new FileDownloadingDBOperations();
            private bool id;
            Impartunate impartunate = null;

            public GrantFolderPermission(string transactionid, string lotid = "")
            {
                if (transactionid == "")
                {
                    string username = System.Configuration.ConfigurationSettings.AppSettings["username"].ToString();
                    string password = System.Configuration.ConfigurationSettings.AppSettings["password"].ToString();
                    string domain = System.Configuration.ConfigurationSettings.AppSettings["domain"].ToString();
                    impartunate = new Impartunate(username, password, domain);
                    impartunate.AllowAccesstoServer();
                }
                else
                {
                    DataTable ds = null;

                    if (transactionid != "")
                        ds = info.getdatatable("usr_server_path_info", info.GetNpgsqlParameter(transactionid, 0, "logininfo"));
                    else if (lotid != "")
                        ds = info.getdatatable("usr_server_path_info", info.GetNpgsqlParameter("0", Convert.ToInt32(lotid), "loginlotinfo"));

                    /*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.Rows.Count > 0)
                    {
                        string username = ds.Rows[0]["username"].ToString();
                        string password = ds.Rows[0]["password"].ToString();
                        string domain = ds.Rows[0]["domain"].ToString();
                        impartunate = new Impartunate(username, password, domain);
                        impartunate.AllowAccesstoServer();
                    }
                }
                //}
            }
            public GrantFolderPermission(string jobid)
            {
                try
                {
                    // DataSet ds = null;
                    DataSet ds = new DataSet();

                    FileDownloadingDBOperations serinfo = new FileDownloadingDBOperations();
                    ds.Tables.Add(serinfo.getdatatable("usr_server_path_info", serinfo.GetNpgsqlParameter(jobid, 0, "loginjobinfo")));
                    if (ds.Tables.Count > 0)
                    {
                        string username = ds.Tables[0].Rows[0]["username"].ToString();
                        string password = ds.Tables[0].Rows[0]["password"].ToString();
                        string domain = ds.Tables[0].Rows[0]["domain"].ToString();
                        impartunate = new Impartunate(username, password, domain);
                        impartunate.AllowAccesstoServer();
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                }
            }
            public void Dispose()
            {
                if (id == true)
                    return;
                impartunate.RemoveServerAccess();
                impartunate = null;
                id = true;
                GC.SuppressFinalize(this);
            }
        }
        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()
            {
                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;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex) { return false; }
            }

            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())
                {
                    RemoveServerAccess();
                }
            }

            #endregion
        }
        private List<NpgsqlParameter> GetNpgsqlParameter(string transid, string process)
        {
            List<NpgsqlParameter> parametr = new List<NpgsqlParameter>();
            parametr.Add(new NpgsqlParameter { ParameterName = "p_tranid", NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer, Value = transid });
            parametr.Add(new NpgsqlParameter { ParameterName = "p_option", NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar, Value = process });
            return parametr;
        }
        private List<NpgsqlParameter> GetNpgsqlParameter(string transid, int lotid, string process)
        {
            List<NpgsqlParameter> parametr = new List<NpgsqlParameter>();
            parametr.Add(new NpgsqlParameter { ParameterName = "p_tranid", NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer, Value = transid });
            parametr.Add(new NpgsqlParameter { ParameterName = "p_lotid", NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer, Value = lotid });
            parametr.Add(new NpgsqlParameter { ParameterName = "p_option", NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar, Value = process });
            return parametr;
        }
        public 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)
            {
            }
            return string.Concat("\\\\", inputpath);
        }
    }
}