// End 0000179, KuangWei, 2015-03-20
protected virtual void LoadRosterTableEventList()
{
eventList = new RosterTableEventList();
//resourceList = new SchedulerResourceList();
DBFilter rosterTableFilter = new DBFilter();
rosterTableFilter.add(new Match("RosterTableDate", ">=", DateFrom.AddDays(-1).Date));
rosterTableFilter.add(new Match("RosterTableDate", "<=", DateTo.AddMilliseconds(-1).Date));
DBFilter rosterClientSiteFilter = new DBFilter();
OR orRosterClientSiteIDTerms = new OR();
orRosterClientSiteIDTerms.add(new Match("rc.RosterClientSiteID", -1));
//OR orEmpHierarchyTerms = null;
OR orEmpPosRosterTableGroupTerms = null;
foreach (object obj in RosterClientSiteIDList)
{
ERosterClientSite site = null;
if (obj is string)
{
string rosterClientSiteString = (string)obj;
int rosterClientSiteID = 0;
site = new ERosterClientSite();
if (int.TryParse(rosterClientSiteString, out rosterClientSiteID))
{
site.RosterClientSiteID = rosterClientSiteID;
}
else
{
}
}
else if (obj is ERosterClientSite)
{
site = (ERosterClientSite)obj;
}
if (site != null)
{
orRosterClientSiteIDTerms.add(new Match("rc.RosterClientSiteID", site.RosterClientSiteID));
if (ShowEmployeeRosterWithSameSite)
{
if (ERosterClientSite.db.select(dbConn, site))
{
//ERosterClient rosterClient = new ERosterClient();
//rosterClient.RosterClientID = site.RosterClientID;
//if (ERosterClient.db.select(dbConn, rosterClient))
//{
// DBFilter hElementFilter = new DBFilter();
// hElementFilter.add(new Match("HLevelID", rosterClient.RosterClientMappingSiteCodeToHLevelID));
// ArrayList hElementList = EHierarchyElement.db.select(dbConn, hElementFilter);
// foreach (EHierarchyElement hElement in hElementList)
// {
// if (hElement.HElementCode.Trim().Equals(site.RosterClientSiteCode.ToString().Trim()))
// {
// DBFilter sub = new DBFilter();
// sub.add(new MatchField("epi.EmpPosID", "eeh.EmpPosID"));
// sub.add(new Match("eeh.HLevelID", hElement.HLevelID));
// sub.add(new Match("eeh.HElementID", hElement.HElementID));
// if (orEmpHierarchyTerms == null)
// orEmpHierarchyTerms = new OR();
// orEmpHierarchyTerms.add(new Exists(EEmpHierarchy.db.dbclass.tableName + " eeh", sub));
// }
// }
//}
DBFilter rosterTableGroupFilter = new DBFilter();
rosterTableGroupFilter.add(new MatchField("ertg.RosterTableGroupID", "rtg.RosterTableGroupID"));
rosterTableGroupFilter.add(new Match("RosterClientSiteID", site.RosterClientSiteID));
if (orEmpPosRosterTableGroupTerms == null)
{
orEmpPosRosterTableGroupTerms = new OR();
}
orEmpPosRosterTableGroupTerms.add(new Exists(ERosterTableGroup.db.dbclass.tableName + " rtg", rosterTableGroupFilter));
}
}
}
}
OR orRosterTableTerms = new OR();
if (ShowRosterCodeWithoutSiteMapping)
{
orRosterClientSiteIDTerms.add(new Match("rc.RosterClientSiteID", 0));
DBFilter notExistsFilter = new DBFilter();
notExistsFilter.add(new MatchField("RosterTable.RosterCodeID", "nic.RosterCodeID"));
orRosterTableTerms.add(new Exists(ERosterCode.db.dbclass.tableName + " nic", notExistsFilter, true));
}
rosterClientSiteFilter.add(orRosterClientSiteIDTerms);
orRosterTableTerms.add(new IN("RosterCodeID", "Select rc.RosterCodeID From " + ERosterCode.db.dbclass.tableName + " rc", rosterClientSiteFilter));
//if (orEmpHierarchyTerms != null)
//{
// DBFilter empRosterTableGroupFilter = new DBFilter();
// empRosterTableGroupFilter.add(orEmpHierarchyTerms);
// empRosterTableGroupFilter.add(new Match("ertg.empRosterTableGroupEffFr", "<=", DateTo));
// OR orEmpPosEffToTerms = new OR();
// orEmpPosEffToTerms.add(new Match("ertg.empRosterTableGroupEffTo", ">=", DateFrom));
// orEmpPosEffToTerms.add(new NullTerm("ertg.empRosterTableGroupEffTo"));
// empRosterTableGroupFilter.add(orEmpPosEffToTerms);
// orRosterTableTerms.add(new IN("EmpID", "Select distinct EmpID From " + EEmpPositionInfo.db.dbclass.tableName + " epi ", empRosterTableGroupFilter));
//}
if (orEmpPosRosterTableGroupTerms != null)
{
DBFilter empRosterTableGroupFilter = new DBFilter();
empRosterTableGroupFilter.add(orEmpPosRosterTableGroupTerms);
empRosterTableGroupFilter.add(new Match("ertg.empRosterTableGroupEffFr", "<=", DateTo));
OR orEmpPosEffToTerms = new OR();
orEmpPosEffToTerms.add(new Match("ertg.empRosterTableGroupEffTo", ">=", DateFrom));
orEmpPosEffToTerms.add(new NullTerm("ertg.empRosterTableGroupEffTo"));
empRosterTableGroupFilter.add(orEmpPosEffToTerms);
orRosterTableTerms.add(new IN("EmpID", "Select distinct EmpID From " + EEmpRosterTableGroup.db.dbclass.tableName + " ertg ", empRosterTableGroupFilter));
}
rosterTableFilter.add(orRosterTableTerms);
if (UserID > 0)
{
rosterTableFilter.add(AppUtils.AddRankDBTerm(UserID, "EmpID", true));
}
ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter);
eventList.AddRange(GenerateEventList(rosterTableList));
DBFilter leaveAppFilter = new DBFilter();
leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", DateFrom.AddDays(-1)));
leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", DateTo));
if (!ShowRosterCodeWithoutSiteMapping && orEmpPosRosterTableGroupTerms != null)
{
DBFilter empRosterTableGroupFilter = new DBFilter();
empRosterTableGroupFilter.add(orEmpPosRosterTableGroupTerms);
empRosterTableGroupFilter.add(new Match("ertg.empRosterTableGroupEffFr", "<=", DateTo));
OR orEmpPosEffToTerms = new OR();
orEmpPosEffToTerms.add(new Match("ertg.empRosterTableGroupEffTo", ">=", DateFrom));
orEmpPosEffToTerms.add(new NullTerm("ertg.empRosterTableGroupEffTo"));
empRosterTableGroupFilter.add(orEmpPosEffToTerms);
leaveAppFilter.add(new IN("EmpID", "Select distinct EmpID From " + EEmpRosterTableGroup.db.dbclass.tableName + " ertg ", empRosterTableGroupFilter));
}
eventList.AddRange(GenerateEventList(ELeaveApplication.db.select(dbConn, leaveAppFilter)));
// Put the holiday list at the end of the collection so that the item can be display at the top of the day.
eventList.AddRange(GenerateEventList(HolidayDBObjectList(DateFrom, DateTo)));
}
public RosterTableEventList Select(DateTime DateFrom, DateTime DateTo, int ESSUserID, bool DisplayOvernightToNextDay, int RosterTableGroupID, bool LeaveChecking, bool RosterChecking)
{
eventList = new RosterTableEventList();
for (DateTime rosterTableDate = DateFrom.AddDays(-1); rosterTableDate <= DateTo; rosterTableDate = rosterTableDate.AddDays(1))
{
DBFilter rosterTableFilter = new DBFilter();
rosterTableFilter.add(new Match("RosterTableDate", "=", rosterTableDate));
DBFilter leaveAppFilter = new DBFilter();
leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", rosterTableDate));
leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", rosterTableDate));
//EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, rosterTableDate, ESSUserID);
OR orRosterTableTerms = new OR();
orRosterTableTerms.add(new Match("EmpID", ESSUserID));
OR orLeaveAppTerms = new OR();
orLeaveAppTerms.add(new Match("EmpID", ESSUserID));
DBFilter empRosterTableGroupListFilter = new DBFilter();
{
empRosterTableGroupListFilter.add(new Match("EmpID", ESSUserID));
empRosterTableGroupListFilter.add(new Match("empRosterTableGroupEffFr", "<=", DateTo));
OR orEmpPosEffToTerms = new OR();
orEmpPosEffToTerms.add(new Match("empRosterTableGroupEffTo", ">=", DateFrom));
orEmpPosEffToTerms.add(new NullTerm("empRosterTableGroupEffTo"));
empRosterTableGroupListFilter.add(orEmpPosEffToTerms);
}
System.Collections.Generic.List <string> RosterTableGroupIDList = new System.Collections.Generic.List <string>();
if (RosterTableGroupID == 0)
{
ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, empRosterTableGroupListFilter);
foreach (EEmpRosterTableGroup empRosterTableGroup in empRosterTableGroupList)
{
string idString = empRosterTableGroup.RosterTableGroupID.ToString();
if (!RosterTableGroupIDList.Contains(idString))
{
RosterTableGroupIDList.Add(idString);
}
}
}
else
{
RosterTableGroupIDList.Add(RosterTableGroupID.ToString());
}
if (RosterTableGroupIDList.Count > 0)
{
string RosterTableGroupListInString = string.Join(",", RosterTableGroupIDList.ToArray());
//ERosterTableGroup rosterTableGroup = new ERosterTableGroup();
//rosterTableGroup.RosterTableGroupID = empPos.RosterTableGroupID;
//if (ERosterTableGroup.db.select(dbConn, rosterTableGroup))
//{
DBFilter rosterClientSiteFilter = new DBFilter();
{
DBFilter rosterTableGroupFilter = new DBFilter();
rosterTableGroupFilter.add(new IN("rtg.RosterTableGroupID", RosterTableGroupListInString, null));
OR orRosterClientSiteIDTerms = new OR();
orRosterClientSiteIDTerms.add(new IN("rc.RosterClientSiteID", "SELECT DISTINCT RosterClientSiteID FROM " + ERosterTableGroup.db.dbclass.tableName + " rtg", rosterTableGroupFilter));
rosterClientSiteFilter.add(orRosterClientSiteIDTerms);
}
orRosterTableTerms.add(new IN("RosterCodeID", "Select rc.RosterCodeID From " + ERosterCode.db.dbclass.tableName + " rc", rosterClientSiteFilter));
DBFilter empRosterTableGroupFilter = new DBFilter();
{
DBFilter rosterTableGroupFilter = new DBFilter();
rosterTableGroupFilter.add(new IN("ertg.RosterTableGroupID", RosterTableGroupListInString, null));
empRosterTableGroupFilter.add(new Exists(ERosterTableGroup.db.dbclass.tableName + " rtg", rosterTableGroupFilter));
empRosterTableGroupFilter.add(new Match("ertg.empRosterTableGroupEffFr", "<=", rosterTableDate));
OR orEmpPosEffToTerms = new OR();
orEmpPosEffToTerms.add(new Match("ertg.empRosterTableGroupEffTo", ">=", rosterTableDate));
orEmpPosEffToTerms.add(new NullTerm("ertg.empRosterTableGroupEffTo"));
empRosterTableGroupFilter.add(orEmpPosEffToTerms);
}
orRosterTableTerms.add(new IN("EmpID", "Select distinct EmpID From " + EEmpRosterTableGroup.db.dbclass.tableName + " ertg ", empRosterTableGroupFilter));
orLeaveAppTerms.add(new IN("EmpID", "Select distinct EmpID From " + EEmpRosterTableGroup.db.dbclass.tableName + " ertg ", empRosterTableGroupFilter));
//}
}
if (LeaveChecking && RosterChecking)
{
rosterTableFilter.add(orRosterTableTerms);
ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter);
eventList.AddRange(GenerateEventList(rosterTableList));
leaveAppFilter.add(orLeaveAppTerms);
ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter);
eventList.AddRange(GenerateEventList(leaveAppList));
}
else if (RosterChecking)
{
rosterTableFilter.add(orRosterTableTerms);
ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter);
eventList.AddRange(GenerateEventList(rosterTableList));
}
else if (LeaveChecking)
{
leaveAppFilter.add(orLeaveAppTerms);
ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter);
eventList.AddRange(GenerateEventList(leaveAppList));
}
}
// Put the holiday list at the end of the collection so that the item can be display at the top of the day.
eventList.AddRange(GenerateEventList(HolidayDBObjectList(DateFrom, DateTo)));
return(eventList);
}