//acount
public static NetCom.ServerResponseGetAcountTable GetAcountTable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestGetAcountsTable _request)
{
NetCom.ServerResponseGetAcountTable toReturn = new NetCom.ServerResponseGetAcountTable()
{
deEntrys = FuncsVSQL.GetListATFromReader($"select * from {DBDingus.AcountsTableNames.AcountsTableName}")
};
return(toReturn);
}
public static NetCom.ServerResponseChangeUserTable ChangeUserTable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestChangeUserTable _request)
{
NetCom.ServerResponseChangeUserTable toReturn = new NetCom.ServerResponseChangeUserTable();
SqlCommand command = new SqlCommand();
if (!_request.IsNewUser || _request.DeleteEntry)
{
command.Parameters.AddWithValue("@ID", _request.deEntry.ID);
}
if (_request.DeleteEntry)
{
command.CommandText = $"delete from {DBDingus.UserTableNames.UserTableName} where {DBDingus.UserTableNames.ID} = @ID";
}
else
{
command.Parameters.AddWithValue("@Voornaam", _request.deEntry.VoorNaam);
command.Parameters.AddWithValue("@Achternaam", _request.deEntry.AchterNaam);
command.Parameters.AddWithValue("@NFCID", _request.deEntry.NFCID);
command.Parameters.AddWithValue("@DateJoined", _request.deEntry.DateJoined);
command.Parameters.AddWithValue("@IsActive", _request.deEntry.IsActiveUser);
if (!_request.IsNewUser)
{
command.Parameters.AddWithValue("@DateLeft", _request.deEntry.DateLeft);
}
if (_request.IsNewUser)
{
if (!_request.IsNewUser)
{
command.CommandText = $"insert into {DBDingus.UserTableNames.UserTableName} ({DBDingus.UserTableNames.VoorNaam}, {DBDingus.UserTableNames.AchterNaam}, {DBDingus.UserTableNames.NFCID}, {DBDingus.UserTableNames.DateJoined}, {DBDingus.UserTableNames.IsActiveUser}, {DBDingus.UserTableNames.DateLeft} ) values (@Voornaam, @Achternaam, @NFCID, cast(@DateJoined as date), cast(@IsActive as bit), cast(@DateLeft as date))";
}
else
{
command.CommandText = $"insert into {DBDingus.UserTableNames.UserTableName} ({DBDingus.UserTableNames.VoorNaam}, {DBDingus.UserTableNames.AchterNaam}, {DBDingus.UserTableNames.NFCID}, {DBDingus.UserTableNames.DateJoined}, {DBDingus.UserTableNames.IsActiveUser}) values (@Voornaam, @Achternaam, @NFCID, cast(@DateJoined as date), cast(@IsActive as bit))";
}
}
else
{
if (!_request.IsNewUser)
{
command.CommandText = $"update {DBDingus.UserTableNames.UserTableName} set {DBDingus.UserTableNames.VoorNaam} = @Voornaam, {DBDingus.UserTableNames.AchterNaam} = @Achternaam, {DBDingus.UserTableNames.NFCID} = @NFCID, {DBDingus.UserTableNames.DateJoined} = cast(@DateJoined as date), {DBDingus.UserTableNames.IsActiveUser} = cast(@IsActive as bit), {DBDingus.UserTableNames.DateLeft} = cast(@DateLeft as date) where {DBDingus.UserTableNames.ID} = @ID";
}
else
{
command.CommandText = $"update {DBDingus.UserTableNames.UserTableName} set {DBDingus.UserTableNames.VoorNaam} = @Voornaam, {DBDingus.UserTableNames.AchterNaam} = @Achternaam, {DBDingus.UserTableNames.NFCID} = @NFCID, {DBDingus.UserTableNames.DateJoined} = cast(@DateJoined as date), {DBDingus.UserTableNames.IsActiveUser} = cast(@IsActive as bit) where {DBDingus.UserTableNames.ID} = @ID";
}
}
}
if (FuncsVSQL.SQLNonQuery(command) != 1)
{
toReturn.OK = false;
}
return(toReturn);
}
public static List <DBDingus.AcountTableEntry> GetListATFromReader(SqlCommand _command)
{
List <DBDingus.AcountTableEntry> toReturn = new List <DBDingus.AcountTableEntry>();
try
{
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
_command.Connection = connection;
connection.Open();
SqlDataReader _reader = _command.ExecuteReader();
List <string> fields = new List <string>();
for (int i = 0; i < _reader.FieldCount; i++)
{
fields.Add(_reader.GetName(i));
}
while (_reader.Read())
{
DBDingus.AcountTableEntry entry = new DBDingus.AcountTableEntry();
if (fields.Contains(DBDingus.AcountsTableNames.ID))
{
entry.ID = ReadFromReader <Int32>((IDataRecord)_reader, DBDingus.AcountsTableNames.ID);
}
if (fields.Contains(DBDingus.AcountsTableNames.Naam))
{
entry.Naam = ReadFromReader <string>((IDataRecord)_reader, DBDingus.AcountsTableNames.Naam);
}
if (fields.Contains(DBDingus.AcountsTableNames.inlogNaam))
{
entry.inlogNaam = ReadFromReader <string>((IDataRecord)_reader, DBDingus.AcountsTableNames.inlogNaam);
}
if (fields.Contains(DBDingus.AcountsTableNames.inlogWachtwoord))
{
entry.inlogWachtwoord = ReadFromReader <string>((IDataRecord)_reader, DBDingus.AcountsTableNames.inlogWachtwoord);
}
if (fields.Contains(DBDingus.AcountsTableNames.aanspreekpuntBevoegthijdLvl))
{
entry.aanspreekpuntBevoegdhijd = ReadFromReader <Int32>((IDataRecord)_reader, DBDingus.AcountsTableNames.aanspreekpuntBevoegthijdLvl);
}
if (fields.Contains(DBDingus.AcountsTableNames.adminBevoegdhijd))
{
entry.adminBevoegdhijd = ReadFromReader <Int32>((IDataRecord)_reader, DBDingus.AcountsTableNames.adminBevoegdhijd);
}
toReturn.Add(entry);
}
}
}
catch (Exception ex)
{
throw new Exception($"(ATFromReader)ERROR @ SQL Command: {_command.CommandText} | Message: {ex.Message}");
}
return(toReturn);
}
//is school dag
public static NetCom.ServerResponseGetIsSchoolDagTable GetIsSchooldagTable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestGetIsSchoolDagTable _request)
{
NetCom.ServerResponseGetIsSchoolDagTable toReturn = new NetCom.ServerResponseGetIsSchoolDagTable();
if (_request.byBetween)
{
toReturn.DagenDateErSchoolIs = FuncsVSQL.GetLisISDFromReader($"select * from {DBDingus.IsSchoolDagTableNames.HetIsEenSchoolDagTableNames} where {DBDingus.IsSchoolDagTableNames.Date} between cast('{_request.dateFromAndWith.Date.ToString("yyyy\\/ MM\\/ dd")}' as date) and cast('{_request.dateTotEnMet.Date.ToString("yyyy\\/ MM\\/ dd")}' as date)");
}
else
{
toReturn.DagenDateErSchoolIs = FuncsVSQL.GetLisISDFromReader($"select * from {DBDingus.IsSchoolDagTableNames.HetIsEenSchoolDagTableNames} where {DBDingus.IsSchoolDagTableNames.Date} between DATEADD(yy, DATEDIFF(yy, 0, cast('{_request.year}/3/19' as date)), 0) and DATEADD(yy, DATEDIFF(yy, 0, cast('{_request.year}/3/19' as date)) + 1, -1) ");
}
return(toReturn);
}
public HttpResponseMessage Index()
{
DBDingus.AcountTableEntry sysUser = new DBDingus.AcountTableEntry();
NetCom.ServerRequestOverzightFromOneDate request = new NetCom.ServerRequestOverzightFromOneDate();
request.useToday = true;
request.alsoReturnExUsers = false;
NetCom.ServerResponseOverzightFromOneDate resp = FuncsVController.overzight(sysUser, request);
DateTime lkNu = FuncsVController.GetDateTimeFromSqlDatabase();
List <string> DaiNiBan = new List <string>();
string DaiIkan = lkNu.ToString() + "\r\n \n";
foreach (var x in resp.EtList)
{
string toAdd = "";
toAdd = x.UsE.VoorNaam + " " + x.UsE.AchterNaam;
if (x.HasTodayRegEntry)
{
if (x.RegE.HeeftIngetekend)
{
toAdd += " In:" + x.RegE.TimeInteken.ToString("hh\\:mm\\:ss");
if (x.RegE.IsAanwezig)
{
toAdd += " Uit:Aanwezig Totaal:" + lkNu.Subtract(x.RegE.TimeInteken).ToString("hh\\:mm\\:ss\\.fff");
}
else
{
toAdd += $" Uit:{x.RegE.TimeUitteken.ToString("hh\\:mm\\:ss")} Totaal:" + x.RegE.TimeUitteken.Subtract(x.RegE.TimeInteken).ToString("hh\\:mm\\:ss\\.fff");
}
}
}
DaiNiBan.Add(toAdd + "\r\n \n");
}
foreach (var x in DaiNiBan.OrderBy(x => x.Length))
{
DaiIkan += x;
}
HttpResponseMessage toReturn = new HttpResponseMessage(HttpStatusCode.OK);
toReturn.Content = new StringContent(DaiIkan, System.Text.Encoding.UTF8, "text/plain");
return(toReturn);
}
//user
public static NetCom.ServerResponseGetUserTable GetUserTable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestGetUserTable _request)
{
NetCom.ServerResponseGetUserTable toReturn = new NetCom.ServerResponseGetUserTable();
SqlCommand command = new SqlCommand();
if (_request.aleenDieNogOpSchoolZitten)
{
command.CommandText = $"select * from {DBDingus.UserTableNames.UserTableName} where ZitNogOpSchool = 1";
}
else
{
command.CommandText = $"select * from {DBDingus.UserTableNames.UserTableName}";
}
toReturn.deEntrys = FuncsVSQL.GetListUTFromReader(command);
return(toReturn);
}
public static NetCom.ServerResponseOverzightFromOneDate overzight(DBDingus.AcountTableEntry _userAcount, NetCom.ServerRequestOverzightFromOneDate _request)
{
NetCom.ServerResponseOverzightFromOneDate toReturn = new NetCom.ServerResponseOverzightFromOneDate();
SqlCommand command = new SqlCommand();
command.CommandText = $"select * from {DBDingus.UserTableNames.UserTableName}";
if (!_request.alsoReturnExUsers)
{
command.CommandText += $" where {DBDingus.UserTableNames.IsActiveUser} = 1";
}
List <DBDingus.UserTableTableEntry> userEntrys = FuncsVSQL.GetListUTFromReader(command); //_DBDingus.GetListUTFromReader(FuncsVSQL.SQLQuery(command));
List <DBDingus.RegistratieTableTableEntry> regEntrys = new List <DBDingus.RegistratieTableTableEntry>();
command = new SqlCommand();
command.CommandText = $"select * from {DBDingus.RegistratieTableNames.RegistratieTableName} where {DBDingus.RegistratieTableNames.Date}";
if (_request.useToday)
{
command.CommandText += " = cast(getdate() as date)";
}
else
{
command.CommandText += $" = cast('{_request.dateToGetOverzightFrom.Date.ToString("yyyy-MM-dd")}' as date)";
}
regEntrys = FuncsVSQL.GetListRTFromReader(command); //_DBDingus.GetListRTFromReader(FuncsVSQL.SQLQuery(command));
foreach (var User in userEntrys)
{
if (_request.alsoReturnExUsers || User.IsActiveUser)
{
DBDingus.CombUserAfwEntry toPutInList = new DBDingus.CombUserAfwEntry();
toPutInList.UsE = User;
foreach (var Entry in regEntrys)
{
if (Entry.IDOfUserRelated == User.ID)
{
toPutInList.HasTodayRegEntry = true;
toPutInList.RegE = Entry;
break;
}
}
toReturn.EtList.Add(toPutInList);
}
}
toReturn.SQlDateTime = GetDateTimeFromSqlDatabase();
return(toReturn);
}
public static NetCom.ServerResponseChangeModTable ChangeModtable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestChangeModTable _request)
{
NetCom.ServerResponseChangeModTable toReturn = new NetCom.ServerResponseChangeModTable();
SqlCommand command = new SqlCommand();
if (!_request.IsNewEntry || _request.DeleteEntry)
{
command.Parameters.AddWithValue("@ID", _request.deEntry.ID);
}
if (_request.DeleteEntry)
{
command.CommandText = $"delete from {DBDingus.ModifierTableNames.ModifierTableName} where {DBDingus.ModifierTableNames.ID} = @ID";
}
else
{
command.Parameters.AddWithValue("@dateVan", _request.deEntry.DateVanafEnMet);
command.Parameters.AddWithValue("@dateTot", _request.deEntry.DateTotEnMet);
command.Parameters.AddWithValue("@daysOfEffect", JsonConvert.SerializeObject(_request.deEntry.DaysOfEffect));
command.Parameters.AddWithValue("@users", JsonConvert.SerializeObject(_request.deEntry.UserIDs));
command.Parameters.AddWithValue("@hoursToAdd", _request.deEntry.HoursToAdd);
command.Parameters.AddWithValue("@omschrij", _request.deEntry.omschrijveing);
command.Parameters.AddWithValue("@isStudiev", _request.deEntry.isStudieVerlof);
command.Parameters.AddWithValue("@isExcur", _request.deEntry.isExurtie);
command.Parameters.AddWithValue("@isFlexy", _request.deEntry.isFlexibelverlofoeorfsjklcghiur);
if (_request.IsNewEntry)
{
command.CommandText = $"insert into {DBDingus.ModifierTableNames.ModifierTableName} ({DBDingus.ModifierTableNames.DateVanafEnMet}, {DBDingus.ModifierTableNames.DateTotEnMet}, {DBDingus.ModifierTableNames.DaysOfEffect}, {DBDingus.ModifierTableNames.UserIDs}, {DBDingus.ModifierTableNames.HoursToAdd}, {DBDingus.ModifierTableNames.Omschrijving}, {DBDingus.ModifierTableNames.isStudiever}, {DBDingus.ModifierTableNames.isExur}, {DBDingus.ModifierTableNames.isflexy}) values (cast(@dateVan as date), cast(@dateTot as date), @daysOfEffect, @users, @hoursToAdd, @omschrij, @isStudiev, @isExcur, @isFlexy)";
}
else
{
command.CommandText = $"update {DBDingus.ModifierTableNames.ModifierTableName} set {DBDingus.ModifierTableNames.DateVanafEnMet} = cast(@dateVan as date), {DBDingus.ModifierTableNames.DateTotEnMet} = @dateTot, {DBDingus.ModifierTableNames.DaysOfEffect} = @daysOfEffect, {DBDingus.ModifierTableNames.UserIDs} = @users, {DBDingus.ModifierTableNames.HoursToAdd} = @hoursToAdd, {DBDingus.ModifierTableNames.Omschrijving} = @omschrij, {DBDingus.ModifierTableNames.isStudiever} = @isStudiev, {DBDingus.ModifierTableNames.isExur} = @isExcur, {DBDingus.ModifierTableNames.isflexy} = @isFlexy where {DBDingus.ModifierTableNames.ID} = @ID";
}
}
if (FuncsVSQL.SQLNonQuery(command) != 1)
{
toReturn.OK = false;
}
return(toReturn);
}
public static NetCom.ServerResponseChangeAcountTable ChangeAcountTable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestChangeAcountTable _request)
{
NetCom.ServerResponseChangeAcountTable toReturn = new NetCom.ServerResponseChangeAcountTable();
SqlCommand command = new SqlCommand();
if (!_request.IsNewEntry || _request.DeleteEntry)
{
command.Parameters.AddWithValue("@ID", _request.deEntry.ID);
}
if (_request.DeleteEntry)
{
command.CommandText = $"delete from {DBDingus.AcountsTableNames.AcountsTableName} where {DBDingus.AcountsTableNames.ID} = @ID";
}
else
{
command.Parameters.AddWithValue("@aansprBevoeg", _request.deEntry.aanspreekpuntBevoegdhijd);
command.Parameters.AddWithValue("@adminBevoeg", _request.deEntry.adminBevoegdhijd);
command.Parameters.AddWithValue("@inlogNaam", _request.deEntry.inlogNaam);
command.Parameters.AddWithValue("@pw", _request.deEntry.inlogWachtwoord);
command.Parameters.AddWithValue("@naam", _request.deEntry.Naam);
if (_request.IsNewEntry)
{
command.CommandText = $"insert into {DBDingus.AcountsTableNames.AcountsTableName} ({DBDingus.AcountsTableNames.Naam}, {DBDingus.AcountsTableNames.inlogNaam}, {DBDingus.AcountsTableNames.inlogWachtwoord}, {DBDingus.AcountsTableNames.aanspreekpuntBevoegthijdLvl}, {DBDingus.AcountsTableNames.adminBevoegdhijd}) values (@naam, @inlogNaam, @pw, @aansprBevoeg, @adminBevoeg)";
}
else
{
command.CommandText = $"update {DBDingus.AcountsTableNames.AcountsTableName} set {DBDingus.AcountsTableNames.Naam}= @naam, {DBDingus.AcountsTableNames.inlogNaam} = @inlogNaam, {DBDingus.AcountsTableNames.inlogWachtwoord} = @pw, {DBDingus.AcountsTableNames.aanspreekpuntBevoegthijdLvl} = @aansprBevoeg, {DBDingus.AcountsTableNames.adminBevoegdhijd} = @adminBevoeg where {DBDingus.AcountsTableNames.ID} = @ID";
}
}
if (FuncsVSQL.SQLNonQuery(command) != 1)
{
toReturn.OK = false;
}
return(toReturn);
}
private void push(bool isNew, bool delete)
{
try
{
NetCom.ServerRequestChangeAcountTable request = new NetCom.ServerRequestChangeAcountTable();
DBDingus.AcountTableEntry deEntry = new DBDingus.AcountTableEntry();
if (delete)
{
deEntry.ID = deEntry.ID = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
request.DeleteEntry = true;
}
else
{
request.IsNewEntry = isNew;
if (isNew)
{
deEntry.Naam = textBoxNewNaam.Text;
deEntry.inlogNaam = textBoxNewInlogNaam.Text;
deEntry.inlogWachtwoord = textBoxNewInlogWachtwoord.Text;
deEntry.aanspreekpuntBevoegdhijd = Convert.ToInt32(textBoxNewaansprelvl.Text);
deEntry.adminBevoegdhijd = Convert.ToInt32(textBoxNewAdminlbvl.Text);
}
else
{
deEntry.ID = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
deEntry.Naam = textBoxUpdateNaam.Text;
deEntry.inlogNaam = textBoxUpdateInlogNaam.Text;
if (textBoxUpdateInlogWachtwoord.Text != "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
{
deEntry.inlogWachtwoord = textBoxUpdateInlogWachtwoord.Text;
}
else
{
deEntry.inlogWachtwoord = alleDeEntrys.First(x => x.ID == deEntry.ID).inlogWachtwoord; // not changed moet een bool zijn en pw moet niet gestuurd worden....
}
deEntry.aanspreekpuntBevoegdhijd = Convert.ToInt32(textBoxUpdateAnsprlvl.Text);
deEntry.adminBevoegdhijd = Convert.ToInt32(textBoxUpdateAdminlvl.Text);
}
}
request.deEntry = deEntry;
NetCom.ServerResponseChangeAcountTable response = webr <NetCom.ServerResponseChangeAcountTable>(request);
if (response.OK == false)
{
if (isNew)
{
throw new Exception("buttonNew~~ response !OK");
}
else
{
throw new Exception("buttonUpdate~~ response !OK");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
refreshList();
}
public HttpResponseMessage tokidokiaru([FromBody] NetCom.ServerRequest _request)
{
NetCom.ServerResponse toReturn = new NetCom.ServerResponse();
toReturn.IsErrorOccurred = false;
try
{
DBDingus.AcountTableEntry usingUser = GetUser(_request.UserName, _request.Password);
string param = Serilalise(_request.Request);
JObject baylife = JObject.Parse(param);
switch ((NetCom.WhatIsThisEnum)Enum.Parse(typeof(NetCom.WhatIsThisEnum), (string)baylife["WatIsDit"]))
{
case NetCom.WhatIsThisEnum.RSqlServerDateTime:
toReturn.Response = FuncsVController.GetDateTimeFromSqlDatabase();
break;
case NetCom.WhatIsThisEnum.RInteken:
toReturn.Response = FuncsVController.inteken(usingUser, Deserialise <NetCom.ServerRequestTekenInOfUit>(param));
break;
case NetCom.WhatIsThisEnum.ROneDateRegiOverzight:
toReturn.Response = FuncsVController.overzight(usingUser, Deserialise <NetCom.ServerRequestOverzightFromOneDate>(param));
break;
case NetCom.WhatIsThisEnum.RChangeRegTable:
toReturn.Response = FuncsVController.ChangeRegistatieTable(usingUser, Deserialise <NetCom.ServerRequestChangeRegistratieTable>(param));
break;
case NetCom.WhatIsThisEnum.RMultiDateRegiOverzight:
toReturn.Response = FuncsVController.alleDagOverzightenVanTussenTweDatums(usingUser, Deserialise <NetCom.ServerRequestOverzightFromMultipleDates>(param));
break;
//newr
case NetCom.WhatIsThisEnum.GetUserTable:
toReturn.Response = FuncsVController.GetUserTable(usingUser, Deserialise <NetCom.ServerRequestGetUserTable>(param));
break;
case NetCom.WhatIsThisEnum.ChangeUserTable:
toReturn.Response = FuncsVController.ChangeUserTable(usingUser, Deserialise <NetCom.ServerRequestChangeUserTable>(param));
break;
case NetCom.WhatIsThisEnum.GetModTable:
toReturn.Response = FuncsVController.GetModtable(usingUser, Deserialise <NetCom.ServerRequestGetModTable>(param));
break;
case NetCom.WhatIsThisEnum.ChangeModTable:
toReturn.Response = FuncsVController.ChangeModtable(usingUser, Deserialise <NetCom.ServerRequestChangeModTable>(param));
break;
case NetCom.WhatIsThisEnum.GetAcountsTable:
toReturn.Response = FuncsVController.GetAcountTable(usingUser, Deserialise <NetCom.ServerRequestGetAcountsTable>(param));
break;
case NetCom.WhatIsThisEnum.ChangeAcountTable:
toReturn.Response = FuncsVController.ChangeAcountTable(usingUser, Deserialise <NetCom.ServerRequestChangeAcountTable>(param));
break;
case NetCom.WhatIsThisEnum.GetIsSchoolDagTable:
toReturn.Response = FuncsVController.GetIsSchooldagTable(usingUser, Deserialise <NetCom.ServerRequestGetIsSchoolDagTable>(param));
break;
case NetCom.WhatIsThisEnum.ChangeIsSchoolDagTable:
toReturn.Response = FuncsVController.ChangeIsSchooldagTable(usingUser, Deserialise <NetCom.ServerRequestChangeIsSchoolDagTable>(param));
break;
default: throw new Exception("bla bla bla lala la lal aaaaa noob");
}
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new StringContent(JsonConvert.SerializeObject(toReturn), System.Text.Encoding.UTF8, "application/json");
return(response);
}
catch (Exception ex)
{
toReturn.IsErrorOccurred = true;
toReturn.ErrorInfo.ErrorMessage = ex.Message;
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new StringContent(JsonConvert.SerializeObject(toReturn), System.Text.Encoding.UTF8, "application/json");
return(response);
}
}
public static NetCom.ServerResponseChangeIsSchoolDagTable ChangeIsSchooldagTable(DBDingus.AcountTableEntry _MasterRightsEnty, NetCom.ServerRequestChangeIsSchoolDagTable _request)
{
NetCom.ServerResponseChangeIsSchoolDagTable toReturn = new NetCom.ServerResponseChangeIsSchoolDagTable();
if (_request.deleteList.Count > 0)
{
string addings = "";
foreach (var x in _request.deleteList)
{
addings += x.ID + ", ";
}
addings = addings.Substring(0, addings.Length - 2);
FuncsVSQL.SQLNonQuery($"delete from {DBDingus.IsSchoolDagTableNames.HetIsEenSchoolDagTableNames} where {DBDingus.IsSchoolDagTableNames.ID} in ({addings})");
}
if (_request.toAddToDB.Count > 0)
{
string addings = "";
for (int x = 0; x < _request.toAddToDB.Count; x++)
{
addings += $"(cast('{_request.toAddToDB[x].Date.ToString("yyyy\\/ MM\\/ dd")}' as date)), ";
}
addings += $"(cast('{_request.toAddToDB[_request.toAddToDB.Count].Date.ToString("yyyy\\/ MM\\/ dd")}' as date))";
FuncsVSQL.SQLNonQuery($"insert into {DBDingus.IsSchoolDagTableNames.HetIsEenSchoolDagTableNames} ({DBDingus.IsSchoolDagTableNames.Date}) values {addings}");
}
return(toReturn);
}
public static NetCom.ServerResponseInteken inteken(DBDingus.AcountTableEntry _userAcount, NetCom.ServerRequestTekenInOfUit _request)
{
NetCom.ServerResponseInteken toReturn = new NetCom.ServerResponseInteken();
SqlCommand command;
//get userID of scan
command = new SqlCommand();
command.Parameters.AddWithValue("@nfcode", _request.NFCCode);
command.CommandText = $"select * from {DBDingus.UserTableNames.UserTableName} where {DBDingus.UserTableNames.NFCID} = @nfcode";
List <DBDingus.UserTableTableEntry> foundUsers = FuncsVSQL.GetListUTFromReader(command); //_DBDingus.GetListUTFromReader(FuncsVSQL.SQLQuery(command));
if (foundUsers.Count > 0)
{
toReturn.TheUserWithEntryInfo.UsE = foundUsers[0];
}
else
{
throw new Exception("Card Unknown");
}
//inteken/uiteken
command = new SqlCommand();
command.Parameters.AddWithValue("@userid", toReturn.TheUserWithEntryInfo.UsE.ID);
if (_request.DateIsToday)
{
command.CommandText = $"select * from {DBDingus.RegistratieTableNames.RegistratieTableName} where {DBDingus.RegistratieTableNames.IDOfUserRelated} = @userid and {DBDingus.RegistratieTableNames.Date} = cast(getdate() as date)";
}
else
{
command.CommandText = $"select * from {DBDingus.RegistratieTableNames.RegistratieTableName} where {DBDingus.RegistratieTableNames.IDOfUserRelated} = @userid and {DBDingus.RegistratieTableNames.Date} = cast('{_request.Date.ToString("yyyy\\/MM\\/dd")}' as date)";
}
List <DBDingus.RegistratieTableTableEntry> _existingRegEntry = FuncsVSQL.GetListRTFromReader(command); //_DBDingus.GetListRTFromReader(FuncsVSQL.SQLQuery(command));
DBDingus.RegistratieTableTableEntry existingRegEntry;
command = new SqlCommand();
if (_existingRegEntry.Count > 0)
{
// edit
existingRegEntry = _existingRegEntry[0];
existingRegEntry.IsLaat = false;
existingRegEntry.Verwachtetijdvanaanwezighijd = new TimeSpan();
command.Parameters.AddWithValue("@id", existingRegEntry.ID);
if (existingRegEntry.HeeftIngetekend)
{
if (existingRegEntry.IsAanwezig)
{
//update teken uit cast('{_Request.deEntry.Date.ToString("yyyy\\/MM\\/dd")}
toReturn.uitgetekened = true;
if (_request.DateIsToday)
{
command.CommandText = $"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.TimeUitteken} = cast(getdate() as time), {DBDingus.RegistratieTableNames.IsAanwezig} = 0 ,{DBDingus.RegistratieTableNames.IsLaat} = 0 where {DBDingus.RegistratieTableNames.Date} = cast(getdate() as date) and {DBDingus.RegistratieTableNames.IDOfUserRelated} = {toReturn.TheUserWithEntryInfo.UsE.ID}";
}
else
{
command.CommandText = $"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.TimeUitteken} = cast(getdate() as time), {DBDingus.RegistratieTableNames.IsAanwezig} = 0 ,{DBDingus.RegistratieTableNames.IsLaat} = 0 where {DBDingus.RegistratieTableNames.Date} = cast('{_request.Date.ToString("yyyy\\/MM\\/dd")}' as date) and {DBDingus.RegistratieTableNames.IDOfUserRelated} = {toReturn.TheUserWithEntryInfo.UsE.ID}";
}
}
else
{
//update anuleer uitteken
toReturn.uitekenengeanuleerd = true;
if (_request.DateIsToday)
{
command.CommandText = $"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.IsAanwezig} = 1, {DBDingus.RegistratieTableNames.IsLaat} = 0 where {DBDingus.RegistratieTableNames.Date} = cast(getdate() as date) and {DBDingus.RegistratieTableNames.IDOfUserRelated} = {toReturn.TheUserWithEntryInfo.UsE.ID}";
}
else
{
command.CommandText = $"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.IsAanwezig} = 1, {DBDingus.RegistratieTableNames.IsLaat} = 0 where {DBDingus.RegistratieTableNames.Date} = cast('{_request.Date.ToString("yyyy\\/MM\\/dd")}' as date) and {DBDingus.RegistratieTableNames.IDOfUserRelated} = {toReturn.TheUserWithEntryInfo.UsE.ID}";
}
}
}
else
{
//update inteken
toReturn.ingetekened = true;
if (_request.DateIsToday)
{
command.CommandText = $"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.HeeftIngetekend} = 1, {DBDingus.RegistratieTableNames.TimeInteken} = cast(getdate() as time), {DBDingus.RegistratieTableNames.IsAanwezig} = 1, {DBDingus.RegistratieTableNames.IsLaat} = 0 where {DBDingus.RegistratieTableNames.Date} = cast(getdate() as date) and {DBDingus.RegistratieTableNames.IDOfUserRelated} = {toReturn.TheUserWithEntryInfo.UsE.ID}";
}
else
{
command.CommandText = $"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.HeeftIngetekend} = 1, {DBDingus.RegistratieTableNames.TimeInteken} = cast(getdate() as time), {DBDingus.RegistratieTableNames.IsAanwezig} = 1, {DBDingus.RegistratieTableNames.IsLaat} = 0 where {DBDingus.RegistratieTableNames.Date} = cast('{_request.Date.ToString("yyyy\\/MM\\/dd")}' as date) and {DBDingus.RegistratieTableNames.IDOfUserRelated} = {toReturn.TheUserWithEntryInfo.UsE.ID}";
}
}
}
else
{
//new
//inteken
command.Parameters.AddWithValue("@relatedUserId", foundUsers[0].ID);
toReturn.ingetekened = true;
if (_request.DateIsToday)
{
command.CommandText = $"insert into {DBDingus.RegistratieTableNames.RegistratieTableName} ({DBDingus.RegistratieTableNames.IDOfUserRelated},{DBDingus.RegistratieTableNames.Date},{DBDingus.RegistratieTableNames.TimeInteken},{DBDingus.RegistratieTableNames.HeeftIngetekend},{DBDingus.RegistratieTableNames.IsAanwezig},{DBDingus.RegistratieTableNames.IsZiek},{DBDingus.RegistratieTableNames.IsFlexibelverlof},{DBDingus.RegistratieTableNames.IsStudieverlof},{DBDingus.RegistratieTableNames.IsExcursie},{DBDingus.RegistratieTableNames.IsLaat},{DBDingus.RegistratieTableNames.IsToegestaanAfwezig},{DBDingus.RegistratieTableNames.Opmerking},{DBDingus.RegistratieTableNames.Verwachtetijdvanaanwezighijd}) values (@relatedUserId, cast(getdate() as date), cast(getdate() as time), 1,1,0,0,0,0,0,0,'','')";
}
else
{
command.CommandText = $"insert into {DBDingus.RegistratieTableNames.RegistratieTableName} ({DBDingus.RegistratieTableNames.IDOfUserRelated},{DBDingus.RegistratieTableNames.Date},{DBDingus.RegistratieTableNames.TimeInteken},{DBDingus.RegistratieTableNames.HeeftIngetekend},{DBDingus.RegistratieTableNames.IsAanwezig},{DBDingus.RegistratieTableNames.IsZiek},{DBDingus.RegistratieTableNames.IsFlexibelverlof},{DBDingus.RegistratieTableNames.IsStudieverlof},{DBDingus.RegistratieTableNames.IsExcursie},{DBDingus.RegistratieTableNames.IsLaat},{DBDingus.RegistratieTableNames.IsToegestaanAfwezig},{DBDingus.RegistratieTableNames.Opmerking},{DBDingus.RegistratieTableNames.Verwachtetijdvanaanwezighijd}) values (@relatedUserId, cast('{_request.Date.ToString("yyyy\\/MM\\/dd")}' as date), cast(getdate() as time), 1,1,0,0,0,0,0,0,'','')";
}
}
FuncsVSQL.SQLNonQuery(command);
command = new SqlCommand();
command.Parameters.AddWithValue("@userid", toReturn.TheUserWithEntryInfo.UsE.ID);
if (_request.DateIsToday)
{
command.CommandText = $"select * from {DBDingus.RegistratieTableNames.RegistratieTableName} where {DBDingus.RegistratieTableNames.IDOfUserRelated} = @userid and {DBDingus.RegistratieTableNames.Date} = cast(getdate() as date)";
}
else
{
command.CommandText = $"select * from {DBDingus.RegistratieTableNames.RegistratieTableName} where {DBDingus.RegistratieTableNames.IDOfUserRelated} = @userid and {DBDingus.RegistratieTableNames.Date} = cast('{_request.Date.ToString("yyyy\\/MM\\/dd")}' as date)";
}
List <DBDingus.RegistratieTableTableEntry> endResult = FuncsVSQL.GetListRTFromReader(command); //_DBDingus.GetListRTFromReader(FuncsVSQL.SQLQuery(command));
toReturn.TheUserWithEntryInfo.HasTodayRegEntry = true;
toReturn.TheUserWithEntryInfo.RegE = endResult[0];
return(toReturn);
}
public static NetCom.ServerResponseChangeRegistratieTable ChangeRegistatieTable(DBDingus.AcountTableEntry _MasterRightsEntry, NetCom.ServerRequestChangeRegistratieTable _Request)
{
NetCom.ServerResponseChangeRegistratieTable _toReturn = new NetCom.ServerResponseChangeRegistratieTable();
SqlCommand _commamd = new SqlCommand();
_commamd.Parameters.AddWithValue("@andered", _Request.deEntry.Opmerking);
_commamd.Parameters.AddWithValue("@verwachtetijdvana", _Request.deEntry.Verwachtetijdvanaanwezighijd);
if (_Request.isNieuwEntry)
{
if (_Request.newEntryDateIsToday)
{
_commamd.CommandText = $"insert into {DBDingus.RegistratieTableNames.RegistratieTableName}( {DBDingus.RegistratieTableNames.IDOfUserRelated}, {DBDingus.RegistratieTableNames.Date}, {DBDingus.RegistratieTableNames.TimeInteken}, {DBDingus.RegistratieTableNames.TimeUitteken}, {DBDingus.RegistratieTableNames.HeeftIngetekend}, {DBDingus.RegistratieTableNames.IsAanwezig}, {DBDingus.RegistratieTableNames.IsZiek}, {DBDingus.RegistratieTableNames.IsFlexibelverlof}, {DBDingus.RegistratieTableNames.IsStudieverlof}, {DBDingus.RegistratieTableNames.IsExcursie}, {DBDingus.RegistratieTableNames.IsLaat}, {DBDingus.RegistratieTableNames.IsToegestaanAfwezig}, {DBDingus.RegistratieTableNames.Opmerking}, {DBDingus.RegistratieTableNames.Verwachtetijdvanaanwezighijd}) values ( {_Request.deEntry.IDOfUserRelated}, cast(getdate() as date), cast('{_Request.deEntry.TimeInteken}' as time), cast('{_Request.deEntry.TimeUitteken}' as time), cast('{_Request.deEntry.HeeftIngetekend}' as bit), cast('{_Request.deEntry.IsAanwezig}' as bit), cast('{_Request.deEntry.IsZiek}' as bit), cast('{_Request.deEntry.IsFlexiebelverlof}' as bit), cast('{_Request.deEntry.IsStudieverlof}' as bit), cast('{_Request.deEntry.IsExcurtie}' as bit), cast('{_Request.deEntry.IsLaat}' as bit), cast('{_Request.deEntry.IsToegestaalAfwezig}' as bit), @andered,cast(@verwachtetijdvana as time) )";
}
else
{
_commamd.CommandText = $"insert into {DBDingus.RegistratieTableNames.RegistratieTableName}( {DBDingus.RegistratieTableNames.IDOfUserRelated}, {DBDingus.RegistratieTableNames.Date}, {DBDingus.RegistratieTableNames.TimeInteken}, {DBDingus.RegistratieTableNames.TimeUitteken}, {DBDingus.RegistratieTableNames.HeeftIngetekend}, {DBDingus.RegistratieTableNames.IsAanwezig}, {DBDingus.RegistratieTableNames.IsZiek}, {DBDingus.RegistratieTableNames.IsFlexibelverlof}, {DBDingus.RegistratieTableNames.IsStudieverlof}, {DBDingus.RegistratieTableNames.IsExcursie}, {DBDingus.RegistratieTableNames.IsLaat}, {DBDingus.RegistratieTableNames.IsToegestaanAfwezig}, {DBDingus.RegistratieTableNames.Opmerking}, {DBDingus.RegistratieTableNames.Verwachtetijdvanaanwezighijd}) values ( {_Request.deEntry.IDOfUserRelated}, cast('{_Request.deEntry.Date.ToString("yyyy\\/MM\\/dd")}' as date), cast('{_Request.deEntry.TimeInteken}' as time), cast('{_Request.deEntry.TimeUitteken}' as time), cast('{_Request.deEntry.HeeftIngetekend}' as bit), cast('{_Request.deEntry.IsAanwezig}' as bit), cast('{_Request.deEntry.IsZiek}' as bit), cast('{_Request.deEntry.IsFlexiebelverlof}' as bit), cast('{_Request.deEntry.IsStudieverlof}' as bit), cast('{_Request.deEntry.IsExcurtie}' as bit), cast('{_Request.deEntry.IsLaat}' as bit), cast('{_Request.deEntry.IsToegestaalAfwezig}' as bit), @andered, cast(@verwachtetijdvana as time) )";
}
if (FuncsVSQL.SQLNonQuery(_commamd) > 0)
{
//_toReturn.deEntry=_Request.deEntry;
// _toReturn.deEntry.ID=(int)SqlDingusEnUserRechten.SQLQuery("select SCOPE_IDENTITY() as [yui]").Rows[0]["yui"];
}
else
{
throw new Exception("SQL CHANGED_0 ERROR AT: " + _commamd.CommandText);
}
}
else
{
if (_Request.newEntryDateIsToday)
{
_commamd.CommandText = $@"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.IDOfUserRelated} = {_Request.deEntry.IDOfUserRelated}, {DBDingus.RegistratieTableNames.Date} = cast(getdate() as date), {DBDingus.RegistratieTableNames.TimeInteken} = cast('{_Request.deEntry.TimeInteken}' as time), {DBDingus.RegistratieTableNames.TimeUitteken} = cast('{_Request.deEntry.TimeUitteken}' as time), {DBDingus.RegistratieTableNames.IsAanwezig} = cast('{_Request.deEntry.IsAanwezig}' as bit), {DBDingus.RegistratieTableNames.HeeftIngetekend} = cast('{_Request.deEntry.HeeftIngetekend}' as bit), {DBDingus.RegistratieTableNames.IsZiek} = cast('{_Request.deEntry.IsZiek}' as bit), {DBDingus.RegistratieTableNames.IsFlexibelverlof} = cast('{_Request.deEntry.IsFlexiebelverlof}' as bit), {DBDingus.RegistratieTableNames.IsStudieverlof} = cast('{_Request.deEntry.IsStudieverlof}' as bit), {DBDingus.RegistratieTableNames.IsExcursie} = cast('{_Request.deEntry.IsExcurtie}' as bit), {DBDingus.RegistratieTableNames.IsLaat} = cast('{_Request.deEntry.IsLaat}' as bit), {DBDingus.RegistratieTableNames.IsToegestaanAfwezig} = cast('{_Request.deEntry.IsToegestaalAfwezig}' as bit), {DBDingus.RegistratieTableNames.Opmerking} = @andered, {DBDingus.RegistratieTableNames.Verwachtetijdvanaanwezighijd} = cast(@verwachtetijdvana as time) where {DBDingus.RegistratieTableNames.ID} = {_Request.deEntry.ID}";
}
else
{
_commamd.CommandText = $@"update {DBDingus.RegistratieTableNames.RegistratieTableName} set {DBDingus.RegistratieTableNames.IDOfUserRelated} = {_Request.deEntry.IDOfUserRelated}, {DBDingus.RegistratieTableNames.Date} = cast('{_Request.deEntry.Date.ToString("yyyy\\/MM\\/dd")}' as date), {DBDingus.RegistratieTableNames.TimeInteken} = cast('{_Request.deEntry.TimeInteken}' as time), {DBDingus.RegistratieTableNames.TimeUitteken} = cast('{_Request.deEntry.TimeUitteken}' as time), {DBDingus.RegistratieTableNames.IsAanwezig} = cast('{_Request.deEntry.IsAanwezig}' as bit), {DBDingus.RegistratieTableNames.HeeftIngetekend} = cast('{_Request.deEntry.HeeftIngetekend}' as bit), {DBDingus.RegistratieTableNames.IsZiek} = cast('{_Request.deEntry.IsZiek}' as bit), {DBDingus.RegistratieTableNames.IsFlexibelverlof} = cast('{_Request.deEntry.IsFlexiebelverlof}' as bit), {DBDingus.RegistratieTableNames.IsStudieverlof} = cast('{_Request.deEntry.IsStudieverlof}' as bit), {DBDingus.RegistratieTableNames.IsExcursie} = cast('{_Request.deEntry.IsExcurtie}' as bit), {DBDingus.RegistratieTableNames.IsLaat} = cast('{_Request.deEntry.IsLaat}' as bit), {DBDingus.RegistratieTableNames.IsToegestaanAfwezig} = cast('{_Request.deEntry.IsToegestaalAfwezig}' as bit), {DBDingus.RegistratieTableNames.Opmerking} = @andered, {DBDingus.RegistratieTableNames.Verwachtetijdvanaanwezighijd} = cast(@verwachtetijdvana as time) where {DBDingus.RegistratieTableNames.ID} = {_Request.deEntry.ID}";
}
if (FuncsVSQL.SQLNonQuery(_commamd) > 0)
{
//_toReturn.deEntry=_Request.deEntry;
}
else
{
throw new Exception("SQL CHANGED_0 ERROR AT: " + _commamd.CommandText);
}
}
return(_toReturn);
}
public static NetCom.ServerResponseOverzightFromMultipleDates alleDagOverzightenVanTussenTweDatums(DBDingus.AcountTableEntry _userAcount, NetCom.ServerRequestOverzightFromMultipleDates _request)
{
NetCom.ServerResponseOverzightFromMultipleDates toReturn = new NetCom.ServerResponseOverzightFromMultipleDates();
foreach (DateTime dag in ElkeDatumTussenTweDatums(_request.FromAndWithThisDate, _request.TotEnMetDezeDatum))
{
NetCom.ServerResponseOverzightFromMultipleDatesSubType toAddToList = new NetCom.ServerResponseOverzightFromMultipleDatesSubType();
toAddToList.DateOfOverzight = dag;
NetCom.ServerRequestOverzightFromOneDate moreRequest = new NetCom.ServerRequestOverzightFromOneDate();
moreRequest.alsoReturnExUsers = _request.getForExUsers;
moreRequest.useToday = false;
moreRequest.dateToGetOverzightFrom = dag;
toAddToList.OverZichtFromThisDate = overzight(_userAcount, moreRequest);
toReturn.allesDatJeNodigHebt.Add(toAddToList);
}
return(toReturn);
}
