< Summary

Information
Class: ReportingServices.Shared.Repositories.ScrapeDatabaseRepository
Assembly: ReportingServices.Shared
File(s): C:\Users\wathen\source\repos\ReportingServices\ReportingServices.Shared\Repositories\Implementations\ScrapeDatabaseRepository.cs
Line coverage
0%
Covered lines: 0
Uncovered lines: 190
Coverable lines: 190
Total lines: 273
Line coverage: 0%
Branch coverage
0%
Covered branches: 0
Total branches: 24
Branch coverage: 0%
Method coverage

Method coverage is only available for sponsors.

Upgrade to PRO version

Metrics

MethodBranch coverage Cyclomatic complexity Line coverage
.ctor()100%10%
OpenConnection()0%40%
CloseConnection()0%20%
GetNumberOfPartChanges(...)100%10%
GetScrapByDay(...)0%60%
GetReactorPSNWORuns(...)0%20%
GetQuarterlyTargets()0%60%
GetReactors()0%20%
GetRDSForLastDay(...)0%20%

File(s)

C:\Users\wathen\source\repos\ReportingServices\ReportingServices.Shared\Repositories\Implementations\ScrapeDatabaseRepository.cs

#LineLine coverage
 1using Microsoft.Data.SqlClient;
 2using ReportingServices.Shared.Models.PlanningReport;
 3using ReportingServices.Shared.Models.ProductionReport;
 4using System.Data;
 5
 6namespace ReportingServices.Shared.Repositories
 7{
 8    public class ScrapeDatabaseRepository : IScrapeDatabaseRepository
 9    {
 10        private SqlConnection _connection;
 11        private readonly string _connectionString;
 12
 013        public ScrapeDatabaseRepository()
 014        {
 015            _connectionString = "Server=MESSV01EC.EC.LOCAL\\PROD1,53959;Database=LSL2SQL;User Id=srpadmin;Password=0okm9
 016        }
 17
 18        public void OpenConnection()
 019        {
 020            if (_connection == null)
 021                _connection = new SqlConnection(_connectionString);
 22
 023            if (_connection.State != ConnectionState.Open)
 024                _connection.Open();
 025        }
 26
 27        public void CloseConnection()
 028        {
 029            if (_connection.State != ConnectionState.Closed)
 030                _connection.Close();
 031        }
 32
 33        public int GetNumberOfPartChanges(string startDate, string endDate)
 034        {
 035            int result = 0;
 36
 037            OpenConnection();
 38
 039            SqlCommand cmd = _connection.CreateCommand();
 40
 041            string query = "SELECT COUNT(*) FROM " +
 042                           "(SELECT REACTOR, COUNT(PROD_SPEC_ID) - 1 AS PCHANGE FROM " +
 043                           "(SELECT REACTOR, PROD_SPEC_ID, COUNT(WO) AS PSN_COUNT FROM RDS WHERE DATE_OUT BETWEEN @start
 044                           "GROUP BY REACTOR) AS l WHERE PCHANGE > 0";
 45
 046            cmd.CommandText = query;
 047            cmd.Parameters.AddWithValue("@startDate", startDate);
 048            cmd.Parameters.AddWithValue("@endDate", endDate);
 49
 050            using (SqlDataReader reader = cmd.ExecuteReader())
 051            {
 052                reader.Read();
 53
 054                result = int.Parse(reader[0].ToString());
 055            }
 56
 057            cmd.Dispose();
 58
 059            CloseConnection();
 60
 061            return result;
 062        }
 63
 64        public List<ScrapByDay> GetScrapByDay(List<ReactorOutsByRDS> outs)
 065        {
 066            List<ScrapByDay> scrap = new();
 067            string rdsNumbers = "";
 68
 069            foreach (ReactorOutsByRDS rout in outs)
 070                rdsNumbers = rdsNumbers + "'" + rout.RDS_NO + "', ";
 71
 072            rdsNumbers = rdsNumbers.Substring(0, rdsNumbers.Length - 2);
 73
 074            OpenConnection();
 75
 076            SqlCommand cmd = _connection.CreateCommand();
 77
 078            string query = "SELECT " +
 079                            "  DATE_OUT," +
 080                            "  SUM(CUST_TOT_REJ) AS TOT_REJ_CUST," +
 081                            "  SUM(LSL_TOT_REJ) AS TOT_REJ_MANU," +
 082                            "  SUM(TW_PROD) AS TW_PROD " +
 083                            "FROM RDS " +
 084                            "WHERE SEQ IN (" + rdsNumbers + ") " +
 085                            "GROUP BY DATE_OUT " +
 086                            "ORDER BY 1 DESC";
 87
 088            cmd.CommandText = query;
 89
 090            using (SqlDataReader reader = cmd.ExecuteReader())
 091            {
 092                while (reader.Read() && reader[0].ToString() != "1/1/1900 12:00:00 AM")
 093                    scrap.Add(new ScrapByDay
 094                    {
 095                        StartDate = reader[0].ToString(),
 096                        TW_PROD = int.Parse(reader[3].ToString()),
 097                        TOT_REJ_CUST = int.Parse(reader[1].ToString()),
 098                        TOT_REJ_MANU = int.Parse(reader[2].ToString()),
 099                        TOT_REJ_WFRS =
 0100                        int.Parse(reader[1].ToString()) + int.Parse(reader[2].ToString())
 0101                    });
 0102            }
 103
 0104            cmd.Dispose();
 105
 0106            CloseConnection();
 107
 0108            return scrap;
 0109        }
 110
 111        public List<ReactorPSNWORuns> GetReactorPSNWORuns(string startDate, string endDate)
 0112        {
 0113            List<ReactorPSNWORuns> weeklyPartChanges = new();
 114
 0115            OpenConnection();
 116
 0117            SqlCommand cmd = _connection.CreateCommand();
 118
 0119            string query = "SELECT REACTOR, PROD_SPEC_ID, COUNT(WO) FROM RDS " +
 0120                            "WHERE DATE_OUT BETWEEN @startDate AND @endDate " +
 0121                            "GROUP BY REACTOR, PROD_SPEC_ID " +
 0122                            "ORDER BY 1";
 123
 0124            cmd.CommandText = query;
 0125            cmd.Parameters.AddWithValue("@startDate", startDate);
 0126            cmd.Parameters.AddWithValue("@endDate", endDate);
 127
 0128            using (SqlDataReader reader = cmd.ExecuteReader())
 0129            {
 0130                while (reader.Read())
 0131                    weeklyPartChanges.Add(new ReactorPSNWORuns
 0132                    {
 0133                        REACTOR = reader[0].ToString(),
 0134                        PSN = reader[1].ToString(),
 0135                        WO_COUNT = int.Parse(reader[2].ToString())
 0136                    });
 0137            }
 138
 0139            cmd.Dispose();
 140
 0141            CloseConnection();
 142
 0143            return weeklyPartChanges;
 0144        }
 145
 146        public QuarterlyTargets GetQuarterlyTargets()
 0147        {
 0148            Dictionary<string, float> targets = new();
 149
 0150            OpenConnection();
 151
 0152            SqlCommand cmd = _connection.CreateCommand();
 153
 0154            string query = "SELECT THRU_TARGET, THRU_QTY, THRU_PCNT FROM FISCAL_QTR_TARGETS " +
 0155                           " WHERE THRU_GROUP = 'TOT' " +
 0156                           "   AND FISCAL_YR = " +
 0157                           "    (SELECT FISCAL_YR FROM FISCAL_QTR " +
 0158                           "      WHERE START_DT < SYSDATETIME() " +
 0159                           "        AND END_DT > SYSDATETIME()) " +
 0160                           "   AND FISCAL_QTR = " +
 0161                           "    (SELECT FISCAL_QTR FROM FISCAL_QTR " +
 0162                           "      WHERE START_DT < SYSDATETIME() " +
 0163                           "        AND END_DT > SYSDATETIME()) " +
 0164                           "UNION " +
 0165                           "SELECT 'PlanWorkingDays' As THRU_TARGET," +
 0166                           "       PLAN_WORKING_DAYS AS THRU_QTY," +
 0167                           "       NULL AS THRU_PCNT" +
 0168                           "  FROM FISCAL_QTR " +
 0169                           " WHERE SYSDATETIME() BETWEEN START_DT AND END_DT";
 170
 0171            cmd.CommandText = query;
 172
 0173            using (SqlDataReader reader = cmd.ExecuteReader())
 0174            {
 0175                while(reader.Read())
 0176                {
 0177                    if (reader[0].ToString().ToUpper() == "YIELD")
 0178                        targets.Add(reader[0].ToString(), float.Parse(reader[2].ToString()));
 0179                    else if (!string.IsNullOrEmpty(reader[1].ToString()))
 0180                        targets.Add(reader[0].ToString(), int.Parse(reader[1].ToString()));
 0181                }
 0182            }
 183
 0184            cmd.Dispose();
 185
 0186            CloseConnection();
 187
 0188            QuarterlyTargets quarterlyTargets = new()
 0189            {
 0190                Reactor_Outs = (int)targets["Reactor_Outs"],
 0191                Yield_Outs = (int)targets["Yield_Outs"],
 0192                IFX_Scrap = (int)targets["IFX_Scrap"],
 0193                Yield = targets["Yield"],
 0194                PlanWorkingDays = (int)targets["PlanWorkingDays"]
 0195            };
 196
 0197            return quarterlyTargets;
 0198        }
 199
 200        public List<Reactor> GetReactors()
 0201        {
 0202            List<Reactor> reactors = new();
 203
 0204            OpenConnection();
 205
 0206            SqlCommand cmd = _connection.CreateCommand();
 207
 0208            string query = "SELECT " +
 0209                           "   REACT_NO, REACT_TYPE, SUSC_POCKET_SIZE, CASE WHEN ACTIVE_LL_DISABLED <> '' THEN 'TRUE' EL
 0210                           "  FROM REACTOR " +
 0211                           " WHERE REACT_ASSIGNMENT IS NOT NULL " +
 0212                           "   AND REACT_ASSIGNMENT <> 'Out of Service' " +
 0213                           "   AND REACT_ASSIGNMENT<> ''";
 214
 0215            cmd.CommandText = query;
 216
 0217            using (SqlDataReader reader = cmd.ExecuteReader())
 0218            {
 0219                while (reader.Read())
 0220                    reactors.Add(new Reactor
 0221                    {
 0222                        ReactorNumber = int.Parse(reader[0].ToString()),
 0223                        Type = reader[1].ToString(),
 0224                        PocketSize = reader[2].ToString(),
 0225                        HasDisabledLoadlock = bool.Parse(reader[3].ToString())
 0226                    });
 0227            }
 228
 0229            cmd.Dispose();
 230
 0231            CloseConnection();
 232
 0233            return reactors;
 0234        }
 235
 236        public List<RDS> GetRDSForLastDay(string date)
 0237        {
 0238            List<RDS> rdsList = new();
 239
 0240            OpenConnection();
 241
 0242            SqlCommand cmd = _connection.CreateCommand();
 243
 0244            string query = "SELECT rds.REACTOR, rds.REACTOR_TYPE, rds.DATE_OUT, " +
 0245                           "CASE WHEN lay.UL_TEMP IS NULL THEN '1000' ELSE lay.UL_TEMP END, psn.LAYER_TYPE FROM RDS " +
 0246                           "INNER JOIN RDS_LAYER lay ON lay.RDS_NO = SEQ " +
 0247                           "INNER JOIN PROD_SPEC psn ON rds.PROD_SPEC_ID = psn.SEQ " +
 0248                           "WHERE DATE_OUT >= @date";
 249
 0250            cmd.CommandText = query;
 0251            cmd.Parameters.AddWithValue("@date", date);
 252
 0253            using (SqlDataReader reader = cmd.ExecuteReader())
 0254            {
 0255                while (reader.Read())
 0256                    rdsList.Add(new RDS
 0257                    {
 0258                        Reactor = int.Parse(reader[0].ToString()),
 0259                        ReactorType = reader[1].ToString(),
 0260                        DateOut = DateTime.Parse(reader[2].ToString()),
 0261                        UnloadTemp = int.Parse(reader[3].ToString()),
 0262                        LayerType = reader[4].ToString()
 0263                    });
 0264            }
 265
 0266            cmd.Dispose();
 267
 0268            CloseConnection();
 269
 0270            return rdsList;
 0271        }
 272    }
 273}