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;
}
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);
}