partial void Deletemid_lookup(mid_lookup instance);
 partial void Insertmid_lookup(mid_lookup instance);
 partial void Updatemid_lookup(mid_lookup instance);
Пример #4
0
        static void push_whq_data(Boolean stage = false, Boolean dev = false, int months = 5, Boolean reset = false, Boolean new_model_only = false)
        {
            var d = new WHQtoGR.App_Code.whq2grDataContext();
            GR gr = null;
            ids = new gr_ids(stage);
            string whq_system = "";
            if (stage)
            {
                gr = new GR(ConfigurationManager.AppSettings["whq_stage_api_key"], ConfigurationManager.AppSettings["gr_stage_server"], false);
               // gr.MeasApi = "http://*****:*****@"SELECT
                    CAST( DATE_FORMAT(`gma_directorreport`.`directorreport_endDate`, '%Y-%m-01') as DATE) AS `Date`,
                    `gma_locationtrans`.`location_name` AS `Area`,
                    `gma_organizationdata`.`organization_countryCode` AS `Country`,
                    `gma_measurementlbl`.`measurementlbl_name` AS `LMI`,
                    `gma_strategylbl`.`strategylbl_name` AS `MCC`,
                    SUM( `gma_directordata`.`directordata_value` ) AS `Value`
                FROM
                    `gma_directordata`
                    JOIN `gma_directorreport` ON `gma_directordata`.`directorreport_id` = `gma_directorreport`.`directorreport_id`
                    JOIN `gma_measurement` ON `gma_directordata`.`measurement_id` = `gma_measurement`.`measurement_id`
                    JOIN `gma_newmeasurementdetails` ON `gma_measurement`.`measurement_details_id` = `gma_newmeasurementdetails`.`measurement_details_id`
                    JOIN `gma_measurementlbl` ON `gma_measurement`.`measurementlbl_id` = `gma_measurementlbl`.`measurementlbl_id`
                    JOIN `gma_organizationdata` ON `gma_measurement`.`organization_id` = `gma_organizationdata`.`organization_id`
                    JOIN `gma_strategydata` ON `gma_newmeasurementdetails`.`strategy_id` = `gma_strategydata`.`strategy_id`
                    JOIN `gma_strategylbl` ON `gma_strategydata`.`strategylbl_id` = `gma_strategylbl`.`strategylbl_id`
                    JOIN `gma_locationtrans` ON `gma_organizationdata`.`location_id` = `gma_locationtrans`.`location_id`
                WHERE
                    `gma_organizationdata`.`version_id` = 2
                    AND `gma_strategydata`.`version_id` = 2
                    AND `gma_locationtrans`.`language_id` = 1
                    AND `gma_organizationdata`.`organization_leftIndex` + 1 = `gma_organizationdata`.`organization_rightIndex`
                    AND MONTH(`gma_directorreport`.`directorreport_endDate`)= MONTH(CURDATE() - INTERVAL " + i + @" MONTH )
            AND YEAR(`gma_directorreport`.`directorreport_endDate`)= YEAR(CURDATE() - INTERVAL " + i + @" MONTH )
            and Not `gma_locationtrans`.`location_name`  = 'NONE' and Not `gma_locationtrans`.`location_name`  = 'TEST'
            And `gma_directordata`.`directordata_value`  >0
                GROUP BY
                    `Date`,
                    `Area`,
                    `Country`,
                    `LMI`,
                    `MCC`";

                    var dt = new DataTable();
                    var da = new MySqlDataAdapter(cmd);
                    da.Fill(dt);

                    //get all measurements

                   // Console.WriteLine(dt.Rows.Count);

                    var rows = dt.AsEnumerable().Where(row => (d.wierd_countries.Where(c => c.whq_code == row["Country"].ToString() && c.min_code == null).Count() == 0) && ids.m.ContainsKey(row["LMI"].ToString()) && (d.mid_lookups.Where(c => (c.country == row["Country"].ToString()) && c.etl && (c.stage == stage)).Count() == 0 || (row["LMI"].ToString().Contains("actualreportingnodes") || row["LMI"].ToString().Contains("actualreportingnodes")))).ToArray();
                    //Console.WriteLine("before: " + rows.Where(row => row["Country"].ToString() == "EGY").Count().ToString());

                    //filter out ETL Active rows and only

                    //now some tests

                    int delta = rows.Count() / 10;
                    int count = 0;
                    var flat_list = new Dictionary<string, double>();

                    var flat_list_mt = new Dictionary<string, string>(); //The flatlist contains a name/value list of all measurements (from whq), where the name contains  ministry_id : measurement_type_id : dimension. This allows me to lookup a value and see if it has changed.
                    if (rows.Count() > 0)
                    {
                        var everything = gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_win_%25" +  m_filter).ToList();

                        everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_build_%25" + m_filter).ToList());
                        everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_send_%25" + m_filter).ToList());
                        everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_movements_%25" + m_filter).ToList());
                        everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_nbr_%25" + m_filter).ToList());
                        everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_actual_reporting_nodes" + m_filter).ToList());
                        everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_possible_reporting_nodes" + m_filter).ToList());

                        foreach (MeasurementType row in everything)
                        {
                            foreach (Measurement m in row.measurements)
                            {
                                flat_list.Add(m.RelatedEntityId + ":" + row.ID + ":" + m.Dimension, m.Value);
                                flat_list_mt.Add(m.RelatedEntityId + ":" + row.ID + ":" + m.Dimension, m.ID ) ;
                            }
                        }

                    }
                   //  Console.WriteLine("rows: " + rows.Count().ToString());
                   // Console.WriteLine("flatlist count: " + flat_list.Count);
                    int c_changed = 0;
                    int c_not_changed = 0;

                    Console.Write("processing data:");
                    foreach (DataRow row in rows)
                    {

                        count++;
                        if (delta > 0)
                        {
                            if (count % delta == 1)
                                Console.Write("\rprocessing data for " + ((DateTime)row["Date"]).ToString("yyyy-MM") + ": " + ((int)(100 * count / rows.Count())).ToString() + "% complete.");

                        }

                            string min_id = (from c in d.mid_lookups where c.country == row["Country"].ToString() && c.stage == stage select c.ministry_id).FirstOrDefault();

                            if (string.IsNullOrEmpty(min_id))
                            {
                                var nms_wierd = (from c in d.wierd_countries where c.whq_code == row["Country"].ToString() select c.min_code);
                                Entity nms_ministry = null;

                                if (nms_wierd.Count() > 0 )
                                {
                                    nms_ministry = gr.GetEntities("ministry", "&filters[owned_by]=all&filters[min_code]=" + nms_wierd.First() + "&fields=min_code", 0, 0, ref totalPage).FirstOrDefault();

                                }
                                if (nms_ministry == null)
                                {
                                    nms_ministry = gr.GetEntities("ministry", "&filters[owned_by]=all&filters[iso_country:relationship][iso3_code]=" + row["Country"].ToString() + "&filters[area:relationship][area_code]=" + ids.areas[row["Area"].ToString()] + "&fields=min_code", 0, 0, ref totalPage).FirstOrDefault();

                                }
                                min_id = nms_ministry.ID;
                                App_Code.mid_lookup insert = new App_Code.mid_lookup();
                                insert.ministry_id = min_id;
                                insert.stage = stage;
                                insert.country = row["Country"].ToString();
                                d.mid_lookups.InsertOnSubmit(insert);

                                d.SubmitChanges();
                            }

                            Boolean changed=true;
                            string perm_link = row["LMI"].ToString().ToLower().Replace("fa_", "lmi_local_").Replace("fr_", "lmi_local_").Replace("movements", "lmi_local_movements").Replace("actualreportingnodes", "lmi_local_actual_reporting_nodes").Replace("possiblereportingnodes", "lmi_local_possible_reporting_nodes").Replace("nbr_staff_reporting", "lmi_local_nbr_staff_reporting").Replace("nbr_nonstaff_reporting", "lmi_local_nbr_nonstaff_reporting");
                            if (perm_link.StartsWith("lmi_local"))
                            {

                                MeasurementType this_mt = ids.nms.Where(c => c.PermLink == perm_link).FirstOrDefault();

                                    if (this_mt == null)
                                    {
                                        //get the measurement
                                        // var cache = new WHQtoGR.App_Code.meas_cacheDataContext();

                                        this_mt = gr.GetMeasurements("", "3000-01", "3000-01", "", "", false, "&filters[perm_link]=" + perm_link).FirstOrDefault();
                                        if (this_mt != null)
                                        {
                                            //  Console.WriteLine(perm_link);
                                            ids.nms.Add(this_mt);
                                        }

                                    }
                                    if (this_mt != null)
                                    {
                                        string key = min_id + ":" + this_mt.ID + ":" + ids.mcc_code[row["MCC"].ToString()] + "_gma" ;
                                        if (flat_list.ContainsKey(key ))
                                        {
                                            changed = (flat_list[key] != System.Convert.ToDouble(row["Value"]));
                                            flat_list.Remove(key);
                                            flat_list_mt.Remove(key);  //the remaining items need to be deleted, as they exist in GR but not in WHQ
                                        }

                                        if (changed)
                                        {
                                            this_mt.addMeasurement(min_id, ((DateTime)row["Date"]).ToString("yyyy-MM"), System.Convert.ToDouble(row["Value"]), ids.mcc_code[row["MCC"].ToString()] + "_gma");
                                            c_changed++;
                                        }
                                        else c_not_changed++;
                                        //Console.WriteLine(row["Country"].ToString() + " " + row["MCC"].ToString().ToLower() + " " + perm_link + " : " + System.Convert.ToDouble(row["Value"]));

                                    }
                                    else
                                    {
                                        Console.WriteLine("could not create measurement_type");
                                    }
                            }
                            else
                                Console.WriteLine("could not find " + perm_link);

                    }
                    //Console.WriteLine("changed: " + c_changed);
                    //Console.WriteLine("not_changed: " + c_not_changed);

                    //Console.WriteLine("updloading " + ids.nms.Sum(c => c.measurements.Count()) + " into New Msasurement Model");

                   //TODO: DELETE ALL MEAUREMENTS THAT ARE LEFT IN THE FLATLIST
                    Console.WriteLine("extra (to be delete): " + flat_list_mt.Count());
                   foreach(var m in flat_list_mt)
                   {
                       gr.DeleteMeasuerment(m.Value);
                      //need to get measurement_id into flatlist key
                   }

                    da.Dispose();
                    dt.Dispose();
                }
                //dt.Dump();
            }

            Console.WriteLine("Finished generating measurmenets");

            Console.WriteLine("updloading " + ids.nms.Sum(c => c.measurements.Count()) + " into New Msasurement Model");
            var tasks = new List<Task>();
            foreach (MeasurementType mt in ids.nms)
            {

                tasks.Add(System.Threading.Tasks.Task.Factory.StartNew(() =>
                {

                    gr.AddUpdateMeasurement(mt, true, 100);
                }));

            }

            System.Threading.Tasks.Task.WaitAll(tasks.ToArray<Task>());

            Console.WriteLine("Finished ");
            // Console.ReadKey();
        }