private static void BuildVariables(DataTable variables, PxMainTable table)
{
var vari = from c in variables.AsEnumerable()
where c.Field<string>("Name") != null
select
new PxVariable {
Variable = c.Field<string>("Name"),
PresText = c.Field<string>("PressText"),
PresTextEnglish = c.Field<string>("EnglishName"),
Footnote = "N",
VariableType = GetVariableType(c.Field<string>("Name"))
};
foreach (var item in vari)
{
var variable = VariableFacade.GetVarible(item.Variable);
if (variable != null)
{
if (String.Compare(variable.Variable, item.Variable, true) == 0 )
{
if (String.Compare(variable.PresText, item.PresText, true) == 0)
{
item.IsNew = false;
}
else
{
item.Variable = item.Variable + Guid.NewGuid().ToString();
}
}
}
table.Variables.Add(item);
}
}
public CreateMainTable()
{
InitializeComponent();
pressCategoryComboBox.DataSource = Option.GetOptions("PresCategory");
specCharExistsComboBox.DataSource = Option.GetOptions("SpecCharExists");
englishStatusComboBox.DataSource = Option.GetOptions("StatusEn");
englishPublishedComboBox.DataSource = Option.GetOptions("YesNo");
statAuthorityComboBox.DataSource = Option.GetOptions("StatAuthority");
producerComboBox.DataSource = Option.GetOptions("StatAuthority");
presCellsZeroComboBox.DataSource = Option.GetOptions("YesNo");
aggregPossibleComboBox.DataSource = Option.GetOptions("YesNo");
stockFAComboBox.DataSource = Option.GetOptions("StockFA");
cFPricesComboBox.DataSource = Option.GetOptions("CFPrices");
dayAdjComboBox.DataSource = Option.GetOptions("YesNo");
seasAdjComboBox.DataSource = Option.GetOptions("YesNo");
storeFormatComboBox.DataSource = Option.GetOptions("StoreFormat");
variableTypeComboBox.DataSource = Option.GetOptions("VariableType");
copyrightComboBox.DataSource = Option.GetOptions("Copyright");
themeComboBox.DataSource = Option.GetThemeOptions();
timeScaleIdComboBox.DataSource = Option.GetTimeScaleOptions();
presMissingLineComboBox.DataSource = Option.GetPresMissingLineOptions();
FootnoteNo = VariableFacade.GetFootnoteNextId();
NewMainTable = new PxMainTable();
//lbContents.DataSource = _table.Contents;
//lbVariables.DataSource = _table.Variables;
}
public static PxMainTable BuildTableFromFile(string path, string tableId)
{
PxMainTable tbl = new PxMainTable();
tbl.TableId = tableId;
ExcelFileWrapper excel = new ExcelFileWrapper(path);
BuildContents(excel.GetSheetData("Contents"), tbl);
BuildVariables(excel.GetSheetData("Variables"), tbl);
return tbl;
}
public static bool CreateDataTable(PxMainTable table, ref string msg)
{
try
{
//Create the table
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PcAxisDatabase"].ConnectionString);
SqlCommand sc = new System.Data.SqlClient.SqlCommand(CreateDataTableCreateCommand(table), conn);
conn.Open();
sc.ExecuteNonQuery();
conn.Close();
//Update TableStatus to E in database
table.TableStatus = "E";
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var tbl = (from mt in context.MainTables
where mt.MainTable1 == table.TableId
select mt).First();
tbl.TableStatus = "E";
context.SaveChanges();
}
catch (SqlException ex)
{
if (ex.InnerException != null)
{
msg = ex.InnerException.Message;
}
else
{
msg = ex.Message;
}
return false;
}
return true;
}
public static bool AddContentsTime(PxMainTable mainTable)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
foreach (var content in mainTable.Contents)
{
foreach (var timePeriod in content.TimePeriods)
{
timePeriod.CreateEntities(context);
}
}
if (mainTable.TableStatus != "M")
{
mainTable.TableStatus = "U";
mainTable.Save(context);
}
try
{
context.SaveChanges();
foreach (var content in mainTable.Contents)
{
foreach (var timePeriod in content.TimePeriods)
{
timePeriod.MarkAsOld();
}
}
return true;
}
catch (Exception)
{
return false;
}
}
private static void BuildContents(DataTable contents, PxMainTable table)
{
var cont = from c in contents.AsEnumerable()
where c.Field<string>("Id") != null
select new PxContent
{ Content = c.Field<string>("Id"),
PresText = c.Field<string>("Text"),
PressTextS = c.Field<string>("Text"),
Copyright = GetCopyright(c.Field<string>("CopyRight")),
Unit = c.Field<string>("Unit"),
RefPeriod = c.Field<string>("RefPeriod"),
StockFA = c.Field<string>("StockFlowAverage") != "Other" ? c.Field<string>("StockFlowAverage")[0].ToString(): "X",
BasePeriod = c.Field<string>("BasePeriod"),
CFPrices = GetCFPrices(c.Field<string>("CurrentFixPrices")),
DayAdj = GetYesNo(c.Field<string>("DayAdj")),
SeasAdj = GetYesNo(c.Field<string>("SeasAdj")),
StoreFormat = GetStoreFormat(c.Field<string>("StoreFormat")),
StoreNoChar = c.IsNull("StoreNoChar") ? (short)0 : (short)c.Field<double>("StoreNoChar"),
StoreDecimals = c.IsNull("StoreDecimals") ? (short)0 : (short)c.Field<double>("StoreDecimals"),
FootnoteContents = "N",
FootnoteVariable = "N",
FootnoteTime = "N",
FootnoteValue = "N",
PressTextEnglish = c.Field<string>("EnglishText"),
PressTextEnglishS = c.Field<string>("EnglishText"),
UnitEnglish = c.Field<string>("EnglishUnit"),
RefPeriodEnglish = c.Field<string>("EnglishRefPeriod"),
BasePeriodEnglish = c.Field<string>("EnglishBasePeriod"),
StatAuthority = "INSTAT",
Producer = "INSTAT"
};
foreach (var item in cont)
{
table.Contents.Add(item);
}
}
public static bool DataInsertInDb(PxMainTable mt, string excelPath, bool deleteExistingRowsFirst, ref string message)
{
if (System.IO.File.Exists(excelPath))
{
string qryExcel;
//string qrySecondPart;
//qrySecondPart = String.Format(" FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database={0};HDR=YES', 'SELECT * FROM [Data$]'", ExcelSource._path);
qryExcel = "SELECT ";
int countVariables;
countVariables = mt.Variables.Count;
qryExcel = GetExcelQueryForDataLoading(mt);
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PcAxisDatabase"].ConnectionString);
try
{
DataTable dt = GetSheetDataForInsert(qryExcel, excelPath);
List<DataRow> drToRemove = new List<DataRow>();
foreach (DataRow row in dt.Rows)
{
if (String.IsNullOrEmpty(row[0].ToString()) )
{
drToRemove.Add(row);
}
}
foreach (DataRow row in drToRemove)
{
dt.Rows.Remove(row);
}
if (deleteExistingRowsFirst)
{
SqlCommand sc = new SqlCommand("DELTE FROM " + (mt.TableId + 1), conn);
conn.Open();
sc.ExecuteNonQuery();
conn.Close();
}
SqlBulkCopy sbc = new SqlBulkCopy(conn);
sbc.DestinationTableName = mt.TableId + 1;
conn.Open();
sbc.WriteToServer(dt);
conn.Close();
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
mt.TableStatus = "O";
Save(mt, ref message);
}
catch (Exception ex)
{
conn.Close();
message = ex.ToString();
return false;
}
return true;
}
return false;
}
private static string GetExcelQueryForDataLoading(PxMainTable table)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
for (int i = 0; i < table.Variables.Count; i++)
{
sb.Append("Variable" + (i+1).ToString() + ", ");
}
for (int i = 0; i < table.Contents.Count; i++)
{
sb.Append("Content" + (i + 1).ToString());
if (i != table.Contents.Count - 1)
{
sb.Append(", ");
}
}
sb.Append( " FROM [Data$]");
return sb.ToString();
}
private static string CreateDataTableCreateCommand(PxMainTable table)
{
string createCommand = "CREATE TABLE " + table.TableId + "1" + " (";
foreach (var i in table.Variables)
{
createCommand += i.Variable + " varchar(255), ";
}
//createCommand = createCommand.Substring(0, createCommand.Length - 2); //remove the last comma
string formatType = "";
foreach (var c in table.Contents)
{
switch (c.StoreFormat)
{
case "C":
formatType = "varchar(255)";
break;
case "I":
formatType = "int";
break;
case "S":
formatType = "int";
break;
case "N":
formatType = "float";
break;
default:
formatType = "varchar(255)";
break;
}//TODO find columntype from storeformat
createCommand += c.Content + " " + formatType + ", ";
}
createCommand = createCommand.Substring(0, createCommand.Length - 2); //remove the last comma
createCommand += ")";
return createCommand;
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
ImportFile = ofd.FileName;
txbExcelPath.Text = ImportFile;
PxMainTable tbl = PxDataLoader.Import.TableBuilder.BuildTableFromFile(ImportFile, "Test");
tbl.Theme = _table.Theme;
NewMainTable = tbl;
btnImport.Enabled = false;
}
}