Exemplo n.º 1
0
        public static DataTable SelectTicketsByFilter(UserAuth usr, Config cfg, ColumnsSetting colset, Filter filter, QueryFilter qfilter, bool reqSortFilter, int bussinesDayLength)
        {
            string _query = "SELECT 0 as DaysOld, tkt.id as TicketId, tkt.Status, tkt.technician_id, tkt.user_id, tkt.TicketNumber, tkt.TicketNumberPrefix, ISNULL(tkt.TicketNumberPrefix,'')+CAST(tkt.TicketNumber AS nvarchar(10)) AS TicketNumberFull, tkt.CreateTime, tkt.ClosedTime";
            _query += ", tkt.newtechpost, tkt.newuserpost, tkt.dtFollowUp, tkt.subject, tkt.NextStep, tkt.NextStepDate, tkt.SchedTicketID";
            if (!qfilter.IsPrintMode) _query += ", ISNULL((SELECT TOP 1 1 FROM Mfs_File fsf WHERE fsf.OrganizationId='" + usr.InstanceID.ToString() + "' AND fsf.DepartmentId='" + usr.InstanceID.ToString() + "' AND fsf.LocalObjectId=CAST(tkt.id AS nvarchar(255))), 0) as files_count";
            else _query += ", dbo.fxSelectInitialPost(tkt.company_id,tkt.id) AS InitPost";
            if (cfg.ResolutionTracking) _query += ", ISNULL(tkt.btResolved, 0) btResolved, CASE ISNULL(tkt.btResolved, 0) WHEN 1 THEN 'Resolved' ELSE 'UnResolved' END+CASE WHEN NOT rc.vchName IS NULL AND LEN(rc.vchName)>0 THEN ' - '+rc.vchName ELSE '' END AS Resolution";
            if (cfg.ResolutionTracking && cfg.ConfirmationTracking) _query += ", CASE WHEN ISNULL(tkt.btResolved, 0)=1 THEN tkt.btConfirmed ELSE CAST (1 AS bit) END AS btConfirmed";
            else if (cfg.ConfirmationTracking) _query += ", tkt.btConfirmed";
            for (int i = 0; i < colset.BrowseColumnsCount; i++)
            {
                if (colset.GetColSQLName(colset.GetBrowseColumn(i)).Length == 0) continue;
                else if (colset.GetBrowseColumn(i) == BrowseColumn.TicketNumber) continue;
                switch (colset.GetBrowseColumn(i))
                {
                    case BrowseColumn.Account:
                        if (!cfg.AccountManager) continue;
                        break;
                    case BrowseColumn.Location:
                        if (!cfg.LocationTracking) continue;
                        break;
                    case BrowseColumn.Class:
                        if (!cfg.ClassTracking) continue;
                        break;
                    case BrowseColumn.Level:
                        if (!cfg.TktLevels) continue;
                        break;
                    case BrowseColumn.Priority:
                        if (!cfg.PrioritiesGeneral) continue;
                        break;
                    case BrowseColumn.SupportGroup:
                        if (!cfg.SupportGroups) continue;
                        break;
                    case BrowseColumn.Project:
                        if (!cfg.ProjectTracking || !cfg.EnableTicketToProjectRelation) continue;
                        break;
                    case BrowseColumn.Time:
                        if (!cfg.TimeTracking) continue;
                        break;
                }
                if (colset.GetBrowseColumn(i) == BrowseColumn.Location) _query += ", dbo.fxGetUserLocationName(" + usr.lngDId.ToString() + ", tkt.LocationId) AS " + colset.GetColSQLAlias(colset.GetBrowseColumn(i));
                else if (colset.GetBrowseColumn(i) == BrowseColumn.Account) _query += ", ISNULL(" + colset.GetColSQLName(colset.GetBrowseColumn(i)) + ",  CASE WHEN ISNULL(tkt.btNoAccount, 0) = 0 THEN co.company_name + ' (Internal)' ELSE '' END) + CASE WHEN tkt.AccountLocationId IS NULL THEN '' ELSE '<br class=brVisible><span class=subTitle>' + dbo.fxGetUserLocationName(" + usr.lngDId.ToString() + ", tkt.AccountLocationId) + '</span>' END AS " + colset.GetColSQLAlias(colset.GetBrowseColumn(i));
                else if (colset.GetBrowseColumn(i) == BrowseColumn.Class) _query += ", dbo.fxGetFullClassName(" + usr.lngDId.ToString() + ", tkt.class_id) AS " + colset.GetColSQLAlias(colset.GetBrowseColumn(i));
                else if (colset.GetBrowseColumn(i) == BrowseColumn.Time) _query += ", tkt.TotalHours, tkt.RemainingHours, tkt.EstimatedTime";
                else if (colset.GetBrowseColumn(i) == BrowseColumn.SLAResponse) _query += ", " + colset.GetColSQLName(colset.GetBrowseColumn(i)) + " AS " + colset.GetColSQLAlias(colset.GetBrowseColumn(i)) + ", tkt.Created_id, tkt.btInitResponse";
                else _query += ", " + colset.GetColSQLName(colset.GetBrowseColumn(i)) + " AS " + colset.GetColSQLAlias(colset.GetBrowseColumn(i));
            }
            if (qfilter.TicketId != 0 && qfilter.TicketRelation != RelatedTickets.TicketRelationType.None)
            {
                _query += " FROM RelatedTickets RT INNER JOIN tbl_ticket tkt ON tkt.company_id=" + usr.lngDId.ToString() + " AND tkt.Id=RT.RelatedTicketId";
            }
            else _query += " FROM tbl_ticket tkt";
            _query += " INNER JOIN tbl_LoginCompanyJunc tlj1 ON tlj1.company_id=" + usr.lngDId.ToString() + " AND tlj1.id=tkt.user_id INNER JOIN tbl_Logins lgu ON lgu.id=tlj1.login_id";
            _query += " INNER JOIN tbl_LoginCompanyJunc tlj2 ON tlj2.company_id=" + usr.lngDId.ToString() + " AND tlj2.id=tkt.technician_id INNER JOIN tbl_Logins lgt ON lgt.id=tlj2.login_id";
            _query += " INNER JOIN tbl_company co ON co.company_id=tkt.company_id";
            if (cfg.ResolutionTracking) _query += " LEFT OUTER JOIN ResolutionCats rc ON rc.DId=" + usr.lngDId.ToString() + " AND rc.Id=tkt.ResolutionCatsId";
            if (cfg.AccountManager) _query += " LEFT OUTER JOIN Accounts acct ON acct.DId=" + usr.lngDId.ToString() + " AND acct.id=tkt.intAcctId";
            if (cfg.PrioritiesGeneral) _query += " LEFT OUTER JOIN Priorities pri ON pri.DId=" + usr.lngDId.ToString() + " AND pri.id=tkt.PriorityId";
            if (cfg.SupportGroups) _query += " LEFT OUTER JOIN SupportGroups sg ON sg.DId=" + usr.lngDId.ToString() + " AND sg.Id=tlj2.SupGroupId";
            if (cfg.ProjectTracking && cfg.EnableTicketToProjectRelation) _query += " LEFT OUTER JOIN Project prj ON prj.CompanyID=" + usr.lngDId.ToString() + " AND prj.ProjectID=tkt.ProjectID";

            if (qfilter.Sort == SortMode.MyTicketsAsAlternateTech)
                _query += " JOIN TicketAssignment AS TA ON TA.DepartmentId = tkt.company_id AND TA.TicketId = tkt.Id AND TA.UserId = " + usr.lngUId.ToString() + " AND TA.AssignmentType = " + ((int)Ticket.TicketAssignmentType.Technician).ToString() + " AND IsPrimary = 0 AND StopDate IS NULL";
            else
                _query += " LEFT OUTER JOIN TicketAssignment AS TA ON TA.DepartmentId = tkt.company_id AND TA.TicketId = tkt.Id AND TA.UserId = " + usr.lngUId.ToString() + " AND TA.AssignmentType = " + ((int)Ticket.TicketAssignmentType.User).ToString() + " AND TA.IsPrimary = 0 AND TA.StopDate IS NULL";

            if (qfilter.SQLJoin.Length > 0) _query += Security.SQLInjectionBlock(qfilter.SQLJoin);

            if (qfilter.TicketId != 0 && qfilter.TicketRelation != RelatedTickets.TicketRelationType.None)
            {
                _query += " WHERE RT.DId=" + usr.lngDId.ToString() + " AND RT.TicketId=" + qfilter.TicketId.ToString() + " AND RelationType=" + ((int)qfilter.TicketRelation).ToString();
            }
            else _query += " WHERE tkt.company_id=" + usr.lngDId.ToString();

            if (qfilter.SQLWhere.Length > 0)
            {
                _query += Security.SQLInjectionBlock(qfilter.SQLWhere);
            }
            if (qfilter.SQLWhere.Length == 0 || reqSortFilter)
            {
                switch (qfilter.Sort)
                {
                    case SortMode.MyTickets:
                        if (usr.IsInRole(UserAuth.UserRole.StandardUser, UserAuth.UserRole.SuperUser)) _query += " AND (tkt.user_id=" + usr.lngUId.ToString() + " OR TA.UserId=" + usr.lngUId.ToString() + ")";
                        else _query += " AND tkt.technician_id=" + usr.lngUId;
                        break;
                    case SortMode.MyTicketsAsUser:
                        _query += " AND tkt.user_id=" + usr.lngUId.ToString();
                        break;
                    case SortMode.MyTicketsAsUserAndTech:
                        if (usr.IsInRole(UserAuth.UserRole.StandardUser, UserAuth.UserRole.SuperUser))
                        {
                            _query += " AND (tkt.user_id=" + usr.lngUId.ToString() + " OR tkt.technician_id=" +
                                      usr.lngUId.ToString() + " OR TA.UserId=" + usr.lngUId.ToString() + ")";
                        }
                        else
                        {
                            _query += " AND (tkt.user_id=" + usr.lngUId.ToString() + " OR tkt.technician_id=" +
                                      usr.lngUId.ToString() + ")";
                        }
                        break;
                    case SortMode.TechTickets:
                        _query += " AND tkt.technician_id = " + qfilter.TechnicianId.ToString();
                        break;
                    case SortMode.MyTicketsAsAlternateTech:
                        _query += " AND TA.UserId = " + usr.lngUId.ToString() + " AND TA.UserId <> tkt.technician_id";
                        break;
                    case SortMode.TicketsAsUserNotTech:
                        if (usr.IsInRole(UserAuth.UserRole.StandardUser, UserAuth.UserRole.SuperUser))
                        {
                            _query += "  AND (tkt.user_id=" + qfilter.UserId.ToString() + " OR TA.UserId=" + usr.lngUId.ToString()
                                + ") AND tkt.technician_id<>" + qfilter.UserId.ToString();
                        }
                        else
                        {
                            _query += "  AND tkt.user_id=" + qfilter.UserId.ToString() + " AND tkt.technician_id<>" + qfilter.UserId.ToString();
                        }
                        break;
                    case SortMode.SupportGroupTickets:
                        _query += " AND tlj2.SupGroupID=(SELECT SupGroupId FROM tbl_LoginCompanyJunc WHERE company_id=" + usr.lngDId.ToString() + " AND id=" + (qfilter.UserId != 0 ? qfilter.UserId.ToString() : usr.lngUId.ToString()) + ")";
                        break;
                    default:
                        if (qfilter.Sort == SortMode.SuperUserTickets || qfilter.Sort == SortMode.TicketsAsSuperUserNotTech)
                        {
                            string accQuery = " (tkt.intAcctId IN (SELECT AccountId FROM UserAccounts WHERE DepartmentId="
                                + usr.lngDId.ToString() + " AND UserId=" +
                                usr.lngUId.ToString() + ") OR (tkt.intAcctId IS NULL AND ((SELECT COUNT(*) FROM UserAccounts WHERE DepartmentId="
                                + usr.lngDId.ToString() + " AND UserId=" + usr.lngUId.ToString() + " AND AccountId IS NULL) > 0)))";
                            string locQuery = " tkt.LocationId IN (SELECT Id FROM dbo.fxGetAllChildLocationsFromList("
                                + usr.lngDId.ToString() + ", '" + usr.strGSUserRootLocationId + "'))";
                            switch (usr.sintGSUserType)
                            {
                                case 1://Account
                                    _query += " AND" + accQuery;
                                    break;
                                case 2://Loccation
                                    _query += " AND" + locQuery;
                                    break;
                                case 3://Account and Location
                                    _query += " AND (" + accQuery + " OR" + locQuery + ")";
                                    break;
                            }
                            if (qfilter.Sort == SortMode.TicketsAsSuperUserNotTech) _query += "  AND tkt.user_id=" + qfilter.UserId.ToString() + " AND tkt.technician_id<>" + qfilter.UserId.ToString();
                        }
                        else
                        {
                            if (usr.IsInRole(UserAuth.UserRole.StandardUser, UserAuth.UserRole.SuperUser)) _query += " AND tkt.user_id=" + usr.lngUId.ToString();
                        }
                        if (qfilter.AccountId != 0)
                        {
                            if (qfilter.AccountId > 0)
                                _query += " AND tkt.intAcctId=" + qfilter.AccountId.ToString();
                            else
                            {
                                _query += " AND tkt.intAcctId IS NULL AND ISNULL(tkt.btNoAccount, 0)=" + (qfilter.AccountId == -2 ? "1" : "0");
                            }
                            if (qfilter.AccountUserId != 0) _query += " AND tkt.user_id=" + qfilter.AccountUserId.ToString();
                            if (qfilter.AccountLocationId != 0) _query += " AND tkt.AccountLocationId IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + usr.lngDId.ToString() + ", " + qfilter.AccountLocationId.ToString() + "))";
                        }
                        else
                            if (qfilter.AccountLocationId != 0) // Location's tickets
                                _query += " AND tkt.intAcctId IS NULL AND tkt.AccountLocationId = " + qfilter.AccountLocationId.ToString();

                        if (qfilter.LocationId > 0)
                            _query += " AND tkt.LocationId = " + qfilter.LocationId.ToString();

                        if (qfilter.ProjectID > 0)
                        {
                            _query += " AND tkt.ProjectID=" + qfilter.ProjectID.ToString();
                        }
                        break;
                }
            }

            if (qfilter.ShowNewMessages == NewMessagesMode.UserAndTech) _query += " AND ((tkt.NewUserPost=1 AND tkt.technician_id =" + usr.lngUId.ToString() + " AND tkt.user_id<>" + usr.lngUId.ToString() + ") OR (tkt.NewTechPost=1 AND  tkt.user_id=" + usr.lngUId.ToString() + " AND technician_id<>" + usr.lngUId.ToString() + "))";
            else if (qfilter.ShowNewMessages == NewMessagesMode.User) _query += " AND tkt.newuserpost=1";
            else if (qfilter.ShowNewMessages == NewMessagesMode.Technician) _query += " AND tkt.newtechpost=1";

            if (cfg.Folders && qfilter.FolderId != 0) _query += " AND tkt.folder_id=" + qfilter.FolderId.ToString();

            if (qfilter.ShowFollowUpTicketsOnly) _query += " AND tkt.dtFollowUp IS NOT NULL";

            switch (qfilter.TicketStatus)
            {
                case TicketStatusMode.AllOpen:
                    _query += "  AND tkt.status<>'Closed'";
                    break;
                case TicketStatusMode.Close:
                    _query += " AND tkt.status='Closed'";
                    break;
                case TicketStatusMode.Open:
                    _query += " AND tkt.status='Open'";
                    break;
                case TicketStatusMode.OnHold:
                    _query += " AND tkt.status='On Hold'";
                    break;
                case TicketStatusMode.PartsOnOrder:
                    _query += " AND tkt.status='Parts On Order'";
                    break;
                case TicketStatusMode.OpenClosed:
                    _query += " AND (tkt.status='Open' OR tkt.status='Closed')";
                    break;
            }

            if (filter != null && filter.IsEnabled && !qfilter.IsUseSql && !qfilter.IgnoreFilter && (qfilter.Sort == SortMode.NotSet || qfilter.Sort == SortMode.MyTickets || qfilter.Sort == SortMode.MyTicketsAsUser) && qfilter.AccountId == 0 && qfilter.AccountLocationId == 0 && qfilter.AccountUserId == 0 && qfilter.FolderId == 0 && qfilter.TechnicianId == 0 && qfilter.UserId == 0) //begin filter
            {
                if (filter.Statuses.Length > 0) _query += " AND tkt.status IN (" + filter.Statuses + ")";
                if (cfg.LocationTracking && filter.Locations.Length > 0) _query += " AND tkt.LocationId" + (!filter.IsLocationsInclude ? " NOT" : string.Empty) + " IN (SELECT Id FROM dbo.fxGetAllChildLocationsFromList(" + usr.lngDId.ToString() + ",'" + filter.Locations + "'))";
                if (cfg.ClassTracking && filter.Classes.Length > 0) _query += " AND tkt.class_id" + (!filter.IsClasseInclude ? " NOT" : string.Empty) + " IN (SELECT Id FROM dbo.fxGetAllChildClassesFromList(" + usr.lngDId.ToString() + ",'" + filter.Classes + "'))";
                if (cfg.TktLevels && filter.Levels.Length > 0) _query += " AND tkt.tintLevel IN (" + filter.Levels + ")";
                if (cfg.PrioritiesGeneral && filter.Priority.Length > 0) _query += " AND pri.tintpriority" + filter.Priority;
                if (cfg.AccountManager && filter.Accounts.Length > 0) _query += " AND ISNULL(tkt.intAcctId,-1)" + (!filter.IsAccountsInclude ? " NOT" : string.Empty) + " IN (" + filter.Accounts + ")";
                if (cfg.Folders)
                {
                    if (filter.Folders == Filter.FilterType.TicketsAssignedToItems) _query += " AND tkt.folder_id IS NOT NULL";
                    else if (filter.Folders == Filter.FilterType.TicketsNOTAsignedToItems) _query += " AND tkt.folder_id IS NULL";
                }
                if (cfg.TimeTracking)
                {
                    if (filter.Projects == Filter.FilterType.TicketsAssignedToItems) _query += " AND tkt.ProjectID IS NOT NULL";
                    else if (filter.Projects == Filter.FilterType.TicketsNOTAsignedToItems) _query += " AND tkt.ProjectID IS NULL";
                }
            }

            //use global filters
            if (qfilter.Sort == SortMode.NotSet || qfilter.Sort == SortMode.TechTickets) _query += GlobalFilters.GlobalFiltersSqlWhere(usr, cfg, "tkt.", "tlj2.", "SupGroupID");

            if (qfilter.SortColumnIndex < 0)
            {
                string _order = string.Empty;
                for (int i = 0; i < colset.SortColumnsCount; i++)
                {
                    if (colset.GetColSQLName(colset.GetSortColumn(i)).Length == 0) continue;

                    switch (colset.GetSortColumn(i))
                    {
                        case BrowseColumn.Account:
                            if (!cfg.AccountManager) continue;
                            break;
                        case BrowseColumn.Location:
                            if (!cfg.LocationTracking) continue;
                            break;
                        case BrowseColumn.Class:
                            if (!cfg.ClassTracking) continue;
                            break;
                        case BrowseColumn.Level:
                            if (!cfg.TktLevels) continue;
                            break;
                        case BrowseColumn.Priority:
                            if (!cfg.PrioritiesGeneral) continue;
                            break;
                        case BrowseColumn.SupportGroup:
                            if (!cfg.SupportGroups) continue;
                            break;
                        case BrowseColumn.Project:
                            if (!cfg.ProjectTracking || !cfg.EnableTicketToProjectRelation) continue;
                            break;
                    }
                    if (_order.IndexOf(colset.GetColSQLName(colset.GetSortColumn(i))) < 0)
                    {
                        if (colset.GetSortColumn(i) == BrowseColumn.Location) _order += " dbo.fxGetUserLocationName(" + usr.lngDId.ToString() + ", tkt.LocationId)";
                        else if (colset.GetColSQLAlias(colset.GetSortColumn(i)) == "TicketNumberFull") _order += " tkt.TicketNumber";
                        else _order += " " + colset.GetColSQLName(colset.GetSortColumn(i));
                        if (colset.GetSortOrderDesc(i)) _order += " DESC,";
                        else _order += " ASC,";
                    }
                }
                if (colset.SortColumnsCount > 0 && _order.Length > 0)
                {
                    _order = _order.Substring(0, _order.Length - 1);
                    _query += " ORDER BY" + _order;
                }
            }
            else
            {
                _query += " ORDER BY ";
                switch (qfilter.SortColumnSQLAlias)
                {
                    case "LocationName":
                        _query += "dbo.fxGetUserLocationName(" + usr.lngDId.ToString() + ", tkt.LocationId)";
                        break;
                    case "TicketNumberFull":
                        _query += "tkt.TicketNumber";
                        break;
                    case "CreateTime":
                        _query += "tkt.CreateTime";
                        break;
                    default:
                        _query += qfilter.SortColumnSQLAlias;
                        break;
                }
                _query += qfilter.IsSortColumnDesc ? " DESC" : " ASC";
            }

            DataTable dt = SelectByQuery(_query);

            if (usr != null && dt != null && string.Compare(qfilter.SortColumnSQLAlias, "DaysOld", true) == 0)
            {
                Data.Tickets.TicketTimer m_TicketTimer = (Data.Tickets.TicketTimer)usr.tintTicketTimer;
                dt.Columns.Add(new DataColumn("DaysOldSort", typeof(long)));
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["Status"].ToString() == "Closed")
                    {
                        if (m_TicketTimer == Data.Tickets.TicketTimer.SLATimer)
                            dr["DaysOldSort"] = Data.Tickets.SelectTicketSLATime(usr.OrgID, usr.lngDId, Functions.DB2UserDateTime((DateTime)dr["CreateTime"]), Functions.DB2UserDateTime((DateTime)dr["ClosedTime"]));
                        else
                            dr["DaysOldSort"] = ((DateTime)dr["ClosedTime"] - (DateTime)dr["CreateTime"]).TotalMinutes;
                    }
                    else
                    {
                        if (m_TicketTimer == Data.Tickets.TicketTimer.SLATimer)
                            dr["DaysOldSort"] = Data.Tickets.SelectTicketSLATime(usr.lngDId, Functions.DB2UserDateTime((DateTime)dr["CreateTime"]), Functions.DB2UserDateTime(DateTime.UtcNow));
                        else
                            dr["DaysOldSort"] = (DateTime.UtcNow - (DateTime)dr["CreateTime"]).TotalMinutes;
                    }
                }

                DataView dv = dt.DefaultView;
                dv.Sort = "DaysOldSort" + (qfilter.IsSortColumnDesc ? " DESC" : " ASC");
                dt = dv.ToTable();
            }

            return dt;
        }
