//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 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 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;
}
}
}
}
public static DataTable GetThisPeriodEndDay()
{
clsDBComm commDB = new clsDBComm();
DataTable DT = new DataTable();
try
{
string selectCmd = "";
selectCmd += "select VariableValue";
selectCmd += " From tblSysParameter";
selectCmd += " where VariableName='ThisPeriodEndDay'";
DT = commDB.SelectDBData(selectCmd);
}
catch (Exception ex)
{
var message = ex.Message + "," + ex.StackTrace;
throw ex;
}
return DT;
}
public static DataTable GetAllERNameDate()
{
clsDBComm commDB = new clsDBComm();
DataTable DT = new DataTable();
try
{
string selectCmd = "";
selectCmd += "select ERID";
selectCmd += " From tblPowerMeter";
DT = commDB.SelectDBData(selectCmd);
}
catch (Exception ex)
{
var message = ex.Message + "," + ex.StackTrace;
throw ex;
}
return DT;
}
public System.Collections.Generic.List<R23DoorInfo> GetR23DoorInfo()
{
clsDBComm commDB = new clsDBComm();
DataTable dt = new DataTable();
DataTable dtSelect = new DataTable();
string cmd = "";
SecureService.SecureServiceClient client = new SecureService.SecureServiceClient(new System.ServiceModel.InstanceContext(this), "CustomBinding_ISecureService");
//client.SetR23EngineRoomRecovery("AT");覆歸
cmd = string.Format("select * from tblEngineRoomConfig where ERID not in (18,22,23,24);"); //過濾無門禁功能 18國姓1號東口機房 22國姓1號隧道 23國姓2號隧道 24埔里隧道
dt = commDB.SelectDBData(cmd);
try
{
if (dt.Rows.Count > 0)
{
foreach (DataRow ERData in dt.Rows)
{
R23DoorInfo GDI = new R23DoorInfo();
SecureService.PersonData[] RoomPerson;
RoomPerson = client.GetR23RoomPerson(ERData["ERNo"].ToString());//查看讀卡機人員清單；取最後一人刷進記錄(公司+姓名)
if (RoomPerson.Count() > 0)
{
string sComp = "";
string sName = "";
if (RoomPerson.Last().COMP == null)
{
sComp = "";
}
else
{
sComp = RoomPerson.Last().COMP;
}
if (RoomPerson.Last().NAME == null)
{
sName = "";
}
else
{
sName = RoomPerson.Last().NAME;
}
if (sComp != "")
{
if (sComp == "中工處南投工務段")
sComp = sComp.Substring(3, 2);
else
sComp = sComp.Substring(0, 2);
}
if (sName == "強制開門帳號")
{
sName = "強制開門";
}
if ((sName == "") && (sComp == ""))
{
GDI.StayDoorName = RoomPerson.Last().CARDNO;
}
else
{
GDI.StayDoorName = sComp + sName;
}
}
//else
//{
// GDI.StayDoorName = "none";
//}
cmd = string.Format("select * from vwEntranceGuardDetail where ERName='{0}';", ERData["ERName"].ToString());
dtSelect = commDB.SelectDBData(cmd);
if (dtSelect.Rows.Count > 0)
{
bool SingleHardWareState1 = false;
bool SingleHardWareState0 = false;
foreach (DataRow vwEGDData in dtSelect.Rows)
{
SecureService.ControlStatus ControlConnect;
ControlConnect = client.GetR23ControlConnect(vwEGDData["ControlID"].ToString());//查詢機房硬體狀態
if (ControlConnect.connect) //連線
SingleHardWareState1 = true;
if (!ControlConnect.connect) //斷線
SingleHardWareState0 = true;
byte[] ReaderStatus;
if ((vwEGDData["ControlType"].ToString() == "1") || (vwEGDData["ControlID"].ToString() == "AB-960N-1") || (vwEGDData["ControlID"].ToString() == "AG-960N-1"))
{
if ((bool)vwEGDData["IsEnable"])
{
//查看機房ADAM之狀態
if ((vwEGDData["ControlID"].ToString() == "AB-960N-1") || (vwEGDData["ControlID"].ToString() == "AG-960N-1"))//苗栗or中交控
{
//ReaderStatus = client.GetR23ReaderStatus(vwEGDData["ERNo"].ToString());//("AB"/"AG");
if (RoomPerson.Count() > 0)
{
GDI.IsShowPerson = true;
GDI.state = 1;//有人逗留
GDI.stateAlarm = 1;
}
else
{
GDI.IsShowPerson = false;
GDI.state = 0; //無人在內
GDI.stateAlarm = 2;//保全啟動
}
}
else//非苗栗or中交控 無控制器 讀取讀卡機
{
ReaderStatus = client.GetR23ReaderStatus(vwEGDData["ControlID"].ToString());//("AN-2400N-1");
if (ReaderStatus != null)
{
if (ReaderStatus[32] != 0)
{
if (RoomPerson.Count() > 0) //查讀卡機是否有人員逗留
{
GDI.IsShowPerson = true;
GDI.state = 1; //有人逗留
if (ReaderStatus[32] == 2)//當收到狀態為「保全啟動」在此要強制把stateAlarm設定為「人員逗留」狀態
GDI.stateAlarm = 1; //有人逗留
else
GDI.stateAlarm = ReaderStatus[32];
}
else
{
if (ReaderStatus[32] == 1) //門禁被開啟
{
GDI.StayDoorName = "門禁被開啟";
GDI.state = 1; //有人逗留
GDI.IsShowPerson = true;
}
else
{
GDI.IsShowPerson = false;
GDI.state = 0; //無人在內
}
GDI.stateAlarm = ReaderStatus[32]; //保全警報
}
}
else
{
GDI.IsShowPerson = false;
GDI.state = 3; //斷線
GDI.stateAlarm = ReaderStatus[32]; //保全警報
}
}
}
}
}
}
if ((SingleHardWareState1) && (SingleHardWareState0))
{
GDI.ERName = ERData["ERName"].ToString();
//為硬體異常(只要state有一個為true)
GDI.stateHardware = 1;
}
else if ((SingleHardWareState1) && (!SingleHardWareState0))
{
GDI.ERName = ERData["ERName"].ToString();
//為硬體正常
GDI.stateHardware = 0;
}
else
{
//全部顯示斷線
GDI.ERName = ERData["ERName"].ToString();
GDI.stateHardware = 2;
GDI.IsShowPerson = false;
GDI.state = 3;
GDI.stateAlarm = 0;
}
d_R23DoorInfo.Add(GDI);
}
//ControlConnect = client.GetR23ControlConnect("AA-2400N-1");
//RoomPerson = client.GetR23RoomPerson("AA");
//ReaderStatus = client.GetR23ReaderStatus("AA-2400N-1");
//if (RoomPerson.Count() > 0)
//{
// string aa = RoomPerson.Last().COMP;
//}
//string status = ReaderStatus[32].ToString();
//GDI.ERName = row["ERName"].ToString();
//GDI.stateHardware = 0;
//GDI.IsShowPerson = false;
//GDI.StayDoorName = "";
//GDI.state = 0;
//GDI.stateAlarm = ReaderStatus[32];
//if (!d_R23DoorInfo.ContainsKey(row["ERID"].ToString()))
//{
// d_R23DoorInfo.Add(row["ERID"].ToString(), GDI);
//}
}
}
}
catch (Exception ex)
{
var message = ex.Message + "," + ex.StackTrace;
throw ex;
}
return d_R23DoorInfo;
}