/// <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="dataLists">週ごとの収入と支出データ</param>
/// <returns>表示用文字列データ作成</returns>
public static List <string[]> GetDisplayDataList(List <MonthDataList> dataLists)
{
List <string[]> vs = new List <string[]>();
try
{
if (dataLists == null)
{
return(null);
}
for (int i = 0; i < MAX_MONTH_COUNT; i++)
{
MonthDataList monthData = dataLists[i];
string[] dataListStr =
{
monthData.Id + "月",
monthData.Income.ToString(),
monthData.Spending.ToString()
};
vs.Add(dataListStr);
}
}
catch
{
//string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.MONTHDATA_MESSAGE);
return(null);
}
return(vs);
}
/// <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>
/// <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);
}
private void BtnToastSend_Click(object sender, EventArgs e)
{
if (CbToastUseFlg.Checked)
{
if (int.Parse(TbAddHour.Text) == 0)
{
var result = OriginMBox.MBoxInfoOKCancel(AppConst.TOAST_HOUR_ERR);
if (result != DialogResult.OK)
{
return;
}
}
else if (int.Parse(TbAddMinute.Text) == 0)
{
var result = OriginMBox.MBoxInfoOKCancel(AppConst.TOAST_MINUTE_ERR);
if (result != DialogResult.OK)
{
return;
}
}
CreateScheduleToast();
OriginMBox.MBoxInfoOK(AppConst.TOAST_SUCCESS);
Close();
}
else
{
OriginMBox.MBoxErrorOK(AppConst.TOAST_USEFLG_OFF);
}
}
/// <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);
}
private void TbPassword_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
int userId = int.Parse(config.AppSettings.Settings["UserId"].Value);
// ログイン認証チェック
User user = User.SelectUserFromId(userId);
bool isSuccess = PasswordManager.VerifyPassword(user.Password, TbPassword.Text, user.Salt);
// ログイン合否によって処理分岐
if (isSuccess)
{
OriginMBox.MBoxInfoOK(AppConst.LOGIN_SUCCESS);
controller.User = user;
Hide();
MainHouseholdABookForm bookForm = new MainHouseholdABookForm(controller);
if (bookForm.ShowDialog() != DialogResult.OK)
{
Close();
}
}
else
{
OriginMBox.MBoxErrorOK(AppConst.LOGIN_ERROR);
}
}
}
/// <summary>
/// 統計確認データマスタにデータが存在するか確認
/// </summary>
/// <param name="typeList">統計確認データマスタ</param>
/// <param name="moneyPairs">分類ごとの金額のデータテーブル</param>
/// <returns>統計確認データマスタにデータが存在するか確認</returns>
public static bool IsStatisticsType(List <StatisticsType> typeList, out Dictionary <string, int> moneyPairs)
{
try
{
if (typeList.Count < 0)
{
moneyPairs = new Dictionary <string, int>();
return(false);
}
int id = typeList[0].Id;
moneyPairs = new Dictionary <string, int>();
//データ初期化
for (int i = 0; i < typeList.Count; i++)
{
var data = typeList[i];
if (!moneyPairs.ContainsKey(data.Classification))
{
moneyPairs.Add(data.Classification, 0);
}
}
return(true);
}
catch (Exception e)
{
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.STATISTICSTYPE_MESSAGE);
moneyPairs = new Dictionary <string, int>();
return(false);
}
}
/// <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>
/// PDFファイル名取得
/// </summary>
/// <param name="dateStr">今日日付</param>
/// <returns>PDFファイル名取得</returns>
public static string GetPDFFile(string dateStr)
{
string fileName = "";
try
{
var pdfFileList = Directory.GetFiles(PDF_DIR, dateStr + "*.pdf", SearchOption.TopDirectoryOnly).ToList();
if (pdfFileList.Count == 0)
{
return(null);
}
List <int> timeList = new List <int>();
foreach (var file in pdfFileList)
{
var strs = Path.GetFileNameWithoutExtension(file).Split('_');
if (!int.TryParse(strs[1], out int time))
{
continue;
}
timeList.Add(time);
}
var nowTime = timeList.Max();
fileName = pdfFileList.Where(x => Path.GetFileName(x).Substring(0, 15) == dateStr + "_" + nowTime).FirstOrDefault();
}
catch (Exception e)
{
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.PDF_FAILURE_MESSAGE2);
}
return(fileName);
}
/// <summary>
/// 最新版のデータか判定
/// </summary>
/// <param name="monthDataLst">1年間のデータリスト</param>
/// <param name="dataLst">収入と支出リスト</param>
/// <returns>最新データかどうか</returns>
public static bool IsNewMonthDataList(List <WeekDataList> weekDataLst, List <int[]> dataLst)
{
try
{
if (weekDataLst.Count() != dataLst[0].Count())
{
return(false);
}
if (weekDataLst.Count() != dataLst[1].Count())
{
return(false);
}
for (int i = 0; i < weekDataLst.Count; i++)
{
if (weekDataLst[i].Id == 0 && weekDataLst[i].WeekData != dataLst[0][i])
{
return(false);
}
if (weekDataLst[i].Id == 1 && weekDataLst[i].WeekData != dataLst[1][i])
{
return(false);
}
}
}
catch (Exception ex)
{
string s = ex.Message;
OriginMBox.MBoxErrorOK(AppConst.MONTHDATA_MESSAGE2);
return(false);
}
return(true);
}
/// <summary>
/// 列（コラム）ヘッダの作成
/// </summary>
/// <param name="pairs">リスト表示の動的配列</param>
/// <returns>ヘッダー</returns>
private ColumnHeader[] CreateColumnHeader(Dictionary <string, int> pairs)
{
ColumnHeader[] colHeaderList = null;
try
{
List <ColumnHeader> headersList = new List <ColumnHeader>();
// 列（コラム）ヘッダの作成
int count = 0;
foreach (KeyValuePair <string, int> valuePair in pairs)
{
ColumnHeader header = new ColumnHeader(count)
{
Text = valuePair.Key,
Width = valuePair.Value
};
headersList.Add(header);
count++;
}
colHeaderList = headersList.ToArray();
return(colHeaderList);
}
catch
{
OriginMBox.MBoxErrorOK(AppConst.LIST_HEADER_MESSAGE);
return(null);
}
}
/// <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>
/// 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="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>
/// カレンダーDBデータ作成
/// </summary>
/// <param name="count">作成回数</param>
/// <param name="incomes">収入</param>
/// <param name="spendings">支出</param>
/// <returns>カレンダーDBデータリスト</returns>
public static List <Calender> CreateCalenderList(int count, int[] incomes, int[] spendings, DateTime nowTime, int userId)
{
List <Calender> calenders = new List <Calender>();
try
{
int id = 1;
for (int i = 0; i < count; i++)
{
Calender calender = new Calender
{
Id = id,
Income = incomes[i],
Spending = spendings[i],
CreateDate = nowTime,
UserId = userId
};
calenders.Add(calender);
id++;
}
}
catch
{
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE2);
return(null);
}
return(calenders);
}
/// <summary>
/// 最新版のカレンダー情報か判定
/// </summary>
/// <param name="calenderLst">カレンダーリスト</param>
/// <param name="dataLst">収入と支出リスト</param>
/// <returns>最新データかどうか</returns>
public static bool IsNewCalenderData(List <Calender> calenderLst, List <int[]> dataLst)
{
try
{
if (calenderLst.Count() != dataLst[0].Count())
{
return(false);
}
if (calenderLst.Count() != dataLst[1].Count())
{
return(false);
}
for (int i = 0; i < calenderLst.Count; i++)
{
if (calenderLst[i].Income != dataLst[0][i])
{
return(false);
}
if (calenderLst[i].Spending != dataLst[1][i])
{
return(false);
}
}
}
catch (Exception ex)
{
string s = ex.Message;
OriginMBox.MBoxErrorOK(AppConst.CALENDER_MESSAGE2);
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>
/// 統計確認リストビューデータ表示(PDF用)
/// </summary>
/// <param name="statisticsListData">統計割合データ</param>
/// <returns>データ表示可否</returns>
private DataTable MakeDataTable(List <StatisticsListData> statisticsListData)
{
try
{
DataTable table = new DataTable();
foreach (KeyValuePair <string, int> valuePair in STATISTICS_HEADERS_DICT)
{
table.Columns.Add(valuePair.Key);
}
foreach (var data in statisticsListData)
{
table.Rows.Add(data.GetStatisticsListDataStrs());
}
return(table);
}
catch (Exception e)
{
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.LIST_ITEM_MESSAGE);
return(null);
}
}
/// <summary>
/// iniファイルの文字列リスト取得
/// </summary>
/// <returns></returns>
private static List <string> IniStrList(string section, NewDataCreater.DisplayBtnType displayBtnType)
{
List <string> iniStrList = new List <string>();
try
{
switch (displayBtnType)
{
case NewDataCreater.DisplayBtnType.BtnDate:
//何も返さない
return(null);
case NewDataCreater.DisplayBtnType.Assets:
case NewDataCreater.DisplayBtnType.IncomeClassification:
case NewDataCreater.DisplayBtnType.SpendingClassification:
iniStrList = GetAllValue(section);
break;
case NewDataCreater.DisplayBtnType.Money:
//何も返さない
return(null);
case NewDataCreater.DisplayBtnType.Content:
//何も返さない
return(null);
}
return(iniStrList);
}
catch
{
OriginMBox.MBoxErrorOK(AppConst.INI_MESSAGE2);
return(null);
}
}
/// <summary>
/// PNGファイル名取得
/// </summary>
/// <param name="dateStr">今日日付</param>
/// <returns>PNGファイル名取得</returns>
public static string GetImageFile(string dateStr)
{
string fileName = "";
try
{
var pngFileList = Directory.GetFiles(AppConst.GRAPH_IMAGE_FILE_DIR, dateStr + "*pie.png", SearchOption.TopDirectoryOnly).ToList();
if (pngFileList.Count == 0)
{
return(null);
}
List <long> timeList = new List <long>();
foreach (var file in pngFileList)
{
var strs = Path.GetFileNameWithoutExtension(file).Split('_');
if (!long.TryParse(strs[0], out long time))
{
continue;
}
timeList.Add(time);
}
var nowTime = timeList.Max();
fileName = pngFileList.Where(x => Path.GetFileName(x).Substring(0, 14) == nowTime.ToString()).FirstOrDefault();
}
catch (Exception e)
{
string s = e.Message;
OriginMBox.MBoxErrorOK(AppConst.PNG_FAILURE_MESSAGE);
}
return(fileName);
}
static void Main()
{
//二重起動防止処理
//Mutex名を決める（必ずアプリケーション固有の文字列に変更すること！）
string mutexStr = Application.ProductName;
//Mutexオブジェクトを作成する
Mutex mutex = new Mutex(false, mutexStr);
bool hasHandle = false;
try
{
//ミューテックスの所有権を要求する
hasHandle = mutex.WaitOne(0, false);
if (hasHandle == false)
{
OriginMBox.MBoxWarningOK(AppConst.MUTEX_MESSAGE);
return;
}
AppController appController = new AppController();
appController.Init();
if (string.IsNullOrEmpty(ConfigurationManager.AppSettings["UserId"].ToString()) &&
string.IsNullOrEmpty(ConfigurationManager.AppSettings["HashPassword"].ToString()))
{
LoginCreate loginCreate = new LoginCreate(appController);
if (loginCreate.ShowDialog() == DialogResult.OK)
{
OriginMBox.MBoxInfoOK(AppConst.USER_MESSAGE);
}
else
{
return;
}
}
Application.EnableVisualStyles();
Application.Run(GetMainForm(appController));
}
catch (AbandonedMutexException e)
{
string s = e.Message;
OriginMBox.MBoxErrorOK(s);
//別のアプリケーションがミューテックスを解放しないで終了した時
hasHandle = true;
}
finally
{
if (hasHandle)
{
//ミューテックスを解放する
mutex.ReleaseMutex();
}
mutex.Close();
}
}
/// <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);
}
}
private void BtnToastTestSend_Click(object sender, EventArgs e)
{
if (CbToastUseFlg.Checked)
{
CreateTestToast();
}
else
{
OriginMBox.MBoxErrorOK(AppConst.TOAST_USEFLG_OFF);
}
}
/// <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);
}