353 lines
No EOL
11 KiB
Dart
353 lines
No EOL
11 KiB
Dart
// DatabaseHelper - シンプルデータベースアクセスヘルパー（sqflite 直接操作）
|
||
import 'dart:io';
|
||
import 'package:flutter/foundation.dart';
|
||
import 'package:sqflite/sqflite.dart';
|
||
import '../models/product.dart';
|
||
|
||
// Customer モデル
|
||
class Customer {
|
||
final int? id;
|
||
final String? customerCode;
|
||
final String? name;
|
||
final String? address;
|
||
final String? phone;
|
||
final String? email;
|
||
final bool isInactive;
|
||
|
||
Customer({
|
||
this.id,
|
||
this.customerCode,
|
||
this.name,
|
||
this.address,
|
||
this.phone,
|
||
this.email,
|
||
this.isInactive = false,
|
||
});
|
||
}
|
||
|
||
class DatabaseHelper {
|
||
static Database? _database;
|
||
|
||
/// データベース初期化（サンプルデータ付き）
|
||
static Future<void> init() async {
|
||
if (_database != null) return;
|
||
|
||
try {
|
||
// アプリの現在のフォルダを DB パスに使用（開発/テスト用）
|
||
final dbPath = Directory.current.path + '/data/db/sales.db';
|
||
|
||
_database = await _initDatabase(dbPath);
|
||
print('[DatabaseHelper] DB initialized successfully');
|
||
|
||
} catch (e) {
|
||
print('DB init error: $e');
|
||
throw Exception('Database initialization failed: $e');
|
||
}
|
||
}
|
||
|
||
/// テーブル作成時にサンプルデータを自動的に挿入
|
||
static Future<Database> _initDatabase(String path) async {
|
||
return await openDatabase(
|
||
path,
|
||
version: 2, // バージョンアップ（仕入先情報カラム追加用）
|
||
onCreate: _onCreateTableWithSampleData,
|
||
);
|
||
}
|
||
|
||
/// テーブル作成用関数 + サンプルデータ自動挿入
|
||
static Future<void> _onCreateTableWithSampleData(Database db, int version) async {
|
||
// products テーブル（Product モデルと整合性を取る）+ 仕入先情報カラム追加
|
||
await db.execute('''
|
||
CREATE TABLE products (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
product_code TEXT UNIQUE NOT NULL,
|
||
name TEXT NOT NULL,
|
||
unit_price REAL DEFAULT 0.0,
|
||
quantity INTEGER DEFAULT 0,
|
||
stock INTEGER DEFAULT 0,
|
||
supplier_contact_name TEXT,
|
||
supplier_phone_number TEXT,
|
||
email TEXT,
|
||
address TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''');
|
||
|
||
// customers テーブル
|
||
await db.execute('''
|
||
CREATE TABLE customers (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
customer_code TEXT UNIQUE NOT NULL,
|
||
name TEXT NOT NULL,
|
||
address TEXT,
|
||
phone TEXT,
|
||
email TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''');
|
||
|
||
// sales テーブル
|
||
await db.execute('''
|
||
CREATE TABLE sales (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
customer_id INTEGER,
|
||
product_id INTEGER REFERENCES products(id),
|
||
quantity INTEGER NOT NULL,
|
||
unit_price REAL NOT NULL,
|
||
total_amount REAL NOT NULL,
|
||
tax_rate REAL DEFAULT 8.0,
|
||
tax_amount REAL,
|
||
grand_total REAL NOT NULL,
|
||
status TEXT DEFAULT 'completed',
|
||
payment_status TEXT DEFAULT 'paid',
|
||
invoice_number TEXT UNIQUE,
|
||
notes TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''');
|
||
|
||
// estimates テーブル（Estimate モデルと整合性を取る）
|
||
await db.execute('''
|
||
CREATE TABLE estimates (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
quote_number TEXT UNIQUE,
|
||
customer_id INTEGER REFERENCES customers(id),
|
||
product_id INTEGER REFERENCES products(id),
|
||
quantity INTEGER NOT NULL,
|
||
unit_price REAL NOT NULL,
|
||
discount_percent REAL DEFAULT 0.0,
|
||
total_amount REAL NOT NULL,
|
||
tax_rate REAL DEFAULT 8.0,
|
||
tax_amount REAL,
|
||
grand_total REAL NOT NULL,
|
||
status TEXT DEFAULT 'pending',
|
||
payment_status TEXT DEFAULT 'unpaid',
|
||
expiry_date TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''');
|
||
|
||
// インデックス
|
||
await db.execute('CREATE INDEX idx_products_code ON products(product_code)');
|
||
await db.execute('CREATE INDEX idx_customers_code ON customers(customer_code)');
|
||
|
||
// サンプル製品データを挿入（テーブル作成時に自動的に実行）
|
||
final sampleProducts = <Map<String, dynamic>>[
|
||
{'product_code': 'TEST001', 'name': 'サンプル商品 A', 'unit_price': 1000.0, 'quantity': 50, 'stock': 50},
|
||
{'product_code': 'TEST002', 'name': 'サンプル商品 B', 'unit_price': 2500.0, 'quantity': 30, 'stock': 30},
|
||
{'product_code': 'TEST003', 'name': 'サンプル商品 C', 'unit_price': 5000.0, 'quantity': 20, 'stock': 20},
|
||
];
|
||
|
||
for (final data in sampleProducts) {
|
||
await db.insert('products', data);
|
||
}
|
||
|
||
print('[DatabaseHelper] Sample products inserted');
|
||
}
|
||
|
||
/// データベースインスタンスへのアクセス
|
||
static Database get instance => _database!;
|
||
|
||
/// 製品一覧を取得（非アクティブ除外）
|
||
static Future<List<Product>> getProducts() async {
|
||
final result = await instance.query('products', orderBy: 'id DESC');
|
||
|
||
// DateTime オブジェクトを文字列に変換してから Product からマップ
|
||
return List.generate(result.length, (index) {
|
||
final item = Map<String, dynamic>.from(result[index]);
|
||
|
||
if (item['created_at'] is DateTime) {
|
||
item['created_at'] = (item['created_at'] as DateTime).toIso8601String();
|
||
}
|
||
if (item['updated_at'] is DateTime) {
|
||
item['updated_at'] = (item['updated_at'] as DateTime).toIso8601String();
|
||
}
|
||
|
||
return Product.fromMap(item);
|
||
});
|
||
}
|
||
|
||
/// 製品を ID で取得（エラー時は null を返す）
|
||
static Future<Product?> getProduct(int id) async {
|
||
final result = await instance.query(
|
||
'products',
|
||
where: 'id = ?',
|
||
whereArgs: [id],
|
||
);
|
||
|
||
if (result.isNotEmpty) {
|
||
final item = Map<String, dynamic>.from(result[0]);
|
||
|
||
if (item['created_at'] is DateTime) {
|
||
item['created_at'] = (item['created_at'] as DateTime).toIso8601String();
|
||
}
|
||
if (item['updated_at'] is DateTime) {
|
||
item['updated_at'] = (item['updated_at'] as DateTime).toIso8601String();
|
||
}
|
||
|
||
return Product.fromMap(item);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// 製品を productCode で取得（エラー時は null を返す）
|
||
static Future<Product?> getProductByCode(String code) async {
|
||
final result = await instance.query(
|
||
'products',
|
||
where: 'product_code = ?',
|
||
whereArgs: [code],
|
||
);
|
||
|
||
if (result.isNotEmpty) {
|
||
final item = Map<String, dynamic>.from(result[0]);
|
||
|
||
if (item['created_at'] is DateTime) {
|
||
item['created_at'] = (item['created_at'] as DateTime).toIso8601String();
|
||
}
|
||
if (item['updated_at'] is DateTime) {
|
||
item['updated_at'] = (item['updated_at'] as DateTime).toIso8601String();
|
||
}
|
||
|
||
return Product.fromMap(item);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// クライアント ID での顧客検索（エラー時は null を返す）
|
||
static Future<Customer?> getCustomerById(int id) async {
|
||
final result = await instance.query(
|
||
'customers',
|
||
where: 'id = ?',
|
||
whereArgs: [id],
|
||
);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Customer(
|
||
id: result[0]['id'] as int?,
|
||
customerCode: result[0]['customer_code'] as String?,
|
||
name: result[0]['name'] as String?,
|
||
address: result[0]['address'] as String?,
|
||
phone: result[0]['phone'] as String?,
|
||
email: result[0]['email'] as String?,
|
||
isInactive: (result[0]['is_inactive'] as bool?) ?? false,
|
||
);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// 顧客をコードで検索（エラー時は null を返す）
|
||
static Future<Customer?> getCustomerByCode(String code) async {
|
||
final result = await instance.query(
|
||
'customers',
|
||
where: 'customer_code = ?',
|
||
whereArgs: [code],
|
||
);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Customer(
|
||
id: result[0]['id'] as int?,
|
||
customerCode: result[0]['customer_code'] as String?,
|
||
name: result[0]['name'] as String?,
|
||
address: result[0]['address'] as String?,
|
||
phone: result[0]['phone'] as String?,
|
||
email: result[0]['email'] as String?,
|
||
isInactive: (result[0]['is_inactive'] as bool?) ?? false,
|
||
);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// 顧客を insert
|
||
static Future<int> insertCustomer(Customer customer) async {
|
||
final id = await instance.insert('customers', customer.toMap());
|
||
print('[DatabaseHelper] Customer inserted: $id');
|
||
return id;
|
||
}
|
||
|
||
/// 顧客を更新
|
||
static Future<void> updateCustomer(Customer customer) async {
|
||
await instance.update(
|
||
'customers',
|
||
{'name': customer.name, 'address': customer.address, 'phone': customer.phone, 'email': customer.email},
|
||
where: 'id = ?',
|
||
whereArgs: [customer.id],
|
||
);
|
||
print('[DatabaseHelper] Customer updated');
|
||
}
|
||
|
||
/// 顧客を削除
|
||
static Future<void> deleteCustomer(int id) async {
|
||
await instance.delete('customers', where: 'id = ?', whereArgs: [id]);
|
||
print('[DatabaseHelper] Customer deleted');
|
||
}
|
||
|
||
/// 製品を insert
|
||
static Future<int> insertProduct(Product product) async {
|
||
final id = await instance.insert('products', product.toMap());
|
||
print('[DatabaseHelper] Product inserted: $id');
|
||
return id;
|
||
}
|
||
|
||
/// 製品を更新
|
||
static Future<void> updateProduct(Product product) async {
|
||
await instance.update(
|
||
'products',
|
||
{
|
||
'name': product.name,
|
||
'unit_price': product.unitPrice,
|
||
'quantity': product.quantity,
|
||
'stock': product.stock,
|
||
'supplier_contact_name': product.supplierContactName,
|
||
'supplier_phone_number': product.supplierPhoneNumber,
|
||
'email': product.email,
|
||
'address': product.address,
|
||
},
|
||
where: 'id = ?',
|
||
whereArgs: [product.id],
|
||
);
|
||
print('[DatabaseHelper] Product updated');
|
||
}
|
||
|
||
/// 製品を削除
|
||
static Future<void> deleteProduct(int id) async {
|
||
await instance.delete('products', where: 'id = ?', whereArgs: [id]);
|
||
print('[DatabaseHelper] Product deleted');
|
||
}
|
||
|
||
/// DB をクリア（サンプルデータは保持しない）
|
||
static Future<void> clearDatabase() async {
|
||
await instance.delete('products');
|
||
await instance.delete('customers');
|
||
await instance.delete('sales');
|
||
await instance.delete('estimates');
|
||
}
|
||
|
||
/// データベースを回復（全削除 + リセット + テーブル再作成）
|
||
static Future<void> recover() async {
|
||
try {
|
||
// 既存の DB ファイルを削除
|
||
final dbPath = Directory.current.path + '/data/db/sales.db';
|
||
final file = File(dbPath);
|
||
if (await file.exists()) {
|
||
await file.delete();
|
||
print('[DatabaseHelper] recover: DB ファイルを削除');
|
||
} else {
|
||
print('[DatabaseHelper] recover: DB ファイルが見つからない');
|
||
}
|
||
|
||
// 初期化を再実行（テーブル作成時にサンプルデータが自動的に挿入される）
|
||
await init();
|
||
} catch (e) {
|
||
print('[DatabaseHelper] recover error: $e');
|
||
}
|
||
}
|
||
|
||
/// DB パスを取得
|
||
static Future<String> getDbPath() async {
|
||
return Directory.current.path + '/data/db/sales.db';
|
||
}
|
||
} |