Created
June 9, 2025 08:52
-
-
Save the9ball/8154f245428a30e27765cc1b8c1b3cf9 to your computer and use it in GitHub Desktop.
ヘルスコネクトからPFCデータを取ってくる
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <Query Kind="Program"> | |
| <NuGetReference>System.Data.SQLite.Core</NuGetReference> | |
| <Namespace>System.IO.Compression</Namespace> | |
| <Namespace>System.Threading.Tasks</Namespace> | |
| <Namespace>System.Data.SQLite</Namespace> | |
| <Namespace>System.Runtime.CompilerServices</Namespace> | |
| </Query> | |
| // Need Packages | |
| // System.Data.SQLite.Core | |
| const string SourcePath = @"path/to/healthconnect.zip"; | |
| const string DestinationFilePath = @"path/to/output.txt"; | |
| record class Entry(DateTime date, double protein, double fat, double carbohydrate) | |
| { | |
| public override string ToString() => $"{date:yyyy/MM/dd}\t{protein}\t{fat}\t{carbohydrate}"; | |
| } | |
| async Task Main() | |
| { | |
| var cts = new CancellationTokenSource(TimeSpan.FromMinutes(10)); // Timeout | |
| var destinationPath = Directory.CreateTempSubdirectory().FullName; | |
| try | |
| { | |
| var ct = cts.Token; | |
| await UnzipAsync(SourcePath, destinationPath, ct); | |
| var data = ReadDataAsync(destinationPath, ct); | |
| await WriteAsync(data, ct); | |
| } | |
| catch (Exception e) | |
| { | |
| e.Dump(); | |
| throw; | |
| } | |
| finally | |
| { | |
| try | |
| { | |
| await cts.CancelAsync(); | |
| // Wait for release SQLite Connection | |
| await Task.Delay(TimeSpan.FromSeconds(1)); | |
| GC.Collect(); | |
| GC.WaitForPendingFinalizers(); | |
| // Delete Temporary Directory | |
| if (Directory.Exists(destinationPath)) | |
| { | |
| Directory.Delete(destinationPath, true); | |
| } | |
| } | |
| catch (Exception e) | |
| { | |
| e.Dump(); | |
| } | |
| } | |
| } | |
| /// <summary>Unzip HealthConnect Zip</summary> | |
| Task UnzipAsync(string sourcePath, string destinationPath, CancellationToken ct) | |
| => Task.Run( | |
| () => | |
| { | |
| ZipFile.ExtractToDirectory(sourcePath, destinationPath); | |
| ct.ThrowIfCancellationRequested(); | |
| } | |
| ); | |
| /// <summary>Read data from Database</summary> | |
| async IAsyncEnumerable<Entry> ReadDataAsync(string destinationPath, [EnumeratorCancellation] CancellationToken ct) | |
| { | |
| const string dataName = "health_connect_export.db"; | |
| const string query = "SELECT DATE('1970-01-01', '+' || local_date || ' days') AS date, protein, total_fat as fat, total_carbohydrate as carbohydrate FROM nutrition_record_table;"; | |
| string connectionString = $"Data Source={Path.Combine(destinationPath, dataName)};Pooling=false;"; | |
| await using var connection = new SQLiteConnection(connectionString); | |
| await connection.OpenAsync(ct); | |
| try | |
| { | |
| await using var command = new SQLiteCommand(query, connection); | |
| var reader = await command.ExecuteReaderAsync(ct); | |
| while (await reader.ReadAsync(ct)) | |
| { | |
| yield return toEntry(reader); | |
| } | |
| } | |
| finally | |
| { | |
| await connection.CloseAsync(); | |
| SQLiteConnection.ClearAllPools(); | |
| } | |
| static Entry toEntry(System.Data.Common.DbDataReader reader) | |
| => new( | |
| date: DateTime.Parse(reader["date"].ToString()), | |
| protein: reader["protein"] is var p && p is DBNull ? 0.0 : Convert.ToDouble(p), | |
| fat: reader["fat"] is var f && f is DBNull ? 0.0 : Convert.ToDouble(f), | |
| carbohydrate: reader["carbohydrate"] is var c && c is DBNull ? 0.0 : Convert.ToDouble(c) | |
| ); | |
| } | |
| /// <summary>Write results</summary> | |
| async Task WriteAsync(IAsyncEnumerable<Entry> data, CancellationToken ct) | |
| { | |
| using var s = new StreamWriter(DestinationFilePath); | |
| await foreach (var d in data) | |
| { | |
| await s.WriteLineAsync(d.ToString()); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment