""){ $startdate = $_POST[startdate]; $enddate = $_POST[enddate]; } else { $startdate = date("Y-m-d"); $enddate = date("Y-m-d"); } //アクション格納 $actiondata1 = dbselect("action_name,action_cd","`action`","media_type = 1","action_cd",-1);//通常顧客 $actiondata2 = dbselect("action_name,action_cd","`action`","media_type = 2","action_cd",-1);//オイル顧客 //顧客種別 $customer_typedata = dbselect("type_name,customer_type_id","`customer_type`",-1,-1,-1); //メディア種別 $media_typedata[] = array("通常","1"); $media_typedata[] = array("オイル","2"); //売上確定データ $strsql ="SELECT * FROM `customer_handle` WHERE `handle_kbn` = 2 AND `handle_result` = 51 and ordermainid is null limit 100"; $result=@mysql_query($strsql,$my_con); Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug mysql_sql_log2($strsql,$_SERVER['SCRIPT_NAME'].":".__LINE__); $line = 1;$page=1;//処理行数/ページ while ($data = mysql_fetch_array($result)) { $strsql2 ="SELECT ordermainid,operatedate,deliverdate,name,returnflg,SUM(price_total) as total FROM `".$company."ordermain` as om INNER JOIN `".$company."ordersheet` as os USING(ordermainid) WHERE customerid='".$data["customerid"]."' and om.createdate >'".$data["handle_date"]." 00:00:00' and om.createdate <'".$data["handle_date"]." 23:59:00' GROUP BY ordermainid ORDER BY ordermainid DESC "; Testecho ("LINE ".__LINE__." ".$y." ".$strsql2); //debug $result2=@mysql_query($strsql2,$my_con); // mysql_sql_log($strsql2,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); $data2 = mysql_fetch_array($result2); // echo "
" ; // echo $strsql2 ; if($data2["ordermainid"] > 0) { $strsql3 = "UPDATE customer_handle SET ordermainid ='".$data2["ordermainid"]."',amount ='".$data2["total"]."' WHERE handle_id ='".$data["handle_id"]."'"; // echo $strsql3; $result3=@mysql_query($strsql3,$my_con); mysql_sql_log($strsql2,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); } } //アウトバウンド金額取得用広告区分 $strsql_ad = dbselectin("SELECT code FROM ".$company."code WHERE codekind = '144'"," ad_kubun in "); Testecho($strsql_ad); //$price_sum_data = dbselect //$strsql = "SELECT sum(price_total) as price_total,operatedate FROM nagase_ordermain as om inner join nagase_ordersheet as os USING(ordermainid) WHERE $strsql_ad and operatedate between '".$startdate."' and '".$enddate."' GROUP BY operatedate ORDER BY operatedate"; //$price_sum_data = dbselect("sum(price_total) as price_total,DATE_FORMAT(om.createdate,'%Y-%m-%d') as opdate ",$company."ordermain as om inner join ".$company."ordersheet as os USING(ordermainid)",$strsql_ad."and om.createdate between '".$startdate." 00:00:00' and '".$enddate." 23:00:00' GROUP BY opdate ","opdate",-1); //Testecho($strsqldbselect); $price_sum_data01 = dbselect("sum(amount) as price_total,handle_date","customer_handle","`handle_kbn` = 2 and handle_result=51 and customer_type_id ='01' and handle_date between '".$startdate." ' and '".$enddate."' GROUP BY handle_date","handle_date",-1); $price_sum_data02 = dbselect("sum(amount) as price_total,handle_date","customer_handle","`handle_kbn` = 2 and handle_result=51 and customer_type_id ='02' and handle_date between '".$startdate." ' and '".$enddate."' GROUP BY handle_date","handle_date",-1); $price_sum_data03 = dbselect("sum(amount) as price_total,handle_date","customer_handle","`handle_kbn` = 2 and handle_result=51 and customer_type_id ='03' and handle_date between '".$startdate." ' and '".$enddate."' GROUP BY handle_date","handle_date",-1); $price_sum_data04 = dbselect("sum(amount) as price_total,handle_date","customer_handle","`handle_kbn` = 2 and handle_result=51 and customer_type_id ='04' and handle_date between '".$startdate." ' and '".$enddate."' GROUP BY handle_date","handle_date",-1); $price_sum_data06 = dbselect("sum(amount) as price_total,handle_date","customer_handle","`handle_kbn` = 2 and handle_result=51 and customer_type_id ='06' and handle_date between '".$startdate." ' and '".$enddate."' GROUP BY handle_date","handle_date",-1); $price_sum_data90 = dbselect("sum(amount) as price_total,handle_date","customer_handle","`handle_kbn` = 2 and handle_result=51 and customer_type_id ='90' and handle_date between '".$startdate." ' and '".$enddate."' GROUP BY handle_date","handle_date",-1); $call_count90 = dbselect("count(handle_id) as cnt,handle_date","customer_handle","`handle_kbn` = 2 and customer_type_id ='90' and handle_date between '".$startdate." ' and '".$enddate."' and buy_last <= DATE_ADD(curdate(),INTERVAL -380 DAY) and handle_kbn = 2 GROUP BY handle_date","handle_date",-1); Testecho($strsqldbselect); $strsql = "SELECT count(`customerid`) as cnt, `handle_date`, sum(case WHEN `handle_result` >= 50 THEN 1 ELSE 0 END) as fixed, sum(case WHEN `handle_result` = '51' THEN 1 ELSE 0 END) as buy,sum(case WHEN `handle_kbn` = 1 THEN 1 ELSE 0 END) as dm FROM customer_handle WHERE handle_date between '".$startdate."' and '".$enddate."' and handle_kbn <= 2 and handle_result <> '81' GROUP BY handle_date ORDER by handle_date "; $result=@mysql_query($strsql,$my_con); $maxrow=@mysql_num_rows($result); Testecho($strsql); if($_POST["dl"] <> "") { //ファイル名 $strcsvtitle = Sjis(date("Y-m-d_")."アウトバウンド実績.csv"); header("Content-type:application/octet-stream"); header("Content-Disposition:attachment;filename=$strcsvtitle"); header("Connection: close"); ob_end_clean(); echo Sjis("No,実施日,DM発送件数,コール総件数,確定件数,受注件数,NG件数,受注金額\r\n"); $i=1; while ($data = mysql_fetch_array($result)) { /* $strsql = "SELECT count(`customerid`) as cnt FROM customer_handle WHERE handle_date = '".$data["handle_date"]."' AND handle_kbn=1 GROUP BY handle_date"; $result8=@mysql_query($strsql,$my_con);$data8 = mysql_fetch_array($result8);*/ $int_total = getList($price_sum_data,$data["handle_date"]); $data["cnt"] = $data["cnt"]- $data["dm"] ; $data["fixed"] = $data["fixed"] - $data["dm"]; echo $i.","; echo Sprint($data["handle_date"]); // echo Sprint($data["dm"]); // echo Sprint($data["cnt"]); // echo Sprint($data["fixed"]); // echo Sprint($data["buy"]); // echo Sprint($data["fixed"]-$data["buy"]); // echo Sprint((int)$int_total); // echo "\r\n"; $i++; } exit; } ?>
",-1); ?> ～
>
= 50 THEN 1 ELSE 0 END) as fixed, sum(case WHEN `handle_result` = '51' THEN 1 ELSE 0 END) as buy FROM customer_handle WHERE handle_date = '".$data["handle_date"]."' AND customer_type_id = '"; $strsqlbase2 = "' and handle_kbn=2 and handle_result <> '81' GROUP BY handle_date"; $strsql = $strsqlbase1."01".$strsqlbase2;$result2=@mysql_query($strsql,$my_con);$data2 = mysql_fetch_array($result2);//0 $strsql = $strsqlbase1."02".$strsqlbase2;$result3=@mysql_query($strsql,$my_con);$data3 = mysql_fetch_array($result3);//0受注 $strsql = $strsqlbase1."03".$strsqlbase2;$result4=@mysql_query($strsql,$my_con);$data4 = mysql_fetch_array($result4);//500 $strsql = $strsqlbase1."04".$strsqlbase2;$result5=@mysql_query($strsql,$my_con);$data5 = mysql_fetch_array($result5);//500受注 /* $strsql = $strsqlbase1."05".$strsqlbase2;$result6=@mysql_query($strsql,$my_con);$data6 = mysql_fetch_array($result6);*/ $strsql = $strsqlbase1."06".$strsqlbase2;$result7=@mysql_query($strsql,$my_con);$data7 = mysql_fetch_array($result7);//既存 $strsql = $strsqlbase1."90".$strsqlbase2;$result9=@mysql_query($strsql,$my_con);$data9 = mysql_fetch_array($result9);//休眠 /* $strsql = "SELECT count(`customerid`) as cnt FROM customer_handle WHERE handle_date = '".$data["handle_date"]."' AND handle_kbn=1 GROUP BY handle_date"; $result8=@mysql_query($strsql,$my_con);$data8 = mysql_fetch_array($result8);*/ $strsqldm = "SELECT count(`customerid`) as cnt,h.action_cd, CAST(h.action_cd as SIGNED) as action_cd2,media_type,action_name FROM customer_handle as h INNER JOIN action USING(action_cd,media_type) WHERE handle_date = '".$data["handle_date"]."' AND handle_kbn = 1 GROUP BY media_type,h.action_cd ORDER BY media_type,action_name"; $result10=@mysql_query($strsqldm,$my_con);//DM $dispstrDM =":総数"; $inthagaki = 0;$intA4 = 0; while ($data10 = mysql_fetch_array($result10)) { if($data10["media_type"] == "1") { $actiondata = $actiondata1; } else { $actiondata = $actiondata2; } $dispstrDM .= "
"; $dispstrDM .= $data10["cnt"]." :".$data10["action_name"].""; if($data10["action_cd"] < "5") { $inthagaki += $data10["cnt"]; } else { $intA4 += $data10["cnt"]; } } // $int_total = getList($price_sum_data,$data["handle_date"]); $int_total01 = getList($price_sum_data01,$data["handle_date"]); $int_total02 = getList($price_sum_data02,$data["handle_date"]); $int_total03 = getList($price_sum_data03,$data["handle_date"]); $int_total04 = getList($price_sum_data04,$data["handle_date"]); $int_total06 = getList($price_sum_data06,$data["handle_date"]); $int_total90 = getList($price_sum_data90,$data["handle_date"]); $int_total900 = getList($call_count90,$data["handle_date"]); $int_total = (int)$int_total01 + (int)$int_total02 + (int)$int_total03 + (int)$int_total04 + (int)$int_total06 + (int)$int_total90; $data["cnt"] = $data["cnt"] - $data["dm"]; $data["fixed"] = $data["fixed"] - $data["dm"]; // $data["buy"] = $data["dm"] - $data["buy"]; Testecho($strsqldm); $strsqldm2 = "SELECT count(`customerid`) as cnt,h.action_cd,CAST(h.action_cd as SIGNED) as action_cd2,h.media_type,action_name,h.customer_type_id,h.customer_action_day FROM customer_handle as h INNER JOIN action USING(action_cd,media_type) WHERE handle_date = '".$data["handle_date"]."' AND handle_kbn = 1 GROUP BY h.media_type,customer_type_id,customer_action_day,h.action_cd ORDER BY h.media_type,customer_type_id,customer_action_day"; $result12=@mysql_query($strsqldm2,$my_con);//DM $dispstrDM2 = ""; while ($data10 = mysql_fetch_array($result12)) { $dispstrDM2 .= ""; $dispstrDM2 .= $data10["cnt"].":"; $dispstrDM2 .= GetListData($media_typedata,$data10["media_type"],0)."_"; $dispstrDM2 .= $data10["customer_type_id"]."".GetListData($customer_typedata,$data10["customer_type_id"],0)."_"; $dispstrDM2 .= $data10["customer_action_day"]."日_"; $dispstrDM2 .= $data10["action_name"]; $dispstrDM2 .= "
\n"; } ?> 8) { switch (gmp_div_r($j,3)) { //3で割った余りの数 case "0": echo " ";//確定（受注）j-1:cnt j:fixed j+1:buy echo " ";//ＮＧ fixed-buy break; case "1": break; default: echo " "; } } else { echo " "; } if($j == 0) { echo " ";//施策別 } if($j == 7) { echo " "; echo " "; echo " "; echo " "; echo " "; echo " "; } } echo " "; ?> 5) { ?>
No. 実施日 DM発送件数
DM種別		 DM発送件数
施策別		 ﾊｶﾞｷ A4 コール総件数 確定件数 受注件数 NG件数 受注合計 受注金額 ｻﾝﾌﾟﾙ(0) ｻﾝﾌﾟﾙ(0)受注 ｻﾝﾌﾟﾙ(500) ｻﾝﾌﾟﾙ(500)受注 既存 休眠
ｻﾝ0 ｻﾝ0後受注 ｻﾝ500 ｻﾝ500後受注 既存 休眠 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 5回以上
".number_format($data2["buy"]);$sumdata[10] += $data2["buy"];?> ".number_format($data3["buy"]);$sumdata[13] += $data3["buy"];?> ".number_format($data4["buy"]);$sumdata[16] += $data4["buy"];?> ".number_format($data5["buy"]);$sumdata[19] += $data5["buy"];?> ".number_format($data7["buy"]);$sumdata[22] += $data7["buy"];?> ".number_format($data9["buy"]);$sumdata[25] += $data9["buy"];?>
合計 ".number_format($sumdata[$j],0)."".number_format($sumdata[$j + 1],0)."".number_format($sumdata[$j] - $sumdata[$j + 1],0)."".number_format($sumdata[$j],0)."".number_format($sumdata[$j],0)."".number_format($sumdata[$j],0)."".number_format($sumdata01,0)."".number_format($sumdata02,0)."".number_format($sumdata03,0)."".number_format($sumdata04,0)."".number_format($sumdata06,0)."".number_format($sumdata90,0)."".number_format($sumdata900,0)."
No. 実施日 DM発送件数
DM種別		 DM発送件数
施策別		 ﾊｶﾞｷ A4 コール総件数 確定件数 受注件数 NG件数 受注合計 受注金額 ｻﾝﾌﾟﾙ(0) ｻﾝﾌﾟﾙ(0)受注 ｻﾝﾌﾟﾙ(500) ｻﾝﾌﾟﾙ(500)受注 既存 休眠
ｻﾝ0 ｻﾝ0後受注 ｻﾝ500 ｻﾝ500後受注 既存 休眠 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 総件数 確定 受注 NG 5回以上

※確定件数は受注件数+NG件数