public ActionResult DisplayData()
{
using (ExcelSourceEntities db = new ExcelSourceEntities())
{
var userdata = db.ExcelSources.ToList();
var dataVms = new List <DataVm>();
foreach (var user in userdata)
{
var dataVm = new DataVm();
dataVm.FirstName = user.FirstName;
dataVm.LastName = user.LastName;
dataVm.EmailAddress = user.EmailAddress;
dataVms.Add(dataVm);
}
return(View(dataVms));
}
}
public DataVm GetColumn(int sqlConfigId, string sqlContent)
{
try
{
//tạo parameter ảo, tạm thời chưa dùng
var storeParam = _sqlStoreParamRepository.GetAll().Where(x => x.SqlConfigId == sqlConfigId).ToList();
var storeParamMap = ObjectMapper.Map <List <SqlStoreParamDto> >(storeParam);
//(from c in _context.ConfigReportFilter
// where c.ReportId == reportid && c.IsDelete == false
// select c).ToList();
List <Parameter> param = new List <Parameter>();
var config = _sqlConfigRepository.FirstOrDefault(sqlConfigId);
if (sqlContent.IsNullOrEmpty())
{
sqlContent = config.SqlContent;
}
////var config = (from c in _context.ConfigReport
//// where c.Id == reportid
//// select c).SingleOrDefault();
//long datasourceId = config.DataSourceId;
// dataSource = _context.ConfigReportDataSource.SingleOrDefault(m => m.Id == datasourceId);
//string sqlType = dataSource.SqlType;
//Bảo cmt, param của store. tạm thời chưa dùng
if (storeParamMap.Count > 0)
{
foreach (SqlStoreParamDto element in storeParamMap)
{
Parameter a = new Parameter("", null);
//tạo danh sách param test
if (element.ValueString != null)
{
a.Varible = "@" + element.Code;
a.Value = element.ValueString;
param.Add(a);
}
else
{
a.Varible = "@" + element.Code;
a.Value = DBNull.Value;
param.Add(a);
}
}
}
//Get danh sách report display hiện có
var reportDisplayOld = _sqlConfigDetailRepository.GetAll().Where(x => x.SqlConfigId == sqlConfigId).ToList();
var reportDisplayOldMap = ObjectMapper.Map <List <SqlConfigDetailDto> >(reportDisplayOld);
//(from c in _context.ConfigReportDisplay
// where c.ReportId == reportid && c.IsDelete == false
// select c).ToList();
//get danh sách cột mới theo câu sql
var items = new List <Dictionary <string, object> >();
using (var conn = new SqlConnection(connectionString))
if (config.IsRawQuery == true)
{
using (var command = new SqlCommand(sqlContent, conn)
{
CommandType = CommandType.Text
})
{
conn.Open();
if (param != null)
{
foreach (Parameter _param in param)
{
SqlParameter iparam = new SqlParameter();
iparam.ParameterName = _param.Varible;
iparam.Value = _param.Value;
command.Parameters.Add(iparam);
}
}
//command.Parameters.Add(new SqlParameter("@"+param.Varible.ToString(), param.Value));
IDataReader reader = command.ExecuteReader();
var table = reader.GetSchemaTable();
foreach (DataRow column in table.Rows)
{
var item = new Dictionary <string, object>(reader.FieldCount);
item["col"] = column.ItemArray[0];
items.Add(item);
}
reader.Close();
conn.Close();
}
}
else
{
using (var command = new SqlCommand(sqlContent, conn)
{
CommandType = CommandType.StoredProcedure
})
{
conn.Open();
if (param != null)
{
foreach (Parameter _param in param)
{
SqlParameter iparam = new SqlParameter();
iparam.ParameterName = _param.Varible;
iparam.Value = _param.Value;
command.Parameters.Add(iparam);
}
}
//command.Parameters.Add(new SqlParameter("@"+param.Varible.ToString(), param.Value));
IDataReader reader = command.ExecuteReader();
var table = reader.GetSchemaTable();
foreach (DataRow column in table.Rows)
{
var item = new Dictionary <string, object>(reader.FieldCount);
item["col"] = column.ItemArray[0];
items.Add(item);
}
reader.Close();
conn.Close();
}
}
//so sánh danh sách col cũ và mới giữ lại những col trùng và xóa đi những col không trùng
List <SqlConfigDetailDto> reportDisplayNew = new List <SqlConfigDetailDto>();
foreach (SqlConfigDetailDto temp in reportDisplayOldMap)
{
bool key = false;
foreach (var a in items)
{
if (temp.Code == a["col"].ToString())
{
key = true;
}
}
if (key == true)
{
reportDisplayNew.Add(temp);
}
}
foreach (var a in items)
{
bool key = false;
foreach (SqlConfigDetailDto temp in reportDisplayOldMap)
{
if (temp.Code == a["col"].ToString())
{
key = true;
}
}
if (key == false)
{
SqlConfigDetailDto reportDisplay = new SqlConfigDetailDto();
reportDisplay.Code = a["col"].ToString();
reportDisplay.GroupLevel = -1;
reportDisplay.Name = a["col"].ToString();
reportDisplay.IsDisplay = true;
reportDisplay.GroupSort = "";
reportDisplay.SqlConfigId = sqlConfigId;
reportDisplayNew.Add(reportDisplay);
}
}
return(DataVm.Success("Success", "Thành công", reportDisplayNew));
}
catch (Exception e)
{
return(DataVm.Fail("Error", "Thất bại"));
}
}
