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