Commit: f8acc5d752cbb824c20f3eacd57030b749281695 - UmaTest (git)

EveryDB2のデータベースを読み込んでWinFormでGUI表示するサンプル

Commit MetaInfo

• Revision: f8acc5d752cbb824c20f3eacd57030b749281695 (tree)
• Time: 2021-06-05 05:23:52
• Author: yoshy <yoshy@user...>
• Commiter: yoshy

Log Message

temp_20210605_0523

Change Summary

• modified: App/Adaptor/Gateway/ViewModel/Dto/UmaRaceRowDto.cs
• modified: App/Adaptor/Gateway/ViewModel/Dto/UmaSummaryRowDto.cs
• modified: App/Presentation/View/MainWindow.cs
• modified: Infra/Helper/CodeHelper.cs
• modified: Infra/Helper/FormatHelper.cs
• modified: Resources/Sql/uma_race_stddev2.sql

Incremental Difference

--- a/App/Adaptor/Gateway/ViewModel/Dto/UmaRaceRowDto.cs
+++ b/App/Adaptor/Gateway/ViewModel/Dto/UmaRaceRowDto.cs
@@ -43,11 +43,15 @@ namespace UmaTest.App.Adaptor.Gateway.ViewModel.Dto
 
         public string GradeName => CodeHelper.GetInstance().GetGradeName(Race, "？");
 
-        public string CourseName => CodeHelper.GetInstance().GetTurfDurtName(Race?.Trackcd, "？") + Race?.Kyori;
+        public string TurfDirtName => CodeHelper.GetInstance().GetTurfDurtName(Race?.Trackcd, "？");
+
+        public string CourseName => TurfDirtName + Race?.Kyori;
 
         public string NinkiFormatted => FormatHelper.SuppressZeroLeading(Ninki);
 
-        public string SyussoTosuFormatted => FormatHelper.SuppressZeroLeading(Race.Syussotosu);
+        public string TosuFormatted => FormatHelper.FormatTosu(Race);
+
+        public string BabaJyotaiFormatted => FormatHelper.FormatBabaJyotai(Race);
 
         public string KakuteiJyuniFormatted => FormatHelper.SuppressZeroLeading(Kakuteijyuni);
 

--- a/App/Adaptor/Gateway/ViewModel/Dto/UmaSummaryRowDto.cs
+++ b/App/Adaptor/Gateway/ViewModel/Dto/UmaSummaryRowDto.cs
@@ -97,12 +97,20 @@ namespace UmaTest.App.Adaptor.Gateway.ViewModel.Dto
         {
             get
             {
-                if (Konso?.Bataijyu != null)
+                if (!string.IsNullOrEmpty(Konso?.Bataijyu) && (Konso.Bataijyu != "0000"))
                 {
                     return Konso.BataijyuFormatted;
                 }
-                if (Zenso?.Bataijyu != null)
+
+                if (!string.IsNullOrEmpty(Zenso?.Bataijyu))
                 {
+                    if ( string.IsNullOrEmpty(Konso?.Bataijyu) || (Konso?.Bataijyu == "0000") )
+                    {
+                        Konso.Bataijyu = Zenso.Bataijyu;
+                        Konso.Zogensa = null;
+                        return Konso.Bataijyu;
+                    }
+
                     return Zenso.BataijyuFormatted;
                 }
 

--- a/App/Presentation/View/MainWindow.cs
+++ b/App/Presentation/View/MainWindow.cs
@@ -208,6 +208,11 @@ namespace UmaTest.App.Presentation.View
                 },
                 new DataGridViewTextBoxColumn
                 {
+                    DataPropertyName = "Zenso.BabaJyotaiFormatted",
+                    Name = "前走馬場",
+                },
+                new DataGridViewTextBoxColumn
+                {
                     DataPropertyName = "Zenso.NinkiFormatted",
                     Name = "前走人気",
                 },
@@ -288,6 +293,11 @@ namespace UmaTest.App.Presentation.View
                 },
                 new DataGridViewTextBoxColumn
                 {
+                    DataPropertyName = "NisoMae.BabaJyotaiFormatted",
+                    Name = "前々走馬場",
+                },
+                new DataGridViewTextBoxColumn
+                {
                     DataPropertyName = "NisoMae.NinkiFormatted",
                     Name = "前々走人気",
                 },
