public int this[Model.Statistic stat] {
get { return((int)Modifiers[stat].Calculate(stats[stat] * Multiplier(stat))); }
}
public Model.AdminStatistics getAdminStatistic(string dateStart, string dateEnd)
{
// prepare the listing
Model.AdminStatistics result = new Model.AdminStatistics();
result.total = new Model.Statistic();
result.statistics = new Dictionary <string, Model.Statistic>();
// get from database
MySqlCommand mySqlCmd = null;
MySqlDataReader reader = null;
var datetimeStart = DateTime.Parse(dateStart);
var datetimeEnd = DateTime.Parse(dateEnd);
foreach (DateTime day in EachDay(datetimeStart, datetimeEnd))
{
var statistic = new Model.Statistic();
result.statistics.Add(day.ToShortDateString(), statistic);
string query = string.Format("SELECT (SELECT COUNT(*) FROM {0} WHERE DATE(creation_date) = '{1}') AS userCount, " +
"(SELECT COUNT(*) FROM {2} WHERE DATE(creation_date) = '{1}') AS companyCount, " +
"(SELECT COUNT(*) FROM {3} WHERE DATE(creation_date) = '{1}') AS jobCount, " +
"(SELECT COALESCE(SUM(amount), 0) FROM {3} WHERE DATE(creation_date) = '{1}') AS revenue, " +
"(SELECT COALESCE(SUM(amount), 0)*0.1 FROM {3} WHERE DATE(creation_date) = '{1}') AS profit, " +
"(SELECT COUNT(*) FROM {4} WHERE DATE(creation_date) = '{1}') AS fleetCount ",
TABLE_USERS, day.ToString("yyyy-MM-dd"), TABLE_COMPANIES, TABLE_JOBS, TABLE_FLEETS);
mySqlCmd = new MySqlCommand(query);
reader = PerformSqlQuery(mySqlCmd);
if (reader.Read())
{
statistic.numUniqueUsers = reader.GetInt32("userCount");
statistic.numPartners = reader.GetInt32("companyCount");
statistic.numJobs = reader.GetInt32("jobCount");
statistic.numProfits = reader.GetFloat("profit");
statistic.numRevenues = reader.GetFloat("revenue");
statistic.numFleets = reader.GetInt32("fleetCount");
}
CleanUp(reader, mySqlCmd);
}
// get all the figures
string totalQuery = string.Format("SELECT (SELECT COUNT(*) FROM {0} WHERE DATE(creation_date) between '{4}' AND '{5}') AS userCount, " +
"(SELECT COUNT(*) FROM {1} WHERE DATE(creation_date) between '{4}' AND '{5}') AS companyCount, " +
"(SELECT COUNT(*) FROM {2} WHERE DATE(creation_date) between '{4}' AND '{5}') AS jobCount, " +
"(SELECT COALESCE(SUM(amount), 0) FROM {2} WHERE DATE(creation_date) between '{4}' AND '{5}') AS revenue, " +
"(SELECT COALESCE(SUM(amount), 0)*0.1 FROM {2} WHERE DATE(creation_date) between '{4}' AND '{5}') AS profit, " +
"(SELECT COUNT(*) FROM {3} WHERE DATE(creation_date) between '{4}' AND '{5}') AS fleetCount ",
TABLE_USERS, TABLE_COMPANIES, TABLE_JOBS, TABLE_FLEETS, datetimeStart.ToString("yyyy-MM-dd"), datetimeEnd.AddDays(1).ToString("yyyy-MM-dd"));
mySqlCmd = new MySqlCommand(totalQuery);
reader = PerformSqlQuery(mySqlCmd);
if (reader.Read())
{
result.total.numUniqueUsers = reader.GetInt32("userCount");
result.total.numPartners = reader.GetInt32("companyCount");
result.total.numJobs = reader.GetInt32("jobCount");
result.total.numProfits = reader.GetFloat("profit");
result.total.numRevenues = reader.GetFloat("revenue");
result.total.numFleets = reader.GetInt32("fleetCount");
}
CleanUp(reader, mySqlCmd);
return(result);
}
public List <Model.Statistic> Get_List_Customer_By_Month()
{
//List<customer> lst_customer = new List<customer>();
List <Model.Statistic_Customer> statistic_customer = new List <Model.Statistic_Customer>();
List <Model.Statistic_Money> statistic_money = new List <Model.Statistic_Money>();
List <Model.Statistic_Money> statistic_interset = new List <Model.Statistic_Money>();
List <Model.Statistic> statistic = new List <Model.Statistic>();
for (int i = 1; i < 13; i++)
{
Model.Statistic statis = new Model.Statistic();
statis.Month = i;
statis.NumCustomer = 0;
statis.NumInterestMoney = 0;
statis.NumMoney = 0;
statis.NumReturnMoney = 0;
statistic.Add(statis);
}
statistic_customer = (from data in _db.customers
group data by new
{
Column1 = (int)SqlFunctions.DatePart("mm", data.CreatedAt)
}
into g
select new Model.Statistic_Customer
{
Month = g.Key.Column1,
Count = g.Count(data => data.id != null),
Money = g.Sum(data => data.Money)
}
).ToList();
statistic_money = (from data in _db.historyPaids
where data.TypePaid == "0"
group data by new
{
Column1 = (int)SqlFunctions.DatePart("mm", data.CreatedAt)
}
into g
select new Model.Statistic_Money
{
Month = g.Key.Column1,
Money = g.Sum(data => data.Money)
}
).ToList();
statistic_interset = (from data in _db.historyPaids
where data.TypePaid == "1"
group data by new
{
Column1 = (int)SqlFunctions.DatePart("mm", data.CreatedAt)
}
into g
select new Model.Statistic_Money
{
Month = g.Key.Column1,
Money = g.Sum(data => data.Money)
}
).ToList();
for (int i = 0; i < statistic_customer.Count; i++)
{
for (int j = 0; j < statistic.Count; j++)
{
if (statistic[j].Month == statistic_customer[i].Month)
{
statistic[j].NumMoney = Convert.ToInt32(statistic_customer[i].Money);
statistic[j].NumCustomer = Convert.ToInt32(statistic_customer[i].Count);
}
}
}
for (int i = 0; i < statistic_money.Count; i++)
{
for (int j = 0; j < statistic.Count; j++)
{
if (statistic[j].Month == statistic_money[i].Month)
{
statistic[j].NumReturnMoney = Convert.ToInt32(statistic_money[i].Money);
}
}
}
for (int i = 0; i < statistic_interset.Count; i++)
{
for (int j = 0; j < statistic.Count; j++)
{
if (statistic[j].Month == statistic_interset[i].Month)
{
statistic[j].NumInterestMoney = Convert.ToInt32(statistic_interset[i].Money);
}
}
}
return(statistic);
}
