123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313 |
- 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 BEMSDataGateway
- {
- class FirstCheck
- {
- LogFileCreate LFC = new LogFileCreate();
- My_DB_DML MDD = new My_DB_DML();
- public float[] UpdateDay(string date, int[] Array) // 15분 데이터 하루치 최대, 최소, 특정값 산출
- {
- float[] updatevalue = new float[3];
- string strcon = MDD.ConnDBInfo();
- string query = "";
- float[] SelectValue = new float[2];
- string SearchDate = "";
- object o = 0;
- DateTime tmpDate = Convert.ToDateTime(date);
- 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) //15분 데이터 하루치 합, 갯수, 최소, 최대 저장
- {
- query = "SELECT " + ch + "(CurrentValue) FROM BemsMonitoringPointHistory15min "
- + "WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(SearchDate).ToString("yyyy-MM-dd 00:00:00") + "' "
- + "AND '" + Convert.ToDateTime(SearchDate).ToString("yyyy-MM-dd 23:45: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이면 1로 나눔
- {
- 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();
- FP.Start_progressBar();
- StreamReader sr = new StreamReader("SettingFile.csv", Encoding.GetEncoding("euc-kr")); // 파일 읽기.
- while (!sr.EndOfStream) // Stream 끝까지 읽음
- {
- string s = sr.ReadLine(); // 한줄 읽기
- if (!(s.Length > 0))
- {
- continue;
- }
- temp = s.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]); // 데이터 종류(현재값, 적산값 등...)
- if (values[7] == 7)
- {
- continue;
- }
- ConnInfo = MDD.ConnDBInfo(); // DB 접속 정보
- HourlyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd HH:00:00.000");
- result = RV.ExistValue(HourlyDate, ConnInfo, values); // 데이터 존재 여부 판단, result[0] = Hourly의 값, result[1]에는 Daily값
- Return_Value = UpdateDay(HourlyDate, values); // 15분 데이터 하루치 최대, 최소, 특정값 산출
- string TmpWhere = "WHERE SiteId = " + values[2] + " "
- + "AND FacilityTypeId = " + values[3] + " "
- + "AND FacilityCode = " + values[4] + " "
- + "AND PropertyId = " + values[5] + " ";
- //Daily 테이블 데일리값, 최대, 최소 값 업데이트
- 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;
- }
- //LFC.Log("[FirstCheck.cs] Hour, Daily 테이블 삽입 및 업데이트" + Environment.NewLine);
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- try
- {
- conn.Open();
- SqlCommand cmd = new SqlCommand(TmpDML, conn);
- cmd.ExecuteNonQuery();
- conn.Close();
- }
- catch (Exception e)
- {
- LFC.Log("[FirstCheck.cs] Intsert에러 발생 : " + e + Environment.NewLine);
- }
- }
- }
- FP.Stop_progressBar();
- }
- }
- /// <summary>
- /// 값의 존재유무를 확인하기 위한 클래스
- /// </summary>
- 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 = "";
- 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);
- }
- return result;
- }
- }
- //결과를 상수로 넘겨 주기 위한 메서드
- public int ResultDataArr(int[] arr)
- {
- int ResultInt = 0;
- if (arr[0] == 0 && arr[1] == 0)
- {
- ResultInt = 1; // Daily, Hourly 둘다 없는 경우
- }
- 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; // Daily, Hourly 둘다 있는 경우
- }
- return ResultInt;
- }
- }
- }
|