private List <TblPositionRoute> GetTblPositionRoute(int skip, int take, int group, string sort, string filter, Dictionary <string, object> valuesObjects, out int fullCount, string company, out List <EmployeesView> EmpList)
{
using (var entity = new ccnewEntities(GetSqlConnectionString(company)))
{
IQueryable <TblPositionRoute> query;
if (filter != null)
{
filter = filter + " and it.TblPositionRouteHeader ==(@Group0)";
valuesObjects.Add("Group0", group);
var parameterCollection = ConvertToParamters(valuesObjects);
fullCount = entity.TblPositionRoutes.Where(filter, parameterCollection.ToArray()).Count();
query = entity.TblPositionRoutes.Include(t => t.TblStore).Include(t => t.TblStore1).Include("TblPositionRouteHeader1").Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = entity.TblPositionRoutes.Count(x => x.TblPositionRouteHeader == group);
query = entity.TblPositionRoutes.Include(t => t.TblStore).Include(t => t.TblStore1).Include("TblPositionRouteHeader1").OrderBy(sort).Where(v => v.TblPositionRouteHeader == group).Skip(skip).Take(take);
}
using (var context = new TimeAttEntities())
{
List <string> intList = query.Select(x => x.Emplid).ToList();
EmpList = context.EmployeesViews.Where(x => intList.Contains(x.Emplid)).ToList();
}
return(query.ToList());
}
}
private int DeleteTblEmployeeShift(TblEmployeeShift row)
{
if (!Att.AttService.EmpCanEdit(row.TransDate, row.EmpId))
{
throw new Exception("Cannot edit a transaction because this period is already closed");
}
using (var context = new TimeAttEntities())
{
var oldRow = (from e in context.TblEmployeeShifts.Include("TblEmployeeShiftLookup1")
where
e.EmpId == row.EmpId &&
e.TransDate == row.TransDate
select e).SingleOrDefault();
if (oldRow != null)
{
//if (oldRow.Status == 1)
//{
// var srv = new EmployeePayrollServiceClient();
// srv.DeleteEmpCalendarDeviation(row.EmpId, row.TransDate, oldRow.TblEmployeeShiftLookup1.CalendarId);
//}
context.DeleteObject(oldRow);
}
context.SaveChanges();
}
return(row.Iserial);
}
private List <FingerPrintTransaction> GetFingerPrintTransaction(int skip, int take, string sort, string filter, Dictionary <string, object> valuesObjects, out int fullCount, out List <EmployeesView> empList, out List <StoreForAllCompany> storesAllCompanies)
{
using (var context = new TimeAttEntities())
{
IQueryable <FingerPrintTransaction> query;
if (filter != null)
{
var parameterCollection = ConvertToParamters(valuesObjects);
fullCount = context.FingerPrintTransactions.Where(filter, parameterCollection.ToArray()).Count();
query = context.FingerPrintTransactions.Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = context.FingerPrintTransactions.Count();
query = context.FingerPrintTransactions.OrderBy(sort).Skip(skip).Take(take);
}
var emp = query.Select(x => x.UserId);
empList = context.EmployeesViews.Where(x => emp.Contains(x.Emplid)).ToList();
var stores = query.Select(x => x.StoreCode).ToList();
using (var model = new WorkFlowManagerDBEntities())
{
storesAllCompanies = model.StoreForAllCompanies.Where(w => stores.Contains(w.Code)).ToList();
}
return(query.ToList());
}
}
public TblEmployeeShift UpdateAndInsertTblEmployeeShift(TblEmployeeShift row, int index, int userIserial, out int outindex)
{
if (!Att.AttService.EmpCanEdit(row.TransDate, row.EmpId))
{
throw new Exception("Cannot edit a transaction because this period is already closed");
}
outindex = index;
IEnumerable <string> changedList = null;
bool save = row.Iserial == 0;
using (var db = new TimeAttEntities())
{
var oldRow = (from e in db.TblEmployeeShifts
where e.EmpId == row.EmpId &&
e.TransDate == row.TransDate
select e).SingleOrDefault();
if (oldRow != null)
{
if (row.CreationDate == DateTime.MinValue)
{
row.CreatedBy = userIserial;
row.CreationDate = DateTime.Now;
}
row.CreatedBy = userIserial;
row.Iserial = oldRow.Iserial;
changedList = GenericUpdate(oldRow, row, db);
}
else
{
row.CreatedBy = userIserial;
row.CreationDate = DateTime.Now;
db.TblEmployeeShifts.AddObject(row);
}
if (row.Status == 1)
{
// var srv = new EmployeePayrollServiceClient();
// var listOfEmployeeShifts = new List<ServiceReference1.TblEmployeeShift>
//{
// new ServiceReference1.TblEmployeeShift().InjectFrom(row) as ServiceReference1.TblEmployeeShift
//};
// try
// {
// if (listOfEmployeeShifts.Count != 0)
// {
// srv.InsertEmpCalendarDeviation(listOfEmployeeShifts);
// }
// }
// catch (Exception)
// {
// }
}
db.SaveChanges();
}
return(row);
}
private List <TblEmpWeeklyDayOff> GetTblEmpWeeklyDayOff(int skip, int take, string sort, string filter,
Dictionary <string, object> valuesObjects, out int fullCount, out List <EmployeesView> EmpList)
{
using (var context = new TimeAttEntities())
{
IQueryable <TblEmpWeeklyDayOff> query;
if (filter != null)
{
var parameterCollection = ConvertToParamters(valuesObjects);
fullCount = context.TblEmpWeeklyDayOffs.Where(filter, parameterCollection.ToArray()).Count();
query = context.TblEmpWeeklyDayOffs.Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = context.TblEmpWeeklyDayOffs.Count();
query = context.TblEmpWeeklyDayOffs.OrderBy(sort).Skip(skip).Take(take);
}
var querylist = query.Select(x => x.Emp);
EmpList =
context.EmployeesViews.Where(x => querylist.Any(l => x.Emplid == l)).ToList();
return(query.ToList());
}
}
private EmpLeavingTransaction UpdateOrInsertEmpLeavingTransaction(EmpLeavingTransaction newRow, bool save, int index, int user, out int outindex)
{
outindex = index;
using (var context = new TimeAttEntities())
{
if (save)
{
newRow.CreationBy = user;
newRow.CreationDate = DateTime.Now;
context.EmpLeavingTransactions.AddObject(newRow);
}
else
{
var oldRow = (from e in context.EmpLeavingTransactions
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, context);
}
}
context.SaveChanges();
return(newRow);
}
}
private TblTransferMsg UpdateOrInsertTblTransferMsg(TblTransferMsg newRow, bool save, int index,
out int outindex, string code)
{
outindex = index;
using (var model = new TimeAttEntities())
{
var emp = model.EmployeesViews.FirstOrDefault(x => x.Emplid == code);
newRow.Company = emp.Company;
newRow.Store = emp.Store;
using (var context = new WorkFlowManagerDBEntities())
{
if (save)
{
context.TblTransferMsgs.AddObject(newRow);
}
else
{
var oldRow = (from e in context.TblTransferMsgs
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, context);
}
}
context.SaveChanges();
return(newRow);
}
}
}
private List <TblTransferMsg> GetTblTransferMsg(int skip, int take, string sort, string filter, Dictionary <string, object> valuesObjects, string code, out int fullCount)
{
using (var model = new TimeAttEntities())
{
var emp = model.EmployeesViews.FirstOrDefault(x => x.Emplid == code);
using (var context = new WorkFlowManagerDBEntities())
{
IQueryable <TblTransferMsg> query;
if (filter != null)
{
filter = filter + " and it.Company ==(@Company0)";
valuesObjects.Add("Company0", emp.Company);
filter = filter + " and it.Store ==(@Store0)";
valuesObjects.Add("Store0", emp.Store);
var parameterCollection = ConvertToParamters(valuesObjects);
fullCount = context.TblTransferMsgs.Where(filter, parameterCollection.ToArray()).Count();
query = context.TblTransferMsgs.Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = context.TblTransferMsgs.Count(x => x.Company == emp.Company && x.Store == emp.Store);
query = context.TblTransferMsgs.Where(x => x.Company == emp.Company && x.Store == emp.Store).OrderBy(sort).Skip(skip).Take(take);
}
return(query.ToList());
}
}
}
private List <TblEmployeeShiftStore> GetTblEmployeeShiftStore(int store, string Company)
{
using (var context = new TimeAttEntities())
{
var query = context.TblEmployeeShiftStores.Include("TblEmployeeShiftLookup1").Where(w => w.TblStore == store && w.Company == Company).ToList();
return(query);
}
}
public List <EmployeesView> GetAllEmp()
{
using (var db = new TimeAttEntities())
{
var q = db.EmployeesViews.ToList();
return(q);
}
}
private List <Model.TblSalaryTerm> GetTblSalaryTerm()
{
using (var context = new TimeAttEntities())
{
var oldRow = (from e in context.TblSalaryTerms
select e).ToList();
return(oldRow);
}
}
private List <TblEmployeeBehalf> GetTblEmployeeBehalf(ObservableCollection <string> empList)
{
using (var context = new TimeAttEntities())
{
IQueryable <TblEmployeeBehalf> query = context.TblEmployeeBehalves.Where(z => empList.Contains(z.Emplid));
return(query.ToList());
}
}
public List <TblEmployeeShift> GetTblEmployeeShiftByStore(int week, ObservableCollection <string> empList, bool pending)
{
using (var db = new TimeAttEntities())
{
db.TblEmployeeShifts.MergeOption = MergeOption.NoTracking;
var u = db.TblEmployeeShifts.Where(z => z.Week == week && empList.Contains(z.EmpId) && (z.Status == 0 || pending == false)).ToList();
return(u);
}
}
private List <Model.TblVariableTermManual> GetTblVariableTermManual(List <string> empCodeList)
{
using (var context = new TimeAttEntities())
{
var query = context.TblVariableTermManuals.Where(x => empCodeList.Contains(x.Emplid));
return(query.ToList());
}
}
public List <EmployeesView> GetEmpTablebyStoreAndCompany(string company, string storeCode)
{
using (var db = new TimeAttEntities())
{
db.EmployeesViews.MergeOption = MergeOption.NoTracking;
var emp =
db.EmployeesViews.Where(x => x.Company == company && x.Store == storeCode).ToList();
return(emp);
}
}
public List <TblEmployeeShiftLookup> GetTblEmployeeShiftLookup()
{
using (var db = new TimeAttEntities())
{
db.TblEmployeeShiftLookups.MergeOption = MergeOption.NoTracking;
var u = db.TblEmployeeShiftLookups.ToList();
return(u);
}
}
public List <string> GetEmpTransportationLine(string code)
{
using (var db = new TimeAttEntities())
{
var empList = (from e in db.EmployeesViews
join eb in db.TblEmployeeBehalves
on e.Emplid equals eb.Emplid
orderby e.Emplid
where eb.AttOperatorId == code ||
eb.Emplid == code ||
eb.ManagerId == code
select e.TransportationLine).Distinct();
var EMPLOYESS = empList.ToList();
return(empList.ToList());
}
}
private int DeleteTblPeriodLock(TblPeriodLock row)
{
using (var context = new TimeAttEntities())
{
var oldRow = (from e in context.TblPeriodLocks
where e.Iserial == row.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
context.DeleteObject(oldRow);
}
context.SaveChanges();
}
return(row.Iserial);
}
private int DeleteTblVariableTermManual(Model.TblVariableTermManual row)
{
using (var context = new TimeAttEntities())
{
var oldRow = (from e in context.TblVariableTermManuals
where e.Iserial == row.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
context.DeleteObject(oldRow);
}
context.SaveChanges();
}
return(row.Iserial);
}
private int DeleteEmpLeavingTransaction(EmpLeavingTransaction row)
{
using (var context = new TimeAttEntities())
{
var oldRow = (from e in context.EmpLeavingTransactions
where e.Iserial == row.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
context.DeleteObject(oldRow);
}
context.SaveChanges();
}
return(row.Iserial);
}
public List <TblEmployeeShift> UpdateAndInsertTblEmployeeShiftList(List <TblEmployeeShift> newList)
{
using (var db = new TimeAttEntities())
{
foreach (var row in newList)
{
var oldRow = (from e in db.TblEmployeeShifts
where e.EmpId == row.EmpId &&
e.TransDate == row.TransDate
select e).SingleOrDefault();
if (oldRow != null)
{
row.Iserial = oldRow.Iserial;
GenericUpdate(oldRow, row, db);
}
else
{
db.TblEmployeeShifts.AddObject(row);
}
db.SaveChanges();
}
//var srv = new EmployeePayrollServiceClient();
//var listOfEmployeeShifts = new List<ServiceReference1.TblEmployeeShift>();
//foreach (var row in newList.Where(x => x.Status == 1))
//{
// listOfEmployeeShifts.Add(new ServiceReference1.TblEmployeeShift().InjectFrom(row) as ServiceReference1.TblEmployeeShift);
//}
//try
//{
// if (listOfEmployeeShifts.Count != 0)
// {
// srv.InsertEmpCalendarDeviation(listOfEmployeeShifts);
// }
//}
//catch (Exception)
//{
// throw;
//}
return(newList);
}
}
public List <string> GetEmpPosition(string code)
{
using (var db = new TimeAttEntities())
{
if (string.IsNullOrWhiteSpace(code))
{
return(db.EmployeesViews.Select(wde => wde.Position).Distinct().ToList());
}
var empList = (from e in db.EmployeesViews
join eb in db.TblEmployeeBehalves
on e.Emplid equals eb.Emplid
orderby e.Emplid
where eb.AttOperatorId == code ||
eb.Emplid == code ||
eb.ManagerId == code
select e.Position).Distinct();
return(empList.ToList());
}
}
private List <TblPeriodLock> GetTblPeriodLock(int skip, int take, string sort, string filter, Dictionary <string, object> valuesObjects, out int fullCount)
{
using (var context = new TimeAttEntities())
{
IQueryable <TblPeriodLock> query;
if (filter != null)
{
var parameterCollection = Operations.SharedOperation.ConvertToParamters(valuesObjects);
fullCount = context.TblPeriodLocks.Where(filter, parameterCollection.ToArray()).Count();
query = context.TblPeriodLocks.Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = context.TblPeriodLocks.Count();
query = context.TblPeriodLocks.OrderBy(sort).Skip(skip).Take(take);
}
return(query.ToList());
}
}
public List <EmployeesView> GetEmpTable(int skip, int take, string sort, string filter,
Dictionary <string, object> valuesObjects, out int fullCount)
{
using (var context = new TimeAttEntities())
{
IQueryable <EmployeesView> query;
if (filter != null)
{
var parameterCollection = SharedOperation.ConvertToParamters(valuesObjects);
fullCount = context.EmployeesViews.Where(filter, parameterCollection.ToArray()).Count();
query = context.EmployeesViews.Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = context.EmployeesViews.Count();
query = context.EmployeesViews.OrderBy(sort).Skip(skip).Take(take);
}
return(query.ToList());
}
}
private TblPeriodLock UpdateOrInsertTblPeriodLock(TblPeriodLock newRow, bool save, int index, out int outindex)
{
outindex = index;
using (var context = new TimeAttEntities())
{
if (save)
{
context.TblPeriodLocks.AddObject(newRow);
}
else
{
var oldRow = (from e in context.TblPeriodLocks
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
Operations.SharedOperation.GenericUpdate(oldRow, newRow, context);
}
}
context.SaveChanges();
return(newRow);
}
}
private FingerPrintTransaction UpdateOrInsertFingerPrintTransaction(FingerPrintTransaction newRow, bool save, int index, out int outindex)
{
outindex = index;
using (var context = new TimeAttEntities())
{
if (save)
{
context.FingerPrintTransactions.AddObject(newRow);
}
else
{
var oldRow = (from e in context.FingerPrintTransactions
where e.GlSerial == newRow.GlSerial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, context);
}
}
context.SaveChanges();
return(newRow);
}
}
public List <BehalfFiltered> GetBehalfFiltereds(int skip, int take, string sort, string filter,
Dictionary <string, object> valuesObjects, string empCode, out int fullCount)
{
using (var context = new TimeAttEntities())
{
IQueryable <BehalfFiltered> query;
if (filter != null)
{
filter = filter + " and it.ManagerId ==(@EmpCode)";
valuesObjects.Add("EmpCode0", empCode);
var parameterCollection = ConvertToParamters(valuesObjects);
fullCount = context.BehalfFiltereds.Where(filter, parameterCollection.ToArray()).Count();
query = context.BehalfFiltereds.Where(filter, parameterCollection.ToArray()).OrderBy(sort).Skip(skip).Take(take);
}
else
{
fullCount = context.BehalfFiltereds.Count(x => x.ManagerId == empCode);
query = context.BehalfFiltereds.OrderBy(sort).Where(v => v.ManagerId == empCode).Skip(skip).Take(take);
}
return(query.ToList());
}
}
private TblEmployeeBehalf UpdateOrInsertTblEmployeeBehalf(TblEmployeeBehalf newRow, bool save, int index, out int outindex)
{
outindex = index;
using (var context = new TimeAttEntities())
{
if (save)
{
context.TblEmployeeBehalves.AddObject(newRow);
}
else
{
var oldRow = (from e in context.TblEmployeeBehalves
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, context);
}
}
context.SaveChanges();
return(newRow);
}
}
private Model.TblVariableTermManual UpdateOrInsertTblVariableTermManual(Model.TblVariableTermManual newRow, bool save, int index, out int outindex)
{
var oldRow = new Model.TblVariableTermManual();
outindex = index;
using (var context = new TimeAttEntities())
{
if (save)
{
context.TblVariableTermManuals.AddObject(newRow);
}
else
{
oldRow = (from e in context.TblVariableTermManuals
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, context);
}
}
context.SaveChanges();
var srv = new EmployeePayrollServiceClient();
//newRow
var row = new ServiceReference1.TblVariableTermManual();
row.InjectFrom(newRow);
srv.InsertVariableTerm(row, row);
var selectedTerm = context.TblSalaryTerms.FirstOrDefault(w => w.Iserial == newRow.TermId).Ename;
using (PayrollEntities payrolldb = new PayrollEntities())
{
if (oldRow != null)
{
deletePayrollVr(payrolldb, oldRow, selectedTerm);
}
var emp = payrolldb.TblEmployeePayrolls.FirstOrDefault(w => w.EmpId == newRow.Emplid);
var period = getPeriodByOrganizationUnit(emp.TblOrganizationUnit ?? 0, payrolldb).Iserial;
var periodline = payrolldb.TblPeriodLines.FirstOrDefault(x => x.FromDate <= newRow.TransDate && x.ToDate >= newRow.TransDate && x.TblPeriod == period);
var exType = payrolldb.PayrollTblSalaryTerms.FirstOrDefault(w => w.Ename == selectedTerm).Iserial;
if (emp != null && exType != null)
{
var ExToInsert = new PayrollTblVariableTermManual()
{
TblEmployee = emp.Iserial,
TblSalaryTerm = exType,
Hours = newRow.Hours,
AmountCUR = 0,
AttHours = 0,
AttType = "0",
ExcuseTransRECId = null,
OldAmount = 0,
TblAttendaceFile = null,
TblCalculated = 0,
TblCurrency = null,
TblPenalty = null,
TransDate = newRow.TransDate,
FromAtt = false,
TblPeriodLine = periodline.Iserial,
};
payrolldb.PayrollTblVariableTermManuals.AddObject(ExToInsert);
}
payrolldb.SaveChanges();
}
return(newRow);
}
}
public List <EmployeesView> GetEmpByAttOperator(string position, string transportation, string code)
{
bool IsMangerOfMangers = false;
using (var wf = new WorkFlowManagerDBEntities())
{
//Added By Hashem on 05-10-2020 To Get Employees Related to the second Level Manger
var employeeJob = wf.TblAuthUsers.FirstOrDefault(x => x.Code == code);
var perm = wf.TblAuthJobPermissions.FirstOrDefault(x => x.Tbljob == employeeJob.TblJob && x.TblAuthPermission.Code == "AttendanceDirector");
if (perm != null)
{
IsMangerOfMangers = true;
}
}
using (var db = new TimeAttEntities())
{
//Added By Hashem on 05-10-2020 To Get Employees Related to the second Level Manger
if (IsMangerOfMangers)
{
List <EmployeesView> empList = new List <EmployeesView>();
var firstLevelMangers = (from e in db.EmployeesViews
join eb in db.TblEmployeeBehalves
on e.Emplid equals eb.Emplid
orderby e.Emplid
where (eb.AttOperatorId == code ||
eb.Emplid == code ||
eb.ManagerId == code) &&
(e.Position == position || position == null) &&
(e.TransportationLine == transportation || transportation == null)
select e);
foreach (var mangerCode in firstLevelMangers)
{
var mangerEmployees = (from e in db.EmployeesViews
join eb in db.TblEmployeeBehalves
on e.Emplid equals eb.Emplid
orderby e.Emplid
where (eb.AttOperatorId == mangerCode.Emplid ||
eb.Emplid == mangerCode.Emplid ||
eb.ManagerId == mangerCode.Emplid) &&
(e.Position == position || position == null) &&
(e.TransportationLine == transportation || transportation == null)
select e);
empList.AddRange(mangerEmployees);
}
var ee = empList.GroupBy(cc => cc.Emplid)
.Select(grp => grp.First())
.ToList();
return(ee);
}
else
{
var empList = (from e in db.EmployeesViews
join eb in db.TblEmployeeBehalves
on e.Emplid equals eb.Emplid
orderby e.Emplid
where (eb.AttOperatorId == code ||
eb.Emplid == code ||
eb.ManagerId == code) &&
(e.Position == position || position == null) &&
(e.TransportationLine == transportation || transportation == null)
select e);
return(empList.ToList());
}
}
}
