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);
}
