using Foresight.Data;
using Foresight.Fleet.Services.Asset;
using Foresight.ServiceModel;
using IronIntel.Contractor.Users;
using IronIntel.Services.Business.Admin;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static IronIntel.Contractor.MapView.MachinesMapViewerManagement;

namespace IronIntel.Contractor.Maintenance
{
    public class AlertManager : BusinessBase
    {
        public AlertManager(string dbstr) : base(dbstr)
        {
        }

        const string SEL_ALERT = "select ALERTID,ALERTTYPE,ALERTTIME_UTC,COMPLETED,MACHINEID,VIN,MACHINENAME,ENGINGHOURS,ALERTDESC,PMTYPE from ALERTS";

        public MachineInfoForAlert[] SearchMachineAlerts(string sessionid, string filtertext, string[] alertstatus, string[] alerttypes, string[] assetgroups, DateTime beginDate, DateTime endDate, string useriid)
        {
            string SQL = @"select a.ALERTID,w.WORKORDERID,wo.STATUS,ALERTTYPE,a.ALERTTIME_UTC,ISNULL(COMPLETED,0) COMPLETED,a.MACHINEID,a.VIN,a.MACHINENAME,a.ENGINGHOURS,m.ENGINEHOURS as CENGINGHOURS,ALERTDESC,m.MACHINENAME2
            ,a.MAKEID,a.MODELID,pit.SERVICEDESCRIPTION
            ,(select count(1) from WORKORDER wo1 where wo1.MACHINEID=a.MACHINEID and wo1.STATUS<>'Completed') as OpenWorkOrderCount,m.ONROAD,a.PMTYPE from ALERTS a with (nolock) 
            left join WORKORDER_ALERTS w with (nolock) on a.ALERTID=w.ALERTID 
            left join WORKORDER wo with (nolock) on w.WORKORDERID=wo.WORKORDERID
			left join MACHINES m with (nolock) on a.MACHINEID=m.MACHINEID
            left join PM_ALERTS pa with (nolock) on a.ALERTID=pa.ALERTID 
            left join PM_INTERAVLS pit with (nolock) on pa.PMINTERVALID=pit.PMINTERVALID
            where m.MACHINEID is not null and (m.HIDE=0 or m.HIDE is null) and ISNULL(ACKNOWLEDGED,0)<>1 and a.ALERTTIME_UTC>={0} and a.ALERTTIME_UTC<={1} ";

            if (Array.IndexOf(alertstatus, "Completed") >= 0 && Array.IndexOf(alertstatus, "Uncompleted") < 0)
                SQL = SQL + " and ISNULL(COMPLETED,0)=1 ";
            if (Array.IndexOf(alertstatus, "Completed") < 0 && Array.IndexOf(alertstatus, "Uncompleted") >= 0)
                SQL = SQL + " and ISNULL(COMPLETED,0)=0 ";
            if (Array.IndexOf(alertstatus, "Assigned") >= 0 && Array.IndexOf(alertstatus, "Unassigned") < 0)
                SQL = SQL + " and w.WORKORDERID is not null ";
            else if (Array.IndexOf(alertstatus, "Assigned") < 0 && Array.IndexOf(alertstatus, "Unassigned") >= 0)
                SQL = SQL + " and w.WORKORDERID is null ";

            if (assetgroups.Length > 0)//asset group
            {
                SQL = SQL + string.Format(" and exists(select 1 from MACHINEGROUPMAP mg where mg.MACHINEID=m.MACHINEID and GROUPID in ('{0}'))", string.Join("','", assetgroups));
            }

            string SQL_FILTER = SQL + " and (ALERTTYPE like {0} or a.MACHINEID like {0} or a.VIN like {0} or a.MACHINENAME like {0} or m.MACHINENAME2 like {0} or ALERTDESC like {0} or SERVICEDESCRIPTION like {0})";

            string ORDER_BY = " order by ALERTID";

            double timeadjust = SystemParams.GetHoursOffset();
            if (beginDate != Helper.DBMinDateTime)
                beginDate = beginDate.AddHours(-timeadjust);
            if (endDate != DateTime.MaxValue)
                endDate = endDate.AddHours(-timeadjust);

            DataTable dt = GetDataTableBySQL(SQL + ORDER_BY, beginDate, endDate);
            if (dt.Rows.Count == 0)
            {
                return new MachineInfoForAlert[0];
            }

            long[] availableAssetsids = null;
            var user = Users.UserManagement.GetUserByIID(useriid);
            if (user.UserType < Users.UserTypes.Admin)
                availableAssetsids = FleetServiceClientHelper.CreateClient<AssetQueryClient>(sessionid).GetAvailableAssetsForUsers(SystemParams.CompanyID, useriid);

            MachineServiceClient2 mc = SystemParams.GetMachineServiceClient();
            MachineMake[] _makes = mc.GetMachineMakes();
            MachineModel[] _models = mc.GetMachineModels();

            List<MachineInfoForAlert> results = new List<MachineInfoForAlert>(dt.Rows.Count);
            Dictionary<string, AssetEngineHour> machineEngineHours = GetAssetEngineHour();
            foreach (DataRow dr in dt.Rows)
            {
                long mid = FIDbAccess.GetFieldInt64(dr["MACHINEID"], 0);
                if (user.UserType < Users.UserTypes.Admin && !availableAssetsids.Contains(mid))
                    continue;
                string alerttype = FIDbAccess.GetFieldString(dr["ALERTTYPE"], string.Empty).Trim();
                if (alerttypes.Length > 0 && !alerttypes.Contains(alerttype))//alerttype
                    continue;

                AlertInfo ai = ConvertToAlertInfo(dr, timeadjust);

                ai.ServiceDescription = FIDbAccess.GetFieldString(dr["SERVICEDESCRIPTION"], string.Empty);
                ai.WorkOrderID = FIDbAccess.GetFieldInt(dr["WORKORDERID"], 0);
                ai.WorkOrderStatus = FIDbAccess.GetFieldString(dr["STATUS"], string.Empty);
                ai.MakeID = FIDbAccess.GetFieldInt(dr["MAKEID"], 0);
                ai.ModelID = FIDbAccess.GetFieldInt(dr["MODELID"], 0);
                string name2 = FIDbAccess.GetFieldString(dr["MACHINENAME2"], string.Empty);
                string showname = name2;
                if (string.IsNullOrWhiteSpace(showname))
                    showname = ai.MachineName;
                if (string.IsNullOrWhiteSpace(showname))
                    showname = ai.VIN;
                if (string.IsNullOrWhiteSpace(showname))
                    showname = ai.MachineID.ToString();
                ai.MachineName = showname;

                MachineMake mk = _makes.FirstOrDefault(m => m.ID == ai.MakeID);
                if (mk != null)
                    ai.Make = mk.Name;
                MachineModel md = _models.FirstOrDefault(m => m.ID == ai.ModelID);
                if (md != null)
                    ai.Model = md.Name;

                MachineInfoForAlert mi = results.FirstOrDefault((i) => i.MachineID == ai.MachineID);
                if (mi == null)
                {
                    mi = new MachineInfoForAlert();
                    mi.MachineID = ai.MachineID;
                    mi.MachineName = ai.MachineName;
                    mi.VIN = ai.VIN;
                    mi.Make = ai.Make;
                    mi.Model = ai.Model;
                    mi.EngineHours = FIDbAccess.GetFieldDouble(dr["CENGINGHOURS"], 0);// ai.EngineHours;
                    if (machineEngineHours.ContainsKey(mi.MachineID.ToString()))
                    {
                        var meh = machineEngineHours[mi.MachineID.ToString()];
                        mi.EngineHours = meh.EngineHours;
                    }
                    if (!string.IsNullOrWhiteSpace(filtertext))
                    {
                        if (Helper.Contains(ai.AlertType, filtertext)
                           || Helper.Contains(ai.MachineID.ToString(), filtertext)
                           || Helper.Contains(ai.VIN, filtertext)
                           || Helper.Contains(ai.MachineName, filtertext)
                           || Helper.Contains(ai.Description, filtertext)
                           //|| Helper.Contains(ai.ServiceDescription, filtertext)
                           || Helper.Contains(mi.Make, filtertext)
                           || Helper.Contains(mi.Model, filtertext))
                            results.Add(mi);
                    }
                    else
                        results.Add(mi);
                }

                if (ai.PMType == "PM_ALERT" || ai.PMType == "TBM_ALERT" || ai.PMType == "HM_ALERT"
                   || ai.PMType == "RDM_ALERT" || ai.PMType == "ADM_ALERT")
                    mi.PMAlertCount++;
                else if (ai.AlertType == "Red-Inspect" || ai.AlertType == "Yellow-Inspect" || ai.AlertType == "Info-Inspect")
                    mi.InspectAlertCount++;
                else
                    mi.DTCAlertCount++;

                AlertInfo oildai = mi.Alerts.FirstOrDefault(m => m.Description == ai.Description && m.MachineID == ai.MachineID);
                if (oildai == null)
                {
                    ai.AlertCount = 1;
                    mi.Alerts.Add(ai);
                }
                else
                {
                    ai.AlertCount = oildai.AlertCount;
                    int index = mi.Alerts.IndexOf(oildai);
                    if (ai.AlertTime_UTC > oildai.AlertTime_UTC)
                    {
                        ai.AlertCount++;
                        mi.Alerts[index] = ai;
                    }
                    else
                        mi.Alerts[index].AlertCount++;
                }
                mi.OpenWorkOrders = FIDbAccess.GetFieldInt(dr["OpenWorkOrderCount"], 0);
                //mi.OpenWorkOrders = mi.Alerts.Where(m => m.WorkOrderID != 0 && m.WorkOrderStatus != "Completed").Select(m => m.WorkOrderID).Distinct().Count();
                var timealerts = mi.Alerts.OrderByDescending(m => m.AlertTime_UTC).ToArray();
                mi.LatestAlertDateTime = timealerts == null ? DateTime.MinValue : timealerts[0].AlertTime_UTC;
            }
            return results.ToArray();
        }

