memo

### const.py

```
# Excelファイルパス
excelFilePath = "./"
# Excelファイル名
excelFileName = "テーブル定義書.xlsx"
# Migrationファイル出力先
laravelMigrationFilePath = excelFilePath
# Migrationファイル名
laravelMigrationTimeStamp = "0000_00_00_000000"
# ファイル文字コード
fileEncode = "UTF-8"
# 改行コード
fileIndention = "\n"
# テーブル定義書除外シート一覧
exceptionSheetName = ['テーブル一覧','テーブル定義']
# テーブル情報
indexTableData = 4
# テーブル名
indexTableNmae = 6
# テーブルID
indexTableId = 36
# カラム情報開始行
indexColumnRow = 7

# ***************************************
# テーブル定義情報
# ***************************************
# 論理名
indexLogicalName = 2
# 物理名
indexPhysicalName = 14
# 型
indexShape = 26
# 符号付
indexUnsigned = 29
# ０埋め
indexZeroFill = 31
# カラム長さ
indexLength = 33
# カラム長さ（小数）
indexFew = 35
# PK
indexPrimaryKey = 40
# FK
indexForeignKey = 42
# UK
indexUniqueKey = 44
# AI
indexAutoIncrement = 46
# NN
indexNotNull = 48
# DF
indexDefault = 50
# IDX
indexIdx = 52
```

### common.py
```
import re
import const

# スネークケース To キャメルケース変換
def snakeCaseToCamelCase(strValue):
    return re.sub("_(.)",lambda x:x.group(1).upper(),strValue)

def spaceInsert(spaceCount):
    strScript = ""
    for num in range(spaceCount):
        strScript = strScript + " "
    return strScript

def bracketsStart():
    return "{" + const.fileIndention

def bracketsEnd():
    return "}" + const.fileIndention

def strSpaceInsert(spaceCount, strValue):
    return spaceInsert(spaceCount) + strValue

def strNone(strValue):
    if strValue is None:
        return ""
    return strValue
```

### docTableDefinition.py
```
import common
import const

def columnDefinition(rowData):
    addrs = []
    addrs.append(common.strNone(rowData[const.indexLogicalName].value))
    addrs.append(common.strNone(rowData[const.indexPhysicalName].value))
    addrs.append(common.strNone(rowData[const.indexShape].value))

    indexUnsignedFlg = str(False)
    if rowData[const.indexUnsigned].value is not None:
        indexUnsignedFlg = str(True)
    addrs.append(indexUnsignedFlg)

    indexZeroFillFlg = str(False)
    if rowData[const.indexZeroFill].value is not None:
        indexZeroFillFlg = str(True)
    addrs.append(indexZeroFillFlg)

    addrs.append(str(common.strNone(rowData[const.indexLength].value)))
    addrs.append(str(common.strNone(rowData[const.indexFew].value)))

    indexPrimaryKeyFlg = str(False)
    if rowData[const.indexPrimaryKey].value is not None:
        indexPrimaryKeyFlg = str(True)
    addrs.append(indexPrimaryKeyFlg)

    indexForeignKeyFlg = str(False)
    if rowData[const.indexForeignKey].value is not None:
        indexForeignKeyFlg = str(True)
    addrs.append(indexForeignKeyFlg)

    indexUniqueKeyFlg = str(False)
    if rowData[const.indexUniqueKey].value is not None:
        indexUniqueKeyFlg = str(True)
    addrs.append(indexUniqueKeyFlg)

    indexAutoIncrementFlg = str(False)
    if rowData[const.indexAutoIncrement].value is not None:
        indexAutoIncrementFlg = str(True)
    addrs.append(indexAutoIncrementFlg)

    indexNotNullFlg = str(False)
    if rowData[const.indexNotNull].value is not None:
        indexNotNullFlg = str(True)
    addrs.append(indexNotNullFlg)

    indexDefaultFlg = str(False)
    if rowData[const.indexDefault].value is not None:
        indexDefaultFlg = str(True)
    addrs.append(indexDefaultFlg)

    return addrs
```

