Beispiel #1
0
        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();
        }
Beispiel #2
0
        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;
        }
Beispiel #3
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();
        }
Beispiel #4
0
        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!");
            }
        }
Beispiel #5
0
        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();
        }
Beispiel #6
0
        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 = "";
        }
Beispiel #7
0
        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 ");
            }
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        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();
        }
Beispiel #10
0
        //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();
        }
Beispiel #11
0
        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();
        }
Beispiel #12
0
        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");
        }
Beispiel #13
0
        //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");
        }
Beispiel #14
0
        //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");
        }
Beispiel #15
0
        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");
        }