public ScrapeModel Scrape(ScrapeQuery query)
{
foreach (var scraper in Scrapers)
{
if (scraper.TrackerType == query.TrackerType)
{
return(scraper.Scrape(query));
}
}
throw new Exception("No compatible Scraper found");
}
public async Task <IActionResult> OnGetAsync(int?id)
{
if (id == null)
{
return(NotFound());
}
ScrapeQuery = await _context.ScrapeQuery.FirstOrDefaultAsync(m => m.Id == id);
if (ScrapeQuery == null)
{
return(NotFound());
}
return(Page());
}
public async Task <IActionResult> OnPostAsync(int?id)
{
if (id == null)
{
return(NotFound());
}
ScrapeQuery = await _context.ScrapeQuery.FindAsync(id);
if (ScrapeQuery != null)
{
_context.ScrapeQuery.Remove(ScrapeQuery);
await _context.SaveChangesAsync();
}
return(RedirectToPage("./Index"));
}
/// <summary>
/// Creates the Create Table, Drop Table, Select queries of the given formname.
/// The queries are stored, form is not active by default.
/// </summary>
/// <param name="formname">Name of the form.</param>
public async Task <int> GenerateScrapeFormQueries(string formname)
{
List <FormTransData> AllCols = new List <FormTransData>();
using (var command = _profiledbcontext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "huapiGetFormTransData";
var formnameParam = new SqlParameter("@FormName", SqlDbType.VarChar);
formnameParam.Value = formname;
command.Parameters.Add(formnameParam);
command.CommandType = System.Data.CommandType.StoredProcedure;
_profiledbcontext.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
while (result.Read())
{
FormTransData ftd = new FormTransData();
ftd.COLLECTION = 0;
if (!result.IsDBNull(result.GetOrdinal("COLLECTION")))
{
ftd.COLLECTION = result.GetInt64(result.GetOrdinal("COLLECTION"));
}
ftd.DESCRIPTION = result.GetString(result.GetOrdinal("DESCRIPTION"));
ftd.CID = result.GetInt64(result.GetOrdinal("CID"));
ftd.CODE = result.GetString(result.GetOrdinal("CODE"));
ftd.DT_MODIFIED = result.GetDateTime(result.GetOrdinal("DT_MODIFIED"));
ftd.OID = result.GetInt64(result.GetOrdinal("OID"));
ftd.CONCEPT = result.GetInt64(result.GetOrdinal("CONCEPT"));
var cntr = RecursiveCheckNameExists(AllCols, ftd.DESCRIPTION, 0);
if (cntr > 0)
{
ftd.DESCRIPTION = $"{ftd.DESCRIPTION}_{cntr}";
}
AllCols.Add(ftd); //TODO change to kvp so we deal with duplicate field names
}
}
}
//lets get the root of the HRI values which is the form collection itself.
FormTransData roothri = (from p in AllCols where p.COLLECTION == 0 select p).SingleOrDefault();
if (roothri == null)
{
throw new Exception("The root HRI collection couldn't be found. This shouldn't happen.");
}
//TODO cleans the roothri.DESCRIPTION in case it has symbols and such
StringBuilder fields = new StringBuilder();
StringBuilder scrapefields = new StringBuilder();
scrapefields.Append($"INSERT INTO [{stageDBName}].[dbo].[{formname}] SELECT Patient_2.PTNT_PATIENTCODE PAT_ID, Patient_4.DTDISPLAY thedate");
//go through rest of HRIs to build create and scrape queries for the HRI fields.
foreach (var item in AllCols.Where(p => p.COLLECTION != 0))
{
//create the create table from this list.
string desc = item.DESCRIPTION.Replace(' ', '_').Replace(',', '_').Replace('(', '_').Replace(')', '_');
switch (item.CID)
{
case 100511: //text
fields.Append($",[{desc}] [varchar](255) NULL");
scrapefields.Append($",(select TOP 1 LEFT([{sourceDBName}].[dbo].GetTextByTag(td0.TEXTS, 'TX'),255) from [{sourceDBName}].[dbo].cdo_transdata td0 where td0.trans = TheForm_1.trans and td0.versionmax > 1000000 and td0.concept = {item.CONCEPT} ) [{desc}] ");
break;
case 100842: //lookup
fields.Append($",[{desc}] [varchar](255) NULL");
scrapefields.Append($",(select TOP 1 [{sourceDBName}].[dbo].GetTextByTag(td0.TEXTS, 'TX') from [{sourceDBName}].[dbo].cdo_transdata td0 where td0.trans = TheForm_1.trans and td0.versionmax > 1000000 and td0.concept = {item.CONCEPT} ) [{desc}] ");
break;
case 100512: //memo
fields.Append($",[{desc}] [varchar](1024) NULL");
scrapefields.Append($",(select TOP 1 LEFT([{sourceDBName}].[dbo].GetTextByTag(td0.TEXTS, 'TX*'),255) from [{sourceDBName}].[dbo].cdo_transdata td0 where td0.trans = TheForm_1.trans and td0.versionmax > 1000000 and td0.concept = {item.CONCEPT} ) [{desc}] ");
break;
case 100517: //date
fields.Append($",[{desc}] [datetime] NULL");
scrapefields.Append($", (select TOP 1 td0.DT1 from [{sourceDBName}].[dbo].cdo_transdata td0 where td0.trans = TheForm_1.trans and td0.versionmax > 1000000 and td0.concept = {item.CONCEPT} ) [{desc}] ");
break;
default:
fields.Append($",[{desc}] [varchar](255) NULL");
scrapefields.Append($",(select TOP 1 [{sourceDBName}].[dbo].GetTextByTag(td0.TEXTS, 'TX') from [{sourceDBName}].[dbo].cdo_transdata td0 where td0.trans = TheForm_1.trans and td0.versionmax > 1000000 and td0.concept = {item.CONCEPT} ) [{desc}] ");
break;
}
}
//construct the create table query
string createstr = $@"CREATE TABLE [dbo].[{formname}](
[patid][varchar](50) NOT NULL,
[dtmodified] [datetime] NOT NULL
{fields.ToString()}
) ON[PRIMARY]";
//construct the drop table query
string dropstr = $@"DROP TABLE [dbo].[{formname}]";
//construct the scrape table query
scrapefields.Append($@"FROM [{sourceDBName}].[dbo].CDO_TRANS Form__Registered__3 inner join [{sourceDBName}].[dbo].CDO_TRANSDATA TheForm_1 on (Form__Registered__3.OID = TheForm_1.TRANS) left outer join [{sourceDBName}].[dbo].CDO_TRANS Patient_4 on (TheForm_1.TRANS=Patient_4.OID) and (Patient_4.Deleted > CAST('01-JAN-3000' AS datetime)) left outer join [{sourceDBName}].[dbo].CDO_EHCR EHCR__5 on (Patient_4.EHCR=EHCR__5.OID) left outer join [{sourceDBName}].[dbo].CDO_PERSON CDO_Person__6 on (EHCR__5.PATIENT=CDO_Person__6.OID) left outer join [{sourceDBName}].[dbo].PATIENT Patient_2 on (CDO_Person__6.REAL_ID=Patient_2.PTNT_ID) and (/*INT_IND_OFF*/ Patient_2.PTNT_DELETEDID < 1 )WHERE ( ( (Form__Registered__3.CID = 100400 or Form__Registered__3.CID = 100401) and Form__Registered__3.Deleted >= CAST('01-JAN-3000' AS datetime) AND Form__Registered__3.FILING_CATEGORY = 7) and ( TheForm_1.VERSIONMAX > 1000000 and TheForm_1.CID = 100502 and TheForm_1.collection is null and TheForm_1.concept = {roothri.CONCEPT} AND not exists (select 1 from [{sourceDBName}].[dbo].CDO_TRANS TRANSREF1CHECK where TheForm_1.TRANS = TRANSREF1CHECK.OID and TRANSREF1CHECK.REF1 = 13) AND (EXISTS (SELECT 1 FROM [{sourceDBName}].[dbo].CDO_TRANS C_T WHERE C_T.OID = TheForm_1.TRANS ))) and (CDO_Person__6.CID = 100105) )");
ScrapeQuery sq = new ScrapeQuery("ScrapeQuery", formname, "General", DateTime.Now, false, dropstr, createstr, scrapefields.ToString(), 1000);
//now insert the SQ into the HUAPI database and then we can call an api to schedule the scraping to occur.
_huapidbcontext.Add <ScrapeQuery>(sq);
await _huapidbcontext.SaveChangesAsync();
return(0);
}