private IQuery getZabGisQuery(ErfassungsPeriod closedPeriod)
{
string zabGisQueryString = String.Format("insert into {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}) " +
"Select guid(),zsg.{2}, zsg.{3}, zsg.{4}, zsg.{5}, zsg.{6}, zsg.{7}, zsg.{8}, zsg.{9}, zsg.{10}, zsg.{11}, zsg.{12}, " +
"zsg.{13}, zsg.{14}, zsg.{15}, zsg.{16}, zsg.{17}, zsg.{18}, zsg.{19}, zsg.{20}, zsg.{21}, zsg.{22}, stg, rfg, zsg " +
"From {0} zsg, {26} stg, {27} rfg " +
"Where zsg.{23} = stg.{28} and zsg.{24} = rfg.{29} and zsg.{23}.{30}=:oldErfPeriod",
new string[]
{
zabGisTypeName, zabGisIdPropName, zabGisIndexPropName, zabGisBezVonPropName,
zabGisBezBisPropName, zabGisErfssungsMPropName, zabGisLaengePropName, zabGisAufnahmeDatPropName,
zabGisAufnahmeTeamPropName, zabGisWetterPropName, zabGisBemerkungPropName, zabGisZstIndTrLPropName,
zabGisZstIndTrRPropName, zabGisFBKostenPropName, zabGisFBDringlichPropName, zabGisTrRKostenPropName,
zabGisTrRDringlichPropName, zabGisTrLKostenPropName, zabGisTrLDringlichPropName, zabGisShapePropName,
zabGisMvkFbPropName, zabGisMvkTrRPropName, zabGisMvkTrLPropName, zabGisStrabPropName,
zabGisRefGruppePropName, zabGisCopyFromPropName, straGisTypeName, refGruppeTypeName,
straGisCopyFromPropName, refGruppeCopyFromPropName, straGisErfPeriodPropName
});
var zabGisQuery = CurrentSession.CreateQuery(zabGisQueryString);
zabGisQuery.SetParameter("oldErfPeriod", closedPeriod);
return(zabGisQuery);
}
public static bool ExistsInDb(Type type, Guid entityID)
{
string queryString = string.Format("select count(*) from {0} as e where e.Id=:entityId", type.FullName);
IQuery query = CurrentSession.CreateQuery(queryString).SetGuid("entityId", entityID);
return(Count(query) == 1);
}
public IList <AnnouncementDTO> LoadAnnouncementsByType(String type)
{
return(CurrentSession
.CreateQuery("from AnnouncementDTO announcement where announcement.Type=?")
.SetParameter(0, type)
.List <AnnouncementDTO>());
}
protected override IQuery getKoorMassGisQuery()
{
//Dummy update for the integration tests: SQLCE does not support subquery in Update statements
string massTeilGisQueryString = String.Format("Update {0} set Id = guid() Where Id = guid()", typeof(KoordinierteMassnahmeGIS).Name);
var massTeilGisQuery = CurrentSession.CreateQuery(massTeilGisQueryString);
return(massTeilGisQuery);
}
public void UpdateBalancesFromDate(DateTime fromDate, int productId, double addedQuantity)
{
var update = CurrentSession.CreateQuery("update Balance set Quantity = Quantity + :addedQuantity where Date >= :fromDate and Product.Id = :productId")
.SetDouble("addedQuantity", addedQuantity)
.SetDateTime("fromDate", fromDate)
.SetInt32("productId", productId);
update.ExecuteUpdate();
}
public IList <Emp> GetEmpsByDeptNo(int deptNo)
{
StringBuilder builder = new StringBuilder();
builder.Append("select a from Emp a ");
builder.Append("where a.Dept.DeptNo=:deptNo ");
builder.Append("order by a.EName asc ");
IQuery query = CurrentSession.CreateQuery(builder.ToString());
query.SetParameter("deptNo", deptNo);
return(query.List <Emp>());
}
protected override void OnEntityUpdating(GlobalMassnahmenvorschlagKatalog entity)
{
base.OnEntityUpdating(entity);
var query = CurrentSession.CreateQuery(
@"update MassnahmenvorschlagKatalog mv
set mv.DefaultKosten = :kosten
where mv.Id IN (select mk.Id from MassnahmenvorschlagKatalog mk where mk.IsCustomized = false and mk.ErfassungsPeriod.IsClosed = false and mk.Belastungskategorie = :bel and mk.Parent = :parent)").
SetParameter("kosten", entity.DefaultKosten)
.SetParameter("parent", entity.Parent)
.SetParameter("bel", entity.Belastungskategorie);
query.ExecuteUpdate();
}
private IQuery getRefGruppeQuerystg(ErfassungsPeriod closedPeriod)
{
string refGruppeQueryStringStg = String.Format("insert into {0} ({1},{2}) " +
"SELECT guid(),rfg " +
"FROM {0} rfg , {3} stg Where rfg.{1} = stg.{4}.{1} and stg.{5} = :oldErfPeriod ",
new string[] {
refGruppeTypeName, refGruppeIdPropName, refGruppeCopyFromPropName, straGisTypeName,
straGisRefGruppePropName, straGisErfPeriodPropName
});
var refGruppeQuerystg = CurrentSession.CreateQuery(refGruppeQueryStringStg);
refGruppeQuerystg.SetParameter("oldErfPeriod", closedPeriod);
return(refGruppeQuerystg);
}
private IQuery getRefGruppeQueryKmg()
{
string refGruppeQueryStringKmg = String.Format("insert into {0} ({1},{2}) " +
"SELECT guid(),rfg " +
"FROM {0} rfg , {3} kmg " +
"Where rfg.{1} = kmg.{4}.{1} and kmg.{5} = :Mandant ",
new string[] {
refGruppeTypeName, refGruppeIdPropName, refGruppeCopyFromPropName, koorMassGisTypeName,
koormassgisRefGruppePropName, koormassgisMandantPropName
});
var refGruppeQueryKmg = CurrentSession.CreateQuery(refGruppeQueryStringKmg);
refGruppeQueryKmg.SetParameter("Mandant", CurrentMandant);
return(refGruppeQueryKmg);
}
protected virtual IQuery getKoorMassGisQuery()
{
string koorMassGisQueryString = String.Format("Update {0} kmg " +
"set kmg.{1} = (Select rfg from {2} rfg where rfg.{3} = kmg.{1}) " +
"Where kmg.{4}=:Mandant",
new string[]
{
koorMassGisTypeName, koormassgisRefGruppePropName, refGruppeTypeName, refGruppeCopyFromPropName,
koormassgisMandantPropName
});
var koorMassGisQuery = CurrentSession.CreateQuery(koorMassGisQueryString);
koorMassGisQuery.SetParameter("Mandant", CurrentMandant);
return(koorMassGisQuery);
}
protected virtual IQuery getMassTeilGisQuery()
{
string massTeilGisQueryString = String.Format("Update {0} mtg " +
"set mtg.{3} = (Select rfg From {1} rfg Where rfg.{2} = mtg.{3}) " +
"Where mtg.{4} = :Mandant",
new string[]
{
MassTeilGisTypeName, refGruppeTypeName, refGruppeCopyFromPropName, massTeilGisRefGruppePropName,
massTeilGisMandantPropName
});
var massTeilGisQuery = CurrentSession.CreateQuery(massTeilGisQueryString);
massTeilGisQuery.SetParameter("Mandant", CurrentMandant);
return(massTeilGisQuery);
}
private IQuery getAchsRefQuery(ErfassungsPeriod closedPeriod)
{
string achsRefQueryString = String.Format("insert into {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}) " +
"SELECT guid(), acr.{2}, acr.{3}, acr.{4}, acr.{5}, acr.{6}, acs, rfg, acr " +
"FROM {0} acr join acr.{7} acs2,{10} acs,{11} rfg " +
"WHERE acs.{12} = acr.{7} and rfg.{13} = acr.{8} and acs2.{14} = :oldErfPeriod",
new string[] {
achsrefTypeName, achsRefIdPropName, achsRefStrassenNamePropName, achsRefVersionPropName,
achsRefVonRbbsPropName, achsRefNachRbbsPropName, achsRefShapePropName, achsRefAchsSegPropName,
achsRefRefGruppePropName, achsRefCopiedFromPropName, achssegTypeName, refGruppeTypeName,
achssegCopdiedFromPropName, refGruppeCopyFromPropName, achseErfassungsPeriodPropName
});
var achsRefQuery = CurrentSession.CreateQuery(achsRefQueryString);
achsRefQuery.SetParameter("oldErfPeriod", closedPeriod);
return(achsRefQuery);
}
private IQuery getAchsQuery(ErfassungsPeriod currentErfassungsperiod, ErfassungsPeriod closedPeriod)
{
string achsQuerystring = String.Format(
"insert into {0}({1},{2},{3},{4},{5},{6},{7},{8},{9}) " +
"Select guid(),a.{2},a.{3},a.{4},a.{5},a.{6},a.{7},:newErfPeriod,a " +
"FROM {0} a " +
"where a.{8} =:oldErfPeriod"
, new string[] {
achseTypeName, achseIdPropName, achseBsidPropName, achseValidFromPropName,
achseNamePropName, achseOperationPropName, achseImpnrPropName, achseMandantPropName,
achseErfassungsPeriodPropName, achseCopdiedFromPropName
});
var achsQuery = CurrentSession.CreateQuery(achsQuerystring);
achsQuery.SetParameter("newErfPeriod", currentErfassungsperiod);
achsQuery.SetParameter("oldErfPeriod", closedPeriod);
return(achsQuery);
}
public bool InUse(string orgId)
{
if (string.IsNullOrEmpty(orgId))
{
throw new ArgumentNullException("orgId");
}
Org org = Get(orgId);
string maxOrderId;
string minOrderId;
Org.CreateGetChildCondition(org, out maxOrderId, out minOrderId);
const string hql =
"select count(*) from User user where user.Org.OrderId>='{0}' and user.Org.OrderId<='{1}'";
var oo = (Int64)CurrentSession.CreateQuery(String.Format(hql, minOrderId, maxOrderId)).UniqueResult();
return(oo > 0);
}
public ReadOnlyCollection <Role> GetRolesByName(string loginId)
{
if (loginId == null)
{
throw new ArgumentNullException("loginId");
}
const string foundOrgId = "Select u.Org.Id From User u where u.LoginId=?";
IList guid = CurrentSession.CreateQuery(foundOrgId).SetString(0, loginId).List();
string orgId = null;
if (guid.Count != 0)
{
orgId = guid[0] + "%";
}
string hql =
@"From Role r where r.Name in(select elements(u.Roles) From User u where u.LoginId=:LoginId)
or r.Name in
(
Select elements(g.Roles) From UserGroup g where g.Id in
(select elements(u1.UserGroups) from User u1 where u1.LoginId=:LoginId)
)";
if (orgId != null)
{
hql +=
@" or r.Name in(
select elements(o.Roles) from Org o where
o.OrderId like :orgId
)";
}
IQuery query = CurrentSession.CreateQuery(hql);
query.SetString("LoginId", loginId);
if (orgId != null)
{
query.SetString("orgId", orgId);
}
return(new ReadOnlyCollection <Role>(query.List <Role>()));
}
private IQuery getSektorQuery(ErfassungsPeriod closedPeriod)
{
string sektorQueryString = String.Format(
"insert into {0} ({1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}) " +
"SELECT guid(),sek.{2},sek.{3},sek.{4},sek.{5},sek.{6},sek.{7},sek.{8},sek.{9}, acs2, sek " +
"FROM {0} sek, {12} acs1, {12} acs2 " +
"where sek.{10}.{13} =:oldErfPeriod and acs1.{15} = sek.{10}.{15} and acs1.{15} = acs2.{14}.{15}",
new string[]
{
sektorTypeName, sektIdPropName, sektBsIdPropName, sektKmPropName,
sektLengthPropName, sektNamePropName, sektSequencePropName, sektMarkerPropName,
sektOperationPropName, sektImpNrPropName, sektAchsSegPropName, sektCopyFromPropName,
achssegTypeName, achssegErfPeriodPropName, achssegCopdiedFromPropName, achssegIdPropName
});
var sektorQuery = CurrentSession.CreateQuery(sektorQueryString);
sektorQuery.SetParameter("oldErfPeriod", closedPeriod);
return(sektorQuery);
}
public IEnumerable <Balance> GetBalancesOnDate(DateTime onDate, int[] productIds)
{
var multiQuery = CurrentSession.CreateMultiQuery();
foreach (int id in productIds)
{
multiQuery.Add <Balance>(
CurrentSession.CreateQuery(@"
from Balance
where Date = (select max(b.Date) from Balance as b where b.Date <= :onDate and b.Product.Id = :productId)
and Product.Id = :productId
")
.SetDateTime("onDate", onDate)
.SetInt32("productId", id)
);
}
var balancesOnDate = multiQuery.List().Cast <List <Balance> >();
return((from balance in balancesOnDate select balance.Count == 0 ? null : balance[0]).Where(b => b != null).ToList());
}
private IQuery getAchsSegQuery(ErfassungsPeriod currentErfassungsperiod, ErfassungsPeriod closedPeriod)
{
string achssegQueryString = String.Format(
"insert into {0} ({1},{2},{3},{4},{5},{6},{7},{9},{10},{11},{12},{13},{14}, {18}) " +
"SELECT guid(),acs.{2},acs.{3},acs.{4},acs.{5},acs.{6},acs.{7},acs.{9},acs.{10},ach,:newErfPeriod,acs.{13},acs, acs.{18} " +
"FROM {0} acs, {15} ach " +
"where acs.{12} =:oldErfPeriod and ach.{17} = acs.{11}",
new string[] {
achssegTypeName, achssegIdPropName, achssegBsIdPropName, achssegOperationPropName, //0-3
achssegNamePropName, achssegSequencePropName, achssegImpNrPropName, achssegShapePropName, achssegShape4dPropName, //4-8
achssegVersionPropName, achssegAchsenIdPropName, achssegAchsPropName, achssegErfPeriodPropName, //9-12
achssegMandantPropName, achssegCopdiedFromPropName, achseTypeName, achseIdPropName, //13-16
achseCopdiedFromPropName, achssegIsInvertedPropName
});
var achsSegQuery = CurrentSession.CreateQuery(achssegQueryString);
achsSegQuery.SetParameter("newErfPeriod", currentErfassungsperiod);
achsSegQuery.SetParameter("oldErfPeriod", closedPeriod);
return(achsSegQuery);
}
private IQuery getStraGisQuery(ErfassungsPeriod closedPeriod, ErfassungsPeriod currentErfassungsperiod)
{
string straGisQueryString = String.Format("insert into {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, " +
"{11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}) " +
"Select guid(), stg.{2}, stg.{3}, stg.{4}, stg.{5}, stg.{6}, stg.{7}, stg.{8}, stg.{9}, stg.{10}, " +
"stg.{11}, stg.{12}, stg.{13}, stg.{14}, stg.{15}, stg.{16},:newErfPeriod, rfg, stg " +
"From {0} stg, {20} rfg " +
"Where stg.{17} = :oldErfPeriod and stg.{18} = rfg.{21}",
new string[]
{
straGisTypeName, straGisIdPropName, straGisNamePropName, straGisBezVonPropName,
straGisBezBisPropName, straGisBelagPropName, straGisLaengePropName, straGisBreiteFBPropName,
straGisTrottPropName, straGisBreiteTrLPropName, straGisBreiteTrRPropName, straGisEigenTuemerPropName,
straGisOrtsBezPropName, straGisShapePropName, straGisIsLockedPropName, straGisBLKPropName,
straGisMandantPropName, straGisErfPeriodPropName, straGisRefGruppePropName, straGisCopyFromPropName,
refGruppeTypeName, refGruppeCopyFromPropName
});
var straGisQuery = CurrentSession.CreateQuery(straGisQueryString);
straGisQuery.SetParameter("newErfPeriod", currentErfassungsperiod);
straGisQuery.SetParameter("oldErfPeriod", closedPeriod);
return(straGisQuery);
}
public void Delete(Guid accountingPriceListId)
{
CurrentSession.CreateQuery("DELETE FROM ArticleAccountingPriceIndicator WHERE AccountingPriceListId = :accountingPriceListId")
.SetParameter("accountingPriceListId", accountingPriceListId)
.ExecuteUpdate();
}