/// <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="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; } }
/// <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; } } }
//Added by Donnie on 2016/01/31 /// <summary> /// 更新用戶頭像 /// </summary> /// <returns></returns> public bool UpdateAvatar(string UserID, byte[] AvatarData) { try { using (SQLHelper conn = new SQLHelper()) { if (conn.OpenConnection()) { conn.ExecuteSQLImage("update a set a.user_img =@avatar from lrtduser a where user_no='" + UserID + "'", "@avatar", AvatarData); } conn.CloseConnection(); } //返回Client return true; } catch { return false; } }
/// <summary> /// 合並文件(輸入一個byte[]:inByte,把它合併到自已的byte[] ownByte中去,再返回) /// </summary> /// <param name="inByte"></param> /// <param name="ownByte"></param> /// <returns></returns> //public byte[] incorporateByte(byte[] inByte, byte[] ownByte) //{ // int inByteLength = 0; // if (inByte != null) { inByteLength = inByte.Length; } // int ownByteLength = 0; // if (ownByte != null) { ownByteLength = ownByte.Length; } // byte[] buffer = new byte[ownByteLength + inByteLength]; // if (ownByte != null) { ownByte.CopyTo(buffer, 0); } // if (inByte != null) { inByte.CopyTo(buffer, ownByteLength); } // return buffer; //} #region " 訊息在DB中的存取操作 " /// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="User"></param> /// <param name="Img"></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 InsertImage(SQLHelper conn, string User, byte[] Img, string td_no, string msg_to, string MsgType, string GUID) { try { long SeqNO = 0; string SQL = "select msg_no from lrtdmsg (nolock) where msg_guid='" + GUID + "'"; DataTable Rec = conn.OpenDataTable(SQL, CommandType.Text); if (Rec.Rows.Count > 0) { //重復就不寫入 return Rec.Rows[0]["msg_no"].ToString(); } lock (Ticket) { 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()); } } 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_doctype,msg_docstate,msg_guid,msg_img) " + "values('" + ymd + "','" + time + "','" + User + "','" + td_no + "','" + User + "','" + msg_to + "','" + ymd + "','" + time + "',N''," + SeqNO + ",'" + MsgType + "','3','0','" + GUID + "',@img)"; conn.ExecuteSQLImage(SQL, "@img", Img); //clsClientLog.WriteLog(clsClientLog.LogType.Error, "InsertMsg", "發送的消息是:" + Msg); 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); } else if (msg_to.Length > 0) { // ##發給個人## SQL = "insert into lrmsgstate (msg_no,msg_to,msg_state) values('" + SeqNO + "','" + msg_to + "','0')"; conn.ExecuteSQL(SQL); } 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; } }