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);
        }
Example #3
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);
        }
Example #6
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);
        }
Example #7
0
        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);
        }
Example #8
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 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);
        }
Example #12
0
        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);
            }
        }
Example #14
0
        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);
        }
Example #17
0
        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);
        }
Example #20
0
        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));
 }
Example #28
0
 public virtual void Delete(int id)
 {
     FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.DELETE, tableName, new Param(idName, id));
 }
Example #29
0
 public virtual void Add(params Param[] @params)
 {
     FinanceDbManager.BasicQuery(FinanceDbManager.QueryMode.CREATE, tableName, null, null, @params);
 }