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