77b65bea-e766-4e3c-a1ed-c4c2534b6019 true D0001-SQL-11.sdt.local jazz dev master Prod true <ProgramFilesX64>\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll Amazon.Lambda.S3Events AWSSDK.Core AWSSDK.S3 Amazon Amazon.Auth.AccessControlPolicy Amazon.Auth.AccessControlPolicy.ActionIdentifiers Amazon.Internal Amazon.Lambda.S3Events Amazon.MissingTypes Amazon.Runtime Amazon.Runtime.CredentialManagement Amazon.Runtime.CredentialManagement.Internal Amazon.Runtime.EventStreams Amazon.Runtime.EventStreams.Internal Amazon.Runtime.Internal Amazon.Runtime.Internal.Auth Amazon.Runtime.Internal.Settings Amazon.Runtime.Internal.Transform Amazon.Runtime.Internal.Util Amazon.Runtime.SharedInterfaces Amazon.Runtime.SharedInterfaces.Internal Amazon.S3 Amazon.S3.Encryption Amazon.S3.Encryption.Internal Amazon.S3.Internal Amazon.S3.IO Amazon.S3.Model Amazon.S3.Model.Internal.MarshallTransformations Amazon.S3.Transfer Amazon.S3.Util Amazon.Util Amazon.Util.Internal Amazon.Util.Internal.PlatformServices Newtonsoft.Json Newtonsoft.Json.Converters Newtonsoft.Json.Linq Newtonsoft.Json.Schema Newtonsoft.Json.Serialization System System.Diagnostics System.Diagnostics.Tracing System.IO.Compression ThirdParty.BouncyCastle.Asn1 ThirdParty.BouncyCastle.Asn1.Utilities ThirdParty.BouncyCastle.Math ThirdParty.BouncyCastle.OpenSsl ThirdParty.BouncyCastle.Utilities.IO.Pem ThirdParty.Ionic.Zlib ThirdParty.Json.LitJson ThirdParty.MD5 var opcptcodefile = @"D:\Users\tlamb\Downloads\OP CTF_03232020.txt"; var opcptcodelist = File.ReadAllLines(opcptcodefile); var opcptcodes = opcptcodelist.Skip(1).Select(o => new { CaseTypeFamily = o.Split('\t')[0], CPTCode = o.Split('\t')[1].Trim() }); var newCaseTypeFamily = from op in opcptcodes join ctf in CaseTypeFamilies on op.CaseTypeFamily equals ctf.Name into joined from j in joined.DefaultIfEmpty() where j == null select op.CaseTypeFamily; var newCaseTypeFamilies = @"--Author: tlamb --Version: 2020.21 --Forward update pes set pes.MSDRGCaseTypeFamilyID = 0, pes.APRDRGCaseTypeFamilyID = 0 from [clientdss].[FactPatientEncounterSummary] pes INNER JOIN fw.DimPatientType pt on pt.PatientTypeID = pes.PatientTypeID INNER JOIN fw.DimPatientTypeRollup ptr on pt.PatientTypeRollupID = ptr.PatientTypeRollupID where ptr.Type <> 'Inpatient'; GO DELETE FROM dss.CaseTypeFamily WHERE CaseTypeFamilyGlobalID LIKE 'O-%'; GO DELETE FROM dss.CaseTypeFamilyMappingCPT; GO " + string.Join("\r\n", opcptcodes .Select(o => o.CaseTypeFamily) .Distinct().OrderBy(ctf => ctf) .Select((ctf, i) => new {i, value = $"\t( NEWID(), N'{ctf}', N'O-{(i + 1):0000}' )"}) .GroupBy(ctf => (int)ctf.i / 100, (g, d) => string.Join(",\r\n", d.Select(v => v.value))) .Select(ctf => $"INSERT INTO dss.CaseTypeFamily ( CaseTypeFamilyGUID, Name, CaseTypeFamilyGlobalID )\r\nVALUES\r\n{ctf};\r\nGO")) + @" DECLARE @CaseTypeFamilyVersionID INT; SELECT @CaseTypeFamilyVersionID = CaseTypeFamilyVersionID FROM dss.CaseTypeFamilyVersion AS ctfv WHERE GETDATE() BETWEEN ctfv.StartDate AND ctfv.EndDate; " + string.Join("\r\n", opcptcodes .Select(o => $@"INSERT INTO dss.CaseTypeFamilyMappingCPT ( CaseTypeFamilyID, CPTCode, CaseTypeFamilyVersionID ) SELECT CaseTypeFamilyID, '{o.CPTCode}', @CaseTypeFamilyVersionID FROM dss.CaseTypeFamily AS ctf WHERE ctf.Name = '{o.CaseTypeFamily}'")) + @" GO --Rollback update pes set pes.MSDRGCaseTypeFamilyID = 0, pes.APRDRGCaseTypeFamilyID = 0 from [clientdss].[FactPatientEncounterSummary] pes INNER JOIN fw.DimPatientType pt on pt.PatientTypeID = pes.PatientTypeID INNER JOIN fw.DimPatientTypeRollup ptr on pt.PatientTypeRollupID = ptr.PatientTypeRollupID where ptr.Type <> 'Inpatient'; GO DELETE FROM dss.CaseTypeFamily WHERE CaseTypeFamilyGlobalID LIKE 'O-%'; GO DELETE FROM dss.CaseTypeFamilyMappingCPT; GO "; File.WriteAllText(@"D:\Git\jazz\Migrations\CCI\2020-03-25-15-34 D-09502 OP CaseTypeFamily Definitions 2.sql", newCaseTypeFamilies); newCaseTypeFamilies.Dump(nameof(newCaseTypeFamilies)); /* var dbResults = from ctfmc in CaseTypeFamilyMappingCPTs join ctf in CaseTypeFamilies on ctfmc.CaseTypeFamilyID equals ctf.CaseTypeFamilyID join ctfv in CaseTypeFamilyVersions on ctfmc.CaseTypeFamilyVersionID equals ctfv.CaseTypeFamilyVersionID select new { ctfmc.CPTCode, ctf.CaseTypeFamilyID, ctf.Name }; var missing = from op in opcptcodes join db in dbResults on op.CPTCode equals db.CPTCode into joined from j in joined.DefaultIfEmpty() where j == null || (j != null && op.CaseTypeFamily != j.Name) select new { op.CPTCode, NewName = op.CaseTypeFamily, OldCaseTypeFamilyID = j?.CaseTypeFamilyID ?? -1, OldName = j?.Name ?? "" }; var updates = from m in missing join ctf in CaseTypeFamilies on m.NewName equals ctf.Name into joined from j in joined.DefaultIfEmpty() select new { m.CPTCode, m.NewName, NewCaseTypeFamilyID = j?.CaseTypeFamilyID ?? -1, m.OldCaseTypeFamilyID, m.OldName }; updates .Where(u => u.NewCaseTypeFamilyID != -1) .OrderBy(u => u.NewCaseTypeFamilyID) .Dump(nameof(updates)); updates .Where(u => u.NewCaseTypeFamilyID == -1) .Count().Dump("Count"); updates .Where(u => u.NewCaseTypeFamilyID == -1) .OrderBy(u => u.NewCaseTypeFamilyID) .Dump("New Case Type Family"); var oldCaseTypeFamilies = updates .Where(u => u.NewCaseTypeFamilyID != -1) .Select(u => u.NewName).Distinct().OrderBy(u => u); oldCaseTypeFamilies.Dump(nameof(oldCaseTypeFamilies)); var newCaseTypeFamilies = updates .Where(u => u.NewCaseTypeFamilyID == -1) .Select(u => u.NewName).Distinct().OrderBy(u => u); newCaseTypeFamilies.Dump(nameof(newCaseTypeFamilies)); */