using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.IO; using System.Data; using System.Windows.Forms; namespace DataGateWayProject { class FirstCheck { My_DB_DML MDD = new My_DB_DML(); public float[] UpdateDay(string date, int[] Array) { float[] updatevalue = new float[3]; string strcon = MDD.ConnDBInfo(); string query = ""; float[] SelectValue = new float[2]; string SearchDate = ""; object o = 0; //float minAccountLevel = float.MaxValue; //float maxAccountLevel = float.MinValue; DateTime tmpDate = Convert.ToDateTime(date); if ((tmpDate.Hour == 00) && (tmpDate.Minute == 00)) { SearchDate = tmpDate.AddDays(-1).ToString("yyyy-MM-dd 00:00:00"); } else { SearchDate = tmpDate.ToString("yyyy-MM-dd 00:00:00"); } using (SqlConnection conn = new SqlConnection(strcon)) { // [0] [1] [2] [3] string[] Check = { "SUM", "COUNT", "MIN", "MAX" }; foreach (string ch in Check) { query = "SELECT " + ch + "(CurrentValue) FROM BemsMonitoringPointHistory15min " + "WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(SearchDate).ToString("yyyy-MM-dd 00:15:00") + "' " + "AND '" + Convert.ToDateTime(date).AddDays(1).ToString("yyyy-MM-dd 00:00:00") + "' " + "AND SiteId = " + Array[2] + " " + "AND FacilityTypeId = " + Array[3] + " " + "AND FacilityCode = " + Array[4] + " " + "AND PropertyId = " + Array[5] + " "; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); o = cmd.ExecuteScalar(); // 1,100,1,4 if (o.GetType() != typeof(DBNull)) { switch (ch) { case "SUM": SelectValue[0] = (float)Convert.ToDouble(o); break; case "COUNT": SelectValue[1] = (float)Convert.ToDouble(o); break; case "MIN": updatevalue[1] = (float)Convert.ToDouble(o); break; case "MAX": updatevalue[2] = (float)Convert.ToDouble(o); break; } } conn.Close(); } switch (Array[7]) { case 0: // Raw break; case 1: // 평균 if (SelectValue[1] == 0) // 분모가 0이 되면 안되므로... { SelectValue[1] = 1; } updatevalue[0] = SelectValue[0] / SelectValue[1]; break; case 2: // 적산 updatevalue[0] = SelectValue[0]; break; case 3: // 현재 break; case 4: // 가동시간 updatevalue[0] = SelectValue[0]; break; case 5: // 적산값 updatevalue[0] = SelectValue[0]; break; case 6: // 현재2 if (SelectValue[0] > 0) { updatevalue[0] = 1; } else { updatevalue[0] = 0; } break; } } return updatevalue; } // DB접속 정보를 읽는 메서드 public string ConnDBInfo() { Readini Ri = new Readini(); Ri.ReadData(); string strconInfo = @"Data Source = " + Ri.UserIP + "," + Ri.UserPort + "; " + "Initial Catalog =" + Ri.UserDB + "; " + "Persist Security Info=True; " + "User ID =" + Ri.UserID + "; " + "Password =" + Ri.UserPW + ""; return strconInfo; } public void Check_DailyData() { FirstCheck_Progressbar FP = new FirstCheck_Progressbar(); My_DB_DML MDD = new My_DB_DML(); ReturnValue RV = new ReturnValue(); string ConnInfo = ""; string HourlyDate = ""; int result = 0; string[] temp; string TmpDML = ""; DateTime HourDate = DateTime.Now; int Min = int.Parse(HourDate.Minute.ToString()); string Hour = (HourDate).ToString("yyyy-MM-dd HH:00:00.000"); string Day = (HourDate).ToString("yyyy-MM-dd 00:00:00.000"); string strcon = MDD.ConnDBInfo(); //if ((15 <= Min) && (Min < 60)) //{ FP.Start_progressBar(); //} StreamReader sr = new StreamReader("SettingFile.csv", Encoding.GetEncoding("euc-kr")); // 파일 한번만 읽기. while (!sr.EndOfStream) // Stream의 끝이 올때까지 반복 { string s = sr.ReadLine(); // 한줄을 읽음. temp = s.Split(','); // Split() 메서드를 이용하여 ',' 구분하여 잘라냄 float[] Return_Value; temp[1] = temp[1].Trim(); string[] PointDivid = temp[0].Split('.'); int[] values = new int[8]; values[2] = int.Parse(PointDivid[0]); // SiteId values[3] = int.Parse(PointDivid[1]); // FacilityTypeId values[4] = int.Parse(PointDivid[2]); // FacilityCode values[5] = int.Parse(PointDivid[3]); // PropertyId values[7] = Convert.ToInt32(temp[2]); ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음. HourlyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd HH:00:00.000"); result = RV.ExistValue(HourlyDate, ConnInfo, values); // RECORD 존재 여부 판단 Return_Value = UpdateDay(HourlyDate, values); string TmpWhere = "WHERE SiteId = " + values[2] + " " + "AND FacilityTypeId = " + values[3] + " " + "AND FacilityCode = " + values[4] + " " + "AND PropertyId = " + values[5] + " "; string TmpDMLDayUP = " UPDATE " + Table.Daily + " SET DailyValue = " + Math.Round(Return_Value[0], 4, MidpointRounding.AwayFromZero) + ", MaxValue = " + Math.Round(Return_Value[2], 4, MidpointRounding.AwayFromZero) + ", MinValue = " + Math.Round(Return_Value[1], 4, MidpointRounding.AwayFromZero) + " " + TmpWhere + "AND CreatedDateTime = '" + Day + "'"; ; string TmpDMLHourIn = ""; ////////////////////////////// ////// INSERT 구문 ////// if ((15 <= Min) && (Min < 60)) { TmpDMLHourIn = " INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)" + " VALUES(" + values[2] + "," + values[3] + "," + values[4] + "," + values[5] + ",'" + Hour + "'," + 0 + ") "; } string TmpDMLDayIn = " INSERT INTO " + Table.Daily + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue)" + " VALUES(" + values[2] + "," + values[3] + "," + values[4] + "," + values[5] + ",'" + Day + "'," + Math.Round(Return_Value[0], 4, MidpointRounding.AwayFromZero) + "," + Math.Round(Return_Value[2], 4, MidpointRounding.AwayFromZero) + "," + Math.Round(Return_Value[1], 4, MidpointRounding.AwayFromZero) + ") "; ////////////////////////////// //쿼리를 조합해주는 구문 /// switch (result) { case 1: // 둘다 없는 경우,,, 모두 인서트 TmpDML = TmpDMLHourIn + TmpDMLDayIn; break; case 2: // 데일리만 있는 경우,,, 시간 인서트 TmpDML = TmpDMLHourIn + TmpDMLDayUP; break; case 3: // 시간만 있는 경우,,, 데일리 인서트 TmpDML = TmpDMLDayIn; break; case 4: // 둘다 있는 경우,,, 둘다 업데이트 TmpDML = TmpDMLDayUP; break; } using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); SqlCommand cmd = new SqlCommand(TmpDML, conn); cmd.ExecuteNonQuery(); conn.Close(); } } FP.Stop_progressBar(); } } /// /// 값의 존재유무를 확인하기 위한 클래스 /// class ReturnValue { int[] reader = new int[2]; // Hourly, Daily의 존재 결과를 담기 위한 변수 public int ExistValue(string Date, string strcon, int[] Arr) { int result = 0; using (SqlConnection conn = new SqlConnection(strcon)) { string TmpDML = ""; // TmpDMl 초기화 for (int Check = 0; Check < 2; Check++) // 리턴값을 체크하기 위한 쿼리문 { switch (Check) { case 0: // Hourly 유무 TmpDML = "SELECT COUNT(CurrentValue) FROM " + Table.Hourly + " " + "WHERE CreatedDateTime = '" + Date + "'"; break; case 1: // Daily 유무 TmpDML = "SELECT Count(DailyValue) FROM " + Table.Daily + " " + "WHERE CreatedDateTime = '" + Convert.ToDateTime(Date).ToString("yyyy-MM-dd 00:00:00.000") + "'"; break; } TmpDML += "AND SiteId = " + Arr[2] + " " + "AND FacilityTypeId = " + Arr[3] + " " + "AND FacilityCode = " + Arr[4] + " " + "AND PropertyId = " + Arr[5] + ""; SqlCommand cmd = new SqlCommand(TmpDML, conn); conn.Open(); reader[Check] = Convert.ToInt32(cmd.ExecuteScalar()); conn.Close(); result = ResultDataArr(reader); } //Check[0]에는 Hourly의 값이 들어갈 것이고, Check[1]에는 Daily값이 들어갈 것이다. return result; } } //결과를 상수로 넘겨 주기 위한 메서드 public int ResultDataArr(int[] arr) { int ResultInt = 0; if(arr[0]==0 && arr[1] ==0) { ResultInt = 1; // 둘다 조회 결과가 없는경우 } else if (arr[0] == 0 && arr[1] == 1) { ResultInt = 2; // Daily만 결과가 있는 경우 } else if (arr[0] == 1 && arr[1] == 0) { ResultInt = 3; // Hourly만 결과가 있는 경우 } else if (arr[0] == 1 && arr[1] == 1) { ResultInt = 4; // 둘다 조회 결과가 있는 경우 } return ResultInt; } } }