Exemplo n.º 2
0
        public static DataTable SelectByFilter(UserAuth userAuth, ColumnsSetting colset, Filter filter, bool addNoAccountRow)
        {
            string _query = "SELECT 0 AS ArtificialSortingField, a.id AS Id, a.AcctRepId";
            System.Collections.Generic.List<string> columnAliases = new System.Collections.Generic.List<string>();
            for (int i = 0; i < colset.BrowseColumnsCount; i++)
            {
                string columnAlias = colset.GetColSQLAlias(colset.GetBrowseColumn(i));
                if (colset.GetBrowseColumn(i) == BrowseColumn.DeptLocation)
                {
                    _query += ", dbo.fxGetUserLocationName(" + userAuth.lngDId.ToString() + ", a.LocationId) AS " + columnAlias;
                }
                else if (colset.GetBrowseColumn(i) == BrowseColumn.BWDAccRef)
                {
                    _query += ", " + colset.GetColSQLName(colset.GetBrowseColumn(i)) + " AS " + columnAlias;
                    _query += ", " + colset.GetColSQLName(Data.Accounts.BrowseColumn.Ref1Number) + " AS " + colset.GetColSQLAlias(Data.Accounts.BrowseColumn.Ref1Number);
                    _query += ", " + colset.GetColSQLName(Data.Accounts.BrowseColumn.Ref2Number) + " AS " + colset.GetColSQLAlias(Data.Accounts.BrowseColumn.Ref2Number);
                    columnAliases.Add(colset.GetColSQLAlias(Data.Accounts.BrowseColumn.Ref1Number));
                    columnAliases.Add(colset.GetColSQLAlias(Data.Accounts.BrowseColumn.Ref2Number));
                }
                else
                {
                    _query += ", " + colset.GetColSQLName(colset.GetBrowseColumn(i)) + " AS " + columnAlias;
                }

                columnAliases.Add(columnAlias);
            }
            _query += " FROM Accounts a";
            _query += " LEFT OUTER JOIN SupportGroups sp ON sp.DId=a.DId AND a.SupGroupId=sp.Id";
            _query += " LEFT OUTER JOIN tbl_LoginCompanyJunc tlj ON tlj.company_id=a.DId AND tlj.id=a.AcctRepId JOIN tbl_Logins lg ON lg.id=tlj.login_id";
            _query += " LEFT OUTER JOIN Locations al ON al.DId=a.DId AND al.Id=a.LocationId";
            _query += " LEFT OUTER JOIN LocationTypes t on t.DId=a.DId and t.Id=al.LocationTypeId and t.Name='Building'  ";
            _query += " LEFT OUTER JOIN (SELECT t.intAcctId AS AccountId, COUNT(*) AS OpenTickets FROM tbl_ticket t WHERE t.company_id=" + userAuth.lngDId.ToString() + " AND t.Status<>'Closed' GROUP BY t.intAcctId) at ON at.AccountId=a.Id";
            _query += " WHERE a.DId=" + userAuth.lngDId;
            if (filter.AccActive != ActiveStatus.NoFilter) _query += " AND a.btActive=" + ((int)filter.AccActive).ToString();
            if (colset.ListViewMode == ViewMode.MyAccounts) _query += " AND a.AcctRepId=" + filter.UserId.ToString();
            else if (colset.ListViewMode == ViewMode.SupportGroupAccounts) _query += " AND a.SupGroupId=" + filter.SupportGroupId.ToString();
            if (filter.AccName.Length > 0)
            {
                string _fstr = Security.SQLInjectionBlock(Functions.SqlStr(filter.AccName)).Trim('\'');
                _query += " AND (a.vchName LIKE '%" + _fstr + "%'";
                if (filter.FilterLocation)
                {
                    _query += " OR (dbo.fxGetUserLocationName(" + userAuth.lngDId.ToString() + ", a.LocationId) LIKE '%" + _fstr + "%')";
                }
                _query += " OR (";
                _query += " CAST(a.intBWDAcctNum as nvarchar) LIKE '%" + _fstr + "%' OR a.vchRef1Num LIKE '%" + _fstr + "%' OR a.vchRef2Num LIKE '%" + _fstr + "%' OR a.vchAcctNum LIKE '%" + _fstr + "%'))";
            }

            string internalAccountName = userAuth.strGDName + " (Internal)";
            if (colset.ListViewMode == ViewMode.AllAccounts && filter.AccActive != ActiveStatus.Inactive && (string.IsNullOrEmpty(filter.AccName) || internalAccountName.ToLower().Contains(filter.AccName.ToLower())))
            {
                string union = " UNION SELECT 1 AS ArtificialSortingField, -1 AS Id, -1 AS AcctRepId";
                foreach (string columnAlias in columnAliases)
                {
                    union += ", ";
                    switch (columnAlias)
                    {
                        case "AccName":
                            union += Functions.SqlStr(internalAccountName);
                            break;
                        case "OpenTickets":
                            union += "(SELECT COUNT(*) AS OpenTickets FROM tbl_ticket t WHERE t.company_id=" + userAuth.lngDId.ToString() + " AND t.Status<>'Closed' AND t.intAcctId IS NULL AND ISNULL(t.btNoAccount, 0) = 0)";
                            break;
                        default:
                            union += "NULL";
                            break;
                    }
                    union += " AS " + columnAlias;
                }
                _query += union;
            }
            if (addNoAccountRow)
            {
                string union = " UNION SELECT -1 AS ArtificialSortingField, -2 AS Id, -2 AS AcctRepId";
                foreach (string columnAlias in columnAliases)
                {
                    union += ", ";
                    switch (columnAlias)
                    {
                        case "AccName":
                            union += Functions.SqlStr("(No Account)");
                            break;
                        case "OpenTickets":
                            union += "(SELECT COUNT(*) AS OpenTickets FROM tbl_ticket t WHERE t.company_id=" + userAuth.lngDId.ToString() + " AND t.Status<>'Closed' AND t.intAcctId IS NULL AND t.btNoAccount = 1)";
                            break;
                        default:
                            union += "NULL";
                            break;
                    }
                    union += " AS " + columnAlias;
                }
                _query += union;
            }

            string _order = " ORDER BY ArtificialSortingField DESC,";
            for (int i = 0; i < colset.SortColumnsCount; i++)
            {
                if (!columnAliases.Contains(colset.GetColSQLAlias(colset.GetSortColumn(i)))) continue;
                if (_order.IndexOf(colset.GetColSQLName(colset.GetSortColumn(i))) < 0)
                {
                    if (colset.GetBrowseColumn(i) == BrowseColumn.DeptLocation)
                    {
                        _order += " " + colset.GetColSQLAlias(colset.GetSortColumn(i));
                    }
                    else
                    {
                        _order += " " + colset.GetColSQLName(colset.GetSortColumn(i));
                    }
                    if (colset.GetSortOrderDesc(i)) _order += " DESC,";
                    else _order += " ASC,";
                }
            }
            _order = _order.Substring(0, _order.Length - 1);
            _query += _order;
            return SelectByQuery(_query);
        }