public async Task <ActionResult <HttpResponseMessage> > CaricaSmarriti()
{
var connectionString = RepositoryContext.ConnectionString;
var isError = false;
var exMessage = String.Empty;
var isXlsFiles = IsDocumentUploaded(_uploadFolder, out string[] xlsFiles);
_logger.LogInformation("Controller started!");
if (!isXlsFiles)
{
return(BadRequest("Nessun file excel caricato!"));
}
try
{
foreach (var excelfile in _dictFile.DictionaryOfFiles.Values)
{
var filenamepath = _uploadFolder + @"\" + excelfile; // add upload folder to path of filename
await Task.Run(() => _outputNames.Add(OpenDocument(filenamepath, connectionString)));
_logger.LogInformation("open excel document");
}
_logger.LogInformation("Reading ended.");
}
catch (Exception ex)
{
_logger.LogInformation("Exception : " + ex.Message);
isError = true;
exMessage = ex.Message;
}
finally
{
if (isError)
{
// build filename append guid to end of filename to have unique name
var fileName = "log_error_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("HH.mm.ss_") + Guid.NewGuid() + ".txt";
_outputNames.Add(fileName);
ReportFile.InitFile(fileName);
ReportFile.WriteLine("Problemi durante il caricamento dei codici");
ReportFile.WriteLine(exMessage);
ReportFile.CloseFile();
CopyLogErrorFile2SharedFolder(fileName);
}
}
// return all filename generated included file of errors
if (isError)
{
isError = false;
return(BadRequest("Problema durante la lettura del/i file di codici!"));
}
// Serialize text file to json
var result = ConvertToFileContentResult(_outputNames);
return(result);
}
// Write summary at the end in the report file
private static void WriteSummary(long outcomes, long wasteOutcomeDate, long wasted, long newCodeNotInLostCodes, long outcomeNewClaimDate, long notInDb)
{
ReportFile.WriteLine("");
ReportFile.WriteLine("");
ReportFile.WriteLine(notInDb + " esiti non presenti in esa_racc");
ReportFile.WriteLine(outcomes + " esiti presenti in esa_racc");
ReportFile.WriteLine(newCodeNotInLostCodes + " esiti caricati (primo inserimento)");
ReportFile.WriteLine(outcomeNewClaimDate + " esiti caricati con nuova data denuncia");
ReportFile.WriteLine(wasted + " scartati");
ReportFile.WriteLine(wasteOutcomeDate + " scartati per data esito non valida");
ReportFile.CloseFile();
}
// Check if send code is present in table
private static void CodiceInvioEsistAggiornaDataDenuncia(string connectionString, string sendCode, DateTime outcomeDate, string outcome, string filename, ILogger logger)
{
try
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "SP_LoadSmarriti_CodiceInvioEsistAggiornaDataDenuncia";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@sendCode", SqlDbType.VarChar).Value = sendCode;
command.Parameters.Add("@outcomeDate", SqlDbType.SmallDateTime).Value = outcomeDate;
command.Parameters.Add("@outcome", SqlDbType.VarChar).Value = outcome;
command.Parameters.Add("@filename", SqlDbType.VarChar).Value = filename;
connection.Open();
command.ExecuteNonQuery();
//ts.WriteLine str_code &" inserito nuovamente perchè già presente in CODICI_SMARRITI con data denuncia diversa"
ReportFile.WriteLine(sendCode + " inserito nuovamente perchè già presente in CODICI_SMARRITI con data denuncia diversa");
}
}
catch (Exception ex)
{
logger.LogError("Error checking if send code exist then update outcome date : " + ex.Message);
}
}
public IActionResult AggiornaRacc()
{
var isError = false;
var nomefileErr = String.Empty;
try
{
_logger.LogInformation("Controller update data started!");
long cons = 0;
var today = DateTime.Now.ToString("yyyy-MM-dd");
if (!Directory.Exists(_outputFolder))
{
_logger.LogInformation("create directory " + _outputFolder);
Directory.CreateDirectory(_outputFolder);
}
var outputFileName = DateTime.Now.ToString("yyyy-MM-dd") + "-SCARTI_UPDATE_" + DateTime.Now.ToString("hh.mm.ss") + ".txt";
var outcomesFilename = Path.Combine(_outputFolder, _lostFolder) + @"\" + outputFileName;
//esegue stored prodecedure
_logger.LogInformation("execute stored procedure: SP_aggiorna_esa_racc");
var updatedRecords = new DAL.StoredProcedure.SP_aggiorna_esa_racc(RepositoryContext);
long itemsUpdated = updatedRecords.Set(today);
cons += itemsUpdated;
//controlla se esistono esiti scartati per data non corretta
_logger.LogInformation("execute stored procedure: SP_controlla_esiti_scartati");
var esitiScartati = new RAR.DAL.StoredProcedure.SP_controlla_esiti_scartati(RepositoryContext);
long scarti = esitiScartati.Get(today).Result.Count();
ReportFile.InitFile(outcomesFilename);
_logger.LogInformation("create end write file: " + outcomesFilename);
if (scarti != 0)
{
foreach (var rowScartati in esitiScartati.Get(today).Result)
{
ReportFile.WriteLine(rowScartati.CodiceInvio + " - " +
$"{rowScartati.DataDenuncia:yyyy-MM-dd}" + " - NOTIFICA ERRATA");
}
}
//scrive il riepilogo
ReportFile.WriteLine("");
ReportFile.WriteLine("");
ReportFile.WriteLine(cons + " esiti aggiornati");
ReportFile.WriteLine(scarti + " scartati");
ReportFile.CloseFile();
_logger.LogInformation("Esiti recuperati correttamente");
//return Content(JsonConvert.SerializeObject(new { messaggio = "Esiti recuperati correttamente", avviso = "Creati files di riepilogo in " + _outputFolder }));
CopyOutcomesFile2SharedFolder(outcomesFilename, outputFileName);
if (Directory.Exists(@"\\" + _sharedOutputFolder))
{
return(Content(new String("Esiti recuperati correttamente.\n Creati files di riepilogo in : " + _sharedOutputFolder)));
}
else
{
return(Content(new String("Esiti recuperati correttamente.\n Creati files di riepilogo.")));
}
}
catch (Exception ex)
{
_logger.LogError("Problemi durante l'aggiornamento degli esiti");
_logger.LogError(ex.Message);
isError = true;
nomefileErr = "log_error_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh.mm.ss") + ".txt";
ReportFile.InitFile(nomefileErr);
ReportFile.WriteLine("Problemi durante l'aggiornamento degli esiti");
ReportFile.WriteLine(ex.Message);
ReportFile.WriteLine(ex.Source);
ReportFile.CloseFile();
return(BadRequest("Problemi durante l'aggiornamento degli esiti. Impossibile proseguire."));
}
finally
{
if (isError)
{
CopyLogErrorFile2SharedFolder(nomefileErr);
}
}
}
private static void ParseDocument(string connectionString, IExcelDataReader reader, string filename, ILogger logger)
{
long outcomes = 0;
long wasteOutcomeDate = 0; //scartidataesito;
long wasted = 0; // scarti
string outcomeDate = String.Empty;
string outcome = String.Empty;
long newCodeNotInLostCodes = 0;
long outcomeNewClaimDate = 0; //esiti caricati con nuova data denuncia
bool flag = false;
long notInDb = 0; // codice invio non presente in esa racc nome variabile : noDb
// Extract source file name only without path
filename = Path.GetFileName(filename);
try
{
using (reader)
{
// assign excel sheet to dataset
DataSet ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
// parsing rows, getting first table for default
foreach (DataRow r in ds.Tables[0].Rows)
{
// Validate data , first column we search is defined in app settings
string sendCode = r.ItemArray[0].ToString().ToUpper();
if (!flag && sendCode != "CODICE INVIO")
{
//rowLine++;
continue;
}
if (sendCode == "CODICE INVIO")
{
// the next rows contains send code
flag = true;
continue;
}
if ((sendCode != String.Empty) && (sendCode.Length == 12) && sendCode.All(char.IsDigit)) // try to validate sendcode
{
// if send code is not present in esa racc
if (IsSendCodeInEsaRacc(connectionString, sendCode, logger) == 0) //if (selectSendCode.Count == 0)
{
notInDb++;
ReportFile.WriteLine(sendCode + " - NON PRESENTE A DB (in esa_racc)");
continue;
}
// controllo se esistono esiti scartati con un'esito in esa_racc
using (var connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("SP_LoadSmarriti_SeEsitiScartatiConEsitoInEsaRacc", connection);
cmd.Parameters.Add("@sendCode", SqlDbType.VarChar).Value = sendCode;
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
var dataReader = cmd.ExecuteReader();
var dtEsaRacc = new DataTable();
dtEsaRacc.Load(dataReader);
// if there is records
if (dtEsaRacc.Rows.Count > 0)
{
// get first row
var rowDtEsaRacc = dtEsaRacc.Rows[0];
//var discarderdOutcomes = db.EsaRaccs.FromSql(sqlQuery).ToList();
// add counter for outcomes or 'esitati'
outcomes++;
ReportFile.WriteLine(rowDtEsaRacc["code_racc"] + " - " + String.Format(rowDtEsaRacc["data_elab"].ToString(), "yyyy-MM-dd") +
" - PRESENTE GIA IN ESA_RACC CON ESITO " + rowDtEsaRacc["codice_esito"] + " E FONTE " + rowDtEsaRacc["flag_fonte_esito"]);
// goto cliente errato
continue;
}
cmd.Dispose();
}
// Check outcome date 'data esito'
outcomeDate = r.ItemArray[1].ToString(); // data esito
outcome = (r.ItemArray[2].ToString()).Replace("'", "''"); // esito
DateTime dtOutComeDate; // convert in datetime type outcomeDate string
if (String.IsNullOrEmpty(outcomeDate))
{
wasteOutcomeDate++;
ReportFile.WriteLine(sendCode + " - DATA ESITO NON VALIDA ");
continue;
}
var provider = new CultureInfo("it-IT");
dtOutComeDate = DateTime.Parse(outcomeDate, provider, DateTimeStyles.NoCurrentDateDefault);
outcomeDate = outcomeDate.Replace("/", "-");
var dtCodiciSmarriti = SeCodiceInvioInCodiciSmarriti(connectionString, sendCode, logger);
DataRow rowDtCodiciSmarriti = null;
if (dtCodiciSmarriti.Rows.Count > 0)
{
rowDtCodiciSmarriti = dtCodiciSmarriti.Rows[0];
}
if (dtCodiciSmarriti.Rows.Count == 0)
{
// new code not present in lost codes - nuovo codice non presente in codici_smarriti
newCodeNotInLostCodes++;
// Convert outcomeDate to datetime
CodiceInvioNonInCodiciSmarriti(connectionString, sendCode, dtOutComeDate, outcome, filename, logger);
}
else if (rowDtCodiciSmarriti != null && dtOutComeDate != DateTime.Parse(rowDtCodiciSmarriti["data_denuncia"].ToString()))
{
// codice già esistente ma con data denuncia da aggiornare viene inserito per mantenere i dati iniziali
outcomeNewClaimDate++;
CodiceInvioEsistAggiornaDataDenuncia(connectionString, sendCode, dtOutComeDate, outcome, filename, logger);
}
else
{
wasted++;
ReportFile.WriteLine(sendCode + " scartato perchè già presente in CODICI_SMARRITI con data denuncia uguale");
}
}
else
{
wasted++;
ReportFile.WriteLine(sendCode + " non conforme");
}
}
// Write summary - 'scrivo il riepilogo
WriteSummary(outcomes, wasteOutcomeDate, wasted, newCodeNotInLostCodes, outcomeNewClaimDate, notInDb);
}
}
catch (Exception ex)
{
logger.LogError("Error parsing document: " + ex.Message);
}
}