using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Runtime.InteropServices; using System.Text; namespace DataGateWayProject { class Control_Point_Group { My_DB_DML MDD = new My_DB_DML(); LogFileCreate LFC = new LogFileCreate(); private StringBuilder sb = new StringBuilder(); private DataTable dataTable = new DataTable(); private DataTable dataTable2 = new DataTable(); private string[] temp; ///ControlGroup /// public void Control_Group() { string query = ""; string Update_query = ""; string strcon = MDD.ConnDBInfo(); using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); // 접속 query = "SELECT G.GroupName, G.GroupId, H.ExtendID, H.ScheduleDateTime, H.ControlValue, H.WriteServiceName, H.ActionDateTime " +"FROM BemsControlGroup G RIGHT OUTER JOIN BemsControlGroupHistory H ON G.GroupId = H.GroupId WHERE WriteServiceName IS NULL"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dataTable); cmd.ExecuteNonQuery(); conn.Close(); foreach(DataRow row in dataTable.Rows) { StreamReader sr = new StreamReader("GroupMatching.csv", Encoding.GetEncoding("euc-kr")); // 파일 한번만 읽기. while (!sr.EndOfStream) // Stream의 끝이 올때까지 반복 { string s = sr.ReadLine(); // 한줄을 읽음. if (!(s.Length > 0)) { continue; } temp = s.Split(','); // Split() 메서드를 이용하여 ',' 구분하여 잘라냄 temp[0] = temp[0].Trim(); temp[1] = temp[1].Trim(); temp[2] = temp[2].Trim(); if (temp[1] == Convert.ToString(row["GroupName"])) //GroupName 이 같다면.. { GetData_From_iCos GDFi = new GetData_From_iCos(); GDFi.SetTagValue(temp[2], (float)Convert.ToDouble(row["ControlValue"])); // Value Set한다. LFC.Log(temp[1] + (float)Convert.ToDouble(row["ControlValue"]) + "값을 iCos로 송신하였습니다" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } } conn.Open(); // 접속 Update_query = "UPDATE BemsControlGroupHistory SET WriteServiceName = 'Datagateway', ActionDateTime = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' " +"WHERE GroupId = " + Convert.ToString(row["GroupId"]) + " AND ScheduleDateTime = '" + Convert.ToDateTime(row["ScheduleDateTime"]).ToString("yyyy-MM-dd HH:mm:ss.000") + "'"; SqlCommand cmd_update = new SqlCommand(Update_query, conn); cmd_update.ExecuteNonQuery(); conn.Close(); } da.Dispose(); } } ///ControlPoint /// public void Control_Point() { string query = ""; string Update_query = ""; string strcon = MDD.ConnDBInfo(); StreamReader sr = new StreamReader("SettingFile.csv", Encoding.GetEncoding("euc-kr")); // 파일 한번만 읽기. using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); // 접속 query = "SELECT SiteId, FacilityTypeId, FacilityCode, PropertyId, CreateDateTime, ControlValue " + "FROM BemsControlPointHistory " + "WHERE WriteServiceName IS NULL"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataAdapter da2 = new SqlDataAdapter(cmd); da2.Fill(dataTable2); cmd.ExecuteNonQuery(); conn.Close(); foreach (DataRow row in dataTable2.Rows) { sb.Append(row["SiteId"]); // String을 하나로 합침. sb.Append("."); sb.Append(row["FacilityTypeId"]); sb.Append("."); sb.Append(row["FacilityCode"]); sb.Append("."); sb.Append(row["PropertyId"]); while (!sr.EndOfStream) // Stream의 끝이 올때까지 반복 { string s = sr.ReadLine(); // 한줄을 읽음. temp = s.Split(','); // Split() 메서드를 이용하여 ',' 구분하여 잘라냄 temp[0] = temp[0].Trim(); temp[1] = temp[1].Trim(); if(sb.ToString() == temp[0]) { GetData_From_iCos GDFi = new GetData_From_iCos(); GDFi.SetTagValue(temp[1], (float)Convert.ToDouble(row["ControlValue"])); // Value Set한다. conn.Open(); // 접속 Update_query = "UPDATE BemsControlPointHistory SET WriteServiceName = 'datagateway', ActionDateTime = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' " + "WHERE FacilityTypeId = " + Convert.ToString(row["FacilityTypeId"]) + " " + "AND FacilityCode = " + Convert.ToString(row["FacilityCode"]) + " " + "AND SiteId = " + Convert.ToString(row["SiteId"]) + " " + "AND PropertyId = " + Convert.ToString(row["PropertyId"]) + " " + "AND CreateDateTime = '" + Convert.ToDateTime(row["CreateDateTime"]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'"; SqlCommand cmd_update = new SqlCommand(Update_query, conn); cmd_update.ExecuteNonQuery(); conn.Close(); } } sb.Clear(); } da2.Dispose(); } } } }