/*public ActionResult Report16(Report16Model model)
{
//return Pdf("/reportsgen/report16/", this._report[16].ToString(), false);
return new ReportsGenController().Report16(1);
}*/
public ActionResult Report17(Report17Model model)
{
return Jpg("/reportsgen/report17/");
}
public ActionResult Report17(Report17Model model)
{
int region_id = model.region_id;
string start_date = model.start_date;
string end_date = model.end_date;
ArrayList where = new ArrayList();
if (!String.IsNullOrWhiteSpace(start_date))
{
where.Add("e.repair_date >= '" + ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'");
}
if (!String.IsNullOrWhiteSpace(end_date))
{
where.Add("e.repair_date < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')");
}
if (region_id > 0)
{
where.Add("d.region = '" + region_id + "'");
}
string sql = Common.doSql(@"
SELECT
CONVERT(NVARCHAR(7), e.repair_date, 120) AS [month],
d.region, COUNT(c.id) AS total
FROM {p}_order AS e
LEFT JOIN {p}_relate_customers AS c ON e.customer_id = c.id
LEFT JOIN {p}_district AS d ON c.district2 = d.id
WHERE e.status = '1'
AND e.by_system = 0
AND c.district2 != 0" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @"
GROUP BY CONVERT(NVARCHAR(7), e.repair_date, 120), d.region ORDER BY [month], d.region");
ArrayList ecall = Common.query(this._db.ExecuteStoreQuery<report17>(sql));
if (ecall.Count > 0)
{
var region = (from r in this._db.recsys_region
orderby r.id ascending
select r);
Hashtable _region = Common.array_conv(Common.query(region), "id", "name");
Dictionary<string, ArrayList> data = new Dictionary<string, ArrayList>();
DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
foreach (Hashtable item in ecall)
{
if (item["month"] == null)
continue;
DateTime parsedDate = DateTime.Parse(item["month"].ToString(), null, DateTimeStyles.None);
string tmp = item["region"].ToString();
if (!data.ContainsKey(tmp))
{
data[tmp] = new ArrayList();
}
TimeSpan diff = parsedDate - origin;
data[tmp].Add(new double[] { diff.TotalSeconds * 1000, Convert.ToInt32(item["total"]) });
}
ArrayList arr = new ArrayList();
if (data.Count > 0)
{
foreach (KeyValuePair<string, ArrayList> item in data)
{
arr.Add(new Hashtable() {
{ "name", _region[item.Key] },
{ "data", item.Value }
});
}
}
ViewBag.data = arr;
return View();
}
else
{
return Content("找不到任何紀錄。");
}
}