/// <summary>
/// 月始めから月終わりまでの週ごとの収入と支出データ取得
/// </summary>
/// <param name="start">月初め</param>
/// <param name="end">月終わり</param>
/// <returns>週ごとの収入と支出データ</returns>
public static List <WeekDataList> SelectDisplayList(DateTime start, DateTime end, int userId)
{
List <WeekDataList> dataLists = new List <WeekDataList>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
//収入
string sql = "select * from public.\"WeekDataList\" where \"Id\" = 0"
+ " and \"FirstDate\" >= '" + start.ToString() + "'"
+ " and \"LastDate\" < '" + end.ToString() + "'"
+ " and \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
for (int i = 0; i < table.Rows.Count; i++)
{
WeekDataList weekData = new WeekDataList();
weekData.InitDataRow(table.Rows[i]);
dataLists.Add(weekData);
}
//支出
sql = "select * from public.\"WeekDataList\" where \"Id\" = 1"
+ " and \"FirstDate\" >= '" + start.ToString() + "'"
+ " and \"LastDate\" < '" + end.ToString() + "'"
+ " and \"userId\" = " + userId;
dataSet = dBManager.GetDataSet(sql);
table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
for (int i = 0; i < table.Rows.Count; i++)
{
WeekDataList weekData = new WeekDataList();
weekData.InitDataRow(table.Rows[i]);
dataLists.Add(weekData);
}
}
catch
{
dBManager.RollBack();
dBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.WEEKDATA_MESSAGE);
return(null);
}
}
return(dataLists);
}
/// <summary>
/// 分類マスタデータリスト取得
/// </summary>
/// <param name="_id">資産ID</param>
/// <returns>分類マスタデータリスト取得</returns>
public static List <StatisticsType> SelectStatisticsTypeList(int _id, int userId)
{
List <StatisticsType> statisticsTypeList = new List <StatisticsType>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "select * from public.\"StatisticsType\" where \"id\" = "
+ _id + " and \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
for (int i = 0; i < table.Rows.Count; i++)
{
StatisticsType statisticsData = new StatisticsType(table.Rows[i]);
statisticsTypeList.Add(statisticsData);
}
}
catch
{
dBManager.RollBack();
dBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.STATISTICS_MESSAGE);
return(null);
}
}
return(statisticsTypeList);
}
/// <summary>
/// UserIdから設定情報取得
/// </summary>
/// <param name="_id">UserId</param>
/// <returns>設定情報取得</returns>
public static ConfigurationSetting SelectSetting(int _id)
{
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
ConfigurationSetting configuration;
try
{
string sql = "select * from public.\"ConfigurationSetting\" where \"Id\" = " + _id;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
configuration = new ConfigurationSetting(table.Rows[0]);
}
catch (Exception e)
{
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.USER_ERROR_MESSAGE);
return(null);
}
return(configuration);
}
}
/// <summary>
/// パスワードからユーザー情報を取得
/// </summary>
/// <param name="HashPassword">パスワード</param>
/// <returns>ユーザー情報</returns>
public static User SelectUserFromPassword(string HashPassword)
{
User user = null;
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "select * from public.\"User\" where \"Password\" = " + HashPassword;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
user = new User(table.Rows[0]);
}
catch (Exception ex)
{
dBManager.RollBack();
dBManager.Close();
string s = ex.Message;
OriginMBox.MBoxErrorOK(AppConst.USER_ERROR_MESSAGE02);
return(null);
}
}
return(user);
}
/// <summary>
/// 月ごとの収入と支出データ管理データ取得
/// </summary>
/// <returns></returns>
public static List <MonthDataList> SelectDisplayList(int year, int userId)
{
List <MonthDataList> monthDataList = new List <MonthDataList>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "select * from public.\"MonthDataList\" where \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
for (int i = 0; i < table.Rows.Count; i++)
{
DateTime date = DateTime.Parse(table.Rows[i][CREATEDATE_STR].ToString());
if (year != date.Year)
{
continue;
}
MonthDataList monthData = new MonthDataList();
monthData.InitDataRow(table.Rows[i]);
monthDataList.Add(monthData);
}
//データが12か月分あるか確認
if (monthDataList.Count != MAX_MONTH_COUNT)
{
return(null);
}
}
catch
{
dBManager.RollBack();
dBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.MONTHDATA_MESSAGE);
return(null);
}
}
return(monthDataList);
}
/// <summary>
/// 家計簿のベースデータを期間指定取得(リスト取得)
/// </summary>
public static List <HouseholdABook> SelectHouseholdABookBase(int id, DateTime start, DateTime end, int userId)
{
List <HouseholdABook> householdsList = new List <HouseholdABook>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
dBManager.Open();
dBManager.BeginTran();
//SQL文
string sql = "select * from public.householdabookbase"
+ " where data_id = " + id
+ " and creation_datetime >= '" + start.ToString("yyyy/MM/dd") + "'"
+ " and creation_datetime < '" + end.ToString("yyyy/MM/dd") + "'"
+ " and \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
for (int i = 0; i < table.Rows.Count; i++)
{
HouseholdABook household = new HouseholdABook
{
idStr = table.Rows[i][ID_STR].ToString() == "0" ? AppConst.INCOME : AppConst.SPENDING,
assets = table.Rows[i][ASSETS_STR].ToString(),
createDate = DateTime.Parse(table.Rows[i][CREATEDATE_STR].ToString()),
classification = table.Rows[i][CLASSIFICATION_STR].ToString(),
money = int.Parse(table.Rows[i][MONEY_STR].ToString()),
content = table.Rows[i][CONTENT_STR].ToString()
};
householdsList.Add(household);
}
return(householdsList);
}
catch
{
dBManager.RollBack();
dBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.BASEDATA_MESSAGE);
return(null);
}
}
}
/// <summary>
/// グラフ表示用の分類データ取得(デフォルトで期間指定)
/// </summary>
/// <param name="start">スタート日時</param>
/// <param name="end">エンド日時</param>
/// <returns></returns>
public static List <StatisticsData> SelectStatisticsDataList(int data_id, DateTime start, DateTime end, int userId)
{
List <StatisticsData> statisticsDataList = new List <StatisticsData>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "select * from public.\"StatisticsData\" where \"data_id\" = " + data_id
+ " and \"StartDate\" = '" + start.ToString() + "'"
+ " and \"EndDate\" = '" + end.ToString() + "'"
+ " and \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
if (table.Rows.Count < 1)
{
return(null);
}
for (int i = 0; i < table.Rows.Count; i++)
{
StatisticsData statisticsData = new StatisticsData(table.Rows[i]);
statisticsDataList.Add(statisticsData);
}
}
catch (Exception e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.STATISTICS_MESSAGE);
return(null);
}
}
return(statisticsDataList);
}
/// <summary>
/// 収入と支出の取得(月ごとのデータ)
/// </summary>
/// <returns></returns>
public static List <int[]> SelectMonthDatasCreate(DateTime start, DateTime end, int userId)
{
List <int[]> propList = new List <int[]>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
int dataCount = end.Month;
int[] incomeBooks = new int[dataCount];
int[] spendingBooks = new int[dataCount];
DateTime date = start;
DateTime nextDate = start.AddMonths(1);
dBManager.Open();
dBManager.BeginTran();
//収入
for (int i = 0; i < dataCount; i++)
{
string sql = "select * "
+ "from public.\"householdabookbase\" where \"householdabookbase\".data_id = 0 "
+ "and \"householdabookbase\".creation_datetime >= CAST('" + date.ToString() + "' AS TIMESTAMP) "
+ "and \"householdabookbase\".creation_datetime < CAST('" + nextDate.ToString() + "' AS TIMESTAMP) "
+ "and \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
int data = 0;
for (int j = 0; j < table.Rows.Count; j++)
{
data += int.Parse(table.Rows[j][MONEY_STR].ToString());
}
incomeBooks[i] = data;
date = nextDate;
nextDate = nextDate.AddMonths(1);
}
date = start;
nextDate = start.AddMonths(1);
//支出
for (int i = 0; i < dataCount; i++)
{
string sql = "select * "
+ "from public.\"householdabookbase\" where \"householdabookbase\".data_id = 1 "
+ "and \"householdabookbase\".creation_datetime >= CAST('" + date.ToString() + "' AS TIMESTAMP) "
+ "and \"householdabookbase\".creation_datetime < CAST('" + nextDate.ToString() + "' AS TIMESTAMP) "
+ "and \"userId\" = " + userId;
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
int data = 0;
for (int j = 0; j < table.Rows.Count; j++)
{
data += int.Parse(table.Rows[j][MONEY_STR].ToString());
}
spendingBooks[i] = data;
date = nextDate;
nextDate = nextDate.AddMonths(1);
}
propList.Add(incomeBooks);
propList.Add(spendingBooks);
return(propList);
}
catch (NpgsqlException e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.BASEDATA_MESSAGE);
return(null);
}
}
}
/// <summary>
/// 収入と支出の取得(週ごとのデータ)
/// </summary>
/// <returns></returns>
public static List <int[]> SelectHouseholdABooks(DateTime start, DateTime end, int userId)
{
List <int[]> propList = new List <int[]>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
//収入
string sql = "select * "
+ "from public.\"householdabookbase\" where \"householdabookbase\".data_id = 0 "
+ "and \"householdabookbase\".creation_datetime >= CAST('" + start.ToString() + "' AS TIMESTAMP) "
+ "and \"householdabookbase\".creation_datetime < CAST('" + end.ToString() + "' AS TIMESTAMP)"
+ "and \"userId\" = " + userId;
dBManager.Open();
dBManager.BeginTran();
DataSet dataSet = dBManager.GetDataSet(sql);
DataTable table = dataSet.Tables[0];
int dataCount = end.AddDays(-1).Day;
int[] incomeBooks = new int[dataCount];
int count = 0;
for (int i = 0; i < dataCount; i++)
{
if (table.Rows.Count == 0)
{
break;
}
if (table.Rows.Count <= count)
{
count = table.Rows.Count - 1;
}
if (table.Rows[count] == null)
{
continue;
}
string dateStr = table.Rows[count]["creation_datetime"].ToString();
DateTime time = DateTime.Parse(dateStr);
if (i == time.Day - 1)
{
incomeBooks[i] = int.Parse(table.Rows[count]["amountofmoney"].ToString());
count++;
}
}
//支出
sql = "select * "
+ "from public.\"householdabookbase\" where \"householdabookbase\".data_id = 1 "
+ "and \"householdabookbase\".creation_datetime >= CAST('" + start.ToString() + "' AS TIMESTAMP) "
+ "and \"householdabookbase\".creation_datetime < CAST('" + end.ToString() + "' AS TIMESTAMP)"
+ "and \"userId\" = " + userId;
dataSet = dBManager.GetDataSet(sql);
table = dataSet.Tables[0];
int[] spendingBooks = new int[dataCount];
count = 0;
for (int i = 0; i < dataCount; i++)
{
if (table.Rows.Count == 0)
{
break;
}
if (table.Rows.Count <= count)
{
count = table.Rows.Count - 1;
}
if (table.Rows[count] == null)
{
continue;
}
string dateStr = table.Rows[count]["creation_datetime"].ToString();
DateTime time = DateTime.Parse(dateStr);
if (i == time.Day - 1)
{
spendingBooks[i] = int.Parse(table.Rows[count]["amountofmoney"].ToString());
count++;
}
}
propList.Add(incomeBooks);
propList.Add(spendingBooks);
return(propList);
}
catch (NpgsqlException e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.BASEDATA_MESSAGE);
return(null);
}
}
}
