BulkPrintingClient/BulkPrinting/BulkPrinting/Utility.cs

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Management;
using System.Net;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using System.Windows.Forms;

namespace BulkPrinting
{
    public class Utility
    {
        public const int WM_USER = 0x400;

        [DllImport("user32.dll", SetLastError = true)]
        public static extern bool PostMessage(HandleRef hWnd, uint Msg, IntPtr wParam, IntPtr lParam);

        public static string GetHDDSerial()
        {
            ManagementObjectSearcher managementObjectSearcher = new ManagementObjectSearcher("SELECT * FROM Win32_PhysicalMedia");
            string result;
            ManagementObjectCollection.ManagementObjectEnumerator enumerator = null;
            try
            {
                enumerator = managementObjectSearcher.Get().GetEnumerator();
                while (enumerator.MoveNext())
                {
                    string text = ((ManagementObject)enumerator.Current)["SerialNumber"].ToString();
                    text = text.Replace(" ", "").Replace(".", "");
                    if (text != "")
                    {
                        result = text;
                        return result.Length > 15 ? result.Substring(result.Length - 15, 15) : result;
                    }
                }
            }
            finally
            {
                if (enumerator != null)
                {
                    ((IDisposable)enumerator).Dispose();
                }
            }
            result = "SERIAL ERROR";
            return result.Length > 15 ? result.Substring(result.Length - 15, 15) : result;
        }

        public static DBHelper OpenDBConnection()
        {
            return new DBHelper(String.Format("Data Source={0};", Globals.MaxDBFilePath), Globals.SessionDatabasePassword);
        }

        public static bool Login(LoginData UserLoginData, bool Offline, bool RememberMe) {
            string MaxDBPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), Configuration.MaxDataPathName);
            string PostData = JsonConvert.SerializeObject(UserLoginData);
            byte[] data = Encoding.ASCII.GetBytes(PostData);

            HttpWebRequest request = WebRequest.Create(Configuration.ServerBaseURL + "/api/login") as HttpWebRequest;
            request.ServerCertificateValidationCallback = delegate { return true; };

            request.Method = "POST";
            request.ContentType = "application/json";
            request.ContentLength = data.Length;
            request.Accept = "application/json";

