DataTable GetTotalOfYear(int year, int givingTypeId = 0) { DataTable dt = new DataTable(); dt.Columns.Add("total"); dt.Columns.Add("month"); dt.Columns.Add("year"); for (int month = 1; month <= 12; month++) { string condition = new QueryBuilder().Where("strftime('%Y', g.givingDate)").EqualsTo($"'{year.ToString()}'") .And("strftime('%m', g.givingDate)").EqualsTo($"'{ month.ToString("D2")}'").ToString(); if (givingTypeId != 0) { condition += new QueryBuilder().And("gi.givingTypeId").EqualsTo(givingTypeId).ToString(); } DataTable Tempdt = FinanceDbManager.CustomQuery(new QueryBuilder().Custom("SELECT " + "SUM(gi.amount) AS total, " + "Strftime('%m', g.givingDate) AS 'month', " + "Strftime('%Y', g.givingDate) AS 'year' " + "FROM Givings g JOIN GivingItems gi ON gi.givingId = g.givingId ").Custom(condition)); if (Tempdt.Rows[0]["total"] != DBNull.Value) { dt.Rows.Add(Tempdt.Rows[0]["total"], Tempdt.Rows[0]["month"], Tempdt.Rows[0]["year"]); continue; } dt.Rows.Add(0, month, year); } return(dt); }
public bool UsernameExist(string username) { DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("username").EqualsTo("@username"), new Param("username", username)); return(dt.Rows.Count > 0); }
public void DeleteRoleFromUser(Role role, User user) { FinanceDbManager.CustomQuery(new QueryBuilder(). Delete(tableName).Where("roleId").EqualsTo("@roleId"). And("userId").EqualsTo("@userId"), new Param("roleId", role.id), new Param("userId", user.id) ); }
public bool IsDuplicateUsername(string desiredUsername, User currenUser) { DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("username").EqualsTo("@username") .And("userId").NotEqualsTo("@userId"), new Param("username", desiredUsername), new Param("userId", currenUser.id) ); return(dt.Rows.Count > 0); }
//get role by tag public Role GetRoleByTag(string tag) { DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder() .SelectAll(tableName).Where("tag").EqualsTo("@tag"), new Param("tag", tag)); if (dt.Rows.Count == 0) { return(null); } return(new Role(dt.Rows[0])); }
public void Delete(Member member, Family family) { Console.WriteLine(new QueryBuilder(). Delete(tableName).Where("memberId").EqualsTo(member.id).And("familyId").EqualsTo(family.id).ToString()); FinanceDbManager.CustomQuery(new QueryBuilder(). Delete(tableName).Where("memberId").EqualsTo(member.id).And("familyId").EqualsTo(family.id) ); }
public User Show(string username) { DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("username").EqualsTo("@username"), new Param("username", username)); if (dt.Rows.Count == 0) { return(null); } return(new User(dt.Rows[0])); }
public Session GetLastLoginByUser(User user) { DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("userId").EqualsTo("@userId").OrderBy("start", false).Limit(1), new Param("userId", user.id) ); if (dt.Rows.Count > 0) { return(new Session(dt.Rows[0])); } else { return(null); } }
public List <Giving> ShowAllWithinSession(Session session) { List <Giving> givings = new List <Giving>(); DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("sessionId").EqualsTo("@sessionId"), new Param("sessionId", session.id)); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { givings.Add(new Giving(r)); } } return(givings); }
public List <Session> GetAllLoginByUser(User user) { List <Session> sessions = new List <Session>(); DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("userId").EqualsTo("@userId").OrderBy("start", false), new Param("userId", user.id) ); if (dt.Rows.Count == 0) { return(sessions); } foreach (DataRow r in dt.Rows) { sessions.Add(new Session(r)); } return(sessions); }
public List <DateTime> GetAllYears() { QueryBuilder qb = new QueryBuilder().Custom("SELECT DISTINCT strftime('%Y',g.givingDate) as year FROM GivingItems gi JOIN Givings g ON gi.givingId = g.givingId"); List <DateTime> YearList = new List <DateTime>(); DataTable dt = FinanceDbManager.CustomQuery(qb); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { YearList.Add(new DateTime( Convert.ToInt32(r["year"]), 1, //default value which will not be used 1 //default value which will not be used )); } } return(YearList); }
public List <Giving> ShowAllWithinScope(Service service, DateTime date) { List <Giving> givings = new List <Giving>(); DataTable dt = FinanceDbManager.CustomQuery(new QueryBuilder(). SelectAll(tableName).Where("serviceId").EqualsTo("@serviceId") .Custom("AND date(givingDate) ").EqualsTo("@givingDate"), new Param("serviceId", service.id), new Param("givingDate", date.ToString("yyyy-MM-dd"))); Console.WriteLine(date.ToString("yyyy-MM-dd") + " " + service.id); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { givings.Add(new Giving(r)); } } return(givings); }
DataTable GetTotalOfMonth(int month, int year, int givingTypeId = 0) { DataTable dt = new DataTable(); dt.Columns.Add("total"); dt.Columns.Add("start"); dt.Columns.Add("end"); // Get the weeks in a month DateTime date = new DateTime(year, month, 1); // first generate all dates in the month of 'date' var dates = Enumerable.Range(1, DateTime.DaysInMonth(date.Year, date.Month)).Select(n => new DateTime(date.Year, date.Month, n)); // then filter the only the start of weeks var weekStarts = from d in dates where d.DayOfWeek == DayOfWeek.Sunday select d; foreach (DateTime start in weekStarts) { string condition = new QueryBuilder().Where("date(g.givingDate)") .BETWEEN( $"'{ start.ToString("yyyy-MM-dd")}'", $"'{start.AddDays(7).ToString("yyyy-MM-dd")}'" ).ToString(); if (givingTypeId != 0) { condition += new QueryBuilder().And("gi.givingTypeId").EqualsTo(givingTypeId).ToString(); } DataTable Tempdt = FinanceDbManager.CustomQuery(new QueryBuilder().Custom("SELECT " + "SUM(gi.amount) AS total, " + $"date('{start.ToString("yyyy-MM-dd")}') AS 'start', " + $"date('{start.AddDays(7).ToString("yyyy-MM-dd")}') AS 'end' " + "FROM Givings g JOIN GivingItems gi ON gi.givingId = g.givingId ").Custom(condition)); Console.WriteLine("Controller Total:" + Tempdt.Rows[0]["total"].ToString()); dt.Rows.Add((Tempdt.Rows[0]["total"] == DBNull.Value? 0 : Tempdt.Rows[0]["total"]), Tempdt.Rows[0]["start"], Tempdt.Rows[0]["end"]); } return(dt); }