using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


namespace infoExpediters
{
    public class Search
    {
        // gets all company details which matched with search criteria
       public DataSet BindSearch(int index, int pagesize, string sfield, string ffield, string wfield, string sortfield,string sortdatatype, string comintfield, string comdatefield, string comvarfield)
        {
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            DataSet ds = new DataSet();
             try
            {
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand cmd;
                SqlDataAdapter daObj = new SqlDataAdapter();
                conn.Open();

                cmd = new SqlCommand("dbo.sp_SearchDetails", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@sortField", SqlDbType.VarChar);
                cmd.Parameters.Add("@pagenum", SqlDbType.Int);
                cmd.Parameters.Add("@perpage", SqlDbType.Int);
                cmd.Parameters.Add("@selectField", SqlDbType.VarChar);
                cmd.Parameters.Add("@fromField", SqlDbType.VarChar);
                cmd.Parameters.Add("@whereField", SqlDbType.VarChar);
                cmd.Parameters.Add("@SortFieldData", SqlDbType.VarChar);
                cmd.Parameters.Add("@Compareint", SqlDbType.VarChar);
                cmd.Parameters.Add("@Comparedatetime", SqlDbType.VarChar);
                cmd.Parameters.Add("@CompareVarchar", SqlDbType.VarChar);

                daObj.SelectCommand = cmd;
                daObj.SelectCommand.Parameters["@sortField"].Value = sortfield;
                daObj.SelectCommand.Parameters["@pagenum"].Value = index + 1;
                daObj.SelectCommand.Parameters["@perpage"].Value = pagesize;
                daObj.SelectCommand.Parameters["@selectField"].Value = sfield;
                daObj.SelectCommand.Parameters["@fromField"].Value = ffield;
                daObj.SelectCommand.Parameters["@whereField"].Value = wfield;
                daObj.SelectCommand.Parameters["@SortFieldData"].Value = sortdatatype;
                daObj.SelectCommand.Parameters["@Compareint"].Value = comintfield;
                daObj.SelectCommand.Parameters["@Comparedatetime"].Value = comdatefield;
                daObj.SelectCommand.Parameters["@CompareVarchar"].Value = comvarfield;

                //Pass index and page size to get the required set of records into dataset
                daObj.Fill(ds);


                //GET the total number of records
                if (ds.Tables.Count > 1)
                {
                    int TotalRecords = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                }

                daObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return ds;
               
        }
        catch (Exception ex)
        {
            throw new ArgumentException(ex.Message);
        }
       }
        //Deletes a company
        public DataSet DeleteRecord(int CompId)
        {
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    SqlCommand cmd;
                    SqlDataAdapter daObj = new SqlDataAdapter();
                    conn.Open();
                    cmd = new SqlCommand("dbo.DeleteCompanyDetails", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@companyid", SqlDbType.VarChar);
                    cmd.Parameters["@companyid"].Value = CompId;
                    daObj.SelectCommand = cmd;
                    daObj.Fill(ds);
                    daObj.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
        }
    }
}