254 lines
14 KiB
C#
254 lines
14 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using WX.CRM.Common;
|
|
using WX.CRM.Model.weapp;
|
|
|
|
namespace WX.CRM.DAL.weapp
|
|
{
|
|
public class AppletMsgDal
|
|
{
|
|
public bool WeapAddMessage(AppletMsgModel info, int eid, int issend, int isRead)
|
|
{
|
|
bool issucced = true;
|
|
if (info.Event != null && (info.Event == "VIEW" || info.Event == "TEMPLATESENDJOBFINISH"))
|
|
return WeapAddMessage_Event(info, eid, issend, isRead);
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnecionString(SqlHelper.DatabaseType.AYCRM)))
|
|
{
|
|
conn.Open();
|
|
using (SqlTransaction trans = conn.BeginTransaction())
|
|
{
|
|
try
|
|
{
|
|
var sql = @"INSERT INTO weapp_message([ToUserName],[FromUserName],[CreateTime],[MsgType],[Content],[MsgId],[PicUrl],[MediaId],[Ctime],[EID],[IsSend],[IsRead],[Event],[EventKey],[Ticket],[Latitude],[Longitude],[Precision]) VALUES
|
|
(@ToUserName,@FromUserName,@CreateTime,@MsgType, @Content,@MsgId,@PicUrl,@MediaId,@Ctime,@Eid,@IsSend,@IsRead,@Event,@EventKey,@Ticket,@Latitude,@Longitude,@Precision)";
|
|
#region
|
|
|
|
var para = new List<SqlParameter>
|
|
{
|
|
new SqlParameter("@ToUserName", info.ToUserName),
|
|
new SqlParameter("@FromUserName", info.FromUserName),
|
|
new SqlParameter("@CreateTime",info.CreateTime),
|
|
new SqlParameter("@MsgType", info.MsgType),
|
|
string.IsNullOrEmpty(info.Content)?new SqlParameter("@Content", DBNull.Value):new SqlParameter("@Content", info.Content),
|
|
new SqlParameter("@MsgId", info.MsgId),
|
|
string.IsNullOrEmpty(info.PicUrl)? new SqlParameter("@PicUrl", DBNull.Value):new SqlParameter("@PicUrl", info.PicUrl),
|
|
string.IsNullOrEmpty(info.MediaId)? new SqlParameter("@MediaId", DBNull.Value): new SqlParameter("@MediaId", info.MediaId),
|
|
new SqlParameter("@Ctime", System.DateTime.Now),
|
|
new SqlParameter("@Eid",eid),
|
|
new SqlParameter("@IsSend",issend),
|
|
new SqlParameter("@IsRead",isRead),
|
|
string.IsNullOrEmpty(info.Event)? new SqlParameter("@Event", DBNull.Value): new SqlParameter("@Event", info.Event),
|
|
string.IsNullOrEmpty(info.EventKey)? new SqlParameter("@EventKey", DBNull.Value): new SqlParameter("@EventKey", info.EventKey),
|
|
string.IsNullOrEmpty(info.Ticket)? new SqlParameter("@Ticket", DBNull.Value): new SqlParameter("@Ticket", info.Ticket),
|
|
string.IsNullOrEmpty(info.Latitude)? new SqlParameter("@Latitude", DBNull.Value): new SqlParameter("@Latitude", info.Latitude),
|
|
string.IsNullOrEmpty(info.Longitude)? new SqlParameter("@Longitude", DBNull.Value): new SqlParameter("@Longitude", info.Longitude),
|
|
string.IsNullOrEmpty(info.Precision)? new SqlParameter("@Precision", DBNull.Value): new SqlParameter("@Precision", info.Precision),
|
|
};
|
|
#endregion
|
|
SqlHelper.ExecuteSql(conn, trans, sql, para.ToArray());
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
issucced = false;
|
|
LogHelper.Error("weap erro--- MsgId:" + info.MsgId + ex.ToString());
|
|
trans.Rollback();
|
|
}
|
|
}
|
|
}
|
|
return issucced;
|
|
}
|
|
public bool WeapAddMessage_Event(AppletMsgModel info, int eid, int issend, int isRead)
|
|
{
|
|
bool issucced = true;
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnecionString(SqlHelper.DatabaseType.AYCRM)))
|
|
{
|
|
conn.Open();
|
|
using (SqlTransaction trans = conn.BeginTransaction())
|
|
{
|
|
try
|
|
{
|
|
var sql = @"INSERT INTO weapp_message_event([ToUserName],[FromUserName],[CreateTime],[MsgType],[Content],[MsgId],[PicUrl],[MediaId],[Ctime],[EID],[IsSend],[IsRead],[Event],[EventKey],[Ticket],[Latitude],[Longitude],[Precision]) VALUES
|
|
(@ToUserName,@FromUserName,@CreateTime,@MsgType, @Content,@MsgId,@PicUrl,@MediaId,@Ctime,@Eid,@IsSend,@IsRead,@Event,@EventKey,@Ticket,@Latitude,@Longitude,@Precision)";
|
|
#region
|
|
|
|
var para = new List<SqlParameter>
|
|
{
|
|
new SqlParameter("@ToUserName", info.ToUserName),
|
|
new SqlParameter("@FromUserName", info.FromUserName),
|
|
new SqlParameter("@CreateTime",info.CreateTime),
|
|
new SqlParameter("@MsgType", info.MsgType),
|
|
string.IsNullOrEmpty(info.Content)?new SqlParameter("@Content", DBNull.Value):new SqlParameter("@Content", info.Content),
|
|
new SqlParameter("@MsgId", info.MsgId),
|
|
string.IsNullOrEmpty(info.PicUrl)? new SqlParameter("@PicUrl", DBNull.Value):new SqlParameter("@PicUrl", info.PicUrl),
|
|
string.IsNullOrEmpty(info.MediaId)? new SqlParameter("@MediaId", DBNull.Value): new SqlParameter("@MediaId", info.MediaId),
|
|
new SqlParameter("@Ctime", System.DateTime.Now),
|
|
new SqlParameter("@Eid",eid),
|
|
new SqlParameter("@IsSend",issend),
|
|
new SqlParameter("@IsRead",isRead),
|
|
string.IsNullOrEmpty(info.Event)? new SqlParameter("@Event", DBNull.Value): new SqlParameter("@Event", info.Event),
|
|
string.IsNullOrEmpty(info.EventKey)? new SqlParameter("@EventKey", DBNull.Value): new SqlParameter("@EventKey", info.EventKey),
|
|
string.IsNullOrEmpty(info.Ticket)? new SqlParameter("@Ticket", DBNull.Value): new SqlParameter("@Ticket", info.Ticket),
|
|
string.IsNullOrEmpty(info.Latitude)? new SqlParameter("@Latitude", DBNull.Value): new SqlParameter("@Latitude", info.Latitude),
|
|
string.IsNullOrEmpty(info.Longitude)? new SqlParameter("@Longitude", DBNull.Value): new SqlParameter("@Longitude", info.Longitude),
|
|
string.IsNullOrEmpty(info.Precision)? new SqlParameter("@Precision", DBNull.Value): new SqlParameter("@Precision", info.Precision),
|
|
};
|
|
#endregion
|
|
SqlHelper.ExecuteSql(conn, trans, sql, para.ToArray());
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
issucced = false;
|
|
LogHelper.Error("weap erro--- MsgId:" + info.MsgId + ex.ToString());
|
|
trans.Rollback();
|
|
}
|
|
}
|
|
}
|
|
return issucced;
|
|
}
|
|
public bool WeapAddFilePath(long MsgId, string MediaId, string Path)
|
|
{
|
|
bool issucced = true;
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnecionString(SqlHelper.DatabaseType.AYCRM)))
|
|
{
|
|
conn.Open();
|
|
using (SqlTransaction trans = conn.BeginTransaction())
|
|
{
|
|
try
|
|
{
|
|
var sql = @"delete from weapp_filepath where MediaId=@MediaId; INSERT INTO weapp_filepath ([MsgId],[MediaId],[Ctime],[Path])VALUES (@MsgId,@MediaId,@Ctime,@Path)";
|
|
#region
|
|
|
|
var para = new List<SqlParameter>
|
|
{
|
|
new SqlParameter("@MsgId", MsgId),
|
|
new SqlParameter("@MediaId", MediaId),
|
|
new SqlParameter("@Ctime",System.DateTime.Now),
|
|
new SqlParameter("@Path", Path)
|
|
};
|
|
#endregion
|
|
SqlHelper.ExecuteSql(conn, trans, sql, para.ToArray());
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
issucced = false;
|
|
LogHelper.Error("weapFilepath erro--- MsgId:" + MsgId + ex.ToString());
|
|
trans.Rollback();
|
|
}
|
|
}
|
|
}
|
|
return issucced;
|
|
}
|
|
/// <summary>
|
|
/// 是否含有文件
|
|
/// </summary>
|
|
/// <param name="MediaId"></param>
|
|
/// <returns></returns>
|
|
public DataTable IsHasMediaId(string MediaId)
|
|
{
|
|
try
|
|
{
|
|
DataSet dset = SqlHelper.Query(SqlHelper.DatabaseType.AYCRM, "select * from weapp_filepath where mediaid='" + MediaId + "' and path is not null and path!=''");
|
|
return dset.Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
}
|
|
return null;
|
|
}
|
|
/// <summary>
|
|
/// 获取未读消息
|
|
/// </summary>
|
|
/// <param name="eid">工号</param>
|
|
/// <param name="accountnum">公众号</param>
|
|
/// <returns></returns>
|
|
public DataTable GetUnReadMsg(int eid, string accountnum)
|
|
{
|
|
try
|
|
{
|
|
string sql = "select m.*,f.Path from weapp_message m left join weapp_filepath f on m.MediaId = f.MediaId where m.eid=@eid and m.tousername=@accountnum and m.isread=0 and (Event is null or Event not in ('VIEW','TEMPLATESENDJOBFINISH')) order by m.id";
|
|
SqlParameter[] param = {
|
|
new SqlParameter("@eid",eid),
|
|
new SqlParameter("@accountnum",accountnum)
|
|
};
|
|
|
|
DataSet dset = SqlHelper.GetDataSet(SqlHelper.DatabaseType.AYCRM, sql, CommandType.Text, param);
|
|
return dset.Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public DataTable GetNewAndIsReadMsg(int eid, string accountnum, string openid, long pkid)
|
|
{
|
|
try
|
|
{
|
|
//string sql = "select m.*,f.Path from weapp_message m left join weapp_filepath f on m.MediaId = f.MediaId where m.eid=@eid and m.id>@pkid and m.isread=1 and (Event is null or Event not in ('VIEW','TEMPLATESENDJOBFINISH')) and ((m.fromusername=@accountnum and m.tousername=@openid)or(m.fromusername=@openid and m.tousername=@accountnum)) order by m.id";
|
|
string sql = "select m.*,f.Path from weapp_message m left join weapp_filepath f on m.MediaId = f.MediaId where m.id>@pkid and m.isread=1 and (Event is null or Event not in ('VIEW','TEMPLATESENDJOBFINISH')) and ((m.fromusername=@accountnum and m.tousername=@openid)or(m.fromusername=@openid and m.tousername=@accountnum)) order by m.id";
|
|
SqlParameter[] param = {
|
|
//new SqlParameter("@eid",eid),
|
|
new SqlParameter("@accountnum",accountnum),
|
|
new SqlParameter("@openid",openid),
|
|
new SqlParameter("@pkid",pkid)
|
|
};
|
|
|
|
DataSet dset = SqlHelper.GetDataSet(SqlHelper.DatabaseType.AYCRM, sql, CommandType.Text, param);
|
|
return dset.Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
}
|
|
return null;
|
|
|
|
}
|
|
/// <summary>
|
|
/// 修改消息状态成已读
|
|
/// </summary>
|
|
/// <param name="eid">工号</param>
|
|
/// <param name="openId">客户openid</param>
|
|
/// <param name="accountnum">公众号</param>
|
|
/// <returns></returns>
|
|
public bool UpdateIsAlreadyRead(int eid, string openId, string accountnum)
|
|
{
|
|
bool issucced = true;
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnecionString(SqlHelper.DatabaseType.AYCRM)))
|
|
{
|
|
conn.Open();
|
|
using (SqlTransaction trans = conn.BeginTransaction())
|
|
{
|
|
try
|
|
{
|
|
var sql = @"update weapp_message set isread=1 where FromUserName=@openId and ToUserName=@tousername and EID=@eid and IsRead=0";
|
|
#region
|
|
|
|
var para = new List<SqlParameter>
|
|
{
|
|
new SqlParameter("@openId", openId),
|
|
new SqlParameter("@tousername", accountnum),
|
|
new SqlParameter("@eid",eid),
|
|
};
|
|
#endregion
|
|
SqlHelper.ExecuteSql(conn, trans, sql, para.ToArray());
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
issucced = false;
|
|
trans.Rollback();
|
|
}
|
|
}
|
|
}
|
|
return issucced;
|
|
}
|
|
}
|
|
}
|