Windowssiamo Archivi - Petar Karan

17 Giugno 2022 Nessun commento

[c#] Semplice classe per gestire connessione e query su un database MySQL

Il nostro scopo è scrivere una semplice classe in C# che permetta di gestire l’inserimento e la lettura dei dati dal database. La caratteristica saliente che ci interessa in questo caso è l’accesso ai dati, in fase di lettura, tramite una List<ListDictionary>, che consente di trattare i risultati come una lista di dizionari.

La classe che vogliamo creare permetterà un utilizzo simile a questo:

using (MySQLdb mysql = new MySQLdb(@"server=localhost;userid=root;password=;database=esempio"))
{
    mysql.setQuery("INSERT INTO studenti(nome,cognome) VALUES(@nome,@cognome)")
        .addParameter("@nome","Gianpiero")
        .addParameter("@cognome","Franchi").In();

    List<ListDictionary> studenti = mysql.setQuery("SELECT * FROM studenti WHERE nome = @nome OR 1")
        .addParameter("@nome","Mario").Out();

    foreach(ListDictionary s in studenti)
    {
        Console.WriteLine(s["nome"]);
    }
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

using (MySQLdb mysql = new MySQLdb(@"server=localhost;userid=root;password=;database=esempio"))

{

mysql.setQuery("INSERT INTO studenti(nome,cognome) VALUES(@nome,@cognome)")

.addParameter("@nome","Gianpiero")

.addParameter("@cognome","Franchi").In();

List<ListDictionary> studenti = mysql.setQuery("SELECT * FROM studenti WHERE nome = @nome OR 1")

.addParameter("@nome","Mario").Out();

foreach(ListDictionary s in studenti)

{

Console.WriteLine(s["nome"]);

}

La classe sarà formata invece nel modo seguente:

namespace DatabaseMySQL
{
    public class MySQLdb : IDisposable
    {
        private bool isDisposed = false;
        private string connectionString { get; set; }
        private MySqlConnection connection;
        private MySqlCommand command;

        public MySQLdb(string connectionString)
        {
            this.connectionString = connectionString;

            this.connect();
        }

        ~MySQLdb()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected void Dispose(bool disposing)
        {
            if(!isDisposed && disposing)
            {
                if(this.command != null) this.command.Dispose();
                if (this.connection != null) this.connection.Close();
            }
            isDisposed = true;
        }

        private void connect()
        {
            this.connection = new MySqlConnection(this.connectionString);
            this.connection.Open();
        }

        public MySQLdb setQuery(string query)
        {
            this.command = new MySqlCommand(query, this.connection);
            return this;
        }

        public MySQLdb addParameter(string name, string value)
        {
            if (this.command != null)
            {
                this.command.Parameters.AddWithValue(name, value);
            }
            return this;
        }

        public List<ListDictionary> Out()
        {
            List<ListDictionary> risultato = new List<ListDictionary>();
            if (this.command != null)
            {
                this.command.Prepare();
                using (MySqlDataReader reader = this.command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ListDictionary dict = new ListDictionary();
                        for (int col = 0; col < reader.FieldCount; col++)
                        {
                            dict.Add(reader.GetName(col), reader.GetValue(col).ToString());
                        }
                        risultato.Add(dict);
                    }
                }
                this.command.Dispose();
            }
            return risultato;
        }

        public long In()
        {
            if (this.command != null)
            {
                this.command.Prepare();
                this.command.ExecuteNonQuery();
                long lastId = this.command.LastInsertedId;
                this.command.Dispose();
                return lastId;
            }
            return -1;
        }
    }
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

namespace DatabaseMySQL

{

public class MySQLdb : IDisposable

{

private bool isDisposed = false;

private string connectionString { get; set; }

private MySqlConnection connection;

private MySqlCommand command;

public MySQLdb(string connectionString)

{

this.connectionString = connectionString;

this.connect();

}

~MySQLdb()

{

Dispose(false);

}

public void Dispose()

{

Dispose(true);

GC.SuppressFinalize(this);

}

protected void Dispose(bool disposing)

{

if(!isDisposed && disposing)

{

if(this.command != null) this.command.Dispose();

if (this.connection != null) this.connection.Close();

}

isDisposed = true;

}

private void connect()

{

this.connection = new MySqlConnection(this.connectionString);

this.connection.Open();

}

public MySQLdb setQuery(string query)

{

this.command = new MySqlCommand(query, this.connection);

return this;

}

public MySQLdb addParameter(string name, string value)

{

if (this.command != null)

{

this.command.Parameters.AddWithValue(name, value);

}

return this;

}

public List<ListDictionary> Out()

{

List<ListDictionary> risultato = new List<ListDictionary>();

if (this.command != null)

{

this.command.Prepare();

using (MySqlDataReader reader = this.command.ExecuteReader())

{

while (reader.Read())

{

ListDictionary dict = new ListDictionary();

for (int col = 0; col < reader.FieldCount; col++)

{

dict.Add(reader.GetName(col), reader.GetValue(col).ToString());

}

risultato.Add(dict);

}

this.command.Dispose();

}

return risultato;

}

public long In()

{

if (this.command != null)

{

this.command.Prepare();

this.command.ExecuteNonQuery();

long lastId = this.command.LastInsertedId;

this.command.Dispose();

return lastId;

}

return -1;

}

Vedi articolo

10 Settembre 2021 Nessun commento

[word] Rimuovere “Limita modifica” da un file docx di Word

Problema: Il file word risulta protetto con l’opzione Limita modifica che impedisce anche di copiare il testo dal file.

Soluzione: Rinominare il file docx con estensione zip, decomprimere il file, individuare all’interno della cartella il file word/settings.xml, aprirlo con un editor di testo ed impostare su false il parametro w:enforcement, salvare il file, ricomprimere tutto il contenuto della cartella in un file zip e rinominarlo in docx

Vediamo la procedura nel dettaglio.

Anzitutto individuiamo il file Word che non permette le modifiche. Aprendo il file e andando nella scheda Revisione > Proteggi cliccando sull’opzione Limita modifica si aprirà una finestra simile alla seguente:

Il file potrebbe essere protetto a diversi livelli e non consentire nemmeno la copia del testo.

Per rimuovere la protezione procediamo nella maniera seguente.

A questo punto prendiamo il file e rinominiamolo con l’estensione zip.

Per farlo da Windows 10 dobbiamo attivare la visualizzazione delle estensioni dei file. Se non l’abbiamo già fatto, in Esplora Risorse andiamo su Visualizza > Mostra / Nascondi e spuntiamo Estensioni nomi file.

Una volta rinominato il file in formato zip possiamo decomprimerlo in una cartella, trovando dentro una struttura simile a questa:

Entriamo nella cartella word e individuiamo il file settings.xml

Apriamo il file con un editor di testo (consiglio Notepad++) e individuiamo il parametro w:enforcement

Il parametro sarà probabilmente impostato su true oppure 1.

Modifichiamolo su false.

A questo punto torniamo nella cartella precedente e comprimiamo tutti i file.

Otterremo un nuovo file zip, cambiamo di nuovo l’estensione in docx e il gioco è fatto.

Vedi articolo

26 Novembre 2020 Nessun commento

[windows] Installazione di Microsoft Exchange Mailbox server su Windows Server 2016

Per installare Exchange abbiamo bisogno di un Windows Server configurato come controller di dominio. Per tale configurazione rimando alla guida Configurazione Windows Server 2016 con Dominio, DNS e DHCP [per esordienti totali

1. Installazione di Exchange Server

Inseriamo il disco di installazione di Exchange Server 2016 che comparirà in Questo PC > Dispositivi e unità:

Accediamo al disco ed avviamo il Setup.

Si avvierà la richiesta di verifica degli aggiornamenti, nel mio caso li eseguirò semmai successivamente, selezionando dunque Non verificare la disponibilità di aggiornamenti adesso.

Premiamo Avanti. Si avvierà la copia dei file, attendiamo:

Nella schermata successiva scegliamo Avanti.

Accettiamo il Contratto di licenza.

Nella schermata successiva scegliamo Usa impostazioni consigliate:

Nella Scelta del ruolo del server selezioniamo Ruolo Cassette postali e Installa automaticamente ruoli e funzionalità di Windows Server necessari per installare Exchange Server.

Premiamo di nuovo avanti.

A questo punto selezioniamo la posizione di installazione di Exchange, possiamo lasciare quella predefinita e premere nuovamente Avanti.

Specifichiamo un nome per la nostra organizzazione Exchange, per esempio la Torregatti Spa. Premiamo di nuovo Avanti.

Alla richiesta se disabilitare la protezione antimalaware, diciamo di No e procediamo premendo Avanti.

Verranno avviati i controlli di conformità, attendiamo.

A questo punto è molto probabile che si presenti una serie di errori, che andranno risolti.

Per risolverli basterà seguire i link ed effettuare le installazioni suggerite.

Nel mio caso vengono richieste due installazioni specifiche, il cui:

Errore:
Questo computer richiede l'aggiornamento descritto nell'articolo della Microsoft Knowledge Base KB3206632 (http://support.microsoft.com/kb/3206632). Senza questo aggiornamento, Exchange Server 2016 non funzionerà in modo affidabile su Windows Server 2016.
Per altre informazioni, visitare il sito: http://technet.microsoft.com/library(EXCHG.150)/ms.exch.setupreadiness.Win2k16LSARollupUpdateNotInstalled.aspx

Errore:
Questo computer richiede Microsoft Unified Communications Managed API 4.0, Core Runtime a 64 bit. Installare il software dal sito http://go.microsoft.com/fwlink/?LinkId=260990.
Per altre informazioni, visitare il sito: http://technet.microsoft.com/library(EXCHG.150)/ms.exch.setupreadiness.UcmaRedistMsi.aspx

Alla fine dell’installazione potrebbe essere necessario riavviare. In tal caso riavviamo e poi riprendiamo l’installazione dall’inizio.

Se tutto è andato a dovere arriveremo nuovamente alla schermata precedente con solo un elenco di avvisi. A questo punto possiamo premere su Installa.

A questo punto si avvierà l’installazione vera e propria. Attendiamo.

Quando sarà tutto completato otterremo un risultato simile al seguente.

Riavviamo il sistema.

Una volta riavviato il server è probabile che dal Server Manager si vedano diversi servizi, principalmente quelli di Exchange, non avviati.

Selezioniamo i servizi (possiamo selezionarli anche tutti utilizzando SHIFT ed il mouse), clicchiamo con il destro e scegliamo Avvia Servizio.

2. Configurazioni cassette postali

Quando tutto è avviato possiamo procedere alla configurazione delle cassette postali.

Accediamo al Menù Start e cerchiamo Microsoft Exchange Server 2016 > Exchange Administrative Server

Si aprirà l’interfaccia amministrativa dentro il browser web (nel mio caso Google Chrome).

Inseriamo nome utente e password dell’amministratore del dominio e premiamo accedi.

Nella scheda Destinatari > Cassette postali possiamo configurare le caselle di posta. Ogni caselle può essere associata ad un utente del dominio, oppure può essere creata una casella a se stante. Clicchiamo sul tasto più e selezioniamo Cassetta postale utente.

Ricordiamoci che il nostro dominio è petarkaran.local, quindi le caselle postali saranno del tipo nome_utente@petarkaran.local. Si aprirà quindi la finestra di configurazione della cassetta postale:

Vogliamo creare la casella luisa.neri@petarkaran.local per l’utente di dominio luisa.neri. Clicchiamo su Sfoglia… per selezionare un utente del dominio.

Selezioniamo l’utente desiderato e premiamo su Salva.

In questo modo abbiamo creato la cassetta postale desiderata per l’utente selezionato.

Se volessimo aggiungere un altro dominio al server di posta, per esempio un dominio esterno accettato, selezioniamo flusso di posta > domini accettati.

Vogliamo aggiungere torregatti.com. Clicchiamo sul più e compiliamo la scheda nel modo seguente:

Premiamo su Salva.

Andiamo su flusso di posta > criteri degli indirizzi e-mail. Facciamo un doppio click su Default Policy. Andiamo su formato indirizzi posta elettronica.

Clicchiamo sul più. Selezioniamo il dominio appena creato, modifichiamo eventualmente il nome utente e premiamo su Salva.

Nella schermata principale, se lo stato della policy è su Non applicato, clicchiamo su Applica, poi premiamo su Sì.

In questo modo tutti i nostri destinatari di posta potranno ricevere le email anche sull’indirizzo nome_utente@torregatti.com.

Vedi articolo

23 Settembre 2020 Nessun commento

[PowerShell] Esempio di script per inviare un messaggio a diversi computer sulla rete tramite PowerShell

Ecco un esempio di script in PowerShell per inviare un messaggio a tutti i computer Windows all’interno di una rete.

Anzitutto creiamo un file invia_messaggio.ps1 nella posizione che preferiamo. Clicchiamo sopra col destro e scegliamo la voce Modifica per modificare il file con PowerShell ISE.

Inseriamo all’interno il seguente codice:

$computers = @()
$sessione = "*"
while( $true ) {
    Write-Host "-- menu opzioni --" -ForegroundColor Red
    Write-Host "0. Esci"
    Write-Host "1. Aggiungi computer"
    Write-Host "2. Invia messaggio"
    Write-Host "3. Reimposta computer"
    Write-Host "4. Mostra computer"
    $opzione = Read-Host "Scelta"
    if( $opzione -eq 0 ) {
        Write-Host "Ciao!"
        break
    }
    if( $opzione -eq 1 ) {
        $computers += Read-Host "Nome computer"
    }
    if( $opzione -eq 2 ) {
        $messaggio = Read-Host "Messaggio"
        foreach( $computer in $computers ) {
            msg $sessione /server:$computer $messaggio
        }
        Write-Host "Messaggio inviato" -ForegroundColor Green
    }
    if( $opzione -eq 3 ) {
        $computers.Clear()
        Write-Host "Elenco computer cancellato" -ForegroundColor Red
    }
    if( $opzione -eq 4 ) {
        Write-Host "Elenco computer" -ForegroundColor Yellow
        $computers
    }
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

$computers = @()

$sessione = "*"

while( $true ) {

Write-Host "-- menu opzioni --" -ForegroundColor Red

Write-Host "0. Esci"

Write-Host "1. Aggiungi computer"

Write-Host "2. Invia messaggio"

Write-Host "3. Reimposta computer"

Write-Host "4. Mostra computer"

$opzione = Read-Host "Scelta"

if( $opzione -eq 0 ) {

Write-Host "Ciao!"

break

}

if( $opzione -eq 1 ) {

$computers += Read-Host "Nome computer"

}

if( $opzione -eq 2 ) {

$messaggio = Read-Host "Messaggio"

foreach( $computer in $computers ) {

msg $sessione /server:$computer $messaggio

}

Write-Host "Messaggio inviato" -ForegroundColor Green

}

if( $opzione -eq 3 ) {

$computers.Clear()

Write-Host "Elenco computer cancellato" -ForegroundColor Red

}

if( $opzione -eq 4 ) {

Write-Host "Elenco computer" -ForegroundColor Yellow

$computers

}

Per inviare un messaggio sarà sufficiente aggiungere i nomi dei computer all’interno della rete.

Vedi articolo

1 Giugno 2020 Nessun commento

[dax] Creare una tabella temporanea con SUMMARIZE ed effettuarci sopra dei calcoli

In questo esempio utilizzerò i dati già usati per: Analizzare i dati da Excel e MySQL utilizzando Power BI (per esordienti totali)

Quello che vogliamo fare è calcolare, nella tabella agenti, la media delle vendite di tutti gli altri agenti eccetto quello corrente ed utilizzarla come obiettivo KPI.

Anzitutto vediamo come è composta la tabella vendite usando la seguente query:

SELECT `idAgente`, SUM(`importo`) FROM `vendite` GROUP BY `idAgente`

1	SELECT `idAgente`, SUM(`importo`) FROM `vendite` GROUP BY `idAgente`

I dati che avremo saranno i seguenti:

Ricordiamoci che nel nostro esempio la tabella agenti ha “solo” 6 agenti, fino all’id 6.

Se adesso volessimo calcolare, in SQL, la media delle vendite di tutti gli agenti, escluso quello corrente, per ciascun agente, dovremmo eseguire una query come la seguente:

SELECT 
	a.nome, a.cognome,
    (SELECT AVG(t.totale_importo) FROM (SELECT v.`idAgente`, SUM(v.`importo`) AS totale_importo FROM `vendite` v GROUP BY v.`idAgente`) t WHERE t.idAgente != a.id) AS obiettivo
FROM
	agenti a

1

2

3

4

5

SELECT

a.nome, a.cognome,

(SELECT AVG(t.totale_importo) FROM (SELECT v.`idAgente`, SUM(v.`importo`) AS totale_importo FROM `vendite` v GROUP BY v.`idAgente`) t WHERE t.idAgente != a.id) AS obiettivo

FROM

agenti a

Questo ci darebbe un risultato simile al seguente:

Per ottenere lo stesso risultato in DAX dovremmo creare una tabella temporanea delle vendite e poi effettuare su di essa il calcolo della media, nel modo seguente:

obiettivo = 
VAR tabella_provvisoria = SUMMARIZE( 
    'tommaso3 vendite' , 
    'tommaso3 vendite'[idAgente], 
    "importo_totale", SUM( 'tommaso3 vendite'[importo]) 
    ) 
RETURN AVERAGEX( 
    FILTER(tabella_provvisoria, 
    [idAgente] <> 'tommaso3 agenti'[id]) , 
    [importo_totale] 
    )

1

2

3

4

5

6

7

8

9

10

11

obiettivo =

VAR tabella_provvisoria = SUMMARIZE(

'tommaso3 vendite' ,

'tommaso3 vendite'[idAgente],

"importo_totale", SUM( 'tommaso3 vendite'[importo])

)

RETURN AVERAGEX(

FILTER(tabella_provvisoria,

[idAgente] <> 'tommaso3 agenti'[id]) ,

[importo_totale]

)

La tabella provvisoria fa le veci della prima query, sulla quale poi eseguiamo il calcolo con AVERAGEX.

Vedi articolo

26 Maggio 2020 1 commento

Analizzare i dati da Excel e MySQL utilizzando Power BI (per esordienti totali)

Procuriamoci anzitutto il necessario per poter lavorare, in questo esempio utilizzerò un database MySQL, un file Excel con ulteriori dati da integrare e Power BI.

In ordine avremo quindi bisogno di:

XAMPP per server MySQL e PHPMyAdmin (non è necessario utilizzare XAMPP, si può usare un qualunque database MySQL ovviamente)
Un database con dei dati di esempio che puoi scaricare da qui (questo database è già stato utilizzato nell’esercizio [mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali))
Un file excel con integrazioni dei dati per clienti e agenti che puoi scaricare da qui
Power BI che possiamo ottenere gratuitamente a questo link
MySQL Connector/NET 8.0.20 che si può scaricare da questo link (se non lo installiamo in anticipo sarà Power BI stesso ad invitarci di farlo nel momento in cui vorremo collegare un database MySQL)

Preparato tutto questo possiamo cominciare a lavorare con Power BI.

1. Importazione dati da MySQL

Avviamo Power BI e scegliamo Recupera dati dalla maschera iniziale.

Nel campo di ricerca cerchiamo mysql:

Selezioniamo il database MySQL. Se non abbiamo installato il connettore, come indicato prima, ci verrà chiesta l’installazione. In caso contrario procediamo configurando il database. Inseriamo, se stiamo usando XAMPP, come server 127.0.0.1 e il nome del database, nel mio caso powerbi.

Procediamo premendo OK ed impostiamo nome utente del database come root e lasciamo il campo password vuoto.

All’avviso sul supporto crittografia premiamo OK.

A questo punto selezioniamo tutte le tabelle che intendiamo caricare. Nel caso specifico selezioneremo tutte le tabelle eccetto storico_vendite.

Premiamo sul tasto carica.

2. Elaborazione dati

A questo punto elaboriamo le connessioni tra le tabelle. Sulla sinistra spostiamoci su modello:

Dovremmo vedere qualcosa di simile a questo.

Spostandoci su una qualsiasi delle relazioni notiamo come siano stati associati automaticamente gli ID. Purtroppo queste associazioni non vanno bene, perché i nomi delle colonne, tra le varie tabelle non corrispondono. A vendite.idAgente corrisponde infatti agenti.id, mentre secondo il sistema automatico vendite.id corrisponde a agenti.id. Possiamo ricostruire le associazioni trascinando i campi gli uni sugli altri, oppure andando ad intervenire da Gestisci relazioni, nella scheda Home > Relazioni.

Cliccando su Gestisci relazioni si aprirà una schermata come la seguente:

Modifichiamo opportunamente tutte le relazioni. Per esempio quella tra vendite ed agenti nel modo seguente:

Se abbiamo fatto tutto correttamente dovremmo vedere qualcosa di simile a questo:

Faccio notare che in tutti i casi ho impostato direzione filtro incrociato su Entrambi. L’utilizzo dell’opzione è finalizzato specificatamente alla semplificazione dell’uso dei filtri. La stessa Microsoft lo spiega nell’articolo Abilitare il filtro incrociato bidirezionale con DirectQuery in Power BI Desktop

Con il filtro incrociato bidirezionale, gli autori di report e modelli di dati hanno ora un maggiore controllo sulla modalità di applicazione dei filtri durante l’uso di tabelle correlate. Il filtro incrociato bidirezionale consente loro di applicare filtri su entrambi i lati di una relazione tra tabelle. Per ottenere questo risultato, il contesto di filtro viene propagato a una seconda tabella correlata sull’altro lato della relazione.

Fatto tutto questo lo schema che visualizzeremo sarà come il seguente:

3. Creiamo la nostra prima tabella nel report

Adesso mettiamo alla prova il sistema creato e proviamo a visualizzare il totale venduto dagli agenti.

Modifichiamo anzitutto il nome della pagina in Vendite:

Dalle visualizzazioni selezioniamo Tabella:

Poi afferriamo il nome dell’agente e trasciniamolo nella tabella. Stessa cosa per importo dalla tabella vendite:

Vedremo un risultato simile a questo:

Questo succede perché nelle vendite c’è un idAgente che non corrisponde ad alcun agente nella tabella agenti. Quello che vorremmo ottenere è una tabella di agenti LEFT JOIN vendite. Per farlo andiamo su Query e clicchiamo su Trasforma dati.

Clicchiamo col destro nel riquadro Query e selezioniamo Nuova query > Combina > Unisci query come nuova:

Configuriamo una LEFT OUTER JOIN nella maniera seguente:

A questo punto vedremo una tabella come la seguente:

Noi vogliamo visualizzare i dati aggregati delle vendite, quindi clicchiamo sul pulsante in alto a destra di powerbi vendite.

Selezioniamo l’opzione di aggregazione nella maniera seguente, scegliendo quantità e importo.

Dovremmo ottenere qualcosa di simile a questo:

Sulla destra clicchiamo sulla nuova tabella col destro e rinominiamola in vendite_x_agente. A titolo di esempio vogliamo anche aggiungere una colonna che calcoli l’importo medio di vendita per quantità di venduto.

Creiamo la colonna nella maniera seguente:

La formula che usiamo è: = Table.AddColumn(#"Colonne powerbi vendite aggregate", "media", each [Somma di powerbi vendite.importo] / [Somma di powerbi vendite.qta])

Fatto tutto questo salviamo e scegliamo di applicare i dati dal prompt che ci apparirà sullo schermo. Chiudiamo e torniamo a Power BI.

Sotto la voce Campi clicchiamo col destro su vendite_x_agente e aggiungiamo un’altra colonna (lo potevamo fare anche prima, ma per esercizio voglio farlo da questa posizione diversa): Nel campo formula digitiamo: nominativo = vendite_x_agente[nome]& " " & vendite_x_agente[cognome]

In questo modo abbiamo concatenato il nome e il cognome di ciascun agente aggiungendo in mezzo uno spazio.

A questo punto rinominiamo le colonne aggiunte prima, in modo da ottenere la seguente situazione:

Reimpostiamo la tabella nella maniera seguente:

4. Integriamo i dati da un foglio Excel

Adesso procediamo integrando i dati da un foglio Excel, per calcolare le provvigioni per ciascun agente.

Importiamo anzitutto i dati andando su Home > Dati > Recupera dati. Selezioniamo Excel. (c’è anche il collegamento veloce sotto Dati)

Selezioniamo il file excel anagrafica_clienti.xlsx.

Selezioniamo entrambe le tabelle e clicchiamo su Carica.

Andando su Modello vedremo che Power BI avrà collegato tutte le tabelle totalmente a caso.

Clicchiamo col destro su una delle due, poi andiamo su Gestisci relazioni, usando SHIFT e il mouse selezioniamo le relazioni che interessano Compensi agenti e Anagrafica Clienti (nella maniera mostrata nell’immagine) e clicchiamo su Elimina per tutte le relazioni selezionate (e create).

Ricreiamo le relazioni nella maniera seguente:

Fatto questo torniamo sulle tabelle e andiamo su vendite_x_agenti, dove abbiamo aggiunto prima i dati di vendita.

Clicchiamo col destro su vendite_x_agenti e selezioniamo Modifica query. Dalla finestra di modifica andiamo su Home > Combina > Merge query.

Impostiamo l’unione nella maniera seguente:

Clicchiamo sul pulsante in alto a destra della nuova colonna aggregata:

Selezioniamo Compenso e Fisso nella maniera seguente:

Salviamo il tutto e chiudiamo. Clicchiamo col destro sulla tabella e aggiungiamo una Nuova colonna. Inseriamo la seguente formula:

provvigione = vendite_x_agente[totale_importo]*vendite_x_agente[Compensi agenti.Compenso]

In questo modo calcoliamo il compenso in percentuale sul totale venduto. Torniamo sul report e aggiungiamo alla tabella anche la provvigione. Dovremo arrivare a vedere qualcosa di simile:

5. Formattazione valori

A questo punto vogliamo formattare i valori delle vendite nel formato valuta. Essendo dati aggregati in una tabella generata da JOIN, possiamo anzitutto formattare i valori all’origine, nelle tabelle di partenza. Questo vuol dire che ci spostiamo su vendite.

Selezioniamo la colonna desiderata ed inseriamo il formato Valuta. Possiamo modificare il tipo di valuta cliccando sull’opportuno pulsante come nell’immagine precedente.

Spostiamoci adesso su vendite_x_agente. Clicchiamo sulla colonna totale_importo, da Tipo dati selezioniamo Numero decimale, da Formato selezioniamo Valuta come prima. Stesso discorso per la provvigione.

Fatto questo possiamo tornare sulla visualizzazione dei dati. Dove vedremo qualcosa di simile a questo:

6. Creazione di Drill-through

Adesso vogliamo creare una analisi dettagliata per il totale venduto da ogni agente.

Aggiungiamo anzitutto una seconda pagina che chiameremo VENDITE AGENTE

Aggiungiamo una scheda per il titolo dell’agente analizzato:

Impostiamo una scheda che mostri anche il totale venduto nella maniera seguente:

Adesso aggiungiamo l’importo del venduto per ciascun agente per ciascuna città, procedendo nella maniera seguente:

Fatto questo attiviamo il campo Drill-thorugh sul nominativo dell’agente. Per farlo procediamo nel modo seguente:

Fatto tutto questo andiamo sulla pagina VENDITE. Clicchiamo col destro su un nominativo agente e scegliamo Drill-thought > VENDITE AGENTE.

Visualizzeremo i dati dell’agente nel report VENDITE AGENTE creato in precedenza.

Fatto questo abbiamo creato il collegamento Drill-through che volevamo.

Vedi articolo

21 Aprile 2020 Nessun commento

[vba] Classe per leggere e scrivere su un database MySQL

A seguito del precedente articolo [excel] Aggiungere all’origine dati delle tabelle da un database MySQL ho deciso di integrare l’esercizio creando una classe per leggere e scrivere tramite VBA su un database MySQL.

Affinché la classe funzioni ricordiamoci di installare il connettore recuperabile sul sito ufficiale (Connector/ODBC 8.0.19). Inoltre ricordiamoci anche che per poter eseguire la connessione ADO abbiamo bisogno di importare l’opportuna libreria dai riferimenti. In questo modo:

Detto tutto questo possiamo creare una classe MySQL con le seguenti istruzioni all’interno:

Private cs As String

Private Sub Class_Initialize()

    cs = "DRIVER={MySQL ODBC 8.0 ANSI Driver}"

End Sub

Public Sub setConnectionString( _
                                ByVal driver As String, _
                                Optional ByVal server As String = "localhost", _
                                Optional ByVal database As String = "", _
                                Optional ByVal user As String = "", _
                                Optional ByVal pass As String = "")
                                
    cs = "DRIVER={" & driver & "}"
    
    If server <> "" Then
        cs = cs & ";SERVER=" & server
    End If
    
    If database <> "" Then
        cs = cs & ";DATABASE=" & database
    End If
    
    If user <> "" Then
        cs = cs & ";USER=" & user
    End If
    
    If pass <> "" Then
        cs = cs & ";PASSWORD=" & pass
    End If

End Sub

Public Function dbout(ByVal query As String) As Variant

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Dim risultato() As Variant
    
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open cs
    
    rs.Open query, conn
    
    ' mi calcolo le dimensioni del recordset
    righe = 0
    colonne = 0
    
    If Not rs.EOF Then
        Do
            righe = righe + 1
            colonne = rs.Fields.Count
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    ' mi riposiziono all'inizio
    rs.MoveFirst
    
    ReDim risultato(righe, colonne)
    
    ' leggo i dati nel risultato
    righe = 0
    If Not rs.EOF Then
        Do
            For j = 0 To rs.Fields.Count - 1
                risultato(righe, j) = rs(j)
            Next j
            righe = righe + 1
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    rs.Close
    
    conn.Close
    
    dbout = risultato

End Function

Public Function dbin(ByVal query As String)

    Dim conn As ADODB.Connection
    
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open cs
    
    conn.Execute query
    
    conn.Close

End Function

Public Function clear(ByVal testo As String) As String

    testo = Replace(testo, "\", "\\")
    testo = Replace(testo, "'", "\'")
    
    clear = testo

End Function

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

Private cs As String

Private Sub Class_Initialize()

cs = "DRIVER={MySQL ODBC 8.0 ANSI Driver}"

End Sub

Public Sub setConnectionString( _

ByVal driver As String, _

Optional ByVal server As String = "localhost", _

Optional ByVal database As String = "", _

Optional ByVal user As String = "", _

Optional ByVal pass As String = "")

cs = "DRIVER={" & driver & "}"

If server <> "" Then

cs = cs & ";SERVER=" & server

End If

If database <> "" Then

cs = cs & ";DATABASE=" & database

End If

If user <> "" Then

cs = cs & ";USER=" & user

End If

If pass <> "" Then

cs = cs & ";PASSWORD=" & pass

End If

End Sub

Public Function dbout(ByVal query As String) As Variant

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim risultato() As Variant

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open cs

rs.Open query, conn

' mi calcolo le dimensioni del recordset

righe = 0

colonne = 0

If Not rs.EOF Then

Do

righe = righe + 1

colonne = rs.Fields.Count

rs.MoveNext

Loop Until rs.EOF

End If

' mi riposiziono all'inizio

rs.MoveFirst

ReDim risultato(righe, colonne)

' leggo i dati nel risultato

righe = 0

If Not rs.EOF Then

Do

For j = 0 To rs.Fields.Count - 1

risultato(righe, j) = rs(j)

Next j

righe = righe + 1

rs.MoveNext

Loop Until rs.EOF

End If

rs.Close

conn.Close

dbout = risultato

End Function

Public Function dbin(ByVal query As String)

Dim conn As ADODB.Connection

Set conn = CreateObject("ADODB.Connection")

conn.Open cs

conn.Execute query

conn.Close

End Function

Public Function clear(ByVal testo As String) As String

testo = Replace(testo, "\", "\\")

testo = Replace(testo, "'", "\'")

clear = testo

End Function

Possiamo usare la classe nel modo seguente:

Sub MostraTotale()

    Dim db As New MySQL
    db.setConnectionString driver:="MySQL ODBC 8.0 ANSI Driver", server:="localhost", database:="miodb", user:="root"
    
    Dim righe As Variant
    
    ' inseriamo dei valori
    nome = "Mario"
    cognome = "D'Angelo"
    indirizzo = "Via delle prove"
    
    db.dbin "INSERT INTO `clienti`(`nome`, `cognome`, `indirizzo`) VALUES ('" & db.clear(nome) & "','" & db.clear(cognome) & "','" & db.clear(indirizzo) & "')"
    
    ' leggiamo diverse righe
    righe = db.dbout("SELECT * FROM clienti")
    
    For i = 0 To UBound(righe, 1) - 1
        Debug.Print righe(i, 0) & " - " & righe(i, 1)
    Next i
    
    ' leggiamo una singola riga
    righe = db.dbout("SELECT COUNT(*) FROM clienti")
    
    Debug.Print righe(0, 0)

End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

Sub MostraTotale()

Dim db As New MySQL

db.setConnectionString driver:="MySQL ODBC 8.0 ANSI Driver", server:="localhost", database:="miodb", user:="root"

Dim righe As Variant

' inseriamo dei valori

nome = "Mario"

cognome = "D'Angelo"

indirizzo = "Via delle prove"

db.dbin "INSERT INTO `clienti`(`nome`, `cognome`, `indirizzo`) VALUES ('" & db.clear(nome) & "','" & db.clear(cognome) & "','" & db.clear(indirizzo) & "')"

' leggiamo diverse righe

righe = db.dbout("SELECT * FROM clienti")

For i = 0 To UBound(righe, 1) - 1

Debug.Print righe(i, 0) & " - " & righe(i, 1)

Next i

' leggiamo una singola riga

righe = db.dbout("SELECT COUNT(*) FROM clienti")

Debug.Print righe(0, 0)

End Sub

Faccio notare che con il metodo clear possiamo aggiungere gli slash agli apici, presenti nel cognome.

Vedi articolo

20 Aprile 2020 1 commento

[excel] Aggiungere all’origine dati delle tabelle da un database MySQL

Vediamo come collegare su Excel una tabella da un database MySQL. Per il mio esempio utilizzerò un database MySQL installato in locale grazie a XAMPP.

Chiameremo il database in questione torregatti e lo struttureremo nel modo seguente, con due tabelle: clienti e agenti

Il file di creazione del database si trova al seguente link: torregatti.zip

A questo punto dobbiamo procurarci l’opportuno Driver ODBC, se non lo abbiamo già installato. In particolare per il MySQL il driver è recuperabile sul sito ufficiale (Connector/ODBC 8.0.19).

Una volta installato e scaricato andiamo a verificare la stringa di connessione.

Premiamo il tasto WIN + R. Nella finestra Esegui digitiamo %systemdrive%\Windows\SysWoW64\odbcad32

In questo modo apriremo l’Amministrazione origine dati ODBC (32 bit). Nel caso si debba aprire quella a 64 bit sarà sufficiente digitare odbcad32 oppure %systemdrive%\Windows\System32\odbcad32

Nella scheda Driver individuiamo il driver che ci interessa, in questo modo:

Nel mio caso è il MySQL ODBC 8.0 ANSI Driver

Fatto questo apriamo Excel e andiamo Dati > Recupera dati > Da altre origini > Da ODBC

Nella finestra Da ODBC selezioniamo Nome origine dati (DNS) come Nessuno e apriamo le Opzioni avanzate. Alla Stringa di connessione inseriamo la seguente stringa: DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;

Qualora il nostro database non si trovasse in locale sostituiamo localhost con l’indirizzo del database. Fatto tutto questo premiamo su OK. A questo punto, se è la prima volta che effettuiamo la connessione, ci verranno chiesto nome utente e password. Selezionare la voce Database e scegliere come username root e lasciare il campo password vuoto. Queste impostazioni dipendono, nel mio caso, dal fatto che sto utilizzando XAMPP. In altre circostanze dovrò inserire nome utente e password appropriati.

Fatto questo possiamo selezionare la tabella alla quale ci vogliamo connettere.

Nel mio caso voglio prelevare la tabella agenti:

Una volta selezionata la tabella che ci interessa premere su Carica.

Possiamo caricare anche delle viste in MySQL, come nel mio caso la vista clienti_agenti. Se tutto è andato bene vedremo un risultato simile a questo:

Vedi articolo

14 Aprile 2020 Nessun commento

[VBA] Tema musicale di Tetris eseguito da una macro in VBA su Excel

Ispirato da un articolo che ho trovato online sull’argomento (Tetris Theme Song Using Processing) ho deciso di riprodurre il medesimo risultato in VBA su Excel, riproducendo quindi il tema musicale di Tetris tramite una macro per Excel.

Anzitutto importiamo dal kernel32 di Windows la funzione appropriata per eseguire un Beep, riproducendo quindi un suono, nel modo seguente:

Public Declare PtrSafe Function Beep Lib "kernel32" _
   (ByVal dwFreq As Long, _
    ByVal dwDuration As Long) As Long

1

2

3

Public Declare PtrSafe Function Beep Lib "kernel32" _

(ByVal dwFreq As Long, _

ByVal dwDuration As Long) As Long

La funzione accetta due argomenti: frequenza in Hz e durata in ms. Quindi per eseguire un 1 beep a 800Hz di 0,5 secondi, dovremo scrivere: Beep 800, 500

Detto questo voglio anzitutto riconvertire le funzioni dell’esempio nel link in VBA, creando un vettore di frequenze ed uno dei tempi, nel modo seguente:

Sub Gioca()

    Dim frequenze As Variant
    frequenze = Array(659.25511, 493.8833, 523.25113, 587.32954, 523.25113, 493.8833, 440#, 440#, _
    523.25113, 659.25511, 587.32954, 523.25113, 493.8833, 523.25113, 587.32954, _
    659.25511, 523.25113, 440#, 440#, 440#, 493.8833, 523.25113, 587.32954, _
    698.45646, 880#, 783.99087, 698.45646, 659.25511, 523.25113, 659.25511, _
    587.32954, 523.25113, 493.8833, 493.8833, 523.25113, 587.32954, 659.25511, _
    523.25113, 440#, 440#)
    
    Dim durata As Variant
    durata = Array(406.25, 203.125, 203.125, 406.25, 203.125, 203.125, 406.25, 203.125, _
    203.125, 406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 406.25, _
    406.25, 406.25, 203.125, 203.125, 203.125, 203.125, 609.375, 203.125, _
    406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 203.125, 203.125, _
    406.25, 203.125, 203.125, 406.25, 406.25, 406.25, 406.25, 406.25)
    
    For i = LBound(frequenze) To UBound(frequenze)
        Beep frequenze(i), durata(i)
    Next i

End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

Sub Gioca()

Dim frequenze As Variant

frequenze = Array(659.25511, 493.8833, 523.25113, 587.32954, 523.25113, 493.8833, 440#, 440#, _

523.25113, 659.25511, 587.32954, 523.25113, 493.8833, 523.25113, 587.32954, _

659.25511, 523.25113, 440#, 440#, 440#, 493.8833, 523.25113, 587.32954, _

698.45646, 880#, 783.99087, 698.45646, 659.25511, 523.25113, 659.25511, _

587.32954, 523.25113, 493.8833, 493.8833, 523.25113, 587.32954, 659.25511, _

523.25113, 440#, 440#)

Dim durata As Variant

durata = Array(406.25, 203.125, 203.125, 406.25, 203.125, 203.125, 406.25, 203.125, _

203.125, 406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 406.25, _

406.25, 406.25, 203.125, 203.125, 203.125, 203.125, 609.375, 203.125, _

406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 203.125, 203.125, _

406.25, 203.125, 203.125, 406.25, 406.25, 406.25, 406.25, 406.25)

For i = LBound(frequenze) To UBound(frequenze)

Beep frequenze(i), durata(i)

Next i

End Sub

Faccio notare che per la conversione dello spartito (che si può trovare al seguente link) è stata usata una tabella di conversione simile a questa.

Detto questo voglio riportare la medesima tabella di conversione in Excel, in un foglio che chiameremo NOTE. Il risultato che otterremo sarà simile a questo:

A questo punto voglio creare un altro foglio, chiamato SPARTITO, dove creeremo tre colonne, una nella quale scegliere le note e l’altra dove inserire i tempi come ottavi di nota.

A questo punto associamo al pulsante Suona la seguente macro:

Sub Suona()

    Dim ottavo As Double
    ottavo = 1000 * 13 / 8 / 8 ' 1000 * 13 secondi / 8 / 8 = 203,125 ms

    Dim frequenze As Variant
    frequenze = Worksheets("SPARTITO").Range("FREQUENZE").Value
    
    Dim tempi As Variant
    tempi = Worksheets("SPARTITO").Range("TEMPI").Value
    
    For i = LBound(frequenze) To UBound(frequenze)
        Beep CDbl(frequenze(i, 1)), CDbl(tempi(i, 1) * ottavo)
    Next i

End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Sub Suona()

Dim ottavo As Double

ottavo = 1000 * 13 / 8 / 8 ' 1000 * 13 secondi / 8 / 8 = 203,125 ms

Dim frequenze As Variant

frequenze = Worksheets("SPARTITO").Range("FREQUENZE").Value

Dim tempi As Variant

tempi = Worksheets("SPARTITO").Range("TEMPI").Value

For i = LBound(frequenze) To UBound(frequenze)

Beep CDbl(frequenze(i, 1)), CDbl(tempi(i, 1) * ottavo)

Next i

End Sub

Le frequenze vengono recuperate dal foglio NOTE.

Da qui si può scaricare il file excel comprensivo di macro: excel VBA – note musicali – tema tetris

Vedi articolo

Categoria: Windowssiamo

[virtualbox] Failed to query SMC value from the host (VERR_INVALID_HANDLE)

[c#] Semplice classe per gestire connessione e query su un database MySQL

[word] Rimuovere “Limita modifica” da un file docx di Word

[windows] Installazione di Microsoft Exchange Mailbox server su Windows Server 2016

1. Installazione di Exchange Server

2. Configurazioni cassette postali

[PowerShell] Esempio di script per inviare un messaggio a diversi computer sulla rete tramite PowerShell

[dax] Creare una tabella temporanea con SUMMARIZE ed effettuarci sopra dei calcoli

Analizzare i dati da Excel e MySQL utilizzando Power BI (per esordienti totali)

1. Importazione dati da MySQL

2. Elaborazione dati

3. Creiamo la nostra prima tabella nel report

4. Integriamo i dati da un foglio Excel

6. Creazione di Drill-through

[vba] Classe per leggere e scrivere su un database MySQL

[excel] Aggiungere all’origine dati delle tabelle da un database MySQL

[VBA] Tema musicale di Tetris eseguito da una macro in VBA su Excel