public void dataLoad()
{
try
{
using (SqlConnection con = new SqlConnection(strConString))
{
SqlCommand cmd = new SqlCommand("select a.IdBarang, a.[Nama Barang], a.Qty, a.Harga From tblBarang a", con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
GridResult.DataSource = dt;
GridResult.DataBind();
}
con.Close();
}
}
catch (Exception ex)
{
message = ex.Message;
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! following error occured : " + message.ToString() + "');", true);
}
}
public void dataLoad_Grid()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spLoadGridView";
cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = textNoInvoice.Text.Trim();
cmd.Connection = con;
try
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
GridResult.DataSource = dt;
GridResult.DataBind();
}
con.Close();
}
catch (Exception ex)
{
message = ex.Message;
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! following error occured : " + message.ToString() + "');", true);
}
}
private void fillResult(string sid, SqlConnection con)
{
SqlDataAdapter ad = new SqlDataAdapter("select SID,ExamSeason,Course,Part,SubID,Status from SExamMarks where SID='" + sid + "' order by Part,ExamSeason desc", con);
DataTable dt = new DataTable();
ad.Fill(dt);
GridResult.DataSource = dt;
GridResult.DataBind();
}
protected void btnSearchAgent_Click(object sender, EventArgs e)
{
// Currently available search fields.
int genderId = getIntValue(cboGender.SelectedValue, -1);
int cityId = getIntValue(cboAgentCity.SelectedValue, -1);
// Currently non-available search fields.
int employeeId = -1;
string firstName = string.Empty;
string lastName = string.Empty;
DateTime birthDate = default(DateTime);
string email = string.Empty;
string phone = string.Empty;
double salary = -1;
string username = string.Empty;
// The positionId: parameter is set to (int)Position.Agent in order to filter for Agents only.
EmployeeList agentList = new EmployeeList(employeeId, firstName, lastName, birthDate, email, genderId, phone, cityId,
(int)Position.Agent, salary, username);
DataSet dsAgents = agentList.AsDataSet();
if ((dsAgents != null) && (dsAgents.Tables[0].Rows.Count > 0))
{
GridResult.DataSource = dsAgents;
GridResult.DataBind();
LiteralResult.Text = "";
}
else
{
GridResult.DataSource = "";
//Building an HTML string.
StringBuilder html = new StringBuilder();
string msgNoResult = "<div class=\"alert alert-info alert-dismissable\">";
msgNoResult += "<a href=\"#\" class=\"close\" data-dismiss=\"alert\" aria-label=\"close\">×</a>";
msgNoResult += "Sorry, no results for your search.</div>";
html.Append(msgNoResult);
LiteralResult.Text = html.ToString();
}
}
private void BindGridResult()
{
#region 原方法
//OracleAccess access=new OracleAccess();
//StringBuilder strEmp=new StringBuilder();
//strEmp.Append(" select SR.train_class_subject_result_id, SR.train_class_subject_id,CS.PASS_RESULT,SR.result ,SR.ispass, ");
//strEmp.Append(" EE.Employee_ID,EE.Employee_Name ,PT.Post_Name ,EE.WORK_NO ,getorgname(EE.ORG_ID) ");
//strEmp.Append(" from zj_train_class_subject_result SR left join zj_train_class_subject CS ");
//strEmp.Append(" on CS.TRAIN_CLASS_SUBJECT_ID=SR.TRAIN_CLASS_SUBJECT_ID left join employee EE on EE.EMPLOYEE_ID=SR.Employee_Id");
//strEmp.AppendFormat(" left join post PT on PT.POST_ID=EE.Post_Id where SR.train_class_id={0} order by EE.Employee_ID", hidClassID.Value == "" ? 0 : Convert.ToInt32(hidClassID.Value));
//DataTable dtEmp = access.RunSqlDataSet(strEmp.ToString()).Tables[0];
//dtEmp.Columns.Add("isMore", typeof (int));
//dtEmp.Columns.Add("unit", typeof (string));
//dtEmp.Columns.Add("workshop", typeof(string));
//dtEmp.Columns.Add("group", typeof(string));
//string sqlSubject =
// string.Format("select train_class_subject_id,subject_name from zj_train_class_subject where train_class_id={0}",
// hidClassID.Value == "" ? 0 : Convert.ToInt32(hidClassID.Value));
//DataTable dtSub= access.RunSqlDataSet(sqlSubject.ToString()).Tables[0];
//foreach (DataRow r in dtSub.Rows)
//{
// dtEmp.Columns.Add(r["subject_name"].ToString(), typeof (string));
// foreach (DataRow empr in dtEmp.Rows)
// {
// if (empr["train_class_subject_id"].Equals(r["train_class_subject_id"]))
// {
// if(empr["result"].ToString()=="")
// empr[r["subject_name"].ToString()] = "";
// else
// empr[r["subject_name"].ToString()] = Convert.ToDouble(empr["result"]);
// }
// empr["isMore"] = 0;
// }
//}
// if(dtEmp.Columns.Count>14)
// {
// string empID = string.Empty;
// foreach (DataRow r in dtEmp.Rows)
// {
// if (empID != r["Employee_ID"].ToString())
// {
// string sql = string.Format("Employee_ID={0} ", Convert.ToInt32(r["Employee_ID"]));
// DataRow[] arr = dtEmp.Select(sql.ToString());
// if (arr.Length > 1)
// {
// for (int i = 14; i < dtEmp.Columns.Count; i++)
// {
// List<string > lst = new List<string>();
// for (int j = 0; j < arr.Length; j++)
// {
// if (!string.IsNullOrEmpty(arr[j][dtEmp.Columns[i].ColumnName].ToString()))
// {
// string strRes = Convert.ToDouble(arr[j][dtEmp.Columns[i].ColumnName]).ToString();
// if (!string.IsNullOrEmpty(strRes))
// {
// if (Convert.ToDouble(strRes) < Convert.ToDouble(arr[j]["PASS_RESULT"]))
// strRes = "<font color='red'>" + strRes + "</font>";
// lst.Add(strRes);
// }
// }
// }
// r[dtEmp.Columns[i].ColumnName] = string.Join("/", lst.ToArray());
// }
// }
// r["isMore"] = 1;
// empID = r["Employee_ID"].ToString();
// string[] strArr=r["getorgname(EE.ORG_ID)"].ToString().Split('-');
// if (strArr.Length>0)
// r["unit"] = strArr[0];
// if (strArr.Length > 1)
// r["workshop"] = strArr[1];
// if (strArr.Length > 2)
// r["group"] = strArr[2];
// }
// }
// }
// DataRow[] arrRow = dtEmp.Select("isMore=0");
// for (int i = 0; i < arrRow.Length; i++)
// {
// dtEmp.Rows.Remove(arrRow[i]);
// }
//DataTable newDT=new DataTable();
//newDT.Columns.Add("Employee_Name", typeof (string));
//newDT.Columns.Add("unit", typeof(string));
//newDT.Columns.Add("workshop", typeof(string));
//newDT.Columns.Add("group", typeof(string));
//newDT.Columns.Add("Post_Name", typeof(string));
//newDT.Columns.Add("WORK_NO", typeof(string));
//if (dtEmp.Columns.Count > 14)
//{
// for (int i = 14; i < dtEmp.Columns.Count; i++)
// {s
// newDT.Columns.Add(dtEmp.Columns[i].ColumnName, typeof(string));
// }
//}
//foreach (DataRow dr in dtEmp.Rows)
//{
// DataRow r = newDT.NewRow();
// r["Employee_Name"] = dr["Employee_Name"];
// r["unit"] = dr["unit"];
// r["workshop"] = dr["workshop"];
// r["group"] = dr["group"];
// r["Post_Name"] = dr["Post_Name"];
// r["WORK_NO"] = dr["WORK_NO"];
// if (dtEmp.Columns.Count > 14)
// {
// for (int i = 14; i < dtEmp.Columns.Count; i++)
// {
// r[dtEmp.Columns[i].ToString()] = dr[dtEmp.Columns[i].ToString()];
// }
// }
// newDT.Rows.Add(r);
//}
//newDT.Columns["Employee_Name"].ColumnName = "姓名";
//newDT.Columns["Post_Name"].ColumnName = "工作岗位";
//newDT.Columns["WORK_NO"].ColumnName = "员工编码";
//newDT.Columns["unit"].ColumnName = "单位";
//newDT.Columns["workshop"].ColumnName = "车间";
//newDT.Columns["group"].ColumnName = "班组";
//GridResult.EnableViewState = false;
//GridResult.DataSource = newDT;
//GridResult.DataBind();
#endregion
#region 新方法
OracleAccess access = new OracleAccess();
DataTable dtOrg = new DataTable();
if (hfID.Value != "")
{
string str1 =
"select b.SPONSOR_UNIT_ID,b.UNDERTAKE_UNIT_ID from ZJ_Train_Class a inner join ZJ_Train_Plan b on a.Train_Plan_ID=b.Train_Plan_ID where Train_Class_ID=" + hfID.Value;
dtOrg = access.RunSqlDataSet(str1).Tables[0];
}
//改班的科目
string sqlSubject =
string.Format("select train_class_subject_id,subject_name from zj_train_class_subject where train_class_id={0} order by train_class_subject_id desc",
hidClassID.Value == "" ? 0 : Convert.ToInt32(hidClassID.Value));
DataTable dtSub = access.RunSqlDataSet(sqlSubject.ToString()).Tables[0];
string sql = @" select r.employee_id,e.employee_name as 姓名,getorgname(e.org_id) as 组织机构,p.post_name as 工作岗位,e.work_no as 员工编码";
int x = 0;
Hashtable ht = new Hashtable();
foreach (DataRow r in dtSub.Rows)
{
sql += string.Format(@",
case when r.train_class_subject_id={0} then
case when result<s.pass_result then '<font color=''red''>'||result||'</font>' else to_char(result) end
end as {1}
", r["train_class_subject_id"], "subject" + x);
ht.Add("SUBJECT" + x, r["subject_name"]);
x++;
}
sql += string.Format(@"
from zj_train_class_subject_result r
left join employee e on r.employee_id=e.employee_id
left join post p on p.post_id=e.post_id
left join zj_train_class_subject s on s.train_class_subject_id=r.train_class_subject_id
where r.train_class_id={0}
", hidClassID.Value == "" ? 0 : Convert.ToInt32(hidClassID.Value));
if (dtOrg.Rows.Count > 0)
{
//当是站段单位登录时，只能查看本单位的学员名单
if (dtOrg.Rows[0]["SPONSOR_UNIT_ID"].ToString() != PrjPub.CurrentLoginUser.StationOrgID.ToString() &&
dtOrg.Rows[0]["UNDERTAKE_UNIT_ID"].ToString() != PrjPub.CurrentLoginUser.StationOrgID.ToString() &&
PrjPub.CurrentLoginUser.StationOrgID != 200)
{
sql += string.Format(" and GetStationOrgID(e.Org_ID)={0} ", PrjPub.CurrentLoginUser.StationOrgID);
}
}
sql += string.Format("order by r.employee_id,train_class_subject_result_id desc");
try
{
DataTable dt = access.RunSqlDataSet(sql).Tables[0];
//新Table对数据操作
DataTable dtNew = dt.Copy();
dtNew.Rows.Clear();
foreach (DataRow r in dt.Rows)
{
DataRow dr = dtNew.NewRow();
foreach (DataColumn c in dt.Columns)
{
dr[c.ToString()] = r[c.ToString()];
}
string str = "employee_id='" + r["employee_id"] + "'";
DataRow[] arr = dtNew.Select(str);
if (arr.Length == 0)
{
//新表中没有重复的学员信息
DataRow[] arrMore = dt.Select(str); //该学员的多条成绩
if (arrMore.Length > 0)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
foreach (DataRow dataRow in arrMore)
{
foreach (DataRow row in dtSub.Rows)
{
int i = dtNew.Columns.IndexOf(row["subject_name"].ToString());
if (i > -1)
{
if (dataRow[i].ToString().Trim() != "")
{
if (!dic.ContainsKey(row["subject_name"].ToString()))
{
dic.Add(row["subject_name"].ToString(), dataRow[i].ToString());
}
else
{
dic[row["subject_name"].ToString()] += "/" + dataRow[i];
}
dr[row["subject_name"].ToString()] = dic[row["subject_name"].ToString()];
}
}
}
}
}
dtNew.Rows.Add(dr);
}
}
for (int i = 0; i < dtNew.Columns.Count; i++)
{
if (ht.ContainsKey(dtNew.Columns[i].ColumnName))
{
dtNew.Columns[i].ColumnName = ht[dtNew.Columns[i].ColumnName].ToString();
}
}
//增加单位，车间，班组
DataTable dtInfo = new DataTable();
dtInfo.Columns.Add("姓名", typeof(string));
dtInfo.Columns.Add("单位", typeof(string));
dtInfo.Columns.Add("车间", typeof(string));
dtInfo.Columns.Add("班组", typeof(string));
dtNew.Columns.Remove("employee_id");
foreach (DataColumn cl in dtNew.Columns)
{
if (!cl.ColumnName.Equals("姓名"))
{
dtInfo.Columns.Add(cl.ColumnName, typeof(string));
}
}
foreach (DataRow r in dtNew.Rows)
{
DataRow dr = dtInfo.NewRow();
string[] strArr = r["组织机构"].ToString().Split('-');
if (strArr.Length > 0)
{
dr["单位"] = strArr[0];
}
if (strArr.Length > 1)
{
dr["车间"] = strArr[1];
}
if (strArr.Length > 2)
{
dr["班组"] = strArr[2];
}
foreach (DataColumn cl in dtNew.Columns)
{
dr[cl.ColumnName] = r[cl.ColumnName];
}
dtInfo.Rows.Add(dr);
}
dtInfo.Columns.Remove("组织机构");
GridResult.EnableViewState = false;
GridResult.DataSource = dtInfo;
GridResult.DataBind();
}
catch
{
}
#endregion
}