### laravelMigrations.py
```
import const
import common

# Laravel Migration Class 名変換
def snakeCaseToCamelCaseForLaravelMigrationClassName(strValue):
    laravelMigrationClassName = common.snakeCaseToCamelCase(strValue)
    return laravelMigrationClassName[0].upper() + laravelMigrationClassName[1:]

# Laravel Migration ヘッダー部
def laravelMigrationHeader(strValue):
    strScript = "<?php" + const.fileIndention
    strScript = strScript + const.fileIndention
    strScript = strScript + "use Illuminate\Support\Facades\Schema;" + const.fileIndention
    strScript = strScript + "use Illuminate\Database\Schema\Blueprint;" + const.fileIndention
    strScript = strScript + "use Illuminate\Database\Migrations\Migration;" + const.fileIndention
    strScript = strScript + const.fileIndention
    strScript = strScript + "class " + snakeCaseToCamelCaseForLaravelMigrationClassName(strValue) + " extends Migration" + const.fileIndention
    strScript = strScript + common.bracketsStart()
    return strScript

# Laravel Migration フッター部
def laravelMigrationFooter(strValue):
    return common.bracketsEnd()

# Laravel Migration Up Method ヘッダー部
def laravelMigrationUpHeader(strValue):
    strScript = common.strSpaceInsert(4,"/**" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* マイグレーション実行" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* @return void" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*/" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,"public function up()" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsStart())
    return strScript

def laravelMigrationUpSchema(strValue, aryColumns):
    strScript = common.strSpaceInsert(8,"Schema::create('"+ strValue +"', function (Blueprint $table) {" + const.fileIndention)
    for column in aryColumns:
        strScript = strScript + common.strSpaceInsert(12, column + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableEngine() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableCharset() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableCollation() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(8,"});" + const.fileIndention)
    return strScript
        
# Laravel Migration Up Method フッター部
def laravelMigrationUpFooter(strValue):
    return common.strSpaceInsert(4,common.bracketsEnd())

# Laravel Migration Down Method
def laravelMigrationDown(strValue):
    strScript = common.strSpaceInsert(4,"/**" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* マイグレーションを元に戻す" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* @return void" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*/" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,"public function down()" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsStart())
    strScript = strScript + common.strSpaceInsert(8,"Schema::drop('" + strValue + "');" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsEnd())
    return strScript

# Laravel Migration Script
def laravelMigrationScript(strValue, aryColumns):
    strScript = laravelMigrationHeader(strValue)
    strScript = strScript + laravelMigrationUpHeader(strValue)
    strScript = strScript + laravelMigrationUpSchema(strValue, aryColumns)
    strScript = strScript + laravelMigrationUpFooter(strValue)
    strScript = strScript + laravelMigrationDown(strValue)
    strScript = strScript + laravelMigrationFooter(strValue)
    return strScript

def laravelMigrationTableEngine(strValue = "InnoDB"):
    return "$table->engine = '" + strValue + "';"

def laravelMigrationTableCharset(strValue = "utf8mb4"):
    return "$table->charset = '" + strValue + "';"

def laravelMigrationTableCollation(strValue = "utf8mb4_unicode_ci"):
    return "$table->collation = '" + strValue + "';"

def laravelMigrationTableIncrements(strValue, strShape):
    strScript = ""

    if strShape == 'bigint':
        strScript = "$table->bigIncrements('" + strValue + "')"
    elif strShape == 'integer':
        strScript = "$table->increments('" + strValue + "')"
    elif strShape == 'mediumint':
        strScript = "$table->mediumIncrements('" + strValue + "')"
    elif strShape == 'smallint':
        strScript = "$table->smallIncrements('" + strValue + "')"
    elif strShape == 'tinyint':
        strScript = "$table->tinyIncrements('" + strValue + "')"
    else:
        strScript = "$table->increments('" + strValue + "')"

    return strScript

def laravelMigrationTableString(strValue, strShape, strLength):
    strScript = ""

    if strShape == 'char':
        strScript = "$table->char('" + strValue + "', " + strLength + ")"
    elif strShape == 'varchar':
        strScript = "$table->string('" + strValue + "', " + strLength + ")"
    elif strShape == 'text':
        strScript = "$table->text('" + strValue + "')"
    elif strShape == 'mediumText':
        strScript = "$table->mediumText('" + strValue + "')"
    elif strShape == 'longText':
        strScript = "$table->longText('" + strValue + "')"

    return strScript

def laravelMigrationTableNumerical(strValue, strShape):
    strScript = ""
    if strShape == 'bigint':
        strScript = "$table->bigInteger('" + strValue +  "')"
    elif strShape == 'integer':
        strScript = "$table->integer('" + strValue +  "')"
    elif strShape == 'mediumint':
        strScript = "$table->mediumInteger('" + strValue +  "')"
    elif strShape == 'smallint':
        strScript = "$table->smallInteger('" + strValue +  "')"
    elif strShape == 'tinyint':
        strScript = "$table->tinyInteger('" + strValue +  "')"
    elif strShape == 'unsignedbigint':
        strScript = "$table->unsignedBigInteger('" + strValue +  "')"
    elif strShape == 'unsignedinteger':
        strScript = "$table->unsignedInteger('" + strValue +  "')"
    elif strShape == 'unsignedmediumint':
        strScript = "$table->unsignedMediumInteger('" + strValue +  "')"
    elif strShape == 'unsignedsmallint':
        strScript = "$table->unsignedSmallInteger('" + strValue +  "')"
    elif strShape == 'unsignedtinyint':
        strScript = "$table->unsignedTinyInteger('" + strValue +  "')"

    return strScript

def laravelMigrationTableComment(strValue, strLogicalName):
    return strValue + "->comment('" + strLogicalName + "');"
```

