Пример #1
0
        public static void deleteMainTbl2(BusLicense busLic)
        {
            string connStr   = ConfigurationManager.ConnectionStrings["IMSReader"].ConnectionString;
            string deleteQry = "DELETE FROM [LRW_V8].[LR_BusinessLicenses] WHERE license_number = @license_number AND license_type = @license_type AND license_classification = @license_classification " +
                               "AND license_category = @license_category AND contact_name = @contact_name AND account_start_date = @account_start_date AND street_address = @street_address " +
                               "AND city = @city AND state = @state AND zip_code = @zip_code";

            using (var conn = new SqlConnection(connStr))
            {
                using (SqlCommand command = new SqlCommand(deleteQry, conn))
                {
                    command.Parameters.AddWithValue("@license_number", busLic.license_number);
                    command.Parameters.AddWithValue("@license_type", busLic.license_type);
                    command.Parameters.AddWithValue("@license_classification", busLic.license_classification);
                    command.Parameters.AddWithValue("@license_category", busLic.license_category);
                    command.Parameters.AddWithValue("@contact_name", busLic.contact_name);
                    command.Parameters.AddWithValue("@account_start_date", busLic.account_start_date);
                    command.Parameters.AddWithValue("@street_address", busLic.street_address);
                    command.Parameters.AddWithValue("@city", busLic.city);
                    command.Parameters.AddWithValue("@state", busLic.state);
                    command.Parameters.AddWithValue("@zip_code", busLic.zip_code);

                    conn.Open();
                    int result = command.ExecuteNonQuery();
                    if (result < 1)
                    {
                        Debug.WriteLine("No data inserted");
                    }
                    conn.Close();
                }
            }
        }
Пример #2
0
        public static void InsertChangesTbl(BusLicense busLic, string action)
        {
            string connStr = ConfigurationManager.ConnectionStrings["IMSReader"].ConnectionString;

            string insertChangesQry = "INSERT INTO [LRW_V8].[LR_BusinessLicenses_Changes] (RUNDATE, ACTION, license_number, license_type, license_classification, license_category, business_name, contact_name, account_start_date, street_address, city, state, zip_code) " +
                                      "VALUES (@RUNDATE, @ACTION, @license_number, @license_type, @license_classification, @license_category, @business_name, @contact_name, @account_start_date, @street_address, @city, @state, @zip_code)";

            using (var conn = new SqlConnection(connStr))
            {
                using (SqlCommand command2 = new SqlCommand(insertChangesQry, conn))
                {
                    command2.Parameters.AddWithValue("@RUNDATE", DateTime.Now);
                    command2.Parameters.AddWithValue("@ACTION", action);
                    command2.Parameters.AddWithValue("@license_number", (string)busLic.license_number);
                    command2.Parameters.AddWithValue("@license_type", (string)busLic.license_type);
                    command2.Parameters.AddWithValue("@license_classification", (string)busLic.license_classification);
                    command2.Parameters.AddWithValue("@license_category", (string)busLic.license_category);
                    command2.Parameters.AddWithValue("@business_name", (string)busLic.business_name);
                    command2.Parameters.AddWithValue("@contact_name", (string)busLic.contact_name);
                    command2.Parameters.AddWithValue("@account_start_date", busLic.account_start_date);
                    command2.Parameters.AddWithValue("@street_address", (string)busLic.street_address);
                    command2.Parameters.AddWithValue("@city", (string)busLic.city);
                    command2.Parameters.AddWithValue("@state", (string)busLic.state);
                    command2.Parameters.AddWithValue("@zip_code", (string)busLic.zip_code);
                    //command2.Parameters.AddWithValue("@x_coord", Convert.ToDouble(busLic.geocoded_column.longitude));
                    //command2.Parameters.AddWithValue("@y_coord", Convert.ToDouble(busLic.geocoded_column.latitude));

                    conn.Open();
                    int result = command2.ExecuteNonQuery();
                    if (result < 1)
                    {
                        Debug.WriteLine("No data inserted");
                    }
                    conn.Close();
                }
            }
        }
