erp/SiteCore/Handler/app.du.api.cs

using SiteCore.Redis;
using SQLData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SiteCore.Handler
{
    public partial class app
    {
        public void qdb_api_site()
        {
            //redis.RedisHelper.StringSet("duba_site", "");
            string siteStr = redis.RedisHelper.StringGet("duba_site");
            if (string.IsNullOrEmpty(siteStr))
            {
                string sql = "select id,(lng+'|'+lat) as lnglat,name,address from s_dusite where lng<>'' order by id asc";
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<string> strLst = new List<string>();
                    foreach (DataRow dr in dt.Rows)
                    {
                        strLst.Add("{ title: \"" + dr["name"] + "\",point:\"" + dr["lnglat"] + "\",address:\"" + dr["address"] + "\"}");
                    }
                    if (strLst.Count > 0)
                    {
                        string str = string.Join(",", strLst.ToArray());
                        redis.RedisHelper.StringSet("duba_site", str);
                        conSuccess("success", str);
                        return;
                    }
                }
            }
            else
            {
                conSuccess("success", siteStr);
                return;
            }
            conError("none");
        }

        public void qdb_api_usesite()
        {
            //redis.RedisHelper.StringSet("duba_usesite", "");
            string siteStr = redis.RedisHelper.StringGet("duba_usesite");
            if (string.IsNullOrEmpty(siteStr))
            {
                string sql = "select id,(lng+'|'+lat) as lnglat,name,address from s_dusite where lng<>'' order by id asc";
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<string> strLst = new List<string>();
                    foreach (DataRow dr in dt.Rows)
                    {
                        strLst.Add("{ \"id\":\"" + dr["id"] + "\", \"name\": \"" + dr["name"] + "\",\"point\":\"" + dr["lnglat"] + "\",\"address\":\"" + dr["address"] + "\"}");
                    }
                    if (strLst.Count > 0)
                    {
                        string str = string.Join(",", strLst.ToArray());
                        redis.RedisHelper.StringSet("duba_usesite", str);
                        conSuccess("success", str);
                        return;
                    }
                }
            }
            else
            {
                conSuccess("success", siteStr);
                return;
            }
            conError("none");
        }


        public void qdb_api_perday()
        {
            string perday = redis.RedisHelper.StringGet("duba_perday");
            if (string.IsNullOrEmpty(perday))
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("select (SELECT COUNT(0) FROM S_DuUser where Mobile<>'') as ucount,(SELECT COUNT(0) FROM S_DuUser WHERE DATEDIFF(D, addtime, GETDATE())=0 and Mobile <> '') as joinuser,(SELECT COUNT(0) FROM S_DuEntryExit WHERE DATEDIFF(D,addtime,GETDATE())=0) as entryuser");
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[0];
                    StringBuilder result = new StringBuilder();
                    result.Append("{");
                    result.AppendFormat("\"ucount\":{0},\"joinuser\":{1},\"entryuser\":{2}", dr["ucount"], dr["joinuser"], dr["entryuser"]);
                    result.Append("}");
                    redis.RedisHelper.StringSet("duba_perday", result.ToString(), TimeSpan.FromMinutes(1));
                    conSuccess("success", result.ToString());
                }
            }
            else
            {
                conSuccess("success", perday.ToString());
            }
        }

        public void qdb_api_sitecurday()
        {
            //redis.RedisHelper.StringSet("duba_siteperday", "");
            string siteperday = redis.RedisHelper.StringGet("duba_siteperday");
            if (string.IsNullOrEmpty(siteperday))
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("SELECT siteid,COUNT(0) as c FROM S_DuEntryExit WHERE DATEDIFF(D,addtime,GETDATE())=0 group by siteid");
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    StringBuilder result = new StringBuilder();
                    int k = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (k > 0) result.Append(",{");
                        else result.Append("{");
                        result.AppendFormat("\"siteid\":{0},\"ucount\":{1}", dr["siteid"], dr["c"]);
                        result.Append("}");
                        k++;
                    }
                    redis.RedisHelper.StringSet("duba_siteperday", result.ToString(), TimeSpan.FromMinutes(1));
                    conSuccess("success", result.ToString());
                }
            }
            else
            {
                conSuccess("success", siteperday.ToString());
            }

        }

        public void qdb_api_sitemonth()
        {
            //redis.RedisHelper.StringSet("duba_sitemonth", "");
            string rstr = redis.RedisHelper.StringGet("duba_sitemonth");
            if (string.IsNullOrEmpty(rstr))
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("SELECT siteid,COUNT(0) as c FROM S_DuEntryExit WHERE DATEDIFF(m,addtime,GETDATE())=0 group by siteid");
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    StringBuilder result = new StringBuilder();
                    int k = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (k > 0) result.Append(",{");
                        else result.Append("{");
                        result.AppendFormat("\"siteid\":{0},\"ucount\":{1}", dr["siteid"], dr["c"]);
                        result.Append("}");
                        k++;
                    }
                    redis.RedisHelper.StringSet("duba_sitemonth", result.ToString(), TimeSpan.FromMinutes(10));
                    conSuccess("success", result.ToString());
                }
            }
            else
            {
                conSuccess("success", rstr);
            }
        }

        public void qdb_api_monthreport()
        {
            //redis.RedisHelper.StringSet("duba_monthreport", "");
            string rstr = redis.RedisHelper.StringGet("duba_monthreport");
            if (string.IsNullOrEmpty(rstr))
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("SELECT convert(varchar,addtime,23) as ctime,COUNT(0) as c FROM S_DuEntryExit WHERE DATEDIFF(m,addtime,GETDATE())=0 group by convert(varchar,addtime,23)");
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    StringBuilder result = new StringBuilder();
                    int k = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (k > 0) result.Append(",{");
                        else result.Append("{");
                        result.AppendFormat("\"day\":{0},\"ucount\":{1}", Convert.ToDateTime(dr["ctime"]).Day, dr["c"]);
                        result.Append("}");
                        k++;
                    }
                    redis.RedisHelper.StringSet("duba_monthreport", result.ToString(), TimeSpan.FromMinutes(10));
                    conSuccess("success", result.ToString());
                }
            }
            else
            {
                conSuccess("success", rstr);
            }
        }

        public void qdb_api_userduty()
        {
            //redis.RedisHelper.StringSet("duba_userduty", "");
            string rstr = redis.RedisHelper.StringGet("duba_userduty");
            if (string.IsNullOrEmpty(rstr))
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("SELECT duty,COUNT(0) as c FROM s_duuser group by duty order by count(0) desc");
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    StringBuilder result = new StringBuilder();
                    int i = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["duty"].ToString() == "") continue;
                        if(i==0) result.Append("{");
                        else result.Append(",{");
                        result.AppendFormat("\"duty\":\"{0}\",\"ucount\":{1}", dr["duty"], dr["c"]);
                        result.Append("}");
                        i++;
                        if (i >= 5) break;
                    }
                    var pList = from t in dt.AsEnumerable()
                                group t by new { } into v
                                select new
                                {
                                    count = v.Sum(t => t.Field<int>("c"))
                                };
                    if (pList.Count() > 0)
                    {
                        result.Append(",{");
                        result.AppendFormat("\"duty\":\"其它\",\"ucount\":{0}", pList.ToList()[0].count);
                        result.Append("}");
                       
                    }

                    redis.RedisHelper.StringSet("duba_userduty", result.ToString(), TimeSpan.FromMinutes(10));
                    conSuccess("success", result.ToString());
                }
            }
            else
            {
                conSuccess("success", rstr);
            }
        }






    }
}