mes-client/PLCDataReader/PublicMethod/LogicHandler.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Net;
using System.Text;
using System.Windows.Forms;
using UAS_PLCDataReader.DataOperate;
using UAS_PLCDataReader.Entity;

namespace UAS_PLCDataReader.PublicMethod
{
    class LogicHandler
    {
        public LogicHandler() { }

        static DataHelper dh = SystemInf.dh;
        //用于存放批量执行的SQL
        static List<string> sqls = new List<string>();
        /// <summary>
        /// 验证用户身份信息
        /// </summary>
        /// <param name="iUserCode"></param>
        /// <param name="oErrorMessage"></param>
        /// <returns></returns>
        public static bool CheckUserLogin(string iUserCode, string iPassWord, out string oErrorMessage)
        {
            oErrorMessage = "";
            string SQL = "select em_code from employee where upper(em_code)=:UserName and em_password =:PassWord";
            DataTable dt;
            dt = (DataTable)dh.ExecuteSql(SQL, "select", iUserCode.ToUpper(), iPassWord);
            if (dt.Rows.Count > 0)
                return true;
            else
            {
                oErrorMessage = "用户名或者密码不正确!";
                return false;
            }
        }

        public static bool CheckUserLogin(string UserName, string PassWord, string Master, out string oMsg)
        {
            oMsg = "";
            try
            {
                string url = DataHelper.ERPAddesss + "mobile/login.action";//html调用的地址              
                HttpWebRequest webrequest = (HttpWebRequest)WebRequest.Create(url);
                if (webrequest == null)
                {
                    return false;
                }
                webrequest.Method = "POST";
                webrequest.Timeout = 1000;
                webrequest.ContentType = "application/x-www-form-urlencoded";
                System.Collections.Hashtable pars = new System.Collections.Hashtable();
                pars.Add("username", UserName);
                pars.Add("password", PassWord);
                pars.Add("master", Master);
                string buffer = "";
                //发送POST数据 
                if (!(pars == null || pars.Count == 0))
                {
                    foreach (string key in pars.Keys)
                    {
                        buffer = buffer + "&" + key + "=" + pars[key].ToString();
                    }
                    byte[] data = Encoding.UTF8.GetBytes(buffer);
                    using (Stream stream = webrequest.GetRequestStream())
                    {
                        stream.Write(data, 0, data.Length);
                    }
                }

                string[] values = webrequest.Headers.GetValues("Content-Type");
                WebResponse myResponse = webrequest.GetResponse();

                using (Stream resStream = myResponse.GetResponseStream())//得到回写的流
                {
                    StreamReader newReader = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8);
                    string Content = newReader.ReadToEnd();
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    dic = BaseUtil.ToDictionary(Content);
                    if (!dic.ContainsKey("erpaccount"))
                    {
                        oMsg = dic["reason"].ToString();
                        return false;
                    }
                    newReader.Close();
                }
            }
            catch (Exception ex)
            {
                LogManager.DoLog(ex.Message.ToString());
            }
            return true;
        }

