Professor6T9 Web SheLL

Windows NT WEB-23 10.0 build 17763 (Windows Server 2016) i586
Microsoft-IIS/10.0
103.118.16.173

DataAccess.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Net;
using System.Globalization;
using System.IO;
using System.Collections;

public class DataAccess
{
    #region ...[General]...

public string serverName;
    public string dataBase;
    public string user;
    public string password;
    public string CRMRoot;
    private string _Text, _Description, _ParentID, _NavigateUrl, _OldPassword, _NewPassword, _UserName;
    public string UserName
    {
        get
        {
            return this._UserName;
        }
        set
        {
            this._UserName = value.Trim();
        }
    }
    public string OldPassword
    {
        get
        {
            return this._OldPassword;
        }
        set
        {
            this._OldPassword = value.Trim();
        }
    }
    public string NewPassword
    {
        get
        {
            return this._NewPassword;
        }
        set
        {
            this._NewPassword = value.Trim();
        }
    }

public DataAccess()
    {
        //serverName = System.Configuration.ConfigurationManager.AppSettings["serverName"].ToString();
        //dataBase = System.Configuration.ConfigurationManager.AppSettings["databaseName"].ToString();
        //user = System.Configuration.ConfigurationManager.AppSettings["userName"].ToString();
        //password = System.Configuration.ConfigurationManager.AppSettings["password"].ToString();
        //CRMRoot = System.Configuration.ConfigurationManager.AppSettings["CRMROOT"].ToString();
    }

public bool IsAdminOwner(int OwnerID)
    {
        //,1,2,3,56,66,81,97,118,133,149,152,153,154,
        bool IsAdmin = false;
        ArrayList AdminOwners = new ArrayList();

AdminOwners.Add(1);
        AdminOwners.Add(2);
        AdminOwners.Add(3);
        AdminOwners.Add(56);  //Muthu@allcad
        AdminOwners.Add(63);  //allcad.chennai@allcadservices.com
        AdminOwners.Add(66);  //Ashu@autodesk
        AdminOwners.Add(81);  //Jaya@autodesk
        AdminOwners.Add(97);  //Ashu@allcad
        AdminOwners.Add(118); //Sonali@allcad
        AdminOwners.Add(133); //Jaya@allcad
        AdminOwners.Add(134); //Nisha@allcad
        AdminOwners.Add(149); //Ruchi@allcad
        AdminOwners.Add(152); //Ravi@smsoft
        AdminOwners.Add(153); //Ravi@allcad
        AdminOwners.Add(154); // Ashu@smsoft

IsAdmin = AdminOwners.Contains(OwnerID) ? true : false;

return IsAdmin;
    }

public SqlConnection OpenConnection()
    {
        SqlConnection connection;
        string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConncectionStr"].ConnectionString;
        //connection = new SqlConnection("Data Source=" + serverName + "; Initial Catalog=" + dataBase + "; User id=" + user + "; Password=" + password + ";");
        connection = new SqlConnection(connString);
        connection.Open();
        return connection;
    }

public string EncryptData1(string Value)
    {
        Char[] charValue = Value.ToCharArray();
        string Encryptstring = string.Empty;
        foreach (char ch in charValue)
        {
            switch (ch)
            {
                case '1':
                    Encryptstring += "R";
                    break;
                case '2':
                    Encryptstring += "Z";
                    break;
                case '3':
                    Encryptstring += "A";
                    break;
                case '4':
                    Encryptstring += "W";
                    break;
                case '5':
                    Encryptstring += "K";
                    break;
                case '6':
                    Encryptstring += "P";
                    break;
                case '7':
                    Encryptstring += "E";
                    break;
                case '8':
                    Encryptstring += "V";
                    break;
                case '9':
                    Encryptstring += "N";
                    break;
                case '0':
                    Encryptstring += "Y";
                    break;
            }
        }
        return Encryptstring;
    }

public string EncryptData2(string Value)
    {
        Char[] charValue = Value.ToCharArray();
        string Encryptstring = string.Empty;
        foreach (char ch in charValue)
        {
            switch (ch)
            {
                case '1':
                    Encryptstring += "U";
                    break;
                case '2':
                    Encryptstring += "C";
                    break;
                case '3':
                    Encryptstring += "M";
                    break;
                case '4':
                    Encryptstring += "O";
                    break;
                case '5':
                    Encryptstring += "L";
                    break;
                case '6':
                    Encryptstring += "G";
                    break;
                case '7':
                    Encryptstring += "Q";
                    break;
                case '8':
                    Encryptstring += "I";
                    break;
                case '9':
                    Encryptstring += "B";
                    break;
                case '0':
                    Encryptstring += "T";
                    break;
            }
        }
        return Encryptstring;
    }

public string DecryptData1(string Value)
    {
        Char[] charValue = Value.ToCharArray();
        string Decryptstring = string.Empty;
        foreach (char ch in charValue)
        {
            switch (ch)
            {
                case 'R':
                    Decryptstring += "1";
                    break;
                case 'Z':
                    Decryptstring += "2";
                    break;
                case 'A':
                    Decryptstring += "3";
                    break;
                case 'W':
                    Decryptstring += "4";
                    break;
                case 'K':
                    Decryptstring += "5";
                    break;
                case 'P':
                    Decryptstring += "6";
                    break;
                case 'E':
                    Decryptstring += "7";
                    break;
                case 'V':
                    Decryptstring += "8";
                    break;
                case 'N':
                    Decryptstring += "9";
                    break;
                case 'Y':
                    Decryptstring += "0";
                    break;
                default:
                    Decryptstring += "*";
                    break;
            }
        }

return Decryptstring;
    }

public string DecryptData2(string Value)
    {
        Char[] charValue = Value.ToCharArray();
        string Decryptstring = string.Empty;
        foreach (char ch in charValue)
        {
            switch (ch)
            {

case 'U':
                    Decryptstring += "1";
                    break;
                case 'C':
                    Decryptstring += "2";
                    break;
                case 'M':
                    Decryptstring += "3";
                    break;
                case 'O':
                    Decryptstring += "4";
                    break;
                case 'L':
                    Decryptstring += "5";
                    break;
                case 'G':
                    Decryptstring += "6";
                    break;
                case 'Q':
                    Decryptstring += "7";
                    break;
                case 'I':
                    Decryptstring += "8";
                    break;
                case 'B':
                    Decryptstring += "9";
                    break;
                case 'T':
                    Decryptstring += "0";
                    break;
                default:
                    Decryptstring += "*";
                    break;
            }
        }

return Decryptstring;
    }

public string EncryptString(string Variable)
    {
        Variable = Variable.ToLower();
        char[] c = Variable.ToCharArray();
        Array.Reverse(c);
        Variable = new string(c);

string Encryptstring = string.Empty;
        Char[] charValue = Variable.ToCharArray();
        foreach (char ch in charValue)
        {
            switch (ch)
            {
                case 'a': Encryptstring += "K"; break;
                case 'b': Encryptstring += "T"; break;
                case 'c': Encryptstring += "P"; break;
                case 'd': Encryptstring += "N"; break;
                case 'e': Encryptstring += "M"; break;
                case 'f': Encryptstring += "X"; break;
                case 'g': Encryptstring += "S"; break;
                case 'h': Encryptstring += "D"; break;
                case 'i': Encryptstring += "A"; break;
                case 'j': Encryptstring += "E"; break;
                case 'k': Encryptstring += "W"; break;
                case 'l': Encryptstring += "F"; break;
                case 'm': Encryptstring += "Q"; break;
                case 'n': Encryptstring += "R"; break;
                case 'o': Encryptstring += "J"; break;
                case 'p': Encryptstring += "O"; break;
                case 'q': Encryptstring += "Z"; break;
                case 'r': Encryptstring += "G"; break;
                case 's': Encryptstring += "B"; break;
                case 't': Encryptstring += "~"; break;
                case 'u': Encryptstring += "2"; break;
                case 'v': Encryptstring += "1"; break;
                case 'w': Encryptstring += "I"; break;
                case 'x': Encryptstring += "C"; break;
                case 'y': Encryptstring += "4"; break;
                case 'z': Encryptstring += "L"; break;
                case '1': Encryptstring += "5"; break;
                case '2': Encryptstring += "H"; break;
                case '3': Encryptstring += "3"; break;
                case '4': Encryptstring += "U"; break;
                case '5': Encryptstring += "8"; break;
                case '6': Encryptstring += "Y"; break;
                case '7': Encryptstring += "*"; break;
                case '8': Encryptstring += "0"; break;
                case '9': Encryptstring += "6"; break;
                case '0': Encryptstring += "9"; break;
                case '@': Encryptstring += "7"; break;
                case '.': Encryptstring += "V"; break;
                case '-': Encryptstring += "_"; break;
                case '_': Encryptstring += "-"; break;
                default: Encryptstring += ch; break;
            }
        }
        return Encryptstring;
    }

public string DecryptString(string Variable)
    {
        Variable = Variable.ToLower();
        char[] c = Variable.ToCharArray();
        Array.Reverse(c);
        Variable = new string(c);

string Encryptstring = string.Empty;
        Char[] charValue = Variable.ToCharArray();
        foreach (char ch in charValue)
        {
            switch (ch)
            {
                case 'k': Encryptstring += "a"; break;
                case 't': Encryptstring += "b"; break;
                case 'p': Encryptstring += "c"; break;
                case 'n': Encryptstring += "d"; break;
                case 'm': Encryptstring += "e"; break;
                case 'x': Encryptstring += "f"; break;
                case 's': Encryptstring += "g"; break;
                case 'd': Encryptstring += "h"; break;
                case 'a': Encryptstring += "i"; break;
                case 'e': Encryptstring += "j"; break;
                case 'w': Encryptstring += "k"; break;
                case 'f': Encryptstring += "l"; break;
                case 'q': Encryptstring += "m"; break;
                case 'r': Encryptstring += "n"; break;
                case 'j': Encryptstring += "o"; break;
                case 'o': Encryptstring += "p"; break;
                case 'z': Encryptstring += "q"; break;
                case 'g': Encryptstring += "r"; break;
                case 'b': Encryptstring += "s"; break;
                case '~': Encryptstring += "t"; break;
                case '2': Encryptstring += "u"; break;
                case '1': Encryptstring += "v"; break;
                case 'i': Encryptstring += "w"; break;
                case 'c': Encryptstring += "x"; break;
                case '4': Encryptstring += "y"; break;
                case 'l': Encryptstring += "z"; break;
                case '5': Encryptstring += "1"; break;
                case 'h': Encryptstring += "2"; break;
                case '3': Encryptstring += "3"; break;
                case 'u': Encryptstring += "4"; break;
                case '8': Encryptstring += "5"; break;
                case 'y': Encryptstring += "6"; break;
                case '*': Encryptstring += "7"; break;
                case '0': Encryptstring += "8"; break;
                case '6': Encryptstring += "9"; break;
                case '9': Encryptstring += "0"; break;
                case '7': Encryptstring += "@"; break;
                case 'v': Encryptstring += "."; break;
                case '_': Encryptstring += "-"; break;
                case '-': Encryptstring += "_"; break;
                default: Encryptstring += ch; break;
            }
        }
        return Encryptstring;
    }

public void WriteToLog(Exception _Exception, String _MessageText)
    {
        #region ...[Open Log File]...
        string LogFile = LogFolder() + DateTime.Now.ToString("yyyy-MM-dd_HH") + ".txt";
        TextWriter tw = new StreamWriter(LogFile, true);
        #endregion

#region ...[Set Log]...
        try
        {
            if (_Exception != null)
            {
                string _ExceptionHelpLink = _Exception.HelpLink == null ? "-" : _Exception.HelpLink.ToString();
                string _ExceptionInnerExceptionMessage = _Exception.InnerException == null ? "-" : _Exception.InnerException.Message == null ? "-" : _Exception.InnerException.Message;
                string _ExceptionMessage = _Exception.Message == null ? "-" : _Exception.Message;
                string _ExceptionSource = _Exception.Source == null ? "-" : _Exception.Source;
                string _ExceptionStackTrace = _Exception.StackTrace == null ? "-" : _Exception.StackTrace;
                string _ExceptionTargetSiteAttributes = _Exception.TargetSite == null ? "-" : _Exception.TargetSite.Attributes.ToString();
                string _ExceptionTargetSiteMemberType = _Exception.TargetSite == null ? "-" : _Exception.TargetSite.MemberType.ToString();
                string _ExceptionTargetSiteName = _Exception.TargetSite == null ? "-" : _Exception.TargetSite.Name == null ? "-" : _Exception.TargetSite.Name;
                string _ExceptionTargetSiteReflectedTypeFullName = _Exception.TargetSite == null ? "-" : _Exception.TargetSite.ReflectedType == null ? "-" : _Exception.TargetSite.ReflectedType.FullName == null ? "-" : _Exception.TargetSite.ReflectedType.FullName;
                string _ExceptionTargetSiteReflectedTypeName = _Exception.TargetSite == null ? "-" : _Exception.TargetSite.ReflectedType == null ? "-" : _Exception.TargetSite.ReflectedType.Name == null ? "-" : _Exception.TargetSite.ReflectedType.Name;
                string _ExceptionTargetSiteReflectedTypeNamespace = _Exception.TargetSite == null ? "-" : _Exception.TargetSite.ReflectedType == null ? "-" : _Exception.TargetSite.ReflectedType.Namespace == null ? "-" : _Exception.TargetSite.ReflectedType.Namespace.ToString();
                string _ExceptionDataValues = _Exception.Data == null ? "-" : _Exception.Data.Values == null ? "-" : _Exception.Data.Values.ToString();

tw.WriteLine(DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss:fff") + Environment.NewLine +
                             "01. Exception.HelpLink: " + _ExceptionHelpLink + Environment.NewLine + Environment.NewLine +
                             "02. Exception.InnerException.Message: " + _ExceptionInnerExceptionMessage + Environment.NewLine + Environment.NewLine +
                             "03. Exception.Message: " + _ExceptionMessage + Environment.NewLine + Environment.NewLine +
                             "04. Exception.Source: " + _ExceptionSource + Environment.NewLine + Environment.NewLine +
                             "05. Exception.StackTrace: " + _ExceptionStackTrace + Environment.NewLine + Environment.NewLine +
                             "06. Exception.TargetSite.Attributes: " + _ExceptionTargetSiteAttributes + Environment.NewLine + Environment.NewLine +
                             "07. Exception.TargetSite.MemberType: " + _ExceptionTargetSiteMemberType + Environment.NewLine + Environment.NewLine +
                             "08. Exception.TargetSite.Name: " + _ExceptionTargetSiteName + Environment.NewLine + Environment.NewLine +
                             "09. Exception.TargetSite.ReflectedType.FullName: " + _ExceptionTargetSiteReflectedTypeFullName + Environment.NewLine + Environment.NewLine +
                             "10. Exception.TargetSite.ReflectedType.Name: " + _ExceptionTargetSiteReflectedTypeName + Environment.NewLine + Environment.NewLine +
                             "11. Exception.TargetSite.ReflectedType.Namespace: " + _ExceptionTargetSiteReflectedTypeNamespace + Environment.NewLine + Environment.NewLine +
                             "12. Exception.Data.Values: " + _ExceptionDataValues + Environment.NewLine +
                             "--0--- x ---0--" + Environment.NewLine);
            }
            else
            {
                tw.WriteLine(DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss:fff") + Environment.NewLine +
                            _MessageText + Environment.NewLine +
                            "--0--- x ---0--" + Environment.NewLine);
            }
        }
        catch { }
        #endregion

#region ...[Close Log File]...
        tw.Close();
        #endregion
    }

private string LogFolder()
    {
        string folder = CRMRoot + @"\LogFolder\";
        string yearFolder = DateTime.Now.Year.ToString();
        string monthFolder = DateTime.Now.Month <= 9 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString();
        string dayFolder = DateTime.Now.Day <= 9 ? "0" + DateTime.Now.Day.ToString() : DateTime.Now.Day.ToString();

if (!System.IO.Directory.Exists(folder))
        {
            System.IO.Directory.CreateDirectory(folder);
        }

folder += yearFolder + @"\";
        if (!System.IO.Directory.Exists(folder))
        {
            System.IO.Directory.CreateDirectory(folder);

System.IO.Directory.CreateDirectory(folder + monthFolder + @"\");

System.IO.Directory.CreateDirectory(folder + monthFolder + @"\" + dayFolder);
        }

folder += monthFolder + @"\";
        if (!System.IO.Directory.Exists(folder))
        {
            System.IO.Directory.CreateDirectory(folder);

System.IO.Directory.CreateDirectory(folder + dayFolder + @"\");
        }

folder += dayFolder + @"\";
        if (!System.IO.Directory.Exists(folder))
        {
            System.IO.Directory.CreateDirectory(folder);
        }

return folder;
    }
    #endregion

//Hemant
    #region ...[Rahul]...

public DataTable SaveEMITransactionMaster(String TMDID, String Remarks)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "SaveEMITransactionMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@TMDID", TMDID);
        sql.Parameters.AddWithValue("@Remarks", Remarks);
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable ListFirmWiseReport(String FRM_ID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "ListFirmWiseReport";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@FRM_ID", FRM_ID);
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }
    public DataTable ListPartyWiseReport(String FRM_ID, String CMID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "ListPartyWiseReport";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@FRM_ID", FRM_ID);
        sql.Parameters.AddWithValue("@CMID", CMID);
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable GetTransactionMaster(String TMID, String SeletionType,
                                          String IsReceive, String CMID, String Day,
                                          String Month, String Year)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "GetTransactionMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@TMID", TMID);
        sql.Parameters.AddWithValue("@SeletionType", SeletionType);
        sql.Parameters.AddWithValue("@IsReceive", IsReceive);
        sql.Parameters.AddWithValue("@CMID", CMID);
        sql.Parameters.AddWithValue("@Day", Day);
        sql.Parameters.AddWithValue("@Month", Month);
        sql.Parameters.AddWithValue("@Year", Year);
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable InsertTransactionMaster(String CMID, String PMID,
                                             String TotalAmt, String CashAmt,
                                             String CreditAmt, String NoOfEMI,
                                             String EMIAmt, String Date,String refBookNo,String DayofEMI)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "InsertTransactionMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@CMID", CMID);
        sql.Parameters.AddWithValue("@PMID", PMID);
        sql.Parameters.AddWithValue("@TotalAmt", TotalAmt);
        sql.Parameters.AddWithValue("@CashAmt", CashAmt);
        sql.Parameters.AddWithValue("@CreditAmt", CreditAmt);
        sql.Parameters.AddWithValue("@NoOfEMI", NoOfEMI);
        sql.Parameters.AddWithValue("@EMIAmt", EMIAmt);
        sql.Parameters.AddWithValue("@Date", Date);
        sql.Parameters.AddWithValue("@RefBookNo", refBookNo);
        sql.Parameters.AddWithValue("@EMIDays", DayofEMI);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable GetClientMaster(String CMID, String SelectionType)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "GetClientMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@CMID", CMID);
        sql.Parameters.AddWithValue("@SelectionType", SelectionType);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable DeleteClientMaster(String CMID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "DELETEClientMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@CMID", CMID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable ActiveDeactivePostJob(String PD_ID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "ActiveDeactivePostJob";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@PD_ID", PD_ID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }
    public DataTable GetProductMaster(String PMID, String SelectionType)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "GetProductMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@PMID", PMID);
        sql.Parameters.AddWithValue("@SelectionType", SelectionType);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable InsertProductMaster(String CompanyName, String ProductName,
                                        String ProductRate)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "InsertProductMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@CompanyName", CompanyName);
        sql.Parameters.AddWithValue("@ProductName", ProductName);
        sql.Parameters.AddWithValue("@ProductRate", ProductRate);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable UpdateProductMaster(String PMID, String CompanyName, String ProductName,
                                      String ProductRate)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "UpdateProductMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@PMID", PMID);
        sql.Parameters.AddWithValue("@CompanyName", CompanyName);
        sql.Parameters.AddWithValue("@ProductName", ProductName);
        sql.Parameters.AddWithValue("@ProductRate", ProductRate);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable InsertClientMaster(String LM_ID, String FRM_ID, String Name, String FatherName,
                                        String Address, String MobileNo,
                                        String PhoneNo, String EmailID, String Remark,String OpeningBalance, String CreatedBy,
                                        String DOB, String PresentCompanyName, String CurrentSalary, String ExpSalary, String dsClientdetails,String resumepath,
                                        String Experience, String ParmanetAddress, String Gender, String Qualification,
                                       String JobRollID, String IndustryID, String FunctionalAreaID, String Duration, String JobStatusID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "InsertClientMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@LM_ID", LM_ID);
        sql.Parameters.AddWithValue("@FRM_ID", FRM_ID);
        sql.Parameters.AddWithValue("@Name", Name);
        sql.Parameters.AddWithValue("@FatherName", FatherName);
        sql.Parameters.AddWithValue("@Address", Address);
        sql.Parameters.AddWithValue("@MobileNo", MobileNo);
        sql.Parameters.AddWithValue("@PhoneNo", PhoneNo);
        sql.Parameters.AddWithValue("@EmailID", EmailID);
        sql.Parameters.AddWithValue("@Remark", Remark);
        sql.Parameters.AddWithValue("@OpeningBalance", OpeningBalance);
        sql.Parameters.AddWithValue("@CreatedBy", CreatedBy);
         sql.Parameters.AddWithValue("@DOB", DOB);
        sql.Parameters.AddWithValue("@PresentCompanyName", PresentCompanyName);
        sql.Parameters.AddWithValue("@CurrentSalary", CurrentSalary);
        sql.Parameters.AddWithValue("@ExpSalary", ExpSalary);
        sql.Parameters.AddWithValue("@dsClientdetails", dsClientdetails);
        sql.Parameters.AddWithValue("@ResumePath", resumepath);
        sql.Parameters.AddWithValue("@Experience", Experience);
        sql.Parameters.AddWithValue("@ParmanetAddress", ParmanetAddress);
        sql.Parameters.AddWithValue("@Gender", Gender);
        sql.Parameters.AddWithValue("@Qualification", Qualification);

