public static GraphDataSet LoadSampleFromNormalizedDatabase(int?size)
{
var retour = new GraphDataSet();
RewriteSampleFiles(size);
using (var loader = new CsvLoaderNormalized())
{
loader.OnMessage += Loader_OnMessage;
loader.LoadReferences();
loader.LoadProviders(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\entreprise_2020_08_01_04_00.csv"));
loader.LoadLinks(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\declaration_avantage_2020_08_01_04_00.csv"));
loader.LoadLinks(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\declaration_convention_2020_08_01_04_00.csv"));
loader.LoadLinks(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\declaration_remuneration_2020_08_01_04_00.csv"));
loader.Save();
retour = loader.LinksToJson(null, size);
loader.OnMessage -= Loader_OnMessage;
}
return(retour);
}
public static GraphDataSet LoadSampleFromMetadataDatabase(int?size)
{
GraphDataSet retour = null;
RewriteSampleFiles(size);
using (var loader = new CvsLoaderMetadata())
{
loader.OnMessage += Loader_OnMessage;
loader.DbConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=AdexMeta;Integrated Security=True;";
loader.LoadReferences();
loader.LoadProviders(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\entreprise_2020_08_01_04_00.csv"));
loader.LoadLinks(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\declaration_avantage_2020_08_01_04_00.csv"));
loader.LoadLinks(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\declaration_convention_2020_08_01_04_00.csv"));
loader.LoadLinks(Path.Combine(_dataRootPath, @"ImmobilisCommander\ADEX\Data\declaration_remuneration_2020_08_01_04_00.csv"));
retour = loader.LinksToJson(null, size);
loader.OnMessage -= Loader_OnMessage;
}
return(retour);
}
public GraphDataSet LinksToJson(string txt, int?take)
{
var retour = new GraphDataSet();
var links = new List <Link>();
using (var db = new AdexContext())
{
db.Database.Log = (log) =>
{
OnMessage?.Invoke(this, new MessageEventArgs {
Level = Level.Debug, Message = log
});
};
if (!string.IsNullOrEmpty(txt))
{
if (db.Entities.Any(x => x.Reference.Contains(txt)))
{
links.AddRange(db.Links.Include("From").Include("To").Where(x => x.From.Reference.Contains(txt)));
links.AddRange(db.Links.Include("From").Include("To").Where(x => x.To.Reference.Contains(txt)));
}
}
else
{
links = db.Links.Include("From").Include("To").ToList();
}
}
var all = links.Select(x => new { id = x.From.Reference, name = x.From.Reference }).Distinct().ToList();
all.AddRange(links.Select(x => new { id = x.To.Reference, name = x.To.Reference }).Distinct());
foreach (var item in all.Where(x => !string.IsNullOrEmpty(x.id)).Take(take ?? all.Count))
{
var temp = links.Where(x => x.From.Reference == item.id).Where(x => !string.IsNullOrEmpty(x.To.Reference)).Select(x => x.To.Reference);
retour.BundlingItems.Add(new EdgeBundlingItem {
Name = item.id, Size = temp.Distinct().Count(), Imports = temp.Distinct().ToList()
});
}
return(retour);
}
private Rectangle GetPointBox(PointF pt, GraphDataSet current)
{
float size = current.SymbolSize;
Rectangle retval = new Rectangle(
(int)(pt.X - size / 2), (int)(pt.Y - size / 2),
(int)size, (int)size);
return retval;
}
public GraphDataSet CreateNewDataSet()
{
GraphDataSet retval = new GraphDataSet();
mDataSetCreatedCount++;
retval.Name = "Dataset " + mDataSetCreatedCount.ToString();
retval.Color = mColors[mDataSets.Count % mColors.Length];
mDataSets.Add(retval);
OnCreateDataSet(retval);
return retval;
}
private void OnCreateDataSet(GraphDataSet dataSet)
{
if (AddedDataset != null)
{
GraphDataSetEventArgs e = new GraphDataSetEventArgs();
e.Graph = this;
e.GraphDataSet = dataSet;
AddedDataset(this, e);
}
}
public GraphDataSet LinksToJson(string txt, int?take)
{
var retour = new GraphDataSet()
{
ForceDirectedData = new ForceDirectedData()
};
string query =
@"select a.Reference as Company, am.Value as Designation, p.Reference as Beneficiary, pm1.Value + ' ' + pm2.Value as SocialDenomination, b.NumberOfLinks, b.Amount
from
(
select
l.From_Id, l.To_Id, count(*) as NumberOfLinks, SUM(CONVERT(decimal, lm.Value)) as Amount
from
Links l
inner join Metadatas lm on lm.Entity_Id = l.Id
inner join Members lmb on lmb.Id = lm.Member_Id
where
lmb.Name like '%_montant_ttc'
group by
l.From_Id, l.To_Id
having
SUM(CONVERT(decimal, lm.Value)) > 30000
) b
inner join Entities a on a.Id = b.From_Id
inner join Metadatas am on am.Entity_Id = a.Id
inner join Members amb on amb.Id = am.Member_Id and amb.Name = 'denomination_sociale'
inner join Entities p on p.Id = b.To_Id
inner join Metadatas pm1 on pm1.Entity_Id = p.Id
inner join Members pmb1 on pmb1.Id = pm1.Member_Id and pmb1.Name = 'benef_nom'
inner join Metadatas pm2 on pm2.Entity_Id = p.Id
inner join Members pmb2 on pmb2.Id = pm2.Member_Id and pmb2.Name = 'benef_prenom'" ;
var result = new List <QueryResult>();
using (var con = new SqlConnection(DbConnectionString))
{
con.Open();
using (var cm = new SqlCommand(query, con))
{
cm.CommandTimeout = 3600;
cm.CommandType = System.Data.CommandType.Text;
using (var reader = cm.ExecuteReader())
{
while (reader.Read())
{
var obj = new QueryResult
{
Company = (string)reader["Company"]
,
Beneficiary = (string)reader["Beneficiary"]
,
Designation = (string)reader["Designation"]
,
SocialDenomination = (string)reader["SocialDenomination"]
,
NumberOfLinks = (int)reader["NumberOfLinks"]
,
Amount = (decimal)reader["Amount"]
};
result.Add(obj);
}
}
}
//result = con.Query<QueryResult>(query);
}
//retour.BundlingItems.AddRange(result.Select(x => x.Company).Distinct().Select(x => new EdgeBundlingItem { Name = x, Imports = new List<string>() }));
//retour.BundlingItems.AddRange(result.Select(x => x.Beneficiary).Distinct().Select(x => new EdgeBundlingItem { Name = x, Imports = new List<string>() }));
foreach (var grp in result.GroupBy(x => new { company = x.Company, designation = x.Designation }))
{
retour.ForceDirectedData.ForceDirectedNodes.Add(new ForceDirectedNodeItem {
Id = grp.Key.company, Name = grp.Key.designation, Amount = grp.Sum(s => s.Amount), Group = "1"
});
}
foreach (var grp in result.GroupBy(x => new { benef = x.Beneficiary, denomination = x.SocialDenomination }))
{
retour.ForceDirectedData.ForceDirectedNodes.Add(new ForceDirectedNodeItem {
Id = grp.Key.benef, Name = grp.Key.denomination, Amount = grp.Sum(s => s.Amount), Group = "2"
});
}
retour.ForceDirectedData.ForceDirectedLinks.AddRange(result.Select(a => new ForceDirectedLinkItem
{
Source = a.Company,
Target = a.Beneficiary,
NbLinks = Convert.ToInt32(a.NumberOfLinks),
Amount = Convert.ToInt32(a.Amount),
Size = 1
}));
return(retour);
}
void WriteDataSetHeader(int index, GraphDataSet set)
{
string indexString = "s" + index.ToString();
string colorString = ColorIndex(set.Color).ToString();
int symbol = 0;
if (set.DrawPoints)
{
switch (set.PointType)
{
case GraphDataSet.SymbolType.Square: symbol = 2; break;
case GraphDataSet.SymbolType.Circle: symbol = 1; break;
case GraphDataSet.SymbolType.Point: symbol = 3; break;
}
}
int lineType = 0;
if (set.DrawLine)
{
switch (set.LineStyle)
{
case GraphDataSet.LineType.Solid: lineType = 1; break;
case GraphDataSet.LineType.Dashed: lineType = 2; break;
case GraphDataSet.LineType.Dotted: lineType = 3; break;
}
}
string header =
"@ " + indexString + " hidden false\n" +
"@ " + indexString + " type xy\n" +
"@ " + indexString + " symbol " + symbol + "\n" +
"@ " + indexString + " symbol size 1.000000\n" +
"@ " + indexString + " symbol color " + colorString + "\n" +
"@ " + indexString + " symbol pattern 1\n" +
"@ " + indexString + " symbol fill color 1\n" +
"@ " + indexString + " symbol fill pattern 0\n" +
"@ " + indexString + " symbol linewidth " + set.LineWeight + "\n" +
"@ " + indexString + " symbol linestyle 1\n" +
"@ " + indexString + " symbol char 65\n" +
"@ " + indexString + " symbol char font 0\n" +
"@ " + indexString + " symbol skip 0\n" +
"@ " + indexString + " line type " + lineType + "\n" +
"@ " + indexString + " line linestyle 1\n" +
"@ " + indexString + " line linewidth " + set.LineWeight + "\n" +
"@ " + indexString + " line color " + colorString + "\n" +
"@ " + indexString + " line pattern 1\n" +
"@ " + indexString + " baseline type 0\n" +
"@ " + indexString + " baseline off\n" +
"@ " + indexString + " dropline off\n" +
"@ " + indexString + " fill type 0\n" +
"@ " + indexString + " fill rule 0\n" +
"@ " + indexString + " fill color " + colorString + "\n" +
"@ " + indexString + " fill pattern 1\n" +
"@ " + indexString + " avalue off\n" +
"@ " + indexString + " avalue type 2\n" +
"@ " + indexString + " avalue char size 1.000000\n" +
"@ " + indexString + " avalue font 0\n" +
"@ " + indexString + " avalue color " + colorString + "\n" +
"@ " + indexString + " avalue rot 0\n" +
"@ " + indexString + " avalue format general\n" +
"@ " + indexString + " avalue prec 3\n" +
"@ " + indexString + " avalue prepend \"\"\n" +
"@ " + indexString + " avalue append \"\"\n" +
"@ " + indexString + " avalue offset 0.000000 , 0.000000\n" +
"@ " + indexString + " errorbar on\n" +
"@ " + indexString + " errorbar place both\n" +
"@ " + indexString + " errorbar color " + colorString + "\n" +
"@ " + indexString + " errorbar pattern 1\n" +
"@ " + indexString + " errorbar size 1.000000\n" +
"@ " + indexString + " errorbar linewidth 1.0\n" +
"@ " + indexString + " errorbar linestyle 1\n" +
"@ " + indexString + " errorbar riser linewidth 1.0\n" +
"@ " + indexString + " errorbar riser linestyle 1\n" +
"@ " + indexString + " errorbar riser clip off\n" +
"@ " + indexString + " errorbar riser clip length 0.100000\n" +
"@ " + indexString + " comment \"\"\n" +
"@ " + indexString + " legend \"\"\n"
;
mFile.Write(header);
}
void WriteDataSet(int index, GraphDataSet set)
{
string indexString = "S" + index.ToString();
mFile.Write("@target G0.{0}\n@type xy\n", indexString);
for (int i = 0; i < set.Count; i++)
{
if (float.IsNaN(set[i].X) || float.IsNaN(set[i].Y))
continue;
if (float.IsInfinity(set[i].X) || float.IsInfinity(set[i].Y))
continue;
mFile.Write("{0} {1}\n", set[i].X, set[i].Y);
}
mFile.Write("&\n");
}
