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); }