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("[]");
}