275 lines
10 KiB
Plaintext
275 lines
10 KiB
Plaintext
<%@ WebHandler Language="C#" Class="getCountData" %>
|
|
|
|
using System;
|
|
using System.Web;
|
|
using System.Runtime.Serialization.Json;
|
|
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using System.Web.Services.Protocols;
|
|
using System.Security.Cryptography;
|
|
using System.Text;
|
|
using System.Web.SessionState;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.IO.Compression;
|
|
using System.Drawing;
|
|
using System.Drawing.Imaging;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Text.RegularExpressions;
|
|
|
|
public class getCountData : IHttpHandler, IReadOnlySessionState {
|
|
|
|
public void ProcessRequest (HttpContext context) {
|
|
result objRet = new result();
|
|
DataContractJsonSerializer json = new DataContractJsonSerializer(objRet.GetType());
|
|
context.Response.ContentType = "application/json;charset=utf-8";
|
|
context.Response.AddHeader("Access-Control-Allow-Origin", "*");
|
|
authPermission objAuth = new authPermission();
|
|
|
|
string project_uid = (context.Request["project_uid"] == null) ? "" : context.Request["project_uid"].ToString();
|
|
|
|
if (!objAuth.isLogin())
|
|
{
|
|
objRet.ret = "no";
|
|
objRet.err_code = "0001";
|
|
objRet.message = "尚未登入，請登入後使用";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
string startDate = (context.Request["startDate"] == null) ? "" : context.Request["startDate"].ToString();
|
|
string endDate = (context.Request["endDate"] == null) ? "" : context.Request["endDate"].ToString();
|
|
string url_uid = (context.Request["url_uid"] == null) ? "" : context.Request["url_uid"].ToString();
|
|
string type = (context.Request["type"] == null) ? "" : context.Request["type"].ToString();
|
|
|
|
string errmsg = "";
|
|
|
|
if (globalClass.IsDate(startDate) == false) {
|
|
errmsg = "請輸入開始日期!\n";
|
|
}
|
|
|
|
if (globalClass.IsDate(endDate) == false) {
|
|
errmsg = "請輸入結束日期!\n";
|
|
}
|
|
|
|
if (type != "day" && type != "total" && type != "month" && type != "hour") {
|
|
errmsg += "無正確的統計方式!\n";
|
|
}
|
|
|
|
if (errmsg.Length > 0) {
|
|
objRet.ret = "no";
|
|
objRet.err_code = "0002";
|
|
objRet.message = errmsg;
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
string urlString = string.Format("select * from url where url_uid = '{0}' and url_isRevoke = 'N' ", url_uid);
|
|
autoBindDataTable urlSQL = new autoBindDataTable(urlString);
|
|
|
|
if (urlSQL.dataRows.Count == 0) {
|
|
objRet.ret = "no";
|
|
objRet.err_code = "0003";
|
|
objRet.message = "找不到此 url_uid 的資料!";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
DateTime dateStart = DateTime.Parse(startDate);
|
|
DateTime dateEnd = DateTime.Parse(endDate + " 23:59:59");
|
|
|
|
if (type == "hour") {
|
|
string projString = string.Format("select * from projects where project_isRevoke = 'N' and project_uid = '{0}'", urlSQL.dataRows[0]["project_uid"].ToString());
|
|
autoBindDataTable projSQL = new autoBindDataTable(projString);
|
|
|
|
project_uid = urlSQL.dataRows[0]["project_uid"].ToString();
|
|
|
|
if (projSQL.dataRows.Count == 0) {
|
|
objRet.ret = "no";
|
|
objRet.err_code = "0003";
|
|
objRet.message = "找不到此URL的專案資料!";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
string recTableName = projSQL.dataRows[0]["project_recTableName"].ToString();
|
|
|
|
string rangeString = string.Format("select * from {0} where project_uid = '{1}' and url_uid = '{2}' and rec_createdate >= '{3}' and rec_createdate <= '{4}'", recTableName, project_uid, url_uid, dateStart.ToString("yyyy/MM/dd HH:mm:ss"), dateEnd.ToString("yyyy/MM/dd HH:mm:ss"));
|
|
autoBindDataTable rangeSQL = new autoBindDataTable(rangeString);
|
|
|
|
DateTime tmpDate = dateStart;
|
|
int intTotal = 0;
|
|
while (tmpDate <= dateEnd) {
|
|
DateTime dateS = DateTime.Parse(tmpDate.ToString("yyyy/MM/dd HH:00:00"));
|
|
DateTime dateE = DateTime.Parse(tmpDate.AddHours(1).ToString("yyyy/MM/dd HH:00:00")).AddSeconds(-1);
|
|
|
|
//if (dateStart > dateS) {
|
|
// dateS = dateStart;
|
|
//}
|
|
|
|
//if (dateE > dateEnd) {
|
|
// dateE = dateEnd;
|
|
//}
|
|
|
|
|
|
DataRow[] objTmp = rangeSQL.selectedRows(string.Format("rec_createdate >= '{0}' and rec_createdate <= '{1}'", dateS.ToString("yyyy/MM/dd HH:mm:ss"), dateE.ToString("yyyy/MM/dd HH:mm:ss")));
|
|
|
|
countData objCount = new countData();
|
|
objRet.data.Add(objCount);
|
|
|
|
objCount.title = tmpDate.ToString("yyyy/MM/dd") + " " + tmpDate.ToString("HH") + ":00:00 - " + tmpDate.ToString("HH") + ":59:59";
|
|
if (objTmp.Length == 0)
|
|
{
|
|
objCount.num = 0;
|
|
}
|
|
else {
|
|
objCount.num = objTmp.Length;
|
|
intTotal += objCount.num;
|
|
}
|
|
|
|
tmpDate = tmpDate.AddHours(1);
|
|
}
|
|
|
|
countData objTotal = new countData();
|
|
objTotal.title = "總計";
|
|
objTotal.num = intTotal;
|
|
objRet.data.Add(objTotal);
|
|
|
|
objRet.ret = "yes";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
if (type == "month") {
|
|
|
|
|
|
string rangeString = string.Format("select * from recSubTotal where url_uid = '{0}' and recSubTotal_date >= '{1}' and recSubTotal_date <= '{2}'", url_uid, dateStart.ToString("yyyy/MM/dd HH:mm:ss"), dateEnd.ToString("yyyy/MM/dd HH:mm:ss"));
|
|
autoBindDataTable rangeSQL = new autoBindDataTable(rangeString);
|
|
|
|
DateTime tmpDate = dateStart;
|
|
int intTotal = 0;
|
|
while (tmpDate <= dateEnd) {
|
|
DateTime dateS = DateTime.Parse(tmpDate.ToString("yyyy/MM/01 00:00:00"));
|
|
DateTime dateE = DateTime.Parse(tmpDate.AddMonths(1).ToString("yyyy/MM/01 00:00:00")).AddSeconds(-1);
|
|
|
|
if (dateStart > dateS) {
|
|
dateS = dateStart;
|
|
}
|
|
|
|
if (dateE > dateEnd) {
|
|
dateE = dateEnd;
|
|
}
|
|
|
|
|
|
DataRow[] objTmp = rangeSQL.selectedRows(string.Format("recSubTotal_date > '{0}' and recSubTotal_date <= '{1}'", dateS.ToString("yyyy/MM/dd 00:00:00"), dateE.ToString("yyyy/MM/dd 23:59:59")));
|
|
|
|
countData objCount = new countData();
|
|
objRet.data.Add(objCount);
|
|
|
|
objCount.title = tmpDate.ToString("yyyy") + "年" + tmpDate.ToString("MM") + "月";
|
|
if (objTmp.Length == 0)
|
|
{
|
|
objCount.num = 0;
|
|
}
|
|
else {
|
|
objCount.num = int.Parse(objTmp[0]["recSubTotal_count"].ToString());
|
|
objCount.num = objTmp.Sum(row => row.Field<int>("recSubTotal_count"));
|
|
intTotal += objCount.num;
|
|
}
|
|
|
|
tmpDate = tmpDate.AddMonths(1);
|
|
|
|
if (tmpDate > dateEnd && tmpDate.ToString("yyyy/MM") == dateEnd.ToString("yyyy/MM")) {
|
|
tmpDate = dateEnd;
|
|
}
|
|
}
|
|
|
|
countData objTotal = new countData();
|
|
objTotal.title = "總計";
|
|
objTotal.num = intTotal;
|
|
objRet.data.Add(objTotal);
|
|
|
|
objRet.ret = "yes";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
if (type == "day") {
|
|
string rangeString = string.Format("select * from recSubTotal where url_uid = '{0}' and recSubTotal_date >= '{1}' and recSubTotal_date <= '{2}'", url_uid, dateStart.ToString("yyyy/MM/dd HH:mm:ss"), dateEnd.ToString("yyyy/MM/dd HH:mm:ss"));
|
|
autoBindDataTable rangeSQL = new autoBindDataTable(rangeString);
|
|
|
|
DateTime tmpDate = dateStart;
|
|
int intTotal = 0;
|
|
while (tmpDate < dateEnd) {
|
|
DataRow[] objTmp = rangeSQL.selectedRows(string.Format("recSubTotal_date > '{0}' and recSubTotal_date <= '{1}'", tmpDate.ToString("yyyy/MM/dd"), tmpDate.ToString("yyyy/MM/dd 23:59:59")));
|
|
|
|
countData objCount = new countData();
|
|
objRet.data.Add(objCount);
|
|
|
|
objCount.title = tmpDate.ToString("yyyy/MM/dd");
|
|
if (objTmp.Length == 0)
|
|
{
|
|
objCount.num = 0;
|
|
}
|
|
else {
|
|
objCount.num = int.Parse(objTmp[0]["recSubTotal_count"].ToString());
|
|
intTotal += objCount.num;
|
|
}
|
|
|
|
tmpDate = tmpDate.AddDays(1);
|
|
}
|
|
|
|
countData objTotal = new countData();
|
|
objTotal.title = "總計";
|
|
objTotal.num = intTotal;
|
|
objRet.data.Add(objTotal);
|
|
|
|
objRet.ret = "yes";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
|
|
if (type == "total") {
|
|
string rangeString = string.Format("select sum(recSubTotal_count) as num from recSubTotal where url_uid = '{0}' and recSubTotal_date >= '{1}' and recSubTotal_date <= '{2}'", url_uid, dateStart.ToString("yyyy/MM/dd HH:mm:ss"), dateEnd.ToString("yyyy/MM/dd HH:mm:ss"));
|
|
autoBindDataTable rangeSQL = new autoBindDataTable(rangeString);
|
|
|
|
countData objTotal = new countData();
|
|
objTotal.title = "總計";
|
|
|
|
if (rangeSQL.dataRows.Count == 0)
|
|
{
|
|
objTotal.num = 0;
|
|
}
|
|
else {
|
|
objTotal.num = int.Parse(rangeSQL.dataRows[0]["num"].ToString());
|
|
}
|
|
|
|
objRet.data.Add(objTotal);
|
|
objRet.ret = "yes";
|
|
json.WriteObject(context.Response.OutputStream, objRet);
|
|
return;
|
|
}
|
|
}
|
|
|
|
public class result
|
|
{
|
|
public string ret = "no";
|
|
public string err_code = "0000";
|
|
public string message = "";
|
|
public List<countData> data = new List<countData>();
|
|
}
|
|
|
|
public class countData
|
|
{
|
|
public string title = "";
|
|
public int num = 0;
|
|
}
|
|
|
|
public bool IsReusable {
|
|
get {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
} |