static SchedulerDataHelper()
{
var db = new BehoerdenloesungEntities();
using (db)
{
_SitzungCodeId = (int)
(from x in db.TBGMXCODEs
where x.CODEART == "KAL_TerminArt" && x.CODEKEY == "1"
select x.TBGMXCODE_ID).FirstOrDefault(); // Sitzungen
_SchulferienCodeId = (int)
(from x in db.TBGMXCODEs
where x.CODEART == "KAL_TerminArt" && x.CODEKEY == "2"
select x.TBGMXCODE_ID).FirstOrDefault(); // Schulferien
_FeiertageCodeId = (int)
(from x in db.TBGMXCODEs
where x.CODEART == "KAL_TerminArt" && x.CODEKEY == "3"
select x.TBGMXCODE_ID).FirstOrDefault(); // Feiertage
_TreffenCodeId = (int)
(from x in db.TBGMXCODEs
where x.CODEART == "KAL_TerminArt" && x.CODEKEY == "4"
select x.TBGMXCODE_ID).FirstOrDefault(); // Treffen
}
_Criticalsection = new object();
}
//private string _VD_;
protected override void Initialize(RequestContext requestContext)
{
base.Initialize(requestContext);
_SessionContext = (HomeViewModel)Session["SessionContext"];
_Entities = new BehoerdenloesungEntities();
//_VD_ = ConfigurationManager.AppSettings["vd"];
}
protected override void Initialize(RequestContext requestContext)
{
base.Initialize(requestContext);
_SessionContext = (HomeViewModel)Session["SessionContext"];
_Entities = new BehoerdenloesungEntities();
if (Session["IdOffset"] == null)
{
Session["MaxMand"] = (int)_Entities.TbBHDMandants.Max(m => m.TbBHDMandant_id);
Session["MaxInst"] = (int)_Entities.TbBHDInstitutions.Max(m => m.TbBHDInstitution_id);
Session["IdOffset"] = (int)Session["MaxInst"] + (int)Session["MaxMand"] + 1;
}
ViewBag.IdOffset = (int)Session["IdOffset"];
_MaxInst = (int)Session["MaxInst"];
_MaxMand = (int)Session["MaxMand"];
}
public static string GetValue(string user, string modul, string section, string userKey)
{
var entities = new BehoerdenloesungEntities();
using (entities)
{
var value = string.Empty;
var q = from x in entities.TbGmxUserInis
where x.Visum == user && x.Modul == modul && x.Section == section && x.UserKey == userKey
select x.Value;
if (q.Any())
{
value = q.First();
}
return(value);
}
}
public static string GetValue(string codeKey)
{
var entities = new BehoerdenloesungEntities();
using (entities)
{
var value = string.Empty;
var q = from x in entities.TbGMXGlobals
where x.CODEKEY == codeKey
select x.INHALT;
if (q.Any())
{
value = q.First();
}
return(value);
}
}
protected void CreateDbContext()
{
DbContext = new BehoerdenloesungEntities();
// Do NOT enable proxied entities, else serialization fails
DbContext.Configuration.ProxyCreationEnabled = false;
// Load navigation properties explicitly (avoid serialization trouble)
DbContext.Configuration.LazyLoadingEnabled = false;
// Because Web API will perform validation, we don't need/want EF to do so
DbContext.Configuration.ValidateOnSaveEnabled = false;
//DbContext.Configuration.AutoDetectChangesEnabled = false;
// We won't use this performance tweak because we don't need
// the extra performance and, when autodetect is false,
// we'd have to be careful. We're not being that careful.
}
public static List <Appointment> GetAppointments()
{
var list = new List <Appointment>();
var db = new BehoerdenloesungEntities();
using (db)
{
var fromDate = DateTime.Today.AddYears(-1);
var toDate = DateTime.Today.AddYears(1);
var q = from x in db.TbGMXTermins
where x.AnfangsZeit.Value > fromDate && x.EndZeit.Value < toDate
select x;
foreach (var t in q)
{
var apt = new Appointment()
{
UniqueID = (int)t.TbGMXTermin_ID, // t.TbGESSitzung_ID.HasValue ? (int)t.TbGESSitzung_ID.Value : (int)t.TbGMXTermin_ID,
StartDate = t.AnfangsZeit,
EndDate = t.EndZeit,
Subject = t.Titel,
Description = t.Beschreibung,
Location = t.TerminOrt,
AllDay = (t.Ganztaegig == "1"),
Type = 0, // 0:Normal 1:Pattern 2:Occurrence 3:ChangedOccurrence 4:DeletedOccurrence
RecurrenceInfo = "xxx",
ReminderInfo = "yyy",
Label = (t.TbGmxCodeTerminArt_ID == _SitzungCodeId ? 4 : (t.TbGmxCodeTerminArt_ID == _FeiertageCodeId ? 1 : (t.TbGmxCodeTerminArt_ID == _TreffenCodeId ? 2 : 3))), // apt.TbGmxCodeTerminArt_ID,
Status = 2, // 0:Free 1:Tentative 2:Busy 3:Out If Office 4:Working Elsewhere
ResourceID = (int)t.TbGMXKalender_ID,
ResourceIDs = null,
SitzungID = t.TbGESSitzung_ID.HasValue ? (int)t.TbGESSitzung_ID.Value : 0,
CanOpen = false
//CustomField1 = t.TbGESSitzung_ID.HasValue ? t.TbGESSitzung_ID.Value.ToString() : ""
};
list.Add(apt);
}
}
return(list);
}
// GET: Test
public ActionResult DownloadFile(int id)
{
var entities = new BehoerdenloesungEntities();
var q = from x in entities.TbGMXDateis
where x.TbGMXDatei_id == id
select new
{
Bytes = x.Datei,
Name = x.DateiName,
Typ = x.DateiTyp,
Size = x.DateiGroesse,
IsIndexiert = x.IsIndexiert
};
if (q.Any())
{
return(File(q.First().Bytes, "application/vnd.ms-outlook", q.First().Name));
}
else
{
return(Content(""));
}
}
public static List <Resource> GetResources()
{
var list = new List <Resource>();
var db = new BehoerdenloesungEntities();
using (db)
{
foreach (var t in db.TbGMXKalenders)
{
var res = new Resource()
{
Color = 1,
CustomField1 = null,
Image = null,
ResourceID = (int)t.TbGMXKalender_ID,
ResourceName = t.Bezeichnung,
UniqueID = (int)t.TbGMXKalender_ID
};
list.Add(res);
}
}
return(list);
}
public static int?GetPersonIdFromSysUsrId(int sysUsrId)
{
int?personId = null;
if (_Cache == null)
{
_Cache = new Dictionary <int, int>();
}
else
{
if (_Cache.ContainsKey(sysUsrId))
{
personId = _Cache[sysUsrId];
}
else
{
var entities = new BehoerdenloesungEntities();
var q = from x in entities.TbBHDMitglieds
where x.TbSYSUsr_ID == sysUsrId
select x.Person_id;
if (!q.Any())
{
return(null);
}
else
{
if (q.First().HasValue)
{
personId = (int)(q.First().Value);
_Cache[sysUsrId] = personId.Value;
}
};
}
}
return(personId);
}
public static void SetCanOpenFlags(List <Appointment> appointments)
{
var db = new BehoerdenloesungEntities();
using (db)
{
foreach (var apt in appointments)
{
apt.CanOpen = false;
if (apt.SitzungID > 0)
{
//if (apt.SitzungID == 50223)
//{
// int n = 0;
//}
var qs = db.TbGESSitzungs.Where(x => x.TbGESSitzung_id == apt.SitzungID);
if (qs.Any())
{
var sitzung = qs.First();
if (sitzung.Gremium_id.HasValue && sitzung.WebFreigabe == "1")
{
var gremiumId = (int)sitzung.Gremium_id.Value;
// Ist Sitzung freigegeben und und hat der Benutzer Berechtigung
var homeModel = (HomeViewModel)HttpContext.Current.Session["SessionContext"];
if (homeModel.GremiumListe.Where(g => g.TbBHDGremium_id == gremiumId).Any())
{
// Er bekommet sein Id wieder nur wenn er Rechte hat und es WebFrei ist
apt.CanOpen = true;
apt.Label = 0; // meineSitzung
}
}
}
}
}
}
}
protected override void Initialize(RequestContext requestContext)
{
base.Initialize(requestContext);
_SessionContext = (HomeViewModel)Session["SessionContext"];
_Entities = new BehoerdenloesungEntities();
}
protected override void Initialize(RequestContext requestContext)
{
base.Initialize(requestContext);
_SessionContext = (HomeViewModel)Session["SessionContext"];
var value = UserIniHelper.GetValue(_SessionContext.Shortname, "AFG", "Tasks", "CanSeeAllTasks");
_CanSeeAllTasks = !string.IsNullOrEmpty(value) && value == "1";
_Entities = new BehoerdenloesungEntities();
if (Session["statusList"] == null)
{
var q1 = from x in CodeArten.GESAufgabeStatCodes
select new ComboBoxItem <int>()
{
Value = (int)x.ID,
Text = x.BEZ
};
Session["statusList"] = q1.ToList();
}
if (Session["prioritaetList"] == null)
{
var q2 = from x in CodeArten.GESAufgabePrioCodes
select new ComboBoxItem <int>()
{
Value = (int)x.ID,
Text = x.BEZ
};
Session["prioritaetList"] = q2.ToList();
}
if (Session["filterList"] == null)
{
var liste = new List <ComboBoxItem <int> >();
liste.Add(new ComboBoxItem <int>()
{
Value = -1, Text = "Meine"
});
liste.Add(new ComboBoxItem <int>()
{
Value = 0, Text = "Alle"
});
foreach (var g in _SessionContext.GremiumListe)
{
liste.Add(new ComboBoxItem <int>()
{
Value = (int)g.TbBHDGremium_id, Text = g.Bezeichnung
});
}
;
Session["filterList"] = liste;
}
if (Session["artListAfg"] == null)
{
var q3 = from x in CodeArten.AFGInternArt
select new ComboBoxItem <int>()
{
Value = (int)x.ID,
Text = x.BEZ
};
Session["artListAfg"] = q3.ToList();
}
if (Session["sachbearbeiterList"] == null)
{
var q4 = from x in _Entities.TbSysUSRs
orderby x.Name
select new ComboBoxItem <int>()
{
Value = (int)x.ID,
Text = x.Name
};
Session["sachbearbeiterList"] = q4.ToList();
}
ViewData["statusList"] = Session["statusList"];
ViewData["prioritaetList"] = Session["prioritaetList"];
ViewData["filterList"] = Session["filterList"];
if (Session["filter"] == null)
{
Session["filter"] = -1; // -1: nur meine, 0: alle, > 0: gremium_id
}
ViewData["artListAfg"] = Session["artListAfg"];
ViewData["sachbearbeiterList"] = Session["sachbearbeiterList"];
}
private EwkViewModel GetEwkModel()
{
var model = new EwkViewModel();
if (Session["EwkViewModel"] == null)
{
model.Geburte = new List <EwkGeburt>();
model.Todesfaelle = new List <EwkTodesfall>();
model.Zuzuege = new List <EwkZuzug>();
model.Wegzuege = new List <EwkWegzug>();
model.Totale = new List <EwkTotal>();
model.Jubilare1 = new List <EwkJubilar>();
model.Jubilare2 = new List <EwkJubilar>();
var N = 100;
var value = ConfigurationManager.AppSettings["EwkTopN"];
if (!string.IsNullOrEmpty(value))
{
int.TryParse(ConfigurationManager.AppSettings["EwkTopN"], out N);
}
using (var context = new BehoerdenloesungEntities())
{
//--Geburten
model.Geburte = context.Database.SqlQuery <EwkGeburt>(
"select top " + N + " NAME,VORNAME,STRASSE,HAUSNR,HAUSNRZUSATZ,PLZ,ORT,GEBDAT " +
"from VwEWKUndADRPersonTodayStrVerz_IF " +
"where ADRESSART = 'MAIN' " +
"and EINWOHNER_CD = 'E' " +
"and WEGZUG = 0 " +
"and tod = 0 " +
"order by GEBDAT desc").ToList();
//--Todesfälle
model.Todesfaelle = context.Database.SqlQuery <EwkTodesfall>(
"select top " + N + " NAME,VORNAME,STRASSE,HAUSNR,HAUSNRZUSATZ,PLZ,ORT,TODGUELTIGAB " +
"from VwEWKUndADRPersonTodayStrVerz_IF " +
"where ADRESSART = 'MAIN' " +
"and EINWOHNER_CD = 'G' " +
"and WEGZUG = 0 " +
"and tod = 1 " +
"order by TODGUELTIGAB desc").ToList();
//--Zuzüge
model.Zuzuege = context.Database.SqlQuery <EwkZuzug>(
"select top " + N + " NAME,VORNAME,STRASSE,HAUSNR,HAUSNRZUSATZ,PLZ,ORT,ZUZDAT " +
"from VwEWKUndADRPersonTodayStrVerz_IF " +
"where ADRESSART = 'MAIN' " +
"and EINWOHNER_CD = 'E' " +
"and WEGZUG = 0 " +
"and tod = 0 " +
"order by ZUZDAT desc").ToList();
//--Wegzüge
model.Wegzuege = context.Database.SqlQuery <EwkWegzug>(
"select top " + N + " NAME,VORNAME,STRASSE,HAUSNR,HAUSNRZUSATZ,PLZ,ORT,WEGZUGDAT " +
"from VwEWKUndADRPersonTodayStrVerz_IF " +
"where ADRESSART = 'MAIN' " +
"and EINWOHNER_CD = 'A' " +
"and WEGZUG = 1 " +
"and tod = 0 " +
"order by WEGZUGDAT desc").ToList();
//--CH und Ausländer
var q1 = context.Database.SqlQuery <int>(
"select count(*) as Anzahl_Einwohner " +
"from tbewkpers " +
"where EINWOHNER_CD = 'E'").ToList();
if (q1.Any())
{
model.Totale.Add(new EwkTotal()
{
Text = "CH und Ausländer", Total = q1.First()
});
}
else
{
model.Totale.Add(new EwkTotal()
{
Text = "CH und Ausländer", Total = 0
});
}
//--CH
var q2 = context.Database.SqlQuery <int>(
"select count(*) as Anzahl_Einwohner " +
"from tbewkpers e " +
"inner join TBADRPERSON p " +
"on e.TBADRPERSON_ID = p.TBADRPERSON_ID " +
"where e.EINWOHNER_CD = 'E' " +
"and p.AUSLAENDER = 0").ToList();
if (q2.Any())
{
model.Totale.Add(new EwkTotal()
{
Text = "CH", Total = q2.First()
});
}
else
{
model.Totale.Add(new EwkTotal()
{
Text = "CH", Total = 0
});
}
//--Ausländer
var q3 = context.Database.SqlQuery <int>(
"select count(*) as Anzahl_Einwohner " +
"from tbewkpers e " +
"inner join TBADRPERSON p " +
"on e.TBADRPERSON_ID = p.TBADRPERSON_ID " +
"where e.EINWOHNER_CD = 'E' " +
"and p.AUSLAENDER <> 0").ToList();
if (q3.Any())
{
model.Totale.Add(new EwkTotal()
{
Text = "Ausländer", Total = q3.First()
});
}
else
{
model.Totale.Add(new EwkTotal()
{
Text = "Ausländer", Total = 0
});
}
}
Session["EwkViewModel"] = model;
}
model = Session["EwkViewModel"] as EwkViewModel;
var jubilare = GlobalHelper.GetValue("GES_Jubilare");
//var jubilare = UserIniHelper.GetValue(_SessionContext.Shortname, "EWK", "Jubilare", "Jubilare");
if (string.IsNullOrEmpty(jubilare))
{
jubilare = "=80,=90,>100";
}
var arr = jubilare.Split(',');
using (var context = new BehoerdenloesungEntities())
{
var mask = JubilareSqlBase(arr);
model.Jubilare1 = context.Database.SqlQuery <EwkJubilar>(string.Format(mask, DateTime.Today.ToString("yyyy-MM-dd"))).ToList();
model.Jubilare2 = context.Database.SqlQuery <EwkJubilar>(string.Format(mask, (new DateTime(DateTime.Today.Year + 1, 1, 1)).ToString("yyyy-MM-dd"))).ToList();
}
return(model);
}
protected override void Initialize(RequestContext requestContext)
{
base.Initialize(requestContext);
_SessionContext = GetSessionContext();
_Entities = new BehoerdenloesungEntities();
}