sql.Parameters.AddWithValue("@JobRollID", JobRollID);
        sql.Parameters.AddWithValue("@IndustryID", IndustryID);
        sql.Parameters.AddWithValue("@FunctionalAreaID", FunctionalAreaID);
        sql.Parameters.AddWithValue("@Duration", Duration);
        sql.Parameters.AddWithValue("@JobStatusID", JobStatusID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }
    public DataTable UpdateCompanyProfile(String CMID, String Name, String CompanyName, String PhoneNo, String EmailID,
                                      String Address, String MobileNo, String LM_ID,String IndustryID, String CompanyDesignation)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "UpdateCompanyProfile";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@CMID", CMID);
        sql.Parameters.AddWithValue("@Name", Name);
        sql.Parameters.AddWithValue("@CompanyName", CompanyName);
        sql.Parameters.AddWithValue("@Address", Address);
        sql.Parameters.AddWithValue("@MobileNo", MobileNo);
        sql.Parameters.AddWithValue("@PhoneNo", PhoneNo);
        sql.Parameters.AddWithValue("@EmailID", EmailID);
        sql.Parameters.AddWithValue("@LM_ID", LM_ID);
        sql.Parameters.AddWithValue("@IndustryID", IndustryID);
        sql.Parameters.AddWithValue("@CompanyDesignation", CompanyDesignation);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable UpdateClientMaster(String CMID,String Name, String FatherName,
                                       String Address, String MobileNo,
                                       String PhoneNo, String EmailID,String OpeningBalance, String Remark,
                                        String DOB, String PresentCompanyName, String CurrentSalary, String ExpSalary, String dsClientdetails, String resumepath,String LM_ID,
                                         String Experience, String ParmanetAddress, String Gender, String Qualification,
                                         String JobRollID, String IndustryID, String FunctionalAreaID, String Duration, String JobStatusID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "UpdateClientMaster";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@CMID", CMID);
        sql.Parameters.AddWithValue("@Name", Name);
        sql.Parameters.AddWithValue("@FatherName", FatherName);
        sql.Parameters.AddWithValue("@Address", Address);
        sql.Parameters.AddWithValue("@MobileNo", MobileNo);
        sql.Parameters.AddWithValue("@PhoneNo", PhoneNo);
        sql.Parameters.AddWithValue("@EmailID", EmailID);
        sql.Parameters.AddWithValue("@OpeningBalance", OpeningBalance);
        sql.Parameters.AddWithValue("@Remark", Remark);
        sql.Parameters.AddWithValue("@LM_ID", LM_ID);
        sql.Parameters.AddWithValue("@DOB", DOB);
        sql.Parameters.AddWithValue("@PresentCompanyName", PresentCompanyName);
        sql.Parameters.AddWithValue("@CurrentSalary", CurrentSalary);
        sql.Parameters.AddWithValue("@ExpSalary", ExpSalary);
        sql.Parameters.AddWithValue("@dsClientdetails", dsClientdetails);
        sql.Parameters.AddWithValue("@ResumePath", resumepath);
        sql.Parameters.AddWithValue("@Experience", Experience);
        sql.Parameters.AddWithValue("@ParmanetAddress", ParmanetAddress);
        sql.Parameters.AddWithValue("@Gender", Gender);
        sql.Parameters.AddWithValue("@Qualification", Qualification);
        sql.Parameters.AddWithValue("@JobRollID", JobRollID);
        sql.Parameters.AddWithValue("@IndustryID", IndustryID);
        sql.Parameters.AddWithValue("@FunctionalAreaID", FunctionalAreaID);
        sql.Parameters.AddWithValue("@Duration", Duration);
        sql.Parameters.AddWithValue("@JobStatusID", JobStatusID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Insertusers(String username,String upass,String LM_ID, String FRM_ID, String Name, String FatherName,
                                       String Address, String MobileNo,
                                       String PhoneNo, String EmailID, String Remark, String CreatedBy)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Insertusers";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@username", username);
        sql.Parameters.AddWithValue("@upass", upass);
        sql.Parameters.AddWithValue("@LM_ID", LM_ID);
        sql.Parameters.AddWithValue("@FRM_ID", FRM_ID);
        sql.Parameters.AddWithValue("@Name", Name);
        sql.Parameters.AddWithValue("@FatherName", FatherName);
        sql.Parameters.AddWithValue("@Address", Address);
        sql.Parameters.AddWithValue("@MobileNo", MobileNo);
        sql.Parameters.AddWithValue("@PhoneNo", PhoneNo);
        sql.Parameters.AddWithValue("@EmailID", EmailID);
        sql.Parameters.AddWithValue("@Remark", Remark);
        sql.Parameters.AddWithValue("@CreatedBy", CreatedBy);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable UpdateUsers(String userid, String username, String upass, String Name, String FatherName,
                                       String Address, String MobileNo,
                                       String PhoneNo, String EmailID, String Remark)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "UpdateUsers";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@userid", userid);
        sql.Parameters.AddWithValue("@username", username);
        sql.Parameters.AddWithValue("@upass", upass);
        sql.Parameters.AddWithValue("@Name", Name);
        sql.Parameters.AddWithValue("@FatherName", FatherName);
        sql.Parameters.AddWithValue("@Address", Address);
        sql.Parameters.AddWithValue("@MobileNo", MobileNo);
        sql.Parameters.AddWithValue("@PhoneNo", PhoneNo);
        sql.Parameters.AddWithValue("@EmailID", EmailID);
        sql.Parameters.AddWithValue("@Remark", Remark);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }
    public DataTable ListUser(String userid, String SelectionType)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "ListUser";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@userid", userid);
        sql.Parameters.AddWithValue("@SelectionType", SelectionType);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataSet GetLoginMember(String username,String upass)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet dt;

