public List <FieldDefineInfo> GetFieldDefineInfos(TableDefineInfo TableInfo)
{
List <FieldDefineInfo> FieldInfos = new List <FieldDefineInfo>();
if (TableInfo != null)
{
StringBuilder Sql_Select = new StringBuilder();
Sql_Select.Append("Select * from sys_Columns where TableName ='");
Sql_Select.Append(TableInfo.Name);
Sql_Select.Append("' and (Scdel IS NULL or Scdel=0) order by scts");//增加查询条件 (Scdel IS NULL or Scdel=0) 2013-10-15
DataTable Data = GetDataTable(Sql_Select.ToString());
foreach (DataRow row in Data.Rows)
{
FieldDefineInfo FieldInfo = new FieldDefineInfo(TableInfo);
FieldInfo.Index = row["ID"].ToString();
FieldInfo.Description = row["DESCRIPTION"].ToString();
FieldInfo.FieldName = row["COLNAME"].ToString();
FieldInfo.IsKeyField = Convert.ToBoolean(row["IsKeyField"]);
String scpt = row["scpt"].ToString();
FieldInfo.IsNotNull = ((scpt.Length > 0 ? scpt.Substring(0, 1) : "0") == "1"? true:false);
FieldInfo.IsNotCopy = ((scpt.Length > 1 ? scpt.Substring(1, 1) : "0") == "1" ? true : false);
FieldInfo.IsPingxing = ((scpt.Length > 2 ? scpt.Substring(2, 1) : "0") == "1" ? true : false);
FieldInfo.IsReadOnly = ((scpt.Length > 3 ? scpt.Substring(3, 1) : "0") == "1" ? true : false);
String ColType = row["COLType"].ToString();
FieldInfo.FieldType = FieldType.GetFieldType(ColType);
FieldInfos.Add(FieldInfo);
}
}
return(FieldInfos);
}
private void AppendTableByTableName(string TableName, bool IsReferencedTable)
{
if (!HaveTableDiagram(TableName))
{
TableDefineInfo TableDefineInfo = DepositoryTableDefineInfo.GetTableDefineInfo(TableName);
int TableHeight = tableFlow1.FieldHeight;
foreach (FieldDefineInfo fieldInfo in TableDefineInfo.FieldInfos)
{
if (!ColumnRegular.JudgeColumnName(fieldInfo.Description))
{
TableHeight = TableHeight + tableFlow1.FieldHeight;
}
}
Node tableNode = tableFlow1.AddTable(TableDefineInfo.Description, TableLeft, TableTop, TableWidth, TableHeight);
tableNode.Properties["Name"].Value = TableDefineInfo.Name;
tableNode.Properties["Text"].Value = TableDefineInfo.Description;
foreach (FieldDefineInfo fieldInfo in TableDefineInfo.FieldInfos)
{
if (!ColumnRegular.JudgeColumnName(fieldInfo.Description))
{
Node FieldNode = tableFlow1.AddField(tableNode, fieldInfo.Description + "(" + fieldInfo.FieldType.DisplayType + ")", !IsReferencedTable, IsReferencedTable, Color.Transparent);
FieldNode.Properties["Name"].Value = fieldInfo.FieldName;
FieldNode.Properties["Text"].Value = fieldInfo.Description;
FieldNode.Properties["DispType"].Value = fieldInfo.FieldType.DisplayType;
}
}
}
}
public Boolean DeleteDataAreaInfo(string SheetID, TableDefineInfo TableInfo)
{
if (string.IsNullOrEmpty(SheetID) || TableInfo == null)
{
return(false);
}
Boolean Result = false;
//增加字段Scts_1,Scdel 之后 删除操作只做伪删除，便于数据同步 2013-10-15
StringBuilder Sql_Delete = new StringBuilder();
//Sql_Delete.Append("Delete From sys_biz_DataArea Where SheetID = '");
//Sql_Delete.Append(SheetID);
//Sql_Delete.Append("' and TableName='");
//Sql_Delete.Append(TableInfo.Name);
//Sql_Delete.Append("'");
Sql_Delete.Append("Update sys_biz_DataArea Set Scts_1=Getdate(),Scdel=1");
Sql_Delete.Append(" Where SheetID='");
Sql_Delete.Append(SheetID);
Sql_Delete.Append("' and TableName='");
Sql_Delete.Append(TableInfo.Name);
Sql_Delete.Append("'");
object r = ExcuteCommand(Sql_Delete.ToString());
Result = (Convert.ToInt32(r) == 1);
return(Result);
}
public Hashtable InitModelTableInfo(String Index)
{
Hashtable Results = new Hashtable();
List <IndexDescriptionPair> Pairs = new List <IndexDescriptionPair>();
StringBuilder Sql_Select = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
Sql_Select.Append("Select Sheets from sys_biz_Module where Scdel=0 and ID ='");
Sql_Select.Append(Index);
Sql_Select.Append("'");
DataTable Data = GetDataTable(Sql_Select.ToString());
if (Data != null && Data.Rows.Count > 0)
{
DataRow Row = Data.Rows[0];
String[] Indexes = Row["Sheets"].ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
Pairs = SheetManager.InitSheetInfo(Indexes);
}
foreach (IndexDescriptionPair pair in Pairs)
{
TableDefineInfo info = TableDefineInfoManager.GetTableDefineInfo(pair.Index, pair.DataTable);
Results.Add(pair.Index, info);
}
return(Results);
}
public void DeleteWrongStadiumData()
{
ModuleManager Manager = new ModuleManager();
TableDefineInfoManager TableManager = new TableDefineInfoManager();
ModelDataManager DataManager = new ModelDataManager();
String modelIndex = "05d0d71b-def3-42ee-a16a-79b34de97e9b";
DataTable dataTb = GetDataTable("select * from [biz_norm_extent_" + modelIndex + "]");
if (dataTb != null)
{
foreach (DataRow dataRow in dataTb.Rows)
{
try
{
DataTable dt = GetDataTable("SELECT * FROM sys_biz_reminder_stadiumData WHERE DataID = '" +
dataRow["ID"].ToString() + "' AND F_IsDone=0");
if (dt == null || dt.Rows.Count == 0)
{
continue;
}
List <IndexDescriptionPair> Sheets = Manager.InitModuleInfo(modelIndex);
List <TableDefineInfo> TableSchemas = new List <TableDefineInfo>();
foreach (IndexDescriptionPair pair in Sheets)
{
TableDefineInfo Info = TableManager.GetTableDefineInfo(pair.Index, pair.DataTable);
TableSchemas.Add(Info);
}
String extentTable = "biz_norm_extent_" + modelIndex;
TableDefineInfo ExtentTableSchema = TableManager.GetTableDefineInfo("1", extentTable);
DataSet ds = DataManager.GetData(TableSchemas, ExtentTableSchema, dataRow["ID"].ToString(), dataRow["scpt"].ToString());
Object key = ds.Tables["[biz_norm_混凝土检查试件抗压强度试验报告]"].Rows[0]["col_norm_I33"];
String deleteSql = "Update dbo.sys_biz_reminder_stadiumData set F_IsDone=1 WHERE F_IsDone IS NULL AND DataID='{0}' AND DateSpan={1}";
if (key != DBNull.Value)
{
if (key.ToString().Contains("28"))
{
ExcuteCommand(String.Format(deleteSql, dataRow["ID"].ToString(), 56));
}
else if (key.ToString().Contains("56"))
{
ExcuteCommand(String.Format(deleteSql, dataRow["ID"].ToString(), 28));
}
}
}
catch (Exception ex)
{
logger.Error("Delete Old Data Error: " + ex.Message + "; ModelIndex=" + modelIndex + "; DataID=" + dataRow["ID"].ToString());
}
}
}
}
//获得模板的全部数据
public DataSet GetData(ModuleConfiguration Module, String DataCode)
{
List <TableDefineInfo> TableSchemas = new List <TableDefineInfo>();
foreach (SheetConfiguration Sheet in Module.Sheets)
{
TableSchemas.Add(Sheet.DataTableSchema.Schema);
}
TableDefineInfo ExtentDataSchema = Module.ExtentDataSchema;
return(Agent.CallService("Yqun.BO.BusinessManager.dll", "GetData", new object[] { TableSchemas, ExtentDataSchema, DataCode }) as DataSet);
}
//删除与模板相关的数据
public Boolean DeleteData(ModuleConfiguration Module, String ModelCode)
{
List <TableDefineInfo> TableSchemas = new List <TableDefineInfo>();
foreach (SheetConfiguration Sheet in Module.Sheets)
{
TableSchemas.Add(Sheet.DataTableSchema.Schema);
}
TableDefineInfo ExtentDataSchema = Module.ExtentDataSchema;
return(Convert.ToBoolean(Agent.CallService("Yqun.BO.BusinessManager.dll", "DeleteData", new object[] { TableSchemas, ExtentDataSchema, ModelCode })));
}
public void MoveOldStadiumData()
{
ModuleManager Manager = new ModuleManager();
TableDefineInfoManager TableManager = new TableDefineInfoManager();
ModelDataManager DataManager = new ModelDataManager();
DataTable tb = GetDataTable("SELECT DISTINCT a.ID FROM dbo.sys_biz_reminder_stadiumInfo a join sys_biz_Module b on a.id=b.id WHERE a.IsActive=1");
if (tb != null)
{
foreach (DataRow row in tb.Rows)
{
String modelIndex = row["ID"].ToString();
DataTable dataTb = GetDataTable("select * from [biz_norm_extent_" + modelIndex + "]");
if (dataTb != null)
{
foreach (DataRow dataRow in dataTb.Rows)
{
try
{
List <IndexDescriptionPair> Sheets = Manager.InitModuleInfo(modelIndex);
List <TableDefineInfo> TableSchemas = new List <TableDefineInfo>();
foreach (IndexDescriptionPair pair in Sheets)
{
TableDefineInfo Info = TableManager.GetTableDefineInfo(pair.Index, pair.DataTable);
TableSchemas.Add(Info);
}
String extentTable = "biz_norm_extent_" + modelIndex;
TableDefineInfo ExtentTableSchema = TableManager.GetTableDefineInfo("1", extentTable);
DataSet ds = DataManager.GetData(TableSchemas, ExtentTableSchema, dataRow["ID"].ToString(), dataRow["scpt"].ToString());
InitStadium(modelIndex, ds);
}
catch (Exception ex)
{
logger.Error("Move Old Data Error: " + ex.Message + "; ModelIndex=" + modelIndex + "; DataID=" + dataRow["ID"].ToString());
}
}
}
}
}
}
public Boolean NewDataAreaInfo(string SheetID, TableDefineInfo TableInfo)
{
if (string.IsNullOrEmpty(SheetID) || TableInfo == null)
{
return(false);
}
Boolean Result = false;
StringBuilder Sql_Select = new StringBuilder();
Sql_Select.Append("Select ID,SCTS,SheetID,TableName,ColumnName,Range,Scts_1,Scdel From sys_biz_DataArea Where SheetID='");
Sql_Select.Append(SheetID);
Sql_Select.Append("' and TableName='");
Sql_Select.Append(TableInfo.Name);
Sql_Select.Append("'");
//增加查询条件 判断此条记录是否标记为已删除 Scdel=1 代表已删除。 2013-10-15
Sql_Select.Append(" And (Scdel IS NULL or Scdel=0) ");
DataTable Data = GetDataTable(Sql_Select.ToString());
if (Data != null)
{
foreach (FieldDefineInfo FieldInfo in TableInfo.FieldInfos)
{
DataRow Row = Data.NewRow();
Row["ID"] = Guid.NewGuid().ToString();
Row["SCTS"] = DateTime.Now.ToString();
Row["SheetID"] = SheetID;
Row["TableName"] = TableInfo.Name;
Row["ColumnName"] = FieldInfo.FieldName;
Row["Range"] = FieldInfo.RangeInfo;
Row["Scts_1"] = DateTime.Now.ToString();
Row["Scdel"] = 0;
Data.Rows.Add(Row);
}
object r = Update(Data);
Result = (Convert.ToInt32(r) == 1);
}
return(Result);
}
public List <TableDefineInfo> GetTableDefineInfos()
{
List <TableDefineInfo> Infos = new List <TableDefineInfo>();
StringBuilder sql_Select = new StringBuilder();
// 增加查询条件 (Scdel IS NULL or Scdel=0) 2013-10-16
sql_Select.Append("select * from sys_Tables Where (Scdel IS NULL or Scdel=0) ");
DataTable Data = GetDataTable(sql_Select.ToString());
foreach (DataRow row in Data.Rows)
{
TableDefineInfo Info = new TableDefineInfo();
Info.Name = row["TableName"].ToString();;
Info.Description = row["DESCRIPTION"].ToString();
Info.Index = row["ID"].ToString();
Infos.Add(Info);
}
return(Infos);
}
private void ReferenceFilterDialog_Load(object sender, EventArgs e)
{
SheetfilterControl.InitializeControls();
ComboBox ComboBoxColumns = SheetfilterControl.ComboBoxColumns;
TableDefineInfo TableInfo = DepositoryTableDefineInfo.GetTableDefineInfo(TableName);
if (TableInfo != null)
{
foreach (FieldDefineInfo Field in TableInfo.FieldInfos)
{
if (!ColumnRegular.JudgeColumnName(Field.Description))
{
ComboBoxColumns.Items.Add(Field);
}
}
}
TreeView filterView = SheetfilterControl.tViewFilters;
filterView.Nodes.Clear();
foreach (DataFilterItem Item in dataFilterCondition.Items)
{
TreeNode Node = new TreeNode();
Node.Text = string.Format("{0} {1}", Item.ConditionalOperator, Item.ToString());
Node.Tag = Item;
filterView.Nodes.Add(Node);
if (Node.Index == 0)
{
Node.Text = Item.ToString();
}
}
SheetfilterControl.Button_Add.Click += new EventHandler(Button_Add_Click);
SheetfilterControl.Button_Delete.Click += new EventHandler(Button_Delete_Click);
}
public List <String> GetErrorFieldList(String SheetIndex, String DataTableName)
{
List <String> ErrorList = new List <String>();
StringBuilder sql_select = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
sql_select.Append("select sheetstyle from sys_biz_sheet where Scdel=0 and ID = '");
sql_select.Append(SheetIndex);
sql_select.Append("'");
DataTable SheetData = GetDataTable(sql_select.ToString());
if (SheetData == null || SheetData.Rows.Count == 0)
{
logger.Error(string.Format("没有找到表单的样式xml。", SheetIndex));
return(ErrorList);
}
XmlDocument doc = new XmlDocument();
doc.LoadXml(SheetData.Rows[0]["sheetstyle"].ToString());
sql_select = new StringBuilder();
sql_select.Append("SELECT sysobjects.Name as tb_name, syscolumns.Name as col_name, SysTypes.Name as col_type, syscolumns.Length as col_len, isnull(sys.extended_properties.Value,syscolumns.Name) as col_memo,");
sql_select.Append(" case when syscolumns.name in (select primarykey=a.name FROM syscolumns a inner join sysobjects b on a.id=b.id and b.xtype='U' and b.name<>'dtproperties' where exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) and b.name=sysobjects.Name)");
sql_select.Append(" then 1 else 0 end as is_key ");
sql_select.Append("FROM sysobjects,SysTypes,syscolumns LEFT JOIN sys.extended_properties ON (Syscolumns.Id = sys.extended_properties.major_id AND Syscolumns.Colid = sys.extended_properties.minor_id) ");
sql_select.Append("WHERE (sysobjects.Xtype ='u' OR Sysobjects.Xtype ='v') AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType AND SysTypes.Name <> 'sysname' AND ");
sql_select.Append(" Sysobjects.Name = '");
sql_select.Append(DataTableName);
sql_select.Append("' ");
sql_select.Append("ORDER By SysObjects.Name, SysColumns.colid");
DataTable DataSchema = GetDataTable(sql_select.ToString());
if (DataSchema != null && DataSchema.Rows.Count > 0)
{
sql_select = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
sql_select.Append("select * from sys_columns where Scdel=0 and tablename='");
sql_select.Append(DataTableName);
sql_select.Append("'");
TableDefineInfo TableDefineInfo = TableDefineInfoManager.GetTableDefineInfo(SheetIndex, DataTableName);
if (TableDefineInfo != null)
{
foreach (FieldDefineInfo Field in TableDefineInfo.FieldInfos)
{
String FieldName = Field.FieldName;
FieldType FieldType = Field.FieldType;
if (string.IsNullOrEmpty(Field.RangeInfo))
{
string error = string.Format("表单中的字段数据(FieldName={0})没有数据区！", Field.FieldName);
logger.Error(error);
ErrorList.Add(error);
continue;
}
int[] Locations = ConvertIntFromRange(Field.RangeInfo);
String XPath = String.Format("//CellStyle[@Row='{0}'][@Column='{1}']/CellType", Locations[1], Locations[0]);
XmlNode Node = doc.SelectSingleNode(XPath);
if (Node == null)
{
string error = string.Format("使用xpath表达式({0})在表单中查找字段数据(FieldName={1})的单元格类型失败！", XPath, Field.FieldName);
logger.Error(error);
ErrorList.Add(error);
continue;
}
if (Node.Attributes["class"] == null)
{
string error = string.Format("表单中的字段(FieldName={0})单元格类型已找到，获取该节点的class属性失败！", Field.FieldName);
logger.Error(error);
ErrorList.Add(error);
continue;
}
DataRow[] SchemaRows = DataSchema.Select("col_name='" + FieldName + "'");
if (SchemaRows.Length > 0)
{
String col_type = SchemaRows[0]["col_type"].ToString();
if (!FieldType.BasicDataType.ToLower().StartsWith(col_type.ToLower()))
{
string error = string.Format("{0}:[syscolumns]{1} != [sys_columns]{2}", Field.RangeInfo, col_type.ToLower(), FieldType.BasicDataType.ToLower());
logger.Error(error);
ErrorList.Add(error);
}
string celltype = GetFieldTypeDescription(Node.Attributes["class"].Value);
if (celltype != Field.FieldType.Description)
{
string error = string.Format("{0}:[sys_columns]{1} != [sheet]{2}", Field.RangeInfo, celltype, Field.FieldType.Description);
logger.Error(error);
ErrorList.Add(error);
}
}
else
{
logger.Error(string.Format("sys_columns 中的列 ‘{0}.{1}’没有在物理表 syscolumns 中找到对应项", Field.TableInfo.Name, Field.FieldName));
}
}
}
}
else
{
logger.Error(string.Format("数据表 {0}的字段信息未找到", DataTableName));
}
return(ErrorList);
}
void worker_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
BatchPrintClass Params = e.Argument as BatchPrintClass;
ModuleConfiguration Model = Params.modelInfo;
String DataCode = Params.dataCode;
ModelDataManager DataManager = new ModelDataManager();
DataSet dataSet = new DataSet();
if (dataID != null)
{
dataSet = DataManager.GetData(Model, dataID, DataCode);
}
else
{
dataSet = DataManager.GetData(Model, DataCode);
}
FpSpread fpSpread = new MyCell();
PrintDialog Dialog = new PrintDialog();
Dialog.AllowSomePages = true;
Dialog.PrinterSettings.PrintRange = PrintRange.SomePages;
Dialog.PrinterSettings.FromPage = 1;
Dialog.PrinterSettings.ToPage = 1;
if (DialogResult.OK == Dialog.ShowDialog())
{
//初始化模板样式
fpSpread.Sheets.Clear();
foreach (SheetConfiguration Sheet in Model.Sheets)
{
SheetView SheetView = Serializer.LoadObjectXml(typeof(SheetView), Sheet.SheetStyle, "SheetView") as SheetView;
SheetView.Tag = Sheet;
SheetView.SheetName = Sheet.Description;
fpSpread.Sheets.Add(SheetView);
}
fpSpread.LoadFormulas(true);
//加载数据到模板样式
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow Row in dataSet.Tables[0].Rows)
{
Application.DoEvents();
int Index = dataSet.Tables[0].Rows.IndexOf(Row);
worker.ReportProgress((Index + 1) / dataSet.Tables[0].Rows.Count);
String DataID = Row["ID"].ToString();
foreach (SheetView SheetView in fpSpread.Sheets)
{
SheetConfiguration SheetConfiguration = SheetView.Tag as SheetConfiguration;
TableDefineInfo TableInfo = SheetConfiguration.DataTableSchema.Schema;
if (TableInfo != null)
{
foreach (FieldDefineInfo FieldInfo in TableInfo.FieldInfos)
{
DataRow[] DataRows = dataSet.Tables[GetBracketName(TableInfo.Name)].Select("ID='" + DataID + "'");
SheetView.Cells[FieldInfo.RangeInfo].Value = DataRows[0][FieldInfo.FieldName];
}
}
}
//批量打印
BatchPrintDocument Document = new BatchPrintDocument(fpSpread);
Document.PrinterSettings = Dialog.PrinterSettings;
Document.Print();
}
e.Result = "已输出全部资料到打印机。";
}
else
{
e.Result = string.Format("模板 {0} 中没有资料数据。", Model.Description);
}
fpSpread.Dispose();
fpSpread = null;
}
else
{
return;
}
}
public Boolean UpdateTableDefineInfo(TableDefineInfo Info)
{
if (Info == null)
{
return(false);
}
Boolean Result = false;
StringBuilder Sql_Tables = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
Sql_Tables.Append("Select ID,SCTS,DESCRIPTION,TABLENAME,TABLETYPE,Scts_1 From sys_Tables Where Scdel=0 and ID='");
Sql_Tables.Append(Info.Index);
Sql_Tables.Append("'");
StringBuilder Sql_Fields = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
Sql_Fields.Append("Select ID,SCTS,SCPT,COLNAME,DESCRIPTION,COLTYPE,TABLENAME,IsKeyField,Scts_1 From sys_Columns Where Scdel=0 and TableName='");
Sql_Fields.Append(Info.Name);
Sql_Fields.Append("'");
List <string> Sql_Commands = new List <string>();
Sql_Commands.Add(Sql_Tables.ToString());
Sql_Commands.Add(Sql_Fields.ToString());
DataSet dataset = GetDataSet(Sql_Commands.ToArray());
if (dataset != null)
{
DataTable TableInfo = dataset.Tables["sys_Tables"];
DataTable FieldInfoData = dataset.Tables["sys_Columns"];
if (TableInfo != null && TableInfo.Rows.Count > 0)
{
DataRow Row = TableInfo.Rows[0];
Row["SCTS"] = DateTime.Now.ToString();
Row["DESCRIPTION"] = Info.Description;
Row["TABLENAME"] = Info.Name;
Row["TABLETYPE"] = Info.Type;
Row["Scts_1"] = DateTime.Now.ToString();
}
else
{
DataRow Row = TableInfo.NewRow();
Row["ID"] = Info.Index;
Row["SCTS"] = DateTime.Now.ToString();
Row["DESCRIPTION"] = Info.Description;
Row["TABLENAME"] = Info.Name;
Row["TABLETYPE"] = Info.Type;
Row["Scts_1"] = DateTime.Now.ToString();
TableInfo.Rows.Add(Row);
}
foreach (FieldDefineInfo fieldInfo in Info.FieldInfos)
{
DataRow[] Rows = FieldInfoData.Select("ID='" + fieldInfo.Index + "'");
if (Rows.Length > 0)
{
DataRow Row = Rows[0];
Row["SCTS"] = DateTime.Now.ToString();
Row["SCPT"] = string.Concat(fieldInfo.IsNotNull ? "1" : "0", fieldInfo.IsNotCopy ? "1" : "0", fieldInfo.IsPingxing ? "1" : "0", fieldInfo.IsReadOnly ? "1" : "0");
Row["COLNAME"] = fieldInfo.FieldName;
Row["DESCRIPTION"] = fieldInfo.Description;
Row["COLTYPE"] = fieldInfo.FieldType.Description;
Row["TABLENAME"] = Info.Name;
Row["IsKeyField"] = fieldInfo.IsKeyField;
Row["Scts_1"] = DateTime.Now.ToString();
}
else
{
DataRow Row = FieldInfoData.NewRow();
Row["ID"] = fieldInfo.Index;
Row["SCTS"] = DateTime.Now.ToString();
Row["SCPT"] = string.Concat(fieldInfo.IsNotNull ? "1" : "0", fieldInfo.IsNotCopy ? "1" : "0", fieldInfo.IsPingxing ? "1" : "0", fieldInfo.IsReadOnly ? "1" : "0");
Row["COLNAME"] = fieldInfo.FieldName;
Row["DESCRIPTION"] = fieldInfo.Description;
Row["COLTYPE"] = fieldInfo.FieldType.Description;
Row["TABLENAME"] = Info.Name;
Row["IsKeyField"] = fieldInfo.IsKeyField;
Row["Scts_1"] = DateTime.Now.ToString();
FieldInfoData.Rows.Add(Row);
}
}
IDbConnection DbConnection = GetConntion();
Transaction Transaction = new Transaction(DbConnection);
try
{
object r = Update(dataset, Transaction);
Result = (Convert.ToInt32(r) == 1);
if (Result)
{
Transaction.Commit();
}
else
{
Transaction.Rollback();
}
}
catch (Exception ex)
{
Transaction.Rollback();
logger.Error(String.Format("UpdateTableDefineInfo(TableDefineInfo Info) 更新表信息‘sys_Tables,sys_Columns’出错，原因：{0}", ex.Message));
return(false);
}
}
return(Result);
}
public TableDefineInfo GetTableDefineInfo(string TableName)
{
if (string.IsNullOrEmpty(TableName))
{
return(null);
}
TableDefineInfo Info = null;
StringBuilder Sql_Select = new StringBuilder();
// 增加查询条件 (Scdel IS NULL or Scdel=0) 2013-10-16
Sql_Select.Append("Select * from sys_Tables where (Scdel IS NULL or Scdel=0) And TABLENAME='");
Sql_Select.Append(TableName);
Sql_Select.Append("'");
StringBuilder Sql_FieldInfos = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
Sql_FieldInfos.Append("Select * from sys_Columns where (Scdel IS NULL or Scdel=0) And TableName ='");
Sql_FieldInfos.Append(TableName);
Sql_FieldInfos.Append("'");
List <string> Sql_Commands = new List <string>();
Sql_Commands.Add(Sql_Select.ToString());
Sql_Commands.Add(Sql_FieldInfos.ToString());
DataSet dataset = GetDataSet(Sql_Commands.ToArray());
if (dataset != null)
{
DataTable TableInfo = dataset.Tables["sys_Tables"];
DataTable FieldInfoData = dataset.Tables["sys_Columns"];
Info = new TableDefineInfo();
Info.Name = TableName;
Info.Description = TableInfo.Rows[0]["DESCRIPTION"].ToString();
Info.Index = TableInfo.Rows[0]["ID"].ToString();
foreach (DataRow row in FieldInfoData.Rows)
{
FieldDefineInfo FieldInfo = new FieldDefineInfo(Info);
FieldInfo.Index = row["ID"].ToString();
FieldInfo.Description = row["DESCRIPTION"].ToString();
FieldInfo.FieldName = row["COLNAME"].ToString();
FieldInfo.IsKeyField = Convert.ToBoolean(row["IsKeyField"]);
String scpt = row["scpt"].ToString();
FieldInfo.IsNotNull = ((scpt.Length > 0 ? scpt.Substring(0, 1) : "0") == "1" ? true : false);
FieldInfo.IsNotCopy = ((scpt.Length > 1 ? scpt.Substring(1, 1) : "0") == "1" ? true : false);
FieldInfo.IsPingxing = ((scpt.Length > 2 ? scpt.Substring(2, 1) : "0") == "1" ? true : false);
FieldInfo.IsReadOnly = ((scpt.Length > 3 ? scpt.Substring(3, 1) : "0") == "1" ? true : false);
String ColType = row["COLType"].ToString();
FieldInfo.FieldType = FieldType.GetFieldType(ColType);
}
}
return(Info);
}
public Boolean UpdateDataAreaInfo(string SheetID, TableDefineInfo TableInfo)
{
if (string.IsNullOrEmpty(SheetID) || TableInfo == null)
{
return(false);
}
Boolean Result = false;
StringBuilder Sql_Select = new StringBuilder();
//增加查询条件Scdel=0 2013-10-19
Sql_Select.Append("Select ID,SCTS,SheetID,TableName,ColumnName,Range,Scts_1 From sys_biz_DataArea Where Scdel=0 and SheetID='");
Sql_Select.Append(SheetID);
Sql_Select.Append("' and TableName='");
Sql_Select.Append(TableInfo.Name);
Sql_Select.Append("'");
DataTable Data = GetDataTable(Sql_Select.ToString());
if (Data != null)
{
foreach (FieldDefineInfo FieldInfo in TableInfo.FieldInfos)
{
DataRow[] Rows = Data.Select("ColumnName='" + FieldInfo.FieldName + "'");
if (Rows.Length > 0)
{
DataRow Row = Rows[0];
Row["SCTS"] = DateTime.Now.ToString();
Row["SheetID"] = SheetID;
Row["TableName"] = TableInfo.Name;
Row["ColumnName"] = FieldInfo.FieldName;
Row["Range"] = FieldInfo.RangeInfo;
//增加字段Scts_1 2013-10-15
Row["Scts_1"] = DateTime.Now.ToString();
}
else
{
DataRow Row = Data.NewRow();
Row["ID"] = Guid.NewGuid().ToString();
Row["SCTS"] = DateTime.Now.ToString();
Row["SheetID"] = SheetID;
Row["TableName"] = TableInfo.Name;
Row["ColumnName"] = FieldInfo.FieldName;
Row["Range"] = FieldInfo.RangeInfo;
//增加字段Scts_1 2013-10-15
Row["Scts_1"] = DateTime.Now.ToString();
Data.Rows.Add(Row);
}
}
try
{
object r = Update(Data);
Result = (Convert.ToInt32(r) == 1);
}
catch (Exception ex)
{
logger.Error(String.Format("UpdateDataAreaInfo(string SheetID, TableDefineInfo TableInfo) 更新表‘sys_biz_DataArea’出错，原因：{0}", ex.Message));
return(false);
}
}
return(Result);
}
public Boolean DeleteTableDefineInfo(TableDefineInfo Info)
{
if (Info == null)
{
return(false);
}
//增加字段Scts_1,Scdel 之后 删除操作只做伪删除，用于数据同步 2013-10-15
StringBuilder Sql_DeleteTable = new StringBuilder();
//Sql_DeleteTable.Append("Delete From sys_Tables Where ID ='");
//Sql_DeleteTable.Append(Info.Index);
//Sql_DeleteTable.Append("'");
Sql_DeleteTable.Append("Update sys_Tables set Scts_1='" + DateTime.Now + "',Scdel=1 Where ID ='");
Sql_DeleteTable.Append(Info.Index);
Sql_DeleteTable.Append("'");
StringBuilder Sql_DeleteField = new StringBuilder();
//Sql_DeleteField.Append("Delete From sys_Columns Where TableName='");
//Sql_DeleteField.Append(Info.Name);
//Sql_DeleteField.Append("'");
Sql_DeleteField.Append("Update sys_Columns set Scts_1='" + DateTime.Now + "',Scdel=1 Where TableName='");
Sql_DeleteField.Append(Info.Name);
Sql_DeleteField.Append("'");
List <string> Sql_Commands = new List <string>();
Sql_Commands.Add(Sql_DeleteTable.ToString());
Sql_Commands.Add(Sql_DeleteField.ToString());
Boolean Result = false;
IDbConnection DbConnection = GetConntion();
Transaction Transaction = new Transaction(DbConnection);
try
{
object r = ExcuteCommands(Sql_Commands.ToArray(), Transaction);
int[] ints = (int[])r;
for (int i = 0; i < ints.Length; i++)
{
if (i != 0)
{
Result = Result & (Convert.ToInt32(ints[i]) == 1);
}
else
{
Result = (Convert.ToInt32(ints[i]) == 1);
}
}
if (Result)
{
Transaction.Commit();
}
else
{
Transaction.Rollback();
}
}
catch (Exception ex)
{
Transaction.Rollback();
logger.Error(String.Format("DeleteTableDefineInfo(TableDefineInfo Info) 删除表信息‘sys_Tables,sys_Columns’出错，原因：{0}", ex.Message));
return(false);
}
return(Result);
}
private void ButtonOk_Click(object sender, EventArgs e)
{
if (modelInfo == null)
{
return;
}
modelIndex = modelInfo.Index;
modelText = modelInfo.Description;
ModelFields.Clear();
if (CheckBox_ModelIndex.Checked)
{
ModelFields.Add(string.Concat("'", modelIndex, "'"));
}
if (CheckBox_ModelName.Checked)
{
ModelFields.Add(string.Concat("'", modelText, "'"));
}
if (CheckBox_SCTS.Checked)
{
ModelFields.Add(string.Concat("biz_norm_extent_", modelIndex, ".SCTS"));
}
if (CheckBox_SCPT.Checked)
{
ModelFields.Add(string.Concat("biz_norm_extent_", modelIndex, ".SCPT"));
}
if (CheckBox_SCCT.Checked)
{
ModelFields.Add(string.Concat("biz_norm_extent_", modelIndex, ".SCCT"));
}
foreach (SheetView Sheet in FpSpread.Sheets)
{
if (Sheet.Tag is SheetConfiguration)
{
SheetConfiguration sheetConfiguration = Sheet.Tag as SheetConfiguration;
TableDefineInfo TableSchema = sheetConfiguration.DataTableSchema.Schema;
if (TableSchema != null)
{
foreach (FieldDefineInfo FieldInfo in TableSchema.FieldInfos)
{
if (Sheet.Cells[FieldInfo.RangeInfo].BackColor == Color.Blue)
{
ModelFields.Add(string.Concat("[", TableSchema.Name, "].[", FieldInfo.FieldName, "]"));
}
}
}
}
else
{
foreach (Row Row in Sheet.Rows)
{
if (Row.BackColor == Color.Blue)
{
ModelFields.Add(string.Concat("[", Sheet.Tag.ToString(), "].[", Sheet.Cells[Row.Index, 0].Text, "]"));
}
}
}
}
this.DialogResult = DialogResult.OK;
Close();
}
public static List <FieldDefineInfo> GetFieldDefineInfos(TableDefineInfo TableInfo)
{
List <FieldDefineInfo> FieldInfos = Agent.CallService("Yqun.BO.BusinessManager.dll", "GetFieldDefineInfos", new object[] { TableInfo }) as List <FieldDefineInfo>;
return(FieldInfos);
}
//获得台账数据条数
public int GetCount(TableDefineInfo ExtentDataSchema, String ModuleCode)
{
return(Convert.ToInt32(Agent.CallService("Yqun.BO.BusinessManager.dll", "GetCount", new object[] { ExtentDataSchema, ModuleCode })));
}
private void ModelView_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
ProgressScreen.Current.ShowSplashScreen();
this.AddOwnedForm(ProgressScreen.Current);
modelInfo = DepositoryModuleConfiguration.InitModuleConfiguration(e.Node.Name);
FpSpread.Sheets.Clear();
foreach (SheetConfiguration Sheet in modelInfo.Sheets)
{
if (Sheet == null)
{
continue;
}
ProgressScreen.Current.SetStatus = "正在初始化表单‘" + Sheet.Description + "’";
SheetView SheetView = Serializer.LoadObjectXml(typeof(SheetView), Sheet.SheetStyle, "SheetView") as SheetView;
SheetView.Tag = Sheet;
SheetView.SheetName = Sheet.Description;
SheetView.OperationMode = OperationMode.ReadOnly;
FpSpread.Sheets.Add(SheetView);
if (Sheet.DataTableSchema.Schema == null)
{
continue;
}
foreach (FieldDefineInfo field in Sheet.DataTableSchema.Schema.FieldInfos)
{
SheetView.Cells[field.RangeInfo].BackColor = Color.LightPink;
SheetView.Cells[field.RangeInfo].Tag = field;
}
}
//将表外字段的数据表显示在当前窗口中
TableDefineInfo Info = modelInfo.ExtentDataSchema;
SheetView ExtentSheet = new SheetView("系统表（含表外字段）");
ExtentSheet.OperationMode = OperationMode.RowMode;
ExtentSheet.SelectionUnit = FarPoint.Win.Spread.Model.SelectionUnit.Row;
ExtentSheet.Protect = true;
ExtentSheet.ColumnCount = 3;
ExtentSheet.Columns[0].Label = "字段名称";
ExtentSheet.Columns[0].Width = 100;
ExtentSheet.Columns[0].Locked = true;
ExtentSheet.Columns[0].HorizontalAlignment = CellHorizontalAlignment.Center;
ExtentSheet.Columns[0].VerticalAlignment = CellVerticalAlignment.Center;
ExtentSheet.Columns[1].Label = "字段描述";
ExtentSheet.Columns[1].Width = 100;
ExtentSheet.Columns[1].Locked = true;
ExtentSheet.Columns[1].HorizontalAlignment = CellHorizontalAlignment.Center;
ExtentSheet.Columns[1].VerticalAlignment = CellVerticalAlignment.Center;
ExtentSheet.Columns[2].Label = "数据类型";
ExtentSheet.Columns[2].Width = 100;
ExtentSheet.Columns[2].Locked = true;
ExtentSheet.Columns[2].HorizontalAlignment = CellHorizontalAlignment.Center;
ExtentSheet.Columns[2].VerticalAlignment = CellVerticalAlignment.Center;
ExtentSheet.RowCount = Info.FieldInfos.Count;
foreach (FieldDefineInfo FieldInfo in Info.FieldInfos)
{
int index = Info.FieldInfos.IndexOf(FieldInfo);
ExtentSheet.Cells[index, 0].Text = FieldInfo.FieldName;
ExtentSheet.Cells[index, 1].Text = FieldInfo.Description;
ExtentSheet.Cells[index, 2].Text = FieldInfo.FieldType.DisplayType;
}
ExtentSheet.Tag = Info.Name;
FpSpread.Sheets.Add(ExtentSheet);
this.RemoveOwnedForm(ProgressScreen.Current);
ProgressScreen.Current.CloseSplashScreen();
Activate();
}
public static TableDefineInfo GetTableDefineInfo(string TableName)
{
TableDefineInfo Info = Agent.CallService("Yqun.BO.BusinessManager.dll", "GetTableDefineInfo", new object[] { TableName }) as TableDefineInfo;
return(Info);
}
/// <summary>
///加载模板中所有表单的数据项值
/// </summary>
/// <param name="Schemas"></param>
public void InitDataItems()
{
foreach (SheetConfiguration Sheet in Module.Sheets)
{
DataTableSchema DataTable = Sheet.DataTableSchema;
TreeView treeView = WriteToTable_Value_Editor.DropDownControl;
treeView.Nodes.Clear();
if (DataTable.Schema != null)
{
TableDefineInfo TableInfo = DataTable.Schema;
DropDownNode Node = new DropDownNode(TableInfo.GetHashCode(), TableInfo.Description);
Node.Name = TableInfo.Name;
treeView.Nodes.Add(Node);
#region 时间戳字段
DropDownNode SubNode = new DropDownNode((int)DateTime.Now.Ticks, "时间戳");
SubNode.Name = "scts";
FieldInfo fieldInfo = new FieldInfo();
fieldInfo.Name = "scts";
fieldInfo.Text = "时间戳";
SubNode.Tag = fieldInfo;
Node.Nodes.Add(SubNode);
#endregion 时间戳字段
foreach (FieldDefineInfo FieldInfo in TableInfo.FieldInfos)
{
if (!ColumnRegular.JudgeColumnName(FieldInfo.Description))
{
SubNode = new DropDownNode(FieldInfo.GetHashCode(), FieldInfo.Description);
SubNode.Name = FieldInfo.FieldName;
fieldInfo = new FieldInfo();
fieldInfo.Name = FieldInfo.FieldName;
fieldInfo.Text = FieldInfo.Description;
SubNode.Tag = fieldInfo;
Node.Nodes.Add(SubNode);
}
}
}
treeView.ExpandAll();
treeView = ModificationValue_Editor.DropDownControl;
treeView.Nodes.Clear();
if (DataTable.Schema != null)
{
TableDefineInfo TableInfo = DataTable.Schema;
DropDownNode Node = new DropDownNode(TableInfo.GetHashCode(), TableInfo.Description);
Node.Name = TableInfo.Name;
treeView.Nodes.Add(Node);
#region 时间戳字段
DropDownNode SubNode = new DropDownNode((int)DateTime.Now.Ticks, "时间戳");
SubNode.Name = "scts";
FieldInfo fieldInfo = new FieldInfo();
fieldInfo.Name = "scts";
fieldInfo.Text = "时间戳";
SubNode.Tag = fieldInfo;
Node.Nodes.Add(SubNode);
#endregion 时间戳字段
foreach (FieldDefineInfo FieldInfo in TableInfo.FieldInfos)
{
if (!ColumnRegular.JudgeColumnName(FieldInfo.Description))
{
SubNode = new DropDownNode(FieldInfo.GetHashCode(), FieldInfo.Description);
SubNode.Name = FieldInfo.FieldName;
fieldInfo = new FieldInfo();
fieldInfo.Name = FieldInfo.FieldName;
fieldInfo.Text = FieldInfo.Description;
SubNode.Tag = fieldInfo;
Node.Nodes.Add(SubNode);
}
}
}
treeView.ExpandAll();
}
}
/// <summary>
/// 为西成线11标中心，删除219条资料的28d数据，并生成1条56d数据
/// </summary>
public void SpStadiumData()
{
ModuleManager Manager = new ModuleManager();
TableDefineInfoManager TableManager = new TableDefineInfoManager();
ModelDataManager DataManager = new ModelDataManager();
String modelIndex = "05d0d71b-def3-42ee-a16a-79b34de97e9b";
DataTable dataTb = GetDataTable("select * from [biz_norm_extent_" + modelIndex + "] WHERE scpt LIKE '0001001700010001%' ");
DataTable stadiumInfo = GetStadiumInfoByModelIndex(modelIndex);
StadiumConfig config = Newtonsoft.Json.JsonConvert.DeserializeObject <StadiumConfig>(stadiumInfo.Rows[0]["StadiumConfig"].ToString());
if (dataTb != null)
{
foreach (DataRow dataRow in dataTb.Rows)
{
try
{
DataTable dt = GetDataTable("SELECT * FROM sys_biz_reminder_stadiumData WHERE DataID='" + dataRow["ID"].ToString() + "' AND DateSpan = 56");
if (dt.Rows.Count == 0)
{
List <IndexDescriptionPair> Sheets = Manager.InitModuleInfo(modelIndex);
List <TableDefineInfo> TableSchemas = new List <TableDefineInfo>();
foreach (IndexDescriptionPair pair in Sheets)
{
TableDefineInfo Info = TableManager.GetTableDefineInfo(pair.Index, pair.DataTable);
TableSchemas.Add(Info);
}
String extentTable = "biz_norm_extent_" + modelIndex;
TableDefineInfo ExtentTableSchema = TableManager.GetTableDefineInfo("1", extentTable);
DataSet dataset = DataManager.GetData(TableSchemas, ExtentTableSchema, dataRow["ID"].ToString(), dataRow["scpt"].ToString());
DataTable extTable = dataset.Tables["[biz_norm_extent_" + modelIndex + "]"];
String dataID = extTable.Rows[0]["ID"].ToString();
String modelCode = extTable.Rows[0]["SCPT"].ToString();
String fName = extTable.Rows[0]["DataName"].ToString();
String fPH = config.fPH == null ? "" : dataset.Tables[config.fPH.TableName].Rows[0][config.fPH.ColumnName].ToString().Replace("'", "''");
String fZJRQ = config.fZJRQ == null ? "" : dataset.Tables[config.fZJRQ.TableName].Rows[0][config.fZJRQ.ColumnName].ToString();
String fSJBH = config.fSJBH == null ? "" : dataset.Tables[config.fSJBH.TableName].Rows[0][config.fSJBH.ColumnName].ToString().Replace("'", "''");
String fSJSize = config.fSJSize == null ? "" : dataset.Tables[config.fSJSize.TableName].Rows[0][config.fSJSize.ColumnName].ToString();
String fAdded = config.fAdded == null ? "" : dataset.Tables[config.fAdded.TableName].Rows[0][config.fAdded.ColumnName].ToString();
String fBGBH = config.fBGBH == null ? "" : dataset.Tables[config.fBGBH.TableName].Rows[0][config.fBGBH.ColumnName].ToString();
String fWTBH = config.fWTBH == null ? "" : dataset.Tables[config.fWTBH.TableName].Rows[0][config.fWTBH.ColumnName].ToString();
String insertSql = String.Format(@"INSERT INTO dbo.sys_biz_reminder_stadiumData
( DataID ,
Scts ,
ModelCode ,
ModelIndex ,
DateSpan ,
F_Name ,
F_ItemId ,
F_PH ,
F_ZJRQ ,
F_SJBH ,
F_SJSize ,
F_SYXM ,
F_BGBH ,
F_WTBH,
F_Added
)
VALUES ( '{0}' , '{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}' ,
'{8}' , '{9}' , '{10}' , '{11}' , '{12}' , '{13}','{14}' )",
dataID, DateTime.Now.ToString(), modelCode, modelIndex,
56, fName, "95", fPH, fZJRQ, fSJBH, fSJSize, "混凝土抗压56天", fBGBH, fWTBH, fAdded);
ExcuteCommand(insertSql);
}
}
catch (Exception ex)
{
logger.Error("SP Old Data Error: " + ex.Message + "; ModelIndex=" + modelIndex + "; DataID=" + dataRow["ID"].ToString());
}
}
}
}
public static Boolean Update(string SheetID, TableDefineInfo TableInfo)
{
Boolean Result = Convert.ToBoolean(Agent.CallService("Yqun.BO.BusinessManager.dll", "UpdateDataAreaInfo", new object[] { SheetID, TableInfo }));
return(Result);
}
//复制资料
public DataSet CopyData(ModuleConfiguration module, String[] DataID, String DataCode)
{
DataSet Data = DataManager.GetData(module, DataID, DataCode);
DataSet NewData = Data.Copy();
List <String> DataList = new List <string>();
for (int i = 0; i < DataID.Length; i++)
{
DataList.Add(Guid.NewGuid().ToString());
}
foreach (DataTable DataTable in NewData.Tables)
{
for (int i = 0; i < DataTable.Rows.Count; i++)
{
DataTable.Rows[i]["ID"] = DataList[i];
DataTable.Rows[i]["SCCS"] = Guid.NewGuid().ToString();
}
}
Dictionary <string, List <string> > Fields = new Dictionary <string, List <string> >();
foreach (SheetConfiguration sheetConfiguration in module.Sheets)
{
if (sheetConfiguration.DataTableSchema.Schema == null)
{
continue;
}
TableDefineInfo TableInfo = sheetConfiguration.DataTableSchema.Schema;
foreach (FieldDefineInfo field in TableInfo.FieldInfos)
{
if (field.IsKeyField)
{
if (!Fields.ContainsKey(TableInfo.Name))
{
Fields.Add(TableInfo.Name, new List <string>());
}
Fields[TableInfo.Name].Add(field.FieldName);
}
}
}
foreach (DataTable DataTable in NewData.Tables)
{
if (!Fields.ContainsKey(DataTable.TableName))
{
continue;
}
for (int i = 0; i < DataTable.Rows.Count; i++)
{
foreach (String key in Fields[DataTable.TableName])
{
DataTable.Rows[i][key] = "";
}
}
}
if (DataManager.UpdateData(module.Index, NewData, false))
{
return(Data);
}
else
{
return(new DataSet());
}
}
public static Boolean Update(TableDefineInfo Info)
{
Boolean Result = Convert.ToBoolean(Agent.CallService("Yqun.BO.BusinessManager.dll", "UpdateTableDefineInfo", new object[] { Info }));
return(Result);
}
public Boolean HasPingXingData(TableDefineInfo ExtentDataSchema, String RelationID)
{
return(Convert.ToBoolean(Agent.CallService("Yqun.BO.BusinessManager.dll", "HasPingXingData", new object[] { RelationID })));
}
