/// <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="_id">UserId</param>
/// <param name="isToast">トースト通知ありorなし</param>
/// <returns>通知設定</returns>
public static ConfigurationSetting UpdateConfigSettingFromIsToast(int _id, bool isToast)
{
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "UPDATE public.\"ConfigurationSetting\" "
+ "SET \"IsToast\" = " + isToast
+ " where \"Id\" = " + _id;
dBManager.Open();
dBManager.BeginTran();
dBManager.ExecuteNonQuery(sql);
dBManager.CommitTran();
}
catch (Exception ex)
{
dBManager.RollBack();
dBManager.Close();
string s = ex.Message;
OriginMBox.MBoxErrorOK(AppConst.USER_ERROR_MESSAGE02);
return(null);
}
}
ConfigurationSetting setting = SelectSetting(_id);
return(setting);
}
/// <summary>
/// 家計簿のベースデータ新規登録
/// </summary>
/// <returns></returns>
public static bool InsertHouseholdABookBase(int id, DateTime date, string assets, string classifcation, int money, string content, int userId)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
//SQL文
string sql = "INSERT INTO \"householdabookbase\" VALUES("
+ id + ",CAST('" + date.ToString() + "' AS TIMESTAMP),'"
+ assets + "','" + classifcation + "'," + money + ",'" + content + ",'" + userId + "')";
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
npgSqlDBManager.ExecuteNonQuery(sql);
npgSqlDBManager.CommitTran();
}
catch
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.NEWDATA_MESSAGE);
return(false);
}
}
return(true);
}
/// <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>
/// 週ごとの収入と支出データ登録更新
/// </summary>
/// <returns>成功か失敗</returns>
public static bool UpdateWeekDataList(List <WeekDataList> weekDataLists)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
for (int i = 0; i < weekDataLists.Count; i++)
{
//SQL文
string strSQL = "Update public.\"WeekDataList\" set \"WeekData\" = " + weekDataLists[i].WeekData
+ " where \"Id\" = " + weekDataLists[i].Id +
" and \"FirstDate\" = '" + weekDataLists[i].FirstDate.ToString("yyyy/MM/dd") + "' " +
" and \"LastDate\" = '" + weekDataLists[i].LastDate.ToString("yyyy/MM/dd") + "'" +
" and \"userId\" = " + weekDataLists[i].UserId;
npgSqlDBManager.ExecuteNonQuery(strSQL);
}
npgSqlDBManager.CommitTran();
}
catch
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE2);
return(false);
}
}
return(true);
}
/// <summary>
/// 週ごとの収入と支出データ登録
/// </summary>
/// <returns>成功か失敗</returns>
public static bool InsertWeekDataList(List <WeekDataList> weekDataLists)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
for (int i = 0; i < weekDataLists.Count; i++)
{
//SQL文
string strSQL = "INSERT INTO public.\"WeekDataList\" VALUES( "
+ weekDataLists[i].Id + " , " + weekDataLists[i].WeekData + " , "
+ "CAST('" + weekDataLists[i].FirstDate.ToString("yyyy/MM/dd") + "' AS TIMESTAMP)" + ", "
+ "CAST('" + weekDataLists[i].LastDate.ToString("yyyy/MM/dd") + "' AS TIMESTAMP)" + ", "
+ weekDataLists[i].UserId + ")";
npgSqlDBManager.ExecuteNonQuery(strSQL);
}
npgSqlDBManager.CommitTran();
}
catch
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE2);
return(false);
}
}
return(true);
}
/// <summary>
/// 月ごとの収入と支出データ更新
/// </summary>
/// <returns>成功か失敗</returns>
public static bool UpdateMonthDataList(List <MonthDataList> monthDataLists)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
//SQL文
foreach (var pair in monthDataLists)
{
string strSQL = "Update public.\"MonthDataList\" set \"Income\" = " + pair.Income
+ ", \"Spending\" = " + pair.Spending
+ " where \"Id\" = " + pair.Id
+ " and \"CreateDate\" = '" + pair.CreateDate.ToString("yyyy/MM/dd") + "'"
+ " and \"userId\" = " + pair.UserId;
npgSqlDBManager.ExecuteNonQuery(strSQL);
}
npgSqlDBManager.CommitTran();
}
catch
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.MONTHDATA_MESSAGE2);
return(false);
}
}
return(true);
}
/// <summary>
/// 月ごとの収入と支出データ登録
/// </summary>
/// <returns>成功か失敗</returns>
public static bool InsertMonthDataList(List <MonthDataList> monthDataLists)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
for (int i = 0; i < monthDataLists.Count; i++)
{
//SQL文
string strSQL = "INSERT INTO public.\"MonthDataList\" VALUES( "
+ monthDataLists[i].Id + " , " + monthDataLists[i].Income + " , "
+ monthDataLists[i].Spending + " , "
+ "CAST('" + monthDataLists[i].CreateDate.ToString("yyyy/MM/dd") + "' AS TIMESTAMP) , "
+ monthDataLists[i].UserId + ")";
npgSqlDBManager.ExecuteNonQuery(strSQL);
}
npgSqlDBManager.CommitTran();
}
catch
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.MONTHDATA_MESSAGE2);
return(false);
}
}
return(true);
}
/// <summary>
/// パスワード更新
/// </summary>
/// <param name="_id">ID</param>
/// <param name="userName">ユーザー名</param>
/// <param name="password">ハッシュパスワード</param>
/// <param name="salt">ソルト</param>
/// <returns>ユーザー情報</returns>
public static User UpdatePasswordAndSalt(int _id, string password, string salt)
{
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "UPDATE public.\"User\" "
+ "SET \"Password\" = '" + password + "', "
+ "\"Salt\" = '" + salt + "'"
+ " where \"Id\" = " + _id;
dBManager.Open();
dBManager.BeginTran();
dBManager.ExecuteNonQuery(sql);
dBManager.CommitTran();
}
catch (Exception ex)
{
dBManager.RollBack();
dBManager.Close();
string s = ex.Message;
OriginMBox.MBoxErrorOK(AppConst.USER_ERROR_MESSAGE02);
return(null);
}
}
User user = SelectUserFromId(_id);
return(user);
}
/// <summary>
/// ユーザー情報作成時に設定情報も作成
/// </summary>
/// <param name="_id">UserID</param>
/// <param name="styleColor">スタイルの色</param>
/// <returns>設定情報取得</returns>
public static ConfigurationSetting InsertConfigSetting(int _id, int styleColor)
{
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
dBManager.Open();
dBManager.BeginTran();
string sql = "INSERT INTO public.\"ConfigurationSetting\" "
+ "VALUES(" + _id + ", "
+ "'" + DateTime.Now.ToString() + "', "
+ "'" + styleColor + "', "
+ false + ")";
dBManager.ExecuteNonQuery(sql);
dBManager.CommitTran();
}
catch (Exception e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.USER_MESSAGE);
return(null);
}
}
// 作成したユーザー情報取得
ConfigurationSetting setting = SelectSetting(_id);
return(setting);
}
/// <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>
/// <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>
/// <returns></returns>
public static List <int> SelectPropManageList(int userId)
{
List <int> propList = new List <int>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
//収入
string sql = "select SUM(\"amountofmoney\") AS Total from public.\"householdabookbase\" where \"householdabookbase\".data_id = 0"
+ " and \"userId\" = " + userId;
dBManager.Open();
dBManager.BeginTran();
NpgsqlDataReader reader = dBManager.ExecuteQuery(sql);
int total = 0;
while (reader.Read())
{
total = int.Parse(reader["Total"].ToString());
propList.Add(total);
}
reader.Close();
//支出
sql = "select SUM(\"amountofmoney\") AS Total2 from public.\"householdabookbase\" where \"householdabookbase\".data_id = 1"
+ " and \"userId\" = " + userId;
reader = dBManager.ExecuteQuery(sql);
int total2 = 0;
while (reader.Read())
{
total2 = int.Parse(reader["Total2"].ToString());
propList.Add(total2);
}
reader.Close();
//合計
int sum = total - total2;
propList.Add(sum);
return(propList);
}
catch//(NpgsqlException e)
{
dBManager.RollBack();
dBManager.Close();
//string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.NEWDATA_MESSAGE);
return(null);
}
}
}
/// <summary>
/// カレンダーのベースデータ取得
/// </summary>
/// <param name="end"></param>
/// <returns></returns>
public static List <Calender> SelectCalender(DateTime start, DateTime end, out DateTime registerDate, int userId)
{
List <Calender> calenderList = new List <Calender>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
//カレンダーDBに該当データが存在するか確認
string sql = "select * from public.\"Calender\""
+ " where \"Calender\".\"Create_Date\" >= '" + start.ToString("yyyy/MM/dd") + "'"
+ " and \"Calender\".\"Create_Date\" < '" + end.ToString("yyyy/MM/dd") + "'"
+ " and \"Calender\".\"UserId\" = " + userId;
dBManager.Open();
dBManager.BeginTran();
NpgsqlDataReader reader = dBManager.ExecuteQuery(sql);
//データがある場合はデータ取得
registerDate = DateTime.MinValue;
int count = 0;
while (reader.Read())
{
if (registerDate == DateTime.MinValue && count == 0)
{
count++;
registerDate = DateTime.Parse(reader["Create_Date"].ToString());
}
Calender calender1 = new Calender
{
Id = int.Parse(reader["Id"].ToString()),
Income = int.Parse(reader["Income"].ToString()),
Spending = int.Parse(reader["Spending"].ToString()),
CreateDate = DateTime.Parse(reader["Create_Date"].ToString()),
UserId = int.Parse(reader["UserId"].ToString())
};
calenderList.Add(calender1);
}
reader.Close();
}
catch
{
dBManager.RollBack();
dBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE);
registerDate = DateTime.MinValue;
return(null);
}
return(calenderList);
}
}
/// <summary>
/// 新規ユーザー情報作成
/// </summary>
/// <param name="userName">ユーザー名</param>
/// <param name="userNameKana">ユーザー名カナ</param>
/// <param name="hashPassword">パスワード</param>
/// <param name="isOnce">オートログイン有り無し</param>
/// <param name="salt">ソルト</param>
/// <returns></returns>
public static User InsertUser(string userName, string userNameKana, string hashPassword, bool isOnce, string salt)
{
int id = 1;
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
string sql = "SELECT MAX(\"Id\") AS max FROM public.\"User\"";
dBManager.Open();
dBManager.BeginTran();
using (NpgsqlDataReader reader = dBManager.ExecuteQuery(sql))
{
while (reader.Read())
{
if (string.IsNullOrEmpty(reader["max"].ToString()))
{
break;
}
id = int.Parse(reader["max"].ToString()) + 1;
}
}
sql = "";
sql = "INSERT INTO public.\"User\" "
+ "VALUES(" + id + ", "
+ "'" + DateTime.Now.ToString() + "', "
+ "'" + userName + "', "
+ "'" + userNameKana + "', "
+ "'" + hashPassword + "', "
+ isOnce + ", "
+ "'" + salt + "')";
dBManager.ExecuteNonQuery(sql);
dBManager.CommitTran();
}
catch (Exception e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.USER_MESSAGE);
return(null);
}
}
// 作成したユーザー情報取得
User user = SelectUserFromId(id);
return(user);
}
public static bool UpdateStatisticsData(List <HouseholdABookBase.HouseholdABook> householdABookList,
DateTime start, DateTime end, int userId)
{
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
if (householdABookList.Count <= 0)
{
return(false);
}
int id = householdABookList[0].idStr == AppConst.INCOME ? AppConst.INCOME_VALUE : AppConst.SPENDING_VALUE;
Dictionary <string, int> moneyParam = StatisticsType.GetMoneysParam(householdABookList, userId);
if (moneyParam == null && moneyParam.Count < 0)
{
return(false);
}
dBManager.Open();
dBManager.BeginTran();
foreach (var moneyData in moneyParam)
{
if (moneyData.Value > 0)
{
string sql = "UPDATE public.\"StatisticsData\" "
+ "SET \"createDate\" = '" + DateTime.Now.ToString() + "', "
+ "\"money\" = " + moneyData.Value
+ " where \"data_id\" = " + id
+ " and \"Classification\" = '" + moneyData.Key + "'"
+ " and \"StartDate\" = '" + start + "' and \"EndDate\" = '" + end + "'"
+ " and \"userId\" = " + userId;
dBManager.ExecuteNonQuery(sql);
}
}
dBManager.CommitTran();
}
catch (Exception e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.STATISTICS_MESSAGE2);
return(false);
}
}
return(true);
}
public static bool InsertStatisticsData(List <HouseholdABookBase.HouseholdABook> householdABookList,
DateTime start, DateTime end, int userId)
{
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
if (householdABookList.Count <= 0)
{
return(false);
}
int id = householdABookList[0].idStr == AppConst.INCOME ? AppConst.INCOME_VALUE : AppConst.SPENDING_VALUE;
Dictionary <string, int> moneyParam = StatisticsType.GetMoneysParam(householdABookList, userId);
if (moneyParam == null && moneyParam.Count < 0)
{
return(false);
}
dBManager.Open();
dBManager.BeginTran();
foreach (var moneyData in moneyParam)
{
if (moneyData.Value > 0)
{
string sql = "INSERT INTO public.\"StatisticsData\" "
+ "VALUES(" + id + ", "
+ "'" + DateTime.Now + "', "
+ "'" + moneyData.Key + "', "
+ moneyData.Value + ", '" + start + "', '" + end + "', "
+ userId + ")";
dBManager.ExecuteNonQuery(sql);
}
}
dBManager.CommitTran();
}
catch (Exception e)
{
dBManager.RollBack();
dBManager.Close();
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.STATISTICS_MESSAGE2);
return(false);
}
}
return(true);
}
/// <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>
/// <returns></returns>
public static bool UpdateCalender(List <Calender> calenderList, DateTime date)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
if (calenderList.Count() < 1 && date == DateTime.MinValue)
{
foreach (var pair in calenderList)
{
string strSQL = "Insert into public.\"Calender\" VALUES( "
+ pair.Id + " , " + pair.Income + " , "
+ pair.Spending + " , " + pair.CreateDate + " , "
+ pair.UserId + ")";
npgSqlDBManager.ExecuteNonQuery(strSQL);
}
}
else
{
foreach (var pair in calenderList)
{
string strSQL = "Update public.\"Calender\" set \"Income\" = " + pair.Income
+ ", \"Spending\" = " + pair.Spending
+ " where \"Id\" = " + pair.Id
+ " and \"Create_Date\" = '" + date.ToString("yyyy/MM/dd") + "'"
+ " and \"UserId\" = " + pair.UserId;
npgSqlDBManager.ExecuteNonQuery(strSQL);
}
}
npgSqlDBManager.CommitTran();
}
catch//(Npgsql.NpgsqlException e)
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
//string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE2);
return(false);
}
}
return(true);
}
/// <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 bool InsertInitCalender(int count, DateTime time, int userId)
{
using (NpgSqlDBManager npgSqlDBManager = new NpgSqlDBManager())
{
try
{
int id = 1;
int income = 0;
int spending = 0;
npgSqlDBManager.Open();
npgSqlDBManager.BeginTran();
for (int i = 0; i < count; i++)
{
//SQL文
string strSQL = "INSERT INTO public.\"Calender\" VALUES( "
+ id + " , " + income + " , " + spending + " , "
+ "CAST('" + time.ToString("yyyy/MM/dd") + "' AS TIMESTAMP) , "
+ userId + ")";
npgSqlDBManager.ExecuteNonQuery(strSQL);
id++;
}
npgSqlDBManager.CommitTran();
}
catch
{
npgSqlDBManager.RollBack();
npgSqlDBManager.Close();
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE2);
return(false);
}
}
return(true);
}
/// <summary>
/// 期間ごとの収入と支出と合計の取得(上限日時(end)はStartの1ヶ月)
/// </summary>
/// <returns></returns>
public static List <int> SelectPropManageList(DateTime start, DateTime end, int userId)
{
List <int> propList = new List <int>();
using (NpgSqlDBManager dBManager = new NpgSqlDBManager())
{
try
{
//収入
string sql = "select SUM(\"amountofmoney\") AS Total "
+ "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();
NpgsqlDataReader reader = dBManager.ExecuteQuery(sql);
int total = 0;
while (reader.Read())
{
if (!int.TryParse(reader["Total"].ToString(), out total))
{
total = 0;
}
propList.Add(total);
}
reader.Close();
//支出
sql = "select SUM(\"amountofmoney\") AS Total2 "
+ "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;
reader = dBManager.ExecuteQuery(sql);
int total2 = 0;
while (reader.Read())
{
if (!int.TryParse(reader["Total2"].ToString(), out total2))
{
total2 = 0;
}
propList.Add(total2);
}
reader.Close();
//合計
int sum = total - total2;
propList.Add(sum);
return(propList);
}
catch (NpgsqlException ex)
{
dBManager.RollBack();
dBManager.Close();
string s = ex.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);
}
}
}
/// <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);
}
}
}
