DELIMITER $$

USE `topsale`$$

DROP PROCEDURE IF EXISTS `sale_proce`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sale_proce`(IN countryList VARCHAR() ,IN beg VARCHAR(),IN endd VARCHAR())

BEGIN

DELETE  FROM sale_record WHERE country_id IN(countryList) AND datadate BETWEEN beg AND endd;

INSERT INTO sale_Record(branch_model,hq_model,line,size,spec,country_id,center,country,datadate,saleQty)

SELECT t.branch_model,t.hq_model,t.product_line,t.size,t.PRODUCT_SPEC_ID,pa.`COUNTRY_ID`,center.PARTY_NAME center,coun.`PARTY_NAME` country,

 DATE_FORMAT(t.`datadate`,'%Y-%m-01') DATE,COALESCE(SUM(t.`h_quantity`),0) saleQty

 FROM vive_tv_sale t

JOIN shop_info si ON si.`SHOP_ID`=t.`shop_id`  AND si.flag=0

JOIN party pa ON pa.`PARTY_ID`=si.`PARTY_ID` AND pa.flag=0

JOIN (SELECT * FROM party) coun ON coun.party_id=pa.country_id

JOIN (SELECT * FROM party) center ON center.party_id=coun.PARENT_PARTY_ID

WHERE pa.country_id IN(countryList)

AND  t.datadate BETWEEN beg AND endd

GROUP BY pa.`COUNTRY_ID`,t.model,DATE_FORMAT(t.`datadate`,'%Y%m')

ORDER BY center.PARTY_NAME,coun.`PARTY_NAME`,DATE_FORMAT(t.`datadate`,'%Y%m');

    END$$

DELIMITER ;

　　　　　UPDATE vive_tv_sale vts,

        (SELECT a.id, pa.`COUNTRY_ID`,pa.`PARTY_ID`,si.`SHOP_ID`,tm.`hq_model`,tm.`branch_model`,

        pr.`product_line`,pr.`size`,pr.`PRODUCT_SPEC_ID`

        ,co.all_products_coefficient coeff

        ,'',si.`CUSTOMER_ID`

         FROM  vive_tv_sale a,shop_info si,party pa,t_modelmap tm,product pr ,`coefficient` co

         WHERE a.shop_id=si.shop_id

         AND a.model=tm.branch_model

         AND si.`PARTY_ID` = pa.`PARTY_ID`

         AND co.`country` = pa.`COUNTRY_ID`

         AND tm.`party_id` = pa.`COUNTRY_ID`

         AND pr.`head_type_id` = 1

         AND a.country=#{country}

        AND a.datadate BETWEEN  #{beginDate} AND #{endDate}

         AND tm.`hq_model` = pr.`PRODUCT_model`

         AND si.flag = 0

         AND pa.flag = 0

         AND tm.flag = 0

         AND pr.flag = 0) v_sl

        SET

        vts.`country_id` = v_sl.COUNTRY_ID

        ,vts.`party_id` = v_sl.PARTY_ID

        ,vts.`hq_model` = v_sl.hq_model

        ,vts.`branch_model` = v_sl.branch_model

        ,vts.`product_line` = v_sl.product_line

        ,vts.`size` = v_sl.size

        ,vts.`PRODUCT_SPEC_ID` = v_sl.PRODUCT_SPEC_ID

        ,vts.`coe_quan` = vts.`quantity`  / v_sl.coeff

        ,vts.`coe_amou` = vts.`amount`  / v_sl.coeff

        ,vts.`h_coe_quan` = vts.`quantity` /v_sl.coeff

        ,vts.`h_coe_amou` = vts.h_amount /v_sl.coeff

        ,vts.`customer_id` = v_sl.CUSTOMER_ID

        ,vts.`country` = v_sl.COUNTRY_ID

        ,vts.`flag`=

        WHERE flag=

        AND v_sl.id=vts.id

        AND vts.country=#{country}

        AND vts.datadate BETWEEN #{beginDate} AND #{endDate}