private void butRun_Click(object sender, EventArgs e)
{
this.textLog.Text = "";
string outFile = ODFileUtils.CombinePaths(textEligibilityFolder.Text, textEligibilityFile.Text);
if (File.Exists(outFile))
{
if (MessageBox.Show("The file at " + outFile + " already exists. Overwrite?", "Overwrite File?",
MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
}
string outputText = "";
string patientsIdNumberStr = "SPID#";
string householdGrossIncomeStr = "Household Gross Income";
string householdPercentOfPovertyStr = "Household % of Poverty";
string statusStr = "Eligibility Status";
string command = "";
//Locate the payment definition number for copayments of patients using the Arizona Primary Care program.
command = "SELECT DefNum FROM definition WHERE Category=" + POut.Long((int)DefCat.PaymentTypes) + " AND IsHidden=0 AND LOWER(TRIM(ItemName))='noah'";
DataTable copayDefNumTab = Reports.GetTable(command);
if (copayDefNumTab.Rows.Count != 1)
{
MessageBox.Show("You must define exactly one payment type with the name 'NOAH' before running this report. " +
"This payment type must be used on payments made by Arizona Primary Care patients.");
return;
}
long copayDefNum = PIn.Long(copayDefNumTab.Rows[0][0].ToString());
//Get the list of all Arizona Primary Care patients, based on the patients which have an insurance carrier named 'noah'
command = "SELECT DISTINCT p.PatNum FROM patplan pp,inssub,insplan i,patient p,carrier c " +
"WHERE p.PatNum=pp.PatNum AND inssub.InsSubNum=pp.InsSubNum AND inssub.PlanNum=i.PlanNum AND i.CarrierNum=c.CarrierNum " +
"AND LOWER(TRIM(c.CarrierName))='noah' AND " +
"(SELECT MAX(a.AptDateTime) FROM appointment a WHERE a.PatNum=p.PatNum AND a.AptStatus=" + ((int)ApptStatus.Complete) + ") BETWEEN " +
POut.Date(dateTimeFrom.Value) + " AND " + POut.Date(dateTimeTo.Value);
DataTable primaryCarePatients = Reports.GetTable(command);
for (int i = 0; i < primaryCarePatients.Rows.Count; i++)
{
string patNum = POut.Long(PIn.Long(primaryCarePatients.Rows[i][0].ToString()));
command = "SELECT " +
"TRIM((SELECT f.FieldValue FROM patfield f WHERE f.PatNum=p.PatNum AND " +
"LOWER(f.FieldName)=LOWER('" + patientsIdNumberStr + "') " + DbHelper.LimitAnd(1) + ")) PCIN, " + //Patient's Care ID Number
"TRIM((" + DbHelper.LimitOrderBy("SELECT cl.Description FROM appointment ap,clinic cl WHERE ap.PatNum=" + patNum + " AND " +
"ap.AptStatus=" + ((int)ApptStatus.Complete) + " AND ap.ClinicNum=cl.ClinicNum ORDER BY ap.AptDateTime DESC", 1) + ")) SiteIDNumber," +
"p.BirthDate," +
"CASE p.Position WHEN " + ((int)PatientPosition.Single) + " THEN 1 " +
"WHEN " + ((int)PatientPosition.Married) + " THEN 2 ELSE 3 END MaritalStatus," + //Marital status
//"CASE p.Race WHEN "+((int)PatientRaceOld.Asian)+" THEN 'A' WHEN "+((int)PatientRaceOld.HispanicLatino)+" THEN 'H' "+
// "WHEN "+((int)PatientRaceOld.HawaiiOrPacIsland)+" THEN 'P' WHEN "+((int)PatientRaceOld.AfricanAmerican)+" THEN 'B' "+
// "WHEN "+((int)PatientRaceOld.AmericanIndian)+" THEN 'I' WHEN "+((int)PatientRaceOld.White)+" THEN 'W' ELSE 'O' END PatRace,"+
"CONCAT(CONCAT(TRIM(p.Address),' '),TRIM(p.Address2)) HouseholdAddress," + //Patient address
"p.City HouseholdCity," + //Household residence city
"p.State HouseholdState," + //Household residence state
"p.Zip HouseholdZip," + //Household residence zip code
"TRIM((SELECT f.FieldValue FROM patfield f WHERE f.PatNum=p.PatNum AND " +
"LOWER(f.FieldName)=LOWER('" + householdGrossIncomeStr + "') " + DbHelper.LimitAnd(1) + ")) HGI, " + //Household gross income
"TRIM((SELECT f.FieldValue FROM patfield f WHERE f.PatNum=p.PatNum AND " +
"LOWER(f.FieldName)=LOWER('" + householdPercentOfPovertyStr + "') " + DbHelper.LimitAnd(1) + ")) HPP, " + //Household % of poverty
"(" + DbHelper.LimitOrderBy("SELECT a.AdjAmt FROM adjustment a WHERE a.PatNum=" + patNum + " AND a.AdjType=" +
copayDefNum + " ORDER BY AdjDate DESC", 1) + ") HSFS," + //Household sliding fee scale
"(SELECT i.DateEffective FROM insplan i,inssub,patplan pp WHERE pp.PatNum=" + patNum + " AND inssub.InsSubNum=pp.InsSubNum AND inssub.PlanNum=i.PlanNum " + DbHelper.LimitAnd(1) + ") DES," + //Date of eligibility status
"TRIM((SELECT f.FieldValue FROM patfield f WHERE f.PatNum=p.PatNum AND " +
"LOWER(f.FieldName)=LOWER('" + statusStr + "') " + DbHelper.LimitAnd(1) + ")) CareStatus " + //Status
"FROM patient p WHERE " +
"p.PatNum=" + patNum;
DataTable primaryCareReportRow = Reports.GetTable(command);
if (primaryCareReportRow.Rows.Count != 1)
{
//Either the results are ambiguous or for some reason, the patient number listed in the patfield table
//does not actually exist. In either of these cases, it makes the most sense to just skip this patient
//and continue with the rest of the reporting.
continue;
}
string outputRow = "";
string rowErrors = "";
string rowWarnings = "";
string pcin = PIn.String(primaryCareReportRow.Rows[0]["PCIN"].ToString());
if (pcin.Length < 9)
{
rowErrors += "ERROR: Incorrectly formatted patient data for patient with patnum " + patNum +
". Patient ID Number '" + pcin + "' is not at least 9 characters long." + Environment.NewLine;
}
outputRow += pcin.PadLeft(15, '0'); //Patient's ID Number
string siteId = primaryCareReportRow.Rows[0]["SiteIDNumber"].ToString();
if (siteId == "null")
{
siteId = "";
}
if (!Regex.IsMatch(siteId, "^.*_[0-9]{5}$"))
{
rowErrors += "ERROR: The clinic description for the clinic associated with the last completed appointment " +
"for the patient with a patnum of " + patNum + " must be the clinic name, follwed by a '_', followed by the 5-digit Site ID Number " +
"for the clinic. i.e. ClinicName_12345. The current clinic description is '" + siteId + "'." + Environment.NewLine;
}
else
{
siteId = siteId.Substring(siteId.Length - 5);
}
outputRow += siteId;
outputRow += PIn.Date(primaryCareReportRow.Rows[0]["Birthdate"].ToString()).ToString("MMddyyyy"); //Patient's Date of Birth
outputRow += POut.Long(PIn.Long(primaryCareReportRow.Rows[0]["MaritalStatus"].ToString()));
//outputRow+=primaryCareReportRow.Rows[0]["PatRace"].ToString();
//Gets the old patient race enum based on the PatientRace entries in the db and displays the corresponding letters.
switch (PatientRaces.GetPatientRaceOldFromPatientRaces(PIn.Long(patNum)))
{
case PatientRaceOld.Asian:
outputRow += 'A';
break;
case PatientRaceOld.HispanicLatino:
outputRow += 'H';
break;
case PatientRaceOld.HawaiiOrPacIsland:
outputRow += 'P';
break;
case PatientRaceOld.AfricanAmerican:
outputRow += 'B';
break;
case PatientRaceOld.AmericanIndian:
outputRow += 'I';
break;
case PatientRaceOld.White:
outputRow += 'W';
break;
default:
outputRow += 'O';
break;
}
//Household residence address
string householdAddress = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HouseholdAddress"].ToString()));
if (householdAddress.Length > 29)
{
string newHouseholdAddress = householdAddress.Substring(0, 29);
rowWarnings += "WARNING: Address for patient with patnum of " + patNum + " was longer than 29 characters and " +
"was truncated in the report ouput. Address was changed from '" +
householdAddress + "' to '" + newHouseholdAddress + "'" + Environment.NewLine;
householdAddress = newHouseholdAddress;
}
outputRow += householdAddress.PadRight(29, ' ');
//Household residence city
string householdCity = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HouseholdCity"].ToString()));
if (householdCity.Length > 15)
{
string newHouseholdCity = householdCity.Substring(0, 15);
rowWarnings += "WARNING: City name for patient with patnum of " + patNum + " was longer than 15 characters and " +
"was truncated in the report ouput. City name was changed from '" +
householdCity + "' to '" + newHouseholdCity + "'" + Environment.NewLine;
householdCity = newHouseholdCity;
}
outputRow += householdCity.PadRight(15, ' ');
//Household residence state
string householdState = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HouseholdState"].ToString()));
if (householdState.Length > 2)
{
string newHouseholdState = householdState.Substring(0, 2);
rowWarnings += "WARNING: State abbreviation for patient with patnum of " + patNum + " was longer than 2 characters and " +
"was truncated in the report ouput. State abbreviation was changed from '" +
householdState + "' to '" + newHouseholdState + "'" + Environment.NewLine;
householdState = newHouseholdState;
}
outputRow += householdState.PadRight(2, ' ');
//Household residence zip code
string householdZip = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HouseholdZip"].ToString()));
if (householdZip.Length > 5)
{
string newHouseholdZip = householdZip.Substring(0, 5);
rowWarnings += "WARNING: The zipcode for patient with patnum of " + patNum + " was longer than 5 characters and " +
"was truncated in the report ouput. The zipcode was changed from '" +
householdZip + "' to '" + newHouseholdZip + "'" + Environment.NewLine;
householdZip = newHouseholdZip;
}
outputRow += householdZip.PadRight(5, ' ');
//Household gross income
string householdGrossIncome = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HGI"].ToString()));
if (householdGrossIncome == "" || householdGrossIncome == "null")
{
householdGrossIncome = "0";
}
//Remove any character that is not a digit or a decimal.
string newHouseholdGrossIncome = Math.Round(Convert.ToDouble(Regex.Replace(householdGrossIncome, "[^0-9\\.]", "")), 0).ToString();
if (householdGrossIncome != newHouseholdGrossIncome)
{
rowWarnings += "WARNING: The household gross income for patient with patnum " + patNum + " contained invalid characters " +
"and was changed in the output report from '" + householdGrossIncome + "' to '" + newHouseholdGrossIncome + "'." + Environment.NewLine;
}
householdGrossIncome = newHouseholdGrossIncome.PadLeft(7, '0');
if (householdGrossIncome.Length > 7)
{
rowErrors += "ERROR: Abnormally large household gross income of '" + householdGrossIncome +
"' for patient with patnum of " + patNum + "." + Environment.NewLine;
}
outputRow += householdGrossIncome;
//Household percent of poverty
string householdPercentPoverty = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HPP"].ToString()));
if (householdPercentPoverty == "" || householdPercentPoverty == "null")
{
householdPercentPoverty = "0";
}
string newHouseholdPercentPoverty = Regex.Replace(householdPercentPoverty, "[^0-9]", ""); //Remove anything that is not a digit.
if (newHouseholdPercentPoverty != householdPercentPoverty)
{
rowWarnings += "WARNING: Household percent poverty for the patient with a patnum of " + patNum +
" had to be modified in the output report from '" + householdPercentPoverty + "' to '" + newHouseholdPercentPoverty +
"' based on output requirements." + Environment.NewLine;
}
householdPercentPoverty = newHouseholdPercentPoverty.PadLeft(3, '0');
if (householdPercentPoverty.Length > 3 || Convert.ToInt16(householdPercentPoverty) > 200)
{
rowErrors += "ERROR: Household percent poverty must be between 0 and 200 percent, but is set to '" +
householdPercentPoverty + "' for the patient with the patnum of " + patNum + Environment.NewLine;
}
outputRow += householdPercentPoverty;
string householdSlidingFeeScale = POut.String(PIn.String(primaryCareReportRow.Rows[0]["HSFS"].ToString()));
if (householdSlidingFeeScale.Length == 0)
{
householdSlidingFeeScale = "0";
}
string newHouseholdSlidingFeeScale = Regex.Replace(householdSlidingFeeScale, "[^0-9]", "");
if (newHouseholdSlidingFeeScale != householdSlidingFeeScale)
{
rowWarnings += "WARNING: The household sliding fee scale (latest NOAH copay amount) for the patient with a patnum of " + patNum +
" contains invalid characters and was changed from '" + householdSlidingFeeScale + "' to '" + newHouseholdSlidingFeeScale + "'." + Environment.NewLine;
householdSlidingFeeScale = newHouseholdSlidingFeeScale;
}
if (householdSlidingFeeScale.Length > 3 || Convert.ToInt16(householdSlidingFeeScale) > 100)
{
rowWarnings += "WARNING: The household sliding fee scale (latest NOAH copay amount) for the patient with a patnum of " + patNum +
" is '" + householdSlidingFeeScale + "', but will be reported as 100." + Environment.NewLine;
householdSlidingFeeScale = "100";
}
outputRow += householdSlidingFeeScale.PadLeft(3, '0');
string dateOfEligibilityStatusStr = primaryCareReportRow.Rows[0]["DES"].ToString();
DateTime dateOfEligibilityStatus = DateTime.MinValue;
if (dateOfEligibilityStatusStr != "" && dateOfEligibilityStatusStr != "null")
{
dateOfEligibilityStatus = PIn.Date(dateOfEligibilityStatusStr);
}
outputRow += dateOfEligibilityStatus.ToString("MMddyyyy");
//Primary care status
string primaryCareStatus = POut.String(PIn.String(primaryCareReportRow.Rows[0]["CareStatus"].ToString())).ToUpper();
if (primaryCareStatus != "A" && primaryCareStatus != "B" && primaryCareStatus != "C" && primaryCareStatus != "D")
{
rowErrors += "ERROR: The primary care status of the patient with a patnum of " + patNum + " is set to '" + primaryCareStatus +
"', but must be set to A, B, C or D. " + Environment.NewLine;
}
outputRow += primaryCareStatus;
textLog.Text += rowErrors + rowWarnings;
if (rowErrors.Length > 0)
{
continue;
}
outputText += outputRow + Environment.NewLine; //Only add the row to the output file if it is properly formatted.
}
File.WriteAllText(outFile, outputText);
MessageBox.Show("Done.");
}
