iT邦幫忙

0

php優化程式

各位前輩好,小弟有撈出以下兩個SQL語法要個別輸出成兩個CSV檔案,最近有想把這隻程式優化,因為想說輸出CSV的欄位都一樣想二合一兩段SQL語法只差AND siret IS NOT NULL AND mobile IS NOT NULL AND mobile != ''跟AND siret IS NULL AND mobile IS NULL其他地方都一樣,優化後一樣是要個別輸出成兩個CSV檔案,小弟困擾許久一直想不出要怎麼優化程式碼,不知各位前輩們有什麼好辦法來給小弟指點一下迷津,感激不盡!!!
程式碼如下:

//第一段語法輸出CSV
$sql="SELECT ps_customer.id_customer,ps_customer.mobile,
     ps_customer.chinese_firstname,ps_customer.chinese_lastname,
 	 ps_customer.firstname,ps_customer.lastname,
     ps_customer.msg_subscription,
 	 ps_customer.email,ps_customer.newsletter,
 	 ps_customer.date_upd,ps_orders.id_order
     FROM ps_customer
     LEFT JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer
     LEFT JOIN ps_customer_group ON
     ps_customer_group.id_customer=ps_customer.id_customer
     LEFT JOIN ps_order_cart_rule ON
     ps_order_cart_rule.id_order=ps_orders.id_order
     WHERE (ps_orders.id_order IS NULL OR ps_customer.id_customer NOT IN
     (SELECT id_customer FROM ps_orders WHERE current_state IN
     (1,2,3,4,5,6,10)))
     AND siret IS NOT NULL AND mobile IS NOT NULL AND mobile != ''
     AND date_format(ps_customer.date_add,'%Y-%m'
     )<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m') GROUP BY
     ps_customer.id_customer;";

$date=date("Y_m_d");

$csv_filename = "FirstCouponPromoteOneMonth_$date.csv";
$file_csv = fopen("/home/ubuntu/customer_coupon_list/".$y."-".$m."/".$csv_filename, 'w');
fwrite($file_csv,'客戶編號,中文姓名,中文姓名,英文姓名,英文姓名,行動電話,簡訊訂閱,電子郵件,電子報訂閱,有效會員,無效會員,最新登入時間'."\n");
		
$firstcouponexpiredlastmonth = DB::select($sql);

    if (!empty($firstcouponexpiredlastmonth)){
    
    $delete_sql="DELETE FROM ps_customer_group WHERE id_group = $coupon_group_return";
    DB::delete($delete_sql);

    foreach($firstcouponexpiredlastmonth as $customer){
        //輸出CSV檔案內容
        $customers = array();
        $customers['id_customer'] = $customer->id_customer;
        $customers['chinese_firstname'] = $customer->chinese_firstname;
        $customers['chinese_lastname'] = $customer->chinese_lastname;
        $customers['firstname'] = $customer->firstname;
        $customers['lastname'] = $customer->lastname;
        $customers['mobile'] = $customer->mobile;
        $customers['msg_subscription'] = $customer->msg_subscription;
        $customers['email'] = $customer->email;
        $customers['newsletter'] = $customer->newsletter;
        $customers['id_order'] = $customer->id_order;
        $customers[] = '';
        $customers['date_upd'] = $customer->date_upd;
        fputcsv($file_csv,$customers);
        
        $insert_sql = "INSERT INTO `ps_customer_group` (`id_customer`,`id_group`) VALUES ('{$customer->id_customer}','$coupon_group')";
        DB::insert($insert_sql);
            }
        }
        fclose($file_csv);
        
//第二段語法輸出CSV
$sql="SELECT ps_customer.id_customer,ps_customer.mobile,
     ps_customer.chinese_firstname,ps_customer.chinese_lastname,
 	 ps_customer.firstname,ps_customer.lastname,
     ps_customer.msg_subscription,
 	 ps_customer.email,ps_customer.newsletter,
 	 ps_customer.date_upd,ps_orders.id_order
     FROM ps_customer
     LEFT JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer
     LEFT JOIN ps_customer_group ON
     ps_customer_group.id_customer=ps_customer.id_customer
     LEFT JOIN ps_order_cart_rule ON
     ps_order_cart_rule.id_order=ps_orders.id_order
     WHERE (ps_orders.id_order IS NULL OR ps_customer.id_customer NOT IN
     (SELECT id_customer FROM ps_orders WHERE current_state IN
     (1,2,3,4,5,6,10)))
     AND siret IS NULL AND mobile IS NULL
     AND date_format(ps_customer.date_add,'%Y-%m'
     )<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m') GROUP BY
     ps_customer.id_customer;";

$date=date("Y_m_d");

$csv_filename = "nobind60day_$date.csv";
$file_csv = fopen("/home/ubuntu/customer_coupon_list/".$y."-".$m."/".$csv_filename, 'w');
fwrite($file_csv,'客戶編號,中文姓名,中文姓名,英文姓名,英文姓名,行動電話,簡訊訂閱,電子郵件,電子報訂閱,有效會員,無效會員,最新登入時間'."\n");
			
$nobind60day = DB::select($sql);

    if (!empty($nobind60day)){

    $delete_sql = "DELETE FROM ps_customer_group WHERE id_group = $coupon_group_return";

    DB::delete($delete_sql);

    foreach($nobind60day as $customer){
                        
        $customers = array();
        $customers['id_customer'] = $customer->id_customer;
        $customers['chinese_firstname'] = $customer->chinese_firstname;
        $customers['chinese_lastname'] = $customer->chinese_lastname;
        $customers['firstname'] = $customer->firstname;
        $customers['lastname'] = $customer->lastname;
        $customers['mobile'] = $customer->mobile;
        $customers['msg_subscription'] = $customer->msg_subscription;
        $customers['email'] = $customer->email;
        $customers['newsletter'] = $customer->newsletter;
        $customers['id_order'] = $customer->id_order;
        $customers[] = '';
        $customers['date_upd'] = $customer->date_upd;
        fputcsv($file_csv,$customers);
        //新增群組
        $insert_sql = "INSERT INTO `ps_customer_group` (`id_customer`,`id_group`) VALUES ('{$customer->id_customer}','$coupon_group')";
        DB::insert($insert_sql);
        }
     }
     fclose($file_csv);
柯柯 iT邦新手 5 級 ‧ 2020-03-02 16:31:11 檢舉
不改SQL 語法
$sql = "select * from table ";
相同的部分用成變數
之後
$where = "where id='1'";
query($sql.$where);
$where = "where id='2'";
query($sql.$where);

目前看起來是想要這種把重複的碼取代掉
柯柯大大好,我的確是想把重複的程式碼取代掉,你說不改SQL語法把相同地方改成變數這邊可以再講具體一點嗎?有點不太懂你的意思

1 個回答

0
舜~
iT邦高手 1 級 ‧ 2020-03-02 08:42:28

sql輸出欄位一樣的話,可以考慮用聯集(UNION)合併起來

[sql 1]
UNION
[sql 2]
;

舜~大大好抱歉這麼晚回覆,我有想過合併起來的作法,但是我一樣是要輸出成兩個csv檔案,聯集起來的話判斷會比較難做,所以後來我才沒考慮這麼做

我要發表回答

立即登入回答