// GET api/excel/5
public string Get(string id)
{
try
{
if (id == "")
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại userid\"}]}");
}
var user = id;
general gn = new general();
dicAppSet = gn.ReadAppseting();
var lst = JObject.Parse(_dicPara_exnom["StrColumnNames" + user]).SelectToken("kq").ToString();
var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst);
if (dicAppSet == null || dicAppSet.Count == 0)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}");
}
var lstPara = gn.ParaConvertListPara(_dicPara_exnom["StrPara" + user]);
var dicConfig = gn.ConfigConvertToDicConfig(_dicPara_exnom["StrConfig" + user]);
if (lstPara == null && dicConfig == null)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}");
}
_dicPara_exnom.Remove("StrPara" + user);
_dicPara_exnom.Remove("StrConfig" + user);
_dicPara_exnom.Remove("StrColumnNames" + user);
gnOracle orl = new gnOracle();
gnExcel exx = new gnExcel();
// var dtPara = orl.DecodeProceduePara(lstPara, dicConfigOk, dicAppSet);
var dtPara = orl.DecodeProceduePara(lstPara, dicConfig, dicAppSet);
var lstPrOrl = orl.dicParaToOraclePara(dtPara == null || dtPara.Tables.Count == 0 ? null : dtPara.Tables[0], lstPara);
var ds = orl.ExcuteReturnDataSet(dicConfig["namesql"], (lstPrOrl == null ?null: lstPrOrl), CommandType.StoredProcedure, dicAppSet[dicConfig["connstr"].ToLower()]);
if (ds == null || ds.Tables.Count == 0)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Không có dữ liệu xuất excel\"}]}");
}
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
ds.Tables[0].Columns[i].ColumnName = ds.Tables[0].Columns[i].ColumnName.ToLower();
}
exx.ExporttoExcel(new List <string>(), ds.Tables[0], LstColumnNames, dicConfig.ContainsKey("namefile") ? dicConfig["namefile"] : "" + DateTime.Now.ToString("dd-MM-yyyy"), 1, false, true);
return("");
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
}
// GET api/ExcelExportOracle/5
public string Get(string idrandom)
{
general gn = new general();
var dicAppSet = gn.ReadAppseting();
var lst = JObject.Parse(_dicPara_exoracle["StrColumnNames" + idrandom]).SelectToken("kq").ToString();
var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst);
// lay du lieu
gnOracle or = new gnOracle();
if (dicAppSet == null || dicAppSet.Count == 0)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}");
}
var dicPara = gn.ParaConvertDicPara(_dicPara_exoracle["StrPara" + idrandom]);
var dicConfig = gn.ConfigConvertToDicConfig(_dicPara_exoracle["StrConfig" + idrandom]);
_dicPara_exoracle.Remove("StrPara" + idrandom);
_dicPara_exoracle.Remove("StrConfig" + idrandom);
_dicPara_exoracle.Remove("StrColumnNames" + idrandom);
if (dicPara == null && dicConfig == null)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}");
}
var dsParaOnSql = or.DecodeProceduePara_New(dicConfig, dicAppSet);
var paraOk = or.dicParaToOraclePara_New(dsParaOnSql, dicPara);
var ds = or.ExcuteReturnDataSet(dicConfig["namesql"], paraOk.ToList(), CommandType.StoredProcedure, dicAppSet[dicConfig["connstr"].ToLower()]);
if (ds == null || ds.Tables.Count == 0)
{
return("");
}
var ds1 = gn.convertDatasetToLower(ds);
gnExcel exx = new gnExcel();
var idtable = 0;
if (dicConfig.ContainsKey("exporttable"))
{
idtable = Convert.ToInt32(dicConfig["exporttable"]);
}
exx.ExporttoExcel(new List <string>(), ds1.Tables[idtable], LstColumnNames, dicConfig.ContainsKey("namefile") ? dicConfig["namefile"] : "" + DateTime.Now.ToString("dd-MM-yyyy"), 1, false, true);
return("");
}
// GET api/excelsqlexcute/5
public string Get(string id)
{
try
{
if (id == null || id == "undefind" || id == "")
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại userid\"}]}");
}
var user = id;
general gn = new general();
dicAppSet = gn.ReadAppseting();
var lst = JObject.Parse(_dicPara_exsql["StrColumnNames_SqlExcute" + user]).SelectToken("kq").ToString();
var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst);
if (dicAppSet == null || dicAppSet.Count == 0)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}");
}
gnSqlNomal gns = new gnSqlNomal();
var dicConfig = gns.convertConfigToDic(_dicPara_exsql["StrConfig_SqlExcute" + user]);
var dicPara = gns.convertParaToDic(_dicPara_exsql["StrPara_SqlExcute" + user]);
_dicPara_exsql.Remove("StrPara_SqlExcute" + user);
_dicPara_exsql.Remove("StrConfig_SqlExcute" + user);
_dicPara_exsql.Remove("StrColumnNames_SqlExcute" + user);
gnSql sql = new gnSql();
gnExcel exx = new gnExcel();
var paraSql = gn.ConvertDicToSqlPara(dicPara);
var ds = sql.DynamicSelectDataset(dicConfig["namesql"], (paraSql == null ?null: paraSql), CommandType.StoredProcedure, dicAppSet[dicConfig["connstr"].ToLower()]);
if (ds == null || ds.Tables.Count == 0)
{
return("{\"result\":\"OK\",\"data\":[{\"status\":\"Không có dữ liệu xuất excel\"}]}");
}
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
ds.Tables[0].Columns[i].ColumnName = ds.Tables[0].Columns[i].ColumnName.ToLower();
}
exx.ExporttoExcel(new List <string>(), ds.Tables[0], LstColumnNames, dicConfig.ContainsKey("namefile") ? dicConfig["namefile"] : "" + DateTime.Now.ToString("dd-MM-yyyy"), 1, false, true);
return("");
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
}
// GET api/ExcelExportTable/5
public string Get(string id)
{
try
{
if (id == "")
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại userid\"}]}");
}
var user = id;
general gn = new general();
dicAppSet = gn.ReadAppseting();
var lst = JObject.Parse(_dicPara_table["StrTableColumnNames" + user]).SelectToken("kq").ToString();
var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst);
var dicConfig = gn.ConfigConvertToDicConfig(_dicPara_table["StrTableConfig" + user]);
DataTable dt = null;
foreach (var val in JObject.Parse(_dicPara_table["StrTableData" + user]))
{
dt = gn.ConvertJsonToDataTable(val.Value.ToString(), val.Key);
}
if (dt == null)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}");
}
_dicPara_table.Remove("StrTableColumnNames" + user);
_dicPara_table.Remove("StrTableConfig" + user);
_dicPara_table.Remove("StrTableData" + user);
gnExcel exx = new gnExcel();
exx.ExporttoExcel(new List <string>(), dt, LstColumnNames, dicConfig.ContainsKey("namefile") ? dicConfig["namefile"] : "" + DateTime.Now.ToString("dd-MM-yyyy"), 1, false, true);
return("");
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
}
