public void ChargeDonnées(string[] titreCommencePar)
{
using (new WaitingCursor())
using (IDbCommand cmd = BDDatabase.Database.GetCommand())
{
StringBuilder SQL = new StringBuilder();
cmd.Parameters.Clear();
SQL.Append("SELECT * FROM ALBUMS A INNER JOIN SERIES S ON A.ID_SERIE = S.ID_SERIE");
if (titreCommencePar != null && titreCommencePar.Length > 0)
{
SQL.Append("\nWHERE");
for (int i = 0; i < titreCommencePar.Length; i++)
{
SQL.Append((i == 0 ? "" : "\nOR") + "\nUPPERTITREALBUM LIKE ?");
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@P" + i.ToString(), titreCommencePar[i].ToUpper() + "%"));
}
}
SQL.Append("\nORDER BY A.UPPERTITREALBUM");
cmd.CommandText = SQL.ToString();
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Album> dataReader = new BaseDataReader <Album>(result))
if (result != null)
{
dataReader.FillList(this);
}
}
}
public void ChargeDonnées(string[] titreCommencePar)
{
using (new WaitingCursor())
using (IDbCommand cmd = BDPPCDatabase.GetCommand())
{
StringBuilder SQL = new StringBuilder();
cmd.Parameters.Clear();
SQL.Append("SELECT REFEMPRUNTEUR, NOMEMPRUNTEUR FROM EMPRUNTEURS");
if (titreCommencePar != null && titreCommencePar.Length > 0)
{
SQL.Append("\nWHERE");
for (int i = 0; i < titreCommencePar.Length; i++)
{
SQL.Append((i == 0 ? "" : "\nOR") + "\nUPPERNOMEMPRUNTEUR LIKE ?");
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@P" + i.ToString(), titreCommencePar[i].ToUpper() + "%"));
}
}
SQL.Append("\nORDER BY UPPERNOMEMPRUNTEUR");
cmd.CommandText = SQL.ToString();
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Emprunteur> dataReader = new BaseDataReader <Emprunteur>(result))
if (result != null)
{
dataReader.FillList(this);
}
}
}
public void ChargeDonnées(string[] titreCommencePar)
{
using (new WaitingCursor())
using (IDbCommand cmd = BDDatabase.Database.GetCommand())
{
StringBuilder SQL = new StringBuilder();
cmd.Parameters.Clear();
SQL.Append("SELECT * FROM SERIES S LEFT JOIN EDITEURS E ON S.ID_EDITEUR = E.ID_EDITEUR LEFT JOIN COLLECTIONS C ON S.ID_COLLECTION=C.ID_COLLECTION");
if (titreCommencePar != null && titreCommencePar.Length > 0)
{
SQL.Append("\nWHERE");
for (int i = 0; i < titreCommencePar.Length; i++)
{
SQL.Append((i == 0 ? "" : "\nOR") + "\nUPPERTITRESERIE LIKE ?");
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@P" + i.ToString(), titreCommencePar[i].ToUpper() + "%"));
}
}
SQL.Append("\nORDER BY S.UPPERTITRESERIE");
cmd.CommandText = SQL.ToString();
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Série> dataReader = new BaseDataReader <Série>(result))
if (result != null)
{
dataReader.FillList(this);
}
}
}
public override void Fill(params System.Object[] references)
{
if (references.Length < 1)
{
return;
}
Guid reference = (Guid)references[0];
if (reference.Equals(Guid.Empty))
{
return;
}
using (new WaitingCursor())
using (IDbCommand cmd = BDDatabase.Database.GetCommand())
{
cmd.CommandText = "SELECT ID_EDITION, ID_Album, e.ID_Editeur, e.ID_Collection, NOMCOLLECTION, ANNEEEDITION, PRIX, VO, COULEUR, ISBN, DEDICACE, PRETE,"
+ "STOCK, Offert, Gratuit, NombreDePages, etat, le.libelle as setat, reliure, lr.libelle as sreliure, orientation, lo.libelle as sorientation,"
+ "FormatEdition, lf.libelle as sFormatEdition, typeedition, lte.libelle as stypeedition, DateAchat, Notes, AnneeCote, PrixCote "
+ "FROM EDITIONS e LEFT JOIN COLLECTIONS c ON e.ID_Collection = c.ID_Collection "
+ "LEFT JOIN LISTES le on (le.ref = e.etat and le.categorie = 1) "
+ "LEFT JOIN LISTES lr on (lr.ref = e.reliure and lr.categorie = 2) "
+ "LEFT JOIN LISTES lte on (lte.ref = e.typeedition and lte.categorie = 3) "
+ "LEFT JOIN LISTES lo on (lo.ref = e.orientation and lo.categorie = 4) "
+ "LEFT JOIN LISTES lf on (lf.ref = e.formatedition and lf.categorie = 5) "
+ "WHERE ID_Edition = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Edition", StringUtils.GuidToString(reference)));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <EditionComplet> dataReader = new BaseDataReader <EditionComplet>(result))
if (result != null && result.Read())
{
dataReader.LoadData(this);
Editeur.Fill(dataReader.GetGuid("ID_EDITEUR"));
// Self.Collection.Fill(q);
}
Emprunts.Fill(reference, srcEmprunt.Album);
cmd.CommandText = "SELECT ID_Couverture, FichierCouverture, STOCKAGECOUVERTURE, CategorieImage, l.Libelle as sCategorieImage";
cmd.CommandText += " FROM Couvertures c LEFT JOIN Listes l ON (c.categorieimage = l.ref and l.categorie = 6)";
cmd.CommandText += " WHERE ID_Edition = ? ORDER BY c.categorieimage NULLS FIRST, c.Ordre";
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Edition", StringUtils.GuidToString(reference)));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Couverture> dataReader = new BaseDataReader <Couverture>(result))
if (result != null)
{
dataReader.FillList(fCouvertures);
}
}
}
public override void ProcAuteurs(ListeAuteurs list, Guid ID_Album, Guid ID_Série, Guid ID_ParaBD)
{
using (new WaitingCursor())
using (IDbCommand cmd = BDDatabase.Database.GetCommand())
{
cmd.CommandText = "select id_personne, nompersonne, id_album, id_serie, id_parabd, metier from proc_auteurs(?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Album", StringUtils.GuidToString(ID_Album)));
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Serie", StringUtils.GuidToString(ID_Série)));
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_ParaBD", StringUtils.GuidToString(ID_ParaBD)));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Auteur> dataReader = new BaseDataReader <Auteur>(result))
if (result != null)
{
dataReader.FillList(list);
}
}
}
public static void ProcAuteurs(ArrayList list, int refAlbum, int refSérie)
{
using (new WaitingCursor())
using (IDbCommand cmd = BDPPCDatabase.GetCommand())
{
if (refAlbum != -1)
{
cmd.CommandText = "select p.refpersonne, p.nompersonne, a.refalbum, NULL as refserie, a.metier "
+ "from personnes p inner join auteurs a on a.refpersonne = p.refpersonne "
+ "where a.refalbum = ? "
+ "order by a.metier, p.uppernompersonne";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefAlbum", refAlbum));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Auteur> dataReader = new BaseDataReader <Auteur>(result))
if (result != null)
{
dataReader.FillList(list);
}
}
if (refSérie != -1)
{
cmd.CommandText = "select p.refpersonne, p.nompersonne, NULL as refalbum, a.refserie, a.metier "
+ "from personnes p inner join auteurs_series a on a.refpersonne = p.refpersonne "
+ "where a.refserie = ? "
+ "order by a.metier, p.uppernompersonne";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefSerie", refSérie));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Auteur> dataReader = new BaseDataReader <Auteur>(result))
if (result != null)
{
dataReader.FillList(list);
}
}
}
}
public override void Fill(params object[] references)
{
if (references.Length < 1)
{
return;
}
Guid reference = (Guid)references[0];
if (reference.Equals(Guid.Empty))
{
return;
}
if (references.Length > 1)
{
this.fID_Auteur = (Guid)references[1];
}
else
{
this.fID_Auteur = Guid.Empty;
}
using (new WaitingCursor())
using (IDbCommand cmd = BDDatabase.Database.GetCommand())
{
cmd.CommandText = "SELECT ID_SERIE, TITRESERIE, TERMINEE, SUJETSERIE, REMARQUESSERIE, SITEWEB, S.ID_EDITEUR, S.ID_COLLECTION, NOMCOLLECTION "
+ "FROM SERIES S LEFT JOIN COLLECTIONS C ON S.ID_COLLECTION = C.ID_COLLECTION "
+ "WHERE ID_SERIE = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_serie", StringUtils.GuidToString(reference)));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <SérieComplet> dataReader = new BaseDataReader <SérieComplet>(result))
if (result != null && result.Read())
{
dataReader.LoadData(this);
this.fTerminée = dataReader.GetInt(2, -1);
this.Editeur.Fill(this.Editeur.Reference);
}
cmd.CommandText = "SELECT ID_ALBUM, TITREALBUM, INTEGRALE, HORSSERIE, TOME, TOMEDEBUT, TOMEFIN, ID_SERIE "
+ "FROM ALBUMS "
+ "WHERE ID_SERIE = ? ";
if (!this.fID_Auteur.Equals(Guid.Empty))
{
cmd.CommandText += "AND ID_ALBUM IN (SELECT ID_ALBUM FROM AUTEURS WHERE ID_PERSONNE = ?) ";
}
cmd.CommandText += "ORDER BY COALESCE(HORSSERIE, -1), COALESCE(INTEGRALE, -1), COALESCE(TOME, -1)";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Serie", StringUtils.GuidToString(reference)));
if (!this.fID_Auteur.Equals(Guid.Empty))
{
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@FIdAuteur", StringUtils.GuidToString(this.fID_Auteur)));
}
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Album> dataReader = new BaseDataReader <Album>(result))
if (result != null)
{
dataReader.FillList(this.Albums);
}
cmd.CommandText = "SELECT Genre "
+ "FROM GenreSeries s INNER JOIN Genres g ON g.ID_Genre = s.ID_Genre "
+ "WHERE ID_Serie = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDDatabase.Database.GetParameter("@ID_Serie", StringUtils.GuidToString(reference)));
this.fGenres.Clear();
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <object> dataReader = new BaseDataReader <object>(result))
while (result != null && result.Read())
{
this.fGenres.Add(dataReader.GetString(0, string.Empty));
}
ListeAuteurs Auteurs = new ListeAuteurs();
StoredProcedures.PS.ProcAuteurs(Auteurs, Guid.Empty, reference, Guid.Empty);
this.Scénaristes.Clear();
this.Dessinateurs.Clear();
this.Coloristes.Clear();
foreach (Auteur auteur in Auteurs)
{
switch (auteur.Metier)
{
case 0:
{
this.Scénaristes.Add(auteur);
break;
}
case 1:
{
this.Dessinateurs.Add(auteur);
break;
}
case 2:
{
this.Coloristes.Add(auteur);
break;
}
}
}
}
}
public override void Fill(params object[] references)
{
if (references.Length < 1)
{
return;
}
int reference = (int)references[0];
if (reference == 0)
{
return;
}
if (references.Length > 1)
{
this.FIdAuteur = (int)references[1];
}
else
{
this.FIdAuteur = -1;
}
using (new WaitingCursor())
using (IDbCommand cmd = BDPPCDatabase.GetCommand())
{
cmd.CommandText = "SELECT REFSERIE, TITRESERIE, TERMINEE, SUJETSERIE, REMARQUESSERIE, SITEWEB, S.REFEDITEUR, S.REFCOLLECTION, NOMCOLLECTION "
+ "FROM SERIES S LEFT JOIN COLLECTIONS C ON S.REFCOLLECTION = C.REFCOLLECTION "
+ "WHERE REFSERIE = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@refserie", reference));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <SérieComplet> dataReader = new BaseDataReader <SérieComplet>(result))
if (result != null && result.Read())
{
dataReader.LoadData(this);
this.Terminée = dataReader.GetInt(2, -1);
this.Editeur.Fill(this.Editeur.Reference);
}
cmd.CommandText = "SELECT REFALBUM, TITREALBUM, INTEGRALE, HORSSERIE, TOME, TOMEDEBUT, TOMEFIN, REFSERIE "
+ "FROM ALBUMS "
+ "WHERE REFSERIE = ? ";
if (this.FIdAuteur != -1)
{
cmd.CommandText += "AND REFALBUM IN (SELECT REFALBUM FROM AUTEURS WHERE REFPERSONNE = ?) ";
}
cmd.CommandText += "ORDER BY COALESCE(HORSSERIE, -1), COALESCE(INTEGRALE, -1), COALESCE(TOME, -1)";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefSerie", reference));
if (this.FIdAuteur != -1)
{
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@FIdAuteur", this.FIdAuteur));
}
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Album> dataReader = new BaseDataReader <Album>(result))
if (result != null)
{
dataReader.FillList(this.Albums);
}
cmd.CommandText = "SELECT Genre "
+ "FROM GenreSeries s INNER JOIN Genres g ON g.RefGenre = s.RefGenre "
+ "WHERE RefSerie = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@RefSerie", reference));
this.Genres.Clear();
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <object> dataReader = new BaseDataReader <object>(result))
while (result.Read())
{
this.Genres.Add(dataReader.GetString(0, string.Empty));
}
ArrayList Auteurs = new ArrayList();
StoredProceduresPPC.ProcAuteurs(Auteurs, -1, reference);
this.Scénaristes.Clear();
this.Dessinateurs.Clear();
this.Coloristes.Clear();
foreach (Auteur auteur in Auteurs)
{
switch (auteur.Metier)
{
case 0:
{
this.Scénaristes.Add(auteur);
break;
}
case 1:
{
this.Dessinateurs.Add(auteur);
break;
}
case 2:
{
this.Coloristes.Add(auteur);
break;
}
}
}
}
}
public override void Fill(params object[] references)
{
if (references.Length < 1)
{
return;
}
int reference = (int)references[0];
if (reference == 0)
{
return;
}
using (new WaitingCursor())
using (IDbCommand cmd = BDPPCDatabase.GetCommand())
{
cmd.CommandText = "SELECT REFALBUM, TITREALBUM, ANNEEPARUTION, REFSERIE, TOME, TOMEDEBUT, TOMEFIN, SUJETALBUM, REMARQUESALBUM, HORSSERIE, INTEGRALE "
+ "FROM ALBUMS "
+ "WHERE RefAlbum = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@refalbum", reference));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <AlbumCompletPPC> dataReader = new BaseDataReader <AlbumCompletPPC>(result))
if (result != null && result.Read())
{
dataReader.LoadData(this);
Série.Fill(dataReader.GetInt(3, -1));
}
ArrayList Auteurs = new ArrayList();
StoredProceduresPPC.ProcAuteurs(Auteurs, reference, -1);
this.Scénaristes.Clear();
this.Dessinateurs.Clear();
this.Coloristes.Clear();
foreach (Auteur auteur in Auteurs)
{
switch (auteur.Metier)
{
case 0:
{
this.Scénaristes.Add(auteur);
break;
}
case 1:
{
this.Dessinateurs.Add(auteur);
break;
}
case 2:
{
this.Coloristes.Add(auteur);
break;
}
}
}
this.Editions.Clear();
cmd.CommandText = "SELECT REFEDITION, RefAlbum, e.REFEDITEUR, e.REFCOLLECTION, NOMCOLLECTION, ANNEEEDITION, PRIX, VO, COULEUR, ISBN, DEDICACE, PRETE, STOCK, Offert, Gratuit, "
+ "NombreDePages, etat, le.libelle as setat, reliure, lr.libelle as sreliure, orientation, lo.libelle as sorientation, FormatEdition, lf.libelle as sFormatEdition, typeedition, lte.libelle as stypeedition, DateAchat, Notes "
+ "FROM EDITIONS e LEFT JOIN COLLECTIONS c ON e.REFCOLLECTION = c.REFCOLLECTION "
+ "LEFT JOIN LISTES le on (le.ref = e.etat and le.categorie = 1) "
+ "LEFT JOIN LISTES lr on (lr.ref = e.reliure and lr.categorie = 2) "
+ "LEFT JOIN LISTES lte on (lte.ref = e.typeedition and lte.categorie = 3) "
+ "LEFT JOIN LISTES lo on (lo.ref = e.orientation and lo.categorie = 4) "
+ "LEFT JOIN LISTES lf on (lf.ref = e.formatedition and lf.categorie = 5) "
+ "WHERE REFALBUM = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add(BDPPCDatabase.GetParameter("@refalbum", RefAlbum));
using (IDataReader result = cmd.ExecuteReader())
using (BaseDataReader <Edition> dataReader = new BaseDataReader <Edition>(result))
if (result != null)
{
dataReader.FillList(this.Editions);
}
//Self.Editeur.Fill(Fields.ByNameAsInteger['REFEDITEUR']);
//Self.Collection.Fill(q);
}
}
