pro_dw_pcauto_month

pro_dw_pcauto_month

IN p_i_date VARCHAR(10),OUT p_o_state INT

BEGIN
 
#   月第一天
    DECLARE v_monthfirst_date VARCHAR(10); 
    SET v_monthfirst_date = CONCAT(SUBSTR(p_i_date,1,8),'01');
    
    START TRANSACTION;	
    
    DELETE FROM T_SC_PCAUTO_REPORT WHERE DATETIME = v_monthfirst_date AND REPORTTYPE = 2  ;   --  爱卖车 周报表
   
    INSERT INTO T_SC_PCAUTO_REPORT (
		VERSON, REPORTID, DATETIME, AREA, MAC, DEALERCODE, DEALERNAME, 
		PV, PUBLISHNEWS, RECOMMENDNEWS, NEWSPV, PRICERECOMMEND, 
		PRICESEARCH, OPERATE, NETWORKLEADES, CALLANDKEY400, CALL400, 
		HANDLE400, INTENTORDERS, INTENTORDERSREAD, INTENTORDERSREADRATE, NEWORDERS, 
		NEWORDERSREAD, TESTDRIVEORDERS, TESTDRIVEORDERSREAD, STATISTICSTIME, INTEGRAL, 
		CREATETIME, REPORTTYPE, CDATE, UDATE, PICKUPRATE400,
		AVERAGECALL400, LENG400, AVERAGELENG400, WAITTIME400, AVERAGEWAITTIME400, 
		NEWINQUIRYORDERS, NEWINQUIRYORDERSALLOT, NEWINQUIRYORDERSALLOTRATE, NEWINQUIRYORDERSLOST, NEWTESTDRIVEORDERS, 
		NEWTESTDRIVEORDERSALLOT, NEWTESTDRIVEORDERSALLOTRATE, NEWTESTDRIVEORDERSLOST, NEWREPLACEMENTORDERS
)
    SELECT 
			0, d1.REPORTID, d1.DATETIME, d1.AREA, d1.MAC, d1.DEALERCODE, d1.DEALERNAME, 
			d1.PV, d1.PUBLISHNEWS, d1.RECOMMENDNEWS, d1.NEWSPV, d1.PRICERECOMMEND, 
			d1.PRICESEARCH, d1.OPERATE, d2.PCAUTONUM, d1.CALLANDKEY400, d1.CALL400, 
			d1.HANDLE400, d1.INTENTORDERS, d1.INTENTORDERSREAD, d1.INTENTORDERSREADRATE, d1.NEWORDERS, 
			d1.NEWORDERSREAD, d1.TESTDRIVEORDERS, d1.TESTDRIVEORDERSREAD, d1.STATISTICSTIME, d1.INTEGRAL, 
			d1.CREATETIME, d1.REPORTTYPE, d1.CDATE, d1.UDATE, d1.PICKUPRATE400,
			d1.AVERAGECALL400, d1.LENG400, d1.AVERAGELENG400, d1.WAITTIME400, d1.AVERAGEWAITTIME400, 
			d1.NEWINQUIRYORDERS, d1.NEWINQUIRYORDERSALLOT, d1.NEWINQUIRYORDERSALLOTRATE, d1.NEWINQUIRYORDERSLOST, d1.NEWTESTDRIVEORDERS, 
			d1.NEWTESTDRIVEORDERSALLOT, d1.NEWTESTDRIVEORDERSALLOTRATE, d1.NEWTESTDRIVEORDERSLOST, d1.NEWREPLACEMENTORDERS
		FROM  	T_PCAUTO_REPORT_CADILLAC   d1
	  LEFT JOIN (
		SELECT 
			DEALERCODE,
			SUM(PCAUTONUM) PCAUTONUM
			FROM t_sc_leadsabpxreort_day
			WHERE  1=1
			and THEDATE <= p_i_date
			and THEDATE >= v_monthfirst_date
			GROUP BY DEALERCODE
			)  d2 ON d1.DEALERCODE = d2.DEALERCODE
		where 1=1 and  d1.DATETIME= v_monthfirst_date AND d1.REPORTTYPE = 2 GROUP BY d1.DEALERCODE;

    COMMIT;	
    SET p_o_state = 1;
END

pro_dw_pcauto_week

IN p_i_date VARCHAR(10),OUT p_o_state INT

BEGIN
 
#   周 第一天
    DECLARE v_weekfirst_date VARCHAR(10); 
    SET v_weekfirst_date = DATE_SUB(p_i_date,INTERVAL WEEKDAY(p_i_date) DAY);
    
    START TRANSACTION;	
    
    DELETE FROM T_SC_PCAUTO_REPORT WHERE DATETIME = v_weekfirst_date AND REPORTTYPE = 1  ;   --  爱卖车月报表
   
    INSERT INTO T_SC_PCAUTO_REPORT (
		VERSON, REPORTID, DATETIME, AREA, MAC, DEALERCODE, DEALERNAME, 
		PV, PUBLISHNEWS, RECOMMENDNEWS, NEWSPV, PRICERECOMMEND, 
		PRICESEARCH, OPERATE, NETWORKLEADES, CALLANDKEY400, CALL400, 
		HANDLE400, INTENTORDERS, INTENTORDERSREAD, INTENTORDERSREADRATE, NEWORDERS, 
		NEWORDERSREAD, TESTDRIVEORDERS, TESTDRIVEORDERSREAD, STATISTICSTIME, INTEGRAL, 
		CREATETIME, REPORTTYPE, CDATE, UDATE, PICKUPRATE400,
		AVERAGECALL400, LENG400, AVERAGELENG400, WAITTIME400, AVERAGEWAITTIME400, 
		NEWINQUIRYORDERS, NEWINQUIRYORDERSALLOT, NEWINQUIRYORDERSALLOTRATE, NEWINQUIRYORDERSLOST, NEWTESTDRIVEORDERS, 
		NEWTESTDRIVEORDERSALLOT, NEWTESTDRIVEORDERSALLOTRATE, NEWTESTDRIVEORDERSLOST, NEWREPLACEMENTORDERS
)
    SELECT 
		0, d1.REPORTID, d1.DATETIME, d1.AREA, d1.MAC, d1.DEALERCODE, d1.DEALERNAME, 
			d1.PV, d1.PUBLISHNEWS, d1.RECOMMENDNEWS, d1.NEWSPV, d1.PRICERECOMMEND, 
			d1.PRICESEARCH, d1.OPERATE, d2.PCAUTONUM, d1.CALLANDKEY400, d1.CALL400, 
			d1.HANDLE400, d1.INTENTORDERS, d1.INTENTORDERSREAD, d1.INTENTORDERSREADRATE, d1.NEWORDERS, 
			d1.NEWORDERSREAD, d1.TESTDRIVEORDERS, d1.TESTDRIVEORDERSREAD, d1.STATISTICSTIME, d1.INTEGRAL, 
			d1.CREATETIME, d1.REPORTTYPE, d1.CDATE, d1.UDATE, d1.PICKUPRATE400,
			d1.AVERAGECALL400, d1.LENG400, d1.AVERAGELENG400, d1.WAITTIME400, d1.AVERAGEWAITTIME400, 
			d1.NEWINQUIRYORDERS, d1.NEWINQUIRYORDERSALLOT, d1.NEWINQUIRYORDERSALLOTRATE, d1.NEWINQUIRYORDERSLOST, d1.NEWTESTDRIVEORDERS, 
			d1.NEWTESTDRIVEORDERSALLOT, d1.NEWTESTDRIVEORDERSALLOTRATE, d1.NEWTESTDRIVEORDERSLOST, d1.NEWREPLACEMENTORDERS 
		from  T_PCAUTO_REPORT_CADILLAC  d1
		LEFT JOIN (
				SELECT DEALERCODE,SUM(PCAUTONUM) PCAUTONUM FROM t_sc_leadsabpxreort_day WHERE  THEDATE <= p_i_date and THEDATE >= v_weekfirst_date  GROUP BY DEALERCODE
		)d2 ON d2.DEALERCODE = d1.DEALERCODE
		where  d1.DATETIME= v_weekfirst_date AND d1.REPORTTYPE = 1 GROUP BY d1.DEALERCODE;

    COMMIT;	
    SET p_o_state = 1;
