public async Task DoRun()
{
IDBHelper db = new MssqlHelper(JobConfig.ConnectionStrings["YiliscrmDb"]);
var accounts = await db.FindToListAsync <MpAccountDto>("SELECT * FROM MpAccounts WHERE ISDELETED=0 ", null, false);
var sendList = await db.FindToListAsync <MpCourseSignupDto>("SELECT * FROM MpCourseSignups WHERE ISDELETED=0 AND DATEDIFF(HOUR,GETDATE(),BEGINTIME)<=2 and SendMessageState=0", null, false);
var handler = new HttpClientHandler()
{
AutomaticDecompression = System.Net.DecompressionMethods.None
};
var url = JobConfig.AppSettings["SendTemplateMessageUrl"];
var templateId = JobConfig.AppSettings["TemplateID"];
var coursedetialurl = JobConfig.AppSettings["WKTCourseDetailUrl"];
using (HttpClient httpclient = new HttpClient(handler))
{
httpclient.BaseAddress = new Uri(url);
foreach (var item in sendList)
{
var tempdata = new {
touser = item.OpenID,
template_id = templateId,
url = coursedetialurl,
data = new
{
first = new { value = "亲爱的会员您好，您报名的课程即将开始" }, //标题
keyword1 = new { value = item.CourseName }, //课程名称
keyword2 = new { value = item.BeginTime }, //课程时间
keyword3 = new { value = item.Address }, //上课地点
keyword4 = new { value = "400-800-1111" }, //联系方式
remark = new { value = item.Reamrk }//备注
}
};
var token = accounts.Where(m => m.Id == item.MpID).FirstOrDefault().TaskAccessToken;
var content = new FormUrlEncodedContent(new Dictionary <string, string>()
{
{ "data", JsonConvert.SerializeObject(tempdata) }
});
var response = await httpclient.PostAsync(url, content);
string responseString = await response.Content.ReadAsStringAsync();
await db.ExcuteNonQueryAsync("Update MpCourseSignups Set SendTime=GetDate(),SendMessageState=1,SendResult='" + responseString.Replace("'", "''") + "' WHERE ID=" + item.Id, null, false);
}
}
}
/// <summary>
/// 按openid逐组群发
/// </summary>
/// <param name="account"></param>
/// <param name="args"></param>
/// <param name="taskId"></param>
/// <param name="totalCount"></param>
public async Task SendMessageAsync(MpAccountDto account, MpMessageDto args, long totalCount)
{
IDBHelper db = new MssqlHelper(JobConfig.ConnectionStrings["YiliscrmGroupMessage"]);
IDBHelper yilidb = new MssqlHelper(JobConfig.ConnectionStrings["YiliscrmDb"]);
var msgResult = await db.FindToListAsync <TaskGroupMessageDto>($"SELECT * FROM TaskGroupMessages WHERE ISDELETED=0 AND MpID='{args.MpID}' AND TaskState='{(int)MpMessageTaskState.Wait}' AND MessageID='" + args.Id + "'", null, false);
if (JobConfig.AppSettings["Debugger"] != "true")//正式发送
{
string updateSql = "Update TaskGroupMessages SET TaskState='{1}',WxMsgID='{2}' WHERE ID='{0}'";
foreach (var drItem in msgResult)
{
var result = await SendByOpenIds(account, args, drItem.OpenIDs.Split(','), yilidb);
if (result != null && result.errcode == ReturnCode.请求成功)
{
await db.ExcuteNonQueryAsync(string.Format(updateSql, drItem.Id, (int)MpMessageTaskState.Doing, result.msg_id), null, false);
}
else
{
await db.ExcuteNonQueryAsync(string.Format(updateSql, drItem.Id, (int)MpMessageTaskState.Fail, ""), null, false);
}
}
}
else
{
foreach (var drItem in msgResult)
{
await db.ExcuteNonQueryAsync($"Update TaskGroupMessages SET TaskState='{(int)MpMessageTaskState.Success}',SuccessCount='{drItem.SendCount}',FailCount=0 WHERE ID='{drItem.Id}'", null, false);
}
yilidb.ExcuteNonQuery($"UPDATE MpMessages SET SuccessCount=totalCount,FailCount=0,FinishDate=GetDate(),SendCount={totalCount},SendState='{(int)MpMessageTaskState.Success}' Where ID={args.Id}", null, false);
}
}
public async Task TimeSendCloseText()
{
IDBHelper db = new MssqlHelper(JobConfig.ConnectionStrings["Kf"]);
IDBHelper db2 = new MssqlHelper(JobConfig.ConnectionStrings["YiliscrmDb"]);
var accounts = await db2.FindToListAsync <MpAccountDto>("SELECT * FROM MpAccounts WHERE ISDELETED=0 ", null, false);
WebClient wc = new WebClient();
foreach (var item in accounts)
{
var state = (int)CustomerServiceConversationState.Asking;
var allAskingList = await db.FindToListAsync <CustomerServiceConversation>($"SELECT * FROM CustomerServiceConversations WHERE MPID={item.Id} AND STATE={state}", null, false); //提问中的列表
var defaultCustomer = await db.FindOneAsync <CustomerServiceOnline>($"SELECT TOP 1 * FROM CustomerServiceOnlines WHERE KfType='WX' AND IsDeleted=0", null, false); //获取默认微信客服
foreach (var conversation in allAskingList)
{
var fansId = Convert.ToInt32(conversation.FanId);
var lastMsg = await db.FindOneAsync <CustomerServiceConversationMsg>($"SELECT TOP 1 * FROM CustomerServiceConversationMsgs WHERE MPID={item.Id} AND FanId={fansId} ORDER BY CreationTime Desc", null, false);
if (lastMsg != null)
{
if (DateTime.Now >= lastMsg.CreationTime.AddMinutes(30))//关闭对话
{
await db.ExcuteNonQueryAsync($"UPDATE CustomerServiceConversations SET EndTalkTime=Getdate(),State={(int)CustomerServiceConversationState.Closed} WHERE ID={conversation.Id}", null, false);
var values = new NameValueCollection();
values.Add("conversationId", conversation.Id.ToString());//会话ID
await wc.UploadValuesTaskAsync(JobConfig.AppSettings["AutoStopConversationUrl"], values);
}
//else if (DateTime.Now < lastMsg.CreationTime.AddMinutes(30) && DateTime.Now > lastMsg.CreationTime.AddMinutes(15))//推送即将关闭的客服消息
//{
// if (defaultCustomer != null)//默认客服推送消息
// {
// //推送消息
// string token = null;
// var tokenApiResult = await StaticObjects.GetAccessToken(item.TaskAccessToken);
// token = tokenApiResult.access_token;
// try
// {
// await CustomApi.SendTextAsync(token, conversation.FanOpenId, "您已经很久没有和客服对话了，对话稍后将会自动关闭。", kfAccount: defaultCustomer.KfAccount);
// }
// catch (Exception ex)
// {
// tokenApiResult = await StaticObjects.GetAccessToken(item.TaskAccessToken, 1);
// token = tokenApiResult.access_token;
// try
// {
// await CustomApi.SendTextAsync(token, conversation.FanOpenId, "您已经很久没有和客服对话了，对话稍后将会自动关闭。", kfAccount: defaultCustomer.KfAccount);
// }
// catch (Exception e)
// {
// await CustomApi.SendTextAsync(token, conversation.FanOpenId, "您已经很久没有和客服对话了，对话稍后将会自动关闭。", kfAccount: defaultCustomer.KfAccount);
// throw e;
// }
// }
// }
//}
}
}
}
}
//[AutomaticRetry(Attempts = 1)]
public async Task StatistDatas()
{
IDBHelper defaultdb = new MssqlHelper(JobConfig.ConnectionStrings["YiliscrmDb"]);
var accounts = await defaultdb.FindToListAsync <MpAccountDto>("SELECT * FROM MpAccounts WHERE ISDELETED=0 ", null, false);
foreach (var account in accounts)
{
IDBHelper db = new MssqlHelper(JobConfig.ConnectionStrings["Kf"]);
var now = DateTime.Now;
var startTime = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
var endTime = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
string msgSql = $"SELECT * FROM CustomerServiceConversationMsgs WHERE CreationTime>='{startTime}' AND CreationTime<='{endTime}' AND Sender={(int)CustomerServiceMsgSender.customer} AND MpID={account.Id}";
var msgList = await db.FindToListAsync <CustomerServiceConversationMsgDto>(msgSql, null, false);
string conSql = $"SELECT * FROM CustomerServiceConversations WHERE MpID={account.Id} AND CreationTime>='{startTime}' AND CreationTime<='{endTime}'";
var conList = await db.FindToListAsync <CustomerServiceConversationDto>(conSql, null, false);
string inoutLogSql = $"SELECT * FROM CustomerInOutLogs WHERE CreationTime>='{startTime}' AND CreationTime<='{endTime}'";
var inoutList = await db.FindToListAsync <LogInout>(inoutLogSql, null, false);
var customers = await db.FindToListAsync <CustomerServiceOnlineDto>($"SELECT * FROM dbo.CustomerServiceOnlines WHERE KfType='{KFType.YL.ToString()}' AND IsDeleted=0 AND MpID={account.Id}", null, false);
foreach (var customer in customers)
{
var cusMsgList = msgList.Where(m => m.CustomerId == customer.Id).ToList();
var cusConList = conList.Where(m => m.CustomerId == customer.Id).ToList();
var cusInoutList = inoutList.Where(m => m.CustomerId == customer.Id).OrderBy(m => m.CreationTime).ToList();
CustomerServiceReportDto model = new CustomerServiceReportDto();
model.ServiceMsgCount = cusMsgList.Count();
model.ServiceCount = cusConList.Count();
model.ReceiveCount = cusConList.Select(m => m.FanId).Distinct().Count();
var source = cusConList.Where(m => m.ConversationScore > -1).ToList();
if (source.Count > 0)
{
model.AvgScore = Math.Round(Convert.ToDecimal(source.Average(m => m.ConversationScore)), 2);
}
model.MpID = account.Id;
model.CreationTime = DateTime.Now;
model.NickName = customer.KfNick;
model.ReportDate = startTime;
model.CustomerId = customer.Id;
model.ScoreCount = source.Count();
model.TotalScore = source.Sum(m => m.ConversationScore);
#region onlineTime
var t = 0;
var start = false;
var minutes = 0.0;
DateTime?_sD = null;
DateTime?_eD = null;
for (int i = 0; i < cusInoutList.Count; i++)
{
if (!start)
{
if (cusInoutList[i].InOutState == (int)InOutState.Login || cusInoutList[i].InOutState == (int)InOutState.online || cusInoutList[i].InOutState == (int)InOutState.ReLogin)
{
start = true;
_sD = cusInoutList[i].CreationTime;
}
else
{
continue;
}
}
else
{
if (_sD.HasValue)
{
if (cusInoutList[i].InOutState == (int)InOutState.Logout || cusInoutList[i].InOutState == (int)InOutState.leave || cusInoutList[i].InOutState == (int)InOutState.quit)
{
_eD = cusInoutList[i].CreationTime;
TimeSpan sp = _eD.Value.Subtract(_sD.Value);
minutes += sp.TotalMinutes;
_sD = null;
_eD = null;
}
else
{
continue;
}
}
else
{
if (cusInoutList[i].InOutState == (int)InOutState.Login || cusInoutList[i].InOutState == (int)InOutState.online || cusInoutList[i].InOutState == (int)InOutState.ReLogin)
{
_sD = cusInoutList[i].CreationTime;
}
else
{
continue;
}
}
}
}
model.OnlineTime = Math.Round(Convert.ToDecimal(minutes) / 60, 2);
#endregion
var sql = model.GetInsertSql("CustomerServiceReports", "Id");
await db.ExcuteNonQueryAsync(sql, null, false);
}
}
}
private async Task DoRun()
{
IDBHelper db = new MssqlHelper(JobConfig.ConnectionStrings["YiliscrmDb"]);
var accounts = await db.FindToListAsync <MpAccountDto>("SELECT * FROM MpAccounts WHERE ISDELETED=0 ", null, false);
var Messages = await db.FindToListAsync <MpMessageDto>($"SELECT * FROM MpMessages WHERE ISDELETED=0 AND ISTASK=1 AND ExecTaskTime<=GETDATE() AND SendState={(int)MpMessageTaskState.Wait}", null, false);
List <string> successList = new List <string>();
foreach (var args in Messages)
{
var account = accounts.Where(m => m.Id == args.MpID).FirstOrDefault();
args.WxMsgID = Guid.NewGuid().ToString();
await db.ExcuteNonQueryAsync($"Update MpMessages Set SendState='{(int)MpMessageTaskState.Doing}' Where ID='{args.Id}'", null, false);//正在发送
if (!string.IsNullOrWhiteSpace(args.GroupIds))
{
if (args.IsMember == IsMemberEnum.ALL.ToString())
{
var fancount = await db.ExecuteScalarAsync("SELECT count(*) FROM MpFans WHERE ISDELETED=0 AND MpID=" + args.MpID, null, false);
await SendAllAsync(account, args, Convert.ToInt64(fancount));
}
else if (args.IsMember == IsMemberEnum.Tag.ToString())
{
var tagids = new List <int>();
var tagidstr = args.TargetID.Split(',', StringSplitOptions.RemoveEmptyEntries);
int tagid = -1;
foreach (var item in tagidstr)
{
if (int.TryParse(item, out tagid))
{
tagids.Add(tagid);
}
}
if (tagids.Count == 0)
{
tagids.Add(-1);
}
var _fan = await db.FindToListAsync <MpFanDto>("SELECT a.OpenID FROM MpFans a inner join MpFansTagItems b on a.Id=b.FansId WHERE a.ISDELETED=0 AND a.MpID=" + args.MpID + " AND b.TagId in (" + string.Join(",", tagids) + ")", null, false);
await SaveIntoGroupMessage(args, _fan.Select(m => m.OpenID));
await SendMessageAsync(account, args, _fan.LongCount());
}
else if (args.IsMember == IsMemberEnum.NotMember.ToString())
{
var _fan = await db.FindToListAsync <MpFanDto>("SELECT OpenID FROM MpFans WHERE ISDELETED=0 AND MemberID=0 AND MpID=" + args.MpID, null, false);
await SaveIntoGroupMessage(args, _fan.Select(m => m.OpenID));
await SendMessageAsync(account, args, _fan.LongCount());
}
else
{
var messageResult = await StaticObjects.GetMessageResponse(account.Token, account.Id, args.Id);
await SaveIntoGroupMessage(args, messageResult.OpenIDs);
await SendMessageAsync(account, args, messageResult.OpenIDs.LongCount());
}
}
else
{
var fancount = await db.ExecuteScalarAsync("SELECT count(*) FROM MpFans WHERE ISDELETED=0 AND MpID=" + args.MpID, null, false);
await SendAllAsync(account, args, Convert.ToInt64(fancount));
}
}
}