async def update_in_db(type_product,
sex,
category,
post,
number,
price,
product_name,
sale=None,
sale_start=None,
sale_end=None):
async with pool.acquire() as conn:
if sale is None:
row = await conn.execute(
f"update catalog set post='{post}', price='{price}', product_name='{product_name}'"
f"where name~('{category_rename[type_product]}.{sex_rename[sex]}.{category}.' || substring((select "
f"name from catalog where name ~ '*.{sex_rename[sex]}.{category}.*{{1}}' order by name desc "
f"limit 1 offset {number})::text from '%.%.%.#\"%#\"' for '#'))::lquery"
)
else:
row = await conn.execute(
f"update catalog set post='{post}', price='{price}', product_name='{product_name}', sale='{sale}',"
f"sale_start='{sale_start}'::date, sale_end='{sale_start}'::date + {sale_end}, sale_indicator=false "
f"where name~('{category_rename[type_product]}.{sex_rename[sex]}.{category}.' || "
f"substring((select name from catalog where name ~ '*.{sex_rename[sex]}.{category}.*{{1}}' "
f"order by name desc limit 1 offset {number})::text from '%.%.%.#\"%#\"' for '#'))::lquery "
)
return row
async def add_to_db(type_product,
sex,
category,
post,
price,
product_name,
sale=None,
sale_start=None,
sale_end=None):
async with pool.acquire() as conn:
if sale is None:
row = await conn.execute(
"""insert into catalog (name, post, price, product_name) values
(($1 || '.' || $2 || '.' || $3 || '.' ||
get_number(('*.' || $2 || '.' || $3 || '.*{1}')::lquery))::ltree, $4, $5, $6)""",
str(category_rename[type_product]), str(sex_rename[sex]),
str(category), str(post), str(price), str(product_name))
else:
row = await conn.execute(
"""insert into catalog (name, post, price, sale, product_name, sale_start, sale_end, sale_indicator)
values (($1 || '.' || $2 || '.' || $3 || '.' ||
get_number(('*.' || $2 || '.' || $3 || '.*{1}')::lquery))::ltree,
$4, $5, $7, $6, $8::date,
$8::date + $9::integer, false)""",
str(category_rename[type_product]), str(sex_rename[sex]),
str(category), str(post), str(price), str(product_name),
str(sale), sale_start, int(sale_end))
return row
async def search_in_base(text):
async with pool.acquire() as conn:
row = await conn.fetch(
"""SELECT * from catalog where (substring(post from '#"%#"<a href%>' for '#') ~* $1 or
substring(product_name from '<b>#"%#"</%' for '#') ~* $1) and
price is not null and name ~ '*.*.*.*';""", str(text))
return row
async def delete_from_db(sex, category, number):
async with pool.acquire() as conn:
row = await conn.execute(
"""delete from catalog where name ~ ('*.' || $1 || '.' || $2 || '.' || substring((select name
from catalog where name ~ ('*.' || $1 || '.' || $2 || '.*{1}')::lquery order by name desc limit 1 offset
$3)::text from '%.%.%.#\"%#\"' for '#'))::lquery""",
str(sex_rename[sex]), str(category), int(number))
return row
async def sales2cat_add(sex, category, per, s2c_start, s2c_end):
print(
f"select sales2cat_add('*.{sex_rename[sex]}.{category}'::lquery, '{per}', '{s2c_start}'::date, {s2c_end})"
)
async with pool.acquire() as conn:
row = await conn.fetch(
"""select sales2cat_add(('*.' || $1 || '.' || $2)::lquery, $3, $4::date, $5)""",
str(sex_rename[sex]), str(category), int(per), s2c_start,
int(s2c_end))
return row[0][0]
async def get_product(sex, name, number):
async with pool.acquire() as conn:
row = await conn.fetch(
f"select catalog.*, sel.resul, sel.inf from catalog, sel('*.{sex_rename[sex]}.{name}.*{{1}}', {number}) "
f"where catalog.name ~ '*.{sex_rename[sex]}.{name}.*{{1}}' and catalog.price is not null order by "
f"catalog.name desc limit 1 offset (select resul from sel('*.{sex_rename[sex]}.{name}.*{{1}}', {number}));"
)
if not row:
return False
return row
async def del_prod_sale(sex, category, number):
async with pool.acquire() as conn:
row = await conn.fetch(
"""UPDATE CATALOG SET
price = substring(price from '%<s>#"%#"</s>' for '#'),
sale = null,
sale_start = null,
sale_end = null,
sale_indicator = null
where
name ~ ('*.' || $1 || '.' || $2 || '.' || substring((select name
from catalog where name ~ ('*.' || $1 || '.' || $2 || '.*{1}')::lquery order by name desc limit 1
offset $3)::text from '%.%.%.#\"%#\"' for '#'))::lquery
and position('<s>' in price) > 0
and sale_indicator is true;""", sex_rename[sex], category,
int(number))
return row
async def update_sales():
async with pool.acquire() as conn:
row = await conn.fetch('select update_sales()')
return row[0][0]
async def get_search_product(product):
async with pool.acquire() as conn:
row = await conn.fetch(
"""select * from catalog where name ~ $1::lquery""", product)
return row
async def sale_cat_end(sex, category):
async with pool.acquire() as conn:
row = await conn.fetch(
"""SELECT del_sales(('*.' || $1 || '.' || $2)::lquery)""",
sex_rename[sex], category)
return row[0][0]