· 8 years ago · Aug 12, 2018, 12:00 PM
1/// <summary>
2 /// Gets the unmapped matches.
3 /// </summary>
4 /// <returns></returns>
5 public DataTable GetUnmappedMatches()
6 {
7 const string sql = @"DROP TEMPORARY TABLE IF EXISTS groupedTP1;
8DROP TEMPORARY TABLE IF EXISTS groupedTP2;
9
10CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP1
11(PRIMARY KEY (team_id, person_id, active_ind))
12SELECT person_id, team_id, active_ind FROM team_person tp
13INNER JOIN (
14 SELECT max(team_person_id) as tpi from team_person
15 GROUP by team_id, person_id
16) grp ON (grp.tpi = tp.team_person_id);
17
18CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP2
19(PRIMARY KEY (team_id, person_id, active_ind))
20SELECT * FROM groupedTP1;
21
22SELECT DISTINCT m.matches_id as 'MatchId', DATE_FORMAT(m.begin_date, '%m/%d/%Y %H:%i') as 'DateTime', t1.name as 'HomeTeamName', t2.name as 'AwayTeamName', c.name as 'CompetitionName', m.use_gsm
23FROM matches m
24INNER JOIN round r ON (r.round_id = m.round_id)
25INNER JOIN season s ON (s.season_id = r.season_id)
26INNER JOIN competition c ON (c.competition_id = s.competition_id)
27INNER JOIN team t1 ON (t1.team_id = m.team1_id)
28INNER JOIN team t2 ON (t2.team_id = m.team2_id)
29LEFT OUTER JOIN groupedTP1 tp1 ON (tp1.team_id = t1.team_id AND tp1.active_ind = 'Y')
30LEFT OUTER JOIN groupedTP2 tp2 ON (tp2.team_id = t2.team_id AND tp2.active_ind = 'Y')
31LEFT OUTER JOIN person p1 ON (p1.person_id = tp1.person_id)
32LEFT OUTER JOIN person p2 ON (p2.person_id = tp2.person_id)
33WHERE
34(
35m.feed_id IS NULL
36OR t1.feed_id IS NULL
37OR t2.feed_id IS NULL
38OR p1.feed_id IS NULL
39OR p2.feed_id IS NULL
40)
41AND m.begin_date <= (NOW() + INTERVAL 7 DAY)
42AND m.begin_date >= CURDATE()
43AND c.responsible_site_id = @SiteId
44ORDER by m.begin_date ASC LIMIT 100;";
45
46 var prm = DALManager.CreateDALParameters();
47 prm.Add("@SiteId", RelatedSiteId);
48 var dt = CurrentDAL.GetDT(sql, CommandType.Text, prm, false, false, null, null);
49 return dt;
50 }
51
52 /// <summary>
53 /// Gets the unmapped matches.
54 /// </summary>
55 /// <param name="beginDate">The begin date.</param>
56 /// <param name="endDate">The end date.</param>
57 /// <returns></returns>
58 public DataTable GetUnmappedMatches(DateTime beginDate, DateTime endDate)
59 {
60 const string sql =
61 @"DROP TEMPORARY TABLE IF EXISTS groupedTP1;
62DROP TEMPORARY TABLE IF EXISTS groupedTP2;
63
64CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP1
65(PRIMARY KEY (team_id, person_id, active_ind))
66SELECT person_id, team_id, active_ind FROM team_person tp
67INNER JOIN (
68 SELECT max(team_person_id) as tpi from team_person
69 GROUP by team_id, person_id
70) grp ON (grp.tpi = tp.team_person_id);
71
72CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP2
73(PRIMARY KEY (team_id, person_id, active_ind))
74SELECT * FROM groupedTP1;
75
76SELECT DISTINCT m.matches_id as 'MatchId', DATE_FORMAT(m.begin_date, '%m/%d/%Y %H:%i') as 'DateTime', t1.name as 'HomeTeamName', t2.name as 'AwayTeamName', c.name as 'CompetitionName', m.use_gsm
77FROM matches m
78INNER JOIN round r ON (r.round_id = m.round_id)
79INNER JOIN season s ON (s.season_id = r.season_id)
80INNER JOIN competition c ON (c.competition_id = s.competition_id)
81INNER JOIN team t1 ON (t1.team_id = m.team1_id)
82INNER JOIN team t2 ON (t2.team_id = m.team2_id)
83LEFT OUTER JOIN groupedTP1 tp1 ON (tp1.team_id = t1.team_id AND tp1.active_ind = 'Y')
84LEFT OUTER JOIN groupedTP2 tp2 ON (tp2.team_id = t2.team_id AND tp2.active_ind = 'Y')
85LEFT OUTER JOIN person p1 ON (p1.person_id = tp1.person_id)
86LEFT OUTER JOIN person p2 ON (p2.person_id = tp2.person_id)
87WHERE
88(
89m.feed_id IS NULL
90OR t1.feed_id IS NULL
91OR t2.feed_id IS NULL
92OR p1.feed_id IS NULL
93OR p2.feed_id IS NULL
94)
95AND m.begin_date <= @endDate
96AND m.begin_date >= @beginDate
97AND c.responsible_site_id = @SiteId
98ORDER by m.begin_date ASC;";
99
100 var prm = DALManager.CreateDALParameters();
101 prm.Add("@beginDate", beginDate);
102 prm.Add("@endDate", endDate);
103 prm.Add("@SiteId", RelatedSiteId);
104 var dt = CurrentDAL.GetDT(sql, CommandType.Text, prm, false, false, null, null);
105 return dt;
106 }
107
108 /// <summary>
109 /// Gets the mapped matches.
110 /// </summary>
111 /// <returns></returns>
112 public DataTable GetMappedMatches()
113 {
114 const string sql = @"DROP TEMPORARY TABLE IF EXISTS groupedTP1;
115DROP TEMPORARY TABLE IF EXISTS groupedTP2;
116
117CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP1
118(PRIMARY KEY (team_id, person_id, active_ind))
119SELECT person_id, team_id, active_ind FROM team_person tp
120INNER JOIN (
121 SELECT max(team_person_id) as tpi from team_person
122 GROUP by team_id, person_id
123) grp ON (grp.tpi = tp.team_person_id);
124
125CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP2
126(PRIMARY KEY (team_id, person_id, active_ind))
127SELECT * FROM groupedTP1;
128
129SELECT DISTINCT m.matches_id as 'MatchId', DATE_FORMAT(m.begin_date, '%m/%d/%Y %H:%i') as 'DateTime', t1.name as 'HomeTeamName', t2.name as 'AwayTeamName', c.name as 'CompetitionName', m.feed_id as 'GsmMatchId', t1.feed_id as 'GsmTeam1Id', t2.feed_id as 'GsmTeam2Id', t1.team_id as 'GoalTeam1Id', t2.team_id as 'GoalTeam2Id', m.use_gsm
130FROM matches m
131INNER JOIN round r ON (r.round_id = m.round_id)
132INNER JOIN season s ON (s.season_id = r.season_id)
133INNER JOIN competition c ON (c.competition_id = s.competition_id)
134INNER JOIN team t1 ON (t1.team_id = m.team1_id)
135INNER JOIN team t2 ON (t2.team_id = m.team2_id)
136LEFT OUTER JOIN groupedTP1 tp1 ON (tp1.team_id = t1.team_id AND tp1.active_ind = 'Y')
137LEFT OUTER JOIN groupedTP2 tp2 ON (tp2.team_id = t2.team_id AND tp2.active_ind = 'Y')
138LEFT OUTER JOIN person p1 ON (p1.person_id = tp1.person_id)
139LEFT OUTER JOIN person p2 ON (p2.person_id = tp2.person_id)
140WHERE
141(
142m.feed_id IS NOT NULL
143AND t1.feed_id IS NOT NULL
144AND t2.feed_id IS NOT NULL
145AND p1.feed_id IS NOT NULL
146AND p2.feed_id IS NOT NULL
147)
148AND m.begin_date <= (NOW() + INTERVAL 7 DAY)
149AND m.begin_date >= CURDATE()
150AND c.responsible_site_id = @SiteId
151ORDER by m.begin_date ASC LIMIT 100;";
152
153 var prm = DALManager.CreateDALParameters();
154 prm.Add("@SiteId", RelatedSiteId);
155 var dt = CurrentDAL.GetDT(sql, CommandType.Text, prm, false, false, null, null);
156 return dt;
157 }
158
159 /// <summary>
160 /// Gets the mapped matches.
161 /// </summary>
162 /// <param name="beginDate">The begin date.</param>
163 /// <param name="endDate">The end date.</param>
164 /// <returns></returns>
165 public DataTable GetMappedMatches(DateTime beginDate, DateTime endDate)
166 {
167 const string sql =
168 @"DROP TEMPORARY TABLE IF EXISTS groupedTP1;
169DROP TEMPORARY TABLE IF EXISTS groupedTP2;
170
171CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP1
172(PRIMARY KEY (team_id, person_id, active_ind))
173SELECT person_id, team_id, active_ind FROM team_person tp
174INNER JOIN (
175 SELECT max(team_person_id) as tpi from team_person
176 GROUP by team_id, person_id
177) grp ON (grp.tpi = tp.team_person_id);
178
179CREATE TEMPORARY TABLE IF NOT EXISTS groupedTP2
180(PRIMARY KEY (team_id, person_id, active_ind))
181SELECT * FROM groupedTP1;
182
183SELECT DISTINCT m.matches_id as 'MatchId', DATE_FORMAT(m.begin_date, '%m/%d/%Y %H:%i') as 'DateTime', t1.name as 'HomeTeamName', t2.name as 'AwayTeamName', c.name as 'CompetitionName', m.feed_id as 'GsmMatchId', t1.feed_id as 'GsmTeam1Id', t2.feed_id as 'GsmTeam2Id', t1.team_id as 'GoalTeam1Id', t2.team_id as 'GoalTeam2Id', m.use_gsm
184FROM matches m
185INNER JOIN round r ON (r.round_id = m.round_id)
186INNER JOIN season s ON (s.season_id = r.season_id)
187INNER JOIN competition c ON (c.competition_id = s.competition_id)
188INNER JOIN team t1 ON (t1.team_id = m.team1_id)
189INNER JOIN team t2 ON (t2.team_id = m.team2_id)
190LEFT OUTER JOIN groupedTP1 tp1 ON (tp1.team_id = t1.team_id AND tp1.active_ind = 'Y')
191LEFT OUTER JOIN groupedTP2 tp2 ON (tp2.team_id = t2.team_id AND tp2.active_ind = 'Y')
192LEFT OUTER JOIN person p1 ON (p1.person_id = tp1.person_id)
193LEFT OUTER JOIN person p2 ON (p2.person_id = tp2.person_id)
194WHERE
195(
196m.feed_id IS NOT NULL
197AND t1.feed_id IS NOT NULL
198AND t2.feed_id IS NOT NULL
199AND p1.feed_id IS NOT NULL
200AND p2.feed_id IS NOT NULL
201)
202AND m.begin_date <= @endDate
203AND m.begin_date >= @beginDate
204AND c.responsible_site_id = @SiteId
205ORDER by m.begin_date ASC;";
206
207 var prm = DALManager.CreateDALParameters();
208 prm.Add("@beginDate", beginDate);
209 prm.Add("@endDate", endDate);
210 prm.Add("@SiteId", RelatedSiteId);
211 var dt = CurrentDAL.GetDT(sql, CommandType.Text, prm, false, false, null, null);
212 return dt;
213 }