MySqlで工事契約情報を管理してみる

# 工事契約情報をMySqlで管理できたらすごく便利でした

## 目的
エクセルでの管理に限界を感じたのでMySqlに移行しました。
工事契約では、一度契約したあとに契約が変更になることがしばしばあります。
「請負金額」が当初契約から変更された場合は、関数sumで集計できます。
ですが、「工事期間」に変更がある場合に最終的な工期を取得することができませんでした
（複数の関数を組み合わせれば可能かもしれませんが、自分にはできなかったですし、関数を管理するのが嫌になりました）。

## データベースの準備
### データベースの作成
```sql
CREATE SCHEMA `new_schema` ;
```
### テーブルの作成
```sql
/* 工事の基本情報 */
CREATE TABLE `base` (
  `Base_id` int(11) NOT NULL AUTO_INCREMENT,
  `Base_construct_cd` char(5) NOT NULL,  
  `Base_clients_cd` varchar(6) DEFAULT '',
  `Base_area_cd` char(1) DEFAULT NULL,
  `Base_construct_name` varchar(255) DEFAULT NULL,
  `Base_public_construct_name` varchar(255) DEFAULT '',
  `Base_construct_address` varchar(255) DEFAULT '',
  `Base_contractor_subcontractor` enum('元請','下請') NOT NULL,  
  `Base_remarks` varchar(255) DEFAULT '',
  `Base_personnel` varchar(255) DEFAULT '',  
  `Base_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Base_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Base_id`)
);

/* 工事の金額・工期 */
CREATE TABLE `amountdate` (
  `AmountDate_id` int(11) NOT NULL AUTO_INCREMENT,
  `AmountDate_contract_date` date NOT NULL,
  `AmountDate_amount` bigint(20) DEFAULT '0',
  `AmountDate_tax` bigint(20) DEFAULT '0',
  `AmountDate_start_date` date DEFAULT NULL,
  `AmountDate_end_date` date DEFAULT NULL,
  `AmountDate_contract_groups` enum('当初','変更') NOT NULL,
  `Amountdate_Base_construct_cd` varchar(5) DEFAULT NULL,
  `Amountdate_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Amountdate_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`AmountDate_id`)
);

/* 受注地区 */
CREATE TABLE `area` (
  `Area_id` int(11) NOT NULL AUTO_INCREMENT,
  `Area_area_cd` char(1) DEFAULT NULL,
  `Area_area_name` varchar(255) DEFAULT NULL,
  `Area_area_name_alias` varchar(255) DEFAULT NULL,
  `Area_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Area_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Area_id`)
);

/* 顧客 */
CREATE TABLE `clients` (
  `Clients_id` int(11) NOT NULL AUTO_INCREMENT,
  `Clients_cd` varchar(6) DEFAULT NULL,
  `Clients_name` varchar(255) DEFAULT NULL,  
  `Clients_remarks` varchar(255) DEFAULT NULL,  
  `Clients_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Clients_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Clients_id`),
  UNIQUE KEY `Client_cd_UNIQUE` (`Clients_cd`)
);

/* 工事の完成日 */
CREATE TABLE `complete` (
  `Complete_id` int(11) NOT NULL AUTO_INCREMENT,
  `Complete_complete_date` date DEFAULT NULL,
  `Complete_Base_construct_cd` varchar(5) DEFAULT NULL,  
  `Complete_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Complete_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Complete_id`)
);

/* 入金額 */
CREATE TABLE `income` (
  `Income_id` int(11) NOT NULL AUTO_INCREMENT,
  `Income_income_date` date DEFAULT NULL,
  `Income_income` bigint(20) DEFAULT '0',
  `Income_Base_construct_cd` varchar(5) DEFAULT NULL,  
  `Income_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Income_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Income_id`)
);

