private void CreateTables(bool onlycurruser)
{
try
{
string createTableSql = $"CREATE TABLE {OracleDbContext.OldTable} AS SELECT * FROM v$sql";
//Context.DB.ExecSqlStatement(createTableSql);
OracleDbHelper.ExecuteSql(createTableSql);
this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Create data table succeed.\r\n")));
string getDateSql = "SELECT to_char(SYSDATE,'yyyy/MM/dd hh24:mi:ss') AS currdate FROM dual";
//DataSet ds = Context.DB.ExecuteDataSet(getDateSql);
//if (ds != null) { this.StartOnText = Convert.ToString(ds.Tables[0].Rows[0]["currdate"]); }
string currdate = OracleDbHelper.GetSingle(getDateSql)?.ToString();
if (!string.IsNullOrEmpty(currdate))
{
this.StartOnText = currdate;
}
if (onlycurruser)
{
this.IsCurrUser = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "异常", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
private ActionResult Import(int start = 0)
{
Stopwatch sw = Stopwatch.StartNew();
try
{
// Seed VISIT records
OracleDbHelper.SeedVisitTable(db);
// Use ExcelImporter to import CP_ORDER table.
var excelImporter = new ExcelImporter(db);
excelImporter.Import(start);
// Use OracleDbHelper to import all other tables.
OracleDbHelper.PopulateDB <CP_VITAL_SIGN>(db);
OracleDbHelper.PopulateDB <CP_EXAM>(db);
OracleDbHelper.PopulateDB <CP_LAB_TEST>(db);
OracleDbHelper.PopulateDB <CP_MEDICAL_DOC>(db); // alone cost: 3.23801252166667 minutes.
//return ("Total cost: " + sw.Elapsed.TotalMinutes + "minutes. ");
return(RedirectToAction("Index"));
}
catch (Exception e)
{
return(RedirectToAction("Error", new{ message = e.Message }));
}
}
public ActionResult PreviewParameterModal(long id)
{
var parametes = _ParameterAppService.GetReportParametersByTemplate(new Abp.Application.Services.Dto.EntityDto {
Id = Convert.ToInt32(id)
}).Result;
StringBuilder html = new StringBuilder();
foreach (var item in parametes.Items)
{
if (item.UiType == (int)ReportDefine.UiTypes.日期型)
{
html.AppendFormat("<div class=\"form-group form-md-line-input no-hint\">");
html.AppendFormat("<input type=\"text\" name=\"{0}\" class=\"form-control date-picker\" required>", item.ParameterName);
html.AppendFormat("<label>{0}</label>", item.LabelName);
html.AppendFormat("</div>");
}
else if (item.UiType == (int)ReportDefine.UiTypes.日期时间型)
{
html.AppendFormat("<div class=\"form-group form-md-line-input no-hint\">");
html.AppendFormat("<input type=\"text\" name=\"{0}\" class=\"form-control date-time-picker\" required>", item.ParameterName);
html.AppendFormat("<label>{0}</label>", item.LabelName);
html.AppendFormat("</div>");
}
else if (item.UiType == (int)ReportDefine.UiTypes.拉框 &&
!string.IsNullOrWhiteSpace(item.DynamicDataSource) &&
!string.IsNullOrWhiteSpace(item.DynamicSql))
{
string conn = ConfigurationManager.ConnectionStrings[item.DynamicDataSource].ConnectionString;
var table = OracleDbHelper.ExecuteDataset(conn, item.DynamicSql, System.Data.CommandType.Text).Tables[0];
html.AppendFormat("<div class=\"form-group\">");
html.AppendFormat("<label for=\"{0}\">{1}</label>", item.ParameterName, item.LabelName);
html.AppendFormat("<select id=\"{0}\" name=\"{1}\" class=\"form-control bs-select\" data-live-search=\"true\">", item.ParameterName, item.ParameterName);
foreach (DataRow row in table.Rows)
{
html.AppendFormat("<option data-icon=\"{0}\" value=\"{1}\")><i class=\"{2}\"></i>{3}</option>",
row["value"].ToString(),
row["value"].ToString(),
row["value"].ToString(),
row["text"].ToString());
}
html.AppendFormat("</select>");
}
else
{
html.AppendFormat("<div class=\"form-group form-md-line-input no-hint\">");
html.AppendFormat("<input type=\"text\" name=\"{0}\" class=\"form-control\">", item.ParameterName);
html.AppendFormat("<label>{0}</label>", item.LabelName);
html.AppendFormat("</div>");
}
}
ViewBag.FormHtml = html.ToString();
ViewBag.Id = id;
return(PartialView("_PreviewParameterModal"));
}
public FuncResult IndustryDetail(string code)
{
string sql = "select * from APD_DIM_ORG_V where ORG_CODE=" + "'" + code + "'";
List <IndustryInfo> list = OracleDbHelper.Query <IndustryInfo>(sql.ToString());
return(new FuncResult()
{
IsSuccess = true, Content = new { list }
});
}
public FuncResult GetIndustry()
{
string sql = "select * from APD_DIM_INDUSTRY";
List <Town> list = OracleDbHelper.Query <Town>(sql.ToString());
return(new FuncResult()
{
IsSuccess = true, Content = new { list }
});
}
public FuncResult GetMuAdd()
{
string sql = "select * from VIEW_ADDED_VALUE_PER_MU";
List <JHAPDViewAddedValuePERMU> list = OracleDbHelper.Query <JHAPDViewAddedValuePERMU>(sql.ToString());
return(new FuncResult()
{
IsSuccess = true, Content = new { list }
});
}
private void DropTables(string host, string port, string service, string userid, string password)
{
try
{
Runtimes.ConnectionString = $"Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = {host})(PORT = {port}))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orclbak))); Persist Security Info=True;User ID={userid};Password={password};";
OracleDbContext.Initialize(host, port, service, userid, password);
//DataTable dt = OracleDbContext.GetDomainSystemTable();
//int count = OracleDbHelper.ExecuteSql("select * from CORE.DOMAINSYSTEM where SYSTEMKEY = :p0", "cis");
//DataTable dt1 = OracleDbHelper.ExecuteDataTable("select * from CORE.DOMAINSYSTEM where SYSTEMKEY = :key", new OracleParameter("@key", "emr"));
StringBuilder sb = new StringBuilder();
IList <OracleParameter> paras = new List <OracleParameter>();
IList <string> uniqueIds = new List <string> {
"c5331527bcc249909742c1f961df3e0d,672a1eb4bedd414c9baa36051f63d120", "5d09f9f161d04202895d18233d56886b,8d531688e23b4d96964f852921925e16", "e32db5191dec4c91a5a176d33a98236d,28273471d69644518225fcc4ce2a86f1"
};
sb.AppendLine("BEGIN");
for (int i = 0; i < 3; i++)
{
sb.AppendLine($"update DATA.bjcasignitem set SignStatusCodeId=:p{i * 3},RowVersion=:p{i * 3 + 1} where UniqueId in (SELECT CAST(column_value AS VARCHAR2(128)) ItemId FROM TABLE(poor.fnIDInString(:p{i * 3 + 2},',')));");
paras.Add(new OracleParameter($"@p{i * 3}", OracleDbType.Int32, i, ParameterDirection.Input));
paras.Add(new OracleParameter($"@p{i * 3 + 1}", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
paras.Add(new OracleParameter($"@p{i * 3 + 2}", OracleDbType.Varchar2, uniqueIds[i], ParameterDirection.Input));
}
sb.Append("END;");
int ret = OracleDbHelper.ExecuteSql(sb.ToString(), paras.ToArray());
sb = new StringBuilder();
sb.AppendLine("BEGIN");
sb.AppendLine($"UPDATE POOR.ORDERREQUEST s set S.ISDELETED=1 where s.encounterid =:EncounterId and s.ISDELETED=0;");
sb.AppendLine($"UPDATE POOR.PERFORMREQUEST s SET S.ISDELETED = 1 where s.ENCOUNTERID =:EncounterId and s.ISDELETED = 0;");
sb.AppendLine($"UPDATE POOR.PERFORMREQUESTBILL s SET S.ISDELETED= 1 where s.ENCOUNTERID =:EncounterId and s.ISDELETED=0;");
sb.AppendLine($"UPDATE FIAB.OTHERBILLING s SET S.ISDELETED= 1 where s.ENCOUNTERID =:EncounterId and s.ISDELETED=0;");
sb.AppendLine("COMMIT;");
sb.AppendLine("END;");
paras.Add(new OracleParameter($"@EncounterId", OracleDbType.Int32, 11961, ParameterDirection.Input));
ret = OracleDbHelper.ExecuteSql(sb.ToString(), paras.ToArray());
string sql = "SELECT table_name FROM all_tables WHERE table_name = :p0";
IList <string> tns = new List <string> {
OracleDbContext.OldTable, OracleDbContext.NewTable
};
foreach (var tablename in tns)
{
if (!string.IsNullOrEmpty(OracleDbHelper.GetSingle(sql, new OracleParameter("@p0", tablename))?.ToString()))
{
OracleDbHelper.ExecuteSql($"DROP TABLE {tablename} PURGE"); //删除Table不进入Recycle
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "异常", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
public FuncResult GetScorePercentage(string year)
{
string sql = "select count(*) as count from apd_dim_org";
string sql1 = "select count(*) as count from VIEW_COMPANY_INDEX_SCORE_TOTAL where COMPOSITE_SCORE > 90 and PERIOD_YEAR=" + year + "";
string sql2 = "select count(*) as count from VIEW_COMPANY_INDEX_SCORE_TOTAL where COMPOSITE_SCORE > 80 and COMPOSITE_SCORE <=90 and PERIOD_YEAR=" + year + "";
string sql3 = "select count(*) as count from VIEW_COMPANY_INDEX_SCORE_TOTAL where COMPOSITE_SCORE > 60 and COMPOSITE_SCORE <=80 and PERIOD_YEAR=" + year + "";
string sql4 = "select count(*) as count from VIEW_COMPANY_INDEX_SCORE_TOTAL where COMPOSITE_SCORE <=60 and PERIOD_YEAR=" + year + "";
List <ScoreCount> list = OracleDbHelper.Query <ScoreCount>(sql.ToString());
List <ScoreCount> list1 = OracleDbHelper.Query <ScoreCount>(sql1.ToString());
List <ScoreCount> list2 = OracleDbHelper.Query <ScoreCount>(sql2.ToString());
List <ScoreCount> list3 = OracleDbHelper.Query <ScoreCount>(sql3.ToString());
List <ScoreCount> list4 = OracleDbHelper.Query <ScoreCount>(sql4.ToString());
decimal value = 0;
decimal value1 = 0;
decimal value2 = 0;
decimal value3 = 0;
decimal value4 = 0;
foreach (var item in list)
{
value = (decimal)item.Count;
}
;
foreach (var item in list1)
{
value1 = (decimal)item.Count;
}
;
foreach (var item in list2)
{
value2 = (decimal)item.Count;
}
;
foreach (var item in list3)
{
value3 = (decimal)item.Count;
}
;
foreach (var item in list4)
{
value4 = (decimal)item.Count;
}
;
string list1percentage = (Convert.ToDouble(value1) / Convert.ToDouble(value)).ToString("0.00%");
string list2percentage = (Convert.ToDouble(value2) / Convert.ToDouble(value)).ToString("0.00%");
string list3percentage = (Convert.ToDouble(value3) / Convert.ToDouble(value)).ToString("0.00%");
string list4percentage = (Convert.ToDouble(value4) / Convert.ToDouble(value)).ToString("0.00%");
return(new FuncResult()
{
IsSuccess = true, Content = new { value1, value2, value3, value4, list1percentage, list2percentage, list3percentage, list4percentage }
});
}
public FuncResult SelectORGInfo(int pageSize, int currentPage, string OrgCode)
{
StringBuilder sql = new StringBuilder("select * from APD_DIM_SUB_ORG where PARENT_ORG_CODE=" + "'" + OrgCode + "'");
List <SubOrgInfo> list = OracleDbHelper.Query <SubOrgInfo>(sql.ToString());
int total = list.Count();
var data = list.ToList().Skip(pageSize * currentPage).Take(pageSize).ToList();
return(new FuncResult()
{
IsSuccess = true, Content = new { data, total }
});
}
//根据行业下拉框改变获取数据
public FuncResult SelectIndustryData(int pageSize, int currentPage, string Industry, string OrgName, string year, string Town)
{
StringBuilder sql = new StringBuilder("select * from VIEW_COMPANY_INDEX_SCORE_TOTAL");
List <string> wheres = new List <string>();
if (Industry == "全部")
{
Industry = null;
}
if (Industry != null)
{
wheres.Add(" INDUSTRY = " + "'" + Industry + "'");
}
if (Town == "全部")
{
Town = null;
}
if (Town != null)
{
wheres.Add(" TOWN = " + "'" + Town + "'");
}
if (OrgName != null)
{
wheres.Add(" ORG_NAME like '%" + OrgName + "%'");
}
if (year != null)
{
wheres.Add(" PERIOD_YEAR =" + "'" + year + "'");
}
if (wheres.Count > 0)
{
string wh = string.Join(" and ", wheres.ToArray());
sql.Append(" where " + wh);
}
List <ReturnDate> list = OracleDbHelper.Query <ReturnDate>(sql.ToString());
int total = list.Count();
if (pageSize * currentPage > total)
{
currentPage = 0;
}
var data = list.ToList().Skip(pageSize * currentPage).Take(pageSize).ToList();
return(new FuncResult()
{
IsSuccess = true, Content = new { data, total }
});
}
public FuncResult GetAvarageScore(string year)
{
StringBuilder sql = new StringBuilder("select * from VIEW_COMPANY_INDEX_AVERAGE");
if (year != null)
{
sql.Append(" where PERIOD_YEAR=" + year);
}
List <AvarageScore> list = OracleDbHelper.Query <AvarageScore>(sql.ToString());
return(new FuncResult()
{
IsSuccess = true, Content = new { list }
});
}
//查询所有
//public FuncResult Select(int pageSize, int currentPage, string OrgName,string year) {
// try {
// StringBuilder sql = new StringBuilder("select * from VIEW_COMPANY_INDEX_SCORE_TOTAL");
// List<string> wheres = new List<string>();
// if (OrgName != null)
// {
// wheres.Add(" ORG_NAME like '%" + OrgName + "%'");
// }
// if (year != null)
// {
// wheres.Add(" PERIOD_YEAR =" +"'"+ year+"'" );
// }
// if (wheres.Count > 0)
// {
// string wh = string.Join(" and ", wheres.ToArray());
// sql.Append(" where " + wh);
// }
// List<ReturnDate> list = OracleDbHelper.Query<ReturnDate>(sql.ToString());
// foreach (var item in list) {
// item.OWNER_EQUITY= Math.Round(Convert.ToDecimal( item.OWNER_EQUITY/10000), 2);
// }
// int total = list.Count();
// if (pageSize * currentPage > total)
// {
// currentPage = 0;
// }
// var data = list.ToList().Skip(pageSize * currentPage).Take(pageSize).ToList();
// return new FuncResult() { IsSuccess = true, Content = new { data, total } };
// }
// catch (Exception ex) { throw new Exception(ex.Message); };
//}
public FuncResult Select(int pageSize, int currentPage, string OrgName, string year, string field, string desc)
{
try
{
StringBuilder sql = new StringBuilder("select * from VIEW_COMPANY_INDEX_SCORE_TOTAL");
List <string> wheres = new List <string>();
if (OrgName != null)
{
wheres.Add(" ORG_NAME like '%" + OrgName + "%'");
}
if (year != null)
{
wheres.Add(" PERIOD_YEAR =" + "'" + year + "'");
}
if (wheres.Count > 0)
{
string wh = string.Join(" and ", wheres.ToArray());
sql.Append(" where " + wh);
}
if (field != null)
{
sql.Append(" order by " + field + " " + desc);
}
else
{
sql.Append(" order by DATA_STATUS");
}
List <ReturnDate> list = OracleDbHelper.Query <ReturnDate>(sql.ToString());
//foreach (var item in list)
//{
// item.OWNER_EQUITY = Math.Round(Convert.ToDecimal(item.OWNER_EQUITY / 10000), 2);
//}
int total = list.Count();
if (pageSize * currentPage > total)
{
currentPage = 0;
}
var data = list.ToList().Skip(pageSize * currentPage).Take(pageSize).ToList();
return(new FuncResult()
{
IsSuccess = true, Content = new { data, total }
});
}
catch (Exception ex) { throw new Exception(ex.Message); };
}
public static DbHelper GetDbHelper()
{
DbHelper result = null;
switch (DbFactory.DbType)
{
case "mysql":
result = new MySqlDbHelper(DbFactory.ConnectionString);
break;
case "oracle":
result = new OracleDbHelper(DbFactory.ConnectionString);
break;
}
return result;
}
public static DbHelper GetDbHelper()
{
DbHelper result = null;
switch (DbFactory.DbType)
{
case "mysql":
result = new MySqlDbHelper(DbFactory.ConnectionString);
break;
case "oracle":
result = new OracleDbHelper(DbFactory.ConnectionString);
break;
}
return(result);
}
public FuncResult BenefiteValuationInfo(string code)
{
StringBuilder sql = new StringBuilder("select * from VIEW_COMPANY_INDEX_SCORE_TOTAL where ORG_CODE=" + "'" + code + "'");
string sql2 = "select count(*) as count from apd_dim_org";
List <Counts> result = OracleDbHelper.Query <Counts>(sql2.ToString());
decimal industyCount = 0;
foreach (var item in result)
{
industyCount = item.Count;
}
List <ReturnDate> list = OracleDbHelper.Query <ReturnDate>(sql.ToString());
return(new FuncResult()
{
IsSuccess = true, Content = new { list, industyCount }
});
}
public FuncResult GetTownData(string name, int score1, int score2, string year)
{
string sql = "";
if (name == "" || name == null)
{
sql = "select Town,count(*) as Count from VIEW_COMPANY_INDEX_SCORE_TOTAL WHERE COMPOSITE_SCORE <=" + score2 + " and COMPOSITE_SCORE >" + score1 + " and PERIOD_YEAR=" + year + " group by Town";
}
else
{
sql = "select Town,sum(" + name + ") as Count from VIEW_COMPANY_INDEX_SCORE_TOTAL WHERE COMPOSITE_SCORE <=" + score2 + " and COMPOSITE_SCORE >" + score1 + " and PERIOD_YEAR=" + year + " group by Town";
}
List <TownCount> list = OracleDbHelper.Query <TownCount>(sql.ToString());
return(new FuncResult()
{
IsSuccess = true, Content = new { list }
});
}
/// <summary>
/// 获取列
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
private DataColumnCollection GetColumnsByDataSource(ReportDataSourceInputDto input)
{
string sql = input.CommandText;
string conn = ConfigurationManager.ConnectionStrings[input.ConnkeyName].ConnectionString;
//List<string> resultP = GetParamsBySql(sql);
List <string> resultP = GetParamsByStr(input.DataParams);
OracleParameter[] paras = new OracleParameter[resultP.Count];
for (int i = 0; i < resultP.Count; i++)
{
paras[i] = new OracleParameter {
ParameterName = resultP[i], Value = ""
};
}
var table = OracleDbHelper.ExecuteDataset(conn,
sql,
input.CommandType == 1 ? System.Data.CommandType.Text : System.Data.CommandType.StoredProcedure,
paras);
return(table.Tables[0].Columns);
}
private List <ConditionRawValue> GetConditionRawValueList()
{
var conditions = _context.SysDataCondition.Select(e => new
{
e.ConditionName,
e.ConditionValueDesc,
e.TableName,
Id = e.SysDataConditionId,
e.ParentColumn,
e.ChildColumn,
e.MasterSlaveFlag,
e.SortValue
}).ToList();
List <string> listsql = new List <string>();
foreach (var obj in conditions)
{
string sqlstr = "";
if (!(obj.MasterSlaveFlag == 1))
{
sqlstr += $"select '{obj.Id}' as ConditionId ,'{obj.SortValue}' as SortValue , '{obj.ConditionName}' as ConditionName,'{obj.MasterSlaveFlag}' as MasterSlaveFlag,'{obj.ConditionValueDesc}' as ConditionValueDesc,'{obj.TableName}' as TableName,cast({obj.ParentColumn} as nvarchar2(36)) as ParentCode,cast('' as nvarchar2(36)) as Code from {obj.TableName} ";
}
else
{
sqlstr += $"select '{obj.Id}' as ConditionId ,'{obj.SortValue}' as SortValue , '{obj.ConditionName}' as ConditionName,'{obj.MasterSlaveFlag}' as MasterSlaveFlag,'{obj.ConditionValueDesc}' as ConditionValueDesc, '{obj.TableName}' as TableName,cast({obj.ParentColumn} as nvarchar2(36)) as ParentCode,cast({obj.ChildColumn} as nvarchar2(36)) as Code from {obj.TableName} ";
}
listsql.Add(sqlstr);
}
string sql = string.Join("union ", listsql);
List <ConditionRawValue> data = OracleDbHelper.Query <ConditionRawValue>(sql);
if (data == null)
{
return(new List <ConditionRawValue>());
}
return(data);
}
private DataSet LoadData(string sql)
{
//return Context.DB.ExecuteDataSet(sql);
return(OracleDbHelper.Query(sql));
}
public async Task <List <DataSet> > GetDataSource(long template_Id, Dictionary <string, string> dicParameters)
{
var entityParameters = await _ReportParameterManager.GetAllReportParametersAsync();
var parameters = entityParameters.Where(d => d.Template_Id == template_Id).OrderBy(d => d.Id);
var entityDataSources = await _ReportDataSourceManager.GetAllReportDataSourcesAsync();
var dataSources = entityDataSources.Where(d => d.Template_Id == template_Id).OrderBy(d => d.Id);
List <DataSet> listDs = new List <DataSet>();
foreach (var dataSource in dataSources)
{
string sql = dataSource.CommandText;
string conn = ConfigurationManager.ConnectionStrings[dataSource.ConnkeyName].ConnectionString;
//List<string> resultP = GetParamsBySql(sql);
List <string> resultP = GetParamsByStr(dataSource.DataParams);
OracleParameter[] paras = new OracleParameter[resultP.Count];
for (int i = 0; i < resultP.Count; i++)
{
var dataParam = parameters.Where(p => p.ParameterName.ToUpper() == resultP[i].ToUpper());
if (dataParam.Count() == 0)
{
continue;
}
#region 判断类型
switch (dataParam.First().ParamterType)
{
case 1:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = dicParameters[resultP[i]]
};
break;
case 2:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = Convert.ToInt32(dicParameters[resultP[i]])
};
break;
case 3:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = Convert.ToDecimal(dicParameters[resultP[i]])
};
break;
case 4:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = Convert.ToDateTime(dicParameters[resultP[i]])
};
break;
case 5:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = Convert.ToBoolean(dicParameters[resultP[i]])
};
break;
case 6:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = new Guid(dicParameters[resultP[i]])
};
break;
default:
paras[i] = new OracleParameter
{
ParameterName = resultP[i],
Value = dicParameters[resultP[i]]
};
break;
}
#endregion
}
var dataSet = OracleDbHelper.ExecuteDataset(conn,
sql,
dataSource.CommandType == 1 ? System.Data.CommandType.Text : System.Data.CommandType.StoredProcedure,
paras);
dataSet.Tables[0].TableName = "dt_" + dataSource.TableName;
dataSet.DataSetName = "ds_" + dataSource.TableName;
listDs.Add(dataSet);
}
return(await Task.FromResult(listDs));
}
public async Task <byte[]> ExportAll(string orgname, string year, string industy, string town, string field, string desc)
{
var comlumHeadrs = new[] { "年份", "企业名称", "所属行业", "所在街道(园区)", "数据状态", "数据提示", "综合评分", "亩均税收得分", "亩均增加值得分", "全员劳动生产率得分", "单位增加值主要污染物排放得分", "单位能耗增加值得分", "净资产收益率得分", "研发经费支出占主营业务收入比重得分", "所有者权益（万元）", "从业人员平均数（人）", "工业增加值（万元）", "污染物排放当量（吨）", "实缴税金（万元）", "用地面积（亩）", "利润总额（万元）", "净资产", "主营业务收入（万元）", "研发经费支出（万元）", "工业企业能源消费量（吨标准煤）", "亩均税收（万元/亩）", "亩均增加值（万元/亩）", " 单位能耗增加值（万元/吨标煤）", "单位增加值主要污染物排放当量（当量/万元）", "全员劳动生产率（万元/人）", "净资产收益率（%）", "研发经费支出占主营业务收入比重（%）" };
byte[] result;
StringBuilder sql = new StringBuilder("select * from VIEW_COMPANY_INDEX_SCORE_TOTAL");
List <string> wheres = new List <string>();
if (orgname != null)
{
wheres.Add(" ORG_NAME like '%" + orgname + "%'");
}
if (year != null)
{
wheres.Add(" PERIOD_YEAR =" + "'" + year + "'");
}
if (industy == "全部")
{
industy = null;
}
if (industy != null)
{
wheres.Add(" INDUSTRY = " + "'" + industy + "'");
}
if (town == "全部")
{
town = null;
}
if (town != null)
{
wheres.Add(" TOWN = " + "'" + town + "'");
}
if (wheres.Count > 0)
{
string wh = string.Join(" and ", wheres.ToArray());
sql.Append(" where " + wh);
}
if (field != null)
{
sql.Append(" order by " + field + " " + desc);
}
var data = _context.SysUserInfo.ToList();
List <ReturnDate> datas = OracleDbHelper.Query <ReturnDate>(sql.ToString());
var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Sheet1"); //Worksheet name
//First add the headers
for (var i = 0; i < comlumHeadrs.Count(); i++)
{
worksheet.Cells[1, i + 1].Value = comlumHeadrs[i];
}
//Add values
var j = 2;
// var chars = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
await Task.Run(() =>
{
foreach (var obj in datas)
{
worksheet.Cells["A" + j].Value = obj.PERIOD_YEAR;
worksheet.Cells["B" + j].Value = obj.ORG_NAME;
worksheet.Cells["C" + j].Value = obj.INDUSTRY;
worksheet.Cells["D" + j].Value = obj.TOWN;
worksheet.Cells["E" + j].Value = obj.DATA_STATUS;
worksheet.Cells["F" + j].Value = obj.DATA_TIP;
worksheet.Cells["G" + j].Value = obj.COMPOSITE_SCORE;
worksheet.Cells["H" + j].Value = obj.TAX_PER_MU_SCORE;
worksheet.Cells["I" + j].Value = obj.ADD_VALUE_PER_MU_SCORE;
worksheet.Cells["J" + j].Value = obj.PRODUCTIVITY_SCORE;
worksheet.Cells["K" + j].Value = obj.POLLUTANT_DISCHARGE_SCORE;
worksheet.Cells["L" + j].Value = obj.ENERGY_CONSUMPTION_SCORE;
worksheet.Cells["M" + j].Value = obj.NET_ASSETS_PROFIT_SCORE;
worksheet.Cells["N" + j].Value = obj.R_D_EXPENDITURE_RATIO_SCORE;
worksheet.Cells["O" + j].Value = obj.OWNER_EQUITY;
worksheet.Cells["P" + j].Value = obj.WORKER_MONTH;
worksheet.Cells["Q" + j].Value = obj.Industrial_added_value;
worksheet.Cells["R" + j].Value = obj.pollutant_discharge2;
worksheet.Cells["S" + j].Value = obj.fact_tax;
worksheet.Cells["T" + j].Value = obj.LAND_AREA;
worksheet.Cells["U" + j].Value = obj.PROFIT;
worksheet.Cells["V" + j].Value = obj.ASSETS;
worksheet.Cells["W" + j].Value = obj.MAIN_BUSINESS_INCOME;
worksheet.Cells["X" + j].Value = obj.R_D_EXPENDITURE;
worksheet.Cells["Y" + j].Value = obj.Energy_consumption2;
worksheet.Cells["Z" + j].Value = obj.TAX_PER_MU;
worksheet.Cells["AA" + j].Value = obj.ADD_VALUE_PER_MU;
worksheet.Cells["AB" + j].Value = obj.ENERGY_CONSUMPTION;
worksheet.Cells["AC" + j].Value = obj.POLLUTANT_DISCHARGE;
worksheet.Cells["AD" + j].Value = obj.PRODUCTIVITY;
worksheet.Cells["AE" + j].Value = obj.NET_ASSETS_PROFIT;
worksheet.Cells["AF" + j].Value = obj.R_D_EXPENDITURE_RATIO;
j++;
}
});
result = package.GetAsByteArray();
return(result);
}
private bool ProcessData(string tablename, string module, out string sql)
{
try
{
string createTableSql = $"CREATE TABLE {OracleDbContext.NewTable} AS SELECT * FROM v$sql";
//Context.DB.ExecSqlStatement(createTableSql);
OracleDbHelper.ExecuteSql(createTableSql);
//string sqlbase = string.Format(@"SELECT a.SQL_ID, a.parsing_schema_name AS SCHEMA, a.module AS MODULE, a.sql_text AS SQL_TEXT
//, a.sql_fulltext AS SQL_FULLTEXT
//, a.parse_calls - nvl(b.parse_calls, 0) AS PARSE_CALLS
//, a.buffer_gets - nvl(b.buffer_gets, 0) AS BUFFER_GETS
//, a.disk_reads - nvl(b.disk_reads, 0) AS DISK_READS
//, a.executions - nvl(b.executions, 0) AS EXECUTIONS
//, round((a.cpu_time - nvl(b.cpu_time, 0)) / 1000000, 2) AS CPU_TIME
//, round((a.cpu_time - nvl(b.cpu_time, 0)) / ((a.executions - nvl(b.executions, 0)) * 1000000), 2) AS CPU_TIME_PER_EXE
//, round((a.elapsed_time - nvl(b.elapsed_time, 0)) / ((a.executions - nvl(b.executions, 0)) * 1000000), 2) AS ELAPSED_TIME_PER_EXE
//, to_date(a.FIRST_LOAD_TIME, 'yyyy-MM-dd hh24:mi:ss') AS FIRST_LOAD_TIME, a.LAST_ACTIVE_TIME
//FROM {0} a LEFT JOIN {1} b ON a.hash_value = b.hash_value AND a.address = b.address
//WHERE ( to_date(a.FIRST_LOAD_TIME, 'yyyy/MM/dd hh24:mi:ss') > to_date('{2}', 'yyyy/MM/dd hh24:mi:ss') OR a.LAST_ACTIVE_TIME > to_date('{2}', 'yyyy/MM/dd hh24:mi:ss') )
//AND (a.executions - nvl(b.executions, 0)) > 0 <CRITERIA>
//ORDER BY a.LAST_ACTIVE_TIME DESC, a.FIRST_LOAD_TIME DESC", OracleDbContext.NewTable, OracleDbContext.OldTable, this.StartOnText);
string sqlbase = string.Format(@"SELECT a.SQL_ID, a.parsing_schema_name AS SCHEMA, a.module AS MODULE, a.sql_text AS SQL_TEXT
, a.sql_fulltext AS SQL_FULLTEXT
, a.parse_calls AS PARSE_CALLS
, a.buffer_gets AS BUFFER_GETS
, a.disk_reads AS DISK_READS
, decode(a.executions,0,1,a.executions) AS EXECUTIONS
, round(a.cpu_time / 1000000, 2) AS CPU_TIME
, round(a.cpu_time / (decode(a.executions,0,1,a.executions) * 1000000), 2) AS CPU_TIME_PER_EXE
, round(a.elapsed_time / (decode(a.executions,0,1,a.executions) * 1000000), 2) AS ELAPSED_TIME_PER_EXE
, to_date(a.FIRST_LOAD_TIME, 'yyyy-MM-dd hh24:mi:ss') AS FIRST_LOAD_TIME, a.LAST_ACTIVE_TIME
FROM v$sql a
WHERE ( to_date(a.FIRST_LOAD_TIME, 'yyyy/MM/dd hh24:mi:ss') > to_date('{0}', 'yyyy/MM/dd hh24:mi:ss') OR a.LAST_ACTIVE_TIME > to_date('{0}', 'yyyy/MM/dd hh24:mi:ss') )
<CRITERIA>
ORDER BY a.LAST_ACTIVE_TIME DESC, a.FIRST_LOAD_TIME DESC", this.StartOnText);
string criteria = "";
string criteria2 = "";
if (!string.IsNullOrWhiteSpace(module))
{
IList <string> ms = module.Split(';').Select(a => string.Format("'{0}'", a.ToUpper())).ToList();
string modules = string.Join(",", ms);
criteria2 = string.Format("AND UPPER(a.module) IN ({0})", modules);
}
criteria = criteria2;
if (this.IsCurrUser)
{
string user = "";
string usersql = "SELECT user FROM dual";
//DataSet ds = Context.DB.ExecuteDataSet(usersql);
//if ((ds != null) && (ds.Tables[0].Rows.Count > 0))
// user = Convert.ToString(ds.Tables[0].Rows[0]["user"]);
user = OracleDbHelper.GetSingle(usersql).ToString();
criteria = string.Format("AND a.parsing_schema_name = '{0}'", user);
if (!string.IsNullOrWhiteSpace(criteria2))
{
criteria = string.Format("{0} {1}", criteria, criteria2);
}
}
sqlbase = sqlbase.Replace("<CRITERIA>", criteria);
if (this.IsSaveOracle && !string.IsNullOrWhiteSpace(tablename))
{
this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Checking table:" + tablename + " ...\r\n")));
string sql2 = "SELECT table_name FROM all_tables WHERE table_name = :p0";
if (!string.IsNullOrEmpty(OracleDbHelper.GetSingle(sql2, new OracleParameter("@p0", tablename))?.ToString()))
{
this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Drop table:" + tablename + " ...\r\n")));
OracleDbHelper.ExecuteSql($"DROP TABLE {tablename} PURGE");
}
this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Creating table:" + tablename + " ...\r\n")));
string createTable = $"CREATE TABLE {tablename} AS {sqlbase}";
//Context.DB.ExecSqlStatement(createTable);
OracleDbHelper.ExecuteSql(createTable);
}
sql = string.Format("SELECT ROWNUM, t.* FROM ({0}) t WHERE ROWNUM <= 500", sqlbase);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "异常", MessageBoxButton.OK, MessageBoxImage.Error);
sql = string.Empty;
return(false);
}
}
public override void Execute(IJobExecutionContext context)
{
JobDataMap jobDM = context.JobDetail.JobDataMap;
string connkeyName = jobDM["connkeyName"] == null ? string.Empty : jobDM["connkeyName"].ToString();
string commandType = jobDM["commandType"] == null ? string.Empty : jobDM["commandType"].ToString();
string commandText = jobDM["commandText"] == null ? string.Empty : jobDM["commandText"].ToString();
string paramters = jobDM["paramters"] == null ? string.Empty : jobDM["paramters"].ToString();
//Thread.Sleep(999);
var dicParameter = paramters.ToObject <Dictionary <string, string> >();
OracleParameter[] paras = new OracleParameter[dicParameter == null ? 0 : dicParameter.Count()];
if (dicParameter != null)
{
int i = 0;
foreach (var p in dicParameter)
{
string pName = p.Key;
string pValue = p.Value.Split(',')[0];
int pType = p.Value.Split(',')[1] == null ? 0 : Convert.ToInt32((p.Value.Split(',')[1]));
#region 判断类型
switch (pType)
{
case (int)ReportDefine.ParamterTypes.字符型:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = pValue
};
break;
case (int)ReportDefine.ParamterTypes.整型:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = Convert.ToInt32(pValue)
};
break;
case (int)ReportDefine.ParamterTypes.浮点型:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = Convert.ToDecimal(pValue)
};
break;
case (int)ReportDefine.ParamterTypes.日期型:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = pValue == "#sysDate#" ? DateTime.Now : Convert.ToDateTime(pValue)
};
break;
case (int)ReportDefine.ParamterTypes.布尔型:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = Convert.ToBoolean(pValue)
};
break;
case (int)ReportDefine.ParamterTypes.Guid型:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = new Guid(pValue)
};
break;
default:
paras[i] = new OracleParameter
{
ParameterName = pName,
Value = pValue
};
break;
}
#endregion
i++;
}
}
string conn = ConfigurationManager.ConnectionStrings[connkeyName].ConnectionString;
OracleDbHelper.ExecuteNonQuery(
conn,
commandText,
commandType == "1" ? System.Data.CommandType.Text : System.Data.CommandType.StoredProcedure,
paras);
}