        public AlertInfo[] SearchAcknowledgedAlerts(string sessionid, string filtertext, string[] alertstatus, string[] alerttypes, string[] assetgroups, DateTime beginDate, DateTime endDate, string useriid)
        {
            string SQL = @"select a.ALERTID,w.WORKORDERID,wo.STATUS,ALERTTYPE,a.ALERTTIME_UTC,COMPLETED,a.MACHINEID,a.VIN,a.MACHINENAME, ENGINGHOURS,ALERTDESC,m.MACHINENAME2
            ,a.MAKEID,a.MODELID,pit.SERVICEDESCRIPTION,a.PMTYPE
            ,(select count(1) from WORKORDER wo1 where wo1.MACHINEID=a.MACHINEID and wo1.STATUS<>'Completed') as OpenWorkOrderCount
            ,a.ACKNOWLEDGEDBY,a.ACKNOWLEDGEDDATE_UTC,a.ACKNOWLEDGMENTCOMMENT
            from ALERTS a with (nolock)
            left join WORKORDER_ALERTS w with (nolock) on a.ALERTID=w.ALERTID 
            left join WORKORDER wo with (nolock) on w.WORKORDERID=wo.WORKORDERID 
            left join MACHINES m with (nolock) on a.MACHINEID=m.MACHINEID  
            left join PM_ALERTS pa with (nolock) on a.ALERTID=pa.ALERTID 
            left join PM_INTERAVLS pit with (nolock) on pa.PMINTERVALID=pit.PMINTERVALID
            where m.MACHINEID is not null and (m.HIDE=0 or m.HIDE is null) and ISNULL(ACKNOWLEDGED,0)=1 and a.ALERTTIME_UTC>={0} and a.ALERTTIME_UTC<={1} ";

            if (assetgroups.Length > 0)//asset group
            {
                SQL = SQL + string.Format(" and exists(select 1 from MACHINEGROUPMAP mg where mg.MACHINEID=m.MACHINEID and GROUPID in ('{0}'))", string.Join("','", assetgroups));
            }

            const string ORDER_BY = " order by ALERTID";

            double timeadjust = SystemParams.GetHoursOffset();
            if (beginDate != Helper.DBMinDateTime)
                beginDate = beginDate.AddHours(-timeadjust);
            if (endDate != DateTime.MaxValue)
                endDate = endDate.AddHours(-timeadjust);

            DataTable dt = GetDataTableBySQL(SQL + ORDER_BY, beginDate, endDate);
            if (dt.Rows.Count == 0)
            {
                return new AlertInfo[0];
            }

            long[] availableAssetsids = null;
            var user = Users.UserManagement.GetUserByIID(useriid);
            if (user.UserType < Users.UserTypes.Admin)
                availableAssetsids = FleetServiceClientHelper.CreateClient<AssetQueryClient>(sessionid).GetAvailableAssetsForUsers(SystemParams.CompanyID, useriid);

            MachineServiceClient2 mc = SystemParams.GetMachineServiceClient();
            MachineMake[] _makes = mc.GetMachineMakes();
            MachineModel[] _models = mc.GetMachineModels();
            UserInfo[] _users = UserManagement.GetAllAvailableUsers();

            List<AlertInfo> result = new List<AlertInfo>(dt.Rows.Count);
            foreach (DataRow dr in dt.Rows)
            {
                long mid = FIDbAccess.GetFieldInt64(dr["MACHINEID"], 0);
                if (user.UserType < Users.UserTypes.Admin && !availableAssetsids.Contains(mid))
                    continue;
                string alerttype = FIDbAccess.GetFieldString(dr["ALERTTYPE"], string.Empty).Trim();
                if (alerttypes.Length > 0 && !alerttypes.Contains(alerttype))//alerttype                
                    continue;
                AlertInfo ai = ConvertToAlertInfo(dr, timeadjust);

                ai.ServiceDescription = FIDbAccess.GetFieldString(dr["SERVICEDESCRIPTION"], string.Empty);
                ai.WorkOrderID = FIDbAccess.GetFieldInt(dr["WORKORDERID"], 0);
                ai.WorkOrderStatus = FIDbAccess.GetFieldString(dr["STATUS"], string.Empty);
                ai.MakeID = FIDbAccess.GetFieldInt(dr["MAKEID"], 0);
                ai.ModelID = FIDbAccess.GetFieldInt(dr["MODELID"], 0);
                string name2 = FIDbAccess.GetFieldString(dr["MACHINENAME2"], string.Empty);
                string showname = name2;
                if (string.IsNullOrWhiteSpace(showname))
                    showname = ai.MachineName;
                if (string.IsNullOrWhiteSpace(showname))
                    showname = ai.VIN;
                if (string.IsNullOrWhiteSpace(showname))
                    showname = ai.MachineID.ToString();
                ai.MachineName = showname;

                MachineMake mk = _makes.FirstOrDefault(m => m.ID == ai.MakeID);
                if (mk != null)
                    ai.Make = mk.Name;
                MachineModel md = _models.FirstOrDefault(m => m.ID == ai.ModelID);
                if (md != null)
                    ai.Model = md.Name;

                ai.AcknowledgedBy = FIDbAccess.GetFieldString(dr["ACKNOWLEDGEDBY"], string.Empty);
                ai.AcknowledgedTime_UTC = FIDbAccess.GetFieldDateTime(dr["ACKNOWLEDGEDDATE_UTC"], DateTime.MinValue);
                if (ai.AcknowledgedTime_UTC != DateTime.MinValue)
                    ai.AcknowledgedTime_UTC = ai.AcknowledgedTime_UTC.AddHours(timeadjust);
                ai.AcknowledgedComment = FIDbAccess.GetFieldString(dr["ACKNOWLEDGMENTCOMMENT"], string.Empty);
                if (!string.IsNullOrWhiteSpace(ai.AcknowledgedBy))
                {
                    UserInfo ui = _users.FirstOrDefault(m => m.IID == ai.AcknowledgedBy);
                    if (ui != null)
                        ai.AcknowledgedByName = ui.DisplayName;
                }

                ai.OpenWorkOrderCount = FIDbAccess.GetFieldInt(dr["OpenWorkOrderCount"], 0);

                AlertInfo existAlert = result.FirstOrDefault(m => m.Description == ai.Description && m.MachineID == ai.MachineID && m.AcknowledgedComment == ai.AcknowledgedComment);
                if (existAlert == null)
                {
                    ai.AlertCount = 1;
                    if (!string.IsNullOrWhiteSpace(filtertext))
                    {
                        if (Helper.Contains(ai.AlertType, filtertext)
                           || Helper.Contains(ai.MachineID.ToString(), filtertext)
                           || Helper.Contains(ai.VIN, filtertext)
                           || Helper.Contains(ai.MachineName, filtertext)
                           || Helper.Contains(ai.Description, filtertext)
                           //|| Helper.Contains(ai.ServiceDescription, filtertext)
                           || Helper.Contains(ai.Make, filtertext)
                           || Helper.Contains(ai.Model, filtertext))
                            result.Add(ai);
                    }
                    else
                        result.Add(ai);
                }
                else
                {
                    existAlert.AlertCount++;
                    if (ai.AlertTime_UTC > existAlert.AlertTime_UTC)
                        existAlert.AlertTime_UTC = ai.AlertTime_UTC;
                }
            }
            return result.ToArray();
        }