// Setting Stored Procedure
        sql.CommandText = "GetLoginMember";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@username", username);
        sql.Parameters.AddWithValue("@upass", upass);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataSet();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Contacts_To_Transfer(Int64 MasterAccountID,
                                          String AllAccountIDS,
                                          String Email)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Contacts_To_Transfer";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public String DeleteSchedule(string ScheduleID)
    {
        SqlConnection connection = OpenConnection();
        SqlCommand thiscommand;
        String Result = "";

thiscommand = new SqlCommand("Delete_Schedule", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.AddWithValue("@ScheduleID", ScheduleID);

Result = thiscommand.ExecuteScalar().ToString();
        connection.Close();
        return Result;
    }

public DataTable Get_MasterEntries(String MasterType)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_MasterEntries";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@MasterType", MasterType);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_AccountsOwnerWise(string TypeID, string TypeName, string SearchID,
                                           string LoginID, int pageno, int pagerowcount,
                                           string sorthingcolno, string sort, string IsMyAccount,
                                           string SearchText, out int TotalRowCount)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;
        TotalRowCount = 0;
        sql.CommandText = "Get_AccountsOwnerWise";
        sql.CommandType = CommandType.StoredProcedure;

sql.Parameters.AddWithValue("@TypeID", TypeID);
        sql.Parameters.AddWithValue("@TypeName", TypeName);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@LoginID", LoginID);
        sql.Parameters.AddWithValue("@PageNumber", pageno);
        sql.Parameters.AddWithValue("@PageRowCount", pagerowcount);
        sql.Parameters.AddWithValue("@SorthingCol", sorthingcolno);
        sql.Parameters.AddWithValue("@Sort", sort);
        sql.Parameters.AddWithValue("@IsMyAccount", IsMyAccount);

if (SearchText.Length == 0)
        {
            sql.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 300)).Value = DBNull.Value;
        }
        else
        {
            sql.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 300)).Value = SearchText;
        }

//sql.Parameters.AddWithValue("@SearchText", SearchText);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);

sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

sql.Connection = Conn;
        sql.CommandTimeout = 240;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);
        TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);
        // Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_AccountsGruopByCountry(String SearchID,
                                                int GroupBY,
                                                int OwnerID,
                                                out Int64 TotalRowCount,
                                                out String SearchName)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();

sql.CommandText = "Get_DashBroardReports";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 720;
        sql.Connection = Conn;

// Setting SP Parameter
        SearchName = String.Empty;
        TotalRowCount = 0;

sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@GroupBY", GroupBY);
        sql.Parameters.AddWithValue("@OwnerID", OwnerID);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;
        sql.Parameters.Add(new SqlParameter("@SearchName", SqlDbType.NVarChar, 300, SearchName));// WithValue("@SearchName", SearchName);
        sql.Parameters["@SearchName"].Direction = ParameterDirection.Output;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        SqlDataAdapter sda = new SqlDataAdapter(sql);
        DataTable dt = new DataTable();
        sda.Fill(dt);

//Filling Out Parameter
        SearchName = Convert.ToString(sql.Parameters["@SearchName"].Value);

//TotalRowCount = Convert.ToInt64(sql.Parameters["@TotalRowCount"].Value);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return dt;
    }

public DataSet Get_MsterGroupName(int SearchID, int OwnerID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet dsResult;
        sql.Parameters.Clear();
        sql.CommandText = "Get_MsterGroupName";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@OwnerID", OwnerID);
        sql.Connection = Conn;

//Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dsResult = new DataSet();
        sda.Fill(dsResult);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return dsResult;

}

public DataSet Get_AccountsContactsForAdvanceView(string AccountIDS, Int32 LoginID, string SearchID,
                                                      string AccSortColNo, string AccSortDirection,
                                                      Int16 IsCriteria, out Int64 TotalExcludedAccounts)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_AccountsContactsDetailForAdvanceView";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

TotalExcludedAccounts = 0;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@AccountIDS", AccountIDS);
        sql.Parameters.AddWithValue("@LoginID", LoginID);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@AccountSortingCol", AccSortColNo);
        sql.Parameters.AddWithValue("@AccountSort", AccSortDirection);
        sql.Parameters.AddWithValue("@IsCriteria", IsCriteria);

sql.Parameters.Add("@TotalExcludedAccounts", @TotalExcludedAccounts);
        sql.Parameters["@TotalExcludedAccounts"].Direction = ParameterDirection.Output;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

TotalExcludedAccounts = Convert.ToInt64(sql.Parameters["@TotalExcludedAccounts"].Value);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds;
    }

public int CreateSchedule(int DBSID, int OwnerID, int TabID, int SenderID, int CreatorID,
                            int ModifierID, int ScheduleTypeID, DateTime ScheduleTime,
                            int ScheduleDAY, int ScheduleDate, int ScheduleMonth, string ReceiverID,
                            string CcReceiverIDs, string BCcReceiverIDs, out int MaxDBSID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        connection = OpenConnection();
        thiscommand = new SqlCommand("InsertUpdate_DashBoardScheduleEmails", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        MaxDBSID = 0;

thiscommand.Parameters.AddWithValue("@DBSID", DBSID);
        thiscommand.Parameters.AddWithValue("@DBOwnerID", OwnerID);
        thiscommand.Parameters.AddWithValue("@DBTabID", TabID);
        thiscommand.Parameters.AddWithValue("@IsActive", 1);
        thiscommand.Parameters.AddWithValue("@SenderID", SenderID);
        thiscommand.Parameters.AddWithValue("@LoginID", CreatorID);
        thiscommand.Parameters.AddWithValue("@ScheduleTypeID", ScheduleTypeID);
        thiscommand.Parameters.AddWithValue("@ScheduleTime", ScheduleTime);
        thiscommand.Parameters.AddWithValue("@ScheduleDAY", ScheduleDAY);
        thiscommand.Parameters.AddWithValue("@ScheduleDate", ScheduleDate);
        thiscommand.Parameters.AddWithValue("@ScheduleMonth", ScheduleMonth);
        thiscommand.Parameters.AddWithValue("@ToReceiverIDs", ReceiverID);
        thiscommand.Parameters.AddWithValue("@CcReceiverIDs", CcReceiverIDs);
        thiscommand.Parameters.AddWithValue("@BCcReceiverIDs", BCcReceiverIDs);
        thiscommand.Parameters.AddWithValue("@MaxDBSID", MaxDBSID);
        thiscommand.Parameters["@MaxDBSID"].Direction = ParameterDirection.Output;
        thiscommand.ExecuteNonQuery();
        connection.Close();

MaxDBSID = Convert.ToInt32(thiscommand.Parameters["@TotalRowCount"].Value);
        return 1;
    }

public DataSet Get_ScheduleTabReportDetails(int TabID, int OwnerID)
    {

SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet dsResult;
        sql.Parameters.Clear();
        sql.CommandText = "Get_TabScheduleReportDetail";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Parameters.AddWithValue("@TabID", TabID);
        sql.Parameters.AddWithValue("@OwnerID", OwnerID);
        sql.Connection = Conn;

//Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dsResult = new DataSet();
        sda.Fill(dsResult);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return dsResult;
    }

public int ChangeIframeTab(string Action, int OwnerID, int SearchID, int TabID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        connection = OpenConnection();
        thiscommand = new SqlCommand("InsertUpdateTabReports", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@Action", Action);
        thiscommand.Parameters.AddWithValue("@OwnerID", OwnerID);
        thiscommand.Parameters.AddWithValue("@SearchID", SearchID);
        thiscommand.Parameters.AddWithValue("@TabID", TabID);
        thiscommand.Parameters.AddWithValue("@MasterTableID", 0);

thiscommand.ExecuteNonQuery();
        connection.Close();
        return 1;
    }

public DataSet Get_IfremaeDetails(int OwnerID, int SearchID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet dsTabReports;
        sql.Parameters.Clear();
        sql.CommandText = "GET_IframDetail";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Parameters.AddWithValue("@OwnerID", OwnerID);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Connection = Conn;

//Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dsTabReports = new DataSet();
        sda.Fill(dsTabReports);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return dsTabReports;
    }

public string Set_ParentChildRelation(String ParentAccountID, String ChildAccountIDS,
                                          String Email, String MoveContactsToAccount, String LoginID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Set_ParentChildRelation", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Clear();

thiscommand.Parameters.AddWithValue("@ParentAccountID", ParentAccountID);
        thiscommand.Parameters.AddWithValue("@ChildAccountIDS", ChildAccountIDS);
        thiscommand.Parameters.AddWithValue("@Email", Email);
        thiscommand.Parameters.AddWithValue("@MoveContactsToAccount", MoveContactsToAccount);
        thiscommand.Parameters.AddWithValue("@LoginID", LoginID);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_ParentChildAccountDetails(String AccountIDS)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_ParentChildAccountDetails";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@AccountIDS", AccountIDS);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_AccountContacts(string AccountID, string LoginID, string SearchID,
                                         string PageNumber, string PageRowCount, string SorthingCol,
                                         string Sort, out int TotalRowCount)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

TotalRowCount = 0;

conn = OpenConnection();

sql = new SqlCommand("Get_AccountContacts", conn);
        sql.CommandType = CommandType.StoredProcedure;

sql.Parameters.AddWithValue("@AccountID", AccountID);
        sql.Parameters.AddWithValue("@LoginID", LoginID);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@PageNumber", PageNumber);
        sql.Parameters.AddWithValue("@PageRowCount", PageRowCount);
        sql.Parameters.AddWithValue("@SorthingCol", SorthingCol);
        sql.Parameters.AddWithValue("@Sort", Sort);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);

conn.Close();
        conn = null;

return dt;
    }

