public static void AddCreditPerEmployee(Guid _userID, int VL, int SL, int soloLeave, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var user = db.UserAccount.FirstOrDefault(r => r.ID == _userID);
if (user != null)
{
user.VacationLeavCount += VL;
user.SickLeaveCount += SL;
user.SoloParentLeaveCount += soloLeave;
db.Entry(user).State = EntityState.Modified;
db.SaveChanges();
}
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static List <LeaveTypeDropdownModel> GetLeaveType(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from l in db.LeaveType
orderby l.Description ascending
select new LeaveTypeDropdownModel
{
Label = l.Description,
Value = l.ID
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static UserModel ValidateLogin(string username, string password, out string message)
{
message = "";
try
{
using (var db = new SLVLOnlineEntities())
{
var user = db.UserAccount.FirstOrDefault(r => r.Username.ToLower() == username.ToLower());
if (user != null)
{
if (user.Password.ToLower() == password.ToLower())
{
if (user.Status == "X")
{
message = "User is deactivated";
}
else
{
UserModel userModel = new UserModel
{
Username = user.Username,
Status = user.Status,
FirstName = user.FirstName,
MiddleInitial = user.MiddleInitial,
LastName = user.LastName,
BirthDate = user.Birthdate,
Gender = user.Gender,
CivilStatus = user.CivilStatus,
BirthPlace = user.BirthPlace,
Position = user.Position,
DateHired = user.DateHired,
ContactNo = user.ContactNo,
DeptID = user.DeptID
};
return(userModel);
}
}
else
{
message = "Invalid Password";
}
}
else
{
message = "Invalid Username";
}
return(null);
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void AddCreditPerGroup(Guid _groupID, int VL, int SL, int soloLeave, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var users = db.UserAccount.Where(r => r.DeptID == _groupID).ToList();
if (users != null)
{
users.ToList().ForEach(user =>
{
user.VacationLeavCount += VL;
user.SickLeaveCount += SL;
user.SoloParentLeaveCount += soloLeave;
db.Entry(user).State = EntityState.Modified;
});
db.SaveChanges();
}
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static List <OvertimeFormModel> GetForApproval(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var ot = from o in db.OvertimeForm
join u in db.UserAccount
on o.CreatedBy equals u.ID
orderby o.EffectiveDate descending
select new OvertimeFormModel
{
ID = o.ID,
EffectiveDate = o.EffectiveDate,
Status = o.Status,
StartTime = o.StartTime,
EndTime = o.EndTime,
CreatedBy = o.CreatedBy,
CreatedDate = o.CreatedDate,
Reason = o.Reason,
ShowCreatedBy = u.FirstName + " " + u.LastName
};
return(ot.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void ChangePassword(string _oldPassword, string _newPassword, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var user = db.UserAccount.FirstOrDefault(r => r.ID == UniversalHelpers.CurrentUser.ID && r.Password == _oldPassword);
if (user != null)
{
user.Password = _newPassword;
db.Entry(user).State = EntityState.Modified;
db.SaveChanges();
}
else
{
message = "Invalid Password. . .";
}
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static EmailAccountModel GetEmail(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var email = from e in db.EmailCredential
where e.ID != null && e.ID != Guid.Empty
select new EmailAccountModel
{
ID = e.ID,
Email = e.Email,
};
return(email.FirstOrDefault());
}
}
catch (Exception error)
{
message = error.Message;
return(new EmailAccountModel());
}
}
public static List <UserDropDownModel> GetUserDropDown(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from u in db.UserAccount
where u.Status == "Y"
select new UserDropDownModel
{
ID = u.ID,
Name = u.FirstName + " " + u.LastName
};
return(query.OrderBy(r => r.Name).ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static int GetMinDateForLeave(Guid _leaveID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var leave = db.LeaveType.FirstOrDefault(r => r.ID == _leaveID);
if (leave != null)
{
return(leave.DaysBeforeFilling);
}
else
{
return(0);
}
}
}
catch (Exception error)
{
message = error.Message;
return(0);
}
}
public static List <CalendarModel> GetHoliday(out string message, DateTime?start = null, DateTime?end = null)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var holiday = from h in db.Holiday
select new CalendarModel
{
ID = h.ID,
CreatedBy = h.CreatedBy,
CreatedDate = h.CreatedDate,
start = h.StartDate,
end = h.EndDate,
title = h.Description,
Status = "Y"
};
return(holiday.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <AdvisoryModel> GetAdvisory(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var advisory = from a in db.Advisory
join u in db.UserAccount on a.ModifiedBy equals u.ID
orderby a.ModifiedDate descending
select new AdvisoryModel
{
ID = a.ID,
Description = a.Description,
ModifiedDate = a.ModifiedDate,
ModifiedBy = a.ModifiedBy,
ShowModifiedBy = u.FirstName + " " + u.LastName,
Status = 1
};
return(advisory.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <LeaveTypeModel> GetAll(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from l in db.LeaveType
select new LeaveTypeModel
{
ID = l.ID,
CreateDate = l.CreatedDate,
CreatedBy = l.CreatedBy,
DaysBeforeFilling = l.DaysBeforeFilling,
Description = l.Description,
Type = l.Type,
Status = "Y",
LeaveDeduction = l.LeaveDeduction
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <GroupApproverMemberModel> GetMembers(Guid _groupID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from m in db.ApproverGroupMember
join g in db.ApproverGroup on m.GroupID equals g.ID
join u in db.UserAccount on m.UserID equals u.ID
orderby u.FirstName ascending
where m.GroupID == _groupID
select new GroupApproverMemberModel
{
ID = m.ID,
GroupID = m.GroupID,
GroupName = g.Name,
Status = "Y",
UserID = m.UserID,
UserName = u.FirstName + " " + u.LastName
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void Save(LeaveTypeModel _leaveType, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
if (_leaveType.ID == null || _leaveType.ID == Guid.Empty)
{
LeaveType newLeaveType = new LeaveType
{
ID = Guid.NewGuid(),
Description = _leaveType.Description,
CreatedBy = UniversalHelpers.CurrentUser.ID,
CreatedDate = DateTime.Now,
DaysBeforeFilling = _leaveType.DaysBeforeFilling,
Type = _leaveType.Type,
LeaveDeduction = _leaveType.LeaveDeduction
};
db.Entry(newLeaveType).State = EntityState.Added;
}
else //Update
{
var leave = db.LeaveType.FirstOrDefault(r => r.ID == _leaveType.ID);
if (leave != null)
{
if (_leaveType.Status == "X")
{
db.Entry(leave).State = EntityState.Deleted;
}
else
{
leave.Description = _leaveType.Description;
leave.CreatedBy = UniversalHelpers.CurrentUser.ID;
leave.CreatedDate = DateTime.Now;
leave.DaysBeforeFilling = _leaveType.DaysBeforeFilling;
leave.Type = _leaveType.Type;
leave.LeaveDeduction = _leaveType.LeaveDeduction;
db.Entry(leave).State = EntityState.Modified;
}
}
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static GroupApproverModel GetGroup(Guid _groupID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from g in db.ApproverGroup
where g.ID == _groupID
select new GroupApproverModel
{
ID = g.ID,
FirstApprover = g.FirstApprover,
SecondApprover = g.SecondApprover,
Name = g.Name,
Status = "Y"
};
return(query.FirstOrDefault());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <GroupApproverDropDownModel> GetGroupDropDown(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from g in db.ApproverGroup
orderby g.Name ascending
select new GroupApproverDropDownModel
{
ID = g.ID,
Name = g.Name
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static GroupApproverModel GetSingleGroup(Guid _ID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from g in db.ApproverGroup
join u in db.UserAccount on g.FirstApprover equals u.ID into qU
from user in qU.DefaultIfEmpty()
join u2 in db.UserAccount on g.SecondApprover equals u2.ID into qU2
from user2 in qU2.DefaultIfEmpty()
where g.ID == _ID
select new GroupApproverModel
{
ID = g.ID,
Name = g.Name,
FirstApprover = g.FirstApprover,
FirstApproverName = user.FirstName + " " + user.LastName,
SecondApprover = g.SecondApprover,
SecondApproverName = user2.FirstName + " " + user2.LastName,
Status = "Y"
};
return(query.FirstOrDefault());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <GroupApproverModel> GetAllGroup(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from g in db.ApproverGroup
join first in db.UserAccount on g.FirstApprover equals first.ID into qF
from f in qF.DefaultIfEmpty()
join second in db.UserAccount on g.SecondApprover equals second.ID into qS
from s in qS.DefaultIfEmpty()
orderby g.Name ascending
select new GroupApproverModel
{
ID = g.ID,
FirstApprover = g.FirstApprover,
FirstApproverName = f.FirstName + " " + f.LastName,
SecondApprover = g.SecondApprover,
SecondApproverName = s.FirstName + " " + s.LastName,
Name = g.Name,
Status = "Y"
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void Save(CalendarModel model, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
if (model.ID == null || model.ID == Guid.Empty)
{
Holiday newHoliday = new Holiday
{
ID = Guid.NewGuid(),
Description = model.title,
StartDate = model.end,
EndDate = model.start,
CreatedBy = UniversalHelpers.CurrentUser.ID,
CreatedDate = DateTime.Now
};
db.Entry(newHoliday).State = EntityState.Added;
}
else
{
var holiday = db.Holiday.FirstOrDefault(r => r.ID == model.ID);
if (holiday != null)
{
if (model.Status == "X")
{
db.Entry(holiday).State = EntityState.Deleted;
}
else
{
holiday.Description = model.title;
holiday.EndDate = model.end;
holiday.StartDate = model.start;
holiday.CreatedBy = UniversalHelpers.CurrentUser.ID;
holiday.CreatedDate = DateTime.Now;
db.Entry(holiday).State = EntityState.Modified;
}
}
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static void SendEmail(string _subject, string _content, string _toMail)
{
try
{
_subject = "SLVL Online - " + _subject;
_content += "\n\n\n Please do not reply on this email . . . \n";
using (var db = new SLVLOnlineEntities())
{
var email = db.EmailCredential.FirstOrDefault(r => r.ID != null);
if (email != null)
{
using (MailMessage mm = new MailMessage(email.Email, _toMail))
{
mm.Subject = _subject;
mm.Body = _content;
mm.IsBodyHtml = false;
SmtpClient smtp = new SmtpClient();
smtp.Host = "mail.casto.com.ph";
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.UseDefaultCredentials = false;
smtp.EnableSsl = true;
NetworkCredential networkCredential = new NetworkCredential(email.Email, email.Password);
smtp.UseDefaultCredentials = true;
smtp.Credentials = networkCredential;
smtp.Port = 587;
smtp.Send(mm);
}
}
}
}
catch (Exception error)
{
}
}
public static void SaveMember(GroupApproverMemberModel _member, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
if (_member.ID == Guid.Empty || _member.ID == null)//NEW
{
var ifExist = db.ApproverGroupMember.FirstOrDefault(r => r.UserID == _member.UserID);
if (ifExist == null)
{
ApproverGroupMember newMember = new ApproverGroupMember
{
ID = Guid.NewGuid(),
GroupID = _member.GroupID,
UserID = _member.UserID
};
db.Entry(newMember).State = EntityState.Added;
}
else
{
message = "Employee is already a member";
}
}
else //UPDATE
{
var member = db.ApproverGroupMember.FirstOrDefault(r => r.ID == _member.ID);
if (member != null)
{
if (_member.Status == "X")
{
db.Entry(member).State = EntityState.Deleted;
}
}
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static void Save(AdvisoryModel _advisory, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
if (_advisory.ID == Guid.Empty)
{
Advisory newAdvisory = new Advisory
{
ID = Guid.NewGuid(),
Description = _advisory.Description,
ModifiedBy = UniversalHelpers.CurrentUser.ID,
ModifiedDate = DateTime.Now
};
db.Entry(newAdvisory).State = System.Data.Entity.EntityState.Added;
}
else
{
var adv = db.Advisory.FirstOrDefault(r => r.ID == _advisory.ID);
if (adv != null && _advisory.Status == 1)
{
adv.Description = _advisory.Description;
adv.ModifiedBy = UniversalHelpers.CurrentUser.ID;
adv.ModifiedDate = DateTime.Now;
db.Entry(adv).State = System.Data.Entity.EntityState.Modified;
}
else if (_advisory.Status == 0)
{
db.Entry(adv).State = System.Data.Entity.EntityState.Deleted;
}
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static List <UserModel> GetAll(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from u in db.UserAccount
join grp in db.ApproverGroup on u.DeptID equals grp.ID into qG
from g in qG.DefaultIfEmpty()
orderby u.FirstName
select new UserModel
{
ID = u.ID,
Username = u.Username,
FirstName = u.FirstName,
MiddleInitial = u.MiddleInitial,
LastName = u.LastName,
Status = u.Status,
BirthDate = u.Birthdate,
Position = u.Position,
DateHired = u.DateHired,
ContactNo = u.ContactNo,
Email = u.Email,
Type = u.Type,
DeptID = u.DeptID,
Department = g.Name,
SickLeaveCount = u.SickLeaveCount,
VacationLeaveCount = u.VacationLeavCount,
SoloParentLeaveCount = u.SoloParentLeaveCount
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <UserModel> GetSelectedGroup(Guid GroupID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from g in db.ApproverGroup
join m in db.ApproverGroupMember on g.ID equals m.GroupID
join u in db.UserAccount on m.UserID equals u.ID
where g.ID == GroupID
select new UserModel
{
ID = u.ID,
BirthDate = u.Birthdate,
CivilStatus = u.CivilStatus,
BirthPlace = u.BirthPlace,
ContactNo = u.ContactNo,
DateHired = u.DateHired,
Email = u.Email,
FirstName = u.FirstName,
Gender = u.Gender,
LastName = u.LastName,
MiddleInitial = u.MiddleInitial,
Position = u.Position,
SickLeaveCount = u.SickLeaveCount,
Status = u.Status,
Username = u.Username,
VacationLeaveCount = u.VacationLeavCount,
SoloParentLeaveCount = u.SoloParentLeaveCount
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static string GetGroupName(Guid _groudID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = db.ApproverGroup.FirstOrDefault(r => r.ID == _groudID);
return(query.Name);
}
}
catch (Exception error)
{
message = error.Message;
return("");
}
}
public static UserModel GetSelectedUser(Guid _ID, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var query = from u in db.UserAccount
where u.ID == _ID
select new UserModel
{
ID = u.ID,
BirthDate = u.Birthdate,
CivilStatus = u.CivilStatus,
BirthPlace = u.BirthPlace,
ContactNo = u.ContactNo,
DateHired = u.DateHired,
Email = u.Email,
FirstName = u.FirstName,
Gender = u.Gender,
LastName = u.LastName,
MiddleInitial = u.MiddleInitial,
Position = u.Position,
SickLeaveCount = u.SickLeaveCount,
Status = u.Status,
Username = u.Username,
VacationLeaveCount = u.VacationLeavCount,
SoloParentLeaveCount = u.SoloParentLeaveCount
};
return(query.FirstOrDefault());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <LeaveFormModel> GetForApproval(out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var leave = from l in db.LeaveForm
join u in db.UserAccount on l.CreatedBy equals u.ID
join g in db.ApproverGroup on u.DeptID equals g.ID
join t in db.LeaveType on l.Type equals t.ID
where g.FirstApprover == UniversalHelpers.CurrentUser.ID ||
g.SecondApprover == UniversalHelpers.CurrentUser.ID
orderby l.StartDate descending
select new LeaveFormModel
{
ID = l.ID,
StartDate = l.StartDate,
EndDate = l.EndDate,
CreatedBy = l.CreatedBy,
CreatedDate = l.CreatedDate,
Reason = l.Reason,
Status = l.Status,
Type = l.Type,
ShowCreatedBy = u.FirstName + " " + u.LastName,
DeclineReason = l.DeclineReason,
ShowType = t.Description
};
return(leave.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void SaveEmail(EmailAccountModel _email, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
var email = db.EmailCredential.FirstOrDefault(r => r.ID != null);
if (email == null)
{
EmailCredential newEmail = new EmailCredential
{
ID = Guid.NewGuid(),
Email = _email.Email,
Password = _email.Password
};
db.Entry(newEmail).State = EntityState.Added;
}
else
{
_email.Password = _email.Password;
_email.Email = _email.Email;
db.Entry(_email).State = EntityState.Modified;
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
public static List <LeaveFormModel> GetAllLeave(out string message)
{
message = "";
try
{
using (var db = new SLVLOnlineEntities())
{
var leave = from l in db.LeaveForm
join u in db.UserAccount on l.CreatedBy equals u.ID
join t in db.LeaveType on l.Type equals t.ID
orderby l.StartDate descending
select new LeaveFormModel
{
ID = l.ID,
StartDate = l.StartDate,
EndDate = l.EndDate,
CreatedBy = l.CreatedBy,
CreatedDate = l.CreatedDate,
Reason = l.Reason,
Status = l.Status,
Type = l.Type,
ShowCreatedBy = u.FirstName + " " + u.LastName,
DeclineReason = l.DeclineReason,
ShowType = t.Description
};
return(leave.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static void SaveUpdate(OvertimeFormModel _overtime, out string message)
{
try
{
message = "";
using (var db = new SLVLOnlineEntities())
{
if (_overtime.ID == Guid.Empty || _overtime.ID == null)
{
OvertimeForm newOvertime = new OvertimeForm
{
ID = Guid.NewGuid(),
EffectiveDate = _overtime.EffectiveDate,
StartTime = _overtime.StartTime,
EndTime = _overtime.EndTime,
CreatedBy = UniversalHelpers.CurrentUser.ID,
CreatedDate = DateTime.Now,
Reason = _overtime.Reason,
Status = "P"
};
db.Entry(newOvertime).State = EntityState.Added;
//========FIRST APPROVER EMAIL==============
EmailService.SendEmail("Overtime For Approval", UniversalHelpers.CurrentUser.FirstName + " " + UniversalHelpers.CurrentUser.LastName +
" filed overtime and waiting for your approval", UniversalHelpers.CurrentUser.FirstApproverEmail);
//========SECOND APPROVER EMAIL=============
EmailService.SendEmail("Overtime For Approval", UniversalHelpers.CurrentUser.FirstName + " " + UniversalHelpers.CurrentUser.LastName +
" filed overtime and waiting for your approval", UniversalHelpers.CurrentUser.SecondApproverEmail);
}
else
{
var ot = db.OvertimeForm.FirstOrDefault(r => r.ID == _overtime.ID);
if (ot != null)
{
var user = db.UserAccount.FirstOrDefault(r => r.ID == ot.CreatedBy);
ot.Status = _overtime.Status;
if (_overtime.Status == "D")
{
EmailService.SendEmail("Filed Overtime Declined", "Your filed overtime has been declined by " + UniversalHelpers.CurrentUser.FirstName + " "
+ UniversalHelpers.CurrentUser.LastName, user.Email);
}
else if (_overtime.Status == "A")
{
EmailService.SendEmail("Filed Overtime Approved", "Your filed overtime has been approved by " + UniversalHelpers.CurrentUser.FirstName + " "
+ UniversalHelpers.CurrentUser.LastName, user.Email);
}
else if (_overtime.Status == "X")
{
EmailService.SendEmail("Filed Overtime of " + UniversalHelpers.CurrentUser.FirstName + " has been canceled", "Filed overtime of " +
UniversalHelpers.CurrentUser.FirstName + " " + UniversalHelpers.CurrentUser.LastName + " has been canceled for some reasons", UniversalHelpers.CurrentUser.FirstApproverEmail);
EmailService.SendEmail("Filed Overtime of " + UniversalHelpers.CurrentUser.FirstName + " has been canceled", "Filed overtime of " +
UniversalHelpers.CurrentUser.FirstName + " " + UniversalHelpers.CurrentUser.LastName + " has been canceled for some reasons", UniversalHelpers.CurrentUser.SecondApproverEmail);
}
db.Entry(ot).State = EntityState.Modified;
}
}
db.SaveChanges();
}
}
catch (Exception error)
{
message = error.Message;
}
}
