public static class SessionWrapper
    {

        //---------------------------------------------------------------

        public static PermissionSession PermissionSession
        {
            get { return GetFromSession<PermissionSession>("PermissionSession"); }
            set { SetInSession<PermissionSession>("PermissionSession", value); }
        }

        private static T GetFromSession<T>(string key)
        {
            object obj = HttpContext.Current.Session[key];
            if (obj == null)
            {
                return default(T);
            }
            return (T)obj;
        }
        private static void SetInSession<T>(string key, T value)
        {
            if (value == null)
            {
                HttpContext.Current.Session.Remove(key);
            }
            else
            {
                HttpContext.Current.Session[key] = value;
            }
        }
        private static T GetFromApplication<T>(string key)
        {
            return (T)HttpContext.Current.Application[key];
        }
        private static void SetInApplication<T>(string key, T value)
        {
            if (value == null)
            {
                HttpContext.Current.Application.Remove(key);
            }
            else
            {
                HttpContext.Current.Application[key] = value;
            }
        }

    }

public class Q_User
    {
        public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();

        public bool AuthenticateUser(string username, string password, out int userid)
        {
            bool result = false;
            userid = 0;
            result = ctx.tblusers.Any(u => u.username == username.Trim() && u.password == password.Trim() && u.active == true && u.administrator.HasValue && u.administrator.Value == true && (u.broker.HasValue == false || u.broker.Value == false)); // Check Administrator
            if (!result)
            {
                result = ctx.tblusers.Any(u => u.username == username.Trim() && u.password == password.Trim() && u.active == true && u.broker.HasValue && u.broker.Value == true && (u.administrator.HasValue == false || u.administrator.Value == false)); // Check Broker
                if (!result)
                {
                    result = ctx.tblusers.Any(u => u.username == username.Trim() && u.password == password.Trim() && u.active == true && (u.administrator.HasValue == false || u.administrator.Value == false) && (u.broker.HasValue == false || u.broker.Value == false)); // Check User
                    if (!result)
                    {
                        result = ctx.tblusers.Any(u => u.username == username.Trim() && u.password == password.Trim() && u.active == true && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == true && u.broker.Value == true)); // Check Manager
                    }
                }
            }

            if (result)
            {
                userid = ctx.tblusers.Where(u => u.username == username.Trim() && u.password == password.Trim() && u.active == true).Select(u => u.userid).FirstOrDefault();

            }
            return result;
        }
    }

Q Setting
======================================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Occfinance_Data;
using Occfinance.Models;
using System.Net;
using System.Data.SqlClient;
using System.Data;
using Occfinance.Helpers;
using System.Configuration;
using Mandrill;
using System.Drawing;
using System.IO;
using System.Web.Configuration;

namespace Occfinance.Code
{
    public class Q_settings
    {
        public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
        System.Web.HttpContext con = HttpContext.Current;

        #region Add Broker
        /// <summary>
        /// Adding Broker
        /// </summary>
        /// <param name="brokername"></param>
        /// <param name="username"></param>
        /// <param name="accountid"></param>
        /// <param name="TeamsArray"></param>
        /// <param name="PrimaryContact"></param>
        /// <param name="SecondaryContact"></param>
        /// <param name="Address1"></param>
        /// <param name="Address2"></param>
        /// <param name="OtherDetails"></param>
        /// <param name="title"></param>
        /// <param name="fax"></param>
        /// <param name="qualification"></param>
        /// <param name="brkImage"></param>
        /// <returns></returns>
        public bool AddBroker(string brokername, string username, out int accountid, List<tblTeamMember> TeamsArray, string PrimaryContact, string SecondaryContact, string Address1, string Address2, string OtherDetails, string title, string fax, string qualification, string brkImage = "")
        {
            bool result = false;
            accountid = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {

                    if (ctx.tblusers.Where(c => c.username.ToLower().Trim() == username.ToLower().Trim() && c.active == true && !string.IsNullOrEmpty(c.username)).Any())
                    {
                        result = false;
                    }
                    else
                    {
                        try
                        {
                            if (!string.IsNullOrWhiteSpace(brkImage))
                            {
                                brkImage = System.IO.Path.GetFileName(brkImage);
                            }
                        }
                        catch { }

                        int parentaccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"]);

                        var u = ctx.tblusers.Add(new tbluser()
                        {
                            account = parentaccountId,
                            username = username,
                            password = "test",
                            active = true,
                            administrator = false,
                            Title = title,
                            brokername = brokername,
                            broker = true,
                            PrimaryContact = PrimaryContact ?? "",
                            SecondaryContact = SecondaryContact ?? "",
                            Address1 = Address1 ?? "",
                            Address2 = Address2 ?? "",
                            FAX = fax,
                            Qualification = qualification,
                            OtherDetails = OtherDetails ?? "",
                            BrokerImage = brkImage

                        });
                        ctx.SaveChanges();
                        result = true;
                        accountid = u.userid;

                        //create Thumb
                        if (!string.IsNullOrWhiteSpace(brkImage))
                        {
                            string path = System.Web.HttpContext.Current.Server.MapPath("~/Content/BrokerImages/");
                            string fullImagePath = path + brkImage;
                            if (File.Exists(fullImagePath))
                            {
                                string thumbPath = path + "Thumb/" + u.userid + ".jpg";
                                System.Drawing.Image image = System.Drawing.Image.FromFile(fullImagePath);
                                System.Drawing.Image thumb = image.GetThumbnailImage(100, 100, () => false, IntPtr.Zero);
                                thumb.Save(thumbPath, System.Drawing.Imaging.ImageFormat.Jpeg);
                            }
                        }


                        string body = "Hi " + brokername + ", <br /><br />";
                        body += "Your email id: " + username + "<br /><br />";
                        body += "Password: test <br /><br />";
                        body += "Regards: <br /><br />";
                        body += "Apptrack Admin";

                        //-------------------------------------
                        List<ManDRILLModel> _mandrillOBJ = new List<ManDRILLModel>();

                        var _mailDrilEmails = new List<Mandrill.EmailAddress>();
                        Mandrill.EmailAddress objEm = new Mandrill.EmailAddress();
                        objEm.email = username;
                        objEm.name = brokername;
                        _mailDrilEmails.Add(objEm);

                        //Man DRILL ********************************************************************

                        var domain = "occfinance.com";
                        Mandrill.EmailMessage message = new Mandrill.EmailMessage();
                        var _mandrillMessage = new List<Mandrill.EmailMessage>();

                        message.to = _mailDrilEmails;
                        message.html = body;
                        message.from_email = ConfigurationManager.AppSettings["SMTP_FROM"].ToString();
                        message.from_name = "OCC";
                        message.subject = "Your Apptrack Credential";
                        message.inline_css = true;
                        message.signing_domain = domain;
                        message.AddHeader("X-MC-SigningDomain", domain);
                        message.AddHeader("X-MC-TrackingDomain", domain);
                        _mandrillMessage.Add(message);

                        //********************************************************************************

                        // Manage The ManDRILLModel - 25-07-2014

                        var objMAN = new ManDRILLModel();
                        objMAN.contactId = 0;
                        objMAN.financeid = 0;
                        objMAN.mailcontent = body;
                        objMAN.MandrillMSG = message;
                        objMAN.IsAppContact = false;

                        objMAN.notemsg = "";

                        _mandrillOBJ.Add(objMAN);

                        int userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                        WebUtility.SendMailViaManDRILL(_mandrillOBJ, userId);
                        //-------------------------------------


                        int _type = 1;
                        if (TeamsArray != null)
                        {
                            foreach (var obj in TeamsArray)
                            {
                                if (obj.IsActive.HasValue && obj.IsActive.Value == true)
                                    _type = 1;
                                else
                                    _type = 2;

                                AddTeamMembers(obj.TeamId.HasValue ? obj.TeamId.Value : 0, u.userid, userId, _type);
                            }
                        }
                        AddAdminTeamMembers(parentaccountId, parentaccountId, u.userid, userId, 1);

                        // Add Default Permission ---------------------------
                        // 1- Admin User, 2- Broker, 3- General User As per the client response ON 21-07-2014
                        try
                        {
                            AddDefaultPermission(2, u.userid, parentaccountId, userId);
                        }
                        catch
                        {
                        }
                    }

                }
                return result;
            }
            catch (Exception)
            {
                return result;
            }
        }


        #endregion

