public int RequestCount()
{
VOExpenseGroup criteria = GetExpenseGroupCriteria();
int count = ScgDbQueryProvider.DbExpenseGroupQuery.CountExpenseGroupByCriteria(criteria);
return(count);
}
public int GetExpenseGroupCount(VOExpenseGroup criteria, short languageId)
{
return(NHibernateQueryHelper.CountByCriteria(
ScgDbQueryProvider.DbExpenseGroupLangQuery,
"FindExpenseGroupByCriteria",
new object[] { criteria, languageId, true, string.Empty }));
}
public IList <VOExpenseGroup> GetExpenseGroupList(VOExpenseGroup criteria, short languageId, int firstResult, int maxResult, string sortExpression)
{
return(NHibernateQueryHelper.FindPagingByCriteria <VOExpenseGroup>(
ScgDbQueryProvider.DbExpenseGroupLangQuery,
"FindExpenseGroupByCriteria",
new object[] { criteria, languageId, false, sortExpression },
firstResult, maxResult, sortExpression));
}
public VOExpenseGroup GetExpenseGroupCriteria()
{
VOExpenseGroup expGroup = new VOExpenseGroup();
expGroup.ExpenseGroupCode = ctlExpenseGroupSearch.Text;
expGroup.Description = ctlDescriptionSearch.Text;
return(expGroup);
}
public int CountExpenseGroupByCriteria(VOExpenseGroup criteria)
{
return(NHibernateQueryHelper.CountByCriteria(ScgDbQueryProvider.DbExpenseGroupQuery, "FindExpenseGroupByCriteria", new object[] { criteria, true, string.Empty }));
}
public ISQLQuery FindExpenseGroupByCriteria(VOExpenseGroup criteria, bool isCount, string sortExpression)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" select DbExpenseGroup.ExpenseGroupID as ExpenseGroupID , DbExpenseGroup.ExpenseGroupCode as ExpenseGroupCode, ");
sqlBuilder.Append(" DbExpenseGroup.Active as Active , ");
sqlBuilder.Append(" DbExpenseGroupLang.ExpenseGroupLangID as ExpenseGroupLangID, DbExpenseGroupLang.LanguageID as LanguageID ");
sqlBuilder.Append(" , DbExpenseGroupLang.Description as Description ");
}
else
{
sqlBuilder.Append(" select count(DbExpenseGroup.ExpenseGroupID ) as Count ");
}
sqlBuilder.Append(" from DbExpenseGroup left join DbExpenseGroupLang ");
sqlBuilder.Append(" on DbExpenseGroup.ExpenseGroupID=DbExpenseGroupLang.ExpenseGroupID and DbExpenseGroupLang.LanguageID= :langId ");
queryParameterBuilder.AddParameterData("langId", typeof(short), UserAccount.CurrentLanguageID);
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(criteria.Description))
{
whereClauseBuilder.Append(" and DbExpenseGroupLang.Description like :Description ");
queryParameterBuilder.AddParameterData("Description", typeof(string), String.Format("%{0}%", criteria.Description));
}
if (!string.IsNullOrEmpty(criteria.ExpenseGroupCode))
{
whereClauseBuilder.Append(" and DbExpenseGroup.ExpenseGroupCode like :ExpenseGroupCode ");
queryParameterBuilder.AddParameterData("ExpenseGroupCode", typeof(string), String.Format("%{0}%", criteria.ExpenseGroupCode));
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
}
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by DbExpenseGroup.ExpenseGroupCode,DbExpenseGroupLang.Description");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("ExpenseGroupID", NHibernateUtil.Int64)
.AddScalar("ExpenseGroupLangID", NHibernateUtil.Int64)
.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("ExpenseGroupCode", NHibernateUtil.String)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOExpenseGroup)));
}
return(query);
}
public Object RequestData(int startRow, int pageSize, string sortExpression)
{
VOExpenseGroup criteria = GetExpenseGroupCriteria();
return(ScgDbQueryProvider.DbExpenseGroupQuery.GetExpenseGroupByCriteria(criteria, startRow, pageSize, sortExpression));
}
public ISQLQuery FindExpenseGroupByCriteria(VOExpenseGroup criteria, short languageId, bool isCount, string sortExpression)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
if (isCount)
{
sqlBuilder.Append(" select count(*) as Count ");
}
else
{
sqlBuilder.Append(" select e.ExpenseGroupID as ExpenseGroupID, el.ExpenseGroupName as ExpenseGroupName, e.Description as Description, e.Active as Active , ");
}
sqlBuilder.Append(" from DbExpenseGroup e ");
sqlBuilder.Append(" inner join DbExpenseGroupLang el on e.ExpenseGroupID = el.ExpenseGroupID and el.LanguageID = :LanguageID ");
parameterBuilder.AddParameterData("LanguageID", typeof(short), languageId);
StringBuilder whereClauseBuilder = new StringBuilder();
whereClauseBuilder.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(criteria.ExpenseGroupName))
{
whereClauseBuilder.Append(" and el.ExpenseGroupName Like :ExpenseGroupName ");
parameterBuilder.AddParameterData("ExpenseGroupName", typeof(string), String.Format("%{0}%", criteria.ExpenseGroupName));
}
if (!string.IsNullOrEmpty(criteria.Description))
{
whereClauseBuilder.Append(" and e.Description Like :Description ");
parameterBuilder.AddParameterData("Description", typeof(string), String.Format("%{0}%", criteria.Description));
}
sqlBuilder.Append(whereClauseBuilder.ToString());
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by e.ExpenseGroupID, el.ExpenseGroupName, e.Description, e.Active");
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("ExpenseGroupID", NHibernateUtil.Int64)
.AddScalar("ExpenseGroupName", NHibernateUtil.String)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOExpenseGroup)));
}
else
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
return(query);
}