dpMGm0GR

1LOF is FL with fixed corp-cc-deliver_to headers.
2LOF cares more about UOM/QOE
3LOF is replenish-able item order.
4UNDERSTAND LOF TO LOAD CUSTOM SCRIPT IF CUSTOMER REQUIRED
5 
6sample load FL for cookeville https://pastebin.com/2w8YZqky
7sample load LOF for stpeter https://pastebin.com/v7wBiJjm
8 
9Steps
101. scp file lên môi trường cần issue
11 
122. Load file vào, header lẫn item
13iload stluke_kansas_city -i TemplateAndCC.csv -t temp_20170626_fl_header
14iload stluke_kansas_city -i TemplateAndItem.csv -t temp_20170626_fl_item
15 
16----Map facility_id, cost_center_id in MSSS and update for tmp favorite cost center
17DROP TABLE IF EXISTS tmp_fav_cc;
18 
19SELECT * INTO tmp_fav_cc FROM temp_20170626_fl_header;
20 
21-- update tmp_fav_cc set cost_center_id = '00' || cost_center_id where length(cost_center_id) < 5;
22-- fix update tmp_fav_cc set cost_center_id = substring(cost_center_id from 2 for 999) where length(cost_center_id) = 6;
23ALTER TABLE tmp_fav_cc
24add column facility_id int,
25add column cc_id int;
26 
27update tmp_fav_cc tfcc
28set facility_id = f.id
29from facilities f
30where tfcc.corp_id = f.facility_number and tfcc.corp_id is not null and tfcc.corp_id != ''
31and f.is_gpo = false;
32 
33update tmp_fav_cc tfcc
34set cc_id = cc.id
35from cost_centers cc
36where cc.facility_id = tfcc.facility_id and cc.account_number = tfcc.cost_center_id and tfcc.cost_center_id is not null and tfcc.cost_center_id != '';
37 
38-- Map master_item_id in MSSS and update for tmp_fav_items
39DROP TABLE IF EXISTS tmp_fav_items;
40SELECT * into tmp_fav_items  FROM temp_20170626_fl_item;
41 
42ALTER TABLE tmp_fav_items
43add column master_item_id int;
44 
45update tmp_fav_items tfi
46set master_item_id = mi.id
47from master_items mi
48where
49  trim(upper(tfi.item_id)) = trim(upper(mi.org_item_id))
50  and trim(upper(tfi.vendor_code)) = trim(upper(mi.vendor_code))
51  and tfi.item_id is not null and tfi.item_id != ''
52  and tfi.vendor_code is not null and tfi.vendor_code != ''
53  and mi.is_discerned;
54--- end
55 
56alter table tmp_fav_cc
57rename column req_no to req_number;
58alter table tmp_fav_items
59rename column req_no to req_number;
60 
614. ruby script/console
62 
63DB.connect_by_org_id org_id
64sql_arr = []
65 
66fav_ccs = FavoriteList.connection.execute("SELECT DISTINCT req_number
67 FROM tmp_fav_cc;")
68p "-------fav_ccs: #{fav_ccs.count}"
69 
70fav_ccs.each do |f_cc|
71  p f_cc["req_number"]
72 
73  # Get cost center by req_number
74  cc_ids = FavoriteList.connection.execute("select cc_id
75                                 from tmp_fav_cc
76                                 where req_number=$$#{f_cc["req_number"]}$$ AND cc_id is not null;").map{|i| i["cc_id"].to_i}
77 
78  is_published = cc_ids.blank? ? false : true
79  if FavoriteList.where(:name => f_cc["req_number"]).present?
80     fav = FavoriteList.where(:name => f_cc["req_number"]).first
81     FavoriteCostCenter.where(:favorite_id => fav.id).destroy_all
82     cc_ids.each do |cc_id|
83        ::FavoriteCostCenter.create({
84          :favorite_id => fav.id,
85          :cost_center_id => cc_id
86        })
87     end
88  else
89    fav = FavoriteList.create({
90            :name => f_cc["req_number"],
91            :org_user_id => 0,
92            :published => is_published,
93            :is_auto_generated => false,
94            :public_all => false,
95            :share_mode => is_published ? 3 : 1
96          })
97 
98    # create relationship fav and cost center
99    cc_ids.each do |cc_id|
100      ::FavoriteCostCenter.create({
101        :favorite_id => fav.id,
102        :cost_center_id => cc_id
103      })
104    end
105  end
106 
107 
108  items = FavoriteList.connection.execute("SELECT DISTINCT master_item_id
109                                         FROM tmp_fav_items
110                                         WHERE req_number = $$#{f_cc["req_number"]}$$ AND master_item_id is not null;").map{|i| i["master_item_id"]}
111 
112  p "---------items: #{items.count}"
113  items.each do |item|
114    fav_item = FavoriteItem.new
115    fav_item[:favorite_list_id] = fav.id
116    fav_item[:source_id] = item.to_i
117    fav_item[:source_type] = "vim"
118 
119    sql = FavoriteItem.to_sql(fav_item, FavoriteItem)
120    sql_arr << sql
121  end
122end
123 
124sql_query = sql_arr.join(";"); nil
125FavoriteItem.connection.execute(sql_query)
126 
127 
128 
129 
130 
131 
132 
133 
134( custom request, export FL with corps )
135 
136copy (
137select distinct fav.name, f.facility_number as corporation_id, f.facility_name as corporation_name, cc.name as cost_center_name, cc.account_number as cc_acct_no, ou.first_name || ' ' || ou.last_name as created_by, ou.email
138from favorites fav
139join favorite_items fi on fi.favorite_list_id = fav.id
140join favorite_cost_centers fcc on fcc.favorite_id = fav.id
141join cost_centers cc on fcc.cost_center_id = cc.id
142join master_items mi on fi.source_id = mi.id
143join facility_items ffi on ffi.master_item_id = mi.id and ffi.id != 1
144join facilities f on cc.facility_id = f.id AND f.status = 't' AND f.is_gpo = 'f' AND ffi.facility_id = f.id
145left join org_users ou on ou.id = fav.org_user_id
146where fav.fav_type = 0 AND fi.source_type = 'vim' AND mi.is_discerned order by fav.name
147) to '/tmp/maintenance/20171110/stluke_kc_20171110_headers.csv' CSV header;