private IList <Member> FindByEmployer(ISession session, int EmployerID)
{
String query = "";
query = @"
select * from Account
inner join dbo.Employer on dbo.Employer.EmployerID = dbo.Account.EmployerID
where dbo.Account.AccountDiscriminator = 2 and dbo.Employer.EmployerID =" + EmployerID + @"
AND (dbo.Account.EffectiveFrom <= GETDATE())
AND (dbo.Account.EffectiveTo >= GETDATE())" ;
string terminationCondition = " and dbo.Account.TerminationInfoID is not null ";
if (TerminationStatus.Equals(EmployeeTerminatedStatus.Terminated))
{
query += terminationCondition;
}
IList <Member> results =
session.CreateSQLQuery(query).AddEntity("employee",
typeof(Member)).List <Member>();
return(results);
}
private IList <Member> FindLikeByName(int findType, string keyword)
{
String divisionCode = null;
divisionCode = ddDepartment.SelectedItem != null?ddDepartment.SelectedItem.Value.ToString() : null;
Employer employer = getPageEmployer();
String query = "";
if (findType == 1)
{
if (divisionCode == null || divisionCode.Equals("-All Division-"))
{
query = @"select * from Account where AccountID in(
SELECT dbo.Account.AccountID
FROM dbo.Person INNER JOIN
dbo.Account ON dbo.Person.PersonID = dbo.Account.EmployeeID INNER JOIN
dbo.PersonName ON dbo.Person.CurrentNameID = dbo.PersonName.PersonNameID INNER JOIN
dbo.MLSValue ON dbo.PersonName.FirstNameID = dbo.MLSValue.MLSID
where dbo.MLSValue.Value like N'" + keyword + @"%' and Account.AccountDiscriminator=2
and EmployerID=" + employer.EmployerID + @"
";
}
else
{
query = @"select * from Account where AccountID in(
SELECT dbo.Account.AccountID
FROM dbo.Person INNER JOIN
dbo.Account ON dbo.Person.PersonID = dbo.Account.EmployeeID INNER JOIN
dbo.PersonName ON dbo.Person.CurrentNameID = dbo.PersonName.PersonNameID INNER JOIN
dbo.MLSValue ON dbo.PersonName.FirstNameID = dbo.MLSValue.MLSID
inner join EmployeeInfo on Account.AccountID=EmployeeInfo.AccountID
where dbo.MLSValue.Value like N'" + keyword + @"%' and Account.AccountDiscriminator=2
and EmployerID=" + employer.EmployerID + @"
and EmployeeInfo.DivisionCode='" + divisionCode + @"')
";
}
}
else if (findType == 2)
{
if (divisionCode == null || divisionCode.Equals("-All Division-"))
{
query = @"select * from Account where AccountID in(
SELECT dbo.Account.AccountID
FROM dbo.Person INNER JOIN
dbo.Account ON dbo.Person.PersonID = dbo.Account.EmployeeID INNER JOIN
dbo.PersonName ON dbo.Person.CurrentNameID = dbo.PersonName.PersonNameID INNER JOIN
dbo.MLSValue ON dbo.PersonName.LastNameID = dbo.MLSValue.MLSID
where dbo.MLSValue.Value like N'" + keyword + @"%' and Account.AccountDiscriminator=2
and EmployerID=" + employer.EmployerID + @"
)
";
}
else
{
query = @"select * from Account where AccountID in(
SELECT dbo.Account.AccountID
FROM dbo.Person INNER JOIN
dbo.Account ON dbo.Person.PersonID = dbo.Account.EmployeeID INNER JOIN
dbo.PersonName ON dbo.Person.CurrentNameID = dbo.PersonName.PersonNameID INNER JOIN
dbo.MLSValue ON dbo.PersonName.LastNameID = dbo.MLSValue.MLSID
inner join EmployeeInfo on Account.AccountID=EmployeeInfo.AccountID
where dbo.MLSValue.Value like N'" + keyword + @"%' and Account.AccountDiscriminator=2
and EmployerID=" + employer.EmployerID + @"
and EmployeeInfo.DivisionCode='" + divisionCode + @"'
)
";
}
}
else
{
if (divisionCode == null || divisionCode.Equals("-All Division-"))
{
query = @" select * from Account inner join EmployeeInfo on EmployeeInfo.EmployeeInfoID = Account.CurrentEmployeeInfoID
where Account.AccountDiscriminator = 2 and EmployeeInfo.EmployeeNo like N'" + keyword + @"%'
and EmployerID=" + employer.EmployerID + @"
";
}
else
{
query = @" select Account.*
from Account
inner join EmployeeInfo on Account.AccountID=EmployeeInfo.AccountID
and EmployeeInfo.EmployeeInfoID = Account.CurrentEmployeeInfoID
where Account.AccountDiscriminator = 2 and EmployeeInfo.EmployeeNo like N'" + keyword + @"%'
and EmployerID=" + employer.EmployerID + @"
and EmployeeInfo.DivisionCode='" + divisionCode + @"'
";
}
}
string terminationCondition = " and dbo.Account.TerminationInfoID is not null ";
if (TerminationStatus.Equals(EmployeeTerminatedStatus.Terminated))
{
query += terminationCondition;
}
IList <Member> results =
iSabayaContext.PersistencySession.CreateSQLQuery(query).AddEntity("employee",
typeof(Member)).List <Member>();
return(results);
}