public List <BillsDepartment> GetBillsDepartments(Patient patient, BillingFlag flag)
{
List <BillsDepartment> depts = new List <BillsDepartment>();
string query = "WHERE vst_patient=" + patient.Id;
if (flag != null)
{
query += " AND bl_flag=" + flag.Id;
}
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("SELECT TOP(6) dpt_idnt, dpt_name, ISNULL(bl_count,0)bl_count, ISNULL(bl_amt,0)bl_amt FROM Departments LEFT OUTER JOIN (SELECT bl_dept, COUNT(*) bl_count, SUM(bl_amount-bl_paid-bl_waiver) bl_amt FROM Bills INNER JOIN Visit ON vst_idnt=bl_visit " + query + " GROUP BY bl_dept) As Foo ON bl_dept=dpt_idnt ORDER BY bl_amt DESC, dpt_name");
if (dr.HasRows)
{
while (dr.Read())
{
depts.Add(new BillsDepartment {
Department = new Department {
Id = Convert.ToInt64(dr[0]),
Name = dr[1].ToString()
},
Count = Convert.ToDouble(dr[2]),
Total = Convert.ToDouble(dr[3]),
});
}
}
return(depts);
}
public JsonResult GetBillingCashierQueue(string start, string stop, string flag = "")
{
BillingFlag bf = null;
if (!string.IsNullOrEmpty(flag) && long.TryParse(flag, out long dblFlag))
{
bf = new BillingFlag {
Id = dblFlag
};
}
return(Json(IFinanceService.GetBillingCashierQueue(DateTime.ParseExact(start, "dd/MM/yyyy", CultureInfo.InvariantCulture), DateTime.ParseExact(stop, "dd/MM/yyyy", CultureInfo.InvariantCulture), bf)));
}
public List <Bills> GetBillingCashierQueue(DateTime start, DateTime stop, BillingFlag flag)
{
List <Bills> queue = new List <Bills>();
string query = "WHERE CAST(bl_date AS DATE) BETWEEN '" + start + "' AND '" + stop + "'";
if (flag != null)
{
query += " AND bl_flag=" + flag.Id;
}
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("SELECT bl_idnt, bl_amts, bl_date, bl_flag, bf_flag, pt_idnt, pt_uuid, pt_identifier, ps_idnt, ps_name, ps_gender, ps_dob FROM vBillingCashierQueue " + query + " ORDER BY bl_idnt");
if (dr.HasRows)
{
while (dr.Read())
{
Bills bill = new Bills {
Id = Convert.ToInt64(dr[0]),
Amount = Convert.ToDouble(dr[1]),
Date = Convert.ToDateTime(dr[2]).ToString("dd/MM/yyyy"),
CreatedOn = Convert.ToDateTime(dr[2]),
Flag = new BillingFlag {
Id = Convert.ToInt64(dr[3]),
Name = dr[4].ToString(),
},
Visit = new Visit {
Patient = new Patient {
Id = Convert.ToInt64(dr[5]),
Uuid = dr[6].ToString(),
Identifier = dr[7].ToString(),
Person = new Person {
Id = Convert.ToInt64(dr[8]),
Name = dr[9].ToString(),
Gender = dr[10].ToString(),
DateOfBirth = Convert.ToDateTime(dr[11])
}
}
}
};
bill.Visit.Patient.GetAge();
queue.Add(bill);
}
}
return(queue);
}
public List <Bills> GetBills(Patient patient, Visit visit, DateTime?start, DateTime?stop, BillingFlag flag, bool otherFlags = false)
{
List <Bills> bills = new List <Bills>();
string query = "";
if (patient != null)
{
query = "WHERE pt_idnt=" + patient.Id;
}
if (start.HasValue && stop.HasValue)
{
query += (string.IsNullOrEmpty(query) ? "WHERE " : " AND ") + "CAST(bl_created_on AS DATE) BETWEEN '" + start + "' AND '" + stop + "'";
}
if (start.HasValue)
{
query += (string.IsNullOrEmpty(query) ? "WHERE " : " AND ") + "CAST(bl_created_on AS DATE)='" + start + "'";
}
if (stop.HasValue)
{
query += (string.IsNullOrEmpty(query) ? "WHERE " : " AND ") + "CAST(bl_created_on AS DATE)='" + stop + "'";
}
if (otherFlags)
{
query += (string.IsNullOrEmpty(query) ? "WHERE " : " AND ") + "bl_flag<>0";
}
else
{
if (flag != null)
{
query += (string.IsNullOrEmpty(query) ? "WHERE " : " AND ") + "bl_flag=" + flag.Id;
}
}
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("SELECT bl_idnt, bl_amount, bl_paid, bl_waiver, bl_waiver_reason, bl_created_on, bl_processed_on, bl_waived_on, bl_notes, bf_idnt, bf_flag, dpt_idnt, dpt_name, vst_idnt, vst_type, cl_idnt, cl_code, cl_name, pt_idnt, pt_uuid, pt_identifier, pt_notes, ps_idnt, ps_name, ps_gender, ps_dob, ps_notes, bl_created_by, cb.usr_name, bl_processed_by, pb.usr_name, bl_waived_by, wb.usr_name FROM Bills INNER JOIN BillsFlag ON bl_flag=bf_idnt INNER JOIN Departments ON bl_dept=dpt_idnt INNER JOIN Visit ON bl_visit=vst_idnt INNER JOIN ClientCodes ON cl_idnt=vst_client_code INNER JOIN Patient ON vst_patient=pt_idnt INNER JOIN Person ON pt_person=ps_idnt INNER JOIN Users cb ON cb.usr_idnt=bl_created_by LEFT OUTER JOIN Users pb ON pb.usr_idnt=bl_processed_by LEFT OUTER JOIN Users wb ON wb.usr_idnt=bl_waived_by " + query + " ORDER BY bl_created_on DESC, bl_idnt");
if (dr.HasRows)
{
while (dr.Read())
{
Bills bill = new Bills {
Id = Convert.ToInt64(dr[0]),
Amount = Convert.ToDouble(dr[1]),
Paid = Convert.ToDouble(dr[2]),
Waiver = Convert.ToDouble(dr[3]),
WaiverReason = dr[4].ToString(),
CreatedOn = Convert.ToDateTime(dr[5]),
ProcessedOn = dr[6].ToString() == "" ? (DateTime?)null : Convert.ToDateTime(dr[6]),
WaivedOn = dr[7].ToString() == "" ? (DateTime?)null : Convert.ToDateTime(dr[7]),
Notes = dr[8].ToString(),
Flag = new BillingFlag {
Id = Convert.ToInt64(dr[9]),
Name = dr[10].ToString()
},
Department = new Department {
Id = Convert.ToInt64(dr[11]),
Name = dr[12].ToString()
},
Visit = new Visit {
Id = Convert.ToInt64(dr[13]),
Type = new VisitType {
Id = Convert.ToInt64(dr[14]),
},
ClientCode = new ClientCode {
Id = Convert.ToInt64(dr[15]),
Code = dr[16].ToString(),
Name = dr[17].ToString(),
},
Patient = new Patient {
Id = Convert.ToInt64(dr[18]),
Uuid = dr[19].ToString(),
Identifier = dr[20].ToString(),
Notes = dr[21].ToString(),
Person = new Person {
Id = Convert.ToInt64(dr[22]),
Name = dr[23].ToString(),
Gender = dr[24].ToString(),
DateOfBirth = Convert.ToDateTime(dr[25]),
Notes = dr[26].ToString()
}
}
},
CreatedBy = new Users {
Id = Convert.ToInt64(dr[27]),
Name = dr[28].ToString()
},
ProcessedBy = new Users {
Id = Convert.ToInt64(dr[29]),
Name = dr[30].ToString()
},
WaivedBy = new Users {
Id = Convert.ToInt64(dr[31]),
Name = dr[32].ToString()
}
};
bill.Balance = bill.Amount - bill.Paid - bill.Waiver;
bill.Date = bill.CreatedOn.ToString("dd/MM/yyyy");
bill.Visit.Patient.GetAge();
bills.Add(bill);
}
}
return(bills);
}
