/// <summary>
    /// Get all potential user
    /// </summary>
    public void DataBind()
    {
        //var query = GetPotentialUsers.PotentialUsers.OrderBy(a => a.FirstName).Where(x=>x.Deleted==null||x.Deleted==false);
        var query = PotentialUserComponent.GetPotentialUsers().OrderBy(x => x.FirstName).Where(x => x.Deleted == null || x.Deleted == false);

        grdPreUsers.DataSource = query;
    }
    public void SendEmail(int ExistUser, string Email)
    {
        string MessageTitle = string.Empty;
        string MessageBody  = string.Empty;

        if (ExistUser == 1)
        {
            MessageTitle = "MessageTitle";
            MessageBody  = "MessageBody";
        }
        else
        {
            MessageTitle = "MessageTitleUpdateUser";
            MessageBody  = "MessageBodyUpdateUser";
        }
        try
        {
            potentialUserComponent = new PotentialUserComponent(Email);
            DotNetNuke.Services.Mail.Mail.SendEmail("*****@*****.**",
                                                    Email,
                                                    string.Format(
                                                        Localization.GetString(MessageTitle,
                                                                               LocalResourceFile),
                                                        potentialUserComponent.PotentialUser.LastName + " " + potentialUserComponent.PotentialUser.FirstName),
                                                    string.Format(
                                                        Localization.GetString(MessageBody,
                                                                               LocalResourceFile),
                                                        potentialUserComponent.PotentialUser.LastName + " " + potentialUserComponent.PotentialUser.FirstName
                                                        ));
        }
        catch
        {
            throw;
        }
    }
    /// <summary>
    /// Delete selected user (delete button) in controls
    /// </summary>
    /// <param name="source"></param>
    /// <param name="e"></param>
    protected void RadGrid1_DeleteCommand(object source, GridCommandEventArgs e)
    {
        string PotentialUserId = e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["PotentialUserId"].ToString();

        potentialUserComponent = new PotentialUserComponent(new Guid(PotentialUserId));
        potentialUserComponent.PotentialUser.Deleted = true;
        potentialUserComponent.Save();
        DataBind();
    }
    /// <summary>
    /// Update information of the Potential user
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void RadGrid1_UpdateCommand(object sender, GridCommandEventArgs e)
    {
        if (e.CommandName == RadGrid.UpdateCommandName)
        {
            if (e.Item is GridEditableItem)
            {
                GridEditableItem editItem = (GridEditableItem)e.Item;
                RadTextBox       txtEmail = (RadTextBox)editItem.FindControl("txtEmail");
                if (txtEmail.Text != string.Empty)
                {
                    //get information from the controls
                    TextBox     txtPotentialUserId     = (TextBox)editItem.FindControl("txtPotentialUserId");
                    RadTextBox  txtFirstName           = (RadTextBox)editItem.FindControl("txtFirstName");
                    RadTextBox  txtLastName            = (RadTextBox)editItem.FindControl("txtLastName");
                    RadTextBox  txtOrganizationName    = (RadTextBox)editItem.FindControl("txtOrganizationName");
                    RadTextBox  txtPhone               = (RadTextBox)editItem.FindControl("txtPhone");
                    RadTextBox  txtAddress             = (RadTextBox)editItem.FindControl("txtAddress");
                    RadTextBox  txtMiddleName          = (RadTextBox)editItem.FindControl("txtMiddleName");
                    RadTextBox  txtQualification       = (RadTextBox)editItem.FindControl("txtQualification");
                    RadTextBox  txtSource              = (RadTextBox)editItem.FindControl("txtSource");
                    RadTextBox  txtSector              = (RadTextBox)editItem.FindControl("txtSector");
                    RadTextBox  txtBatch               = (RadTextBox)editItem.FindControl("txtBatch");
                    RadTextBox  txtTitle               = (RadTextBox)editItem.FindControl("txtTitle");
                    RadTextBox  txtZipCode             = (RadTextBox)editItem.FindControl("txtZipCode");
                    RadTextBox  txtWebSite             = (RadTextBox)editItem.FindControl("txtWebSite");
                    RadTextBox  txtLinkedIn            = (RadTextBox)editItem.FindControl("txtLinkedIn");
                    RadTextBox  txtGooglePlus          = (RadTextBox)editItem.FindControl("txtGooglePlus");
                    RadTextBox  txtTwitter             = (RadTextBox)editItem.FindControl("txtTwitter");
                    RadTextBox  txtFacebook            = (RadTextBox)editItem.FindControl("txtFacebook");
                    RadTextBox  txtSkype               = (RadTextBox)editItem.FindControl("txtSkype");
                    RadComboBox ddLanguage             = (RadComboBox)editItem.FindControl("ddLanguage");
                    RadComboBox ddOrganizationType     = (RadComboBox)editItem.FindControl("ddOrganizationType");
                    RadTextBox  txtNewOrganizationType = (RadTextBox)editItem.FindControl("txtNewOrganizationType");
                    RadComboBox ddCountry              = (RadComboBox)editItem.FindControl("ddCountry");

                    bool sw = false;
                    potentialUserComponent = new PotentialUserComponent(txtEmail.Text);

                    //If is new potential user
                    if (potentialUserComponent.PotentialUser.PotentialUserId == Guid.Empty)
                    {
                        potentialUserComponent.PotentialUser.Created = DateTime.Now;
                        potentialUserComponent.PotentialUser.Updated = potentialUserComponent.PotentialUser.Created;

                        sw = true;

                        ExistEmail = false;
                    }
                    else
                    {
                        if (txtPotentialUserId.Text != string.Empty)
                        {
                            if (potentialUserComponent.PotentialUser.PotentialUserId == (new Guid(txtPotentialUserId.Text)))
                            {
                                //update Date
                                potentialUserComponent.PotentialUser.Updated = DateTime.Now;

                                sw = true;

                                ExistEmail = false;
                            }
                            else
                            {
                                ExistEmail = true;
                            }
                        }
                        else
                        {
                            ExistEmail = true;
                        }
                    }

                    //Update information for the existing user
                    if (sw == true)
                    {
                        potentialUserComponent.PotentialUser.Email            = txtEmail.Text;
                        potentialUserComponent.PotentialUser.FirstName        = txtFirstName.Text;
                        potentialUserComponent.PotentialUser.LastName         = txtLastName.Text;
                        potentialUserComponent.PotentialUser.OrganizationName = txtOrganizationName.Text;
                        potentialUserComponent.PotentialUser.Phone            = txtPhone.Text;
                        potentialUserComponent.PotentialUser.Address          = txtAddress.Text;
                        potentialUserComponent.PotentialUser.MiddleName       = txtMiddleName.Text;
                        potentialUserComponent.PotentialUser.Qualification    = txtQualification.Text;
                        potentialUserComponent.PotentialUser.Source           = txtSource.Text;
                        potentialUserComponent.PotentialUser.Sector           = txtSector.Text;
                        potentialUserComponent.PotentialUser.Batch            = txtBatch.Text;
                        potentialUserComponent.PotentialUser.Title            = txtTitle.Text;
                        potentialUserComponent.PotentialUser.ZipCode          = txtZipCode.Text;
                        potentialUserComponent.PotentialUser.WebSite          = txtWebSite.Text;
                        potentialUserComponent.PotentialUser.LinkedIn         = txtLinkedIn.Text;
                        potentialUserComponent.PotentialUser.GooglePlus       = txtGooglePlus.Text;
                        potentialUserComponent.PotentialUser.Twitter          = txtTwitter.Text;
                        potentialUserComponent.PotentialUser.Facebook         = txtFacebook.Text;
                        potentialUserComponent.PotentialUser.Skype            = txtSkype.Text;
                        potentialUserComponent.PotentialUser.Language         = ddLanguage.SelectedValue;
                        potentialUserComponent.PotentialUser.Country          = ddCountry.SelectedValue;

                        string newOrgType = Localization.GetString("NewOrganizationType", this.LocalResourceFile).ToString();
                        if (ddOrganizationType.SelectedValue == newOrgType)
                        {
                            if (txtNewOrganizationType.Text != string.Empty)
                            {
                                potentialUserComponent.PotentialUser.OrganizationType = txtNewOrganizationType.Text;
                            }
                        }
                        else
                        {
                            potentialUserComponent.PotentialUser.OrganizationType = ddOrganizationType.SelectedValue;
                        }

                        potentialUserComponent.Save();
                        if (editItem.ItemIndex != -1)
                        {
                            this.grdPreUsers.MasterTableView.Items[editItem.ItemIndex].Edit = false;
                        }
                        else
                        {
                            this.grdPreUsers.MasterTableView.Rebind();
                        }
                        e.Item.OwnerTableView.IsItemInserted = false;
                    }
                }
            }
        }
    }
    /// <summary>
    /// Upload Excel file
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void aUploadFile_FileUploaded(object sender, Telerik.Web.UI.FileUploadedEventArgs e)
    {
        try
        {
            int counter        = 0;
            int counterSuccess = 0;
            int counterRepeat  = 0;

            if (aUploadFile.UploadedFiles.Count > 0)
            {
                string FileName      = Path.GetFileName(aUploadFile.UploadedFiles[0].FileName);
                string extensionName = Path.GetExtension(aUploadFile.UploadedFiles[0].FileName);
                FileName = Guid.NewGuid().ToString();
                aUploadFile.UploadedFiles[0].SaveAs(
                    Server.MapPath(PortalSettings.HomeDirectory + "tmpfiles/" + FileName + extensionName));


                string connString  = "";
                string strFileType = extensionName.ToLower();
                string path        = Server.MapPath(PortalSettings.HomeDirectory + "tmpfiles/" + FileName + extensionName);
                //Connection String to Excel Workbook
                if (strFileType.Trim() == ".xls")
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
                                 ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (strFileType.Trim() == ".xlsx")
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
                                 ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }
                string          query = "SELECT *  FROM [Sheet1$]";
                OleDbConnection conn  = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                OleDbCommand     cmd = new OleDbCommand(query, conn);
                OleDbDataAdapter da  = new OleDbDataAdapter(cmd);


                DataSet ds = new DataSet();


                da.Fill(ds);

                // List<PotentialUser> potentialUsers=new List<PotentialUser>();
                PotentialUserComponent tmpPotentialUser;
                long batch = DateTime.Now.Ticks;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    counter++;
                    if (ds.Tables[0].Columns.Contains("Email"))
                    {
                        if (!string.IsNullOrEmpty(dr["Email"].ToString().Replace(" ", "")))
                        {
                            tmpPotentialUser = new PotentialUserComponent(dr["Email"].ToString().Replace(" ", ""));
                            if (tmpPotentialUser.PotentialUser.PotentialUserId == Guid.Empty)
                            {
                                tmpPotentialUser.PotentialUser.Created = DateTime.Now;
                                tmpPotentialUser.PotentialUser.Updated = tmpPotentialUser.PotentialUser.Created;
                                tmpPotentialUser.PotentialUser.Deleted = false;
                            }
                            tmpPotentialUser.PotentialUser.Updated = DateTime.Now;
                            tmpPotentialUser.PotentialUser.Batch   = batch.ToString();

                            if (ds.Tables[0].Columns.Contains("FirstName"))
                            {
                                tmpPotentialUser.PotentialUser.FirstName = dr["FirstName"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("LastName"))
                            {
                                tmpPotentialUser.PotentialUser.LastName = dr["LastName"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("MiddleName"))
                            {
                                tmpPotentialUser.PotentialUser.MiddleName = dr["MiddleName"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Phone"))
                            {
                                tmpPotentialUser.PotentialUser.Phone = dr["Phone"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Address"))
                            {
                                tmpPotentialUser.PotentialUser.Address = dr["Address"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Country"))
                            {
                                tmpPotentialUser.PotentialUser.Country = dr["Country"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Region"))
                            {
                                tmpPotentialUser.PotentialUser.Region = dr["Region"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("City"))
                            {
                                tmpPotentialUser.PotentialUser.City = dr["City"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Language"))
                            {
                                tmpPotentialUser.PotentialUser.Language = dr["Language"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("OrganizationName"))
                            {
                                tmpPotentialUser.PotentialUser.OrganizationName = dr["OrganizationName"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("OrganizationType"))
                            {
                                tmpPotentialUser.PotentialUser.OrganizationType = dr["OrganizationType"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Qualification"))
                            {
                                tmpPotentialUser.PotentialUser.Qualification = dr["Qualification"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Source"))
                            {
                                tmpPotentialUser.PotentialUser.Source = dr["Source"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Source"))
                            {
                                tmpPotentialUser.PotentialUser.Source = dr["Source"].ToString();
                            }
                            try
                            {
                                if (ds.Tables[0].Columns.Contains("Latitude"))
                                {
                                    tmpPotentialUser.PotentialUser.Latitude = Convert.ToDecimal(dr["Latitude"]);
                                }
                                if (ds.Tables[0].Columns.Contains("Longitude"))
                                {
                                    tmpPotentialUser.PotentialUser.Longitude = Convert.ToDecimal(dr["Longitude"]);
                                }
                            }

                            catch (Exception)
                            {
                            }
                            if (ds.Tables[0].Columns.Contains("CustomField1"))
                            {
                                tmpPotentialUser.PotentialUser.CustomField1 = dr["CustomField1"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("CustomField2"))
                            {
                                tmpPotentialUser.PotentialUser.CustomField2 = dr["CustomField2"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Title"))
                            {
                                tmpPotentialUser.PotentialUser.Title = dr["Title"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("ZipCode"))
                            {
                                tmpPotentialUser.PotentialUser.ZipCode = dr["ZipCode"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("WebSite"))
                            {
                                tmpPotentialUser.PotentialUser.WebSite = dr["WebSite"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("LinkedIn"))
                            {
                                tmpPotentialUser.PotentialUser.LinkedIn = dr["LinkedIn"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("GooglePlus"))
                            {
                                tmpPotentialUser.PotentialUser.GooglePlus = dr["GooglePlus"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Twitter"))
                            {
                                tmpPotentialUser.PotentialUser.Twitter = dr["Twitter"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Facebook"))
                            {
                                tmpPotentialUser.PotentialUser.Facebook = dr["Facebook"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Skype"))
                            {
                                tmpPotentialUser.PotentialUser.Skype = dr["Skype"].ToString();
                            }
                            if (ds.Tables[0].Columns.Contains("Sector"))
                            {
                                tmpPotentialUser.PotentialUser.Sector = dr["Sector"].ToString();
                            }
                            if (tmpPotentialUser.PotentialUser.PotentialUserId != Guid.Empty)
                            {
                                counterRepeat++;
                                if (chkUpdate.Checked)
                                {
                                    counterSuccess += tmpPotentialUser.Save();
                                }
                            }
                            else
                            {
                                counterSuccess += tmpPotentialUser.Save();
                            }
                        }
                    }
                }

                MIFNEXSOEntities nx = new MIFNEXSOEntities();
                grdPreUsers.DataSource = nx.PotentialUsers;
                grdPreUsers.DataBind();
                da.Dispose();
                conn.Close();
                conn.Dispose();
            }
            lblResult.Text = String.Format("Uploading contact done Succesfutl ({0}), Repeats ({1}) Errors ({2})", counterSuccess, counterRepeat,
                                           counter);
        }
        catch (Exception ee)
        {
            lblResult.Text = "Exception: " + ee.ToString();
        }
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        int ExistUser = 0;

        potentialUserComponent = new PotentialUserComponent(txtEmail.Text);
        if (potentialUserComponent.PotentialUser.PotentialUserId == Guid.Empty)
        {
            ExistUser = 1;
            potentialUserComponent.PotentialUser.Deleted = false;
            potentialUserComponent.PotentialUser.Created = DateTime.Now;
            potentialUserComponent.PotentialUser.Updated = potentialUserComponent.PotentialUser.Created;
        }
        else
        {
            potentialUserComponent.PotentialUser.Updated = DateTime.Now;
            ExistUser = 2;
        }
        potentialUserComponent.PotentialUser.Email            = txtEmail.Text;
        potentialUserComponent.PotentialUser.FirstName        = txtFirstName.Text;
        potentialUserComponent.PotentialUser.LastName         = txtLastName.Text;
        potentialUserComponent.PotentialUser.Address          = txtAddress.Text;
        potentialUserComponent.PotentialUser.Phone            = txtPhone.Text;
        potentialUserComponent.PotentialUser.OrganizationName = txtOrganizationName.Text;
        potentialUserComponent.PotentialUser.Country          = ddCountry.SelectedValue;
        potentialUserComponent.PotentialUser.Region           = ddRegion.SelectedValue;
        potentialUserComponent.PotentialUser.City             = ddCities.SelectedValue;
        var lan = "EN";

        if (Language == "pt-BR")
        {
            lan = "PT";
        }
        if (Language == "es-ES")
        {
            lan = "ES";
        }
        potentialUserComponent.PotentialUser.Source     = "VPC2015FORM_" + lan;
        potentialUserComponent.PotentialUser.Title      = txtTitle.Text;
        potentialUserComponent.PotentialUser.WebSite    = txtWebSite.Text;
        potentialUserComponent.PotentialUser.LinkedIn   = txtLinkedIn.Text;
        potentialUserComponent.PotentialUser.GooglePlus = txtGooglePlus.Text;
        potentialUserComponent.PotentialUser.Twitter    = txtTwitter.Text;
        potentialUserComponent.PotentialUser.Facebook   = txtFacebook.Text;
        potentialUserComponent.PotentialUser.Skype      = txtSkype.Text;
        if (txtOther.Text != string.Empty)
        {
            potentialUserComponent.PotentialUser.Sector = txtOther.Text;
        }
        else
        {
            if (!string.IsNullOrEmpty(ddSectorItems.SelectedValue))
            {
                potentialUserComponent.PotentialUser.Sector = ddSectorItems.SelectedValue;
            }
            else
            {
                potentialUserComponent.PotentialUser.Sector = ddSectorJPO.SelectedValue;
            }
        }
        if (potentialUserComponent.Save() > 0)
        {
            pnlSubscription.Visible = false;
            pnlMessage.Visible      = true;
        }
        //SendEmail(ExistUser, txtEmail.Text);
    }