private string GetCategorisationID(CategoryScheme cs)
{
string catsID = String.Empty;
if (cs.Id != String.Empty)
{
catsID = String.Format("{0}@{1}@{2}@{3}@{4}@{5}",
_ds.DSDIdentifier.id,
_ds.DSDIdentifier.agencyid,
_ds.DSDIdentifier.version.Replace(".", ""),
cs.Id,
cs.Version.Replace(".", ""),
cs.Categories[0].Code);
}
return catsID;
}
public static CategoryScheme GetCategoryInfo(int idCat)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "proc_GET_CATEGORY_INFO";
System.Data.IDbDataParameter prmIDCat = cmd.CreateParameter();
prmIDCat.DbType = System.Data.DbType.Int32;
prmIDCat.ParameterName = "@IDCat";
prmIDCat.Value = idCat;
cmd.Parameters.Add(prmIDCat);
CategoryScheme cs = new CategoryScheme();
IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
cs.Id = reader["ID"].ToString();
cs.AgencyId = reader["Agency"].ToString();
cs.Version = reader["Version"].ToString();
cs.Categories.Add(new Category() { Code = reader["CatCode"].ToString() });
}
dtw.DBConnection.Close();
return cs;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
/*
public void Set_TRANSCODE_TIME(string charEnd, TranscodeTime.TypePeriod typePeriod) {
_useTranscodeTime = true;
_transcodeTime = new TranscodeTime();
_transcodeTime.stopChar = charEnd;
_transcodeTime.periodChar = ((TranscodeTime.TypePeriod)typePeriod);
if (this._mapping!=null && this._mapping.IsValid)
{
ISTAT.DBDAL.DataAccess.Update_TranscodeTime(this._mapping.IDSchema, charEnd, (int) typePeriod);
}
}
public TranscodeTime Get_TRANSCODE_TIME()
{
_useTranscodeTime = true;
if (this._mapping != null && this._mapping.IsValid)
{
return ISTAT.DBDAL.DataAccess.Get_TranscodeTime(this._mapping.IDSchema);
}
else return null;
}
public Dictionary<string, List<string>> Get_DATA_VIEW(int maxResult=0)
{
Dictionary<string, List<string>> _view = null;
// return view of csv file
if (_fileCsvData != null && _fileCsvData.IsValid)
{
string[] filesCSV= System.IO.Directory.GetFiles(_fileCsvData.filePath);
foreach (string fileName in filesCSV)
{
System.IO.StreamReader streamFile = new System.IO.StreamReader(fileName);
_view = Mapping.GetView(streamFile.BaseStream, _mapping, _transcodeTime, maxResult, (_fileCsvData.firstRowHeader) ? 1 : 0);
streamFile.Close();
break;
}
}
// return view of xml file
if (_filesXmlData != null)
foreach (string fileName in _filesXmlData)
{
System.IO.StreamReader streamFile = new System.IO.StreamReader(fileName);
_view = ISTAT.DBDAL.DataSDMX.GetView(streamFile.BaseStream, _transcodeTime, maxResult);
streamFile.Close();
break;
}
return _view;
}
public QueryReport Insert_DATA()
{
QueryReport result = new QueryReport();
Dictionary<string, List<string>> _view = null;
// return view of csv file
if (_fileCsvData != null && _fileCsvData.IsValid)
{
string[] filesCSV = System.IO.Directory.GetFiles(_fileCsvData.filePath);
foreach (string fileName in filesCSV)
{
using (System.IO.StreamReader streamFile = new System.IO.StreamReader(fileName))
{
_view = Mapping.GetView(streamFile.BaseStream, _mapping, _transcodeTime, 0, 0);
}
QueryReport _result = ISTAT.DBDAL.DataAccess.Insert_Data(this.DataSet.IDSet, _view);
result.Marge(_result);
}
}
// return view of xml file
if (_filesXmlData != null)
foreach (string fileName in _filesXmlData)
{
using (System.IO.StreamReader streamFile = new System.IO.StreamReader(fileName))
{
_view = ISTAT.DBDAL.DataSDMX.GetView(streamFile.BaseStream, _transcodeTime, 0);
}
QueryReport _result = ISTAT.DBDAL.DataAccess.Insert_Data(this.DataSet.IDSet, _view);
result.Marge(_result);
}
return result;
}
*/
public List<CategoryScheme> GetCategoryScheme()
{
try
{
DataTable dtCSFull = DataAccess.GetCategorySchemes();
List<CategoryScheme> lCategories = new List<CategoryScheme>();
if (dtCSFull == null || dtCSFull.Rows.Count <= 0)
return null;
DataView viewCS = new DataView(dtCSFull);
DataTable dtCS = viewCS.ToTable(true, "IDCS", "CSID", "CSAgency", "CSVersion");
CategoryScheme catSchema = null;
string filter;
foreach (DataRow cs in dtCS.Rows)
{
catSchema = new CategoryScheme();
catSchema.Id = cs["CSID"].ToString();
catSchema.AgencyId = cs["CSAgency"].ToString();
catSchema.Version = cs["CSVersion"].ToString();
// Get Names
DataTable dtCSNames = viewCS.ToTable(true, "IDCS", "CSLangName", "CSValueName");
filter = String.Format("IDCS = {0}", cs["IDCS"].ToString());
var names = dtCSNames.Select(filter);
foreach (var name in names)
catSchema.Names.Add(new TextTypeWrapper(name[1].ToString(), name[2].ToString()));
DataTable dtCategories = viewCS.ToTable(true, "IdCat", "IdParent", "Catcode", "IDCS");
var categories = dtCategories.Select(filter);
foreach (var category in categories)
{
Category cat;
cat = new Category(category["IDCat"].ToString(), category["IDParent"].ToString(), category["CatCode"].ToString());
DataTable dtCatNames = viewCS.ToTable(true, "IDCat", "CatLangName", "CatValueName", "IDCS");
string newfilter = filter + " AND IDCat = " + cat.IDCategory;
var categoryNames = dtCatNames.Select(newfilter);
foreach (var categoryName in categoryNames)
cat.Names.Add(new TextTypeWrapper(categoryName["CatLangName"].ToString(), categoryName["CatValueName"].ToString()));
catSchema.Categories.Add(cat);
}
lCategories.Add(catSchema);
}
return lCategories;
}
catch (Exception ex)
{
throw ex;
}
}