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