# coding: utf-8
"""
# @Time : 2020/11/26 15:32
# @Author : Gina Gao
# @File :
# @Software: PyCharm
# @Descript:
"""
import openpyxl
import time
dbname={
'CH_DB_MID':'CHDB',
'CGENIUS' :'CG',
'PGENIUS' :'PG',
'DATAPOOL':'DP'
}
tranconfig={
'CH_DB_MID|CGENIUS':'TRAN_CHDB_C',
'CGENIUS|PGENIUS' :'TRAN_C_P',
'DATAPOOL|CGENIUS' :'TRAN_DP_C',
'DATAPOOL|PGENIUS' :'TRAN_DP_P',
'DATAPOOL|CUSTOMIZE':'TRAN_DP_CU'
}
path=r'E:\数据\Z.规划设计部\1-策划工作规范\002-策划案&转换文档模板.xlsx'
file_path=r'E:\转换开发2020\\'
#读表操作
workbook=openpyxl.load_workbook(path)
sheet_names = workbook.sheetnames
sheet=workbook['策划案转换文档模板'] ##等价sheet=workbook[sheet_names[1]]
# print(sheet.cell(1,2).value)
dbs=dbname.get(sheet['B4'].value)
dbm=dbname.get(sheet['B5'].value)
tran=tranconfig.get("{}|{}".format(sheet['B4'].value,sheet['B5'].value))
row1='USE'+'\t'+''+tran+'\n'+'GO'+'\n'
row1+=('/' + '*'*66)
row1+='\n'
codename=sheet['B1'].value
tabname=dbs+'_'+sheet['C8'].value+'_2_'+dbm+'_'+sheet['I8'].value
procname="{}:{}_{}".format('名 称',codename,tabname)
file_name111="{}_{}".format(codename,tabname)
row1+=procname
row1+='\n'
row1+=('源 表:'+ sheet['B4'].value+'\t'+sheet['B8'].value+'\t'+sheet['C8'].value)
row1+='\n'
row1+=('目的表:'+sheet['B5'].value+'\t'+sheet['H8'].value+'\t'+sheet['I8'].value)
row1+='\n'
row1+=('创 建:'+'GINA'+'\t'+time.strftime("%Y-%m-%d", time.localtime()) )
row1+='\n'
row1+=('文档路径:'+path)
row1+='\n'
row1+=('示 例:' +'\n'+'select ' + '\''+sheet['I8'].value+ '\''+','+'\''+tabname+'\''+','+'\''+sheet['I8'].value+ '\''+'\n'+'from '+sheet['B4'].value+'..'+sheet['C8'].value+'\n'+ 'where isvalid=1' )
row1+='\n'
row1+=('*'*66+ '/')
row1+='\n'
row1+=('CREATE PROC '+ dbs+'_'+sheet['C8'].value+'_2_'+dbm+'_'+sheet['I8'].value+'AS (@SEQ INT)'+'\n')
row1+=('AS'+'\n')
row1+=('BEGIN'+'\n')
row1+=('\tSELECT'+'\n')
row1+=('\t SEQ AS SEQ'+'\n')
row1+=('\t'+','+'GETDATE() AS MTIME'+'\n')
row1+=('\t'+','+'ISVALID AS ISVALID'+'\n')
max_len = max(len(i[1].value) for i in sheet[10:27])
for i in sheet[10:27]:
row1 +=('\t,'+i[1].value.ljust(max_len,' ')+' as '+i[7].value)
row1 += '\n'
row1+=('FROM '+sheet['B4'].value+'..'+sheet['C8'].value+'\n'+'where isvalid=1 and SEQ=@SEQ'+'\n' +'\n')
row1+=('END'+'\n')
file_name ='{}{}.sql'.format(file_path,file_name111)
with open(file_name,'w') as file_obj:
file_obj.write(row1)
