public void initIdOrder()
{
mbd.Connection();
dt1 = mbd.selectionquery("select * from zakaz;");
DataForBD.IdZakaz = 1;
for (int i = 0; i < dt1.Rows.Count; i++)
{
if (Convert.ToInt32(dt1.Rows[i][0]) > DataForBD.IdZakaz)
{
DataForBD.IdZakaz = Convert.ToInt32(dt1.Rows[i][0]);
}
}
DataForBD.IdZakaz++;
}
private void новыйToolStripMenuItem_Click(object sender, EventArgs e)
{
mbd.Connection();
DataTable dt1 = mbd.selectionquery("select * from zakaz;");
DataForBD.IdZakaz = 1;
for (int i = 0; i < dt1.Rows.Count; i++)
{
if (Convert.ToInt32(dt1.Rows[i][0]) > DataForBD.IdZakaz)
{
DataForBD.IdZakaz = Convert.ToInt32(dt1.Rows[i][0]);
}
}
DataForBD.IdZakaz++;
room.ClearRoom();
DataForBD.ListZakazMebTeh.Clear();
}
private void FormAddStroyMat_Load(object sender, EventArgs e)
{
mbd.Connection();
DataTable dt = mbd.selectionquery("select * from StroyMaterial; ");
dataGridView1.DataSource = dt;
cbType.Items.Add("plitka");
cbType.Items.Add("oboi");
cbType.SelectedIndex = 0;
}
private void ToMail_Load(object sender, EventArgs e)
{
mbd.Connection();
dt = mbd.selectionquery("select * from zakaz;");
for (int i = 0; i < dt.Rows.Count; i++)
{
comboBox1.Items.Add("Заказ №" + dt.Rows[i][0] + "" + " Размеры:" + Convert.ToDouble(dt.Rows[i][1]) / 100 + "*" + Convert.ToDouble(dt.Rows[i][2]) / 100 +
"*" + Convert.ToDouble(dt.Rows[i][3]) / 100 + " Сумма:" + dt.Rows[i][6]);
}
}
private void FormBD_Load(object sender, EventArgs e)
{
mbd.Connection();
//DataTable dt = mbd.selectionquery("select naimenovanie,nazvanie,price, count(*),id_furnitura from FurnituraZakaz join furnitura on furnitura.id_furnit = FurnituraZakaz.id_furnitura group by id_furnitura order by count(*) desc ");
//DataTable dt = mbd.selectionquery("select * from StroyMaterialZakaz ");
//DataTable dt = mbd.selectionquery("select * from FurnituraZakaz ");
//DataTable dt = mbd.selectionquery("select sum(summa)from zakaz where month_Zakaz = 4");
//DataTable dt = mbd.selectionquery("select count(*) from zakaz where month_zakaz = 5");
//DataTable dt = mbd.selectionquery("select Famil,Name,otchestvo, month_zakaz,count(*) from zakaz join customer on zakaz.id_customer = customer.id_customer join FurnituraZakaz on zakaz.id_zakaz = FurnituraZakaz.id_zakaz where id_furnitura = 21 group by zakaz.id_zakaz order by famil");
//mbd.controlquery("ALTER TABLE FurnituraZakaz add Z double");
//mbd.controlquery("update FurnituraZakaz set x = 1.51151, y=1.5985955, z=1.5595 where id_zakaz=1 and id_furnitura = 21");
//mbd.controlquery("drop table FurnituraZakaz");
//mbd.controlquery("create table FurnituraZakaz (id_zakaz INTEGER,id_Furnitura INTEGER ,coordX DOUBLE,coordY DOUBLE, FOREIGN KEY (id_Furnitura) REFERENCES Furnitura (id_furnit),FOREIGN KEY (id_zakaz) REFERENCES Zakaz (id_zakaz))");
//mbd.controlquery("insert into FurnituraZakaz values (1, 61, -3, -3.5)");
//mbd.controlquery("delete from Furnitura where id_furnit = 51");
//mbd.controlquery("delete from FurnituraZakaz where id_zakaz = 4 and id_furnitura = 61");
//DataTable dt = mbd.selectionquery("select * from zakaz join customer on zakaz.id_customer = customer.id_customer ; ");
//DataTable dt = mbd.selectionquery("select * from FurnituraZakaz ; ");
//DataTable dt = mbd.selectionquery("select Famil, Name, otchestvo, month_zakaz, count(*),id_zakaz from zakaz join customer on zakaz.id_customer = customer.id_customer join FurnituraZakaz on zakaz.id_zakaz = FurnituraZakaz.id_zakaz where id_furnitura = " + "61" + " group by zakaz.id_zakaz order by famil");
//DataTable dt = mbd.selectionquery("select Famil,Name,otchestvo, month_zakaz,count(*) from zakaz join customer on zakaz.id_customer = customer.id_customer join FurnituraZakaz on zakaz.id_zakaz = FurnituraZakaz.id_zakaz where id_furnitura = " + "11"+ " group by zakaz.id_zakaz order by famil");
/*
* mbd.controlquery("update Furnitura set naimenovanie = 'cupboard' where naimenovanie = 'shkaf'");
* mbd.controlquery("update Furnitura set naimenovanie = 'fridge' where naimenovanie = 'icebox'");
* mbd.controlquery("update Furnitura set naimenovanie = 'stove' where naimenovanie = 'plita'");
* mbd.controlquery("update Furnitura set naimenovanie = 'chair' where naimenovanie = 'stol'");
*/
DataTable dt = mbd.selectionquery("select * from Furnitura; ");
dataGridView1.DataSource = dt;
comboBox1.Items.Add("mebel");
comboBox1.Items.Add("technics");
comboBox1.SelectedIndex = 0;
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex != -1)
{
textBox1.Text = "";
ManagerBD mbd = new ManagerBD();
mbd.Connection();
DataTable dt = mbd.selectionquery("select count(*) from zakaz where month_zakaz = " + Convert.ToString(comboBox1.SelectedItem));
if (Convert.ToInt32(dt.Rows[0][0]) != 0)
{
ProfitAnalysis pa = new ProfitAnalysis();
textBox1.Text = pa.Analis(Convert.ToInt32(comboBox1.SelectedItem));
}
else
{
textBox1.Text = "В этом месяце не было заказов";
}
}
else
{
MessageBox.Show("Выберите месяц", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void blank(int id)
{
dict = new Dictionary <string, string>();
dict.Add("oboi", "Обои");
dict.Add("plitka", "Плитка");
dict.Add("table", "Стол");
dict.Add("chair", "Стул");
dict.Add("stove", "Плита");
dict.Add("fridge", "Холодильник");
dict.Add("cupboard", "Шкаф");
mbd.Connection();
DataTable dt = mbd.selectionquery("select naimenovanie,nazvanie,price from StroyMaterial join StroyMaterialZakaz on StroyMaterial.id_stroy_mater = StroyMaterialZakaz.id_StroyMaterial" +
" join Zakaz on StroyMaterialZakaz.id_zakaz = Zakaz.id_zakaz where zakaz.id_zakaz = " + Convert.ToString(id) + ";");
DataTable dt1 = mbd.selectionquery("select naimenovanie,nazvanie,price from Furnitura join FurnituraZakaz on FurnituraZakaz.id_Furnitura = Furnitura.id_furnit " +
" join Zakaz on FurnituraZakaz.id_zakaz = Zakaz.id_zakaz where zakaz.id_zakaz = " + Convert.ToString(id) + " AND type = 'mebel';");
DataTable dt2 = mbd.selectionquery("select naimenovanie,nazvanie,price from Furnitura join FurnituraZakaz on FurnituraZakaz.id_Furnitura = Furnitura.id_furnit " +
" join Zakaz on FurnituraZakaz.id_zakaz = Zakaz.id_zakaz where zakaz.id_zakaz = " + Convert.ToString(id) + " AND type = 'technics';");
DataTable dt3 = mbd.selectionquery("select length,width,height from zakaz where id_zakaz = " + Convert.ToString(id) + ";");
var doc = new Document();
PdfWriter.GetInstance(doc, new FileStream(@"Document.pdf", FileMode.Create));
doc.Open();
BaseFont baseFont = BaseFont.CreateFont(@"lib/ARIAL.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
iTextSharp.text.Phrase p = new Phrase("Бланк заказа №" + Convert.ToString(id),
new iTextSharp.text.Font(baseFont, 14,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black)));
Paragraph a1 = new Paragraph(p);
a1.Alignment = Element.ALIGN_CENTER;
a1.Add(Environment.NewLine);
a1.SpacingAfter = 5;
doc.Add(a1);
PdfPTable table = new PdfPTable(4);
p = new Phrase("Строительные материалы",
new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.BOLD, new BaseColor(Color.Black)));
a1 = new Paragraph(p);
a1.Alignment = Element.ALIGN_CENTER;
a1.Add(Environment.NewLine);
a1.SpacingAfter = 5;
doc.Add(a1);
PdfPCell cell = new PdfPCell(new Phrase("Наименование", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.Colspan = 1;
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Название", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Цена за кв.м", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Кв.м", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
double sumSM = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
cell = new PdfPCell(new Phrase(dict[Convert.ToString(dt.Rows[i][j])], new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
}
else
{
cell = new PdfPCell(new Phrase(Convert.ToString(dt.Rows[i][j]), new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
}
table.AddCell(cell);
}
double S = 0;
if (Convert.ToString(dt.Rows[i][0]) == "plitka")
{
S = (Convert.ToDouble(dt3.Rows[0][0]) / 100) * (Convert.ToDouble(dt3.Rows[0][1]) / 100);
cell = new PdfPCell(new Phrase(Convert.ToString(S), new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
table.AddCell(cell);
}
else if (Convert.ToString(dt.Rows[i][0]) == "oboi")
{
S = (Convert.ToDouble(dt3.Rows[0][0]) / 100) * (Convert.ToDouble(dt3.Rows[0][2]) / 100) * 2;
S += (Convert.ToDouble(dt3.Rows[0][1]) / 100) * (Convert.ToDouble(dt3.Rows[0][2]) / 100) * 2;
cell = new PdfPCell(new Phrase(Convert.ToString(S), new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
table.AddCell(cell);
}
sumSM += Convert.ToInt32(dt.Rows[i][2]) * S;
}
doc.Add(table);
table = new PdfPTable(3);
int sumM = 0;
if (dt1.Rows.Count != 0)
{
p = new Phrase("Мебель",
new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.BOLD, new BaseColor(Color.Black)));
a1 = new Paragraph(p);
a1.Alignment = Element.ALIGN_CENTER;
a1.Add(Environment.NewLine);
a1.SpacingAfter = 5;
doc.Add(a1);
cell = new PdfPCell(new Phrase("Наименование", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.Colspan = 1;
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Название", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Цена", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
for (int i = 0; i < dt1.Rows.Count; i++)
{
for (int j = 0; j < dt1.Columns.Count; j++)
{
if (j == 0)
{
cell = new PdfPCell(new Phrase(dict[Convert.ToString(dt1.Rows[i][j])], new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
}
else
{
cell = new PdfPCell(new Phrase(Convert.ToString(dt1.Rows[i][j]), new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
}
table.AddCell(cell);
}
sumM += Convert.ToInt32(dt1.Rows[i][2]);
}
}
doc.Add(table);
table = new PdfPTable(3);
int sumT = 0;
if (dt2.Rows.Count != 0)
{
p = new Phrase("Техника",
new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.BOLD, new BaseColor(Color.Black)));
a1 = new Paragraph(p);
a1.Alignment = Element.ALIGN_CENTER;
a1.Add(Environment.NewLine);
a1.SpacingAfter = 5;
doc.Add(a1);
cell = new PdfPCell(new Phrase("Наименование", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.Colspan = 1;
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Название", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Цена", new iTextSharp.text.Font(baseFont,
12, iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
table.AddCell(cell);
for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
if (j == 0)
{
cell = new PdfPCell(new Phrase(dict[Convert.ToString(dt2.Rows[i][j])], new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
}
else
{
cell = new PdfPCell(new Phrase(Convert.ToString(dt2.Rows[i][j]), new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black))));
}
table.AddCell(cell);
}
sumT += Convert.ToInt32(dt2.Rows[i][2]);
}
}
doc.Add(table);
Phrase p1 = new Phrase(" Параметры комнаты: " + Convert.ToString(Convert.ToDouble(dt3.Rows[0][0]) / 100) + " м * " +
Convert.ToString(Convert.ToDouble(dt3.Rows[0][1]) / 100) + " м * " + Convert.ToString(Convert.ToDouble(dt3.Rows[0][2]) / 100) + " м",
new iTextSharp.text.Font(baseFont, 14,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black)));
Paragraph a2 = new Paragraph(p1);
a2.Alignment = Element.ALIGN_LEFT;
a2.Add(Environment.NewLine);
doc.Add(a2);
p1 = new Phrase(" Сумма строительных материалов: " + Convert.ToString(sumSM),
new iTextSharp.text.Font(baseFont, 14,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black)));
a2 = new Paragraph(p1);
a2.Alignment = Element.ALIGN_LEFT;
a2.Add(Environment.NewLine);
doc.Add(a2);
p1 = new Phrase(" Сумма техники: " + Convert.ToString(sumT),
new iTextSharp.text.Font(baseFont, 14,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black)));
a2 = new Paragraph(p1);
doc.Add(a2);
p1 = new Phrase(" Сумма мебели: " + Convert.ToString(sumM),
new iTextSharp.text.Font(baseFont, 14,
iTextSharp.text.Font.NORMAL, new BaseColor(Color.Black)));
a2 = new Paragraph(p1);
doc.Add(a2);
int allSum = Convert.ToInt32(sumM + sumT + sumSM);
p1 = new Phrase(" Общая сумма: " + Convert.ToString(allSum),
new iTextSharp.text.Font(baseFont, 14,
iTextSharp.text.Font.BOLD, new BaseColor(Color.Black)));
a2 = new Paragraph(p1);
a2.SpacingAfter = 30;
doc.Add(a2);
a2.SpacingAfter = 30;
DateTime second = DateTime.Now;
Phrase p2 = new Phrase("Дата формирования заказа: " + second,
new iTextSharp.text.Font(baseFont, 12,
iTextSharp.text.Font.ITALIC, new BaseColor(Color.Black)));
a2 = new Paragraph(p2);
a2.Alignment = Element.ALIGN_RIGHT;
doc.Add(a2);
doc.Close();
DataForBD.Summa = allSum;
}