public string Get_DuplicateContactsCount(String AccountID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Get_DuplicateContactsCount", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@AccountID", AccountID);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_DuplicateContact_By_EmailAndContactName(Int64 AccountID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateContact_By_EmailAndContactName";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@AccountID", AccountID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_EmailAndContactNameBaseDuplicateContacts(String Email, String ContactName, String AccountID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_EmailAndContactNameBaseDuplicateContacts";
        sql.CommandTimeout = 240;
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@ContactName", ContactName);
        sql.Parameters.AddWithValue("@AccountID", AccountID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable EmailBatchLog(String FromDate, String ToDate, String Mode)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "R_EmailBatchLog";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@FromDate", FromDate);
        sql.Parameters.AddWithValue("@ToDate", ToDate);
        sql.Parameters.AddWithValue("@Mode", Mode);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public string InsertUpdateEmailScheduleRate(String NoofEmails, String NoofMinutes,
                                            String NotWorkingFrom, String NotWorkingTo)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("InsertUpdateEmailScheduleRate", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Clear();

thiscommand.Parameters.AddWithValue("@NoofEmails", NoofEmails);
        thiscommand.Parameters.AddWithValue("@NoofMinutes", NoofMinutes);
        thiscommand.Parameters.AddWithValue("@NotWorkingFrom", NotWorkingFrom);
        thiscommand.Parameters.AddWithValue("@NotWorkingTo", NotWorkingTo);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataSet Get_EmailScheduleRate(String FromDate, String ToDate)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;
        sql.Parameters.Clear();
        sql.CommandText = "Get_EmailScheduleRate";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@FromDate", FromDate);
        sql.Parameters.AddWithValue("@ToDate", ToDate);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return ds;
    }

public DataTable GetDomainDetails(Int64 CampaignID, DateTime LineUpDate, String BatchType)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "GetDomainDetails";
        sql.CommandType = CommandType.StoredProcedure;
        // Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@CampaignID", CampaignID);
        sql.Parameters.AddWithValue("@LineUpDate", LineUpDate);
        sql.Parameters.AddWithValue("@BatchType", BatchType);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable GetBatchDetails(Int64 CampaignID, DateTime LineUpDate, String BatchType)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "GetBatchDetails";
        sql.CommandType = CommandType.StoredProcedure;
        // Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@CampaignID", CampaignID);
        sql.Parameters.AddWithValue("@LineUpDate", LineUpDate);
        sql.Parameters.AddWithValue("@BatchType", BatchType);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable GetOwnerHavingLeads(int OwnerID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        DataSet dsResult = new DataSet();
        SqlDataAdapter adapter;
        DataTable dtItem = new DataTable();
        connection = OpenConnection();

thiscommand = new SqlCommand("GetOwnerHavingLeads", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.AddWithValue("@OwnerID", OwnerID);
        adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtItem);
        connection.Close();
        return dtItem;
    }

public DataTable Get_DuplicatePhone_For_ContactMerge(Int64 DPID, String Mode)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicatePhone_For_ContactMerge";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.Add(new SqlParameter("@DPID", SqlDbType.VarChar, 1000)).Value = DPID;
        sql.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar, 1000)).Value = Mode;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_DuplicatePhone_For_ContactMergeWithSearchID(String DPWSID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicatePhone_For_ContactMergeWithSearchID";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@DPWSID", DPWSID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_DuplicateEmails_For_ContactMerge(String DEID, String Mode)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateEmails_For_ContactMerge";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@DEID", DEID);
        sql.Parameters.AddWithValue("@Mode", Mode);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_DuplicateEmails_For_ContactMergeWithSearchID(String DEID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateEmails_For_ContactMergeWithSearchID";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@DEID", DEID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_PrevAccountsInfo(Int64 ContactID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_PrevAccountsInfo";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@ContactID", ContactID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable R_CommunicationSummaryOwnerWise(String Email, Int64 ContactID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dtResult = new DataTable();
        DataSet dsResult = new DataSet();

// Setting Stored Procedure
        sql.CommandText = "R_CommunicationSummaryOwnerWise";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@ContactID", Convert.ToInt64(ContactID));
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        sda.Fill(dsResult);

dtResult = R_CommunicationSummaryOwnerWise_CreateTable(dsResult);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dtResult;
    }

public DataTable R_CommunicationSummaryOwnerWise_CreateTable(DataSet _ds)
    {
        DataTable dtResult = null;
        if (_ds.Tables != null &&
            _ds.Tables[0].Rows != null &&
            _ds.Tables[0].Rows.Count > 0 &&
            _ds.Tables[1].Rows.Count > 0)
        {
            #region ...Creating DataColumns in DataTable
            Int32 QCount = 0;
            Int32 Count = 0;
            DataTable _dtMainCommunicationtable = new DataTable();
            _dtMainCommunicationtable.Columns.Add(new DataColumn("SNo", typeof(Int32)));
            _dtMainCommunicationtable.Columns.Add(new DataColumn("CommunicationType", typeof(String)));
            Int32 SnoQuarterCnt = 0;
            foreach (DataRow _rowheader in _ds.Tables[1].Rows)
            {
                _dtMainCommunicationtable.Columns.Add(new DataColumn(_rowheader["OwnerID"].ToString() + "_" + _rowheader["OwnerName"].ToString(), typeof(String)));
                _dtMainCommunicationtable.Columns[_rowheader["OwnerID"].ToString() + "_" + _rowheader["OwnerName"].ToString()].DefaultValue = "0";
            }

_dtMainCommunicationtable.Columns.Add(new DataColumn("GrandTotal", typeof(String)));
            _dtMainCommunicationtable.Columns["GrandTotal"].DefaultValue = "0";

#endregion

#region ...[Creating DataRows in Datatable]...
            String ColumnQName = "";
            String ColumnQText = "";
            String PrevCommunicationType = "";
            String CommunicationType = "";
            DataView dvQNew = null;
            DataRow _newRowQData = null;

//Fetching Starting Quater Information
            if (_ds.Tables[0].Rows.Count > 0)
            {
                PrevCommunicationType = _ds.Tables[0].Rows[0]["CommunicationType"].ToString();
            }

DataTable dtCopyQuarter = _dtMainCommunicationtable.Copy();

foreach (DataRow _rowData in _ds.Tables[0].Rows)
            {
                CommunicationType = Convert.ToString(_rowData["CommunicationType"]).Trim();

_newRowQData = _dtMainCommunicationtable.NewRow();

dvQNew = dtCopyQuarter.DefaultView;
                dvQNew.RowFilter = "CommunicationType ='" + CommunicationType.Trim() + "'";

if (dvQNew.ToTable().Rows.Count == 0)
                {
                    foreach (DataColumn dcCol in _dtMainCommunicationtable.Columns)
                    {
                        String[] strArrColumnName = dcCol.ColumnName.Split('_');
                        if (strArrColumnName.Length > 0)
                        {
                            if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["OwnerID"]).Trim())
                            {
                                ColumnQName = strArrColumnName[0].ToString();
                                ColumnQText = strArrColumnName[1].ToString();
                                ColumnQName = ColumnQName + "_" + ColumnQText;
                            }
                        }
                    }

//Filling Country Wise Opportunity
                    _newRowQData[ColumnQName] = Convert.ToString(_rowData["CommCount"]);
                    SnoQuarterCnt = SnoQuarterCnt + 1;
                    _newRowQData["SNo"] = SnoQuarterCnt;
                    _newRowQData["CommunicationType"] = CommunicationType;
                    _dtMainCommunicationtable.Rows.Add(_newRowQData);
                    _dtMainCommunicationtable.AcceptChanges();

_newRowQData = dtCopyQuarter.NewRow();
                    _newRowQData[ColumnQName] = Convert.ToString(_rowData["CommCount"]);
                    //SnoCnt = SnoCnt + 1;
                    _newRowQData["SNo"] = SnoQuarterCnt;
                    _newRowQData["CommunicationType"] = CommunicationType;
                    dtCopyQuarter.Rows.Add(_newRowQData);
                    dtCopyQuarter.AcceptChanges();
                }
                else
                {
                    DataRow[] drTemp = _dtMainCommunicationtable.Select("CommunicationType ='" + CommunicationType.Trim() + "'");
                    if (drTemp.Length > 0)
                    {
                        foreach (DataColumn dcCol in _dtMainCommunicationtable.Columns)
                        {
                            String[] strArrColumnName = dcCol.ColumnName.Split('_');
                            if (strArrColumnName.Length > 0)
                            {
                                if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["OwnerID"]).Trim())
                                {
                                    ColumnQName = strArrColumnName[0].ToString();
                                    ColumnQText = strArrColumnName[1].ToString();
                                    ColumnQName = ColumnQName + "_" + ColumnQText;
                                }
                            }
                        }

//Filling Country Wise Opportunity
                        drTemp[0][ColumnQName] = Convert.ToString(_rowData["CommCount"]);
                        _dtMainCommunicationtable.AcceptChanges();
                    }
                }
                PrevCommunicationType = CommunicationType;
            }
            #endregion

#region ...[Fetching Grand Total For All Years and Quarters]...
            bool isGrandTotal = false;
            for (int m = 0; m < _dtMainCommunicationtable.Columns.Count; m++)
            {
                if (m > 2)
                {
                    for (int h = 0; h < _dtMainCommunicationtable.Rows.Count; h++)
                    {
                        if (Convert.ToString(_dtMainCommunicationtable.Rows[h][m]).Trim().Length > 0 && Convert.ToInt32(_dtMainCommunicationtable.Rows[h][0]) == 0)
                        {
                            Count = Count + Convert.ToInt32(_dtMainCommunicationtable.Rows[h][m]);
                            isGrandTotal = true;
                        }
                    }

if (isGrandTotal == true)
                    {
                        _dtMainCommunicationtable.Rows[_dtMainCommunicationtable.Rows.Count - 1][m] = Count.ToString();
                        Count = 0;
                        _dtMainCommunicationtable.AcceptChanges();
                        isGrandTotal = false;
                    }
                }
            }
            #endregion

#region ...[Setting Totals of DataTable]...

for (int i = 0; i < _dtMainCommunicationtable.Rows.Count; i++)
            {
                for (int j = 0; j < _dtMainCommunicationtable.Columns.Count; j++)
                {
                    if (Convert.ToString(_dtMainCommunicationtable.Rows[i][j]).Trim().Length > 0 && j > 1)
                    {
                        Count = Count + Convert.ToInt32(_dtMainCommunicationtable.Rows[i][j]);
                    }
                }

_dtMainCommunicationtable.Rows[i]["GrandTotal"] = Count.ToString();
                Count = 0;
                _dtMainCommunicationtable.AcceptChanges();
            }

Count = 0;

for (int m = 0; m < _dtMainCommunicationtable.Columns.Count; m++)
            {
                if (m > 1)
                {
                    for (int h = 0; h < _dtMainCommunicationtable.Rows.Count; h++)
                    {
                        if (Convert.ToString(_dtMainCommunicationtable.Rows[h]["CommunicationType"]).Trim() == "Notes Communication (Total)" ||
                            Convert.ToString(_dtMainCommunicationtable.Rows[h]["CommunicationType"]).Trim() == "Email Communication (Total)")
                        {
                            if (Convert.ToString(_dtMainCommunicationtable.Rows[h][m]).Trim().Length > 0)
                            {
                                Count = Count + Convert.ToInt32(_dtMainCommunicationtable.Rows[h][m]);
                            }
                        }
                    }

_dtMainCommunicationtable.Rows[_dtMainCommunicationtable.Rows.Count - 1][m] = Count.ToString();
                    Count = 0;
                    _dtMainCommunicationtable.AcceptChanges();
                }
            }

for (int m = 0; m < _dtMainCommunicationtable.Columns.Count; m++)
            {
                if (m > 1)
                {
                    string[] strCol = _dtMainCommunicationtable.Columns[m].ColumnName.Split('_');
                    if (strCol.Length > 1)
                    {
                        _dtMainCommunicationtable.Columns[m].ColumnName = strCol[1].ToString();
                    }
                }
            }
            dtResult = _dtMainCommunicationtable;
            #endregion
        }
        return dtResult;
    }

public DataTable Get_DuplicatePhone(Int64 DPID, String Mode, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicatePhone";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@DPID", DPID);
        sql.Parameters.AddWithValue("@Mode", Mode);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_DuplicatePhoneWithSearchID(String DPID, String Mode, String SearchID, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicatePhoneWithSearchID";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@DPID", DPID);
        sql.Parameters.AddWithValue("@Mode", Mode);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_DuplicatePhone(Int64 DPID, String Mode)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicatePhone";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.Add(new SqlParameter("@DPID", SqlDbType.VarChar, 1000)).Value = DPID;
        sql.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar, 1000)).Value = Mode;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_PhoneBaseDuplicateAccounts(String MainPhone, Int32 BillingCityID, Int32 CountryID, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_PhoneBaseDuplicateAccounts";
        sql.CommandTimeout = 420;
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@MainPhone", MainPhone);
        sql.Parameters.AddWithValue("@BillingCityID", BillingCityID);
        sql.Parameters.AddWithValue("@BillingCountryID", CountryID);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string Delete_DuplicatePhone(Int64 DPID, String Value)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Delete_DuplicatePhone", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@DPID", DPID);
        thiscommand.Parameters.AddWithValue("@Value", Value);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public string Delete_DuplicatePhoneWithSearchID(String DPWSID, String SearchID, String Value)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Delete_DuplicatePhoneWithSearchID", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@DPWSID", DPWSID);
        thiscommand.Parameters.AddWithValue("@SearchID", SearchID);
        thiscommand.Parameters.AddWithValue("@Value", Value);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_Country_By_IDS(String CountryIDS)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_Country_By_IDS";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@CountryIDS", CountryIDS);
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable R_AccountsSegmentationReport(Int32 AccountListID,
                                                  Int32 ClassificationID,
                                                  string CountryIDs)
    {

SqlConnection connection = null;
        SqlCommand thiscommand;
        SqlDataAdapter adapter;
        DataTable dtResult = new DataTable();
        DataSet dsResult = new DataSet();
        connection = OpenConnection();

thiscommand = new SqlCommand("R_AccountsSegmentationReport", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.CommandTimeout = 240;

thiscommand.Parameters.AddWithValue("@AccountListID", AccountListID);
        thiscommand.Parameters.AddWithValue("@ClassificationID", ClassificationID);
        thiscommand.Parameters.AddWithValue("@CountryIDs", CountryIDs);

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dsResult);

connection.Close();
        connection = null;

//return dtResult;

dtResult = R_AccountsSegmentationReport_CreateTable(dsResult, ClassificationID);

return dtResult;
    }

