Exemplo n.º 1
0
    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);
    }
Exemplo n.º 2
0
    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);
    }