public void BindSimpleExpense(string expenseType, long userId, bool refreshHeader) { this.refreshHeaderGrid = refreshHeader; ExpenseRecommend recommand = new ExpenseRecommend(); recommand.UserID = userId; recommand.LanguageID = UserAccount.CurrentLanguageID; SuUser requester = QueryProvider.SuUserQuery.FindByIdentity(userId); if (requester != null) { CompanyIDRequester = requester.Company.CompanyID; } this.DocumentType = expenseType; if (expenseType.Equals(ZoneType.Domestic)) { recommand.IsDomesticRecommend = true; } else if (expenseType.Equals(ZoneType.Foreign)) { recommand.IsForegnRecommend = true; } ExpenseDataSet ds = (ExpenseDataSet)TransactionService.GetDS(this.TransactionId); ExpenseDataSet.FnExpenseDocumentRow expRow = ds.FnExpenseDocument.FindByExpenseID(ExpDocumentID); if (!expRow.IsLocalCurrencyIDNull()) { this.FinalCurrencyID = expRow.LocalCurrencyID; } ctlSimpleExpenseGridView.DataSource = ScgDbQueryProvider.DbAccountQuery.FindExpenseRecommendByExpenseGroup(recommand); ctlSimpleExpenseGridView.DataBind(); SetCurrentSimpleExpense(); ctlUpdateSimpleExpenseGridView.Update(); }
public IList <ExpenseRecommend> FindExpenseRecommendByExpenseGroup(ExpenseRecommend recommend) { QueryParameterBuilder parameterBuilder = new QueryParameterBuilder(); StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append(" select distinct ac.AccountID as AccountID, ac.AccountCode as AccountCode, acl.AccountName,ac.DomesticRecommend, ac.ForeignRecommend,cost.CostCenterID as CostCenterID, cost.CostCenterCode as CostCenterCode "); sqlBuilder.Append(" from DbAccount ac "); sqlBuilder.Append(" inner join DbAccountLang acl on ac.AccountID = acl.AccountID and acl.LanguageID = :LangugeID "); sqlBuilder.Append(" inner join DbExpenseGroup eg on ac.ExpenseGroupID = eg.ExpenseGroupID and eg.Active = 1 "); sqlBuilder.Append(" inner join DbAccountCompany acc on ac.AccountID = acc.AccountID "); sqlBuilder.Append(" inner join SuUser u on u.CompanyID = acc.CompanyID and u.UserID = :UserID "); sqlBuilder.Append(" inner join DbCostCenter cost on cost.CostCenterID = u.CostCenterID "); sqlBuilder.Append(" where ac.Active = 1 "); parameterBuilder.AddParameterData("UserID", typeof(long), recommend.UserID); parameterBuilder.AddParameterData("LangugeID", typeof(short), recommend.LanguageID); if (recommend.IsDomesticRecommend) { sqlBuilder.Append(" and DomesticRecommend = 1 "); } else if (recommend.IsForegnRecommend) { sqlBuilder.Append(" and ForeignRecommend = 1 "); } sqlBuilder.Append(" order by cost.CostCenterCode, ac.AccountCode, acl.AccountName "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); parameterBuilder.FillParameters(query); query.AddScalar("CostCenterID", NHibernateUtil.Int64) .AddScalar("CostCenterCode", NHibernateUtil.String) .AddScalar("AccountID", NHibernateUtil.Int64) .AddScalar("AccountCode", NHibernateUtil.String) .AddScalar("AccountName", NHibernateUtil.String); query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpenseRecommend))); return(query.List <ExpenseRecommend>()); }