CHAT BOT

[BilliarIssa]

-- We have been struggling in making chat-bot, backend and frontend developers were lack in my ex-company. I did not have enough hard skills in order to realize such projects. -- After searching a while i found out chat-bot constructor. It is not just simple and easy construction, it requires several numerical and mathematical skills (MEGA-EASY).

The purpose was to make a chat bot, that will insert data of peasant farm for meat-processing plant. So that they can fill their information individually.

So we have two options of supplying rams and cows. To supply them our scheme looked like this:

HERE IS THE REVISED VERSION OF ALGORITHMS

[BilliarIssa]

-- THAT IS HOW IT LOOKS LIKE WHEN WE CONSTRUCT THE COMMON LOGIC FOR THIS KIND OF TASK.

-- IT MIGHT BE COMPLICATED. THE RIGHT WAY TO DO IT IS TO CREATE "Scenario", THAT WILL SAVE OTHER CONSTRUCTIONS RELATED TO THIS

DATA THAT IS INSERTED INTO THROUGH THE CHAT BOT, WAS STORING INTO THE CLOUD_SPREADSHEET.

[BilliarIssa]

How it looks like from whatsapp interface:

"Welcoming peasant farm holders"

"Choosing their date(when they want to supply)"

"When farm is over, they get their confirmation"

[BilliarIssa]

Why it was comfortable for simple worker, because it requires a lot of patience by writing it with paper and pen. The purpose was just optimize reserving places for supply.

Here is the table and data of our suppliers.

Note That unblurred information is fake!!!

HERE IN THE SECOND TABLE IS THE IDENTIFIER OF WHERE CITY IS LOCATED, WE GAVE THEM AND ID SO THAT IT WOULD BE EASY TO SORT OUT LOCATIONS. NAME OF THE TABLE "rams1"

[BilliarIssa]

So again, Let's dive into SQL query, so we have two tables: first one is the information about suppliers, the second one is their location. So i need to combine these two tables and make and query that will ouput combinde table. Then create new table with name "SORTED":

SELECT DISTINCT r.name_company, r.passport_id, r.quantity, r1.Status INTO SORTED FROM rams r JOIN rams1 r1 ON r1.Track_ID = r.City_id;

So, we have combined them.

[BilliarIssa]

Also, I did not like the phone_number formats; like: 77711234567, It is not comfortable to read. So I just formatted its phone number in sql query:

UPDATE rams SET phone_number = '7 (' + SUBSTRING(CAST(phone_number AS VARCHAR), 2, 3) + ') ' + SUBSTRING(CAST(phone_number AS VARCHAR), 5, 3) + ' ' + SUBSTRING(CAST(phone_number AS VARCHAR), 8, 2) + ' ' + SUBSTRING(CAST(phone_number AS VARCHAR), 10, 2);

Here is the result:

[BilliarIssa]

Also I did not like at all the date because, it has two strokes. "08.01.2024 ДҮЙСЕНБІ",

UPDATE RAMS SET date = LEFT(date, 10);

IT WILL SELECT IN DATE column only first ten characters;

As u can see we removed "ДҮЙСЕНБІ"

And replace "." dot to slash "/" with the code

update rams SET date = REPLACE(date, '.', '/');

Then we selected only total sum of quantity by date:

select DateName (year, date) + '-' + DateName(month, date) + '-' + DATENAME(DAY, date) AS date_sup, COUNT(DISTINCT id) AS total_orders INTO day_order FROM rams GROUP BY DateName(YEAR, date) + '-' + DateName(MONTH, date) + '-' + DateName(DAY, date);

select *from rams

select date, sum(quantity) as qnt, (select sum(quantity) from rams) as total, CAST(sum(quantity) * 100 / (select sum(quantity) from rams) as decimal(10,2)) as PRCNT

from rams group by date order by prcnt asc;

THIS QUERY WILL FIND TOTAL OF QUANTITY, PERCENTAGE OF THE SUPPLIERS BY DATE;

[BilliarIssa]

AFTER WE HAVE CREATED SEVERAL PROCEDURES IN ORDER TO OUTPUT THESE VALUES THAT ARE HIGHER THAN THE GIVEN NUMBER;

With the procedures it will give opportunity to output data with the conditions that are neccessary.

create procedure Analysis1 @quantitygiven INT as begin begin transaction;

CREATE TABLE #CompanyStats (
    company_name NVARCHAR(255),
    total_quantity INT,
    average_quantity DECIMAL(10,2),
    Status NVARCHAR(255)
);

INSERT INTO #CompanyStats (company_name, total_quantity, average_quantity, Status)
SELECT 
    r.name_company, 
    SUM(r.quantity) AS total_quantity,
    AVG(CAST(r.quantity AS DECIMAL(10,2))) AS average_quantity,
    r1.Status
FROM 
    rams r
JOIN rams1 r1 on r1.Track_ID = r.City_Id
GROUP BY 
    r.name_company, r1.Status;

SELECT 
    company_name, 
    total_quantity, 
    average_quantity,
    Status
FROM 
    #CompanyStats
WHERE 
    average_quantity > @quantitygiven;

COMMIT TRANSACTION;

DROP TABLE #CompanyStats;

END;

[BilliarIssa]

here is the power bi model