<Query Kind="Program">
  <Connection>
    <ID>580b3b8d-cc02-4a68-8fbd-d1ec374a25f2</ID>
    <Persist>true</Persist>
    <Server>D0001-sql-11.sdt.local</Server>
    <Database>jazz dev master Prod</Database>
    <ShowServer>true</ShowServer>
  </Connection>
  <NuGetReference>LinqToCsv</NuGetReference>
  <Namespace>LINQtoCSV</Namespace>
</Query>

void Main()
{
	var path = @"D:\Users\tlamb\Downloads";
	var fileName = @"FY2020 New Codes_04242020_Final.csv";

	var filePath = Path.Combine(path, fileName);
	if (!File.Exists(filePath)) return;

	//var data = File.ReadAllLines(filePath);
	//data.Dump();

	var inputFileDescription = new CsvFileDescription
	{
		SeparatorChar = ',',
		FirstLineHasColumnNames = true
	};

	var ctfVersion = CaseTypeFamilyVersions.Any(x => x.Name.Equals("2019 Standard Mapping"))
	? (from x in CaseTypeFamilyVersions
	   where x.Name.Equals("2019 Standard Mappings")
	   select x.CaseTypeFamilyVersionID).FirstOrDefault()
	   : 2;
	//ctfVersion.Dump();

	var cc = new CsvContext();
	var dat = from x in cc.Read<CTFDefinitions>(filePath, inputFileDescription)
			  select new
			  {
				  ctfVersion,
				  x.Column1,
				  x.MS_DRG_Code,
				  x.MDC,
				  x.ICD10Code,
				  x.ICD10_Code_Description,
				  x.Code_Type_1DX_3PX,
				  Exclusion_Type = (x.Exclusion_Type == "N"),
				  x.DRGs_to_CTFs,
				  ICD_Needs_Review = (x.ICD_Needs_Review != "#N/A"),
				  x.CTF_Type,
				  Same_Type = (x.Same_Type == "Yes"),
				  CTF_To_Assign_Existing = (x.CTF_To_Assign_Existing ?? "").Replace("&", "And"),
				  New_CTF = ((x.New_CTF ?? "No") == "Yes")
			  };
	//dat.Dump();

	var xCTF_To_Assign = dat.Select(d => d.CTF_To_Assign_Existing).Distinct();
	var ctf = CaseTypeFamilies
	.Where(c => xCTF_To_Assign.Contains(c.Name))
	.GroupBy(c => c.Name, (g, d) => new { name = g, ids = d.Select(x => x.CaseTypeFamilyID).ToList() })
	.ToDictionary(c => c.name, c => c.ids);
	//ctf.Aggregate("", (x, y) => $"{x}\r\nUNION\r\nSelect Name, Case Name when '{y.Key}' THEN 1 ELSE 0 END isMatch from dss.CaseTypeFamily where CaseTypeFamilyID = {y.Value.First()}").Substring(9)	.Dump();

	var msDrg = dat
	.Where(d => !string.IsNullOrEmpty(d.ICD10Code))
	.ToList()
	.ConvertAll(d =>
	{
		var caseTypeFamilyID = ctf.TryGetValue(d.CTF_To_Assign_Existing, out var value)
		? value.FirstOrDefault()
		: 0;
		return new
		{
			CaseTypeFamilyID = caseTypeFamilyID,
			CTFExisting = d.CTF_To_Assign_Existing,
			SkipICD = false,
			ICD10Code = d.ICD10Code.Replace(".",""),
			MSDRGCode = d.MS_DRG_Code,
			CaseTypeFamilyVersionID = d.ctfVersion
		};
	})
	//.Where(d => d.ICD10Code.Contains("."))
	.Aggregate("", (x, y) => $@"{x}

INSERT INTO dss.CaseTypeFamilyMappingICD10 
( CaseTypeFamilyID, SkipICD, ICD10Code, MSDRGCode, CaseTypeFamilyVersionID )
SELECT ctf.CaseTypeFamilyID,{(y.SkipICD ? 1 : 0)},'{y.ICD10Code}','{y.MSDRGCode}',{y.CaseTypeFamilyVersionID}
FROM dss.CaseTypeFamily ctf 
WHERE ctf.Name = '{y.CTFExisting}'")
	.Substring(4);
	msDrg.Dump();
}

// Define other methods and classes here
public class CTFDefinitions
{
	public string Column1 { get; set; }
	public string MS_DRG_Code { get; set; }
	public string MDC { get; set; }
	public string ICD10Code { get; set; }
	public string ICD10_Code_Description { get; set; }
	public string Code_Type_1DX_3PX { get; set; }
	public string Exclusion_Type { get; set; }
	public string DRGs_to_CTFs { get; set; }
	public string ICD_Needs_Review { get; set; }
	public string CTF_Type { get; set; }
	public string Same_Type { get; set; }
	public string CTF_To_Assign_Existing { get; set; }
	public string New_CTF { get; set; }
}