BulkPrintingClient/BulkPrinting/BulkPrinting/OrderReport.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using System.Globalization;

namespace BulkPrinting
{
    public partial class OrderReport : Form
    {
        public OrderReport()
        {
            InitializeComponent();
        }

        private void RefreshReportGrid()
        {
            dgvOrderReport.Rows.Clear();
            dgvOrderReport.Columns.Clear();
            dgvOrderReport.Columns.Add("Reference", "Order Reference");
            dgvOrderReport.Columns.Add("UserId", "User ID");
            dgvOrderReport.Columns.Add("UserName", "User Name");
            dgvOrderReport.Columns.Add("OrderDate", "Order Date");
            dgvOrderReport.Columns.Add("Description", "Description");
            dgvOrderReport.Columns.Add("OrderCost", "Total Order Cost");
            dgvOrderReport.Columns.Add("BalanceAfterOrder", "Balance After Order");
            dgvOrderReport.Columns.Add("Cost", "Individual Product Cost");
            dgvOrderReport.Columns.Add("OrderedQuantity", "Ordered Quantity");
            dgvOrderReport.Columns.Add("DeliveredQuantity", "Delivered Quantity");
            string Sql = "Select o.Id,o.InternalReference,o.UserId,o.UserName,o.OrderDate,oi.Description,o.OrderCost,o.BalanceAfterOrder,oi.Cost,b.RequestedQuantity,b.DeliveredQuantity From Orders o LEFT JOIN OrderedItems oi on oi.OrderId = o.Id LEFT JOIN Batch b ON b.InternalReference = o.InternalReference AND b.ProductId = oi.ProductId WHERE o.OrderDate BETWEEN @startdate AND @enddate";

            CultureInfo IVC = CultureInfo.InvariantCulture;
            using (var Command = Globals.DB.CreateCommand(Sql,
                new SQLiteParameter("@startdate", dtpStartDate.Value.ToString("yyyy-MM-dd 00:00:00", IVC)),
                new SQLiteParameter("@enddate", dtpEndDate.Value.ToString("yyyy-MM-dd 23:59:59", IVC))))
            {

                using (SQLiteDataReader read = Command.ExecuteReader())
                {
                    while (read.Read())
                    {
                        dgvOrderReport.Rows.Add(new object[] {
                            read.GetValue(1).ToString(),
                            read.GetValue(2).ToString(),
                            read.GetValue(3).ToString(),
                            read.GetValue(4).ToString(),
                            read.GetValue(5).ToString(),
                            read.GetValue(6).ToString(),
                            read.GetValue(7).ToString(),
                            read.GetValue(8).ToString(),
                            read.GetValue(9).ToString(),
                            read.GetValue(10).ToString(),
                        });
                    }
                }
            }
        }

        private void OrderReport_Load(object sender, EventArgs e)
        {
            dtpStartDate.Value = DateTime.Now.AddMonths(-3);
        }

        private void dtpStartDate_ValueChanged(object sender, EventArgs e)
        {
            RefreshReportGrid();
        }

        private void dtpEndDate_ValueChanged(object sender, EventArgs e)
        {
            RefreshReportGrid();
        }

        private void btnSaveToCSV_Click(object sender, EventArgs e)
        {
            SaveFileDialog SFD = new SaveFileDialog();
            SFD.Filter = "*.csv|*.*";
            SFD.FilterIndex = 0;
            if (SFD.ShowDialog() == DialogResult.OK)
            {
                using (StreamWriter OutputStream = new StreamWriter(SFD.OpenFile()))
                {
                    for (int j = 0; j < 10; j++)
                    {
                        OutputStream.Write(dgvOrderReport.Columns[j].Name + ",");
                    }
                    for (int i = 0; i < dgvOrderReport.RowCount; i++)
                    {
                        for (int j = 0; j < 10; j++) {
                            OutputStream.Write(dgvOrderReport.Rows[i].Cells[j].Value.ToString() + ",");
                        }
                        OutputStream.Write("\n");
                    }
                }
                this.Close();
            }
        }

        private void btnPrint_Click(object sender, EventArgs e)
        {
            string Sql = "Select o.Id,o.OrderDate,o.InternalReference,o.UserName,o.OrderCost,o.BalanceAfterOrder,oi.Description,oi.Cost,b.RequestedQuantity,b.DeliveredQuantity From Orders o LEFT JOIN OrderedItems oi on oi.OrderId = o.Id LEFT JOIN Batch b ON b.InternalReference = o.InternalReference AND b.ProductId = oi.ProductId WHERE o.OrderDate BETWEEN @startdate AND @enddate ORDER BY o.Id";
            CultureInfo IVC = CultureInfo.InvariantCulture;
            using (var Command = Globals.DB.CreateCommand(Sql,
                new SQLiteParameter("@startdate", dtpStartDate.Value.ToString("yyyy-MM-dd 00:00:00", IVC)),
                new SQLiteParameter("@enddate", dtpEndDate.Value.ToString("yyyy-MM-dd 23:59:59", IVC))))
            {
                int CurrentId;
                int LastId = -1;
                List<OrderList> Orders = new List<OrderList>();
                OrderList IndividualOrder = new OrderList();
                using (SQLiteDataReader read = Command.ExecuteReader())
                {
                    while (read.Read())
                    {
                        CurrentId = int.Parse(read.GetValue(0).ToString());
                        if (CurrentId != LastId)
                        {
                            if (IndividualOrder.OrderReference != null)
                            {
                                Orders.Add(IndividualOrder);
                            }
                            IndividualOrder = new OrderList();
                            IndividualOrder.OrderLines = new List<OrderReportLine>();
                            IndividualOrder.OrderDate = read.GetValue(1).ToString();
                            IndividualOrder.OrderDate = IndividualOrder.OrderDate.Substring(0, IndividualOrder.OrderDate.IndexOf(" "));
                            IndividualOrder.OrderReference = read.GetValue(2).ToString();
                            IndividualOrder.User = read.GetValue(3).ToString();
                            IndividualOrder.OrderCost = read.GetValue(4).ToString();
                            IndividualOrder.Balance = read.GetValue(5).ToString();
                            LastId = CurrentId;
                        }
                        OrderReportLine OrderLine = new OrderReportLine();
                        OrderLine.OrderItem = read.GetValue(6).ToString();
                        OrderLine.Cost = read.GetValue(7).ToString();
                        OrderLine.QtyOrdered = read.GetValue(8).ToString();
                        OrderLine.QtyDelivered = read.GetValue(9).ToString();
                        IndividualOrder.OrderLines.Add(OrderLine);
                    }
                    if (IndividualOrder.OrderReference != null)
                    {
                        Orders.Add(IndividualOrder);
                    }
                }
                Printer.PrintOrderReport(Orders);
            }
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }
    }
}