/// <summary>
/// 获取表结构
/// </summary>
/// <param name="TableName"></param>
/// <param name="Version"></param>
/// <returns></returns>
public static List <Field> GetTableSchema(string TableName, string Version = "V10")
{
var dtColumns = new DataTable();
List <Field> Fields = new List <Field>();
var sqlColumns = string.Format(@"SELECT
*
FROM INFORMATION_SCHEMA.COLUMNS t
WHERE t.TABLE_NAME = '{0}'
ORDER BY t.ORDINAL_POSITION", TableName);
switch (Version)
{
case "V9":
dtColumns = H3DBHelper.GetDataTable(sqlColumns);
break;
default:
dtColumns = OThinker.H3.Controllers.AppUtility.Engine.Query.QueryTable(sqlColumns);
break;
}
foreach (DataRow ItemCol in dtColumns.Rows)
{
Field field = new Field();
field.Name = ItemCol["column_name"].ToString();
field.Type = ItemCol["data_type"].ToString();
if (ItemCol["DATA_TYPE"].ToString().ToLower() == "char" || ItemCol["DATA_TYPE"].ToString().ToLower() == "nvarchar")
{
field.Length = ItemCol["CHARACTER_MAXIMUM_LENGTH"].ToString();
}
Fields.Add(field);
}
return(Fields);
}
/// <summary>
/// 创建储存过程
/// </summary>
/// <param name="temp"></param>
public static void CreateProc(Temp temp)
{
var DropPeoc = string.Format(@"DROP PROCEDURE {0}", temp.ProcName);
H3DBHelper.ExecuteNonQuery(DropPeoc);
if (temp.ProCols.Count > 0)
{
//创建存储过程
var CreateProc = string.Format(@"CREATE PROCEDURE {0}
(
@TempTable {1} Readonly
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION
DELETE FROM {2};
INSERT INTO {2}
({3})
SELECT
{3}
FROM @TempTable
COMMIT TRANSACTION
END"
, temp.ProcName
, temp.TypeName
, temp.TableName
, string.Join(",\n", temp.ProCols.ToArray()));
H3DBHelper.ExecuteNonQuery(CreateProc);
}
}
/// <summary>
/// 创建数据类型
/// </summary>
/// <param name="temp"></param>
public static void CreateType(Temp temp)
{
var DropType = string.Format(@"DROP TYPE {0}", temp.TypeName);
H3DBHelper.ExecuteNonQuery(DropType);
var createtype = string.Format(@"CREATE TYPE {0} AS TABLE({1})"
, temp.TypeName
, string.Join(",\n", temp.TypeCols.ToArray()));
H3DBHelper.ExecuteNonQuery(createtype);
}
private void CreateSystemTable()
{
var sqltable = string.Format(@"SELECT
*
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND Table_Name LIKE 'OT_%'");
var dt = H3DBHelper.GetDataTable(sqltable);
foreach (DataRow item in dt.Rows)
{
CreateDBScript(item["TABLE_NAME"].ToString());
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="temp"></param>
public static void CreateTable(Temp temp)
{
//在10版本中创建自定义Table
var DropTable = string.Format(@"DROP Table {0}", temp.TableName);
H3DBHelper.ExecuteNonQuery(DropTable);
var CreateTable = string.Format(@"CREATE TABLE {0}
(
{1}
)"
, temp.TableName
, string.Join(",\n", temp.TypeCols.ToArray()));
H3DBHelper.ExecuteNonQuery(CreateTable);
}
public static string GetTableColumns(string TableName)
{
var ProCols = new List <string>();
var Schema10 = H3DBHelper.GetTableSchema(TableName, "V10");
var Schema9 = H3DBHelper.GetTableSchema(TableName, "V9");
foreach (var filed in Schema10)
{
var tt = Schema9.Where(a => a.Name == filed.Name);
if (Schema9.Contains(filed))
{
ProCols.Add("[" + filed.Name + "]");
}
}
return(string.Join(",\n", ProCols.ToArray()));
}
private void CreateCustomerTable()
{
var sqltable = string.Format(@"SELECT
*
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND Table_Name LIKE 'I_%'");
var dt = H3DBHelper.GetDataTable(sqltable);
foreach (DataRow item in dt.Rows)
{
var TableName = item["TABLE_NAME"].ToString();
var temp = DataBusiness.GetTableCols(TableName);
DataBusiness.CreateTable(temp);
DataBusiness.CreateType(temp);
DataBusiness.CreateProc(temp);
}
}
public static void SyncTable(string[] TableName)
{
try
{
var ProcName = "Proc_" + TableName[0];
var SqlTable = GetTableSql(TableName);
var dt = H3DBHelper.GetDataTable(SqlTable);
var Parameters = new List <SqlParameter>();
Parameters.Add(new SqlParameter()
{
ParameterName = "@TempTable", Value = dt
});
var Result = H3DBHelper.ExecuteProcNonQuery(ProcName, Parameters);
LogHelper.Info("导入成功:" + TableName);
}
catch (Exception ex)
{
LogHelper.Debug("导入报错:" + TableName);
LogHelper.Error("导入报错:" + ex.Message);
}
}
/// <summary>
/// 角色同步
/// </summary>
private void SyncRole()
{
//角色
//说明V10版本中的角色相当于V9版本中职务、岗位、编制的集合。所以在同步时，要进行转化
//岗位名称->角色名称
//岗位编码->角色编码
//岗位成员->角色用户
//岗位所在部门->角色管理范围
var sqlorgjob = string.Format(@"SELECT
[ObjectID]
,[Code]
,[SuperiorCode]
,[DisplayName]
,[Description]
,[ParentObjectID]
,[ParentPropertyName]
,[ParentIndex]
,[Level]
FROM [OT_OrgJob]
ORDER BY Code");
var dtjob = H3DBHelper.GetDataTable(sqlorgjob);
foreach (DataRow item in dtjob.Rows)
{
var orgpost = new OThinker.Organization.OrgPost
{
ObjectID = item["ObjectID"].ToString(),
Code = item["Code"].ToString(),
Name = item["DisplayName"].ToString(),
JobLevel = item["Level"].ToString() == "" ? 0 : int.Parse(item["Level"].ToString())
};
var staff = string.Format(@"SELECT
t3.ChildID
,t2.ParentID
,t1.Code
FROM OT_OrgJob t1
,OT_OrgPost t2
,OT_GroupChild t3
WHERE t1.Code = t2.JobCode
AND t2.ObjectID = t3.ParentObjectID
AND t1.Code='{0}'
ORDER BY t1.Code, t2.Code, t2.ParentID", orgpost.Code);
var dtorgstaff = H3DBHelper.GetDataTable(staff);
var list = new List <OThinker.Organization.OrgStaff>();
foreach (DataRow item2 in dtorgstaff.Rows)
{
var orgstaff = new OThinker.Organization.OrgStaff
{
OUScope = new string[] { item2["ParentID"].ToString() },
UserID = item2["ChildID"].ToString(),
ParentObjectID = orgpost.ObjectID
};
list.Add(orgstaff);
}
orgpost.ChildList = list.ToArray();
OThinker.H3.Controllers.AppUtility.Engine.Organization.AddUnit("", orgpost);
}
//编制名称->角色名称
//编制编码->角色编码
//编制成员->角色用户
//编制管理部门->角色管理范围
//组
var sqlgroup = string.Format(@"SELECT
ObjectID
,Name
,Code
,ParentID
FROM [OT_Group]");
var dtgroup = H3DBHelper.GetDataTable(sqlgroup);
foreach (DataRow item in dtgroup.Rows)
{
var group = new OThinker.Organization.Group()
{
ObjectID = item["ObjectID"].ToString(),
Name = item["Name"].ToString(),
ParentID = item["ParentID"].ToString()
};
var groupchild = string.Format(@"SELECT
*
FROM OT_GroupChild
WHERE ParentObjectID = '{0}'", item["ObjectID"].ToString());
var dtchild = H3DBHelper.GetDataTable(groupchild);
foreach (DataRow item2 in dtchild.Rows)
{
var list = new List <OThinker.Organization.GroupChild>();
var staff = new OThinker.Organization.GroupChild
{
ObjectID = item2["ObjectID"].ToString(),
ChildID = item2["ChildID"].ToString(),
ParentObjectID = item2["ParentObjectID"].ToString()
};
list.Add(staff);
group.ChildList = list.ToArray();
}
OThinker.H3.Controllers.AppUtility.Engine.Organization.AddUnit("", group);
}
}
