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