public void WaterThisPeriodValue()
{
//DataTable dtStartDay = GetThisPeriodStartDay();
//DateTime tmp_StartDate = Convert.ToDateTime(dtStartDay.Rows[0]["VariableValue"]);
//string sStartDay = tmp_StartDate.ToString("yyyy-MM-dd HH:mm:ss");
//DataTable dtEndDay = GetThisPeriodEndDay();
//DateTime tmp_EndDate = Convert.ToDateTime(dtEndDay.Rows[0]["VariableValue"]);
//string sEndDay = tmp_EndDate.ToString("yyyy-MM-dd HH:mm:ss");
DataTable dtStartDay = GetThisPeriodStartDay();
string sStartDay = dtStartDay.Rows[0]["VariableValue"].ToString() + " 00:00:00";
DataTable dtEndDay = GetThisPeriodEndDay();
string sEndDay = dtEndDay.Rows[0]["VariableValue"].ToString() + " 23:59:59";
clsDBComm commDB = new clsDBComm();
//DataTable DT = new DataTable();
bool IsSuccess = false;
string sSelect = "(select CASE MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) when 0 then MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) else CAST(( (MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm)) ) AS decimal(18, 3)) END AS CumulateValueAlarm from vwPowerMeter1HourLog where (1=1)and ERID != 4 and (Timestamp) >= '";
for (int ExeCount = 0; ExeCount < dt.Rows.Count; ExeCount++)
{
try
{
string text = "UPDATE tblPowerMeter SET ";
text += " SysThisPeriodWater = " + sSelect + sStartDay + "' and (Timestamp) <= '" + sEndDay + "' and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += " WHERE [ERID] = " + dt.Rows[ExeCount]["ERID"].ToString();
IsSuccess = commDB.ModifyDBData(text);
}
catch (Exception ex)
{
var message = ex.Message + "," + ex.StackTrace;
throw ex;
}
}
}
//public void Exchange(string Memo, int ERID, string ABA, string Name, DateTime StartDate, DateTime EndDate)
//{
// int RoleID=0;
// clsDBComm commDB = new clsDBComm();
// DataTable dt = new DataTable();
// DataTable dtSelect = new DataTable();
// string cmd = "";
// bool IsSuccess = false;
// string tempABA = "";
// tempABA = (Convert.ToUInt32(ABA)).ToString("0000000000");
// DateTime tmp_StartDate = StartDate;
// DateTime tmp_EndDate = EndDate;
// string s_StartDate = tmp_StartDate.ToShortDateString() + " 00:00:00";
// string s_EndDate = tmp_EndDate.ToShortDateString() + " 23:59:59";
// //先判斷磁卡是否已存在
// cmd = string.Format("select * from tblMagneticCard where ABA='{0}';", tempABA);
// dt = commDB.SelectDBData(cmd);
// if (dt.Rows.Count > 0)
// {
// RoleID = int.Parse(dt.Rows[0]["RoleID"].ToString());
// ////0330刪除已存在，再加的
// //cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0};", RoleID);
// //dtSelect = commDB.SelectDBData(cmd);
// //if (dtSelect.Rows.Count > 0)
// //{
// // cmd = string.Format("delete from tblSysRoleAuthority where RoleID={0};", RoleID);
// // IsSuccess = commDB.ModifyDBData(cmd);
// //}
// //查詢已加的權限不重複加入
// cmd = string.Format("select * from vwEntranceGuardDetail where ERID={0};", ERID);
// dt = commDB.SelectDBData(cmd);
// foreach (DataRow row in dt.Rows)
// {
// cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0} and ControlID='{1}';", RoleID, row["ControlID"].ToString());
// dtSelect = commDB.SelectDBData(cmd);
// if (dtSelect.Rows.Count == 0)
// {
// cmd = string.Format("INSERT INTO tblSysRoleAuthority(RoleID,ControlID) VALUES ({0},'{1}');", RoleID, row["ControlID"].ToString());
// IsSuccess = commDB.ModifyDBData(cmd);
// }
// }
// }
// else
// {
// //1.取得磁卡ABA後，加入tblSysRole磁卡權限群組表
// cmd = string.Format("INSERT INTO tblSysRole(RoleName,UpdateDate) VALUES ('{0}','{1}');", tempABA, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
// IsSuccess = commDB.ModifyDBData(cmd);
// if (IsSuccess)
// {
// cmd = string.Format("select * from tblSysRole where RoleName='{0}';", tempABA);
// dt = commDB.SelectDBData(cmd);
// if (dt.Rows.Count > 0)
// {
// RoleID = int.Parse(dt.Rows[0]["RoleID"].ToString());
// }
// //2.取得磁卡對應的RoleID, 加入tblMagneticCard磁卡資料表
// cmd = string.Format("INSERT INTO tblMagneticCard(ABA,Name,StartDate,EndDate,Type,RoleID,Timestamp,Memo,NormalID) VALUES " +
// "('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}',{8});",
// tempABA, Name, s_StartDate, s_EndDate, 2, RoleID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "施工單號:" + Memo, 0);
// IsSuccess = commDB.ModifyDBData(cmd);
// if (IsSuccess)
// {
// //3.取得此機房的各讀卡機的ControlID及磁卡對應的RoleID，加入tblSysRoleAuthority磁卡權限表，查詢已加的權限不重複加入
// cmd = string.Format("select * from vwEntranceGuardDetail where ERID={0};", ERID);
// dt = commDB.SelectDBData(cmd);
// foreach (DataRow row in dt.Rows)
// {
// cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0} and ControlID='{1}';", RoleID, row["ControlID"].ToString());
// dtSelect = commDB.SelectDBData(cmd);
// if (dtSelect.Rows.Count == 0)
// {
// cmd = string.Format("INSERT INTO tblSysRoleAuthority(RoleID,ControlID) VALUES ({0},'{1}');", RoleID, row["ControlID"].ToString());
// IsSuccess = commDB.ModifyDBData(cmd);
// }
// }
// }
// }
// }
//}
public void Exchange(string MCNSID, int ERID, string ABA, string Name, DateTime StartDate, DateTime EndDate)
{
int RoleID = 0;
clsDBComm commDB = new clsDBComm();
DataTable dt = new DataTable();
DataTable dtSelect = new DataTable();
string cmd = "";
bool IsSuccess = false;
string tempABA = "";
tempABA = (Convert.ToUInt32(ABA)).ToString("0000000000");
DateTime tmp_StartDate = StartDate;
DateTime tmp_EndDate = EndDate;
string s_StartDate = tmp_StartDate.ToString("yyyy-MM-dd HH:mm:ss");
string s_EndDate = tmp_EndDate.ToString("yyyy-MM-dd HH:mm:ss");
//先判斷(1)磁卡是否已存在，(2)是否已啟用，(3)磁卡實際可進機房的時間是否在磁卡有效期限的範圍
cmd = string.Format("select * from tblMagneticCard where ABA='{0}' and Enable='Y'and '{1}' between CardStartDate and CardEndDate and '{2}' between CardStartDate and CardEndDate;", tempABA, s_StartDate, s_EndDate);
dt = commDB.SelectDBData(cmd);
if (dt.Rows.Count > 0)
{
//已存在，先更新-可進入機房時間起迄
cmd = string.Format("UPDATE tblMagneticCard set StartDate ='{0}',EndDate='{1}',Type=2,MCNSID='{2}',NormalID=0,Timestamp='{3}' where ABA='{4}';", s_StartDate, s_EndDate, MCNSID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), tempABA);
IsSuccess = commDB.ModifyDBData(cmd);
RoleID = int.Parse(dt.Rows[0]["RoleID"].ToString());
//查詢已加的權限不重複加入
cmd = string.Format("select * from vwEntranceGuardDetail where ERID={0};", ERID);
dt = commDB.SelectDBData(cmd);
foreach (DataRow row in dt.Rows)
{
cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0} and ControlID='{1}';", RoleID, row["ControlID"].ToString());
dtSelect = commDB.SelectDBData(cmd);
if (dtSelect.Rows.Count == 0)
{
cmd = string.Format("INSERT INTO tblSysRoleAuthority(RoleID,ControlID) VALUES ({0},'{1}');", RoleID, row["ControlID"].ToString());
IsSuccess = commDB.ModifyDBData(cmd);
}
}
//寫入CardCommandLog，ABA,*,C
cmd = string.Format("INSERT INTO [SecureDB].[dbo].[tblCardCommandLog]([ABA],[ControlID],[CommandType],[CardType])VALUES('{0}','*','*','C')", tempABA);
IsSuccess = commDB.ModifyDBData(cmd);
}
else
{
throw new System.Exception("(1)磁卡不存在(2)磁卡未啟用(3)磁卡實際可進機房的時間超過磁卡有效期限的範圍");
}
}
public void WaterBaseValueDailyAvg(string s_year)
{
clsDBComm commDB = new clsDBComm();
DataTable dtYear = new DataTable();
bool IsSuccess = false;
string cmd = "";
//YearData();
string sSelect = "(select CASE MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) when 0 then MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) else CAST(( (MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm))/30 ) AS decimal(18, 3)) END AS CumulateValueAlarm from vwPowerMeter1HourLog where (1=1)and ERID != 4 and year(Timestamp)= ";
for (int ExeCount = 0; ExeCount < dt.Rows.Count; ExeCount++)
{
//先判別是否為新的年份
cmd = string.Format("select * from tblPowerAndWaterMeterYearMonthDailyAvg where YearsNo='{0}' and ERID={1};", s_year, dt.Rows[ExeCount]["ERID"].ToString());
dtYear = commDB.SelectDBData(cmd);
if (dtYear.Rows.Count > 0)
{
try
{
string text = "UPDATE tblPowerAndWaterMeterYearMonthDailyAvg SET ";
text += " Water1MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=1 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water2MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=2 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water3MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=3 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water4MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=4 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water5MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=5 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water6MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=6 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water7MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=7 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water8MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=8 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water9MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=9 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water10MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=10 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water11MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=11 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",Water12MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=12 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",[WaterUpdateDate] = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
text += ",[Memo] = 'SYS' ";
text += " WHERE [YearsNo] = " + s_year + " and [ERID] = " + dt.Rows[ExeCount]["ERID"].ToString();
IsSuccess = commDB.ModifyDBData(text);
}
catch (Exception ex)
{
var message = ex.Message + "," + ex.StackTrace;
throw ex;
}
}
else
{
try
{
string text = "INSERT INTO tblPowerAndWaterMeterYearMonthDailyAvg (YearsNo,ERID,Water1MonthDailyAvg,Water2MonthDailyAvg,Water3MonthDailyAvg,Water4MonthDailyAvg,Water5MonthDailyAvg,Water6MonthDailyAvg,Water7MonthDailyAvg,Water8MonthDailyAvg,Water9MonthDailyAvg,Water10MonthDailyAvg,Water11MonthDailyAvg,Water12MonthDailyAvg,WaterUpdateDate,Memo)VALUES ";
text += "(" + s_year + "," + dt.Rows[ExeCount]["ERID"].ToString();
text += "," + sSelect + s_year + " and month(Timestamp)=1 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=2 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=3 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=4 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=5 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=6 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=7 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=8 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=9 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=10 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=11 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += "," + sSelect + s_year + " and month(Timestamp)=12 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
text += ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
text += ",'SYS') ";
IsSuccess = commDB.ModifyDBData(text);
}
catch (Exception ex)
{
var message = ex.Message + "," + ex.StackTrace;
throw ex;
}
}
}
}