        /// <summary>
        /// 记录轮询日志，记录时间
        /// </summary>
        /// <param name="iDeCode">设备编号</param>
        /// <param name="iSendDataSize">发送数据大小</param>
        /// <param name="iReceiveDataSize">接收数据大小</param>
        /// <param name="iOnLine">上线或者离线</param>
        public static void DoDevicePollingLog(string iDeCode, bool iOnLine)
        {
            StringBuilder sql = new StringBuilder();
            sql.Clear();
            sql.Append("update DEVICEPOLLINGLOG set ");
            if (iOnLine)
            {
                sql.Append("dpg_onlinetime = sysdate,dpg_status='" + DeviceStatus.Running + "' where dpg_id=(select max(dpg_id)");
                sql.Append(" from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "') and nvl(dpg_status,'disconnect')='disconnect'");
                Main.QueueUpdateDevice.Enqueue(sql.ToString());
                sql.Clear();
                sql.Append("update DeviceRunTime set drt_onlinetime=sysdate where drt_id=(select min(drt_id) from DeviceRunTime ");
                sql.Append("where drt_decode='" + iDeCode + "' and drt_onlinetime is null)");
                Main.QueueUpdateDevice.Enqueue(sql.ToString());
                //删除无用的重复数据
                sql.Clear();
                sql.Append("delete from DeviceRunTime where drt_decode='" + iDeCode + "' and drt_onlinetime is null");
                Main.QueueUpdateDevice.Enqueue(sql.ToString());
            }
            else
            {
                sql.Append("dpg_offlinetime = sysdate,dpg_status='" + DeviceStatus.Disconnect + "' where dpg_id=(select max(dpg_id)");
                sql.Append(" from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "') and dpg_status='running'");
                Main.QueueUpdateDevice.Enqueue(sql.ToString());
                sql.Clear();
                sql.Append("insert into DeviceRunTime (drt_id,drt_decode,drt_offlinetime,drt_badcount) values(DeviceRunTime_seq.nextval,");
                sql.Append("'" + iDeCode + "',sysdate,(select count(1)+1 from DeviceRunTime where drt_decode='" + iDeCode + "' and drt_onlinetime is not null))");
                Main.QueueUpdateDevice.Enqueue(sql.ToString());
            }
        }

        /// <summary>
        /// 记录轮询日志，记录时间
        /// </summary>
        /// <param name="iDeCode">设备编号</param>
        /// <param name="iSendDataSize">发送数据大小</param>
        /// <param name="iReceiveDataSize">接收数据大小</param>
        /// <param name="iOnLine">上线或者离线</param>
        public static void DoDevicePollingLog(string iDeCode, string iRemark)
        {
            StringBuilder sql = new StringBuilder();
            sql.Clear();
            sql.Append("update DEVICEPOLLINGLOG set dpg_remark='" + iRemark + "' where dpg_id=(select max(dpg_id) ");
            sql.Append("from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "')");
            Main.QueueUpdateDevice.Enqueue(sql.ToString());
        }

        /// <summary>
        /// 记录轮询日志，不记录时间
        /// </summary>
        /// <param name="iDeCode">设备编号</param>
        /// <param name="iSendDataSize">发送数据大小</param>
        /// <param name="iReceiveDataSize">接收数据大小</param>
        public static void DoDevicePollingLog(string iDeCode, string iSendDataSize, string iReceiveDataSize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Clear();
            sql.Append("update DEVICEPOLLINGLOG set dpg_senddatasize=nvl(dpg_senddatasize,0)+" + iSendDataSize);
            sql.Append(",dpg_receivedatasize=nvl(dpg_receivedatasize,0)+" + iReceiveDataSize + ",dpg_count=nvl(dpg_count,0)+1 ");
            sql.Append(" where dpg_id=(select max(dpg_id) from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "')");
            Main.QueueUpdateDevice.Enqueue(sql.ToString());
        }

        /// <summary>
        /// 更新设备数据投入，产出等数据
        /// </summary>
        /// <param name="Decode"></param>
        /// <param name="ItemData"></param>
        public static void UpdateDeviceData(string Decode, Dictionary<string, string> ItemData)
        {
            if (ItemData == null)
                return;
            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            //更新设备运行状态
            sql.Append("update DeviceRunstatus set ");
            //更新设备工作时间
            sql1.Append("merge into deviceworktime a using  (select '' decode,sysdate time from  dual)  b on(trunc(a.dwt_indate)=trunc(b.time) and a.dwt_decode = b.decode) when matched then update set ");
            //不良代码索引
            int BadIndex = 0;
            foreach (var item in ItemData)
            {
                switch (item.Key)
                {
                    case "INQTY":
                        if (item.Value != "")
                        {
                            sql.Append("dr_qty='" + item.Value + "',");
                        }
                        break;
                    case "OUTQTY":
                        if (item.Value != "")
                        {
                            sql.Append("dr_okqty='" + item.Value + "',");
                        }
                        break;
                    case "NGQTY":
                        if (item.Value != "")
                        {
                            sql.Append("dr_ngqty='" + item.Value + "',");
                        }
                        break;
                    case "TEMPERATURE":
                        if (item.Value != "")
                        {
                            sql.Append("dr_TEMPERATURE='" + item.Value + "',");
                        }
                        break;
                    case "PARAM1":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value1='" + item.Value + "',");
                        }
                        break;
                    case "PARAM2":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value2='" + item.Value + "',");
                        }
                        break;
                    case "PARAM3":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value3='" + item.Value + "',");
                        }
                        break;
                    case "PARAM4":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value4='" + item.Value + "',");
                        }
                        break;
                    case "PARAM5":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value5='" + item.Value + "',");
                        }
                        break;
                    case "PARAM6":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value6='" + item.Value + "',");
                        }
                        break;
                    case "PARAM7":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value7='" + item.Value + "',");
                        }
                        break;
                    case "PARAM8":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value8='" + item.Value + "',");
                        }
                        break;
                    case "PARAM9":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value9='" + item.Value + "',");
                        }
                        break;
                    case "PARAM10":
                        if (item.Value != "")
                        {
                            sql.Append("dr_value10='" + item.Value + "',");
                        }
                        break;
                    case "WAITTIME":
                        if (item.Value != "")
                        {
                            sql1.Append("dwt_waittime='" + item.Value + "',");
                        }
                        break;
                    case "BREAKTIME":
                        if (item.Value != "")
                        {
                            sql1.Append("dwt_breaktime='" + item.Value + "',");
                        }
                        break;
                    case "MANSTOPTIME":
                        if (item.Value != "")
                        {
                            sql1.Append("dwt_manstoptime='" + item.Value + "',");
                        }
                        break;
                    case "WORKTIME":
                        if (item.Value != "")
                        {
                            sql1.Append("dwt_worktime='" + item.Value + "',");
                        }
                        break;
                    case "STARTTIME":
                        if (item.Value != "")
                        {
                            sql1.Append("dwt_starttime='" + item.Value + "',");
                        }
                        break;
                    case "RUNTIME":
                        if (item.Value != "")
                        {
                            sql1.Append("dwt_runtime='" + item.Value + "',");
                        }
                        break;
                    default:
                        break;
                }
                if (item.Key.Contains("BADDETAIL"))
                {
                    DataTable dt = (DataTable)dh.ExecuteSql("select bgd_badcode from badgroup left join BADGROUPDETAIL on bg_id=bgd_bgid where bg_code='"+ Decode + "' order by bgd_detno", "select");
                    if(BadIndex<dt.Rows.Count)
                    {
                        string badcode = dt.Rows[BadIndex]["bgd_badcode"].ToString();
                        sql2.Clear();
                        sql2.Append("merge into makebad a using  (select '"+ Decode + "' decode,sysdate time from  dual)b ");
                        sql2.Append("on(trunc(a.mb_indate)=trunc(b.time) and a.mb_badcode = '"+ badcode + "' and a.mb_decode='"+Decode+"') when matched then update set ");
                        sql2.Append("mb_value='"+item.Value+ "',mb_updatetime=sysdate when not matched then insert (mb_id,mb_decode,mb_badcode,mb_value,mb_updatetime)values(deviceworktime_seq.nextval,'" + Decode+"', '"+ badcode + "', '" + item.Value+"',sysdate)");
                        Console.WriteLine(Decode);
                        Main.QueueUpdateDevice.Enqueue(sql2.ToString());
                    }
                    BadIndex = BadIndex + 1;
                }
            }
            //更新设别运行状态
            sql.Append("dr_updatedate=sysdate where dr_decode='" + Decode + "'");
            Main.QueueUpdateDevice.Enqueue(sql.ToString());
            //更新设备工作时长
            sql1.Append("dwt_updatetime=sysdate  when not matched then insert (dwt_id,dwt_decode,dwt_indate) values(deviceworktime_seq.nextval,'" + Decode + "',sysdate)");
            Main.QueueUpdateDevice.Enqueue(sql1.ToString());
        }

        /// <summary>
        /// 更新设备运行状态
        /// </summary>
        /// <param name="dh"></param> 
        /// <param name="iDecode"></param>
        /// <param name="iDename"></param>
        /// <param name="iDestatus"></param>
        public static void UpdateDeviceStatus(string iDecode, string iDename, string iErrMessage, string iDestatus, int iType)
        {
            StringBuilder sql = new StringBuilder();
            if (iDestatus == DeviceStatus.Running)
            {
                sql.Clear();
                sql.Append("MERGE INTO DeviceRunstatus alias1 USING (select '" + iDecode + "' dr_decode from  dual) alias2 ");
                sql.Append("ON (alias1.dr_decode=alias2.dr_decode) WHEN MATCHED THEN  UPDATE  SET dr_startdate =sysdate,Dr_runstatus='" + iDestatus + "',dr_getdatatime=(sysdate-dr_updatestatusdate)* 24 * 60 * 60*1000 ");
                sql.Append("WHEN NOT MATCHED THEN INSERT (Dr_id,dr_decode,dr_dename,Dr_runstatus,dr_startdate,dr_updatestatusdate) VALUES (DeviceRunstatus_seq.nextval,");
                sql.Append("'" + iDecode + "','" + iDename + "','" + iDestatus + "',sysdate,sysdate)");
            }
            else
            {
                sql.Clear();
                sql.Append("MERGE INTO DeviceRunstatus alias1 USING (select '" + iDecode + "' dr_decode from  dual) alias2 ");
                sql.Append("ON (alias1.dr_decode=alias2.dr_decode) WHEN MATCHED THEN  UPDATE  SET dr_startdate=sysdate,Dr_runstatus='" + iDestatus + "',dr_updatestatusdate=sysdate ");
                sql.Append("WHEN NOT MATCHED THEN INSERT (Dr_id,dr_decode,dr_dename,Dr_runstatus,dr_startdate,dr_updatestatusdate) VALUES (DeviceRunstatus_seq.nextval,");
                sql.Append("'" + iDecode + "','" + iDename + "','" + iDestatus + "',sysdate,sysdate)");
            }
            Main.QueueUpdateDevice.Enqueue(sql.ToString());
            if (iType == -1)
            {
                sql.Clear();
                sql.Append("insert into DeviceRunRecord(drr_id,drr_decode,drr_dename,drr_date,drr_doman,drr_remark,drr_logstatus,drr_type)");
                sql.Append("values(DeviceRunRecord_seq.nextval,'" + iDecode + "','" + iDename + "',sysdate,'" + User.UserName + "','" + iErrMessage.Replace("'", "") + "','" + iDestatus + "'," + iType + ")");
                Main.QueueUpdateDevice.Enqueue(sql.ToString());
            }
        }

        public static void DoCommandLog(string iUserCode, string iPageText, string iContent)
        {
            StringBuilder sql = new StringBuilder();
            sql.Clear();
            sql.Append("insert into commandlog(cL_id,cl_date,cl_man,cl_result,cl_caller)values(commandlog_seq.nextval,sysdate,'" + iUserCode + "','" + iContent + "','" + iPageText + "')");
            dh.ExecuteSql(sql.ToString(), "insert");
        }
    }
}