/// <summary>
/// 获取所有视频列表
/// </summary>
/// <returns></returns>
public DataTable getvideolist()
{
string sql = "select V.*,u.User_Name from TB_Video v inner join TB_User u on v.Video_User = u.User_ID order by Video_ID desc";
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, CommandType.Text);
return(dt);
}
/// <summary>
/// 查询解决工单表
/// </summary>
/// <returns></returns>
public DataTable GetSolveList()
{
string sql = "select * from TB_SolveList";
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, CommandType.Text);
return(dt);
}
public DataTable getUsers(string teacherID)
{
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@teacherID", teacherID) };
string sql = "SELECT * FROM TB_User where User_Teacher = @teacherID and User_Del = 0";
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, paras, CommandType.Text);
return(dt);
}
/// <summary>
/// 获取拟合源数据
/// </summary>
/// <param name="TB_Name"></param>
/// <param name="option"></param>
/// <returns></returns>
public DataTable getNormalData(string usertype)
{
string sql = "ProCreateNew";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@tablename", "class" + usertype) };
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, paras, CommandType.StoredProcedure);
return(dt);
}
/// <summary>
/// 教师登录
/// </summary>
/// <param name="ID"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public DataTable getteacherlogin(string ID, string pwd)
{
string sql = "select * from TB_User where (User_Name =@ID) and User_PWD = @pwd and User_Teacher = 0";
SqlParameter[] paras = { new SqlParameter("@ID", ID), new SqlParameter("@pwd", pwd) };
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, paras, CommandType.Text);
return(dt);
}
public DataTable getvideo(string videoid)
{
string sql = "select V.*,u.User_Name from TB_Video v inner join TB_User u on v.Video_User = u.User_ID where Video_ID = @id";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@id", videoid) };
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, paras, CommandType.Text);
return(dt);
}
public DataTable GetAbList()
{
string sql = "select * from TB_AbList";
/*
* IsSubmit列为1时说明解决表单已经提交，查看工单按钮
* IsSubmit列为0时说明解决表单还未提交，提交工单按钮
*/
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, CommandType.Text);
return(dt);
}
/// <summary>
/// 根据异常Id查询工单记录
/// </summary>
/// <param name="abId">异常Id</param>
/// <returns></returns>
public DataTable GetSolve(int abId)
{
string sql = "select * from TB_SolveList where AbId=@id";
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@id", abId)
};
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, paras, CommandType.Text);
return(dt);
}
public string postdeluser(string UserID)
{
string res = "False";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UserID", UserID) };
string sql = "update TB_User set User_Del = 1 where User_ID = @UserID";
int flag = new Helper.SQLHelper().ExecuteNonQuery(sql, paras, CommandType.Text);
if (flag > 0)
{
res = "True";
}
return(res);
}
public string postchangeuser(string name, string pwd, string teacher, string id)
{
string res = "False";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@name", name), new SqlParameter("@pwd", pwd), new SqlParameter("@teacher", teacher), new SqlParameter("@UserID", id), };
string sql = "update TB_User set User_Name = @name, User_PWD = @pwd, User_Teacher = @teacher where User_ID = @UserID";
int flag = new Helper.SQLHelper().ExecuteNonQuery(sql, paras, CommandType.Text);
if (flag > 0)
{
res = "True";
}
return(res);
}
public string postuser(string name, string pwd, string teacher)
{
string res = "False";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@name", name), new SqlParameter("@pwd", pwd), new SqlParameter("@teacher", teacher) };
string sql = "insert into TB_User (User_Name,User_PWD,User_Teacher) values (@name ,@pwd , @teacher)";
int flag = new Helper.SQLHelper().ExecuteNonQuery(sql, paras, CommandType.Text);
if (flag > 0)
{
res = "True";
}
return(res);
}
public string postvideo(string userid, string problem, string contest, string url, string hash)
{
string res = "false";
string sql = "insert into TB_Video (Video_User,Video_Problem,Video_Contest,Video_URL,Video_Hash) values (@userid,@problem,@contest,@url,@hash)";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@userid", userid),
new SqlParameter("@problem", problem), new SqlParameter("@contest", contest),
new SqlParameter("@url", url), new SqlParameter("@hash", hash) };
int flag = new Helper.SQLHelper().ExecuteNonQuery(sql, paras, CommandType.Text);
if (flag > 0)
{
res = "true";
}
return(res);
}
/// <summary>
/// 插入生成的数据
/// </summary>
/// <param name="NorID"></param>
/// <param name="DataI"></param>
/// <param name="DataU"></param>
/// <param name="DataW"></param>
/// <returns></returns>
public string InsertIUW(string UpperID, string DataI, string DataU, string DataW)
{
string flag = "False"; //默认返回False
string sql = "InsertIUW"; //插入数据存储过程
SqlParameter[] paras = { new SqlParameter("@UpperID", UpperID),
new SqlParameter("@DataI", DataI),
new SqlParameter("@DataU", DataU),
new SqlParameter("@DataW", DataW) }; //参数 NorID，电流96点Json，电压96点Json，电能96点Json，
int res = new Helper.SQLHelper().ExecuteNonQuery(sql, paras, CommandType.StoredProcedure); //使用存储过程
if (res > 0)
{
flag = "True";//插入成功返回True
}
return(flag);
}
/// <summary>
/// 插入一条TB_Data,生成UpperID，NorID，AbIDe，返回 UpperID
/// </summary>
/// <param name="UserType">用户类别</param>
/// <returns></returns>
public string SetData(string UserType)
{
string res = "False"; //异常失败返回False
string sql = "InsertTB_Data"; //插入一条TB_Data,生成UpperID，NorID，AbIDe，返回 UpperID
string UpperID = "";
try
{
SqlParameter[] paras = { new SqlParameter("@UserType", UserType) };
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, paras, CommandType.StoredProcedure);//使用存储过程
res = dt.Rows[0]["Data_UpperID"].ToString();
}catch {
return(res); //报错返回False
}
if (UpperID == "") //若为获取UpperID返回False
{
UpperID = res;
}
return(UpperID);
}
/// <summary>
/// 插入解决工单
/// </summary>
/// <param name="abId">异常记录ID</param>
/// <param name="voltage">电压</param>
/// <param name="electricity">电流</param>
/// <param name="wattage">用电量</param>
/// <param name="scription">描述</param>
/// <param name="suberName">提交者姓名</param>
/// <returns></returns>
public string InsertSolve(string abId, string voltage, string electricity, string wattage, string scription, string suberName)
{
string res = "false";
SqlParameter[] paras1 = new SqlParameter[]
{
new SqlParameter("@AbId", abId),
new SqlParameter("@Voltage", voltage),
new SqlParameter("@Electricity", electricity),
new SqlParameter("@Wattage", wattage),
new SqlParameter("@Scription", scription),
new SqlParameter("@SuberName", suberName)
};
string sql1 = "insert into TB_SolveList(AbId,Voltage_V,Electricity_A,Wattage,Scription,SuberName) values (@AbId,@Voltage,@Electricity,@Wattage,@Scription,@SuberName)";
SqlParameter[] paras2 = new SqlParameter[]
{
new SqlParameter("@AbId", abId)
};
string sql2 = "update TB_AbList set IsSubmit='true'where AbId=@AbId";
try
{
int flag1 = new Helper.SQLHelper().ExecuteNonQuery(sql1, paras1, CommandType.Text);
int flag2 = new Helper.SQLHelper().ExecuteNonQuery(sql2, paras2, CommandType.Text);
if (flag1 > 0 && flag2 > 0)
{
res = "True";
}
}
catch (Exception exception)
{
throw exception;
}
return(res);
}
public DataTable getNormalData(string TB_Name, string option)
{
string sql = "";
int tableflag = 0;
switch (TB_Name)
{
case "class01JCC":
tableflag = 0;
break;
case "class02CLC":
tableflag = 1;
break;
case "class03ZZC":
tableflag = 2;
break;
case "class04HGC":
tableflag = 3;
break;
case "class05ZGC":
tableflag = 4;
break;
case "class06Hospital":
tableflag = 5;
break;
case "class07ZJDX":
tableflag = 6;
break;
case "class08FZC":
tableflag = 7;
break;
case "class09CKC":
tableflag = 8;
break;
case "class10SC":
tableflag = 9;
break;
default:
break;
}
//判断日期
int dayofyear = DateTime.Now.DayOfYear;
if (dayofyear != Models.Data.dayofyear)
{
Models.Data.time = new string[] { " ", " ", " ", " ", " ", " ", " ", " ", " ", " " };
Models.Data.dayofyear = dayofyear;
}
if (Models.Data.time[tableflag] == " ")
{
string sqlgetdate = "ProCreateNew";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@tablename", TB_Name) };
DataTable dtgetdate = new Helper.SQLHelper().ExcuteQuery(sqlgetdate, paras, CommandType.StoredProcedure);
Models.Data.time[tableflag] = dtgetdate.Rows[0]["时间"].ToString();
}
string starttime = Models.Data.time[tableflag];
;//起始时间
switch (option)
{
case "I":
sql = "select top 672 时间,A相电流,B相电流,C相电流 from " + TB_Name + " where 时间 >= '" + starttime + "' order by 时间 asc";
break;
case "U":
sql = "select top 672 时间,A相电压,B相电压,C相电压 from " + TB_Name + " where 时间 >= '" + starttime + "' order by 时间 asc";
break;
case "W":
sql = "select top 7 时间,用电量,变压器容量,倍率 from " + TB_Name + " where 时间 >= '" + starttime + "' and 用电量 is not NULL";
break;
default:
break;
}
//SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TBname", TB_Name) };
DataTable dt = new Helper.SQLHelper().ExcuteQuery(sql, CommandType.Text);
return(dt);
}