@@ -481,6 +491,7 @@ namespace UmaTest.App.Presentation.View
             dataGridView1.Columns["間隔"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
 
             dataGridView1.Columns["前走G"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
+            dataGridView1.Columns["前走馬場"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
             dataGridView1.Columns["前走人気"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
             dataGridView1.Columns["前走通過順位"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
             dataGridView1.Columns["前走着順"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
@@ -491,6 +502,7 @@ namespace UmaTest.App.Presentation.View
             dataGridView1.Columns["前走間隔"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
 
             dataGridView1.Columns["前々走G"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
+            dataGridView1.Columns["前々走馬場"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
             dataGridView1.Columns["前々走人気"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
             dataGridView1.Columns["前々走通過順位"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
             dataGridView1.Columns["前々走着順"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
@@ -555,11 +567,16 @@ namespace UmaTest.App.Presentation.View
                 },
                 new DataGridViewTextBoxColumn
                 {
-                    DataPropertyName = "SyussoTosuFormatted",
+                    DataPropertyName = "TosuFormatted",
                     Name = "頭数",
                 },
                 new DataGridViewTextBoxColumn
                 {
+                    DataPropertyName = "BabaJyotaiFormatted",
+                    Name = "馬場",
+                },
+                new DataGridViewTextBoxColumn
+                {
                     DataPropertyName = "WakubanFormatted",
                     Name = "枠番",
                 },
@@ -605,23 +622,23 @@ namespace UmaTest.App.Presentation.View
                 },
                 new DataGridViewTextBoxColumn
                 {
-                    DataPropertyName = "AvgHaronTimeMinus3Formatted",
-                    Name = "平均-3F",
+                    DataPropertyName = "AvgHaronTimeMinus3DevFormatted",
+                    Name = "平均-3F ﾍﾟｰｽ",
                 },
                 new DataGridViewTextBoxColumn
                 {
-                    DataPropertyName = "AvgHaronTimeMinus3DevFormatted",
-                    Name = "平均-3F ﾍﾟｰｽ",
+                    DataPropertyName = "HaronTimeL3DevFormatted",
+                    Name = "上3F指数",
                 },
                 new DataGridViewTextBoxColumn
                 {
-                    DataPropertyName = "HaronTimeL3Formatted",
-                    Name = "上3F",
+                    DataPropertyName = "AvgHaronTimeMinus3Formatted",
+                    Name = "平均-3F",
                 },
                 new DataGridViewTextBoxColumn
                 {
-                    DataPropertyName = "HaronTimeL3DevFormatted",
-                    Name = "上3F指数",
+                    DataPropertyName = "HaronTimeL3Formatted",
+                    Name = "上3F",
                 },
                 new DataGridViewTextBoxColumn
                 {
@@ -674,6 +691,7 @@ namespace UmaTest.App.Presentation.View
 
             dataGridView2.Columns["G"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
             dataGridView2.Columns["頭数"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
+            dataGridView2.Columns["馬場"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
             dataGridView2.Columns["枠番"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
             dataGridView2.Columns["馬番"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
             dataGridView2.Columns["人気"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

--- a/Infra/Helper/CodeHelper.cs
+++ b/Infra/Helper/CodeHelper.cs
@@ -15,6 +15,7 @@ namespace UmaTest.Infra.Helper
         public static readonly string GRADE_CODE = "2003";
         public static readonly string JYOKEN_CODE = "2007";
         public static readonly string TRACK_CODE = "2009";
+        public static readonly string BABA_JYOTAI_CODE = "2010";
 
         public static readonly string SEX_CODE = "2202";
 

--- a/Infra/Helper/FormatHelper.cs
+++ b/Infra/Helper/FormatHelper.cs
@@ -106,7 +106,7 @@ namespace UmaTest.Infra.Helper
 
         public static string FormatTimeSSF(string time3)
         {
-            return time3.Length == 3
+            return time3?.Length == 3
                     ? time3 != "000" && time3 != "999"
                             ? $"{time3.Substring(0, 2)}.{time3.Substring(2, 1)}"
                             : "-"
@@ -115,7 +115,7 @@ namespace UmaTest.Infra.Helper
 
         public static string FormatTimeMSSF(string time4)
         {
-            return time4.Length == 4
+            return time4?.Length == 4
                     ? time4 != "0000"
                             ? $"{time4.Substring(0, 1)}:{time4.Substring(1, 2)}.{time4.Substring(3, 1)}"
                             : "-"
@@ -124,7 +124,7 @@ namespace UmaTest.Infra.Helper
 
         public static string FormatTimeHHMM(string time4)
         {
-            return time4.Length == 4
+            return time4?.Length == 4
                     ? time4 != "0000"
                             ? $"{time4.Substring(0, 2)}:{time4.Substring(2, 2)}"
                             : "-"
@@ -305,7 +305,7 @@ namespace UmaTest.Infra.Helper
 
         public static string ToNumber(string value)
         {
-            return ToNumber(value, value.Length, 0);
+            return !string.IsNullOrEmpty(value) ? ToNumber(value, value.Length, 0) : string.Empty;
         }
 
         public static string ToNumber(string value, int length, int digits)
@@ -340,5 +340,55 @@ namespace UmaTest.Infra.Helper
 
             return Math.Round((number / pow10s[digits]), digits, MidpointRounding.AwayFromZero).ToString(digitFormats[digits]);
         }
+
+        public static string FormatBabaJyotai(NRace race)
+        {
+            if (race == null)
+            {
+                return "-";
+            }
+
+            string sibaJyotai = CodeHelper.GetInstance().GetName(CodeHelper.BABA_JYOTAI_CODE, race.Sibababacd, string.Empty);
+            string dirtJyotai = CodeHelper.GetInstance().GetName(CodeHelper.BABA_JYOTAI_CODE, race.Dirtbabacd, string.Empty);
+
+            string turfDirtName = CodeHelper.GetInstance().GetTurfDurtName(race.Trackcd, string.Empty);
+
+            switch(turfDirtName)
+            {
+                case "芝":
+                    return sibaJyotai;
+                case "ダ":
+                    return dirtJyotai;
+                case "芝ダ":
+                    return !string.IsNullOrEmpty(sibaJyotai) && !string.IsNullOrEmpty(dirtJyotai) ? sibaJyotai + "・" + dirtJyotai : string.Empty;
+            }
+
+            return string.Empty;
+        }
+
+        public static string FormatTosu(NRace race)
+        {
+            if (race == null)
+            {
+                return "-";
+            }
+
+            string syussoTosuu = SuppressZeroLeading(race?.Syussotosu);
+
+            if ( !string.IsNullOrEmpty(syussoTosuu) && (syussoTosuu != "-") )
+            {
+                return syussoTosuu;
+            }
+
+            string torokuTosuu = SuppressZeroLeading(race?.Torokutosu);
+
+            if ( !string.IsNullOrEmpty(torokuTosuu) && (torokuTosuu != "-") )
+            {
+                return torokuTosuu;
+            }
+
+            return string.Empty;
+        }
+
     }
 }

--- a/Resources/Sql/uma_race_stddev2.sql
+++ b/Resources/Sql/uma_race_stddev2.sql
@@ -1,5 +1,6 @@
+create table app_uma_race_stddev as
 with v_uma_race_base1 as (
-select distinct
+select
   r.jyocd
   , r.year
   , r.monthday
@@ -11,7 +12,7 @@ select distinct
         when '702' then 'N'
         when '703' then 'M'
         else case
-          when jyokencd5 =  '000' then '-'
+          when jyokencd5 <= '000' then '-'
           when jyokencd5 <= '005' then '1'
           when jyokencd5 <= '010' then '2'
           when jyokencd5 <= '016' then '3'
@@ -21,19 +22,21 @@ select distinct
       else r.gradecd
     end as gradecd
   , r.syubetucd
+  , ur.time
+  , ur.harontimel3
 from
   n_uma_race ur
   inner join n_race r
   using (year, jyocd, kaiji, nichiji, racenum)
-where
-  ur.kettonum in ('1985104409')
 ), v_uma_race_base2 as (
-select distinct
+select
   ur.jyocd
   , ur.kyori
   , ur.trackcd
   , ur.gradecd
   , ur.syubetucd
+  , ur.time
+  , ur.harontimel3
   , coalesce(max(c.kaishudate)
       filter (where c.kaishudate <= ur.year || ur.monthday)
       over (partition by ur.jyocd, ur.kyori, ur.trackcd, ur.gradecd, ur.syubetucd)
@@ -43,37 +46,85 @@ from
   v_uma_race_base1 ur
   inner join n_course c
   using(jyocd, kyori, trackcd)
+), v_uma_race_base3 as (
+select
+  ur.jyocd
+  , to_number(ur.kyori, '0000') as kyori
+  , ur.trackcd
+  , ur.gradecd
+  , ur.syubetucd
+  , ur.kaishudate
+  , case ur.time 
+      when '0000' then null 
+      when '    ' then null 
+      else to_number(substring(ur.time, 1, 1), '0') * 600
+        + to_number(substring(ur.time, 2, 2), '00') * 10
+        + to_number(substring(ur.time, 4, 1), '0') 
+    end as time
+  , case ur.harontimel3
+      when '000' then null 
+      when '999' then null 
+      when '   ' then null 
+      else to_number(substring(ur.harontimel3, 1, 2), '00') * 10
+        + to_number(substring(ur.harontimel3, 3, 1), '0') 
+    end as harontimel3
+from
+  v_uma_race_base2 ur
+), v_uma_race as (
+select
+  ur.jyocd
+  , ur.kyori
+  , ur.trackcd
+  , ur.gradecd
+  , ur.syubetucd
+  , ur.kaishudate 
+  , ur.time 
+  , ur.harontimel3
+  , case ur.harontimel3
+      when null then null
+      else (ur.time - ur.harontimel3) * 600 / (ur.kyori - 600)
+    end as avgharontimeminus3
+from
+  v_uma_race_base3 ur
 )
-SELECT ur.*, r.*, vur.*, urdev.*
-FROM n_uma_race ur
-INNER JOIN n_race r USING(year, jyocd, kaiji, nichiji, racenum)
-LEFT JOIN n_uma u USING(kettonum)
-LEFT JOIN v_uma_race_base2 vur
- ON r.jyocd     = vur.jyocd
-AND r.kyori     = vur.kyori
-AND r.trackcd   = vur.trackcd
-AND vur.gradecd = case r.gradecd 
-      when '' then case r.jyokencd5
-        when '701' then 'N'
-        when '702' then 'N'
-        when '703' then 'M'
-        else case
-          when r.jyokencd5 =  '000' then null
-          when r.jyokencd5 <= '005' then '1'
-          when r.jyokencd5 <= '010' then '2'
-          when r.jyokencd5 <= '016' then '3'
-          else 'OP'
-        end
-      end
-      else r.gradecd
-    end
-AND r.syubetucd = vur.syubetucd
-LEFT JOIN app_uma_race_stddev urdev
- ON vur.jyocd      = urdev.jyocd
-AND vur.kyori      = urdev.kyori
-AND vur.trackcd    = urdev.trackcd
-AND vur.gradecd    = urdev.gradecd
-AND vur.syubetucd  = urdev.syubetucd
-AND vur.kaishudate = urdev.kaishudate
-WHERE ur.kettonum in ('1985104409')
-ORDER BY ur.kettonum, ur.year desc, ur.monthday desc
\ No newline at end of file
+select
+  ur.jyocd
+  , to_char(ur.kyori, 'FM0000') as kyori
+  , ur.trackcd
+  , ur.gradecd
+  , ur.syubetucd
+  , ur.kaishudate
+  , count(ur.time) as cnt
+  , min(ur.time) as time_min
+  , max(ur.time) as time_max
+  , round(avg(ur.time), 2) as time_avg
+  , round(stddev(ur.time), 2) as time_dev
+  , min(ur.harontimel3) as harontimel3_min
+  , max(ur.harontimel3) as harontimel3_max
+  , round(avg(ur.harontimel3), 2) as harontimel3_avg
+  , round(stddev(ur.harontimel3), 2) as harontimel3_dev
+  , round(min(ur.avgharontimeminus3), 2) as avgharontimeminus3_min
+  , round(max(ur.avgharontimeminus3), 2) as avgharontimeminus3_max
+  , round(avg(ur.avgharontimeminus3), 2) as avgharontimeminus3_avg
+  , round(stddev(ur.avgharontimeminus3), 2) as avgharontimeminus3_dev
+from
+  v_uma_race ur
+group by
+  ur.jyocd
+  , ur.kyori
+  , ur.trackcd
+  , ur.kaishudate
+  , ur.gradecd
+  , ur.syubetucd
+order by
+  ur.jyocd
+  , ur.kyori
+  , ur.trackcd
+  , ur.gradecd
+  , ur.syubetucd
+  , ur.kaishudate
+;
+alter table app_uma_race_stddev add constraint pk_app_uma_race_stddev primary key (jyocd, kyori, trackcd, gradecd, syubetucd, kaishudate)
+;
+drop table app_uma_race_stddev
+;