LibraryManagementApp/Statistics.cs at master · erenakbas57/LibraryManagementApp · GitHub

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
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace Kutuphanecsharp
{
    public partial class Statistics : Form
    {
        public Statistics()
        {
            InitializeComponent();
        }

        SQLiteConnection baglanti = new SQLiteConnection("Data Source=kihmed.db;Version=3");
        SQLiteCommand komut;
        SQLiteDataAdapter da;
        DataSet ds;
        SQLiteDataReader dr;

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void KitapIstatistik_Load(object sender, EventArgs e)
        {
            AylikEnlbl();
            Listele();

            baglanti.Open();
            komut = new SQLiteCommand();
            komut.Connection = baglanti;

            string ay = DateTime.Today.ToString("MM");
            string yil = DateTime.Today.ToString("yyyy");
            string deger = $@"select count(*) from odunc where sontarih like '{yil}{ay}__' group by uyeid order by count(kitap) desc";
            komut.CommandText = deger;
            int adet = Convert.ToInt32(komut.ExecuteScalar());
            baglanti.Close();

            if (adet != 0)
            {
                lblAy.Visible = true;
                lblAyAd.Visible = true;
            }

        }

        void AylikEnlbl()
        {

            komut = new SQLiteCommand();
            komut.Connection = baglanti;

            string ay = DateTime.Today.ToString("MM");
            string yaziay = DateTime.Today.ToString("MMMM");
            string yil = DateTime.Today.ToString("yyyy");

            lblAy.Text = $"{yaziay} ayı en çok kitap okuyan";

            baglanti.Open();
            komut.CommandText = $@"select uyeid,ad,soyad,count(kitap) from odunc where sontarih like '{yil}{ay}__' group by uyeid order by count(kitap) desc limit 1";
            dr = komut.ExecuteReader();


            if (dr.Read())
            {
                string ad = dr["ad"].ToString();
                string soyad = dr["soyad"].ToString();
                lblAyAd.Text = $"{ad.ToUpper()} {soyad.ToUpper()}";
            }
            dr.Close();
            baglanti.Close();

        }

        void Listele()
        {
            string ay = DateTime.Today.ToString("MM");
            string yil = DateTime.Today.ToString("yyyy");

            string deger = $@"select uyeid as 'ÜYE NO',ad as 'AD',soyad as 'SOYAD',count(kitap) as 'Toplam Kitap' from odunc where sontarih like '{yil}{ay}__' group by uyeid order by count(kitap) desc";
            da = new SQLiteDataAdapter(deger, baglanti);
            ds = new DataSet();
            baglanti.Open();
            da.Fill(ds, "kihmed");
            dgw.DataSource = ds.Tables["kihmed"];
            baglanti.Close();
        }


    }
}