        public StringKeyValue[] GetAlertTypes()
        {
            const string SQL = "select distinct ltrim(rtrim(ALERTTYPE)) as ALERTTYPE from ALERTS where ISNULL(ALERTTYPE,'')<>'' order by ALERTTYPE";
            DataTable tb = GetDataTableBySQL(SQL);
            if (tb.Rows.Count == 0)
            {
                return new StringKeyValue[0];
            }
            List<StringKeyValue> list = new List<StringKeyValue>();
            foreach (DataRow dr in tb.Rows)
            {
                string type = FIDbAccess.GetFieldString(dr["ALERTTYPE"], string.Empty);
                StringKeyValue kv = new StringKeyValue();
                kv.Key = type;
                kv.Value = type;
                list.Add(kv);
            }
            return list.ToArray();
        }

        public AlertInfo[] GetAlertByID(long[] alertid)
        {
            if (alertid == null || alertid.Length == 0)
                return new AlertInfo[0];
            string SQL = SEL_ALERT + string.Format(" where ALERTID in ({0})", string.Join(",", alertid));
            DataTable tb = GetDataTableBySQL(SQL);
            if (tb.Rows.Count == 0)
            {
                return new AlertInfo[0];
            }
            List<AlertInfo> ls = new List<AlertInfo>(tb.Rows.Count);
            double timeadjust = SystemParams.GetHoursOffset();
            foreach (DataRow dr in tb.Rows)
            {
                ls.Add(ConvertToAlertInfo(dr, timeadjust));
            }
            return ls.ToArray();
        }

