/// <summary>
/// 根据(主表同步副表)字段
/// </summary>
/// <param name="Params">参数</param>
/// <param name="tablesCode">表编码</param>
/// <param name="columnDt">主表</param>
/// <param name="columnlist">副表</param>
private void SynchronousSide(NameValueCollection Params, Guid tablesCode, DataTable columnDt, IList <Column> columnlist)
{
for (int k = 0; k < columnDt.Rows.Count; k++)
{
int columnNumber = 0;//记录主表与副表的交集数
for (int q = 0; q < columnlist.Count; q++)
{
if (columnDt.Rows[k]["columnsName"].ToString() == columnlist[q].Name)
{
columnNumber++;
}
}
//主表存在新的列字段
if (columnNumber == 0)
{
Column column = new Column();
column.Author = User.Identity.Name;
column.TablesCode = tablesCode;
column.Owner = columnDt.Rows[k]["owner"].ToString();
column.Name = columnDt.Rows[k]["columnsName"].ToString();
column.Type = columnDt.Rows[k]["columnType"].ToString();
column.Bewrite = columnDt.Rows[k]["remark"].ToString();
column.FieldState = 1;
column.HideAuthor = string.Empty;
column.HideTime = DateTime.Now;
column.Code = Guid.NewGuid();
DColumn.Add(column);
}
}
}
//保存列字段信息
public void SaveColumn()
{
NameValueCollection Params = HttpContext.Request.Form;//参数
Column column = new Column();
column.Author = User.Identity.Name;
column.TablesCode = new Guid(Params["tablesCode"]);
column.Owner = Params["owner"];
column.Name = Params["name"];
column.Type = Params["type"];
column.Bewrite = Params["bewrite"];
column.FieldState = int.Parse(Params["fieldState"]);
column.HideAuthor = Params["hideAuthor"];
column.HideTime = DateTime.Now;//默认为空
string result = string.Empty;
if (Params["code"] != null)
{
column.Code = new Guid(Params["code"]);
result = DColumn.Update(column) != null ? "{HasError:false,msg:'列字段编辑成功！'}" : "{HasError:true,msg:'列字段编辑失败，请稍候再试！'}";
}
else
{
column.Code = Guid.NewGuid();
result = DColumn.Add(column) != null ? "{HasError:false,msg:'列字段创建成功！'}" : "{HasError:true,msg:'列字段创建失败，请稍候再试！'}";
}
Response.Write(result);
Response.End();
}
//根据表编码，获取列名字段信息(用于子页展示字段信息)
public void PreviewColumn()
{
NameValueCollection Params = HttpContext.Request.Form;//参数
Guid tableCode = new Guid(Params["tableCode"]);
IList <Column> columnlist = DColumn.GetColumn(tableCode);
//根据表编码获取表信息
Tables tables = DTables.GetProjectById(tableCode);
StringBuilder Json = new StringBuilder();
Json.Append("{ Name: '" + tables.Name + "',"); //表名
Json.Append("Alias:'" + tables.Alias + "',"); //别名
Json.Append("copyright:'DBVortaro',");
Json.Append("list: [");
for (int i = 0; i < columnlist.Count; i++)
{
if (i > 0)
{
Json.Append(",");
}
Json.Append("{ Name: '" + columnlist[i].Name + "',");
Json.Append("Type: '" + columnlist[i].Type + "',");
Json.Append("Bewrite: '" + columnlist[i].Bewrite + "',");
Json.Append("Author: '" + columnlist[i].Author + "',");
Json.Append("CreateTime: '" + columnlist[i].CreateTime.ToString("yyyy年MM月dd日 HH:mm:ss") + "'");
Json.Append("}");
}
Json.Append("]}");
Response.Write(Json.ToString().Replace("\'", "\""));
Response.End();
}
/// <summary>
/// 批量添加列字段信息
/// </summary>
/// <param name="resultT">执行结果</param>
/// <param name="table">表信息</param>
/// <param name="dt">列字段信息</param>
/// <returns></returns>
private bool BatchAddColumn(bool resultT, Tables table, DataTable dt)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
//列名是否重复，重复则不执行保存操作
string columnName = dt.Rows[j]["columnsName"].ToString();
if (!DColumn.RepeatColumnName(columnName, table.Code, User.Identity.Name))
{
Column column = new Column();
column.Author = User.Identity.Name;
column.TablesCode = table.Code;
column.Owner = dt.Rows[j]["owner"].ToString();
column.Name = columnName;
column.Type = dt.Rows[j]["columnType"].ToString();
column.Bewrite = dt.Rows[j]["remark"].ToString();
column.FieldState = 1;
column.HideAuthor = "";
column.HideTime = DateTime.Now; //默认为空
column.Code = Guid.NewGuid();
DColumn.Add(column); //添加列
resultT = true;
}
}
return(resultT);
}
//批量保存列字段说明
public void SaveColumnRemark()
{
NameValueCollection Params = HttpContext.Request.Params;//参数
List <Column> Columnlist = JsonConvert.DeserializeObject <List <Column> >(Params["changRecord"]);
Response.Write(DColumn.SaveColumnRemark(Columnlist) > 0 ? "{HasError:false,msg:'列字段说明保存成功！'}" : "{HasError:true,msg:'列字段说明保存失败！'}");
Response.End();
}
//粘贴列字段说明信息
public void PasteColumnBewrite()
{
NameValueCollection Params = HttpContext.Request.Form; //参数
string CopyTablesCode = Params["copyTablesCode"]; //复制表编码
string PasteTablesCode = Params["pasteTablesCode"]; //粘贴表编码
Response.Write(DColumn.PasteColumnBewrite(CopyTablesCode, PasteTablesCode) > 0 ? "{HasError:false,msg:'粘贴列字段说明成功！'}" : "{HasError:true,msg:'粘贴列字段说明失败！'}");
Response.End();
}
/// <summary>
/// 根据(副表同步主表)说明
/// </summary>
/// <param name="Params">参数</param>
/// <param name="tablesCode">表编码</param>
/// <param name="TablesName">表名</param>
/// <param name="SqlConnection">链接字符串</param>
/// <param name="columnDt">主表</param>
/// <param name="columnlist">附表</param>
private void SynchronousChief(NameValueCollection Params, Guid tablesCode, string TablesName, string SqlConnection, DataTable columnDt, IList <Column> columnlist)
{
string SynchronousSQL = string.Empty;
for (int i = 0; i < columnlist.Count; i++)
{
int columnNumber = 0;//记录主表与副表的交集数
for (int j = 0; j < columnDt.Rows.Count; j++)
{
//同步主表说明的SQL
if (columnDt.Rows[j]["columnsName"].ToString() == columnlist[i].Name && columnlist[i].Bewrite != "" &&
(columnDt.Rows[j]["remark"].ToString() == "" || columnDt.Rows[j]["remark"].ToString() != columnlist[i].Bewrite))
{
SynchronousSQL += string.Format("execute {4} N'MS_Description',N'{3}',N'SCHEMA',N'{0}',N'table',N'{1}',N'column',N'{2}';",
columnlist[i].Owner, TablesName, columnlist[i].Name, columnlist[i].Bewrite,
columnDt.Rows[j]["remark"].ToString() == "" ? "sp_addextendedproperty" : "sp_updateextendedproperty");
}
//同步副表说明
if (columnDt.Rows[j]["columnsName"].ToString() == columnlist[i].Name && columnlist[i].Bewrite == "" &&
columnDt.Rows[j]["remark"].ToString() != "")
{
Column column = new Column();
column.Author = User.Identity.Name;
column.TablesCode = tablesCode;
column.Owner = columnlist[i].Owner;
column.Name = columnlist[i].Name;
column.Type = columnlist[i].Type;
column.Bewrite = columnDt.Rows[j]["remark"].ToString();
column.FieldState = 1;
column.HideAuthor = string.Empty;
column.HideTime = DateTime.Now;
column.Code = columnlist[i].Code;
DColumn.Update(column);
}
if (columnDt.Rows[j]["columnsName"].ToString() == columnlist[i].Name)
{
columnNumber++;
}
}
//附表存在作废字段
if (columnNumber == 0)
{
Column column = columnlist[i];
column.FieldState = 0;
column.HideAuthor = User.Identity.Name;
column.HideTime = DateTime.Now;
DColumn.Update(column);
}
}
if (!string.IsNullOrEmpty(SynchronousSQL))
{
SQLHelper.ExecuteSql(SqlConnection, SynchronousSQL);
}
}
//导入指定表到数据库
public void ImportTables()
{
NameValueCollection Params = HttpContext.Request.Form;//参数
//批量添加表信息
string[] tables = Params["tables"].Split(','); //表名称数据集
string ServerName = Params["ServerName"]; //服务器名称
string ServerUser = Params["ServerUser"]; //用户名
string ServerPwd = Params["ServerPwd"]; //密码
ServerPwd = NHibernateHelper.DecryptAES(ServerPwd, "zhangzhangdebing"); //解密
string DatabaseName = Params["DatabaseName"]; //指定数据库名称
string databaseCode = Params["databaseCode"]; //数据库信息编码
string groupCode = Params["groupCode"]; //功能分组编码
bool resultT = false;
try
{
for (int i = 0; i < tables.Length; i++)
{
resultT = false;
string tableName = tables[i];
//表名是否，重复则不执行保存操作
if (!DTables.RepeatTablesName(tableName, databaseCode, groupCode, User.Identity.Name))
{
Tables table = new Tables();
table.Author = User.Identity.Name;
table.DatabaseCode = new Guid(databaseCode);
table.GroupCode = new Guid(groupCode);
table.Name = tableName;
table.Alias = tableName;
table.Code = Guid.NewGuid();
//根据表名，获取表字段信息
string SqlConnection = string.Format("server={0};database={1};uid={2};pwd={3};", ServerName, DatabaseName, ServerUser, ServerPwd);
DataTable dt = DColumn.GetTableColumn(tableName, SqlConnection);
if (dt.Rows.Count > 0)
{
DTables.Add(table);//添加表
resultT = BatchAddColumn(resultT, table, dt);
}
}
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("导入指定表发生异常", ex);
return;
}
Response.Write(resultT ? "{HasError:false,msg:'指定表导入成功！'}" : "{HasError:true,msg:'指定表导入失败，请稍候再试！'}");
Response.End();
}
//获取列字段信息
public void GetPageColumn()
{
NameValueCollection Params = HttpContext.Request.Form;//参数
string page = Params["page"];
string rows = Params["rows"];
int intPage = int.Parse((page == null || page == "0") ? "1" : page);
//每页显示条数
int pageSize = int.Parse((rows == null || rows == "0") ? "10" : rows);
//每页的开始记录 第一页为1 第二页为number +1
int start = (intPage - 1) * pageSize;
string tablesCode = Params["tablesCode"];
Response.Write(DColumn.GetPageColumn(start, pageSize, "", tablesCode));
Response.End();
}
/// <summary>
/// 生成HTML子页
/// </summary>
/// <param name="tableCode">表编码</param>
/// <param name="tableName">表名称</param>
/// <param name="tableAlias">表别名</param>
/// <param name="directory">输出路径</param>
/// <param name="itemsPath">模板路径</param>
private static void BuildItems(Guid tableCode, string tableName, string tableAlias, string directory, string itemsPath)
{
IList <Column> columnlist = DColumn.GetColumn(tableCode);
string tableStr = @"<table style=""width: 100%;"">
<tr class=""text-center""><th style=""width:60px"">序号</th><th>字段</th><th>类型</th><th>说明</th><th>作者</th><th>时间</th></tr>";
for (int k = 0; k < columnlist.Count; k++)
{
tableStr += string.Format(@"<tr><td style=""padding-left:10px;"">{0}</td>
<td>{1}</td><td>{2}</td><td>{3}</td><td class=""text-center"">{4}</td><td class=""text-center"">{5}</td></tr>",
k + 1, columnlist[k].Name, columnlist[k].Type, columnlist[k].Bewrite, columnlist[k].Author, columnlist[k].CreateTime.ToString("yyyy年MM月dd日 HH:mm:ss"));
}
tableStr += "</table>";
string[] itemsTemp = new string[] { "@title", "@name", "@alias", "@table", "@footer" };
string[] itemsHtml = new string[] { tableAlias, tableName, tableAlias, tableStr, "DBVortaro" };
DTemplates.WriteHtml(itemsTemp, itemsHtml, itemsPath, directory);
}
//同步列字段及说明信息
public void SynchronousColumnBewrite()
{
NameValueCollection Params = HttpContext.Request.Form; //参数
string ServerName = Params["ServerName"]; //服务器名称
string ServerUser = Params["ServerUser"]; //用户名
string ServerPwd = Params["ServerPwd"]; //密码
ServerPwd = NHibernateHelper.DecryptAES(ServerPwd, "zhangzhangdebing"); //解密
string DatabaseName = Params["DatabaseName"]; //指定数据库名称
string[] TablesCode = Params["tablesCodes"].Split(','); //表编码集合
string[] TablesName = Params["tablesNames"].Split(','); //表名称集合
try
{
string SqlConnection = string.Format("server={0};database={1};uid={2};pwd={3};", ServerName, DatabaseName, ServerUser, ServerPwd);
string exMsg = string.Empty; //异常记录
for (int i = 0; i < TablesCode.Length; i++) //循环表编码集合
{
//根据表编码，获取列字段信息（主表）
DataTable columnDt = DColumn.GetTableColumn(TablesName[i], SqlConnection);
if (columnDt.Rows.Count == 0)
{
exMsg += "主表[" + TablesName[i] + "],列字段信息为空。<br/>";
continue;
}
//根据表编码，获取列字段信息（副表）
IList <Column> columnlist = DColumn.GetColumn(new Guid(TablesCode[i]));
if (columnlist.Count == 0)
{
exMsg += "副表[" + TablesName[i] + "],列字段信息为空。<br/>";
continue;
}
//获取(副表同步主表)说明的SQL语句
SynchronousChief(Params, new Guid(TablesCode[i]), TablesName[i], SqlConnection, columnDt, columnlist);
//根据(主表同步副表)字段
SynchronousSide(Params, new Guid(TablesCode[i]), columnDt, columnlist);
}
Response.Write("{HasError:false,msg:'同步成功！<br/>" + exMsg + "'}");
}
catch (Exception ex)
{
Response.Write("{HasError:true,msg:'同步失败！<br/>异常：" + ex.Message.Replace("\r\n", "<br/>") + "'}");
}
Response.End();
}
//删除列字段信息
public void DeleteColumn()
{
NameValueCollection Params = HttpContext.Request.Form;//参数
string[] codes = Params["code"].ToString().Split(',');
bool T = false;
if (codes.Length > 0)
{
foreach (string code in codes)
{
T = DColumn.Delete(new Guid(code)) != null ? true : false;
}
}
string result = T ? "{HasError:false,msg:'列字段删除成功！'}" : "{HasError:true,msg:'列字段删除失败，请稍候再试！'}";
Response.Write(result);
Response.End();
}
/// <summary>
/// InitalizeTimeTableContext
/// </summary>
private void InitalizeTimeTableContext()
{
TimeTableContext = new DContext();
// Create List Columns
TimeTableContext.Columns = new ObservableCollection <DColumn>
{
};
// Add Label Column
DColumn labelColum = new DColumn {
Width = 20
};
TimeTableContext.Columns.Add(labelColum);
// Add 7x12 column for content
for (var i = 0; i < 84; i++)
{
var contentColumn = new DColumn()
{
Width = 30
};
TimeTableContext.Columns.Add(contentColumn);
}
TimeTableContext.Rows = new ObservableCollection <DRow>()
{
};
// Add row for Day
var dayRow = new DRow()
{
Height = 20
};
TimeTableContext.Rows.Add(dayRow);
// Add row for index
var indexRow = new DRow()
{
Height = 15
};
TimeTableContext.Rows.Add(indexRow);
// Add 50 content Row
for (var i = 0; i < 50; i++)
{
var contentRow = new DRow()
{
Height = 30
};
TimeTableContext.Rows.Add(contentRow);
}
// Add Monday
var mondayCell = new DCell(0, 1, 1, 12);
mondayCell.BackgroundContent = "Monday";
mondayCell.UIContent = new lessonView();
TimeTableContext.AddCell(mondayCell);
AddLessonIndex(1, 12);
// Add Tuesday
var tuesdayCell = new DCell(0, 13, 1, 12);
tuesdayCell.BackgroundContent = "Tuesday";
TimeTableContext.AddCell(tuesdayCell);
AddLessonIndex(13, 12);
// Add Wednesday
var wednesDayCell = new DCell(0, 25, 1, 12);
wednesDayCell.BackgroundContent = "Wesnesday";
TimeTableContext.AddCell(wednesDayCell);
AddLessonIndex(25, 12);
// Add Thursday
var thursdayCell = new DCell(0, 37, 1, 12);
thursdayCell.BackgroundContent = "Thursday";
TimeTableContext.AddCell(thursdayCell);
AddLessonIndex(37, 12);
// Add Friday
var fridayCell = new DCell(0, 49, 1, 12);
fridayCell.BackgroundContent = "Friday";
TimeTableContext.AddCell(fridayCell);
AddLessonIndex(49, 12);
// Add Saturday
var saturdayCell = new DCell(0, 61, 1, 12);
saturdayCell.BackgroundContent = "Saturday";
TimeTableContext.AddCell(saturdayCell);
AddLessonIndex(61, 12);
// Add Sunday
var sundayCell = new DCell(0, 73, 1, 12);
sundayCell.BackgroundContent = "Sunday";
TimeTableContext.AddCell(sundayCell);
AddLessonIndex(73, 12);
//var dumyCell = new DCell(1, 1, 1, 1);
//TimeTableContext.AddCell(dumyCell);
// InsertBackgroudForAllTimeItems
InsertBackgroudForAllTimeItems();
}
