public static List <UserAccountModel> GetAll(out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var query = from u in db.UserAccount
select new UserAccountModel
{
ID = u.ID,
Department = u.Department,
CreatedBy = u.CreatedBy,
CreatedDate = u.CreatedDate,
Email = u.Email,
Firstname = u.FirstName,
LastName = u.LastName,
MiddleInitial = u.MiddleInitial,
Status = u.Status,
Type = u.Type,
Username = u.Username,
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void ChangePassword(ChangePassModel _user, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var user = db.UserAccount.FirstOrDefault(r => r.ID == UniversalService.CurrentUser.ID);
if (user != null)
{
if (user.Password == _user.CurrentPass)
{
user.Password = _user.NewPass;
db.Entry(user).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
else
{
message = "Invalid Password";
}
}
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static List <ApprovedReportModel> GetAllMotoReport(DateTime start, DateTime end, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var qDB = new QuickipediaEntities();
var moto = db.v_MotoRequest.Where(r => r.ApprovedDate >= start && r.ApprovedDate <= end).ToList();
var motoClientCode = moto.Select(r => r.ClientCode);
var quicki = qDB.ClientProfile.Where(r => motoClientCode.Contains(r.ClientCode)).ToList();
var join = from a in moto
join c in quicki on a.ClientCode equals c.ClientCode
join u in db.UserAccount on a.RequestedBy equals u.ID
join usr in db.UserAccount on a.ApprovedBy equals usr.ID into qUsr
from ap in qUsr.DefaultIfEmpty()
orderby a.Date ascending
select new ApprovedReportModel
{
ID = a.ID,
Date = a.Date,
FirstName = a.FirstName,
LastName = a.LastName,
ClientCode = a.ClientCode,
Company = a.Company,
PaxName = a.PaxName,
RecordLocator = a.RecordLocator,
Currency = a.Currency,
Amount = a.Amount,
Others = a.Others,
BCDFee = a.BCDFee,
AdminFee = a.AdminFee,
Total = a.Total,
OptionTime = a.OptionTime,
ApprovalCode = a.ApprovalCode,
Remarks = a.Remarks,
ApprovedDate = a.ApprovedDate,
ApprovedBy = ap.ID,
ShowApprovedBy = ap.FirstName + " " + ap.LastName,
ClientName = c.ClientName,
LogID = a.LogID
};
return(join.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <MotoRequestModel> GetDuplicate(MotoRequestModel _model, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var qDB = new QuickipediaEntities();
var moto = db.MotoRequest.Where(r => r.RecordLocator.ToLower() == _model.RecordLocator.ToLower() &&
r.PaxName.ToLower() == _model.PaxName.ToLower()).ToList();
var motoClientCode = moto.Select(r => r.ClientCode);
var quicki = qDB.ClientProfile.Where(r => motoClientCode.Contains(r.ClientCode)).ToList();
var join = from m in moto
join c in quicki on m.ClientCode equals c.ClientCode
join u in db.UserAccount on m.RequestedBy equals u.ID
orderby m.Date ascending
select new MotoRequestModel
{
ClientCode = m.ClientCode,
ClientName = c.ClientName,
AdminFee = m.AdminFee,
Amount = m.Amount,
ApprovalCode = m.ApprovalCode,
BCDFee = m.BCDFee,
Company = m.Company,
Currency = m.Currency,
Date = m.Date,
ID = m.ID,
Invoice = m.Invoice,
OptionTime = m.OptionTime,
Others = m.Others,
PaxName = m.PaxName.ToUpper(),
RecordLocator = m.RecordLocator,
Remarks = m.Remarks,
Status = m.Status,
Total = m.Total,
RequestedBy = m.RequestedBy,
ShowRequestedBy = u.FirstName + " " + u.LastName,
DeclinedReason = m.DeclinedReason,
ApprovedDate = m.ApprovedDate,
LogID = m.LogID
};
return(join.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static UserAccountModel ValidateLogin(string _username, string _password, out string message)
{
message = "";
try
{
using (var db = new eCardEntities())
{
var user = db.UserAccount.FirstOrDefault(r => r.Username.ToLower() == _username.ToLower() &&
r.Password == _password);
if (user != null)
{
if (user.Status == "N")
{
message = "User is deactivated";
}
else
{
UserAccountModel userModel = new UserAccountModel
{
ID = user.ID,
Username = user.Username,
Firstname = user.FirstName,
MiddleInitial = user.MiddleInitial,
LastName = user.LastName,
Department = user.Department,
Type = user.Type,
Status = user.Type,
Email = user.Email
};
return(userModel);
}
}
else
{
message = "Invalid username or password";
}
return(null);
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}//Validate Login
public static string GetRequestor(Guid?_ID, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var user = db.UserAccount.FirstOrDefault(r => r.ID == _ID);
return(user.FirstName + " " + user.LastName);
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
Panel1.Visible = btnGo.Visible = false;
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Reports/ApprovedMoto.rdlc");
eCardEntities entities = new eCardEntities();
DateTime endDate = EndDate.SelectedDate.AddHours(24);
ReportDataSource dataSource = new ReportDataSource("DataSet1", (from moto in entities.vw_Approved
where moto.Date >= StartDate.SelectedDate &&
moto.Date <= endDate
select moto));
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(dataSource);
}
protected void Button1_Click(object sender, EventArgs e)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Reports/AllMotoRequest.rdlc");
eCardEntities entities = new eCardEntities();
DateTime endDate = Calendar2.SelectedDate.AddHours(24);
ReportDataSource dataSource = new ReportDataSource("DataSet1", (from customer in entities.v_MotoRequest where customer.Date >= Calendar1.SelectedDate &&
customer.Date <= endDate select customer));
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(dataSource);
Panel1.Visible = Button1.Visible = false;
Button2.Visible = true;
}
private static EmailModel GetEmail()
{
try
{
using (var db = new eCardEntities())
{
var query = from e in db.EmailSender
select new EmailModel
{
Email = e.Email,
Password = e.Password
};
return(query.FirstOrDefault());
}
}
catch
{
return(null);
}
}
public static void SaveMotoRequest(MotoRequestModel _request, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
if (_request.ID == Guid.Empty) //NEW
{
MotoRequest newMoto = new MotoRequest
{
ID = Guid.NewGuid(),
Date = DateTime.Now,
RequestedBy = UniversalService.CurrentUser.ID,
ClientCode = _request.ClientCode,
Company = _request.Company,
PaxName = _request.PaxName.ToUpper(),
RecordLocator = _request.RecordLocator.ToUpper(),
Currency = _request.Currency,
Amount = _request.Amount,
Others = _request.Others,
BCDFee = _request.BCDFee,
AdminFee = _request.AdminFee,
Total = _request.Amount + _request.BCDFee + _request.Others + _request.AdminFee,
OptionTime = _request.OptionTime,
ApprovalCode = _request.ApprovalCode,
Remarks = _request.Remarks,
Invoice = _request.Invoice,
Status = _request.Status,
DeclinedReason = _request.DeclinedReason,
ApprovedBy = Guid.Empty
};
db.Entry(newMoto).State = EntityState.Added;
db.SaveChanges();
}
else
{
var moto = db.MotoRequest.FirstOrDefault(r => r.ID == _request.ID);
if (moto != null)
{
moto.ClientCode = _request.ClientCode;
moto.Company = _request.Company;
moto.PaxName = _request.PaxName.ToUpper();
moto.RecordLocator = _request.RecordLocator.ToUpper();
moto.Currency = _request.Currency;
moto.AdminFee = _request.AdminFee;
moto.Total = _request.Amount + _request.Others + _request.BCDFee + _request.AdminFee;
moto.OptionTime = _request.OptionTime;
moto.ApprovalCode = _request.ApprovalCode;
moto.Remarks = _request.Remarks;
moto.Invoice = _request.Invoice;
moto.Status = _request.Status;
moto.DeclinedReason = _request.DeclinedReason;
moto.ApprovedDate = DateTime.Now;
moto.ApprovedBy = UniversalService.CurrentUser.ID;
db.Entry(moto).State = EntityState.Modified;
db.SaveChanges();
}
}
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static List <MotoRequestModel> Search(string search, out string message)
{
try
{
message = "";
var lowerSearch = search.ToLower();
using (var db = new eCardEntities())
{
var qDB = new QuickipediaEntities();
var moto = db.v_MotoRequest.Where(r => r.RecordLocator.ToLower().Contains(lowerSearch) ||
r.Remarks.ToLower().Contains(lowerSearch) || r.PaxName.ToLower().Contains(lowerSearch) ||
r.LogID.ToString().ToLower().Contains(lowerSearch)
).ToList();
var motoClientCode = moto.Select(r => r.ClientCode);
var quicki = qDB.ClientProfile.Where(r => motoClientCode.Contains(r.ClientCode)).ToList();
var join = from a in moto
join c in quicki on a.ClientCode equals c.ClientCode
join u in db.UserAccount on a.RequestedBy equals u.ID
join usr in db.UserAccount on a.ApprovedBy equals usr.ID into qUsr
from ap in qUsr.DefaultIfEmpty()
orderby a.Date ascending
select new MotoRequestModel
{
ID = a.ID,
Date = a.Date,
ClientCode = a.ClientCode,
Company = a.Company,
PaxName = a.PaxName,
RecordLocator = a.RecordLocator,
Currency = a.Currency,
Amount = a.Amount,
Others = a.Others,
BCDFee = a.BCDFee,
AdminFee = a.AdminFee,
Total = a.Total,
OptionTime = a.OptionTime,
ApprovalCode = a.ApprovalCode,
Remarks = a.Remarks,
ApprovedDate = a.ApprovedDate,
ApprovedBy = ap.ID,
ShowApprovedBy = ap.FirstName + " " + ap.LastName,
Status = a.Status,
ClientName = c.ClientName,
RequestedBy = a.RequestedBy,
ShowRequestedBy = u.FirstName + " " + u.LastName,
DeclinedReason = a.DeclinedReason,
LogID = a.LogID
};
var val = join.ToList();
if (val.Count() == 0)
{
message = "No record found . . .";
return(null);
}
else
{
return(val);
}
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void Save(UserAccountModel _user, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
if (_user.ID == Guid.Empty || _user.ID == null) // NEW
{
UserAccount newAccount = new UserAccount
{
ID = Guid.NewGuid(),
Username = _user.Username,
Password = _user.Password,
Department = _user.Department,
Email = _user.Email,
FirstName = _user.Firstname,
LastName = _user.LastName,
MiddleInitial = _user.MiddleInitial,
Status = "Y",
Type = _user.Type,
CreatedBy = UniversalService.CurrentUser.ID,
CreatedDate = DateTime.Now
};
db.Entry(newAccount).State = System.Data.Entity.EntityState.Added;
}
else
{
var user = db.UserAccount.FirstOrDefault(r => r.ID == _user.ID);
if (user != null)
{
user.Username = _user.Username;
if (_user.Password != "" && _user.Password != null)
{
user.Password = _user.Password;
}
user.Department = _user.Department;
user.Email = _user.Email;
user.FirstName = _user.Firstname;
user.LastName = _user.LastName;
user.MiddleInitial = _user.MiddleInitial;
user.Status = _user.Status;
user.Type = _user.Type;
user.CreatedBy = UniversalService.CurrentUser.ID;
user.CreatedDate = DateTime.Now;
db.Entry(user).State = System.Data.Entity.EntityState.Modified;
}
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
