139
Chart1.Series[0]["PieDrawingStyle"] =
"SoftEdge";
dr.Close();
}
else//если выбрано по отделам
{
command = new SqlCommand("SELECT
CONCAT(сумма,' ', Отдел) AS Отдел, Summa AS Сумма FROM
(SELECT ResponsibleDepartment AS Отдел,
Replace(Format(SUM(Sum), 'N'),',',' ') AS Сумма, SUM(Sum) AS
Summa FROM DZ WHERE Month='" +
comboBoxMonthToChart.SelectedValue.ToString() + "' GROUP BY
ResponsibleDepartment) AS th", sqlConnection);
SqlDataReader dr =
command.ExecuteReader();
Chart1.DataBindTable(dr, "Отдел");
Chart1.Series[0].ChartType =
SeriesChartType.Pie;
Chart1.Series[0].IsVisibleInLegend =
false;
Chart1.Series[0]["PieLabelStyle"] =
"Outside";
Chart1.ChartAreas[0].Area3DStyle.Enable3D
= true;
Chart1.Series[0]["PieDrawingStyle"] =
"SoftEdge";
dr.Close();
}
}
private void timer1_Tick(object sender,
System.EventArgs e)
{
if (checkBoxRotate.Checked)
{
angle += 1;
140
if (angle >= 360)
{
angle = 0;
}
Chart1.Series[0]["PieStartAngle"] =
angle.ToString();
}
}
private void graf()
{//задаем стиль для графика
System.Windows.Forms.DataVisualization.Charting.ChartArea
chartArea1 = new
System.Windows.Forms.DataVisualization.Charting.ChartArea();
System.Windows.Forms.DataVisualization.Charting.Series series1
= new
System.Windows.Forms.DataVisualization.Charting.Series();
this.chart3 = new
System.Windows.Forms.DataVisualization.Charting.Chart();
this.SuspendLayout();
chartArea1.AxisX.LineColor =
System.Drawing.Color.FromArgb(((int)(((byte)(64)))),
((int)(((byte)(64)))), ((int)(((byte)(64)))),
((int)(((byte)(64)))));
chartArea1.AxisX.MajorGrid.LineColor =
System.Drawing.Color.FromArgb(((int)(((byte)(64)))),
((int)(((byte)(64)))), ((int)(((byte)(64)))),
((int)(((byte)(64)))));
chartArea1.AxisY.LabelStyle.Font = new
System.Drawing.Font("Trebuchet MS", 8.25F,
System.Drawing.FontStyle.Bold);
chartArea1.AxisY.LineColor =
System.Drawing.Color.FromArgb(((int)(((byte)(64)))),
((int)(((byte)(64)))), ((int)(((byte)(64)))),
((int)(((byte)(64)))));
141
chartArea1.AxisY.MajorGrid.LineColor =
System.Drawing.Color.FromArgb(((int)(((byte)(64)))),
((int)(((byte)(64)))), ((int)(((byte)(64)))),
((int)(((byte)(64)))));
chartArea1.BackColor =
System.Drawing.Color.FromArgb(((int)(((byte)(64)))),
((int)(((byte)(165)))), ((int)(((byte)(191)))),
((int)(((byte)(228)))));
chartArea1.BackGradientStyle =
System.Windows.Forms.DataVisualization.Charting.GradientStyle.
TopBottom;
chartArea1.BackSecondaryColor =
System.Drawing.Color.White;
chartArea1.BorderColor =
System.Drawing.Color.FromArgb(((int)(((byte)(64)))),
((int)(((byte)(64)))), ((int)(((byte)(64)))),
((int)(((byte)(64)))));
chartArea1.BorderDashStyle =
System.Windows.Forms.DataVisualization.Charting.ChartDashStyle
.Solid;
chartArea1.Name = "Default";
chartArea1.Position.Auto = false;
chartArea1.Position.Height = 94F;
chartArea1.Position.Width = 94F;
chartArea1.Position.X = 2F;
chartArea1.Position.Y = 2F;
chartArea1.ShadowColor =
System.Drawing.Color.Transparent;
this.chart3.ChartAreas.Add(chartArea1);
this.BackColor = System.Drawing.Color.White;
this.ResumeLayout(false);
}
private void UpdateChartSettings()
{
// Loop through all series
142
foreach (Series series in chart3.Series)
{
// Set series chart type
series.ChartType =
(SeriesChartType)Enum.Parse(typeof(SeriesChartType), "Line",
true);
}
}
private void DataForGraf()
{
// Populate series data
SqlCommand command = new SqlCommand("SELECT
right(convert(varchar, Month, 106), 8) AS Месяц, SUM(Sum) AS
Сумма FROM DZ GROUP BY Month ORDER BY Month", sqlConnection);
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
List<string> axisLabel = new List<string>();
string sdf = "";
for (int pointIndex = 0; pointIndex <
ds.Tables[0].Rows.Count; pointIndex++)
{
chart3.Series["Series1"].Points.AddY(ds.Tables[0].Rows[pointIn
dex][1]);
chart3.ChartAreas[0].AxisX.Title =
"Месяц";
chart3.ChartAreas[0].AxisY.Title =
"Сумма";
chart3.Series["Series1"].Points[pointIndex].AxisLabel =
ds.Tables[0].Rows[pointIndex][0].ToString();
}
UpdateChartSettings();
}
143
List<string> listColumns = new List<string>(new
string[] { "Unit AS Почтамт, ", "KAY AS 'Код аналитического
учета', ",
"Client AS Клиент, ", "Contract AS Договор, ",
"NumberOfContract AS 'Номер договора', ",
"ContractDate AS 'Дата договора', ", "INN AS
ИНН, ", "ResponsibleBlock AS 'Ответственный блок', ",
"ResponsibleDepartment AS 'Ответственный
отдел', ", "Comment AS Комментарии, ", "KindOfDZ AS Вид, "});
List<string> listColumnsInDZ = new
List<string>(new string[] { "Unit, ", "KAY, ",
"Client, ", "Contract, ", "NumberOfContract,
", "ContractDate, ", "INN, ", "ResponsibleBlock, ",
"ResponsibleDepartment, ", "Comment, ",
"KindOfDZ, "});
private void
checkedListBox1_SelectedIndexChanged(object sender, EventArgs
e)
{
string namesColumns = null;
string namesColumnsGROUP = null;
columnSelection(0, 1);
columnSelection(1, 2);
columnSelection(2, 3);
columnSelection(3, 4);
columnSelection(4, 5);
columnSelection(5, 6);
columnSelection(6, 8);
columnSelection(7, 9);
columnSelection(8, 10);
columnSelection(9, 12);
columnSelection(10, 13);
foreach (int indexChecked in
checkedListBox1.CheckedIndices)
{
144
namesColumns += listColumns[indexChecked];
}
foreach (int indexChecked in
checkedListBox1.CheckedIndices)
{
namesColumnsGROUP +=
listColumnsInDZ[indexChecked];
}
//перестраиваем таблицу
string MonthName =
comboBoxMonth2.SelectedValue.ToString();
SqlCommand command = new SqlCommand("SELECT "
+ namesColumns.Remove(namesColumns.Length - 2) + ",
Replace(Format(SUM(Sum), 'N'),',',' ') AS Сумма FROM DZ WHERE
Month='" + MonthName + "' GROUP BY " +
namesColumnsGROUP.Remove(namesColumnsGROUP.Length - 2) + " ",
sqlConnection);//создаем новый запрос к БД без указанных полей
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds, "DZ");
dataGridView1.DataSource = ds.Tables[0];
double balans = 0;
foreach (DataGridViewRow row in
dataGridView1.Rows)
{
double incom;
double.TryParse(row.Cells[7].Value.ToString().Replace(".",
","), out incom);
balans += incom;//пересчитываем сумму
}
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberGroupSeparator = " ";
145
textBox1.Text = balans.ToString("N", nfi);
}
private void columnSelection(int i1, int i2)
{
if (checkedListBox1.GetItemChecked(i1))
dataGridView1.Columns[i2].Visible = true;
else dataGridView1.Columns[i2].Visible =
false;
}
private void button6_Click(object sender,
EventArgs e)
{
DataForGraf();
graf();
}//построение графика
private void AddComment(object sender, EventArgs
e)
{
OpenFileDialog ope = new OpenFileDialog();
ope.Filter = "Excel
Files|*.xls;*.xlsx;*.xlsm";
if (ope.ShowDialog() == DialogResult.Cancel)
return;
DataTable table =
LoadExcelSheetToTable(ope.FileName, "Лист1");
foreach (DataRow row in table.Rows)
{
SqlCommand command = new
SqlCommand("UPDATE DZ SET Comment=@Comment WHERE Id=@Id",
sqlConnection);
command.Parameters.AddWithValue("Id",
Convert.ToString(row[0]));
146
command.Parameters.AddWithValue("Comment",
Convert.ToString(row[13]));
command.ExecuteNonQuery();
}
MessageBox.Show(
"Комментарии успешно импортированы. " +
"\n" + "Обновлено " + table.Rows.Count + " записей",
"Сообщение",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
Form1_Load(sender, e);
}
public void makePDF(string Name, string
NumberOfContract, string ContractDate, string Sum)
{
string existingFile =
Path.Combine(Environment.GetFolderPath(Environment.SpecialFold
er.Desktop), "file1.pdf");//указываем шаблон
string newFile =
Path.Combine(Environment.GetFolderPath(Environment.SpecialFold
er.Desktop), Name+".pdf");//указываем путь и название нового
файла
PdfReader reader = new
PdfReader(existingFile);//читаем pdf
byte[] bytes;
using (MemoryStream ms = new
MemoryStream())//создаем поток
{
using (PdfStamper stamper = new
PdfStamper(reader, ms))
{
PdfContentByte cb =
stamper.GetOverContent(1);//записываем даные поверх шаблона
147
ColumnText ct = new
ColumnText(cb);//разметка с помощью ColumnText
ColumnText ct1 = new ColumnText(cb);
ColumnText ct2 = new ColumnText(cb);
ColumnText ct3 = new ColumnText(cb);
ct.SetSimpleColumn(100, 100, 563,
563);//располагаем
ct.AddElement(new
Paragraph(NumberOfContract));//добавляем текст
ct1.SetSimpleColumn(380, 380, 563,
563);
ct1.AddElement(new
Paragraph(ContractDate));
ct2.SetSimpleColumn(335, 335, 540,
540);
ct2.AddElement(new Paragraph(Sum));
ct3.SetSimpleColumn(380, 380, 800,
800);
ct3.AddElement(new Paragraph(Name));
ct.Go();
ct1.Go();
ct2.Go();
ct3.Go();
stamper.Close();//закрваем поток
bytes = ms.ToArray();//записываем в
массив
}
}
using (FileStream fs = new FileStream(newFile,
FileMode.Create, FileAccess.Write,
FileShare.None))//записываем изменени
{
fs.Write(bytes, 0, bytes.Length);
}
}
148
private void dataGridView2_DoubleClick(object
sender, EventArgs e)
{
string Name=dataGridView2[3,
dataGridView2.CurrentCell.RowIndex].Value.ToString();//по
номеру выбранной строки, ищем имя киента, договор, и сумму
string Contract= dataGridView2[4,
dataGridView2.CurrentCell.RowIndex].Value.ToString();
string NumberOfContract = dataGridView2[5,
dataGridView2.CurrentCell.RowIndex].Value.ToString();
string ContractDate = dataGridView2[6,
dataGridView2.CurrentCell.RowIndex].Value.ToString();
string Sum = dataGridView2[7,
dataGridView2.CurrentCell.RowIndex].Value.ToString();
ContractDate=ContractDate.Substring(0, 10);
//MessageBox.Show(dataGridView2.CurrentCell.RowIndex.ToString(
));
//MessageBox.Show(Name + Contract +
NumberOfContract + ContractDate + Sum);
makePDF(Name, NumberOfContract, ContractDate,
Sum);
}
}
}