public int ConvertZhiLiDimScore(int dimID, double originalScore)
{
int nRet = 0;
if (dimID < 0 || originalScore < 0)
{
return nRet;
}
else
{
Dimension dim = new Dimension();
dim = DimensionDAL.GetModel(dimID);
double[] dimP = {dim.Dimension_2,//1
dim.Dimension_8,//2
dim.Dimension_17,//3
dim.Dimension_32,//4
dim.Dimension_50,//5
dim.Dimension_68,//6
dim.Dimension_83,//7
dim.Dimension_92,//8
dim.Dimension_98};//9
if (originalScore <= dimP[0]) return 2;
if (originalScore >= dimP[8]) return 98;
double[,] dimscore = new double[8, 10];
for (int r = 0; r < 8; r++)
{
double diff = (dimP[r + 1] - dimP[r]) / 10;
for (int c = 0; c < 10; c++)
{
dimscore[r, c] = dimP[r] + (diff * c);
dimscore[r, c] = Math.Round(dimscore[r, c], 3, MidpointRounding.AwayFromZero);
}
}
double[] dimZhiLi = { 4.006, 10.565, 22.663, 40.129, 50.000, 59.871, 77.337, 89.435, 95.994 };
for (int i = 0; i < 8; i++)
{
double diff = (dimP[i + 1] - dimP[i]) / 10;
for (int j = 0; j < 10; j++)
{
if (dimscore[i, j] >= originalScore)//返回右边值？
{
nRet = (int)Math.Round(dimZhiLi[i] + (j * diff),0,MidpointRounding.AwayFromZero);
//return (int)Math.Round(()nRet, 0, MidpointRounding.AwayFromZero);
return nRet;
}
}
}
return nRet;
}
}
public double ConvertDimScore(int dimID, double originalScore)
{
double fRet = 0.0f;
if (dimID < 0 || originalScore < 0)
{
return fRet;
}
else
{
Dimension dim = new Dimension();
dim = DimensionDAL.GetModel(dimID);
double[] dimP = {dim.Dimension_2,//1
dim.Dimension_8,//2
dim.Dimension_17,//3
dim.Dimension_32,//4
dim.Dimension_50,//5
dim.Dimension_68,//6
dim.Dimension_83,//7
dim.Dimension_92,//8
dim.Dimension_98};//9
if (originalScore <= dimP[0]) return 1.0f;
if (originalScore >= dimP[8]) return 9.0f;
double[,] dimscore = new double[8, 10];
for (int r = 0; r < 8; r++)
{
double diff = (dimP[r + 1] - dimP[r]) / 10;
for (int c = 0; c < 10; c++)
{
dimscore[r, c] = dimP[r] + (diff * c);
dimscore[r, c] = Math.Round(dimscore[r, c], 0, MidpointRounding.AwayFromZero);
}
}
for (int i = 0; i < 8; i++)
{
for (int j = 0; j < 10; j++)
{
if (dimscore[i, j] >= originalScore)//返回右边值？
{
fRet = (i + 1) + (j * 0.1);
return fRet;
}
}
}
return fRet;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
DimensionBLL dimensionBll = new DimensionBLL();
Dimension temp = new Dimension();
if (Request.QueryString["dimension_id"] != null)
{
temp.Dimension_id = int.Parse(Request.QueryString["dimension_id"]);
}
temp.Dimnm =txtbDimnm.Text.Trim();
temp.Dimension_descrip = txtbDescrip.Text.Trim();
temp.Dimension_classid=int.Parse(ddlType.SelectedValue);
temp.Time_min = int.Parse(txtbTime.Text.Trim());//界面输入值为分，DB存储秒
temp.Linterval = float.Parse(txtbQ3.Text.Trim());
temp.Minterval = float.Parse(txtbQ2.Text.Trim());
temp.Sinterval = float.Parse(txtbQ1.Text.Trim());
temp.Dimension_2 = float.Parse(txtb2.Text.Trim());
temp.Dimension_8 = float.Parse(txtb8.Text.Trim());
temp.Dimension_17 = float.Parse(txtb17.Text.Trim());
temp.Dimension_32 = float.Parse(txtb32.Text.Trim());
temp.Dimension_50 = float.Parse(txtb50.Text.Trim());
temp.Dimension_68 = float.Parse(txtb68.Text.Trim());
temp.Dimension_83 = float.Parse(txtb83.Text.Trim());
temp.Dimension_92 = float.Parse(txtb92.Text.Trim());
temp.Dimension_98 = float.Parse(txtb98.Text.Trim());
//维度上限时间，下限时间
temp.Time_xiaxian = int.Parse(txtXiaXianTime.Text.Trim());
temp.Time_shangxian = int.Parse(txtShangXianTime.Text.Trim());
if (dimensionBll.Exists(temp.Dimension_id))
{
int n = dimensionBll.Update(temp);
if (n > 0)
{
MessageBox.ShowAndRedirect(this, "维度修改成功", "DimensionList.aspx");
}
}
else
{
if (dimensionBll.Add(temp) != -1)
{
MessageBox.ShowAndRedirect(this, "维度添加成功", "DimensionList.aspx");
}
}
}
public void Delete(Dimension dimension)
{
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Dimension model)
{
//TODO: ADD return EnterpriseUserDAL.Add(model);
return DimensionDAL.Add(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(Dimension model)
{
return DimensionDAL.Update(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public static int Update(Dimension model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update dbo.tb_Dimension set ");
strSql.Append("dimension_name=@dimension_name,");
strSql.Append("dimension_description=@dimension_description,");
strSql.Append("dimension_class_id=@dimension_class_id,");
strSql.Append("dimension_time_min=@dimension_time_min,");
strSql.Append("dimension_sinterval=@dimension_sinterval,");
strSql.Append("dimension_minterval=@dimension_minterval,");
strSql.Append("dimension_linterval=@dimension_linterval,");
strSql.Append("dimension_98=@dimension_98,");
strSql.Append("dimension_92=@dimension_92,");
strSql.Append("dimension_83=@dimension_83,");
strSql.Append("dimension_68=@dimension_68,");
strSql.Append("dimension_50=@dimension_50,");
strSql.Append("dimension_32=@dimension_32,");
strSql.Append("dimension_17=@dimension_17,");
strSql.Append("dimension_8=@dimension_8,");
strSql.Append("dimension_2=@dimension_2,");
strSql.Append("dimension_time_xiaxian=@dimension_time_xiaxian,");
strSql.Append("dimension_time_shangxian=@dimension_time_shangxian ");
strSql.Append(" where dimension_id=@dimension_id ");
SqlParameter[] parameters = {
new SqlParameter("@dimension_id", SqlDbType.Int),
new SqlParameter("@dimension_name", SqlDbType.NVarChar,255),
new SqlParameter("@dimension_description", SqlDbType.NVarChar,255),
new SqlParameter("@dimension_class_id", SqlDbType.Int,4),
new SqlParameter("@dimension_time_min", SqlDbType.Int,4),
new SqlParameter("@dimension_sinterval",SqlDbType.Float),
new SqlParameter("@dimension_minterval", SqlDbType.Float),
new SqlParameter("@dimension_linterval", SqlDbType.Float),
new SqlParameter("@dimension_98", SqlDbType.Float),
new SqlParameter("@dimension_92", SqlDbType.Float),
new SqlParameter("@dimension_83", SqlDbType.Float),
new SqlParameter("@dimension_68", SqlDbType.Float),
new SqlParameter("@dimension_50", SqlDbType.Float),
new SqlParameter("@dimension_32", SqlDbType.Float),
new SqlParameter("@dimension_17", SqlDbType.Float),
new SqlParameter("@dimension_8", SqlDbType.Float),
new SqlParameter("@dimension_2", SqlDbType.Float),
new SqlParameter("@dimension_time_xiaxian", SqlDbType.Int,4),
new SqlParameter("@dimension_time_shangxian", SqlDbType.Int,4) };
parameters[0].Value = model.Dimension_id;
parameters[1].Value = model.Dimnm;
parameters[2].Value = model.Dimension_descrip;
parameters[3].Value = model.Dimension_classid;
parameters[4].Value = model.Time_min;
parameters[5].Value = model.Sinterval;
parameters[6].Value = model.Minterval;
parameters[7].Value = model.Linterval;
parameters[8].Value = model.Dimension_98;
parameters[9].Value = model.Dimension_92;
parameters[10].Value = model.Dimension_83;
parameters[11].Value = model.Dimension_68;
parameters[12].Value = model.Dimension_50;
parameters[13].Value = model.Dimension_32;
parameters[14].Value = model.Dimension_17;
parameters[15].Value = model.Dimension_8;
parameters[16].Value = model.Dimension_2;
parameters[17].Value = model.Time_xiaxian;
parameters[18].Value = model.Time_shangxian;
return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public static Dimension GetModel(int dimension_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from dbo.tb_Dimension");
strSql.Append(" where dimension_id=@ID ");
SqlParameter[] parameters = {
new SqlParameter("@ID", SqlDbType.Int,4)};
parameters[0].Value = dimension_id;
Dimension model = new Dimension();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["dimension_id"].ToString() != "")
{
model.Dimension_id = int.Parse(ds.Tables[0].Rows[0]["dimension_id"].ToString());
}
model.Dimnm = ds.Tables[0].Rows[0]["dimension_name"].ToString();
model.Dimension_descrip = ds.Tables[0].Rows[0]["dimension_description"].ToString();
model.Dimension_classid = int.Parse(ds.Tables[0].Rows[0]["dimension_class_id"].ToString());
model.Time_min = (int)ds.Tables[0].Rows[0]["dimension_time_min"];
model.Sinterval = float.Parse(ds.Tables[0].Rows[0]["dimension_sinterval"].ToString());
model.Minterval = float.Parse(ds.Tables[0].Rows[0]["dimension_minterval"].ToString());
model.Linterval = float.Parse(ds.Tables[0].Rows[0]["dimension_linterval"].ToString());
model.Dimension_classid = (int)ds.Tables[0].Rows[0]["dimension_class_id"];
model.Time_min = (int)ds.Tables[0].Rows[0]["dimension_time_min"];
model.Dimension_98 = float.Parse(ds.Tables[0].Rows[0]["dimension_98"].ToString());
model.Dimension_92 = float.Parse(ds.Tables[0].Rows[0]["dimension_92"].ToString());
model.Dimension_83 = float.Parse(ds.Tables[0].Rows[0]["dimension_83"].ToString());
model.Dimension_68 = float.Parse(ds.Tables[0].Rows[0]["dimension_68"].ToString());
model.Dimension_50 = float.Parse(ds.Tables[0].Rows[0]["dimension_50"].ToString());
model.Dimension_32 = float.Parse(ds.Tables[0].Rows[0]["dimension_32"].ToString());
model.Dimension_17 = float.Parse(ds.Tables[0].Rows[0]["dimension_17"].ToString());
model.Dimension_8 = float.Parse(ds.Tables[0].Rows[0]["dimension_8"].ToString());
model.Dimension_2 = float.Parse(ds.Tables[0].Rows[0]["dimension_2"].ToString());
if (false == ds.Tables[0].Rows[0]["dimension_time_xiaxian"].Equals(DBNull.Value))
{
model.Time_xiaxian = Int32.Parse(ds.Tables[0].Rows[0]["dimension_time_xiaxian"].ToString());
}
if (false == ds.Tables[0].Rows[0]["dimension_time_shangxian"].Equals(DBNull.Value))
{
model.Time_shangxian = Int32.Parse(ds.Tables[0].Rows[0]["dimension_time_shangxian"].ToString());
}
return model;
}
else
{
return null;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public static int Add(Dimension model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into dbo.tb_Dimension(");
strSql.Append(@"dimension_name,
dimension_description,
dimension_class_id,
dimension_time_min,
dimension_sinterval,
dimension_minterval,
dimension_linterval,
dimension_98,
dimension_92,
dimension_83,
dimension_68,
dimension_50,
dimension_32,
dimension_17,
dimension_8,
dimension_2,dimension_time_xiaxian,dimension_time_shangxian)");
strSql.Append(" values (");
strSql.Append(@"@dimension_name,
@dimension_description,
@dimension_class_id,
@dimension_time_min,
@dimension_sinterval,
@dimension_minterval,
@dimension_linterval,
@dimension_98,
@dimension_92,
@dimension_83,
@dimension_68,
@dimension_50,
@dimension_32,
@dimension_17,
@dimension_8,
@dimension_2,@dimension_time_xiaxian,@dimension_time_shangxian)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@dimension_name", SqlDbType.NVarChar,255),
new SqlParameter("@dimension_description", SqlDbType.NVarChar,255),
new SqlParameter("@dimension_class_id", SqlDbType.Int,4),
new SqlParameter("@dimension_time_min", SqlDbType.Int,4),
new SqlParameter("@dimension_sinterval",SqlDbType.Float),
new SqlParameter("@dimension_minterval", SqlDbType.Float),
new SqlParameter("@dimension_linterval", SqlDbType.Float),
new SqlParameter("@dimension_98", SqlDbType.Float),
new SqlParameter("@dimension_92", SqlDbType.Float),
new SqlParameter("@dimension_83", SqlDbType.Float ),
new SqlParameter("@dimension_68", SqlDbType.Float),
new SqlParameter("@dimension_50", SqlDbType.Float),
new SqlParameter("@dimension_32", SqlDbType.Float),
new SqlParameter("@dimension_17", SqlDbType.Float),
new SqlParameter("@dimension_8", SqlDbType.Float),
new SqlParameter("@dimension_2", SqlDbType.Float),
new SqlParameter("@dimension_time_xiaxian", SqlDbType.Int,4),
new SqlParameter("@dimension_time_shangxian", SqlDbType.Int,4)
};
parameters[0].Value = model.Dimnm;
parameters[1].Value = model.Dimension_descrip;
parameters[2].Value = model.Dimension_classid;
parameters[3].Value = model.Time_min;
parameters[4].Value = model.Sinterval;
parameters[5].Value = model.Minterval;
parameters[6].Value = model.Linterval;
parameters[7].Value = model.Dimension_98;
parameters[8].Value = model.Dimension_92;
parameters[9].Value = model.Dimension_83;
parameters[10].Value = model.Dimension_68;
parameters[11].Value = model.Dimension_50;
parameters[12].Value = model.Dimension_32;
parameters[13].Value = model.Dimension_17;
parameters[14].Value = model.Dimension_8;
parameters[15].Value = model.Dimension_2;
parameters[16].Value = model.Time_xiaxian;
parameters[17].Value = model.Time_shangxian;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return -1;
}
else
{
return Convert.ToInt32(obj);
}
}
public double GetSingleScore(int dimID, int originalScore)
{
double fRet = 0.0f;
if (dimID < 0 || originalScore < 0)
{
return fRet;
}
else
{
Dimension dim = new Dimension();
dim = DimensionDAL.GetModel(dimID);
double[] dimP = {dim.Dimension_2,//1
dim.Dimension_8,//2
dim.Dimension_17,//3
dim.Dimension_32,//4
dim.Dimension_50,//5
dim.Dimension_68,//6
dim.Dimension_83,//7
dim.Dimension_92,//8
dim.Dimension_98};//9
double[] dimScore = { 0.01, 0.03, 0.10, 0.28, 0.59, 0.76, 0.89, 0.98, 0.92 };
//查找最接近的一个值
Double searchValue = originalScore;
Double currentNearest = dimP[0];
int nearestPos = 0;
Double currentDifference = Math.Abs(currentNearest - searchValue);
for (int i = 1; i < dimP.Length; i++)
{
Double diff = Math.Abs(dimP[i] - searchValue);
if (diff < currentDifference)
{
currentDifference = diff;
currentNearest = dimP[i];
nearestPos = i;
}
}
fRet = dimScore[nearestPos];
return fRet;
}
}
private bool ImportDimension(string xlsFileNm)
{
try
{
if (!File.Exists(xlsFileNm)) return false;
Workbook book = Workbook.Load(xlsFileNm);
Worksheet sheet = book.Worksheets[0];
DimensionBLL dimBll = new DimensionBLL();
Dimension dimModel = new Dimension();
// traverse rows by Index
for (int rowIndex = sheet.Cells.FirstRowIndex + 1;//忽略第一行(标题行)
rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
Row row = sheet.Cells.GetRow(rowIndex);
dimModel.Dimnm = row.GetCell(1).StringValue;
dimModel.Dimension_descrip = row.GetCell(2).StringValue;
switch (row.GetCell(3).StringValue)
{
case "职业能力/技能类":
dimModel.Dimension_classid = 1;
break;
case "职业行为风格类":
dimModel.Dimension_classid = 2;
break;
case "职业智力/潜能类":
dimModel.Dimension_classid = 3;
break;
case "职业态度/动机类":
dimModel.Dimension_classid = 4;
break;
case "质量检测类":
dimModel.Dimension_classid = 5;
break;
case "职业心理基础类":
dimModel.Dimension_classid = 6;
break;
default:
break;
}
dimModel.Time_min = (int)float.Parse(row.GetCell(4).StringValue) ;
dimModel.Sinterval = (int)float.Parse(row.GetCell(5).StringValue);
dimModel.Minterval = (int)float.Parse(row.GetCell(6).StringValue);
dimModel.Linterval = (int)float.Parse(row.GetCell(7).StringValue);
dimModel.Dimension_2 = float.Parse(row.GetCell(8).StringValue);
dimModel.Dimension_8 = float.Parse(row.GetCell(9).StringValue);
dimModel.Dimension_17 = float.Parse(row.GetCell(10).StringValue);
dimModel.Dimension_32 = float.Parse(row.GetCell(11).StringValue);
dimModel.Dimension_50 = float.Parse(row.GetCell(12).StringValue);
dimModel.Dimension_68 = float.Parse(row.GetCell(13).StringValue);
dimModel.Dimension_83 = float.Parse(row.GetCell(14).StringValue);
dimModel.Dimension_92 = float.Parse(row.GetCell(15).StringValue);
dimModel.Dimension_98 = float.Parse(row.GetCell(16).StringValue);
dimBll.Add(dimModel);
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}