php pdo 脚本记录

<?php

 

require 'vendor/autoload.php';

 

use Aws\S3\S3Client;

use Aws\Sts\StsClient;

 

header("Content-type: text/html; charset=utf-8");

ini_set("display_errors", "On"); //打开错误

ini_set("error_reporting", E_ALL);

$time_start = microtime(true);

set_time_limit(0);

// 测试时使用

 

// $dbms = 'mysql';

// $dbName = 'dw_cms';

// $user = 'root';

// $pwd = '2222';

// $host = '127.0.0.1';

 

$charset = 'utf8';

$dsn = "$dbms:host=$host;dbname=$dbName;charset=$charset";

$merchantAccountId = "sub_hk01_001";

 

try {

$pdo = new PDO($dsn, $user, $pwd);

} catch (Exception $e) {

echo $e->getMessage();

}

 

$sql = 'select pay_way,subscribe_start_date,subscribe_end_date,account_id,category_id,price,pay_time,apple_product_id,order_no,is_auto_renew,devices,status from products_subscribe_record where platform_id = 2 AND pay_time <1615305600';

// echo $comment_real_url_list;exit;

$stmt = $pdo->prepare($sql);

$stmt->execute();

 

$orderModel = $stmt->fetchAll(PDO::FETCH_ASSOC);

$track_data = "";

$count = 0;

foreach ($orderModel as $order_key => $orderData) {

try {

$sql2 = 'SELECT product_duration_price,subscribe_pattern_title,product_free_days,category_id,hk_plan_id,product_id FROM products_record_detail WHERE category_id = ? AND product_duration_price = ? ORDER BY id desc LIMIT 1';

//获取价格

$price = $orderData['price'];

$category_id = $orderData['category_id'];

$stmt2 = $pdo->prepare($sql2);

// 绑定参数

$stmt2->bindValue(1, $category_id);

$stmt2->bindValue(2, $price);

// 执行预处理语句

$stmt2->execute();

$detailModel = $stmt2->fetchAll(PDO::FETCH_ASSOC);

if (count($detailModel) == 0) {

continue;

}

$detailData = $detailModel[0];

if (strlen($orderData['subscribe_end_date']) > 13) {

$orderData['subscribe_end_date'] = substr($orderData['subscribe_end_date'], 0, 13);

}

if (strlen($orderData['subscribe_start_date']) > 13) {

$orderData['subscribe_start_date'] = substr($orderData['subscribe_start_date'], 0, 13);

}

$orderData['subscribe_end_date'] = $orderData['subscribe_end_date'] ? date('Y-m-d H:i:s', $orderData['subscribe_end_date'] / 1000) : null;

$orderData['subscribe_start_date'] = $orderData['subscribe_start_date'] ? date('Y-m-d H:i:s', $orderData['subscribe_start_date'] / 1000) : null;

$info = [

'account_id' => $orderData['account_id'],

'order_no' => $orderData['order_no'],

'status' => $orderData['status'],

'price' => $price ? $price : 0,

'product_id' => $detailData['product_id'],

'subscribe_start_date' => $orderData['subscribe_start_date'],

'subscribe_end_date' => $orderData['subscribe_end_date'],

'pay_way' => $orderData['pay_way'],

'is_auto_renew' => $orderData['is_auto_renew'],

'platform_id' => 2,

'subscribe_plans_title' => $detailData['subscribe_pattern_title'],

'subscribe_free_days' => $detailData['product_free_days'],

'mag_id' => $category_id,

'merchant_accountId' => $merchantAccountId,

'plan_id' => $detailData['hk_plan_id'],

'source_platform_order' => $orderData['apple_product_id'],

];

$track_data = $track_data . json_encode($info) . PHP_EOL;

$count++;

// 释放查询结果

$stmt2 = null;

} catch (\Throwable $th) {

//throw $th;

}

}

file_put_contents("data.json", $track_data . PHP_EOL, FILE_APPEND);

echo '个数是' . $count . PHP_EOL;

trackData($track_data, $count);

 

// 关闭连接

$pdo = null;

$stmt1 = null;

 

function trackData($track_data, $count)

{

try {

//换成prod

$Bucket = "sssss-data-external";

$stsClient = new StsClient([

// 'profile' => 'default',

'region' => 'us-west-2',

'version' => '2011-06-15'

]);

//换成prod

$ARN = "xxxxxxxx";

$sessionName = "EBOOK";

 

$result = $stsClient->AssumeRole([

'RoleArn' => $ARN,

'RoleSessionName' => $sessionName,

]);

 

$s3Client = new S3Client([

'version' => '2006-03-01',

'region' => 'us-west-2',

'credentials' => [

'key' => $result['Credentials']['AccessKeyId'],

'secret' => $result['Credentials']['SecretAccessKey'],

'token' => $result['Credentials']['SessionToken']

]

]);

$now_time = '20210309';

$key = 'EBOOK' . DIRECTORY_SEPARATOR . 'FACT_ORDER' . DIRECTORY_SEPARATOR . 'partition_date=' . $now_time . DIRECTORY_SEPARATOR;

// put埋点数据

$res = $s3Client->putObject([

'Bucket' => $Bucket,

'Key' => $key . 'data.json',

'Body' => $track_data,

'ACL' => 'bucket-owner-full-control',

]);

var_dump($res["@metadata"]["effectiveUri"]);

// put 个数

$success_data = ['record_count' => $count, 'ts' => time()];

$res2 = $s3Client->putObject([

'Bucket' => $Bucket,

'Key' => $key . '_SUCCESS',

'Body' => json_encode($success_data),

'ACL' => 'bucket-owner-full-control',

]);

var_dump($res2["@metadata"]["effectiveUri"]);

} catch (\Throwable $th) {

file_put_contents("1", $th . PHP_EOL, FILE_APPEND);

echo ("失败");

var_dump($th);

}

}

 

echo "success" . PHP_EOL;