internal static List <Posts> GetPosts(int departmentId)
{
using (var db = new HrContext())
{
return(new List <Posts>(db.Posts.Where(x => x.DepartmentId == departmentId).ToList()));
}
}
public static bool IsPostsEmpty()
{
using (var db = new HrContext())
{
return(db.Posts.Count() == 0);
}
}
public static bool IsDepartmentsEmpty()
{
using (var db = new HrContext())
{
return(db.Departments.Count() == 0);
}
}
public AddBranchViewModel ReadBranch(int id, string culture)
{
var branch = (from l in context.Branches
where l.Id == id
select new AddBranchViewModel
{
Id = l.Id,
Code = l.Code,
Name = l.Name,
TimeZone = l.TimeZone,
LName = HrContext.TrlsName(l.Name, culture),
Longitude = l.Longitude,
Latitude = l.Latitude,
Address1 = l.Address1,
CityId = l.CityId,
CountryId = l.CountryId,
DistrictId = l.DistrictId,
Telephone = l.Telephone,
CreatedTime = l.CreatedTime,
ModifiedTime = l.ModifiedTime,
CreatedUser = l.CreatedUser,
ModifiedUser = l.ModifiedUser
}).FirstOrDefault();
return(branch);
}
public IQueryable <EmpChkListViewModel> GetEmpCheckLists(string culture, int companyId)
{
var EmpList = from el in context.EmpChkLists
where el.CompanyId == companyId
join p in context.People on el.EmpId equals p.Id into g
from p in g.DefaultIfEmpty()
select new EmpChkListViewModel
{
Id = el.Id,
EmpId = el.EmpId,
ListEndDate = el.ListEndDate,
ListStartDate = el.ListStartDate,
PicUrl = HrContext.GetDoc("EmployeePic", p.Id),
Gender = p.Gender,
ListType = el.ListType,
Name = el.Name,
Employee = p != null?HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture) : "",
Status = el.Status,
CreatedUser = el.CreatedUser,
ManagerId = el.ManagerId,
Count = context.EmpTasks.Where(a => a.EmpListId == el.Id).Count(),
PrograssBar = context.EmpTasks.Where(a => a.EmpListId == el.Id && a.Status == 2).Count()
};
return(EmpList);
}
public CompanyFormViewModel ReadCompany(int id, string culture)
{
var query = (from c in context.Companies
where c.Id == id
select new CompanyFormViewModel
{
Id = c.Id,
CommFileNo = c.CommFileNo,
Consolidation = c.Consolidation,
CountryId = c.CountryId,
Email = c.Email,
InsuranceNo = c.InsuranceNo,
Language = c.Language,
Name = c.Name,
LocalName = HrContext.TrlsName(c.Name, culture),
Memo = c.Memo,
Purpose = c.Purpose,
TaxCardNo = c.TaxCardNo,
WebSite = c.WebSite,
TaxAuthority = c.TaxAuthority,
SearchName = c.SearchName,
CreatedTime = c.CreatedTime,
CreatedUser = c.CreatedUser,
ModifiedTime = c.ModifiedTime,
ModifiedUser = c.ModifiedUser,
Attachments = HrContext.GetAttachments("Company", c.Id),
LegalForm = c.LegalForm,
Office = c.Office,
Region = c.Region,
Responsible = c.Responsible
}).FirstOrDefault();
return(query);
}
public CompanyStructureViewModel GetStructure(int?Id, string culture)
{
var Compastruc = (from c in context.CompanyStructures
where c.Id == Id
select new CompanyStructureViewModel
{
Id = c.Id,
Code = c.Code,
ModifiedTime = c.ModifiedTime,
ModifiedUser = c.ModifiedUser,
Name = c.Name,
ParentId = c.ParentId,
PlannedCount = c.PlannedCount,
ParentName = c.Parent.Name,
NodeType = c.NodeType,
StartDate = c.StartDate,
EndDate = c.EndDate,
Icon = c.Icon,
IsVisible = c.IsVisible,
ColorName = c.ColorName != null? c.ColorName:"",
CreatedTime = c.CreatedTime,
CreatedUser = c.CreatedUser,
LocalName = HrContext.TrlsName(c.Name, culture),
}).FirstOrDefault();
return(Compastruc);
}
public RecievedCustodyForm ReadEditRecievedCustody(int id, string culture)
{
var res = (from e in context.EmpCustodies
where e.Id == id && e.delvryDate == null
join c in context.Custody on e.CustodyId equals c.Id
select new RecievedCustodyForm
{
Id = e.Id,
CustodyId = c.Id,
Name = c.Name,
CompanyId = c.CompanyId,
CustodyCatId = c.CustodyCatId,
PurchaseDate = c.PurchaseDate,
RecvStatus = c.Status,
PurchaseAmount = c.PurchaseAmount,
ItemCode = c.ItemCode,
Qty = 1,
SerialNo = c.SerialNo,
Attachments = HrContext.GetAttachments("RecieveCustody", id),
EmpId = e.EmpId,
RecvDate = e.RecvDate,
Notes = e.Notes,
BranchId = e.BranchId
}).FirstOrDefault();
return(res);
}
public IQueryable <CustodyViewModel> ReadEmpCustody(int CompanyId, string culture)
{
var result = (from c in context.Custody
where (c.CompanyId == CompanyId && c.InUse == true)
join Emp in context.EmpCustodies on c.Id equals Emp.CustodyId
where Emp.delvryDate == null
join p in context.People on Emp.EmpId equals p.Id
join l in context.Sites on Emp.BranchId equals l.Id into j
from l in j.DefaultIfEmpty()
select new CustodyViewModel
{
Id = c.Id,
EmpCustodyId = Emp.Id,
Code = c.Code,
Name = c.Name,
SerialNo = c.SerialNo,
CompanyId = c.CompanyId,
CustodyCatId = HrContext.TrlsName(c.CustodyCat.Name, culture),
PurchaseAmount = c.PurchaseAmount,
Status = c.Status,
InUse = c.InUse,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
Branch = HrContext.TrlsName(l.Name, culture),
EmpId = Emp.EmpId,
Description = c.Description,
Qty = Emp.Qty,
RecvDate = Emp.RecvDate,
PurchaseDate = c.PurchaseDate,
AttUrl = HrContext.GetDoc("RecieveCustody", Emp.Id),
Filename = context.CompanyDocsView.Where(a => a.Source == "RecieveCustody" && a.SourceId == Emp.Id).Select(c => c.name).FirstOrDefault()
});
return(result);
}
public IQueryable <CustodyViewModel> ReadCustody(byte Range, DateTime?Start, DateTime?End, string culture, int CompanyId)
{
//10- All, 0-Custom
if (Range != 10 && Range != 0)
{
RequestRangeFilter(Range, CompanyId, out Start, out End);
}
var custody = from c in context.Custody
where (c.CompanyId == CompanyId && !c.CustodyCat.Disposal && !c.InUse)
select new CustodyViewModel
{
Id = c.Id,
Code = c.Code,
Name = c.Name,
SerialNo = c.SerialNo,
CompanyId = c.CompanyId,
CustodyCatId = HrContext.TrlsName(c.CustodyCat.Name, culture),
PurchaseDate = c.PurchaseDate,
PurchaseAmount = c.PurchaseAmount,
Status = c.Status,
InUse = c.InUse,
Freeze = c.Freeze,
Description = c.Description,
Qty = c.Qty
};
if (Range != 10)
{
custody = custody.Where(c => Start <= c.PurchaseDate && End >= c.PurchaseDate);
}
return(custody);
}
public CustodyFormViewModel ReadCustObject(int id, string culture)
{
var res = (from c in context.Custody
where c.Id == id
select new CustodyFormViewModel
{
Id = c.Id,
Name = c.Name,
Code = c.Code,
CustodyCatId = c.CustodyCatId,
Description = c.Description,
SerialNo = c.SerialNo,
Attachments = HrContext.GetAttachments("Custody", c.Id),
PurchaseDate = c.PurchaseDate,
JobId = c.JobId,
BranchId = c.BranchId,
Status = c.Status,
PurchaseAmount = c.PurchaseAmount,
ItemCode = c.ItemCode,
Qty = c.Qty,
Curr = c.Curr,
Freeze = c.Freeze,
CurrencyRate = c.CurrencyRate == 0 ? 1 : c.CurrencyRate,
CreatedTime = c.CreatedTime,
CreatedUser = c.CreatedUser,
ModifiedTime = c.ModifiedTime,
ModifiedUser = c.ModifiedUser
}).FirstOrDefault();
return(res);
}
public ComplaintRequestViewModel GetRequest(int requestId, string culture)
{
ComplaintRequestViewModel Request = (from req in context.ComplainRequests
where req.Id == requestId
join p in context.People on req.EmpId equals p.Id
select new ComplaintRequestViewModel()
{
Id = req.Id,
RequestDate = req.RequestDate,
EmpId = req.EmpId,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
ApprovalStatus = req.ApprovalStatus,
RejectReason = req.RejectReason,
RejectDesc = req.RejectDesc,
CancelReason = req.CancelReason,
CancelDesc = req.CancelDesc,
Attachments = HrContext.GetAttachments("ComplaintRequest", req.Id),
CreatedTime = req.CreatedTime,
CreatedUser = req.CreatedUser,
ModifiedTime = req.ModifiedTime,
ModifiedUser = req.ModifiedUser,
ComplainType = req.ComplainType,
Description = req.Description,
Against = req.Against
}).FirstOrDefault();
return(Request);
}
public DocTypeFormViewModel ReadDocType(int Id, string culture)
{
var query = (from d in context.DocTypes
where d.Id == Id
select new DocTypeFormViewModel
{
Id = d.Id,
EndDate = d.EndDate,
IsLocal = d.IsLocal,
Name = d.Name,
RequiredOpt = d.RequiredOpt,
StartDate = d.StartDate,
DocumenType = d.DocumenType,
HasExpiryDate = d.HasExpiryDate,
AccessLevel = d.AccessLevel,
LocalName = HrContext.TrlsName(d.Name, culture),
IJobs = d.Jobs.Select(a => a.Id),
INationalities = d.Nationalities.Select(a => a.Id),
ModifiedTime = d.ModifiedTime,
ModifiedUser = d.ModifiedUser,
CreatedUser = d.CreatedUser,
CreatedTime = d.CreatedTime,
Gender = d.Gender,
NotifyDays = d.NotifyDays
}).FirstOrDefault();
return(query);
}
public CheckListFormViewModel ReadCheckList(int id, string culture)
{
var Job = (from J in context.CheckLists
where J.Id == id
select new CheckListFormViewModel
{
Id = J.Id,
Name = J.Name,
IsLocal = J.IsLocal,
LocalName = HrContext.TrlsName(J.Name, culture),
StartDate = J.StartDate,
EndDate = J.EndDate,
Duration = J.Duration,
CompanyId = J.CompanyId,
Default = J.Default,
Description = J.Description,
ListType = J.ListType,
ModifiedTime = J.ModifiedTime,
ModifiedUser = J.ModifiedUser,
CreatedTime = J.CreatedTime,
CreatedUser = J.CreatedUser,
}).FirstOrDefault();
return(Job);
}
public static List <Departments> GetDepartments()
{
using (var db = new HrContext())
{
return(new List <Departments>(db.Departments.ToList()));
}
}
//ReadConsumeCustody
public IQueryable <CustodyViewModel> ReadConsumeCustody(byte Range, DateTime?Start, DateTime?End, string culture, int CompanyId)
{
//10- All, 0-Custom
if (Range != 10 && Range != 0)
{
RequestRangeFilter(Range, CompanyId, out Start, out End);
}
var query = from c in context.Custody
where (c.CompanyId == CompanyId && c.CustodyCat.Disposal)
join ec in context.EmpCustodies on c.Id equals ec.CustodyId into g
from ec in g.DefaultIfEmpty()
group ec by new { Id = c.Id, Name = c.Name, PurchaseDate = c.PurchaseDate, PurchaseAmount = c.PurchaseAmount, Qty = c.Qty, Description = c.Description, CustodyCat = c.CustodyCat.Name, CurrRate = c.CurrencyRate } into g
select new CustodyViewModel
{
Id = g.Key.Id,
Name = g.Key.Name,
PurchaseDate = g.Key.PurchaseDate,
PurchaseAmount = g.Key.PurchaseAmount,
Qty = g.Key.Qty,
CurrencyRate = g.Key.CurrRate,
CustodyCatId = HrContext.TrlsName(g.Key.CustodyCat, culture),
RestQty = g.Key.Qty - (g.Sum(x => x.Qty) == null ? 0 : g.Sum(x => x.Qty)),
Description = g.Key.Description
};
if (Range != 10)
{
query = query.Where(c => Start <= c.PurchaseDate && End >= c.PurchaseDate);
}
return(query.Where(a => a.RestQty > 0));
}
public List <StreamID_DocTypeFormViewModel> GetDocsViews_Uploaded(string Source, int SourceId, string CodeName /*,int SysCodeId*/, string Lang)
{
try
{
var BasicQuery = GetDocsViews_Queryable(Source, SourceId);
var DocsViews = (from c in BasicQuery
join d in context.DocTypes on c.TypeId equals d.Id
//join l in context.LookUpUserCodes on d.DocumenType equals l.SysCodeId//new { p1 = d.Name, p2 = d.DocumenType } equals new { p1 = l.CodeName, p2 = l.CodeId }
// where c.Source == Source /*&& l.CodeName == CodeName *///&& l.SysCodeId == SysCodeId
select new StreamID_DocTypeFormViewModel
{
Stream_Id = c.stream_id,
DocTypeFormViewModel = new DocTypeFormViewModel
{
Id = d.Id,
Name = HrContext.TrlsName(d.Name, Lang),
RequiredOpt = d.RequiredOpt,
HasExpiryDate = d.HasExpiryDate
}
}).ToList();
return(DocsViews);
}
catch
{
return(new List <StreamID_DocTypeFormViewModel>());
}
}
//read Borrow Document with Employee
public IEnumerable <EmpDocBorrowViewModel> ReadDocBorrow(byte Range, DateTime?Start, DateTime?End, int CompanyId, string culture)
{
//10- All, 0-Custom
if (Range != 10 && Range != 0)
{
RequestRangeFilter(Range, CompanyId, out Start, out End);
}
var query = (from d in context.EmpDocBorrow
where (d.CompanyId == CompanyId && d.delvryDate == null)
join p in context.People on d.EmpId equals p.Id
select new EmpDocBorrowViewModel
{
Id = d.Id,
CompanyId = d.CompanyId,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
EmpId = d.EmpId,
Purpose = d.Purpose,
Notes = d.Notes,
RecvDate = d.RecvDate,
Site = d.Site,
ExpdelvryDate = d.ExpdelvryDate,
AttUrl = HrContext.GetDoc("BorrowPapers", d.Id),
Document = context.DocBorrowList.Where(a => a.DocBorrowId == d.Id).Select(s => s.Doc.Name).ToList()
}).ToList();
if (Range != 10)
{
query = query.Where(c => Start <= c.RecvDate && End >= c.RecvDate).ToList();
}
return(query);
}
public string DeleteFlexColumns(FlexColumn model, string culture)
{
string msg = "OK";
if (model == null)
{
return(msg);
}
// Check before delete
var columnNames = model.ColumnName;
var page = model.PageId;
var related = (from fc in context.FlexColumns
where fc.PageId == page && columnNames.Contains(fc.ColumnName)
join fd in context.FlexData on new { fc.PageId, fc.ColumnName } equals new { fd.PageId, fd.ColumnName }
join pd in context.PageDiv on fc.PageId equals pd.Id
join m in context.Menus on pd.MenuId equals m.Id
select new { name = fc.ColumnName, page = HrContext.TrlsName(m.Name + m.Sequence, culture) }).FirstOrDefault();
if (related != null)
{
msg = MsgUtils.Instance.Trls(culture, "DeleteRelatedRecord").Replace("{0}", related.page) + ": " + related.name;
return(msg);
}
var Id = model.Id;
var titles = context.ColumnTitles.ToList();
var DeleteObj = model;
context.FlexColumns.Attach(DeleteObj);
context.Entry(DeleteObj).State = EntityState.Deleted;
var DeleteTitles = titles.Where(a => a.ColumnName == model.ColumnName).ToList();
RemoveRange(DeleteTitles);
return(msg);
}
public IQueryable <CustodyViewModel> GetEmpCustody(int EmpId, int CompanyId, string culture)
{
var result = from c in context.Custody
where (c.CompanyId == CompanyId)
join Emp in context.EmpCustodies on c.Id equals Emp.CustodyId
where Emp.delvryDate == null && Emp.EmpId == EmpId
join p in context.People on Emp.EmpId equals p.Id
join l in context.Sites on Emp.BranchId equals l.Id into j
from l in j.DefaultIfEmpty()
select new CustodyViewModel
{
Id = c.Id,
Code = c.Code,
Name = c.Name,
SerialNo = c.SerialNo,
CustodyCatId = HrContext.TrlsName(c.CustodyCat.Name, culture),
Disposal = c.CustodyCat.Disposal,
Status = c.Status,
InUse = c.InUse,
Description = c.Description,
PurchaseDate = c.PurchaseDate,
PurchaseAmount = c.PurchaseAmount,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
Branch = HrContext.TrlsName(l.Name, culture),
EmpId = Emp.EmpId,
Qty = Emp.Qty
};
return(result);
}
public IQueryable <MeetingAgendaViewModel> GetAgenda(int Id, string culture)
{
var query = (from m in context.MeetScheduals
where m.MeetingId == Id
join p in context.People on m.EmpId equals p.Id into j
from x in j.DefaultIfEmpty()
select new MeetingAgendaViewModel
{
Id = m.Id,
StartTime = m.StartTime.ToString(),
EndTime = m.EndTime.ToString(),
Description = m.Description,
EmpId = m.EmpId,
EmpName = HrContext.TrlsName(x.Title + " " + x.FirstName + " " + x.Familyname, culture),
}).ToList().Select(l => new MeetingAgendaViewModel
{
Id = l.Id,
EmpId = l.EmpId,
StartTime = Convert.ToDateTime(l.StartTime).ToString("hh:mm tt"),
EndTime = Convert.ToDateTime(l.EndTime).ToString("hh:mm tt"),
Description = l.Description,
Time = (Convert.ToDateTime(l.EndTime) - Convert.ToDateTime(l.StartTime)).ToString(@"hh\:mm")
});
return(query.AsQueryable());
}
public IEnumerable <EmployeeTrainPathViewModel> TrainPathProgress(int id, string culture)
{
var q = context.TrainPath.Where(a => a.Id == id).Select(a => new { c = a.Courses.Select(b => b.Id), id = a.Id }).FirstOrDefault();
if (q != null)
{
var Emp = context.PeopleTraining.
Where(a => q.c.Contains(a.CourseId) && a.Status == 2).GroupBy(a => new { person = a.EmpId, Emp = a.Person }).
Select(a => new EmployeeTrainPathViewModel
{
EmpId = a.Key.person,
Id = q.id,
Percent = a.Count() * 100 / q.c.Count(),
Employee = HrContext.TrlsName(a.Key.Emp.Title + " " + a.Key.Emp.FirstName + " " + a.Key.Emp.Familyname, culture),
CourseName = a.Select(j => j.Course.Name).ToList(),
CourseIds = a.Select(j => j.Course.Id).ToList(),
}
).ToList();
return(Emp);
}
else
{
return(null);
}
}
// Get the employee with Dept manger role only // Done By fatma
public IQueryable <FormList> GetActiveMangers(int companyId, string culture)
{
var today = DateTime.Today.Date;
//Get Departments managers
var query1 = (from a in context.Assignments
where a.CompanyId == companyId && a.AssignDate <= today && a.EndDate >= today && a.IsDepManager == true
select new FormList
{
id = a.EmpId,
name = HrContext.TrlsName(a.Employee.Title + " " + a.Employee.FirstName + " " + a.Employee.Familyname, culture),
PicUrl = HrContext.GetDoc("EmployeePic", a.EmpId),
Gender = a.Employee.Gender,
Icon = HrContext.GetEmpStatus(a.Employee.Id)
});
var query2 = (from a in context.Assignments
where a.CompanyId == companyId && a.AssignDate <= today && a.EndDate >= today
join p in context.People on a.ManagerId equals p.Id
select new FormList
{
id = p.Id,
name = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
PicUrl = HrContext.GetDoc("EmployeePic", p.Id),
Gender = p.Gender,
Icon = HrContext.GetEmpStatus(p.Id)
}).Distinct();
var query3 = query1.Union(query2);
return(query3);
}
public EmpTasksViewModel GetEmployeeTask(int Id, string culture)
{
EmpTasksViewModel task = context.EmpTasks.Where(t => t.Id == Id)
.Select(t => new EmpTasksViewModel
{
Id = t.Id,
EmpList = t.EmpChklist.Name,
Manager = HrContext.TrlsName(t.Manager.Title + " " + t.Manager.FirstName + " " + t.Manager.Familyname, culture),
Description = t.Description,
Priority = t.Priority,
Status = t.Status,
TaskCategory = HrContext.GetLookUpCode("EmpTaskCat", t.TaskCat.Value, culture),
TaskNo = t.TaskNo,
AssignedTime = t.AssignedTime,
Attachments = HrContext.GetAttachments("EmpTasksForm", t.Id),
StartTime = t.StartTime,
EndTime = t.EndTime,
Duration = t.Duration,
EmpListId = t.EmpListId,
ExpectDur = t.ExpectDur,
Unit = t.Unit,
Required = t.Required,
Employee = HrContext.TrlsName(t.EmpChklist.Employee.Title + " " + t.EmpChklist.Employee.FirstName + " " + t.EmpChklist.Employee.Familyname, culture),
}).FirstOrDefault();
return(task);
}
