private void ShowReport(string rep_name, bool export, C1.Win.C1Report.FileFormatEnum format)
{
if (CheckState(db_connection))
{
try
{
if (prop.PathReportsTemplates != "")
{
{
bool ok = false;
DataTable r = new DataTable("Report");
SqlCommand c;
SqlDataAdapter a;
switch (rep_name)
{
case "Admin full order":
{
frmGetDateIntervalTypeDate f = new frmGetDateIntervalTypeDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
switch (f.txtDateType.SelectedValue.ToString())
{
case "1":
{
filter = "WHERE CONVERT(datetime, [input_date], 120)>=CONVERT(DATETIME, '" + f.Y1 + "." + f.M1 + "." + f.D1 +
" 00:00:00.000" + "', 120) AND CONVERT(datetime, [input_date], 120)<=CONVERT(DATETIME, '" + f.Y2 +
"." +
f.M2 + "." + f.D2 + " 23:59:59.999" + "', 120) ";
break;
}
case "2":
{
filter = "WHERE CONVERT(datetime, [output_date], 120)>=CONVERT(DATETIME, '" + f.Y1 + "." + f.M1 + "." +
f.D1 +
" 00:00:00.000" + "', 120) AND CONVERT(datetime, [output_date], 120)<=CONVERT(DATETIME, '" + f.Y2 +
"." +
f.M2 + "." + f.D2 + " 23:59:59.999" + "', 120) ";
break;
}
case "3":
{
filter = "WHERE CONVERT(datetime, [expected_date], 120)>=CONVERT(DATETIME, '" + f.Y1 + "." + f.M1 + "." +
f.D1 +
" 00:00:00.000" + "', 120) AND CONVERT(datetime, [expected_date], 120)<=CONVERT(DATETIME, '" +
f.Y2 +
"." +
f.M2 + "." + f.D2 + " 23:59:59.999" + "', 120) ";
break;
}
}
string query =
"SELECT [number], [input_date], [expected_date], [output_date], [client], [category], [status], [date_add], [id_user_add], [name_add], [datework], [name_accept], [name_operator], [name_designer], [name_delivery], [good], [quantity], [actual_quantity], [price], [name_work], [defect_quantity], [user_defect], [defect] FROM [vwAdminFullLog] " +
filter + " ORDER BY [number]";
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Discont percent":
{
/*
SELECT dbo.[order].input_date, dbo.[order].output_date, dbo.[order].number, dbo.[order].discont_percent, dbo.[order].discont_code, dbo.dcard.name, dbo.[order].name_accept, dbo.[order].name_delivery FROM dbo.[order] LEFT OUTER JOIN dbo.dcard ON dbo.[order].discont_code = dbo.dcard.code WHERE (dbo.[order].status = N'100000') AND dbo.[order].discont_percent > 0 AND (dbo.[order].input_date > CONVERT(DATETIME, '2000-01-01 00:00:00', 102) AND dbo.[order].input_date < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
filter =
"AND (dbo.[order].input_date >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.[order].input_date <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT dbo.[order].input_date, dbo.[order].output_date, dbo.[order].number, dbo.[order].discont_percent, dbo.[order].discont_code, dbo.dcard.name, dbo.[order].name_accept, dbo.[order].name_delivery FROM dbo.[order] LEFT OUTER JOIN dbo.dcard ON dbo.[order].discont_code = dbo.dcard.code WHERE (dbo.[order].status = N'100000') AND dbo.[order].discont_percent > 0 " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Bonus":
{
/*
SELECT dbo.[order].input_date, dbo.[order].output_date, dbo.[order].number, dbo.dcard.name, dbo.[order].name_accept, dbo.[order].name_delivery, dbo.[order].bonus, dbo.dcard.code FROM dbo.[order] LEFT OUTER JOIN dbo.dcard ON dbo.[order].discont_code = dbo.dcard.code WHERE (dbo.[order].status = N'100000') AND (dbo.[order].bonus > 0) AND (dbo.dcard.typebonus IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M')) AND (dbo.[order].input_date > CONVERT(DATETIME, '2000-01-01 00:00:00', 102) AND dbo.[order].input_date < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
filter =
"AND (dbo.[order].input_date >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.[order].input_date <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT dbo.[order].input_date, dbo.[order].output_date, dbo.[order].number, dbo.dcard.name, dbo.[order].name_accept, dbo.[order].name_delivery, dbo.[order].bonus, dbo.dcard.code FROM dbo.[order] LEFT OUTER JOIN dbo.dcard ON dbo.[order].discont_code = dbo.dcard.code WHERE (dbo.[order].status = N'100000') AND (dbo.[order].bonus > 0) AND (dbo.dcard.typebonus IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M')) " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Cupon":
{
/*
SELECT dbo.[order].input_date, dbo.[order].output_date, dbo.[order].number, dbo.dcard.name, dbo.[order].name_accept, dbo.[order].name_delivery, dbo.[order].bonus, dbo.dcard.code FROM dbo.[order] LEFT OUTER JOIN dbo.dcard ON dbo.[order].discont_code = dbo.dcard.code WHERE (dbo.[order].status = N'100000') AND (dbo.[order].bonus > 0) AND (dbo.dcard.typebonus IN ('N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')) AND (dbo.[order].input_date > CONVERT(DATETIME, '2000-01-01 00:00:00', 102) AND dbo.[order].input_date < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
filter =
"AND (dbo.[order].input_date >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.[order].input_date <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT dbo.[order].input_date, dbo.[order].output_date, dbo.[order].number, dbo.dcard.name, dbo.[order].name_accept, dbo.[order].name_delivery, dbo.[order].bonus, dbo.dcard.code FROM dbo.[order] LEFT OUTER JOIN dbo.dcard ON dbo.[order].discont_code = dbo.dcard.code WHERE (dbo.[order].status = N'100000') AND (dbo.[order].bonus > 0) AND (dbo.dcard.typebonus IN ('N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')) " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Defect":
{
/*
SELECT TOP (100) PERCENT dbo.orderbody.datework, dbo.[order].number, dbo.defect.defect_name, dbo.orderbody.name_work, dbo.orderbody.user_defect, dbo.orderbody.defect_quantity, dbo.orderbody.defect_ok FROM dbo.orderbody LEFT OUTER JOIN dbo.defect ON dbo.orderbody.tech_defect = dbo.defect.defect_code LEFT OUTER JOIN dbo.[order] ON dbo.orderbody.id_order = dbo.[order].id_order WHERE (dbo.orderbody.tech_defect > 0) AND (dbo.orderbody.datework > CONVERT(DATETIME, '2000-01-01 00:00:00', 102) AND dbo.orderbody.datework < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDateDefect f = new frmReportSelectDateDefect();
f.db_connection = db_connection;
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
filter =
"AND (dbo.orderbody.datework >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.orderbody.datework <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
if (f.txtDefect.SelectedValue.ToString().Trim() != "-1")
filter += " AND (dbo.orderbody.tech_defect = " +
f.txtDefect.SelectedValue.ToString().Trim() + ")";
string query =
"SELECT TOP (100) PERCENT dbo.orderbody.datework, dbo.[order].number, dbo.defect.defect_name, dbo.orderbody.name_work, dbo.orderbody.user_defect, dbo.orderbody.defect_quantity, dbo.orderbody.defect_ok, dbo.good.name AS good FROM dbo.orderbody LEFT OUTER JOIN dbo.good ON dbo.orderbody.id_good = dbo.good.id_good LEFT OUTER JOIN dbo.defect ON dbo.orderbody.tech_defect = dbo.defect.defect_code LEFT OUTER JOIN dbo.[order] ON dbo.orderbody.id_order = dbo.[order].id_order WHERE (dbo.orderbody.tech_defect > 0) " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Input work":
{
/*
SELECT datecnt.dated, order_1.name_accept, COUNT(order_1.id_order) AS cnt, datecnt.cntd FROM (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, input_date)) AS dated, COUNT(id_order) AS cntd FROM dbo.[order] GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, input_date))) AS datecnt INNER JOIN dbo.[order] AS order_1 ON datecnt.dated = DATEADD(dd, 0, DATEDIFF(dd, 0, order_1.input_date)) GROUP BY datecnt.dated, datecnt.cntd, order_1.name_accept HAVING (datecnt.dated > CONVERT(DATETIME, '2000-01-01 00:00:00', 102) AND datecnt.dated < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
filter =
" (datecnt.dated >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND datecnt.dated <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT datecnt.dated, order_1.name_accept, COUNT(order_1.id_order) AS cnt, datecnt.cntd FROM (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, input_date)) AS dated, COUNT(id_order) AS cntd FROM dbo.[order] GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, input_date))) AS datecnt INNER JOIN dbo.[order] AS order_1 ON datecnt.dated = DATEADD(dd, 0, DATEDIFF(dd, 0, order_1.input_date)) GROUP BY datecnt.dated, datecnt.cntd, order_1.name_accept HAVING " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Input work service":
{
/*
SELECT TOP (100) PERCENT datecnt.dated, orderbody_1.name_add, dbo.good.name, SUM(orderbody_1.quantity) AS cnt, datecnt.cntd FROM dbo.good INNER JOIN dbo.orderbody AS orderbody_1 ON dbo.good.id_good = orderbody_1.id_good INNER JOIN (SELECT TOP (100) PERCENT DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) AS dated, good_1.id_good, SUM(dbo.orderbody.quantity) AS cntd FROM dbo.orderbody INNER JOIN dbo.good AS good_1 ON dbo.orderbody.id_good = good_1.id_good GROUP BY good_1.id_good, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) HAVING (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) > CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))ORDER BY dated) AS datecnt ON orderbody_1.id_good = datecnt.id_good AND DATEADD(dd, 0, DATEDIFF(dd, 0, orderbody_1.dateadd)) = datecnt.dated GROUP BY datecnt.dated, orderbody_1.name_add, dbo.good.name, datecnt.cntd HAVING (datecnt.dated > CONVERT(DATETIME, '2000-01-01 00:00:00', 102) AND datecnt.dated < CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) */
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (datecnt.dated >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND datecnt.dated <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
filter2 =
" (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT TOP (100) PERCENT datecnt.dated, orderbody_1.name_add, dbo.good.name, SUM(orderbody_1.quantity) AS cnt, datecnt.cntd FROM dbo.good INNER JOIN dbo.orderbody AS orderbody_1 ON dbo.good.id_good = orderbody_1.id_good INNER JOIN (SELECT TOP (100) PERCENT DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) AS dated, good_1.id_good, SUM(dbo.orderbody.quantity) AS cntd FROM dbo.orderbody INNER JOIN dbo.good AS good_1 ON dbo.orderbody.id_good = good_1.id_good GROUP BY good_1.id_good, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.dateadd)) HAVING " + filter2 + " ORDER BY dated) AS datecnt ON orderbody_1.id_good = datecnt.id_good AND DATEADD(dd, 0, DATEDIFF(dd, 0, orderbody_1.dateadd)) = datecnt.dated GROUP BY datecnt.dated, orderbody_1.name_add, dbo.good.name, datecnt.cntd HAVING " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Work by service":
{
/*
SELECT TOP (100) PERCENT datecnt.dated, orderbody_1.name_work, dbo.good.name, SUM(orderbody_1.actual_quantity) AS cnt, datecnt.cntd FROM dbo.good INNER JOIN dbo.orderbody AS orderbody_1 ON dbo.good.id_good = orderbody_1.id_good INNER JOIN (SELECT TOP (100) PERCENT DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) AS dated, good_1.id_good, SUM(dbo.orderbody.actual_quantity) AS cntd FROM dbo.orderbody INNER JOIN dbo.good AS good_1 ON dbo.orderbody.id_good = good_1.id_good GROUP BY good_1.id_good, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) HAVING (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) > CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) < CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) ORDER BY dated) AS datecnt ON orderbody_1.id_good = datecnt.id_good AND DATEADD(dd, 0, DATEDIFF(dd, 0, orderbody_1.datework)) = datecnt.dated WHERE (dbo.good.type = N'1') GROUP BY datecnt.dated, orderbody_1.name_work, dbo.good.name, datecnt.cntd HAVING (SUM(orderbody_1.actual_quantity) > 0) AND (datecnt.dated > CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) AND (datecnt.dated < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (datecnt.dated >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND datecnt.dated <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
filter2 =
" (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT TOP (100) PERCENT datecnt.dated, orderbody_1.name_work, dbo.good.name, SUM(orderbody_1.actual_quantity) AS cnt, datecnt.cntd FROM dbo.good INNER JOIN dbo.orderbody AS orderbody_1 ON dbo.good.id_good = orderbody_1.id_good INNER JOIN (SELECT TOP (100) PERCENT DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) AS dated, good_1.id_good, SUM(dbo.orderbody.actual_quantity) AS cntd FROM dbo.orderbody INNER JOIN dbo.good AS good_1 ON dbo.orderbody.id_good = good_1.id_good GROUP BY good_1.id_good, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) HAVING " + filter2 + " ORDER BY dated) AS datecnt ON orderbody_1.id_good = datecnt.id_good AND DATEADD(dd, 0, DATEDIFF(dd, 0, orderbody_1.datework)) = datecnt.dated WHERE (dbo.good.type <> N'2') GROUP BY datecnt.dated, orderbody_1.name_work, dbo.good.name, datecnt.cntd HAVING (SUM(orderbody_1.actual_quantity) > 0) AND " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Work by service 1":
{
/*
SELECT TOP (100) PERCENT datecnt.dated, orderbody_1.name_work, dbo.good.name, SUM(orderbody_1.actual_quantity) AS cnt, datecnt.cntd FROM dbo.good INNER JOIN dbo.orderbody AS orderbody_1 ON dbo.good.id_good = orderbody_1.id_good INNER JOIN (SELECT TOP (100) PERCENT DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) AS dated, good_1.id_good, SUM(dbo.orderbody.actual_quantity) AS cntd FROM dbo.orderbody INNER JOIN dbo.good AS good_1 ON dbo.orderbody.id_good = good_1.id_good GROUP BY good_1.id_good, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) HAVING (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) > CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) < CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) ORDER BY dated) AS datecnt ON orderbody_1.id_good = datecnt.id_good AND DATEADD(dd, 0, DATEDIFF(dd, 0, orderbody_1.datework)) = datecnt.dated WHERE (dbo.good.type = N'1') GROUP BY datecnt.dated, orderbody_1.name_work, dbo.good.name, datecnt.cntd HAVING (SUM(orderbody_1.actual_quantity) > 0) AND (datecnt.dated > CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) AND (datecnt.dated < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
*/
frmReportSelectDate f = new frmReportSelectDate();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (datecnt.dated >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND datecnt.dated <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
filter2 =
" (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) >= CONVERT(DATETIME, '" +
f.txtDateBegin.Value.Year.ToString("D4") + "-" +
f.txtDateBegin.Value.Month.ToString("D2") + "-" +
f.txtDateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) <= CONVERT(DATETIME, '" +
f.txtDateEnd.Value.Year.ToString("D4") + "-" +
f.txtDateEnd.Value.Month.ToString("D2") + "-" +
f.txtDateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT TOP (100) PERCENT datecnt.dated, orderbody_1.name_work, dbo.good.name, SUM(orderbody_1.actual_quantity) AS cnt, datecnt.cntd FROM dbo.good INNER JOIN dbo.orderbody AS orderbody_1 ON dbo.good.id_good = orderbody_1.id_good INNER JOIN (SELECT TOP (100) PERCENT DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) AS dated, good_1.id_good, SUM(dbo.orderbody.actual_quantity) AS cntd FROM dbo.orderbody INNER JOIN dbo.good AS good_1 ON dbo.orderbody.id_good = good_1.id_good GROUP BY good_1.id_good, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.orderbody.datework)) HAVING " + filter2 + " ORDER BY dated) AS datecnt ON orderbody_1.id_good = datecnt.id_good AND DATEADD(dd, 0, DATEDIFF(dd, 0, orderbody_1.datework)) = datecnt.dated WHERE (dbo.good.type = N'2') GROUP BY datecnt.dated, orderbody_1.name_work, dbo.good.name, datecnt.cntd HAVING (SUM(orderbody_1.actual_quantity) > 0) AND " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Kassa":
{
frmSelectDateIntervalKassa f = new frmSelectDateIntervalKassa();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (dbo.payments.date >= CONVERT(DATETIME, '" +
f.dateBegin.Value.Year.ToString("D4") + "-" +
f.dateBegin.Value.Month.ToString("D2") + "-" +
f.dateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.payments.date <= CONVERT(DATETIME, '" +
f.dateEnd.Value.Year.ToString("D4") + "-" +
f.dateEnd.Value.Month.ToString("D2") + "-" +
f.dateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT SUM(dbo.payments.payment) AS SM, dbo.category.name FROM dbo.payments INNER JOIN dbo.[order] ON dbo.payments.number = dbo.[order].number INNER JOIN dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN dbo.category ON dbo.client.id_category = dbo.category.id_category WHERE " + filter + " GROUP BY dbo.category.name";
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Kassa full":
{
frmSelectDateIntervalKassa f = new frmSelectDateIntervalKassa();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (dbo.payments.date >= CONVERT(DATETIME, '" +
f.dateBegin.Value.Year.ToString("D4") + "-" +
f.dateBegin.Value.Month.ToString("D2") + "-" +
f.dateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.payments.date <= CONVERT(DATETIME, '" +
f.dateEnd.Value.Year.ToString("D4") + "-" +
f.dateEnd.Value.Month.ToString("D2") + "-" +
f.dateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT dbo.payments.id_payment, dbo.payments.date as d, dbo.payments.time as t, dbo.payments.id_user, dbo.payments.name_user, dbo.payments.number, dbo.payments.payment, dbo.payments.type, dbo.payments.comment, dbo.payments.payment_way, dbo.payments.exported, dbo.category.name FROM dbo.payments INNER JOIN dbo.[order] ON dbo.payments.number = dbo.[order].number INNER JOIN dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN dbo.category ON dbo.client.id_category = dbo.category.id_category WHERE " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Debet":
{
frmSelectDateIntervalKassa f = new frmSelectDateIntervalKassa();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (dbo.[order].output_date >= CONVERT(DATETIME, '" +
f.dateBegin.Value.Year.ToString("D4") + "-" +
f.dateBegin.Value.Month.ToString("D2") + "-" +
f.dateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.[order].output_date <= CONVERT(DATETIME, '" +
f.dateEnd.Value.Year.ToString("D4") + "-" +
f.dateEnd.Value.Month.ToString("D2") + "-" +
f.dateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT TOP (100) PERCENT BODYSUM.id_order, BODYSUM.sm, dbo.[order].number, dbo.client.name AS client, dbo.client.id_client, dbo.order_status.status_desc, dbo.order_status.order_status, dbo.[order].input_date, dbo.[order].output_date, dbo.[order].advanced_payment, dbo.[order].final_payment FROM (SELECT TOP (100) PERCENT id_order, SUM(actual_quantity * price) AS sm FROM dbo.orderbody GROUP BY id_order HAVING (id_order > 0)) AS BODYSUM INNER JOIN dbo.[order] ON BODYSUM.id_order = dbo.[order].id_order INNER JOIN dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN dbo.category ON dbo.client.id_category = dbo.category.id_category INNER JOIN dbo.order_status ON dbo.[order].status = dbo.order_status.order_status WHERE (dbo.category.id_category > 2) AND (dbo.order_status.order_status = N'100000' OR dbo.order_status.order_status = N'200000') AND " + filter + " ORDER BY client";
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
default:
{
ok = false;
break;
}
}
if (ok)
{
if (export)
{
switch (format)
{
case C1.Win.C1Report.FileFormatEnum.PDF:
{
sdlg.Filter = "Adobe PDF (*.pdf)|*.pdf";
sdlg.ShowDialog();
if (sdlg.FileName != "")
{
rep.RenderToFile(sdlg.FileName, C1.Win.C1Report.FileFormatEnum.PDF);
}
break;
}
case C1.Win.C1Report.FileFormatEnum.Excel:
{
sdlg.Filter = "Microsoft Excel (*.xls)|*.xls";
sdlg.ShowDialog();
if (sdlg.FileName != "")
{
rep.RenderToFile(sdlg.FileName, C1.Win.C1Report.FileFormatEnum.Excel);
}
break;
}
}
}
else
{
PrintPreviewDialog pd = new PrintPreviewDialog();
pd.Document = rep.Document;
pd.ShowDialog();
}
}
}
}
else
{
MessageBox.Show("Не выбран файл шаблонов отчетов", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
ErrorNfo.WriteErrorInfo(ex);
}
}
}
private void ShowReport(string rep_name, bool export, C1.Win.C1Report.FileFormatEnum format)
{
if (CheckState(db_connection))
{
try
{
if (prop.PathReportsTemplates != "")
{
{
bool ok = false;
DataTable r = new DataTable("Report");
SqlCommand c;
SqlDataAdapter a;
switch (rep_name)
{
case "Kassa":
{
frmSelectDateIntervalKassa f = new frmSelectDateIntervalKassa();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (dbo.payments.date >= CONVERT(DATETIME, '" +
f.dateBegin.Value.Year.ToString("D4") + "-" +
f.dateBegin.Value.Month.ToString("D2") + "-" +
f.dateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.payments.date <= CONVERT(DATETIME, '" +
f.dateEnd.Value.Year.ToString("D4") + "-" +
f.dateEnd.Value.Month.ToString("D2") + "-" +
f.dateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT SUM(dbo.payments.payment) AS SM, dbo.category.name FROM dbo.payments INNER JOIN dbo.[order] ON dbo.payments.number = dbo.[order].number INNER JOIN dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN dbo.category ON dbo.client.id_category = dbo.category.id_category WHERE " + filter + " GROUP BY dbo.category.name";
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Kassa full":
{
frmSelectDateIntervalKassa f = new frmSelectDateIntervalKassa();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (dbo.payments.date >= CONVERT(DATETIME, '" +
f.dateBegin.Value.Year.ToString("D4") + "-" +
f.dateBegin.Value.Month.ToString("D2") + "-" +
f.dateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.payments.date <= CONVERT(DATETIME, '" +
f.dateEnd.Value.Year.ToString("D4") + "-" +
f.dateEnd.Value.Month.ToString("D2") + "-" +
f.dateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT dbo.payments.id_payment, dbo.payments.date, dbo.payments.time, dbo.payments.id_user, dbo.payments.name_user, dbo.payments.number, dbo.payments.payment, dbo.payments.type, dbo.payments.comment, dbo.payments.payment_way, dbo.payments.exported, dbo.category.name FROM dbo.payments INNER JOIN dbo.[order] ON dbo.payments.number = dbo.[order].number INNER JOIN dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN dbo.category ON dbo.client.id_category = dbo.category.id_category WHERE " + filter;
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
case "Debet":
{
frmSelectDateIntervalKassa f = new frmSelectDateIntervalKassa();
f.ShowDialog();
if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
{
string filter = "";
string filter2 = "";
filter =
" (dbo.[order].output_date >= CONVERT(DATETIME, '" +
f.dateBegin.Value.Year.ToString("D4") + "-" +
f.dateBegin.Value.Month.ToString("D2") + "-" +
f.dateBegin.Value.Day.ToString("D2") +
" 00:00:00', 102) AND dbo.[order].output_date <= CONVERT(DATETIME, '" +
f.dateEnd.Value.Year.ToString("D4") + "-" +
f.dateEnd.Value.Month.ToString("D2") + "-" +
f.dateEnd.Value.Day.ToString("D2") + " 23:59:59', 102))";
string query =
"SELECT TOP (100) PERCENT BODYSUM.id_order, BODYSUM.sm, dbo.[order].number, dbo.client.name AS client, dbo.client.id_client, dbo.order_status.status_desc, " +
" dbo.order_status.order_status, dbo.[order].input_date, dbo.[order].output_date, dbo.[order].advanced_payment, dbo.[order].final_payment, " +
" BODYSUM.sm - (dbo.[order].advanced_payment + dbo.[order].final_payment) AS dolg, dbo.[user].name " +
"FROM (SELECT TOP (100) PERCENT id_order, SUM(actual_quantity * price) AS sm" +
" FROM dbo.orderbody" +
" GROUP BY id_order" +
" HAVING (id_order > 0)) AS BODYSUM INNER JOIN" +
" dbo.[order] ON BODYSUM.id_order = dbo.[order].id_order INNER JOIN" +
" dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN" +
" dbo.category ON dbo.client.id_category = dbo.category.id_category INNER JOIN" +
" dbo.order_status ON dbo.[order].status = dbo.order_status.order_status INNER JOIN" +
" dbo.[user] ON dbo.[order].id_user_accept = dbo.[user].id_user " +
"WHERE (dbo.category.id_category > 2) AND (dbo.order_status.order_status = N'100000' OR" +
" dbo.order_status.order_status = N'200000') AND " + filter + " " +
"ORDER BY client";
//"SELECT TOP (100) PERCENT BODYSUM.id_order, BODYSUM.sm, dbo.[order].number, dbo.client.name AS client, dbo.client.id_client, dbo.order_status.status_desc, dbo.order_status.order_status, dbo.[order].input_date, dbo.[order].output_date, dbo.[order].advanced_payment, dbo.[order].final_payment FROM (SELECT TOP (100) PERCENT id_order, SUM(actual_quantity * price) AS sm FROM dbo.orderbody GROUP BY id_order HAVING (id_order > 0)) AS BODYSUM INNER JOIN dbo.[order] ON BODYSUM.id_order = dbo.[order].id_order INNER JOIN dbo.client ON dbo.[order].id_client = dbo.client.id_client INNER JOIN dbo.category ON dbo.client.id_category = dbo.category.id_category INNER JOIN dbo.order_status ON dbo.[order].status = dbo.order_status.order_status WHERE (dbo.category.id_category > 2) AND (dbo.order_status.order_status = N'100000' OR dbo.order_status.order_status = N'200000') AND " + filter + " ORDER BY client";
c = new SqlCommand(query, db_connection);
c.CommandTimeout = 9000;
a = new SqlDataAdapter(c);
a.Fill(r);
rep.Load(prop.PathReportsTemplates, rep_name);
rep.DataSource.Recordset = r;
ok = true;
}
break;
}
default:
{
ok = false;
break;
}
}
if (ok)
{
if (export)
{
switch (format)
{
case C1.Win.C1Report.FileFormatEnum.PDF:
{
sdlg.Filter = "Adobe PDF (*.pdf)|*.pdf";
sdlg.ShowDialog();
if (sdlg.FileName != "")
{
rep.RenderToFile(sdlg.FileName, C1.Win.C1Report.FileFormatEnum.PDF);
}
break;
}
case C1.Win.C1Report.FileFormatEnum.Excel:
{
sdlg.Filter = "Microsoft Excel (*.xls)|*.xls";
sdlg.ShowDialog();
if (sdlg.FileName != "")
{
rep.RenderToFile(sdlg.FileName, C1.Win.C1Report.FileFormatEnum.Excel);
}
break;
}
}
}
else
{
PrintPreviewDialog pd = new PrintPreviewDialog();
pd.Document = rep.Document;
pd.ShowDialog();
}
}
}
}
else
{
MessageBox.Show("Не выбран файл шаблонов отчетов", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
ErrorNfo.WriteErrorInfo(ex);
}
}
}