public IList <TaiKhoanNganHang> getHanMucTrongNgayTaiKhoanNganHang()
{
try
{
string query = @"
select ID,TenChuThe,SoTaiKhoan,
(case
when TongGiaoDich IS NULL then HanMucTrongNgay
else (HanMucTrongNgay-TongGiaoDich)
end
) HanMucTrongNgay,TrangThai
from TaiKhoanNganHang tknh
left join
(
select IDTaiKhoanNganHang,SUM(GiaTri) TongGiaoDich from DonHang
where NgayThanhToan >= CAST(CURRENT_TIMESTAMP AS DATE) and NgayThanhToan < DATEADD(DD, 1, CAST(CURRENT_TIMESTAMP AS DATE))
group by IDTaiKhoanNganHang
) temp
on tknh.ID=temp.IDTaiKhoanNganHang
";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(TaiKhoanNganHang));
IList <TaiKhoanNganHang> listTaiKhoanNganHang = iQuery.List <TaiKhoanNganHang>();
return(listTaiKhoanNganHang);
}
catch (Exception ex)
{
return(null);
}
}
public static IList <TDI_Estado> ObtieneEstadoPorCP(int cdPostal)
{
#region Query Armado
List <TDI_Estado> lstEstado = new List <TDI_Estado>();
string strSQL = string.Empty;
Azteca.Utility.Security.Rijndael _ChyperRijndael = new Azteca.Utility.Security.Rijndael();
ISession session = NHibernateHelperORACLE.GetSession();
strSQL += " SELECT est.ESTADO_NOMBRE Nombre, est.ID_ESTADO iD ";
strSQL += " FROM seml_tdi_colonias col, ";
strSQL += " seml_tdi_cpcol cpcol, ";
strSQL += " seml_the_tipoasenta asen, ";
strSQL += " seml_the_tipozona zon, ";
strSQL += " seml_tdi_municipios mun, ";
strSQL += " seml_tdi_estado est ";
strSQL += " WHERE cpcol.id_codigopostal = " + cdPostal;
strSQL += " AND cpcol.id_colonia = col.id_colonia ";
strSQL += " AND col.id_asentamiento = asen.id_asentamiento ";
strSQL += " AND zon.id_zona = col.id_zona ";
strSQL += " AND mun.ID_MUNICIPIO = col.ID_MUNICIPIO ";
strSQL += " and mun.ID_ESTADO = est.ID_ESTADO ";
strSQL += " and mun.MUNICIPIO_ESTATUS = 'A' ";
strSQL += " and est.ESTADO_ESTATUS = 'A' ";
strSQL += " ORDER BY ESTADO_NOMBRE ASC ";
try
{
ISQLQuery consultaIQRY = session.CreateSQLQuery(strSQL);
consultaIQRY.AddScalar("Nombre", NHibernateUtil.String); //0
consultaIQRY.AddScalar("iD", NHibernateUtil.Int32); //1
IList lista = consultaIQRY.List();
foreach (Object[] obj in lista)
{
TDI_Estado oEstado = new TDI_Estado();
oEstado.EstadoNombre = System.Convert.ToString(obj[0]);
oEstado.IdEstado = System.Convert.ToInt32(obj[1]);
lstEstado.Add(oEstado);
}
}
catch (Exception ex)
{
MngDatosLogErrores.GuardaError(ex, "MngDatosEstado");
lstEstado = null;
return(lstEstado);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
return(lstEstado);
#endregion
}
public IEnumerable <Person> Read()
{
CreateConnectionAndQuery("SELECT Id, FirstName, LastName, Age FROM person");
IList list = query.List();
personList = new List <Person>();
//while (dataReader.Read())
//{
// personList.Add(
// new Person(
// (uint)dataReader["Id"],
// dataReader["FirstName"].ToString(),
// dataReader["LastName"].ToString(),
// (uint)dataReader["Age"]
// )
// );
//}
//dataReader.Close();
//dataReader.Dispose();
DisposeConnection();
return(personList);
}
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 static IList <T> FindPagingByCriteria <T>(object finder, string methodName, object[] parameters, int firstResult, int maxResults, string sortExpression)
{
MethodInfo methodInfo = finder.GetType().GetMethod(methodName);
object invokeResult = methodInfo.Invoke(finder, parameters);
IList <T> result = new List <T>();
if (invokeResult is ISQLQuery)
{
ISQLQuery query = (ISQLQuery)invokeResult;
if (firstResult != -1 && maxResults != -1)
{
query.SetFirstResult(firstResult);
query.SetMaxResults(maxResults);
}
result = query.List <T>();
}
else if (invokeResult is IQuery)
{
IQuery query = (IQuery)invokeResult;
if (firstResult != -1 && maxResults != -1)
{
query.SetFirstResult(firstResult);
query.SetMaxResults(maxResults);
}
result = query.List <T>();
}
else if (invokeResult is ICriteria)
{
ICriteria criteria = (ICriteria)invokeResult;
if (!string.IsNullOrEmpty(sortExpression))
{
sortExpression = sortExpression.Trim();
if (sortExpression.ToLower().EndsWith(" asc"))
{
sortExpression = sortExpression.Substring(0, sortExpression.Length - 4);
criteria.AddOrder(Order.Asc(sortExpression));
}
else if (sortExpression.ToLower().EndsWith(" desc"))
{
sortExpression = sortExpression.Substring(0, sortExpression.Length - 5);
criteria.AddOrder(Order.Desc(sortExpression));
}
}
if (firstResult != -1 && maxResults != -1)
{
criteria.SetFirstResult(firstResult);
criteria.SetMaxResults(maxResults);
}
result = criteria.List <T>();
}
return(result);
}
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 static List <IntentosUserXIP> ConsultaUltimoAccesos(string IP)
{
string strSql = string.Empty;
List <IntentosUserXIP> Accesos = new List <IntentosUserXIP>();
ISession session = NHibernateHelperORACLE.GetSession();
strSql += " select INTENTOS, ACCESO, IP from ( " +
" SELECT loac.loac_llav_pr as INTENTOS, tiac.tiac_llav_pr AS acceso, loac_usip AS ip " +
" FROM secn_the_loac loac, secn_tdi_tiac tiac " +
" where " +
" loac_usip = '" + IP + "' " +
" order by loac_llav_pr desc) " +
" where rownum <= 10 ";
try
{
ISQLQuery consultaImagenesOT = session.CreateSQLQuery(strSql);
consultaImagenesOT.AddScalar("INTENTOS", NHibernateUtil.Int32);
consultaImagenesOT.AddScalar("ACCESO", NHibernateUtil.Int32);
consultaImagenesOT.AddScalar("IP", NHibernateUtil.String);
IList listaImagenesOT = consultaImagenesOT.List();
if (listaImagenesOT != null)
{
if (listaImagenesOT.Count > 1)
{
foreach (Object[] Tmp in listaImagenesOT)
{
IntentosUserXIP IUAdd = new IntentosUserXIP();
IUAdd.TipoIntento = (Int32)Tmp[0];
IUAdd.NoIP = Tmp[1].ToString();
IUAdd.NumIntento = (Int32)Tmp[2];
Accesos.Add(IUAdd);
}
}
else
{
IntentosUserXIP IUAdd = new IntentosUserXIP();
IUAdd.TipoIntento = (Int32)((object[])((listaImagenesOT[0])))[0];
IUAdd.NoIP = ((object[])((listaImagenesOT[0])))[1].ToString();
IUAdd.NumIntento = (Int32)((object[])((listaImagenesOT[0])))[2];
Accesos.Add(IUAdd);
}
}
return(Accesos);
}
catch (Exception ex)
{
return(Accesos);
}
finally
{
session.Close();
session.Dispose();
session = null;
}
}
/// <summary>
/// returns a list of byte[] containing all
/// pfds for the given customer id
/// </summary>
/// <param name="customerID">the id of the customer</param>
public static List <byte[]> GetAllRechnungenForKunde(int customerID)
{
try
{
List <byte[]> ret = new List <byte[]>();
using (IRepository repository = RepositoryFactory.Instance.CreateRepository <Repository>())
{
ISQLQuery query = repository.GetQuery("select text from " + TABLERECHNUNGDOCS + " r where r.title like ?");
query.SetString(0, "%" + customerID + "%");
query.AddScalar("text", NHibernateUtil.BinaryBlob);
var all = query.List();
foreach (var s in all)
{
ret.Add(s as byte[]);
}
}
return(ret);
}
catch (DatabaseException)
{
throw;
}
catch (Exception ex)
{
throw (new DatabaseException(ex, ""));
}
}
//2009/07/30 jackter end
/// <summary>
/// 不分頁查詢(本地sql)
/// </summary>
/// <param name="sql">sql</param>
/// <returns>結果集</returns>
public IList <T> SelectNativeSQL <T>(string sql)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
//操作訊息
ISQLQuery sqlQuery = session.CreateSQLQuery(sql);
//已定義的實體類
if (typeof(T).FullName.StartsWith("Com.Everunion"))
{
return(sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(T)))
.List <T>());
}
//其它,如(T=object[])
else
{
return(sqlQuery.List <T>());
}
}
//拋出例外
catch (Exception e)
{
log.Error("SelectNativeSQL.sql:" + sql);
//輸出錯誤
log.Error("SelectNativeSQL.error:" + e.ToString());
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
public virtual List <ReportGrupa> getDnevniPrihodiClanoviReportGrupeDanGrupa(DateTime from, DateTime to,
List <Grupa> grupe)
{
// NOTE: U select delu je dodat dan, mesec i godina da bi mogao da ih koristim u order by delu.
from = from.Date;
to = to.Date.AddDays(1);
try
{
string query = @"
SELECT
datepart(year,u.datum_vreme_uplate) god,
datepart(month,u.datum_vreme_uplate) mes,
datepart(day,u.datum_vreme_uplate) dan,
g.broj_grupe, g.podgrupa, g.naziv, Sum(u.iznos), Count(*)
FROM grupe g INNER JOIN uplate u
ON g.grupa_id = u.grupa_id
WHERE (u.datum_vreme_uplate BETWEEN '{0}' AND '{1}')
{2}
GROUP BY
datepart(year,u.datum_vreme_uplate),
datepart(month,u.datum_vreme_uplate),
datepart(day,u.datum_vreme_uplate),
g.broj_grupe, g.podgrupa, g.naziv
ORDER BY god DESC, mes DESC, dan DESC, g.broj_grupe, g.podgrupa
";
bool filterGrupe = grupe != null && grupe.Count > 0;
string filter = String.Empty;
if (filterGrupe)
{
filter = " AND " + Util.getGrupeFilter(grupe, "g", "grupa_id");
}
query = String.Format(query, from.ToString("yyyy-MM-dd"), to.ToString("yyyy-MM-dd"), filter);
ISQLQuery q = Session.CreateSQLQuery(query);
IList <object[]> result = q.List <object[]>();
List <ReportGrupa> result2 = new List <ReportGrupa>();
foreach (object[] row in result)
{
int god = (int)row[0];
int mes = (int)row[1];
int dan = (int)row[2];
int brojGrupe = (int)row[3];
string podgrupa = (string)row[4];
string nazivGrupe = (string)row[5];
decimal ukupanIznos = (decimal)row[6];
int brojClanova = (int)row[7];
string sifra = brojGrupe + podgrupa;
object[] data = new object[] { new SifraGrupe(sifra), nazivGrupe, ukupanIznos };
result2.Add(new ReportGrupa(data, brojClanova));
}
return(result2);
}
catch (HibernateException ex)
{
string message = String.Format(
"{0} \n\n{1}", Strings.DatabaseAccessExceptionMessage, ex.Message);
throw new InfrastructureException(message, ex);
}
}
public List <string> GetTableNames(string projectName, string applicationName, string dbProvider,
string dbServer, string portNumber, string dbInstance, string dbName, string dbSchema, string dbUserName, string dbPassword, string serName)
{
List <string> tableNames = new List <string>();
try
{
InitializeScope(projectName, applicationName);
List <DataObject> dataObjects = new List <DataObject>();
ISession session = GetNHSession(dbProvider, dbServer, dbInstance, dbName, dbSchema, dbUserName, dbPassword, portNumber, serName);
string sql = GetDatabaseMetaquery(dbProvider, dbName, dbSchema);
ISQLQuery query = session.CreateSQLQuery(sql);
foreach (string tableName in query.List <string>())
{
tableNames.Add(tableName);
}
session.Close();
}
catch (Exception ex)
{
_logger.Error(string.Format("Error in GetTableNames: {0}", ex));
throw ex;
}
return(tableNames);
}
public WorkFlowStateEvent GetSendDraftStateEvent(int workFlowTypeID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" select workflowstateevent.* ");
strQuery.AppendLine(" from workflowstateevent ");
strQuery.AppendLine(" inner join workflowstate ");
strQuery.AppendLine(" on workflowstate.workflowstateid = workflowstateevent.workflowstateid ");
strQuery.AppendLine(" where workflowstate.workflowtypeid = :workFlowTypeID ");
strQuery.AppendLine(" and workflowstate.name = 'Draft' ");
strQuery.AppendLine(" and workflowstateevent.name = 'Send' ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt32("workFlowTypeID", workFlowTypeID);
query.AddEntity(typeof(WorkFlowStateEvent));
IList <WorkFlowStateEvent> list = query.List <WorkFlowStateEvent>();
if (list != null)
{
return(list[0]);
}
else
{
return(null);
}
}
public IList <WorkFlowStateEventWithLang> FindByWorkFlowStateEventByStateID(int workFlowStateID, short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT wse.WorkFlowStateEventID as EventID , wse.Name , wsel.DisplayName , wse.UserControlPath ");
strQuery.AppendLine(" FROM WorkFlowState ws ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEvent wse ");
strQuery.AppendLine(" ON ws.WorkFlowStateID = wse.WorkFlowStateID ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEventLang wsel ");
strQuery.AppendLine(" ON wse.WorkFlowStateEventID = wsel.WorkFlowStateEventID AND wsel.LanguageID = :LanguageID ");
strQuery.AppendLine(" WHERE ws.WorkFlowStateID = :WorkFlowStateID");
strQuery.AppendLine(" AND ws.Active = '1'");
strQuery.AppendLine(" AND wse.Active = '1'");
strQuery.AppendLine(" ORDER BY ws.Name ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID);
query.SetInt32("WorkFlowStateID", workFlowStateID);
query.AddScalar("EventID", NHibernateUtil.Int64);
query.AddScalar("Name", NHibernateUtil.String);
query.AddScalar("DisplayName", NHibernateUtil.String);
query.AddScalar("UserControlPath", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(WorkFlowStateEventWithLang)));
return(query.List <WorkFlowStateEventWithLang>());
}
public IList QueryBySQL(string sql, Action <ISQLQuery> action)
{
ISQLQuery query = session.CreateSQLQuery(sql);
action(query);
return(query.List());
}
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 ICollection <T> FindAll(string sqlQuery, NHibernate.Transform.IResultTransformer resultTransformer, params Parameter[] parameters)
{
ISQLQuery query = CreateSQLQuery(sqlQuery, parameters);
query.SetResultTransformer(resultTransformer);
return(query.List <T>());
}
public IList <T> ExecuteSqlQuery(String sql)
{
List <T> retVal = new List <T>();
using (ISession session = HibernateHelper.OpenSession())
{
try
{
ISQLQuery sqlQuery = session.CreateSQLQuery(sql);
IList rResult = sqlQuery.List();
foreach (Object data in rResult)
{
retVal.Add((T)data);
}
}
catch (Exception)
{
}
finally
{
session.Disconnect();
session.Close();
}
}
return(retVal);
}
public IList <SuProgramRole> FindProgramPermission(ArrayList arrayRoleID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from SuProgramRole where roleID ");
sqlBuilder.Append(" in (");
string strValue = "";
for (int i = 0; i < arrayRoleID.Count; i++)
{
strValue += "," + arrayRoleID[i].ToString();
}
sqlBuilder.AppendFormat("{0}", strValue.TrimStart(','));
sqlBuilder.Append(")");
ISQLQuery queryTransactionPermission = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryTransactionPermission.AddEntity(typeof(SuProgramRole));
IList <SuProgramRole> UserTransactionPermissionList = queryTransactionPermission.List <SuProgramRole>();
foreach (SuProgramRole programRole in UserTransactionPermissionList)
{
Console.Write(programRole.Program);
}
return(UserTransactionPermissionList);
}
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 short FindProgramIDByProgramCode(string programCode)
{
StringBuilder sql = new StringBuilder();
QueryParameterBuilder parameter = new QueryParameterBuilder();
sql.Append("SELECT Programid ");
sql.Append("FROM SuProgram ");
sql.Append("WHERE ProgramCode = :programCode ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
parameter.AddParameterData("programCode", typeof(string), programCode);
parameter.FillParameters(query);
query.AddScalar("Programid", NHibernateUtil.Int16);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuProgram)));
IList <SuProgram> list = query.List <SuProgram>();
if (list.Count > 0)
{
return(list[0].Programid);
}
else
{
return(0);
}
}
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 <SuProgramSearchResult> FindSuProgramByLanguageId(short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT p.ProgramID as ProgramID, p.ProgramCode as ProgramCode, p.ProgramPath as ProgramPath ");
strQuery.AppendLine(" , p.Comment as Comment, p.Active as Active ");
strQuery.AppendLine(" , pl.ProgramsName as ProgramName, l.LanguageID as LanguageId ");
strQuery.AppendLine(" FROM SuProgram p ");
strQuery.AppendLine(" INNER JOIN SuProgramLang pl ");
strQuery.AppendLine(" ON pl.ProgramID = p.ProgramID");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON l.LanguageID = pl.languageID and l.LanguageID = :LanguageID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID);
query.AddScalar("ProgramID", NHibernateUtil.Int16);
query.AddScalar("ProgramCode", NHibernateUtil.String);
query.AddScalar("ProgramPath", NHibernateUtil.String);
query.AddScalar("ProgramName", NHibernateUtil.String);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuProgramSearchResult)));
return(query.List <SuProgramSearchResult>());
}
public short FindMenuMainIDByProgramID(short programID)
{
StringBuilder sql = new StringBuilder();
QueryParameterBuilder parameter = new QueryParameterBuilder();
sql.Append("SELECT MenuMainID ");
sql.Append("FROM SuMenu ");
sql.Append("WHERE ProgramID = :programID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
parameter.AddParameterData("programID", typeof(short), programID);
parameter.FillParameters(query);
query.AddScalar("MenuMainID", NHibernateUtil.Int16);
query.SetResultTransformer(Transformers.AliasToBean(typeof(MenuPath)));
IList <MenuPath> list = query.List <MenuPath>();
if (list.Count > 0)
{
return(list[0].MenuMainID.Value);
}
else
{
return(0);
}
}
public IList <CompanyTaxRate> GetCompanyTaxList(DbCompanyTax taxID, int firstResult, int maxResult, string sortExpression)
{
ISQLQuery queryy = FindByCompanyTaxCriteria(taxID, false, sortExpression);
IList <CompanyTaxRate> CompanyTaxList = queryy.List <CompanyTaxRate>();
return(CompanyTaxList);
}
public virtual string GetListBySQL(string sql)
{
using (ISession sess = HbmSessionFactory.OpenSession())
{
try
{
ISQLQuery q = sess.CreateSQLQuery(sql);
IList <object[]> ls = q.List <object[]>();
//object o = ls[0][3];
//Type t = o.GetType();
//double dd = (double)o;
if (ls.Count > 0 && ls[0] == null)
{
ls.RemoveAt(0);
}
string json = JsonSerializerHelper.EntityToJson(ls);
string s = PackJsonListResultForArray("true", json, string.Empty, ls.Count);
return(s);
}
catch (Exception ex)
{
return(PackJsonListResultForArray("false", "[]", ex.Message, 0));
}
}
}
public static IList CreateSQLQuery(string queryString, List <ScalarPair> spList, out string msgException)
{
IList result = null;
lock (lockObj)
{
ISession session = NHibernateHelper.GetCurrentSession();
msgException = string.Empty;
try
{
ISQLQuery q = session.CreateSQLQuery(queryString);
if (spList != null)
{
foreach (ScalarPair sp in spList)
{
q.AddScalar(sp.ScalarName, sp.SType);
}
}
result = q.List();
}
catch (Exception ex)
{
msgException = ex.StackTrace;
LogHelper.Log(ex);
}
finally
{
}
return(result);
}
}
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 <ServiceTeamInformation> GetServiceTeamInformation(short roleID, string sortExpression)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
sqlBuilder.Append("SELECT srs.roleserviceid AS RoleServiceID,");
sqlBuilder.Append("dst.serviceteamcode AS ServiceCode,");
sqlBuilder.Append("dst.description AS ServiceName ");
sqlBuilder.Append("FROM suroleservice srs,dbserviceteam dst ");
sqlBuilder.Append("WHERE srs.active=1 ");
sqlBuilder.Append("AND srs.roleid = :roleID ");
sqlBuilder.Append("AND srs.serviceteamid = dst.serviceteamid ");
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(" ORDER BY RoleServiceID, ServiceCode, ServiceName");
}
else
{
sqlBuilder.Append(" ORDER BY " + sortExpression);
}
parameterBuilder.AddParameterData("roleID", typeof(Int16), roleID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("RoleServiceID", NHibernateUtil.Int64);
query.AddScalar("ServiceCode", NHibernateUtil.String);
query.AddScalar("ServiceName", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ServiceTeamInformation)));
return(query.List <ServiceTeamInformation>());
}
public 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>());
}
/// <summary>
/// 根据用户ID，获取所有报表功能模块权限ID
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public IList GetReportFuncsByUserID(string userID)
{
string strSql = string.Format(@"select distinct FUNC_ID from (select * from Web_Login_Role where LOGIN_ID={0}) LR join Web_Sys_Rolefuncs RF on LR.ROLE_ID=RF.ROLE_ID where FTYPE!=1", userID);
ISQLQuery query = NHelper.GetCurrentSession().CreateSQLQuery(strSql);
return(query.List());
}
