BulkPrintingClient/BulkPrinting/BulkPrinting/ExportForm.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 ExportForm : Form
    {
        public enum ExportMode {
            Export,
            Reexport
        }

        private ExportMode SelectedMode;

        private class BatchItem {
            public int Id;
            public string Description;
            public int Quantity;
            public decimal FaceValue;
            public override string ToString()
            {
                return "[" + Id.ToString() + "] " + this.Description + " (" + Quantity.ToString() + " Vouchers)";
            }
        }
        public ExportForm(ExportMode Mode)
        {
            SelectedMode = Mode;
            InitializeComponent();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void PopulateBatchList() {
            string Sql = "SELECT b.Id,b.FaceValue,b.ProductDescription,b.DeliveredQuantity,COUNT(l.Id) AS Exported,COUNT(l2.Id) AS Printed FROM Batch b LEFT JOIN Voucher v on v.BatchId = b.Id LEFT JOIN Logs l on v.Id = l.VoucherId AND l.EventType=@exportevent LEFT JOIN Logs l2 on v.Id = l2.VoucherId AND l2.EventType=@printevent WHERE ReadyForDownload=1 AND b.OrderDate BETWEEN @startdate AND @enddate";
            if (cmbNetwork.SelectedItem.ToString() != "All") {
                Sql += " AND b.NetworkName=@networkname";
            }
            Sql += " GROUP BY b.Id,b.FaceValue,b.ProductDescription,b.DeliveredQuantity";
            using (var Command = Globals.DB.CreateCommand(Sql))
            {
                if (cmbNetwork.SelectedItem.ToString() != "All")
                {
                    Command.Parameters.AddWithValue("@networkname", cmbNetwork.SelectedItem.ToString());
                }
                CultureInfo IVC = CultureInfo.InvariantCulture;
                Command.Parameters.AddWithValue("@startdate", dtpStartDate.Value.Date.ToString("yyyy-MM-dd 00:00:00", IVC));
                Command.Parameters.AddWithValue("@enddate", dtpEndDate.Value.Date.ToString("yyyy-MM-dd 23:59:59", IVC));
                Command.Parameters.AddWithValue("@printevent", VendorEvent.VendorEventType.PrintVoucher);
                Command.Parameters.AddWithValue("@exportevent", VendorEvent.VendorEventType.ExportVoucher);
                lstBatchList.Items.Clear();
                using (SQLiteDataReader read = Command.ExecuteReader())
                {
                    while (read.Read())
                    {
                        if (read["Printed"].ToString() == "0")
                        {
                            if ((SelectedMode == ExportMode.Export && read["Exported"].ToString() != "0") ||
                                (SelectedMode == ExportMode.Reexport && read["Exported"].ToString() == "0"))
                            {
                                continue;
                            }
                            BatchItem NewBatchItem = new BatchItem();
                            NewBatchItem.Id = int.Parse(read["Id"].ToString());
                            NewBatchItem.Description = read["ProductDescription"].ToString();
                            NewBatchItem.Quantity = int.Parse(read["DeliveredQuantity"].ToString());
                            NewBatchItem.FaceValue = int.Parse(read["FaceValue"].ToString());
                            lstBatchList.Items.Add(NewBatchItem);
                        }
                    }
                }
            }
        }

        private void ExportForm_Load(object sender, EventArgs e)
        {
            if (SelectedMode == ExportMode.Export)
            {
                this.Text = "Export Vouchers";
                lblBatchType.Text = "New Batches";
            }
            else if (SelectedMode == ExportMode.Reexport)
            {
                this.Text = "Re-export Vouchers";
                lblBatchType.Text = "Previously Exported Batches";
            }
            string Sql = "SELECT DISTINCT NetworkName FROM Batch";
            using (var Command = Globals.DB.CreateCommand(Sql))
            {
                cmbNetwork.Items.Clear();
                cmbNetwork.Items.Add("All");
                using (SQLiteDataReader read = Command.ExecuteReader())
                {
                    while (read.Read())
                    {
                        cmbNetwork.Items.Add(read["NetworkName"]);
                    }
                }
            }
            cmbNetwork.SelectedItem="All";
            dtpStartDate.Value = DateTime.Now.AddMonths(-3);
            dtpEndDate.Value = DateTime.Now;
            PopulateBatchList();
        }

        private void btnAddBatch_Click(object sender, EventArgs e)
        {
            if (this.lstBatchList.SelectedIndex == -1)
            {
                return;
            }
            lstSelectedBatches.Items.Add(lstBatchList.SelectedItem);
            lstBatchList.Items.Remove(lstBatchList.SelectedItem);
        }

        private void btnRemoveBatch_Click(object sender, EventArgs e)
        {
            if (this.lstSelectedBatches.SelectedIndex == -1)
            {
                return;
            }
            lstBatchList.Items.Add(lstSelectedBatches.SelectedItem);
            lstSelectedBatches.Items.Remove(lstSelectedBatches.SelectedItem);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            if (lstSelectedBatches.Items.Count == 0)
            {
                MessageBox.Show("Please select at least one batch from the left list to add to the export list on the right.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            using (var PathDialog = new FolderBrowserDialog())
            {
                DialogResult Result = PathDialog.ShowDialog();
                if (Result == DialogResult.OK && !string.IsNullOrWhiteSpace(PathDialog.SelectedPath))
                {
                    //using (var logger = new Utility.BulkLogger())
                    //{

                    List<EventLog> LogEvents = new List<EventLog>();

                    string Sql = "SELECT v.Id,v.ExpiryDate,v.Serial,v.EncryptedPIN,b.FaceValue,v.BatchId,v.SequenceNumber,b.ProductDescription,COUNT(l.Id) AS Exports FROM Voucher v LEFT JOIN Logs l ON v.Id = l.VoucherId AND l.EventType=@eventtype LEFT JOIN Batch b ON v.BatchId=b.Id WHERE BatchId=@batchid GROUP BY v.Id,v.ExpiryDate,v.Serial,v.EncryptedPIN,b.FaceValue,v.BatchId,v.SequenceNumber,b.ProductDescription";
                    using (var Command = Globals.DB.CreateCommand(Sql))
                    {
                        foreach (BatchItem ExportBatch in lstSelectedBatches.Items)
                        {
                            /*  -Id
                                -Expiry
                                -Serial
                                -PIN
                                -Face Value
                                -BatchID
                                -SQN
                                -Description
                            */
                            if (Command.Parameters.Count == 0)
                            {
                                Command.Parameters.AddWithValue("@batchid", ExportBatch.Id);
                                Command.Parameters.AddWithValue("@eventtype", VendorEvent.VendorEventType.ExportVoucher);
                            }
                            else
                            {
                                Command.Parameters[0].Value = ExportBatch.Id;
                                Command.Parameters[1].Value = VendorEvent.VendorEventType.ExportVoucher;
                            }
                            // Command.Prepare(); // < -- internal
                            using (StreamWriter sw = File.CreateText(Path.Combine(PathDialog.SelectedPath, "RG_" + ExportBatch.Id.ToString() + "_" + ExportBatch.Description + "_" + ExportBatch.FaceValue.ToString() + ".txt")))
                            {
                                sw.WriteLine("ID|Expiry|Serial|PIN|Value|BatchId|SQN|Description");
                                using (SQLiteDataReader read = Command.ExecuteReader())
                                {
                                    while (read.Read())
                                    {
                                        string DecryptedPIN = Utility.TripleDESDecrypt(read["EncryptedPIN"].ToString(), Globals.SessionVoucherKey).ToString();
                                        string ExportRow = string.Format("{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}",
                                                     read["Id"].ToString(),
                                                     DateTime.Parse(read["ExpiryDate"].ToString()).ToString("dd/MM/yyyy"),
                                                     read["Serial"].ToString(),
                                                     DecryptedPIN,
                                                     read["FaceValue"].ToString(),
                                                     read["BatchId"].ToString(),
                                                     read["SequenceNumber"].ToString(),
                                                     read["ProductDescription"].ToString());
                                        sw.WriteLine(ExportRow);
                                        var ExportEvent = new EventLog();
                                        ExportEvent.EventType = VendorEvent.VendorEventType.ExportVoucher;
                                        ExportEvent.VoucherId = int.Parse(read["Id"].ToString());
                                        ExportEvent.Retry = (read["Exports"].ToString() != "0");
                                        LogEvents.Add(ExportEvent);
                                    }
                                }
                            }
                        }
                    }
                    Utility.LogBulkEvents(Globals.DB, LogEvents);
                }
                MessageBox.Show("Vouchers Exported");
                this.Close();
            }
        }
        

        private void cmbNetwork_SelectedIndexChanged(object sender, EventArgs e)
        {
            PopulateBatchList();
        }

        private void dtpStartDate_CloseUp(object sender, EventArgs e)
        {
            PopulateBatchList();
        }

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