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"); }