public object MakeDataSource(ISQLQuery query)
{
IList lista = this.GetDataSource(query);
if (lista == null)
return null;
object firstEl = lista[0];
Type objType = firstEl.GetType();
if (objType.IsArray)
{
object[] columns = firstEl as object[];
if (columns == null)
return null;
this.aliases = new string[columns.Length];
this.aliasTypes = new IType[columns.Length];
for (int index = 0; index < columns.Length; index++)
{
this.aliases[index] = (index + 1).ToString();
this.aliasTypes[index] = NHibernateUtil.Object;
}
return this.GetDataSourceTable(lista);
}
return lista;
}
//--------------------------------------------------------------------------------
public static IList ExecQuery(string queryString)
{
using (ISession iSession = NHSessionManager.GetSession())
{
ISQLQuery query = iSession.CreateSQLQuery(queryString);
IList genericList = query.List();
if (genericList.Count == 0)
{
return(new ArrayList());
}
Type t = CreateType(genericList[0] as object[]);
if (t == null)
{
return(new ArrayList());
}
for (int i = 0; i < genericList.Count; i++)
{
genericList[i] = CreateTypeInstance(t, genericList[i] as object[]);
}
return(genericList);
}
}
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 <WorkFlowStateEvent> FindWorkFlowStateEvent(string WorkFlowStateName, string WorkFlowStateEventName, int WorkFlowTypeID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT t2.WorkFlowStateEventID AS WorkFlowStateEventID ");
strQuery.AppendLine(" FROM WorkFlowState t1 ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEvent t2 ");
strQuery.AppendLine(" ON t1.WorkFlowStateID = t2.WorkFlowStateID ");
strQuery.AppendLine(" WHERE t1.[Name] = :WorkFlowStateEventName ");
strQuery.AppendLine(" AND t2.[Name] = :WorkFlowStateName ");
strQuery.AppendLine(" AND t1.WorkFlowTypeID = :WorkFlowTypeID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("WorkFlowStateName", WorkFlowStateName);
query.SetString("WorkFlowStateEventName", WorkFlowStateEventName);
query.SetInt32("WorkFlowTypeID", WorkFlowTypeID);
query.AddScalar("WorkFlowStateEventID", NHibernateUtil.Int32);
query.SetResultTransformer(Transformers.AliasToBean(typeof(WorkFlowStateEvent)));
return(query.List <WorkFlowStateEvent>());
}
public IList <VORejectReasonLang> FindRejectReasonByDocumentTypeIDAndLanguageID(int documentTypeID, short languageID)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT r.ReasonID AS ReasonID , r.ReasonCode AS ReasonCode , rl.ReasonDetail AS ReasonDetail ");
sql.Append("FROM DbRejectReason AS r ");
sql.Append("INNER JOIN DbRejectReasonLang AS rl ON rl.ReasonID = r.ReasonID ");
sql.Append("WHERE r.DocumentTypeID =:documentTypeID AND rl.LanguageID =:languageID AND r.Active = 1 ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameter = new QueryParameterBuilder();
queryParameter.AddParameterData("documentTypeID", typeof(int), documentTypeID);
queryParameter.AddParameterData("languageID", typeof(int), languageID);
queryParameter.FillParameters(query);
query.AddScalar("ReasonID", NHibernateUtil.Int16);
query.AddScalar("ReasonCode", NHibernateUtil.String);
query.AddScalar("ReasonDetail", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VORejectReasonLang))).List <VORejectReasonLang>());
}
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 IList <TranslatedListItem> FindCompanyCriteriaShowIDJoinName()
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" CompanyID AS ID, ");
sqlBuilder.Append(" '[ ' + CONVERT(VARCHAR,CompanyCode) + ' ] ' + CompanyName AS Symbol ");
sqlBuilder.Append(" FROM ");
sqlBuilder.Append(" DbCompany ");
sqlBuilder.Append(" WHERE Active = 1 ORDER BY CompanyCode");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.FillParameters(query);
query.AddScalar("ID", NHibernateUtil.Int16);
query.AddScalar("Symbol", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem)));
return(query.List <TranslatedListItem>());
}
public void RunScript(ISessionFactory sessionFactory)
{
string[] parts = Script.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
using (ISession session = sessionFactory.OpenSession())
{
foreach (var queryString in parts)
{
if (string.IsNullOrEmpty(queryString.Trim()))
{
continue;
}
using (ITransaction tx = session.BeginTransaction())
{
ISQLQuery query = session.CreateSQLQuery(queryString);
query.SetTimeout(600);
query.UniqueResult();
tx.Commit();
}
}
}
}
public ValidateMilage GetMileageItemForValidationCheckLength(long RequesterId, string CarLicenseNo, DateTime travelDate, long expId, Double meter)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select a.DocumentNo from Document a ");
sqlBuilder.Append(" inner join [dbo].[FnExpenseDocument] b on a.DocumentID = b.DocumentID ");
sqlBuilder.Append(" inner join [dbo].FnExpenseMileage c on b.ExpenseID = c.ExpenseID ");
sqlBuilder.Append(" inner join [dbo].FnExpenseMileageItem d on c.ExpenseMileageID = d.ExpenseMileageID ");
sqlBuilder.Append(" where a.CacheCurrentStateName <> 'Cancel' and c.[Owner] = 'EMP' ");
sqlBuilder.Append(" and a.RequesterID = :RequesterID and c.CarLicenseNo = :CarLicenseNo and d.TravelDate = :travelDate and b.ExpenseID <> :expId ");
sqlBuilder.Append(" and (d.CarMeterStart <= :meter and d.CarMeterEnd > :meter) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("RequesterID", typeof(long), RequesterId);
queryParameterBuilder.AddParameterData("CarLicenseNo", typeof(string), CarLicenseNo);
queryParameterBuilder.AddParameterData("travelDate", typeof(DateTime), travelDate);
queryParameterBuilder.AddParameterData("expId", typeof(long), expId);
queryParameterBuilder.AddParameterData("meter", typeof(Double), meter);
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ValidateMilage))).List <ValidateMilage>().FirstOrDefault());
}
public static IList GetObjectList(string sql, string filedname, out string msgException)
{
IList result = null;
lock (lockObj)
{
ISession session = NHibernateHelper.GetCurrentSession();
msgException = string.Empty;
try
{
ISQLQuery q = session.CreateSQLQuery(sql);
q.AddScalar(filedname, NHibernateUtil.String);
result = q.List();
return(result);
}
catch (Exception ex)
{
msgException = ex.StackTrace;
LogHelper.Log(ex);
return(null);
}
}
}
public WorkFlowState FindWorkFlowStateIDByTypeIDAndStateName(int workFlowTypeID, string Name)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM WorkFlowState as w ");
//sql.Append("LEFT JOIN WorkFlowStateLang as wl on wl.WorkFlowStateID = w.WorkFlowStateID ");
//sql.Append("Where wl.LanguageID =:LanguageID AND w.WorkFlowTypeID =:WorkFlowTypeID AND wl.DisplayName =:DisplayName ");
sql.Append("Where w.WorkFlowTypeID =:WorkFlowTypeID AND w.Name =:Name ");
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
//queryParameterBuilder.AddParameterData("LanguageID",typeof(long),languageID);
queryParameterBuilder.AddParameterData("WorkFlowTypeID", typeof(int), workFlowTypeID);
queryParameterBuilder.AddParameterData("Name", typeof(string), Name);
queryParameterBuilder.FillParameters(query);
return(query.AddEntity(typeof(WorkFlowState)).UniqueResult <WorkFlowState>());
}
static void Main(string[] args)
{
try
{
using (ISession session = OpenSession())
{
ISQLQuery
query = (ISQLQuery)session.CreateSQLQuery("select ID, Name from Staff").SetResultTransformer(Transformers.AliasToBean(typeof(Staff))); //.AddEntity(typeof(Staff));
IList <Staff>
_List_Staff_ = query.List <Staff>();
foreach (Staff s in _List_Staff_)
{
Console.WriteLine("{0} {1}", s.ID, s.Name);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public IList <SuRTEContentSearchResult> FindSuRTEContentByContentIdLanguageId(short contentId, short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" select c.LanguageId as LanguageId, c.Id as contentId, c.Header as Header, c.Content as Content, c.Comment as Comment, c.Active as Active");
sqlBuilder.AppendLine(" from SuRTEContent c where c.Id = :ContentId and c.LanguageId = :LanguageId");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId);
parameterBuilder.AddParameterData("ContentId", typeof(short), contentId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.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 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());
}
}
/// <summary>
/// IsObjectExist
/// </summary>
/// <param name="queryString"> SELECT VRF_ID FROM v_recordingfile WHERE VRF_GUID = @guid</param>
/// <param name="idColumnName">VRF_ID</param>
/// <param name="msgException"></param>
/// <returns></returns>
public static bool IsObjectExist(string queryString, string idColumnName, out string msgException)
{
IList result = null;
lock (lockObj)
{
ISession session = NHibernateHelper.GetCurrentSession();
msgException = string.Empty;
try
{
ISQLQuery q = session.CreateSQLQuery(queryString);
q.AddScalar(idColumnName, NHibernateUtil.Int32);
result = q.List();
return(result != null && result.Count > 0);
}
catch (Exception ex)
{
msgException = ex.StackTrace;
LogHelper.Log(ex);
return(false);
}
}
}
public static int ObtienePreguntaFinEncuesta(int IdEncuesta)
{
string strSQL = string.Empty;
Azteca.Utility.Security.Rijndael _ChyperRijndael = new Azteca.Utility.Security.Rijndael();
ISession session = NHibernateHelperORACLE.GetSession();
int IdPreg = -1;
strSQL += " SELECT MIN (id_pregunta)IDPreg ";
strSQL += " FROM seml_the_preguntas ";
strSQL += " WHERE id_encuesta = " + IdEncuesta;
strSQL += " AND preg_estatus = 'A' ";
try
{
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("IDPreg", NHibernateUtil.Int32);//0
IList lista = consultaIQRY.List();
if (lista.Count > 0)
{
IdPreg = System.Convert.ToInt32(lista[0].ToString());
}
}
catch (Exception ex)
{
return(IdPreg);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
return(IdPreg);
}
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 UploadCustomerForm()
{
InitializeComponent();
string sql = "select DISTINCT(province) from xcar_shop ";
ISQLQuery isql = NHibernateHelper.CreateSQLQuery(sql);
var ds = isql.List <string>();
foreach (var item in ds)
{
this.cobProvince.Items.Add(item);
}
sql = "select text from xcar_buytime ";
isql = NHibernateHelper.CreateSQLQuery(sql);
ds = isql.List <string>();
foreach (var item in ds)
{
this.cobBuytime.Items.Add(item);
}
sql = "select text from xcar_drivercs ";
isql = NHibernateHelper.CreateSQLQuery(sql);
ds = isql.List <string>();
foreach (var item in ds)
{
this.cobDrivercs.Items.Add(item);
}
this.cobDrivercs.SelectedIndex = 0;
this.cobBuytime.SelectedIndex = 0;
reflashDgvCustomer();
updateGroupBox();
timer1.Stop();
}
public ArrayList ReadHtmlList(String userId, String[] keywordArray)
{
builder.Clear();
builder
.AppendFormat(" SELECT HTML_URL")
.AppendFormat(" FROM")
.AppendFormat(" (");
for (int i = 0; i < keywordArray.Length; i++)
{
if (i > 0)
{
builder.AppendFormat(" UNION");
}
builder
.AppendFormat(" SELECT A.HTML_URL")
.AppendFormat(" , DENSE_RANK() OVER(ORDER BY B.HTML_WD_CNT) HTML_SCORE")
.AppendFormat(" FROM TBL_HTML A")
.AppendFormat(" INNER JOIN TBL_HTML_WORD B")
.AppendFormat(" ON B.USR_ID = A.USR_ID")
.AppendFormat(" AND B.HTML_ID = A.HTML_ID")
.AppendFormat(" AND LOWER(B.HTML_WD) LIKE LOWER('{0}') + '%'", keywordArray[i].Replace("\'", ""))
.AppendFormat(" WHERE A.USR_ID = :userId");
}
builder
.AppendFormat(" ) A")
.AppendFormat(" GROUP BY HTML_URL")
.AppendFormat(" ORDER BY MAX(HTML_SCORE) DESC");
ISQLQuery query = Session.CreateSQLQuery(builder.ToString());
query.SetParameter("userId", userId);
return((ArrayList)query.List());
}
private IQuery addDateFilterAndSortingToQuery(IQuery sourceQuery)
{
string modifiedQueryString = sourceQuery.QueryString;
bool dateFilterExists = false;
//добавляем фильтр по дате
if (StartPeriod != DateTime.MinValue && EndPeriod != DateTime.MinValue)
{
dateFilterExists = true;
modifiedQueryString += " and test_datetime between :startPeriod and :endPeriod";
}
//добавляем опции сортировки
if (SortOptions != null)
{
modifiedQueryString += GetSortQueryEnding(SortOptions);
}
ISQLQuery modifiedQuery = _session.CreateSQLQuery(modifiedQueryString);
FieldInfo returnTypesFi = sourceQuery.GetType().GetField("queryReturns", BindingFlags.NonPublic | BindingFlags.Instance);
//SQLQueryScalarReturn
ArrayList returns = (ArrayList)returnTypesFi.GetValue(sourceQuery);
foreach (object value in returns)
{
modifiedQuery.AddScalar(((SQLQueryScalarReturn)value).ColumnAlias, ((SQLQueryScalarReturn)value).Type);
}
if (dateFilterExists)
{
modifiedQuery.SetDateTime("startPeriod", StartPeriod);
modifiedQuery.SetDateTime("endPeriod", EndPeriod);
}
return(modifiedQuery);
}
public IList <DbSapInstance> FindALL()
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * From DbSapInstance ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.AddScalar("Code", NHibernateUtil.String);
query.AddScalar("AliasName", NHibernateUtil.String);
query.AddScalar("SystemID", NHibernateUtil.String);
query.AddScalar("Client", NHibernateUtil.String);
query.AddScalar("UserName", NHibernateUtil.String);
query.AddScalar("Password", NHibernateUtil.String);
query.AddScalar("Language", NHibernateUtil.String);
query.AddScalar("SystemNumber", NHibernateUtil.String);
query.AddScalar("MsgServerHost", NHibernateUtil.String);
query.AddScalar("LogonGroup", NHibernateUtil.String);
query.AddScalar("UserCPIC", NHibernateUtil.String);
query.AddScalar("DocTypeExpPostingDM", NHibernateUtil.String);
query.AddScalar("DocTypeExpRmtPostingDM", NHibernateUtil.String);
query.AddScalar("DocTypeExpPostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeExpRmtPostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeExpICPostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeAdvancePostingDM", NHibernateUtil.String);
query.AddScalar("DocTypeAdvancePostingFR", NHibernateUtil.String);
query.AddScalar("DocTypeRmtPosting", NHibernateUtil.String);
query.AddScalar("DocTypeFixedAdvancePosting", NHibernateUtil.String);
query.AddScalar("DocTypeFixedAdvanceReturnPosting", NHibernateUtil.String);
query.AddScalar("UpdBy", NHibernateUtil.String);
query.AddScalar("UpdDate", NHibernateUtil.String);
query.AddScalar("CreBy", NHibernateUtil.String);
query.AddScalar("CreDate", NHibernateUtil.String);
query.AddScalar("UpdPgm", NHibernateUtil.String);
query.AddScalar("RowVersion", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbSapInstance))).List <DbSapInstance>());
}
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>());
}
private void button49_Click(object sender, EventArgs e)
{
try
{
ISession s = DataLayer.GetSession();
ISQLQuery q = s.CreateSQLQuery("SELECT O.* FROM Objekat O");
q.AddEntity(typeof(Objekat));
IList <Objekat> objekti = q.List <Objekat>();
foreach (Objekat o in objekti)
{
MessageBox.Show(o.TipObjekta.ToString());
}
s.Close();
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
}
private void button48_Click(object sender, EventArgs e)
{
try
{
ISession s = DataLayer.GetSession();
ISQLQuery q = s.CreateSQLQuery("SELECT O.* FROM Vestina O");
q.AddEntity(typeof(Vestina));
IList <Vestina> vestine = q.List <Vestina>();
foreach (Vestina o in vestine)
{
MessageBox.Show(o.Naziv.ToString());
}
s.Close();
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
}
private void button28_Click(object sender, EventArgs e)
{
try
{
ISession s = DataLayer.GetSession();
ISQLQuery q = s.CreateSQLQuery("SELECT O.* FROM ORGANIZATOR O");
q.AddEntity(typeof(Organizator));
IList <Organizator> organizatori = q.List <Organizator>();
foreach (Organizator o in organizatori)
{
MessageBox.Show(o.maticni_broj.ToString() + " - " + o.ime + " " + o.prezime);
}
s.Close();
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
}
public bool IsDuplicateCostCenterCode(DbCostCenter costCenter)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select count(1) as CostCenterCount ");
sqlBuilder.Append("from dbcostcenter ");
sqlBuilder.Append("where costcentercode = :costcentercode ");
sqlBuilder.Append("and costcenterid <> :costcenterid ");
QueryParameterBuilder param = new QueryParameterBuilder();
param.AddParameterData("costcentercode", typeof(string), costCenter.CostCenterCode);
param.AddParameterData("costcenterid", typeof(long), costCenter.CostCenterID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
param.FillParameters(query);
query.AddScalar("CostCenterCount", NHibernateUtil.Int32);
int i = (int)query.UniqueResult();
if (i > 0)
{
return(true);
}
return(false);
}
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 VOPB GetDescription(long pbID, int languageID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select DbPB.PBCode as PBCode, DbPBLang.Description as Description,DbPB.CompanyID as CompanyID ");
sqlBuilder.Append(" from DbPBLang INNER JOIN ");
sqlBuilder.Append(" DbPB on DbPBLang.PBID = DbPB.PBID ");
sqlBuilder.Append(" WHERE DbPB.PBID = :PBID and ");
sqlBuilder.Append(" DbPBLang.LanguageID = :LanguageID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("PBID", typeof(long), pbID);
queryParameterBuilder.AddParameterData("LanguageID", typeof(int), languageID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("PBCode", NHibernateUtil.String);
query.AddScalar("Description", NHibernateUtil.String);
query.AddScalar("CompanyID", NHibernateUtil.Int64);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB)));
return(query.UniqueResult <VOPB>());
}
private void Btn_native_Click(object sender, EventArgs e)
{
try
{
ISession s = DataLayer.GetSession();
ISQLQuery q = s.CreateSQLQuery("SELECT O.* FROM GRAD O");
q.AddEntity(typeof(global::Cirkus.Entiteti.Grad));
IList <Entiteti.Grad> gradovi = q.List <global::Cirkus.Entiteti.Grad>();
foreach (global::Cirkus.Entiteti.Grad g in gradovi)
{
MessageBox.Show(g.naziv);
}
s.Close();
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
}
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>());
}
private void button47_Click(object sender, EventArgs e)
{
try
{
ISession s = DataLayer.GetSession();
ISQLQuery q = s.CreateSQLQuery("SELECT O.* FROM CIN O");
q.AddEntity(typeof(Cin));
IList <Cin> cinovi = q.List <Cin>();
foreach (Cin o in cinovi)
{
MessageBox.Show(o.Naziv.ToString());
}
s.Close();
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
}
