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.SqlClient; 
using System.Collections; 

namespace infoExpediters
{
    public class AddCompany
    {
        // Get all the states from database
        public DataSet GetStates()
        {
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                DataSet ds = new DataSet();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "Select CodeType, CodeName,CodeValue From tblCodes where  CodeType='StateType'";
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    da.Fill(ds, "Codes");
                    
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        // Get all the regions from database
        public DataSet GetRegions()
        {
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                DataSet ds = new DataSet();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "Select CodeType, CodeName,CodeValue From tblCodes where  CodeType='RegionType'";
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    da.Fill(ds, "Codes");                   
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        // Get all the states from database
        public DataSet GetPaperStates()
        {
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                DataSet ds = new DataSet();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "Select CodeType, CodeName,CodeValue From tblCodes where  CodeType='StateType'";
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    da.Fill(ds, "Codes");
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        // Get all the states from database
        public DataSet GetPartStates()
        {
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                DataSet ds = new DataSet();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "Select CodeType, CodeName,CodeValue From tblCodes where  CodeType='StateType'";
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    da.Fill(ds, "Codes");                  
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        // Get all Time zones from database
        public DataSet GetTimeZone()
        {
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                DataSet ds = new DataSet();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "Select CodeType, CodeName,CodeValue From tblCodes where  CodeType='TimeZoneType'";
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    da.Fill(ds, "Codes");
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        //Save company data into database
        public Hashtable InsertData(Hashtable ht)
        {            
            Hashtable ht1 = new Hashtable(); 
            try
            {
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Insert_Update_CompanyDetails", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@companyname", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@tag", SqlDbType.VarChar, 500));
                cmd.Parameters.Add(new SqlParameter("@parentcompanyid", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@address1", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@address2", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@statetype", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@zip", SqlDbType.VarChar,10));
                cmd.Parameters.Add(new SqlParameter("@paperaddress1", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@paperaddress2", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@papercity", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@paperstatetype", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@paperzip", SqlDbType.VarChar,10));
                cmd.Parameters.Add(new SqlParameter("@partsaddress1", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@partsaddress2", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@partscity", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@partsstatetype", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@partszip", SqlDbType.VarChar,10));
                cmd.Parameters.Add(new SqlParameter("@shippingcutofftime", SqlDbType.VarChar));
                cmd.Parameters.Add(new SqlParameter("@timezonecode", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@companyphonearea", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyphonepre", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyphonesuffix", SqlDbType.VarChar,6));
                cmd.Parameters.Add(new SqlParameter("@companyfaxarea", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyfaxpre", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyfaxsuffix", SqlDbType.VarChar,6));
                cmd.Parameters.Add(new SqlParameter("@companyTollarea", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyTollpre", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyTollsuffix", SqlDbType.VarChar,6));
                cmd.Parameters.Add(new SqlParameter("@companyTecharea", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyTechpre", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyTechsuffix", SqlDbType.VarChar,6));
                cmd.Parameters.Add(new SqlParameter("@companyCSarea", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyCSpre", SqlDbType.Char,3));
                cmd.Parameters.Add(new SqlParameter("@companyCSsuffix", SqlDbType.VarChar,6));
                cmd.Parameters.Add(new SqlParameter("@webSite", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@emailId", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@pricelist", SqlDbType.VarChar, 200));
                cmd.Parameters.Add(new SqlParameter("@keywords", SqlDbType.VarChar, 500));
                cmd.Parameters.Add(new SqlParameter("@pricelistdate", SqlDbType.SmallDateTime));
                cmd.Parameters.Add(new SqlParameter("@logoUrl", SqlDbType.VarChar, 250));
                cmd.Parameters.Add(new SqlParameter("@string", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@Error", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@CompId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@CompanyIdValue", SqlDbType.Int));      

                cmd.Parameters["@companyname"].Value = ht["@companyname"].ToString();
                cmd.Parameters["@tag"].Value = ht["@tag"].ToString();
                cmd.Parameters["@parentcompanyid"].Value = ht["@parentcompanyid"].ToString() != "" ? ht["@parentcompanyid"].ToString() : Convert.DBNull;
                cmd.Parameters["@address1"].Value = ht["@address1"].ToString();
                cmd.Parameters["@address2"].Value = ht["@address2"].ToString();
                cmd.Parameters["@city"].Value = ht["@city"].ToString();
                cmd.Parameters["@statetype"].Value = ht["@statetype"].ToString(); ;
                cmd.Parameters["@zip"].Value = ht["@zip"].ToString() != "" ? ht["@zip"].ToString() : Convert.DBNull;
                cmd.Parameters["@paperaddress1"].Value = ht["@paperaddress1"].ToString();
                cmd.Parameters["@paperaddress2"].Value = ht["@paperaddress2"].ToString();
                cmd.Parameters["@papercity"].Value = ht["@papercity"].ToString();
                cmd.Parameters["@paperstatetype"].Value = ht["@paperstatetype"].ToString();
                cmd.Parameters["@paperzip"].Value = ht["@paperzip"].ToString() != "" ? ht["@paperzip"].ToString() : Convert.DBNull;
                cmd.Parameters["@partsaddress1"].Value = ht["@partsaddress1"].ToString();
                cmd.Parameters["@partsaddress2"].Value = ht["@partsaddress2"].ToString();
                cmd.Parameters["@partscity"].Value = ht["@partscity"].ToString();
                cmd.Parameters["@partsstatetype"].Value = ht["@partsstatetype"].ToString();
                cmd.Parameters["@partszip"].Value = ht["@partszip"].ToString() != "" ? ht["@partszip"].ToString() : Convert.DBNull;
                cmd.Parameters["@shippingcutofftime"].Value = ht["@shippingcutofftime"].ToString();
                cmd.Parameters["@timezonecode"].Value = ht["@timezonecode"].ToString() != "" ? ht["@timezonecode"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyphonearea"].Value = ht["@companyphonearea"].ToString() != "" ? ht["@companyphonearea"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyphonepre"].Value = ht["@companyphonepre"].ToString() != "" ? ht["@companyphonepre"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyphonesuffix"].Value = ht["@companyphonesuffix"].ToString() != "" ? ht["@companyphonesuffix"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyfaxarea"].Value = ht["@companyfaxarea"].ToString() != "" ? ht["@companyfaxarea"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyfaxpre"].Value = ht["@companyfaxpre"].ToString() != "" ? ht["@companyfaxpre"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyfaxsuffix"].Value = ht["@companyfaxsuffix"].ToString() != "" ? ht["@companyfaxsuffix"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyTollarea"].Value = ht["@companyTollarea"].ToString() != "" ? ht["@companyTollarea"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyTollpre"].Value = ht["@companyTollpre"].ToString() != "" ? ht["@companyTollpre"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyTollsuffix"].Value = ht["@companyTollsuffix"].ToString() != "" ? ht["@companyTollsuffix"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyTecharea"].Value = ht["@companyTecharea"].ToString() != "" ? ht["@companyTecharea"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyTechpre"].Value = ht["@companyTechpre"].ToString() != "" ? ht["@companyTechpre"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyTechsuffix"].Value = ht["@companyTechsuffix"].ToString() != "" ? ht["@companyTechsuffix"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyCSarea"].Value = ht["@companyCSarea"].ToString() != "" ? ht["@companyCSarea"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyCSpre"].Value = ht["@companyCSpre"].ToString() != "" ? ht["@companyCSpre"].ToString() : Convert.DBNull;
                cmd.Parameters["@companyCSsuffix"].Value = ht["@companyCSsuffix"].ToString() != "" ? ht["@companyCSsuffix"].ToString() : Convert.DBNull;
                cmd.Parameters["@webSite"].Value = ht["@webSite"].ToString();
                cmd.Parameters["@emailId"].Value = ht["@emailId"].ToString();
                cmd.Parameters["@pricelist"].Value = ht["@pricelist"].ToString();
                cmd.Parameters["@keywords"].Value = ht["@keywords"].ToString();
                cmd.Parameters["@logoUrl"].Value = ht["@logoUrl"].ToString();
                cmd.Parameters["@string"].Value = ht["@string"].ToString();//ddlRegions.SelectedItem.Value.ToString(); //txtRegion.Text;    //ddlRegions.SelectedItem.Value.ToStri0ng(); //"1,2";//
                cmd.Parameters["@Error"].Value = 0;
                cmd.Parameters["@Status"].Value = ht["@Status"].ToString() != "" ? ht["@Status"].ToString() : Convert.DBNull;
                cmd.Parameters["@CompId"].Value = ht["@CompanyId"].ToString();
                cmd.Parameters["@Error"].Direction = ParameterDirection.Output;
                cmd.Parameters["@CompanyIdValue"].Value = 0;
                cmd.Parameters["@CompanyIdValue"].Direction = ParameterDirection.Output;   
                string date = ht["@pricelistdate"].ToString();
                DateTime dt;
                if (date.Trim().Length > 0)
                {
                    dt = DateTime.Parse(date.ToString());
                    cmd.Parameters["@pricelistdate"].Value = dt.Date;
                }
                else
                {
                    cmd.Parameters["@pricelistdate"].Value = ht["@pricelistdate"].ToString() != "" ? ht["@pricelistdate"].ToString() : Convert.DBNull;
                }
                int insertflag = cmd.ExecuteNonQuery();
                ht1["status"] = cmd.Parameters["@Error"].Value.ToString();
                ht1["companyid"] = cmd.Parameters["@CompanyIdValue"].Value.ToString();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
             }
          }
          catch(Exception ex)
          {
             string name = ex.Message;
          }
          return ht1;
      }
        // Get company details to display in edit company details pages
        public DataSet fillvalues(int cid)
        {
            DataSet ds = new DataSet();
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    SqlCommand sqlcmd = new SqlCommand("getvalues", conn);

                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Parameters.Add("@CompId", cid);
                    sqlcmd.ExecuteNonQuery();
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = sqlcmd;
                    da.Fill(ds, "values");
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                
            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }
            return ds;
        }
        //Get all company details to display in parent company company drop down list in add/edit company pages
        public DataSet GetCompanyNames()
        {
            try
            {
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
                DataSet ds = new DataSet();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "select CompanyId,ParentCompanyId,CompanyName from tblCompany";
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    da.Fill(ds, "Company");                    
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        // Deletes company logo detail in database
        public void Delete(int CompId)
        {
            try
            { 
                String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];

                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    string strSql = "Update tblCompany set LogoUrl='NULL' where CompanyId='"+CompId+"'";
                    SqlCommand sqlcmd=new SqlCommand(strSql,conn);
                    sqlcmd.ExecuteNonQuery();
                }
            }
              catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }           
        }
    }   
}