private static List <ElasticMagazineIndex> LoadPopularMagazines()
{
var items = new List <ElasticMagazineIndex>();
using (var cn = new MySqlConnection("metl.zinio.mysql"))
//using (var cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["metl.zinio.mysql"].ConnectionString))
{
cn.Open();
using (MySqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Magazine_Id AS MagazineId, COUNT(PATRON_ID) As PatronCheckoutCount FROM RBDG.ZINIO_MAGAZINE_STATISTIC " +
"LEFT JOIN RBDG.PATRON ON RBDG.PATRON.ID = RBDG.ZINIO_MAGAZINE_STATISTIC.PATRON_ID " +
"LEFT JOIN RBDG.LIBRARY ON RBDG.PATRON.LIBRARY_ID = RBDG.LIBRARY.ID " +
"WHERE THE_DATE >= now() - interval 3 month " +
"AND RBDG.LIBRARY.OCD_ROOT_DOMAIN = 'rbdigital.com' " +
"GROUP BY Magazine_ID;";
cmd.CommandTimeout = 3600;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var magazine = new ElasticMagazineIndex()
{
MagazineId = reader.GetString(reader.GetOrdinal("MagazineId")),
CheckoutCount = reader.GetInt32(reader.GetOrdinal("PatronCheckoutCount"))
};
items.Add(magazine);
}
}
}
}
return(items);
}
private static List <ElasticMagazineIndex> LoadMagazineFromTrilogy()
{
var items = new List <ElasticMagazineIndex>();
using (SqlConnection cn = new SqlConnection((EtlServiceProvider.ConnectionStrings.SqlServer.trilogy)))
//using (SqlConnection cn = SqlConnectionProvider.GetConnection(EtlServiceProvider.ConnectionStrings.SqlServer.trilogy))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Job_no AS Id, GrossSellingPrice as Price,Genre, " +
"(SELECT TOP 1 Id FROM HydratedModel(NOLOCK) HM WHERE HM.Job_No = s.Job_No AND Hm.Stock_no = s.stock_no AND PublisherModelId = 794 AND IsActive = 1) AS HydratedModelId " +
"FROM eMagazineDetail m INNER JOIN stocklines s on m.Stock_no = s.stock_no and s.status_code = 'Available'";
cmd.CommandTimeout = 3600;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var magazine = new ElasticMagazineIndex()
{
Id = reader.GetString(reader.GetOrdinal("Id")),
Genre = reader.GetString(reader.GetOrdinal("Genre")),
Price = reader.GetDecimal(reader.GetOrdinal("Price")),
TermsAndConditionIdentifier = reader.GetInt32(reader.GetOrdinal("HydratedModelId")),
};
items.Add(magazine);
}
}
}
}
return(items);
}
private static List <ElasticMagazineIndex> LoadMagazineMetadata()
{
var items = new List <ElasticMagazineIndex>();
using (var cn = new MySqlConnection("metl.zinio.mysql"))
//using (var cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["metl.zinio.mysql"].ConnectionString))
{
cn.Open();
using (MySqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT RBID,M.NAME, COUNTRY,GENRE,DESCRIPTION,LANGUAGE,COVER_DATE AS CoverDate," +
"M.ID AS MagazineID,MI.Id AS IssueId,P.Name AS Publisher,REPLACE(MI.COVER_IMAGE_URL,'http','https') As ImageUrl,Frequency" +
", Rating, CURRENT_ISSUE_LIMIT AS CapLimit, ISSN,Price_USD AS Price " +
"FROM RBDG.ZINIO_MAGAZINE M " +
"INNER JOIN RBDG.ZINIO_MAGAZINE_PUBLISHER P ON M.PUBLISHER_ID = P.Id " +
"INNER JOIN RBDG.ZINIO_MAGAZINE_ISSUE MI ON MI.ID = " +
"(SELECT ID FROM RBDG.ZINIO_MAGAZINE_ISSUE WHERE Magazine_ID = M.Id ORDER BY COVER_DATE DESC LIMIT 1) " +
"WHERE P.TIME_PERIOD = 4";
cmd.CommandTimeout = 3600;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var magazine = new ElasticMagazineIndex()
{
Id = reader.GetString(reader.GetOrdinal("RBID")),
Title = reader.GetString(reader.GetOrdinal("Name")),
Country = reader.GetString(reader.GetOrdinal("Country")),
Genre = reader.GetString(reader.GetOrdinal("Genre")),
Description = reader.GetString(reader.GetOrdinal("Description")),
IssueId = reader.GetString(reader.GetOrdinal("IssueId")),
Language = reader.GetString(reader.GetOrdinal("Language")),
Publisher = reader.GetString(reader.GetOrdinal("publisher")),
MagazineId = reader.GetString(reader.GetOrdinal("MagazineId")),
CoverDate = reader.GetDateTime(reader.GetOrdinal("CoverDate")),
ImageUrl = reader.GetString(reader.GetOrdinal("ImageUrl")),
Issn = reader.GetString(reader.GetOrdinal("ISSN")),
//Audience = reader.GetString(reader.GetOrdinal("ImageUrl")),
PublishedOn = reader.GetDateTime(reader.GetOrdinal("CoverDate")),
Rating = reader.GetString(reader.GetOrdinal("Rating")),
Frequency = reader.GetString(reader.GetOrdinal("Frequency")),
CapLimit = reader.GetInt32(reader.GetOrdinal("CapLimit")),
Price = reader.GetInt32(reader.GetOrdinal("Price")),
};
items.Add(magazine);
}
}
}
}
return(items);
}
