public IList <AccountLang> FindAutoComplete(string prefixText, AccountAutoCompleteParameter param)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select distinct ac.AccountID as AccountID, ac.AccountCode as AccountCode, acl.AccountName as AccountName ");
sqlBuilder.Append(" from DbAccount ac ");
sqlBuilder.Append(" inner join DbAccountLang acl on acl.AccountID = ac.AccountID and acl.LanguageID = :LanguageID ");
sqlBuilder.Append(" inner join dbo.DbAccountCompany accC ");
sqlBuilder.Append(" on accC.AccountID = ac.AccountID ");
sqlBuilder.Append(" inner join DbExpenseGroup exG ");
sqlBuilder.Append(" on exG.ExpenseGroupID = ac.ExpenseGroupID ");
StringBuilder whereClauseBuilder = new StringBuilder();
whereClauseBuilder.Append(" and ac.Active = 1 ");
if (!string.IsNullOrEmpty(prefixText))
{
whereClauseBuilder.Append(" and ((ac.AccountCode Like :Account) or (acl.AccountName Like :Account)) ");
queryParameterBuilder.AddParameterData("Account", typeof(string), String.Format("0000{0}%", prefixText));
}
if ((param.ExpenseGroupID) != null && param.ExpenseGroupID > 0)
{
whereClauseBuilder.Append(" and ac.ExpenseGroupID = :ExpenseGroupID ");
queryParameterBuilder.AddParameterData("ExpenseGroupID", typeof(string), param.ExpenseGroupID);
}
if ((param.CompanyID) != null && param.CompanyID > 0)
{
whereClauseBuilder.Append(" and accC.CompanyID = :CompanyID ");
queryParameterBuilder.AddParameterData("CompanyID", typeof(long), param.CompanyID);
}
if (!string.IsNullOrEmpty(param.WithoutExpenseCode))
{
whereClauseBuilder.Append(" and ac.AccountCode not in (" + param.WithoutExpenseCode + ") ");
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
}
queryParameterBuilder.AddParameterData("LanguageID", typeof(Int16), param.LanguageID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("AccountID", NHibernateUtil.Int64)
.AddScalar("AccountCode", NHibernateUtil.String)
.AddScalar("AccountName", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(AccountLang)));
IList <AccountLang> list = query.List <AccountLang>();
return(list);
}
public void SerializeParameter()
{
AccountAutoCompleteParameter parameter = new AccountAutoCompleteParameter();
parameter.LanguageID = UserAccount.CurrentLanguageID;
parameter.CompanyID = this.CompanyID;
parameter.WithoutExpenseCode = this.WithoutExpenseCode;
JavaScriptSerializer serializer = new JavaScriptSerializer();
ctlAccountTextAutoComplete.ContextKey = serializer.Serialize(parameter);
ctlAccountTextAutoComplete.UseContextKey = true;
}
protected void Page_Load(object sender, EventArgs e)
{
ctlAccountAutoCompleteUpdatePanel.Update();
ctlAccountTextAutoComplete.BehaviorID = String.Format("AccountAutoCompleteEx{0}", ctlAccountCode.ClientID);
SetAutoCompleteEvent();
AccountAutoCompleteParameter parameter = new AccountAutoCompleteParameter();
parameter.LanguageID = UserAccount.CurrentLanguageID;
parameter.CompanyID = this.CompanyID;
parameter.WithoutExpenseCode = this.WithoutExpenseCode;
JavaScriptSerializer serializer = new JavaScriptSerializer();
ctlAccountTextAutoComplete.ContextKey = serializer.Serialize(parameter);
ctlAccountTextAutoComplete.UseContextKey = true;
}
public string[] GetAccountList(string prefixText, int count, string contextKey)
{
JavaScriptSerializer serializer = new JavaScriptSerializer();
// AutoCompleteParameter is a ValueObject DTO that used in AutoComplete only
AccountAutoCompleteParameter parameter = serializer.Deserialize <AccountAutoCompleteParameter>(contextKey);
// Retreive Query Object from Spring
IDbAccountQuery DbAccountQuery = (IDbAccountQuery)ContextRegistry.GetContext().GetObject("DbAccountQuery");
// Return type of FindAutoComplete method must be a ValueObject only
IList <AccountLang> list = DbAccountQuery.FindAutoComplete(prefixText, parameter);
List <string> items = new List <string>(list.Count);
foreach (AccountLang ac in list)
{
// Serialize ValueObject to JSON String and set it to AutoCompleteItem's Value
string item = AutoCompleteExtender.CreateAutoCompleteItem(String.Format("[{0}]{1}", ac.AccountCode, ac.AccountName), serializer.Serialize(ac));
items.Add(item);
}
return(items.ToArray());
}
