private TreeListColumn[] CreateColumns(ColumnsSetting current)
{
List <ColumnParameter> currentColumns = columnSettings[current];
TreeListColumn[] retVal = new TreeListColumn[currentColumns.Count];
int counter = 0;
foreach (ColumnParameter parameter in currentColumns)
{
switch (parameter.FieldName)
{
case Column_Name:
retVal[counter] = GetNameColumn();
break;
case Column_Description:
retVal[counter] = GetDescriptionColumn();
break;
case Column_Value:
retVal[counter] = GetValueColumn();
break;
case Column_IsUsed:
retVal[counter] = GetIsUsedColumn();
break;
default:
break;
}
retVal[counter].VisibleIndex = parameter.VisibleIndex;
counter++;
}
return(retVal);
}
public static string GetAssetsByFilterSQLQuery(Guid organizationId, int departmentId, ref Filter filter, ref ColumnsSetting colsetting, bool isRealDates, bool isForExport, string customSort, string customGroupSort, int? UserId, Config cfg, bool? Active)
{
string sqlQuery = "";
string _sql = string.Empty;
string _static_order = string.Empty;
string _having = string.Empty;
string _groupby = string.Empty;
int _custom_property_count = 0;
string _select_list = string.Empty;
AssetsConfig assetsConfig;
if (filter != null && !string.IsNullOrEmpty(filter.SerialNumber))
assetsConfig = Companies.SelectAssetUniqueCaptions(organizationId, departmentId);
else
assetsConfig = new AssetsConfig();
bool accountManager = true;
if (cfg != null && !cfg.AccountManager)
{
accountManager = false;
}
string default_sort = String.Empty;
if (!isForExport)
{
if (filter == null) _sql += "SELECT TOP 0 ";
else _sql += "SELECT ";
_select_list = "Assets.Id, ";
_select_list += " Assets.CategoryId as AssetCategoryId, AssetCategories.Name as AssetCategoryName, ";
_select_list += " Assets.TypeId as AssetTypeId, AssetTypes.Name as AssetTypeName, ";
_select_list += " Assets.MakeId as AssetMakeId, AssetMakes.Make as AssetMakeName, ";
_select_list += " Assets.ModelId as AssetModelId, AssetModels.Model as AssetModelName, ";
_select_list += " Assets.AccountId as AccountId, ";
if (accountManager)
{
_select_list += " ISNULL(Accounts.vchName,'(Internal)') AccountName, ";
}
_select_list += " Assets.AssetGUID, Assets.SerialNumber, Assets.Unique1, Assets.Unique2, Assets.Unique3, Assets.Unique4, Assets.Unique5, Assets.Unique6, Assets.Unique7, CASE WHEN LEN(Assets.SerialNumber)>0 THEN Assets.SerialNumber END AS AssetIdentifyCode, ";
if (colsetting != null)
{
string _sql_custom = string.Empty;
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column1, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column2, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column3, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column4, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column5, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column6, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column7, true, isRealDates);
_sql_custom += GetColumnName(departmentId, ColumnTypeInfo.Table, colsetting.Column8, true, isRealDates);
_select_list += _sql_custom;
}
_select_list += " dbo.fxGetUserName(lo_checkout.FirstName, lo_checkout.LastName, lo_checkout.Email) as CheckoutName, AssetStatus.vchStatus as StatusName, Assets.dtUpdated AS UpdatedDate, dbo.fxGetUserName2(lo_updated.FirstName, lo_updated.LastName, lo_updated.Email) AS UpdatedByName";
_static_order = " ORDER BY ";
if (String.IsNullOrEmpty(customGroupSort))
{
bool FirstOrder = true;
string[] DefGroupSort = new string[] { "AssetCategoryName", "AssetTypeName", "AssetMakeName", "AssetModelName" };
foreach (string s in DefGroupSort)
{
if (!customSort.Contains(s))
{
default_sort += (FirstOrder ? "" : ",") + s;
FirstOrder = false;
}
}
}
}
else
{
_sql += "SELECT ";
_select_list += " 0 as GroupId, 0 as AssetId, (CASE WHEN EXISTS(select TOP 1 * from AssetTypeProperties where AssetTypeProperties.DId=Assets.DepartmentId and AssetTypeProperties.AssetTypeId=Assets.TypeId) THEN 1 ELSE 0 END) AS IsHaveCustomPropertiesId, Assets.DepartmentId, Assets.Id, ";
_select_list += " Assets.CategoryId as AssetCategoryId, AssetCategories.Name as AssetCategoryName, ";
_select_list += " Assets.TypeId as AssetTypeId, AssetTypes.Name as AssetTypeName, ";
_select_list += " Assets.MakeId as AssetMakeId, AssetMakes.Make as AssetMakeName, ";
_select_list += " Assets.ModelId as AssetModelId, AssetModels.Model as AssetModelName, ";
_select_list += " Assets.AssetGUID, Assets.SerialNumber, Assets.Unique1, Assets.Unique2, Assets.Unique3, Assets.Unique4, Assets.Unique5, Assets.Unique6, Assets.Unique7, Assets.StatusID as StatusId, AssetStatus.vchStatus as StatusName, ";
_select_list += " Assets.Name as AssetName, Assets.Description as AssetDescription";
_select_list += ", Assets.AccountId as AccountId";
if (accountManager)
{
_select_list += ", ISNULL(Accounts.vchName,'(Internal)') AccountName";
}
_select_list += ", Assets.LocationId as LocationId, dbo.fxGetUserLocationName(" + departmentId.ToString() + ", Assets.LocationId) as LocationName";
_select_list += ", Assets.OwnerId as OwnerId, lo_owner.FirstName as OwnerFirstName, lo_owner.LastName as OwnerLastName, lo_owner.EMail as OwnerMail, Assets.CheckedOutId as CheckedOutId, lo_checkout.FirstName as CheckedOutFirstName, lo_checkout.LastName as CheckedOutLastName, lo_checkout.EMail as CheckoutMail, Assets.VendorID as VendorId, Vendors.Name as VendorName, Assets.WarrantyVendor as WarrantyVendorId, WarrantyVendors.Name as WarrantyVendorName, Assets.PONumber, Assets.DateAquired, Assets.DateDeployed, Assets.DateDisposed, Assets.DateEntered, Assets.DateOutOfService, Assets.DatePurchased, Assets.DateReceived, Assets.LaborWarrantyLength, Assets.PartsWarrantyLength, Assets.Value, Assets.ValueCurrent, Assets.ValueDepreciated, Assets.ValueReplacement, Assets.ValueSalvage, Assets.DisposalCost, Assets.AssetSort, Assets.FundingCode, Assets.FundingSource, CONVERT(nvarchar(4000), Assets.Notes) as Notes, Assets.dtUpdated AS UpdatedDate, dbo.fxGetUserName2(lo_updated.FirstName, lo_updated.LastName, lo_updated.Email) AS UpdatedByName ";
_static_order = " ORDER BY IsHaveCustomPropertiesId asc, AssetCategories.Name asc, AssetTypes.Name asc, AssetMakes.Make asc, AssetModels.Model asc ";
};
if (colsetting != null)
{
string _dynamic_order = "";
string _sort_1 = GetColumnName(departmentId, ColumnTypeInfo.Field, colsetting.SortColumn1, false, isRealDates);
string _sort_2 = GetColumnName(departmentId, ColumnTypeInfo.Field, colsetting.SortColumn2, false, isRealDates);
string _sort_3 = GetColumnName(departmentId, ColumnTypeInfo.Field, colsetting.SortColumn3, false, isRealDates);
if (_sort_1.Length > 0 && (String.IsNullOrEmpty(customGroupSort) || _sort_1.ToLower() != customGroupSort.ToLower()))
{
if (colsetting.SortColumnDesc1)
_dynamic_order = _dynamic_order + _sort_1 + " DESC";
else
_dynamic_order = _dynamic_order + _sort_1 + " ASC";
}
if (_sort_2.Length > 0 && (String.IsNullOrEmpty(customGroupSort) || _sort_2.ToLower() != customGroupSort.ToLower()))
{
if (_dynamic_order.Length > 0)
_dynamic_order += ",";
if (colsetting.SortColumnDesc2)
_dynamic_order = _dynamic_order + _sort_2 + " DESC";
else
_dynamic_order = _dynamic_order + _sort_2 + " ASC";
}
if (_sort_3.Length > 0 && (String.IsNullOrEmpty(customGroupSort) || _sort_3.ToLower() != customGroupSort.ToLower()))
{
if (_dynamic_order.Length > 0)
_dynamic_order += ",";
if (colsetting.SortColumnDesc3)
_dynamic_order = _dynamic_order + _sort_3 + " DESC";
else
_dynamic_order = _dynamic_order + _sort_3 + " ASC";
}
if (!isForExport)
{
if (!String.IsNullOrEmpty(customGroupSort))
_static_order += customGroupSort;
if (!String.IsNullOrEmpty(customSort) && (String.IsNullOrEmpty(customGroupSort) || customGroupSort.ToLower() != customSort.ToLower()))
_static_order += (!String.IsNullOrEmpty(customGroupSort) ? ", " : String.Empty) + customSort;
else if (!String.IsNullOrEmpty(_dynamic_order))
_static_order += (!String.IsNullOrEmpty(customGroupSort) ? ", " : String.Empty) + _dynamic_order;
else if (!String.IsNullOrEmpty(default_sort))
_static_order += (!String.IsNullOrEmpty(customGroupSort) ? ", " : String.Empty) + default_sort;
}
}
_sql += _select_list;
_sql += " FROM Assets ";
_sql = _sql + " INNER JOIN AssetCategories ON AssetCategories.DepartmentId=" + departmentId.ToString() + " and Assets.CategoryId = AssetCategories.Id ";
_sql = _sql + " INNER JOIN AssetTypes ON AssetTypes.DepartmentId=" + departmentId.ToString() + " and Assets.TypeId = AssetTypes.Id ";
_sql = _sql + " INNER JOIN AssetMakes ON AssetMakes.DepartmentId=" + departmentId.ToString() + " and Assets.MakeId = AssetMakes.Id ";
_sql = _sql + " INNER JOIN AssetModels ON AssetModels.DepartmentId=" + departmentId.ToString() + " and Assets.ModelId = AssetModels.Id ";
_sql = _sql + " INNER JOIN AssetStatus ON (AssetStatus.DId is NULL OR AssetStatus.DId=" + departmentId.ToString() + ") and Assets.StatusId = AssetStatus.Id ";//now statuses use for all departments
_sql = _sql + " LEFT OUTER JOIN Accounts ON Accounts.DId=" + departmentId.ToString() + " and Assets.AccountId = Accounts.Id ";
_sql = _sql + " LEFT OUTER JOIN Locations ON Locations.DId=" + departmentId.ToString() + " and Assets.LocationId = Locations.Id ";
_sql = _sql + " LEFT OUTER JOIN tbl_vendors Vendors ON Vendors.company_id=" + departmentId.ToString() + " and Assets.VendorId = Vendors.Id ";
_sql = _sql + " LEFT OUTER JOIN tbl_vendors WarrantyVendors ON WarrantyVendors.company_id=" + departmentId.ToString() + " and Assets.WarrantyVendor = WarrantyVendors.Id ";
_sql = _sql + " LEFT OUTER JOIN tbl_LoginCompanyJunc tlj_owner ON tlj_owner.company_id=" + departmentId.ToString() + " and Assets.OwnerId=tlj_owner.id LEFT OUTER JOIN tbl_Logins lo_owner ON lo_owner.id=tlj_owner.login_id ";
_sql = _sql + " LEFT OUTER JOIN tbl_LoginCompanyJunc tlj_checkout ON tlj_checkout.company_id=" + departmentId.ToString() + " and Assets.CheckedOutId=tlj_checkout.id LEFT OUTER JOIN tbl_Logins lo_checkout ON lo_checkout.id=tlj_checkout.login_id ";
_sql = _sql + " LEFT OUTER JOIN tbl_LoginCompanyJunc tlj_updated ON tlj_updated.company_id=" + departmentId.ToString() + " and Assets.intUpdatedBy=tlj_updated.id LEFT OUTER JOIN tbl_Logins lo_updated ON lo_updated.id=tlj_updated.login_id ";
_sql = _sql + " LEFT OUTER JOIN AssetStatusCompany ON AssetStatusCompany.DId=" + departmentId.ToString() + " and AssetStatusCompany.AssetStatusID=Assets.StatusId ";
if (filter == null)
{
string _DefWhere = " WHERE Assets.StatusId<>17 AND Assets.DepartmentId=" + departmentId.ToString();
if (Active == true) _DefWhere += " AND (AssetStatusCompany.NonActive=0 OR (AssetStatusCompany.NonActive IS NULL)) ";
else if (Active == false) _DefWhere += " AND AssetStatusCompany.NonActive=1 ";
return _sql + _DefWhere + _static_order;
}
string DeptCriteria = "Assets.DepartmentId = " + departmentId.ToString();
string AssetStatusCriteria = String.Empty;
if (filter.StatusID > 0)
AssetStatusCriteria = " AND Assets.StatusID=" + filter.StatusID.ToString();
else if (filter.StatusID == 0)
{
AssetStatusCriteria = " AND Assets.StatusId<>17";
if (Active == true)
AssetStatusCriteria += " AND (AssetStatusCompany.NonActive=0 OR (AssetStatusCompany.NonActive IS NULL)) ";
else if (Active == false)
AssetStatusCriteria += " AND AssetStatusCompany.NonActive=1 ";
}
else if (filter.StatusID == -1)
AssetStatusCriteria = " AND Assets.StatusId<>17"; // All except deleted
else if (filter.StatusID == -2)
AssetStatusCriteria = String.Empty; // All + deleted
else if (filter.StatusID == -3)
AssetStatusCriteria = " AND Assets.StatusId<>17 AND (AssetStatusCompany.NonActive=0 OR (AssetStatusCompany.NonActive IS NULL))"; // All Active statuses
else if (filter.StatusID == -4)
AssetStatusCriteria = " AND Assets.StatusId<>17 AND AssetStatusCompany.NonActive=1 ";
string _where = " WHERE " + DeptCriteria + AssetStatusCriteria;
if (filter.CategoryID != 0) _where += " AND Assets.CategoryID=" + filter.CategoryID.ToString();
if (filter.TypeID != 0) _where += " AND Assets.TypeID=" + filter.TypeID.ToString();
if (filter.MakeID != 0) _where += " AND Assets.MakeID=" + filter.MakeID.ToString();
if (filter.ModelID != 0) _where += " AND Assets.ModelID=" + filter.ModelID.ToString();
//Check Global Filters
if (UserId != null && cfg != null)
{
GlobalFilters _gf = new GlobalFilters(organizationId, departmentId, (int)UserId);
string _gfAccWhere = string.Empty;
if (cfg.AccountManager && _gf.IsFilterEnabled(GlobalFilters.FilterState.EnabledGlobalFilters) && _gf.IsFilterEnabled(GlobalFilters.FilterType.Accounts))
{
string _strAcc = GlobalFilters.SelectFilterByTypeToString(organizationId, departmentId, (int)UserId, GlobalFilters.FilterType.Accounts);
if (_strAcc.Length > 0)
_gfAccWhere = " ISNULL(Assets.AccountId, -1) IN (" + _strAcc + ")";
}
string _gfLocWhere = string.Empty;
if (cfg.LocationTracking && _gf.IsFilterEnabled(GlobalFilters.FilterState.EnabledGlobalFilters) && _gf.IsFilterEnabled(GlobalFilters.FilterType.Locations))
{
string _strLoc = GlobalFilters.SelectFilterByTypeToString(organizationId, departmentId, (int)UserId, GlobalFilters.FilterType.Locations);
if (_strLoc.Length > 0)
_gfLocWhere = " Assets.LocationID IN (SELECT Id FROM dbo.fxGetAllChildLocationsFromList(" + departmentId.ToString() + ", '" + _strLoc + "'))";
else
_gfLocWhere = " Assets.LocationID IS NULL";
}
if (!String.IsNullOrEmpty(_gfAccWhere))
_where += " AND " + _gfAccWhere;
if (!String.IsNullOrEmpty(_gfLocWhere))
_where += " AND " + _gfLocWhere;
}
if (filter.AccountID != 0)
{
if ((filter.AccountID == -1) || (filter.AccountID == int.MinValue))
_where += " AND Assets.AccountId IS NULL";
else
_where += " AND Assets.AccountId = " + filter.AccountID.ToString();
}
if (!string.IsNullOrEmpty(filter.CustomWhere)) _where += " AND (" + filter.CustomWhere + ")";
int CurLocationID = filter.LocationID;
if (CurLocationID > 0) _where += " AND Assets.LocationID IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + departmentId.ToString() + ", " + CurLocationID.ToString() + "))";
else if (CurLocationID == int.MinValue) _where += " AND Assets.LocationID IS NULL";
else if (CurLocationID < 0)
{
CurLocationID = (-1) * CurLocationID;
_where += " AND ((Assets.LocationID IN (SELECT Id FROM dbo.fxGetAllChildLocations(" + departmentId.ToString() + ", " + CurLocationID.ToString() + "))) OR Assets.LocationID IS NULL)";
}
if (filter.OwnerID > 0) _where += " AND Assets.OwnerID=" + filter.OwnerID.ToString();
else if (filter.OwnerID == int.MinValue) _where += " AND Assets.OwnerID IS NULL";
if (filter.CheckedOutID > 0) _where += " AND Assets.CheckedOutID=" + filter.CheckedOutID.ToString();
else if (filter.CheckedOutID == int.MinValue) _where += " AND Assets.CheckedOutID IS NULL";
if (filter.VendorID > 0) _where += " AND Assets.VendorID=" + filter.VendorID.ToString();
else if ((filter.VendorID == int.MinValue) || (filter.VendorID == -1)) _where += " AND Assets.VendorID IS NULL";
if (filter.WarrantyVendorID > 0) _where += " AND Assets.WarrantyVendor=" + filter.WarrantyVendorID.ToString();
else if ((filter.WarrantyVendorID == int.MinValue) || (filter.WarrantyVendorID == -1)) _where += " AND Assets.WarrantyVendor IS NULL";
// #1 Serial Number
string CurValue = filter.SerialNumber;
if (CurValue.Length > 0)
{
_where += " AND ( " + GetWildcardSearch("Assets.SerialNumber", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique1Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique1", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique2Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique2", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique3Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique3", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique4Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique4", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique5Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique5", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique6Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique6", CurValue, false);
if (!string.IsNullOrEmpty(assetsConfig.Unique7Caption))
_where += " OR " + GetWildcardSearch("Assets.Unique7", CurValue, false);
_where += " )";
}
// #3 Asset Name
CurValue = filter.AssetName;
if (CurValue.Length > 0)
{
_where += " AND " + GetWildcardSearch("Assets.Name", CurValue, true);
}
// #4 PO Number
CurValue = filter.PONumber;
if (CurValue.Length > 0)
{
_where += " AND " + GetWildcardSearch("Assets.PONumber", CurValue, true);
}
// #5 Funding Code
CurValue = filter.FundingCode;
if (CurValue.Length > 0)
{
_where += " AND " + GetWildcardSearch("Assets.FundingCode", CurValue, true);
}
if (filter.AssetDateStart != DateTime.MinValue && filter.AssetDateEnd != DateTime.MinValue)
{
if (filter.CheckDateAquired) _where += " AND (Assets.DateAquired BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDateDeployed) _where += " AND (Assets.DateDeployed BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDateDisposed) _where += " AND (Assets.DateDisposed BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDateEntered) _where += " AND (Assets.DateEntered BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDateOutOfService) _where += " AND (Assets.DateOutOfService BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDatePurchased) _where += " AND (Assets.DatePurchased BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDateReceived) _where += " AND (Assets.DateReceived BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
if (filter.CheckDateUpdated) _where += " AND (Assets.dtUpdated BETWEEN '" + Functions.FormatSQLDateTime(filter.AssetDateStart) + "' AND '" + Functions.FormatSQLDateTime(filter.AssetDateEnd) + "') ";
}
//#1
CurValue = filter.ValueCurrentExpression;
if (CurValue.Length > 0)
{
if (CurValue.Contains("NULL"))
{
if (CurValue == "NULL") _where += " AND Assets.ValueCurrent IS NULL";
else
{
CurValue = CurValue.Substring(4, CurValue.Length - 4);
_where += " AND (Assets.ValueCurrent IS NULL OR Assets.ValueCurrent" + Security.SQLInjectionBlock(CurValue.Replace("'", "''")) + ")";
}
}
else
{
_where += " AND Assets.ValueCurrent" + Security.SQLInjectionBlock(CurValue.Replace("'", "''"));
}
}
//#2
CurValue = filter.ValueDepreciatedExpression;
if (CurValue.Length > 0)
{
if (CurValue.Contains("NULL"))
{
if (CurValue == "NULL") _where += " AND Assets.ValueDepreciated IS NULL";
else
{
CurValue = CurValue.Substring(4, CurValue.Length - 4);
_where += " AND (Assets.ValueDepreciated IS NULL OR Assets.ValueDepreciated" + Security.SQLInjectionBlock(CurValue.Replace("'", "''")) + ")";
}
}
else
{
_where += " AND Assets.ValueDepreciated" + Security.SQLInjectionBlock(CurValue.Replace("'", "''"));
}
}
//#3
CurValue = filter.ValueReplacementExpression;
if (CurValue.Length > 0)
{
if (CurValue.Contains("NULL"))
{
if (CurValue == "NULL") _where += " AND Assets.ValueReplacement IS NULL";
else
{
CurValue = CurValue.Substring(4, CurValue.Length - 4);
_where += " AND (Assets.ValueReplacement IS NULL OR Assets.ValueReplacement" + Security.SQLInjectionBlock(CurValue.Replace("'", "''")) + ")";
}
}
else
{
_where += " AND Assets.ValueReplacement" + Security.SQLInjectionBlock(CurValue.Replace("'", "''"));
}
}
//#4
CurValue = filter.ValueSalvageExpression;
if (CurValue.Length > 0)
{
if (CurValue.Contains("NULL"))
{
if (CurValue == "NULL") _where += " AND Assets.ValueSalvage IS NULL";
else
{
CurValue = CurValue.Substring(4, CurValue.Length - 4);
_where += " AND (Assets.ValueSalvage IS NULL OR Assets.ValueSalvage" + Security.SQLInjectionBlock(CurValue.Replace("'", "''")) + ")";
}
}
else
{
_where += " AND Assets.ValueSalvage" + Security.SQLInjectionBlock(CurValue.Replace("'", "''"));
}
}
//#5
CurValue = filter.DisposalCostExpression;
if (CurValue.Length > 0)
{
if (CurValue.Contains("NULL"))
{
if (CurValue == "NULL") _where += " AND Assets.DisposalCost IS NULL";
else
{
CurValue = CurValue.Substring(4, CurValue.Length - 4);
_where += " AND (Assets.DisposalCost IS NULL OR Assets.DisposalCost" + Security.SQLInjectionBlock(CurValue.Replace("'", "''")) + ")";
}
}
else
{
_where += " AND Assets.DisposalCost" + Security.SQLInjectionBlock(CurValue.Replace("'", "''"));
}
}
_having = GetAssetCustomPropertiesByFilterSQLQuery(ref filter, ref _custom_property_count);
if (!string.IsNullOrEmpty(_having))
{
_having = "having ((Select count(*) from AssetPropertyValues apv1 where apv1.DId = " + departmentId.ToString() + " AND apv1.AssetId=Assets.Id " +
_having + ")=" + _custom_property_count + ") ";
_groupby = "Assets.DepartmentId,Assets.Id,Assets.OwnerId,Assets.CheckedOutId,Assets.TypeId,Assets.MakeId,Assets.ModelId," +
"Assets.location_id,Assets.LocationId,Assets.VendorId,Assets.WarrantyVendor,Assets.Name,Assets.SerialNumber,Assets.Description,Assets.Value," +
"Assets.DateAquired,Assets.LaborWarrantyLength,Assets.PartsWarrantyLength,CONVERT(nvarchar(max),Assets.Notes),Assets.Room,Assets.PONumber,Assets.Active,Assets.FundingCode," +
"Assets.CategoryId,Assets.StatusId,Assets.AssetSort,Assets.DatePurchased,Assets.DateDeployed,Assets.DateOutOfService,Assets.DateEntered," +
"Assets.DateReceived,Assets.DateDisposed,Assets.ValueCurrent,Assets.ValueReplacement,Assets.ValueDepreciated,Assets.ValueSalvage,Assets.DisposalCost," +
"Assets.FundingSource,Assets.dtUpdated,Assets.intUpdatedBy,Assets.AccountId,Accounts.vchName,Assets.AssetNumber,Assets.AssetGUID," +
"Assets.Unique1,Assets.Unique2,Assets.Unique3,Assets.Unique4,Assets.Unique5,Assets.Unique6,Assets.Unique7," +
"lo_owner.LastName,lo_owner.FirstName,Vendors.Name,WarrantyVendors.NameAssetStatus.vchStatus,lo_checkout.LastName,lo_checkout.FirstName,lo_owner.Email,lo_checkout.Email,WarrantyVendors.name,AssetStatus.vchStatus," +
"AssetCategories.Name,AssetTypes.Name,AssetMakes.Make,AssetModels.Model,dbo.fxGetUserName2(lo_updated.FirstName, lo_updated.LastName, lo_updated.Email),AuditNote";
string[] Groups = _groupby.Split(',');
_groupby = string.Empty;
string LowSql = _sql.ToLower();
foreach (string Group in Groups) if (LowSql.Contains(Group.ToLower())) _groupby += "," + Group;
_groupby = " GROUP BY " + _groupby.Substring(1) + " ";
}
if (colsetting != null)
sqlQuery = _sql + _where + _groupby + _having + _static_order;
return sqlQuery;
}
public static string GetAssetsByFilterSQLQuery(int departmentId, ref Filter filter, ref ColumnsSetting colsetting, bool isRealDates, bool isForExport, string customSort, string customGroupSort, int? UserId, Config cfg, bool? Active)
{
return GetAssetsByFilterSQLQuery(Guid.Empty, departmentId, ref filter, ref colsetting, isRealDates, isForExport, customSort, customGroupSort, UserId, cfg, Active)
;
}
public static DataTable SelectNonActiveAssetsByFilter(int departmentId, Filter filter, ColumnsSetting colsetting, string customSort, out int assetsCount, string customGroupSort, int UserId, Config cfg, bool? Active)
{
string sqlQuery = GetAssetsByFilterSQLQuery(departmentId, ref filter, ref colsetting, false, false, customSort, customGroupSort, UserId, cfg, Active);
DataTable _table = SelectByQuery(sqlQuery);
assetsCount = _table.Rows.Count;
_table = AssetGroupingByStatus(ref _table);
return _table;
}
public static DataTable SelectAssetsByFilterForExport(Guid organizationId, int departmentId, Filter filter, ColumnsSetting colsetting, int? UserId, Config cfg)
{
string sqlQuery = GetAssetsByFilterSQLQuery(organizationId, departmentId, ref filter, ref colsetting, true, true, "", null, UserId, cfg, true);
DataTable dtAssets = SelectByQuery(sqlQuery, organizationId);
dtAssets = AssetGrouping(ref dtAssets);
return dtAssets;
}
public static DataTable SelectAssetsByFilterForExport(Guid organizationId, int departmentId, Filter filter, ColumnsSetting colsetting)
{
return SelectAssetsByFilterForExport(organizationId, departmentId, filter, colsetting, null, null);
}
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 SelectTicketsByFilter(UserAuth usr, Config cfg, ColumnsSetting colset, Filter filter, QueryFilter qfilter)
{
return SelectTicketsByFilter(usr, cfg, colset, filter, qfilter, false, 0);
}
public static DataTable SelectFiltered(UserAuth userAuth, ColumnsSetting colset, Filter filter, bool addNoAccountRow)
{
return GlobalFilters.SetFilter(userAuth.lngDId, userAuth.lngUId, SelectByFilter(userAuth, colset, filter, addNoAccountRow), "Id", GlobalFilters.FilterType.Accounts);
}
public static DataTable SelectFiltered(UserAuth userAuth, ColumnsSetting colset, Filter filter)
{
return SelectFiltered(userAuth, colset, filter, false);
}
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);
}