public static string demandeApresDate(string afterdate, int toindex) { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = "SELECT * FROM db.demande WHERE datedemande > :afterdate order by datedemande OFFSET :toindex ROWS FETCH NEXT 3 ROWS ONLY"; //Create Command OracleCommand cmd = new OracleCommand(command, co.GetConnection()); cmd.Parameters.Add("date", afterdate); cmd.Parameters.Add("page", toindex); OracleDataReader reader = cmd.ExecuteReader(); /* * while (reader.Read()) * { * Console.WriteLine(reader.GetValue(0)); * }*/ string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string employeavecmoinsdentournee(int nbTournee) { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @"select t1.noemploye ,t1.nom, t1.prenom, t1.datenaiss, t1.dateembauche, t1.salaire, t1.nofonction , (SELECT COUNT(T2.notournee) from db.tournee T2 where T2.noemploye = t1.noemploye) as nbResultat FROM db.employe T1 WHERE (SELECT COUNT(T2.notournee) from db.tournee T2 where T2.noemploye = t1.noemploye) < :notournee ORDER BY (SELECT COUNT(T2.notournee) from db.tournee T2 where T2.noemploye = t1.noemploye) desc"; //Create Command OracleCommand cmd = new OracleCommand(command, co.GetConnection()); cmd.Parameters.Add("nbtournee", nbTournee); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string EntrepriseAvecPlusDeDemandeQue(string raisonsociale) { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @"select * from db.Entreprise t1 where ( Select COUNT(nodemande) from db.demande where siret = t1.siret ) > ( Select COUNT(nodemande) from db.demande join db.entreprise on entreprise.siret = demande.siret where entreprise.raisonsociale = :raisonsociale ) "; OracleCommand cmd = new OracleCommand(command, co.GetConnection()); cmd.Parameters.Add("raisonsociale", raisonsociale); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string QuantiteDechetPourUnCentrePourUnePeriode(string datedebut, string datefin, int centre, int notypedechet) { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @" SELECT SUM(t1.quantitedeposee) AS Quantite FROM db.detaildepot t1 JOIN db.centretraitement t2 on t1.nocentre = t2.nocentre join db.tournee t3 on t3.notournee = t1.notournee join db.demande t4 on t4.notournee = t3.notournee WHERE t4.dateenlevement > :datedebut and t4.dateenlevement< :datefin and t1.notypedechet = :notypedechet and t1.nocentre = :nocentre"; OracleCommand cmd = new OracleCommand(command, co.GetConnection()); cmd.Parameters.Add("datedebut", datedebut); cmd.Parameters.Add("datefin", datefin); cmd.Parameters.Add("notypedechet", notypedechet); cmd.Parameters.Add("nocentre", centre); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string QuantiteDechetPourMoisAnnee(string datedebut, string datefin) { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @"SELECT t3.nomtypedechet , SUM(t1.quantiteenlevee) FROM db.detaildemande t1 join db.demande t2 on t1.nodemande = t2.nodemande join db.typedechet t3 on t1.notypedechet = t3.notypedechet WHERE t2.dateenlevement > :datedebut and t2.dateenlevement < :datefin group by t3.nomtypedechet"; OracleCommand cmd = new OracleCommand(command, co.GetConnection()); cmd.Parameters.Add("datedebut", datedebut); cmd.Parameters.Add("datefin", datefin); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string NombreDechetPourUneDemande(int idDemande) { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @"select t4.raisonsociale, t3.notournee, t2.nomtypedechet, T1.quantiteenlevee from db.detaildemande T1 join db.typedechet T2 on T1.notypedechet = T2.notypedechet join db.demande T3 on t3.nodemande = t1.nodemande join db.entreprise T4 on t4.siret = t3.siret WHERE T1.nodemande = :nodemande"; OracleCommand cmd = new OracleCommand(command, co.GetConnection()); cmd.Parameters.Add("nodemande", idDemande); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string DemandePasDansTournee() { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @"select * from db.demande where notournee is null"; OracleCommand cmd = new OracleCommand(command, co.GetConnection()); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }
public static string CentreTraitement() { ConnectionDatabase co = new ConnectionDatabase(); if (co.OpenConnection() == true) { string command = @"select * from db.centretraitement"; OracleCommand cmd = new OracleCommand(command, co.GetConnection()); OracleDataReader reader = cmd.ExecuteReader(); string json = RequestToJson.ToJson(reader); reader.Dispose(); cmd.Dispose(); co.CloseConnection(); return(json); } return("[]"); }