END

pro_dw_days_100

IN p_i_date VARCHAR(10), OUT p_o_state INT

BEGIN
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------   
DECLARE v_begin_date VARCHAR(20); 	-- 开始时间(当月第一天)
DECLARE v_end_date VARCHAR(20);		-- 结束时间(当天23:59:59分)
DECLARE v_start_date VARCHAR(20);	-- 开始时间(当天0点)
DECLARE v_first_date VARCHAR(20);	-- 本年1月1号
DECLARE v_yearmonth VARCHAR(7);		-- 年月(当年当月)
DECLARE v_last_yearmonth VARCHAR(7);	-- 本年上月
DECLARE v_cur_date VARCHAR(20);		-- 当前日期
DECLARE v_fetch_ok BOOLEAN;
DECLARE v_media_id INT;			-- 媒体来源ID
DECLARE v_media_name VARCHAR(20);	-- 媒体来源MEDIANAME
DECLARE v_dealer_id INT;		-- 经销商ID
DECLARE v_dealer_code VARCHAR(32);	-- 经销商code
DECLARE v_dealer_name VARCHAR(256);	-- 经销商name
DECLARE v_dealer_regionalid INT;		-- 经销商大区
DECLARE v_dealer_regionalname VARCHAR(16);	-- 经销商大区名称
DECLARE v_dealer_macid INT;			-- 经销商mac
DECLARE v_dealer_macname VARCHAR(64);		-- 经销商mac名称
DECLARE v_report_val1 INT;		-- 当月MyChevy获得网络线索总量
DECLARE v_report_val2 INT;		-- MyChevy内部去重数
DECLARE v_report_val3 INT;		-- DOSS接收去重数
DECLARE v_report_val5 INT;		-- 累计去重总数
DECLARE v_report_val6 INT;		-- 当月MyChevy实际下发DOSS数
DECLARE v_report_val7 INT;		-- 当月DOSS意向生成量
DECLARE v_report_val8 INT;		-- 当月MyChevy获得400线索总量：该经销商该月在MyChevy中从该渠道获得的400线索总量
DECLARE v_report_val9 INT;		-- 当月DOSS呼入电话流量记录数
DECLARE v_report_val11 INT;		-- 当月DOSS呼入电话流量生成意向数
DECLARE v_report_val12 INT;		-- DOSS内去重后来源MyChevy线索总数
DECLARE v_report_val15 INT;		-- 48小时内跟进数
DECLARE v_report_val16 INT;		-- 邀约到店数	
DECLARE v_report_val17 INT;		-- 试乘试驾数
DECLARE v_report_val19 INT;		-- 当月线索成交量
DECLARE v_report_val21 INT;		-- 累计销量
declare v_report_val22 int;
declare v_report_val23 int;
declare v_report_val24 int;
DECLARE v_report_id INT;		-- 报表ID
DECLARE v_dealer CURSOR FOR SELECT ID,DEALERCODE FROM t_dealer WHERE ISTEST=0 AND VALID = 1;	-- 经销商
DECLARE v_media CURSOR FOR SELECT ID,MEDIANAME FROM t_origional_media WHERE ID IN (1,5);	-- 来源媒体
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fetch_ok = TRUE; 	
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET v_yearmonth = LEFT(p_i_date,7);
SET v_begin_date = CONCAT(v_yearmonth,'-01 00:00:00');
SET v_start_date = CONCAT(p_i_date, ' 00:00:00');
SET v_end_date = CONCAT(p_i_date,' 23:59:59');
SET v_first_date = CONCAT(YEAR(p_i_date),'-01-01 00:00:00');
SET v_last_yearmonth = SUBSTR(DATE_ADD(p_i_date,INTERVAL -1 MONTH),1,7);
SET v_cur_date = NOW();
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 删除
DELETE FROM t_sc_leadsreport_day WHERE THEDATE = p_i_date;
DELETE FROM t_sc_leadsreport_day_dist WHERE THEDATE = p_i_date;
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* 第一步 利用以下比对方式生成的配对标记，统计出该经销商该月包含配对关系的DOSS呼入电话流量记录的个数
	比对方式：
	1.新接收到的DOSS反馈流量记录：
	查询系统中是否有与其所属经销商和电话号码相同、且与其他DOSS流量记录无配对关系、且媒体创建时间早于流量记录的DOSS创建时间的400线索
	1.1若有一条，将两条记录进行配对(各自增加一个字段记录配对者的id或其他办法)
	1.2若有多条，将(流量记录在DOSS内的创建时间和400线索的媒体创建时间之差最小)的400线索与流量记录进行配对
	2.接收到时未比对到配对关系的流量记录：
	每日定时将接收时间在7天内的、且与其他400线索无配对关系的DOSS反馈流量记录整体进行上述比对。*/
