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