Пример #3
0
        static void Main(string[] args)
        {
            /*
             *  First create a dictionary using business license number as keys and List<BusLicense> as values.  Use the city of LR REST service to build this.
             *  Loop thru each business object key in the dictionary:
             *          query records for that license number in main table of database (this I call a rec set or "recs")
             *      If there is one business obj and <2 recs for that license number
             *        Check if there are any recs for that busness license number,
             *          if not add to main tbl and changes tbl as ADD
             *          if there is one, check if the records all match
             *            If not, update rec in main table and add 2 recs to changes table as UPDATEOLD and UPDATENEW
             *      If there are >=2 business objs OR >=2 recs
             *        Loop through each business object(s) and each rec for given license number and compare every field.
             *          If match is found for given business object, then skip
             *          If no match is found for given business object, add that business object to main table and to changes table (as ADD).
             *
             *    Loop through each rec in record set - this part is to determine the recs to delete.
             *      See if there is a key in the dictionary with the license number in the record set.
             *        If there is no key, the license number must have been dropped.  Delete this record from main db table and add to changes tbl as DELETE.
             *        If there is a key, loop through the business object(s) in list and compare all fields.
             *          If there is no match, delete this record from main db table and add to changes tbl as DELETE.
             *
             *  The Program2 Main method is for creating a new LR_BusinessLicenses table.  This should only be done once except for during testing phase.
             *  To toggle between the programs, go to Properties --> Application tab --> startup object
             */

            string connStr = ConfigurationManager.ConnectionStrings["IMSReader"].ConnectionString;

            // Get the current business license data from the LR city website as list of business license objects.

            // Had to add this because it was not working on the server.
            System.Net.ServicePointManager.Expect100Continue = false;
            System.Net.ServicePointManager.SecurityProtocol  = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

            //HttpWebRequest WebReq = (HttpWebRequest)WebRequest.Create(string.Format("https://data.littlerock.gov/resource/vthq-dt7e.json?license_type=RESTAURANT"));
            HttpWebRequest WebReq = (HttpWebRequest)WebRequest.Create(string.Format("https://data.littlerock.gov/resource/vthq-dt7e.json?$limit=50000"));

            WebReq.Method = "GET";

            HttpWebResponse WebResp = (HttpWebResponse)WebReq.GetResponse();

            int     busLicCount     = 0;
            int     busLicDupCount  = 0;
            int     changedRecCount = 0;
            int     addCount        = 0;
            int     deletedRecs     = 0;
            Boolean matchFound      = false;

            string jsonString;

            using (Stream stream = WebResp.GetResponseStream())
            {
                // Create a list of business objects from the LR city REST service.

                StreamReader reader = new StreamReader(stream, System.Text.Encoding.UTF8);
                jsonString = reader.ReadToEnd();

                List <BusLicense> busLicenses = JsonConvert.DeserializeObject <List <BusLicense> >(jsonString);
                System.Diagnostics.Debug.WriteLine("busLicenses.Count= " + busLicenses.Count.ToString());

                if (busLicenses.Count < 1)
                {
                    log.Debug("no business license objects returned from LR REST");
                    return;
                }
                log.Debug("Total number of business licenses on LR site = " + busLicenses.Count);


                // Create a dictionary from list of BusLicense objects:  license number --> list of busLicense objects
                // Note: have to do this because there are duplicate records in LR City data with same business license number.
                // Only add the license numbers that start with BL

                Dictionary <string, List <BusLicense> > busLicenseDict = new Dictionary <string, List <BusLicense> >();
                foreach (var itm in busLicenses)
                {
                    busLicCount = busLicCount + 1;
                    if (itm.license_number.ToString().Substring(0, 2) == "BL")
                    {
                        if (busLicenseDict.ContainsKey(itm.license_number))
                        {
                            busLicDupCount = busLicDupCount + 1;
                            busLicenseDict[itm.license_number].Add(itm);
                        }
                        else
                        {
                            busLicenseDict.Add(itm.license_number, new List <BusLicense>());
                            busLicenseDict[itm.license_number].Add(itm);
                        }
                    }
                }
                log.Debug("Total business licenses with BL prefix = " + busLicenseDict.Count().ToString());


                // Loop through the business license objects in the dictionary and use the license number to query the db table.
                // If there is only one business object in the list and one or less recs, deal with these first.

                foreach (var dictEntry in busLicenseDict)
                {
                    string mainTblQry = "SELECT [license_number], [license_type], [license_classification], [license_category], [business_name], [contact_name], " +
                                        "[account_start_date], [street_address], [city], [state], [zip_code], [x_coord], [y_coord] " +
                                        "FROM [V8].[LRW_V8].[LR_BusinessLicenses] " +
                                        "WHERE license_number = '" + dictEntry.Key + "'";
                    BusinessLics_console.DataAccess da1 = new BusinessLics_console.DataAccess();
                    DataSet ds2 = da1.GetData(mainTblQry);

                    List <BusLicense> busLicenseList = busLicenseDict[dictEntry.Key];

                    if (busLicenseList.Count == 1 && ds2.Tables[0].Rows.Count < 2)
                    {
                        if (ds2.Tables[0].Rows.Count < 1)
                        {
                            // There is no rec in main table, so add business license object to records in main and changes tables.

                            addCount = addCount + 1;
                            InsertMainTbl(busLicenseList[0]);
                            InsertChangesTbl(busLicenseList[0], "ADD");
                        }
                        else
                        {
                            DataRow dr = ds2.Tables[0].Rows[0];

                            if (busLicenseList[0].license_type == (string)dr["license_type"] &&
                                busLicenseList[0].license_classification == (string)dr["license_classification"] &&
                                busLicenseList[0].license_category == (string)dr["license_category"] &&
                                busLicenseList[0].business_name == (string)dr["business_name"] &&
                                busLicenseList[0].contact_name == (string)dr["contact_name"] &&
                                busLicenseList[0].account_start_date == (DateTime)dr["account_start_date"] &&
                                busLicenseList[0].street_address == (string)dr["street_address"] &&
                                busLicenseList[0].city == (string)dr["city"] &&
                                busLicenseList[0].state == (string)dr["state"] &&
                                busLicenseList[0].zip_code == (string)dr["zip_code"])
                            {
                                // Do nothing because there is a match.
                            }
                            else
                            {
                                // One or more field do not match, and there is only one business object and only one rec so must be an update.
                                changedRecCount = changedRecCount + 1;

                                deleteMainTbl(dictEntry.Key);
                                InsertMainTbl(busLicenseList[0]);
                                InsertChangesTbl(busLicenseList[0], "UPDATENEW");

                                BusLicense blTemp = new BusLicense();
                                blTemp.license_number         = (string)dr["license_number"];
                                blTemp.license_type           = (string)dr["license_type"];
                                blTemp.license_classification = (string)dr["license_classification"];
                                blTemp.license_category       = (string)dr["license_category"];
                                blTemp.business_name          = (string)dr["business_name"];
                                blTemp.contact_name           = (string)dr["contact_name"];
                                blTemp.account_start_date     = (DateTime)dr["account_start_date"];
                                blTemp.street_address         = (string)dr["street_address"];
                                blTemp.city     = (string)dr["city"];
                                blTemp.state    = (string)dr["state"];
                                blTemp.zip_code = (string)dr["zip_code"];
                                InsertChangesTbl(blTemp, "UPDATEOLD");
                                blTemp = null;
                            }
                        }
                    }
                    else // There is either >1 business objects or >1 recs in main table.
                    {
                        foreach (var listEntry in dictEntry.Value)
                        {
                            BusLicense bl = new BusLicense();
                            matchFound = false;
                            foreach (DataRow dr in ds2.Tables[0].Rows)
                            {
                                bl.license_number         = (string)dr["license_number"];
                                bl.license_type           = (string)dr["license_type"];
                                bl.license_classification = (string)dr["license_classification"];
                                bl.license_category       = (string)dr["license_category"];
                                bl.business_name          = (string)dr["business_name"];
                                bl.contact_name           = (string)dr["contact_name"];
                                bl.account_start_date     = (DateTime)dr["account_start_date"];
                                bl.street_address         = (string)dr["street_address"];
                                bl.city     = (string)dr["city"];
                                bl.state    = (string)dr["state"];
                                bl.zip_code = (string)dr["zip_code"];

                                if (bl.license_type == listEntry.license_type &&
                                    bl.license_classification == listEntry.license_classification &&
                                    bl.license_category == listEntry.license_category &&
                                    bl.business_name == listEntry.business_name &&
                                    bl.account_start_date == listEntry.account_start_date &&
                                    bl.contact_name == listEntry.contact_name &&
                                    bl.street_address == listEntry.street_address &&
                                    bl.city == listEntry.city &&
                                    bl.state == listEntry.state &&
                                    bl.zip_code == listEntry.zip_code)
                                {
                                    // There is a match, so flip the switch
                                    matchFound = true;
                                }
                                else
                                {
                                    // This was not a match so continue checking for matches
                                }
                            }
                            if (matchFound == false) // No matching recs found for this business object, so add it to main as ADD
                            {
                                InsertMainTbl(listEntry);
                                InsertChangesTbl(listEntry, "ADD");
                            }
                            bl = null;
                        }
                    }
                } // Ends foreach loop for business object dictionary


                // Loop through the main table in database to see what recs should be deleted.  If a rec in main table does not have matching license number
                // in the dictionary, then delete it from main table and add a rec to changes table as DELETE.

                string allRecsQry = "SELECT [license_number], [license_type], [license_classification], [license_category], [business_name], [contact_name], " +
                                    "[account_start_date], [street_address], [city], [state], [zip_code], [x_coord], [y_coord] " +
                                    "FROM [V8].[LRW_V8].[LR_BusinessLicenses] ";
                BusinessLics_console.DataAccess daAllRecs = new BusinessLics_console.DataAccess();
                DataSet dsAllRecs = daAllRecs.GetData(allRecsQry);

                Boolean    keyExists   = true;
                Boolean    matchExists = false;
                BusLicense bl_rec      = new BusLicense();

                foreach (DataRow dr in dsAllRecs.Tables[0].Rows)
                {
                    bl_rec.license_number         = (string)dr["license_number"];
                    bl_rec.license_type           = (string)dr["license_type"];
                    bl_rec.license_classification = (string)dr["license_classification"];
                    bl_rec.license_category       = (string)dr["license_category"];
                    bl_rec.business_name          = (string)dr["business_name"];
                    bl_rec.contact_name           = (string)dr["contact_name"];
                    bl_rec.account_start_date     = (DateTime)dr["account_start_date"];
                    bl_rec.street_address         = (string)dr["street_address"];
                    bl_rec.city     = (string)dr["city"];
                    bl_rec.state    = (string)dr["state"];
                    bl_rec.zip_code = (string)dr["zip_code"];

                    keyExists = busLicenseDict.ContainsKey((string)dr["license_number"]);
                    if (keyExists == false)
                    {
                        InsertChangesTbl(bl_rec, "DELETE");
                        deleteMainTbl((string)dr["license_number"]);
                    }
                    else
                    {
                        matchExists = false;
                        foreach (BusLicense blo in busLicenseDict[(string)dr["license_number"]])
                        {
                            if (blo.license_type == bl_rec.license_type &&
                                blo.license_classification == bl_rec.license_classification &&
                                blo.license_category == bl_rec.license_category &&
                                blo.business_name == bl_rec.business_name &&
                                blo.contact_name == bl_rec.contact_name &&
                                blo.account_start_date == bl_rec.account_start_date &&
                                blo.street_address == bl_rec.street_address &&
                                blo.city == bl_rec.city &&
                                blo.state == bl_rec.state &&
                                blo.zip_code == bl_rec.zip_code)
                            {
                                matchExists = true;
                            }
                        }
                        if (matchExists == false)
                        {
                            deletedRecs = deletedRecs + 1;
                            InsertChangesTbl(bl_rec, "DELETE");
                            deleteMainTbl2(bl_rec);
                        }
                    }
                }
            }

            log.Debug("Number of business licenses with duplicate entries = " + busLicDupCount.ToString());
            log.Debug("Number of records added = " + addCount.ToString());
            log.Debug("Number of updated records = " + changedRecCount.ToString());
            log.Debug("Number of deleted records = " + deletedRecs.ToString());
            log.Debug("program complete");
        }