using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
namespace C6Contract {
public partial class Main : Form {
string schema = string.Empty;
DateTime dataCongelamento = DateTime.MinValue; //null;
public Main() {
InitializeComponent();
}
void LoadCFs() {
dgvExistingCFs.Rows.Clear();
dgvToDelete.Rows.Clear();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerStampeC6Connection"].ConnectionString)) {
SqlCommand cmd = new SqlCommand("SELECT IdReport, TipoReport, GPF.Rete AS Rete, CodiceFiscale, NOME, COGNOME FROM " + schema + ".GESTIONE_PDF_FTP GPF INNER JOIN " + schema + ".ANAG_CLIENTI AC ON GPF.Rete = AC.RETE AND GPF.CodiceFiscale = AC.COD_FISCALE WHERE dbo.ToShortDateString(@Data) = dbo.ToShortDateString(DataGenerazione) ORDER BY Rete, COGNOME, NOME, TipoReport", conn);
SqlDataReader reader = null;
try {
string data = dtpCFs.Value.Year.ToString() + (dtpCFs.Value.Month < 10 ? "0" + dtpCFs.Value.Month.ToString() : dtpCFs.Value.Month.ToString()) + (dtpCFs.Value.Day < 10 ? "0" + dtpCFs.Value.Day.ToString() : dtpCFs.Value.Day.ToString());
cmd.Parameters.Add("@Data", SqlDbType.VarChar).Value = data;
conn.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
while (reader.Read()) {
DataGridViewRow dgvr = new DataGridViewRow();
dgvr.CreateCells(dgvExistingCFs);
dgvr.Cells[5].Value = reader["IdReport"];
dgvr.Cells[3].Value = reader["Rete"];
dgvr.Cells[4].Value = reader["CodiceFiscale"];
dgvr.Cells[2].Value = reader["NOME"];
dgvr.Cells[1].Value = reader["COGNOME"];
dgvr.Cells[0].Value = reader["TipoReport"].ToString().Contains("D") ? "Diagnosi" : "Monitoraggio";
dgvExistingCFs.Rows.Add(dgvr);
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
finally {
if (reader != null && !reader.IsClosed)
reader.Close();
if (conn.State == ConnectionState.Open)
conn.Close();
dgvExistingCFs.Columns["ExistingReport"].Visible = schema == "C6MartPeriodico";
dgvToDelete.Columns["ToDeleteReport"].Visible = schema == "C6MartPeriodico";
if (dgvExistingCFs.Rows.Count > 0)
dgvExistingCFs.ClearSelection();
}
}
}
void MoveItems(DataGridView from, DataGridView to) {
foreach (DataGridViewRow dgvr in from.SelectedRows)
if (!to.Rows.Contains(dgvr)) {
from.Rows.Remove(dgvr);
to.Rows.Add(dgvr);
}
to.Sort(to.Columns[0], ListSortDirection.Ascending);
to.Sort(to.Columns[2], ListSortDirection.Ascending);
to.Sort(to.Columns[1], ListSortDirection.Ascending);
to.Sort(to.Columns[3], ListSortDirection.Ascending);
from.ClearSelection();
to.ClearSelection();
}
void ScrollGVBar(DataGridView toMove, ScrollEventArgs e) {
if (e.ScrollOrientation == ScrollOrientation.HorizontalScroll)
toMove.HorizontalScrollingOffset = e.NewValue;
}
private void Main_Load(object sender, EventArgs e) {
cmbReportType.SelectedIndex = 0;
}
private void cmbReportType_SelectedIndexChanged(object sender, EventArgs e) {
lblReportTypeDesc.Text = cmbReportType.SelectedItem.ToString().ToUpper();
//Recupera data Trimestre se PERIODICO, altrimenti setta label
if (cmbReportType.SelectedItem.ToString().ToUpper() == "PERIODICO")
{
using (SqlConnection connC6 = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerStampeC6Connection"].ConnectionString))
{
try
{
connC6.Open();
SqlCommand cmdC6 = new SqlCommand("SELECT [DATA_CONGELAMENTO] FROM [C6MartPeriodico].[CONGELAMENTO_SEI]",connC6);
dataCongelamento = (DateTime)cmdC6.ExecuteScalar();
DataTrim.Text = dataCongelamento.ToString("dddd, dd MMMM yyyy");
}
catch (Exception ex) {
DataTrim.Text = "ATTENZIONE ERRORE NEL RECUPERO DELLA DATA: "+ex.Message;
}
finally
{
if (connC6.State == ConnectionState.Open)
connC6.Close();
}
}
}
else
DataTrim.Text = "Selezionare PERIODICO per impostare la data";
}
private void txtCodiceFiscale_TextChanged(object sender, EventArgs e) {
foreach (DataGridViewRow dgvr in dgvExistingCFs.Rows) {
string CF = dgvr.Cells["ExistingCodiceFiscale"].Value.ToString();
dgvr.Visible = CF.StartsWith(txtCodiceFiscale.Text, StringComparison.OrdinalIgnoreCase);
}
}
private void btnLoad_Click(object sender, EventArgs e) {
schema = cmbReportType.SelectedIndex == 0 ? "C6Mart" : "C6MartPeriodico";
LoadCFs();
}
private void dgvExistingCFs_Scroll(object sender, ScrollEventArgs e) {
ScrollGVBar(dgvToDelete, e);
}
private void dgvToDelete_Scroll(object sender, ScrollEventArgs e) {
ScrollGVBar(dgvExistingCFs, e);
}
private void btnAdd_Click(object sender, EventArgs e) {
MoveItems(dgvExistingCFs, dgvToDelete);
}
private void btnRemove_Click(object sender, EventArgs e) {
MoveItems(dgvToDelete, dgvExistingCFs);
}
private void btnExecute_Click(object sender, EventArgs e) {
if (dgvToDelete.Rows.Count > 0)
if (MessageBox.Show("Vuoi veramente eliminare i report da SeiReport e da StampeCentralizzate?", "C6 Contracts Manager", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
StringBuilder idReports = new StringBuilder();
foreach (DataGridViewRow dgvr in dgvToDelete.Rows)
idReports.AppendFormat("{0},", dgvr.Cells["ToDeleteIdReport"].Value.ToString());
idReports.Remove(idReports.Length - 1, 1);
using (SqlConnection connRM = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerConnection"].ConnectionString), connC6 = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerStampeC6Connection"].ConnectionString)) {
//V Cancellazione dei metadati
//SqlCommand cmdMeta = new SqlCommand("DELETE FROM RepositoryMetadati WHERE repository=1 and IdinRepository IN (" + idReports.ToString() + ")");
SqlCommand cmdRM = new SqlCommand("DELETE FROM C6ReportFisico WHERE Identificativo IN (" + idReports.ToString() + ")", connRM);
SqlCommand cmdC6 = new SqlCommand("SELECT Cod_Fiscale FROM C6Mart.CONTRATTOSEI C INNER JOIN " + schema + ".GESTIONE_PDF_FTP GPF ON C.Rete = GPF.Rete AND C.Cod_Fiscale = GPF.CodiceFiscale WHERE GPF.IdReport IN (" + idReports.ToString() + ")", connC6);
SqlDataReader readerC6 = null;
int cancC6ReportFisico = 0, cancLogFilePdf = 0, cancGestionePdfFtp = 0, aggContrattoSei = 0, cancMeta = 0;
StringBuilder CFs = new StringBuilder();
try {
connRM.Open();
cmdRM.Transaction = connRM.BeginTransaction(IsolationLevel.Serializable);
cancC6ReportFisico = cmdRM.ExecuteNonQuery();
cmdRM.CommandText = "DELETE FROM LogFilePdf WHERE Applicativo = 'C6' AND Tipo = " + (schema.Equals("C6Mart") ? "1" : "2") + " AND IDImage IN (" + idReports.ToString() + ")";
cancLogFilePdf = cmdRM.ExecuteNonQuery();
cmdRM.CommandText = "DELETE FROM RepositoryMetadati WHERE repository=1 and IdinRepository IN (" + idReports.ToString() + ")";
cancMeta = cmdRM.ExecuteNonQuery();
connC6.Open();
readerC6 = cmdC6.ExecuteReader();
if (readerC6.HasRows) {
while (readerC6.Read())
if (!CFs.ToString().Contains(readerC6.GetString(0)))
CFs.AppendFormat("'{0}',", readerC6.GetString(0));
CFs.Remove(CFs.Length - 1, 1);
}
readerC6.Close();
if (CFs.Length > 0) {
cmdC6.CommandText = "DELETE FROM " + schema + ".GESTIONE_PDF_FTP WHERE IdReport IN (" + idReports.ToString() + ")";
cmdC6.Transaction = connC6.BeginTransaction(IsolationLevel.Serializable);
cancGestionePdfFtp = cmdC6.ExecuteNonQuery();
if (schema.Equals("C6Mart"))
cmdC6.CommandText = "UPDATE C6Mart.CONTRATTOSEI SET InvioBenvenuto = 'N' WHERE InvioBenvenuto = 'S' AND Cod_Fiscale IN (" + CFs.ToString() + ")";
else
cmdC6.CommandText = "UPDATE C6Mart.CONTRATTOSEI SET Data_Invio_Diagnosi_Trimestrale = NULL, Data_Invio_Monitoraggio_Trimestrale = NULL WHERE Data_Invio_Monitoraggio_Trimestrale IS NOT NULL AND Cod_Fiscale IN (" + CFs.ToString() + ")";
aggContrattoSei = cmdC6.ExecuteNonQuery();
if (cancMeta > 0 && cancC6ReportFisico > 0 && cancLogFilePdf > 0 && cancGestionePdfFtp > 0 && aggContrattoSei > 0) {
StringBuilder testo = new StringBuilder("Verranno:\n\n");
testo.AppendFormat("- eliminati {0} record da SeiReport.C6ReportFisico\n", cancC6ReportFisico);
testo.AppendFormat("- eliminati {0} record da SeiReport.LogFilePDF\n", cancLogFilePdf);
testo.AppendFormat("- eliminati {0} record da SeiReport.Metadati\n", cancMeta);
testo.AppendFormat("- eliminati {0} record da " + schema + ".GESTIONE_PDF_FTP\n", cancGestionePdfFtp);
testo.AppendFormat("- aggiornati {0} record in C6Mart.CONTRATTOSEI\n", aggContrattoSei);
testo.Append("\nContinuare?");
if (MessageBox.Show(testo.ToString(), "C6 Contracts Manager", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
cmdRM.Transaction.Commit();
cmdC6.Transaction.Commit();
MessageBox.Show("Operazione eseguita con successo.", "C6 Contracts Manager", MessageBoxButtons.OK, MessageBoxIcon.Information);
dgvToDelete.Rows.Clear();
}
else {
cmdRM.Transaction.Rollback();
cmdC6.Transaction.Rollback();
MessageBox.Show("Operazione annullata.", "C6 Contracts Manager", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else {
cmdRM.Transaction.Rollback();
cmdC6.Transaction.Rollback();
MessageBox.Show("I record da eliminare ed aggiornare non coincidono, operazione annullata.", "C6 Contracts Manager", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else {
cmdRM.Transaction.Rollback();
MessageBox.Show("Impossibile recuperare i codici fiscali dei clienti interessati, operazione annullata.", "C6 Contracts Manager", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex) {
if (cmdRM.Transaction != null)
cmdRM.Transaction.Rollback();
if (cmdC6.Transaction != null)
cmdC6.Transaction.Rollback();
MessageBox.Show(ex.Message, "C6 Contracts Manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
if (!readerC6.IsClosed)
readerC6.Close();
cmdRM.Dispose();
cmdC6.Dispose();
if (connRM.State == ConnectionState.Open)
connRM.Close();
if (connC6.State == ConnectionState.Open)
connC6.Close();
}
}
}
}
}
}