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