                
--更新由特殊操作(如数据转换或Excel模版数据导入不正确导入基本数据不完善)
IF NOT EXISTS(SELECT 1 FROM t_bd_branch_area with(nolock)  WHERE branch_clsno = '00' AND branch_flag = 'B') 
BEGIN INSERT INTO t_bd_branch_area(branch_clsno, branch_clsname, branch_flag, branch_cls_parent, branch_display_flag) VALUES('00','默认区域','B','','1') END
GO

IF NOT EXISTS(SELECT 1 FROM t_bd_item_cls with(nolock)  WHERE item_clsno = 'LB') BEGIN INSERT INTO t_bd_item_cls(item_clsno, item_clsname, item_flag, cls_parent) VALUES('LB','其它类别','0','') END
GO


IF NOT EXISTS(SELECT 1 FROM t_bd_item_brand with(nolock)  WHERE item_brand = 'PP') BEGIN INSERT INTO t_bd_item_brand(item_brand, item_brandname, item_flag, brand_parent) VALUES('PP','其它品牌','0','') END
GO
      
 
IF NOT EXISTS(SELECT 1 FROM t_bd_supcust_cls with(nolock)  WHERE supcust_clsno = '00' and supcust_flag = 'C') BEGIN INSERT INTO t_bd_supcust_cls(supcust_clsno, supcust_clsname, supcust_flag) VALUES('00','客户默认区域','C') END
GO

IF NOT EXISTS(SELECT 1 FROM t_bd_supcust_cls with(nolock)  WHERE supcust_clsno = 'QY' and supcust_flag = 'S') BEGIN INSERT INTO t_bd_supcust_cls(supcust_clsno, supcust_clsname, supcust_flag) VALUES('QY','其它区域','S') END
GO


--供应商客户默认区域信息
UPDATE t_bd_supcust_info SET region_no = '00' WHERE (region_no = '' OR region_no is NULL) AND supcust_flag = 'C'

UPDATE t_bd_supcust_info SET region_no = 'QY' WHERE (region_no = '' OR region_no is NULL) AND supcust_flag = 'S'

---类别和品牌提成率不超过1
UPDATE t_bd_item_cls SET comp_rate = 0 WHERE comp_rate >= 1 OR comp_rate < 0 OR comp_rate is NULL
UPDATE t_bd_item_brand SET comp_rate = 0 WHERE comp_rate >= 1 OR comp_rate < 0 OR comp_rate is NULL


   
---商品档案进货规格、配送规格以及包装率
UPDATE t_bd_item_info SET purchase_spec = 1 WHERE purchase_spec <= 0 OR purchase_spec is NULL
 

UPDATE t_bd_item_info SET shipment_spec = 1 WHERE shipment_spec <= 0 OR shipment_spec is NULL
               
UPDATE t_bd_item_info SET package_ratio = 1 WHERE package_ratio <=0 OR package_ratio IS NULL
                   

--商品档案按比率提成时不允许超过1
UPDATE t_bd_item_info SET return_rate = 0 WHERE return_rate is NULL OR return_rate < 0 OR (return_rate >= 1 AND dpfm_type = 0)
       

                    
--更新商品档案税率(进项税和销项税)
UPDATE t_bd_item_info SET purchase_tax = 0.13 WHERE purchase_tax IS NULL OR purchase_tax < 0
                 


UPDATE t_bd_item_info SET sale_tax = 0.13 WHERE sale_tax IS NULL OR sale_tax < 0
                 

UPDATE dbo.t_bd_item_info SET pro_code1 = ISNULL(pro_code1, ''), pro_code2 = ISNULL(pro_code2, ''), pro_code3 = ISNULL(pro_code3, ''), pro_code4 = ISNULL(pro_code4, ''), pro_code5 = ISNULL(pro_code5, ''), pro_code6 = ISNULL(pro_code6, ''), pro_code7 = ISNULL(pro_code7, ''), pro_code8 = ISNULL(pro_code8, ''), pro_code9 = ISNULL(pro_code9, '') 
where ( ( pro_code1 is null ) or ( pro_code2 is null ) or ( pro_code3 is null ) or ( pro_code4 is null ) or ( pro_code5 is null ) or ( pro_code6 is null ) or ( pro_code7 is null ) or ( pro_code8 is null ) or ( pro_code9 is null ) ) 
				                 
        

--更新是否管理库存(捆绑商品、联营商品不需要管理库存)
UPDATE t_bd_item_info SET item_stock = '0' WHERE (item_sup_flag = 'C' OR combine_sta = '1') and (item_stock is null or item_stock != '0') 
                 
----小象称重5 混售商品不管理库存且库存方式为标准
IF EXISTS(SELECT 1 FROM t_sys_system where sys_var_id like 'TradeVersions' and sys_var_value = 'A')
UPDATE t_bd_item_info SET item_stock = '0',stock_type='B' WHERE  combine_sta = 'A'
        
                

					
UPDATE t_bd_item_info SET item_sup_flag  = case ISNULL(sup.sale_way,'') when 'D' then 'A' when 'E' then 'A' WHEN '' THEN 'A' ELSE sup.sale_way END FROM t_bd_supcust_info sup with(nolock)  
WHERE main_supcust = sup.supcust_no AND sup.supcust_flag = 'S' AND ( item_sup_flag IS NULL OR item_sup_flag <> sup.sale_way )
         