        public AlertInfo[] GetAlertsByWorkOrder(long workorderid)
        {
            const string SQL = @"select a.ALERTID,ALERTTYPE,a.ALERTTIME_UTC,COMPLETED,a.MACHINEID,a.VIN,a.MACHINENAME,a.ENGINGHOURS,a.ALERTDESC,pit.SERVICEDESCRIPTION,a.PMTYPE from ALERTS a
		  left join PM_ALERTS pa on a.ALERTID=pa.ALERTID left join PM_INTERAVLS pit on pa.PMINTERVALID=pit.PMINTERVALID
		  where a.ALERTID in (select ALERTID from WORKORDER_ALERTS where WORKORDERID={0}) order by ALERTID";
            DataTable tb = GetDataTableBySQL(SQL, workorderid);
            if (tb.Rows.Count == 0)
            {
                return new AlertInfo[0];
            }
            List<AlertInfo> ls = new List<AlertInfo>(tb.Rows.Count);
            double timeadjust = SystemParams.GetHoursOffset();
            foreach (DataRow dr in tb.Rows)
            {
                AlertInfo ai = ConvertToAlertInfo(dr, timeadjust);
                ai.ServiceDescription = FIDbAccess.GetFieldString(dr["SERVICEDESCRIPTION"], string.Empty);
                ls.Add(ai);
            }
            return ls.ToArray();
        }

