/// <summary>
/// Returns all available operations with their sql templates
/// </summary>
/// <param name="confiFilePath">path to the excel configuration file for operations and templates</param>
/// <returns></returns>
private List <Operations> GetOperationFromConfig(string confiFilePath)
{
List <Operations> curentOpers = new List <Operations>();
if (!File.Exists(configurationFolderPath + templateConfigFileName))
{
throw new FileNotFoundException("File not found in App Configuration directory" + configurationFolderPath, templateConfigFileName);
}
DataTable currentData = ReadExcelFile.getExcellToDtbl(configurationFolderPath + templateConfigFileName);
for (int i = 0; i < currentData.Rows.Count; i++)
{
if (!File.Exists(configurationSQLPath + currentData.Rows[i][0].ToString()))
{
throw new FileNotFoundException("File not found in App SQL directory" + configurationSQLPath, currentData.Rows[i][0].ToString());
}
Operations currentOper = new Operations(
currentData.Rows[i][0].ToString()
, (TableData.Operation)Enum.Parse(typeof(TableData.Operation), currentData.Rows[i][1].ToString())
, currentData.Rows[i][2].ToString()
, currentData.Rows[i][3].ToString()
);
curentOpers.Add(currentOper);
}
return(curentOpers);
}
static void Main(string[] args)
{
ExportAccessTabletoExcel.ExportQuery(@"C:\Projects\CHIA\Cerner\Cerner_Multum_en-US_249_180601.accdb", "drc_gestational_age_non_continuous_infusion", @"C:\Projects\CHIA\drc_gestational_age_non_continuous_infusion.xlsx");
ReadExcelFileReturnList GetGetExcel = new ReadExcelFileReturnList();
GetGetExcel.getExcelFile(@"C:\Projects\CHIA\drc_gestational_age_non_continuous_infusion.xlsx", 1);
//List<string> ExcelList = new List<string>();
//ReadExcelFile GetExcel = new ReadExcelFile(@"C:\Projects\CHIA\Copyof20180513_drc_age2_updates_US_CA_v249.xlsx");
ReadExcelFile GetExcel = new ReadExcelFile();
//ReadExcelFile GetExcel = new ReadExcelFile();
//GetExcel.ExcelList = ReadExcelFile.getExcelFile();
List <string> ExcelList = GetExcel.getExcelFile(@"C:\Projects\CHIA\Copyof20180513_drc_age2_updates_US_CA_v251.xlsx", 3); //Pass in Excel path and string
//List<string> ExcelList2 = GetExcel2.getExcelFile(@"C:\Projects\CHIA\Copyof20180513_drc_age2_updates_US_CA_v251.xlsx", 3);
List <string> ExcelList2 = GetExcel.getExcelFile(@"C:\Projects\CHIA\drc_gestational_age_non_continuous_infusion.xlsx", 1);
CompareList CompareTwoExcelLists = new CompareList();
//List<string> ReturnLists = CompareTwoLists.Contains(ExcelList, DataList);
IEnumerable <string> ReturnDiffLists = CompareTwoExcelLists.Contains(ExcelList, ExcelList2);
PrintToFile.Print(ExcelList);
PrintToFile.Print(ExcelList2);
PrintToFile.Print(ReturnDiffLists);
//ConsoleWriteLineList.DumpExcelSet(ExcelList);
// Load an Access ACCDB file
DataSet ds1 = AccessDbLoader.LoadFromFile(@"C:\Projects\CHIA\Cerner\Cerner_Multum_en-US_249_180601.accdb");
//DataSet ds1 = AccessDbLoader.LoadFromFile(@"C:\Projects\CHIA\Cerner\Cerner_Multum_en-CA_249_180601.accdb");
//DataSet ds1 = AccessDbLoader.LoadFromFile(@"C:\Projects\CHIA\Lexi\Cerner_LexiComp_en-US_249_180601.accdb");
List <string> DataList = DataSetToList.DumpDataSet(ds1);
//DumpDataSetToConsole.DumpDataSet(ds1);
//var firstNotSecond = ExcelList.Except(DataList).ToList();
//var secondNotFirst = DataList.Except(ExcelList).ToList();
CompareList CompareTwoLists = new CompareList();
//List<string> ReturnLists = CompareTwoLists.Contains(ExcelList, DataList);
IEnumerable <string> ReturnLists = CompareTwoLists.Contains(ExcelList, DataList);
PrintToFile.Print(ExcelList);
PrintToFile.Print(DataList);
PrintToFile.Print(ReturnLists);
//ConsoleWriteLineList.DumpExcelSet(ReturnLists);
Console.ReadLine();
}
static void Main(string[] args)
{
List <SimpleExcelModel> simpleExcelModelList = new List <SimpleExcelModel>();
// 엑셀파일 읽기
ReadExcelFile readExcelFile = new ReadExcelFile();
simpleExcelModelList = readExcelFile.ReadExcelFileValues();
// 위에서 읽은 엑셀파일의 텍스트 값을 가지고 그대로 새 엑셀파일을 만들어서 뿌려주기
MakeExcelFiles makeExcelFiles = new MakeExcelFiles();
makeExcelFiles.MakeDetailsAsExcel(simpleExcelModelList);
}
protected void btnUploadExcel_Click(object sender, EventArgs e)
{
try
{
if (excelUpload.HasFile)
{
extension = String.Empty;
extension = excelUpload.FileName.Substring(excelUpload.FileName.LastIndexOf("."));
string FileName = excelUpload.FileName;
excelUpload.SaveAs(HttpContext.Current.Server.MapPath("excelfile/" + FileName));
StateData sdata = new StateData();
dt = ReadExcelFile.ReadAsDataTable(FileName);
for (int i = 0; i < dt.Rows.Count; i++)
{
sdata.Name = dt.Rows[i][0].ToString();
sdata.Save();
}
}
}
catch (Exception ex)
{
}
}
protected void btnUploadCity_Click(object sender, EventArgs e)
{
try
{
if (FileUploadCity.HasFile)
{
extension = String.Empty;
extension = FileUploadCity.FileName.Substring(FileUploadCity.FileName.LastIndexOf("."));
string FileName = FileUploadCity.FileName;
FileUploadCity.SaveAs(HttpContext.Current.Server.MapPath("excelfile/" + FileName));
CityData cdata = new CityData();
dt = ReadExcelFile.ReadAsDataTable(FileName);
for (int i = 0; i < dt.Rows.Count; i++)
{
cdata.StateId = int.Parse(dt.Rows[i][0].ToString());
cdata.Name = dt.Rows[i][1].ToString();
cdata.Save();
}
}
}
catch (Exception ex)
{
}
}
//changeit to work with Wpf
public bool DrawGroupBoxElements(string confFilePath)
{
DataTable formatConfiguration = ReadExcelFile.GetExcellToDtbl(confFilePath);
Point previousPoint = new Point(6, 0);
Point firstRow = new Point(6, 0);
int maxY = 407;
int controlHeight = 25;
int currentCol = 1;
int columnMaxLen = 0;
foreach (DataRow dr in formatConfiguration.Rows)
{
if (dr[3].ToString().ToUpper() == "TRUE OR FALSE")
{
CheckBox myCb = new CheckBox();
myCb.Name = dr[1].ToString();
myCb.Content = dr[1].ToString();
myCb.BackColor = Color.Black;
myCb.ForeColor = Color.MediumTurquoise;
if (((previousPoint.Y + controlHeight - 5) < maxY) && currentCol == 1)
{
previousPoint.X = previousPoint.X;
previousPoint.Y = previousPoint.Y + controlHeight;
myCb.Location = previousPoint;
}
else
{
previousPoint.X = firstRow.X + currentCol * 10 + 260;
previousPoint.Y = firstRow.Y + controlHeight;
myCb.Location = previousPoint;
}
if (int.Parse(dr[0].ToString()) == 1)
{
myCb.IsChecked = true;
}
else
{
myCb.IsChecked = false;
}
this.gbFormatSettings.Controls.Add(myCb);
}
else if (dr[3].ToString().ToUpper() == "SqlVersion".ToUpper() || dr[3].ToString().ToUpper() == "KeywordCasing".ToUpper())
{
Label myComboLable = new Label();
myComboLable.Text = dr[1].ToString();
ComboBox myCombo = new ComboBox();
myCombo.Name = dr[1].ToString();
myCombo.AutoSize = true;
myCombo.DropDownStyle = ComboBoxStyle.DropDownList;
myCombo.BackColor = Color.Black;
myCombo.ForeColor = Color.MediumTurquoise;
if (dr[3].ToString().ToUpper() == "SqlVersion".ToUpper())
{
myCombo.Items.Add("SQL 2005");
myCombo.Items.Add("SQL 2000");
myCombo.Items.Add("SQL 2008");
myCombo.Items.Add("SQL 2012");
myCombo.Items.Add("SQL 2014");
myCombo.Items.Add("SQL 2016");
}
else
{
myCombo.Items.Add("Lowercase");
myCombo.Items.Add("Uppercase");
myCombo.Items.Add("PascalCase");
}
if (drawFromFile)
{
myCombo.SelectedIndex = int.Parse(dr[0].ToString());
}
if (((previousPoint.Y + controlHeight - 5) < maxY) && currentCol == 1)
{
previousPoint.X = previousPoint.X;
previousPoint.Y = previousPoint.Y + controlHeight;
myComboLable.Location = previousPoint;
myCombo.Location = new Point(previousPoint.X, previousPoint.Y + 25);
previousPoint.Y = previousPoint.Y + 25;
}
else
{
previousPoint.X = firstRow.X + currentCol * 10 + 260;
previousPoint.Y = firstRow.Y + controlHeight;
myComboLable.Location = previousPoint;
myCombo.Location = new Point(previousPoint.X, previousPoint.Y + 25);
previousPoint.Y = firstRow.Y + 25;
}
this.gbFormatSettings.Controls.Add(myComboLable);
this.gbFormatSettings.Controls.Add(myCombo);
}
else if (dr[3].ToString().ToUpper() == "Indent".ToUpper())
{
Label myTextLable = new Label();
myTextLable.Text = dr[1].ToString();
TextBox mytext = new TextBox();
mytext.Name = dr[1].ToString();
mytext.BackColor = Color.Black;
mytext.ForeColor = Color.MediumTurquoise;
if (drawFromFile)
{
mytext.Text = dr[0].ToString();
}
mytext.AutoSize = true;
if (((previousPoint.Y + controlHeight - 5) < maxY) && currentCol == 1)
{
previousPoint.X = previousPoint.X;
previousPoint.Y = previousPoint.Y + controlHeight;
myTextLable.Location = previousPoint;
mytext.Location = new Point(previousPoint.X, previousPoint.Y + 25);
previousPoint.Y = previousPoint.Y + 25;
}
else
{
previousPoint.X = firstRow.X + currentCol * 10 + 260;
previousPoint.Y = firstRow.Y + controlHeight;
myTextLable.Location = previousPoint;
mytext.Location = new Point(previousPoint.X, previousPoint.Y + 25);
previousPoint.Y = firstRow.Y + 25;
}
this.gbFormatSettings.Controls.Add(myTextLable);
this.gbFormatSettings.Controls.Add(mytext);
}
}
System.Diagnostics.Debug.WriteLine(columnMaxLen);
return(true);
}
/// <summary>
/// Fill the Database Data based on Information that`s extracted of SQL Server Selected Database
/// in the Interface of the app
/// </summary>
/// <param name="customColumnsFileName"> Full path file for CustomMappings.xlsx file </param>
private void GetDBData(string customColumnsFileName)
{
dbData.DatabaseName = currentDB;
List <ManagedColumns> customInsertColumns = new List <ManagedColumns>();
List <ManagedColumns> customUpdateColumns = new List <ManagedColumns>();
List <ManagedColumns> customSelectColumns = new List <ManagedColumns>();
if (File.Exists(customColumnsFileName) && customColumns)
{
DataTable configTbl = ReadExcelFile.getExcellToDtbl(customColumnsFileName);
for (int i = 0; i < configTbl.Rows.Count; i++)
{
if (configTbl.Rows[i][0].ToString() != string.Empty)
{
ManagedColumns managedInsertColumns = new ManagedColumns();
managedInsertColumns.ColumnName = configTbl.Rows[i][0].ToString();
managedInsertColumns.ColumnValue = configTbl.Rows[i][1].ToString();
managedInsertColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][2].ToString()));
managedInsertColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][3].ToString()));
customInsertColumns.Add(managedInsertColumns);
ManagedColumns managedUpdateColumns = new ManagedColumns();
managedUpdateColumns.ColumnName = configTbl.Rows[i][0].ToString();
managedUpdateColumns.ColumnValue = configTbl.Rows[i][1].ToString();
managedUpdateColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][4].ToString()));
managedUpdateColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][5].ToString()));
customUpdateColumns.Add(managedUpdateColumns);
ManagedColumns managedSelectColumns = new ManagedColumns();
managedSelectColumns.ColumnName = configTbl.Rows[i][0].ToString();
managedSelectColumns.ColumnValue = configTbl.Rows[i][1].ToString();
managedSelectColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][6].ToString()));
managedSelectColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][7].ToString()));
customSelectColumns.Add(managedSelectColumns);
}
}
dbData.ManagedInsertColumns = customInsertColumns;
dbData.ManagedUpdateColumns = customUpdateColumns;
dbData.ManagedSelectColumns = customSelectColumns;
}
else if (customColumns)
{
MessageBox.Show("File CustomColumsMappings.xlsx does not exist!\nNot found in the Application Configuration dir!");
}
int startvalue = 1;
//1 RANK 2 tableName, 3 columnname, 4 primarikey chek, 5 columndatatype , 6 null , 7 IsFK
procGenData = sqlConn.GetProcTablesData(serverAddress, serverUser, serverUserPassword, currentDB, configurationFolderPath, columnsDataSql);
string currentPrimaryKey = string.Empty;
string currentPrimaryKeyDataType = string.Empty;
List <ColumnData> currentColumns = new List <ColumnData>();
for (int i = 0; i < procGenData.Rows.Count; i++)
{
if ((startvalue == int.Parse(procGenData.Rows[i][0].ToString()) - 1) || ((i + 1) == procGenData.Rows.Count))
{
TableData currentTbl = new TableData();
currentTbl.Columns = currentColumns;
currentTbl.TableName = procGenData.Rows[i - 1][1].ToString();
currentTbl.PrimaryKey = currentPrimaryKey;
currentTbl.PrimaryKeyDataType = currentPrimaryKeyDataType;
dbData.Tables.Add(currentTbl);
currentColumns = new List <ColumnData>();
startvalue = int.Parse(procGenData.Rows[i][0].ToString());
}
if (bool.Parse(procGenData.Rows[i][3].ToString()))
{
currentPrimaryKey = procGenData.Rows[i][2].ToString();
currentPrimaryKeyDataType = procGenData.Rows[i][4].ToString();
}
if (bool.Parse(procGenData.Rows[i][3].ToString()) == false)
{
ColumnData currentColumn = new ColumnData();
currentColumn.ColumnName = procGenData.Rows[i][2].ToString();
currentColumn.ColumnDataType = procGenData.Rows[i][4].ToString();
currentColumn.isNullable = bool.Parse(procGenData.Rows[i][5].ToString());
currentColumn.isFK = bool.Parse(procGenData.Rows[i][6].ToString());
currentColumns.Add(currentColumn);
}
}
}
