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.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); }