UPDATE t_origional_leads_freecall INNER JOIN 
(SELECT MAX(t_doss_back.ID) AS did, MAX(t_origional_leads_freecall.id) AS oid
	FROM t_doss_back, t_origional_leads_freecall
	WHERE t_doss_back.DEALERID = t_origional_leads_freecall.DEALERCODE	-- 经销商
	AND t_doss_back.PHONE = t_origional_leads_freecall.CALLNUM		-- 电话号码
	AND t_origional_leads_freecall.PAIRID IS NULL 				-- 未关联
	AND t_origional_leads_freecall.CHANNELTIME <= t_doss_back.CREATETIME
	AND DATE_ADD(t_origional_leads_freecall.CHANNELTIME,INTERVAL 7 DAY) >= t_doss_back.CREATETIME		-- 7天内
	AND t_doss_back.FEEDBACKTYPE = 2	-- 流量
	AND t_doss_back.CREATETIME <= v_end_date AND t_doss_back.CREATETIME >= v_start_date GROUP BY t_doss_back.PHONE) vals 
ON t_origional_leads_freecall.id = vals.oid SET t_origional_leads_freecall.PAIRID = vals.did;
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- {   来源媒体 start
OPEN v_media;v_media_loop:LOOP FETCH v_media INTO v_media_id,v_media_name;
IF v_fetch_ok THEN 
LEAVE v_media_loop;
ELSE
-- 将doss反馈当天数据插入到临时表中方便统计
-- 网路线索
INSERT INTO `t_sc_leadsreport_day_dist` (DEALERCODE,SOURCEMEDIAID,MOBILE,FIRSTVISITDATE,FIRSTTESTDRIVEDATE,PRODUCTID,VISITCOUNT,FOLLOWSTATE,ISEFFECTIVE,INTYPE,CREATETIME,THEDATE,CDATE,UDATE)
(SELECT DEALERID,v_media_id,MOBILE,FIRSTVISITDATE,FIRSTTESTDRIVEDATE,PRODUCTID,TIMESTAMPDIFF(HOUR,CREATETIME,FIRSTFOLLOWTIME),FOLLOWSTATE,ISEFFECTIVE,1,CREATETIME,p_i_date,CDATE,NOW()
FROM t_doss_back 
WHERE FEEDBACKTYPE = 1 AND CDATE >= v_start_date AND CDATE <= v_end_date AND CREATETIME <= v_end_date AND CREATETIME >= v_first_date
AND DOSSBACKID IN (SELECT CONCAT('CD',ID) FROM t_origional_leads WHERE SOURCEMEDIAID = v_media_id));
-- 400
INSERT INTO `t_sc_leadsreport_day_dist` (DEALERCODE,SOURCEMEDIAID,MOBILE,FIRSTVISITDATE,FIRSTTESTDRIVEDATE,PRODUCTID,VISITCOUNT,FOLLOWSTATE,ISEFFECTIVE,INTYPE,CREATETIME,THEDATE,CDATE,UDATE)
SELECT DEALERID,v_media_id,MOBILE,FIRSTVISITDATE,FIRSTTESTDRIVEDATE,PRODUCTID,TIMESTAMPDIFF(HOUR,CREATETIME,FIRSTFOLLOWTIME),FOLLOWSTATE,ISEFFECTIVE,2,CREATETIME,p_i_date,CDATE,NOW()
FROM t_doss_back 
WHERE FEEDBACKTYPE = 1 AND CDATE >= v_start_date AND CDATE <= v_end_date AND CREATETIME <= v_end_date AND CREATETIME >= v_first_date 
AND DSSTELID IN (SELECT t_doss_back.DSSTELID FROM t_doss_back,t_origional_leads_freecall WHERE t_doss_back.ID = PAIRID AND SOURCEMEDIAID = v_media_id);
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- {   经销商 start
OPEN v_dealer;v_dealer_loop:LOOP FETCH v_dealer INTO v_dealer_id,v_dealer_code;	-- 打开游标(来源媒体)
IF v_fetch_ok THEN 
LEAVE v_dealer_loop;
ELSE
-- 1.	当月MyChevy获得网络线索总量：该经销商该月从该渠道获得的MyChevy已下发网络线索+无效网络线索总量 
SET v_report_val1 = (SELECT COUNT(1) val FROM t_origional_leads WHERE SOURCEMEDIAID = v_media_id AND DEALERCODE = v_dealer_code AND CHANNELTIME >= v_begin_date AND CHANNELTIME <= v_end_date AND CDATE >= v_start_date AND CDATE <= v_end_date);
-- 2.	MyChevy内部去重数：第1项中，有多少网络线索的“意向转化情况”是“关联已有意向”
SET v_report_val2 = (SELECT COUNT(1) val FROM t_origional_leads WHERE CONVERTINTENT = 1 AND SOURCEMEDIAID = v_media_id AND DEALERCODE = v_dealer_code AND CHANNELTIME >= v_begin_date AND CHANNELTIME <= v_end_date AND CDATE >= v_start_date AND CDATE <= v_end_date);
-- 3.	DOSS接收去重数：第1项中，“意向转化情况”是“生成新意向”的网络线索所关联的销售意向中，有多少销售意向的DOSS接收状态是“被去重”
SET v_report_val3 = (SELECT COUNT(1) val FROM t_origional_leads WHERE RECEIVESTATUS = 3 AND SOURCEMEDIAID = v_media_id AND DEALERCODE = v_dealer_code AND CHANNELTIME >= v_begin_date AND CHANNELTIME <= v_end_date AND CDATE >= v_start_date AND CDATE <= v_end_date);
-- 6.	*当月MyChevy实际下发DOSS数：
-- 第1项中，有多少网络线索的“意向转化情况”是“生成新意向” 已下发
SET v_report_val6 = (SELECT COUNT(1) val FROM t_origional_leads WHERE SENDSTATUS = 1 AND SOURCEMEDIAID = v_media_id AND DEALERCODE = v_dealer_code AND CHANNELTIME >= v_begin_date AND CHANNELTIME <= v_end_date AND CDATE >= v_start_date AND CDATE <= v_end_date);
-- 7.  当月DOSS意向生成量.根据DOSS线索反馈接口数据，统计出该经销商该月在DOSS中生成的新意向中，多少意向由该渠道的MyChevy网络线索生成的MyChevy意向下发后生成(根据ID比对)
SET v_report_val7 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE INTYPE = 1 AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND CREATETIME <= v_end_date AND CREATETIME >= v_begin_date AND THEDATE = p_i_date);
-- 8.	*当月MyChevy获得400线索总量：该经销商该月在MyChevy中从该渠道获得的400线索总量
SET v_report_val8 = (SELECT COUNT(1) val FROM t_origional_leads_freecall WHERE SOURCEMEDIAID = v_media_id AND DEALERCODE = v_dealer_code AND CHANNELTIME >= v_begin_date AND CHANNELTIME <= v_end_date AND CDATE >= v_start_date AND CDATE <= v_end_date);
--  9.	当月DOSS呼入电话流量记录数：利用以下比对方式生成的配对标记，统计出该经销商该月包含配对关系的DOSS呼入电话流量记录的个数
-- 比对方式：
-- 1.新接收到的DOSS反馈流量记录：
-- 查询系统中是否有与其所属经销商和电话号码相同、且与其他DOSS流量记录无配对关系、且媒体创建时间早于流量记录的DOSS创建时间的400线索
-- 1.1若有一条，将两条记录进行配对(各自增加一个字段记录配对者的id或其他办法)
-- 1.2若有多条，将(流量记录在DOSS内的创建时间和400线索的媒体创建时间之差最小)的400线索与流量记录进行配对
-- 2.接收到时未比对到配对关系的流量记录：
-- 每日定时将接收时间在7天内的、且与其他400线索无配对关系的DOSS反馈流量记录整体进行上述比对。
SET v_report_val9 = (SELECT COUNT(1) val FROM t_origional_leads_freecall WHERE PAIRID IS NOT NULL AND SOURCEMEDIAID = v_media_id AND DEALERCODE = v_dealer_code AND CHANNELTIME >= v_begin_date AND CHANNELTIME <= v_end_date AND CDATE >= v_start_date AND CDATE <= v_end_date);
-- 11.	*当月DOSS呼入电话流量生成意向数：利用以下比对方式，统计出该经销商该月在DOSS中生成的新意向中，多少意向由该渠道的电话流量生成
-- 利用第8项统计出的DOSS电话流量记录的DSSTELID，查询DSSTELID与其一致的DOSS反馈意向。 
SET v_report_val11 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE INTYPE = 2 AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND CREATETIME <= v_end_date AND CREATETIME >= v_begin_date AND THEDATE = p_i_date);
-- 12.  DOSS内去重后来源MyChevy线索总数：第7项+第11项 
SET v_report_val12 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND CREATETIME <= v_end_date AND CREATETIME >= v_begin_date AND THEDATE = p_i_date);
-- 15.	*48小时内跟进数：统计出第12项统计出的DOSS反馈意向中，首次跟进时间-DOSS接受时间值小于等于48小时的DOSS反馈意向的数量(两时间任何一项未反馈为空计算不出时间差的不纳入统计)
SET v_report_val15 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE VISITCOUNT <= 48 AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND CREATETIME <= v_end_date AND CREATETIME >= v_begin_date AND THEDATE = p_i_date and ISEFFECTIVE in (2,3,6));
-- 16.	*邀约到店数：统计出第11项统计出的DOSS反馈意向中 “DOSS邀约到店时间”值非空的DOSS反馈意向的数量
SET v_report_val16 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE FIRSTVISITDATE > '2000-01-01 00:00:00' AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND CREATETIME <= v_end_date AND CREATETIME >= v_begin_date AND THEDATE = p_i_date);
-- 17   试乘试驾数：统计出第11项统计出的DOSS反馈意向中“DOSS试驾时间”值非空的DOSS反馈意向的数量
SET v_report_val17 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE FIRSTTESTDRIVEDATE > '2000-01-01 00:00:00' AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND CREATETIME <= v_end_date AND CREATETIME >= v_begin_date AND THEDATE = p_i_date);
-- 19.	*当月线索成交量：统计出从该统计月份所属年份的第一月份到该月，该经销商在该渠道获得的全部DOSS反馈意向中，“DOSS跟进状态”值在本月更新为“已交车”的DOSS反馈意向数量。
SET v_report_val19 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE FOLLOWSTATE = 'D' AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND THEDATE = p_i_date);
-- 12.	跟进有计划线索量：统计出从该统计月份所属年份的第一月份到该月，该经销商在该渠道获得的全部DOSS反馈线索中，DOSS接收状态在本月首次更新为“已生成计划”的数量
set v_report_val22 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE ISEFFECTIVE = 6 AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND THEDATE = p_i_date);
-- 13. 到店生成意向量：统计出从该统计月份所属年份的第一月份到该月，该经销商在该渠道获得的全部DOSS反馈线索中，“DOSS邀约到店时间”在本月首次有值的DOSS反馈线索的数量
set v_report_val23 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE FIRSTVISITDATE > '2000-01-01 00:00:00' AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND THEDATE = p_i_date and FOLLOWSTATE in ('A','B','C','O','H','F'));
-- 15.	*当月线索订单量：统计出从该统计月份所属年份的第一月份到该月，该经销商在该渠道获得的全部DOSS反馈线索中，“DOSS跟进状态”值在本月更新为“O”的DOSS反馈线索数量。
set v_report_val24 = (SELECT COUNT(DISTINCT MOBILE) val FROM `t_sc_leadsreport_day_dist` WHERE FOLLOWSTATE = 'O' AND DEALERCODE = v_dealer_code AND SOURCEMEDIAID = v_media_id AND THEDATE = p_i_date);
-- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 插入报表数据
INSERT INTO t_sc_leadsreport_day 
-- 版本号, 所属日期, 所属大区ID, 所属大区名称, 所属MAC编号, MAC名称, 所属经销商ID, 经销商代码(DOSS代码), 所属经销商名称, 来源媒体ID, 来源媒体名称,
(VERSON, THEDATE, REGIONALID, REGIONALNAME, MACID, MACNAME, DEALERID, DEALERCODE, DEALERNAME, SOURCEMEDIAID, MEDIANAME,	
-- 当天MyChevy获得网络线索总量, MyChevy内部去重数, DOSS接收去重数, 当天去重总数, 当天MyChevy实际下发DOSS数, 当天doss意向生成量, 当天MyChevy获得400线索总量, 当天DOSS呼入电话流量记录数
 LEADSTOTAL, MYCHEVYREMOVAL, DOSSRECEIVEREMOVAL, REMOVALTOTAL, DOSSSENDNUM, DOSSINTENTCOUNT, FREECALLLEADSTOTAL, FREECALLDOSSRECORD,
-- 当月DOSS呼入电话流量生成意向数, DOSS内去重后来源MyChevy线索总数, 48小时内跟进数, 邀约到店数, 试乘试驾数, 当天线索成交量
 FREECALLDOSSRECORDINTENT, DOSSREMOVALMYCHEVYCUETOTAL, FOURTYFIRSTFOLLOW, INVITATIONSTORE, TESTDRIVER, CLUEDEALVOLUME, 
-- 跟进有计划线索量 , 到店生成意向量, 当月线索订单量, 当月线索总量
FOLLOWUPNUM,  STOREINTENTNUM, ORDERNUM, CLUESTOTAL, CDATE, UDATE)
VALUES  
(0, p_i_date, null, null, null, null, v_dealer_id, v_dealer_code, null, v_media_id, v_media_name, 
v_report_val1, v_report_val2, v_report_val3, (v_report_val2+v_report_val3), v_report_val6, v_report_val7, v_report_val8, v_report_val9,  
v_report_val11, v_report_val12, v_report_val15, v_report_val16, v_report_val17, v_report_val19, 
v_report_val22, v_report_val23, v_report_val24, (v_report_val1+v_report_val8), v_cur_date, NOW());
SET v_fetch_ok = 0;
END IF;
END LOOP; 
CLOSE v_dealer;
--
SET v_fetch_ok = 0;
END IF;
END LOOP; 
CLOSE v_media;	
-- 成功数量 
SET p_o_state = (SELECT COUNT(1) FROM t_sc_leadsreport_day WHERE THEDATE = p_i_date);
END

