public void ReadFromCSV(string FileName)
{
List <QSUser> values;
try
{
if (!File.Exists(FileName))
{
File.Create(FileName);
}
values = File.ReadAllLines(FileName, System.Text.Encoding.Default)
.Skip(0)
.Select(v => QSUser.FromCsv(v))
.ToList();
}
catch (Exception e)
{
Console.WriteLine("Error in QSUsers with file \"{0}\": {1} Exception caught.", FileName, e);
values = null;
}
if (values != null)
{
Users = values;
}
}
public QSUser AddUser(string UserId, string UserName = "")
{
QSUser Usr;
try
{
Usr = GetUser(UserId);
if (Usr == null)
{
// New User
Usr = new QSUser();
Usr.UserId = UserId.Trim();
Users.Add(Usr);
}
// Update properties
if (Usr.UserName == "" || UserName != "")
{
Usr.UserName = UserName.Trim();
}
}
catch (Exception e)
{
Console.WriteLine("Error in AddUser with UserId \"{0}\": {1} Exception caught.", UserId, e);
return(null);
}
return(Usr);
}
public static string ToCsv(QSUser U)
{
string csvLine;
csvLine = string.Format("{0};{1}", U.UserId, U.UserName);
return(csvLine);
}
private async void Bot_OnInlineQuery(object sender, InlineQueryEventArgs e)
{
var message = e.InlineQuery;
QSUser Usr = CheckTheUser(message.From.Id.ToString(),
message.From,
message.From.FirstName + " " + message.From.LastName);
var vis = Usr.QS.FindVisualization(e.InlineQuery.Query);
var MasterMea = Usr.QS.GetMasterMeasure(e.InlineQuery.Query);
var MasterVis = Usr.QS.GetMasterVisualizations(e.InlineQuery.Query);
//var MasterDim = Usr.QS.GetMasterDimensions(e.InlineQuery.Query);
//Console.WriteLine(Usr.QS.GetExpressionValue(MasterDim.Expression));
Console.WriteLine("Inline query " + message.Query + " received by " + message.From.Id);
InlineQueryResult[] results =
{
new InlineQueryResultArticle {
Description = "Visualizations",
Id = "1",
Title = vis.Title,
InputMessageContent = new InputTextMessageContent{
MessageText = vis.Title
}
, Url = Usr.QS.PrepareVisualizationDirectLink(vis)
, ThumbUrl = Usr.QS.GetVisualizationThumbnailUrl(vis.Type), HideUrl = true
},
new InlineQueryResultArticle {
Description = "Master Visualizations",
Id = "2",
Title = MasterVis.Name,
InputMessageContent = new InputTextMessageContent{
MessageText = MasterVis.Name
},
Url = Usr.QS.PrepareMasterVisualizationDirectLink(MasterVis),
HideUrl = true
},
new InlineQueryResultArticle {
Description = "Measures",
Id = "3",
Title = MasterMea.Name,
InputMessageContent = new InputTextMessageContent{
MessageText = ("The value of Measure " + MasterMea.Name + " is " + MasterMea.FormattedExpression)
}
}
//new InlineQueryResultArticle {
// Description = "Dimensions",
// Id ="4",
// Title = MasterDim.Name,
// InputMessageContent = new InputTextMessageContent { MessageText= MasterDim.Name + ": " + Usr.QS.GetExpressionValue(MasterDim.Expression)}
// }
};
await Bot.AnswerInlineQueryAsync(e.InlineQuery.Id, results, isPersonal : true, cacheTime : 0);
}
public static QSUser FromCsv(string csvLine)
{
string[] values = csvLine.Split(';');
QSUser U = new QSUser();
U.UserId = values[0].ToLower().Trim(); // To compare case insensitive
U.UserName = values[1].Trim();
return(U);
}
public void WriteToCSV(string FileName)
{
try
{
File.WriteAllLines(FileName, Users.Select(u => QSUser.ToCsv(u)), System.Text.Encoding.Default);
}
catch (Exception e)
{
Console.WriteLine("Error in QSUsers writing to file \"{0}\": {1} Exception caught.", FileName, e);
}
}
/*********************************************************************************************************
* // This function will check the user is already known or it will be added
* // It will also do any check to be sure the user is ready, for example test the Qlik Sense connection
*********************************************************************************************************/
private static QSUser CheckTheUser(string UserId, Telegram.Bot.Types.User TelegramUser, string UserName = "")
{
// Add the user if not exists
// All new users are allowed by default except if running in Demo Mode, they can be banned in the users file
QSUser Usr = QlikUsers.AddUser(UserId, UserName);
Usr.QS = (Usr.QS == null) ? QSTemplateApp : Usr.QS;
if (Usr.QS == null)
{ // This user has no connection
Usr.QS = new QSApp();
// This is the default Qlik Sense app for new users
// Fill it with the default values from the template
Usr.QS.qsAppName = QSTemplateApp.qsAppName;
Usr.QS.qsAppId = QSTemplateApp.qsAppId;
Usr.QS.qsServer = QSTemplateApp.qsServer;
Usr.QS.qsSingleServer = QSTemplateApp.qsSingleServer;
Usr.QS.qsSingleApp = QSTemplateApp.qsSingleApp;
Usr.QS.qsAlternativeStreams = QSTemplateApp.qsAlternativeStreams;
try
{
// Create or use the Telegram UserID as the Qlik Sense UserID to open the connection
Connect(Usr.QS, UserId, QSTemplateApp.VirtualProxy(), QSTemplateApp.IsUsingSSL());
// And open the app
if (!Usr.QS.AppIsOpen)
{
Usr.QS.QSOpenApp();
}
Console.WriteLine(string.Format("Opened the Qlik Sense app: {0} for user {1} ({2})", Usr.QS.qsAppName, Usr.UserId, Usr.UserName));
}
catch (Exception e)
{
Console.WriteLine(string.Format("Error opening the Qlik Sense app{0} for user {1}: {2}", Usr.QS.qsAppName, Usr.UserId, e));
}
}
else
{
// The connection was previously created, so check the connection is alive
Usr.QS.CheckConnection();
}
return(Usr);
}
public async void BotOnMessageReceived(object sender, MessageEventArgs messageEventArgs)
{
try
{
var message = messageEventArgs.Message;
string response = "";
// Show the bot is working, as he was typing something
BotShowTypingState(message.Chat.Id);
if (message == null || message.Type == MessageType.ServiceMessage || message.Type == MessageType.UnknownMessage)
{
return;
}
if (message.Entities.Count > 0 && message.Entities[0].Type == MessageEntityType.TextLink)
{
return;
}
string MessageText = "";
if (message.Text != null)
{
MessageText = message.Text.Trim().ToLower(); // Here the message to compare with the predefined commands
}
// Show the message
Console.WriteLine(string.Format("Message Received from {0}: {1}", message.From.Id.ToString(), MessageText));
// Add the user if it not exists
QSUser Usr = CheckTheUser(message.From.Id.ToString(),
message.From,
message.From.FirstName + " " + message.From.LastName);
// Start some logic to answer the questions
// This first block, only some basic predefined commands
if (MessageText.Contains("sales"))
{
string Val = Usr.QS.GetExpression(MeasDefSales);
response = "The value of sales is " + Val;
}
else if (MessageText.Contains("margin"))
{
string Val = Usr.QS.GetExpression(MeasDefMargin);
response = "The value of margin is " + Val;
}
else if (MessageText.Contains("inventory"))
{
string Val = Usr.QS.GetExpression(MeasDefInventory);
response = "The value of inventory is " + Val;
}
else if (MessageText.Contains("cost"))
{
string Val = Usr.QS.GetExpression(MeasDefCost);
response = "The value of inventory is " + Val;
}
// Now we show a button menu, to allow the user select some KPIs
else if (MessageText == "kpi") // request some kpis
{
// Let's prepare a set of buttons with different kpis
// ready to touch and get the answer
var keyboard = new InlineKeyboardMarkup(new[]
{
new[] // first row
{
new InlineKeyboardButton("Sales", "#kpiSales"),
new InlineKeyboardButton("Margin", "#kpiMargin"),
},
new[] // second row
{
new InlineKeyboardButton("Inventory", "#kpiInventory"),
new InlineKeyboardButton("Cost", "#kpiCost")
},
new[] // second row
{
new InlineKeyboardButton("Full Analysis", "#kpiAnalysis")
}
});
await Bot.SendTextMessageAsync(message.Chat.Id, "Choose a Kpi", replyMarkup : keyboard);
return;
}
// It is important to be polite
else if (MessageText.Contains("hello") || MessageText.StartsWith("hi"))
{
response = "Hi " + Usr.UserName + ", how are you?";
}
else if (MessageText.Contains("bye"))
{
response = "Bye " + Usr.UserName + "\n👋";
}
// Show basic help if needed
else if (MessageText.Contains("help"))
{
// Options not supported yet
// response = "I am sorry, but I do not know how to manage your message \"" + message.Text + "\"";
response = "This cool bot is connected to Qlik Sense to help you analyse your information.";
response += "\n\nUsage:\nkpi - show a KPI\nSales - Show the current sales value\nEtc.";
}
// If not a predefined command, let's try to find a measure from the text received
else
{
// If no other option, the user could have asked for a measure --> Try to find one and send the value
string FoundMeasure = "";
string val = Usr.QS.GetMeasureFormattedValue(MessageText, ref FoundMeasure);
response = "The value of " + FoundMeasure + " is " + val;
}
await BotSendTextMessage(message.Chat.Id, response);
Console.WriteLine("Response: " + response);
}
catch (Exception e)
{
Console.WriteLine(string.Format("General Error in BotOnMessageReceived: {0}", e));
}
}
public async void BotOnCallbackQueryReceived(object sender, CallbackQueryEventArgs callbackQueryEventArgs)
{
string msg;
var chatMsg = callbackQueryEventArgs.CallbackQuery.Message;
QSUser Usr = CheckTheUser(callbackQueryEventArgs.CallbackQuery.From.Id.ToString(),
callbackQueryEventArgs.CallbackQuery.From,
callbackQueryEventArgs.CallbackQuery.From.FirstName + " " + callbackQueryEventArgs.CallbackQuery.From.LastName);
string ButtonId = callbackQueryEventArgs.CallbackQuery.Data;
if (ButtonId.StartsWith("#kpi"))
{
// This is a KPI button
string kpi = ButtonId.Substring(4);
string val;
switch (kpi.ToLower())
{
// let's use lowercase for comparisons
case "sales":
val = Usr.QS.GetExpression("Num(Sum([Sales Quantity] * [Sales Price]), '#,##0.00 €')");
msg = "The value of Sales is " + val;
break;
case "margin":
val = Usr.QS.GetExpression("Num(Sum({<[Fiscal Year]={$(=(vCurrentYear)-1)}>}[Sales Amount]), '#,##0.00 €')");
msg = "The value of Margin is " + val;
break;
case "inventory":
val = Usr.QS.GetExpression("Num(((Sum([Sales Quantity]*[Sales Price]))-(Sum([Sales Cost Amount])))/(Sum([Sales Price]* [Sales Quantity])), '0.0%')");
msg = "The value of Inventory is " + val;
break;
case "cost":
val = Usr.QS.GetExpression("Num(Sum(ExpenseActual), '#,##0.00 €')");
msg = "The value of Cost is " + val;
break;
case "analysis":
string url = Usr.QS.qsSingleServer + "/sense/app/" + Usr.QS.qsSingleApp + "/sheet/" + Usr.QS.Sheets.First().Id + "/state/analysis"; // Open the Analysis sheet
msg = string.Format("{0}, <a href='{1}'>Here</a> you have a link to analyze all the information freely.", callbackQueryEventArgs.CallbackQuery.From.FirstName, url);
await BotSendTextMessage(chatMsg.Chat.Id, msg, parseMode : ParseMode.Html, replyMarkup : new ReplyKeyboardHide());
return;
default:
msg = "The value of " + kpi + " is unknown";
break;
}
await BotSendTextMessage(chatMsg.Chat.Id, msg, parseMode : ParseMode.Html, replyMarkup : new ReplyKeyboardHide());
}
else if (ButtonId.StartsWith("#measure"))
{
// This is a Measure button
string Measure = ButtonId.Substring(8);
string FoundMeasure = "";
string val = Usr.QS.GetMeasureFormattedValue(Measure, ref FoundMeasure);
msg = "The value of " + FoundMeasure + " is " + val;
await BotSendTextMessage(chatMsg.Chat.Id, msg, parseMode : ParseMode.Html, replyMarkup : new ReplyKeyboardHide());
}
else
{
Console.WriteLine("Button command not supported");
}
}
