function daylyCalcMeasure($date, $rkw_id, &$rcd_measure_yen_pc, $kView, $arrTodayCap, $measure, $strKwInfo) { //print_r($arrTodayCap); $yenAll = 0; $rcd_measure_yen_pc = 0; switch ($measure) { case "kotei80": $plusSql = " AND (rkw_R201311_flag = 1) "; #固定80サイトあり $plusSql .= " AND (rkw_random_flag != 9) "; #新ランダムなし # $plusSql .= " AND (rkw_naver_flag != 1) ";#ネイバーなし break; //ランダムリンク2014年1月製造 //ランダムリンク2014年1月製造 case "random": $plusSql .= " AND (rkw_R201311_flag != 1) "; #固定80サイトなし $plusSql = " AND (rkw_random_flag = 9) "; #新ランダム有り # $plusSql .= " AND (rkw_naver_flag != 1) ";#ネイバーなし $plusSql .= " AND (rkw_id NOT IN ({$strKwInfo})) "; break; //ランダムリンク2014年2月製造 //ランダムリンク2014年2月製造 case "random2": $plusSql .= " AND (rkw_R201311_flag != 1) "; #固定80サイトなし $plusSql = " AND (rkw_random_flag = 8) "; #新ランダム有り # $plusSql .= " AND (rkw_naver_flag != 1) ";#ネイバーなし $plusSql .= " AND (rkw_id NOT IN ({$strKwInfo})) "; break; //ランダムリンク2014年3月製造 //ランダムリンク2014年3月製造 case "random3": $plusSql .= " AND (rkw_R201311_flag != 1) "; #固定80サイトなし $plusSql = " AND (rkw_random_flag = 7) "; #新ランダム有り # $plusSql .= " AND (rkw_naver_flag != 1) ";#ネイバーなし $plusSql .= " AND (rkw_id NOT IN ({$strKwInfo})) "; break; case "naverlink": $plusSql .= " AND (rkw_R201311_flag != 1) "; #固定80サイトなし $plusSql = " AND (rkw_random_flag != 9) "; #新ランダムなし $plusSql .= " AND (rkw_naver_flag = 1) "; #ネイバーあり $plusSql .= " AND (rkw_id NOT IN ({$strKwInfo})) "; break; } //End switch require 'SQLConnect_jas.php'; $sql = "SELECT rank_crawl.rcr_id, rank_crawl.rcr_rkwid, rank_crawl.rcr_yahooranking, rank_crawl.rcr_googleranking, rank_crawl.rcr_gooranking, rank_keyword.rkw_account_type, rank_keyword.rkw_is_fee, rank_keyword.rkw_word, rank_client.rcl_status, rank_crawl.rcr_datetime "; $sql .= "FROM (rank_crawl LEFT JOIN rank_keyword ON rank_crawl.rcr_rkwid = rank_keyword.rkw_id) "; $sql .= "LEFT JOIN rank_client ON rank_crawl.rcr_rclid = rank_client.rcl_id "; $sql .= "WHERE ( TRUE "; $sql .= $plusSql; $sql .= "AND (rkw_gra_flag != 1) "; $sql .= "AND (rkw_seo_flag != 1) "; $sql .= "AND (rank_crawl.rcr_datetime Like '" . $date . "%') "; $sql .= "AND (rank_keyword.rkw_contract_startdate <= '" . $date . "') "; $sql .= "AND (rank_keyword.rkw_contract_enddate >= '" . $date . "') ) "; $sql .= "Order By rcr_rkwid;"; switch ($measure) { //KリンクSQL case "klink": $sql = "SELECT rank_crawl.rcr_id, rank_crawl.rcr_rkwid, rank_crawl.rcr_yahooranking, rank_crawl.rcr_googleranking, rank_crawl.rcr_gooranking, rank_keyword.rkw_account_type, rank_keyword.rkw_is_fee, rank_keyword.rkw_word, rank_client.rcl_status, rank_crawl.rcr_datetime "; $sql .= "FROM (rank_crawl LEFT JOIN rank_keyword ON rank_crawl.rcr_rkwid = rank_keyword.rkw_id)"; $sql .= "LEFT JOIN rank_client ON rank_crawl.rcr_rclid = rank_client.rcl_id "; $sql .= "LEFT JOIN satellite_branch ON rkw_id = sab_rkwid "; $sql .= "LEFT JOIN satellite_list ON sab_salid = stl_id "; $sql .= "WHERE (TRUE "; $sql .= " AND (rank_crawl.rcr_datetime Like '" . $date . "%') "; $sql .= " AND (rank_keyword.rkw_contract_startdate <= '" . $date . "') "; $sql .= " AND (rank_keyword.rkw_contract_enddate >= '" . $date . "') "; $sql .= " AND (stl_purchase = 'PR4(金井リンク)') "; $sql .= " AND (sab_flag = 0) "; #はってあるキーワード対象 $sql .= " AND (rkw_random_flag != 9) "; #ランダムなし $sql .= " AND (rkw_R201311_flag != 1) "; #80固定サテなし $sql .= " AND (rkw_gra_flag != 1) "; #外注ジラフなし $sql .= " AND (rkw_seo_flag != 1) ) "; #外注SEO㈱なし # $sql .= " AND (rkw_naver_flag != 1) ";#ネイバーなし $sql .= " GROUP BY rkw_id"; # $externalSql .= ""; # $externalSql .= ""; break; //SリンクSQL //SリンクSQL case "slink": $sql = "SELECT rank_crawl.rcr_id, rank_crawl.rcr_rkwid, rank_crawl.rcr_yahooranking, rank_crawl.rcr_googleranking, rank_crawl.rcr_gooranking, rank_keyword.rkw_account_type, rank_keyword.rkw_is_fee, rank_keyword.rkw_word, rank_client.rcl_status, rank_crawl.rcr_datetime "; $sql .= "FROM (rank_crawl LEFT JOIN rank_keyword ON rank_crawl.rcr_rkwid = rank_keyword.rkw_id)"; $sql .= "LEFT JOIN rank_client ON rank_crawl.rcr_rclid = rank_client.rcl_id "; $sql .= "LEFT JOIN satellite_branch ON rkw_id = sab_rkwid "; $sql .= "LEFT JOIN satellite_list ON sab_salid = stl_id "; $sql .= "WHERE (TRUE "; $sql .= " AND (rank_crawl.rcr_datetime Like '" . $date . "%') "; $sql .= " AND (rank_keyword.rkw_contract_startdate <= '" . $date . "') "; $sql .= " AND (rank_keyword.rkw_contract_enddate >= '" . $date . "') "; $sql .= " AND (stl_purchase = 'PR3(SEO株式会社)') "; $sql .= " AND (sab_flag = 0) "; #はってあるキーワードを対象 $sql .= " AND (rkw_random_flag != 9) "; #ランダムなし $sql .= " AND (rkw_R201311_flag != 1) "; #80固定サテなし $sql .= " AND (rkw_gra_flag != 1) "; #外注ジラフなし $sql .= " AND (rkw_seo_flag != 1) ) "; #外注SEO㈱なし # $sql .= " AND (rkw_naver_flag != 1) ";#ネイバーなし $sql .= " GROUP BY rkw_id"; break; } //End switch print "{$sql}<br /><br />"; $arrTendays = array(); //10日課金の計算 $sql2 = makeSqlExternalTendays($date, $measure, $strKwInfo); #施策別でWHEREに追加 print "10日課金 : {$sql2}<br />"; //print "10日: <br>" . $sql2 . "<br><br>"; $rs2 = mysql_db_query($dbName, $sql2); $rsCount2 = mysql_num_rows($rs2); //レコード数 for ($i = 0; $i <= $rsCount2 - 1; $i++) { $tempRow = @mysql_fetch_array($rs2); #2014-02-17 # if($tempRow[rfe_provider_kind]==1) $chgFieldName = "rcr_yahoo_yen"; # if($tempRow[rfe_provider_kind]==2) $chgFieldName = "rcr_google_yen"; # if($tempRow[rfe_provider_kind]==3) $chgFieldName = "rcr_goo_yen"; # $tempSql = "UPDATE `rank_crawl` SET `" . $chgFieldName . "` = '" . $tempRow[rfe_yen] . "' WHERE (`rcr_id` = " . $tempRow[rcr_id] . ");"; // print $tempSql . "<BR><BR>"; # $c_hit = mysql_db_query($dbName,$tempSql) or die('UPDATE error: '.mysql_errno().', '.mysql_error()); if (array_key_exists($tempRow["rcr_rkwid"], $arrTendays)) { $arrTendays[$tempRow["rcr_rkwid"]]["rfe_yen"] += $tempRow["rfe_yen"]; } else { $arrTendays[$tempRow["rcr_rkwid"]] = $tempRow; } } //10日どちららか課金の計算 $sql2 = makeSqlExternalTendaysWhich($date, $measure, $strKwInfo); print "10日課金どちらか : {$sql2}<br />"; #$yahooTenyen = 0; #$googleTenyen = 0; #$gooTenyen = 0; //print "10日: <br>" . $sql2 . "<br><br>"; $rs2 = mysql_db_query($dbName, $sql2); $rsCount2 = mysql_num_rows($rs2); //レコード数 for ($i = 0; $i <= $rsCount2 - 1; $i++) { $tempRow = @mysql_fetch_array($rs2); $yahooTenyen = 0; $googleTenyen = 0; $gooTenyen = 0; # if($tempRow[rfe_provider_kind]==1) $chgFieldName = "rcr_yahoo_yen"; # if($tempRow[rfe_provider_kind]==2) $chgFieldName = "rcr_google_yen"; # if($tempRow[rfe_provider_kind]==3) $chgFieldName = "rcr_goo_yen"; # $tempSql = "UPDATE `rank_crawl` SET `" . $chgFieldName . "` = '" . $tempRow[rfe_yen] . "' WHERE (`rcr_id` = " . $tempRow[rcr_id] . ");"; // print $tempSql . "<BR><BR>"; # $c_hit = mysql_db_query($dbName,$tempSql) or die('UPDATE error: '.mysql_errno().', '.mysql_error()); if ((int) $tempRow['rfe_provider_kind'] === 1) { $yahooTenyen = (int) $tempRow[rfe_yen]; } if ((int) $tempRow['rfe_provider_kind'] === 2) { $googleTenyen = (int) $tempRow[rfe_yen]; } if ((int) $tempRow['rfe_provider_kind'] === 3) { $gooTenyen = (int) $tempRow[rfe_yen]; } if (array_key_exists($tempRow["rcr_rkwid"], $arrTendays)) { if ($yahooYen >= $googleYen) { $tempYen = $yahooYen; } else { $tempYen = $googleYen; } //End if #$arrTendays[$tempRow["rcr_rkwid"]]["rfe_yen"] += $tempRow["rfe_yen"]; $arrTendays[$tempRow["rcr_rkwid"]]["rfe_yen"] += $tempYen; } else { $arrTendays[$tempRow["rcr_rkwid"]] = $tempRow; } } if (!$kView) { ?> PC用画面 <table border=1 cellpadding=3 cellspacing=1> <tr> <td>ID</td> <td>キーワードID</td> <td>キーワード</td> <td>Y順位</td> <td>G順位</td> <td>Goo順位</td> <td>Y金額</td> <td>G金額</td> <td>Goo金額</td> <td>10日課金</td> <td><font color=red>キャップ</font></td> <td>合計</td> </tr> <?php } //SQL文を実行する $isMobile = false; $rs = mysql_db_query($dbName, $sql); $rsCount = mysql_num_rows($rs); //レコード数 for ($i = 0; $i <= $rsCount - 1; $i++) { $row = @mysql_fetch_array($rs); $yahooYen = 0; $googleYen = 0; $gooYen = 0; $tendaysYen = 0; $tempYen = 0; //通常課金 if (!$isMobile && $row[rkw_mobile_flag]) { $isMobile = true; if (!$kView) { ?> </table> <br><br> 携帯用画面 <table border=1 cellpadding=3 cellspacing=1> <tr> <td>ID</td> <td>キーワードID</td> <td>キーワード</td> <td>Y順位</td> <td>G順位</td> <td>Goo順位</td> <td>Y金額</td> <td>G金額</td> <td>Goo金額</td> <td>10日課金</td> <td><font color=red>キャップ</font></td> <td>合計</td> </tr> <?php } } $tendaysYen = 0; if ($row["rkw_account_type"] == 1) { //print "---" . $row[rcr_gooranking] . "---<br>"; $tempYen = feeCalc($row[rcr_rkwid], $row[rcr_yahooranking], $row[rcr_googleranking], $row[rcr_gooranking], $yahooYen, $googleYen, $gooYen); } //日毎どちらか課金の時は、金額の大きいほうを足し合わせる if ($row["rkw_account_type"] == 5) { feeCalc($row[rcr_rkwid], $row[rcr_yahooranking], $row[rcr_googleranking], $row[rcr_gooranking], $yahooYen, $googleYen, $gooYen); if ($yahooYen >= $googleYen) { $tempYen = $yahooYen; } else { $tempYen = $googleYen; } //金額をアップデート require 'SQLConnect_jas.php'; } //End if //n日課金もしくはn日どちらか課金 //account_type=2 : n日課金 if ($row["rkw_account_type"] == 2) { if (array_key_exists($row["rcr_rkwid"], $arrTendays)) { $tendaysYen = (int) $arrTendays[$row["rcr_rkwid"]]["rfe_yen"]; $tempYen = (int) $arrTendays[$row["rcr_rkwid"]]["rfe_yen"]; } else { $tempYen = 0; } } #account_type=3 : n日どちらか課金 if ($row["rkw_account_type"] == 3) { if (array_key_exists($row["rcr_rkwid"], $arrTendays)) { $tendaysYen = (int) $arrTendays[$row["rcr_rkwid"]]["rfe_yen"]; $tempYen = (int) $arrTendays[$row["rcr_rkwid"]]["rfe_yen"]; } else { $tempYen = 0; } } $key = $row[rcr_rkwid]; if (!$kView) { ?> <tr> <td><?php echo $row[rcr_id]; ?> </td> <td><?php echo $row[rcr_rkwid]; ?> </td> <td><?php echo $row[rkw_word]; ?> </td> <td><?php echo $row[rcr_yahooranking]; ?> </td> <td><?php echo $row[rcr_googleranking]; ?> </td> <td><?php echo $row[rcr_gooranking]; ?> </td> <td><?php echo $yahooYen; ?> </td> <td><?php echo $googleYen; ?> </td> <td><?php echo $gooYen; ?> </td> <td><?php echo $tendaysYen; ?> </td> <td> <?php $key = $row[rcr_rkwid]; if ($arrTodayCap[$key] != "") { print "<font color=red>" . $arrTodayCap[$key] . "</font>"; } $arrTodayCap[$key] = "OK"; ?> </td> <td><?php echo $tempYen; ?> </td> </tr> <?php } $yenAll += $tempYen; //施策ごとのPC課金のSUM if ($row["rkw_account_type"] == 1 || $row["rkw_account_type"] == 5) { if (!$isMobile) { $rcd_measure_yen_pc += $tempYen; } } //End if } //for require 'SQLClose.php'; if (!$kView) { ?> </table> <?php } //print "トータルは" . $yenAll . "円です<br>"; #print_r($arrTodayCap); return $yenAll; ?> <?php }
/** ▼ キャップ金額計算 ****************************/ function capCalc(&$arrTodayCap = array()) { //会社名を割り出し $arrClientName = array(); //担当者名を割り出し $arrRcmName = array(); #print $_SERVER['HTTP_REFERER']."<br />"; //デイリー計算用PHP require_once "feeCalc.php"; ob_end_flush(); ob_start('mb_output_handler'); $arrMinusPc = array(); $arrMinusMobile = array(); $arrKeywordMinus = array(); $arrEachWords = array(); //$startDate = date('Y-m-01'); $startDate = $_POST[month] . "-01"; $nowMonth = date('Y-m'); if ($nowMonth == $_POST[month]) { $endDate = date('Y-m-d'); //今日まで } else { $endDate = date('Y-m-t', strtotime($startDate)); //その月の月末 } //End if $arrClientMinusYenPc = array(); $arrClientMinusYenMobile = array(); print $startDate . "~" . $endDate . "<br><br>"; $intDays = (strtotime($endDate) - strtotime($startDate)) / (60 * 60 * 24); /** ▼ DELETE処理 *****************/ #require('SQLConnect.php'); //該当日分を削除 #$sql = "DELETE FROM `rank_cap_dayly` WHERE (`cad_date` Between '" . $startDate . "' and '" . $endDate . "');"; //print $sql . "<BR><BR>"; # $c_hit = mysql_db_query($dbName,$sql) or die('UPDATE error: '.mysql_errno().', '.mysql_error()); //rank_chargeも削除 #$sql = "DELETE FROM `rank_charge` WHERE (`rch_proviso` = '上限差引金額' and rch_month = '" . $_POST[month] . "');"; //print $sql . "<BR><BR>"; # $c_hit = mysql_db_query($dbName,$sql) or die('UPDATE error: '.mysql_errno().', '.mysql_error()); #mysql_close($GLOBALS[dbHandle]); #var_dump($_POST[month]); #exit; //初期化 #for ($l = 0; $l<= $intDays; $l++){ //該当の日付を出します。 # $tempDate = date('Y-m-d', strtotime("+" . $l. " day " . $startDate)) ; # $arrMinusPc[$tempDate] = 0; # $arrMinusMobile[$tempDate] = 0; #}//End for #require('SQLClose.php'); /** ▲ DELETE処理 *****************/ require 'SQLConnect_jas.php'; $sql = "SELECT * FROM `rank_cap_mas` "; $sql .= " WHERE TRUE "; $sql .= " AND ((cap_enddate > '{$startDate}') OR (cap_enddate = '0000-00-00')) "; if ($_POST[month] !== '2014-09') { $sql .= " AND cap_id != 479 "; } //End if print "Cap : " . $sql . "<br><br>"; //SQL文を実行する $rs = mysql_db_query($dbName, $sql); $rsCount = mysql_num_rows($rs); //レコード数 for ($i = 0; $i <= $rsCount - 1; $i++) { $row = @mysql_fetch_array($rs); //print "最高:" . $row[cap_max_yen] . "円<br>"; $restYen = $row[cap_max_yen]; $clientId = $row[cap_clientid]; for ($l = 0; $l <= $intDays; $l++) { #日付ごとに繰り返し //該当の日付を出します。 $tempDate = date('Y-m-d', strtotime("+" . $l . " day " . $startDate)); //print $tempDate . "<br>"; $sql2 = " SELECT cac_id, cac_capid, cac_rkwid, rkw_id, rkw_mobile_flag, rkw_account_type, rkw_rclid, rkw_client, rcm_id, rcm_name "; $sql2 .= " FROM `rank_cap_child` inner join rank_keyword on rkw_id = cac_rkwid "; $sql2 .= " inner join rank_charger_mas on rcm_id = rkw_rcmid "; $sql2 .= " Where(cac_capid = " . $row[cap_id] . " "; $sql2 .= " and rkw_contract_startdate <= '" . $tempDate . "' "; $sql2 .= " and rkw_contract_enddate >= '" . $tempDate . "'"; $sql2 .= " and cac_status = 0 "; $sql2 .= " );"; #print $sql2 . "<br><br>"; #exit; //SQL文を実行する $rs2 = mysql_db_query($dbName, $sql2); $rsCount2 = mysql_num_rows($rs2); //レコード数 //print $rsCount2 . "<br>"; $intDaylyPcAll = 0; $intDaylyMobileAll = 0; $intPcCount = 0; $intMobileCount = 0; for ($j = 0; $j <= $rsCount2 - 1; $j++) { $row2 = @mysql_fetch_array($rs2); $key = $row2[rkw_rclid]; $arrClientName[$key] = $row2[rkw_client]; $arrRcmId[$key] = $row2[rcm_id]; $arrRcmName[$key] = $row2[rcm_name]; if ($row2[rkw_account_type] != 1 && $row2[rkw_account_type] != 5) { die($row2[rkw_id] . "処理できない:" . $row2[rkw_id] . " : " . $row2[rkw_account_type]); } if ($row2[rkw_mobile_flag] == "0") { $intPcCount++; } else { $intMobileCount++; } //End if $sql3 = "SELECT rcr_id, rcr_yahooranking, rcr_googleranking, rcr_gooranking FROM "; $sql3 .= " `rank_crawl` "; $sql3 .= " Where TRUE "; $sql3 .= " and (rcr_datetime Like '" . $tempDate . "%' "; $sql3 .= " and rcr_rkwid = " . $row2[cac_rkwid] . ");"; # print $sql3 . "<br><br>"; //SQL文を実行する $rs3 = mysql_db_query($dbName, $sql3); $rsCount3 = mysql_num_rows($rs3); //レコード数 if ($rsCount3 == 0) { continue; } $rcd_yen_pc = 0; $rcd_yen_mobile = 0; $row3 = @mysql_fetch_array($rs3); //print "id : " . $row2[cac_rkwid] . "<br>"; //print "Y : " . $row3[rcr_yahooranking] . "<br>"; //print "G : " . $row3[rcr_googleranking] . "<br>"; $intFee = feeCalc($row2[cac_rkwid], $row3[rcr_yahooranking], $row3[rcr_googleranking], $row3[rcr_gooranking], $yahooYen, $googleYen, $gooYen); if ($row2[rkw_account_type] == 5) { //大きいほうをとる if ($yahooYen > $googleYen) { $intFee = $yahooYen; } else { $intFee = $googleYen; } //End if } //End if //if($row[cap_id]==4)print $intFee . "<br>"; //if ($row2[cac_rkwid] ==400||$row2[cac_rkwid] ==401) print $intFee . "<br>"; if ($row2[rkw_mobile_flag] == 1) { //モバイルの計算 $intDaylyMobileAll += $intFee; $rcd_yen_mobile = $intFee; //マイナスに行ったら全部加える if ($restYen < 0) { $arrMinusMobile[$tempDate] += $intFee; $arrKeywordMinus[$row2[cac_rkwid]] += $intFee; $arrEachWords[$tempDate][$row2[cac_rkwid]] = $intFee; $arrClientMinusYenMobile[$clientId] += $intFee; } else { //ちょうどかかるときは差を引く if ($restYen < $intFee) { $arrMinusMobile[$tempDate] += $intFee - $restYen; $arrKeywordMinus[$row2[cac_rkwid]] += $intFee - $restYen; $arrEachWords[$tempDate][$row2[cac_rkwid]] = $intFee - $restYen; $arrClientMinusYenMobile[$clientId] += $intFee - $restYen; } //End if } //End if $restYen -= $intFee; } else { //PCの計算 $intDaylyPcAll += $intFee; $rcd_yen_pc = $intFee; //マイナスに行ったら全部加える if ($restYen < 0) { $arrMinusPc[$tempDate] += $intFee; $arrKeywordMinus[$row2[cac_rkwid]] += $intFee; $arrEachWords[$tempDate][$row2[cac_rkwid]] = $intFee; $arrClientMinusYenPc[$clientId] += $intFee; } else { //ちょうどかかるときは差を引く if ($restYen < $intFee) { $arrMinusPc[$tempDate] += $intFee - $restYen; $arrKeywordMinus[$row2[cac_rkwid]] += $intFee - $restYen; $arrEachWords[$tempDate][$row2[cac_rkwid]] = $intFee - $restYen; $arrClientMinusYenPc[$clientId] += $intFee - $restYen; } //End if } //End if $restYen -= $intFee; } //End if //print $intFee. "円<br>"; //print "PC : " . $rcd_yen_pc . "<br>"; //print "Mobile : " . $rcd_yen_mobile . "<br>"; if (false) { ?> <?php echo $tempDate; ?> <br> PC合計:<?php echo $intDaylyPcAll; ?> <br> 携帯合計:<?php echo $intDaylyMobileAll; ?> <br> 残り:<?php echo $restYen; ?> <br> <br><br> <?php } //End If } ob_flush(); flush(); //if($l==0)break(1); } //End for DayLoop } //End for キャップ金額 rank_cap_mas require 'SQLClose.php'; return $arrKeywordMinus; }