/// <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;
}
}