public DataTable R_AccountsSegmentationReport_CreateTable(DataSet _ds, Int32 ClassificationID)
    {
        DataTable dtResult = null;
        if (_ds.Tables != null &&
            _ds.Tables[0].Rows != null &&
            _ds.Tables[0].Rows.Count > 0 &&
            _ds.Tables[1].Rows.Count > 0)
        {
            if (ClassificationID > 0 && ClassificationID < 7)
            {
                #region ...[Classification Table]...

#region ...Creating DataColumns in DataTable
                Int32 Count = 0;
                DataTable _dtmaintable = new DataTable();
                _dtmaintable.Columns.Add(new DataColumn("SNo", typeof(Int32)));
                _dtmaintable.Columns.Add(new DataColumn("CountryName", typeof(String)));
                Int32 SnoCnt = 0;
                foreach (DataRow _rowheader in _ds.Tables[1].Rows)
                {
                    //_dtmaintable.Columns.Add( _rowheader["CountryID"] + "_" + _rowheader["CountryName"], typeof(String));
                    _dtmaintable.Columns.Add(new DataColumn(_rowheader["ClassificationID"] + "_" + _rowheader["ClassificationName"], typeof(String)));
                }

_dtmaintable.Columns.Add(new DataColumn("GrandTotal", typeof(String)));
                _dtmaintable.Columns["GrandTotal"].DefaultValue = "0";

#endregion

#region ...[Creating DataRows in Datatable]...
                String ColumnName = "";
                String ColumnText = "";
                DataView dvNew = null;
                DataRow _newRowData = null;

DataTable dtCopyCampaign = _dtmaintable.Copy();

foreach (DataRow _rowData in _ds.Tables[0].Rows)
                {
                    _newRowData = _dtmaintable.NewRow();
                    dvNew = dtCopyCampaign.DefaultView;
                    dvNew.RowFilter = "CountryName ='" + Convert.ToString(_rowData["CountryName"]).Trim() + "'";

if (dvNew.ToTable().Rows.Count == 0)
                    {
                        _newRowData["CountryName"] = Convert.ToString(_rowData["CountryName"]);

foreach (DataColumn dcCol in _dtmaintable.Columns)
                        {
                            String[] strArrColumnName = dcCol.ColumnName.Split('_');
                            if (strArrColumnName.Length > 0)
                            {
                                if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["ClassificationID"]).Trim())
                                {
                                    ColumnName = strArrColumnName[0].ToString();
                                    ColumnText = strArrColumnName[1].ToString();
                                    ColumnName = ColumnName + "_" + ColumnText;
                                }
                            }
                        }

//Filling Country Wise Opportunity
                        _newRowData[ColumnName] = Convert.ToString(_rowData["ClassificationCount"]);
                        SnoCnt = SnoCnt + 1;
                        _newRowData["SNo"] = SnoCnt;
                        _dtmaintable.Rows.Add(_newRowData);
                        _dtmaintable.AcceptChanges();

_newRowData = dtCopyCampaign.NewRow();
                        _newRowData[ColumnName] = Convert.ToString(_rowData["ClassificationCount"]);
                        //SnoCnt = SnoCnt + 1;
                        _newRowData["SNo"] = SnoCnt;
                        _newRowData["CountryName"] = Convert.ToString(_rowData["CountryName"]);
                        dtCopyCampaign.Rows.Add(_newRowData);
                        dtCopyCampaign.AcceptChanges();
                    }
                    else
                    {
                        DataRow[] drTemp = _dtmaintable.Select("CountryName='" + Convert.ToString(_rowData["CountryName"]).Trim() + "'");
                        if (drTemp.Length > 0)
                        {
                            drTemp[0]["CountryName"] = Convert.ToString(_rowData["CountryName"]);

foreach (DataColumn dcCol in _dtmaintable.Columns)
                            {
                                String[] strArrColumnName = dcCol.ColumnName.Split('_');
                                if (strArrColumnName.Length > 0)
                                {
                                    if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["ClassificationID"]).Trim())
                                    {
                                        ColumnName = strArrColumnName[0].ToString();
                                        ColumnText = strArrColumnName[1].ToString();
                                        ColumnName = ColumnName + "_" + ColumnText;
                                    }
                                }
                            }

//Filling Country Wise Opportunity
                            drTemp[0][ColumnName] = Convert.ToString(_rowData["ClassificationCount"]);
                            _dtmaintable.AcceptChanges();
                        }
                    }
                }

//Add the Last Row Named GrandTotal in DataTable
                DataRow _LastRow = _dtmaintable.NewRow();
                _LastRow["SNo"] = 0;
                _LastRow["CountryName"] = "Grand Total";
                _dtmaintable.Rows.Add(_LastRow);

#endregion

#region ...[Setting Totals of DataTable]...

for (int i = 0; i < _dtmaintable.Rows.Count; i++)
                {
                    for (int j = 0; j < _dtmaintable.Columns.Count; j++)
                    {
                        if (Convert.ToString(_dtmaintable.Rows[i][j]).Trim().Length > 0 && j > 1)
                        {
                            Count = Count + Convert.ToInt32(_dtmaintable.Rows[i][j]);
                        }
                    }

_dtmaintable.Rows[i]["GrandTotal"] = Count.ToString();
                    Count = 0;
                    _dtmaintable.AcceptChanges();
                }

Count = 0;

for (int m = 0; m < _dtmaintable.Columns.Count; m++)
                {
                    if (m > 1)
                    {
                        for (int h = 0; h < _dtmaintable.Rows.Count; h++)
                        {
                            if (Convert.ToString(_dtmaintable.Rows[h][m]).Trim().Length > 0)
                            {
                                Count = Count + Convert.ToInt32(_dtmaintable.Rows[h][m]);
                            }
                            else if (Convert.ToString(_dtmaintable.Rows[h][m]).Trim().Length == 0)
                            {
                                _dtmaintable.Rows[h][m] = "-";
                            }
                        }

_dtmaintable.Rows[_dtmaintable.Rows.Count - 1][m] = Count.ToString();
                        Count = 0;
                        _dtmaintable.AcceptChanges();
                    }
                }

for (int m = 0; m < _dtmaintable.Columns.Count; m++)
                {
                    if (m > 1)
                    {
                        string[] strCol = _dtmaintable.Columns[m].ColumnName.Split('_');
                        if (strCol.Length > 1)
                        {
                            _dtmaintable.Columns[m].ColumnName = strCol[1].ToString();
                        }
                    }
                }

#endregion

dtResult = _dtmaintable;

#endregion
            }
        }
        return dtResult;
    }

public string IncludeAccountsToAccountList(String AccountIDS)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("IncludeAccountsToAccountList", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@AccountIDS", AccountIDS);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_ExcludedAccounts(Int32 SaveSearchID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_ExcludedAccounts";
        sql.CommandType = CommandType.StoredProcedure;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@SaveSearchID", SaveSearchID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string Enrich_Account(Int64 AccountID, Int32 LoginID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Enrich_Account", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@AccountID", AccountID);
        thiscommand.Parameters.AddWithValue("@LoginID", LoginID);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public string ExcludeAccountFrom_SavedSearch(Int64 AccountID, Int32 SaveSearchID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("ExcludeAccountFrom_SavedSearch", connection);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable R_CountryAndCampaignWiseMQL(string OwnerID, string OwnerGroupID,
                                                     string CampaignID, string CampaignGroupID,
                                                     string AccountListID,
                                                     string FromDate, string ToDate,
                                                     string SalesStageID, string LoginID, string Duration)
    {
        SqlConnection connection = OpenConnection();
        SqlCommand thiscommand;
        DataSet dsResult = new DataSet();
        DataTable dtResult = new DataTable();
        SqlDataAdapter adapter;

thiscommand = new SqlCommand("R_CountryAndCampaignWiseMQL", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dsResult);
        dtResult = R_CountryAndCampaignWiseMQL_CreateTable(dsResult, Duration);
        connection.Close();
        return dtResult;
    }

public DataTable R_CountryAndCampaignWiseMQL_CreateTable(DataSet _ds, String Duration)
    {
        DataTable dtResult = null;
        if (_ds.Tables[0].Rows.Count > 0 && _ds.Tables[1].Rows.Count > 0)
        {
            if (Duration == "CampaignGroup")
            {
                #region ...[Group By CampaignGroup]...

#region ...Creating DataColumns in DataTable
                Int32 Count = 0;
                DataTable _dtmaintable = new DataTable();
                _dtmaintable.Columns.Add(new DataColumn("SNo", typeof(Int32)));
                _dtmaintable.Columns.Add(new DataColumn("CampaignGroup", typeof(String)));
                Int32 SnoCnt = 0;
                foreach (DataRow _rowheader in _ds.Tables[1].Rows)
                {
                    //_dtmaintable.Columns.Add( _rowheader["CountryID"] + "_" + _rowheader["CountryName"], typeof(String));
                    _dtmaintable.Columns.Add(new DataColumn(_rowheader["CountryID"] + "_" + _rowheader["CountryName"], typeof(String)));
                }

_dtmaintable.Columns.Add(new DataColumn("GrandTotal", typeof(String)));
                _dtmaintable.Columns["GrandTotal"].DefaultValue = "0";

#endregion

DataTable dtCopyCampaign = _dtmaintable.Copy();

foreach (DataRow _rowData in _ds.Tables[0].Rows)
                {
                    _newRowData = _dtmaintable.NewRow();
                    dvNew = dtCopyCampaign.DefaultView;
                    dvNew.RowFilter = "CampaignGroup ='" + Convert.ToString(_rowData["SourceCode"]).Trim() + "'";

if (dvNew.ToTable().Rows.Count == 0)
                    {
                        _newRowData["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);

foreach (DataColumn dcCol in _dtmaintable.Columns)
                        {
                            String[] strArrColumnName = dcCol.ColumnName.Split('_');
                            if (strArrColumnName.Length > 0)
                            {
                                if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["CountryID"]).Trim())
                                {
                                    ColumnName = strArrColumnName[0].ToString();
                                    ColumnText = strArrColumnName[1].ToString();
                                    ColumnName = ColumnName + "_" + ColumnText;
                                }
                            }
                        }

//Filling Country Wise Opportunity
                        _newRowData[ColumnName] = Convert.ToString(_rowData["TotalOpportunity"]);
                        SnoCnt = SnoCnt + 1;
                        _newRowData["SNo"] = SnoCnt;
                        _dtmaintable.Rows.Add(_newRowData);
                        _dtmaintable.AcceptChanges();

_newRowData = dtCopyCampaign.NewRow();
                        _newRowData[ColumnName] = Convert.ToString(_rowData["TotalOpportunity"]);
                        //SnoCnt = SnoCnt + 1;
                        _newRowData["SNo"] = SnoCnt;
                        _newRowData["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);
                        dtCopyCampaign.Rows.Add(_newRowData);
                        dtCopyCampaign.AcceptChanges();
                    }
                    else
                    {
                        DataRow[] drTemp = _dtmaintable.Select("CampaignGroup='" + Convert.ToString(_rowData["SourceCode"]).Trim() + "'");
                        if (drTemp.Length > 0)
                        {
                            drTemp[0]["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);

foreach (DataColumn dcCol in _dtmaintable.Columns)
                            {
                                String[] strArrColumnName = dcCol.ColumnName.Split('_');
                                if (strArrColumnName.Length > 0)
                                {
                                    if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["CountryID"]).Trim())
                                    {
                                        ColumnName = strArrColumnName[0].ToString();
                                        ColumnText = strArrColumnName[1].ToString();
                                        ColumnName = ColumnName + "_" + ColumnText;
                                    }
                                }
                            }

//Filling Country Wise Opportunity
                            drTemp[0][ColumnName] = Convert.ToString(_rowData["TotalOpportunity"]);
                            _dtmaintable.AcceptChanges();
                        }
                    }
                }

//Add the Last Row Named GrandTotal in DataTable
                DataRow _LastRow = _dtmaintable.NewRow();
                _LastRow["SNo"] = 0;
                _LastRow["CampaignGroup"] = "Grand Total";
                _dtmaintable.Rows.Add(_LastRow);

#endregion

#region ...[Setting Totals of DataTable]...

_dtmaintable.Rows[i]["GrandTotal"] = Count.ToString();
                    Count = 0;
                    _dtmaintable.AcceptChanges();
                }

Count = 0;

_dtmaintable.Rows[_dtmaintable.Rows.Count - 1][m] = Count.ToString();
                        Count = 0;
                        _dtmaintable.AcceptChanges();
                    }
                }

#endregion

dtResult = _dtmaintable;

#endregion
            }
            else if (Duration == "QuarterlyGroup")
            {
                #region ...[Group By Quarter]...

#region ...Creating DataColumns in DataTable
                Int32 QCount = 0;
                DataTable _dtmainQuartertable = new DataTable();
                _dtmainQuartertable.Columns.Add(new DataColumn("SNo", typeof(Int32)));
                _dtmainQuartertable.Columns.Add(new DataColumn("Quarter", typeof(String)));
                _dtmainQuartertable.Columns.Add(new DataColumn("CampaignGroup", typeof(String)));
                Int32 SnoQuarterCnt = 0;
                foreach (DataRow _rowheader in _ds.Tables[1].Rows)
                {
                    //_dtmaintable.Columns.Add( _rowheader["CountryID"] + "_" + _rowheader["CountryName"], typeof(String));
                    _dtmainQuartertable.Columns.Add(new DataColumn(_rowheader["CountryID"] + "_" + _rowheader["CountryName"], typeof(String)));
                }

_dtmainQuartertable.Columns.Add(new DataColumn("GrandTotal", typeof(String)));
                _dtmainQuartertable.Columns["GrandTotal"].DefaultValue = "0";

#endregion

#region ...[Creating DataRows in Datatable]...
                String ColumnQName = "";
                String ColumnQText = "";
                String PrevQuarter = "";
                String Quarter = "";
                DataView dvQNew = null;
                DataRow _newRowQData = null;

//Fetching Starting Quater Information
                if (_ds.Tables[0].Rows.Count > 0)
                {
                    PrevQuarter = _ds.Tables[0].Rows[0]["Quarter"].ToString();
                }

DataTable dtCopyQuarter = _dtmainQuartertable.Copy();

foreach (DataRow _rowData in _ds.Tables[0].Rows)
                {
                    Quarter = Convert.ToString(_rowData["Quarter"]).Trim();

_newRowQData = _dtmainQuartertable.NewRow();

dvQNew = dtCopyQuarter.DefaultView;
                    dvQNew.RowFilter = "Quarter ='" + Quarter.Trim() + "' and CampaignGroup ='" + Convert.ToString(_rowData["SourceCode"]).Trim() + "'";

if (PrevQuarter == Quarter)
                    {
                        if (dvQNew.ToTable().Rows.Count == 0)
                        {
                            _newRowQData["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);

foreach (DataColumn dcCol in _dtmainQuartertable.Columns)
                            {
                                String[] strArrColumnName = dcCol.ColumnName.Split('_');
                                if (strArrColumnName.Length > 0)
                                {
                                    if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["CountryID"]).Trim())
                                    {
                                        ColumnQName = strArrColumnName[0].ToString();
                                        ColumnQText = strArrColumnName[1].ToString();
                                        ColumnQName = ColumnQName + "_" + ColumnQText;
                                    }
                                }
                            }

//Filling Country Wise Opportunity
                            _newRowQData[ColumnQName] = Convert.ToString(_rowData["TotalOpportunity"]);
                            SnoQuarterCnt = SnoQuarterCnt + 1;
                            _newRowQData["SNo"] = SnoQuarterCnt;
                            _newRowQData["Quarter"] = Quarter;
                            _dtmainQuartertable.Rows.Add(_newRowQData);
                            _dtmainQuartertable.AcceptChanges();

_newRowQData = dtCopyQuarter.NewRow();
                            _newRowQData[ColumnQName] = Convert.ToString(_rowData["TotalOpportunity"]);
                            //SnoCnt = SnoCnt + 1;
                            _newRowQData["SNo"] = SnoQuarterCnt;
                            _newRowQData["Quarter"] = Quarter;
                            _newRowQData["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);
                            dtCopyQuarter.Rows.Add(_newRowQData);
                            dtCopyQuarter.AcceptChanges();
                        }
                        else
                        {
                            DataRow[] drTemp = _dtmainQuartertable.Select("Quarter ='" + Quarter.Trim() + "' and CampaignGroup='" + Convert.ToString(_rowData["SourceCode"]).Trim() + "'");
                            if (drTemp.Length > 0)
                            {
                                drTemp[0]["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);

foreach (DataColumn dcCol in _dtmainQuartertable.Columns)
                                {
                                    String[] strArrColumnName = dcCol.ColumnName.Split('_');
                                    if (strArrColumnName.Length > 0)
                                    {
                                        if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["CountryID"]).Trim())
                                        {
                                            ColumnQName = strArrColumnName[0].ToString();
                                            ColumnQText = strArrColumnName[1].ToString();
                                            ColumnQName = ColumnQName + "_" + ColumnQText;
                                        }
                                    }
                                }

//Filling Country Wise Opportunity
                                drTemp[0][ColumnQName] = Convert.ToString(_rowData["TotalOpportunity"]);
                                _dtmainQuartertable.AcceptChanges();
                            }
                        }
                        PrevQuarter = Quarter;
                    }
                    else
                    {
                        #region ...[Setting Totals of DataTable]...
                        bool isInner = false;
                        for (int i = 0; i < _dtmainQuartertable.Rows.Count; i++)
                        {
                            for (int j = 0; j < _dtmainQuartertable.Columns.Count; j++)
                            {
                                if (Convert.ToString(_dtmainQuartertable.Rows[i][j]).Trim().Length > 0 && j > 2 && Convert.ToString(_dtmainQuartertable.Rows[i][1]).Trim() == PrevQuarter.Trim())
                                {
                                    isInner = true;
                                    QCount = QCount + Convert.ToInt32(_dtmainQuartertable.Rows[i][j]);
                                }
                            }

if (isInner == true)
                            {
                                _dtmainQuartertable.Rows[i]["GrandTotal"] = QCount.ToString();
                                QCount = 0;
                                _dtmainQuartertable.AcceptChanges();
                                isInner = false;
                            }
                        }

//Add a Row To Calculate Total of a Quarter in a DataTable
                        DataRow _TRow = _dtmainQuartertable.NewRow();
                        _TRow["SNo"] = 0;
                        _TRow["Quarter"] = PrevQuarter + " Total";
                        _dtmainQuartertable.Rows.Add(_TRow);

QCount = 0;

for (int m = 0; m < _dtmainQuartertable.Columns.Count; m++)
                        {
                            if (m > 2)
                            {
                                for (int h = 0; h < _dtmainQuartertable.Rows.Count; h++)
                                {
                                    if (Convert.ToString(_dtmainQuartertable.Rows[h][m]).Trim().Length > 0 && Convert.ToString(_dtmainQuartertable.Rows[h][1]).Trim() == PrevQuarter.Trim())
                                    {
                                        QCount = QCount + Convert.ToInt32(_dtmainQuartertable.Rows[h][m]);
                                    }
                                }

_dtmainQuartertable.Rows[_dtmainQuartertable.Rows.Count - 1][m] = QCount.ToString();
                                QCount = 0;
                                _dtmainQuartertable.AcceptChanges();
                            }
                        }
                        #endregion

#region ...[When PrevQurater <> Qurater But We Have To Save Value]...
                        _newRowQData["CampaignGroup"] = Convert.ToString(_rowData["SourceCode"]);

foreach (DataColumn dcCol in _dtmainQuartertable.Columns)
                        {
                            String[] strArrColumnName = dcCol.ColumnName.Split('_');
                            if (strArrColumnName.Length > 0)
                            {
                                if (strArrColumnName[0].ToString().Trim() == Convert.ToString(_rowData["CountryID"]).Trim())
                                {
                                    ColumnQName = strArrColumnName[0].ToString();
                                    ColumnQText = strArrColumnName[1].ToString();
                                    ColumnQName = ColumnQName + "_" + ColumnQText;
                                }
                            }
                        }

//Filling Country Wise Opportunity
                        _newRowQData[ColumnQName] = Convert.ToString(_rowData["TotalOpportunity"]);
                        SnoQuarterCnt = SnoQuarterCnt + 1;
                        _newRowQData["SNo"] = SnoQuarterCnt;
                        _newRowQData["Quarter"] = Quarter;
                        _dtmainQuartertable.Rows.Add(_newRowQData);
                        _dtmainQuartertable.AcceptChanges();

PrevQuarter = Quarter;
                    }
                }

