public DataTable StatisticDailyPlanCount(DateTime startDate, DateTime endDate)
{
string str_StartDate = startDate.ToString("yyyy-MM-dd");
string str_EndDate = endDate.ToString("yyyy-MM-dd");
StringBuilder builder = new StringBuilder();
builder.Append("SELECT TB_DailyPlanCatalog.Name,R.Times FROM TB_DailyPlanCatalog ");
builder.Append("LEFT JOIN ( SELECT CatalogID, count(*) AS Times FROM TB_DailyPlanSign ");
builder.Append(string.Format("WHERE TB_DailyPlanSign.Date > '{0}' AND TB_DailyPlanSign.Date <= '{1}' ", str_StartDate, str_EndDate));
builder.Append("AND TB_DailyPlanSign.IsSign = 'True' ");
builder.Append("GROUP BY CatalogID) AS R ON TB_DailyPlanCatalog.ID = R.CatalogID");
DataTable result = new DataTable();
try
{
using (var db = this.mFactory.OpenDefalutDataBase())
{
result = db.ExecuteSqlToDataSet(builder.ToString()).Tables[0];
}
}
catch (Exception ex)
{
mFactory.WriteSystemLog("StatisticDailyPlanCount", ex);
}
return(result);
}
public DataTable StatisticianReadDays(DateTime startDate, DateTime endDate)
{
string str_StartDate = startDate.ToString("yyyy-MM-dd");
string str_EndDate = endDate.ToString("yyyy-MM-dd");
int dayrange = (endDate - startDate).Days + 1;
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("状态", typeof(string)));
table.Columns.Add(new DataColumn("天数", typeof(string)));
string sql = string.Format("SELECT count(*) FROM TB_BookReadSign where Date >= '{0}' AND Date<='{1}'", str_StartDate, str_EndDate);
try
{
using (var db = mFactory.OpenDefalutDataBase())
{
string value = db.ExecuteSqlToDataSet(sql).Tables[0].Rows[0][0].ToString();
int readdays = string.IsNullOrEmpty(value) ? 0 : int.Parse(value);
DataRow row = table.NewRow();
row[0] = "读书天数"; row[1] = readdays;
table.Rows.Add(row);
DataRow row2 = table.NewRow();
row2[0] = "未读书天数"; row2[1] = dayrange - readdays;
table.Rows.Add(row2);
}
}
catch (Exception ex)
{
mFactory.WriteSystemLog("StatisticianReadDays", ex);
}
return(table);
}