public IList <LoadBillInCome> GetByLoadBillNums(IEnumerable <string> loadBillNums)
{
var query = NHibernateSession.CreateQuery("from LoadBillInCome where LoadBillNum in (:loadBillNums)");
query.SetParameterList("loadBillNums", loadBillNums);
return(query.List <LoadBillInCome>());
}
public LoadBillInCome GetByLoadBillNum(string loadBillNum)
{
var query = NHibernateSession.CreateQuery("from LoadBillInCome where LoadBillNum=:loadBillNum");
query.SetParameter("loadBillNum", loadBillNum);
return(query.List <LoadBillInCome>().FirstOrDefault());
}
//public AbstractVueDao(string sessionFactoryConfigPath)
//{
// SessionFactoryConfigPath = sessionFactoryConfigPath;
//}
public virtual List <ObjetListeType> GetVue(Dictionary <string, string> filters)
{
////////ISession isession = NHibernateSession;
////////var query0 = from m in isession.Query<Livre>()
//////// select m;
////////IEnumerable<Livre> xy = query0.ToList();
//Contract.Requires(filters != null);
//TestCodeContract(null);
//GetTuple();
//GetSortedSet();
IQuery query = NHibernateSession.CreateQuery(QueryBuilder(filters));
if (filters != null)
{
foreach (KeyValuePair <string, string> filter in filters)
{
query.SetParameter(filter.Key, filter.Value);
}
}
return((List <ObjetListeType>)query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean <ObjetListeType>()).List <ObjetListeType>());
}
public MonthPayOffDetail GetByLoadBillID(int loadBillID)
{
var query = NHibernateSession.CreateQuery("FROM MonthPayOffDetail where LoadBillBy.ID=:loadBillID");
query.SetParameter("loadBillID", loadBillID);
return(query.List <MonthPayOffDetail>().FirstOrDefault());
}
public IQuery GetInfo(int poid, DateTime date, int provider, int status, int page, int pagesize, string querystring, int POtype)
{
string query = "select " + querystring + " from PurchaseOrder PO";
query += " join PO.Provider PV";
query += " where PO.PurchaseOrderStatus = :Status";
if (poid > 0)
{
query += " AND PO.Id = :Id";
}
if (provider > 0)
{
query += " AND PV.Id = :Provider";
}
if (date > Convert.ToDateTime("1/1/1900"))
{
query += " AND PO.Date = :Date";
}
if (POtype > 0)
{
query += " AND PO.PurchaseOrderType = :POtype";
}
query += " order by PO.Id";
IQuery q = NHibernateSession.CreateQuery(query);
q.SetString("Status", status.ToString());
if (poid > 0)
{
q.SetInt32("Id", poid);
}
if (provider > 0)
{
q.SetInt32("Provider", provider);
}
if (date > Convert.ToDateTime("1/1/1900"))
{
q.SetDateTime("Date", date);
}
if (POtype > 0)
{
q.SetEnum("POtype", (PurchaseOrderType)POtype);
}
if (pagesize != 0)
{
q.SetMaxResults(pagesize);
if (page == 1)
{
q.SetFirstResult(0);
}
else
{
q.SetFirstResult((page - 1) * pagesize);
}
}
return(q);
}
public IList ListIdentifiersFromFunction(Type classType, Object userId, PermissionAction action)
{
string hql = "SELECT DISTINCT EP.Identifier FROM EntityPermission EP JOIN EP.Functions F JOIN F.Roles R JOIN R.Users U WHERE ClassName = :ClassName AND U.ID = :UserID AND EP.PermissionAction = :Action";
IQuery q = NHibernateSession.CreateQuery(hql).SetString("ClassName", classType.Name).SetGuid("UserID", (Guid)userId).SetEnum("Action", action);
return(q.List());
}
public Altura GetDireccionMasCercana(double lat, double lon)
{
var str = string.Format("order by ((a.LatitudFin + a.LatitudInicio - 2 * {0}) * (a.LatitudFin + a.LatitudInicio - 2 * {0})) \r\n + ((a.LongitudFin + a.LongitudInicio - 2 * {1}) * (a.LongitudFin + a.LongitudInicio - 2 * {1}))", lat.ToString(CultureInfo.InvariantCulture), lon.ToString(CultureInfo.InvariantCulture));
var list = NHibernateSession.CreateQuery("from Altura a where abs(a.LatitudInicio - :lat) < 0.01 and abs(a.LongitudInicio - :lon) < 0.01 " + str).SetParameter("lat", lat).SetParameter("lon", lon).SetMaxResults(1).List <Altura>();
return((list.Count > 0) ? list[0] : null);
}
public UserAccount GetByAccount(string account)
{
var query = NHibernateSession.CreateQuery("from Core.UserModule.UserAccount where Account=:account");
query.SetParameter("account", account);
return(query.UniqueResult <UserAccount>());
}
public IList <WayBillException> GetByExpressNo(string expressNo)
{
var query = NHibernateSession.CreateQuery("FROM WayBillException WHERE ExpressNo =:ExpressNo");
query.SetParameter("ExpressNo", expressNo);
return(query.List <WayBillException>());
}
/// <summary>
/// Get all items for the current type
/// </summary>
/// <returns>A list of Lookup objects</returns>
public IList <Lookup> List(LookupType type)
{
//Cache Section
string cacheKey = null;
MembershipHelperUser mhu = MembershipHelper.GetUser();
if (mhu != null)
{
cacheKey = string.Format("LOOKUPS_{0}_{1}_{2}", mhu.UserId, typeof(Lookup).ToString(), type.ToString());
}
object result = CacheManager.GetCached(typeof(Lookup), cacheKey);
ICriteria crit = GetCriteria();
if (result == null)
{
string query = "SELECT L FROM Lookup L WHERE";
query += " LookupType = :Type";
query += " ORDER BY UPPER(Description)";
IQuery q = NHibernateSession.CreateQuery(query);
q.SetEnum("Type", type);
result = q.List <Lookup>();
//crit.Add(Expression.Eq("LookupType", type));
//crit.AddOrder(new Order("Description", true));
//result = crit.List<Lookup>();
CacheManager.AddItem(typeof(Lookup), cacheKey, result);
}
return(result as IList <Lookup>);
}
public VnsHtSoCtMax SetSoChungTuMaxByThangNamEtc(Guid LoaichungtuId, int SoChungTuMax, int Thang, int Nam)
{
String sql = "Select a from VnsHtSoCtMax a " +
" where a.LoaichungtuId = :LoaichungtuId " +
" and Thang = :Thang " +
" and Nam = :Nam ";
IQuery q = NHibernateSession.CreateQuery(sql);
q.SetParameter("LoaichungtuId", LoaichungtuId);
q.SetParameter("Thang", Thang);
q.SetParameter("Nam", Nam);
IList <VnsHtSoCtMax> ls = q.List <VnsHtSoCtMax>();
VnsHtSoCtMax maxct = new VnsHtSoCtMax();
if (ls == null || ls.Count == 0)
{
maxct.LoaichungtuId = LoaichungtuId;
maxct.Thang = Thang;
maxct.Nam = Nam;
maxct.SoChungtuMax = SoChungTuMax;
Save(maxct);
}
else
{
maxct = ls[0];
maxct.SoChungtuMax = SoChungTuMax;
Update(maxct);
}
return(maxct);
}
public ProductView GetProductView(int id, int toCurrency)
{
string hql = "SELECT WLI.ID, P.Description, P.Code, PI.PriceSell, PI.Price, PI.CTM, PI.CTR, PI.Index, PI.LastPrice, PI.PCR, WLI.WorkListItemStatus, PI.PLCurrencyId, PI.PricePurchase";
hql += " FROM ProductInfoByPriceList PI";
hql += " JOIN PI.WorkListItem WLI";
hql += " JOIN WLI.PriceAttribute PA";
hql += " JOIN PA.PriceGroup PG";
hql += " JOIN PA.PriceBase PB";
hql += " JOIN PB.Provider PV";
hql += " JOIN PB.Product P";
hql += " WHERE PB.Status = :Status";
hql += " AND P.Status = :ProductStatus";
hql += " AND WLI.ID = :Id";
IQuery q = NHibernateSession.CreateQuery(hql);
q.SetEnum("Status", PriceBaseStatus.Verified);
q.SetEnum("ProductStatus", ProductStatus.Active);
q.SetInt32("Id", id);
q.SetMaxResults(1);
q.SetResultTransformer(new NHibernate.Transform.AliasToBeanConstructorResultTransformer(typeof(ProductView).GetConstructors()[3]));
return(q.UniqueResult <ProductView>());
}
public IList <Permission> ListForCurrentUserAndSite(object userId, Enum action, string siteCode)
{
Site s = ControllerManager.Site.Get(siteCode);
if (s == null)
{
throw new NullReferenceException(string.Format("There is no site with the code '{0}' defined.", siteCode));
}
if (userId == null)
{
throw new NullReferenceException("There is no Membership User logged.");
}
string hql = "select distinct P from HierarchyFunctionsView HFV join HFV.ParentFunction PF join HFV.ChildFunction F join F.Permissions P join PF.Roles R join R.Users U where U.ID = :UserID and PF.Site = :Site";
if (action != null)
{
hql += " and P.PermissionAction = :Action";
}
IQuery q = NHibernateSession.CreateQuery(hql).SetEntity("Site", s).SetGuid("UserID", (Guid)userId);
if (action != null)
{
q.SetEnum("PermissionAction", action);
}
return(q.List <Permission>());
}
// http://stackoverflow.com/questions/3390561/nhibernate-update-single-field-without-loading-entity
public void UpdateGooglePlusId(Guid id, string googlePlusId)
{
NHibernateSession.CreateQuery("update User set GooglePlusId = :googlePlusId where id = :id")
.SetParameter("googlePlusId", googlePlusId)
.SetParameter("id", id)
.ExecuteUpdate();
}
public IList <PriceImportLogView> GetList(Guid id, PriceImportLogStatus piLogStatus, int pageSize, int pageNumber, out int totalcount)
{
string hql = "SELECT COUNT(PI.Description) FROM PriceImport PI JOIN PI.LogResults PIL WHERE PI.ID = :Id AND PIL.Status = :Status";
IQuery qcount = NHibernateSession.CreateQuery(hql).SetGuid("Id", id).SetEnum("Status", piLogStatus);
totalcount = Convert.ToInt32(qcount.UniqueResult());
hql = "SELECT PIL FROM PriceImport PI JOIN PI.LogResults PIL WHERE PI.ID = :Id AND PIL.Status = :Status";
IQuery q = NHibernateSession.CreateQuery(hql);
q.SetGuid("Id", id);
q.SetEnum("Status", piLogStatus);
if (pageNumber != 0 && pageSize != 0)
{
q.SetFirstResult((pageNumber * pageSize) - pageSize);
q.SetMaxResults(pageSize);
}
q.SetResultTransformer(new NHibernate.Transform.AliasToBeanConstructorResultTransformer(typeof(PriceImportLogView).GetConstructors()[1]));
IList <PriceImportLogView> pil = q.List <PriceImportLogView>();
if (pil.Count > 0)
{
return(pil);
}
return(new List <PriceImportLogView>());
}
public IList <MonthPayOff> GetPayOffMonth(IList <int> loadBillIDs)
{
var query = NHibernateSession.CreateQuery("SELECT a.MonthPayOffBy FROM MonthPayOffDetail a where a.LoadBillBy.ID IN (:loadBillIDs);");
query.SetParameterList("loadBillIDs", loadBillIDs);
return(query.List <MonthPayOff>());
}
public IList <RpNganSachTongHop> GetKeHoachTrongNam(String MaCt, DateTime TuNgay, DateTime DenNgay)
{
try
{
String HQuery = "select sum(d.SoTien) as SoTien, h.MaCt as MaCt, d.LoaiKhoanId as LoaiKhoanId, d.KhoanChiId as KhoanChiId, h.DonViId as DonViId " +
"from VnsKhNganSach h inner join h.LstNganSach d " +
"where h.NgayKeHoach >= :TuNgay And h.NgayKeHoach <= :DenNgay " +
(string.IsNullOrEmpty(MaCt) ? "" : " and h.MaCt = :MaCt ") +
"Group by h.MaCt, d.LoaiKhoanId, d.KhoanChiId, h.DonViId";
IQuery query = NHibernateSession.CreateQuery(HQuery);
if (!String.IsNullOrEmpty(MaCt))
{
query.SetParameter("MaCt", MaCt);
}
query.SetParameter("TuNgay", TuNgay);
query.SetParameter("DenNgay", DenNgay);
query.SetResultTransformer(Transformers.AliasToBean <RpNganSachTongHop>());
IList <RpNganSachTongHop> lst = query.List <RpNganSachTongHop>();
return(lst);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 根据运单查询运单成本详细
/// </summary>
/// <param name="ExpressNo"></param>
/// <returns></returns>
public IList <WayBillCost> GetCostByExpressNo(string expressNo)
{
var query = NHibernateSession.CreateQuery("from WayBillCost where ExpressNo=:expressNo");
query.SetParameter("expressNo", expressNo);
return(query.List <WayBillCost>());
}
public IList <Site> CanManage(object userId)
{
string hql =
"SELECT DISTINCT R.Site FROM Rol R JOIN R.Users U WHERE R.IsAdministrator = :IsAdministrator AND U.ID = :UserID";
return(NHibernateSession.CreateQuery(hql).SetBoolean("IsAdministrator", true).SetGuid("UserID", (Guid)userId).List <Site>());
}
private object GetAggregatedValue(string fnName, FilterExpression filter)
{
// adding filter
List <object> parameterValues = new List <object>();
string filterString = filter.GetFilterString(parameterValues);
if (string.IsNullOrEmpty(filterString) == false)
{
filterString = " Where (" + filterString + ")";
}
string hqlQuery = "Select " + fnName + " from " + this.GetTableName(GetSourceTypeEnum.View) + filterString;
hqlQuery = hqlQuery.Replace("[", "").Replace("]", "");
var query = NHibernateSession.CreateQuery(hqlQuery);
// adding parameters
for (int i = 0; i < parameterValues.Count; i++)
{
query.SetParameter(Filter.ParameterPrefix.Substring(1) + i, parameterValues[i]);
}
var currentSeq = query.List();
if (currentSeq == null)
{
return(null);
}
else
{
return(currentSeq[0]);
}
}
public IList <MonthPayOff> GetByPayOffMonth(DateTime dt)
{
var query = NHibernateSession.CreateQuery("from MonthPayOff where PayOffMonth=:dt");
query.SetParameter("dt", dt);
return(query.List <MonthPayOff>());
}
public List <CodeProvView> GetCodProvViewList()
{
string hql = "select P.ID, P.Code, PV.ID, PB.ProviderCode from Product P JOIN P.Providers PV JOIN P.PriceBases PB";
IQuery q = NHibernateSession.CreateQuery(hql);
q.SetResultTransformer(new NHibernate.Transform.AliasToBeanConstructorResultTransformer(typeof(CodeProvView).GetConstructors()[1]));
return(q.List <CodeProvView>() as List <CodeProvView>);
}
public IList ListUsersForEntity(Object o, string identifier, PermissionAction action)
{
string hql = "SELECT DISTINCT U.Email FROM EntityPermission EP JOIN EP.Users U WHERE EP.ClassName = :ClassName AND (EP.Identifier = :Identifier OR EP.Identifier is NULL)AND EP.PermissionAction = :Action";
IQuery q = NHibernateSession.CreateQuery(hql).SetString("ClassName", o.GetType().Name).SetString("Identifier", identifier).SetEnum("Action", action);
return(q.List());
}
public List <IDIDView> GetProviderIds()
{
string hql = "select P.ID, PV.ID from Provider PV JOIN PV.Products P";
IQuery q = NHibernateSession.CreateQuery(hql);
q.SetResultTransformer(new NHibernate.Transform.AliasToBeanConstructorResultTransformer(typeof(IDIDView).GetConstructors()[1]));
return(q.List <IDIDView>() as List <IDIDView>);
}
public IList <Country> GetAll(string orderField)
{
string query = "SELECT C FROM Country C";
query += " ORDER BY UPPER(" + orderField + ")";
IQuery q = NHibernateSession.CreateQuery(query);
return(q.List <Country>());
}
private string GetNewCote(string cleCote)
{
string _query = "select max(Cote) as result from Exemplaire where Cote like :ParamCote";
IQuery query = NHibernateSession.CreateQuery(_query).SetString("ParamCote", cleCote + "%").SetMaxResults(1);
IList <string> _lastCote = query.List <string>();
int _LastCote = _lastCote[0] == null ? 0 : int.Parse(_lastCote[0].Substring(_lastCote[0].LastIndexOf("-") + 1));
return(cleCote + "-" + string.Format("{0:00000}", _LastCote + 1));
}
public IList <Direccion> GetByAlturaSinProvincia(string[] tokens, int altura)
{
var list = NHibernateSession.CreateQuery(QueryBuilder.CreateAlturaQueryBuilder(altura).AddCalle(tokens, 3).GetQuery()).SetCacheable(true).List <Altura>();
var list2 = new List <Direccion>(list.Count);
foreach (var current in list)
{
list2.Add(new Direccion(current, altura));
}
return(list2);
}
public IList <Direccion> GetByEsquinaEnProvincia(string[] tokens, string[] tokensEsquina, int provincia)
{
var list = NHibernateSession.CreateQuery(QueryBuilder.CreateCruceQueryBuilder().AddCalle(tokens, 3).AddEsquina(tokensEsquina, 3).AddProvincia(provincia).GetQuery()).SetCacheable(true).List <Cruce>();
var list2 = new List <Direccion>(list.Count);
foreach (var current in list)
{
list2.Add(new Direccion(current));
}
return(list2);
}
public IList <Info> GetLikeMa(String Ma)
{
String HQuery = "from Info i where i.Ma like :Ma";
IQuery query = NHibernateSession.CreateQuery(HQuery);
query.SetParameter("Ma", Ma + "%");
IList <Info> lst = query.List <Info>();
return(lst);
}
private Int64 GetCountByProduct(int productId)
{
string hql = "Select Count (THW.Id) FROM TransactionHistoryWeekly THW ";
hql += "JOIN THW.ProductID P Where P.Id = :productId";
IQuery q = NHibernateSession.CreateQuery(hql);
q.SetInt32("productId", productId);
return(q.UniqueResult <Int64>());
}
