100 lines
4.7 KiB
C#
100 lines
4.7 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using WX.CRM.DAL;
|
|
|
|
namespace WX.CRM.CRMServices.WeiXin
|
|
{
|
|
public static class SummaryMessage
|
|
{
|
|
public static MySqlDbHelper mySqlhelper = new MySqlDbHelper("MysqlQWConn");//读取
|
|
/// <summary>
|
|
/// 汇总
|
|
/// </summary>
|
|
public static void Summary()
|
|
{
|
|
//所有企业
|
|
var allCorp = mySqlhelper.ExecuteDataDictionary("select * from ww_corp");
|
|
//设置缓存表内存
|
|
mySqlhelper.ExecuteScalar("SET GLOBAL tmp_table_size = 3 * 1024*1024*1024");
|
|
mySqlhelper.ExecuteScalar("SET GLOBAL innodb_buffer_pool_size = 3 * 1024*1024*1024");
|
|
//查询是否有数据
|
|
//判断昨天是否已有数据插入
|
|
var dataCountDic = mySqlhelper.ExecuteDataDictionary($"select Count(*) as count from ww_message_cacheLog where msgtime >= '{DateTime.Now.Date.AddDays(-1)}' and msgtime< '{DateTime.Now.Date}' ");
|
|
var dataCount = Convert.ToInt32(dataCountDic.First()["count"]);
|
|
if (dataCount == 0)
|
|
{
|
|
foreach (var item in allCorp)
|
|
{
|
|
var DBName = $"wework_{item["corpid"]}";//库名
|
|
var corpId = item["corpid"].ToString();//企业号id
|
|
var tabName = $"{DBName}.ww_message_{DateTime.Now.ToString("yyyyMM")}";
|
|
//将昨日数据插入临时表
|
|
var sql = $@" insert into ww_message_cacheLog (`corpid`,`fromer`,`tolist`,msgtime)
|
|
select '{corpId}' as `corpid`,fromer, tolist, msgtime from {tabName}
|
|
where msgtime >= '{DateTime.Now.Date.AddDays(-1)}' and msgtime< '{DateTime.Now.Date}' and roomid = ''";
|
|
|
|
mySqlhelper.ExecuteNonQuery(sql, 600);//数据量较大 等待10分钟
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 统计并推送
|
|
/// </summary>
|
|
public static void Push()
|
|
{
|
|
IList<IDictionary<string, string>> dataList = new List<IDictionary<string, string>>();
|
|
//发送记录
|
|
var pushList = mySqlhelper.ExecuteDataDictionary($@"
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
select a.corpid,fromer,tolist,count(*) as `sum`
|
|
from ww_message_cacheLog a
|
|
left join ww_hhuser b on a.fromer=b.userid and a.corpid=b.corpid
|
|
where b.userid is not null
|
|
group by a.corpid,fromer,tolist;
|
|
COMMIT;", 30);
|
|
|
|
//接受记录
|
|
var getList = mySqlhelper.ExecuteDataDictionary($@"
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
select a.corpid,fromer,tolist,count(*) as `sum`
|
|
from ww_message_cacheLog a
|
|
left join ww_hhuser b on a.tolist=b.userid and a.corpid=b.corpid
|
|
where b.userid is not null
|
|
group by a.corpid,fromer,tolist;
|
|
COMMIT;", 30);
|
|
|
|
//接受记录 tolist=> fromer颠倒
|
|
var getDic = getList.ToDictionary(m => $"{m["corpid"]}_{m["tolist"]}_{m["fromer"]}", n => n["sum"].ToString());
|
|
foreach (var item in pushList)
|
|
{
|
|
IDictionary<string, string> r = new Dictionary<string, string>();
|
|
var key = $"{item["corpid"]}_{item["fromer"]}_{item["tolist"]}";
|
|
r.Add("id", "LAST_INSERT_ID()");
|
|
r.Add("appid", item["corpid"].ToString());
|
|
r.Add("userid", item["fromer"].ToString());
|
|
r.Add("externaluserid", item["tolist"].ToString());
|
|
r.Add("pushCount", item["sum"].ToString());
|
|
if (getDic.TryGetValue(key, out string getCount))
|
|
{
|
|
r.Add("getCount", getCount);
|
|
}
|
|
else
|
|
{
|
|
r.Add("getCount", "0");
|
|
}
|
|
r.Add("ctime", DateTime.Now.ToString());
|
|
r.Add("smgDate", DateTime.Now.Date.AddDays(-1).ToString());//每次统计昨天
|
|
r.Add("ispush", "0");//是否推送
|
|
dataList.Add(r);
|
|
}
|
|
//清空本次请求需要入库的现有数据（当日重复触发时需要）
|
|
mySqlhelper.ExecuteNonQuery($"delete from ww_message_summary where msgDate = '{DateTime.Now.Date.AddDays(-1)}'");
|
|
//插入dncms库
|
|
mySqlhelper.InsertBigList(dataList, "ww_message_summary");
|
|
//清空已经处理数据
|
|
mySqlhelper.ExecuteNonQuery($"truncate table ww_message_cacheLog");
|
|
}
|
|
}
|
|
}
|