        #region Update Broker
        /// <summary>
        /// Updating broker
        /// </summary>
        /// <param name="brokername"></param>
        /// <param name="username"></param>
        /// <param name="brokerid"></param>
        /// <param name="accountid"></param>
        /// <param name="TeamsArray"></param>
        /// <param name="PrimaryContact"></param>
        /// <param name="SecondaryContact"></param>
        /// <param name="Address1"></param>
        /// <param name="Address2"></param>
        /// <param name="OtherDetails"></param>
        /// <param name="title"></param>
        /// <param name="fax"></param>
        /// <param name="qualification"></param>
        /// <param name="brkImage"></param>
        /// <returns></returns>
        public bool UpdateBroker(string brokername, string username, int brokerid, out int accountid, List<tblTeamMember> TeamsArray, string PrimaryContact, string SecondaryContact, string Address1, string Address2, string OtherDetails, string title, string fax, string qualification, string brkImage = "")
        {
            bool result = false;
            accountid = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null && brokerid > 0)
                {
                    if (ctx.tblusers.Where(c => c.username.ToLower().Trim() == username.ToLower().Trim() && c.active == true && c.userid != brokerid && !string.IsNullOrEmpty(c.username)).Any())
                    {
                        result = false;
                    }
                    else
                    {
                        try
                        {
                            if (!string.IsNullOrWhiteSpace(brkImage))
                            {
                                brkImage = System.IO.Path.GetFileName(brkImage);
                            }
                        }
                        catch { }

                        int parentaccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"]);
                        var u = ctx.tblusers.Where(c => c.userid == brokerid).FirstOrDefault();
                        u.account = parentaccountId;//broker.accountid,
                        u.username = username;
                        u.brokername = brokername;
                        u.Title = title;
                        u.PrimaryContact = PrimaryContact ?? "";
                        u.SecondaryContact = SecondaryContact ?? "";
                        u.Address1 = Address1 ?? "";
                        u.Address2 = Address2 ?? "";
                        u.FAX = fax;
                        u.Qualification = qualification;
                        u.OtherDetails = OtherDetails ?? "";
                        u.BrokerImage = brkImage;
                        // u.TeamId = teamid;
                        ctx.SaveChanges();
                        result = true;
                        accountid = u.userid;

                        //create Thumb
                        if (!string.IsNullOrWhiteSpace(brkImage))
                        {
                            string path = System.Web.HttpContext.Current.Server.MapPath("~/Content/BrokerImages/");
                            string fullImagePath = path + brkImage;
                            if (File.Exists(fullImagePath))
                            {
                                string thumbPath = path + "Thumb/" + u.userid + ".jpg";
                                System.Drawing.Image image = System.Drawing.Image.FromFile(fullImagePath);
                                System.Drawing.Image thumb = image.GetThumbnailImage(100, 100, () => false, IntPtr.Zero);
                                thumb.Save(thumbPath, System.Drawing.Imaging.ImageFormat.Jpeg);
                            }
                        }

                        string body = "Hi " + brokername + ", <br /><br />";
                        body += "Your updated email id: " + username + "<br /><br />";
                        body += "Password: " + u.password + " <br /><br />";
                        body += "Regards: <br /><br />";
                        body += "Apptrack Admin";

                        //------------------------------------------------------------

                        List<ManDRILLModel> _mandrillOBJ = new List<ManDRILLModel>();

                        var _mailDrilEmails = new List<Mandrill.EmailAddress>();
                        Mandrill.EmailAddress objEm = new Mandrill.EmailAddress();
                        objEm.email = username;
                        objEm.name = brokername;
                        _mailDrilEmails.Add(objEm);

                        //Man DRILL ********************************************************************

                        var domain = "occfinance.com";
                        Mandrill.EmailMessage message = new Mandrill.EmailMessage();
                        var _mandrillMessage = new List<Mandrill.EmailMessage>();

                        message.to = _mailDrilEmails;
                        message.html = body;
                        message.from_email = ConfigurationManager.AppSettings["SMTP_FROM"].ToString();
                        message.from_name = "OCC";
                        message.subject = "Your Apptrack Credential";
                        message.inline_css = true;
                        message.signing_domain = domain;
                        message.AddHeader("X-MC-SigningDomain", domain);
                        message.AddHeader("X-MC-TrackingDomain", domain);
                        _mandrillMessage.Add(message);

                        //********************************************************************************

                        // Manage The ManDRILLModel - 25-07-2014

                        var objMAN = new ManDRILLModel();
                        objMAN.contactId = 0;
                        objMAN.financeid = 0;
                        objMAN.mailcontent = body;
                        objMAN.MandrillMSG = message;
                        objMAN.IsAppContact = false;

                        objMAN.notemsg = "";

                        _mandrillOBJ.Add(objMAN);

                        int userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                        WebUtility.SendMailViaManDRILL(_mandrillOBJ, userId);

                        //------------------------------------------------------------



                        int _type = 1;
                        if (TeamsArray != null)
                        {
                            foreach (var obj in TeamsArray)
                            {
                                if (obj.IsActive.HasValue && obj.IsActive.Value == true)
                                    _type = 1;
                                else
                                    _type = 2;

                                AddTeamMembers(obj.TeamId.HasValue ? obj.TeamId.Value : 0, u.userid, userId, _type);
                            }
                        }
                    }

                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Settings", "UpdateBroker", ex.Message);
                return result;
            }
        }

        /// <summary>
        /// Updating Team Broker
        /// </summary>
        /// <param name="brokername"></param>
        /// <param name="username"></param>
        /// <param name="brokerid"></param>
        /// <param name="accountid"></param>
        /// <param name="TeamsArray"></param>
        /// <returns></returns>
        public bool UpdateTeamBroker(string brokername, string username, int brokerid, out int accountid, List<tblAdminTeammeber> TeamsArray)
        {
            bool result = false;
            accountid = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null && brokerid > 0)
                {


                    int parentaccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"]);
                    var u = ctx.tblusers.Where(c => c.userid == brokerid).FirstOrDefault();

                    u.AdminId = 0;
                    ctx.SaveChanges();
                    result = true;
                    accountid = u.userid;

                    int userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    int _type = 1;
                    if (TeamsArray != null)
                    {
                        foreach (var obj in TeamsArray)
                        {
                            if (obj.IsActive.HasValue && obj.IsActive.Value == true)
                                _type = 1;
                            else
                                _type = 2;

                            AddAdminTeamMembers(obj.AdminTeamId.HasValue ? obj.AdminTeamId.Value : 0, obj.AdminTeamId.HasValue ? obj.AdminTeamId.Value : 0, u.userid, userId, _type);
                        }
                    }

                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "UpdateTeamBroker", ex.Message);
                return result;
            }
        }


        #endregion

        #region Add Team
        /// <summary>
        /// Adding Team
        /// </summary>
        /// <param name="teamName"></param>
        /// <param name="managerId"></param>
        /// <param name="teamid"></param>
        /// <returns></returns>
        public bool AddTeam(string teamName, int managerId, out int teamid)
        {
            bool result = false;
            teamid = 0;
            try
            {

                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");

                    SqlParameter[] Param = new SqlParameter[9];
                    Param[0] = new SqlParameter("@AccountId", AccountId);
                    Param[1] = new SqlParameter("@CompanyName", "0");
                    Param[2] = new SqlParameter("@TeamName", teamName);
                    Param[3] = new SqlParameter("@Regions", "0");
                    Param[4] = new SqlParameter("@ManagerId", managerId);
                    Param[5] = new SqlParameter("@EntryBy", userId);
                    Param[6] = new SqlParameter("@IsActive", true);
                    Param[7] = new SqlParameter("@IsDelete", false);

                    Param[8] = new SqlParameter("@TeamId", 0);
                    Param[8].Direction = ParameterDirection.Output;
                    string _procedurename = "tblTeamAdd";

                    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                    teamid = Convert.ToInt32(Param[8] != null ? Param[8].SqlValue.ToString() : "0");

                    if (teamid > 0)
                    {
                        if (AddTeamMembers(teamid, managerId, userId, 1) > 0)
                            result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "AddTeam", ex.Message);
                return result;
            }
        }

        #endregion

        #region Add Team Members
        /// <summary>
        /// Adding Team member
        /// </summary>
        /// <param name="TeamId"></param>
        /// <param name="MemberId"></param>
        /// <param name="userId"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public int AddTeamMembers(int TeamId, int MemberId, int userId, int Type)
        {
            int chk = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    SqlParameter[] Param = new SqlParameter[6];
                    Param[0] = new SqlParameter("@TeamId", TeamId);
                    Param[1] = new SqlParameter("@MemberId", MemberId);
                    Param[2] = new SqlParameter("@EntryBy", userId);
                    Param[3] = new SqlParameter("@IsActive", true);
                    Param[4] = new SqlParameter("@IsDelete", false);
                    Param[5] = new SqlParameter("@Type", Type);

                    string _procedurename = "tblTeamMembersAdd";

                    chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                }

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "Addteammembers", ex.Message);
            }
            return chk;
        }


        #endregion

        #region Update Team
        /// <summary>
        /// Update team
        /// </summary>
        /// <param name="teamName"></param>
        /// <param name="managerId"></param>
        /// <param name="editteamId"></param>
        /// <param name="teamid"></param>
        /// <returns></returns>
        public bool UpdateTeam(string teamName, int managerId, int editteamId, out int teamid)
        {
            bool result = false;
            teamid = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    SqlParameter[] Param = new SqlParameter[10];
                    Param[0] = new SqlParameter("@TeamId", editteamId);
                    Param[1] = new SqlParameter("@AccountId", AccountId);
                    Param[2] = new SqlParameter("@CompanyName", "0");
                    Param[3] = new SqlParameter("@TeamName", teamName);
                    Param[4] = new SqlParameter("@Regions", "0");
                    Param[5] = new SqlParameter("@ManagerId", managerId);
                    Param[6] = new SqlParameter("@UpdateBy", userId);
                    Param[7] = new SqlParameter("@IsActive", true);
                    Param[8] = new SqlParameter("@IsDelete", false);

                    Param[9] = new SqlParameter("@Chk", 0);
                    Param[9].Direction = ParameterDirection.Output;
                    string _procedurename = "tblTeamUpdate";

                    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                    teamid = Convert.ToInt32(Param[9] != null ? Param[9].SqlValue.ToString() : "0");

                    if (teamid > 0)
                    {
                        if (AddTeamMembers(editteamId, managerId, userId, 1) > 0)
                            result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "UpdateTeam", ex.Message);
                return result;
            }
        }

        #endregion

        #region Delete Team
        /// <summary>
        /// Deleting Team
        /// </summary>
        /// <param name="teamid"></param>
        /// <param name="managerId"></param>
        /// <param name="chk"></param>
        /// <returns></returns>
        public bool DeleteTeam(int teamid, int managerId, out int chk)
        {
            bool result = false;
            chk = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    SqlParameter[] Param = new SqlParameter[4];
                    Param[0] = new SqlParameter("@TeamId", teamid);
                    Param[1] = new SqlParameter("@ManagerId", managerId);
                    Param[2] = new SqlParameter("@UpdateBy", userId);

                    Param[3] = new SqlParameter("@Chk", 0);
                    Param[3].Direction = ParameterDirection.Output;
                    string _procedurename = "tblTeamDelete";

                    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
                    chk = Convert.ToInt32(Param[3] != null ? Param[3].SqlValue.ToString() : "0");

                    if (chk > 0)
                    {
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "deleteTeam", ex.Message);
                return result;
            }
        }

        #endregion

        #region Delete Broker
        /// <summary>
        /// Delete Broker
        /// </summary>
        /// <param name="brokerid"></param>
        /// <returns></returns>
        public bool DeleteBroker(int brokerid)
        {
            bool result = false;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    int parentaccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"]);
                    var brokeruser = ctx.tblusers.Where(b => b.userid == brokerid && b.active && b.broker.HasValue && b.broker.Value == true).Select(b => b).FirstOrDefault();
                    if (brokeruser != null)
                    {
                        if (brokeruser != null)
                        {
                            brokeruser.active = false;
                            brokeruser.TeamId = 0;
                            ctx.SaveChanges();
                            result = true;
                            TeamMembersDeleteByMemberId(brokerid);
                            int userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                            AddAdminTeamMembers(parentaccountId, parentaccountId, brokerid, userId, 2);
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "deleteBroker", ex.Message);
                return result;
            }
        }

        #endregion

        #region Change LoggedIn User Password
        /// <summary>
        /// Changing logged inuser Password
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="oldPassword"></param>
        /// <param name="newPassword"></param>
        /// <returns></returns>
        public bool ChangeLoggedUserPassword(int userId, string oldPassword, string newPassword)
        {
            bool result = false;
            try
            {
                var usr = ctx.tblusers.Where(u => u.userid == userId && u.password == oldPassword).Select(u => u).SingleOrDefault();
                if (usr != null)
                {
                    usr.password = newPassword;
                    ctx.SaveChanges();
                    result = true;
                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "ChangedLoggedUserPassword", ex.Message);
                return result;
            }

        }
        #endregion

        #region Find Broker for logged in user
        /// <summary>
        /// Finding broker for logged in user
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public List<Clients> FindBrokerForLoggedInUser(int userid)
        {
            bool flag = false;
            List<Clients> brokers = new List<Clients>();
            Clients result = new Clients();
            Q_Application _obj = new Q_Application();
            try
            {
                int teamId = 0;
                string brokersIds = "";
                int _userType = 0;
                int _accountId = 0;
                _userType = _obj.CheckUserType(userid, out teamId, out _accountId, out brokersIds);

                //Updated With Store Procedure For Navigation Delay ********* 7/7/2014 ********************************************

                var _Id = 0;

                if (_userType == (int)UserType.Admin)
                    _Id = _accountId;
                else if (_userType == (int)UserType.Manager)
                    _Id = teamId;
                else if (_userType == (int)UserType.Broker)
                    _Id = userid;

                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", _userType);
                Param[1] = new SqlParameter("@ID", _Id);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindBrokerForLoggedInUser", Param);
                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                               TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString()),
                               TeamNames = ((row["TeamNames"] == DBNull.Value || row["TeamNames"] == null) ? " " : row["TeamNames"]).ToString(),
                               AdminTeamName = ((row["AdminTeamName"] == DBNull.Value || row["AdminTeamName"] == null) ? " " : row["AdminTeamName"]).ToString(),
                               PrimaryContact = ((row["PrimaryContact"] == DBNull.Value || row["PrimaryContact"] == null) ? " " : row["PrimaryContact"]).ToString(),
                               SecondaryContact = ((row["SecondaryContact"] == DBNull.Value || row["SecondaryContact"] == null) ? " " : row["SecondaryContact"]).ToString(),
                               Address1 = ((row["Address1"] == DBNull.Value || row["Address1"] == null) ? " " : row["Address1"]).ToString(),
                               Address2 = ((row["Address2"] == DBNull.Value || row["Address2"] == null) ? " " : row["Address2"]).ToString(),
                               OtherDetails = ((row["OtherDetails"] == DBNull.Value || row["OtherDetails"] == null) ? " " : row["OtherDetails"]).ToString(),
                               Title = ((row["Title"] == DBNull.Value || row["Title"] == null) ? " " : row["Title"]).ToString(),
                               FAX = ((row["FAX"] == DBNull.Value || row["FAX"] == null) ? " " : row["FAX"]).ToString(),
                               Qualification = ((row["Qualification"] == DBNull.Value || row["Qualification"] == null) ? " " : row["Qualification"]).ToString(),
                               BrokerImage = ((row["BrokerImage"] == DBNull.Value || row["BrokerImage"] == null || row["BrokerImage"] == "") ? "noimage.jpg" : row["BrokerImage"]).ToString()



                           }).ToList();

                //**********************************************************************************


                return brokers;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "FindBrokerForLoggedinUser");
                return brokers;
            }
        }
        /// <summary>
        /// Brokers for logged in user
        /// </summary>
        /// <param name="accid">Account Id</param>
        /// <param name="userid">User Id</param>
        /// <returns></returns>
        public List<Clients> FindBrokerForLoggedInUserWithUserId(int accid, int userid)
        {
            bool flag = false;
            List<Clients> brokers = new List<Clients>();
            Q_Application _obj = new Q_Application();
            try
            {
                int teamId = 0;
                string brokersIds = "";
                int _userType = 0;
                int _accountId = 0;
                _userType = _obj.CheckUserType(userid, out teamId, out _accountId, out brokersIds);
                var _Id = 0;
                if (_userType == (int)UserType.Admin)
                    _Id = _accountId;
                else if (_userType == (int)UserType.Manager)
                    _Id = teamId;
                else if (_userType == (int)UserType.Broker)
                    _Id = userid;
                else if (_userType == (int)UserType.GeneralUser)
                {
                    int account = ctx.tblaccounts.Where(u => u.accountid == _accountId).Select(u => u.parent.HasValue ? u.parent.Value : 0).FirstOrDefault();
                    _Id = account;
                }

                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", _userType);
                Param[1] = new SqlParameter("@ID", _Id);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindBrokerForLoggedInUserWithUserId", Param);
                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                               TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString()),
                           }).ToList();
                return brokers;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "FindBrokerforLoggedInUserWithUserId");
                return brokers;
            }
        }

        public bool DeleteFactFindUserById(long id)
        {
            if (id > 0)
            {
                try
                {
                    tblUser_FactFind data;
                    data = ctx.tblUser_FactFind.Where(x => x.Client_ID == id).FirstOrDefault();
                    if (data != null)
                    {
                        data.IsActive = false;
                        data.IsDeleted = true;
                        ctx.SaveChanges();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    Helper.ErrorLog(ex.InnerException, "Q_setting", "DeleteFactFindUserById");
                }
            }
            return false;
        }

        /// <summary>
        /// Get All Intellic Users
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllIntellicUsers()
        {
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAll_tblUser_FactFind_For_Intellicalc", Param);
                return dt;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "GetAllIntellicUsers");
                return null;
            }
        }

        /// <summary>
        /// Save Adviser For Intellicalc
        /// </summary>
        /// <param name="clientId"></param>
        /// <param name="brokerId"></param>
        /// <returns></returns>
        public int SaveAdviserForIntellicalc(int clientId, int brokerId)
        {
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@clientId", clientId);
                Param[1] = new SqlParameter("@adviserId", brokerId);
                SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "sp_Save_Adviser_For_Intellicalc", Param);

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "SaveAdviserForIntellicalc");
                return 0;
            }
            return 1;
        }


        /// <summary>
        /// Get All Intellic Users
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllFactfindUsers(int adviserId = 0)
        {
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@adviserId", adviserId);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAll_tblUser_FactFind_For_FactFind", Param);
                return dt;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "GetAllFactfindUsers");
                return null;
            }
        }

        public int SaveAdviserForTblUserFactFind(int clientId, int brokerId)
        {
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@clientId", clientId);
                Param[1] = new SqlParameter("@adviserId", brokerId);
                SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "sp_Save_Adviser_For_tbluser_FactFind", Param);
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "SaveAdviserForTblUserFactFind");
                return 0;
            }
            return 1;
        }

        public DataTable GetFactfindUsersByClient_Id(int client_id)
        {
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@client_id", client_id);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAll_tblUser_FactFind_ByClientid", Param);
                return dt;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "GetFactfindUsersByClient_Id");
                return null;
            }
        }


        /// <summary>
        /// Find broker for logged in user with account Id
        /// </summary>
        /// <param name="accid"></param>
        /// <returns></returns>
        public List<Clients> FindBrokerForLoggedInBrokerWithAccountId(int accid)
        {
            bool flag = false;
            List<Clients> brokers = new List<Clients>();
            Q_Application _obj = new Q_Application();

            try
            {


                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", 1);
                Param[1] = new SqlParameter("@ID", accid);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindBrokerForLoggedInUserWithUserId", Param);

                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                               TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString()),
                           }).ToList();

                //**********************************************************************************

                return brokers;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "FindBrokerForLoggedinUserwithAccountId", ex.Message);
                return brokers;
            }
        }
        /// <summary>
        /// Finding Referral Broker for logged in user with account Id
        /// </summary>
        /// <param name="accid"></param>
        /// <param name="userid"></param>
        /// <param name="userType"></param>
        /// <returns></returns>
        public List<Clients> FindReferralBrokerForLoggedInBrokerWithAccountId(int accid, int userid, int userType)
        {
            bool flag = false;
            List<Clients> brokers = new List<Clients>();
            Q_Application _obj = new Q_Application();

            try
            {
                SqlParameter[] Param = new SqlParameter[3];
                Param[0] = new SqlParameter("@UserType", userType);
                Param[1] = new SqlParameter("@ID", accid);
                Param[2] = new SqlParameter("@UserId", userid);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindReferralBrokerForLoggedInUserWithUserId", Param);

                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                               TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString()),
                           }).ToList();

                //**********************************************************************************

                return brokers;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "FindReferralBrokerForLoggedinBrokerWithscoountId", ex.Message);
                return brokers;
            }
        }

        public List<Clients> FindBrokerLoggedInBrokerId(int userid)
        {
            List<Clients> result = new List<Clients>();
            try
            {


                result = (from u in ctx.tblusers.Where(u => u.userid == userid)
                          join t in ctx.tblTeams on u.TeamId equals t.TeamId into teamdata
                          from t in teamdata.DefaultIfEmpty()
                          select new Clients()
                          {
                              clientid = u.account,
                              parent = u.tblaccount.parent,
                              companyname = u.tblaccount.companyname,
                              TeamName = t.TeamName ?? "",
                              TeamId = u.TeamId ?? 0,
                              affiliateid = u.tblaccount.affiliateid,
                              telephone = u.tblaccount.telephone,
                              email = u.username,
                              contactname = u.brokername,
                              address = u.tblaccount.address,
                              userid = u.userid,
                              AdminId = u.AdminId,
                              countLeads = u.tblaccount.tblcontacts.Count()
                          }).ToList();

                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "FindBrokerLoggedInBrokeRID", ex.Message);
                return result;
            }
        }
        /// <summary>
        /// Find Admin Team List
        /// </summary>
        /// <param name="AccountId"></param>
        /// <returns></returns>
        public List<AdminTeam> FindAdminTeamList(int AccountId)
        {
            List<AdminTeam> result = new List<AdminTeam>();
            SqlParameter[] Param = new SqlParameter[1];
            if (AccountId > 0)
                Param[0] = new SqlParameter("@AccountId", AccountId);
            string _procedurename = "GetALLAdminTeam";
            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);

                result = (from DataRow row in dt.Rows
                          select new AdminTeam
                          {
                              UserId = Convert.ToInt32(((row["UserId"] == DBNull.Value || row["UserId"] == null) ? "0" : row["UserId"]).ToString()),
                              AdminName = Convert.ToString((row["AdminName"] == DBNull.Value || row["AdminName"] == null) ? " " : row["AdminName"]),
                              AdminTeamName = Convert.ToString((row["AdminTeamName"] == DBNull.Value || row["AdminTeamName"] == null) ? " " : row["AdminTeamName"]),
                              AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
                              Brokers = Convert.ToString((row["Brokers"] == DBNull.Value || row["Brokers"] == null) ? " " : row["Brokers"]),

                          }).OrderBy(c => c.AdminTeamName).ToList();

                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "FindAdminTeamList", ex.Message);
                return result;
            }
        }

        /// <summary>
        /// Finding Team List
        /// </summary>
        /// <param name="AccountId"></param>
        /// <returns></returns>
        public List<Team> FindTeamList(int AccountId)
        {
            List<Team> result = new List<Team>();
            SqlParameter[] Param = new SqlParameter[1];
            Param[0] = new SqlParameter("@AccountId", AccountId);
            string _procedurename = "tblTeamGetALL";
            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);

                result = (from DataRow row in dt.Rows
                          select new Team
                          {
                              TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                              TeamName = Convert.ToString((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]),
                              brokername = Convert.ToString((row["brokername"] == DBNull.Value || row["brokername"] == null) ? " " : row["brokername"]),
                              AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
                              IsActive = Convert.ToBoolean(((row["IsActive"] == DBNull.Value || row["IsActive"] == null) ? false : row["IsActive"]).ToString()),
                              IsDelete = Convert.ToBoolean(((row["IsDelete"] == DBNull.Value || row["IsDelete"] == null) ? false : row["IsDelete"]).ToString()),
                              CompanyName = Convert.ToString((row["CompanyName"] == DBNull.Value || row["CompanyName"] == null) ? " " : row["CompanyName"]),
                              TeamBrokers = Convert.ToString((row["TeamBrokers"] == DBNull.Value || row["TeamBrokers"] == null) ? " " : row["TeamBrokers"]),
                              ManagerId = Convert.ToInt32(((row["ManagerId"] == DBNull.Value || row["ManagerId"] == null) ? "0" : row["ManagerId"]).ToString()),
                              EntryBy = Convert.ToInt32(((row["EntryBy"] == DBNull.Value || row["EntryBy"] == null) ? "0" : row["EntryBy"]).ToString()),
                              UpdateBy = Convert.ToInt32(((row["UpdateBy"] == DBNull.Value || row["UpdateBy"] == null) ? "0" : row["UpdateBy"]).ToString()),
                              AddDate = (row["AddDate"] == DBNull.Value || row["AddDate"] == null) ? (DateTime?)null : Convert.ToDateTime(row["AddDate"]),

                          }).ToList();

                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "FindteamList", ex.Message);
                return result;
            }
        }
        /// <summary>
        /// Find Team by Team Id
        /// </summary>
        /// <param name="AccountId"></param>
        /// <param name="TeamId"></param>
        /// <returns></returns>
        public List<Team> FindTeamByTeamId(int AccountId, int TeamId)
        {
            List<Team> result = new List<Team>();
            SqlParameter[] Param = new SqlParameter[2];
            Param[0] = new SqlParameter("@AccountId", AccountId);
            Param[1] = new SqlParameter("@TeamId", TeamId);
            string _procedurename = "tblTeamGetByTeamId";
            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);

                result = (from DataRow row in dt.Rows
                          select new Team
                          {
                              TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                              TeamName = Convert.ToString((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]),
                              brokername = Convert.ToString((row["brokername"] == DBNull.Value || row["brokername"] == null) ? " " : row["brokername"]),
                              AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
                              IsActive = Convert.ToBoolean(((row["IsActive"] == DBNull.Value || row["IsActive"] == null) ? false : row["IsActive"]).ToString()),
                              IsDelete = Convert.ToBoolean(((row["IsDelete"] == DBNull.Value || row["IsDelete"] == null) ? false : row["IsDelete"]).ToString()),
                              CompanyName = Convert.ToString((row["CompanyName"] == DBNull.Value || row["CompanyName"] == null) ? " " : row["CompanyName"]),
                              TeamBrokers = Convert.ToString((row["TeamBrokers"] == DBNull.Value || row["TeamBrokers"] == null) ? " " : row["TeamBrokers"]),
                              ManagerId = Convert.ToInt32(((row["ManagerId"] == DBNull.Value || row["ManagerId"] == null) ? "0" : row["ManagerId"]).ToString()),
                              EntryBy = Convert.ToInt32(((row["EntryBy"] == DBNull.Value || row["EntryBy"] == null) ? "0" : row["EntryBy"]).ToString()),
                              UpdateBy = Convert.ToInt32(((row["UpdateBy"] == DBNull.Value || row["UpdateBy"] == null) ? "0" : row["UpdateBy"]).ToString()),
                              AddDate = (row["AddDate"] == DBNull.Value || row["AddDate"] == null) ? (DateTime?)null : Convert.ToDateTime(row["AddDate"]),

                          }).ToList();

                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "FindTeamByTeamId", ex.Message);
                return result;
            }
        }
        /// <summary>
        /// To check Broker
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public bool hasBroker(int userid)
        {
            bool flag = false;

            try
            {
                flag = ctx.tblusers.Any(d => d.userid == userid && d.active == true && d.broker.HasValue && d.broker.Value == true);

                return flag;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "hasBroker", ex.Message);
                return flag;
            }
        }
        #endregion

        #region Check password exist in database
        /// <summary>
        /// Check Password
        /// </summary>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool checkpassword(string password)
        {
            bool result = false;
            try
            {
                result = ctx.tblusers.Any(u => u.password == password);
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "checkpassword", ex.Message);
                return result;
            }
        }

        #endregion

        #region Manage Permission
        /// <summary>
        /// User Permission Add update and delete
        /// </summary>
        /// <param name="accid"></param>
        /// <param name="loggeduserid"></param>
        /// <param name="PermissionArray"></param>
        /// <returns></returns>
        public int UserPermissionADDUpdateDelete(int accid, int loggeduserid, List<UserPermissionModel> PermissionArray)
        {
            int chk = 0;
            try
            {
                foreach (var obj in PermissionArray)
                {
                    SqlParameter[] Param = new SqlParameter[7];

                    Param[0] = new SqlParameter("@UserId", obj.userid);
                    Param[1] = new SqlParameter("@AccountId", accid);
                    Param[2] = new SqlParameter("@PermissionId", obj.PermissionId);
                    Param[3] = new SqlParameter("@EntryBy", loggeduserid);
                    Param[4] = new SqlParameter("@IsActive", true);
                    Param[5] = new SqlParameter("@IsDelete", false);
                    Param[6] = new SqlParameter("@Type", obj.Type);

                    int _chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblUserPermissionADDUpdateDelete", Param);
                    chk = 1;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "UserPermissionAddUpdateDelete", ex.Message);
            }

            return chk;
        }
        #endregion

        #region Manage Permission
        /// <summary>
        /// Adding Defualt Permission
        /// </summary>
        /// <param name="UserType"></param>
        /// <param name="UserId"></param>
        /// <param name="AccountId"></param>
        /// <param name="LoggdInUserId"></param>
        /// <returns></returns>
        public int AddDefaultPermission(int UserType, int UserId, int AccountId, int LoggdInUserId)
        {
            // User Type - 1- Admin user having only View All permission, 2- Broker Full Permissions, 3- General User --  As per the client response ON 21-07-2014
            int chk = 0;
            try
            {
                SqlParameter[] Param = new SqlParameter[4];

                Param[0] = new SqlParameter("@UserId", UserId);
                Param[1] = new SqlParameter("@AccountId", AccountId);
                Param[2] = new SqlParameter("@LoggdInUserId", LoggdInUserId);
                Param[3] = new SqlParameter("@UserType", UserType);

                int _chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "AddDefaultPermission", Param);
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_settings", "AdddefualtPermission", ex.Message);
            }

            return chk;
        }
        #endregion

        #region Attendance Settings
        /// <summary>
        /// Attendence for brokers and damin team
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="usertype"></param>
        /// <param name="Year"></param>
        /// <param name="Name"></param>
        /// <param name="TeamId"></param>
        /// <returns></returns>
        public List<AttendanceClients> Attendance_FindBrokersForAdminTeam(int userid, int usertype, int Year = 0, string Name = "", int TeamId = 0)
        {
            var brokers = new List<AttendanceClients>();
            string _procedurename = "GetUsersForYearAttendance";
            try
            {
                SqlParameter[] Param = new SqlParameter[5];
                Param[0] = new SqlParameter("@UserType", usertype);
                Param[1] = new SqlParameter("@ID", userid);

                if (Year > 0)
                    Param[2] = new SqlParameter("@Year", Year);
                if (!string.IsNullOrWhiteSpace(Name))
                    Param[3] = new SqlParameter("@Name", Name);
                if (TeamId > 0)
                    Param[4] = new SqlParameter("@TeamId", TeamId);

                var dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);
                brokers = (from DataRow row in dt.Rows
                           select new AttendanceClients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               companyname = Convert.ToString((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]),
                               email = Convert.ToString((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]),
                               contactname = Convert.ToString((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               ActivityYear = Convert.ToInt32(((row["ActivityYear"] == DBNull.Value || row["ActivityYear"] == null) ? "0" : row["ActivityYear"]).ToString()),
                               DaysOffAllowed = Convert.ToDecimal(((row["DaysOffAllowed"] == DBNull.Value || row["DaysOffAllowed"] == null) ? "0" : row["DaysOffAllowed"]).ToString()),
                               DaysOffUsed = Convert.ToDecimal(((row["DaysOffUsed"] == DBNull.Value || row["DaysOffUsed"] == null) ? "0" : row["DaysOffUsed"]).ToString()),

                               DaysOffUsedManually = Convert.ToDecimal(((row["DaysOffUsedManually"] == DBNull.Value || row["DaysOffUsedManually"] == null) ? "0" : row["DaysOffUsedManually"]).ToString()),
                           }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "Attendance_FindbrokersForAdminTeam", ex.Message);
            }
            return brokers;

        }
        /// <summary>
        /// Attendence Brokers for admin team day wise.
        /// </summary>
        /// <param name="DayVal"></param>
        /// <param name="userid"></param>
        /// <param name="usertype"></param>
        /// <param name="Name"></param>
        /// <param name="TeamId"></param>
        /// <returns></returns>
        public List<AttendanceClients> Attendance_FindBrokersForAdminTeamDayWise(string DayVal, int userid, int usertype, string Name = "", int TeamId = 0)
        {
            var brokers = new List<AttendanceClients>();
            string _procedurename = "GetUsersForDaysAttendance";
            try
            {
                string activitydate = System.DateTime.Now.ToString("MM/dd/yyyy");
                if (!string.IsNullOrWhiteSpace(DayVal))
                    activitydate = DayVal.GetDate().Value.ToString("MM/dd/yyyy");

                SqlParameter[] Param = new SqlParameter[5];
                Param[0] = new SqlParameter("@UserType", usertype);
                Param[1] = new SqlParameter("@ID", userid);

                if (!string.IsNullOrWhiteSpace(Name))
                    Param[2] = new SqlParameter("@Name", Name);
                if (TeamId > 0)
                    Param[3] = new SqlParameter("@TeamId", TeamId);
                if (!string.IsNullOrWhiteSpace(DayVal))
                    Param[4] = new SqlParameter("@ActivityDate", activitydate);



                var dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);
                brokers = (from DataRow row in dt.Rows
                           select new AttendanceClients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               companyname = Convert.ToString((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]),
                               email = Convert.ToString((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]),
                               contactname = Convert.ToString((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AbsentHrsAllowed = Convert.ToDecimal(((row["AbsentHrsAllowed"] == DBNull.Value || row["AbsentHrsAllowed"] == null) ? "0" : row["AbsentHrsAllowed"]).ToString()),
                               AbsentHrsUsed = Convert.ToDecimal(((row["AbsentHrsUsed"] == DBNull.Value || row["AbsentHrsUsed"] == null) ? "0" : row["AbsentHrsUsed"]).ToString()),
                               ActivityDate = (row["ActivityDate"] == DBNull.Value || row["ActivityDate"] == null) ? (DateTime?)null : Convert.ToDateTime(row["ActivityDate"])

                           }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "Attendence_FindBrokersForAdminTeamDayWise", ex.Message);
            }
            return brokers;

        }

        #endregion

        #region Admin Team

        /// <summary>
        /// This will return all available brokers regardless the Account id. As these brokers will be tranferable for other team as well
        /// </summary>
        /// <param name="AccountId">Account Id</param>
        /// <returns></returns>
        public List<Clients> FindBrokersForAdminTeam(int AccountId)
        {
            List<Clients> brokers = new List<Clients>();
            string _procedurename = "FindBrokersForAdminTeam";
            try
            {
                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@Id", AccountId);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);
                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               companyname = Convert.ToString((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]),
                               affiliateid = Convert.ToString((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]),
                               email = Convert.ToString((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]),
                               telephone = Convert.ToString((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]),
                               address = Convert.ToInt32((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]),
                               TeamName = Convert.ToString((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]),
                               AdminTeamName = Convert.ToString((row["AdminTeamName"] == DBNull.Value || row["AdminTeamName"] == null) ? " " : row["AdminTeamName"]),
                               contactname = Convert.ToString((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]),


                           }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting.cs", "FindBrokerForAdminTeam", ex.Message);
            }
            return brokers;

        }
        /// <summary>
        /// Getting Admin team
        /// </summary>
        /// <param name="AccountId"></param>
        /// <returns></returns>
        public AdminDetails GetAdminTeam(int AccountId)
        {
            AdminDetails result = new AdminDetails();
            SqlParameter[] Param = new SqlParameter[1];
            Param[0] = new SqlParameter("@AdminId", AccountId);
            string _procedurename = "GetAdminTeam";
            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procedurename, Param);

                result = (from DataRow row in dt.Rows
                          select new AdminDetails
                          {
                              UserId = Convert.ToInt32(((row["UserId"] == DBNull.Value || row["UserId"] == null) ? "0" : row["UserId"]).ToString()),
                              ContactName = Convert.ToString((row["ContactName"] == DBNull.Value || row["ContactName"] == null) ? " " : row["ContactName"]),
                              AffiliateId = Convert.ToString((row["AffiliateId"] == DBNull.Value || row["AffiliateId"] == null) ? " " : row["AffiliateId"]),
                              AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
                              CompanyName = Convert.ToString((row["CompanyName"] == DBNull.Value || row["CompanyName"] == null) ? " " : row["CompanyName"]),
                              Email = Convert.ToString((row["Email"] == DBNull.Value || row["Email"] == null) ? " " : row["Email"]),
                              Telephone = Convert.ToString((row["Telephone"] == DBNull.Value || row["Telephone"] == null) ? " " : row["Telephone"]),
                              Address = Convert.ToString((row["Address"] == DBNull.Value || row["Address"] == null) ? " " : row["Address"]),
                              TeamName = Convert.ToString((row["CompanyName"] == DBNull.Value || row["CompanyName"] == null) ? " " : row["CompanyName"] + "-(Team)"),


                          }).FirstOrDefault();


                return result ?? new AdminDetails();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetAdminteam", ex.Message);
                return result;
            }
        }
        /// <summary>
        /// Update Attendence
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="TotalAbsentDaysAllowed"></param>
        /// <param name="TotalAbsentDaysUsedManually"></param>
        /// <param name="TotalAbsentDaysUsedBySystem"></param>
        /// <param name="ActivityYear"></param>
        /// <param name="accountid"></param>
        /// <param name="ActionType"></param>
        /// <returns></returns>
        public int UpdateAttendance(int userid, decimal TotalAbsentDaysAllowed, decimal TotalAbsentDaysUsedManually, decimal TotalAbsentDaysUsedBySystem, int ActivityYear, int accountid = 0, int ActionType = 1)
        {
            int _chk = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    SqlParameter[] Param = new SqlParameter[10];
                    Param[0] = new SqlParameter("@UserId", userid);
                    Param[1] = new SqlParameter("@AccountId", accountid);
                    Param[2] = new SqlParameter("@TotalAbsentDaysAllowed", TotalAbsentDaysAllowed);
                    Param[3] = new SqlParameter("@TotalAbsentDaysUsedManually", TotalAbsentDaysUsedManually);
                    Param[4] = new SqlParameter("@TotalAbsentDaysUsedBySystem", TotalAbsentDaysUsedBySystem);
                    Param[5] = new SqlParameter("@ActivityYear", ActivityYear);
                    Param[6] = new SqlParameter("@EntryBy", userId);
                    Param[7] = new SqlParameter("@IsActive", true);
                    Param[8] = new SqlParameter("@IsDelete", false);
                    Param[9] = new SqlParameter("@ActionType", ActionType);
                    string _procedurename = "UserYearWiseAbsentAddUpdate";
                    _chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
                }
                else
                {
                    return -3;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "updateAttendence", ex.Message);
                return 0;
            }
            return _chk;
        }
        /// <summary>
        /// Update Attendence Day
        /// </summary>
        /// <param name="ActivityDate"></param>
        /// <param name="userid"></param>
        /// <param name="TotalHoursAllowed"></param>
        /// <param name="ActionType"></param>
        /// <returns></returns>
        public int UpdateAttendanceDay(string ActivityDate, int userid, decimal TotalHoursAllowed, int ActionType = 1)
        {
            int _chk = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {

                    string activitydate = System.DateTime.Now.ToString("MM/dd/yyyy");
                    if (!string.IsNullOrWhiteSpace(ActivityDate))
                        activitydate = ActivityDate.GetDate().Value.ToString("MM/dd/yyyy");

                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    SqlParameter[] Param = new SqlParameter[8];
                    Param[0] = new SqlParameter("@UserId", userid);
                    Param[1] = new SqlParameter("@AccountId", AccountId);
                    Param[2] = new SqlParameter("@TotalAbsentHrsAllowed", TotalHoursAllowed);
                    Param[3] = new SqlParameter("@EntryBy", userId);
                    Param[4] = new SqlParameter("@IsActive", true);
                    Param[5] = new SqlParameter("@IsDelete", false);
                    Param[6] = new SqlParameter("@ActionType", ActionType);
                    Param[7] = new SqlParameter("@ActivityDate", activitydate);
                    string _procedurename = "UserYearWiseAbsentAddUpdateDay";
                    _chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
                }
                else
                {
                    return -3;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "UpdateAttendenceDay", ex.Message);
                return 0;
            }
            return _chk;
        }

        /// <summary>
        /// Update Admin Team
        /// </summary>
        /// <param name="companyname"></param>
        /// <param name="contactname"></param>
        /// <param name="telephone"></param>
        /// <param name="email"></param>
        /// <param name="accountid"></param>
        /// <returns></returns>
        public int UpdateAdminTeam(string companyname, string contactname, string telephone, string email, int accountid = 0)
        {
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    SqlParameter[] Param = new SqlParameter[6];
                    Param[0] = new SqlParameter("@AccountId", accountid);
                    Param[1] = new SqlParameter("@Contactname", contactname);
                    Param[2] = new SqlParameter("@Companyname", companyname);
                    Param[3] = new SqlParameter("@Email", email);
                    Param[4] = new SqlParameter("@Telephone", telephone);

                    Param[5] = new SqlParameter("@Chk", 0);
                    Param[5].Direction = ParameterDirection.Output;

                    string _procedurename = "UpdateAdminTeam";
                    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                    accountid = Convert.ToInt32(Param[5] != null ? Param[5].SqlValue.ToString() : "0");
                }
                else
                {
                    return -3;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "UpdateAdminTeam", ex.Message);
                return 0;
            }
            return accountid;
        }
        /// <summary>
        /// Delete Client
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int DeleteClient(int userId)
        {
            int accountid = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    SqlParameter[] Param = new SqlParameter[6];
                    Param[0] = new SqlParameter("@AdminId", userId);


                    string _procedurename = "DeleteClient";
                    accountid = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                }
                else
                {
                    return -3;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "DeleteClient", ex.Message);
                return 0;
            }
            return accountid;
        }
        /// <summary>
        /// Addng Admin team
        /// </summary>
        /// <param name="companyname"></param>
        /// <param name="contactname"></param>
        /// <param name="telephone"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public int AddAdminTeam(string companyname, string contactname, string telephone, string email)
        {
            int accountid = 0;
            string affiliateid = "";
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    var AccountId = Convert.ToInt32(con.Session["LoggedInUserAccountId"] ?? "0");
                    var userId = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
                    SqlParameter[] Param = new SqlParameter[8];
                    Param[0] = new SqlParameter("@Contactname", contactname);
                    Param[1] = new SqlParameter("@Companyname", companyname);
                    Param[2] = new SqlParameter("@Email", email);
                    Param[3] = new SqlParameter("@Telephone", telephone);

                    Param[4] = new SqlParameter("@AffiliateId", SqlDbType.VarChar, 200);
                    Param[4].Direction = ParameterDirection.Output;

                    Param[5] = new SqlParameter("@AccountId", 0);
                    Param[5].Direction = ParameterDirection.Output;

                    Param[6] = new SqlParameter("@UserId", 0);
                    Param[6].Direction = ParameterDirection.Output;

                    Param[7] = new SqlParameter("@AdminId", AccountId);


                    string _procedurename = "AddAdminTeam";
                    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                    affiliateid = Convert.ToString(Param[4] != null ? Param[4].SqlValue.ToString() : "");
                    accountid = Convert.ToInt32(Param[6] != null ? Param[6].SqlValue.ToString() : "0");
                    //Send Crdential Mail *****************************************************
                    if (accountid > 0 && !string.IsNullOrWhiteSpace(affiliateid))
                    {
                        string body = "Hi " + contactname + ", <br /><br /><br /><br />";
                        body += "Your email address/User Name: " + email + "<br /><br />";
                        body += "Password: " + affiliateid + " <br /><br /><br /><br />";
                        body += "Regards, <br /><br />";
                        body += "Apptrack Admin";

                        var _secureURL = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["SecureURL"] ?? "");
                        var _contentURL = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["ContentURL"] ?? "");

                        var AppTrackImg = _secureURL + "/Content/images/" + Convert.ToString("apptrack.jpg");
                        var AppTrackLogo = _secureURL + "/Content/images/" + Convert.ToString(HttpContext.Current.Session["SiteLogo"] ?? "logo_6569.jpg");
                        var FooterCopyRight = "© 2014 Finance Technology Ltd. Registered trademarks IntelliCalc®, AppTrack®. All rights reserved";
                        var SupportFooter = "Contact Us on: 0845 521 5153, Specialising in financial technologies & solutions";
                        var _mailTemplate = Codes.Extensions.Mailtemplates(AppTrackImg, AppTrackLogo, body, FooterCopyRight, SupportFooter, _contentURL);

                        //-------------------------------------
                        List<ManDRILLModel> _mandrillOBJ = new List<ManDRILLModel>();

                        var _mailDrilEmails = new List<Mandrill.EmailAddress>();
                        Mandrill.EmailAddress objEm = new Mandrill.EmailAddress();
                        objEm.email = email;
                        objEm.name = contactname;
                        _mailDrilEmails.Add(objEm);

                        //Man DRILL ********************************************************************

                        var domain = "occfinance.com";
                        Mandrill.EmailMessage message = new Mandrill.EmailMessage();
                        var _mandrillMessage = new List<Mandrill.EmailMessage>();

                        message.to = _mailDrilEmails;
                        message.html = _mailTemplate;
                        message.from_email = ConfigurationManager.AppSettings["SMTP_FROM"].ToString();
                        message.from_name = "OCC";
                        message.subject = "Your Apptrack Credential";
                        message.inline_css = true;
                        message.signing_domain = domain;
                        message.AddHeader("X-MC-SigningDomain", domain);
                        message.AddHeader("X-MC-TrackingDomain", domain);
                        _mandrillMessage.Add(message);

                        //********************************************************************************

                        // Manage The ManDRILLModel - 25-07-2014

                        var objMAN = new ManDRILLModel();
                        objMAN.contactId = 0;
                        objMAN.financeid = 0;
                        objMAN.mailcontent = _mailTemplate;
                        objMAN.MandrillMSG = message;
                        objMAN.IsAppContact = false;

                        objMAN.notemsg = "";

                        _mandrillOBJ.Add(objMAN);


                        WebUtility.SendMailViaManDRILL(_mandrillOBJ, userId);
                        //-------------------------------------


                        // Add Default Permission ---------------------------
                        // 1- Admin User, 2- Broker, 3- General User As per the client response ON 21-07-2014
                        try
                        {

                            AddDefaultPermission(1, accountid, AccountId, userId);
                        }
                        catch
                        {
                        }
                    }
                    //*************************************************************************
                }
                else
                {
                    return -3;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "AddAdminTeam", ex.Message);
                return 0;
            }
            return accountid;
        }

        /// <summary>
        /// Registration
        /// </summary>
        /// <param name="companyname"></param>
        /// <param name="contactname"></param>
        /// <param name="telephone"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public int Registration(string companyname, string contactname, string telephone, string email)
        {
            int UserId = 0;
            int accountid = 0;
            try
            {

                SqlParameter[] Param = new SqlParameter[7];
                Param[0] = new SqlParameter("@Contactname", contactname);
                Param[1] = new SqlParameter("@Companyname", companyname);
                Param[2] = new SqlParameter("@Email", email);
                Param[3] = new SqlParameter("@Telephone", telephone);
                Param[4] = new SqlParameter("@UserId", 0);
                Param[4].Direction = ParameterDirection.Output;
                Param[5] = new SqlParameter("@AccountId", 0);
                Param[5].Direction = ParameterDirection.Output;
                Param[6] = new SqlParameter("@parent", 6569);
                string _procedurename = "ClientRegistration";
                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
                accountid = Convert.ToInt32(Param[5] != null ? Param[5].SqlValue.ToString() : "0");
                UserId = Convert.ToInt32(Param[4] != null ? Param[4].SqlValue.ToString() : "0"); // return userid

                if (UserId > 0)
                {

                    try
                    {
                        AddDefaultPermission(1, accountid, 6569, UserId);
                    }
                    catch
                    {
                    }
                }
                //*************************************************************************

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "Registration", ex.Message);
                return 0;
            }
            return UserId;
        }


        public int ActivateClient(int Userid)
        {
            int chk = 0;
            try
            {

                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@Userid", Userid);

                string _procedurename = "ActivateClient";
                chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "ActivateClient", ex.Message);
                return 0;
            }
            return chk;
        }

        #endregion
        /// <summary>
        /// Get Mortgage List
        /// </summary>
        /// <returns></returns>
        public List<MortgageRate> GetMortgageList()
        {

            List<MortgageRate> Mortgage_Rate = new List<MortgageRate>();
            Clients result = new Clients();
            try
            {

                Mortgage_Rate = ctx.tblaccount_mortgagerates.Select(u => new MortgageRate()
                {
                    accountid = u.accountid,
                    Rate = u.rate,
                    LTV = u.ltv
                }).ToList();
                return Mortgage_Rate;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "GetMortgageList", ex.Message);
                return Mortgage_Rate;
            }
        }
        /// <summary>
        /// getting Team member by member id
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public List<tblTeamMember> TeammembersGetByMemberId(int MemberId)
        {

            List<tblTeamMember> _teams = new List<tblTeamMember>();

            try
            {
                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@MemberId", MemberId);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "tblteammembersGetByMemberId", Param);
                _teams = (from DataRow row in dt.Rows
                          select new tblTeamMember
                          {
                              TeamMembersId = Convert.ToInt32(((row["TeamMembersId"] == DBNull.Value || row["TeamMembersId"] == null) ? "0" : row["TeamMembersId"]).ToString()),
                              TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                              IsActive = Convert.ToBoolean(((row["IsActive"] == DBNull.Value || row["IsActive"] == null) ? false : row["IsActive"]).ToString()),
                              IsDelete = Convert.ToBoolean(((row["IsDelete"] == DBNull.Value || row["IsDelete"] == null) ? false : row["IsDelete"]).ToString()),
                              MemberId = Convert.ToInt32(((row["MemberId"] == DBNull.Value || row["MemberId"] == null) ? "0" : row["MemberId"]).ToString()),
                              EntryBy = Convert.ToInt32(((row["EntryBy"] == DBNull.Value || row["EntryBy"] == null) ? "0" : row["EntryBy"]).ToString()),
                              UpdateBy = Convert.ToInt32(((row["UpdateBy"] == DBNull.Value || row["UpdateBy"] == null) ? "0" : row["UpdateBy"]).ToString()),
                              AddDate = (row["AddDate"] == DBNull.Value || row["AddDate"] == null) ? (DateTime?)null : Convert.ToDateTime(row["AddDate"]),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "TeamMemberGetbyMemberID");
            }
            return _teams;
        }
        /// <summary>
        /// User Permission Get all by account Id
        /// </summary>
        /// <param name="AccountId"></param>
        /// <param name="UserId"></param>
        /// <param name="UserType"></param>
        /// <returns></returns>
        public List<UserPermissionModel> UserPermissionGetAllByAccountId(int AccountId, int UserId, int UserType)
        {
            List<UserPermissionModel> _teams = new List<UserPermissionModel>();
            try
            {
                SqlParameter[] Param = new SqlParameter[3];
                Param[0] = new SqlParameter("@AccountId", AccountId);
                Param[1] = new SqlParameter("@UserId", UserId);
                Param[2] = new SqlParameter("@UserType", UserType);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "tblUserPermissionGetAllByAccountId", Param);
                _teams = (from DataRow row in dt.Rows
                          select new UserPermissionModel
                          {
                              userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                              account = Convert.ToInt32(((row["account"] == DBNull.Value || row["account"] == null) ? "0" : row["account"]).ToString()),
                              administrator = Convert.ToBoolean(((row["administrator"] == DBNull.Value || row["administrator"] == null) ? false : row["administrator"]).ToString()),
                              broker = Convert.ToBoolean(((row["broker"] == DBNull.Value || row["broker"] == null) ? false : row["broker"]).ToString()),
                              brokername = Convert.ToString(((row["brokername"] == DBNull.Value || row["brokername"] == null) ? "" : row["brokername"]).ToString()),
                              PermissionIds = Convert.ToString(((row["PermissionIds"] == DBNull.Value || row["PermissionIds"] == null) ? "" : row["PermissionIds"]).ToString()),
                              PermissionNames = Convert.ToString(((row["PermissionNames"] == DBNull.Value || row["PermissionNames"] == null) ? "" : row["PermissionNames"]).ToString()),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "UserPermissionGetAllByaccountID", ex.Message);
            }
            return _teams;
        }

        /// <summary>
        /// get All activate client by Account Id
        /// </summary>
        /// <param name="AccountId"></param>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public List<InActiveUser> GetAllInActiveClientByAccountId(int AccountId, int UserId)
        {

            List<InActiveUser> _teams = new List<InActiveUser>();

            try
            {
                SqlParameter[] Param = new SqlParameter[3];
                Param[0] = new SqlParameter("@AccountId", AccountId);
                Param[1] = new SqlParameter("@UserId", UserId);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllInActiveClientByAccountId", Param);
                _teams = (from DataRow row in dt.Rows
                          select new InActiveUser
                          {
                              userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                              account = Convert.ToInt32(((row["account"] == DBNull.Value || row["account"] == null) ? "0" : row["account"]).ToString()),
                              administrator = Convert.ToBoolean(((row["administrator"] == DBNull.Value || row["administrator"] == null) ? false : row["administrator"]).ToString()),
                              broker = Convert.ToBoolean(((row["broker"] == DBNull.Value || row["broker"] == null) ? false : row["broker"]).ToString()),
                              brokername = Convert.ToString(((row["brokername"] == DBNull.Value || row["brokername"] == null) ? "" : row["brokername"]).ToString()),
                              PermissionIds = Convert.ToString(((row["PermissionIds"] == DBNull.Value || row["PermissionIds"] == null) ? "" : row["PermissionIds"]).ToString()),
                              PermissionNames = Convert.ToString(((row["PermissionNames"] == DBNull.Value || row["PermissionNames"] == null) ? "" : row["PermissionNames"]).ToString()),
                              username = Convert.ToString(((row["username"] == DBNull.Value || row["username"] == null) ? "" : row["username"]).ToString()),
                              password = Convert.ToString(((row["password"] == DBNull.Value || row["password"] == null) ? "" : row["password"]).ToString()),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetallnActivateclientByAccoutnId", ex.Message);
            }
            return _teams;
        }

        /// <summary>
        /// Get All Permissions
        /// </summary>
        /// <returns></returns>
        public List<PermissionModel> GetAllPermissions()
        {

            List<PermissionModel> _teams = new List<PermissionModel>();

            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllPermissions");
                _teams = (from DataRow row in dt.Rows
                          select new PermissionModel
                          {
                              PermissionId = Convert.ToInt32(((row["PermissionId"] == DBNull.Value || row["PermissionId"] == null) ? "0" : row["PermissionId"]).ToString()),
                              AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
                              PermissionName = Convert.ToString(((row["PermissionName"] == DBNull.Value || row["PermissionName"] == null) ? "" : row["PermissionName"]).ToString()),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetAllPermission", ex.Message);
            }
            return _teams;
        }

        /// <summary>
        /// Team Members Delete by Member Id
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public int TeamMembersDeleteByMemberId(int MemberId)
        {
            int _chk = 0;
            try
            {
                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@MemberId", MemberId);
                _chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblTeamMembersDeleteByMemberId", Param);
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "TeamMemberDelteByMemberId", ex.Message);
            }

            return _chk;
        }

        public int TeamMembersDeleteByTeamId(int TeamId)
        {
            int _chk = 0;
            try
            {
                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@TeamId", TeamId);
                _chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblTeamMembersDeleteByTeamId", Param);
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "TeammembersDeleteByTeamId", ex.Message);
            }

            return _chk;
        }

        #region Add Team Members
        /// <summary>
        /// Adding Team Members
        /// </summary>
        /// <param name="AdminTeamId"></param>
        /// <param name="AdminId"></param>
        /// <param name="MemberId"></param>
        /// <param name="userId"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public int AddAdminTeamMembers(int AdminTeamId, int AdminId, int MemberId, int userId, int Type)
        {
            int chk = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    SqlParameter[] Param = new SqlParameter[7];
                    Param[0] = new SqlParameter("@AdminTeamId", AdminTeamId);
                    Param[1] = new SqlParameter("@AdminId", AdminId);
                    Param[2] = new SqlParameter("@MemberId", MemberId);
                    Param[3] = new SqlParameter("@EntryBy", userId);
                    Param[4] = new SqlParameter("@IsActive", true);
                    Param[5] = new SqlParameter("@IsDelete", false);
                    Param[6] = new SqlParameter("@Type", Type);

                    string _procedurename = "tblAdminTeammebersADD";

                    chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                }

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "AddAdminTeammember", ex.Message);
            }
            return chk;
        }


        #endregion
        /// <summary>
        /// Admin team Members Get By Member Id
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public List<tblAdminTeammeber> AdminTeammembersGetByMemberId(int MemberId)
        {

            List<tblAdminTeammeber> _teams = new List<tblAdminTeammeber>();

            try
            {
                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@MemberId", MemberId);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "tblAdminTeammebersGetAllByMemberId", Param);
                _teams = (from DataRow row in dt.Rows
                          select new tblAdminTeammeber
                          {
                              AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                              AdminTeamId = Convert.ToInt32(((row["AdminTeamId"] == DBNull.Value || row["AdminTeamId"] == null) ? "0" : row["AdminTeamId"]).ToString()),
                              AdminTeamMembersId = Convert.ToInt32((row["AdminTeamMembersId"] == DBNull.Value || row["AdminTeamMembersId"] == null) ? "0" : row["AdminTeamMembersId"]),
                              MemberId = Convert.ToInt32(((row["MemberId"] == DBNull.Value || row["MemberId"] == null) ? "0" : row["MemberId"]).ToString()),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "AdminTeammembersGetByMemberId", ex.Message);
            }
            return _teams;
        }
        /// <summary>
        /// Email Tracking Add
        /// </summary>
        /// <param name="SenderId"></param>
        /// <param name="ReceiverId"></param>
        /// <param name="ReceiverEmail"></param>
        /// <param name="MailContent"></param>
        /// <param name="IsAotherContact"></param>
        /// <param name="EntryBy"></param>
        /// <param name="fromEmail"></param>
        /// <param name="fromName"></param>
        /// <param name="subject"></param>
        /// <param name="inlinecss"></param>
        /// <param name="signingDomain"></param>
        /// <param name="financeId"></param>
        /// <param name="mailContentString"></param>
        /// <param name="notemsg"></param>
        /// <param name="templateName"></param>
        /// <param name="status"></param>
        /// <param name="isfollowup"></param>
        /// <param name="emailStatus"></param>
        /// <param name="mandrilmsg"></param>
        /// <returns></returns>
        public int EmailTrackingAdd(int SenderId, int ReceiverId, string ReceiverEmail, string MailContent, bool IsAotherContact, int EntryBy, string fromEmail, string fromName, string subject, bool inlinecss, string signingDomain, int financeId, string mailContentString, string notemsg, string templateName, int status, bool isfollowup, int emailStatus = 1, IEnumerable<EmailAddress> mandrilmsg = null)
        {
            int chk = 0;
            try
            {
                if (con.Session["LoggedInUserAccountId"] != null)
                {
                    SqlParameter[] Param = new SqlParameter[21];
                    Param[0] = new SqlParameter("@SenderId", SenderId);
                    Param[1] = new SqlParameter("@ReceiverId", ReceiverId);
                    Param[2] = new SqlParameter("@ReceiverEmail", ReceiverEmail);
                    Param[3] = new SqlParameter("@MailContent", MailContent);
                    Param[4] = new SqlParameter("@IsAotherContact", IsAotherContact);
                    Param[5] = new SqlParameter("@EntryBy", EntryBy);
                    Param[6] = new SqlParameter("@IsActive", true);
                    Param[7] = new SqlParameter("@IsDelete", false);
                    Param[8] = new SqlParameter("@FromEmail", fromEmail);
                    Param[9] = new SqlParameter("@FromName", fromName);
                    Param[10] = new SqlParameter("@Subject", subject);
                    Param[11] = new SqlParameter("@InlineCss", inlinecss);
                    Param[12] = new SqlParameter("@SigningDomain", signingDomain);
                    Param[13] = new SqlParameter("@financeId", financeId);
                    Param[14] = new SqlParameter("@MailContentstring", mailContentString);
                    Param[15] = new SqlParameter("@NoteMsg", notemsg);
                    Param[16] = new SqlParameter("@TemplateName", templateName);
                    Param[17] = new SqlParameter("@status", status);
                    Param[19] = new SqlParameter("@Isfollowup", isfollowup);
                    Param[20] = new SqlParameter("@EmailStatus", emailStatus);

                    //  Param[18] = new SqlParameter("@new_identity",ParameterDirection.Output);
                    SqlParameter id = new SqlParameter();
                    id.SqlDbType = SqlDbType.Int;
                    id.Direction = ParameterDirection.Output;
                    id.ParameterName = "@new_identity";
                    Param[18] = id;
                    string _procedurename = "EmailTrackingAdd";

                    chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
                    var trackingId = Param[18].Value;

                    if (chk > 0 && trackingId != null)
                    {
                        if (mandrilmsg != null)
                        {
                            foreach (var v in mandrilmsg)
                            {
                                SqlParameter[] Param1 = new SqlParameter[3];
                                Param1[0] = new SqlParameter("@TrackingId", trackingId);
                                Param1[1] = new SqlParameter("@Email", v.email);
                                Param1[2] = new SqlParameter("@Name", v.name);
                                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "AddEmailForMail", Param1);
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "EmailTrackingAdd", ex.Message);
            }
            return chk;
        }
        /// <summary>
        /// Getting Broker List for tree structure
        /// </summary>
        /// <param name="userid">User Id</param>
        /// <returns>Broker List</returns>
        public List<Clients> GetBrokersWithTeams(int userid)
        {
            Q_Application _obj = new Q_Application();
            int teamId = 0;
            string brokersIds = "";
            int _userType = 0;
            int _accountId = 0;

            _userType = _obj.CheckUserType(userid, out teamId, out _accountId, out brokersIds);

            var _Id = 0;

            if (_userType == (int)UserType.Admin)
                _Id = _accountId;
            else if (_userType == (int)UserType.Manager)
                _Id = teamId;
            else if (_userType == (int)UserType.Broker)
                _Id = userid;
            else if (_userType == (int)UserType.GeneralUser)
            {
                int account = ctx.tblaccounts.Where(u => u.accountid == _accountId).Select(u => u.parent.HasValue ? u.parent.Value : 0).FirstOrDefault();
                _Id = account;
            }


            SqlParameter[] Param = new SqlParameter[2];
            Param[0] = new SqlParameter("@UserType", _userType);
            Param[1] = new SqlParameter("@ID", _Id);

            List<Clients> _teams = new List<Clients>();

            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetBrokersWithTeams", Param);
                _teams = (from DataRow row in dt.Rows
                          select new Clients
                          {
                              clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                              parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                              companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                              TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                              TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                              affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                              telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                              email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                              contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                              address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                              userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Qsetting", "GetBrokerWithTeams", ex.Message);
            }
            return _teams;
        }
        /// <summary>
        /// Get Referral Brokers With Teams
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public List<Clients> GetReferralBrokersWithTeams(int userid)
        {
            Q_Application _obj = new Q_Application();
            int teamId = 0;
            string brokersIds = "";
            int _userType = 0;
            int _accountId = 0;
            _userType = _obj.CheckUserType(userid, out teamId, out _accountId, out brokersIds);

            var _Id = 0;

            if (_userType == (int)UserType.Admin)
                _Id = _accountId;
            else if (_userType == (int)UserType.Manager)
                _Id = teamId;
            else if (_userType == (int)UserType.Broker)
                _Id = userid;
            else if (_userType == (int)UserType.GeneralUser)
            {
                int account = ctx.tblaccounts.Where(u => u.accountid == _accountId).Select(u => u.parent.HasValue ? u.parent.Value : 0).FirstOrDefault();
                _Id = account;
            }


            SqlParameter[] Param = new SqlParameter[2];
            Param[0] = new SqlParameter("@UserType", 3);
            Param[1] = new SqlParameter("@ID", _Id);

            List<Clients> _teams = new List<Clients>();

            try
            {
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetReferralBrokersWithTeams", Param);
                _teams = (from DataRow row in dt.Rows
                          select new Clients
                          {
                              clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                              parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                              companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                              TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                              TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                              affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                              telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                              email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                              contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                              address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                              userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),

                          }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetRferralBrokersWithTeams", ex.Message);
            }
            return _teams;
        }
        /// <summary>
        /// Finding Referral Broker for logged in user with user id
        /// </summary>
        /// <param name="accid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public List<Clients> FindReferralBrokerForLoggedInUserWithUserId(int accid, int userid)
        {
            bool flag = false;
            List<Clients> brokers = new List<Clients>();
            Q_Application _obj = new Q_Application();

            try
            {

                int teamId = 0;
                string brokersIds = "";
                int _userType = 0;
                int _accountId = 0;
                _userType = _obj.CheckUserType(userid, out teamId, out _accountId, out brokersIds);

                //Updated With Store Procedure For Navigation Delay ********* 7/7/2014 ********************************************

                var _Id = 0;

                if (_userType == (int)UserType.Admin)
                    _Id = _accountId;
                else if (_userType == (int)UserType.Manager)
                    _Id = teamId;
                else if (_userType == (int)UserType.Broker)
                    _Id = userid;
                else if (_userType == (int)UserType.GeneralUser)
                {
                    int account = ctx.tblaccounts.Where(u => u.accountid == userid).Select(u => u.parent.HasValue ? u.parent.Value : 0).FirstOrDefault();
                    _Id = account;
                }

                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", _userType);
                Param[1] = new SqlParameter("@ID", _Id);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindReferralBrokerForLoggedInUserWithUserId", Param);

                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                               TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString()),
                           }).ToList();

                //**********************************************************************************

                return brokers;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "FindReferralBrokerForLoggedInUserWithUserId", ex.Message);
                return brokers;
            }
        }
        /// <summary>
        /// Getting and setting Permission
        /// </summary>
        /// <param name="loginuserid"></param>
        public void GetSetPermission(int loginuserid)
        {
            try
            {
                if (SessionWrapper.PermissionSession == null)
                {
                    PermissionSession objS = new PermissionSession();
                    SessionWrapper.PermissionSession = objS;
                }
                SessionWrapper.PermissionSession.UserId = loginuserid;

                Q_Application obj = new Q_Application();
                if (obj.CheckIsSuperAdmin(loginuserid) == 1)
                {
                    SessionWrapper.PermissionSession.IsFullPermission = true;
                    SessionWrapper.PermissionSession.IsViewAllData = true;
                    SessionWrapper.PermissionSession.IsUpdateAllData = true;
                    SessionWrapper.PermissionSession.IsPrintAllData = true;
                }
                else
                {
                    SessionWrapper.PermissionSession.IsFullPermission = false;

                    List<UserPermissionModel> _permissions = new List<UserPermissionModel>();
                    SqlParameter[] Param = new SqlParameter[1];
                    Param[0] = new SqlParameter("@UserId", loginuserid);
                    DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "tblUserPermissionGetByUserId", Param);
                    _permissions = (from DataRow row in dt.Rows
                                    select new UserPermissionModel
                                    {
                                        userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                                        PermissionId = Convert.ToInt32(((row["PermissionId"] == DBNull.Value || row["PermissionId"] == null) ? "0" : row["PermissionId"]).ToString()),
                                        PermissionNames = Convert.ToString((row["PermissionName"] == DBNull.Value || row["PermissionName"] == null) ? "" : row["PermissionName"]),

                                    }).ToList();

                    if (_permissions.Any(c => c.userid == loginuserid && c.PermissionId == (int)Permission.ViewData))
                    {

                        SessionWrapper.PermissionSession.IsViewAllData = true;
                    }
                    else
                    {
                        SessionWrapper.PermissionSession.IsViewAllData = false;
                    }

                    if (_permissions.Any(c => c.userid == loginuserid && c.PermissionId == (int)Permission.UpdateData))
                    {

                        SessionWrapper.PermissionSession.IsUpdateAllData = true;
                    }
                    else
                    {
                        SessionWrapper.PermissionSession.IsUpdateAllData = false;
                    }

                    if (_permissions.Any(c => c.userid == loginuserid && c.PermissionId == (int)Permission.PrintReport))
                    {
                        SessionWrapper.PermissionSession.IsPrintAllData = true;
                    }
                    else
                    {
                        SessionWrapper.PermissionSession.IsPrintAllData = false;
                    }


                }

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetsetPermission", ex.Message);
            }
        }


        #region GetYearsList
        /// <summary>
        /// Getting Years for autocomplete
        /// </summary>
        /// <param name="SearchText"></param>
        /// <returns></returns>
        public List<string> GetYearsForAutoComplete(string SearchText)
        {
            List<string> _tblusers = new List<string>();
            try
            {
                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@SearchText", SearchText);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetYearsForAutoComplete", Param);
                _tblusers = (from DataRow row in dt.Rows
                             select Convert.ToString(((row["ActivityYear"] == DBNull.Value || row["ActivityYear"] == null) ? "" : row["ActivityYear"]).ToString())
                             ).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetYearsForAutocomplete", ex.Message);
            }

            return _tblusers;
        }
        #endregion

        #region GetBrokersNameList
        /// <summary>
        /// Getting Brokers for autocomplete
        /// </summary>
        /// <param name="SearchText"></param>
        /// <param name="UserType"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public List<string> GetBrokersForAutoComplete(string SearchText, int UserType, int ID)
        {
            List<string> _tblusers = new List<string>();
            try
            {
                SqlParameter[] Param = new SqlParameter[3];
                Param[0] = new SqlParameter("@SearchText", SearchText);
                Param[1] = new SqlParameter("@UserType", UserType);
                Param[2] = new SqlParameter("@ID", ID);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetBrokersForAutoComplete", Param);
                _tblusers = (from DataRow row in dt.Rows
                             select Convert.ToString(((row["brokername"] == DBNull.Value || row["brokername"] == null) ? "" : row["brokername"]).ToString())
                             ).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetBrokerForComplete", ex.Message);
            }

            return _tblusers;
        }

        #endregion

        #region GetTeamManager
        /// <summary>
        /// Get Team Manager Dropdown
        /// </summary>
        /// <param name="UserType"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public List<tbluser> GetTeamManagerDropDown(int UserType, int ID)
        {
            List<tbluser> _tblmanagers = new List<tbluser>();
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", UserType);
                Param[1] = new SqlParameter("@ID", ID);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetTeamManager", Param);
                _tblmanagers = (from DataRow row in dt.Rows
                                select new tbluser
                                {
                                    TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                                    userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                                    brokername = Convert.ToString((row["brokername"] == DBNull.Value || row["brokername"] == null) ? " " : row["brokername"])
                                }).ToList();
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetteamManagerdropDown", ex.Message);
            }
            return _tblmanagers;
        }
        #endregion


        /***************************************************
         * Code Block for E-Mail subscription/ unsubscription
         *
         * Created On : 31/01/2015
         *
         * ************************************************/
        /// <summary>
        /// Getting User Subscription
        /// </summary>
        /// <param name="loggedinuserid"></param>
        /// <param name="pageNo"></param>
        /// <param name="pageSize"></param>
        /// <param name="brokers"></param>
        /// <param name="clients"></param>
        /// <param name="username"></param>
        /// <param name="isSusbcribed"></param>
        /// <returns></returns>
        public DataTable GetUSerSubscription(int loggedinuserid, int pageNo, int pageSize, string brokers, string clients, string username, bool isSusbcribed = false)
        {
            DataTable subscribedUsers = null;
            try
            {
                SqlParameter[] Param = new SqlParameter[7];
                Param[0] = new SqlParameter("@Page", pageNo);
                Param[1] = new SqlParameter("@RecsPerPage", pageSize);
                Param[2] = new SqlParameter("@SubscribedUsers", isSusbcribed ? false : true /* for tweaking checked property */);
                Param[3] = new SqlParameter("@Broker", string.IsNullOrEmpty(brokers) ? null : brokers.Trim());
                Param[4] = new SqlParameter("@Client", string.IsNullOrEmpty(clients) ? null : clients.Trim());
                Param[5] = new SqlParameter("@UserName", string.IsNullOrEmpty(username) ? null : username.Trim());
                Param[6] = new SqlParameter("@CurrentUserId", loggedinuserid);

                subscribedUsers = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "proc_SubscribedUsersList", Param);


            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetUserSubscription", ex.Message);
                throw ex;
            }
            return subscribedUsers;
        }
        /// <summary>
        /// Get All Clients
        /// </summary>
        /// <param name="loggedinuserid"></param>
        /// <returns></returns>
        public DataTable GetClients(int loggedinuserid)
        {
            DataTable subscribedUsers = null;
            SqlParameter[] par = new SqlParameter[]
            {
            new SqlParameter("@CurrentUserId", loggedinuserid)
            };
            try
            {
                subscribedUsers = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "proc_GetClients_Update", par);
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetClients", ex.Message);
                throw ex;
            }
            return subscribedUsers;
        }
        /// <summary>
        /// Getting Brokers
        /// </summary>
        /// <param name="loggedinuserid"></param>
        /// <returns></returns>
        public DataTable GetBrokers(int loggedinuserid)
        {
            DataTable subscribedUsers = null;

            SqlParameter[] par = new SqlParameter[]
            {
            new SqlParameter("@Id",loggedinuserid)
            };
            try
            {
                subscribedUsers = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "proc_GetBrokers", par);
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetBrokers", ex.Message);
                throw ex;
            }
            return subscribedUsers;
        }
        #region Getting all User

        /// <summary>
        /// Get all the user details to show in assign notes functionality.
        /// </summary>
        /// <param name="accId"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public List<Users> GetAllUser(int accId, int userid)
        {
            bool IsSubAdmin = ctx.tblusers.Where(x => x.userid == userid && x.administrator == true && x.active == true && x.broker == false && x.AdminId != null && x.AdminId != 0).Any();
            if (IsSubAdmin)
            {
                accId = ctx.tblusers.Where(x => x.userid == userid).FirstOrDefault().AdminId ?? 0;
            }
            List<Users> allUser = new List<Users>();
            allUser = (from u in ctx.tblusers
                       where (u.userid != userid && u.account == accId && u.active == true) || (u.AdminId == accId && u.active == true && u.administrator == true && u.broker == false && u.userid != userid)
                       select new Users()
                       {
                           userName = u.brokername,
                           userId = u.userid
                       }).OrderBy(x => x.userName).ToList();

            return allUser;
        }
        /// <summary>
        /// Getting all user for manage pwd
        /// </summary>
        /// <param name="accId"></param>
        /// <param name="userid"></param>
        /// <param name="usertype"></param>
        /// <param name="pageNo"></param>
        /// <param name="pagesize"></param>
        /// <param name="searchName"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<Users> GetAllUserForManagePwd(int accId, int userid, int usertype, int pageNo, int pagesize, string searchName, out int total)
        {


            total = 0;
            int Type = 0;
            List<Users> allUser = new List<Users>();
            SqlParameter[] Param = new SqlParameter[2];
            if (usertype > 0)
                Param[0] = new SqlParameter("@usertype", usertype);
            if (!string.IsNullOrEmpty(searchName))
                Param[1] = new SqlParameter("@searchName", searchName);
            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetDataForManagePwd", Param);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                allUser = Occfinance.Code.DataTableHelper.CreateListFromTableForAll<Users>(ds.Tables[0]).ToList();
            }
            total = allUser.Count;
            return allUser;
        }
        /// <summary>
        /// Getting User type
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userType"></param>
        /// <returns></returns>
        public string GetUserType(int userId, out int userType)
        {
            userType = 0;
            string uType = string.Empty;
            if (ctx.tblusers.Any(u => u.userid == userId && u.active == true && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == false || u.broker.Value == false) && (!u.AdminId.HasValue || u.AdminId.Value == 0)))
            {
                uType = "CEO/Admin";
                userType = Convert.ToInt32(UserTypes.Admin);
            }
            else if (ctx.tblusers.Any(u => u.userid == userId && u.active == true && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == false || u.broker.Value == false) && (u.AdminId.HasValue && u.AdminId.Value > 0)))
            {
                uType = "Senior Adviser";
                userType = Convert.ToInt32(UserTypes.SubAdmin);
            }
            else if (ctx.tblusers.Any(u => u.userid == userId && u.active == true && (u.administrator.HasValue == false || u.administrator.Value == false) && (u.broker.HasValue == true && u.broker.Value == true)))
            {
                uType = "Adviser";
                userType = Convert.ToInt32(UserTypes.Broker);
            }
            else if (ctx.tblusers.Any(u => u.userid == userId && u.active == true && (u.administrator.HasValue == false || u.administrator.Value == false) && (u.broker.HasValue == false || u.broker.Value == false)))
            {
                uType = "Introducer";
                userType = Convert.ToInt32(UserTypes.GeneralUser);
            }

            return uType;
        }
        #endregion Getting all User

        #region [Getting all Existing clients]
        /// <summary>
        /// Get all Existing clients
        /// </summary>
        /// <param name="pageNo"></param>
        /// <param name="pagesize"></param>
        /// <param name="searchName"></param>
        /// <param name="loggedInUser"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<ExistingClients> GetAllExistingClients(int pageNo, int pagesize, string searchName, int loggedInUser, out int total)
        {


            total = 0;

            List<ExistingClients> allclients = new List<ExistingClients>();
            SqlParameter[] Param = new SqlParameter[4];

            if (!string.IsNullOrEmpty(searchName))
                Param[0] = new SqlParameter("@searchName", searchName);
            Param[1] = new SqlParameter("@PageNo", pageNo);
            Param[2] = new SqlParameter("@PageSize", pagesize);
            Param[3] = new SqlParameter("@loggedInUser", loggedInUser);
            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetContactForfactFind", Param);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                allclients = Occfinance.Code.DataTableHelper.CreateListFromTableForAll<ExistingClients>(ds.Tables[0]).ToList();
                total = allclients[0].TotalCount;
            }

            return allclients;
        }


        #endregion

        #region[Defualt Currency Setting ]
        public string GetDefaultCurrency()
        {
            try
            {
                db_occfinance_5572Entities _ctx = new db_occfinance_5572Entities();
                var defaultCurrency = _ctx.tblDefaultCurrencySettings.Where(x => x.IsDefault == true).FirstOrDefault();

                return defaultCurrency == null ? string.Empty : defaultCurrency.Value;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetDeafultCurrency", ex.Message);
                return string.Empty;
            }
        }

        public void UpdateDefaultCurrency(string val)
        {
            try
            {
                db_occfinance_5572Entities _ctx = new db_occfinance_5572Entities();
                var defaultCurrency = _ctx.tblDefaultCurrencySettings.Where(x => x.Value == val).FirstOrDefault();
                var allCurrency = _ctx.tblDefaultCurrencySettings.ToList();
                if (defaultCurrency != null)
                {
                    foreach (var v in allCurrency)
                    {
                        v.IsDefault = false;
                    }
                    defaultCurrency.IsDefault = true;
                    _ctx.SaveChanges();

                    HttpContext.Current.Session["DefaultCurrency"] = defaultCurrency.Value;
                }
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "GetDeafultCurrency", ex.Message);
            }

        }
        #endregion


        #region webservice Setting methode

        #region Add broker from webservice

        /// <summary>
        /// WSAddBroker from webservice
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="brokername"></param>
        /// <param name="username"></param>
        /// <param name="accountid"></param>
        /// <param name="TeamsArray"></param>
        /// <param name="PrimaryContact"></param>
        /// <param name="SecondaryContact"></param>
        /// <param name="Address1"></param>
        /// <param name="Address2"></param>
        /// <param name="OtherDetails"></param>
        /// <param name="title"></param>
        /// <param name="fax"></param>
        /// <param name="qualification"></param>
        /// <param name="brkImage"></param>
        /// <returns></returns>
        public bool WSAddBroker(int _userId, int _accountId, string brokername, string username, out int accountid, List<tblTeamMember> TeamsArray, string PrimaryContact, string SecondaryContact, string Address1, string Address2, string OtherDetails, string title, string fax, string qualification, string brkImage = "", string path = "")
        {
            bool result = false;
            string fileName = "noimage.jpg";
            string fullImagePath = "";
            accountid = 0;
            try
            {
                if (ctx.tblusers.Where(c => c.username.ToLower().Trim() == username.ToLower().Trim() && c.active == true && !string.IsNullOrEmpty(c.username)).Any())
                {
                    result = false;
                }
                else
                {
                    try
                    {
                        if (!string.IsNullOrWhiteSpace(brkImage))
                        {
                            fileName = Guid.NewGuid().ToString() + ".jpg";
                            fullImagePath = path + fileName;
                            byte[] imageBytes = Convert.FromBase64String(brkImage);
                            MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);
                            ms.Write(imageBytes, 0, imageBytes.Length);
                            System.Drawing.Image image = System.Drawing.Image.FromStream(ms, true);
                            image.Save(fullImagePath);
                        }
                    }
                    catch (Exception ex)
                    {
                        fileName = "noimage.jpg";
                    }

                    int parentaccountId = _accountId;

                    var u = ctx.tblusers.Add(new tbluser()
                    {
                        account = parentaccountId,
                        username = username,
                        password = "test",
                        active = true,
                        administrator = false,
                        Title = title,
                        brokername = brokername,
                        broker = true,
                        PrimaryContact = PrimaryContact ?? "",
                        SecondaryContact = SecondaryContact ?? "",
                        Address1 = Address1 ?? "",
                        Address2 = Address2 ?? "",
                        FAX = fax,
                        Qualification = qualification,
                        OtherDetails = OtherDetails ?? "",
                        BrokerImage = fileName

                    });
                    ctx.SaveChanges();
                    result = true;
                    accountid = u.userid;

                    //create Thumb
                    if (File.Exists(fullImagePath))
                    {
                        string thumbPath = path + "Thumb/" + u.userid + ".jpg";
                        System.Drawing.Image image = System.Drawing.Image.FromFile(fullImagePath);
                        System.Drawing.Image thumb = image.GetThumbnailImage(100, 100, () => false, IntPtr.Zero);
                        thumb.Save(thumbPath, System.Drawing.Imaging.ImageFormat.Jpeg);
                    }

                    string body = "Hi " + brokername + ", <br /><br />";
                    body += "Your email id: " + username + "<br /><br />";
                    body += "Password: test <br /><br />";
                    body += "Regards: <br /><br />";
                    body += "Apptrack Admin";

                    //-------------------------------------
                    List<ManDRILLModel> _mandrillOBJ = new List<ManDRILLModel>();

                    var _mailDrilEmails = new List<Mandrill.EmailAddress>();
                    Mandrill.EmailAddress objEm = new Mandrill.EmailAddress();
                    objEm.email = username;
                    objEm.name = brokername;
                    _mailDrilEmails.Add(objEm);

                    //Man DRILL ********************************************************************

                    var domain = "occfinance.com";
                    Mandrill.EmailMessage message = new Mandrill.EmailMessage();
                    var _mandrillMessage = new List<Mandrill.EmailMessage>();

                    message.to = _mailDrilEmails;
                    message.html = body;
                    message.from_email = ConfigurationManager.AppSettings["SMTP_FROM"].ToString();
                    message.from_name = "OCC";
                    message.subject = "Your Apptrack Credential";
                    message.inline_css = true;
                    message.signing_domain = domain;
                    message.AddHeader("X-MC-SigningDomain", domain);
                    message.AddHeader("X-MC-TrackingDomain", domain);
                    _mandrillMessage.Add(message);

                    //********************************************************************************

                    // Manage The ManDRILLModel - 25-07-2014

                    var objMAN = new ManDRILLModel();
                    objMAN.contactId = 0;
                    objMAN.financeid = 0;
                    objMAN.mailcontent = body;
                    objMAN.MandrillMSG = message;
                    objMAN.IsAppContact = false;

                    objMAN.notemsg = "";

                    _mandrillOBJ.Add(objMAN);

                    int userId = _userId;
                    WebUtility.SendMailViaManDRILL(_mandrillOBJ, userId);
                    //-------------------------------------


                    int _type = 1;
                    if (TeamsArray != null)
                    {
                        foreach (var obj in TeamsArray)
                        {
                            if (obj.IsActive.HasValue && obj.IsActive.Value == true)
                                _type = 1;
                            else
                                _type = 2;

                            WSAddTeamMembers(_userId, _accountId, obj.TeamId.HasValue ? obj.TeamId.Value : 0, u.userid, userId, _type);
                        }
                    }
                    WSAddAdminTeamMembers(_userId, _accountId, parentaccountId, parentaccountId, u.userid, userId, 1);

                    // Add Default Permission ---------------------------
                    // 1- Admin User, 2- Broker, 3- General User As per the client response ON 21-07-2014
                    try
                    {
                        AddDefaultPermission(2, u.userid, parentaccountId, userId);
                    }
                    catch
                    {
                    }


                }
                return result;
            }
            catch (Exception)
            {
                return result;
            }
        }

        #endregion

        #region Update Broker
        /// <summary>
        /// Updating broker
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="brokername"></param>
        /// <param name="username"></param>
        /// <param name="brokerid"></param>
        /// <param name="accountid"></param>
        /// <param name="TeamsArray"></param>
        /// <param name="PrimaryContact"></param>
        /// <param name="SecondaryContact"></param>
        /// <param name="Address1"></param>
        /// <param name="Address2"></param>
        /// <param name="OtherDetails"></param>
        /// <param name="title"></param>
        /// <param name="fax"></param>
        /// <param name="qualification"></param>
        /// <param name="brkImage"></param>
        /// <returns></returns>
        public bool WSUpdateBroker(int _userId, int _accountId, string brokername, string username, int brokerid, out int accountid, List<tblTeamMember> TeamsArray, string PrimaryContact, string SecondaryContact, string Address1, string Address2, string OtherDetails, string title, string fax, string qualification, string brkImage = "", string path = "")
        {
            bool result = false;
            string fileName = string.Empty;
            string fullImagePath = "";
            accountid = 0;
            try
            {
                if (brokerid > 0)
                {
                    if (ctx.tblusers.Where(c => c.username.ToLower().Trim() == username.ToLower().Trim() && c.active == true && c.userid != brokerid && !string.IsNullOrEmpty(c.username)).Any())
                    {
                        result = false;
                    }
                    else
                    {
                        try
                        {
                            if (!string.IsNullOrWhiteSpace(brkImage))
                            {
                                fileName = Guid.NewGuid().ToString() + ".jpg";
                                fullImagePath = path + fileName;
                                byte[] imageBytes = Convert.FromBase64String(brkImage);
                                MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);
                                ms.Write(imageBytes, 0, imageBytes.Length);
                                System.Drawing.Image image = System.Drawing.Image.FromStream(ms, true);
                                image.Save(fullImagePath);
                            }
                        }
                        catch
                        {
                            fileName = "";
                        }

                        int parentaccountId = _accountId;
                        var u = ctx.tblusers.Where(c => c.userid == brokerid).FirstOrDefault();
                        u.account = parentaccountId;//broker.accountid,
                        u.username = username;
                        u.brokername = brokername;
                        u.Title = title;
                        u.PrimaryContact = PrimaryContact ?? "";
                        u.SecondaryContact = SecondaryContact ?? "";
                        u.Address1 = Address1 ?? "";
                        u.Address2 = Address2 ?? "";
                        u.FAX = fax;
                        u.Qualification = qualification;
                        u.OtherDetails = OtherDetails ?? "";
                        if (!string.IsNullOrWhiteSpace(fileName))
                        {
                            u.BrokerImage = fileName;
                        }
                        ctx.SaveChanges();
                        result = true;
                        accountid = u.userid;

                        //create Thumb
                        if (File.Exists(fullImagePath))
                        {
                            string thumbPath = path + "Thumb/" + u.userid + ".jpg";
                            System.Drawing.Image image = System.Drawing.Image.FromFile(fullImagePath);
                            System.Drawing.Image thumb = image.GetThumbnailImage(100, 100, () => false, IntPtr.Zero);
                            thumb.Save(thumbPath, System.Drawing.Imaging.ImageFormat.Jpeg);
                        }

                        string body = "Hi " + brokername + ", <br /><br />";
                        body += "Your updated email id: " + username + "<br /><br />";
                        body += "Password: " + u.password + " <br /><br />";
                        body += "Regards: <br /><br />";
                        body += "Apptrack Admin";

                        //------------------------------------------------------------

                        List<ManDRILLModel> _mandrillOBJ = new List<ManDRILLModel>();

                        var _mailDrilEmails = new List<Mandrill.EmailAddress>();
                        Mandrill.EmailAddress objEm = new Mandrill.EmailAddress();
                        objEm.email = username;
                        objEm.name = brokername;
                        _mailDrilEmails.Add(objEm);

                        //Man DRILL ********************************************************************

                        var domain = "occfinance.com";
                        Mandrill.EmailMessage message = new Mandrill.EmailMessage();
                        var _mandrillMessage = new List<Mandrill.EmailMessage>();

                        message.to = _mailDrilEmails;
                        message.html = body;
                        message.from_email = ConfigurationManager.AppSettings["SMTP_FROM"].ToString();
                        message.from_name = "OCC";
                        message.subject = "Your Apptrack Credential";
                        message.inline_css = true;
                        message.signing_domain = domain;
                        message.AddHeader("X-MC-SigningDomain", domain);
                        message.AddHeader("X-MC-TrackingDomain", domain);
                        _mandrillMessage.Add(message);

                        //********************************************************************************

                        // Manage The ManDRILLModel - 25-07-2014

                        var objMAN = new ManDRILLModel();
                        objMAN.contactId = 0;
                        objMAN.financeid = 0;
                        objMAN.mailcontent = body;
                        objMAN.MandrillMSG = message;
                        objMAN.IsAppContact = false;

                        objMAN.notemsg = "";

                        _mandrillOBJ.Add(objMAN);

                        int userId = _userId;
                        WebUtility.SendMailViaManDRILL(_mandrillOBJ, userId);

                        //------------------------------------------------------------



                        int _type = 1;
                        if (TeamsArray != null)
                        {
                            foreach (var obj in TeamsArray)
                            {
                                if (obj.IsActive.HasValue && obj.IsActive.Value == true)
                                    _type = 1;
                                else
                                    _type = 2;

                                WSAddTeamMembers(_userId, _accountId, obj.TeamId.HasValue ? obj.TeamId.Value : 0, u.userid, userId, _type);
                            }
                        }
                    }

                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Settings", "UpdateBroker", ex.Message);
                return result;
            }
        }

        /// <summary>
        /// Updating Team Broker
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="brokername"></param>
        /// <param name="username"></param>
        /// <param name="brokerid"></param>
        /// <param name="accountid"></param>
        /// <param name="TeamsArray"></param>
        /// <returns></returns>
        public bool WSUpdateTeamBroker(int _userId, int _accountId, string brokername, string username, int brokerid, out int accountid, List<tblAdminTeammeber> TeamsArray)
        {
            bool result = false;
            accountid = 0;
            try
            {
                if (brokerid > 0)
                {
                    int parentaccountId = _accountId;
                    var u = ctx.tblusers.Where(c => c.userid == brokerid).FirstOrDefault();
                    u.AdminId = 0;
                    ctx.SaveChanges();
                    result = true;
                    accountid = u.userid;
                    int userId = _userId;
                    int _type = 1;
                    if (TeamsArray != null)
                    {
                        foreach (var obj in TeamsArray)
                        {
                            if (obj.IsActive.HasValue && obj.IsActive.Value == true)
                                _type = 1;
                            else
                                _type = 2;

                            WSAddAdminTeamMembers(_userId, _accountId, obj.AdminTeamId.HasValue ? obj.AdminTeamId.Value : 0, obj.AdminTeamId.HasValue ? obj.AdminTeamId.Value : 0, u.userid, userId, _type);
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_setting", "UpdateTeamBroker", ex.Message);
                return result;
            }
        }

        /// <summary>
        /// WSAddAdminTeamMembers
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="AdminTeamId"></param>
        /// <param name="AdminId"></param>
        /// <param name="MemberId"></param>
        /// <param name="userId"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public int WSAddAdminTeamMembers(int _userId, int _accountId, int AdminTeamId, int AdminId, int MemberId, int userId, int Type)
        {
            int chk = 0;
            try
            {
                if (_accountId > 0)
                {
                    SqlParameter[] Param = new SqlParameter[7];
                    Param[0] = new SqlParameter("@AdminTeamId", AdminTeamId);
                    Param[1] = new SqlParameter("@AdminId", AdminId);
                    Param[2] = new SqlParameter("@MemberId", MemberId);
                    Param[3] = new SqlParameter("@EntryBy", userId);
                    Param[4] = new SqlParameter("@IsActive", true);
                    Param[5] = new SqlParameter("@IsDelete", false);
                    Param[6] = new SqlParameter("@Type", Type);

                    string _procedurename = "tblAdminTeammebersADD";

                    chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                }

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "AddAdminTeammember", ex.Message);
            }
            return chk;
        }

        #endregion

        #region Add Team calling from webservice
        /// <summary>
        /// Adding Team calling from webservice
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="teamName"></param>
        /// <param name="managerId"></param>
        /// <param name="teamid"></param>
        /// <returns></returns>
        public int WSAddTeam(int _userId, int _accountId, string teamName, int managerId, out int teamid)
        {

            teamid = 0;
            try
            {
                var AccountId = _accountId;
                var userId = _userId;

                SqlParameter[] Param = new SqlParameter[9];
                Param[0] = new SqlParameter("@AccountId", AccountId);
                Param[1] = new SqlParameter("@CompanyName", "0");
                Param[2] = new SqlParameter("@TeamName", teamName);
                Param[3] = new SqlParameter("@Regions", "0");
                Param[4] = new SqlParameter("@ManagerId", managerId);
                Param[5] = new SqlParameter("@EntryBy", userId);
                Param[6] = new SqlParameter("@IsActive", true);
                Param[7] = new SqlParameter("@IsDelete", false);

                Param[8] = new SqlParameter("@TeamId", 0);
                Param[8].Direction = ParameterDirection.Output;
                string _procedurename = "tblTeamAdd";

                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                teamid = Convert.ToInt32(Param[8] != null ? Param[8].SqlValue.ToString() : "0");

                if (teamid > 0)
                {
                    if (WSAddTeamMembers(_userId, _accountId, teamid, managerId, userId, 1) > 0)
                    { return teamid; }
                }

                return teamid;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "AddTeam", ex.Message);
                return 0;
            }
        }

        #endregion

        #region Add Team Members calling from webservice
        /// <summary>
        /// Adding Team member calling from webservice
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="TeamId"></param>
        /// <param name="MemberId"></param>
        /// <param name="userId"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public int WSAddTeamMembers(int _userId, int _accountId, int TeamId, int MemberId, int userId, int Type)
        {
            int chk = 0;
            try
            {
                SqlParameter[] Param = new SqlParameter[6];
                Param[0] = new SqlParameter("@TeamId", TeamId);
                Param[1] = new SqlParameter("@MemberId", MemberId);
                Param[2] = new SqlParameter("@EntryBy", userId);
                Param[3] = new SqlParameter("@IsActive", true);
                Param[4] = new SqlParameter("@IsDelete", false);
                Param[5] = new SqlParameter("@Type", Type);

                string _procedurename = "tblTeamMembersAdd";

                chk = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "Addteammembers", ex.Message);
            }
            return chk;
        }


        #endregion

        #region Update Team calling from webservice
        /// <summary>
        /// Update team calling from webservice
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="teamName"></param>
        /// <param name="managerId"></param>
        /// <param name="editteamId"></param>
        /// <param name="teamid"></param>
        /// <returns></returns>
        public int WSUpdateTeam(int _userId, int _accountId, string teamName, int managerId, int editteamId, out int teamid)
        {

            teamid = 0;
            try
            {
                var AccountId = _accountId;
                var userId = _userId;
                SqlParameter[] Param = new SqlParameter[10];
                Param[0] = new SqlParameter("@TeamId", editteamId);
                Param[1] = new SqlParameter("@AccountId", AccountId);
                Param[2] = new SqlParameter("@CompanyName", "0");
                Param[3] = new SqlParameter("@TeamName", teamName);
                Param[4] = new SqlParameter("@Regions", "0");
                Param[5] = new SqlParameter("@ManagerId", managerId);
                Param[6] = new SqlParameter("@UpdateBy", userId);
                Param[7] = new SqlParameter("@IsActive", true);
                Param[8] = new SqlParameter("@IsDelete", false);

                Param[9] = new SqlParameter("@Chk", 0);
                Param[9].Direction = ParameterDirection.Output;
                string _procedurename = "tblTeamUpdate";

                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);

                teamid = Convert.ToInt32(Param[9] != null ? Param[9].SqlValue.ToString() : "0");

                if (teamid > 0)
                {
                    if (WSAddTeamMembers(_userId, _accountId, editteamId, managerId, userId, 1) > 0)
                        return teamid;
                }

                return teamid;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "UpdateTeam", ex.Message);
                return 0;
            }
        }

        #endregion

        #region Delete Team calling from webservice
        /// <summary>
        /// Deleting Team calling from webservice
        /// </summary>
        /// <param name="_userId"></param>
        /// <param name="_accountId"></param>
        /// <param name="teamid"></param>
        /// <param name="managerId"></param>
        /// <param name="chk"></param>
        /// <returns></returns>
        public bool WSDeleteTeam(int _userId, int _accountId, int teamid, int managerId, out int chk)
        {
            bool result = false;
            chk = 0;
            try
            {
                var AccountId = _accountId;
                var userId = _userId;
                SqlParameter[] Param = new SqlParameter[4];
                Param[0] = new SqlParameter("@TeamId", teamid);
                Param[1] = new SqlParameter("@ManagerId", managerId);
                Param[2] = new SqlParameter("@UpdateBy", userId);

                Param[3] = new SqlParameter("@Chk", 0);
                Param[3].Direction = ParameterDirection.Output;
                string _procedurename = "tblTeamDelete";

                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
                chk = Convert.ToInt32(Param[3] != null ? Param[3].SqlValue.ToString() : "0");

                if (chk > 0)
                {
                    result = true;
                }

                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "deleteTeam", ex.Message);
                return result;
            }
        }

        #endregion

        #region Delete Broker calling from webservice
        /// <summary>
        /// Delete Broker calling from webservice
        /// </summary>
        /// <param name="brokerid"></param>
        /// <returns></returns>
        public bool WSDeleteBroker(int _userId, int _accountId, int brokerid)
        {
            bool result = false;
            try
            {

                int parentaccountId = _accountId;
                var brokeruser = ctx.tblusers.Where(b => b.userid == brokerid && b.active && b.broker.HasValue && b.broker.Value == true).Select(b => b).FirstOrDefault();
                if (brokeruser != null)
                {
                    if (brokeruser != null)
                    {
                        brokeruser.active = false;
                        brokeruser.TeamId = 0;
                        ctx.SaveChanges();
                        result = true;
                        TeamMembersDeleteByMemberId(brokerid);
                        int userId = _userId;
                        WSAddAdminTeamMembers(_userId, _accountId, parentaccountId, parentaccountId, brokerid, userId, 2);
                    }
                }

                return result;
            }
            catch (Exception ex)
            {
                Helper.ErrorLog(ex.InnerException, "Q_Setting", "deleteBroker", ex.Message);
                return result;
            }
        }

        #endregion

        #endregion webservice Setting methode

    }
}

