private void get_xiaoqu(string xiaoquid)
{
xiaoquExist = false;
MysqlTOOLS mt = new MysqlTOOLS();
string sql = "SELECT area_name , area_city , area_province,xiaoquid,xiaoqu_name from area,xiaoqu WHERE xiaoqu_area_id = areaID\n" +
$"and xiaoquID = '{xiaoquid}'\n";
var rdr = mt.sqlToReader(sql);
while (rdr.Read())
{
xiaoquExist = true;
var tmpitem = new xiaoqu_item();
tmpitem.area.name = rdr[0].ToString();
tmpitem.area.city = rdr[1].ToString();
tmpitem.area.province = rdr[2].ToString();
tmpitem.id = rdr[3].ToString();
tmpitem.name = rdr[4].ToString();
theXiaoqu = tmpitem;
}
TextBox2.Text = theXiaoqu.name;
TextBox3.Text = theXiaoqu.area.province;
TextBox4.Text = theXiaoqu.area.city;
TextBox5.Text = theXiaoqu.area.name;
rdr.Close();
mt.closeconnection();
}
private void addxiaoqu()
{
MysqlTOOLS mt = new MysqlTOOLS();
string sql = $"INSERT INTO xiaoqu (xiaoqu_name , xiaoqu_area_id , xiaoqu_management_id) VALUES('{theXiaoqu.name}','{areaID}','1')";
mt.sqlToExec(sql);
}
private void addarea(area are)
{
MysqlTOOLS mt = new MysqlTOOLS();
string sql = $"INSERT into area (area_name,area_city,area_province) VALUES('{are.name}','{are.city}','{are.province}')";
//Button2.Text = sql;
mt.sqlToExec(sql);
}
private void getGrapicsettings_query()
{
xiaoqu_id = TextBox1.Text;
//获取用户选择的日期数量/显示方式
chartdots = Convert.ToInt32(RadioButtonList1.SelectedValue);
chartstyle = RadioButtonList2.SelectedValue;
//获取需要的所有日期数据
for (int i = 0; i <= chartdots; i++)
{
string a = DateTime.Now.AddDays(-chartdots + i).ToShortDateString();
bottomTextlist.Add(a);
}
//获取数据库内容
var mt = new MysqlTOOLS();
string sql = "";
if (xiaoqu_id.Equals(""))
{
sql = "SELECT citizen_id,xiaoqu_id , action_time from in_and_out ";
}
else
{
sql = $"SELECT citizen_id,xiaoqu_id , action_time from in_and_out WHERE xiaoqu_id = '{xiaoqu_id}'";
}
var rdr = mt.sqlToReader(sql);
while (rdr.Read())
{
var item = new chart_time_item();
item.citizenid = rdr[0].ToString();
item.xiaoqu_id = rdr[1].ToString();
item.action_time = rdr[2].ToString();
// 通过substring得到较短的时间格式
item.action_time_short = item.action_time.Substring(0, 10);
fulldatalist.Add(item);
}
//从数据库所有内容中获取每个对应日期的出入次数
foreach (var item in bottomTextlist)
{
int counter = 0;
foreach (var t in fulldatalist)
{
if (t.action_time_short == item)
{
counter++;
}
}
datalist.Add(counter.ToString());
}
Panel1.Visible = true;
}
// 新建sql链接查看是否为worker
public bool getIsworker(string citizenid)
{
var mt = new MysqlTOOLS();
string sql =
"SELECT * FROM `user`,worker\n" +
"WHERE `user`.user_citizenID = worker.worker_citizenid\n" +
$"and worker_citizenid = '{citizenid}'\n";
bool ans = mt.sqlToBoolhave(sql);
mt.closeconnection();
return(ans);
}
private void saveXiaoqu()
{
theXiaoqu.name = TextBox2.Text;
theXiaoqu.area.province = TextBox3.Text;
theXiaoqu.area.city = TextBox4.Text;
theXiaoqu.area.name = TextBox5.Text;
theXiaoqu.id = TextBox1.Text;
MysqlTOOLS mt = new MysqlTOOLS();
string sql = $"UPDATE xiaoqu SET xiaoqu_name = '{theXiaoqu.name}' WHERE xiaoquID = '{theXiaoqu.id}'";
//Button2.Text = sql;
mt.sqlToExec(sql);
}
public string get(string id)
{
MysqlTOOLS mt = new MysqlTOOLS();
string sql = "SELECT area_name ,area_city ,area_province ,xiaoqu_name,`in`,`out`,action_time\n" +
"FROM area,in_and_out,xiaoqu\n" +
"WHERE xiaoqu.xiaoqu_area_id = areaID\n" +
"and xiaoqu_id = xiaoquID\n" +
$"and in_and_out.citizen_id = '{id}'";
var rdr = mt.sqlToReader(sql);
while (rdr.Read())
{
var hisinfo = new history_item();
hisinfo.area.name = rdr[0].ToString();
hisinfo.area.city = rdr[1].ToString();
hisinfo.area.province = rdr[2].ToString();
hisinfo.xiaoqu = rdr[3].ToString();
hisinfo.in_ = Convert.ToBoolean(rdr[4]);
hisinfo.out_ = Convert.ToBoolean(rdr[5]);
hisinfo.time = rdr[6].ToString();
if (hisinfo.in_)
{
hisinfo.fangxiang = "进入";
}
else
{
hisinfo.fangxiang = "离开";
}
hislist.AddLast(hisinfo);
}
rdr.Close();
mt.closeconnection();
string contentstr = "";
foreach (var item in hislist)
{
string addstr =
" <tr>\n" +
$" <td>{item.area.ToString()}</td>\n" +
$" <td>{item.xiaoqu}</td>\n" +
$" <td>{item.fangxiang}</td>\n" +
$" <td>{item.time}</td>\n" +
" </tr>";
contentstr += addstr;
}
return(contentstr);
}
public string get(string xiaoqu_id,
string xiaoqu_name,
string province,
string city,
string qu)
{
MysqlTOOLS mt = new MysqlTOOLS();
string sql =
"SELECT area_name , area_city , area_province,xiaoquid,xiaoqu_name from area,xiaoqu WHERE xiaoqu_area_id = areaID\n" +
$"and xiaoquID like '{xiaoqu_id}%'\n" +
$"and xiaoqu_name like '%{xiaoqu_name}%'\n" +
$"and area_name like '%{qu}%'\n" +
$"and area_city like '%{city}%'\n" +
$"and area_province like '%{province}%'";
var rdr = mt.sqlToReader(sql);
while (rdr.Read())
{
// tmpitem,可是专门设计的类,也可以是有足够信息的实体类.
// 通过reader 获取信息
var tmpitem = new xiaoqu_item();
tmpitem.area.name = rdr[0].ToString();
tmpitem.area.city = rdr[1].ToString();
tmpitem.area.province = rdr[2].ToString();
tmpitem.id = rdr[3].ToString();
tmpitem.name = rdr[4].ToString();
xxxlist.AddLast(tmpitem);
}
rdr.Close();
mt.closeconnection();
string contentstr = "";
foreach (var item in xxxlist)
{
// 每一行表格的html代码
string addstr =
" <tr>\n" +
$" <td>{item.id}</td>\n" +
$" <td>{item.name}</td>\n" +
$" <td>{item.area.ToString()}</td>\n" +
" </tr>";
contentstr += addstr;
}
return(contentstr);
}
private string getname(string id)
{
string ans = "";
MysqlTOOLS mt = new MysqlTOOLS();
string sql1 = $"SELECT citizen_name from citizen WHERE citizenID = '{id}'";
var rdr = mt.sqlToReader(sql1);
while (rdr.Read())
{
ans = rdr[0].ToString();
}
rdr.Close();
mt.closeconnection();
return(ans);
}
public string xiaoquExist_andgetname()
{
string ans = "";
var mt = new MysqlTOOLS();
string sql = $"SELECT xiaoqu_name FROM xiaoqu WHERE xiaoquid = '{xiaoqu_id}'";
var rdr = mt.sqlToReader(sql);
try
{
rdr.Read();
ans = rdr[0].ToString();
}catch
{
}
rdr.Close();
mt.closeconnection();
return(ans);
}
private bool getareaExist(area are)//确定area是否存在,返回真.假 如果存在将id放入 areaID 变量中
{
areaID = "";
bool ans = false;
MysqlTOOLS mt = new MysqlTOOLS();
string sql = $"SELECT areaID FROM area WHERE area_name = '{are.name}' and area_city = '{are.city}' and area_province = '{are.province}'";
var rdr = mt.sqlToReader(sql);
while (rdr.Read())
{
ans = true;
areaID = rdr[0].ToString();
}
rdr.Close();
mt.closeconnection();
return(ans);
}
private string getIsworker(string id)
{
var mt = new MysqlTOOLS();
string sql =
"SELECT * FROM `user`,worker\n" +
"WHERE `user`.user_citizenID = worker.worker_citizenid\n" +
$"and worker_citizenid = '{id}'\n";
bool ans = mt.sqlToBoolhave(sql);
mt.closeconnection();
if (ans)
{
return("是");
}
else
{
return("不是");
}
}
protected void Button2_Click(object sender, EventArgs e)//升级/降级的按钮
{
id = TextBox1.Text;
if (Label_isworker.Text.Equals("是"))
{
//降级
MysqlTOOLS mt = new MysqlTOOLS();
string sql = $"DELETE FROM worker WHERE worker.worker_citizenid = '{id}'";
mt.sqlToExec(sql);
mt.closeconnection();
Label_success.Text = $"成功降级{Label_name.Text }为普通人员";
}
else
{
//升级
MysqlTOOLS mt = new MysqlTOOLS();
string sql = $"INSERT INTO worker VALUES('{id}', '1','default')";
mt.sqlToExec(sql);
mt.closeconnection();
Label_success.Text = $"成功提升{Label_name.Text }为管理员";
}
Panel1.Visible = true;
Panel3.Visible = true;
Label_name.Text = getname(id);
Label_isworker.Text = getIsworker(id);
// 判断提供升级还是降级的功能
if (Label_isworker.Text.Equals("是"))
{
Button2.Text = "降级为普通人员";
}
else
{
Button2.Text = "提升为管理人员";
}
}
//返回html table格式的内容字符串
public string get(string name, string id)
{
MysqlTOOLS mt = new MysqlTOOLS();
string sql1 =
"SELECT citizen_name,citizen_gender, \n" +
"\tuser_phone,user_avtarlink,user_email,user_note,\n" +
"\t xiaoqu_name,buliding,unit,room,area_name,area_city,area_province,citizen.citizenID\n" +
"FROM `user` , citizen , link ,xiaoqu,area\n" +
"WHERE link.citizenid = user_citizenID \n" +
"and xiaoqu_area_id=areaID\n" +
"AND user_citizenID = citizen.citizenID AND link.xiaoquid = xiaoqu.xiaoquID " +
$"and citizen.citizenID like '{id}%'\n" +
$"and citizen_name like '%{name}%'";
var rdr = mt.sqlToReader(sql1);
while (rdr.Read())
{
var dumdum = new citizen();
dumdum.name = rdr[0].ToString();
dumdum.gender = rdr[1].ToString();
dumdum.phone = rdr[2].ToString();
dumdum.avtarlink = rdr[3].ToString();
dumdum.email = rdr[4].ToString();
dumdum.note = rdr[5].ToString();
dumdum.livIn.xiaoquname = rdr[6].ToString();
dumdum.livIn.building = rdr[7].ToString();
dumdum.livIn.unit = rdr[8].ToString();
dumdum.livIn.room = rdr[9].ToString();
dumdum.livarea.name = rdr[10].ToString();
dumdum.livarea.city = rdr[11].ToString();
dumdum.livarea.province = rdr[12].ToString();
dumdum.citizenid = rdr[13].ToString();
// 下面信息的计算和获取需要上述信息先存在
dumdum.age = getAgeById(dumdum.citizenid);
if (do_getisworker)
{
dumdum.isworker = getIsworker(dumdum.citizenid);
}
ctznlist.AddLast(dumdum);
}
rdr.Close();
mt.closeconnection();
string contentstr = "";
foreach (var item in ctznlist)
{
string addstr =
" <tr data-toggle=\"collapse\" data-target=\"#_" + item.citizenid + "\">\n" +
$" <td>{item.name}</td>\n" +
$" <td>{item.gender}</td>\n" +
$" <td>{item.citizenid}</td>\n" +
" </tr>\n" +
" <tr>\n" +
" <td class=\"table-active\" colspan=\"3\" style=\"padding:0; border: none;\">\n" +
$" <div id=\"_{item.citizenid}\" class=\"collapse\">\n" +
" <div class=\"row\">\n" +
" <div class=\"col-3\">\n" +
" <img style=\"border: 5px solid whitesmoke;\n" +
" margin-left: 9px;\n" +
" height: 185px;\n" +
$" box-shadow: 0 0 3px #000000ab;\" src=\"{item.avtarlink}\" alt=\"照片\">\n" +
" </div>\n" +
" <div class=\"col-9\">\n" +
" <table class=\" bg-light ml-auto\">\n" +
" <tr>\n" +
$" <td>居住区域</td>\n" +
$" <td>{item.livarea.province}省 {item.livarea.city}市 {item.livarea.name}</td>\n" +
$" <td>年龄</td>\n" +
$" <td>{item.age}</td>\n" +
" </tr>\n" +
" <tr>\n" +
$" <td>具体住址</td>\n" +
$" <td>{item.livIn.xiaoquname} {item.livIn.building}号楼 {item.livIn.room}室 </td>\n" +
$" <td>手机</td>\n" +
$" <td>{item.phone}</td>\n" +
" </tr>\n" +
" <tr>\n" +
" <td>工作人员</td>\n" +
$" <td>{item.isworker}</td>\n" +
" <td rowspan=\"2\">备注</td>\n" +
$" <td rowspan=\"2\">{item.note}</td>\n" +
" </tr>\n" +
" <tr>\n" +
" <td>email</td>\n" +
$" <td>{item.email}</td>\n" +
" </tr>\n" +
" </table>\n" +
" </div>\n" +
" </div>\n" +
" </div>\n" +
" </td>\n" +
" </tr>";
contentstr += addstr;
}
return(contentstr);
}