public ActionResult GetDrugWiseReport(DateTime FromDate, DateTime ToDate, int?DrugId, int?insuaranceCompany)
{
DrugWiseReportDataSet drugWiseReportDataSet = new DrugWiseReportDataSet();
drugWiseReportDataSet = GetDrugTWiseReportData(FromDate, ToDate, DrugId, insuaranceCompany);
ReportDocument Rd = new ReportDocument();
Rd.Load(Path.Combine(Server.MapPath("~/Areas/PharmacyModule/Reports/DrugWiseReport/DrugWiseReport.rpt")));
Rd.SetDataSource(drugWiseReportDataSet);
Rd.Subreports["RptReportHeader.rpt"].SetDataSource(HeaderAndFooterForReports.GetAllReportHeader());
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
Stream Stream = Rd.ExportToStream(
CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Stream.Seek(0, SeekOrigin.Begin);
return(File(Stream, "application/pdf", "DrugWiseSummary.pdf"));
}
private DrugWiseReportDataSet GetDrugTWiseReportData(DateTime FromDate, DateTime ToDate, int?DrugId, int?insuaranceCompany)
{
DrugWiseReportDataSet drugWiseReportDataSet = new DrugWiseReportDataSet();
var data = new List <DrugTransactions>();
if (DrugId.HasValue && insuaranceCompany.Value == 0)
{
var DrugItems = db.ItemMaster.Where(p => p.DrugId == DrugId.Value && p.StoreName == "MS").ToList();
foreach (var item in DrugItems)
{
var dt = db.DrugTransactions
.Where(p => DbFunctions.TruncateTime(p.TransactionDate) >= FromDate && DbFunctions.TruncateTime(p.TransactionDate) <= ToDate)
.Where(x => x.ItemMasterId == item.Id)
.ToList();
data.AddRange(dt);
}
foreach (var item in data)
{
if (item.IsWalkIn == true)
{
var walkInData = db.Walkings.Where(x => x.Id == item.PatientId).FirstOrDefault();
var drug = db.ItemMaster.Find(walkInData.DrugId);
if (drug != null)
{
var CurrentStockForThatDay = 0;
if (item.PatientId.HasValue)
{
CurrentStockForThatDay = GetClosingStockOfSomeDrug(drug.Id, item.PatientId.Value, drug.CurrentStock, FromDate, ToDate);
}
else
{
CurrentStockForThatDay = GetClosingStockOfSomeDrugForDepartment(drug.Id, item.DepartmentId.Value, drug.CurrentStock, FromDate, ToDate);
}
var patientsName = walkInData.PatientsName + " - #Walk in";
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((walkInData.UnitPrice - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-#Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
walkInData.PatientIdentifierId.ToString() ?? "",
walkInData.TimeAdded.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
walkInData.UnitPrice,
(item.QuantityIssued * walkInData.UnitPrice) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
}
else
{
var drug = db.ItemMaster.Find(item.ItemMasterId);
if (drug != null)
{
var CurrentStockForThatDay = 0;
if (item.PatientId.HasValue)
{
CurrentStockForThatDay = GetClosingStockOfSomeDrug(drug.Id, item.PatientId.Value, drug.CurrentStock, FromDate, ToDate);
}
else
{
CurrentStockForThatDay = GetClosingStockOfSomeDrugForDepartment(drug.Id, item.DepartmentId.Value, drug.CurrentStock, FromDate, ToDate);
}
var medication = db2.Medications.Where(p => p.OPDNo == item.PatientId)?.FirstOrDefault();
if (medication != null)
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((medication.UnitPrice - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
medication.BillNo.ToString() ?? "",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
medication.UnitPrice,
(item.QuantityIssued * medication.UnitPrice) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
else
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((item.Rate - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + " -Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
"",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
item.Rate,
(item.QuantityIssued * item.Rate) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
}
}
}
}
else if (DrugId.HasValue && insuaranceCompany.Value > 0)
{
var DrugItems = db.ItemMaster.Where(p => p.DrugId == DrugId.Value && p.StoreName == "MS").ToList();
var dt2 = new List <DrugTransactions>();
//we get all drugs with that id irrespective of the companies
foreach (var item in DrugItems)
{
var dt = db.DrugTransactions
.Where(p => DbFunctions.TruncateTime(p.TransactionDate) >= FromDate && DbFunctions.TruncateTime(p.TransactionDate) <= ToDate)
.Where(x => x.ItemMasterId == item.Id)
.ToList();
dt2.AddRange(dt);
}
//now we sort the drugs based on the insurance selected
var dt3 = new List <DrugTransactions>();
//we get all drugs with that id irrespective of the companies
foreach (var item in dt2)
{
var patientInOpd = db2.OpdRegisters
.Where(p => p.Id == item.PatientId && p.Tariff.Company.Id == insuaranceCompany.Value)
.FirstOrDefault();
if (patientInOpd != null)
{
dt3.Add(item);
}
}
data = dt3;
foreach (var item in data)
{
var drug = db.ItemMaster.Find(item.ItemMasterId);
if (drug != null)
{
var CurrentStockForThatDay = 0;
if (item.PatientId.HasValue)
{
CurrentStockForThatDay = GetClosingStockOfSomeDrug(drug.Id, item.PatientId.Value, drug.CurrentStock, FromDate, ToDate);
}
else
{
CurrentStockForThatDay = GetClosingStockOfSomeDrugForDepartment(drug.Id, item.DepartmentId.Value, drug.CurrentStock, FromDate, ToDate);
}
var medication = db2.Medications.Where(p => p.OPDNo == item.PatientId)?.FirstOrDefault();
if (medication != null)
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((medication.UnitPrice - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
medication.BillNo.ToString() ?? "",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
medication.UnitPrice,
(item.QuantityIssued * medication.UnitPrice) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
else
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((item.Rate - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
"",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
item.Rate,
(item.QuantityIssued * item.Rate) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
}
}
}
else if (DrugId.HasValue == false && insuaranceCompany.Value > 0)
{
//select all drugs irrespective of drug id
var dt2 = db.DrugTransactions.Where(p => DbFunctions.TruncateTime(p.TransactionDate) >= FromDate && DbFunctions.TruncateTime(p.TransactionDate) <= ToDate).ToList();
var dt3 = new List <DrugTransactions>();
//we get only drugs that were issued to the insurance patientsof that specific company
foreach (var item in dt2)
{
var patientInOpd = db2.OpdRegisters
.Where(p => p.Id == item.PatientId && p.Tariff.Company.Id == insuaranceCompany.Value)
.FirstOrDefault();
if (patientInOpd != null)
{
dt3.Add(item);
}
}
data = dt3;
foreach (var item in data)
{
var drug = db.ItemMaster.Where(x => x.Id == item.ItemMasterId && x.StoreName == "MS").FirstOrDefault();
if (drug != null)
{
var CurrentStockForThatDay = 0;
if (item.PatientId.HasValue)
{
CurrentStockForThatDay = GetClosingStockOfSomeDrug(drug.Id, item.PatientId.Value, drug.CurrentStock, FromDate, ToDate);
}
else
{
CurrentStockForThatDay = GetClosingStockOfSomeDrugForDepartment(drug.Id, item.DepartmentId.Value, drug.CurrentStock, FromDate, ToDate);
}
var medication = db2.Medications.Where(p => p.OPDNo == item.PatientId)?.FirstOrDefault();
if (medication != null)
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((medication.UnitPrice - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
medication.BillNo.ToString() ?? "",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
medication.UnitPrice,
(item.QuantityIssued * medication.UnitPrice) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
else
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((item.Rate - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
"",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
item.Rate,
(item.QuantityIssued * item.Rate) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
}
}
}
else if (DrugId.HasValue == false && insuaranceCompany.Value == 0)
{
data = db.DrugTransactions.Where(p => DbFunctions.TruncateTime(p.TransactionDate) >= FromDate && DbFunctions.TruncateTime(p.TransactionDate) <= ToDate).ToList();
foreach (var item in data)
{
if (item.IsWalkIn == true)
{
var walkInData = db.Walkings.Where(x => x.Id == item.PatientId).FirstOrDefault();
if (walkInData != null)
{
var drug = db.ItemMaster.Where(p => p.Id == walkInData.DrugId && p.StoreName == "MS").FirstOrDefault();
if (drug != null)
{
var CurrentStockForThatDay = 0;
if (item.PatientId.HasValue)
{
CurrentStockForThatDay = GetClosingStockOfSomeDrug(drug.Id, item.PatientId.Value, drug.CurrentStock, FromDate, ToDate);
}
else
{
CurrentStockForThatDay = GetClosingStockOfSomeDrugForDepartment(drug.Id, item.DepartmentId.Value, drug.CurrentStock, FromDate, ToDate);
}
var patientsName = walkInData.PatientsName + " #Walk in";
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((walkInData.UnitPrice - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
walkInData.PatientIdentifierId.ToString() ?? "",
walkInData.TimeAdded.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
walkInData.UnitPrice,
(item.QuantityIssued * walkInData.UnitPrice) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
}
}
else
{
var drug = db.ItemMaster.Where(p => p.Id == item.ItemMasterId && p.StoreName == "MS").FirstOrDefault();
if (drug != null)
{
var CurrentStockForThatDay = 0;
if (item.PatientId.HasValue)
{
CurrentStockForThatDay = GetClosingStockOfSomeDrug(drug.Id, item.PatientId.Value, drug.CurrentStock, FromDate, ToDate);
}
else
{
CurrentStockForThatDay = GetClosingStockOfSomeDrugForDepartment(drug.Id, item.DepartmentId.Value, drug.CurrentStock, FromDate, ToDate);
}
var medication = db2.Medications.Where(p => p.OPDNo == item.PatientId)?.FirstOrDefault();
if (medication != null)
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((medication.UnitPrice - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
medication.BillNo.ToString() ?? "",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
medication.UnitPrice,
(item.QuantityIssued * medication.UnitPrice) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
else
{
var patientsName = db2.OpdRegisters.Where(p => p.Id == item.PatientId).Select(x => x.Patient.FName + " " + x.Patient.LName)?.FirstOrDefault();
var profit = 0.0;
if (drug.CostPriceUnit != 0)
{
profit = ((item.Rate - drug.CostPriceUnit) / drug.CostPriceUnit) * 100;
}
if (patientsName == null || patientsName == "")
{
if (item.DepartmentId != null)
{
patientsName = db2.Departments.FirstOrDefault(p => p.Id == item.DepartmentId.Value)?.DepartmentName ?? "";
patientsName = patientsName + "-Department";
}
}
drugWiseReportDataSet.DrugWiseReport.AddDrugWiseReportRow(
drug.ItemName ?? "",
drug.BatchNo ?? "",
"",
item.TransactionDate.ToString("dd-MM-yyyy") ?? "",
patientsName ?? "",
item.QuantityIssued,
CurrentStockForThatDay,
drug.CostPriceUnit,
item.Rate,
(item.QuantityIssued * item.Rate) - (item.QuantityIssued * drug.CostPriceUnit),
profit
);
}
}
}
}
}
DrugWiseReportDataSet dataSet2 = new DrugWiseReportDataSet();
foreach (var item in drugWiseReportDataSet.DrugWiseReport.OrderByDescending(p => p.CurrentStock).ToList())
{
dataSet2.DrugWiseReport.AddDrugWiseReportRow(
item.DrugName ?? "",
item.BatchNo ?? "",
item.VoucherNo ?? "",
item.VoucherDate ?? DateTime.Now.ToString("dd-MM-yyyy"),
item.IssueTo ?? "",
item.Quantity,
item.CurrentStock,
item.CostPrice,
item.Selling,
item.ProfitAmount,
item.Profit);
}
return(dataSet2);
}