/* Выполняем удаление выбранных элементов */
void removeInTable(String _tableName, String _value)
{
if(_tableName == "Фирмы") _deleteMySQL.SqlCommand = "DELETE FROM firms WHERE (firm_name = '" + _value + "' AND firm_delete = 1)";
if(_tableName == "ТМЦ") _deleteMySQL.SqlCommand = "DELETE FROM tmc WHERE (tmc_name = '" + _value + "' AND tmc_delete = 1)";
if(_tableName == "Склады") _deleteMySQL.SqlCommand = "DELETE FROM store WHERE (store_name = '" + _value + "' AND store_delete = 1)";
if(_tableName == "Ед.Изм") _deleteMySQL.SqlCommand = "DELETE FROM units WHERE (units_name = '" + _value + "' AND units_delete = 1)";
if(_tableName == "Вид налога") _deleteMySQL.SqlCommand = "DELETE FROM typeTax WHERE (typeTax_name = '" + _value + "' AND typeTax_delete = 1)";
if(_tableName == "Сотрудники") _deleteMySQL.SqlCommand = "DELETE FROM staff WHERE (staff_name = '" + _value + "' AND staff_delete = 1)";
if(_tableName == "План счетов") _deleteMySQL.SqlCommand = "DELETE FROM planAccounts WHERE (planAccounts_name = '" + _value + "' AND planAccounts_delete = 1)";
if(_tableName == "Документ") {
ClassMySQL_Full _removeDocMySQL = new ClassMySQL_Full();
DataSet _removeDocDataSet = new DataSet();
_removeDocMySQL.SelectSqlCommand = "SELECT * FROM journal WHERE (journal_number = '" + _value + "' AND journal_delete = 1)";
_removeDocDataSet.Clear();
_removeDocDataSet.DataSetName = "journal";
if(_removeDocMySQL.ExecuteFill(_removeDocDataSet, "journal")){
_deleteMySQL.SqlCommand = "DELETE FROM tabularSection WHERE (tabularSection_id_doc = '" + _removeDocDataSet.Tables["journal"].Rows[0]["journal_id_doc"].ToString() + "')";
if(_deleteMySQL.ExecuteNonQuery())
{
_deleteMySQL.SqlCommand = "DELETE FROM journal WHERE (journal_number = '" + _value + "' AND journal_delete = 1)";
if(_deleteMySQL.ExecuteNonQuery())
{
listBox1.Items.Add("[" + _tableName + "] " + _value + " - - - Запись удалена!");
}
}
}
}else{
if(_deleteMySQL.ExecuteNonQuery())
{
listBox1.Items.Add("[" + _tableName + "] " + _value + " - - - Запись удалена!");
}
}
}
/*Возвращает значение выбранной константы */
public static String constantValue(String constName)
{
ClassMySQL_Full _constMySQL = new ClassMySQL_Full();
DataSet _constDataSet = new DataSet();
_constDataSet.Clear();
_constDataSet.DataSetName = "constants";
_constMySQL.SelectSqlCommand = "SElECT * FROM constants WHERE (const_name = '" + constName + "')";
if(_constMySQL.ExecuteFill(_constDataSet, "constants")){
DataTable table = _constDataSet.Tables["constants"];
return table.Rows[0]["const_value"].ToString();
}else return "";
}
/*public ClassCalculation()
{
}*/
/* Вычисление НДС */
public static String NDS(String _ndsName, String _sum)
{
double _nds;
ClassMySQL_Full typetaxMySQL = new ClassMySQL_Full();
DataSet typetaxDataSet = new DataSet();
typetaxDataSet.Clear();
typetaxDataSet.DataSetName = " typetax";
typetaxMySQL.SelectSqlCommand = "SELECT * FROM typetax WHERE (typeTax_name = '" + _ndsName + "')";
if(typetaxMySQL.ExecuteFill(typetaxDataSet, "typetax")){
DataTable table = typetaxDataSet.Tables["typetax"];
if(table.Rows.Count > 0){
// НДС (в %) = Сумма без НДС * Ставка НДС / 100
_nds = ClassConversion.StringToDouble(_sum) * ClassConversion.StringToDouble(table.Rows[0]["typeTax_rating"].ToString()) / 100.00;
_nds = Math.Round(_nds, 2);
return ClassConversion.StringToMoney(_nds.ToString());
} else return "0.00";
}else ClassForms.Rapid_Client.MessageConsole("Заказ: Ошибка получения ставки НДС при вычислении.", true);
return "0.00";
}
/* Изменение Всего с НДС */
public static String ChangeTotal_ReturnNDS(String _total, String _ndsName)
{
ClassMySQL_Full typetaxMySQL = new ClassMySQL_Full();
DataSet typetaxDataSet = new DataSet();
typetaxDataSet.Clear();
typetaxDataSet.DataSetName = " typetax";
typetaxMySQL.SelectSqlCommand = "SELECT * FROM typetax WHERE (typeTax_name = '" + _ndsName + "')";
if(typetaxMySQL.ExecuteFill(typetaxDataSet, "typetax")){
DataTable table = typetaxDataSet.Tables["typetax"];
if(table.Rows.Count > 0){
if(ClassConversion.StringToDouble(table.Rows[0]["typeTax_rating"].ToString()) > 0)
{
double _nds;
// НДС = Всего с НДС / 6
_nds = ClassConversion.StringToDouble(_total) / 6;
_nds = Math.Round(_nds, 2);
return ClassConversion.StringToMoney(_nds.ToString());
} else return "0.00";
} else return "0.00";
}else ClassForms.Rapid_Client.MessageConsole("Заказ: Ошибка получения ставки НДС при вычислении.", true);
return "0.00";
}
/* Уменьшение остатков */
public static void BalanceMinus(DataSet ResourceDS)
{
ClassMySQL_Full _mySql = new ClassMySQL_Full();
DataSet _dataSet = new DataSet();
_dataSet.Clear();
_dataSet.DataSetName = "balance";
_mySql.SelectSqlCommand = "SELECT id_balance, balance_tmc, balance_date, balance_number FROM balance";
_mySql.InsertSqlCommand = "INSERT INTO balance (balance_tmc, balance_date, balance_number) VALUE (@balance_tmc, @balance_date, @balance_number)";
_mySql.InsertParametersAdd("@balance_tmc", MySqlDbType.VarChar, 250, "balance_tmc", UpdateRowSource.None);
_mySql.InsertParametersAdd("@balance_date", MySqlDbType.Date, 10, "balance_date", UpdateRowSource.None);
_mySql.InsertParametersAdd("@balance_number", MySqlDbType.Double, 10, "balance_number", UpdateRowSource.None);
_mySql.UpdateSqlCommand = "UPDATE balance SET balance_tmc = @balance_tmc, balance_date = @balance_date, balance_number = @balance_number WHERE (id_balance = @id_balance)";
_mySql.UpdateParametersAdd("@balance_tmc", MySqlDbType.VarChar, 250, "balance_tmc", UpdateRowSource.None);
_mySql.UpdateParametersAdd("@balance_date", MySqlDbType.Date, 10, "balance_date", UpdateRowSource.None);
_mySql.UpdateParametersAdd("@balance_number", MySqlDbType.Double, 10, "balance_number", UpdateRowSource.None);
_mySql.UpdateParametersAdd("@id_balance", MySqlDbType.Int16, 11, "id_balance", UpdateRowSource.None);
_mySql.DeleteSqlCommand = "DELETE FROM balance WHERE (id_balance = @id_balance)";
_mySql.DeleteParametersAdd("@id_balance", MySqlDbType.Int16, 11, "id_balance", UpdateRowSource.None);
if(_mySql.ExecuteFill(_dataSet, "balance")){
// Ввод остатков
foreach (DataRow rowTS in ResourceDS.Tables["tabularsection"].Rows)
{
foreach (DataRow rowBalance in _dataSet.Tables["balance"].Rows){
if(rowTS["tabularSection_tmc"].ToString() == rowBalance["balance_tmc"].ToString()){
double sum = ClassConversion.StringToDouble(rowBalance["balance_number"].ToString()) - ClassConversion.StringToDouble(rowTS["tabularSection_number"].ToString());
rowBalance["balance_number"] = sum;
}
}
}
if(_mySql.ExecuteUpdate(_dataSet, "balance")){
ClassForms.Rapid_Client.MessageConsole("Остатки: Успешное обновление остатков.", false);
}else ClassForms.Rapid_Client.MessageConsole("Остатки: Ошибка ввод и сохранения новых остатков.", true);
} else ClassForms.Rapid_Client.MessageConsole("Остатки: Ошибка обращения к остаткам.", true);
}
/* Загрузка данных из таблицы фирмы*/
void FirmSellerDataLoad(String firmName)
{
ClassMySQL_Full firmMySQL = new ClassMySQL_Full();
DataSet firmDataSet = new DataSet();
firmDataSet.Clear();
firmDataSet.DataSetName = "firms";
firmMySQL.SelectSqlCommand = "SELECT * FROM firms WHERE (firm_name = '" + firmName + "')";
if(firmMySQL.ExecuteFill(firmDataSet, "firms")){
DataTable table = firmDataSet.Tables["firms"];
if(table.Rows.Count > 0) textBox4.Text = table.Rows[0]["firm_details"].ToString() + System.Environment.NewLine + "Адрес и телефон:" + System.Environment.NewLine + table.Rows[0]["firm_address_phone"].ToString();
}else ClassForms.Rapid_Client.MessageConsole("Приходная Накладная: Ошибка при загрузке данных о фирме.", true);
}
void FormAdminUserEditLoad(object sender, EventArgs e)
{
//Создание новой записи
if(this.Text == "Создать"){
//...
}
//Изменение записи
if(this.Text == "Редактировать"){
// Загрузка данных из базы данных
ClassMySQL_Full UsersMySQL = new ClassMySQL_Full();
DataSet UsersDataSet = new DataSet();
UsersDataSet.Clear();
UsersDataSet.DataSetName = "users";
UsersMySQL.SelectSqlCommand = "SELECT * FROM users WHERE (id_user = "******")";
UsersMySQL.ExecuteFill(UsersDataSet, "users");
DataTable table = UsersDataSet.Tables["users"];
DataRow row = table.Rows[0];
textBox1.Text = row["user_name"].ToString();
comboBox1.Text = row["user_right"].ToString();
textBox2.Text = row["user_pass"].ToString();
textBox3.Text = textBox2.Text;
richTextBox1.Text = row["user_additionally"].ToString();
}
}
/* Загрузка данных из таблицы фирмы*/
void TmcDataLoad(String tmcName)
{
ClassMySQL_Full tmcMySQL = new ClassMySQL_Full();
DataSet tmcDataSet = new DataSet();
tmcDataSet.Clear();
tmcDataSet.DataSetName = "tmc";
tmcMySQL.SelectSqlCommand = "SELECT * FROM tmc WHERE (tmc_name = '" + tmcName + "')";
if(tmcMySQL.ExecuteFill(tmcDataSet, "tmc")){
DataTable table = tmcDataSet.Tables["tmc"];
if(table.Rows.Count > 0){
// Наименование ТМЦ
textBox2.Text = table.Rows[0]["tmc_units"].ToString();
// Количество ТМЦ на складе
textBox3.Text = "1.00";
label9.Text = "Остаток на складе: " + ClassBalance.BalanceShow(textBox1.Text, ActualDate) + " ";
// Цена (покупка или продажа)
if(BuyOrSell)textBox4.Text = ClassConversion.StringToMoney(table.Rows[0]["tmc_buy"].ToString());
else textBox4.Text = ClassConversion.StringToMoney(table.Rows[0]["tmc_sale"].ToString());
// Вид НДС
textBox5.Text = table.Rows[0]["tmc_type_tax"].ToString();
//Вычисление
Calculation();
}else{
//ТМЦ не найден
textBox2.Clear();
textBox3.Text = "0.00";
textBox4.Text = "0.00";
textBox5.Text = ClassSelectConst.constantValue("Вид НДС");
textBox6.Text = "0.00";
textBox7.Text = "0.00";
textBox8.Text = "0.00";
}
}else ClassForms.Rapid_Client.MessageConsole("Заказ: Ошибка при загрузке данных о тмц.", true);
}
/* Показать остаток на складе */
public static String BalanceShow(String _tmcName, String _actualDate)
{
ClassMySQL_Full balanceMySQL = new ClassMySQL_Full();
DataSet balanceDataSet = new DataSet();
balanceDataSet.Clear();
balanceDataSet.DataSetName = "balance";
balanceMySQL.SelectSqlCommand = "SELECT * FROM balance WHERE (balance_tmc = '" + _tmcName + "' AND balance_date <= '" + _actualDate + "')";
if(balanceMySQL.ExecuteFill(balanceDataSet, "balance")){
DataTable table = balanceDataSet.Tables["balance"];
if(table.Rows.Count > 0){
return table.Rows[0]["balance_number"].ToString();
} else return "--";
} else return "--";
}
/* Остатки после удаления документа */
public static void BalanceRemoval(String docID, bool actionPlus)
{
ClassMySQL_Full _mySql = new ClassMySQL_Full();
DataSet _dataSet = new DataSet();
_dataSet.Clear();
_dataSet.DataSetName = "tabularsection";
_mySql.SelectSqlCommand = "SELECT tabularsection.*, journal.* FROM tabularsection, journal WHERE (journal.id_journal = " + docID + " AND tabularsection.tabularSection_id_doc = journal.journal_id_doc)";
if(_mySql.ExecuteFill(_dataSet, "tabularsection")){
if(actionPlus) BalancePlus(_dataSet);
else BalanceMinus(_dataSet);
} else ClassForms.Rapid_Client.MessageConsole("Остатки: Ошибка обращения к табличной части документа.", true);
}