public ISQLQuery FindReasonByDocumentTypeLanguageId(string documentType, short languageId, bool isCount, string sortExpression)
{
StringBuilder strQuery = new StringBuilder();
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
ISQLQuery query;
if (isCount)
{
strQuery.Append(" select count(*) as Count ");
strQuery.Append(" from DbReason r ");
strQuery.Append(" inner join DbReasonLang rl on r.ReasonID = rl.ReasonID and rl.LanguageID = :LanguageID ");
strQuery.Append(" where r.DocumentTypeCode = :DocumentTypeCode ");
parameterBuilder.AddParameterData("LanguageID", typeof(short), languageId);
parameterBuilder.AddParameterData("DocumentTypeCode", typeof(string), documentType);
query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
strQuery.Append(" select r.ReasonID as ReasonID, r.ReasonCode as ReasonCode, r.DocumentTypeCode as DocumentTypeCode, r.Comment as Comment, r.Active as Active ");
strQuery.Append(" from DbReason r ");
strQuery.Append(" left join DbReasonLang rl on r.ReasonID = rl.ReasonID and rl.LanguageID = :LanguageID ");
strQuery.Append(" where r.DocumentTypeCode = :DocumentTypeCode ");
if (string.IsNullOrEmpty(sortExpression))
{
strQuery.Append(" order by r.ReasonID, r.ReasonCode, r.Comment, r.Active ");
}
else
{
strQuery.Append(String.Format(" order by {0} ", sortExpression));
}
parameterBuilder.AddParameterData("LanguageID", typeof(short), languageId);
parameterBuilder.AddParameterData("DocumentTypeCode", typeof(string), documentType);
query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("ReasonID", NHibernateUtil.Int16)
.AddScalar("ReasonCode", NHibernateUtil.String)
.AddScalar("DocumentTypeCode", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbScgReason)));
}
return(query);
}
public SapInstanceData GetSAPInstanceByCompanyCode(string companyCode)
{
/*N-Fix Query*/
string sqlCommand = @"SELECT sap.AliasName, sap.SystemID, sap.Client, sap.UserName ,sap.Password, sap.Language, sap.SystemNumber, sap.MsgServerHost, sap.LogonGroup
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("AliasName", NHibernateUtil.String);
query.AddScalar("SystemID", NHibernateUtil.String);
query.AddScalar("Client", NHibernateUtil.String);
query.AddScalar("UserName", NHibernateUtil.String);
query.AddScalar("Password", NHibernateUtil.String);
query.AddScalar("Language", NHibernateUtil.String);
query.AddScalar("SystemNumber", NHibernateUtil.String);
query.AddScalar("MsgServerHost", NHibernateUtil.String);
query.AddScalar("LogonGroup", NHibernateUtil.String);
IList <SapInstanceData> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SapInstanceData))).List <SapInstanceData>();
if (list.Count <= 0)
{
return(null);
}
else
{
return(list.ElementAt <SapInstanceData>(0));
}
}
public IList <SS.DB.DTO.ValueObject.ProvinceLang> FindByProvinceId(short provinceId)
{
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT ");
sql.Append(" DbProvinceLang.ID , ");
sql.Append(" DbProvinceLang.ProvinceId , ");
sql.Append(" DbProvinceLang.ProvinceName , ");
sql.Append(" DbProvinceLang.Comment , ");
sql.Append(" DbProvinceLang.Active , ");
sql.Append(" DbLanguage.LanguageId , ");
sql.Append(" DbLanguage.LanguageName ");
sql.Append(" FROM DbLanguage");
sql.Append(" LEFT JOIN DbProvinceLang ON DbLanguage.LanguageId = DbProvinceLang.LanguageId AND DbProvinceLang.ProvinceID = :ProvinceId ");
sql.Append(" LEFT JOIN DbProvince ON DbProvinceLang.ProvinceID = DbProvince.ProvinceID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ProvinceId", typeof(Int16), provinceId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("ProvinceId", NHibernateUtil.Int16);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("ProvinceName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("LanguageName", NHibernateUtil.String);
IList <ProvinceLang> list =
query.SetResultTransformer(Transformers.AliasToBean(typeof(ProvinceLang))).List <ProvinceLang>();
return(list);
}
public IList <VOReasonLang> FindReasonLangByReasonId(short reasonId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select l.LanguageID as LanguageID, l.LanguageName as LanguageName, rl.ReasonID as ReasonID, ");
sqlBuilder.Append(" rl.ReasonDetail as ReasonDetail, rl.Comment as Comment,rl.Active as Active");
sqlBuilder.Append(" from DbLanguage l ");
sqlBuilder.Append(" left join DbReasonLang rl on l.LanguageID = rl.LanguageID and rl.ReasonID = :ReasonID ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("ReasonID", typeof(short), reasonId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("ReasonID", NHibernateUtil.Int16)
.AddScalar("ReasonDetail", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VOReasonLang))).List <VOReasonLang>());
}
public IList <RoleLang> FindByRoleId(short roleId)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select rl.ID as RoleLangId, rl.RoleId as RoleId, rl.RoleName as RoleName, rl.Comment as Comment, rl.Active as Active,l.LanguageId as LanguageId, l.LanguageName as LanguageName ");
sql.Append("from DbLanguage as l ");
sql.Append("left join SuRoleLang as rl on l.LanguageId = rl.LanguageId and rl.RoleId = :RoleId ");
sql.Append("left join SuRole as r on rl.RoleId = r.RoleId");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("RoleId", typeof(Int16), roleId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("RoleLangId", NHibernateUtil.Int16);
query.AddScalar("RoleId", NHibernateUtil.Int16);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("RoleName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("LanguageName", NHibernateUtil.String);
IList <SS.SU.DTO.ValueObject.RoleLang> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.SU.DTO.ValueObject.RoleLang)))
.List <SS.SU.DTO.ValueObject.RoleLang>();
return(list);
}
//public IList<SuDivision> FindByOrganization(SuOrganization org)
//{
// return GetCurrentSession().CreateQuery("from SuDivision div where div.Organization.OrganizationID = :OrganizationID")
// .SetInt16("OrganizationID", org.Organizationid)
// .List<SuDivision>();
//}
public ISQLQuery FindByOrganizationCriteria(SuOrganization org, bool isCount)
{
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append("select div.DivisionId as Divisionid, div.Comment as Comment from SuDivision div where div.OrganizationID = :OrganizationId");
}
else
{
sqlBuilder.Append("select count(div.DivisionId) as DivisionCount from SuDivision div where div.OrganizationID = :OrganizationId");
}
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("OrganizationId", typeof(short), org.Organizationid);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("Divisionid", NHibernateUtil.Int16)
.AddScalar("Comment", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.SU.DTO.SuDivision)));
}
else
{
query.AddScalar("DivisionCount", NHibernateUtil.Int32);
}
return(query);
}
public IList <AnnouncementLang> FindAnnouncementLangByAnnouncementId(short announcementId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT l.LanguageId as LanguageId, l.LanguageName as LanguageName, al.AnnouncementId as AnnouncementId, ");
sqlBuilder.Append(" al.Id as AnnouncementLangId, al.AnnouncementHeader as AnnouncementHeader, al.Comment as Comment, al.Active as Active");
sqlBuilder.Append(" FROM DbLanguage l LEFT JOIN SuAnnouncementLang al on al.LanguageId = l.LanguageId and al.AnnouncementId = :announcementId");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("announcementId", typeof(short), announcementId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("AnnouncementId", NHibernateUtil.Int16)
.AddScalar("AnnouncementLangId", NHibernateUtil.Int64)
.AddScalar("AnnouncementHeader", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(AnnouncementLang))).List <AnnouncementLang>());
}
public short FindMenuMainIDByProgramID(short programID)
{
StringBuilder sql = new StringBuilder();
QueryParameterBuilder parameter = new QueryParameterBuilder();
sql.Append("SELECT MenuMainID ");
sql.Append("FROM SuMenu ");
sql.Append("WHERE ProgramID = :programID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
parameter.AddParameterData("programID", typeof(short), programID);
parameter.FillParameters(query);
query.AddScalar("MenuMainID", NHibernateUtil.Int16);
query.SetResultTransformer(Transformers.AliasToBean(typeof(MenuPath)));
IList <MenuPath> list = query.List <MenuPath>();
if (list.Count > 0)
{
return(list[0].MenuMainID.Value);
}
else
{
return(0);
}
}
public IList <AccountLang> FindAccountLangByAccountID(long accountID)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT DbLanguage.LanguageID as LanguageId , DbLanguage.LanguageName as LanguageName ,");
sqlBuilder.Append(" DbAccountLang.AccountLangID as AccountLangID , ");
sqlBuilder.Append(" DbAccountLang.AccountID as AccountID , DbAccountLang.AccountName as AccountName , DbAccountLang.Comment as Comment , ");
sqlBuilder.Append(" DbAccountLang.Active as Active ");
sqlBuilder.Append(" FROM DbLanguage LEFT JOIN ");
sqlBuilder.Append(" DbAccountLang ON DbLanguage.LanguageID = DbAccountLang.LanguageID and DbAccountLang.AccountID = :accountID ");
queryParameterBuilder.AddParameterData("accountID", typeof(long), accountID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("AccountLangID", NHibernateUtil.Int64)
.AddScalar("AccountID", NHibernateUtil.Int64)
.AddScalar("AccountName", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(AccountLang)));
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(AccountLang))).List <AccountLang>());;
}
public IList <PerdiemRateValObj> GetPerdiemRateByRequesterID(long requesterId)
{
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 ");
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);
query.SetResultTransformer(Transformers.AliasToBean(typeof(PerdiemRateValObj)));
IList <PerdiemRateValObj> ilistquery = query.List <PerdiemRateValObj>();
return(ilistquery);
}
public IList <PerdiemRateValObj> FindByPerdiemProfileID(long perdiemProfileID, short languageID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select f.PerdiemRateID as PerdiemRateID ,f.PerdiemProfileID as PerdiemProfileID , f.ZoneID as ZoneID , zl.ZoneName as ZoneName , f.PersonalLevel as PersonalLevel , f.ExtraPerdiemRate as ExtraPerdiemRate , f.OfficialPerdiemRate as OfficialPerdiemRate , f.InternationalStaffPerdiemRate as InternationalStaffPerdiemRate ,f.SCGStaffPerdiemRate as SCGStaffPerdiemRate , f.Active as Active ");
sqlBuilder.Append(" from FnPerdiemRate f");
sqlBuilder.Append(" inner join DbZone as z on f.ZoneID = z.ZoneID ");
sqlBuilder.Append("left join DbZoneLang as zl on z.ZoneID = zl.ZoneID and zl.LanguageID =:LanguageID ");
sqlBuilder.Append(" where f.PerdiemProfileID = :perdiemProfileID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("perdiemProfileID", typeof(long), perdiemProfileID);
queryParameterBuilder.AddParameterData("LanguageID", typeof(Int16), languageID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("PerdiemRateID", NHibernateUtil.Int64);
query.AddScalar("PerdiemProfileID", NHibernateUtil.Int64);
query.AddScalar("ZoneID", NHibernateUtil.Int16);
query.AddScalar("ZoneName", NHibernateUtil.String);
query.AddScalar("PersonalLevel", NHibernateUtil.String);
query.AddScalar("ExtraPerdiemRate", NHibernateUtil.Double);
query.AddScalar("OfficialPerdiemRate", NHibernateUtil.Double);
query.AddScalar("InternationalStaffPerdiemRate", NHibernateUtil.Double);
query.AddScalar("SCGStaffPerdiemRate", NHibernateUtil.Double);
query.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(PerdiemRateValObj))).List <PerdiemRateValObj>());
}
public IList <ServiceTeamInformation> GetServiceTeamInformation(short roleID, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
sqlBuilder.Append("SELECT srs.roleserviceid AS RoleServiceID,");
sqlBuilder.Append("dst.serviceteamcode AS ServiceCode,");
sqlBuilder.Append("dst.description AS ServiceName ");
sqlBuilder.Append("FROM suroleservice srs,dbserviceteam dst ");
sqlBuilder.Append("WHERE srs.active=1 ");
sqlBuilder.Append("AND srs.roleid = :roleID ");
sqlBuilder.Append("AND srs.serviceteamid = dst.serviceteamid ");
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(" ORDER BY RoleServiceID, ServiceCode, ServiceName");
}
else
{
sqlBuilder.Append(" ORDER BY " + sortExpression);
}
parameterBuilder.AddParameterData("roleID", typeof(Int16), roleID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("RoleServiceID", NHibernateUtil.Int64);
query.AddScalar("ServiceCode", NHibernateUtil.String);
query.AddScalar("ServiceName", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ServiceTeamInformation)));
return(query.List <ServiceTeamInformation>());
}
public DbTax GetCompanyTaxRateByCompany(long taxID, long companyID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" Select Case When ISNULL(DbCompanyTax.UseParentRate,1) = 1 then ");
sqlBuilder.Append(" Convert(varchar(30),DbTax.Rate) ");
sqlBuilder.Append(" else Convert(varchar(30),DbCompanyTax.Rate) ");
sqlBuilder.Append(" end as Rate,");
sqlBuilder.Append(" Case When ISNULL(DbCompanyTax.UseParentRate,1) = 1 then ");
sqlBuilder.Append(" Convert(varchar(30),DbTax.RateNonDeduct) ");
sqlBuilder.Append(" else Convert(varchar(30),DbCompanyTax.RateNonDeduct) ");
sqlBuilder.Append(" end as RateNonDeduct ");
sqlBuilder.Append(" From DbTax ");
sqlBuilder.Append(" Left Join DbCompanyTax ");
sqlBuilder.Append(" on DbTax.TaxID = DbCompanyTax.TaxID and DbCompanyTax.CompanyID = :companyID");
sqlBuilder.Append(" Where DbTax.Active = 1 ");
sqlBuilder.Append(" and (ISNULL(DbCompanyTax.Disable,0) <> 1 or DbTax.ApplyAllCompany = 1) ");
sqlBuilder.Append(" and DbTax.TaxID = :taxID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("companyID", typeof(string), companyID);
queryParameterBuilder.AddParameterData("taxID", typeof(string), taxID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("Rate", NHibernateUtil.Double)
.AddScalar("RateNonDeduct", NHibernateUtil.Double);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbTax))).UniqueResult <DbTax>());
}
public DbCompany getDbCompanyByCompanyCode(string companyCode)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT com.CompanyID, com.UseEcc, com.CompanyCode, com.SapCode, comEx.MileageProfileId FROM DbCompany com INNER JOIN DbCompanyEx comEx ON com.CompanyCode = comEx.CompanyCode WHERE com.CompanyCode = :companycode");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("companycode", typeof(string), companyCode);
parameterBuilder.FillParameters(query);
query.AddScalar("CompanyID", NHibernateUtil.Int64);
query.AddScalar("UseEcc", NHibernateUtil.Boolean);
query.AddScalar("CompanyCode", NHibernateUtil.String);
query.AddScalar("SapCode", NHibernateUtil.String);
query.AddScalar("MileageProfileId", NHibernateUtil.Guid);
IList <DbCompany> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbCompany))).List <DbCompany>();
if (list.Count <= 0)
{
return(null);
}
else
{
return(list.ElementAt <DbCompany>(0));
}
}
public DbCompany GetDbCompanyByCriteria(string companyCode, bool?useECC, bool?flagActive)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT CompanyID From DbCompany WHERE CompanyCode = :companycode ");
if (useECC.HasValue)
{
sql.Append(" and UseEcc = :useECC ");
parameterBuilder.AddParameterData("useECC", typeof(bool), useECC.Value ? true : false);
}
if (flagActive.HasValue)
{
sql.Append(" and Active = :flagActive ");
parameterBuilder.AddParameterData("flagActive", typeof(bool), flagActive);
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
parameterBuilder.AddParameterData("companycode", typeof(string), companyCode);
parameterBuilder.FillParameters(query);
query.AddScalar("CompanyID", NHibernateUtil.Int64);
IList <DbCompany> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbCompany))).List <DbCompany>();
if (list.Count <= 0)
{
return(null);
}
else
{
return(list.ElementAt <DbCompany>(0));
}
}
public IList <InitiatorData> GetDocumentInitiatorList(long documentID)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
string sqlCommand = @"SELECT DocumentInitiator.Seq as InitiatorSeq, SuUser.EmployeeName as InitiatorName, SuUser.Email as InitiatorEmail,
(case DocumentInitiator.InitiatorType when '1' then 'Accept' when '2' then 'CC' end) as InitialType, ISNULL(SuUser.SMSApproveOrReject,0) as IsSMS
FROM DocumentInitiator
INNER JOIN SuUser ON DocumentInitiator.UserID = SuUser.UserID
WHERE DocumentInitiator.DocumentID = :DocumentID
Order By DocumentInitiator.Seq";
parameterBuilder.AddParameterData("DocumentID", typeof(long), documentID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlCommand);
parameterBuilder.FillParameters(query);
query.AddScalar("InitiatorSeq", NHibernateUtil.Int16)
.AddScalar("InitiatorName", NHibernateUtil.String)
.AddScalar("InitiatorEmail", NHibernateUtil.String)
.AddScalar("InitialType", NHibernateUtil.String)
.AddScalar("IsSMS", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(InitiatorData))).List <InitiatorData>());
// query.AddEntity(typeof(DocumentInitiatorLang));
//return query.List<DocumentInitiatorLang>();
}
public IList <AdvanceData> FindAdvanceDataByExpenseID(long expenseID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select distinct Document.DocumentNo , AvAdvanceDocument.RequestDateOfAdvance, AvAdvanceDocument.Amount ");
sqlBuilder.Append(" from FnExpenseDocument left outer join ");
sqlBuilder.Append(" FnExpenseAdvance on FnExpenseDocument.ExpenseID = FnExpenseAdvance.ExpenseID left outer join ");
sqlBuilder.Append(" AvAdvanceDocument on FnExpenseAdvance.AdvanceID = AvAdvanceDocument.AdvanceID left outer join ");
sqlBuilder.Append(" Document on AvAdvanceDocument.DocumentID = Document.DocumentID ");
sqlBuilder.Append(" WHERE FnExpenseDocument.ExpenseID = :ExpenseID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ExpenseID", typeof(long), expenseID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("RequestDateOfAdvance", NHibernateUtil.DateTime);
query.AddScalar("Amount", NHibernateUtil.Double);
query.SetResultTransformer(Transformers.AliasToBean(typeof(AdvanceData)));
return(query.List <AdvanceData>());
}
/// <summary>
/// query for auto complete
/// </summary>
/// <param name="taxNo"></param>
/// <returns></returns>
public IList <DbVendor> FindByDbVendorAutoComplete(string taxNo)
{
taxNo = "%" + taxNo + "%";
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select VendorID, VendorCode,VendorTitle, Street, City,Country, PostalCode,");
sqlBuilder.Append(" (case when TaxNo1 is null and TaxNo2 is not null then VendorName2 ");
sqlBuilder.Append(" when TaxNo1 is not null and TaxNo2 is null then VendorName1 ");
sqlBuilder.Append(" else VendorName2 end) as VendorName1 ");
sqlBuilder.Append(" ,(case when TaxNo1 is null and TaxNo2 is not null then TaxNo2 ");
sqlBuilder.Append(" when TaxNo1 is not null and TaxNo2 is null then TaxNo1 ");
sqlBuilder.Append(" else TaxNo2 end) as TaxNo1 ");
sqlBuilder.Append(" FROM DbVendor ");
sqlBuilder.Append(" WHERE (ISNULL(TaxNo1,'') like :taxNo) or (ISNULL(TaxNo2,'') like :taxNo)");
//sqlBuilder.Append(" AND (TaxNo1 is not null or TaxNo2 is not null)");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("taxNo", typeof(string), taxNo);
queryParameterBuilder.FillParameters(query);
query.AddScalar("VendorID", NHibernateUtil.Int64);
query.AddScalar("VendorCode", NHibernateUtil.String);
query.AddScalar("VendorTitle", NHibernateUtil.String);
query.AddScalar("VendorName1", NHibernateUtil.String);
query.AddScalar("TaxNo1", NHibernateUtil.String);
query.AddScalar("Street", NHibernateUtil.String);
query.AddScalar("City", NHibernateUtil.String);
query.AddScalar("Country", NHibernateUtil.String);
query.AddScalar("PostalCode", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbVendor))).List <DbVendor>());
}
public IList <MenuLang> FindMenuLangByTranslateId(short menuId)
{
StringBuilder sqlBuilder = new StringBuilder();
//sqlBuilder.Append("select l.LanguageId as LanguageId, l.LanguageName as LanguageName, tl.TranslateId as TranslateId,");
//sqlBuilder.Append(" tl.Id as TranslateLangId, tl.TranslateWord as TranslateWord, tl.Comment as Comment, tl.Active as Active");
//sqlBuilder.Append(" from DbLanguage l left join SuGlobalTranslateLang tl on tl.LanguageId = l.LanguageId and tl.translateId = :TranslateId");
ISQLQuery query;
sqlBuilder.AppendLine(" select l.LanguageId as LanguageId ");
sqlBuilder.AppendLine(" ,l.LanguageName as LanguageName ");
sqlBuilder.AppendLine(" ,m.menuId as MenuId ");
sqlBuilder.AppendLine(" ,m.Id as MenuLangId ");
sqlBuilder.AppendLine(" ,m.menuName as MenuName ");
sqlBuilder.AppendLine(" ,m.Comment as Comment ");
sqlBuilder.AppendLine(" ,m.Active as Active");
sqlBuilder.AppendLine(" from DbLanguage l ");
sqlBuilder.AppendLine(" left outer join SuMenuLang m ");
sqlBuilder.AppendLine(" on l.LanguageId = m.LanguageId ");
sqlBuilder.AppendLine(" and m.menuId = :MenuID ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("MenuID", typeof(long), menuId);
query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("MenuId", NHibernateUtil.Int16)
.AddScalar("MenuLangId", NHibernateUtil.Int16)
.AddScalar("MenuName", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.MenuLang))).List <MenuLang>());
}
public IList <DbVendor> FindByDbVendor(long vendorID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT VendorID,VendorCode,VendorTitle,VendorName1,VendorName2,TaxNo1,TaxNo2,Street, City,Country, PostalCode");
sqlBuilder.Append(" FROM DbVendor ");
sqlBuilder.Append(" WHERE (VendorID = :vendorID) ");
sqlBuilder.Append(" AND (TaxNo1 is not null or TaxNo2 is not null)");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("vendorID", typeof(string), vendorID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("VendorID", NHibernateUtil.Int64);
query.AddScalar("VendorCode", NHibernateUtil.String);
query.AddScalar("VendorTitle", NHibernateUtil.String);
query.AddScalar("VendorName1", NHibernateUtil.String);
query.AddScalar("VendorName2", NHibernateUtil.String);
query.AddScalar("TaxNo1", NHibernateUtil.String);
query.AddScalar("TaxNo2", NHibernateUtil.String);
query.AddScalar("Street", NHibernateUtil.String);
query.AddScalar("City", NHibernateUtil.String);
query.AddScalar("Country", NHibernateUtil.String);
query.AddScalar("PostalCode", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbVendor))).List <DbVendor>());
}
public IList <AnnouncementLang> FindByDateAnnouncementLangId(short announcementId, short languageId)
{
DateTime dateTime = DateTime.Now.Date;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select agl.AnnouncementGroupName,al.Announcementid,AnnouncementHeader,AnnouncementBody,AnnouncementFooter,ImagePath");
sqlBuilder.Append(" from SuAnnouncementLang al inner join SuAnnouncement a ");
sqlBuilder.Append(" on a.AnnouncementID = al.AnnouncementID inner join ");
sqlBuilder.Append(" SuAnnouncementGroupLang agl on a.AnnouncementGroupID = agl.AnnouncementGroupID");
sqlBuilder.Append(" inner join SuAnnouncementGroup ag on agl.AnnouncementGroupID = ag.AnnouncementGroupID");
sqlBuilder.Append(" where al.Announcementid = :announcementId and al.Languageid = :languageId");
sqlBuilder.Append(" and agl.Languageid = :languageId and a.EffectiveDate <= :dateTime ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("announcementId", typeof(short), announcementId);
parameterBuilder.AddParameterData("languageId", typeof(short), languageId);
parameterBuilder.AddParameterData("dateTime", typeof(DateTime), dateTime);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("AnnouncementGroupName", NHibernateUtil.String)
.AddScalar("AnnouncementId", NHibernateUtil.Int16)
.AddScalar("AnnouncementHeader", NHibernateUtil.String)
.AddScalar("AnnouncementBody", NHibernateUtil.String)
.AddScalar("AnnouncementFooter", NHibernateUtil.String)
.AddScalar("ImagePath", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(AnnouncementLang))).List <AnnouncementLang>());
}
public IList <InvoiceDataForEmail> FindInvoiceDataByExpenseID(long expenseID)
{
string sqlCommand = @"SELECT FnExpenseInvoice.InvoiceID, ROW_NUMBER() OVER(ORDER BY FnExpenseInvoice.CreDate ASC) AS ItemNo, FnExpenseInvoice.InvoiceNo, convert(varchar, FnExpenseInvoice.InvoiceDate,103) as InvoiceDate,
(FnExpenseInvoice.VendorCode + (case when NULLIF(FnExpenseInvoice.VendorCode,'') is null or NULLIF(FnExpenseInvoice.VendorName,'') is null then '' else ' - ' end) + FnExpenseInvoice.VendorName) as Vendor,
case when ISNULL(FnExpenseDocument.IsRepOffice,0) = 0
then convert(varchar,cast(FnExpenseInvoice.TotalBaseAmount as money),1)
else convert(varchar,cast(FnExpenseInvoice.TotalBaseAmountLocalCurrency as money),1) end as BaseAmount,
convert(varchar,cast(FnExpenseInvoice.VatAmount as money),1) as VatAmount,
convert(varchar,cast(FnExpenseInvoice.WHTAmount as money),1) as WHTAmount,
convert(varchar,cast(FnExpenseInvoice.NetAmount as money),1) as NetAmount
FROM FnExpenseDocument with (nolock)
INNER JOIN FnExpenseInvoice with (nolock) ON FnExpenseDocument.ExpenseID = FnExpenseInvoice.ExpenseID
WHERE FnExpenseInvoice.ExpenseID = :ExpenseID
order by FnExpenseInvoice.CreDate ";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlCommand);
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ExpenseID", typeof(long), expenseID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("InvoiceID", NHibernateUtil.Int64);
query.AddScalar("ItemNo", NHibernateUtil.Int32);
query.AddScalar("InvoiceNo", NHibernateUtil.String);
query.AddScalar("InvoiceDate", NHibernateUtil.String);
query.AddScalar("BaseAmount", NHibernateUtil.String);
query.AddScalar("VatAmount", NHibernateUtil.String);
query.AddScalar("WHTAmount", NHibernateUtil.String);
query.AddScalar("NetAmount", NHibernateUtil.String);
query.AddScalar("Vendor", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(InvoiceDataForEmail)));
return(query.List <InvoiceDataForEmail>());
}
public IList <VORejectReasonLang> FindRejectReasonByDocTypeIDStateEventIDAndLanguageID(int documentTypeID, int workflowStateEventID, short languageID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT r.ReasonID AS ReasonID , r.ReasonCode AS ReasonCode , rl.ReasonDetail AS ReasonDetail ");
sql.AppendLine("FROM DbRejectReason AS r ");
sql.AppendLine("INNER JOIN DbRejectReasonLang AS rl ON rl.ReasonID = r.ReasonID ");
sql.AppendLine("WHERE ISNULL(r.DocumentTypeID, :documentTypeID) = :documentTypeID AND rl.LanguageID = :languageID AND r.Active = 1 ");
sql.AppendLine("AND (r.WorkFlowStateEventID IS NULL OR r.WorkFlowStateEventID = :WorkflowStateEventID) order by ReasonCode asc");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameter = new QueryParameterBuilder();
queryParameter.AddParameterData("documentTypeID", typeof(int), documentTypeID);
queryParameter.AddParameterData("WorkflowStateEventID", typeof(int), workflowStateEventID);
queryParameter.AddParameterData("languageID", typeof(int), languageID);
queryParameter.FillParameters(query);
query.AddScalar("ReasonID", NHibernateUtil.Int32);
query.AddScalar("ReasonCode", NHibernateUtil.String);
query.AddScalar("ReasonDetail", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VORejectReasonLang))).List <VORejectReasonLang>());
}
public IList <FnExpenseInvoice> GetInvoiceByExpenseID(long expenseId)
{
string sql = @" SELECT * FROM FnExpenseInvoice with (nolock) where ExpenseID = :ExpenseID and Active = 1";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ExpenseID", typeof(long), expenseId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("InvoiceID", NHibernateUtil.Int64);
query.AddScalar("InvoiceNo", NHibernateUtil.String);
query.AddScalar("InvoiceDocumentType", NHibernateUtil.String);
query.AddScalar("InvoiceDate", NHibernateUtil.DateTime);
query.AddScalar("VendorCode", NHibernateUtil.String);
query.AddScalar("VendorName", NHibernateUtil.String);
query.AddScalar("Street", NHibernateUtil.String);
query.AddScalar("City", NHibernateUtil.String);
query.AddScalar("Country", NHibernateUtil.String);
query.AddScalar("PostalCode", NHibernateUtil.String);
query.AddScalar("TotalAmount", NHibernateUtil.Double);
query.AddScalar("VatAmount", NHibernateUtil.Double);
query.AddScalar("WHTAmount", NHibernateUtil.Double);
query.AddScalar("NetAmount", NHibernateUtil.Double);
query.AddScalar("Description", NHibernateUtil.String);
query.AddScalar("IsVAT", NHibernateUtil.Boolean);
query.AddScalar("IsWHT", NHibernateUtil.Boolean);
query.AddScalar("TaxID", NHibernateUtil.Int64);
query.AddScalar("NonDeductAmount", NHibernateUtil.Double);
query.AddScalar("TotalBaseAmount", NHibernateUtil.Double);
query.AddScalar("WHTRate1", NHibernateUtil.Double);
query.AddScalar("WHTTypeID1", NHibernateUtil.Int64);
query.AddScalar("BaseAmount1", NHibernateUtil.Double);
query.AddScalar("WHTAmount1", NHibernateUtil.Double);
query.AddScalar("WHTRate2", NHibernateUtil.Double);
query.AddScalar("WHTTypeID2", NHibernateUtil.Int64);
query.AddScalar("BaseAmount2", NHibernateUtil.Double);
query.AddScalar("WHTAmount2", NHibernateUtil.Double);
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);
query.AddScalar("WHTID1", NHibernateUtil.Int64);
query.AddScalar("WHTID2", NHibernateUtil.Int64);
query.AddScalar("VendorTaxCode", NHibernateUtil.String);
query.AddScalar("VendorBranch", NHibernateUtil.String);
query.AddScalar("BranchCode", NHibernateUtil.String);
query.AddScalar("VendorID", NHibernateUtil.Int64);
query.AddEntity("Expense", typeof(FnExpenseDocument));
//field for Rep Office
query.AddScalar("TotalAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("TotalBaseAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("NetAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("ExchangeRateForLocalCurrency", NHibernateUtil.Double);
query.AddScalar("ExchangeRateMainToTHBCurrency", NHibernateUtil.Double);
query.SetResultTransformer(Transformers.AliasToBean(typeof(FnExpenseInvoice)));
return(query.List <FnExpenseInvoice>());
}
public IList <VOReasonLang> FindAutoComplete(string reasonDetail, string documentType, short languageId)
{
reasonDetail = "%" + reasonDetail + "%";
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select l.LanguageId as LanguageId, l.LanguageName as LanguageName, rl.ReasonId as ReasonId,r.ReasonCode as ReasonCode, ");
sqlBuilder.Append(" rl.ReasonDetail as ReasonDetail, rl.Comment as Comment,rl.Active as Active,r.DocumentTypeCode as DocumentTypeCode");
sqlBuilder.Append(" from DbReason r left join DbReasonLang rl on rl.ReasonID = r.ReasonID and rl.LanguageId = :LanguageId ");
sqlBuilder.Append(" left join DbLanguage l on l.LanguageId = :LanguageId ");
sqlBuilder.Append(" where rl.ReasonDetail Like :reasonDetail");
sqlBuilder.Append(" and r.DocumentTypeCode = :documentType");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId);
parameterBuilder.AddParameterData("reasonDetail", typeof(string), reasonDetail);
parameterBuilder.AddParameterData("documentType", typeof(string), documentType);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("ReasonID", NHibernateUtil.Int16)
.AddScalar("ReasonCode", NHibernateUtil.String)
.AddScalar("ReasonDetail", NHibernateUtil.String)
.AddScalar("DocumentTypeCode", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VOReasonLang))).List <VOReasonLang>());
}
public void InsertData(DbSellingLetterDetail sellingLetterDetail)
{
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO DbSellingLetterDetail (LetterID,LetterNo,BuyingDate,CompanyName,BankName,BankBranch,AccountType,AccountNo,CreBy,CreDate,UpdBy,UpdDate) ");
sql.Append(" VALUES ( :letterID , :letterNo , :buyingDate, :companyName, :bankName, :bankBranch, :accountType, :accountNo, :creBy, :creDate, :updBy, :updDate ) ");
QueryParameterBuilder param = new QueryParameterBuilder();
param.AddParameterData("letterID", typeof(long), sellingLetterDetail.LetterID);
param.AddParameterData("letterNo", typeof(long), sellingLetterDetail.LetterNo);
param.AddParameterData("buyingDate", typeof(long), sellingLetterDetail.BuyingDate);
param.AddParameterData("companyName", typeof(long), sellingLetterDetail.CompanyName);
param.AddParameterData("bankName", typeof(long), sellingLetterDetail.BankName);
param.AddParameterData("bankBranch", typeof(long), sellingLetterDetail.BankBranch);
param.AddParameterData("accountType", typeof(long), sellingLetterDetail.AccountType);
param.AddParameterData("accountNo", typeof(long), sellingLetterDetail.AccountNo);
param.AddParameterData("creBy", typeof(long), sellingLetterDetail.CreBy);
param.AddParameterData("creDate", typeof(DateTime), sellingLetterDetail.CreDate);
param.AddParameterData("updBy", typeof(long), sellingLetterDetail.UpdBy);
param.AddParameterData("updDate", typeof(DateTime), sellingLetterDetail.UpdDate);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
param.FillParameters(query);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public IList <UserRole> FindUserRoleByUserId(long userId, short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" SELECT ur.ID as UserRoleId, ur.UserID as UserId, ur.RoleId as RoleId, rl.RoleName as RoleName, ur.Comment as Comment, ur.Active as Active ");
sqlBuilder.AppendLine(" FROM SuUserRole ur INNER JOIN SuRole r on r.RoleID = ur.RoleID ");
sqlBuilder.AppendLine(" INNER JOIN SuRoleLang rl on rl.RoleID = r.RoleID and rl.LanguageID = :languageID ");
sqlBuilder.AppendLine(" WHERE ur.UserID = :userID ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("languageID", typeof(short), languageId);
parameterBuilder.AddParameterData("userID", typeof(long), userId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("UserRoleId", NHibernateUtil.Int64)
.AddScalar("UserId", NHibernateUtil.Int64)
.AddScalar("RoleId", NHibernateUtil.Int16)
.AddScalar("RoleName", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(UserRole))).List <UserRole>());
}
public IList <ProgramLang> FindByProgramId(short programId)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select pl.ID as ProgramLangId, pl.ProgramId as ProgramId, pl.ProgramsName as ProgramName, pl.Comment as Comment, pl.Active as Active,l.LanguageId as LanguageId, l.LanguageName as LanguageName ");
sql.Append("from DbLanguage as l ");
sql.Append("left join SuProgramLang as pl on l.LanguageId = pl.LanguageId and pl.programId = :ProgramId ");
sql.Append("left join Suprogram as p on pl.ProgramId = p.ProgramId");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ProgramId", typeof(Int16), programId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("ProgramLangId", NHibernateUtil.Int64);
query.AddScalar("ProgramId", NHibernateUtil.Int16);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("ProgramName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("LanguageName", NHibernateUtil.String);
IList <SS.SU.DTO.ValueObject.ProgramLang> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.SU.DTO.ValueObject.ProgramLang)))
.List <SS.SU.DTO.ValueObject.ProgramLang>();
return(list);
}
public IList <DbZoneResult> FindZoneLangByZoneId(short zoneId)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select zl.ID as Id,zl.ID as ZoneLangID,zl.ZoneName as ZoneName, zl.Comment as Comment, zl.Active as Active,l.LanguageID as LanguageID, l.LanguageName as LanguageName ");
sql.Append("from DbLanguage as l ");
sql.Append("left join DbZoneLang as zl on l.LanguageId = zl.LanguageId and zl.ZoneId = :ZoneId ");
//sql.Append("left join Suprogram as p on pl.ProgramId = p.ProgramId");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ZoneId", typeof(Int16), zoneId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("ZoneLangID", NHibernateUtil.Int16);
query.AddScalar("LanguageID", NHibernateUtil.Int16);
query.AddScalar("ZoneName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("LanguageName", NHibernateUtil.String);
IList <DbZoneResult> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbZoneResult)))
.List <DbZoneResult>();
return(list);
}
public IList <DocumentInitiatorLang> GetDocumentInitiatorByDocumentIDAndInitiatorType(long documentID)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT di.InitiatorID AS InitiatorID , u.UserID as UserID , u.EmployeeName AS EmployeeName , u.Email AS Email , u.SMS AS SMS , di.isSkip AS isSkip , di.SkipReason AS SkipReason ");
sql.Append("FROM DocumentInitiator AS di ");
sql.Append("INNER JOIN SuUser AS u ON u.UserID = di.UserID ");
sql.Append("WHERE di.DocumentID =:documentID AND InitiatorType = '1' ");
sql.Append("ORDER BY di.Seq ASC");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("documentID", typeof(long), documentID);
parameterBuilder.FillParameters(query);
query.AddScalar("InitiatorID", NHibernateUtil.Int64);
query.AddScalar("UserID", NHibernateUtil.Int64);
query.AddScalar("EmployeeName", NHibernateUtil.String);
query.AddScalar("Email", NHibernateUtil.String);
query.AddScalar("SMS", NHibernateUtil.Boolean);
query.AddScalar("isSkip", NHibernateUtil.Boolean);
query.AddScalar("SkipReason", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DocumentInitiatorLang))).List <DocumentInitiatorLang>());
}