using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

/// <summary>
/// Summary description for SearchUtility
/// </summary>
public class SearchUtility
{
	public SearchUtility()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static DataSet GetSearchResults(string wordsToSearch)
    {
        string[] wordsSeperated = wordsToSearch.Split(' ');
        wordsToSearch = "";
        for (int i = 0; i < wordsSeperated.Length; i++)
        {
            if ((wordsSeperated[i] != "") && (wordsSeperated[i] != " "))
            {
                if (i == wordsSeperated.Length - 1)
                    wordsToSearch += wordsSeperated[i];
                else
                    wordsToSearch += wordsSeperated[i] + " ";
            }
        }
        wordsToSearch = wordsToSearch.Replace(" ", "%");
        wordsToSearch = wordsToSearch.Insert(0, "%");
        wordsToSearch = wordsToSearch.Insert(wordsToSearch.Length, "%");
        string select1 = "SELECT ID,en_title,en_abstract,en_path FROM news where en_abstract like '' + ? + ''";
        string select2 = "SELECT ID,en_title,en_abstract,en_path FROM publications where en_abstract like '' + ? + ''";
        string select3 = "SELECT ID,en_title,en_abstract as en_abstract,en_path FROM casestudies where en_abstract like '' + ? + ''";
        string select4 = "SELECT ID,en_title,en_abstarct as en_abstract,en_path FROM events where en_abstarct like '' + ? + ''";
        //string select5 = "SELECT rank as ID, characterization as en_abstract, vpath as en_path from scope() WHERE en_abstarct like '' + ? + ''";
        OleDbConnection con = new OleDbConnection(ConfigurationSettings.AppSettings["ConString"]);
        OleDbCommand com1 = new OleDbCommand(select1, con);
        com1.Parameters.Add("P1", OleDbType.VarChar).Value =  wordsToSearch;
        OleDbCommand com2 = new OleDbCommand(select2, con);
        com2.Parameters.Add("P2", OleDbType.VarChar).Value = wordsToSearch;
        OleDbCommand com3 = new OleDbCommand(select3, con);
        com3.Parameters.Add("P3", OleDbType.VarChar).Value = wordsToSearch;
        OleDbCommand com4 = new OleDbCommand(select4, con);
        com4.Parameters.Add("P4", OleDbType.VarChar).Value = wordsToSearch;
        OleDbDataAdapter ad1 = new OleDbDataAdapter(com1);
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        OleDbDataAdapter ad2 = new OleDbDataAdapter(com2);
        DataSet ds2 = new DataSet();
        ad2.Fill(ds2);
        OleDbDataAdapter ad3 = new OleDbDataAdapter(com3);
        DataSet ds3 = new DataSet();
        ad3.Fill(ds3);
        OleDbDataAdapter ad4 = new OleDbDataAdapter(com4);
        DataSet ds4 = new DataSet();
        ad4.Fill(ds4);
        
        //OleDbConnection odbSearch = new System.Data.OleDb.OleDbConnection();
        //odbSearch.ConnectionString = "Provider=\"MSIDXS\";Data Source=\"ECOindex\";";
        //OleDbCommand cmdSearch = new OleDbCommand(select5, odbSearch);
        //cmdSearch.Parameters.Add("P5", OleDbType.VarChar).Value = wordsToSearch;
        //OleDbDataAdapter ad5 = new OleDbDataAdapter(cmdSearch);
        //DataSet ds5 = new DataSet();
        //ad5.Fill(ds5);
        //odbSearch.Close(); 

        ds1.Merge(ds2);
        ds1.Merge(ds3);
        ds1.Merge(ds4);
        //ds1.Merge(ds5);

        return ds1;
    }
    public static DataSet GetSearchResultsForPartnersAndClients(string wordsToSearch)
    {
        string[] wordsSeperated = wordsToSearch.Split(' ');
        wordsToSearch = "";
        for (int i = 0; i < wordsSeperated.Length; i++)
        {
            if ((wordsSeperated[i] != "") && (wordsSeperated[i] != " "))
            {
                if (i == wordsSeperated.Length - 1)
                    wordsToSearch += wordsSeperated[i];
                else
                    wordsToSearch += wordsSeperated[i] + " ";
            }
        }
        wordsToSearch = wordsToSearch.Replace(" ", "%");
        wordsToSearch = wordsToSearch.Insert(0, "%");
        wordsToSearch = wordsToSearch.Insert(wordsToSearch.Length, "%");
        
        string select5 = "SELECT * FROM clients where en_brief like '' + ? + ''";
        string select6 = "SELECT * FROM EcoPartners where en_brief like '' + ? + ''";
        OleDbConnection con = new OleDbConnection(ConfigurationSettings.AppSettings["ConString"]);

        OleDbCommand com5 = new OleDbCommand(select5, con);
        com5.Parameters.Add("P5", OleDbType.VarChar).Value = wordsToSearch;
        OleDbCommand com6 = new OleDbCommand(select6, con);
        com6.Parameters.Add("P6", OleDbType.VarChar).Value = wordsToSearch;

        OleDbDataAdapter ad5 = new OleDbDataAdapter(com5);
        DataSet ds5 = new DataSet();
        ad5.Fill(ds5);

        OleDbDataAdapter ad6 = new OleDbDataAdapter(com6);
        DataSet ds6 = new DataSet();
        ad6.Fill(ds6);

        ds5.Merge(ds6);

        return ds5;

    }
    public static DataSet GetSearchResultsForArabic(string wordsToSearch)
    {
        string[] wordsSeperated = wordsToSearch.Split(' ');
        wordsToSearch = "";
        for (int i = 0; i < wordsSeperated.Length; i++)
        {
            if ((wordsSeperated[i] != "") && (wordsSeperated[i] != " "))
            {
                if (i == wordsSeperated.Length - 1)
                    wordsToSearch += wordsSeperated[i];
                else
                    wordsToSearch += wordsSeperated[i] + " ";
            }
        }
        wordsToSearch = wordsToSearch.Replace(" ", "%");
        wordsToSearch = wordsToSearch.Insert(0, "%");
        wordsToSearch = wordsToSearch.Insert(wordsToSearch.Length, "%");
        string select1 = "SELECT ID,ar_title,ar_abstract,ar_path FROM news where ar_abstract like '' + ? + ''";
        string select2 = "SELECT ID,ar_title,ar_abstract,ar_path FROM publications where ar_abstract like '' + ? + ''";
        string select3 = "SELECT ID,ar_title,ar_abstarct as ar_abstract,ar_path FROM casestudies where ar_abstarct like '' + ? + ''";
        string select4 = "SELECT ID,ar_title,ar_abstarct as ar_abstract,ar_path FROM events where ar_abstarct like '' + ? + ''";
        OleDbConnection con = new OleDbConnection(ConfigurationSettings.AppSettings["ConString"]);
        OleDbCommand com1 = new OleDbCommand(select1, con);
        com1.Parameters.Add("P1", OleDbType.VarChar).Value =  wordsToSearch;
        OleDbCommand com2 = new OleDbCommand(select2, con);
        com2.Parameters.Add("P2", OleDbType.VarChar).Value = wordsToSearch;
        OleDbCommand com3 = new OleDbCommand(select3, con);
        com3.Parameters.Add("P3", OleDbType.VarChar).Value = wordsToSearch;
        OleDbCommand com4 = new OleDbCommand(select4, con);
        com4.Parameters.Add("P4", OleDbType.VarChar).Value = wordsToSearch;
        OleDbDataAdapter ad1 = new OleDbDataAdapter(com1);
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        OleDbDataAdapter ad2 = new OleDbDataAdapter(com2);
        DataSet ds2 = new DataSet();
        ad2.Fill(ds2);
        OleDbDataAdapter ad3 = new OleDbDataAdapter(com3);
        DataSet ds3 = new DataSet();
        ad3.Fill(ds3);
        OleDbDataAdapter ad4 = new OleDbDataAdapter(com4);
        DataSet ds4 = new DataSet();
        ad4.Fill(ds4);

        ds1.Merge(ds2);
        ds1.Merge(ds3);
        ds1.Merge(ds4);

        return ds1;
    }
    public static DataSet GetSearchResultsForPartnersAndClientsForArabic(string wordsToSearch)
    {
        string[] wordsSeperated = wordsToSearch.Split(' ');
        wordsToSearch = "";
        for (int i = 0; i < wordsSeperated.Length; i++)
        {
            if ((wordsSeperated[i] != "") && (wordsSeperated[i] != " "))
            {
                if (i == wordsSeperated.Length - 1)
                    wordsToSearch += wordsSeperated[i];
                else
                    wordsToSearch += wordsSeperated[i] + " ";
            }
        }
        wordsToSearch = wordsToSearch.Replace(" ", "%");
        wordsToSearch = wordsToSearch.Insert(0, "%");
        wordsToSearch = wordsToSearch.Insert(wordsToSearch.Length, "%");

        string select5 = "SELECT * FROM clients where ar_brief like '' + ? + ''";
        string select6 = "SELECT * FROM EcoPartners where ar_brief like '' + ? + ''";
        OleDbConnection con = new OleDbConnection(ConfigurationSettings.AppSettings["ConString"]);

        OleDbCommand com5 = new OleDbCommand(select5, con);
        com5.Parameters.Add("P5", OleDbType.VarChar).Value = wordsToSearch;
        OleDbCommand com6 = new OleDbCommand(select6, con);
        com6.Parameters.Add("P6", OleDbType.VarChar).Value = wordsToSearch;

        OleDbDataAdapter ad5 = new OleDbDataAdapter(com5);
        DataSet ds5 = new DataSet();
        ad5.Fill(ds5);

        OleDbDataAdapter ad6 = new OleDbDataAdapter(com6);
        DataSet ds6 = new DataSet();
        ad6.Fill(ds6);

        ds5.Merge(ds6);

        return ds5;

    }


