mes-client/PLCDataReader/PublicMethod/LogicHandler.cs

using System;
using System.Collections.Generic;
using System.Data;
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 StringBuilder sql = new StringBuilder();
        //用于存放批量执行的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;
            }
        }

        /// <summary>
        /// 进行差异日志记录
        /// </summary>
        public static void DoDeviceDataDiffLog(DataHelper dh, string iDeCode, string iDeName, string iCommand, string iCommandFrom, string iMan)
        {
            sql.Clear();
            sql.Append("insert into DeviceRunRecord(drr_id,drr_decode,drr_dename,drr_command,drr_commandfrom,drr_date,drr_doman,drr_remark)");
            sql.Append("values(DeviceRunRecord_seq.nextval,'" + iDeCode + "','" + iDeName + "','" + iCommand + "','" + iCommandFrom + "',sysdate,'" + iMan + "','')");
            dh.ExecuteSql(sql.ToString(), "insert");
        }

        /// <summary>
        /// 进行差异日志记录
        /// </summary>
        public static void DoDeviceDataDiffLog(string iDeCode, string iDeName, string iCommand, string iCommandFrom, string iMan)
        {
            sql.Clear();
            sql.Append("insert into DeviceRunRecord(drr_id,drr_decode,drr_dename,drr_command,drr_commandfrom,drr_date,drr_doman,drr_remark)");
            sql.Append("values(DeviceRunRecord_seq.nextval,'" + iDeCode + "','" + iDeName + "','" + iCommand + "','" + iCommandFrom + "',sysdate,'" + iMan + "','')");
            dh.ExecuteSql(sql.ToString(), "insert");
        }

        /// <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)
        {
            sql.Clear();
            sql.Append("update DEVICEPOLLINGLOG set ");
            if (iOnLine)
            {
                sql.Append("dpg_onlinetime = sysdate,dpg_status='running' where dpg_id=(select max(dpg_id)");
                sql.Append(" from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "') and nvl(dpg_status,'disconnected')='disconnected'");
            }
            else
            {
                sql.Append("dpg_offlinetime = sysdate,dpg_status='disconnected' where dpg_id=(select max(dpg_id)");
                sql.Append(" from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "') and dpg_status='running'");
            }

            dh.ExecuteSql(sql.ToString(), "insert");
        }

        /// <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 iSendDataSize, string iReceiveDataSize, bool iOnLine)
        {
            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 ");
            if (iOnLine)
            {
                sql.Append(",dpg_onlinetime = sysdate,dpg_status='running'");
            }
            else
            {
                sql.Append(",dpg_offlinetime = sysdate,dpg_status='disconnected'");
            }
            sql.Append(" where dpg_id=(select max(dpg_id) from DEVICEPOLLINGLOG where dpg_decode='" + iDeCode + "')");
            dh.ExecuteSql(sql.ToString(), "insert");
        }

        /// <summary>
        /// 记录轮询日志，不记录时间
        /// </summary>
        /// <param name="iDeCode">设备编号</param>
        /// <param name="iSendDataSize">发送数据大小</param>
        /// <param name="iReceiveDataSize">接收数据大小</param>
        public static void DoDevicePollingLog(string iDeCode, string iSendDataSize, string iReceiveDataSize)
        {
            sql.Clear();
            sql.Append("update DEVICEPOLLINGLOG set dpg_status='running',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 + "')");
            dh.ExecuteSql(sql.ToString(), "insert");
        }

        /// <summary>
        /// 更新设备数据投入，产出等数据
        /// </summary>
        /// <param name="Decode"></param>
        /// <param name="ItemData"></param>
        public static void UpdateDeviceData(DataHelper dh, string Decode, Dictionary<string, string> ItemData)
        {
            string INQTY = "";
            string OUTQTY = "";
            string TEMPERATURE = "";
            string PARAM1 = "";
            string PARAM2 = "";
            string PARAM3 = "";
            foreach (var item in ItemData)
            {
                switch (item.Key)
                {
                    case "INQTY":
                        INQTY = item.Value;
                        break;
                    case "OUTQTY":
                        OUTQTY = item.Value;
                        break;
                    case "TEMPERATURE":
                        TEMPERATURE = item.Value;
                        break;
                    case "PARAM1":
                        PARAM1 = item.Value;
                        break;
                    case "PARAM2":
                        PARAM2 = item.Value;
                        break;
                    case "PARAM3":
                        PARAM3 = item.Value;
                        break;
                    default:
                        break;
                }
            }
            sql.Clear();
            sql.Append("update DeviceRunstatus set dr_qty=:dr_inqty,dr_okqty=:dr_okqty,dr_TEMPERATURE=:dr_TEMPERATURE,");
            sql.Append("dr_value1=:dr_value1,dr_value2=:dr_value2,dr_value3=:dr_value3,dr_updatedate=sysdate where dr_decode='" + Decode + "'");
            dh.ExecuteSql(sql.ToString(), "update", INQTY, OUTQTY, TEMPERATURE, PARAM1, PARAM2, PARAM3);
        }

        /// <summary>
        /// 更新设备数据投入，产出等数据
        /// </summary>
        /// <param name="Decode"></param>
        /// <param name="ItemData"></param>
        public static void UpdateDeviceData(string Decode, Dictionary<string, string> ItemData)
        {
            string INQTY = "";
            string OUTQTY = "";
            string TEMPERATURE = "";
            string PARAM1 = "";
            string PARAM2 = "";
            string PARAM3 = "";
            foreach (var item in ItemData)
            {
                switch (item.Key)
                {
                    case "INQTY":
                        INQTY = item.Value;
                        break;
                    case "OUTQTY":
                        OUTQTY = item.Value;
                        break;
                    case "TEMPERATURE":
                        TEMPERATURE = item.Value;
                        break;
                    case "PARAM1":
                        PARAM1 = item.Value;
                        break;
                    case "PARAM2":
                        PARAM2 = item.Value;
                        break;
                    case "PARAM3":
                        PARAM3 = item.Value;
                        break;
                    default:
                        break;
                }
            }
            sql.Clear();
            sql.Append("update DeviceRunstatus set dr_qty=:dr_inqty,dr_okqty=:dr_okqty,dr_TEMPERATURE=:dr_TEMPERATURE,");
            sql.Append("dr_value1=:dr_value1,dr_value2=:dr_value2,dr_value3=:dr_value3,dr_updatedate=sysdate where dr_decode='" + Decode + "'");
            dh.ExecuteSql(sql.ToString(), "update", INQTY, OUTQTY, TEMPERATURE, PARAM1, PARAM2, PARAM3);
        }

        /// <summary>
        /// 更新设备运行状态
        /// </summary>
        /// <param name="dh"></param>
        /// <param name="iDecode"></param>
        /// <param name="iDename"></param>
        /// <param name="iDestatus"></param>
        public static void UpdateDeviceStatus(DataHelper dh, string iDecode, string iDename, string iDestatus)
        {
            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 + "' ");
            sql.Append("WHEN NOT MATCHED THEN INSERT (Dr_id,dr_decode,dr_dename,Dr_runstatus,dr_startdate) VALUES (DeviceRunstatus_seq.nextval,");
            sql.Append("'" + iDecode + "','" + iDename + "','" + iDestatus + "',sysdate)");
            dh.ExecuteSql(sql.ToString(), "update");
        }
    }
}