///<summary>Gets all unattached payments for a new deposit slip. Excludes payments before dateStart. There is a chance payTypes might be of length 1 or even 0.</summary>
public static List <Payment> GetForDeposit(DateTime dateStart, long clinicNum, List <long> payTypes)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Payment> >(MethodBase.GetCurrentMethod(), dateStart, clinicNum, payTypes));
}
string command =
"SELECT * FROM payment "
+ "WHERE DepositNum = 0 "
+ "AND PayDate >= " + POut.Date(dateStart) + " ";
if (clinicNum != 0)
{
command += "AND ClinicNum=" + POut.Long(clinicNum);
}
for (int i = 0; i < payTypes.Count; i++)
{
if (i == 0)
{
command += " AND (";
}
else
{
command += " OR ";
}
command += "PayType=" + POut.Long(payTypes[i]);
if (i == payTypes.Count - 1)
{
command += ")";
}
}
//Order by the date on the payment, and then the incremental order of the creation of each payment (doesn't affect random primary keys).
//It was an internal complaint that checks on the same date show up in a 'random' order.
//The real fix for this issue would be to add a time column and order by it by that instead of the PK.
command += " ORDER BY PayDate,PayNum"; //Not usual pattern to order by PK
object[] parameters = { command, payTypes };
Plugins.HookAddCode(null, "Payments.GetForDeposit_beforequeryrun", parameters);
command = (string)parameters[0];
return(Crud.PaymentCrud.SelectMany(command));
}
///<summary>Gets all unattached payments for a new deposit slip. Excludes payments before dateStart. There is a chance payTypes might be of length 1 or even 0.</summary>
public static List <Payment> GetForDeposit(DateTime dateStart, long clinicNum, List <long> payTypes)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Payment> >(MethodBase.GetCurrentMethod(), dateStart, clinicNum, payTypes));
}
string command =
"SELECT * FROM payment "
+ "WHERE DepositNum = 0 "
+ "AND PayDate >= " + POut.Date(dateStart) + " ";
if (clinicNum != 0)
{
command += "AND ClinicNum=" + POut.Long(clinicNum);
}
for (int i = 0; i < payTypes.Count; i++)
{
if (i == 0)
{
command += " AND (";
}
else
{
command += " OR ";
}
command += "PayType=" + POut.Long(payTypes[i]);
if (i == payTypes.Count - 1)
{
command += ")";
}
}
command += " ORDER BY PayDate";
object[] parameters = { command, payTypes };
Plugins.HookAddCode(null, "Payments.GetForDeposit_beforequeryrun", parameters);
command = (string)parameters[0];
return(Crud.PaymentCrud.SelectMany(command));
}
///<summary>Called from FormRpOutstandingIns. Gets outstanding insurance claims. Requires all fields. provNumList may be empty (but will return null if isAllProv is false). listClinicNums may be empty. dateMin and dateMax will not be used if they are set to DateTime.MinValue() (01/01/0001). If isPreauth is true only claims of type preauth will be returned.</summary>
public static List <OutstandingInsClaim> GetOutInsClaims(List <long> listProvNums, DateTime dateFrom,
DateTime dateTo, bool isPreauth, List <long> listClinicNums, string carrierName, List <long> listUserNums, DateFilterBy dateFilterBy)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <OutstandingInsClaim> >(MethodBase.GetCurrentMethod(), listProvNums, dateFrom,
dateTo, isPreauth, listClinicNums, carrierName, listUserNums, dateFilterBy));
}
string command = @"
SELECT carrier.CarrierName,
carrier.Phone carrierPhone,
claim.ClaimType,
claim.DateService,
claim.DateSent,
claim.DateSentOrig DateOrigSent,
claim.ClaimFee,
claim.ClaimNum,
claim.ClinicNum,
insplan.GroupNum,
insplan.GroupName,
inssub.SubscriberID SubID,"
+ DbHelper.Concat("sub.LName", "', '", "sub.FName") + @" SubName,
sub.Birthdate SubDOB,
patient.FName PatFName,
patient.LName PatLName,
patient.MiddleI PatMiddleI,
patient.PatNum,
patient.Birthdate PatDOB,
definition.ItemValue DaysSuppressed,"
+ DbHelper.DtimeToDate("statusHistory.DateTimeEntry") + @" DateLog,
definition.DefNum CustomTrackingDefNum,
statusHistory.TrackingErrorDefNum ErrorCodeDefNum,
COALESCE(claimtracking.UserNum,0) UserNum
FROM carrier
INNER JOIN insplan ON insplan.CarrierNum=carrier.CarrierNum
INNER JOIN claim ON claim.PlanNum=insplan.PlanNum
AND claim.ClaimStatus='S' " ;
if (dateFrom != DateTime.MinValue)
{
if (dateFilterBy == DateFilterBy.DateSentOrig)
{
command += "AND claim.DateSentOrig >= " + POut.Date(dateFrom) + " ";
}
else if (dateFilterBy == DateFilterBy.DateSent)
{
command += "AND claim.DateSent >= " + POut.Date(dateFrom) + " ";
}
else
{
command += "AND claim.DateService >= " + POut.Date(dateFrom) + " ";
}
}
if (dateTo != DateTime.MinValue)
{
if (dateFilterBy == DateFilterBy.DateSentOrig)
{
command += "AND claim.DateSentOrig <= " + POut.Date(dateTo) + " ";
}
else if (dateFilterBy == DateFilterBy.DateSent)
{
command += "AND claim.DateSent <= " + POut.Date(dateTo) + " ";
}
else
{
command += "AND claim.DateService <= " + POut.Date(dateTo) + " ";
}
}
if (listProvNums.Count > 0)
{
command += "AND claim.ProvTreat IN (" + String.Join(",", listProvNums) + ") ";
}
if (listClinicNums.Count > 0)
{
command += "AND claim.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
if (!isPreauth)
{
command += "AND claim.ClaimType!='Preauth' ";
}
command += "LEFT JOIN claimtracking ON claimtracking.ClaimNum=claim.ClaimNum AND TrackingType='" + POut.String(ClaimTrackingType.ClaimUser.ToString()) + "' ";
command += "LEFT JOIN definition ON definition.DefNum=claim.CustomTracking "
+ "LEFT JOIN claimtracking statusHistory ON statusHistory.ClaimNum=claim.ClaimNum "
+ "AND statusHistory.TrackingDefNum=definition.DefNum "
+ "AND statusHistory.DateTimeEntry=(SELECT MAX(ct.DateTimeEntry) FROM claimtracking ct WHERE ct.ClaimNum=claim.ClaimNum AND ct.TrackingDefNum!=0) "
+ "AND statusHistory.TrackingType='" + POut.String(ClaimTrackingType.StatusHistory.ToString()) + "' "
+ "INNER JOIN patient ON patient.PatNum=claim.PatNum "
+ "LEFT JOIN inssub ON claim.InsSubNum = inssub.InsSubNum "
+ "LEFT JOIN patient sub ON inssub.Subscriber = sub.PatNum "
+ "WHERE carrier.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' ";
if (listUserNums.Count > 0)
{
command += "AND (claimtracking.UserNum IN (" + String.Join(",", listUserNums) + ") ";
if (listUserNums.Contains(0))
{
//Selected users includes 'Unassigned' so we want to allow claims without associated claimTracking rows to show.
command += " OR claimtracking.UserNum IS NULL";
}
command += ") ";
}
command += "ORDER BY carrier.CarrierName,claim.DateService,patient.LName,patient.FName,claim.ClaimType";
object[] parameters = { command };
Plugins.HookAddCode(null, "Claims.GetOutInsClaims_beforequeryrun", parameters); //Moved entire method from Claims.cs
command = (string)parameters[0];
DataTable table = Db.GetTable(command);
List <OutstandingInsClaim> listOutstandingInsClaims = table.Rows.OfType <DataRow>().Select(x => new OutstandingInsClaim(x)).ToList();
return(listOutstandingInsClaims);
}
