public static int GetSitesCount(SiteFilter filter) { int count; var sql = "SELECT COUNT(*) FROM Site where (1=1)" + getFilterString(filter); using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString)) { SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection); try { sqlConnection.Open(); count = (int)sqlCommand.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { sqlConnection.Close(); } } return(count); }
private static string getFilterString(SiteFilter filter) { string sql = ""; if (!string.IsNullOrEmpty(filter.SiteCode)) { sql += " AND dbo.[Site].[SiteCode] LIKE '%" + filter.SiteCode + "%'"; } if (!string.IsNullOrEmpty(filter.SiteName)) { sql += " AND dbo.[Site].[SiteName] LIKE '%" + filter.SiteName + "%'"; } if (!(filter.CityId == 0)) { sql += " AND dbo.[Site].[CityId] LIKE '%" + filter.CityId + "%'"; } if (filter._2g) { sql += " AND dbo.[Site].[2g] = " + 1; } if (filter._3g) { sql += " AND dbo.[Site].[3g] = " + 1; } if (filter._4g) { sql += " AND dbo.[Site].[4g] = " + 1; } return(sql); }
public static DataTable GetSites(SiteFilter filter, int maximumRows, int startRowIndex) { DataTable dataTable = new DataTable(); var sql = @" SELECT * from ( SELECT ROW_NUMBER() OVER ( ORDER BY SiteId DESC ) ROWNUMBER, SiteCode , SiteName, SiteId, [2g], [3g], [4g] FROM [dbo].[Site] WHERE (1 = 1) "; sql += getFilterString(filter); sql += ") As query where query.ROWNUMBER > " + startRowIndex + " AND query.ROWNUMBER <= " + (startRowIndex + maximumRows); using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString)) { sqlConnection.Open(); SqlDataAdapter sqlAdapter = new SqlDataAdapter(sql, sqlConnection); sqlAdapter.Fill(dataTable); } return(dataTable); }
/// <summary> /// Список сайтов /// </summary> /// <param name="filtr"></param> /// <returns></returns> public override SitesShortModel[] getSiteListWithCheckedForUser(SiteFilter filtr) { using (var db = new CMSdb(_context)) { var query = db.cms_sitess .Where(w => w.c_alias != String.Empty) .OrderBy(o => new { o.c_name }); var data = query //.Skip(filtr.Size * (filtr.Page - 1)) //.Take(filtr.Size) .Select(s => new SitesShortModel { Id = s.id, Title = s.c_name, Alias = s.c_alias, SiteOff = s.b_site_off, Type = s.c_content_type, DomainList = getSiteDomains(s.c_alias), Checked = (filtr.UserId.HasValue) ? s.fklinksitetousers.Any(u => u.f_user == filtr.UserId) ? true : false : false }); if (data.Any()) { return(data.ToArray()); } return(null); } }
/// <summary> /// Формирование sql-запрса на основании фильтра /// </summary> /// <param name="db"></param> /// <param name="filtr"></param> /// <returns></returns> private IQueryable <cms_sites> queryBySiteFilter(CMSdb db, SiteFilter filtr) { if (filtr == null) { throw new Exception("cmsRepository > queryByEventFilter: Filter is null"); } var query = db.cms_sitess.AsQueryable(); if (!string.IsNullOrEmpty(filtr.SearchText)) { query = query.Where(w => w.c_name.Contains(filtr.SearchText)); } if (filtr.RelId.HasValue && filtr.RelId.Value != Guid.Empty) { //В таблице ищем связи оранизация - контент (новость/событие) //var objctLinks = db.content_content_links // .Where(s => s.f_content == filtr.RelId.Value) // .Where(s => s.f_content_type == filtr.RelType.ToString().ToLower()) // .Where(s => s.f_link_type == ContentLinkType.SITE.ToString().ToLower()); //if (!objctLinks.Any()) // query = query.Where(o => o.id == Guid.Empty); //Делаем заранее ложный запрос //else //{ // var objctsId = objctLinks.Select(o => o.f_link); // query = query.Where(o => objctsId.Contains(o.id)); //} } return(query); }
public override SitesShortModel[] getSiteListWithCheckedForBanner(SiteFilter filtr) { using (var db = new CMSdb(_context)) { var query = db.cms_sitess.AsQueryable(); if (filtr.RelId.HasValue && filtr.RelId.Value != Guid.Empty) { var List = query .OrderBy(o => new { o.c_name }) .Take(filtr.Size) .Select(s => new SitesShortModel() { Id = s.id, Title = s.c_name, Alias = s.c_alias, SiteOff = s.b_site_off, Type = s.c_content_type, DomainList = getSiteDomains(s.c_alias), //Checked = ContentLinkExists(filtr.RelId.Value, filtr.RelType, s.id, ContentLinkType.SITE), //Origin = ContentLinkOrigin(filtr.RelId.Value, filtr.RelType, s.id, ContentLinkType.SITE) }); if (List.Any()) { return(List.ToArray()); } } return(null); } }
/// <summary> /// Список сайтов /// </summary> /// <param name="filtr"></param> /// <returns></returns> public override SitesList getSiteList(SiteFilter filtr) { using (var db = new CMSdb(_context)) { var query = queryBySiteFilter(db, filtr); if (filtr.Disabled != null) //в данном случае используется для определения отключенных/включенных сайтов { if ((bool)filtr.Disabled) { query = query.Where(w => w.b_site_off == filtr.Disabled); } } if (filtr.SearchText != null) { query = query.Where(w => w.c_name.Contains(filtr.SearchText)); } query = query.OrderBy(o => new { o.c_name }); if (query.Any()) { int ItemCount = query.Count(); var List = query .Select(s => new SitesModel { Id = s.id, Title = s.c_name, Alias = s.c_alias, SiteOff = s.b_site_off, Type = s.c_content_type, DomainList = getSiteDomains(s.c_alias) }). Skip(filtr.Size * (filtr.Page - 1)). Take(filtr.Size); SitesModel[] sitesInfo = List.ToArray(); return(new SitesList { Data = sitesInfo, Pager = new Pager { page = filtr.Page, size = filtr.Size, items_count = ItemCount, page_count = ItemCount / filtr.Size } }); } return(null); } }
public DataTable GetSites(SiteFilter siteFilter) { DataTable dataTable = new DataTable(); var sql = "select * from [Site] ORDER BY SiteId DESC"; using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString)) { sqlConnection.Open(); SqlDataAdapter sqlAdapter = new SqlDataAdapter(sql, sqlConnection); sqlAdapter.Fill(dataTable); } return(dataTable); }
private SiteFilter GetFilter() { SiteFilter entity = new SiteFilter(); entity.SiteName = txtSiteName.Text.ToString(); entity.SiteCode = txtSiteCode.Text.ToString(); if (ddlCity.SelectedIndex == 0) { entity.CityId = 0; } else { entity.CityId = Convert.ToInt32(ddlCity.SelectedValue); } if (_2g.Checked) { entity._2g = true; } else { entity._2g = false; } if (_3g.Checked) { entity._3g = true; } else { entity._3g = false; } if (_4g.Checked) { entity._4g = true; } else { entity._4g = false; } return(entity); }
public ActionResult SiteListModal(Guid objId, ContentType objType) { var filtr = new SiteFilter() { Domain = Domain, RelId = objId, RelType = objType, Size = last_items }; var model = new SitesModalViewModel() { ObjctId = objId, ObjctType = objType, SitesList = _cmsRepository.getSiteListWithCheckedForBanner(filtr) }; return(PartialView("Modal/Sites", model)); }
public DataTable GetSitesForExcel(SiteFilter filter) { DataTable dataTable = new DataTable(); var sql = @" SELECT dbo.Region.RegionName AS 'Region' , dbo.[Site].SiteCode AS 'Site_Code', dbo.[Site].SiteName AS 'Site_Name', dbo.City.CityName AS 'City', dbo.Zone.ZoneName AS 'City_Zone', dbo.SiteType.[Type], Controller_2g.ControllerName AS 'BSC', Controller_3g.ControllerName AS 'RNC', Controller_4g.ControllerName AS 'LTE_City', CONVERT(NVARCHAR(1), dbo.[Site].[2g]) AS '2g', CONVERT(NVARCHAR(1), dbo.[Site].[3g]) AS '3g', CONVERT(NVARCHAR(1), dbo.[Site].[4g]) AS 'LTE' FROM [dbo].[Site] INNER JOIN dbo.Region ON dbo.[Site].RegionId = dbo.Region.RegionId INNER JOIN dbo.City ON dbo.[Site].CityId = dbo.City.CityId INNER JOIN dbo.Zone ON dbo.Site.ZoneId = dbo.Zone.ZoneId INNER JOIN dbo.SiteType ON dbo.Site.SiteType = dbo.SiteType.TypeId LEFT OUTER JOIN dbo.Controller AS Controller_2g ON Controller_2g.ControllerId = dbo.Site.ControlerId2g LEFT OUTER JOIN dbo.Controller AS Controller_3g ON Controller_3g.ControllerId = dbo.Site.ControlerId3g LEFT OUTER JOIN dbo.Controller AS Controller_4g ON Controller_4g.ControllerId = dbo.Site.ControlerId4g WHERE (1 = 1) "; sql += getFilterString(filter); sql += " ORDER BY dbo.Site.SiteCode"; using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString)) { sqlConnection.Open(); SqlDataAdapter sqlAdapter = new SqlDataAdapter(sql, sqlConnection); sqlAdapter.Fill(dataTable); } return(dataTable); }
public async Task <IActionResult> Get([FromQuery] SiteFilter filter, int rowsPerPage = 0, int pageNumber = 1) { var sites = siteService.Get(filter, rowsPerPage, pageNumber); return(Ok(Mapper.Map <PaginatedItemsDto <SiteOverviewDto> >(sites))); }
public abstract SitesShortModel[] getSiteListWithCheckedForBanner(SiteFilter filtr);
// Все сайты портала public abstract SitesList getSiteList(SiteFilter filtr);