public void SortByIP(Entity_sheet1 input, bool isAscending)
{
if (isAscending)
{
input.sheets[0].list = input.sheets[0].list.OrderBy(param => param.IP).ToList();
}
else
{
input.sheets[0].list = input.sheets[0].list.OrderByDescending(param => param.IP).ToList();
}
}
public void GroupByIPThenSortByGroupCount(Entity_sheet1 input, bool isAscending)
{
if (isAscending)
{
input.sheets[0].list = input.sheets[0].list.GroupBy(x => x.IP).OrderBy(g => g.Count())
.SelectMany(g => g).ToList();
}
else
{
input.sheets[0].list = input.sheets[0].list.GroupBy(x => x.IP).OrderByDescending(g => g.Count())
.SelectMany(g => g).ToList();
}
}
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 Export(Entity_sheet1 input, [FolderPath(AbsolutePath = true)] string path)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("IP,LicenseHash,MachineId,Version,Location,Userid,count,SerialNumber,LicenseType,ShortVer");
List <Entity_sheet1.Param> list = input.sheets[0].list;
foreach (Entity_sheet1.Param param in list)
{
sb.AppendLine(param.ToString());
}
string finalPath = path.EndsWith($"{Path.DirectorySeparatorChar}") ? path : $"{path}{Path.DirectorySeparatorChar}";
using (StreamWriter file = new StreamWriter($"{finalPath}{input.name}.csv"))
{
file.WriteLine(sb.ToString()); // "sb" is the StringBuilder
}
}
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;
}
public void FilterLocation(string value, Entity_sheet1 input, Entity_sheet1 output)
{
List <Entity_sheet1.Param> records = input.sheets[0].list.FindAll(x => x.Location.Equals(value));
output.sheets[0].list = records;
}
public Entity_sheet1 GenerateData([FilePath] string filePath)
{
string exportPath = filePath.Replace(Path.GetExtension(filePath), ".asset");
Entity_sheet1 data = (Entity_sheet1)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_sheet1));
if (data == null)
{
data = ScriptableObject.CreateInstance <Entity_sheet1> ();
AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
// data.hideFlags = HideFlags.NotEditable;
data.name = Path.GetFileNameWithoutExtension(filePath);
}
if (data.sheets.Count > 0 && data.sheets[0].list.Count > 0)
{
Debug.Log($"{exportPath} existed");
return(data);
}
data.sheets.Clear();
using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
IWorkbook book = null;
if (Path.GetExtension(filePath) == ".xls")
{
book = new HSSFWorkbook(stream);
}
else
{
book = new XSSFWorkbook(stream);
}
for (int k = 0; k < book.NumberOfSheets; k++)
{
ISheet sheet = book.GetSheetAt(k);
// }
// foreach(string sheetName in sheetNames) {
// ISheet sheet = book.GetSheet(sheetName);
if (sheet == null)
{
// Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
Entity_sheet1.Sheet s = new Entity_sheet1.Sheet();
s.name = sheet.SheetName;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = null;
Entity_sheet1.Param p = new Entity_sheet1.Param();
cell = row.GetCell(0); p.IP = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(1); p.LicenseHash = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(2); p.MachineId = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(3); p.Version = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(4); p.Location = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(5); p.Userid = (cell == null ? 0.0 : cell.NumericCellValue);
cell = row.GetCell(6); p.count = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(7); p.SerialNumber = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(8); p.LicenseType = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(9); p.ShortVer = (cell == null ? "" : cell.StringCellValue);
s.list.Add(p);
}
data.sheets.Add(s);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
Debug.Log($"Generate intermediate data: {exportPath}");
return(data);
}
