/// <summary> /// Retourne la liste des commandes /// </summary> /// <returns></returns> public Collection <Commande> GetAll(CommandeFiltre filtre) { Collection <Commande> list = new Collection <Commande>(); using (SqlConnection cnx = new SqlConnection(cnxString)) { cnx.Open(); using (SqlCommand cmd = cnx.CreateCommand()) { // Date livraison cmd.CommandText = $@"SELECT DE.DO_Piece, DE.DO_Date, DE.DO_Type, DE.DO_Statut, DE.[Date Statut], DE.DO_TotalHT, DE.DO_Ref, DE.DO_Reliquat, CT.CT_Num, CT.CT_Intitule, CONCAT(CO.CO_Prenom, ' ', CO.CO_Nom) AS Collaborateur, CASE WHEN YEAR(DE.DO_DateLivr) < 2000 THEN NULL ELSE DE.DO_DateLivr END AS DO_DateLivr, (SELECT COUNT(DISTINCT DL.NumPiece) FROM F_DOCLIGNE DL JOIN F_CMLIEN CM ON CM.DL_NoIn = DL.DL_No WHERE DL.DO_Piece = DE.DO_Piece) AS TotalCM, CASE WHEN ISNULL(DE.RETRO_FOURN, '') = '' THEN NULL ELSE (SELECT CT_Intitule FROM F_COMPTET WHERE CT_Num = DE.DO_Tiers) END AS MagFourn, (SELECT CONCAT(CO_Prenom, ' ', CO_Nom) FROM F_COLLABORATEUR WHERE CO_No = CT.CO_No) AS AcheteurPrinc , DE.RETRO_FOURN, DE.DO_Coord01 FROM F_DOCENTETE DE LEFT JOIN F_COLLABORATEUR CO ON CO.CO_No = DE.CO_No JOIN F_COMPTET CT ON CT.CT_Num = CASE WHEN ISNULL(DE.RETRO_FOURN, '') <> '' THEN DE.RETRO_FOURN ELSE DE.DO_Tiers END WHERE DE.DO_Domaine = 1 {filtre.ToString()} ORDER BY CT.CT_Intitule, DE.DO_Piece"; if (filtre.DateLivFrom != null) { cmd.Parameters.Add("@DateLivFrom", SqlDbType.Date).Value = filtre.DateLivFrom; } if (filtre.DateLivTo != null) { cmd.Parameters.Add("@DateLivTo", SqlDbType.Date).Value = filtre.DateLivTo; } if (filtre.DateDocFrom != null) { cmd.Parameters.Add("@DateDocFrom", SqlDbType.Date).Value = filtre.DateDocFrom; } using (SqlDataReader reader = cmd.ExecuteReader()) { if (!reader.HasRows) { return(list); } while (reader.Read()) { list.Add(new Commande() { Piece = (string)reader["DO_Piece"], Type = (CommandeType)int.Parse(reader["DO_Type"].ToString()), Statut = (CommandeStatut)int.Parse(reader["DO_Statut"].ToString()), DateStatut = reader["Date Statut"] == DBNull.Value ? (DateTime?)null:(DateTime)reader["Date Statut"], Fournisseur = (string)reader["CT_Intitule"], AcheteurPrinc = (reader["AcheteurPrinc"] == DBNull.Value) ? "" : (string)reader["AcheteurPrinc"], MagFourn = (reader["MagFourn"] == DBNull.Value) ? "": (string)reader["MagFourn"], TotalHT = Convert.ToDouble(reader["DO_TotalHT"]), CtNum = (string)reader["CT_Num"], TotalCm = (int)reader["TotalCM"], DateLivraison = reader["DO_DateLivr"] == DBNull.Value ? (DateTime?)null:(DateTime)reader["DO_DateLivr"], Date = (DateTime)reader["Do_Date"], DoRef = (string)reader["DO_Ref"], Collaborateur = (string)reader["Collaborateur"], Relicat = int.Parse(reader["DO_Reliquat"].ToString()) == 1, Entete1 = (string)reader["DO_Coord01"] }); } } } } return(list); }
/// <summary> /// Retourne la liste des commandes avec contremarque /// </summary> /// <returns></returns> public Collection <Commande> GetAllCm(CommandeFiltre filtre) { var list = new Collection <Commande>(); using (var cnx = new SqlConnection(cnxString)) { cnx.Open(); using (var cmd = cnx.CreateCommand()) { cmd.CommandText = $@"SELECT DEA.DO_Piece AS APiece, DEA.DO_TotalHT AS ATotal, DEA.DO_Type AS AType, DEA.DO_Statut AS AStatus, DEA.[Date Statut] AS ADate, FOURN.CT_Intitule AS Fournisseur, CONCAT(COA.CO_Prenom, ' ', COA.CO_Nom) AS Acheteur, CASE WHEN YEAR(DEA.DO_DateLivr) = 1900 THEN NULL ELSE DEA.DO_DateLivr END AS DO_DateLivr, DEA.DO_Reliquat, DLV.DO_Piece AS VPiece, DLV.DL_MontantHT AS VTotal, DEV.[Date Statut] AS VDate, CLI.CT_Intitule AS Client, DLV.AR_Ref, DLA.AF_RefFourniss, DLV.DL_Design, CM.CM_Qte AS CMQte, DLV.DL_Qte AS DLQte, CONCAT(COV.CO_Prenom, ' ', COV.CO_Nom) AS Vendeur FROM F_CMLIEN CM JOIN F_DOCLIGNE DLV ON DLV.DL_No = CM.DL_NoOut JOIN F_DOCLIGNE DLA ON DLA.DL_No = CM.DL_NoIn JOIN F_DOCENTETE DEA ON DEA.DO_Piece = DLA.DO_Piece JOIN F_DOCENTETE DEV ON DEV.DO_Piece = DLV.DO_Piece JOIN F_COMPTET CLI ON CLI.CT_Num = DLV.CT_Num JOIN F_COMPTET FOURN ON FOURN.CT_Num = DLA.CT_Num LEFT JOIN F_COLLABORATEUR COA ON COA.CO_No = DLA.CO_No LEFT JOIN F_COLLABORATEUR COV ON COV.CO_No = DLV.CO_No WHERE DEV.DO_Type IN(1,2) {filtre.ToString().Replace("DE.", "DEA.")} ORDER BY Fournisseur, AType, APiece"; if (filtre.DateLivFrom != null) { cmd.Parameters.Add("@DateLivFrom", SqlDbType.Date).Value = filtre.DateLivFrom; } if (filtre.DateLivTo != null) { cmd.Parameters.Add("@DateLivTo", SqlDbType.Date).Value = filtre.DateLivTo; } if (filtre.DateDocFrom != null) { cmd.Parameters.Add("@DateDocFrom", SqlDbType.Date).Value = filtre.DateDocFrom; } using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { var dict = new Dictionary <string, Commande>(); while (reader.Read()) { string piece = reader["APiece"].ToString(); if (!dict.ContainsKey(piece)) { dict.Add(piece, new Commande() { Piece = piece, TotalHT = Convert.ToDouble(reader["ATotal"]), Type = (CommandeType)int.Parse(reader["AType"].ToString()), Statut = (CommandeStatut)int.Parse(reader["AStatus"].ToString()), DateStatut = reader["ADate"] == DBNull.Value ? (DateTime?)null : (DateTime)reader["ADate"], Fournisseur = (string)reader["Fournisseur"], Lignes = new Collection <LigneCommande>(), Relicat = int.Parse(reader["DO_Reliquat"].ToString()) == 1 }); } dict[piece].Lignes.Add(new LigneCommande() { Acheteur = (string)reader["Acheteur"], Piece = (string)reader["VPiece"], DateStatut = reader["VDate"] == DBNull.Value ? (DateTime?)null : (DateTime)reader["VDate"], Client = (string)reader["Client"], ArRef = (string)reader["AR_Ref"], RefFourn = (string)reader["AF_RefFourniss"], Designation = (string)reader["DL_Design"], CmQte = Convert.ToDouble(reader["CMQte"]), CliQte = Convert.ToDouble(reader["DLQte"]), Vendeur = (string)reader["Vendeur"], TotalHT = Convert.ToDouble(reader["VTotal"]) }); } foreach (KeyValuePair <string, Commande> commande in dict) { list.Add(commande.Value); } } } } } return(list); }