public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Chat.Id;
using (var db = new DbNorthwind())
{
var query = await db.LastQuery
.FirstOrDefaultAsync(w => w.ChatId == chatId.ToString());
if (query != null)
{
if (query.IsWatching == 1)
{
ReplyKeyboardMarkup ReplyKeyboard = new[]
{
new [] { "Помощь", "Поиск по категориям" }
};
ReplyKeyboard.ResizeKeyboard = true;
query.IsWatching = 0;
await db.UpdateAsync(query);
await botClient.SendTextMessageAsync(chatId, $"Автоматические уведомления по запрсу \"{query.Query}\" были отключены.\nМожете делать новые запросы", replyMarkup : ReplyKeyboard);
}
}
}
}
async Task <List <WeaponList> > GetLastQuery(string chatId)
{
using (var db = new DbNorthwind())
{
var query = await db.LastQuery.FirstOrDefaultAsync(w => w.ChatId == chatId);
if (query == null)
{
return(null);
}
var turnsList = (await db.ViewsTurns
.Where(w => w.ChatId == int.Parse(chatId))
.ToListAsync())
.Take(settings.CountMessage)
.ToList();
var list = await db.WeaponList
.Where(w => turnsList.Select(s => s.WeaponListId).Contains(w.Id))
.ToListAsync();
await DeleteItems(chatId);
return(list);
}
}
public async Task AddUser(Models.Chat chat)
{
using (var db = new DbNorthwind())
{
await db.InsertWithInt32IdentityAsync(chat);
}
}
async Task SetQueryNull(string chatId)
{
using (var db = new DbNorthwind())
{
await db.LastQuery.Where(w => w.ChatId == chatId).Set(s => s.Query, String.Empty).UpdateAsync();
}
}
public override async Task Execute(CallbackQuery message, TelegramBotClient botClient,
Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Message.Chat.Id;
int.TryParse(message.Data.Replace("Показать лоты", ""), out int userVkId);
using var db = new DbNorthwind();
await db.ViewsTurns.Where(w => w.ChatId == chatId).DeleteAsync();
var listWeapon = await db.WeaponList.Where(w => w.UserId == userVkId).ToListAsync();
var viewsTurns = new List <ViewsTurns>();
listWeapon.ForEach(x => viewsTurns.Add(new ViewsTurns()
{
WeaponListId = x.Id,
ChatId = (int)chatId,
}));
ReplyKeyboardMarkup keyboard4 = new[]
{
new [] { "Показать результат", "Поиск по категориям" },
new[] { "Помощь", "Вкл.авто уведомление", }
};
keyboard4.ResizeKeyboard = true;
await botClient.SendTextMessageAsync(chatId, "Нажмите 'Показать результат', чтобы увидеть лоты пользователя", replyMarkup : keyboard4);
db.BulkCopy(viewsTurns);
}
async Task <string> GetLatQueryText(string chatId)
{
using (var db = new DbNorthwind())
{
return((await db.LastQuery
.FirstOrDefaultAsync(s => s.ChatId == chatId)).Query);
}
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
await using var db = new DbNorthwind();
Console.WriteLine(message.Chat.Id);
await db.Chats.Where(w => w.ChatId == message.Chat.Id.ToString())
.Set(s => s.CategorySearch, "")
.UpdateAsync();
await botClient.SendTextMessageAsync(message.Chat.Id, "Все категории включены");
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Chat.Id;
using var db = new DbNorthwind();
var count = await db.WeaponList.CountAsync();
await botClient.SendTextMessageAsync(chatId, $"Количество {count}", parseMode : Telegram.Bot.Types.Enums.ParseMode.Markdown);
}
public async Task <string> Get()
{
await using var db = new DbNorthwind();
var count = await db.WeaponList.Where(w => w.FirstComment == "" || w.Text == "").CountAsync();
var url = await db.WeaponList.Where(w => w.FileId == null).CountAsync();
return(count + Environment.NewLine + url);
}
public override async Task Execute(CallbackQuery message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Message.Chat.Id;
var settings = configuration.GetSection("Settings").Get <Settings>();
using var db = new DbNorthwind();
int.TryParse(message.Data.Replace("Проверить", ""), out int userVkId);
var listWeapon = await db.WeaponList.Where(w => w.UserId == userVkId).ToListAsync();
if (listWeapon.Count > 1)
{
var kidal = await db.Kidals.FirstOrDefaultAsync(f => f.VkId == listWeapon[0].UserId);
var list = new InlineKeyboardMarkup(new List <InlineKeyboardButton>()
{
InlineKeyboardButton.WithCallbackData("Показать лоты", "Показать лоты" + listWeapon[0].UserId)
});
if (kidal == null)
{
await botClient.SendTextMessageAsync(chatId, "Информация по аккаунту: "
+ Environment.NewLine + $"Профиль: vk.com/id{listWeapon[0].UserId}"
+ Environment.NewLine + "В списках ненадежных продавцов не найден"
+ Environment.NewLine + Environment.NewLine + $"Чтобы посмотреть все лоты данного человека нажмите на кнопку ({listWeapon.Count} лотов)", replyMarkup : list);
}
else
{
await botClient.SendTextMessageAsync(chatId, "Информация по аккаунту: "
+ Environment.NewLine + $"Профиль: vk.com/id{listWeapon[0].UserId}"
+ Environment.NewLine + $"❗Найден в списке мошенников: https://vk.com/topic-{kidal.GroupId}_{kidal.TopicId}?post={kidal.PostId}"
+ Environment.NewLine + Environment.NewLine + $"Чтобы посмотреть все лоты данного человека нажмите на кнопку ({listWeapon.Count} лотов)", replyMarkup : list);
}
}
else
{
var kidal = await db.Kidals.FirstOrDefaultAsync(f => f.VkId == listWeapon[0].UserId);
if (kidal == null)
{
await botClient.SendTextMessageAsync(chatId, "Информация по аккаунту: "
+ Environment.NewLine + $"Профиль: vk.com/id{listWeapon[0].UserId}"
+ Environment.NewLine + "В списках ненадежных продавцов не найден"
+ Environment.NewLine + Environment.NewLine + "❗Больше лотов у данного продавца не найдено");
}
else
{
await botClient.SendTextMessageAsync(chatId, "Информация по аккаунту: "
+ Environment.NewLine + $"Профиль: vk.com/id{listWeapon[0].UserId}"
+ Environment.NewLine + $"❗Найден в списке мошенников: https://vk.com/topic-{kidal.GroupId}_{kidal.TopicId}?post={kidal.PostId}"
+ Environment.NewLine + Environment.NewLine + "❗Больше лотов у данного продавца не найдено");
}
}
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
using (var db = new DbNorthwind())
{
await db.Chats.Where(w => w.ChatId == message.Chat.Id.ToString())
.Set(s => s.CategorySearch, Name)
.UpdateAsync();
await botClient.SendTextMessageAsync(message.Chat.Id, $"Категория '{Name}' включена");
}
}
static void Main(string[] args)
{
Write("Input format of serialization (json/xml/binary):");
string serializationFormat = ReadLine();
using (DbNorthwind db = new DbNorthwind()){
List <Category> categories = db.Categories.ToList();
List <Product> products = db.Products.ToList();
switch (serializationFormat)
{
case ("json"):
Newtonsoft.Json.JsonSerializer jsonSerializer = new Newtonsoft.Json.JsonSerializer();
using (StreamWriter file = File.CreateText("Categories.json"))
{
jsonSerializer.Serialize(file, categories);
}
using (StreamWriter file = File.CreateText("Products.json"))
{
jsonSerializer.Serialize(file, products);
}
break;
case ("xml"):
XmlSerializer xmlSerializerCategories = new XmlSerializer(typeof(List <Category>));
XmlSerializer xmlSerializerProducts = new XmlSerializer(typeof(List <Product>));
using (FileStream file = File.Create("Categories.xml"))
{
xmlSerializerCategories.Serialize(file, categories);
}
using (FileStream file = File.Create("Products.xml"))
{
xmlSerializerProducts.Serialize(file, products);
}
break;
case ("binary"):
BinaryFormatter binarySerializer = new BinaryFormatter();
using (FileStream file = File.Create("Categories.dat"))
{
binarySerializer.Serialize(file, categories);
}
using (FileStream file = File.Create("Products.dat"))
{
binarySerializer.Serialize(file, products);
}
break;
default:
WriteLine("Unknown format of serialization");
break;
}
}
}
public void RunZ2()
{
//Z2.1
Console.WriteLine("Z2.1");
using (var db = new DbNorthwind())
{
var query = db.Products.LoadWith(c => c.Categories).LoadWith(s => s.Suppliers).Select(p =>
new { p.ProductID, p.ProductName, p.Categories.CategoryName, p.Suppliers.ContactName });
foreach (var p in query)
{
Console.WriteLine("{0}|{1}|{2}|{3}", p.ProductID, p.ProductName, p.CategoryName, p.ContactName);
}
}
//Z2.2
Console.WriteLine("Z2.2");
using (var db = new DbNorthwind())
{
var query = db.Employees.Select(e =>
new { e.EmployeeID, EmployeeName = e.FirstName + e.LastName, e.Region });
foreach (var e in query)
{
Console.WriteLine("{0}|{1}|{2}", e.EmployeeID, e.EmployeeName, e.Region);
}
}
//Z2.3
Console.WriteLine("Z2.3");
using (var db = new DbNorthwind())
{
var query = db.Employees.GroupBy(e => e.Region).Select(g =>
new { GroupName = g.Key != null ? g.Key : "No region", Count = g.Count() });
foreach (var e in query)
{
Console.WriteLine("{0}|{1}", e.GroupName, e.Count);
}
}
//Z2.4
Console.WriteLine("Z2.4");
using (var db = new DbNorthwind())
{
var q = db.Orders.LoadWith(e => e.Employees).LoadWith(s => s.Shippers).GroupBy(g =>
new
{
g.EmployeeID,
g.Employees.FirstName,
g.Employees.LastName,
g.Shippers.CompanyName
}).OrderBy(r => r.Key.FirstName);
foreach (var e in q)
{
Console.WriteLine("{0}|{1}|{2}", e.Key.FirstName + " " + e.Key.LastName, e.Key.CompanyName, e.Count());
}
}
}
public void AddProducts()
{
TransactionScope scope = new TransactionScope();
using (var db = new DbNorthwind(providerName, connectionString))
{
List <Product> productList = GetProductList();
foreach (var product in productList)
{
Category category = db.Categories.FirstOrDefault(c => c.Name == product.Category.Name);
Supplier supplier = db.Suppliers.FirstOrDefault(s => s.CompanyName == product.Supplier.CompanyName);
if (category == null)
{
//если такой категории не существует то заносим ее в бд и получаем её id
product.CategoryId = db.InsertWithInt32Identity(new Category {
Name = product.Category.Name
});
}
else
{
product.CategoryId = category.Id;
}
if (supplier == null)
{
product.SupplierId = db.InsertWithIdentity(new Supplier {
CompanyName = product.Supplier.CompanyName
}).ToString();
}
else
{
product.SupplierId = supplier.Id;
}
}
db.BulkCopy(productList);
#region Demonstration
foreach (var product in productList)
{
foreach (var item in db.Products.LoadWith(p => p.Supplier).LoadWith(p => p.Category).Where(p => p.Name == product.Name))
{
Console.WriteLine($" Product name: {item.Name} Supplier name: {item.Supplier.CompanyName} Category name: {item.Category.Name}");
}
}
#endregion
scope.Dispose();
}
}
async Task AddInQueryAsync(string chatId, string query)
{
using (var db = new DbNorthwind())
{
await db.InsertAsync(new Querys()
{
Date = DateTime.Now,
Query = query,
ChatId = chatId
});
}
}
static void GetData()
{
using (var db = new DbNorthwind())
{
var query = db.Product.Where(o => o.Id > 25).OrderByDescending(o => o.ProductName)
.ToList();
foreach (var item in query.ToList())
{
Console.WriteLine(item.ProductName);
}
}
}
public DbNorthwind GetDbNorthwind()
{
var db = new DbNorthwind();
//Have to change our table names because Linq2Db for Sqlite doesn't use the schema name
ChangeTableNames(db, _nameSpaces);
//build up the database from our script
foreach (var cmd in _buildCommands)
{
db.Execute(cmd);
}
return(db);
}
public void GetProductListWithCategoryAndSupllier()
{
using (var db = new DbNorthwind(providerName, connectionString))
{
var products = db.Products.LoadWith(p => p.Category).LoadWith(p => p.Supplier);
#region Demonstration
foreach (var product in products)
{
Console.WriteLine($" Id: {product.Id} ProductName: {product.Name} Category: {product.Category?.Name} SupplierName: {product.Supplier?.ContactName}");
}
#endregion
}
}
public void ReplaceProduct()
{
TransactionScope scope = new TransactionScope();
using (var db = new DbNorthwind(providerName, connectionString))
{
var orderDetailsWithOutstandingOrder = db.OrderDetails.LoadWith(od => od.Order).LoadWith(od => od.Product).Where(od => od.Order.ShippedDate == null);
#region Demonstration
Console.WriteLine("Before the replacement");
foreach (var orderDetail in orderDetailsWithOutstandingOrder.Take(10))
{
Console.WriteLine($"Product name: {orderDetail.Product.Name} Order Id: {orderDetail.OrderId}");
}
Console.WriteLine();
#endregion
var orderDetails = orderDetailsWithOutstandingOrder
.Update(od => new OrderDetail
{
ProductId = db.Products.First(p => p.CategoryId == od.Product.CategoryId && p.Id > od.ProductId) != null
? db.Products.First(p => p.CategoryId == od.Product.CategoryId && p.Id > od.ProductId).Id
: db.Products.First(p => p.CategoryId == od.Product.CategoryId).Id
});
#region Demonstration
Console.WriteLine("After the replacement");
foreach (var orderDetail in orderDetailsWithOutstandingOrder.Take(10))
{
Console.WriteLine($"Product name: {orderDetail.Product.Name} Order Id: {orderDetail.OrderId}");
}
Console.WriteLine();
#endregion
scope.Dispose();
}
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Chat.Id;
await using var db = new DbNorthwind();
var countLots = await db.WeaponList.CountAsync();
var countKidals = await db.Kidals.CountAsync();
await botClient.SendTextMessageAsync(chatId, "Бот для поиска по страйкбольным барахолкам."
+ Environment.NewLine + "Введи слово и бот найдет. Для сложного поиска можно использовать «и» или «или». Например: АК и Тюмень."
+ Environment.NewLine + Environment.NewLine + " В случае замечаний/предложений для связи с администрацией напишите: /report текст сообщения"
+ Environment.NewLine + " Чтобы проверить человека в списках мошенников: /check ссылка на вк"
+ Environment.NewLine + Environment.NewLine + $"✅ Количество лотов на данный момент : {countLots}"
+ Environment.NewLine + $"❗ Количество мошенников в базе : {countKidals}", parseMode : Telegram.Bot.Types.Enums.ParseMode.Markdown);
}
public async Task DeleteItems(string chatId)
{
using (var db = new DbNorthwind())
{
var turnsList = (await db.ViewsTurns
.Where(w => w.ChatId == int.Parse(chatId))
.ToListAsync())
.Take(settings.CountMessage)
.ToList();
foreach (var item in turnsList)
{
await db.ViewsTurns
.Where(w => w.ChatId == int.Parse(chatId) && w.WeaponListId == item.WeaponListId)
.DeleteAsync();
}
}
}
async Task AddQuery(string chatId, string query, List <WeaponList> reslist)
{
using (var db = new DbNorthwind())
{
await db.ViewsTurns
.Where(w => w.ChatId == int.Parse(chatId))
.DeleteAsync();
var list = reslist
.OrderByDescending(w => w.StartTime)
.Take(100)
.ToList();
list = list.GroupBy(f => f.Text)
.Select(g => g.First()).Take(50).ToList();
var turnsList = new List <ViewsTurns>();
foreach (var item in list)
{
turnsList.Add(new ViewsTurns()
{
ChatId = int.Parse(chatId),
WeaponListId = item.Id,
});
}
db.ViewsTurns.BulkCopy(turnsList);
var queryClass = await db.LastQuery.FirstOrDefaultAsync(w => w.ChatId == chatId);
if (queryClass == null)
{
await db.InsertAsync(new LastQuery { ChatId = chatId, Query = query });
}
else
{
await db.LastQuery
.Where(w => w.Id == queryClass.Id)
.Set(s => s.Query, query)
.UpdateAsync();
}
}
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var settings = configuration.GetSection("Settings").Get <Settings>();
if (message.Chat.Id != settings.AdminChatId)
{
return;
}
await using (var db = new DbNorthwind())
{
var users = await db.LastQuery.Select(s => s.ChatId).ToListAsync();
users.ForEach(f =>
{
botClient.SendTextMessageAsync(long.Parse(f), message.Text.Replace(@"/notify ", ""));
});
}
}
public void GetEmployeesWithRegion()
{
using (var db = new DbNorthwind(providerName, connectionString))
{
var employees = from employee in db.Employees
join employeeTerritory in db.EmployeeTerritories on employee.Id equals employeeTerritory.EmployeeId
join territory in db.Territories on employeeTerritory.TerritoryId equals territory.Id
join region in db.Regions on territory.RegionId equals region.Id
select new { employee.Id, employee.FirstName, employee.LastName, region.RegionDescription };
#region Demonstration
foreach (var employee in employees.Distinct())
{
Console.WriteLine($"Id: {employee.Id} Name: {employee.FirstName} {employee.LastName} Region: {employee.RegionDescription}");
}
#endregion
}
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Chat.Id;
using (var db = new DbNorthwind())
{
if (await db.Chats.FirstOrDefaultAsync(w => w.ChatId == chatId.ToString()) == null)
{
await AddUser(new Models.Chat {
ChatId = chatId.ToString(), FirstName = message.From.FirstName, LastName = message.From.LastName, UserName = message.From.Username
});
}
}
ReplyKeyboardMarkup ReplyKeyboard = new[]
{
new [] { "Помощь", "Поиск по категориям" }
};
ReplyKeyboard.ResizeKeyboard = true;
await botClient.SendTextMessageAsync(chatId, "Приветствую! Введите слово для поиска или нажмите \"Помощь\"", replyMarkup : ReplyKeyboard);
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = (int)message.Chat.Id;
var adminId = configuration.GetSection("Settings").Get <Settings>().AdminChatId;
if (chatId != adminId)
{
return;
}
var text = "";
using (var db = new DbNorthwind())
{
int countInDay = 0, countInWeek = 0, countInMoth = 0, uniqueUsersInDay = 0, uniqueUsersInWeek = 0, uniqueUsersInMoth = 0;
var list = await db.Querys.Where(w => w.Date > DateTime.Now.AddDays(-30)).ToListAsync();
countInDay = list.Where(w => w.Date.Day == DateTime.Now.Day).ToList().Count;
countInWeek = list.Where(w => w.Date > DateTime.Now.AddDays(-7)).ToList().Count;
countInMoth = list.Where(w => w.Date > DateTime.Now.AddDays(-30)).ToList().Count;
uniqueUsersInDay = list.Where(w => w.Date.Day == DateTime.Now.Day).Select(s => s.ChatId).Distinct().ToList().Count;
uniqueUsersInWeek = list.Where(w => w.Date > DateTime.Now.AddDays(-7)).Select(s => s.ChatId).Distinct().ToList().Count;
uniqueUsersInMoth = list.Where(w => w.Date > DateTime.Now.AddDays(-30)).Select(s => s.ChatId).Distinct().ToList().Count;
var countIsWatching = await db.LastQuery.Where(f => f.IsWatching == 1).CountAsync();
text += $"Количество запросов: {countInDay}/{countInWeek}/{countInMoth}";
text += Environment.NewLine + Environment.NewLine;
text += $"Уникальных пользователей: {uniqueUsersInDay}/{uniqueUsersInWeek}/{uniqueUsersInMoth}";
text += Environment.NewLine + Environment.NewLine;
text += $"Подписаны на уведомление: {countIsWatching}";
await botClient.SendTextMessageAsync(adminId, text);
}
}
public void GetShipperListByEmployee()
{
using (var db = new DbNorthwind(providerName, connectionString))
{
var orders = from order in db.Orders.LoadWith(o => o.Employee).LoadWith(o => o.Shipper)
group order by new { order.Employee.FirstName, order.Employee.LastName };
#region Demonstration
foreach (var order in orders)
{
Console.WriteLine($"{order.Key.FirstName} {order.Key.LastName}");
foreach (var item in order)
{
Console.WriteLine($"Company name: {item.Shipper.CompanyName} ShipCountry {item.ShipCountry} ShipCity {item.ShipCity} ShipAddress {item.ShipAddress}");
}
}
#endregion
}
}
public override async Task Execute(Message message, TelegramBotClient botClient, Microsoft.Extensions.Configuration.IConfiguration configuration)
{
var chatId = message.Chat.Id;
using (var db = new DbNorthwind())
{
ReplyKeyboardMarkup ReplyKeyboard = new[]
{
new[] { "Откл. авто уведомление", "Помощь" },
};
ReplyKeyboard.ResizeKeyboard = true;
await db.LastQuery
.Where(w => w.ChatId == chatId.ToString())
.Set(s => s.IsWatching, 1)
.Set(s => s.StartWatchTime, DateTime.Now)
.UpdateAsync();
var query = (await db.LastQuery
.FirstOrDefaultAsync(s => s.ChatId == chatId.ToString())).Query;
await botClient.SendTextMessageAsync(chatId, $"Отслеживание новых объявлений по запросу \"{query}\" успешно включено", replyMarkup : ReplyKeyboard);
}
}
public void GetEmployeeCountByRegion()
{
using (var db = new DbNorthwind(providerName, connectionString))
{
var employees = from employee in db.Employees
join employeeTerritory in db.EmployeeTerritories on employee.Id equals employeeTerritory.EmployeeId
join territory in db.Territories on employeeTerritory.TerritoryId equals territory.Id
join region in db.Regions on territory.RegionId equals region.Id
select new { RegionName = region.RegionDescription, EmployeesId = employee.Id };
var regionStatistic = from regStat in employees.Distinct()
group regStat.EmployeesId by regStat.RegionName into groupResult
select new { Region = groupResult.Key, EmployeesCount = groupResult.Count() };
#region Demonstration
foreach (var employee in regionStatistic)
{
Console.WriteLine($"Region: { employee.Region} EmployeesCount { employee.EmployeesCount} ");
}
#endregion
}
}
public void MoveProducts()
{
TransactionScope scope = new TransactionScope();
using (var db = new DbNorthwind(providerName, connectionString))
{
int sourceCategoryId = 1;
int newCAtegoryId = 2;
#region Demonstration
//начальное количество продуктов под категорией newCAtegoryId
Console.WriteLine(db.Products.Where(p => p.CategoryId == newCAtegoryId).Count());
#endregion
db.Products.Where(p => p.CategoryId == sourceCategoryId).Set(p => p.CategoryId, newCAtegoryId).Update();
#region Demonstration
// количество продуктов под категорией newCAtegoryId после запроса
Console.WriteLine(db.Products.Where(p => p.CategoryId == newCAtegoryId).Count());
#endregion
scope.Dispose();
}
}
