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)