23.04.28 Day64

대기질 정보 확인 APP(OpenAI 사용)

MainWindow.xaml

<mah:MetroWindow  xmlns:mah="http://metro.mahapps.com/winfx/xaml/controls" x:Class="wpf12_finedustCheck.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:iconPacks="http://metro.mahapps.com/winfx/xaml/iconpacks"
        xmlns:local="clr-namespace:wpf12_finedustCheck"
        mc:Ignorable="d"
        Title="FineDust" Height="450" Width="800"
                  MinWidth="500" MinHeight="350"
                  Loaded="MetroWindow_Loaded">
    <mah:MetroWindow.IconTemplate>
        <DataTemplate>
            <iconPacks:PackIconWeatherIcons Kind="Dust" Margin="5,7,0,0" Foreground="White"/>
        </DataTemplate>
    </mah:MetroWindow.IconTemplate>
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="60"/>
            <RowDefinition Height="1*"/>
            <RowDefinition Height="25"/>
        </Grid.RowDefinitions>

        <Grid Grid.Row="0">
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="1*"/>
                <ColumnDefinition Width="1*"/>
            </Grid.ColumnDefinitions>

            <Label Grid.Column="0" Content="김해시 실시간 미세먼지 측정" FontSize="14" FontWeight="Bold" Margin="10"/>

            <StackPanel Grid.Column="1" Orientation="Horizontal" HorizontalAlignment="Right">
                <Button x:Name="BtnReqReqltime" Content="실시간 조회" Margin="5" Style="{StaticResource MahApps.Styles.Button.Dialogs.Accent}" Width="100" Click="BtnReqReqltime_Click"/>
                <Button x:Name="BtnSaveData" Content="저장" Margin="5" Style="{StaticResource MahApps.Styles.Button.Square.Highlight}" Width="60" Click="BtnSaveData_Click"/>
                <ComboBox x:Name="CboReqDate" Margin="5" Style="{StaticResource MahApps.Styles.ComboBox}" Width="150"
                          mah:TextBoxHelper.Watermark="검색날짜 선택"
                          mah:TextBoxHelper.UseFloatingWatermark="True"
                          mah:TextBoxHelper.ClearTextButton="True" SelectionChanged="CboReqDate_SelectionChanged"/>
            </StackPanel>

        </Grid>

        <DataGrid Grid.Row="1" x:Name="GrdResult" Style="{StaticResource MahApps.Styles.DataGrid.Azure}" Margin="10"
                  IsReadOnly="True" ItemsSource="{Binding}"
                  MouseDoubleClick="GrdResult_MouseDoubleClick"></DataGrid>

        <StatusBar Grid.Row="2">
            <StatusBarItem Content="김해시 미세먼지앱"/>
            <Separator Style="{StaticResource MahApps.Styles.Separator.StatusBar}"/>
            <StatusBarItem x:Name="StsResult"/>
        </StatusBar>

    </Grid>
</mah:MetroWindow>

MainWindow.xaml.cs

using MahApps.Metro.Controls;
using MySql.Data.MySqlClient;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using wpf12_finedustCheck.Logics;
using wpf12_finedustCheck.Models;


