private void InsertMsgs(string User, DateTime now, DataTable Msgs, SQLHelper conn)
{
string SQL = "";
string ymd = now.ToString("yyyy/MM/dd");
string time = now.ToString("HH:mm:ss");
try
{
foreach (DataRow dr in Msgs.Rows)
{
string maxID = GetMaxMsgID(conn, dr[0].ToString());
int curNumber = int.Parse(maxID) + 1;
maxID = curNumber.ToString().PadLeft(5, '0');
string curTDID = GetMatchString("M") + maxID;
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) " +
"values('" + ymd + "','" + time + "','" + User + "','" + dr[0].ToString() + "','" + User + "','','" + ymd + "','" + time + "',N'" + dr[1].ToString() + "','" + curTDID + "','0')";
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_no ='" + curTDID + "' and b.td_no = '" + dr[0].ToString() + "'" +
" 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_no ='" + curTDID + "' and b.td_no = '" + dr[0].ToString() + "'" +
" and b.td_member <> '" + User + "'";
conn.ExecuteSQL(SQL);
}
}
catch (System.Exception ex)
{
throw ex;
}
}
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);
}
//2016/03/03
/// <summary>
/// 更新數據類群信息
/// </summary>
private void UpdateVDataThreadFunc()
{
//Test
//VDataQueue.Enqueue("iemis.UpdateVData 'D151000001',N'這是一條測試數據,这是一条测试数据555.','2016/06/03','10:20:30'");
bool TryOpenConn = false;
int TryCount = 0;
SQLHelper conn = new SQLHelper(DBInfo);
try
{
conn.OpenConnection();
while (true)
{
Thread.Sleep(1000);
try
{
//當前設備數據更新隊列
ServerStatistics.CurentDeviceData_Queue = VDataQueue.Count;
//最高設備數據更新隊列
if (ServerStatistics.MaxDeviceData_Queue < ServerStatistics.CurentDeviceData_Queue)
ServerStatistics.MaxDeviceData_Queue = ServerStatistics.CurentDeviceData_Queue;
if (TryOpenConn)
{
try
{
conn.OpenConnection();
TryOpenConn = false;
//成功连接
LogHelper.WriteLog("UpdateVDataThreadFunc()", "重新连接数据库成功.");
TryCount = 0;
}
catch
{
//尝试重新连接超过180次(3分钟内不恢复)
TryCount++;
}
if (TryCount > 180)
{
LogHelper.WriteLog("UpdateVDataThreadFunc()", "尝试重新连接数据库次数达到" + TryCount.ToString() + "次仍然无法成功,更新作业被迫中止.请检查数据库状态和网络连接.");
break;
}
}
while (VDataQueue.Count > 0)
{
conn.ExecuteSQL(VDataQueue.Dequeue().ToString());
}
}
catch (Exception ex)
{
//发生异常有可能是数据库连接断开.标记下一次执行时尝试重新连接
TryOpenConn = true;
LogHelper.WriteLog("UpdateVDataThreadFunc()", "更新數據類群信息時:" + (ex != null ? ex.Message : ""));
}
}
}
catch (Exception ex)
{
LogHelper.WriteLog("UpdateVDataThreadFunc()", "更新數據類群信息時:" + (ex != null ? ex.Message : ""));
}
finally
{
if (conn != null)
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="UserID"></param>
/// <param name="conn"></param>
/// <param name="State"></param>
private void UpdateUsersState(string State)
{
using (SQLHelper conn = new SQLHelper(DBInfo))
{
conn.OpenConnection();
try
{
conn.ExecuteSQL("update a set a.user_state='" + State + "' from lrtduser a");
conn.CloseConnection();
}
catch
{
conn.CloseConnection();
}
}
}
/// <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;
}
}
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;
}
}
private void btnStop_Click(object sender, EventArgs e)
{
try
{
ListViewItem lvi = listSubSrvs.SelectedItems[0];
if (SubServices[lvi.SubItems[0].Text] != null)
{
ClientSocket subSrv = SubServices[lvi.SubItems[0].Text] as ClientSocket;
if (subSrv != null && subSrv.Connected)
{
try
{
subSrv.Send("SubSrv logged out", "1", subSrv.GroupID, "mac", "SUB", Guid.NewGuid().ToString().ToString().Replace("-", "").ToUpper());
subSrv.Disconnect();
subSrv.ReciveMsg -= subSrv_ReciveMsg;
subSrv.Dispose();
}
catch
{ }
}
using (SQLHelper conn = new SQLHelper(DBAlias))
{
conn.OpenConnection();
try
{
// 標記為已讀
DateTime now = DateTime.Now;
string ymd = now.ToString("yyyy/MM/dd");
string time = now.ToString("HH:mm:ss");
conn.ExecuteSQL("update a set a.srv_state='0',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lxsubsrvs a where srv_device = '" + Device + "' and srv_no ='" + lvi.SubItems[0].Text + "'");
conn.CloseConnection();
}
catch
{
conn.CloseConnection();
}
}
SubServices.Remove(lvi.SubItems[0].Text);
//刷新列表
btnRefresh_Click(sender, e);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void MainUI_FormClosed(object sender, FormClosedEventArgs e)
{
try
{
lock (SubServices.SyncRoot)
{
foreach (DictionaryEntry de in SubServices)
{
ClientSocket subSrv = de.Value as ClientSocket;
if (subSrv != null && subSrv.Connected)
{
try
{
subSrv.Send("SubSrv logged out", "1", subSrv.GroupID, "mac", "SUB", Guid.NewGuid().ToString().ToString().Replace("-", "").ToUpper());
subSrv.Disconnect();
subSrv.ReciveMsg -= subSrv_ReciveMsg;
subSrv.Dispose();
}
catch
{ }
}
}
}
using (SQLHelper conn = new SQLHelper(DBAlias))
{
conn.OpenConnection();
try
{
// 標記為已讀
DateTime now = DateTime.Now;
string ymd = now.ToString("yyyy/MM/dd");
string time = now.ToString("HH:mm:ss");
conn.ExecuteSQL("update a set a.srv_state='0',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lxsubsrvs a where srv_device = '" + Device + "' and srv_state = '1'");
conn.CloseConnection();
}
catch
{
conn.CloseConnection();
}
}
SubServices.Clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//設置執行人時 發郵件
private void SendMsgAndEmail(SQLHelper conn, string tmp_LRTD00H, bool mailTrue, bool msgTrue, string userid)
{
//更改了執行人 發郵件/短信通知
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
if (mailTrue)
{
string SQL =
" select '" + ymd + "','" + time + "','" + userid + "',isnull(d.user_email,c.td_member) 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) + '[' + a.td_name + ']' lr_subject," + "\r\n" +
" 'Dear ' + isnull(d.user_name,b.user_name) + ':<br> 您有一項來自:' + isnull(b.user_name,a.td_owner) + ' 的[' + a.td_name + ']待辦事項.<br> 內容如下:" + "\r\n" +
"' + a.td_description,'2','1' " + "\r\n" +
" from lrtd00h a(nolock) " + "\r\n" +
" left join lrtduser b(nolock) on a.td_owner = b.user_no " + "\r\n" +
" left join lrtd00d1 c(nolock) on a.td_no = c.td_no and c.td_role = '2' " + "\r\n" +
" left join lrtduser d(nolock) on c.td_member = d.user_no " +
" join " + tmp_LRTD00H + " e(nolock) on a.td_no = e.td_no " +
" where e.send_msg = 'Y'";
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);
//if (mailTrue)
//{
// foreach (DataRow dr in emails.Rows)
// {
// string subject = " [待辦事項通知] 來自:" + dr["ownername"].ToString() + " [" + dr["td_name"].ToString() + "]";
// string content =
// "Dear " + dr["executor_name"].ToString() + ":" + "<br>" +
// "您有一項來自:" + dr["ownername"].ToString() + " 的[" + dr["td_name"].ToString() + "]待辦事項." + "<br>" +
// "內容如下:" + "\r\n" + dr["td_description"].ToString();
// SendEmail(dr["toAddr"].ToString(), dr["ccAddr"].ToString(), content, subject, "");
// }
//}
if (msgTrue)
{
string SQL =
" select '" + ymd + "','" + time + "','" + userid + "','' toAddr,isnull((select ss.user_cellphone+';' " + "\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('')),'') phone,'' lr_subject,'來自:'+ isnull(b.user_name,a.td_owner) +' [待辦事項通知] \r\n 事項內容:'+a.td_name lr_content," + "\r\n" +
" '1','1' " + "\r\n" +
" from lrtd00h a(nolock) " + "\r\n" +
" left join lrtduser b(nolock) on a.td_owner = b.user_no " + "\r\n" +
" left join lrtd00d1 c(nolock) on a.td_no = c.td_no and c.td_role = '2' " + "\r\n" +
" left join lrtduser d(nolock) on c.td_member = d.user_no " +
" join " + tmp_LRTD00H + " e(nolock) on a.td_no = e.td_no " +
" where e.send_msg = 'Y'";
conn.ExecuteSQL("insert into lrtdmail(ie_ymd,ie_time,ie_user,lr_to,lr_cc,lr_subject,lr_content,lr_type,lr_ftype) " + SQL);
}
////'來自:'+ isnull(b.user_name,a.td_owner) +' [待辦事項通知] \r\n 事項內容:'+a.td_name
//if (msgTrue)
//{
// foreach (DataRow dr in emails.Rows)
// {
// string content = string.Format("來自:{0} [待辦事項通知] \r\n 事項內容:{1}", dr["ownername"].ToString(), dr["td_name"].ToString());
// sendMsg(dr["phone"].ToString().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries), content);
// }
//}
}
/// <summary>
/// 對附件的操作
/// </summary>
/// <param name="attachements"></param>
/// <param name="userid"></param>
public void UpdateToDoAttachMents(DataTable UpdateItems, DataTable attachements, string userid)
{
string SQL = "";
using (SQLHelper conn = new SQLHelper())
{
conn.OpenConnection();
try
{
string tmp_LRTD00H = "tmp_LRTD00H_" + 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 + "(td_no varchar(20) not null default '')");
connTemp.ExecuteSQL("create table " + tmp_LRTDFILE + "(" + conn.GetTableStruct("lrtdfile") + ")");
connTemp.CloseConnection();
tmp_LRTDFILE = "tempdb.." + tmp_LRTDFILE;
tmp_LRTD00H = "tempdb.." + tmp_LRTD00H;
foreach (DataRow dr in UpdateItems.Rows)
{
SQL = "insert into " + tmp_LRTD00H + "(td_no) values('" + 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._Transaction = conn._Connection.BeginTransaction();
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
#region 對附件的操作
SQL =
" delete a " +
" from lrtdfile a " +
" join lrtd00h b(nolock) on b.td_no = a.td_no " +
" where b.td_owner = '" + userid + "'" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=b.td_no)" +
" 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 對附件的操作
conn._Transaction.Commit();
conn.CloseConnection();
}
catch (System.Exception ex)
{
conn._Transaction.Rollback();
conn.CloseConnection();
WriteLog(userid, ex.Message, "ErrLog");
throw ex;
}
}
}
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;
}
}
}
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;
}
}
}
/// <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;
}
}
}
/// <summary>
/// 轉人到人音頻
/// </summary>
/// <param name="fromUserID"></param>
/// <param name="fromUserName"></param>
/// <param name="toUserID"></param>
/// <param name="conn"></param>
/// <param name="MsgGUID"></param>
/// <param name="MsgNO"></param>
private void SendP2PVoice(string fromUserID, string fromUserName, string toUserID, SQLHelper conn, string MsgGUID, string MsgNO, string VoiceSeconds)
{
string Users = "";
if (toUserID.Length > 0)
{
List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(VoiceSeconds, "R", fromUserID, fromUserName, "", MsgGUID, MsgNO);
#region " PC Client "
Users = PushMessage(Users, msgs, toUserID, m_asyncSocketServer.PcUserTokenList, "SendP2PVoice");
#endregion
#region " Mobile Client "
Users = PushMessage(Users, msgs, toUserID, m_asyncSocketServer.MobileUserTokenList, "SendP2PVoice");
#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 + ")");
}
}
//處理同一帳號多個端登錄的轉發.
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(fromUserID, ParseProtocol.ConvertMsgToByte(VoiceSeconds, "R", toUserID, "", "", 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(fromUserID, ParseProtocol.ConvertMsgToByte(VoiceSeconds, "R", toUserID, "", "", MsgGUID, MsgNO, "R"), m_asyncSocketServer.PcUserTokenList);
#endregion
}
}
private void btnStart_Click(object sender, EventArgs e)
{
try
{
string SrvNOs = "";
ListViewItem lvi = listSubSrvs.SelectedItems[0];
//foreach (ListViewItem lvi in listSubSrvs.Items)
//{
if (lvi.SubItems[1].Text == "0")
{
ClientSocket clientSocket = new ClientSocket(GetLX_IP(IP.Text), Convert.ToInt32(Port.Text));
clientSocket.Connect(true);
clientSocket.ReciveMsg -= subSrv_ReciveMsg;
clientSocket.ReciveMsg += subSrv_ReciveMsg;
clientSocket.GroupID = lvi.SubItems[0].Text;
clientSocket.Send("SubSrv logged in", "0", clientSocket.GroupID, "mac", "SUB", Guid.NewGuid().ToString().ToString().Replace("-", "").ToUpper());
if (SubServices.Contains(clientSocket.GroupID))
{
//斷開原來的連接
try
{
ClientSocket oldClientSocket = SubServices[clientSocket.GroupID] as ClientSocket;
oldClientSocket.Disconnect();
oldClientSocket.Dispose();
}
catch
{ }
SubServices.Remove(clientSocket.GroupID);
}
SubServices.Add(clientSocket.GroupID, clientSocket);
//記錄已開啟的訂閱呺
SrvNOs += "'" + lvi.SubItems[0].Text + "',";
}
//}
//更新訂閱號狀態
if (SrvNOs.Length > 0)
{
using (SQLHelper conn = new SQLHelper(DBAlias))
{
conn.OpenConnection();
try
{
SrvNOs = SrvNOs.Substring(0, SrvNOs.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.srv_state='1',a.ie_lymd='" + ymd + "',a.ie_ltime='" + time + "' from lxsubsrvs a where a.srv_no in (" + SrvNOs + ")");
conn.CloseConnection();
}
catch
{
conn.CloseConnection();
}
}
}
//刷新列表
btnRefresh_Click(sender, e);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <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
}
}
/// <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 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="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();
}
}
}
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="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();
}
}
}
}
/// <summary>
/// 更新日誌及用戶檔
/// </summary>
/// <param name="conn"></param>
/// <param name="UserID">用戶帳號</param>
/// <param name="logType">日誌類別:0=登錄;1=登退;2=改簽名;3=改頭像</param>
/// <param name="logDevice">設備</param>
/// <param name="logText">日誌描述或用戶簽名</param>
/// <param name="logIP">IP地址</param>
/// <param name="devName">設備名稱</param>
/// <param name="devMac">設備網卡Mac</param>
/// <param name="logAvatar">頭像數據</param>
private void DoUpdateUserLog(SQLHelper conn, string UserID, string logType, string logDevice, string logText, string logIP, string devName, string devMac, byte[] logAvatar)
{
try
{
DateTime LogDT = DateTime.Now;
//Added by Donne on 2016/06/04
//加入devName
//Added by Donne on 2016/06/07
//加入devMac
conn.ExecuteSQL("insert into lrlxlog (user_no,log_date,log_time,log_type,log_device,log_text,log_ip,dev_name,dev_mac) " + "\r\n" +
"values('" + UserID + "','" + LogDT.ToString("yyyy/MM/dd") + "','" + LogDT.ToString("HH:mm:ss") + "','" + logType + "','" + logDevice + "',N'" + logText.Replace("'", "''") + "','" + logIP + "',N'" + devName.Replace("'", "''") + "','" + devMac + "')");
if (logType == "2" || logType == "3")
{
if (logType == "2")
{
//改簽名
conn.ExecuteSQL("update a set a.user_msg=N'" + logText.Replace("'", "''") + "' from lrtduser a where user_no='" + UserID + "'");
}
else if (logType == "3")
{
//改頭像
conn.ExecuteSQLImage("update a set a.user_img=@img from lrtduser a where user_no='" + UserID + "'", "@img", logAvatar);
}
//更新聯絡人時間stamp
conn.ExecuteSQL("update a set a.log_time='" + LogDT.ToString("yyyyMMddHHmmssfff") + "' from lrcontact_log a join lrcontact b (nolock) on a.user_no=b.user_no and a.contact_id=b.contact_id where b.contact_id='" + UserID + "'");
conn.ExecuteSQL("insert into lrcontact_log (user_no,contact_id,log_time,log_do) " + "\r\n" +
"select a.user_no,a.contact_id,'" + LogDT.ToString("yyyyMMddHHmmssfff") + "','U' from lrcontact a (nolock) " + "\r\n" +
"where a.contact_id ='" + UserID + "' and not exists(select 1 from lrcontact_log b (nolock) where a.user_no=b.user_no and a.contact_id=b.contact_id)");
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <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();
}
}
}
private void DoUpdateUserState(SQLHelper conn, string UserID, string State)
{
try
{
conn.ExecuteSQL("update a set a.user_state='" + State + "' from lrtduser a where user_no='" + UserID + "'");
}
catch (Exception ex)
{
throw ex;
}
}
/// <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();
}
}
}
//2016/02/27
/// <summary>
/// 標記歷史消息為已讀
/// </summary>
private void MarkReadThreadFunc()
{
bool TryOpenConn = false;
int TryCount = 0;
SQLHelper conn = new SQLHelper(DBInfo);
try
{
conn.OpenConnection();
while (true)
{
Thread.Sleep(1000);
try
{
//當前歷史消息標記已讀隊列
ServerStatistics.CurentMarkRead_Queue = MarkReadHistoryMsgQueue.Count;
//最高歷史消息標記已讀隊列
if (ServerStatistics.MaxMarkRead_Queue < ServerStatistics.CurentMarkRead_Queue)
ServerStatistics.MaxMarkRead_Queue = ServerStatistics.CurentMarkRead_Queue;
if (TryOpenConn)
{
try
{
conn.OpenConnection();
TryOpenConn = false;
//成功连接
LogHelper.WriteLog("MarkReadThreadFunc()", "重新连接数据库成功.");
TryCount = 0;
}
catch
{
//尝试重新连接超过180次(3分钟内不恢复)
TryCount++;
}
if (TryCount > 180)
{
LogHelper.WriteLog("MarkReadThreadFunc()", "尝试重新连接数据库次数达到" + TryCount.ToString() + "次仍然无法成功,更新作业被迫中止.请检查数据库状态和网络连接.");
break;
}
}
while (MarkReadHistoryMsgQueue.Count > 0)
{
conn.ExecuteSQL(MarkReadHistoryMsgQueue.Dequeue().ToString());
}
}
catch (Exception ex)
{
//发生异常有可能是数据库连接断开.标记下一次执行时尝试重新连接
TryOpenConn = true;
LogHelper.WriteLog("MarkReadThreadFunc()", "標記歷史消息為已讀:" + (ex != null ? ex.Message : ""));
}
}
}
catch (Exception ex)
{
LogHelper.WriteLog("MarkReadThreadFunc()", "標記歷史消息為已讀:" + (ex != null ? ex.Message : ""));
}
finally
{
if (conn != null)
conn.CloseConnection();
}
}
/// <summary>
/// 轉人到人消息
/// </summary>
/// <param name="DeviceType">P=PC;M=Mobile(Web);A=第三方</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>
protected void SendP2PMessage(string fromUserID, string fromUserName, string toUserID, string DataBody, SQLHelper conn, string MsgGUID, string MsgNO, string DataType)
{
string Users = "";
if (toUserID.Length > 0)
{
List<byte[]> msgs = ParseProtocol.ConvertMsgToByte(DataBody, DataType, fromUserID, fromUserName, "", MsgGUID, MsgNO);
#region " PC Client "
Users = PushMessage(Users, msgs, toUserID, m_asyncSocketServer.PcUserTokenList, "SendP2PMessage");
#endregion
#region " Web Client (不向Web端發送) "
////userSkt = _ws_transmit_tb[toUserID] as Socket;
//recUser = _ws_transmit_tb[toUserID] as UserSocket;
//if (recUser != null)
//{
// userSkt = recUser.Socket;
// byte[] msg = WebConvertMethod.ConvertMsgToByte(DataBody, DataType, fromUserID, fromUserName, "", MsgNO);
// try
// {
// //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息
// lock (userSkt)
// {
// userSkt.Send(msg);
// //SendData(userSkt, msg);
// }
// send = true;//標記已經發送成功
// }
// catch (SocketException ex)
// {
// //掉線了,直接干掉.
// try
// {
// LogHelper.WriteLog("SendP2PMessage", "發消息給手機用戶:" + toUserID + "時發生異常,斷開連接." + (ex != null ? ex.Message : ""));
// //clsClientLog.WriteLog(clsClientLog.LogType.Error, "WebThreadFunc(object obj):catch (SocketException)", "發消息給用戶:" + toUserID + "時發生異常,斷開連接.");
// 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(toUserID);
// Thread userThread = _ws_thread_tb[toUserID] as Thread;
// userThread.Abort();
// //線程池不會被遍歷,不鎖.
// _ws_thread_tb.Remove(toUserID);
// }
// catch
// { }
// }
//}
#endregion
#region " Mobile Client "
Users = PushMessage(Users, msgs, toUserID, m_asyncSocketServer.MobileUserTokenList, "SendP2PMessage");
#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 + ")");
}
}
//處理同一帳號多個端登錄的轉發.
if (m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.PC ||
m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.AudioCall ||
m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP)
{
//來自PC端消息.
//檢查相同帳號Mobile(Web)端有沒有登錄,有就轉發一次.
#region " Web Client (不發) "
////userSkt = _ws_transmit_tb[fromUserID] as Socket;
//recUser = _ws_transmit_tb[fromUserID] as UserSocket;
//if (recUser != null)
//{
// userSkt = recUser.Socket;
// // 系統將訊息轉發給群成員(以發送者的帳號)
// //問題: 1.只有帳號沒有姓名.Client無法顯示姓名
// // 2.沒有發送日期和時間
// byte[] msg = WebConvertMethod.ConvertMsgToByte(DataBody, "A", fromUserID, fromUserName, toUserID, MsgNO);
// try
// {
// //鎖住線程,保證本次發送完成後其他線程才能對此用戶發送消息
// lock (userSkt)
// {
// userSkt.Send(msg);
// //SendData(userSkt, msg);
// }
// }
// catch (SocketException ex)
// {
// //掉線了,直接干掉.
// try
// {
// LogHelper.WriteLog("SendP2PMessage", "發消息給手機用戶:" + fromUserID + "時發生異常,斷開連接." + (ex != null ? ex.Message : ""));
// //clsClientLog.WriteLog(clsClientLog.LogType.Error, "WebThreadFunc(object obj):catch (SocketException)", "發消息給Mobile端用戶:" + fromUserID + "時發生異常,斷開連接.");
// if (userSkt.Connected)
// {
// userSkt.Shutdown(SocketShutdown.Both);
// //userSkt.Disconnect(false);
// }
// userSkt.Close();
// #region " 聯絡人狀態變更 "
// //通知在線的聯絡人當前用戶改變了狀態
// //recUser.SetUserState("6");
// //UpdateUserLog(recUser.ID, "1", "1", "異常登出");
// //如果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(fromUserID);
// Thread userThread = _ws_thread_tb[fromUserID] as Thread;
// userThread.Abort();
// //線程池不會被遍歷,不鎖.
// _ws_thread_tb.Remove(fromUserID);
// }
// catch
// { }
// }
//}
#endregion
#region " Mobile Client "
PushRoaming(fromUserID, ParseProtocol.ConvertMsgToByte(DataBody, DataType, toUserID, "", "", 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.AudioCall ||
m_userToken.BindingUser.Role == LxTcpServer.Core.User.UserRole.APP)
{
//來自Mobile(Web)端消息.
//檢查相同帳號Web端有沒有登錄,有就轉發一次.
#region " PC Client "
PushRoaming(fromUserID, ParseProtocol.ConvertMsgToByte(DataBody, DataType, toUserID, "", "", MsgGUID, MsgNO, "R"), m_asyncSocketServer.PcUserTokenList);
#endregion
}
}
/// <summary>
/// 通話日誌記錄
/// </summary>
private void UpdateAudioLog(string SenderID, string ReceiverID, DateTime StartDT, DateTime EndDT, string logGUID, string logSIP, string devSName, string devSMac, string logRIP, string devRName, string devRMac)
{
using (SQLHelper conn = new SQLHelper(m_asyncSocketServer.DBInfo))
{
conn.OpenConnection();
try
{
DateTime LogDT = DateTime.Now;
TimeSpan TS = EndDT.Subtract(StartDT);
conn.ExecuteSQL("insert into lxaudiolog (sender_id,receiver_id,ie_ymd,ie_time,log_sdate,log_stime,log_edate,log_etime,log_durtime,log_guid,log_sip,dev_sname,dev_smac,log_rip,dev_rname,dev_rmac) " + "\r\n" +
"values('" + SenderID + "','" + ReceiverID + "','" + LogDT.ToString("yyyy/MM/dd") + "','" + LogDT.ToString("HH:mm:ss") + "'," + "\r\n" +
"'" + StartDT.ToString("yyyy/MM/dd") + "','" + StartDT.ToString("HH:mm:ss") + "'," +
"'" + EndDT.ToString("yyyy/MM/dd") + "','" + EndDT.ToString("HH:mm:ss") + "'," + (int)TS.TotalSeconds + "," +
"'" + logGUID + "','" + logSIP + "','" + devSName + "','" + devSMac + "','" + logRIP + "','" + devRName + "','" + devRMac + "')");
conn.CloseConnection();
}
catch (Exception ex)
{
conn.CloseConnection();
throw ex;
}
}
}
/// <summary>
/// DataTable 里存放的是有異動的資料
/// </summary>
/// <param name="Contacts"></param>
/// <param name="GMembers"></param>
/// <param name="userid"></param>
public void UpdateContacts(DataTable Contacts, DataTable GMembers, string userid)
{
string SQL = "";
using (SQLHelper conn = new SQLHelper())
{
conn.OpenConnection();
try
{
string tmp_LRCONTACT = "tmp_LRCONTACT_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
string tmp_LRGMEMBER = "tmp_LRGMEMBER_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
SQLHelper connTemp = new SQLHelper();
//開啟tempdb連接
connTemp.OpenConnection("tempdb");
connTemp.ExecuteSQL("create table " + tmp_LRCONTACT + "(" + conn.GetTableStruct("lrcontact") + ",contact_modify varchar(1) not null default ('9'))");
connTemp.ExecuteSQL("create table " + tmp_LRGMEMBER + "(" + conn.GetTableStruct("lrgmember") + ")");
connTemp.CloseConnection();
tmp_LRCONTACT = "tempdb.." + tmp_LRCONTACT;
tmp_LRGMEMBER = "tempdb.." + tmp_LRGMEMBER;
foreach (DataRow dr in Contacts.Rows)
{
byte[] by;
if (dr["contact_img"] == null)
{
by = (byte[])dr["contact_img"];
SQL =
"insert into " + tmp_LRCONTACT + "(user_no,contact_id,contact_name,contact_company," +
"contact_position,contact_modify,contact_sex,contact_img) " + "\r\n" +
" select '" + dr["user_no"].ToString() + "','" + dr["contact_id"].ToString() + "','" + dr["contact_name"].ToString() + "','" + dr["contact_company"].ToString() + "','" + dr["contact_position"].ToString() + "','" + dr["contact_modify"].ToString() + "','" + dr["contact_sex"].ToString() + "',@image";
conn.ExecuteSQLImage(SQL, "@image", by);
}
else
{
SQL =
"insert into " + tmp_LRCONTACT + "(user_no,contact_id,contact_name,contact_company," +
"contact_position,contact_modify,contact_sex) " + "\r\n" +
" select '" + dr["user_no"].ToString() + "','" + dr["contact_id"].ToString() + "','" + dr["contact_name"].ToString() + "','" + dr["contact_company"].ToString() + "','" + dr["contact_position"].ToString() + "','" + dr["contact_modify"].ToString() + "','" + dr["contact_sex"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
}
foreach (DataRow dr in GMembers.Rows)
{
SQL =
"insert into " + tmp_LRGMEMBER + "(contact_id,group_id) " + "\r\n" +
"select '" + dr["contact_id"].ToString() + "','" + dr["group_id"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
conn._Transaction = conn._Connection.BeginTransaction();
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
//Add = 0,//新增的
//Delete =1,//刪除的
//Update = 2,//有更新的
//1.刪除要標記為Delete的資料
SQL =
" delete a " + "\r\n" +
" from lrcontact a " + "\r\n" +
" join " + tmp_LRCONTACT + " b on a.contact_id = b.contact_id and a.user_no = b.user_no " + "\r\n" +
" where b.contact_modify = '1'";
conn.ExecuteSQL(SQL);
//2.更新要標記為Update的資料的資料
SQL =
" update a set " + "\r\n" +
" a.contact_id = b.contact_id," + "\r\n" +
" a.contact_name = b.contact_name," + "\r\n" +
" a.contact_company = b.contact_company," + "\r\n" +
" a.contact_position = b.contact_position," + "\r\n" +
" a.contact_sex = b.contact_sex," + "\r\n" +
" a.contact_img = b.contact_img," + "\r\n" +
" a.ie_lymd ='" + ymd + "'," + "\r\n" +
" a.ie_ltime ='" + time + "'," + "\r\n" +
" a.ie_luser ='******' " + "\r\n" +
" from lrcontact a " + "\r\n" +
" join " + tmp_LRCONTACT + " b on a.contact_id = b.contact_id and a.user_no = b.user_no " + "\r\n" +
" where b.contact_modify = '2'";
conn.ExecuteSQL(SQL);
//3.新增要標記為Add的資料
SQL =
" insert into lrcontact(ie_ymd,ie_time,ie_user,user_no,contact_id,contact_name," + "\r\n" +
" contact_company,contact_position,contact_sex) " + "\r\n" +
" select '" + ymd + "','" + time + "','" + userid + "',user_no,contact_id,contact_name," + "\r\n" +
" contact_company,contact_position,contact_sex" + "\r\n" +
" from " + tmp_LRCONTACT + " b " + "\r\n" +
" where b.contact_modify = '0'";
conn.ExecuteSQL(SQL);
//最後清楚對照關係
//已經刪除的group 要清除
SQL =
" delete a from lrgmember a " +
" left join lrgroup b on a.group_id=b.group_id" +
" where b.uid is null ";
conn.ExecuteSQL(SQL);
//新增新的群組
SQL =
"insert into lrgmember(ie_ymd,ie_time,ie_user,group_id,contact_id) " +
"select '" + ymd + "','" + time + "','" + userid + "',group_id,contact_id " +
"from " + tmp_LRGMEMBER + " a " +
"where not exists(select 1 from lrgmember x where x.group_id=a.group_id and x.contact_id=a.contact_id)";
conn.ExecuteSQL(SQL);
//刪除不存在的聯絡人
//沒有對照關係的要刪除
SQL =
" delete a from lrgmember a " + "\r\n" +
" join lrgroup b on a.group_id= b.group_id " + "\r\n" +
" where not exists(select 1 from " + tmp_LRGMEMBER + " c " + "\r\n" +
" where a.group_id=c.group_id and a.contact_id=c.contact_id) " + "\r\n" +
" and exists(select 1 from " + tmp_LRGMEMBER + " x where x.contact_id=a.contact_id) and b.user_no ='" + userid + "'";
conn.ExecuteSQL(SQL);
//聯絡人 沒有了要刪除
SQL =
" delete a from lrgmember a " +
" join lrgroup c on a.group_id = c.group_id " +
" left join lrcontact b on a.contact_id = b.contact_id and c.user_no = b.user_no " +
" where b.uid is null and c.user_no ='" + userid + "' ";
conn.ExecuteSQL(SQL);
SQL =
" delete a from lrgmember a " +
" left join lrgroup b on a.group_id = b.group_id" +
" where b.uid is null";
conn.ExecuteSQL(SQL);
conn._Transaction.Commit();
conn.CloseConnection();
}
catch (System.Exception ex)
{
conn._Transaction.Rollback();
conn.CloseConnection();
WriteLog(userid, ex.Message, "ErrLog");
throw ex;
}
}
}