programme/FlamezMakeXMLToSql.php at master · flamez57/programme · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
<?php
class FlamezMakeXMLToSql{
    private static $_instance = NULL;
    private $url;
    private $host;
	private $username;
	private $password;
	private $db;
	private $db_name;
    /**
     * @return FlamezMakeXMLToSql
     */
    final public static function getInstance()
    {
        if (!isset(self::$_instance) || !self::$_instance instanceof self) {
            self::$_instance = new self;
        }
        return self::$_instance;
    }
    //拼接sql语句
    public function getSql($url,$host,$username,$password,$db,$db_name)
    {
    	$this->host = $host;
		$this->username = $username;
		$this->password = $password;
		$this->db = $db;
    	$this->url = $url;
    	$this->db_name = $db_name;
    	$sql = "INSERT INTO {$db_name}(conversion_id,conversion_date,offer_id,offer_name,campaign_id,subid_1,subid_2,subid_3,subid_4,subid_5,price,disposition) VALUES";
    	$row = 0;
    	$price = 0;
		// var_dump($this->getArray());
		$tt = $this->toMysql();
		$hl = $this->getArray();
		for($i=0;$i<$hl['row_count'];$i++){
			if(!in_array($hl['con'][$i]['conversion_id'],$tt)){
				$sql .="('{$hl['con'][$i]['conversion_id']}','{$hl['con'][$i]['conversion_date']}','{$hl['con'][$i]['offer_id']}','{$hl['con'][$i]['offer_name']}','{$hl['con'][$i]['campaign_id']}','{$hl['con'][$i]['subid_1']}','','{$hl['con'][$i]['subid_3']}','','{$hl['con'][$i]['subid_5']}','{$hl['con'][$i]['price']}','{$hl['con'][$i]['disposition']}'),";
				$row += 1;
				$price += $hl['con'][$i]['price'];
			}
		}
		$data['row_count'] = $row;
		$data['price'] = $price;
		$data['sql'] = rtrim($sql,',');
		return $data;
    }
	//get方式获取xml文件内容
    private function getXml()
    {
    	$ch = curl_init();
		curl_setopt($ch,CURLOPT_URL,$this->url);
		curl_setopt($ch,CURLOPT_RETURNTRANSFER,1);
		curl_setopt($ch,CURLOPT_HEADER,0);

		$output = curl_exec($ch);
		curl_close($ch);
		return trim($output);
    }
	//将xml转换成数组
    private function XmlToArray($xml)
    {
    	$xml = simplexml_load_string($xml, 'SimpleXMLElement', LIBXML_NOCDATA | LIBXML_NOBLANKS);
		return @json_decode(@json_encode($xml),1);
    }
	//处理数组
    private function getArray()
    {
    	$chu = $this->XmlToArray($this->getXml());
    	$data['row_count'] = $chu['row_count'];
    	$data['price'] = $chu['summary']['price'];
    	$data['con'] = $chu['conversions']['conversion'];
    	return $data;
    }
	//连接查询mysql库
    private function toMysql()
    {
	$dsn="mysql:dbname={$this->db};host={$this->host}";
	try{
	    $pdo=new PDO($dsn,$this->username,$this->password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8"));
	}catch(PDOException $e){
	    return '数据库连接失败'.$e->getMessage();
	}
	$sql = "select conversion_id from {$this->db_name}";
	$res=$pdo->query($sql);
	foreach($res as $row){
	$rows[] = $row['conversion_id'];
	}
	return $rows;
    }
}

// $host = 'localhost';
// $username = 'root';
// $password = '';
// $db = 'text';
// $str = FlamezMakeXMLToSql::getInstance()->getSql('http://etmanonline.com/api/conv.xml',$host,$username,$password,$db,'conver');
// var_dump($str);