update05_t_origional_leads

IN p_i_date VARCHAR(10), OUT p_o_state INT

BEGIN
DECLARE v_dealer_macid INT; -- maxid
DECLARE v_dealer_macname VARCHAR(20); -- mac name
DECLARE v_dealer_id INT;	-- 经销商id
DECLARE v_fetch_ok BOOLEAN;
DECLARE t_origional_leads INT; -- t_origional_leads;
 


DECLARE v_dealer CURSOR FOR SELECT  ID,MACID,MACNAME  FROM t_dealer; -- 查询所有的经销商
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fetch_ok = TRUE; 

OPEN v_dealer;v_dealer_loop:LOOP FETCH v_dealer INTO v_dealer_id,v_dealer_macid,v_dealer_macname ;
IF v_fetch_ok THEN LEAVE v_dealer_loop;

ELSE
	SET t_origional_leads = (select count(id) from t_origional_leads where  dealerid = v_dealer_id);
 
	IF t_origional_leads > 0 
		THEN update t_origional_leads set MACID = v_dealer_macid,MACNAME = v_dealer_macname,UDATE = NOW() WHERE ID <= 1598999 and DEALERID=v_dealer_id;
	END IF;
 
	SET v_fetch_ok = 0;
END IF;
END LOOP;
CLOSE v_dealer;
 SET p_o_state = 0;
