<Query Kind="Program">
  <Reference>&lt;ProgramFilesX64&gt;\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll</Reference>
  <Reference>&lt;RuntimeDirectory&gt;\System.Data.Entity.Design.dll</Reference>
  <Reference>&lt;RuntimeDirectory&gt;\System.Data.Entity.dll</Reference>
  <NuGetReference>LINQtoCSV</NuGetReference>
  <Namespace>LINQtoCSV</Namespace>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Newtonsoft.Json.Converters</Namespace>
  <Namespace>Newtonsoft.Json.Linq</Namespace>
  <Namespace>Newtonsoft.Json.Schema</Namespace>
  <Namespace>Newtonsoft.Json.Serialization</Namespace>
  <Namespace>System</Namespace>
  <Namespace>System.Diagnostics</Namespace>
  <Namespace>System.Diagnostics.Tracing</Namespace>
  <Namespace>System.Dynamic</Namespace>
  <Namespace>System.Globalization</Namespace>
  <Namespace>System.IO.Compression</Namespace>
  <Namespace>System.Data.Entity.Design.PluralizationServices</Namespace>
</Query>

private PluralizationService ps => PluralizationService.CreateService(new CultureInfo("en-US"));

void Main()
{
	var testExpando = new List<object>();
	var meds = new List<Inventory>();
	var inputFileDescription = new CsvFileDescription
	{
		SeparatorChar = '\t', // tab delimited
		FirstLineHasColumnNames = false, // no column names in first record
		FileCultureName = "en-US" // use formats used in The Netherlands
	};
	var cc = new CsvContext();
	var path = @"C:\Users\tlamb\Downloads\";
	var files = new[] {
		@"Outreach - Monthly Inventory Records - Inventory - March.tsv",
		@"Outreach - Monthly Inventory Records - Inventory - April.tsv",
		@"Outreach - Monthly Inventory Records - Inventory - May.tsv",
		@"Outreach - Monthly Inventory Records - Inventory - June.tsv"
		}.ToList();
	var nextmonth = new DateTime(2019, 2, 1);
	files.ForEach(inventory =>
	{
		nextmonth = nextmonth.AddMonths(1);
		var monthData = cc.Read<MyDataRow>(Path.Combine(path, inventory), inputFileDescription);
		var data = ConvertToDataRow(ConvertMyDataRow(monthData, nextmonth).ToList()).ToList();
		meds.AddRange(data);
	});

	var medBrands = meds
	.Select(m => new PropDef(m))
	.Distinct(new NameBrandComparer())
	.OrderBy(m => m.name).ThenBy(m => m.type)
	.Select((m, i) => new
	{
		Id = i + 1,
		Medication = m.name,
		Brand = m.type,
		Minimum = Math.Max(m.inventory.StartQty, 10),
		m.inventory.Dosage,
		Unit = m.inventory.container, //ps.Singularize(m.inventory.container.Trim("() ".ToCharArray())),
		Package = Regex.Replace(m.inventory.PerBox, @"[^\d]*", ""),
		Per = m.inventory.Per, //ps.Singularize(m.inventory.Unit.Trim("() ".ToCharArray())),
		Purchased = m.inventory.Purchased
	})
	.ToList();

	medBrands.Dump();
	foreach (var m in meds)
	{
		var mbx = medBrands.FirstOrDefault(mb => m.Medicine.Trim() == mb.Medication && m.MedicineBrand.Trim() == mb.Brand);
		if (mbx != null)
		{
			m.MedicineID = mbx.Id;
		}
		else
		{
			m.Dump();
		}
	};
	meds.GroupBy(m => m.MedicineID)
	.Select(grp => grp.Last())
	.OrderBy(m => m.MedicineID)
	.ThenBy(m => m.Expiry)
	.ThenBy(m => m.InvDate)
	.Dump();
}

