public List <Model.User> Get_Data_User(user user)
{
_DB = new QLKHEntities();
List <Model.User> lst_user = new List <Model.User>();
try
{
lst_user = (from use in _DB.users
where (use.Status == true && use.id != user.id)
select new Model.User
{
UserName = use.UserName,
FullName = use.FullName,
id = use.id,
CMND = use.IdCard,
BirthDay = use.BirthDay,
PhoneNumber = use.PhoneNumber,
Sex = use.Sex,
SexString = use.Sex ? "Nam" : "Nữ",
Address = use.Address,
Note = use.Note,
Type = use.Type
}).AsEnumerable().ToList();
}
catch (Exception ex)
{
throw;
}
return(lst_user);
}
public LoginDTO UserLogin(string username, string password)
{
LoginDTO response = new LoginDTO();
try
{
using (var _db = new QLKHEntities())
{
var user = _db.Users.Where(o => o.Username == username && o.Password == password && o.IsDelete == false)
.Join(_db.Roles, u => u.RoleID, r => r.ID, (u, r) => new { u, r }).FirstOrDefault();
if (user != null)
{
response.Success = true;
response.Name = user.u.Username;
response.Role = user.r.Name;
response.Code = user.r.Code;
}
else
{
response.Message = "Username or password is incorrect.";
response.Caption = "Login failed!";
response.MessageButton = MessageBoxButton.OK;
response.MessageImage = MessageBoxImage.Warning;
}
}
}
catch (Exception ex)
{
response.Message = ex.Message;
response.Caption = "Error!";
response.MessageButton = MessageBoxButton.OK;
response.MessageImage = MessageBoxImage.Error;
}
return(response);
}
public async Task <ActionResult> ExternalLoginCallback(string returnUrl)
{
var loginInfo = await AuthenticationManager.GetExternalLoginInfoAsync();
if (loginInfo == null)
{
return(RedirectToAction("Login", "Account"));
}
using (QLKHEntities db = new QLKHEntities())
{
LyLichKhoaHoc user = db.LyLichKhoaHocs.Where(m => m.EmailCQ == loginInfo.Email).FirstOrDefault();
if (user == null)
{
TempData["LoginFail"] = "1";
return(RedirectToAction("Login", "Account"));
}
else
{
TempData["LoginFail"] = "0";
Session["LoginByGoogle"] = user;
return(RedirectToAction("Index", "Home"));
}
}
}
public List <Model.HistoryPaid> Get_List_History_Paid(int idCustomer)
{
List <Model.HistoryPaid> lst_History_Paid = new List <Model.HistoryPaid>();
try
{
_db = new QLKHEntities();
lst_History_Paid = (from data in _db.historyPaids
join data_user in _db.users
on data.CreatedBy equals data_user.id
join data_customer in _db.customers
on data.CustomerId equals data_customer.id
where data.CustomerId == idCustomer
orderby data.PaidDate descending
select new Model.HistoryPaid
{
IdHistory = data.id,
NameCustomer = data_customer.FullName,
IdCard = data_customer.IdCard,
PhoneNumber = data_customer.PhoneNumber,
PaidDate = data.PaidDate,
PaidMoney = data.Money,
NoteHistory = data.Note,
TypePaid = data.TypePaid == "0" ? "Tiền gốc" : "Tiền lãi",
CreatedByUser = data_user.FullName,
CreatedAtHistory = data.CreatedAt
}).AsEnumerable().ToList();
}
catch (Exception ex)
{
throw ex;
}
return(lst_History_Paid);
}
public List <Model.Category> Get_List_Category()
{
List <Model.Category> lst_Category = new List <Model.Category>();
try
{
_db = new QLKHEntities();
lst_Category = (from data in _db.categories
join user_create in _db.users
on data.CreatedBy equals user_create.id
join user_update in _db.users
on data.UpdatedBy equals user_update.id
where (data.Status == true)
select new Model.Category
{
id = data.id,
Name = data.Name,
CreatedAt = data.CreatedAt,
UpdatedAt = data.UpdatedAt,
Note = data.Note,
Status = data.Status,
CreatedBy = data.CreatedBy,
UpdatedBy = data.UpdatedBy,
UserCreate = user_create.FullName,
UserUpdated = user_update.FullName
}).AsEnumerable().ToList();
}
catch (Exception ex)
{
throw;
}
return(lst_Category);
}
public int Get_Total_Money_Day()
{
_db = new QLKHEntities();
int tong_tien = 0;
List <customer> lst_customer = new List <customer>();
try
{
lst_customer = (from data in _db.customers
where data.Status == true
select data).ToList();
foreach (var item in lst_customer)
{
if (DAL_QLCustomer.Get_After_Date(item.id) > 0)
{
tong_tien += Convert.ToInt32(DAL_LichSuTraTien.Get_Tien_Lai(item.id));
}
}
}
catch (Exception ex)
{
throw ex;
}
return(tong_tien);
}
public int Get_After_Money(int IdCustomer) //lấy số nợ còn phải trả
{
_db = new QLKHEntities();
int tien_goc = (from data in _db.customers
where data.Status == true && data.id == IdCustomer
select data.Money).FirstOrDefault();
var lich_su_tra_tien_goc = (from data in _db.historyPaids
where data.CustomerId == IdCustomer && data.TypePaid == "0"
select data).ToList();
var tien_goc_da_tra = lich_su_tra_tien_goc.Select(m => m.Money).Sum();
return(tien_goc - tien_goc_da_tra);
}
public DateTime Get_Next_Date(int IdCustomer) //lấy ngày nộp lãi tiếp theo
{
_db = new QLKHEntities();
customer Customer = new customer();
Customer = (from data in _db.customers
where data.id == IdCustomer && data.Status == true
select data).FirstOrDefault();
int countDay = (DateTime.Now - Customer.CreatedAt).Days + 1;
int countPaid = Convert.ToInt32(Math.Ceiling((double)(countDay * 1.0 / Customer.cycle)));
int sumDay = countPaid * Customer.cycle - countDay;
return(DateTime.Now.AddDays(sumDay));
}
public user Check_Login(string username, string password)
{
QLKHEntities _DB = new QLKHEntities();
user user = new user();
try
{
user = (from data in _DB.users
where data.UserName == username && data.PassWord == password && data.Status == true
select data).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
return(user);
}
public double Get_Total_Money()
{
_db = new QLKHEntities();
double tong_tien = 0;
List <customer> lst_customer = new List <customer>();
try
{
lst_customer = (from data in _db.customers
select data).ToList();
tong_tien = lst_customer.Select(m => m.Money).Sum();
}
catch (Exception ex)
{
throw ex;
}
return(tong_tien);
}
public int Get_After_Date(int IdCustomer) //lấy số ngày chậm lãi
{
_db = new QLKHEntities();
customer Customer = new customer();
Customer = (from data in _db.customers
where data.id == IdCustomer && data.Status == true
select data).FirstOrDefault();
historyPaid Last_Paid = new historyPaid();
Last_Paid = (from data in _db.historyPaids
where data.CustomerId == IdCustomer && data.TypePaid == "1"
orderby data.PaidDate descending
select data).FirstOrDefault();
if (Last_Paid != null)
{
return((DateTime.Now - Last_Paid.PaidDate).Days + 1 > Customer.cycle ? (DateTime.Now - Last_Paid.PaidDate).Days + 1 - Customer.cycle : 0);
}
return((DateTime.Now - Customer.CreatedAt).Days + 1 > Customer.cycle ? (DateTime.Now - Customer.CreatedAt).Days + 1 - Customer.cycle : 0);
}
public int Get_Total_Customer()
{
_db = new QLKHEntities();
int count_customer = 0;
List <customer> lst_customer = new List <customer>();
try
{
lst_customer = (from data in _db.customers
where data.Status == true
select data).ToList();
count_customer = lst_customer.Count;
}
catch (Exception ex)
{
throw ex;
}
return(count_customer);
}
public double Get_Total_Money_Paid(string typePaid)
{
_db = new QLKHEntities();
double tong_tien_da_tra = 0;
List <historyPaid> lst_paid = new List <historyPaid>();
try
{
lst_paid = (from data in _db.historyPaids
where data.TypePaid == typePaid
select data).ToList();
tong_tien_da_tra = lst_paid.Select(m => m.Money).Sum();
}
catch (Exception ex)
{
throw ex;
}
return(tong_tien_da_tra);
}
public double Get_Money_Thu_Day()
{
_db = new QLKHEntities();
int tong_tien = 0;
List <historyPaid> lst_paid = new List <historyPaid>();
try
{
lst_paid = (from data in _db.historyPaids
where data.TypePaid == "1"
select data).ToList();
lst_paid = lst_paid.Where(x => x.PaidDate.Date.ToString() == DateTime.Now.Date.ToString()).ToList();
tong_tien = lst_paid.Select(m => m.Money).Sum();
}
catch (Exception ex)
{
throw ex;
}
return(tong_tien);
}
public double Get_Tien_Lai(int IdCustomer)
{
_db = new QLKHEntities();
historyPaid history = new historyPaid();
customer customer = new customer();
try
{
history = (from data in _db.historyPaids // lấy bản ghi trả nợ gần nhất
join data_customer in _db.customers
on data.CustomerId equals data_customer.id
where data.CustomerId == IdCustomer && data.TypePaid == "1" && data_customer.Status == true
orderby data.PaidDate descending
select data).FirstOrDefault();
customer = (from data in _db.customers
where data.id == IdCustomer
select data).FirstOrDefault();
DateTime start_date = customer.CreatedAt;
if (history != null)
{
start_date = history.PaidDate;
}
List <historyInterestRate> lst_lai_suat = new List <historyInterestRate>();
lst_lai_suat = (from data in _db.historyInterestRates
orderby data.StartDate ascending
select data).ToList();
double tien_lai = 0;
if (history == null || history.PaidDate.Date.ToString() != DateTime.Now.Date.ToString())
{
int so_tien_vay = customer.Money;
int demngay = (Convert.ToDateTime(DateTime.Now) - Convert.ToDateTime(start_date)).Days + 1;
for (int i = 0; i < demngay; i++)
{
DateTime date_start = start_date.AddDays(i);//ngày để tính số tiền gốc đang nợ
double tien_goc = get_tien_goc_by_day(date_start, IdCustomer);
if (date_start >= lst_lai_suat[lst_lai_suat.Count - 1].StartDate)
{
tien_lai += tien_goc * lst_lai_suat[lst_lai_suat.Count - 1].Percents * 0.01;
}
else
{
for (int j = 0; j < (lst_lai_suat.Count - 1); j++)
{
if (date_start >= lst_lai_suat[j].StartDate && date_start < lst_lai_suat[j + 1].StartDate)
{
tien_lai += tien_goc * lst_lai_suat[j].Percents * 0.01;
}
}
}
}
}
// Util.Show_Message_Notification(Message.msg_notification, dateRange.ToString());
return(tien_lai);
}
catch (Exception ex)
{
throw ex;
}
}