public ActionResult SaveAgencyMail(List <M_Agency_Email> EmailList)
{
try
{
if (EmailList[0].AgencyCode != null)
{
db.M_AgencyReassign(EmailList[0].AgencyCode);
foreach (M_Agency_Email mail in EmailList)
{
mail.CreateID = user.UserName;
mail.CreateDate = DateTime.Now;
mail.UpdateID = user.UserName;
mail.UpdateDate = DateTime.Now;
db.M_Agency_Email.Add(mail);
db.SaveChanges();
}
return(Json(new { msg = "" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Cannot Save" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult SaveApprover(List <M_Section_Approver> Approver)
{
try
{
if (Approver[0].Section != null)
{
db.M_SectionApproverReassign(Approver[0].Section);
foreach (M_Section_Approver approver in Approver)
{
approver.CreateID = user.UserName;
approver.CreateDate = DateTime.Now;
approver.UpdateID = user.UserName;
approver.UpdateDate = DateTime.Now;
db.M_Section_Approver.Add(approver);
db.SaveChanges();
}
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Section";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
return(Json(new { msg = "" }, JsonRequestBehavior.AllowGet));
}
public ActionResult CreateSchedule(M_Schedule data)
{
try
{
string startTime = data.Timein.ToString();
string endTime = data.TimeOut.ToString();
DateTime start = DateTime.Parse(startTime);
DateTime end = DateTime.Parse(endTime);
TimeSpan duration = end.Subtract(start);
if (duration.TotalHours < 0)
{
end = end.AddDays(1);
duration = end.Subtract(start);
}
//if (duration.TotalHours != 0)
if (duration.TotalHours > 0) //change to military time
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
M_Schedule checker = (from c in db.M_Schedule
where c.Timein == data.Timein &&
c.TimeOut == data.TimeOut &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_Schedule.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(Json(new { msg = "zero" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Schedule";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult AddBreaks(M_ScheduleBreaks data)
{
try
{
string startTime = data.BreakOut.ToString();
string endTime = data.BreakIn.ToString();
DateTime start = DateTime.Parse(startTime);
DateTime end = DateTime.Parse(endTime);
if (start > end)
{
end = end.AddDays(1);
}
TimeSpan duration = end.Subtract(start);
if (duration.TotalMinutes > 0)
{
data.BreakTime = duration.TotalMinutes.ToString();
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
M_ScheduleBreaks checker = (from c in db.M_ScheduleBreaks
where c.BreakTime == data.BreakTime &&
c.BreakIn == data.BreakIn &&
c.BreakOut == data.BreakOut &&
c.ScheduleID == data.ScheduleID
select c).FirstOrDefault();
if (checker == null)
{
db.M_ScheduleBreaks.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success", ID = data.ScheduleID }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed", ID = data.ScheduleID }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(Json(new { msg = "zero", ID = data.ScheduleID }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Schedule";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult EditAgency(M_Agency data)
{
try
{
M_Agency agency = new M_Agency();
agency = (from u in db.M_Agency.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
agency.AgencyName = data.AgencyName;
agency.Address = data.Address;
agency.ISO_OT = data.ISO_OT;
agency.ISO_CS = data.ISO_CS;
agency.ISO_DTR = data.ISO_DTR;
agency.Status = data.Status;
agency.TelNo = data.TelNo;
//agency.EmailAddress = data.EmailAddress;
agency.UpdateID = user.UserName;
agency.UpdateDate = DateTime.Now;
M_Agency checker = (from c in db.M_Agency
where c.AgencyName == data.AgencyName &&
c.Address == data.Address &&
c.ISO_OT == data.ISO_OT &&
c.ISO_CS == data.ISO_CS &&
c.ISO_DTR == data.ISO_DTR &&
c.Status == data.Status &&
c.TelNo == data.TelNo &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(agency).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult CreateUsers(M_Users data)
{
try
{
data.Status = true;
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
data.Password = "******";
data.Password = EncodePasswordMd5(data.Password);
data.Section = "";
data.CostCode = "";
M_Users checker = (from c in db.M_Users
where c.UserName == data.UserName &&
c.FirstName == data.FirstName &&
c.LastName == data.LastName &&
c.Status == data.Status &&
c.Section == data.Section &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_Users.Add(data);
db.SaveChanges();
#region Give All Access
PageAccessGiver(data.UserName);
#endregion
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Users";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult EditUsers(M_Users data)
{
try
{
M_Users users = new M_Users();
users = (from u in db.M_Users.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
users.UserName = data.UserName;
users.FirstName = data.FirstName;
users.LastName = data.LastName;
users.Status = data.Status;
users.UpdateID = user.UserName;
users.UpdateDate = DateTime.Now;
M_Users checker = (from c in db.M_Users
where c.UserName == data.UserName &&
c.FirstName == data.FirstName &&
c.LastName == data.LastName &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(users).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Users";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult EditBreaks(M_ScheduleBreaks data)
{
try
{
M_ScheduleBreaks schedule = new M_ScheduleBreaks();
schedule = (from u in db.M_ScheduleBreaks.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
schedule.BreakIn = data.BreakIn;
schedule.BreakOut = data.BreakOut;
TimeSpan BreakIn = DateTime.Parse(data.BreakIn).TimeOfDay;
TimeSpan BreakOut = DateTime.Parse(data.BreakOut).TimeOfDay;
TimeSpan ts = BreakIn - BreakOut;
schedule.BreakTime = ts.TotalMinutes.ToString();
schedule.UpdateID = user.UserName;
schedule.UpdateDate = DateTime.Now;
M_ScheduleBreaks checker = (from c in db.M_ScheduleBreaks
where c.BreakIn == data.BreakIn &&
c.BreakOut == data.BreakOut &&
c.ScheduleID == data.ScheduleID
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(schedule).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Schedule";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult EditSchedule(M_Schedule data)
{
try
{
M_Schedule schedule = new M_Schedule();
schedule = (from u in db.M_Schedule.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
schedule.Type = data.Type;
schedule.Timein = data.Timein;
schedule.TimeOut = data.TimeOut;
schedule.Status = data.Status;
schedule.UpdateID = user.UserName;
schedule.UpdateDate = DateTime.Now;
M_Schedule checker = (from c in db.M_Schedule
where c.Timein == data.Timein &&
c.Type == data.Type &&
c.TimeOut == data.TimeOut &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(schedule).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Schedule";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult EditApprover(M_Approver data)
{
try
{
M_Approver approver = new M_Approver();
approver = (from u in db.M_Approver.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
approver.BIPH_Agency = data.BIPH_Agency;
approver.EmployeeNo = data.EmployeeNo;
approver.Section = data.Section;
approver.Status = data.Status;
approver.UpdateID = "AdminUp";
approver.UpdateDate = DateTime.Now;
M_Approver checker = (from c in db.M_Approver
where c.BIPH_Agency == data.BIPH_Agency &&
c.EmployeeNo == data.EmployeeNo &&
c.Section == data.Section &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(approver).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Approver";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult EditForecastPos(FC_EmployeeForecast data)
{
try
{
FC_EmployeeForecast agency = new FC_EmployeeForecast();
agency = (from u in db.FC_EmployeeForecast.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
agency.Position = data.Position;
agency.ClassE = data.ClassE;
agency.ClassJ = data.ClassJ;
agency.Unit = data.Unit;
agency.UpdateID = user.UserName;
agency.UpdateDate = DateTime.Now;;
FC_EmployeeForecast checker = (from c in db.FC_EmployeeForecast
where c.Position == data.Position &&
c.ClassJ == data.ClassJ &&
c.ClassE == data.ClassE &&
c.Unit == data.Unit &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(agency).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult UploadImagePackage(int AgencyID)
{
try
{
#region Save to Server
//bool isSuccess = false;
//string serverMessage = string.Empty;
//var fileOne = Request.Files[0] as HttpPostedFileBase;
// uploadPath = Server.MapPath(@"~/PictureResources/AgencyLogo/");
// newFileOne = Path.Combine(uploadPath, fileOne.FileName);
//fileOne.SaveAs(newFileOne);
#endregion
#region Save to Server
bool isSuccess = false;
string serverMessage = string.Empty;
var fileOne = Request.Files[0] as HttpPostedFileBase;
string uploadPath = Server.MapPath(@"~/PictureResources/AgencyLogo/");
string newFileOne = Path.Combine(uploadPath, fileOne.FileName);
//fileOne.SaveAs(newFileOne);
fileOne.SaveAs(HttpContext.Server.MapPath("~/PictureResources/AgencyLogo/") + Path.GetFileName(Regex.Replace(fileOne.FileName, @"\s+", "")));
#endregion
#region ImageSet
M_Agency pack = (from c in db.M_Agency where c.ID == AgencyID select c).FirstOrDefault();
//pack.Logo = fileOne.FileName;
string[] data = fileOne.FileName.Split('\\');
pack.Logo = Regex.Replace(data[data.Length - 1], @"\s+", "");//fileOne.FileName;
db.Entry(pack).State = EntityState.Modified;
db.SaveChanges();
#endregion
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { wew = "" }, JsonRequestBehavior.AllowGet));
}
public ActionResult EditSkills(M_Skills data)
{
try
{
M_Skills skill = new M_Skills();
skill = (from u in db.M_Skills.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
skill.Skill = data.Skill;
skill.Count = data.Count;
//skill.Status = data.Status;
skill.UpdateID = user.UserName;
skill.UpdateDate = DateTime.Now;
M_Skills checker = (from c in db.M_Skills
where c.Skill == data.Skill &&
c.Line == data.Line &&
c.Count == data.Count
//&& c.Status == data.Status
&& c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(skill).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Skills";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult SyncIT()
{
try
{
db.M_SP_ImportCostCodeFromITSystem();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Employee";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message, msgs = err.InnerException }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult EditComputer(M_ComputerRegistration data)
{
try
{
M_ComputerRegistration computer = new M_ComputerRegistration();
computer = (from u in db.M_ComputerRegistration.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
computer.ComputerName = data.ComputerName;
computer.ComputerIP = data.ComputerIP;
computer.Status = data.Status;
computer.UpdateID = user.UserName;
computer.UpdateDate = DateTime.Now;
M_ComputerRegistration checker = (from c in db.M_ComputerRegistration
where c.ComputerName == data.ComputerName &&
c.ComputerIP == data.ComputerIP &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(computer).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Computer Registration";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult CreateAgency(M_Agency data)
{
try
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
M_Agency checker = (from c in db.M_Agency
where c.AgencyName == data.AgencyName &&
c.Address == data.Address &&
c.ISO_OT == data.ISO_OT &&
c.ISO_CS == data.ISO_CS &&
c.ISO_DTR == data.ISO_DTR &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_Agency.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult CreateSkills(M_Skills data)
{
try
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
data.IsDeleted = false;
//data.Status = true;
M_Skills checker = (from c in db.M_Skills
where c.Skill == data.Skill &&
c.Line == data.Line
//&& c.Status == data.Status
&& c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_Skills.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Skills";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult CreateApprover(M_Approver data)
{
try
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
M_Approver checker = (from c in db.M_Approver
where c.BIPH_Agency == data.BIPH_Agency &&
c.EmployeeNo == data.EmployeeNo &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_Approver.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Masters - Approver";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult CreateComputer(M_ComputerRegistration data)
{
try
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
M_ComputerRegistration checker = (from c in db.M_ComputerRegistration
where c.ComputerName == data.ComputerName &&
c.ComputerIP == data.ComputerIP &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_ComputerRegistration.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - M_ComputerRegistration";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult CreateForecastPos(FC_EmployeeForecast data)
{
try
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;;
FC_EmployeeForecast checker = (from c in db.FC_EmployeeForecast
where c.Position == data.Position &&
c.ClassJ == data.ClassJ &&
c.ClassE == data.ClassE &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.FC_EmployeeForecast.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
//public ActionResult UpdateSectionAccess(List<PA_Section> PA_section)
//{
// foreach (PA_Section u in PA_section)
// {
// PA_Section section = new PA_Section();
// section = (from c in db.PA_Section where c.SectionID == u.SectionID && c.UserName == u.UserName select c).FirstOrDefault();
// if (section != null)
// {
// section.SectionAccess = u.SectionAccess;
// db.Entry(section).State = EntityState.Modified;
// db.SaveChanges();
// }
// else
// {
// section = new PA_Section();
// section.UserName = u.UserName;
// section.SectionID = u.SectionID;
// section.SectionAccess = u.SectionAccess;
// db.PA_Section.Add(section);
// db.SaveChanges();
// }
// }
// return Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet);
//}
#endregion
public ActionResult GetLineAccess(string UserName, string Section)
{
List <M_SP_LineAccess_Result> LineList = new List <M_SP_LineAccess_Result>();
try
{
LineList = db.M_SP_LineAccess(UserName, Section).ToList();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Users";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new
{
LineList = LineList,
}, JsonRequestBehavior.AllowGet));
}
public void sendMail(string subject, List <M_Section_Approver> Approver, string RefNo, string Directory)
{
try
{
#region BIPH
MailMessage mail = new MailMessage("*****@*****.**", "*****@*****.**");
SmtpClient client = new SmtpClient();
client.Port = 25;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Host = "10.113.10.1";
mail.Subject = "Brother User Reset Password";
string msg = "Hi " + "Test User" + "!<br /><br />";
msg = msg + "Your account has been locked due to 3 failed attempts, kindly change your password after your login. <br /><br />";
msg = msg + " Username: "******"Test User" + "<br />";
msg = msg + " Reset Login Password: "******"Test pass";
String vhtml = String.Empty;
//StreamReader reader = new StreamReader(Server.MapPath("/Template/email.html"));
//vhtml = reader.ReadToEnd();
//vhtml = vhtml.Replace("{{{body}}}", msg);
mail.IsBodyHtml = true;
mail.Body = vhtml;
client.Send(mail);
#endregion
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Mail";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = "******";
db.Error_Logs.Add(error);
db.SaveChanges();
}
}
public void SendTheMail(string RefNo)
{
try
{
List <string> AgencyList = new List <string>();
AgencyList = (from c in db.AF_DTRfiling where c.DTR_RefNo == RefNo select c.BIPH_Agency).ToList();
AgencyList = AgencyList.Distinct().ToList();
foreach (string Agency in AgencyList)
{
long? lineID, a;
string lineid = "";
if (System.Web.HttpContext.Current.Session["lINEID"] == null)
{
lineID = null;
}
else
{
a = null;
lineid = System.Web.HttpContext.Current.Session["lINEID"].ToString();
lineID = (lineid == "") ? a : Convert.ToInt64(lineid);
}
string Agencycode = (Agency == "") ? "BIPH" : Agency;
M_Agency AgencyDetails = (from c in db.M_Agency where c.AgencyCode == Agencycode select c).FirstOrDefault();
string templateFilename = "";
templateFilename = "StandardizeDTR_template.xlsx";
string dir = Path.GetTempPath();
string datetimeToday = DateTime.Now.ToString("yyMMddhhmmss");
string filename = string.Format("StandardizeDTR_template{0}.xlsx", datetimeToday);
FileInfo newFile = new FileInfo(Path.Combine(dir, filename));
FileInfo newFilecopy = new FileInfo(Path.Combine(dir, filename));
string apptemplatePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"TemplateFiles\StandardTemplate\", templateFilename);
FileInfo templateFile = new FileInfo(apptemplatePath);
M_Employee_Master_List current = (from c in db.M_Employee_Master_List where c.EmpNo == user.UserName select c).FirstOrDefault();
using (ExcelPackage package = new ExcelPackage(newFile, templateFile)) //-- With template.
{
List <GET_AF_DTRExport_Result> list = db.GET_AF_DTRExport(RefNo, Agency).ToList();
int start = 12;
ExcelWorksheet ExportData = package.Workbook.Worksheets["Standardized-DTR Form"];
for (int i = 0; i < list.Count; i++)
{
ExportData.Cells["B" + start].Value = list[i].EmployeeNo;
ExportData.Cells["C" + start].Value = list[i].Family_Name + ", " + list[i].First_Name;
ExportData.Cells["D" + start].Value = list[i].Concerns;
ExportData.Cells["E" + start].Value = list[i].Reason;
ExportData.Cells["F" + start].Value = list[i].DateFrom;
ExportData.Cells["G" + start].Value = list[i].DateTo;
ExportData.Cells["H" + start].Value = list[i].Timein;
ExportData.Cells["I" + start].Value = list[i].TimeOut;
ExportData.Cells["J" + start].Value = list[i].EmployeeAccept;
start++;
}
ExportData.Cells["C5"].Value = current.Department;
ExportData.Cells["J6"].Value = user.Section;
ExportData.Cells["J5"].Value = DateTime.Now.ToShortDateString();
ExportData.Cells["C1"].Value = AgencyDetails.AgencyName;
ExportData.Cells["C2"].Value = AgencyDetails.Address;
ExportData.Cells["C3"].Value = AgencyDetails.TelNo;
ExportData.Cells["I51"].Value = AgencyDetails.ISO_DTR;
string path = Server.MapPath(@"/PictureResources/AgencyLogo/" + AgencyDetails.Logo);
#region IMAGE
using (System.Drawing.Image image = System.Drawing.Image.FromFile(path))
{
var excelImage = ExportData.Drawings.AddPicture("logohere", image);
excelImage.SetSize(140, 69);
excelImage.SetPosition(0, 0, 1, 1);
}
#endregion
//string paths = @"\\192.168.200.100\Published Files\Brothers_AMS\" + filename;
//Stream stream = System.IO.File.Create(paths);
//package.SaveAs(stream);
//stream.Close();
//try
//{
// db.AF_SendAgency("*****@*****.**", filename);
//}
//catch (Exception err) { }
}
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - CS";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
}
public JsonResult ReadUploadedFile()
{
try {
var file = Request.Files[0];
int fileSize = file.ContentLength;
string fileName = file.FileName;
string TodayRefno = helper.GenerateCSRef();
string Section = "";
List <string> Unregistered = new List <string>();
using (var package = new ExcelPackage(file.InputStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int noOfCol = worksheet.Dimension.End.Column;
int noOfRow = worksheet.Dimension.End.Row;
int endColumn = worksheet.Dimension.Start.Column;
int startColumn = endColumn;
int startRowForTable = 14;
int totalNoOfTableRow = 30;
for (int x = 0; x < totalNoOfTableRow; x++)
{
string Empno = "";
try
{
Empno = worksheet.Cells[startRowForTable, 2].Value.ToString();
M_Employee_Master_List Employee = (from c in db.M_Employee_Master_List
where c.EmpNo == Empno
select c).FirstOrDefault();
if (Employee != null)
{
AF_ChangeSchedulefiling bemodify = (from c in db.AF_ChangeSchedulefiling where c.EmployeeNo == Employee.EmpNo && c.CS_RefNo == TodayRefno select c).FirstOrDefault();
if (bemodify == null)
{
#region Creating via upload
//Section = user.Section;//Employee.Section;
AF_ChangeSchedulefiling CSrequest = new AF_ChangeSchedulefiling();
CSrequest.CS_RefNo = TodayRefno;
CSrequest.BIPH_Agency = Employee.Company;
CSrequest.FileType = 3; //Upload
CSrequest.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == Empno orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == CSrequest.Section orderby c.ID descending select c.GroupSection).FirstOrDefault(); //otfile.Section;
CSrequest.EmployeeNo = Employee.EmpNo;
CSrequest.CSType = worksheet.Cells[startRowForTable, 4].Value.ToString();
CSrequest.DateFrom = Convert.ToDateTime(worksheet.Cells[startRowForTable, 5].Value);
CSrequest.DateTo = Convert.ToDateTime(worksheet.Cells[startRowForTable, 6].Value);
DateTime csin = Convert.ToDateTime(worksheet.Cells[startRowForTable, 7].Value.ToString());
DateTime csout = Convert.ToDateTime(worksheet.Cells[startRowForTable, 8].Value.ToString());
CSrequest.CSin = csin.ToString("HH:mm");
CSrequest.CSout = csout.ToString("HH:mm");
//if (Employee.EmpNo.Contains("BIPH"))
//{
CSrequest.EmployeeAccept = db.TT_GETTIME().FirstOrDefault(); //DateTime.Now;;
//}
CSrequest.Reason = worksheet.Cells[startRowForTable, 9].Value.ToString();
CSrequest.Status = 0;
CSrequest.StatusMax = 2;
CSrequest.CreateID = user.UserName;
CSrequest.CreateDate = DateTime.Now;;
CSrequest.UpdateID = user.UserName;
CSrequest.UpdateDate = DateTime.Now;;
try
{
db.AF_ChangeSchedulefiling.Add(CSrequest);
db.SaveChanges();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - OT Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
#endregion
}
else
{
#region modifying via upload
//Section = Employee.Section;
bemodify.CS_RefNo = TodayRefno;
bemodify.BIPH_Agency = Employee.Company;
bemodify.FileType = 3; //Upload
bemodify.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == Empno orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == bemodify.Section orderby c.ID descending select c.GroupSection).FirstOrDefault(); //otfile.Section;
bemodify.EmployeeNo = Employee.EmpNo;
bemodify.CSType = worksheet.Cells[startRowForTable, 4].Value.ToString();
bemodify.DateFrom = Convert.ToDateTime(worksheet.Cells[startRowForTable, 5].Value);
bemodify.DateTo = Convert.ToDateTime(worksheet.Cells[startRowForTable, 6].Value);
DateTime csin = Convert.ToDateTime(worksheet.Cells[startRowForTable, 7].Value.ToString());
DateTime csout = Convert.ToDateTime(worksheet.Cells[startRowForTable, 8].Value.ToString());
bemodify.CSin = csin.ToString("HH:mm");
bemodify.CSout = csout.ToString("HH:mm");
bemodify.Reason = worksheet.Cells[startRowForTable, 9].Value.ToString();
bemodify.Status = 0;
bemodify.StatusMax = 2;
bemodify.UpdateID = user.UserName;
bemodify.UpdateDate = DateTime.Now;;
if (Employee.EmpNo.Contains("BIPH"))
{
bemodify.EmployeeAccept = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
}
try
{
db.Entry(bemodify).State = EntityState.Modified;
db.SaveChanges();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - CS Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
#endregion
}
}
else
{
if (Empno != "")
{
Unregistered.Add(Empno);
}
}
}
catch (Exception err)
{
if (Empno != "")
{
Unregistered.Add(Empno);
}
}
startRowForTable++;
}
M_Section_ApproverStatus checker = (from c in db.M_Section_ApproverStatus where c.RefNo == TodayRefno select c).FirstOrDefault();
if (checker == null)
{
#region GET approver & Email
//string SectionID = (from c in db.M_Cost_Center_List
// where c.Cost_Center == Section
// select c.ID).FirstOrDefault().ToString();
List <M_Section_Approver> approver = (from c in db.M_Section_Approver where c.Section == Section select c).ToList();
#endregion
#region Generate CS Status
foreach (M_Section_Approver approv in approver)
{
M_Section_ApproverStatus approverstat = new M_Section_ApproverStatus();
approverstat.Position = approv.Position;
approverstat.EmployeeNo = approv.EmployeeNo;
approverstat.Section = Section;
approverstat.RefNo = helper.GenerateCSRef();
approverstat.Approved = 0;
approverstat.OverTimeType = "";
approverstat.CreateID = user.UserName;
approverstat.CreateDate = DateTime.Now;;
approverstat.UpdateID = user.UserName;
approverstat.UpdateDate = DateTime.Now;;
db.M_Section_ApproverStatus.Add(approverstat);
db.SaveChanges();
}
#endregion
}
return(Json(new { Unregistered = Unregistered, Failed = "" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
return(Json(new { Failed = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult SaveDTR(AF_DTRfiling Filing, string Reasons, string EmployeeNos, string DTRType, string concerns)
{
List <string> concernsList = concerns.Split(',').ToList();
List <string> ReasonsList = Reasons.Split(',').ToList();
List <string> EmployeeNosLis = EmployeeNos.Split(',').ToList();
int emploCounter = 0;
string DTRRefNo = helper.GenerateDTRRef();
string Section = "";
foreach (string reason in ReasonsList)
{
string EmploNos = EmployeeNosLis[emploCounter];
string Concern = concernsList[emploCounter];
AF_DTRfiling dtrfile = new AF_DTRfiling();
dtrfile.DTR_RefNo = DTRRefNo;
dtrfile.BIPH_Agency = Filing.BIPH_Agency;
dtrfile.EmployeeNo = EmploNos;
dtrfile.FileType = Filing.FileType;
dtrfile.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == EmploNos orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == dtrfile.Section orderby c.ID descending select c.GroupSection).FirstOrDefault();//otfile.Section;//dtrfile.Section;
//dtrfile.Line_Team = 1;// (from c in db.M_Employee_Master_List where c.EmpNo == EmploNos select c.LineID).FirstOrDefault();
dtrfile.OvertimeType = Filing.OvertimeType;
dtrfile.DateFrom = Filing.DateFrom.Date;
dtrfile.DateTo = Filing.DateTo.AddHours(23).AddMinutes(59).AddSeconds(59);
dtrfile.OTin = Filing.OTin;
dtrfile.OTout = Filing.OTout;
dtrfile.Timein = Filing.Timein;
dtrfile.TimeOut = Filing.TimeOut;
dtrfile.Reason = reason;
dtrfile.Concerns = Concern;
dtrfile.Status = 0;
//if (EmploNos.Contains("BIPH"))
//{
dtrfile.EmployeeAccept = db.TT_GETTIME().FirstOrDefault(); //DateTime.Now;;
//}
if (DTRType == "HR")
{
dtrfile.StatusMax = (Filing.OvertimeType == "SundayHoliday") ? 4 : 2;
}
else
{
dtrfile.StatusMax = 1; //Supervisor only
}
dtrfile.CreateID = user.UserName;
dtrfile.CreateDate = DateTime.Now;
dtrfile.UpdateID = user.UserName;
dtrfile.UpdateDate = DateTime.Now;
try
{
db.AF_DTRfiling.Add(dtrfile);
db.SaveChanges();
}
catch (Exception err) {
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - DTR";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
emploCounter++;
}
#region GET approver & Email
List <M_Section_Approver> approver = (from c in db.M_Section_Approver
where c.Section == Section &&
c.Position != "GeneralManager" &&
c.Position != "FactoryGeneralManager"
select c).ToList();
#endregion
#region Generate OT Status
foreach (M_Section_Approver approv in approver)
{
M_Section_ApproverStatus approverstat = new M_Section_ApproverStatus();
approverstat.Position = approv.Position;
approverstat.EmployeeNo = approv.EmployeeNo;
approverstat.Section = Section;
approverstat.RefNo = DTRRefNo;
approverstat.Approved = 0;
approverstat.OverTimeType = Filing.OvertimeType;
approverstat.CreateID = user.UserName;
approverstat.CreateDate = DateTime.Now;
approverstat.UpdateID = user.UserName;
approverstat.UpdateDate = DateTime.Now;
db.M_Section_ApproverStatus.Add(approverstat);
db.SaveChanges();
}
#endregion
return(Json(new { DTRRefNo = DTRRefNo }, JsonRequestBehavior.AllowGet));
}
public ActionResult SaveOT(AF_OTfiling Filing, string Purposes, string EmployeeNos)
{
try
{
List <string> PurposeList = Purposes.Split(',').ToList();
List <string> EmployeeNosLis = EmployeeNos.Split(',').ToList();
int emploCounter = 0;
string OTRefnow = helper.GenerateOTRef();
//long SectionID = 0;
string Section = "";
foreach (string purpose in PurposeList)
{
string EmploNos = EmployeeNosLis[emploCounter];
AF_OTfiling otfile = new AF_OTfiling();
otfile.OT_RefNo = OTRefnow;
otfile.BIPH_Agency = Filing.BIPH_Agency;
otfile.EmployeeNo = EmploNos;
otfile.FileType = Filing.FileType;
long?Schedule_current = (from c in db.M_Employee_Master_List_Schedule where c.EmployeeNo == EmploNos orderby c.ID descending select c.ScheduleID).FirstOrDefault();
otfile.ScheduleID = Schedule_current;
otfile.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == EmploNos orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
//SectionID = otfile.Section;
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == otfile.Section orderby c.ID descending select c.GroupSection).FirstOrDefault();//otfile.Section;
otfile.OvertimeType = Filing.OvertimeType;
otfile.DateFrom = Filing.DateFrom.Date;
otfile.DateTo = Filing.DateFrom.Date;
otfile.OTin = Filing.OTin;
otfile.OTout = Filing.OTout;
otfile.Purpose = purpose;
otfile.Status = 0;
otfile.StatusMax = (otfile.OvertimeType == "SundayHoliday") ? 4 : 2;
//if (EmploNos.Contains("BIPH"))
//{
otfile.EmployeeAccept = db.TT_GETTIME().FirstOrDefault(); //DateTime.Now;;
//}
otfile.CreateID = user.UserName;
otfile.CreateDate = DateTime.Now;
otfile.UpdateID = user.UserName;
otfile.UpdateDate = DateTime.Now;;
try
{
db.AF_OTfiling.Add(otfile);
db.SaveChanges();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - OT Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
emploCounter++;
}
M_Section_ApproverStatus checker = (from c in db.M_Section_ApproverStatus where c.RefNo == OTRefnow && c.OverTimeType == Filing.OvertimeType select c).FirstOrDefault();
if (checker == null)
{
#region GET approver & Email
//string SectionID = (from c in db.M_Cost_Center_List
// where c.Cost_Center == Section
// select c.ID).FirstOrDefault().ToString();
List <M_Section_Approver> approver = (from c in db.M_Section_Approver where c.Section == Section select c).ToList();
#region EMAIL FUNCTION TRANSFER TO SERVER JOBS
//try
//{
// db.AF_EmailOTRequest();
//}
//catch (Exception err)
//{
// Error_Logs error = new Error_Logs();
// error.PageModule = "Application Form - OT Request";
// error.ErrorLog = err.Message;
// error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
// error.Username = user.UserName;
// db.Error_Logs.Add(error);
// db.SaveChanges();
//}
//EmailApproverController email = new EmailApproverController();
////M_Employee_Master_List getSupervisor = (from c in db.M_Employee_Master_List where c.EmpNo == approver.Supervisor select c).FirstOrDefault();
//email.sendMail("OT Approval", approver, OTRefnow, Session["emailpath"].ToString());
#endregion
#endregion
#region Generate OT Status
foreach (M_Section_Approver approv in approver)
{
M_Section_ApproverStatus approverstat = new M_Section_ApproverStatus();
approverstat.Position = approv.Position;
approverstat.EmployeeNo = approv.EmployeeNo;
approverstat.Section = Section;
approverstat.RefNo = OTRefnow;
approverstat.Approved = 0;
approverstat.OverTimeType = Filing.OvertimeType;
approverstat.CreateID = user.UserName;
approverstat.CreateDate = DateTime.Now;;
approverstat.UpdateID = user.UserName;
approverstat.UpdateDate = DateTime.Now;;
db.M_Section_ApproverStatus.Add(approverstat);
db.SaveChanges();
}
#endregion
}
return(Json(new { OTRefnow = OTRefnow }, JsonRequestBehavior.AllowGet));
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - OT Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { OTRefnow = "" }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult DownloadTemplate(string Agency)
{
try
{
long? lineID, a;
string searchnow = System.Web.HttpContext.Current.Session["Searchvalueot"].ToString();
string lineid = "";
if (System.Web.HttpContext.Current.Session["lINEID"] == null)
{
lineID = null;
}
else
{
a = null;
lineid = System.Web.HttpContext.Current.Session["lINEID"].ToString();
lineID = (lineid == "") ? a : Convert.ToInt64(lineid);
}
Agency = (Agency == "") ? "BIPH" : Agency;
M_Agency AgencyDetails = (from c in db.M_Agency where c.AgencyCode == Agency select c).FirstOrDefault();
string templateFilename = "StandardizeCS_template.xlsx";
string dir = Path.GetTempPath();
string filename = string.Format("StandardizeCS_template.xlsx");
FileInfo newFile = new FileInfo(Path.Combine(dir, filename));
string apptemplatePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"TemplateFiles\StandardTemplate\", templateFilename);
FileInfo templateFile = new FileInfo(apptemplatePath);
M_Employee_Master_List current = (from c in db.M_Employee_Master_List where c.EmpNo == user.UserName select c).FirstOrDefault();
ObjectParameter totalCount = new ObjectParameter("TotalCount", typeof(int));
using (ExcelPackage package = new ExcelPackage(newFile, templateFile)) //-- With template.
{
List <GET_Employee_OTFiling_Result> list = new List <GET_Employee_OTFiling_Result>();
list = db.GET_Employee_OTFiling(Agency, user.CostCode, lineID, "", 0, 100000, "", totalCount).ToList();
if (!string.IsNullOrEmpty(searchnow))//filter
{
#region null remover
list = list.Where(xx => xx.EmpNo != null).ToList();
list = list.Where(xx => xx.First_Name != null).ToList();
list = list.Where(xx => xx.Family_Name != null).ToList();
#endregion
list = list.Where(x => x.First_Name.ToLower().Contains(searchnow.ToLower()) ||
x.Family_Name.ToLower().Contains(searchnow.ToLower()) ||
x.EmpNo.Contains(searchnow)
).ToList <GET_Employee_OTFiling_Result>();
}
int start = 14;
ExcelWorksheet ExportData = package.Workbook.Worksheets["Standardized-CS Form"];
if (list.Count < 30)
{
for (int i = 0; i < list.Count; i++)
{
ExportData.Cells["B" + start].Value = list[i].EmpNo;
ExportData.Cells["C" + start].Value = list[i].Family_Name + ", " + list[i].First_Name;
start++;
}
}
ExportData.Cells["C5"].Value = current.Department;
ExportData.Cells["J5"].Value = user.Section;
ExportData.Cells["C1"].Value = AgencyDetails.AgencyName;
ExportData.Cells["C2"].Value = AgencyDetails.Address;
ExportData.Cells["C3"].Value = AgencyDetails.TelNo;
ExportData.Cells["H51"].Value = AgencyDetails.ISO_CS;
string path = Server.MapPath(@"/PictureResources/AgencyLogo/" + AgencyDetails.Logo);
#region IMAGE
using (System.Drawing.Image image = System.Drawing.Image.FromFile(path))
{
var excelImage = ExportData.Drawings.AddPicture("logohere", image);
excelImage.SetSize(140, 69);
excelImage.SetPosition(0, 0, 0, 10);
}
#endregion
return(File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
}
catch (Exception err) {
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - Change Schedule";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { }, JsonRequestBehavior.AllowGet));
}
public JsonResult ReadUploadedFile()
{
try {
var file = Request.Files[0];
int fileSize = file.ContentLength;
string fileName = file.FileName;
string TodayRefno = helper.GenerateOTRef();
string Section = "";
string OTType = "";
List <AF_OTfiling> OTFilingList = new List <AF_OTfiling>();
List <string> Unregistered = new List <string>();
using (var package = new ExcelPackage(file.InputStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int noOfCol = worksheet.Dimension.End.Column;
int noOfRow = worksheet.Dimension.End.Row;
int endColumn = worksheet.Dimension.Start.Column;
int startColumn = endColumn;
int startRowForTable = 16;
int totalNoOfTableRow = 25;
#region Find Type
if (worksheet.Cells[12, 3].Value.ToString().ToLower().Contains("x"))
{
OTType = "SundayHoliday";
}
else if (worksheet.Cells[11, 6].Value.ToString().ToLower().Contains("x"))
{
OTType = "LegalHoliday";
}
else if (worksheet.Cells[12, 6].Value.ToString().ToLower().Contains("x"))
{
OTType = "SpecialHoliday";
}
else
{
OTType = "Regular";
}
#endregion
for (int x = 0; x < totalNoOfTableRow; x++)
{
string Empno = "";
try
{
Empno = worksheet.Cells[startRowForTable, 3].Value.ToString();
M_Employee_Master_List Employee = (from c in db.M_Employee_Master_List
where c.EmpNo == Empno
select c).FirstOrDefault();
if (Employee != null)
{
AF_OTfiling bemodify = (from c in db.AF_OTfiling where c.EmployeeNo == Employee.EmpNo && c.OT_RefNo == TodayRefno select c).FirstOrDefault();
if (bemodify == null)
{
#region Creating via upload
AF_OTfiling OTrequest = new AF_OTfiling();
OTrequest.OT_RefNo = TodayRefno;
OTrequest.BIPH_Agency = Employee.Company;
OTrequest.FileType = 3; //Upload
OTrequest.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == Empno orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == OTrequest.Section orderby c.ID descending select c.GroupSection).FirstOrDefault(); //otfile.Section;
OTrequest.EmployeeNo = Employee.EmpNo;
OTrequest.OvertimeType = OTType;
OTrequest.DateFrom = Convert.ToDateTime(worksheet.Cells[6, 9].Value);
OTrequest.DateTo = Convert.ToDateTime(worksheet.Cells[6, 9].Value);
OTrequest.OTin = worksheet.Cells[startRowForTable, 5].Value.ToString();
OTrequest.OTout = worksheet.Cells[startRowForTable, 6].Value.ToString();
OTrequest.Purpose = worksheet.Cells[startRowForTable, 8].Value.ToString();
OTrequest.Status = 0;
OTrequest.StatusMax = (OTrequest.OvertimeType == "SundayHoliday") ? 4 : 2;
OTrequest.EmployeeAccept = db.TT_GETTIME().FirstOrDefault();
OTrequest.CreateID = user.UserName;
OTrequest.CreateDate = DateTime.Now;;
OTrequest.UpdateID = user.UserName;
OTrequest.UpdateDate = DateTime.Now;;
try
{
db.AF_OTfiling.Add(OTrequest);
db.SaveChanges();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - OT Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
#endregion
}
else
{
#region modifying via upload
//Section = Employee.CostCode;
bemodify.OT_RefNo = TodayRefno;
bemodify.BIPH_Agency = Employee.Company;
bemodify.FileType = 3; //Upload
bemodify.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == Empno orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == bemodify.Section orderby c.ID descending select c.GroupSection).FirstOrDefault(); //otfile.Section;
bemodify.EmployeeNo = Employee.EmpNo;
bemodify.OvertimeType = OTType;
bemodify.DateFrom = Convert.ToDateTime(worksheet.Cells[6, 8].Value);
bemodify.DateTo = Convert.ToDateTime(worksheet.Cells[6, 8].Value);
bemodify.OTin = worksheet.Cells[startRowForTable, 4].Value.ToString();
bemodify.OTout = worksheet.Cells[startRowForTable, 5].Value.ToString();
bemodify.Purpose = worksheet.Cells[startRowForTable, 7].Value.ToString();
bemodify.Status = 0;
bemodify.StatusMax = (bemodify.OvertimeType == "SundayHoliday") ? 4 : 2;
bemodify.EmployeeAccept = db.TT_GETTIME().FirstOrDefault();
bemodify.UpdateID = user.UserName;
bemodify.UpdateDate = DateTime.Now;;
try
{
db.Entry(bemodify).State = EntityState.Modified;
db.SaveChanges();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - OT Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
#endregion
}
}
else
{
if (Empno != "")
{
Unregistered.Add(Empno);
}
}
}
catch (Exception err)
{
if (Empno != "")
{
Unregistered.Add(Empno);
}
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - OT Request";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
//return Json(new { Failed = "Failed" }, JsonRequestBehavior.AllowGet);
}
startRowForTable++;
}
M_Section_ApproverStatus checker = (from c in db.M_Section_ApproverStatus where c.RefNo == TodayRefno select c).FirstOrDefault();
if (checker == null)
{
#region GET approver & Email
//string SectionID = (from c in db.M_Cost_Center_List
// where c.Cost_Center == Section
// select c.ID).FirstOrDefault().ToString();
List <M_Section_Approver> approver = (from c in db.M_Section_Approver where c.Section == Section select c).ToList();
#endregion
#region Generate OT Status
foreach (M_Section_Approver approv in approver)
{
M_Section_ApproverStatus approverstat = new M_Section_ApproverStatus();
approverstat.Position = approv.Position;
approverstat.EmployeeNo = approv.EmployeeNo;
approverstat.Section = Section;
approverstat.RefNo = helper.GenerateOTRef();
approverstat.Approved = 0;
approverstat.OverTimeType = OTType;
approverstat.CreateID = user.UserName;
approverstat.CreateDate = DateTime.Now;;
approverstat.UpdateID = user.UserName;
approverstat.UpdateDate = DateTime.Now;;
db.M_Section_ApproverStatus.Add(approverstat);
db.SaveChanges();
}
#endregion
}
return(Json(new { Unregistered = Unregistered, Failed = "" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
return(Json(new { Failed = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult SaveCS(AF_ChangeSchedulefiling Filing, string Reasons, string EmployeeNos)
{
List <string> ReasonList = Reasons.Split(',').ToList();
List <string> EmployeeNosLis = EmployeeNos.Split(',').ToList();
int emploCounter = 0;
string CSRefnow = helper.GenerateCSRef();
string Section = "";
foreach (string reason in ReasonList)
{
string EmploNos = EmployeeNosLis[emploCounter];
AF_ChangeSchedulefiling csfile = new AF_ChangeSchedulefiling();
csfile.CS_RefNo = CSRefnow;
csfile.CSType = Filing.CSType;
csfile.BIPH_Agency = Filing.BIPH_Agency;
csfile.EmployeeNo = EmploNos;
csfile.FileType = Filing.FileType;
csfile.Section = (from c in db.M_Employee_CostCenter where c.EmployNo == EmploNos orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
Section = (from c in db.M_Cost_Center_List where c.Cost_Center == csfile.Section orderby c.ID descending select c.GroupSection).FirstOrDefault();//otfile.Section;//csfile.Section;
csfile.DateFrom = Filing.DateFrom.Date;
csfile.DateTo = Filing.DateTo.AddHours(23).AddMinutes(59).AddSeconds(59);
csfile.CSin = Filing.CSin;
csfile.CSout = Filing.CSout;
csfile.Reason = reason;
csfile.Status = 0;
csfile.StatusMax = 2;
csfile.CreateID = user.UserName;
csfile.CreateDate = DateTime.Now;
csfile.UpdateID = user.UserName;
csfile.UpdateDate = DateTime.Now;
csfile.Schedule = Filing.Schedule;
//if (EmploNos.Contains("BIPH"))
//{
csfile.EmployeeAccept = db.TT_GETTIME().FirstOrDefault(); //DateTime.Now;;
//}
try
{
db.AF_ChangeSchedulefiling.Add(csfile);
db.SaveChanges();
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - Change Schedule";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault(); //DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
emploCounter++;
}
#region Generate CS Status
db.AF_EmailCSRequestApprovers();
db.AF_EmailCSRequest(CSRefnow);
#endregion
#region REmoved
//M_Section_ApproverStatus checker = (from c in db.M_Section_ApproverStatus where c.RefNo == CSRefnow select c).FirstOrDefault();
//if (checker == null)
//{
// #region GET approver & Email
// //string SectionID = (from c in db.M_Cost_Center_List
// // where c.Cost_Center == Section
// // select c.ID).FirstOrDefault().ToString();
// List<M_Section_Approver> approver = (from c in db.M_Section_Approver
// where c.Section == Section
// && c.Position != "GeneralManager"
// && c.Position != "FactoryGeneralManager"
// select c).ToList();
// #endregion
// #region Generate CS Status
// db.AF_EmailCSRequestApprovers();
// //foreach (M_Section_Approver approv in approver)
// //{
// // M_Section_ApproverStatus approverstat = new M_Section_ApproverStatus();
// // approverstat.Position = approv.Position;
// // approverstat.EmployeeNo = approv.EmployeeNo;
// // approverstat.Section = Section;
// // approverstat.RefNo = CSRefnow;
// // approverstat.Approved = 0;
// // approverstat.OverTimeType = "";
// // approverstat.CreateID = user.UserName;
// // approverstat.CreateDate = DateTime.Now;
// // approverstat.UpdateID = user.UserName;
// // approverstat.UpdateDate = DateTime.Now;
// // db.M_Section_ApproverStatus.Add(approverstat);
// // db.SaveChanges();
// //}
// #endregion
//}
//db.AF_EmailCSRequest(CSRefnow);
#endregion
return(Json(new { CSRefnow = CSRefnow }, JsonRequestBehavior.AllowGet));
}
public ActionResult Authenticate(UsersLog user)
{
string result = "";
db.Database.CommandTimeout = 0;
try
{
string pass = EncodePasswordMd5(user.Password);
M_Users check = (from c in db.M_Users
where c.UserName == user.UserName &&
c.Password == pass &&
c.IsDeleted == false
select c).FirstOrDefault();
check.CostCode = (from c in db.M_Employee_CostCenter where c.EmployNo == user.UserName orderby c.ID descending select c.CostCenter_AMS).FirstOrDefault();
string CostCodenow = check.CostCode;
check.Section = (from c in db.M_Cost_Center_List where c.Cost_Center == CostCodenow select c.GroupSection).FirstOrDefault();
if (check != null)
{
bool rememberme = false;
if (user.Rememberme)
{
rememberme = true;
}
string emailtemplatepath = Server.MapPath(@"~/Content/EmailForm/OTEmail.html");
System.Web.HttpContext.Current.Session["emailpath"] = emailtemplatepath;
System.Web.HttpContext.Current.Session["UserName"] = check.FirstName + ' ' + check.LastName;
System.Web.HttpContext.Current.Session["user"] = check;
FormsAuthentication.SetAuthCookie(user.UserName, true);
FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(
1,
user.UserName,
DateTime.Now,
DateTime.Now.AddMinutes(FormsAuthentication.Timeout.TotalMinutes),
rememberme,
user.ToString());
RefreshPageAccess(check.UserName, check.Section);
List <CostCenterM> newCostCode = (from c in db.M_Cost_Center_List where c.GroupSection == "" || c.GroupSection == null
select new CostCenterM {
CostCodenew = c.Cost_Center,
CostCodenewname = c.Section
}).ToList();
System.Web.HttpContext.Current.Session["newCostCode"] = newCostCode;
}
result = (check == null) ? "Failed" : "Success";
if (result == "Failed")
{
//Error_Logs error = new Error_Logs();
//error.PageModule = "Login";
//error.ErrorLog = "Incorrect Username or Password";
//error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
//error.Username = user.UserName;
//db.Error_Logs.Add(error);
//db.SaveChanges();
}
string urlmail = (Session["urlmail"] != null) ? Session["urlmail"].ToString() : "/";
return(Json(new { result = result, urlmail = urlmail }, JsonRequestBehavior.AllowGet));
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Login";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { result = result, urlmail = "" }, JsonRequestBehavior.AllowGet));
}
}