            try
            {
                using (var stream = request.GetRequestStream())
                {
                    stream.Write(data, 0, data.Length);
                }
                string responseString;
                using (var response = (HttpWebResponse)request.GetResponse())
                {
                    responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                }
                Globals.SessionData = JsonConvert.DeserializeObject<OKResponse>(responseString);
                if (RememberMe == true)
                {
                    SaveSetting("Username", UserLoginData.Username);
                    SaveSetting("UserID", UserLoginData.UserId.ToString());
                }
                else {
                    SaveSetting("Username", "");
                    SaveSetting("UserID", "");
                }
                SaveSetting("VendorID", UserLoginData.VendorId.ToString());

                Globals.SessionEncryptedDatabasePassword = Globals.SessionData.Credentials.Payload.EncryptedDatabasePassword;
                Globals.SessionSalt = Globals.SessionData.Credentials.Salt;
                Globals.SessionIterations = Globals.SessionData.Credentials.Iterations;

                return true;
            }
            catch (WebException ex)
            {
                if (ex.Response != null)
                {
                    var response = ex.Response;
                    var stream = response.GetResponseStream();
                    var reader = new StreamReader(stream);
                    var message = reader.ReadToEnd();
                    MaxException MaxError = JsonConvert.DeserializeObject<MaxException>(message);
                    if (MaxError.Code != null)
                    {
                        MessageBox.Show("Error " + MaxError.Code.ToString() + ": " + MaxError.Error, "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                    else
                    {
                        MessageBox.Show("Login Failed. Please try again.", "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                else
                {
                    MessageBox.Show("The server did not return a response to the login request. Please try again later.", "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                return false;
            }
        }

        public static bool RESTRequest<R>(ref R Result, string RESTPath)
        {
            return RESTRequest<bool?, R>(null, ref Result, RESTPath);
        }

        public static bool RESTRequest<T, R>(T POSTData, ref R Result, string RESTPath)
        {
            HttpWebRequest request = WebRequest.Create(Configuration.ServerBaseURL + RESTPath) as HttpWebRequest;
            request.AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip;
            request.ServerCertificateValidationCallback = delegate { return true; };
            request.Headers.Add("Authorization", "Bearer " + Globals.SessionData.AccessToken);
            request.ContentType = "application/json";
            request.Accept = "application/json";
            try
            {
                if (POSTData != null)
                {
                    string PostData = JsonConvert.SerializeObject(POSTData);

                    byte[] data = Encoding.ASCII.GetBytes(PostData);
                    request.ContentLength = data.Length;
                    request.Method = "POST";
                    using (var stream = request.GetRequestStream())
                    {
                        stream.Write(data, 0, data.Length);
                    }
                }
                else {
                    request.Method = "GET";
                }
                string responseString;
                using (var response = (HttpWebResponse)request.GetResponse())
                {
                    responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                }
                Result = JsonConvert.DeserializeObject<R>(responseString);
                return true;
            }
            catch (WebException ex)
            {
                if (ex != null && ex.Response != null)
                {
                    var response = ex.Response;
                    HttpStatusCode? status = (response as HttpWebResponse)?.StatusCode;
                    if (status.HasValue && (status == HttpStatusCode.NotFound))
                    {
                        Result = default(R);
                    }
                    else
                    {
                        var stream = response.GetResponseStream();
                        var reader = new StreamReader(stream);
                        var message = reader.ReadToEnd();
                        MaxException MaxError = JsonConvert.DeserializeObject<MaxException>(message);
                        if (MaxError.Code != null)
                        {
                            MessageBox.Show("Error " + MaxError.Code.ToString() + ": " + MaxError.Error, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
                return false;
            }
        }

        private static bool SaveSetting(string Key, string Value)
        {
            SavedSettings StoredValues = LoadSavedSettings();
            switch (Key)
            {
                case "Username":
                    StoredValues.Username = Value;
                    break;
                case "UserID":
                    if (Value == "")
                        StoredValues.UserId = 0;
                    else
                        StoredValues.UserId = int.Parse(Value);
                    break;
                case "VendorID":
                    if (Value == "")
                        StoredValues.VendorId = 0;
                    else
                        StoredValues.VendorId = int.Parse(Value);
                    break;
                default:
                    return false;
            }
            string MaxAppDataPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), Configuration.MaxDataPathName);
            string MaxSettingsFilePath = Path.Combine(MaxAppDataPath, "maxsettings.dat");
            File.WriteAllText(MaxSettingsFilePath, JsonConvert.SerializeObject(StoredValues));
            return true;
        }

        public static string LoadSetting(string Key)
        {
            SavedSettings StoredValues = LoadSavedSettings();
            string ReturnVal = "";
            switch (Key)
            {
                case "Username":
                    ReturnVal = StoredValues.Username;
                    break;
                case "UserID":
                    ReturnVal = StoredValues.UserId.ToString();
                    break;
                case "VendorID":
                    ReturnVal = StoredValues.VendorId.ToString();
                    break;
                default:
                    return "";
            }
            if (ReturnVal == "0")
                return "";
            return ReturnVal;
        }

        private static SavedSettings LoadSavedSettings() {
            string MaxAppDataPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), Configuration.MaxDataPathName);
            string MaxSettingsFilePath = Path.Combine(MaxAppDataPath, "maxsettings.dat");
            SavedSettings StoredValues = new SavedSettings();
            if (!Directory.Exists(MaxAppDataPath))
            {
                Directory.CreateDirectory(MaxAppDataPath);
            }
            if (File.Exists(MaxSettingsFilePath)) {
                StoredValues = JsonConvert.DeserializeObject<SavedSettings>(File.ReadAllText(MaxSettingsFilePath));
            }
            else {
                File.WriteAllText(MaxSettingsFilePath, JsonConvert.SerializeObject(StoredValues));
            }
            return StoredValues;
        }
        
        public static byte[] Transform(ICryptoTransform transform, byte[] input)
        {
            using (var memoryStream = new MemoryStream())
            using (var cryptoStream = new CryptoStream(memoryStream, transform, CryptoStreamMode.Write))
            {
                cryptoStream.Write(input, 0, input.Length);
                cryptoStream.FlushFinalBlock();
                return memoryStream.ToArray();
            }
        }

        public static byte[] AesDecryptBytes(byte[] cipherText, byte[] key)
        {
            using (var aes = Aes.Create())
            using (var decryptor = aes.CreateDecryptor(key, new byte[16]))
            {
                return Transform(decryptor, cipherText);
            }
        }

        public static string AesDecryptString(byte[] cipherText, byte[] key)
        {
            return Encoding.ASCII.GetString(AesDecryptBytes(cipherText, key));
        }

        public static string TripleDESDecrypt(string cipherText, TripleDES des)
        {
            using (var decryptor = des.CreateDecryptor(des.Key, des.IV))
            {
                return Encoding.UTF8.GetString(Transform(decryptor, Convert.FromBase64String(cipherText)));
            }
        }

        public static string TripleDESDecrypt(string cipherText, byte[] key)
        {
            using (var des = TripleDES.Create())
            {
                des.Key = key;
                des.IV = new byte[8];
                return TripleDESDecrypt(cipherText, des);
            }
        }

        public static Batch GetBatch(int BatchId) {
            Batch RequestedBatch = new Batch();
            bool OrderResult = RESTRequest<Batch>(ref RequestedBatch, String.Format("/api/batches/{0}", BatchId));
            return RequestedBatch;
        }

        public static string GetNextInternalReference()
        {
            InternalReferenceResponse InternalReferenceRequest = new InternalReferenceResponse();
            bool OrderResult = RESTRequest<InternalReferenceResponse>(ref InternalReferenceRequest, "/api/vendors/nextinternalref");
            return InternalReferenceRequest.InternalReference;
        }

        public static void SaveBatch(DBHelper db, Batch batch)
        {
            lock (db.WriteLock)
            {
                using (var trans = db.BeginTransaction())
                {
                    using (var command = db.CreateCommand(trans))
                    {
                        command.Parameters.AddWithValue("@Id", batch.Id);
                        command.Parameters.AddWithValue("@OrderDate", batch.OrderDate);
                        command.Parameters.AddWithValue("@OrderGuid", batch.OrderGuid);
                        command.Parameters.AddWithValue("@OrderReference", batch.OrderReference);
                        command.Parameters.AddWithValue("@NetworkId", batch.NetworkId);
                        command.Parameters.AddWithValue("@NetworkName", batch.NetworkName);
                        command.Parameters.AddWithValue("@ProductId", batch.ProductId);
                        command.Parameters.AddWithValue("@ProductDescription", batch.ProductDescription);
                        command.Parameters.AddWithValue("@VoucherType", batch.VoucherType);
                        command.Parameters.AddWithValue("@FaceValue", batch.FaceValue);
                        command.Parameters.AddWithValue("@DiscountPercentage", batch.DiscountPercentage);
                        command.Parameters.AddWithValue("@RequestedQuantity", batch.RequestedQuantity);
                        command.Parameters.AddWithValue("@DeliveredQuantity", batch.DeliveredQuantity);
                        command.Parameters.AddWithValue("@Cost", batch.Cost);
                        command.Parameters.AddWithValue("@ReadyForDownload", batch.ReadyForDownload);
                        command.Parameters.AddWithValue("@InternalReference", batch.InternalReference);

                        // We leave the value of 'Downloaded' unchanged here.
                        command.CommandText =
                            "UPDATE Batch SET OrderDate=@OrderDate, OrderGuid=@OrderGuid, OrderReference=@OrderReference, NetworkId=@NetworkId, " +
                                "NetworkName=@NetworkName, ProductId=@ProductId, ProductDescription=@ProductDescription, VoucherType=@VoucherType, " +
                                "FaceValue=@FaceValue, DiscountPercentage=@DiscountPercentage, RequestedQuantity=@RequestedQuantity, " +
                                "DeliveredQuantity=@DeliveredQuantity, Cost=@Cost, ReadyForDownload=@ReadyForDownload, InternalReference=@InternalReference " +
                                "WHERE Id=@Id";

                        if (command.ExecuteNonQuery() == 0)
                        {
                            // 'Downloaded' is always 0 for new batches.
                            command.CommandText =
                                "INSERT INTO Batch (Id, OrderDate, OrderGuid, OrderReference, NetworkId, NetworkName, ProductId, ProductDescription, VoucherType, FaceValue, DiscountPercentage, RequestedQuantity, DeliveredQuantity, Cost, ReadyForDownload, InternalReference, Downloaded) " +
                                           "VALUES (@Id,@OrderDate,@OrderGuid,@OrderReference,@NetworkId,@NetworkName,@ProductId,@ProductDescription,@VoucherType,@FaceValue,@DiscountPercentage,@RequestedQuantity,@DeliveredQuantity,@Cost,@ReadyForDownload,@InternalReference,0)";
                            command.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();
                }
            }
        }

        public static string GetSavedParameter(DBHelper db, string key)
        {
            return (string)db.ExecuteScalar("SELECT Value FROM Parameters WHERE Key=@Key",
                new SQLiteParameter("@Key", key));
        }

        public static int GetSavedParameterAsInt(DBHelper db, string key)
        {
            return Convert.ToInt32(GetSavedParameter(db, key));
        }

        public static bool GetSavedParameterAsBoolean(DBHelper db, string key)
        {
            return Convert.ToBoolean(GetSavedParameter(db, key));
        }

        public static bool UpdateSavedParameter(DBHelper db, string key, object value)
        {
            return db.ExecuteNonQuery(
                "UPDATE Parameters SET Value=@Value WHERE Key=@Key",
                new SQLiteParameter("@Key", key),
                new SQLiteParameter("@Value", Convert.ToString(value))) > 0;
        }

        public static void PrintVouchers(DBHelper db, int BatchId, int StartSeqNo, int EndSeqNo)
        {

            int VoucherCount = 0;
            int RowCount = 0;
            int PageCount = (int) Math.Ceiling(((decimal)StartSeqNo)/20);
            int TotalCount = 0;
            string SerialNumberTrimmed;
            bool IsReprint;

            var PrinterInitString = new StringBuilder();
            PrinterInitString.Append(Printer.INITIALISE_PRINTER).Append(Printer.EMPHASISE_ON).Append(Printer.UNIDIRECTIONAL_OFF).Append(Printer.CHARPITCHELITE);
            int initJobID = Globals.MaxPrinter.Open("Printer_Init");
            if (initJobID == 0) return;
            Globals.MaxPrinter.Print(PrinterInitString.ToString());
            Globals.MaxPrinter.Close();

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

            IList<PrintVoucher> VoucherRow = new List<PrintVoucher>();
            using (var Command = db.CreateCommand("SELECT DISTINCT v.Id,v.SequenceNumber,v.Serial,v.EncryptedPIN,v.BatchId,b.ProductDescription,l.VoucherId From Voucher v LEFT JOIN Batch b on v.BatchId = b.Id LEFT JOIN Logs l ON v.Id = l.VoucherId AND l.EventType = @eventtype WHERE v.BatchId=@batch_id AND v.SequenceNumber BETWEEN @seqstartno AND @seqendno ORDER BY v.SequenceNumber",
                new SQLiteParameter("@batch_id", BatchId),
                new SQLiteParameter("@eventtype", VendorEvent.VendorEventType.PrintVoucher),
                new SQLiteParameter("@seqstartno", StartSeqNo),
                new SQLiteParameter("@seqendno", EndSeqNo)))
            {
                using (SQLiteDataReader read = Command.ExecuteReader())
                {
                    int JobID = Globals.MaxPrinter.Open("Vouchers");
                    if (JobID == 0) return;
                    while (read.Read())
                    {
                        VoucherCount++;
                        PrintVoucher IndividualVoucher = new PrintVoucher();
                        IndividualVoucher.SequenceNumber = (int)read["SequenceNumber"];
                        IndividualVoucher.BatchId = (int)read["BatchId"];
                        IndividualVoucher.Serial = (string)read["Serial"];
                        IndividualVoucher.VoucherId = (int)read["Id"];
                        IndividualVoucher.Description = (string)read["ProductDescription"];
                        IsReprint = !read.IsDBNull(6);

                        if (IsReprint)
                        {
                            IndividualVoucher.Description = "*" + IndividualVoucher.Description;
                        }
                        IndividualVoucher.DecryptedPIN = TripleDESDecrypt((string)read["EncryptedPIN"], Globals.SessionVoucherKey);


                        VoucherRow.Add(IndividualVoucher);
                        if (VoucherCount >= 5 || TotalCount == (EndSeqNo - StartSeqNo))
                        {
                            RowCount++;
                            string PrintRow = "\r\n\n\n  ";
                            for (int Column = 0; Column < VoucherRow.Count(); Column++)
                            {
                                IndividualVoucher = VoucherRow[Column];
                                PrintRow += (Column == 2 ? " " : "") + ("  " + IndividualVoucher.DecryptedPIN).PadRight(30, ' ');
                            }
                            PrintRow = PrintRow.TrimEnd() + "\r\n\n  ";
                            for (int Column = 0; Column < VoucherRow.Count(); Column++)
                            {
                                IndividualVoucher = VoucherRow[Column];
                                SerialNumberTrimmed = IndividualVoucher.Serial;
                                if (SerialNumberTrimmed.Length > 24)
                                {
                                    SerialNumberTrimmed = SerialNumberTrimmed.Substring(0, 24);
                                }
                                PrintRow += (Column == 2 ? " " : "") + SerialNumberTrimmed.PadRight(30, ' ');
                            }
                            PrintRow = PrintRow.TrimEnd() + "\r\n  ";
                            for (int Column = 0; Column < VoucherRow.Count(); Column++)
                            {
                                IndividualVoucher = VoucherRow[Column];
                                int CurrentPage = (int)Math.Ceiling(((decimal)IndividualVoucher.SequenceNumber) / 20);
                                PrintRow += (Column == 2 ? " " : "") + String.Format("{0}/{1}/{2}", IndividualVoucher.BatchId, IndividualVoucher.SequenceNumber, PageCount).PadRight(30, ' ');
                            }
                            PrintRow = PrintRow.TrimEnd() + "\r\n  ";
                            for (int Column = 0; Column < VoucherRow.Count(); Column++)
                            {
                                IndividualVoucher = VoucherRow[Column];
                                PrintRow += (Column == 2 ? " " : "") + IndividualVoucher.Description.PadRight(30, ' ');
                            }
                            PrintRow = PrintRow.TrimEnd() + "\r\n\n\n\n\n\n\n\n\n\n\n";
                            Globals.MaxPrinter.Print(PrintRow);
                            foreach (PrintVoucher PrintedVoucher in VoucherRow)
                            {
                                var ExportEvent = new EventLog();
                                ExportEvent.EventType = VendorEvent.VendorEventType.PrintVoucher;
                                ExportEvent.VoucherId = PrintedVoucher.VoucherId;
                                ExportEvent.Retry = IsReprint;
                                LogEvents.Add(ExportEvent);
                            }
                            VoucherRow = new List<PrintVoucher>();
                            VoucherCount = 0;
                            if (RowCount >= 4)
                            {
                                //Globals.MaxPrinter.NewPage();
                                PageCount++;
                                RowCount = 0;
                                //Globals.MaxPrinter.GetJobInfo(JobID);
                            }
                        }
                        TotalCount++;
                    }
                    Globals.MaxPrinter.Close();
                }
            }
            LogBulkEvents(db, LogEvents);
        }

        public enum UserPermissions {
            CanPrintOnline, 
            CanReprintOnline,
            CanPrintOffline,
            CanReprintOffline,
            BulkExport,
            BulkOrder,
            BulkViewPins,
            BulkReExport
        }

        public static bool CheckUserAccess(UserPermissions Permission) {
            if (Globals.SessionData.Credentials.Payload.User.Level == (int)UserLevel.Administrator) {
                return true;
            }
            switch (Permission) {
                case UserPermissions.CanPrintOnline:
                    return Globals.SessionData.Credentials.Payload.User.CanPrintOnline;
                case UserPermissions.CanReprintOnline:
                    return Globals.SessionData.Credentials.Payload.User.CanReprintOnline;
                case UserPermissions.CanPrintOffline:
                    return Globals.SessionData.Credentials.Payload.User.CanPrintOffline;
                case UserPermissions.CanReprintOffline:
                    return Globals.SessionData.Credentials.Payload.User.CanReprintOffline;
                case UserPermissions.BulkViewPins:
                    return Globals.SessionData.Credentials.Payload.User.BulkViewPins;
                case UserPermissions.BulkOrder:
                    return Globals.SessionData.Credentials.Payload.User.BulkOrder;
                case UserPermissions.BulkExport:
                    return Globals.SessionData.Credentials.Payload.User.BulkExport;
                case UserPermissions.BulkReExport:
                    return Globals.SessionData.Credentials.Payload.User.BulkReExport;
                default:
                    return false;
            }
        }

        public static void Logout() {
            if (Globals.SessionData != null)
            {
                Globals.DB.ExecuteNonQuery("DELETE FROM SessionData"); //Destroy stored session data
                string SessionDataJson = JsonConvert.SerializeObject(Globals.SessionData);
                Globals.DB.ExecuteNonQuery(
                    "INSERT INTO SessionData (Key,Value) VALUES (@key,@value)",
                    new SQLiteParameter("@key", "SessionDataJson"),
                    new SQLiteParameter("@value", SessionDataJson));
                LogEvent(Globals.DB, VendorEvent.VendorEventType.Logout);

                if (Globals.LogUploader != null)
                    Globals.LogUploader.Cancel();

                if (Globals.LogDownloader != null)
                    Globals.LogDownloader.Cancel();

                if (Globals.LogUploader != null)
                {
                    Globals.LogUploader.Join();
                    Globals.LogUploader = null;
                }

                if (Globals.LogDownloader != null)
                {
                    Globals.LogDownloader.Join();
                    Globals.LogDownloader = null;
                }

                Globals.DB.Close();
                Globals.DB.Dispose();
                Globals.DB = null;
                Globals.SessionData = null;
                Globals.SessionDatabasePassword = null;
                Globals.SessionVoucherKey = null;
                Globals.SessionMode = SessionModes.Invalid;
                Globals.ProductCatalogue = null;
                Globals.MaxPrinter.Close();
                UserLoginForm LoginForm = (UserLoginForm)Application.OpenForms["UserLoginForm"];
                LoginForm.Show();
            }
        }

        public static int GetLastSyncedLogID() {
            VendorEventsMetaData MetaData = new VendorEventsMetaData();
            bool MetaDataResult = RESTRequest<VendorEventsMetaData>(ref MetaData, "/api/vendorevents/meta");
            if (MetaData.LastVendorEventRemoteId == null) {
                return 0;
            }
            return (int)MetaData.LastVendorEventRemoteId;
        }

        public static void CheckLogSynchronisation(DBHelper db)
        {
            if (!GetSavedParameterAsBoolean(db, "LoggingInitialised"))
            {
                var lastSyncedLogId = GetLastSyncedLogID();

                // If we already have logs then there is no need to synchronise.
                // NOTE: logs are synchronised in reverse from SyncBackwardsFromLogId downwards
                if ((long)db.ExecuteScalar("SELECT COUNT(*) FROM Logs") == 0)
                {
                    UpdateSavedParameter(db, "SyncBackwardsFromLogId", lastSyncedLogId);
                }

                // If there are logs on the server then ensure that the log autoincrement value is set to the last
                // log ID received by the server so new local logs don't clash with existing log entries on the server.
                if (lastSyncedLogId != 0)
                {
                    lock (db.WriteLock)
                    {
                        using (var trans = db.BeginTransaction())
                        {
                            using (var command = db.CreateCommand(trans, new SQLiteParameter("@Id", lastSyncedLogId)))
                            {
                                command.CommandText = "UPDATE sqlite_sequence SET seq=CASE WHEN IFNULL(seq, 0)<@Id THEN @Id ELSE seq END WHERE name='Logs'";
                                if (command.ExecuteNonQuery() == 0)
                                {
                                    command.CommandText = "INSERT INTO sqlite_sequence (name, seq) VALUES ('Logs', @id)";
                                    command.ExecuteNonQuery();
                                }
                            }
                            trans.Commit();
                        }
                    }
                }

                UpdateSavedParameter(db, "LoggingInitialised", true);
            }
        }

        public static void TriggerLogDownload()
        {
            if (Globals.LogDownloader == null)
            {
                Globals.LogDownloader = new LogDownloader(Globals.DB);
                Globals.LogDownloader.Start();
            }
        }

        public static void TriggerLogUpload()
        {
            if (Globals.LogUploader == null)
            {
                Globals.LogUploader = new LogUploader(Globals.DB);
                Globals.LogUploader.Start();
            }
            else
            {
                Globals.LogUploader.TriggerUpload();
            }
        }

        public static void LogBulkEvents(DBHelper db, List<EventLog> EventLogs)
        {
            string Sql = "INSERT INTO Logs (UserId, VoucherId, EventDate, EventType, Retry) VALUES (@userid, @voucherid, @eventdate, @eventtype, @retry)";

            lock (db.WriteLock)
            {
                using (var trans = db.BeginTransaction())
                {
                    using (var Command = db.CreateCommand(Sql, trans))
                    {
                        foreach (var EventLog in EventLogs)
                        {
                            Command.Parameters.Clear();
                            Command.Parameters.AddWithValue("@userid", Globals.SessionData.Credentials.Payload.User.Id);
                            Command.Parameters.AddWithValue("@voucherid", EventLog.VoucherId);
                            Command.Parameters.AddWithValue("@eventdate", DateTime.UtcNow);
                            Command.Parameters.AddWithValue("@eventtype", EventLog.EventType);
                            Command.Parameters.AddWithValue("@retry", EventLog.Retry);
                            Command.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();
                }
            }

            if (Globals.SessionMode == SessionModes.Online)
            {
                TriggerLogUpload();
            }
        }

        public static void LogEvent(DBHelper db, VendorEvent.VendorEventType EventType, int? VoucherId = null, bool Retry = false)
        {
            db.ExecuteNonQuery(
                "INSERT INTO Logs (UserId,  VoucherId,  EventDate,  EventType,  Retry) " +
                          "VALUES (@userid, @voucherid, @eventdate, @eventtype, @retry)",
                new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id),
                new SQLiteParameter("@voucherid", VoucherId),
                new SQLiteParameter("@eventdate", DateTime.UtcNow),
                new SQLiteParameter("@eventtype", EventType),
                new SQLiteParameter("@retry", Retry));
            if (Globals.SessionMode == SessionModes.Online)
            {
                TriggerLogUpload();
            }
        }

        private static void SaveCurrentUserUsage(DBHelper db) {
            db.ExecuteNonQuery(
                "DELETE FROM AccessControlTracking WHERE UserID = @userid",
                new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id));

            db.ExecuteNonQuery(
                "INSERT INTO AccessControlTracking (UserID, Date, Permission, CurrentUsage) VALUES " +
                    "(@userid, datetime('now'), 'OfflinePrint', @offlineprintvalue)," +
                    "(@userid, datetime('now'), 'OfflineReprint', @offlinereprintvalue)," +
                    "(@userid, datetime('now'), 'OnlinePrint', @onlineprintvalue)," +
                    "(@userid, datetime('now'), 'OnlineReprint', @onlinereprintvalue)," +
                    "(@userid, datetime('now'), 'BulkOrder', @bulkorder)," +
                    "(@userid, datetime('now'), 'BulkExport', @bulkexport)",
                new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id),
                new SQLiteParameter("@offlineprintvalue", Globals.UserCurrentUsage.OfflinePrintValue),
                new SQLiteParameter("@offlinereprintvalue", Globals.UserCurrentUsage.OfflineReprintValue),
                new SQLiteParameter("@onlineprintvalue", Globals.UserCurrentUsage.OnlinePrintValue),
                new SQLiteParameter("@onlinereprintvalue", Globals.UserCurrentUsage.OnlineReprintValue),
                new SQLiteParameter("@bulkorder", Globals.UserCurrentUsage.BulkOrderValue),
                new SQLiteParameter("@bulkexport", Globals.UserCurrentUsage.BulkExportValue));
        }

        public static void AddUserUsage(DBHelper db, UserLimits.UserLimitTypes UserLimitType, decimal Value) {
            DateTime ServerDate = Globals.SessionData.Credentials.Payload.Date;
            DateTime Today = DateTime.Now;
            if (ServerDate.Date != Today.Date) //prevent system time tampering
            {
                MessageBox.Show("Date mismatch detected. Logging out.", "Server Date Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Logout();
                return;
            }
            switch (UserLimitType) {
                case UserLimits.UserLimitTypes.OfflinePrint:
                    Globals.UserCurrentUsage.OfflinePrintValue += Value;
                    break;
                case UserLimits.UserLimitTypes.OfflineReprint:
                    Globals.UserCurrentUsage.OfflineReprintValue += Value;
                    break;
                case UserLimits.UserLimitTypes.OnlinePrint:
                    Globals.UserCurrentUsage.OnlinePrintValue += Value;
                    break;
                case UserLimits.UserLimitTypes.OnlineReprint:
                    Globals.UserCurrentUsage.OnlineReprintValue += Value;
                    break;
                case UserLimits.UserLimitTypes.BulkExport:
                    Globals.UserCurrentUsage.BulkExportValue += Value;
                    break;
                case UserLimits.UserLimitTypes.BulkOrder:
                    Globals.UserCurrentUsage.BulkOrderValue += Value;
                    break;
            }

            //Immediately write to db to avoid cheating
            SaveCurrentUserUsage(db);
        }

        public static Boolean IsValueWithinRemainingUserLimit(UserLimits.UserLimitTypes UserLimitType, decimal Value) {
            decimal RemainingUserLimit = CheckRemainingUserLimit(UserLimitType);
            if (RemainingUserLimit == -1 || RemainingUserLimit >= Value) {
                return true;
            }
            return false;
        }


        public static decimal CheckRemainingUserLimit(UserLimits.UserLimitTypes UserLimitType) {
            if (Globals.SessionData.Credentials.Payload.User.Level == (int)UserLevel.Administrator)
            {
                return -1; //Signifies unlimited
            }
            switch (UserLimitType)
            {
                case UserLimits.UserLimitTypes.OfflinePrint:
                    if (Globals.SessionData.Credentials.Payload.User.CanPrintOffline)
                    {
                        if (Globals.SessionData.Credentials.Payload.User.OfflinePrintValue > 0) //Unlimited from server is signified by 0, but remaining limit at 0 means limit reached, so change unlimited to -1 for internal use
                        {
                            return Globals.SessionData.Credentials.Payload.User.OfflinePrintValue - Globals.UserCurrentUsage.OfflinePrintValue;
                        }
                        else
                        {
                            return -1; //Signifies unlimited
                        }
                    }
                    else
                    {
                        return 0;
                    }
                case UserLimits.UserLimitTypes.OfflineReprint:
                    if (Globals.SessionData.Credentials.Payload.User.CanReprintOffline)
                    {
                        if (Globals.SessionData.Credentials.Payload.User.OfflineReprintValue > 0) //Unlimited from server is signified by 0, but remaining limit at 0 means limit reached, so change unlimited to -1 for internal use
                        {
                            return Globals.SessionData.Credentials.Payload.User.OfflineReprintValue - Globals.UserCurrentUsage.OfflineReprintValue;
                        }
                        else
                        {
                            return -1; //Signifies unlimited
                        }
                    }
                    else
                    {
                        return 0;
                    }
                case UserLimits.UserLimitTypes.OnlinePrint:
                    if (Globals.SessionData.Credentials.Payload.User.CanPrintOnline)
                    {
                        if (Globals.SessionData.Credentials.Payload.User.OnlinePrintValue > 0) //Unlimited from server is signified by 0, but remaining limit at 0 means limit reached, so change unlimited to -1 for internal use
                        {
                            return Globals.SessionData.Credentials.Payload.User.OnlinePrintValue - Globals.UserCurrentUsage.OnlinePrintValue;
                        }
                        else
                        {
                            return -1; //Signifies unlimited
                        }
                    }
                    else
                    {
                        return 0;
                    }
                case UserLimits.UserLimitTypes.OnlineReprint:
                    if (Globals.SessionData.Credentials.Payload.User.CanReprintOnline)
                    {
                        if (Globals.SessionData.Credentials.Payload.User.OnlineReprintValue > 0) //Unlimited from server is signified by 0, but remaining limit at 0 means limit reached, so change unlimited to -1 for internal use
                        {
                            return Globals.SessionData.Credentials.Payload.User.OnlineReprintValue - Globals.UserCurrentUsage.OnlineReprintValue;
                        }
                        else
                        {
                            return -1; //Signifies unlimited
                        }
                    }
                    else
                    {
                        return 0;
                    }
                case UserLimits.UserLimitTypes.BulkExport:
                    if (Globals.SessionData.Credentials.Payload.User.BulkExport)
                    {
                        if (Globals.SessionData.Credentials.Payload.User.BulkExportMaxValue > 0) //Unlimited from server is signified by 0, but remaining limit at 0 means limit reached, so change unlimited to -1 for internal use
                        {
                            return Globals.SessionData.Credentials.Payload.User.BulkExportMaxValue - Globals.UserCurrentUsage.BulkExportValue;
                        }
                        else
                        {
                            return -1; //Signifies unlimited
                        }
                    }
                    else
                    {
                        return 0;
                    }
                case UserLimits.UserLimitTypes.BulkOrder:
                    if (Globals.SessionData.Credentials.Payload.User.BulkOrder)
                    {
                        if (Globals.SessionData.Credentials.Payload.User.BulkOrderMaxValue > 0) //Unlimited from server is signified by 0, but remaining limit at 0 means limit reached, so change unlimited to -1 for internal use
                        {
                            return Globals.SessionData.Credentials.Payload.User.BulkOrderMaxValue - Globals.UserCurrentUsage.BulkOrderValue;
                        }
                        else
                        {
                            return -1; //Signifies unlimited
                        }
                    }
                    else
                    {
                        return 0;
                    }
            }
            return 0;
        }

        private static int GetVoucherPrintCountFromLogs(DBHelper db, int VoucherId)
        {
            return int.Parse(db.ExecuteScalar(
                "SELECT COUNT(*) FROM Logs WHERE VoucherId=@voucherid AND EventType=@eventtype",
                new SQLiteParameter("@voucherid", VoucherId),
                new SQLiteParameter("@eventtype", (int)VendorEvent.VendorEventType.PrintVoucher)).ToString());
        }

        private static DateTime GetVoucherFirstPrintDateFromLogs(DBHelper db, int VoucherId)
        {
            return (DateTime)db.ExecuteScalar(
                "SELECT MIN(EventDate) FROM Logs WHERE VoucherId=@voucherid AND EventType=@eventtype",
                new SQLiteParameter("@voucherid", VoucherId),
                new SQLiteParameter("@eventtype", (int)VendorEvent.VendorEventType.PrintVoucher));
        }

        private static decimal GetVoucherFaceValue(DBHelper db, int VoucherId)
        {
            return (decimal)db.ExecuteScalar(
                "SELECT b.FaceValue FROM Voucher v LEFT JOIN Batch b on v.BatchId = b.Id WHERE v.Id=@voucherid",
                new SQLiteParameter("@voucherid", VoucherId));
        }


        private static UserLimits CalculateUsageFromLogs(DBHelper db) {
            UserLimits CalculateUsage = new UserLimits();
            CalculateUsage.OfflinePrintValue = 0;
            CalculateUsage.OfflineReprintValue = 0;
            CalculateUsage.OnlinePrintValue = 0;
            CalculateUsage.OnlineReprintValue = 0;
            var CurrentDate = DateTime.Today.Date;
            CultureInfo IVC = CultureInfo.InvariantCulture;
            string Sql = "SELECT VoucherId FROM Logs WHERE UserId=@userid AND EventDate BETWEEN @eventdatea AND @eventdateb AND EventType=@eventtype";
            using (var Command = db.CreateCommand(Sql,
                new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id),
                new SQLiteParameter("@eventdatea", CurrentDate.ToString("yyyy-MM-dd 00:00:00", IVC)),
                new SQLiteParameter("@eventdateb", CurrentDate.ToString("yyyy-MM-dd 23:59:59", IVC)),
                new SQLiteParameter("@eventtype", (int)VendorEvent.VendorEventType.PrintVoucher)))
            {
                int VoucherId;
                decimal VoucherFaceValue;
                using (SQLiteDataReader read = Command.ExecuteReader())
                {
                    while (read.Read())
                    {
                        VoucherId = read.GetInt32(0);
                        VoucherFaceValue = GetVoucherFaceValue(db, VoucherId);
                        //Don't distinguish between online and offline usage for log recalculations
                        CalculateUsage.OfflinePrintValue += VoucherFaceValue;
                        CalculateUsage.OnlinePrintValue += VoucherFaceValue;
                        if (GetVoucherPrintCountFromLogs(db, VoucherId) > 1)
                        { //For reprinted vouchers just assume all prints were reprints - log recalculations should be stricter to prevent cheating
                            CalculateUsage.OfflineReprintValue += VoucherFaceValue;
                            CalculateUsage.OnlineReprintValue += VoucherFaceValue;
                        }
                    }
                }
            }
            return CalculateUsage;
        }

        public static void InitialiseUserLimits(DBHelper db)
        {
            Globals.UserCurrentUsage = new UserLimits();
            UserLimits CurrentUserLimits = new UserLimits();

            int Result = int.Parse(db.ExecuteScalar(
                "SELECT COUNT(*) FROM AccessControlTracking WHERE UserID = @userid",
                new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id)).ToString());

            object ResultDate = db.ExecuteScalar(
                "SELECT Date FROM AccessControlTracking WHERE UserID = @userid",
                new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id));

            bool DatePassed = false;
            if (ResultDate != null) {
                DateTime DateResult = DateTime.Parse(ResultDate.ToString());
                if (DateResult.Date < Globals.SessionData.Credentials.Payload.Date.Date) {
                    DatePassed = true;
                }
            }
            if (DatePassed)
            { //Fresh day since last db write or data missing - fetch usage from logs incase of tampering
                Globals.UserCurrentUsage = CalculateUsageFromLogs(db);
                SaveCurrentUserUsage(db); //Immediately write calculated usage to DB
            }
            else
            {
                if (Result == 6)
                {
                    var Sql = "SELECT UserID, Permission, CurrentUsage FROM AccessControlTracking WHERE UserID = @userid";
                    using (var Command = db.CreateCommand(Sql,
                        new SQLiteParameter("@userid", Globals.SessionData.Credentials.Payload.User.Id)))
                    {
                        using (SQLiteDataReader read = Command.ExecuteReader())
                        {
                            while (read.Read())
                            {
                                switch (read["Permission"])
                                {
                                    case "OfflinePrint":
                                        Globals.UserCurrentUsage.OfflinePrintValue = (decimal)read["CurrentUsage"];
                                        break;
                                    case "OfflineReprint":
                                        Globals.UserCurrentUsage.OfflineReprintValue = (decimal)read["CurrentUsage"];
                                        break;
                                    case "OnlinePrint":
                                        Globals.UserCurrentUsage.OnlinePrintValue = (decimal)read["CurrentUsage"];
                                        break;
                                    case "OnlineReprint":
                                        Globals.UserCurrentUsage.OnlineReprintValue = (decimal)read["CurrentUsage"];
                                        break;
                                    case "BulkOrder":
                                        Globals.UserCurrentUsage.BulkOrderValue = (decimal)read["CurrentUsage"];
                                        break;
                                    case "BulkExport":
                                        Globals.UserCurrentUsage.BulkExportValue = (decimal)read["CurrentUsage"];
                                        break;
                                }
                            }
                        }
                    }
                }
                else
                {
                    Globals.UserCurrentUsage = CalculateUsageFromLogs(db);
                    SaveCurrentUserUsage(db); //Immediately write calculated usage to DB
                }
            }
        }

        public static int GetNumberOfUnprintedVouchersInRange(DBHelper db, int StartSeqNo, int EndSeqNo, int BatchId) {
            int UnprintedVouchers = 0;
            int VoucherId;
            string Sql = "SELECT Id FROM Voucher WHERE SequenceNumber=@sequencenumber AND BatchId=@batchid";
            using (var Command = db.CreateCommand(Sql))
            {
                for (int SeqNo = StartSeqNo; SeqNo <= EndSeqNo; SeqNo++)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.AddWithValue("@sequencenumber", SeqNo);
                    Command.Parameters.AddWithValue("@batchid", BatchId);
                    VoucherId = int.Parse(Command.ExecuteScalar().ToString());
                    int NumPrintedVouchers = GetVoucherPrintCountFromLogs(db, VoucherId);
                    if (NumPrintedVouchers == 0)
                    {
                        UnprintedVouchers++;
                    }
                }
            }
            return UnprintedVouchers;
        }
    }
}