public static int CreateValueSet(SQL_utils sql, int mID, uwac.Valueset vset)
{
int max = sql.IntScalar_from_SQLstring("select max(fieldvaluesetID) from datfieldvalueset");
string measname = sql.StringScalar_from_SQLstring("select measname from uwautism_research_backend..tblmeasure where measureID=" + mID.ToString());
int newmax = (max > 0) ? max + 1 : 0;
if (newmax > 0)
{
try
{
string setname = String.Format("{0} set {1}", measname, vset.localvaluesetid.ToString());
string code = String.Format("insert into datfieldvalueset (fieldvaluesetID, fieldvaluesetdesc) values({0},'{1}')"
, newmax, setname);
sql.NonQuery_from_SQLstring(code);
foreach (Valuesetitem itm in vset.valitems)
{
AddValueSetItem(sql, newmax, Convert.ToInt32(itm.value), itm.label);
}
return(newmax);
}
catch (Exception ex)
{
return(-1);
}
}
else
{
return(-1);
}
}
public void CreateDatadictionaryFromREDCapMeta(DataTable dt_meta, List <string> formnames)
{
foreach (DataRow row in dt_meta.Rows)
{
if (formnames.Contains(row["form_name"]))
{
uwac.Variable var = new uwac.Variable(row);
string raw_vallabels = row["select_choices_or_calculations"].ToString();
if (raw_vallabels.Contains("|"))
{
uwac.Valueset valset = new Valueset(raw_vallabels);
if (valset != null)
{
var.valueset = valset;
}
}
if (var != null)
{
vars.Add(var);
}
}
}
ExtractValuesets();
}
public bool CompareEm(Valueset vset1, Valueset vset2)
{
bool match = false;
if (vset1.valitems.Count == vset2.valitems.Count)
{
int tot = vset1.valitems.Count;
int numerrors = 0;
for (int i = 0; i < vset1.valitems.Count; i++)
{
if (numerrors == 0)
{
var o1 = vset1.valitems[i];
var o2 = vset2.valitems[i];
if (o1.value != o2.value | o1.label != o2.label)
{
numerrors++;
}
//else{
// var x = "hooray!";
//}
}
}
if (numerrors == 0)
{
match = true;
}
}
if (match)
{
vset1.numtimesused++;
}
return(match);
}
public int ValuesetToDB(SQL_utils sql, Valueset vset)
{
if (measureid > 0)
{
int newvaluesetid = DxDbOps.CreateValueSet(sql, measureid, vset);
return(newvaluesetid);
}
return(-1);
}
private void PopulateValueSets(int tblpk)
{
SQL_utils sql = new SQL_utils("data");
DataTable dt_valsets = sql.DataTable_from_SQLstring("select fldname, fieldvaluesetID from def.fld where ConvertFromLabelToValue=1 and tblpk=" + tblpk.ToString());
List <int> valsetIDs = dt_valsets.AsEnumerable().Select(f => f.Field <int>("fieldvaluesetID")).Distinct().ToList();
foreach (int valsetID in valsetIDs)
{
Valueset valset = new Valueset(valsetID);
if (valset != null)
{
valuesets.Add(valset);
}
}
}
public Variable(SpssLib.SpssDataset.Variable spssvar)
{
varname = spssvar.Name;
varlabel = spssvar.Label;
//if (spssvar.IsDate()) datatype = "date";
//else if (spssvar.Type == SpssLib.SpssDataset.DataType.Numeric) datatype = "float";
//else if (spssvar.Type == SpssLib.SpssDataset.DataType.Text) datatype = "varchar";
if (spssvar.IsDate())
{
datatype = new Datatype(SqlDatatype.Date);
}
else if (spssvar.Type == SpssLib.SpssDataset.DataType.Numeric)
{
datatype = new Datatype(SqlDatatype.Float);
}
else if (spssvar.Type == SpssLib.SpssDataset.DataType.Text)
{
datatype = new Datatype(SqlDatatype.Varchar);
}
if (spssvar.ValueLabels != null)
{
if (spssvar.ValueLabels.Count > 0)
{
Valueset valset = new Valueset();
foreach (KeyValuePair <double, string> label in spssvar.ValueLabels)
{
Valuesetitem itm = new Valuesetitem(label);
valset.valitems.Add(itm);
}
valueset = valset;
}
}
}
public void AddValueset(Valueset valset, int idx)
{
valset.localvaluesetid = idx;
valsets.Add(valset);
}
public void ExtractValuesets()
{
int counter = 0;
foreach (Variable v in vars)
{
if (v.HasValueset())
{
//add the first one
if (valsets.Count == 0)
{
AddValueset(v.valueset, counter);
counter++;
}
else
{
bool hasmatch = false;
bool keeplooking = true;
int nummatches = 0;
for (int i = 0; i < valsets.Count; i++)
{
if (keeplooking)
{
Valueset _vset = valsets[i];
bool boo1 = (_vset == v.valueset) ? true : false;
bool boo2 = (_vset.Equals(v.valueset)) ? true : false;
bool boo3 = CompareEm(_vset, v.valueset);
nummatches += (boo3) ? 1 : 0;
//Debug.WriteLine("nummatches: " + nummatches.ToString());
if (boo3)
{
v.valueset.localvaluesetid = _vset.localvaluesetid;
hasmatch = true;
keeplooking = false;
}
}
}
if (!hasmatch)
{
AddValueset(v.valueset, counter);
counter++;
}
}
}
}
Debug.WriteLine("1. # valsets = " + valsets.Count.ToString());
valsets = valsets.Distinct().ToList();
Debug.WriteLine("2. # valsets = " + valsets.Count.ToString());
bool print_valsets = false;
if (print_valsets)
{
foreach (Valueset valset in valsets)
{
Debug.WriteLine(String.Format("local: {0} numtimesused: {1} numitems: {2}", valset.localvaluesetid, valset.numtimesused, valset.valitems.Count));
}
foreach (uwac.Variable v in vars)
{
if (v.HasValueset())
{
Debug.WriteLine(String.Format(" -- > local: {0} numitems: {1}", v.valueset.localvaluesetid, v.valueset.valitems.Count));
}
}
}
}