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