int FillObjects(string Table, TreeIter root, TableInfo.Params ParametersIn)
{
TreeIter DeleteIter, ClearIter, GroupIter, ItemIter;
int Totalcount = 0;
int DelCount = 0;
int ClearCount = 0;
int GroupCount;
DbCommand cmd;
DbDataReader rdr;
QSMain.CheckConnectionAlive();
logger.Debug ("Поиск зависимостей для таблицы {0}", Table);
if(!QSMain.ProjectTables.ContainsKey(Table))
{
ErrorString = "Нет описания для таблицы " + Table;
logger.Error(ErrorString);
ErrorHappens = true;
return 0;
}
if(QSMain.ProjectTables[Table].DeleteItems.Count > 0)
{
if(!ObjectsTreeStore.IterIsValid(root))
DeleteIter = ObjectsTreeStore.AppendNode();
else
DeleteIter = ObjectsTreeStore.AppendNode (root);
foreach(KeyValuePair<string, TableInfo.DeleteDependenceItem> pair in QSMain.ProjectTables[Table].DeleteItems)
{
GroupCount = 0;
if(!QSMain.ProjectTables.ContainsKey(pair.Key))
{
ErrorString = String.Format ("Зависимость удаления у таблицы {1} ссылается на таблицу {0} описания для которой нет.", pair.Key, Table);
logger.Error(ErrorString);
ErrorHappens = true;
return 0;
}
string sql = QSMain.ProjectTables[pair.Key].SqlSelect + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
rdr = cmd.ExecuteReader();
if(!rdr.HasRows)
{
rdr.Close ();
continue;
}
GroupIter = ObjectsTreeStore.AppendNode(DeleteIter);
int IndexIntParam = 0;
int IndexStrParam = 0;
if(QSMain.ProjectTables[pair.Key].PrimaryKey.ParamInt != "")
IndexIntParam = rdr.GetOrdinal(QSMain.ProjectTables[pair.Key].PrimaryKey.ParamInt);
if(QSMain.ProjectTables[pair.Key].PrimaryKey.ParamStr != "")
IndexStrParam = rdr.GetOrdinal(QSMain.ProjectTables[pair.Key].PrimaryKey.ParamStr);
List<object[]> ReadedData = new List<object[]>();
while(rdr.Read())
{
object[] Fields = new object[rdr.FieldCount];
rdr.GetValues(Fields);
ReadedData.Add(Fields);
}
rdr.Close ();
foreach(object[] row in ReadedData)
{
ItemIter = ObjectsTreeStore.AppendValues(GroupIter, String.Format(QSMain.ProjectTables[pair.Key].DisplayString, row));
if(QSMain.ProjectTables[pair.Key].DeleteItems.Count > 0 || QSMain.ProjectTables[pair.Key].ClearItems.Count > 0)
{
TableInfo.Params OutParam = new TableInfo.Params();
if(QSMain.ProjectTables[pair.Key].PrimaryKey.ParamInt != "")
OutParam.ParamInt = Convert.ToInt32(row[IndexIntParam]);
if(QSMain.ProjectTables[pair.Key].PrimaryKey.ParamStr != "")
OutParam.ParamStr = row[IndexStrParam].ToString();
Totalcount += FillObjects (pair.Key,ItemIter,OutParam);
}
GroupCount++;
Totalcount++;
DelCount++;
}
ObjectsTreeStore.SetValues(GroupIter, QSMain.ProjectTables[pair.Key].ObjectsName + "(" + GroupCount.ToString() + ")");
}
if(DelCount > 0)
ObjectsTreeStore.SetValues(DeleteIter, String.Format ("Будет удалено ({0}/{1}) объектов:",DelCount,Totalcount));
else
ObjectsTreeStore.Remove (ref DeleteIter);
}
if(QSMain.ProjectTables[Table].ClearItems.Count > 0)
{
if(!ObjectsTreeStore.IterIsValid(root))
ClearIter = ObjectsTreeStore.AppendNode();
else
ClearIter = ObjectsTreeStore.AppendNode (root);
foreach(KeyValuePair<string, TableInfo.ClearDependenceItem> pair in QSMain.ProjectTables[Table].ClearItems)
{
GroupCount = 0;
if(!QSMain.ProjectTables.ContainsKey(pair.Key))
{
ErrorString = String.Format ("Зависимость очистки у таблицы {1} ссылается на таблицу {0} описания для которой нет.", pair.Key, Table);
logger.Error(ErrorString);
ErrorHappens = true;
return 0;
}
string sql = QSMain.ProjectTables[pair.Key].SqlSelect + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
rdr = cmd.ExecuteReader();
if(!rdr.HasRows)
{
rdr.Close ();
continue;
}
GroupIter = ObjectsTreeStore.AppendNode(ClearIter);
while(rdr.Read())
{
object[] Fields = new object[rdr.FieldCount];
rdr.GetValues(Fields);
ItemIter = ObjectsTreeStore.AppendValues(GroupIter, String.Format(QSMain.ProjectTables[pair.Key].DisplayString,Fields));
GroupCount++;
Totalcount++;
ClearCount++;
}
ObjectsTreeStore.SetValues(GroupIter, QSMain.ProjectTables[pair.Key].ObjectsName + "(" + GroupCount.ToString() + ")");
rdr.Close ();
}
if(ClearCount > 0)
ObjectsTreeStore.SetValues(ClearIter, String.Format ("Будет очищено ссылок у {0} объектов:",ClearCount));
else
ObjectsTreeStore.Remove (ref ClearIter);
}
return Totalcount;
}
private void DeleteObjects(string Table, TableInfo.Params ParametersIn)
{
DbCommand cmd;
DbDataReader rdr;
string sql;
if(!QSMain.ProjectTables.ContainsKey(Table))
{
ErrorString = "Нет описания для таблицы " + Table;
logger.Error(ErrorString);
ErrorHappens = true;
return;
}
QSMain.CheckConnectionAlive();
if(QSMain.ProjectTables[Table].DeleteItems.Count > 0)
{
foreach(KeyValuePair<string, TableInfo.DeleteDependenceItem> pair in QSMain.ProjectTables[Table].DeleteItems)
{
if(QSMain.ProjectTables[pair.Key].DeleteItems.Count > 0 || QSMain.ProjectTables[pair.Key].ClearItems.Count > 0)
{
sql = "SELECT * FROM " +pair.Key + " " + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
rdr = cmd.ExecuteReader();
List<TableInfo.Params> ReadedData = new List<TableInfo.Params>();
string IntFieldName = QSMain.ProjectTables[pair.Key].PrimaryKey.ParamInt;
string StrFieldName = QSMain.ProjectTables[pair.Key].PrimaryKey.ParamStr;
while(rdr.Read())
{
TableInfo.Params OutParam = new TableInfo.Params();
if(IntFieldName != "")
OutParam.ParamInt = rdr.GetInt32(rdr.GetOrdinal(IntFieldName));
if(StrFieldName != "")
OutParam.ParamStr = rdr.GetString(rdr.GetOrdinal(StrFieldName));
ReadedData.Add(OutParam);
}
rdr.Close ();
foreach(TableInfo.Params row in ReadedData)
{
DeleteObjects (pair.Key, row);
}
}
sql = "DELETE FROM " + pair.Key + " " + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
cmd.ExecuteNonQuery();
}
}
if(QSMain.ProjectTables[Table].ClearItems.Count > 0)
{
foreach(KeyValuePair<string, TableInfo.ClearDependenceItem> pair in QSMain.ProjectTables[Table].ClearItems)
{
sql = "UPDATE " + pair.Key + " SET ";
bool first = true;
foreach (string FieldName in pair.Value.ClearFields)
{
if(!first)
sql += ", ";
sql += FieldName + " = NULL ";
first = false;
}
sql += pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
cmd.ExecuteNonQuery ();
}
}
sql = "DELETE FROM " + Table + " WHERE ";
bool FirstKey = true;
if(QSMain.ProjectTables[Table].PrimaryKey.ParamInt != "")
{
sql += QSMain.ProjectTables[Table].PrimaryKey.ParamInt + " = @IntParam ";
FirstKey = false;
}
if(QSMain.ProjectTables[Table].PrimaryKey.ParamStr != "")
{
if(!FirstKey)
sql += "AND ";
sql += QSMain.ProjectTables[Table].PrimaryKey.ParamStr + " = @StrParam ";
FirstKey = false;
}
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
TableInfo.PrimaryKeys TempParams = new TableInfo.PrimaryKeys(
QSMain.ProjectTables[Table].PrimaryKey.ParamInt != "" ? "@IntParam" : "",
QSMain.ProjectTables[Table].PrimaryKey.ParamStr != "" ? "@StrParam" : ""
);
AddParameters(cmd, TempParams, ParametersIn);
cmd.ExecuteNonQuery();
}
void AddParameters(DbCommand cmd, TableInfo.PrimaryKeys SqlParam, TableInfo.Params ParametersIn)
{
if(SqlParam.ParamStr != "")
{
DbParameter parameterStr = cmd.CreateParameter();
parameterStr.ParameterName = SqlParam.ParamStr;
parameterStr.Value = ParametersIn.ParamStr;
cmd.Parameters.Add(parameterStr);
}
if(SqlParam.ParamInt != "")
{
DbParameter parameterInt = cmd.CreateParameter();
parameterInt.ParameterName = SqlParam.ParamInt;
parameterInt.Value = ParametersIn.ParamInt;
cmd.Parameters.Add(parameterInt);
}
}
static void CreateProjectParam()
{
QSMain.ProjectPermission = new Dictionary<string, UserPermission>();
QSMain.User = new UserInfo();
//Параметры удаления
Dictionary<string, TableInfo> Tables = new Dictionary<string, TableInfo>();
QSMain.ProjectTables = Tables;
TableInfo PrepareTable;
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Каркасы";
PrepareTable.ObjectName = "каркас";
PrepareTable.SqlSelect = "SELECT name, id FROM basis ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("orders",
new TableInfo.DeleteDependenceItem("WHERE basis_id = @id ", "", "@id"));
Tables.Add("basis", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Кубы";
PrepareTable.ObjectName = "куб";
PrepareTable.SqlSelect = "SELECT name, id FROM cubes ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("order_details",
new TableInfo.DeleteDependenceItem("WHERE cube_id = @id ", "", "@id"));
Tables.Add("cubes", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Кубы в заказах";
PrepareTable.ObjectName = "куб в заказе";
PrepareTable.SqlSelect = "SELECT order_details.order_id, order_details.count, cubes.name, order_details.id FROM order_details " +
"LEFT JOIN cubes ON order_details.cube_id = cubes.id ";
PrepareTable.DisplayString = "{1} куб(ов) \"{2}\" в заказе №{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("order_details", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Компоненты каркаса";
PrepareTable.ObjectName = "компонент каркаса";
PrepareTable.SqlSelect = "SELECT nomenclature.name, basis.name, basis_items.id FROM basis_items " +
"LEFT JOIN nomenclature ON nomenclature.id = basis_items.item_id " +
"LEFT JOIN basis ON basis_items.basis_id = basis.id ";
PrepareTable.DisplayString = "Компонент {0} в {1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("basis_items", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Номенклатура";
PrepareTable.ObjectName = "номенклатуру";
PrepareTable.SqlSelect = "SELECT nomenclature.name, id FROM nomenclature ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("basis_items",
new TableInfo.DeleteDependenceItem("WHERE item_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add("cubes_items",
new TableInfo.DeleteDependenceItem("WHERE item_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add("order_basis_details",
new TableInfo.DeleteDependenceItem("WHERE nomenclature_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add("order_cubes_details",
new TableInfo.DeleteDependenceItem("WHERE nomenclature_id = @id ", "", "@id"));
Tables.Add("nomenclature", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Компоненты куба";
PrepareTable.ObjectName = "Компонент куба";
PrepareTable.SqlSelect = "SELECT nomenclature.name, cubes.name, cubes_items.id FROM cubes_items " +
"LEFT JOIN nomenclature ON cubes_items.item_id = nomenclature.id " +
"LEFT JOIN cubes ON cubes_items.cubes_id = cubes.id ";
PrepareTable.DisplayString = "{0} в кубе {1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("cubes_items", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Компоненты куба из заказов";
PrepareTable.ObjectName = "Компонент куба из заказа";
PrepareTable.SqlSelect = "SELECT nomenclature.name, order_cubes_details.order_id, order_cubes_details.id as id FROM order_cubes_details " +
"LEFT JOIN nomenclature ON order_cubes_details.nomenclature_id = nomenclature.id ";
PrepareTable.DisplayString = "{0} в заказе №{1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("order_cubes_details", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Выставки";
PrepareTable.ObjectName = "выставку";
PrepareTable.SqlSelect = "SELECT name, id FROM exhibition ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.ClearItems.Add ("orders",
new TableInfo.ClearDependenceItem ("WHERE exhibition_id = @exhibition_id ", "", "@exhibition_id", "exhibition_id"));
Tables.Add("exhibition", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Материалы";
PrepareTable.ObjectName = "материал";
PrepareTable.SqlSelect = "SELECT name, id FROM materials ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.ClearItems.Add ("order_details",
new TableInfo.ClearDependenceItem ("WHERE material_id = @material_id ", "", "@material_id", "material_id"));
PrepareTable.ClearItems.Add ("order_basis_details",
new TableInfo.ClearDependenceItem ("WHERE material_id = @material_id ", "", "@material_id", "material_id"));
Tables.Add("materials", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Виды облицовки";
PrepareTable.ObjectName = "вид облицовки";
PrepareTable.SqlSelect = "SELECT name, id FROM facing ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.ClearItems.Add ("order_details",
new TableInfo.ClearDependenceItem ("WHERE facing_id = @facing_id ", "", "@facing_id", "facing_id"));
PrepareTable.ClearItems.Add ("order_basis_details",
new TableInfo.ClearDependenceItem ("WHERE facing_id = @facing_id ", "", "@facing_id", "facing_id"));
Tables.Add("facing", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Компоненты каркаса из заказов";
PrepareTable.ObjectName = "Компонент каркаса из заказов";
PrepareTable.SqlSelect = "SELECT nomenclature.name, order_basis_details.order_id, order_basis_details.id as id FROM order_basis_details " +
"LEFT JOIN nomenclature ON order_basis_details.nomenclature_id = nomenclature.id ";
PrepareTable.DisplayString = "{0} в заказе №{1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("order_basis_details", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Заказы";
PrepareTable.ObjectName = "заказ";
PrepareTable.SqlSelect = "SELECT id FROM orders ";
PrepareTable.DisplayString = "Заказ №{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("orders", PrepareTable);
}
static void CreateProjectParam()
{
QSMain.AdminFieldName = "admin";
QSMain.ProjectPermission = new Dictionary<string, UserPermission>();
//QSMain.ProjectPermission.Add("edit_slips", new UserPermission("edit_slips", "Изменение кассы задним числом",
//"Пользователь может изменять или добавлять кассовые документы задним числом."));
QSMain.User = new UserInfo();
//Параметры удаления
Dictionary<string, TableInfo> Tables = new Dictionary<string, TableInfo>();
QSMain.ProjectTables = Tables;
TableInfo PrepareTable;
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Пользователи";
PrepareTable.ObjectName = "пользователя";
PrepareTable.SqlSelect = "SELECT name, id FROM users ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add ("users", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Услуги";
PrepareTable.ObjectName = "услуга";
PrepareTable.SqlSelect = "SELECT name , id FROM services ";
PrepareTable.DisplayString = "Услуга {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("order_pays",
new TableInfo.DeleteDependenceItem ("WHERE service_id = @id ", "", "@id"));
Tables.Add("services", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Оплаты заказа";
PrepareTable.ObjectName = "оплата заказа";
PrepareTable.SqlSelect = "SELECT order_id, id, cost FROM order_pays ";
PrepareTable.DisplayString = "Оплата в заказе {0} на сумму{2:C}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("order_pays", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Заказы";
PrepareTable.ObjectName = "заказ";
PrepareTable.SqlSelect = "SELECT id, date, hour FROM orders ";
PrepareTable.DisplayString = "Заказ №{0} на {2} часа {1:d} числа";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("orders", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Стекла в заказе";
PrepareTable.ObjectName = "стекло в заказе";
PrepareTable.SqlSelect = "SELECT order_id, id FROM order_glasses ";
PrepareTable.DisplayString = "Стекло в заказе №{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
Tables.Add("order_glasses", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Виды стекол";
PrepareTable.ObjectName = "вид стекла";
PrepareTable.SqlSelect = "SELECT name, id FROM glass ";
PrepareTable.DisplayString = "Стекло {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("order_glasses",
new TableInfo.DeleteDependenceItem ("WHERE glass_id = @id ", "", "@id"));
Tables.Add("glass", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Статусы";
PrepareTable.ObjectName = "статус";
PrepareTable.SqlSelect = "SELECT name, id FROM status ";
PrepareTable.DisplayString = "Статус {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("orders",
new TableInfo.DeleteDependenceItem ("WHERE status_id = @id ", "", "@id"));
Tables.Add("status", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Склады";
PrepareTable.ObjectName = "склад";
PrepareTable.SqlSelect = "SELECT name, id FROM stocks ";
PrepareTable.DisplayString = "Склад {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.ClearItems.Add ("orders",
new TableInfo.ClearDependenceItem ("WHERE stock_id = @id", "", "@id", "stock_id"));
Tables.Add("stocks", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Производители";
PrepareTable.ObjectName = "производитель";
PrepareTable.SqlSelect = "SELECT name, id FROM manufacturers ";
PrepareTable.DisplayString = "Производитель {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.ClearItems.Add ("orders",
new TableInfo.ClearDependenceItem ("WHERE manufacturer_id = @id", "", "@id", "manufacturer_id"));
Tables.Add("manufacturers", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Модели автомобилей";
PrepareTable.ObjectName = "модель автомобиля";
PrepareTable.SqlSelect = "SELECT marks.name, models.name, models.id FROM models " +
"LEFT JOIN marks ON marks.id = models.mark_id ";
PrepareTable.DisplayString = "Модель {0} {1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("orders",
new TableInfo.DeleteDependenceItem ("WHERE car_model_id = @id ", "", "@id"));
Tables.Add("models", PrepareTable);
PrepareTable = new TableInfo();
PrepareTable.ObjectsName = "Марки автомобилей";
PrepareTable.ObjectName = "марка автомобиля";
PrepareTable.SqlSelect = "SELECT name, id FROM marks ";
PrepareTable.DisplayString = "Марка {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys("id");
PrepareTable.DeleteItems.Add("models",
new TableInfo.DeleteDependenceItem ("WHERE mark_id = @id ", "", "@id"));
Tables.Add("marks", PrepareTable);
}
static void CreateProjectParam()
{
QSMain.ProjectPermission = new Dictionary<string, UserPermission> ();
QSMain.ProjectPermission.Add ("edit_slips", new UserPermission ("edit_slips", "Изменение кассы задним числом",
"Пользователь может изменять или добавлять кассовые документы задним числом."));
//Скрипты создания базы
DBCreator.AddBaseScript (
new Version(2,3),
"Чистая база",
"bazar.SQLScripts.new-2.3.sql"
);
//Настраиваем обновления
QSUpdater.DB.DBUpdater.AddUpdate (
new Version (2, 2),
new Version (2, 3),
"bazar.SQLScripts.Update 2.2 to 2.3.sql");
//Параметры удаления
Dictionary<string, TableInfo> Tables = new Dictionary<string, TableInfo> ();
QSMain.ProjectTables = Tables;
TableInfo PrepareTable;
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Места";
PrepareTable.ObjectName = "место";
PrepareTable.SqlSelect = "SELECT place_types.name as type, place_no, area , type_id FROM places " +
"LEFT JOIN place_types ON places.type_id = place_types.id ";
PrepareTable.DisplayString = "Место {0}-{1} с площадью {2} кв.м.";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("type_id", "place_no");
PrepareTable.DeleteItems.Add ("contracts",
new TableInfo.DeleteDependenceItem ("WHERE contracts.place_type_id = @type_id AND contracts.place_no = @place_no", "@place_no", "@type_id"));
PrepareTable.DeleteItems.Add ("meters",
new TableInfo.DeleteDependenceItem ("WHERE meters.place_type_id = @type_id AND meters.place_no = @place_no ", "@place_no", "@type_id"));
PrepareTable.DeleteItems.Add ("events",
new TableInfo.DeleteDependenceItem ("WHERE place_type_id = @type_id AND place_no = @place_no AND lessee_id IS NULL", "@place_no", "@type_id"));
PrepareTable.ClearItems.Add ("events",
new TableInfo.ClearDependenceItem ("WHERE place_type_id = @type_id AND place_no = @place_no AND lessee_id IS NOT NULL", "@place_no", "@type_id", "place_type_id", "place_no"));
Tables.Add ("places", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Договора";
PrepareTable.ObjectName = "договор";
PrepareTable.SqlSelect = "SELECT number, sign_date, lessees.name as lessee, contracts.id as id FROM contracts " +
"LEFT JOIN lessees ON lessees.id = lessee_id ";
PrepareTable.DisplayString = "Договор №{0} от {1:d} с арендатором {2}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("accrual",
new TableInfo.DeleteDependenceItem ("WHERE contract_id = @contract_id ", "", "@contract_id"));
PrepareTable.ClearItems.Add ("credit_slips",
new TableInfo.ClearDependenceItem ("WHERE contract_id = @contract_id ", "", "@contract_id", "contract_id"));
Tables.Add ("contracts", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Арендаторы";
PrepareTable.ObjectName = "арендатора";
PrepareTable.SqlSelect = "SELECT name , id FROM lessees ";
PrepareTable.DisplayString = "Арендатор {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("credit_slips",
new TableInfo.DeleteDependenceItem ("WHERE lessee_id = @lessee_id ", "", "@lessee_id"));
PrepareTable.DeleteItems.Add ("contracts",
new TableInfo.DeleteDependenceItem ("WHERE lessee_id = @lessee_id ", "", "@lessee_id"));
PrepareTable.DeleteItems.Add ("events",
new TableInfo.DeleteDependenceItem ("WHERE lessee_id = @lessee_id AND (place_type_id IS NULL OR place_no IS NULL) ", "", "@lessee_id"));
PrepareTable.ClearItems.Add ("events",
new TableInfo.ClearDependenceItem ("WHERE lessee_id = @lessee_id AND place_type_id IS NOT NULL AND place_no IS NOT NULL", "", "@lessee_id", "lessee_id"));
Tables.Add ("lessees", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "События";
PrepareTable.ObjectName = "событие";
PrepareTable.SqlSelect = "SELECT classes.name as class, date, place_types.name as type, place_no, lessees.name as lessee, events.id as id FROM events " +
"LEFT JOIN place_types ON events.place_type_id = place_types.id " +
"LEFT JOIN lessees ON events.lessee_id = lessees.id " +
"LEFT JOIN classes ON events.class_id = classes.id ";
PrepareTable.DisplayString = "{0} в {1} на месте {2}-{3} c арендатором {4}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("events", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Контактные лица";
PrepareTable.ObjectName = "контактное лицо";
PrepareTable.SqlSelect = "SELECT name, id FROM contact_persons ";
PrepareTable.DisplayString = "Контакт {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.ClearItems.Add ("places",
new TableInfo.ClearDependenceItem ("WHERE contact_person_id = @id", "", "@id", "contact_person_id"));
Tables.Add ("contact_persons", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Начисления";
PrepareTable.ObjectName = "начисление";
PrepareTable.SqlSelect = "SELECT DATE(CONCAT('2012-', month, '-1')) as month, year, lessees.name as lessee, contracts.number, accrual.id as id FROM accrual " +
"LEFT JOIN contracts ON accrual.contract_id = contracts.id " +
"LEFT JOIN lessees ON contracts.lessee_id = lessees.id ";
PrepareTable.DisplayString = "Начисление за {0:MMMM} {1} арендатору {2} по договору {3}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("credit_slips",
new TableInfo.DeleteDependenceItem ("WHERE accrual_id = @id AND operation = 'payment'", "", "@id"));
PrepareTable.ClearItems.Add ("credit_slips",
new TableInfo.ClearDependenceItem ("WHERE accrual_id = @id AND operation <> 'payment'", "", "@id", "accrual_id"));
Tables.Add ("accrual", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Оплаты";
PrepareTable.ObjectName = "оплату";
PrepareTable.SqlSelect = "SELECT payments.createdate, accrual_id, payments.id FROM payments ";
PrepareTable.DisplayString = "Оплата от {0:d} по начислению № {1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("payments", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Авансовые отчеты";
PrepareTable.ObjectName = "авансовый отчет";
PrepareTable.SqlSelect = "SELECT advance.id as id, date, employees.name as employee FROM advance " +
"LEFT JOIN employees ON employees.id = employee_id ";
PrepareTable.DisplayString = "Авансовый отчет №{0} от {1:d} на {2}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("advance", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Кассы";
PrepareTable.ObjectName = "кассу";
PrepareTable.SqlSelect = "SELECT name, id FROM cash ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("advance",
new TableInfo.DeleteDependenceItem ("WHERE cash_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("debit_slips",
new TableInfo.DeleteDependenceItem ("WHERE cash_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("accrual_pays",
new TableInfo.DeleteDependenceItem ("WHERE cash_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("contract_pays",
new TableInfo.DeleteDependenceItem ("WHERE cash_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("credit_slips",
new TableInfo.DeleteDependenceItem ("WHERE cash_id = @id ", "", "@id"));
Tables.Add ("cash", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Строки начислений";
PrepareTable.ObjectName = "строку начисления";
PrepareTable.SqlSelect = "SELECT accrual_id, services.name as service, (count * price) as sum, accrual_pays.id as id FROM accrual_pays " +
"LEFT JOIN services ON service_id = services.id ";
PrepareTable.DisplayString = "Строка в начислении {0} услуги {1} на сумму {2:C}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.ClearItems.Add ("meter_reading",
new TableInfo.ClearDependenceItem ("WHERE accrual_pay_id = @id", "", "@id", "accrual_pay_id"));
PrepareTable.DeleteItems.Add ("payment_details",
new TableInfo.DeleteDependenceItem ("WHERE accrual_pay_id = @id ", "", "@id"));
Tables.Add ("accrual_pays", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Строки оплаты";
PrepareTable.ObjectName = "строку оплаты";
PrepareTable.SqlSelect = "SELECT payment_id, services.name as service, sum, payment_details.id as id FROM payment_details " +
"LEFT JOIN accrual_pays ON accrual_pays.id = payment_details.accrual_pay_id " +
"LEFT JOIN services ON accrual_pays.service_id = services.id ";
PrepareTable.DisplayString = "Строка оплаты услуги {1} на сумму {2:C} в платеже {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("payment_details", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Строки услуг в договоре";
PrepareTable.ObjectName = "строку услуги в договоре";
PrepareTable.SqlSelect = "SELECT services.name as service, contracts.number, (count * price) as sum, contract_pays.id as id FROM contract_pays " +
"LEFT JOIN services ON service_id = services.id " +
"LEFT JOIN contracts ON contracts.id = contract_pays.contract_id ";
PrepareTable.DisplayString = "Строка услуги {0} в договоре {1} на сумму {2:C}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("contract_pays", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Типы событий";
PrepareTable.ObjectName = "тип события";
PrepareTable.SqlSelect = "SELECT name, id FROM classes ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("events",
new TableInfo.DeleteDependenceItem ("WHERE class_id = @id ", "", "@id"));
Tables.Add ("classes", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Контрагенты";
PrepareTable.ObjectName = "контрагента";
PrepareTable.SqlSelect = "SELECT name, id FROM contractors ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.ClearItems.Add ("debit_slips",
new TableInfo.ClearDependenceItem ("WHERE contractor_id = @id", "", "@id", "contractor_id"));
PrepareTable.ClearItems.Add ("advance",
new TableInfo.ClearDependenceItem ("WHERE contractor_id = @id", "", "@id", "contractor_id"));
Tables.Add ("contractors", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Приходные ордера";
PrepareTable.ObjectName = "приходный ордер";
PrepareTable.SqlSelect = "SELECT id, date, sum FROM credit_slips ";
PrepareTable.DisplayString = "Приходный ордер №{0} от {1:d} на сумму {2:C}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("payments",
new TableInfo.DeleteDependenceItem ("WHERE credit_slip_id = @id ", "", "@id"));
Tables.Add ("credit_slips", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Расходные ордера";
PrepareTable.ObjectName = "расходный ордер";
PrepareTable.SqlSelect = "SELECT id, date, sum FROM debit_slips ";
PrepareTable.DisplayString = "Расходный ордер №{0} от {1:d} на сумму {2:C}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("debit_slips", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Сотрудники";
PrepareTable.ObjectName = "сотрудника";
PrepareTable.SqlSelect = "SELECT name, id FROM employees ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("advance",
new TableInfo.DeleteDependenceItem ("WHERE employee_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("credit_slips",
new TableInfo.DeleteDependenceItem ("WHERE employee_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("debit_slips",
new TableInfo.DeleteDependenceItem ("WHERE employee_id = @id ", "", "@id"));
Tables.Add ("employees", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Статьи расходов";
PrepareTable.ObjectName = "статью расходов";
PrepareTable.SqlSelect = "SELECT name, id FROM expense_items ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("advance",
new TableInfo.DeleteDependenceItem ("WHERE expense_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("debit_slips",
new TableInfo.DeleteDependenceItem ("WHERE expense_id = @id ", "", "@id"));
Tables.Add ("expense_items", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Группы товаров";
PrepareTable.ObjectName = "группу товаров";
PrepareTable.SqlSelect = "SELECT name, id FROM goods ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.ClearItems.Add ("lessees",
new TableInfo.ClearDependenceItem ("WHERE goods_id = @id", "", "@id", "goods_id"));
Tables.Add ("goods", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Статьи доходов";
PrepareTable.ObjectName = "статью доходов";
PrepareTable.SqlSelect = "SELECT name, id FROM income_items ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("credit_slips",
new TableInfo.DeleteDependenceItem ("WHERE income_id = @id ", "", "@id"));
PrepareTable.ClearItems.Add ("services",
new TableInfo.ClearDependenceItem ("WHERE income_id = @id", "", "@id", "income_id"));
Tables.Add ("income_items", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Организации";
PrepareTable.ObjectName = "организацию";
PrepareTable.SqlSelect = "SELECT name, id FROM organizations ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("advance",
new TableInfo.DeleteDependenceItem ("WHERE org_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("debit_slips",
new TableInfo.DeleteDependenceItem ("WHERE org_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("contracts",
new TableInfo.DeleteDependenceItem ("WHERE org_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("credit_slips",
new TableInfo.DeleteDependenceItem ("WHERE org_id = @id ", "", "@id"));
PrepareTable.ClearItems.Add ("places",
new TableInfo.ClearDependenceItem ("WHERE org_id = @id", "", "@id", "org_id"));
Tables.Add ("organizations", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Типы мест";
PrepareTable.ObjectName = "тип места";
PrepareTable.SqlSelect = "SELECT name, description, id FROM place_types ";
PrepareTable.DisplayString = "{0} - {1}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("places",
new TableInfo.DeleteDependenceItem ("WHERE type_id = @id ", "", "@id"));
Tables.Add ("place_types", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Услуги";
PrepareTable.ObjectName = "услугу";
PrepareTable.SqlSelect = "SELECT name, id FROM services ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("accrual_pays",
new TableInfo.DeleteDependenceItem ("WHERE service_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("contract_pays",
new TableInfo.DeleteDependenceItem ("WHERE service_id = @id ", "", "@id"));
PrepareTable.ClearItems.Add ("meter_tariffs",
new TableInfo.ClearDependenceItem ("WHERE service_id = @id", "", "@id", "service_id"));
Tables.Add ("services", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Единицы измерения";
PrepareTable.ObjectName = "единицу измерения";
PrepareTable.SqlSelect = "SELECT name, id FROM units ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("services",
new TableInfo.DeleteDependenceItem ("WHERE units_id = @id ", "", "@id"));
Tables.Add ("units", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Показания счётчика";
PrepareTable.ObjectName = "показание счётчика";
PrepareTable.SqlSelect = "SELECT date, value, id FROM meter_reading ";
PrepareTable.DisplayString = "Показания {1} на {0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
Tables.Add ("meter_reading", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Тарифы счётчиков";
PrepareTable.ObjectName = "тариф счётчика";
PrepareTable.SqlSelect = "SELECT name, id FROM meter_tariffs ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("meter_reading",
new TableInfo.DeleteDependenceItem ("WHERE meter_tariff_id = @id ", "", "@id"));
Tables.Add ("meter_tariffs", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Типы счётчиков";
PrepareTable.ObjectName = "тип счётчика";
PrepareTable.SqlSelect = "SELECT name, id FROM meter_types ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("meter_tariffs",
new TableInfo.DeleteDependenceItem ("WHERE meter_type_id = @id ", "", "@id"));
PrepareTable.DeleteItems.Add ("meters",
new TableInfo.DeleteDependenceItem ("WHERE meters.meter_type_id = @id ", "", "@id"));
Tables.Add ("meter_types", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Поставщики услуг";
PrepareTable.ObjectName = "поставщика";
PrepareTable.SqlSelect = "SELECT name, id FROM service_providers ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.ClearItems.Add ("services",
new TableInfo.ClearDependenceItem ("WHERE service_provider_id = @id ", "", "@id","service_provider_id"));
Tables.Add ("service_providers", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Cчётчики";
PrepareTable.ObjectName = "счётчик";
PrepareTable.SqlSelect = "SELECT meters.name, meter_types.name as type, meters.id FROM meters " +
"LEFT JOIN meter_types ON meters.meter_type_id = meter_types.id ";
PrepareTable.DisplayString = "{0} ({1})";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.DeleteItems.Add ("meter_reading",
new TableInfo.DeleteDependenceItem ("WHERE meter_id = @id ", "", "@id"));
PrepareTable.ClearItems.Add ("meters",
new TableInfo.ClearDependenceItem ("WHERE meters.parent_meter_id = @id", "", "@id", "parent_meter_id"));
Tables.Add ("meters", PrepareTable);
PrepareTable = new TableInfo ();
PrepareTable.ObjectsName = "Пользователи";
PrepareTable.ObjectName = "пользователя";
PrepareTable.SqlSelect = "SELECT name, id FROM users ";
PrepareTable.DisplayString = "{0}";
PrepareTable.PrimaryKey = new TableInfo.PrimaryKeys ("id");
PrepareTable.ClearItems.Add ("advance",
new TableInfo.ClearDependenceItem ("WHERE user_id = @id", "", "@id", "user_id"));
PrepareTable.ClearItems.Add ("debit_slips",
new TableInfo.ClearDependenceItem ("WHERE user_id = @id", "", "@id", "user_id"));
PrepareTable.ClearItems.Add ("accrual",
new TableInfo.ClearDependenceItem ("WHERE user_id = @id", "", "@id", "user_id"));
PrepareTable.ClearItems.Add ("credit_slips",
new TableInfo.ClearDependenceItem ("WHERE user_id = @id", "", "@id", "user_id"));
Tables.Add ("users", PrepareTable);
}