namespace wpf12_finedustCheck
{
    /// <summary>
    /// MainWindow.xaml에 대한 상호 작용 논리
    /// </summary>
    public partial class MainWindow : MetroWindow
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        //김해시 openAPI 조회
        private async void BtnReqReqltime_Click(object sender, RoutedEventArgs e)
        {
            string openApiUri = "https://smartcity.gimhae.go.kr/smart_tour/dashboard/api/publicData/dustSensor";
            string result = string.Empty;

            //WebRequest , WebResponse 객체
            WebRequest req = null;
            WebResponse res = null;
            StreamReader reader = null;

            try
            {
                req = WebRequest.Create(openApiUri);
                res = await req.GetResponseAsync();
                reader = new StreamReader(res.GetResponseStream());
                result = reader.ReadToEnd();

                Debug.WriteLine(result);

            }
            catch (Exception ex)
            {
                await Commons.ShowMessageAsync("오류", $"OpenAPI 조회오류 {ex.Message}");
            }

            var jsonResult = JObject.Parse(result);
            var status = Convert.ToInt32(jsonResult["status"]);

            try
            {
                if (status == 200) // 정상이면 데이터 받아서 처리
                {
                    var data = jsonResult["data"];
                    var json_array = data as JArray;

                    var dustSensors = new List<DustSensor>();
                    foreach (var sensor in json_array)
                    {
                        dustSensors.Add(new DustSensor
                        {
                            Id = 0,
                            Dev_id = Convert.ToString(sensor["dev_id"]),
                            Name = Convert.ToString(sensor["name"]),
                            Loc = Convert.ToString(sensor["loc"]),
                            Coordx = Convert.ToDouble(sensor["coordx"]),
                            Coordy = Convert.ToDouble(sensor["coordy"]),
                            Ison = Convert.ToBoolean(sensor["ison"]),
                            Pm10_after = Convert.ToInt32(sensor["pm10_after"]),
                            Pm25_after = Convert.ToInt32(sensor["pm25_after"]),
                            State = Convert.ToInt32(sensor["state"]),
                            Timestamp = Convert.ToDateTime(sensor["timestamp"]),
                            Company_id = Convert.ToString(sensor["company_id"]),
                            Company_name = Convert.ToString(sensor["company_name"])
                        });
                    }
                    this.DataContext = dustSensors;
                    StsResult.Content = $"OpenAPI {dustSensors.Count}건 조회 완료";
                }

            }
            catch (Exception ex)
            {
                await Commons.ShowMessageAsync("오류", $"JSON 처리오류 {ex.Message}");
            }
        }
        //검색한 결과 DB(MySQL)에 저장 
        private async void BtnSaveData_Click(object sender, RoutedEventArgs e)
        {
            if(GrdResult.Items.Count==0)
            {
                await (Commons.ShowMessageAsync("오류", "조회 이후 저장하세요!"));
                return;
            }
            try
            {
                using (MySqlConnection conn = new MySqlConnection(Commons.myConnString))
                {
                    if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
                    var query = @"INSERT INTO dustsensor
                                (
                                Dev_id,
                                Name,
                                Loc,
                                Coordx,
                                Coordy,
                                Ison,
                                Pm10_after,
                                Pm25_after,
                                State,
                                Timestamp,
                                Company_id,
                                Company_name)
                                VALUES
                                (
                                @Dev_id,
                                @Name,
                                @Loc,
                                @Coordx,
                                @Coordy,
                                @Ison,
                                @Pm10_after,
                                @Pm25_after,
                                @State,
                                @Timestamp,
                                @Company_id,
                                @Company_name)";
                    var insRes = 0;
                    foreach(var temp in GrdResult.Items)
                    {
                        if(temp is DustSensor)
                        {
                            var item = temp as DustSensor;

                            MySqlCommand cmd = new MySqlCommand(query, conn);
                            cmd.Parameters.AddWithValue("@Dev_id", item.Dev_id);
                            cmd.Parameters.AddWithValue("@Name", item.Name);
                            cmd.Parameters.AddWithValue("@Loc", item.Loc);
                            cmd.Parameters.AddWithValue("@Coordx", item.Coordx);
                            cmd.Parameters.AddWithValue("@Coordy", item.Coordy);
                            cmd.Parameters.AddWithValue("@Ison", item.Ison);
                            cmd.Parameters.AddWithValue("@Pm10_after", item.Pm10_after);
                            cmd.Parameters.AddWithValue("@Pm25_after", item.Pm25_after);
                            cmd.Parameters.AddWithValue("@State", item.State);
                            cmd.Parameters.AddWithValue("@Timestamp", item.Timestamp);
                            cmd.Parameters.AddWithValue("@Company_id", item.Company_id);
                            cmd.Parameters.AddWithValue("@Company_name", item.Company_name);

                            insRes += cmd.ExecuteNonQuery();
                        }
                    }
                    await Commons.ShowMessageAsync("저장", "DB 저장 성공!");
                    StsResult.Content = $"DB 저장 {insRes}건 성공!";
                }
            }
            catch(Exception ex)
            {
                await Commons.ShowMessageAsync("오류", $"DB저장 오류! {ex.Message}");
            }
        }
        //DB(MySQL)에서 조회해서 리스트에 뿌리기
        private void CboReqDate_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if(CboReqDate.SelectedValue!=null)
            {
                using (MySqlConnection conn = new MySqlConnection(Commons.myConnString))
                {
                    conn.Open();
                    var query = @"SELECT Id,
                                    Dev_id,
                                    Name,
                                    Loc,
                                    Coordx,
                                    Coordy,
                                    Ison,
                                    Pm10_after,
                                    Pm25_after,
                                    State,
                                    Timestamp,
                                    Company_id,
                                    Company_name
                                FROM dustsensor
                                WHERE date_format(Timestamp,'%Y-%m-%d') = @Timestamp";
                    MySqlCommand cmd = new MySqlCommand(query, conn);
                    cmd.Parameters.AddWithValue("@Timestamp", CboReqDate.SelectedValue.ToString());
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "dustsensor");
                    List<DustSensor> dustSensors = new List<DustSensor>();
                    foreach(DataRow row in ds.Tables["dustsensor"].Rows)
                    {
                        dustSensors.Add(new DustSensor
                        {
                            Id = Convert.ToInt32(row["Id"]),
                            Dev_id = Convert.ToString(row["dev_id"]),
                            Name = Convert.ToString(row["name"]),
                            Loc = Convert.ToString(row["loc"]),
                            Coordx = Convert.ToDouble(row["coordx"]),
                            Coordy = Convert.ToDouble(row["coordy"]),
                            Ison = Convert.ToBoolean(row["ison"]),
                            Pm10_after = Convert.ToInt32(row["pm10_after"]),
                            Pm25_after = Convert.ToInt32(row["pm25_after"]),
                            State = Convert.ToInt32(row["state"]),
                            Timestamp = Convert.ToDateTime(row["timestamp"]),
                            Company_id = Convert.ToString(row["company_id"]),
                            Company_name = Convert.ToString(row["company_name"])
                        });
                    }
                    this.DataContext = dustSensors;
                    StsResult.Content = $"DB {dustSensors.Count}건 조회완료";
                }
            }
            else
            {
                this.DataContext = null;
                StsResult.Content = $"DB 조회클리어";
            }
        }

        private void MetroWindow_Loaded(object sender, RoutedEventArgs e)
        {
            //콤보박스에 들어갈 날짜를 DB에서 불러옴
            using(MySqlConnection conn = new MySqlConnection(Commons.myConnString))
            {
                conn.Open();
                var query = @"SELECT date_format(timestamp,'%Y-%m-%d') AS Save_date
                            FROM dustsensor
                            group by 1
                            order by 1";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                List<string> saveDateList = new List<string>(); 
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    saveDateList.Add(Convert.ToString(row["Save_Date"]));
                }
                CboReqDate.ItemsSource = saveDateList;
            }
        }

        //그리드 특정 Row 더블클릭 새창에 센서 위치 출력
        private void GrdResult_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {
            var selItem = GrdResult.SelectedItem as DustSensor;

            var mapWindow = new MapWindow(selItem.Coordy,selItem.Coordx);//부모창 위치값을 자식창으로 전달
            mapWindow.Owner = this;//MainWindow 부모
            mapWindow.WindowStartupLocation = WindowStartupLocation.CenterOwner;//부모창 중간에 출력
            mapWindow.ShowDialog();
        }
    }
}

