public Entity_sheet_Modified CreateSheetModifed(Entity_sheet1_Extra extra, [FolderPath] string input)
{
string exportPath = input.Replace(Path.GetFileName(input), $"{extra.name}.asset");
Entity_sheet_Modified data = (Entity_sheet_Modified)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_sheet_Modified));
if (data == null)
{
data = ScriptableObject.CreateInstance <Entity_sheet_Modified>();
AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
// data.hideFlags = HideFlags.NotEditable;
data.name = Path.GetFileNameWithoutExtension(exportPath);
}
if (data.sheets.Count > 0 && data.sheets[0].list.Count > 0)
{
Debug.Log($"{exportPath} will be overriden");
}
data.sheets.Clear();
Entity_sheet_Modified.SheetModified sheet = new Entity_sheet_Modified.SheetModified();
sheet.name = extra.name;
data.sheets.Add(sheet);
for (int i = 0; i < extra.list.Count; i++)
{
ParamModified param = new ParamModified(extra.list[i]);
data.sheets[0].list.Add(param);
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
return(data);
}
public void AutomateAllStepsAbove([FilePath] string filePath, CountryAndReference filterCountry, [FolderPath(AbsolutePath = true)] string outputFolder)
{
DateTime now = DateTime.Now;
Entity_sheet1 data = GenerateData(filePath);
Entity_sheet1 dataFiltered = ScriptableObject.CreateInstance <Entity_sheet1> ();
dataFiltered.sheets = new List <Entity_sheet1.Sheet>();
dataFiltered.sheets.Add(new Entity_sheet1.Sheet());
dataFiltered.name = data.name;
FilterLocation(filterCountry.country, data, dataFiltered);
SortByIP(dataFiltered, false);
GroupByIPThenSortByGroupCount(dataFiltered, false);
Entity_sheet1_Extra dataExtra = ScriptableObject.CreateInstance <Entity_sheet1_Extra> ();
dataExtra.name = $"{filterCountry.country}_{dataFiltered.name}";
ExpandGroupBySearchingBasedOnUserID(dataFiltered, dataExtra);
SortTheGroupsByMachineCount(dataExtra);
Entity_sheet_Modified dataModified = CreateSheetModifed(dataExtra, filePath);
dataModified.output_GroupToCSV = dataModified.output_GroupStatisticToCSV = outputFolder;
// dataModified.referenceData = filterCountry.referenceData;
dataModified.AutomateAllStep();
if (filterCountry.matchWeeks != null)
{
if (!filterCountry.matchWeeks.weeks.Contains(dataModified))
{
filterCountry.matchWeeks.weeks.Add(dataModified);
}
filterCountry.matchWeeks.referenceData = filterCountry.referenceData;
filterCountry.matchWeeks.CompareAndMatchWeeks();
filterCountry.matchWeeks.ExportData();
}
else
{
dataModified.ExportGroupToCSV();
dataModified.ExportGroupStatisticToCSV();
}
// ExportSheetExtra(dataExtra, outputFolder);
// ExportSheetModified(dataModified, outputFolder);
TimeSpan ts = DateTime.Now.Subtract(now);
Debug.Log($"Took: {ts.ToString()}");
}
public void ExportSheetExtra(Entity_sheet1_Extra input, [FolderPath(AbsolutePath = true)] string path)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("Group,Data Rows Count,Count Unique UserID,Count Unique MachineID,Count License F,Count License H,IP,LicenseHash,MachineId,Version,Location,Userid,count,SerialNumber,LicenseType,ShortVer");
List <Param_Extra> list = input.list;
foreach (Param_Extra param in list)
{
sb.AppendLine(param.ToString());
}
string finalPath = path.EndsWith($"{Path.DirectorySeparatorChar}") ? path : $"{path}{Path.DirectorySeparatorChar}";
string finalFileName = $"{finalPath}{input.name}.csv";
using (StreamWriter file = new StreamWriter(finalFileName))
{
file.WriteLine(sb.ToString()); // "sb" is the StringBuilder
}
Debug.Log($"Final output: {finalFileName}");
}
public void SortTheGroupsByMachineCount(Entity_sheet1_Extra input)
{
input.list = input.list.OrderByDescending(x => x.machines).ThenBy(x => x.group).ToList();
}
public void ExpandGroupBySearchingBasedOnUserID(Entity_sheet1 input, Entity_sheet1_Extra output)
{
List <Entity_sheet1.Param> list = input.sheets[0].list.Clone().ToList();
List <Param_Extra> retVal = new List <Param_Extra>();
List <Entity_sheet1.Param> currentGroup = new List <Entity_sheet1.Param>();
currentGroup.Add(list[0]);
list.RemoveAt(0);
int groupNumber = 1;
int groupCount;
int countUniqueMachineID;
int countUniqueUserID;
int countLicenseF;
int countLicenseH;
while (list.Count > 0)
{
// if still in the same group base on IP check
if (currentGroup[currentGroup.Count - 1].IP.Equals(list[0].IP))
{
currentGroup.Add(list[0]);
list.RemoveAt(0);
}
else // this row belongs to another group based on IP check, so now let process the previous group
{
// base on UserID, find matched rows in the full list to amend to this group
for (int i = 0; i < currentGroup.Count; i++)
{
Entity_sheet1.Param param = currentGroup[i];
List <Entity_sheet1.Param> temp = list.FindAll(x => x.Userid.Equals(param.Userid)).ToList();
for (int j = 0; j < temp.Count; j++)
{
list.Remove(temp[j]);
}
currentGroup.AddRange(temp);
}
// statistic about this group
groupCount = currentGroup.Count;
countUniqueMachineID = currentGroup.Select(x => x.MachineId).Distinct().Count();
countUniqueUserID = currentGroup.Select(x => x.Userid).Distinct().Count();
countLicenseF = currentGroup.GroupBy(x => x.MachineId)
.Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList()
.FindAll(x => x.licenseType.Equals("F")).Count;
countLicenseH = currentGroup.GroupBy(x => x.MachineId)
.Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList()
.FindAll(x => x.licenseType.Equals("H")).Count;
// let group by ip then sort by ip count within this group
currentGroup = currentGroup.GroupBy(x => x.IP).OrderByDescending(g => g.Count())
.SelectMany(g => g).ToList();
// finish current group
for (int i = 0; i < currentGroup.Count; i++)
{
Param_Extra pe = new Param_Extra(currentGroup[i]);
pe.group = groupNumber;
pe.rows = groupCount;
pe.machines = countUniqueMachineID;
pe.users = countUniqueUserID;
pe.F = countLicenseF;
pe.H = countLicenseH;
retVal.Add(pe);
}
currentGroup.Clear();
// next group
if (list.Count == 0)
{
break;
}
currentGroup.Add(list[0]);
list.RemoveAt(0);
++groupNumber;
}
}
if (currentGroup.Count > 0)
{
// statistic about this group
groupCount = currentGroup.Count;
countUniqueMachineID = currentGroup.Select(x => x.MachineId).Distinct().Count();
countUniqueUserID = currentGroup.Select(x => x.Userid).Distinct().Count();
countLicenseF = currentGroup.GroupBy(x => x.MachineId)
.Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList()
.FindAll(x => x.licenseType.Equals("F")).Count;
countLicenseH = currentGroup.GroupBy(x => x.MachineId)
.Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList()
.FindAll(x => x.licenseType.Equals("H")).Count;
// finish current group
for (int i = 0; i < currentGroup.Count; i++)
{
Param_Extra pe = new Param_Extra(currentGroup[i]);
pe.group = groupNumber;
pe.rows = groupCount;
pe.machines = countUniqueMachineID;
pe.users = countUniqueUserID;
pe.F = countLicenseF;
pe.H = countLicenseH;
retVal.Add(pe);
}
currentGroup.Clear();
}
output.list = retVal;
}
