public IEnumerable <EMPLOYEESTABLE> Get()
{
using (mydbEntities entities = new mydbEntities())
{
return(entities.EMPLOYEESTABLE.ToList());
}
}
public List <SupllierDataView> GetAllSupplierByName(string name)
{
List <SupllierDataView> Supllier = new List <SupllierDataView>();
List <Supllier> pList = new List <Supllier>();
using (mydbEntities db = new mydbEntities())
{
SupllierDataView PSR = new SupllierDataView();
var p1 = db.Suplliers.Where(o => o.SupllierName.Contains(name));
if (p1.Any())
{
pList = p1.ToList();
foreach (Supllier p in pList)
{
SupllierDataView PR = new SupllierDataView();
PR.SupllierID = p.SupllierID;
PR.SupllierName = p.SupllierName;
PR.SupllierPhone = p.SupllierPhone;
PR.SupllierAddress = p.SupllierAddress;
Supllier.Add(PR);
}
}
}
return(Supllier);
}
public UserDataView GetUserDataView(string loginName)
{
UserDataView UDV = new UserDataView();
List <UserProfileView> profiles = GetAllUserProfiles();
List <LOOKUPAvailableRole> roles = GetAllRoles();
int? userAssignedRoleID = 0, userID = 0;
string userGender = string.Empty;
userID = GetUserID(loginName);
using (mydbEntities db = new mydbEntities())
{
userAssignedRoleID = db.SYSUserRoles.Where(o => o.SYSUserID == userID)?.FirstOrDefault().LOOKUPRoleID;
userGender = db.SYSUserProfiles.Where(o => o.SYSUserID == userID)?.FirstOrDefault().Gender;
}
List <Gender> genders = new List <Gender>();
genders.Add(new Gender {
Text = "Male", Value = "M"
});
genders.Add(new Gender {
Text = "Female", Value = "F"
});
UDV.UserProfile = profiles;
UDV.UserRoles = new UserRoles {
SelectedRoleID = userAssignedRoleID, UserRoleList = roles
};
UDV.UserGender = new UserGender {
SelectedGender = userGender, Gender = genders
};
return(UDV);
}
public List <SupllierDataView> GetAllSuplliers()
{
List <SupllierDataView> supllier = new List <SupllierDataView>();
using (mydbEntities db = new mydbEntities())
{
SupllierDataView SR;
var sups = db.Suplliers.ToList();
if (sups != null)
{
foreach (Supllier s in sups)
{
SR = new SupllierDataView();
SR.SupllierID = s.SupllierID;
SR.SupllierName = s.SupllierName;
SR.SupllierPhone = s.SupllierPhone;
SR.SupllierAddress = s.SupllierAddress;
supllier.Add(SR);
}
}
}
return(supllier);
}
public UserProfileView GetUserProfile(int userID)
{
UserProfileView UPV = new UserProfileView();
using (mydbEntities db = new mydbEntities())
{
var user = db.SYSUsers.Find(userID);
if (user != null)
{
UPV.SYSUserID = user.SYSUserID;
UPV.LoginName = user.LoginName;
UPV.Password = user.PasswordEncryptedText;
var SUP = db.SYSUserProfiles.Find(userID);
if (SUP != null)
{
UPV.FirstName = SUP.FirstName;
UPV.LastName = SUP.LastName;
UPV.Gender = SUP.Gender;
}
var SUR = db.SYSUserRoles.Find(userID);
if (SUR != null)
{
UPV.LOOKUPRoleID = SUR.LOOKUPRoleID;
UPV.RoleName = SUR.LOOKUPRole.RoleName;
UPV.IsRoleActive = SUR.IsActive;
}
}
}
return(UPV);
}
public bool IsLoginNameExist(string loginName)
{
using (mydbEntities db = new mydbEntities())
{
return(db.SYSUsers.Where(o => o.LoginName.Equals(loginName)).Any());
}
}
public List <ProductDataView> GetAllProductsByName(string name)
{
List <ProductDataView> products = new List <ProductDataView>();
List <Product> pList = new List <Product>();
using (mydbEntities db = new mydbEntities())
{
ProductsDataView PSR = new ProductsDataView();
var p1 = db.Products.Where(o => o.product_name.Contains(name));
if (p1.Any())
{
pList = p1.ToList();
foreach (Product p in pList)
{
ProductDataView PR = new ProductDataView();
PR.product_id = p.product_id;
PR.product_name = p.product_name;
PR.product_price = p.product_price;
PR.product_address = p.product_address;
products.Add(PR);
}
}
return(products);
}
}
public static void updateData()
{
mydbEntities m3 = new mydbEntities();
Product p = m3.Products.Find(2);
p.Name = "Headphones";
m3.SaveChanges();
}
// GET: Demo
public ActionResult Details(int Id)
{
doctor_Register frnds = new doctor_Register();
mydbEntities db = new mydbEntities();
frnds = db.doctor_Register.Find(Id);
return(PartialView("_Details", frnds));
}
public void deleteItem(int id)
{
var context = new mydbEntities();
inventario itemDelete = context.inventario.Find(id);
if (itemDelete != null)
{
context.inventario.Remove(itemDelete);
context.SaveChanges();
}
}
public void addItem(String product, int quantity)
{
var context = new mydbEntities();
var sql = @"INSERT INTO [inventario] (PRODUCT,QUANTITY,MODIFIED_DATE) VALUES (@Product,@Quantity,@Datetime)";
context.Database.ExecuteSqlCommand(sql,
new SqlParameter("@Product", product),
new SqlParameter("@Quantity", quantity),
new SqlParameter("@Datetime", DateTime.Now)
);
}
public static void insertData()
{
mydbEntities m1 = new mydbEntities();
Product p = new Product();
p.Id = 09;
p.Name = "Book";
p.Price = 400;
p.Qty = 9;
m1.Products.Add(p);
m1.SaveChanges();
}
public int GetUserID(string loginName)
{
using (mydbEntities db = new mydbEntities())
{
var user = db.SYSUsers.Where(o => o.LoginName.Equals(loginName));
if (user.Any())
{
return(user.FirstOrDefault().SYSUserID);
}
}
return(0);
}
static void Main(string[] args)
{
mydbEntities m = new mydbEntities();
List <Product> l = m.Products.ToList <Product>();
foreach (var r in l)
{
Console.WriteLine("{0} {1} {2} {3}", r.Id, r.Name, r.Price, r.Qty);
}
//insertData();
// deleteData();
updateData();
}
public void updateItem(int id, String product, int quantity)
{
var context = new mydbEntities();
var sql = @"UPDATE [inventario] SET PRODUCT=@Product,QUANTITY=@Quantity,MODIFIED_DATE=@Datetime WHERE ID=@Id";
context.Database.ExecuteSqlCommand(sql,
new SqlParameter("@Product", product),
new SqlParameter("@Quantity", quantity),
new SqlParameter("@Datetime", DateTime.Now),
new SqlParameter("@Id", id)
);
}
public List <LOOKUPAvailableRole> GetAllRoles()
{
using (mydbEntities db = new mydbEntities())
{
var roles = db.LOOKUPRoles.Select(o => new LOOKUPAvailableRole
{
LOOKUPRoleID = o.LOOKUPRoleID,
RoleName = o.RoleName,
RoleDescription = o.RoleDescription
}).ToList();
return(roles);
}
}
public static void deleteData()
{
mydbEntities m2 = new mydbEntities();
Product p = m2.Products.Find(1);
try
{
m2.Products.Remove(p);
m2.SaveChanges();
}
catch
{
Console.WriteLine("No Record find");
}
}
public string GetUserPassword(string loginName)
{
using (mydbEntities db = new mydbEntities())
{
var user = db.SYSUsers.Where(o => o.LoginName.ToLower().Equals(loginName));
if (user.Any())
{
return(user.FirstOrDefault().PasswordEncryptedText);
}
else
{
return(string.Empty);
}
}
}
public ActionResult DoctorRegister(doctor_Register doctor_Register)
{
using (mydbEntities mydbEntities = new mydbEntities())
{
if (mydbEntities.doctor_Register.Any(x => x.mob_Number == doctor_Register.mob_Number))
{
ViewBag.DuplicateMessage = "mobile number already exits";
return(View("DoctorRegister", new doctor_Register()));
}
mydbEntities.doctor_Register.Add(doctor_Register);
mydbEntities.SaveChanges();
}
ModelState.Clear();
ViewBag.SuccessMessage = "Registration SuccessFull";
return(View("DoctorRegister", new doctor_Register()));
}
public int GetUserRule(int ID)
{
using (mydbEntities db = new mydbEntities())
{
var Roles = db.SYSUserRoles.Where(o => o.SYSUserID.Equals(ID));
if (Roles.Any())
{
return(Roles.FirstOrDefault().SYSUserRoleID);
}
else
{
return(-1);
}
}
}
public static ArrayList GetCoffeeByType(string coffeetype)
{
ArrayList list = new ArrayList();
try
{
mydbEntities db = new mydbEntities();
var query = from p in db.tableabc select p;
list = query.ToList();
}
catch (Exception ex)
{
Alert.Show(ex.Message);
}
return(list);
}
public List <ProductToSupllier> GetSuplliersAndProductsbyname(string SupllierName, string productname, int productprice)
{
List <ProductToSupllier> PTSList = new List <ProductToSupllier>();
ProductsToSupllier PSTS = new ProductsToSupllier();
//ProductsToSupllier PTS = new ProductsToSupllier();
using (mydbEntities db = new mydbEntities())
{
var query =
from p in db.Products
join su in db.Suplliers on p.SupllierID equals su.SupllierID
select new
{
PID = p.product_id,
PNAME = p.product_name,
PPRICE = p.product_price,
SID = su.SupllierID,
SNAME = su.SupllierName,
SAD = su.SupllierAddress,
};
if (!query.Any() || SupllierName == null || productname == null || productprice == 0)
{
return(PTSList);
}
//query.GroupBy(SNAME);
var a = query.ToList();
for (int i = 0; i < a.Count; i++)
{
if (a[i].PNAME.Contains(productname) && a[i].SNAME.Contains(SupllierName) && a[i].PPRICE < productprice)
{
ProductToSupllier PTS = new ProductToSupllier();
PTS.PID = a[i].PID;
PTS.PNAME = a[i].PNAME;
PTS.PPRICE = a[i].PPRICE;
PTS.SID = a[i].SID;
PTS.SNAME = a[i].SNAME;
PTS.SAD = a[i].SAD;
PTSList.Add(PTS);
}
}
}
PSTS.PTSList = PTSList;
return(PTSList);
}
public void AddUserAccount(UserSignUpView user)
{
using (mydbEntities db = new mydbEntities())
{
SYSUser SU = new SYSUser();
SU.LoginName = user.LoginName;
SU.PasswordEncryptedText = user.Password;
SU.RowCreatedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SU.RowModifiedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;;
SU.RowCreatedDateTime = DateTime.Now;
SU.RowMOdifiedDateTime = DateTime.Now;
db.SYSUsers.Add(SU);
db.SaveChanges();
SYSUserProfile SUP = new SYSUserProfile();
SUP.SYSUserID = SU.SYSUserID;
SUP.FirstName = user.FirstName;
SUP.LastName = user.LastName;
SUP.Gender = user.Gender;
SUP.RowCreatedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUP.RowModifiedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUP.RowCreatedDateTime = DateTime.Now;
SUP.RowModifiedDateTime = DateTime.Now;
db.SYSUserProfiles.Add(SUP);
db.SaveChanges();
if (user.LOOKUPRoleID > 0)
{
SYSUserRole SUR = new SYSUserRole();
SUR.LOOKUPRoleID = user.LOOKUPRoleID;
SUR.SYSUserID = SU.SYSUserID;
SUR.IsActive = true;
SUR.RowCreatedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUR.RowModifiedSYSUserID = user.SYSUserID > 0 ? user.SYSUserID : 1;
SUR.RowCreatedDateTime = DateTime.Now;
SUR.RowModifiedDateTime = DateTime.Now;
db.SYSUserRoles.Add(SUR);
db.SaveChanges();
}
}
}
private bool IsValid(string username, string password)
{
var crypto = new SimpleCrypto.PBKDF2();
bool IsValid = false;
using (var db = new mydbEntities())
{
var user = db.userprofiles.FirstOrDefault(u => u.UserName == username);
if (user != null)
{
// if (user.Password == crypto.Compute(password, user.PasswordSalt))
// {
IsValid = true;
//}
}
}
return(IsValid);
}
public void AddSupllier(SupllierDataView supllier)
{
using (mydbEntities db = new mydbEntities())
{
var intIdt = db.Suplliers.Max(u => (int)u.SupllierID);
Supllier SR = new Supllier();
SR.SupllierID = intIdt + 1;
SR.SupllierName = supllier.SupllierName;
SR.SupllierPhone = supllier.SupllierPhone;
SR.SupllierAddress = supllier.SupllierAddress;
db.Suplliers.Add(SR);
db.SaveChanges();
}
}
protected override bool AuthorizeCore(HttpContextBase httpContext)
{
bool authorize = false;
using (mydbEntities db = new mydbEntities())
{
UserManager UM = new UserManager();
foreach (var roles in userAssignedRoles)
{
authorize = UM.IsUserInRole(httpContext.User.Identity.Name, roles);
if (authorize)
{
return(authorize);
}
}
}
return(authorize);
}
public ProductDataView GetProductData(int productID)
{
ProductDataView PD = new ProductDataView();
using (mydbEntities db = new mydbEntities())
{
var product = db.Products.Find(productID);
if (product != null)
{
PD.product_id = product.product_id;
PD.product_name = product.product_name;
PD.product_price = product.product_price;
PD.product_address = product.product_address;
}
}
return(PD);
}
public void AddProduct(ProductDataView product)
{
using (mydbEntities db = new mydbEntities())
{
var intIdt = db.Products.Max(u => (int)u.product_id);
Product PR = new Product();
PR.product_id = intIdt + 1;
PR.product_name = product.product_name;
PR.product_price = product.product_price;
PR.product_address = product.product_address;
PR.SupllierID = product.SupllierID;
db.Products.Add(PR);
db.SaveChanges();
}
}
public ActionResult Register(userprofile user)
{
try
{
if (ModelState.IsValid)
{
//mydbEntities db = new mydbEntities()
using (var db = new mydbEntities())
{
var newUser = db.userprofiles.Create();
newUser.UserName = user.UserName;
newUser.Password = user.Password;
newUser.IsActive = true;
db.userprofiles.Add(newUser);
db.SaveChanges();
return(RedirectToAction("Index", "Home"));
}
}
else
{
ModelState.AddModelError("", "Data is not correct");
}
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
return(View());
}
public bool IsUserInRole(string loginName, string roleName)
{
using (mydbEntities db = new mydbEntities())
{
SYSUser SU = db.SYSUsers.Where(o => o.LoginName.ToLower().Equals(loginName))?.FirstOrDefault();
if (SU != null)
{
var roles = from q in db.SYSUserRoles
join r in db.LOOKUPRoles on q.LOOKUPRoleID equals r.LOOKUPRoleID
where r.RoleName.Equals(roleName) && q.SYSUserID.Equals(SU.SYSUserID)
select r.RoleName;
if (roles != null)
{
return(roles.Any());
}
}
return(false);
}
}
