public ActionResult AjaxMethod_CustName(string custNM)
{
KeystoneProject.Buisness_Logic.Pharmacy.BL_PatientMedicalBill BL_obj = new BL_PatientMedicalBill();
KeystoneProject.Models.Pharmacy.PatientMedicalBill obj = new PatientMedicalBill();
List <PatientMedicalBill> searchList = new List <PatientMedicalBill>();
DataSet ds = BL_obj.BindCustID(custNM);
DataTable dt = new DataTable();
dt = BL_obj.Bind_CustDetail(Convert.ToInt32(ds.Tables[0].Rows[0]["CustomerID"]));
foreach (DataRow dr in dt.Rows)
{
searchList.Add(
new PatientMedicalBill
{
CustomerName = Convert.ToString(dr["CustomerName"]),
Address = Convert.ToString(dr["Address"]),
});
}
return(new JsonResult
{
Data = searchList,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public ActionResult AjaxMethod_regno(int regno)
{
KeystoneProject.Buisness_Logic.Pharmacy.BL_PatientMedicalBill BL_obj = new BL_PatientMedicalBill();
KeystoneProject.Models.Pharmacy.PatientMedicalBill obj = new PatientMedicalBill();
List <PatientMedicalBill> searchList = new List <PatientMedicalBill>();
DataTable dt = new DataTable();
dt = BL_obj.Bind_patDetail(regno);
foreach (DataRow dr in dt.Rows)
{
searchList.Add(
new PatientMedicalBill
{
PatientName = Convert.ToString(dr["PatientName"]),
PatientRegNO = Convert.ToString(dr["PatientRegNO"]),
CustomerName = Convert.ToString(dr["PatientName"]),
PatientType = Convert.ToString(dr["PatientType"]),
Address = Convert.ToString(dr["Address"]),
DoctorPrintName = Convert.ToString(dr["DoctorPrintName"]),
});
}
return(new JsonResult
{
Data = searchList,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
//public ActionResult AjaxMethod_CustOldBill(string custNM)
//{
// KeystoneProject.Buisness_Logic.Pharmacy.BL_PatientMedicalBill BL_obj = new BL_PatientMedicalBill();
// KeystoneProject.Models.Pharmacy.PatientMedicalBill obj = new PatientMedicalBill();
// List<PatientMedicalBill> searchList = new List<PatientMedicalBill>();
// DataTable dt = new DataTable();
// DataSet ds = BL_obj.BindCustID(custNM);
// dt = BL_obj.GetOldBill(Convert.ToInt16(ds.Tables[0].Rows[0]["CustomerID"].ToString()));
// foreach (DataRow dr in dt.Rows)
// {
// searchList.Add(
// new PatientMedicalBill
// {
// BillNo = Convert.ToString(dr["BillNo"]),
// });
// }
// return new JsonResult { Data = searchList, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
//}
public ActionResult AjaxMethod_batchno(string prodnm)
{
KeystoneProject.Buisness_Logic.Pharmacy.BL_PatientMedicalBill BL_obj = new BL_PatientMedicalBill();
KeystoneProject.Models.Pharmacy.PatientMedicalBill obj = new PatientMedicalBill();
List <PatientMedicalBill> searchList = new List <PatientMedicalBill>();
DataTable dt = new DataTable();
DataSet ds1 = BL_obj.BindProductName(prodnm);
int ProductID = Convert.ToInt16(ds1.Tables[0].Rows[0]["ProductID"].ToString());
DataSet ds = BL_obj.GetMedicalBillForProductStock(ProductID);
List <PatientMedicalBill> obj1 = new List <Models.Pharmacy.PatientMedicalBill>();
if (ds.Tables[0].Rows.Count > 0)
{
Connect();
SqlCommand cmd = new SqlCommand("select ProductDetailsID ,BatchNo,ExpiryDate,SalesRate,CurrentStock ,CurrentStock1, PurchaseRate,MRP from ProductDetails left join Product on Product .ProductID = ProductDetails.ProductID where Product.HospitalID = " + HospitalID + " and Product.LocationID = " + LocationID + " and CurrentStock > 0 and Convert(varchar(10),ExpiryDate,120) > (GETDATE()) and ProductDetails.ProductID =" + ProductID + " and ProductDetails.RowStatus = 0 order by ExpiryDate asc", con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
}
else
{
Connect();
SqlCommand cmd = new SqlCommand("select ProductDetailsID ,BatchNo,ExpiryDate,SalesRate,CurrentStock ,CurrentStock1, PurchaseRate,MRP,SalesRate from ProductDetails left join Product on Product .ProductID = ProductDetails.ProductID where Product.HospitalID = " + HospitalID + " and Product.LocationID = " + LocationID + " and Convert(varchar(10),ExpiryDate,120) > (GETDATE()) and ProductDetails.ProductID =" + ProductID + " and ProductDetails.RowStatus = 0 order by ExpiryDate asc", con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
}
string color = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
string date = Convert.ToDateTime(dr["ExpiryDate"]).AddDays(-90).ToString();
if (Convert.ToDateTime(DateTime.Now) > Convert.ToDateTime(date))
{
color = "#90EE90";
}
else
{
color = "#D3D3D3";
}
obj1.Add(new Models.Pharmacy.PatientMedicalBill
{
ProductDetailsID = dr["ProductDetailsID"].ToString(),
batchNumber = dr["BatchNo"].ToString(),
expiryDate = dr["ExpiryDate"].ToString(),
ExparyDateColor = color,
});
}
return(new JsonResult {
Data = obj1, JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public ActionResult AjaxMethod_OldBill_regno(int regno)
{
KeystoneProject.Buisness_Logic.Pharmacy.BL_PatientMedicalBill BL_obj = new BL_PatientMedicalBill();
KeystoneProject.Models.Pharmacy.PatientMedicalBill obj = new PatientMedicalBill();
List <PatientMedicalBill> searchList = new List <PatientMedicalBill>();
DataTable dt = new DataTable();
dt = BL_obj.GetOldBill(regno);
foreach (DataRow dr in dt.Rows)
{
searchList.Add(
new PatientMedicalBill
{
BillNo = Convert.ToString(dr["BillNo&Date"]),
});
}
return(new JsonResult {
Data = searchList, JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public ActionResult AjaxMethod_OldBill(string patnm)
{
KeystoneProject.Buisness_Logic.Pharmacy.BL_PatientMedicalBill BL_obj = new BL_PatientMedicalBill();
KeystoneProject.Models.Pharmacy.PatientMedicalBill obj = new PatientMedicalBill();
List <PatientMedicalBill> searchList = new List <PatientMedicalBill>();
DataTable dt = new DataTable();
DataSet ds = BL_obj.BindRegno(patnm);
dt = BL_obj.GetOldBill(Convert.ToInt16(ds.Tables[0].Rows[0]["PatientRegNO"].ToString()));
foreach (DataRow dr in dt.Rows)
{
searchList.Add(
new PatientMedicalBill
{
BillNo = Convert.ToString(dr["BillNo&Date"]),
});
}
return(new JsonResult {
Data = searchList, JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public bool Save(PatientMedicalBill obj)
{
try
{
int regno = Convert.ToInt32(obj.PatientRegNO);
DataTable dt = Bind_patDetail(regno);
Connect();
SqlCommand cmd = new SqlCommand("IUPatientBillsMedical", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@HospitalID", HospitalID);
cmd.Parameters.AddWithValue("@LocationID", LocationID);
if (obj.MedicalBillID == 0)
{
cmd.Parameters.AddWithValue("@MedicalBillID", 0);
cmd.Parameters["@MedicalBillID"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@Mode", "ADD");
}
else
{
cmd.Parameters.AddWithValue("@MedicalBillID", obj.MedicalBillID);
cmd.Parameters.AddWithValue("@Mode", "EDIT");
}
if (obj.BillNo == null || obj.BillNo == "" || obj.BillNo == "--New--")
{
cmd.Parameters.AddWithValue("@BillNo", 0);
}
else
{
cmd.Parameters.AddWithValue("@BillNo", obj.BillNo);
}
if (obj.BillNoDate == null || obj.BillNoDate == "")
{
cmd.Parameters.AddWithValue("@BillDate", "");
}
else
{
cmd.Parameters.AddWithValue("@BillDate", obj.BillNoDate);
}
if (obj.PatientRegNO == null || obj.PatientRegNO == "")
{
cmd.Parameters.AddWithValue("@PatientRegNo", obj.CustomerID);
cmd.Parameters.AddWithValue("@BillType", "GeneralMedicalBill");
}
else
{
cmd.Parameters.AddWithValue("@PatientRegNo", obj.PatientRegNO);
cmd.Parameters.AddWithValue("@BillType", dt.Rows[0]["PatientType"].ToString() + "MedicalBill");
}
if (obj.PatientName == null)
{
cmd.Parameters.AddWithValue("@PatientName", "");
}
else
{
cmd.Parameters.AddWithValue("@PatientName", obj.CustomerName);
}
if (obj.Address == null)
{
cmd.Parameters.AddWithValue("@Address", "");
}
else
{
cmd.Parameters.AddWithValue("@Address", obj.Address);
}
if (obj.message == null)
{
cmd.Parameters.AddWithValue("@Message", "");
}
else
{
cmd.Parameters.AddWithValue("@Message", obj.message);
}
cmd.Parameters.AddWithValue("@Path", "");
if (obj.DoctorID == null || obj.DoctorID == "")
{
cmd.Parameters.AddWithValue("@DoctorID", 0);
}
else
{
cmd.Parameters.AddWithValue("@DoctorID", obj.DoctorID);
}
if (obj.DoctorPrintName == null)
{
cmd.Parameters.AddWithValue("@DoctorName", "");
}
else
{
cmd.Parameters.AddWithValue("@DoctorName", obj.DoctorPrintName);
}
if (obj.SalesPersonID == null || obj.SalesPersonID == "")
{
cmd.Parameters.AddWithValue("@SalesPersonID", 0);
}
else
{
cmd.Parameters.AddWithValue("@SalesPersonID", obj.SalesPersonID);
}
if (obj.SalesPersonName == null)
{
cmd.Parameters.AddWithValue("@SalesPersonName", "");
}
else
{
cmd.Parameters.AddWithValue("@SalesPersonName", obj.SalesPersonName);
}
if (obj.paymentType == null)
{
cmd.Parameters.AddWithValue("@PaymentType", "");
}
else
{
cmd.Parameters.AddWithValue("@PaymentType", obj.paymentType);
}
if (obj.Number == null)
{
cmd.Parameters.AddWithValue("@Number", "");
}
else
{
cmd.Parameters.AddWithValue("@Number", obj.Number);
}
if (obj.Name == null)
{
cmd.Parameters.AddWithValue("@Name", "");
}
else
{
cmd.Parameters.AddWithValue("@Name", obj.Name);
}
if (obj.paymentDate == null)
{
cmd.Parameters.AddWithValue("@ChequeDate", "");
}
else
{
cmd.Parameters.AddWithValue("@ChequeDate", obj.paymentDate);
}
cmd.Parameters.AddWithValue("@Other", "");
if (obj.Remarks == null)
{
cmd.Parameters.AddWithValue("@Remarks", "");
}
else
{
cmd.Parameters.AddWithValue("@Remarks", obj.Remarks);
}
if (obj.grossTotal == null || obj.grossTotal == "")
{
cmd.Parameters.AddWithValue("@GrossAmount", 0);
}
else
{
cmd.Parameters.AddWithValue("@GrossAmount", obj.grossTotal);
}
if (obj.discountAmount == null || obj.discountAmount == "")
{
cmd.Parameters.AddWithValue("@DiscountAmount", 0);
}
else
{
cmd.Parameters.AddWithValue("@DiscountAmount", obj.discountAmount);
}
//extra
if (obj.vatAmount == null || obj.vatAmount == "")
{
cmd.Parameters.AddWithValue("@TaxAmount", 0);
}
else
{
cmd.Parameters.AddWithValue("@TaxAmount", obj.vatAmount);
}
if (obj.salesReturn == null || obj.salesReturn == "")
{
cmd.Parameters.AddWithValue("@SalesReturn", 0);
}
else
{
cmd.Parameters.AddWithValue("@SalesReturn", obj.salesReturn);
}
if (obj.totalAmount == null || obj.totalAmount == "")
{
cmd.Parameters.AddWithValue("@TotalAmount", 0);
}
else
{
cmd.Parameters.AddWithValue("@TotalAmount", obj.totalAmount);
}
if (obj.discountPercentage == null || obj.discountPercentage == "")
{
cmd.Parameters.AddWithValue("@DiscountPercent", 0);
}
else
{
cmd.Parameters.AddWithValue("@DiscountPercent", obj.discountPercentage);
}
if (obj.otheLess == null || obj.otheLess == "")
{
cmd.Parameters.AddWithValue("@OtherLess", 0);
}
else
{
cmd.Parameters.AddWithValue("@OtherLess", obj.otheLess);
}
if (obj.netAmount == null || obj.netAmount == "")
{
cmd.Parameters.AddWithValue("@NetAmount", 0);
}
else
{
cmd.Parameters.AddWithValue("@NetAmount", obj.netAmount);
}
if (obj.cashRecipt == null || obj.cashRecipt == "")
{
cmd.Parameters.AddWithValue("@CashReceived", 0);
}
else
{
cmd.Parameters.AddWithValue("@CashReceived", obj.cashRecipt);
}
cmd.Parameters.AddWithValue("@CreationID", UserID);
con.Open();
int i = cmd.ExecuteNonQuery();
string MedBillID = cmd.Parameters["@MedicalBillID"].Value.ToString();
con.Close();
if (i > 0)
{
string[] prdctID = obj.tblProductID.Split(',');
string[] prdctNm = obj.tblProductName.Split(',');
for (int k = 0; k < prdctNm.Length; k++)
{
if (prdctNm[k].Trim() != "")
{
SqlCommand cmd1 = new SqlCommand("IUPatientBillsDetailsMedical", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@HospitalID", HospitalID);
cmd1.Parameters.AddWithValue("@LocationID", LocationID);
cmd1.Parameters.AddWithValue("@Mode", "ADD");
cmd1.Parameters.AddWithValue("@PatientBillsDetailsMedicalID", 0);
cmd1.Parameters.AddWithValue("@MedicalBillID", Convert.ToInt32(MedBillID));
if (obj.BillNo == null || obj.BillNo == "" || obj.BillNo == "--New--")
{
cmd1.Parameters.AddWithValue("@BillNo", 0);
}
else
{
cmd1.Parameters.AddWithValue("@BillNo", obj.BillNo);
}
cmd1.Parameters.AddWithValue("@ProductID", obj.tblProductID.Split(',')[k]);
cmd1.Parameters.AddWithValue("@ProductName", obj.tblProductName.Split(',')[k]);
cmd1.Parameters.AddWithValue("@ProductUnitID", 1);
cmd1.Parameters.AddWithValue("@ProductDetailID", obj.ProductDetailsID);
cmd1.Parameters.AddWithValue("@BatchNo", obj.tblbatchNumber.Split(',')[k]);
cmd1.Parameters.AddWithValue("@ExpiryDate", obj.tblexpiryDate.Split(',')[k]);
cmd1.Parameters.AddWithValue("@MRP", obj.tblmrp.Split(',')[k]);
cmd1.Parameters.AddWithValue("@SalesRate", obj.tblnetRate.Split(',')[k]);
cmd1.Parameters.AddWithValue("@Quantity", obj.tblQtyU.Split(',')[k]);
cmd1.Parameters.AddWithValue("@QuantityL", obj.tblQtyL.Split(',')[k]);
cmd1.Parameters.AddWithValue("@FreeQuantity", obj.tblfreeQuantity.Split(',')[k]);
cmd1.Parameters.AddWithValue("@DiscountPer", obj.tbldiscountPercentage.Split(',')[k]);
if (obj.PatientRegNO == "" || obj.PatientRegNO == null)
{
cmd1.Parameters.AddWithValue("@BillType", "GeneralMedicalBill");
}
else
{
cmd1.Parameters.AddWithValue("@BillType", dt.Rows[0]["PatientType"].ToString() + "MedicalBill");
}
cmd1.Parameters.AddWithValue("@TotalAmount", obj.tbltotalAmount.Split(',')[k]);
cmd1.Parameters.AddWithValue("@CreationID", UserID);
con.Open();
int j = cmd1.ExecuteNonQuery();
con.Close();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(true);
}
public ActionResult PatientMedicalBill(PatientMedicalBill obj, FormCollection fc)
{
try
{
//obj.MedicalBillID = Convert.ToInt32(fc["MedicalBillID"]);
//obj.PatientRegNO = fc["PatientRegNO"].ToString();
//obj.PatientName = fc["PatientName"].ToString();
//obj.PatientType = fc["PatientType"].ToString();
//obj.DoctorPrintName = fc["DoctorPrintName"].ToString();
//obj.DoctorID = fc["DoctorID"].ToString();
//obj.Address = fc["address"].ToString();
obj.BillNo = fc["oldBillNumber"].ToString();
obj.BillNoDate = fc["billDate"].ToString();
//obj.message = fc["message"].ToString();
//obj.CustomerName = fc["CustomerName"].ToString();
//obj.CustomerID = fc["CustomerID"].ToString();
//obj.SalesPersonName = fc["SalesPersonName"].ToString();
//obj.SalesPersonID = fc["SalesPersonID"].ToString();
//obj.grossTotal = fc["grossTotal"].ToString();
//obj.totalAmount = fc["totalAmount"].ToString();
//obj.discountAmount = fc["discountAmount"].ToString();
//obj.discountPercentage = fc["discountPercentage"].ToString();
//obj.vatAmount = fc["vatAmount"].ToString();
obj.otheLess = fc["otheLess"].ToString();
//obj.salesReturn = fc["salesReturn"].ToString();
//obj.netAmount = fc["netAmount"].ToString();
//obj.paymentType = fc["paymentType"].ToString();
//obj.cashRecipt = fc["cashRecipt"].ToString();
//obj.scanBarcode = fc["scanBarcode"].ToString();
//obj.barcodeNumber = fc["barcodeNumber"].ToString();
//obj.Number = fc["Number"].ToString();
//obj.Name = fc["Name"].ToString();
//obj.paymentDate = fc["paymentDate"].ToString();
//obj.Remarks = fc["Remarks"].ToString();
//obj.ProductName = fc["ProductName"].ToString();
//obj.ProductID = fc["ProductID"].ToString();
//obj.PatientBillsDetailsMedicalID = Convert.ToInt32(fc["PatientBillsDetailsMedicalID"]);
//obj.batchNumber = fc["batchNumber"].ToString();
//obj.ProductDetailsID = fc["ProductDetailsID"].ToString();
//obj.expiryDate = fc["expiryDate"].ToString();
//obj.mrp = fc["mrp"].ToString();
//obj.netRate = fc["netRate"].ToString();
//obj.Prod_discountPercentage = fc["Prod_discountPercentage"].ToString();
//obj.QtyU = fc["QtyU"].ToString();
//obj.QtyL = fc["QtyL"].ToString();
//obj.freeQuantity = fc["freeQuantity"].ToString();
obj.tblProductName = fc["tblProductName"].ToString();
obj.tblProductID = fc["tblProductID"].ToString();
obj.tblbatchNumber = fc["tblbatchNumber"].ToString();
obj.tblmrp = fc["tblmrp"].ToString();
obj.tblexpiryDate = fc["tblexpiryDate"].ToString();
obj.tblnetRate = fc["tblnetRate"].ToString();
obj.tblQtyU = fc["tblQtyU"].ToString();
obj.tblQtyL = fc["tblQtyL"].ToString();
obj.tblGST = fc["tblGST"].ToString();
obj.tbldiscountPercentage = fc["tbldiscountPercentage"].ToString();
obj.tbltotalAmount = fc["tbltotalAmount"].ToString();
obj.tblDiscountValue = fc["tblDiscountValue"].ToString();
obj.tblfreeQuantity = fc["tblfreeQuantity"].ToString();
int saleID = obj.MedicalBillID;
if (BL_obj.Save(obj))
{
if (saleID == 0)
{
ModelState.Clear();
TempData["msg"] = "Medical Bill Saved Successfully !";
Session["medID"] = obj.MedicalBillID;
return(RedirectToAction("medicalBill", "PharmacyReport"));
}
else
{
ModelState.Clear();
TempData["msg"] = "Medical Bill Updated Successfully !";
Session["medID"] = obj.MedicalBillID;
return(RedirectToAction("medicalBill", "PharmacyReport"));
}
}
else
{
ModelState.Clear();
TempData["msg"] = "Medical Bill Not Saved !";
}
}
catch (Exception ex)
{
return(RedirectToAction("PatientMedicalBill", "PatientMedicalBill"));
}
return(RedirectToAction("PatientMedicalBill", "PatientMedicalBill"));
}