public static DataTable SelectUserIdsByUnionID(IEnumerable <string> unionids)
{
if (unionids != null && unionids.Any())
{
string sql = @"SELECT u.UserId ,u.UnionId COLLATE Chinese_PRC_CI_AS
FROM Tuhu_profiles..UserAuth AS u WITH ( NOLOCK )
JOIN @TVP AS t ON u.UnionId COLLATE Chinese_PRC_CI_AS = t.TargetID
WHERE u.AuthSource = N'Weixin'
AND u.BindingStatus = N'Bound' and u.UserId is not null and u.UnionId is not null ;";
var records = new List <SqlDataRecord>(unionids.Count());
foreach (var target in unionids)
{
var record = new SqlDataRecord(new SqlMetaData("TargetID", SqlDbType.Char, 100));
var chars = new SqlChars(target);
record.SetSqlChars(0, chars);
records.Add(record);
}
using (var helper = DbHelper.CreateDbHelper(true))
{
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
SqlParameter p = new SqlParameter("@TVP", SqlDbType.Structured);
p.TypeName = "dbo.Targets";
p.Value = records;
cmd.Parameters.Add(p);
DataTable result = helper.ExecuteQuery(cmd, dt => dt);
return(result);
}
}
}
return(null);
}
private void WriteSyncLogs(IEnumerable <UserMessageBoxInfoRelation> logs)
{
// type=1的时候id是Tuhu_notification..MessageBoxMessageInfo的pkid type=2时id是Tuhu_notification..UserMessageBoxInfoRelation的pkid
if (logs != null && logs.Any())
{
Func <IEnumerable <int>, int, int> WriteLogs = (ids, type) =>
{
if (ids != null && ids.Any())
{
string sql = $@"INSERT INTO Tuhu_notification..ExpiredMessageBoxInfo
( ID ,
Type ,
CreateDateTime ,
LastUpdateDateTime
)
SELECT ss.TargetID ,
{type} ,
GETDATE() ,
GETDATE()
FROM @TVP AS ss;";
using (var helper = DbHelper.CreateLogDbHelper())
{
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
var records = new List <SqlDataRecord>(ids.Count());
foreach (var target in ids)
{
var record = new SqlDataRecord(new SqlMetaData("TargetID", SqlDbType.Char, 40));
var chars = new SqlChars(target.ToString());
record.SetSqlChars(0, chars);
records.Add(record);
}
SqlParameter p = new SqlParameter("@TVP", SqlDbType.Structured);
p.TypeName = "dbo.Target";
p.Value = records;
cmd.Parameters.Add(p);
return(helper.ExecuteNonQuery(cmd));
}
}
}
return(0);
};
var messageids = logs.Select(x => x.MessageID).Distinct();
var relationids = logs.Select(x => x.PKID);
var messageresult = WriteLogs(messageids, 1);
Logger.Info($"写过期消息{messageresult}个");
var relationresult = WriteLogs(relationids, 2);
Logger.Info($"写过期消息关系{relationresult}个");
}
}
