public void DeleteLetter(DbBuyingLetter letter)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(" DELETE FROM DbBuyingLetter WHERE LetterID = :letterId AND DocumentID = :documentID ");
query.SetInt64("letterId", letter.LetterID);
query.SetInt64("documentID", letter.DocumentID);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public void UpdateFnPerdiemProfileCompany(long companyID, long perdiemProfileID, long userID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[UpdateFnPerdiemProfileCompany] :companyID , :perdiemProfileID , :userID ");
query.SetInt64("companyID", companyID);
query.SetInt64("perdiemProfileID", perdiemProfileID);
query.SetInt64("userID", userID);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public bool CommitToAutoPayment(long documentID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE FnAutoPayment ");
sqlBuilder.Append("SET ");
sqlBuilder.Append("Status = t1.status, ");
sqlBuilder.Append("chequenumber = t2.chequenumber, ");
sqlBuilder.Append("chequeBankName = t2.chequeBankName, ");
sqlBuilder.Append("chequeDate = t2.chequeDate, ");
sqlBuilder.Append("PayeeBankAccountNumber = t2.PayeeBankAccountNumber, ");
sqlBuilder.Append("PayeeBankName = t2.PayeeBankName, ");
sqlBuilder.Append("Amount = t1.Amount, ");
sqlBuilder.Append("PaymentDate = t2.PaymentDate, ");
sqlBuilder.Append("UpdDate = t2.UpdDate,");
sqlBuilder.Append("CurrencyDoc = t2.CurrencyDoc, ");
sqlBuilder.Append("CurrencyPay = t2.CurrencyPay ");
sqlBuilder.Append("FROM FnAutoPaymentTemp t1 ");
sqlBuilder.Append("INNER JOIN FnAutoPaymentTemp t2 ");
sqlBuilder.Append("ON t1.ClearingDocID = t2.FIDOC and t1.companycode = t2.companycode ");
sqlBuilder.Append("WHERE FnAutoPayment.FIDoc = t1.FIDoc and FnAutoPayment.DocumentID = :DocumentID ");
sqlBuilder.Append(" and FnAutoPayment.companycode = t1.companycode ");
//to do resolve.
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetInt64("DocumentID", documentID);
query.AddScalar("TCount", NHibernateUtil.Int32).UniqueResult();
return(IsUpdateSuccess(documentID));
}
public ISQLQuery FindSuRoleLangSearchResult(SuRoleLangSearchResult roleLangSearchResult, short languageID, string roleName, long userID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT r.RoleID as RoleId, rl.RoleName as RoleName ");
strQuery.AppendLine(" , lang.LanguageID as LanguageId, lang.LanguageName as LanguageName ");
strQuery.AppendLine(" , r.Active as Active ");
strQuery.AppendLine(" FROM SuRole r ");
strQuery.AppendLine(" INNER JOIN SuRoleLang rl ");
strQuery.AppendLine(" ON rl.RoleID = r.RoleID AND rl.LanguageID = :languageID ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ");
strQuery.AppendLine(" ON lang.LanguageID = :languageID ");
strQuery.AppendLine(" WHERE rl.RoleName LIKE :roleName ");
strQuery.AppendLine(" AND r.Active = 'true' ");
strQuery.AppendLine(" AND r.RoleID NOT IN ");
strQuery.AppendLine(" (SELECT ur.RoleID FROM SuUserRole ur WHERE ur.UserID = :userID) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("languageID", languageID);
query.SetString("roleName", "%" + roleName + "%");
query.SetInt64("userID", userID);
query.AddScalar("RoleId", NHibernateUtil.Int16);
query.AddScalar("RoleName", NHibernateUtil.String);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("LanguageName", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRoleLangSearchResult)));
return query;
}
public List <UserMenu> FindAllMenu(long userID, short languageID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(
@"
SELECT M.MenuID,ISNULL(M.MenuMainID,M.MenuID)AS MenuMainID
,ML.MenuName
,P.ProgramPath
,M.MenuLevel
,M.MenuSeq
,ML.LanguageID as MenuLanguageID
,SUR.RoleID
FROM SuMenu as M
LEFT OUTER JOIN SuMenuLang as ML ON M.MenuID = ML.MenuID AND ML.LanguageID = :LanguageID
LEFT OUTER JOIN SuProgram as P ON M.ProgramID = P.ProgramID
LEFT OUTER JOIN SuProgramRole SPR ON P.ProgramID = SPR.ProgramID
LEFT OUTER JOIN SuUserRole SUR ON SPR.RoleID = SUR.RoleID AND SUR.UserID = :UserID
WHERE M.Active = 1 AND ML.Active=1 AND (m.programid is null OR sur.roleid is not null)
Order by M.MenuLevel ASC, M.MenuSeq ASC "
);
query.SetInt64("UserID", userID);
query.SetInt16("LanguageID", languageID);
query.AddScalar("MenuID", NHibernateUtil.Int16);
query.AddScalar("MenuMainID", NHibernateUtil.Int16);
query.AddScalar("MenuName", NHibernateUtil.String);
query.AddScalar("ProgramPath", NHibernateUtil.String);
query.AddScalar("MenuLevel", NHibernateUtil.Int16);
query.AddScalar("MenuSeq", NHibernateUtil.Int16);
query.AddScalar("MenuLanguageID", NHibernateUtil.Int16);
query.AddScalar("RoleID", NHibernateUtil.Int16);
IList <UserMenu> menuList = query.SetResultTransformer(Transformers.AliasToBean(typeof(UserMenu))).List <UserMenu>();
return(menuList as List <UserMenu>);
}
public DbAccount FindAccountByEGroupIDAID(long expenseID, long accoutID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("SELECT * FROM DbAccount WHERE [ExpenseGroupID] = :expenseID and [AccountID] = :accoutID");
query.SetInt64("expenseID", expenseID);
query.SetInt64("accoutID", accoutID);
query.AddEntity(typeof(DbAccount));
IList <DbAccount> user = query.List <DbAccount>();
if (user.Count > 0)
{
return(user[0]);
}
else
{
return(null);
}
}
public SuSession GetUserSession(long userID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strGetSuSession);
query.SetInt64("UserID", userID);
query.AddEntity(typeof(SuSession));
IList <SuSession> suSession = query.List <SuSession>();
return(suSession.Count > 0 ? suSession[0] : null);
}
public void ResetActiveWorkFlowResponse(long wfid, int nextOrdinal)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(@"update WorkFlowResponse set Active = 0 from WorkFlowResponse a
inner join WorkFlowStateEvent b on a.WorkFlowStateEventID = b.WorkFlowStateEventID
inner join WorkFlowState c on b.WorkFlowStateID = c.WorkFlowStateID
where a.WorkFlowID = :WorkFlowID and a.Active = 1 and c.Ordinal >= :NextOrdinal ");
query.SetInt64("WorkFlowID", wfid)
.SetInt32("NextOrdinal", nextOrdinal);
query.AddScalar("Count", NHibernateUtil.Int32).UniqueResult();
}
public void DeleteWorkFlowStateEventPermission(long workFlowID)
{
#region Old Code
//GetCurrentSession()
// .Delete("from WorkFlowStateEventPermission where WorkFlowID = :WorkFlowID "
// , new object[] { workFlowID }
// , new NHibernate.Type.IType[] { NHibernateUtil.Int64 });
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(" delete from WorkFlowStateEventPermission where WorkFlowID = :WorkFlowID");
query.SetInt64("WorkFlowID", workFlowID);
query.AddScalar("Count", NHibernateUtil.Int32).UniqueResult();
}
public long GetFnPerdiemProfileCompany(long companyID)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder paramBuilder = new QueryParameterBuilder();
sqlBuilder.Append(" select PerdiemProfileID ");
sqlBuilder.Append("from FnPerdiemProfileCompany p where p.CompanyID = :CompanyID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetInt64("CompanyID", companyID);
paramBuilder.FillParameters(query);
query.AddScalar("PerdiemProfileID", NHibernateUtil.Int64);
return(query.List <long>().FirstOrDefault());
}
public IList <DbPBCurrency> FindPBCurrencyByPBID(long pbID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("select ID,PBID,CurrencyID,UpdBy,UpdDate,CreBy,CreDate,UpdPgm from DbPBCurrency where PBID = :pbID");
query.SetInt64("pbID", pbID);
query.AddScalar("ID", NHibernateUtil.Int64);
query.AddScalar("PBID", NHibernateUtil.Int64);
query.AddScalar("CurrencyID", NHibernateUtil.Int16);
query.AddScalar("UpdBy", NHibernateUtil.Int64);
query.AddScalar("UpdDate", NHibernateUtil.DateTime);
query.AddScalar("CreBy", NHibernateUtil.Int64);
query.AddScalar("CreDate", NHibernateUtil.DateTime);
query.AddScalar("UpdPgm", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPBCurrency)));
return(query.List <DbPBCurrency>());;
}
public SuUserFavoriteActor FindUserFavoriteByFavoriteId(long fid)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("SELECT * FROM SuUserFavoriteActor WHERE [UserFavoriteActorID] = :fid ");
query.SetInt64("fid", fid);
query.AddEntity(typeof(SuUserFavoriteActor));
IList <SuUserFavoriteActor> user = query.List <SuUserFavoriteActor>();
if (user.Count > 0)
{
return(user[0]);
}
else
{
return(null);
}
}
public void HqlAsBoundary()
{
IList results = session
.CreateQuery("select l.Id, NHSP.AsBinary(l.Geometry) from LineStringEntity as l where l.Geometry is not null")
.List();
ISQLQuery query = this.SqlAsBinaryLineString(session);
foreach (object[] item in results)
{
long id = (long)item[0];
byte[] wkb = (byte[])item[1];
query.SetInt64(0, id);
byte[] expected = query.UniqueResult <byte[]>();
Assert.AreEqual(expected, wkb);
}
}
public void HqlIsSimple()
{
IList results = session
.CreateQuery("select l.Id, NHSP.IsSimple(l.Geometry) from LineStringEntity as l where l.Geometry is not null")
.List();
ISQLQuery query = this.SqlIsSimpleLineString(session);
foreach (object[] item in results)
{
long id = (long)item[0];
bool isSimple = (bool)item[1];
query.SetInt64(0, id);
bool expected = query.UniqueResult <bool>();
Assert.AreEqual(expected, isSimple);
}
}
public SuUserRole FindUserRoleByUserRoleId(long id)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("SELECT * FROM SuUserRole WHERE [ID] = :id ");
query.SetInt64("id", id);
query.AddEntity(typeof(SuUserRole));
IList <SuUserRole> user = query.List <SuUserRole>();
if (user.Count > 0)
{
return(user[0]);
}
else
{
return(null);
}
}
public IList <TranslatedListItem> GetServiceTeamListItemByUserID(long userID, IList <short> userRole)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" SELECT dst.serviceteamid AS strID,");
sqlBuilder.AppendLine(" '[ ' + dst.serviceteamcode + ' ] ' + dst.description AS strSymbol ");
sqlBuilder.AppendLine(" FROM DbServiceTeam dst ");
if (userID != 0)
{
sqlBuilder.AppendLine(" INNER JOIN SuRoleService srs ");
sqlBuilder.AppendLine(" ON dst.ServiceTeamID = srs.ServiceTeamID ");
sqlBuilder.AppendLine(" INNER JOIN SuUserRole sur ");
sqlBuilder.AppendLine(" ON sur.RoleID = srs.RoleID ");
sqlBuilder.AppendLine(" INNER JOIN SuUser su ");
sqlBuilder.AppendLine(" ON su.UserID = sur.UserID ");
sqlBuilder.AppendLine(" INNER JOIN SuRole sr ");
sqlBuilder.AppendLine(" ON sr.RoleID = sur.RoleID ");
}
sqlBuilder.AppendLine(" WHERE dst.Active = 1");
if (userID != 0)
{
sqlBuilder.AppendLine(" AND su.UserID = :UserID ");
sqlBuilder.AppendLine(" AND sr.RoleID in (:UserRoleList) ");
sqlBuilder.AppendLine(" GROUP BY dst.ServiceTeamID,dst.ServiceTeamCode,dst.Description ");
}
//sqlBuilder.AppendLine(" ORDER BY strID");
sqlBuilder.AppendLine(" ORDER BY strSymbol");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
if (userID != 0)
{
query.SetInt64("UserID", userID);
query.SetParameterList("UserRoleList", userRole);
}
query.AddScalar("strID", NHibernateUtil.String);
query.AddScalar("strSymbol", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>());
}
public void HqlIsEmpty()
{
IList results = Session
.CreateQuery(
"select l.Id, NHSP.IsEmpty(l.Geometry) from LineStringEntity as l where l.Geometry is not null")
.List();
ISQLQuery query = SqlIsEmptyLineString(Session);
foreach (object[] item in results)
{
var id = (long)item[0];
var isEmpty = (bool)item[1];
query.SetInt64(0, id);
var expected = query.UniqueResult <bool>();
Assert.AreEqual(expected, isEmpty);
}
}
public IList <DbPBCurrency> FindPBLocalCurrencyByPBID(long pbID, string currencyType)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("select PBCurrency.ID,PBCurrency.PBID,Currency.CurrencyID,Currency.Symbol,PBCurrency.UpdBy,PBCurrency.UpdDate,PBCurrency.CreBy,PBCurrency.CreDate,PBCurrency.UpdPgm from DbPBCurrency as PBCurrency inner join DbCurrency as Currency on PBCurrency.CurrencyID = Currency.CurrencyID where PBCurrency.PBID = :pbID");
query.SetInt64("pbID", pbID);
query.AddScalar("ID", NHibernateUtil.Int64);
query.AddScalar("PBID", NHibernateUtil.Int64);
query.AddScalar("CurrencyID", NHibernateUtil.Int16);
query.AddScalar("Symbol", NHibernateUtil.String);
query.AddScalar("UpdBy", NHibernateUtil.Int64);
query.AddScalar("UpdDate", NHibernateUtil.DateTime);
query.AddScalar("CreBy", NHibernateUtil.Int64);
query.AddScalar("CreDate", NHibernateUtil.DateTime);
query.AddScalar("UpdPgm", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPBCurrency)));
return(query.List <DbPBCurrency>());;
}
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 IList <TranslatedListItem> GetPBLangByCriteria(short languageID, long UserID, IList <short> userRole)
{
StringBuilder sqlbuilder = new StringBuilder();
sqlbuilder.AppendLine(" select pb.pbid as strID,pbl.description as strSymbol,pb.pbCode as strCode");
sqlbuilder.AppendLine(" from dbpb pb inner join dbpblang pbl");
sqlbuilder.AppendLine(" on pb.pbid = pbl.pbid");
sqlbuilder.AppendLine(" and pbl.languageID = :languageID ");
sqlbuilder.AppendLine(" and pbl.active = 1 ");
if (UserID != 0)
{
sqlbuilder.AppendLine(" inner join SuRolePB srp ");
sqlbuilder.AppendLine(" on pb.pbid = srp.pbid ");
sqlbuilder.AppendLine(" inner join SuUserRole sur ");
sqlbuilder.AppendLine(" on sur.RoleID = srp.RoleID ");
sqlbuilder.AppendLine(" inner join SuUser su ");
sqlbuilder.AppendLine(" on su.UserID = sur.UserID ");
sqlbuilder.AppendLine(" inner join SuRole sr ");
sqlbuilder.AppendLine(" on sr.RoleID = sur.RoleID ");
sqlbuilder.AppendLine(" where su.Userid = :UserID ");
sqlbuilder.AppendLine(" AND sr.RoleID in (:UserRoleList) ");
sqlbuilder.AppendLine(" Group By pb.pbid ,pbl.description,pb.pbCode ");
}
sqlbuilder.AppendLine(" ORDER BY strID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlbuilder.ToString());
if (UserID != 0)
{
query.SetInt64("UserID", UserID);
query.SetParameterList("UserRoleList", userRole);
}
query.SetInt16("languageID", languageID);
query.AddScalar("strID", NHibernateUtil.String);
query.AddScalar("strSymbol", NHibernateUtil.String);
query.AddScalar("strCode", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>());
}
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 IList <DocumentAttachment> GetDocumentAttachmentByDocumentID(long documentID)
{
//ICriteria criteria = GetCurrentSession().CreateCriteria(typeof(DocumentAttachment),"a");
//criteria.Add(Expression.And(Expression.Eq("a.DocumentID.DocumentID",documentID),Expression.Eq("a.Active",true)));
//return criteria.List<DocumentAttachment>();
string sql = @"select * from DocumentAttachment with (nolock) where Active = 1 and DocumentID = :DocumentID";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
query.SetInt64("DocumentID", documentID);
query.AddScalar("AttachmentID", NHibernateUtil.Int64);
query.AddScalar("AttachFileName", NHibernateUtil.String);
query.AddScalar("AttachFilePath", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("CreBy", NHibernateUtil.Int64);
query.AddScalar("CreDate", NHibernateUtil.DateTime);
query.AddScalar("UpdBy", NHibernateUtil.Int64);
query.AddScalar("UpdDate", NHibernateUtil.DateTime);
query.AddScalar("UpdPgm", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DocumentAttachment))).List <DocumentAttachment>());
}
public IList <SuProgramRole> FindByProgramCode_UserID(string programCode, long userID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(@"
select SuProgramRole.*
from SuProgramRole
inner join SuUserRole
on SuProgramRole.RoleID = SuUserRole.RoleID
inner join SuProgram
on SuProgramRole.ProgramID = SuProgram.ProgramID
where SuProgram.ProgramCode = :ProgramCode
and SuUserRole.UserID = :UserID
");
//and SuProgramRole.Active = 1
//and SuUserRole.Active = 1
query.SetString("ProgramCode", programCode);
query.SetInt64("UserID", userID);
query.AddEntity(typeof(SuProgramRole));
return(query.List <SuProgramRole>());
}
public int FindCountSuRoleLangSearchResult(SuRoleLangSearchResult roleLangSearchResult, short languageID, string roleName, long userID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT Count(*) as Count ");
strQuery.AppendLine(" FROM SuRole r ");
strQuery.AppendLine(" INNER JOIN SuRoleLang rl ");
strQuery.AppendLine(" ON rl.RoleID = r.RoleID AND rl.LanguageID = :languageID ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ");
strQuery.AppendLine(" ON lang.LanguageID = :languageID ");
strQuery.AppendLine(" WHERE rl.RoleName LIKE :roleName ");
strQuery.AppendLine(" AND r.Active = 'true' ");
strQuery.AppendLine(" AND r.RoleID NOT IN ");
strQuery.AppendLine(" (SELECT ur.RoleID FROM SuUserRole ur WHERE ur.UserID = :userID) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("languageID", languageID);
query.SetString("roleName", "%" + roleName + "%");
query.SetInt64("userID", userID);
query.AddScalar("Count", NHibernateUtil.Int32);
return Convert.ToInt32(query.UniqueResult());
}