MapWindow.xaml

<Window x:Class="wpf12_finedustCheck.MapWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:mah="http://metro.mahapps.com/winfx/xaml/controls"
        xmlns:cefSharp ="clr-namespace:CefSharp.Wpf;assembly=CefSharp.Wpf"
        xmlns:local="clr-namespace:wpf12_finedustCheck"
        mc:Ignorable="d"
        Title="MapWindow" Height="300" Width="600">
    <Grid Margin="20">
        <cefSharp:ChromiumWebBrowser x:Name="BrsLocSensor" Address="https://map.google.com"/>
    </Grid>
</Window>

MapWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace wpf12_finedustCheck
{
    /// <summary>
    /// MapWindow.xaml에 대한 상호 작용 논리
    /// </summary>
    public partial class MapWindow : Window
    {
        public MapWindow()
        {
            InitializeComponent();
        }
        public MapWindow(double coordy,double coordx):this()
        {
            BrsLocSensor.Address = $"https://google.com/maps/place/{coordy},{coordx}";
        }
    }
}

Logics/Commons.cs

using MahApps.Metro.Controls;
using MahApps.Metro.Controls.Dialogs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace wpf12_finedustCheck.Logics
{
    public class Commons
    {
        //MySQL용
        public static readonly string myConnString = "Server=localhost;" +
                                                     "Port=3306;" +
                                                     "Database=miniproject;" +
                                                     "Uid=root;" +
                                                     "Pwd=12345;";


        public static async Task<MessageDialogResult> ShowMessageAsync(string title, string message,
            MessageDialogStyle style = MessageDialogStyle.Affirmative)
        {
            return await ((MetroWindow)Application.Current.MainWindow).ShowMessageAsync(title, message, style, null);
        }
    }
}

Models/DustSensor.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace wpf12_finedustCheck.Models
{
    public class DustSensor
    {
        public int Id { get; set; }
        public string Dev_id { get; set; }
        public string Name { get; set; }
        public string Loc { get; set; }
        public double Coordx { get; set; }
        public double Coordy { get; set; }
        public bool Ison { get; set; }
        public int Pm10_after { get; set; }
        public int Pm25_after { get; set; }
        public int State { get; set; }
        public DateTime Timestamp { get; set; }
        public string Company_id { get; set; }
        public string Company_name { get; set; }
    }
}

실행화면