public static ServeurFlux getServeurFlux(int cdSrv, int cdFlux)
{
OracleTrans o = OracleTrans.getInstance;
var data = o.ExecuterSelect <ServeurFlux>("SELECT CD_SERVEUR, CD_FLUX, CHEMIN_LOCAL, CHEMIN_DISTANT FROM trft_serveur_flux WHERE cd_srv = :1 AND cd_flux = :2", -1, cdSrv, cdFlux).Data;
return(data.FirstOrDefault());
}
public static void UpdateClient(int cdClient, String raisonSocial, String adressePostale, String codePostal, String ville, String pays)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("UPDATE trft_client SET RAISON_SOCIAL = :2, ADRESSE_POSTALE = :3, CODE_POSTAL = :4, VILLE = :5, PAYS = :6 WHERE CD_CLIENT = :1 ", -1, cdClient, raisonSocial, adressePostale, codePostal, ville, pays).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
//Ajoute un flux au serveur
public static void InsertServeurFlux(int cdFlux, int cdServeur, String cheminLocal, String cheminDistant)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("INSERT INTO trft_serveur_flux (CD_FLUX, CD_SRV, CHEMIN_LOCAL, CHEMIN_DISTANT) VALUES (:1, :2, :3, :4)", transac, cdFlux, cdServeur, cheminLocal, cheminDistant).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static Client getClient(int cdClient)
{
OracleTrans o = OracleTrans.getInstance;
var data = o.ExecuterSelect <Client>("SELECT RAISON_SOCIAL, ADRESSE_POSTALE, CODE_POSTAL, VILLE, PAYS FROM TRFT_CLIENT WHERE cd_client = :1 ", -1, cdClient).Data;
return(data.FirstOrDefault());
}
public static void InsertClient(String raisonSocial, String adressePostale, String codePostal, String ville, String pays)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
int cd_client = getCdClientmax();
bool res = o.ExecuterUpdate("INSERT INTO trft_client (CD_CLIENT, RAISON_SOCIAL, ADRESSE_POSTALE, CODE_POSTAL, VILLE, PAYS) VALUES (:1, :2, :3, :4, :5, :6)", transac, cd_client, raisonSocial, adressePostale, codePostal, ville, pays).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
} catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static void DeleteClientServeur(int cdServeur, int cdClient)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("DELETE FROM trft_client_serveur WHERE cd_srv = :1 AND cd_client = :2", transac, cdServeur, cdClient).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static void UpdateCDSRVServeurFlux(int cdFlux, int cdServeurOld, int cdServeurNew, String cheminLocal, String cheminDistant)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("UPDATE trft_serveur_flux SET CHEMIN_LOCAL = :2, CHEMIN_DISTANT = :3, CD_SRV = :5 WHERE CD_SRV = :1 AND CD_FLUX = :4 ", -1, cdServeurOld, cheminLocal, cheminDistant, cdFlux, cdServeurNew).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static Boolean InsertTransfert(int cdFlux, int cdClient, String designation, String tailleFichier, String etat, String ipSource, DateTime date)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
Boolean res;
try
{
int cd_transfert = getCdTransfertmax();
res = o.ExecuterUpdate("INSERT INTO trft_transfert (CD_TRFT, CD_FLUX, CD_CLIENT, DESIGNATION, TAILLE_FICHIER, ETAT, IP_SOURCE, DATE_TRANSFERT) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)", transac, cd_transfert, cdFlux, cdClient, designation, tailleFichier, etat, ipSource, date).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
res = false;
}
return(res);
}
//Ajoute un client au serveur
public static void InsertClientServeur(int cdClient, int cdServeur)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("INSERT INTO trft_client_serveur (CD_CLIENT, CD_SRV) VALUES (:1, :2)", transac, cdClient, cdServeur).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static Boolean InsertTransaction(int cdTransfert, String detail, String codeErreur, String etat, DateTime date)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
Boolean res;
try
{
int cd_transaction = getCdTransactionmax();
res = o.ExecuterUpdate("INSERT INTO trft_transaction (CD_TRST, CD_TRFT, DETAIL, CODE_ERREUR, ETAT, DATE_TRANSACTION) VALUES (:1, :2, :3, :4, :5, :6)", transac, cd_transaction, cdTransfert, detail, codeErreur, etat, date).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
res = false;
}
return(res);
}
public static Flux getFlux(int cdFlux)
{
OracleTrans o = OracleTrans.getInstance;
var data = o.ExecuterSelect <Flux>("SELECT DESIGNATION FROM trft_flux WHERE cd_flux = :1 ", -1, cdFlux).Data;
return(data.FirstOrDefault());
}
public static void InsertServeur(String adresseIp, String ftpIdtf, String ftpMdp, String ftpPort, String trftPort, int cd_client_srv)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
int cd_serveur = getCdServeurmax();
bool res = o.ExecuterUpdate("INSERT INTO trft_serveurs (CD_SRV, ADRESSE_IP, FTP_IDENTIFIANT, FTP_MDP, FTP_PORT, TRFT_PORT, CD_CLIENT_SRV) VALUES (:1, :2, :3, :4, :5, :6, :7)", transac, cd_serveur, adresseIp, ftpIdtf, ftpMdp, ftpPort, trftPort, cd_client_srv).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static void InsertFlux(String designation)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
int cd_flux = getCdFluxmax();
bool res = o.ExecuterUpdate("INSERT INTO trft_flux (CD_FLUX, DESIGNATION) VALUES (:1, :2)", transac, cd_flux, designation).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static int getNbreFlux()
{
OracleTrans o = OracleTrans.getInstance;
String re = o.ExecuterSelectScalar("SELECT COUNT(cd_flux) FROM trft_flux", -1).Result.ToString();
return(Convert.ToInt32(re));
}
public static void UpdateServeur(int cdServeur, String adresseIp, String ftpIdtf, String ftpMdp, String ftpPort, String trftPort, int cd_client_srv)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("UPDATE trft_serveurs SET ADRESSE_IP = :2, FTP_IDENTIFIANT = :3, FTP_MDP = :4, FTP_PORT = :5, TRFT_PORT = :6, CD_CLIENT_SRV = :7 WHERE CD_SRV = :1 ", -1, cdServeur, adresseIp, ftpIdtf, ftpMdp, ftpPort, trftPort, cd_client_srv).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static void DeleteFlux(int cd_flux)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("DELETE FROM trft_flux WHERE cd_flux = :1", transac, cd_flux).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static int getnbreServeur()
{
OracleTrans o = OracleTrans.getInstance;
String re = o.ExecuterSelectScalar("SELECT COUNT(cd_srv) FROM trft_serveurs", -1).Result.ToString();
return(Convert.ToInt32(re));
}
public static Serveur getServeur(int cdSrv)
{
OracleTrans o = OracleTrans.getInstance;
var data = o.ExecuterSelect <Serveur>("SELECT CD_SRV, CD_CLIENT_SRV, ADRESSE_IP, FTP_IDENTIFIANT, FTP_MDP, FTP_PORT, TRFT_PORT FROM trft_serveurs WHERE cd_srv = :1 ", -1, cdSrv).Data;
return(data.FirstOrDefault());
}
public static void UpdateFlux(int cdFlux, String designation)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("UPDATE trft_flux SET DESIGNATION = :2 WHERE CD_FLUX = :1 ", -1, cdFlux, designation).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static Transfert getTransfert(int cdTRFT)
{
OracleTrans o = OracleTrans.getInstance;
var data = o.ExecuterSelect <Transfert>("SELECT CD_TRFT, CD_FLUX, CD_CLIENT, DESIGNATION, TAILLE_FICHIER, ETAT, IP_SOURCE, DATE FROM trft_transfert WHERE cd_trft = :1 ", -1, cdTRFT).Data;
return(data.FirstOrDefault());
}
public static void UpdateTransfertEtat(int cdTransfert, String etat)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("UPDATE trft_transfert SET ETAT = :2 WHERE CD_TRFT = :1 ", -1, cdTransfert, etat).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static void UpdateClientServeur(int cdClient, int cdServeur)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
try
{
bool res = o.ExecuterUpdate("UPDATE trft_client_serveur SET CD_CLIENT = :2, CD_SRV = :1 WHERE CD_SRV = :1", -1, cdServeur, cdClient).ErrCode == 0;
if (res)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
/// <summary>
/// Purge des logs opérateur > durée
/// </summary>
private static Boolean PurgerSessionsCle(int cdLogiciel, int dureeSecondes, int transac)
{
OracleTrans o = OracleTrans.getInstance;
Boolean res = true;
try
{
// Purge à la durée + une minute
dureeSecondes += 60;
ExtendedReturnedUpdate resultLogOpe = o.ExecuterUpdate(
" DELETE FROM LOGICIEL_SESSIONS WHERE (SYSDATE - DATE_MAJ) > (:1 / 60 / 60 / 24) ", //CD_LOGICIEL = :1 AND
transac,
dureeSecondes);
res = res && resultLogOpe.ErrCode == 0;
if (!res)
{
throw new Exception("Impossible de purger les sessions d'utilisation. " + (resultLogOpe.OracleException != null ? resultLogOpe.OracleException.Message : ""));
}
return(res);
}
catch (Exception ex)
{
throw ex;
}
}
///// <summary>
///// Mise à jour de l'activité de session d'utilisation d'un logiciel
///// </summary>
//public static Boolean MajActiviteSessionCle(int cdLogiciel, String sessionId)
//{
// lock (LOCK)
// {
// if (DateTime.Now.Subtract(_dateDernierePurge).TotalSeconds < 10)
// {
// try
// {
// // Purge des sessions existantes qui dépassent le délai d'inactivité
// PurgerSessionsCle(cdLogiciel, _DUREE_INACTIVITE);
// _dateDernierePurge = DateTime.Now;
// }
// catch (Exception) { }
// }
// }
// OracleTrans o = OracleTrans.getInstance;
// int transac = o.DebutTransaction();
// Boolean res = true;
// // Vérifie la présence en base du log opérateur
// try
// {
// res = res && MajActiviteSessionCle(cdLogiciel, sessionId, transac);
// if (res)
// {
// if (_COMMIT)
// o.Commit(transac);
// else
// o.RollBack(transac);
// }
// else
// o.RollBack(transac);
// return res;
// }
// catch (Exception ex)
// {
// o.RollBack(transac);
// throw ex;
// }
//}
///// <summary>
///// Mise à jour de l'activité de session d'utilisation d'un logiciel
///// </summary>
//private static Boolean MajActiviteSessionCle(int cdLogiciel, String sessionId, int transac)
//{
// OracleTrans o = OracleTrans.getInstance;
// Boolean res = true;
// try
// {
// // Vérifie la présence en base du log opérateur
// try
// {
// res = res &&
// Convert.ToInt32(o.ExecuterSelectScalar(
// "SELECT count(*) FROM LOGICIEL_SESSIONS " +
// " WHERE CD_LOGICIEL = :1 " +
// " AND SESSION_ID = :2 ",
// transac,
// cdLogiciel, sessionId).Result) > 0;
// if (!res)
// return false;
// }
// catch (Exception)
// {
// throw new Exception("Impossible de retrouver la session d'utilisation. ");
// }
// if (res)
// {
// ExtendedReturnedUpdate resultLogOpe = o.ExecuterUpdate(
// " UPDATE LOGICIEL_SESSIONS SET " +
// " DATE_MAJ = SYSDATE, " +
// " DUREE_ACTIVITE = Round((DATE_MAJ - DATE_CREAT) * 24 * 60, 2) " + // EN MINUTES
// " WHERE CD_LOGICIEL = :1 " +
// " AND SESSION_ID = :2 ",
// transac,
// cdLogiciel, sessionId);
// res = res && resultLogOpe.ErrCode == 0;
// if (!res) throw new Exception("Impossible de mettre à jour l'activité de la session d'utilisation. " + (resultLogOpe.OracleException != null ? resultLogOpe.OracleException.Message : ""));
// return res;
// }
// else
// {
// return false;
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
//}
/// <summary>
/// Purge des logs opérateur > durée
/// </summary>
private static Boolean PurgerSessionsCle(int cdLogiciel, int dureeSecondes)
{
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
Boolean res = true;
try
{
res = PurgerSessionsCle(cdLogiciel, dureeSecondes, transac);
if (res)
{
if (_COMMIT)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
}
else
{
o.RollBack(transac);
}
return(res);
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
/// <summary>
/// Vérifie la version du logiciel
/// </summary>
/// <param name="codeLogiciel"></param>
/// <param name="version"></param>
public static Boolean CheckVersion(String nomLogiciel, String version)
{
OracleTrans o = OracleTrans.getInstance;
return
(Convert.ToInt32(o.ExecuterSelectScalar(
"SELECT count(*) FROM VERSION WHERE CD_VERSION = :1 AND N_VERSION = :2", -1, nomLogiciel, version).Result) == 1);
}
/// <summary>
/// Récupère le répertoire ARCHIVE dans la table SOCIETE
/// </summary>
/// <param name="codeLogiciel"></param>
/// <returns></returns>
public static String GetRepArchive(int codeSociete)
{
OracleTrans o = OracleTrans.getInstance;
ExtendedReturnedScalar res = o.ExecuterSelectScalar(
"SELECT BD_NET FROM SOCIETE WHERE CD_SOCIETE = :1", -1, codeSociete);
if (res.Result == null || res.Result == null || res.Result == DBNull.Value)
{
return("");
}
else
{
return((String)res.Result);
}
}
/// <summary>
/// Retourne l'équivalence de chemin réseau (UNC par exemple) associée à une lettre de lecteur réseau
/// </summary>
/// <param name="networkDrive">Lettre du lecteur</param>
public static String NetworkDriveLetterToUNCPath(char networkDrive)
{
OracleTrans o = OracleTrans.getInstance;
ExtendedReturnedScalar res = o.ExecuterSelectScalar(
"SELECT path FROM ext_net_drive WHERE UPPER(drive) = :1 AND NVL(inactif, 0) = 0 ", -1, networkDrive.ToString().ToUpper());
if (o.ErrCode > 0 || res.Result == null || res.Result == DBNull.Value)
{
return(null);
}
else
{
return((String)res.Result);
}
}
/// <summary>
/// Récupère la version associée au logiciel
/// </summary>
/// <param name="codeLogiciel"></param>
/// <returns></returns>
public static String GetVersion(String nomLogiciel)
{
OracleTrans o = OracleTrans.getInstance;
ExtendedReturnedScalar res = o.ExecuterSelectScalar(
"SELECT n_version FROM VERSION WHERE CD_VERSION = :1", -1, nomLogiciel);
if (res.Result == null || res.Result == null || res.Result == DBNull.Value)
{
return("");
}
else
{
return((String)res.Result);
}
}
public static int getCdFluxmax()
{
OracleTrans o = OracleTrans.getInstance;
String re = o.ExecuterSelectScalar("SELECT MAX(cd_flux) FROM trft_flux", -1).Result.ToString();
int cd_flux;
if (re == "")
{
cd_flux = 1;
}
else
{
cd_flux = Convert.ToInt32(o.ExecuterSelectScalar("SELECT MAX(cd_flux) FROM trft_flux", -1).Result) + 1;
}
return(cd_flux);
}
public static int getCdServeurmax()
{
OracleTrans o = OracleTrans.getInstance;
String re = o.ExecuterSelectScalar("SELECT MAX(cd_srv) FROM trft_serveurs", -1).Result.ToString();
int cd_serveur;
if (re == "")
{
cd_serveur = 1;
}
else
{
cd_serveur = Convert.ToInt32(o.ExecuterSelectScalar("SELECT MAX(cd_srv) FROM trft_serveurs", -1).Result) + 1;
}
return(cd_serveur);
}
