public bool PauseOrder_Call(string jsonedata)
{
COrderList mesEvent = JsonConvert.DeserializeObject <COrderList>(jsonedata);
//写队列
WriteMesEvent writeMes = new WriteMesEvent();
if (writeMes.WriteMesEvent_Call(mesEvent, 3))
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET TaskFinishState = '3',MesResName = '" + mesEvent.MesResName + "', MesOpName = '" + mesEvent.MesOpName + "', bgPerson = '" +
mesEvent.MesOperator + "', updateDateTime = '" + DateTime.Now + "' WHERE UID = '" + mesEvent.OrderUID + "'";
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
public List <CFinishHistory> GetOpFinishHistory(string orderuid)
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT eventMessage,eventTime,finishedQty,failedQty,planQty,jobQty,mesOperator FROM wapMesEventRec WHERE OrderUID = '" + orderuid + "' ORDER BY EventTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dthistory = new DataTable();
da.Fill(dthistory);
da.Dispose();
cmd.Connection.Close();
List <CFinishHistory> cFinishHistories = new List <CFinishHistory>();
foreach (DataRow item in dthistory.Rows)
{
CFinishHistory cFinish = new CFinishHistory
{
EventMessage = item["eventMessage"].ToString(),
EventTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(item["eventTime"]), 1),
FinishedQty = Convert.ToDouble(item["finishedQty"]),
FailedQty = Convert.ToDouble(item["failedQty"]),
PlannedQty = Convert.ToDouble(item["planQty"]),
JobQty = Convert.ToDouble(item["jobQty"]),
MesOperator = item["mesOperator"].ToString()
};
cFinishHistories.Add(cFinish);
}
return(cFinishHistories);
}
private bool IsAdmin(int empid, int userSysID)
{
SqlCommand cmd = PmConnections.ModCmd();
cmd.CommandText = "SELECT userName,shopUSergroupid,sysid FROM wapUser WHERE userName in (SELECT USERNAME FROM wapEmpUserMap WHERE empid = '" + empid + "') and sysid = '" + userSysID + "'";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dtusergroup = new DataTable();
adapter.Fill(dtusergroup);
adapter.Dispose();
bool isadmin = false;
if (dtusergroup.Rows.Count > 0)
{
foreach (DataRow item in dtusergroup.Rows)
{
if (item["shopUSergroupid"].ToString().ToUpper() != "ADMIN")
{
continue;
}
else
{
isadmin = true;
break;
}
}
}
else
{
isadmin = false;
}
return(isadmin);
}
public CLogin ForceOut(string userName, string userpass)
{
PublicFunc.DeleteUserResLock(GetUpdateVal());
SqlCommand cmd = PmConnections.CtrlCmd();
cmd.CommandText = "SELECT online FROM wapUserstate WHERE empid = '" + GetUpdateVal() + "'";
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
if (Convert.ToBoolean(rd[0]))
{
rd.Close();
cmd.CommandText = "DELETE FROM wapUserstate WHERE empid = '" + GetUpdateVal() + "'";
cmd.ExecuteNonQuery();
}
rd.Close();
cmd.Connection.Close();
return(Login(userName, userpass));
}
else
{
return(Login(userName, userpass));
}
}
public void SetResUnused(string resName, string useType)
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "DELETE FROM wapResLockState WHERE ResName = '" + resName + "' and ResEventType = '" + useType + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
//查询是否有其他订单是否换班
private void EndOtherOrder(string resname)
{
MUnStartList unStartList = new MUnStartList();
List <COrderList> cOrders = unStartList.GetUnStartOrderList(resname);
if (cOrders.Count > 0)
{
foreach (COrderList item in cOrders)
{
//查询下一个班次是哪天,第几个班次
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT MAX(dayShift) FROM User_MesDailyData WHERE pmResName = '" + item.PmResName + "' and mesDailyDate ='" + PublicFunc.GetDailyDate(thisresname) + "'";
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
int maxdayshift = Convert.ToInt32(rd[0]);
rd.Close();
cmd.Connection.Close();
int nextdayshift;
DateTime nextdailydate;
if (item.DayShift < maxdayshift)
{
nextdayshift = item.DayShift + 1;
nextdailydate = PublicFunc.GetDailyDate(thisresname);
}
else
{
nextdayshift = 1;
nextdailydate = PublicFunc.GetDailyDate(thisresname).AddDays(1);
}
//先查找这个设备上的下个班次是否有这个订单
cmd = PmConnections.SchCmd();
//SELECT COUNT(UID) FROM [sch_test].[dbo].[User_MesDailyData] where pmResName = '纽威立式车床17:2' and mesDailyDate = '2020-6-20' and workID = '0021905001' and dayShift = 1 and productID = '8311280776' and pmOpName = '精车一'
cmd.CommandText = "SELECT COUNT(UID) FROM User_MesDailyData WHERE pmResName = '" + thisresname + "' and mesDailyDate = '" + nextdailydate + "' and dayshift = '" + nextdayshift +
"' and workid = '" + item.WorkID + "' and productid = '" + item.ProductID + "' and pmOpName = '" + item.PmOpName + "'";
rd = cmd.ExecuteReader();
rd.Read();
int hasthisorder = Convert.ToInt32(rd[0]);
rd.Close();
cmd.Connection.Close();
if (hasthisorder < 1)
{
cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET mesDailyDate = '" + nextdailydate + "', dayshift = '" + nextdayshift + "' WHERE UID = '" + item.OrderUID + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
else
{
cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET TaskFinishState = '5' WHERE UID = '" + item.OrderUID + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
}
public bool Report_Call(string jsonedata)
{
COrderList mesEvent = JsonConvert.DeserializeObject <COrderList>(jsonedata);
WriteMesEvent writeMes = new WriteMesEvent();
if (writeMes.WriteMesEvent_Call(mesEvent, 2))
{
//更新mesdailydata
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT finishedQty,AllfinishedQty,AllJobTaskQty FROM User_MesDailyData WHERE UID = '" + mesEvent.OrderUID + "'";
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
double serverallfinishedQty = Convert.ToDouble(rd[1]);
double serverfinishedqty = Convert.ToDouble(rd[0]);
double alljobtaskqty = Convert.ToDouble(rd[2]);
rd.Close();
int taskfinishedstate;
if ((serverallfinishedQty + mesEvent.FinishedQty + mesEvent.FailedQty) == alljobtaskqty)
{
taskfinishedstate = 4;
}
else
{
if ((serverfinishedqty + mesEvent.FinishedQty + mesEvent.FailedQty) >= mesEvent.Plannedqty)
{
taskfinishedstate = 4;
}
else
{
taskfinishedstate = 2;
}
}
cmd.CommandText = "UPDATE User_MesDailyData SET mesResName = '" + mesEvent.MesResName + "', mesOpName = '" + mesEvent.MesOpName + "',finishedQty = finishedQty + '" + (mesEvent.FinishedQty + mesEvent.FailedQty) +
"', failQty = failQty + '" + mesEvent.FailedQty + "', endDateTime = '" + mesEvent.ReportTime + "', updateDateTime = '" + DateTime.Now + "', bgPerson = '" + mesEvent.MesOperator +
"',ScrappedQty = ScrappedQty + '" + mesEvent.ScrappedQty + "', TaskFinishState = '" + taskfinishedstate + "',UserComment = '" + mesEvent.UserComment + " ' WHERE UID = '" + mesEvent.OrderUID + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE User_MesDailyData SET AllFinishedQty = AllFinishedQty + '" + (mesEvent.FinishedQty + mesEvent.FailedQty) + "', updateDateTime ='" + DateTime.Now + "' WHERE workID = '" + mesEvent.WorkID +
"' and productID = '" + mesEvent.ProductID + "' and pmOpName = '" + mesEvent.PmOpName + "'";
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
public void SetResUsed(string empid, string resName, string useType, string lockedStartTime, string lockedEndTime, string eventMessage)
{
int pmuid = PublicFunc.GetMaxUID("S", "wapResLockState", "PMUID");
string username = PublicFunc.GetEmpName(Convert.ToInt32(empid));
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "INSERT INTO wapResLockState (PMUID,ResName,LockedStartTime,LockedEndTime,LockedPerson,ResEventType,ResEventComment) VALUES ('"
+ pmuid + "','" + resName + "','" + lockedStartTime + "','" + lockedEndTime + "','" + username + "','" + useType + "','" + eventMessage + "')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
public DataTable Operate(string empName)
{
DataTable table = new DataTable();
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "select * from wapMesEventRec where MesOperator = '" + empName + "'";
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(table);
ad.Dispose();
cmd.Connection.Close();
return(table);
}
public List <string> GetCanResList_Call(string jsonedata)
{
COrderList mesEvent = JsonConvert.DeserializeObject <COrderList>(jsonedata);
List <string> canuseres = new List <string>();
SqlCommand cmd = PmConnections.ModCmd();
string thisresname = mesEvent.MesResName;
string thisopname = mesEvent.PmOpName;
string thisproduct = mesEvent.ProductID;
string usersysid = PmUser.UserSysID.ToString();
cmd.CommandText = "SELECT resourceName,capacity FROM vResPrcsOpItem WHERE itemname ='" + thisproduct + "' and operationName = '" + thisopname + "' and sysid = '" + usersysid + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable canress = new DataTable();
da.Fill(canress);
da.Dispose();
cmd.Connection.Close();
foreach (DataRow dr in canress.Rows)
{
int rescount = Convert.ToInt32(dr[1]);
string resname = dr[0].ToString();
if (resname == thisresname)
{
continue;
}
if (rescount > 1)
{
for (int i = 1; i <= rescount; i++)
{
if (resname + ":" + i.ToString() == thisresname)
{
continue;
}
else
{
canuseres.Add(resname + ":" + i.ToString());
}
}
}
else if (rescount == 0)
{
continue;
}
else
{
canuseres.Add(resname);
}
}
return(canuseres);
}
public Boolean SignOut(string empid)
{
SqlCommand cmd = PmConnections.CtrlCmd();
cmd.CommandText = "delete from wapUserstate where Empid = '" + empid + "'";
int count = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (count > 0)
{
return(true);
}
return(false);
}
public bool CanChangePass(string empid, string password)
{
SqlCommand cmd = PmConnections.ModCmd();
cmd.CommandText = "select count(*) from wapEmpList where empID = '" + empid + "' and password = '******'";
int count = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
if (count > 0)
{
return(true);
}
return(false);
}
public Boolean HasLogin(string userName, string userGuid)
{
SqlCommand cmd = PmConnections.CtrlCmd();
cmd.CommandText = "select count(empID) from wapUserstate where empID = '" + userName + "' and userGuid = '" + userGuid + "'";
//int count = Convert.ToInt32(cmd.ExecuteReader());
int count = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
if (count > 0)
{
return(true);
}
return(false);
}
public bool UpdateUserinfo(string userinfo)
{
CUserInfo mesEvent = JsonConvert.DeserializeObject <CUserInfo>(userinfo);
SqlCommand cmd = PmConnections.ModCmd();
cmd.CommandText = "UPDATE wapEmpList SET phoneNum = '" + mesEvent.PhoneNumber + "',email = '" + mesEvent.Email + "' WHERE SYSID = '" + mesEvent.UserSysID + "' and empid = '" + mesEvent.EmpID + "'";
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool BeginDown_Call(string orderuid, string resName, string dayshift)
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET pmresname = '" + resName + "', updateDatetime ='" + DateTime.Now + "', bgPerson = '" + PmUser.UserName + "',dayShift = '" + dayshift + "',mesdailydate = '" + DateTime.Now.Date + "' WHERE UID = '" + orderuid + "'";
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool OrderAjustmest_Call(string jsonEdata)
{
COrderList mesEvent = JsonConvert.DeserializeObject <COrderList>(jsonEdata);
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET adjustment = '1' WHERE UID = '" + mesEvent.OrderUID + "'";
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
public DataTable GetUserLog(string empid, int logtype)
{
DataTable table = new DataTable();
SqlCommand cmd = PmConnections.CtrlCmd();
DateTime start;
DateTime end;
if (logtype == 0)
{
end = DateTime.Now.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
start = DateTime.Now.Date;
cmd.CommandText = "select * from wapUserlog where empID = '" + empid + "'and logTime >= '" + start + "' and logTime <= '" + end + "' ORDER BY logTime DESC";
}
else if (logtype == 1)
{
end = DateTime.Now.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
start = DateTime.Now.Date.AddDays(-3);
cmd.CommandText = "select * from wapUserlog where empID = '" + empid + "' and logTime >= '" + start + "' and logTime <= '" + end + "' ORDER BY logTime DESC";
}
else if (logtype == 2)
{
end = DateTime.Now.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
start = DateTime.Now.Date.AddDays(-7);
cmd.CommandText = "select * from wapUserlog where empID = '" + empid + "' and logTime >= '" + start + "' and logTime <= '" + end + "' ORDER BY logTime DESC";
}
else if (logtype == 3)
{
end = DateTime.Now.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
start = DateTime.Now.Date.AddMonths(-1);
cmd.CommandText = "select * from wapUserlog where empID = '" + empid + "' and logTime >= '" + start + "' and logTime <= '" + end + "' ORDER BY logTime DESC";
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
for (int i = 0; i < table.Rows.Count; i++)
{
string mobietype = CreateMobelType(table.Rows[i]["webinfomation"].ToString());
table.Rows[i]["webinfomation"] = mobietype;
}
table.AcceptChanges();
return(table);
}
public CUserInfo GetInformation(string localGuid, string userEmpid)
{
CUserInfo cUser = new CUserInfo();
SqlCommand cmd = PmConnections.CtrlCmd();
cmd.CommandText = "SELECT userGUID FROM wapUserstate WHERE empID = '" + userEmpid + "'";
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
//可能存在BUG
string serverUserguid = rd[0].ToString();
if (serverUserguid == localGuid)
{
cUser.Email = PmUser.Email;
cUser.EmpID = PmUser.EmpID;
cUser.EmpWorkID = PmUser.EmpWorkID;
cUser.FunctionList = PmUser.FunctionList;
cUser.IsAdmin = PmUser.IsAdmin;
cUser.PhoneNumber = PmUser.PhoneNumber;
cUser.UsercusID = PmUser.UsercusID;
cUser.UserDesc = PmUser.UserDesc;
cUser.UserGuid = PmUser.UserGuid;
cUser.UserIpAdress = PmUser.UserIpAdress;
cUser.UserName = PmUser.UserName;
cUser.UserShopUserGroupID = PmUser.UserShopUserGroupID;
cUser.UserSysID = PmUser.UserSysID;
cUser.UserSysName = PmUser.UserSysName;
cUser.UserAgent = PmUser.UserAgent;
}
else
{
cUser.AlertMessage = "请不要越权操作!";
}
}
else
{
cUser.AlertMessage = "请不要越权操作!";
}
return(cUser);
}
public Boolean ChangePass(string empid, string oldpass, string newPass)
{
string pass = GetSafePass(empid, newPass);
string olpass = GetSafePass(empid, oldpass);
Boolean canChange = CanChangePass(empid, olpass);
if (canChange)
{
SqlCommand cmd = PmConnections.ModCmd();
cmd.CommandText = "update wapEmpList set password ='******' where empID = '" + empid + "'";
int count = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (count > 0)
{
return(true);
}
return(false);
}
else
{
return(false);
}
}
public bool Resume_Call(string jsonedata)
{
COrderList mesEvent = JsonConvert.DeserializeObject<COrderList>(jsonedata);
WriteMesEvent writeMes = new WriteMesEvent();
if(writeMes.WriteMesEvent_Call(mesEvent,4))
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET TaskFinishState = '0' WHERE UID = '" + mesEvent.OrderUID + "'";
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result == 1)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
public bool ChangeResource_Call(string jsonedata)
{
COrderList mesEvent = JsonConvert.DeserializeObject <COrderList>(jsonedata);
WriteMesEvent writeMes = new WriteMesEvent();
if (writeMes.WriteMesEvent_Call(mesEvent, 5))
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET pmResName = '" + mesEvent.ChangeResName + "' WHERE UID = '" + mesEvent.OrderUID + "'";
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
/// <summary>
/// 执行写入mes事件队列的方法
/// </summary>
/// <param name="edata">api接收到的数据</param>
/// <param name="writetype">0:开始切换;1:结束切换;2:报工;3:暂停;4:恢复生产;5:切换设备;6:换班</param>
/// <returns>SQL执行结果bool值</returns>
public bool WriteMesEvent_Call(COrderList edata, int writetype)
{
SqlCommand cmd = PmConnections.SchCmd();
int maxUID = PublicFunc.GetMaxUID("S", "wapMesEventRec", "PMUID");
if (writetype == 0)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID) VALUES ('" +
maxUID + "','S','StartChange','开始切换','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID + "','" + edata.ItemDesp +
"','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty + "','" + edata.DayShift +
"','" + edata.Plannedqty + "','" + edata.OrderUID + "')";
}
else if (writetype == 1)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID) VALUES ('" +
maxUID + "','E','EndChange','结束切换','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID + "','" + edata.ItemDesp +
"','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty + "','" + edata.DayShift +
"','" + edata.Plannedqty + "','" + edata.OrderUID + "')";
}
else if (writetype == 2)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID,MesAttr1) VALUES ('" +
maxUID + "','R','Report','报工','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID + "','" + edata.ItemDesp +
"','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty + "','" + edata.DayShift +
"','" + edata.Plannedqty + "','" + edata.OrderUID + "','" + edata.UserComment + "')";
}
else if (writetype == 3)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID) VALUES ('" +
maxUID + "','P','Pause','暂停生产','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID + "','" + edata.ItemDesp +
"','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty + "','" + edata.DayShift +
"','" + edata.Plannedqty + "','" + edata.OrderUID + "')";
}
else if (writetype == 4)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID) VALUES ('" +
maxUID + "','U','Resume','恢复生产','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID + "','" + edata.ItemDesp +
"','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty + "','" + edata.DayShift +
"','" + edata.Plannedqty + "','" + edata.OrderUID + "')";
}
else if (writetype == 5)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID) VALUES ('" +
maxUID + "','C','ChangeRes','推送订单到=>" + edata.ChangeResName + "','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID
+ "','" + edata.ItemDesp + "','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty
+ "','" + edata.DayShift + "','" + edata.Plannedqty + "','" + edata.OrderUID + "')";
}
else if (writetype == 6)
{
cmd.CommandText = @"INSERT INTO wapMesEventRec
(PMUID,EventType,EventName,EventMessage,EventTime,ResName,WorkID,OpName,ProductID,Description,JobQty,FinishedQty,MesDate,MesOperator,FailedQty,ScrappedQty,DayShift,PlanQty,OrderUID) VALUES ('" +
maxUID + "','EW','EndWorking','换班','" + DateTime.Now + "','" + edata.MesResName + "','" + edata.WorkID + "','" + edata.MesOpName + "','" + edata.ProductID
+ "','" + edata.ItemDesp + "','" + edata.JobQty + "','" + edata.FinishedQty + "','" + DateTime.Now.Date + "','" + edata.MesOperator + "','" + edata.FailedQty + "','" + edata.ScrappedQty
+ "','" + edata.DayShift + "','" + edata.Plannedqty + "','" + edata.OrderUID + "')";
}
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
public List <COrderList> CanDownThisRes_Call(string resname, int dayshift)
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE resCanUsed like '%" + resname.Split(':')[0] + "%' and datatype = 'P' and dailyDate >='" + DateTime.Now + "' and TaskFinishState < '4' ORDER BY workid,productid,pmresname,pmopname,PlanStartTime Desc";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable();
da.Fill(data);
da.Dispose();
cmd.Connection.Close();
//获取现有设备上的工单数据
cmd.CommandText = "SELECT pmresname,pmopname,workid FROM User_MesDailyData WHERE pmresname = '" + resname + "' and datatype ='P' and taskfinishstate < 4 and dailydate = '" + DateTime.Now.Date + "' and dayshift = '" + dayshift + "'";
da = new SqlDataAdapter(cmd);
DataTable dtthisrestasks = new DataTable();
da.Fill(dtthisrestasks);
da.Dispose();
cmd.Connection.Close();
//对拉取过来的数据去重
DataTable newdata = data.Clone();
string thisworkid, preworkid = string.Empty;
string thisproduct, preproduct = string.Empty;
string thisresname, preresname = string.Empty;
string thisopname, preopname = string.Empty;
for (int i = 0; i < data.Rows.Count; i++)
{
if (i == 0)
{
preworkid = data.Rows[i]["workid"].ToString();
preproduct = data.Rows[i]["productid"].ToString();
preresname = data.Rows[i]["pmresname"].ToString();
preopname = data.Rows[i]["pmopname"].ToString();
continue;
}
else
{
thisworkid = data.Rows[i]["workid"].ToString();
thisproduct = data.Rows[i]["productid"].ToString();
thisresname = data.Rows[i]["pmresname"].ToString();
thisopname = data.Rows[i]["pmopname"].ToString();
DataRow[] finddr = dtthisrestasks.Select("pmresname = '" + data.Rows[i]["pmresname"].ToString() + "' and pmopname = '"
+ data.Rows[i]["pmopname"].ToString() + "' and workid = '" + data.Rows[i]["workid"].ToString() + "'");
if (finddr.Count() > 0)
{
continue;
}
if (thisworkid == preworkid)
{
//工单一样,比较产品是否一样
if (thisproduct == preproduct)
{
//产品一样 比较设备是否一样
if (thisresname == preresname)
{
//如果设备一样,比较工序是否一样
if (thisopname == preopname)
{
continue;
}
else
{
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
}
else
{
//设备不一样 添加
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
}
else
{
//如果产品不一样,添加
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
}
else
{
//工单不一样,直接添加
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
preworkid = thisworkid;
preproduct = thisproduct;
preresname = thisresname;
preopname = thisopname;
}
}
DataView dv = newdata.DefaultView;
dv.Sort = "PlanStartTime ASC";
newdata = dv.ToTable();
List <COrderList> orderlist = new List <COrderList>();
if (newdata.Rows.Count > 0)
{
foreach (DataRow item in newdata.Rows)
{
DataRow checkeddata = PublicFunc.CheckEmptyVal(data, item);
COrderList cOrder = new COrderList()
{
MesResName = checkeddata["MesResName"].ToString(),
MesOpName = checkeddata["MesOpName"].ToString(),
MesOperator = checkeddata["bgPerson"].ToString(),
WorkID = checkeddata["WorkID"].ToString(),
PlanStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["PlanStartTime"]), 1),
Planendtime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["Planendtime"]), 1),
PmResName = checkeddata["PmResName"].ToString(),
PmOpName = checkeddata["PmOpName"].ToString(),
ProductID = checkeddata["ProductID"].ToString(),
TaskFinishState = Convert.ToInt32(checkeddata["TaskFinishState"]),
FinishedQty = Convert.ToDouble(checkeddata["FinishedQty"]),
Plannedqty = Convert.ToDouble(checkeddata["Plannedqty"]),
FailedQty = Convert.ToDouble(checkeddata["FailQty"]),
AllFinishedQty = Convert.ToDouble(checkeddata["AllFinishedQty"]),
JobQty = Convert.ToDouble(checkeddata["JobQty"]),
ItemAttr1 = checkeddata["ItemAttr1"].ToString(),
ItemAttr2 = checkeddata["ItemAttr2"].ToString(),
ItemAttr3 = checkeddata["ItemAttr3"].ToString(),
ItemAttr4 = checkeddata["ItemAttr4"].ToString(),
DayShift = Convert.ToInt32(checkeddata["DayShift"]),
ItemDesp = checkeddata["itemDesp"].ToString(),
WorkHours = Convert.ToDouble(checkeddata["workHour"]),
SetupTime = Convert.ToDouble(checkeddata["setupTime"]),
OrderUID = Convert.ToInt32(checkeddata["UID"]),
BomComused = Convert.ToDouble(checkeddata["AllJobTaskqty"]) / Convert.ToDouble(checkeddata["JobQty"]),
CanReport = true,
CanReportQty = Convert.ToDouble(checkeddata["AllJobTaskqty"]) - Convert.ToDouble(checkeddata["AllFinishedQty"]),
ChangeResName = string.Empty
};
orderlist.Add(cOrder);
}
bool hasoutput = false;
foreach (COrderList c in orderlist)
{
if (c.TaskFinishState != 0 && c.TaskFinishState != 3)
{
hasoutput = true;
}
else
{
continue;
}
}
if (hasoutput == true)
{
foreach (COrderList c in orderlist)
{
if (c.TaskFinishState != 0)
{
c.CanReport = true;
}
else
{
c.CanReport = false;
}
}
}
}
//处理重复的数据
return(orderlist);
}
public COpFinish GetOpFinishedData(string jsonedata)
{
COrderList order = JsonConvert.DeserializeObject <COrderList>(jsonedata);
SqlCommand cmd = PmConnections.SchCmd();
//获取计划数据
cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE UID = '" + order.OrderUID + "'";
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
DateTime planstarttime = Convert.ToDateTime(rd["planStartTime"]);
DateTime planendtime = Convert.ToDateTime(rd["planEndTime"]);
double plannedsetuphours = Convert.ToDouble(rd["setupTime"]);
int setuptime = Convert.ToInt32(plannedsetuphours * 60);
double plannedqty = Convert.ToDouble(rd["plannedQty"]);
double plannedhours = Convert.ToDouble(rd["workHour"]);
double failedqty = Convert.ToDouble(rd["failQty"]);
double finishedqty = Convert.ToDouble(rd["finishedqty"]);
rd.Close();
DateTime plansetupstarttime = planstarttime.AddMinutes(-setuptime);
DateTime plansetupendtime = planstarttime;
//获取实际数据
cmd.CommandText = "SELECT * FROM wapMesEventRec WHERE OrderUID = '" + order.OrderUID + "' order by EventTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtmesdata = new DataTable();
da.Fill(dtmesdata);
da.Dispose();
cmd.Connection.Close();
//计算数据
COpFinish returndata = new COpFinish
{
PlanStartTime = PublicFunc.ForMatDateTimeStr(planstarttime, 1),
PlanEndTime = PublicFunc.ForMatDateTimeStr(planendtime, 1),
PlannedHours = plannedhours * 3600,
PlannedQty = plannedqty,
PlanSetupStartTime = PublicFunc.ForMatDateTimeStr(plansetupstarttime, 1),
PlanSetupEndTime = PublicFunc.ForMatDateTimeStr(plansetupendtime, 1),
PlannedSetupHours = plannedsetuphours * 3600,
FinishedQty = finishedqty,
FailedQty = failedqty
};
//开始计算mes数据
//获取mes切换开始时间
double messetuphours = 0;
double meshours = 0;
for (int i = 0; i < dtmesdata.Rows.Count; i++)
{
if (dtmesdata.Rows[i]["EventType"].ToString().Equals("S"))
{
messetuphours += (Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]) - Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"])).TotalSeconds;
if (dtmesdata.Rows[i + 1]["EventType"].ToString().Equals("E"))
{
returndata.MesSetupStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"]), 1);
returndata.MesSetupEndTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]), 1);
}
}
if (dtmesdata.Rows[i]["EventType"].ToString().Equals("E"))
{
if (dtmesdata.Rows[i + 1]["EventType"].ToString().Equals("R"))
{
meshours += (Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]) - Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"])).TotalSeconds;
returndata.MesStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"]), 1);
returndata.MesEndTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]), 1);
}
}
}
returndata.MesSetupHours = Convert.ToDouble(messetuphours.ToString("0.00"));
returndata.MesHours = Convert.ToDouble(meshours.ToString("0.00"));
return(returndata);
}
public List <COrderList> GetUnStartOrderList(string resName)
{
int dayshift = PublicFunc.GetDayShift(resName);
DateTime mesdailydate = PublicFunc.GetDailyDate(resName);
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE pmResName = '" + resName + "' and datatype = 'P' and mesdailyDate ='" + mesdailydate.Date + "' and TaskFinishState < '4' and dayShift = '" + dayshift + "' ORDER BY mesdailyDate,planStartTime";
//cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE pmResName = '" + resName + "' and datatype = 'P'and TaskFinishState < '4' and dayShift = '" + shift + "'ORDER BY dailyDate,planStartTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable returndata = new DataTable();
da.Fill(returndata);
da.Dispose();
cmd.Connection.Close();
if (returndata.Rows.Count < 1)
{
return(null);
}
else
{
List <COrderList> LiReturnData = new List <COrderList>();
foreach (DataRow item in returndata.Rows)
{
DataRow checkeddata = PublicFunc.CheckEmptyVal(returndata, item);
COrderList li = new COrderList
{
MesResName = checkeddata["MesResName"].ToString(),
MesOpName = checkeddata["MesOpName"].ToString(),
MesOperator = checkeddata["bgPerson"].ToString(),
WorkID = checkeddata["WorkID"].ToString(),
PlanStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["PlanStartTime"]), 1),
Planendtime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["Planendtime"]), 1),
PmResName = checkeddata["PmResName"].ToString(),
PmOpName = checkeddata["PmOpName"].ToString(),
ProductID = checkeddata["ProductID"].ToString(),
TaskFinishState = Convert.ToInt32(checkeddata["TaskFinishState"]),
FinishedQty = Convert.ToDouble(checkeddata["FinishedQty"]),
Plannedqty = Convert.ToDouble(checkeddata["Plannedqty"]),
FailedQty = Convert.ToDouble(checkeddata["FailQty"]),
AllFinishedQty = Convert.ToDouble(checkeddata["AllFinishedQty"]),
JobQty = Convert.ToDouble(checkeddata["JobQty"]),
ItemAttr1 = checkeddata["ItemAttr1"].ToString(),
ItemAttr2 = checkeddata["ItemAttr2"].ToString(),
ItemAttr3 = checkeddata["ItemAttr3"].ToString(),
ItemAttr4 = checkeddata["ItemAttr4"].ToString(),
DayShift = Convert.ToInt32(checkeddata["DayShift"]),
ItemDesp = checkeddata["itemDesp"].ToString(),
WorkHours = Convert.ToDouble(checkeddata["workHour"]),
SetupTime = Convert.ToDouble(checkeddata["setupTime"]),
OrderUID = Convert.ToInt32(checkeddata["UID"]),
BomComused = Convert.ToDouble(checkeddata["AllJobTaskqty"]) / Convert.ToDouble(checkeddata["JobQty"]),
CanReport = true,
CanReportQty = Convert.ToDouble(checkeddata["AllJobTaskqty"]) - Convert.ToDouble(checkeddata["AllFinishedQty"]) - Convert.ToDouble(checkeddata["FailQty"]),
ChangeResName = string.Empty,
ReportTime = Convert.ToDateTime(checkeddata["updateDatetime"]),
JobDemandDay = Convert.ToDateTime(checkeddata["jobDemandDay"]),
ScrappedQty = Convert.ToDouble(checkeddata["ScrappedQty"]),
Ajustment = Convert.ToBoolean(checkeddata["adjustment"]),
UnitPrice = Convert.ToDouble(checkeddata["unitPrice"]),
MesSetupStartTime = Convert.ToDateTime(checkeddata["setupStartTime"]),
MesSetupEndTime = Convert.ToDateTime(checkeddata["setupEndTime"]),
MesStartTime = Convert.ToDateTime(checkeddata["startDateTime"]),
MesEndTime = Convert.ToDateTime(checkeddata["endDateTime"]),
UserComment = checkeddata["userComment"].ToString()
};
LiReturnData.Add(li);
}
//public DateTime MesStartTime { get; set; }
//public DateTime MesEndTime { get; set; }
//public DateTime MesSetupStartTime { get; set; }
//public DateTime MesSetupEndtime { get; set; }
bool hasoutput = false;
foreach (COrderList c in LiReturnData)
{
if (c.TaskFinishState != 0 && c.TaskFinishState != 3)
{
hasoutput = true;
}
else
{
continue;
}
}
if (hasoutput == true)
{
foreach (COrderList c in LiReturnData)
{
if (c.TaskFinishState != 0)
{
c.CanReport = true;
}
else
{
c.CanReport = false;
}
}
}
return(LiReturnData);
}
}
public bool EndWork(string jsonedata)
{
COrderList mesEvent = JsonConvert.DeserializeObject <COrderList>(jsonedata);
WriteMesEvent writeMes = new WriteMesEvent();
if (writeMes.WriteMesEvent_Call(mesEvent, 6))
{
thisresname = mesEvent.MesResName;
SqlDataReader rd;
SqlCommand cmd;
int taskfinishedstate = -1;
if (mesEvent.TaskFinishState == 2)
{
//更新mesdailydata
cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT finishedQty,AllfinishedQty,AllJobTaskQty FROM User_MesDailyData WHERE UID = '" + mesEvent.OrderUID + "'";
rd = cmd.ExecuteReader();
rd.Read();
double serverallfinishedQty = Convert.ToDouble(rd[1]);
double serverfinishedqty = Convert.ToDouble(rd[0]);
double alljobtaskqty = Convert.ToDouble(rd[2]);
rd.Close();
if ((serverallfinishedQty + mesEvent.FinishedQty + mesEvent.FailedQty) == alljobtaskqty)
{
taskfinishedstate = 4;
}
else
{
if ((serverfinishedqty + mesEvent.FinishedQty + mesEvent.FailedQty) >= mesEvent.Plannedqty)
{
taskfinishedstate = 4;
}
else
{
taskfinishedstate = 2;
}
}
cmd.CommandText = "UPDATE User_MesDailyData SET mesResName = '" + mesEvent.MesResName + "', mesOpName = '" + mesEvent.MesOpName + "',finishedQty = finishedQty + '" + (mesEvent.FinishedQty + mesEvent.FailedQty) +
"', failQty = failQty + '" + mesEvent.FailedQty + "', endDateTime = '" + mesEvent.ReportTime + "', updateDateTime = '" + DateTime.Now + "', bgPerson = '" + mesEvent.MesOperator +
"',ScrappedQty = ScrappedQty + '" + mesEvent.ScrappedQty + "', TaskFinishState = '" + taskfinishedstate + "' WHERE UID = '" + mesEvent.OrderUID + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE User_MesDailyData SET AllFinishedQty = AllFinishedQty + '" + (mesEvent.FinishedQty + mesEvent.FailedQty) + "', updateDateTime ='" + DateTime.Now + "' WHERE workID = '" + mesEvent.WorkID +
"' and productID = '" + mesEvent.ProductID + "' and pmOpName = '" + mesEvent.PmOpName + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
//查询下一个班次是哪天,第几个班次
cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT MAX(dayShift) FROM User_MesDailyData WHERE pmResName = '" + mesEvent.PmResName + "' and mesDailyDate ='" + PublicFunc.GetDailyDate(thisresname) + "'";
rd = cmd.ExecuteReader();
rd.Read();
int maxdayshift = Convert.ToInt32(rd[0]);
rd.Close();
cmd.Connection.Close();
int nextdayshift;
DateTime nextdailydate;
if (mesEvent.DayShift < maxdayshift)
{
nextdayshift = mesEvent.DayShift + 1;
nextdailydate = PublicFunc.GetDailyDate(thisresname);
}
else
{
nextdayshift = 1;
nextdailydate = PublicFunc.GetDailyDate(thisresname).AddDays(1);
}
//先查找这个设备上的下个班次是否有这个订单
cmd = PmConnections.SchCmd();
//SELECT COUNT(UID) FROM [sch_test].[dbo].[User_MesDailyData] where pmResName = '纽威立式车床17:2' and mesDailyDate = '2020-6-20' and workID = '0021905001' and dayShift = 1 and productID = '8311280776' and pmOpName = '精车一'
cmd.CommandText = "SELECT COUNT(UID) FROM User_MesDailyData WHERE pmResName = '" + thisresname + "' and mesDailyDate = '" + nextdailydate + "' and dayshift = '" + nextdayshift +
"' and workid = '" + mesEvent.WorkID + "' and productid = '" + mesEvent.ProductID + "' and pmOpName = '" + mesEvent.PmOpName + "'";
rd = cmd.ExecuteReader();
rd.Read();
int hasthisorder = Convert.ToInt32(rd[0]);
rd.Close();
cmd.Connection.Close();
if (taskfinishedstate == 4)
{
EndOtherOrder(thisresname);
return(true);
}
else
{
if (hasthisorder < 1)
{
cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET mesDailyDate = '" + nextdailydate + "', dayshift = '" + nextdayshift + "' WHERE UID = '" + mesEvent.OrderUID + "'";
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (result > 0)
{
EndOtherOrder(thisresname);
return(true);
}
else
{
return(false);
}
}
else
{
cmd = PmConnections.SchCmd();
cmd.CommandText = "UPDATE User_MesDailyData SET TaskFinishState = '5' WHERE UID = '" + mesEvent.OrderUID + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
EndOtherOrder(thisresname);
return(true);
}
}
}
else
{
return(false);
}
}
public CLogin Login(string userName, string userPass)
{
if (CUserInfo.LogedUserInfo == null)
{
CUserInfo.LogedUserInfo = new Dictionary <string, string>();
}
// 获取用户的IP地址和浏览器信息
SqlCommand cmd = PmConnections.ModCmd();
cmd.CommandText = "SELECT * FROM wapEmpList WHERE " + PmSettings.LoginColName + " = '" + userName + "'";
DataTable dtuserdata = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dtuserdata);
dataAdapter.Dispose();
cmd.Connection.Close();
if (dtuserdata.Rows.Count > 0)
{
DataRow data = PublicFunc.CheckEmptyVal(dtuserdata.Clone(), dtuserdata.Rows[0]);
PmUser.EmpID = Convert.ToInt32(data["empID"]);
PmUser.UserName = data["empName"].ToString();
PmUser.EmpWorkID = data["empWorkID"].ToString();
PmUser.UserDesc = data["dept"].ToString();
PmUser.PhoneNumber = data["phoneNum"].ToString();
PmUser.Email = data["email"].ToString();
PmUser.UserSysID = Convert.ToInt32(data["sysID"]);
PmUser.UsercusID = Convert.ToInt32(data["cusID"]);
PmUser.UserShopUserGroupID = data["shopUserGroupID"].ToString();
PmUser.UserSysName = PublicFunc.GetSysName(PmUser.UserSysID);
PmUser.UserIpAdress = UserIP;
PmUser.UserPass = userPass;
PmUser.UserAgent = UserAgent;
}
CLogin login = new CLogin();
//查询是否有这个用户名
if (dtuserdata.Rows.Count == 0)
{
login.LoginState = 0;
login.Message = "没有这个用户名.";
}
//判断密码是否正确
else
{
//服务器获取登录状态
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "SELECT * FROM wapUserstate WHERE empid = '" + userName + "'";
DataTable dtuserstate = new DataTable();
dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dtuserstate);
dataAdapter.Dispose();
cmd.Connection.Close();
//判断是否是登录状态
if (dtuserstate.Rows.Count > 0)
{
//查询用户是否在线
if (Convert.ToBoolean(dtuserstate.Rows[0]["online"]))
{
//用户在线,返回在线消息
login.LoginState = 2;
login.Message = "用户已经在IP:" + dtuserstate.Rows[0]["userIpaddress"] + " 上登陆.";
}
else
{
//用户不在线,但是密码错误
DateTime lasterrortime = Convert.ToDateTime(dtuserstate.Rows[0]["errorTime"]);
int errortimes = Convert.ToInt32(dtuserstate.Rows[0]["errorTimes"]);
int haslogintimes = pwderrTimes - errortimes;
if (haslogintimes > 0)
{
//可以再次登录
if (userPass == dtuserdata.Rows[0]["password"].ToString())
{
//密码正确,返回登录信息,记录用户为正常登录
login.LoginState = 1;
login.Message = "登录成功.";
login.UserGuiD = Guid.NewGuid().ToString();
PmUser.UserGuid = login.UserGuiD;
login.EmpID = PmUser.EmpID;
if (CUserInfo.LogedUserInfo.ContainsKey(PmUser.EmpID.ToString()))
{
CUserInfo.LogedUserInfo[PmUser.EmpID.ToString()] = PmUser.UserGuid;
}
else
{
CUserInfo.LogedUserInfo.Add(PmUser.EmpID.ToString(), PmUser.UserGuid);
}
//更新用户信息类
PmUser.UserGuid = login.UserGuiD;
//查询用户是不是管理员
PmUser.IsAdmin = IsAdmin(PmUser.EmpID, PmUser.UserSysID);
//获取用户的功能列表
PmUser.FunctionList = new List <string>();
if (PmUser.IsAdmin == true)
{
PmUser.FunctionList.Add("systemsetting");
PmUser.FunctionList.Add("reportsystem");
PmUser.FunctionList.Add("datacenter");
PmUser.FunctionList.Add("planboard");
}
else
{
cmd = PmConnections.ModCmd();
cmd.CommandText = "SELECT shopUsergroupid FROM wapUser WHERE userName in (SELECT USERNAME FROM wapEmpUserMap WHERE empid = '" + PmUser.EmpID + "')";
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0].ToString().ToUpper() == "CFM")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "REP")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "VIEW")
{
PmUser.FunctionList.Add("datacenter");
}
if (rd[0].ToString().ToUpper() == "BOARD")
{
PmUser.FunctionList.Add("planboard");
}
}
rd.Close();
cmd.Connection.Close();
}
//更新登录状态,这里是update
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "UPDATE wapUserstate SET userIpaddress = '" + PmUser.UserIpAdress + "',onLine = '1',errorTimes='0',errorTime='" + DateTime.Now + "',message = '登录成功',userGuid = '" + PmUser.UserGuid + "'WHERE empid = '" + GetUpdateVal() + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "登陆成功.", UserAgent);
}
else
{
//密码错误,返回登录信息,记录用户密码错误
errortimes++;
login.LoginState = 0;
login.Message = "用户密码错误！再输入" + (3 - errortimes).ToString() + "次错误密码后，账号将被锁定5分钟.";
//更新登录状态,这里是update
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "UPDATE wapUserstate SET userIpaddress = '" + PmUser.UserIpAdress + "',onLine = '0',errorTimes='" + errortimes + "',errorTime='" + DateTime.Now + "',message = '密码错误',userGuid = '" + PmUser.UserGuid + "'WHERE empid = '" + GetUpdateVal() + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "密码错误,错误次数:" + errortimes, UserAgent);
}
}
else
{
if ((DateTime.Now - lasterrortime).TotalSeconds > 300)
{
//可以验证密码登录
if (userPass == dtuserdata.Rows[0]["password"].ToString())
{
login.LoginState = 1;
login.Message = "登录成功.";
login.UserGuiD = Guid.NewGuid().ToString();
PmUser.UserGuid = login.UserGuiD;
login.EmpID = PmUser.EmpID;
if (CUserInfo.LogedUserInfo.ContainsKey(PmUser.EmpID.ToString()))
{
CUserInfo.LogedUserInfo[PmUser.EmpID.ToString()] = PmUser.UserGuid;
}
else
{
CUserInfo.LogedUserInfo.Add(PmUser.EmpID.ToString(), PmUser.UserGuid);
}
//更新用户信息类
PmUser.UserGuid = login.UserGuiD;
//查询用户是不是管理员
PmUser.IsAdmin = IsAdmin(PmUser.EmpID, PmUser.UserSysID);
//获取用户的功能列表
PmUser.FunctionList = new List <string>();
if (PmUser.IsAdmin == true)
{
PmUser.FunctionList.Add("systemsetting");
PmUser.FunctionList.Add("reportsystem");
PmUser.FunctionList.Add("datacenter");
PmUser.FunctionList.Add("planboard");
}
else
{
cmd = PmConnections.ModCmd();
cmd.CommandText = "SELECT shopUSergroupid FROM wapUser WHERE userName in (SELECT USERNAME FROM wapEmpUserMap WHERE empid = '" + PmUser.EmpID + "') and sysid = '" + PmUser.UserSysID + "'";
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0].ToString().ToUpper() == "CFM")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "REP")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "VIEW")
{
PmUser.FunctionList.Add("datacenter");
}
if (rd[0].ToString().ToUpper() == "BOARD")
{
PmUser.FunctionList.Add("planboard");
}
}
rd.Close();
cmd.Connection.Close();
}
//更新登录状态,这里是update
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "UPDATE wapUserstate SET userIpaddress = '" + PmUser.UserIpAdress + "',onLine = '1',errorTimes='0',errorTime='" + DateTime.Now + "',message = '登录成功',userGuid = '" + PmUser.UserGuid + "' WHERE empid = '" + GetUpdateVal() + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "登陆成功.", UserAgent);
}
//用户密码错误记录密码错误次数
else
{
//删除登录状态,重新登录
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "DELETE FROM wapUserstate WHERE empid = '" + GetUpdateVal() + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
//正常退出的用户,判断用户密码是否正确
if (userPass == dtuserdata.Rows[0]["password"].ToString())
{
//密码正确,返回登录信息,记录用户为正常登录
login.LoginState = 1;
login.Message = "登录成功.";
login.UserGuiD = Guid.NewGuid().ToString();
PmUser.UserGuid = login.UserGuiD;
login.EmpID = PmUser.EmpID;
if (CUserInfo.LogedUserInfo.ContainsKey(PmUser.EmpID.ToString()))
{
CUserInfo.LogedUserInfo[PmUser.EmpID.ToString()] = PmUser.UserGuid;
}
else
{
CUserInfo.LogedUserInfo.Add(PmUser.EmpID.ToString(), PmUser.UserGuid);
}
//更新用户信息类
PmUser.UserGuid = login.UserGuiD;
//查询用户是不是管理员
PmUser.IsAdmin = IsAdmin(PmUser.EmpID, PmUser.UserSysID);
//获取用户的功能列表
PmUser.FunctionList = new List <string>();
if (PmUser.IsAdmin == true)
{
PmUser.FunctionList.Add("systemsetting");
PmUser.FunctionList.Add("reportsystem");
PmUser.FunctionList.Add("datacenter");
PmUser.FunctionList.Add("planboard");
}
else
{
cmd = PmConnections.ModCmd();
cmd.CommandText = "SELECT shopUSergroupid FROM wapUser WHERE userName in (SELECT USERNAME FROM wapEmpUserMap WHERE empid = '" + PmUser.EmpID + "') and sysid = '" + PmUser.UserSysID + "'";
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0].ToString().ToUpper() == "CFM")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "REP")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "VIEW")
{
PmUser.FunctionList.Add("datacenter");
}
if (rd[0].ToString().ToUpper() == "BOARD")
{
PmUser.FunctionList.Add("planboard");
}
}
rd.Close();
cmd.Connection.Close();
}
//更新登录状态
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "INSERT wapUserstate (empID,empName,userPass,userIpaddress,onLine,errorTimes,errorTime,message,userGuid) VALUES ('" + GetUpdateVal() + "','"
+ PmUser.UserName + "','" + PmUser.UserPass + "','" + PmUser.UserIpAdress + "','1','0','" + DateTime.Now + "','登录成功','" + PmUser.UserGuid + "')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (CUserInfo.LogedUserInfo.ContainsKey(PmUser.UserName))
{
CUserInfo.LogedUserInfo[PmUser.UserName] = PmUser.UserGuid;
}
else
{
CUserInfo.LogedUserInfo.Add(PmUser.UserName, PmUser.UserGuid);
}
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "登陆成功.", UserAgent);
}
else
{
//密码错误,返回登录信息,记录用户密码错误
login.LoginState = 0;
login.Message = "用户密码错误！再输入2次错误密码后，账号将被锁定5分钟.";
//更新登录状态
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "INSERT wapUserstate (empID,empName,userPass,userIpaddress,onLine,errorTimes,errorTime,message,userGuid) VALUES ('" + GetUpdateVal() + "','"
+ PmUser.UserName + "','" + PmUser.UserPass + "','" + PmUser.UserIpAdress + "','0','1','" + DateTime.Now + "','密码错误','" + PmUser.UserGuid + "')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "密码错误,错误次数:1", UserAgent);
};
}
}
else
{
login.LoginState = 0;
login.Message = "用户被锁定，请在" + (300 - (DateTime.Now - lasterrortime).TotalSeconds).ToString("0") + "秒后登陆.";
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户锁定", "用户由于密码错误超过3次,用户锁定.", UserAgent);
}
}
}
}
else
{
//正常退出的用户,判断用户密码是否正确
if (userPass == dtuserdata.Rows[0]["password"].ToString())
{
//密码正确,返回登录信息,记录用户为正常登录
login.LoginState = 1;
login.Message = "登录成功.";
login.UserGuiD = Guid.NewGuid().ToString();
PmUser.UserGuid = login.UserGuiD;
login.EmpID = PmUser.EmpID;
if (CUserInfo.LogedUserInfo.ContainsKey(PmUser.EmpID.ToString()))
{
CUserInfo.LogedUserInfo[PmUser.EmpID.ToString()] = PmUser.UserGuid;
}
else
{
CUserInfo.LogedUserInfo.Add(PmUser.EmpID.ToString(), PmUser.UserGuid);
}
//更新用户信息类
PmUser.UserGuid = login.UserGuiD;
//查询用户是不是管理员
PmUser.IsAdmin = IsAdmin(PmUser.EmpID, PmUser.UserSysID);
//获取用户的功能列表
PmUser.FunctionList = new List <string>();
if (PmUser.IsAdmin == true)
{
PmUser.FunctionList.Add("systemsetting");
PmUser.FunctionList.Add("reportsystem");
PmUser.FunctionList.Add("datacenter");
PmUser.FunctionList.Add("planboard");
}
else
{
cmd = PmConnections.ModCmd();
cmd.CommandText = "SELECT shopUSergroupid FROM wapUser WHERE userName in (SELECT USERNAME FROM wapEmpUserMap WHERE empid = '" + PmUser.EmpID + "') and sysid = '" + PmUser.UserSysID + "'";
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0].ToString().ToUpper() == "CFM")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "REP")
{
PmUser.FunctionList.Add("reportsystem");
}
if (rd[0].ToString().ToUpper() == "VIEW")
{
PmUser.FunctionList.Add("datacenter");
}
if (rd[0].ToString().ToUpper() == "BOARD")
{
PmUser.FunctionList.Add("planboard");
}
}
rd.Close();
cmd.Connection.Close();
}
//更新登录状态
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "INSERT wapUserstate (empID,empName,userPass,userIpaddress,onLine,errorTimes,errorTime,message,userGuid) VALUES ('" + GetUpdateVal() + "','"
+ PmUser.UserName + "','" + PmUser.UserPass + "','" + PmUser.UserIpAdress + "','1','0','" + DateTime.Now + "','登录成功','" + PmUser.UserGuid + "')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "登陆成功.", UserAgent);
}
else
{
//密码错误,返回登录信息,记录用户密码错误
login.LoginState = 0;
login.Message = "用户密码错误！再输入2次错误密码后，账号将被锁定5分钟.";
//更新登录状态
cmd = PmConnections.CtrlCmd();
cmd.CommandText = "INSERT wapUserstate (empID,empName,userPass,userIpaddress,onLine,errorTimes,errorTime,message,userGuid) VALUES ('" + GetUpdateVal() + "','"
+ PmUser.UserName + "','" + PmUser.UserPass + "','" + PmUser.UserIpAdress + "','0','1','" + DateTime.Now + "','密码错误','" + PmUser.UserGuid + "')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
PublicFunc.WriteUserLog(PmUser.EmpID.ToString(), UserIP, "用户登录", "密码错误,错误次数:1", UserAgent);
}
}
}
return(login);
}
