編集履歴

質問編集履歴

検索ページのformと、検索処理、データベースへの接続のコードを追記しました。

2019/10/02 05:23

投稿

kpg

スコア23

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -55,3 +55,379 @@
 PHP 7.1.23
 mysql 5.6.43
+```php
+//検索ページのform
+<form method="post" action="sales_report_choiceCheck.php" class="" name="sales_report_check_form" onSubmit="return formCheck()">
+	<table id="table" class="search">
+			<tr>
+				<th style="width:110px;">営業担当者<br>で絞る</th>
+<!-- ①選択条件でドライバーを選択するためにドライバー一覧を取得 -->
+				<td>
+					<select name="driver_id" id="driver_id">
+						<option value="<?php echo $rec['driver_id'] . '-' . $rec['first_name'] . $rec['last_name']; ?>">
+							<?php echo $rec['first_name'] . $rec['last_name']; ?>
+						</option>
+						<?php
+							try {
+								$dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, $options);
+								$dbh->query('SET NAMES utf8');
+								$sql = "SELECT * FROM m_driver_master WHERE retirement=0";
+								$stmt = $dbh->prepare($sql);
+								$stmt->execute();
+								$dbh = null;
+								while(true) {
+									$rec = $stmt->fetch(PDO::FETCH_ASSOC);
+									if($rec === false) {
+										break;
+									}
+									echo '<option name="driver_id" value="' . $rec['driver_id'] . '-' . $rec['first_name'] . $rec['last_name'] . '">' .  $rec['first_name'] . $rec['last_name'] . '</option>';
+								}
+							} catch (PDOException $e)  {
+								exit('顧客データベース接続失敗。'.$e->getMessage());
+							}
+						?>
+					</select>
+				</td>
+			</tr>
+			<tr>
+				<th style="width:110px;">顧客名で<br>絞る</th>
+<!-- ②選択条件で顧客名を選択するために顧客一覧を取得 -->
+				<td>
+					<select name="salesClient_id" id="salesClient_id">
+						<option value="<?php echo $rec2['salesClient_id'] . '-' . $rec2['shop_name']; ?>">
+							<?php echo $rec2['shop_name']; ?>
+						</option>
+						<?php
+							try {
+								$dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, $options);
+								$dbh->query('SET NAMES utf8');
+								$sql = "SELECT * FROM m_salesClient_master WHERE 1";
+								$stmt = $dbh->prepare($sql);
+								$stmt->execute();
+								$dbh = null;
+								while(true) {
+									$rec2 = $stmt->fetch(PDO::FETCH_ASSOC);
+									if($rec2 === false) {
+										break;
+									}
+									echo '<option name="salesClient_id" value="' . $rec2['salesClient_id'] . '-' . $rec2['shop_name'] . '">' . $rec2['shop_name'] . '</option>';
+								}
+							} catch (PDOException $e)  {
+								exit('顧客データベース接続失敗。'.$e->getMessage());
+							}
+						?>
+					</select>
+				</td>
+			</tr>
+			<tr>
+				<th style="width:110px;">日付で絞る</th>
+<!-- ③日付を選択するためのカレンダー -->
+				<td>
+					<div class="calendar-inner">
+						<input class="calendar" type="date" name="sales_date" value="">
+						<img class="calendar-icon" src="../../images/icon_calendar.png">
+					</div>
+				</td>
+			</tr>
+	</table>
+<!-- 登録ボタン -->
+	<div class="button">
+		<input type="submit" class="search_button" value="検索">
+		<p id="notice_9" class="input">必須項目は全て入力して下さい</p>
+	</div>
+</form>
+```
+```php
+//検索処理、データベースへの接続
+// 条件なし
+if(empty($driver_id) && empty($salesClient_id) && empty($sales_date)) {
+  $search_condition = 1;
+// driver_id軸条件
+} else if(!empty($driver_id) && empty($salesClient_id) && empty($sales_date)) {
+  $search_condition = 'a.driver_id=' . $driver_id;
+}
+if(!empty($driver_id) && empty($salesClient_id) && !empty($sales_date)) {
+  $search_condition = 'a.driver_id=' . $driver_id . ' AND sales_date=\'' . $sales_date . "'";
+}
+if(!empty($driver_id) && !empty($salesClient_id) && empty($sales_date)) {
+  $search_condition = 'a.driver_id=' . $driver_id . ' AND a.salesClient_id=' . $salesClient_id;
+// salesClient_id軸条件
+} else if(empty($driver_id) && !empty($salesClient_id) && empty($sales_date)) {
+  $search_condition = 'a.salesClient_id=' . $salesClient_id;
+}
+if(empty($driver_id) && !empty($salesClient_id) && !empty($sales_date)) {
+  $search_condition = 'a.salesClient_id=' . $salesClient_id . ' AND sales_date=\'' . $sales_date . "'";
+// sales_date軸条件
+} else if(empty($driver_id) && empty($salesClient_id) && !empty($sales_date)) {
+  $search_condition = 'sales_date=\'' . $sales_date . "'";
+// すべてに条件設定
+} else if(!empty($driver_id) && !empty($salesClient_id) && !empty($sales_date)) {
+  $search_condition = 'a.driver_id=' . $driver_id . ' AND a.salesClient_id=' . $salesClient_id. ' AND sales_date=\'' . $sales_date . "'";
+}
+try {
+  $dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, $options);
+  $dbh->query('SET NAMES utf8');
+  $sql = "SELECT * FROM t_sales_db AS a
+          JOIN m_salesClient_master AS c ON a.salesClient_id=c.salesClient_id
+          JOIN m_sales_progress_master AS d ON a.progress_id=d.progress_id
+          JOIN m_sales_progress_purpose_master AS e ON a.progress_purpose_id=e.progress_purpose_id
+          JOIN m_driver_master AS b ON a.driver_id=b.driver_id
+					WHERE " . $search_condition . " ORDER BY sales_date DESC";
+  $stmt = $dbh->prepare($sql);
+  $stmt->execute();
+  while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
+    $salesdata[] = $result;
+  }
+  $sql = "SELECT * FROM t_competitor_status_db AS a
+  JOIN m_shouhinn_master AS b ON a.shouhinn_code = b.shouhinn_code
+  JOIN m_driver_master AS c ON a.driver_id=c.driver_id
+	JOIN m_salesClient_master AS d ON a.salesClient_id=d.salesClient_id
+	ORDER BY reflash_date DESC LIMIT 5";
+	$stmt = $dbh->prepare($sql);
+  $stmt->execute();
+  while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
+    $competitorData[] = $row;
+	}
+	$sql = "SELECT driver_id FROM m_driver_master
+	WHERE " .$_SESSION['login'] = 1;
+	$stmt = $dbh->prepare($sql);
+	$stmt->execute();
+	while($thirdResult = $stmt->fetch(PDO::FETCH_ASSOC)) {
+		$driversId = $thirdResult;
+	}
+	$dbh = null;
+} catch (PDOException $e)  {
+  exit('顧客データベース接続失敗。'.$e->getMessage());
+}
+```