private void ProcessCodeFile(string codesFileName, String SQLPathName)
{
_goCounter = 0;
StreamWriter sw = null;
int codeCounter = 0;
try
{
sw = new StreamWriter(SQLPathName);
if (_categoryBuilder != null)
{
_categoryBuilder.SaveLookupListCategories(sw);
}
sw.WriteLine("--================================================================================================================================= ");
sw.WriteLine("--CREATING LOOKUP LIST VALUES");
sw.WriteLine("--================================================================================================================================= ");
using (XmlTextReader reader = new XmlTextReader(codesFileName))
{
CODE_STRUCT codeData = new CODE_STRUCT();
while (reader.ReadToFollowing("CODE_ENTRY"))
{
XmlReader codeReader = reader.ReadSubtree();
codeData.Categories = new List <string>();
if (codeReader.ReadToFollowing("LISTID"))
{
codeData.LISTID = (string)codeReader.ReadElementContentAs("string".GetType(), null);
}
if (codeReader.ReadToFollowing("CODE"))
{
codeData.CODE = (string)codeReader.ReadElementContentAs("string".GetType(), null);
}
if (codeReader.ReadToFollowing("CODE_WITH_DECIMAL"))
{
codeData.CODE_WITH_DECIMAL = (string)codeReader.ReadElementContentAs("string".GetType(), null);
}
if (codeReader.ReadToFollowing("DESCRIPTION"))
{
codeData.DESCRIPTION = (string)codeReader.ReadElementContentAs("string".GetType(), null);
}
while (codeReader.ReadToFollowing("CATEGORY"))
{
codeData.Categories.Add((string)codeReader.ReadElementContentAs("string".GetType(), null));
}
String ValidationMessage = codeData.ValidateData();
if (!string.IsNullOrEmpty(ValidationMessage))
{
throw (new Exception(ValidationMessage));
}
SaveCode(codeData, sw);
codeReader.Close();
codeCounter++;
}
reader.Close();
}
sw.WriteLine("GO");
sw.WriteLine(" ");
}
catch (Exception ex)
{
Console.WriteLine("Exception @ entry " + codeCounter);
throw ex;
}
finally
{
if (sw != null)
{
sw.Close();
}
}
}
private void SaveCode(CODE_STRUCT code, StreamWriter sw)
{
string c;
string cd;
string spanDiagnosisID = "10";
string spanProcedureID = "11";
//Delete the existing List Lookup Values for the given listid if not done already.
if (!_listToUpdate.Contains(code.LISTID))
{
sw.WriteLine(" ");
sw.WriteLine("DELETE FROM [LookupListValues] WHERE ListId = " + code.LISTID);
if (code.LISTID == "4")// || code.LISTID == "7" || code.LISTID == "8")
{
sw.WriteLine("DELETE FROM [LookupListValues] WHERE ListId = " + spanDiagnosisID);
}
else if (code.LISTID == "5")// || code.LISTID == "9")
{
sw.WriteLine("DELETE FROM [LookupListValues] WHERE ListId = " + spanProcedureID);
}
sw.WriteLine("GO");
sw.WriteLine(" ");
_listToUpdate.Add(code.LISTID);
}
if (code.LISTID != "2" && code.LISTID != "3")
{
try
{
c = (code.CODE != "UNDEFINED" ? code.CODE.PadRight(6) : "");
cd = (code.CODE_WITH_DECIMAL != "UNDEFINED" ? code.CODE_WITH_DECIMAL.PadRight(6) : "");
}
catch
{
c = "UNDEFINED";
cd = "UNDEFINED";
}
}
else
{
c = string.Empty;
cd = string.Empty;
}
string desc;
if (!String.IsNullOrEmpty(code.DESCRIPTION))
{
desc = code.DESCRIPTION;
}
else
{
desc = "UNDEFINED";
}
desc = desc.Replace("'", "''");
string categoryLookupCode;
if (code.LISTID == "1")
{
categoryLookupCode = cd;
cd = desc;
c = "";
}
else if (code.LISTID == "2")
{
categoryLookupCode = cd;
cd = desc;
c = "";
}
else if (code.LISTID == "6")
{
cd = c;
categoryLookupCode = c;
}
else
{
categoryLookupCode = cd;
}
/*
* Code can belong to multiple categories. One INSERT for each category.
*/
foreach (String catName in code.Categories)
{
string categoryID = (_categoryBuilder != null ? _categoryBuilder.LookupCategory(code.LISTID, catName, categoryLookupCode != null ? categoryLookupCode : "UNDEFINED") : "0");
string x = "INSERT INTO [dbo].[LookupListValues] ([ListId],[CategoryId],[ItemName],[ItemCode],[ItemCodeWithNoPeriod]) VALUES (" + code.LISTID + ", " + categoryID + ",'" + desc + "','" + cd.Replace("'", "''") + "','" + c.Replace("'", "''") + "')";
sw.WriteLine(x);
/*
* SPAN- Diagnostics(ListID=10) represents all diagnostic codes (ListID=4,7,8)
* SPAN- Procedure(ListID=11) represents all procedure codes (ListID=5,9)
* Thus whenever an entry for ListID=4,7,8 encountered, put an additional entry for ListID=10..Keep the Category ID same
* whenever an entry for ListID=5,9 encountered, put an additional entry for ListID=11..Keep the Category ID same
*/
string spanListID = string.Empty;
if (code.LISTID == "4" || code.LISTID == "7" || code.LISTID == "8")
{
spanListID = "10";
}
else if (code.LISTID == "5" || code.LISTID == "9")
{
spanListID = "11";
}
if (!string.IsNullOrEmpty(spanListID))
{
categoryID = (_categoryBuilder != null ? _categoryBuilder.LookupCategory(spanListID, catName, categoryLookupCode != null ? categoryLookupCode : "UNDEFINED") : "0");
x = " IF NOT EXISTS (SELECT 1 FROM [dbo].[LookupListValues] WHERE [ListID] = " + spanListID + " and [CategoryId] = " + categoryID + " and [ItemName] = '" + desc + "' and [ItemCode] = '" + cd.Replace("'", "''") + "') ";
x = x + " INSERT INTO [dbo].[LookupListValues] ([ListId],[CategoryId],[ItemName],[ItemCode],[ItemCodeWithNoPeriod]) VALUES (" + spanListID + ", " + categoryID + ",'" + desc + "','" + cd.Replace("'", "''") + "','" + c.Replace("'", "''") + "')";
sw.WriteLine(x);
}
}
if (_goCounter++ > 100)
{
sw.WriteLine("GO");
_goCounter = 0;
}
}