namespace Occfinance.Code
{
    /*! Class is used to hold data access for scheduler
     * \This classs provides data access facility for shceduler
     * \All the fields are declared on top
     * \This class may hold large number of methods implementation
     */
    public class Q_SchedulerAccess
    {
        #region Fields

        public db_occfinance_5572Entities dbCtx = new db_occfinance_5572Entities();
        public int taskStatus = 0;

        #endregion Fields

        #region Methods Implementation

        public int TaskOperation(string financeIds, string rIds, DateTime? start)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[3];
                parameters[0] = new SqlParameter("@financeid", string.IsNullOrEmpty(financeIds) ? "" : financeIds);
                parameters[1] = new SqlParameter("@ID", string.IsNullOrEmpty(rIds) ? "" : rIds);
                parameters[2] = new SqlParameter("@followup", start ?? (object)DBNull.Value);
                taskStatus = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateFollowupDatefinance", parameters);
            }
            catch
            {
            }
            return taskStatus;
        }

        public int TaskOperation(string recurrenceRule, string recurrenceException, int? taskId)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[3];
                parameters[0] = new SqlParameter("@taskid", taskId ?? 0);
                parameters[1] = new SqlParameter("@recurrencerule", string.IsNullOrEmpty(recurrenceRule) ? "" : recurrenceRule);
                parameters[2] = new SqlParameter("@recurrenceexception", string.IsNullOrEmpty(recurrenceException) ? "" : recurrenceException);
                taskStatus = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateRecurrenceRuleException", parameters);
            }
            catch
            {
            }
            return taskStatus;
        }

        public int TaskOperation(string financeIds, string Ids, string followUp)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[3];
                parameters[0] = new SqlParameter("@ID", string.IsNullOrEmpty(Ids) ? "" : Ids);
                parameters[1] = new SqlParameter("@financeid", string.IsNullOrEmpty(financeIds) ? "" : financeIds);
                parameters[2] = new SqlParameter("@followup", Convert.ToDateTime(followUp));
                taskStatus = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateFollowupDateSavedEvent", parameters);
            }
            catch
            {
            }
            return taskStatus;
        }

        public int TaskOperation(string financeIds, DateTime? followUp)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@ID", string.IsNullOrEmpty(financeIds) ? "" : financeIds);
                parameters[1] = new SqlParameter("@followup", followUp ?? (object)DBNull.Value);
                taskStatus = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateFollowupDateSavedEvent", parameters);
            }
            catch
            {
            }
            return taskStatus;
        }

        public int TaskOperation(int financeId, int Type, int Status, DateTime? startEvent, DateTime? endEvent, string recurrenceRule, int recurrenceId, string recurrenceException)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[8];
                parameters[0] = new SqlParameter("financeid", financeId);
                parameters[1] = new SqlParameter("type", Type);
                parameters[2] = new SqlParameter("status", Status);
                parameters[3] = new SqlParameter("startevent", startEvent ?? (object)DBNull.Value);
                parameters[4] = new SqlParameter("endevent", endEvent ?? (object)DBNull.Value);
                parameters[5] = new SqlParameter("recurrencerule", string.IsNullOrEmpty(recurrenceRule) ? "" : recurrenceRule);
                parameters[6] = new SqlParameter("recurrenceID", recurrenceId);
                parameters[7] = new SqlParameter("recurrenceexception", string.IsNullOrEmpty(recurrenceException) ? "" : recurrenceException);

                taskStatus = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SaveEvents", parameters);
            }
            catch
            {
            }
            return taskStatus;
        }

        public DataTable GetIcsFiles(int financeId)
        {
            DataTable icsFiles = null;
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@financeid", financeId);
            icsFiles = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetIcsFileForEvent_Update", parameters);
            return icsFiles;
        }

        public DataTable GetIcsFilesForApplicationPage(int financeId)
        {
            DataTable icsFiles = null;
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@financeid", financeId);

            icsFiles = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetIcsFileForEventForApplicationPage_Update", parameters);
            return icsFiles;
        }

        public DataTable GetCompleteIcsFile(string financeId)
        {
            DataTable icsFiles = null;
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@financeid", financeId);
            icsFiles = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetCompleteIcsFileForEvents_Update", parameters);
            return icsFiles;
        }

        #endregion Methods Implementation
    }
}