        public AlertInfo[] GetAlertsByAlerts(long[] alertids)
        {
            const string SQL = SEL_ALERT + " where ALERTID in ({ALERTIDS}) order by ALERTID";

            string gids = "'" + string.Join("','", alertids) + "'";
            DataTable tb = GetDataTableBySQL(SQL.Replace("{ALERTIDS}", gids));
            if (tb.Rows.Count == 0)
            {
                return new AlertInfo[0];
            }
            List<AlertInfo> ls = new List<AlertInfo>(tb.Rows.Count);
            double timeadjust = SystemParams.GetHoursOffset();
            foreach (DataRow dr in tb.Rows)
            {
                ls.Add(ConvertToAlertInfo(dr, timeadjust));
            }
            return ls.ToArray();
        }

        public AlertInfo[] GetAlertsByMachineID(long machineid)
        {
            const string SQL = SEL_ALERT + " where MACHINEID={0} and ISNULL(COMPLETED,0)=0 and ALERTID not in(select ALERTID from WORKORDER_ALERTS)";

            DataTable tb = GetDataTableBySQL(SQL, machineid);
            if (tb.Rows.Count == 0)
            {
                return new AlertInfo[0];
            }
            List<AlertInfo> ls = new List<AlertInfo>(tb.Rows.Count);
            double timeadjust = SystemParams.GetHoursOffset();
            foreach (DataRow dr in tb.Rows)
            {
                AlertInfo ai = ConvertToAlertInfo(dr, timeadjust);

                AlertInfo oildai = ls.FirstOrDefault(m => m.Description == ai.Description);
                if (oildai == null)
                {
                    ai.AlertCount = 1;
                    ls.Add(ai);
                }
                else
                {
                    ai.AlertCount = oildai.AlertCount;
                    int index = ls.IndexOf(oildai);
                    if (ai.AlertTime_UTC > oildai.AlertTime_UTC)
                    {
                        ai.AlertCount++;
                        ls[index] = ai;
                    }
                    else
                        ls[index].AlertCount++;
                }
            }

            return ls.ToArray();
        }

