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";

            SQLiteCommand Command = new SQLiteCommand(Sql, Globals.DBConnection);
            CultureInfo IVC = CultureInfo.InvariantCulture;
            Command.Parameters.Add(new SQLiteParameter("@startdate", dtpStartDate.Value.ToString("yyyy-MM-dd 00:00:00", IVC)));
            Command.Parameters.Add(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";
            SQLiteCommand Command = new SQLiteCommand(Sql, Globals.DBConnection);
            CultureInfo IVC = CultureInfo.InvariantCulture;
            Command.Parameters.Add(new SQLiteParameter("@startdate", dtpStartDate.Value.ToString("yyyy-MM-dd 00:00:00", IVC)));
            Command.Parameters.Add(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)
        {

        }
    }
}