public void CorrectGroupNameAndDescriptionBetweenSheets(Entity_sheet_Modified other, float matchingThreshold = 0.3f, bool applyCorrection = false)
{
StringBuilder log = new StringBuilder();
List <Group> namedGroups1 = groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList();
List <Group> namedGroups2 = other.groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList();
for (int i = 0; i < namedGroups1.Count; i++)
{
for (int j = 0; j < namedGroups2.Count; j++)
{
Group g1 = namedGroups1[i];
Group g2 = namedGroups2[j];
if (g1.Compare(g2, name, other.name, log, matchingThreshold) > matchingThreshold)
{
if (applyCorrection)
{
Debug.Log(log);
Group groupToChoose = g1.description.Length > g2.description.Length ? g1 : g2;
g1.name = groupToChoose.name;
g2.name = groupToChoose.name;
g1.description = groupToChoose.description;
g2.description = groupToChoose.description;
}
else
{
Debug.Log(log);
}
}
}
}
}
public void ExportSheetModified(Entity_sheet_Modified input, [FolderPath(AbsolutePath = true)] string path)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("Description,Combined Group,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 <ParamModified> list = input.sheets[0].list;
foreach (ParamModified 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 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 int MatchingNamedGroupsBetweenSheets(/*Entity_sheet_Modified other,*/ float matchingThreshold = 0.2f, bool applyLabeling = false)
{
Entity_sheet_Modified other = referenceData;
if (other == this)
{
return(0);
}
List <Group> namedGroups1 = groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList();
List <Group> namedGroups2 = other.groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList();
CrossCheck(namedGroups1, this.name, other.groups, other.name, matchingThreshold, applyLabeling);
CrossCheck(namedGroups2, other.name, groups, this.name, matchingThreshold, applyLabeling);
MergeDuplicatedGroupsByName();
return(other.MergeDuplicatedGroupsByName());
}
public Entity_sheet_Modified GenerateDataThatHasDescription([FilePath] string filePath)
{
string exportPath = filePath.Replace(Path.GetExtension(filePath), ".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(filePath);
}
if (data.sheets.Count > 0 && data.sheets[0].list.Count > 0)
{
Debug.Log($"{exportPath} will be overriden");
}
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 j = 0; j < book.NumberOfSheets; j++)
{
ISheet sheet = book.GetSheetAt(j);
if (sheet == null)
{
Debug.LogError("[QuestData] sheet not found:");
continue;
}
Entity_sheet_Modified.SheetModified s = new Entity_sheet_Modified.SheetModified();
s.name = sheet.SheetName;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = null;
ParamModified p = new ParamModified();
int k = 0;
cell = row.GetCell(k);
p.Description = (cell == null ? "" : cell.StringCellValue.Trim());
cell = row.GetCell(++k);
p.combinedGroup = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.group = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.rows = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.users = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.machines = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.F = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.H = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.IP = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.LicenseHash = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.MachineId = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.Version = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.Location = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.Userid = (cell == null ? 0.0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.count = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(++k);
p.SerialNumber = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
p.LicenseType = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(++k);
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);
}