public ExcelModel GetXMLConfig(string xmlFilePath)
{
ExcelModel output = new ExcelModel();
JObject jObject = BI_Project.Helpers.Utility.JTokenHelper.GetXML2Jobject(xmlFilePath);
output.FolderUploadedDirectory = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.UploadedDirectory");
output.DBTableName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.database.tablename");
string strstartRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.@started_row");
output.ExcelStartRow = Int32.Parse(strstartRow);
string strnumberRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell.@number_row");
//number_row = Int32.Parse(strnumberRow);
string strnumberCell = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell.@number_cell");
//number_cell = Int32.Parse(strnumberCell);
output.ExcelSheetName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.#text");
output.FolderHelpDocumentPath = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.HelpDocumentPath");
output.LangNote = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.Note.#cdata-section");
output.FolderFileNativeName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.FileNativeName");
//LAY CAC COT TUONG UNG
IEnumerable <JToken> columns = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, "..mapping.column");
int index = 0;
foreach (JToken column in columns)
{
MappingExcelDB mappingExcel = new MappingExcelDB();
mappingExcel.ColumnName = column.SelectToken("..name").Value <string>();
mappingExcel.ExcelColumn = index;
mappingExcel.Datatype = column.SelectToken("..datatype").Value <string>();
output.DBLstColumns.Add(mappingExcel);
index++;
}
//LAY CAC THAM SO PARAMETERS
IEnumerable <JToken> paras = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, "..paras.para");
index = 0;
foreach (JToken para in paras)
{
//XMLParaModel
XMLParaModel paraXMLModel = new XMLParaModel();
paraXMLModel.Name = para.SelectToken("..name").Value <string>();
paraXMLModel.Row = para.SelectToken("..row").Value <Int32>();
//paraXMLModel.Column = para.SelectToken("..column").Value<Int32>();
paraXMLModel.DataType = para.SelectToken("..datatype").Value <string>();
paraXMLModel.Active = para.SelectToken("..active").Value <string>();
paraXMLModel.Value = para.SelectToken("..value").Value <string>();
output.XmlParas.Add(paraXMLModel.Name, paraXMLModel);
}
return(output);
}
public void ImportPLDatabase(int userid, string excelFilePath, int uploadRoleId, ExcelModel lstMapping, string excelFileName)
{
int index = 0, columnIndex = 0, count = 0; int year = 0;
//int index = 0, columnIndex = 0, count = 0; int year = 0;
string oleConnString = GetOleDbConnectionString(excelFilePath);
OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
OleDbCommand oleReadCommand = new OleDbCommand();
OracleTransaction transaction = null;
try
{
//int output = 0;
//string sqlInserPara2tDWValues = null;
//string sqlInsertPara2DW = null;
//string oleConnString = GetOleDbConnectionString(excelFilePath);
//OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
//OleDbCommand oleReadCommand = new OleDbCommand();
//OracleTransaction transaction = null;
//GET EXCEL DATA
string sqlOle = "select * from [" + lstMapping.ExcelSheetName + "$]";
oleDbConnection.Open();
oleReadCommand.Connection = oleDbConnection;
oleReadCommand.CommandText = sqlOle;
ConnectOracleDB.OpenDBConnect();
transaction = ConnectOracleDB.OracleDBConnect.BeginTransaction();
ConnectOracleDB.command.Transaction = transaction;
ConnectOracleDB.command.Parameters.Clear();
/**************************************************INSERT HISTORY**************************************/
this.DBConnection.OpenDBConnect();
int idHistory = 0;
this.InsertHistory(userid, uploadRoleId, lstMapping.FolderHelpDocumentPath, lstMapping.LangNote, lstMapping.FolderFileNativeName, excelFileName, ref idHistory);
this.DBConnection.CloseDBConnect();
string deleteRecordQuery = "DELETE FROM " + lstMapping.DBTableName + " WHERE ";
string sqlInsertDW = "insert into " + lstMapping.DBTableName + "(";
string sqlInsertDWValues = " values (";
bool isCommplateGenerateSQL = false;
using (OleDbDataReader dataReader = oleReadCommand.ExecuteReader())
{
while (dataReader.Read())
{
index++;
if (index == 1)
{
continue;
}
if (index == 2)
{
var madviqly = dataReader[0].ToString().Substring(0, 2);
var _month = dataReader[1].ToString();
var _year = dataReader[2].ToString();
deleteRecordQuery += "ma_dviqly like '%" + madviqly + "%'" + " " + "AND " + "THANG_BC " + "= " + _month + " " + "AND " + "NAM_BC " + "= " + _year;
this.ConnectOracleDB.command.CommandText = deleteRecordQuery;
this.ConnectOracleDB.command.ExecuteNonQuery();
}
if (index >= lstMapping.ExcelStartRow)
{
columnIndex = 0;
int countNullColumn = 0;
foreach (MappingExcelDB column in lstMapping.DBLstColumns)
{
//var test = dataReader.
columnIndex++;
column.Value = dataReader[column.ExcelColumn];
if (!isCommplateGenerateSQL)
{
sqlInsertDW += column.ColumnName + ",";
sqlInsertDWValues += ":" + column.ColumnName + ",";
}
try
{
if (column.Value.ToString().Trim() == "")
{
countNullColumn++;
}
}
catch (Exception)
{
countNullColumn++;
}
ConnectOracleDB.command.Parameters.Add(new OracleParameter(":" + column.ColumnName, column.Value ?? (object)DBNull.Value));
}
if (!isCommplateGenerateSQL)
{
sqlInsertDW = sqlInsertDW.Trim(',') + ")";
sqlInsertDWValues = sqlInsertDWValues.Trim(',') + ")";
isCommplateGenerateSQL = true;
}
//*****************************INSERT INTO DATABASE******************************************************
if (countNullColumn == lstMapping.DBLstColumns.Count())
{
break;
}
else
{
this.ConnectOracleDB.command.CommandText = sqlInsertDW + sqlInsertDWValues;
this.ConnectOracleDB.command.CommandType = CommandType.Text;
this.ConnectOracleDB.command.ExecuteNonQuery();
ConnectOracleDB.command.Parameters.Clear();
}
}
}
}
transaction.Commit();
UpdateHistory(idHistory, index);
}
catch (Exception ex)
{
this.ERROR = "tại dòng số : " + (index + 1).ToString() + ", Cột số : " + (columnIndex) + " " + ex.Message;;
}
finally
{
ConnectOracleDB.CloseDBConnect();
oleReadCommand.Dispose();
oleDbConnection.Close();
oleDbConnection.Dispose();
}
//return output;
}
