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);
}
}
}
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);
}
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);
}
}
}
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);
}