public IList <CountryLang> GetAllCountryLangByLang(short languageId, long requesterId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select cl.CountryId as CountryId,c.CountryCode as CountryCode, ");
sqlBuilder.Append(" cl.CountryName as CountryName, cl.Comment as Comment,cl.Active as Active");
sqlBuilder.Append(" from DbCountry c left join DbCountryLang cl on c.CountryId = cl.CountryId and cl.LanguageId = :LanguageId ");
sqlBuilder.Append(" inner join FnPerdiemProfileCountry prCountry on prCountry.CountryID = c.CountryID ");
sqlBuilder.Append(" inner join FnPerdiemProfile pf on pf.PerdiemProfileID = prCountry.PerdiemProfileID ");
sqlBuilder.Append(" inner join FnPerdiemProfileCompany Pc on Pf.PerdiemProfileID = Pc.PerdiemProfileID ");
sqlBuilder.Append(" inner join SuUser u on u.CompanyID = Pc.CompanyID ");
sqlBuilder.Append(" where c.Active = 1 and u.UserID = :RequesterID ");
sqlBuilder.Append(" order by prCountry.ZoneID desc, cl.CountryName asc ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId);
parameterBuilder.AddParameterData("RequesterID", typeof(long), requesterId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
//query.AddScalar("LanguageId", NHibernateUtil.Int16);
//query.AddScalar("LanguageName", NHibernateUtil.String);
query.AddScalar("CountryID", NHibernateUtil.Int16);
query.AddScalar("CountryCode", NHibernateUtil.String);
query.AddScalar("CountryName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(CountryLang))).List <CountryLang>());
}
public IList <TranslatedListItem> FindExpenseGroupByLangCriteria(short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbExpenseGroup.ExpenseGroupID AS ID, ");
sqlBuilder.Append(" '[' + DbExpenseGroup.ExpenseGroupCode + ']' + ISNULL(DbExpenseGroupLang.Description,'') AS Symbol ");
sqlBuilder.Append(" FROM ");
sqlBuilder.Append(" DbExpenseGroup ");
sqlBuilder.Append(" INNER JOIN DbExpenseGroupLang ON ");
sqlBuilder.Append(" DbExpenseGroup.ExpenseGroupID = DbExpenseGroupLang.ExpenseGroupID ");
sqlBuilder.Append(" WHERE 1=1 AND DbExpenseGroupLang.LanguageID = :LanguageId AND DbExpenseGroup.Active = 1 ");
sqlBuilder.Append(" Order By DbExpenseGroup.ExpenseGroupCode");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LanguageId", typeof(Int16), languageId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("ID", NHibernateUtil.Int16);
query.AddScalar("Symbol", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem)));
return(query.List <TranslatedListItem>());
}
public IList <SS.SU.DTO.TranslatedListItem> FindRegionByLangCriteria(short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbRegionLang.RegionID AS Id ,");
sqlBuilder.Append(" DbRegionLang.RegionName AS Text ");
sqlBuilder.Append(" FROM ");
sqlBuilder.Append(" DbRegionLang ");
sqlBuilder.Append(" WHERE ");
sqlBuilder.Append(" DbRegionLang.LanguageID = :LanguageId ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LanguageId", typeof(Int16), languageId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("Id", NHibernateUtil.Int16);
query.AddScalar("Text", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.SU.DTO.TranslatedListItem)));
return(query.List <SS.SU.DTO.TranslatedListItem>());
}
public IList <TranslatedListItem> FindStatusLangCriteria(string groupStatus, short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbStatus.Status AS strID, ");
sqlBuilder.Append(" DbStatusLang.StatusDesc AS strSymbol ");
sqlBuilder.Append(" FROM DbStatus ");
sqlBuilder.Append(" INNER JOIN DbStatusLang ON ");
sqlBuilder.Append(" DbStatus.StatusID = DbStatusLang.StatusID ");
sqlBuilder.Append(" WHERE ");
sqlBuilder.Append(" DbStatus.GroupStatus = :GroupStatus AND ");
sqlBuilder.Append(" DbStatusLang.LanguageID = :LanguageId AND ");
sqlBuilder.Append(" DbStatus.Active = 1 ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("GroupStatus", typeof(string), groupStatus);
queryParameterBuilder.AddParameterData("LanguageId", typeof(Int16), languageId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("strID", NHibernateUtil.String);
query.AddScalar("strSymbol", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem)));
return(query.List <TranslatedListItem>());
}
public IList <ExpensesMPA> FindByExpenseMPAID(IList <long> expensesMPAIdList)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("Select distinct doc.DocumentNo, doc.Subject, doc.DocumentDate, mpaDoc.MPADocumentID , w.WorkflowID ");
sqlBuilder.AppendLine(" from Document AS doc ");
sqlBuilder.AppendLine(" LEFT JOIN MPADocument AS mpaDoc ");
sqlBuilder.AppendLine(" ON doc.DocumentID = mpaDoc.DocumentID ");
sqlBuilder.AppendLine(" LEFT JOIN MPAItem AS mpaItem ");
sqlBuilder.AppendLine(" ON mpaItem.MPADocumentID = mpaDoc.MPADocumentID ");
sqlBuilder.AppendLine(" LEFT JOIN Workflow w ON w.DocumentID = doc.DocumentID ");
sqlBuilder.AppendLine(" WHERE mpaItem.MPADocumentID IN (:expensesMPAIdList) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetParameterList("expensesMPAIdList", expensesMPAIdList);
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("DocumentDate", NHibernateUtil.Date);
query.AddScalar("MPADocumentID", NHibernateUtil.Int64);
query.AddScalar("WorkflowID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpensesMPA))).List <ExpensesMPA>());
}
public IList <Parameter> FindByDbParameterCriteria(DbParameter parameter)
{
//StringBuilder sql = new StringBuilder();
//sql.Append("Select d.ParameterValue ");
//sql.Append("from DbParameter as d ");
//sql.Append("where d.ParameterValue = :ParameterValue ");
//ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
//QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
//queryParameterBuilder.AddParameterData("ParameterValue", typeof(String), parameter.ParameterValue);
//queryParameterBuilder.FillParameters(query);
//query.AddScalar("ParameterValue", NHibernateUtil.String); ;
StringBuilder sql = new StringBuilder();
sql.Append("Select d.GroupNo, d.SeqNo ");
sql.Append("from DbParameter as d ");
sql.Append("where d.GroupNo = :GroupNo ");
sql.Append("and d.SeqNo = :SeqNo ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("GroupNo", typeof(Int16), parameter.GroupNo.GroupNo);
queryParameterBuilder.AddParameterData("SeqNo", typeof(Int16), parameter.SeqNo);
queryParameterBuilder.FillParameters(query);
query.AddScalar("GroupNo", NHibernateUtil.Int16);
query.AddScalar("SeqNo", NHibernateUtil.Int16);
IList <SS.DB.DTO.ValueObject.Parameter> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.DB.DTO.ValueObject.Parameter)))
.List <SS.DB.DTO.ValueObject.Parameter>();
return(list);
}
public ISQLQuery FindByProfileListCriteria(bool isCount)
{
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" SELECT ProfileName , Id ");
sqlBuilder.Append(" FROM MileageProfile ");
}
else
{
sqlBuilder.Append(" SELECT count(ProfileName) AS ProfileNameCount");
sqlBuilder.Append(" FROM MileageProfile ");
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
if (!isCount)
{
query.AddScalar("ProfileName", NHibernateUtil.String);
query.AddScalar("Id", NHibernateUtil.Guid);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbProfileList)));
}
else
{
query.AddScalar("ProfileNameCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public IList <WorkFlowStateEventWithLang> FindByWorkFlowStateEventByStateID(int workFlowStateID, short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT wse.WorkFlowStateEventID as EventID , wse.Name , wsel.DisplayName , wse.UserControlPath ");
strQuery.AppendLine(" FROM WorkFlowState ws ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEvent wse ");
strQuery.AppendLine(" ON ws.WorkFlowStateID = wse.WorkFlowStateID ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEventLang wsel ");
strQuery.AppendLine(" ON wse.WorkFlowStateEventID = wsel.WorkFlowStateEventID AND wsel.LanguageID = :LanguageID ");
strQuery.AppendLine(" WHERE ws.WorkFlowStateID = :WorkFlowStateID");
strQuery.AppendLine(" AND ws.Active = '1'");
strQuery.AppendLine(" AND wse.Active = '1'");
strQuery.AppendLine(" ORDER BY ws.Name ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID);
query.SetInt32("WorkFlowStateID", workFlowStateID);
query.AddScalar("EventID", NHibernateUtil.Int64);
query.AddScalar("Name", NHibernateUtil.String);
query.AddScalar("DisplayName", NHibernateUtil.String);
query.AddScalar("UserControlPath", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(WorkFlowStateEventWithLang)));
return(query.List <WorkFlowStateEventWithLang>());
}
public IList <DbPaymentMethod> FindPaymentMethodNotAdd(IList <string> paymentMethodIDList)
{
StringBuilder sqlBuilder = new StringBuilder();
ISQLQuery query = null;
sqlBuilder.Append("SELECT PaymentMethodID , PaymentMethodCode ,Active ");
sqlBuilder.Append("FROM DbPaymentMethod ");
if (paymentMethodIDList.Count == 0)
{
query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
}
else
{
sqlBuilder.Append("WHERE PaymentMethodID NOT IN (:PaymentMethodIdList) ");
query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetParameterList("PaymentMethodIdList", paymentMethodIDList);
}
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("PaymentMethodID", NHibernateUtil.Int64);
query.AddScalar("PaymentMethodCode", NHibernateUtil.String);
IList <DbPaymentMethod> dbPyamentMethodList = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPaymentMethod))).List <DbPaymentMethod>();
return(dbPyamentMethodList);
}
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 IList <GlobalTranslate> LoadProgramResources(string programCode, string languageCode)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT TranslateControl , TranslateWord ");
strQuery.AppendLine(" FROM SuGlobalTranslate g ");
strQuery.AppendLine(" INNER JOIN SuGlobalTranslateLang gl ");
strQuery.AppendLine(" ON g.TranslateID = gl.TranslateID ");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON gl.LanguageID = l.LanguageID ");
strQuery.AppendLine(" WHERE l.LanguageCode = :LanguageCode ");
strQuery.AppendLine(" AND g.ProgramCode = :ProgramCode ");
strQuery.AppendLine(" AND g.Active = 1 ");
strQuery.AppendLine(" AND gl.Active = 1 ");
strQuery.AppendLine(" AND l.Active = 1 ");
strQuery.AppendLine(" AND isnull(g.TranslateControl,'') <> '' ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("LanguageCode", languageCode);
query.SetString("ProgramCode", programCode);
query.AddScalar("TranslateControl", NHibernateUtil.String);
query.AddScalar("TranslateWord", NHibernateUtil.String);
IList <GlobalTranslate> globalTranslates = query.SetResultTransformer(Transformers.AliasToBean(typeof(GlobalTranslate))).List <GlobalTranslate>();
return(globalTranslates);
}
public ValidateMilage GetMileageItemForValidationRight(long RequesterId, string CarLicenseNo, DateTime travelDate, long expId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select TOP(1) d.CarMeterStart,d.CarMeterEnd,d.TravelDate,a.DocumentNo from Document a ");
sqlBuilder.Append(" inner join [dbo].[FnExpenseDocument] b on a.DocumentID = b.DocumentID ");
sqlBuilder.Append(" inner join [dbo].FnExpenseMileage c on b.ExpenseID = c.ExpenseID ");
sqlBuilder.Append(" inner join [dbo].FnExpenseMileageItem d on c.ExpenseMileageID = d.ExpenseMileageID ");
sqlBuilder.Append(" where a.CacheCurrentStateName <> 'Cancel' and c.[Owner] = 'EMP' ");
sqlBuilder.Append(" and a.RequesterID = :RequesterID and c.CarLicenseNo = :CarLicenseNo and d.TravelDate > :travelDate and b.ExpenseID <> :expId ");
sqlBuilder.Append(" ORDER BY d.TravelDate ASC ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("RequesterID", typeof(long), RequesterId);
queryParameterBuilder.AddParameterData("CarLicenseNo", typeof(string), CarLicenseNo);
queryParameterBuilder.AddParameterData("travelDate", typeof(DateTime), travelDate);
queryParameterBuilder.AddParameterData("expId", typeof(long), expId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("CarMeterStart", NHibernateUtil.Double);
query.AddScalar("CarMeterEnd", NHibernateUtil.Double);
query.AddScalar("TravelDate", NHibernateUtil.DateTime);
query.AddScalar("DocumentNo", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ValidateMilage))).List <ValidateMilage>().FirstOrDefault());
}
public IList GetMaxCollector(DateTime month)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(@"
select top 1 isnull(a.COLLECTOR_ID,'') COLLECTOR_ID,isnull(a.LOAN_BASIC_INSTALLMENT,0) LOAN_BASIC_INSTALLMENT, isnull(b.INSTALLMENT_BASIC,0) INSTALLMENT_BASIC
from (
select loan.COLLECTOR_ID, sum(loan.LOAN_BASIC_INSTALLMENT) LOAN_BASIC_INSTALLMENT
from dbo.T_LOAN loan
where loan.LOAN_STATUS = 'OK'
and loan.COLLECTOR_ID is not null
group by loan.COLLECTOR_ID
having sum(loan.LOAN_BASIC_INSTALLMENT) > 0 ) a
left join
(
select ins.EMPLOYEE_ID, sum(ins.INSTALLMENT_BASIC) INSTALLMENT_BASIC
from dbo.T_INSTALLMENT ins
where ins.INSTALLMENT_MATURITY_DATE >= :startDate and ins.INSTALLMENT_MATURITY_DATE <= :endDate
and ins.INSTALLMENT_STATUS = 'Paid' and ins.EMPLOYEE_ID is not null
group by ins.EMPLOYEE_ID ) b
on a.COLLECTOR_ID = b.EMPLOYEE_ID
order by b.INSTALLMENT_BASIC desc, a.LOAN_BASIC_INSTALLMENT desc;
");
ISQLQuery q = Session.CreateSQLQuery(sql.ToString());
q.SetDateTime("startDate", month);
q.SetDateTime("endDate", month.AddMonths(1).AddDays(-1));
q.AddScalar("COLLECTOR_ID", NHibernateUtil.String);
q.AddScalar("LOAN_BASIC_INSTALLMENT", NHibernateUtil.Decimal);
q.AddScalar("INSTALLMENT_BASIC", NHibernateUtil.Decimal);
IList list = q.List();
return(list);
}
public IList <SuUser> FindUserIDAdvanceByTADocumentID(long taDocumentID)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" b.UserID as Userid , ");
sqlBuilder.Append(" b.UserName as UserName ");
sqlBuilder.Append(" FROM TaDocumentTraveller a ");
sqlBuilder.Append(" INNER JOIN SuUser b ");
sqlBuilder.Append(" ON a.UserID = b.UserID ");
sqlBuilder.Append(" WHERE a.UserID NOT IN ( ");
sqlBuilder.Append(" SELECT d.RequesterID ");
sqlBuilder.Append(" FROM AvAdvanceDocument c ");
sqlBuilder.Append(" INNER JOIN Document d ");
sqlBuilder.Append(" ON d.DocumentID = c.DocumentID ");
sqlBuilder.Append(" WHERE c.TADocumentID = :TADocumentID ) ");
sqlBuilder.Append(" AND a.TADocumentID = :TADocumentID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.AddParameterData("TADocumentID", typeof(Int64), taDocumentID);
parameterBuilder.FillParameters(query);
query.AddScalar("Userid", NHibernateUtil.Int64);
query.AddScalar("UserName", NHibernateUtil.String);
IList <SuUser> list =
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuUser))).List <SuUser>();
return(list);
}
public ISQLQuery FindByCompanyTaxCriteria(DbCompanyTax taxID, bool isCount, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbCompanyTax.ID AS ID ,");
sqlBuilder.Append(" DbCompanyTax.TaxID AS TaxID ,");
sqlBuilder.Append(" DbCompanyTax.CompanyID AS CompanyID ,");
sqlBuilder.Append(" DbCompany.CompanyCode AS CompanyCode ,");
sqlBuilder.Append(" DbCompany.CompanyName AS CompanyName ,");
sqlBuilder.Append(" DbCompanyTax.Rate AS Rate ,");
sqlBuilder.Append(" DbCompanyTax.RateNonDeduct AS RateNonDeduct ,");
sqlBuilder.Append(" DbCompanyTax.UseParentRate AS UseParentRate , ");
sqlBuilder.Append(" DbCompanyTax.Disable AS Disable ");
sqlBuilder.Append(" FROM DbCompanyTax ");
sqlBuilder.Append(" INNER JOIN DbCompany ON DbCompanyTax.CompanyID = DbCompany.CompanyID");
sqlBuilder.Append(" WHERE DbCompanyTax.TaxID = :taxID ");
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.AppendLine("ORDER BY DbCompany.CompanyCode");
}
else
{
sqlBuilder.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
else
{
sqlBuilder.Append(" SELECT COUNT(TaxID) AS CompanyTaxCount FROM DbCompanyTax ");
sqlBuilder.Append(" WHERE DbCompanyTax.TaxID = :taxID ");
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("taxID", typeof(string), taxID.TaxID);
queryParameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("ID", NHibernateUtil.Int64);
query.AddScalar("TaxID", NHibernateUtil.Int64);
query.AddScalar("CompanyID", NHibernateUtil.Int64);
query.AddScalar("CompanyCode", NHibernateUtil.String);
query.AddScalar("CompanyName", NHibernateUtil.String);
query.AddScalar("Rate", NHibernateUtil.Double);
query.AddScalar("RateNonDeduct", NHibernateUtil.Double);
query.AddScalar("UseParentRate", NHibernateUtil.Boolean);
query.AddScalar("Disable", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(CompanyTaxRate)));
}
else
{
query.AddScalar("CompanyTaxCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public IList <DbStatus> FindByStatusCriteria(DbStatus status)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select StatusID,GroupStatus,Status,Comment,Active ");
sql.Append("from DbStatus ");
sql.Append("where GroupStatus = :GroupStatus ");
sql.Append("and Status = :Status ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("GroupStatus", typeof(String), status.GroupStatus);
queryParameterBuilder.AddParameterData("Status", typeof(String), status.Status);
queryParameterBuilder.FillParameters(query);
query.AddScalar("StatusID", NHibernateUtil.Int16);
query.AddScalar("GroupStatus", NHibernateUtil.String);
query.AddScalar("Status", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
IList <SS.DB.DTO.DbStatus> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.DB.DTO.DbStatus)))
.List <SS.DB.DTO.DbStatus>();
return(list);
}
public IList <ExportClearing> GetExportClearingListByDate(string sapCode)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT bap.DOC_ID as DocumentID, bap.FI_DOC as FIDOC, comp.CompanyCode ,bap.doc_year as Year ");
sqlBuilder.Append("FROM BAPIACHE09 bap ");
sqlBuilder.Append("INNER JOIN [Document] doc ");
sqlBuilder.Append("ON doc.DocumentID = bap.Doc_ID ");
sqlBuilder.Append("INNER JOIN [WorkFlow] wf ");
sqlBuilder.Append("ON wf.DocumentID = doc.DocumentID ");
sqlBuilder.Append("LEFT JOIN dbCompany comp ");
sqlBuilder.Append("ON comp.CompanyID = doc.CompanyID ");
sqlBuilder.Append("WHERE doc.CacheCurrentStateName = 'WaitPaymentFromSAP' ");
sqlBuilder.Append("AND bap.DOC_SEQ = 'M' ");
sqlBuilder.Append("AND comp.SapCode = :sapCode ");
//sqlBuilder.Append("AND NOT EXISTS ");
//sqlBuilder.Append("(SELECT * FROM FNAUTOPAYMENT fn ");
//sqlBuilder.Append("WHERE fn.FIdoc = bap.fi_doc ");
//sqlBuilder.Append("and fn.year = bap.doc_year ");
//sqlBuilder.Append("and comp.CompanyCode = fn.companycode) ");
//sqlBuilder.Append("AND doc.DocumentDate > '2017-04-23 00:00:00.000' "); /*n-test*/
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("sapCode", typeof(string), sapCode);
parameterBuilder.FillParameters(query);
query.AddScalar("DocumentID", NHibernateUtil.String);
query.AddScalar("CompanyCode", NHibernateUtil.String);
query.AddScalar("FIDOC", NHibernateUtil.String);
query.AddScalar("Year", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ExportClearing))).List <ExportClearing>());
}
//new location table use in LocationLookup
public IList <DbLocation> FindByLocationNameID(long locationID, string locationName)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT LocationID as LocationID , LocationCode as LocationCode ,Description as LocationName ");
sqlBuilder.Append(" FROM DbLocation ");
sqlBuilder.Append(" WHERE (LocationID = :locationID) ");
if (!string.IsNullOrEmpty(locationName))
{
sqlBuilder.Append(" And Description like :locationName ");
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("locationID", typeof(long), locationID);
if (!string.IsNullOrEmpty(locationName))
{
queryParameterBuilder.AddParameterData("locationName", typeof(string), "%" + locationName + "%");
}
queryParameterBuilder.FillParameters(query);
query.AddScalar("LocationID", NHibernateUtil.Int64);
query.AddScalar("LocationCode", NHibernateUtil.String);
query.AddScalar("LocationName", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbLocation))).List <DbLocation>());
}
public IList <ExpensesMPA> FindByMPADocumentID(long MPADocmentID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("Select distinct doc.DocumentNo, doc.Subject, doc.DocumentDate, mpaDoc.MPADocumentID ");
sqlBuilder.AppendLine(" from Document AS doc ");
sqlBuilder.AppendLine(" LEFT JOIN MPADocument AS mpaDoc ");
sqlBuilder.AppendLine(" ON doc.DocumentID = mpaDoc.DocumentID ");
sqlBuilder.AppendLine(" LEFT JOIN MPAItem AS mpaItem ");
sqlBuilder.AppendLine(" ON mpaItem.MPADocumentID = mpaDoc.MPADocumentID ");
sqlBuilder.AppendLine(" WHERE mpaDoc.MPADocumentID = :MPADocmentID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
//query.SetParameterList("MPADocmentID", Convert.ToString(MPADocmentID));
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("MPADocmentID", typeof(long), MPADocmentID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("DocumentDate", NHibernateUtil.Date);
query.AddScalar("MPADocumentID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpensesMPA))).List <ExpensesMPA>());
}
public static List <IntentosUserXIP> ConsultaUltimoAccesos()
{
string strSql = string.Empty;
List <IntentosUserXIP> Accesos = new List <IntentosUserXIP>();
ISession session = NHibernateHelperORACLE.GetSession();
strSql += " SELECT DISTINCT ID_TIPOACCESO AS ACCESO,LOG_IP AS IP , COUNT(EMPL_LLAV_PR) INTENTOS FROM ";
strSql += " ( SELECT * FROM SEML_TDI_LOGACCESO ";
strSql += " ORDER BY LOG_FECHAACCESO DESC ) WHERE ROWNUM <= 10 ";
strSql += " GROUP BY ID_TIPOACCESO,LOG_IP ORDER BY 2,1 ";
try
{
ISQLQuery consultaImagenesOT = session.CreateSQLQuery(strSql);
consultaImagenesOT.AddScalar("ACCESO", NHibernateUtil.Int32);
consultaImagenesOT.AddScalar("IP", NHibernateUtil.String);
consultaImagenesOT.AddScalar("INTENTOS", NHibernateUtil.Int32);
IList listaImagenesOT = consultaImagenesOT.List();
if (listaImagenesOT != null)
{
if (listaImagenesOT.Count > 1)
{
foreach (Object[] Tmp in listaImagenesOT)
{
IntentosUserXIP IUAdd = new IntentosUserXIP();
IUAdd.TipoIntento = (Int32)Tmp[0];
IUAdd.NoIP = Tmp[1].ToString();
IUAdd.NumIntento = (Int32)Tmp[2];
Accesos.Add(IUAdd);
}
}
else
{
IntentosUserXIP IUAdd = new IntentosUserXIP();
IUAdd.TipoIntento = (Int32)((object[])((listaImagenesOT[0])))[0];
IUAdd.NoIP = ((object[])((listaImagenesOT[0])))[1].ToString();
IUAdd.NumIntento = (Int32)((object[])((listaImagenesOT[0])))[2];
Accesos.Add(IUAdd);
}
}
return(Accesos);
}
catch (Exception ex)
{
return(Accesos);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
}
public static IList <THE_Encuesta> ObtieneDatosEncuestaPreview(int IdEncuesta)
{
#region Query Armado
List <THE_Encuesta> lstEncuesta = new List <THE_Encuesta>();
string strSQL = string.Empty;
Azteca.Utility.Security.Rijndael _ChyperRijndael = new Azteca.Utility.Security.Rijndael();
ISession session = NHibernateHelperORACLE.GetSession();
strSQL += " SELECT encuesta.ID_ENCUESTA encuId, encuesta.ENCUESTA_NOMBRE encuNombre ";
strSQL += " FROM seml_the_encuesta encuesta ";
strSQL += " WHERE encuesta.id_encuesta = " + IdEncuesta;
strSQL += " AND encuesta.encuesta_stat = 'A' ";
try
{
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("encuId", NHibernateUtil.Int32); //0
consultaIQRY.AddScalar("encuNombre", NHibernateUtil.String); //1
IList lista = consultaIQRY.List();
List <THE_Preguntas> lstPregun = new List <THE_Preguntas>();
List <THE_Respuestas> lstRespu = new List <THE_Respuestas>();
THE_Encuesta ItemEncuesta = new THE_Encuesta();
foreach (Object[] obj in lista)
{
foreach (THE_Preguntas oPreg in MngDatosPreguntas.ObtienePreguntasPorEncuesta(System.Convert.ToInt32(obj[0])))
{
lstRespu = new List <THE_Respuestas>();
foreach (THE_Respuestas oResp in MngDatosRespuestas.ObtenerRespuestasPorPregunta(oPreg.IdPregunta))
{
lstRespu.Add(oResp);
}
oPreg.ListaRespuestas = lstRespu;
lstPregun.Add(oPreg);
ItemEncuesta.LstPreg = lstPregun;
}
}
lstEncuesta.Add(ItemEncuesta);
}
catch (Exception ex)
{
lstEncuesta = null;
return(lstEncuesta);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
return(lstEncuesta);
#endregion
}
public ISQLQuery GetExpensesMPAListQuery(long?CompanyID, long?RequesterID, long?CurrentUserID, bool isCount, string sortExpression)
{
StringBuilder sql = new StringBuilder();
if (!isCount)
{
sql.AppendLine("Select doc.DocumentNo, doc.Subject, doc.DocumentDate, mpaDoc.MPADocumentID ");
}
else
{
sql.AppendLine(" SELECT COUNT(*) ExpensesCount ");
}
sql.AppendLine(" from Document AS doc ");
sql.AppendLine(" LEFT JOIN MPADocument AS mpaDoc ");
sql.AppendLine(" ON doc.DocumentID = mpaDoc.DocumentID ");
sql.AppendLine(" LEFT JOIN MPAItem AS mpaItem ");
sql.AppendLine(" ON mpaItem.MPADocumentID = mpaDoc.MPADocumentID ");
sql.AppendLine(" WHERE doc.CompanyID = :CompanyID ");
sql.AppendLine(" AND mpaItem.UserID = :RequesterID ");
sql.AppendLine(" AND GETDATE() Between mpaDoc.StartDate AND mpaDoc.EndDate ");
sql.AppendLine(" AND doc.CacheCurrentStateName = 'Complete'");
if (!isCount)
{
if (string.IsNullOrEmpty(sortExpression))
{
sql.AppendLine(" ORDER BY doc.DocumentNo");
}
else
{
sql.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetParameter("CompanyID", Convert.ToString(CompanyID));
query.SetParameter("RequesterID", Convert.ToString(RequesterID));
if (!isCount)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("DocumentDate", NHibernateUtil.Date);
query.AddScalar("MPADocumentID", NHibernateUtil.Int64);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpensesMPA))).List <ExpensesMPA>();
}
else
{
query.AddScalar("ExpensesCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public static IList <TDI_EncuestaDispositivo> ObtieneDispoByIdEncNumTel(string idEncuesta, string numTelefonico)
{
#region Query Armado
List <TDI_EncuestaDispositivo> lstEncuestasDispo = new List <TDI_EncuestaDispositivo>();
string strSQL = string.Empty;
Azteca.Utility.Security.Rijndael _ChyperRijndael = new Azteca.Utility.Security.Rijndael();
ISession session = NHibernateHelperORACLE.GetSession();
strSQL += " select distinct EDISPO.ID_DISPOSITIVO id_Dispo, EDISPO.ID_ENCUESTA id_enc, edispo.id_envio id_envio ";
strSQL += " from seml_tdi_encuestadispositivo edispo, seml_the_dispositivo dispo ";
strSQL += " where EDISPO.ID_DISPOSITIVO=DISPO.ID_DISPOSITIVO and ";
strSQL += " DISPO.DISPO_NUMTELEFONO=" + numTelefonico + " AND EDISPO.ID_ENCUESTA=" + idEncuesta + " ";
try
{
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("ID_DISPO", NHibernateUtil.Int32); //0
consultaIQRY.AddScalar("ID_ENC", NHibernateUtil.Int32); //1
consultaIQRY.AddScalar("ID_ENVIO", NHibernateUtil.Int32); //2
IList lista = consultaIQRY.List();
foreach (Object[] obj in lista)
{
TDI_EncuestaDispositivo EncuDispo = new TDI_EncuestaDispositivo();
EncuDispo.IdDispositivo = new THE_Dispositivo()
{
IdDispositivo = int.Parse(obj[0].ToString())
};
EncuDispo.IdDispo = int.Parse(obj[0].ToString());
EncuDispo.IdEncuesta = new THE_Encuesta()
{
IdEncuesta = int.Parse(obj[1].ToString())
};
EncuDispo.IdEnvio = Convert.ToInt32(obj[2].ToString());
lstEncuestasDispo.Add(EncuDispo);
}
}
catch
{
lstEncuestasDispo = null;
return(lstEncuestasDispo);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
return(lstEncuestasDispo);
#endregion
}
public static IList <THE_ArbolEncuesta> ObtenerArbol(int id_Encuesta)
{
List <THE_ArbolEncuesta> lstArbolEncuesta = new List <THE_ArbolEncuesta>();
string strSQL = string.Empty;
Azteca.Utility.Security.Rijndael _ChyperRijndael = new Azteca.Utility.Security.Rijndael();
ISession session = NHibernateHelperORACLE.GetSession();
strSQL += " SELECT DISTINCT preg.id_pregunta, preg.pregunta_desc, resp.id_respuesta, ";
strSQL += " resp.respuesta_desc, resp.id_siguientepregunta ";
strSQL += " FROM seml_the_respuestas resp, ";
strSQL += " seml_the_preguntas preg, ";
strSQL += " seml_the_encuesta enc ";
strSQL += " WHERE enc.id_encuesta = preg.id_encuesta ";
strSQL += " AND preg.id_pregunta = resp.id_pregunta(+) ";
strSQL += " AND enc.id_encuesta = " + id_Encuesta;
strSQL += " AND preg.preg_estatus = 'A' ";
strSQL += " AND RESP.RESP_ESTATUS (+)= 'A' ";
strSQL += " ORDER BY id_pregunta ";
try
{
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("ID_Pregunta", NHibernateUtil.Int32); //0
consultaIQRY.AddScalar("Pregunta_Desc", NHibernateUtil.String); //1
consultaIQRY.AddScalar("ID_Respuesta", NHibernateUtil.Int32); //2
consultaIQRY.AddScalar("Respuesta_Desc", NHibernateUtil.String); //3
consultaIQRY.AddScalar("ID_SIGUIENTEPREGUNTA", NHibernateUtil.Int32); //4
IList lista = consultaIQRY.List();
foreach (Object[] obj in lista)
{
THE_ArbolEncuesta oNodo = new THE_ArbolEncuesta();
oNodo.ID_Pregunta = System.Convert.ToInt32(obj[0]);
oNodo.Pregunta_Desc = System.Convert.ToString(obj[1]);
oNodo.ID_Respuesta = System.Convert.ToInt32(obj[2]);
oNodo.Respuesta_Desc = System.Convert.ToString(obj[3]);
oNodo.ID_PreguntaAnterior = System.Convert.ToInt32(obj[4]);
lstArbolEncuesta.Add(oNodo);
}
}
catch
{
lstArbolEncuesta = null;
return(lstArbolEncuesta);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
return(lstArbolEncuesta);
}
public static IList <THE_Respuestas> ObtenerRespuestasPorPregunta(int IdPregunta, int IdRespuesta)
{
List <THE_Respuestas> lstDispoDisponibles = new List <THE_Respuestas>();
string strSQL = string.Empty;
Azteca.Utility.Security.Rijndael _ChyperRijndael = new Azteca.Utility.Security.Rijndael();
ISession session = NHibernateHelperORACLE.GetSession();
strSQL += " SELECT respu.id_pregunta idpreg, respu.id_respuesta idrespu, ";
strSQL += " respu.id_siguientepregunta idsigpreg, respu.resp_estatus respuestat, ";
strSQL += " respu.respuesta_desc respdesc ";
strSQL += " FROM seml_the_respuestas respu ";
strSQL += " WHERE respu.id_pregunta = " + IdPregunta;
strSQL += " AND respu.resp_estatus = 'A' AND respu.id_respuesta <> '" + IdRespuesta + "'";
try
{
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("idpreg", NHibernateUtil.Int32); //0
consultaIQRY.AddScalar("idrespu", NHibernateUtil.Int32); //1
consultaIQRY.AddScalar("idsigpreg", NHibernateUtil.Int32); //2
consultaIQRY.AddScalar("respuestat", NHibernateUtil.AnsiChar); //3
consultaIQRY.AddScalar("respdesc", NHibernateUtil.String); //4
IList lista = consultaIQRY.List();
foreach (Object[] obj in lista)
{
THE_Respuestas oResp = new THE_Respuestas();
oResp.IdPregunta = new THE_Preguntas()
{
IdPregunta = System.Convert.ToInt32(obj[0])
};
oResp.IdRespuesta = System.Convert.ToInt32(obj[1]);
oResp.IdSiguientePregunta = System.Convert.ToInt32(obj[2]);
oResp.RespuestaEstatus = System.Convert.ToChar(obj[3]);
oResp.RespuestaDescripcion = System.Convert.ToString(obj[4]);
List <THE_Preguntas> PreguntaDesc = (List <THE_Preguntas>)MngDatosPreguntas.ObtienePreguntaPorID(oResp.IdSiguientePregunta);
oResp.DescSigPreg = PreguntaDesc[0].PreguntaDesc;
lstDispoDisponibles.Add(oResp);
}
}
catch (Exception ex)
{
lstDispoDisponibles = null;
return(lstDispoDisponibles);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
return(lstDispoDisponibles);
}
public static IList <THE_SesionUsuario> VerExisteSesionUsuario(int idusuario, string ipMaquina)
{
try
{
ISession session = NHibernateHelperORACLE.GetSession();
List <THE_SesionUsuario> lstUsuarioSesion = new List <THE_SesionUsuario>();
string strSQL = string.Empty;
strSQL += " SELECT DISTINCT usesion.empl_llav_pr idusuario, ";
strSQL += " usesion.ip_usr ipuser, usesion.id_sesion idsesion";
strSQL += " FROM seml_the_sesion_usuario usesion ";
if (idusuario.ToString() != "")
{
strSQL += " WHERE usesion.empl_llav_pr=" + idusuario;
}
if (ipMaquina != "")
{
strSQL += " AND usesion.ip_usr='******' ";
}
try {
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("idusuario", NHibernateUtil.Int32); //0
consultaIQRY.AddScalar("ipuser", NHibernateUtil.String); //1
consultaIQRY.AddScalar("idsesion", NHibernateUtil.Int32);
IList lista = consultaIQRY.List();
foreach (Object[] obj in lista)
{
THE_SesionUsuario oDispo = new THE_SesionUsuario();
oDispo.EmplLlavPr = new THE_Empleado {
EmpleadoLlavePrimaria = System.Convert.ToInt32(obj[0])
};
oDispo.DirIP = System.Convert.ToString(obj[1]);
oDispo.IdSesion = System.Convert.ToInt32(obj[2]);
lstUsuarioSesion.Add(oDispo);
}
}
catch (Exception Ms) {
lstUsuarioSesion = null;
return(lstUsuarioSesion);
}finally{
session.Close();
session.Dispose();
session = null;
}
return(lstUsuarioSesion);
}
catch (Exception ex)
{
MngDatosLogErrores.GuardaError(ex, "MngDatosUsuarioSesion");
return(new List <THE_SesionUsuario>());
}
}
public IList <NewUserEmail> FindNewUser()
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("SELECT SuUser.UserID AS UserID, Tmp_SuUser.OldPassword AS Password FROM SuUser INNER JOIN Tmp_SuUser ON SuUser.PeopleID = Tmp_SuUser.PeopleID AND SuUser.UserName = Tmp_SuUser.UserName WHERE Tmp_SuUser.isNewUser = '******'");
query.AddScalar("UserID", NHibernateUtil.Int64);
query.AddScalar("Password", NHibernateUtil.String);
IList <NewUserEmail> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(NewUserEmail))).List <NewUserEmail>();
return(list);
}
/// <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 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 <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>());
}
