private void LogIn_Click(object sender, RoutedEventArgs e)
{
DB db = new DB();
string uid = this.UserNumber.Text.Trim();
string pwd = this.PassWord.Password;
if (uid == null || uid.Equals(""))
{
this.Info.Height = 20;
this.Info.Content = "用户名不能为空！";
return;
}
if (pwd == null || pwd.Equals(""))
{
this.Info.Height = 20;
this.Info.Content = "密码不能为空！";
return;
}
/* 验证登录。* 3 无此用户* 2 密码不对 * 1 非管理员* 0 成功 */
int login = db.LogIn(this.UserNumber.Text, this.PassWord.Password);
switch (login)
{
case 3: this.Info.Height = 20; this.Info.Content = "没有此用户！"; break;
case 2: this.Info.Height = 20; this.Info.Content = "用户名或密码不正确！"; break;
case 1: this.Info.Height = 20; this.Info.Content = "您没有管理权限！"; break;
case 0: this.Info.Height = 20; this.Info.Content = "验证成功"; this.DialogResult = true; this.Close(); break;
default: break;
}
}
public Courses()
{
InitializeComponent();
this.db = new DB();
this.show_list();
LoadData();
}
public Teachers()
{
InitializeComponent();
this.db = new DB();
this.show_list();
this.LoadData();
}
public Schedules()
{
InitializeComponent();
this.db = new DB();
//this.dt = new System.Data.DataTable();
this.show_list();
LoadData();
}
public Students()
{
InitializeComponent();
this.currentStudent = new Student();
this.db = new DB();
this.show_list();
LoadData();
}
private void import_Click(object sender, RoutedEventArgs e)
{
DB db = new DB();
string dbPath = this.filePath.Text;
this.info.Content += "导入中....\n";
if(dbPath==null||dbPath.Trim().Equals(""))
{
this.info.Content = "请选择有效路径！\n";
return;
}
int k = db.merge_ORDER(new DB(dbPath));
this.info.Content += "文件" + dbPath + "导入成功！本次变更了" + k + "条记录。\n";
//this.filePath.Text = "";
}
public void LoadData()
{
DB db = new DB();
dt = db.ExecuteDataTable("SELECT * FROM TEACHER", null);
this.dataGrid1.ItemsSource = dt.DefaultView;
}
public void LoadData()
{
DB db = new DB();
//dt = db.ExecuteDataTable("SELECT * FROM COURSE", null);
string sql = "select course.*, ifnull (b.usedTime,'0') as USEDTIME from course left join (select course_ID,count(*) as usedTime from orders group by course_id) as b on course._id = b.course_id";
dt = db.ExecuteDataTable(sql, null);
this.dataGrid1.ItemsSource = dt.DefaultView;
}
public int merge_ORDER(DB db2)
{
int sum = 0;
int change=0;
SQLiteCommand cmd2 = new SQLiteCommand("select * from ORDERS",db2.connection);
SQLiteDataReader reader2 = cmd2.ExecuteReader();
SQLiteCommand cmd1 = this.connection.CreateCommand();
SQLiteDataReader reader1 = null;
if(reader2.HasRows)
{
while(reader2.Read())
{
long s1 = reader2.GetInt64(2);
string s2 = reader2.GetString(1);
cmd1.CommandText = "select * from ORDERS where ORDERNUMBER=\"" + reader2.GetString(1) + "\" and COURSE_ID = " + reader2.GetInt64(2);
reader1 = cmd1.ExecuteReader();
if (!reader1.HasRows)
{
SQLiteCommand cmdTemp = new SQLiteCommand(connection);
cmdTemp.CommandText = "insert into ORDERS(ORDERNUMBER,COURSE_ID,USER_ID,ADDTIME,STATUS,DEV_ID,EXPECTED_TIME,REMARK) VALUES(@mORDERNUMBER,@mCOURSE_ID,@mUSER_ID,@mADDTIME,@mSTATUS,@mDEV_ID,@mEXPECTED_TIME,@mREMARK)";
SQLiteParameter[] parameters = new SQLiteParameter[8];
parameters[0] = (new SQLiteParameter("@mORDERNUMBER", reader2.GetString(1)));
parameters[1] = (new SQLiteParameter("@mCOURSE_ID", reader2.GetInt64(2)));
parameters[2] = (new SQLiteParameter("@mUSER_ID", reader2.GetInt64(3)));
parameters[3] = (new SQLiteParameter("@mADDTIME", reader2.GetInt64(4)));
parameters[4] = (new SQLiteParameter("@mSTATUS", reader2.GetInt64(5)));
parameters[5] = (new SQLiteParameter("@mDEV_ID", reader2.GetString(6)));
parameters[6] = (new SQLiteParameter("@mEXPECTED_TIME", reader2.GetString(7)));
parameters[7] = (new SQLiteParameter("@mREMARK", reader2.GetString(8)));
cmdTemp.Parameters.AddRange(parameters);
int affected = cmdTemp.ExecuteNonQuery();
if (affected > 0)
sum++;
}
else//update
{
SQLiteCommand cmdTemp = new SQLiteCommand(connection);
//cmdTemp.CommandText = "insert into ORDERS(ORDERNUMBER,COURSE_ID,USER_ID,ADDTIME,STATUS,DEV_ID,EXPECTED_TIME,REMARK) VALUES(@mORDERNUMBER,@mCOURSE_ID,@mUSER_ID,@mADDTIME,@mSTATUS,@mDEV_ID,@mEXPECTED_TIME,@mREMARK)";
cmdTemp.CommandText = "update ORDERS set ORDERNUMBER=@mORDERNUMBER,COURSE_ID=@mCOURSE_ID,USER_ID=@mUSER_ID,ADDTIME=@mADDTIME,STATUS=@mSTATUS,DEV_ID=@mDEV_ID,EXPECTED_TIME=@mEXPECTED_TIME,REMARK=@mREMARK where ORDERNUMBER=\"" + reader2.GetString(1) + "\" and COURSE_ID = " + reader2.GetInt64(2);
SQLiteParameter[] parameters = new SQLiteParameter[8];
parameters[0] = (new SQLiteParameter("@mORDERNUMBER", reader2.GetString(1)));
parameters[1] = (new SQLiteParameter("@mCOURSE_ID", reader2.GetInt64(2)));
parameters[2] = (new SQLiteParameter("@mUSER_ID", reader2.GetInt64(3)));
parameters[3] = (new SQLiteParameter("@mADDTIME", reader2.GetInt64(4)));
parameters[4] = (new SQLiteParameter("@mSTATUS", reader2.GetInt64(5)));
parameters[5] = (new SQLiteParameter("@mDEV_ID", reader2.GetString(6)));
parameters[6] = (new SQLiteParameter("@mEXPECTED_TIME", reader2.GetString(7)));
parameters[7] = (new SQLiteParameter("@mREMARK", reader2.GetString(8)));
cmdTemp.Parameters.AddRange(parameters);
int affected = cmdTemp.ExecuteNonQuery();
if (affected > 0)
sum++;
}
reader1.Close();
}
}
return sum;
}
public void LoadData()
{
DB db = new DB();
dt = db.ExecuteDataTable("SELECT ORDERS._id as _id, ORDERS.ORDERNUMBER as 订单号, COURSE.NAME as 课程名, COURSE.COURSENUMBER as 课程编号, COURSE.TIME as 课时, COURSE.PRICE as 价格,USER.NAME as 联系人, USER._id as 客户,USER.NAME as 联系人,USER.PHONE as 联系人电话,USER.EMAIL as 邮箱,USER.ADDRESS as 地址,ADDTIME,ORDERS.EXPECTED_TIME as 期望上课时间,ORDERS.REMARK as 备注 from ORDERS inner join USER on ORDERS.[USER_ID] = USER.[_id] inner join COURSE on ORDERS.COURSE_ID = COURSE._id WHERE ORDERS.[STATUS] = 0", null);
dt.Columns.Add("GMT_time", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][dt.Columns[15].ColumnName] = this.db.GetTime(dt.Rows[i][dt.Columns[12].ColumnName].ToString());
}
this.dataGrid1.ItemsSource = dt.DefaultView;
this.dataGrid1.CanUserAddRows = false;
}
/**
* 添加用户
*/
private void Add_Save_Click(object sender, RoutedEventArgs e)
{
int age;
if (this.aUserName.Text == null || this.aUserName.Text.Trim().Equals(""))
{
Message ms = new Message("错误提示", "用户名称不能为空！");
ms.ShowDialog();
return;
}
if (this.db.getStudentByName(this.aUserName.Text)!=null)
{
Message ms = new Message("错误提示", "该名称已经被占用！");
ms.ShowDialog();
return;
}
try
{
age = Int16.Parse(this.aAge.Text);
}
catch (Exception age_e)
{
Message ms = new Message("错误提示", "数据类型不符！年龄必须为数字。");
ms.ShowDialog();
return;
}
Student student = new Student();
student.USERNAME = this.aUserName.Text;
student.password = this.aPassword.Text;
student.NAME = this.aName.Text;
student.age = age;
student.sex = this.aSex_nan.IsChecked == true ? "男" : "女";//this.aSex.Text;
student.phone = this.aPhone.Text;
student.email = this.aEmail.Text;
student.address = this.aAddress.Text;
student.remark = this.aRemark.Text;
student.photo = Utils.Utils.BitmapImageToByteArray((BitmapImage)(this.aPhoto.Source));//Utils.Utils.BitmapImageToByteArray((BitmapImage)(this.aPhoto.Source));
student.isAdmin = this.aIsadmin.IsChecked == true ? 1 : 0;
DB db = new DB();
int res = db.insertStudent(student);
if (res > 0)
{
Message msg = new Message("添加客户", "成功添加客户" + student.USERNAME + "。");
msg.ShowDialog();
this.LoadData();
// 自动跳转
List_Click(sender, e);
}
else
{
Message msg = new Message("添加客户", "添加客户" + student.USERNAME + "失败！");
msg.ShowDialog();
}
}
