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