private void dgv_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
MySqlCommand cmd;
string queryId = "SELECT Id FROM TbCommesse WHERE NomeCommessa = @name";
cmd = new MySqlCommand();
cmd.CommandText = queryId;
cmd.Parameters.AddWithValue("@name", dgv[0, e.RowIndex].Value);
cmd.Connection = ProGestDatabase.OpenConnection();
var idcom = cmd.ExecuteScalar();
string query = "UPDATE TbOre SET IdCommessa=@IdCom,Ore=@ore,Attivita=@attivita WHERE Id=@Id";
cmd = new MySqlCommand();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@Id", Convert.ToInt16(dgv[3, e.RowIndex].Value));
cmd.Parameters.AddWithValue("@IdCom", Convert.ToInt16(idcom));
cmd.Parameters.AddWithValue("@ore", dgv[1, e.RowIndex].Value);
cmd.Parameters.AddWithValue("@attivita", dgv[2, e.RowIndex].Value);
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.ExecuteNonQuery();
settotal();
}
public AdminForm(string NomeAdmin)
{
InitializeComponent();
labelusername.Text = NomeAdmin;
MySqlCommand cmd = new MySqlCommand();
string query = "SELECT DISTINCT Nome FROM TbUtenti ";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
checkedListDisegnatore.Items.Add(dr.GetString(0));
}
cmd.Connection.Close();
cmd = new MySqlCommand();
query = "SELECT DISTINCT NomeCommessa FROM TbCommesse order by NomeCommessa";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboCommesse.Items.Add(dr.GetString(0));
comboRiepilogoCommessa.Items.Add(dr.GetString(0));
}
cmd.Connection.Close();
comboCommesse.SelectedIndex = 0;
comboRiepilogoCommessa.SelectedIndex = 0;
}
private void BtnChiudi_Click(object sender, EventArgs e)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
string query = "SELECT Id FROM TbCommesse WHERE NomeCommessa=@nome AND Chiusa!=2 ";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@nome", comboCommesseAperte.SelectedItem.ToString());
int id;
if (cmd.ExecuteScalar() != null)
{
id = (int)cmd.ExecuteScalar();
query = "UPDATE TbCommesse SET Chiusa=1 WHERE Id=@idcommessa ";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idcommessa", id);
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("Commessa chiusa con successo");
}
else
{
MessageBox.Show("Errore Chiama il tecnico ");
}
}
else
{
MessageBox.Show("non puoi modificare questa commessa");
}
AggiornaCombo();
cmd.Connection.Close();
}
private void button1_Click(object sender, EventArgs e)
{
this.Hide();
string query = "SELECT Nome,Diritti FROM TbUtenti WHERE Username = @userutente AND Password = @password";
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@userutente", TxtLogin.Text);
cmd.Parameters.AddWithValue("@password", TxtPassword.Text);
MySqlDataReader dr = cmd.ExecuteReader();
bool found = false;
while (dr.Read())
{
found = true;
string nome = dr.GetString(0);
int diritti = dr.GetInt32(1);
if (diritti == 0)
{
Anteprima ante = new Anteprima(nome);
ante.Show();
}
else
{
AdminForm admin = new AdminForm(nome);
admin.Show();
}
}
if (!found)
{
MessageBox.Show("Username o password sbagliati!");
}
}
public Inserimento(string nome, DateTime date)
{
TimeSpan time = new TimeSpan(0, 0, 0);
date = date.Date + time;
date.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss");
InitializeComponent();
labelNome.Text = nome;
dateTimePicker1.Value = date;
MySqlConnection con = ProGestDatabase.OpenConnection();
string query = "SELECT DISTINCT NomeCommessa FROM TbCommesse WHERE Chiusa=0 or Chiusa=2 order by NomeCommessa ";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, con);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
List <string> commesse = new List <string>();
foreach (DataRow r in dt.Rows)
{
commesse.Add(r[0].ToString());
}
DataGridViewComboBoxColumn col = ((DataGridViewComboBoxColumn)dgv.Columns[0]);
col.DataSource = dt;
col.ValueMember = "NomeCommessa";
string query2 = "SELECT NomeCommessa,Ore,Attivita,TbOre.Id FROM (TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.ID)INNER JOIN TbUtenti " +
"on TbOre.IdUtente=TbUtenti.ID WHERE TbOre.Data= '" + date.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss") + "' AND TbUtenti.Nome='" + nome + "'";
dataAdapter = new MySqlDataAdapter(query2, con);
dt = new DataTable();
BindingSource bs = new BindingSource();
dataAdapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
int i = 0;
while (row[0].ToString() != commesse[i])
{
i++;
}
DataGridViewRow dgvrow = (DataGridViewRow)dgv.Rows[0].Clone();
((DataGridViewComboBoxCell)dgvrow.Cells[0]).DataSource = col.DataSource;
dgvrow.Cells[0].Value = row[0].ToString();
dgvrow.Cells[1].Value = row[1].ToString();
dgvrow.Cells[2].Value = row[2].ToString();
dgvrow.Cells[3].Value = row[3].ToString();
dgv.Rows.Add(dgvrow);
}
settotal();
}
private void AggiornaCombo()
{
MySqlCommand cmd = new MySqlCommand();
cmd = new MySqlCommand();
string query = "SELECT DISTINCT NomeCommessa FROM TbCommesse WHERE Chiusa=1 order by NomeCommessa ";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
MySqlDataReader dr = cmd.ExecuteReader();
comboCommesseChiuse.Items.Clear();
while (dr.Read())
{
comboCommesseChiuse.Items.Add(dr.GetString(0));
}
cmd.Connection.Close();
comboCommesseChiuse.SelectedIndex = 0;
query = "SELECT DISTINCT NomeCommessa FROM TbCommesse WHERE Chiusa=0 order by NomeCommessa";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
dr = cmd.ExecuteReader();
comboCommesseAperte.Items.Clear();
while (dr.Read())
{
comboCommesseAperte.Items.Add(dr.GetString(0));
}
cmd.Connection.Close();
comboCommesseAperte.SelectedIndex = 0;
cmd = new MySqlCommand();
query = "SELECT DISTINCT NomeCommessa FROM TbCommesse order by NomeCommessa";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboCommesse.Items.Add(dr.GetString(0));
}
cmd.Connection.Close();
comboCommesse.SelectedIndex = 0;
txt_AnnoCommessa.Text = "";
txt_NomeCommessa.Text = "";
}
private void BtnAddRow_Click(object sender, EventArgs e)
{
int result = -1;
try
{
foreach (DataGridViewRow row in dgv.Rows)
{
if (row.Cells[0].Value != null && row.Cells[1].Value != null && row.Cells[3].Value == null)
{
MySqlCommand cmd;
string queryId = "SELECT Id FROM TbUtenti WHERE Nome = @name";
cmd = new MySqlCommand();
cmd.CommandText = queryId;
cmd.Parameters.AddWithValue("@name", labelNome.Text);
cmd.Connection = ProGestDatabase.OpenConnection();
var idUte = cmd.ExecuteScalar();
cmd = new MySqlCommand();
string queryIdCom = "SELECT Id FROM TbCommesse WHERE NomeCommessa = @name";
cmd = new MySqlCommand();
cmd.CommandText = queryIdCom;
cmd.Parameters.AddWithValue("@name", dgv[0, row.Index].Value);
cmd.Connection = ProGestDatabase.OpenConnection();
var idcom = cmd.ExecuteScalar();
string query = "INSERT INTO TbOre(Ore , Attivita ,Data ,IdUtente ,IdCommessa) VALUES(@ore, @attivita,@data,@idutente,@idcommessa)";
cmd = new MySqlCommand();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idutente", Convert.ToInt16(idUte));
cmd.Parameters.AddWithValue("@idcommessa", Convert.ToInt16(idcom));
cmd.Parameters.AddWithValue("@data", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@ore", dgv[1, row.Index].Value);
cmd.Parameters.AddWithValue("@attivita", dgv[2, row.Index].Value);
cmd.Connection = ProGestDatabase.OpenConnection();
result = cmd.ExecuteNonQuery();
}
}
if (result > 0)
{
MessageBox.Show("Inserimento Eseguito ");
}
//this.Close();
}
catch
{
MessageBox.Show("Controlla di aver compilato la colona delle ore ");
}
}
public Anteprima(String Username)
{
InitializeComponent();
name = Username;
labelusername.Text = name;
MySqlConnection con = ProGestDatabase.OpenConnection();
string query = "SELECT DISTINCT NomeCommessa FROM (TbCommesse INNER JOIN TbOre on TbCommesse.Id = TbOre.IdCommessa) " +
" INNER JOIN TbUtenti on TbOre.IdUtente = TbUtenti.Id" +
" WHERE TbUtenti.Nome ='" + name + "'";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, con);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
}
private void BtnElimina_Click(object sender, EventArgs e)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
foreach (DataGridViewRow row in dgv.SelectedRows)
{
int id = int.Parse(row.Cells[3].Value.ToString());
string query = "DELETE FROM TbOre WHERE Id='" + id + "';";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
MessageBox.Show("Data Deleted");
//this.Close();
}
//Add Commessa
private void btn_AddCommessa_Click(object sender, EventArgs e)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
var confirmResult = MessageBox.Show("Controlla di aver ben scritto il nome ", "Confermi ?", MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
String query = "SELECT NomeCommessa FROM TbCommesse WHERE NomeCommessa=@nome";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@nome", txt_NomeCommessa.Text);
if (cmd.ExecuteScalar() != null)
{
MessageBox.Show("Errore è già presente la commessa che stai cercando di inserire");
}
else
{
query = "INSERT INTO TbCommesse(NomeCommessa ,Anno,Chiusa) VALUES(@nome , @anno , @chiusa)";
cmd.CommandText = query;
//cmd.Parameters.AddWithValue("@nome", txt_NomeCommessa.Text);
cmd.Parameters.AddWithValue("@anno", Convert.ToInt16(txt_AnnoCommessa.Text));
cmd.Parameters.AddWithValue("@chiusa", Convert.ToInt16(0));
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("inserimento effetuato");
}
else
{
MessageBox.Show("Errore verifica di aver ben scritto il nome");
}
}
}
else
{
// If 'No', do something here.
}
AggiornaCombo();
cmd.Connection.Close();
}
private void BtnCancella_Click(object sender, EventArgs e)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
string query = "Delete FROM TbCommesse WHERE NomeCommessa=@nome";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@nome", comboCommesse.SelectedItem.ToString());
if (cmd.ExecuteNonQuery() != -1)
{
MessageBox.Show("Commessa cancellata con successo");
}
else
{
MessageBox.Show("problema !!!");
}
AggiornaCombo();
cmd.Connection.Close();
}
private void GeneraRiepilogoCommessa(string commessa)
{
ExcelPackage pack = new ExcelPackage(new FileInfo("templatecommessamensile.xlsx"));
ExcelWorksheet sheet = pack.Workbook.Worksheets[1];
MySqlCommand cmd = new MySqlCommand();
// tutti i disegnatori che hanno lavorato in quella commessa
string query = "SELECT DISTINCT Nome FROM TbOre inner join TbUtenti on TbOre.IdUtente = TbUtenti.Id inner join TbCommesse on TbOre.IdCommessa = TbCommesse.Id " +
"where NomeCommessa = '" + commessa + "'";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
MySqlDataReader dr = cmd.ExecuteReader();
int contautenti = 0;
while (dr.Read())
{
sheet.Cells[5, 2 * (contautenti + 1)].Value = dr.GetString(0);
contautenti++;
}
cmd.Connection.Close();
int contarighe = 0;
cmd = new MySqlCommand();
query = "SELECT Ore,Data as DataInserimento ,Attivita ,TbUtenti.Nome FROM TbOre inner join TbUtenti on TbOre.IdUtente = TbUtenti.Id inner join TbCommesse on TbOre.IdCommessa = TbCommesse.Id " +
"where NomeCommessa = '" + commessa + "' order by DataInserimento ";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
dr = cmd.ExecuteReader();
string giornomese;
while (dr.Read())
{
giornomese = dr.GetDateTime(1).ToString("dd/M/yyyy", CultureInfo.InvariantCulture);
if (giornomese == sheet.Cells[5 + contarighe, 1].Value.ToString())
{
contarighe--;
sheet.Cells[6 + contarighe, 1].Value = giornomese;
int countNumUtenti = 0;
while (countNumUtenti < contautenti)
{
if (dr.GetString(3) == sheet.Cells[5, 2 * (countNumUtenti + 1)].Value.ToString())
{
sheet.Cells[6 + contarighe, (countNumUtenti + 1) * 2].Value = dr.GetDouble(0);
try { sheet.Cells[6 + contarighe, (countNumUtenti + 1) * 2 + 1].Value = dr.GetString(2); }
catch { }
}
countNumUtenti++;
}
}
else
{
sheet.Cells[6 + contarighe, 1].Value = giornomese;
int countNumUtenti = 0;
while (countNumUtenti < contautenti)
{
if (dr.GetString(3) == sheet.Cells[5, 2 * (countNumUtenti + 1)].Value.ToString())
{
sheet.Cells[6 + contarighe, (countNumUtenti + 1) * 2].Value = dr.GetDouble(0);
try { sheet.Cells[6 + contarighe, (countNumUtenti + 1) * 2 + 1].Value = dr.GetString(2); }
catch { }
}
countNumUtenti++;
}
}
contarighe++;
}
cmd.Connection.Close();
double somma = 0;
double totale = 0;
for (int i = 1; i <= contautenti; i++)
{
somma = 0;
for (int j = 0; j < contarighe; j++)
{
if (sheet.Cells[6 + j, i * 2].Value != null)
{
somma += (double)sheet.Cells[6 + j, i * 2].Value;
}
}
totale += somma;
sheet.Cells[6 + contarighe, i * 2].Value = somma;
}
sheet.Cells[3, 17].Value = totale;
sheet.Cells[3, 1].Value = commessa;
sheet.Cells[3, 8].Value = "Tutto";
//formattazione
sheet.Cells[6, 1, 6 + contarighe, contautenti * 2 + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
for (int i = 1; i <= contarighe; i++)
{
sheet.Cells[i + 5, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[i + 5, 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
for (int j = 0; j < 8; j++)
{
sheet.Cells[i + 5, (j + 1) * 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[i + 5, (j + 1) * 2 + 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i + 5, (j + 1) * 2 + 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[6 + contarighe, (j + 1) * 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
sheet.Cells[6, (j + 1) * 2 + 1, 6 + contarighe, (j + 1) * 2 + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
}
sheet.Cells[6 + contarighe, 1, 6 + contarighe, contautenti * 2 + 1].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[6, 1, 6 + contarighe, contautenti * 2 + 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
pack.SaveAs(new FileInfo(@"C:\Report\" + commessa + ".xlsx"));
MessageBox.Show("Report" + commessa + ".xlsx " + " Generato al percorso C:\\Report");
pack.Dispose();
System.Diagnostics.Process.Start(@"C:\Report\" + commessa + ".xlsx");
}
//riempilogo annuale
public void GeneraAnnuale(string utente)
{
MySqlCommand cmd = new MySqlCommand();
string queryId = "SELECT Id FROM TbUtenti WHERE Nome = @name";
cmd = new MySqlCommand();
cmd.CommandText = queryId;
cmd.Parameters.AddWithValue("@name", utente);
cmd.Connection = ProGestDatabase.OpenConnection();
var idUte = cmd.ExecuteScalar();
cmd.Connection.Close();
// la mia query
/*SELECT IdCommessa,NomeCommessa ,MONTH(Data) as Mese ,sum(ore) as totaleOre FROM TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.Id
* WHERE IdUtente = 2 AND YEAR(Data) = 2019 AND Chiusa<2 group by Mese ,NomeCommessa ORDER BY NomeCommessa*/
string query = "SELECT IdCommessa,NomeCommessa ,MONTH(Data) as Mese ,sum(ore) as totaleOre FROM TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.Id" +
" WHERE IdUtente = @idutente AND YEAR(Data) = @anno AND Chiusa<2 group by Mese ,NomeCommessa ORDER BY NomeCommessa";
cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idutente", (int)(idUte));
cmd.Parameters.AddWithValue("@anno", dateTimeAnno.Value.Year);
MySqlDataReader dr = cmd.ExecuteReader();
List <RigaOre> righe = new List <RigaOre>();
while (dr.Read())
{
int id = dr.GetInt32(0);
string NomCom = dr.GetString(1);;
int mese = dr.GetInt32(2);
double totOre = dr.GetDouble(3);
RigaOre riga = new RigaOre(NomCom, mese, totOre);
righe.Add(riga);
}
cmd.Connection.Close();
ExcelPackage pack = new ExcelPackage(new System.IO.FileInfo("templateannualedisegnatore.xlsx"));
ExcelWorksheet sheet = pack.Workbook.Worksheets[1];
sheet.Cells[1, 4].Value = utente;
sheet.Cells[2, 4].Value = dateTimeMese.Value.Year;
int puntatoreriga = 5;
string prevnomecom = "a";
foreach (RigaOre riga in righe)
{
string nomeCommessa = riga.NomeCommessa;
double totalOre = riga.ToTotaleOre;
int mese = riga.Mese;
if (prevnomecom != nomeCommessa)
{
prevnomecom = nomeCommessa;
sheet.Cells[puntatoreriga, 1].Value = nomeCommessa;
sheet.Cells[puntatoreriga, mese + 1].Value = totalOre;
puntatoreriga++;
}
else
{
sheet.Cells[puntatoreriga - 1, mese + 1].Value = totalOre;
}
}
puntatoreriga++;
int puntatore_seconda_parte = puntatoreriga;
// parte formazione , riunioni , ferie , mallatie.....
query = "SELECT IdCommessa,NomeCommessa ,MONTH(Data) as Mese ,sum(ore) as totaleOre FROM TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.Id" +
" WHERE IdUtente = @idutente AND YEAR(Data) = @anno AND Chiusa=2 group by Mese ,NomeCommessa ORDER BY NomeCommessa";
cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idutente", (int)(idUte));
cmd.Parameters.AddWithValue("@anno", dateTimeMese.Value.Year);
dr = cmd.ExecuteReader();
righe = new List <RigaOre>();
while (dr.Read())
{
int id = dr.GetInt32(0);
string NomCom = dr.GetString(1);;
int mese = dr.GetInt32(2);
double totOre = dr.GetDouble(3);
RigaOre riga = new RigaOre(NomCom, mese, totOre);
righe.Add(riga);
}
cmd.Connection.Close();
prevnomecom = "a";
foreach (RigaOre riga in righe)
{
string nomeCommessa = riga.NomeCommessa;
double totalOre = riga.ToTotaleOre;
int mese = riga.Mese;
if (prevnomecom != nomeCommessa)
{
prevnomecom = nomeCommessa;
sheet.Cells[puntatoreriga, 1].Value = nomeCommessa;
sheet.Cells[puntatoreriga, mese + 1].Value = totalOre;
puntatoreriga++;
}
else
{
sheet.Cells[puntatoreriga - 1, mese + 1].Value = totalOre;
}
}
List <string> commesseSpeciale = new List <string>();
for (int i = puntatore_seconda_parte; i < puntatoreriga; i++)
{
commesseSpeciale.Add(sheet.Cells[i, 1].Value.ToString());
}
List <string> comspe = new List <string> {
"RIUNIONI", "FORMAZIONE", "IT", "FERIE", "MALATTIA"
};
foreach (string com in comspe)
{
if (!commesseSpeciale.Contains(com))
{
sheet.Cells[puntatoreriga, 1].Value = com;
puntatoreriga++;
}
}
//totali
double somma;
for (int i = 5; i < puntatoreriga; i++)
{
somma = 0;
for (int j = 2; j < 14; j++)
{
if (sheet.Cells[i, j].Value != null)
{
somma += (double)sheet.Cells[i, j].Value;
}
}
sheet.Cells[i, 14].Value = somma;
}
sheet.Cells[puntatoreriga, 1].Value = "TOTALE";
for (int i = 2; i < 14; i++)
{
somma = 0;
for (int j = 5; j < puntatoreriga; j++)
{
if (sheet.Cells[j, i].Value != null)
{
somma += (double)sheet.Cells[j, i].Value;
}
}
sheet.Cells[puntatoreriga, i].Value = somma;
}
somma = 0;
for (int i = 2; i < 14; i++)
{
if (sheet.Cells[puntatoreriga, i].Value != null)
{
somma += (double)sheet.Cells[puntatoreriga, i].Value;
}
}
sheet.Cells[puntatoreriga, 14].Value = somma;
//formattazione
sheet.Column(1).AutoFit();
for (int i = 5; i < puntatoreriga; i++)
{
for (int j = 2; j <= 13; j++)
{
sheet.Cells[i, j].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
sheet.Cells[i, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
sheet.Cells[i, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
sheet.Cells[i, 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i, 1].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[i, 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[i, 14].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i, 14].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i, 14].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
for (int i = 2; i <= 14; i++)
{
sheet.Cells[puntatoreriga, i].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, i].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, i].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, i].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
sheet.Cells[puntatoreriga, 1].Style.Font.Bold = true;
sheet.Cells[puntatoreriga, 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 1].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 1].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 14].Style.Font.Bold = true;
//save and exit
pack.SaveAs(new System.IO.FileInfo(@"C:\Report\" + utente + "_" + dateTimeMese.Value.Year + ".xlsx"));
MessageBox.Show("Report " + utente + "_" + dateTimeMese.Value.Year + ".xlsx" + " Generato al percorso C:\\Report");
pack.Dispose();
System.Diagnostics.Process.Start(@"C:\Report\" + utente + "_" + dateTimeMese.Value.Year + ".xlsx");
}
//Riempilogo mensile
public void GeneraMensile(string utente)
{
ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(@"\\PRO-SERVER\dis\PROGIM\Gestione Ore\ORE MENSILI.xlsx"));
MySqlCommand cmd = new MySqlCommand();
string queryId = "SELECT Id FROM TbUtenti WHERE Nome = @name";
cmd = new MySqlCommand();
cmd.CommandText = queryId;
cmd.Parameters.AddWithValue("@name", utente);
cmd.Connection = ProGestDatabase.OpenConnection();
var idUte = cmd.ExecuteScalar();
cmd.Connection.Close();
// da qui controllare --------------------
string query = "SELECT * FROM TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.Id " +
" WHERE IdUtente = @idutente AND MONTH(Data) = @mese AND YEAR(Data) = @anno AND Chiusa<2 ORDER BY IdCommessa";
cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idutente", (int)(idUte));
cmd.Parameters.AddWithValue("@anno", dateTimeMese.Value.Year);
cmd.Parameters.AddWithValue("@mese", dateTimeMese.Value.Month);
List <RigaOre> righe = new List <RigaOre>();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = dr.GetInt32(0);
double ore = dr.GetDouble(1);
string attivita;
try { attivita = dr.GetString(2); }
catch { attivita = null; }
DateTime data = dr.GetDateTime(3);
int idut = dr.GetInt32(4);
int idcom = dr.GetInt32(5);
RigaOre riga = new RigaOre(id, ore, attivita, data, idut, idcom);
righe.Add(riga);
}
cmd.Connection.Close();
ExcelPackage pack = new ExcelPackage(new System.IO.FileInfo("templatemensiledisegnatore.xlsx"));
ExcelWorksheet sheet = pack.Workbook.Worksheets[1];
sheet.Cells[1, 5, 1, 17].Value = utente;
sheet.Cells[2, 5, 2, 17].Value = dateTimeMese.Value.Month + "/" + dateTimeMese.Value.Year;
string prevnomecom = "a";
int puntatoreriga = 5;
foreach (RigaOre riga in righe)
{
cmd = new MySqlCommand();
string queryIdCom = "SELECT NomeCommessa FROM TbCommesse WHERE ID ='" + riga.IdCommessa + "'";
cmd = new MySqlCommand();
cmd.CommandText = queryIdCom;
cmd.Connection = ProGestDatabase.OpenConnection();
string nomeCommessa = (string)(cmd.ExecuteScalar());
cmd.Connection.Close();
if (prevnomecom != nomeCommessa)
{
prevnomecom = nomeCommessa;
sheet.Cells[puntatoreriga, 1].Value = nomeCommessa;
sheet.Cells[puntatoreriga, riga.Data.Day + 1].Value = riga.Ore;
puntatoreriga++;
}
else
{
if (sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value != null)
{
sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value = riga.Ore;
}
else
{
//sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value = (double)sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value + riga.Ore;
sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value = riga.Ore;
}
}
}
puntatoreriga++;
query = "SELECT * FROM TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.Id WHERE IdUtente = @idutente AND MONTH(Data) = @mese AND YEAR(Data) = @anno AND Chiusa=2 ORDER BY IdCommessa";
cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idutente", (int)(idUte));
cmd.Parameters.AddWithValue("@anno", dateTimeMese.Value.Year);
cmd.Parameters.AddWithValue("@mese", dateTimeMese.Value.Month);
righe = new List <RigaOre>();
dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = dr.GetInt32(0);
double ore = dr.GetDouble(1);
string attivita;
try { attivita = dr.GetString(2); }
catch { attivita = null; }
DateTime data = dr.GetDateTime(3);
int idut = dr.GetInt32(4);
int idcom = dr.GetInt32(5);
RigaOre riga = new RigaOre(id, ore, attivita, data, idut, idcom);
righe.Add(riga);
}
cmd.Connection.Close();
prevnomecom = "a";
foreach (RigaOre riga in righe)
{
cmd = new MySqlCommand();
string queryIdCom = "SELECT NomeCommessa FROM TbCommesse WHERE ID ='" + riga.IdCommessa + "'";
cmd = new MySqlCommand();
cmd.CommandText = queryIdCom;
cmd.Connection = ProGestDatabase.OpenConnection();
string nomeCommessa = (string)(cmd.ExecuteScalar());
cmd.Connection.Close();
if (prevnomecom != nomeCommessa)
{
prevnomecom = nomeCommessa;
sheet.Cells[puntatoreriga, 1].Value = nomeCommessa;
sheet.Cells[puntatoreriga, riga.Data.Day + 1].Value = riga.Ore;
puntatoreriga++;
}
else
{
if (sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value != null)
{
sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value = riga.Ore;
}
else
{
sheet.Cells[puntatoreriga - 1, riga.Data.Day + 1].Value = riga.Ore;
}
}
}
query = "SELECT NomeCommessa FROM TbCommesse WHERE Chiusa=2 AND NomeCommessa <> ALL(SELECT NomeCommessa FROM TbOre INNER JOIN TbCommesse on TbOre.IdCommessa=TbCommesse.Id WHERE IdUtente = @idutente AND MONTH(Data) = @mese AND YEAR(Data) = @anno AND Chiusa=2)";
cmd = new MySqlCommand();
cmd.Connection = ProGestDatabase.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idutente", (int)(idUte));
cmd.Parameters.AddWithValue("@anno", dateTimeMese.Value.Year);
cmd.Parameters.AddWithValue("@mese", dateTimeMese.Value.Month);
righe = new List <RigaOre>();
dr = cmd.ExecuteReader();
while (dr.Read())
{
string nomecommessa = dr.GetString(0);
sheet.Cells[puntatoreriga, 1].Value = nomecommessa;
puntatoreriga++;
}
cmd.Connection.Close();
double somma;
for (int i = 5; i < puntatoreriga; i++)
{
somma = 0;
for (int j = 2; j < 32; j++)
{
if (sheet.Cells[i, j].Value != null)
{
somma += (double)sheet.Cells[i, j].Value;
}
}
sheet.Cells[i, 33].Value = somma;
}
sheet.Cells[puntatoreriga, 1].Value = "TOTALE";
for (int i = 2; i < 33; i++)
{
somma = 0;
for (int j = 5; j < puntatoreriga; j++)
{
if (sheet.Cells[j, i].Value != null)
{
somma += (double)sheet.Cells[j, i].Value;
}
}
sheet.Cells[puntatoreriga, i].Value = somma;
}
somma = 0;
for (int i = 2; i < 33; i++)
{
if (sheet.Cells[puntatoreriga, i].Value != null)
{
somma += (double)sheet.Cells[puntatoreriga, i].Value;
}
}
sheet.Cells[puntatoreriga, 33].Value = somma;
//formattazione
sheet.Column(1).AutoFit();
for (int i = 5; i < puntatoreriga; i++)
{
for (int j = 2; j <= 32; j++)
{
sheet.Cells[i, j].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
sheet.Cells[i, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
sheet.Cells[i, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
sheet.Cells[i, 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i, 1].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[i, 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[i, 33].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i, 33].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i, 33].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
for (int i = 2; i <= 33; i++)
{
sheet.Cells[puntatoreriga, i].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, i].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, i].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, i].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
sheet.Cells[puntatoreriga, 1].Style.Font.Bold = true;
sheet.Cells[puntatoreriga, 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 1].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 1].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[puntatoreriga, 33].Style.Font.Bold = true;
//save and exit
pack.SaveAs(new System.IO.FileInfo(@"C:\Report\" + utente + "_" + dateTimeMese.Value.Month + "-" + dateTimeMese.Value.Year + ".xlsx"));
MessageBox.Show("Report " + utente + "_" + dateTimeMese.Value.Month + "-" + dateTimeMese.Value.Year + ".xlsx" + " Generato al percorso C:\\Report");
pack.Dispose();
System.Diagnostics.Process.Start(@"C:\Report\" + utente + "_" + dateTimeMese.Value.Month + "-" + dateTimeMese.Value.Year + ".xlsx");
}
private void GeneraCommessa(string commessa, DateTime giorno)
{
ExcelPackage pack = new ExcelPackage(new FileInfo("templatecommessamensile.xlsx"));
ExcelWorksheet sheet = pack.Workbook.Worksheets[1];
MySqlCommand cmd = new MySqlCommand();
// tutti i disegnatori che hanno lavorato in quella commessa
string query = "SELECT DISTINCT Nome FROM TbOre inner join TbUtenti on TbOre.IdUtente = TbUtenti.Id inner join TbCommesse on TbOre.IdCommessa = TbCommesse.Id " +
"where NomeCommessa = '" + commessa + "'";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
MySqlDataReader dr = cmd.ExecuteReader();
int contautenti = 0;
while (dr.Read())
{
sheet.Cells[5, 2 * (contautenti + 1)].Value = dr.GetString(0);
contautenti++;
}
cmd.Connection.Close();
//////////////////////////////////////////////////////////////////////////////////////////////////////////////// quiiiii //////////////
DateTime giornomese = new DateTime(giorno.Year, giorno.Month, 1);
int contarighe = 0;
while (giornomese.Month == giorno.Month)
{
if (giornomese.DayOfWeek == DayOfWeek.Sunday || giornomese.DayOfWeek == DayOfWeek.Saturday)
{
sheet.Cells[6 + contarighe, 1, 6 + contarighe, 17].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
sheet.Cells[6 + contarighe, 1, 6 + contarighe, 17].Style.Fill.BackgroundColor.SetColor(Color.Gray);
}
sheet.Cells[6 + contarighe, 1].Value = giornomese.Day + "/" + giornomese.Month + "/" + giornomese.Year;
contarighe++;
giornomese = giornomese.AddDays(1);
}
for (int j = 1; j <= contautenti; j++)
{
cmd = new MySqlCommand();
query = "SELECT Ore,Data,Attivita FROM TbOre inner join TbUtenti on TbOre.IdUtente = TbUtenti.Id inner join TbCommesse on TbOre.IdCommessa = TbCommesse.Id " +
"where MONTH(TbOre.Data)= '" + giorno.Month + "' AND NomeCommessa = '" + commessa + "' AND Nome = '" + sheet.Cells[5, j *2].Value + "'";
cmd.CommandText = query;
cmd.Connection = ProGestDatabase.OpenConnection();
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (sheet.Cells[dr.GetDateTime(1).Day + 5, j * 2].Value == null)
{
sheet.Cells[dr.GetDateTime(1).Day + 5, j * 2].Value = dr.GetDouble(0);
}
else
{
sheet.Cells[dr.GetDateTime(1).Day + 5, j * 2].Value = (double)sheet.Cells[dr.GetDateTime(1).Day + 5, j * 2].Value + dr.GetDouble(0);
}
try { sheet.Cells[dr.GetDateTime(1).Day + 5, j * 2 + 1].Value = dr.GetString(2); }
catch { }
}
cmd.Connection.Close();
}
double somma = 0;
double totale = 0;
for (int i = 1; i <= contautenti; i++)
{
somma = 0;
for (int j = 0; j < contarighe; j++)
{
if (sheet.Cells[6 + j, i * 2].Value != null)
{
somma += (double)sheet.Cells[6 + j, i * 2].Value;
}
}
totale += somma;
sheet.Cells[6 + contarighe, i * 2].Value = somma;
}
sheet.Cells[3, 17].Value = totale;
sheet.Cells[3, 1].Value = commessa;
sheet.Cells[3, 8].Value = giorno.Month + "/" + giorno.Year;
//formattazione
sheet.Cells[6, 1, 6 + contarighe, contautenti * 2 + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
for (int i = 1; i <= contarighe; i++)
{
sheet.Cells[i + 5, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[i + 5, 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
for (int j = 0; j < 8; j++)
{
sheet.Cells[i + 5, (j + 1) * 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[i + 5, (j + 1) * 2 + 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[i + 5, (j + 1) * 2 + 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[6 + contarighe, (j + 1) * 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
sheet.Cells[6, (j + 1) * 2 + 1, 6 + contarighe, (j + 1) * 2 + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
}
sheet.Cells[6 + contarighe, 1, 6 + contarighe, contautenti * 2 + 1].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[6, 1, 6 + contarighe, contautenti * 2 + 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
pack.SaveAs(new FileInfo(@"C:\Report\" + commessa + "_" + giorno.Month + "-" + giorno.Year + ".xlsx"));
MessageBox.Show("Report" + commessa + "_" + giorno.Month + " - " + giorno.Year + ".xlsx " + " Generato al percorso C:\\Report");
pack.Dispose();
System.Diagnostics.Process.Start(@"C:\Report\" + commessa + "_" + giorno.Month + "-" + giorno.Year + ".xlsx");
}