    public static DataSet GetHomeDetails()
    {
        OleDbConnection con = new OleDbConnection(ConfigurationSettings.AppSettings["ConString"]);

        try
        {
            /*
            //News
            string selectNews = "SELECT ID,en_title,en_abstract,en_path,img FROM news where inHome = yes";
            OleDbCommand com1 = new OleDbCommand(selectNews, con);
            OleDbDataAdapter ad1 = new OleDbDataAdapter(com1);
            DataSet ds1 = new DataSet();
            ad1.Fill(ds1);
            //publications
            string selectPublications = "SELECT ID,en_title,en_abstract,en_path,img FROM publications where inHome = yes";
            OleDbCommand com2 = new OleDbCommand(selectPublications, con);
            OleDbDataAdapter ad2 = new OleDbDataAdapter(com2);
            DataSet ds2 = new DataSet();
            ad2.Fill(ds2);
            //Events
            string selectEvents = "SELECT ID,en_title,en_abstarct as en_abstract,en_path,img FROM events where inHome = yes";
            OleDbCommand com3 = new OleDbCommand(selectEvents, con);
            OleDbDataAdapter ad3 = new OleDbDataAdapter(com3);
            DataSet ds3 = new DataSet();
            ad3.Fill(ds3);
            //Merging
            ds1.Merge(ds2);
            ds1.Merge(ds3);
            return ds1;
             * */
            string selectNews = @"SELECT ID,en_title,en_abstract,en_path,img,ar_title,ar_abstract,ar_path FROM news where inHome = true 
                                union
                                  SELECT ID,en_title,en_abstract,en_path,img,ar_title,ar_abstract,ar_path FROM publications where inHome = true
                                union
                                  SELECT ID,en_title,en_abstarct as en_abstract,en_path,img,ar_title,ar_abstarct as ar_abstract,ar_path FROM events where inHome = true
                                ";
            OleDbCommand com1 = new OleDbCommand(selectNews, con);
            OleDbDataAdapter ad1 = new OleDbDataAdapter(com1);
            DataSet ds1 = new DataSet();
            ad1.Fill(ds1);
            return ds1;
        }
        catch (Exception ex)
        {
            throw ex;
        }


    }
}