private static bool GetOneclickTransaction(int counter) { BulkProfileOwnership = new BulkProfileOwnership(); using (var cn = new SqlConnection("metl.destination.oneclick")) //using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["metl.destination.oneclick"].ConnectionString)) { cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = @"WITH Holds(libraryid,isbn,totalcopies) AS ( select PatronLibraryId AS LibraryId,Isbn,COUNT(1) AS totalcopies from DPCore.holding.PatronInterest(NOLOCK) where InterestTypeId=2 AND CAST(BeginOn AS DATE) = DATEADD(day,-" + counter + @" , Cast(GETDATE() as date)) AND EndOn > getdate() GROUP BY PatronLibraryId,Isbn ) , Circs(libraryid,isbn,totalcopies) AS ( select PatronLibraryId AS LibraryId,Isbn,COUNT(1) AS totalcopies from DPCore.holding.PatronInterest(NOLOCK) where InterestTypeId=3 AND CAST(BeginOn AS DATE) = DATEADD(day,-" + counter + @" , Cast(GETDATE() as date)) AND EndOn > getdate() GROUP BY PatronLibraryId,Isbn ) SELECT L.SourceItemId AS ScopeId, Li.Isbn, SUM(ISNULL(Li.CircCount,0)) AS TotalCopies, ISNULL(C.totalcopies,0) AS CirculationCopies, ISNULL(H.totalcopies,0) AS HoldsCopies FROM DPCore.holding.LibraryIsbn LI(NOLOCK) INNER JOIN OneClick.LibraryProfile.Library L(NOLOCK) ON Li.LibraryId = l.Id LEFT OUTER JOIN Holds H ON Li.Isbn = H.isbn ANd li.LibraryId = H.libraryid LEFT OUTER JOIN Circs C ON Li.Isbn = C.isbn ANd li.LibraryId = C.libraryid WHERE l.Id <> 1062 GROUP BY SourceItemId, Li.isbn,C.totalcopies,H.totalcopies "; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { var item = new ProfileOwnership(); item.ScopeId = reader.GetInt32(0); item.Isbn = reader.GetString(1); item.TotalCopies = reader.GetInt32(2); item.CirculationCopies = reader.GetInt32(3); item.HoldsCopies = reader.GetInt32(4); item.CreatedAt = DateTime.Now.Subtract(TimeSpan.FromDays(counter)); BulkProfileOwnership.ProfileOwnerships.Add(item); } } } } return(BulkProfileOwnership.ProfileOwnerships.Count > 0); }
public BulkProfileOwnership Post(BulkProfileOwnership t) { var collection = new MongoClient(new MongoUrl("")).GetDatabase("").GetCollection <BsonDocument>("etlownershipdata"); var builder = Builders <BsonDocument> .Filter; var filter = builder.Eq("Year", DateTime.Now.Year) & builder.Eq("Month", DateTime.Now.Month) & builder.Eq("Day", DateTime.Now.Day - 1); if (collection.CountAsync(filter).Result == 0) { var groupedOwnership = t.ProfileOwnerships.GroupBy(g => g.ScopeId); var dateTime = t.ProfileOwnerships.First().CreatedAt; var ownershipList = groupedOwnership.Select(item => new BsonDocument() { { "ScopeId", item.Key }, { "Data", new BsonArray(item.Select(i => new BsonDocument() { { "Isbn", i.Isbn }, { "HoldsCopies", i.HoldsCopies }, { "TotalCopies", i.TotalCopies }, { "CirculationCopies", i.CirculationCopies }, })) }, { "Day", dateTime.Day }, { "Month", dateTime.Month }, { "Year", dateTime.Year } }).ToList(); ConsoleProcess.Start(t.GetType()); foreach (var ownership in ownershipList) { collection.InsertOneAsync(ownership); } ConsoleProcess.End(t.GetType()); } return(t); }