private string appendSQLString(Ast ast,List<string> groups,ArrayList score103,ArrayList level, string EL,int expectedSalary)
{
ArrayList data = changeToArray(ast);
string group = appendData(groups);
string command = "SELECT DISTINCT D.DID,D.UName,D.UURL,D.DName,D.DURL, D.Salary, D.SalaryURL, D.MinScore, ("
+score103[0].ToString()+"*D.EW1+"+score103[1].ToString()+"*D.EW2+"
+score103[2].ToString()+"*D.EW3+"+score103[3].ToString()+"*D.EW4+"
+score103[4].ToString()+"*D.EW5+"+score103[5].ToString()+"*D.EW6+"
+score103[6].ToString()+"*D.EW7+"+score103[7].ToString()+"*D.EW8+"
+score103[8].ToString()+"*D.EW9+"+score103[9].ToString()+"*D.EW10) As YourScore "+
"FROM D,DC,CG WHERE D.DID=DC.DID AND DC.CNAME=CG.CNAME AND CG.GNAME IN ("+group+") "+
"AND D.ELLEVEL >= '" + EL + "' AND D.TL1 <= " + level[0].ToString() + " " +
"AND D.TL2 <= "+ level[1].ToString() +" AND D.TL3 <= "+level[2].ToString()+" "+
"AND D.TL4 <= "+level[3].ToString()+" AND D.TL5 <= "+level[4].ToString()+" "+
"AND D.TL6 <= "+level[5].ToString()+" "+
"AND D.MinScore <= ("+score103[0].ToString()+"*D.EW1+"+score103[1].ToString()+"*D.EW2+"
+score103[2].ToString()+"*D.EW3+"+score103[3].ToString()+"*D.EW4+"
+score103[4].ToString()+"*D.EW5+"+score103[5].ToString()+"*D.EW6+"
+score103[6].ToString()+"*D.EW7+"+score103[7].ToString()+"*D.EW8+"
+score103[8].ToString()+"*D.EW9+"+score103[9].ToString()+"*D.EW10)*1.1 "+
"AND D.Salary >= " + expectedSalary.ToString() + " AND (D.NEW1 = 0 OR " + data[0].ToString() + " IS NOT NULL) " +
"AND (D.NEW2 =0 OR " + data[1].ToString() + " IS NOT NULL) AND (D.NEW3 = 0 OR " + data[2].ToString() + " IS NOT NULL) " +
"AND (D.NEW4 =0 OR " + data[3].ToString() + " IS NOT NULL) AND (D.NEW5 = 0 OR " + data[4].ToString() + " IS NOT NULL) " +
"AND (D.NEW6 = 0 OR " + data[5].ToString() + " IS NOT NULL) AND (D.NEW7 = 0 OR " + data[6].ToString() + " IS NOT NULL) " +
"AND (D.NEW8 = 0 OR " + data[7].ToString() + " IS NOT NULL) AND (D.NEW9 = 0 OR " + data[8].ToString() + " IS NOT NULL) " +
"AND (D.NEW10 = 0 OR " + data[9].ToString() + " IS NOT NULL) ORDER BY D.Salary DESC;";
return command;
}
private ArrayList changeToArray(Ast ast)
{
string[] score = { ast.Chinese ,ast.English , ast.Math_A ,ast.Math_B ,
ast.History,ast.Geographic , ast.Citizen_and_Society,
ast.Physics,ast.Chemistry,ast.Biology };
ArrayList list = new ArrayList();
for(int i=0;i<score.Length;i++)
{
if (String.IsNullOrEmpty(score[i])) list.Add("null");
else list.Add(score[i]);
}
return list;
}
/// <summary>
/// 將103學年度的指考分數換算成104學年度的指考分數
/// </summary>
/// <param name="ast">指考成績</param>
/// <returns>104學年度的指考成績</returns>
///
public ArrayList turnTo103Score(Ast ast)
{
string sqlCom = null;
SqlDataAdapter buffer = null;
ArrayList oldScore = new ArrayList();
int[] newScore = {ast.Chinese == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Chinese))),
ast.English == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.English))),
ast.Math_A == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Math_A))),
ast.Math_B == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Math_B))),
ast.History == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.History))),
ast.Geographic == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Geographic))),
ast.Citizen_and_Society == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Citizen_and_Society))),
ast.Physics == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Physics))),
ast.Chemistry == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Chemistry))),
ast.Biology == null? 0 : Convert.ToInt32(Math.Floor(Convert.ToDouble(ast.Biology)))
};
string[] subject = {"國文","英文","數甲","數乙","歷史","地理","公社","物理","化學","生物"};
oldScore.Clear();
this.conn.Open();
for (int i = 0; i < 10; i++)
{
sqlCom = "SELECT Min(OldScoreData.Score) As Score FROM OldScoreData,NewScoreData WHERE OldScoreData.Ename = '" + subject[i] + "' " +
"AND NewScoreData.Ename = '" + subject[i] + "' AND NewScoreData.Score = " + newScore[i].ToString() + " AND NewScoreData.Percentage <= OldScoreData.Percentage;";
buffer = new SqlDataAdapter(sqlCom,this.conn);
buffer.Fill(dt);
if (dt.Rows[0].IsNull("Score")) oldScore.Add(0);
else oldScore.Add(Convert.ToInt32(dt.Rows[0]["Score"]));
dt.Clear();
}
buffer.Dispose();
this.conn.Close();
return oldScore;
}