/// <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); } } }