#region ...[Setting Totals of DataTable]...
                bool isInnerNew = false;
                for (int i = 0; i < _dtmainQuartertable.Rows.Count; i++)
                {
                    for (int j = 0; j < _dtmainQuartertable.Columns.Count; j++)
                    {
                        if (Convert.ToString(_dtmainQuartertable.Rows[i][j]).Trim().Length > 0 && j > 2 && Convert.ToString(_dtmainQuartertable.Rows[i][1]).Trim() == PrevQuarter.Trim())
                        {
                            isInnerNew = true;
                            QCount = QCount + Convert.ToInt32(_dtmainQuartertable.Rows[i][j]);
                        }
                    }

if (isInnerNew == true)
                    {
                        _dtmainQuartertable.Rows[i]["GrandTotal"] = QCount.ToString();
                        QCount = 0;
                        _dtmainQuartertable.AcceptChanges();
                        isInnerNew = false;
                    }
                }

DataRow _LastTRow = _dtmainQuartertable.NewRow();
                _LastTRow["SNo"] = 0;
                _LastTRow["Quarter"] = PrevQuarter + "Total";
                _dtmainQuartertable.Rows.Add(_LastTRow);
                PrevQuarter = Quarter;

QCount = 0;

for (int m = 0; m < _dtmainQuartertable.Columns.Count; m++)
                {
                    if (m > 2)
                    {
                        for (int h = 0; h < _dtmainQuartertable.Rows.Count; h++)
                        {
                            if (Convert.ToString(_dtmainQuartertable.Rows[h][m]).Trim().Length > 0 && Convert.ToString(_dtmainQuartertable.Rows[h][1]).Trim() == PrevQuarter.Trim())
                            {
                                QCount = QCount + Convert.ToInt32(_dtmainQuartertable.Rows[h][m]);
                            }
                        }

_dtmainQuartertable.Rows[_dtmainQuartertable.Rows.Count - 1][m] = QCount.ToString();
                        QCount = 0;
                        _dtmainQuartertable.AcceptChanges();
                    }
                }
                #endregion

//Add the Last Row Named GrandTotal in DataTable
                DataRow _LastQRow = _dtmainQuartertable.NewRow();
                _LastQRow["Sno"] = 0;
                _LastQRow["Quarter"] = "Grand Total";
                _dtmainQuartertable.Rows.Add(_LastQRow);

#endregion

#region ...[Fetching Grand Total For All Years and Quarters]...
                bool isGrandTotal = false;
                for (int m = 0; m < _dtmainQuartertable.Columns.Count; m++)
                {
                    if (m > 2)
                    {
                        for (int h = 0; h < _dtmainQuartertable.Rows.Count; h++)
                        {
                            if (Convert.ToString(_dtmainQuartertable.Rows[h][m]).Trim().Length > 0 && Convert.ToInt32(_dtmainQuartertable.Rows[h][0]) == 0)
                            {
                                QCount = QCount + Convert.ToInt32(_dtmainQuartertable.Rows[h][m]);
                                isGrandTotal = true;
                            }
                        }

if (isGrandTotal == true)
                        {
                            _dtmainQuartertable.Rows[_dtmainQuartertable.Rows.Count - 1][m] = QCount.ToString();
                            QCount = 0;
                            _dtmainQuartertable.AcceptChanges();
                            isGrandTotal = false;
                        }
                    }
                }
                #endregion

#region ...[Setting Totals of DataTable]...

for (int m = 0; m < _dtmainQuartertable.Columns.Count; m++)
                {
                    if (m > 2)
                    {
                        for (int h = 0; h < _dtmainQuartertable.Rows.Count; h++)
                        {
                            if (Convert.ToString(_dtmainQuartertable.Rows[h][m]).Trim().Length == 0)
                            {
                                _dtmainQuartertable.Rows[h][m] = "-";
                                _dtmainQuartertable.AcceptChanges();
                            }
                        }
                    }
                }

for (int m = 0; m < _dtmainQuartertable.Columns.Count; m++)
                {
                    if (m > 2)
                    {
                        string[] strCol = _dtmainQuartertable.Columns[m].ColumnName.Split('_');
                        if (strCol.Length > 1)
                        {
                            _dtmainQuartertable.Columns[m].ColumnName = strCol[1].ToString();
                        }
                    }
                }

#endregion

dtResult = _dtmainQuartertable;
                #endregion
            }
        }
        return dtResult;
    }

public DataTable Get_CommunicationSummary(String Email, Int64 ContactID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "R_CommunicationSummary";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@ContactID", Convert.ToInt64(ContactID));
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string Get_MinimumAccountID(String Email)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Get_MinimumAccountID", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.CommandTimeout = 240;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@Email", Email);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_Duplicate_Account_Removal(String StartDate, String EndDate)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "R_DuplicateAccountRemoval";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@StartDateTime", Convert.ToDateTime(StartDate));
        sql.Parameters.AddWithValue("@EndDateTime", Convert.ToDateTime(EndDate));
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_Email_From_Accounts(Int64 AccountID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_Email_From_Accounts";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.Add(new SqlParameter("@AccountID", SqlDbType.VarChar, 1000)).Value = AccountID;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_DuplicateAccountName(Int64 DANID, String Mode, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateAccountName";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@DANID", DANID);
        sql.Parameters.AddWithValue("@Mode", Mode);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_DuplicateAccountNameWithSearchID(String DANID, String Mode, String SearchID, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateAccountNameWithSearchID";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@DANID", DANID);
        sql.Parameters.AddWithValue("@Mode", Mode);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string Delete_DuplicateAccountName(Int64 DANID, String Value)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Delete_DuplicateAccountName", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@DANID", DANID);
        thiscommand.Parameters.AddWithValue("@Value", Value);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public string Delete_DuplicateAccountNameWithSearchID(String DAWSID, String SearchID, String Value)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Delete_DuplicateAccountNameWithSearchID", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@DAWSID", DAWSID);
        thiscommand.Parameters.AddWithValue("@SearchID", SearchID);
        thiscommand.Parameters.AddWithValue("@Value", Value);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_AccountNameBaseDuplicateAccounts(String AccountName, Int32 BillingCityID, Int32 CountryID, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_AccountNameBaseDuplicateAccounts";
        sql.CommandTimeout = 340;
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@AccountName", AccountName);
        sql.Parameters.AddWithValue("@BillingCityID", BillingCityID);
        sql.Parameters.AddWithValue("@BillingCountryID", CountryID);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_DuplicateAccountName_For_ContactMerge(Int64 DANID, String Mode)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateAccountName_For_ContactMerge";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.Add(new SqlParameter("@DANID", SqlDbType.VarChar, 1000)).Value = DANID;
        sql.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar, 1000)).Value = Mode;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_DuplicateAccountName_For_ContactMergeWithSearchID(String DAWSID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateAccountName_For_ContactMergeWithSearchID";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 340;
        // Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@DAWSID", DAWSID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string Transfer_DuplicateEmailContact_To_One_Account(string MasterAccountID, string AccountIDs,
                                                                     string Email, string LoginID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        string result = string.Empty;

// Setting Stored Procedure
        sql.CommandText = "Transfer_DuplicateEmailContact_To_One_Account";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@MasterAccountID", MasterAccountID);
        sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@LoginID", LoginID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        result = sql.ExecuteScalar().ToString();

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return result;
    }

