//public override bool GetNeedSave()
//{
// if (Column != null)
// return Column.GetNeedSave();
// else
// return false;
//}
public SchemaTableColumnAddDialogModel(Controller controller, BaseModel parentModel, SchemaTable table)
: base(controller, parentModel)
{
EditedColumnDataTypes = new List<SqlDataType>();
Column = new SchemaTableColumn();
Column.Position = table.GetMaxColumnPosition() + 1;
Column.Table = table;
Column.DataType = new GuidDataType() { Column = Column };
Column.Name = "";
EditedObject = Column;
}
public SchemaTable GetProvodkasTable()
{
if (IsLogTable || IsProvodkasTable)
throw new Exception("internal error 23A598AB");
//if (!TableRoles.Contains(RoleConst.ВложеннаяТаблица))
// throw new Exception("internal error 86D606F2");
if (provodkasTable == null)
{
provodkasTable = new SchemaTable();
provodkasTable.ID = Guid.NewGuid();
provodkasTable.IsProvodkasTable = true;
provodkasTable.IsLogTable = false;
provodkasTable.Name = provodkasPrefix + Name;
var c = new SchemaTableColumn();
c.Name = "tableRecordID";
c.DataType = new GuidDataType() { Column = c };
c.IsNotNullable = true;
c.Table = provodkasTable;
provodkasTable.Columns.Add(c);
c = new SchemaTableColumn();
c.Name = "provodkaRecordID";
c.DataType = new GuidDataType() { Column = c };
c.IsNotNullable = true;
c.Table = provodkasTable;
provodkasTable.Columns.Add(c);
c = new SchemaTableColumn();
c.Name = "registrID";
c.DataType = new GuidDataType() { Column = c };
c.IsNotNullable = true;
c.Table = provodkasTable;
provodkasTable.Columns.Add(c);
}
return provodkasTable;
}
public override void firePropertyChanged(string propertyName)
{
columnsByName.Clear();
logTable = null;
cached_PrimaryKeyColumn = null;
base.firePropertyChanged(propertyName);
}
public SchemaTable GetLogTable()
{
if (IsLogTable || IsProvodkasTable)
throw new Exception("internal error 98AB23A5");
if (logTable == null)
{
logTable = App.Schema.GetObject<SchemaTable>(ID);
logTable.ID = Guid.NewGuid();
logTable.IsLogTable = true;
logTable.Name = logPrefix + Name;
var toDelete = new List<SchemaTableColumn>();
foreach (var col in logTable.Columns)
{
if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__")
toDelete.Add(col);
if (col.Name == "__timestamp__")
col.DataType = new BinaryDataType() { MaxLength = 8 };
}
toDelete.ForEach(col => logTable.Columns.Remove(col));
if (logTable.TableRoles.Contains(RoleConst.ВложеннаяТаблица))
if (GetColumnByName("__mastertimestamp__") == null)
{
var c = new SchemaTableColumn();
c.Position = Columns.Count;
c.Name = "__mastertimestamp__";
c.DataType = new BinaryDataType() { MaxLength = 8 };
c.IsNotNullable = false;
c.Table = logTable;
logTable.Columns.Add(c);
}
}
return logTable;
}
public void EmitCancelAddChangesSql(StringBuilder sql, string id_variable, SchemaTable masterTable, string indent)
{
int detNum = App.Random.Next(1, int.MaxValue / 2);
foreach (var detail in Details)
{
detNum++;
var detailTable = detail.GetDetailTable();
sql.AppendLine(indent + "DECLARE @detid" + detNum + " uniqueidentifier");
sql.AppendLine(indent + "DECLARE curs" + detNum + " CURSOR LOCAL STATIC FOR");
sql.AppendLine(indent + "SELECT ID FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + id_variable);
sql.AppendLine(indent + "OPEN curs" + detNum);
sql.AppendLine(indent + "FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum);
sql.AppendLine(indent + "WHILE @@FETCH_STATUS = 0");
sql.AppendLine(indent + "BEGIN");
detailTable.EmitCancelAddChangesSql(sql, "@detid" + detNum, this, indent + " ");
sql.AppendLine(indent + " FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum);
sql.AppendLine(indent + "END");
sql.AppendLine(indent + "CLOSE curs" + detNum);
sql.AppendLine(indent + "DEALLOCATE curs" + detNum);
}
sql.AppendLine(indent + "DELETE /*1*/ FROM [" + Name + "] WHERE ID=" + id_variable);
}
public void EmitSaveToLogSql(StringBuilder sql, string id_variable, SchemaTable masterTable, string indent)
{
sql.Append(indent + "INSERT [" + GetLogTable().Name + "] (");
foreach (var col in GetLogTable().Columns)
{
if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__") continue;
sql.Append(" [" + col.Name + "],");
}
sql.RemoveLastChar(1);
sql.AppendLine(")");
sql.Append(indent + "SELECT ");
foreach (var col in GetLogTable().Columns)
{
if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__") continue;
if (col.Name == "__mastertimestamp__")
sql.Append(" @master_timestamp,");
//sql.Append(" ( SELECT __timestamp__ FROM [" + masterTable.Name + "] WHERE ID=[" + Name + "].[" + GetMasterColumn().Name + "]),");
else
sql.Append(" [" + col.Name + "],");
}
sql.RemoveLastChar(1);
sql.AppendLine(" FROM [" + Name + "] WHERE ID=" + id_variable);
int detNum = App.Random.Next(1, int.MaxValue / 2);
foreach (var detail in Details)
{
detNum++;
var detailTable = detail.GetDetailTable();
sql.AppendLine(indent + "DECLARE @detid" + detNum + " uniqueidentifier");
sql.AppendLine(indent + "DECLARE curs" + detNum + " CURSOR LOCAL STATIC FOR");
sql.AppendLine(indent + "SELECT ID FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + id_variable);
sql.AppendLine(indent + "OPEN curs" + detNum);
sql.AppendLine(indent + "FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum);
sql.AppendLine(indent + "WHILE @@FETCH_STATUS = 0");
sql.AppendLine(indent + "BEGIN");
detailTable.EmitSaveToLogSql(sql, "@detid" + detNum, this, indent + " ");
sql.AppendLine(indent + " FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum);
sql.AppendLine(indent + "END");
sql.AppendLine(indent + "CLOSE curs" + detNum);
sql.AppendLine(indent + "DEALLOCATE curs" + detNum);
}
}
public SchemaTable GetRefTable()
{
if (RefTableID == null)
return null;
if (refTable_cached != null)
return refTable_cached;
if (SchemaBaseRole.Roles.ContainsKey((Guid)RefTableID))
throw new Exception("internal error 4B9C89C6");
refTable_cached = App.Schema.GetObject<SchemaTable>((Guid)RefTableID);
return refTable_cached;
}
public SchemaTable GetDetailTable(bool noCached=false)
{
if (noCached)
detailTable_cached = null;
if (DetailTableID == null)
return null;
if (detailTable_cached != null)
return detailTable_cached;
detailTable_cached = App.Schema.GetObject<SchemaTable>((Guid)DetailTableID);
return detailTable_cached;
}
public void SynchronizeTable(SchemaTable schemaTable)
{
var smoTable = GetSmoTable(schemaTable);
if (smoTable == null)
{
smoTable = new Table(GetSmoDatabase(), schemaTable.Name);
foreach (var column in schemaTable.Columns)
{
var newcol = column.GetSmoColumn(smoTable);
smoTable.Columns.Add(newcol);
}
smoTable.Create();
// primary key
if (!schemaTable.IsLogTable && !schemaTable.IsProvodkasTable)
{
var pk = new Index(smoTable, "PK_" + schemaTable.Name);
var icol = new IndexedColumn(pk, schemaTable.GetPrimaryKeyColumn().Name, false);
pk.IndexedColumns.Add(icol);
pk.IndexKeyType = IndexKeyType.DriPrimaryKey;
pk.IsClustered = true;
pk.FillFactor = 50;
pk.Create();
}
}
else
{
foreach (var schemaColumn in schemaTable.Columns)
{
if (smoTable.Columns.Contains(schemaColumn.Name))
{
if ((schemaColumn.Table.GetPrimaryKeyColumn()==null || schemaColumn.Name != schemaColumn.Table.GetPrimaryKeyColumn().Name) &&
!(schemaColumn.DataType is TimestampDataType))
{
var smoColumn = smoTable.Columns[schemaColumn.Name];
var newDataType = schemaColumn.DataType.GetSmoDataType();
if (!smoColumn.DataType.Equals(newDataType))
smoColumn.DataType = newDataType;
if (smoColumn.Nullable != !schemaColumn.IsNotNullable)
smoColumn.Nullable = !schemaColumn.IsNotNullable;
}
}
else
{
var newcol = schemaColumn.GetSmoColumn(smoTable);
smoTable.Columns.Add(newcol);
}
}
smoTable.Alter();
}
}
public Table GetSmoTable(SchemaTable schemaTable)
{
return GetSmoDatabase().Tables[schemaTable.Name];
}
void EmitSaveRegistrFromTempTable(StringBuilder sql, string indent, SchemaTable registrTable)
{
sql.Append(indent + "INSERT " + registrTable.Get4PartsTableName() + "(");
foreach (var col in registrTable.Columns)
sql.Append("[" + col.Name + "],");
sql.RemoveLastChar(1);
sql.Append(") SELECT ");
foreach (var col in registrTable.Columns)
sql.Append("[" + col.Name + "],");
sql.RemoveLastChar(1);
sql.AppendLine(" FROM " + registrTable.GetRegistrTempTableName());
sql.Append(indent + "INSERT " + GetProvodkasTable().Get4PartsTableName() + "(tableRecordID,provodkaRecordID,registrID)");
sql.AppendLine(" SELECT @masterID,[" + registrTable.GetPrimaryKeyColumn().Name + "]," + registrTable.ID.AsSQL() + " FROM " + registrTable.GetRegistrTempTableName());
}
void EmitGenerateProvodkaDbKrPartToTempTable(StringBuilder sql, string indent, SchemaTableProvodka prov, DbKrSaldo dbKr, SchemaTable registr, ObservableCollection<SchemaTableProvodkaField> registrFields)
{
var masterTable = this;
var detailTable = prov.OwnerTableOper.TableDetail.GetDetailTable();
var operID = prov.OwnerTableOper.ID;
sql.AppendLine(indent + "-- " + dbKr + ", регистр: " + registr.Name.AsSQL() + ", деталь: " + prov.OwnerTableOper.TableDetail.Name.AsSQL() + ", операция: '[" + prov.OwnerTableOper.Num + "] " + prov.OwnerTableOper.Name + "', проводка: " + prov.Name.AsSQL());
sql.AppendLine(indent + "INSERT " + registr.GetRegistrTempTableName() + "(");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Таблица_Ключ).Name + "],");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "],");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигДеталь).Name + "],");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигБизнесОперация).Name + "],");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигПроводка).Name + "],");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_Мастер).Name + "],");
sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_Деталь).Name + "],");
foreach (var field in registrFields.Where(field => registr.GetColumnByName(field.RegistrFieldName) != null))
{
sql.AppendLine(indent + " [" + field.RegistrFieldName + "],");
}
sql.RemoveLastChar(3);
sql.AppendLine();
sql.AppendLine(indent + ")");
sql.AppendLine(indent + "SELECT");
sql.AppendLine(indent + " NewID(),");
sql.AppendLine(indent + " '" + dbKr.ToString()[0] + "',");
sql.AppendLine(indent + " " + prov.OwnerTableOper.TableDetail.ID.AsSQL() + ", -- деталь: " + prov.OwnerTableOper.TableDetail.Name);
sql.AppendLine(indent + " " + prov.OwnerTableOper.ID.AsSQL() + ", -- бизнес-операция: " + prov.OwnerTableOper.Name);
sql.AppendLine(indent + " " + prov.ID.AsSQL() + ", -- проводка: " + prov.Name);
sql.AppendLine(indent + " @masterID,");
sql.AppendLine(indent + " " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.Таблица_Ключ).Name + "],");
foreach (var field in registrFields.Where(field => registr.GetColumnByName(field.RegistrFieldName) != null))
{
if (field.DataFieldName == "<пусто>")
sql.AppendLine(indent + " NULL,");
else
{
if (dbKr == DbKrSaldo.Кредит && registr.GetColumnByName(field.RegistrFieldName).ColumnRoles.Contains(RoleConst.Регистр_Мера))
sql.AppendLine(indent + " -" + detailTable.Get4PartsTableName() + ".[" + field.DataFieldName + "],");
else
sql.AppendLine(indent + " " + detailTable.Get4PartsTableName() + ".[" + field.DataFieldName + "],");
}
}
sql.RemoveLastChar(3);
sql.AppendLine();
sql.AppendLine(indent + "FROM " + detailTable.Get4PartsTableName());
sql.AppendLine(indent + "WHERE " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.ВложеннаяТаблица_БизнесОперация).Name + "]=" + operID.AsSQL() + " AND ");
sql.AppendLine(indent + " " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.ВложеннаяТаблица_Мастер).Name + "]=@masterID");
}
void EmitCreateRegisterTempTable(StringBuilder sql, string indent, SchemaTable registrTable)
{
sql.AppendLine(indent + "CREATE TABLE " + registrTable.GetRegistrTempTableName() + "(");
foreach (var col in registrTable.Columns)
{
sql.AppendLine(indent + " [" + col.Name + "] " + col.DataType.GetDeclareSql() + ",");
}
sql.RemoveLastChar(3);
sql.AppendLine();
sql.AppendLine(indent + ")");
}