### laravelMigrationCreateScript.py
```
import openpyxl
import pprint

import common
import const
import laravelMigrations
import docTableDefinition

wb = openpyxl.load_workbook(const.excelFilePath + const.excelFileName)

for sheets in wb.sheetnames:
    rows = []
    strTableId = wb[sheets].cell(row=const.indexTableData, column=const.indexTableId).value
    for row in wb[sheets].iter_rows(min_row=const.indexColumnRow):
        if row[const.indexLogicalName].value is None:
            break

        # [0] :論理名
        # [1] :物理名
        # [2] :型
        # [3] :符号付
        # [4] :０埋め
        # [5] :カラム長さ
        # [6] :カラム長さ（小数）
        # [7] :PK
        # [8] :FK
        # [9] :UK
        # [10]:AI
        # [11]:NN
        # [12]:DF
        # [13]:IDX

        strScriptData = docTableDefinition.columnDefinition(row)
        strScript = ""

        # Auto Increment
        if strScriptData[10] == "True":
            strScript = laravelMigrations.laravelMigrationTableIncrements(strScriptData[1], strScriptData[2])
        
        # 文字列カラム
        elif strScriptData[2] == "char" \
            or strScriptData[2] == "varchar" \
            or strScriptData[2] == "text" \
            or strScriptData[2] == "mediumText" \
            or strScriptData[2] == "longText" :
            strScript = laravelMigrations.laravelMigrationTableString(strScriptData[1], strScriptData[2], strScriptData[5])

        # 数値カラム
        elif strScriptData[2] == "bigint" \
            or strScriptData[2] == "integer" \
            or strScriptData[2] == "mediumint" \
            or strScriptData[2] == "smallint" \
            or strScriptData[2] == "tinyint" :

            if strScriptData[3] == "True":
                strScriptData[2] = "unsigned" + strScriptData[2]

            strScript = laravelMigrations.laravelMigrationTableNumerical(strScriptData[1], strScriptData[2])


        # Comment
        strScript = laravelMigrations.laravelMigrationTableComment(strScript, strScriptData[0])
        
        #print(strScript)

        rows.append(strScript)
    
    print(laravelMigrations.laravelMigrationScript(strTableId, rows))

#        print(docTableDefinition.columnDefinition(row))
#        rows.append(docTableDefinition.columnDefinition(row))



#for row in rows:
#    print(row)
#    print(",".join(row))
```

### test.py
```
import openpyxl
import pprint

import const
#import common
import laravelMigrations

#wb = openpyxl.load_workbook("テーブル定義書.xlsx")
wb = openpyxl.load_workbook(const.excelFilePath + const.excelFileName)
print(wb.sheetnames)
#print(len(wb.sheetnames))
#print(wb.worksheets[0].title)

#print("")

for sheets in wb.sheetnames:
    for row in wb[sheets].iter_rows(min_row=const.indexColumnRow):
        addrs = []
        addrs.append(row[const.indexLogicalName].value)
        print(",".join(addrs))
        break


#for row in wb.worksheets[0].iter_rows(min_row=const.indexColumnRow):
#    addrs = []
#    for cell in row:
#        addrs.append(cell.coordinate)
#    print(",".join(addrs))
#    print(row[const.indexLogicalName].value)
#    print(row[const.indexPhysicalName].value)
#    print(row[const.indexShape].value)

#    addrs.append(row[const.indexLogicalName].value)
#   addrs.append(row[const.indexPhysicalName].value)
#    addrs.append(row[const.indexShape].value)
#    indexUnsignedFlg = str(False)
#    if row[const.indexUnsigned].value is not None:
#        indexUnsignedFlg = str(True)
#    addrs.append(indexUnsignedFlg)
#    print(",".join(addrs))
#    break

#    print(wb.worksheets[0].max_row)

#print(common.snakeCaseToCamelCase("aaa_bbb_ccc"))

#print(laravelMigrations.snakeCaseToCamelCaseForLaravelMigrationClassName("aaa_bbb_ccc"))

#print(laravelMigrations.laravelMigrationScript("aaa_bbb_ccc"))
```