//查询账目的最后日期
public static DataTable GetAccEndDate()
{
string sql = string.Format(@"select top 1 accDate
from account
order by accDate DESC");
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//查询预算是否已存在
public static DataTable BudgetExist(string budgetDate)
{
string sql = string.Format(@"select * from budget where budgetDate='{0}'",budgetDate);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//查询某一分类名称是否存在
public static DataTable ExistAccClassName(string classAccName, string classAccInOut)
{
string sql = string.Format(@"select * from classAcc where classAccName='{0}' and classAccInOut='{1}'",classAccName, classAccInOut);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到某段时间的支出
public static DataTable GetPeriodOutcome(string timeStart, string timeEnd)
{
string sql = string.Format(@"select accID ID,accDate 日期,classAccInOut 收支,accNum 数目,classAccName 分类,accCon 备注
from account inner join classAcc on account.classAccID=classAcc.classAccID
where classAccInOut='支出' and accDate between '{0}' and '{1}' order by accDate DESC, accID DESC", timeStart, timeEnd);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到账户信息
public static DataTable GetUserInfo(string userName)
{
string sql = string.Format(@"select * from users where userName='******'",userName);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到某段时间某一分类的账目
public static DataTable GetCertainClassAccount(int classAccID, string timeStart, string timeEnd)
{
string sql = string.Format(@"select * from account where classAccID={0} and accDate between '{1}' and '{2}'", classAccID, timeStart, timeEnd);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//查询账目分类是否已存在账目
public static DataTable AccClassUsed(int classAccID)
{
string sql = string.Format(@"select * from account where classAccID={0}",classAccID);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到某一账目
public static DataTable GetCertainAccount(int accID)
{
string sql = string.Format(@"select accDate,classAccInOut,accNum,classAccName,accCon
from account inner join classAcc on account.classAccID=classAcc.classAccID
where accID={0}",accID);
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到收入或支出的全部分类
public static DataTable GetAllInOutClass(int sign)
{
//sign==0为支出
if(sign == 0)
{
string sql = string.Format(@"select * from classAcc where classAccInOut='支出'");
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//sign==1为收入
if (sign == 1)
{
string sql = string.Format(@"select * from classAcc where classAccInOut='收入'");
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
return null;
}
//得到所有预算
public static DataTable GetAllBudget()
{
string sql = string.Format(@"select * from budget order by budgetDate DESC");
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到所有账目
public static DataTable GetALLAccount()
{
string sql = string.Format(@"select accID ID,accDate 日期,classAccInOut 收支,accNum 数目,classAccName 分类,accCon 备注
from account inner join classAcc on account.classAccID=classAcc.classAccID
order by accDate DESC, accID DESC");
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
//得到余额下限值
public static DataTable GetAccLimit()
{
string sql = string.Format(@"select * from accLimit");
ManagerHelper mngHelper = new ManagerHelper();
return mngHelper.GetDataSet(sql).Tables[0];
}
