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