/* mySQL */
private void btn_DisplaySQL_Click(object sender, EventArgs e)
{
SQLserver blc = new SQLserver();
gridview.DataSource = blc.DisplayEmpsOnGrid();
gridview.Columns["EmpFirstName"].HeaderText = "Prenume";
gridview.Columns["EmpLastName"].HeaderText = "Nume";
gridview.Columns["Salary"].HeaderText = "Salariu";
gridview.Columns["City"].HeaderText = "Oras";
gridview.Columns["PhoneNo"].HeaderText = "Numar Telefon";
}
/// <summary>
/// 获取登陆用户信息，判断登陆是否上限
/// </summary>
/// <returns></returns>
//public static string IsRole()
//{
// string Result = string.Empty;
// string sql = string.Format("SELECT username,password,LoginSize,role FROM usertable WHERE username='******' AND password='******';", uname, pswd);
// DataTable dt = new DataTable();
// try
// {
// dt = SQLserver.QueryTable(sql);//获取 用户名/密码 与uname/pswd 匹配的该条数据记录（username,password,LoginSize,role）
// if (dt.Rows.Count == 0)
// {
// return Result;
// }
// usertable ut = new usertable()
// {
// username = dt.Rows[0]["username"].ToString(),
// password = dt.Rows[0]["password"].ToString(),
// LoginSize = Convert.ToInt32(dt.Rows[0]["LoginSize"]),
// role = Convert.ToInt32(dt.Rows[0]["role"])
// };
// if (ut.role == 0)
// {
// if (ut.LoginSize >= 0 && ut.LoginSize < 10)
// {
// dicClass.Add(ut);//本地保存用户信息
// //数据库中标识+1
// string sqlstr = string.Format("UPDATE usertable set LoginSize = LoginSize+1 WHERE username='******' AND password='******';", uname, pswd);
// try
// {
// int i = SQLserver.ExecuteSql(sqlstr);
// }
// catch (Exception)
// {
// throw;
// }
// return Result;
// }
// else
// {
// Result = "登陆超过上限";
// }
// }
// else if (ut.role == 1)
// {
// if (ut.LoginSize >= 0 && ut.LoginSize < 2)
// {
// dicClass.Add(ut);//本地保存用户信息
// //数据库中标识+1
// string sqlstr = string.Format("UPDATE usertable set LoginSize = LoginSize+1 WHERE username='******' AND password='******';", uname, pswd);
// try
// {
// int i = SQLserver.ExecuteSql(sqlstr);
// }
// catch (Exception)
// {
// throw;
// }
// return Result;
// }
// else
// {
// Result = "登陆超过上限";
// }
// }
// }
// catch (Exception)
// {
// throw;
// }
// return Result;
//}
/// <summary>
/// 用户登录过期，删除登陆
/// </summary>
public static void UpdLoginSize(usertable us)
{
if (dicClass.Count == 0)
{
return;
}
string sql = string.Format("UPDATE usertable set LoginSize = LoginSize-1 WHERE username='******' AND password='******';", us.username, us.password);
try
{
int i = SQLserver.ExecuteSql(sql);
}
catch (Exception)
{
throw;
}
}
public static string getData(string tb, string startTime, string endTime, string[] seriesNames)
{
//string[] sname = new string[10];
// sname = seriesNames.Split(',');
string jsonStr = null;
DataTable dt = new DataTable();
//try
{
if (tb == "YW")
{
string[] serieNameP = { "datetimee", "出水流量", "进水流量", "进水液位" };
dt = SQLserver.GetDataDischarge(startTime, endTime);
jsonStr = Dtb2Json(dt, serieNameP);//将datatable转换为json字符串
}
else if (tb == "LLday")
{
string[] serieNameP = { "datetimee", "一期日进水", "二期日进水", "日进水流量", "日出水流量" };
dt = SQLserver.GetDataDischarge(tb, startTime, endTime);
//将datatable转换为json字符串
jsonStr = Dtb2Json(dt, serieNameP);
}
else if (tb == "all")//主页数据获取
{
string[] tbS = { "YW", "JS", "CS", "LLday", "YQ", "EQ" };
jsonStr = getList2Json(tbS);
}
else
{
tb = tb.Substring(0, 2);
dt = SQLserver.GetData(tb, startTime, endTime);
jsonStr = Dtb2Json(dt, seriesNames);//将datatable转换为json字符串
}
}
return(jsonStr);
}
private void btn_InsertSQL_Click(object sender, EventArgs e)
{
SQLserver blcl = new SQLserver();
if (string.IsNullOrWhiteSpace(txt_FirstName.Text))
{
MessageBox.Show("Prenumele nu este completat", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrWhiteSpace(txt_LastName.Text))
{
MessageBox.Show("Numele nu este completat", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrWhiteSpace(txt_Salary.Text))
{
MessageBox.Show("Salariul nu este completat", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrWhiteSpace(txt_City.Text))
{
MessageBox.Show("Orasul nu este completat", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrWhiteSpace(txt_PhoneNo.Text))
{
MessageBox.Show("Numarul de telefon nu este completat", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
blcl.InsertEmpsIntoDB(txt_FirstName.Text, txt_LastName.Text, txt_Salary.Text, txt_City.Text, txt_PhoneNo.Text, out bool status);
MessageBox.Show("Angajatul a fost adaugat");
}
public static string getList2Json(string[] tbNameS)
{
DataTable dt = SQLserver.getLast(tbNameS[0]); //YW
DataTable dt1 = SQLserver.getLast(tbNameS[1]); //JS
DataTable dt2 = SQLserver.getLast(tbNameS[2]); //CS
DataTable dt3 = SQLserver.getLast(tbNameS[3]); //LLday
DataTable dt4 = SQLserver.getLast(tbNameS[4]); //YQ
DataTable dt5 = SQLserver.getLast(tbNameS[5]); //EQ
JavaScriptSerializer jss = new JavaScriptSerializer();
ArrayList dic = new ArrayList();
Dictionary <string, object> drow = new Dictionary <string, object>();
dt = deleMore(dt);
dt1 = deleMore(dt1);
dt2 = deleMore(dt2);
dt3 = deleMore(dt3);
dt4 = deleMore(dt4);
dt5 = deleMore(dt5);
//================================液位表==========================================================================
foreach (DataRow row in dt.Rows)//YW
{
foreach (DataColumn col in dt.Columns)
{
//if (seriesNames.Contains(col.ColumnName))
{
if (col.DataType == (new DateTime()).GetType())//如果是时间数据
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
string timeStr = ((DateTime)row[col.ColumnName]).ToString("yyyy/MM/dd HH:mm");
drow.Add(col.ColumnName, timeStr);
}
else
{
drow.Add(col.ColumnName, row[col.ColumnName]);
}
}
}
}
//=========================================进水表==========================================================================
foreach (DataRow row in dt1.Rows)//JS
{
foreach (DataColumn col in dt1.Columns)
{
//if (seriesNames.Contains(col.ColumnName))
{
if (col.DataType == (new DateTime()).GetType())//如果是时间数据
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
string timeStr = ((DateTime)row[col.ColumnName]).ToString("yyyy/MM/dd HH:mm");
drow.Add(col.ColumnName + "1", timeStr);
}
else
{
drow.Add(col.ColumnName + "1", row[col.ColumnName]);
}
}
}
}
//=================================================出水表=========================================================================
foreach (DataRow row in dt2.Rows)//CS
{
foreach (DataColumn col in dt2.Columns)
{
// if (seriesNames.Contains(col.ColumnName))
{
if (col.DataType == (new DateTime()).GetType())//如果是时间数据
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
string timeStr = ((DateTime)row[col.ColumnName]).ToString("yyyy/MM/dd HH:mm");
drow.Add(col.ColumnName + "2", timeStr);
}
else
{
drow.Add(col.ColumnName + "2", row[col.ColumnName]);
}
}
}
}
//===================================================日累计流量=============================================================
foreach (DataRow row in dt3.Rows)//LLday
{
foreach (DataColumn col in dt3.Columns)
{
// if (seriesNames.Contains(col.ColumnName))
{
if (col.DataType == (new DateTime()).GetType())//如果是时间数据
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
string timeStr = ((DateTime)row[col.ColumnName]).ToString("yyyy/MM/dd HH:mm");
drow.Add(col.ColumnName + "_LLday", timeStr);
}
else
{
drow.Add(col.ColumnName + "_LLday", row[col.ColumnName]);
}
}
}
}
//=========================================================一期MLSS/DO================================================================
foreach (DataRow row in dt4.Rows)//YQ
{
foreach (DataColumn col in dt4.Columns)
{
//if (seriesNames.Contains(col.ColumnName))
{
if (col.DataType == (new DateTime()).GetType())//如果是时间数据
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
string timeStr = ((DateTime)row[col.ColumnName]).ToString("yyyy/MM/dd HH:mm");
drow.Add(col.ColumnName + "3", timeStr);
}
else
{
drow.Add(col.ColumnName + "3", row[col.ColumnName]);
}
}
}
}
//==================================================二期MLSS/DO===============================================================================
foreach (DataRow row in dt5.Rows)//EQ
{
foreach (DataColumn col in dt5.Columns)
{
//if (seriesNames.Contains(col.ColumnName))
{
if (col.DataType == (new DateTime()).GetType())//如果是时间数据
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
string timeStr = ((DateTime)row[col.ColumnName]).ToString("yyyy/MM/dd HH:mm");
drow.Add(col.ColumnName + "4", timeStr);
}
else
{
drow.Add(col.ColumnName + "4", row[col.ColumnName]);
}
}
}
}
dic.Add(drow);
string ccc = jss.Serialize(dic);
return(ccc);
}
private void btn_DeleteRecordSQL_Click(object sender, EventArgs e)
{
SQLserver blcl = new SQLserver();
blcl.DropSQLDB();
}
private void btn_CreateTableSQL_Click(object sender, EventArgs e)
{
SQLserver blcl = new SQLserver();
blcl.CreateSQLDB();
}
