public DataTable GetFilterDataByType(FilterType ftype)
{
if (m_IsGlobalFilterEnabled)
{
switch (ftype)
{
case FilterType.Accounts:
return(GlobalFilters.SetFilter(m_DeptID, m_GlobalFilterUserId, SelectCriteriaDataByType(m_DeptID, ftype, m_ID), "ID", GlobalFilters.FilterType.Accounts));
case FilterType.Classes:
return(GlobalFilters.SetFilter(m_DeptID, m_GlobalFilterUserId, SelectCriteriaDataByType(m_DeptID, ftype, m_ID), "ID", GlobalFilters.FilterType.Classes));
case FilterType.Levels:
return(GlobalFilters.SetFilter(m_DeptID, m_GlobalFilterUserId, SelectCriteriaDataByType(m_DeptID, ftype, m_ID), "ID", GlobalFilters.FilterType.Levels));
case FilterType.Locations:
return(GlobalFilters.SetFilter(m_DeptID, m_GlobalFilterUserId, SelectCriteriaDataByType(m_DeptID, ftype, m_ID), "ID", GlobalFilters.FilterType.Locations));
case FilterType.SupportGroups:
return(GlobalFilters.SetFilter(m_DeptID, m_GlobalFilterUserId, SelectCriteriaDataByType(m_DeptID, ftype, m_ID), "ID", GlobalFilters.FilterType.SupportGroups));
case FilterType.UnassignedQueue:
return(GlobalFilters.SetFilter(m_DeptID, m_GlobalFilterUserId, SelectCriteriaDataByType(m_DeptID, ftype, m_ID), "ID", GlobalFilters.FilterType.UnassignedQueue));
default:
return(SelectCriteriaDataByType(m_DeptID, ftype, m_ID));
}
}
else
{
return(SelectCriteriaDataByType(m_DeptID, ftype, m_ID));
}
}
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 DataTable SelectUsersByFilter(Guid OrgId, int DeptID, int UserID, int TOPCount, Filter UsersFilter)
{
string _sqlSelect = "SELECT " + (TOPCount >= 0 ? "TOP " + TOPCount.ToString() : string.Empty);
string _sqlGroupBy = string.Empty;
string _sqlOrderBy = string.Empty;
if (UsersFilter.SearchAccountsToo)
{
_sqlSelect += " LJ.id AS Id, L.Email, LJ.id AS UserID, LJ.UserType_Id, L.FirstName, L.LastName, L.Title, dbo.fxGetUserName(L.FirstName, L.LastName, L.Email) AS UserFullName, UA.AccountId, dbo.fxGetUserName(L.FirstName, L.LastName, '') + ' - ' + CASE WHEN Accounts.DId IS NULL THEN ISNULL(tbl_company.company_name, '') + ' (Internal)' ELSE Accounts.vchName END + ' - ' + L.Email AS FullName, COUNT(tbl_ticket.id) AS TicketCount, ISNULL(UA.AccountLocationId, 0) AS AccountLocationId, ISNULL(dbo.fxGetUserLocationName(" + DeptID.ToString() + ", UA.AccountLocationId), '') AS AccountLocationName, dbo.fxGetUserLocationName(" + DeptID.ToString() + ", LJ.LocationId) AS DeptLocation, "
+ " L.MobileEmail, CASE L.MobileEmailType WHEN 0 THEN 'HTML' WHEN 1 THEN 'Text' WHEN 2 THEN 'Short Text' ELSE 'Undefined' END AS MobileEmailTypeName "
+ " FROM tbl_LoginCompanyJunc LJ INNER JOIN tbl_Logins L ON L.id = LJ.login_id INNER JOIN tbl_company ON LJ.company_id = tbl_company.company_id LEFT OUTER JOIN UserAccounts UA ON UA.DepartmentId = " + DeptID.ToString() + " AND UA.UserId = LJ.Id LEFT OUTER JOIN Accounts ON Accounts.DId=" + DeptID.ToString() + " AND Accounts.Id = UA.AccountId LEFT OUTER JOIN tbl_ticket ON tbl_ticket.company_id = " + DeptID.ToString() + " AND tbl_ticket.user_id = LJ.id AND (tbl_ticket.intAcctId = UA.AccountId OR (tbl_ticket.intAcctId IS NULL AND UA.AccountId IS NULL))";
_sqlGroupBy = " GROUP BY LJ.id, UA.AccountId, L.LastName, L.FirstName, Accounts.DId, tbl_company.company_name, Accounts.vchName, L.Email, LJ.UserType_Id, UA.AccountLocationId, UA.AccountLocationId";
_sqlOrderBy = " ORDER BY L.LastName, L.FirstName, LJ.id, TicketCount DESC";
}
else
{
_sqlSelect += " LJ.id AS Id, L.Email, LJ.Id AS UserID, LJ.UserType_Id, L.FirstName, L.LastName, L.Title, dbo.fxGetUserName(L.FirstName, L.LastName, L.Email) AS UserFullName, dbo.fxGetFullUserName(L.FirstName, L.LastName, L.Email) AS FullName, LJ.LocationId, dbo.fxGetUserLocationName(" + DeptID.ToString() + ", LJ.LocationId) AS DeptLocation, "
+ " L.MobileEmail, CASE L.MobileEmailType WHEN 0 THEN 'HTML' WHEN 1 THEN 'Text' WHEN 2 THEN 'Short Text' ELSE 'Undefined' END AS MobileEmailTypeName "
+ " FROM tbl_LoginCompanyJunc LJ"
+ " INNER JOIN tbl_Logins L ON L.id=LJ.login_id"
+ " LEFT OUTER JOIN Locations DL ON DL.DId=" + DeptID.ToString() + " AND DL.Id=LJ.LocationId";
_sqlOrderBy = " ORDER BY L.LastName, L.FirstName";
}
string _sqlWhere = " WHERE LJ.company_id=" + DeptID.ToString();
if (UsersFilter.SearchString.Length > 0)
{
string _searchString = Security.SQLInjectionBlock(UsersFilter.SearchString.Replace("'", "''"));
_sqlWhere += " AND (L.LastName+', '+L.FirstName LIKE '" + _searchString + "%'";
_sqlWhere += " OR L.FirstName+' '+L.LastName LIKE '" + _searchString + "%'";
_sqlWhere += " OR L.Email LIKE '" + _searchString + "%'";
if (UsersFilter.SearchAccountsToo)
{
_sqlWhere += " OR (Accounts.DId IS NULL AND ISNULL(tbl_company.company_name, '') + ' (Internal)' LIKE '" + _searchString + "%')";
_sqlWhere += " OR (Accounts.DId IS NOT NULL AND Accounts.vchName LIKE '" + _searchString + "%')";
}
_sqlWhere += ")";
}
else
{
if (UsersFilter.FirstName.Length > 0) _sqlWhere += " AND (" + OrLogic(UsersFilter.FirstName, "L.FirstName") + ")";
if (UsersFilter.LastName.Length > 0) _sqlWhere += " AND (" + OrLogic(UsersFilter.LastName, "L.LastName") + ")";
if (UsersFilter.EMail.Length > 0) _sqlWhere += " AND (" + OrLogic(UsersFilter.EMail, "L.Email") + ")";
if (UsersFilter.LocationID != 0) _sqlWhere += " AND EXISTS(SELECT ULC.Id FROM UserLocations UL INNER JOIN dbo.fxGetAllChildLocations(" + DeptID.ToString() + ", " + UsersFilter.LocationID.ToString() + ") ULC ON UL.LocationId=ULC.Id WHERE UL.DId=" + DeptID.ToString() + " AND UL.UId=LJ.Id)";
if (UsersFilter.AccountID > 0)
{
if (UsersFilter.SearchAccountsToo) _sqlWhere += " AND UA.AccountId=" + UsersFilter.AccountID.ToString();
else _sqlWhere += " AND " + UsersFilter.AccountID.ToString() + " IN (SELECT AccountId FROM UserAccounts WHERE DepartmentId=" + DeptID.ToString() + " AND UserId=LJ.Id)";
}
else if (UsersFilter.AccountID == -1)
{
if (UsersFilter.SearchAccountsToo) if (UsersFilter.SearchAccountsToo) _sqlWhere += " AND UA.AccountId IS NULL";
else _sqlWhere += " AND (NOT EXISTS(SELECT AccountId FROM UserAccounts WHERE DepartmentId=" + DeptID.ToString() + " AND UserId=LJ.Id) OR EXISTS(SELECT AccountId FROM UserAccounts WHERE DepartmentId=" + DeptID.ToString() + " AND UserId=LJ.Id AND AccountId IS NULL))";
}
if (UsersFilter.AccountID != 0 && UsersFilter.AccLocationID != 0)
{
if (UsersFilter.AccountID > 0)
_sqlWhere += " AND EXISTS(SELECT ULC.Id FROM UserAccounts UA INNER JOIN dbo.fxGetAllChildLocations(" + DeptID.ToString() + ", " + UsersFilter.AccLocationID.ToString() + ") ULC ON UA.AccountLocationId=ULC.Id WHERE UA.DepartmentId=" + DeptID.ToString() + " AND UA.AccountId=" + UsersFilter.AccountID.ToString() + " AND UA.UserId=LJ.Id)";
else if (UsersFilter.AccountID == -1) _sqlWhere += " AND EXISTS(SELECT ULC.Id FROM UserLocations UL INNER JOIN dbo.fxGetAllChildLocations(" + DeptID.ToString() + ", " + UsersFilter.AccLocationID.ToString() + ") ULC ON UL.LocationId=ULC.Id WHERE UL.DId=" + DeptID.ToString() + " AND UL.UId=LJ.Id)";
}
}
if (UsersFilter.Type != UserType.NotSet) _sqlWhere += " AND LJ.UserType_Id=" + ((int)UsersFilter.Type).ToString();
else _sqlWhere += " AND LJ.UserType_Id<>4";
if (UsersFilter.Status != ActiveStatus.NotSet) _sqlWhere += " AND LJ.btUserInactive=" + ((int)UsersFilter.Status).ToString();
if (UsersFilter.LastLogin != UserLastLogin.NotSet) _sqlWhere += " AND LJ.dtlastLogin<DATEADD(day," + ((int)UsersFilter.LastLogin).ToString() + ", GETUTCDATE())";
//Check Global Filters
if (UserID != 0 && UsersFilter.UseGlobalFilters)
{
GlobalFilters _gf = new GlobalFilters(OrgId, DeptID, UserID);
string _gfWhere = string.Empty;
/*if (UsersFilter.ConfigLocationsEnabled && _gf.IsFilterEnabled(GlobalFilters.FilterState.EnabledGlobalFilters) && _gf.IsFilterEnabled(GlobalFilters.FilterType.Locations))
{
string _strLoc = GlobalFilters.SelectFilterByTypeToString(DeptID, UserID, GlobalFilters.FilterType.Locations);
if (_strLoc.Length > 0) _gfWhere += "(EXISTS(SELECT Id FROM UserLocations WHERE DId=" + DeptID.ToString() + " AND UId=LJ.Id AND LocationId IN (SELECT Id FROM dbo.fxGetAllChildLocationsFromList(" + DeptID.ToString() + ", '" + _strLoc + "'))) OR LJ.LocationId IN (SELECT Id FROM dbo.fxGetAllChildLocationsFromList(" + DeptID.ToString() + ", '" + _strLoc + "'))";
}*/
if (UsersFilter.ConfigAccountsEnabled && _gf.IsFilterEnabled(GlobalFilters.FilterState.EnabledGlobalFilters) && _gf.IsFilterEnabled(GlobalFilters.FilterType.Accounts))
{
string _strAcc = GlobalFilters.SelectFilterByTypeToString(OrgId, DeptID, UserID, GlobalFilters.FilterType.Accounts);
if (_strAcc.Length > 0)
{
if (_gfWhere.Length > 0) _gfWhere += " OR ";
else _gfWhere += "(";
_gfWhere += "EXISTS(SELECT Id FROM UserAccounts WHERE DepartmentId=" + DeptID.ToString() + " AND UserId=LJ.Id AND AccountId IN (" + _strAcc + ")" + (_strAcc.Contains("-1") ? " OR AccountId IS NULL" : string.Empty) + ")";
}
}
if (_gfWhere.Length > 0) _sqlWhere += " AND " + _gfWhere + ")";
}
return SelectByQuery(_sqlSelect + _sqlWhere + _sqlGroupBy + _sqlOrderBy, OrgId);
}
public static DataTable SelectAll(int DeptID, int UserID)
{
return(GlobalFilters.SetFilter(DeptID, UserID, SelectAll(DeptID), "id", GlobalFilters.FilterType.SupportGroups));
}
public static DataTable SelectAll(int DeptID, int UserID)
{
return(PostFilter(GlobalFilters.SetFilter(DeptID, UserID, SelectAll(DeptID), "id", GlobalFilters.FilterType.Classes), -1));
}
public static DataTable SelectByInactiveStatus(Guid OrgId, int DeptID, int UserID, InactiveStatus inactiveStatus, int ParentId)
{
return(PostFilter(GlobalFilters.SetFilter(OrgId, DeptID, UserID, SelectByInactiveStatus(OrgId, DeptID, inactiveStatus, ParentId), "id", GlobalFilters.FilterType.Classes), ParentId));
}
public static DataTable SelectAll(Guid OrgId, int DeptID, int UserID)
{
return(GlobalFilters.SetFilter(OrgId, DeptID, UserID, SelectAll(OrgId, DeptID), "tintLevel", GlobalFilters.FilterType.Levels));
}
