/// <summary> /// 得到當前最大ToDo Item ID /// </summary> /// <returns></returns> private string GetMaxItemID(SQLHelper conn, string user_id, out int mySeq) { string rtn = ""; mySeq = 0; string matchString = GetMatchString("D"); DataTable table = conn.OpenDataTable("select isnull(max(td_no),'') as maxid from lrtd00h (nolock) where td_no like '" + matchString + "%'", CommandType.Text); if (table.Rows.Count > 0) { rtn = table.Rows[0][0].ToString(); } table = conn.OpenDataTable("select isnull(max(td_seq),0) as maxid from lrtd00h (nolock) where td_owner= '" + user_id + "'", CommandType.Text); if (table.Rows.Count > 0) { mySeq = Convert.ToInt32(table.Rows[0][0]); } else { mySeq = 0; } // 只留下流水號 rtn = rtn.Replace(matchString, ""); if (rtn == "") rtn = "00000"; return rtn; }
public bool UpdateActKey(string user_id) { bool rtn = false; try { string SQL = "select user_actkey from lrtduser (nolock) where user_no ='" + user_id + "'"; using (SQLHelper conn = new SQLHelper()) { conn.OpenConnection(); try { DataTable account = conn.OpenDataTable(SQL, CommandType.Text); if (account.Rows.Count > 0) { conn._Transaction = conn._Connection.BeginTransaction(); SQL = "update a set a.user_actkey = '" + Guid.NewGuid().ToString().Replace("-", "") + "' from lrtduser a where user_no ='" + user_id + "'"; conn.ExecuteSQL(SQL); conn._Transaction.Commit(); rtn = true; } account.Dispose(); account = null; conn.CloseConnection(); } catch (System.Exception ex) { conn._Transaction.Rollback(); conn.CloseConnection(); throw ex; } //返回Client return rtn; } } catch { } return rtn; }
public void Start(IPEndPoint localEndPoint) { listenSocket = new Socket(localEndPoint.AddressFamily, SocketType.Stream, ProtocolType.Tcp); listenSocket.Bind(localEndPoint); listenSocket.Listen(m_maxServerLoading); //LogHelper.WriteLog("Start", "服務器已啟動,正在監聽..."); LogHelper.WriteLog("Start", string.Format("服務器已啟動,正在監聽IP: {0} 端口號: {1}", localEndPoint.Address.ToString(), localEndPoint.Port.ToString())); //測試時如果數據庫為正式區,這句不能執行. UpdateUsersState("6"); //全部離線 //測試時如果數據庫為正式區,這句不能執行. #region " 檢查lrtdseq是否添加 并 生成系統廣播" try { using (SQLHelper conn = new SQLHelper(DBInfo)) { conn.OpenConnection(); try { //DataTable test = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '1',@seqNO output select @seqNO", CommandType.Text); //if (test.Rows.Count > 0) //{ //} DataTable dtSeq = conn.OpenDataTable("select seq_no from lrtdseq (nolock) where seq_type='1'", CommandType.Text); if (dtSeq.Rows.Count > 0) { // SeqNO = long.Parse(dtSeq.Rows[0]["seq_no"].ToString()); // clsClientLog.WriteLog(clsClientLog.LogType.System, hostName, "消息起始ID: " + SeqNO.ToString()); } else { conn.ExecuteSQL("insert into lrtdseq (seq_type,seq_no) values('1',0)"); //clsClientLog.WriteLog(clsClientLog.LogType.System, hostName, "消息起始ID: 0"); LogHelper.WriteLog("WebThreadFunc", "正式區消息起始ID:0"); } ////For Test //dtSeq = conn.OpenDataTable("select seq_no from lrtdseq (nolock) where seq_type='2'", CommandType.Text); //if (dtSeq.Rows.Count > 0) //{ // // SeqNO = long.Parse(dtSeq.Rows[0]["seq_no"].ToString()); // // clsClientLog.WriteLog(clsClientLog.LogType.System, hostName, "消息起始ID: " + SeqNO.ToString()); //} //else //{ // conn.ExecuteSQL("insert into lrtdseq (seq_type,seq_no) values('2',0)"); // //clsClientLog.WriteLog(clsClientLog.LogType.System, hostName, "消息起始ID: 0"); // LogHelper.WriteLog("WebThreadFunc", "測試區消息起始ID:0"); //} // 生成系統公告 dtSeq = conn.OpenDataTable("select * from lrbroadcast (nolock) where bdc_cmd = '1' order by region_id,bdc_seq", CommandType.Text); if (dtSeq.Rows.Count > 0) { DataTable dtRegions = dtSeq.DefaultView.ToTable(true, "region_id"); foreach (DataRow drRegion in dtRegions.Rows) { DataRow[] dtRows = dtSeq.Select("region_id = '" + drRegion["region_id"].ToString() + "'", "bdc_seq"); if (dtRows.Length > 0) { List<NoticeMsg> nMsgs = new List<NoticeMsg>(); foreach (DataRow dr in dtRows) { nMsgs.Add(new NoticeMsg() { MsgSeq = dr["bdc_seq"].ToString(), MsgText = dr["bdc_text"].ToString() }); } this.SysNotices.Add(drRegion["region_id"].ToString(), nMsgs); } } dtRegions.Dispose(); dtRegions = null; } dtSeq.Dispose(); dtSeq = null; conn.CloseConnection(); } catch { conn.CloseConnection(); } } } catch (Exception ex) { //clsClientLog.WriteLog(clsClientLog.LogType.System, hostName, "獲得消息起始ID失敗.原因: " + ex.Message); LogHelper.WriteLog("WebThreadFunc", "獲得消息起始ID失敗.原因:" + (ex != null ? ex.Message : "")); return; } #endregion #region " 開一個獨立的線程來定時發送1.活動用戶數,2.在線用戶數,3註冊用戶數,4.今日消息數,5.累積消息數 " SysThread = new Thread(SysTreadFunc); SysThread.Start(); #endregion // 開一個線程來標記歷史消息為已讀 MarkReadThread = new Thread(MarkReadThreadFunc); MarkReadThread.Start(); // 開一個線程來推送用戶在線狀態 // 初始化-MaxStateNotifyThreads for (int i = 0; i < MaxStateNotifyThreads; i++) { StateNofityThreadsQueue.Enqueue(new Thread(new ParameterizedThreadStart(StateNotifyThreadFunc))); } StateNotifyControlThread = new Thread(StateNotifyControlThreadFunc); StateNotifyControlThread.Priority = ThreadPriority.AboveNormal; // 次高級 StateNotifyControlThread.Start(); // 開一個線程來更新數據類群信息 UpdateVDataThread = new Thread(UpdateVDataThreadFunc); UpdateVDataThread.Start(); StartAccept(null); //未完成 //守護線程 //m_daemonThread = new DaemonThread(this); }
private DataTable GetContacts(SQLHelper conn, string UserID) { //暫時改為只抓好友. //string SQL = "select contact_id from " + "\r\n" + // "( " + "\r\n" + // " select contact_id from lrcontact (nolock) where user_no='" + UserID + "' " + "\r\n" + // " union " + "\r\n" + // " --我是owner時抓所有member " + "\r\n" + // " select b.td_member as contact_id from lrtd00h a (nolock) " + "\r\n" + // " join lrtd00d1 b (nolock) on a.td_no=b.td_no " + "\r\n" + // " where a.td_owner='" + UserID + "' " + "\r\n" + // " union " + "\r\n" + // " --我不是owner時抓所有owner " + "\r\n" + // " select a.td_owner as contact_id from lrtd00h a (nolock) " + "\r\n" + // " join lrtd00d1 b (nolock) on a.td_no=b.td_no " + "\r\n" + // " where a.td_owner<>'" + UserID + "' and b.td_member='" + UserID + "' " + "\r\n" + // " union " + "\r\n" + // " --我不是owner時抓所有member " + "\r\n" + // " select x.td_member as contact_id from lrtd00d1 x (nolock) " + "\r\n" + // " where x.td_no in " + "\r\n" + // " ( " + "\r\n" + // " select a.td_no from lrtd00h a (nolock) " + "\r\n" + // " join lrtd00d1 b (nolock) on a.td_no=b.td_no " + "\r\n" + // " where a.td_owner<>'" + UserID + "' and b.td_member='" + UserID + "' " + "\r\n" + // " ) and x.td_member<>'" + UserID + "' " + "\r\n" + // ") a"; //Added by Donnie on 2016/04/29 //把自己的好友和添加了自己為好友的人都作為聯絡人 //string SQL = "select contact_id from lrcontact (nolock) where user_no='" + UserID + "' "; string SQL = "select contact_id from ( " + "\r\n" + " select contact_id as contact_id from lrcontact (nolock) where user_no='" + UserID + "' " + "\r\n" + " union " + "\r\n" + " select user_no as contact_id from lrcontact (nolock) where contact_id='" + UserID + "' " + "\r\n" + ") a"; return conn.OpenDataTable(SQL, CommandType.Text); }
private void GetSysData() { //1.活動用戶(14天內有發消息的) //2.在線用戶 //3.註冊用戶 //4.今天消息量 //5.累積消息量 string ActiveDate = DateTime.Now.AddDays(-14).ToString("yyyy/MM/dd"); // 兩周內 string SQL = ""; //抓數據 using (SQLHelper conn = new SQLHelper(DBInfo)) { try { conn.OpenConnection(); //1.活動用戶(14天內有發消息的) SQL = "select COUNT(1) as active_users_count from " + "\r\n" + "( " + "\r\n" + " select a.msg_from from lrtdmsg a (nolock) join lrtduser b (nolock) on a.msg_from=b.user_no where a.msg_date>='" + ActiveDate + "' group by a.msg_from " + "\r\n" + ") a"; ActiveUsersCount = conn.OpenDataTable(SQL, CommandType.Text).Rows[0]["active_users_count"].ToString(); //2.在線用戶(App客戶端不算) OnlineUsersCount = ((PcUserTokenList != null ? PcUserTokenList.Count : 0) + (MobileUserTokenList != null ? MobileUserTokenList.Count : 0)).ToString(); //3.註冊用戶 SQL = "select COUNT(1) as registered_users_count from lrtduser (nolock)"; RegisteredUsersCount = conn.OpenDataTable(SQL, CommandType.Text).Rows[0]["registered_users_count"].ToString(); //4.今天消息量 SQL = "select COUNT(1) as today_msgs_count from lrtdmsg (nolock) where msg_date='" + DateTime.Now.ToString("yyyy/MM/dd") + "' and msg_from in (select user_no from lrtduser (nolock))"; TodayMessagesCount = conn.OpenDataTable(SQL, CommandType.Text).Rows[0]["today_msgs_count"].ToString(); //5.累積消息量 SQL = "select COUNT(1) as total_msgs_count from lrtdmsg (nolock) where msg_from in (select user_no from lrtduser (nolock))"; TotalMessagesCount = conn.OpenDataTable(SQL, CommandType.Text).Rows[0]["total_msgs_count"].ToString(); conn.CloseConnection(); } catch (Exception ex) { conn.CloseConnection(); //clsClientLog.WriteLog(clsClientLog.LogType.Error, "SysTreadFunc", "讀取平臺數據時:" + (ex != null ? ex.Message : "")); LogHelper.WriteLog("SysTreadFunc()", "讀取平臺數據時:" + (ex != null ? ex.Message : "")); return; } } }
public string InsertSysMsg(SQLHelper conn, string User, string Msg, string SysType, string SysObjType, string td_no, string msg_to, string GUID) { try { long SeqNO = 0; string SQL; //lock (Ticket) 這里先不鎖,單純靠SQL的鎖機制來處理 //{ DataTable dtSeq = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '5',@seqNO output select @seqNO", CommandType.Text); if (dtSeq.Rows.Count > 0) { SeqNO = long.Parse(dtSeq.Rows[0][0].ToString()); } //} if (SeqNO > 0) { DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn._Transaction = conn._Connection.BeginTransaction(); if (SysType == "1") { //撤回消息操作(msg_doctype='1' 切换回文字类别 SQL = "update a set a.msg_text=N'" + Msg.Replace("'", "''") + "',a.msg_docstate='2',a.msg_doctype='1' from lrtdmsg a where msg_guid='" + GUID + "'"; conn.ExecuteSQL(SQL); } SQL = "insert into lrsysmsg (ie_ymd,ie_time,ie_user,sys_date,sys_time,sys_user,sys_id,sys_type,sys_objtype,sys_objid,sys_remk) " + "values('" + ymd + "','" + time + "','" + User + "','" + ymd + "','" + time + "','" + User + "','" + SeqNO + "','" + SysType + "','" + SysObjType + "','" + GUID + "',N'" + Msg.Replace("'", "''") + "')"; conn.ExecuteSQL(SQL); //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + Msg); if (td_no.Length > 0) { // ##發群組## //寫表頭Owner SQL = " insert into lrsysmsgstate (sys_id,sysmsg_to,sysmsg_state)" + "\r\n" + " select '" + SeqNO + "',a.td_owner,'0' " + "\r\n" + " from lrtd00h a(nolock) " + "\r\n" + " where a.td_no = '" + td_no + "' and a.td_owner <> '" + User + "'"; conn.ExecuteSQL(SQL); //寫表身相關人 SQL = " insert into lrsysmsgstate (sys_id,sysmsg_to,sysmsg_state)" + "\r\n" + " select '" + SeqNO + "',b.td_member,'0' " + "\r\n" + " from lrtd00d1 b(nolock) " + "\r\n" + " join lrtd00h c (nolock) on b.td_no=c.td_no " + "\r\n" + " where c.td_no = '" + td_no + "'" + " and b.td_member <> '" + User + "' and b.td_member <> c.td_owner "; conn.ExecuteSQL(SQL); // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','" + td_no + "','','G','" + ymd + "','" + time + "'," + SeqNO); } else if (msg_to.Length > 0) { // ##發給個人## SQL = "insert into lrsysmsgstate (sys_id,sysmsg_to,sysmsg_state) values('" + SeqNO + "','" + msg_to + "','0')"; conn.ExecuteSQL(SQL); // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','','" + msg_to + "','P','" + ymd + "','" + time + "'," + SeqNO); } conn._Transaction.Commit(); } return SeqNO.ToString(); //return new string[] { curTDID, ymd, time }; } catch (System.Exception ex) { //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + ex.Message); LogHelper.WriteLog("StartUp()", "將系統消息寫入數據庫時:" + (ex != null ? ex.Message : "")); conn._Transaction.Rollback(); throw ex; } }
/// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="User"></param> /// <param name="Msg"></param> /// <param name="td_no"></param> /// <param name="msg_to"></param> /// <param name="MsgType">0=群消息,1=個人消息</param> /// <param name="MsgPullType">拉取式消息子分類(空=普通消息;LXSubject=主題;自定義表名=什麼都可以)</param> /// <param name="MsgDocType">0=File;1=Text;2=Offline File;3=Image;4=Mail;5=Quake</param> /// <param name="MsgDocState">文件狀態.</param> /// <param name="GUID"></param> /// <param name="ForTest"></param> /// <param name="IsPushMode"></param> /// <returns></returns> public string InsertMsg(SQLHelper conn, string User, string Msg, string td_no, string msg_to, string MsgType, string MsgPullType, string MsgDocType, string MsgDocState, string GUID, bool ForTest, bool IsPushMode) { try { long SeqNO = 0; string tbMsg = "lrtdmsg"; string tbState = "lrmsgstate"; if (ForTest) { tbMsg = "lrtdmsg_test"; tbState = "lrmsgstate_test"; } string SQL = "select msg_no from " + tbMsg + " (nolock) where msg_guid='" + GUID + "'"; DataTable dtSeq = conn.OpenDataTable(SQL, CommandType.Text); if (dtSeq.Rows.Count > 0) { //重復就不寫入 return dtSeq.Rows[0]["msg_no"].ToString(); } else //lock (Ticket) 這里先不鎖,單純靠SQL的鎖機制來處理 { string SeqCode = "1"; // 正式單 if (ForTest) SeqCode = "2"; dtSeq = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '" + SeqCode + "',@seqNO output select @seqNO", CommandType.Text); if (dtSeq.Rows.Count > 0) { SeqNO = long.Parse(dtSeq.Rows[0][0].ToString()); } } if (SeqNO > 0) { DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn._Transaction = conn._Connection.BeginTransaction(); //string maxID = GetMaxMsgID(conn, td_no); //int curNumber = int.Parse(maxID) + 1; //maxID = curNumber.ToString().PadLeft(5, '0'); //string curTDID = Guid.NewGuid().ToString().Replace("-", "").ToUpper(); //Added by Donnie on 2016/05/26 //增加MsgPullType SQL = "insert into " + tbMsg + " (ie_ymd,ie_time,ie_user,td_no,msg_from,msg_to,msg_date,msg_time,msg_text,msg_no,msg_type,msg_datatype,msg_doctype,msg_docstate,msg_guid) " + "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "','" + msg_to + "','" + ymd + "','" + time + "',N'" + Msg.Replace("'", "''") + "'," + SeqNO + ",'" + MsgType + "','" + MsgPullType + "','" + MsgDocType + "','" + MsgDocState + "','" + GUID + "')"; conn.ExecuteSQL(SQL); //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + Msg); if (td_no.Length > 0) { // ##發群組## // Added by Donnie on 2016/05/13 // 只有Push模式的群才向lrmsgstate里寫記錄 // Added by Donnie on 2016/05/31 // Pull模式只向群主轉發 // Modified by Donnie on 2016/06/01 // 最終商定Pull消息不向任何群成員推送(包括群主) if (IsPushMode) { //寫表頭Owner SQL = " insert into " + tbState + " (ie_ymd,ie_time,ie_user,msg_no,msg_to,msg_state)" + "\r\n" + " select '" + ymd + "','" + time + "','" + User + "',a.msg_no,b.td_owner,'0' " + "\r\n" + " from " + tbMsg + " a(nolock)" + "\r\n" + " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" + " where a.msg_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + " and b.td_owner <> '" + User + "'"; conn.ExecuteSQL(SQL); //寫表身相關人 SQL = " insert into " + tbState + " (ie_ymd,ie_time,ie_user,msg_no,msg_to,msg_state)" + "\r\n" + " select '" + ymd + "','" + time + "','" + User + "',a.msg_no,b.td_member,'0' " + "\r\n" + " from " + tbMsg + " a(nolock)" + "\r\n" + " join lrtd00d1 b(nolock) on a.td_no = b.td_no " + "\r\n" + " join lrtd00h c (nolock) on b.td_no=c.td_no " + "\r\n" + " where a.msg_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + " and b.td_member <> '" + User + "' and b.td_member <> c.td_owner "; conn.ExecuteSQL(SQL); } // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','" + td_no + "','','G','" + ymd + "','" + time + "'," + SeqNO); } else if (msg_to.Length > 0) { // ##發給個人## SQL = "insert into " + tbState + " (msg_no,msg_to,msg_state) values('" + SeqNO + "','" + msg_to + "','0')"; conn.ExecuteSQL(SQL); // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','','" + msg_to + "','P','" + ymd + "','" + time + "'," + SeqNO); } conn._Transaction.Commit(); } return SeqNO.ToString(); } catch (System.Exception ex) { //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + ex.Message); LogHelper.WriteLog("StartUp()", "將消息寫入數據庫時:" + (ex != null ? ex.Message : "")); conn._Transaction.Rollback(); throw ex; } }
/// <summary> /// 轉發群消息 /// </summary> /// <param name="DeviceType">P=PC;M=Mobile(Web);A=APP</param> /// <param name="DataTerminal"></param> /// <param name="DataGroup"></param> /// <param name="DataGUID"></param> /// <param name="DataBody"></param> /// <param name="DataTerminalName"></param> /// <param name="conn"></param> /// <param name="MsgNO"></param> private void SendGroupVoice(string DataTerminal, string DataGroup, string DataTerminalName, SQLHelper conn, string MsgGUID, string MsgNO, string VoiceSeconds) { DataTable UserList = conn.OpenDataTable("select a.msg_to from lrmsgstate a (nolock) where a.msg_no=" + MsgNO + " and a.msg_state='0'", CommandType.Text); if (UserList.Rows.Count > 0) { string Users = ""; //系統將訊息轉發給群成員(以發送者的帳號) //問題: 1.只有帳號沒有姓名.Client無法顯示姓名 // 2.沒有發送日期和時間 // PC 和 Mobile 要用到的信息.提前打包好,沒必要放到for循環里去做 List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(VoiceSeconds, "R", DataTerminal, DataTerminalName, DataGroup, MsgGUID, MsgNO); foreach (DataRow User in UserList.Rows) { #region " PC Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.PcUserTokenList, "SendGroupVoice"); #endregion #region " Mobile Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.MobileUserTokenList, "SendGroupVoice"); #endregion } // 去掉最後一個逗號 if (Users.Length > 0) { Users = Users.Substring(0, Users.Length - 1); // 標記為已讀 DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn.ExecuteSQL("update a set a.msg_state='1',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lrmsgstate a where a.msg_no=" + MsgNO + " and a.msg_state='0' and a.msg_to in (" + Users + ")"); } } UserList.Dispose(); UserList = null; //處理同一帳號多個端登錄的轉發. if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.PC || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自PC端消息. //檢查相同帳號Mobile(Web)端有沒有登錄,有就轉發一次. #region " Mobile Client " PushRoaming(DataTerminal, ParseProtocol.ConvertMsgToByte(VoiceSeconds, "R", "", "", DataGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.MobileUserTokenList); #endregion } if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.Mobile || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自Mobile(Web)端消息. //檢查相同帳號Web端有沒有登錄,有就轉發一次. #region " PC Client " PushRoaming(DataTerminal, ParseProtocol.ConvertMsgToByte(VoiceSeconds, "R", "", "", DataGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.PcUserTokenList); #endregion } }
//Added by Donnie on 2015/12/11 //新增按任務抓信息(私有) private DataTable DoGetToDoMsg(SQLHelper conn, string td_no, string cur_msgid, string msg_block, string sort_type) { DataTable dtMsg = new DataTable("dtMsg"); try { string strTop = (msg_block.Length == 0 || msg_block == "0") ? "" : "top " + msg_block; string strSort = sort_type == "A" ? "asc" : "desc"; string strMsgAnchor = " and 1=1"; if (cur_msgid.Length > 0) { if (sort_type == "A") strMsgAnchor = " and a.msg_no > '" + cur_msgid + "'"; else strMsgAnchor = " and a.msg_no < '" + cur_msgid + "'"; } string SQL = "select " + strTop + " a.msg_from as userno,b.user_name as username,a.msg_no as msgid," + "\r\n" + " a.msg_date + ' ' + LEFT(a.msg_time,5) as msgdatetime,a.msg_text as msg,a.msg_type as msgtype" + "\r\n" + "from lrtdmsg a (nolock)" + "\r\n" + "left join lrtduser b (nolock) on a.msg_from=b.user_no" + "\r\n" + "where a.td_no='" + td_no + "' and a.msg_type <> 'N'" + "\r\n" + strMsgAnchor + "\r\n" + "order by a.msg_no " + strSort; dtMsg = conn.OpenDataTable(SQL, CommandType.Text); //返回Client return dtMsg; } catch { return dtMsg; } }
/// <summary> /// 點擊發送時調用 /// </summary> /// <param name="User"></param> /// <param name="now"></param> /// <param name="Msg"></param> /// <param name="td_no"></param> /// <returns></returns> public string InsertMsg(string User, string Msg, string td_no, string MsgType, string GUID) { string SQL = ""; using (SQLHelper conn = new SQLHelper()) { DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn.OpenConnection(); try { // 得到訊息編號 long SeqNO = 0; DataTable dtSeq = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '1',@seqNO output select @seqNO", CommandType.Text); if (dtSeq.Rows.Count > 0) { SeqNO = long.Parse(dtSeq.Rows[0][0].ToString()); } conn._Transaction = conn._Connection.BeginTransaction(); SQL = "insert into lrtdmsg(ie_ymd,ie_time,ie_user,td_no,msg_from,msg_to,msg_date,msg_time,msg_text,msg_no,msg_type,msg_guid) " + "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "','','" + ymd + "','" + time + "',N'" + Msg + "'," + SeqNO + ",'" + MsgType + "','" + GUID + "')"; conn.ExecuteSQL(SQL); //SQL = // "insert into lrtdmsg(ie_ymd,ie_time,ie_user,td_no,msg_from,msg_to,msg_date,msg_time,msg_text,msg_no,msg_type,msg_guid) " + // "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "','','" + ymd + "','" + time + "',N'" + Msg + "',-1,'N','" + GUID + "')"; //conn.ExecuteSQL(SQL); //寫表頭Owner SQL = " insert into lrmsgstate(msg_no,msg_to,msg_state)" + "\r\n" + " select a.msg_no,b.td_owner,'0' " + "\r\n" + " from lrtdmsg a(nolock)" + "\r\n" + " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" + " where a.msg_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + " and b.td_owner <> '" + User + "'"; conn.ExecuteSQL(SQL); //寫表身相關人 SQL = " insert into lrmsgstate(msg_no,msg_to,msg_state)" + "\r\n" + " select a.msg_no,b.td_member,'0' " + "\r\n" + " from lrtdmsg a(nolock)" + "\r\n" + " join lrtd00d1 b(nolock) on a.td_no = b.td_no " + "\r\n" + " where a.msg_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + " and b.td_member <> '" + User + "'"; conn.ExecuteSQL(SQL); //SQL = // " update lrtdmsg set msg_no = uid,msg_type='1' " + "\r\n" + // " where msg_guid = '" + GUID + "'"; //conn.ExecuteSQL(SQL); conn._Transaction.Commit(); return SeqNO.ToString(); } catch (System.Exception ex) { conn._Transaction.Rollback(); conn.CloseConnection(); throw ex; } } }
public DataTable GetToDoMsg(string user_id, DataTable MessageLocal) { string SQL = ""; using (SQLHelper conn = new SQLHelper()) { try { conn.OpenConnection(); //開啟tempdb連接 #region 臨時數據 string tmp_LRTDMSG = "tmp_LRTDMSG_" + DateTime.Now.ToString("yyyyMMddHHmmssfff"); SQLHelper connTemp = new SQLHelper(); connTemp.OpenConnection("tempdb"); connTemp.ExecuteSQL("create table " + tmp_LRTDMSG + "(msg_no varchar(100) not null default '')"); ; tmp_LRTDMSG = "tempdb.." + tmp_LRTDMSG; connTemp.CloseConnection(); #endregion 臨時數據 foreach (DataRow dr in MessageLocal.Rows) { SQL = "insert into " + tmp_LRTDMSG + "(msg_no) " + "select '" + dr["msg_no"].ToString() + "'"; conn.ExecuteSQL(SQL); } //msg_state 0 標示未讀 1 標示已讀 SQL = " select a.td_no,b.msg_from,isnull(c.user_name,isnull(d.contact_name,b.msg_from)) msg_fromname, " + "\r\n" + " b.msg_to,isnull(e.user_name,isnull(f.contact_name,'')) msg_toname," + " b.msg_date,b.msg_time,b.msg_text,isnull(g.msg_state,'1') msg_state,b.msg_no,b.msg_type " + "\r\n" + " from lrtdmsg b(nolock) " + "\r\n" + " join lrtd00h a(nolock) on a.td_no=b.td_no " + "\r\n" + " left join lrmsgstate g(nolock) on b.msg_no=g.msg_no and g.msg_to='" + user_id + "' " + "\r\n" + //--from " left join lrtduser c(nolock) on b.msg_from=c.user_no " + "\r\n" + " left join lrcontact d(nolock) on b.msg_from=d.contact_id and d.user_no='" + user_id + "' " + "\r\n" + //--to " left join lrtduser e(nolock) on b.msg_to=e.user_no " + "\r\n" + " left join lrcontact f(nolock) on b.msg_to=f.user_no " + "\r\n" + " where (a.td_owner = '" + user_id + "'" + " or exists(select 1 from lrtd00d1 x where x.td_member = '" + user_id + "' and x.td_no = a.td_no)) " + "\r\n" + " and not exists(select 1 from " + tmp_LRTDMSG + " x where x.msg_no = b.msg_no)" + "\r\n" + " order by a.td_no,b.msg_date,b.msg_time"; DataTable msgTable = conn.OpenDataTable(SQL, CommandType.Text); //資料被拉過去 //就已讀 SQL = " update g set g.msg_state = '1' " + "\r\n" + " from lrtdmsg a(nolock) " + "\r\n" + " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" + " join lrmsgstate g(nolock) on a.msg_no=g.msg_no and g.msg_to='" + user_id + "' " + "\r\n" + " where (b.td_owner = '" + user_id + "'" + " or exists(select 1 from lrtd00d1 x where x.td_member = '" + user_id + "' and x.td_no = b.td_no)) and g.msg_state='0'"; conn.ExecuteSQL(SQL); conn.CloseConnection(); return msgTable; } catch (System.Exception ex) { conn.CloseConnection(); throw ex; } } }
private string GetMaxMsgID(SQLHelper conn, string td_no) { string rtn = ""; string matchString = GetMatchString("M"); DataTable table = conn.OpenDataTable("select isnull(max(msg_no),'') as maxid from lrtdmsg (nolock) where msg_no like '" + matchString + "%'", CommandType.Text); if (table.Rows.Count > 0) { rtn = table.Rows[0][0].ToString(); } // 只留下流水號 rtn = rtn.Replace(matchString, ""); if (rtn == "") rtn = "00000"; return rtn; }
public DataSet GetContactAndMembers(string userid) { DataSet dSet = new DataSet(); try { using (SQLHelper conn = new SQLHelper()) { if (conn.OpenConnection()) { string SQL = " select a.user_no,a.contact_id,isnull(b.user_email,a.contact_id) contact_email,a.contact_name," + " a.contact_company,a.contact_position,a.contact_sex,a.contact_img," + " case when b.uid is null then 1 else 0 end contact_register " + " from lrcontact a " + " left join lrtduser b(nolock) on a.contact_id = b.user_no " + " where a.user_no = '" + userid + "'"; DataTable GroupData = conn.OpenDataTable(SQL, CommandType.Text); GroupData.TableName = "GroupData"; dSet.Tables.Add(GroupData.Copy()); SQL = "select a.group_id,a.contact_id,b.user_no " + "from lrgmember a " + "join lrgroup b on a.group_id=b.group_id " + "where b.user_no = '" + userid + "'"; DataTable MemberData = conn.OpenDataTable(SQL, CommandType.Text); MemberData.TableName = "MemberData"; dSet.Tables.Add(MemberData.Copy()); dSet.AcceptChanges(); } conn.CloseConnection(); } //返回Client return dSet; } catch { return dSet; } }
public DataTable GetGroups(string userid) { DataTable GroupData = new DataTable("GroupData"); try { using (SQLHelper conn = new SQLHelper()) { if (conn.OpenConnection()) { string SQL = "select group_id,group_name,user_no " + "from lrgroup " + "where user_no = '" + userid + "'"; GroupData = conn.OpenDataTable(SQL, CommandType.Text); } conn.CloseConnection(); } //返回Client return GroupData; } catch { return GroupData; } }
/// <summary> /// 轉發群消息 /// </summary> /// <param name="DataTerminal"></param> /// <param name="DataGroup"></param> /// <param name="DataGUID"></param> /// <param name="DataBody"></param> /// <param name="DataTerminalName"></param> /// <param name="conn"></param> /// <param name="MsgNO"></param> protected void SendGroupBroadcast(string DataTerminal, string DataGroup, string DataBody, string DataTerminalName, string MsgGUID, bool IsPushMode) { // 抓出群成員 using (SQLHelper conn = new SQLHelper(m_asyncSocketServer.DBInfo)) { conn.OpenConnection(); try { // 記錄到數據庫中 // DataType == "L"時, DataTerminalName為Pull消息子分類(表名) string MsgNO = this.InsertMsg(conn, DataTerminal, DataBody, DataGroup, "", "1", (IsPushMode ? "" : DataTerminalName), MsgGUID, false, IsPushMode); if (MsgNO.Length > 0) { // 系統將訊息轉發給群成員(以發送者的帳號) //base.SendGroupBroadcast(DataTerminal, DataGroup, DataBody, DataTerminalName, conn, DataGUID, MsgNO, DataType == "G"); if (IsPushMode) { DataTable UserList = conn.OpenDataTable("select a.msg_to from lrmsgstate a (nolock) where a.msg_no=" + MsgNO + " and a.msg_state='0'", CommandType.Text); if (UserList.Rows.Count > 0) { string Users = ""; //系統將訊息轉發給群成員(以發送者的帳號) //問題: 1.只有帳號沒有姓名.Client無法顯示姓名 // 2.沒有發送日期和時間 // PC 和 Mobile 要用到的信息.提前打包好,沒必要放到for循環里去做 List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(DataBody, "4", DataTerminal, DataTerminalName, DataGroup, MsgGUID, MsgNO); // Web 要用到的信息.提前打包好,沒必要放到for循環里去做 - 暫時不實現 //byte[] msgw = WebConvertMethod.ConvertMsgToByte(DataBody, "4", DataTerminal, DataTerminalName, DataGroup, MsgNO); foreach (DataRow User in UserList.Rows) { #region " PC Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.PcUserTokenList, "SendGroupBroadcast"); #endregion #region " Mobile Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.MobileUserTokenList, "SendGroupBroadcast"); #endregion #region " Web Client " //recUser = _ws_transmit_tb[User["msg_to"].ToString()] as UserSocket; //if (recUser != null) //{ // userSkt = recUser.Socket; // //放到for循環外面去,提高效率 // //byte[] msg = WebConvertMethod.ConvertMsgToByte(DataBody, "4", DataTerminal, DataTerminalName, DataGroup, MsgNO); // try // { // //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 // lock (userSkt) // { // userSkt.Send(msgw); // //SendData(userSkt, msg); // } // Users += "'" + User["msg_to"].ToString() + "',"; // } // catch (SocketException ex) // { // //掉線了,直接干掉. // try // { // //clsClientLog.WriteLog(clsClientLog.LogType.Error, "WebThreadFunc(object obj):catch (SocketException)", "轉發群消息給用戶:" + User["msg_to"].ToString() + "時發生異常,斷開連接."); // LogHelper.WriteLog("SendGroupBroadcast", "轉發群消息給手機用戶:" + User["msg_to"].ToString() + "時發生異常,斷開連接." + (ex != null ? ex.Message : "")); // try // { // if (userSkt.Connected) // { // userSkt.Shutdown(SocketShutdown.Both); // //userSkt.Disconnect(false); // } // userSkt.Close(); // #region " 聯絡人狀態變更 " // //通知在線的聯絡人當前用戶改變了狀態 // //recUser.SetUserState("6"); // //UpdateUserLog(recUser.ID, "1", "1", "Mobile端異常登出"); // //如果PC端沒在線就通知聯絡人此人離線了 // ProcessWebUserState(recUser); // //UserSocket PCUser = _transmit_tb[recUser.ID] as UserSocket; // //if (PCUser == null) // //{ // // SendUserState(recUser); // // UpdateUserState(recUser.ID, "6"); //離線 // //} // #endregion // lock (_ws_transmit_tb.SyncRoot) // _ws_transmit_tb.Remove(User["msg_to"].ToString()); // Thread userThread = _ws_thread_tb[User["msg_to"].ToString()] as Thread; // userThread.Abort(); // //線程池不會被遍歷,不鎖. // _ws_thread_tb.Remove(User["msg_to"].ToString()); // } // catch // { } // } // catch // { } // } //} #endregion } // 去掉最後一個逗號 if (Users.Length > 0) { Users = Users.Substring(0, Users.Length - 1); // 標記為已讀 DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn.ExecuteSQL("update a set a.msg_state='1',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lrmsgstate a where a.msg_no=" + MsgNO + " and a.msg_state='0' and a.msg_to in (" + Users + ")"); } } UserList.Dispose(); UserList = null; } //處理同一帳號多個端登錄的轉發. if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.PC || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自PC端消息. //檢查相同帳號Mobile(Web)端有沒有登錄,有就轉發一次. #region " Web Client " //recUser = _ws_transmit_tb[DataTerminal] as UserSocket; //if (recUser != null) //{ // userSkt = recUser.Socket; // // 系統將訊息轉發給群成員(以發送者的帳號) // //問題: 1.只有帳號沒有姓名.Client無法顯示姓名 // // 2.沒有發送日期和時間 // byte[] msg = WebConvertMethod.ConvertMsgToByte(DataBody, "4", "", "", DataGroup, MsgNO); // try // { // //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 // lock (userSkt) // { // userSkt.Send(msg); // //SendData(userSkt, msg); // } // } // catch (SocketException ex) // { // //掉線了,直接干掉. // try // { // LogHelper.WriteLog("SendGroupBroadcast", "轉發群消息給手機用戶:" + DataTerminal + "時發生異常,斷開連接." + (ex != null ? ex.Message : "")); // //clsClientLog.WriteLog(clsClientLog.LogType.Error, "WebThreadFunc(object obj):catch (SocketException)", "轉發群消息給Mobile端用戶:" + DataTerminal + "時發生異常,斷開連接."); // if (userSkt.Connected) // { // userSkt.Shutdown(SocketShutdown.Both); // //userSkt.Disconnect(false); // } // userSkt.Close(); // #region " 聯絡人狀態變更 " // //通知在線的聯絡人當前用戶改變了狀態 // //recUser.SetUserState("6"); // //UpdateUserLog(recUser.ID, "1", "1", "Mobile端異常登出"); // //如果PC端沒在線就通知聯絡人此人離線了 // ProcessWebUserState(recUser); // //UserSocket PCUser = _transmit_tb[recUser.ID] as UserSocket; // //if (PCUser == null) // //{ // // SendUserState(recUser); // // UpdateUserState(recUser.ID, "6"); //離線 // //} // #endregion // lock (_ws_transmit_tb.SyncRoot) // _ws_transmit_tb.Remove(DataTerminal); // Thread userThread = _ws_thread_tb[DataTerminal] as Thread; // userThread.Abort(); // //線程池不會被遍歷,不鎖. // _ws_thread_tb.Remove(DataTerminal); // } // catch // { } // } //} #endregion #region " Mobile Client " PushRoaming(DataTerminal, ParseProtocol.ConvertMsgToByte(DataBody, "4", "", "", DataGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.MobileUserTokenList); #endregion } if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.Mobile || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自Mobile(Web)端消息. //檢查相同帳號Web端有沒有登錄,有就轉發一次. #region " PC Client " PushRoaming(DataTerminal, ParseProtocol.ConvertMsgToByte(DataBody, "4", "", "", DataGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.PcUserTokenList); #endregion } // 轉發完再發回執 // 向發送者發送回執7-03 this.PushFeedback(MsgGUID, MsgNO); } conn.CloseConnection(); } catch { conn.CloseConnection(); } } }
//Added by Donnie on 2016/01/17 /// <summary> /// 得到樂信公告欄數據 /// </summary> /// <returns></returns> public DataTable GetLxAdvData() { DataTable dtADV = new DataTable(); try { using (SQLHelper conn = new SQLHelper()) { if (conn.OpenConnection()) { string SQL = "select adv_img as img,adv_link as link,adv_title as title from lrlxadv (nolock) order by adv_seq"; dtADV = conn.OpenDataTable(SQL, CommandType.Text); } conn.CloseConnection(); } //返回Client return dtADV; } catch { return dtADV; } }
/// <summary> /// 轉發訂閱號回覆到用戶 /// </summary> /// <param name="DataTerminal"></param> /// <param name="DataGroup"></param> /// <param name="DataBody"></param> /// <param name="DataGUID"></param> protected void SendSubServiceFeedbackToUser(string DataTerminal, string DataGroup, string DataBody, string MsgGUID) { // 抓出群成員 using (SQLHelper conn = new SQLHelper(m_asyncSocketServer.DBInfo)) { conn.OpenConnection(); try { // 記錄到數據庫中 // DataType == "L"時, DataTerminalName為Pull消息子分類(表名) string MsgNO = this.InsertSubSrvFeedback(conn, m_userToken.BindingUser.ID, DataBody, DataGroup, DataTerminal, "0", "", "1", "0", MsgGUID); if (MsgNO.Length > 0) { #region " 發給接收人 " DataTable UserList = conn.OpenDataTable("select a.msg_to from lrmsgstate a (nolock) where a.msg_no=" + MsgNO + " and a.msg_state='0'", CommandType.Text); if (UserList.Rows.Count > 0) { string Users = ""; //系統將訊息轉發給群成員(以發送者的帳號) //問題: 1.只有帳號沒有姓名.Client無法顯示姓名 // 2.沒有發送日期和時間 // PC 和 Mobile 要用到的信息.提前打包好,沒必要放到for循環里去做 List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(DataBody, "5", "", "", DataGroup, MsgGUID, MsgNO); foreach (DataRow User in UserList.Rows) { #region " PC Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.PcUserTokenList, "SendSubServiceFeedbackToUser"); #endregion #region " Mobile Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.MobileUserTokenList, "SendSubServiceFeedbackToUser"); #endregion } // 去掉最後一個逗號 if (Users.Length > 0) { Users = Users.Substring(0, Users.Length - 1); // 標記為已讀 DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn.ExecuteSQL("update a set a.msg_state='1',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lrmsgstate a where a.msg_no=" + MsgNO + " and a.msg_state='0' and a.msg_to in (" + Users + ")"); } } UserList.Dispose(); UserList = null; #endregion // 轉發完再發回執 // 向發送者發送回執7-03 //this.PushFeedback(MsgGUID, MsgNO); } conn.CloseConnection(); } catch { conn.CloseConnection(); } } }
public void UpdateToDoItems(DataTable UpdateItems, DataTable members, DataTable tags, string userid) { string SQL = ""; using (SQLHelper conn = new SQLHelper()) { conn.OpenConnection(); try { #region 資料及臨時表準備 string tmpExecutor = "tmp_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");//原來的執行者 string tmp_LRTD00H = "tmp_LRTD00H_" + DateTime.Now.ToString("yyyyMMddHHmmssfff"); string tmp_LRTD00D1 = "tmp_LRTD00D1_" + DateTime.Now.ToString("yyyyMMddHHmmssfff"); string tmp_LRTD00D2 = "tmp_LRTD00D2_" + DateTime.Now.ToString("yyyyMMddHHmmssfff"); //string tmp_LRTDFILE = "tmp_LRTDFILE_" + DateTime.Now.ToString("yyyyMMddHHmmssfff"); SQLHelper connTemp = new SQLHelper(); //開啟tempdb連接 connTemp.OpenConnection("tempdb"); connTemp.ExecuteSQL("create table " + tmp_LRTD00H + "(" + conn.GetTableStruct("lrtd00h") + ",justread varchar(1) not null default '',canback varchar(1) not null default '',td_dir varchar(10) not null default '')"); connTemp.ExecuteSQL("create table " + tmp_LRTD00D1 + "(" + conn.GetTableStruct("lrtd00d1") + ")"); connTemp.ExecuteSQL("create table " + tmp_LRTD00D2 + "(" + conn.GetTableStruct("lrtd00d2") + ")"); //connTemp.ExecuteSQL("create table " + tmp_LRTDFILE +"("+ conn.GetTableStruct("lrtdfile")+")"); connTemp.ExecuteSQL("create table " + tmpExecutor + "(td_no varchar(20) not null default '',td_executor varchar(50) not null default '',send_msg varchar(1) not null default '')"); connTemp.CloseConnection(); tmp_LRTD00H = "tempdb.." + tmp_LRTD00H; tmp_LRTD00D1 = "tempdb.." + tmp_LRTD00D1; tmp_LRTD00D2 = "tempdb.." + tmp_LRTD00D2; //tmp_LRTDFILE = "tempdb.." + tmp_LRTDFILE; tmpExecutor = "tempdb.." + tmpExecutor; foreach (DataRow dr in UpdateItems.Rows) { string _td_sylog = dr["td_sylog"].ToString(); SQL = "insert into " + tmp_LRTD00H + "(td_no,td_name,td_owner,td_description,td_prdate,td_prtime," + "td_fno,td_priority,td_code,td_fdate,td_rsdate,td_rstime,td_redate," + "td_retime,td_state,td_sylog,td_relno,td_type,td_ctype," + "td_prsdate,td_predate,td_sdate,td_edate,td_top,td_hold,canback,td_dir,justread,td_flag) " + "select '" + dr["td_no"].ToString() + "',N'" + dr["td_name"].ToString() + "'," + "'" + dr["td_owner"].ToString() + "',N'" + dr["td_description"].ToString() + "'," + "'" + dr["td_prdate"].ToString() + "','" + dr["td_prtime"].ToString() + "'," + "'" + dr["td_fno"].ToString() + "','" + dr["td_priority"].ToString() + "'," + "'" + dr["td_code"].ToString() + "','" + dr["td_fdate"].ToString() + "'," + "'" + dr["td_rsdate"].ToString() + "','" + dr["td_rstime"].ToString() + "'," + "'" + dr["td_redate"].ToString() + "','" + dr["td_retime"].ToString() + "'," + "'" + dr["td_state"].ToString() + "','" + _td_sylog.Substring(0, _td_sylog.Length > 500 ? 500 : _td_sylog.Length) + "'," + "'" + dr["td_relno"].ToString() + "','" + dr["td_type"].ToString() + "'," + "'" + dr["td_ctype"].ToString() + "','" + dr["td_prsdate"].ToString() + "'," + "'" + dr["td_predate"].ToString() + "','" + dr["td_sdate"].ToString() + "'," + "'" + dr["td_edate"].ToString() + "','" + dr["td_top"].ToString() + "'," + "'" + dr["td_hold"].ToString() + "','" + dr["canback"].ToString() + "'," + "'" + dr["td_dir"].ToString() + "','" + dr["justread"].ToString() + "','" + dr["td_flag"].ToString() + "'"; conn.ExecuteSQL(SQL); } foreach (DataRow dr in members.Rows) { string _td_sylog = dr["td_sylog"].ToString(); SQL = "insert into " + tmp_LRTD00D1 + "(td_no,td_member,td_role,td_rsdate,td_rstime,td_redate," + "td_retime,td_state,td_prsdate,td_predate,td_sdate,td_edate,td_top,td_sylog,td_hold,td_flag) " + "select '" + dr["td_no"].ToString() + "','" + dr["td_member"].ToString() + "'," + "'" + dr["td_role"].ToString() + "','" + dr["td_rsdate"].ToString() + "'," + "'" + dr["td_rstime"].ToString() + "','" + dr["td_redate"].ToString() + "'," + "'" + dr["td_retime"].ToString() + "','" + dr["td_state"].ToString() + "'," + "'" + dr["td_prsdate"].ToString() + "','" + dr["td_predate"].ToString() + "'," + "'" + dr["td_sdate"].ToString() + "','" + dr["td_edate"].ToString() + "'," + "'" + dr["td_top"].ToString() + "','" + _td_sylog.Substring(0, _td_sylog.Length > 500 ? 500 : _td_sylog.Length) + "'," + "'" + dr["td_hold"].ToString() + "','" + dr["td_flag"].ToString() + "'"; conn.ExecuteSQL(SQL); } foreach (DataRow dr in tags.Rows) { SQL = "insert into " + tmp_LRTD00D2 + "(td_tag,td_no) " + "select N'" + dr["td_tag"].ToString() + "','" + dr["td_no"].ToString() + "'"; conn.ExecuteSQL(SQL); } //foreach (DataRow dr in attachements.Rows) //{ // SQL = // "insert into " + tmp_LRTDFILE + "(td_no,td_file,td_guid,td_type,td_remk) " + // "select '" + dr["td_no"].ToString() + "',N'" + dr["td_file"].ToString() + "'," + // "'" + dr["td_guid"].ToString() + "','" + dr["td_type"].ToString() + "',N'" + dr["td_remk"].ToString() + "'"; // conn.ExecuteSQL(SQL); //} conn.ExecuteSQL( "insert into " + tmpExecutor + "(td_no,td_executor) " + "\r\n" + "select a.td_no,isnull(b.td_member,a.td_owner) td_executor " + "\r\n" + "from lrtd00h a " + "\r\n" + "left join lrtd00d1 b(nolock) on a.td_no=b.td_no and b.td_role ='2' " + "\r\n" + "join " + tmp_LRTD00H + " c on a.td_no = c.td_no "); #endregion 資料及臨時表準備 conn._Transaction = conn._Connection.BeginTransaction(); //1.當前修改人員是owner #region 只有owner才可以變更表頭 string ymd = DateTime.Now.ToString("yyyy/MM/dd"); string time = DateTime.Now.ToString("HH:mm:ss"); //JustRead = 1 表示只是狀態的變化 SQL = " update a set a.td_name = b.td_name," + "\r\n" + " a.td_rsdate = b.td_rsdate," + "\r\n" + " a.td_rstime = b.td_rstime," + "\r\n" + " a.td_redate =b.td_redate," + "\r\n" + " a.td_retime =b.td_retime, " + "\r\n" + " a.td_state=b.td_state," + "\r\n" + " a.td_hold=b.td_hold," + "\r\n" + " a.td_top=b.td_top," + "\r\n" + " a.td_flag=b.td_flag," + "\r\n" + " a.ie_lymd = '" + ymd + "', " + "\r\n" + " a.ie_ltime = '" + time + "', " + "\r\n" + " a.ie_luser = '******' " + "\r\n" + " from lrtd00h a(nolock) " + "\r\n" + " join " + tmp_LRTD00H + " b on a.td_no=b.td_no " + "\r\n" + " where a.td_owner = '" + userid + "' and b.justread ='1'"; conn.ExecuteSQL(SQL); //JustRead = 0 表示異動了狀態外的其他屬性 SQL = " update a set a.td_name = b.td_name," + "\r\n" + " a.td_description =b.td_description, " + "\r\n" + " a.td_priority = b.td_priority, " + "\r\n" + " a.td_prdate = b.td_prdate, " + "\r\n" + " a.td_prtime = b.td_prtime," + "\r\n" + " a.td_fdate = b.td_fdate, " + "\r\n" + " a.td_fno = b.td_fno," + "\r\n" + " a.td_rsdate = b.td_rsdate," + "\r\n" + " a.td_rstime = b.td_rstime," + "\r\n" + " a.td_redate = b.td_redate," + "\r\n" + " a.td_retime = b.td_retime, " + "\r\n" + " a.td_code = b.td_code," + "\r\n" + " a.td_ctype = b.td_ctype, " + "\r\n" + " a.td_state=b.td_state," + "\r\n" + " a.td_hold=b.td_hold," + "\r\n" + " a.td_top=b.td_top," + "\r\n" + " a.td_flag=b.td_flag," + "\r\n" + " a.td_prsdate =b.td_prsdate," + "\r\n" + " a.td_predate = b.td_predate," + "\r\n" + " a.td_sdate = case when c.td_role is null then b.td_sdate else '' end," + "\r\n" + " a.td_edate = case when c.td_edate is null then b.td_edate else '' end, " + "\r\n" + " a.td_sylog = b.td_sylog," + "\r\n" + " a.ie_lymd = '" + ymd + "', " + "\r\n" + " a.ie_ltime = '" + time + "', " + "\r\n" + " a.ie_luser = '******' " + "\r\n" + " from lrtd00h a(nolock)" + "\r\n" + " join " + tmp_LRTD00H + " b on a.td_no=b.td_no " + "\r\n" + " left join " + tmp_LRTD00D1 + " c on a.td_no = c.td_no and c.td_role='2' " + "\r\n" + " where a.td_owner = '" + userid + "' and b.justread ='0'"; //表頭信息 conn.ExecuteSQL(SQL); #endregion 只有owner才可以變更表頭 //2.表身的變更 #region owner 的操作 ymd = DateTime.Now.ToString("yyyy/MM/dd"); time = DateTime.Now.ToString("HH:mm:ss"); #region owner 對角色的改變 //刪除已經不存在的資料 SQL = " delete a " + " from lrtd00d1 a(nolock) " + " join " + tmp_LRTD00H + " b on a.td_no = b.td_no " + " where not exists(select 1 from " + tmp_LRTD00D1 + " x where x.td_no = a.td_no and x.td_member = a.td_member)"; conn.ExecuteSQL(SQL); //owner 改變角色 SQL = " update a set " + "\r\n" + " a.td_role = b.td_role, " + "\r\n" + " a.ie_lymd = '" + ymd + "', " + "\r\n" + " a.ie_ltime = '" + time + "', " + "\r\n" + " a.ie_luser = '******' " + "\r\n" + " from lrtd00d1 a(nolock)" + " join " + tmp_LRTD00D1 + " b on a.td_no = b.td_no and a.td_member = b.td_member " + " join lrtd00h c on a.td_no = c.td_no " + "\r\n" + " where c.td_owner = '" + userid + "'"; conn.ExecuteSQL(SQL); SQL = " update a set " + "\r\n" + " a.td_edate = b.td_edate " + "\r\n" + " from lrtd00d1 a(nolock) " + "\r\n" + " join " + tmp_LRTD00D1 + " b on a.td_no = b.td_no and a.td_member = b.td_member " + " join lrtd00h c(nolock) on a.td_no = c.td_no " + "\r\n" + " join " + tmp_LRTD00H + " d on c.td_no = d.td_no " + "\r\n" + " where c.td_owner = '" + userid + "' and d.canback = '1' and a.td_role = '2'"; conn.ExecuteSQL(SQL); //新增 SQL = " insert into lrtd00d1(ie_ymd,ie_time,ie_user,td_no,td_member,td_role,td_flag) " + "\r\n" + " select '" + ymd + "','" + time + "','" + userid + "',a.td_no,a.td_member,a.td_role,a.td_flag" + "\r\n" + " from " + tmp_LRTD00D1 + " a " + "\r\n" + " where not exists(select 1 from lrtd00d1 x where x.td_no = a.td_no " + "\r\n" + " and x.td_member = a.td_member)"; conn.ExecuteSQL(SQL); #endregion owner 對角色的改變 #region owner 對關鍵字的改變 SQL = " delete a " + " from lrtd00d2 a " + " join " + tmp_LRTD00H + " b on a.td_no = b.td_no " + " join lrtd00h c(nolock) on c.td_no = b.td_no " + " where b.td_owner = '" + userid + "'" + " and not exists(select 1 from " + tmp_LRTD00D2 + " x where x.td_no = a.td_no and x.td_tag=a.td_tag)"; conn.ExecuteSQL(SQL); SQL = " insert into lrtd00d2(ie_ymd,ie_time,ie_user,td_no,td_tag) " + "\r\n" + " select distinct '" + ymd + "','" + time + "','" + userid + "',a.td_no,a.td_tag" + " from " + tmp_LRTD00D2 + " a " + " join lrtd00h b on a.td_no =b.td_no " + " where b.td_owner = '" + userid + "' and a.td_tag <> '' " + " and not exists(select 1 from lrtd00d2 x(nolock) where x.td_no=a.td_no and x.td_tag=a.td_tag)"; conn.ExecuteSQL(SQL); #endregion owner 對關鍵字的改變 #region 對附件的操作 分開操作 //SQL = // " delete a " + // " from lrtdfile a " + // " join " + tmp_LRTD00H + " b on a.td_no = b.td_no " + // " join lrtd00h c(nolock) on c.td_no = b.td_no " + // " where b.td_owner = '" + userid + "'" + // " and not exists(select 1 from " + tmp_LRTDFILE + " x where x.td_guid=a.td_guid)"; //conn.ExecuteSQL(SQL); //SQL = // " insert into lrtdfile(ie_ymd,ie_time,ie_user,td_no,td_file,td_guid,td_type,td_remk) " + // " select '" + ymd + "', '" + time + "','" + userid + "',a.td_no," + "\r\n" + // " a.td_file,a.td_guid,a.td_type,a.td_remk "+ // " from " + tmp_LRTDFILE + " a " + // " join lrtd00h b on a.td_no =b.td_no " + // " where b.td_owner = '" + userid + "'" + // " and not exists(select 1 from lrtdfile x(nolock) where x.td_guid=a.td_guid)"; //conn.ExecuteSQL(SQL); #endregion 對附件的操作 #endregion owner 的操作 //3.非Onwer操作 #region 非owner 的操作 SQL = " update a set " + "\r\n" + " a.ie_lymd='" + ymd + "', " + "\r\n" + " a.ie_ltime='" + time + "', " + "\r\n" + " a.ie_luser='******'," + "\r\n" + " a.td_rsdate=b.td_rsdate," + "\r\n" + " a.td_rstime=b.td_rstime," + "\r\n" + " a.td_redate=b.td_redate," + "\r\n" + " a.td_retime=b.td_retime," + "\r\n" + //更新當前用戶資料 預計完成 實際完成 " a.td_prsdate=b.td_prsdate," + "\r\n" + " a.td_predate=b.td_predate," + "\r\n" + " a.td_sdate=b.td_sdate," + "\r\n" + " a.td_edate=b.td_edate," + "\r\n" + " a.td_hold=b.td_hold," + "\r\n" + " a.td_top=b.td_top," + "\r\n" + " a.td_flag=b.td_flag," + "\r\n" + //日誌 " a.td_sylog=b.td_sylog," + "\r\n" + //讀取屬性 " a.td_state='1' " + "\r\n" + " from lrtd00d1 a(nolock) " + "\r\n" + " join " + tmp_LRTD00D1 + " b on a.td_no=b.td_no and a.td_member=b.td_member and a.td_role=b.td_role " + "\r\n" + " join lrtd00h c(nolock) on a.td_no=c.td_no " + "\r\n" + " where c.td_owner<>'" + userid + "' and a.td_member='" + userid + "'" + " and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')"; conn.ExecuteSQL(SQL); //如果是自己的提醒時間 / 狀態的變化 //那麼就不要更新日誌 並且 只更新狀態的欄位 SQL = " update a set " + "\r\n" + " a.ie_lymd='" + ymd + "', " + "\r\n" + " a.ie_ltime='" + time + "', " + "\r\n" + " a.ie_luser='******'," + "\r\n" + " a.td_rsdate=b.td_rsdate, " + "\r\n" + " a.td_rstime=b.td_rstime, " + "\r\n" + " a.td_redate=b.td_redate, " + "\r\n" + " a.td_retime=b.td_retime, " + "\r\n" + " a.td_top=b.td_top, " + "\r\n" + " a.td_flag=b.td_flag," + "\r\n" + " a.td_hold=b.td_hold, " + "\r\n" + //讀取屬性 " a.td_state='1' " + "\r\n" + " from lrtd00d1 a(nolock) " + "\r\n" + " join " + tmp_LRTD00D1 + " b on a.td_no=b.td_no and a.td_member=b.td_member and a.td_role=b.td_role " + "\r\n" + " join lrtd00h c(nolock) on a.td_no=c.td_no " + "\r\n" + " where c.td_owner<>'" + userid + "' and a.td_member='" + userid + "'" + " and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '1')"; conn.ExecuteSQL(SQL); #endregion 非owner 的操作 //4.更新狀態 #region 更新所有人的狀態為未讀 //是owner 且 本次修改不是狀態修改 conn.ExecuteSQL( " update a set " + "\r\n" + " td_state = '0', " + "\r\n" + " ie_lymd = '" + ymd + "', " + "\r\n" + " ie_ltime = '" + time + "', " + "\r\n" + " ie_luser = '******' " + "\r\n" + " from lrtd00d1 a(nolock) " + "\r\n" + " join lrtd00h b on a.td_no = b.td_no" + "\r\n" + " where b.td_owner = '" + userid + "' " + "\r\n" + " and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')"); // 不是owner 且 本次修改不是狀態修改 conn.ExecuteSQL( " update a set " + "\r\n" + " a.td_state = '0', " + "\r\n" + " a.ie_lymd = '" + ymd + "', " + "\r\n" + " a.ie_ltime = '" + time + "', " + "\r\n" + " a.ie_luser = '******' " + "\r\n" + " from lrtd00d1 a(nolock) " + "\r\n" + " join lrtd00h b on a.td_no = b.td_no" + "\r\n" + " where b.td_owner <> '" + userid + "' and td_member <> '" + userid + "'" + "\r\n" + " and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')"); // 不是owner 且 本次修改不是狀態修改 conn.ExecuteSQL( " update a set a.td_state = '0'," + " a.ie_lymd='" + ymd + "'," + " a.ie_ltime='" + time + "'," + " a.ie_luser='******' " + " from lrtd00h a(nolock) " + " where a.td_owner <> '" + userid + "'" + " and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')"); #endregion 更新所有人的狀態為未讀 //5.用戶目錄修改 #region 用戶目錄 //狀態變化 不需要更新附件 //刪除不一樣的 SQL = " delete a " + " from lrtdship a(nolock) " + " join " + tmp_LRTD00H + " b on a.td_no=b.td_no " + " where a.user_no='" + userid + "' and a.td_dir <> b.td_dir "; conn.ExecuteSQL(SQL); //新增不存在的 SQL = " insert into lrtdship(ie_ymd,ie_time,ie_user,td_no,user_no,td_dir) " + " select '" + ymd + "', '" + time + "','" + userid + "',a.td_no,'" + userid + "',a.td_dir " + " from " + tmp_LRTD00H + " a " + " where a.td_dir <> '' " + " and not exists(select 1 from lrtdship x where x.td_no = a.td_no and x.user_no ='" + userid + "')"; conn.ExecuteSQL(SQL); #endregion 目錄跟用戶有關 //6.發送郵件 #region 發郵件 //如果td_edate<>'' 且 canback = '1' 且 owner 不是 執行者 表示 有回饋完成 #region 完成日期的回饋/否決 //執行者回饋完成 SQL = " select '" + ymd + "','" + time + "','" + userid + "',isnull(b.user_email,'') toAddr," + " isnull((select isnull(ss.user_email,tt.td_member)+';' " + "\r\n" + " from lrtd00d1 tt (nolock) " + "\r\n" + " left join lrtduser ss (nolock) on tt.td_member=ss.user_no " + "\r\n" + " where tt.td_role <> '2' and tt.td_no = a.td_no " + "\r\n" + " for xml path('')),'') ccAddr," + " '[待辦事項] 來自 [' + isnull(b.user_name,a.td_owner) + ']' lr_subject," + "\r\n" + " 'To All:<br> 待辦事項單號: '+a.td_no+' ('+a.td_name+') 已經完成<br> 事項說明:<br> '+a.td_description lr_content,'2','1' " + "\r\n" + " from " + tmp_LRTD00H + " a(nolock) " + "\r\n" + " left join lrtduser b on a.td_owner = b.user_no " + "\r\n" + " left join lrtd00d1 c on a.td_no = c.td_no and c.td_role = '2' " + "\r\n" + " left join lrtduser d on c.td_member = d.user_no " + "\r\n" + " where a.canback = '1' and a.td_edate <> '' and isnull(d.user_no,a.td_owner) = '" + userid + "'" + " and a.td_owner <> isnull(d.user_no,a.td_owner) and isnull(b.user_email,'') <> ''";//完成 conn.ExecuteSQL("insert into lrtdmail(ie_ymd,ie_time,ie_user,lr_to,lr_cc,lr_subject,lr_content,lr_type,lr_ftype) " + SQL); //DataTable emails = conn.OpenDataTable(SQL, CommandType.Text); //foreach (DataRow dr in emails.Rows) //{ // SendEmail(dr["toAddr"].ToString(), dr["ccAddr"].ToString(), "To All:<br> 待辦事項單號:" + dr["td_no"].ToString() + "(" + dr["td_name"].ToString() + ") 已經完成<br> 事項說明:<br> " + dr["td_description"].ToString(), "[待辦事項] 來自 [" + dr["executor_name"].ToString() + "]", ""); //} //如果td_edate='' 且 canback = '1' 且 owner 不是 執行者 表示 被否決完成了 //owner 的否決才算 SQL = " select '" + ymd + "','" + time + "','" + userid + "',isnull(d.user_email,d.user_no) toAddr," + " isnull((select isnull(ss.user_email,tt.td_member)+';' " + "\r\n" + " from lrtd00d1 tt (nolock) " + "\r\n" + " left join lrtduser ss (nolock) on tt.td_member=ss.user_no " + "\r\n" + " where tt.td_role <> '2' and tt.td_no = a.td_no " + "\r\n" + " for xml path('')),'') ccAddr," + " '[待辦事項] 來自 [' + isnull(b.user_name,a.td_owner) + ']' lr_subject," + "\r\n" + " 'To All:<br> 待辦事項單號:' + a.td_no + '(' + a.td_name + ') 已被[' + isnull(b.user_name,a.td_owner) + ']否決結果,請重新執行該項目!<br> 事項說明:<br> ' + a.td_description lr_content,'2','1' " + "\r\n" + " from " + tmp_LRTD00H + " a(nolock) " + "\r\n" + " left join lrtduser b on a.td_owner = b.user_no " + "\r\n" + " left join lrtd00d1 c on a.td_no = c.td_no and c.td_role = '2' " + "\r\n" + " left join lrtduser d on c.td_member = d.user_no " + "\r\n" + " where a.canback = '1' and a.td_edate = '' and a.td_owner = '" + userid + "'" + " and a.td_owner <> isnull(d.user_no,a.td_owner) and isnull(b.user_email,'') <> ''";//否決 conn.ExecuteSQL("insert into lrtdmail(ie_ymd,ie_time,ie_user,lr_to,lr_cc,lr_subject,lr_content,lr_type,lr_ftype) " + SQL); //emails = conn.OpenDataTable(SQL, CommandType.Text); //foreach (DataRow dr in emails.Rows) //{ // SendEmail(dr["toAddr"].ToString(), dr["ccAddr"].ToString(), "To All:<br> 待辦事項單號:" + dr["td_no"].ToString() + "(" + dr["td_name"].ToString() + ") 已被[" + dr["ownername"].ToString() + "]否決結果,請重新執行該項目!<br> 事項說明:<br> " + dr["td_description"].ToString(), "[待辦事項] 來自 [" + dr["ownername"].ToString() + "]", ""); //} #endregion 完成日期的回饋/否決 //Owner有變更執行者的 send_msg conn.ExecuteSQL( " update d set d.send_msg = 'Y' " + " from " + tmpExecutor + " d(nolock) " + " join " + " (" + " select a.td_no,b.td_member td_executor " + "\r\n" + " from lrtd00h a(nolock) " + "\r\n" + " join lrtd00d1 b(nolock) on a.td_no=b.td_no and b.td_role ='2' " + "\r\n" + " left join " + tmp_LRTD00H + " c on a.td_no = c.td_no " + "\r\n" + " where a.td_owner = '" + userid + "'" + "\r\n" + " ) x on d.td_no = x.td_no " + " where d.td_executor<> x.td_executor"); SendMsgAndEmail(conn, tmpExecutor, true, false, userid); #endregion 發郵件 #region 最後寫日誌 DataTable Msgs = new DataTable("t1"); Msgs.Columns.Add("td_no", typeof(string)); Msgs.Columns.Add("msg_text", typeof(string)); DataTable dt = conn.OpenDataTable( " select td_no,td_sylog " + " from " + tmp_LRTD00H + " where justread = '0' and td_owner = '" + userid + "'" + " union all " + " select a.td_no,a.td_sylog " + " from " + tmp_LRTD00D1 + " a(nolock) " + " join " + tmp_LRTD00H + " b(nolock) on a.td_no=b.td_no " + " where b.td_owner<>'" + userid + "' and b.justread = '0' and a.td_member = '" + userid + "'", CommandType.Text ); foreach (DataRow dr in dt.Rows) { DataRow newRow = Msgs.NewRow(); newRow["td_no"] = dr["td_no"].ToString(); newRow["msg_text"] = dr["td_sylog"].ToString(); Msgs.Rows.Add(newRow); } InsertMsgs(userid, DateTime.Now, Msgs, conn); #endregion 寫日誌 conn._Transaction.Commit(); conn.CloseConnection(); } catch (System.Exception ex) { conn._Transaction.Rollback(); conn.CloseConnection(); WriteLog(userid, ex.Message, "ErrLog"); throw ex; } } }
/// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="User"></param> /// <param name="Voice"></param> /// <param name="td_no"></param> /// <param name="msg_to"></param> /// <param name="MsgType">0=群消息,1=個人消息</param> /// <param name="GUID"></param> /// <returns></returns> public string InsertVoice(SQLHelper conn, string User, byte[] Voice, string td_no, string msg_to, string MsgType, string GUID, string VoiceSeconds) { try { long SeqNO = 0; string SQL = "select msg_no from lrtdmsg (nolock) where msg_guid='" + GUID + "'"; DataTable dtSeq = conn.OpenDataTable(SQL, CommandType.Text); if (dtSeq.Rows.Count > 0) { //重復就不寫入 return dtSeq.Rows[0]["msg_no"].ToString(); } else //lock (Ticket) 這里先不鎖,單純靠SQL的鎖機制來處理 { dtSeq = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '1',@seqNO output select @seqNO", CommandType.Text); if (dtSeq.Rows.Count > 0) { SeqNO = long.Parse(dtSeq.Rows[0][0].ToString()); } } if (SeqNO > 0) { DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); SQL = "insert into lrtdmsg (ie_ymd,ie_time,ie_user,td_no,msg_from,msg_to,msg_date,msg_time,msg_text,msg_no,msg_type,msg_doctype,msg_docstate,msg_guid) " + "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "','" + msg_to + "','" + ymd + "','" + time + "',N'" + VoiceSeconds + "'," + SeqNO + ",'" + MsgType + "','6','0','" + GUID + "')"; conn._Transaction = conn._Connection.BeginTransaction(); conn.ExecuteSQL(SQL); SQL = "insert into lrmsgimg (ie_ymd,ie_time,ie_user,msg_no,msg_img) " + "values('" + ymd + "','" + time + "','" + User + "'," + SeqNO + ",@voice)"; //寫入完整圖 conn.ExecuteSQLImage(SQL, "@voice", Voice); if (td_no.Length > 0) { // ##發群組## //寫表頭Owner SQL = " insert into lrmsgstate (msg_no,msg_to,msg_state)" + "\r\n" + " select a.msg_no,b.td_owner,'0' " + "\r\n" + " from lrtdmsg a(nolock)" + "\r\n" + " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" + " where a.msg_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + " and b.td_owner <> '" + User + "'"; conn.ExecuteSQL(SQL); //寫表身相關人 SQL = " insert into lrmsgstate (msg_no,msg_to,msg_state)" + "\r\n" + " select a.msg_no,b.td_member,'0' " + "\r\n" + " from lrtdmsg a(nolock)" + "\r\n" + " join lrtd00d1 b(nolock) on a.td_no = b.td_no " + "\r\n" + " join lrtd00h c (nolock) on b.td_no=c.td_no " + "\r\n" + " where a.msg_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + " and b.td_member <> '" + User + "' and b.td_member <> c.td_owner "; conn.ExecuteSQL(SQL); // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','" + td_no + "','','G','" + ymd + "','" + time + "'," + SeqNO); } else if (msg_to.Length > 0) { // ##發給個人## SQL = "insert into lrmsgstate (msg_no,msg_to,msg_state) values('" + SeqNO + "','" + msg_to + "','0')"; conn.ExecuteSQL(SQL); // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','','" + msg_to + "','P','" + ymd + "','" + time + "'," + SeqNO); } conn._Transaction.Commit(); } return SeqNO.ToString(); //return new string[] { curTDID, ymd, time }; } catch (System.Exception ex) { //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + ex.Message); LogHelper.WriteLog("InsertVoice()", "將音頻寫入數據庫時:" + (ex != null ? ex.Message : "")); conn._Transaction.Rollback(); throw ex; } }
private void btnRefresh_Click(object sender, EventArgs e) { listSubSrvs.Items.Clear(); using (SQLHelper conn = new SQLHelper(DBAlias)) { conn.OpenConnection(); try { string SQL = "select * from lxsubsrvs (nolock) where srv_device = '" + Device + "'"; DataTable Rec = conn.OpenDataTable(SQL, CommandType.Text); conn.CloseConnection(); foreach (DataRow row in Rec.Rows) { ListViewItem lvi = new ListViewItem(new string[2] { row["srv_no"].ToString(), row["srv_state"].ToString() }); listSubSrvs.Items.Add(lvi); } Rec.Dispose(); Rec = null; } catch { conn.CloseConnection(); } } }
public string InsertCmd(SQLHelper conn, string User, string Msg, string td_no, string GUID) { try { long SeqNO = 0; string SQL = "select cmd_no from lrtdcmd (nolock) where cmd_guid='" + GUID + "'"; DataTable dtSeq = conn.OpenDataTable(SQL, CommandType.Text); if (dtSeq.Rows.Count > 0) { //重復就不寫入 return dtSeq.Rows[0]["cmd_no"].ToString(); } else { dtSeq = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '3',@seqNO output select @seqNO", CommandType.Text); if (dtSeq.Rows.Count > 0) { SeqNO = long.Parse(dtSeq.Rows[0][0].ToString()); } } if (SeqNO > 0) { DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); //conn._Transaction = conn._Connection.BeginTransaction(); SQL = "insert into lrtdcmd (ie_ymd,ie_time,ie_user,td_no,cmd_user,cmd_text,cmd_date,cmd_time,cmd_no,cmd_guid) " + "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "',N'" + Msg.Replace("'", "''") + "','" + ymd + "','" + time + "'," + SeqNO + ",'" + GUID + "')"; conn.ExecuteSQL(SQL); //if (td_no.Length > 0) //{ // // ##發群組## // //寫表頭Owner // SQL = // " insert into lrcmdstate (cmd_no,cmd_to,cmd_state)" + "\r\n" + // " select a.cmd_no,b.td_owner,'0' " + "\r\n" + // " from lrtdcmd a(nolock)" + "\r\n" + // " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" + // " where a.cmd_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + // " and b.td_owner <> '" + User + "'"; // conn.ExecuteSQL(SQL); // //寫表身相關人 // SQL = // " insert into lrcmdstate (cmd_no,cmd_to,cmd_state)" + "\r\n" + // " select a.cmd_no,b.td_member,'0' " + "\r\n" + // " from lrtdcmd a(nolock)" + "\r\n" + // " join lrtd00d1 b(nolock) on a.td_no = b.td_no " + "\r\n" + // " join lrtd00h c (nolock) on b.td_no=c.td_no " + "\r\n" + // " where a.cmd_guid ='" + GUID + "' and a.td_no = '" + td_no + "'" + // " and b.td_member <> '" + User + "' and b.td_member <> c.td_owner "; // conn.ExecuteSQL(SQL); //} //conn._Transaction.Commit(); } return SeqNO.ToString(); } catch (System.Exception ex) { //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + ex.Message); LogHelper.WriteLog("StartUp()", "將命令寫入數據庫時:" + (ex != null ? ex.Message : "")); //conn._Transaction.Rollback(); throw ex; } }
/// <summary> /// 群異動系統消息 /// </summary> /// <param name="Sender"></param> /// <param name="ToGroup"></param> /// <param name="MsgGUID"></param> /// <param name="MsgText"></param> protected void SendGroupSysMessage(string Sender, string ToGroup, string MsgGUID, string MsgText) { // 抓出群成員 using (SQLHelper conn = new SQLHelper(m_asyncSocketServer.DBInfo)) { conn.OpenConnection(); try { // 記錄到數據庫中 string MsgNO = this.InsertMsg(conn, Sender, MsgText, ToGroup, "", "0", MsgGUID, false, true); DataTable UserList = conn.OpenDataTable("select a.msg_to from lrmsgstate a (nolock) where a.msg_no=" + MsgNO + " and a.msg_state='0'", CommandType.Text); if (UserList.Rows.Count > 0) { string Users = ""; // PC 和 Mobile 要用到的信息.提前打包好,沒必要放到for循環里去做 List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(MsgText, "5", "", "", ToGroup, MsgGUID, MsgNO); // Web 要用到的信息.提前打包好,沒必要放到for循環里去做 - 暫時不實現 //byte[] msgw = WebConvertMethod.ConvertMsgToByte(DataBody, "5", "", "", DataGroup, MsgNO); foreach (DataRow User in UserList.Rows) { //有沒有必要PC和MOBLIE端各開個一個欄位來存放消息是否讀取的標記? #region " PC Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.PcUserTokenList, "SendGroupSysMessage"); #endregion #region " Mobile Client " Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.MobileUserTokenList, "SendGroupSysMessage"); #endregion #region " Web Client - 暫時不實現" //recUser = _ws_transmit_tb[User["msg_to"].ToString()] as UserSocket; //if (recUser != null) //{ // Socket userSkt = recUser.Socket; // try // { // //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 // lock (userSkt) // { // userSkt.Send(msgw); // //SendData(userSkt, msg); // } // Users += "'" + User["msg_to"].ToString() + "',"; // } // catch (SocketException ex) // { // LogHelper.WriteLog("SendGroupSysMessage", "手機用戶:" + User["msg_to"].ToString() + "異常斷開連接:" + (ex != null ? ex.Message : "")); // try // { // //掉線了,直接干掉. // if (userSkt.Connected) // { // userSkt.Shutdown(SocketShutdown.Both); // //userSkt.Disconnect(false); // } // userSkt.Close(); // #region " 聯絡人狀態變更 " // //如果PC端沒在線就通知聯絡人此人離線了 // ProcessWebUserState(recUser); // #endregion // lock (_ws_transmit_tb.SyncRoot) // _ws_transmit_tb.Remove(User["msg_to"].ToString()); // Thread userThread = _ws_thread_tb[User["msg_to"].ToString()] as Thread; // userThread.Abort(); // //線程池不會被遍歷,不鎖. // _ws_thread_tb.Remove(User["msg_to"].ToString()); // } // catch // { } // } //} #endregion } // 去掉最後一個逗號 if (Users.Length > 0) { Users = Users.Substring(0, Users.Length - 1); // 標記為已讀 DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn.ExecuteSQL("update a set a.msg_state='1',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lrmsgstate a where a.msg_no=" + MsgNO + " and a.msg_state='0' and a.msg_to in (" + Users + ")"); } } UserList.Dispose(); UserList = null; conn.CloseConnection(); #region " 向發送者發送回執7-03 " PushFeedback(MsgGUID, MsgNO); #endregion #region " 處理同帳號設備之間發送的消息漫遊 " //處理同一帳號多個端登錄的轉發. if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.PC || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自PC端消息. //檢查相同帳號Mobile(Web)端有沒有登錄,有就轉發一次. #region " Mobile Client " PushRoaming(Sender, ParseProtocol.ConvertMsgToByte(MsgText, "5", "", "", ToGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.MobileUserTokenList); #endregion } if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.Mobile || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自Mobile(Web)端消息. //檢查相同帳號Web端有沒有登錄,有就轉發一次. #region " PC Client " PushRoaming(Sender, ParseProtocol.ConvertMsgToByte(MsgText, "5", "", "", ToGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.PcUserTokenList); #endregion } #endregion } catch { conn.CloseConnection(); } } }
/// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="User"></param> /// <param name="Msg"></param> /// <param name="td_no"></param> /// <param name="msg_to"></param> /// <param name="MsgType">0=群消息,1=個人消息</param> /// <param name="MsgPullType">拉取式消息子分類(空=普通消息;LXSubject=主題;自定義表名=什麼都可以)</param> /// <param name="MsgDocType">0=File;1=Text;2=Offline File;3=Image;4=Mail;5=Quake</param> /// <param name="MsgDocState">文件狀態.</param> /// <param name="GUID"></param> /// <param name="ForTest"></param> /// <param name="IsPushMode"></param> /// <returns></returns> public string InsertSubSrvFeedback(SQLHelper conn, string User, string Msg, string td_no, string msg_to, string MsgType, string MsgPullType, string MsgDocType, string MsgDocState, string GUID) { try { long SeqNO = 0; string SQL = "select msg_no from lrtdmsg (nolock) where msg_guid='" + GUID + "'"; DataTable dtSeq = conn.OpenDataTable(SQL, CommandType.Text); if (dtSeq.Rows.Count > 0) { //重復就不寫入 return dtSeq.Rows[0]["msg_no"].ToString(); } else //lock (Ticket) 這里先不鎖,單純靠SQL的鎖機制來處理 { dtSeq = conn.OpenDataTable("declare @seqNO bigint exec GetMessageNO '1',@seqNO output select @seqNO", CommandType.Text); if (dtSeq.Rows.Count > 0) { SeqNO = long.Parse(dtSeq.Rows[0][0].ToString()); } } if (SeqNO > 0) { DateTime now = DateTime.Now; string ymd = now.ToString("yyyy/MM/dd"); string time = now.ToString("HH:mm:ss"); conn._Transaction = conn._Connection.BeginTransaction(); SQL = "insert into lrtdmsg (ie_ymd,ie_time,ie_user,td_no,msg_from,msg_to,msg_date,msg_time,msg_text,msg_no,msg_type,msg_datatype,msg_doctype,msg_docstate,msg_guid) " + "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "','" + msg_to + "','" + ymd + "','" + time + "',N'" + Msg.Replace("'", "''") + "'," + SeqNO + ",'" + MsgType + "','" + MsgPullType + "','" + MsgDocType + "','" + MsgDocState + "','" + GUID + "')"; conn.ExecuteSQL(SQL); // 寫接收人 SQL = " insert into lrmsgstate (ie_ymd,ie_time,ie_user,msg_no,msg_to,msg_state)" + "\r\n" + " values('" + ymd + "','" + time + "','" + User + "'," + SeqNO + ",'" + msg_to + "','0')"; conn.ExecuteSQL(SQL); // Added by Donnie on 2016/06/01 // 更新Recents //@USER_NO varchar(50), @TD_NO varchar(50),@MSG_TO varchar(50), @DATA_TYPE varchar(1), @RC_DATE varchar(10), @RC_TIME varchar(8), @MSG_NO numeric(28,0) conn.ExecuteSQL("exec UpdateRecents '" + User + "','" + td_no + "','','G','" + ymd + "','" + time + "'," + SeqNO); conn._Transaction.Commit(); } return SeqNO.ToString(); } catch (System.Exception ex) { //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + ex.Message); LogHelper.WriteLog("StartUp()", "將消息寫入數據庫時:" + (ex != null ? ex.Message : "")); conn._Transaction.Rollback(); throw ex; } }
/// <summary> /// 系統公告消息 /// </summary> /// <param name="Command"></param> /// <param name="Seq"></param> /// <param name="Region"></param> /// <param name="MsgText"></param> protected void SendSysNotice(string Command, string Seq, string Region, string MsgText) { #region " 更新公告內容和區域 " lock (m_asyncSocketServer.SysNotices.SyncRoot) { if (m_asyncSocketServer.SysNotices.ContainsKey(Region)) { //ALL 也會存里面 List<NoticeMsg> nMsgs = (List<NoticeMsg>)m_asyncSocketServer.SysNotices[Region]; NoticeMsg nMsg = nMsgs.Find(delegate(NoticeMsg _msg) { return _msg.MsgSeq == Seq; }); if (nMsg != null) { if (Command == "1") { // 替換現有的公告 nMsg.MsgText = MsgText; } else { // 移除現有的公告 nMsgs.Remove(nMsg); if (nMsgs.Count == 0) { // 當前區域沒有公告了,將區域移除 m_asyncSocketServer.SysNotices.Remove(Region); } } } else { if (Command == "1") { // 新公告 nMsgs.Add(new NoticeMsg() { MsgSeq = Seq, MsgText = MsgText }); } } } else { if (Command == "1") { //新公告 m_asyncSocketServer.SysNotices.Add(Region, new List<NoticeMsg>() { new NoticeMsg() { MsgSeq = Seq, MsgText = MsgText } }); } } } #endregion // 抓出群成員 using (SQLHelper conn = new SQLHelper(m_asyncSocketServer.DBInfo)) { conn.OpenConnection(); try { #region " 更新廣播內容到數據庫 " if (conn.OpenDataTable("select uid from lrbroadcast (nolock) where region_id = '" + Region + "' and bdc_seq = '" + Seq + "'", CommandType.Text).Rows.Count > 0) { //有資料 conn.ExecuteSQL("update a set a.bdc_cmd='" + Command + "',a.bdc_text=N'" + MsgText + "' from lrbroadcast a where a.region_id = '" + Region + "' and a.bdc_seq = '" + Seq + "'"); } else { //沒資料 if (Command == "1") { conn.ExecuteSQL("insert into lrbroadcast (region_id,bdc_seq,bdc_text,bdc_cmd) values('" + Region + "','" + Seq + "',N'" + MsgText + "','" + Command + "')"); } } #endregion string strWhere = "where ie_cancel <> 'Y' and user_state <> '6'"; if (Region.ToUpper() == "ALL") { strWhere += " and user_region > ''"; } else { strWhere += " and user_region = '" + Region + "'"; } DataTable UserList = conn.OpenDataTable("select user_no from lrtduser (nolock) " + strWhere, CommandType.Text); //儘早釋放SQL資源 conn.CloseConnection(); if (UserList.Rows.Count > 0) { // 提前打包好,沒必要放到for循環里去做 List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(MsgText, "B", Command, Seq, Region, "", ""); foreach (DataRow User in UserList.Rows) { //有沒有必要PC和MOBLIE端各開個一個欄位來存放消息是否讀取的標記? #region " PC Client " PushMessage("", msgs, User["user_no"].ToString(), m_asyncSocketServer.PcUserTokenList, "SendSysNotice"); #endregion #region " Mobile Client " //Users = PushMessage(Users, msgs, User["msg_to"].ToString(), m_asyncSocketServer.MobileUserTokenList, "SendGroupSysMessage"); #endregion } } UserList.Dispose(); UserList = null; #region " 向發送者發送回執7-03 " PushFeedback("", ""); #endregion } catch { conn.CloseConnection(); } } }
private bool DoPcMobileLogin(string UserID, string DataTerminalName, string DataGroup, string DataText, SocketUserToken userToken, SocketUserTokenList UserTokenList) { bool Passed = true, Done = false; SocketUserToken ExistsUser = UserTokenList[UserID]; if (ExistsUser != null) { Socket curSkt = ExistsUser.ConnectSocket; string curIPAddress = ExistsUser.ConnectSocket.RemoteEndPoint.ToString(); #region " 發送離線 " try { List<byte[]> msgs = ParseProtocol.ConvertMsgToByte("00", "7", UserID, DataTerminalName, "", Guid.NewGuid().ToString().Replace("-", "").ToUpper(), ""); //如果2秒內得不到之前登陸的對象,就直接干掉 //否則此帳號將永遠無法登錄 if (Monitor.TryEnter(curSkt, 2000)) { try { //向用戶本人發送離線 foreach (byte[] msg in msgs) { //2016/05/19 test ExistsUser.AsyncSendAgent.DoSendBuffer(msg); //curSkt.Send(msg); //SendData(curSkt, msg); } } catch (SocketException ex) { LogHelper.WriteLog("DoPcMobileLogin()", "執行向" + DataGroup + "用戶: " + UserID + " [" + curIPAddress + "] 發送強制離線[不允許自動重連]消息時:" + (ex != null ? ex.Message : "")); } finally { Monitor.Exit(curSkt); } } else { //如果到這里說明之前登陸的對象已經死鎖 LogHelper.WriteLog("DoPcMobileLogin()", "嘗試使用Monitor.TryEnter鎖定" + DataGroup + "用戶: " + UserID + " [" + curIPAddress + "] 時失敗."); } } catch (SocketException ex) { LogHelper.WriteLog("DoPcMobileLogin()", "向" + DataGroup + "用戶: " + UserID + " [" + curIPAddress + "] 發送離線[不允許自動重連]消息時:" + (ex != null ? ex.Message : "")); } try { //運行到這里時,有可能客戶端收到7-00之後主動斷開連接,則會觸發CloseClientSocket. if (!ExistsUser.Closing) { //ExistsUser.Closing = true; CloseClientSocket(ExistsUser); } LogHelper.WriteLog("DoPcMobileLogin", string.Format("因相同帳號再次登錄,強制先登錄的用戶{0} [{1}] 下線.", UserID, curIPAddress)); UpdateUserLog(UserID, "1", "0", "因相同帳號再次登陸而強制離線.", "", null); } catch (SocketException ex) { LogHelper.WriteLog("DoPcMobileLogin()", "嘗試關閉" + DataGroup + "用戶: " + UserID + " [" + curIPAddress + "] 線程和Socket連接時出錯." + (ex != null ? ex.Message : "")); } #endregion } // 驗證是否為唯一用戶 if (UserTokenList.ContainsKey(UserID)) { //到這里說明前面強制離線沒有成功 try { List<byte[]> msgs = ParseProtocol.ConvertMsgToByte("登錄失敗.", "3", UserID, DataTerminalName, "", Guid.NewGuid().ToString().Replace("-", "").ToUpper(), ""); //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (userToken.ConnectSocket) { foreach (byte[] msg in msgs) { //同步發送 //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //userToken.ConnectSocket.Send(msg); //SendData(userToken.ConnectSocket, msg); } } LogHelper.WriteLog("ProcessLogin()", DataGroup + "用戶:" + UserID + "登錄失敗"); } catch (SocketException ex) { LogHelper.WriteLog("ProcessLogin()", "發送登陸失敗信息時" + (ex != null ? ex.Message : "")); } } else { // 服務器開放登錄功能 try { List<byte[]> msgs = ParseProtocol.ConvertMsgToByte("登錄成功.", "2", UserID, DataTerminalName, DataGroup, Guid.NewGuid().ToString().Replace("-", "").ToUpper(), ""); //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (userToken.ConnectSocket) { foreach (byte[] msg in msgs) { //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //userToken.ConnectSocket.Send(msg); //SendData(userToken.ConnectSocket, msg); } } } catch (Exception ex) { Passed = false; LogHelper.WriteLog("ProcessLogin()", "發送登錄成功信息時" + (ex != null ? ex.Message : "")); } if (Passed) { //設置對象狀態 userToken.BindingUser = new User(); userToken.BindingUser.State = User.UserStates.Online; userToken.BindingUser.Role = (DataGroup.Length > 0 ? User.UserRole.Mobile : User.UserRole.PC); //移動設備登錄為: //iPhone = "IPHONE" //iPad = "IPAD" //Android Phone = "APHONE" //Android Pad = "APAD" switch (DataGroup.ToUpper()) { case "IPHONE": userToken.BindingUser.Mobile = User.UserMobileType.iPhone; //iToken userToken.BindingUser.iToken = DataText; break; case "IPAD": userToken.BindingUser.Mobile = User.UserMobileType.iPad; //iToken userToken.BindingUser.iToken = DataText; break; case "APHONE": userToken.BindingUser.Mobile = User.UserMobileType.AndroidPhone; break; case "APAD": userToken.BindingUser.Mobile = User.UserMobileType.AndroidPad; break; default: //默認PC userToken.BindingUser.Mobile = User.UserMobileType.None; break; } userToken.BindingUser.ID = UserID; //Mac userToken.BindingUser.MacAddr = DataTerminalName; //IP userToken.BindingUser.IP = ((IPEndPoint)userToken.ConnectSocket.RemoteEndPoint).Address.ToString(); //設備名稱 userToken.BindingUser.DevName = DataText; Socket newClient = userToken.ConnectSocket; string UserRegion = ""; using (SQLHelper conn = new SQLHelper(DBInfo)) { conn.OpenConnection(); try { //取消日誌 //LogHelper.WriteLog("ProcessLogin()", "#5.登錄成功,準備截入聯絡人信息及推送未讀消息. " + UserID + " [" + userToken.ConnectSocket.RemoteEndPoint + "]"); #region " 載入聯絡人清單(聯絡人變更時應更新) " DataTable ContactsList = GetContacts(conn, UserID); if (ContactsList.Rows.Count > 0) { foreach (DataRow Contact in ContactsList.Rows) { //加入聯絡人(防呆自己加自己) if (UserID != Contact["contact_id"].ToString()) userToken.BindingUser.Contacts.Add(Contact["contact_id"].ToString()); } } ContactsList = null; #endregion #region " 當PC端和Mobile端未登錄的情況下才向其聯絡人推送用戶上線消息 " if (!m_pcUserTokenList.ContainsKey(UserID) && !m_mobileUserTokenList.ContainsKey(UserID)) //ID,狀態,簽名,聯絡人清單,頭像數據 if (userToken.BindingUser.Contacts.Count > 0) StateNotifyQueue.Enqueue(new List<object>() { userToken.BindingUser.ID, "1", userToken.BindingUser.Signature, userToken.BindingUser.Contacts, null }); #endregion #region " 發送所有未讀訊息和群命令 " #region " 訊息 " DataTable MsgsList = GetUnreadMsgs(conn, UserID, "M"); if (MsgsList.Rows.Count > 0) { if (newClient != null) { if (MsgsList.Rows.Count > 0) { long MaxMsgNO = 0; foreach (DataRow UnreadMsg in MsgsList.Rows) { // 記錄最大MsgNO if (MaxMsgNO < long.Parse(UnreadMsg["msg_no"].ToString())) MaxMsgNO = long.Parse(UnreadMsg["msg_no"].ToString()); // 發給登錄人 DateTime MsgDT = DateTime.Parse(UnreadMsg["msg_date"].ToString() + " " + UnreadMsg["msg_time"].ToString()); // 判斷個人消息還是群消息 // 消息子類型(msg_doctype)(文字消息/文件消息) // 0 = File // 1 = Text Msg // 2 = offline File // 3 = 截圖 // 4 = email // 5 = 抖動 // 6 = 音頻 List<byte[]> rtnMsgs = null; switch (UnreadMsg["msg_doctype"].ToString()) { case "0": rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["msg_text"].ToString(), "P", UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), "S", MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; case "2": rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["msg_text"].ToString(), "P", UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), "O", MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; case "3": rtnMsgs = ParseProtocol.ConvertMsgToByte((byte[])UnreadMsg["msg_img"], "I", UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), UnreadMsg["td_no"].ToString(), MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; case "4": rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["msg_text"].ToString(), "M", UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), UnreadMsg["td_no"].ToString(), MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; case "5": rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["msg_text"].ToString(), "Q", UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), UnreadMsg["td_no"].ToString(), MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; case "6": rtnMsgs = ParseProtocol.ConvertMsgToByte("Voice", "R", UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), UnreadMsg["td_no"].ToString(), MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; default: //1 string DataType = ""; if (UnreadMsg["msg_type"].ToString() == "0") { //小助手系統消息 DataType = "5"; } else { DataType = UnreadMsg["td_no"].ToString().Length > 0 ? "4" : (UnreadMsg["msg_doctype"].ToString() == "0" ? "P" : "U"); } rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["msg_text"].ToString(), DataType, UnreadMsg["msg_from"].ToString(), UnreadMsg["user_name"].ToString(), UnreadMsg["td_no"].ToString(), MsgDT, UnreadMsg["msg_guid"].ToString(), UnreadMsg["msg_no"].ToString(), ""); break; } //if (UnreadMsg["msg_doctype"].ToString() == "3") //{ // rtnMsgs = ParseProtocol.ConvertMsgToByte((byte[])UnreadMsg["msg_img"], // "I", // UnreadMsg["msg_from"].ToString(), // UnreadMsg["user_name"].ToString(), // UnreadMsg["td_no"].ToString(), // MsgDT, // UnreadMsg["msg_guid"].ToString(), // UnreadMsg["msg_no"].ToString(), // ""); //} //else //{ // rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["msg_text"].ToString(), // UnreadMsg["td_no"].ToString().Length > 0 ? "4" : (UnreadMsg["msg_doctype"].ToString() == "0" ? "P" : "U"), // UnreadMsg["msg_from"].ToString(), // UnreadMsg["user_name"].ToString(), // UnreadMsg["td_no"].ToString(), // MsgDT, // UnreadMsg["msg_guid"].ToString(), // UnreadMsg["msg_no"].ToString(), // ""); //} //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (newClient) { foreach (byte[] msg in rtnMsgs) { //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //newClient.Send(msg); //SendData(newClient, msg); } } } // 更新為已讀 try { MarkReadHistoryMsgQueue.Enqueue("update a set a.msg_state='1' from lrmsgstate a where a.msg_no<=" + MaxMsgNO.ToString() + " and a.msg_to='" + UserID + "' and a.msg_state='0'"); } catch (Exception ex) { LogHelper.WriteLog("ProcessLogin()", "標識歷史信息為已讀時:" + (ex != null ? ex.Message : "")); } } } //Test LogHelper.WriteLog("ProcessLogin()", "共向" + DataGroup + "用戶:" + userToken.BindingUser.ID + "推送" + MsgsList.Rows.Count.ToString() + "條歷史未讀消息."); } #endregion #region " 系統消息 " MsgsList = GetUnreadMsgs(conn, UserID, "S"); if (MsgsList.Rows.Count > 0) { if (newClient != null) { if (MsgsList.Rows.Count > 0) { long MaxMsgNO = 0; foreach (DataRow UnreadMsg in MsgsList.Rows) { // 記錄最大MsgNO if (MaxMsgNO < long.Parse(UnreadMsg["sys_id"].ToString())) MaxMsgNO = long.Parse(UnreadMsg["sys_id"].ToString()); // 發給登錄人 DateTime MsgDT = DateTime.Parse(UnreadMsg["sys_date"].ToString() + " " + UnreadMsg["sys_time"].ToString()); // 判斷個人消息還是群消息 List<byte[]> rtnMsgs = ParseProtocol.ConvertMsgToByte(UnreadMsg["sys_remk"].ToString(), UnreadMsg["sys_type"].ToString() == "1" ? "D" : "*", UnreadMsg["sys_type"].ToString(), UnreadMsg["sys_objtype"].ToString(), "*", MsgDT, UnreadMsg["sys_objid"].ToString(), UnreadMsg["sys_id"].ToString(), ""); //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (newClient) { foreach (byte[] msg in rtnMsgs) { //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //newClient.Send(msg); //SendData(newClient, msg); } } } // 更新為已讀 try { MarkReadHistoryMsgQueue.Enqueue("update a set a.sysmsg_state='1' from lrsysmsgstate a where a.sys_id<=" + MaxMsgNO.ToString() + " and a.sysmsg_to='" + UserID + "' and a.sysmsg_state='0'"); //conn.ExecuteSQL("update a set a.msg_state='1' from lrmsgstate a where a.msg_no<=" + MaxMsgNO.ToString() + " and a.msg_to='" + UserID + "' and a.msg_state='0'"); } catch (Exception ex) { LogHelper.WriteLog("ProcessLogin()", "標識歷史系統消息為已讀時:" + (ex != null ? ex.Message : "")); } } } //Test //LogHelper.WriteLog("ProcessLogin()", "共向用戶:" + User.ID + "推送" + MsgsList.Rows.Count.ToString() + "條歷史命令."); } #endregion MsgsList.Dispose(); MsgsList = null; #endregion //得到用戶區域代號 try { UserRegion = conn.OpenDataTable("select user_region from lrtduser (nolock) where user_no='" + UserID + "'", CommandType.Text).Rows[0][0].ToString(); } catch { UserRegion = ""; } } catch (SocketException ex) { //test //clsClientLog.WriteLog(clsClientLog.LogType.Error, "StartUp()", "向PC端" + DataTerminal + "推送未讀信息時出錯." + (ex != null ? ex.Message : "")); LogHelper.WriteLog("ProcessLogin()", "向" + DataGroup + "用戶:" + UserID + "推送未讀信息和系統消息時出錯." + (ex != null ? ex.Message : "")); } //更新用戶狀態 try { DoUpdateUserLog(conn, UserID, "0", ((int)userToken.BindingUser.Mobile).ToString(), DataGroup + "登錄", ((IPEndPoint)userToken.ConnectSocket.RemoteEndPoint).Address.ToString(), DataText, DataTerminalName, null); DoUpdateUserState(conn, UserID, "1"); //上線中 } catch (Exception ex) { Passed = false; LogHelper.WriteLog("ProcessLogin()", "更新" + DataGroup + "用戶:" + UserID + "狀態." + (ex != null ? ex.Message : "")); } conn.CloseConnection(); } //只推PC端用戶 if (Passed && DataGroup.Length == 0) { #region " 向PC客戶端推送平臺數據(在線人數等) " try { List<byte[]> MsgToPC = ParseProtocol.ConvertMsgToByte(string.Format(SysMessage, ActiveUsersCount, OnlineUsersCount, RegisteredUsersCount, TodayMessagesCount, TotalMessagesCount), "9", "", "", "", "", ""); //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (newClient) { foreach (byte[] msg in MsgToPC) { //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //newClient.Send(msg); //SendData(newClient, msg); } } } catch (SocketException ex) { //這個沒必要斷開 //Passed = false; //clsClientLog.WriteLog(clsClientLog.LogType.Error, "StartUp()", "向PC端" + DataTerminal + "推送未讀信息時出錯." + (ex != null ? ex.Message : "")); LogHelper.WriteLog("ProcessLogin()", "向PC端" + UserID + "推送平臺數據(在線人數等)." + (ex != null ? ex.Message : "")); } #endregion #region " 向PC客戶端推送系統公告 " try { if (SysNotices.Count > 0 && UserRegion.Length > 0 && (SysNotices.ContainsKey(UserRegion) || SysNotices.ContainsKey("ALL"))) { List<NoticeMsg> nMsgs; // 全服廣播 object AllNotices = SysNotices["ALL"]; if (AllNotices != null) { nMsgs = (List<NoticeMsg>)AllNotices; List<byte[]> MsgNotice; foreach (NoticeMsg nMsg in nMsgs) { MsgNotice = ParseProtocol.ConvertMsgToByte(nMsg.MsgText, "B", "1", nMsg.MsgSeq, "ALL", "", ""); //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (newClient) { foreach (byte[] msg in MsgNotice) { //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //newClient.Send(msg); //SendData(newClient, msg); } } } } // 區域廣播 object RegionNotices = SysNotices[UserRegion]; if (RegionNotices != null) { nMsgs = (List<NoticeMsg>)RegionNotices; List<byte[]> MsgNotice; foreach (NoticeMsg nMsg in nMsgs) { MsgNotice = ParseProtocol.ConvertMsgToByte(nMsg.MsgText, "B", "1", nMsg.MsgSeq, UserRegion, "", ""); //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 lock (newClient) { foreach (byte[] msg in MsgNotice) { //2016/05/19 test userToken.AsyncSendAgent.DoSendBuffer(msg); //newClient.Send(msg); //SendData(newClient, msg); } } } } } } catch (SocketException ex) { //這個沒必要斷開 //Passed = false; //clsClientLog.WriteLog(clsClientLog.LogType.Error, "StartUp()", "向PC端" + DataTerminal + "推送未讀信息時出錯." + (ex != null ? ex.Message : "")); LogHelper.WriteLog("ProcessLogin()", "向PC端" + UserID + "推送系統公告." + (ex != null ? ex.Message : "")); } #endregion } if (Passed) { //添加到正在執行的列表中 if (UserTokenList.ContainsKey(UserID)) { ExistsUser = UserTokenList[UserID]; if (ExistsUser != null) { try { LogHelper.WriteLog("DoPcMobileLogin", string.Format("加入在線人員列表時發現已有同名帳號在里面,強制用戶{0} [{1}] 下線.", UserID, ExistsUser.ConnectSocket.RemoteEndPoint)); if (!ExistsUser.Closing) { //ExistsUser.Closing = true; CloseClientSocket(ExistsUser); } } catch (SocketException ex) { LogHelper.WriteLog("ProcessLogin()", "嘗試關閉" + DataGroup + "用戶" + UserID + "線程和Socket連接時出錯." + (ex != null ? ex.Message : "")); } } //LogHelper.WriteLog("ProcessLogin()", "#5.加入在線人員列表時發現已有同名帳號在里面. " + UserID + " [" + userToken.ConnectSocket.RemoteEndPoint + "]"); } UserTokenList.Add(UserID, userToken); //取消日志 //LogHelper.WriteLog("ProcessLogin()", "#6.已加入到在線人員列表. " + UserID + " [" + userToken.ConnectSocket.RemoteEndPoint + "]"); //System.Diagnostics.Debug.WriteLine(UserID + " Logged in at " + DateTime.Now.ToString("HH:mm:ss:fff")); Done = true; } } } return Done; }
/// <summary> /// 轉發群命令 /// </summary> /// <param name="DataTerminal"></param> /// <param name="DataGroup"></param> /// <param name="DataBody"></param> /// <param name="DataTerminalName"></param> /// <param name="conn"></param> /// <param name="MsgGUID"></param> /// <param name="MsgNO"></param> protected void SendGroupCommand(string DataTerminal, string DataGroup, string DataBody, string DataTerminalName, SQLHelper conn, string MsgGUID, string MsgNO) { DataTable UserList = conn.OpenDataTable("select a.cmd_to from " + "\r\n" + "( " + "\r\n" + " select b.td_owner as cmd_to " + "\r\n" + " from lrtdcmd a(nolock) " + "\r\n" + " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" + " where a.cmd_guid ='" + MsgGUID + "' and a.td_no = '" + DataGroup + "' " + "\r\n" + " and b.td_owner <> '" + DataTerminal + "' " + "\r\n" + " union " + "\r\n" + " select b.td_member as cmd_to " + "\r\n" + " from lrtdcmd a(nolock) " + "\r\n" + " join lrtd00d1 b(nolock) on a.td_no = b.td_no " + "\r\n" + " join lrtd00h c (nolock) on b.td_no=c.td_no " + "\r\n" + " where a.cmd_guid ='" + MsgGUID + "' and a.td_no = '" + DataGroup + "' " + "\r\n" + " and b.td_member <> '" + DataTerminal + "' and b.td_member <> c.td_owner " + "\r\n" + ") a", CommandType.Text); if (UserList.Rows.Count > 0) { // 系統將訊息轉發給群成員(以發送者的帳號) //問題: 1.只有帳號沒有姓名.Client無法顯示姓名 // 2.沒有發送日期和時間 // PC 和 Mobile 要用到的信息.提前打包好,沒必要放到for循環里去做 List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(DataBody, "C", DataTerminal, DataTerminalName, DataGroup, MsgGUID, MsgNO); string Users = ""; foreach (DataRow User in UserList.Rows) { #region " PC Client " PushMessage(Users, msgs, User["cmd_to"].ToString(), m_asyncSocketServer.PcUserTokenList, "SendGroupCommand"); #endregion #region " Web Client (不發)" ////userSkt = _ws_transmit_tb[User["msg_to"].ToString()] as Socket; //recUser = _ws_transmit_tb[User["cmd_to"].ToString()] as UserSocket; //if (recUser != null) //{ // userSkt = recUser.Socket; // // 系統將訊息轉發給群成員(以發送者的帳號) // //問題: 1.只有帳號沒有姓名.Client無法顯示姓名 // // 2.沒有發送日期和時間 // byte[] msg = WebConvertMethod.ConvertMsgToByte(DataBody, "C", DataTerminal, DataTerminalName, DataGroup, MsgNO); // try // { // //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息 // lock (userSkt) // { // userSkt.Send(msg); // //SendData(userSkt, msg); // } // //Users += "'" + User["cmd_to"].ToString() + "',"; // } // catch (SocketException ex) // { // //掉線了,直接干掉. // try // { // //clsClientLog.WriteLog(clsClientLog.LogType.Error, "WebThreadFunc(object obj):catch (SocketException)", "轉發群消息給用戶:" + User["msg_to"].ToString() + "時發生異常,斷開連接."); // LogHelper.WriteLog("SendGroupCommand", "轉發群命令給手機用戶:" + User["cmd_to"].ToString() + "時發生異常,斷開連接." + (ex != null ? ex.Message : "")); // try // { // if (userSkt.Connected) // { // userSkt.Shutdown(SocketShutdown.Both); // //userSkt.Disconnect(false); // } // userSkt.Close(); // #region " 聯絡人狀態變更 " // //通知在線的聯絡人當前用戶改變了狀態 // //recUser.SetUserState("6"); // //UpdateUserLog(recUser.ID, "1", "1", "Mobile端異常登出"); // //如果PC端沒在線就通知聯絡人此人離線了 // ProcessWebUserState(recUser); // //UserSocket PCUser = _transmit_tb[recUser.ID] as UserSocket; // //if (PCUser == null) // //{ // // SendUserState(recUser); // // UpdateUserState(recUser.ID, "6"); //離線 // //} // #endregion // lock (_ws_transmit_tb.SyncRoot) // _ws_transmit_tb.Remove(User["cmd_to"].ToString()); // Thread userThread = _ws_thread_tb[User["cmd_to"].ToString()] as Thread; // userThread.Abort(); // //線程池不會被遍歷,不鎖. // _ws_thread_tb.Remove(User["cmd_to"].ToString()); // } // catch // { } // } // catch // { } // } //} #endregion #region " Mobile Client " PushMessage(Users, msgs, User["cmd_to"].ToString(), m_asyncSocketServer.MobileUserTokenList, "SendGroupCommand"); #endregion } } UserList.Dispose(); UserList = null; //處理同一帳號多個端登錄的轉發. if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.PC || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自PC端消息. //檢查相同帳號Mobile(Web)端有沒有登錄,有就轉發一次. #region " Mobile Client " PushRoaming(DataTerminal, ParseProtocol.ConvertMsgToByte(DataBody, "C", DataTerminal, "", DataGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.MobileUserTokenList); #endregion } if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.Mobile || m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP) { //來自Mobile(Web)端消息. //檢查相同帳號Web端有沒有登錄,有就轉發一次. #region " PC Client " PushRoaming(DataTerminal, ParseProtocol.ConvertMsgToByte(DataBody, "C", DataTerminal, "", DataGroup, MsgGUID, MsgNO, "R"), m_asyncSocketServer.PcUserTokenList); #endregion } }
private DataTable GetUnreadMsgs(SQLHelper conn, string UserID, string Source) { string SQL = ""; switch (Source) { case "M": //消息 SQL = " select b.td_no,b.msg_date,b.msg_time,b.msg_text,b.msg_img,b.msg_no, " + "\r\n" + " b.msg_from,isnull(c.user_name,'') as user_name,b.msg_guid,b.msg_doctype,b.msg_type " + " from lrmsgstate a (nolock) " + "\r\n" + " join lrtdmsg b (nolock) on a.msg_no=b.msg_no " + "\r\n" + " left join lrtduser c (nolock) on b.msg_from=c.user_no " + "\r\n" + " where a.msg_to='" + UserID + "' and a.msg_state='0' " + "\r\n" + " order by b.td_no,b.msg_no"; break; //case "C": // //命令 // SQL = // " select b.td_no,b.cmd_date,b.cmd_time,b.cmd_text,b.cmd_no, " + "\r\n" + // " b.cmd_user,isnull(c.user_name,'') as user_name,b.cmd_guid " + // " from lrcmdstate a (nolock) " + "\r\n" + // " join lrtdcmd b (nolock) on a.cmd_no=b.cmd_no " + "\r\n" + // " left join lrtduser c (nolock) on b.cmd_user=c.user_no " + "\r\n" + // " where a.cmd_to='" + UserID + "' and a.cmd_state='0' " + "\r\n" + // " order by b.td_no,b.cmd_no"; // break; case "S": //系統消息 SQL = " select b.sys_date,b.sys_time,b.sys_type,b.sys_objtype,b.sys_remk,b.sys_id,b.sys_user,b.sys_objid " + " from lrsysmsgstate a (nolock) " + "\r\n" + " join lrsysmsg b (nolock) on a.sys_id=b.sys_id " + "\r\n" + " where a.sysmsg_to='" + UserID + "' and a.sysmsg_state='0' " + "\r\n" + " order by b.sys_id"; break; } return conn.OpenDataTable(SQL, CommandType.Text); }
/// <summary> /// 保存登錄流量和耗時數據 /// </summary> /// <param name="userToken"></param> /// <param name="DataTerminal"></param> /// <param name="DataBody"></param> protected void SaveLoginStatData(SocketUserToken userToken, string DataTerminal, string DataBody) { //DataBody包體格式: //類別A;流量(Byte);耗時(ms);開始(當地)日期(yyyy/MM/dd);開始(當地)時間(HH:mm:ss.fff);結束(當地)日期(yyyy/MM/dd);結束(當地)時間(HH:mm:ss.fff)|類別B;流量(Byte);耗時(ms);開始(當地)日期(yyyy/MM/dd);開始(當地)時間(HH:mm:ss.fff);結束(當地)日期(yyyy/MM/dd);結束(當地)時間(HH:mm:ss.fff) //邏輯: //根據帳號+IP抓到lrlxlog里此用戶最大UID作業log_uid string[] StatData = DataBody.Split('|'); if (StatData.Length > 0) { DateTime curDate = DateTime.Now; string Today = curDate.ToString("yyyy/MM/dd"); string Yesterday = curDate.AddDays(-1).ToString("yyyy/MM/dd"); //防止登錄時剛好跨天 using (SQLHelper conn = new SQLHelper(m_asyncSocketServer.DBInfo)) { conn.OpenConnection(); try { conn._Transaction = conn._Connection.BeginTransaction(); DataTable recTmp = conn.OpenDataTable("select MAX(uid) as log_uid from lrlxlog (nolock) " + "\r\n" + "where log_date between '" + Yesterday + "' and '" + Today + "' and user_no='" + DataTerminal + "' and log_ip='" + ((IPEndPoint)userToken.ConnectSocket.RemoteEndPoint).Address.ToString() + "'", CommandType.Text); if (recTmp.Rows.Count > 0) { long log_uid = long.Parse(recTmp.Rows[0]["log_uid"].ToString()); string[] statValues; foreach (string stat in StatData) { statValues = stat.Split(';'); //[0]:類別 //[1]:流量(Byte) //[2]:耗時(ms) //[3]:開始(當地)日期(yyyy/MM/dd) //[4]:開始(當地)時間(HH:mm:ss.fff) //[5]:結束(當地)日期(yyyy/MM/dd) //[6]:結束(當地)時間(HH:mm:ss.fff) if (statValues.Length == 7) { conn.ExecuteSQL("insert into lrlxstat (log_uid,stat_type,data_size,duration_time,stat_sdate,stat_stime,stat_edate,stat_etime,ie_ymd,ie_time,ie_user)" + "\r\n" + "values(" + log_uid + ",'" + statValues[0] + "'," + statValues[1] + "," + statValues[2] + ",'" + statValues[3] + "','" + statValues[4] + "','" + statValues[5] + "','" + statValues[6] + "','" + curDate.ToString("yyyy/MM/dd") + "','" + curDate.ToString("HH:mm:ss") + "','" + DataTerminal + "')"); } } } conn._Transaction.Commit(); conn.CloseConnection(); } catch { conn._Transaction.Rollback(); conn.CloseConnection(); } } } }
public static string[] ProcessRequest(object[] requestData, string SrvNO, string DBAlias) { //20條數據分一頁,防止單條消息超過4000byte int ROWS_PER_PAGE = 20; //返回數組 0=type ; 1=終端; 2=群號; 3=時間; 4=消息ID; 5=消息內容 6=終端姓名 string result = "無效命令." + Environment.NewLine + "請使用命令: ? 查詢本訂閱號提供的服務."; //訂閱號業務處理分類處理 //訂閱號,命令Code,操作腳本 //switch (GroupID) //{ // case "888": // //在這里加入解析命令并處理 // //Do something // result = "SubSrv #1:Hello"; // break; // case "D151100318": // //在這里加入解析命令并處理 // //Do something // result = "SubSrv #2:Hello"; // break; //} if (requestData[5].ToString().Trim() == "?" || requestData[5].ToString().Trim() == "?") { #region " ?命令:返回訂閱號的命令清單 " using (SQLHelper conn = new SQLHelper(DBAlias)) { conn.OpenConnection(); try { DataTable Rec = conn.OpenDataTable("select cmd_code,cmd_remk,cmd_format from lxsubcmds (nolock) where srv_no='" + SrvNO + "' and cmd_visible='Y' order by cmd_seq", CommandType.Text); conn.CloseConnection(); if (Rec.Rows.Count > 0) { //<table> // <caption>訂閱號命令清單</caption> // <thead> // <tr> // <th>命令代碼</th> // <th>命令說明</th> // <th>命令格式</th> // </tr> // </thead> // <tbody> // <tr> // <td>FILE</td> // <td>列出EIS報表代號及名稱清單</td> // <td>FILE</td> // </tr> // <tr> // <td>EIS</td> // <td>取得EIS系統資料</td> // <td>EIS EIC-ID-003 A169 2016/06</td> // </tr> // </tbody> //</table> string h5_Table_Format = "<table>" + "\r\n" + " <caption>訂閱號命令清單{0}</caption>" + "\r\n" + " <thead>" + "\r\n" + " <tr>" + "\r\n" + " <th style=\"width:80px;\">命令代碼</th>" + "\r\n" + " <th style=\"width:auto;\">命令說明</th>" + "\r\n" + " <th style=\"width:auto;\">命令格式</th>" + "\r\n" + " </tr>" + "\r\n" + " </thead>" + "\r\n" + " <tbody>" + "\r\n" + " {1}" + "\r\n" + " </tbody>" + "\r\n" + "</table>"; string h5_tBody_Format = "<tr>" + "\r\n" + " {0}" + "\r\n" + "</tr>"; result = ""; //string h5_Head_Value = ""; List<string> CmdRows = new List<string>(); for (int i = 0; i < Rec.Rows.Count; i++) { //result += Rec.Rows[i]["cmd_code"].ToString() + " : " + Rec.Rows[i]["cmd_remk"].ToString(); //h5_Head_Value += string.Format(h5_tBody_Format, "<td>" + Rec.Rows[i]["cmd_code"].ToString() + "</td>" + // "<td>" + Rec.Rows[i]["cmd_remk"].ToString() + "</td>" + // "<td>" + Rec.Rows[i]["cmd_format"].ToString() + "</td>") + Environment.NewLine; //if (i + 1 < Rec.Rows.Count) // result += Environment.NewLine; CmdRows.Add(string.Format(h5_tBody_Format, "<td>" + Rec.Rows[i]["cmd_code"].ToString() + "</td>" + "<td>" + Rec.Rows[i]["cmd_remk"].ToString() + "</td>" + "<td>" + Rec.Rows[i]["cmd_format"].ToString() + "</td>")); } //得到消息條數 string[] Results = new string[CmdRows.Count / ROWS_PER_PAGE + 1]; int k = 0; for (int i = 0; i < CmdRows.Count; i++) { if (i % ROWS_PER_PAGE == 0 && i > 0) { //達到分頁數,產生一條消息. Results[k] = string.Format(h5_Table_Format, "(" + (k+1).ToString() + ")", result); k++; result = ""; } result += CmdRows[i] + Environment.NewLine; } if (result.Length > 0 && k < Results.Length) { //最後一筆 Results[k] = string.Format(h5_Table_Format, "(" + (k + 1).ToString() + ")", result); } CmdRows.Clear(); CmdRows = null; //result = "本訂閱號命令如下:" + Environment.NewLine + result; //result = string.Format(h5_Table_Format, h5_Head_Value); //直接返回 return Results; } else { result = "本訂閱號暫無命令可使用."; } } catch (Exception ex) { LogHelper.WriteLog("ProcessRequest()", "處理?命令時:" + (ex != null ? ex.Message : "")); conn.CloseConnection(); } } #endregion } else if (requestData[5].ToString().Trim().IndexOf("<Data Name=") > -1 && requestData[5].ToString().Trim().IndexOf("</Data>") > -1) { //格式化輸入不響應 result = ""; } else { #region " 其他命令 " //select cmd_operation,cmd_paras from lxsubcmds (nolock) where srv_no='3737' and cmd_code='PO' using (SQLHelper conn = new SQLHelper(DBAlias)) { conn.OpenConnection(); try { //空格分隔 string[] tmp = requestData[5].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); //命令 string CMD = tmp[0].Trim().ToUpper(); //規則: //1.用命令得到訂閱號表名及欄位. DataTable Rec = conn.OpenDataTable("select cmd_operation,cmd_paras,srv_table,filter_key,order_key,top_num,template_no,match_type,multi_value,cmd_format,rows_paging from lxsubcmds (nolock) where srv_no='" + SrvNO + "' and cmd_code='" + CMD + "'", CommandType.Text); if (Rec.Rows.Count > 0) { //得到分頁靈敏 ROWS_PER_PAGE = int.Parse(Rec.Rows[0]["rows_paging"].ToString()); if (ROWS_PER_PAGE <= 0) ROWS_PER_PAGE = 20; //2.得到權限分組代號 DataTable subRec = conn.OpenDataTable("select permission_group from lxpermissions (nolock) where srv_no='" + SrvNO + "' and lx_account='" + requestData[1].ToString().Trim() + "'", CommandType.Text); if (subRec.Rows.Count > 0) { string andWhere = "", OrderBy = ""; //1=等於;2=以..開始 string MathType = Rec.Rows[0]["match_type"].ToString(); string SrvTable = Rec.Rows[0]["srv_table"].ToString(); string CMDFormat = Rec.Rows[0]["cmd_format"].ToString(); string PermissionGroup = ""; for (int i = 0; i < subRec.Rows.Count; i++) { PermissionGroup += "'" + subRec.Rows[i]["permission_group"].ToString() + "'"; if (i + 1 < subRec.Rows.Count) PermissionGroup += ","; } //3.根據表名得到參數名 subRec = conn.OpenDataTable("select tb_colno,tb_colna,tb_coltype from lxsubtemplates (nolock) where srv_no='" + SrvNO + "' and srv_table='" + SrvTable + "' and template_no='" + Rec.Rows[0]["template_no"].ToString() + "' order by paras_order", CommandType.Text); if (subRec.Rows.Count > 0) { //4.得到訂閱號表數據(lxsub01,lxsub02...) (根據filter_key,order_key,top_num抓數據) result = GetFilterAndOrder(tmp, CMD, Rec, ref andWhere, ref OrderBy, MathType); if (result.Length == 0) { DataTable dataRec = conn.OpenDataTable("select " + Rec.Rows[0]["top_num"].ToString() + " * from " + SrvTable + " (nolock) where srv_no='" + SrvNO + "' and group_no in (" + PermissionGroup + ")" + andWhere + OrderBy, CommandType.Text); if (dataRec.Rows.Count > 1) { #region " 明細 " string h5_Table_Format = "<table>" + "\r\n" + " <caption>{0}{1}</caption>" + "\r\n" + " <thead>" + "\r\n" + " <tr>" + "\r\n" + " {2}" + "\r\n" + " </tr>" + "\r\n" + " </thead>" + "\r\n" + " <tbody>" + "\r\n" + " {3}" + "\r\n" + " </tbody>" + "\r\n" + "</table>"; string h5_tBody_Format = "<tr>" + "\r\n" + " {0}" + "\r\n" + "</tr>"; //得到cmd_operation,并把轉換為橫多行模式 //<table> // <caption>項目回報表</caption> // <thead> // <tr> // <th>序號</th> // <th>工作人員</th> // <th>項目編號</th> // <th>項目名稱</th> // <th>立案日期</th> // <th>備注</th> // </tr> // </thead> // <tbody> // <tr> // <td>0001</td> // <td>D003842</td> // <td>T0000001</td> // <td>樂行樂信底層開發</td> // <td>2016/01/01</td> // <td>進行中</td> // </tr> // <tr> // <td>0002</td> // <td>D003842</td> // <td>T0000002</td> // <td>樂行樂信第三方應用開發</td> // <td>2016/01/01</td> // <td>進行中</td> // </tr> // </tbody> //</table> string h5_Table_Caption = ""; //{0} string h5_Head_Value = ""; //{1} result = ""; List<string> CmdRows = new List<string>(); //得到顯示列表樣式 DataTable ListFormat = XmlStrToDataTable(subRec, Rec.Rows[0]["cmd_operation"].ToString()); decimal width = 0; if (ListFormat != null && ListFormat.Rows.Count > 0) { h5_Table_Caption = HtmlConvertor(ListFormat.ExtendedProperties["Caption"].ToString()); //<th>序號</th> foreach (DataRow dr in ListFormat.Rows) { try { width = decimal.Parse(dr["tb_colwidth"].ToString()); } catch { width = 0; } if (width <= 0) width = 80; h5_Head_Value += "<th style=\"width:" + width.ToString() + "px;\">" + dr["tb_colna"].ToString() + "</th>"; } //開始填表身數據 string colno = ""; //string h5_Body_Value = ""; //{0} string h5_Body_Value_tmp = ""; foreach (DataRow row in dataRec.Rows) { h5_Body_Value_tmp = ""; for (int i = 0; i < subRec.Rows.Count; i++) { colno = subRec.Rows[i]["tb_colno"].ToString(); if (subRec.Rows[i]["tb_coltype"].ToString() == "1") { //FTP文件 h5_Body_Value_tmp += "<td>" + "ftp://lx.lorom.net.cn/ProductAttatchments/ToDoList/" + SrvNO + "/" + row[colno].ToString() + "</td>"; } else { h5_Body_Value_tmp += "<td>" + HtmlConvertor(row[colno].ToString()) + "</td>"; } } CmdRows.Add(string.Format(h5_tBody_Format, h5_Body_Value_tmp)); //h5_Body_Value += string.Format(h5_tBody_Format, h5_Body_Value_tmp) + "\r\n"; } //得到消息條數 string[] Results = new string[(CmdRows.Count % ROWS_PER_PAGE == 0) ? (CmdRows.Count / ROWS_PER_PAGE) : (CmdRows.Count / ROWS_PER_PAGE + 1)]; int k = 0; for (int i = 0; i < CmdRows.Count; i++) { if (i % ROWS_PER_PAGE == 0 && i > 0) { //達到分頁數,產生一條消息. Results[k] = string.Format(h5_Table_Format, h5_Table_Caption, "(" + (k + 1).ToString() + ")", h5_Head_Value, result); k++; result = ""; } result += CmdRows[i] + Environment.NewLine; } if (result.Length > 0 && k < Results.Length) { //最後一筆 Results[k] = string.Format(h5_Table_Format, h5_Table_Caption, "(" + (k + 1).ToString() + ")", h5_Head_Value, result); } CmdRows.Clear(); CmdRows = null; //組成最後的列表 //result = string.Format(h5_Table_Format, h5_Table_Caption, // h5_Head_Value, // h5_Body_Value); //直接返回一條或多條. return Results; } else { LogHelper.WriteLog("ProcessRequest()", "嘗試命令:" + CMD + "的cmd_operation參數設置錯誤."); result = "對不起,當前命令暫時無法使用."; } #endregion } else if (dataRec.Rows.Count == 1) { #region " 單筆 " string[] paras = new string[subRec.Rows.Count]; string colno = ""; for (int i = 0; i < subRec.Rows.Count; i++) { colno = subRec.Rows[i]["tb_colno"].ToString(); if (subRec.Rows[i]["tb_coltype"].ToString() == "1") { //FTP文件 paras[i] = "ftp://lx.lorom.net.cn/ProductAttatchments/ToDoList/" + SrvNO + "/" + dataRec.Rows[0][colno].ToString(); } else { paras[i] = HtmlConvertor(dataRec.Rows[0][colno].ToString()); } } result = string.Format(Rec.Rows[0]["cmd_operation"].ToString(), paras); #endregion } else { result = "對不起,暫時無法取得任何數據."; } } else { //命令格式不正確 result += CMDFormat; return new string[] { result }; } } else { LogHelper.WriteLog("ProcessRequest()", "命令:" + CMD + "的cmd_operation參數設置錯誤."); result = "對不起,當前命令暫時無法使用."; } #region " 舊邏輯 " //if (Rec.Rows[0]["multi_value"].ToString() == "Y" && subRec.Rows.Count > 0) //{ // #region " 明細類數據 " // //4.得到訂閱號表數據(lxsub01,lxsub02...) (根據filter_key,order_key,top_num抓數據) // result = GetFilterAndOrder(tmp, CMD, Rec, ref andWhere, ref OrderBy, MathType); // if (result.Length > 0) // { // //命令格式不正確 // result += CMDFormat; // return result; // } // DataTable dataRec = conn.OpenDataTable("select * from " + SrvTable + " (nolock) where srv_no='" + SrvNO + "' and group_no in (" + PermissionGroup + ")" + andWhere + OrderBy, CommandType.Text); // if (dataRec.Rows.Count > 0) // { // string colno = "", colna = ""; // result = ""; // foreach (DataRow row in dataRec.Rows) // { // for (int i = 0; i < subRec.Rows.Count; i++) // { // colno = subRec.Rows[i]["tb_colno"].ToString(); // colna = subRec.Rows[i]["tb_colna"].ToString(); // if (subRec.Rows[i]["tb_coltype"].ToString() == "1") // { // //FTP文件 // result += colna + "ftp://lx.lorom.net.cn/ProductAttatchments/ToDoList/" + SrvNO + "/" + row[colno].ToString(); // } // else // { // result += colna + row[colno].ToString(); // } // if (i + 1 < subRec.Rows.Count) // { // result += " "; // } // } // result += Environment.NewLine; // } // result = string.Format(Rec.Rows[0]["cmd_operation"].ToString(), result); // } // else // { // result = "對不起,暫時無法取得任何數據."; // } // #endregion //} //else //{ // #region " 單筆數據 " // if (subRec.Rows.Count == float.Parse(Rec.Rows[0]["cmd_paras"].ToString()) && subRec.Rows.Count > 0) // { // //參數設置正確 // //4.得到訂閱號表數據(lxsub01,lxsub02...) (根據filter_key,order_key,top_num抓數據) // result = GetFilterAndOrder(tmp, CMD, Rec, ref andWhere, ref OrderBy, MathType); // if (result.Length > 0) // { // //命令格式不正確 // result += CMDFormat; // return result; // } // DataTable dataRec = conn.OpenDataTable("select " + Rec.Rows[0]["top_num"].ToString() + " * from " + SrvTable + " (nolock) where srv_no='" + SrvNO + "' and group_no in (" + PermissionGroup + ")" + andWhere + OrderBy, CommandType.Text); // if (dataRec.Rows.Count > 0) // { // string[] paras = new string[subRec.Rows.Count]; // string colno = ""; // for (int i = 0; i < subRec.Rows.Count; i++) // { // colno = subRec.Rows[i]["tb_colno"].ToString(); // if (subRec.Rows[i]["tb_coltype"].ToString() == "1") // { // //FTP文件 // paras[i] = "ftp://lx.lorom.net.cn/ProductAttatchments/ToDoList/" + SrvNO + "/" + dataRec.Rows[0][colno].ToString(); // } // else // { // paras[i] = dataRec.Rows[0][colno].ToString(); // } // } // result = string.Format(Rec.Rows[0]["cmd_operation"].ToString(), paras); // } // else // { // result = "對不起,暫時無法取得任何數據."; // } // } // else // { // LogHelper.WriteLog("ProcessRequest()", "命令:" + CMD + "的cmd_operation參數設置錯誤."); // result = "對不起,當前命令暫時無法使用."; // } // #endregion //} #endregion } else { result = "對不起,當前帳號無權限使用此命令."; } } conn.CloseConnection(); } catch (Exception ex) { result = "訂閱號服務出現異常,請稍後再試."; LogHelper.WriteLog("ProcessRequest()", "處理: " + requestData[5].ToString() + " 命令時:" + (ex != null ? ex.Message : "")); conn.CloseConnection(); } } #endregion } return new string[] { result }; }
public DataTable GetTemplateList(string user_id) { DataTable TplData = new DataTable("tplData"); try { using (SQLHelper conn = new SQLHelper()) { if (conn.OpenConnection()) { string SQL = "select * from " + "\r\n" + "( " + "\r\n" + " select distinct tpl_no, tpl_name, case when tpl_owner = 'System' then '' else tpl_name end as orderkey " + "\r\n" + " from lrtdtpl (nolock) " + "\r\n" + " where tpl_type='1' and (tpl_owner ='System' or tpl_owner ='" + user_id + "') " + "\r\n" + ") a " + "\r\n" + "order by a.orderkey"; TplData = conn.OpenDataTable(SQL, CommandType.Text); } conn.CloseConnection(); } //返回Client return TplData; } catch { return TplData; } }