public DataTable Contacts_Transfer_Account(Int64 MasterAccountID, String AllAccountIDS,
                                               String Email, Int32 BillingCountryID, Int32 BillingCityID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Contacts_Transfer_Account";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 360;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@MasterAccountID", MasterAccountID);
        sql.Parameters.AddWithValue("@AccountIDS", AllAccountIDS);
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@BillingCountryID", BillingCountryID);
        sql.Parameters.AddWithValue("@BillingCityID", BillingCityID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string InsertUpdateContactsTempData(string ContactXML, string ContactID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("InsertUpdateContactsTempData", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.CommandTimeout = 240;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.Add(new SqlParameter("@XMLContactsTempData", SqlDbType.NText)).Value = ContactXML;
        thiscommand.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.VarChar, 1000)).Value = ContactID;

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_Accounts_And_Contacts_Of_Email(string Email, string AccountID, string AccountIDs)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable ds;

// Setting Stored Procedure
        sql.CommandText = "Get_Accounts_And_Contacts_Of_Email";
        sql.CommandType = CommandType.StoredProcedure;

//// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@AccountID", AccountID);
        sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);

//// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

//// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataTable();
        sda.Fill(ds);

//// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds;
    }

public DataTable Get_DEID(string Email, string BillingCityID, string BillingCountryID)
    {

SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DEID";
        sql.CommandType = CommandType.StoredProcedure;

//// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@BillingCityID", BillingCityID);
        sql.Parameters.AddWithValue("@BillingCountryID", BillingCountryID);

//// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

//// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

//// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_DuplicateEmails(Int64 DEID, String Mode, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateEmails";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.AddWithValue("@DEID", DEID);
        sql.Parameters.AddWithValue("@Mode", Mode);
        sql.Parameters.AddWithValue("@DataSource", DataSource);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_DuplicateEmailsWithSearchID(String DEID, String Mode, String SearchID, String DataSource)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateEmailsWithSearchID";
        sql.CommandType = CommandType.StoredProcedure;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_EmailBaseDuplicateContacts(String Email, String AccountID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_EmailBaseDuplicateContacts";
        sql.CommandTimeout = 240;
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@AccountID", AccountID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable GetOpportunityDetail_Contact(string AccountID, string ContactID, string SalesStageID, string LoginID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        DataSet dsResult = new DataSet();
        SqlDataAdapter adapter;
        DataTable dtItem = new DataTable();

connection = OpenConnection();

thiscommand = new SqlCommand("GetOpportunityDetail_Contact", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.AddWithValue("@AccountID", AccountID);
        thiscommand.Parameters.AddWithValue("@ContactID", ContactID);
        thiscommand.Parameters.AddWithValue("@SalesStageID", SalesStageID);
        thiscommand.Parameters.AddWithValue("@LoginID", @LoginID);

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtItem);

connection.Close();
        connection = null;

return dtItem;
    }

public DataTable Get_Account_Summary(Int64 AccountID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_Account_Summary";
        sql.CommandType = CommandType.StoredProcedure;
        sql.CommandTimeout = 240;
        // Setting Connection
        sql.Connection = Conn;

// Setting Parameter
        sql.Parameters.AddWithValue("@AccountID", AccountID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public DataTable Get_Account_Contact_Summary(Int64 AccountID, string AccountIDS, string Email, string AccountName,
                                               Int32 CountryID, Int32 CityID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_Account_Contact_Summary";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.CommandTimeout = 240;

sql.Parameters.AddWithValue("@AccountID", AccountID);
        sql.Parameters.AddWithValue("@AccountIDS", AccountIDS);
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@AccountName", AccountName);
        sql.Parameters.AddWithValue("@CountryID", CountryID);
        sql.Parameters.AddWithValue("@CityID", CityID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_Cities(Int32 CountryID, String CityName, Int32 CityID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_Cities";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

sql.Parameters.AddWithValue("@CountryID", CountryID);
        sql.Parameters.AddWithValue("@CityName", CityName);
        sql.Parameters.AddWithValue("@CityID", CityID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public string Update_AccountTempData(string ColName, string ColValue, Int64 AccountID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Update_AccountTempData", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.Add(new SqlParameter("@ColName", SqlDbType.VarChar, 500)).Value = ColName;
        thiscommand.Parameters.Add(new SqlParameter("@ColValue", SqlDbType.NVarChar, 4000)).Value = ColValue;
        thiscommand.Parameters.Add(new SqlParameter("@AccountID", SqlDbType.VarChar, 1000)).Value = AccountID;

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_DuplicateEmails(Int64 DEID, String Mode)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_DuplicateEmails";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;
        sql.Parameters.Add(new SqlParameter("@DEID", SqlDbType.VarChar, 1000)).Value = DEID;
        sql.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar, 1000)).Value = Mode;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public string Delete_DuplicateEmail(Int64 DEID, String Value)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Delete_DuplicateEmail", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@DEID", DEID);
        thiscommand.Parameters.AddWithValue("@Value", Value);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public string Delete_DuplicateEmailWithSearchID(String DEWSID, String SearchID, String Value)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Delete_DuplicateEmailWithSearchID", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.AddWithValue("@DEWSID", DEWSID);
        thiscommand.Parameters.AddWithValue("@SearchID", SearchID);
        thiscommand.Parameters.AddWithValue("@Value", Value);

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public string InsertUpdateAccountsTempData(string AccountXML, string AccountId)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("InsertUpdateAccountsTempData", connection);

thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.CommandTimeout = 360;
        thiscommand.Parameters.Clear();
        thiscommand.Parameters.Add(new SqlParameter("@XMLAccountsTempData", SqlDbType.NText)).Value = AccountXML;
        thiscommand.Parameters.Add(new SqlParameter("@AccountID", SqlDbType.VarChar, 1000)).Value = AccountId;

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataTable Get_AllOpportunityEmails(string AccountIDs, string Email, string IsSystemEmailIncluded,
                                              string PageNumber, string PageRowCount, out int TotalRowCount)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

TotalRowCount = 0;

conn = OpenConnection();

sql = new SqlCommand("Get_OpportunityEmails_ForAllAccounts", conn);
        sql.CommandType = CommandType.StoredProcedure;

sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@IsSystemEmailIncluded", IsSystemEmailIncluded);
        sql.Parameters.AddWithValue("@PageNumber", PageNumber);
        sql.Parameters.AddWithValue("@PageRowCount", PageRowCount);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);

conn.Close();
        conn = null;

return dt;
    }

public DataTable Get_AllOpportunityNotes(string AccountIDs, string Email, string IsNextStepIncluded,
                                             string PageNumber, string PageRowCount, out int TotalRowCount)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

TotalRowCount = 0;

conn = OpenConnection();

sql = new SqlCommand("Get_OpportunityNotes_ForAllAccounts", conn);
        sql.CommandType = CommandType.StoredProcedure;

sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@IsNextStepIncluded", IsNextStepIncluded);
        sql.Parameters.AddWithValue("@PageNumber", PageNumber);
        sql.Parameters.AddWithValue("@PageRowCount", PageRowCount);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);

conn.Close();
        conn = null;

return dt;
    }

public DataTable Get_AllLeadEmails(string AccountIDs, string Email, string IsSystemEmailIncluded,
                                     string PageNumber, string PageRowCount, out int TotalRowCount)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

TotalRowCount = 0;

conn = OpenConnection();

sql = new SqlCommand("Get_LeadEmails_ForAllAccounts", conn);
        sql.CommandType = CommandType.StoredProcedure;

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);

conn.Close();
        conn = null;

return dt;
    }

public DataTable Get_AllLeadNotes(string AccountIDs, string Email, string IsNextStepIncluded,
                                     string PageNumber, string PageRowCount, out int TotalRowCount)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

TotalRowCount = 0;

conn = OpenConnection();

sql = new SqlCommand("Get_LeadNotes_ForAllAccounts", conn);
        sql.CommandType = CommandType.StoredProcedure;

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);

conn.Close();
        conn = null;

return dt;
    }

public DataTable Get_AllOpportunities(string AccountIDS, string SalesStageID, string LoginID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        DataSet dsResult = new DataSet();
        SqlDataAdapter adapter;
        DataTable dtItem = new DataTable();

connection = OpenConnection();

thiscommand = new SqlCommand("GetOpportunityDetail_ForAllAccounts", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;
        thiscommand.Parameters.AddWithValue("@AccountIDS", AccountIDS);
        thiscommand.Parameters.AddWithValue("@SalesStageID", SalesStageID);
        thiscommand.Parameters.AddWithValue("@LoginID", @LoginID);

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtItem);

connection.Close();
        connection = null;

return dtItem;
    }

public DataTable Get_AllLeads(string AccountIDS, string ContactID, string CampaignID,
                                           string LeadStatusID, string LoginID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        SqlDataAdapter adapter;
        DataTable dtItem = new DataTable();

connection = OpenConnection();

thiscommand = new SqlCommand("GetLeadDetail_ForAllAccounts", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.AddWithValue("@AccountIDS", AccountIDS);
        thiscommand.Parameters.AddWithValue("@ContactID", ContactID);
        thiscommand.Parameters.AddWithValue("@CampaignID", CampaignID);
        thiscommand.Parameters.AddWithValue("@LeadStatusID", LeadStatusID);
        thiscommand.Parameters.AddWithValue("@LoginID", LoginID);

thiscommand.CommandTimeout = 240;

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtItem);

connection.Close();
        connection = null;
        return dtItem;
    }

public DataTable Get_AllContacts(string AccountIDs, string Email,
                                     string PageNumber, string PageRowCount,
                                     out int TotalRowCount)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

TotalRowCount = 0;

conn = OpenConnection();

sql = new SqlCommand("GetContactDetail_ForAllAccounts", conn);
        sql.CommandType = CommandType.StoredProcedure;

sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@PageNumber", PageNumber);
        sql.Parameters.AddWithValue("@PageRowCount", PageRowCount);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

TotalRowCount = Convert.ToInt32(sql.Parameters["@TotalRowCount"].Value);

conn.Close();
        conn = null;

return dt;
    }

public DataTable Get_AllSubscriptions(string AccountIDS, string LoginID, string SearchID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        DataSet dsResult = new DataSet();
        SqlDataAdapter adapter;
        DataTable dtItem = new DataTable();
        connection = OpenConnection();

thiscommand = new SqlCommand("GetSubscriptionDetail_ForAllAccounts", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.AddWithValue("@AccountIDS", AccountIDS);
        thiscommand.Parameters.AddWithValue("@LoginID", LoginID);
        thiscommand.Parameters.AddWithValue("@SearchID", SearchID);

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtItem);
        connection.Close();
        connection = null;
        return dtItem;
    }

public DataTable Get_AccountSummaryCount(string AccountIDs)
    {
        SqlConnection conn = null;
        SqlCommand sql;

SqlDataAdapter adapter;
        DataTable dt;

conn = OpenConnection();

sql = new SqlCommand("Get_AccountSummaryCount", conn);
        sql.CommandType = CommandType.StoredProcedure;

sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);

adapter = new SqlDataAdapter(sql);
        dt = new DataTable();
        adapter.Fill(dt);

conn.Close();
        conn = null;

return dt;
    }

public DataTable Get_EmailBaseDuplicateAccounts(String Email, Int32 BillingCityID, Int32 CountryID, String DataSource,
                                                    String AccountIDs)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_EmailBaseDuplicateAccounts";
        sql.CommandTimeout = 420;
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@Email", Email);
        sql.Parameters.AddWithValue("@BillingCityID", BillingCityID);
        sql.Parameters.AddWithValue("@BillingCountryID", CountryID);
        sql.Parameters.AddWithValue("@DataSource", DataSource);
        sql.Parameters.AddWithValue("@AccountIDs", AccountIDs);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public string InsertUpdateDefaultView(Int32 OwnerID,
                                     string CookieName,
                                     string CookieValue)
    {
        string returnValue;

SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();

// Setting Stored Procedure
        sql.CommandText = "InsertUpdateDefaultView";
        sql.CommandType = CommandType.StoredProcedure;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@OwnerID", OwnerID);
        sql.Parameters.AddWithValue("@CookieName", CookieName);
        sql.Parameters.AddWithValue("@CookieValue", CookieValue);

// Setting Connection
        sql.Connection = Conn;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        returnValue = sql.ExecuteScalar().ToString();

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return returnValue;
    }

public DataTable Get_DefaultView(Int32 OwnerID,
                                     string CookieName)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_DefaultView";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@OwnerID", OwnerID);
        sql.Parameters.AddWithValue("@CookieName", CookieName);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_AccountsIDS(string SearchID,
                                     string LoginID,
                                     string PageNo,
                                     string PageRowCount,
                                     string AccountSortColNo,
                                     string AccountSortAZ,
                                     out Int64 TotalRowCount)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataTable dt;

// Setting Stored Procedure
        sql.CommandText = "Get_AccountsIDS";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        TotalRowCount = 0;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@LoginID", LoginID);
        sql.Parameters.AddWithValue("@PageNumber", PageNo);
        sql.Parameters.AddWithValue("@PageRowCount", PageRowCount);
        sql.Parameters.AddWithValue("@AccountSortingCol", AccountSortColNo);
        sql.Parameters.AddWithValue("@AccountSort", AccountSortAZ);
        sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        dt = new DataTable();
        sda.Fill(dt);

TotalRowCount = Convert.ToInt64(sql.Parameters["@TotalRowCount"].Value);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return dt;
    }

public DataTable Get_ContactsForUnMergedAccount(Int64 AccountID, Int32 LoginID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_ContactsForUnMergedAccount";
        sql.CommandType = CommandType.StoredProcedure;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@AccountID", AccountID);

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds.Tables[0];
    }

