//TODO update entities
public override void UpdateEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
PxMetaModel.Value value = (from v in context.Values
where v.ValuePool == ValuePool && v.ValueCode == ValueCode
select v).First();
value.ValueTextL = ValueText;
value.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
value.LogDate = DateTime.Now;
PxMetaModel.Value_Eng value_eng = value.Value_Eng;
value_eng.ValuetextL = ValueTextEnglish;
value_eng.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
value_eng.LogDate = DateTime.Now;
foreach (var valueFootnote in ValueFootnotes)
{
valueFootnote.Value = this;
valueFootnote.Save(context);
}
foreach (var removedValueFootnote in RemovedValueFootnotes)
{
removedValueFootnote.DeleteEntities(context);
}
}
public override void UpdateEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
if (!IsNew)
{
var valueset = (from vs in context.ValueSets
where vs.ValueSet1 == Valueset
select vs).First();
valueset.PresText = PresText;
valueset.Description = PresText;
valueset.Elimination = Elimination;
valueset.SortCodeExists = "Y";
valueset.Footnote = "S";
valueset.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
valueset.LogDate = DateTime.Now;
var valueset_eng = (from vs_eng in context.ValueSet_Eng
where vs_eng.ValueSet == Valueset
select vs_eng).First();
valueset_eng.PresText = PresTextEnglish;
valueset_eng.Description = PresTextEnglish;
valueset_eng.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
valueset_eng.LogDate = DateTime.Now;
foreach (var v in Values)
{
if (!v.IsNew)
{
v.ValuePool = ValuePool;
v.Save(context);
}
}
}
}
public static List<Option> GetTimeScaleOptions()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var timeScales = from ts in context.TimeScales
select new Option() { Code = ts.TimeScale1, Text = ts.PresText };
return timeScales.ToList();
}
public override void CreateEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
PxMetaModel.MenuSelection menuSelection = new PxMetaModel.MenuSelection();
menuSelection.Menu = Parent.Menu;
menuSelection.Selection = Menu;
menuSelection.PresText = PresText;
menuSelection.PresTextS = PresTextS;
menuSelection.Presentation = Presentation;
menuSelection.LevelNo = LevelNo;
menuSelection.Description = Description;
menuSelection.SortCode = SortCode;
menuSelection.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
menuSelection.LogDate = DateTime.Now;
context.AddToMenuSelections(menuSelection);
PxMetaModel.MenuSelection_Eng menuSelectionEng = new PxMetaModel.MenuSelection_Eng();
menuSelectionEng.MenuSelection = menuSelection;
menuSelectionEng.PresText = PresTextEnglish;
menuSelectionEng.PresTextS = PresTextSEnglish;
menuSelectionEng.Presentation = PresentationEnglish;
menuSelectionEng.Description = DescriptionEnglish;
menuSelectionEng.SortCode = SortCodeEnglish;
menuSelectionEng.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
menuSelectionEng.LogDate = DateTime.Now;
context.AddToMenuSelection_Eng(menuSelectionEng);
}
public override void CreateEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
if (IsNew)
{
base.CreateEntities(context);
PxMetaModel.FootnoteContent footnoteContent = new PxMetaModel.FootnoteContent();
footnoteContent.MainTable = MainTable.TableId;
footnoteContent.Contents = Content.Content;
footnoteContent.FootnoteNo = FootnoteNo;
footnoteContent.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
footnoteContent.LogDate = DateTime.Now;
context.AddToFootnoteContents(footnoteContent);
}
else
{
base.UpdateEntities(context);
}
}
public override void CreateEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
PxMetaModel.Value value = new PxMetaModel.Value();
value.ValuePool = ValuePool;
value.ValueCode = ValueCode;
value.ValueTextL = ValueText;
value.Footnote = Footnote;
value.SortCode = ValueCode;
value.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
value.LogDate = DateTime.Now;
context.AddToValues(value);
PxMetaModel.Value_Eng valueEng = new PxMetaModel.Value_Eng();
valueEng.Value = value;
valueEng.ValuetextL = ValueTextEnglish;
valueEng.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
valueEng.LogDate = DateTime.Now;
valueEng.SortCode = ValueCode;
context.AddToValue_Eng(valueEng);
foreach (var valueFootnote in ValueFootnotes)
{
valueFootnote.Value = this;
valueFootnote.Save(context);
}
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
var var = (from v in context.SubTableVariables
where v.MainTable == MainTable.TableId && v.SubTable == "1" && v.Variable == Variable
select v).First();
context.DeleteObject(var);
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
var f = (from mfvalue in context.FootnoteMaintValues
where mfvalue.FootnoteNo == FootnoteNo && mfvalue.MainTable == MainTable.TableId && mfvalue.Variable == Variable.Variable && mfvalue.ValuePool == Value.ValuePool && mfvalue.ValueCode == Value.ValueCode
select mfvalue).First();
context.DeleteObject(f);
}
public static List<Option> GetThemeOptions()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var themes = from theme in context.MenuSelections
where theme.LevelNo == "1"
select new Option() { Code = theme.Selection, Text = theme.PresText };
return themes.ToList();
}
public static List<Option> GetPresMissingLineOptions()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var timeScales = from ts in context.SpecialCharacter
select new Option() { Code = ts.CharacterType, Text = ts.PresText };
List<Option> l =timeScales.ToList();
l.Insert(0, new Option() { Code="", Text = ""});
return l;
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
var f = (from c in context.FootnoteMainTables
where c.FootnoteNo == FootnoteNo && c.MainTable == MainTable.TableId
select c).First();
context.DeleteObject(f);
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
var f = (from fvalue in context.FootnoteValues
where fvalue.FootnoteNo == FootnoteNo && fvalue.ValuePool == Value.ValuePool && fvalue.ValueCode == Value.ValueCode
select fvalue).First();
context.DeleteObject(f);
}
public static bool VariableExisit(string variableName)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = from v in context.Variables
where v.Variable1 == variableName
select new { ValueCode = v.Variable1 };
return(valueList.ToList().Count == 1);
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
var f = (from cv in context.FootnoteContVbls
where cv.FootnoteNo == FootnoteNo && cv.MainTable == MainTable.TableId && cv.Contents == Content.Content && cv.Variable == Variable.Variable
select cv).First();
context.DeleteObject(f);
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
foreach (PxTime time in _time)
{
time.DeleteEntities(context);
}
foreach (PxContentFootnote contentFootnote in Footnotes)
{
contentFootnote.DeleteEntities(context);
}
foreach (PxContentVariableFootnote contentVariableFootnote in ContentVariableFootnotes)
{
contentVariableFootnote.DeleteEntities(context);
}
foreach (PxContentValueFootnote contentValueFootnote in ContentValueFootnotes)
{
contentValueFootnote.DeleteEntities(context);
}
foreach (PxContentVariableFootnote contentVariableFootnote in RemovedContentVariableFootnotes)
{
contentVariableFootnote.DeleteEntities(context);
}
foreach (PxContentFootnote contentFootnote in RemovedFootnotes)
{
contentFootnote.DeleteEntities(context);
}
foreach (PxContentValueFootnote contentValueFootnote in RemovedContentValueFootnotes)
{
contentValueFootnote.DeleteEntities(context);
}
var contEngToDel = (from c in context.Contents_Eng
where c.Contents == Content && c.MainTable == MainTable.TableId
select c).FirstOrDefault();
if (contEngToDel != null)
{
context.DeleteObject(contEngToDel);
}
var contToDel = (from c in context.Contents
where c.Contents == Content && c.MainTable == MainTable.TableId
select c).FirstOrDefault();
if (contToDel != null)
{
context.DeleteObject(contToDel);
}
}
public static PxMenuSelection GetMenuStart()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var mythemes = new PxMenuSelection()
{
Menu = "Start", PresText = "Start", LevelNo = "0", Description = "Start", Presentation = "A", PresTextS = "Start", SortCode = "A"
};
return(mythemes);
}
public static List <Option> GetTimeScaleOptions()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var timeScales = from ts in context.TimeScales
select new Option()
{
Code = ts.TimeScale1, Text = ts.PresText
};
return(timeScales.ToList());
}
public static List <Option> GetVariableList()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var varList = from v in context.Variables
select new Option()
{
Code = v.Variable1, Text = v.PresText
};
return(varList.ToList());
}
public static List <Option> GetValuePools()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var poolList = from v in context.ValuePools
select new Option()
{
Code = v.ValuePool1, Text = v.PresText
};
return(poolList.ToList());
}
public static List <Option> GetValuesetsByValuePool(string valuePool)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var poolList = from v in context.ValueSets
where v.ValuePool == valuePool
select new Option()
{
Code = v.ValueSet1, Text = v.PresText
};
return(poolList.ToList());
}
public static List <Option> GetThemeOptions()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var themes = from theme in context.MenuSelections
where theme.LevelNo == "1"
select new Option()
{
Code = theme.Selection, Text = theme.PresText
};
return(themes.ToList());
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
foreach (PxTimeFootnote timeFootnote in _timeFootnotes)
{
timeFootnote.DeleteEntities(context);
}
var contTime = (from ct in context.ContentsTimes
where ct.Contents == Content.Content && ct.MainTable == Content.MainTable.TableId && ct.TimePeriod == TimePeriod
select ct).First();
context.DeleteObject(contTime);
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
var f = (from c in context.FootnoteContents
where c.FootnoteNo == FootnoteNo && c.MainTable == MainTable.TableId && c.Contents == Content.Content
select c).FirstOrDefault();
if (f != null)
{
context.DeleteObject(f);
}
}
public override void DeleteEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
base.DeleteEntities(context);
var f = (from fcontTime in context.FootnoteContTimes
where fcontTime.FootnoteNo == FootnoteNo && fcontTime.MainTable == ContentTime.Content.MainTable.TableId && fcontTime.Contents == ContentTime.Content.Content && fcontTime.TimePeriod == ContentTime.TimePeriod
select fcontTime).FirstOrDefault();
if (f != null)
{
context.DeleteObject(f);
}
}
public static string GetValuepoolByValueSet(string valueset)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valuePool = (from vp in context.ValueSets
where vp.ValueSet1 == valueset
select vp.ValuePool).ToList();
if (valuePool.Count != 0)
{
return(valuePool.First());
}
return(null);
}
//public static bool CreateEntities(PxObject obj, ref string message)
//{
// try
// {
// PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
// obj.CreateEntities(context);
// context.SaveChanges();
// obj.MarkAsOld();
// }
// catch (Exception ex)
// {
// //TODO chage the exception type to EF Exception and look for an inner exception.
// message = ex.Message;
// return false;
// }
// return true;
//}
public static List <Model.PxValue> GetValuesByValuePool(string valuepool)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = from v in context.Values
where v.ValuePool == valuepool
select new Model.PxValue()
{
ValueCode = v.ValueCode, ValueText = v.ValueTextL, ValueTextEnglish = v.Value_Eng.ValuetextL, IsNew = false
};
return(valueList.ToList());
}
public static List <Option> GetValuesListByValueset(string valueSet)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = from v in context.VSValues
where v.ValueSet == valueSet
select new Option()
{
Code = v.ValueCode, Text = v.Value.ValueTextL
};
return(valueList.ToList());
}
public override void UpdateEntities(PxMetaModel.PcAxisMetabaseEntities context)
{
if (!IsNew)
{
PxMetaModel.Variable variable = (from v in context.Variables
where v.Variable1 == Variable
select v).First();
variable.PresText = PresText;
variable.VariableInfo = VariableInfo;
variable.Footnote = Footnote == null ? "N" : Footnote;
variable.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
variable.LogDate = DateTime.Now;
PxMetaModel.Variable_Eng variableEng = (from v in context.Variable_Eng
where v.Variable == Variable
select v).First();
variableEng.PresText = PresTextEnglish;
variableEng.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
variableEng.LogDate = DateTime.Now;
}
PxMetaModel.SubTableVariable subTableVariable = (from st in context.SubTableVariables
where st.MainTable == MainTable.TableId && st.SubTable == "1" && st.Variable == Variable
select st).FirstOrDefault();
if (subTableVariable == null)
{
subTableVariable = new PxMetaModel.SubTableVariable();
subTableVariable.MainTable = MainTable.TableId;
subTableVariable.SubTable = "1";
subTableVariable.Variable = Variable;
context.AddToSubTableVariables(subTableVariable);
}
subTableVariable.ValueSet = String.IsNullOrWhiteSpace(ValueSet) ? null : ValueSet;
subTableVariable.VariableType = VariableType;
subTableVariable.StoreColumnNo = StoreColumnNo;
subTableVariable.UserID = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
subTableVariable.LogDate = DateTime.Now;
foreach (var variableFootnote in VariableFootnotes)
{
variableFootnote.Variable = this;
variableFootnote.Save(context);
}
foreach (var removedVariableFootnote in RemovedVariableFootnotes)
{
removedVariableFootnote.DeleteEntities(context);
}
}
public static int GetFootnoteNextId()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
int maxID = 0;
if ((from f in context.Footnotes
select f.FootnoteNo).Count() != 0)
{
maxID = (int)(from f in context.Footnotes
select f.FootnoteNo).Max();
}
maxID++;
return(maxID);
}
public void Save(PxMetaModel.PcAxisMetabaseEntities context)
{
//TODO check if isdirty works
if (IsDirty)
{
if (IsNew)
{
CreateEntities(context);
}
else
{
UpdateEntities(context);
}
}
}
public static Model.PxValueSet GetValueSet(string valueSet)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var varList = from v in context.ValueSets
where v.ValueSet1 == valueSet
select new Model.PxValueSet()
{
Valueset = v.ValueSet1, PresText = v.PresText, Elimination = v.Elimination, ValuePool = v.ValuePool, ValuePres = v.ValuePres, PresTextEnglish = v.ValueSet_Eng.PresText
};
if (varList.Count() != 0)
{
return(varList.First());
}
return(null);
}
public static List <Option> GetPresMissingLineOptions()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var timeScales = from ts in context.SpecialCharacter
select new Option()
{
Code = ts.CharacterType, Text = ts.PresText
};
List <Option> l = timeScales.ToList();
l.Insert(0, new Option()
{
Code = "", Text = ""
});
return(l);
}
public static Model.PxVariable GetVarible(string varId)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var varList = from v in context.Variables
where v.Variable1 == varId
select new Model.PxVariable()
{
Variable = v.Variable1, PresText = v.PresText, VariableInfo = v.VariableInfo, PresTextEnglish = v.Variable_Eng.PresText, IsNew = false
};
if (varList.Count() > 0)
{
return(varList.First());
}
return(null);
}
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;
}
}
public static int GetFootnoteNextId()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
int maxID = 0;
if ((from f in context.Footnotes
select f.FootnoteNo).Count() != 0)
{
maxID = (int)(from f in context.Footnotes
select f.FootnoteNo).Max();
}
maxID++;
return maxID;
}
public static List<Model.PxValue> GetValuesByValueset(string valueSet)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = (from v in context.VSValues
where v.ValueSet == valueSet
select new Model.PxValue() { ValueCode = v.ValueCode, ValuePool = v.ValuePool, ValueText = v.Value.ValueTextL, ValueTextEnglish = v.Value.Value_Eng.ValuetextL, IsNew = false, IsDirty = false }).ToList();
foreach (var value in valueList)
{
var footnoteValues = (from fv in context.FootnoteValues
where fv.ValuePool == value.ValuePool && fv.ValueCode == value.ValueCode
select new Model.PxValueFootnote()
{
FootnoteNo = (int)fv.FootnoteNo,
FootnoteText = fv.Footnote.FootnoteText,
FootnoteTextEnglish = fv.Footnote.Footnote_Eng.FootnoteText,
FootnoteType = fv.Footnote.FootnoteType,
MandOption = fv.Footnote.MandOpt,
ShowFootnote = fv.Footnote.ShowFootnote,
IsDirty = false,
IsNew = false
}).ToList();
foreach (var f in footnoteValues)
{
value.ValueFootnotes.Add(f);
}
}
return valueList
;
}
public static PxMenuSelection GetMenuStart()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var mythemes = new PxMenuSelection() { Menu = "Start", PresText = "Start", LevelNo = "0", Description = "Start", Presentation = "A", PresTextS = "Start", SortCode = "A" };
return mythemes;
}
public static PxMainTable GetMainTableById(string mainTable)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var table = (from tbl in context.MainTables
where tbl.MainTable1 == mainTable
select new PxMainTable()
{
TableId = tbl.MainTable1,
ContentVariable = tbl.ContentsVariable,
PresText = tbl.PresText,
PressTextS = tbl.PresTextS,
PresCategory = tbl.PresCategory,
SpecCharExists = tbl.SpecCharExists,
ProductId = tbl.ProductId,
TableStatus = tbl.TableStatus,
Theme = tbl.SubjectCode,
TimeScaleId = tbl.TimeScale,
ContentVariableEnglish = tbl.MainTable_Eng.ContentsVariable,
TableTitleEnglish = tbl.MainTable_Eng.PresText,
EnglishPublished = tbl.MainTable_Eng.Published,
EnglishStatus = tbl.MainTable_Eng.Status,
IsNew = false,
IsDirty = false
}).FirstOrDefault();
if (table != null)
{
var contents = (from cont in context.Contents
where cont.MainTable == mainTable
select new PxContent()
{
Content = cont.Contents,
AggregPossible = cont.AggregPossible,
BasePeriod = cont.BasePeriod,
CFPrices = cont.CFPrices,
Copyright = cont.Copyright,
DayAdj = cont.DayAdj,
PresCellsZero = cont.PresCellsZero,
PresDecimals = cont.PresDecimals,
PresMissingLine = cont.PresMissingLine,
PressTextS = cont.PresTextS,
PresText = cont.PresText,
Producer = cont.Producer,
RefPeriod = cont.RefPeriod,
SeasAdj = cont.SeasAdj,
StatAuthority = cont.StatAuthority,
StockFA = cont.StockFA,
StoreColumnNo = cont.StoreColumnNo,
StoreDecimals = cont.StoreDecimals,
StoreFormat = cont.StoreFormat,
StoreNoChar = cont.StoreNoChar,
Unit = cont.Unit,
IsNew = false,
IsDirty = false,
PressTextEnglish = cont.Contents_Eng.PresText,
PressTextEnglishS = cont.Contents_Eng.PresTextS,
UnitEnglish = cont.Contents_Eng.Unit,
RefPeriodEnglish = cont.Contents_Eng.RefPeriod,
BasePeriodEnglish = cont.Contents_Eng.BasePeriod
}).ToList();
foreach (var content in contents)
{
content.MainTable = table;
table.Contents.Add(content);
var timePeriods = (from tp in context.ContentsTimes
where tp.Contents == content.Content && tp.MainTable == mainTable
select new PxTime() { TimePeriod = tp.TimePeriod, IsNew = false, IsDirty = false }).ToList();
foreach (var timePeriod in timePeriods)
{
timePeriod.Content = content;
timePeriod.IsNew = false;
timePeriod.IsDirty = false;
content.TimePeriods.Add(timePeriod);
}
var contentFootnotes = (from f in context.FootnoteContents
where f.MainTable == mainTable && f.Contents == content.Content
select new PxContentFootnote()
{
FootnoteNo = (int)f.FootnoteNo,
FootnoteType = f.Footnote.FootnoteType,
ShowFootnote = f.Footnote.ShowFootnote,
MandOption = f.Footnote.MandOpt,
FootnoteText = f.Footnote.FootnoteText,
FootnoteTextEnglish = f.Footnote.Footnote_Eng.FootnoteText,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var contentFootnote in contentFootnotes)
{
contentFootnote.MainTable = table;
contentFootnote.Content = content;
content.IsNew = false;
content.IsDirty = false;
content.Footnotes.Add(contentFootnote);
}
}
var variables = (from vb in context.SubTableVariables
where vb.MainTable == mainTable && vb.SubTable == "1"
orderby vb.StoreColumnNo
select new PxVariable()
{
PresText = vb.Variable1.PresText,
PresTextEnglish = vb.Variable1.Variable_Eng.PresText,
VariableInfo = vb.Variable1.VariableInfo,
ValueSet = vb.ValueSet,
VariableType = vb.VariableType,
StoreColumnNo = vb.StoreColumnNo,
Variable = vb.Variable,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var variable in variables)
{
variable.MainTable = table;
table.Variables.Add(variable);
var variableFootnotes = (from vf in context.FootnoteVariables
where vf.Variable == variable.Variable
select new PxVariableFootnote()
{
FootnoteNo = (int)vf.FootnoteNo,
FootnoteType = vf.Footnote.FootnoteType,
ShowFootnote = vf.Footnote.ShowFootnote,
MandOption = vf.Footnote.MandOpt,
FootnoteText = vf.Footnote.FootnoteText,
FootnoteTextEnglish = vf.Footnote.Footnote_Eng.FootnoteText,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var variableFootnote in variableFootnotes)
{
variableFootnote.Variable = variable;
variableFootnote.IsDirty = false;
variableFootnote.IsNew = false;
variable.VariableFootnotes.Add(variableFootnote);
}
}
foreach (var content in contents)
{
foreach (var variable in variables)
{
var contentVariableFootnotes = (from cvf in context.FootnoteContVbls
where cvf.MainTable == mainTable && cvf.Contents == content.Content && cvf.Variable == variable.Variable
select new PxContentVariableFootnote()
{
FootnoteNo = (int)cvf.FootnoteNo,
FootnoteType = cvf.Footnote.FootnoteType,
ShowFootnote = cvf.Footnote.ShowFootnote,
MandOption = cvf.Footnote.MandOpt,
FootnoteText = cvf.Footnote.FootnoteText,
FootnoteTextEnglish = cvf.Footnote.Footnote_Eng.FootnoteText,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var contentVariableFootnote in contentVariableFootnotes)
{
contentVariableFootnote.MainTable = table;
contentVariableFootnote.Content = content;
contentVariableFootnote.Variable = variable;
contentVariableFootnote.IsDirty = false;
contentVariableFootnote.IsNew = false;
content.ContentVariableFootnotes.Add(contentVariableFootnote);
}
var valuesOfVariable = GetValuesByValueset(variable.ValueSet);
foreach (var value in valuesOfVariable)
{
var contentValueFootnotes = (from cvf in context.FootnoteContValues
where cvf.MainTable == mainTable && cvf.Contents == content.Content && cvf.Variable == variable.Variable && cvf.ValuePool == value.ValuePool && cvf.ValueCode == value.ValueCode
select new PxContentValueFootnote()
{
FootnoteNo = (int)cvf.FootnoteNo,
FootnoteType = cvf.Footnote.FootnoteType,
ShowFootnote = cvf.Footnote.ShowFootnote,
MandOption = cvf.Footnote.MandOpt,
FootnoteText = cvf.Footnote.FootnoteText,
FootnoteTextEnglish = cvf.Footnote.Footnote_Eng.FootnoteText,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var contentValueFootnote in contentValueFootnotes)
{
contentValueFootnote.MainTable = table;
contentValueFootnote.Content = content;
contentValueFootnote.Variable = variable;
contentValueFootnote.Value = value;
contentValueFootnote.IsDirty = false;
contentValueFootnote.IsNew = false;
content.ContentValueFootnotes.Add(contentValueFootnote);
}
}
}
foreach (var variable in variables)
{
var valuesOfVariable = GetValuesByValueset(variable.ValueSet);
foreach (var value in valuesOfVariable)
{
var mainTableValueFootnotes = (from mtf in context.FootnoteMaintValues
where mtf.MainTable == mainTable && mtf.Variable == variable.Variable && mtf.ValuePool == value.ValuePool && mtf.ValueCode == value.ValueCode
select new PxMainTableValueFootnote()
{
FootnoteNo = (int)mtf.FootnoteNo,
FootnoteType = mtf.Footnote.FootnoteType,
ShowFootnote = mtf.Footnote.ShowFootnote,
MandOption = mtf.Footnote.MandOpt,
FootnoteText = mtf.Footnote.FootnoteText,
FootnoteTextEnglish = mtf.Footnote.Footnote_Eng.FootnoteText,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var maintableValueFootnote in mainTableValueFootnotes)
{
maintableValueFootnote.MainTable = table;
maintableValueFootnote.Variable = variable;
maintableValueFootnote.Value = value;
maintableValueFootnote.IsDirty = false;
maintableValueFootnote.IsNew = false;
table.MainTableValueFootnotes.Add(maintableValueFootnote);
}
}
}
}
var footnotes = (from f in context.FootnoteMainTables
where f.MainTable == mainTable
select new PxMainTableFootnote()
{
FootnoteNo = (int)f.FootnoteNo,
FootnoteType = f.Footnote.FootnoteType,
ShowFootnote = f.Footnote.ShowFootnote,
MandOption = f.Footnote.MandOpt,
FootnoteText = f.Footnote.FootnoteText,
FootnoteTextEnglish = f.Footnote.Footnote_Eng.FootnoteText,
IsNew = false,
IsDirty = false
}).ToList();
foreach (var footnote in footnotes)
{
footnote.MainTable = table;
footnote.IsNew = false;
footnote.IsDirty = false;
table.Footnotes.Add(footnote);
}
return table;
}
else
{
return null;
}
}
public static bool VariableExisit(string variableName)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = from v in context.Variables
where v.Variable1 == variableName
select new { ValueCode = v.Variable1 };
return valueList.ToList().Count == 1;
}
public static string GetValuepoolByValueSet(string valueset)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valuePool = (from vp in context.ValueSets
where vp.ValueSet1 == valueset
select vp.ValuePool).ToList();
if (valuePool.Count != 0)
{
return valuePool.First();
}
return null;
}
public static bool UpdateMenuSelection(PxMenuSelection selection)
{
try
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var selectedMenu = from sm in context.MenuSelections
where sm.Menu == selection.Parent.Menu && sm.Selection == selection.Menu
select sm;
PxMetaModel.MenuSelection ms = selectedMenu.First();
ms.PresText = selection.PresText;
ms.PresTextS = selection.PresTextS;
ms.Presentation = selection.Presentation;
ms.Description = selection.Description;
ms.SortCode = selection.SortCode;
ms.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
ms.LogDate = DateTime.Now;
var selectedMenuEng = from sm in context.MenuSelection_Eng
where sm.Menu == selection.Parent.Menu && sm.Selection == selection.Menu
select sm;
PxMetaModel.MenuSelection_Eng msEng = selectedMenuEng.First();
msEng.PresText = selection.PresTextEnglish;
msEng.PresTextS = selection.PresTextSEnglish;
msEng.Presentation = selection.PresentationEnglish;
msEng.Description = selection.DescriptionEnglish;
msEng.SortCode = selection.SortCodeEnglish;
msEng.UserId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
msEng.LogDate = DateTime.Now;
context.SaveChanges();
selection.MarkAsOld();
}
catch (Exception)
{
return false;
}
return true;
}
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;
}
public static bool Delete(PxObject obj, ref string message)
{
try
{
if (obj == null) return true;
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
obj.DeleteEntities(context);
context.SaveChanges();
}
catch (Exception ex)
{
//TODO chage the exception type to EF Exception and look for an inner exception.
if (ex.InnerException == null)
{
message = ex.Message;
}
else
{
message = ex.InnerException.Message;
}
return false;
}
return true;
}
public static void LoadChildren(PxMenuSelection parent)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var mythemes = (from theme in context.MenuSelections
where theme.Menu == parent.Menu
orderby theme.SortCode
select new PxMenuSelection()
{
Menu = theme.Selection,
PresText = theme.PresText,
LevelNo = theme.LevelNo,
Description = theme.Description,
Presentation = theme.Presentation,
PresTextS = theme.PresTextS,
SortCode = theme.SortCode,
PresTextEnglish = theme.MenuSelection_Eng.PresText,
PresTextSEnglish = theme.MenuSelection_Eng.PresTextS,
DescriptionEnglish = theme.MenuSelection_Eng.Description,
PresentationEnglish = theme.MenuSelection_Eng.Presentation,
SortCodeEnglish = theme.MenuSelection_Eng.SortCode
});
foreach (var item in mythemes)
{
item.Parent = parent;
parent.Childrens.Add(item);
}
}
public static Model.PxValueSet GetValueSet(string valueSet)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var varList = from v in context.ValueSets
where v.ValueSet1 == valueSet
select new Model.PxValueSet() { Valueset = v.ValueSet1, PresText = v.PresText, Elimination = v.Elimination, ValuePool = v.ValuePool, ValuePres = v.ValuePres, PresTextEnglish = v.ValueSet_Eng.PresText };
if (varList.Count() != 0)
{
return varList.First();
}
return null;
}
public static List<Option> GetValuesetsByValuePool(string valuePool)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var poolList = from v in context.ValueSets
where v.ValuePool == valuePool
select new Option() { Code = v.ValueSet1, Text = v.PresText };
return poolList.ToList();
}
public static List<PxValueSet> GetValuesetsByValuePoolPxValueset(string valuePool)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valuesetslList = (from v in context.ValueSets
where v.ValuePool == valuePool
select new PxValueSet() {
Valueset = v.ValueSet1,
PresText = v.PresText,
ValuePool = v.ValuePool,
Elimination = v.Elimination,
PresTextEnglish=v.ValueSet_Eng.PresText,
ValuePres=v.ValuePres,
IsNew = false,
IsDirty = false }).ToList();
return valuesetslList;
}
public static List<Option> GetValuesListByValueset(string valueSet)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = from v in context.VSValues
where v.ValueSet == valueSet
select new Option() { Code = v.ValueCode, Text = v.Value.ValueTextL };
return valueList.ToList();
}
public static Model.PxVariable GetVarible(string varId)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var varList = from v in context.Variables
where v.Variable1 == varId
select new Model.PxVariable() { Variable = v.Variable1, PresText = v.PresText, VariableInfo = v.VariableInfo, PresTextEnglish = v.Variable_Eng.PresText, IsNew = false };
if (varList.Count() > 0)
{
return varList.First();
}
return null;
}
//public static bool CreateEntities(PxObject obj, ref string message)
//{
// try
// {
// PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
// obj.CreateEntities(context);
// context.SaveChanges();
// obj.MarkAsOld();
// }
// catch (Exception ex)
// {
// //TODO chage the exception type to EF Exception and look for an inner exception.
// message = ex.Message;
// return false;
// }
// return true;
//}
public static List<Model.PxValue> GetValuesByValuePool(string valuepool)
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var valueList = from v in context.Values
where v.ValuePool == valuepool
select new Model.PxValue() { ValueCode = v.ValueCode, ValueText = v.ValueTextL, ValueTextEnglish = v.Value_Eng.ValuetextL, IsNew = false };
return valueList.ToList();
}
public static List<Option> GetValuePools()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var poolList = from v in context.ValuePools
select new Option() { Code = v.ValuePool1, Text = v.PresText };
return poolList.ToList();
}
public static List<Option> GetVariableList()
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
var varList = from v in context.Variables
select new Option() { Code = v.Variable1, Text = v.PresText };
return varList.ToList();
}
public static bool Save(PxObject obj, ref string message)
{
try
{
PxMetaModel.PcAxisMetabaseEntities context = new PxMetaModel.PcAxisMetabaseEntities();
obj.Save(context);
context.SaveChanges();
obj.MarkAsOld();
}
catch (Exception ex)
{
//TODO chage the exception type to EF Exception and look for an inner exception.
if (string.IsNullOrEmpty(ex.InnerException.Message))
{
message = ex.Message;
}
else
{
message = ex.InnerException.Message;
}
return false;
}
return true;
}
