private void FullImport(object sql)
        {
            data_mapping_schema dms = data_mapping_schema.GetDefaultSchema;
            OleDbConnection     oConnect;
            DataTable           resultData = new DataTable();
            OleDbDataAdapter    oAdapter;

            try
            {
                oConnect = new OleDbConnection(dms.GetConnectionString(true));
                oConnect.Open();
            }
            catch (Exception err)
            {
                LoggingHelper.Log("Could not connect to DB in frmClaimSendHistory_Load", LogSeverity.Error, err, false);
                ImportTouchup();
                return;
            }



            oAdapter = new OleDbDataAdapter(sql.ToString(), oConnect);
            oAdapter.Fill(resultData);

            foreach (DataRow aRow in resultData.Rows)
            {
                CheckCreateEligibility(aRow);
            }

            system_options.LastEligibilityDate = DateTime.Now;

            ImportTouchup();
        }
        private void frmClaimSendHistory_Load(object sender, EventArgs e)
        {
            data_mapping_schema dms = data_mapping_schema.GetDefaultSchema;

            dtpStart.Value = DateTime.Now.Subtract(new TimeSpan(365, 0, 0, 0));
            dtpEnd.Value   = DateTime.Now;

            try
            {
                oConnect = new OleDbConnection(dms.GetConnectionString(true));
                oConnect.Open();
            }
            catch (Exception err)
            {
                LoggingHelper.Log("Could not connect to DB in frmClaimSendHistory_Load", LogSeverity.Error, err, false);
                MessageBox.Show(this, "A connection to the database could not be established.");
                return;
            }
        }
        public static void GoImport()
        {
            claim     workingClaim                  = new claim();
            DataTable importData                    = new DataTable();
            DataTable importDataSecondaries         = new DataTable();
            DataTable importDataPredeterms          = new DataTable();
            DataTable importDataSecondaryPredeterms = new DataTable();

            system_options.SetImportFlag(true);
            primaryClaimCount            = 0;
            secondaryClaimCount          = 0;
            predetermClaimCount          = 0;
            secondaryPredetermClaimCount = 0;
            closedClaimCount             = 0;

            OleDbConnection oConnect;

            //UpdateProgressBar(50, "Initiating Remote Connection...");


            #region Initiate Connection, Get Data
            try
            {
                oConnect = new OleDbConnection(dms.GetConnectionString(true));
            }
            catch (Exception err)
            {
                CreateLogFile(err.ToString());
                Updateimporterror(true);
                DeleteDirectoryFromTemp();
                //LoggingHelper.Log("An error occurred getting the connection string for a new connection in frmImportData.Import", LogSeverity.Error, err, false);
                //e.Cancel = true;
                CancelImport();
                return;
            }



            OleDbDataAdapter oAdapter;
            // Use Connection object for the DataAdapter to retrieve all tables from selected Database
            try
            {
                oConnect.Open();
            }
            catch (Exception err)
            {
                CreateLogFile(err.ToString());
                Updateimporterror(true);
                DeleteDirectoryFromTemp();
                //LoggingHelper.Log("Could not connect to the database in frmImportdata.Import", LogSeverity.Error, err, false);
                //e.Cancel = true;
                CancelImport();
                return;
            }



            try
            {
                //UpdateProgressBar(50, "Querying remote database (Standard)...");

                // ************* Standard Claims
                oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatement, changesOnly), oConnect);
                oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(dataTimeout);
                oAdapter.Fill(importData);

                //UpdateProgressBar(10, "Querying remote database (Secondary)...");

                // **************  Secondaries
                if (dms.sqlstatementsecondaries != "")
                {
                    oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatementsecondaries, changesOnly), oConnect);
                    oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(dataTimeout);
                    oAdapter.Fill(importDataSecondaries);
                }

                //UpdateProgressBar(10, "Querying remote database (Predeterms)...");

                // *************** Predeterms
                if (dms.sqlstatementpredeterms != "")
                {
                    oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatementpredeterms, changesOnly), oConnect);
                    oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(dataTimeout);
                    oAdapter.Fill(importDataPredeterms);
                }

                //UpdateProgressBar(10, "Querying remote database (Secondary Predeterms)...");
                // *************** Predeterms
                if (dms.sqlstatementsecondarypredeterms != "")
                {
                    oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatementsecondarypredeterms, changesOnly), oConnect);
                    oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(dataTimeout);
                    oAdapter.Fill(importDataSecondaryPredeterms);
                }
            }
            catch (Exception err)
            {
                CreateLogFile(err.ToString());
                Updateimporterror(true);
                DeleteDirectoryFromTemp();
                //LoggingHelper.Log("Error with SQL statement or connection in frmImportData.Import", LogSeverity.Error, err);
                //MessageBox.Show(this, "There was an error with your SQL statement or with your connection.\n\n" + err.Message,
                //   "Error retrieving data");
                CancelImport();
                return;
            }

            #endregion

            data_mapping_schema_data dmsd = new data_mapping_schema_data();
            dmsd.schema_id = dms.id;
            DataTable dataForSchema = dmsd.Search();

            // Generate our list of objects one time, and then use them for each iteration of rows
            List <data_mapping_schema_data> allMappedSchemaData = new List <data_mapping_schema_data>();
            foreach (DataRow aMapping in dataForSchema.Rows)
            {
                // For every row, need to get the data for every field
                dmsd = new data_mapping_schema_data();
                dmsd.Load(aMapping);
                allMappedSchemaData.Add(dmsd);
            }

            // UpdateProgressBar(100, "Importing data...");

            if (okToZap)
            {
                company cmp = new company();
                cmp.Zap();

                workingClaim.Zap();

                call aCall = new call();
                aCall.Zap();

                company_contact_info info = new company_contact_info();
                info.Zap();

                procedure p = new procedure();
                p.Zap();

                choice c = new choice();
                c.Zap();

                notes n = new notes();
                n.Zap();

                claim_batch cb = new claim_batch();
                cb.Zap();

                batch_claim_list bcl = new batch_claim_list();
                bcl.Zap();
            }
            else
            {
                if (!changesOnly)
                {
                    workingClaim.MarkAllImportsUpdated(false);
                }
            }

            // Apply incremental updates to progress bar
            int currentRow = 0;

            totalRows = importData.Rows.Count + importDataSecondaries.Rows.Count + importDataPredeterms.Rows.Count + importDataSecondaryPredeterms.Rows.Count;
            decimal exactIncrementAmount;

            if (totalRows > 0)
            {
                exactIncrementAmount = 500m / totalRows;
            }
            else
            {
                exactIncrementAmount = 500m;
            }

            decimal incrementCounter = 0;

            int increment;

            if (exactIncrementAmount < 1)
            {
                increment = 1;
            }
            else
            {
                increment = Convert.ToInt32(Math.Truncate(exactIncrementAmount));
            }



            string  lastClaimID             = "";
            claim   aClaim                  = new claim();
            company aCompany                = new company();
            company_contact_info anInfo     = new company_contact_info();
            procedure            aProcedure = new procedure();



            for (int p = 0; p < 4; p++)
            {
                claim.ClaimTypes ct;
                DataTable        thisImport;
                switch (p)
                {
                case 0:
                    thisImport = importData;
                    ct         = claim.ClaimTypes.Primary;
                    //UpdateLabels(0);
                    break;

                case 1:
                    thisImport = importDataSecondaries;
                    ct         = claim.ClaimTypes.Secondary;
                    //UpdateLabels(1);
                    break;

                case 2:
                    thisImport = importDataPredeterms;
                    ct         = claim.ClaimTypes.Predeterm;
                    //UpdateLabels(2);
                    break;

                default:
                    thisImport = importDataSecondaryPredeterms;
                    //UpdateLabels(3);
                    ct = claim.ClaimTypes.SecondaryPredeterm;
                    break;
                }



                // Have data at this point, need to tie them to the internal mapping schema data
                foreach (DataRow anImportRow in thisImport.Rows)
                {
                    string newID = anImportRow[dms.claim_id_column].ToString();
                    string newDB = anImportRow[dms.claim_db_column].ToString();
                    bool   isOnlyProcedureData;

                    if (newID == lastClaimID)
                    {
                        // We're only dealing with the import of "some" data
                        isOnlyProcedureData = true;
                    }
                    else
                    {
                        if (ct == claim.ClaimTypes.Primary)
                        {
                            primaryClaimCount++;
                        }
                        else if (ct == claim.ClaimTypes.Secondary)
                        {
                            secondaryClaimCount++;
                        }
                        else if (ct == claim.ClaimTypes.Predeterm)
                        {
                            predetermClaimCount++;
                        }
                        else
                        {
                            secondaryPredetermClaimCount++;
                        }

                        //UpdateTypeCount();

                        aClaim      = FindClaim(anImportRow, ct);
                        aCompany    = FindCompany(anImportRow[dms.company_namecolumn].ToString());
                        anInfo      = FindContactInfo(anImportRow["Ins_Co_Street1"].ToString(), aCompany.id, anImportRow["Ins_Co_Phone"].ToString());
                        lastClaimID = newID;
                        aClaim.ClearClaimProcedures();
                        isOnlyProcedureData = false;

                        // Check for "X" in provider field
                        try
                        {
                            if (aClaim.doctor_provider_id.StartsWith("X"))
                            {
                                AddStatus(string.Format("The claim for patient {0} on {1} uses an X provider ({2})", aClaim.PatientName, aClaim.DatesOfServiceString(), aClaim.doctor_provider_id), true, true);
                            }
                        }
                        catch (Exception err)
                        {
                            CreateLogFile(err.ToString());
                            Updateimporterror(true);
                            DeleteDirectoryFromTemp();
                            //LoggingHelper.Log(err, false);
                        }
                    }

                    aProcedure = FindProcedure(anImportRow["PROC_LOGID"].ToString());

                    if (CommonFunctions.DBNullToString(anImportRow["DATERECEIVED"]) == "")
                    {
                        aClaim.open = 1;
                    }
                    else if (((DateTime)anImportRow["DATERECEIVED"]).Year == 1753)
                    {
                        aClaim.open = 1;
                    }
                    else
                    {
                        aClaim.open = 0;
                        UpdateStatusHistory(aClaim);
                    }


                    foreach (data_mapping_schema_data aMappedData in allMappedSchemaData)
                    {
                        // We do a check for is only procedure data to speed up processing
                        // It makes the code a little messier.

                        if (isOnlyProcedureData)
                        {
                            // If we're only importing the procedure data, none of the other information is important

                            if ((aMappedData.LinkedField.table_name == "claims") ||
                                (aMappedData.LinkedField.table_name == "companies") ||
                                (aMappedData.LinkedField.table_name == "company_contact_info"))
                            {
                                // Ignore
                            }
                            else if (aMappedData.LinkedField.table_name == "procedures")
                            {
                                if (aMappedData.LinkedField.field_name == "surf_string")
                                {
                                    aProcedure[aMappedData.LinkedField.field_name] = CommonFunctions.RemoveNonPrintableCharacters(anImportRow[aMappedData.mapped_to_text].ToString());
                                }
                                else if (aMappedData.LinkedField.field_name == "claim_id")
                                {
                                    aProcedure["claim_id"] = lastClaimID;
                                }
                                else
                                {
                                    aProcedure[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text];
                                }
                            }
                            else
                            {
                                //LoggingHelper.Log("Uninitialized table name in frmImportData.Import", LogSeverity.Critical,
                                //    new Exception("Uninitialized table name in import procedure."), true);
                            }
                        }
                        else
                        {
                            // This is a new claim - we need to get the data for every field
                            if (aMappedData.LinkedField.table_name == "claims")
                            {
                                aClaim[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text];
                            }
                            else if (aMappedData.LinkedField.table_name == "companies")
                            {
                                if (aMappedData.mapped_to_text != dms.company_namecolumn)
                                {
                                    aCompany[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text];
                                }
                            }
                            else if (aMappedData.LinkedField.table_name == "company_contact_info")
                            {
                                anInfo[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text];
                            }
                            else if (aMappedData.LinkedField.table_name == "procedures")
                            {
                                if (aMappedData.LinkedField.field_name == "surf_string")
                                {
                                    aProcedure[aMappedData.LinkedField.field_name] = CommonFunctions.RemoveNonPrintableCharacters(anImportRow[aMappedData.mapped_to_text].ToString());
                                }
                                else
                                {
                                    aProcedure[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text];
                                }
                            }
                            else
                            {
                                //LoggingHelper.Log("Uninitialized table name in frmImport.Import", LogSeverity.Critical);
                                //throw new Exception("Uninitialized table name in import procedure.");
                            }
                        }
                    }

                    aCompany.Save();



                    anInfo.company_id = aCompany.id;
                    if (CommonFunctions.DBNullToZero(anInfo["order_id"]) == 0)
                    {
                        anInfo.order_id = anInfo.GetNextOrderID();
                    }
                    anInfo.Save();


                    aClaim.company_id            = aCompany.id;
                    aClaim.company_address_id    = anInfo.order_id;
                    aClaim["import_update_flag"] = true;
                    aClaim.Save();

                    if (p == 0 || p == 2 || p == 3) // Only update the id if this is the primary claim or a predeterm
                    {
                        aProcedure.claim_id = aClaim.id;
                    }

                    aProcedure.Save();

                    currentRow++;

                    if (Math.Truncate(incrementCounter + exactIncrementAmount) != Math.Truncate(incrementCounter))
                    {
                        //UpdateProgressBar(increment, string.Format("{0} / {1} procedures completed...", currentRow, totalRows), false);
                    }
                    incrementCounter += exactIncrementAmount;
                }
            }

            if (changesOnly)
            {
                // Grab all the deleted claims and mark them as closed here
                // Add a note that they have been deleted, I guess
                string deletedClaimsSQL = "SELECT CLAIMID, CLAIMDB " +
                                          "FROM AUDIT_DDB_CLAIM " +
                                          "WHERE N_CLAIMID is null " +
                                          "AND CLAIMID is not null AND CLAIMDB is not null " +
                                          "AND date_changed >= '" + lastWrite.ToString("G") + "'";
                DataTable    deletedClaims = new DataTable();
                OleDbCommand cmd           = new OleDbCommand(deletedClaimsSQL, oConnect);
                cmd.CommandTimeout = 90;

                oAdapter = new OleDbDataAdapter(cmd);

                oAdapter.Fill(deletedClaims);

                //UpdateProgressBar(5, "Updating Local Status for Deleted Claims...");
                foreach (DataRow aDeletedClaim in deletedClaims.Rows)
                {
                    // Close the claims
                    DataTable matches = aClaim.Search("SELECT * FROM claims WHERE claimidnum = '" + aDeletedClaim["claimid"] +
                                                      "' and claimdb = '" + aDeletedClaim["claimdb"] + "'");

                    if (matches.Rows.Count > 0)
                    {
                        // This should honestly not load every claim
                        aClaim = new claim();
                        aClaim.Load(matches.Rows[0]);
                        aClaim.open = 0;
                        aClaim.Save();
                        UpdateStatusHistory(aClaim);
                        closedClaimCount++;
                    }
                }
            }
            else
            {
                closedClaimCount = workingClaim.CloseClaimsWithoutUpdate();
            }

            //UpdateLabels(4);
            workingClaim.FixRevisitDateAfterImport();



            system_options.SetLastImportDate(DateTime.Now);
            ShowLastImportDate();
            AddStatus("The import completed successfully! " + totalRows + " rows were imported.");
            AddReportMessage(string.Format("The import completed successfully!\nPrimary: {0}\nSecondary: {1}\n" +
                                           "Predeterm: {2}\nSecondary Predeterm: {3}\nTotal Open Claims: {4}", primaryClaimCount, secondaryClaimCount, predetermClaimCount, secondaryPredetermClaimCount,
                                           primaryClaimCount + secondaryClaimCount + predetermClaimCount + secondaryPredetermClaimCount));

            try
            {
                string importFileName = Application.StartupPath + "\\Imports\\" + DateTime.Now.ToString("yyyy-MM-dd hh-mm-ss") + " import.rtf";
                //Directory.CreateDirectory(Path.GetDirectoryName(importFileName));
                //rtxtReport.SaveFile(importFileName);
                saveFileFromTemp();
                DeleteDirectoryFromTemp();
                CreateLogFile("Import Successfully.");
                Updateimporterror(false);
            }
            catch (Exception ex) {
                CreateLogFile(ex.ToString());
                Updateimporterror(true);
                DeleteDirectoryFromTemp();
                //LoggingHelper.Log("Error creating import report file.", LogSeverity.Error, ex, false);
            }
        }
        private void CheckEligibility()
        {
            data_mapping_schema dms = data_mapping_schema.GetDefaultSchema;
            OleDbConnection     oConnect;
            DataTable           resultData = new DataTable();
            OleDbDataAdapter    oAdapter;

            try
            {
                oConnect = new OleDbConnection(dms.GetConnectionString(true));
                oConnect.Open();
            }
            catch (Exception err)
            {
                LoggingHelper.Log("Could not connect to DB to send claims", LogSeverity.Error, err, false);
                return;
            }

            string sql = @"SELECT appt.patNAme as patient, 
payer.inscoName AS PAYER_NAME, payer.insID + '-' + payer.insDB AS Payer_ID, payer.STREET AS PAYER_ADDRESS1, payer.STREET2 AS PAYER_ADDRESS2, payer.CITY AS PAYER_CITY, payer.STATE AS PAYER_STATE,payer.ZIP AS PAYER_ZIP, 
appt.APPTDATE AS APPT_DATE, appt.time_hour AS APPT_HOUR, appt.time_minute AS APPT_MINUTE,
prov.NAME_FIRST AS Provider_FirstName, prov.NAME_LAST AS Provider_LastName, prov.ID1 AS Provider_NPI, prov.STATEID AS Provider_LicenseNo, 'CT' AS provider_state,
insured.idnum as Subscriber_ID, sub.lastname AS Subscriber_LastName, sub.firstname AS Subscriber_FirstName, sub.gender AS Subscriber_Gender, sub.birthdate AS Subscriber_DOB, payer.GroupNum as GroupNo, dep.patID + '-' + dep.patDB AS SubscriberDependentID,
dep.PRINSREL AS PatientRelation,
dep.patID + '-' + dep.PATDB as Dependent_ID, dep.LastName as Dependent_LastName, dep.FirstName as Dependent_FirstName, dep.gender AS Dependent_Gender, dep.BirthDate as Dependent_DOB

FROM DDB_APPT appt
INNER JOIN DDB_PAT_BASE dep ON (appt.PATID = dep.PATID AND appt.PATDB = dep.PATDB)
INNER JOIN DDB_INSURED insured ON (dep.PRINSUREDID = insured.INSUREDID AND dep.PRINSUREDDB = insured.insuredDB)
INNER JOIN DDB_INSURANCE_BASE payer ON (insured.insID = payer.insID AND insured.INSDB = payer.insDB)
INNER JOIN DDB_PAT_BASE sub ON (insured.insuredPartyID = sub.PATID AND insured.insuredPartyDB = sub.PATDB)
INNER JOIN DDB_RSC_BASE prov ON (prov.URSCID = appt.PRPROVID AND prov.RSCDB = appt.PRPROVDB)
WHERE (OPTYPE = 3) AND (";

            string whereClause = "";

            foreach (Control aControl in pnlResults.Controls)
            {
                if (aControl is ctlPatientEligibility)
                {
                    ctlPatientEligibility anAppointment = (ctlPatientEligibility)aControl;
                    if (anAppointment.CheckState)
                    {
                        if (whereClause != "")
                        {
                            whereClause += " OR";
                        }
                        whereClause += string.Format(" (APPTID = {0} AND APPTDB = {1})", anAppointment.EligibilityData.dentrix_id, anAppointment.EligibilityData.dentrix_db);
                    }
                }
            }

            sql += whereClause + ")";


            oAdapter = new OleDbDataAdapter(sql, oConnect);
            oAdapter.Fill(resultData);

            string fullDocAsList = EligibilityHelper.ConvertFromTable(resultData);

            ftpTransfer.Initialize();
            ftpTransfer.Show();

            ftpTransfer.UpdateMainLabel("Creating files to send to Mercury...", false);

            ParameterizedThreadStart ts = new ParameterizedThreadStart(UploadThreadSafe);
            Thread t = new Thread(ts);

            t.Start(fullDocAsList);
        }