void saveNewFolder()
{
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice";
oleDb.ExecuteFill("Practice");
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Practice (type, name, parent) VALUES (@type, @name, @parent)";
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
DataRow newRow = oleDb.dataSet.Tables["Practice"].NewRow();
newRow["type"] = Constants.TYPE_FOLDER;
newRow["name"] = textBox2.Text;
newRow["parent"] = "";
oleDb.dataSet.Tables["Practice"].Rows.Add(newRow);
if (oleDb.ExecuteUpdate("Practice"))
{
Utilits.Console.Log("Новая папка - успешно сохранена.");
Close();
(ParentForm as PracticeForm).TableRefresh("");
}
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL && DataConfig.typeDatabase == DataConstants.TYPE_OLEDB)
{
// OLEDB
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, pass, permissions, info FROM Users WHERE (id = 0)";
oleDb.ExecuteFill("Users");
DataRow newRow = oleDb.dataSet.Tables["Users"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["pass"] = passTextBox1.Text;
newRow["permissions"] = setPermissions(permissionsComboBox.Text);
newRow["info"] = infoTextBox.Text;
oleDb.dataSet.Tables["Users"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Users (name, pass, permissions, info) VALUES (@name, @pass, @permissions, @info)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@pass", OleDbType.VarChar, 255, "pass");
oleDb.oleDbCommandInsert.Parameters.Add("@permissions", OleDbType.VarChar, 255, "permissions");
oleDb.oleDbCommandInsert.Parameters.Add("@info", OleDbType.LongVarChar, 0, "info");
if (oleDb.ExecuteUpdate("Users"))
{
DataForms.FClient.updateHistory("Users");
Utilits.Console.Log("Создан новый пользователь.");
Close();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER && DataConfig.typeDatabase == DataConstants.TYPE_MSSQL)
{
// MSSQL SERVER
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, pass, permissions, info FROM Users WHERE (id = 0)";
sqlServer.ExecuteFill("Users");
DataRow newRow = sqlServer.dataSet.Tables["Users"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["pass"] = passTextBox1.Text;
newRow["permissions"] = setPermissions(permissionsComboBox.Text);
newRow["info"] = infoTextBox.Text;
sqlServer.dataSet.Tables["Users"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Users (name, pass, permissions, info) VALUES (@name, @pass, @permissions, @info)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@pass", SqlDbType.VarChar, 255, "pass");
sqlServer.sqlCommandInsert.Parameters.Add("@permissions", SqlDbType.VarChar, 255, "permissions");
sqlServer.sqlCommandInsert.Parameters.Add("@info", SqlDbType.Text, 0, "info");
if (sqlServer.ExecuteUpdate("Users"))
{
DataForms.FClient.updateHistory("Users");
Utilits.Console.Log("Создан новый пользователь.");
Close();
}
}
}
public static bool SaveSettings()
{
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = ";Jet OLEDB:Database Password="******"12345";
OleDb oleDb;
oleDb = new OleDb(DataConfig.configFile);
try{
oleDb.oleDbCommandSelect.CommandText = "SELECT [id], [autoUpdate], [showConsole], [period] FROM Settings";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE Settings SET " +
"[autoUpdate] = @autoUpdate, " +
"[showConsole] = @showConsole, " +
"[period] = @period " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@autoUpdate", OleDbType.VarChar, 255, "autoUpdate");
oleDb.oleDbCommandUpdate.Parameters.Add("@showConsole", OleDbType.VarChar, 255, "showConsole");
oleDb.oleDbCommandUpdate.Parameters.Add("@period", OleDbType.VarChar, 255, "period");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
oleDb.ExecuteFill("Settings");
oleDb.dataSet.Tables["Settings"].Rows[0]["autoUpdate"] = DataConfig.autoUpdate.ToString();
oleDb.dataSet.Tables["Settings"].Rows[0]["showConsole"] = DataConfig.showConsole.ToString();
oleDb.dataSet.Tables["Settings"].Rows[0]["period"] = DataConfig.period;
if (oleDb.ExecuteUpdate("Settings"))
{
oleDb.Dispose();
Utilits.Console.Log("Сохранение настроек программы прошло успешно.");
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(true);
}
else
{
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Настройки программы не сохранены.", false, true);
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(false);
}
}catch (Exception ex) {
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] " + ex.Message.ToString(), false, true);
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(false);
}
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, type FROM Nomenclature WHERE (id = 0)";
oleDb.ExecuteFill("Nomenclature");
DataRow newRow = oleDb.dataSet.Tables["Nomenclature"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["type"] = DataConstants.FOLDER;
oleDb.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Nomenclature (name, type) VALUES (@name, @type)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
if (oleDb.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Создана новая папка.");
Close();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, type FROM Nomenclature WHERE (id = 0)";
sqlServer.ExecuteFill("Nomenclature");
DataRow newRow = sqlServer.dataSet.Tables["Nomenclature"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["type"] = "folder";
sqlServer.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Nomenclature (name, type) VALUES (@name, @type)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@type", SqlDbType.VarChar, 255, "type");
if (sqlServer.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Создана новая папка.");
Close();
}
}
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, info FROM Units WHERE (id = 0)";
oleDb.ExecuteFill("Units");
DataRow newRow = oleDb.dataSet.Tables["Units"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["info"] = infoTextBox.Text;
oleDb.dataSet.Tables["Units"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Units (name, info) VALUES (@name, @info)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@info", OleDbType.LongVarChar, 0, "info");
if (oleDb.ExecuteUpdate("Units"))
{
DataForms.FClient.updateHistory("Units");
Utilits.Console.Log("Создана новая единица измерения.");
Close();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, info FROM Units WHERE (id = 0)";
sqlServer.ExecuteFill("Units");
DataRow newRow = sqlServer.dataSet.Tables["Units"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["info"] = infoTextBox.Text;
sqlServer.dataSet.Tables["Units"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Units (name, info) VALUES (@name, @info)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@info", SqlDbType.Text, 0, "info");
if (sqlServer.ExecuteUpdate("Units"))
{
DataForms.FClient.updateHistory("Units");
Utilits.Console.Log("Создана новая единица измерения.");
Close();
}
}
}
public static bool SaveDatabaseSettings()
{
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = ";Jet OLEDB:Database Password="******"12345";
OleDb oleDb;
oleDb = new OleDb(DataConfig.configFile);
try{
oleDb.oleDbCommandSelect.CommandText = "SELECT [id], [name], [localDatabase], [typeDatabase], [typeConnection], [serverConnection] FROM DatabaseSettings";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE DatabaseSettings SET " +
"[name] = @name, " +
"[localDatabase] = @localDatabase, " +
"[typeDatabase] = @typeDatabase, " +
"[typeConnection] = @typeConnection, " +
"[serverConnection] = @serverConnection " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@localDatabase", OleDbType.VarChar, 255, "localDatabase");
oleDb.oleDbCommandUpdate.Parameters.Add("@typeDatabase", OleDbType.VarChar, 255, "typeDatabase");
oleDb.oleDbCommandUpdate.Parameters.Add("@typeConnection", OleDbType.VarChar, 255, "typeConnection");
oleDb.oleDbCommandUpdate.Parameters.Add("@serverConnection", OleDbType.VarChar, 255, "serverConnection");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
oleDb.ExecuteFill("DatabaseSettings");
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["localDatabase"] = DataConfig.localDatabase;
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["typeDatabase"] = DataConfig.typeDatabase;
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["typeConnection"] = DataConfig.typeConnection;
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["serverConnection"] = DataConfig.serverConnection;
oleDb.ExecuteUpdate("DatabaseSettings");
oleDb.Dispose();
Utilits.Console.Log("Сохранение настроек соединения с базой данных прошло успешно.");
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(true);
}catch (Exception ex) {
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Сохранение настроек соединения с базой данных. " + ex.Message.ToString(), false, true);
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(false);
}
}
void deleteUser()
{
if (listView1.SelectedIndices.Count > 0)
{
if (listView1.Items[listView1.SelectedIndices[0]].SubItems[1].Text.ToString() == "Администратор")
{
MessageBox.Show("Администратора нельзя удалить из системы.");
return;
}
if (MessageBox.Show("Удалить пользователя " + listView1.Items[listView1.SelectedIndices[0]].SubItems[1].Text.ToString() + " безвозвратно?", "Вопрос", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb.dataSet.Tables["Users"].Rows[listView1.SelectedIndices[0]].Delete();
if (oleDb.ExecuteUpdate("Users"))
{
Utilits.Console.Log("Пользователь был успешно удалён.");
DataForms.FClient.updateHistory("Users");
}
else
{
Utilits.Console.Log("[ОШИБКА] Произошла ошибка при удалении пользователя.");
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer.dataSet.Tables["Users"].Rows[listView1.SelectedIndices[0]].Delete();
if (sqlServer.ExecuteUpdate("Users"))
{
Utilits.Console.Log("Пользователь был успешно удалён.");
DataForms.FClient.updateHistory("Users");
}
else
{
Utilits.Console.Log("[ОШИБКА] Произошла ошибка при удалении пользователя.");
}
}
}
}
}
void savePrice()
{
returnNameColumn();
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO " + PriceName + " (" +
"name, code, series, article, remainder, manufacturer, price, " +
"discount1, discount2, discount3, discount4, term" +
") VALUES (" +
"@name, @code, @series, @article, @remainder, @manufacturer, @price, " +
"@discount1, @discount2, @discount3, @discount4, @term" +
")";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandInsert.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandInsert.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandInsert.Parameters.Add("@remainder", OleDbType.Double, 15, "remainder");
oleDb.oleDbCommandInsert.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandInsert.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandInsert.Parameters.Add("@discount1", OleDbType.Double, 15, "discount1");
oleDb.oleDbCommandInsert.Parameters.Add("@discount2", OleDbType.Double, 15, "discount2");
oleDb.oleDbCommandInsert.Parameters.Add("@discount3", OleDbType.Double, 15, "discount3");
oleDb.oleDbCommandInsert.Parameters.Add("@discount4", OleDbType.Double, 15, "discount4");
oleDb.oleDbCommandInsert.Parameters.Add("@term", OleDbType.Date, 15, "term");
oleDb.oleDbCommandUpdate.CommandText = "UPDATE " + PriceName + " SET " +
"[name] = @name, " +
"[code] = @code, " +
"[series] = @series, " +
"[article] = @article, " +
"[remainder] = @remainder, " +
"[manufacturer] = @manufacturer, " +
"[price] = @price, " +
"[discount1] = @discount1, " +
"[discount2] = @discount2, " +
"[discount3] = @discount3, " +
"[discount4] = @discount4, " +
"[term] = @term " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandUpdate.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandUpdate.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandUpdate.Parameters.Add("@remainder", OleDbType.Double, 15, "remainder");
oleDb.oleDbCommandUpdate.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandUpdate.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount1", OleDbType.Double, 15, "discount1");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount2", OleDbType.Double, 15, "discount2");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount3", OleDbType.Double, 15, "discount3");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount4", OleDbType.Double, 15, "discount4");
oleDb.oleDbCommandUpdate.Parameters.Add("@term", OleDbType.Date, 15, "term");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
oleDb.oleDbCommandDelete.CommandText = "DELETE * FROM " + PriceName + " WHERE ([id] = ?)";
oleDb.oleDbCommandDelete.Parameters.Add("id", OleDbType.Integer, 10, "id").SourceVersion = DataRowVersion.Original;
if (oleDb.ExecuteUpdate(PriceName))
{
Utilits.Console.Log("Прайс " + PriceName + " был успешно сохранён.");
Close();
}
else
{
oleDb.Error();
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + PriceName + " не удалось сохранить.");
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO " + PriceName + " (" +
"name, code, series, article, remainder, manufacturer, price, " +
"discount1, discount2, discount3, discount4, term" +
") VALUES (" +
"@name, @code, @series, @article, @remainder, @manufacturer, @price, " +
"@discount1, @discount2, @discount3, @discount4, @term" +
")";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandInsert.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandInsert.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandInsert.Parameters.Add("@remainder", SqlDbType.Float, 15, "remainder");
sqlServer.sqlCommandInsert.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandInsert.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandInsert.Parameters.Add("@discount1", SqlDbType.Float, 15, "discount1");
sqlServer.sqlCommandInsert.Parameters.Add("@discount2", SqlDbType.Float, 15, "discount2");
sqlServer.sqlCommandInsert.Parameters.Add("@discount3", SqlDbType.Float, 15, "discount3");
sqlServer.sqlCommandInsert.Parameters.Add("@discount4", SqlDbType.Float, 15, "discount4");
sqlServer.sqlCommandInsert.Parameters.Add("@term", SqlDbType.Date, 15, "term");
sqlServer.sqlCommandUpdate.CommandText = "UPDATE " + PriceName + " SET " +
"[name] = @name, " +
"[code] = @code, " +
"[series] = @series, " +
"[article] = @article, " +
"[remainder] = @remainder, " +
"[manufacturer] = @manufacturer, " +
"[price] = @price, " +
"[discount1] = @discount1, " +
"[discount2] = @discount2, " +
"[discount3] = @discount3, " +
"[discount4] = @discount4, " +
"[term] = @term " +
"WHERE ([id] = @id)";
sqlServer.sqlCommandUpdate.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandUpdate.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandUpdate.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandUpdate.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandUpdate.Parameters.Add("@remainder", SqlDbType.Float, 15, "remainder");
sqlServer.sqlCommandUpdate.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandUpdate.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount1", SqlDbType.Float, 15, "discount1");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount2", SqlDbType.Float, 15, "discount2");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount3", SqlDbType.Float, 15, "discount3");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount4", SqlDbType.Float, 15, "discount4");
sqlServer.sqlCommandUpdate.Parameters.Add("@term", SqlDbType.Date, 15, "term");
sqlServer.sqlCommandUpdate.Parameters.Add("@id", SqlDbType.Int, 10, "id");
sqlServer.sqlCommandDelete.CommandText = "DELETE FROM " + PriceName + " WHERE ([id] = @id)";
sqlServer.sqlCommandDelete.Parameters.Add("@id", SqlDbType.Int, 10, "id").SourceVersion = DataRowVersion.Original;
if (sqlServer.ExecuteUpdate(PriceName))
{
Utilits.Console.Log("Прайс " + PriceName + " был успешно сохранён.");
Close();
}
else
{
sqlServer.Error();
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + PriceName + " не удалось сохранить.");
}
}
}
/* Выполнение */
void ExecuteOleDb()
{
OrderDoc orderDoc;
String thisIsOrderUpdate;
Double sum = 0;
Double amount = 0;
Double price = 0;
Double vat = 0;
Double total = 0;
String report;
report = "Процесс создания Заказов - запушен!";
try{
/* Обход прайсов */
foreach (Price pl in priceList)
{
sum = 0;
amount = 0;
price = 0;
vat = 0;
total = 0;
/* Создание основной информации документа заказ */
orderDoc = new OrderDoc();
orderDoc.docDate = DateTime.Today.Date;
orderDoc.docNumber = createDocNumber();
orderDoc.docName = "Заказ";
orderDoc.docCounteragent = pl.counteragentName;
orderDoc.docAutor = DataConfig.userName;
orderDoc.docSum = 0;
orderDoc.docVat = 0;
orderDoc.docTotal = 0;
orderDoc.docPurchasePlan = docPPNumber;
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT " +
"id, nomenclatureID, nomenclatureName, units, amount, " +
"name, price, manufacturer, remainder, term, discount1, discount2, discount3, discount4, code, series, article, " +
"counteragentName, counteragentPricelist, " +
"docPurchasePlan, docOrder " +
"FROM OrderNomenclature WHERE (docPurchasePlan = '" + docPPNumber + "' AND counteragentName = '" + pl.counteragentName + "')";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE OrderNomenclature SET " +
"nomenclatureID = @nomenclatureID, nomenclatureName = @nomenclatureName, units = @units, amount = @amount, " +
"name = @name, price = @price, manufacturer = @manufacturer, remainder = @remainder, term = @term, " +
"discount1 = @discount1, discount2 = @discount2, discount3 = @discount3, discount4 = @discount4, " +
"code = @code, series = @series, article = @article, " +
"counteragentName = @counteragentName, counteragentPricelist = @counteragentPricelist, " +
"docPurchasePlan = @docPurchasePlan, docOrder = @docOrder " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@nomenclatureID", OleDbType.Integer, 10, "nomenclatureID");
oleDb.oleDbCommandUpdate.Parameters.Add("@nomenclatureName", OleDbType.VarChar, 255, "nomenclatureName");
oleDb.oleDbCommandUpdate.Parameters.Add("@units", OleDbType.VarChar, 255, "units");
oleDb.oleDbCommandUpdate.Parameters.Add("@amount", OleDbType.Double, 15, "amount");
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandUpdate.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandUpdate.Parameters.Add("@remainder", OleDbType.Double, 15, "remainder");
oleDb.oleDbCommandUpdate.Parameters.Add("@term", OleDbType.Date, 15, "term");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount1", OleDbType.Double, 15, "discount1");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount2", OleDbType.Double, 15, "discount2");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount3", OleDbType.Double, 15, "discount3");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount4", OleDbType.Double, 15, "discount4");
oleDb.oleDbCommandUpdate.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandUpdate.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandUpdate.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandUpdate.Parameters.Add("@counteragentName", OleDbType.VarChar, 255, "counteragentName");
oleDb.oleDbCommandUpdate.Parameters.Add("@counteragentPricelist", OleDbType.VarChar, 255, "counteragentPricelist");
oleDb.oleDbCommandUpdate.Parameters.Add("@docPurchasePlan", OleDbType.VarChar, 255, "docPurchasePlan");
oleDb.oleDbCommandUpdate.Parameters.Add("@docOrder", OleDbType.VarChar, 255, "docOrder");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
if (oleDb.ExecuteFill("OrderNomenclature"))
{
thisIsOrderUpdate = orderMustBeUpdated(oleDb.dataSet);
foreach (DataRow row in oleDb.dataSet.Tables["OrderNomenclature"].Rows)
{
/* Привязываем к документу */
if (thisIsOrderUpdate == "")
{
row["docOrder"] = orderDoc.docNumber;
}
else
{
row["docOrder"] = thisIsOrderUpdate;
}
/* Вычисления */
price = (Double)row["price"];
amount = (Double)row["amount"];
sum += (price * amount);
}
/* Итоги вычислений */
sum = Math.Round(sum, 2);
vat = sum * DataConstants.ConstFirmVAT / 100;
vat = Math.Round(vat, 2);
total = sum + vat;
total = Math.Round(total, 2);
orderDoc.docSum = sum;
orderDoc.docVat = vat;
orderDoc.docTotal = total;
if (thisIsOrderUpdate == "") // Создаём новый заказ
/* Сохранение основных данных документа Заказ */
{
oleDbQuery = new QueryOleDb(DataConfig.localDatabase);
oleDbQuery.SetCommand("INSERT INTO Orders " +
"(docDate, docNumber, docName, docCounteragent, " +
"docAutor, docSum, docVat, docTotal, docPurchasePlan) " +
"VALUES ('" + orderDoc.docDate + "', " +
"'" + orderDoc.docNumber + "', " +
"'" + orderDoc.docName + "', " +
"'" + orderDoc.docCounteragent + "', " +
"'" + orderDoc.docAutor + "', " +
"" + Conversion.DoubleToString(orderDoc.docSum) + ", " +
"" + Conversion.DoubleToString(orderDoc.docVat) + ", " +
"" + Conversion.DoubleToString(orderDoc.docTotal) + ", " +
"'" + orderDoc.docPurchasePlan + "')");
if (oleDbQuery.Execute())
{
report += Environment.NewLine;
report += "Документ Заказ №" + orderDoc.docNumber + " - создан!";
if (oleDb.ExecuteUpdate("OrderNomenclature"))
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - обновлён!";
}
else
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - ошибка обновления!";
}
}
else
{
report += Environment.NewLine;
report += "Документ Заказ №" + orderDoc.docNumber + " - ошибка создания!";
}
}
else // Обновляем данные в заказе
{
if (oleDb.ExecuteUpdate("OrderNomenclature"))
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - обновлён!";
/* Перерасчет Заказа */
oleDbQuery = new QueryOleDb(DataConfig.localDatabase);
oleDbQuery.SetCommand("UPDATE Orders SET " +
"docSum = " + Conversion.DoubleToString(orderDoc.docSum) + ", " +
"docVat = " + Conversion.DoubleToString(orderDoc.docVat) + ", " +
"docTotal = " + Conversion.DoubleToString(orderDoc.docTotal) + " " +
"WHERE ([docNumber] = '" + thisIsOrderUpdate + "')");
if (oleDbQuery.Execute())
{
report += Environment.NewLine;
report += "Документ Заказ №" + thisIsOrderUpdate + " - обновлён!";
}
else
{
report += Environment.NewLine;
report += "Документ Заказ №" + thisIsOrderUpdate + " - ошибка обновления!";
}
}
else
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - ошибка обновления!";
}
}
}
else
{
MessageBox.Show("Не удалось загрузить перечень номенклатуры из документа" + docPPNumber + "" + Environment.NewLine +
"Создание Заказов на основании Плана закупок невозможно!", "Сообщение");
Dispose();
return;
}
}
DataForms.FClient.updateHistory("Orders");
/* Отчёт о проделанной работе */
Dispose();
Utilits.Console.Log("[ВВОД НА ОСНОВАНИИ]" + Environment.NewLine +
"Отчёт --------------------------------------------------------------------" +
Environment.NewLine + report + Environment.NewLine +
"------------------------------------------------------------------------------");
MessageBox.Show("Обработка Плана закупок №" + docPPNumber + " завершена!", "Сообщение");
}catch (Exception ex) {
Dispose();
Utilits.Console.Log("[ОШИБКА] " + ex.Message, false, true);
}
}
void createOrdersOleDb()
{
Double sum = 0;
Double amount = 0;
Double price = 0;
Double vat = 0;
Double total = 0;
OleDb oleDb = null;
QueryOleDb oleDbQuery = null;
OrderDoc orderDoc;
try{
oleDb = new OleDb(DataConfig.localDatabase);
foreach (Price plist in priceList) // Обход прайсов
{
sum = 0;
amount = 0;
price = 0;
vat = 0;
total = 0;
/* Создание основной информации документа заказ */
orderDoc = new OrderDoc();
orderDoc.docDate = DateTime.Today.Date;
orderDoc.docNumber = createDocNumber();
orderDoc.docName = "Заказ";
orderDoc.docCounteragent = plist.counteragentName;
orderDoc.docAutor = DataConfig.userName;
orderDoc.docSum = 0;
orderDoc.docVat = 0;
orderDoc.docTotal = 0;
orderDoc.docPurchasePlan = docPPNumber;
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT " +
"id, nomenclatureID, nomenclatureName, units, amount, " +
"name, price, manufacturer, remainder, term, discount1, discount2, discount3, discount4, code, series, article, " +
"counteragentName, counteragentPricelist, " +
"docPurchasePlan, docOrder " +
"FROM OrderNomenclature WHERE (docPurchasePlan = '" + docPPNumber + "' AND counteragentName = '" + plist.counteragentName + "')";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE OrderNomenclature SET " +
"nomenclatureID = @nomenclatureID, nomenclatureName = @nomenclatureName, units = @units, amount = @amount, " +
"name = @name, price = @price, manufacturer = @manufacturer, remainder = @remainder, term = @term, " +
"discount1 = @discount1, discount2 = @discount2, discount3 = @discount3, discount4 = @discount4, " +
"code = @code, series = @series, article = @article, " +
"counteragentName = @counteragentName, counteragentPricelist = @counteragentPricelist, " +
"docPurchasePlan = @docPurchasePlan, docOrder = @docOrder " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@nomenclatureID", OleDbType.Integer, 10, "nomenclatureID");
oleDb.oleDbCommandUpdate.Parameters.Add("@nomenclatureName", OleDbType.VarChar, 255, "nomenclatureName");
oleDb.oleDbCommandUpdate.Parameters.Add("@units", OleDbType.VarChar, 255, "units");
oleDb.oleDbCommandUpdate.Parameters.Add("@amount", OleDbType.Double, 15, "amount");
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandUpdate.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandUpdate.Parameters.Add("@remainder", OleDbType.Double, 15, "remainder");
oleDb.oleDbCommandUpdate.Parameters.Add("@term", OleDbType.Date, 15, "term");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount1", OleDbType.Double, 15, "discount1");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount2", OleDbType.Double, 15, "discount2");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount3", OleDbType.Double, 15, "discount3");
oleDb.oleDbCommandUpdate.Parameters.Add("@discount4", OleDbType.Double, 15, "discount4");
oleDb.oleDbCommandUpdate.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandUpdate.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandUpdate.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandUpdate.Parameters.Add("@counteragentName", OleDbType.VarChar, 255, "counteragentName");
oleDb.oleDbCommandUpdate.Parameters.Add("@counteragentPricelist", OleDbType.VarChar, 255, "counteragentPricelist");
oleDb.oleDbCommandUpdate.Parameters.Add("@docPurchasePlan", OleDbType.VarChar, 255, "docPurchasePlan");
oleDb.oleDbCommandUpdate.Parameters.Add("@docOrder", OleDbType.VarChar, 255, "docOrder");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
if (oleDb.ExecuteFill("OrderNomenclature")) // получаем перечень номенклатуры ПЗ
{
if (oleDb.dataSet.Tables["OrderNomenclature"].Rows.Count <= 0)
{
continue; // пропускаем (нет номенклатуры по данному контрагенту)
}
foreach (DataRow row in oleDb.dataSet.Tables["OrderNomenclature"].Rows)
{
/* Привязываем к документу */
row["docOrder"] = orderDoc.docNumber;
/* Вычисления */
price = (Double)row["price"];
amount = (Double)row["amount"];
sum += (price * amount);
}
/* Итоги вычислений */
sum = Math.Round(sum, 2);
vat = sum * DataConstants.ConstFirmVAT / 100;
vat = Math.Round(vat, 2);
total = sum + vat;
total = Math.Round(total, 2);
orderDoc.docSum = sum;
orderDoc.docVat = vat;
orderDoc.docTotal = total;
/* Создаём новый заказ */
oleDbQuery = new QueryOleDb(DataConfig.localDatabase);
oleDbQuery.SetCommand("INSERT INTO Orders " +
"(docDate, docNumber, docName, docCounteragent, " +
"docAutor, docSum, docVat, docTotal, docPurchasePlan) " +
"VALUES ('" + orderDoc.docDate + "', " +
"'" + orderDoc.docNumber + "', " +
"'" + orderDoc.docName + "', " +
"'" + orderDoc.docCounteragent + "', " +
"'" + orderDoc.docAutor + "', " +
"" + Conversion.DoubleToString(orderDoc.docSum) + ", " +
"" + Conversion.DoubleToString(orderDoc.docVat) + ", " +
"" + Conversion.DoubleToString(orderDoc.docTotal) + ", " +
"'" + orderDoc.docPurchasePlan + "')");
if (oleDbQuery.Execute())
{
/* Обновляем журнал Заказов */
DataForms.FClient.updateHistory("Orders");
Utilits.Console.Log("Ввод на основании: создан Заказ №" + orderDoc.docNumber + " для План закупок №" + docPPNumber);
/* Обновление номенклатуры ПЗ (добавляем номер документа Заказ) */
if (oleDb.ExecuteUpdate("OrderNomenclature"))
{
Utilits.Console.Log("Ввод на основании: План заказов №" + docPPNumber + " обновлён.");
}
else
{
if (oleDb != null)
{
oleDb.Dispose();
}
if (oleDbQuery != null)
{
oleDbQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: План закупок №" + docPPNumber + " не удалось одновить! Заказ №" + orderDoc.docNumber, false, true);
MessageBox.Show("Не удалось обновить План закупок №" + docPPNumber + " Создание заказов прервано!", "Сообщение");
return;
}
}
else
{
if (oleDb != null)
{
oleDb.Dispose();
}
if (oleDbQuery != null)
{
oleDbQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: Не удалось создать Заказ для План закупок №" + docPPNumber, false, true);
MessageBox.Show("Не удалось создать Заказ для План закупок №" + docPPNumber, "Сообщение");
return;
}
}
else
{
if (oleDb != null)
{
oleDb.Dispose();
}
if (oleDbQuery != null)
{
oleDbQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: Не удалось загрузить перечень номенклатуры из документа" + docPPNumber, false, true);
MessageBox.Show("Не удалось загрузить перечень номенклатуры из документа" + docPPNumber + "" + Environment.NewLine +
"Создание Заказов на основании Плана закупок невозможно!", "Сообщение");
return;
}
}
}catch (Exception ex) {
if (oleDb != null)
{
oleDb.Dispose();
}
if (oleDbQuery != null)
{
oleDbQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: " + ex.Message, false, true);
}
if (oleDb != null)
{
oleDb.Dispose();
}
if (oleDbQuery != null)
{
oleDbQuery.Dispose();
}
MessageBox.Show("План закупок №" + docPPNumber + " был успешно обработан!" + Environment.NewLine + "Заказы созданы в соответствии с выбранными прайс-листами и номенклатурой! ", "Сообщение");
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = 0)";
oleDb.ExecuteFill("Nomenclature");
DataRow newRow;
int colsCount = dataSet.Tables[0].Columns.Count;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
newRow = oleDb.dataSet.Tables["Nomenclature"].NewRow();
if (colsCount > 0)
{
newRow["name"] = row[0];
}
if (colsCount > 1)
{
newRow["code"] = row[1];
}
if (colsCount > 2)
{
newRow["series"] = row[2];
}
if (colsCount > 3)
{
newRow["article"] = row[3];
}
if (colsCount > 4)
{
newRow["manufacturer"] = row[4];
}
if (colsCount > 5)
{
if (row[5].ToString() == "")
{
newRow["price"] = 0;
}
else
{
newRow["price"] = row[5];
}
}
if (colsCount > 6)
{
newRow["units"] = row[6];
}
newRow["type"] = DataConstants.FILE;
newRow["parent"] = ParentFolder;
oleDb.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
}
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Nomenclature " +
"(name, type, code, series, article, manufacturer, price, units, parent) " +
"VALUES (@name, @type, @code, @series, @article, @manufacturer, @price, @units, @parent)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandInsert.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandInsert.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandInsert.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandInsert.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandInsert.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandInsert.Parameters.Add("@units", OleDbType.VarChar, 255, "units");
oleDb.oleDbCommandInsert.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
if (oleDb.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Номенклатура успешно загружена из Excel файла.");
Close();
}
else
{
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Ошибка загрузки данных из Excel файла.");
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = 0)";
sqlServer.ExecuteFill("Nomenclature");
DataRow newRow;
int colsCount = dataSet.Tables[0].Columns.Count;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
newRow = sqlServer.dataSet.Tables["Nomenclature"].NewRow();
if (colsCount > 0)
{
newRow["name"] = row[0];
}
if (colsCount > 1)
{
newRow["code"] = row[1];
}
if (colsCount > 2)
{
newRow["series"] = row[2];
}
if (colsCount > 3)
{
newRow["article"] = row[3];
}
if (colsCount > 4)
{
newRow["manufacturer"] = row[4];
}
if (colsCount > 5)
{
if (row[5].ToString() == "")
{
newRow["price"] = 0;
}
else
{
newRow["price"] = row[5];
}
}
if (colsCount > 6)
{
newRow["units"] = row[6];
}
newRow["type"] = DataConstants.FILE;
newRow["parent"] = ParentFolder;
sqlServer.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
}
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Nomenclature " +
"(name, type, code, series, article, manufacturer, price, units, parent) " +
"VALUES (@name, @type, @code, @series, @article, @manufacturer, @price, @units, @parent)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@type", SqlDbType.VarChar, 255, "type");
sqlServer.sqlCommandInsert.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandInsert.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandInsert.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandInsert.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandInsert.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandInsert.Parameters.Add("@units", SqlDbType.VarChar, 255, "units");
sqlServer.sqlCommandInsert.Parameters.Add("@parent", SqlDbType.VarChar, 255, "parent");
if (sqlServer.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Номенклатура успешно загружена из Excel файла.");
Close();
}
else
{
sqlServer.Error();
Utilits.Console.Log("[ОШИБКА] Ошибка загрузки данных из Excel файла.");
}
}
}
/* Отметить обновление данных в базе данных */
public void update(int index)
{
oleDb.dataSet.Tables["History"].Rows[index]["datetime"] = DateTime.Now.ToString();
oleDb.ExecuteUpdate("History");
}
void saveEditFile()
{
/* Изменение теста */
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice WHERE (ID = " + ID + ")";
oleDb.ExecuteFill("Practice");
oleDb.oleDbCommandUpdate.CommandText = "UPDATE Practice SET " +
"[type] = @type, [name] = @name, [parent] = @parent " +
"WHERE ([ID] = @ID)";
oleDb.oleDbCommandUpdate.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
oleDb.oleDbCommandUpdate.Parameters.Add("@ID", OleDbType.Integer, 10, "ID");
oleDb.dataSet.Tables["Practice"].Rows[0]["name"] = textBox2.Text;
oleDb.dataSet.Tables["Practice"].Rows[0]["parent"] = comboBox1.Text;
if (oleDb.ExecuteUpdate("Practice"))
{
// Редактирование записей в тесте
if (idDeletedList.Count > 0) // Удаление записей
{
foreach (int deleteId in idDeletedList)
{
QueryOleDb query = new QueryOleDb(Config.databaseFile);
query.SetCommand("DELETE FROM Tests WHERE (ID = " + deleteId + ")");
query.Execute();
}
}
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Tests";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Tests WHERE (IDPractice = " + textBox1.Text + ") ORDER BY ID ASC";
oleDb.ExecuteFill("Tests");
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Tests (IDPractice, condition, question, answer, hint) VALUES (@IDPractice, @condition, @question, @answer, @hint)";
oleDb.oleDbCommandInsert.Parameters.Add("@IDPractice", OleDbType.Integer, 10, "IDPractice");
oleDb.oleDbCommandInsert.Parameters.Add("@condition", OleDbType.VarChar, 255, "condition");
oleDb.oleDbCommandInsert.Parameters.Add("@question", OleDbType.VarChar, 255, "question");
oleDb.oleDbCommandInsert.Parameters.Add("@answer", OleDbType.VarChar, 255, "answer");
oleDb.oleDbCommandInsert.Parameters.Add("@hint", OleDbType.VarChar, 255, "hint");
oleDb.oleDbCommandUpdate.CommandText = "UPDATE Tests SET " +
"[IDPractice] = @IDPractice, [condition] = @condition, " +
"[question] = @question, [answer] = @answer, [hint] = @hint " +
"WHERE ([ID] = @ID)";
oleDb.oleDbCommandUpdate.Parameters.Add("@IDPractice", OleDbType.Integer, 10, "IDPractice");
oleDb.oleDbCommandUpdate.Parameters.Add("@condition", OleDbType.VarChar, 255, "condition");
oleDb.oleDbCommandUpdate.Parameters.Add("@question", OleDbType.VarChar, 255, "question");
oleDb.oleDbCommandUpdate.Parameters.Add("@answer", OleDbType.VarChar, 255, "answer");
oleDb.oleDbCommandUpdate.Parameters.Add("@hint", OleDbType.VarChar, 255, "hint");
oleDb.oleDbCommandUpdate.Parameters.Add("@ID", OleDbType.Integer, 10, "ID");
DataRow newRow;
foreach (ListViewItem value in listView1.Items)
{
if (value.SubItems[5].Text == "+")
{
newRow = null;
newRow = oleDb.dataSet.Tables["Tests"].NewRow();
newRow["IDPractice"] = textBox1.Text;
newRow["condition"] = value.SubItems[1].Text;
newRow["question"] = value.SubItems[2].Text;
newRow["answer"] = value.SubItems[3].Text;
newRow["hint"] = value.SubItems[4].Text;
oleDb.dataSet.Tables["Tests"].Rows.Add(newRow);
}
else
{
foreach (DataRow row in oleDb.dataSet.Tables["Tests"].Rows)
{
if (row["ID"].ToString() == value.SubItems[5].Text.ToString())
{
row["condition"] = value.SubItems[1].Text;
row["question"] = value.SubItems[2].Text;
row["answer"] = value.SubItems[3].Text;
row["hint"] = value.SubItems[4].Text;
break;
}
}
}
}
if (oleDb.ExecuteUpdate("Tests"))
{
Utilits.Console.Log("Изменения в тесте '" + comboBox1.Text + "\\" + textBox2.Text + "' - успешно сохранены.");
Close();
(ParentForm as PracticeForm).TableRefresh();
}
else
{
Utilits.Console.LogError("Не удалось записать изменённые пункты теста '" + textBox2.Text + "'");
}
}
else
{
Utilits.Console.LogError("Не удалось сохранить изменения в тесте '" + oldName + "'");
}
}
void saveNewFile()
{
/* Создание теста */
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice";
oleDb.ExecuteFill("Practice");
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Practice (type, name, parent) VALUES (@type, @name, @parent)";
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
DataRow newRow = oleDb.dataSet.Tables["Practice"].NewRow();
newRow["type"] = Constants.TYPE_FILE;
newRow["name"] = textBox2.Text;
newRow["parent"] = comboBox1.Text;
oleDb.dataSet.Tables["Practice"].Rows.Add(newRow);
if (oleDb.ExecuteUpdate("Practice"))
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice WHERE (name = '" + textBox2.Text + "')";
oleDb.ExecuteFill("Practice");
int idPractice = Convert.ToInt32(oleDb.dataSet.Tables["Practice"].Rows[0]["ID"].ToString());
/* Добавление записей в тест */
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Tests";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Tests";
oleDb.ExecuteFill("Tests");
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Tests (IDPractice, condition, question, answer, hint) VALUES (@IDPractice, @condition, @question, @answer, @hint)";
oleDb.oleDbCommandInsert.Parameters.Add("@IDPractice", OleDbType.Integer, 10, "IDPractice");
oleDb.oleDbCommandInsert.Parameters.Add("@condition", OleDbType.VarChar, 255, "condition");
oleDb.oleDbCommandInsert.Parameters.Add("@question", OleDbType.VarChar, 255, "question");
oleDb.oleDbCommandInsert.Parameters.Add("@answer", OleDbType.VarChar, 255, "answer");
oleDb.oleDbCommandInsert.Parameters.Add("@hint", OleDbType.VarChar, 255, "hint");
newRow = null;
foreach (ListViewItem value in listView1.Items)
{
newRow = oleDb.dataSet.Tables["Tests"].NewRow();
newRow["IDPractice"] = idPractice;
newRow["condition"] = value.SubItems[1].Text;
newRow["question"] = value.SubItems[2].Text;
newRow["answer"] = value.SubItems[3].Text;
newRow["hint"] = value.SubItems[4].Text;
oleDb.dataSet.Tables["Tests"].Rows.Add(newRow);
}
if (oleDb.ExecuteUpdate("Tests"))
{
Utilits.Console.Log("Тест '" + comboBox1.Text + "\\" + textBox2.Text + "' - успешно создан.");
Close();
(ParentForm as PracticeForm).TableRefresh();
}
else
{
Utilits.Console.LogError("Не удалось записать пункты теста '" + textBox2.Text + "'");
}
}
else
{
Utilits.Console.LogError("Не удалось создать новый тест '" + textBox2.Text + "'");
}
}
void saveEdit()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = " + ID + ")";
oleDb.ExecuteFill("Nomenclature");
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["name"] = nameTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["type"] = DataConstants.FILE;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["code"] = codeTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["series"] = seriesTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["article"] = articleTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["manufacturer"] = manufacturerTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["price"] = priceTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["units"] = unitsTextBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows[0]["parent"] = foldersComboBox.Text;
oleDb.oleDbCommandUpdate.CommandText = "UPDATE Nomenclature SET " +
"[name] = @name, [type] = @type, " +
"[code] = @code, [series] = @series, [article] = @article, [manufacturer] = @manufacturer," +
"[price] = @price, [units] = @units, [parent] = @parent " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandUpdate.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandUpdate.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandUpdate.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandUpdate.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandUpdate.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandUpdate.Parameters.Add("@units", OleDbType.VarChar, 255, "units");
oleDb.oleDbCommandUpdate.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
if (oleDb.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Номенклатура успешно изменена.");
Close();
}
else
{
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Ошибка изменения номенклатуры.");
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = " + ID + ")";
sqlServer.ExecuteFill("Nomenclature");
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["name"] = nameTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["type"] = DataConstants.FILE;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["code"] = codeTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["series"] = seriesTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["article"] = articleTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["manufacturer"] = manufacturerTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["price"] = priceTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["units"] = unitsTextBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["parent"] = foldersComboBox.Text;
sqlServer.sqlCommandUpdate.CommandText = "UPDATE Nomenclature SET " +
"[name] = @name, [type] = @type, " +
"[code] = @code, [series] = @series, [article] = @article, [manufacturer] = @manufacturer," +
"[price] = @price, [units] = @units, [parent] = @parent " +
"WHERE ([id] = @id)";
sqlServer.sqlCommandUpdate.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandUpdate.Parameters.Add("@type", SqlDbType.VarChar, 255, "type");
sqlServer.sqlCommandUpdate.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandUpdate.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandUpdate.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandUpdate.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandUpdate.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandUpdate.Parameters.Add("@units", SqlDbType.VarChar, 255, "units");
sqlServer.sqlCommandUpdate.Parameters.Add("@parent", SqlDbType.VarChar, 255, "parent");
sqlServer.sqlCommandUpdate.Parameters.Add("@id", SqlDbType.Int, 10, "id");
if (sqlServer.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Номенклатура успешно изменена.");
Close();
}
else
{
sqlServer.Error();
Utilits.Console.Log("[ОШИБКА] Ошибка изменения номенклатуры.");
}
}
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = 0)";
oleDb.ExecuteFill("Nomenclature");
DataRow newRow = oleDb.dataSet.Tables["Nomenclature"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["type"] = DataConstants.FILE;
newRow["code"] = codeTextBox.Text;
newRow["series"] = seriesTextBox.Text;
newRow["article"] = articleTextBox.Text;
newRow["manufacturer"] = manufacturerTextBox.Text;
newRow["price"] = priceTextBox.Text;
newRow["units"] = unitsTextBox.Text;
newRow["parent"] = foldersComboBox.Text;
oleDb.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Nomenclature " +
"(name, type, code, series, article, manufacturer, price, units, parent) " +
"VALUES (@name, @type, @code, @series, @article, @manufacturer, @price, @units, @parent)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandInsert.Parameters.Add("@code", OleDbType.VarChar, 255, "code");
oleDb.oleDbCommandInsert.Parameters.Add("@series", OleDbType.VarChar, 255, "series");
oleDb.oleDbCommandInsert.Parameters.Add("@article", OleDbType.VarChar, 255, "article");
oleDb.oleDbCommandInsert.Parameters.Add("@manufacturer", OleDbType.VarChar, 255, "manufacturer");
oleDb.oleDbCommandInsert.Parameters.Add("@price", OleDbType.Double, 15, "price");
oleDb.oleDbCommandInsert.Parameters.Add("@units", OleDbType.VarChar, 255, "units");
oleDb.oleDbCommandInsert.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
if (oleDb.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Создана новая номенклатура.");
Close();
}
else
{
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Ошибка создания новой номенклатуры.");
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = 0)";
sqlServer.ExecuteFill("Nomenclature");
DataRow newRow = sqlServer.dataSet.Tables["Nomenclature"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["type"] = DataConstants.FILE;
newRow["code"] = codeTextBox.Text;
newRow["series"] = seriesTextBox.Text;
newRow["article"] = articleTextBox.Text;
newRow["manufacturer"] = manufacturerTextBox.Text;
newRow["price"] = priceTextBox.Text;
newRow["units"] = unitsTextBox.Text;
newRow["parent"] = foldersComboBox.Text;
sqlServer.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Nomenclature " +
"(name, type, code, series, article, manufacturer, price, units, parent) " +
"VALUES (@name, @type, @code, @series, @article, @manufacturer, @price, @units, @parent)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@type", SqlDbType.VarChar, 255, "type");
sqlServer.sqlCommandInsert.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandInsert.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandInsert.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandInsert.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandInsert.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandInsert.Parameters.Add("@units", SqlDbType.VarChar, 255, "units");
sqlServer.sqlCommandInsert.Parameters.Add("@parent", SqlDbType.VarChar, 255, "parent");
if (sqlServer.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Создана новая номенклатура.");
Close();
}
else
{
sqlServer.Error();
Utilits.Console.Log("[ОШИБКА] Ошибка создания новой номенклатуры.");
}
}
}