END

pro_dw_days_datareportnew_300

IN p_i_date VARCHAR(10), OUT p_o_state INT

BEGIN

DECLARE v_begin_date VARCHAR(20); 	-- 开始时间(当月第一天)
DECLARE v_end_date VARCHAR(20);		-- 结束时间(当天23:59:59分)
DECLARE v_start_date VARCHAR(20);	-- 开始时间(当天0点)
DECLARE v_yearmonth VARCHAR(7);		-- 年月(当年当月)
DECLARE v_last_yearmonth VARCHAR(7);	-- 本年上月
DECLARE v_cur_date VARCHAR(20);		-- 当前日期
DECLARE v_fetch_ok BOOLEAN;
DECLARE v_dealer_regionalid INT;
DECLARE v_dealer_regionalname VARCHAR(20);
DECLARE v_dealer_macid INT;
DECLARE v_dealer_macname VARCHAR(20);
DECLARE v_dealer_id INT;		-- 经销商ID
DECLARE v_dealer_code VARCHAR(32);	-- 经销商code
DECLARE v_dealer_dealername VARCHAR(50); -- 经销商名称
DECLARE v_dealer_referred VARCHAR(50);	-- 经销商简称
DECLARE v_dealer_address VARCHAR(100);	-- 经销商地址
DECLARE v_dealer_zipcode VARCHAR(20);	-- 经销商邮编
DECLARE v_dealer_email VARCHAR(50);	-- 经销商邮件
DECLARE v_dealer_lat VARCHAR(20);	-- 经销商维度
DECLARE v_dealer_lng VARCHAR(20);	-- 经销商经度
DECLARE v_dealer_tel VARCHAR(30);	-- 经销商销售热线
DECLARE v_dealer_lever INT;-- 星级
DECLARE v_this_have_data INT; -- 此表是否已有数据标识

DECLARE v_report_val1w INT; -- PV		
DECLARE v_report_val2w INT; -- UV

DECLARE v_report_val1 INT; -- PV		
DECLARE v_report_val2 INT; -- UV
DECLARE v_report_val3 INT; -- 网站首页焦点轮播图发布数?
DECLARE v_report_val4 INT; -- 在售车型首页发布数?
DECLARE v_report_val5 INT; -- 新闻评测新闻动态发布量
DECLARE v_report_val6 INT; -- 试驾评测发布量	
DECLARE v_report_val7 INT; -- 是否微博绑定成功?
DECLARE v_report_val8 INT; -- 是否微信公众号二维码上传?
DECLARE v_report_val9 INT;  -- 会购车本店活动发布数		
DECLARE v_report_val10 INT;		-- 在线答疑发布数
DECLARE v_report_val11 INT;		-- 车友会售后优惠发布数
DECLARE v_report_val12 INT;		-- 特色服务发布数?
DECLARE v_report_val13 INT;		-- 图片信息准确无误、按照后台尺寸上传图片（有、无）?
DECLARE v_report_val14 INT;		-- 公司有无logo
DECLARE v_report_val15 VARCHAR(50);		-- 公司简介
DECLARE v_report_val16 VARCHAR(50); -- 负责人手机
DECLARE v_report_val17 VARCHAR(30); -- 星级
DECLARE v_report_val18 VARCHAR(30); -- 销售营业时间
DECLARE v_report_val19 VARCHAR(30); -- 售后服务热线
DECLARE v_report_val20 VARCHAR(30); -- 售后服务时间
DECLARE v_report_val21 VARCHAR(30); -- 客服热线
DECLARE v_report_val22 VARCHAR(30); -- 养修预约电话
DECLARE v_report_val23 VARCHAR(30); -- 24小时救援电话
DECLARE v_report_val24 VARCHAR(200); -- e触电专员
DECLARE v_report_val25 VARCHAR(200); -- SA
DECLARE v_report_val26 VARCHAR(200); -- 救援人员
DECLARE v_report_val27 VARCHAR(200); -- 客服
DECLARE v_report_val28 VARCHAR(200); -- 客服经理
DECLARE v_report_val29 VARCHAR(200); -- 市场负责人
DECLARE v_report_val30 VARCHAR(200); -- 市场专员
DECLARE v_report_val31 VARCHAR(200); -- 售后负责人
DECLARE v_report_val32 VARCHAR(200); -- 网电经理
DECLARE v_report_val33 VARCHAR(200); -- 网电销售顾问
DECLARE v_report_val34 VARCHAR(200); -- 销售负责人
DECLARE v_report_val35 VARCHAR(200); -- 销售顾问
DECLARE v_report_val36 VARCHAR(200); -- 销售支持
DECLARE v_report_val37 VARCHAR(200); -- 续保专员
DECLARE v_report_val38 VARCHAR(200); -- 总经理

