//查询账目的最后日期 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]; }