public int Zakupki(string pathodb, string pathoffile)
{
string s = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
SqlDataAdapter da = new SqlDataAdapter("Select * from Preparat", s);
MyDBDataSet ds = new MyDBDataSet();
da.Fill(ds, "Preparat");
int resuly = 0;
Excel.Application excelapp = new Excel.Application();
Excel.Workbook file;
file = excelapp.Workbooks.Open(pathoffile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Sheets sheets = excelapp.Worksheets;
Excel.Worksheet worksheet;
worksheet = (Excel.Worksheet)sheets.get_Item(1);
Excel.Range cell_1 = (Excel.Range)worksheet.Cells[1, 1];
int i = 1, j = 1;
string a1 = Convert.ToString(cell_1.Value2);
a1 = a1.ToLower();
DataBase bd = new DataBase(pathodb);
Excel.Range cell_2;
i = 3;
cell_1 = (Excel.Range)worksheet.Cells[i, 1];
while (Convert.ToString(cell_1.Value2) != null)
{
i++;
cell_1 = (Excel.Range)worksheet.Cells[i, 1];
}
int rowend = i-1;
bool datt = false;
string date = "";
while (datt == false)
{
if (Convert.ToString((cell_2 = (Excel.Range)worksheet.Cells[i, 6]).Value2) != null)
{
datt = true;
date = Convert.ToString((cell_2 = (Excel.Range)worksheet.Cells[i, 6]).Value2);
}
i++;
}
for (i = 3; i <= rowend; i++)
{
cell_2 = (Excel.Range)worksheet.Cells[i, 4];
string postavchik = Convert.ToString(cell_2.Value2);
int Id_postavchik = FindPostav(postavchik);
string name = Convert.ToString((cell_2 = (Excel.Range)worksheet.Cells[i,1]).Value2);
int kolvo = Convert.ToInt32((cell_2 = (Excel.Range)worksheet.Cells[i, 3]).Value2);
float price = Convert.ToDouble((cell_2 = (Excel.Range)worksheet.Cells[i, 2]).Value2);
int id_prep = bd.FindIdPrep(Id_postavchik);
bd.Update_Preparat(id_prep, Id_postavchik, name, price, kolvo);
}
file.Close();
excelapp.Quit();
return resuly;
}
private void ADD_card_FormClosing(object sender, FormClosingEventArgs e)
{
if (DialogResult == System.Windows.Forms.DialogResult.OK)
{
Card ca = this.Owner as Card;
int index = ca.dataGridView1.CurrentCell.RowIndex;
DataBase db = new DataBase(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\user\Documents\GitHub\oop\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
var q = from k in db.Card select k;
var c = from i in db.Discount select i;
// var w = from g in db.Discount select g.summa;
List<string> qwe = new List<string>();
foreach (var k in q)
{
foreach (var i in c)
if (k.id_algoritm == i.id_algoritm)
{
{ qwe.Add(i.summa.ToString()); }
}
}
// foreach (var i in qwe)
// { MessageBox.Show(i.ToString()); }
foreach (var k in q)
{//скидки
foreach (var i in c)
{
if (k.id_algoritm == i.id_algoritm)
{
for (int j = 0; j < qwe.Count;j++ )
{
if ((k.summa_nakopl > Convert.ToInt32(qwe[j])) && (k.summa_nakopl < Convert.ToInt32(qwe[j+1])))
{
if(i.summa== Convert.ToInt32(qwe[j]))
{
// MessageBox.Show(i.procent.ToString());
ca.dataGridView1[3, index].Value = i.procent.ToString();
}
}
}
}
}
}
cardBindingSource.EndEdit();
}
else
cardBindingSource.CancelEdit();
}
private void button_sale_Click_1(object sender, EventArgs e)
{
DataBase db = new DataBase(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\user\Documents\GitHub\oop\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
int a = dataGridView2.RowCount;
for (int i = 0; i < a; i++)
{
int str2 = 0;
var c = from j in db.Preparat select j;
foreach (var j in c)
{
if (j.name == dataGridView2[0,i].Value.ToString())
{
str2 = j.id_preparat;
}
}
int str = Convert.ToInt32(dataGridView2[1, i].Value);
float str1 = Convert.ToSingle(dataGridView2[3, i].Value);
db.ADD_Sale(str2, str, DateTime.Today, str1);
}
MessageBox.Show("Продажа успешно совершена !");
dataGridView2.Rows.Clear();
textBox1.Clear();
textBox2.Clear();
textBox3.Clear();
}
private void BTN_OK_Click(object sender, EventArgs e)
{
textBox3.Text = "";
DataBase db = new DataBase(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\user\Documents\GitHub\oop\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
int t = 0;
int count = 0;
var c = from i in db.Card select i;
foreach (var i in c)
{
if (i.id_card == Convert.ToInt32(textBox1.Text))
{
t = i.current_procent;
count++;
}
}
if(count==0)
{
MessageBox.Show("Данная карта не зарегестрирована в сети аптек");
}
textBox3.Text = (Convert.ToDouble(textBox2.Text) - (Convert.ToInt32(textBox2.Text) * t*0.01)).ToString();
int a = dataGridView2.RowCount;
for (int i = 0; i < a; i++)
{ dataGridView2[3, i].Value = (Convert.ToInt32(dataGridView2[2, i].Value) - ((Convert.ToInt32(dataGridView2[2, i].Value)) * t * 0.01)).ToString(); }
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: данная строка кода позволяет загрузить данные в таблицу "myDBDataSet.NalDGV". При необходимости она может быть перемещена или удалена.
this.nalDGVTableAdapter.Fill(this.myDBDataSet.NalDGV);
label4.Visible = false;
textBox3.Visible = false;
BTN_OK.Visible = false;
int count = 0;
int count1 = 0;
DataBase db = new DataBase(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\user\Documents\GitHub\oop\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
var c = from i in db.Preparat select i;
var d = from j in db.Setup select j;
foreach (var i in c)
{
foreach (var j in d)
{
if (i.id_setup == j.id_setup)
{
if ((i.amount < j.porog)||(i.srok<=DateTime.Today))
{
count++;
}
else
{
count1++;
}
}
}
}
if (count > 0)
{
string message = "Вам необходимо оформить заказ.! Оформить?";
string caption = "Что делать?";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(message, caption, buttons);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Zakaz zakaz = new Zakaz();
this.Hide();
zakaz.ShowDialog();
this.Show();
}
else
{
this.Text = "SystemPharmacy";
}
}
else {
if (count1 > 0)
{ MessageBox.Show("Новые заказы на данный момент не требуется"); }
}
// dataGridView2.AllowUserToAddRows = false;
// dataGridView2.AllowUserToDeleteRows = false;
// TODO: данная строка кода позволяет загрузить данные в таблицу "myDBDataSet1.saleDGV". При необходимости она может быть перемещена или удалена.
// this.saleDGVTableAdapter.Fill(this.myDBDataSet1.saleDGV);
// var c = from i in myDBDataSet.Setup select i;
/* foreach (var i in c)
{
comboBox2.Items.Add(i.Porog.ToString());
}*/
// DataGridViewComboBoxColumn newc = new DataGridViewComboBoxColumn();
// newc.Name = "abc";
// newc.DataSource = new string[] { "a", "b", "c" };
// dataGridView1.Columns.Add(newc);
/* foreach (var i in c)
{
newc.Items.Add(i.ToString());
}*/
// MessageBox.Show(monthCalendar1.TodayDate.ToString());
// TODO: данная строка кода позволяет загрузить данные в таблицу "myDBDataSet.Setup". При необходимости она может быть перемещена или удалена.
// this.setupTableAdapter.Fill(this.myDBDataSet.Setup);
// TODO: данная строка кода позволяет загрузить данные в таблицу "myDBDataSet.Preparat". При необходимости она может быть перемещена или удалена.
// this.preparatTableAdapter.Fill(this.myDBDataSet.Preparat);
/* Class_Zakupki_Excel cl = new Class_Zakupki_Excel();
int count=0;
this.Text = "System_of_Pharmacy";
string str= @"D:\Zakupki";
DirectoryInfo docs = new DirectoryInfo(str);
FileInfo[] files = docs.GetFiles();
foreach (FileInfo f in files)
{ count++;
int result = cl.Zakupki(@"D:\MyDB.mdf", f.FullName);
f.Delete();
}
MessageBox.Show(count.ToString());
DataGridViewComboBoxColumn cmb = new DataGridViewComboBoxColumn();
var c = from i in this.myDBDataSet.Setup select i.Porog;
idsetupDataGridViewTextBoxColumn.
for (int i = 0; i < c.Count();i++ )
{
idsetupDataGridViewTextBoxColumn.Items.Add(c);
}
dataGridView1.Columns.AddRange(cmb);
DataTable dt = new DataTable();
combobox1.DataSource = setupBindingSource;
combobox1.ValueMember = "Id_setup";
combobox1.DisplayMember = "Porog";
DataGridViewComboBoxColumn newc = new DataGridViewComboBoxColumn();
newc.Name = "abc";
newc.DataSource = new string[] { "a", "b", "c" };
dataGridView1.Columns.Add(newc);
newc.Items.Add("sdfghjhgfdfg");
foreach (DataGridViewRow row in dataGridView1.Rows)
{
DataGridViewComboBoxCell cell = (DataGridViewComboBoxCell)(row.Cells["abc"]);
cell.DataSource = new string[] { "a", "c" };
}*/
}