public static FrayDbTeamsChannel SqlSaveChannel(FrayDbTeamsChannel channel)
{
using (SqlConnection sqlConnection = new SqlConnection(ChallongeSQLHelperConnectionString))
{
SqlCommand cmd = new SqlCommand(@"INSERT INTO dbo.DB_TEAMS_CHANNEL
(
TEAM_ID,
CHANNEL_ID,
CHANNEL_NAME,
IS_POST
)
VALUES
(
@TeamId,
@ChannelId,
@ChannelName,
@IsPost
)", sqlConnection);
cmd.Parameters.AddWithValue("@TeamId", channel.TeamId);
cmd.Parameters.AddWithValue("@ChannelId", channel.ChannelId);
cmd.Parameters.AddWithValue("@ChannelName", channel.ChannelName);
cmd.Parameters.AddWithValue("@IsPost", channel.IsPost);
sqlConnection.Open();
cmd.ExecuteNonQuery();
}
return(channel);
}
public static FrayDbTeamsChannel SqlGetSingleChannel(string channelId, string teamId)
{
FrayDbTeamsChannel rtnItem = null;
using (SqlConnection sqlConnection = new SqlConnection(ChallongeSQLHelperConnectionString))
{
SqlCommand cmd = new SqlCommand(@"SELECT * FROM dbo.DB_TEAMS_CHANNEL
WHERE CHANNEL_ID = @ChannelId
AND TEAM_ID = @TeamId", sqlConnection);
cmd.Parameters.AddWithValue("@ChannelId", channelId);
cmd.Parameters.AddWithValue("@TeamId", teamId);
sqlConnection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
rtnItem = new FrayDbTeamsChannel();
rtnItem.ChannelId = reader["CHANNEL_ID"].ToString();
rtnItem.TeamId = reader["TEAM_ID"].ToString();
rtnItem.ChannelName = reader["CHANNEL_NAME"].ToString();
rtnItem.IsPost = bool.Parse(reader["IS_POST"].ToString());
}
}
}
return(rtnItem);
}
public static List <FrayDbTeamsChannel> SqlGetPostChannels()
{
List <FrayDbTeamsChannel> rtnList = new List <FrayDbTeamsChannel>();
using (SqlConnection sqlConnection = new SqlConnection(ChallongeSQLHelperConnectionString))
{
SqlCommand cmd = new SqlCommand($@"SELECT * FROM DB_TEAMS_CHANNEL WHERE IS_POST = 1", sqlConnection);
sqlConnection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
FrayDbTeamsChannel part = new FrayDbTeamsChannel();
part.TeamId = reader["TEAM_ID"].ToString();
part.ChannelId = reader["CHANNEL_ID"].ToString();
part.ChannelName = reader["CHANNEL_NAME"].ToString();
part.IsPost = bool.Parse(reader["IS_POST"].ToString());
rtnList.Add(part);
}
}
}
return(rtnList);
}
public async Task HandleMessage(ConnectorClient connector, Activity activity)
{
bool rejected = true;
ChallongeSQLHelper.ChallongeSQLHelperConnectionString = ConfigurationManager.AppSettings["dbConnection"];
var channelThing = activity.GetChannelData <TeamsChannelData>();
FrayDbTeamsTeam existingTeam = ChallongeSQLHelper.SqlGetSingleTeam(channelThing.Team.Id);
if (existingTeam == null)
{
var teamInfo = connector.GetTeamsConnectorClient().Teams.FetchTeamDetails(channelThing.Team.Id);
var addTeam = new FrayDbTeamsTeam()
{
TeamId = teamInfo.Id,
TeamName = teamInfo.Name
};
ChallongeSQLHelper.SqlSaveTeam(addTeam);
rejected = false;
}
//Check to see whether we've already got the channel
FrayDbTeamsChannel existingChannel = ChallongeSQLHelper.SqlGetSingleChannel(channelThing.Channel.Id, channelThing.Team.Id);
if (existingChannel == null)
{
//If your channel id matches the team id, you're in the autogenerate General
if (channelThing.Channel.Id.Equals(channelThing.Team.Id))
{
var addChannel = new FrayDbTeamsChannel()
{
ChannelId = channelThing.Channel.Id,
TeamId = channelThing.Team.Id,
ChannelName = "General",
IsPost = true
};
ChallongeSQLHelper.SqlSaveChannel(addChannel);
rejected = false;
}
else
{
//We gotta get the channels and figure out which one we're in
var convoList = connector.GetTeamsConnectorClient().Teams.FetchChannelList(channelThing.Team.Id);
var currentChannel = convoList.Conversations.First(x => x.Id.Equals(channelThing.Channel.Id));
var addChannel = new FrayDbTeamsChannel()
{
ChannelId = currentChannel.Id,
TeamId = channelThing.Team.Id,
ChannelName = currentChannel.Name,
IsPost = true
};
ChallongeSQLHelper.SqlSaveChannel(addChannel);
rejected = false;
}
}
Activity reply = null;
if (rejected)
{
reply = activity.CreateReply("That's going to be a hard pass");
}
else
{
reply = activity.CreateReply("Saved the data");
}
await connector.Conversations.ReplyToActivityWithRetriesAsync(reply);
}