public void CRMMergeRequest()
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Get_CRMMergeRequest", connection);

thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Clear();

thiscommand.ExecuteNonQuery();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;
    }

public void RemoveMergedAccount(int MergedAccountId, int AccountId)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        connection = OpenConnection();

thiscommand = new SqlCommand("Get_RemoveMergedAccount", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Add(new SqlParameter("@MergedAccountID", SqlDbType.Int));
        thiscommand.Parameters["@MergedAccountID"].Value = MergedAccountId;
        thiscommand.Parameters.Add(new SqlParameter("@AccountID", SqlDbType.Int));
        thiscommand.Parameters["@AccountID"].Value = AccountId;

thiscommand.ExecuteNonQuery();
        connection.Close();
        connection = null;
    }

public DataTable GET_OpportunityStrategy()
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        sql.Parameters.Clear();
        sql.CommandText = "GET_OpportunityStrategy";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Connection = Conn;
        // Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        SqlDataAdapter sda = new SqlDataAdapter(sql);
        DataTable dt = new DataTable();
        sda.Fill(dt);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;
        return dt;
    }

public DataTable R_OpportunityLifeCycleWithNotes(string OwnerID, string OwnerGroupID,
                                                     string CampaignID, string CampaignGroupID,
                                                     string AccountListID,
                                                     string FromDate, string ToDate,
                                                     string SourceType, string MessageType,
                                                     string IncludeNextStep, string IncludeEDM,
                                                     string IncludeNoContactNote,
                                                     string SalesStageID, string LoginID)
    {
        SqlConnection connection = OpenConnection();
        SqlCommand thiscommand;
        DataTable dtResult = new DataTable();
        SqlDataAdapter adapter;

thiscommand = new SqlCommand("R_OpportunityLifeCycleWithNotes", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.AddWithValue("@OwnerID", OwnerID);
        thiscommand.Parameters.AddWithValue("@OwnerGroupID", OwnerGroupID);
        thiscommand.Parameters.AddWithValue("@CampaignID", CampaignID);
        thiscommand.Parameters.AddWithValue("@CampaignGroupID", CampaignGroupID);
        thiscommand.Parameters.AddWithValue("@AccountListID", AccountListID);
        thiscommand.Parameters.AddWithValue("@FromDate", FromDate);
        thiscommand.Parameters.AddWithValue("@ToDate", ToDate);
        thiscommand.Parameters.AddWithValue("@SourceType", SourceType);
        thiscommand.Parameters.AddWithValue("@MessageType", MessageType);
        thiscommand.Parameters.AddWithValue("@IncludeNextStep", IncludeNextStep);
        thiscommand.Parameters.AddWithValue("@IncludeEDM", IncludeEDM);
        thiscommand.Parameters.AddWithValue("@IncludeNoContactNote", IncludeNoContactNote);
        thiscommand.Parameters.AddWithValue("@SalesStageID", SalesStageID);
        thiscommand.Parameters.AddWithValue("@LoginID", LoginID);

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtResult);
        connection.Close();
        return dtResult;
    }

public DataTable Get_MergeAccount(Int32 LoginID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        SqlDataAdapter adapter;
        DataTable dtItem = new DataTable();
        connection = OpenConnection();

thiscommand = new SqlCommand("Get_MergeAccount", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Add(new SqlParameter("@LoginID", SqlDbType.Int));
        thiscommand.Parameters["@LoginID"].Value = LoginID;

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dtItem);
        connection.Close();
        return dtItem;
    }

public DataSet Get_ContactsForMergedAccount(Int64 AccountID, out Int32 TotalDuplicateCount)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;
        DataSet ds;

// Setting Stored Procedure
        sql.CommandText = "Get_ContactsForMergedAccount";
        sql.CommandType = CommandType.StoredProcedure;

@TotalDuplicateCount = 0;

// Setting Connection
        sql.Connection = Conn;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@AccountID", AccountID);

//Output Parameter
        sql.Parameters.AddWithValue("@TotalDuplicateCount", TotalDuplicateCount);
        sql.Parameters["@TotalDuplicateCount"].Direction = ParameterDirection.Output;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sda = new SqlDataAdapter(sql);
        ds = new DataSet();
        sda.Fill(ds);

//Fetching Output Parameter
        TotalDuplicateCount = Convert.ToInt32(sql.Parameters["@TotalDuplicateCount"].Value);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;

return ds;
    }

public DataSet Get_ContactsForDataCompare(string MergeID, string ContactIDs,
                                             int Counter, string ParentContact)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        SqlDataAdapter adapter;
        DataSet dsData = new DataSet();
        DataTable dtResult = new DataTable();
        DataSet dsResult = new DataSet();

connection = OpenConnection();

thiscommand = new SqlCommand("Get_ContactsForDataCompare", connection);
        thiscommand.CommandTimeout = 240;

thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Clear();

thiscommand.Parameters.AddWithValue("@MergeID", MergeID);
        thiscommand.Parameters.AddWithValue("@ContactIDs", ContactIDs);
        thiscommand.Parameters.AddWithValue("@ParentContactId", ParentContact);

adapter = new SqlDataAdapter(thiscommand);
        adapter.Fill(dsData);

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

dtResult = dsData.Tables[0];
        DataTable dtContactInfo = dtResult.Copy();

string MergeResolve = "";
        if (dsData.Tables[1].Rows.Count > 0)
        {
            MergeResolve = dsData.Tables[1].Rows[0]["MergeResolve"].ToString();
        }

DataTable dtContact = new DataTable();
        dtContact.Columns.Add("Contact Fields");
        for (int i = 1; i <= Counter; i++)
        {
            dtContact.Columns.Add("Contact " + i.ToString());
        }

dtContact.Columns.Add("IsAnyChange");
        dtContact.Columns.Add("IsAllBlank");
        dtContact.Columns.Add("Resolved");

DataRow drContact;
        for (int count = 0; count < (dtResult.Columns.Count); count++)
        {
            DataColumn dcResult = dtResult.Columns[count];
            drContact = dtContact.NewRow();
            drContact["Contact Fields"] = dcResult.ColumnName.ToString();
            dtContact.Rows.Add(drContact);
        }

int j = 0;
        bool IsAnyChange = false;
        bool IsAllBlank = false;
        string MergeValue = "";
        for (int count = 0; count < dtContact.Rows.Count; count++)
        {
            DataRow ContactRow = dtContact.Rows[count];
            IsAnyChange = false;
            IsAllBlank = true;
            string ParentValue = "";
            string strValue = "";
            MergeValue = "1";
            for (int i = 0; i <= dtResult.Rows.Count - 1; i++)
            {
                ContactRow["Contact " + Convert.ToInt32(i + 1).ToString()] = dtResult.Rows[i][j].ToString().Trim() != "0" ? dtResult.Rows[i][j].ToString().Trim() : "";
                strValue = ContactRow["Contact " + Convert.ToInt32(i + 1).ToString()].ToString();
                if (i == 0)
                {
                    ParentValue = strValue;
                }
                if (!IsAnyChange && i > 0 && strValue.ToLower() != ParentValue.ToLower())
                {
                    IsAnyChange = true;
                }
                if (IsAllBlank && i > 0 && strValue.Length > 0)
                {
                    IsAllBlank = false;
                }
            }
            if (MergeResolve.Length > 0)
            {
                MergeValue = MergeResolve.Substring(count, 1);
            }
            ContactRow["IsAnyChange"] = IsAnyChange.ToString();
            ContactRow["IsAllBlank"] = IsAllBlank.ToString();
            ContactRow["Resolved"] = MergeValue.ToString();
            j += 1;
        }

dsData.Tables.Add(dtContact);
        return dsData;
    }

public void RemoveMergedContact(int MergedContactId, int ContactId)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;
        connection = OpenConnection();

thiscommand = new SqlCommand("Get_RemoveMergedContact", connection);
        thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Add(new SqlParameter("@MergedContactID", SqlDbType.Int));
        thiscommand.Parameters["@MergedContactID"].Value = MergedContactId;
        thiscommand.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int));
        thiscommand.Parameters["@ContactID"].Value = ContactId;

thiscommand.ExecuteNonQuery();
        connection.Close();
        connection = null;
    }

public void RejectContactMerge(string MasterContactID)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        SqlDataAdapter sda;

// Setting Stored Procedure
        sql.CommandText = "RejectContactMerge";
        sql.CommandType = CommandType.StoredProcedure;

// Setting SP Parameter
        sql.Parameters.AddWithValue("@MasterContactID", MasterContactID);

// Setting Connection
        sql.Connection = Conn;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        sql.ExecuteNonQuery();

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();
        Conn = null;
    }

public string MergeContacts(string MergeContactID)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("MergeContacts", connection);
        thiscommand.CommandTimeout = 240;

thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Clear();
        thiscommand.Parameters.Add(new SqlParameter("@MergeContactID", SqlDbType.Int)).Value = MergeContactID;

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public string Merge_Save_Contacts(string ContactXML, string MergeContactIds)
    {
        SqlConnection connection = null;
        SqlCommand thiscommand;

connection = OpenConnection();

thiscommand = new SqlCommand("Merge_Save_Contacts", connection);

thiscommand.CommandType = CommandType.StoredProcedure;

thiscommand.Parameters.Clear();
        thiscommand.Parameters.Add(new SqlParameter("@XMLMergeContacts", SqlDbType.NText)).Value = ContactXML;
        thiscommand.Parameters.Add(new SqlParameter("@MergeContactID", SqlDbType.VarChar, 1000)).Value = MergeContactIds;

string result = thiscommand.ExecuteScalar().ToString();

// Connection Close
        if (connection.State == ConnectionState.Open)
            connection.Close();
        connection = null;

return result;
    }

public DataSet Get_AccountsSubscription(string AccountIDS,
                                               string SearchID,
                                               string SqlSyntax,
                                               string LoginID,
                                               string PageNo,
                                               string PageRowCount,
                                               string SortColNo,
                                               string SortAZ,
                                               string SearchText,
                                               Int16 IsCriteria,
                                               string AccountSortColNo,
                                               string AccountSortAZ,
                                               out Int64 TotalRowCount,
                                               out string SearchName,
                                               out Int64 TotalExcludedAccounts)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        sql.Parameters.Clear();
        sql.CommandText = "Get_AccountsSubscription";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Connection = Conn;

// Setting SP Parameter
        SearchName = "";
        TotalRowCount = 0;
        TotalExcludedAccounts = 0;

sql.Parameters.AddWithValue("@AccountIDS", AccountIDS);
        sql.Parameters.AddWithValue("@SearchID", SearchID);
        sql.Parameters.AddWithValue("@LoginID", LoginID);
        sql.Parameters.AddWithValue("@PageNumber", PageNo);
        sql.Parameters.AddWithValue("@PageRowCount", PageRowCount);
        sql.Parameters.AddWithValue("@SorthingCol", SortColNo);
        sql.Parameters.AddWithValue("@Sort", SortAZ);
        sql.Parameters.AddWithValue("@IsCriteria", IsCriteria);
        sql.Parameters.AddWithValue("@AccountSortingCol", AccountSortColNo);
        sql.Parameters.AddWithValue("@AccountSort", AccountSortAZ);

if (SqlSyntax.Trim().Length > 0)
            sql.Parameters.AddWithValue("@SQLText", SqlSyntax);
        else
            sql.Parameters.AddWithValue("@SQLText", DBNull.Value);

if (SearchText.Trim().Length > 0)
            sql.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 300)).Value = SearchText;
        else
            sql.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 300)).Value = DBNull.Value;

sql.Parameters.AddWithValue("@TotalRowCount", TotalRowCount);
        sql.Parameters["@TotalRowCount"].Direction = ParameterDirection.Output;

sql.Parameters.Add("@SearchName", SqlDbType.NVarChar, 500);
        sql.Parameters["@SearchName"].Direction = ParameterDirection.Output;

sql.Parameters.Add("@TotalExcludedAccounts", @TotalExcludedAccounts);
        sql.Parameters["@TotalExcludedAccounts"].Direction = ParameterDirection.Output;

// Connection Open
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();

// Executing Procedure
        SqlDataAdapter sda = new SqlDataAdapter(sql);
        DataSet ds = new DataSet();
        sda.Fill(ds);

//Filling Out Parameter
        SearchName = Convert.ToString(sql.Parameters["@SearchName"].Value);
        TotalRowCount = Convert.ToInt64(sql.Parameters["@TotalRowCount"].Value);
        TotalExcludedAccounts = Convert.ToInt64(sql.Parameters["@TotalExcludedAccounts"].Value);

// Connection Close
        if (Conn.State == ConnectionState.Open)
            Conn.Close();

Conn = null;

return ds;
    }

public DataSet Get_AccountsLeads(string AccountIDS,
                                       string SearchID,
                                       string SqlSyntax,
                                       string LoginID,
                                       string PageNo,
                                       string PageRowCount,
                                       string SortColNo,
                                       string SortAZ,
                                       string SearchText,
                                       Int16 IsCriteria,
                                       string AccountSortColNo,
                                       string AccountSortAZ,
                                       out Int64 TotalRowCount,
                                       out string SearchName,
                                       out Int64 TotalExcludedAccounts)
    {
        SqlConnection Conn = OpenConnection();
        SqlCommand sql = new SqlCommand();
        sql.Parameters.Clear();
        sql.CommandText = "Get_AccountsLeads";
        sql.CommandType = CommandType.StoredProcedure;
        sql.Connection = Conn;