/*
* ///<summary></summary>
* public static DateTime GetClosestFirst(DateTime date){
* if(date.Day > 15){
* if(date.Month!=12){
* return new DateTime(date.Year,date.Month+1,1);
* }
* else{
* return new DateTime(date.Year+1,1,1);
* }
* }
* else{
* return new DateTime(date.Year,date.Month,1);
* }
* }*/
/*
* ///<summary></summary>
* public static void ComputeAging(int guarantor){
* DateTime asOfDate;
* if(DateTime.Today.Day > 15){
* if(DateTime.Today.Month==12){
* asOfDate=new DateTime(DateTime.Today.Year+1,1,1);
* }
* else{
* asOfDate=new DateTime(DateTime.Today.Year,DateTime.Today.Month+1,1);
* }
* }
* else{
* asOfDate=new DateTime(DateTime.Today.Year,DateTime.Today.Month,1);
* }
* ComputeAging(guarantor,asOfDate);
* Patients.ResetAging(guarantor);
* Patients.UpdateAging(guarantor,Bal[0],Bal[1],Bal[2],Bal[3],InsEst,BalTotal);
* }*/
///<summary>Computes aging for entire family. Gets all info from database.</summary>
public static void ComputeAging(int guarantor, DateTime asOfDate)
{
AsOfDate = asOfDate;
Bal = new double[4];
Bal[0] = 0; //0_30
Bal[1] = 0; //31_60
Bal[2] = 0; //61_90
Bal[3] = 0; //90plus
BalTotal = 0;
InsEst = 0;
DateValuePair[] pairs;
string wherePats = "";
ArrayList ALpatNums = new ArrayList(); //used for payplans
string command = "SELECT PatNum FROM patient WHERE guarantor = '" + POut.PInt(guarantor) + "'";
//MessageBox.Show(command);
DataTable table = General.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
ALpatNums.Add(PIn.PInt(table.Rows[i][0].ToString()));
if (i > 0)
{
wherePats += " OR";
}
wherePats += " PatNum = '" + table.Rows[i][0].ToString() + "'";
}
//REGULAR PROCEDURES:
command = "SELECT procdate,procfee FROM procedurelog"
+ " WHERE procstatus = '2'" //complete
+ " AND (" + wherePats + ")";
table = General.GetTable(command);
pairs = new DateValuePair[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
pairs[i].Date = PIn.PDate(table.Rows[i][0].ToString());
//if(PIn.PDouble(table.Rows[i][2].ToString())==-1)//not a capitation proc
pairs[i].Value = PIn.PDouble(table.Rows[i][1].ToString());
//else//capitation proc
// pairs[i].Value= PIn.PDouble(table.Rows[i][2].ToString());
}
for (int i = 0; i < pairs.Length; i++)
{
Bal[GetAgingType(pairs[i].Date)] += pairs[i].Value;
}
//POSITIVE ADJUSTMENTS:
command = "SELECT adjdate,adjamt FROM adjustment"
+ " WHERE adjamt > 0"
+ " AND (" + wherePats + ")";
table = General.GetTable(command);
pairs = new DateValuePair[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
pairs[i].Date = PIn.PDate(table.Rows[i][0].ToString());
pairs[i].Value = PIn.PDouble(table.Rows[i][1].ToString());
}
for (int i = 0; i < pairs.Length; i++)
{
Bal[GetAgingType(pairs[i].Date)] += pairs[i].Value;
}
//NEGATIVE ADJUSTMENTS:
command = "SELECT adjdate,adjamt FROM adjustment"
+ " WHERE adjamt < 0"
+ " AND (" + wherePats + ")"
+ " ORDER BY adjdate";
table = General.GetTable(command);
pairs = new DateValuePair[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
pairs[i].Date = PIn.PDate(table.Rows[i][0].ToString());
pairs[i].Value = -PIn.PDouble(table.Rows[i][1].ToString());
}
ComputePayments(pairs);
//CLAIM PAYMENTS AND CAPITATION WRITEOFFS:
//Always use DateCP rather than ProcDate to calculate the date of a claim payment
command = "SELECT datecp,inspayamt,writeoff FROM claimproc"
+ " WHERE (status = '1' " //received
+ "OR status = '4'" //or supplemental
+ "OR status = '7'" //or CapComplete
+ "OR status = '5'" //or CapClaim
+ ")"
//pending insurance is handled further down
//ins adjustments do not affect patient balance, but only insurance benefits
+ " AND (" + wherePats + ")"
+ " ORDER BY datecp";
table = General.GetTable(command);
pairs = new DateValuePair[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
pairs[i].Date = PIn.PDate(table.Rows[i][0].ToString());
pairs[i].Value = PIn.PDouble(table.Rows[i][1].ToString())
+ PIn.PDouble(table.Rows[i][2].ToString());
}
ComputePayments(pairs);
//PAYSPLITS:
command = "SELECT procdate,splitamt FROM paysplit"
+ " WHERE"
+ wherePats
+ " ORDER BY procdate";
table = General.GetTable(command);
pairs = new DateValuePair[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
pairs[i].Date = PIn.PDate(table.Rows[i][0].ToString());
pairs[i].Value = PIn.PDouble(table.Rows[i][1].ToString());
}
ComputePayments(pairs);
//PAYMENT PLANS:
string whereGuars = "";
for (int i = 0; i < ALpatNums.Count; i++)
{
if (i > 0)
{
whereGuars += " OR";
}
whereGuars += " Guarantor = '" + ((int)ALpatNums[i]).ToString() + "'";
}
command = "SELECT PatNum,Guarantor,Principal,Interest,ChargeDate FROM payplancharge"
//"SELECT currentdue,totalamount,patnum,guarantor FROM payplan"
+ " WHERE"
+ wherePats
+ " OR"
+ whereGuars
+ " ORDER BY ChargeDate";
table = General.GetTable(command);
pairs = new DateValuePair[1]; //always just one single combined entry
pairs[0].Date = DateTime.Today;
foreach (int patNum in ALpatNums)
{
for (int i = 0; i < table.Rows.Count; i++)
{
//one or both of these conditions may be met:
//if is guarantor
if (PIn.PInt(table.Rows[i][1].ToString()) == patNum)
{
if (PIn.PDate(table.Rows[i][4].ToString()) <= DateTime.Today)
{
pairs[0].Value += PIn.PDouble(table.Rows[i][2].ToString())
+ PIn.PDouble(table.Rows[i][3].ToString());
}
}
//if is patient
if (PIn.PInt(table.Rows[i][0].ToString()) == patNum)
{
pairs[0].Value -= PIn.PDouble(table.Rows[i][2].ToString());
}
}
}
if (pairs[0].Value > 0)
{
Bal[GetAgingType(pairs[0].Date)] += pairs[0].Value;
}
else if (pairs[0].Value < 0)
{
pairs[0].Value = -pairs[0].Value;
ComputePayments(pairs);
}
//CLAIM ESTIMATES
command = "SELECT inspayest FROM claimproc"
+ " WHERE status = '0'" //not received
+ " AND (" + wherePats + ")";
table = General.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
InsEst += PIn.PDouble(table.Rows[i][0].ToString());
}
//balance is sum of 4 aging periods
BalTotal = Bal[0] + Bal[1] + Bal[2] + Bal[3];
//after this, balance will NOT necessarily be the same as the sum of the 4.
//clean up negative numbers:
if (Bal[3] < 0)
{
Bal[2] += Bal[3];
Bal[3] = 0;
}
if (Bal[2] < 0)
{
Bal[1] += Bal[2];
Bal[2] = 0;
}
if (Bal[1] < 0)
{
Bal[0] += Bal[1];
Bal[1] = 0;
}
if (Bal[0] < 0)
{
Bal[0] = 0;
}
//must complete by updating patient table. Done from wherever this was called.
}
///<summary></summary>
public static void Delete(ClockEvent ce)
{
string command = "DELETE FROM clockevent WHERE ClockEventNum = " + POut.PInt(ce.ClockEventNum);
General.NonQ(command);
}
///<summary></summary>
public static void Delete(PatField pf)
{
string command = "DELETE FROM patfield WHERE PatFieldNum =" + POut.PInt(pf.PatFieldNum);
General.NonQ(command);
}
///<summary></summary>
public static void Delete(SigButDefElement element)
{
string command = "DELETE from sigbutdefelement WHERE ElementNum = '" + POut.PInt(element.ElementNum) + "'";
General.NonQ(command);
}
///<summary>Used from FormInsPlans to get a big list of many plans, organized by carrier name or by employer. Identical plans are grouped as one row.</summary>
public static DataTable GetBigList(bool byEmployer, string empName, string carrierName, string groupName, string groupNum)
{
DataTable table = new DataTable();
DataRow row;
table.Columns.Add("Address");
table.Columns.Add("City");
table.Columns.Add("CarrierName");
table.Columns.Add("ElectID");
table.Columns.Add("EmpName");
table.Columns.Add("GroupName");
table.Columns.Add("GroupNum");
table.Columns.Add("noSendElect");
table.Columns.Add("Phone");
table.Columns.Add("PlanNum");
table.Columns.Add("plans");
table.Columns.Add("State");
table.Columns.Add("Zip");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT carrier.Address,carrier.City,CarrierName,ElectID,EmpName,GroupName,GroupNum,NoSendElect,"
+ "carrier.Phone,MAX(PlanNum) onePlanNum," //for Oracle
+ "COUNT(*) plans,carrier.State,carrier.Zip, "
+ "CASE WHEN (EmpName IS NULL) THEN 1 ELSE 0 END as haveName " //for Oracle
+ "FROM insplan "
+ "LEFT JOIN employer ON employer.EmployerNum = insplan.EmployerNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "WHERE CarrierName LIKE '%" + POut.PString(carrierName) + "%' ";
if (empName != "")
{
command += "AND EmpName LIKE '%" + POut.PString(empName) + "%' ";
}
if (groupName != "")
{
command += "AND GroupName LIKE '%" + POut.PString(groupName) + "%' ";
}
if (groupNum != "")
{
command += "AND GroupNum LIKE '%" + POut.PString(groupNum) + "%' ";
}
command += "GROUP BY insplan.EmployerNum,GroupName,GroupNum,DivisionNo,"
+ "insplan.CarrierNum,insplan.IsMedical ";
if (FormChooseDatabase.DBtype == DatabaseType.Oracle)
{
command += ",carrier.Address,carrier.City,CarrierName,ElectID,EmpName,NoSendElect,carrier.Phone,carrier.State,carrier.Zip ";
}
if (byEmployer)
{
command += "ORDER BY haveName,EmpName,CarrierName";
}
else //not by employer
{
command += "ORDER BY CarrierName";
}
DataTable rawT = General.GetTable(command);
for (int i = 0; i < rawT.Rows.Count; i++)
{
row = table.NewRow();
row["Address"] = rawT.Rows[i]["Address"].ToString();
row["City"] = rawT.Rows[i]["City"].ToString();
row["CarrierName"] = rawT.Rows[i]["CarrierName"].ToString();
row["ElectID"] = rawT.Rows[i]["ElectID"].ToString();
row["EmpName"] = rawT.Rows[i]["EmpName"].ToString();
row["GroupName"] = rawT.Rows[i]["GroupName"].ToString();
row["GroupNum"] = rawT.Rows[i]["GroupNum"].ToString();
if (rawT.Rows[i]["NoSendElect"].ToString() == "0")
{
row["noSendElect"] = "";
}
else
{
row["noSendElect"] = "X";
}
row["Phone"] = rawT.Rows[i]["Phone"].ToString();
row["PlanNum"] = rawT.Rows[i]["onePlanNum"].ToString();
row["plans"] = rawT.Rows[i]["plans"].ToString();
row["State"] = rawT.Rows[i]["State"].ToString();
row["Zip"] = rawT.Rows[i]["Zip"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<summary>There are no dependencies.</summary>
public static void Delete(ProcTP proc)
{
string command = "DELETE from proctp WHERE ProcTPNum = '" + POut.PInt(proc.ProcTPNum) + "'";
General.NonQ(command);
}
///<summary>Must run UsedBy before running this.</summary>
public static void Delete(School Cur)
{
string command = "DELETE from school WHERE SchoolName = '" + POut.PString(Cur.SchoolName) + "'";
General.NonQ(command);
}
///<summary>Gets all tasks for a given taskList. But the 5 trunks don't have parents: For main trunk use date.Min and TaskListNum=0. For Repeating trunk use date.Min isRepeating and TaskListNum=0. For the 3 dated trunks, use a date and a dateType. Date and TaskListNum are mutually exclusive. Also used to get all repeating tasks for one dateType without any heirarchy: supply listNum=-1.</summary>
public static Task[] Refresh(int listNum, DateTime date, TaskDateType dateType, bool isRepeating)
{
DateTime dateFrom = DateTime.MinValue;
DateTime dateTo = DateTime.MaxValue;
string where = "";
if (date.Year > 1880)
{
//date supplied always indicates one of 3 dated trunks.
//the value of listNum is completely ignored
if (dateType == TaskDateType.Day)
{
dateFrom = date;
dateTo = date;
}
else if (dateType == TaskDateType.Week)
{
dateFrom = date.AddDays(-(int)date.DayOfWeek);
dateTo = dateFrom.AddDays(6);
}
else if (dateType == TaskDateType.Month)
{
dateFrom = new DateTime(date.Year, date.Month, 1);
dateTo = dateFrom.AddMonths(1).AddDays(-1);
}
where = "DateTask >= " + POut.PDate(dateFrom)
+ " AND DateTask <= " + POut.PDate(dateTo) + " "
+ "AND DateType=" + POut.PInt((int)dateType) + " ";
}
else //no date supplied.
{
if (listNum == 0) //main trunk or repeating trunk
{
where = "TaskListNum=" + POut.PInt(listNum)
+ " AND DateTask < '1880-01-01'"
+ " AND IsRepeating=" + POut.PBool(isRepeating) + " ";
}
else if (listNum == -1 && isRepeating) //all repeating items with no heirarchy
{
where = "IsRepeating=1 "
+ "AND DateType=" + POut.PInt((int)dateType) + " ";
}
else //any child
{
where = "TaskListNum=" + POut.PInt(listNum) + " ";
//+" AND IsRepeating="+POut.PBool(isRepeating)+" ";
}
}
string command =
"SELECT * FROM task "
+ "WHERE " + where
+ "ORDER BY DateTimeEntry";
DataTable table = General.GetTable(command);
Task[] List = new Task[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
List[i] = new Task();
List[i].TaskNum = PIn.PInt(table.Rows[i][0].ToString());
List[i].TaskListNum = PIn.PInt(table.Rows[i][1].ToString());
List[i].DateTask = PIn.PDate(table.Rows[i][2].ToString());
List[i].KeyNum = PIn.PInt(table.Rows[i][3].ToString());
List[i].Descript = PIn.PString(table.Rows[i][4].ToString());
List[i].TaskStatus = PIn.PBool(table.Rows[i][5].ToString());
List[i].IsRepeating = PIn.PBool(table.Rows[i][6].ToString());
List[i].DateType = (TaskDateType)PIn.PInt(table.Rows[i][7].ToString());
List[i].FromNum = PIn.PInt(table.Rows[i][8].ToString());
List[i].ObjectType = (TaskObjectType)PIn.PInt(table.Rows[i][9].ToString());
List[i].DateTimeEntry = PIn.PDateT(table.Rows[i][10].ToString());
}
return(List);
}
///<summary>Used in the Button edit dialog.</summary>
public static void DeleteElements(SigButDef def)
{
string command = "DELETE FROM sigbutdefelement WHERE SigButDefNum=" + POut.PInt(def.SigButDefNum);
General.NonQ(command);
}
///<summary></summary>
public static void Delete(Disease disease)
{
string command = "DELETE FROM disease WHERE DiseaseNum =" + POut.PInt(disease.DiseaseNum);
General.NonQ(command);
}
///<summary>Deletes all diseases for one patient.</summary>
public static void DeleteAllForPt(int patNum)
{
string command = "DELETE FROM disease WHERE PatNum =" + POut.PInt(patNum);
General.NonQ(command);
}
