Samuele Locatelli
1559 lines
65 KiB
C#
1559 lines
65 KiB
C#
using MagMan.Core;
|
|
using MagMan.Core.DTO;
|
|
using MagMan.Data.Tenant.DbModels;
|
|
using MagMan.Data.Tenant.Services;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using Microsoft.Extensions.Configuration;
|
|
using MySqlConnector;
|
|
using NLog;
|
|
using NLog.LayoutRenderers;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Runtime.ConstrainedExecution;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Xml;
|
|
using static MagMan.Core.Enums;
|
|
using static MagMan.Core.RestPayload;
|
|
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
|
|
|
|
namespace MagMan.Data.Tenant.Controllers
|
|
{
|
|
public class TenantController : IDisposable
|
|
{
|
|
#region Public Constructors
|
|
|
|
public TenantController()
|
|
{
|
|
}
|
|
|
|
#endregion Public Constructors
|
|
|
|
#region Public Methods
|
|
|
|
/// <summary>
|
|
/// Elimina Alias da magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2del">Alias da eliminare</param>
|
|
/// <returns></returns>
|
|
public bool AliasDelete(string connString, AliasModel rec2del)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
var currData = dbCtx
|
|
.DbSetAlias
|
|
.Where(x => x.Family == rec2del.Family && x.ValueOriginal == rec2del.ValueOriginal)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
// 2024.04.04: cancellazione logica al posto di delete!
|
|
//dbCtx
|
|
// .DbSetAlias
|
|
// .Remove(currData);
|
|
currData.IsActive = false;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in AliasDelete{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Alias gestiti a magazzino data famiglia
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="family">Famiglia richiesta, "" = tutti</param>
|
|
/// <returns></returns>
|
|
public List<AliasModel> AliasGetFilt(string connString, string family)
|
|
{
|
|
List<AliasModel> dbResult = new List<AliasModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetAlias
|
|
.Where(x => (string.IsNullOrEmpty(family) || x.Family == family))
|
|
.OrderBy(x => x.ValueOriginal)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Upsert record alias
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2upd">Record da aggiornare/inserire</param>
|
|
/// <returns></returns>
|
|
public bool AliasUpsert(string connString, AliasModel rec2upd)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Ricerca record
|
|
*/
|
|
var currData = dbCtx
|
|
.DbSetAlias
|
|
.Where(x => x.Family == rec2upd.Family && x.ValueOriginal.ToLower() == rec2upd.ValueOriginal.ToLower())
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
currData.ValueAlias = rec2upd.ValueAlias;
|
|
currData.IsActive = rec2upd.IsActive;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
else
|
|
{
|
|
dbCtx
|
|
.DbSetAlias
|
|
.Add(rec2upd);
|
|
}
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in AliasUpsert{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Upsert record alias
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="recList">Lista Record da aggiornare/inserire</param>
|
|
/// <returns></returns>
|
|
public bool AliasUpsert(string connString, List<AliasModel> recList)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
foreach (var rec2upd in recList)
|
|
{
|
|
var currData = dbCtx
|
|
.DbSetAlias
|
|
.Where(x => x.Family == rec2upd.Family && x.ValueOriginal.ToLower() == rec2upd.ValueOriginal.ToLower())
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
if (currData.ValueAlias != rec2upd.ValueAlias || currData.IsActive != rec2upd.IsActive)
|
|
{
|
|
currData.ValueAlias = rec2upd.ValueAlias;
|
|
currData.IsActive = rec2upd.IsActive;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
dbCtx
|
|
.DbSetAlias
|
|
.Add(rec2upd);
|
|
}
|
|
}
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in AliasUpsert{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
public async Task<bool> DatabaseMigrate(string connString)
|
|
{
|
|
bool answ = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
await dbCtx.Database.MigrateAsync();
|
|
answ = true;
|
|
}
|
|
return answ;
|
|
}
|
|
|
|
public void Dispose()
|
|
{
|
|
// Clear database context
|
|
Log.Info("Dispose di TenantController");
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elimina Item da magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2del">Item da eliminare</param>
|
|
/// <returns></returns>
|
|
public bool ItemDelete(string connString, RawItemModel rec2del)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
var currData = dbCtx
|
|
.DbSetItems
|
|
.Where(x => x.RawItemId == rec2del.RawItemId)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
//dbCtx
|
|
// .DbSetItems
|
|
// .Remove(currData);
|
|
|
|
// eliminazione logica...
|
|
currData.IsDeleted = true;
|
|
// registro modifica RawItem
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ItemDelete{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Converte il DTO in ItemModel
|
|
/// </summary>
|
|
/// <param name="origItem">DTO di partenza</param>
|
|
/// <param name="isActive">Parametro active da impostare</param>
|
|
/// <returns></returns>
|
|
public RawItemModel ItemFromDto(ItemDTO origItem)
|
|
{
|
|
RawItemModel answ = new RawItemModel()
|
|
{
|
|
MatId = origItem.MatCloudId,
|
|
IsDeleted = origItem.IsDeleted,
|
|
IsRemn = origItem.IsRemn,
|
|
Location = origItem.Location,
|
|
QtyAvail = origItem.QtyAvail,
|
|
HMm = origItem.HMm,
|
|
LMm = origItem.LMm,
|
|
WMm = origItem.WMm,
|
|
Note = origItem.Note
|
|
};
|
|
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Items gestiti a magazzino (all)
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="onlyActive">Solo attivi (default) o anche cancellati</param>
|
|
/// <returns></returns>
|
|
public List<RawItemModel> ItemGetAll(string connString, bool onlyActive = true)
|
|
{
|
|
List<RawItemModel> dbResult = new List<RawItemModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetItems
|
|
.Where(x => !x.IsDeleted || !onlyActive)
|
|
.Include(c => c.MaterialNav)
|
|
.OrderBy(x => x.MatId)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Items gestiti a magazzino dato Materiale
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="matID">ID del materiale x cui filtrare, 0 = tutti</param>
|
|
/// <param name="onlyActive">Solo attivi (default) o anche cancellati</param>
|
|
/// <returns></returns>
|
|
public List<RawItemModel> ItemGetByMat(string connString, int matID, bool onlyActive = true)
|
|
{
|
|
List<RawItemModel> dbResult = new List<RawItemModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetItems
|
|
.Where(x => (matID == 0 || x.MatId == matID) && (!x.IsDeleted || !onlyActive))
|
|
.Include(c => c.MaterialNav)
|
|
.OrderBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Items gestiti a magazzino dato Materiale
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="QrCode">QrCode/Dtmx cercato</param>
|
|
/// <returns></returns>
|
|
public RawItemModel ItemGetByQr(string connString, string qrCode)
|
|
{
|
|
RawItemModel? dbResult = new RawItemModel();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
var rawList = dbCtx
|
|
.DbSetItems
|
|
.Include(m => m.MaterialNav)
|
|
.ToList();
|
|
dbResult = rawList
|
|
.Where(x => x.ItemDtmx == qrCode)
|
|
.FirstOrDefault();
|
|
if (dbResult == null)
|
|
{
|
|
dbResult = new RawItemModel();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary> Aggiunge/Modifica un item in magazzino </summary> <param
|
|
/// name="connString">Stringa connessione (variabile x cliente)</param> <param
|
|
/// name="rec2upd">Record da aggiornare</param> <param name="deltaQty">quantità da
|
|
/// aggiornare (se <0 è consumo)</param> <param name="userId">User corrente (SE
|
|
/// applicabile)</param> <param name="msgAdd">Messaggio registrato x variazione
|
|
/// positiva</param> <param name="msgRem">Messaggio registrato x variazione negativa</param> <returns></returns>
|
|
public bool ItemModQty(string connString, RawItemModel rec2upd, int deltaQty, string userId, string msgAdd, string msgRem)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Verifica se esistesse: deve essere valido TUTTO
|
|
* - stesso materiale
|
|
* - stesse dimensioni
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetItems
|
|
.Where(x => (x.RawItemId == rec2upd.RawItemId) ||
|
|
(x.MatId == rec2upd.MatId && (x.WMm == rec2upd.WMm && x.HMm == rec2upd.HMm && x.LMm == rec2upd.LMm)))
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
// calcolo variazione
|
|
currData.QtyAvail += deltaQty;
|
|
// salvo SOLO SE è >=0,,,
|
|
if (currData.QtyAvail >= 0)
|
|
{
|
|
MovMagModel recMovMag = new MovMagModel()
|
|
{
|
|
DtRec = DateTime.Now,
|
|
RawItemId = rec2upd.RawItemId,
|
|
QtyRec = deltaQty,
|
|
UserId = userId,
|
|
Note = deltaQty > 0 ? msgAdd : msgRem
|
|
};
|
|
// registro movimento
|
|
dbCtx.DbSetMovMag.Add(recMovMag);
|
|
// registro modifica RawItem
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
// salvo il tutto
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ItemModQty{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Riattiva Item da magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2del">Item da riattivare</param>
|
|
/// <returns></returns>
|
|
public bool ItemReactiv(string connString, RawItemModel rec2del)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
var currData = dbCtx
|
|
.DbSetItems
|
|
.Where(x => x.RawItemId == rec2del.RawItemId)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
// riattivazione logica...
|
|
currData.IsDeleted = false;
|
|
// registro modifica RawItem
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ItemReactiv{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Converte lista ItemModel in DTO
|
|
/// </summary>
|
|
/// <param name="origItem">Elenco ItemModel di partenza</param>
|
|
/// <returns></returns>
|
|
public List<ItemDTO> ItemsToDto(List<RawItemModel> origItem)
|
|
{
|
|
List<ItemDTO> answ = answ = origItem.Select(x => ItemToDto(x)).ToList();
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Converte ItemModel in DTO
|
|
/// </summary>
|
|
/// <param name="origItem">ItemModel di partenza</param>
|
|
/// <returns></returns>
|
|
public ItemDTO ItemToDto(RawItemModel origItem)
|
|
{
|
|
ItemDTO answ = new ItemDTO()
|
|
{
|
|
MatCloudId = origItem.MatId,
|
|
RawItemCloudId = origItem.RawItemId,
|
|
IsDeleted = origItem.IsDeleted,
|
|
IsRemn = origItem.IsRemn,
|
|
Location = origItem.Location,
|
|
QtyAvail = origItem.QtyAvail,
|
|
HMm = origItem.HMm,
|
|
LMm = origItem.LMm,
|
|
WMm = origItem.WMm,
|
|
Note = origItem.Note,
|
|
ItemDtmx = origItem.ItemDtmx
|
|
};
|
|
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiunge/Modifica un item in magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2upd">Record da aggiungere/aggiornare</param>
|
|
/// <param name="userId">User corrente (SE applicabile)</param>
|
|
/// <param name="forceQty">Se true aggiorna giacenze quantita correnti</param>
|
|
/// <param name="ignoreRemn">Se true ignora aggiornamento remnant in cloud</param>
|
|
/// <returns></returns>
|
|
public bool ItemUpdate(string connString, RawItemModel rec2upd, string userId, bool forceQty, bool ignoreRemn)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Verifica se esistesse: deve essere valido TUTTO
|
|
* - stesso materiale
|
|
* - stesse dimensioni
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetItems
|
|
.Where(x => (x.RawItemId == rec2upd.RawItemId) ||
|
|
(x.MatId == rec2upd.MatId && (x.WMm == rec2upd.WMm && x.HMm == rec2upd.HMm && x.LMm == rec2upd.LMm)))
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
// SOLO SE modifico quantità...
|
|
if (forceQty)
|
|
{
|
|
// aggiungo record variazione quantità...
|
|
int delta = rec2upd.QtyAvail - currData.QtyAvail;
|
|
if (delta != 0)
|
|
{
|
|
MovMagModel recMovMag = new MovMagModel()
|
|
{
|
|
DtRec = DateTime.Now,
|
|
RawItemId = currData.RawItemId,
|
|
//RawItemId = rec2upd.RawItemId,
|
|
QtyRec = delta,
|
|
UserId = userId,
|
|
Note = delta > 0 ? "M02+: Rettifica Inventariale" : "M02-: Rettifica Inventariale"
|
|
};
|
|
dbCtx.DbSetMovMag.Add(recMovMag);
|
|
}
|
|
// aggiorno qty registrata
|
|
currData.QtyAvail = rec2upd.QtyAvail;
|
|
}
|
|
|
|
// sistemo record...
|
|
currData.MatId = rec2upd.MatId;
|
|
currData.IsDeleted = rec2upd.IsDeleted;
|
|
if (!ignoreRemn)
|
|
{
|
|
currData.IsRemn = rec2upd.IsRemn;
|
|
}
|
|
currData.Location = rec2upd.Location;
|
|
currData.LMm = rec2upd.LMm;
|
|
currData.HMm = rec2upd.HMm;
|
|
currData.WMm = rec2upd.WMm;
|
|
currData.Note = rec2upd.Note;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
else
|
|
{
|
|
// levo il matNav...
|
|
if (rec2upd.MaterialNav != null)
|
|
{
|
|
dbCtx.Entry(rec2upd.MaterialNav).State = EntityState.Unchanged;
|
|
}
|
|
// se non FORZA la quantità --> la imposto a zero...
|
|
if (!forceQty)
|
|
{
|
|
rec2upd.QtyAvail = 0;
|
|
}
|
|
|
|
// aggiungo record
|
|
dbCtx
|
|
.DbSetItems
|
|
.Add(rec2upd);
|
|
dbCtx.SaveChanges();
|
|
|
|
// di nuovo registro movimento se va impostata quantità
|
|
if (forceQty)
|
|
{
|
|
// aggiungo record variazione quantità...
|
|
MovMagModel recMovMag = new MovMagModel()
|
|
{
|
|
DtRec = DateTime.Now,
|
|
RawItemId = rec2upd.RawItemId,
|
|
QtyRec = rec2upd.QtyAvail,
|
|
Note = rec2upd.QtyAvail > 0 ? "M03+: Aggiunta Record" : "M03+: Aggiunta Record"
|
|
};
|
|
dbCtx.DbSetMovMag.Add(recMovMag);
|
|
}
|
|
}
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ItemUpdate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Materiali gestiti a magazzino formato DTO
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="machineId">idMacchina di cui si vuole log</param>
|
|
/// <param name="numRec">num rec max da recuperare</param>
|
|
/// <returns></returns>
|
|
public List<LogMachineModel> LogMacGetLast(string connString, int machineId, int numRec)
|
|
{
|
|
List<LogMachineModel> dbResult = new List<LogMachineModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetLogMac
|
|
.Where(x => x.MachineID == machineId)
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(numRec)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elimina un range di dati dal DB per poter inserire in blocco
|
|
/// </summary>
|
|
/// <param name="connString"></param>
|
|
/// <param name="keyNum">Key di riferimento</param>
|
|
/// <param name="machineId">Id Macchina</param>
|
|
/// <param name="dtStart">Data inizio set da eliminare</param>
|
|
/// <param name="dtEnd">Data fine set da eliminare</param>
|
|
/// <returns></returns>
|
|
public int LogMacRemoveRange(string connString, int keyNum, int machineId, DateTime dtStart, DateTime dtEnd)
|
|
{
|
|
int numMod = 0;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
// eseguo stored
|
|
string sqlCommand = $"CALL stp_removeLogMachine ({keyNum}, {machineId}, '{dtStart:yyyy-MM-dd HH:mm:ss.fff}', '{dtEnd:yyyy-MM-dd HH:mm:ss.fff}');";
|
|
dbCtx.Database.ExecuteSqlRaw(sqlCommand);
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in LogMacRemoveRange{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return numMod;
|
|
}
|
|
|
|
public int LogMacUpdate(string connString, List<LogMachineModel> recList)
|
|
{
|
|
bool useStored = true;
|
|
int numMod = 0;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
if (useStored)
|
|
{
|
|
// eseguo stored x ogni record...
|
|
foreach (var item in recList)
|
|
{
|
|
string sqlCommand = $"CALL stp_mergeLogMachine ({item.KeyNum}, {item.MachineID}, {item.ProjDbId}, '{item.DtEvent:yyyy-MM-dd HH:mm:ss.fff}', {(int)item.EvType}, '{item.SupervId}', '{item.VarValue}');";
|
|
dbCtx.Database.ExecuteSqlRaw(sqlCommand);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
// verifico record x data/progetto...
|
|
foreach (var item in recList)
|
|
{
|
|
// cerco
|
|
var recOld = dbCtx
|
|
.DbSetLogMac
|
|
.Where(x => x.KeyNum == item.KeyNum
|
|
&& x.MachineID == item.MachineID
|
|
&& x.ProjDbId == item.ProjDbId
|
|
&& x.DtEvent == item.DtEvent
|
|
&& x.EvType == item.EvType
|
|
&& x.SupervId == item.SupervId
|
|
&& x.VarValue == item.VarValue)
|
|
.FirstOrDefault();
|
|
if (recOld == null)
|
|
{
|
|
dbCtx
|
|
.DbSetLogMac
|
|
.Add(item);
|
|
numMod++;
|
|
}
|
|
}
|
|
// salvo su DB
|
|
dbCtx.SaveChanges();
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in LogMacUpdate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return numMod;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elimina Materiale da magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2del">Item da eliminare</param>
|
|
/// <returns></returns>
|
|
public bool MaterialDelete(string connString, MaterialModel rec2del)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
var currData = dbCtx
|
|
.DbSetMaterials
|
|
.Where(x => x.MatId == rec2del.MatId)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
dbCtx
|
|
.DbSetMaterials
|
|
.Remove(currData);
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in MaterialDelete{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Materiali gestiti a magazzino formato DTO
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="withChild">Se true allora include record child (Items)</param>
|
|
/// <param name="withRemn">Se true allora include record remnants (Items) nei conteggi qty</param>
|
|
/// <returns></returns>
|
|
public List<MaterialDTO> MaterialDtoGetAll(string connString, bool withChild, bool withRemn)
|
|
{
|
|
List<MaterialDTO> dbResult = new List<MaterialDTO>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
// recupero modello base
|
|
var rawList = MaterialGetAll(connString, true);
|
|
// converto
|
|
if (rawList != null)
|
|
{
|
|
dbResult = rawList
|
|
.Select(x => new MaterialDTO()
|
|
{
|
|
MatCloudId = x.MatId,
|
|
MatCode = x.MatCode,
|
|
MatDesc = x.MatDesc,
|
|
HMm = x.HMm,
|
|
LMm = x.LMm,
|
|
WMm = x.WMm,
|
|
SizeNum = x.RawItemList == null ? 0 : x.RawItemList.Where(x => !x.IsDeleted).Count(),
|
|
QtyTot = x.RawItemList == null ? 0 : calcQty(x.IsBeam, x.RawItemList.Where(x => !x.IsDeleted && (withRemn || !x.IsRemn)).ToList()),
|
|
MatDtmx = x.MatDtmx,
|
|
IsBeam = x.IsBeam,
|
|
IsWall = x.IsWall,
|
|
ItemList = withChild ? ItemsToDto(x.RawItemList != null ? x.RawItemList.ToList() : new List<RawItemModel>()) : new List<ItemDTO>()
|
|
})
|
|
.OrderBy(x => x.MatDesc)
|
|
.ThenBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
protected decimal calcQty(bool isBeam, List<RawItemModel> items)
|
|
{
|
|
decimal answ = 0;
|
|
if (isBeam)
|
|
{
|
|
answ = items.Sum(x => x.QtyAvail * x.LMm / 1000);
|
|
}
|
|
else
|
|
{
|
|
answ = items.Sum(x => x.QtyAvail * x.LMm / 1000 * x.WMm / 1000);
|
|
}
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Materiali gestiti a magazzino formato DTO
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="matID">Materiale richiesto, 0=tutti</param>
|
|
/// <param name="withChild">Se true allora include record child (Items)</param>
|
|
/// <returns></returns>
|
|
public List<MaterialDTO> MaterialDtoGetFilt(string connString, int matID, bool withChild)
|
|
{
|
|
List<MaterialDTO> dbResult = new List<MaterialDTO>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
// recupero modello base
|
|
var rawList = MaterialGetFilt(connString, matID, true);
|
|
// converto
|
|
if (rawList != null)
|
|
{
|
|
dbResult = rawList
|
|
.Select(x => new MaterialDTO()
|
|
{
|
|
MatCloudId = x.MatId,
|
|
MatCode = x.MatCode,
|
|
MatDesc = x.MatDesc,
|
|
HMm = x.HMm,
|
|
LMm = x.LMm,
|
|
WMm = x.WMm,
|
|
SizeNum = x.RawItemList == null ? 0 : x.RawItemList.Count,
|
|
QtyTot = x.RawItemList == null ? 0 : x.RawItemList.Sum(r => r.QtyAvail),
|
|
MatDtmx = x.MatDtmx,
|
|
IsBeam = x.IsBeam,
|
|
IsWall = x.IsWall,
|
|
ItemList = withChild ? ItemsToDto(x.RawItemList != null ? x.RawItemList.ToList() : new List<RawItemModel>()) : new List<ItemDTO>()
|
|
})
|
|
.OrderBy(x => x.MatDesc)
|
|
.ThenBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Materiali gestiti a magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <returns></returns>
|
|
public List<MaterialModel> MaterialGetAll(string connString, bool withChild)
|
|
{
|
|
List<MaterialModel> dbResult = new List<MaterialModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
if (withChild)
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetMaterials
|
|
.Include(x => x.RawItemList)
|
|
.OrderBy(x => x.MatDesc)
|
|
.ThenBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
else
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetMaterials
|
|
.OrderBy(x => x.MatDesc)
|
|
.ThenBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Materiali gestiti a magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="projDbId">Materiale richiesto, 0 = tutti</param>
|
|
/// <param name="withChild">Se true allora include record child (Items)</param>
|
|
/// <returns></returns>
|
|
public List<MaterialModel> MaterialGetFilt(string connString, int matID, bool withChild)
|
|
{
|
|
List<MaterialModel> dbResult = new List<MaterialModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
if (withChild)
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetMaterials
|
|
.Where(x => matID == 0 || x.MatId == matID)
|
|
.Include(x => x.RawItemList)
|
|
.OrderBy(x => x.MatDesc)
|
|
.ThenBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
else
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetMaterials
|
|
.Where(x => matID == 0 || x.MatId == matID)
|
|
.OrderBy(x => x.MatDesc)
|
|
.ThenBy(x => x.WMm)
|
|
.ThenBy(x => x.HMm)
|
|
.ThenBy(x => x.LMm)
|
|
.ToList();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiunge/Modifica un Materiale in magazzino
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2upd">Record da aggiungere/aggiornare</param>
|
|
/// <returns></returns>
|
|
public bool MaterialUpdate(string connString, MaterialModel rec2upd)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Ricerca equal: corrisponde se
|
|
* - MatCloudId identico
|
|
* - se Uguali + NonNulli [MatCode oppure MatDescript] + uguali [W/H/L]...
|
|
*/
|
|
var currData = dbCtx
|
|
.DbSetMaterials
|
|
.Where(x => (rec2upd.MatId > 0 && x.MatId == rec2upd.MatId) ||
|
|
((x.WMm == rec2upd.WMm && x.HMm == rec2upd.HMm && x.LMm == rec2upd.LMm) &&
|
|
((!string.IsNullOrEmpty(rec2upd.MatCode) && x.MatCode == rec2upd.MatCode) || (!string.IsNullOrEmpty(rec2upd.MatDesc) && x.MatDesc == rec2upd.MatDesc))
|
|
))
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
currData.MatCode = rec2upd.MatCode;
|
|
currData.MatDesc = rec2upd.MatDesc;
|
|
currData.LMm = rec2upd.LMm;
|
|
currData.HMm = rec2upd.HMm;
|
|
currData.WMm = rec2upd.WMm;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
else
|
|
{
|
|
dbCtx
|
|
.DbSetMaterials
|
|
.Add(rec2upd);
|
|
}
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in MaterialUpdate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco MovMag dato Item
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rawItemID">ID dell'item x cui filtrare, 0 = tutti</param>
|
|
/// <param name="maxRec">numMax record da leggere, default 1000</param>
|
|
/// <returns></returns>
|
|
public List<MovMagModel> MovMagGetFilt(string connString, int rawItemID, int maxRec = 1000)
|
|
{
|
|
List<MovMagModel> dbResult = new List<MovMagModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetMovMag
|
|
.Where(x => rawItemID == 0 || x.RawItemId == rawItemID)
|
|
//.Include(c => c.ItemNav)
|
|
.OrderByDescending(x => x.DtRec)
|
|
.Take(maxRec)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elimina record Project
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2del">Item da eliminare</param>
|
|
/// <returns></returns>
|
|
public bool ProjectDelete(string connString, ProjModel rec2del)
|
|
{
|
|
bool done = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
var currData = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => x.ProjDbId == rec2del.ProjDbId)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
dbCtx
|
|
.DbSetProjects
|
|
.Remove(currData);
|
|
dbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ProjectDelete{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco Projects (all)
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <returns></returns>
|
|
public List<ProjModel> ProjectGetAll(string connString)
|
|
{
|
|
List<ProjModel> dbResult = new List<ProjModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetProjects
|
|
.OrderBy(x => x.DtCreated)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Record progetto dato cliente e Key (ID)
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="ProjCloudId">Key del record cercato (>0)</param>
|
|
/// <returns></returns>
|
|
public ProjModel ProjectGetById(string connString, int ProjCloudId)
|
|
{
|
|
ProjModel? dbResult = new ProjModel();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => ProjCloudId > 0 && x.ProjDbId == ProjCloudId)
|
|
.FirstOrDefault();
|
|
if (dbResult == null)
|
|
{
|
|
dbResult = new ProjModel();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
/// <summary>
|
|
/// Elenco progetti modificati dopo data
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="DtRif">Data rispetto cui cercare modifiche successive</param>
|
|
/// <returns>Elenco records progetti</returns>
|
|
public List<ProjModel> ProjectGetModAfter(string connString, DateTime DtRif)
|
|
{
|
|
List<ProjModel> dbResult = new List<ProjModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => x.DtLastAction >= DtRif)
|
|
.ToList();
|
|
if (dbResult == null)
|
|
{
|
|
dbResult = new List<ProjModel>();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// Elenco progetti dato cliente e macchina
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="machineID">ID macchina, 0 = tutti</param>
|
|
/// <returns></returns>
|
|
public List<ProjModel> ProjectGetByMachine(string connString, int machineID)
|
|
{
|
|
List<ProjModel> dbResult = new List<ProjModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => (machineID == 0 || x.MachineID == machineID))
|
|
.OrderBy(x => x.DtCreated)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco progetti dato cliente e macchina + periodo
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="numKey">ID master key, 0 = tutti</param>
|
|
/// <param name="period">periodo x filtraggio</param>
|
|
/// <returns></returns>
|
|
public List<ProjModel> ProjectGetFilt(string connString, int numKey, SelectData period)
|
|
{
|
|
List<ProjModel> dbResult = new List<ProjModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => (numKey == 0 || x.KeyNum == numKey) &&
|
|
((x.DtCreated >= period.DateStart && x.DtCreated <= period.DateEnd)
|
|
|| (x.DtSchedule >= period.DateStart && x.DtSchedule <= period.DateEnd)
|
|
|| (x.DtLastAction >= period.DateStart && x.DtLastAction <= period.DateEnd)
|
|
))
|
|
.OrderBy(x => x.DtCreated)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Upsert di un record Project
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="projDbId">ID Record progetto da aggiornare</param>
|
|
/// <param name="procTime">Durata progetto (cumulata)</param>
|
|
/// <param name="valAct">Valore Progresso attuale (tipicamente num barre/pezzi)</param>
|
|
/// <param name="valMax">Valore Progresso max atteso (tipicamente num barre/pezzi)</param>
|
|
/// <returns>Esito update</returns>
|
|
public bool ProjectSetProgr(string connString, int projDbId, double procTime, double valAct, double valMax)
|
|
{
|
|
bool fatto = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Ricerca:
|
|
* - DbId corrisponde e > 0...
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => (projDbId > 0 && x.ProjDbId == projDbId))
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
// aggiorno solo se > 0...
|
|
if (procTime > 0)
|
|
{
|
|
currData.ProcTimeReal = procTime;
|
|
}
|
|
currData.ValAct = valAct;
|
|
currData.ValMax = valMax;
|
|
currData.DtLastAction = DateTime.Now;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
var done = dbCtx.SaveChanges();
|
|
// registro esito ok
|
|
fatto = done > 0;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ProjectSetProgr{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return fatto;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Upsert di un record Project
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2upd">Record da aggiungere/aggiornare</param>
|
|
/// <returns>ID del progetto creato/aggiornato da usare come CloudId</returns>
|
|
public int ProjectUpsert(string connString, ProjModel rec2upd)
|
|
{
|
|
int newId = 0;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Ricerca:
|
|
* - DbId (cloud) corrisponde e > 0...
|
|
* [[ RIMOSSO - Key + Id remoti corrispondono]]
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => (rec2upd.ProjDbId > 0 && x.ProjDbId == rec2upd.ProjDbId))
|
|
.FirstOrDefault();
|
|
//.Where(x => (rec2upd.projDbId > 0 && x.projDbId == rec2upd.projDbId) ||
|
|
// ((x.ProjExtDbId == rec2upd.ProjExtDbId && x.KeyNum == rec2upd.KeyNum) && (x.ProjExtId == rec2upd.ProjExtId && x.KeyNum == rec2upd.KeyNum)))
|
|
if (currData != null)
|
|
{
|
|
currData.MachineID = rec2upd.MachineID;
|
|
currData.KeyNum = rec2upd.KeyNum;
|
|
currData.ProjExtDbId = rec2upd.ProjExtDbId;
|
|
currData.ProjExtId = rec2upd.ProjExtId;
|
|
currData.BTLFileName = rec2upd.BTLFileName;
|
|
currData.PType = rec2upd.PType;
|
|
currData.Machine = rec2upd.Machine;
|
|
currData.ProjDescription = rec2upd.ProjDescription;
|
|
currData.DtCreated = rec2upd.DtCreated;
|
|
// imposto comunque adesso come aggiornamento
|
|
//currData.DtLastAction = rec2upd.DtLastAction;
|
|
currData.DtLastAction = DateTime.Now;
|
|
currData.DtSchedule = rec2upd.DtSchedule;
|
|
currData.DtStartProd = rec2upd.DtStartProd;
|
|
currData.ListName = rec2upd.ListName;
|
|
currData.ProcTimeEst = rec2upd.ProcTimeEst;
|
|
currData.ProcTimeReal = rec2upd.ProcTimeReal;
|
|
currData.IsActive = rec2upd.IsActive;
|
|
currData.IsArchived = rec2upd.IsArchived;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
else
|
|
{
|
|
dbCtx
|
|
.DbSetProjects
|
|
.Add(rec2upd);
|
|
}
|
|
dbCtx.SaveChanges();
|
|
// il mio ID è quello originale o appena creato post save...
|
|
newId = rec2upd.ProjDbId;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ProjectUpsert{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return newId;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recupera ultimo record attivo di un progetto/stato indicato
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="ProjCloudId">ID del progetto da cercare</param>
|
|
/// <param name="ResState">Stato richiesta da cercare</param>
|
|
/// <returns></returns>
|
|
public RequestPlanModel ReqPlanGetLast(string connString, int ProjCloudId, ProjResState ResState)
|
|
{
|
|
RequestPlanModel dbResult = new RequestPlanModel(); ;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
/*
|
|
* Ricerca x Id corrispondente
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetReqPlan
|
|
.Where(x => x.ProjDbId == ProjCloudId && x.ReqState == ResState && x.IsActive)
|
|
.OrderByDescending(x => x.DtRequest)
|
|
.FirstOrDefault();
|
|
|
|
dbResult = currData ?? new RequestPlanModel();
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ReqPlanGetLast{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiunge/Modifica un record ReqPlan
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="rec2upd">Record da aggiungere/aggiornare</param>
|
|
/// <returns></returns>
|
|
public int ReqPlanUpdate(string connString, RequestPlanModel rec2upd)
|
|
{
|
|
int newId = 0;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
DateTime adesso = DateTime.Now;
|
|
// imposto data update Proj relativo ad adesso...
|
|
var recProj = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => x.ProjDbId == rec2upd.ProjDbId)
|
|
.FirstOrDefault();
|
|
if (recProj != null)
|
|
{
|
|
recProj.DtLastAction = adesso;
|
|
dbCtx.Entry(recProj).State = EntityState.Modified;
|
|
}
|
|
|
|
/*
|
|
* Ricerca ReqPlan x Id corrispondente
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetReqPlan
|
|
.Where(x => x.RequestId == rec2upd.RequestId)
|
|
.FirstOrDefault();
|
|
|
|
// aggiorno
|
|
if (currData != null)
|
|
{
|
|
currData.ProjDbId = rec2upd.ProjDbId;
|
|
currData.DtRequest = rec2upd.DtRequest;
|
|
currData.ReqState = rec2upd.ReqState;
|
|
currData.IsActive = rec2upd.IsActive;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
else
|
|
{
|
|
// se NON di consumo prima rendo disattivi altri PRECEDENTI...
|
|
if (rec2upd.ReqState > Enums.ProjResState.Consumed)
|
|
{
|
|
var rec2disable = dbCtx
|
|
.DbSetReqPlan
|
|
.Where(x => x.IsActive && x.ProjDbId == rec2upd.ProjDbId && x.ReqState > Enums.ProjResState.Consumed && x.DtRequest <= rec2upd.DtRequest)
|
|
.ToList();
|
|
if (rec2disable != null)
|
|
{
|
|
foreach (var recAct in rec2disable)
|
|
{
|
|
recAct.IsActive = false;
|
|
dbCtx.Entry(recAct).State = EntityState.Modified;
|
|
}
|
|
}
|
|
}
|
|
// verifico se ho richieste successive così da disattivare la corrente...
|
|
var recActive = dbCtx
|
|
.DbSetReqPlan
|
|
.Where(x => x.IsActive && x.ProjDbId == rec2upd.ProjDbId && x.ReqState > Enums.ProjResState.Consumed && x.DtRequest > rec2upd.DtRequest)
|
|
.ToList();
|
|
// se ci fossero...
|
|
if (recActive != null && recActive.Count > 0)
|
|
{
|
|
// disattivo richiesta corrente
|
|
rec2upd.IsActive = false;
|
|
}
|
|
// aggiungo record
|
|
dbCtx
|
|
.DbSetReqPlan
|
|
.Add(rec2upd);
|
|
}
|
|
dbCtx.SaveChanges();
|
|
newId = rec2upd.RequestId;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ReqPlanUpdate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return newId;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Converte il DTO in ResourceModel
|
|
/// </summary>
|
|
/// <param name="origItem">DTO di partenza</param>
|
|
/// <returns></returns>
|
|
public ResourceModel ResourceFromDto(ResourceDTO origItem, int reqId)
|
|
{
|
|
ResourceModel answ = new ResourceModel()
|
|
{
|
|
Qty = origItem.Qty,
|
|
RawItemId = origItem.RawItemCloudId,
|
|
RequestId = reqId,
|
|
ResourceId = 0
|
|
};
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco risorse dato progetto e stato
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="projDbId">ID progetto</param>
|
|
/// <param name="isEstim">true = ultima stima attiva / false = consumi effettivi</param>
|
|
/// <returns></returns>
|
|
public List<ResourceExpDTO> ResourcesExpGetByProject(string connString, int projDbId, bool isEstim, bool showAll)
|
|
{
|
|
List<ResourceExpDTO> dbResult = new List<ResourceExpDTO>();
|
|
List<ResourceModel> rawData = new List<ResourceModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
if (isEstim)
|
|
{
|
|
rawData = dbCtx
|
|
.DbSetResources
|
|
.Include(p => p.RequestNav)
|
|
.Include(i => i.ItemNav)
|
|
.Where(x => x.RequestNav.ProjDbId == projDbId && (x.RequestNav.IsActive || showAll) && x.RequestNav.ReqState > Enums.ProjResState.ND)
|
|
.ToList();
|
|
}
|
|
else
|
|
{
|
|
rawData = dbCtx
|
|
.DbSetResources
|
|
.Include(p => p.RequestNav)
|
|
.Include(i => i.ItemNav)
|
|
.Where(x => x.RequestNav.ProjDbId == projDbId && x.RequestNav.IsActive && x.RequestNav.ReqState == Enums.ProjResState.Consumed)
|
|
.OrderBy(x => x.ResourceId)
|
|
.ToList();
|
|
}
|
|
try
|
|
{
|
|
dbResult = rawData.Select(x => new ResourceExpDTO()
|
|
{
|
|
ResourceCloudId = x.ResourceId,
|
|
RawItemCloudId = x.RawItemId,
|
|
Qty = x.Qty,
|
|
Note = x.ItemNav.Note,
|
|
DtRequest = x.RequestNav.DtRequest,
|
|
HMm = x.ItemNav.HMm,
|
|
LMm = x.ItemNav.LMm,
|
|
WMm = x.ItemNav.WMm,
|
|
IsDeleted = x.ItemNav.IsDeleted,
|
|
IsRemn = x.ItemNav.IsRemn,
|
|
MatCloudId = x.ItemNav.MatId,
|
|
RequestId = x.RequestId
|
|
})
|
|
.OrderByDescending(x => x.DtRequest)
|
|
.ThenByDescending(x => x.ResourceCloudId)
|
|
.ToList();
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione ResourcesExpGetByProject{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco risorse dato progetto e stato
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="projDbId">ID progetto</param>
|
|
/// <param name="isEstim">true = ultima stima attiva / false = consumi effettivi</param>
|
|
/// <returns></returns>
|
|
public List<ResourceModel> ResourcesGetByProject(string connString, int projDbId, bool isEstim, bool showAll)
|
|
{
|
|
List<ResourceModel> dbResult = new List<ResourceModel>();
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
if (isEstim)
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetResources
|
|
.Where(x => x.RequestNav.ProjDbId == projDbId && (x.RequestNav.IsActive || showAll) && x.RequestNav.ReqState > Enums.ProjResState.ND)
|
|
.OrderBy(x => x.ResourceId)
|
|
.ToList();
|
|
}
|
|
else
|
|
{
|
|
dbResult = dbCtx
|
|
.DbSetResources
|
|
.Where(x => x.RequestNav.ProjDbId == projDbId && x.RequestNav.IsActive && x.RequestNav.ReqState == Enums.ProjResState.Consumed)
|
|
.OrderBy(x => x.ResourceId)
|
|
.ToList();
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiunge/Modifica un elenco di Resource (+ eventuali update giacenze)
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="requestPlanId">Key della richiesta di riferimento</param>
|
|
/// <param name="recList">Elenco record da aggiungere/aggiornare</param>
|
|
/// <param name="resState">Tipo di aggiornamento da registratre</param>
|
|
/// <param name="noteUid">User corrente (SE applicabile)</param>
|
|
/// <returns></returns>
|
|
public int ResourceUpdate(string connString, int requestPlanId, List<ResourceDTO> recList, Enums.ProjResState resState, string noteUid)
|
|
{
|
|
int numMod = 0;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
foreach (var rec2upd in recList)
|
|
{
|
|
if (resState == Enums.ProjResState.Consumed)
|
|
{
|
|
// aggiungo record variazione quantità...
|
|
MovMagModel recMovMag = new MovMagModel()
|
|
{
|
|
DtRec = DateTime.Now,
|
|
RawItemId = rec2upd.RawItemCloudId,
|
|
QtyRec = rec2upd.Qty,
|
|
UserId = noteUid,
|
|
Note = rec2upd.Qty > 0 ? "M05+: Aggiunta Risorsa" : "M05-: Consumo Risorsa"
|
|
};
|
|
dbCtx.DbSetMovMag.Add(recMovMag);
|
|
}
|
|
|
|
// aggiungo record
|
|
dbCtx
|
|
.DbSetResources
|
|
.Add(ResourceFromDto(rec2upd, requestPlanId));
|
|
|
|
// se si tratta di consumo --> aggiorno giacenze!
|
|
if (resState == Enums.ProjResState.Consumed)
|
|
{
|
|
// cerco record giacenza da aggiornare...
|
|
|
|
var recGiac = dbCtx
|
|
.DbSetItems
|
|
.Where(x => x.RawItemId == rec2upd.RawItemCloudId)
|
|
.FirstOrDefault();
|
|
if (recGiac != null)
|
|
{
|
|
recGiac.QtyAvail += rec2upd.Qty;
|
|
dbCtx.Entry(recGiac).State = EntityState.Modified;
|
|
}
|
|
}
|
|
numMod++;
|
|
}
|
|
// salvo su DB
|
|
dbCtx.SaveChanges();
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ResourceUpdate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return numMod;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Update record Project + refresh cache x info archived
|
|
/// </summary>
|
|
/// <param name="connString">Stringa connessione (variabile x cliente)</param>
|
|
/// <param name="newData">IDizionario ProdId / IsArchived da impostare</param>
|
|
/// <returns>Esito update</returns>
|
|
public bool ProjectUpdArchived(string connString, Dictionary<int, bool> newData)
|
|
{
|
|
bool fatto = false;
|
|
using (MagManContext dbCtx = new MagManContext(connString))
|
|
{
|
|
try
|
|
{
|
|
DateTime adesso = DateTime.Now;
|
|
foreach (var item in newData)
|
|
{
|
|
/*
|
|
* Ricerca:
|
|
* - DbId corrisponde e > 0...
|
|
* */
|
|
var currData = dbCtx
|
|
.DbSetProjects
|
|
.Where(x => (item.Key > 0 && x.ProjDbId == item.Key))
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
currData.IsArchived = item.Value;
|
|
currData.DtLastAction = adesso;
|
|
dbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
}
|
|
var done = dbCtx.SaveChanges();
|
|
// registro esito ok
|
|
fatto = done > 0;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ProjectUpdArchived{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return fatto;
|
|
}
|
|
|
|
#endregion Public Methods
|
|
|
|
#region Private Fields
|
|
|
|
private static NLog.Logger Log = LogManager.GetCurrentClassLogger();
|
|
|
|
#endregion Private Fields
|
|
}
|
|
} |