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 User(DataRow r) { id = Convert.ToInt32(r["userId"]); name = r["name"].ToString(); isAdmin = Convert.ToBoolean(r["isAdmin"]); username = r["username"].ToString(); password = FinanceDbManager.Decrypt(r["password"].ToString()); this.roles = isAdmin ? new RolesController().ShowAll() : new UserRolesController().ShowUsersRoles(this); }
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); }
public virtual T GetLastAdded() { T obj = null; DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().OrderBy(idName, false).Limit(1)); if (dt.Rows.Count > 0) { obj = (T)Activator.CreateInstance(typeof(T), dt.Rows[0]); } return(obj); }
public virtual T Show(int id) { T obj = null; DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ONE, tableName, new Param(idName, id)); if (dt.Rows.Count > 0) { obj = (T)Activator.CreateInstance(typeof(T), dt.Rows[0]); } return(obj); }
//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 bool UserHaveRole(User user, Role role) { DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().Where("userId").EqualsTo("@userId").And("roleId").EqualsTo("@roleId"), new Param("userId", user.id), new Param("roleId", role.id)); if (dt.Rows.Count == 0) { return(false); } return(true); }
public virtual T Update(int id, params Param[] @params) { FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.UPDATE, tableName, new Param(idName, id), null, @params); DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ONE, tableName, new Param(idName, id)); T obj = null; if (dt.Rows.Count > 0) { obj = (T)Activator.CreateInstance(typeof(T), dt.Rows[0]); } return(obj); }
public bool MemberFamilyExists(Member member, Family family) { DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().Where("memberId").EqualsTo(member.id).And("familyId").EqualsTo(family.id)); if (dt.Rows.Count > 0) { return(true); } else { return(false); } }
public virtual List <T> ShowAll(params Param[] parameters) { List <T> objects = new List <T>(); DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { objects.Add((T)Activator.CreateInstance(typeof(T), r)); } } return(objects); }
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 void Update(string name, string username, string password) { password = FinanceDbManager.Encrypt(password); UsersController uc = new UsersController(); User u = uc.Update(this.id, new Param("name", name), new Param("username", username), new Param("password", password) ); this.id = u.id; this.name = u.name; this.username = u.username; this.password = u.password; this.isAdmin = u.isAdmin; this.roles = isAdmin ? new RolesController().ShowAll() : new UserRolesController().ShowUsersRoles(this); }
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 <GivingType> ShowUnused(Giving giving) { List <GivingType> givingTypes = new List <GivingType>(); DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().Where(idName).NotIn( new QueryBuilder().Select("GivingItems", idName).Where("givingId").EqualsTo(giving.id) ).And("isActive").EqualsTo(true) ); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { givingTypes.Add(new GivingType(r)); } } return(givingTypes); }
public Family ShowMembersFamily(Member member) { FamiliesController fc = new FamiliesController(); Family family = null; if (member == null) { return(null); } DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().Where("memberId").EqualsTo(member.id) ); if (dt.Rows.Count > 0) { family = fc.Show((int)dt.Rows[0]["familyId"]); } return(family); }
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); }
public List <Role> ShowUsersRoles(User user) { List <Role> roles = new List <Role>(); DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().Where("userId").EqualsTo("@userId"), new Param("userId", user.id)); if (dt.Rows.Count == 0) { return(null); } foreach (DataRow r in dt.Rows) { Role role = new Role(Convert.ToInt32(r["roleId"])); if (role == null) { continue; } roles.Add(role); } return(roles); }
public override List <GivingItem> ShowAll(params Param[] @params) { List <GivingItem> givingItems = new List <GivingItem>(); if (@params.Length != 1) { return(givingItems); } QueryBuilder qb = new QueryBuilder().Where(@params[0].name).EqualsTo(@params[0].value); DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, qb); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { givingItems.Add(new GivingItem(r)); } } return(givingItems); }
public List <Member> ShowFamilyMembers(Family family) { MembersController mc = new MembersController(); List <Member> members = new List <Member>(); if (family == null) { return(members); } DataTable dt = FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.SELECT_ALL, tableName, null, new QueryBuilder().Where("familyId").EqualsTo(family.id) ); if (dt.Rows.Count > 0) { foreach (DataRow r in dt.Rows) { members.Add(mc.Show((Convert.ToInt32(r["memberId"])))); } } return(members); }
public void AddRoleToUser(Role role, User user) { FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.CREATE, tableName, null, null, new Param("roleId", role.id), new Param("userId", user.id)); }
public virtual void Delete(int id) { FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.DELETE, tableName, new Param(idName, id)); }
public virtual void Add(params Param[] @params) { FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.CREATE, tableName, null, null, @params); }