public static void GetWorkPlanInfo(string colName)
{
SqlCommand cmd = PMCommand.SchCmd();
if (string.IsNullOrEmpty(colName))
{
cmd.CommandText = "select * from PMS_WorkPlans where sysID = '" + UserModels.PMUser.UserSysID + "' and Status = '" + PMAppSettings.PMPlState + "'";
}
else
{
cmd.CommandText = "select " + colName + " from PMS_WorkPlans where sysID = '" + UserModels.PMUser.UserSysID + "' and Status = '" + PMAppSettings.PMPlState + "'";
}
//cmd.CommandText = "select Owner,WorkPlanId,WorkPlanName from PMS_WorkPlans where sysID = '" + AppSettings.PMSysid + "' and Status = '" + AppSettings.PMPlState + "'";
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
Owner = rd["Owner"].ToString();
WorkPlanName = rd["WorkPlanName"].ToString();
WorkPlanId = rd["WorkPlanId"].ToString();
DateTime tmp = DateTime.Now;
try
{
Convert.ToDateTime(rd["planReleaseTime"].ToString());
}
catch (Exception)
{
}
ReleaseTime = tmp.Year.ToString() + "/" + tmp.Month.ToString() + "/" + tmp.Day.ToString() + " " + tmp.Hour.ToString() + ":" + tmp.Minute.ToString();
rd.Close();
cmd.Connection.Dispose();
}
public string Set_user_purview(string empid, string purviewStr)
{
string[] purviewList = purviewStr.Split(",");
string purviewListstr = String.Empty;
foreach (var item in purviewList)
{
if (String.IsNullOrEmpty(purviewListstr))
{
purviewListstr += "('" + empid + "','" + item + "')";
}
else
{
purviewListstr += ",('" + empid + "','" + item + "')";
}
}
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "insert into wapEmpUserMap(empID,userName) values " + purviewListstr;
int count = cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
if (count >= 1)
{
return("用户添加成功");
}
else
{
return("用户添加失败");
}
}
public DataTable GetAllPlanData()
{
DataTable table = new DataTable();
JObject SQLWorkOrderFileds = PMAppSettings.TableFileds.SelectToken("SQLWorkPlanFiled").ToObject <JObject>();
string SQLWorkPlanFiled = "";
foreach (var item in SQLWorkOrderFileds)
{
if (string.IsNullOrEmpty(SQLWorkPlanFiled))
{
SQLWorkPlanFiled += item.Key;
}
else
{
SQLWorkPlanFiled += "," + item.Key;
}
}
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select " + SQLWorkPlanFiled + " from View_WorkPlansBars where WorkPlanID in (select WorkPlanID from PMS_WorkPlans where Status = 'Released' and sysid = '" + PMUser.UserSysID + "')";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
foreach (var item in SQLWorkOrderFileds)
{
table.Columns[item.Key].ColumnName = item.Value.Value <string>();
}
return(table);
}
public void Add_user(JObject data)
{
//增加用户
string username = data["username"].ToString().Trim();
string pwd = data["password"].ToString().Trim();
string name = data["name"].ToString().Trim();
string phone = data["phone"].ToString().Trim();
string email = data["email"].ToString().Trim();
MD5 md5 = MD5.Create();
//PMStaticModels.UserModels.PMUser.UserSysID
pwd += username;
byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(pwd));
string password = "";
for (int i = 0; i < s.Length; i++)
{
// 将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
password += s[i].ToString("X");
}
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "insert into wapEmpList(empID,empName,password,phoneNum,email,sysID) values('" + username + "','" + name + "','" + password + "','" + phone + "','" + email + "','" + PMUser.UserSysID + "')";
cmd.ExecuteScalar();
cmd.Connection.Dispose();
}
//查看用户
public ActionResult <DataTable> Get_All_Users(int count)
{
DataTable table = new DataTable();
//string[] Authority = null;
if (count == 1)
{
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select top 10 empID,empName,phoneNum,email from wapEmpList";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
}
else
{
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "declare @m int = " + count + "; declare @n int = 8; select top(@n) empID,empName,phoneNum,email from wapEmpList where empID not in(select top(@m - 1) empID from wapEmpList)";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
}
//for (int i = 0; i < table.Rows.Count; i++)
//{
// Authority.Append(table.Rows[i]["empID"].ToString());
//}
return(table);
}
internal static void AddCommands(Assembly assembly)
{
List <MethodInfo> commands = assembly.GetTypes().SelectMany(t => t.GetMethods()).Where(m => m.GetCustomAttributes().OfType <PMCommand>().Any()).ToList();
foreach (MethodInfo command in commands)
{
PMCommand pmCommand = (PMCommand)command.GetCustomAttribute(typeof(PMCommand));
if (allCommands.ContainsKey(pmCommand.name) || oldCommands.ContainsKey(pmCommand.name))
{
Base.Warn($"Plugin {assembly.GetName().Name} tried to register pre-existing command {pmCommand.name}");
}
else
{
allCommands.Add(pmCommand.name, command);
}
PMAlias pmAlias = (PMAlias)command.GetCustomAttribute(typeof(PMAlias));
if (pmAlias != null)
{
foreach (string alias in pmAlias.alias)
{
if (!allCommands.ContainsKey(alias) && !oldCommands.ContainsKey(alias))
{
allCommands.Add(alias, command);
}
else
{
Base.Warn($"Plugin {assembly.GetName().Name} tried to register pre-existing command {alias}");
}
}
}
}
}
public PipeMessage(int pid, PMCommand cmd, byte data)
{
this.PID = pid;
this.Cmd = cmd;
this.Data = new byte[]
{
data
}.Concat(Enumerable.Repeat <byte>(0, 4229)).ToArray <byte>();
}
public int Get_user(string username)
{
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select count(*) from wapEmpList where empID = '" + username + "'";
int counts = (int)cmd.ExecuteScalar();
cmd.Connection.Dispose();
return(counts);
}
public PipeMessage(int pid, PMCommand cmd, byte[] data)
{
this.PID = pid;
this.Cmd = cmd;
if (data.Length > 4230)
{
throw new ArgumentException("Buffer too small", "data");
}
this.Data = data.Concat(Enumerable.Repeat <byte>(0, 4230 - data.Length)).ToArray <byte>();
}
public int ForceLogout(string empID)
{
SqlCommand cmd = PMCommand.CtrlCmd();
PMUser.UserName = GetempName(empID);
cmd.CommandText = "delete from wapUserstate where empID = '" + empID + "'";
int state = cmd.ExecuteNonQuery();
PMPublicFuncs.WriteLogs(empID, PMUser.UserName, PMUser.UserIpAdress, "强制登出", DateTime.Now, "用户选择强制登出。", PMUser.UserWeb);
return(state);
}
public DataTable Getlist()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.CtrlCmd();
cmd.CommandText = "select * from wapUserlog";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
return(table);
}
public ActionResult <DataTable> ParseMessage()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select eventType from wapEventRep";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
return(table);
}
public ActionResult <DataTable> UserMessage()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select empName,phoneNum,sysID,email from wapEmpList where empID='" + PMUser.EmpID + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
return(table);
}
public ActionResult <DataTable> GetCurProduct(string res_name)
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select * from wapMesEventRec where ResName='" + res_name + "' and EventName='EndSetup'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
return(table);
}
public ActionResult <DataTable> Get_Tag_Event(string res_name)
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select * from wapMesEventRec where ResName = '" + res_name + "' and MesDate <= '" + DateTime.Now.Date + "' order by PMUID desc";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
return(table);
}
/// <summary>
/// 获取辅助资源使用情况
/// </summary>
/// <returns></returns>
public static DataTable GetScndUsuageData()
{
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select scndResName,scndResType,allQty,useQty,startDateTime,endDateTime from stsScndUsuage where workplanid = '" + Workplaninfo.WorkPlanId + "' order by scndResName,startDateTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable();
da.Fill(data);
da.Dispose();
cmd.Connection.Dispose();
return(data);
}
public ActionResult <string> GetMenu()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select * from STS_MainMenu";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
return("");
}
public ActionResult <DataTable> GetMenu()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "SELECT resName,ViewName FROM View_PmViewGroup where sysID = '" + PMUser.UserSysID + "' and VGlobal = 'export'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
return(table);
}
public static DataTable GetUseDate()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select distinct useDate from stsMainResUsuage order by useDate";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
return(table);
}
public static DataTable GetAllResUsuage()
{
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select mainResName,useDate,dayAllWorkHour,dayPlanWorkHour,dayResRatio from stsMainResUsuage where workplanid = '" + PlanModels.Workplaninfo.WorkPlanId + "' and mainResName in (select resname from View_PmViewGroup where sysid = '33' and ViewName = 'AllBT')";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable();
da.Fill(data);
da.Dispose();
cmd.Connection.Close();
return(data);
}
public static void DeleteUser()
{
SqlCommand cmd = PMCommand.CtrlCmd();
cmd.CommandText = "delete from wapUserstate where Empid = '" + PMUser.EmpID + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
WriteLogs(PMUser.EmpID, PMUser.UserName, PMUser.UserIpAdress, "用户退出", DateTime.Now, "用户正常退出。", PMUser.UserWeb);
PMUser.EmpID = string.Empty;
PMUser.UserPass = string.Empty;
PMUser.UserIpAdress = string.Empty;
PMUser.UserWeb = string.Empty;
PMUser.UserName = string.Empty;
PMUser.UserGuid = string.Empty;
}
public static string GetuserGuid(string empID)
{
SqlCommand cmd = PMCommand.CtrlCmd();
cmd.Parameters.Add("@EmpID", SqlDbType.VarChar).Value = empID;
cmd.CommandText = "select userGuid from wapUserstate where empID = @EmpID";
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
string userguid = rd[0].ToString();
rd.Close();
cmd.Connection.Dispose();
return(userguid);
}
public static void WriteLogs(string empID, string empName, string ipaddress, string model, DateTime time, string message, string webinfo)
{
//写入log
SqlCommand cmd = PMCommand.CtrlCmd();
cmd.Parameters.Add("@empID", SqlDbType.VarChar).Value = empID;
cmd.Parameters.Add("@empName", SqlDbType.VarChar).Value = empName;
cmd.Parameters.Add("@ipaddress", SqlDbType.VarChar).Value = ipaddress;
cmd.Parameters.Add("@model", SqlDbType.VarChar).Value = model;
cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = time;
cmd.Parameters.Add("@message", SqlDbType.VarChar).Value = message;
cmd.Parameters.Add("@webinfo", SqlDbType.VarChar).Value = webinfo;
cmd.CommandText = "insert into wapUserlog (empID,empName,ipAddress,model,logtime,logmessage,webinfomation) values (@empID,@empName,@ipaddress,@model,@time,@message,@webinfo)";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
/// <summary>
/// 获取设备稼动率的方法
/// </summary>
/// <param name="resName">设备名称</param>
/// <returns>DataTable</returns>
public static DataTable GetResUsuage(string resName)
{
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select useDate,dayAllWorkHour,dayPlanWorkHour,dayResRatio from stsMainResUsuage where workplanid = '" + PlanModels.Workplaninfo.WorkPlanId + "' and mainResName = '" + resName + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable()
{
TableName = resName
};
da.Fill(data);
da.Dispose();
cmd.Connection.Close();
return(data);
}
/// <summary>
/// 获取设备组下的设备名称
/// </summary>
/// <param name="resGroup">设备组名</param>
/// <returns>DataTable</returns>
public static DataTable GetResList(string resGroup)
{
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select resname from View_PmViewGroup where sysid = '" + UserModels.PMUser.UserSysID + "' and ViewName = '" + resGroup + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable()
{
TableName = resGroup
};
da.Fill(data);
da.Dispose();
cmd.Connection.Close();
return(data);
}
public string GetempName(string empid)
{
string empname = string.Empty;
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select empName from wapEmpList where empID = '" + empid + "'";
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
empname = rd["empName"].ToString();
}
rd.Close();
cmd.Connection.Close();
return(empname);
}
public ActionResult <DataTable> GetPlan(string resname)
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
//string today = DateTime.Now.ToString("yyyy-MM-dd");
//string tomorrow = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
string today = "2020-03-30"; //测试数据
string tomorrow = "2020-04-23"; //测试数据
DateTime dt = DateTime.Now.AddDays(1);
cmd.CommandText = "SELECT planStartTime,planendtime,workID,pmOpName,finishedQty,TaskFinishState,itemAttr1,itemAttr2,itemAttr3,itemAttr4,productID,pmResName,jobQty,plannedqty,AllFinishedQty,dayShift FROM User_MesDailyData where pmResName='" + resname + "' and sysID = '" + PMUser.UserSysID + "' and planStartTime >= '" + today + "' and planStartTime < '" + tomorrow + "' and datatype = 'P' ORDER BY planStartTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
return(table);
}
public JObject MGetHistoryData()
{
JObject obj = PMAppSettings.TableFileds.SelectToken("HistoryTableFiled").ToObject <JObject>();
string sqlStr = "";
DataTable table = new DataTable();
foreach (var item in obj)
{
if (string.IsNullOrEmpty(sqlStr))
{
sqlStr += item.Key;
}
else
{
sqlStr += "," + item.Key;
}
}
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "SELECT " + sqlStr + " from wapMesEventRec order by EventTime desc";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
foreach (var item in obj)
{
table.Columns[item.Key].ColumnName = item.Value.Value <string>();
}
JObject tableData = new JObject {
{ "total", table.Rows.Count },
{ "rows", JsonConvert.SerializeObject(table) }
};
//tableData.Add("total", dt.Rows.Count);
//tableData.Add("rows", JsonConvert.SerializeObject(dt));
JObject data = new JObject {
{ "code", "0" },
{ "data", tableData },
{ "msg", "successful" }
};
return(data);
}
public static string GetPmViewGroupTable(string value)
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "SELECT resName FROM View_PmViewGroup where sysID ='" + PMStaticModels.UserModels.PMUser.UserSysID + "' and ViewName = '" + value + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
if (table.Rows.Count == 0)
{
return("视图中没有设备");
}
else
{
return(PMPublicFuncs.DatatableToJson(table));
}
}
/// <summary>
/// 获取设备组
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetResGroup()
{
SqlCommand cmd = PMCommand.SchCmd();
cmd.CommandText = "select DISTINCT ViewName from View_PmViewGroup where sysid = '" + UserModels.PMUser.UserSysID + "' and VGlobal = 'export'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable();
da.Fill(data);
da.Dispose();
if (data.Rows.Count < 1)
{
cmd.CommandText = "select DISTINCT ViewName from View_PmViewGroup where sysid = '" + UserModels.PMUser.UserSysID + "'";
da = new SqlDataAdapter(cmd);
da.Fill(data);
da.Dispose();
}
cmd.Connection.Close();
return(data);
}
