public static Curierman GetCurierByID(int id)
{
Curierman newcurier = null;
using (SqlConnection connection = new SqlConnection(SqlConnectionDB.ConnectionString))
{
string oString = "Select * from Curier where id=@id";
SqlCommand oCmd = new SqlCommand(oString, connection);
oCmd.Parameters.AddWithValue("@id", id);
connection.Open();
using (SqlDataReader oReader = oCmd.ExecuteReader())
{
if (oReader != null)
{
while (oReader.Read())
{
newcurier = new Curierman();
newcurier.ID = id;
newcurier.Nume = oReader["nume"].ToString();
newcurier.Prenume = oReader["prenume"].ToString();
newcurier.Data_Nasterii = oReader["data_nasterii"].ToString();
newcurier.Salariu = oReader["salariu"].ToString();
}
}
connection.Close();
}
}
return(newcurier);
}
public static Curierman GetAvailableCurier()
{
Curierman newcur = new Curierman();
using (SqlConnection connection = new SqlConnection(SqlConnectionDB.ConnectionString))
{
string oString = @"
SELECT x.curierid,COUNT(x.livrareid) as livactive FROM (
SELECT Curier.id AS curierid ,Livrareclient.id as livrareid
FROM Curier
LEFT JOIN Livrareclient ON Curier.id = Livrareclient.curierid
WHERE Livrareclient.status <> 4 OR Livrareclient.status is null
UNION ALL
SELECT Curier.id AS curierid ,Livraredepozit.id as livrareid
FROM Curier
LEFT JOIN Livraredepozit ON Curier.id = Livraredepozit.curierid
WHERE Livraredepozit.status <> 2 OR Livraredepozit.status is null) as x
GROUP BY curierid
ORDER BY livactive
";
SqlCommand oCmd = new SqlCommand(oString, connection);
connection.Open();
using (SqlDataReader oReader = oCmd.ExecuteReader())
{
if (oReader != null)
{
if (oReader.Read())
{
newcur.ID = Int32.Parse(oReader["curierid"].ToString());
}
}
connection.Close();
}
}
return(newcur);
}
public static Curierman GetBestCurier()
{
Curierman cur = new Curierman();
using (SqlConnection connection = new SqlConnection(SqlConnectionDB.ConnectionString))
{
string oString = @"SELECT curierid,nume,prenume,data_nasterii,salariu,nr
FROM
(SELECT curierid,COUNT(Livrareclient.id) as nr
FROM Livrareclient
WHERE Livrareclient.status = 4
GROUP BY curierid) as x
INNER JOIN Curier ON x.curierid = Curier.id
ORDER BY nr DESC";
SqlCommand oCmd = new SqlCommand(oString, connection);
connection.Open();
using (SqlDataReader oReader = oCmd.ExecuteReader())
{
if (oReader != null)
{
if (oReader.Read())
{
cur.ID = Int32.Parse(oReader["curierid"].ToString());
cur.Nume = oReader["nume"].ToString();
cur.Prenume = oReader["prenume"].ToString();
cur.Data_Nasterii = oReader["data_nasterii"].ToString();
cur.Salariu = oReader["salariu"].ToString();
}
}
connection.Close();
}
}
return(cur);
}
