public RdfDataBuilder(ISparqlDataset dataset, INamespaceMapper namespaceMapper, TimelineTable timelineTable)
{
this.dataset = dataset ?? throw new ArgumentNullException(nameof(dataset));
this.namespaceMapper = namespaceMapper ?? throw new ArgumentNullException(nameof(namespaceMapper));
this.timelineTable = timelineTable ?? throw new ArgumentNullException(nameof(timelineTable));
sparqlProcessor = new LeviathanQueryProcessor(dataset);
sparqlQueryParser = new SparqlQueryParser();
}
static int Main(string[] args)
{
if (args.Length < 2)
{
Console.WriteLine("Usage:\ndotnet run rawDataRoot targetRoot");
return(1);
}
var rawDataRoot = Path.GetFullPath(args[0]);
var targetRoot = Path.GetFullPath(args[1]);
if (!Directory.Exists(targetRoot))
{
Directory.CreateDirectory(targetRoot);
}
void ExportJson(string fileName, object root)
{
var fullName = Path.Join(targetRoot, fileName);
{
using var sw = new StreamWriter(fullName);
using var jw = new JsonTextWriter(sw);
outputJsonSerializer.Serialize(jw, root);
}
Console.WriteLine("Exported {0} ({1:#,#} B).", fileName, new FileInfo(fullName).Length);
}
var graph = new Graph();
graph.LoadFromFile(Path.Join(rawDataRoot, RawDataFiles.WbDump));
Console.WriteLine("Loaded {0} tuples from {1}.", graph.Triples.Count, rawDataRoot);
var dataset = new InMemoryDataset(graph);
var timeline = TimelineTable.LoadFrom(Path.Join(rawDataRoot, RawDataFiles.Timeline));
var builder = new RdfDataBuilder(dataset, graph.NamespaceMap, timeline);
ExportJson("characters.json", builder.BuildCharacterProfile());
ExportJson("relations.json", builder.BuildRelationGraph());
ExportJson("timeline.json", builder.BuildTimelineMarkers());
ExportJson("links.json", builder.BuildEntityLinks());
foreach (var(language, root) in builder.BuildEntityLabels())
{
ExportJson($"labels.{language}.json", root);
}
ExportJson("entityLookup.json", builder.BuildEntityLookupTable());
return(0);
}
// SUMMARY: only this is in use! TIMELINE
public TimelineTable LoadTimeline(EntranceTableData data)
{
string command = "";
#region # prepare reference days #
DateTime startingDate = DateTime.Parse(data.From),
endingDate = DateTime.Parse(data.To),
referenceStartDate = DateTime.Now,
referenceEndDate = DateTime.Now;
switch (data.StatisticGroupBy)
{
case "HOUR":
referenceStartDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
startingDate = startingDate.AddDays(1);
referenceEndDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
break;
case "WEEK":
startingDate = startingDate.AddDays(1 - (int)startingDate.DayOfWeek);
referenceStartDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
startingDate = startingDate.AddDays(7);
referenceEndDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
data.StatisticGroupBy = "DAY";
break;
case "MONTH":
referenceStartDate = new DateTime(startingDate.Year, startingDate.Month, 1, 0, 0, 0);
startingDate = startingDate.AddMonths(1);
referenceEndDate = new DateTime(startingDate.Year, startingDate.Month, 1, 0, 0, 0);
data.StatisticGroupBy = "DAY";
break;
case "YEAR":
referenceStartDate = new DateTime(startingDate.Year, 1, 1, 0, 0, 0);
startingDate = startingDate.AddYears(1);
referenceEndDate = new DateTime(startingDate.Year, 1, 1, 0, 0, 0);
data.StatisticGroupBy = "MONTH";
break;
}
#endregion
string startDate = data.ConvertDate(referenceStartDate),
endDate = data.ConvertDate(referenceEndDate);
#region # sql command #
command = " SELECT us._time, us.num AS 'us_num', pr.num AS 'pr_num', t.num AS 't_num', st.num AS 'st_num' " +
" FROM " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", us.Created) AS _time FROM MobilePaywall.core.UserSession AS us, " +
//" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
//" WHERE us.Created >= '" + startDate + "' AND us.Created <= '" + endDate + "' " +
" ( SELECT UserSessionGuid FROM MobilePaywall.core.UserSession AS us WHERE us.Created >= '" + startDate + "' AND us.Created <= '" + endDate + "' GROUP BY us.UserSessionGuid ) AS us1, " +
" ( SELECT ServiceID, Name, Description FROM MobilePaywall.core.Service AS s ) AS s " +
" WHERE us1.UserSessionGuid=us.UserSessionGuid AND us.ServiceID=s.ServiceID AND us.Created >= '" + startDate + "' AND us.Created <= '" + endDate + "' " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", us.Created) " +
" ) AS us " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", pr.Created) AS _time FROM MobilePaywall.core.PaymentRequest AS pr " +
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID "+
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID "+
" WHERE pr.Created >= '"+ startDate + "' AND pr.Created <= '" + endDate + "' " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", pr.Created) " +
" ) AS pr ON us._time=pr._time " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", t.Created) AS _time FROM MobilePaywall.core.[Transaction] AS t " +
" LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON t.PaymentID=p.PaymentID "+
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID "+
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID "+
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID "+
" WHERE t.Created >= '"+ startDate + "' AND t.Created <= '" + endDate + "' AND t.TransactionStatusID=5 " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" AND (SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] WHERE PaymentID=p.PaymentID)=1 "+
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", t.Created) "+
" ) AS t ON t._time=us._time " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", t.Created) AS _time FROM MobilePaywall.core.[Transaction] AS t " +
" LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON t.PaymentID=p.PaymentID "+
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID "+
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID "+
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID "+
" WHERE t.Created >= '"+ startDate + "' AND t.Created <= '" + endDate + "' AND t.TransactionStatusID=5 " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" AND (SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] WHERE PaymentID=p.PaymentID)>1 "+
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", t.Created) "+
" ) AS st ON st._time=us._time " +
" ORDER BY us._time ASC ";
#endregion
TimelineTable returnTable = new TimelineTable();
returnTable.Title = string.Format("Group by {0} in time of {1} - {2}", data.StatisticGroupBy, referenceStartDate.ToString(), referenceEndDate.ToString());
DataTable table = this.Load(command);
if (table == null)
{
return(returnTable);
}
foreach (DataRow row in table.Rows)
{
returnTable.Time.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Time].ToString()) ? "0" : row[(int)TimelineTable.Columns.Time].ToString());
returnTable.UserSession.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.UserSession].ToString()) ? "0" : row[(int)TimelineTable.Columns.UserSession].ToString());
returnTable.Identification.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Identification].ToString()) ? "0" : row[(int)TimelineTable.Columns.Identification].ToString());
returnTable.Transaction.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Transaction].ToString()) ? "0" : row[(int)TimelineTable.Columns.Transaction].ToString());
returnTable.Subsequent.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Subsequent].ToString()) ? "0" : row[(int)TimelineTable.Columns.Subsequent].ToString());
}
return(returnTable);
}
