private void CheckInDicoAuteurs(string current_name)
{
db.Query(string.Format("select * from auteursunique where trim( nomauteur )='{0}'", current_name.ToUpper().Replace("'", "''")));
string exist = string.Empty;
foreach (var row in db.FetchAll())
{
exist = "OUI";
}
if (exist == "")
{
exist = "NON";
}
TB_exist.Text = exist;
if (exist == "NON")
{
listBox2.Enabled = true;
TB_Correction.Enabled = true;
char[] delim = new[] { ' ', '-', ',', '.' }; //ajout de la virgule pour dissocier nom/prenom
string compl = "";
string startcompl = "( ";
foreach (string s in current_name.ToUpper().Split(delim))
{
if (s != string.Empty)
{
compl += " nomauteur containing '" + s.Replace("'", "''").Trim() + "' and";
startcompl += " nomauteur starts with '" + s.Substring(0, 1) + "' or";
}
}
if (compl != string.Empty)
{
//compl = compl.Substring(0, compl.Length - 4);
startcompl = startcompl.Substring(0, startcompl.Length - 3) + " )";
db.Query("select * from auteursunique where " + compl + startcompl + " order by nomauteur");
foreach (var row in db.FetchAll())
{
listBox2.Items.Add(row["nomauteur"].ToString());
}
}
}
}
private void BT_CreateTrad_Click(object sender, EventArgs e)
{
db.Query(string.Format("SELECT * from TRADUCTEURS " +
"where STRCRUNCH( nom, 1 )= STRCRUNCH( '{0}', 1 ) and " +
"STRCRUNCH( prenom, 1 )= STRCRUNCH( '{1}', 1 )",
TB_Nom.Text.Replace("'", "''"), TB_Prenom.Text.Replace("'", "''")));
string Found = "";
foreach (var row in db.FetchAll())
{
Found = row["nom"].ToString() + ", " + row["prenom"].ToString();
}
if (Found != "")
{
CB_SelTraducteur.Text = Found;
MessageBox.Show("traducteur déjà existant !");
}
else
{
db.Query("select max(id) as a1 from traducteurs");
int x = 0;
foreach (var row in db.FetchAll())
{
x = Convert.ToInt32(row["a1"]);
}
if (x > 0)
{
x++;
db.Query(string.Format("insert into TRADUCTEURS ( ID, NOM, PRENOM ) values ( {0}, '{1}', '{2}' )",
x.ToString(), TB_Nom.Text.Replace("'", "''"), TB_Prenom.Text.Replace("'", "''")));
CB_SelTraducteur.Text = TB_Nom.Text + ", " + TB_Prenom.Text;
DTrad.Add(CB_SelTraducteur.Text, x.ToString());
}
}
TB_Prenom.Text = "";
TB_Nom.Text = "";
BT_Select_Click(this, e);
}
public DicoAuteurForm(bool Create,
string Auteur,
FDB fdb,
Dictionary <string, string> DL,
Dictionary <string, string> DP,
string CreateNom,
string CreatePrenom)
{
InitializeComponent();
db = fdb;
AuteurParam = Auteur;
DLangue = DL;
DPays = DP;
Creation = Create;
string Origine = Environment.UserName;
idOrigine = GetIdOrigine(Origine);
if (idOrigine == 0)
{
db.Query("INSERT INTO SESSIONS ( session ) values ( '" + Origine + "' )");
idOrigine = GetIdOrigine(Origine);
}
pictureBox1.Image = null;
foreach (var pair in DLangue)
{
CB_langue.Items.Add(pair.Key);
}
foreach (var pair in DPays)
{
CB_pays.Items.Add(pair.Key);
}
if (!Create)
{
LoadDico(Auteur);
}
else
{
TB_Nom.Text = CreateNom;
TB_Prenom.Text = CreatePrenom;
}
}
public void LoadDico(string Auteur)
{
db.Query(string.Format("SELECT * From AUTEURSUNIQUE where NOMAUTEUR='{0}'", Auteur.Replace("'", "''")));
foreach (var row in db.FetchAll())
{
id_auteur = Convert.ToInt32(row["id_auteur"]);
foreach (var pair in row)
{
foreach (Control c in this.Controls["groupBox3"].Controls)
{
if (c.Tag != null)
{
if (c.Tag.ToString() == pair.Key)
{
c.Text = pair.Value.ToString();
}
}
}
}
if (TB_Nom.Text == "")
{
string[] splitAuteur = Auteur.Split(',');
if (splitAuteur.Length > 1)
{
TB_Nom.Text = splitAuteur[0].Trim().ToLower();
TB_Prenom.Text = splitAuteur[1].Trim().ToLower();
}
else
{
TB_Nom.Text = Auteur;
}
}
TB_Biographie.Text = row["biographie"].ToString();
if (row["certification"].ToString() == "1")
{
pictureBox1.Image = SaisieLivre.Properties.Resources.certified_1;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
string param = string.Empty;
if (checkBox1.Checked && comboBox1.Text != "")
{
param += " editeur='" + comboBox1.Text.Replace("'", "''") + "' AND";
}
if (checkBox4.Checked && comboBox2.Text != "")
{
param += " distributeur='" + comboBox2.Text.Replace("'", "''") + "' AND";
}
if (checkBox2.Checked)
{
string field = string.Empty;
if (radioButton1.Checked)
{
field = " datemaj";
}
else
{
field = " dateparution";
}
param += field + " between '" + dateTimePicker1.Value.ToString("MM/dd/yyyy") + "' and '" + dateTimePicker2.Value.ToString("MM/dd/yyyy") + "' AND";
}
if (checkBox3.Checked)
{
string Not = "";
if (RB_exclure.Checked)
{
Not = "NOT";
}
string codes = string.Empty;
foreach (string s in textBox1.Text.Split(';'))
{
if (s.Trim() != "")
{
codes += "'" + s.Trim() + "',";
}
}
param += " codesupport " + Not + " in ( " + codes.Substring(0, codes.Length - 1) + " ) AND";
}
if (CBX_aparaitre.Checked && CBX_dispo.Checked)
{
param += " dispo in ( 1, 2 ) AND";
}
else if (CBX_aparaitre.Checked)
{
param += " dispo in ( 2 ) AND";
}
else if (CBX_dispo.Checked)
{
param += " dispo in ( 1 ) AND";
}
/*if (checkBox4.Checked)
* {
* param+= " dispo in ( 1,2 ) AND";
* }*/
//vire le " AND" final
param = param.Substring(0, param.Length - 4);
string QGenListe = string.Format(Properties.Resources.QueryGenListe, param);
db.Query(QGenListe);
foreach (var Row in db.FetchAll())
{
listBox.Items.Add(Row["gencod"].ToString());
}
Cursor = Cursors.Arrow;
this.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string Query;
string Hauteur = "null", NBPages = "null", Largeur = "null";
if (!string.IsNullOrEmpty(TB_Hauteur.Text))
{
Hauteur = TB_Hauteur.Text;
}
if (!string.IsNullOrEmpty(TB_Largeur.Text))
{
Largeur = TB_Largeur.Text;
}
if (!string.IsNullOrEmpty(TB_NBPages.Text))
{
NBPages = TB_NBPages.Text;
}
string Present_titre = "0";
if (CBX_PresentTitre.Checked)
{
Present_titre = "1";
}
string Newgenre = "";
if (TB_Genre_0.Text != "")
{
Newgenre += TB_Genre_0.Text;
if (TB_Genre_1.Text == "")
{
TB_Genre_1.Text = "0";
}
string G1 = set2chars(TB_Genre_1.Text);
Newgenre += G1;
if (TB_Genre_2.Text == "")
{
TB_Genre_2.Text = "0";
}
string G2 = set2chars(TB_Genre_2.Text);
Newgenre += G2;
if (TB_Genre_3.Text == "")
{
TB_Genre_3.Text = "0";
}
string G3 = set2chars(TB_Genre_3.Text);
Newgenre += G3;
}
//on attend un entier...
if (Newgenre == "")
{
Newgenre = "null";
}
if ((StrCollection != TB_CollName.Text) &&
(!string.IsNullOrEmpty(StrCollection)))
{
DialogResult dr = MessageBox.Show("Modifier la collection " + StrCollection + " en " + TB_CollName.Text +
" ?\r\n\r\nOui: modification du libellé et mise à jour de toutes les références rattachées à l'ancienne collection.\r\n\r\nNon: création d'une nouvelle collection.",
"Confirmation", MessageBoxButtons.YesNoCancel);
switch (dr)
{
case DialogResult.Yes:
Creation = false;
break;
case DialogResult.No:
Creation = true;
break;
default:
return;
}
}
string CN = StrCollection;
if (string.IsNullOrEmpty(CN) || Creation)
{
CN = TB_CollName.Text;
}
string Qry = string.Format("select * from collections where trim(editeur)='{1}' and trim(collection)='{0}'", CN.Replace("'", "''"), TB_Editeur.Text.Replace("'", "''"));
try
{
db.Query(Qry);
foreach (Row row in db.FetchAll())
{
IDColl = row["id"].ToString();
Creation = false;
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
//lectorat
string IDLectorat = "null";
if (DLectorat.ContainsKey(CB_Lectorat.Text) && CB_Lectorat.Text != "")
{
IDLectorat = "'" + DLectorat[CB_Lectorat.Text] + "'";
}
if (Creation)
{
//on n'ajoute pas le champ ID, il est généré par trigger.
Query = "INSERT INTO COLLECTIONS " +
"( COLLECTION, EDITEUR, CODESUPPORT, " +
" NEWGENRE, HAUTEUR, LARGEUR, AUTEURS, " +
" PRESENTATION, NBRPAGE, WEBSITE, OBCODE, " +
" PRESENT_TITRE, ID_LECTORAT, DATUM, " +
" CODECOLLECTION ) " +
"VALUES ( " +
" '{10}', '{11}', '{0}', " +
" {1}, {2}, {3}, '{4}', " + //param 1 : pas de guillemets, c'est volontaire
" '{5}', {6}, '{7}', '{8}', " +
" '{9}', {12}, 'now', " + //param 12 : pas de guillemets, c'est volontaire
" Gen_Id(collection_num,1) ) -- {13}"; //param 13 en commentaire
}
else
{
Cursor = Cursors.WaitCursor;
Query = "UPDATE COLLECTIONS " +
"SET " +
" Datum='now', " +
" CODESUPPORT='{0}', " +
" NEWGENRE={1}, " +
" HAUTEUR={2}, " +
" LARGEUR={3}, " +
" AUTEURS='{4}', " +
" PRESENTATION='{5}', " +
" NBRPAGE={6}, " +
" WEBSITE='{7}', " +
" OBCODE='{8}', " +
" PRESENT_TITRE='{9}', " +
" ID_LECTORAT= {12}, " + //param 12 : pas de guillemets, c'est volontaire
" COLLECTION='{10}' " +
"WHERE " +
" cast(trim(collection) as varchar(160)) = '{13}' AND " +
" EDITEUR = '{11}'";
}
db.Query(string.Format(Query,
CB_CodeSupport.Text,
Newgenre,
Hauteur,
Largeur,
TB_Auteurs.Text.Replace("'", "''"),
TB_Presentation.Text.Replace("'", "''"),
NBPages,
TB_Web.Text,
TB_OBCode.Text,
Present_titre,
TB_CollName.Text.Replace("'", "''"),
TB_Editeur.Text.Replace("'", "''"),
IDLectorat,
StrCollection.Replace("'", "''")));
if (Creation)
{
try
{
db.Query(Qry);
foreach (Row row in db.FetchAll())
{
IDColl = row["id"].ToString();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
else
{
//maj des réfs liées à la collection uniquement si le libellé collection change.
if (StrCollection != TB_CollName.Text)
{
UpdateRefs();
//mise à jour du combobox
int oldidx = mf.CB_Collection.Items.IndexOf(CollDefaut);
mf.CB_Collection.Items.RemoveAt(oldidx);
mf.CB_Collection.Items.Insert(oldidx, TB_CollName.Text);
mf.TB_IDCollection.Text = IDColl;
}
}
Cursor = Cursors.Default;
}