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