public async Task AddEducation(string word, string reply)
{
var serverId = Context.Guild.Id;
var sql = string.Format("SELECT * FROM 自動応答 WHERE サーバーID = '{0}' AND 言葉 = '{1}'",
serverId,
word);
var table = DBAccesser.RunSQLGetResult(sql);
sql = string.Format("INSERT INTO 自動応答 VALUES ('{0}', '{1}', '{2}')",
serverId,
word,
reply);
if (DBAccesser.RunSQL(sql))
{
await ReplyAsync(string.Format("言葉:「{0}」 返事:「{1}」を覚えました",
word,
reply));
}
else
{
await ReplyAsync("うまく覚えられませんでした・・・");
}
}
public Form1()
{
InitializeComponent();
_db = new DBAccesser();
IRManager = new RetrievalManager();
string[] docs = IRManager.GetSourceDoc();
IRManager.TransferInvertedDocs(docs);
//string[] str = util.Tokenize(@"C:\Users\ligal\source\repos\IR project\IR project\SourceFiles\a love lesson.html");
// util.ParseFiles(@"C:\Users\ligal\source\repos\IR project\IR project\SourceFiles\a love lesson.txt");
}
public ActionResult ImportData(string isB2BData)
{
ViewBag.IsB2BData = Convert.ToBoolean(isB2BData);
ViewBag.Title = ViewBag.IsB2BData ? "Import B2B Data" : "Import B2C Data";
if (Request.Files[0] != null && Request.Files[0].ContentLength > 0)
{
string fileName = string.Format("{0}_{1}", DateTime.Now.ToFileTimeUtc(), Path.GetFileName(Request.Files[0].FileName));
string filePath = Server.MapPath("~/TempExcelFiles/UpLoad/" + fileName);
Request.Files[0].SaveAs(filePath);
DataTable tb = ExcelHelper.GetTableFromExcel("导入数据", filePath);
if (Convert.ToBoolean(isB2BData))
{
DataTable masterDatas = GetPartData(tb, m_B2BMastersFields.Select(p => p.FieldName).ToList());
DataTable contactDatas = GetPartData(tb, m_B2BContactsFields.Select(p => p.FieldName).ToList());
foreach (DataRow item in masterDatas.Rows)
{
string tempSql = GetUpdateSql(m_B2BMastersTableName, item, "公司名称");
if (tempSql.Length > 0)
{
DBAccesser.GetData(DBAccesser.DefaultConnection, tempSql);
}
}
foreach (DataRow item in contactDatas.Rows)
{
string tempSql = GetUpdateSql(m_B2BContactsTableName, item, "公司名称", "联系人姓名");
if (tempSql.Length > 0)
{
DBAccesser.GetData(DBAccesser.DefaultConnection, tempSql);
}
}
}
else
{
DataTable masterDatas = GetPartData(tb, m_B2CMastersFields.Select(p => p.FieldName).ToList());
foreach (DataRow item in masterDatas.Rows)
{
DBAccesser.GetData(DBAccesser.DefaultConnection, GetUpdateSql(m_B2CMastersTableName, item, "姓名", "联系电话"));
}
}
DeleteUploadExcel(filePath);
ViewBag.ImportMsg = "导入成功!";
}
return(View("ImportData"));
}
public async Task RemoveLoginNotification()
{
var serverId = Context.Guild.Id;
var sql = string.Format("DELETE FROM ログイン WHERE サーバーID = '{0}'",
serverId);
if (DBAccesser.RunSQL(sql))
{
await ReplyAsync(string.Format("ログイン通知を削除しました"));
}
else
{
await ReplyAsync("ログイン通知を削除できませんでした");
}
}
private void WriteToDB(DataRow row, DataColumnCollection columns, bool isB2BData)
{
try
{
string tableName = isB2BData ? "B2BDatas" : "B2CDatas";
//filter 需要确认,即需要确认那些字段可以确定唯一一条记录,逻辑意义上, 这些字段在上传的时候是比需要有的字段
string filter = string.Format("数据编码=N'{0}'", Convert.ToString(row["数据编码"]).Replace("'", "''").Trim());
string fieldValues = string.Empty;
StringBuilder sql = new StringBuilder();
sql.AppendLine(string.Format("if exists(select top 1 1 from dataCenter.dbo.{0} with(nolock) where {1})", tableName, filter));
sql.AppendLine("begin");
sql.AppendLine(string.Format("update dataCenter.dbo.{0}", tableName));
sql.AppendLine("set ");
for (int i = 0; i < columns.Count; i++)
{
sql.AppendLine(string.Format("{0}=N'{1}'", columns[i].ColumnName, Convert.ToString(row[columns[i]]).Replace("'", "''").Trim()));
if (i != columns.Count - 1)
{
sql.Append(",");
}
}
sql.AppendLine(string.Format("where {0}", filter));
sql.AppendLine("end");
sql.AppendLine("else");
sql.AppendLine("begin");
sql.AppendLine(string.Format("insert into dataCenter.dbo.{0}", tableName));
sql.AppendLine("(" + string.Join(",", columns.Cast <DataColumn>().Select(p => p.ColumnName)) + ")");
sql.AppendLine("values(");
for (int i = 0; i < columns.Count; i++)
{
sql.AppendLine(string.Format("N'{0}'", Convert.ToString(row[columns[i]]).Replace("'", "''").Trim()));
if (i != columns.Count - 1)
{
sql.Append(",");
}
}
sql.Append(")");
sql.AppendLine("end");
string tempSql = sql.ToString();
DBAccesser.GetData(DBAccesser.DefaultConnection, tempSql);
}
catch (Exception ex)
{
throw;
}
}
public async Task ListEducation()
{
var serverId = Context.Guild.Id;
var sql = string.Format("SELECT * FROM 自動応答 WHERE サーバーID = '{0}'",
serverId);
var table = DBAccesser.RunSQLGetResult(sql);
foreach (DataRow row in table.Rows)
{
var word = row["言葉"].ToString();
var reply = row["返事"].ToString();
await ReplyAsync("言葉:" + word);
await ReplyAsync("返事:" + reply);
}
}
public async Task RemoveEducation(string word)
{
var serverId = Context.Guild.Id;
var sql = string.Format("DELETE FROM 自動応答 WHERE サーバーID = '{0}' AND 言葉 = '{1}'",
serverId,
word);
if (DBAccesser.RunSQL(sql))
{
await ReplyAsync(string.Format("言葉:「{0}」を忘れました",
word));
}
else
{
await ReplyAsync("うまく覚えられませんでした・・・");
}
}
public async Task Reply(SocketUserMessage message)
{
var channel = message.Channel as SocketGuildChannel;
var serverId = channel.Guild.Id;
var word = message.Content;
var sql = string.Format("SELECT * FROM 自動応答 WHERE サーバーID = '{0}' AND 言葉 = '{1}'",
serverId,
word);
var table = DBAccesser.RunSQLGetResult(sql);
foreach (DataRow row in table.Rows)
{
var reply = row["返事"].ToString();
await message.Channel.SendMessageAsync(reply);
}
}
public string ExportData()
{
bool isB2BData = Convert.ToBoolean(Request.QueryString["isB2BData"]);
string sqlSelect = Request.Form["Columns"];
string sqlWhere = string.Empty;
for (int i = 0; i < Request.Form.Keys.Count; i++)
{
string fieldNameKey = string.Format("Filters[{0}][FieldName]", i);
string operationSignKey = string.Format("Filters[{0}][OperationSign]", i);
string inputValueKey = string.Format("Filters[{0}][InputValue]", i);
if (Request.Form[fieldNameKey] != null && Request.Form[operationSignKey] != null && Request.Form[inputValueKey] != null)
{
sqlWhere += string.Format(" {0} {1} {2} and", Request.Form[fieldNameKey], Request.Form[operationSignKey], Request.Form[inputValueKey]);
}
}
string sql = string.Empty;
if (isB2BData)
{
sql = string.Format("select distinct {0} from dataCenter.dbo.B2BMasters with(nolock) left join dataCenter.dbo.B2BContacts with(nolock) on B2BContacts.公司名称 = B2BMasters.公司名称 where {1}", sqlSelect, sqlWhere.TrimEnd(" and".ToArray()));
}
else
{
sql = string.Format("select distinct {0} from dataCenter.dbo.B2CMasters with(nolock) where {1}", sqlSelect, sqlWhere.TrimEnd(" and".ToArray()));
}
DataSet ds = DBAccesser.GetData(DBAccesser.DefaultConnection, sql);
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
string excelXmlStr = ExcelHelper.DataTableToExcelTableXML(ds.Tables[0], null);
string fileName = string.Format("{0}_{1}.xml", DateTime.Now.ToFileTimeUtc(), isB2BData ? "B2BDatas" : "B2CDatas");
string filePath = Server.MapPath("~/TempExcelFiles/DownLoad/" + fileName);
SaveExcel(filePath, excelXmlStr);
return(fileName);
}
else
{
return("error-nodata");
}
}
public async Task RemoveLoginNotification(string twitterId)
{
var channelId = Context.Channel.Id;
var sql = string.Format("DELETE FROM Twitter通知 WHERE チャンネルID = '{0}' AND TwitterID = '{1}'",
channelId,
twitterId);
if (DBAccesser.RunSQL(sql))
{
await ReplyAsync(string.Format("このチャンネルでの「{0}」さんのツイート通知を停止しました",
twitterId));
}
else
{
await ReplyAsync(string.Format("このチャンネルでの「{0}」さんのツイート通知を停止できませんでした",
twitterId));
}
}
/// <summary>
/// ユーザーが会議から抜けたときの処理
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
private async Task UserLeft(SocketGuildUser user)
{
// 退出通知メッセージを流す
var sql = string.Format("SELECT * FROM ログアウト WHERE サーバーID = '{0}'",
user.Guild.Id);
var table = DBAccesser.RunSQLGetResult(sql);
if (table.Rows.Count > 0)
{
var row = table.Rows[0];
var channelId = ulong.Parse(row["チャンネルID"].ToString());
var word = row["内容"].ToString();
word = word.Replace("{ServerName}", user.Guild.Name);
word = word.Replace("{UserName}", user.Username);
await user.Guild.GetTextChannel(channelId).SendMessageAsync(word);
}
}
public async Task AddLoginNotification(string word)
{
var serverId = Context.Guild.Id;
var channelId = Context.Channel.Id;
var sql = string.Format("SELECT * FROM ログイン WHERE サーバーID = '{0}'",
serverId);
var table = DBAccesser.RunSQLGetResult(sql);
if (table.Rows.Count > 0)
{
sql = string.Format("UPDATE ログイン SET チャンネルID = '{0}', 内容 = '{1}' WHERE サーバーID = '{2}'",
channelId,
word,
serverId);
if (DBAccesser.RunSQL(sql))
{
await ReplyAsync("ログイン通知を設定し直しました");
}
else
{
await ReplyAsync("ログイン通知を設定できませんでした");
}
}
else
{
sql = string.Format("INSERT INTO ログイン VALUES ('{0}', '{1}', '{2}')",
serverId,
channelId,
word);
if (DBAccesser.RunSQL(sql))
{
await ReplyAsync("ログイン通知を設定しました");
}
else
{
await ReplyAsync("ログイン通知を設定できませんでした");
}
}
}
public async Task SendReply(SocketUserMessage message)
{
var channel = message.Channel as SocketGuildChannel;
var serverId = channel.Guild.Id;
var content = message.Content;
var sql = string.Format("SELECT * FROM 自動応答 WHERE サーバーID = '{0}'",
serverId);
using (var table = DBAccesser.RunSQLGetResult(sql))
{
var list = new List <string>();
foreach (DataRow row in table.Rows)
{
string word = row["言葉"].ToString();
string reply = row["返事"].ToString();
if (content.Contains(word))
{
list.Add(reply);
}
}
if (list.Count > 0)
{
var rnd = new Random();
var reply = list[rnd.Next(list.Count)];
if (reply == "$")
{
await message.Channel.SendMessageAsync(API.Talk.GetChat(content));
}
else
{
await message.Channel.SendMessageAsync(reply);
}
}
}
await Task.Delay(1);
}
public async Task MainAsync()
{
// DBがない場合再作成
DBAccesser.CreateDB();
// Discordへの接続
client = new DiscordSocketClient();
commands = new CommandService();
services = new ServiceCollection().BuildServiceProvider();
client.MessageReceived += CommandRecieved;
client.UserJoined += UserJoined;
client.UserLeft += UserLeft;
client.Log += Log;
await commands.AddModulesAsync(Assembly.GetEntryAssembly(), services);
await client.LoginAsync(TokenType.Bot, RiptDiscordBot.Common.Connection.DiscordToken);
await client.StartAsync();
await Task.Delay(-1);
}
public async Task AddLoginNotification(string twitterId, string english = "false")
{
var isEnglish = english == "true" ? true : false;
var channelId = Context.Channel.Id;
var sql = string.Format("SELECT * FROM Twitter通知 WHERE チャンネルID = '{0}' AND TwitterID = '{1}'",
channelId,
twitterId);
var table = DBAccesser.RunSQLGetResult(sql);
if (table.Rows.Count > 0)
{
sql = string.Format("UPDATE Twitter通知 SET 英語 = '{0}' WHERE チャンネルID = '{1}', TwitterID = '{2}'",
isEnglish,
channelId,
twitterId);
if (DBAccesser.RunSQL(sql))
{
if (isEnglish)
{
await ReplyAsync(string.Format("このチャンネルで「{0}」さんのツイートを日本語に翻訳して通知します。",
twitterId));
}
else
{
await ReplyAsync(string.Format("このチャンネルで「{0}」さんのツイートを通知します。",
twitterId));
}
}
else
{
await ReplyAsync("Twitter通知を登録できませんでした");
}
}
else
{
sql = string.Format("INSERT INTO Twitter通知 VALUES ('{0}', '{1}', '{2}')",
channelId,
twitterId,
isEnglish);
if (DBAccesser.RunSQL(sql))
{
if (isEnglish)
{
await ReplyAsync(string.Format("このチャンネルで「{0}」さんのツイートを日本語に翻訳して通知します。",
twitterId));
}
else
{
await ReplyAsync(string.Format("このチャンネルで「{0}」さんのツイートを通知します。",
twitterId));
}
}
else
{
await ReplyAsync("Twitter通知を登録できませんでした");
}
}
}
public void Next(Status status)
{
var screenName = status.User.ScreenName; // TUSkyBlock等
var name = status.User.Name; // TUSB等
var text = status.Text; // つぶやき内容
var image = "";
var id = status.Id;
if (status.Entities.Media != null)
{
image = status.Entities.Media.First().MediaUrlHttps;
}
// 自動リプライ応答
if (text.Contains("@TUSkyBlock") && (!text.Contains("RT ")) && screenName != "TUSkyBlock")
{
var str = Docomo.ChatAI.GetChat(text, screenName);
str = "@" + screenName + " " + str;
token.Statuses.Update(new { status = str, in_reply_to_status_id = id });
}
// つぶやき通知
var sql = string.Format("SELECT * FROM Twitter通知 WHERE TwitterID = '{0}'",
screenName);
var table = DBAccesser.RunSQLGetResult(sql);
if (table.Rows.Count > 0)
{
var title = $"{name}さんのつぶやき";
if (text.Contains("RT "))
{
title = $"{name}さんのリツイート";
}
var eb = new EmbedBuilder()
{
Author = new EmbedAuthorBuilder()
{
IconUrl = new Uri(status.User.ProfileImageUrlHttps).ToString(),
Name = $"{name}",
Url = $"https://twitter.com/{screenName}",
},
Color = new Color(0x55ACEE),
Title = title,
Url = new Uri($"https://twitter.com/{screenName}/status/{status.Id}").ToString(),
Description = $"{text}",
ImageUrl = image,
ThumbnailUrl = status.User.ProfileImageUrlHttps,
Footer = new EmbedFooterBuilder()
{
IconUrl = status.User.ProfileImageUrlHttps,
Text = $"ID:{screenName} | フォロー：{status.User.FriendsCount} フォロワー：{status.User.FollowersCount}"
}
};
var channelid = ulong.Parse(table.Rows[0][0].ToString());
var channel = _client.GetChannel(channelid) as SocketTextChannel;
channel.SendMessageAsync("", embed: eb);
}
}
