public IList <ExportBoxID> GetExportBoxIDList(string sapCode)
{
StringBuilder sqlBuider = new StringBuilder();
sqlBuider.Append("SELECT doc.DocumentID,");
sqlBuider.Append("expDoc.BoxID,");
sqlBuider.Append("Doc.DocumentDate,");
sqlBuider.Append("Com.CompanyCode,");
sqlBuider.Append("docBoxPost.Status,");
sqlBuider.Append("imgDocPost.FIDocNumber,");
sqlBuider.Append("imgDocPost.ImageDocID ");
sqlBuider.Append("FROM FnExpenseDocument expDoc ");
sqlBuider.Append("LEFT JOIN [Document] doc ");
sqlBuider.Append("ON doc.DocumentID = expDoc.DocumentID ");
sqlBuider.Append("LEFT JOIN DbCompany com ");
sqlBuider.Append("ON com.CompanyID = doc.CompanyID ");
sqlBuider.Append("LEFT JOIN DbDocumentBoxIDPosting docBoxPost ");
sqlBuider.Append("ON docBoxPost.DocumentID = expDoc.DocumentID ");
sqlBuider.Append("INNER JOIN DbDocumentImagePosting imgDocPost ");
sqlBuider.Append("ON imgDocPost.DocumentID = expDoc.DocumentID ");
sqlBuider.Append("WHERE expDoc.BoxID <> '' ");
sqlBuider.Append("AND (docBoxPost.Status is null or docBoxPost.Status = 'F') ");
sqlBuider.Append("AND imgDocPost.Status = 'S' ");
sqlBuider.Append("AND com.SapCode = :sapCode ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuider.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("sapCode", typeof(string), sapCode);
parameterBuilder.FillParameters(query);
query.AddScalar("DocumentID", NHibernateUtil.Int64)
.AddScalar("BoxID", NHibernateUtil.String)
.AddScalar("DocumentDate", NHibernateUtil.Date)
.AddScalar("CompanyCode", NHibernateUtil.String)
.AddScalar("Status", NHibernateUtil.String)
.AddScalar("FIDocNumber", NHibernateUtil.String)
.AddScalar("ImageDocID", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ExportBoxID)));
return(query.List <ExportBoxID>());
}
public IList <Location> FindByLocationIdentity(long locationId)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbCompany.CompanyID AS CompanyID ,");
sqlBuilder.Append(" DbCompany.CompanyCode AS CompanyCode, ");
sqlBuilder.Append(" DbCompany.CompanyName AS CompanyName, ");
sqlBuilder.Append(" DbLocation.LocationID AS LocationID ,");
sqlBuilder.Append(" DbLocation.LocationCode AS LocationCode ,");
//sqlBuilder.Append(" DbLocationLang.Description AS LocationName ,");
sqlBuilder.Append(" DbCompany.Active AS Active ");
sqlBuilder.Append(" FROM DbCompany ");
sqlBuilder.Append(" INNER JOIN DbLocation ON ");
sqlBuilder.Append(" DbCompany.CompanyID = DbLocation.CompanyID AND ");
sqlBuilder.Append(" DbCompany.CompanyCode = DbLocation.CompanyCode AND ");
sqlBuilder.Append(" DbCompany.Active = 1 ");
//sqlBuilder.Append(" INNER JOIN DbLocationLang ON ");
//sqlBuilder.Append(" DbLocationLang.LocationID = DbLocation.LocationID ");
sqlBuilder.Append(" WHERE DbLocation.LocationID = :LocationID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.AddParameterData("LocationID", typeof(Int64), locationId);
parameterBuilder.FillParameters(query);
query.AddScalar("CompanyID", NHibernateUtil.Int64);
query.AddScalar("CompanyCode", NHibernateUtil.String);
query.AddScalar("CompanyName", NHibernateUtil.String);
query.AddScalar("LocationID", NHibernateUtil.Int64);
query.AddScalar("LocationCode", NHibernateUtil.String);
//query.AddScalar("LocationName", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
IList <Location> list =
query.SetResultTransformer(Transformers.AliasToBean(typeof(Location))).List <Location>();
return(list);
}
public IList <DbZoneResult> FindZone(short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select z.ZoneID as ZoneID,zl.ID as ZoneLangID,zl.ZoneName as ZoneName,z.Comment as Comment,z.Active as Active ");
sqlBuilder.Append("from DbZone as z ");
sqlBuilder.Append("left join DbZoneLang as zl on z.ZoneID = zl.ZoneID and zl.LanguageID =:LanguageID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LanguageID", typeof(Int16), languageId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("ZoneID", NHibernateUtil.Int16);
query.AddScalar("ZoneLangID", NHibernateUtil.Int16);
query.AddScalar("ZoneName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbZoneResult)));
return(query.List <DbZoneResult>());
}
public bool isDuplicationWHTTypeCode(string WHTCode)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" WHTTypeID AS WhtTypeID ,");
sqlBuilder.Append(" WHTTypeCode AS WhtTypeCode ,");
sqlBuilder.Append(" WHTTypeName AS WhtTypeName ,");
sqlBuilder.Append(" Active AS Active , ");
sqlBuilder.Append(" IsPeople AS IsPeople ");
sqlBuilder.Append(" FROM DbWithHoldingTaxType ");
sqlBuilder.Append(" WHERE WHTTypeCode = :WHTTypeCode ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.AddScalar("WhtTypeID", NHibernateUtil.Int32);
query.AddScalar("WhtTypeCode", NHibernateUtil.String);
query.AddScalar("WhtTypeName", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("IsPeople", NHibernateUtil.Boolean);
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("WHTTypeCode", typeof(string), WHTCode);
parameterBuilder.FillParameters(query);
IList <DbWithHoldingTaxType> DbWHTType = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbWithHoldingTaxType))).List <DbWithHoldingTaxType>();
if (DbWHTType.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public IList <SuRole> FindUserRoleCriteria(long userID)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" select ");
sqlBuilder.AppendLine(" b.RoleID as RoleID, ");
sqlBuilder.AppendLine(" b.ReceiveDocument as ReceiveDocument, ");
sqlBuilder.AppendLine(" b.VerifyDocument as VerifyDocument, ");
sqlBuilder.AppendLine(" b.ApproveVerifyDocument as ApproveVerifyDocument, ");
sqlBuilder.AppendLine(" b.VerifyPayment as VerifyPayment, ");
sqlBuilder.AppendLine(" b.ApproveVerifyPayment as ApproveVerifyPayment, ");
sqlBuilder.AppendLine(" b.CounterCashier as CounterCashier ");
sqlBuilder.AppendLine(" b.AllowMultipleApprovePayment as AllowMultipleApprovePayment");
sqlBuilder.AppendLine(" b.AllowMultipleApproveAccountant as AllowMultipleApproveAccountant");
sqlBuilder.AppendLine(" from SuUserRole a inner join SuRole b ");
sqlBuilder.AppendLine(" on a.RoleID = b.RoleID ");
sqlBuilder.AppendLine(" and b.Active = 1 ");
if (userID > 0)
{
sqlBuilder.AppendLine(" where a.UserID = :UserID ");
parameterBuilder.AddParameterData("UserID", typeof(long), userID);
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("RoleID", NHibernateUtil.Int16);
query.AddScalar("ReceiveDocument", NHibernateUtil.Boolean);
query.AddScalar("VerifyDocument", NHibernateUtil.Boolean);
query.AddScalar("ApproveVerifyDocument", NHibernateUtil.Boolean);
query.AddScalar("ApproveVerifyPayment", NHibernateUtil.Boolean);
query.AddScalar("CounterCashier", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRole))).List <SuRole>());
}
public IList <MenuPath> FindAllByLanguage(short languageID)
{
StringBuilder sql = new StringBuilder();
QueryParameterBuilder parameter = new QueryParameterBuilder();
sql.Append("SELECT SuMenu.MenuID,SuMenu.MenuMainID,SuMenuLang.MenuName,SuMenu.ProgramID,SuMenu.MenuLevel ");
sql.Append("FROM SuMenu ");
sql.Append("LEFT OUTER JOIN SuMenuLang ");
sql.Append("ON SuMenu.MenuID = SuMenuLang.MenuID ");
sql.Append("AND SuMenuLang.LanguageID = :languareID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
parameter.AddParameterData("languareID", typeof(short), languageID);
parameter.FillParameters(query);
query.AddScalar("MenuID", NHibernateUtil.Int16);
query.AddScalar("MenuMainID", NHibernateUtil.Int16);
query.AddScalar("MenuName", NHibernateUtil.String);
query.AddScalar("ProgramID", NHibernateUtil.Int16);
query.AddScalar("MenuLevel", NHibernateUtil.Int16);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(MenuPath))).List <MenuPath>());
}
public Guid getMileageProfileByCompanyID(Int64 companyid)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT comEx.MileageProfileId FROM DbCompany com INNER JOIN DbCompanyEx comEx ON com.CompanyCode = comEx.CompanyCode WHERE com.CompanyID = :companyid");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("companyid", typeof(string), companyid);
parameterBuilder.FillParameters(query);
query.AddScalar("MileageProfileId", NHibernateUtil.Guid);
string result = Convert.ToString(query.UniqueResult());
if (!string.IsNullOrEmpty(result))
{
return(new Guid(result));
}
else
{
return(new Guid());
}
}
public IList <ExpenseCA> FindByCADocumentID(long CADocmentID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("Select distinct doc.DocumentNo, doc.Subject, doc.DocumentDate, caDoc.CADocumentID ");
sqlBuilder.AppendLine(" from Document AS doc ");
sqlBuilder.AppendLine(" LEFT JOIN CADocument AS caDoc ");
sqlBuilder.AppendLine(" ON doc.DocumentID = caDoc.DocumentID ");
sqlBuilder.AppendLine(" WHERE caDoc.CADocumentID = :CADocmentID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
//query.SetParameterList("MPADocmentID", Convert.ToString(MPADocmentID));
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("CADocmentID", typeof(long), CADocmentID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("DocumentDate", NHibernateUtil.Date);
query.AddScalar("CADocumentID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpenseCA))).List <ExpenseCA>());
}
public IList <LocationLangResult> FindLocationLangByLocationID(long locationId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("Select lang.LanguageID as LanguageID,lang.LanguageName as LanguageName "); //, ll.Description as LocationName, ll.Comment as Comment, ll.Active as Active ");
sqlBuilder.Append("FROM DbLanguage as lang ");
//sqlBuilder.Append("LEFT JOIN DbLocationLang as ll on lang.LanguageID = ll.LanguageID AND ll.LocationID =:LocationID ");
sqlBuilder.Append("Where lang.LanguageID = :LanguageID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LocationID", typeof(long), locationId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageID", NHibernateUtil.Int16);
query.AddScalar("LanguageName", NHibernateUtil.String);
//query.AddScalar("LocationName", NHibernateUtil.String);
//query.AddScalar("Comment", NHibernateUtil.String);
//query.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(LocationLangResult))).List <LocationLangResult>());
}
public ISQLQuery FindCurrencyDetail(short id, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
sqlBuilder.Append("select db.CurrencyID as CurrencyID, db.Symbol as Symbol, db.Active as Active, dbl.Description as Description ");
sqlBuilder.Append(" from DbCurrency db ");
sqlBuilder.Append(" inner join DbCurrencyLang dbl on db.CurrencyID = dbl.CurrencyID ");
sqlBuilder.Append(" inner join DbLanguage lang on lang.LanguageID = dbl.LanguageID ");
#region WhereClause
StringBuilder whereClauseBuilder = new StringBuilder();
whereClauseBuilder.Append(" and db.Active = 1 ");
if (id != null)
{
whereClauseBuilder.Append(" and db.CurrencyID = :id ");
queryParameterBuilder.AddParameterData("id", typeof(string), String.Format("%{0}%", id));
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
query.AddScalar("CurrencyID", NHibernateUtil.Int16)
.AddScalar("Symbol", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("LangActive", NHibernateUtil.Boolean)
.AddScalar("LanguageName", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOUCurrencySetup)));
return(query);
}
public void InsertData(DbSellingRunning sellingRunning)
{
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO DbSellingRunning(CompanyID,Year,RunningNo,CreBy,CreDate,UpdBy,UpdDate) ");
sql.Append(" VALUES ( :companyID, :year,:runningNo ,:creBy , :creDate , :updBy , :updDate ) ");
QueryParameterBuilder param = new QueryParameterBuilder();
param.AddParameterData("companyID", typeof(long), sellingRunning.CompanyID);
param.AddParameterData("year", typeof(int), sellingRunning.Year);
param.AddParameterData("runningNo", typeof(long), sellingRunning.RunningNo);
param.AddParameterData("creBy", typeof(DateTime), sellingRunning.CreBy);
param.AddParameterData("creDate", typeof(DateTime), sellingRunning.CreDate);
param.AddParameterData("updBy", typeof(long), sellingRunning.UpdBy);
param.AddParameterData("updDate", typeof(DateTime), sellingRunning.UpdDate);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
param.FillParameters(query);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public IList <CompanyPaymentMethodResult> GetPaymentMethod(long companyID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT DbPaymentMethod.PaymentMethodID as PaymentMethodID, DbPaymentMethod.PaymentMethodName as PaymentMethodName, '[' + DbPaymentMethod.PaymentMethodCode + '] ' + DbPaymentMethod.PaymentMethodName as PaymentMethodCode ");
sqlBuilder.Append(" FROM DbCompanyPaymentMethod ");
sqlBuilder.Append(" INNER JOIN DbPaymentMethod ");
sqlBuilder.Append(" ON DbCompanyPaymentMethod.PaymentMethodID = DbPaymentMethod.PaymentMethodID ");
sqlBuilder.Append(" where DbCompanyPaymentMethod.CompanyID= :companyID ");
//sqlBuilder.Append(" and DbCompany.PaymentType = :paymentType ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("companyID", typeof(long), companyID);
//queryParameterBuilder.AddParameterData("paymentType", typeof(string), paymentType);
queryParameterBuilder.FillParameters(query);
query.AddScalar("PaymentMethodID", NHibernateUtil.Int64);
query.AddScalar("PaymentMethodName", NHibernateUtil.String);
query.AddScalar("PaymentMethodCode", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(CompanyPaymentMethodResult))).List <CompanyPaymentMethodResult>());
}
public IList <DivisionLang> FindDivisionLangByDivisionId(short divisionId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select l.LanguageId as LanguageId, l.LanguageName as LanguageName, dl.DivisionId as DivisionId,");
sqlBuilder.Append(" dl.Id as DivisionLangId, dl.DivisionName as DivisionName, dl.Comment as Comment, dl.Active as Active");
sqlBuilder.Append(" from DbLanguage l left join SuDivisionLang dl on dl.LanguageId = l.LanguageId and dl.divisionId = :DivisionId");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("DivisionId", typeof(short), divisionId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("DivisionId", NHibernateUtil.Int16)
.AddScalar("DivisionLangId", NHibernateUtil.Int64)
.AddScalar("DivisionName", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.DivisionLang))).List <DivisionLang>());
}
public IList <SuRTEContentSearchResult> FindSuRTEContentByNodeId(short nodeId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" select l.LanguageId as LanguageId, l.LanguageName as LanguageName, ");
sqlBuilder.AppendLine(" c.Id as contentId, c.Header as Header, c.Content as Content, c.Comment as Comment, c.Active as Active");
sqlBuilder.AppendLine(" from DbLanguage l left join SuRTEContent c on c.LanguageId = l.LanguageId and c.NodeId = :NodeId");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("NodeId", typeof(short), nodeId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("ContentId", NHibernateUtil.Int16)
.AddScalar("Header", NHibernateUtil.String)
.AddScalar("Content", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.SuRTEContentSearchResult))).List <SuRTEContentSearchResult>());
}
public IList <StatusLang> FindStatusLangByStatusId(short statusId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select l.LanguageId , l.LanguageName , sl.StatusId,");
sqlBuilder.Append(" sl.StatusDesc , sl.Comment as Comment, sl.Active as Active");
sqlBuilder.Append(" from DbLanguage l left join DbStatusLang sl on sl.LanguageId = l.LanguageId");
sqlBuilder.Append(" and sl.StatusId = :StatusId");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("StatusId", typeof(short), statusId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("StatusID", NHibernateUtil.Int16)
.AddScalar("StatusDesc", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DB.DTO.ValueObject.StatusLang))).List <StatusLang>());
}
public IList <CountryLang> FindCountry(short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" DbCountry.CountryID AS CountryID ,");
sqlBuilder.Append(" DbCountry.CountryCode AS CountryCode ,");
sqlBuilder.Append(" DbCountryLang.CountryName AS CountryName ");
sqlBuilder.Append(" FROM DbCountry ");
sqlBuilder.Append(" LEFT JOIN DbCountryLang ON ");
sqlBuilder.Append(" DbCountry.CountryID = DbCountryLang.CountryID AND ");
sqlBuilder.Append(" DbCountryLang.LanguageID = :LanguageId ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LanguageId", typeof(Int16), languageId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("CountryID", NHibernateUtil.Int16);
query.AddScalar("CountryCode", NHibernateUtil.String);
query.AddScalar("CountryName", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(CountryLang)));
return(query.List <CountryLang>());
}
public IList <GlobalTranslateLang> FindTranslateLangByTranslateId(long translateId)
{
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");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("TranslateId", typeof(long), translateId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("LanguageName", NHibernateUtil.String)
.AddScalar("TranslateId", NHibernateUtil.Int64)
.AddScalar("TranslateLangId", NHibernateUtil.Int16)
.AddScalar("TranslateWord", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.GlobalTranslateLang))).List <GlobalTranslateLang>());
}
public CostCenterData GetCostCenterData(long expenseID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select distinct DbCostCenter.CostCenterCode, DbCostCenter.Description ");
sqlBuilder.Append("from FnExpenseDocument inner join ");
sqlBuilder.Append("FnExpenseInvoice on FnExpenseDocument.ExpenseID = FnExpenseInvoice.ExpenseID inner join ");
sqlBuilder.Append("FnExpenseInvoiceItem on FnExpenseInvoice.InvoiceID = FnExpenseInvoiceItem.InvoiceID inner join ");
sqlBuilder.Append("DbCostCenter on FnExpenseInvoiceItem.CostCenterID = DbCostCenter.CostCenterID ");
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("CostCenterCode", NHibernateUtil.String);
query.AddScalar("Description", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(CostCenterData)));
return(query.UniqueResult <CostCenterData>());
}
public void InsertData(DbSellingLetter SellingLetter)
{
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO DbSellingLetter (LetterID,DocumentID,CreBy,CreDate,UpdBy,UpdDate) ");
sql.Append(" VALUES ( :letterID , :documentID , :creBy , :creDate , :updBy , :updDate ) ");
QueryParameterBuilder param = new QueryParameterBuilder();
param.AddParameterData("letterID", typeof(long), SellingLetter.LetterID);
param.AddParameterData("documentID", typeof(long), SellingLetter.DocumentID);
param.AddParameterData("creBy", typeof(long), SellingLetter.CreBy);
param.AddParameterData("creDate", typeof(DateTime), SellingLetter.CreDate);
param.AddParameterData("updBy", typeof(long), SellingLetter.UpdBy);
param.AddParameterData("updDate", typeof(DateTime), SellingLetter.UpdDate);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
param.FillParameters(query);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public ISQLQuery FindVendorByVendorCriteria(VOVendor criteria, bool isCount, string sortExpession)
{
StringBuilder sqlBuilder = new StringBuilder();
if (isCount)
{
sqlBuilder.Append(" select count(*) as VendorCount ");
}
else
{
sqlBuilder.Append(" select VendorID, VendorCode, VendorBranch, Street, City, Country, PostalCode, VendorName, VendorTaxCode ");
}
sqlBuilder.Append(" from (");
sqlBuilder.Append(" select VendorID, VendorCode, TaxNo4 as VendorBranch, Street, City, Country, PostalCode, ");
sqlBuilder.Append(" isnull(VendorTitle,'') +' '+ isnull(VendorName1,'') +' '+ isnull(VendorName2,'') as VendorName , isnull(TaxNo3,'') as VendorTaxCode");
sqlBuilder.Append(" from DbVendor with (nolock) where Active = 1" + (string.IsNullOrEmpty(criteria.VendorTaxCode) ? string.Empty : " and TaxNo3 like :TaxCode"));
sqlBuilder.Append(" union select null as VendorID, VendorCode, VendorBranch,Street, City, Country, PostalCode, VendorName, VendorTaxCode ");
sqlBuilder.Append(" from DbOneTimeVendor with (nolock) where 1=1 " + (string.IsNullOrEmpty(criteria.VendorTaxCode) ? string.Empty : " and VendorTaxCode like :TaxCode "));
sqlBuilder.Append(" union select null as VendorID, t.VendorCode, t.VendorBranch,t.Street, t.City, t.Country, t.PostalCode, t.VendorName, t.VendorTaxCode ");
sqlBuilder.Append(" from FnExpenseInvoice t with (nolock) where t.Active = 1 and (convert(varchar,CreDate,103)= convert(varchar,:CreDate,103)) ");
sqlBuilder.Append(" and NULLIF (t.VendorCode, '') <> '' and NULLIF (t.VendorName, '') <> '' ");
sqlBuilder.Append(" and NULLIF (t.Street, '') <> '' and NULLIF (t.Country, '') <> '' and NULLIF (t.VendorTaxCode, '') <> '' " + (string.IsNullOrEmpty(criteria.VendorTaxCode) ? string.Empty : " and t.VendorTaxCode like :TaxCode "));
sqlBuilder.Append(" and t.VendorID = null ");
sqlBuilder.Append(" and not exists(SELECT 'X' FROM DbOneTimeVendor o WHERE o.VendorTaxCode = t.VendorTaxCode AND o.VendorBranch = t.VendorBranch) ");
sqlBuilder.Append(" )t1 ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("CreDate", typeof(DateTime), DateTime.Today);
StringBuilder whereClause = new StringBuilder();
if (!string.IsNullOrEmpty(criteria.VendorCode))
{
whereClause.Append(" and VendorCode like :VendorCode ");
parameterBuilder.AddParameterData("VendorCode", typeof(string), String.Format("%{0}%", criteria.VendorCode));
}
if (!string.IsNullOrEmpty(criteria.VendorName))
{
whereClause.Append(" and VendorName like :VendorName ");
parameterBuilder.AddParameterData("VendorName", typeof(string), String.Format("%{0}%", criteria.VendorName));
}
if (!string.IsNullOrEmpty(criteria.VendorTaxCode))
{
//whereClause.Append(" and VendorTaxCode like :TaxCode ");
parameterBuilder.AddParameterData("TaxCode", typeof(string), String.Format("%{0}%", criteria.VendorTaxCode));
}
if (!string.IsNullOrEmpty(criteria.VendorBranch))
{
whereClause.Append(" and VendorBranch like :VendorBranch ");
parameterBuilder.AddParameterData("VendorBranch", typeof(string), String.Format("%{0}%", criteria.VendorBranch));
}
if (criteria.ExcludeOneTime)
{
whereClause.Append(" and VendorID is not null ");
}
if (!string.IsNullOrEmpty(whereClause.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0}", whereClause.ToString()));
}
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpession))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpession));
}
else
{
sqlBuilder.Append(" order by VendorID, VendorCode, VendorName, VendorTaxCode");
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("VendorID", NHibernateUtil.Int64)
.AddScalar("VendorCode", NHibernateUtil.String)
.AddScalar("VendorBranch", NHibernateUtil.String)
.AddScalar("Street", NHibernateUtil.String)
.AddScalar("City", NHibernateUtil.String)
.AddScalar("Country", NHibernateUtil.String)
.AddScalar("PostalCode", NHibernateUtil.String)
.AddScalar("VendorName", NHibernateUtil.String)
.AddScalar("VendorTaxCode", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOVendor)));
}
else
{
query.AddScalar("VendorCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public ISQLQuery FindServiceTeamLocationByServiceTeamID(DbServiceTeam serviceTeam, bool isCount, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" sl.ServiceTeamLocationID as ServiceTeamLocationID,");
sqlBuilder.Append(" sl.ServiceTeamID as ServiceTeamID,");
sqlBuilder.Append(" sl.LocationID as LocationID,");
sqlBuilder.Append(" l.LocationCode as LocationCode,");
sqlBuilder.Append(" c.CompanyCode as CompanyCode,");
sqlBuilder.Append(" c.CompanyName as CompanyName,");
sqlBuilder.Append(" l.Description as Description,");
sqlBuilder.Append(" sl.Active as Active");
sqlBuilder.Append("");
sqlBuilder.Append(" FROM DbServiceTeamLocation sl LEFT OUTER JOIN DbLocation l ON sl.LocationID = l.LocationID ");
sqlBuilder.Append(" LEFT OUTER JOIN DbCompany c ON l.CompanyID = c.CompanyID ");
sqlBuilder.Append(" WHERE sl.ServiceTeamID = :ServiceTeamID ");
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.AppendLine(" ORDER BY c.CompanyName, l.Description, sl.Active ");
}
else
{
sqlBuilder.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
else
{
sqlBuilder.Append(" SELECT COUNT(sl.ServiceTeamLocationID) AS ServiceTeamLocationCount ");
sqlBuilder.Append(" FROM DbServiceTeamLocation sl LEFT OUTER JOIN DbLocation l ON sl.LocationID = l.LocationID ");
sqlBuilder.Append(" LEFT OUTER JOIN DbCompany c ON l.CompanyID = c.CompanyID ");
sqlBuilder.Append(" WHERE sl.ServiceTeamID = :ServiceTeamID ");
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ServiceTeamID", typeof(long), serviceTeam.ServiceTeamID);
queryParameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("ServiceTeamLocationID", NHibernateUtil.Int64);
query.AddScalar("ServiceTeamID", NHibernateUtil.Int64);
query.AddScalar("LocationID", NHibernateUtil.Int64);
query.AddScalar("LocationCode", NHibernateUtil.String);
query.AddScalar("CompanyCode", NHibernateUtil.String);
query.AddScalar("CompanyName", NHibernateUtil.String);
query.AddScalar("Description", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ServiceTeamLocationResult)));
}
else
{
query.AddScalar("ServiceTeamLocationCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public ISQLQuery FindInvoiceItemByExpenseID(long expenseId, short languageId, bool isCount, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append("select ExpenseCode, CostCenterCode, IONumber, Amount, LocalCurrencyAmount, MainCurrencyAmount from (");
}
else
{
sqlBuilder.Append(" select count(*) as Count from (");
}
sqlBuilder.Append(" select (ac.AccountCode + '-' + acl.AccountName) as ExpenseCode, cost.CostCenterCode as CostCenterCode, i.IONumber as IONumber, ");
sqlBuilder.Append(" sum(case when ISNULL(inv.IsVAT, 0) = 0 then ISNULL(item.Amount, 0) else ISNULL(item.Amount, 0) + ISNULL(item.NonDeductAmount, 0) end) as Amount, ");
sqlBuilder.Append(" sum(ISNULL(item.LocalCurrencyAmount, 0)) as LocalCurrencyAmount, ");
sqlBuilder.Append(" sum(ISNULL(item.MainCurrencyAmount, 0)) as MainCurrencyAmount ");
sqlBuilder.Append(" from FnExpenseInvoice inv with (nolock) ");
sqlBuilder.Append(" inner join FnExpenseInvoiceItem item with (nolock) on inv.InvoiceID = item.InvoiceID ");
sqlBuilder.Append(" left join DbAccount ac with (nolock) on item.AccountID = ac.AccountID ");
sqlBuilder.Append(" inner join DbAccountLang acl with (nolock) on ac.AccountID = acl.AccountID and acl.LanguageID = :LanguageID ");
sqlBuilder.Append(" left join DbInternalOrder i with (nolock) on i.IOID = item.IOID ");
sqlBuilder.Append(" inner join DbCostCenter cost with (nolock) on cost.CostCenterID = item.CostCenterID ");
sqlBuilder.Append(" left join DbCurrency currency with (nolock) on currency.CurrencyID = item.CurrencyID ");
sqlBuilder.Append(" where item.Active = 1 and inv.ExpenseID = :ExpenseID ");
sqlBuilder.Append(" group by ac.AccountCode,acl.AccountName,cost.CostCenterCode,i.IONumber )t ");
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by ExpenseCode, CostCenterCode, IONumber ");
}
}
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageID", typeof(short), languageId);
parameterBuilder.AddParameterData("ExpenseID", typeof(long), expenseId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("ExpenseCode", NHibernateUtil.String)
.AddScalar("CostCenterCode", NHibernateUtil.String)
.AddScalar("IONumber", NHibernateUtil.String)
//.AddScalar("Description", NHibernateUtil.String)
.AddScalar("Amount", NHibernateUtil.Double)
.AddScalar("LocalCurrencyAmount", NHibernateUtil.Double)
.AddScalar("MainCurrencyAmount", NHibernateUtil.Double);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOInvoiceItem)));
}
else
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
return(query);
}
public ISQLQuery FindVendorByCriteria(DbVendor Vendor, bool isCount, short languageId, string sortExpression, string VendorCode, string VendorName)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" VendorID AS VendorID ,");
sqlBuilder.Append(" VendorCode AS VendorCode ,");
sqlBuilder.Append(" VendorTitle AS VendorTitle ,");
sqlBuilder.Append(" VendorName1 AS VendorName1 ,");
sqlBuilder.Append(" VendorName2 AS VendorName2 ,");
sqlBuilder.Append(" Street AS Street ,");
sqlBuilder.Append(" City AS City ,");
sqlBuilder.Append(" Country AS Country ,");
sqlBuilder.Append(" PostalCode AS PostalCode ,");
sqlBuilder.Append(" TaxNo1 AS TaxNo1 ,");
sqlBuilder.Append(" TaxNo2 AS TaxNo2 ,");
sqlBuilder.Append(" TaxNo3 AS TaxNo3 ,");
sqlBuilder.Append(" TaxNo4 AS TaxNo4 ,");
sqlBuilder.Append(" BlockDelete AS BlockDelete ,");
sqlBuilder.Append(" BlockPost AS BlockPost ,");
sqlBuilder.Append(" Active AS Active ");
sqlBuilder.Append(" FROM DbVendor ");
sqlBuilder.Append(" WHERE 1=1 ");
if (VendorCode != null && VendorCode != "")
{
sqlBuilder.Append(" AND VendorCode LIKE :VendorCode ");
parameterBuilder.AddParameterData("VendorCode", typeof(string), "%" + VendorCode + "%");
}
if (VendorName != null && VendorName != "")
{
sqlBuilder.Append(" AND ( VendorName1 LIKE :VendorName1 ");
parameterBuilder.AddParameterData("VendorName1", typeof(string), "%" + VendorName + "%");
sqlBuilder.Append(" OR VendorName2 LIKE :VendorName2 )");
parameterBuilder.AddParameterData("VendorName2", typeof(string), "%" + VendorName + "%");
}
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.AppendLine(" ORDER BY VendorCode ");
}
else
{
sqlBuilder.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
else
{
sqlBuilder.Append(" SELECT COUNT(VendorCode) AS VendorCodeCount ");
sqlBuilder.Append(" FROM DbVendor ");
sqlBuilder.Append(" WHERE 1=1 ");
if (VendorCode != null && VendorCode != "")
{
sqlBuilder.Append(" AND VendorCode LIKE :VendorCode ");
parameterBuilder.AddParameterData("VendorCode", typeof(string), "%" + VendorCode + "%");
}
if (VendorName != null && VendorName != "")
{
sqlBuilder.Append(" AND VendorName1 LIKE :VendorName1 ");
parameterBuilder.AddParameterData("VendorName1", typeof(string), "%" + VendorName + "%");
sqlBuilder.Append(" OR VendorName2 LIKE :VendorName2 ");
parameterBuilder.AddParameterData("VendorName2", typeof(string), "%" + VendorName + "%");
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("VendorID", NHibernateUtil.Int32);
query.AddScalar("VendorCode", NHibernateUtil.String);
query.AddScalar("VendorTitle", NHibernateUtil.String);
query.AddScalar("VendorName1", NHibernateUtil.String);
query.AddScalar("VendorName2", NHibernateUtil.String);
query.AddScalar("Street", NHibernateUtil.String);
query.AddScalar("City", NHibernateUtil.String);
query.AddScalar("Country", NHibernateUtil.String);
query.AddScalar("PostalCode", NHibernateUtil.String);
query.AddScalar("TaxNo1", NHibernateUtil.String);
query.AddScalar("TaxNo2", NHibernateUtil.String);
query.AddScalar("TaxNo3", NHibernateUtil.String);
query.AddScalar("TaxNo4", NHibernateUtil.String);
query.AddScalar("BlockDelete", NHibernateUtil.Boolean);
query.AddScalar("BlockPost", NHibernateUtil.Boolean);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbVendor)));
}
else
{
query.AddScalar("VendorCodeCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public ISQLQuery FindPbByCriteria(VOPB pb, string sortExpression, bool isCount)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
if (!isCount)
{
sqlBuilder.Append(" select DbPB.PBID as Pbid, DbPB.PBCode as PBCode, DbPB.CompanyCode as CompanyCode, ");
sqlBuilder.Append(" DbPB.Active as Active, DbPB.PettyCashLimit as PettyCashLimit,DbPB.BlockPost as BlockPost , ");
sqlBuilder.Append(" DbPBLang.Description as Description ");
}
else
{
sqlBuilder.Append(" select count(DbPB.PBID) as Count ");
}
sqlBuilder.Append(" from DbPB ");
sqlBuilder.Append(" left join DbPBLang on DbPB.PBID=DbPBLang.PBID and DbPBLang.LanguageID=:langId ");
queryParameterBuilder.AddParameterData("langId", typeof(short), UserAccount.CurrentLanguageID);
#region WhereClause
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(pb.Description))
{
whereClauseBuilder.Append(" and DbPBLang.Description Like :Description ");
queryParameterBuilder.AddParameterData("Description", typeof(string), String.Format("%{0}%", pb.Description));
}
if (!string.IsNullOrEmpty(pb.PBCode))
{
whereClauseBuilder.Append(" and DbPB.PBCode Like :CompanyCode ");
queryParameterBuilder.AddParameterData("CompanyCode", typeof(string), String.Format("%{0}%", pb.PBCode));
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
}
#endregion
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by DbPB.PBCode,DbPB.CompanyCode,DbPBLang.Description,DbPB.PettyCashLimit,DbPB.BlockPost ");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("Pbid", NHibernateUtil.Int64)
.AddScalar("PBCode", NHibernateUtil.String)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("PettyCashLimit", NHibernateUtil.Double)
.AddScalar("BlockPost", NHibernateUtil.Boolean)
.AddScalar("CompanyCode", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB)));
}
return(query);
}
public ISQLQuery FindByEmailLogCriteria(string emailType, string sendDate, string requestNo, int status, bool isCount, string sortExpression, short currentLanguage)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" select EmailLogID,RequestNo,EmailType+' ('+dbStatusLang.StatusDesc+')' as EmailType,SendDate,ToEmail,CCEmail, ");
sqlBuilder.Append(" (case when SuEMailLog.status = 1 then 'Success' ");
sqlBuilder.Append(" when SuEMailLog.status = 2 then 'Fail' ");
sqlBuilder.Append(" else '' end) as StatusName, Remark ");
sqlBuilder.Append(" from SuEMailLog with (nolock) ");
sqlBuilder.Append(" LEFT JOIN dbStatus with (nolock) On SuEMailLog.EmailType = dbStatus.STATUS ");
sqlBuilder.Append(" LEFT JOIN dbStatusLang with (nolock) On dbStatus.StatusID = dbStatusLang.StatusID ");
sqlBuilder.Append(" WHERE SuEMailLog.Active =1 AND dbStatusLang.LanguageID = :Language");
queryParameterBuilder.AddParameterData("Language", typeof(string), currentLanguage);
if (!emailType.Equals(string.Empty))
{
sqlBuilder.Append(" AND SuEMailLog.EmailType = :emailType");
queryParameterBuilder.AddParameterData("emailType", typeof(string), emailType);
}
if (!sendDate.Equals(string.Empty))
{
sqlBuilder.Append(" AND Convert(varchar,SuEMailLog.SendDate,103) = Convert(varchar,:sendDate,103)");
queryParameterBuilder.AddParameterData("sendDate", typeof(string), sendDate);
}
if (!requestNo.Equals(string.Empty))
{
sqlBuilder.Append(" AND SuEMailLog.RequestNo = :requestNo");
queryParameterBuilder.AddParameterData("requestNo", typeof(string), requestNo);
}
if (status != 0)
{
sqlBuilder.Append(" AND SuEMailLog.Status = :status");
queryParameterBuilder.AddParameterData("status", typeof(int), status);
}
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.AppendLine(" ORDER BY SendDate desc, RequestNo");
}
else
{
sqlBuilder.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
else
{
sqlBuilder.Append(" SELECT COUNT(EmailLogId) AS LogCount FROM SuEMailLog");
sqlBuilder.Append(" LEFT JOIN dbStatus On SuEMailLog.EmailType = dbStatus.STATUS ");
sqlBuilder.Append(" LEFT JOIN dbStatusLang On dbStatus.StatusID = dbStatusLang.StatusID ");
sqlBuilder.Append(" WHERE SuEMailLog.Active =1 AND dbStatusLang.LanguageID = :Language");
queryParameterBuilder.AddParameterData("Language", typeof(string), currentLanguage);
if (!emailType.Equals(string.Empty))
{
sqlBuilder.Append(" AND SuEMailLog.EmailType = :emailType");
queryParameterBuilder.AddParameterData("emailType", typeof(string), emailType);
}
if (!sendDate.Equals(string.Empty))
{
sqlBuilder.Append(" AND Convert(varchar,SuEMailLog.SendDate,103) = Convert(varchar,:sendDate,103)");
queryParameterBuilder.AddParameterData("sendDate", typeof(string), sendDate);
}
if (!requestNo.Equals(string.Empty))
{
sqlBuilder.Append(" AND SuEMailLog.RequestNo = :requestNo");
queryParameterBuilder.AddParameterData("requestNo", typeof(string), requestNo);
}
if (status != 0)
{
sqlBuilder.Append(" AND SuEMailLog.Status = :status");
queryParameterBuilder.AddParameterData("status", typeof(int), status);
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("RequestNo", NHibernateUtil.String);
query.AddScalar("EmailType", NHibernateUtil.String);
query.AddScalar("SendDate", NHibernateUtil.DateTime);
query.AddScalar("ToEmail", NHibernateUtil.String);
query.AddScalar("CCEmail", NHibernateUtil.String);
query.AddScalar("StatusName", NHibernateUtil.String);
query.AddScalar("Remark", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOEmailLog)));
}
else
{
query.AddScalar("LogCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public ISQLQuery FindExpenseGroupByCriteria(VOExpenseGroup criteria, bool isCount, string sortExpression)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
if (!isCount)
{
sqlBuilder.Append(" select DbExpenseGroup.ExpenseGroupID as ExpenseGroupID , DbExpenseGroup.ExpenseGroupCode as ExpenseGroupCode, ");
sqlBuilder.Append(" DbExpenseGroup.Active as Active , ");
sqlBuilder.Append(" DbExpenseGroupLang.ExpenseGroupLangID as ExpenseGroupLangID, DbExpenseGroupLang.LanguageID as LanguageID ");
sqlBuilder.Append(" , DbExpenseGroupLang.Description as Description ");
}
else
{
sqlBuilder.Append(" select count(DbExpenseGroup.ExpenseGroupID ) as Count ");
}
sqlBuilder.Append(" from DbExpenseGroup left join DbExpenseGroupLang ");
sqlBuilder.Append(" on DbExpenseGroup.ExpenseGroupID=DbExpenseGroupLang.ExpenseGroupID and DbExpenseGroupLang.LanguageID= :langId ");
queryParameterBuilder.AddParameterData("langId", typeof(short), UserAccount.CurrentLanguageID);
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(criteria.Description))
{
whereClauseBuilder.Append(" and DbExpenseGroupLang.Description like :Description ");
queryParameterBuilder.AddParameterData("Description", typeof(string), String.Format("%{0}%", criteria.Description));
}
if (!string.IsNullOrEmpty(criteria.ExpenseGroupCode))
{
whereClauseBuilder.Append(" and DbExpenseGroup.ExpenseGroupCode like :ExpenseGroupCode ");
queryParameterBuilder.AddParameterData("ExpenseGroupCode", typeof(string), String.Format("%{0}%", criteria.ExpenseGroupCode));
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
}
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by DbExpenseGroup.ExpenseGroupCode,DbExpenseGroupLang.Description");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("ExpenseGroupID", NHibernateUtil.Int64)
.AddScalar("ExpenseGroupLangID", NHibernateUtil.Int64)
.AddScalar("LanguageID", NHibernateUtil.Int16)
.AddScalar("ExpenseGroupCode", NHibernateUtil.String)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOExpenseGroup)));
}
return(query);
}
public ISQLQuery FindDataMonitoringDocumentQuery(string comCode, string colnumber, string BuName, short languageID, string sortExpression, bool isCount)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
bool isMoreThanColumn = false;
int from = 0;
int to = 0;
if (colnumber == "1")
{
from = Convert.ToInt32(ParameterServices.Column1_OverdueDayFrom);
to = Convert.ToInt32(ParameterServices.Column1_OverdueDayTo);
}
else if (colnumber == "2")
{
from = Convert.ToInt32(ParameterServices.Column2_OverdueDayFrom);
to = Convert.ToInt32(ParameterServices.Column2_OverdueDayTo);
}
else if (colnumber == "3")
{
from = Convert.ToInt32(ParameterServices.Column3_OverdueDayFrom);
to = Convert.ToInt32(ParameterServices.Column3_OverdueDayTo);
}
else if (colnumber == "4")
{
from = Convert.ToInt32(ParameterServices.Column4_OverdueDayFrom);
to = Convert.ToInt32(ParameterServices.Column4_OverdueDayTo);
}
else
{
isMoreThanColumn = true;
from = Convert.ToInt32(ParameterServices.Column4_OverdueDayTo);
//to = Convert.ToInt32(ParameterServices.Column5_OverdueDayTo);
}
if (isCount)
{
sqlBuilder.Append("SELECT count(*) as Count ");
}
else
{
sqlBuilder.Append("SELECT DocumentNo,ReferenceNo,DocumentDate,CacheCurrentStateName,Subject,CacheCreatorName,CacheRequesterName,CacheAmountLocalCurrency,CacheAmountMainCurrency,CacheAmountTHB,ApproveDate,WorkflowID,Type, CacheAttachment, CacheBoxID,ReceiveDocumentDate,IsVerifyWithImage ");
}
sqlBuilder.Append(" FROM ( ");
sqlBuilder.Append(" SELECT a.DocumentNo,a.ReferenceNo,a.DocumentDate,l.DisplayName as CacheCurrentStateName,a.Subject,a.CacheCreatorName,a.CacheRequesterName,a.CacheAmountLocalCurrency,a.CacheAmountMainCurrency,a.CacheAmountTHB,a.ApproveDate,a.WorkflowID,a.Type, isnull(a.CacheAttachment,0) as CacheAttachment, a.CacheBoxID ,a.ReceiveDocumentDate,a.IsVerifyWithImage AS IsVerifyWithImage");
sqlBuilder.Append(" FROM [dbo].[View_Document_Mornitoring_Inbox] AS a ");
sqlBuilder.Append(" left join WorkFlowStateLang l with (nolock) on l.WorkFlowStateID = a.CurrentState and l.LanguageID = :languageID ");
sqlBuilder.Append(" WHERE a.CompanyCode = coalesce(nullif(:comCode,''), a.CompanyCode) ");
sqlBuilder.Append(" AND a.BuName = coalesce( nullif(:BuName,''), a.BuName) ");
if (!isMoreThanColumn)
{
sqlBuilder.Append(" AND (Case WHEN ISNULL(a.IsVerifyWithImage,0) = 1 THEN DATEDIFF(day,a.ApproveDate, getdate()) ELSE DATEDIFF(day,a.ReceiveDocumentDate, getdate()) END) BETWEEN ");
sqlBuilder.Append(" :fromDate AND :toDate ");
}
else
{
sqlBuilder.Append(" AND (Case WHEN ISNULL(a.IsVerifyWithImage,0) = 1 THEN DATEDIFF(day,a.ApproveDate, getdate()) ELSE DATEDIFF(day,a.ReceiveDocumentDate, getdate()) END) > :fromDate ");
}
sqlBuilder.Append(" ) as t1 ");
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(string.Format(" Order by {0} ", sortExpression));
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.AddParameterData("languageID", typeof(short), languageID);
queryParameterBuilder.AddParameterData("comCode", typeof(string), comCode);
queryParameterBuilder.AddParameterData("BuName", typeof(string), BuName);
queryParameterBuilder.AddParameterData("fromDate", typeof(int), from);
if (!isMoreThanColumn)
{
queryParameterBuilder.AddParameterData("toDate", typeof(int), to);
}
queryParameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("DocumentNo", NHibernateUtil.String)
.AddScalar("ReferenceNo", NHibernateUtil.String)
.AddScalar("DocumentDate", NHibernateUtil.DateTime)
.AddScalar("CacheCurrentStateName", NHibernateUtil.String)
.AddScalar("Subject", NHibernateUtil.String)
.AddScalar("CacheCreatorName", NHibernateUtil.String)
.AddScalar("CacheRequesterName", NHibernateUtil.String)
.AddScalar("CacheAmountLocalCurrency", NHibernateUtil.Double)
.AddScalar("CacheAmountMainCurrency", NHibernateUtil.Double)
.AddScalar("CacheAmountTHB", NHibernateUtil.Double)
.AddScalar("ApproveDate", NHibernateUtil.DateTime)
.AddScalar("WorkflowID", NHibernateUtil.Int64)
.AddScalar("Type", NHibernateUtil.String)
.AddScalar("CacheAttachment", NHibernateUtil.Boolean)
.AddScalar("CacheBoxID", NHibernateUtil.String)
.AddScalar("ReceiveDocumentDate", NHibernateUtil.DateTime)
.AddScalar("IsVerifyWithImage", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbMonitoringDocument)));
}
else
{
query.AddScalar("Count", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
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 ISQLQuery FindDataByIOCriteria(DbInternalOrder io, bool isCount, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
if (!isCount)
{
sqlBuilder.Append(" SELECT io.IOID as IOID , io.IONumber as IONumber, io.IOType as IOType, io.IOText as IOText, ");
sqlBuilder.Append(" io.CostCenterID as CostCenterID , io.CostCenterCode as CostCenterCode, io.CompanyID as CompanyID, ");
sqlBuilder.Append(" io.CompanyCode as CompanyCode, io.EffectiveDate as EffectiveDate, io.ExpireDate as ExpireDate, ");
sqlBuilder.Append(" io.Active as Active ");
}
else
{
sqlBuilder.Append(" select count(io.IOID) as Count ");
}
sqlBuilder.Append(" from DbInternalOrder io ");
#region WhereClause
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(io.IONumber))
{
whereClauseBuilder.Append(" and io.IONumber Like :IONumber ");
queryParameterBuilder.AddParameterData("IONumber", typeof(string), String.Format("%{0}%", io.IONumber));
}
if (!string.IsNullOrEmpty(io.IOText))
{
whereClauseBuilder.Append(" and io.IOText Like :IOText ");
queryParameterBuilder.AddParameterData("IOText", typeof(string), String.Format("%{0}%", io.IOText));
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} and io.Active = 1 ", whereClauseBuilder.ToString()));
}
#endregion
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by IOID, IONumber, IOText,IOType,CostCenterID,CostCenterCode, ");
sqlBuilder.Append(" CompanyID,CompanyCode,EffectiveDate,ExpireDate ");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("IOID", NHibernateUtil.Int64)
.AddScalar("IONumber", NHibernateUtil.String)
.AddScalar("IOText", NHibernateUtil.String)
.AddScalar("IOType", NHibernateUtil.String)
.AddScalar("CostCenterID", NHibernateUtil.Int64)
.AddScalar("CostCenterCode", NHibernateUtil.String)
.AddScalar("CompanyID", NHibernateUtil.Int64)
.AddScalar("CompanyCode", NHibernateUtil.String)
.AddScalar("EffectiveDate", NHibernateUtil.DateTime)
.AddScalar("ExpireDate", NHibernateUtil.DateTime)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbInternalOrder)));
}
return(query);
}
public ISQLQuery FindByIOCriteria(DbInternalOrder io, bool isCount, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
if (!isCount)
{
sqlBuilder.Append(" select io.IOID as IOID, io.IONumber as IONumber, io.IOText as IOText ");
}
else
{
sqlBuilder.Append(" select count(io.IOID) as Count ");
}
sqlBuilder.Append(" from DbInternalOrder io ");
#region WhereClause
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(io.IONumber))
{
whereClauseBuilder.Append(" and io.IONumber Like :IONumber ");
queryParameterBuilder.AddParameterData("IONumber", typeof(string), String.Format("%{0}%", io.IONumber));
}
if (!string.IsNullOrEmpty(io.IOText))
{
whereClauseBuilder.Append(" and io.IOText Like :IOText ");
queryParameterBuilder.AddParameterData("IOText", typeof(string), String.Format("%{0}%", io.IOText));
}
//if (io.CompanyID.HasValue || (io.CompanyID.Value != 0))
if (io.CompanyID.Value > 0)
{
//sqlBuilder.Append(" inner join DbCompany com on com.CompanyCode = io.CompanyCode ");
whereClauseBuilder.Append(" and io.CompanyID = :CompanyID ");
queryParameterBuilder.AddParameterData("CompanyID", typeof(long), io.CompanyID);
}
//if (!string.IsNullOrEmpty(io.CostCenterCode) || (io.CostCenterID != 0))
if (io.CostCenterID != null)
{
//sqlBuilder.Append(" inner join DbCostCenter cost on cost.CostCenterCode = io.CostCenterCode ");
whereClauseBuilder.Append(" and (io.CostCenterID = :CostCenterID or io.CostCenterID is null) ");
queryParameterBuilder.AddParameterData("CostCenterID", typeof(long), io.CostCenterID);
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} and io.Active = 1 ", whereClauseBuilder.ToString()));
}
#endregion
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by IOID, IONumber, IOText ");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("IOID", NHibernateUtil.Int64)
.AddScalar("IONumber", NHibernateUtil.String)
.AddScalar("IOText", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbInternalOrder)));
}
return(query);
}