public IList <VOPB> FindPBLangByPBID(long pbID)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT DbLanguage.LanguageID as LanguageID , DbLanguage.LanguageName as LanguageName ,");
sqlBuilder.Append(" DbPBLang.PBLangID as PBLangID , DbPBLang.LanguageID AS PBLanguageID , ");
sqlBuilder.Append(" DbPBLang.PBID as PBID , DbPBLang.Description as Description , DbPBLang.Comment as Comment , ");
sqlBuilder.Append(" DbPBLang.Active as Active ");
sqlBuilder.Append(" FROM DbLanguage LEFT JOIN ");
sqlBuilder.Append(" DbPBLang ON DbLanguage.LanguageID = DbPBLang.LanguageID and DbPBLang.PBID= :CID ");
queryParameterBuilder.AddParameterData("CID", typeof(long), pbID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("PBLangID", NHibernateUtil.Int64)
.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("PBLanguageID", NHibernateUtil.Int16)
.AddScalar("PBID", NHibernateUtil.Int64)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB)));
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB))).List <VOPB>());;
}
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 <CountryLang> FindCountryLangByCountryID(short cID)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT DbLanguage.LanguageID as LanguageId , DbLanguage.LanguageName as LanguageName ,");
sqlBuilder.Append(" DbCountryLang.ID as ID , DbCountryLang.LanguageID AS LanguageId , ");
sqlBuilder.Append(" DbCountryLang.CountryID as CountryID ,DbCountryLang.CountryName as CountryName, DbCountryLang.Comment as Comment , ");
sqlBuilder.Append(" DbCountryLang.Active as Active ");
sqlBuilder.Append(" FROM DbLanguage LEFT JOIN ");
sqlBuilder.Append(" DbCountryLang ON DbLanguage.LanguageID = DbCountryLang.LanguageID and DbCountryLang.CountryID= :CID ");
queryParameterBuilder.AddParameterData("CID", typeof(short), cID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("ID", NHibernateUtil.Int16)
.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("CountryID", NHibernateUtil.Int16)
.AddScalar("CountryName", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB)));
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(CountryLang))).List <CountryLang>());;
}
public IList <VOUCurrencySetup> FindCurrencyLangByCurrencyID(long cid)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT DbLanguage.LanguageID as LanguageID , DbLanguage.LanguageName as LanguageName ,");
sqlBuilder.Append(" DbCurrencyLang.CurrencyLangID as CurrencyLangID , DbCurrencyLang.LanguageID AS CLanguageID , ");
sqlBuilder.Append(" DbCurrencyLang.CurrencyID as CurrencyID , DbCurrencyLang.Description as Description , ");
sqlBuilder.Append(" DbCurrencyLang.Comment as Comment , DbCurrencyLang.Active as LangActive ,");
sqlBuilder.Append(" DbCurrencyLang.MainUnit as MainUnit,DbCurrencyLang.SubUnit as SubUnit ");
sqlBuilder.Append(" FROM DbLanguage LEFT JOIN ");
sqlBuilder.Append(" DbCurrencyLang ON DbLanguage.LanguageID = DbCurrencyLang.LanguageID and DbCurrencyLang.CurrencyID= :CID ");
queryParameterBuilder.AddParameterData("CID", typeof(long), cid);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("CurrencyID", NHibernateUtil.Int16)
.AddScalar("LangActive", NHibernateUtil.Boolean)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("CLanguageID", NHibernateUtil.Int16)
.AddScalar("CurrencyLangID", NHibernateUtil.Int64)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("MainUnit", NHibernateUtil.String)
.AddScalar("SubUnit", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOUCurrencySetup)));
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VOUCurrencySetup))).List <VOUCurrencySetup>());;
}
public IList <SuRTENodeSearchResult> GetRTEContent(short languageId, string nodetype, short nodeId)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT n.NodeId,n.NodeHeaderId,n.NodeOrderNo,c.languageId,n.NodeType,c.Header,c.Content,n.ImagePath");
strQuery.AppendLine(" From SuRTENode n LEFT OUTER JOIN SuRTEContent c on c.nodeid = n.nodeid ");
strQuery.AppendLine(" WHERE c.LanguageID = :LanguageID and n.nodetype = :NodeType ");
strQuery.AppendLine(" AND n.Active = 'TRUE' and n.NodeId = :NodeId");
strQuery.AppendLine(" ORDER BY n.NodeHeaderId,n.NodeOrderNo");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageId);
query.SetString("NodeType", nodetype);
query.SetInt16("NodeId", nodeId);
query.AddScalar("NodeId", NHibernateUtil.Int16);
query.AddScalar("NodeHeaderId", NHibernateUtil.Int16);
query.AddScalar("NodeOrderNo", NHibernateUtil.Int16);
query.AddScalar("languageId", NHibernateUtil.Int16);
query.AddScalar("NodeType", NHibernateUtil.String);
query.AddScalar("Header", NHibernateUtil.String);
query.AddScalar("Content", NHibernateUtil.String);
query.AddScalar("ImagePath", NHibernateUtil.String);
IList <SuRTENodeSearchResult> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRTENodeSearchResult))).List <SuRTENodeSearchResult>();
return(list);
}
public virtual IList <IUserVO> RetrieveUserList(IList <string> excludedLoginIdList)
{
string query = RETRIEVE_USER;
if (excludedLoginIdList != null && excludedLoginIdList.Count > 0)
{
for (int counter = 0; counter < excludedLoginIdList.Count(); counter++)
{
query += (" AND su.login_id <> :LoginId" + counter);
}
}
ISQLQuery sqlQuery = CurrentSession.CreateSQLQuery(query);
sqlQuery.SetResultTransformer(Transformers.AliasToBean <UserVO>());
if (excludedLoginIdList != null && excludedLoginIdList.Count > 0)
{
for (int counter = 0; counter < excludedLoginIdList.Count(); counter++)
{
sqlQuery.SetParameter(("LoginId" + counter), excludedLoginIdList.ElementAt(counter));
}
}
return(sqlQuery.List <IUserVO>());
}
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 InvoiceExchangeRate GetAdvanceExchangeRate(IList <long> advanceIDlist, short currencyID)
{
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.AppendLine(" SELECT SUM(ISNULL(Amount,0)) as TotalAmount, SUM(ISNULL(AmountTHB,0)) as TotalAmountTHB, SUM(ISNULL(MainCurrencyAmount,0)) as TotalAmountMainCurrency ");
queryBuilder.AppendLine(" FROM AvAdvanceItem ");
queryBuilder.AppendLine(" WHERE AdvanceID in ( :advanceIDList ) ");
queryBuilder.AppendLine(" AND CurrencyID = :currencyID ");
queryBuilder.AppendLine(" GROUP BY CurrencyID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(queryBuilder.ToString());
query.SetParameterList("advanceIDList", advanceIDlist);
query.SetInt16("currencyID", currencyID);
query.AddScalar("TotalAmount", NHibernateUtil.Double);
query.AddScalar("TotalAmountTHB", NHibernateUtil.Double);
query.AddScalar("TotalAmountMainCurrency", NHibernateUtil.Double);
query.SetResultTransformer(Transformers.AliasToBean(typeof(InvoiceExchangeRate)));
IList <InvoiceExchangeRate> list = query.List <InvoiceExchangeRate>();
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
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 <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 <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>());
}
/// <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 List <UserOrder> getOrders(UserOrder o)
{
//ISQLQuery q = _currentNhibernateSession.CreateSQLQuery("SELECT * FROM USERORDER WHERE USER_ID = " + o.USER_ID + " AND STATE = 0");
//UserOrder f = q.SetResultTransformer(Transformers.AliasToBean<UserOrder>()).UniqueResult<UserOrder>();
ISQLQuery query = _currentNhibernateSession.CreateSQLQuery("SELECT * FROM USERORDER WHERE USER_ID = " + o.USER_ID + " AND STATE = 0");
List <UserOrder> f = (List <UserOrder>)query.SetResultTransformer(Transformers.AliasToBean <UserOrder>()).List <UserOrder>();
/*
* ICriteria c = _currentNhibernateSession.CreateCriteria<UserOrder>();
* c.Add(Expression.Eq("Price", 500));
* return c.UniqueResult<UserOrder>();
*/
//List<UserOrder> list = new List<UserOrder>();
// if (f != null)
// {
// list.Add(f);
// }
return(f);
}
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 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 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 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<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 <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 <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 <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>());
}
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 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 <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 <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>());
}
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 <ExportPayroll> GetExportPayrollList(DateTime date, string comCode, string Ordinal)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder paramBuilder = new QueryParameterBuilder();
DateTime startDate = date.AddMonths(-1).AddDays(15);
DateTime endDate = date.AddDays(14);
//sqlBuilder.Append("EXECUTE EXPENSE_PAYROLL_REPORT :com,:sdate,:edate,:ordinal");
sqlBuilder.Append("EXECUTE EXPENSE_PAYROLL_REPORT '" + comCode + "','" + startDate.ToString("MM/dd/yyyy", new CultureInfo("en-US")) + "','" + endDate.ToString("MM/dd/yyyy", new CultureInfo("en-US")) + "','" + Ordinal + "'");
//paramBuilder.AddParameterData("ordinal", typeof(string), Ordinal);
//paramBuilder.AddParameterData("sdate", typeof(string), startDate.ToString("MM/dd/yyyy", new CultureInfo("en-US")));
//paramBuilder.AddParameterData("edate", typeof(string), endDate.ToString("MM/dd/yyyy", new CultureInfo("en-US")));
//paramBuilder.AddParameterData("comcode", typeof(string), comCode);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
//paramBuilder.FillParameters(query);
query.AddScalar("CompanyCode", NHibernateUtil.String)
.AddScalar("EmployeeCode", NHibernateUtil.String)
.AddScalar("CostCenterCode", NHibernateUtil.String)
.AddScalar("totalAmount", NHibernateUtil.Decimal)
.AddScalar("wagecode", NHibernateUtil.String)
.AddScalar("PayrollType", NHibernateUtil.String)
.AddScalar("PeopleID", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ExportPayroll)));
return(query.List <ExportPayroll>());
}
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 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);
}
}
