示例#1
0
文件: frmMain.cs 项目: slagovskiy/psa
        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);
                }
            }
        }
示例#2
0
文件: frmMain.cs 项目: slagovskiy/psa
        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);
                }
            }
        }