close Warning: Can't synchronize with repository "(default)" (/var/svn/tolp does not appear to be a Subversion repository.). Look in the Trac log for more information.

Ticket #877: nucleos.data.tol

File nucleos.data.tol, 32.1 KB (added by Jorge, 16 years ago)
Line 
1/* -*- mode: c++ -*- */
2
3Set Include( "config.tol" );
4Set Include( "../dataaccess/data.frame.tol" );
5Set Include( "../dataaccess/dbconnection.tol" );
6
7NameBlock LoaderDataEstimTarifa( NameBlock dbconn,
8                                     NameBlock args )
9{
10  Text tarifa = args::tarifa;
11  Real null_tarifa = getOptArg( args, "null_tarifa", 0 );
12  Real lower_vol3g = getOptArg( args, "lower_vol3g", -1 );
13  Real lower_duracion_voz = getOptArg( args, "lower_duracion_voz", -1 );
14  Real lower_cob_ba = getOptArg( args, "lower_cob_ba", -1 );
15  Real lower_cob_3g = getOptArg( args, "lower_cob_3g", -1 );
16  Real lower_cob_2g = getOptArg( args, "lower_cob_2g", -1 );
17  Text file.suffix = getOptArg( args, "file.suffix", "" );
18  Text select_category = getOptArg( args, "select_category", "" );
19  Set select_regions = getOptArg( args, "select_regions", Copy(Empty) );
20  Real needs_where = 0;
21  Text cond_v3g = If( lower_vol3g < 0,
22                      "", " AND qt_vol_datos_umts>" << lower_vol3g );
23  Text cond_duracion_voz =
24    If( lower_duracion_voz < 0,
25        "",
26        " AND qt_duracion_voz_gprs + qt_duracion_voz_umts>"<<
27        lower_duracion_voz );
28  Text cond_cob_ba = If( lower_vol3g < 0,
29                          "", " AND perc_cob_ba>" << lower_cob_ba );
30  Text cond_cob_3g = If( lower_cob_3g < 0,
31                          "", " AND perc_cob_umts>" << lower_cob_3g );
32  Text cond_cob_2g = If( lower_cob_2g < 0,
33                          "", " AND perc_cob_gprs>" << lower_cob_2g );
34  Text cond_region =
35    Case(
36         select_category == "", {
37           Text ""
38         },
39         select_category == "CCAA", {
40           sab.codautonomia = If( Card(select_regions), {
41               Text rr = If( needs_where,
42                 { Real needs_where:=0; Text " WHERE " },
43                 Text " AND " ) +
44                 "sab.codautonomia in " +
45                 BinGroup( "+",
46                           [[ "('" + select_regions[1] + "'" ]] <<
47                           Set For( 2, Card( select_regions), Text( Real k ) {
48                               Text ",'" + select_regions[ k ] + "'"
49                                 } ) << [[ ")" ]] )
50                 }, Text "" )
51         },
52         select_category == "PROV", {
53           Warning( "select_category = \"PROV\" no ha sido implementado todavia" );
54           Text ""
55         },
56         1 == 1, {
57           Warning( "select_category = " + select_category + " es invalido. Se asume no seleccion." );
58           Text ""
59         } );
60  Text query =
61"
62SELECT
63 tab.cnuc,
64 qt_lineas_datos_gprs_tarifa,
65 qt_lineas_datos_gprs as qt_lineas_datos_gprs_nucleo,
66 qt_lineas_datos_umts_tarifa,
67 qt_lineas_datos_umts as qt_lineas_datos_umts_nucleo,
68 qt_vol_datos_gprs_tarifa/(1024*1024) AS qt_vol_datos_gprs_tarifa,
69 qt_vol_datos_gprs/(1024*1024) AS qt_vol_datos_gprs_nucleo,
70 qt_vol_datos_umts_tarifa/(1024*1024) AS qt_vol_datos_umts_tarifa,
71 qt_vol_datos_umts/(1024*1024) AS qt_vol_datos_umts_nucleo,
72 qt_vol_datos_gprs_pot_tarifa/(1024*1024) AS qt_vol_datos_gprs_pot_tarifa,
73 qt_vol_datos_gprs_pot/(1024*1024) AS qt_vol_datos_gprs_pot_nucleo,
74 qt_vol_datos_umts_pot_tarifa/(1024*1024) AS qt_vol_datos_umts_pot_tarifa,
75 qt_vol_datos_umts_pot/(1024*1024) AS qt_vol_datos_umts_pot_nucleo,
76 qt_lineas_datos_censored AS qt_lineas_datos_censored_nucleo ,
77 qt_vol_datos_umts_censored/(1024*1024) AS qt_vol_datos_umts_censored_nucleo,
78 qt_lineas_voz_gprs_tarifa,
79 qt_lineas_voz_gprs  AS qt_lineas_voz_gprs_nucleo,
80 qt_lineas_voz_umts_tarifa,
81 qt_lineas_voz_umts  AS qt_lineas_voz_umts_nucleo,
82 qt_duracion_voz_gprs_tarifa,
83 qt_duracion_voz_gprs  AS qt_duracion_voz_gprs_nucleo,
84 qt_duracion_voz_umts_tarifa,
85 qt_duracion_voz_umts  AS qt_duracion_voz_umts_nucleo,
86 qt_llamadas_voz_gprs_tarifa,
87 qt_llamadas_voz_gprs  AS qt_llamadas_voz_gprs_nucleo,
88 qt_llamadas_voz_umts_tarifa,
89 qt_llamadas_voz_umts  AS qt_llamadas_voz_umts_nucleo,
90 perc_cob_gprs, perc_cob_umts,
91 tab.locales AS QT_LOCALES,
92 tab.accesostotales AS QT_RTB_TOTAL,
93 tab.adsltotales AS QT_ADSL_TOTAL,
94 tab.accesosempresas AS QT_RTB_NRES,
95 tab.adslempresas AS QT_ADSL_NRES,
96 CASE WHEN qt_poblacion IS NULL THEN 0 ELSE qt_poblacion END as qt_poblacion,
97 CASE WHEN qt_lineas_rtb IS NULL THEN 0 ELSE qt_lineas_rtb END as qt_lineas_rtb,
98 CASE WHEN perc_cob_ba IS NULL THEN 0 ELSE perc_cob_ba END as perc_cob_ba,
99 CASE WHEN perc_cob_ba IS NULL THEN 0
100      WHEN perc_cob_ba < 0.5 THEN 0
101          WHEN perc_cob_ba >= 0.5 THEN 1 ELSE NULL END as ind_perc_cob_ba,
102 qt_pob_no_cob_ba,
103 tipol_muni,
104 CASE WHEN  \"numero emplazamientos UMTS datos\" IS NULL THEN 0 ELSE \"numero emplazamientos UMTS datos\" END AS qt_emps_3g,
105 tipo,
106 CASE WHEN \"TOTAL VIVIENDAS\" IS NULL OR \"TOTAL VIVIENDAS\" < 0 THEN 0 ELSE \"TOTAL VIVIENDAS\" END AS qt_viviendas_tot,
107 CASE WHEN accesosrtbcorr IS NULL THEN 0 ELSE accesosrtbcorr END AS qt_rtb
108FROM table_nucleo_modelo_agr_tarifa_092009 tab
109     INNER JOIN
110     sabana sab
111     ON tab.cnuc = sab.cnuc
112WHERE qt_poblacion > 0 AND tarifa='" + tarifa + "'" +
113    cond_v3g + cond_duracion_voz + cond_cob_ba + cond_cob_3g + cond_cob_2g + cond_region;
114  Text msg = "Cargando datos por nucleo para la tarifa '" + tarifa + "'";
115  Text file.prefix =
116    "data.nucleos.tarifa."+Replace( tarifa, " ", "_" )+file.suffix;
117  NameBlock loader =
118    CreateDataLoader.SQL( dbconn, query, msg, file.prefix,
119                          ConfigNucleos::data.dir )
120 
121};
122
123// NameBlock loader = LoaderDataForTarifa( RaDe3G.DB::rade3g, "Big Screen" );
124// Set bs.data = loader::LoadData( 0 );
125//
126
127NameBlock LoaderDataForecastNucleo( NameBlock dbconn,
128                                    NameBlock args )
129{
130  Real lower_vol3g = getOptArg( args, "lower_vol3g", -1 );
131  Real lower_duracion_voz = getOptArg( args, "lower_duracion_voz", -1 );
132  Real lower_cob_ba = getOptArg( args, "lower_cob_ba", -1 );
133  Real lower_cob_3g = getOptArg( args, "lower_cob_3g", -1 );
134  Real lower_cob_2g = getOptArg( args, "lower_cob_2g", -1 );
135  Text file.suffix = getOptArg( args, "file.suffix", "" );
136  Text select_category = getOptArg( args, "select_category", "" );
137  Set select_regions = getOptArg( args, "select_regions", Copy(Empty) );
138  Real needs_where = 1;
139  Text cond_v3g =
140    If( lower_vol3g < 0,
141        "", {
142          If( needs_where,
143            { Real needs_where:=0;Text " WHERE " },
144            Text " AND " ) +
145            "qt_vol_datos_umts>" << lower_vol3g } );
146  Text cond_duracion_voz =
147    If( lower_duracion_voz < 0,
148        "",
149        If( needs_where,
150          { Real needs_where:=0;Text " WHERE " },
151          Text " AND " ) +
152        "qt_duracion_voz_gprs + qt_duracion_voz_umts>"<<
153        lower_duracion_voz );
154  Text cond_cob_ba =
155    If( lower_cob_ba < 0,
156        "",
157        If( needs_where,
158          { Real needs_where:=0;Text " WHERE " },
159          Text " AND " ) +
160        "perc_cob_ba>" << lower_cob_ba );
161  Text cond_region =
162    Case(
163         select_category == "", {
164           Text ""
165         },
166         select_category == "CCAA", {
167           sab.codautonomia = If( Card(select_regions), {
168               Text rr = If( needs_where,
169                 { Real needs_where:=0; Text " WHERE " },
170                 Text " AND " ) +
171                 "sab.codautonomia in " +
172                 BinGroup( "+",
173                           [[ "('" + select_regions[1] + "'" ]] <<
174                           Set For( 2, Card( select_regions), Text( Real k ) {
175                               Text ",'" + select_regions[ k ] + "'"
176                                 } ) << [[ ")" ]] )
177                 }, Text "" )
178         },
179         select_category == "PROV", {
180           Warning( "select_category = \"PROV\" no ha sido implementado todavia" );
181           Text ""
182         },
183         1 == 1, {
184           Warning( "select_category = " + select_category + " es invalido. Se asume no seleccion." );
185           Text ""
186         } );
187  Text query =
188"
189SELECT
190 tab.cnuc,
191 CASE WHEN qt_lineas_datos_gprs IS NULL THEN 0 ELSE qt_lineas_datos_gprs END AS qt_lineas_datos_gprs_nucleo,
192 CASE WHEN qt_lineas_datos_umts IS NULL THEN 0 ELSE qt_lineas_datos_umts END AS qt_lineas_datos_umts_nucleo,
193 CASE WHEN qt_vol_datos_gprs IS NULL THEN 0 ELSE qt_vol_datos_gprs/(1024*1024) END AS qt_vol_datos_gprs_nucleo,
194 CASE WHEN qt_vol_datos_umts IS NULL THEN 0 ELSE qt_vol_datos_umts/(1024*1024) END AS qt_vol_datos_umts_nucleo,
195 CASE WHEN qt_vol_datos_gprs_pot IS NULL THEN 0 ELSE qt_vol_datos_gprs_pot/(1024*1024) END AS qt_vol_datos_gprs_pot_nucleo,
196 CASE WHEN qt_vol_datos_umts_pot IS NULL THEN 0 ELSE qt_vol_datos_umts_pot/(1024*1024) END AS qt_vol_datos_umts_pot_nucleo,
197 CASE WHEN qt_lineas_datos_censored IS NULL THEN 0 ELSE qt_lineas_datos_censored END AS qt_lineas_datos_censored_nucleo ,
198 CASE WHEN qt_vol_datos_umts_censored IS NULL THEN 0
199      ELSE qt_vol_datos_umts_censored/(1024*1024) END AS qt_vol_datos_umts_censored_nucleo,
200 CASE WHEN qt_lineas_voz_gprs IS NULL THEN 0 ELSE qt_lineas_voz_gprs END AS qt_lineas_voz_gprs_nucleo,
201 CASE WHEN qt_lineas_voz_umts IS NULL THEN 0 ELSE qt_lineas_voz_umts END AS qt_lineas_voz_umts_nucleo,
202 CASE WHEN qt_duracion_voz_gprs IS NULL THEN 0 ELSE qt_duracion_voz_gprs END AS qt_duracion_voz_gprs_nucleo,
203 CASE WHEN qt_duracion_voz_umts IS NULL THEN 0 ELSE qt_duracion_voz_umts END AS qt_duracion_voz_umts_nucleo,
204 CASE WHEN qt_llamadas_voz_gprs IS NULL THEN 0 ELSE qt_llamadas_voz_gprs END AS qt_llamadas_voz_gprs_nucleo,
205 CASE WHEN qt_llamadas_voz_umts IS NULL THEN 0 ELSE qt_llamadas_voz_umts END AS qt_llamadas_voz_umts_nucleo,
206 CASE WHEN perc_cob_gprs IS NULL THEN 0 ELSE perc_cob_gprs END AS perc_cob_gprs,
207 CASE WHEN perc_cob_umts IS NULL OR perc_cob_umts < 1 THEN 1 ELSE perc_cob_umts END AS perc_cob_umts,
208 tab.locales AS QT_LOCALES,
209 tab.accesostotales AS QT_RTB_TOTAL,
210 tab.adsltotales AS QT_ADSL_TOTAL,
211 tab.accesosempresas AS QT_RTB_NRES,
212 tab.adslempresas AS QT_ADSL_NRES,
213 CASE WHEN qt_poblacion IS NULL THEN 0 ELSE qt_poblacion END AS qt_poblacion,
214 CASE WHEN qt_lineas_rtb IS NULL THEN 0 ELSE qt_lineas_rtb END AS qt_lineas_rtb,
215 CASE WHEN perc_cob_ba IS NULL THEN 0 ELSE perc_cob_ba END AS perc_cob_ba,
216 CASE WHEN perc_cob_ba IS NULL THEN 0
217      WHEN perc_cob_ba < 0.5 THEN 0
218          WHEN perc_cob_ba >= 0.5 THEN 1 ELSE NULL END as ind_perc_cob_ba,
219 CASE WHEN qt_pob_no_cob_ba IS NULL THEN 0 ELSE qt_pob_no_cob_ba END AS qt_pob_no_cob_ba,
220 CASE WHEN tipol_muni IS NULL THEN 0 ELSE tipol_muni END AS tipol_muni,
221 CASE WHEN \"numero emplazamientos UMTS datos\" IS NULL THEN 0 ELSE \"numero emplazamientos UMTS datos\" END AS qt_emps_3g,
222 CASE WHEN tipo IS NULL THEN 0 ELSE tipo END AS tipo,
223 CASE WHEN \"TOTAL VIVIENDAS\" IS NULL OR \"TOTAL VIVIENDAS\" < 0 THEN 0 ELSE \"TOTAL VIVIENDAS\" END AS qt_viviendas_tot,
224 CASE WHEN accesosrtbcorr IS NULL THEN 0 ELSE accesosrtbcorr END AS qt_rtb
225FROM table_nucleo_modelo_092009 tab
226     INNER JOIN
227     sabana sab
228     ON tab.cnuc = sab.cnuc" +
229cond_v3g + cond_duracion_voz + cond_cob_ba + cond_region ;
230  Text msg = "Cargando datos para prediccion por nucleo";
231  Text file.prefix =
232    "data.nucleos."+file.suffix;
233  NameBlock loader =
234    CreateDataLoader.SQL( dbconn, query, msg, file.prefix,
235                          ConfigNucleos::data.dir )
236 
237};
238
239// NameBlock loader = LoaderDataForTarifa( RaDe3G.DB::rade3g, "Big Screen" );
240// Set bs.data = loader::LoadData( 0 );
241//
242
243NameBlock LoaderDataNucleos( NameBlock dbconn,
244                            NameBlock args )
245{
246  Real lower_vol3g = getOptArg( args, "lower_vol3g", -1 );
247  Real lower_duracion_voz = getOptArg( args, "lower_duracion_voz", -1 );
248  Real lower_cob_ba = getOptArg( args, "lower_cob_ba", -1 );
249  Real lower_cob_3g = getOptArg( args, "lower_cob_3g", -1 );
250  Real lower_cob_2g = getOptArg( args, "lower_cob_2g", -1 );
251  Real is_estim = getOptArg( args, "is_estim", 0 );
252  Text file.suffix = getOptArg( args, "file.suffix", "" );
253  Text select_category = getOptArg( args, "select_category", "" );
254  Set select_regions = getOptArg( args, "select_regions", Copy(Empty) );
255  Real needs_where = 1;
256  Text cond_v3g =
257    If( lower_vol3g < 0,
258        "", {
259          If( needs_where,
260            { Real needs_where:=0;Text " WHERE " },
261            Text " AND " ) +
262            "qt_vol_datos_umts>" << lower_vol3g } );
263  Text cond_duracion_voz =
264    If( lower_duracion_voz < 0,
265        "",
266        If( needs_where,
267          { Real needs_where:=0;Text " WHERE " },
268          Text " AND " ) +
269        "qt_duracion_voz_gprs + qt_duracion_voz_umts>"<<
270        lower_duracion_voz );
271  Text cond_cob_ba =
272    If( lower_cob_ba < 0,
273        "",
274        If( needs_where,
275          { Real needs_where:=0;Text " WHERE " },
276          Text " AND " ) +
277        "perc_cob_ba>" << lower_cob_ba );
278  Text cond_cob_3g =
279    If( !is_estim | (lower_cob_3g < 0),
280        "",
281        If( needs_where,
282          { Real needs_where:=0;Text " WHERE " },
283          Text " AND " ) +
284        " perc_cob_umts>" << lower_cob_3g );
285  Text cond_cob_2g =
286    If( !is_estim | (lower_cob_2g < 0),
287        "",
288        If( needs_where,
289          { Real needs_where:=0;Text " WHERE " },
290          Text " AND " ) +
291        " perc_cob_gprs>" << lower_cob_2g );
292  Text var_cob_3g  =
293    If( !is_estim,
294        "CASE WHEN perc_cob_umts IS NULL OR perc_cob_umts <" << lower_cob_3g
295        <<  " THEN " << lower_cob_3g
296        << " ELSE perc_cob_umts END AS perc_cob_umts,", "perc_cob_umts," );
297  Text cond_region =
298    Case(
299         select_category == "", {
300           Text ""
301         },
302         select_category == "CCAA", {
303           sab.codautonomia = If( Card(select_regions), {
304               Text rr = If( needs_where,
305                 { Real needs_where:=0; Text " WHERE " },
306                 Text " AND " ) +
307                 "sab.codautonomia in " +
308                 BinGroup( "+",
309                           [[ "('" + select_regions[1] + "'" ]] <<
310                           Set For( 2, Card( select_regions), Text( Real k ) {
311                               Text ",'" + select_regions[ k ] + "'"
312                                 } ) << [[ ")" ]] )
313                 }, Text "" )
314         },
315         select_category == "PROV", {
316           Warning( "select_category = \"PROV\" no ha sido implementado todavia" );
317           Text ""
318         },
319         1 == 1, {
320           Warning( "select_category = " + select_category + " es invalido. Se asume no seleccion." );
321           Text ""
322         } );
323  Text query =
324"
325SELECT
326 tab.cnuc,
327 CASE WHEN qt_lineas_datos_gprs IS NULL THEN 0 ELSE qt_lineas_datos_gprs END AS qt_lineas_datos_gprs_nucleo,
328 CASE WHEN qt_lineas_datos_umts IS NULL THEN 0 ELSE qt_lineas_datos_umts END AS qt_lineas_datos_umts_nucleo,
329 CASE WHEN qt_vol_datos_gprs IS NULL THEN 0 ELSE qt_vol_datos_gprs/(1024*1024) END AS qt_vol_datos_gprs_nucleo,
330 CASE WHEN qt_vol_datos_umts IS NULL THEN 0 ELSE qt_vol_datos_umts/(1024*1024) END AS qt_vol_datos_umts_nucleo,
331 CASE WHEN qt_vol_datos_gprs_pot IS NULL THEN 0 ELSE qt_vol_datos_gprs_pot/(1024*1024) END AS qt_vol_datos_gprs_pot_nucleo,
332 CASE WHEN qt_vol_datos_umts_pot IS NULL THEN 0 ELSE qt_vol_datos_umts_pot/(1024*1024) END AS qt_vol_datos_umts_pot_nucleo,
333 CASE WHEN qt_lineas_datos_censored IS NULL THEN 0 ELSE qt_lineas_datos_censored END AS qt_lineas_datos_censored_nucleo ,
334 CASE WHEN qt_vol_datos_umts_censored IS NULL THEN 0
335      ELSE qt_vol_datos_umts_censored/(1024*1024) END AS qt_vol_datos_umts_censored_nucleo,
336 CASE WHEN qt_lineas_voz_gprs IS NULL THEN 0 ELSE qt_lineas_voz_gprs END AS qt_lineas_voz_gprs_nucleo,
337 CASE WHEN qt_lineas_voz_umts IS NULL THEN 0 ELSE qt_lineas_voz_umts END AS qt_lineas_voz_umts_nucleo,
338 CASE WHEN qt_duracion_voz_gprs IS NULL THEN 0 ELSE qt_duracion_voz_gprs END AS qt_duracion_voz_gprs_nucleo,
339 CASE WHEN qt_duracion_voz_umts IS NULL THEN 0 ELSE qt_duracion_voz_umts END AS qt_duracion_voz_umts_nucleo,
340 CASE WHEN qt_llamadas_voz_gprs IS NULL THEN 0 ELSE qt_llamadas_voz_gprs END AS qt_llamadas_voz_gprs_nucleo,
341 CASE WHEN qt_llamadas_voz_umts IS NULL THEN 0 ELSE qt_llamadas_voz_umts END AS qt_llamadas_voz_umts_nucleo,
342 CASE WHEN perc_cob_gprs IS NULL THEN 0 ELSE perc_cob_gprs END AS perc_cob_gprs,"+var_cob_3g+
343"tab.locales AS QT_LOCALES,
344 tab.accesostotales AS QT_RTB_TOTAL,
345 tab.adsltotales AS QT_ADSL_TOTAL,
346 tab.accesosempresas AS QT_RTB_NRES,
347 tab.adslempresas AS QT_ADSL_NRES,
348 CASE WHEN qt_poblacion IS NULL THEN 0 ELSE qt_poblacion END AS qt_poblacion,
349 CASE WHEN qt_lineas_rtb IS NULL THEN 0 ELSE qt_lineas_rtb END AS qt_lineas_rtb,
350 CASE WHEN perc_cob_ba IS NULL THEN 0 ELSE perc_cob_ba END AS perc_cob_ba,
351 CASE WHEN perc_cob_ba IS NULL THEN 0
352      WHEN perc_cob_ba < 0.5 THEN 0
353          WHEN perc_cob_ba >= 0.5 THEN 1 ELSE NULL END as ind_perc_cob_ba,
354 CASE WHEN qt_pob_no_cob_ba IS NULL THEN 0 ELSE qt_pob_no_cob_ba END AS qt_pob_no_cob_ba,
355 CASE WHEN tipol_muni IS NULL THEN 0 ELSE tipol_muni END AS tipol_muni,
356 CASE WHEN \"numero emplazamientos UMTS datos\" IS NULL THEN 0 ELSE \"numero emplazamientos UMTS datos\" END AS qt_emps_3g,
357 CASE WHEN tipo IS NULL THEN 0 ELSE tipo END AS tipo,
358 CASE WHEN \"TOTAL VIVIENDAS\" IS NULL OR \"TOTAL VIVIENDAS\" < 0 THEN 0 ELSE \"TOTAL VIVIENDAS\" END AS qt_viviendas_tot,
359 CASE WHEN accesosrtbcorr IS NULL THEN 0 ELSE accesosrtbcorr END AS qt_rtb
360FROM table_nucleo_modelo_092009 tab
361     INNER JOIN
362     sabana sab
363     ON tab.cnuc = sab.cnuc" +
364cond_v3g + cond_duracion_voz + cond_cob_ba + cond_cob_3g + cond_cob_2g + cond_region;
365  Text msg = "Cargando datos para " +
366    If( is_estim, "estimacion ", "prediccion " ) + "por nucleo";
367  Text file.prefix =
368    "data.nucleos." + If( is_estim, "estim", "forecast" );
369  NameBlock loader =
370    CreateDataLoader.SQL( dbconn, query, msg, file.prefix,
371                          ConfigNucleos::data.dir )
372 
373};
374
375NameBlock LoaderDataNucleosWeightedPobRTB( NameBlock dbconn,
376                                           NameBlock args )
377{
378  Real lower_vol3g = getOptArg( args, "lower_vol3g", -1 );
379  Real lower_duracion_voz = getOptArg( args, "lower_duracion_voz", -1 );
380  Real lower_cob_ba = getOptArg( args, "lower_cob_ba", -1 );
381  Real lower_cob_3g = getOptArg( args, "lower_cob_3g", -1 );
382  Real lower_cob_2g = getOptArg( args, "lower_cob_2g", -1 );
383  Real is_estim = getOptArg( args, "is_estim", 0 );
384  Text file.suffix = getOptArg( args, "file.suffix", "" );
385  Text select_category = getOptArg( args, "select_category", "" );
386  Set select_regions = getOptArg( args, "select_regions", Copy(Empty) );
387  Text cond_pob = " where qt_poblacion < 100000"
388    //" where qt_poblacion  1000000";
389  Real needs_where = 0;
390  Text cond_v3g =
391    If( lower_vol3g < 0,
392        "", {
393          If( needs_where,
394            { Real needs_where:=0;Text " WHERE " },
395            Text " AND " ) +
396            "qt_vol_datos_umts>" << lower_vol3g } );
397  Text cond_duracion_voz =
398    If( lower_duracion_voz < 0,
399        "",
400        If( needs_where,
401          { Real needs_where:=0;Text " WHERE " },
402          Text " AND " ) +
403        "qt_duracion_voz_gprs + qt_duracion_voz_umts>"<<
404        lower_duracion_voz );
405  Text cond_cob_ba =
406    If( lower_cob_ba < 0,
407        "",
408        If( needs_where,
409          { Real needs_where:=0;Text " WHERE " },
410          Text " AND " ) +
411        "perc_cob_ba>" << lower_cob_ba );
412  Text cond_cob_3g =
413    If( !is_estim | (lower_cob_3g < 0),
414        "",
415        If( needs_where,
416          { Real needs_where:=0;Text " WHERE " },
417          Text " AND " ) +
418        " perc_cob_umts>" << lower_cob_3g );
419  Text cond_cob_2g =
420    If( !is_estim | (lower_cob_2g < 0),
421        "",
422        If( needs_where,
423          { Real needs_where:=0;Text " WHERE " },
424          Text " AND " ) +
425        " perc_cob_gprs>" << lower_cob_2g );
426  Text var_cob_3g  =
427    If( !is_estim,
428        "CASE WHEN perc_cob_umts <" << lower_cob_3g
429        <<  " THEN " << lower_cob_3g
430        << " ELSE perc_cob_umts END AS perc_cob_umts,", "perc_cob_umts," );
431  Text cond_region =
432    Case(
433         select_category == "", {
434           Text ""
435         },
436         select_category == "CCAA", {
437           sab.codautonomia = If( Card(select_regions), {
438               Text rr = If( needs_where,
439                 { Real needs_where:=0; Text " WHERE " },
440                 Text " AND " ) +
441                 "sab.codautonomia in " +
442                 BinGroup( "+",
443                           [[ "('" + select_regions[1] + "'" ]] <<
444                           Set For( 2, Card( select_regions), Text( Real k ) {
445                               Text ",'" + select_regions[ k ] + "'"
446                                 } ) << [[ ")" ]] )
447                 }, Text "" )
448         },
449         select_category == "PROV", {
450           Warning( "select_category = \"PROV\" no ha sido implementado todavia" );
451           Text ""
452         },
453         1 == 1, {
454           Warning( "select_category = " + select_category + " es invalido. Se asume no seleccion." );
455           Text ""
456         } );
457  Text query =
458"
459SELECT
460 tab.cnuc,
461 tab.qt_lineas_datos_gprs as qt_lineas_datos_gprs_nucleo,
462 tab.qt_lineas_datos_umts as qt_lineas_datos_umts_nucleo,
463 tab.qt_vol_datos_gprs/(1024*1024) as qt_vol_datos_gprs_nucleo,
464 tab.qt_vol_datos_umts/(1024*1024) as qt_vol_datos_umts_nucleo,
465 tab.qt_vol_datos_gprs_pot/(1024*1024) AS qt_vol_datos_gprs_pot_nucleo,
466 tab.qt_vol_umts_pot_1/(1024*1024) AS qt_vol_datos_umts_pot_nucleo,
467 tab.qt_lineas_datos_censored as qt_lineas_datos_censored_nucleo ,
468 tab.qt_vol_datos_umts_censored/(1024*1024) AS qt_vol_datos_umts_censored_nucleo,
469 tab.qt_lineas_voz_gprs AS qt_lineas_voz_gprs_nucleo,
470 tab.qt_lineas_voz_umts AS qt_lineas_voz_umts_nucleo,
471 tab.qt_duracion_voz_gprs AS qt_duracion_voz_gprs_nucleo,
472 tab.qt_duracion_voz_umts AS qt_duracion_voz_umts_nucleo,
473 tab.qt_llamadas_voz_gprs AS qt_llamadas_voz_gprs_nucleo,
474 tab.qt_llamadas_voz_umts AS qt_llamadas_voz_umts_nucleo,
475 tab.perc_cob_gprs,"+var_cob_3g+
476"tab.qt_locales,
477 tab.qt_rtb_total0,
478 tab.qt_adsl_total,
479 tab.qt_rtb_empresas AS QT_RTB_NRES,
480 tab.qt_adsl_empresa AS QT_ADSL_NRES,
481 tab.qt_poblacion,
482 tab.qt_rtb_total AS qt_lineas_rtb,
483 tab.perc_cob_ba,
484 CASE WHEN perc_cob_ba < 0.5  THEN 0
485      WHEN perc_cob_ba >= 0.5 THEN 1 END as ind_perc_cob_ba,
486 tab.qt_pob_no_cob_ba,
487 tab.tipol_muni,
488 -- sab.\"numero emplazamientos UMTS datos\" IS NULL THEN 0 ELSE \"numero emplazamientos UMTS datos\" END AS qt_emps_3g,
489 -- CASE WHEN tipo IS NULL THEN 0 ELSE tipo END AS tipo,
490 --CASE WHEN sab.\"TOTAL VIVIENDAS\" IS NULL OR sab.\"TOTAL VIVIENDAS\" < 0 THEN 0 ELSE sab.\"TOTAL VIVIENDAS\" END AS qt_viviendas_tot
491 tab.qt_viviendas_total
492 --,CASE WHEN accesosrtbcorr IS NULL THEN 0 ELSE accesosrtbcorr END AS qt_rtb
493FROM
494table_modelo_nucleo_102009 tab
495-- view_nucleo_modelo_wpobrtb_agr_102009 tab
496--     INNER JOIN
497--     sabana sab
498--     ON tab.cnuc = sab.cnuc\n" +cond_pob+
499cond_v3g + cond_duracion_voz + cond_cob_ba + cond_cob_3g + cond_cob_2g + cond_region;
500  Text msg = "Cargando datos para " +
501    If( is_estim, "estimacion ", "prediccion " ) + "por nucleo";
502  Text file.prefix =
503    "data.nucleos." + If( is_estim, "estim", "forecast" );
504  NameBlock loader =
505    CreateDataLoader.SQL( dbconn, query, msg, file.prefix,
506                          ConfigNucleos::data.dir )
507 
508};
509
510/*
511NameBlock estim_args =
512[[
513  Real lower_vol3g = -1;
514  Real lower_duracion_voz = 1000;
515  Real lower_cob_ba = 0.05;
516  Real lower_cob_3g = 0.2;
517  Real is_estim = 1;
518  Real rebuild = 1
519]];
520
521NameBlock forecast_args =
522[[
523  Real lower_vol3g = -1;
524  Real lower_duracion_voz = -1;
525  Real lower_cob_ba = -1;
526  Real lower_cob_3g = 0.2;
527  Real is_estim = 0;
528  Real rebuild = 1
529]];
530
531NameBlock ld = LoaderDataNucleo( RaDe3G.DB::rade3g, forecast_args );
532Set data = ld::LoadData( 1 );
533 */
534
535Real BuildVariablePredictors( NameBlock df )
536{
537  Matrix V2G_NUC = df::GetColumn( "QT_VOL_DATOS_GPRS_NUCLEO" );
538  Matrix V3G_NUC = df::GetColumn( "QT_VOL_DATOS_UMTS_NUCLEO" );
539  Matrix V2G_POT_NUC = df::GetColumn( "QT_VOL_DATOS_GPRS_POT_NUCLEO" );
540  Matrix V3G_POT_NUC = df::GetColumn( "QT_VOL_DATOS_UMTS_POT_NUCLEO" );
541  Matrix V3G_CENSORED_NUC = df::GetColumn( "QT_VOL_DATOS_UMTS_CENSORED_NUCLEO" );
542
543  Matrix L3G_CENSORED_NUC = df::GetColumn( "QT_LINEAS_DATOS_CENSORED_NUCLEO" );
544  Matrix L3G_NUC = df::GetColumn( "QT_LINEAS_DATOS_UMTS_NUCLEO" );
545
546  Matrix LINEAS_VOZ_2G = df::GetColumn( "QT_LINEAS_VOZ_GPRS_NUCLEO" );
547  Matrix LINEAS_VOZ_3G = df::GetColumn( "QT_LINEAS_VOZ_UMTS_NUCLEO" );
548  Matrix LINEAS_VOZ_TOT = LINEAS_VOZ_2G + LINEAS_VOZ_3G;
549
550  Matrix DURACION_VOZ_2G = df::GetColumn( "QT_DURACION_VOZ_GPRS_NUCLEO" );
551  Matrix DURACION_VOZ_3G = df::GetColumn( "QT_DURACION_VOZ_UMTS_NUCLEO" );
552  Matrix DURACION_VOZ_TOT = DURACION_VOZ_2G + DURACION_VOZ_3G;
553
554  Matrix LLAMADAS_VOZ_2G = df::GetColumn( "QT_LLAMADAS_VOZ_GPRS_NUCLEO" );
555  Matrix LLAMADAS_VOZ_3G = df::GetColumn( "QT_LLAMADAS_VOZ_UMTS_NUCLEO" );
556  Matrix LLAMADAS_VOZ_TOT = LLAMADAS_VOZ_2G + LLAMADAS_VOZ_3G;
557
558  Matrix PERC_COB_2G = df::GetColumn( "PERC_COB_GPRS" );
559  Matrix PERC_COB_3G = df::GetColumn( "PERC_COB_UMTS" );
560
561  Matrix LINEAS_RTB = df::GetColumn( "QT_LINEAS_RTB" );
562  Matrix PERC_COB_BA = df::GetColumn( "PERC_COB_BA" );
563  Matrix IND_PERC_COB_BA = df::GetColumn( "IND_PERC_COB_BA" );
564
565  // Matrix RTB = df::GetColumn( "QT_RTB" );
566 
567  Matrix ADSL_TOT = df::GetColumn( "QT_ADSL_TOTAL" );
568 
569  // Matrix EMPS_3G = df::GetColumn( "QT_EMPS_3G" );
570 
571  // Matrix TIPO = df::GetColumn( "TIPO" );
572 
573  // Matrix VIVIENDAS_TOT = df::GetColumn( "QT_VIVIENDAS_TOT" );
574 
575  Matrix POB = df::GetColumn( "QT_POBLACION" );
576  Matrix POB_NO_COB_BA = df::GetColumn( "QT_POB_NO_COB_BA" );
577
578  Matrix MARKET = POB + LINEAS_RTB*2;
579  Matrix Log_MARKET = Log( MARKET + 1 );
580  Real df::AppendVariable( Log_MARKET );
581  Matrix COMPETENCIA = RatioPen( DURACION_VOZ_TOT, MARKET );
582  Matrix Log_COMPETENCIA = Log( COMPETENCIA + 1 );
583  Real df::AppendVariable( Log_COMPETENCIA );
584 
585  Matrix TIPOL_MUNI = df::GetColumn( "TIPOL_MUNI" );
586  Real max_tipol_ = MatMax( TIPOL_MUNI );
587  Matrix MAX_TIPOL = Rand( Rows( TIPOL_MUNI ), 1, max_tipol_, max_tipol_ );
588  Matrix TRUE_IND = Not( Not( TIPOL_MUNI ) );
589  Matrix TIPOL_MUNI_INCR = ( MAX_TIPOL - TIPOL_MUNI + 1 ) $* TRUE_IND;
590  Matrix Log_TIPOL_MUNI_INCR = Log( TIPOL_MUNI_INCR + 1 );
591  Real df::AppendVariable( TIPOL_MUNI_INCR );
592  Real df::AppendVariable( Log_TIPOL_MUNI_INCR );
593
594  Matrix Log_V2G_NUC = Log( V2G_NUC + 1 );
595  Real df::AppendVariable( Log_V2G_NUC );
596
597  Matrix Log_V3G_NUC = Log( V3G_NUC + 1 );
598  Real df::AppendVariable( Log_V3G_NUC );
599
600  Matrix Log_L3G_NUC = Log( L3G_NUC + 1 );
601  Real df::AppendVariable( Log_L3G_NUC );
602 
603  Matrix Log_V2G_POT_NUC = Log( V2G_POT_NUC + 1 );
604  Real df::AppendVariable( Log_V2G_POT_NUC );
605
606  Matrix Log_V3G_POT_NUC = Log( V3G_POT_NUC + 1 );
607  Real df::AppendVariable( Log_V3G_POT_NUC );
608
609  Matrix V3GxL3G_POT_NUC = RatioPen( V3G_POT_NUC, L3G_NUC );
610  Matrix Log_V3GxL3G_POT_NUC = Log( V3GxL3G_POT_NUC + 1 );
611  Real df::AppendVariable( Log_V3GxL3G_POT_NUC );
612 
613  Matrix Log_V3G_CENSORED_NUC = Log( V3G_CENSORED_NUC + 1 );
614  Real df::AppendVariable( Log_V3G_CENSORED_NUC ); 
615
616  Matrix Log_L3G_CENSORED_NUC = Log( L3G_CENSORED_NUC + 1 );
617  Real df::AppendVariable( Log_L3G_CENSORED_NUC );
618 
619  Matrix V3GxL3G_CENSORED = RatioPen( V3G_CENSORED_NUC, L3G_CENSORED_NUC );
620  Matrix Log_V3GxL3G_CENSORED_NUC = Log( V3GxL3G_CENSORED + 1 );
621  Real df::AppendVariable( Log_V3GxL3G_CENSORED_NUC ); 
622 
623  Matrix Log_LINEAS_VOZ_2G = Log( LINEAS_VOZ_2G + 1 );
624  Real df::AppendVariable( Log_LINEAS_VOZ_2G );
625
626  Matrix Log_LINEAS_VOZ_3G = Log( LINEAS_VOZ_3G + 1 );
627  Real df::AppendVariable( Log_LINEAS_VOZ_3G );
628
629  Matrix Log_LINEAS_VOZ_TOT = Log( LINEAS_VOZ_TOT + 1 );
630  Real df::AppendVariable( Log_LINEAS_VOZ_TOT );
631
632  Matrix Log_DURACION_VOZ_2G = Log( DURACION_VOZ_2G + 1 );
633  Real df::AppendVariable( Log_DURACION_VOZ_2G );
634
635  Matrix Log_DURACION_VOZ_3G = Log( DURACION_VOZ_3G + 1 );
636  Real df::AppendVariable( Log_DURACION_VOZ_3G );
637
638  Matrix Log_DURACION_VOZ_TOT = Log( DURACION_VOZ_TOT + 1 );
639  Real df::AppendVariable( Log_DURACION_VOZ_TOT );
640
641  Matrix Log_LLAMADAS_VOZ_2G = Log( LLAMADAS_VOZ_2G + 1 );
642  Real df::AppendVariable( Log_LLAMADAS_VOZ_2G );
643
644  Matrix Log_LLAMADAS_VOZ_3G = Log( LLAMADAS_VOZ_3G + 1 );
645  Real df::AppendVariable( Log_LLAMADAS_VOZ_3G );
646
647  Matrix Log_LLAMADAS_VOZ_TOT = Log( LLAMADAS_VOZ_TOT + 1 );
648  Real df::AppendVariable( Log_LLAMADAS_VOZ_TOT );
649
650  Matrix Log_PERC_COB_2G = Log( PERC_COB_2G + 1 );
651  Real df::AppendVariable( Log_PERC_COB_2G );
652 
653  Matrix Log_PERC_COB_3G = Log( PERC_COB_3G + 1 );
654  Real df::AppendVariable( Log_PERC_COB_3G );
655
656  Matrix Log_LINEAS_RTB = Log( LINEAS_RTB + 1 );
657  Real df::AppendVariable( Log_LINEAS_RTB );
658
659  //Matrix Log_RTB = Log( RTB + 1 );
660  //Real df::AppendVariable( Log_RTB );
661 
662  Matrix Log_PERC_COB_BA = Log( PERC_COB_BA + 1 );
663  Real df::AppendVariable( Log_PERC_COB_BA );
664
665  Matrix Log_IND_PERC_COB_BA = Log( IND_PERC_COB_BA + 1 );
666  Real df::AppendVariable( Log_IND_PERC_COB_BA ); 
667 
668  Matrix Log_ADSL_TOT = Log( ADSL_TOT + 1 );
669  Real df::AppendVariable( Log_ADSL_TOT );
670 
671  Matrix PENET_ADSL_POB = RatioPen( ADSL_TOT, POB );
672  Matrix Log_PENET_ADSL_POB = Log( PENET_ADSL_POB + 1 );
673  Real df::AppendVariable( Log_PENET_ADSL_POB ); 
674 
675  //Matrix Log_EMPS_3G = Log( EMPS_3G + 1 );
676  //Real df::AppendVariable( Log_EMPS_3G );
677
678  // Matrix Log_VIVIENDAS_TOT = Log( VIVIENDAS_TOT + 1 );
679  // Real df::AppendVariable( Log_VIVIENDAS_TOT );
680 
681  Matrix Log_POB = Log( POB + 1 );
682  Real df::AppendVariable( Log_POB );
683 
684  Matrix Log_POB_NO_COB_BA = Log( POB_NO_COB_BA + 1 );
685  Real df::AppendVariable( Log_POB_NO_COB_BA );
686
687  Matrix COB_BA_POB = RatioPen( POB - POB_NO_COB_BA, POB );
688  Matrix Log_COB_BA_POB = Log( COB_BA_POB + 1 );
689  Matrix INTER_COB = Log_COB_BA_POB $* Log_PERC_COB_3G ;
690  Real df::AppendVariable( Log_COB_BA_POB );
691  Real df::AppendVariable( INTER_COB )
692 
693  //Matrix Log_TIPO = Log( TIPO + 1 );
694  //Real df::AppendVariable( Log_TIPO )
695};
696
697Real BuildVariableOutputs( NameBlock df )
698{
699  Matrix V2G_TAR = df::GetColumn( "QT_VOL_DATOS_GPRS_TARIFA" );
700  Matrix V3G_TAR = df::GetColumn( "QT_VOL_DATOS_UMTS_TARIFA" );
701  Matrix V2G_POT_TAR = df::GetColumn( "QT_VOL_DATOS_GPRS_POT_TARIFA" );
702  Matrix V3G_POT_TAR = df::GetColumn( "QT_VOL_DATOS_UMTS_POT_TARIFA" );
703
704  Matrix DURACION_VOZ_2G_TAR = df::GetColumn( "QT_DURACION_VOZ_GPRS_TARIFA" );
705  Matrix DURACION_VOZ_3G_TAR = df::GetColumn( "QT_DURACION_VOZ_UMTS_TARIFA" );
706  Matrix DURACION_VOZ_TOT_TAR = DURACION_VOZ_2G_TAR + DURACION_VOZ_3G_TAR;
707 
708  Matrix LINEAS_VOZ_2G_TAR = df::GetColumn( "QT_LINEAS_VOZ_GPRS_TARIFA" );
709  Matrix LINEAS_VOZ_3G_TAR = df::GetColumn( "QT_LINEAS_VOZ_UMTS_TARIFA" );
710  Matrix LINEAS_VOZ_TOT_TAR = LINEAS_VOZ_2G_TAR + LINEAS_VOZ_3G_TAR;
711 
712  Matrix LINEAS_DATOS_3G_TAR = df::GetColumn( "QT_LINEAS_DATOS_UMTS_TARIFA" );
713 
714  Matrix Log_V2G_TAR = Log( V2G_TAR + 1 );
715  Real df::AppendVariable( Log_V2G_TAR );
716
717  Matrix Log_V3G_TAR = Log( V3G_TAR + 1 );
718  Real df::AppendVariable( Log_V3G_TAR );
719 
720  Matrix Log_V2G_POT_TAR = Log( V2G_POT_TAR + 1 );
721  Real df::AppendVariable( Log_V2G_POT_TAR );
722
723  Matrix Log_V3G_POT_TAR = Log( V3G_POT_TAR + 1 );
724  Real df::AppendVariable( Log_V3G_POT_TAR );
725 
726  Matrix Log_DURACION_VOZ_TOT_TAR = Log( DURACION_VOZ_TOT_TAR + 1 );
727  Real df::AppendVariable( Log_DURACION_VOZ_TOT_TAR );
728   
729  Matrix Log_LINEAS_VOZ_TOT_TAR = Log( LINEAS_VOZ_TOT_TAR + 1 );
730  Real df::AppendVariable( Log_LINEAS_VOZ_TOT_TAR );
731 
732  Matrix V3GxL3G_TAR = RatioPen( V3G_TAR, LINEAS_DATOS_3G_TAR );
733  Matrix Log_V3GxL3G_TAR = Log( V3GxL3G_TAR + 1 );
734  Real df::AppendVariable( Log_V3GxL3G_TAR );
735
736  Matrix Log_LINEAS_DATOS_3G_TAR = Log( LINEAS_DATOS_3G_TAR + 1 );
737  Real df::AppendVariable( Log_LINEAS_DATOS_3G_TAR ) 
738};
739
740Set BuildDataNucleosEstimTarifa( NameBlock args )
741{
742  NameBlock loader = LoaderDataEstimTarifa( RaDe3G.DB::rade3g, args );
743  Real rebuild = getOptArg( args, "rebuild", 1 );
744  Set data = loader::BuildDataFrame( rebuild );
745  Set If( Card( data ), {
746      NameBlock df = data[ 1 ];
747      Real df::SetObsIdColumn( "CNUC" );
748      Real BuildVariableOutputs( df );
749      Real BuildVariablePredictors( df );
750      data
751    }, data )
752};
753
754Set BuildDataNucleosForecast( NameBlock args )
755{
756  NameBlock loader = LoaderDataForecastNucleo( RaDe3G.DB::rade3g, args );
757  Real rebuild = getOptArg( args, "rebuild", 1 );
758 Set data = loader::BuildDataFrame( rebuild );
759  Set If( Card( data ), {
760      NameBlock df = data[ 1 ];
761      Real df::SetObsIdColumn( "CNUC" );
762      Real BuildVariablePredictors( df );
763      data
764    }, data )
765};
766
767Set BuildDataNucleos( NameBlock args )
768{
769  //NameBlock loader = LoaderDataNucleos( RaDe3G.DB::rade3g, args );
770  NameBlock loader = LoaderDataNucleosWeightedPobRTB( RaDe3G.DB::rade3g, args );
771  Real rebuild = getOptArg( args, "rebuild", 1 );
772  Set data = loader::BuildDataFrame( rebuild );
773  Set If( Card( data ), {
774      NameBlock df = data[ 1 ];
775      Real df::SetObsIdColumn( "CNUC" );
776      Real BuildVariablePredictors( df );
777      data
778    }, data )
779};
780
781// NameBlock args = [[ Text tarifa = "BigScreen" ]];
782// Set BuildDataNucleosTarifa( "Big Screen", 0 );