public FinalizeResult Finalize(FinalizeCommand command)
        {
            if (command.Period == default)
            {
                throw new Exception("Missing parameter: Period");
            }

            if (command.Period.Day != 1)
            {
                throw new Exception("Period must be the first day of the month.");
            }

            var startedAt = DateTime.Now;

            using (var conn = NewConnection())
            {
                conn.Open();

                var step1 = new BillingDataProcessStep1(new Step1Config {
                    Connection = conn, Context = "ProcessRepository.Finalize", Period = command.Period, Now = startedAt, ClientID = 0, IsTemp = false
                });
                var step4 = new BillingDataProcessStep4Subsidy(new Step4Config {
                    Connection = conn, Context = "ProcessRepository.Finalize", Period = command.Period, ClientID = 0
                });

                var writeToolDataProcessResult = new WriteToolDataProcess(WriteToolDataConfig.Create(conn, "ProcessRepository.Finalize", command.Period, 0, 0, Cost.GetToolCosts(command.Period, 0))).Start();
                var writeRoomDataProcessResult = new WriteRoomDataProcess(new WriteRoomDataConfig {
                    Connection = conn, Context = "ProcessRepository.Finalize", Period = command.Period, ClientID = 0, RoomID = 0
                }).Start();
                var writeStoreDataProcessResult = new WriteStoreDataProcess(new WriteStoreDataConfig {
                    Connection = conn, Context = "ProcessRepository.Finalize", Period = command.Period, ClientID = 0, ItemID = 0
                }).Start();
                var populateToolBillingProcessResult    = step1.PopulateToolBilling();
                var populateRoomBillingProcessResult    = step1.PopulateRoomBilling();
                var populateStoreBillingProcessResult   = step1.PopulateStoreBilling();
                var populateSubsidyBillingProcessResult = step4.PopulateSubsidyBilling();

                conn.Close();

                var result = new FinalizeResult(startedAt)
                {
                    Period = command.Period,
                    WriteToolDataProcessResult          = writeToolDataProcessResult,
                    WriteRoomDataProcessResult          = writeRoomDataProcessResult,
                    WriteStoreDataProcessResult         = writeStoreDataProcessResult,
                    PopulateToolBillingProcessResult    = populateToolBillingProcessResult,
                    PopulateRoomBillingProcessResult    = populateRoomBillingProcessResult,
                    PopulateStoreBillingProcessResult   = populateStoreBillingProcessResult,
                    PopulateSubsidyBillingProcessResult = populateSubsidyBillingProcessResult
                };

                return(result);
            }
        }
        public DataResult Data(DataCommand command)
        {
            if (command.Period == default)
            {
                throw new Exception("Missing parameter: Period");
            }

            if (command.Period.Day != 1)
            {
                throw new Exception("Period must be the first day of the month.");
            }

            var startedAt = DateTime.Now;

            using (var conn = NewConnection())
            {
                conn.Open();

                WriteToolDataResult  writeToolDataProcessResult  = null;
                WriteRoomDataResult  writeRoomDataProcessResult  = null;
                WriteStoreDataResult writeStoreDataProcessResult = null;

                if ((command.BillingCategory & BillingCategory.Tool) > 0)
                {
                    writeToolDataProcessResult = new WriteToolDataProcess(WriteToolDataConfig.Create(conn, "ProcessRepository.Data", command.Period, command.ClientID, command.Record, Cost.GetToolCosts(command.Period, command.Record))).Start();
                }

                if ((command.BillingCategory & BillingCategory.Room) > 0)
                {
                    writeRoomDataProcessResult = new WriteRoomDataProcess(new WriteRoomDataConfig {
                        Connection = conn, Context = "ProcessRepository.Data", Period = command.Period, ClientID = command.ClientID, RoomID = command.Record
                    }).Start();
                }

                if ((command.BillingCategory & BillingCategory.Store) > 0)
                {
                    writeStoreDataProcessResult = new WriteStoreDataProcess(new WriteStoreDataConfig {
                        Connection = conn, Context = "ProcessRepository.Data", Period = command.Period, ClientID = command.ClientID, ItemID = command.Record
                    }).Start();
                }

                conn.Close();

                DataResult result = new DataResult(startedAt)
                {
                    WriteToolDataProcessResult  = writeToolDataProcessResult,
                    WriteRoomDataProcessResult  = writeRoomDataProcessResult,
                    WriteStoreDataProcessResult = writeStoreDataProcessResult
                };

                return(result);
            }
        }
        public IEnumerable <IToolData> CreateToolData(DateTime period, int clientId = 0, int resourceId = 0)
        {
            using (var conn = NewConnection())
            {
                conn.Open();

                var proc        = new WriteToolDataProcess(WriteToolDataConfig.Create(conn, "ToolBillingRepository.CreateToolData", period, clientId, resourceId, Cost.GetToolCosts(period, resourceId)));
                var dtExtract   = proc.Extract();
                var dtTransform = proc.Transform(dtExtract);

                var result = dtTransform.AsEnumerable().Select(x => new ToolDataItem
                {
                    ToolDataID                   = x.Field <int>("ToolDataID"),
                    Period                       = x.Field <DateTime>("Period"),
                    ClientID                     = x.Field <int>("ClientID"),
                    ResourceID                   = x.Field <int>("ResourceID"),
                    RoomID                       = x.Field <int?>("RoomID"),
                    ActDate                      = x.Field <DateTime>("ActDate"),
                    AccountID                    = x.Field <int>("AccountID"),
                    Uses                         = x.Field <double>("Uses"),
                    SchedDuration                = x.Field <double>("SchedDuration"),
                    ActDuration                  = x.Field <double>("ActDuration"),
                    OverTime                     = x.Field <double>("OverTime"),
                    Days                         = x.Field <double?>("Days"),
                    Months                       = x.Field <double?>("Months"),
                    IsStarted                    = x.Field <bool>("IsStarted"),
                    ChargeMultiplier             = x.Field <double>("ChargeMultiplier"),
                    ReservationID                = x.Field <int?>("ReservationID"),
                    ChargeDuration               = x.Field <double>("ChargeDuration"),
                    TransferredDuration          = x.Field <double>("TransferredDuration"),
                    MaxReservedDuration          = x.Field <double>("MaxReservedDuration"),
                    ChargeBeginDateTime          = x.Field <DateTime?>("ChargeBeginDateTime"),
                    ChargeEndDateTime            = x.Field <DateTime?>("ChargeEndDateTime"),
                    IsActive                     = x.Field <bool>("IsActive"),
                    IsCancelledBeforeAllowedTime = x.Field <bool?>("IsCancelledBeforeAllowedTime")
                }).ToList();

                conn.Close();

                return(result);
            }
        }
        public UpdateClientBillingResult UpdateClientBilling(UpdateClientBillingCommand command)
        {
            if (command.Period == default)
            {
                throw new Exception("Missing parameter: Period");
            }

            if (command.Period.Day != 1)
            {
                throw new Exception("Period must be the first day of the month.");
            }

            using (var conn = NewConnection())
            {
                conn.Open();

                DateTime now = DateTime.Now;

                DateTime sd = command.Period;
                DateTime ed = command.Period.AddMonths(1);

                var toolDataClean = new WriteToolDataCleanProcess(new WriteToolDataCleanConfig {
                    Connection = conn, Context = "ProcessRepository.UpdateClientBilling", StartDate = sd, EndDate = ed, ClientID = command.ClientID
                });
                var toolData = new WriteToolDataProcess(WriteToolDataConfig.Create(conn, "ProcessRepository.UpdateClientBilling", sd, command.ClientID, 0, Cost.GetToolCosts(sd, 0)));

                var roomDataClean = new WriteRoomDataCleanProcess(new WriteRoomDataCleanConfig {
                    Connection = conn, Context = "ProcessRepository.UpdateClientBilling", StartDate = sd, EndDate = ed, ClientID = command.ClientID
                });
                var roomData = new WriteRoomDataProcess(new WriteRoomDataConfig {
                    Connection = conn, Context = "ProcessRepository.UpdateClientBilling", Period = sd, ClientID = command.ClientID, RoomID = 0
                });

                var pr1 = toolDataClean.Start();
                var pr2 = roomDataClean.Start();

                var pr3 = toolData.Start();
                var pr4 = roomData.Start();

                bool temp = DateTime.Now.FirstOfMonth() == command.Period;

                var step1 = new BillingDataProcessStep1(new Step1Config {
                    Connection = conn, Context = "ProcessRepository.UpdateClientBilling", Period = command.Period, Now = now, ClientID = command.ClientID, IsTemp = temp
                });

                var pr5 = step1.PopulateToolBilling();
                var pr6 = step1.PopulateRoomBilling();

                PopulateSubsidyBillingResult pr7 = null;

                if (!temp)
                {
                    var step4 = new BillingDataProcessStep4Subsidy(new Step4Config {
                        Connection = conn, Context = "ProcessRepository.UpdateClientBilling", Period = command.Period, ClientID = command.ClientID
                    });
                    pr7 = step4.PopulateSubsidyBilling();
                }

                var result = new UpdateClientBillingResult
                {
                    WriteToolDataCleanProcessResult     = pr1,
                    WriteRoomDataCleanProcessResult     = pr2,
                    WriteToolDataProcessResult          = pr3,
                    WriteRoomDataProcessResult          = pr4,
                    PopulateToolBillingProcessResult    = pr5,
                    PopulateRoomBillingProcessResult    = pr6,
                    PopulateSubsidyBillingProcessResult = pr7
                };

                conn.Close();

                return(result);
            }
        }
        public IEnumerable <string> UpdateBilling(UpdateBillingArgs args)
        {
            // updates all billing
            using (var conn = NewConnection())
            {
                conn.Open();

                DateTime startTime = DateTime.Now;

                var result = new List <string>
                {
                    $"Started UpdateBilling at {startTime:yyyy-MM-dd HH:mm:ss}",
                    $"Period: {string.Join(", ", args.Periods.Select(x => x.ToString("yyyy-MM-dd")))}",
                    $"ClientID: {args.ClientID}",
                    $"BillingCategory: {args.BillingCategory}"
                };

                Stopwatch sw;
                DateTime  sd, ed;

                foreach (var p in args.Periods)
                {
                    if (p == default)
                    {
                        throw new Exception("Missing parameter: Period");
                    }

                    if (p.Day != 1)
                    {
                        throw new Exception("Period must be the first day of the month.");
                    }

                    var temp = Utility.IsCurrentPeriod(p);

                    sd = p;
                    ed = p.AddMonths(1);

                    var populateSubsidy = false;

                    sw = new Stopwatch();

                    DateTime now = DateTime.Now;

                    var step1 = new BillingDataProcessStep1(new Step1Config {
                        Connection = conn, Context = "ProcessRepository.UpdateBilling", Period = p, Now = now, ClientID = args.ClientID, IsTemp = temp
                    });

                    if (args.BillingCategory.HasFlag(BillingCategory.Tool))
                    {
                        var toolDataClean = new WriteToolDataCleanProcess(new WriteToolDataCleanConfig {
                            Connection = conn, Context = "ProcessRepository.UpdateBilling", StartDate = sd, EndDate = ed, ClientID = args.ClientID
                        });
                        var toolData = new WriteToolDataProcess(WriteToolDataConfig.Create(conn, "ProcessRepository.UpdateBilling", p, args.ClientID, 0, Cost.GetToolCosts(p, 0)));

                        sw.Restart();
                        toolDataClean.Start();
                        result.Add(string.Format("Completed ToolDataClean in {0}", sw.Elapsed));

                        sw.Restart();
                        toolData.Start();
                        result.Add(string.Format("Completed ToolData in {0}", sw.Elapsed));

                        sw.Restart();
                        step1.PopulateToolBilling();
                        result.Add(string.Format("Completed ToolBilling in {0}", sw.Elapsed));

                        populateSubsidy = true;
                    }

                    if (args.BillingCategory.HasFlag(BillingCategory.Room))
                    {
                        var roomDataClean = new WriteRoomDataCleanProcess(new WriteRoomDataCleanConfig {
                            Connection = conn, Context = "ProcessRepository.UpdateBilling", StartDate = sd, EndDate = ed, ClientID = args.ClientID, RoomID = 0
                        });
                        var roomData = new WriteRoomDataProcess(new WriteRoomDataConfig {
                            Connection = conn, Context = "ProcessRepository.UpdateBilling", Period = p, ClientID = args.ClientID, RoomID = 0
                        });

                        sw.Restart();
                        roomDataClean.Start();
                        result.Add(string.Format("Completed RoomDataClean in {0}", sw.Elapsed));

                        sw.Restart();
                        roomData.Start();
                        result.Add(string.Format("Completed RoomData in {0}", sw.Elapsed));

                        sw.Restart();
                        step1.PopulateRoomBilling();
                        result.Add(string.Format("Completed RoomBilling in {0}", sw.Elapsed));

                        populateSubsidy = true;
                    }

                    if (args.BillingCategory.HasFlag(BillingCategory.Store))
                    {
                        var storeDataClean = new WriteStoreDataCleanProcess(new WriteStoreDataCleanConfig {
                            Connection = conn, Context = "ProcessRepository.UpdateBilling", StartDate = sd, EndDate = ed, ClientID = args.ClientID
                        });
                        var storeData = new WriteStoreDataProcess(new WriteStoreDataConfig {
                            Connection = conn, Context = "ProcessRepository.UpdateBilling", Period = p, ClientID = args.ClientID, ItemID = 0
                        });

                        sw.Restart();
                        storeDataClean.Start();
                        result.Add(string.Format("Completed StoreDataClean in {0}", sw.Elapsed));

                        sw.Restart();
                        storeData.Start();
                        result.Add(string.Format("Completed StoreData in {0}", sw.Elapsed));

                        sw.Restart();
                        step1.PopulateStoreBilling();
                        result.Add(string.Format("Completed StoreBilling in {0}", sw.Elapsed));
                    }

                    if (!temp && populateSubsidy)
                    {
                        sw.Restart();
                        var step4 = new BillingDataProcessStep4Subsidy(new Step4Config {
                            Connection = conn, Context = "ProcessRepository.UpdateBilling", Period = p, ClientID = args.ClientID
                        });

                        step4.PopulateSubsidyBilling();

                        result.Add(string.Format("Completed SubsidyBilling in {0}", sw.Elapsed));
                    }

                    sw.Stop();
                }

                result.Add(string.Format("Completed at {0:yyyy-MM-dd HH:mm:ss}, time taken: {1}", DateTime.Now, DateTime.Now - startTime));

                conn.Close();

                return(result);
            }
        }
        public UpdateResult Update(UpdateCommand command)
        {
            if (command.Period == default)
            {
                throw new Exception("Missing parameter: Period");
            }

            if (command.Period.Day != 1)
            {
                throw new Exception("Period must be the first day of the month.");
            }

            var startedAt = DateTime.Now;

            using (var conn = NewConnection())
            {
                conn.Open();

                var types = command.BillingTypes.ToString().Split(',').Select(x => x.Trim()).ToArray();
                // types should be {"Tool", "Room", "Store"} for example

                WriteToolDataCleanResult  writeToolDataCleanProcessResult  = null;
                WriteRoomDataCleanResult  writeRoomDataCleanProcessResult  = null;
                WriteStoreDataCleanResult writeStoreDataCleanProcessResult = null;
                WriteToolDataResult       writeToolDataProcessResult       = null;
                WriteRoomDataResult       writeRoomDataProcessResult       = null;
                WriteStoreDataResult      writeStoreDataProcessResult      = null;
                string error = null;

                foreach (string t in types)
                {
                    //Clean data always first, then final data
                    string[] dTypes = null;
                    switch (command.UpdateTypes)
                    {
                    case UpdateDataType.DataClean:
                        dTypes = new string[] { "Clean" };
                        break;

                    case UpdateDataType.Data:
                        dTypes = new string[] { "" };
                        break;

                    case UpdateDataType.DataClean | UpdateDataType.Data:
                        dTypes = new string[] { "Clean", "" };
                        break;
                    }

                    foreach (string dType in dTypes)
                    {
                        DateTime sd;
                        DateTime ed;

                        string dataType = $"{t}Data{dType}";  //e.g. StoreData, StoreDataClean OR RoomData, RoomDataClean
                        string funcName = $"Write{dataType}"; //e.g. WriteStoreData, WriteStoreDataClean OR WriteRoomData, WriteRoomDataClean

                        DateTime fom = command.Period.FirstOfMonth();

                        //always do clean first (called from within the select)
                        if (dType == "Clean")
                        {
                            //This stored procedure will delete all records that are of today (i.e. 00:00:00 of current day)
                            //and will return the max date after the deletion (so the return date should be yesterday)

                            string procName = $"dbo.{dataType}_Select";

                            var lastUpdate = GetLastUpdate(conn, procName, command.ClientID);

                            /*
                             * For tool: lastUpdate is the last BeginDateTime in ToolDataClean after deleteing all records in this way:
                             *
                             * SET @eDate = CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 120)) -- 00:00:00 of current day
                             *
                             * DELETE dbo.ToolDataClean -- allows multiple queries in the same day
                             * WHERE BeginDateTime >= @eDate OR ActualBeginDateTime >= @eDate
                             */

                            if (lastUpdate == default)
                            {
                                throw new Exception($"Cannot get lastUpdate from {procName}");
                            }

                            // sd is which ever is earlier: previousDay or lastUpdate
                            sd = lastUpdate < fom ? lastUpdate : fom;
                            ed = fom.AddMonths(1).Date;
                        }
                        else
                        {
                            // For Data always import the whole period.
                            sd = fom;
                            ed = sd.AddMonths(1);
                        }

                        // At this point ed is always 1 month after period (the arg passed to this method). For Data,
                        // sd will always be period. For DataClean sd will either be period or the last date currently
                        // found in the Clean table, whichever is earliest.

                        // No looping is necessary because for Clean the methods take a start and end date, and for Data
                        // we always use period (the arg passed to this method). Assuming DataClean works, there will be
                        // something to import into Data.

                        try
                        {
                            switch (funcName)
                            {
                            case "WriteToolDataClean":
                                writeToolDataCleanProcessResult = new WriteToolDataCleanProcess(new WriteToolDataCleanConfig {
                                    Connection = conn, Context = "ProcessRepository.Update", StartDate = sd, EndDate = ed, ClientID = command.ClientID
                                }).Start();
                                break;

                            case "WriteToolData":
                                writeToolDataProcessResult = new WriteToolDataProcess(WriteToolDataConfig.Create(conn, "ProcessRepository.Update", sd, command.ClientID, 0, Cost.GetToolCosts(sd, 0))).Start();
                                break;

                            case "WriteRoomDataClean":
                                writeRoomDataCleanProcessResult = new WriteRoomDataCleanProcess(new WriteRoomDataCleanConfig {
                                    Connection = conn, Context = "ProcessRepository.Update", StartDate = sd, EndDate = ed, ClientID = command.ClientID
                                }).Start();
                                break;

                            case "WriteRoomData":
                                writeRoomDataProcessResult = new WriteRoomDataProcess(new WriteRoomDataConfig {
                                    Connection = conn, Context = "ProcessRepository.Update", Period = sd, ClientID = command.ClientID, RoomID = 0
                                }).Start();
                                break;

                            case "WriteStoreDataClean":
                                writeStoreDataCleanProcessResult = new WriteStoreDataCleanProcess(new WriteStoreDataCleanConfig {
                                    Connection = conn, Context = "ProcessRepository.Update", StartDate = sd, EndDate = ed, ClientID = command.ClientID
                                }).Start();
                                break;

                            case "WriteStoreData":
                                writeStoreDataProcessResult = new WriteStoreDataProcess(new WriteStoreDataConfig {
                                    Connection = conn, Context = "ProcessRepository.Update", Period = sd, ClientID = command.ClientID, ItemID = 0
                                }).Start();
                                break;
                            }
                        }
                        catch (Exception ex)
                        {
                            // [2010-02-03] Test code to track who calls this function
                            // [2016-09-28 jg] Only call when there's an error
                            error = $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] function = {funcName}, ClientID = {command.ClientID}, sd = {sd}, ed = {ed}"
                                    + Environment.NewLine + Environment.NewLine + ex.ToString();

                            string subj = $"Call from LNF.CommonTools.WriteData.UpdateTables [{t + dType}] [{DateTime.Now:yyyy-MM-dd HH:mm:ss}]";

                            SendEmail.SendDeveloperEmail("LNF.CommonTools.WriteData.UpdateTables", subj, error);
                        }
                    }
                }

                conn.Close();

                var result = new UpdateResult(startedAt)
                {
                    BillingTypes = command.BillingTypes,
                    UpdateTypes  = command.UpdateTypes,
                    Period       = command.Period,
                    ClientID     = command.ClientID,
                    WriteToolDataCleanProcessResult  = writeToolDataCleanProcessResult,
                    WriteRoomDataCleanProcessResult  = writeRoomDataCleanProcessResult,
                    WriteStoreDataCleanProcessResult = writeStoreDataCleanProcessResult,
                    WriteToolDataProcessResult       = writeToolDataProcessResult,
                    WriteRoomDataProcessResult       = writeRoomDataProcessResult,
                    WriteStoreDataProcessResult      = writeStoreDataProcessResult,
                    Error = error
                };

                return(result);
            }
        }