107
{
dataGridView1->Rows->Clear();
for(int i=0; i<sqlresult.Count; i++)
{
dataGridView1->Rows->Add("", "", "", "", "", "", "", "", "", "",
"", "");
dataGridView1->Rows[i]->HeaderCell->Value =
Convert::ToString(i+1);
dataGridView1->Rows[i]->Cells[0]->Value = sqlresult[i][0];
dataGridView1->Rows[i]->Cells[1]->Value = sqlresult[i][1];
dataGridView1->Rows[i]->Cells[2]->Value = sqlresult[i][2];
dataGridView1->Rows[i]->Cells[3]->Value = sqlresult[i][3];
dataGridView1->Rows[i]->Cells[4]->Value = sqlresult[i][4];
dataGridView1->Rows[i]->Cells[5]->Value = sqlresult[i][5];
dataGridView1->Rows[i]->Cells[6]->Value = sqlresult[i][6];
dataGridView1->Rows[i]->Cells[7]->Value = sqlresult[i][7];
dataGridView1->Rows[i]->Cells[8]->Value = sqlresult[i][8];
dataGridView1->Rows[i]->Cells[9]->Value = sqlresult[i][9];
dataGridView1->Rows[i]->Cells[10]->Value = sqlresult[i][10];
dataGridView1->Rows[i]->Cells[11]->Value = sqlresult[i][11];
}
}
dataGridView1->Columns[0]->Visible = false;
// Загрузка списков
loadTypesCombo();
loadStatusesCombo();
loadSuppliersCombo();
loadClientsCombo();
loadEmployeesCombo();
// Загрузка документа
loadnumdoc();
}
void Form1::showsuppliers()
{
suppliers^ frm = gcnew suppliers(this);
frm->ShowDialog();
}
void Form1::showClients()
{
clients^ frm = gcnew clients(this);
frm->ShowDialog();
}
void Form1::showTypes()
{
types^ frm = gcnew types(this);
frm->ShowDialog();
}
108
void Form1::showStatuses()
{
statuses^ frm = gcnew statuses(this);
frm->ShowDialog();
}
void Form1::showEmployees()
{
employees^ frm = gcnew employees(this);
frm->ShowDialog();
}
void Form1::loadTypesCombo()
{
String ^sql;
list_of_list sqlresult = gcnew list_of_list();
sql = "select a.id_typ, a.typename from types as a";
bool res = this->sqlSelect(sql, %sqlresult);
if(!res)
MessageBox::Show("Ошибка\r\nНе могу прочитать данные о видах
документов");
else
{
comboBox1->Items->Clear();
dTypes1.Clear();
dTypes2.Clear();
for(int i=0; i<sqlresult.Count; i++)
{
comboBox1->Items->Add(sqlresult[i][1]);
dTypes1.Add(i, Convert::ToInt32(sqlresult[i][0]));
dTypes2.Add(Convert::ToInt32(sqlresult[i][0]), i);
}
}
}
void Form1::loadStatusesCombo()
{
String ^sql;
list_of_list sqlresult = gcnew list_of_list();
sql = "select a.id_sta, a.status from statuses as a";
bool res = this->sqlSelect(sql, %sqlresult);
if(!res)
MessageBox::Show("Ошибка\r\nНе могу прочитать данные о статусах
документов");
else
{
comboBox2->Items->Clear();
dStatuses1.Clear();
dStatuses2.Clear();
109
for(int i=0; i<sqlresult.Count; i++)
{
comboBox2->Items->Add(sqlresult[i][1]);
dStatuses1.Add(i, Convert::ToInt32(sqlresult[i][0]));
dStatuses2.Add(Convert::ToInt32(sqlresult[i][0]), i);
}
}
}
void Form1::loadSuppliersCombo()
{
String ^sql;
list_of_list sqlresult = gcnew list_of_list();
sql = "select a.id_sup, a.fio + ' ' + a.tel + ' [' + a.org + '] ' + (CASE WHEN a.is_legal = '1'
THEN 'Юрлицо' ELSE 'Физлицо' END) from suppliers as a";
bool res = this->sqlSelect(sql, %sqlresult);
if(!res)
MessageBox::Show("Ошибка\r\nНе могу прочитать данные о
Cтраховщиках");
else
{
comboBox3->Items->Clear();
dsuppliers1.Clear();
dsuppliers2.Clear();
for(int i=0; i<sqlresult.Count; i++)
{
comboBox3->Items->Add(sqlresult[i][1]);
dsuppliers1.Add(i, Convert::ToInt32(sqlresult[i][0]));
dsuppliers2.Add(Convert::ToInt32(sqlresult[i][0]), i);
}
}
}
void Form1::loadClientsCombo()
{
String ^sql;
list_of_list sqlresult = gcnew list_of_list();
sql = "select a.id_cli, a.fio + ' ' + a.tel + ' [' + a.org + '] ' + (CASE WHEN a.is_legal = '1'
THEN 'Юрлицо' ELSE 'Физлицо' END) from clients as a";
bool res = this->sqlSelect(sql, %sqlresult);
if(!res)
MessageBox::Show("Ошибка\r\nНе могу прочитать данные о клиентах");
else
{
comboBox4->Items->Clear();
dClients1.Clear();
dClients2.Clear();
for(int i=0; i<sqlresult.Count; i++)
{
comboBox4->Items->Add(sqlresult[i][1]);
dClients1.Add(i, Convert::ToInt32(sqlresult[i][0]));
110
dClients2.Add(Convert::ToInt32(sqlresult[i][0]), i);
}
}
}
void Form1::loadEmployeesCombo()
{
String ^sql;
list_of_list sqlresult = gcnew list_of_list();
sql = "select a.id_emp, a.fio + ' ' + (CASE WHEN a.is_admin = '1' THEN 'Администратор'
ELSE 'Обычный сотрудник' END) from employees as a";
bool res = this->sqlSelect(sql, %sqlresult);
if(!res)
MessageBox::Show("Ошибка\r\nНе могу прочитать данные о
сотрудниках");
else
{
comboBox5->Items->Clear();
dEmployees1.Clear();
dEmployees2.Clear();
for(int i=0; i<sqlresult.Count; i++)
{
comboBox5->Items->Add(sqlresult[i][1]);
dEmployees1.Add(i, Convert::ToInt32(sqlresult[i][0]));
dEmployees2.Add(Convert::ToInt32(sqlresult[i][0]), i);
}
}
}
void Form1::loadnumdoc()
{
if(dataGridView1->RowCount < 1)
return;
String ^sql;
list_of_list sqlresult = gcnew list_of_list();
int row = dataGridView1->SelectedCells[0]->RowIndex;
currentID = Convert::ToInt32(dataGridView1->Rows[row]->Cells[0]->Value);
sql = "select id_typ, id_sta, id_sup, id_cli, id_emp, numdoc, summ, CONVERT(nvarchar,
dt, 23), CONVERT(nvarchar, startd, 23), CONVERT(nvarchar, endd, 23), info from documents where
id_doc="+Convert::ToString(currentID);
bool res = this->sqlSelect(sql, %sqlresult);
if(!res)
MessageBox::Show("Ошибка\r\nНе могу прочитать выделенный
документ");
else
{
comboBox1->SelectedIndex = dTypes2[Convert::ToInt32(sqlresult[0][0])];
comboBox2->SelectedIndex = dStatuses2[Convert::ToInt32(sqlresult[0][1])];
comboBox3->SelectedIndex = dsuppliers2[Convert::ToInt32(sqlresult[0][2])];
111
comboBox4->SelectedIndex = dClients2[Convert::ToInt32(sqlresult[0][3])];
comboBox5->SelectedIndex =
dEmployees2[Convert::ToInt32(sqlresult[0][4])];
textBox1->Text = sqlresult[0][5];
textBox2->Text = sqlresult[0][6];
IFormatProvider^ culture = gcnew CultureInfo("ru-RU", true);
dateTimePicker3->Value = DateTime::ParseExact(sqlresult[0][7], "yyyy-MM-
dd", culture);
dateTimePicker1->Value = DateTime::ParseExact(sqlresult[0][8], "yyyy-MM-
dd", culture);
dateTimePicker2->Value = DateTime::ParseExact(sqlresult[0][9], "yyyy-MM-
dd", culture);
textBox3->Text = sqlresult[0][10];
}
//MessageBox::Show(Convert::ToString(currentID));
}
void Form1::insertNew()
{
String ^ newNumdoc = textBox1->Text;
String ^ newSumm = textBox2->Text;
String ^ newInfo = textBox3->Text;
String ^ newId_typ = Convert::ToString(dTypes1[comboBox1->SelectedIndex]);
String ^ newId_sta = Convert::ToString(dStatuses1[comboBox2->SelectedIndex]);
String ^ newid_sup = Convert::ToString(dsuppliers1[comboBox3->SelectedIndex]);
String ^ newId_cli = Convert::ToString(dClients1[comboBox4->SelectedIndex]);
String ^ newId_emp = Convert::ToString(dEmployees1[comboBox5->SelectedIndex]);
String ^sql = "insert into documents (numdoc, summ, dt, startd, endd, info, id_typ,
id_sta, id_sup, id_cli, id_emp) values('"+newNumdoc+"', "+newSumm+", '"+dateTimePicker3-
>Value.ToString("yyyyMMdd")+"', '"+dateTimePicker1->Value.ToString("yyyyMMdd")+"',
'"+dateTimePicker2->Value.ToString("yyyyMMdd")+"', '"+newInfo+"', "+newId_typ+", "+newId_sta+",
"+newid_sup+", "+newId_cli+", "+newId_emp+")";
this->sqlInsert(sql);
loaddocuments();
}
void Form1::deleteCurrent()
{
int row = dataGridView1->SelectedCells[0]->RowIndex;
int id = Convert::ToInt32(dataGridView1->Rows[row]->Cells[0]->Value);
if (MessageBox::Show("Вы уверены, что хотите удалить запись?", "Удаление",
MessageBoxButtons::YesNo, MessageBoxIcon::Question) ==
System::Windows::Forms::DialogResult::No)
return;
String ^sql = "delete from documents where id_doc="+Convert::ToString(id);
this->sqlOther(sql);
loaddocuments();
}
void Form1::updateCurrent()
{
int row = dataGridView1->SelectedCells[0]->RowIndex;
int id = Convert::ToInt32(dataGridView1->Rows[row]->Cells[0]->Value);
112
String ^ newNumdoc = textBox1->Text;
String ^ newSumm = textBox2->Text;
String ^ newInfo = textBox3->Text;
String ^ newId_typ = Convert::ToString(dTypes1[comboBox1->SelectedIndex]);
String ^ newId_sta = Convert::ToString(dStatuses1[comboBox2->SelectedIndex]);
String ^ newid_sup = Convert::ToString(dsuppliers1[comboBox3->SelectedIndex]);
String ^ newId_cli = Convert::ToString(dClients1[comboBox4->SelectedIndex]);
String ^ newId_emp = Convert::ToString(dEmployees1[comboBox5->SelectedIndex]);
String ^sql = "update documents set numdoc='"+newNumdoc+"',
summ="+newSumm+", dt='"+dateTimePicker3->Value.ToString("yyyyMMdd")+"',
startd='"+dateTimePicker1->Value.ToString("yyyyMMdd")+"', endd='"+dateTimePicker2-
>Value.ToString("yyyyMMdd")+"', info='"+newInfo+"', id_typ="+newId_typ+", id_sta="+newId_sta+",
id_sup="+newid_sup+", id_cli="+newId_cli+", id_emp="+newId_emp+" where
id_doc="+Convert::ToString(id);
this->sqlOther(sql);
loaddocuments();
}
void Form1::clearBoxes()
{
comboBox1->SelectedIndex = -1;
comboBox2->SelectedIndex = -1;
comboBox3->SelectedIndex = -1;
comboBox4->SelectedIndex = -1;
comboBox5->SelectedIndex = -1;
textBox1->Text = "";
textBox2->Text = "";
textBox3->Text = "";
dateTimePicker1->Value = DateTime::Now;
dateTimePicker2->Value = DateTime::Now;
dateTimePicker3->Value = DateTime::Now;
}
void FindAndReplace(/*Microsoft::Office::Interop::Word::Application^ doc, Object^ findText,
Object^ replaceWithText*/)
{
//options
/*object matchCase = false;
object matchWholeWord = true;
object matchWildCards = false;
object matchSoundsLike = false;
object matchAllWordForms = false;
object forward = true;
object format = false;
object matchKashida = false;
object matchDiacritics = false;
object matchAlefHamza = false;
object matchControl = false;
object read_only = false;
object visible = true;
object replace = 2;
object wrap = 1;
//execute find and replace
113
doc->Selection.Find.Execute(ref findText, ref matchCase, ref matchWholeWord,
ref matchWildCards, ref matchSoundsLike, ref matchAllWordForms, ref forward, ref wrap, ref
format, ref replaceWithText, ref replace,
ref matchKashida, ref matchDiacritics, ref matchAlefHamza, ref matchControl);*/
}
void Form1::printCurrent()
{
/*int row = dataGridView1->SelectedCells[0]->RowIndex;
Object^ str = "doc.docx";
Microsoft::Office::Interop::Word::Application^ wordApp = gcnew
Microsoft::Office::Interop::Word::Application;
wordApp->Visible = true;
Microsoft::Office::Interop::Word::Document^ aDoc;
//aDoc = wordApp->Documents->Open(str, (System::Object ^)true, (System::Object
^)true, (System::Object ^)true, (System::Object ^)true, (System::Object ^)true, (System::Object ^)true,
(System::Object ^)true, (System::Object ^)true, (System::Object ^)true, (System::Object ^)true,
(System::Object ^)true, (System::Object ^)true, (System::Object ^)true, (System::Object ^)true,
(System::Object ^)true);
aDoc = wordApp->Documents->Open(str);
//FindAndReplace(wordApp, "numdoc", dataGridView1->Rows[row]->Cells[3]->Value);
aDoc->Activate();*/
}
void Form1::exportToExcel(DataGridView^ grd)
{
Microsoft::Office::Interop::Excel::Application^ xla = gcnew
Microsoft::Office::Interop::Excel::Application();
xla->Visible = true;
Microsoft::Office::Interop::Excel::Workbook^ wb = xla->Workbooks-
>Add(Microsoft::Office::Interop::Excel::XlSheetType::xlWorksheet);
Microsoft::Office::Interop::Excel::Worksheet^ ws =
(Microsoft::Office::Interop::Excel::Worksheet ^)xla->ActiveSheet;
for(int j=0, k=1; j<grd->ColumnCount; j++)
if(grd->Columns[j]->Visible)
ws->Cells[1, k++] = dataGridView1->Columns[j]->HeaderCell->Value;
for(int i=0; i<grd->RowCount; i++)
for(int j=0, k=1; j<grd->ColumnCount; j++)
if(grd->Columns[j]->Visible)
ws->Cells[i+2, k++] = grd->Rows[i]->Cells[j]->Value;
}
}