CompanyQuery 改造

[chenchongqi]

• 表改造

新增：ent_company_business_status 定时任务维护，商家记录更新的时候也同时更新一下这个表:

-- Table: ent_company_business_status

-- DROP TABLE ent_company_business_status;

CREATE TABLE ent_company_business_status
(
  company_id bigint NOT NULL,
  recommend_status integer DEFAULT 0, -- 首页的推荐商家状态...
  last_update_date timestamp without time zone, -- 最后更新时间
  CONSTRAINT ent_company_business_status_pkey PRIMARY KEY (company_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ent_company_business_status
  OWNER TO cyp_nw_app;
COMMENT ON TABLE ent_company_business_status
  IS '面向业务的状态，例如可以在前台显示，在报价库显示，在栏目页显示等等，由定时任务负责跟据ent_company的类型(type)，状态(status)，等级(vip rank)组合。为v6.0版本新建，尚未开发完成。';
COMMENT ON COLUMN ent_company_business_status.recommend_status IS '0：缺省
1：
ent_company.TYPE<>Company.TYPE_STORE(<>3,非网店)  AND ent_company.VIP_RANK> Company.VIP_RANK_FREE(>0,非免费商家)  AND ent_company.STATUS>=Company.STATUS_NO_AUDIT(>=0,非禁用或者删除商家)';

-- Index: idx_company_id_recommend_status

-- DROP INDEX idx_company_id_recommend_status;

CREATE INDEX idx_company_id_recommend_status
  ON ent_company_business_status
  USING btree
  (company_id , recommend_status );

定时任务：

update ent_company_business_status a
set recommend_status = case when (b.TYPE<>3  AND b.VIP_RANK> 0 AND b.STATUS>=0) then 1 else 0 end,
last_update_date = now()
from ent_company b
where a.company_id = b.id

insert into ent_company_business_status(company_id, recommend_status, last_update_date) 
select a.id, case when (a.TYPE<>3  AND a.VIP_RANK> 0 AND a.STATUS>=0) then 1 else 0 end,now()
from ent_company a
where not exists (select s.company_id from ent_company_business_status s where s.company_id = a.id)

新增：ent_company_product_line,商家发布操作后也更新一下这个表

-- Table: ent_company_product_line

-- DROP TABLE ent_company_product_line;

CREATE TABLE ent_company_product_line
(
  company_id bigint,
  big_type_id bigint,
  small_type_id bigint,
  brand_id bigint,
  last_update_date timestamp without time zone
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ent_company_product_line
  OWNER TO cyp_nw_app;
COMMENT ON TABLE ent_company_product_line
  IS '商家和产品线关系表';

-- Index: idx_company_product_line

-- DROP INDEX idx_company_product_line;

CREATE INDEX idx_company_product_line
  ON ent_company_product_line
  USING btree
  (company_id , big_type_id , small_type_id , brand_id );

定时任务(原来的查询里，还要加上对ent_product_item.status的判断，但是这个判断是否有必要呢？打个比方说有个商家他这个产品小类的全部商品都被否了，我们仍然认为他有在经营这类商品，对系统有什么影响么？)

insert into ent_company_product_line(company_id,brand_id,small_type_id, big_type_id, last_update_date)
select distinct a.user_id as company_id, a.type_id as brand_id, b.parent_id as small_type_id, c.parent_id as big_type_id, now()
from ent_product a,v_pdl_product_type b, v_pdl_product_type c
where a.type_id = b.id and b.parent_id = c.id and not exists (
select z.company_id from ent_company_product_line z
where z.company_id = a.user_id and z.brand_id = a.type_id and z.small_type_id = c.id and z.big_type_id = c.parent_id
)
-- 删除不符合的关系，包括已删除、否决等
delete from ent_company_product_line a
where not exists( 
select b.id from ent_product b, ent_product_item c
where b.id = c.company_product_id and a.company_id = b.user_id and a.brand_id = b.type_id
and c.status <=1
)