        public void AcknowledgeAlert(string useriid, long[] alertids, string acknowledgmentcomment)
        {
            const string SQL = "update ALERTS set ACKNOWLEDGED=1,ACKNOWLEDGEDBY={1},ACKNOWLEDGMENTCOMMENT={2},ACKNOWLEDGEDDATE_UTC=GETUTCDATE() where ALERTID={0}";
            const string SQL_S = "select ALERTID from ALERTS where ISNULL(ACKNOWLEDGED,0)<>1 and ISNULL(COMPLETED,0)<>1 and MACHINEID=(select MACHINEID from ALERTS where ALERTID={0}) and ALERTDESC=(select ALERTDESC from ALERTS where ALERTID={0}) ";

            if (alertids != null && alertids.Length > 0)
            {
                FISqlConnection db = new FISqlConnection(DbConnectionString);
                foreach (long aid in alertids)
                {
                    DataTable dt = db.GetDataTableBySQL(SQL_S, aid);
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            long alertid = FIDbAccess.GetFieldInt64(dr["ALERTID"], 0);
                            ExecSQL(SQL, alertid, useriid, acknowledgmentcomment);
                        }
                    }
                }
            }
        }

        public void AssignedAlertsToWorkOrder(long workorderid, long[] alertid)
        {
            const string SQL_Del = "delete from WORKORDER_ALERTS where WORKORDERID={0}";
            const string SQL_ALERT = "insert into WORKORDER_ALERTS(WORKORDERID,ALERTID,ADDEDON_UTC) values({0},{1},GETUTCDATE())";
            ExecSQL(SQL_Del, workorderid);
            if ((alertid != null) && (alertid.Length > 0))
            {
                foreach (long aid in alertid)
                {
                    ExecSQL(SQL_ALERT, workorderid, aid);
                }
            }
        }

        private static void AddMaintenanceLog(AlertInfo alert, List<Tuple<long, double, double, string>> machines, string useriid, FISqlConnection db)
        {
            const string SQL_MR = @" insert into MAINTENANCELOG(MAINTENANCEID,MACHINEID,MAINTENANCEDATE,MAINTENANCEHOURS,NOTES,ADDEDBY,ADDEDON,LASTUPDATEDBY,LASTUPDATEDON,
                        ALERTID,ODOMETER,ODOMETERUOM,LOGTYPE,COMPLETEDBY,COMPLETED,COMPLETEDDATE_UTC) values({0},{1},getdate(),{2},{3},{4},GETUTCDATE(),{4},GETUTCDATE(),{5},{6},{7},{8},{4},1,GETUTCDATE())";

            string logtype = "";
            double enginehours = 0;
            double odometer = 0;
            string odometeruom = "";
            var machine = machines.FirstOrDefault(m => m.Item1 == alert.MachineID);
            if (machine != null)
            {
                if (string.Compare(alert.PMType, "HM_ALERT", true) == 0
                    || string.Compare(alert.PMType, "PM_ALERT", true) == 0
                    || string.Compare(alert.PMType, "TBM_ALERT", true) == 0)
                {
                    logtype = "Hours";
                    enginehours = machine.Item2;
                    enginehours = enginehours > 0 ? enginehours : 0;
                }
                else if (string.Compare(alert.PMType, "ADM_ALERT", true) == 0
                    || string.Compare(alert.PMType, "RDM_ALERT", true) == 0)
                {
                    logtype = "Distance";
                    odometer = machine.Item3;
                    odometer = odometer > 0 ? odometer : 0;
                    odometeruom = machine.Item4;
                }
            }
            db.ExecSQL(SQL_MR, Guid.NewGuid().ToString().ToUpper(), alert.MachineID, enginehours, alert.Description, useriid,
                alert.AlertID, odometer, odometeruom, logtype);
        }

        private static List<Tuple<long, double, double, string>> GetMachines(FISqlConnection db)
        {
            const string SQL = "select MACHINEID,ENGINEHOURS,ODOMETER,ODOMETERUOM from MACHINES";
            List<Tuple<long, double, double, string>> list = new List<Tuple<long, double, double, string>>();
            DataTable dt = db.GetDataTableBySQL(SQL);
            foreach (DataRow dr in dt.Rows)
            {
                long machineid = FIDbAccess.GetFieldInt(dr["MACHINEID"], 0);
                double enginhours = FIDbAccess.GetFieldDouble(dr["ENGINEHOURS"], 0);
                double odometer = FIDbAccess.GetFieldDouble(dr["ODOMETER"], 0);
                string odometeruom = FIDbAccess.GetFieldString(dr["ODOMETERUOM"], string.Empty);
                Tuple<long, double, double, string> t = new Tuple<long, double, double, string>(machineid, enginhours, odometer, odometeruom);
                list.Add(t);
            }
            return list;
        }
        private static StringKeyValue[] GetMachineJobSites(FISqlConnection db)
        {
            const string SQL = "select jm.MACHINEID,j.JOBSITENAME from JOBSITEMACHINES jm left join JOBSITES j on jm.JOBSITEID=j.JOBSITEID";
            List<StringKeyValue> list = new List<StringKeyValue>();
            DataTable dt = db.GetDataTableBySQL(SQL);
            foreach (DataRow dr in dt.Rows)
            {
                StringKeyValue kv = new StringKeyValue();
                kv.Key = FIDbAccess.GetFieldInt(dr["MACHINEID"], 0).ToString();
                kv.Value = FIDbAccess.GetFieldString(dr["JOBSITENAME"], string.Empty);
                list.Add(kv);
            }
            return list.ToArray();
        }

        private static AlertInfo ConvertToAlertInfo(DataRow dr, double timeadjust)
        {
            AlertInfo ai = new AlertInfo();
            ai.AlertID = FIDbAccess.GetFieldInt(dr["ALERTID"], 0);
            ai.AlertType = FIDbAccess.GetFieldString(dr["ALERTTYPE"], string.Empty);
            ai.AlertTime_UTC = FIDbAccess.GetFieldDateTime(dr["ALERTTIME_UTC"], DateTime.MinValue);
            if (ai.AlertTime_UTC != DateTime.MinValue)
                ai.AlertTime_UTC = ai.AlertTime_UTC.AddHours(timeadjust);
            ai.Completed = FIDbAccess.GetFieldInt(dr["COMPLETED"], 0) == 1;
            ai.MachineID = FIDbAccess.GetFieldInt(dr["MACHINEID"], 0);
            ai.VIN = FIDbAccess.GetFieldString(dr["VIN"], string.Empty);
            ai.MachineName = FIDbAccess.GetFieldString(dr["MACHINENAME"], string.Empty);
            ai.EngineHours = FIDbAccess.GetFieldDouble(dr["ENGINGHOURS"], 0);
            ai.Description = FIDbAccess.GetFieldString(dr["ALERTDESC"], string.Empty);
            ai.PMType = FIDbAccess.GetFieldString(dr["PMTYPE"], string.Empty);
            return ai;
        }

        private static Dictionary<string, AssetEngineHour> GetAssetEngineHour()
        {
            const string SQL_EH = @"select * from(select AssetId as MACHINEID,AsofTime as ASOFTIME_UTC,Amount,UOM,ROW_NUMBER() over(partition by AssetId order by AsofTime desc) as RowIndex from AssetEngineHours where Datasource<>'Calamp') t where RowIndex=1";

            DataTable tbeh = null;
            string dbString2 = SystemParams.GetIronIntelReportDataDbString(SystemParams.CompanyID);
            if (!string.IsNullOrWhiteSpace(dbString2))
            {
                var db2 = new FISqlConnection(dbString2);
                tbeh = db2.GetDataTableBySQL(SQL_EH);
            }

            Dictionary<string, AssetEngineHour> machineEngineHours = new Dictionary<string, AssetEngineHour>();
            if (tbeh != null && tbeh.Rows.Count > 0)
            {
                foreach (DataRow dr in tbeh.Rows)
                {
                    string mID = FIDbAccess.GetFieldString(dr["MACHINEID"], string.Empty);
                    var meh = new AssetEngineHour();
                    meh.EngineHours = FIDbAccess.GetFieldDouble(dr["Amount"], -1);
                    string uom = FIDbAccess.GetFieldString(dr["UOM"], string.Empty);
                    if (uom.ToLower() == "s")
                        meh.EngineHours = meh.EngineHours / 3600;
                    meh.EngineHoursDate = FIDbAccess.GetFieldDateTime(dr["ASOFTIME_UTC"], DateTime.MinValue);
                    if (meh.EngineHoursDate != DateTime.MinValue)
                        meh.EngineHoursDate = meh.EngineHoursDate.AddHours(SystemParams.GetHoursOffset());

                    machineEngineHours[mID] = meh;
                }
            }

            return machineEngineHours;
        }

    }
}
