public static object Search(string tungay, string denngay, int startIndex, int maximumRows, string sortExpressions)
{
Guid subId = SessionHelper.GetSession(SessionKey.SubDomain) == string.Empty ? Guid.Empty : new Guid(SessionHelper.GetSession(SessionKey.SubDomain));
string query = string.Empty;
query = @"SELECT sb.[Description] as Title ,COUNT([SubDomainId]) as ViewTime FROM [Log] l right join SubDomain sb on l.[SubDomainId]=sb.Id where 1=1";
query += " and l.[SubDomainId] is not null";
if (!(subId == Guid.Empty))
{
query += " and [SubDomainId] ='" + subId + "'";
}
if (tungay != string.Empty && denngay != string.Empty)
{
DateTime dateStart = DateTime.Parse(tungay, new System.Globalization.DateTimeFormatInfo {
ShortDatePattern = "dd/MM/yyyy"
});
denngay += " 23:59:59 PM";
DateTime dateEnd = DateTime.Parse(denngay, new System.Globalization.DateTimeFormatInfo {
ShortDatePattern = "dd/MM/yyyy"
});
query += " and l.LogTime>='" + dateStart + "'";
query += " and l.LogTime<= '" + dateEnd + "'";
}
query += " group by l.[SubDomainId],sb.[Description]";
query += " order by ViewTime Desc";
VisitorSubDomainList = new List <VisitorSubDomain>();
using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PSCPortalConnectionString"].ConnectionString))
{
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandText = string.Format(query);
command.CommandType = System.Data.CommandType.Text;
command.Connection = con;
con.Open();
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
VisitorSubDomain itemp = new VisitorSubDomain();
itemp.Title = (string)reader["Title"];
itemp.ViewTime = (int)reader["ViewTime"];
VisitorSubDomainList.Add(itemp);
}
}
Dictionary <string, object> result = new Dictionary <string, object>();
try
{
result["Data"] = IEnumerableExtentionMethods.GetSegmentList(VisitorSubDomainList, startIndex, maximumRows, sortExpressions);
result["Count"] = VisitorSubDomainList.Count();
}
catch (Exception)
{
throw;
}
return(result);
}
//Thống kê số lượng truy cập trang
public static List <VisitorSubDomain> ThongKeSoLuongTruyCapTrang(string tungay, string denngay)
{
Guid subId = SessionHelper.GetSession(SessionKey.SubDomain) == string.Empty ? Guid.Empty : new Guid(SessionHelper.GetSession(SessionKey.SubDomain));
string query = string.Empty;
query = "SELECT sb.[Description] as Title ,COUNT([SubDomainId]) as ViewTime FROM [Log] l left join SubDomain sb on l.[SubDomainId]=sb.Id where 1=1";
query += " and l.[SubDomainId] is not null";
if (!(subId == Guid.Empty))
{
query += " and [SubDomainId] ='" + subId + "'";
}
if (tungay != string.Empty && denngay != string.Empty)
{
DateTime dateStart = DateTime.Parse(tungay, new System.Globalization.DateTimeFormatInfo {
ShortDatePattern = "dd/MM/yyyy"
});
denngay += " 23:59:59 PM";
DateTime dateEnd = DateTime.Parse(denngay, new System.Globalization.DateTimeFormatInfo {
ShortDatePattern = "dd/MM/yyyy"
});
query += " and l.LogTime>='" + dateStart + "'";
query += " and l.LogTime<= '" + dateEnd + "'";
}
query += " group by l.[SubDomainId],sb.[Description]";
query += " order by ViewTime Desc";
List <PSCPortal.Systems.CMS.VisitorSubDomain> result = new List <PSCPortal.Systems.CMS.VisitorSubDomain>();
using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PSCPortalConnectionString"].ConnectionString))
{
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandText = string.Format(query);
command.CommandType = System.Data.CommandType.Text;
command.Connection = con;
con.Open();
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PSCPortal.Systems.CMS.VisitorSubDomain itemp = new PSCPortal.Systems.CMS.VisitorSubDomain();
itemp.Title = (string)reader["Title"];
itemp.ViewTime = (int)reader["ViewTime"];
result.Add(itemp);
}
}
return(result);
}