//AUTO Complete
public IList <DBProvinceLovReturn> FindByProvinceAutoComplete(string ProvincePrefix, string languageId, string RegionID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbProvince.ProvinceID AS ProvinceId ,");
sqlBuilder.Append(" DbProvince.RegionID AS RegionId ,");
sqlBuilder.Append(" DbProvince.Comment AS Comment ,");
sqlBuilder.Append(" DbProvince.Active AS Active ,");
sqlBuilder.Append(" DbProvinceLang.ProvinceName AS ProvinceName ,");
sqlBuilder.Append(" DbRegionLang.RegionName AS RegionName ");
sqlBuilder.Append(" FROM DbProvince ");
sqlBuilder.Append(" LEFT JOIN DbProvinceLang ON ");
sqlBuilder.Append(" DbProvince.ProvinceId = DbProvinceLang.ProvinceId AND ");
sqlBuilder.Append(" DbProvinceLang.LanguageID = :LanguageId ");
sqlBuilder.Append(" LEFT JOIN DbRegionLang ON ");
sqlBuilder.Append(" DbProvince.RegionId = DbRegionLang.RegionId AND ");
sqlBuilder.Append(" DbRegionLang.LanguageID = :LanguageId ");
sqlBuilder.Append(" WHERE 1=1 ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId);
if (ProvincePrefix != null && ProvincePrefix != "")
{
sqlBuilder.Append(" AND DbProvinceLang.ProvinceName LIKE :ProvinceName ");
parameterBuilder.AddParameterData("ProvinceName", typeof(string), ProvincePrefix + "%");
}
if (RegionID != null && RegionID != "")
{
sqlBuilder.Append(" AND DbProvince.RegionID = :RegionId ");
parameterBuilder.AddParameterData("RegionId", typeof(short), short.Parse(RegionID));
}
sqlBuilder.AppendLine(" ORDER BY DbProvinceLang.ProvinceName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("ProvinceID", NHibernateUtil.Int16);
query.AddScalar("RegionID", NHibernateUtil.Int16);
query.AddScalar("ProvinceName", NHibernateUtil.String);
query.AddScalar("RegionName", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DBProvinceLovReturn))).List <DBProvinceLovReturn>());
}
public void UpdateMarkDocument(IList <ReimbursementReportValueObj> obj)
{
foreach (ReimbursementReportValueObj item in obj)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
sqlBuilder.Append("UPDATE Document ");
sqlBuilder.Append("SET Mark = :Mark ");
sqlBuilder.Append("WHERE DocumentID = :DocumentID ");
queryParameterBuilder.AddParameterData("Mark", typeof(string), String.Format("{0}", item.Mark));
queryParameterBuilder.AddParameterData("DocumentID", typeof(string), String.Format("{0}", item.DocumentID));
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("count", NHibernateUtil.Int32);
query.UniqueResult();
}
}
public IList <UserFavoriteInitiator> FindUserFavoriteInitiatorByUserID(InitiatorCriteria criteria, long UserID)
{
//ActoType = 2 : Initialtor
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT Row_Number() OVER(ORDER BY SuUserFavoriteActor.UserID DESC) as InitiatorSeq, SuUserFavoriteActor.UserFavoriteActorID");
sqlBuilder.Append(", SuUserFavoriteActor.UserID");
sqlBuilder.Append(", SuUserFavoriteActor.ActorType");
sqlBuilder.Append(", SuUserFavoriteActor.ActorUserID");
sqlBuilder.Append(", SuUser.Email");
sqlBuilder.Append(", SuUser.EmployeeName");
sqlBuilder.Append(", SuUser.SMS");
sqlBuilder.Append(" FROM SuUserFavoriteActor LEFT OUTER JOIN");
sqlBuilder.Append(" SuUser ON SuUserFavoriteActor.ActorUserID = SuUser.UserID ");
sqlBuilder.Append(" WHERE SuUserFavoriteActor.UserID = :UserID");
sqlBuilder.AppendFormat(" AND SuUserFavoriteActor.ActorType = {0}", "2");
if (criteria.UserIDFilter != null && criteria.UserIDFilter.Count > 0)
{
string filter = "";
foreach (DocumentInitiatorLang item in criteria.UserIDFilter)
{
filter += "'" + item.UserID.ToString() + "',";
}
sqlBuilder.AppendFormat(" AND SuUserFavoriteActor.ActorUserID NOT IN ({0})", filter.TrimEnd(','));
}
sqlBuilder.Append(" ORDER BY SuUserFavoriteActor.ActorUserID ASC");
parameterBuilder.AddParameterData("UserID", typeof(long), UserID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("UserFavoriteActorID", NHibernateUtil.Int64)
.AddScalar("UserID", NHibernateUtil.Int64)
.AddScalar("ActorUserID", NHibernateUtil.Int64)
.AddScalar("ActorType", NHibernateUtil.String)
.AddScalar("EmployeeName", NHibernateUtil.String)
.AddScalar("Email", NHibernateUtil.String)
.AddScalar("SMS", NHibernateUtil.Boolean)
.AddScalar("InitiatorSeq", NHibernateUtil.Int16);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(UserFavoriteInitiator))).List <UserFavoriteInitiator>());
}
public IList <VOAccountCompany> FindAccountCompany(long companyId, long accountId)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select ac.ID as ID ,ac.AccountID as AccountID, ac.CompanyID as CompanyID, ");
sqlBuilder.Append(" ac.active as Active , com.CompanyCode as CompanyCode, ");
sqlBuilder.Append(" com.CompanyName as CompanyName from DbAccountCompany ac ");
sqlBuilder.Append(" inner join DbCompany com on ac.CompanyID=com.CompanyID ");
sqlBuilder.Append(" inner join DbAccount a on a.AccountID=ac.AccountID ");
StringBuilder whereClauseBuilder = new StringBuilder();
whereClauseBuilder.Append(" and a.Active = 1 ");
if (accountId != 0)
{
whereClauseBuilder.Append(" and ac.AccountID= :AccountID ");
queryParameterBuilder.AddParameterData("AccountID", typeof(long), accountId);
}
if (companyId != 0)
{
whereClauseBuilder.Append(" and com.CompanyID= :CompanyID ");
queryParameterBuilder.AddParameterData("CompanyID", typeof(long), companyId);
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("ID", NHibernateUtil.Int64)
.AddScalar("AccountID", NHibernateUtil.Int64)
.AddScalar("CompanyID", NHibernateUtil.Int64)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOAccountCompany)));
IList <VOAccountCompany> list = query.List <VOAccountCompany>();
return(list);
}
public ISQLQuery FindHolidayProfileByCriteria(int?year, string sortExpression, bool isCount, string type)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder paramBuilder = new QueryParameterBuilder();
if (!isCount)
{
sqlBuilder.Append(" select Id,Year,IsApprove,Type,UpdBy, UpdDate, CreBy,CreDate,UpdPgm,RowVersion");
}
else
{
sqlBuilder.Append(" select count(*) as Count ");
}
sqlBuilder.Append(" from DbHolidayProfile where 1=1 ");
if (year > 0)
{
sqlBuilder.Append(" and Year like :year ");
paramBuilder.AddParameterData("year", typeof(Int32), year);
}
sqlBuilder.Append(" and Type like :type ");
paramBuilder.AddParameterData("type", typeof(string), type);
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
paramBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("Id", NHibernateUtil.Int32);
query.AddScalar("Year", NHibernateUtil.Int32);
query.AddScalar("IsApprove", NHibernateUtil.Boolean);
query.AddScalar("Type", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbHolidayProfile)));
}
return(query);
}
public async Task <TokenResponse> GetAccessTokenFromUsernamePasswordAsync(CancellationToken cancellationToken)
{
var queryParams = new QueryParameterBuilder("grant_type", "password");
queryParams.AddQueryPair("username", _authenticationParameters.UserName);
queryParams.AddQueryPair("password", _authenticationParameters.Password);
AddClientIdQueryParam(queryParams);
AddScopeQueryParam(queryParams);
AddClientInfoQueryParam(queryParams);
var response = await _httpManager.PostAsync(
_authenticationParameters.AuthorityUri.GetTokenEndpoint(),
GetVersionHeaders(),
queryParams.ToString(),
cancellationToken).ConfigureAwait(false);
return(TokenResponse.Create(response.ResponseData));
}
public bool IsRepOffice(long UserID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select isnull(DbPb.RepOffice,0) as RepOffice from SuUser as suUser");
sqlBuilder.Append(" LEFT JOIN DBLocation as location on suUser.LocationID = location.LocationID");
sqlBuilder.Append(" LEFT JOIN DbPB as DbPb on location.DefaultPbID = DbPb.PBID");
sqlBuilder.Append(" WHERE suUser.UserID = :UserID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("UserID", typeof(long), UserID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("RepOffice", NHibernateUtil.Boolean);
return(query.UniqueResult <bool?>() ?? false);
}
public long GetLetterIDByLetterNo(string letterNo)
{
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT TOP 1 l.LetterID FROM DbBuyingLetterDetail l ");
sql.Append(" WHERE l.LetterNo = :letterNo ");
sql.Append(" ORDER BY l.LetterID DESC ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("letterNo", typeof(string), letterNo);
parameterBuilder.FillParameters(query);
query.AddScalar("LetterID", NHibernateUtil.Int64);
return(query.UniqueResult <long>());
}
public long GetWorkflowIdByDocumentID(long documentID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select wf.workflowid ");
sqlBuilder.Append("from [document] doc ");
sqlBuilder.Append("inner join workflow wf ");
sqlBuilder.Append("on wf.documentid = doc.documentid ");
sqlBuilder.Append("where doc.documentid = :documentID ");
QueryParameterBuilder paramBuilder = new QueryParameterBuilder();
paramBuilder.AddParameterData("documentID", typeof(long), documentID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
paramBuilder.FillParameters(query);
return(query.UniqueResult <long>());
}
public IList <DocumentInitiator> FindByDocumentID(long DocumentID)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT * FROM DocumentInitiator ");
sqlBuilder.Append(" WHERE DocumentID = :DocumentID ");
parameterBuilder.AddParameterData("DocumentID", typeof(long), DocumentID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetInt64("DocumentID", DocumentID);
query.AddEntity(typeof(DocumentInitiator));
IList <DocumentInitiator> documentInitiator = query.List <DocumentInitiator>();
return(documentInitiator);
}
public WorkFlowState GetCurrentWorkFlowStateByWorkFolwID(long workFlowID)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT WT.* ");
sql.Append("FROM WorkFlow as W , WorkFlowState as WT , ");
sql.Append("Where ");
sql.Append(" W.CurrentState = WT.WorkFlowStateID AND ");
sql.Append(" W.WorkFlowID = :workFlowID ");
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
queryParameterBuilder.AddParameterData("workFlowID", typeof(long), workFlowID);
queryParameterBuilder.FillParameters(query);
return(query.AddEntity(typeof(WorkFlowState)).UniqueResult <WorkFlowState>());
}
public DbSapInstance GetSAPDocTypeForPosting(string companyCode)
{
/*N-Addnew column DoctypeFixedAdvance*/
string sqlCommand = @"SELECT sap.DocTypeExpPostingDM, sap.DocTypeExpPostingFR, sap.DocTypeExpRmtPostingDM, sap.DocTypeExpRmtPostingFR,
sap.DocTypeExpICPostingFR, sap.DocTypeAdvancePostingDM, sap.DocTypeAdvancePostingFR, sap.DocTypeRmtPosting, sap.UserCPIC
,sap.DocTypeFixedAdvance AS DocTypeFixedAdvancePosting,sap.DocTypeFixedAdvanceReturn AS DocTypeFixedAdvanceReturnPosting
FROM DbCompany
INNER JOIN DbSapInstance sap ON DbCompany.SapCode = sap.Code WHERE CompanyCode = :companycode";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlCommand);
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("companycode", typeof(string), companyCode);
parameterBuilder.FillParameters(query);
query.AddScalar("DocTypeExpPostingDM", NHibernateUtil.String);
query.AddScalar("DocTypeExpPostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeExpRmtPostingDM", NHibernateUtil.String);
query.AddScalar("DocTypeExpRmtPostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeExpICPostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeAdvancePostingDM", NHibernateUtil.String);
query.AddScalar("DocTypeAdvancePostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeRmtPosting", NHibernateUtil.String);
query.AddScalar("UserCPIC", NHibernateUtil.String);
/*N-edit*/
query.AddScalar("DocTypeFixedAdvancePosting", NHibernateUtil.String);
query.AddScalar("DocTypeFixedAdvanceReturnPosting", NHibernateUtil.String);
IList <DbSapInstance> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbSapInstance))).List <DbSapInstance>();
if (list.Count <= 0)
{
return(null);
}
else
{
return(list.ElementAt <DbSapInstance>(0));
}
}
public DbAccount FindAccountByAccountCodeExpenseGroup(string accountCode, long?companyId)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sql = new StringBuilder();
sql.Append(@" SELECT distinct a.AccountID as AccountID FROM DbAccount a
inner join DbExpenseGroup b on a.ExpenseGroupID = b.ExpenseGroupID
");
StringBuilder whereClauseBuilder = new StringBuilder();
whereClauseBuilder.Append(" WHERE a.Active=1 and a.AccountCode = :AccountCode ");
if (companyId.HasValue)
{
sql.Append(" inner join DbAccountCompany c on a.AccountID = c.AccountID ");
whereClauseBuilder.Append(" and c.CompanyID = :CompanyID ");
parameterBuilder.AddParameterData("CompanyID", typeof(long), companyId);
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sql.Append(whereClauseBuilder.ToString());
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
parameterBuilder.AddParameterData("AccountCode", typeof(string), accountCode);
parameterBuilder.FillParameters(query);
query.AddScalar("AccountID", NHibernateUtil.Int64);
IList <DbAccount> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbAccount))).List <DbAccount>();
if (list.Count > 0)
{
return(list.ElementAt <DbAccount>(0));
}
else
{
return(null);
}
}
public IList <PerdiemRateValObj> GetPerdiemRateByRequesterIDForRepOffice(long requesterId, string personalGroup)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select Pr.PerdiemRateID as PerdiemRateID, Pr.PerdiemProfileID as PerdiemProfileID, Pf.PerdiemProfileName as PerdiemProfileName, Pr.ZoneID as ZoneID, Pc.CompanyID as CompanyID, Pr.PersonalLevel as PersonalLevel, Pr.ExtraPerdiemRate as ExtraPerdiemRate, Pr.OfficialPerdiemRate as OfficialPerdiemRate ");
if (personalGroup.StartsWith("A"))
{
sqlBuilder.Append(", Pr.InternationalStaffPerdiemRate as StuffPerdiemRate ");
}
else if (personalGroup.StartsWith("I"))
{
sqlBuilder.Append(", Pr.SCGStaffPerdiemRate as StuffPerdiemRate ");
}
sqlBuilder.Append(" from FnPerdiemRate Pr left join FnPerdiemProfile Pf on Pr.PerdiemProfileID = Pf.PerdiemProfileID ");
sqlBuilder.Append(" left join FnPerdiemProfileCompany Pc on Pf.PerdiemProfileID = Pc.PerdiemProfileID ");
sqlBuilder.Append(" left join SuUser Su on Pc.CompanyID = Su.CompanyID and Pr.PersonalLevel = Su.PersonalLevel ");
sqlBuilder.Append(" where Su.UserID = :RequesterID ");
sqlBuilder.Append(" order by Pr.ZoneID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("RequesterID", typeof(long), requesterId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("PerdiemRateID", NHibernateUtil.Int64);
query.AddScalar("PerdiemProfileID", NHibernateUtil.Int64);
query.AddScalar("PerdiemProfileName", NHibernateUtil.String);
query.AddScalar("ZoneID", NHibernateUtil.Int16);
query.AddScalar("CompanyID", NHibernateUtil.Int64);
query.AddScalar("PersonalLevel", NHibernateUtil.String);
query.AddScalar("ExtraPerdiemRate", NHibernateUtil.Double);
query.AddScalar("OfficialPerdiemRate", NHibernateUtil.Double);
if (personalGroup.StartsWith("A") || personalGroup.StartsWith("I"))
{
query.AddScalar("StuffPerdiemRate", NHibernateUtil.Double);
}
query.SetResultTransformer(Transformers.AliasToBean(typeof(PerdiemRateValObj)));
IList <PerdiemRateValObj> ilistquery = query.List <PerdiemRateValObj>();
return(ilistquery);
}
public FnExpenseDataForEmail GetExpenseForEmailByDocumentID(long documentID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select doc.DocumentID as DocumentID, exp.ExpenseID, ");
sql.AppendLine(" doc.DocumentNo as RequestNo , ");
sql.AppendLine(" doc.Subject as Subject , ");
sql.AppendLine(" exp.TotalExpense as TotalExpense, ");
sql.AppendLine(" cr.Symbol as SymbolLocal, ");
sql.AppendLine(" cr2.Symbol as SymbolMain, ");
sql.AppendLine(" exp.TotalExpenseLocalCurrency as TotalExpenseLocal, ");
sql.AppendLine(" exp.TotalExpenseMainCurrency as TotalExpenseMain, ");
sql.AppendLine(" exp.IsRepOffice , exp.PBID , exp.DifferenceAmount,");
sql.AppendLine(" exp.DifferenceAmountLocalCurrency ,exp.DifferenceAmountMainCurrency ");
sql.AppendLine(" from FnExpenseDocument exp ");
sql.AppendLine(" inner join Document doc on doc.DocumentID = exp.DocumentID ");
sql.AppendLine(" left join DbCurrency cr on cr.CurrencyID = exp.LocalCurrencyID ");
sql.AppendLine(" left join DbCurrency cr2 on cr2.CurrencyID = exp.MainCurrencyID ");
sql.AppendLine(" where doc.DocumentID = :documentID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetParameter("documentID", documentID);
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentID", NHibernateUtil.Int64);
query.AddScalar("ExpenseID", NHibernateUtil.Int64);
query.AddScalar("RequestNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("TotalExpense", NHibernateUtil.Double);
query.AddScalar("SymbolLocal", NHibernateUtil.String);
query.AddScalar("SymbolMain", NHibernateUtil.String);
query.AddScalar("TotalExpenseLocal", NHibernateUtil.Double);
query.AddScalar("TotalExpenseMain", NHibernateUtil.Double);
query.AddScalar("IsRepOffice", NHibernateUtil.Boolean);
query.AddScalar("PBID", NHibernateUtil.Int64);
query.AddScalar("DifferenceAmount", NHibernateUtil.Double);
query.AddScalar("DifferenceAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("DifferenceAmountMainCurrency", NHibernateUtil.Double);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(FnExpenseDataForEmail))).UniqueResult <FnExpenseDataForEmail>());
}
public IList <FnExpenseDocument> GetPaymentType(long documentID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select FnExpenseDocument.PaymentType as PaymentType ");
sqlBuilder.Append(" from Document inner join ");
sqlBuilder.Append(" FnExpenseDocument on Document.DocumentID = FnExpenseDocument.DocumentID ");
sqlBuilder.Append(" where Document.DocumentID = :documentID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("documentID", typeof(long), documentID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("PaymentType", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(FnExpenseDocument))).List <FnExpenseDocument>());
}
/// <summary>
/// use for provide data to drop down list
/// </summary>
/// <returns></returns>
public IList <TranslatedListItem> GetAllServiceTeamListItem()
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
sqlBuilder.Append("SELECT dst.serviceteamid AS strID,");
sqlBuilder.Append("'[ ' + dst.serviceteamcode + ' ] ' + dst.description AS strSymbol ");
sqlBuilder.Append("FROM dbserviceteam dst ");
sqlBuilder.Append("WHERE dst.active=1 ");
//sqlBuilder.Append("ORDER BY dst.serviceteamcode");
sqlBuilder.Append("ORDER BY strSymbol");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("strID", NHibernateUtil.String)
.AddScalar("strSymbol", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>());
}
public DbLanguage FindLanguageByIdentity(short lid)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT LanguageID, LanguageName ");
sqlBuilder.Append(" FROM DbLanguage ");
StringBuilder whereClauseBuilder = new StringBuilder();
//whereClauseBuilder.Append(" and db.Active = 1 ");
if (lid != 0)
{
whereClauseBuilder.Append(" and LanguageID= :LID ");
queryParameterBuilder.AddParameterData("LID", typeof(short), lid);
}
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("Languageid", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbLanguage)));
IList <DbLanguage> currencyList = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbLanguage))).List <DbLanguage>();;
if (currencyList.Count > 0)
{
return(currencyList[0]);
}
else
{
return(null);
}
}
public IList <VOVendor> FindVendorByVendorTaxNO(string taxNo, string branch)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select VendorID, VendorCode, VendorBranch ,Street, City, Country, PostalCode, VendorName, VendorTaxCode ");
sqlBuilder.Append(" from (");
sqlBuilder.Append(" select VendorID, VendorCode, TaxNo4 as VendorBranch,Street, City, Country, PostalCode, ");
sqlBuilder.Append(" isnull(VendorTitle,'') +' '+ isnull(VendorName1,'') +' '+ isnull(VendorName2,'') as VendorName,isnull(TaxNo3,'') as VendorTaxCode");
sqlBuilder.Append(" from DbVendor with (nolock) where Active = 1 and TaxNo3 like :taxNo");
sqlBuilder.Append(" union select null as VendorID, VendorCode, VendorBranch,Street, City, Country, PostalCode, VendorName, VendorTaxCode ");
sqlBuilder.Append(" from DbOneTimeVendor with (nolock) where 1=1 and VendorTaxCode like :taxNo ");
sqlBuilder.Append(" union select null as VendorID, t.VendorCode, t.VendorBranch,t.Street, t.City, t.Country, t.PostalCode, t.VendorName, t.VendorTaxCode ");
sqlBuilder.Append(" from FnExpenseInvoice t with (nolock) where t.Active = 1 and (convert(varchar,CreDate,103)= convert(varchar,:CreDate,103)) ");
sqlBuilder.Append(" and NULLIF (t.VendorCode, '') <> '' and NULLIF (t.VendorName, '') <> '' ");
sqlBuilder.Append(" and NULLIF (t.Street, '') <> '' and NULLIF (t.Country, '') <> '' and NULLIF (t.VendorTaxCode, '') <> '' " + (string.IsNullOrEmpty(taxNo) ? string.Empty : " and t.VendorTaxCode like :taxNo "));
sqlBuilder.Append(" and t.VendorID = null ");
sqlBuilder.Append(" and not exists(SELECT 'X' FROM DbOneTimeVendor o WHERE o.VendorTaxCode = t.VendorTaxCode AND o.VendorBranch = t.VendorBranch) ");
sqlBuilder.Append(" )t1 where VendorBranch = :vendorBranch ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("CreDate", typeof(DateTime), DateTime.Today);
parameterBuilder.AddParameterData("taxNo", typeof(string), taxNo);
parameterBuilder.AddParameterData("vendorBranch", typeof(string), branch);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("VendorID", NHibernateUtil.Int64)
.AddScalar("VendorCode", NHibernateUtil.String)
.AddScalar("VendorBranch", NHibernateUtil.String)
.AddScalar("Street", NHibernateUtil.String)
.AddScalar("City", NHibernateUtil.String)
.AddScalar("Country", NHibernateUtil.String)
.AddScalar("PostalCode", NHibernateUtil.String)
.AddScalar("VendorName", NHibernateUtil.String)
.AddScalar("VendorTaxCode", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOVendor)));
return(query.List <VOVendor>());
}
public IList <TranslatedListItem> GetTranslatedListItem(short languageID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT payment.PaymentTypeID as ID, payment.Name as Text ");
sqlBuilder.Append(" FROM FnPaymentTypeLang payment WHERE payment.LanguageID = :LanguageID ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageID", typeof(short), languageID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("ID", NHibernateUtil.Int16)
.AddScalar("Text", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>());
}
public DbWithHoldingTax FindWithHoldingTaxByWhtCode(string WHTCode)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
string sql = "SELECT Whtid, WhtCode, WhtName, Rate, Active FROM DbWithHoldingTax WHERE WHTCode =:WHTCode";
parameterBuilder.AddParameterData("WHTCode", typeof(string), WHTCode);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
parameterBuilder.FillParameters(query);
query.AddScalar("Whtid", NHibernateUtil.Int64);
query.AddScalar("WhtCode", NHibernateUtil.String);
query.AddScalar("WhtName", NHibernateUtil.String);
query.AddScalar("Rate", NHibernateUtil.Double);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbWithHoldingTax)));
return(query.UniqueResult <DbWithHoldingTax>());
}
public DbBuyingLetter FindLetterByDocumentID(long documentID)
{
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT LetterID,DocumentID FROM DbBuyingLetter where DocumentID = :documentID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("documentID", typeof(Int64), documentID);
parameterBuilder.FillParameters(query);
query.AddScalar("LetterID", NHibernateUtil.Int64);
query.AddScalar("DocumentID", NHibernateUtil.Int64);
DbBuyingLetter buyingLetter = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbBuyingLetter))).UniqueResult <DbBuyingLetter>();
return(buyingLetter);
}
/// <summary>
/// for bind ddl (service Team)
/// </summary>
/// <param name="companyID"></param>
/// <param name="companyCode"></param>
/// <returns></returns>
public IList <PaymentTypeListItem> FindByLocationCompany(long companyID, string companyCode, long userID, long locationID)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
string qs = @"
select distinct t4.ServiceTeamCode as Code, t4.Description as Text, t4.ServiceTeamID as ID
from
dbcompany t1
inner join dblocation t2 on t1.companyid = t2.companyid
inner join dbserviceteamlocation t3 on t2.locationid = t3.locationid
inner join dbserviceteam t4 on t3.serviceteamid = t4.serviceteamid
where
t1.active = 1 and t2.active = 1 and t3.active = 1 and t4.active = 1 and
t1.companyid = :companyID
";
parameterBuilder.AddParameterData("companyID", typeof(Int64), companyID);
if (!String.IsNullOrEmpty(companyCode))
{
qs += " and t1.companycode = :companyCode";
parameterBuilder.AddParameterData("companyCode", typeof(String), companyCode);
}
//if (locationID > 0)
//{
// qs += " and t2.locationId = :locationID";
// parameterBuilder.AddParameterData("locationID", typeof(long), locationID);
//}
//qs += " group by t4.ServiceTeamCode, t4.Description, t4.ServiceTeamID ";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(qs);
parameterBuilder.FillParameters(query);
query.AddScalar("Code", NHibernateUtil.String);
query.AddScalar("Text", NHibernateUtil.String);
query.AddScalar("ID", NHibernateUtil.Int64);
IList <PaymentTypeListItem> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(PaymentTypeListItem))).List <PaymentTypeListItem>();
return(list);
}
public IList <ParameterGroup> FindByDbParameterGroupCriteria(DbParameterGroup parameterGroup)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select d.ParameterValue ");
sql.Append("from DbParameterGroup as d ");
sql.Append("where d.GroupName = :GroupName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("GroupName", typeof(String), parameterGroup.GroupName);
queryParameterBuilder.FillParameters(query);
query.AddScalar("GroupName", NHibernateUtil.String);;
IList <SS.DB.DTO.ValueObject.ParameterGroup> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.DB.DTO.ValueObject.ParameterGroup)))
.List <SS.DB.DTO.ValueObject.ParameterGroup>();
return(list);
}
public IList <DbTax> GetTaxCodeActiveByCompany(long companyID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select DbTax.TaxCode,DbTax.TaxID ");
sqlBuilder.Append(" from DbTax ");
sqlBuilder.Append(" left join DbCompanyTax on DbTax.TaxID = DbCompanyTax.TaxID and DbCompanyTax.CompanyID = :companyID");
sqlBuilder.Append(" Where DbTax.Active = 1 ");
sqlBuilder.Append(" and (ISNULL(DbCompanyTax.Disable,0) <> 1)");
sqlBuilder.Append(" or (DbTax.ApplyAllCompany = 1 and DbTax.Active = 1)");
sqlBuilder.Append(" order by DbTax.TaxCode ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("companyID", typeof(string), companyID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("TaxCode", NHibernateUtil.String)
.AddScalar("TaxID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbTax))).List <DbTax>());
}
public string GetAccountCodeExpMapping(string accountCode, string expenseGroupType)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
string sql = "SELECT TOP 1 AccountCodeExpUse FROM DbAccountMapping WHERE AccountCodeEcc = :accountCode ";
if (!string.IsNullOrEmpty(expenseGroupType))
{
sql += " and (ExpenseGroupType IN (:expenseGroupType, '2')) ";
parameterBuilder.AddParameterData("expenseGroupType", typeof(string), expenseGroupType);
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
parameterBuilder.AddParameterData("accountCode", typeof(string), accountCode);
parameterBuilder.FillParameters(query);
query.AddScalar("AccountCodeExpUse", NHibernateUtil.String);
return(query.UniqueResult <string>());
}
public IList <Language> FindByDbLanguageCriteria(DbLanguage language)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select l.LanguageName ");
sql.Append("from DbLanguage as l ");
sql.Append("where l.LanguageName = :LanguageName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LanguageName", typeof(String), language.LanguageName);
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageName", NHibernateUtil.String);;
IList <SS.DB.DTO.ValueObject.Language> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.DB.DTO.ValueObject.Language)))
.List <SS.DB.DTO.ValueObject.Language>();
return(list);
}
public IList <ProgramRole> FindSuProgramRoleByProgramCode(string programCode)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" SELECT SuProgramRole.RoleId ");
sqlBuilder.AppendLine(" FROM SuProgramRole INNER JOIN SuProgram ON SuProgram.ProgramID = SuProgramRole.ProgramID ");
sqlBuilder.AppendLine(" WHERE SuProgram.ProgramCode = :ProgramCode ");
sqlBuilder.AppendLine(" AND SuProgramRole.Active = 1 ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("ProgramCode", typeof(string), programCode);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("RoleId", NHibernateUtil.Int16);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.ProgramRole))).List <ProgramRole>());
}
public IList <short> GetUserRolePBByUserID(long userID)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
sqlBuilder.AppendLine(" SELECT sr.RoleID ");
sqlBuilder.AppendLine(" FROM SuRole sr ");
sqlBuilder.AppendLine(" INNER JOIN SuUserRole sur ");
sqlBuilder.AppendLine(" ON sr.RoleID = sur.RoleID ");
sqlBuilder.AppendLine(" AND sur.UserID = :UserID ");
sqlBuilder.AppendLine(" WHERE sr.ApproveVerifyPayment = 1 or sr.VerifyPayment = 1 or sr.CounterCashier = 1 ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.SetInt64("UserID", userID);
query.AddScalar("RoleID", NHibernateUtil.Int16);
return(query.List <short>());
}
public async Task <TokenResponse> GetAccessTokenFromAuthCodeAsync(
string authCode,
CancellationToken cancellationToken)
{
var queryParams = new QueryParameterBuilder("grant_type", "authorization_code");
queryParams.AddQueryPair("code", authCode);
AddRedirectUriQueryParam(queryParams);
AddClientIdQueryParam(queryParams);
AddScopeQueryParam(queryParams);
AddClientInfoQueryParam(queryParams);
var response = await _httpManager.PostAsync(
_authenticationParameters.AuthorityUri.GetTokenEndpoint(),
GetVersionHeaders(),
queryParams.ToString(),
cancellationToken).ConfigureAwait(false);
return(TokenResponse.Create(response.ResponseData));
}
