가입 기념으로 ,… 그동안은 WINForm으로 사브작 사브작 PC용 테스트 설비에 썼는데, 세상이 LOT 라내요, 천만 다행인게, 쳇지피티… 이게 axml 을 다 만들어 주내요,… 앞으로 MAUI 를 연마해 볼라고요,…
<?xml version="1.0" encoding="utf-8" ?><ScrollView>
<StackLayout Padding="20" Spacing="15">
<!-- Connect to DB Button -->
<Button x:Name="btnConnect" Text="Connect to DB" Clicked="BtnConnect_Click"/>
<!-- Status Label -->
<Label x:Name="lblStatus" Text="Status: Not Connected" />
<!-- Customer ID Input -->
<Label Text="Customer ID:"/>
<Entry x:Name="txtCustomerId" Keyboard="Numeric"/>
<!-- Timestamp Input using DatePicker (Calendar UI) -->
<Label Text="Timestamp (Format: yyyy-MM-dd):"/>
<Entry x:Name="txtTimestamp" Placeholder="e.g., 2011-01-01" />
<!-- Fetch Data Button -->
<Button x:Name="btnQuery" Text="Fetch Data" Clicked="BtnQuery_Clicked"/>
<!-- Grid for column headers -->
<Grid Padding="10" RowSpacing="10">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<!-- Column Headers -->
<Label Text="First Name" Grid.Column="0" HorizontalOptions="Center" />
<Label Text="Last Name" Grid.Column="1" HorizontalOptions="Center" />
<Label Text="Film ID" Grid.Column="2" HorizontalOptions="Center" />
<Label Text="Title" Grid.Column="3" HorizontalOptions="Center" />
<Label Text="Rental Rate" Grid.Column="4" HorizontalOptions="Center" />
<Label Text="Late Fee" Grid.Column="5" HorizontalOptions="Center" />
<Label Text="Total Payments" Grid.Column="6" HorizontalOptions="Center" />
<Label Text="Balance" Grid.Column="7" HorizontalOptions="Center" />
</Grid>
<!-- ListView for displaying results -->
<ListView x:Name="listView" IsVisible="False">
<ListView.ItemTemplate>
<DataTemplate>
<ViewCell>
<Grid Padding="10" ColumnSpacing="10" RowSpacing="5" >
<!-- Define Columns -->
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<!-- Bind Data to Labels -->
<Label Text="{Binding FirstName}" Grid.Column="0"/>
<Label Text="{Binding LastName}" Grid.Column="1"/>
<Label Text="{Binding FilmId}" Grid.Column="2"/>
<Label Text="{Binding Title}" Grid.Column="3"/>
<Label Text="{Binding RentalRate}" Grid.Column="4"/>
<Label Text="{Binding LateFee}" Grid.Column="5"/>
<Label Text="{Binding TotalPayments}" Grid.Column="6"/>
<Label Text="{Binding Balance}" Grid.Column="7"/>
</Grid>
</ViewCell>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</StackLayout>
</ScrollView>
using Microsoft.Maui.Controls;
using Npgsql;
using System;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
namespace PostGreQuery_MauiApp
{
public partial class MainPage : ContentPage
{
private DbConnector dbConnector;
private int customerId;
private DateTime timestamp;
public MainPage()
{
InitializeComponent();
dbConnector = new DbConnector();
}
private void BtnConnect_Click(object sender, EventArgs e)
{
if (dbConnector.ConnectToDatabase())
{
lblStatus.Text = "Status: Connected";
}
else
{
lblStatus.Text = "Status: Not Connected";
}
}
private async void BtnQuery_Clicked(object sender, EventArgs e)
{
// 유효한 Customer ID와 Timestamp가 입력되었는지 확인
if (int.TryParse(txtCustomerId.Text, out int customerId) && DateTime.TryParse(txtTimestamp.Text, out DateTime timestamp))
{
try
{
// DB에서 데이터를 가져옵니다
DataTable result = await dbConnector.FetchCustomerBalance(customerId, timestamp);
if (result != null && result.Rows.Count > 0)
{
PopulateListView(result); // ListView에 데이터를 채우고 표시
}
else
{
await DisplayAlert("No Data", "No data returned from the query.", "OK");
}
}
catch (Exception ex)
{
// 예외 발생 시 오류 메시지 표시
await DisplayAlert("Error", $"An error occurred: {ex.Message}", "OK");
}
}
else
{
await DisplayAlert("Invalid Input", "Please enter a valid Customer ID and Timestamp.", "OK");
}
}
private async void PopulateListView(DataTable dataTable)
{
var dataList = new List<object>();
foreach (DataRow row in dataTable.Rows)
{
try
{
if (row[0] is object[] dataArray)
{
string[] values = dataArray.Select(item => item?.ToString()?.Trim() ?? "NULL").ToArray();
if (values.Length < 8)
{
await DisplayAlert("Error", "Unexpected data format. Skipping row.", "OK");
continue;
}
// 데이터를 객체로 변환하여 리스트에 추가
var item = new
{
FirstName = values[0], // First Name
LastName = values[1], // Last Name
FilmId = values[2], // FilmId
Title = values[3], // Title
RentalRate = values[4], // RentalRate
LateFee = values[5], // LateFee
TotalPayments = values[6], // TotalPayments
Balance = values[7] // Balance
};
dataList.Add(item);
}
else
{
await DisplayAlert("Error", "Row does not contain an object array.", "OK");
}
}
catch (Exception ex)
{
await DisplayAlert("Error", $"Error processing row: {ex.Message}", "OK");
}
}
// ListView에 데이터 바인딩
listView.ItemsSource = dataList; // ItemsSource를 수정하여 데이터를 갱신
listView.IsVisible = true;
}
private string RemoveParentheses(string input)
{
if (string.IsNullOrEmpty(input))
{
return input;
}
// 앞과 뒤의 괄호를 제거
return input.TrimStart('(').TrimEnd(')');
}
// 문자열에서 따옴표를 처리하고, 괄호를 제거하는 함수
private string RemoveParenthesesAndQuotes(string input)
{
if (string.IsNullOrEmpty(input))
{
return input;
}
// 앞과 뒤의 괄호를 제거
string trimmed = input.TrimStart('(').TrimEnd(')');
// 따옴표를 제거
return trimmed.Replace("\"", "");
}
public class DbConnector
{
private NpgsqlConnection connection;
private string connectionString = "Host=10.0.2.2;Port=5432;Database=dvd_rental;Username=postgres;Password=4314;";
public DbConnector()
{
connection = new NpgsqlConnection(connectionString);
}
public bool ConnectToDatabase()
{
try
{
connection.Open();
return true;
}
catch (Exception ex)
{
Console.WriteLine($"Connection error: {ex.Message}");
return false;
}
}
public async Task<DataTable> FetchCustomerBalance(int customerId, DateTime timestamp)
{
try
{
string query = "SELECT mody_get_customer_balance_with_rented_films(@customerId, @timestamp);";
// NpgsqlCommand 객체 생성
NpgsqlCommand command = new NpgsqlCommand(query, connection);
command.Parameters.AddWithValue("@customerId", customerId);
command.Parameters.AddWithValue("@timestamp", timestamp);
// NpgsqlDataAdapter를 사용하여 결과를 DataTable에 채운다.
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command);
DataTable resultTable = new DataTable();
adapter.Fill(resultTable); // 쿼리 결과를 resultTable에 채운다.
// 결과 확인을 위한 디버깅 코드
if (resultTable.Rows.Count > 0)
{
await Application.Current.MainPage.DisplayAlert("Return Data", "Data was returned from the query.", "OK");
}
else
{
await Application.Current.MainPage.DisplayAlert("No Data", "No data was returned from the query.", "OK");
}
/*
// for 디버깅 결과 데이터 출력
string debugMessage = "Query Results:\n";
foreach (DataRow row in resultTable.Rows)
{
foreach (DataColumn column in resultTable.Columns)
{
var value = row[column] == DBNull.Value ? "NULL" : row[column].ToString();
debugMessage += $"{column.ColumnName}: {value}\n";
}
debugMessage += "----\n";
}
await Application.Current.MainPage.DisplayAlert("Query Result", debugMessage, "OK");
*/
// 결과 반환
return resultTable;
}
catch (Exception ex)
{
await Application.Current.MainPage.DisplayAlert("Error", $"Error executing query: {ex.Message}", "OK");
return null;
}
}
}
}
}
요건 DB 에 등록 되어 있는 Funcntion
– FUNCTION: public.mody_get_customer_balance_with_rented_films(integer, timestamp without time zone)
– DROP FUNCTION IF EXISTS public.mody_get_customer_balance_with_rented_films(integer, timestamp without time zone);
CREATE OR REPLACE FUNCTION public.mody_get_customer_balance_with_rented_films(
p_customer_id integer,
p_effective_date timestamp without time zone)
RETURNS TABLE(first_name character varying, last_name character varying, film_id integer, title character varying, rental_rate numeric, late_fee numeric, total_payments numeric, balance numeric)
LANGUAGE ‘plpgsql’
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS BODY
DECLARE
v_rentfees NUMERIC(10,2) := 0;
v_overfees NUMERIC(10,2) := 0;
v_payments NUMERIC(10,2) := 0;
BEGIN
– 대여 수수료 계산
SELECT COALESCE(SUM(film.rental_rate), 0)
INTO v_rentfees
FROM film
JOIN inventory ON film.film_id = inventory.film_id
JOIN rental ON inventory.inventory_id = rental.inventory_id
WHERE rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
-- 연체료 계산
SELECT COALESCE(SUM(
CASE
WHEN (rental.return_date - rental.rental_date) > (film.rental_duration * INTERVAL '1 day') THEN
(EXTRACT(DAY FROM (rental.return_date - rental.rental_date)) - film.rental_duration)::NUMERIC
ELSE 0
END), 0)
INTO v_overfees
FROM rental
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
WHERE rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
-- 결제 금액 계산
SELECT COALESCE(SUM(payment.amount), 0)
INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
-- 결과 반환
RETURN QUERY
SELECT
customer.first_name,
customer.last_name,
film.film_id,
film.title,
film.rental_rate,
CASE
WHEN (rental.return_date - rental.rental_date) > (film.rental_duration * INTERVAL '1 day') THEN
(EXTRACT(DAY FROM (rental.return_date - rental.rental_date)) - film.rental_duration)::NUMERIC
ELSE 0
END AS late_fee,
v_payments AS total_payments,
v_rentfees + v_overfees - v_payments AS balance
FROM rental
JOIN customer ON rental.customer_id = customer.customer_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
WHERE rental.customer_id = p_customer_id
AND rental.rental_date <= p_effective_date;
END;
BODY;
ALTER FUNCTION public.mody_get_customer_balance_with_rented_films(integer, timestamp without time zone)
OWNER TO postgres;
DB 에서 리턴 되는 값은
“(Mary,Smith,3,”“Adaptation Holes”“,2.99,2,114.70,-0.02)”
“(Mary,Smith,22,”“Amistad Midsummer”“,2.99,0,114.70,-0.02)” 이렇게
2D 배열로 들어오는 거 로 받아서 리스튜뷰에 디스플레이 … 하 사진은 못 올리내요,…