```
### データの挿入
```sql
INSERT INTO `construct_db`.`clients` (`Clients_cd`, `Clients_name`) VALUES ('000001', '総合建設株式会社', 'E');
INSERT INTO `construct_db`.`area` (`Area_area_cd`, `Area_area_name`, `Area_area_name_alias`) VALUES ('1', '東京支店', '東京');
INSERT INTO `construct_db`.`base` (`Base_construct_cd`, `Base_clients_cd`, `Base_area_cd`, `Base_construct_name`, `Base_public_construct_name`, `Base_construct_address`, `Base_contractor_subcontractor`, `Base_personnel`) VALUES ('30001', '000001', '1', '総合建設新社屋', '総合建設株式会社新社屋建設工事', '東京都○○', '元請', '日本太郎');
INSERT INTO `construct_db`.`amountdate` (`AmountDate_contract_date`, `AmountDate_amount`, `AmountDate_tax`, `AmountDate_start_date`, `AmountDate_end_date`, `AmountDate_contract_groups`, `Amountdate_Base_construct_cd`) VALUES ('2018-09-05', '1000000', '80000', '2018-09-06', '2018-12-31', '当初', '30001');
INSERT INTO `construct_db`.`amountdate` (`AmountDate_contract_date`, `AmountDate_amount`, `AmountDate_tax`, `AmountDate_end_date`, `AmountDate_contract_groups`, `Amountdate_Base_construct_cd`) VALUES ('2018-10-01', '500000', '40000', '2019-01-31', '変更', '30001');
INSERT INTO `construct_db`.`income` (`Income_income_date`, `Income_income`, `Income_Base_construct_cd`) VALUES ('2018-09-05', '500000', '30001');
INSERT INTO `construct_db`.`clients` (`Clients_cd`, `Clients_name`) VALUES ('000002', '東京○○不動産');
INSERT INTO `construct_db`.`base` (`Base_construct_cd`, `Base_clients_cd`, `Base_area_cd`, `Base_construct_name`, `Base_public_construct_name`, `Base_construct_address`, `Base_contractor_subcontractor`, `Base_personnel`) VALUES ('30002', '000002', '1', '東京○○不動産舗装', '東京○○不動産駐車場舗装工事', '東京都○○区', '下請', '日本次郎');
INSERT INTO `construct_db`.`amountdate` (`AmountDate_contract_date`, `AmountDate_amount`, `AmountDate_tax`, `AmountDate_start_date`, `AmountDate_end_date`, `AmountDate_contract_groups`, `Amountdate_Base_construct_cd`) VALUES ('2018-09-15', '300000', '24000', '2018-09-16', '2018-09-30', '当初', '30002');
INSERT INTO `construct_db`.`income` (`Income_income_date`, `Income_income`, `Income_Base_construct_cd`) VALUES ('2018-10-31', '324000', '30002');
INSERT INTO `construct_db`.`complete` (`Complete_complete_date`, `Complete_Base_construct_cd`) VALUES ('2018-09-30', '30002');
```

## データの取得
### sqlの発行
```sql
SELECT
  Base_construct_cd AS 工事番号,   
  CONCAT(Base_construct_cd, Base_construct_name) AS 社内工事名,
  Base_public_construct_name AS 正式工事名,
  Clients_name AS 発注者,
  Area_area_name AS 受注部門, 
  CAST(COALESCE(税抜 + 消費税,'0') AS SIGNED) AS 税込,  
  CAST(COALESCE(入金,'0') AS SIGNED) AS 入金, 
  CAST(COALESCE(税抜 + 消費税 - 入金,税抜 + 消費税) AS SIGNED) AS 債権残高,
  COALESCE(AmountDate_start_date,'') AS 工期開始, 
  COALESCE(AmountDate_end_date,'') AS 工期終了,  
  Base_remarks AS 備考,  
  CASE WHEN Complete_complete_date IS NULL THEN '' ELSE Complete_complete_date END AS 完成計上日  
FROM
  base
  /* 変更契約後の最新の工期開始日を取得して連結 */
  LEFT OUTER JOIN(
    SELECT 
      AmountDate_Base_construct_cd,       
      AmountDate_start_date
    FROM
      amountdate
    WHERE
      AmountDate_id IN (
        SELECT 
	      MAX(AmountDate_id)
	    FROM
		  amountdate
		WHERE
          AmountDate_start_date IS NOT NULL 
          AND AmountDate_start_date != ''   
	    GROUP BY 
          AmountDate_Base_construct_cd
	  )
  ) AS current_start_date_tb ON Base_construct_cd = current_start_date_tb.AmountDate_Base_construct_cd
  /* 変更契約後の最新の工期終了日を取得して連結 */
  LEFT OUTER JOIN(
    SELECT 
      AmountDate_Base_construct_cd,       
      AmountDate_end_date
    FROM
      amountdate
    WHERE
      AmountDate_id IN (
        SELECT 
	      MAX(AmountDate_id)
	    FROM
		  amountdate
		WHERE
          AmountDate_end_date IS NOT NULL 
          AND AmountDate_end_date != ''   
	    GROUP BY 
          AmountDate_Base_construct_cd
	  )
  ) AS current_end_date_tb ON Base_construct_cd = current_end_date_tb.AmountDate_Base_construct_cd
  /* 集計した工事の税抜金額と消費税を取得して連結 */
  LEFT OUTER JOIN(
    SELECT
      AmountDate_Base_construct_cd,       
      SUM(AmountDate_amount) AS 税抜,
      SUM(AmountDate_tax) AS 消費税       
	FROM 
      amountdate
	GROUP BY 
      AmountDate_Base_construct_cd
  ) AS sum_amount_tb ON Base_construct_cd = sum_amount_tb.AmountDate_Base_construct_cd
  /* 集計した入金額を取得して連結 */
  LEFT OUTER JOIN(
    SELECT
      Income_Base_construct_cd,      
      SUM(Income_income) AS 入金      
	FROM 
      income 
	GROUP BY 
      Income_Base_construct_cd
  ) AS sum_income_tb ON Base_construct_cd = sum_income_tb.Income_Base_construct_cd
  /* 工事の完成日を取得して連結 */
  LEFT OUTER JOIN(
    SELECT 
      Complete_Base_construct_cd,      
      Complete_complete_date      
	FROM 
      complete
	GROUP BY 
      Complete_Base_construct_cd
  ) AS complete_date_db ON Base_construct_cd = complete_date_db.Complete_Base_construct_cd
  /* 地区を取得して連結 */
  LEFT OUTER JOIN
    area ON Base_area_cd = Area_area_cd
  /* 顧客名を取得して連結 */
  LEFT OUTER JOIN
    clients ON Base_clients_cd = Clients_cd
ORDER BY
  Base_construct_cd  
;
```
### 結果の確認
上記sqlの結果
![20180905sql.png](https://qiita-image-store.s3.amazonaws.com/0/213168/c8910a47-1a97-2814-72a1-83963171d70e.png)

amountdateのデータ
![20180905sql2.png](https://qiita-image-store.s3.amazonaws.com/0/213168/430e3c92-3a05-2e95-d140-2d2670812f91.png)

結果を確認すると、工事番号30001、30002ともに金額は集計となっていて、
工期終了は工事番号30001は変更契約後の最終的な工期が表示されています。

## 改良点
上記で確認した結果に、原価、回収予定、支払条件などの情報を追加すれば、債権管理、資金繰り、原価管理などに応用できると思います。
エクセルの関数でこれをしようとすると大変ややこしくなると思いますが、データベースを使用すれば意外と簡単にできると思います。