public List <FuelPurchasesLedger> GetStocksPurchasesLedgers(Stations station, DateTime start, DateTime stop, string filter)
{
var ledger = new List <FuelPurchasesLedger>();
SqlServerConnection conn = new SqlServerConnection();
string q = conn.GetQueryString(filter, "CAST(qty*price AS NVARCHAR)+'-'+Category+'-'+Items+'-'+SuppInv+'-'+ISNULL(Names,'CASH')", "date_ BETWEEN '" + start.Date + "' AND '" + stop.Date + "'");
if (station != null)
{
q += " AND st=" + station.Id;
}
SqlDataReader dr = conn.SqlServerConnect("SELECT id, date_, qty, ISNULL(price,0)price, tax, Category, Items, SuppInv, Supp, Names, st_idnt, st_code, st_name FROM vStocksLedger INNER JOIN Stations ON st=st_idnt " + q + " ORDER BY date_, SuppInv, Items");
if (dr.HasRows)
{
while (dr.Read())
{
FuelPurchasesLedger item = new FuelPurchasesLedger {
Id = Convert.ToInt64(dr[0]),
Date = Convert.ToDateTime(dr[1]).ToString("dd-MMM"),
Ltrs = Convert.ToDouble(dr[2]),
Price = Convert.ToDouble(dr[3]),
Rate = Convert.ToDouble(dr[4]),
Category = dr[5].ToString(),
Description = dr[6].ToString(),
Invoice = dr[7].ToString(),
Supplier = new Suppliers {
Id = Convert.ToInt64(dr[8]),
Name = dr[9].ToString()
},
Station = new Stations {
Id = Convert.ToInt64(dr[10]),
Code = dr[11].ToString(),
Name = dr[12].ToString()
}
};
item.Total = item.Ltrs * item.Price;
if (item.Rate.Equals(0))
{
item.Zero = item.Total;
}
else
{
item.Vats = (item.Rate / (item.Rate * 100)) * item.Total;
}
item.Excl = item.Total - item.Vats - item.Zero;
ledger.Add(item);
}
}
return(ledger);
}
public List <Purchases> GetPurchases(DateTime date1x, DateTime date2x, string category, Stations station, Suppliers supplier, string filter = "")
{
List <Purchases> purchases = new List <Purchases>();
SqlServerConnection conn = new SqlServerConnection();
string query = conn.GetQueryString(filter, "CAST(PurNum AS NVARCHAR)+'-'+ISNULL(Names,'CASH PURCHASE')+'-'+SuppInv+'-'+Category+'-'+CAST(qty*price AS NVARCHAR)+'-'+Names+'-'+st_name+'-'+st_synonym", "Date BETWEEN '" + date1x.Date + "' AND '" + date2x.Date + "'");
if (!string.IsNullOrEmpty(category))
{
query += " AND Category='" + category + "'";
}
if (!(station is null))
{
query += " AND st_idnt=" + station.Id;
}
if (!(supplier is null))
{
query += " AND Suppid=" + supplier.Id;
}
SqlDataReader dr = conn.SqlServerConnect("SELECT PurNum, Date, Lpo, SuppInv, MAX(Category)Catg, SUM(qty*price) Amts, Supp, ISNULL(Names,'CASH PURCHASE')Names, st_idnt, st_code, st_name, st_synonym FROM vPurchasesAll INNER JOIN Stations ON Stns=st_idnt LEFT OUTER JOIN vSuppliers ON Suppid=Supp AND Stn=Stns " + query + " GROUP BY PurNum, Date, Lpo, SuppInv, Supp, Names, st_idnt, st_code, st_name, st_order, st_synonym ORDER BY Date, SuppInv, st_order, PurNum");
if (dr.HasRows)
{
while (dr.Read())
{
purchases.Add(new Purchases {
Id = Convert.ToInt64(dr[0]),
Date = Convert.ToDateTime(dr[1]),
DateString = Convert.ToDateTime(dr[1]).ToString("dd/MM/yyyy"),
Lpo = dr[2].ToString(),
Invoice = dr[3].ToString(),
Category = dr[4].ToString(),
Amount = Convert.ToDouble(dr[5]),
Supplier = new Suppliers {
Id = Convert.ToInt64(dr[6]),
Name = dr[7].ToString()
},
Station = new Stations {
Id = Convert.ToInt64(dr[8]),
Code = dr[9].ToString(),
Name = dr[10].ToString(),
Synonym = dr[11].ToString(),
}
});
}
}
return(purchases);
}
public List <FacilityDrug> GetInventoryDrugs(Facility facility, DrugCategory category, string filter = "")
{
List <FacilityDrug> FacilityDrug = new List <FacilityDrug>();
SqlServerConnection conn = new SqlServerConnection();
string query = conn.GetQueryString(filter, "drg_initial+'-'+drg_name+'-'+dc_name+'-'+df_name+'-'+df_dosage", "fd_idnt>0");
if (!(facility is null))
{
query += " AND fd_facility=" + facility.Id;
}
if (!(category is null))
{
query += " AND dc_idnt=" + category.Id;
}
SqlDataReader dr = conn.SqlServerConnect("SELECT fd_idnt, fd_reorder, ISNULL(avls,0)fd_avls, drg_idnt, drg_initial, drg_name, dc_idnt, dc_name, df_idnt, df_name, df_dosage FROM InventoryFacilityDrug INNER JOIN InventoryDrug ON fd_drug=drg_idnt INNER JOIN InventoryDrugCategory ON drg_category=dc_idnt INNER JOIN InventoryDrugFormulation ON drg_formulation=df_idnt LEFT OUTER JOIN vDrugsSummary ON fac=fd_facility AND drg=fd_drug " + query + " ORDER BY drg_name");
if (dr.HasRows)
{
while (dr.Read())
{
FacilityDrug.Add(new FacilityDrug {
Id = Convert.ToInt64(dr[0]),
Reorder = Convert.ToInt64(dr[1]),
Available = Convert.ToInt64(dr[2]),
Drug = new Drug {
Id = Convert.ToInt64(dr[3]),
Initial = dr[4].ToString(),
Name = dr[5].ToString(),
Category = new DrugCategory {
Id = Convert.ToInt64(dr[6]),
Name = dr[7].ToString()
},
Formulation = new DrugFormulation {
Id = Convert.ToInt64(dr[8]),
Name = dr[9].ToString(),
Dosage = dr[10].ToString(),
}
},
});
}
}
return(FacilityDrug);
}
public List <Facility> GetFacilities(string filter = "", string agency = "", string region = "", bool includeVoided = true)
{
List <Facility> facilities = new List <Facility>();
SqlServerConnection conn = new SqlServerConnection();
string query = conn.GetQueryString(filter, "fc_status+'-'+fc_prefix+'-'+fc_name+'-'+fc_description+'-'+rg_name+'-'+ag_name", "fc_idnt>0");
if (!string.IsNullOrWhiteSpace(agency))
{
query += " AND fc_agency IN (" + agency + ")";
}
if (!string.IsNullOrWhiteSpace(region))
{
query += " AND fc_region IN (" + region + ")";
}
if (!includeVoided)
{
query += " AND fc_void=0";
}
SqlDataReader dr = conn.SqlServerConnect("SELECT fc_idnt, fc_void, fc_status, fc_prefix, fc_name, fc_description, ISNULL(pp_last_record,'1900-01-01') fc_last, ISNULL(pp_count,0) fc_count, rg_idnt, rg_name, ag_idnt, ag_name FROM Facilities INNER JOIN Agency ON fc_agency=ag_idnt INNER JOIN Regions ON rg_idnt=fc_region LEFT OUTER JOIN vFacilitiesCount ON fc_idnt=pp_facility " + query + " ORDER BY rg_idnt, fc_prefix, fc_name");
if (dr.HasRows)
{
while (dr.Read())
{
facilities.Add(new Facility {
Id = Convert.ToInt64(dr[0]),
Void = Convert.ToBoolean(dr[1]),
Status = dr[2].ToString(),
Prefix = dr[3].ToString(),
Name = dr[4].ToString(),
Description = dr[5].ToString(),
LastRecord = Convert.ToDateTime(dr[6]),
Count = Convert.ToInt32(dr[7]),
Region = new Region(Convert.ToInt64(dr[8]), dr[9].ToString()),
Agency = new Agency(Convert.ToInt64(dr[10]), dr[11].ToString()),
});
}
}
return(facilities);
}
public List <FuelLedgerSummary> GetFuelPurchasesLedgersSummary(DateTime start, DateTime stop, string filter)
{
var ledger = new List <FuelLedgerSummary>();
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("SELECT st_name, SUM(CASE WHEN item_id=1 THEN total ELSE 0 END)DX, SUM(CASE WHEN item_id=2 THEN total ELSE 0 END)uX, SUM(CASE WHEN item_id=3 THEN total ELSE 0 END)vP, SUM(CASE WHEN item_id=4 THEN total ELSE 0 END)IK, SUM(total) TOTAL, SUM(tax_amount) TAX, SUM((tax_amount/0.08))ex_vat, SUM(total-tax_amount-(tax_amount/0.08)) zero_amount FROM vPurchasesLedger INNER JOIN Stations ON st_idnt=st INNER JOIN StationsBrand ON sb_idnt=st_brand " + conn.GetQueryString(filter, "st_name", "dt BETWEEN '" + start.Date + "' AND '" + stop.Date + "'") + " GROUP BY st_name, st_order ORDER BY st_order");
if (dr.HasRows)
{
while (dr.Read())
{
ledger.Add(new FuelLedgerSummary {
Name = dr[0].ToString(),
Dx = Convert.ToDouble(dr[1]),
Ux = Convert.ToDouble(dr[2]),
Vp = Convert.ToDouble(dr[3]),
Ik = Convert.ToDouble(dr[4]),
Total = Convert.ToDouble(dr[5]),
Vats = Convert.ToDouble(dr[6]),
Excl = Convert.ToDouble(dr[7]),
Zero = Convert.ToDouble(dr[8]),
});
}
}
return(ledger);
}
public List <FuelPurchasesLedger> GetFuelPurchasesLedgers(long stid, DateTime start, DateTime stop, string filter)
{
var ledgers = new List <FuelPurchasesLedger>();
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("SELECT id, dt, qty, price, descr, invs, total, tax_amount, pdo1, pdo2, pdon, st_name, sb_brand FROM vPurchasesLedger INNER JOIN Stations ON st_idnt=st INNER JOIN StationsBrand ON sb_idnt=st_brand " + conn.GetQueryString(filter, "CAST(qty AS NVARCHAR)+'-'+descr+'-'+invs+'-'+CAST(total AS NVARCHAR)+'-'+CAST(pdo1 AS NVARCHAR)+'-'+CAST(pdo2 AS NVARCHAR)", "st=" + stid + " AND dt BETWEEN '" + start.Date + "' AND '" + stop.Date + "'") + " ORDER BY dt, descr, id");
if (dr.HasRows)
{
while (dr.Read())
{
FuelPurchasesLedger item = new FuelPurchasesLedger();
item.Station.Id = stid;
item.Id = Convert.ToInt64(dr[0]);
item.Date = Convert.ToDateTime(dr[1]).ToString("dd-MMM");
item.Ltrs = Convert.ToDouble(dr[2]);
item.Price = Convert.ToDouble(dr[3]);
item.Description = dr[4].ToString();
item.Invoice = dr[5].ToString();
item.Total = Convert.ToDouble(dr[6]);
item.Vats = Convert.ToDouble(dr[7]);
item.PayCard = Convert.ToDouble(dr[8]);
item.PayAmts = Convert.ToDouble(dr[9]);
if (!String.IsNullOrEmpty(dr[10].ToString()))
{
item.PayDate = Convert.ToDateTime(dr[10].ToString()).ToString("dd-MMM");
}
item.Station.Name = dr[11].ToString().ToUpper();
item.Station.Brand.Name = dr[12].ToString().ToUpper();
item.Excl = (item.Vats / 0.08);
item.Zero = item.Total - item.Vats - item.Excl;
ledgers.Add(item);
}
}
return(ledgers);
}
public List <Patient> SearchPatients(string names = "", string identifier = "", string phone = "", string age = "", string gender = "", string visit = "")
{
SqlServerConnection conn = new SqlServerConnection();
List <Patient> search = new List <Patient>();
string query = "WHERE pt_idnt>0";
double ageInt;
if (!string.IsNullOrEmpty(names))
{
query += conn.GetQueryString(names, "ps_name", "", true, false);
}
if (!string.IsNullOrEmpty(identifier))
{
query += conn.GetQueryString(identifier, "pt_identifier+'-'+pi_identifier", "", true, false);
}
if (!string.IsNullOrEmpty(phone))
{
query += conn.GetQueryString(phone, "pa_telephone+'-'+pa_location+'-'+pa_email", "", true, false);
}
if (!string.IsNullOrEmpty(age) && Double.TryParse(age, out ageInt) && Double.Parse(age) > 0)
{
query += " AND ps_dob BETWEEN DATEADD(YEAR, 0-" + (ageInt + 1) + ", GETDATE()) AND DATEADD(YEAR, 0-" + (ageInt - 1) + ", GETDATE())";
}
if (!string.IsNullOrEmpty(gender))
{
query += " AND ps_gender='" + gender + "'";
}
if (!string.IsNullOrEmpty(visit))
{
query += " AND last_visit>=DATEADD(MONTH, 0-" + Int32.Parse(visit) + ", GETDATE())";
}
SqlDataReader dr = conn.SqlServerConnect("SELECT pt_idnt, pt_uuid, pt_identifier, pt_notes, pt_added_by, pt_added_on, pt_visit, pst_idnt, pst_status, pi_idnt, pi_identifier, pi_notes, pit_idnt, pit_type, ps_idnt, ps_name, ps_gender, ps_dob, ps_estimate, ps_added_on, ps_added_by, ps_notes, pa_idnt, pa_default, pa_telephone, pa_email, pa_location, pa_added_on, pa_added_by, pa_notes FROM vPatient WHERE pt_idnt IN (SELECT DISTINCT TOP(100)pt_idnt FROM vPatientSearch " + query + ") ORDER BY ps_name, pt_identifier");
if (dr.HasRows)
{
while (dr.Read())
{
Patient patient = new Patient
{
Id = Convert.ToInt64(dr[0]),
Uuid = dr[1].ToString(),
Identifier = dr[2].ToString(),
Notes = dr[3].ToString(),
AddedBy = new Users {
Id = Convert.ToInt64(dr[4])
},
AddedOn = Convert.ToDateTime(dr[5]),
LastDate = Convert.ToDateTime(dr[6]),
Status = new PatientStatus
{
Id = Convert.ToInt64(dr[7]),
Name = dr[8].ToString()
},
PI = new PatientIdentifier
{
Id = Convert.ToInt64(dr[9]),
Identifier = dr[10].ToString(),
Notes = dr[11].ToString(),
Type = new PatientIdentifierType
{
Id = Convert.ToInt64(dr[12]),
Name = dr[13].ToString()
}
},
Person = new Person
{
Id = Convert.ToInt64(dr[14]),
Name = dr[15].ToString(),
Gender = dr[16].ToString(),
DateOfBirth = Convert.ToDateTime(dr[17]),
Estimate = Convert.ToBoolean(dr[18]),
AddedOn = Convert.ToDateTime(dr[19]),
AddedBy = new Users {
Id = Convert.ToInt64(dr[20])
},
Notes = dr[21].ToString(),
Address = new PersonAddress
{
Id = Convert.ToInt64(dr[22]),
Default = Convert.ToBoolean(dr[23]),
Telephone = dr[24].ToString(),
Email = dr[25].ToString(),
Location = dr[26].ToString(),
AddedOn = Convert.ToDateTime(dr[27]),
AddedBy = new Users {
Id = Convert.ToInt64(dr[28])
},
Notes = dr[29].ToString()
}
}
};
patient.GetAge();
patient.LastVisit = GetLastSeenAsTimeSpan(patient.LastDate);
search.Add(patient);
}
}
return(search);
}
public List <Room> GetRooms(RoomType type, Concept concept, BillableService service, bool includeVoid = false, string conditions = "", string filter = "")
{
List <Room> rooms = new List <Room>();
SqlServerConnection conn = new SqlServerConnection();
string query = "";
if (type != null)
{
query = "WHERE rm_type=" + type.Id;
}
if (concept != null)
{
query += (query == "" ? "WHERE " : " AND ") + "rm_concept=" + concept.Id;
}
if (service != null)
{
query += (query == "" ? "WHERE " : " AND ") + "rm_service=" + service.Id;
}
if (!includeVoid)
{
query += (query == "" ? "WHERE " : " AND ") + "rm_void=0";
}
if (!string.IsNullOrEmpty(conditions))
{
query += (query == "" ? "WHERE " : " AND ") + conditions;
}
if (!string.IsNullOrEmpty(filter))
{
query += conn.GetQueryString(filter, "rm_room+'-'+rt_type+'-'+bs_service+'-'+CAST(bs_amount AS NVARCHAR)", "", true, false);
}
SqlDataReader dr = conn.SqlServerConnect("SELECT rm_idnt, rm_void, rm_room, rm_concept, rt_idnt, rt_void, rt_concept, rt_type, ISNULL(bs_idnt,0), bs_code, ISNULL(bs_concept,0), bs_service, ISNULL(bs_amount,0), bs_description FROM Rooms INNER JOIN RoomType ON rm_type=rt_idnt LEFT OUTER JOIN BillableService ON rm_service=bs_idnt " + query);
if (dr.HasRows)
{
while (dr.Read())
{
rooms.Add(new Room {
Id = Convert.ToInt64(dr[0]),
Void = Convert.ToBoolean(dr[1]),
Name = dr[2].ToString(),
Concept = new Concept {
Id = Convert.ToInt64(dr[3])
},
Type = new RoomType {
Id = Convert.ToInt64(dr[4]),
Void = Convert.ToBoolean(dr[5]),
Concept = new Concept {
Id = Convert.ToInt64(dr[6])
},
Name = dr[7].ToString(),
},
Service = new BillableService {
Id = Convert.ToInt64(dr[8]),
Code = dr[9].ToString(),
Concept = new Concept {
Id = Convert.ToInt64(dr[10])
},
Name = dr[11].ToString(),
}
});
}
}
return(rooms);
}
public List <DrugBatches> GetDrugBatches(Facility facility, DrugCategory category, DateTime?expiry, bool includeExpired = true, bool includeFlagged = true, string filter = "")
{
List <DrugBatches> batches = new List <DrugBatches>();
SqlServerConnection conn = new SqlServerConnection();
string query = conn.GetQueryString(filter, "idb_batch+'-'+idb_company+'-'+idb_supplier+'-'+drg_name+'-'+dc_name+'-'+df_name+'-'+df_dosage+'-'+fc_prefix+'-'+fc_name", "idb_idnt>0");
if (!(facility is null))
{
query += " AND fc_idnt=" + facility.Id;
}
if (!(category is null))
{
query += " AND dc_idnt=" + category.Id;
}
if (expiry.HasValue)
{
query += " AND idb_expiry<='" + Convert.ToDateTime(expiry).Date + "'";
}
if (!includeExpired)
{
query += " AND idb_expiry>'" + DateTime.Now.Date + "'";
}
if (!includeFlagged)
{
query += " AND idb_flag=0";
}
SqlDataReader dr = conn.SqlServerConnect("SELECT idb_idnt, idb_batch, idb_company, idb_supplier, idb_manufacture, idb_expiry, idb_notes, drg_idnt, drg_initial, drg_name, dc_idnt, dc_name, df_idnt, df_name, df_dosage, fc_idnt, fc_prefix, fc_name, ISNULL(avls,0)x FROM InventoryDrugBatches INNER JOIN InventoryDrug ON idb_drug=drg_idnt INNER JOIN InventoryDrugCategory ON drg_category=dc_idnt INNER JOIN InventoryDrugFormulation ON drg_formulation=df_idnt INNER JOIN Facilities ON fc_idnt=idb_facility LEFT OUTER JOIN vBatchSummary ON batch=idb_idnt " + query + " ORDER BY idb_expiry, df_name, idb_facility");
if (dr.HasRows)
{
while (dr.Read())
{
batches.Add(new DrugBatches {
Id = Convert.ToInt64(dr[0]),
BatchNo = dr[1].ToString(),
Company = dr[2].ToString(),
Supplier = dr[3].ToString(),
Manufacture = Convert.ToDateTime(dr[4]).ToString("MM/yyyy"),
DateOfManufacture = Convert.ToDateTime(dr[4]),
Expiry = Convert.ToDateTime(dr[5]).ToString("MM/yyyy"),
DateOfExpiry = Convert.ToDateTime(dr[5]),
Notes = dr[6].ToString(),
Drug = new Drug {
Id = Convert.ToInt64(dr[7]),
Initial = dr[8].ToString(),
Name = dr[9].ToString(),
Category = new DrugCategory {
Id = Convert.ToInt64(dr[10]),
Name = dr[11].ToString()
},
Formulation = new DrugFormulation {
Id = Convert.ToInt64(dr[12]),
Name = dr[13].ToString(),
Dosage = dr[14].ToString(),
}
},
Facility = new Facility {
Id = Convert.ToInt64(dr[15]),
Prefix = dr[16].ToString(),
Name = dr[17].ToString(),
},
Available = Convert.ToDouble(dr[18])
});
}
}
return(batches);
}
public List <DrugReceiptDetails> GetDrugReceiptDetails(Facility facility, DrugCategory category, DateTime?start_date, DateTime?end_date, string filter = "")
{
List <DrugReceiptDetails> receipts = new List <DrugReceiptDetails>();
SqlServerConnection conn = new SqlServerConnection();
string query = conn.GetQueryString(filter, "idb_batch+'-'+idb_company+'-'+idb_supplier+'-'+drg_name+'-'+dc_name+'-'+df_name+'-'+df_dosage+'-'+fc_prefix+'-'+fc_name+'-'+CAST(drd_quantity AS NVARCHAR)", "drd_idnt>0");
if (!(facility is null))
{
query += " AND idb_facility=" + facility.Id;
}
if (!(category is null))
{
query += " AND dc_idnt=" + category.Id;
}
if (start_date.HasValue)
{
query += " AND idr_date>='" + Convert.ToDateTime(start_date).Date + "'";
}
if (end_date.HasValue)
{
query += " AND idr_date<='" + Convert.ToDateTime(end_date).Date + "'";
}
SqlDataReader dr = conn.SqlServerConnect("SELECT drd_idnt, drd_quantity, idr_idnt, idr_date, idr_receipt_no, idb_idnt, idb_batch, idb_company, idb_supplier, idb_manufacture, idb_expiry, drg_idnt, drg_initial, drg_name, df_idnt, df_name, df_dosage, dc_idnt, dc_name, fc_idnt, fc_prefix, fc_name FROM InventoryDrugReceiptDetails INNER JOIN InventoryDrugReceipt ON drd_receipt=idr_idnt INNER JOIN InventoryDrugBatches ON drd_batch=idb_idnt INNER JOIN InventoryDrug ON idb_drug=drg_idnt INNER JOIN InventoryDrugFormulation ON drg_formulation=df_idnt INNER JOIN InventoryDrugCategory ON drg_category=dc_idnt INNER JOIN Facilities ON fc_idnt=idb_facility " + query + " ORDER BY drd_idnt");
if (dr.HasRows)
{
while (dr.Read())
{
receipts.Add(new DrugReceiptDetails {
Id = Convert.ToInt64(dr[0]),
Quantity = Convert.ToInt64(dr[1]),
Receipt = new DrugReceipt {
Id = Convert.ToInt64(dr[2]),
Date = Convert.ToDateTime(dr[3]),
DateString = Convert.ToDateTime(dr[3]).ToString("dd/MM/yyyy"),
Number = dr[4].ToString(),
},
Batch = new DrugBatches {
Id = Convert.ToInt64(dr[5]),
BatchNo = dr[6].ToString(),
Company = dr[7].ToString(),
Supplier = dr[8].ToString(),
DateOfManufacture = Convert.ToDateTime(dr[9]),
Manufacture = Convert.ToDateTime(dr[9]).ToString("MM/yyyy"),
DateOfExpiry = Convert.ToDateTime(dr[10]),
Expiry = Convert.ToDateTime(dr[10]).ToString("MM/yyyy"),
Drug = new Drug {
Id = Convert.ToInt64(dr[11]),
Initial = dr[12].ToString(),
Name = dr[13].ToString(),
Formulation = new DrugFormulation {
Id = Convert.ToInt64(dr[14]),
Name = dr[15].ToString(),
Dosage = dr[16].ToString()
},
Category = new DrugCategory {
Id = Convert.ToInt64(dr[17]),
Name = dr[18].ToString()
}
},
Facility = new Facility {
Id = Convert.ToInt64(dr[19]),
Prefix = dr[20].ToString(),
Name = dr[21].ToString()
}
}
});
}
}
return(receipts);
}
public List <BankingReconcileModel> GetBankingReconciles(DateTime start, DateTime stop, string accounts, string filter)
{
List <BankingReconcileModel> model = new List <BankingReconcileModel>();
string q = "";
double cumm = 0;
if (!string.IsNullOrEmpty(accounts))
{
q = " AND ar_st IN (SELECT bs_stidnt FROM BankStations WHERE bs_bank IN (" + accounts + "))";
}
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("DECLARE @date1 DATE='" + start.Date + "'; SELECT ISNULL(SUM(CASE brs_action WHEN 1 THEN ar_amount ELSE 0-ar_amount END),0) FROM vBankReconcile INNER JOIN BankReconcileSources ON ar_source=brs_idnt INNER JOIN Stations ON ar_st=st_idnt WHERE ar_date<@date1" + q);
if (dr.Read() && Convert.ToDouble(dr[0]) > 0)
{
BankingReconcileModel entry = new BankingReconcileModel {
Date = start.ToString("dd-MMM"),
Cummulative = Convert.ToDouble(dr[0]),
Description = "BALANCE B/F"
};
cumm = entry.Cummulative;
model.Add(entry);
}
q = conn.GetQueryString(filter, "ar_cust+'-'+ar_chqs+'-'+ar_invs+'-'+CAST(ar_amount AS NVARCHAR)+'-'+brs_source+'-'+st_synonym", "ar_date BETWEEN '" + start.Date + "' AND '" + stop.Date + "'");
if (!string.IsNullOrEmpty(accounts))
{
q += " AND ar_st IN (SELECT bs_stidnt FROM BankStations WHERE bs_bank IN (" + accounts + "))";
}
conn = new SqlServerConnection();
dr = conn.SqlServerConnect("SELECT ar_st, ar_source, brs_action, ar_date, ar_qty, ar_price, ar_cust, ar_chqs, ar_invs, brs_source+' '+st_synonym ar_desc, ar_amount FROM vBankReconcile INNER JOIN BankReconcileSources ON ar_source=brs_idnt INNER JOIN Stations ON ar_st=st_idnt " + q + " ORDER BY ar_date");
if (dr.HasRows)
{
while (dr.Read())
{
BankingReconcileModel entry = new BankingReconcileModel {
Station = Convert.ToInt32(dr[0]),
Source = Convert.ToInt32(dr[1]),
Action = Convert.ToInt32(dr[2]),
Date = Convert.ToDateTime(dr[3]).ToString("dd-MMM"),
Quantity = Convert.ToDouble(dr[4]),
Price = Convert.ToDouble(dr[5]),
Customer = dr[6].ToString(),
Cheque = dr[7].ToString(),
Invoice = dr[8].ToString(),
Description = dr[9].ToString()
};
if (entry.Action.Equals(1))
{
entry.Revenue = Convert.ToDouble(dr[10]);
}
else
{
entry.Expense = Convert.ToDouble(dr[10]);
}
cumm += entry.Revenue - entry.Expense;
entry.Cummulative = cumm;
model.Add(entry);
}
}
return(model);
}