public int CreateUpdateProductTypeServices(ProductType ud)
{
int ProductTypeID = 0;
using (FDBEntities db = new FDBEntities())
{
if (ud.ProductTypeID > 0)
{
ProductType temp = db.ProductTypes.Where(u => u.ProductTypeID == ud.ProductTypeID).FirstOrDefault();
if (temp != null)
{
temp.Description = ud.Description;
}
}
else
{
db.ProductTypes.Add(ud);
}
int x = db.SaveChanges();
if (x > 0)
{
ProductTypeID = ud.ProductTypeID;
}
}
return ProductTypeID;
}
public List<FACILITY> GetFacility(string FACILITY_KEY)
{
using(FDBEntities db = new FDBEntities())
{
return db.FACILITies.Where(u => u.FACILITY_KEY == FACILITY_KEY || FACILITY_KEY=="").ToList();
}
}
public int CreateUpdateNDCDiscount(NDC_Discounts ud)
{
int NDC_DiscountID = 0;
using (FDBEntities db = new FDBEntities())
{
if (ud.NDC_DiscountID > 0)
{
NDC_Discounts temp = db.NDC_Discounts.Where(u => u.NDC_DiscountID == ud.NDC_DiscountID).FirstOrDefault();
if (temp != null)
{
temp.NDC = ud.NDC;
temp.Discount_Percent = ud.Discount_Percent;
temp.FacilityID = ud.FacilityID;
temp.ProductTypeID = ud.ProductTypeID;
}
}
else
{
db.NDC_Discounts.Add(ud);
}
int x = db.SaveChanges();
if (x > 0)
{
NDC_DiscountID = ud.NDC_DiscountID;
}
}
return NDC_DiscountID;
}
public List<ProductType> GetProductType(int ProductTypeId)
{
List<ProductType> rType = new List<ProductType>();
using (FDBEntities db = new FDBEntities())
{
rType = db.ProductTypes.Where(p => p.ProductTypeID == ProductTypeId || ProductTypeId == 0).ToList();
}
return rType;
}
public List<NDC_M_PRICE> GetNDCPrice(string NDC)
{
List<NDC_M_PRICE> rType = new List<NDC_M_PRICE>();
using (FDBEntities db =new FDBEntities())
{
rType = db.NDC_M_PRICE.Where(n => n.NDC == NDC || NDC == "").ToList();
}
return rType;
}
public List<Medicine> GetMedicine(int MedicineID)
{
List<Medicine> rType = new List<Medicine>();
using (FDBEntities db = new FDBEntities())
{
rType = db.Medicines.Where(n => n.MedicineID == MedicineID || MedicineID == 0).ToList();
}
return rType;
}
public List<NDC_Discounts> GetNDCDiscounts(int NDC_DiscountID, string NDC)
{
List<NDC_Discounts> rType = new List<NDC_Discounts>();
using (FDBEntities db = new FDBEntities())
{
// rType = db.NDC_Discounts.ToList();
rType = (from n in db.NDC_Discounts
where ((n.NDC_DiscountID == NDC_DiscountID || NDC_DiscountID == 0) && (NDC == "" || n.NDC.Contains(NDC)))
select n).ToList();
}
return rType;
}
public LoggedInUser AuthenticateUser(string username, string password, bool isPersistent)
{
LoggedInUser user = null;
try
{
using (FDBEntities db = new FDBEntities())
{
List<User> loginuser = db.Users.Where(u => u.LoginID == username && u.Password == password).ToList();
if (loginuser.Count() > 0)
{
FormsAuthentication.Initialize();
HttpCookie authCookie;
StringBuilder userString;
FormsAuthenticationTicket ticket;
string ticketHash;
XmlSerializer serializer = new XmlSerializer(typeof(LoggedInUser));
user = new LoggedInUser();
user.UserID = loginuser[0].UserID;
user.Name = loginuser[0].FirstName + " " + loginuser[0].FirstName;
user.Email = loginuser[0].Email;
user.Roles = "Admin".Split(',').ToArray() ;
userString = new StringBuilder();
// SaveLogin(email, password, isPersistent);
using (StringWriter stringWriter = new StringWriter(userString))
{
// Serialize the User
serializer.Serialize(stringWriter, user);
}
if (isPersistent == true)
{
ticket = new FormsAuthenticationTicket(
1, loginuser[0].UserID.ToString(),
DateTime.Now,
DateTime.Now.AddMonths(3),
isPersistent,
userString.ToString(),
FormsAuthentication.FormsCookiePath);
ticketHash = FormsAuthentication.Encrypt(ticket);
authCookie = new HttpCookie(FormsAuthentication.FormsCookieName, ticketHash);
authCookie.Expires = DateTime.Now.AddMonths(3);
}
else
{
ticket = new FormsAuthenticationTicket(
1, loginuser[0].UserID.ToString(),
DateTime.Now,
DateTime.Now.AddMinutes(HttpContext.Current.Session.Timeout),
isPersistent,
userString.ToString(),
FormsAuthentication.FormsCookiePath);
ticketHash = FormsAuthentication.Encrypt(ticket);
authCookie = new HttpCookie(FormsAuthentication.FormsCookieName, ticketHash);
}
if (isPersistent)
{
authCookie.Expires = ticket.Expiration;
}
HttpContext.Current.Response.Cookies.Add(authCookie);
if (HttpContext.Current.User != null)
{
HttpContext.Current.User = new GenericPrincipal(HttpContext.Current.User.Identity, user.Roles);
}
}
}
}
catch (Exception ex) { throw ex; }
return user;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
var lstReturnMedicine = new List<clsMedicine>();
if (FUControl.HasFile == true)
{
string FilePath = System.Web.HttpContext.Current.Server.MapPath("Uploads");
string FileName = FUControl.FileName;
string FullFilePath = FilePath + "\\" + FileName;
FUControl.SaveAs(FullFilePath);
var vFacilityCode = string.Empty;
var strConnection = string.Empty;
if (FullFilePath.Contains(".xlsx"))
{
strConnection = string.Concat(str0: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=", str1: FullFilePath, str2: ";Extended Properties=", str3: ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"");
}
else
{
strConnection = string.Concat(str0: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", str1: FullFilePath, str2: ";Extended Properties=", str3: "Excel 8.0;");
}
var dt = new System.Data.DataTable("dtSheet");
using (var conn = new OleDbConnection(strConnection))
{
conn.Open();
var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var lstSheet = new List<string>();
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))
{
lstSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
using (var da = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", lstSheet[0]), conn))
{
da.Fill(dt);
}
try
{
dt = dt.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((field as string).Trim(), string.Empty) == 0)).CopyToDataTable();
}
catch { }
if (dt != null && dt.Rows.Count > 0)
{
vFacilityCode = Convert.ToString(dt.Rows[1][1]);
dt.Columns.Add("MSG", typeof(string));
dt.Columns.Add("Pharmacy Unit Price", typeof(string));
//dt.Columns.Add("FDB Unit Price", typeof(string));
dt.Columns.Add("Unit Price", typeof(string));
dt.Columns.Add("Original Unit Price", typeof(string));
dt.Columns.Add("Price", typeof(string));
dt.Columns.Add("PercentVariance", typeof(string));
dt.Columns.Add("Variance", typeof(string));
FDBEntities db = new FDBEntities();
var objFacility = FacilityService.Instance.GetFacility(vFacilityCode.Trim());
int MedicineCount = db.Medicines.Count();
if (MedicineCount > 0)
{
clsMedicine objMedicine = null;
for (var i = 0; i < dt.Rows.Count; i++)
{
try
{
objMedicine = new clsMedicine();
objMedicine.Chain = dt.Rows[i][0] != DBNull.Value ? Convert.ToString(dt.Rows[i][0]) : string.Empty;
objMedicine.Facility = dt.Rows[i][1] != DBNull.Value ? Convert.ToString(dt.Rows[i][1]) : string.Empty;
objMedicine.Month = dt.Rows[i][2] != DBNull.Value ? DateTime.Parse(Convert.ToString(dt.Rows[i][2])) : default(Nullable<DateTime>);
objMedicine.RX = dt.Rows[i][3] != DBNull.Value ? Convert.ToString(dt.Rows[i][3]) : string.Empty;
try
{
objMedicine.Date = dt.Rows[i][4] != DBNull.Value ? DateTime.Parse(Convert.ToString(dt.Rows[i][4])) : default(Nullable<DateTime>);
}
catch { }
objMedicine.PatientID = dt.Rows[i][5] != DBNull.Value ? Convert.ToInt32(dt.Rows[i][5]) : new Nullable<Int32>();
objMedicine.PatientLast = dt.Rows[i][6] != DBNull.Value ? Convert.ToString(dt.Rows[i][6]) : string.Empty;
objMedicine.PatientFirst = dt.Rows[i][7] != DBNull.Value ? Convert.ToString(dt.Rows[i][7]) : string.Empty;
objMedicine.NDC = dt.Rows[i][8] != DBNull.Value ? Convert.ToString(dt.Rows[i][8]) : string.Empty;
objMedicine.Medication = dt.Rows[i][9] != DBNull.Value ? Convert.ToString(dt.Rows[i][9]) : string.Empty;
objMedicine.Quantity = dt.Rows[i][10] != DBNull.Value ? Double.Parse(Convert.ToString(dt.Rows[i][10])) : default(Nullable<double>);
objMedicine.Cost = dt.Rows[i][11] != DBNull.Value ? Double.Parse(Convert.ToString(dt.Rows[i][11])) : default(Nullable<Double>);
objMedicine.Physician = dt.Rows[i][12] != DBNull.Value ? Convert.ToString(dt.Rows[i][12]) : string.Empty;
objMedicine.PhysicianNPI = dt.Rows[i][13] != DBNull.Value ? Convert.ToString(dt.Rows[i][13]) : string.Empty;
objMedicine.PayType = dt.Rows[i][14] != DBNull.Value ? Convert.ToString(dt.Rows[i][14]) : string.Empty;
objMedicine.DaysSupply = dt.Rows[i][15] != DBNull.Value ? Convert.ToString(dt.Rows[i][15]) : string.Empty;
objMedicine.RouteOfAdmin = dt.Rows[i][16] != DBNull.Value ? Convert.ToString(dt.Rows[i][16]) : string.Empty;
objMedicine.InventoryCategory = dt.Rows[i][17] != DBNull.Value ? Convert.ToString(dt.Rows[i][17]) : string.Empty;
objMedicine.Copay = dt.Rows[i][18] != DBNull.Value ? Convert.ToString(dt.Rows[i][18]) : string.Empty;
objMedicine.Brand = dt.Rows[i][19] != DBNull.Value ? Convert.ToString(dt.Rows[i][19]) : string.Empty;
Medicine objMedicine_5 = new Medicine();
Medicine objMedicine_7 = new Medicine();
Medicine objMedicine_9 = new Medicine();
double? FDBPrice = 0;
double? BillPrice = 0;
var ndc = Convert.ToString(dt.Rows[i][8]);
objMedicine_5 = (from item in
db.Medicines
where item.NDC == ndc
&& item.PriceType == 5
orderby item.Date
descending
select item).FirstOrDefault();
if (objMedicine_5 != null)
{
objMedicine_5.Price = (objMedicine_5.Price * 1.2) + 5;
}
objMedicine_7 = (from item in db.Medicines
where item.NDC == ndc
&& item.PriceType == 7
orderby item.Date
descending
select item).FirstOrDefault();
if (objMedicine_7 != null)
{
objMedicine_7.Price = (objMedicine_7.Price * 1.2) + 5;
}
objMedicine_9 = (from item in
db.Medicines
where item.NDC == ndc
&& item.PriceType == 9
orderby item.Date
descending
select item)
.FirstOrDefault();
if (objMedicine_9 != null)
{
objMedicine_9.Price = (objMedicine_9.Price * 1.2) + 5;
}
try
{
objMedicine.PharmacyUnitPrice = Math.Round(objMedicine.Cost.Value / objMedicine.Quantity.Value, 3);
}
catch { objMedicine.PharmacyUnitPrice = 0; }
BillPrice = objMedicine.Cost;
try
{
if (Convert.ToString(dt.Rows[i]["Brand"]).ToUpper() == "Y")
{
if (objMedicine_9 != null)
{
objMedicine.FDBUnitPrice = objMedicine_9.Price - (objMedicine_9.Price * 5 / 100);
FDBPrice = objMedicine.Quantity * objMedicine_9.Price;
}
else
{
objMedicine.FDBUnitPrice = 0;
FDBPrice = 0;
}
}
else
{
if (objMedicine_7 != null)
{
objMedicine.FDBUnitPrice = objMedicine_7.Price;
FDBPrice = objMedicine.Quantity * objMedicine_7.Price;
}
else
{
objMedicine.FDBUnitPrice = 0;
FDBPrice = 0;
}
}
if (objMedicine_7 != null)
{
objMedicine.SSG = objMedicine_7.Price - (objMedicine_7.Price * 20 / 100);
}
else
{
objMedicine.SSG = 0;
}
if (objMedicine_5 != null)
{
objMedicine.MSG = objMedicine_5.Price - (objMedicine_5.Price * 5 / 100);
}
else
{
objMedicine.MSG = 0;
}
}
catch { }
objMedicine.FDBPrice = FDBPrice;
objMedicine.PharmacyPrice = BillPrice;
objMedicine.Variance = CalculateVariance(FDBPrice, BillPrice);
double PercentVariance = 0;
try
{
PercentVariance = (double)((FDBPrice - BillPrice) / BillPrice);
}
catch { }
objMedicine.PercentVariance = PercentVariance;
lstReturnMedicine.Add(objMedicine);
}
catch (Exception ex)
{
//AddtoLogFile("Upload Error:" + ex.ToString());
}
}
}
}
GridView1.DataSource = lstReturnMedicine;
GridView1.DataBind();
}
}
}