// Define other methods and classes here
internal IEnumerable<object> ConvertMyDataRow(IEnumerable<MyDataRow> data, DateTime month)
{
	var groupData = data.SelectMany(dr => dr.ToList())
	.GroupBy(t => t.LineNbr)
	.Select(t => new { Key = t.Key - 2, Values = t.ToList().Select(v => v.Value).ToArray() });
	//groupData.Dump("groupData");
	var properties = groupData.Where(d => d.Key < 0)
	.SelectMany(d => d.Values.Select(v => Regex.Replace(v, @"[\s()]", "")
	//.Replace("LastMonthsFinalQuantity", "Start_Quantity")
	.Replace("StartingQuantityBeginningofMonth", "StartQuantity")
	.Replace("StartingQuantityBeginningofCurrentMonth", "StartQuantity")
	.Replace("FinalQuantityEndofMonth", "FinalQuantity")
	.Replace("FinalQuantityEndofCurrentMonth", "FinalQuantity")
	.Replace("MedicationTotalStartQuantity", "MedicationTotal")).ToList()).ToArray();
	//properties.Dump("properties");
	var medication = string.Empty;
	var expandoData = groupData.Where(d => d.Key >= 0)
	.Select((t, j) =>
	{
		dynamic expando = new ExpandoObject();
		AddProperty(expando, "MedicineID", j);
		AddProperty(expando, "InvDate", month);
		int i = 0;
		t.Values.ToList().ForEach(d =>
		{
			var property = properties[i]
				.Replace("Medication", "Medicine");
			if (property == "Medicine")
			{
				if (string.IsNullOrEmpty(d))
				{
					d = medication;
				}
				medication = d;
			}
			AddProperty(expando, property, d ?? string.Empty);
			i++;
		});
		return expando;
	});
	return expandoData;
}

