public void DeleteFromSQLITE_TBReferentiel(List <ReferentielBLL> Referentiel)
{
ConnexionSQLITE con = new ConnexionSQLITE();
for (int i = 0; i < Referentiel.Count; i++)
{
if (Referentiel[i].Cpl1 == null || Referentiel[i].Cpl1 == "")
{
Referentiel[i].Cpl1 = "0";
}
if (Referentiel[i].Cpl2 == null || Referentiel[i].Cpl2 == "")
{
Referentiel[i].Cpl2 = "0";
}
string txtSQLQuery = "delete from TB$S_PrevReferentiel " +
" where Type like '" + Referentiel[i].Type +
"' and Code like '" + Referentiel[i].Code +
"' and Cpl like '" + Referentiel[i].Cpl +
"' and IFNULL(Cpl1,'') like'" + Referentiel[i].Cpl1 +
"' and IFNULL(Cpl2,'') like'" + Referentiel[i].Cpl2 +
"' and FlagPreventiel like 2";
con.ExecuteQuery(txtSQLQuery);
//Thread delete_from_sqlite_ref = new Thread(new ParameterizedThreadStart(ThreadQuerySQLite));
//delete_from_sqlite_ref.Start(txtSQLQuery);
}
}
public void InsertIntoSQLITE_TBReferentiel(List <ReferentielBLL> Referentiel)
{
ConnexionSQLITE con = new ConnexionSQLITE();
for (int i = 0; i < Referentiel.Count; i++)
{
if (Referentiel[i].Code != null && Referentiel[i].Code != "" && Referentiel[i].Lib != null && Referentiel[i].Lib != "")
{
if (Referentiel[i].Cpl1 == "55|P03")
{
Console.WriteLine("ici!");
}
string txtSQLQuery = "insert into TB$S_PrevReferentiel (Type,TypeItem,Code,Lib,CodeOrigine,InActif,Cpl,Cpl1,Cpl2,Cpl3,DateFinValidite,FlagPreventiel) " +
" values ('" + Referentiel[i].Type +
"','" + Referentiel[i].TypeItem +
"','" + Referentiel[i].Code +
"','" + Referentiel[i].Lib.Replace("'", "''") +
"','" + Referentiel[i].CodeOrigine +
"','" + Referentiel[i].InActif +
"','" + Referentiel[i].Cpl +
"','" + Referentiel[i].Cpl1 +
"','" + Referentiel[i].Cpl2 +
"','" + Referentiel[i].Cpl3 +
"','" + Referentiel[i].DateFinValidite +
"','" + Referentiel[i].FlagPreventiel + "')";
con.ExecuteQuery(txtSQLQuery);
//Thread insert_into_sqlite_ref = new Thread(new ParameterizedThreadStart(ThreadQuerySQLite));
//insert_into_sqlite_ref.Start(txtSQLQuery);
}
}
}
public void SupprimeRecodageFULLTABLE_referentiel()
{
ConnexionSQLITE con = new ConnexionSQLITE();
string txtSQLQuery = "delete from TB$S_PrevReferentiel where FlagPreventiel = 2";
con.ExecuteQuery(txtSQLQuery);
con.Dispose();
ReferentielDAL RefObject = new ReferentielDAL();
VariablePartage.TableReferentiel = RefObject.ObtenirListeReferentiel_SQLITE();
}
public void SupprimeRecodageModule_referentiel(List <ReferentielBLL> Referentiel)
{
ConnexionSQLITE con = new ConnexionSQLITE();
ReferentielDAL RefObject = new ReferentielDAL();
string txtSQLQuery = "delete from TB$S_PrevReferentiel where FlagPreventiel = 2 and Cpl = '" + Referentiel[0].Cpl + "' and Cpl1 = '" + Referentiel[0].Cpl1 + "' and Cpl2 = '" + Referentiel[0].Cpl2 + "' and Type = '" + Referentiel[0].Type + "'";
con.ExecuteQuery(txtSQLQuery);
con.Dispose();
if (Referentiel[0].Cpl == "135")
{
string txtSQLQuery2 = "delete from TB$S_PrevReferentiel where FlagPreventiel = 2 and Cpl = '50'";
con.ExecuteQuery(txtSQLQuery2);
con.Dispose();
}
if (Referentiel[0].Cpl == "141")
{
string txtSQLQuery2 = "delete from TB$S_PrevReferentiel where FlagPreventiel = 2 and Cpl = '60'";
con.ExecuteQuery(txtSQLQuery2);
con.Dispose();
}
VariablePartage.TableReferentiel = RefObject.ObtenirListeReferentiel_SQLITE();
}
public List <ComboBoxFiltreBLL> ObtenirComboBoxFiltre()
{
using (ConnexionSQLITE con = new ConnexionSQLITE())
{
con.SetConnection();
con.sql_con.Open();
//sql_cmd = sql_con.CreateCommand();
//string CommandText = "select Type_Item, Ancien_Code, Libelle_Ancien_Code,AncienCodeActif,Nouveau_Code,Libelle_Nouveau_Code,Code_utilise,NomSchema,DateRecensement,DateMAJ,TypeRecodage,NouveauCodeInactif,UtilisateurCreation,TypeRef,NomRef,Cpl,Cpl1,Cpl2,Occurrence from TB$S_CorrespondanceItem";
string CommandText = "select distinct NomRef, Cpl, Cpl1, Cpl2,TypeRef from TB$S_CorrespondanceItem where NomRef is not null and Cpl not in('50','60') and NomRef!= 'PersonnePhysique' order by Nomref";
using (con.sql_cmd = new SQLiteCommand(CommandText, con.sql_con))
{
using (SQLiteDataReader reader = con.sql_cmd.ExecuteReader())
{
//DB = new SQLiteDataAdapter(CommandText, sql_con);
List <ComboBoxFiltreBLL> ListComboBox = new List <ComboBoxFiltreBLL>();
while (reader.Read())
{
ComboBoxFiltreBLL comboboxfiltre = new ComboBoxFiltreBLL();
if (reader["NomRef"] != DBNull.Value)
{
comboboxfiltre.NomRef = (string)reader["NomRef"];
}
if (reader["Cpl"] != DBNull.Value)
{
comboboxfiltre.Cpl = (string)reader["Cpl"];
}
if (reader["Cpl1"] != DBNull.Value && (string)reader["Cpl1"] != "")
{
comboboxfiltre.Cpl += "|" + (string)reader["Cpl1"];
}
else
{
comboboxfiltre.Cpl += "|" + "0";
}
if (reader["Cpl2"] != DBNull.Value && (string)reader["Cpl2"] != "")
{
comboboxfiltre.Cpl += "|" + (string)reader["Cpl2"];
}
else
{
comboboxfiltre.Cpl += "|" + "0";
}
if (reader["TypeRef"] != DBNull.Value)
{
comboboxfiltre.Cpl += "|" + (string)reader["TypeRef"];
}
ComboBoxFiltreBLL comboBoxFiltreBLL = new ComboBoxFiltreBLL();
if (comboBoxFiltreBLL.ContientNomRefAdmin(VariablePartage.ListeNomRef_admin, comboboxfiltre.NomRef))
{
//Init.ListeNomRef_Adm_Med[i][0] = Init.ListeNomRef_all[i];
//Init.ListeNomRef_Adm_Med[i][1] = "Adm";
comboboxfiltre.TypeRecodage = 1;
}
else
{
//Init.ListeNomRef_Adm_Med[i][0] = Init.ListeNomRef_all[i];
//Init.ListeNomRef_Adm_Med[i][1] = "Med";
comboboxfiltre.TypeRecodage = 3;
}
ListComboBox.Add(comboboxfiltre);
}
con.sql_con.Close();
ComboBoxFiltreBLL comboboxfiltre_administratif = new ComboBoxFiltreBLL();
comboboxfiltre_administratif.NomRef = "-----Administratif-----";
comboboxfiltre_administratif.Cpl = "adm1|adm1|adm1|adm1";
comboboxfiltre_administratif.TypeRecodage = 0;
ListComboBox.Add(comboboxfiltre_administratif);
ComboBoxFiltreBLL comboboxfiltre_medical = new ComboBoxFiltreBLL();
comboboxfiltre_medical.NomRef = "-------Medical---------";
comboboxfiltre_medical.Cpl = "med1|med1|med1|med1";
comboboxfiltre_medical.TypeRecodage = 2;
ListComboBox.Add(comboboxfiltre_medical);
ListComboBox.Sort(delegate(ComboBoxFiltreBLL a1, ComboBoxFiltreBLL a2) { return(a1.TypeRecodage - a2.TypeRecodage); });
return(ListComboBox);
}
}
}
}
public void ObtenirComboBoxFiltre_Thread(object args)
{
Array argArray = new object[1];
argArray = (Array)args;
Form1 myform = (Form1)argArray.GetValue(0);
while (VariablePartage.ThreadNumber != 6)
{
//Staying here;
}
using (ConnexionSQLITE con = new ConnexionSQLITE())
{
con.SetConnection();
con.sql_con.Open();
//sql_cmd = sql_con.CreateCommand();
//string CommandText = "select Type_Item, Ancien_Code, Libelle_Ancien_Code,AncienCodeActif,Nouveau_Code,Libelle_Nouveau_Code,Code_utilise,NomSchema,DateRecensement,DateMAJ,TypeRecodage,NouveauCodeInactif,UtilisateurCreation,TypeRef,NomRef,Cpl,Cpl1,Cpl2,Occurrence from TB$S_CorrespondanceItem";
string CommandText = "select distinct NomRef, Cpl, Cpl1, Cpl2,TypeRef from TB$S_CorrespondanceItem where NomRef is not null and Cpl not in('50','60') and NomRef!= 'PersonnePhysique' order by Nomref";
using (con.sql_cmd = new SQLiteCommand(CommandText, con.sql_con))
{
using (SQLiteDataReader reader = con.sql_cmd.ExecuteReader())
{
//DB = new SQLiteDataAdapter(CommandText, sql_con);
List <ComboBoxFiltreBLL> ListComboBox = new List <ComboBoxFiltreBLL>();
while (reader.Read())
{
ComboBoxFiltreBLL comboboxfiltre = new ComboBoxFiltreBLL();
if (reader["NomRef"] != DBNull.Value)
{
comboboxfiltre.NomRef = (string)reader["NomRef"];
}
if (reader["Cpl"] != DBNull.Value)
{
comboboxfiltre.Cpl = (string)reader["Cpl"];
}
if (reader["Cpl1"] != DBNull.Value && (string)reader["Cpl1"] != "")
{
comboboxfiltre.Cpl += "|" + (string)reader["Cpl1"];
}
else
{
comboboxfiltre.Cpl += "|" + "0";
}
if (reader["Cpl2"] != DBNull.Value && (string)reader["Cpl2"] != "")
{
comboboxfiltre.Cpl += "|" + (string)reader["Cpl2"];
}
else
{
comboboxfiltre.Cpl += "|" + "0";
}
if (reader["TypeRef"] != DBNull.Value)
{
comboboxfiltre.Cpl += "|" + (string)reader["TypeRef"];
}
ComboBoxFiltreBLL comboBoxFiltreBLL = new ComboBoxFiltreBLL();
if (comboBoxFiltreBLL.ContientNomRefAdmin(VariablePartage.ListeNomRef_admin, comboboxfiltre.NomRef))
{
//Init.ListeNomRef_Adm_Med[i][0] = Init.ListeNomRef_all[i];
//Init.ListeNomRef_Adm_Med[i][1] = "Adm";
comboboxfiltre.TypeRecodage = 1;
}
else
{
//Init.ListeNomRef_Adm_Med[i][0] = Init.ListeNomRef_all[i];
//Init.ListeNomRef_Adm_Med[i][1] = "Med";
comboboxfiltre.TypeRecodage = 3;
}
ListComboBox.Add(comboboxfiltre);
}
con.sql_con.Close();
ComboBoxFiltreBLL comboboxfiltre_administratif = new ComboBoxFiltreBLL();
comboboxfiltre_administratif.NomRef = "-----Administratif-----";
comboboxfiltre_administratif.Cpl = "adm1|adm1|adm1|adm1";
comboboxfiltre_administratif.TypeRecodage = 0;
ListComboBox.Add(comboboxfiltre_administratif);
ComboBoxFiltreBLL comboboxfiltre_medical = new ComboBoxFiltreBLL();
comboboxfiltre_medical.NomRef = "-------Medical---------";
comboboxfiltre_medical.Cpl = "med1|med1|med1|med1";
comboboxfiltre_medical.TypeRecodage = 2;
ListComboBox.Add(comboboxfiltre_medical);
ListComboBox.Sort(delegate(ComboBoxFiltreBLL a1, ComboBoxFiltreBLL a2) { return(a1.TypeRecodage - a2.TypeRecodage); });
VariablePartage.ComboBoxFiltre = ListComboBox;
//return (ListComboBox);
}
}
}
GUIFonction GUI = new GUIFonction();
InitComboBoxFiltre_invoke(myform);
VariablePartage.BaseCharge = true;
ReactiveButton_invoke(myform);
//myform.comboBox_filtre.DropDownStyle = ComboBoxStyle.DropDownList;
InitClientEncour_Invoke(myform);
//GUI.InitStructSaisie(dataGridView_saisie);
//GUI.InitStructReferentiel(dataGridView_ref,dataGridView_saisie);
//ActionRecodage actionRecodage = new ActionRecodage();
//actionRecodage.FilterModule(myform.dataGridView_saisie, myform.dataGridView_ref, myform);
FilterModule_invoke(myform);
VariablePartage.ThreadNumber = 7;
}
public void ThreadQuerySQLite(object query)
{
ConnexionSQLITE con = new ConnexionSQLITE();
con.ExecuteQuery((string)query);
}
public List <ReferentielBLL> ObtenirListeReferentiel_SQLITE()
{
Console.WriteLine("ici");
using (ConnexionSQLITE con = new ConnexionSQLITE())
{
Dictionary <string, string> columnNameToAddColumnSql = new Dictionary <string, string>
{
{
"FlagPreventiel",
"ALTER TABLE TB$S_PrevReferentiel ADD COLUMN FlagPreventiel INTEGER "
}
};
foreach (var pair in columnNameToAddColumnSql)
{
string columnName = pair.Key;
string sql = pair.Value;
try
{
con.ExecuteQuery(sql);
con.ExecuteQuery("Update TB$S_PrevReferentiel set FlagPreventiel = 0");
}
catch (System.Data.SQLite.SQLiteException e)
{
con.sql_con.Close();
Console.WriteLine(string.Format("Failed to create column [{0}]. Most likely it already exists, which is fine.", columnName));
}
}
List <ReferentielBLL> ListRef = new List <ReferentielBLL>();
con.sql_con.Open();
string CommandText = "select * from TB$S_PREVReferentiel";
using (con.sql_cmd = new SQLiteCommand(CommandText, con.sql_con))
{
using (SQLiteDataReader reader = con.sql_cmd.ExecuteReader())
{
Fonc fonc = new Fonc();
while (reader.Read())
{
ReferentielBLL Ref = new ReferentielBLL();
if (reader["Type"] != DBNull.Value)
{
Ref.Type = (string)reader["Type"];
}
if (reader["TypeItem"] != DBNull.Value)
{
Ref.TypeItem = (string)reader["TypeItem"];
}
if (reader["Code"] != DBNull.Value)
{
Ref.Code = (string)reader["Code"];
}
if (reader["Lib"] != DBNull.Value && (string)reader["Lib"] != "")
{
Ref.Lib = (string)reader["Lib"];
Ref.Canonical = fonc.CanonicalString((string)reader["Lib"]);
}
if (reader["CodeOrigine"] != DBNull.Value)
{
Ref.CodeOrigine = (string)reader["CodeOrigine"];
}
if (reader["InActif"] != DBNull.Value)
{
Ref.InActif = Convert.ToBoolean(reader["InActif"]);
}
//Console.WriteLine(reader["InActif"]);
//Ref.InActif = Convert.ToBoolean(Convert.ToString(reader["InActif"]));
if (reader["Cpl"] != DBNull.Value && (string)reader["Cpl"] != "")
{
Ref.Cpl = (string)reader["Cpl"];
}
else
{
Ref.Cpl = "0";
}
if (reader["Cpl1"] != DBNull.Value && (string)reader["Cpl1"] != "")
{
Ref.Cpl1 = (string)reader["Cpl1"];
}
else
{
Ref.Cpl1 = "0";
}
if (reader["Cpl2"] != DBNull.Value && (string)reader["Cpl2"] != "")
{
Ref.Cpl2 = (string)reader["Cpl2"];
}
else
{
Ref.Cpl2 = "0";
}
if (reader["Cpl3"] != DBNull.Value && (string)reader["Cpl3"] != "")
{
Ref.Cpl3 = (string)reader["Cpl3"];
}
else
{
Ref.Cpl3 = "0";
}
if (reader["DateFinValidite"] != DBNull.Value)
{
Ref.DateFinValidite = (string)reader["DateFinValidite"];
}
if (reader["FlagPreventiel"] != DBNull.Value)
{
Ref.FlagPreventiel = Convert.ToInt32(reader["FlagPreventiel"]);
}
ListRef.Add(Ref);
}
con.sql_con.Close();
con.sql_con.Dispose();
}
}
//sql_cmd = sql_con.CreateCommand();
//string CommandText = "select Type_Item, Ancien_Code, Libelle_Ancien_Code,AncienCodeActif,Nouveau_Code,Libelle_Nouveau_Code,Code_utilise,NomSchema,DateRecensement,DateMAJ,TypeRecodage,NouveauCodeInactif,UtilisateurCreation,TypeRef,NomRef,Cpl,Cpl1,Cpl2,Occurrence from TB$S_CorrespondanceItem";
// where TypeItem !='PersonnePhysique'";
//string CommandText = "select Type,TypeItem,Code,Lib,CodeOrigine,InActif,Cpl,Cpl1,Cpl2,Cpl3,DateFinValidite from TB$S_PREVReferentiel where (Type!='CTRL' and Cpl!='0') ";
//DB = new SQLiteDataAdapter(CommandText, sql_con);
return(ListRef);
}
}
