171 lines
7.0 KiB
C#
171 lines
7.0 KiB
C#
<Query Kind="Statements">
|
|
<Connection>
|
|
<ID>77b65bea-e766-4e3c-a1ed-c4c2534b6019</ID>
|
|
<Persist>true</Persist>
|
|
<Server>D0001-SQL-11.sdt.local</Server>
|
|
<Database>jazz dev master Prod</Database>
|
|
<ShowServer>true</ShowServer>
|
|
</Connection>
|
|
<Reference><ProgramFilesX64>\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll</Reference>
|
|
<NuGetReference>Amazon.Lambda.S3Events</NuGetReference>
|
|
<NuGetReference>AWSSDK.Core</NuGetReference>
|
|
<NuGetReference>AWSSDK.S3</NuGetReference>
|
|
<Namespace>Amazon</Namespace>
|
|
<Namespace>Amazon.Auth.AccessControlPolicy</Namespace>
|
|
<Namespace>Amazon.Auth.AccessControlPolicy.ActionIdentifiers</Namespace>
|
|
<Namespace>Amazon.Internal</Namespace>
|
|
<Namespace>Amazon.Lambda.S3Events</Namespace>
|
|
<Namespace>Amazon.MissingTypes</Namespace>
|
|
<Namespace>Amazon.Runtime</Namespace>
|
|
<Namespace>Amazon.Runtime.CredentialManagement</Namespace>
|
|
<Namespace>Amazon.Runtime.CredentialManagement.Internal</Namespace>
|
|
<Namespace>Amazon.Runtime.EventStreams</Namespace>
|
|
<Namespace>Amazon.Runtime.EventStreams.Internal</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Auth</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Settings</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Transform</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Util</Namespace>
|
|
<Namespace>Amazon.Runtime.SharedInterfaces</Namespace>
|
|
<Namespace>Amazon.Runtime.SharedInterfaces.Internal</Namespace>
|
|
<Namespace>Amazon.S3</Namespace>
|
|
<Namespace>Amazon.S3.Encryption</Namespace>
|
|
<Namespace>Amazon.S3.Encryption.Internal</Namespace>
|
|
<Namespace>Amazon.S3.Internal</Namespace>
|
|
<Namespace>Amazon.S3.IO</Namespace>
|
|
<Namespace>Amazon.S3.Model</Namespace>
|
|
<Namespace>Amazon.S3.Model.Internal.MarshallTransformations</Namespace>
|
|
<Namespace>Amazon.S3.Transfer</Namespace>
|
|
<Namespace>Amazon.S3.Util</Namespace>
|
|
<Namespace>Amazon.Util</Namespace>
|
|
<Namespace>Amazon.Util.Internal</Namespace>
|
|
<Namespace>Amazon.Util.Internal.PlatformServices</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.IO.Compression</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Asn1</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Asn1.Utilities</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Math</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.OpenSsl</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Utilities.IO.Pem</Namespace>
|
|
<Namespace>ThirdParty.Ionic.Zlib</Namespace>
|
|
<Namespace>ThirdParty.Json.LitJson</Namespace>
|
|
<Namespace>ThirdParty.MD5</Namespace>
|
|
</Query>
|
|
|
|
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));
|
|
*/ |