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 }
});
}
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 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 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 }
});
}
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 }
});
}
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 <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);
}