define('IN_ECS', true);
define('CLIENT_MULTI_RESULTS', 131072);
$mysql_server_name="192.168.1.156:3306"; //数据库服务器名称
$mysql_username="root"; // 连接数据库用户名
$mysql_password="123456"; // 连接数据库密码
//$mysql_database="log"; // 数据库的名字
$mysql_database=""; // 数据库的名字
// 连接到数据库
$conn=mysql_connect($mysql_server_name, $mysql_username, $mysql_password,1,CLIENT_MULTI_RESULTS) or die('Could not connect : ' . mysql_error($conn));
mysql_select_db($mysql_database, $conn) or die('Could not select database');
$returns =mysql_query("call pro_vip_buyer(@return_status)");
//存储过程
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE return_ok INT DEFAULT 0;
DECLARE vip_id INT;
DECLARE user_id MEDIUMINT(8);
DECLARE user_type TINYINT(1);
DECLARE year_totals DECIMAL(10,2);
DECLARE total_amount DECIMAL(10,2);
DECLARE total_amount_qianli DECIMAL(10,2);
DECLARE into_time DATETIME;
DECLARE vip_remark VARCHAR(255);
DECLARE cs_name VARCHAR(60);
DECLARE user_name VARCHAR(60);
DECLARE end_time DATETIME;
/*定义游标*/
DECLARE tem_cur CURSOR FOR SELECT vb.vip_id,vb.user_id,vb.user_type,vb.year_totals,vb.into_time,vb.vip_remark,vb.cs_name,vb.user_name,vb.end_time FROM v_vip_buyer_temp AS vb WHERE UNIX_TIMESTAMP(vb.end_time) <= UNIX_TIMESTAMP() AND vb.is_delete = '0';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;/*定义系统级错误*/
OPEN tem_cur;
REPEAT
FETCH tem_cur INTO vip_id,user_id,user_type,year_totals,into_time,vip_remark,cs_name,user_name,end_time;/*赋值*/
IF NOT done THEN
/*把过期的VIP买家状态置为1*/
UPDATE v_vip_buyer_temp AS upisdelete SET upisdelete.is_delete='1' WHERE upisdelete.vip_id = vip_id;
/*查找buyer一年以内的定单金额*/
SELECT SUM(voi.order_amount) INTO total_amount FROM v_order_info AS voi WHERE voi.user_id=user_id AND voi.pay_status = '2' AND voi.is_delete = '0' AND TO_DAYS(NOW()) - TO_DAYS(voi.pay_time) <=365;
/*如果定单金额超过20000美金*/
IF total_amount >= 20000 THEN
/*VIP买家*/
INSERT INTO v_vip_buyer_temp(user_id,user_type,year_totals,into_time,vip_remark,cs_name,user_name,end_time,is_delete)VALUES(user_id,'1',total_amount,NOW(),'system_run',cs_name,user_name,FROM_UNIXTIME(UNIX_TIMESTAMP() + (3600*24*365)),'0');
ELSE
/*查找buyer90天以内的定单金额*/
SELECT SUM(voiq.order_amount) INTO total_amount_qianli FROM v_order_info AS voiq WHERE voiq.user_id=user_id AND voiq.pay_status = '2' AND voiq.is_delete = '0' AND TO_DAYS(NOW()) - TO_DAYS(voiq.pay_time) <=90;
/*如果定单金额超过400美金*/
IF total_amount_qianli >= 400 THEN
/*潜力买家*/
INSERT INTO v_vip_buyer_temp(user_id,user_type,year_totals,into_time,vip_remark,cs_name,user_name,end_time,is_delete)VALUES(user_id,'2',total_amount,NOW(),'system_run',cs_name,user_name,FROM_UNIXTIME(UNIX_TIMESTAMP() + (3600*24*90)),'0');
END IF;
END IF;
END IF;
UNTIL done
END REPEAT;
CLOSE tem_cur;
SET return_ok = 1;
END
最新评论