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