private void btn_Insert_Click(object sender, EventArgs e)
{
if (listBox1.Text != "")
{
int rows;
if (int.TryParse(textBox2.Text, out rows))
{
//начать загрузку
if (textBox1.Text == "")
textBox1.Text = "1";
_range = "A" + (Convert.ToInt32(textBox1.Text) - 1).ToString() + ":V" + textBox2.Text;
OdbcConnection cn = new OdbcConnection();
cn.ConnectionString = string.Format(@"Driver={{Microsoft Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", label1.Text);
string strCom = "select * from [" + _sheet + "$" + _range + "]";
cn.Open();
OdbcCommand comm_mon = new OdbcCommand(strCom, cn);
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = comm_mon;
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
DataSaver ds = new DataSaver(login_, password_);
string Warning = "";
Warning = ds.Save(dt, Convert.ToInt32(textBox1.Text));
ContractsFill();
if (Warning != "")
MessageBox.Show(Warning);
}
else
{
MessageBox.Show("Укажите количество строк");
}
}
}
private void btn_KSSS_load_Click(object sender, EventArgs e)
{
if (listBox_KSSS.Text != "")
{
string Warning = "";
int rows;
if (int.TryParse(tb_KSSS_lastRow.Text, out rows) && (Convert.ToInt32(tb_KSSS_lastRow.Text) - Convert.ToInt32(tb_KSSS_firstRow.Text)) >= 0)
{
//начать загрузку
if (tb_KSSS_firstRow.Text == "")
tb_KSSS_firstRow.Text = "1";
_range = "A" + (Convert.ToInt32(tb_KSSS_firstRow.Text) - 1).ToString() + ":D" + tb_KSSS_lastRow.Text;
OdbcConnection cn = new OdbcConnection();
cn.ConnectionString = string.Format(@"Driver={{Microsoft Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", lbl_KSSS_dateload.Text);
string strCom = "select * from [" + _sheet + "$" + _range + "]";
cn.Open();
OdbcCommand comm_mon = new OdbcCommand(strCom, cn);
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = comm_mon;
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
DataSaver ds = new DataSaver(login_, password_);
Warning = ds.SaveKSSStable(dt, Convert.ToInt32(tb_KSSS_firstRow.Text));
if (Warning != "")
MessageBox.Show(Warning);
}
else
{
MessageBox.Show("Укажите корректное количество строк!");
}
}
KSSStoContragentFill();
}
private void btn_del_actual_Click(object sender, EventArgs e)
{
int focusRow = ActualView.FocusedRowHandle;
string idRow = "";
idRow = ActualView.GetFocusedDataRow()["Idactual"].ToString();
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DeleteActualPayments(idRow);
ActualPaymentsFill();
try
{
if (focusRow != ActualView.RowCount - 1)
ActualView.FocusedRowHandle = focusRow - 1;
else
ActualView.FocusedRowHandle = focusRow;
}
catch { }
_connection.Close();
}
private void button3_Click(object sender, EventArgs e)
{
_range = "A" + (Convert.ToInt32(textBox1.Text) - 1).ToString() + ":J" + textBox2.Text;
OdbcConnection cn = new OdbcConnection();
cn.ConnectionString = string.Format(@"Driver={{Microsoft Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", label1.Text);
string strCom = "select * from [" + _sheet + "$" + _range + "]";
cn.Open();
OdbcCommand comm_mon = new OdbcCommand(strCom, cn);
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = comm_mon;
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
DataSaver ds = new DataSaver(login_, password_);
ds.SavePaymentsPlan(dt);
}
private void dtp_pay_Date_ValueChanged(object sender, EventArgs e)
{
try
{
DataSaver ds = new DataSaver(login_, password_);
tb_pay_SummRUB.Text = ds.GetRUSsumm(tb_pay_Summ.Text
, cb_pay_Currency.SelectedValue.ToString()
, dtp_pay_Date.Value.Date.ToString()
);
}
catch { }
}
private void btnAdd_Click(object sender, EventArgs e)
{
int focused_row = 0;
if (link == "INS")
{
if (tb_PFM.Text == "" || tb_finpos.Text == "" || tb_summ.Text == "")
{
lb_status.Text = "Ошибка: Заполните поля Код ПФМ, Финансовая позиция и Сумма!";
}
else
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.AddLimits
(tb_PFM.Text.Replace(" ", "")
, tb_finpos.Text.Replace(" ", "")
, tb_year.Text.Replace(" ", "")
, cb_month.SelectedValue.ToString()
, tb_summ.Text.Replace(separator_false, separator_true).Replace(" ", "")
);
}
tb_PFM.Text = "";
tb_year.Text = "";
tb_finpos.Text = "";
tb_summ.Text = "";
focused_row = limitsView.RowCount;
}
if (link == "UPD")
{
focused_row = limitsView.FocusedRowHandle;
if (tb_PFM.Text == "" || tb_finpos.Text == "" || tb_summ.Text == "")
{
lb_status.Text = "Ошибка: Заполните поля Код ПФМ, Финансовая позиция и Сумма!";
}
else
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.UpdateLimits
(tb_PFM.Text.Replace(" ", "")
, tb_finpos.Text.Replace(" ", "")
, tb_year.Text.Replace(" ", "")
, cb_month.SelectedValue.ToString()
, tb_summ.Text.Replace(separator_false, separator_true).Replace(" ", "")
,tb_id.Text
);
}
}
if (link == "DEL")
{
try
{
if (limitsView.FocusedRowHandle != limitsView.RowCount - 1)
focused_row = limitsView.FocusedRowHandle;
else
focused_row = limitsView.FocusedRowHandle - 1;
}
catch { }
string idRow = "";
idRow = limitsView.GetFocusedDataRow()["limID"].ToString();
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DeleteLimits(idRow);
_connection.Close();
}
LimitsFill();
limitsView.FocusedRowHandle = focused_row;
}
private void button8_Click(object sender, EventArgs e)
{
string curs = "";
if (tb_curs.Text != "")
curs = tb_curs.Text;
else
curs = cb_curs.SelectedValue.ToString();
if (tb_currency_rub.Text == "")
{
lb_status.Text = "Ошибка: Введите стоимость валюты!";
}
else
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.AddCurrency(dateTimePicker2.Text
, curs.Replace(" ", "")
, tb_currency_rub.Text.Replace(separator_false, separator_true).Replace(" ","")
);
}
this.v_CurrencyCursTableAdapter.Fill(this.currencyCurs1.v_CurrencyCurs);
this.v_CurrencyTableAdapter.Fill(this.formationOfScheduleDataSet2.v_Currency);
tb_currency_rub.Text = "";
tb_curs.Text = "";
}
private void tb_pay_ContragentCode_TextChanged(object sender, EventArgs e)
{
DataSaver ds = new DataSaver(login_, password_);
tb_pay_ContragentType.Text = ds.GetContragentType(tb_pay_ContragentCode.Text);
if (tb_pay_ContragentType.Text.Substring(0,26) != "Контрагента не существует!")
{
tp_pay_ContractCode.Enabled = true;
cb_pay_FinPosition.Enabled = true;
gb_pay2.Enabled = true;
link_payment_add.Enabled = true;
link_payment_del.Enabled = true;
link_payment_update.Enabled = true;
tb_pay_EPL.Enabled = true;
tb_pay_PFMcode.Enabled = true;
tb_pay_SummRUB.Enabled = true;
if(inform_[2] != "1")
button7.Enabled = true;
tb_plan_statelikvid.Enabled = true;
tb_plan_stavrolen.Enabled = true;
tb_pay_ContragentType.ForeColor = System.Drawing.Color.Black;
}
else
{
tp_pay_ContractCode.Enabled = false;
cb_pay_FinPosition.Enabled = false;
gb_pay2.Enabled = false;
link_payment_add.Enabled = false;
link_payment_del.Enabled = false;
link_payment_update.Enabled = false;
button7.Enabled = false;
tb_pay_EPL.Enabled = false;
tb_pay_PFMcode.Enabled = false;
tb_pay_SummRUB.Enabled = false;
tb_plan_statelikvid.Enabled = false;
tb_plan_stavrolen.Enabled = false;
tb_pay_ContragentType.ForeColor = System.Drawing.Color.Red;
}
}
private void btn_fp_EPL_Click(object sender, EventArgs e)
{
if (operation_fp == "INS")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.InsertFinPosition(tb_fp_FPcode.Text, tb_fp_Licvid.Text);
tb_fp_FPcode.Text = "";
tb_fp_Licvid.Text = "";
}
else if (operation_fp == "UPD")
{
try
{
// string ID = gridView12.GetFocusedDataRow()["IdFinPosition"].ToString();
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.UpdateFinPosition(FPCODE_save,tb_fp_FPcode.Text, tb_fp_Licvid.Text);
}
catch { }
}
else if (operation_fp == "DEL")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DeleteFinPosition(tb_fp_FPcode.Text, tb_fp_Licvid.Text);
}
this.v_FinPosition_EPLTableAdapter.Fill(this.formationOfScheduleDataSet5.v_FinPosition_EPL);
this.financialPositionTableAdapter.Fill(this.formationOfScheduleDataSet3.FinancialPosition);
this.v_FinancialPosition_TopicTableAdapter.Fill(this.formationOfScheduleDataSet6.v_FinancialPosition_Topic);
ContractsFill();
ActualPaymentsFill();
PlansFill();
}
public void FinPositionChenged()
{
try
{
tb_pay_finpos_copy.Text = cb_pay_FinPosition.SelectedValue.ToString();
DataSaver ds = new DataSaver(login_, password_);
tb_pay_EPL.Text = ds.GetEPL(tb_pay_finpos_copy.Text);
DataSaver ds1 = new DataSaver(login_, password_);
tb_plan_stavrolen.Text = ds1.GetFinPositonName(tb_pay_finpos_copy.Text);
}
catch { }
}
private void tb_pay_EPL_TextChanged(object sender, EventArgs e)
{
try
{
DataSaver ds = new DataSaver(login_, password_);
tb_plan_statelikvid.Text = ds.GetStateEverydayLicvid(tb_pay_EPL.Text);
}
catch { }
}
private void button7_Click(object sender, EventArgs e)
{
string answer = "";
if (dtp_pay_Date.Value == DateTime.Today)
{
string mess = "Вы уверенны, что платеж должен быть совершен сегодня?";
string caption = "Вопрос";
var result = MessageBox.Show(mess, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
answer = "Yes";
}
else answer = "No";
}
if (answer != "No")
{
// if (dtp_pay_Date.Value == DateTime.Today)
// MessageBox.Show("Вы уверенны, что платеж должен быть совершен текущим днем?");
int focused_row = 0;
focused_row = gridView2.FocusedRowHandle;
DataSaver ds1 = new DataSaver(login_, password_);
inform_ = ds1.getPFM();
string admMet = "";
//if (inform_[0].Substring(0, 13) == "Администратор") Изменил Дороненков Г.Г. 03-06-2014
if ((inform_[0] != string.Empty) && (inform_[0].Substring(0, 13) == "Администратор"))
admMet = "0";
else
admMet = "1";
if (inform_[2] == "1" && admMet == "1")
{
button7.Enabled = false;
lBlockMessage.Visible = true;
tb_pay_ContragentCode.Text = "";
tb_pay_Summ.Text = "";
tp_pay_ContractCode.Text = "";
dtp_pay_Date.Value = DateTime.Today;
tb_plan_statelikvid.Text = "";
tb_plan_stavrolen.Text = "";
tb_pay_EPL.Text = "";
tb_Comment.Text = "";
}
else
{
string curs = "";
curs = cb_pay_Currency.SelectedValue.ToString();
try
{
if (operation_pay == "INS")
{
string message = "";
DataSaver ds = new DataSaver(login_, password_);
message = ds.InsertPaymentPlan(tp_pay_ContractCode.Text,
tb_pay_ContragentCode.Text,
tb_pay_ContragentType.Text,
cb_pay_FinPosition.SelectedValue.ToString(),
tb_pay_EPL.Text,
tb_pay_PFMcode.Text.Replace(" ", ""),
dtp_pay_Date.Text,
curs,
tb_pay_Summ.Text.Replace(" ", ""),
tb_pay_SummRUB.Text,
tb_Comment.Text
);
if (message.Substring(0, 14) == "Предупреждение" || message.Substring(0, 5) == "Лимит")
MessageBox.Show(message);
else lb_status.Text = message;
tb_pay_ContragentCode.Text = "";
tb_pay_Summ.Text = "";
tp_pay_ContractCode.Text = "";
dtp_pay_Date.Value = DateTime.Today;
tb_plan_statelikvid.Text = "";
tb_plan_stavrolen.Text = "";
tb_pay_EPL.Text = "";
tb_Comment.Text = "";
focused_row = gridView2.RowCount;
}
else if (operation_pay == "UPD")
{
focused_row = gridView2.FocusedRowHandle;
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.UpdatePaymentPlan(tp_pay_ContractCode.Text,
tb_pay_ContragentCode.Text,
tb_pay_ContragentType.Text,
cb_pay_FinPosition.SelectedValue.ToString(),
tb_pay_EPL.Text,
tb_pay_PFMcode.Text.Replace(" ", ""),
dtp_pay_Date.Text,
curs,
tb_pay_Summ.Text.Replace(" ", ""),
tb_pay_SummRUB.Text,
tb_pay_id.Text,
tb_Comment.Text,
admMet
);
}
else if (operation_pay == "DEL")
{
try
{
if (gridView2.FocusedRowHandle != gridView2.RowCount - 1)
focused_row = gridView2.FocusedRowHandle;
else
focused_row = gridView2.FocusedRowHandle - 1;
}
catch { }
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DeletePayments(tb_pay_id.Text);
}
}
catch
{
lb_status.Text = "Ошибка: Заполнены не все поля!!!";
}
PlansFill();
gridView2.FocusedRowHandle = focused_row;
link_Payment_add_W.LinkVisited = false;
}
}
}
private void btn_ksss_Click(object sender, EventArgs e)
{
int focusRow = 1;
if (operation_ksss == "UPD")
{
if ((tb_contr_type.Text == "" && tb_contr_name.Text == "") ||
(tb_ksss.Text == "" && tb_contr_code.Text == ""))
{
lb_status.Text = "Ошибка: Не обязательным для ввода является тольео поле \"Код контрагента/кредитора\"";
}
else
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.UpdateKSSStoContragent(tb_contr_type.Text, tb_contr_name.Text
, tb_ksss.Text.Replace(" ",""), tb_contr_code.Text.Replace(" ",""), tb_ksss_id.Text);
}
focusRow = KSSSView.FocusedRowHandle;
}
else if (operation_ksss == "INS")
{
if ((tb_contr_type.Text == "" && tb_contr_name.Text == "") ||
(tb_ksss.Text == "" && tb_contr_code.Text == ""))
{
lb_status.Text = "Ошибка: Не обязательным для ввода является тольео поле \"Код контрагента/кредитора\"";
}
else
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.InsertKsssContragent(tb_contr_type.Text, tb_contr_name.Text, tb_ksss.Text.Replace(" ",""), tb_contr_code.Text.Replace(" ",""));
}
focusRow = KSSSView.RowCount;
tb_contr_code.Text = "";
tb_contr_name.Text = "";
tb_contr_type.Text = "";
tb_ksss.Text = "";
}
else if (operation_ksss == "DEL")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DelKSSSContragent(tb_ksss_id.Text);
focusRow = KSSSView.RowCount - 1;
}
try
{
KSSStoContragentFill();
KSSSView.FocusedRowHandle = focusRow;
}
catch { }
}
private void CurrencyView_CellValueChanged(object sender, CellValueChangedEventArgs e)
{
if (CurrencyView.GetFocusedDataRow()["Rus"].ToString() == "" || CurrencyView.GetFocusedDataRow()["Currency"].ToString() == "")
{
lb_status.Text = "Ошибка: Вы оставили пустое поле!";
}
else
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.CurrencyCursFill(CurrencyView.GetFocusedDataRow());
}
}
public Form1(string login, string password, Form main)
{
InitializeComponent();
// _command = new OleDbCommand();
if (_connection == null)
{
temp = main;
try
{
_connection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormationOfSchedule.Properties.Settings.FormationOfSchedule"].ConnectionString);
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = _connection.DataSource;
scsb.InitialCatalog = _connection.Database;
scsb.UserID = login;
scsb.Password = password;
login_ = login;
password_ = password;
_connection = new SqlConnection(scsb.ConnectionString);
string[] inform = new string[3];
DataSaver ds = new DataSaver(login_, password_);
inform = ds.getPFM();
inform_ = inform;
if (inform_[2] == "1")
{
blockMark = "unlock";
btn_block.Text = "Разблокировать ввод ПП";
}
else
{
blockMark = "lock";
btn_block.Text = "Заблокировать ввод ПП";
}
//if (inform_[0].Substring(0, 13) != "Администратор") Изменил Дороненков Г.Г. 03-06-2014
if ((inform_[0] != string.Empty) && (inform_[0].Substring(0, 13) != "Администратор"))
{
//tabPage2.Enabled = false;
btn_del_actual.Visible = false;
lb_limits_file.Visible = false;
btn_limits_load.Visible = false;
groupBox3.Visible = false;
groupBox7.Visible = false;
btnAdd.Visible = false;
linkAdd.Visible = false;
linkDel.Visible = false;
linkChenge.Visible = false;
// limitsControl.Location.Y = System.Drawing.Point.
// limitsControl.Location = new Point(6, 10);
// tabPage4.Enabled = false;
btn_actual_load.Visible = false;
lb_actual_file.Visible = false;
gb_actual.Visible = false;
// ActualControl.Location = new Point(6, 10);
tabPage5.Enabled = false;
btn_block.Visible = false;
otch2.Visible = false;
Otch3.Visible = false;
otch_sv.Visible = false;
otch5.Visible = false;
otch6.Visible = false;
dateTimePicker10.Visible = false;
label68.Visible = false;
dateTimePicker7.Visible = false;
label58.Visible = false;
if (inform_[2] == "1")
{
button7.Enabled = false;
// MessageBox.Show("Ввод планируемых платежей заблокирован!", "Внимание!!!");
lBlockMessage.Visible = true;
}
}
tb_pay_ContragentType.ReadOnly = true;
tb_pay_EPL.ReadOnly = true;
tb_pay_PFMcode.ReadOnly = true;
tb_pay_SummRUB.ReadOnly = true;
tb_plan_statelikvid.ReadOnly = true;
tb_plan_stavrolen.ReadOnly = true;
tb_pay_ContragentType.BackColor = System.Drawing.Color.White;
tb_pay_EPL.BackColor = System.Drawing.Color.White;
tb_pay_PFMcode.BackColor = System.Drawing.Color.White;
tb_pay_SummRUB.BackColor = System.Drawing.Color.White;
tb_plan_stavrolen.BackColor = System.Drawing.Color.White;
tb_plan_statelikvid.BackColor = System.Drawing.Color.White;
tb_pay_PFMcode.Text = inform[1];
TemplatePath = Directory.GetCurrentDirectory() + "\\Reports\\";
string separator = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator;
if (separator == ".")
{
separator_true = '.';
separator_false = ',';
}
else
{
separator_true = ',';
separator_false = '.';
}
}
catch (Exception ex)
{
// MessageBox.Show("Проблема с авторизацией! Возможно у Вас нет доступа к ПО.");
MessageBox.Show(ex.Message);
StringError = "error";
}
}
}
private void btn_fp_Stavrolen_Click(object sender, EventArgs e)
{
if (operation_fp == "INS")
{
string FPcode = "";
FPcode = gridView11.GetFocusedDataRow()["FPcode"].ToString();
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.InsertFinPosition_Stavrolen(FPcode, tb_fp_FPstavrolen.Text, tb_fp_FPSname.Text);
this.v_FinPosition_EPLTableAdapter.Fill(this.formationOfScheduleDataSet5.v_FinPosition_EPL);
tb_fp_FPstavrolen.Text = "";
tb_fp_FPSname.Text = "";
}
else if (operation_fp == "UPD")
{
try
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.UpdateFinPosition_Stavrolen(FPCODESTAVROLEN
, tb_fp_FPstavrolen.Text, tb_fp_FPSname.Text);
}
catch { }
}
else if (operation_fp == "DEL")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DeleteFinPosition_Stavrolen(tb_fp_FPstavrolen.Text, tb_fp_FPSname.Text);
}
// this.v_FinPosition_EPLTableAdapter.Fill(this.formationOfScheduleDataSet5.v_FinPosition_EPL);
this.financialPositionTableAdapter.Fill(this.formationOfScheduleDataSet3.FinancialPosition);
this.v_FinancialPosition_TopicTableAdapter.Fill(this.formationOfScheduleDataSet6.v_FinancialPosition_Topic);
ContractsFill();
ActualPaymentsFill();
PlansFill();
}
public void timer1_Tick(object sender, EventArgs e)
{
lb_status.Text = "";
DataSaver ds = new DataSaver(login_, password_);
inform_ = ds.getPFM();
//if (inform_[2] == "1" && (inform_[0].Substring(0, 13) != "Администратор")) Изменил Дороненков Г.Г. 03-06-2014
if (inform_[2] == "1" && ((inform_[0] != string.Empty) && (inform_[0].Substring(0, 13) != "Администратор")))
{
button7.Enabled = false;
lBlockMessage.Visible = true;
}
else
{
button7.Enabled = true;
lBlockMessage.Visible = false;
}
// MessageBox.Show(DateTime.Now.ToString());
/*
if (DateTime.Now.ToString("HH:mm") == "13:05" || DateTime.Now.ToString("HH:mm") == "17:05")
{
_connection.Close();
timer1.Stop();
temp.Close();
}
*/
}
private void btn_DOWN_Click(object sender, EventArgs e)
{
int focusRow = gridView3.FocusedRowHandle;
string id_tec = gridView3.GetFocusedDataRow()["IdFinPosition"].ToString();
gridView3.MoveNext();
string id_down = gridView3.GetFocusedDataRow()["IdFinPosition"].ToString();
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.SortFinPos(id_tec, id_down);
this.v_FinancialPosition_TopicTableAdapter.Fill(this.formationOfScheduleDataSet6.v_FinancialPosition_Topic);
gridView3.FocusedRowHandle = focusRow;
}
private void btn_Users_Click(object sender, EventArgs e)
{
if (operation == "INS")
{
if (tb_login.Text != "" && tb_fio.Text != "" && tb_PFM_users.Text != "" && tb_PFMname.Text != "")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.AddUsers
(tb_login.Text.Replace(" ","")
, tb_fio.Text
, tb_PFM_users.Text.Replace(" ", "")
, tb_PFMname.Text
, cb_groupUsers.SelectedValue.ToString()
);
}
else
{
lb_status.Text = "Ошибка: Введите всю информацию!";
}
tb_login.Text = "";
tb_fio.Text = "";
tb_PFM_users.Text = "";
tb_PFMname.Text = "";
}
else if (operation == "UPD")
{
if (tb_login.Text != "" && tb_fio.Text != "" && tb_PFM_users.Text != "" && tb_PFMname.Text != "")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.UpdateUsers
(tb_login.Text.Replace(" ", "")
, tb_fio.Text
, tb_PFM_users.Text.Replace(" ", "")
, tb_PFMname.Text
, cb_groupUsers.SelectedValue.ToString()
, tb_users_ID.Text
);
LimitsFill();
ActualPaymentsFill();
ContractsFill();
PlansFill();
}
else
{
lb_status.Text = "Ошибка: Заполнены не все поля!";
}
}
else if (operation == "DEL")
{
DataSaver ds = new DataSaver(login_, password_);
lb_status.Text = ds.DeleteUsers(tb_users_ID.Text);
}
this.v_Users_GETTableAdapter.Fill(this.formationOfScheduleDataSet1.v_Users_GET);
}
private void btn_OK_Click(object sender, EventArgs e)
{
// Путь к файлу шаблона
string TemplateFileName = "ReportIncludePayments.xlt";
//TemplatePath = TemplatePath + TemplateFileName;
//string TemplatePath = TemplateFileName;
string TemplateWorksheetName = "Отчет";
string TemplateWorksheetName2 = "Отчет по декадам";
// Константы для поиска стартовой ячейки вывода данных отчёта
int MaxRowsToFindStart = 18; // Просматриваемых строк
int MaxColumnsToFindStart = 10; // Просматриваемых столбцов
// Переменные для перебора строк
bool StartIsFind; // Флаг найденного старта
int CurrentRow; // Текущая строка
int CurrentColumn; // Текущий столбец
int StartRow; // Стартовая строка
int StartColumn; // Стартовый столбец
int clWeightDifference = 9; // Разница по весу
bool StartIsFind2; // Флаг найденного старта
int CurrentRow2; // Текущая строка
int CurrentColumn2; // Текущий столбец
int StartRow2; // Стартовая строка
int StartColumn2; // Стартовый столбец
int clWeightDifference2 = 6; // Разница по весу
// Константы Excel
int xlDown = -4121;
// Закоментированы для того, чтобы не было Warning-ов
int clShipmentNumber = 1; // Номер отгрузки
int DataRowNumber;
int DataColumnNumber;
//string date1 = dateTimePicker2.Value.ToString();
// string date2;
// string pfm = _information[1];
string Comm = "";
string Comm2 = "";
if(_otch_num == 2)
Comm = "SELECT * FROM [udf_Report_Date] (@startDate,@endDate,2)";
else
Comm = "SELECT * FROM [udf_Report_Date] (@startDate,@endDate,1)";
Comm2 = "SELECT * FROM [udf_ReportDecade] (@data)";
SqlCommand ExelComm = new SqlCommand(Comm, _connection);
ExelComm.Parameters.Add("@startDate", SqlDbType.Date).Direction = ParameterDirection.Input;
ExelComm.Parameters["@startDate"].Value = dateTimePicker1.Value.Date;
ExelComm.Parameters.Add("@endDate", SqlDbType.Date).Direction = ParameterDirection.Input;
ExelComm.Parameters["@endDate"].Value = dateTimePicker2.Value.Date;
DataSet ds = new DataSet();
SqlCommand ExelComm2 = new SqlCommand(Comm2, _connection);
ExelComm2.Parameters.Add("@data", SqlDbType.Date).Direction = ParameterDirection.Input;
ExelComm2.Parameters["@data"].Value = dateTimePicker1.Value.Date;
DataSet ds2 = new DataSet();
try
{
if (_connection.State != System.Data.ConnectionState.Open)
_connection.Open();
ExelComm.ExecuteNonQuery();
SqlDataAdapter new_dataAdapter = new SqlDataAdapter(ExelComm);
new_dataAdapter.Fill(ds);
}
catch { }
try
{
if (_connection.State != System.Data.ConnectionState.Open)
_connection.Open();
ExelComm2.ExecuteNonQuery();
SqlDataAdapter new_dataAdapter2 = new SqlDataAdapter(ExelComm2);
new_dataAdapter2.Fill(ds2);
}
catch { }
if (ExcelApp == null)
/*Microsoft.Office.Interop.Excel.Application*/ ExcelApp = new Microsoft.Office.Interop.Excel.Application();
// Создание книги по шаблону
ExcelApp.Workbooks.Add(TemplatePath + TemplateFileName);
// Получение ссылки на лист отчёта
Microsoft.Office.Interop.Excel.Worksheet ReportWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Worksheets[TemplateWorksheetName];
Microsoft.Office.Interop.Excel.Worksheet ReportWorksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Worksheets[TemplateWorksheetName2];
// Поиск ячейки с которой должен начатся вывод данных
CurrentRow = 2;
CurrentColumn = 1;
StartIsFind = false;
StartRow = 1;
StartColumn = 1;
while (!StartIsFind && CurrentColumn <= MaxColumnsToFindStart)
{
// Поиск стартовой ячейки
// Поиск в текущем столбце
while (!StartIsFind && CurrentRow <= MaxRowsToFindStart)
{
// Проверка текущей ячейки
if (ReportWorksheet.get_Range(CellName(CurrentRow, CurrentColumn), CellName(CurrentRow, CurrentColumn)).Text.ToString() == "#start")
{
StartIsFind = true;
StartRow = CurrentRow;
StartColumn = CurrentColumn;
}
// Переход к следующей ячейке
CurrentRow++;
}
// Переход к следующему столбцу
CurrentColumn++;
CurrentRow = 1;
}
//Для второго отчета
// Поиск ячейки с которой должен начатся вывод данных
CurrentRow2 = 2;
CurrentColumn2 = 1;
StartIsFind2 = false;
StartRow2 = 1;
StartColumn2 = 1;
while (!StartIsFind2 && CurrentColumn2 <= MaxColumnsToFindStart)
{
// Поиск стартовой ячейки
// Поиск в текущем столбце
while (!StartIsFind2 && CurrentRow2 <= MaxRowsToFindStart)
{
// Проверка текущей ячейки
if (ReportWorksheet2.get_Range(CellName(CurrentRow2, CurrentColumn2), CellName(CurrentRow2, CurrentColumn2)).Text.ToString() == "#start")
{
StartIsFind2 = true;
StartRow2 = CurrentRow2;
StartColumn2 = CurrentColumn2;
}
// Переход к следующей ячейке
CurrentRow2++;
}
// Переход к следующему столбцу
CurrentColumn2++;
CurrentRow2 = 1;
}
// Вывод данных отчёта начиная со стартовой позиции
if (StartIsFind)
{
// Вывод данных в шаблон
// Переход к начальной позиции вывода данных
CurrentRow = StartRow;
// Вывод строк из таблицы
// Добавление нужного количества строк
for (CurrentRow = StartRow; CurrentRow < ds.Tables[0].Rows.Count + StartRow - 1; CurrentRow++)
{
ReportWorksheet.get_Range(CellName(CurrentRow, clWeightDifference), CellName(CurrentRow, clWeightDifference)).EntireRow.Insert(xlDown, true);
}
object[,] ReportDataArray = new object[ds.Tables[0].Rows.Count, 9];
for (DataRowNumber = 0; DataRowNumber < ds.Tables[0].Rows.Count; DataRowNumber++)
{
for (DataColumnNumber = 0; DataColumnNumber < 9; DataColumnNumber++)
{
ReportDataArray[DataRowNumber, DataColumnNumber] = ds.Tables[0].Rows[DataRowNumber][DataColumnNumber];
}
}
ReportWorksheet.get_Range(CellName(StartRow, clShipmentNumber), CellName(ds.Tables[0].Rows.Count + StartRow - 1, clWeightDifference)).Value2 = ReportDataArray;
if(_otch_num == 2)
ExcelApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, ExcelApp, new object[] { "Svodnaia" });
}
//Для второго отчета
if (StartIsFind2)
{
// Вывод данных в шаблон
// Переход к начальной позиции вывода данных
CurrentRow2 = StartRow2;
// Вывод строк из таблицы
// Добавление нужного количества строк
for (CurrentRow2 = StartRow2; CurrentRow2 < ds2.Tables[0].Rows.Count + StartRow2 - 1; CurrentRow2++)
{
ReportWorksheet2.get_Range(CellName(CurrentRow2, clWeightDifference2), CellName(CurrentRow2, clWeightDifference2)).EntireRow.Insert(xlDown, true);
}
object[,] ReportDataArray2 = new object[ds2.Tables[0].Rows.Count, 6];
for (DataRowNumber = 0; DataRowNumber < ds2.Tables[0].Rows.Count; DataRowNumber++)
{
for (DataColumnNumber = 0; DataColumnNumber < 6; DataColumnNumber++)
{
ReportDataArray2[DataRowNumber, DataColumnNumber] = ds2.Tables[0].Rows[DataRowNumber][DataColumnNumber];
}
}
ReportWorksheet2.get_Range(CellName(StartRow2, clShipmentNumber), CellName(ds2.Tables[0].Rows.Count + StartRow2 - 1, clWeightDifference2)).Value2 = ReportDataArray2;
ReportWorksheet2.get_Range(CellName(2, 3), CellName(2, 3)).Value2 = dateTimePicker1.Value.Date.Day.ToString() + "."
+ dateTimePicker1.Value.Date.Month.ToString() + "."
+ dateTimePicker1.Value.Date.Year.ToString() + " - "
+ dateTimePicker2.Value.Date.Day.ToString() + "."
+ dateTimePicker2.Value.Date.Month.ToString() + "."
+ dateTimePicker2.Value.Date.Year.ToString();
}
if (StartIsFind || StartIsFind2)
{
// Отображение отчёта на экране
ExcelApp.Visible = true;
//сохранение результатов отчета по декадам для отчета средней оценки точности
if (MessageBox.Show("Сохранить результаты отчета по декадам?", "Сообщение", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
if (_connection != null)
{
DataSaver saver = new DataSaver(_connection);
string mess = saver.InsertPaymentsPlanSum(dateTimePicker1.Value.Date);
MessageBox.Show(mess, "Сообщение", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
this.Close();
}