public ActionResult GetEmployeeNo(double RFID)
{
try
{
string SourceValue = dec2Hex(Convert.ToInt64(RFID));
string Hexvalue = SourceValue.Substring(SourceValue.Length - 4);
string Prefix = SourceValue.Remove(SourceValue.Length - 4).ToUpper();
string THERFID = hex2Dec(Hexvalue).ToString();
M_Employee_Master_List emp = (from c in db.M_Employee_Master_List
where c.RFID == THERFID
select c).FirstOrDefault();
return(Json(new { empno = emp.EmpNo }, JsonRequestBehavior.AllowGet));
}
catch (Exception err)
{
return(Json(new { empno = "" }, JsonRequestBehavior.AllowGet));
}
}
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 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 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.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 TimeValidate(List <string> list, DateTime DateFrom, string OTin, string OTOut, string Type)
{
bool Allow = true;
List <string> EmpConflict = new List <string>();
List <string> EmpAlready = new List <string>();
long? GetSchedID;
if (Type != "Regular")
{
foreach (string emp in list)
{
string currentRefno = helper.GenerateOTRef();
DateTime a = DateFrom.Date;
AF_OTfiling otnow = (from c in db.AF_OTfiling where c.DateFrom == a && c.EmployeeNo == emp select c).FirstOrDefault();
//AF_OTfiling otnow2 = (from c in db.AF_OTfiling where c.OT_RefNo == currentRefno && c.OTout == OTOut && c.Status >= 0 && c.EmployeeNo == emp select c).FirstOrDefault();
if (otnow != null)// || otnow2 != null)
{
EmpAlready.Add(list[0]);
Allow = false;
}
else
{
Allow = true;
}
}
}
else
{
foreach (string emp in list)
{
M_Employee_Master_List Employee = (from c in db.M_Employee_Master_List where c.EmpNo == emp select c).FirstOrDefault();
GetSchedID = (from c in db.M_Employee_Master_List_Schedule where c.EmployeeNo == emp orderby c.ID descending select c.ScheduleID).FirstOrDefault();
string Schedule = (from c in db.M_Schedule where c.ID == GetSchedID select c.Type).FirstOrDefault();
M_Schedule schedcheck = (from c in db.M_Schedule where c.Type == Schedule select c).FirstOrDefault();
DateTime ShiftStart = DateTime.Parse(schedcheck.Timein.ToString());
DateTime ShiftEnd = DateTime.Parse(schedcheck.TimeOut.ToString());
DateTime TimecheckIN = DateTime.Parse(OTin);
DateTime TimecheckOUT = DateTime.Parse(OTOut);
if (ShiftStart > ShiftEnd)
{
ShiftEnd = ShiftEnd.AddDays(1);
}
//if ((ShiftStart > TimecheckIN
// && TimecheckIN < ShiftEnd)
// ||( ShiftStart > TimecheckOUT
// && TimecheckOUT < ShiftEnd))
//{
if (TimecheckIN.ToString("HH:mm") == ShiftEnd.ToString("HH:mm") || TimecheckOUT.ToString("HH:mm") == ShiftStart.ToString("HH:mm"))
{
Allow = (Allow == false) ? false : true;
}
else
{
EmpConflict.Add(emp);
Allow = false;
//break;
}
string currentRefno = helper.GenerateOTRef();
DateTime a = DateFrom.Date;
AF_OTfiling otnow = (from c in db.AF_OTfiling where c.OT_RefNo == currentRefno && c.OTin == OTin && c.Status >= 0 && c.DateFrom == a && c.EmployeeNo == emp select c).FirstOrDefault();
//AF_OTfiling otnow2 = (from c in db.AF_OTfiling where c.OT_RefNo == currentRefno && c.OTout == OTOut && c.Status >= 0 && c.EmployeeNo == emp select c).FirstOrDefault();
if (otnow != null)// || otnow2 != null)
{
EmpAlready.Add(list[0]);
Allow = false;
}
}
}
return(Json(new { Allow = Allow, EmpConflict = EmpConflict, EmpAlready = EmpAlready }, JsonRequestBehavior.AllowGet));
}
private void GetEmployeeDetails(object sender, KeyEventArgs e)
{
try
{
if (e.Key == System.Windows.Input.Key.Enter)
{
string SourceValue = dec2Hex(Convert.ToInt64(RFID.Text));
string Hexvalue = SourceValue.Substring(SourceValue.Length - 4);
string Prefix = SourceValue.Remove(SourceValue.Length - 4).ToUpper();
string THERFID = hex2Dec(Hexvalue).ToString();
string Empnumber = "";
string rawrfid = Convert.ToInt64(RFID.Text).ToString();
bool rfidconverted = false;
M_Employee_Master_List list = new M_Employee_Master_List();
using (SQLiteConnection c = new SQLiteConnection(AppConfig.AMSDBSQLite))
{
if (c.State == System.Data.ConnectionState.Open)
{
c.Close();
}
c.Open();
//Converted RFID
string sql = "SELECT * from M_Employee_Master_List WHERE Status = 'ACTIVE' AND RFID = " + THERFID;
using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
{
try
{
int affectedRows = cmd.ExecuteNonQuery();
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
rfidconverted = true;
while (rdr.Read())
{
Empnumber = rdr["EmpNo"].ToString();
list.ID = Convert.ToInt64(rdr["ID"]);
list.REFID = rdr["REFID"].ToString();
list.ADID = rdr["ADID"].ToString();
list.EmpNo = rdr["EmpNo"].ToString();
list.Family_Name_Suffix = rdr["Family_Name_Suffix"].ToString();
list.Family_Name = rdr["Family_Name"].ToString();
list.First_Name = rdr["First_Name"].ToString();
list.Middle_Name = rdr["Middle_Name"].ToString();
list.Date_Hired = rdr["Date_Hired"].ToString();
list.Date_Resigned = rdr["Date_Resigned"].ToString();
list.Status = rdr["Status"].ToString();
list.Emp_Category = rdr["Emp_Category"].ToString();
list.Date_Regularized = rdr["Date_Regularized"].ToString();
list.Position = rdr["Position"].ToString();
list.Email = rdr["Email"].ToString();
list.Gender = rdr["Gender"].ToString();
list.RFID = rdr["RFID"].ToString();
list.EmployeePhoto = rdr["EmployeePhoto"].ToString();
list.Section = rdr["Section"].ToString();
list.Department = rdr["Department"].ToString();
list.Company = rdr["Company"].ToString();
list.CostCode = rdr["CostCode"].ToString();
//Show the Details
Empno.Content = Empnumber;
Name.Content = list.First_Name + ' ' + list.Family_Name;
Position.Content = GetEmployeePosition(Empnumber);
Status.Content = GetEmployeeStatus(Empnumber);
Comp.Content = list.Company;
this.empphoto.Source = new BitmapImage(new Uri(AppConfig.forderPath + "\\PictureResources\\EmployeePhoto\\" + list.EmployeePhoto));
}
}
else
{
rfidconverted = false;
}
}
}
catch (Exception err)
{
}
}
if (rfidconverted == false)
{
//Unconverted
sql = "SELECT * from M_Employee_Master_List WHERE Status = 'ACTIVE' AND RFID = " + RFID.Text;
using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
{
try
{
int affectedRows = cmd.ExecuteNonQuery();
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
while (rdr.Read())
{
Empnumber = rdr["EmpNo"].ToString();
list.ID = Convert.ToInt64(rdr["ID"]);
list.REFID = rdr["REFID"].ToString();
list.ADID = rdr["ADID"].ToString();
list.EmpNo = rdr["EmpNo"].ToString();
list.Family_Name_Suffix = rdr["Family_Name_Suffix"].ToString();
list.Family_Name = rdr["Family_Name"].ToString();
list.First_Name = rdr["First_Name"].ToString();
list.Middle_Name = rdr["Middle_Name"].ToString();
list.Date_Hired = rdr["Date_Hired"].ToString();
list.Date_Resigned = rdr["Date_Resigned"].ToString();
list.Status = rdr["Status"].ToString();
list.Emp_Category = rdr["Emp_Category"].ToString();
list.Date_Regularized = rdr["Date_Regularized"].ToString();
list.Position = rdr["Position"].ToString();
list.Email = rdr["Email"].ToString();
list.Gender = rdr["Gender"].ToString();
list.RFID = rdr["RFID"].ToString();
list.EmployeePhoto = rdr["EmployeePhoto"].ToString();
list.Section = rdr["Section"].ToString();
list.Department = rdr["Department"].ToString();
list.Company = rdr["Company"].ToString();
list.CostCode = rdr["CostCode"].ToString();
//Show the Details
Empno.Content = Empnumber;
Name.Content = list.First_Name + ' ' + list.Family_Name;
Position.Content = GetEmployeePosition(Empnumber);
Status.Content = GetEmployeeStatus(Empnumber);
Comp.Content = list.Company;
this.empphoto.Source = new BitmapImage(new Uri(AppConfig.forderPath + "\\PictureResources\\EmployeePhoto\\" + list.EmployeePhoto));
}
}
else
{
rfidconverted = false;
}
}
}
catch (Exception err)
{
}
}
}
}
string EmpCostCode = GetEmployeeCostCode(Empnumber);
}
}
catch (Exception err)
{
}
}