예제 #1
0
        public static LogsPal SelectIndividualRow(string identifier)
        {
            using (SqlConnection sqlConn = new SqlConnection(ConnectionStringArchives))
            {
                string cmdString = "SELECT *" +
                                   " FROM [dbo].[logsArchivePAL]" +
                                   " WHERE [ID] = @val1";
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection  = sqlConn;
                    cmd.CommandText = cmdString;
                    cmd.Parameters.AddWithValue("@val1", identifier);

                    sqlConn.Open();

                    var dr = cmd.ExecuteReader();

                    LogsPal individualRow = null;

                    while (dr.Read())
                    {
                        individualRow = new LogsPal
                        {
                            ID              = (DataSql.GetLastItemArchive() + 1)
                            , Date          = Convert.ToDateTime(dr["date"])
                            , IssuerID      = dr["userID"].ToString()
                            , Action        = Convert.ToInt32(dr["action"])
                            , ArchiveID     = dr["ID"].ToString()
                            , IssuerDir     = dr["dir"].ToString()
                            , IssuerEts     = dr["ets"].ToString()
                            , IssuerService = dr["service"].ToString()
                            , Contenu       = dr["contenu"].ToString()
                            , DateMin       = Convert.ToInt32(dr["date_min"])
                            , DateMax       = Convert.ToInt32(dr["date_max"])
                            , Elimination   = dr["prevision_elim"].ToString()
                            , Communication = "NOT_USED_SINCE_2020"
                            , Observation   = dr["observations"].ToString()
                            , Cote          = dr["ID"].ToString()
                            , Localization  = dr["localization"].ToString()
                            , Origin        = ""
                            , Status        = dr["status"].ToString()
                            , RequestGroup  = dr["request_group"].ToString()
                        };
                    }
                    return(individualRow);
                }
            }
        }
예제 #2
0
        public static bool AddArchive(string identifier)
        {
            LogsPal individualRow = LogsPal.SelectIndividualRow(identifier);

            using (SqlConnection sqlConn = new SqlConnection(_connectionString))
            {
                string cmdString = "INSERT INTO [dbo].[ArchivesV2]"
                                   + " ([ID] ,[versement] ,[etablissement] ,[direction] ,[service] ,[dossiers] ,[extremes] ,[elimination] ,[communication] ,[cote] ,[localisation])"
                                   + " VALUES (@val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10, @val11)";
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection  = sqlConn;
                    cmd.CommandText = cmdString;
                    // Gets the last ID from ArchiveV2 database, and add ONE to increment gradually.
                    cmd.Parameters.AddWithValue("@val1", GetLastItemArchive() + 1);
                    cmd.Parameters.AddWithValue("@val2", individualRow.Date);
                    cmd.Parameters.AddWithValue("@val3", individualRow.IssuerEts);
                    cmd.Parameters.AddWithValue("@val4", individualRow.IssuerDir);
                    cmd.Parameters.AddWithValue("@val5", individualRow.IssuerService);
                    cmd.Parameters.AddWithValue("@val6", individualRow.Contenu);
                    cmd.Parameters.AddWithValue("@val7", individualRow.DateMin + "-" + individualRow.DateMax);
                    cmd.Parameters.AddWithValue("@val8", individualRow.Elimination);
                    cmd.Parameters.AddWithValue("@val9", individualRow.Communication);
                    cmd.Parameters.AddWithValue("@val10", individualRow.Cote);
                    cmd.Parameters.AddWithValue("@val11", individualRow.Localization);

                    sqlConn.Open();

                    cmd.ExecuteNonQuery();
                }



                cmdString = "UPDATE [Archives_Logs].[dbo].[logsArchivePAL]"
                            + " SET flg_treated = 1"
                            + " WHERE ID = @val1";
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection  = sqlConn;
                    cmd.CommandText = cmdString;
                    cmd.Parameters.AddWithValue("@val1", identifier);

                    cmd.ExecuteNonQuery();
                }
            }
            return(true);
        }
예제 #3
0
        public static LogsPal GetIndividualArchive(string identifier, string requestGroup = "holder")
        {
            // Connect to the Database
            using (SqlConnection sqlConn = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sqlConn;
                    /*cmd.CommandText = "SELECT * FROM [dbo].[ArchivesV2] WHERE cote = @val1";*/
                    cmd.CommandText = "GetIndividualArchive_ArchiveV2";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@val1", identifier);

                    sqlConn.Open();

                    var dr = cmd.ExecuteReader();

                    LogsPal logsPal = new LogsPal();
                    while (dr.Read())
                    {
                        logsPal = new LogsPal
                        {
                            Cote          = dr["cote"].ToString(),
                            IssuerEts     = dr["etablissement"].ToString(),
                            IssuerDir     = dr["direction"].ToString(),
                            IssuerService = dr["service"].ToString(),
                            Contenu       = dr["dossiers"].ToString(),
                            DateMin       = Convert.ToInt32(dr["extremes"].ToString().Substring(0, dr["extremes"].ToString().IndexOf("-"))),
                            DateMax       = Convert.ToInt32(dr["extremes"].ToString().Substring(dr["extremes"].ToString().IndexOf("-") + 1)),
                            Elimination   = dr["elimination"].ToString(),
                            Localization  = dr["localisation"].ToString(),
                            RequestGroup  = requestGroup
                        };
                    }
                    return(logsPal);
                }
            }
        }
예제 #4
0
        public static bool RequestArchive(LogsPal logsPal)
        {
            string cmdString = "INSERT INTO [dbo].[logsArchivePal] ([ID], [date], [user], [userID], [ets], [dir], [service], [contenu], [date_min], [date_max], [observations], [prevision_elim], [request_group], [localization], [action], [status], [flg_treated], [flg_new]) VALUES (@val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10, @val11, @val12, @val13, @val14, @val15, @val16, @val17, @val18)";

            using (SqlConnection sqlConn = new SqlConnection(ConnectionStringArchives))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    int number;
                    cmd.Connection  = sqlConn;
                    cmd.CommandText = cmdString;
                    cmd.Parameters.AddWithValue("@val1", logsPal.Cote);
                    cmd.Parameters.AddWithValue("@val2", DateTime.Now);
                    cmd.Parameters.AddWithValue("@val3", Default.SessionUser.NomAffiche);
                    cmd.Parameters.AddWithValue("@val4", Default.SessionUser.Id);
                    cmd.Parameters.AddWithValue("@val5", logsPal.IssuerEts);
                    cmd.Parameters.AddWithValue("@val6", logsPal.IssuerDir);
                    cmd.Parameters.AddWithValue("@val7", logsPal.IssuerService);
                    cmd.Parameters.AddWithValue("@val8", logsPal.Contenu);
                    cmd.Parameters.AddWithValue("@val9", logsPal.DateMin);
                    cmd.Parameters.AddWithValue("@val10", logsPal.DateMax);
                    cmd.Parameters.AddWithValue("@val11", "");
                    cmd.Parameters.AddWithValue("@val12", Int32.TryParse(logsPal.Elimination, out number) ? Convert.ToInt32(logsPal.Elimination) : 0);
                    cmd.Parameters.AddWithValue("@val13", logsPal.RequestGroup);
                    cmd.Parameters.AddWithValue("@val14", logsPal.Localization);
                    cmd.Parameters.AddWithValue("@val15", 2);
                    cmd.Parameters.AddWithValue("@val16", 1);
                    cmd.Parameters.AddWithValue("@val17", 0);
                    cmd.Parameters.AddWithValue("@val18", 1);

                    sqlConn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return(true);
        }