--如果是商品和供应商导入要增加处理联营不管理库存
UPDATE t_bd_item_info SET item_stock = 0 FROM t_bd_supcust_info sup with(nolock) WHERE main_supcust = sup.supcust_no AND sup.supcust_flag = 'S' AND sup.sale_way = 'C' AND item_stock <> 0
            
----按商品档案主供应商生成供货关系 N230309-408090
INSERT INTO t_bd_supcust_item (item_no,supcust_no,sale_way,supcust_flag ) 
SELECT a.item_no, a.main_supcust, b.sale_way, b.supcust_flag
FROM t_bd_item_info a with(nolock) join t_bd_supcust_info b  with(nolock) on (a.main_supcust = b.supcust_no AND b.supcust_flag = 'S') 
left join  t_bd_supcust_item c  with(nolock) on (a.item_no = c.item_no AND a.main_supcust = c.supcust_no AND c.supcust_flag = 'S')
WHERE  c.item_no is null

                    
--补全机构价格表信息
      
INSERT INTO t_pc_branch_price (branch_no,item_no,price, base_price,sale_price,vip_price,scheme_price,vip_price2,vip_price3,
vip_price4,vip_price5,price_list,sale_flag,base_price1, base_price2, base_price3, base_price4, base_price5, base_price6, base_price7, base_price8)
SELECT b.branch_no ,a.item_no, price = (CASE b.trade_type WHEN '8' THEN a.scheme_price ELSE a.price END),
a.base_price,a.sale_price,a.vip_price,a.scheme_price,a.vip_price2,a.vip_price3,
a.vip_price4,a.vip_price5,'111111111',a.sale_flag,a.base_price1, a.base_price2, a.base_price3, a.base_price4, a.base_price5, a.base_price6, a.base_price7, a.base_price8
FROM t_bd_item_info a with(nolock) inner join t_bd_branch_info b with(nolock) on( b.trade_type in ('0','1', '2', '3', '8') and 1=1) left join t_pc_branch_price c with(nolock)  on c.branch_no = b.branch_no and c.item_no= a.item_no
WHERE  c.branch_no is null  
     


---更新付款方式是否积分信息
UPDATE t_bd_payment_info SET jf_flag = '0' WHERE jf_flag is NULL
 

--帐户代码中默认的付款方式不可缺(特别是老版本升级上来后特殊的付款方式会缺失)
IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'WXF' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo)  VALUES('WXF', '0', '微支付', 1, 0, '1', '系统默认') END
        

IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'WZF' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('WZF', '0', '微支付', 1, 0, '1', '系统默认') END
            


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'ZFB' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('ZFB', '0', '支付宝', 1, 0, '1', '系统默认') END
           

                                
IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'CFP' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('CFP', '0', '项目储值付款', 1, 0, '1', '系统默认') END
                


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'CHG' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('CHG', '0', '抹零', 1, 0, '0', '系统默认') END
                


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'CHQ' AND pay_flag = '0')
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('CHQ', '0', '人民币支票', 1, 0, '1', '系统默认') END
     


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'CRD' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('CRD', '0', '人民币信用卡', 1, 0, '1', '系统默认') END
          

                
IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'GZH' AND pay_flag = '0')
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('GZH', '0', '挂帐', 1, 0, '1', '系统默认') END
           

IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'SHC' AND pay_flag = '0')
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('SHC', '0', '购物券(找)', 1, 0, '0', '系统默认') END
      
                
IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'SHP' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('SHP', '0', '购物券', 1, 0, '1', '系统默认') END
       

IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'VIP' AND pay_flag = '0') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('VIP', '0', '会员积分', 1, 0, '0', '系统默认') END
          

IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'DJF' AND pay_flag = '0')
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('DJF', '0', '订金付款', 1, 0, '1', '系统默认') END
             


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'YF' AND pay_flag = '4') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('YF', '4', '预付款', 1, 0, '1', '系统默认') END
               
                          
IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'YS' AND pay_flag = '4') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('YS', '4', '预收款', 1, 0, '1', '系统默认') END
              


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'ORD' AND pay_flag = '6') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('ORD', '6', '订货订金', 1, 0, '1', '系统默认') END
           


IF NOT EXISTS(SELECT 1 FROM t_bd_payment_info with(nolock)  WHERE pay_way = 'CZ' AND pay_flag = '6') 
BEGIN INSERT INTO t_bd_payment_info(pay_way,pay_flag,pay_name,rate,default_amt,jf_flag,pay_memo) VALUES('CZ', '6', '储值卡充值', 1, 0, '1', '系统默认') END
         



---操作员所属机构信息调整(因不同产品机构位数不相同，应同步考虑)
UPDATE t_sys_operator SET branch_no = '0000' WHERE len(branch_no) <> '4'