public void DAlloc()
{
prepare();
StudentToDB();
string sx = slist[10].sid;
List <Student> boy = new List <Student>();
List <Student> girl = new List <Student>();
for (int i = 0; i < slist.Count; i++)
{
if (slist[i].ssex.Equals("男"))
{
boy.Add(slist[i]);
}
else
{
girl.Add(slist[i]);
}
}
List <Room> boyroom = geteRoom("男生宿舍");
List <Room> girlroom = geteRoom("女生宿舍");
//安排男生入住
string s = boyroom[11].brsex;
MYSql mysql = new MYSql();
int stu = 0;
for (int i = 0; i < boyroom.Count; i++)
{
int maxsize = getMaxsize(boyroom[i].buildingnum, boyroom[i].roomnum);
int asize = getacsize(boyroom[i].buildingnum, boyroom[i].roomnum);
for (; stu < boy.Count; stu++)
{
if (asize == maxsize)
{
break;
}
string year = "20" + boy[stu].sid.Substring(0, 2);
string intoDS = "insert into DS(buildingnum,roomnum,sid,livetime)values('" + boyroom[i].buildingnum + "','" + boyroom[i].roomnum + "','" + boy[stu].sid + "','" + year + "')";
mysql.ExecuteQuery(intoDS);
asize++;
}
}
stu = 0;
for (int i = 0; i < girlroom.Count; i++)
{
int asize = getacsize(girlroom[i].buildingnum, girlroom[i].roomnum);
int maxsize = getMaxsize(girlroom[i].buildingnum, girlroom[i].roomnum);
for (; stu < girl.Count; stu++)
{
if (asize == maxsize)
{
break;
}
string year = "20" + girl[stu].sid.Substring(0, 2);
string intoDS = "insert into DS(buildingnum,roomnum,sid,livetime)values('" + girlroom[i].buildingnum + "','" + girlroom[i].roomnum + "','" + girl[stu].sid + "','" + year + "')";
mysql.ExecuteQuery(intoDS);
asize++;
}
}
}
DataTable dta = new DataTable();//赋值dt表中的数据，以便进行查询
public usreDor()
{
InitializeComponent();
dt = mysql.ExecuteQuery(s);
string s1 = "select buildingnum,roomnum,brsex,size from broom where buildingnum+roomnum not in(select buildingnum+roomnum from DS) and buildingnum='" + Source.build + "'";
dt1 = mysql.ExecuteQuery(s1);
for (int i = 0; i < dt1.Rows.Count; i++)
{
DataRow dRow = dt.NewRow();
dRow[0] = dt1.Rows[i][0];
dRow[1] = dt1.Rows[i][1];
dRow[2] = dt1.Rows[i][2];
dRow[3] = dt1.Rows[i][3];
dRow[4] = 0;
dt.Rows.Add(dRow);
}
dataGrid.ItemsSource = dt.DefaultView;
dta.Columns.Add("栋号");
dta.Columns.Add("寝室号");
dta.Columns.Add("女寝or男寝");
dta.Columns.Add("寝室大小");
dta.Columns.Add("实际人数");
comboBox.ItemsSource = mysql.ExecuteQuery("select roomnum from broom where buildingnum='" + Source.build + "'").DefaultView;
comboBox.DisplayMemberPath = "roomnum";
}
private async void button_Click(object sender, RoutedEventArgs e)//添加
{
string buildingnum;
string wnum;
GetInfo g = new GetInfo();
buildingnum = comboBox.SelectedValue.ToString().Replace("System.Windows.Controls.ComboBoxItem: ", "");
int num = g.getMnum(buildingnum) + 1;
wnum = "00" + buildingnum + "00" + num.ToString();
string wname = textBox1.Text;
string pwd = "111111";
labelwnum.Content = wnum;
string s = "insert into worker(wnum,wname,bnum,pwd)values('" + wnum + "','" + wname + "','" + buildingnum + "','" + pwd + "')";
if (wname == "" || buildingnum == "")
{
await this.ShowMessageAsync("提示", "请输入完整的信息");
}
MYSql mYSql = new MYSql();
try
{
mYSql.ExecuteQuery(s);
await this.ShowMessageAsync("提示", "插入成功,新的工号是" + wnum);
man.dataGrid.ItemsSource = mYSql.ExecuteQuery("select wnum as '工号',wname as '姓名',bnum as '管理栋号' from worker").DefaultView;
this.Close();
}
catch (Exception ex)
{
await this.ShowMessageAsync("提示", "未插入成功，已有此工号" + ex);
}
}
public MainCome()
{
InitializeComponent();
string s = "select ctime as '时间',name as '姓名',thing as '原因' from Come where buildingnum='" + Source.build + "'";
dt = mysql.ExecuteQuery(s);
dataGrid.ItemsSource = dt.DefaultView;
}
public Exchange()
{
InitializeComponent();
comboBox1.ItemsSource = my.ExecuteQuery("select distinct buildingnum from broom").DefaultView;
comboBox1.DisplayMemberPath = "buildingnum";
comboBox2.ItemsSource = my.ExecuteQuery("select distinct buildingnum from broom").DefaultView;
comboBox2.DisplayMemberPath = "buildingnum";
}
private void radioButton_Checked(object sender, RoutedEventArgs e)//未处理
{
selected = 1;
string s = "select recordid as '单号',content as '报修内容', redate as '报修时间' from record where buildingnum = '" + buildingnum + "' and roomnum = '" + roomnum + "' and status = '" + 1 + "'";
dt = mysql.ExecuteQuery(s);
dataGrid.ItemsSource = dt.DefaultView;
}
public ChangeMan()
{
InitializeComponent();
string s = "select distinct buildingnum from broom";
DataTable dt = mysql.ExecuteQuery(s);
comboBox.ItemsSource = dt.DefaultView;
comboBox.DisplayMemberPath = "buildingnum";
}
public DorChange()
{
InitializeComponent();
comboBox.ItemsSource = mysql.ExecuteQuery("select distinct buildingnum from broom").DefaultView;
comboBox.DisplayMemberPath = "buildingnum";
sex.Add("男生宿舍");
sex.Add("女生宿舍");
size.Add("4");
size.Add("6");
comboBox1.ItemsSource = sex;
comboBox2.ItemsSource = size;
}
public Late()
{
InitializeComponent();
string s = "select roomnum from broom where buildingnum ='" + Source.build + "'";
dt = mysql.ExecuteQuery(s);
for (int i = 0; i < dt.Rows.Count; i++)
{
string r = dt.Rows[i][0].ToString();
list.Add(r);
}
comboBox.ItemsSource = list;
}
public ManageUd()
{
InitializeComponent();
string s = "select wnum as '工号',wname as '姓名',bnum as '管理栋号' from worker";
dt = mysql.ExecuteQuery(s);
dataGrid.ItemsSource = dt.DefaultView;
string s1 = "select wnum from worker";
comboBox.ItemsSource = mysql.ExecuteQuery(s1).DefaultView;
comboBox.DisplayMemberPath = "wnum";
string s2 = "select distinct bnum from worker order by bnum";
comboBox1.ItemsSource = mysql.ExecuteQuery(s2).DefaultView;
comboBox1.DisplayMemberPath = "bnum";
}
public Boolean setin(string id, string bunum)
{
MYSql mYSql = new MYSql();
string sql = "select Ssex from student where sid = '" + id + "'";;
DataTable dt = mYSql.ExecuteQuery(sql);
if (dt.Rows.Count == 0)
{
return(false);
}
string sex = dt.Rows[0]["Ssex"].ToString();
if (sex == "男")
{
if (Convert.ToInt32(bunum) % 2 == 0)
{
return(true);
}
else
{
return(false);
}
}
else
{
if (Convert.ToInt32(bunum) % 2 != 0)
{
return(true);
}
else
{
return(false);
}
}
}
public static DataTable changeDt()
{
MYSql mysql = new MYSql();
string s = "select wnum as '工号',wname as '姓名',bnum as '管理栋号' from worker";
return(dt = mysql.ExecuteQuery(s));
}
private async void button_Click(object sender, RoutedEventArgs e)//增加寝室
{
string buildingnum = textBox.Text;
string roomnum = textBox1.Text;
string sex = textBox3.Text;
int size;
if (buildingnum == "" || roomnum == "" || sex == "" || textBox2.Text == "")
{
await this.ShowMessageAsync("提示", "请输入完整信息");
}
//性别选项
try
{
size = Convert.ToInt32(textBox2.Text);
string s = "insert into DS(buildingnum,roomnum,brsex,size)values('" + buildingnum + "','" + sex + "','" + roomnum + "','" + size + "')";
MYSql mYSql = new MYSql();
try
{
mYSql.ExecuteQuery(s);
await this.ShowMessageAsync("提示", "修改成功");
}
catch
{
await this.ShowMessageAsync("提示", "已存在该宿舍");
}
}
catch
{
await this.ShowMessageAsync("提示", "数据未按格式输入");
}
}
private async void button_Click(object sender, RoutedEventArgs e)
{
IntPtr p = System.Runtime.InteropServices.Marshal.SecureStringToBSTR(this.passwordBox.SecurePassword);
string password = System.Runtime.InteropServices.Marshal.PtrToStringBSTR(p);
IntPtr p1 = System.Runtime.InteropServices.Marshal.SecureStringToBSTR(this.passwordBox1.SecurePassword);
string password1 = System.Runtime.InteropServices.Marshal.PtrToStringBSTR(p1);
MYSql mysql = new MYSql();
if (textBox.Text == "" || password == "" || password1 == "")
{
await this.ShowMessageAsync("提示", "信息填写不完整");
}
else
{
string username = textBox.Text.Trim();
string s1 = "select wnum,pwd from worker where wnum='" + username + "' and pwd='" + password + "'";
DataTable dt = new DataTable();
dt = mysql.ExecuteQuery(s1);
if (dt != null && dt.Rows.Count > 0)
{
string s2 = "update worker set pwd='" + password1 + "'where wnum='" + username + "'";
int n = mysql.ExecuteUpdate(s2);
if (n != 0)
{
await this.ShowMessageAsync("提示", "修改成功");
this.Close();
}
}
else
{
await this.ShowMessageAsync("提示", "用户名或旧密码输入错误");
}
}
}
private async void button_Click(object sender, RoutedEventArgs e)//确定
{
if (textBox.Text == "" || textBox1.Text == "")
{
await this.ShowMessageAsync("提示", "信息填写不完整");
}
else
{
try
{
string currenttime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string name = textBox.Text;
string thing = textBox1.Text;
string build = Source.build;
string s = "Insert into Come values('" + build + "','" + name + "','" + currenttime + "','" + thing + "')";
mysql.ExecuteUpdate(s);
this.Close();
maincome.dataGrid.ItemsSource = mysql.ExecuteQuery("select ctime as '时间',name as '姓名',thing as '原因' from Come where buildingnum='" + Source.build + "'").DefaultView;//刷新新表格
}
catch (Exception ex)
{
await this.ShowMessageAsync("错误", ex.ToString());
}
}
}
public String GetSex(string id)
{
string s = "select Ssex from student where sid = '" + id + "'";
MYSql mYSql = new MYSql();
DataTable dt = mYSql.ExecuteQuery(s);
return(dt.Rows[0]["Ssex"].ToString());
}
public DataTable getRepairman()
{
MYSql mYSql = new MYSql();
DataTable dt = new DataTable();
dt = mYSql.ExecuteQuery("select rid from repairman");
return(dt);
}
public bool Getin(string buildingnum, string roomnum, int type) //type等于1 判断一个人入住，等于2 判断两个人入住情况
{
int size;
buildingnum = buildingnum.Replace("System.Windows.Controls.ComboBoxItem: ", "");
MYSql mYSql = new MYSql();
DataTable dt = mYSql.ExecuteQuery("select 'size'=count(*) from DS where buildingnum = '" + buildingnum + "' and roomnum = '" + roomnum + "'");
if (dt.Rows.Count == 0)
{
size = 0;
return(true);
}
size = Convert.ToInt32(dt.Rows[0]["size"]);
dt = mYSql.ExecuteQuery("select size from broom where buildingnum = '" + buildingnum + "' and roomnum = '" + roomnum + "'");
if (dt.Rows.Count == 0)
{
return(false);
}
int maxsize = Convert.ToInt32(dt.Rows[0]["size"]);
switch (type)
{
case 1:
if (size == maxsize)
{
return(false);
}
else
{
return(true);
}
case 2:
if (size != 0)
{
return(false);
}
return(true);
default:
break;
}
return(false);
}
public int getMnum(string buildingnum)
{
string s = "select count(*) as num from worker group by bnum";
MYSql mYSql = new MYSql();
DataTable dt = mYSql.ExecuteQuery(s);
int num = Convert.ToInt32(dt.Rows[0]["num"]);
return(num);
}
public string GetBuildingnum(string wnum)
{
MYSql mYSql = new MYSql();
DataTable dt = new DataTable();
dt = mYSql.ExecuteQuery("select bnum from worker where wnum = '" + wnum + "'");
string bnum = dt.Rows[0]["bnum"].ToString();
return(bnum);
}
public List <String> getstu()
{
List <string> slist = new List <String>();
MYSql mYSql = new MYSql();
DataTable dt = mYSql.ExecuteQuery("select sid from student where sid not in (select sid from DS)");
for (int i = 0; i < dt.Rows.Count; i++)
{
slist.Add(dt.Rows[i]["sid"].ToString());
}
return(slist);
}
public MainLate()
{
InitializeComponent();
string s1 = "select roomnum as '寝室号',sid as '学号',time as '时间',thing as '原因' from Late where buildingnum= '" + Source.build + "'";
dt = mysql.ExecuteQuery(s1);
dataGrid.ItemsSource = dt.DefaultView;
string s = "select roomnum from broom where buildingnum='" + Source.build + "'";
comboBox.ItemsSource = mysql.ExecuteQuery(s).DefaultView;
comboBox.DisplayMemberPath = "roomnum";
}
DataTable dta = new DataTable();//赋值dt表中的数据，以便进行查询
public void first()
{
dt = mysql.ExecuteQuery(s);
string s1 = "select buildingnum,roomnum,brsex,size from broom where buildingnum+roomnum not in(select buildingnum+roomnum from DS)";
dt1 = mysql.ExecuteQuery(s1);
for (int i = 0; i < dt1.Rows.Count; i++)
{
DataRow dRow = dt.NewRow();
dRow[0] = dt1.Rows[i][0];
dRow[1] = dt1.Rows[i][1];
dRow[2] = dt1.Rows[i][2];
dRow[3] = dt1.Rows[i][3];
dRow[4] = 0;
dt.Rows.Add(dRow);
}
dataGrid.ItemsSource = dt.DefaultView;
dta.Columns.Add("栋号");
dta.Columns.Add("寝室号");
dta.Columns.Add("女寝or男寝");
dta.Columns.Add("寝室大小");
dta.Columns.Add("实际人数");
}
public bool notin(string id)
{
string sql = "select sid from DS where sid ='" + id + "'";
MYSql mYSql = new MYSql();
DataTable dt = mYSql.ExecuteQuery(sql);
try
{
string ids = dt.Rows[0]["sid"].ToString();
return(false);
}
catch
{
return(true);
}
}
public SdDetail()
{
InitializeComponent();
string s = "select student.sid as '学号',student.sname as '姓名',student.Ssex as '性别',student.scollege as '学院',DS.buildingnum as '栋号',DS.roomnum as '寝室号' from student,DS where student.sid=DS.sid";
dt = mysql.ExecuteQuery(s);
dataGrid.ItemsSource = dt.DefaultView;
string s1 = "select distinct scollege from student";
DataTable d1 = mysql.ExecuteQuery(s1);
comboBox1.ItemsSource = d1.DefaultView;
comboBox1.DisplayMemberPath = "scollege";
string s2 = "select distinct buildingnum from broom";
DataTable d2 = mysql.ExecuteQuery(s2);
for (int i = 0; i < d2.Rows.Count; i++)
{
string r = d2.Rows[i][0].ToString();
list.Add(r);
}
comboBox.ItemsSource = list;
}
public int getacsize(string buildingnum, string roomnum)
{
string s = "select count(*) as size from DS where buildingnum = '" + buildingnum + "' and roomnum = '" + roomnum + "'";
int size;
MYSql mYSql = new MYSql();
DataTable dt = mYSql.ExecuteQuery(s);
if (dt.Rows[0]["size"] == null)
{
size = 0;
}
else
{
size = Convert.ToInt32(dt.Rows[0]["size"]);
}
return(size);
}
/// <summary>
/// 自动为学生分配寝室
/// </summary>
public List <Room> geteRoom(string sex)
{
MYSql mYSql = new MYSql();
List <Room> roomlist = new List <Room>();
string sql = "select * from broom where brsex = '" + sex + "'";
DataTable dt = mYSql.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
string buildingnum = dt.Rows[i]["buildingnum"].ToString();
string roomnum = dt.Rows[i]["roomnum"].ToString();
string brsex = dt.Rows[i]["brsex"].ToString();
int size = Convert.ToInt32(dt.Rows[i]["size"]);
roomlist.Add(new Room(buildingnum, roomnum, brsex, size));
}
return(roomlist);
}
public string[] GetTable(string sid)
{
string[] temp = { "", "" };
string sql = "select buildingnum,roomnum from DS where sid = '" + sid + "'";
DataTable dt = new DataTable();
MYSql mysql = new MYSql();
dt = mysql.ExecuteQuery(sql);
try
{
temp[0] = dt.Rows[0]["buildingnum"].ToString();
temp[1] = dt.Rows[0]["roomnum"].ToString();
}
catch (Exception ex)
{
temp[0] = "error";
temp[1] = "error";
}
return(temp);
}
private async void button_Click(object sender, RoutedEventArgs e)
{
string info = infoBox.Text;
string time;
System.DateTime dateTime = new System.DateTime();
dateTime = System.DateTime.Now;
time = dateTime.Year.ToString() + "-" + toStd(dateTime.Month) + "-" + toStd(dateTime.Day);
MYSql mYSql = new MYSql();
string sql = "insert into record(content,buildingnum,roomnum,status,redate)values('" + info + "','" + buildingnum + "','" + roomnum + "','" + 1 + "','" + time + "')";
try
{
mYSql.ExecuteQuery(sql);
await this.ShowMessageAsync(buildingnum + "栋" + roomnum + "号寝室", "您的信息已提交");
}catch (Exception ex)
{
await this.ShowMessageAsync(ex.ToString(), "");
}
}
public userStu()
{
InitializeComponent();
string s = "select student.sid as '学号',student.sname as '姓名',student.Ssex as '性别',student.scollege as '学院',DS.buildingnum as '栋号',DS.roomnum as '寝室号' from student,DS where student.sid=DS.sid and DS.buildingnum='" + Source.build + "'";
dt = mysql.ExecuteQuery(s);
dataGrid.ItemsSource = dt.DefaultView;
string s1 = "select distinct scollege from student,DS where buildingnum='" + Source.build + "'and student.sid=DS.sid";
DataTable d1 = mysql.ExecuteQuery(s1);
comboBox.ItemsSource = d1.DefaultView;
comboBox.DisplayMemberPath = "scollege";
string s2 = "select roomnum from broom where buildingnum='" + Source.build + "'";
DataTable d2 = mysql.ExecuteQuery(s2);
comboBox1.ItemsSource = d2.DefaultView;
comboBox1.DisplayMemberPath = "roomnum";
}