DECLARE v_report_val40 VARCHAR(200); -- MICROISPUBLIC微信公众号是否申请 ？？？
DECLARE v_report_val41 VARCHAR(200); -- MICROISYZFFY微信公众认证已支付审核费用 ？？？

 

DECLARE v_dealer CURSOR FOR SELECT dealer.ID,dealer.REGIONALID,dealer.REGIONALNAME,dealer.MACID,dealer.MACNAME,dealer.DEALERCODE,dealer.DEALERNAME,dealer.REFERRED,dealer.ADDRESS,dealer.ZIPCODE,dealer.EMAIL,dealer.LAT,dealer.LNG,dealer.TEL,dealer.LEVER,
-- 新添加处
if(dealer.REFERRED is null,0, 1),if(info.LOGO is null ,0,1),info.INTRODUCTION,info.MOBILE,CONCAT(info.SALESTIMESTART,'-',info.SALESTIMEEND),info.CUSTOMERSERVICETEL,
CONCAT(info.CUSTOMERSERVICETIMES,'-',info.CUSTOMERSERVICETIMEE),info.CUSTOMERHOTLINE,info.MAINTENANCETEL,info.RESCUETEL24,info.ISOPENWECHAT,info.ISPAYAPPROVEWECHAT


 FROM t_dealer dealer LEFT JOIN t_dealer_info info ON info.dealerid = dealer.ID WHERE dealer.ISTEST = 0 AND dealer.ISOPEN = 1 AND dealer.VALID = 1 and info.valid=1;	-- 经销商
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fetch_ok = TRUE; 

SET v_yearmonth = LEFT(p_i_date,7);
SET v_begin_date = CONCAT(p_i_date, ' 00:00:00');
SET v_end_date = CONCAT(p_i_date, ' 23:59:59');
SET v_last_yearmonth = SUBSTR(DATE_ADD(p_i_date,INTERVAL -1 MONTH),1,7);

OPEN v_dealer;v_dealer_loop:LOOP FETCH v_dealer INTO v_dealer_id,v_dealer_regionalid,v_dealer_regionalname,v_dealer_macid,v_dealer_macname,v_dealer_code,v_dealer_dealername,v_dealer_referred,
v_dealer_address,v_dealer_zipcode,v_dealer_email,v_dealer_lat,v_dealer_lng,v_dealer_tel,v_dealer_lever,
-- 新添加处
v_dealer_referred,v_report_val14,v_report_val15,v_report_val16,v_report_val18,v_report_val19,
v_report_val20,v_report_val21,v_report_val22,v_report_val23,v_report_val40,v_report_val41;	-- 打开游标(来源媒体)
IF v_fetch_ok THEN LEAVE v_dealer_loop;
ELSE
-- 1.	PV：该统计周期内该经销商的MyChevy经销商网站获得的PV
SET v_report_val1 = (SELECT IFNULL(SUM(WEBPV),0) FROM t_sc_piwikresult WHERE DEALERID = v_dealer_id AND THEDATE = p_i_date);
-- 2.	UV：该统计周期内该经销商的MyChevy经销商网站获得的PV
SET v_report_val2 = (SELECT IFNULL(SUM(WEBUV),0) FROM t_sc_piwikresult WHERE DEALERID = v_dealer_id AND THEDATE = p_i_date);

-- 1.	PV：该统计周期内该经销商的MyChevy经销商网站获得的PV
SET v_report_val1w = (SELECT IFNULL(SUM(MOBILEPV),0) FROM t_sc_piwikresult WHERE DEALERID = v_dealer_id AND THEDATE = p_i_date);
-- 2.	UV：该统计周期内该经销商的MyChevy经销商网站获得的PV
SET v_report_val2w = (SELECT IFNULL(SUM(MOBILEUV),0) FROM t_sc_piwikresult WHERE DEALERID = v_dealer_id AND THEDATE = p_i_date);


-- 3.	网站首页焦点轮播图发布数
--  原 SET v_report_val3 = (SELECT COUNT(1) FROM t_activity WHERE VALID = 1 AND OPERTYPE = 3 AND OPERATORID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date);
SET v_report_val3 = (SELECT COUNT(1) FROM t_activity WHERE OPERTYPE =3 and ISTOP =1 and AREACHECKSTATE =1 and `BEGIN` <=NOW() and `END`>=NOW() and OPERATORID = v_dealer_id );
-- 4.	在售车型首页发布数  -- wqk SELECT COUNT(VEHICLETYPEID) FROM T_VEHICLETYPE_DEALER_PUBLISH WHERE VALID = 1 AND ISPUBLISH = 1 AND ISFIRSTPAGE = 1 AND CDATE BETWEEN v_begin_date AND v_end_date AND P.DEALERID = v_dealer_id
-- 原版  SET v_report_val4 = null; -- (SELECT COUNT(1) from t_vehicletype_dealer_publish where  ISFIRSTPAGE = 1 and DEALERID = v_dealer_id and CDATE BETWEEN v_begin_date AND v_end_date);
SET v_report_val4 =  (SELECT COUNT(1) from t_vehicletype_dealer_publish where DEALERID = v_dealer_id AND ISFIRSTPAGE = 1 and  ISPUBLISH = 1 and VALID = 1);
-- 5.	新闻评测新闻动态发布量
SET v_report_val5 = (SELECT count(1) FROM t_promotion WHERE VALID = 1 AND OPERTYPE = 3 AND CATEGORY = 1 AND OPERID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date);
-- 6.试驾评测发布量
SET v_report_val6 = (SELECT count(1) FROM t_promotion WHERE VALID = 1 AND OPERTYPE = 3 AND CATEGORY = 3 AND OPERID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date);
-- 7.是否微博绑定成功
SET v_report_val7 =   (select IF(WEIBOCODE is null,0, 1) from t_dealer_weibo where valid = 1  and dealerid = v_dealer_id and WEIBOCODE is not null limit 1);
-- 8.是否微信公众号二维码上传
SET v_report_val8 = (select IF(UPLOADIMAGEURL is null,0, 1) from t_dealer_twodimensioncode where flag = 1 and valid = 1 and dealerid = v_dealer_id );
-- 9.惠购车本店活动发布数
SET v_report_val9 = (SELECT COUNT(1) FROM t_activity WHERE VALID = 1 AND OPERTYPE = 3 AND OPERATORID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date);