internal IEnumerable<Inventory> ConvertToDataRow(List<object> data)
{
	var newData =
	data.Select(d => (d as IDictionary<string, object>))
	.Select(d =>
	{
		var dr = new Inventory();
		d.Keys.ToList().ForEach(k =>
		{
			var value = d[k];
			var name = Regex.Replace(k, "[^A-Za-z]", "");
			PropertyInfo prop = dr.GetType().GetProperty(name, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
			if (null != prop && prop.CanWrite)
			{
				prop.SetValue(dr, value, null);
			}
		});
		return dr;
	});
	return newData;
}

// Define other methods and classes here
internal class MyDataRow : List<DataRowItem>, IDataRow
{
}

public static void AddProperty(ExpandoObject expando, string propertyName, object propertyValue)
{
	// ExpandoObject supports IDictionary so we can extend it like this
	var expandoDict = expando as IDictionary<string, object>;
	if (expandoDict.ContainsKey(propertyName))
		expandoDict[propertyName] = propertyValue;
	else
		expandoDict.Add(propertyName, propertyValue);
}

public class Inventory
{
	internal Regex regex = new Regex(@"\(?(Tube|Bottle|Tablet|Capsule|Lancet|Vial|Kit)[ s]?\)?", RegexOptions.IgnoreCase);
	internal string PerBox { get; set; }
	internal string container => ps.Singularize(regex.Match(PerBox).Value.Trim("() ".ToCharArray()));
	internal string StartQuantity { get; set; }
	internal string Received { get; set; }
	internal string Output { get; set; }
	internal string FinalQuantity { get; set; }
	internal string Notes { get; set; }
	internal string ExpirationDate { get; set; }
	internal string LastMonthsFinalQuantity { get; set; }

	internal PluralizationService ps => PluralizationService.CreateService(new CultureInfo("en-US"));

	public int MedicineID { get; set; }
	public DateTime InvDate { get; set; }
	public string Medicine { get; set; }
	public string MedicineBrand { get; set; }
	public string Dosage { get; set; }
	public string Unit { get; set; }
	public string Pkg => container != string.Empty ? PerBox.Replace(container, "").TrimEnd() : PerBox;
	public string Per => ps.Singularize((string.IsNullOrEmpty(container) ? regex.Match(Unit).Value : container).Trim());
	public int StartQty => ParseStartQuantity(StartQuantity, LastMonthsFinalQuantity);
	public int FinalQty => ParseFinalQuantity(FinalQuantity);
	public DateTime Purchased => !Notes.StartsWith("Purchased") ? new DateTime(2018,1,1).Date
	: DateTime.Parse(Regex.Replace(Notes, @"[^\d/]*", ""));
	public DateTime Expiry => ParseExpiry();
	public string Note => !Notes.StartsWith("Purchased") ? Notes : string.Empty;
	//	public string Medication { get; set; }
	internal string BrandTotal { get; set; }
	internal string MedicationTotal { get; set; }

	public Inventory()
	{
		Medicine = string.Empty;
		Dosage = string.Empty;
		PerBox = string.Empty;
		StartQuantity = string.Empty;
		Received = string.Empty;
		Output = string.Empty;
		FinalQuantity = string.Empty;
		ExpirationDate = string.Empty;
		Notes = string.Empty;
		//Medication = string.Empty;
		MedicineBrand = string.Empty;
		BrandTotal = string.Empty;
		MedicationTotal = string.Empty;
		Unit = string.Empty;
		LastMonthsFinalQuantity = string.Empty;
	}

	internal DateTime ParseExpiry()
	{
		if (string.IsNullOrEmpty(ExpirationDate)) return DateTime.MaxValue.Date;
		DateTime dt;
		if (!DateTime.TryParseExact(ExpirationDate, @"M/yy", null, DateTimeStyles.None, out dt)) return DateTime.MaxValue.Date;
		return dt;
	}

	internal Int32 ParseFinalQuantity(string FinalQuantity)
	{
		var result = 0;
		var regex = new Regex(@"[^\d]+", RegexOptions.IgnoreCase);
		try
		{
			result = Convert.ToInt32("0" + (string.IsNullOrEmpty(FinalQuantity) || string.IsNullOrEmpty(regex.Replace(FinalQuantity, ""))
			? "" : regex.Replace(FinalQuantity, "")));
		}
		catch (Exception ex)
		{
			ex.Message.Dump();
		}
		return result;
	}

	internal Int32 ParseStartQuantity(string StartQuantity, string LastMonthsFinalQuantity)
	{
		var result = 0;
		var regex = new Regex(@"[^\d]+", RegexOptions.IgnoreCase);
		try
		{
			result = Convert.ToInt32("0" + (!string.IsNullOrEmpty(regex.Replace(StartQuantity, ""))
			? regex.Replace(StartQuantity, "")
			: !string.IsNullOrEmpty(regex.Replace(LastMonthsFinalQuantity, ""))
			? regex.Replace(LastMonthsFinalQuantity, "") : ""));
		}
		catch (Exception ex)
		{
			ex.Message.Dump();
		}
		return result;
	}
}

public class PropDef
{
	public string name { get; set; }
	public string type { get; set; }
	public Inventory inventory { get; set; }

	public PropDef(string a, System.Type b)
	{
		name = a;
		type = b.Name;
	}
	public PropDef(Inventory inv)
	{
		name = inv.Medicine == string.Empty ? inv.MedicineBrand.Trim() : inv.Medicine.Trim();
		type = inv.Medicine == string.Empty ? string.Empty : inv.MedicineBrand.Trim();
		inventory = inv;
	}
}

public class NameBrandComparer : IEqualityComparer<PropDef>
{
	public bool Equals(PropDef med1, PropDef med2)
	{
		if (med1 == null && med2 == null) return true;
		else if (med1 == null || med2 == null) return false;
		else if (med1.inventory != null && med2.inventory != null)
		{
			if (med1.name == med2.name && med1.type == med2.type && med1.inventory.Purchased == med2.inventory.Purchased) return true;
			else return false;
		}
		else if (med1.name == med2.name && med1.type == med2.type) return true;
		else
			return false;
	}

	public int GetHashCode(PropDef med)
	{
		return $"{med.name}{med.type}".GetHashCode();
	}
}