Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- var TelegramBot = require('node-telegram-bot-api');
- const mysql = require('mysql2/promise');
- const moment = require('moment')
- const ejs = require('ejs');
- const diff = require('deep-object-diff').detailedDiff;
- var token = '781591989:AAEhXXunW44d5pHwnLJbJyP-ybE3jRetMqE';
- var bot = new TelegramBot(token, { polling: true });
- const Html2Pdf = require('./HtmlToPdf');
- /** @type {Map<number, string>} */
- const chatStatuses = new Map();
- const chatNaklads = new Map();
- const chatItems = new Map();
- const userStatus = {
- NEW: 'new',
- CREATING_NAKLAD: {
- NEW: 'creatingNaklad_new',
- GOT_BILL: 'nakladHasBill',
- GOT_NAMES: 'nakladHasNames',
- GOT_AMOUNTS: 'nakladHasAmount',
- GOT_PRICES: 'nakladHasPrices',
- GOT_SUMM: 'nakladHasSumm',
- GOT_WAREHOUSE: 'nakladHasWarehouse'
- },
- PRINT: {
- BILL: 'printBill',
- REESTR: 'printReestr',
- NAKLAD: 'printNaklad',
- ORDER: 'printOrder'
- }
- }
- let connection;
- async function initDatabase() {
- connection = await mysql.createConnection({
- host: 'localhost',
- user: 'root',
- password: 'Password123',
- database: 'compbotdb',
- dateStrings: true
- });
- }
- async function registration(id, name, pass, role) {
- const quer = await connection.query(`
- INSERT INTO users (login,password_,role) VALUES('${name}','${pass}','${role}');
- `);
- if(quer[0].affectedRows>0){
- return true;
- }
- return false;
- }
- async function billHistory(count) {
- if(count>=0){
- const quer = await connection.query(`
- SELECT bill.id, bill.bill_date, distributors.name, bill.summ, bill.payment FROM bill
- LEFT JOIN distributors ON bill.distributor = distributors.id
- ORDER BY bill.id
- LIMIT 5 OFFSET ${count}
- `);
- return quer && quer[0];
- }
- return false;
- } //список счет-фактур
- //смаржить все результаты в 1 массив
- async function getBill(Id){
- const quer = await connection.query(`
- SELECT catalog_prod.name, catalog_prod.measure, tovari_bills.amount, tovari_bills.price,
- (tovari_bills.price - (tovari_bills.price * 20)/100) priceNoPDV,
- (bill.id) billId, (bill.bill_date) billDate,
- (distributors.name) distName, (distributors.address) distAdress,
- (distributors.ident_code) distCode, (distributors.t_number) distTel, (distributors.e_mail) distEmail
- FROM bill
- LEFT JOIN distributors ON bill.distributor = distributors.id
- LEFT JOIN tovari_bills ON bill.id = tovari_bills.id_bill
- LEFT JOIN catalog_prod ON tovari_bills.id_tovar = catalog_prod.id
- WHERE bill.id = ${Id}
- `);
- const quer1 = await connection.query(`
- SELECT (bill.summ) summPDV,(bill.summ - (bill.summ*20)/100) summNoPDV
- FROM bill
- LEFT JOIN tovari_bills ON bill.id = tovari_bills.id_bill
- WHERE bill.id = ${Id}
- `)
- const bills = quer[0];
- const summs = quer1[0];
- const billDate = moment(bills.billDate).format('YYYY-MM-DD');
- const foundBill = bills[0];
- const foundSumm = summs[0];
- const bill = {
- billId: Id,
- billDate: billDate,
- distName: foundBill.distName,
- distAdress: foundBill.distAdress,
- distCode: foundBill.distCode,
- distTel: foundBill.distTel,
- distEmail: foundBill.distEmail,
- summNoPDV: foundSumm.summNoPDV,
- summPDV: foundSumm.summPDV
- };
- const items = bills.map(i => {
- return {
- name: i.name,
- measure: i.measure,
- amount: i.amount,
- priceNoPDV: i.priceNoPDV,
- price: i.price
- }
- });
- bill.items = items;
- return bill;
- }
- async function billConfirmation(id) {
- const billID = id;
- const quer = await connection.execute(`UPDATE bill SET bill.confirm = 1 WHERE bill.id = ${billID}`);
- console.dir(quer);
- if(quer[0].changedRows>0){
- return true;
- }else{
- return false;
- }
- } //подтверждение~
- //common (1-2)
- async function countRowsNaklad(){
- const countOfNaklad = await connection.execute(`
- SELECT COUNT(id) AS countOfRows FROM naklad
- `);
- return countOfNaklad[0][0].countOfRows;
- }
- async function countRowsOrder(){
- const countOfOrders = await connection.execute(`
- SELECT COUNT(id) AS countOfRows FROM orders
- `);
- return countOfOrders[0][0].countOfRows;
- }
- async function countRowsBills(){
- const countOfBills = await connection.execute(`
- SELECT COUNT(id) AS countOfRows FROM bill
- `);
- return countOfBills[0][0].countOfRows;
- }
- async function countRowsCatalog(){
- const countOfGoods = await connection.execute(`
- SELECT COUNT (id) AS countOfRows FROM catalog_prod
- `);
- return countOfGoods[0][0].countOfRows;
- }
- async function viewCatalog(count) {
- if(count>=0){
- const resp = await connection.execute(`
- SELECT * FROM catalog_prod LIMIT 5 OFFSET ${count};
- `);
- return resp && resp[0];
- }
- return false;
- }
- async function viewGood(id) {
- const goodID = id;
- const quer = await connection.execute(`
- SELECT * FROM catalog_prod WHERE catalog_prod.id = ${goodID}`);
- const res = quer[0];
- return res && res[0];
- }
- async function editCatalog(id, Col, Val) {
- const quer = await connection.execute(`
- UPDATE catalog_prod SET catalog_prod.${Col} = ${sqlValue(Val)} WHERE id = ${id};
- `);
- if(quer[0].changedRows>0){
- return true;
- }
- return false;
- }
- async function deleteGood(id) {
- const goodId = id;
- const quer = await connection.execute(`
- DELETE FROM catalog_prod WHERE id = ${goodId};
- `);
- if(quer[0].affectedRows>0){
- return true;
- }
- return false;
- }
- //common (1-3)
- //для менеджера
- async function checkOrderBill(orderId, billId) {
- const getOrder = await connection.execute(`
- SELECT (orders.id) orderId, (orders.summ) orderSumm, (orders.order_date) orderDate,
- (tovar_order.amount) orderAmount, (tovar_order.price) orderPrice, (tovar_order.id_tovar) orderGood,
- (catalog_prod.name) orderGoods
- FROM orders
- LEFT JOIN tovar_order ON orders.id = tovar_order.id_order
- LEFT JOIN catalog_prod ON tovar_order.id_tovar = catalog_prod.id
- WHERE orders.id = ${orderId}
- ORDER BY tovar_order.id_tovar`);
- const getBill = await connection.execute(`
- SELECT (bill.id) billId, (bill.summ) billSumm, (bill.bill_date) billDate,
- (tovari_bills.amount) billAmount, (tovari_bills.price) billPrice, (tovari_bills.id_tovar) billGood,
- (catalog_prod.name) billGoods
- FROM bill
- LEFT JOIN tovari_bills ON bill.id = tovari_bills.id_bill
- LEFT JOIN catalog_prod ON tovari_bills.id_tovar = catalog_prod.id
- WHERE bill.id = ${billId}
- ORDER BY tovari_bills.id_tovar`);
- const foundOrder = getOrder[0];
- const tempOrder = foundOrder[0];
- const order = {
- id: orderId,
- date: tempOrder.orderDate,
- summ: tempOrder.orderSumm
- }
- const orderGoods = foundOrder.map(i=>{
- return{
- name: i.orderGoods,
- amount: i.orderAmount,
- price: i.orderPrice
- }
- });
- order.orderGoods = orderGoods;
- const foundBill = getBill[0];
- const tempBill = foundBill[0];
- const bill = {
- id: billId,
- date: tempBill.billDate,
- summ: tempBill.billSumm
- };
- const billGoods = foundBill.map(i=>{
- return{
- name:i.billGoods,
- amount: i.billAmount,
- price: i.billPrice
- }
- });
- bill.billGoods = billGoods;
- const differs = {};
- const diffGoods = diff(bill.billGoods,order.orderGoods);
- differs.goods = Object.entries(diffGoods.updated);
- const difference = differs.goods.map(([i, obj]) => {
- return {
- ...billGoods[i],
- updated: obj
- };
- });
- difference.summ = bill.summ - order.summ;
- console.dir(difference);
- console.log(difference.length);
- return difference;
- }
- async function getBills(name) {
- const distr = name;
- const res = await connection.execute(`
- SELECT bill.id, bill.bill_date, distributors.name, bill.summ, bill.payment FROM bill
- LEFT JOIN distributors ON bill.distributor = distributors.id
- WHERE distributors.name LIKE '%${distr}%';`);
- return res[0];
- } //выборка по счетам
- async function getOrder({orderId, count}){
- if(orderId){
- const res = await connection.execute(`
- SELECT orders.order_date, orders.summ,
- (distributors.name) distName,
- tovar_order.amount, tovar_order.price, (tovar_order.amount * tovar_order.price) summTov,
- catalog_prod.name, catalog_prod.measure
- FROM orders
- LEFT JOIN distributors ON orders.distributor = distributors.id
- LEFT JOIN tovar_order ON orders.id = tovar_order.id_order
- LEFT JOIN catalog_prod ON tovar_order.id_tovar = catalog_prod.id
- WHERE orders.id = ${orderId}
- `);
- const orderDate = moment(res.order_date).format('YYYY-MM-DD');
- const foundOrder = res[0];
- const tempOrder = foundOrder[0];
- const order = {
- id: orderId,
- date: orderDate,
- summ: tempOrder.summ,
- distName: tempOrder.distName
- };
- const goods = foundOrder.map(i=>{
- return{
- name: i.name,
- measure: i.measure,
- amount: i.amount,
- price: i.price,
- summTov: i.summTov
- }
- });
- order.goods = goods;
- console.dir(order);
- return order;
- }
- if(count != undefined){
- if(count >= 0){
- const res = await connection.execute(`
- SELECT * FROM orders LIMIT 5 OFFSET ${count}
- `);
- return res[0];
- }
- return false;
- }
- }
- async function getBillConfirmed() {
- const res = await connection.execute(`
- SELECT bill.id, bill.bill_date, distributors.name, bill.summ, bill.payment FROM bill
- LEFT JOIN distributors ON bill.distributor = distributors.id
- WHERE bill.confirm = 1;
- `);
- return res;
- } //выборку по оплач. счетам
- async function getNaklad({ nakladId, count }){
- if(nakladId){
- const res = await connection.execute(`
- SELECT (naklad.date_) nakladDate, naklad.bill_id, naklad.warehouse, naklad.summ, (naklad.summ - ((naklad.summ*20)/100)) summNoPDS,
- naklad_order.amount, naklad_order.price, (naklad_order.price*naklad_order.amount) summTov,naklad_order.id_tovar,
- (distributors.name) distName, distributors.address, distributors.ident_code, distributors.t_number, distributors.e_mail,
- catalog_prod.name, catalog_prod.measure FROM naklad
- LEFT JOIN bill ON naklad.bill_id = bill.id
- LEFT JOIN distributors ON bill.distributor = distributors.id
- LEFT JOIN naklad_order ON naklad.id = naklad_order.id_naklad
- LEFT JOIN catalog_prod ON naklad_order.id_tovar = catalog_prod.id
- WHERE naklad.id = ${nakladId};
- `);
- const nakladDate = moment(res.nakladDate).format('YYYY-MM-DD');
- const foundNaklad = res[0];
- const foundNaklad2 = foundNaklad[0];
- const naklad = {
- id : nakladId,
- date : nakladDate,
- warehouse: foundNaklad2.warehouse,
- summ: foundNaklad2.summ,
- summNoPDS: foundNaklad2.summNoPDS,
- distName: foundNaklad2.distName,
- distAdress: foundNaklad2.address,
- distCode: foundNaklad2.ident_code,
- distTel: foundNaklad2.t_number,
- distEmail: foundNaklad2.e_mail,
- }
- const goods = foundNaklad.map(i=>{
- return{
- id: i.id_tovar,
- name: i.name,
- measure: i.measure,
- amount: i.amount,
- price: i.price,
- summTov: i.summTov
- }
- });
- naklad.goods = goods;
- return naklad;
- }
- if(count!=undefined){
- if(count>=0){
- const res = await connection.execute(`
- SELECT * FROM naklad LIMIT 5 OFFSET ${count};
- `);
- console.log('da');
- return res[0];
- }
- console.log('net');
- return false;
- }
- } //получение накладной
- async function sendBillForConfirm(id) {
- const resp = await connection.execute(`
- SELECT password_ FROM users WHERE users.role = 'lead';
- `);
- return resp[0];
- } //отправить счет на проверку
- //для кладовщика
- async function getGood({ goodId, goodName }){
- if(goodId){
- const quer = await connection.execute(`
- SELECT * FROM catalog_prod
- WHERE id = ${goodId}`);
- return quer[0];
- }
- if(goodName){
- const quer = await connection.execute(`
- SELECT * FROM catalog_prod
- WHERE name = '${goodName}'`);
- return quer[0];
- }
- }
- async function createNaklad(naklad) {
- const insertNaklad = await connection.execute(`
- INSERT INTO naklad (date_,bill_id,warehouse,summ)
- VALUES('${naklad.date}',${naklad.billId},${naklad.warehouse}, ${naklad.summ})`);
- //console.dir(insertNaklad);
- for(const item of naklad.goods){
- const getGoods = await getGood({goodName: item.name});
- if(getGoods.length > 0){
- const insertNakladTovar = await connection.execute(`
- INSERT INTO naklad_order (id_naklad, id_tovar, amount, price)
- VALUES (${insertNaklad[0].insertId}, ${getGoods[0].id}, ${item.amount}, ${item.price})`);
- if(insertNakladTovar[0].changedRows > 0){
- return true;
- }
- }else{
- const insertTovar = await connection.execute(`
- INSERT INTO catalog_prod (name, amount, price) VALUES ('${item.name}' ,${item.amount},${item.price})`);
- const insertNakladTovar = await connection.execute(`
- INSERT INTO naklad_order (id_naklad, id_tovar, amount, price)
- VALUES (${insertNaklad[0].insertId}, ${insertTovar[0].insertId}, ${item.amount}, ${item.price})`);
- if(insertNakladTovar[0].changedRows > 0){
- return true;
- }
- }
- }
- if(insertNaklad[0].changedRows > 0){
- return true;
- }
- return true;
- } //накладная
- async function createVedomostPostavok(date) {
- const resp = await connection.execute(`
- SELECT naklad.id, naklad.date_ , naklad.warehouse, naklad_order.amount, catalog_prod.name FROM naklad_order
- LEFT JOIN naklad ON naklad_order.id_naklad = naklad.id
- LEFT JOIN catalog_prod ON naklad_order.id_tovar = catalog_prod.id
- WHERE naklad.date_ = '${date}'
- `);
- return resp[0];
- } //сформировать ведомость поcтавок для менеджера
- async function getUnConfirmedBill(id){
- if(!id){
- const res = await connection.execute(`
- SELECT bill.id, catalog_prod.name, tovari_bills.amount,bill.summ,bill.bill_date, distributors.name AS dist_name FROM tovari_bills
- LEFT JOIN catalog_prod ON tovari_bills.id_tovar = catalog_prod.id
- LEFT JOIN bill ON tovari_bills.id_bill = bill.id
- LEFT JOIN distributors ON bill.distributor = distributors.id
- WHERE bill.confirm = 0;
- `);
- return res[0];
- }else{
- const resp = await connection.execute(`
- SELECT bill.id, catalog_prod.name, tovari_bills.amount,bill.summ,bill.bill_date, distributors.name AS dist_name FROM tovari_bills
- LEFT JOIN catalog_prod ON tovari_bills.id_tovar = catalog_prod.id
- LEFT JOIN bill ON tovari_bills.id_bill = bill.id
- LEFT JOIN distributors ON bill.distributor = distributors.id
- WHERE bill.id = ${id}
- `);
- return resp[0];
- }
- }
- // /n [billId] [warehouseId] [summ] [name,price,amount name,price,amount name,price,amount...]
- async function createNakladBotFunc(msg, match) {
- const chatId = msg.chat.id;
- const msgDate = msg.date;
- const nakladDate = moment.unix(msgDate).format("YYYY-MM-DD");
- const tempItems = {};
- tempItems.date = nakladDate;
- tempItems.billId = Number(match[1]);
- tempItems.warehouse = Number(match[2]);
- tempItems.summ = Number(match[3]);
- tempItems.goods = [];
- const goods = match[4].split(' ').map(i => i.split(','));
- for(const item of goods) {
- tempItems.goods.push({
- name: item[0],
- price: Number(item[1]),
- amount: Number(item[2]),
- });
- }
- const res = await createNaklad(tempItems);
- if(res == true){
- return true;
- }
- return false;
- }
- // createBill [orderId] [distName] [summ] [name,price,amount name,price,amount name,price,amount...]
- async function createBillBotFunc(msg, match){
- const billDate = moment.unix(msg.date).format('YYYY-MM-DD');
- const tempBill={
- date: billDate,
- orderId: Number(match[1]),
- distName: match[2],
- summ: Number(match[3]),
- };
- tempBill.goods = [];
- const goods = match[4].split(' ').map(i => i.split(','));
- for(const item of goods) {
- tempBill.goods.push({
- name: item[0],
- price: Number(item[1]),
- amount: Number(item[2]),
- });
- }
- const res = await createBill(tempBill);
- if(res===false){
- return false;
- }else{
- return true;
- }
- }
- async function createBill(bill){
- const getDistName = await connection.execute(`
- SELECT id FROM distributors
- WHERE name LIKE '%${bill.distName}%'`);
- const distId = getDistName[0][0].id;
- if(!distId){
- return false;
- }
- const quer = await connection.execute(`
- INSERT INTO bill (bill_date,distributor,summ,payment,confirm,order_id)
- VALUES('${bill.date}',${distId},${bill.summ},0,0,${bill.orderId})`);
- for(const item of bill.goods){
- const getGoods = await getGood({goodName: item.name});
- console.dir(getGoods);
- if(getGoods.length === 0){
- return false;
- }
- const insertBillGoods = await connection.execute(`
- INSERT INTO tovari_bills (id_bill, id_tovar, amount, price)
- VALUES (${quer[0].insertId},${getGoods[0].id},${item.amount},${item.price})`);
- if(insertBillGoods[0].chengedRows > 0){
- return true;
- }
- }
- return true;
- }
- async function getReestr(date1, date2){
- const res = await connection.execute(`
- SELECT naklad.date_, naklad_order.amount, naklad_order.price, (naklad_order.price*naklad_order.amount) summTov,
- (distributors.name) distName, catalog_prod.name, catalog_prod.measure
- FROM naklad
- LEFT JOIN bill ON naklad.bill_id = bill.id
- LEFT JOIN distributors ON bill.distributor = distributors.id
- LEFT JOIN naklad_order ON naklad.id = naklad_order.id_naklad
- LEFT JOIN catalog_prod ON naklad_order.id_tovar = catalog_prod.id
- WHERE date_ >= '${date1}' AND date_ <= '${date2}'
- ORDER BY naklad.date_ `) ;
- const tempR = res[0];
- const reestr = {
- date1 : date1,
- date2 : date2
- };
- const goods = tempR.map(i=>{
- return{
- distName : i.distName,
- name : i.name,
- amount: i.amount,
- price: i.price,
- measure: i.measure,
- summTov: i.summTov,
- date: moment(i.date_).format('YYYY-MM-DD')
- }
- });
- reestr.goods = goods;
- return reestr;
- }
- async function getDistName(name){
- const res = await connection.execute(`
- SELECT * FROM distributors
- WHERE name LIKE '%${name}%'`);
- console.dir(res[0]);
- return res[0];
- }
- async function botInit() {
- bot.onText(/\/start/, async (msg) => {
- const chatId = msg.chat.id;
- const user = await checkUsers(chatId);
- if(user[0] && user[0].password_) {
- await bot.sendMessage(chatId, `Доброго времени суток, ${user[0].login}\nДля просмотра списка доступных команд введите /main`);
- return;
- }
- chatStatuses.set(chatId, userStatus.NEW);
- await bot.sendMessage(chatId, [`Привет! Я - Бертруд, ваш персональный помощник по закаулкам нашей компании.`,
- `Я помогу вам разобраться в том, что здесь происходит. Следуйте инструкциям!`,
- `Для продолжения необходимо назначить вашу роль в системе (/setrole [lead, manager, worker]).`,
- `Примечание: в [] приводятся доступные параметры`,
- ].join('\n'));
- });
- bot.onText(/\/setrole (.+)/, async (msg, match) => {
- const chatId = msg.chat.id;
- const name = msg.from.username;
- const role = match[1];
- const pass = msg.chat.id;
- if(name === undefined){
- const fName = msg.from.first_name;
- const sName = msg.from.last_name;
- const fullName = `${fName}_${sName}`;
- await registration(chatId, fullName, pass, role);
- await bot.sendMessage(chatId, [`Вы успешно зарегестрированы в системе как ` + match[1] + `.`,
- `Чтобы посмотреть список доступных команд введите /main'`,
- ].join('\n'));
- chatStatuses.delete(chatId);
- return;
- }
- await registration(chatId, name, pass, role);
- await bot.sendMessage(chatId, [`Вы успешно зарегестрированы в системе как ` + match[1] + `.`,
- `Чтобы посмотреть список доступных команд введите /main'`,
- ].join('\n'));
- chatStatuses.delete(chatId);
- });
- bot.onText(/\/main/, async (msg) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role === 'lead'){
- await bot.sendMessage(chatId,`Список доступных команд:`);
- await bot.sendMessage(chatId, [
- `/billList - списко счет-фактур `,
- `/billsbyDist [имя_поставщика] -список счет-фактур от конкретного поставщика`,
- `/viewCatalog - каталог товаров в наличии `,
- `/getConfirmedBills - список подтвержденных счет-фактур `,
- `/getNaklad - список накладных `,
- `/orderList - список заказов`,
- `/addBill - зарегестрировать счет-фактуру`,
- ].join('\n'));
- }
- if(login[0].role === 'manager'){
- await bot.sendMessage(chatId,`Список доступных команд:`);
- await bot.sendMessage(chatId, [
- `/billList - список счет-фактур`,
- `/billsbyDist [имя_поставщика] - список счет-фактур от конкретного поставщика`,
- `/viewCatalog - каталог товаров в наличии `,
- `/getConfirmedBills - список подтвержденных счет-фактур`,
- `/getNaklad - список накладных`,
- `/getUnconfBills - список неподтвержденных счет-фактур`,
- `/orderList - список заказов`,
- `/compareOrderBill [номер_заказа] [номер_счета]`,
- `/addBill - зарегестрировать счет-фактуру`,
- ].join('\n'));
- }
- if(login[0].role === 'worker'){
- await bot.sendMessage(chatId,`Список доступных команд:`);
- await bot.sendMessage(chatId, [
- `/viewCatalog - каталог товаров в наличии`,
- `/createReestr - получить реестр поступлений за период `,
- `/createNaklad [номер_счет-фактуры] [номер_склада] [сумма] [[наименование,цена,количество] ...] - создать накладную`,
- ].join('\n'));
- }
- });
- //lead-manager
- bot.onText(/\/billList/, async (msg) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- chatStatuses.set(chatId,userStatus.PRINT.BILL);
- await bot.sendMessage(chatId,[`Вы можете получить любой из доступных счет-фактур в виде файла.`,
- `Для этого введите /print [billId]`,
- ].join('\n'));
- const resp = await billHistory(0);
- const mess = [];
- const options ={
- reply_markup:JSON.stringify({
- inline_keyboard:[
- [{text:'➡️',callback_data: 'bills_1'}],
- ]
- })
- };
- if(!resp){
- await bot.sendMessage(chatId,`Error 01`);
- }else{
- await bot.sendMessage(chatId, `Cписок счет-фактур: `);
- console.dir(resp);
- for (const item of resp) {
- const payment = item.payment === 1 ? 'done' : 'undone';
- const message = `/${item.id} - ${item.bill_date}. ${item.name} - ${item.summ}, payment: ${payment}`;
- mess.push(message);
- }
- await bot.sendMessage(chatId, mess.join('\n'),options);
- console.dir(mess);
- }
- }else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- });
- //worker
- bot.onText(/\/createReestr/, async (msg) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role === 'worker'){
- chatStatuses.set(chatId, userStatus.PRINT.REESTR);
- await bot.sendMessage(chatId,[`Для получения реестра за период введите /print [нижняя_дата] [верхняя_дата]`,
- `Дата вводится в формате ГГГГ-ММ-ДД`,
- ].join('\n'));
- }else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- });
- //ALL
- bot.onText(/\/viewCatalog/, async (msg) => {
- const chatId = msg.chat.id;
- //chatStatuses.set(chatId, userStatus.PRINT.REESTR);
- const options ={
- reply_markup:JSON.stringify({
- inline_keyboard:[
- [{text:'➡️',callback_data: 'goods_1'}],
- ]
- })
- };
- const mess = [];
- const resp = await viewCatalog(0);
- if (!resp) {
- await bot.sendMessage(chatId, `Ошибка`);
- } else {
- for(const item of resp){
- const message = `${item.id}. ${item.name}, Количество: ${item.amount}(${item.measure}), Цена ${item.price}`;
- mess.push(message);
- }
- console.dir(mess);
- await bot.sendMessage(chatId, mess.join('\n'), options);
- }
- });
- //ALL
- bot.onText(/\/viewGood (.+)/i, async (msg, match) => {
- const chatId = msg.chat.id;
- const goodId = Number(match[1]);
- const good = await viewGood(goodId);
- if(!good){
- await bot.sendMessage(chatId,`Товар не найден`);
- }
- else{
- const mess= [];
- const field = Object.keys(good);
- for (const item of field) {
- const message =`${item}: ${good[item]}`;
- mess.push(message);
- }
- await bot.sendMessage(chatId, mess.join('\n'));
- await bot.sendMessage(chatId, [`Для редактирования записи воспользуйтейсь командой `,
- `/edit [id] [field] [new_value]`,
- ].join('\n'));
- }
- });
- //lead-manager
- bot.onText(/\/edit (.+?) (.+?) (.+)/i, async (msg, match) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- const respId = Number(match[1]);
- const respCol = match[2];
- const respVal = parseVariable(match[3]);
- if(respCol ===undefined || respId === undefined || respVal === undefined){
- await bot.sendMessage(chatId,`Проверьте правильность ввода команды`);
- return;
- }
- const resp = await editCatalog(respId, respCol, respVal);
- if(resp){
- await bot.sendMessage(chatId,`Error 03`);
- }else{
- await bot.sendMessage(chatId, `Успешно`);
- }
- }else{
- await bot.sendMessage(chatId,`Команда недоступна`);
- }
- });
- //lead-manager
- bot.onText(/\/delete (.+)/, async (msg, match) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- const respId = Number(match[1]);
- const resp = await deleteGood(respId);
- if (resp === true) {
- await bot.sendMessage(chatId, 'Успешно удалено');
- } else {
- await bot.sendMessage(chatId, 'Запись не найдена.');
- }
- }else{
- await bot.sendMessage(chatId,`Команда недоступна`);
- }
- });
- //manager
- bot.onText(/\/compareOrderBill (.+?) (.+)/, async (msg, match) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role === 'manager'){
- const orderID = Number(match[1]);
- const billID = Number(match[2]);
- if(orderID === undefined || billID === undefined){
- await bot.sendMessage(chatId,[`Проверьте правильность ввода команды`,
- `/команда [номер_заказа] [номер_счета]`,
- ].join('\n'));
- return;
- }
- const resp = await checkOrderBill(orderID, billID);
- console.dir(resp)
- if (resp.length>0){
- await bot.sendMessage(chatId, [`Имеются расхождения:`,
- `Разница суммы (Счет-Заказ): ${resp.summ}`,
- ].join('\n'));
- for (const item of resp) {
- const name = item.name === undefined ? '-' : item.name;
- const upName = item.updated.name === undefined ? '-' : item.updated.name;
- const price = item.price === undefined ? '-': item.price;
- const upPrice = item.updated.price === undefined ? '-' : item.updated.price;
- const amount = item.amount === undefined ? '-' : item.amount;
- const upAmount = item.updated.price === undefined ? '-' : item.updated.amount;
- await bot.sendMessage(chatId,[`ЗАКАЗАНЫЙ ТОВАР: ${name}, ПРЕДЛОЖЕННЫЙ: ${upName}`,
- `СТОИМОСТЬ: ${price}, ПРЕДЛОЖЕННАЯ: ${upPrice}`,
- `КОЛИЧЕСТВО: ${amount}, ПРЕДЛОЖЕННОЕ: ${upAmount}`,
- ].join('\n'));
- }
- await bot.sendMessage(chatId, [`Для подтверждения оплаты введите /confirm ${billID}`,
- ].join('\n'));
- }else{
- await bot.sendMessage(chatId,`Расхождений не выявлено`);
- }
- }else{
- await bot.sendMessage(chatId,`Команда недоступна`);
- return;
- }
- });
- bot.onText(/\/confirm (.+)/, async (msg, match) => {
- const chatId = msg.chat.id;
- const respId = Number(match[1]);
- if(respId === undefined){
- await bot.sendMessage(chatId,`Не указан номер счета!`);
- return;
- }
- const resp = await billConfirmation(respId);
- if(resp === true){
- await bot.sendMessage(chatId, `Успешно`);
- }
- else{
- await bot.sendMessage(chatId,`Error 02`);
- }
- });
- //lead-manager
- bot.onText(/\/billsbyDist (.+)/, async (msg, match) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role!='worker'){
- const name = String(match[1]);
- if(name === undefined){
- await bot.sendMessage(chatId,'Проверьте правильность ввода команды');
- return;
- }
- chatStatuses.set(chatId,userStatus.PRINT.BILL);
- const mess = [];
- const resp = await getBills(name);
- if(resp){
- const bills = resp;
- console.dir(bills)
- for (const item of bills) {
- const payment = item.payment === 1 ? 'done' : 'undone';
- const message = `/${item.id} - ${item.bill_date}. ${item.name} - ${item.summ}, payment: ${payment}`;
- mess.push(message);
- }
- await bot.sendMessage(chatId, mess.join('\n'));
- }else{
- await bot.sendMessage(chatId,`Error 04`);
- }
- }else{
- await bot.sendMessage(chatId,`Команда недоступна`);
- }
- });
- //manager-lead
- bot.onText(/\/getConfirmedBills/, async msg => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- const resp = await getBillConfirmed();
- const bills = resp[0];
- console.dir(bills);
- await bot.sendMessage(chatId, `Список подтвержденных счет-фактур: `);
- for (const item of bills) {
- await bot.sendMessage(chatId, `/${item.id}. ${item.bill_date} - ${item.name}. Сумма: ${item.summ}`);
- }
- }else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- });
- //manager-lead
- bot.onText(/\/getNaklad/, async msg => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- chatStatuses.set(chatId, userStatus.PRINT.NAKLAD);
- const options ={
- reply_markup:JSON.stringify({
- inline_keyboard:[
- [{text:'➡️',callback_data: 'naklad_1'}],
- ]
- })
- };
- const mess = [];
- const resp = await getNaklad({count : 0});
- console.dir(resp);
- await bot.sendMessage(chatId, `Список приходных накладных: `);
- for (const item of resp){
- const message = [`/${item.id}. ${item.date_};`,
- `Номер счет-фактуры ${item.bill_id}, на сумму: ${item.summ}.`,
- `Товар пришел на склад №${item.warehouse}`,
- ].join('\n');
- mess.push(message);
- }
- await bot.sendMessage(chatId, `Для получения файла введите /print [id]`);
- await bot.sendMessage(chatId, mess.join('\n'),options);
- }else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- });
- //lead- manager
- bot.onText(/\/getUnconfBills/, async (msg) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role === 'manager'){
- const bills = await getUnConfirmedBill();
- if(bills.length == 0){
- await bot.sendMessage(chatId, `Нет неподтвержденных`);
- return;
- }
- const billIds = new Set(bills.map(b=>b.id));
- for (const billId of billIds) {
- const bill = bills.find(b=>b.id === billId);
- const items = bills.filter(b=>b.id === billId);
- await bot.sendMessage(chatId, [`/${billId}\nДата: ${bill.bill_date}.`,
- `${items.map(b => ` - ${b.name} (кол:${b.amount})`).join(',\n')}.`,
- `Сумма: ${bill.summ} грн.`,
- `Поставщик: ${bill.dist_name}.`,
- ].join('\n'));
- }
- await bot.sendMessage(chatId,`Для отправки счета на проверку: /sendBill [billId]`);
- }else{
- await bot.sendMessage(chatId,`Команда недоступна`);
- }
- });
- bot.onText(/\/sendBill (.+)/, async (msg,match)=>{
- const chatId = msg.chat.id;
- const id = Number(match[1]);
- const users = await sendBillForConfirm();//id лида
- const bills = await getUnConfirmedBill(id);
- const login = await checkUsers(chatId);
- for (const user of users) {
- await bot.sendMessage(user.password_,`Вы получили счет для проверки от ${login[0].login}`);
- const billIds = new Set(bills.map(b=>b.id));
- for (const billId of billIds) {
- const bill = bills.find(b=>b.id === billId);
- const items = bills.filter(b=>b.id === billId);
- await bot.sendMessage(user.password_, [`/${billId}\nДата: ${bill.bill_date}.`,
- `${items.map(b => ` - ${b.name} (кол:${b.amount})`).join(',\n')}.`,
- `Сумма: ${bill.summ} грн.`,
- `Поставщик: ${bill.dist_name}.`,
- ].join('\n'));
- }
- await bot.sendMessage(user.password_,` Для подтверждения счет фактуры введите /confirm [id]`)
- }
- await bot.sendMessage(chatId, `Успешно`);
- })
- //worker
- bot.onText(/\/createNaklad (.+?) (.+?) (.+?) (.+)/, async (msg,match) => {
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role === 'worker'){
- if(match.length < 6){
- const resp = await createNakladBotFunc(msg, match);
- if(resp == true){
- await bot.sendMessage(chatId, `Успешно`);
- return;
- }
- await bot.sendMessage(chatId, `Error 05`);
- }
- }else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- });
- bot.onText(/\/distId (.+)/, async (msg,match)=>{
- const chatId = msg.chat.id;
- const name = match[1];
- const res = await getDistName(name);
- if(res.length > 0){
- await bot.sendMessage(chatId,`Id-номер поставщика ${name} - ${res[0].id}`);
- }
- else{
- await bot.sendMessage(chatId,`Поставщик не найден`);
- }
- });
- bot.onText(/\/addBill/, async (msg)=>{
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- await bot.sendMessage(chatId,[`Для того, чтобы зарегестрировать счет-фактуру введите:`,
- `/createBill [orderId] [distName] [summ] [name,price,amount name2,price2,amount2...]`,
- `Команда чувствительна к регистру!`,
- `orderId - номер заказа`,
- `distName - id-номер поставщика (узнать номер - /distId [имя])`,
- `summ - сумма счета`,
- `name,price,amount - список товаров (наименование/цена/количество)`,
- ].join('\n'));
- }
- });
- // /createBill [orderId] [distName] [summ] [name,price,amount name,price,amount name,price,amount...]
- bot.onText(/\/createBill (.+?) (.+?) (.+?) (.+)/, async (msg,match)=>{
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- const res = await createBillBotFunc(msg,match);
- if(res===true){
- await bot.sendMessage(chatId, `Успешно`);
- }else{
- await bot.sendMessage(chatId, `Error`);
- }
- }else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- } );
- // /n [billId] [warehouseId] [summ] [name,price,amount name,price,amount name,price,amount...]
- //lead-manager
- bot.onText(/\/orderList/, async(msg)=>{
- const chatId = msg.chat.id;
- const login = await checkUsers(chatId);
- if(login[0].role != 'worker'){
- const orders = await getOrder({count: 0});
- const mess =[];
- const options ={
- reply_markup:JSON.stringify({
- inline_keyboard: [
- [{text:'➡️',callback_data: 'order_1'}],
- ]
- })
- }
- chatStatuses.set(chatId, userStatus.PRINT.ORDER);
- for (const item of orders) {
- const message = `/${item.id}\nДата: ${item.order_date}\nПоставщик: ${item.distributor}\nСумма заказа: ${item.summ}`;
- mess.push(message);
- };
- await bot.sendMessage(chatId,mess.join('\n'), options);
- await bot.sendMessage(chatId, `Для печати заказа введите /print [id]`);
- }
- else{
- await bot.sendMessage(chatId, `Команда недоступна`);
- }
- })
- bot.onText(/\/print (.+)/, async (msg, match)=>{
- const chatId = msg.chat.id;
- console.log(chatStatuses.get(chatId));
- if(chatStatuses.get(chatId) === userStatus.PRINT.BILL){
- const billId = Number(match[1]);
- const bill = await getBill(billId);
- const file = await ejs.renderFile('./doc1.ejs', { bill });
- console.log(file);
- const pdf = await generatePDF(file);
- const fileOptions = {
- filename: `Счет-фактура #${billId}`
- }
- await bot.sendDocument(chatId, pdf, {}, fileOptions);
- };
- if(chatStatuses.get(chatId) === userStatus.PRINT.NAKLAD){
- const nakladId = Number(match[1]);
- const naklad = await getNaklad({nakladId: nakladId});
- const file = await ejs.renderFile('./naklad.ejs', { naklad });
- const pdf = await generatePDF(file);
- const fileOptions = {
- filename: `Накладная №${nakladId}`
- }
- await bot.sendDocument(chatId, pdf,{}, fileOptions);
- };
- if(chatStatuses.get(chatId) === userStatus.PRINT.ORDER){
- const orderId = Number(match[1]);
- console.log(match[1], match[2]);
- const order = await getOrder({orderId:orderId});
- const file = await ejs.renderFile('./order.ejs', {order});
- const pdf = await generatePDF(file);
- const fileOptions={
- filename: `Заказ № ${orderId}`
- }
- await bot.sendDocument(chatId, pdf, {}, fileOptions);
- };
- if(chatStatuses.get(chatId) === userStatus.PRINT.REESTR){
- const dates = match[1].split(' ');
- const date1 = dates[0];
- const date2 = dates[1];
- console.log(date1,date2);
- const reestr = await getReestr(date1,date2);
- const file = await ejs.renderFile('./reestr.ejs', {reestr});
- const pdf = await generatePDF(file);
- const fileOptions={
- filename: `Реестр за период ${date1} - ${date2} `
- }
- await bot.sendDocument(chatId, pdf, {}, fileOptions);
- }
- })
- }
- async function test() {
- const msg ={
- date: '2018-12-12',
- };
- const temp=[
- '','3','rogale','13300','рогалик,12,11 генерал,22,11 Шоколад,32,1',
- ];
- //await createBillBotFunc(msg,temp);
- }
- async function start() {
- await botInit();
- await initDatabase();
- await test();
- }
- function sqlValue(val) {
- if (val === null || val === undefined) return 'NULL';
- return typeof val === 'string' ? `'${val}'` : String(val);
- }
- function parseVariable(val) {
- const numVal = Number(val);
- if (Number.isNaN(numVal)) {
- return String(val);
- }
- return Number(val);
- }
- async function checkUsers(id){
- const user = await connection.execute(`SELECT * FROM users WHERE users.password_ = ${id}`);
- console.dir(user[0]);
- if(user){
- return user[0];
- }
- return;
- }
- async function generatePDF(file){
- const pdf = await Html2Pdf.render({
- html: file,
- url: 'http://0.0.0.0:3000'
- }, {
- format: 'A4'
- });
- return pdf;
- }
- bot.on('callback_query',async(msg)=>{
- const answer = msg.data.split('_');
- const docType = answer[0];
- const indexCurr = Number(answer[1]);
- const keys = [];
- const keyForward = [{text:'➡️',callback_data: `${docType}_${indexCurr+1}`}];
- const keyBackward =[{text:'⬅️',callback_data: `${docType}_${indexCurr-1}`}];
- const chatId = msg.from.id;
- if(docType ==='goods'){
- const check = await countRowsCatalog();
- if(indexCurr*5 < check && indexCurr>=0){
- keys.push(keyBackward);
- keys.push(keyForward);
- }
- if(indexCurr < 0){
- keys.push(keyForward);
- }
- if(indexCurr*5+1>check){
- keys.push(keyBackward);
- }
- const mess = [];
- const resp = await viewCatalog(indexCurr*5);
- const options = {
- reply_markup:JSON.stringify({
- inline_keyboard: keys
- })
- };
- if (!resp) {
- await bot.sendMessage(chatId, `Ошибка`, options);
- } else {
- for(const item of resp){
- const message = `${item.id}. ${item.name}, Количество: ${item.amount}(${item.measure}), Цена ${item.price}`;
- mess.push(message);
- }
- if(mess.length>0){
- const text = mess.join('\n');
- await bot.sendMessage(chatId,text,options);
- }else{
- await bot.sendMessage(chatId,`Error1`,options);
- }
- }
- }
- if(docType === 'bills'){
- const check = await countRowsBills();
- if(indexCurr*5 < check && indexCurr>=0){
- keys.push(keyBackward);
- keys.push(keyForward);
- }
- if(indexCurr < 0){
- keys.push(keyForward);
- }
- if(indexCurr*5+1>check){
- keys.push(keyBackward);
- }
- const mess = [];
- const resp = await billHistory(indexCurr*5);
- const options = {
- reply_markup:JSON.stringify({
- inline_keyboard: keys
- })
- };
- if (!resp) {
- await bot.sendMessage(chatId, `Ошибка`, options);
- } else {
- for(const item of resp){
- const payment = item.payment === 1 ? 'done' : 'undone';
- const message = `/${item.id} - ${item.bill_date}. ${item.name} - ${item.summ}, payment: ${payment}`;
- mess.push(message);
- }
- if(mess.length>0){
- const text = mess.join('\n');
- await bot.sendMessage(chatId,text,options);
- }else{
- await bot.sendMessage(chatId,`Error1`,options);
- }
- }
- }
- if(docType === 'naklad'){
- const check = await countRowsNaklad();
- if(indexCurr*5 < check && indexCurr>=0){
- keys.push(keyBackward);
- keys.push(keyForward);
- }
- if(indexCurr < 0){
- keys.push(keyForward);
- }
- if(indexCurr*5+1>check){
- keys.push(keyBackward);
- }
- const mess = [];
- const resp = await getNaklad({count: indexCurr*5})
- const options = {
- reply_markup:JSON.stringify({
- inline_keyboard: keys
- })
- };
- if (!resp) {
- await bot.sendMessage(chatId, `Ошибка`, options);
- } else {
- for(const item of resp){
- const message = [`/${item.id}. ${item.date_};`,
- `Номер счет-фактуры ${item.bill_id}, на сумму: ${item.summ}.`,
- `Товар пришел на склад №${item.warehouse}`,
- ].join('\n');
- mess.push(message);
- }
- if(mess.length>0){
- const text = mess.join('\n');
- await bot.sendMessage(chatId,text,options);
- }else{
- await bot.sendMessage(chatId,`Error1`,options);
- }
- }
- }
- if(docType === 'order'){
- const check = await countRowsOrder();
- if(indexCurr*5 < check && indexCurr>=0){
- keys.push(keyBackward);
- keys.push(keyForward);
- }
- if(indexCurr < 0){
- keys.push(keyForward);
- }
- if(indexCurr*5+1>check){
- keys.push(keyBackward);
- }
- const mess = [];
- const resp = await getOrder({count:indexCurr*5});
- const options = {
- reply_markup:JSON.stringify({
- inline_keyboard: keys
- })
- };
- if (!resp) {
- await bot.sendMessage(chatId, `Ошибка`, options);
- } else {
- for(const item of resp){
- const message = `/${item.id}\nДата: ${item.order_date}\nПоставщик: ${item.distributor}\nСумма заказа: ${item.summ}`;
- mess.push(message);
- }
- if(mess.length>0){
- const text = mess.join('\n');
- await bot.sendMessage(chatId,text,options);
- }else{
- await bot.sendMessage(chatId,`Error1`,options);
- }
- }
- }
- });
- start();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement