static void DatabaseAcces()
{
dbroomEntities dbroom = new dbroomEntities();
while(!End || cmdQueue.Count > 0) {
if (cmdQueue.Count > 0)
{
string cmd = cmdQueue.Dequeue();
try
{
dbroom.Database.ExecuteSqlCommand(cmd);
}
catch(Exception ex)
{
TCommon.SaveLog(ex.Message + "\r\n" + cmd);
}
}
if (cmdQueue.Count == 0)
{
wait.WaitOne(1000);
}
}
}
internal static void ReloadPersonName()
{
dbroomEntities dbroom = new dbroomEntities();
var PerosnDatas = from o in dbroom.tblMagneticCard select o;//更新卡片人員對應表
foreach (var Person in PerosnDatas)
{
if (!ServerData.PersonnelData.ContainsKey(Person.ABA))
{
ServerData.PersonnelData.Add(Person.ABA, Person.Name);
}
else
{
if (ServerData.PersonnelData[Person.ABA] != (Person.Name))
{
ServerData.PersonnelData[Person.ABA] = Person.Name;
}
}
if (!ServerData.PersonnelCompData.ContainsKey(Person.ABA))
{
ServerData.PersonnelCompData.Add(Person.ABA, Person.Company);
}
else
{
if (ServerData.PersonnelCompData[Person.ABA] != Person.Company)
{
ServerData.PersonnelCompData[Person.ABA] = Person.Company;
}
}
}
}
//internal event RoomInterface.RoomEventHandler StatusChangeEvent;
public ADAMControl(string name, string ip, int prot)
{
Name = name;
IP = ip;
Port = prot;
timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
byte[] IOStatus = new byte[33];
dbroomEntities dbroom = new dbroomEntities();
try
{
var data = from o in dbroom.tblDeviceStateLog where o.ControlID == name && o.TypeID == 4 orderby o.TimeStamp descending select o.TypeCode;
if (data.Count() > 0)
{
IOStatus[32] = Convert.ToByte(data.First());
}
}
catch (Exception ex)
{
TCommon.SaveLog(ex.Message + "\r\n" + ex.StackTrace);
}
for (int i = 0; i < 16; i++)
{
string No = i.ToString();
try
{
var data = from o in dbroom.tblSingalIOLog
where o.ControlID == name && o.SingalName == "DI-" + No
orderby o.TimeStamp descending
select o.Status;
if (data.Count() > 0)
{
IOStatus[i] = Convert.ToByte(data.First());
}
}
catch (Exception ex)
{
TCommon.SaveLog(ex.Message + "\r\n" + ex.StackTrace);
}
try
{
var data = from o in dbroom.tblSingalIOLog
where o.ControlID == name && o.SingalName == "DO-" + No
orderby o.TimeStamp descending
select o.Status;
if (data.Count() > 0)
{
IOStatus[i + 16] = Convert.ToByte(data.First());
}
}
catch (Exception ex)
{
TCommon.SaveLog(ex.Message + "\r\n" + ex.StackTrace);
}
}
ServerData.ADAMStatus.Add(name, IOStatus);
//SetAlarmTime(30, 30, 30);
}
public bool OpenDoor(string ControlID, int DoorNum,string UserID)
{
bool ok;
try
{
if (ServerData.ADAMController.ContainsKey(ControlID))
{
ADAMControl control = ServerData.ADAMController[ControlID];
ok = control.OpenDoor(DoorNum);
if (ok && DoorNum == 0)
{
string RoomID = ControlID.Split('-')[0];
if (ServerData.RoomPerson.ContainsKey(RoomID))
{
try
{
dbroomEntities dbroom = new dbroomEntities();
string UserName = (from o in dbroom.tblUser where o.UserID == UserID select o.UserName).First();
ServerData.RoomPerson[RoomID].Add(new RoomCardData(UserID,UserName,"交控操作人員",true,DateTime.Now,true));
List<RoomCardData> RoomPersonList = ServerData.RoomPerson[RoomID];
List<RoomInterface.PersonData> PersonList = new List<RoomInterface.PersonData>();
lock (RoomPersonList)
{
foreach (RoomCardData data in RoomPersonList)
{
if (data.In)
{
PersonList.Add(new RoomInterface.PersonData(data.CardID, data.Name,data.Company, data.LastTime, data.IsManual));
}
}
}
ServerData.SendRoomEvent(RoomInterface.ControllEventType.RoomPersonChange, RoomID, PersonList);
}
catch
{ ; }
}
}
}
else
ok = false;
}
catch
{
ok = false;
}
string cmd = string.Format("Insert into tbldeviceStateLog (TypeID,TypeCode,TimeStamp,ControlID,SingalName) "
+ "Values({0},{1},'{2}','{3}','{4}');", 6, ok ? 0 : 1, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), ControlID,"DO-" + DoorNum);
DatabaseAccess.DatabaseAcces(cmd);
return ok;
}
internal static void setGroup(object obj)
{
GroupMessage tmpGroupMessage = (GroupMessage)obj;
List<int> GroupIDs = tmpGroupMessage.GroupIDs;
groupMessage.GroupProgress[0] = 0;
groupMessage.errorMessage[0] = groupMessage.errorMessage[1] = "";
lock (ServerData.RAC2400Controller)
{
int Progress = 0;
string ErrorMessage = string.Empty;
List<string> offLineControlList = new List<string>();
//check Group Change
dbroomEntities dbroom = new dbroomEntities();
foreach (int groupID in GroupIDs)
{
var ControlIDs = from o in dbroom.tblSysRoleAuthority where o.RoleID == groupID && o.Enable == "Y" select o.ControlID;
List<string> newControlID = ControlIDs.ToList();
if (!ServerData.GroupDataDic.ContainsKey(groupID)) //新增群組
{
ServerData.GroupDataDic.Add(groupID, new GroupData(groupID));
ServerData.GroupCardDic.Add(groupID, new List<GroupCardData>());
var NewGroupCards = from o in dbroom.tblMagneticCard where o.RoleID == groupID select o;
foreach (var newGroupCard in NewGroupCards)
{
ServerData.GroupCardDic[groupID].Add(new GroupCardData(newGroupCard.ABA, newGroupCard.StartDate, newGroupCard.EndDate, groupID));
}
foreach (string GroupData in ControlIDs)
{
ServerData.GroupDataDic[groupID].ControlID.Add(GroupData);
}
}
GroupData groupdata = ServerData.GroupDataDic[groupID];
List<string> addList = TCommon.GetAdderList(groupdata.ControlID, newControlID);
List<string> delList = TCommon.GetAdderList(newControlID, groupdata.ControlID);
if (!ServerData.GroupCardDic.ContainsKey(groupID))
{
ServerData.GroupCardDic.Add(groupID, new List<GroupCardData>());
}
List<GroupCardData> cardList = ServerData.GroupCardDic[groupID];
groupMessage.errorMessage[1] += "確認群組控制器名單\r\n";
#region 群組移除控制器
foreach (string del in delList)
{
try
{
if (offLineControlList.Contains(del)) //斷線控制器不動作
continue;
groupMessage.errorMessage[1] += "群組移除控制器" + ServerData.ControlName[del] + "\r\n";
CardControl rac2400 = null;
RAC960CardControl rac960 = null;
if (ServerData.RAC2400Controller.ContainsKey(del))
{
rac2400 = ServerData.RAC2400Controller[del];
}
else if (ServerData.RAC960Controller.ContainsKey(del))
{
rac960 = ServerData.RAC960Controller[del];
}
if (ServerData.ControlCardDic.ContainsKey(del))
{
List<string> ControlCardList = ServerData.ControlCardDic[del];
foreach (GroupCardData newCard in cardList)
{
try
{
if (ControlCardList.Contains(newCard.ABA))
{
bool? ok = null;
if (rac2400 != null)
{
ok = rac2400.DelUserData(newCard.ABA);
if (ok == true)
ok = rac2400.DelUserData(TCommon.GetWEG(newCard.ABA));
}
else if (rac960 != null)
ok = rac960.DelUserData(newCard.ABA);
if (!ok.HasValue)
{
offLineControlList.Add(del);
groupMessage.errorMessage[1] += "控制器" + ServerData.ControlName[del] + "斷線\r\n";
continue;
}
else if (!ok.Value)
{
ErrorMessage += newCard.ABA + " 在" + ServerData.ControlName[del] + " 刪除失敗\r\n";
groupMessage.errorMessage[1] += ServerData.ControlName[del] + "刪除卡號" + newCard.ABA + "失敗\r\n";
}
else
{
if (rac960 != null)
{
string cmd = string.Format("Delete from tblControllerCard where ControlID = '{0}' and ABA = '{1}';", del, newCard.ABA);
DatabaseAccess.DatabaseAcces(cmd);
}
ControlCardList.Remove(newCard.ABA);
groupMessage.errorMessage[1] += ServerData.ControlName[del] + "刪除卡號" + newCard.ABA + "成功\r\n";
}
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
}
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
}
groupMessage.GroupProgress[0] += 100 / (GroupIDs.Count * 3);
#endregion
#region 群組新增控制器
foreach (string add in addList)
{
if (offLineControlList.Contains(add)) //斷線控制器不動作
continue;
groupMessage.errorMessage[1] += "群組增加控制器" + ServerData.ControlName[add] + "\r\n";
CardControl rac2400 = null;
RAC960CardControl rac960 = null;
if (ServerData.RAC2400Controller.ContainsKey(add))
{
rac2400 = ServerData.RAC2400Controller[add];
}
else if (ServerData.RAC960Controller.ContainsKey(add))
{
rac960 = ServerData.RAC960Controller[add];
}
if (ServerData.ControlCardDic.ContainsKey(add))//
{
List<string> ControlCardList = ServerData.ControlCardDic[add];
foreach (GroupCardData newCard in cardList)
{
try
{
if (DateTime.Now > newCard.StartDate && DateTime.Now < newCard.EndDate)
{
if (!ControlCardList.Contains(newCard.ABA))
{
bool? ok = null;
if (rac2400 != null)
{
ok = rac2400.AddUserData(newCard.ABA, ServerData.GetCardName(newCard.ABA), "1111");
if (ok == true)
ok = rac2400.AddUserData(TCommon.GetWEG(newCard.ABA), ServerData.GetCardName(newCard.ABA), "1111");
}
else if (rac960 != null)
ok = rac960.AddUserData(newCard.ABA, ServerData.GetCardName(newCard.ABA));
if (!ok.HasValue)
{
//offLineControlList.Add(add);
//groupMessage.errorMessage[1] += "控制器" +ServerData.ControlName[add] + "斷線\r\n";
//continue;
ErrorMessage += newCard.ABA + " 在" + ServerData.ControlName[add] + " 新增失敗\r\n";
groupMessage.errorMessage[1] += ServerData.ControlName[add] + "新增卡號" + newCard.ABA + "失敗\r\n";
}
else if (!ok.Value)
{
ErrorMessage += newCard.ABA + " 在" + ServerData.ControlName[add] + " 新增失敗\r\n";
groupMessage.errorMessage[1] += ServerData.ControlName[add] + "新增卡號" + newCard.ABA + "失敗\r\n";
}
else
{
if (rac960 != null)
{
string cmd = string.Format("Insert Into tblControllerCard (ControlID,ABA) Values('{0}','{1}');", add, newCard.ABA);
DatabaseAccess.DatabaseAcces(cmd);
}
ControlCardList.Add(newCard.ABA);
groupMessage.errorMessage[1] += ServerData.ControlName[add] + "新增卡號" + newCard.ABA + "成功\r\n";
}
}
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
}
}
else
{
ServerData.ControlCardDic.Add(add, new List<string>());
foreach (GroupCardData newCard in cardList)
{
try
{
if (DateTime.Now > newCard.StartDate && DateTime.Now < newCard.EndDate)
{
bool? ok = null;
if (rac2400 != null)
{
ok = rac2400.AddUserData(newCard.ABA, ServerData.GetCardName(newCard.ABA), "1111");
if (ok == true)
ok = rac2400.AddUserData(TCommon.GetWEG(newCard.ABA), ServerData.GetCardName(newCard.ABA), "1111");
}
else if (rac960 != null)
ok = rac960.AddUserData(newCard.ABA, ServerData.GetCardName(newCard.ABA));
if (!ok.HasValue)
{
//offLineControlList.Add(add);
//groupMessage.errorMessage[1] += "控制器" + ServerData.ControlName[add] + "斷線\r\n";
//continue;
groupMessage.errorMessage[1] += ServerData.ControlName[add] + "新增卡號" + newCard.ABA + "失敗\r\n";
ErrorMessage += newCard.ABA + " 在" + ServerData.ControlName[add] + " 新增失敗\r\n";
}
else if (!ok.Value)
{
groupMessage.errorMessage[1] += ServerData.ControlName[add] + "新增卡號" + newCard.ABA + "失敗\r\n";
ErrorMessage += newCard.ABA + " 在" + ServerData.ControlName[add] + " 新增失敗\r\n";
}
else
{
if (rac960 != null)
{
string cmd = string.Format("Insert Into tblControllerCard (ControlID,ABA) Values('{0}','{1}');", add, newCard.ABA);
DatabaseAccess.DatabaseAcces(cmd);
}
groupMessage.errorMessage[1] += ServerData.ControlName[add] + "新增卡號" + newCard.ABA + "成功\r\n";
ServerData.ControlCardDic[add].Add(newCard.ABA);
}
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
}
}
}
groupMessage.GroupProgress[0] += 100 / (GroupIDs.Count * 3);
#endregion
groupdata.ControlID = newControlID;
//群組內卡片變更
var GroupCards = from o in dbroom.tblMagneticCard where o.RoleID == groupID && o.Enable == "Y" select o;
if (!ServerData.GroupCardDic.ContainsKey(groupID))
{
ServerData.GroupCardDic.Add(groupID, new List<GroupCardData>());
}
List<GroupCardData> groupCard = ServerData.GroupCardDic[groupID];
lock (groupCard)
{
groupCard.Clear();
foreach (var newGroupCard in GroupCards)
{
groupCard.Add(new GroupCardData(newGroupCard.ABA, newGroupCard.StartDate, newGroupCard.EndDate, groupID));
}
}
foreach (string controlID in ServerData.GroupDataDic[groupID].ControlID)
{
try
{
int ControlCooect = 0;
if (offLineControlList.Contains(controlID))
{
groupMessage.GroupProgress[0] += 100 / (GroupIDs.Count * 3 * ServerData.GroupDataDic[groupID].ControlID.Count);
continue;
}
List<string> NewControlCards = new List<string>();
var NewDataControlCards = from role in dbroom.tblSysRoleAuthority
join Magnetic in dbroom.tblMagneticCard on role.RoleID equals Magnetic.RoleID
where role.ControlID == controlID && role.Enable == "Y" && Magnetic.Enable == "Y"
select new { ABA = Magnetic.ABA, StartDate = Magnetic.StartDate, EndDate = Magnetic.Enable };
foreach (var Card in NewDataControlCards)
{
if (DateTime.Now >= Card.StartDate && DateTime.Now <= Convert.ToDateTime(Card.EndDate))
{
NewControlCards.Add(Card.ABA);
}
}
CardControl rac2400 = null;
RAC960CardControl rac960 = null;
if (ServerData.RAC2400Controller.ContainsKey(controlID))
{
rac2400 = ServerData.RAC2400Controller[controlID];
}
else if (ServerData.RAC960Controller.ContainsKey(controlID))
{
rac960 = ServerData.RAC960Controller[controlID];
}
List<string> ControlCards = ServerData.ControlCardDic[controlID];
List<string> AddCardList = TCommon.GetAdderList(ControlCards, NewControlCards);
List<string> DelCardList = TCommon.GetAdderList(NewControlCards, ControlCards);
//if (rac2400 != null && AddCardList.Count > 5)
//{
// string ListErrorMessage = rac2400.AddUserData(AddCardList);
// if (string.IsNullOrWhiteSpace(ListErrorMessage))
// {
// ControlCards.AddRange(AddCardList);
// }
// groupMessage.errorMessage[1] += ListErrorMessage;
//}
//else
//{
foreach (string addCard in AddCardList)
{
try
{
bool? ok = null;
if (rac2400 != null)
{
ok = rac2400.AddUserData(addCard, ServerData.GetCardName(addCard), "1111");
if (!ok.HasValue || !ok.Value)
{
wait.WaitOne(3000);
ok = rac2400.AddUserData(addCard, ServerData.GetCardName(addCard), "1111");
}
if (ok == true)
{
ok = rac2400.AddUserData(TCommon.GetWEG(addCard), ServerData.GetCardName(addCard), "1111");
if (!ok.HasValue || !ok.Value)
{
wait.WaitOne(3000);
ok = rac2400.AddUserData(TCommon.GetWEG(addCard), ServerData.GetCardName(addCard), "1111");
}
}
}
else if (rac960 != null)
{
ok = rac960.AddUserData(addCard, ServerData.GetCardName(addCard));
if (!ok.HasValue || !ok.Value)
{
wait.WaitOne(3000);
ok = rac960.AddUserData(addCard, ServerData.GetCardName(addCard));
}
}
if (ok.HasValue == false)
{
////offLineControlList.Add(controlID);
//groupMessage.errorMessage[1] += "控制器" + ServerData.ControlName[controlID] + "斷線\r\n";
if (ControlCooect < 10)
{
groupMessage.errorMessage[1] += ServerData.ControlName[controlID] + "新增卡號" + addCard + "失敗\r\n";
ErrorMessage += addCard + " 在" + ServerData.ControlName[controlID] + " 新增失敗\r\n";
ControlCooect++;
TCommon.SaveLog(addCard + " 在" + ServerData.ControlName[controlID] + " 新增失敗\r\n");
}
else
{
offLineControlList.Add(controlID);
groupMessage.errorMessage[1] += "控制器" + ServerData.ControlName[controlID] + "斷線\r\n";
TCommon.SaveLog("控制器" + ServerData.ControlName[controlID] + "斷線\r\n");
break;
}
}
else if (ok.Value == false)
{
groupMessage.errorMessage[1] += ServerData.ControlName[controlID] + "新增卡號" + addCard + "失敗\r\n";
ErrorMessage += addCard + " 在" + ServerData.ControlName[controlID] + " 新增失敗\r\n";
TCommon.SaveLog(addCard + " 在" + ServerData.ControlName[controlID] + " 新增失敗\r\n");
}
else
{
if (rac960 != null)
{
string cmd = string.Format("Insert Into tblControllerCard (ControlID,ABA) Values('{0}','{1}');", controlID, addCard);
DatabaseAccess.DatabaseAcces(cmd);
}
groupMessage.errorMessage[1] += ServerData.ControlName[controlID] + "新增卡號" + addCard + "成功\r\n";
TCommon.SaveLog(ServerData.ControlName[controlID] + "新增卡號" + addCard + "成功\r\n");
ControlCards.Add(addCard);
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
}
//}
foreach (string delCard in DelCardList)
{
try
{
bool? ok = null;
if (rac2400 != null)
{
ok = rac2400.DelUserData(delCard);
if (!ok.HasValue || !ok.Value)
{
wait.WaitOne(3000);
ok = rac2400.DelUserData(delCard);
}
if (ok == true)
{
ok = rac2400.DelUserData(TCommon.GetWEG(delCard));
if (!ok.HasValue || !ok.Value)
{
wait.WaitOne(3000);
ok = rac2400.DelUserData(TCommon.GetWEG(delCard));
}
}
}
else if (rac960 != null)
{
ok = rac960.DelUserData(delCard);
if (!ok.HasValue || !ok.Value)
{
ok = rac960.DelUserData(delCard);
}
}
if (ok.HasValue == false)
{
if (ControlCooect < 10)
{
groupMessage.errorMessage[1] += ServerData.ControlName[controlID] + "刪除卡號" + delCard + "失敗\r\n";
ErrorMessage += delCard + " 在" + ServerData.ControlName[controlID] + " 刪除失敗\r\n";
ControlCooect++;
}
else
{
offLineControlList.Add(controlID);
groupMessage.errorMessage[1] += "控制器" + ServerData.ControlName[controlID] + "斷線\r\n";
break;
}
}
else if (ok.Value == false)
{
groupMessage.errorMessage[1] += ServerData.ControlName[controlID] + "刪除卡號" + delCard + "失敗\r\n";
ErrorMessage += delCard + " 在" + ServerData.ControlName[controlID] + " 刪除失敗\r\n";
}
else
{
if (rac960 != null)
{
string cmd = string.Format("Delete from tblControllerCard where ControlID = '{0}' and ABA = '{1}';", controlID, delCard);
DatabaseAccess.DatabaseAcces(cmd);
}
groupMessage.errorMessage[1] += ServerData.ControlName[controlID] + "刪除卡號" + delCard + "成功\r\n";
ControlCards.Remove(delCard);
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@".\Error.log", ex.Message + "\r\n" + ex.StackTrace);
}
groupMessage.GroupProgress[0] += 100 / (GroupIDs.Count * 3 * ServerData.GroupDataDic[groupID].ControlID.Count);
}
groupMessage.GroupProgress[0] = (Progress++ * 100) / GroupIDs.Count;
}
foreach (string offLint in offLineControlList)
{
string controlName = offLint;
if (ServerData.ControlName.ContainsKey(offLint))
{
controlName = ServerData.ControlName[offLint];
}
ErrorMessage = controlName + "斷線\r\n" + ErrorMessage;
}
System.IO.File.AppendAllText(@".\GroupSet.Log", DateTime.Now + "\r\n" + ErrorMessage);
groupMessage.errorMessage[1] += "全部下載完成";
groupMessage.GroupProgress[0] = 100;
groupMessage.errorMessage[0] = ErrorMessage;
}
}
DataTable LoadExcelSaveDataBase()
{
OpenFileDialog LoadFileDialog = new OpenFileDialog();
LoadFileDialog.Filter = "xls|*.xls";
DataTable CardDT = new DataTable();
CardDT.Columns.Add("ABA_num");
CardDT.Columns.Add("WEG_num");
CardDT.Columns.Add("Message");
CardDT.Columns.Add("Add");
CardDT.Columns.Add("Memo");
if (LoadFileDialog.ShowDialog() == DialogResult.OK)
{
System.IO.FileInfo LoadFile = new System.IO.FileInfo(LoadFileDialog.FileName);
if (!LoadFile.Exists)
{
MessageBox.Show("檔案不存在");
return CardDT;
}
DataTable DT = null;
string strConn = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=" + LoadFileDialog.FileName + ";Extended Properties='Excel 8.0;IMEX = 1;'";
OleDbConnection Conn = new OleDbConnection(strConn);
try
{
Conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return CardDT;
}
Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Jet\4.0\Engines\Excel", true);
object keyValue = key.GetValue("TypeGuessRows");
//try
//{
key.SetValue("TypeGuessRows", 16);
DataSet ds = new DataSet();
//clsDBComm commDB = new clsDBComm();
DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
{
dt.Rows[0]["TABLE_Name"] += "$";
}
int RowIndex = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["TABLE_Name"].Equals("'20111022_門禁發卡編號$'"))
{
RowIndex = i;
break;
}
}
string strSelect = "Select * From [" + dt.Rows[RowIndex]["TABLE_Name"].ToString() + "] ";
OleDbDataAdapter da = new OleDbDataAdapter(strSelect, strConn);
da.Fill(ds, dt.Rows[RowIndex]["TABLE_Name"].ToString());
da.Dispose();
Conn.Close();
Conn.Dispose();
//ds.Tables[dt.Rows[0]["TABLE_Name"].ToString()].Rows.RemoveAt(0);
dbroomEntities dbroom = new dbroomEntities();
//tblControllerConfig NewControll = new tblControllerConfig();
//NewControll.ControlID = "TEST-1";
//NewControll.ControlType = 1;
//NewControll.ERId = 1;
//dbroom.AddTotblControllerConfigs(NewControll);
//tblEntranceGuardConfig newEgc = new tblEntranceGuardConfig();
//newEgc.EntranceCode = "TEST-1-1";
//newEgc.ERId = 1;
//newEgc.Floor = 1;
//newEgc.EntranceType = "1";
//newEgc.Memo = "TEST";
//dbroom.AddTotblEntranceGuardConfigs(newEgc);
//tblCardReaderConfig newReader = new tblCardReaderConfig();
//newReader.ReaderID = 1;
//newReader.EntranceCode ="TEST-1-1";
//newReader.ControlID = "TEST-1";
//newReader.ReaderType = 1;
//newReader.IP = "0.0.0.0";
//newReader.Port = 6666;
//dbroom.AddTotblCardReaderConfigs(newReader);
DT = ds.Tables[dt.Rows[RowIndex]["TABLE_Name"].ToString()];
foreach (DataRow dr in DT.Rows)
{
if (dr[2] == DBNull.Value || dr[7] == DBNull.Value)
continue;
DataRow CardRow = CardDT.NewRow();
CardRow["ABA_num"] = dr[1].ToString() + dr[2].ToString();
CardRow["WEG_num"] = "00" + dr[3].ToString() + dr[4].ToString();
CardRow["Message"] = dr[6].Equals(DBNull.Value) ? dr[5] : dr[6].ToString();
CardRow["Add"] = dr[7];
CardRow["Memo"] = dr[8];
CardDT.Rows.Add(CardRow);
int roleID;
string GroupName = dr[5].Equals(DBNull.Value) ? "交控中心" : dr[5].ToString();
var data = from o in dbroom.tblSysRole where o.RoleName == GroupName select o;
if (data.Count() == 0)
{
tblSysRole newRole = new tblSysRole();
int num = (from o in dbroom.tblSysRole select o).Count() + 1;
newRole.RoleID = num;
newRole.RoleName = dr[5].Equals(DBNull.Value) ? "交控中心" : dr[5].ToString();
dbroom.tblSysRole.Add(newRole);
dbroom.SaveChanges();
roleID = num;
}
else
{
roleID = data.First().RoleID;
}
//tblSysUser newUser = new tblSysUser();
//dbroom.ExecuteFunction("delete from tblSysUsers;");
//int UserNum = (from o in dbroom.tblSysUsers select o).Count() + 1;
//newUser.UserId = UserNum.ToString();
//newUser.UserName = dr[6].Equals(DBNull.Value) ? string.Empty : dr[6].ToString();
//newUser.RoleId = roleID;
//newUser.Enable = "Y";
//dbroom.AddTotblSysUsers(newUser);
string aba = CardRow["ABA_num"].ToString();
var CardData = from o in dbroom.tblMagneticCard where o.ABA == aba select o;
if (CardData.Count() > 0)
{
var oldCard = CardData.First();
oldCard.RoleID = roleID;
oldCard.Name = dr[6].Equals(DBNull.Value) ? string.Empty : dr[6].ToString();
oldCard.Company = dr[5].Equals(DBNull.Value) ? string.Empty : dr[5].ToString();
}
else
{
tblMagneticCard newCard = new tblMagneticCard();
newCard.MagneticID = (from o in dbroom.tblMagneticCard select o).Count() + 1;
newCard.ABA = aba;
newCard.WEG1 = "00" + dr[3].ToString();
newCard.WEG2 = dr[4].ToString();
newCard.RoleID = roleID;
newCard.Name = dr[6].Equals(DBNull.Value) ? string.Empty : dr[6].ToString();
newCard.Company = dr[5].Equals(DBNull.Value) ? string.Empty : dr[5].ToString();
newCard.Enable = "Y";
newCard.Memo = dr[8].ToString();
dbroom.tblMagneticCard.Add(newCard);
}
dbroom.SaveChanges();
}
//}
//catch (Exception ex)
//{
// Conn.Close();
// Conn.Dispose();
// MessageBox.Show(ex.Message);
//}
//finally
//{
key.SetValue("TypeGuessRows", keyValue);
//}
}
else
{
this.Close();
return null;
}
return CardDT;
}
void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
if (DateTime.Now.Day != LastTime.Day)//
{
LastTime = DateTime.Now;
foreach (var rac2400 in ServerData.RAC2400Controller)
{
rac2400.Value.SetTime();
}
foreach (var rac960 in ServerData.RAC960Controller)
{
rac960.Value.SetTime();
}
dbroomEntities dbroom = new dbroomEntities();
foreach (var RoomData in ServerData.RoomPerson) //刪除機房人員多餘資料
{
List<RoomCardData> RoomPersonList = RoomData.Value;
lock (RoomPersonList)
{
for (int i = 0; i < RoomPersonList.Count; i++)
{
if (RoomPersonList[i].In == false && RoomPersonList[i].LastTime < DateTime.Now.AddDays(-2))
{
RoomPersonList.RemoveAt(i--);
}
}
}
}
var PerosnDatas = from o in dbroom.tblMagneticCard select o;//更新卡片人員對應表
foreach (var Person in PerosnDatas)
{
if (!ServerData.PersonnelData.ContainsKey(Person.ABA))
{
ServerData.PersonnelData.Add(Person.ABA, Person.Name == "" ? Person.Company : Person.Name);
}
else
{
if (ServerData.PersonnelData[Person.ABA] != (Person.Name == "" ? Person.Company : Person.Name))
{
ServerData.PersonnelData[Person.ABA] = Person.Name == "" ? Person.Company : Person.Name;
}
}
}
Dictionary<string, string> WegDict = new Dictionary<string, string>();
var wegDatas = from o in dbroom.tblMagneticCard select o;
foreach (var weg in wegDatas)
{
string Weg = weg.WEG1 + weg.WEG2;
if (!WegDict.ContainsKey(Weg))
{
WegDict.Add(Weg, weg.ABA);
}
}
//載入讀卡機現有卡片
foreach (var rac2400 in ServerData.RAC2400Controller)
{
List<CardData> NowList = rac2400.Value.ReadAllUserData();
if (NowList != null)
{
List<string> NewCardList = new List<string>();
Dictionary<string, int> NewCardDict = new Dictionary<string, int>();
foreach (CardData card in NowList)
{
if (!WegDict.ContainsKey(card.CarNum))
{
if (!NewCardDict.ContainsKey(card.CarNum))
{
NewCardDict.Add(card.CarNum, 1);
}
else
{
NewCardDict[card.CarNum]++;
}
}
else
{
if (!NewCardDict.ContainsKey(WegDict[card.CarNum]))
{
NewCardDict.Add(WegDict[card.CarNum], 1);
}
else
{
NewCardDict[WegDict[card.CarNum]]++;
}
}
}
foreach (var data in NewCardDict)
{
if (data.Value > 1)
{
NewCardList.Add(data.Key);
}
}
//foreach (CardData card in NowList)
//{
// if (!WegDict.ContainsKey(card.CarNum) )
// {
// NewCardList.Add(card.CarNum);
// }
//}
if (!ServerData.ControlCardDic.ContainsKey(rac2400.Key))
{
ServerData.ControlCardDic.Add(rac2400.Key, new List<string>());
}
List<string> PersonList = ServerData.ControlCardDic[rac2400.Key];
lock (PersonList)
{
PersonList.Clear();
PersonList.AddRange(NewCardList);
}
}
//List<string> PersonList = ServerData.ControlCardDic[rac2400.Key];
//lock (PersonList)
//{
// PersonList.Clear();
// //PersonList.AddRange(NewCardList);
//}
}
foreach (var rac960 in ServerData.RAC960Controller)
{
var Rac960Cards = from o in dbroom.tblControllerCard where o.ControlID == rac960.Key select o;
List<string> NowList = new List<string>();
foreach (var rac960Card in Rac960Cards)
{
NowList.Add(rac960Card.ABA);
}
List<string> PersonList = ServerData.ControlCardDic[rac960.Key];
lock (PersonList)
{
PersonList.Clear();
PersonList.AddRange(NowList);
}
//List<string> Rac960List = rac960.Value.ReadAllUserData();
//if (Rac960List != null)
//{
// List<string> NowList = new List<string>();
// foreach (string rac960Card in Rac960List)
// {
// NowList.Add(rac960Card.Split(',')[0]);
// }
// if (!ServerData.ControlCardDic.ContainsKey(rac960.Key))
// {
// ServerData.ControlCardDic.Add(rac960.Key, new List<string>());
// }
// List<string> PersonList = ServerData.ControlCardDic[rac960.Key];
// lock (PersonList)
// {
// PersonList.Clear();
// PersonList.AddRange(NowList);
// }
//}
//List<string> PersonList = ServerData.ControlCardDic[rac960.Key];
//lock (PersonList)
//{
// PersonList.Clear();
//}
}
//重設讀卡機資料
var GroupDatas = from o in dbroom.tblSysRole select o;
List<int> GroupIDs = new List<int>();
foreach (var GroupData in GroupDatas)
{
GroupIDs.Add(GroupData.RoleID);
}
int[] GroupProgress = new int[1]{0};
string[] errorMessage = new string[2] {"",""};
//object[] obj = new object[3];
//obj[0] = GroupIDs;
//obj[1] = GroupProgress;
//obj[2] = errorMessage;
GroupMessage groupMessage = new GroupMessage();
groupMessage.GroupIDs = GroupIDs;
groupMessage.GroupProgress = GroupProgress;
groupMessage.errorMessage = errorMessage;
RoomObj.setGroup(groupMessage);
}
LastTime = DateTime.Now;
ServerData.SendRoomEvent(RoomInterface.ControllEventType.TimeConnect, string.Empty, null);
}
catch (Exception ex)
{
TCommon.SaveLog(ex.Message + "\r\n" + ex.StackTrace);
}
}
void ReadServerData()
{
dbroomEntities dbroom = new dbroomEntities();
var ControllerData = from o in dbroom.tblControllerConfig select o;
foreach (var data in ControllerData)
{
try
{
if (data.IP != "0.0.0.0")
{
#region 控制器
switch (data.ControlType)
{
case 1:
if (!ServerData.RAC2400Controller.ContainsKey(data.ControlID))
{
var Room = (from room in dbroom.tblEngineRoomConfig
where room.ERID == data.ERID
select room).First();
string RoomName = Room.ERName;
var readerDatas = from reader in dbroom.tblCardReaderConfig
where reader.ControlID == data.ControlID
select reader;
byte[] readerType = new byte[4];
int[] readerID = new int[4];
byte[] readerIO = new byte[4];
foreach (var readData in readerDatas)
{
byte NO = Convert.ToByte(readData.ReaderNO);
byte io = 0;
if (readData.RoomIO == "I")
io = 1;
else if (readData.RoomIO == "O")
io = 2;
readerIO[NO] = io;
readerID[NO] = readData.ReaderID;
readerType[NO] = Convert.ToByte(readData.ReaderType);
}
CardControl control = new CardControl(data.ControlID, data.IP, Room.ERNo, readerID, readerIO, readerType, data.ERID);
ServerData.RAC2400Controller.Add(data.ControlID, control);
ServerData.ThreadEndList.Add(control);
System.Threading.Thread t = new System.Threading.Thread(control.HostAction);
t.Start();
}
break;
case 2:
if (!ServerData.RAC960Controller.ContainsKey(data.ControlID))
{
var Room = (from room in dbroom.tblEngineRoomConfig
where room.ERID == data.ERID
select room).First();
string RoomName = Room.ERName;
var readerData = (from reader in dbroom.tblCardReaderConfig
where reader.ControlID == data.ControlID
select reader).First();
byte type = 0;
if (readerData.RoomIO == "I")
type = 1;
else if (readerData.RoomIO == "O")
type = 2;
RAC960CardControl control = new RAC960CardControl(data.ControlID, data.IP, Room.ERNo, readerData.ReaderID, type, data.Loop == "1");
ServerData.RAC960Controller.Add(data.ControlID, control);
ServerData.ThreadEndList.Add(control);
System.Threading.Thread t = new System.Threading.Thread(control.HostAction);
t.Start();
}
break;
case 4:
if (!ServerData.ADAMController.ContainsKey(data.ControlID))
{
//if (data.IP == "192.168.100.21")
//{
ADAMControl adam = new ADAMControl(data.ControlID, data.IP, data.Port);
ServerData.ADAMController.Add(data.ControlID, adam);
ServerData.ThreadEndList.Add(adam);
System.Threading.Thread t = new System.Threading.Thread(adam.HostAction);
t.Start();
//}
}
break;
}
#endregion
if (!ServerData.ControlStatus.ContainsKey(data.ControlID))
{
try
{
var RoomStatus = (from o in dbroom.tblDeviceStateLog
where o.TypeID == 3 && o.ControlID == data.ControlID
orderby o.TimeStamp descending
select o).First();
ServerData.ControlStatus.Add(data.ControlID, new RoomInterface.ControlStatus(data.ControlID, RoomStatus.TypeCode == 0, RoomStatus.TimeStamp));
}
catch
{
ServerData.ControlStatus.Add(data.ControlID, new RoomInterface.ControlStatus(data.ControlID, false, DateTime.Now));
}
}
}
}
catch (Exception ex)
{
TCommon.SaveLog(ex.Message + "\r\n" + ex.StackTrace);
}
}
var UserData = from card in dbroom.tblMagneticCard
select card;
foreach (var user in UserData)
{
if (!ServerData.PersonnelData.ContainsKey(user.ABA))
{
ServerData.PersonnelData.Add(user.ABA, user.Name);
}
if (!ServerData.PersonnelCompData.ContainsKey(user.ABA))
{
ServerData.PersonnelCompData.Add(user.ABA, user.Company);
}
}
var RoomData = from o in dbroom.tblEngineRoomConfig select o;
foreach (var room in RoomData)
{
if (!ServerData.RoomPerson.ContainsKey(room.ERNo))
{
ServerData.RoomPerson.Add(room.ERNo, new List<RoomCardData>());
DateTime OneDay = DateTime.Now.AddDays(-1);
var RoomPersonData = from o in dbroom.tblEngineRoomLog where o.ERNo == room.ERNo && o.StartTime > OneDay select o;
List<RoomCardData> NewRoomCardData = new List<RoomCardData>();
foreach (var RoomPerson in RoomPersonData)
{
bool AddData = true;
bool InOut = RoomPerson.Endtime == null;
for (int i =0 ;i < NewRoomCardData.Count;i++)
{
if (NewRoomCardData[i].CardID == RoomPerson.ABA)
{
if (NewRoomCardData[i].LastTime < RoomPerson.StartTime)
{
RoomCardData CardData = NewRoomCardData[i];
CardData.LastTime = RoomPerson.StartTime;
CardData.In = InOut;
}
AddData = false;
break;
}
}
if (AddData)
NewRoomCardData.Add(new RoomCardData(RoomPerson.ABA, ServerData.GetCardName(RoomPerson.ABA),ServerData.GetCarComp(RoomPerson.ABA), InOut, RoomPerson.StartTime,false));
}
for (int i = 0; i < NewRoomCardData.Count; i++)
{
if (NewRoomCardData[i].In)
{
ServerData.RoomPerson[room.ERNo].Add(NewRoomCardData[i]);
}
}
var adamControl = from o in dbroom.tblControllerConfig where o.ERID == room.ERID && o.ControlType == 4 select o.ControlID;
if (adamControl.Count() > 0)
{
ServerData.RoomControl.Add(room.ERNo, adamControl.First());
}
}
}
var Group = from o in dbroom.tblSysRole select o;
foreach (var role in Group)
{
if (!ServerData.GroupDataDic.ContainsKey(role.RoleID))
{
GroupData newGroupData = new GroupData(role.RoleID);
var groupController = from o in dbroom.tblSysRoleAuthority where o.RoleID == role.RoleID && o.Enable == "Y" select o;
foreach(var Controller in groupController)
{
newGroupData.ControlID.Add(Controller.ControlID);
}
ServerData.GroupDataDic.Add(role.RoleID, newGroupData);
}
}
var MagenticCard = from o in dbroom.tblMagneticCard select o;
foreach (var card in MagenticCard)
{
if (!ServerData.GroupCardDic.ContainsKey(card.RoleID))
{
ServerData.GroupCardDic.Add(card.RoleID, new List<GroupCardData>());
}
ServerData.GroupCardDic[card.RoleID].Add(new GroupCardData(card.ABA, card.StartDate, card.EndDate, card.RoleID));
}
var ControlCrads = from magentic in dbroom.tblMagneticCard
join role in dbroom.tblSysRoleAuthority on magentic.RoleID equals role.RoleID
where role.Enable == "Y" && magentic.Enable == "Y"
select new { ABA = magentic.ABA, controlID = role.ControlID };
foreach (var data in ControlCrads)
{
if (!ServerData.ControlCardDic.ContainsKey(data.controlID))
{
ServerData.ControlCardDic.Add(data.controlID, new List<string>());
}
ServerData.ControlCardDic[data.controlID].Add(data.ABA);
}
var ControlNames = from control in dbroom.tblControllerConfig
join door in dbroom.tblEntranceGuardConfig on control.EntranceCode equals door.EntranceCode
join room in dbroom.tblEngineRoomConfig on door.ERID equals room.ERID
select new { controlID = control.ControlID, doorName = door.Memo, roomName = room.ERName };
foreach (var controlData in ControlNames)
{
if (!ServerData.ControlName.ContainsKey(controlData.controlID))
{
ServerData.ControlName.Add(controlData.controlID, controlData.roomName + controlData.doorName);
}
}
}
bool IsHostIp()
{
try
{
dbroomEntities dbroom = new dbroomEntities();
var HostData = (from o in dbroom.tblHostConfig select o).First();
foreach (System.Net.IPAddress ip in System.Net.Dns.GetHostAddresses(System.Net.Dns.GetHostName()))
{
if (ip.ToString().Equals(HostData.IP))
{
return true;
}
}
return false;
}
catch
{
return false;
}
}
