public static async Task <List <Category> > GetAllCategoryByType(ItemType type)
{
var ans = new List <Category>();
using (var connection = await DaoUtil.GetSqlConnection())
{
await connection.OpenAsync();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"SELECT id, name, type FROM category WHERE type = @type"
};
command.Parameters.AddWithValue("@type", (int)type);
var query = await command.ExecuteReaderAsync();
while (await query.ReadAsync())
{
ans.Add(new Category
{
Id = query.GetInt32(0),
Name = query.GetString(1),
Type = (ItemType)query.GetInt32(2)
});
}
connection.Close();
}
return(ans);
}
public static async void Update(AccountItem item)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
connection.Open();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"UPDATE accountItem
SET year = @year,
month = @month ,
day = @day,
remark = @remark,
money = @money,
category_id = @categoryId
WHERE id =@id"
};
command.Parameters.AddWithValue("@year", item.Year);
command.Parameters.AddWithValue("@month", item.Month);
command.Parameters.AddWithValue("@day", item.Day);
command.Parameters.AddWithValue("@remark", item.Remark);
command.Parameters.AddWithValue("@money", item.Money);
command.Parameters.AddWithValue("@categoryId", item.CategoryId);
command.Parameters.AddWithValue("@id", item.Id);
await command.ExecuteNonQueryAsync();
connection.Close();
}
}
public static async Task <List <AccountItem> > GetAccountItemOrderByDate(int year, int month)
{
var ans = new List <AccountItem>();
using (var connection = await DaoUtil.GetSqlConnection())
{
await connection.OpenAsync();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"
SELECT accountItem.id, year,month,day, remark, money, category_id, c.name, c.type
FROM accountItem
INNER JOIN category AS c on c.id = accountItem.category_id
WHERE year = @year and month = @month
ORDER BY year,month,day DESC"
};
command.Parameters.AddWithValue("@year", year);
command.Parameters.AddWithValue("@month", month);
var query = await command.ExecuteReaderAsync();
while (await query.ReadAsync())
{
ans.Add(new AccountItem
{
Id = query.GetInt32(0),
Year = query.GetInt32(1),
Month = query.GetInt32(2),
Day = query.GetInt32(3),
Remark = query.GetString(4),
Money = query.GetDouble(5),
CategoryId = query.GetInt32(6),
Category = new Category
{
Id = query.GetInt32(6),
Name = query.GetString(7),
Type = (ItemType)query.GetInt32(8)
}
});
}
connection.Close();
}
return(ans);
}
public static async void DeleteById(int accountItemId)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
connection.Open();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"DELETE FROM accountItem WHERE id =@accountItemId"
};
command.Parameters.AddWithValue("@accountItemId", accountItemId);
await command.ExecuteNonQueryAsync();
connection.Close();
}
}
public static async void DeleteCategoryById(int categoryId)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
await connection.OpenAsync();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"DELETE FROM category WHERE id = @categoryId"
};
command.Parameters.AddWithValue("@categoryId", categoryId);
await command.ExecuteNonQueryAsync();
connection.Close();
}
}
public static async Task <bool> ExistByCategoryId(int categoryId)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
connection.Open();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"SELECT COUNT(*) FROM accountItem WHERE category_id = @categoryId"
};
command.Parameters.AddWithValue("@categoryId", categoryId);
var query = await command.ExecuteReaderAsync();
await query.ReadAsync();
return(query.GetInt32(0) != 0);
}
}
public static async void AddNewCategory(string categoryName, ItemType type)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
await connection.OpenAsync();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"INSERT INTO category(name, type) VALUES (@name,@type)"
};
command.Parameters.AddWithValue("@name", categoryName);
command.Parameters.AddWithValue("@type", type);
await command.ExecuteReaderAsync();
connection.Close();
}
}
public static async Task <AccountItem> GetAccountItemById(int accountItemId)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
await connection.OpenAsync();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"
SELECT accountItem.id, year,month,day, remark, money, category_id, c.name, c.type
FROM accountItem
INNER JOIN category AS c on c.id = accountItem.category_id
WHERE accountItem.id = @accountItemId
ORDER BY year,month,day DESC"
};
command.Parameters.AddWithValue("@accountItemId", accountItemId);
var query = await command.ExecuteReaderAsync();
if (await query.ReadAsync())
{
return(new AccountItem
{
Id = query.GetInt32(0),
Year = query.GetInt32(1),
Month = query.GetInt32(2),
Day = query.GetInt32(3),
Remark = query.GetString(4),
Money = query.GetDouble(5),
CategoryId = query.GetInt32(6),
Category = new Category
{
Id = query.GetInt32(6),
Name = query.GetString(7),
Type = (ItemType)query.GetInt32(8)
}
});
}
return(null);
}
}
public static async Task <List <AccountItem> > GetAccountByDate(DateTime date)
{
var ans = new List <AccountItem>();
using (var connection = await DaoUtil.GetSqlConnection())
{
connection.Open();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"
SELECT id,year,month,day,remark,money,category_id FROM accountItem
WHERE year =@year and month = @month and day = @day"
};
command.Parameters.AddWithValue("@year", date.Year);
command.Parameters.AddWithValue("@month", date.Month);
command.Parameters.AddWithValue("@day", date.Day);
var query = await command.ExecuteReaderAsync();
while (await query.ReadAsync())
{
ans.Add(new AccountItem
{
Id = query.GetInt32(0),
Year = query.GetInt32(1),
Month = query.GetInt32(2),
Day = query.GetInt32(3),
Remark = query.GetString(4),
Money = query.GetDouble(5),
CategoryId = query.GetInt32(6),
});
}
connection.Close();
}
return(ans);
}
/**
* 添加新的账单记录
*/
public static async void AddAccount(AccountItem item)
{
using (var connection = await DaoUtil.GetSqlConnection())
{
connection.Open();
var command = new SqliteCommand
{
Connection = connection,
CommandText = @"
INSERT INTO accountItem(year,month,day, remark, money, category_id)
VALUES (@year,@month,@day, @Remark, @Money, @CategoryId)"
};
command.Parameters.AddWithValue("@year", item.Year);
command.Parameters.AddWithValue("@month", item.Month);
command.Parameters.AddWithValue("@day", item.Day);
command.Parameters.AddWithValue("@Remark", item.Remark);
command.Parameters.AddWithValue("@Money", item.Money);
command.Parameters.AddWithValue("@CategoryId", item.CategoryId);
await command.ExecuteReaderAsync();
connection.Close();
}
}
