Egalware/gpw_next
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
develop
gpw_next/GPW.CORE.Data/Controllers/GPWController.cs
T

3929 lines
154 KiB
C#
Raw Permalink Blame History

using EgwCoreLib.Utils;
using GPW.CORE.Data.DbModels;
using GPW.CORE.Data.DTO;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using NLog;
using System.Data;
namespace GPW.CORE.Data.Controllers
{
public class GPWController : IDisposable
{
#region Public Constructors
public GPWController(IConfiguration configuration)
{
_configuration = configuration;
}
#endregion Public Constructors
#region Public Methods
/// <summary>
/// Recupera l'elenco Clienti (tutti)
/// </summary>
/// <returns></returns>
public List<AnagClientiModel> AnagClientiAll()
{
// init dati necessari
List<AnagClientiModel> dbResult = new List<AnagClientiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagClienti
.OrderBy(x => x.RagSociale)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagClientiAllAsync:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Elimina il cliente (se non ci sono relazioni con fasi attive, in tal caso rende disattivo...)
/// </summary>
/// <returns></returns>
public bool AnagClientiDelete(AnagClientiModel remRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagClienti
.Where(x => x.IdxCliente == remRec.IdxCliente)
.FirstOrDefault();
if (dbRec != null)
{
// controllo se abbia progetti associati
var listFasi = dbCtx
.DbSetAnagProgetti
.Where(x => x.IdxCliente == remRec.IdxCliente)
.ToList();
// --> in tal caso disattivo...
if (listFasi != null && listFasi.Count > 0)
{
dbRec.Attivo = false;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
else
{
// altrimenti elimino davvero...
dbCtx
.DbSetAnagClienti
.Remove(dbRec);
}
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagClientiDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera l'elenco Clienti (tutti)
/// </summary>
/// <returns></returns>
public bool AnagClientiUpsert(AnagClientiModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagClienti
.Where(x => x.IdxCliente == upsRec.IdxCliente)
.FirstOrDefault();
if (dbRec == null)
{
dbCtx
.DbSetAnagClienti
.Add(upsRec);
}
else
{
dbRec.Attivo = upsRec.Attivo;
dbRec.Cap = upsRec.Cap;
dbRec.Cf = upsRec.Cf;
dbRec.Citta = upsRec.Citta;
dbRec.CodExt = upsRec.CodExt;
dbRec.Email = upsRec.Email;
dbRec.Indirizzo = upsRec.Indirizzo;
dbRec.Nota = upsRec.Nota;
dbRec.PIva = upsRec.PIva;
dbRec.Prov = upsRec.Prov;
dbRec.RagSociale = upsRec.RagSociale;
dbRec.Tel = upsRec.Tel;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagClientiUpsert:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Cerca un device dato il suo secret
/// </summary>
/// <param name="devSecret"></param>
/// <returns></returns>
public AnagDeviceModel? AnagDeviceByKey(string devSecret)
{
// init dati necessari
AnagDeviceModel? dbResult = new AnagDeviceModel();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagDevices
.Where(x => x.DeviceSecret == devSecret)
.FirstOrDefault();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagDeviceByKey:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Crea un record device
/// </summary>
/// <param name="newRecord"></param>
/// <returns></returns>
public bool AnagDeviceInsert(AnagDeviceModel newRecord)
{
// init dati necessari
bool done = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbCtx
.DbSetAnagDevices
.Add(newRecord);
// ora salvo!
dbCtx.SaveChanges();
done = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagDeviceInsert:{Environment.NewLine}{exc}");
}
}
return done;
}
/// <summary>
/// Crea un record device
/// </summary>
/// <param name="currItem"></param>
/// <returns></returns>
public bool AnagDeviceUpdate(AnagDeviceModel currItem)
{
// init dati necessari
bool done = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
if (currItem.IdxDevice == 0)
{
dbCtx.Entry(currItem).State = EntityState.Added;
}
else
{
// recupero vecchio
var oldRec = dbCtx
.DbSetAnagDevices
.Where(x => x.IdxDevice == currItem.IdxDevice)
.FirstOrDefault();
if (oldRec != null)
{
oldRec.LastIpv4 = currItem.LastIpv4;
oldRec.DataOraLastSeen = DateTime.Now;
oldRec.Description = currItem.Description;
oldRec.ScreenSize = currItem.ScreenSize;
dbCtx
.DbSetAnagDevices
.Update(oldRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetAnagDevices
.Update(currItem);
}
}
// ora salvo!
dbCtx.SaveChanges();
done = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagDeviceUpdate:{Environment.NewLine}{exc}");
}
}
return done;
}
/// <summary>
/// Recupera l'elenco delle fasi (tutte)
/// </summary>
/// <returns></returns>
public List<AnagFasiModel> AnagFasiAll(bool onlyActive)
{
// init dati necessari
List<AnagFasiModel> dbResult = new List<AnagFasiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagFasi
.Where(x => (x.ProgettoNav != null && x.ProgettoNav.Attivo == true) && (((bool)x.Attivo == true && onlyActive == true) || !onlyActive))
.Include(p => p.ProgettoNav)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiAll:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Elenco fasi dato ancestor
/// </summary>
/// <param name="idxFaseAnc"></param>
/// <returns></returns>
public List<AnagFasiModel> AnagFasiByAncestor(int idxFaseAnc)
{
// init dati necessari
List<AnagFasiModel> dbResult = new List<AnagFasiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
var searchRecord = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxFaseAncest == idxFaseAnc)
.Include(p => p.ProgettoNav)
.ToList();
if (searchRecord != null)
{
dbResult = searchRecord;
}
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiByAncestor:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Recupera singola fase
/// </summary>
/// <param name="idxFase"></param>
/// <returns></returns>
public AnagFasiModel AnagFasiByKey(int idxFase)
{
// init dati necessari
AnagFasiModel dbResult = new AnagFasiModel();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
var searchRecord = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxFase == idxFase)
.Include(p => p.ProgettoNav)
.FirstOrDefault();
if (searchRecord != null)
{
dbResult = searchRecord;
}
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiByKey:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Elenco fasi dato chiave progetto
/// </summary>
/// <param name="idxProj"></param>
/// <returns></returns>
public List<AnagFasiModel> AnagFasiByProj(int idxProj)
{
// init dati necessari
List<AnagFasiModel> dbResult = new List<AnagFasiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxProgetto == idxProj)
.Include(p => p.ProgettoNav)
.OrderBy(x => x.CodFase)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiByProj:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public bool AnagFasiDelete(int IdxFase)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxFase == IdxFase)
.FirstOrDefault();
if (dbRec != null)
{
// verifico NON abbia child...
var listChild = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxFaseAncest == IdxFase)
.ToList();
if (listChild == null || listChild.Count == 0)
{
dbCtx
.DbSetAnagFasi
.Remove(dbRec);
}
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Esegue stored x DUPLICARE Fase (ancestor) tra progetti
/// </summary>
/// <param name="idxProjDest"></param>
/// <param name="idxFaseSrc"></param>
/// <returns></returns>
public bool AnagFasiDupFase(int idxProjDest, int idxFaseSrc)
{
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxPrj = new SqlParameter("@idxProgetto", idxProjDest);
var pIdxFase = new SqlParameter("@Original_idxFase", idxFaseSrc);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AF_clonaFaseAnc @idxProgetto, @Original_idxFase", pIdxPrj, pIdxFase);
fatto = dbResult != 0;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiDupFase:{Environment.NewLine}{exc}");
}
}
return fatto;
}
/// <summary>
/// Esegue stored x DUPLICARE SottoFase in un altra Fase (ancestor)
/// </summary>
/// <param name="idxFaseDest"></param>
/// <param name="idxFaseSrc"></param>
/// <returns></returns>
public bool AnagFasiDupSubFase(int idxFaseDest, int idxFaseSrc)
{
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxFaseDest = new SqlParameter("@idxFaseAncest", idxFaseDest);
var pIdxFaseSrc = new SqlParameter("@Original_idxFase", idxFaseSrc);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AF_clonaFase @idxFaseAncest, @Original_idxFase", pIdxFaseDest, pIdxFaseSrc);
fatto = dbResult != 0;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiDupSubFase:{Environment.NewLine}{exc}");
}
}
return fatto;
}
/// <summary>
/// Elenco FasiExpl dato chiave progetto
/// </summary>
/// <param name="idxProj"></param>
/// <returns></returns>
public List<AnagFasiExplModel> AnagFasiExplByProj(int idxProj)
{
// init dati necessari
List<AnagFasiExplModel> dbResult = new List<AnagFasiExplModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxPrj = new SqlParameter("@idxProgetto", idxProj);
dbResult = dbCtx
.DbSetAnagFasiExpl
.FromSqlRaw("EXEC stp_AF_getByIdxProj @idxProgetto", pIdxPrj)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiExplByProj:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Aggiorno tutte le fasi del proj con il tag indicato
/// </summary>
/// <param name="idxProj"></param>
/// <param name="CodTagFase"></param>
/// <returns></returns>
public bool AnagFasiForceTag(int idxProj, string CodTagFase)
{
int result = 0;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxPrj = new SqlParameter("@idxProgetto", idxProj);
var pCodTag = new SqlParameter("@CodTagFase", CodTagFase);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AF_updCodFaseByProj @idxProgetto, @CodTagFase", pIdxPrj, pCodTag);
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiForceTag:{Environment.NewLine}{exc}");
}
}
return result != 0;
}
/// <summary>
/// Esegue stored x spostare Fase (ancestor) tra progetti
/// </summary>
/// <param name="idxProjDest"></param>
/// <param name="idxFaseSrc"></param>
/// <returns></returns>
public bool AnagFasiMovFase(int idxProjDest, int idxFaseSrc)
{
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxPrj = new SqlParameter("@idxProgetto", idxProjDest);
var pIdxFase = new SqlParameter("@Original_idxFase", idxFaseSrc);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AF_updateProgetto @idxProgetto, @Original_idxFase", pIdxPrj, pIdxFase);
fatto = dbResult != 0;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiMovFase:{Environment.NewLine}{exc}");
}
}
return fatto;
}
/// <summary>
/// Esegue stored x spostare SottoFase in un altra Fase (ancestor)
/// </summary>
/// <param name="idxFaseDest"></param>
/// <param name="idxFaseSrc"></param>
/// <returns></returns>
public bool AnagFasiMovSubFase(int idxFaseDest, int idxFaseSrc)
{
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxFaseDest = new SqlParameter("@idxFaseAncest", idxFaseDest);
var pIdxFaseSrc = new SqlParameter("@Original_idxFase", idxFaseSrc);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AF_updateFaseAncest @idxFaseAncest, @Original_idxFase", pIdxFaseDest, pIdxFaseSrc);
fatto = dbResult != 0;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagFasiMovSubFase:{Environment.NewLine}{exc}");
}
}
return fatto;
}
/// <summary>
/// Esecuzione upsert fase
/// </summary>
/// <param name="upsRec"></param>
/// <returns></returns>
public bool AnagFasiUpsert(AnagFasiModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxFase == upsRec.IdxFase && upsRec.IdxFase > 0)
.FirstOrDefault();
if (dbRec == null)
{
dbCtx
.DbSetAnagFasi
.Add(upsRec);
}
else
{
dbRec.Attivo = upsRec.Attivo;
dbRec.BudgetMoney = upsRec.BudgetMoney;
dbRec.BudgetTime = upsRec.BudgetTime;
dbRec.CodExt = upsRec.CodExt;
dbRec.CodTagFase = upsRec.CodTagFase;
dbRec.DescrizioneFase = upsRec.DescrizioneFase;
dbRec.EnableMoney = upsRec.EnableMoney;
dbRec.EnableTime = upsRec.EnableTime;
dbRec.IdxFaseAncest = upsRec.IdxFaseAncest;
dbRec.IdxProgetto = upsRec.IdxProgetto;
dbRec.NomeFase = upsRec.NomeFase;
dbRec.PercOpen = upsRec.PercOpen;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagTagFasiUpsert:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Elenco Giustificativi
/// </summary>
/// <returns></returns>
public List<AnagGiustModel> AnagGiust()
{
// init dati necessari
List<AnagGiustModel> dbResult = new List<AnagGiustModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagGiust
.OrderBy(x => x.codGiust)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagGiust:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public List<AnagGruppiModel> AnagGruppiAll()
{
// init dati necessari
List<AnagGruppiModel> dbResult = new List<AnagGruppiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagGruppi
.Include(d => d.Dipendenti2GruppiNav)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagGruppiAll:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Eliminazione record gruppo
/// </summary>
/// <param name="currItem"></param>
/// <returns></returns>
public bool AnagGruppiDelete(AnagGruppiModel currItem)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagGruppi
.Where(x => x.Gruppo == currItem.Gruppo)
.FirstOrDefault();
if (dbRec != null)
{
dbCtx
.DbSetAnagGruppi
.Remove(dbRec);
dbCtx.SaveChanges();
answ = true;
}
}
catch (Exception exc)
{
Log.Error($"Errore in AnagGruppiDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Upsert record gruppo
/// </summary>
/// <param name="upsRec"></param>
/// <returns></returns>
public bool AnagGruppiUpsert(AnagGruppiModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagGruppi
.Where(x => x.Gruppo == upsRec.Gruppo)
.FirstOrDefault();
if (dbRec == null)
{
dbCtx
.DbSetAnagGruppi
.Add(upsRec);
}
else
{
dbRec.CodExt = upsRec.CodExt;
dbRec.DescrGruppo = upsRec.DescrGruppo;
dbRec.ExportEnab = upsRec.ExportEnab;
dbRec.Gruppo = upsRec.Gruppo;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagGruppiUpsert:{Environment.NewLine}{exc}");
}
}
return answ;
}
public List<AnagGruppiModel> AnagGruppiUser(int idxDipendente)
{
// init dati necessari
List<AnagGruppiModel> dbResult = new List<AnagGruppiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagGruppi
.Where(x => x.Dipendenti2GruppiNav.Contains(new Dipendenti2GruppiModel() { IdxDipendente = idxDipendente, Gruppo = x.Gruppo }))
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagGruppiUser:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public List<AnagKeyValueModel> AnagKeyValGetAll()
{
List<AnagKeyValueModel> dbResult = new List<AnagKeyValueModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetAnagKeyValue
.ToList();
}
return dbResult;
}
/// <summary>
/// Anagrafica orari gestiti
/// </summary>
/// <returns></returns>
public List<AnagOrariModel> AnagOrarioAll()
{
List<AnagOrariModel> dbResult = new List<AnagOrariModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set....
dbResult = dbCtx
.DbSetAnagOrari
.Include(d => d.DipendentiNav)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagOrarioAll:{Environment.NewLine}{exc}");
}
}
if (dbResult == null)
{
dbResult = new List<AnagOrariModel>();
}
return dbResult;
}
/// <summary>
/// Dati orario del dipendente indicato
/// </summary>
/// <param name="idxProj"></param>
/// <returns></returns>
public AnagOrariModel AnagOrarioByDip(int idxDip)
{
AnagOrariModel? dbResult = new AnagOrariModel();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set....
dbResult = dbCtx
.DbSetAnagOrari
.Where(x => x.DipendentiNav.Count > 0 && x.DipendentiNav.First().IdxDipendente == idxDip)
.FirstOrDefault();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagOrarioByDip:{Environment.NewLine}{exc}");
}
}
if (dbResult == null)
{
dbResult = new AnagOrariModel();
}
return dbResult;
}
/// <summary>
/// Eliminazione orario, con controllo NON sia usato...
/// </summary>
/// <returns></returns>
public bool AnagOrarioDelete(AnagOrariModel rec2del)
{
bool fatto = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// verifica preliminare NON sia usato da dip...
int numDip = dbCtx
.DbSetDipendenti
.Where(x => x.CodOrario == rec2del.codOrario)
.Count();
if (numDip == 0)
{
// recupero record....
var dbRec = dbCtx
.DbSetAnagOrari
.Where(x => x.codOrario == rec2del.codOrario)
.FirstOrDefault();
if (dbRec != null)
{
dbCtx.DbSetAnagOrari.Remove(dbRec);
int numRec = dbCtx.SaveChanges();
fatto = numRec != 0;
}
}
}
catch (Exception exc)
{
Log.Error($"Errore in AnagOrarioDelete:{Environment.NewLine}{exc}");
}
}
return fatto;
}
/// <summary>
/// Tabella setup dati orari - elenco completo
/// </summary>
/// <param name="idxProj"></param>
/// <returns></returns>
public List<AnagOrariModel> AnagOrarioGetAll()
{
List<AnagOrariModel> dbResult = new List<AnagOrariModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set....
dbResult = dbCtx
.DbSetAnagOrari
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagOrarioGetAll:{Environment.NewLine}{exc}");
}
}
if (dbResult == null)
{
dbResult = new List<AnagOrariModel>();
}
return dbResult;
}
/// <summary>
/// Insert/Update record orario
/// </summary>
/// <param name="rec2upd"></param>
/// <param name="codOrarioOrig"></param>
/// <returns></returns>
public bool AnagOrarioUpsert(AnagOrariModel rec2upd, string codOrarioOrig)
{
bool fatto = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero record....
var dbRec = dbCtx
.DbSetAnagOrari
.Where(x => x.codOrario == codOrarioOrig && !string.IsNullOrEmpty(codOrarioOrig))
.FirstOrDefault();
if (dbRec != null)
{
dbRec.codOrario = rec2upd.codOrario;
dbRec.descOrario = rec2upd.descOrario;
dbRec.oreLun = rec2upd.oreLun;
dbRec.oreMar = rec2upd.oreMar;
dbRec.oreMer = rec2upd.oreMer;
dbRec.oreGio = rec2upd.oreGio;
dbRec.oreVen = rec2upd.oreVen;
dbRec.oreSab = rec2upd.oreSab;
dbRec.oreDom = rec2upd.oreDom;
dbRec.oreStraordAss = dbRec.oreStraordAss;
dbRec.autoCompOreOrd = dbRec.autoCompOreOrd;
dbRec.oraInizio1 = dbRec.oraInizio1;
dbRec.oraFine1 = dbRec.oraFine1;
dbRec.oraInizio2 = dbRec.oraInizio2;
dbRec.oraFine2 = dbRec.oraFine2;
dbRec.oraInizio3 = dbRec.oraInizio3;
dbRec.oraFine3 = dbRec.oraFine3;
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
else
{
dbCtx.DbSetAnagOrari.Add(rec2upd);
}
int numRec = dbCtx.SaveChanges();
fatto = numRec != 0;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagOrarioUpsert:{Environment.NewLine}{exc}");
}
}
return fatto;
}
public List<AnagProgettiModel> AnagProjAll(bool onlyActive)
{
// init dati necessari
List<AnagProgettiModel> dbResult = new List<AnagProgettiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set....
dbResult = dbCtx
.DbSetAnagProgetti
.Where(x => ((bool)(x.Attivo ?? false) == true && onlyActive == true) || !onlyActive)
.Include(c => c.ClienteNav)
.Include(g => g.GruppiNav)
.OrderBy(x => x.NomeProj)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagProjAll:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Recupera elenco record da idx cliente
/// </summary>
/// <returns></returns>
public List<AnagProgettiModel> AnagProjByCli(int idxCliente)
{
// init dati necessari
List<AnagProgettiModel> dbResult = new List<AnagProgettiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set....
dbResult = dbCtx
.DbSetAnagProgetti
.Where(x => x.IdxCliente == idxCliente)
.OrderBy(x => x.NomeProj)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagProjByCliAsync:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public AnagProgettiModel AnagProjByKey(int IdxProj)
{
// init dati necessari
AnagProgettiModel dbResult = new AnagProgettiModel();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set....
var dbRec = dbCtx
.DbSetAnagProgetti
.Where(x => (x.IdxProgetto == IdxProj))
.FirstOrDefault();
if (dbRec != null)
{
dbResult = dbRec;
}
}
catch (Exception exc)
{
Log.Error($"Errore in AnagProjByKey:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Elenco progetti calcolati con ore avanzamento filtrata
/// </summary>
/// <param name="gruppo"></param>
/// <param name="idxCliente"></param>
/// <param name="showPrjArch"></param>
/// <param name="showPrjZeroH"></param>
/// <param name="showPrjStar"></param>
/// <returns></returns>
public List<CalcOreProgettiModel> AnagProjCalcFilt(string gruppo, int idxCliente, bool showPrjArch, bool showPrjZeroH, bool showPrjStar)
{
List<CalcOreProgettiModel> dbResult = new List<CalcOreProgettiModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var pGruppo = new SqlParameter("@gruppo", gruppo);
var pIdxCli = new SqlParameter("@idxCliente", idxCliente);
var pShowPAr = new SqlParameter("@showPrjArch", showPrjArch);
var pShowPZH = new SqlParameter("@showPrjZeroH", showPrjZeroH);
var pShowPSt = new SqlParameter("@showPrjStar", showPrjStar);
dbResult = dbCtx
.DbSetCalcOreProj
.FromSqlRaw("EXEC stp_AP_Expl_getData @gruppo, @idxCliente, @showPrjArch, @showPrjZeroH, @showPrjStar", pGruppo, pIdxCli, pShowPAr, pShowPZH, pShowPSt)
.ToList();
}
return dbResult;
}
public bool AnagProjDelete(AnagProgettiModel remRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagProgetti
.Where(x => x.IdxProgetto == remRec.IdxProgetto)
.FirstOrDefault();
if (dbRec != null)
{
// controllo se abbia progetti associati
var listFasi = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxProgetto == remRec.IdxProgetto)
.ToList();
// --> in tal caso disattivo...
if (listFasi != null && listFasi.Count > 0)
{
dbRec.Attivo = false;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
else
{
// altrimenti elimino davvero...
dbCtx
.DbSetAnagProgetti
.Remove(dbRec);
}
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagProjDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Clona record Progetti con stored
/// </summary>
/// <param name="IdxProj"></param>
/// <param name="NewName"></param>
/// <param name="DoYearSubst"></param>
/// <returns></returns>
public bool AnagProjDoClone(int IdxProj, string NewName, bool DoYearSubst)
{
int result = 0;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var pIdxProj = new SqlParameter("@idxProgetto", IdxProj);
var pNomeProj = new SqlParameter("@nomeProj", NewName);
var pSostAnnoFasi = new SqlParameter("@sostAnnoFasi", DoYearSubst);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AP_duplicateProj @idxProgetto, @nomeProj, @sostAnnoFasi", pIdxProj, pNomeProj, pSostAnnoFasi);
}
return result != 0;
}
/// <summary>
/// Esegue update calcolo dati proj x tutti (anche archiviati)
/// </summary>
/// <returns></returns>
public bool AnagProjForceUpdate()
{
int result = 0;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
// imposto default x ricalcolo di tutti
int IdxProj = 0;
string gruppo = "";
int IdxCli = 0;
bool DoArch = true;
var pIdxProj = new SqlParameter("@idxProgetto", IdxProj);
var pGruppo = new SqlParameter("@gruppo", gruppo);
var pIdxCli = new SqlParameter("@idxCliente", IdxCli);
var pPrjArch = new SqlParameter("@PrjArch", DoArch);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_AP_Summary_Update @idxProgetto, @gruppo, @idxCliente, @PrjArch", pIdxProj, pGruppo, pIdxCli, pPrjArch);
}
return result != 0;
}
public bool AnagProjUpdStatus(int IdxProj, bool Attivo, bool Starred)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagProgetti
.Where(x => x.IdxProgetto == IdxProj)
.FirstOrDefault();
if (dbRec != null)
{
dbRec.Attivo = Attivo;
dbRec.Starred = Starred;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagProjUpdStatus:{Environment.NewLine}{exc}");
}
}
return answ;
}
public bool AnagProjUpsert(AnagProgettiModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagProgetti
.Where(x => x.IdxProgetto == upsRec.IdxProgetto)
.FirstOrDefault();
if (dbRec == null)
{
dbCtx
.DbSetAnagProgetti
.Add(upsRec);
}
else
{
dbRec.Attivo = upsRec.Attivo;
dbRec.Avvio = upsRec.Avvio;
dbRec.Chiusura = upsRec.Chiusura;
dbRec.CodExt = upsRec.CodExt;
dbRec.DescrProj = upsRec.DescrProj;
dbRec.Gruppo = upsRec.Gruppo;
dbRec.IdxCliente = upsRec.IdxCliente;
dbRec.NomeProj = upsRec.NomeProj;
dbRec.OldIdx = upsRec.OldIdx;
dbRec.Starred = upsRec.Starred;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagProjUpsert:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Elenco Tags
/// </summary>
/// <returns></returns>
public List<AnagTagModel> AnagTagAll()
{
// init dati necessari
List<AnagTagModel> dbResult = new List<AnagTagModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagTag
.OrderBy(x => x.Descrizione)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagTagAll:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Elenco Tags
/// </summary>
/// <returns></returns>
public List<TagFasiDTO> AnagTagFasiAll()
{
// init dati necessari
List<TagFasiDTO> dbResult = new List<TagFasiDTO>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetAnagTagFasiDTO
.OrderBy(x => x.Descrizione)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in AnagTagFasiAllAsync:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public bool AnagTagFasiDelete(TagFasiDTO upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagTagFasi
.Where(x => x.CodTagFase == upsRec.CodTagFase)
.FirstOrDefault();
if (dbRec != null)
{
dbCtx
.DbSetAnagTagFasi
.Remove(dbRec);
dbCtx.SaveChanges();
answ = true;
}
}
catch (Exception exc)
{
Log.Error($"Errore in AnagTagFasiDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
public bool AnagTagFasiUpsert(TagFasiDTO upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetAnagTagFasi
.Where(x => x.CodTagFase == upsRec.CodTagFase)
.FirstOrDefault();
if (dbRec == null)
{
// creo nuovo rec...
dbRec = new AnagTagFasiModel()
{
CodTagFase = upsRec.CodTagFase,
CodGruppo = upsRec.CodGruppo,
Descrizione = upsRec.Descrizione,
Enabled = upsRec.Enabled
};
dbCtx
.DbSetAnagTagFasi
.Add(dbRec);
}
else
{
dbRec.Descrizione = upsRec.Descrizione;
dbRec.CodGruppo = upsRec.CodGruppo;
dbRec.Enabled = upsRec.Enabled;
//dbRec.CodTagFase = upsRec.CodTagFase;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in AnagTagFasiUpsert:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Vista dati per FASE con totalizzaizone ore budget/real
/// </summary>
/// <param name="idxFase"></param>
/// <returns></returns>
public CalcOreFasiModel CalcOreFase(int idxFase)
{
CalcOreFasiModel dbResult = new CalcOreFasiModel();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var parIdxFase = new SqlParameter("@idxFaseAnc", idxFase);
var rawResult = dbCtx
.DbSetCalcOreFasi
.FromSqlRaw("EXEC stp_AF_getByIdxFase_Child @idxFaseAnc", parIdxFase)
.ToList();
if (rawResult != null && rawResult.Count > 0)
{
dbResult = rawResult[0];
}
}
return dbResult;
}
/// <summary>
/// Vista dati per progetto con totalizzaizone ore budget/real
/// </summary>
/// <param name="idxProj"></param>
/// <returns></returns>
public CalcOreProgettiModel CalcOreProj(int idxProj)
{
CalcOreProgettiModel dbResult = new CalcOreProgettiModel();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var idxProgetto = new SqlParameter("@idxProgetto", idxProj);
var rawResult = dbCtx
.DbSetCalcOreProj
.FromSqlRaw("EXEC stp_AP_getByIdxPrj @idxProgetto", idxProgetto)
.ToList();
if (rawResult != null && rawResult.Count > 0)
{
dbResult = rawResult[0];
}
}
return dbResult;
}
/// <summary>
/// Lista chiusure aziendali dato periodo
/// </summary>
/// <param name="dtStart"></param>
/// <param name="dtEnd"></param>
/// <returns></returns>
public List<CalFesteFerieModel> CalFestFeriePeriodo(DateTime dtStart, DateTime dtEnd)
{
List<CalFesteFerieModel> dbResult = new List<CalFesteFerieModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetCalFesteFerie
.Where(x => x.data >= dtStart && x.data <= dtEnd)
.OrderBy(x => x.data)
.ToList();
}
return dbResult;
}
public bool CalFestFerieUpsert(CalFesteFerieModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetCalFesteFerie
.Where(x => x.data == upsRec.data)
.FirstOrDefault();
if (dbRec == null)
{
dbCtx
.DbSetCalFesteFerie
.Add(upsRec);
}
else
{
dbRec.codGiust = upsRec.codGiust;
dbRec.descrizione = upsRec.descrizione;
//set modificato
dbCtx.Entry(dbRec).State = EntityState.Modified;
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Errore in CalFestFerieUpsert:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera l'elenco dei Rilievi temperatura nel periodo indicato x dipendente
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="dtInizio">Data di riferimento (ultima/corrente)</param>
/// <param name="dtFine">NUm settimane precedenti da recuperare</param>
/// <returns></returns>
public List<CheckVc19Model> CheckVC19List(int idxDipendente, DateTime dtInizio, DateTime dtFine)
{
// init dati necessari
List<CheckVc19Model> dbResult = new List<CheckVc19Model>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetCheckVc19
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtCheck && x.DtCheck <= dtFine)
.ToList();
}
return dbResult;
}
/// <summary>
/// Lista configurazione
/// </summary>
/// <param name="dtStart"></param>
/// <param name="dtEnd"></param>
/// <returns></returns>
public List<ConfigModel> ConfigGetAll()
{
List<ConfigModel> dbResult = new List<ConfigModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetConfig
.OrderBy(x => x.chiave)
.ToList();
}
return dbResult;
}
/// <summary>
/// Recupera l'elenco dei dettaglio giornaliero attività di tutti i dipendenti, dato giorno riferimento
/// </summary>
/// <param name="dtRif">Data di riferimento</param>
/// <returns></returns>
public List<DailyDataDTO> DailyDetailAllDip(DateTime dtRif)
{
//resetto valoredtRif a giornata (mezzanotte)
dtRif = dtRif.Date;
// init dati necessari
List<DailyDataDTO> dbResult = new List<DailyDataDTO>();
List<TimbratureExplModel> rawTimbExp = new List<TimbratureExplModel>();
List<TimbratureModel> rawTimbr = new List<TimbratureModel>();
List<RegAttivitaModel> rawRegAtt = new List<RegAttivitaModel>();
List<RilievoTempModel> rawRilTemp = new List<RilievoTempModel>();
List<CheckVc19Model> rawCheckC19 = new List<CheckVc19Model>();
// fermate/festività/ferie
List<CalFesteFerieModel> rawFermateAz = new List<CalFesteFerieModel>();
List<RegMalattieModel> rawMalattie = new List<RegMalattieModel>();
List<RegRichiesteModel> rawRichDip = new List<RegRichiesteModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
// recupero intero set dati timbrature e attività....
rawTimbExp = dbCtx
.DbSetTimbratureExpl
.Where(x => dtRif == x.DataLav)
.ToList();
rawTimbr = dbCtx
.DbSetTimbrature
.Where(x => dtRif == x.DataOra.Date)
.ToList();
rawRegAtt = dbCtx
.DbSetRegAttivita
.Where(x => dtRif == x.Inizio.Date)
.Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
.ToList();
rawRilTemp = dbCtx
.DbSetRilievoTemp
.Where(x => dtRif <= x.DtRilievo.Date)
.ToList();
rawCheckC19 = dbCtx
.DbSetCheckVc19
.Where(x => dtRif <= x.DtCheck.Date)
.ToList();
// recupero fermate e malattie/permessi del dipendente...
rawFermateAz = dbCtx
.DbSetCalFesteFerie
.Where(x => x.data == dtRif)
.ToList();
rawMalattie = dbCtx
.DbSetRegMalattie
.Where(x => x.DtInizio.Date <= dtRif && x.DtInizio.AddDays(x.NumGG) >= dtRif)
.ToList();
rawRichDip = dbCtx
.DbSetRegRichieste
.Where(x => x.DtStart.Date <= dtRif && x.DtEnd >= dtRif)
.ToList();
// calcolo elenco DIP
List<int> listDip = rawTimbExp.Select(x => x.IdxDipendente).Distinct().ToList();
// x ogni giorno scompongo il set di info...
foreach (var idxDip in listDip)
{
DailyDataDTO currDayDto = new DailyDataDTO()
{
IdxDipendente = idxDip,
DtRif = dtRif,
ListRA = rawRegAtt
.Where(x => x.IdxDipendente == idxDip)
.OrderBy(x => x.Inizio)
.ToList(),
ListTimbr = rawTimbr
.Where(x => x.IdxDipendente == idxDip)
.OrderBy(x => x.DataOra)
.ToList(),
TimbrExpl = rawTimbExp
.Where(x => x.IdxDipendente == idxDip)
.FirstOrDefault(),
ListRilTemp = rawRilTemp
.Where(x => x.IdxDipendente == idxDip)
.ToList(),
ListCheckC19 = rawCheckC19
.Where(x => x.IdxDipendente == idxDip)
.ToList(),
ListFermateAzienda = rawFermateAz,
ListMalattie = rawMalattie
.Where(x => x.IdxDipendente == idxDip)
.ToList(),
ListFerieDip = rawRichDip
.Where(x => x.IdxDipendente == idxDip)
.ToList(),
ListRichiesteDip = rawRichDip
.Where(x => x.IdxDipendente == idxDip)
.ToList()
};
dbResult.Add(currDayDto);
}
}
return dbResult;
}
/// <summary>
/// Recupera l'elenco dei dettagli giornalieri attività di un dipendente, dato periodo riferimento
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="dtInizio">Data di riferimento (ultima/corrente)</param>
/// <param name="dtFine">NUm settimane precedenti da recuperare</param>
/// <returns></returns>
public List<DailyDataDTO> DailyDetails(int idxDipendente, DateTime dtInizio, DateTime dtFine)
{
// init dati necessari
List<DailyDataDTO> dbResult = new List<DailyDataDTO>();
List<TimbratureExplModel> rawTimbExp = new List<TimbratureExplModel>();
List<TimbratureModel> rawTimbr = new List<TimbratureModel>();
List<RegAttivitaModel> rawRegAtt = new List<RegAttivitaModel>();
List<RilievoTempModel> rawRilTemp = new List<RilievoTempModel>();
List<CheckVc19Model> rawCheckC19 = new List<CheckVc19Model>();
// fermate/festività/ferie
List<CalFesteFerieModel> rawFermateAz = new List<CalFesteFerieModel>();
List<RegMalattieModel> rawMalattie = new List<RegMalattieModel>();
List<RegRichiesteModel> rawRichDip = new List<RegRichiesteModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
// recupero intero set dati timbrature e attività....
rawTimbExp = dbCtx
.DbSetTimbratureExpl
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataLav && x.DataLav <= dtFine)
.ToList();
rawTimbr = dbCtx
.DbSetTimbrature
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataOra && x.DataOra <= dtFine.AddDays(1))
.ToList();
rawRegAtt = dbCtx
.DbSetRegAttivita
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.Inizio && x.Inizio <= dtFine.AddDays(1))
.Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
.ToList();
rawRilTemp = dbCtx
.DbSetRilievoTemp
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtRilievo && x.DtRilievo <= dtFine.AddDays(1))
.ToList();
rawCheckC19 = dbCtx
.DbSetCheckVc19
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtCheck && x.DtCheck <= dtFine.AddDays(1))
.ToList();
// recupero fermate e malattie/permessi del dipendente...
rawFermateAz = dbCtx
.DbSetCalFesteFerie
.Where(x => x.data >= dtInizio && x.data <= dtFine)
.ToList();
rawMalattie = dbCtx
.DbSetRegMalattie
.Where(x => (x.DtInizio >= dtInizio && x.DtInizio <= dtFine) || (x.DtInizio.AddDays(x.NumGG) >= dtInizio.AddDays(x.NumGG) && x.DtInizio <= dtFine))
.ToList();
rawRichDip = dbCtx
.DbSetRegRichieste
.Where(x => (idxDipendente == 0 || x.IdxDipendente == idxDipendente) && ((x.DtStart >= dtInizio && x.DtStart <= dtFine) || (x.DtStart <= dtInizio && dtFine <= x.DtEnd) || (x.DtEnd >= dtInizio && x.DtEnd <= dtFine)))
.ToList();
// calcolo intervallo date...
int numDays = dtFine.Subtract(dtInizio).Days;
// x ogni giorno scompongo il set di info...
for (int i = 0; i <= numDays; i++)
{
DateTime thisDay = dtInizio.AddDays(i);
DailyDataDTO currDayDto = new DailyDataDTO()
{
IdxDipendente = idxDipendente,
DtRif = thisDay,
ListRA = rawRegAtt
.Where(x => thisDay <= x.Inizio && x.Inizio < thisDay.AddDays(1))
.OrderBy(x => x.Inizio)
.ToList(),
ListTimbr = rawTimbr
.Where(x => thisDay <= x.DataOra && x.DataOra < thisDay.AddDays(1))
.OrderBy(x => x.DataOra)
.ToList(),
TimbrExpl = rawTimbExp
.Where(x => thisDay == x.DataLav)
.FirstOrDefault(),
ListRilTemp = rawRilTemp
.Where(x => thisDay <= x.DtRilievo && x.DtRilievo < thisDay.AddDays(1))
.ToList(),
ListCheckC19 = rawCheckC19
.Where(x => thisDay <= x.DtCheck && x.DtCheck < thisDay.AddDays(1))
.ToList(),
ListFermateAzienda = rawFermateAz
.Where(x => thisDay == x.data)
.ToList(),
ListMalattie = rawMalattie
.Where(x => thisDay >= x.DtInizio && thisDay <= x.DtInizio.AddDays(x.NumGG))
.ToList(),
ListFerieDip = rawRichDip
.Where(x => x.CodGiust == "FER" && (thisDay >= x.DtStart && thisDay <= x.DtEnd))
.ToList(),
ListRichiesteDip = rawRichDip
.Where(x => x.CodGiust != "FER" && (thisDay.Date == x.DtStart.Date))
.ToList()
};
dbResult.Add(currDayDto);
}
}
return dbResult;
}
public bool DbForceMigrate()
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbCtx.DbForceMigrate();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in DbForceMigrate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera record anomalie dipendenti
/// </summary>
/// <param name="inizio"></param>
/// <param name="fine"></param>
/// <param name="notOkApp"></param>
/// <param name="notOkTim"></param>
/// <param name="notOkLav"></param>
/// <returns></returns>
public List<DipendendiAndAnomalie> DipAndAnomGetAll(DateTime inizio, DateTime fine, bool notOkApp, bool notOkTim, bool notOkLav)
{
List<DipendendiAndAnomalie> dbResult = new List<DipendendiAndAnomalie>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
SqlParameter parInizio = new SqlParameter("@inizio", inizio);
SqlParameter parFine = new SqlParameter("@pCodCert", fine);
SqlParameter parNotOkApp = new SqlParameter("@notOkApp", notOkApp);
SqlParameter parNotOkTim = new SqlParameter("@notOkTim", notOkTim);
SqlParameter parNotOkLav = new SqlParameter("@notOkLav", notOkLav);
var rawResult = dbCtx
.DbSetDipAndAnom
.FromSqlRaw("EXEC stp_DipendentiAndAnomalie @inizio,@pCodCert,@notOkApp,@notOkTim,@notOkLav", parInizio, parFine, parNotOkApp, parNotOkTim, parNotOkLav)
.ToList();
if (rawResult == null)
{
dbResult = new List<DipendendiAndAnomalie>();
}
else
{
dbResult = rawResult;
}
}
return dbResult;
}
/// <summary>
/// Clona assegnazione gruppi dipendente
/// </summary>
/// <param name="idxDipFrom"></param>
/// <param name="idxDipTo"></param>
/// <returns></returns>
public bool Dipendenti2GrpClona(int idxDipFrom, int idxDipTo)
{
int result = 0;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxFrom = new SqlParameter("@idxDipFrom", idxDipFrom);
var pIdxTo = new SqlParameter("@idxDipTo", idxDipTo);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_Dip2Gruppi_clone @idxDipFrom, @idxDipTo", pIdxFrom, pIdxTo);
}
catch (Exception exc)
{
Log.Error($"Errore in Dipendenti2GrpClona:{Environment.NewLine}{exc}");
}
}
return result != 0;
}
/// <summary>
/// Elimina dip 2 gruppo
/// </summary>
/// <param name="IdxDip"></param>
/// <param name="CodGruppo"></param>
/// <returns></returns>
public bool Dipendenti2GrpDelete(int idxDip, string codGruppo)
{
bool fatto = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var dbRec = dbCtx
.DbSetDipendenti2Gruppi
.Where(x => x.IdxDipendente == idxDip && x.Gruppo == codGruppo)
.FirstOrDefault();
if (dbRec != null)
{
dbCtx.DbSetDipendenti2Gruppi.Remove(dbRec);
dbCtx.SaveChanges();
}
}
return fatto;
}
/// <summary>
/// Aggiunge dip a gruppo
/// </summary>
/// <param name="IdxDip"></param>
/// <param name="CodGruppo"></param>
/// <returns></returns>
public bool Dipendenti2GrpInsert(int idxDip, string codGruppo)
{
bool fatto = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var dbRec = dbCtx
.DbSetDipendenti2Gruppi
.Where(x => x.IdxDipendente == idxDip && x.Gruppo == codGruppo)
.FirstOrDefault();
if (dbRec == null)
{
Dipendenti2GruppiModel newRec = new Dipendenti2GruppiModel()
{
IdxDipendente = idxDip,
Gruppo = codGruppo
};
dbCtx.DbSetDipendenti2Gruppi.Add(newRec);
dbCtx.SaveChanges();
}
}
return fatto;
}
public List<Dipendenti2RuoliModel> Dipendenti2RuoliGetAll()
{
List<Dipendenti2RuoliModel> dbResult = new List<Dipendenti2RuoliModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetDip2Ruoli
.Include(o => o.DipNav)
.ToList();
}
return dbResult;
}
/// <summary>
/// Elenco dip filtrati x gruppo
/// </summary>
/// <param name="Gruppo"></param>
/// <returns></returns>
public List<DipendentiModel> DipendentiByGrp(string Gruppo)
{
List<DipendentiModel> dbResult = new List<DipendentiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetDipendenti2Gruppi
.Where(x => x.Gruppo == Gruppo || string.IsNullOrEmpty(Gruppo))
.Select(x => x.DipNav)
.Distinct()
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in DipendentiByGrp:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public List<DipendentiModel> DipendentiGetAll()
{
List<DipendentiModel> dbResult = new List<DipendentiModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetDipendenti
.Include(o => o.OrarioNav)
.ToList();
}
return dbResult;
}
public bool DipendentiUpdate(DipendentiModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
if (currItem.IdxDipendente > 0)
{
var currRec = dbCtx
.DbSetDipendenti
.FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente);
if (currRec != null)
{
currRec.Cognome = currItem.Cognome;
currRec.Nome = currItem.Nome;
currRec.AuthKey = currItem.AuthKey;
currRec.Cf = currItem.Cf;
currRec.Email = currItem.Email;
currRec.DataNascita = currItem.DataNascita;
currRec.LuogoNascita = currItem.LuogoNascita;
dbCtx
.DbSetDipendenti
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetDipendenti
.Update(currItem);
}
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in DipendentiUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Ricalcola Tag Mensili dato dipendente, periodo, tag
/// </summary>
/// <param name="idxDipendente">IdxDipendente</param>
/// <param name="attivo">Stato attivo richiesto</param>
/// <returns></returns>
public bool DipUpdateActive(int idxDipendente, bool attivo)
{
// init dati necessari
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pAttivo = new SqlParameter("@attivo", attivo);
var pIdxDip = new SqlParameter("@Original_idxDipendente", idxDipendente);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC dbo.stp_AD_updateActive @attivo, @Original_idxDipendente", pAttivo, pIdxDip);
fatto = dbResult > 0;
}
catch (Exception exc)
{
Log.Error($"Errore in DipUpdateActive:{Environment.NewLine}{exc}");
}
}
return fatto;
}
public void Dispose()
{
Log.Info("Dispose di GPWController");
}
/// <summary>
/// Recupera Elenco Report x mostrare link
/// </summary>
/// <param name="Area">Se vuoto = tutti</param>
/// <returns></returns>
public List<ElencoReportModel> ElencoReportFilt(string Area)
{
List<ElencoReportModel> dbResult = new List<ElencoReportModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetElencoReport
.Where(x => string.IsNullOrEmpty(Area) || x.Area == Area)
.ToList();
}
return dbResult;
}
/// <summary>
/// Esegue recupero dati in formato ExportCommessa
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dtInizio"></param>
/// <param name="dtFine"></param>
/// <returns></returns>
public List<ExpCommessaModel> ExportCommessa(int idxDipendente, DateTime dtInizio, DateTime dtFine)
{
List<ExpCommessaModel> dbResult = new List<ExpCommessaModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
SqlParameter pIdxDip = new SqlParameter("@idxRA", idxDipendente);
SqlParameter pInizio = new SqlParameter("@dataFrom", dtInizio);
SqlParameter pFine = new SqlParameter("@dataTo", dtFine);
try
{
dbResult = dbCtx
.DbSetExpCommessa
.FromSqlRaw("EXEC stp_RA_pivot_getByIdxDipData @idxRA, @dataFrom, @dataTo", pIdxDip, pInizio, pFine)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Eccezione durante ExportCommessa{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Esegue recupero dati in formato ExportZucchetti
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dtInizio"></param>
/// <param name="dtFine"></param>
/// <param name="Timbratrice"></param>
/// <returns></returns>
public List<ExpZucchettiModel> ExportZucchetti(int idxDipendente, DateTime dtInizio, DateTime dtFine, string Timbratrice)
{
List<ExpZucchettiModel> dbResult = new List<ExpZucchettiModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
SqlParameter pInizio = new SqlParameter("@dataFrom", dtInizio);
SqlParameter pFine = new SqlParameter("@dataTo", dtFine);
SqlParameter pIdxDip = new SqlParameter("@Dipendente", idxDipendente);
SqlParameter pTimbra = new SqlParameter("@Timbratrice", Timbratrice);
try
{
dbResult = dbCtx
.DbSetExpZucchetti
.FromSqlRaw("EXEC export.stp_TimbratureZucchetti @dataFrom, @dataTo, @Dipendente, @Timbratrice", pInizio, pFine, pIdxDip, pTimbra)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Eccezione durante ExportZucchetti{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Imposta stato di una fase
/// </summary>
/// <param name="idxFase">ID</param>
/// <param name="isActive">
/// Stato attivo true/false (se è amster -_&gt; aggiorna sottofasi x trigger su DB
/// </param>
/// <returns></returns>
public async Task<bool> FasiSetActive(int idxFase, bool isActive)
{
// init dati necessari
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
var dbResult = dbCtx
.DbSetAnagFasi
.Where(x => x.IdxFase == idxFase)
.FirstOrDefault();
if (dbResult != null)
{
dbResult.Attivo = isActive;
await dbCtx.SaveChangesAsync();
fatto = true;
}
}
catch (Exception exc)
{
Log.Error($"Errore in FasiSetActive:{Environment.NewLine}{exc}");
}
}
await Task.Delay(1);
return fatto;
}
public List<CheckVc19Model> GetChecksVC19(int maxNum)
{
List<CheckVc19Model> dbResult = new List<CheckVc19Model>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
int numRec = dbCtx
.DbSetCheckVc19
.Count();
// se ho meno record che quelli richiesti --> riduco
maxNum = numRec > maxNum ? maxNum : numRec;
dbResult = dbCtx
.DbSetCheckVc19
.OrderByDescending(x => x.DtCheck)
.Take(maxNum)
.ToList();
}
return dbResult;
}
public List<CheckVc19Model> GetChecksVC19Filt(int idxDip, DateTime dtStart, DateTime dtEnd)
{
List<CheckVc19Model> dbResult = new List<CheckVc19Model>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetCheckVc19
.Where(x => (idxDip == 0 || x.IdxDipendente == idxDip) && (x.DtCheck >= dtStart && x.DtCheck <= dtEnd))
.OrderByDescending(x => x.DtCheck)
.ToList();
}
return dbResult;
}
/// <summary>
/// Eliminazione record giustificativo
/// </summary>
/// <param name="upsRec"></param>
/// <returns></returns>
public bool GiustificativiDelete(GiustificativiModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetGiustificativi
.Where(x => x.IdxDipendente == upsRec.IdxDipendente
&& x.DataLav == upsRec.DataLav
&& x.CodGiust == upsRec.CodGiust)
.FirstOrDefault();
if (dbRec != null)
{
dbCtx
.DbSetGiustificativi
.Remove(dbRec);
dbCtx.SaveChanges();
answ = true;
}
}
catch (Exception exc)
{
Log.Error($"Errore in GiustificativiDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Elenco Giustificativi filtrati x dip
/// </summary>
/// <param name="IdxDip"></param>
/// <param name="DtFrom"></param>
/// <param name="DtTo"></param>
/// <returns></returns>
public List<GiustificativiModel> GiustificativiFilt(int IdxDip, DateTime DtFrom, DateTime DtTo)
{
// init dati necessari
List<GiustificativiModel> dbResult = new List<GiustificativiModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero intero set...
dbResult = dbCtx
.DbSetGiustificativi
.Where(x => x.IdxDipendente == IdxDip && (DtFrom <= x.DataLav && x.DataLav <= DtTo))
.Include(p => p.DipNav)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in GiustificativiFilt:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Chiama stored inserimento giust a copertura compelta x la data + utente indicati
/// </summary>
/// <param name="idxDip"></param>
/// <param name="DtRif"></param>
/// <param name="CodGiust"></param>
/// <returns></returns>
public bool GiustificativiInsByDate(int idxDip, DateTime DtRif, string CodGiust)
{
int result = 0;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var idxDipendente = new SqlParameter("@IdxDip", idxDip);
var pDateRif = new SqlParameter("@dataRif", DtRif);
var pCodGiust = new SqlParameter("@codGiust", CodGiust);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_giust_insByDate @IdxDip, @dataRif, @codGiust", idxDipendente, pDateRif, pCodGiust);
}
return result != 0;
}
/// <summary>
/// Update record giustificativo
/// </summary>
/// <param name="oldRec">Vecchio record (x poter cambiare chaive elimina)</param>
/// <param name="upsRec">Nuovo record</param>
/// <returns></returns>
public bool GiustificativiUpdate(GiustificativiModel oldRec, GiustificativiModel upsRec)
{
// init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetGiustificativi
.Where(x => x.IdxDipendente == oldRec.IdxDipendente
&& x.DataLav == oldRec.DataLav
&& x.CodGiust == oldRec.CodGiust
)
.FirstOrDefault();
if (dbRec != null)
{
// elimino vecchio
dbCtx
.DbSetGiustificativi
.Remove(dbRec);
dbCtx.SaveChanges();
// tolgo proprietà di navigazione x evitare errore insert...
upsRec.DipNav = null;
// aggiungo nuovo
dbCtx
.DbSetGiustificativi
.Add(upsRec);
// salvo
dbCtx.SaveChanges();
answ = true;
}
}
catch (Exception exc)
{
Log.Error($"Errore in GiustificativiUpdate:{Environment.NewLine}{exc}");
}
}
return answ;
}
public bool InsertCheck(DCCDecode updItem, string clientIp)
{
bool done = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
int idxDip = 0;
DipendentiModel? currUser = new DipendentiModel()
{
IdxDipendente = 0
};
// per prima cosa cerca il dipendente x cognome/nome...
var userList = dbCtx
.DbSetDipendenti
.Where(x => x.Cognome.ToUpper() == updItem.nam.fn.ToUpper() && x.Nome.ToUpper() == updItem.nam.gn.ToUpper())
.ToList();
// se ne ho solo 1 ok...
if (userList.Count > 1)
{
currUser = userList
.Where(x => x.DataNascita != null && updItem.dob.Date == ((DateTime)x.DataNascita).Date)
.FirstOrDefault();
}
// altrimenti cerco anche con DOB...
else if (userList.Count == 1)
{
// se lo trova inserisce check...
currUser = userList[0];
}
if (currUser != null)
{
// fisso dipendente
idxDip = currUser.IdxDipendente;
}
string rawPayload = JsonConvert.SerializeObject(updItem);
CheckVc19Model newItem = new CheckVc19Model()
{
IdxDipendente = idxDip,
DtCheck = DateTime.Now,
Cognome = updItem.nam.fn.ToUpper(),
Nome = updItem.nam.gn.ToUpper(),
Payload = rawPayload,
Dob = updItem.dob.Date
};
// aggiungo!
dbCtx
.DbSetCheckVc19
.Add(newItem);
// ora salvo!
dbCtx.SaveChanges();
// se trovato procedo ANCHE x timbratura
if (idxDip > 0)
{
// verifico se ho timbratura ingresso
var listTimbra = dbCtx
.DbSetTimbrature
.Where(x => x.IdxDipendente == idxDip && x.DataOra.Date == DateTime.Today && x.Entrata == true)
.ToList();
// se non ci fosse aggiungo
if (listTimbra == null || listTimbra.Count == 0)
{
var newIngresso = new TimbratureModel()
{
IdxDipendente = idxDip,
DataOra = DateTime.Now,
CodTipoTimb = "Web",
Ipv4 = clientIp,
Entrata = true,
Approv = true
};
// aggiungo!
dbCtx
.DbSetTimbrature
.Add(newIngresso);
}
// ora salvo!
dbCtx.SaveChanges();
done = true;
}
// altrimenti NON FA NULLA
else
{
Log.Info($"Attenzione, dipendente non trovato: {updItem.nam.fn} {updItem.nam.gn}");
}
}
catch (Exception exc)
{
Log.Error($"Eccezione in InsertCheck:{Environment.NewLine}{exc}");
}
}
return done;
}
public bool InsertManual(int idxDip, string clientIp)
{
bool done = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// se trovato procedo
if (idxDip > 0)
{
string rawPayload = "MANUAL";
CheckVc19Model newItem = new CheckVc19Model()
{
IdxDipendente = idxDip,
DtCheck = DateTime.Now,
Payload = rawPayload
};
// aggiungo!
dbCtx
.DbSetCheckVc19
.Add(newItem);
// ora salvo!
dbCtx.SaveChanges();
done = true;
}
// altrimenti NON FA NULLA
else
{
Log.Info($"Attenzione, dipendente non trovato: {idxDip}");
}
}
catch (Exception exc)
{
Log.Error($"Eccezione in InsertManual:{Environment.NewLine}{exc}");
}
}
return done;
}
/// <summary>
/// Recupero dati dettaglio tag da stored dedicata
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dataFrom"></param>
/// <param name="dataTo"></param>
/// <returns></returns>
public List<ListTagDDModel> ListTagDD(int idxDipendente, DateTime dataFrom, DateTime dataTo)
{
// init dati necessari
List<ListTagDDModel> dbResult = new List<ListTagDDModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pidxDipendente = new SqlParameter("@idxRA", idxDipendente);
var pdataFrom = new SqlParameter("@dataFrom", dataFrom);
var pdataTo = new SqlParameter("@dataTo", dataTo);
dbResult = dbCtx
.DbSetListTagDD
.FromSqlRaw("EXEC dbo.stp_LTDD_ByUserDate @idxRA, @dataFrom, @dataTo", pidxDipendente, pdataFrom, pdataTo)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in ListTagDD:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Toggle attiazione record da stored dedicata
/// </summary>
/// <param name="idxTagDD"></param>
/// <returns></returns>
public bool ListTagDDToggle(int idxTagDD)
{
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxTagDD = new SqlParameter("@IdxTagDD", idxTagDD);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC dbo.stp_LTDD_toggleActive @IdxTagDD", pIdxTagDD);
answ = dbResult > 0;
}
catch (Exception exc)
{
Log.Error($"Errore in ListTagDDToggle:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupero dati month tag da stored dedicata
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dataFrom"></param>
/// <param name="dataTo"></param>
/// <returns></returns>
public List<MonthTagModel> MonthTagList(int idxDipendente, DateTime dataFrom, DateTime dataTo)
{
// init dati necessari
List<MonthTagModel> dbResult = new List<MonthTagModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pidxDipendente = new SqlParameter("@idxDipendente", idxDipendente);
var pdataFrom = new SqlParameter("@dataFrom", dataFrom);
var pdataTo = new SqlParameter("@dataTo", dataTo);
dbResult = dbCtx
.DbSetMonthTag
.FromSqlRaw("EXEC dbo.stp_LTM_ByUserDate @idxDipendente, @dataFrom, @dataTo", pidxDipendente, pdataFrom, pdataTo)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in MonthTagList:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Ricalcola Tag Mensili dato dipendente, periodo, tag
/// </summary>
/// <param name="idxDipendente">IdxDipendente</param>
/// <param name="dataFrom">Inizio periodo ricalcolo</param>
/// <param name="dataTo">Fine periodo ricalcolo</param>
/// <param name="codTag">Cod TAG da ricalcolare</param>
/// <param name="minParam">Valore minimo parametri</param>
/// <returns></returns>
public bool MonthTagRecalc(int idxDipendente, DateTime dataFrom, DateTime dataTo, string codTag, int minParam)
{
// init dati necessari
bool fatto = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pCodTag = new SqlParameter("@CodTag", codTag);
var pIdxDipendente = new SqlParameter("@idxDipendente", idxDipendente);
var pDataFrom = new SqlParameter("@DtStart", dataFrom);
var pDataTo = new SqlParameter("@DtEnd", dataTo);
var pMinParam = new SqlParameter("@minParam", minParam);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC dbo.stp_LTDD_checkCreateByDipDate @CodTag, @idxDipendente, @DtStart, @DtEnd, @minParam", pCodTag, pIdxDipendente, pDataFrom, pDataTo, pMinParam);
fatto = dbResult > 0;
}
catch (Exception exc)
{
Log.Error($"Errore in MonthTagRecalc:{Environment.NewLine}{exc}");
}
}
return fatto;
}
/// <summary>
/// Elenco pareto progetti ordinati da filtro
/// </summary>
/// <param name="idxDip"></param>
/// <param name="dataStart"></param>
/// <param name="dataEnd"></param>
/// <param name="maxResult"></param>
/// <returns></returns>
public List<ParetoRegAttModel> ParetoRegAtt(int idxDip, DateTime dataStart, DateTime dataEnd, int maxResult)
{
List<ParetoRegAttModel> dbResult = new List<ParetoRegAttModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var idxDipendente = new SqlParameter("@IdxDip", idxDip);
var inizio = new SqlParameter("@inizio", dataStart);
var fine = new SqlParameter("@pCodCert", dataEnd);
var maxRes = new SqlParameter("@maxRes", maxResult);
dbResult = dbCtx
.DbSetParetoRegAtt
.FromSqlRaw("EXEC stp_freqProjByDipPeriodo @IdxDip, @inizio,@pCodCert,@maxRes", idxDipendente, inizio, fine, maxRes)
.ToList();
}
return dbResult;
}
/// <summary>
/// Righe presenze giornaliere
/// </summary>
/// <param name="dtRif">DataOra riferimento per richiesta presenze</param>
/// <returns></returns>
public List<StatsDayPresModel> PresenzeDay(DateTime dtRif)
{
List<StatsDayPresModel> dbResult = new List<StatsDayPresModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
var idxProgetto = new SqlParameter("@dataRif", dtRif);
dbResult = dbCtx
.DbSetStatsDayPres
.FromSqlRaw("EXEC stp_statsDayPres @dataRif", idxProgetto)
.ToList();
}
return dbResult;
}
/// <summary>
/// Recupero dati delle attività filtrate x spostamento ore tra progetti
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dataFrom"></param>
/// <param name="dataTo"></param>
/// <param name="idxCliente"></param>
/// <param name="idxProgetto"></param>
/// <param name="idxFase"></param>
/// <param name="isAncest"></param>
/// <returns></returns>
public List<RegAttivitaDayExplModel> RegAttDayExplList(int idxDipendente, DateTime dataFrom, DateTime dataTo, int idxCliente, int idxProgetto, int idxFase, bool isAncest)
{
// init dati necessari
List<RegAttivitaDayExplModel> dbResult = new List<RegAttivitaDayExplModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxDipendente = new SqlParameter("@idxRA", idxDipendente);
var pInizio = new SqlParameter("@dataFrom", dataFrom);
var pFine = new SqlParameter("@dataTo", dataTo);
var pIdxCliente = new SqlParameter("@idxCliente", idxCliente);
var pIdxProgetto = new SqlParameter("@idxProgetto", idxProgetto);
var pIdxFase = new SqlParameter("@idxFase", idxFase);
var pIsAncest = new SqlParameter("@soloAncest", isAncest);
dbResult = dbCtx
.DbSetDayRaExpl
.FromSqlRaw("EXEC dbo.stp_RAD_Expl_getByIdxDipPeriodo @idxRA, @dataFrom, @dataTo, @idxCliente, @idxProgetto, @idxFase, @soloAncest", pIdxDipendente, pInizio, pFine, pIdxCliente, pIdxProgetto, pIdxFase, pIsAncest)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in RegAttDayExplList:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public bool RegAttDelete(RegAttivitaModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbCtx.Remove(currItem);
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegAttUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera l'elenco delle attività di un dipendente in un giorno
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="DtRif">Data eventi da recuperare</param>
/// <returns></returns>
public List<RegAttivitaModel> RegAttDipDate(int idxDipendente, DateTime DtRif)
{
List<RegAttivitaModel> listRec = new List<RegAttivitaModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
listRec = dbCtx
.DbSetRegAttivita
.Where(x => x.IdxDipendente == idxDipendente && x.Inizio.Date == DtRif.Date)
.Include(a => a.FasiNav)
.ThenInclude(p => p.ProgettoNav)
.ThenInclude(c => c.ClienteNav)
.OrderByDescending(x => x.IdxRa)
.AsNoTracking()
.ToList();
}
return listRec;
}
/// <summary>
/// Recupera ultima attività dipendente
/// </summary>
/// <param name="IdxDipendente"></param>
/// <param name="onlyActive">cerca ultima solo tra attive (=true) o tutte (=false)</param>
/// <returns></returns>
public RegAttivitaModel RegAttLastByDip(int IdxDipendente, bool onlyActive)
{
RegAttivitaModel? dbResult = new RegAttivitaModel();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbResult = dbCtx
.DbSetRegAttivita
.Where(x => x.FasiNav.Attivo || !onlyActive)
.OrderByDescending(x => x.Inizio)
.FirstOrDefault(x => x.IdxDipendente == IdxDipendente);
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegAttLastByDip{Environment.NewLine}{exc}");
}
if (dbResult == null)
{
dbResult = new RegAttivitaModel();
}
}
if (dbResult == null)
{
dbResult = new RegAttivitaModel();
}
return dbResult;
}
/// <summary>
/// Esegue stored x ricalcolo RegAttExpl x il periodo indicato l'utente indicato
/// </summary>
/// <param name="IdxDip">Se 0 significa tutti</param>
/// <param name="DtStart"></param>
/// <param name="DtEnd"></param>
/// <returns></returns>
public bool RegAttRicalcola(int IdxDip, DateTime DtStart, DateTime DtEnd)
{
int result = 0;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxDip = new SqlParameter("@idxRA", IdxDip);
var pDtStart = new SqlParameter("@inizio", DtStart);
var pDtEnd = new SqlParameter("@fine", DtEnd);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxRA, @inizio, @fine", pIdxDip, pDtStart, pDtEnd);
}
catch (Exception exc)
{
Log.Error($"Errore in RegAttRicalcola:{Environment.NewLine}{exc}");
}
}
return result != 0;
}
/// <summary>
/// Insert/Update attività
/// </summary>
/// <param name="currItem"></param>
/// <returns></returns>
public bool RegAttUpdate(RegAttivitaModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
if (currItem.IdxRa == 0)
{
dbCtx.Entry(currItem).State = EntityState.Added;
}
else
{
var currRec = dbCtx
.DbSetRegAttivita
.FirstOrDefault(x => x.IdxRa == currItem.IdxRa);
if (currRec != null)
{
currRec.IdxDipendente = currItem.IdxDipendente;
currRec.IdxFase = currItem.IdxFase;
currRec.Inizio = currItem.Inizio;
currRec.Fine = currItem.Fine;
currRec.Descrizione = currItem.Descrizione;
dbCtx.Entry(currRec).State = EntityState.Modified;
dbCtx
.DbSetRegAttivita
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetRegAttivita
.Update(currItem);
}
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegAttUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Effettua spostamento di un record RA alla nuova fase indicata
/// </summary>
/// <param name="idxRA"></param>
/// <param name="idxFase"></param>
/// <returns></returns>
public bool RegAttUpdateFase(int idxRA, int idxFase)
{
// init dati necessari
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxRA = new SqlParameter("@Original_idxRA", idxRA);
var pIdxFase = new SqlParameter("@idxFase", idxFase);
var dbResult = dbCtx
.Database
.ExecuteSqlRaw("EXEC dbo.stp_RA_updateFase @idxFase, @Original_idxRA", pIdxFase, pIdxRA);
answ = dbResult > 0;
}
catch (Exception exc)
{
Log.Error($"Errore in RegAttUpdateFase:{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera eventi dato criteri filtro
/// </summary>
/// <param name="inizio"></param>
/// <param name="fine"></param>
/// <param name="evento"></param>
/// <returns></returns>
public List<RegistroEventiModel> RegEventiGetFilt(DateTime inizio, DateTime fine, string evento)
{
List<RegistroEventiModel> dbResult = new List<RegistroEventiModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetRegEventi
.Where(x => x.DataOra >= inizio && x.DataOra <= fine && (string.IsNullOrEmpty(evento) || x.Evento == evento))
.ToList();
}
return dbResult;
}
/// <summary>
/// Insert/Update di un record in Registro Eventi
/// </summary>
/// <param name="currItem"></param>
/// <returns></returns>
public bool RegEventiUpdate(RegistroEventiModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetRegEventi
.FirstOrDefault(x => x.DataOra == currItem.DataOra && x.Evento == currItem.Evento);
if (currRec != null)
{
currRec.Evento = currItem.Evento;
currRec.Commento = currItem.Commento;
dbCtx
.DbSetRegEventi
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetRegEventi
.Add(currItem);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegEventiUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Aggiorna record registro malattie approvando o rifiutando la richiesta e gestendo i
/// giustificativi associati Da impiegare con pagiina ADM
/// </summary>
/// <param name="currItem"></param>
public bool RegMalattieAppRif(RegMalattieModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// versione stored-based che sistema tutti i record dipendenti
SqlParameter pIdxRich = new SqlParameter("@Original_IdxRegMal", currItem.IdxRegMal);
SqlParameter pCodCert = new SqlParameter("@CodCert", currItem.CodCert);
SqlParameter pNumGG = new SqlParameter("@NumGG", currItem.NumGG);
SqlParameter pConf = new SqlParameter("@Conf", currItem.Conf);
int result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_RM_updateQuery @Original_IdxRegMal, @CodCert, @NumGG, @Conf", pIdxRich, pCodCert, pNumGG, pConf);
answ = result != 0;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegMalattieAppRif{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Elimina record registro malattie (SE non confermato)
/// </summary>
/// <param name="currItem"></param>
public bool RegMalattieDelete(RegMalattieModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetRegMalattie
.FirstOrDefault(x => x.IdxRegMal == currItem.IdxRegMal && !x.Conf);
if (currRec != null)
{
dbCtx
.DbSetRegMalattie
.Remove(currRec);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegMalattieDelete{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera elenco reg malattie (tutte, dato max dalla + recente)
/// </summary>
/// <param name="maxRecord"></param>
/// <returns></returns>
public List<RegMalattieModel> RegMalattieGetAll(int maxRecord)
{
List<RegMalattieModel> dbResult = new List<RegMalattieModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetRegMalattie
.Include(d => d.DipNav)
.OrderByDescending(x => x.DtInizio)
.Take(maxRecord)
.ToList();
}
return dbResult;
}
/// <summary>
/// recupera elenco reg malattie del dipendente
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="maxRecord"></param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public List<RegMalattieModel> RegMalattieGetByDip(int idxDipendente, int maxRecord)
{
List<RegMalattieModel> dbResult = new List<RegMalattieModel>();
if (idxDipendente > 0)
{
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetRegMalattie
.Where(x => x.IdxDipendente == idxDipendente)
.Include(d => d.DipNav)
.OrderByDescending(x => x.DtInizio)
.Take(maxRecord)
.ToList();
}
}
return dbResult;
}
/// <summary>
/// Recupera elenco reg malattie dato periodo
/// </summary>
/// <param name="DtFrom"></param>
/// <param name="DtTo"></param>
/// <returns></returns>
public List<RegMalattieModel> RegMalattieGetByPeriod(DateTime DtFrom, DateTime DtTo)
{
List<RegMalattieModel> dbResult = new List<RegMalattieModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetRegMalattie
.Where(x => (x.DtInizio.AddDays(x.NumGG) >= DtFrom && x.DtInizio <= DtTo))
.Include(d => d.DipNav)
.OrderByDescending(x => x.DtInizio)
.ToList();
}
return dbResult;
}
/// <summary>
/// Inserisce/Aggiorna record registro malattie
/// </summary>
/// <param name="currItem"></param>
public bool RegMalattieUpsert(RegMalattieModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// prima versione EFCore senza stored che NON fa partire tutte le operazioni
#if false
// versione stored-based che sistema tutti i record dipendenti
SqlParameter pIdxDip = new SqlParameter("@IdxDip", IdxDip);
SqlParameter pDataFrom = new SqlParameter("@DtFrom", DtFrom);
SqlParameter pDataTo = new SqlParameter("@DtTo", DtTo);
SqlParameter pInatt = new SqlParameter("@pInatt", ShowIn);
SqlParameter pShowWE = new SqlParameter("@ShowWE", ShowWE);
SqlParameter pMaxErrMin = new SqlParameter("@MaxErrMin", MaxErrMin);
SqlParameter pMaxErrPlus = new SqlParameter("@MaxErrPlus", MaxErrPlus);
dbResult = dbCtx
.DbSetTeRaExpl
.FromSqlRaw("EXEC stp_TE_RA_ByUserDate @IdxDip,@DtFrom,@DtTo,@pInatt,@ShowWE,@MaxErrMin,@MaxErrPlus", pIdxDip, pDataFrom, pDataTo, pInatt, pShowWE, pMaxErrMin, pMaxErrPlus)
.ToList();
#endif
var currRec = dbCtx
.DbSetRegMalattie
.FirstOrDefault(x => x.IdxRegMal == currItem.IdxRegMal);
if (currRec != null)
{
// aggiorno i campi
currRec.IdxDipendente = currItem.IdxDipendente;
currRec.DtInizio = currItem.DtInizio;
currRec.NumGG = currItem.NumGG;
currRec.CodCert = currItem.CodCert ?? "ND";
currRec.Conf = currItem.Conf;
dbCtx
.DbSetRegMalattie
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetRegMalattie
.Add(currItem);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegMalattieUpsert{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Aggiorna record registro richieste approvando o rifiutando la richiesta e gestendo i
/// giustificativi associati Da impiegare con pagiina ADM
/// </summary>
/// <param name="currItem"></param>
public bool RegRichiesteAppRif(RegRichiesteModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// versione stored-based che sistema tutti i record dipendenti
SqlParameter pIdxRich = new SqlParameter("@Original_IdxRegRich", currItem.IdxRegRich);
SqlParameter pConf = new SqlParameter("@Conf", currItem.Conf);
SqlParameter pCodGiust = new SqlParameter("@CodGiust", currItem.CodGiust);
SqlParameter pNote = new SqlParameter("@Note", currItem.Note);
int result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_RR_updateQuery @Original_IdxRegRich, @Conf, @CodGiust, @Note", pIdxRich, pConf, pCodGiust, pNote);
answ = result != 0;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegRichiesteAppRif{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Elimina record registro richieste (SE non confermato)
/// </summary>
/// <param name="currItem"></param>
public bool RegRichiesteDelete(RegRichiesteModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetRegRichieste
.FirstOrDefault(x => x.IdxRegRich == currItem.IdxRegRich && !x.Conf);
if (currRec != null)
{
dbCtx
.DbSetRegRichieste
.Remove(currRec);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegRichiesteDelete{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// recupera elenco reg richieste del dipendente
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dtFrom">Inizio periodo richiesto</param>
/// <param name="dtTo">Fine periodo richiesto</param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public List<RegRichiesteModel> RegRichiesteGetByDip(int idxDipendente, DateTime dtFrom, DateTime dtTo)
{
List<RegRichiesteModel> dbResult = new List<RegRichiesteModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
DateTime oggi = DateTime.Today;
dbResult = dbCtx
.DbSetRegRichieste
.Where(x => (idxDipendente == 0 || x.IdxDipendente == idxDipendente) && (x.DtStart >= dtFrom && x.DtStart <= dtTo))
.Include(d => d.DipNav)
.OrderByDescending(x => x.DtStart)
.ToList();
}
return dbResult;
}
/// <summary>
/// Inserisce/Aggiorna record registro richieste
/// </summary>
/// <param name="currItem"></param>
public bool RegRichiesteUpsert(RegRichiesteModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetRegRichieste
.FirstOrDefault(x => x.IdxRegRich == currItem.IdxRegRich);
if (currRec != null)
{
// aggiorno i campi
currRec.IdxDipendente = currItem.IdxDipendente;
currRec.DtStart = currItem.DtStart;
currRec.DtEnd = currItem.DtEnd;
currRec.CodGiust = currItem.CodGiust;
currRec.Note = currItem.Note ?? "";
currRec.Conf = currItem.Conf;
dbCtx
.DbSetRegRichieste
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetRegRichieste
.Add(currItem);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RegRichiesteUpsert{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera l'elenco dei dettagli attività e timbrature di un dipendente, dato periodo riferimento
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="periodo">Periodo di riferimento</param>
/// <returns></returns>
public ReportDataDTO ReportDetails(int idxDipendente, DtUtils.Periodo periodo)
{
// init dati necessari
DateTime dtInizio = periodo.Inizio;
DateTime dtFine = periodo.Fine;
ReportDataDTO dbResult = new ReportDataDTO();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
// recupero intero set dati timbrature e attività....
dbResult.TimbrExpl = dbCtx
.DbSetTimbratureExpl
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataLav && x.DataLav <= dtFine)
.ToList();
dbResult.ListTimbr = dbCtx
.DbSetTimbrature
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataOra && x.DataOra <= dtFine.AddDays(1))
.ToList();
dbResult.ListRA = dbCtx
.DbSetRegAttivita
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.Inizio && x.Inizio <= dtFine.AddDays(1))
.Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
.ToList();
// effettuo calcoli!
dbResult.DoCalc();
}
return dbResult;
}
/// <summary>
/// Recupera l'elenco dei Rilievi temperatura nel periodo indicato x dipendente
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="dtInizio">Data di riferimento (ultima/corrente)</param>
/// <param name="dtFine">NUm settimane precedenti da recuperare</param>
/// <returns></returns>
public List<RilievoTempModel> RilTempList(int idxDipendente, DateTime dtInizio, DateTime dtFine)
{
// init dati necessari
List<RilievoTempModel> dbResult = new List<RilievoTempModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
dbResult = dbCtx
.DbSetRilievoTemp
.Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtRilievo && x.DtRilievo <= dtFine)
.ToList();
}
return dbResult;
}
public bool RilTempUpdate(RilievoTempModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetRilievoTemp
.FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente && x.DtRilievo == currItem.DtRilievo);
if (currRec != null)
{
// aggiorno solo entrata/uscita
currRec.TempRil = currItem.TempRil;
dbCtx
.DbSetRilievoTemp
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetRilievoTemp
.Add(currItem);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in RilTempUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Annulla modifiche su una specifica entity (cancel update)
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public bool rollBackEntity(object item)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
if (dbCtx.Entry(item).State == EntityState.Deleted || dbCtx.Entry(item).State == EntityState.Modified)
{
dbCtx.Entry(item).Reload();
}
}
catch (Exception exc)
{
Log.Error($"Eccezione in rollBackEntity{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera record timbrature explose + consuntivo attivita dipendenti
/// </summary>
/// <param name="IdxDip">singolo idx oppure 0 = tutti</param>
/// <param name="DtFrom">Inizio periodo</param>
/// <param name="DtTo">Fine periodo</param>
/// <param name="ShowIn">Mostra inattivi</param>
/// <param name="ShowWE">Mostra weekend</param>
/// <param name="MaxErrMin">max consentito in mancanza</param>
/// <param name="MaxErrPlus">max consentito in eccesso</param>
/// <returns></returns>
public List<TeRaExplModel> TeRaExplGetFilt(int IdxDip, DateTime DtFrom, DateTime DtTo, bool ShowIn, bool ShowWE, int MaxErrMin, int MaxErrPlus)
{
List<TeRaExplModel> dbResult = new List<TeRaExplModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
SqlParameter pIdxDip = new SqlParameter("@idxRA", IdxDip);
SqlParameter pDataFrom = new SqlParameter("@dataFrom", DtFrom);
SqlParameter pDataTo = new SqlParameter("@dataTo", DtTo);
SqlParameter pInatt = new SqlParameter("@showInattivi", ShowIn);
SqlParameter pShowWE = new SqlParameter("@showWE", ShowWE);
SqlParameter pMaxErrMin = new SqlParameter("@maxErrMin", MaxErrMin);
SqlParameter pMaxErrPlus = new SqlParameter("@maxErrPlus", MaxErrPlus);
dbResult = dbCtx
.DbSetTeRaExpl
.FromSqlRaw("EXEC stp_TE_RA_ByUserDate @idxRA, @dataFrom, @dataTo, @showInattivi, @showWE, @maxErrMin, @maxErrPlus", pIdxDip, pDataFrom, pDataTo, pInatt, pShowWE, pMaxErrMin, pMaxErrPlus)
.ToList();
}
return dbResult;
}
/// <summary>
/// Recupera record anomalie dipendenti
/// </summary>
/// <param name="idxDipendente">singolo idx oppure 0 = tutti</param>
/// <param name="inizio"></param>
/// <param name="fine"></param>
/// <param name="notOkApp"></param>
/// <param name="notOkTim"></param>
/// <param name="notOkLav"></param>
/// <returns></returns>
public List<TimbratureExplModel> TimbExplGetAnomalie(int idxDipendente, DateTime inizio, DateTime fine, bool notOkApp, bool notOkTim, bool notOkLav)
{
List<TimbratureExplModel> dbResult = new List<TimbratureExplModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
SqlParameter pIdxDip = new SqlParameter("@IdxDip", idxDipendente);
SqlParameter pInizio = new SqlParameter("@inizio", inizio);
SqlParameter pFine = new SqlParameter("@fine", fine);
SqlParameter pNotOkApp = new SqlParameter("@notOkApp", notOkApp);
SqlParameter pNotOkTim = new SqlParameter("@notOkTim", notOkTim);
SqlParameter pNotOkLav = new SqlParameter("@notOkLav", notOkLav);
dbResult = dbCtx
.DbSetTimbratureExpl
.FromSqlRaw("EXEC stp_timbratureExpl_getByAnomalia @IdxDip,@inizio,@fine,@notOkApp,@notOkTim,@notOkLav", pIdxDip, pInizio, pFine, pNotOkApp, pNotOkTim, pNotOkLav)
.ToList();
}
return dbResult;
}
/// <summary>
/// Recupera record anomalie dipendenti x orario continuato
/// </summary>
/// <param name="idxDipendente">singolo idx oppure 0 = tutti</param>
/// <param name="inizio"></param>
/// <param name="fine"></param>
/// <returns></returns>
public List<TimbratureExplModel> TimbExplGetContinuato(int idxDipendente, DateTime inizio, DateTime fine)
{
List<TimbratureExplModel> dbResult = new List<TimbratureExplModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
SqlParameter parIdxDip = new SqlParameter("@IdxDip", idxDipendente);
SqlParameter parInizio = new SqlParameter("@inizio", inizio);
SqlParameter parFine = new SqlParameter("@pCodCert", fine);
dbResult = dbCtx
.DbSetTimbratureExpl
.FromSqlRaw("EXEC stp_timbratureExpl_getContinuato @IdxDip,@inizio,@pCodCert", parIdxDip, parInizio, parFine)
.ToList();
}
return dbResult;
}
/// <summary>
/// Esegue stored x ricalcolo TimbExpl x il periodo indicato l'utente indicato
/// </summary>
/// <param name="IdxDip">Se 0 significa tutti</param>
/// <param name="DtStart"></param>
/// <param name="DtEnd"></param>
/// <returns></returns>
public bool TimbExplRicalcola(int IdxDip, DateTime DtStart, DateTime DtEnd)
{
int result = 0;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pIdxDip = new SqlParameter("@idxRA", IdxDip);
var pDtStart = new SqlParameter("@inizio", DtStart);
var pDtEnd = new SqlParameter("@fine", DtEnd);
result = dbCtx
.Database
.ExecuteSqlRaw("EXEC stp_ricalcolaTimbExpl_byPeriodoUser @idxRA, @inizio, @fine", pIdxDip, pDtStart, pDtEnd);
}
catch (Exception exc)
{
Log.Error($"Errore in TimbExplRicalcola:{Environment.NewLine}{exc}");
}
}
return result != 0;
}
/// <summary>
/// Recupero dati di sunto delle timbrature mensili x dipendente da stored dedicata
/// </summary>
/// <param name="idxDipendente"></param>
/// <param name="dataFrom"></param>
/// <param name="dataTo"></param>
/// <returns></returns>
public List<TimbMeseExplModel> TimbMeseExplList(int idxDipendente, DateTime dataFrom, DateTime dataTo)
{
// init dati necessari
List<TimbMeseExplModel> dbResult = new List<TimbMeseExplModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var pidxDipendente = new SqlParameter("@idxRA", idxDipendente);
var pInizio = new SqlParameter("@inizio", dataFrom);
var pFine = new SqlParameter("@fine", dataTo);
dbResult = dbCtx
.DbSetTimbMeseExpl
.FromSqlRaw("EXEC dbo.stp_TimbMeseExpl @inizio, @fine, @idxRA", pInizio, pFine, pidxDipendente)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Errore in TimbMeseExplList:{Environment.NewLine}{exc}");
}
}
return dbResult;
}
/// <summary>
/// Elenco timbrature successive alla data odierna x ogni dipendente (x recupero errori
/// timbrature future)
/// - per il check imposta la mezzanotte del giorno successivo
/// </summary>
/// <returns></returns>
public List<TimbratureModel> TimbratureAnomalieFuture()
{
// imposta data a domani
DateTime dateRif = DateTime.Today.AddDays(1);
List<TimbratureModel> dbResult = new List<TimbratureModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbResult = dbCtx
.DbSetTimbrature
.Where(x => x.DataOra.Date >= dateRif.Date)
.OrderBy(x => x.IdxDipendente)
.ThenByDescending(x => x.DataOra)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureAnomalieFuture{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public List<TimbratureModel> TimbratureDay(DateTime dateRif, int IdxDip)
{
List<TimbratureModel> dbResult = new List<TimbratureModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbResult = dbCtx
.DbSetTimbrature
.Where(x => x.DataOra.Date == dateRif.Date && x.IdxDipendente == IdxDip)
.OrderByDescending(x => x.DataOra)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureDay{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public async Task<TimbratureModel?> TimbratureByUidAsync(string UID)
{
TimbratureModel? dbRec = null;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// UID = $"{IdxDipendente:000}_{DataOra:yyyyMMdd}_{DataOra:HHmmss}"
var parts = UID.Split('_');
if (parts.Length == 3)
{
int idxDip = int.Parse(parts[0]);
DateTime dataOra = DateTime.ParseExact(parts[1] + "_" + parts[2], "yyyyMMdd_HHmmss", null);
dbRec = await dbCtx
.DbSetTimbrature
.FirstOrDefaultAsync(x => x.IdxDipendente == idxDip && x.DataOra == dataOra);
}
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureByUidAsync: {Environment.NewLine}{exc}");
}
}
return dbRec;
}
public bool TimbratureDelete(TimbratureModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbCtx.Remove(currItem);
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureDelete{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Elenco di tutte le timbrature richieste (= NON approvate)
/// </summary>
/// <returns></returns>
public List<TimbratureModel> TimbratureRichieste()
{
List<TimbratureModel> dbResult = new List<TimbratureModel>();
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
dbResult = dbCtx
.DbSetTimbrature
.Where(x => x.Approv == false)
.Include(d => d.DipNav)
.OrderByDescending(x => x.DataOra)
.ToList();
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureRichieste{Environment.NewLine}{exc}");
}
}
return dbResult;
}
public bool TimbratureUpdate(TimbratureModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetTimbrature
.FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente && x.DataOra == currItem.DataOra);
if (currRec != null)
{
// aggiorno solo entrata/uscita
currRec.Entrata = currItem.Entrata;
currRec.Approv = currItem.Approv;
currRec.Ipv4 = currItem.Ipv4;
currRec.DataOra = currItem.DataOra;
dbCtx
.DbSetTimbrature
.Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetTimbrature
.Add(currItem);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Registra update + arrotondamento orario
/// </summary>
/// <param name="currItem"></param>
/// <returns></returns>
public bool TimbratureUpdateRound(TimbratureModel currItem)
{
bool answ = false;
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
var currRec = dbCtx
.DbSetTimbrature
.FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente && x.DataOra == currItem.DataOra);
if (currRec != null)
{
// se non si tratta di una timbratura "last second"...
if (currItem.DataOra.Hour != 23 && currItem.DataOra.Minute != 59 && currItem.DataOra.Second != 59)
{
// arrotondo ingresso/uscita ai 5 minuti secondo sia entrata o uscita...
var dtTimb = Utils.DateRounded(currItem.DataOra, 5, !(currItem.Entrata ?? false));
currItem.DataOra = dtTimb;
}
//// aggiorno solo entrata/uscita
//currRec.Entrata = currItem.Entrata;
//currRec.Approv = currItem.Approv;
//currRec.Ipv4 = currItem.Ipv4;
//currRec.DataOra = currItem.DataOra;
// elimino e reinserisco!
dbCtx.Remove(currRec);
dbCtx.SaveChanges();
dbCtx.Add(currItem);
//dbCtx
// .DbSetTimbrature
// .Update(currRec);
}
// altrimenti aggiungo
else
{
dbCtx
.DbSetTimbrature
.Add(currItem);
}
dbCtx.SaveChanges();
answ = true;
}
catch (Exception exc)
{
Log.Error($"Eccezione in TimbratureUpdate{Environment.NewLine}{exc}");
}
}
return answ;
}
/// <summary>
/// Recupera l'elenco delle ultime attività inserite da un dipendente
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="numRec">Num record da recuperare</param>
/// <returns></returns>
public List<RegAttivitaModel> UserLastRec(int idxDipendente, int numRec)
{
List<RegAttivitaModel> listRec = new List<RegAttivitaModel>();
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
listRec = dbCtx
.DbSetRegAttivita
.Where(x => x.IdxDipendente == idxDipendente)
.Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
.OrderByDescending(x => x.IdxRa)
.Take(numRec)
.AsNoTracking()
.ToList();
}
return listRec;
}
/// <summary>
/// Recupera overview di un periodo settimanale x dipendente, data riferimento, numero
/// settimane precedenti
/// </summary>
/// <param name="idxDipendente">Dipendente interessato</param>
/// <param name="dtRif">Data di riferimento (ultima/corrente)</param>
/// <param name="numWeek">NUm settimane precedenti da recuperare</param>
/// <returns></returns>
public List<WeekStatDTO> WeekOverview(int idxDipendente, DateTime dtRif, int numWeek)
{
// init dati necessari
List<WeekStatDTO> dbResult = new List<WeekStatDTO>();
List<WeekData> weekList = new List<WeekData>();
// aggiungo sett corrente + quelle richieste...
for (int i = numWeek; i >= 0; i--)
{
var currWeek = new WeekData(dtRif.AddDays(-i * 7));
weekList.Add(currWeek);
}
// cerco su DB settimana x settimana....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
foreach (var item in weekList)
{
var totOreTim = dbCtx
.DbSetTimbratureExpl
.Where(x => x.IdxDipendente == idxDipendente && x.DataLav >= item.inizio && x.DataLav <= item.fine)
.Sum(x => (double)x.HLav);
var totMinLav = dbCtx
.DbSetRegAttivitaExpl
.Where(x => x.IdxDipendente == idxDipendente && x.DataLav >= item.inizio && x.DataLav <= item.fine)
.Sum(x => (int)x.MinRegAtt);
// aggiungo alla lista...
var weekData = new WeekStatDTO()
{
IdxDipendente = idxDipendente,
Anno = item.anno,
WeekNumber = item.weekNumber,
Inizio = item.inizio,
Fine = item.fine,
SumOreLav = totOreTim,
SumOreComm = (double)totMinLav / 60,
};
dbResult.Add(weekData);
}
}
return dbResult;
}
#endregion Public Methods
#region Internal Methods
internal bool CalFestFerieDelete(CalFesteFerieModel remRec)
{ // init
bool answ = false;
// cerco su DB recuperando set di dati....
using (GPWContext dbCtx = new GPWContext(_configuration))
{
try
{
// recupero cliente in primis...
var dbRec = dbCtx
.DbSetCalFesteFerie
.Where(x => x.data == remRec.data)
.FirstOrDefault();
if (dbRec != null)
{
dbCtx
.DbSetCalFesteFerie
.Remove(dbRec);
dbCtx.SaveChanges();
answ = true;
}
}
catch (Exception exc)
{
Log.Error($"Errore in CalFestFerieDelete:{Environment.NewLine}{exc}");
}
}
return answ;
}
#endregion Internal Methods
#region Private Fields
private static IConfiguration _configuration = null!;
private static NLog.Logger Log = LogManager.GetCurrentClassLogger();
#endregion Private Fields
}
}
Reference in New Issue View Git Blame Copy Permalink