-- 10.wqk	在线答疑发布量：该经销商在MyChevy上的收集到的全部在线答疑提问，首次发布时间位于该统计周期内的在线答疑提问数量
SET v_report_val10 = (SELECT COUNT(1) FROM t_message WHERE VALID = 1 AND DEALERID = v_dealer_id AND MESSAGEDATE BETWEEN v_begin_date AND v_end_date );
-- 	SET v_report_val10 = (SELECT COUNT(1) FROM t_message WHERE VALID = 1  AND DEALERID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date );


-- 11.车友会售后优惠发布数
-- SET v_report_val11 = (SELECT count(1) FROM t_promotion WHERE VALID = 1 AND OPERTYPE = 3 AND CATEGORY NOT IN (1,3) AND OPERID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date);
SET v_report_val11 = (SELECT count(1) FROM t_promotion WHERE VALID = 1 AND OPERTYPE = 3 AND CATEGORY  IN (8,10) AND OPERID = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date);


-- 12.特色服务发布数
SET v_report_val12 = (select count(id) from t_dealer_special_service  where  VALID = 1 and dealerid = v_dealer_id AND CDATE BETWEEN v_begin_date AND v_end_date );
-- 13.图片信息准确无误、按照后台尺寸上传图片（有、无） 没法统计
SET v_report_val13 = null ;
-- 24.e触电专员  id不会变 只能以id匹配做关联
SET v_report_val24 = (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 27 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 25.SA
SET v_report_val25 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 60 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 26.救援人员
SET v_report_val26 = (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 85 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 27.客服
SET v_report_val27 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 59 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 28.客服经理
SET v_report_val28 =   (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 58 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 29 市场负责人
SET v_report_val29 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 52 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 30.市场专员
SET v_report_val30 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 62 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 31.售后负责人
SET v_report_val31 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 54 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 32.网电经理
SET v_report_val32 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 69 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 33.网电销售顾问
SET v_report_val33 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 57 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 34.销售负责人
SET v_report_val34 = (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 53 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 35.销售顾问
SET v_report_val35 = (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 56 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 36.销售支持
SET v_report_val36 =   (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 55 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 37.续保专员
SET v_report_val37 = (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 87 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- 38.总经理
SET v_report_val38 =  (select GROUP_CONCAT(REALNAME) from t_administrator where ADMINGROUPID = 63 and  OPERTYPE = 3 and valid = 1 and OPERATORID = v_dealer_id);
-- *******************************************************************************************************************************************************************
 

SET v_this_have_data = (select count(id) from t_sc_datareportnew where thedate = p_i_date and dealerid = v_dealer_id);
IF v_this_have_data=0 THEN 
INSERT INTO t_sc_datareportnew (THEDATE, REGIONALID, REGIONALNAME,MACID,MACNAME,DEALERID, DEALERCODE, DEALERNAME, PCPV, PCUV, PCJDLBNUM, PCCARPUSHNUM, 
	PCNEWSPUSHNUM, PCTRYDRIVENUM, PCISWEIBO, PCISWENXINCODE, PCHGCACTNUM, PCZXDYNUM, PCCYHACTNUM, PCTSFWNUM, PCISIMGRIGHT, PCDEALERREFERRED, PCDEALERLOGO, PCDEALERDISC, 
	PCDEALERADDRESS, PCDEALERZIPCODE, PCFZRMOBILE, PCDEALEREMAIL, PCDEALERLNG, PCDEALERLAT, PCDEALERLEVER, PCDEALERTEL, PCDEALEROFFICEHOURS, PCDEALERSHTEL, PCDEALERAFTERHOURS, 
	PCKFRXTEL, PCYXYYTEL, PC24HELPTEL, PCECONTACT, PCSA, PCHELPER, PCCUSTOM, PCCUSTOMMANAGER, PCSCMANAGER, PCSCZY, PCSHMANAGER, PCONLINEMANAGER, PCONLINESELLER, PCSELLERMANAGER, 
	PCSELLERONE, PCSELLERSUPPORT, PCXBZY, PCTOPMANAGER, CDATE, UDATE,MICROISPUBLIC,MICROISYZFFY,
	WAPPV,WAPUV
	
)
VALUES (p_i_date,v_dealer_regionalid,v_dealer_regionalname,v_dealer_macid,v_dealer_macname,v_dealer_id,v_dealer_code,v_dealer_dealername,v_report_val1,v_report_val2,v_report_val3,v_report_val4,
v_report_val5,v_report_val6,v_report_val7,v_report_val8,v_report_val9,v_report_val10,v_report_val11,v_report_val12,v_report_val13,v_dealer_referred,v_report_val14,v_report_val15,
v_dealer_address,v_dealer_zipcode,v_report_val16,v_dealer_email,v_dealer_lng,v_dealer_lat,v_dealer_lever,v_dealer_tel,v_report_val18,v_report_val19,v_report_val20,
v_report_val21,v_report_val22,v_report_val23,v_report_val24,v_report_val25,v_report_val26,v_report_val27,v_report_val28,v_report_val29,v_report_val30,v_report_val31,v_report_val32,v_report_val33,v_report_val34,
v_report_val35,v_report_val36,v_report_val37,v_report_val38,NOW(),NOW(),v_report_val40,v_report_val41,
v_report_val1w,v_report_val2w
);

 
ELSE
update t_sc_datareportnew set THEDATE=p_i_date, REGIONALID=v_dealer_regionalid, REGIONALNAME=v_dealer_regionalname,MACID=v_dealer_macid,MACNAME=v_dealer_macname,
DEALERID=v_dealer_id, DEALERCODE=v_dealer_code, DEALERNAME=v_dealer_dealername, PCPV=v_report_val1, PCUV=v_report_val2, PCJDLBNUM=v_report_val3, PCCARPUSHNUM=v_report_val4, 
PCNEWSPUSHNUM=v_report_val5, PCTRYDRIVENUM=v_report_val6, PCISWEIBO=v_report_val7, PCISWENXINCODE=v_report_val8, PCHGCACTNUM=v_report_val9, PCZXDYNUM=v_report_val10, 
PCCYHACTNUM=v_report_val11, PCTSFWNUM=v_report_val12, PCISIMGRIGHT=v_report_val13, PCDEALERREFERRED=v_dealer_referred, PCDEALERLOGO=v_report_val14, PCDEALERDISC=v_report_val15, 
PCDEALERADDRESS=v_dealer_address, PCDEALERZIPCODE=v_dealer_zipcode, PCFZRMOBILE=v_report_val16, PCDEALEREMAIL=v_dealer_email, PCDEALERLNG=v_dealer_lng, PCDEALERLAT=v_dealer_lat, 
PCDEALERLEVER=v_dealer_lever, PCDEALERTEL=v_dealer_tel, PCDEALEROFFICEHOURS=v_report_val18, PCDEALERSHTEL=v_report_val19, PCDEALERAFTERHOURS=v_report_val20, 
PCKFRXTEL=v_report_val21, PCYXYYTEL=v_report_val22, PC24HELPTEL=v_report_val23, PCECONTACT=v_report_val24, PCSA=v_report_val25, PCHELPER=v_report_val26, PCCUSTOM=v_report_val27, 
PCCUSTOMMANAGER=v_report_val28, PCSCMANAGER=v_report_val29, PCSCZY=v_report_val30, PCSHMANAGER=v_report_val31, PCONLINEMANAGER=v_report_val32, PCONLINESELLER=v_report_val33, PCSELLERMANAGER=v_report_val34, 
PCSELLERONE=v_report_val35, PCSELLERSUPPORT=v_report_val36, PCXBZY=v_report_val37, PCTOPMANAGER=v_report_val38,UDATE=NOW(),MICROISPUBLIC=v_report_val40,MICROISYZFFY= v_report_val41,WAPPV=v_report_val1w, WAPUV=v_report_val2w   where thedate = p_i_date and dealerid = v_dealer_id;
 
END IF;

SET v_fetch_ok = 0;

END IF;
END LOOP; 
CLOSE v_dealer;




SET p_o_state = (SELECT COUNT(1) FROM t_sc_datareportnew WHERE THEDATE = p_i_date);

END

fun_getmaxonlinetime

`dealercode` varchar(30),`curdate` LONG,`predate` LONG

BEGIN
DECLARE v_dealer_code VARCHAR(30);  -- 经销商代码
DECLARE v_dealer_onlinetime LONG;
DECLARE v_curr_date_long LONG; -- 当前日期LONG值
DECLARE v_pre_date_long LONG; -- 前一天日期LONG值
DECLARE v_no_dealer BOOLEAN DEFAULT TRUE;
DECLARE v_pre_date VARCHAR(15);
DECLARE cur_dealer CURSOR FOR SELECT DEALERCODE FROM t_dealer;--  WHERE ISOPEN=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_dealer = FALSE;
SET v_curr_date_long = UNIX_TIMESTAMP(CURRENT_DATE())*1000; 
SET v_pre_date_long = v_curr_date_long - 24*60*60*1000;
SET v_pre_date = date_sub(CURRENT_DATE(),interval 1 day);
OPEN cur_dealer;
dealer_while:WHILE  v_no_dealer DO
	 FETCH cur_dealer INTO v_dealer_code;
		IF !v_no_dealer THEN
			LEAVE dealer_while;
		END IF;
	 -- 查询有退出时间的onlinetime,同时获取最大在线时间
	  SELECT fun_getmaxonlinetime(v_dealer_code,v_curr_date_long,v_pre_date_long) INTO v_dealer_onlinetime;
		START TRANSACTION; -- 开启事务
		 INSERT INTO t_onlinetimerecord (onlinetime,dateline,dealercode,cdate)VALUES(v_dealer_onlinetime,v_pre_date,v_dealer_code,NOW());
		  UPDATE t_onlinetime as onlinetime SET onlinetime.`status`=2 WHERE onlinetime.dealercode = v_dealer_code AND onlinetime.`status` = 1;
		 COMMIT;
		-- END IF;
END WHILE dealer_while;
CLOSE cur_dealer;
END

pro_online_record

BEGIN
DECLARE v_dealer_code VARCHAR(30);  -- 经销商代码
DECLARE v_dealer_onlinetime LONG;
DECLARE v_curr_date_long LONG; -- 当前日期LONG值
DECLARE v_pre_date_long LONG; -- 前一天日期LONG值
DECLARE v_no_dealer BOOLEAN DEFAULT TRUE;
DECLARE v_pre_date VARCHAR(15);
DECLARE cur_dealer CURSOR FOR SELECT DEALERCODE FROM t_dealer;--  WHERE ISOPEN=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_dealer = FALSE;
SET v_curr_date_long = UNIX_TIMESTAMP(CURRENT_DATE())*1000; 
SET v_pre_date_long = v_curr_date_long - 24*60*60*1000;
SET v_pre_date = date_sub(CURRENT_DATE(),interval 1 day);
OPEN cur_dealer;
dealer_while:WHILE  v_no_dealer DO
	 FETCH cur_dealer INTO v_dealer_code;
		IF !v_no_dealer THEN
			LEAVE dealer_while;
		END IF;
	 -- 查询有退出时间的onlinetime,同时获取最大在线时间
	  SELECT fun_getmaxonlinetime(v_dealer_code,v_curr_date_long,v_pre_date_long) INTO v_dealer_onlinetime;
		START TRANSACTION; -- 开启事务
		 INSERT INTO t_onlinetimerecord (onlinetime,dateline,dealercode,cdate)VALUES(v_dealer_onlinetime,v_pre_date,v_dealer_code,NOW());
		  UPDATE t_onlinetime as onlinetime SET onlinetime.`status`=2 WHERE onlinetime.dealercode = v_dealer_code AND onlinetime.`status` = 1;
		 COMMIT;
		-- END IF;
END WHILE dealer_while;
CLOSE cur_dealer;
END