1 Star 0 Fork 0

dongxf / oms2

Create your Gitee Account
Explore and code with more than 5 million developers,Free private repositories !:)
Sign up
Clone or download
update-tags.rb 18.98 KB
Copy Edit Web IDE Raw Blame History
dongxf authored 2020-11-20 17:34 . merged consign stock and attr
#encoding: utf-8
=begin
Usage: This ruby file will update crmbe product tags in keyword field
arguments: ruby update-tags.rb
=end
load 'rds_api.rb'
load 'crmeb_utils.rb'
def get_products_ conditions
products = []
inq = "select id, source_pid, source_mid, sales, price, keyword, store_name, store_info, stock, is_show, cate_id, sort, temp_id, mer_id from crmeb.eb_store_product "
inq += conditions.empty? ? 'where is_del = 0;' : " #{conditions};"
res = queryRds inq
res.each { |product| products += [product] }
return products
end
#根据商家在每个模块的权重,返回对应模块可分配的最大项目数值
def module_quota mer_id, module_symbol
#get mer_id weight
weight = 1
case module_symbol
when :valued_product #超值推荐
return (12 * weight).to_i
when :newest_product #新建商品
return (6 * weight).to_i
else
return (6 * weight).to_i
end
end
def update_tags_for products
idx = 0
products.each do |product|
keywords = {}
tags = []
merchant = '这是臭虫' #理论上不应该有缺少mer_id的产品
template = '这是虫二'
begin
keywords = JSON.parse product['keyword']
rescue => e
#如果keyword为空解析会出错,这时候把原值传给它
keywords = product['keyword']
end
#允许用户第一次输入特性标签列表
if keywords.class == String
tags = keywords.split(" ") #针对默认的字符串设为将头三个设为特性标签
tags.size.times { |idx| tags[idx] = tags[idx][0..9] } #不允许超过6个字符
tags = tags[0..4] if product['mer_id'] > 1 #限制非自营的标签呈现数量
end
#优先使用已保留的合法数据
if keywords.class == Hash && keywords['tags'] && keywords['tags'].class == Array
tags = keywords['tags']
end
#根据用户提交信息更新其他标签,尚未实现
#{"brand":"绿手指农场","supplier":"绿手指农场","producer":"珠海高栏港平沙镇/绿手指有机农业园","security":"中国有机认证","conditions":"#低温冷藏3天"}
keywords = {} if keywords.class != Hash #如果是合法的Hash则保留之前解析出的记录,否则重构
tags += [keywords['security']] if keywords['security'] && keywords['security'] != '-'
#去除pid标签
pidTag = "p##{product['id']}"
tags -= [pidTag]
#准备商店名称,强制更新
merchant = merchantList[product['mer_id']]["name"]
template = '#'+templateList[product['temp_id']]['id'].to_s + templateList[product['temp_id']]["name"]
#准备商品运费及配送时间说明,强制更新
shipping = ''
queryRds("select description from crmeb.eb_shipping_templates where id = #{product['temp_id']}").each do |qr|
shipping = qr['description']
break
end
keywords.store('shipping',shipping) if shipping
keywords.store('merchant',merchant) if merchant
keywords.store('template',template) if template
#重置排序顺序,当前公式为销量*售价,假设常用数为10000-100000,大100000的,大于部分取2的对数后乘1000, 可以再加个库存的对数
base = product['sales']*product['price']
exp = base > 100000 ? Math.log(base,2)*1000 : 0
sort = base > 100000 ? (100000 + exp*1000).to_i : base.to_i
cate_ids = []
product['cate_id'].split(',').uniq.each { |cat| cate_ids += [cat] }
cate_ids = cate_ids - [2]
cate_id = cate_ids.join(',')
tags += ['产地直发'] if product['store_name'].include? '产地直发'
keywords.store('tags',tags.uniq) if tags
#移除产地直发目录3及超值好物目录2,但保留标签
#如果是代销商品,同步其所有的库存
sync_consign_product product if product['source_pid'] > 0
#这里先弥补一个stock缺陷,对于普通商品(type==0)已知系统取消未支付订单时,除了vsotck,实际stock未回撤。在修正这一缺陷之前,在本定时任务中强制更新
res = queryRds "select sum(stock) as vstock from crmeb.eb_store_product_attr_value where product_id = #{product['id']} and type = 0"
vstock = 0
res.each do |r|
vstock = r['vstock']
vstock = 0 if vstock.nil? #避免不连续的产品数据
break;
end
print "v" if vstock != product['stock']
is_show = vstock > 0 ? 1 : 0
is_show = 0 if (product['store_info'].include? '[ZY]') && vstock > 0 && product['is_show'] == 0 #专营商品如果有库存且被手工下架状态,则保持下架以处理小程序审核时的经营许可检查
is_show = 0 if merchantList[product['mer_id']]['status'] == 'close'
sql = "update crmeb.eb_store_product set keyword='#{@rds.escape keywords.to_json}', stock = #{vstock}, is_show=#{is_show}, cate_id = '#{cate_id}', sort=#{sort} where id = #{product['id']}"
idx += 1
queryRds sql
print "."
end
puts "\ndone. #{idx}"
end
#由于某些原因,例如微信地址格式的问题,某些用户提交订单时的地址中,cityid为零
def fix_city_id
usqls = []
puts "fixing city_id for address..."
inq = "select id, uid, city from crmeb.eb_user_address where city_id = 0;"
queryRds(inq).each do |q|
addr_id = q['id']
city_name = q['city']
city_id = q['city_id']
sql = "select city_id from crmeb.eb_system_city where name = '#{city_name}';"
queryRds(sql).each do |u|
city_id = u['city_id']
break
end
usqls += ["update crmeb.eb_user_address set city_id = #{city_id} where id = #{addr_id}; ##{Time.now.to_s}"]
end
if usqls.size > 0
append_lines_to_fix usqls, 'cityid_fix_sql.log'
commitTrans usqls
end
puts "done."
end
def reset_products_ feature
#不需要按照商家分配权重的版块,无需每个商家去循环
if feature == 'is_benefit' || feature == 'is_hot'
reset_mer_products_ 0, 1, feature
return
end
merIds = {}
total_weight = 0.0
merchantList.each do |mer_id, mer_stat|
total_weight += mer_stat['weight']
merIds.store(mer_id, mer_stat['weight'])
end
merIds.each do |mer_id, weight|
reset_mer_products_ mer_id, weight/total_weight, feature
end
end
def reset_mer_products_ mer_id, weight, feature
using_merid = true
using_merid = false if mer_id == 0 #如果mer_id为零,则不按mer_id分配
case feature
when 'is_new' #节气风物首发新品
where = "where is_show = 1 and source_pid = 0"
where = "where is_show = 1 and source_pid = 0 and mer_id = #{mer_id}" if using_merid
sort='order by add_time desc'
limit = (24*weight).to_i
when 'is_benefit' #临期特惠
where="where is_show = 1 and ( store_name like '%特惠%' or name_prefix like '%特惠%' )"
where="where is_show = 1 and ( store_name like '%特惠%' or name_prefix like '%特惠%' ) and mer_id = #{mer_id}" if using_merid
sort='order by stock desc'
limit = (16*weight).to_i
when 'is_hot' #巢中好物热门榜单 groom_list
where = "where is_show = 1"
where = "where is_show = 1 and mer_id = #{mer_id}" if using_merid
where = "" #按照实际设置来
sort='order by sort desc'
limit = (16*weight).to_i
when 'is_best' #超值推荐有口皆碑
where = 'where is_show = 1'
where = "where is_show = 1 and mer_id = #{mer_id}" if using_merid
where = "" #按照实际设置来
sort='order by sales desc'
limit = (24*weight).to_i
when 'is_good' #商品详情优品推荐
where = 'where is_show = 1'
where = "where is_show = 1 and mer_id = #{mer_id}" if using_merid
where = "" #按照实际设置来
sort='order by sort desc'
limit = (16*weight).to_i
else
return
end
#只按照原义设置相关标志
if !where.to_s.empty?
#重置
sql = "update crmeb.eb_store_product set #{feature} = 0 where 1=1;"
sql = "update crmeb.eb_store_product set #{feature} = 0 where 1=1 and mer_id = #{mer_id};" if using_merid
queryRds sql
queryRds "update crmeb.eb_store_product set #{feature} = 1 #{where} #{sort} limit #{limit*2};"
end
#获得已设置为当前FEATURE商品
pids = []
sql = "select id from crmeb.eb_store_product where is_show=1 and #{feature} = 1;"
sql = "select id from crmeb.eb_store_product where is_show=1 and mer_id = #{mer_id} and #{feature} = 1;" if using_merid
queryRds(sql).each { |q| pids += [q['id']] }
#获得已设置为当前FEATURE商品的代销商品
to_remove = []
sql = "select id, source_pid from crmeb.eb_store_product where is_show=1 and #{feature} = 1 and source_pid > 0;"
sql = "select id, source_pid from crmeb.eb_store_product where is_show=1 and mer_id = #{mer_id} and #{feature} = 1 and source_pid > 0;" if using_merid
#如果代销商品的源商品也被设置为了当前FEATURE商品则将其从商品列表中除去
queryRds(sql).each { |q| to_remove += [q['id']] if pids.include? q['source_pid'] }
pids = pids.reverse - to_remove #当order by sort desc limit 10的时候,sort最大的行在数组的最后,所以要把他们反过来用
plist = pids[0..limit-1].join(',')
#将结果列表中的pid置为相关feature启用
if using_merid
queryRds "update crmeb.eb_store_product set #{feature} = 0 where mer_id = #{mer_id};"
queryRds "update crmeb.eb_store_product set #{feature} = 1 where mer_id = #{mer_id} and id in (#{plist});" if plist.size > 0
else
queryRds "update crmeb.eb_store_product set #{feature} = 0 where 1=1;"
queryRds "update crmeb.eb_store_product set #{feature} = 1 where id in (#{plist});" if plist.size > 0
end
end
def reset_item_flags
puts "reset item flags..."
#每个商家设置的热门榜单is_hot,促销单品is_benefit,精品推荐is_best,首发新品is_new,优品推荐is_good不超过12及(1+12/商家数)中的最小值
begin
['is_hot','is_new','is_benefit','is_best','is_good'].each do |feature|
puts "reset #{feature} ..."
reset_products_ feature
puts "done"
end
rescue => e
puts e
exit
end
end
def update_activities act_type
usqls = []
data_value = {}
#准备查询产品名字以及关闭滚动新闻提示的语句
#吐个槽,update eb_system_group_data 时候总是报没有权限错误,复制到navicat看着一模一样的就是一个行一个不行,motherfuck
case act_type
when :combination
puts "update group-buy activity..."
data_id = 191
act_news = '开团提醒:PRODUCT_NAME 等正在拼团共购'
inq = "select id, title as name, info as intro, image from crmeb.eb_store_combination where is_del = 0 and is_show = 1 order by add_time desc"
data_value = {"info":{"type":"input","value":""},"url":{"type":"select","value":"/pages/activity/goods_combination/index"},"show":{"type":"radio","value":"2"},"wap_url":{"type":"select","value":"/activity/group"}}
usql = "UPDATE crmeb.eb_system_group_data SET status = 2 WHERE id = #{data_id};" #status == 1 启用 ==2 关闭
queryRds usql
when :seckill
puts "update flash-buy activity..."
data_id = 194
act_news = '秒杀提醒:PRODUCT_NAME 等正在限量秒杀'
inq = "select id, title as name, info as intro, image from crmeb.eb_store_seckill where status = 1 and is_del = 0 and is_show = 1 order by add_time desc"
data_value = {"info":{"type":"input","value":""},"url":{"type":"select","value":"/pages/activity/goods_seckill/index"},"show":{"type":"radio","value":"2"},"wap_url":{"type":"select","value":"/activity/goods_seckill"}}
usql = "UPDATE crmeb.eb_system_group_data SET `status` = 2 WHERE id = #{data_id};"
queryRds usql
when :bargain
puts "update group-bargain activity..."
data_id = 195
act_news = '砍价提醒:PRODUCT_NAME 等正在限量砍价'
inq = "select id, title as name, info as intro, image from crmeb.eb_store_bargain where is_del = 0 and status = 1 order by add_time desc"
data_value = {"info":{"type":"input","value":""},"url":{"type":"select","value":"/pages/activity/goods_bargain/index"},"show":{"type":"radio","value":"2"},"wap_url":{"type":"select","value":"/activity/bargain"}}
usql = "UPDATE crmeb.eb_system_group_data SET `status` = 2 WHERE id = #{data_id};"
queryRds usql
when :last_sale
puts "unsupported activity type"
act_news = '成交动态:CUSTOMER_NAME 刚刚购买了PRODUCT_NAME'
return
when :last_comment
return
end
queryRds(inq).each do |qi|
#只需要第一个记录结果
product_name = qi['name'].split(' ')[0].split(' ')[0].split('(')[0].split('(')[0]
intro = qi['intro']
image = qi['image']
data_value[:info][:value]=act_news.sub("PRODUCT_NAME",product_name);
usql = "UPDATE crmeb.eb_system_group_data SET `status` = 1, value = '#{@rds.escape data_value.to_json}' where id = #{data_id};"
queryRds usql
break
end
puts "done."
end
#to export stocks execel and change banner for closed store
def check_store_status
data_value = {"name":{"type":"input","value":"店铺盘点版头,修改店名启用,例如'存在小铺正在盘点|19点恢复上架,不便处请多包涵'"},"url":{"type":"input","value":"\/pages\/news_details\/index?id=2"},"pic":{"type":"upload","value":"https:\/\/oss.foodtrust.cn\/\/90631202009080935227888.jpg"},"wap_url":{"type":"input","value":"\/news_detail\/2"}}
merchantList.each do |mer_id, mer_stat|
usql = "UPDATE crmeb.eb_system_group_data SET `status` = 2, value = '#{@rds.escape data_value.to_json}' where mer_id = #{mer_id} and value like '%盘点%';"
if mer_stat['status'] == 'close'
puts "#{mer_id} is closed, export stocks"
export_merchant_stocks mer_id
data_value[:name][:value] = "#{mer_stat['name']} | #{mer_stat['intro']}" #一般来说这时候分离出来的name 'XXX正在盘点'
usql = "UPDATE crmeb.eb_system_group_data SET `status` = 1, value = '#{@rds.escape data_value.to_json}' where mer_id = #{mer_id} and value like '%盘点%';"
end
queryRds usql
end
end
#to create template for new created store
def create_tempaltes_for_new_store
sql = "update crmeb.eb_system_store set tags='TAGS' where id=MER_ID;" #我也不知道为啥直接生产sql代码会出权限错,MD
merchantList.each do |mer_id, state|
tags = state['tags']
if tags.include? 'TCT'
store_name = state['name']
clear_mer_templates mer_id
copy_mer_templates 0, mer_id
create_group_data mer_id
create_category mer_id, store_name
new_tags = ((tags.split('|') - ['TCT'])).join('|')
queryRds sql.gsub('TAGS',new_tags).gsub('MER_ID',mer_id.to_s)
end
end
end
def regen_cate_ids
product_ids = []
cates_ids = []
inq = "select id, cate_id from crmeb.eb_store_product"
queryRds(inq).each do |q|
product_ids += [q['id']]
cates_ids += [ q['cate_id'] ]
end
product_ids.size.times do |idx|
queryRds "delete from crmeb.eb_store_product_cate where product_id = #{product_ids[idx]}"
cates_ids[idx].split(',').each do |cat_id|
queryRds "insert into crmeb.eb_store_product_cate (product_id, cate_id, add_time) values (#{product_ids[idx]}, #{cat_id}, unix_timestamp(#{Time.now.to_i}) );"
end
end
end
#同步代销商品的库存,consign_pid为代销商品id,source_pid为源商品id, is_show=1
def sync_consign_product product
consign_pid = product['id']
source_pid = product['source_pid']
#需要同步consign_ratio
sql = "UPDATE crmeb.eb_store_product t1 join ( select consign_ratio, price, id from crmeb.eb_store_product where id = #{source_pid} limit 1)
as t2 on t1.id = #{consign_pid} set t1.consign_ratio = t2.consign_ratio, t1.price = t2.price"
queryRds sql
consign_avs = []
queryRds("select * from crmeb.eb_store_product_attr_value where product_id = #{consign_pid} and type = 0").each { |q| consign_avs += [q] }
source_avs = []
queryRds("select * from crmeb.eb_store_product_attr_value where product_id = #{source_pid} and type = 0").each { |q| source_avs += [q] }
consign_uniques = []
consign_avs.each { |av| consign_uniques += [av['unique']] }
#如果是多规格的商品,则应同步attr和attr_result(attr用于在后台商品编辑时获得有效的规格值来索引attr_value(或者是attr_result?)
if consign_avs.size > 1 #TBD
queryRds("select result from crmeb.eb_store_product_attr_result where product_id = #{source_pid} and type = 0").each do |qar|
result = qar['result']
queryRds "update crmeb.eb_store_product_attr_result set result = '#{@rds.escape result}' where product_id = #{consign_pid} and type = 0"
break #result应该只有一条的
end
#更新attr,这里有一个bug,如果attr数量增加减少,或者名字改变则不工作
queryRds("select attr_name, attr_values from crmeb.eb_store_product_attr where product_id = #{source_pid} and type = 0").each do |qar|
attr_name = qar['attr_name']
attr_values = qar['attr_values']
queryRds "update crmeb.eb_store_product_attr set attr_values = '#{attr_values}' where product_id = #{consign_pid} and type = 0 and attr_name = '#{attr_name}'"
end
end
if consign_avs.size != source_avs.size
#如果选项数量不一致,则重置所有选项
queryRds "delete from crmeb.eb_store_product_attr_value where product_id = #{consign_pid} and type = 0;"
sql = "insert into
crmeb.eb_store_product_attr_value (
product_id, suk, stock, sales, price, image, `unique`, cost,
bar_code, ot_price, weight, volume, brokerage, brokerage_two, `type`, quota, quota_show)
select
#{consign_pid} as product_id, suk, stock, 0 as sales, price, image, concat(`unique`,'-#{source_pid}') as `unique`, cost,
bar_code, ot_price, weight, volume, brokerage, brokerage_two, 0 as `type`, quota, quota_show
from
crmeb.eb_store_product_attr_value
where
product_id = #{source_pid} and `type` = 0"
queryRds sql
else
#如果选项数量一致,则更新代销商品选项中的相关内容
source_avs.size.times do |idx|
spav = source_avs[idx] #source product attribute value
stock = spav['stock']
cuniq = consign_avs[idx]['unique']
sql = "update crmeb.eb_store_product_attr_value
set
suk = '#{spav['suk']}',
stock = #{spav['stock']},
price = #{spav['price']},
cost = #{spav['cost']},
bar_code = '#{spav['bar_code']}',
ot_price = #{spav['ot_price']},
weight = #{spav['weight']},
volume = #{spav['volume']},
brokerage = #{spav['brokerage']},
brokerage_two = #{spav['brokerage_two']},
quota = #{spav['quota']},
quota_show = #{spav['quota_show']}
where
`unique` = '#{cuniq}'"
queryRds sql
end
end
end
#product={}
#product.store('id',2258)
#product.store('source_pid',2063)
#sync_consign_product_stock product
reset_item_flags
puts "update stocks for closed store"
load 'update-stocks.rb'
puts "export stocks for closed store"
check_store_status
create_tempaltes_for_new_store
puts "update tags for products..."
products = get_products_ ""
update_tags_for products
fix_city_id
[:combination, :seckill, :bargain].each { |act| update_activities act }
puts "update coupons due to sepearte merchant"
load 'update-coupons.rb'
puts "update consigns due for all orders"
load 'update-consigns.rb'

Comment ( 0 )

Sign in for post a comment

1
https://gitee.com/foodtrust/oms2.git
git@gitee.com:foodtrust/oms2.git
foodtrust
oms2
oms2
master

Search