/// <summary>
/// 获得EntityList类型数据
/// </summary>
/// <typeparam name="T">数据模型</typeparam>
/// <param name="strTableName">表名</param>
/// <param name="strWhere">过滤条件</param>
/// <returns>成功返回ListT,失败返回null</returns>
public static List <T> GetEntityList <T>(string strTableName, string strWhere) where T : class, new()
{
try
{
List <T> listT = new List <T>();
SQLiteHelper sqlHelper = new SQLiteHelper();
sqlHelper.SQLiteConnectionString(strSQLiteConn);
sqlHelper.Open();
if (string.IsNullOrEmpty(strWhere))
{
listT = DataProcessing.ConvertToList <T>(sqlHelper.GetDataTable(string.Format("SELECT * FROM {0}", strTableName)));
}
else
{
listT = DataProcessing.ConvertToList <T>(sqlHelper.GetDataTable(string.Format("SELECT * FROM {0} WHERE {1}", strTableName, strWhere)));
}
sqlHelper.Close();
return(listT);
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
SQLiteHelper sh = new SQLiteHelper();
string sql = "select b.c_name_chn as c_name_chn, c.c_addr_id,c.x_coord as x_coord ,c.y_coord as y_coord from biog_addr_data A,biog_main B,ADDR_CODES c";
sql += " where A.c_personid = B.c_personid AND c.c_addr_id = a.c_addr_id and ";
sql += " (b.c_deathyear>=907 and b.c_birthyear<=1200) and c.x_coord is not null and c.y_coord is not null";
//sql += "c.x_coord is not null and c.y_coord is not null";
DataSet ds = new DataSet();
try
{
sh.RunSQL(sql, ref ds);
dt = ds.Tables[0];
// personnumber = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
// jsstirng += string.Format(@"{name:{0}'', geoCoord:[{1}, {2}]},", dr["c_name_chn"].ToString(), dr["x_coord"].ToString(), dr["y_coord"].ToString());
//jsstirng += string.Format(@"{{name:'{0}',geoCoord:[{1}, {2}]}},", dr["c_name_chn"].ToString(), dr["x_coord"].ToString(), dr["y_coord"].ToString());
if (dr["c_name_chn"].ToString() == "呂祖謙")
{
jsli += string.Format(@"{{name:'{0}',geoCoord:[{1}, {2}]}},", dr["c_name_chn"].ToString(), dr["x_coord"].ToString(), dr["y_coord"].ToString());
}
}
}
catch (Exception ex)
{
Response.Write("数据库出错");
}
finally
{
sh.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
SQLiteHelper sh = new SQLiteHelper();
string sql = "SELECT c_dy,c_dynasty_chn FROM DYNASTIES ORDER BY c_sort;";
DataTable dt = new DataTable();
DataSet ds = new DataSet();
try
{
sh.RunSQL(sql, ref ds);
if (ds.Tables[0] != null)
{
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
DdlDynasties.DataTextField = "c_dynasty_chn";
DdlDynasties.DataValueField = "c_dy";
DdlDynasties.DataSource = dt;
DdlDynasties.DataBind();
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
sh.Close();
}
}
protected void DdlDynasties_SelectedIndexChanged(object sender, EventArgs e)
{
SQLiteHelper sQ = new SQLiteHelper();
string sqlEra = "SELECT c_nianhao_id, c_nianhao_chn, c_dy FROM NIAN_HAO " +
"WHERE c_dy = " + DdlDynasties.SelectedValue + ";";
DataTable dt = new DataTable();
DataSet ds = new DataSet();
try
{
sQ.RunSQL(sqlEra, ref ds);
if (ds.Tables[0] != null)
{
dt = ds.Tables[0];
if (dt.Rows.Count >= 0)
{
DdlEraName.DataTextField = "c_nianhao_chn";
DdlEraName.DataValueField = "c_nianhao_id";
DdlEraName.DataSource = dt;
DdlEraName.DataBind();
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
sQ.Close();
}
}
private static void DisplayMenu()
{
Console.Clear();
Console.WriteLine("\n***************************************");
Console.WriteLine("\t 1. Create Database");
Console.WriteLine("\t 2. Init spatial metadata");
Console.WriteLine("\t 3. Create spatial table");
Console.WriteLine("\t 4. Show Table Info");
Console.WriteLine("\t 5. Query");
Console.WriteLine("\t 6. Create polylines");
Console.WriteLine("\t 7. test intersect");
Console.WriteLine("\t 0.Exit");
Console.WriteLine("***************************************");
Console.Write(">");
string s = Console.ReadLine();
switch (s)
{
case "1":
CreateDatabase();
break;
case "2":
InitSpatialMeta();
break;
case "3":
CreateSpatialTable();
break;
case "4":
ShowTableInfo();
break;
case "5":
Query();
break;
case "6":
CreatePolylines();
break;
case "7":
TestIntersect();
break;
case "0":
sh.Close();
System.Environment.Exit(0);
break;
}
}
private void button11_Click(object sender, EventArgs e)
{
Enabled = false;
var sqlhelper = new SQLiteHelper();
var teacherList = sqlhelper.GetTeacherInfoList();
var allMajorList = sqlhelper.GetAllMajor();
sqlhelper.Close();
Form_IncludeClass frm = new Form_IncludeClass(teacherList, allMajorList);
frm.ShowDialog();
Enabled = true;
}
private void LoadTeacherProcess()
{
try
{
OpenFileDialog openFile = new OpenFileDialog();
bool NeedReturn = false;
Invoke((EventHandler) delegate
{
Enabled = false;
MessageBox.Show(this, "*导入数据仅支持(.xls)格式的数据文件，如不是请自行更改保存格式\n\n*数据表格式如下且需包含表头：\n姓名|系别|包含班级\n*姓名：参赛教师姓名\n*系别：参赛教师所属系\n*包含班级：参赛教师的所有班级，格式为\n[专业][.][班级][,][专业][.][班级][,]……………………[专业][.][班级]\n如：软件工程.2016级01班,软件工程.2016级02班\n\".\"及\",\"均为英文状态下标点，[专业]及[班级]需与学生数据表中的对应字段相匹配\n\n*不能重复导入相同的数据表\n\n*由于不遵守此规定导致的一切错误请自行承担", "重要提示");
openFile.Title = "请选择目标Excel表";
openFile.Filter = "Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel)
{
NeedReturn = true;
}
});
if (NeedReturn)
{
return;
}
var dt = ExcelHelper.GetDataFromExcelByConn(openFile.FileName, true);
SQLiteHelper sqlLiteHelper = new SQLiteHelper();
if (sqlLiteHelper.InsertNewTeacher(dt))
{
Invoke((EventHandler) delegate
{
MessageBox.Show(this, "数据已经成功导入", "导入成功");
});
}
sqlLiteHelper.Close();
}
catch (Exception e)
{
Invoke((EventHandler) delegate
{
MessageBox.Show(this, e.Message, "导入失败");
});
}
finally
{
Invoke((EventHandler) delegate
{
Text = "设置";
Enabled = true;
});
}
}
public static void CreateUser()
{
string path = Path.Combine(Application.StartupPath, "Data", "alo_users.xls");
//DataTable dt = util.Excel_To_DataTable(path,0);
DataTable dt = util.GetRequestsDataFromExcel(path);
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
sh.BeginTransaction();
foreach (DataRow dr in dt.Rows)
{
string username = dr["HoTen"].ToString().Replace("'", "");
username = fullname2username(username).ToLower();
string mail = username + "@alobhxh.com";
string birthDay = dr["NgaySinh"].ToString();
//
//
if (Convert.ToInt32(sh.ExecuteScalar("SELECT COUNT(*) FROM USERS WHERE USERNAME = '******'")) > 0)
{
continue;
}
//
userInfo user = new userInfo();
user.Username = username.Replace("'", "");
user.Birthday = birthDay;
user.Fullname = dr["HoTen"].ToString().Replace("'", "");
user.Address = dr["DiaChi"].ToString().Replace("'", "");
user.Sex = dr["GioiTinh"].ToString().Replace("'", "");
//
sh.Insert("USERS", user.ToDictionary());
//
}
sh.Commit();
sh.Close();
}
public static string GetRandomUID(string siteId = "")
{
if (siteId == "bhxhdanang.gov.vn")
{
return(Program.API_MOD_ID_DNG);
}
else if (siteId == "baohiemxahoi.gov.vn")
{
return("1012");
}
string randomUid = "";
if (lstUser == null)
{
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
DataTable dtUser = sh.Select("SELECT uid FROM USERS WHERE uid is not null and uid <> ''");
if (dtUser.Rows.Count > 0)
{
lstUser = new List <string>();
foreach (DataRow dr in dtUser.Rows)
{
lstUser.Add(dr[0].ToString());
}
//
}
//
sh.Close();
}
//
Random r = new Random();
int index = r.Next(lstUser.Count - 1);
randomUid = lstUser[index];
return(randomUid);
}
public void Close()
{
dbHelper.Close();
}
public void Post2Site(List <postInfo> posts)
{
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
sh.BeginTransaction();
//Luu DB local - SQLLITE, de check trung
string strUrrl = "https://bds5s.com/posts";
string apiKey = "a82841644696ebb6381bd74b41c81fb4d5bddc0b91de59a932da5a9fb87ce9be";
string user = "", catid = "", category = "";
foreach (postInfo post in posts)
{
if (Convert.ToInt32(sh.ExecuteScalar("SELECT COUNT(*) FROM POSTS WHERE title='" + post.Title + "'")) > 0)
{
continue;
}
//1. Insert local DB
sh.Insert("POSTS", post.ToDictionary());
//2. Post site
string purl = util.RemoveVietnamese(post.Province);
if (purl.Equals("ha-noi"))
{
user = "******";
catid = util.getQuanHuyen(util.RemoveVietnamese(post.District));
if (catid.Equals(""))
{
category = "5";
}
else
{
category = catid;
}
//
}
else if (purl.Equals("ho-chi-minh"))
{
user = "******";
catid = util.getQuanHuyen(util.RemoveVietnamese(post.District));
if (catid.Equals(""))
{
category = "35";
}
else
{
category = catid;
}
}
else if (purl.Equals("da-nang"))
{
user = "******";
catid = util.getQuanHuyen(util.RemoveVietnamese(post.District));
if (catid.Equals(""))
{
category = "61";
}
else
{
category = catid;
}
}
if (!category.Equals(""))
{
string api_username = user;
string strData = "api_key=" + apiKey + "&api_username="******"&title=" + post.Title + "&category=" + category + "&raw=" + post.Content + "";
util.HTTP_POST(strUrrl, strData);
}
}
sh.Commit();
sh.Close();
}
public void Post2Site_Alo(List <postInfo> posts, string categoryId)
{
//return;
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
sh.BeginTransaction();
//Luu DB local - SQLLITE, de check trung
//string root = "http://localhost:4567/api/v2";
string root = "https://alobhxh.com/api/v2";
string postUrl = "/topics";
string replyUrl = "";
//string apiKey = "Bearer 94cdf3b2-7b9c-4c67-9b98-e0e20682a5fb"; //Master
string apiKey = String.Format("Bearer {0}", Program.API_MASTER_KEY);// 15baf25c-5d82-47c8-a660-71b4abb7b675"; //Master
//string apiKey = "Bearer 02ca7aea-28e5-4f04-b6c6-02861c1c6d36";
//string apiKey = "Bearer f440c24f-a7b1-401e-a788-31c1dc1df19c";
string cid = categoryId, title = "", content = "";
string url = root + postUrl;
foreach (postInfo post in posts)
{
string sqlExist = "SELECT COUNT(*) FROM POSTS WHERE siteId = '" + post.SiteId + "' AND postId='" + post.PostId + "'";
if (post.PostId == "0" || post.PostId == "")
{
sqlExist = "SELECT COUNT(*) FROM POSTS WHERE siteId = '" + post.SiteId + "' AND title='" + post.Title + "'";
}
if (Convert.ToInt32(sh.ExecuteScalar(sqlExist)) > 0)
{
continue;
}
//1. Insert local DB
sh.Insert("POSTS", post.ToDictionary());
if (post.SiteId == "bhxhhn.com.vn" && Program.lstExistPost != null)
{
string checkExist = String.Format("{0}|{1}", post.CategoryId, post.Title);
if (Program.lstExistPost.Contains(checkExist))
{
continue;
}
}
//2. Post site
//string strData = "api_key=" + apiKey + "&api_username="******"&title=" + post.Title + "&category=" + category + "&raw=" + post.Content + "";
try
{
var converter = new Converter();
JObject o = new JObject();
o["cid"] = categoryId;
o["title"] = post.Title;
//
string contentMD = post.Content;
contentMD = converter.Convert(contentMD).Trim();
contentMD = contentMD.Replace("\\r\\n\\", "");
//
string contentRepMD = post.ContentReply;
if (!String.IsNullOrEmpty(contentRepMD))
{
contentRepMD = converter.Convert(contentRepMD).Trim();
contentRepMD = contentRepMD.Replace("\\r\\n\\", "");
}
//Nếu bài dài quá, thì cắt làm 2
if (contentMD.Length > 32000 && String.IsNullOrEmpty(post.ContentReply))
{
string initContent = contentMD;
contentMD = initContent.Substring(0, 32000);
contentRepMD = initContent.Substring(32001, 32000);
}
o["content"] = contentMD;
string userId = userhelper.GetRandomUID(post.SiteId);
o["_uid"] = userId;
//var jsonContent = String.Format("{'cid': '{0}','title': '{1}', 'content': '{2}'}", cid, post.Title, post.Content);
string jsonContent = o.ToString();
JObject obj = util.HTTP_POST_JSON(url, apiKey, jsonContent);
if (obj != null && obj["code"].ToString() == "ok" && !String.IsNullOrEmpty(contentRepMD))
{
string tId = obj["payload"]["topicData"]["tid"].ToString();
if (!String.IsNullOrEmpty(tId))
{
string url2 = url + "/" + tId;
o = new JObject();
//contentMD = post.ContentReply;
//
//contentMD = converter.Convert(post.ContentReply);
o["content"] = contentRepMD.Trim();
o["_uid"] = userhelper.GetRandomUIDReply(post.SiteId, userId);
jsonContent = o.ToString();
//
if (!String.IsNullOrEmpty(contentRepMD))
{
string modAPI = apiKey;
obj = util.HTTP_POST_JSON(url2, modAPI, jsonContent);
if (obj["code"].ToString() == "ok")
{
Console.WriteLine("OK: " + post.Title);
}
}
}
}
}
catch (Exception ex)
{
string s = ex.ToString();
}
}
sh.Commit();
sh.Close();
}
private void LoadExcelProcess()
{
try
{
OpenFileDialog openFile = new OpenFileDialog();
bool NeedReturn = false;
Invoke((EventHandler) delegate
{
Enabled = false;
MessageBox.Show(this, "\n\n数据表格式如下且需包含表头：\n学号|姓名|性别|班级|专业|政治面貌|民族|担任职务|家庭住址|宿舍|宿舍成员|家庭经济状况|奖惩情况|学习状况|兴趣爱好|职业倾向\n\n不能重复导入相同的数据表\n\n", "重要提示");
openFile.Title = "请选择目标Excel表";
openFile.Filter = "Excel(*.xls;*.xlsx)|*.xls;*.xlsx";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = true;
if (openFile.ShowDialog() == DialogResult.Cancel)
{
NeedReturn = true;
}
});
if (NeedReturn)
{
return;
}
Invoke((EventHandler) delegate
{
Text = "正在导入中，请耐心等候";
});
//var dt = ExcelHelper.GetDataFromExcelByConn(openFile.FileName, true);
string input_success = string.Empty;
string input_error = string.Empty;
int input_success_total = 0;
SQLiteHelper sqlLiteHelper = new SQLiteHelper();
foreach (var fs in openFile.FileNames)
{
var dt = ExcelHelper.GetDataFromExcelByConn(fs, true);
int temp_input_success_total = 0;
if (sqlLiteHelper.InsertNewStudent(dt, input_success_total, ref temp_input_success_total, this))
{
input_success = input_success + fs + "\n";
input_success_total += temp_input_success_total;
}
else
{
input_error = input_error + fs + "\n";
//Invoke((EventHandler)delegate
//{
// MessageBox.Show(this, "未导入任何数据\n\n\"" + fs + "\"\n\n请检查格式是否有误", "导入失败");
//});
}
}
sqlLiteHelper.Close();
Invoke((EventHandler) delegate
{
MessageBox.Show(this, string.Format("{0}\n\n{1}\n{2}",
(string.Format("成功导入 {0} 名学生数据", input_success_total)),
(string.IsNullOrEmpty(input_success) ? "" : string.Format("以下文件的数据均已成功导入\n\n{0}", input_success)),
(string.IsNullOrEmpty(input_error) ? "" : string.Format("以下文件的数据导入失败，请检查格式是否有误\n\n{0}", input_error))
), "导入完成");
if (input_success_total > 0)
{
MessageBox.Show(this, "\n\n请确保学生照片位于(程序运行目录/Data/IMG/[对应专业]/[对应年级]/[对应姓名.jpg])中\n\n如不存在，学生照片将不能正常显示", "提示");
}
});
}
catch (Exception e)
{
Invoke((EventHandler) delegate
{
MessageBox.Show(this, e.Message, "导入失败");
});
}
finally
{
Invoke((EventHandler) delegate
{
Invoke((EventHandler) delegate
{
Text = "设置";
});
Enabled = true;
});
}
}