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 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 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 void UpdateTransaction(int cdTRST, String detail, String codeErreur, String etat, DateTime date)
//{
// OracleTrans o = OracleTrans.getInstance;
// int transac = o.DebutTransaction();
// try
// {
// bool res = o.ExecuterUpdate("UPDATE trft_transaction SET DETAIL = :2, CODE_ERREUR = :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;
// }
//}
public static int getCdTransactionmax()
{
OracleTrans o = OracleTrans.getInstance;
String re = o.ExecuterSelectScalar("SELECT MAX(cd_trst) FROM trft_transaction", -1).Result.ToString();
int cd_transaction;
if (re == "")
{
cd_transaction = 1;
}
else
{
cd_transaction = Convert.ToInt32(o.ExecuterSelectScalar("SELECT MAX(cd_trst) FROM trft_transaction", -1).Result) + 1;
}
return(cd_transaction);
}
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);
}
/// <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);
}
}
/// <summary>
/// Nombre de licence pour un logiciel
/// </summary>
/// <param name="codeLogiciel"></param>
private static int NbLicCleLogiciel(int codeLogiciel, int transac)
{
OracleTrans o = OracleTrans.getInstance;
ExtendedReturnedScalar res = o.ExecuterSelectScalar(
"SELECT CELL.VALEUR FROM CELL, VERSION WHERE CELL.CD_CELL = VERSION.CD_CELL AND VERSION.CD_LOGICIEL = :1", transac, codeLogiciel);
if (res.Result != null && res.Result != DBNull.Value)
{
String valeur = Crypte((String)res.Result, false);
String codeCellule = valeur.Substring(6, 2);
int nbLicence = int.Parse(valeur.Substring(16, 5));
return(nbLicence);
}
else
{
return(0);
}
}
private static Boolean TestCle(int cdLogiciel, String sessionID, int dureeSecondes, int transac)
{
OracleTrans o = OracleTrans.getInstance;
ExtendedReturnedScalar resultOpeCon = o.ExecuterSelectScalar(
" SELECT COUNT(*) as NB_OPE_CON " +
" FROM LOGICIEL_SESSIONS " +
" WHERE CD_LOGICIEL = :1 " +
" AND (SYSDATE - DATE_MAJ) < (:2 / 60 / 60 / 24) " +
(sessionID != null && sessionID != "" ? " AND TRIM(SESSION_ID) <> '" + sessionID.Trim() + "'" : ""),
transac, cdLogiciel, _DUREE_INACTIVITE);
if (resultOpeCon.Result == null)
{
throw new Exception("Impossible de récupérer le nombre d'utilisateur connectés. " + (resultOpeCon.OracleException != null ? resultOpeCon.OracleException.Message : ""));
}
else
{
// - Comparaison avec la clé
// - nombre de licences sur la clé
// (si cd_logiciel = 10 et cd_ext_log_fct = 7, alors eCynaps pointage, test avec cd_logiciel = 5 (CAB) parceque pas de clé spécifique à eCynaps pointage pour le moment)
int nbLicAuto = NbLicCleLogiciel(cdLogiciel, transac);
// - nombre de licences majoré
int nbLicPlus = Convert.ToInt32(Math.Round(nbLicAuto * 0.10));
int nbLicAutoMaj = nbLicAuto + Convert.ToInt32(Math.Round(nbLicAuto * 0.10));
// - nombre de connectés
int nbOpeCon = Convert.ToInt32(resultOpeCon.Result);
// - comparaison nombre de connecté + tentative courante / nombre de licences sur clé + 10%
////////////////
// TEST
//nbLicAutoMaj = 2;
////////////////
/// TODO: Ajouter contrôle sur licences clé
// ERREUR LICENCE
//if (nbOpeCon >= nbLicAutoMaj)
// throw new Exception(String.Format("Nombre de licences autorisé atteint ({0}/({1} + {2})). ", nbOpeCon, nbLicAuto, nbLicPlus));
//else
// return true;
return(true);
}
}
public static String getEtat(int cdTRST)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT ETAT FROM trft_transaction WHERE cd_trst = :1", -1, cdTRST).Result.ToString());
}
public static String getMaxTransactTRFT(int cdTRFT)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT MAX(cd_trst) FROM trft_transaction WHERE cd_trft = :1", -1, cdTRFT).Result.ToString());
}
public static String getTailleFichier(int cdTRFT)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT TAILLE_FICHIER FROM trft_transfert WHERE cd_trft = :1", -1, cdTRFT).Result.ToString());
}
public static String getCheminDistant(int cdSrv, int cdFlux)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT CHEMIN_DISTANT FROM trft_serveur_flux WHERE cd_srv = :1 AND cd_flux = :2", -1, cdSrv, cdFlux).Result.ToString());
}
public static String getTrftPort(int cdSrv)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT TRFT_PORT FROM trft_serveurs WHERE cd_srv = :1", -1, cdSrv).Result.ToString());
}
/// <summary>
/// Enregistrement d'une nouvelle session d'utilisation à un logiciel et vérification de la clé associée
/// </summary>
public static Boolean CreerNouvelleSessionCle(int cdLogiciel, string sessionId, string infos)
{
lock (LOCK)
{
if (DateTime.Now.Subtract(_dateDernierePurge).TotalSeconds > _FREQUENCE_PURGE)
{
try
{
_dateDernierePurge = DateTime.Now;
// Purge des sessions existantes qui dépassent le délai d'inactivité
PurgerSessionsCle(cdLogiciel, _DUREE_INACTIVITE);
}
catch (Exception) { }
}
}
OracleTrans o = OracleTrans.getInstance;
int transac = o.DebutTransaction();
Boolean res = true;
try
{
// Test si session existe déjà
Boolean existsSession = false;
if (sessionId != "")
{
ExtendedReturnedScalar resultSessionCourante = o.ExecuterSelectScalar(
" SELECT COUNT(*)" +
" FROM LOGICIEL_SESSIONS " +
" WHERE CD_LOGICIEL = :1 " +
" AND SESSION_ID = :2",
transac, cdLogiciel, sessionId);
if (Convert.ToInt32(resultSessionCourante.Result) > 0)
{
existsSession = true;
Boolean sessionAMAJ = false;
// SI DATE_MAJ session courante > DUREE_INACTIVITE => SESSION NON ACTIVE => test clé
ExtendedReturnedScalar resultSessionCouranteActive = o.ExecuterSelectScalar(
" SELECT CASE WHEN (SYSDATE - DATE_MAJ) < (:2 / 60 / 60 / 24) THEN 1 ELSE 0 END as SESSION_ACTIVE " +
" FROM LOGICIEL_SESSIONS " +
" WHERE CD_LOGICIEL = :1 " +
" AND SESSION_ID = :3",
transac, cdLogiciel, _DUREE_INACTIVITE, sessionId);
// Si session non active -> test clé
if (Convert.ToInt32(resultSessionCouranteActive.Result) == 0)
{
sessionAMAJ = TestCle(cdLogiciel, sessionId, _DUREE_INACTIVITE, transac);
}
else
{
// Sinon mise à jour activité session
sessionAMAJ = true;
}
if (sessionAMAJ)
{
//MajActiviteSessionCle(cdLogiciel, sessionId, transac);
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 : ""));
}
}
}
else
{
existsSession = false;
}
if (!existsSession)
{
res = res && TestCle(cdLogiciel, null, _DUREE_INACTIVITE, transac);
if (res)
{
// - OK, nouvelle session
ExtendedReturnedUpdate resultLogOpe = o.ExecuterUpdate(
" INSERT INTO LOGICIEL_SESSIONS " +
" (CD_LOGICIEL_SESSIONS, CD_LOGICIEL, SESSION_ID, INFOS, DATE_CREAT, DATE_MAJ, DUREE_ACTIVITE) " +
" VALUES (SEQ_LOGICIEL_SESSIONS.NEXTVAL, :1, :2, :3, SYSDATE, SYSDATE, 0)",
transac,
cdLogiciel, sessionId, infos);
res = res && resultLogOpe.ErrCode == 0;
if (!res)
{
throw new Exception("Impossible d'enregistrer la session d'utilisation. " + (resultLogOpe.OracleException != null ? resultLogOpe.OracleException.Message : ""));
}
}
}
if (res)
{
if (_COMMIT)
{
o.Commit(transac);
}
else
{
o.RollBack(transac);
}
return(res);
}
else
{
o.RollBack(transac);
return(false);
}
}
else
{
o.RollBack(transac);
return(false);
}
}
catch (Exception ex)
{
o.RollBack(transac);
throw ex;
}
}
public static String getCodepostal(int cdClient)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT CODE_POSTAL FROM trft_client WHERE cd_client = :1", -1, cdClient).Result.ToString());
}
public static String getFtpMdp(int cdSrv)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT FTP_MDP FROM trft_serveurs WHERE cd_srv = :1", -1, cdSrv).Result.ToString());
}
public static String getDesignation(int cdFlux)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT DESIGNATION FROM trft_flux WHERE cd_flux = :1", -1, cdFlux).Result.ToString());
}
public static String getCdClient(int cdSrv)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT CD_CLIENT_SRV FROM trft_serveurs WHERE cd_srv = :1", -1, cdSrv).Result.ToString());
}
public static String getAdressePostale(int cdClient)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT ADRESSE_POSTALE FROM trft_client WHERE cd_client = :1", -1, cdClient).Result.ToString());
}
public static String getDate(int cdTRFT)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT DATE FROM trft_transfert WHERE cd_trft = :1", -1, cdTRFT).Result.ToString());
}
public static String getPays(int cdClient)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT PAYS FROM trft_client WHERE cd_client = :1", -1, cdClient).Result.ToString());
}
public static int getCdTransfert(int cdFlux, int cdClient, String etat)
{
OracleTrans o = OracleTrans.getInstance;
return(Convert.ToInt32(o.ExecuterSelectScalar("SELECT CD_TRFT FROM trft_transfert WHERE cd_flux= :1 AND cd_Client = :2 AND etat = :3", -1, cdFlux, cdClient, etat).Result.ToString()));
}
public static String getRaisonSocial(int cdClient)
{
OracleTrans o = OracleTrans.getInstance;
return(o.ExecuterSelectScalar("SELECT RAISON_SOCIAL FROM trft_client WHERE cd_client = :1", -1, cdClient).Result.ToString());
}