{
    public class Q_ReportsDataForScreen
    {

        public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
        System.Web.HttpContext con = HttpContext.Current;
        #region [Methods For Big screen]
        #region [All CLients]
        public List<DashboardClients> FindDashboardClients(int parentuserid, int userId = 0)
        {
            List<DashboardClients> clients = new List<DashboardClients>();
            try
            {

                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@parentuserid", parentuserid);
                Param[1] = new SqlParameter("@filteredaccount", null);

                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAgentAndBranchListForScreen", Param);

                clients = (from DataRow row in dt.Rows
                           select new DashboardClients
                           {
                               clientid = Convert.ToInt32(((row["accountid"] == DBNull.Value || row["accountid"] == null) ? "0" : row["accountid"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               averageLeads = Convert.ToInt32(((row["average"] == DBNull.Value || row["average"] == null) ? "0" : row["average"]).ToString()),
                               countLeads = Convert.ToInt32(((row["total"] == DBNull.Value || row["total"] == null) ? "0" : row["total"]).ToString()),
                               thisMonthLeads = Convert.ToInt32(((row["last30days"] == DBNull.Value || row["last30days"] == null) ? "0" : row["last30days"]).ToString()),
                               lastLeadDate = (row["lastleaddate"] == DBNull.Value || row["lastleaddate"] == null) ? (DateTime?)null : Convert.ToDateTime(row["lastleaddate"]),

                           }).OrderBy(c => c.companyname).ToList();

                return clients;
            }
            catch (Exception ex)
            {
                return clients;
            }
        }
        #endregion
        #region [All Brokers]
        public List<DashboardBrokers> FindDashboardBrokers(int parentuserid, int userId = 0)
        {
            bool flag = false;
            List<DashboardBrokers> clients = new List<DashboardBrokers>();
            try
            {

                var userAccount = ctx.tblaccounts.Where(u => u.parent.HasValue && u.parent.Value == parentuserid).Select(u => u.accountid).ToList();
                var account = ctx.tblusers.Where(u => u.broker.HasValue && u.broker.Value == true && u.account == parentuserid).Select(u => u).ToList();

                SqlParameter[] Param = new SqlParameter[1];
                Param[0] = new SqlParameter("@parentuserid", parentuserid);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetBrokerListForScreen", Param);

                if (dt != null)
                {
                    clients = (from DataRow row1 in dt.Rows
                               select new DashboardBrokers()
                               {
                                   contactname = Convert.ToString(row1["brokername"] == null ? "" : row1["brokername"].ToString()),
                                   Completion_Average = Convert.ToInt32(row1["exaverage"] == null ? "0" : row1["exaverage"].ToString()),
                                   Completion_Total = Convert.ToInt32(row1["extotal"] == null ? "0" : row1["extotal"].ToString()),
                                   Completion_Last30 = Convert.ToInt32(row1["exlast30days"] == null ? "0" : row1["exlast30days"].ToString()),
                                   Completion_Change = Convert.ToInt32(row1["exChange"] == null ? "0" : row1["exChange"].ToString()),
                                   Decision_in_principle_Average = Convert.ToInt32(row1["dipaverage"] == null ? "0" : row1["dipaverage"].ToString()),
                                   Decision_in_principle_Total = Convert.ToInt32(row1["diptotal"] == null ? "0" : row1["diptotal"].ToString()),
                                   Decision_in_principle_Last30 = Convert.ToInt32(row1["diplast30days"] == null ? "0" : row1["diplast30days"].ToString()),
                                   Decision_in_principle_Change = Convert.ToInt32(row1["dipChange"] == null ? "0" : row1["dipChange"].ToString()),
                                   ApplicationsSubmitted_Average = Convert.ToInt32(row1["appsubaverage"] == null ? "0" : row1["appsubaverage"].ToString()),
                                   ApplicationsSubmitted_Total = Convert.ToInt32(row1["appsubtotal"] == null ? "0" : row1["appsubtotal"].ToString()),
                                   ApplicationsSubmitted_Last30 = Convert.ToInt32(row1["appsublast30days"] == null ? "0" : row1["appsublast30days"].ToString()),
                                   ApplicationsSubmitted_Change = Convert.ToInt32(row1["appChange"] == null ? "0" : row1["appChange"].ToString())

                               }).ToList();
                }


                return clients;
            }
            catch (Exception ex)
            {
                return clients;
            }
        }
        #endregion
        #region [Get Mortgage Report For Screen]
        public DataSet GetMortgageCaseWrittenReportForScreen(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[9];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                Param[7] = new SqlParameter("@MortgageType", type);
            }

            Param[8] = new SqlParameter("@productTypeID", productTypeID);

            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWrittenForScreen_Update", Param);
            return ds;

        }
        #endregion [Get Mortgage Report For Screen]
        #region [Get Mortgage Completed Report For Screen]

        public DataSet GetMortgageCompletedReportForScreen(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[9];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                Param[7] = new SqlParameter("@MortgageType", type);
            }

            Param[8] = new SqlParameter("@productTypeID", productTypeID);

            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCompletedReportForScreen_Update", Param);
            return ds;
        }

        #endregion [Get Mortgage Completed Report For Screen]
        #region [Get Insurance Written Report For screen]

        /// <summary>
        /// method for InsuranceCaseWrittenForScreen
        /// </summary>
        /// <param name="type"></param>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <param name="userid"></param>
        /// <param name="brokerId"></param>
        /// <param name="Negotiator"></param>
        /// <param name="brokers"></param>
        /// <param name="Company"></param>
        /// <param name="Branches"></param>
        /// <param name="productTypeID"></param>
        /// <returns></returns>
        public DataSet GetInsuranceCaseWrittenReportForScreen(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[9];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                Param[7] = new SqlParameter("@InsuranceType", type);
            }
            Param[8] = new SqlParameter("@productTypeID", productTypeID);

            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCaseWrittenForScreen_Update", Param);
            return ds;
        }

        #endregion [Get Insurance Written Report For screen]
        #region [Get insurance Completed Report for Screen]
        public DataSet GetInsuranceCompletedReportForScreen(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[9];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                Param[7] = new SqlParameter("@InsuranceType", type);
            }
            Param[8] = new SqlParameter("@productTypeID", productTypeID);

            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCompletedReportForScreen_Update", Param);
            return ds;
        }
        #endregion [Get insurance Completed Report for Screen]
        #region [Get Total Writeen Report For Screen]
        public DataSet GetTotalWrittenReportForScreen(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[7];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }


            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWrittenForScreen_Update", Param);
            return ds;
        }
        #endregion [Get Total Completed Report For Screen]
        #region [Get Total Completed Report For Screen]
        public DataSet GetTotalWrittenReportCompleted(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[7];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }


            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWrittenCompleted_Update", Param);
            return ds;
        }
        #endregion [Get Total Written Report For Screen]
        #region [Get Total Writeen Month For Screen]
        public DataSet GetTotalWrittenMonthReportForScreen(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
        {
            SqlParameter[] Param = new SqlParameter[7];
            Param[0] = new SqlParameter("@CurrentUserId", userid);
            Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
            Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
            if (!string.IsNullOrEmpty(Negotiator))
            {
                Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
                Param[4] = new SqlParameter("@Company", Company.Trim());
                Param[5] = new SqlParameter("@Branches", Branches.Trim());
            }
            if (!string.IsNullOrWhiteSpace(brokers))
            {
                Param[6] = new SqlParameter("@BrokerId", brokers);
            }


            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWrittenMonthForScreen_Update", Param);
            return ds;
        }
        #endregion [Get Total Written Month For Screen]
        #region [Condition For Animation]
        public BrokerDetailForScreen ConditionForProductSubmitted()
        {
            BrokerDetailForScreen submittedBrokerDetail = null;// = new SubmittedBrokerDetailForScreen();
            try
            {
                SqlParameter[] Param = new SqlParameter[0];
                DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "SubmittedConditionForScreen", Param);
                submittedBrokerDetail = (from System.Data.DataRow row in ds.Tables[0].Rows
                                         select new BrokerDetailForScreen
                                   {
                                       brokerId = Convert.ToInt32((row["brokerId"] == DBNull.Value || row["brokerId"] == null) ? "" : row["brokerId"]),
                                       brokerName = ((row["brokerName"] == DBNull.Value || row["brokerName"] == null) ? "" : row["brokerName"]).ToString(),
                                       commision = (decimal)((row["commision"] == DBNull.Value || row["commision"] == null) ? (decimal)0 : Convert.ToDecimal(row["commision"])),
                                       dealSize = (decimal)((row["dealSize"] == DBNull.Value || row["dealSize"] == null) ? (decimal)0 : Convert.ToDecimal(row["dealSize"])),
                                       financeId = Convert.ToInt32((row["financeId"] == DBNull.Value || row["financeId"] == null) ? "0" : row["financeId"]),
                                       type = Convert.ToInt32((row["type"] == DBNull.Value || row["type"] == null) ? "0" : row["type"]),
                                   }).FirstOrDefault();
            }
            catch
            {

            }
            return submittedBrokerDetail;
        }

        public BrokerDetailForScreen ConditionForProductCompleted()
        {
            BrokerDetailForScreen completedBrokerDetail = null;
            try
            {
                SqlParameter[] Param = new SqlParameter[0];
                DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "CompletedConditionForScreen", Param);
                completedBrokerDetail = (from System.Data.DataRow row in ds.Tables[0].Rows
                                         select new BrokerDetailForScreen
                                         {
                                             brokerId = Convert.ToInt32((row["brokerId"] == DBNull.Value || row["brokerId"] == null) ? "" : row["brokerId"]),
                                             brokerName = ((row["brokerName"] == DBNull.Value || row["brokerName"] == null) ? "" : row["brokerName"]).ToString(),
                                             commision = (decimal)((row["commision"] == DBNull.Value || row["commision"] == null) ? (decimal)0 : Convert.ToDecimal(row["commision"])),
                                             dealSize = (decimal)((row["dealSize"] == DBNull.Value || row["dealSize"] == null) ? (decimal)0 : Convert.ToDecimal(row["dealSize"])),
                                             financeId = Convert.ToInt32((row["financeId"] == DBNull.Value || row["financeId"] == null) ? "0" : row["financeId"]),
                                             type = Convert.ToInt32((row["type"] == DBNull.Value || row["type"] == null) ? "0" : row["type"]),
                                         }).FirstOrDefault();
            }
            catch
            {

            }
            return completedBrokerDetail;
        }
        public BrokerDetailForScreen ConditionForAnimation(out int animationType)
        {

            animationType = 0;
            var submittedBrokerDetail = ConditionForProductSubmitted();
            var completedBrokerDetail = ConditionForProductCompleted();
            BrokerDetailForScreen brokerDetail = null;
            try
            {
                if (submittedBrokerDetail != null && completedBrokerDetail == null)
                {
                    if (submittedBrokerDetail.type == 1 && submittedBrokerDetail.commision >= 1500)
                    {
                        animationType = 1; // fireworks

                    }
                    else if (submittedBrokerDetail.type == 1 && submittedBrokerDetail.commision < 1500)
                    {
                        animationType = 2; //champagne
                    }
                    else if ((submittedBrokerDetail.type == 2 || submittedBrokerDetail.type == 3) && submittedBrokerDetail.commision >= 1000)
                    {
                        animationType = 1; // fireworks

                    }
                    else if ((submittedBrokerDetail.type == 2 || submittedBrokerDetail.type == 3) && submittedBrokerDetail.commision < 1000)
                    {
                        animationType = 2; // champagne

                    }
                    brokerDetail = submittedBrokerDetail;
                    SaveRecordForBigScreen(brokerDetail, 1);
                }
                else if (submittedBrokerDetail == null && completedBrokerDetail != null)
                {
                    animationType = 3; //stack of money
                    brokerDetail = completedBrokerDetail;
                    SaveRecordForBigScreen(brokerDetail, 2);
                }
                else if (submittedBrokerDetail != null && completedBrokerDetail != null)
                {
                    animationType = 3; // stack of money
                    brokerDetail = completedBrokerDetail;
                    SaveRecordForBigScreen(brokerDetail, 2);
                }
                else
                {
                    animationType = 0;
                    brokerDetail = null;

                }
            }
            catch
            {

            }
            return brokerDetail;

        }
        #endregion [Condition For Animation]

        #region [Saving record in Bigscreen Table for maintain history]
        public int SaveRecordForBigScreen(BrokerDetailForScreen detail, int status)
        {
            int result = 0;
            try
            {
                if (detail != null)
                {
                    tblBigScreenRecords bigScreen = new tblBigScreenRecords();
                    bigScreen.financeId = detail.financeId;
                    bigScreen.status = status;
                    bigScreen.createdDate = DateTime.Now;
                    ctx.tblBigScreenRecords.Add(bigScreen);
                    result = ctx.SaveChanges();
                }
            }
            catch
            {

            }
            return result;

        }
        #endregion [Saving record in Bigscreen Table for maintain history]

        #region[Getting Clients for dropdown]
        public List<Clients> FindClients(int parentuserid, int UserId = 0)
        {
            List<Clients> clients = new List<Clients>();
            try
            {

                //Check User Type
                Q_Application _obj = new Q_Application();
                int teamId = 0;
                string brokersIds = "";
                int _userType = 0;
                int _accountId = 0;
                _userType = _obj.CheckUserType(UserId, out teamId, out _accountId, out brokersIds);
                //**************************************

                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", _userType);
                Param[1] = new SqlParameter("@ParentId", parentuserid); ;
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllClientsForScreen", Param);

                clients = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["accountid"] == DBNull.Value || row["accountid"] == null) ? "0" : row["accountid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString())
                           }).ToList();
                return clients;
            }
            catch (Exception ex)
            {
                return clients;
            }
        }
        #endregion [Getiing clients for dropdown]
        #region[Getting Brokers For dropdown]
        public List<Clients> FindBrokerForLoggedInUserWithUserId(int accid, int userid)
        {
            bool flag = false;
            List<Clients> brokers = new List<Clients>();
            Q_Application _obj = new Q_Application();

            try
            {

                int teamId = 0;
                string brokersIds = "";
                int _userType = 0;
                int _accountId = 0;
                _userType = _obj.CheckUserType(userid, out teamId, out _accountId, out brokersIds);

                //Updated With Store Procedure For Navigation Delay ********* 7/7/2014 ********************************************

                var _Id = 0;

                if (_userType == 1)
                    _Id = _accountId;
                else if (_userType == 2)
                    _Id = teamId;
                else if (_userType == 3)
                    _Id = userid;
                else if (_userType == 4)
                {
                    int account = ctx.tblaccounts.Where(u => u.accountid == userid).Select(u => u.parent.HasValue ? u.parent.Value : 0).FirstOrDefault();
                    _Id = account;
                }

                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@UserType", _userType);
                Param[1] = new SqlParameter("@ID", _Id);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindBrokerForLoggedInUserWithUserIdForScreen", Param);

                brokers = (from DataRow row in dt.Rows
                           select new Clients
                           {
                               clientid = Convert.ToInt32(((row["clientid"] == DBNull.Value || row["clientid"] == null) ? "0" : row["clientid"]).ToString()),
                               parent = Convert.ToInt32(((row["parent"] == DBNull.Value || row["parent"] == null) ? "0" : row["parent"]).ToString()),
                               companyname = ((row["companyname"] == DBNull.Value || row["companyname"] == null) ? " " : row["companyname"]).ToString(),
                               TeamName = ((row["TeamName"] == DBNull.Value || row["TeamName"] == null) ? " " : row["TeamName"]).ToString(),
                               TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
                               affiliateid = ((row["affiliateid"] == DBNull.Value || row["affiliateid"] == null) ? " " : row["affiliateid"]).ToString(),
                               telephone = ((row["telephone"] == DBNull.Value || row["telephone"] == null) ? " " : row["telephone"]).ToString(),
                               email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
                               contactname = ((row["contactname"] == DBNull.Value || row["contactname"] == null) ? " " : row["contactname"]).ToString(),
                               address = Convert.ToInt32(((row["address"] == DBNull.Value || row["address"] == null) ? "0" : row["address"]).ToString()),
                               userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
                               AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
                               countLeads = Convert.ToInt32(((row["countLeads"] == DBNull.Value || row["countLeads"] == null) ? "0" : row["countLeads"]).ToString()),
                           }).ToList();

                //**********************************************************************************

                return brokers;
            }
            catch (Exception)
            {
                return brokers;
            }
        }
        #endregion[Getting Brokers for dropdown]

        #region [Get Monthly lead flow]
        public List<MonthlyLeadFlowBrokers> GetMonthlyLeadReportForScreen(int parentuserid, int userId = 0)
        {

            List<MonthlyLeadFlowBrokers> brokers = new List<MonthlyLeadFlowBrokers>();
            try
            {
                SqlParameter[] Param = new SqlParameter[2];
                Param[0] = new SqlParameter("@parentuserid", parentuserid);
                Param[1] = new SqlParameter("@filteredaccount", null);
                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetMonthlyLeadFlowForScreen", Param);

                if (dt != null)
                {
                    brokers = (from DataRow row1 in dt.Rows
                               select new MonthlyLeadFlowBrokers()
                               {
                                   BrokerName = Convert.ToString(row1["brokername"] == null ? "" : row1["brokername"].ToString()),
                                   Rank = Convert.ToInt32(row1["Ranking"] == null ? "0" : row1["Ranking"].ToString()),
                                   BrokerId = Convert.ToInt32(row1["userid"] == null ? "0" : row1["userid"].ToString()),
                                   Total = Convert.ToInt32(row1["total"] == null ? "0" : row1["total"].ToString()),

                               }).ToList();
                }


                return brokers;
            }
            catch (Exception ex)
            {
                return brokers;
            }
        }
        #endregion [Get Monthly lead flow]


        #endregion [methods for big screen]



    }
}