|
public mysql_query ( string sql ) : |
||
| sql | string | |
| Результат | ||
public SearchForm(List<string> dbInfo)
{
InitializeComponent();
this.dbInfo = dbInfo;
postSort = new bool[levelOneList.Columns.Count];
string sql = "select id, name from pages_info";
DBConnection conn = new DBConnection(this.dbInfo);
conn.MySqlConnect();
MySqlDataReader res = conn.mysql_query(sql);
while (res.Read())
{
pagesID.Add(res.GetString(0));
pagesName.Add(res.GetString(1));
page_list.Items.Add(res.GetString(1));
}
page_list.Text = page_list.Items[0].ToString();
page_list.SelectedIndex = 0;
res.Close();
}
private void startUpdateData(string newIDSince)
{
bool continueUpdate = false;
if (updatePageLogsCheck.Checked)
continueUpdate = true;
while (true)
{
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
string sql = "select * from access_token where 1";
MySqlDataReader res = null;
while (true)
{
try
{
res = conn.mysql_query(sql);
res.Read();
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
DateTime nextUpdateTime = DateTime.Now.AddDays((1.0) * (int)pagesLogUpdateF.Value);
DateTime untileDate = DateTime.Now.AddDays((-1.0) * (int)recentlyDays.Value);
string since = untileDate.Year.ToString() + "-" + untileDate.Month.ToString() + "-" + untileDate.Day.ToString();
string access_token = res.GetString(0);
res.Close();
string IDString = "";
foreach (ListViewItem lvi in pagesInfo.CheckedItems)
{
IDString += lvi.Text + ",";
}
IDString = IDString.Substring(0, IDString.Length - 1);
sql = "select id,name,new_id from pages_info where new_id >= 0 and id in (" + IDString + ")";
List<string[]> pageList = new List<string[]>();
while (true)
{
try
{
pageList.Clear();
res = conn.mysql_query(sql);
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
while (res.Read())
{
string[] tmpPage = { res.GetString(0), res.GetString(1), res.GetString(2) };
pageList.Add(tmpPage);
}
res.Close();
foreach (string[] pagesInfoArray in pageList)
{
DateTime startingTime = DateTime.Now;
showMessage.AppendText("Now updating " + pagesInfoArray[1] + " at " + DateTime.Now.ToString() + "\n");
string url = "https://graph.facebook.com/" + pagesInfoArray[0];
//showMessage.AppendText("In firstThread since=" + newIDSince + "\n");
if (pagesInfoArray[2] == "1")
{
get_post(pagesInfoArray[0], access_token, newIDSince, null);
sql = "update pages_info set new_id = 0 where id = \"" + pagesInfoArray[0] + "\"";
while (true)
{
try
{
conn.mysql_query(sql).Close();
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
}
else
{
get_post(pagesInfoArray[0], access_token, since, null);
}
sql = "select count(*) from allinone where id = \"" + pagesInfoArray[0] + "\"";
while (true)
{
try
{
res = conn.mysql_query(sql);
res.Read();
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
string feedsCount = res.GetString(0);
res.Close();
sql = "select count(*) from pages_posts_comments where page_id = \"" + pagesInfoArray[0] + "\"";
while (true)
{
try
{
res = conn.mysql_query(sql);
res.Read();
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
string commentsCount = res.GetString(0);
res.Close();
List<string> pageInformation = pages_info(url);
if (pageInformation != null)
{
sql = "insert into pages_logs (id,name,picture,link,category,website,founded,info,likes,talking_about_count,updatetime,feeds,comments) values (" +
"?id,?name,?picture,?link,?category,?website,?founded,?info,?likes,?talking_about_count,?updatetime," + feedsCount + "," + commentsCount + ")";
while (true)
{
try
{
MySqlCommand thecmd = new MySqlCommand(sql, conn.conn);
thecmd.Parameters.AddWithValue("?id", pageInformation[0]);
thecmd.Parameters.AddWithValue("?name", pageInformation[1]);
thecmd.Parameters.AddWithValue("?picture", pageInformation[2]);
thecmd.Parameters.AddWithValue("?link", pageInformation[3]);
thecmd.Parameters.AddWithValue("?category", pageInformation[4]);
thecmd.Parameters.AddWithValue("?website", pageInformation[5]);
thecmd.Parameters.AddWithValue("?founded", pageInformation[6]);
thecmd.Parameters.AddWithValue("?info", pageInformation[7]);
thecmd.Parameters.AddWithValue("?likes", pageInformation[8]);
thecmd.Parameters.AddWithValue("?talking_about_count", pageInformation[9]);
thecmd.Parameters.AddWithValue("?updatetime", DateTime.Now);
MySqlDataReader themyData = thecmd.ExecuteReader();
themyData.Close();
thecmd.Parameters.Clear();
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
}
TimeSpan timeSpent = DateTime.Now.Subtract(startingTime);
sql = "update pages_info set timespent = \"" + timeSpent.TotalMinutes.ToString("#.#0") + "分鐘" + "\" where id = \"" + pagesInfoArray[0] + "\"";
while (true)
{
try
{
conn.mysql_query(sql).Close();
break;
}
catch (Exception ee)
{
Console.WriteLine(ee.StackTrace);
conn.closeMySqlConnection();
conn.MySqlConnect();
}
}
conn.closeMySqlConnection();
showMessage.AppendText(pagesInfoArray[1] + " was updated complete at " + DateTime.Now.ToString() + "\n");
}
if (continueUpdate)
{
try
{
TimeSpan ts = nextUpdateTime.Subtract(DateTime.Now);
//七天更新一次
showMessage.AppendText("預計下次更新時間為" + nextUpdateTime.ToString() + "\n\n");
Thread.Sleep(Convert.ToInt32(ts.TotalMilliseconds));
}
catch { }
}
else
{
showMessage.AppendText("更新結束 @" + DateTime.Now.ToString() + "\n");
startUpdate.Enabled = true;
cancleUpdate.Enabled = false;
break;
}
}
}
public void updatePageIDList()
{
pagesInfo.Items.Clear();
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
string sql = "select id,name,new_id,timespent from pages_info where new_id >= 0";
MySqlDataReader res = conn.mysql_query(sql);
if (res != null)
{
while (res.Read())
{
ListViewItem item = new ListViewItem(res.GetString(0));
item.SubItems.Add(res.GetString(1));
if (res.GetInt32(2) == 0)
{
item.SubItems.Add("否");
}
else
{
item.SubItems.Add("是");
}
if (res.GetString(3).Length > 0)
{
item.SubItems.Add(res.GetString(3));
}
else
{
item.SubItems.Add("Null");
}
pagesInfo.Items.Add(item);
}
res.Close();
}
conn.closeMySqlConnection();
}
private void SetAccessToken_Click(object sender, EventArgs e)
{
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
string sql = "truncate table access_token";
conn.mysql_query(sql).Close();
sql = "insert into access_token (access_token) values (\"" + accessTokenBox.Text + "\")";
conn.mysql_query(sql).Close();
pagesEditGroup.Enabled = true;
conn.closeMySqlConnection();
init_Timer();
}
public void checkCanUpdate()
{
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
string sql = "select count(*) from pages_info where 1";
MySqlDataReader res = conn.mysql_query(sql);
res.Read();
if (res.GetInt32(0) > 0)
{
startUpdate.Enabled = true;
}
else
{
startUpdate.Enabled = false;
}
res.Close();
conn.closeMySqlConnection();
}
private void dbConnect_Click(object sender, EventArgs e)
{
if (dbServerBox.Text.Length * dbAcoountBox.Text.Length * dbPasswordBox.Text.Length * dbTableBox.Text.Length == 0)
{
MessageBox.Show("資料庫資訊不得為空！");
return;
}
dbInfo.Clear();
dbInfo.Add(dbServerBox.Text);
dbInfo.Add(dbAcoountBox.Text);
dbInfo.Add(dbPasswordBox.Text);
dbInfo.Add(dbTableBox.Text);
string errorMessage = "";
DBConnection conn = new DBConnection(dbInfo);
if (!conn.checkDBExist())
{
MessageBox.Show("資料庫連線資訊錯誤!!");
return;
}
errorMessage = conn.MySqlConnect();
if (errorMessage.Length > 0)
{
MessageBox.Show(errorMessage);
return;
}
else
{
accessTokenGroup.Enabled = true;
string sql = "select count(*) from access_token where 1";
MySqlDataReader res = conn.mysql_query(sql);
res.Read();
if (res.GetInt32(0) > 0)
{
res.Close();
sql = "select access_token from access_token where 1";
res = conn.mysql_query(sql);
res.Read();
accessTokenBox.Text = res.GetString(0);
pagesEditGroup.Enabled = true;
res.Close();
checkCanUpdate();
init_Timer();
}
else
{
res.Close();
MessageBox.Show("請先指定Access Token");
}
}
FileStream stream = new FileStream(dbInfoFileName, FileMode.OpenOrCreate);
StreamWriter writer = new StreamWriter(stream);
string baseServer = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbServerBox.Text));
string baseAccount = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbAcoountBox.Text));
string basePWD = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbPasswordBox.Text));
string baseTable = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbTableBox.Text));
writer.WriteLine(baseServer + ":" + baseAccount + ":" + basePWD + ":" + baseTable);
writer.Close();
stream.Close();
updatePageIDList();
conn.closeMySqlConnection();
dbConnInfo.Enabled = false;
}
private void update_user_info_by_ids(StringBuilder sb, DBConnection conn)
{
Thread.Sleep(5 * 1000);
string sql = "";
string url = "https://graph.facebook.com/fql?q=select uid,username,sex from user where uid in (" + sb.ToString() + ")&access_token=" + accessTokenBox.Text;
//showMessage.AppendText(url);
//return;
FBGraph api = new FBGraph();
JSONObject result = api.FBGraphGet(url);
result = resultCheck(result, url, 0);
if (result == null)
{
return;
}
//try
//{
// int erro_count = 0;
// while (true)
// {
// if (result.Dictionary.ContainsKey("error_msg") || result.Dictionary.ContainsKey("error"))
// {
// //若發生error 檢查是何種error 若無error再透過exception離開迴圈
// string errorMessage = "";
// string errorNumber = "";
// if (result.Dictionary.ContainsKey("error_msg"))
// {
// errorMessage = result.Dictionary["error_msg"].String;
// errorNumber = result.Dictionary["error_code"].String;
// }
// else
// {
// errorMessage = result.Dictionary["error"].Dictionary["message"].String;
// errorNumber = result.Dictionary["error"].Dictionary["code"].String;
// }
// //Console.WriteLine("Error message(" + errorNumber + ") is : " + errorMessage);
// if (errorNumber.Equals("602"))
// {
// messageLog(errorMessage);
// //showMessage.AppendText(errorMessage + "\n");
// return;
// }
// else
// {
// Thread.Sleep(1000);
// erro_count++;
// if (erro_count > 30)
// {
// messageLog(errorMessage);
// //showMessage.AppendText(errorMessage + "\n");
// return;
// }
// }
// }
// else
// {
// break;
// }
// }
//}
//catch { }
sb.Clear();
Dictionary<string, string[]> uInfo = new Dictionary<string, string[]>();
if (result.Dictionary.ContainsKey("data") && result.Dictionary["data"].Array.Length > 0)
{
foreach (JSONObject pageData in result.Dictionary["data"].Array)
{
string[] tmpString = { pageData.Dictionary["username"].String, pageData.Dictionary["sex"].String };
uInfo.Add(pageData.Dictionary["uid"].String, tmpString);
sb.Append(pageData.Dictionary["uid"].String + ",");
}
string allIDs = sb.ToString();
allIDs = allIDs.Remove(allIDs.Length - 1);
sb.Clear();
sb.Append("update user_info set username = case id ");
foreach (KeyValuePair<string, string[]> kvp in uInfo)
{
sb.Append(" when " + kvp.Key + " then '" + kvp.Value[0] + "' ");
}
sb.Append(" end, gender = case id ");
foreach (KeyValuePair<string, string[]> kvp in uInfo)
{
sb.Append(" when " + kvp.Key + " then '" + kvp.Value[1] + "' ");
}
sb.Append(" end where id in (" + allIDs + ")");
sql = sb.ToString();
conn.mysql_query(sql).Close();
sql = "update user_info set updatetime = ?updatetime where id in (" + allIDs + ")";
MySqlCommand cmd = new MySqlCommand(sql, conn.conn);
cmd.Parameters.AddWithValue("?updatetime", DateTime.Now);
cmd.ExecuteReader().Close();
cmd.Parameters.Clear();
}
}
private void update_user_info()
{
if (updatingUser)
return;
updatingUser = true;
DateTime start = DateTime.Now;
Console.WriteLine("啟動更新@" + DateTime.Now.ToString("HH:mm:ss") + "!!!");
if (accessTokenBox.Text.Length >= 0)
{
string sql = "select id from user_info where username = \"\" order by updatetime asc";
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
List<string> userID = new List<string>();
MySqlDataReader res = conn.mysql_query(sql);
while (res.Read())
{
userID.Add(res.GetString(0));
}
res.Close();
StringBuilder sb = new StringBuilder();
int index = 0;
foreach (string uid in userID)
{
if (index % 1500 == 0)
{
if (index != 0)
{
update_user_info_by_ids(sb, conn);
}
sb.Clear();
sb.Append(uid);
}
else
{
sb.Append("," + uid);
}
index++;
}
update_user_info_by_ids(sb, conn);
conn.closeMySqlConnection();
}
updatingUser = false;
Console.WriteLine("更新結束@" + DateTime.Now.ToString("HH:mm:ss") + "!!!(" + DateTime.Now.Subtract(start).TotalMinutes.ToString(".00Mins"));
}
private void deletePageID_Click(object sender, EventArgs e)
{
int deleteCount = 0;
string deleteID = "";
string deleteResultMessage = "";
foreach (ListViewItem lvi in pagesInfo.Items)
{
if (lvi.Checked)
{
deleteID += lvi.SubItems[0].Text + ",";
deleteResultMessage += lvi.SubItems[1].Text + "(" + lvi.SubItems[0].Text + ")\n";
deleteCount++;
}
}
if (deleteCount == 0)
{
MessageBox.Show("請先勾選要刪除的粉絲頁");
return;
}
deleteID = deleteID.Substring(0, deleteID.Length - 1);
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
string sql = "delete from pages_info where id in (" + deleteID + ")";
try
{
conn.mysql_query(sql).Close();
}
catch { }
sql = "delete from pages_logs where id in (" + deleteID + ")";
try
{
conn.mysql_query(sql).Close();
}
catch { }
sql = "delete from allinone where id in (" + deleteID + ")";
try
{
conn.mysql_query(sql).Close();
}
catch { }
sql = "delete from pages_posts_comments where page_id in (" + deleteID + ")";
try
{
conn.mysql_query(sql).Close();
}
catch { }
foreach (ListViewItem lvi in pagesInfo.Items)
{
if (lvi.Checked)
{
sql = "delete from pages_posts_comments_likes where comment_id like \"" + lvi.SubItems[0].Text + "_%\"";
conn.mysql_query(sql).Close();
sql = "delete from pages_posts_likes where post_id like \"" + lvi.SubItems[0].Text + "_%\"";
conn.mysql_query(sql).Close();
}
}
conn.closeMySqlConnection();
updatePageIDList();
checkCanUpdate();
MessageBox.Show(deleteResultMessage + "已被刪除");
}
private void searchButton_Click(object sender, EventArgs e)
{
if (keywordBox.Text.Length == 0)
{
MessageBox.Show("請輸入要查詢的關鍵詞");
return;
}
searchButton.Text = "資料查詢中…";
searchButton.Enabled = false;
pageName.Clear();
postMessage.Clear();
commentMessage.Clear();
allCommentMessage.Clear();
levelOneList.Items.Clear();
commentsList.Items.Clear();
DBConnection conn = new DBConnection(dbInfo);
conn.MySqlConnect();
string sql = "select id,name from pages_info";
MySqlDataReader res = conn.mysql_query(sql);
while (res.Read())
{
pageName.Add(res.GetString(0), res.GetString(1));
}
res.Close();
string keyword = keywordBox.Text.Replace("\"", "");
string allPostID = "";
keyword = keyword.Replace("%", "\\%");
keyword = keyword.Replace("_", "\\_");
sql = "select post_id, page_id, from_name, message,likes,comments,created_time, from_id from pages_posts where message like \"%" + keyword + "%\" and page_id in (select id from pages_info)";
//MySqlCommand thecmd = new MySqlCommand(sql, conn.conn);
//thecmd.Parameters.AddWithValue("?keyword", keywordBox.Text);
//res = thecmd.ExecuteReader();
res = conn.mysql_query(sql);
Console.WriteLine(sql);
/*
if (res != null)
{
while (res.Read())
{
string[] postData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetString(6) };
//page_id, from_name, message, likes, comments, created_time
postMessage.Add(res.GetString(0), postData);
allPostID += "\"" + res.GetString(0) + "\",";
}
}
*/
while (res.Read())
{
string[] postData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetDateTime(6).ToString("yyyy-MM-dd HH:mm:ss"), res.GetString(7) };
//page_id, from_name, message, likes, comments, created_time
postMessage.Add(res.GetString(0), postData);
allPostID += "\"" + res.GetString(0) + "\",";
}
res.Close();
if (allPostID.Length > 0)
{
allPostID = allPostID.Substring(0, allPostID.Length - 1);
sql = "select comment_id, post_id, page_id, from_name, message, likes, created_time,from_id from pages_posts_comments where post_id in (" + allPostID + ")";
res = conn.mysql_query(sql);
if (res != null)
{
while (res.Read())
{
string[] commentData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetString(6), res.GetString(7) };
allCommentMessage.Add(res.GetString(0), commentData);
}
res.Close();
}
}
//thecmd.Parameters.Clear();
sql = "select comment_id, post_id, page_id, from_name, message, likes, created_time, from_id from pages_posts_comments where message like \"%" + keyword + "%\" and page_id in (select id from pages_info)";
//MySqlCommand thecmd = new MySqlCommand(sql, conn.conn);
//thecmd.Parameters.AddWithValue("?keyword", keywordBox.Text);
//res = thecmd.ExecuteReader();
res = conn.mysql_query(sql);
if (res != null)
{
while (res.Read())
{
string[] commentData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetString(6), res.GetString(7) };
//post_id, page_id, from_name, message, likes, created_time
commentMessage.Add(res.GetString(0), commentData);
}
res.Close();
}
//thecmd.Parameters.Clear();
display_Message();
searchButton.Enabled = true;
searchButton.Text = "查詢";
}