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