public  DataAccessLayer.SOAResult InsertLog ( BusinessLayer.Log log)
        {

            // First check if this record exists in the SQLite

            using (var contents =  SQLiteUtilities.MainSQLiteConnection.CreateCommand ()) 
            {

                using (var c = SQLiteUtilities.MainSQLiteConnection.CreateCommand ()) 
                {
                    c.CommandText = "INSERT INTO[Log] ([Date], [LogText]) VALUES ('" + log.Date + "', '" +
                        log.LogText + "')";
                    try
                    {
                        var rowcount = c.ExecuteNonQuery (); // rowcount will be 1
                        if (rowcount == 1)
                            return  DataAccessLayer.SOAResult.TRUE;
                        else
                            return  DataAccessLayer.SOAResult.FALSE;
                    }
                    catch(Exception ex)
                    {
                        DataAccessLayer.ExceptionWriter.WriteLogFile(ex);
                        return DataAccessLayer.SOAResult.FALSE;
                    }

                }

            }

        }

        public System.Collections.Generic.List<BusinessLayer.Anfrage>    GetAnfragen (string lieferantenId, string deviceLanguage , ref BusinessLayer.User user)
        {
            // First check for the network status
            if (user.NetworkStatus == NetworkState.Disconnected)
                return GetAnfragen (lieferantenId, deviceLanguage, ref user, true);

            Stream dataStream;

            // Use the ServiceManager to build send the data to the SOA services            
            // The ServiceManager is responsible to send the data to the SOA services       
            // Run the service and return the value as a Datastream 
            // EXECUSERPERMISSION.JSON

            SOAServiceManager sm = new SOAServiceManager (DataAccessLayer.Utilities.ServerIP + "/anfragen.json",user.IdSession, DataAccessLayer.HTTPMethod.POST);                  

            // Add the Values                           
            sm.AddCriteriaValue ("KundeID", lieferantenId);
            sm.AddCriteriaValue ("Mandant", user.Mandant);
            sm.AddCriteriaValue ("Sprache", deviceLanguage);

            // Now it's show time   
            dataStream = sm.CommitPost ();                                                  

            if (dataStream == null)
                return null;

            StreamReader reader = new StreamReader(dataStream);
            if (reader == null)
                return null;

            string jsonText = reader.ReadToEnd ();

            return  Newtonsoft.Json.JsonConvert.DeserializeObject<List<BusinessLayer.Anfrage>> (jsonText);

        }

 /// <summary>
 /// Sets the controls.
 /// </summary>
 /// <param name="anschrift">Anschrift.</param>
 /// <param name="image">Image.</param>
 public void SetControls(BusinessLayer.Artikel artikel)
 {
     // fill the controls
     LblArtikelBez1.Text = artikel.Bez1;
     ArtikelBez2_6Label.Text = GetArtikelBez2_6(artikel);
     ArtikelNummerLabel.Text = artikel.Nummer;
 }

        public static System.Collections.Generic.List<BusinessLayer.TaskTyp>    GetTaskTyps( ref BusinessLayer.User user)
        {
            // First check for the network status
            if (user.NetworkStatus == NetworkState.Disconnected)
                return GetTaskTyps ( ref user, true);

            Stream dataStream;

            // Use the ServiceManager to build send the data to the SOA services            
            // The ServiceManager is responsible to send the data to the SOA services       
            // Run the service and return the value as a Datastream 
            // EXECUSERPERMISSION.JSON

            SOAServiceManager sm = new SOAServiceManager (DataAccessLayer.Utilities.ServerIP + "/tasktyp.json",user.IdSession, DataAccessLayer.HTTPMethod.POST);                    


            // Now it's show time   
            dataStream = sm.CommitPost ();                                                  

            if (dataStream == null)
                return null;

            StreamReader reader = new StreamReader(dataStream);
            if (reader == null)
                return null;

            string jsonText = reader.ReadToEnd ();

            return  Newtonsoft.Json.JsonConvert.DeserializeObject<List<BusinessLayer.TaskTyp>> (jsonText);
        }

 /// <summary>
 /// Sets the controls.
 /// </summary>
 /// <param name="anschrift">Anschrift.</param>
 /// <param name="image">Image.</param>
 public void SetControls(BusinessLayer.Person person)
 {
     // fill the controls
     //            MainImageView.Image = UIImage.FromFile(image);
     PersonNameLabel.Text = person.Name;
     PersonNummerLabel.Text = person.Nummer;
 }

    public string SaveQuery()
    {
        string connectionString = ConfigurationManager.AppSettings.Get("connString");
        ListItem li = new ListItem();
        string symptoms = string.Empty ;

        Query queryObj = new Query();
        int i = 0; int counter = 0;

        int max = counter;
        counter = 0;

        queryObj.Username = Session["Username"].ToString();
        queryObj.QueryAreaOfInterest = ddlInterestList.SelectedItem.Value;
        queryObj.Symptoms = txtSymptoms.Text;
        queryObj.MedicalHistory = txtMedHistory.Text;
        queryObj.Status = "Unanswered";
        queryObj.IsAttendedTo = false;
        queryObj.PostedDatetime = DateTime.Now;

        //passing to business layer

        //string message;
        BusinessLayer businessLayerObj = new BusinessLayer();
        try
        {
            businessLayerObj.InsertQuery(queryObj, connectionString);
        }
        catch(Exception ex)
        {
            return ex.Message;
        }

        return "noerror";
    }

 //
 public void InsertDetail(BusinessLayer.NhanVienBsn nhanvien)
 {
     try
     {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandType = CommandType.Text;
         cmd.CommandText = "INSERT INTO " + this.tableName
             + "(" + this.fieldList + ") VALUES ('"
             + nhanvien.TeNV + "','" + nhanvien.Diachi + "','" + nhanvien.Ngaysinh.ToShortDateString() + "'," + Convert.ToInt32(nhanvien.Giotinh) + ",'" + nhanvien.Dienthoai + "'," + nhanvien.MaCV + "," + nhanvien.MaLuong + ",'" + nhanvien.SoBH + "',"+nhanvien.MaPhong+"," + nhanvien.MaCM + "," + nhanvien.MaTD + ")";
         dt = new DataTable();
         dt = GetData(cmd);
        
         MessageBox.Show("thêm m?i thành công", "Thông báo");
         //conn.Close();
     }
     catch (Exception)
     {
         MessageBox.Show("B? trùng khóa, thêm m?i th?t b?i", "Thông báo");
     }
     finally
     {
         closeConnect();
         // conn.Close();
     }
 }

        public string GetUmsatz(string id, ref BusinessLayer.User user)
        {
            // First we test if the networkstatus is disconnected or not
            if (user.NetworkStatus == NetworkState.Disconnected)
                return GetUmsatz (id, ref user, true);

            Stream dataStream;

            // Use the ServiceManager to build send the data to the SOA services			
            // The ServiceManager is responsible to send the data to the SOA services 		
            // Run the service and return the value as a Datastream	
            // EXECUSERPERMISSION.JSON

            SOAServiceManager sm = new SOAServiceManager (DataAccessLayer.Utilities.ServerIP + "/getpersonumsatz.json",user.IdSession, DataAccessLayer.HTTPMethod.POST);

            // Add the Values 							
            sm.AddCriteriaValue("ID", id);
            sm.AddCriteriaValue("Mandant", user.Mandant);
            sm.AddCriteriaValue("Bereich", "8");

            // Now it's show time	
            dataStream = sm.CommitPost();
            if (dataStream == null)
                return "0";

            StreamReader reader = new StreamReader(dataStream);
            string jsonText = reader.ReadToEnd();


            return  jsonText.Trim();

        }

        public static string GetTownViaPLZ (string PLZ, ref BusinessLayer.User user)
		{
			Stream dataStream;

			// Use the ServiceManager to build send the data to the SOA services			
			// The ServiceManager is responsible to send the data to the SOA services 		
			// Run the service and return the value as a Datastream	
			// EXECUSERPERMISSION.JSON

			SOAServiceManager sm = new SOAServiceManager (DataAccessLayer.Utilities.ServerIP + "/GetTownViaPLZ.json",user.IdSession, DataAccessLayer.HTTPMethod.POST);					

			// Add the Values 							
			sm.AddCriteriaValue ("PLZ", PLZ);

			// Now it's show time	
            dataStream = sm.CommitPost ();													

            if (dataStream == null)
                return "";
            
            StreamReader reader = new StreamReader(dataStream);
			string jsonText = reader.ReadToEnd ();		

			/*	JavaScriptSerializer js = new JavaScriptSerializer();
			Person [] persons =  js.Deserialize<Person[]>(json);
*/

			return   jsonText.Trim ();
		}

    public string SaveAdvice()
    {
        string connectionString = ConfigurationManager.AppSettings.Get("connString");
        Advice adviceObj = new Advice();

        //creating an advice object
        adviceObj.QueryId = Convert.ToInt32(Request.QueryString["QueryId"]);

        adviceObj.Username = Session["Username"].ToString() ;

        adviceObj.Likes = 0;

        adviceObj.Dislikes = 0;

        adviceObj.AdviceTitle = txtAdviceTitle.Text;

        adviceObj.AdviceDescription = txtAdvice.Text;

        adviceObj.AdviceDateTime = DateTime.Now;

        //inserting data through business layer
        BusinessLayer businessLayerObj = new BusinessLayer();

        try
        {
            businessLayerObj.InsertAdvice(adviceObj, connectionString);
        }
        catch(Exception ex)
        {
            return ex.Message;
        }

        return "noerror";
    }

        /// <summary>
        /// Contructeur d'un fenètre d'ajout
        /// </summary>
        /// <param name="biblio"> Bibliothèque utilisée</param>
        /// <param name="ih"> Ihm principale </param>
        public ajouterLivre(BusinessLayer.Metier.Bibliotheque biblio, IHM ih)
        {
            _biblio = biblio;
            InitializeComponent();

            // Remplir le comboBoxAuteur et editeur
            foreach (IAuteur auteur in _biblio.Ecrivain)
            {
                comboBoxAuteur.Items.Add(auteur);

            }
            foreach (IEditeur edit in _biblio.Editeur)
            {
                comboBoxEditeur.Items.Add(edit);

            }
            foreach (IGenre genre in _biblio.Genre)
            {
                comboBoxGenre.Items.Add(genre);

            }

            modif = false;
            _ihm = ih;
            ih.Enabled = false;
        }

 protected void AdminDoctorView_RowUpdating(object sender, GridViewUpdateEventArgs e)
 {
     BusinessLayer businessLyerObj = new BusinessLayer();
     string connectString = ConfigurationManager.AppSettings.Get("connString");
     int index = e.RowIndex;
     businessLyerObj.ApproveDoctor(AdminDoctorView.DataKeys[index].Value.ToString(), connectString);
 }

        public static Kunde Create(string id , string deviceLanguage ,ref BusinessLayer.User user)
		{
			// das Objekt wird mit einem vorhandenen Kunden initializiert
			DataAccessLayer.DAL_Kunde dal_Kunde = new DataAccessLayer.DAL_Kunde ();
            var kunde = dal_Kunde.GetKunde (id, deviceLanguage, ref user);
			return kunde;

		} 

        public void SetControls(BusinessLayer.Artikel.StaffelPreis staffelPreis, string image)
        {

            MainImageView.Image = UIImage.FromFile(image);
            LblMengeEinheit.Text = staffelPreis.Menge + " " + staffelPreis.Einheit;
            LblDatum.Text = staffelPreis.Datum;
            LblPreis.Text = staffelPreis.Preis + " " + staffelPreis.Waehrung;
        }

        public static Interessent Create(string id , string deviceLanguage ,ref BusinessLayer.User user)
        {
            // das Objekt wird mit einem vorhandenen Interessenten initializiert
            DataAccessLayer.DAL_Interessent dal_Interessent = new DataAccessLayer.DAL_Interessent ();
            var interessent = dal_Interessent.GetInteressent (id, deviceLanguage, ref user);
            return interessent;

        } 

 protected void AdminUserView_RowDeleting(object sender, GridViewDeleteEventArgs e)
 {
     //Datakey is retrieved using AdminDoctorView.DataKeys[0].Value
     BusinessLayer businessLyerObj = new BusinessLayer();
     string connectString = ConfigurationManager.AppSettings.Get("connString");
     int index = e.RowIndex + (AdminUserView.PageIndex * 4);
     businessLyerObj.DeleteUser(AdminUserView.DataKeys[index].Value.ToString(), connectString);
 }

        public static async System.Threading.Tasks.Task<List<Kunde>>   GetKundenAsync( string Name ,  string deviceLanguage ,BusinessLayer.User User)
		{

			DataAccessLayer.DAL_Kunde dal_Kunde = new DataAccessLayer.DAL_Kunde ();
            var kunden =await dal_Kunde.GetKundenAsync (Name, deviceLanguage,  User);
			return kunden;

		}

		public void TVMainRowSelected(BusinessLayer.Person person)
		{
			// Fill the main Controls of the page
			_person = person;
			FillControls (person);
			// Fill the childview controls

		}

        public static Lieferant Create(string id , string deviceLanguage ,ref BusinessLayer.User user)
		{
			// das Objekt wird mit einem vorhandenen Lieferantn initializiert
			DataAccessLayer.DAL_Lieferant dal_Lieferant = new DataAccessLayer.DAL_Lieferant ();
			var lieferant = dal_Lieferant.GetLieferant (id, deviceLanguage, ref user);
			return lieferant;

		} 

		public void FillControls(BusinessLayer.Person person)
		{
			TxtMainName.Text = person.Name;
			TxtMainEmail.Text = person.Email;
			TxtMainTelefon.Text = person.Telefon;
			TxtMainUmsatz.Text = person.Umsatz;

		}