/// <summary>
/// 获取生成SQL的模版文件路径
/// </summary>
/// <returns>文件路径</returns>
private string GetTemplatePath(TableInfo t)
{
string strPath = AppDomain.CurrentDomain.BaseDirectory + @"Common\";
if (t.IsUpdate)
{
strPath = strPath + "UpdateDic.vm";
}
else
{
strPath = strPath + "DataDic.vm";
}
return strPath;
}
/// <summary>
/// 获取表的主键信息
/// </summary>
/// <param name="xnKeys">节点</param>
/// <param name="pTable">表信息</param>
private void InitKeys(XmlNode xnKeys, TableInfo pTable)
{
List<PkKeyInfo> list = new List<PkKeyInfo>();
if (xnKeys != null && xnKeys.ChildNodes.Count > 0)
{
XmlNode xnKey = xnKeys.ChildNodes[0];
foreach (XmlNode xnP in xnKey.ChildNodes)
{
if (xnP.Name == "c:Key.Columns")
{
foreach (XmlNode xn in xnP.ChildNodes)
{
list.Add(new PkKeyInfo(((XmlElement)xn).GetAttribute("Ref")));
}
}
}
}
pTable.ListPkKeyInfo = list;
}
/// <summary>
/// 生成表的SQL
/// </summary>
private Result GenSQL(TableInfo t)
{
Hashtable param = null;
string tPath = GetTemplatePath(t);
try
{
param = new Hashtable();
param.Add("T", t);
param.Add("V", softVerion);
FileGen.GetFile(tPath, param, txtOutPutPath.Text.Trim() + @"\" + t.Code + ".sql");
return new Result(Level.Success, t.Code);
}
catch (Exception ex)
{
return new Result(Level.Execption, ex.Message);
}
}
/// <summary>
/// 获取节点中表的信息
/// </summary>
/// <param name="xnTable">xmlNode</param>
/// <returns>表信息</returns>
private TableInfo GetTable(XmlNode xnTable)
{
try
{
TableInfo mTable = new TableInfo();
XmlElement xe = (XmlElement)xnTable;
mTable.TableID = xe.GetAttribute("Id");
XmlNodeList xnTProperty = xe.ChildNodes;
foreach (XmlNode xnP in xnTProperty)
{
switch (xnP.Name)
{
//表的ID
case "a:ObjectID":
mTable.TableObjectID = xnP.InnerText;
break;
//表的中文名称
case "a:Name":
mTable.Name = xnP.InnerText;
break;
//表的英文名称
case "a:Code":
mTable.Code = xnP.InnerText;
break;
//表的描述
case "a:Comment":
mTable.Comment = xnP.InnerText;
break;
//表的列信息
case "c:Columns":
InitColumns(xnP, mTable);
break;
//表的主键信息
case "c:Keys":
InitKeys(xnP, mTable);
break;
default:
break;
}
}
if (string.IsNullOrEmpty(mTable.Comment))
{
mTable.Comment = mTable.Name;
}
if (mTable.ListPkKeyInfo != null && mTable.ListPkKeyInfo.Count > 0)
{
foreach (PkKeyInfo pkInfo in mTable.ListPkKeyInfo)
{
ColumnInfo info = mTable.ListColumnInfo.Single(c => c.ColumnId == pkInfo.ColumnId);
pkInfo.Name = info.Code;
info.PK = true;
mTable.PkKeyNameList = mTable.PkKeyNameList + pkInfo.Name + ",";
}
}
//杜冬军2014-05-16 修改没有主键 生成SQL有问题的BUG V1.4
else
{
mTable.ListPkKeyInfo=new List<PkKeyInfo>();
}
if (!string.IsNullOrEmpty(mTable.PkKeyNameList))
{
mTable.PkKeyNameList = mTable.PkKeyNameList.Substring(0, mTable.PkKeyNameList.Length - 1);
}
return mTable;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="xnColumns">列节点</param>
/// <param name="pTable">表信息</param>
private void InitColumns(XmlNode xnColumns, TableInfo pTable)
{
int i = 1;
List<ColumnInfo> list = new List<ColumnInfo>();
pTable.ListColumnInfo = list;
foreach (XmlNode xnColumn in xnColumns)
{
ColumnInfo mColumn = GetColumn(xnColumn);
mColumn.Sequence = i;
pTable.ListColumnInfo.Add(mColumn);
i++;
}
}
/// <summary>
/// 获取节点中表的信息
/// </summary>
/// <param name="table">table</param>
private TableInfo GetTable(Table table)
{
try
{
TableInfo mTable = new TableInfo();
List<ColumnInfo> list = new List<ColumnInfo>();
List<PkKeyInfo> listPkKeyInfo = new List<PkKeyInfo>();
mTable.ListColumnInfo = list;
mTable.ListPkKeyInfo = listPkKeyInfo;
//表的ID
mTable.TableObjectID = Guid.NewGuid().ToString();
//表的中文名称
mTable.Name = Common.GetTableCName(table.Rows[0].Cells[1].Paragraphs[0].Text.Trim());
//表的英文名称
mTable.Code = table.Rows[1].Cells[1].Paragraphs[0].Text.Trim();
//2014-07-28 添加错误详细信息提示,以便准确知道错误地方
Common.JudgeTableInfo(mTable.Code, mTable.Name);
//表的描述
mTable.Comment = mTable.Name;
//标题列
var row = table.Rows[3];
//缓存列索引和名称
Dictionary<int, string> dic = new Dictionary<int, string>();
int i = 0;
foreach (var cell in row.Cells)
{
dic.Add(i, cell.Paragraphs[0].Text.Trim());
i++;
}
for (i = 4; i < table.Rows.Count; i++)
{
row = table.Rows[i];
InitColumns(row, dic, mTable);
}
if (string.IsNullOrEmpty(mTable.Comment))
{
mTable.Comment = mTable.Name;
}
if (mTable.ListPkKeyInfo != null && mTable.ListPkKeyInfo.Count > 0)
{
foreach (PkKeyInfo pkInfo in mTable.ListPkKeyInfo)
{
mTable.PkKeyNameList = String.Format("{0}{1},", mTable.PkKeyNameList, pkInfo.Name);
}
}
if (!string.IsNullOrEmpty(mTable.PkKeyNameList))
{
mTable.PkKeyNameList = mTable.PkKeyNameList.Substring(0, mTable.PkKeyNameList.Length - 1);
}
return mTable;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="row">行节点</param>
/// <param name="dic">列名字典集合</param>
/// <param name="pTable">表信息</param>
private void InitColumns(Row row, Dictionary<int, string> dic, TableInfo pTable)
{
ColumnInfo mColumn = new ColumnInfo();
int iCell = 0;
//列ID
mColumn.ColumnObjectId = Guid.NewGuid().ToString();
string sTemp="";
foreach (var cell in row.Cells)
{
sTemp=cell.Paragraphs[0].Text.Trim();
Common.GetColumnInfo(dic, sTemp, mColumn, iCell, pTable);
iCell++;
}
//2014-07-28 添加错误详细信息提示,以便准确知道错误地方
Common.JudgeColumnInfo(mColumn, pTable.Code);
mColumn.DataTypeStr=Common.GetDataTypeStr(mColumn.DataTypeStr, mColumn.Width);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
//杜冬军2014-07-23修改,添加主键列的判定方式 ,如果中文名称类似 A(主键) 则认为该列是主键列
Common.GetPrimaryKeyInfo(mColumn, pTable);
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
mColumn.Sequence = pTable.ListColumnInfo.Count + 1;
pTable.ListColumnInfo.Add(mColumn);
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="row">列节点</param>
/// <param name="iCell">列起始索引</param>
/// <param name="pTable">表信息</param>
/// <param name="dic">列名字典集合</param>
private void InitColumns(IRow row, int iCell, TableInfo pTable, Dictionary<int, string> dic)
{
ColumnInfo mColumn = new ColumnInfo();
//列ID
mColumn.ColumnObjectId = Guid.NewGuid().ToString();
string sTemp = "";
int LastCellIndex = dic.Keys.Last<int>();
for (int i = dic.Keys.First<int>(); i <= LastCellIndex; i++)
{
//2014-07-01杜冬军修改，row.LastCellNum取出来有误，确保不出现空异常
sTemp = row.GetCell(i) == null ? "" : row.GetCell(i).ToString().Trim();
Common.GetColumnInfo(dic, sTemp, mColumn, i, pTable);
}
//2014-07-28 添加错误详细信息提示,以便准确知道错误地方
Common.JudgeColumnInfo(mColumn, pTable.Code);
mColumn.DataTypeStr = Common.GetDataTypeStr(mColumn.DataTypeStr, mColumn.Width);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
//杜冬军2014-07-23修改,添加主键列的判定方式 ,如果中文名称类似 A(主键) 则认为该列是主键列
Common.GetPrimaryKeyInfo(mColumn, pTable);
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
mColumn.Sequence = pTable.ListColumnInfo.Count + 1;
pTable.ListColumnInfo.Add(mColumn);
}
/// <summary>
/// 将详细表信息转换成List对象
/// </summary>
/// <param name="dt">dt</param>
/// <returns>List<TableInfo></returns>
public static List<TableInfo> DetailDT2List(DataTable dt, List<int> listTableId)
{
List<TableInfo> list = new List<TableInfo>();
TableInfo entity = null;
ColumnInfo colEntity = null;
List<ColumnInfo> listCol = null;
foreach (int tableid in listTableId)
{
DataRow[] drs = dt.Select("TableId=" + tableid);
entity = new TableInfo();
listCol = new List<ColumnInfo>();
foreach (DataRow dr in drs)
{
entity.Code = dr["table_name"].ToString();
entity.Name = dr["table_name_c"].ToString();
entity.IsUpdate = Convert.ToBoolean(dr["IsUpdate"]);
colEntity = new ColumnInfo();
colEntity.Code = dr["field_name"].ToString();
colEntity.Name = dr["field_name_c"].ToString();
colEntity.Sequence = Convert.ToInt32(dr["field_sequence"]);
colEntity.Nullable = Convert.ToBoolean(dr["isnullable"]);
colEntity.PK = Convert.ToBoolean(dr["pk"]);
colEntity.Identity = Convert.ToBoolean(dr["isidentity"]);
colEntity.DataType = dr["date_type"].ToString();
colEntity.DefaultValue = Common.GetDefaultValue(dr["defaultvalue"].ToString(), colEntity.DataType);
colEntity.Width = Common.GetColumnWidth(colEntity.DataType, dr["prec"].ToString(), dr["scale"].ToString());
listCol.Add(colEntity);
}
entity.ListColumnInfo = listCol;
list.Add(entity);
}
return list;
}
/// <summary>
/// 获取节点中表的信息
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="iRow">iRow</param>
/// <param name="iCell">iCell</param>
/// <param name="sheetName">sheetName</param>
/// <returns>表信息</returns>
private int GetTable(ISheet sheet, int iRow, int iCell, List<TableInfo> Tables,string sheetName)
{
try
{
TableInfo mTable = new TableInfo();
List<ColumnInfo> list = new List<ColumnInfo>();
List<PkKeyInfo> listPkKeyInfo = new List<PkKeyInfo>();
mTable.ListColumnInfo = list;
mTable.ListPkKeyInfo = listPkKeyInfo;
mTable.PDName = sheetName;
//表的ID
mTable.TableObjectID = Guid.NewGuid().ToString();
//表的中文名称
mTable.Name = Common.GetTableCName(sheet.GetRow(iRow).GetCell(iCell + 2).ToString().Trim());
//表的英文名称
mTable.Code = sheet.GetRow(iRow + 1).GetCell(iCell + 2).ToString().Trim();
//2014-07-28 添加错误详细信息提示,以便准确知道错误地方
Common.JudgeTableInfo(mTable.Code, mTable.Name);
//表的描述
mTable.Comment = mTable.Name;
//标题列 2014-05-03杜冬军修改，动态读取列，确保列顺序
var row = sheet.GetRow(iRow+3);
//缓存列索引和名称
Dictionary<int, string> dic = new Dictionary<int, string>();
for (int i = iCell; i < row.LastCellNum; i++)
{
//标题列 2015-01-13杜冬军修改BUG
if (row.GetCell(i) == null)
{
break;
}
dic.Add(i, row.GetCell(i).ToString().Trim());
}
iRow = iRow + 4;
row = sheet.GetRow(iRow);
while (row != null)
{
if (row.GetCell(iCell) != null && !String.IsNullOrEmpty(row.GetCell(iCell).ToString()))
{
InitColumns(row, iCell, mTable,dic);
iRow = iRow + 1;
row = sheet.GetRow(iRow);
}
else
{
break;
}
}
if (string.IsNullOrEmpty(mTable.Comment))
{
mTable.Comment = mTable.Name;
}
if (mTable.ListPkKeyInfo != null && mTable.ListPkKeyInfo.Count > 0)
{
foreach (PkKeyInfo pkInfo in mTable.ListPkKeyInfo)
{
mTable.PkKeyNameList = String.Format("{0}{1},", mTable.PkKeyNameList, pkInfo.Name);
}
}
if (!string.IsNullOrEmpty(mTable.PkKeyNameList))
{
mTable.PkKeyNameList = mTable.PkKeyNameList.Substring(0, mTable.PkKeyNameList.Length - 1);
}
Tables.Add(mTable);
return iRow;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="row">行节点</param>
/// <param name="dic">列名字典集合</param>
/// <param name="pTable">表信息</param>
private void InitColumns(Row row, Dictionary<int, string> dic, TableInfo pTable)
{
ColumnInfo mColumn = new ColumnInfo();
int iCell = 0;
//列ID
mColumn.ColumnObjectId = Guid.NewGuid().ToString();
string sTemp="";
foreach (var cell in row.Cells)
{
sTemp=cell.Paragraphs[0].Text.Trim();
Common.GetColumnInfo(dic, sTemp, mColumn, iCell, pTable);
iCell++;
}
mColumn.DataTypeStr=Common.GetDataTypeStr(mColumn.DataTypeStr, mColumn.Width);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
mColumn.Sequence = pTable.ListColumnInfo.Count + 1;
pTable.ListColumnInfo.Add(mColumn);
}
/// <summary>
/// EXECL，WORD模版添加新的主键列判定方式，如列中文名称 A(主键) 带有(主键)则认为该列是主键列
/// 2014-07-23添加该方法以支持项目判断主键的方式
/// </summary>
/// <param name="mColumn">列信息</param>
public static void GetPrimaryKeyInfo(ColumnInfo mColumn, TableInfo pTable)
{
if (mColumn == null || string.IsNullOrEmpty(mColumn.Name) || mColumn.PK)
{
return;
}
string temp = ClearEmpty(mColumn.Name);
temp = temp.Replace("（", "(").Replace("）", ")").ToUpper();
if (temp.Contains("(主键)") || temp.Contains("(PK)"))
{
mColumn.Name = temp.Replace("(主键)", "").Replace("(PK)", "");
mColumn.Nullable = true;
PkKeyInfo pk = new PkKeyInfo();
pk.Name = mColumn.Code;
mColumn.PK = true;
pTable.ListPkKeyInfo.Add(pk);
}
}
/// <summary>
/// 获取EXECL或者Word的列信息
/// </summary>
/// <param name="dic">列信息字典信息</param>
/// <param name="sTemp">列的值</param>
/// <param name="mColumn">列</param>
/// <returns>列信息</returns>
public static void GetColumnInfo(Dictionary<int, string> dic, string sTemp, ColumnInfo mColumn, int iCell, TableInfo pTable)
{
switch (dic[iCell])
{
//列中文名称
case "字段中文名":
mColumn.Name = sTemp;
break;
//列英文名称
case "字段英文名":
mColumn.Code = sTemp;
break;
case "数据类型":
mColumn.DataTypeStr = sTemp.Replace("（", "(").Replace("）", ")");
mColumn.DataType = Common.GetColumnDataType(mColumn.DataTypeStr);
break;
case "宽度":
mColumn.Width = sTemp;
break;
//主键信息
case "主键":
case "约束":
if (sTemp.ToLower() == "pk" || Common.ConvertToBooleanPG(sTemp))
{
PkKeyInfo pk = new PkKeyInfo();
pk.Name = mColumn.Code;
mColumn.PK = true;
pTable.ListPkKeyInfo.Add(pk);
}
break;
//列默认值
case "默认值":
mColumn.DefaultValue = sTemp;
break;
//列描述
case "说明":
case "枚举&说明":
mColumn.Comment = sTemp;
break;
//列是否可为空
case "空值":
//是否为空值处理和其它的不相同,只会填N或0表示该列不允许为空
sTemp = sTemp.ToLower();
if ((sTemp.Equals("n") || sTemp.Equals("0")) || sTemp.Equals("false") || sTemp.Equals("否"))
{
mColumn.Nullable = true;
}
else
{
mColumn.Nullable = false;
}
break;
//列是否自增
case "自增":
mColumn.Identity = Common.ConvertToBooleanPG(sTemp);
break;
default:
break;
}
}
/// <summary>
/// 获取节点中表的信息
/// </summary>
/// <param name="table">table</param>
private TableInfo GetTable(XWPFTable table)
{
try
{
TableInfo mTable = new TableInfo();
List<ColumnInfo> list = new List<ColumnInfo>();
List<PkKeyInfo> listPkKeyInfo = new List<PkKeyInfo>();
mTable.ListColumnInfo = list;
mTable.ListPkKeyInfo = listPkKeyInfo;
//表的ID
mTable.TableObjectID = Guid.NewGuid().ToString();
//表的中文名称
mTable.Name = table.GetRow(0).GetCell(1).GetText().Trim();
//表的英文名称
mTable.Code = table.GetRow(1).GetCell(1).GetText().Trim();
//表的描述
mTable.Comment = mTable.Name;
//标题列
var row = table.GetRow(3);
//缓存列索引和名称
Dictionary<int, string> dic = new Dictionary<int, string>();
int i = 0;
foreach (var cell in row.GetTableCells())
{
dic.Add(i, cell.GetText().Trim());
i++;
}
int iRow = 4;
row = table.GetRow(iRow);
while (row != null)
{
InitColumns(row, dic, mTable);
iRow = iRow + 1;
row = table.GetRow(iRow);
}
if (string.IsNullOrEmpty(mTable.Comment))
{
mTable.Comment = mTable.Name;
}
if (mTable.ListPkKeyInfo != null && mTable.ListPkKeyInfo.Count > 0)
{
foreach (PkKeyInfo pkInfo in mTable.ListPkKeyInfo)
{
mTable.PkKeyNameList = mTable.PkKeyNameList + pkInfo.Name + ",";
}
}
if (!string.IsNullOrEmpty(mTable.PkKeyNameList))
{
mTable.PkKeyNameList = mTable.PkKeyNameList.Substring(0, mTable.PkKeyNameList.Length - 1);
}
return mTable;
}
catch (Exception ex)
{
throw ex;
}
}