//查詢+顯示 private void DoQuery() { //lblMsg.Visible = false; var isSuccess = false; var msg = ""; var EmpDataLists = new List <Util_EmpDataBean>(); var EmpData = new Util_EmpDataBean(); EmpData.EmpID = txtQueryString.Text; EmpData.outEmpID = _outEmpID; isSuccess = LoadEmp(EmpData, out EmpDataTemp, out msg); if (isSuccess && EmpDataTemp != null) { if (EmpDataTemp.Count == 1) { SetSelectedIndex(ddlEmpCompID, EmpDataTemp[0].CompID); DDLChanged("CompID"); SetSelectedIndex(ddlEmpOrganID, EmpDataTemp[0].OrganID); DDLChanged("OrganID"); SetSelectedIndex(ddlEmpID, EmpDataTemp[0].EmpID); DDLChanged("EmpID"); } else { gvMain.DataSource = EmpDataTemp; gvMain.DataBind(); gvMain.Visible = true; } } }
public static bool LoadEmp(Util_EmpDataBean model, out List <Util_EmpDataBean> datas, out string msg) { bool result = false; msg = ""; datas = new List <Util_EmpDataBean>(); try { using (var conn = new SqlConnection() { ConnectionString = DbHelper.getConnectionStrings(_eHRMSDB_ITRD).ConnectionString }) { try { datas = conn.Query <Util_EmpDataBean>(LoadEmpSQL(model), model).ToList(); } catch (Exception) { throw; } } result = true; } catch (Exception ex) { msg = ex.Message; } return(result); }
public static string LoadEmpSQL(Util_EmpDataBean model) { StringBuilder sb = new StringBuilder(); sb.AppendLine("select isnull(P.CompID,'') as CompID,isnull(C.CompName,'') as CompName "); sb.AppendLine(",isnull(P.EmpID,'') as EmpID,isnull(P.NameN,'') as EmpNameN ,isnull(P.EmpID+'-'+P.NameN,'') as ddlEmpName "); sb.AppendLine(",isnull(P.DeptID,'') as DeptID,isnull(Dpt.OrganName,'') as DeptName "); sb.AppendLine(",isnull(P.OrganID,'') as OrganID,isnull(Org.OrganName,'') as OrganName "); sb.AppendLine(",isnull(WW.WorkTypeID,'') as WorkTypeID,isnull(WW.Remark,'') as WorkType "); sb.AppendLine(",isnull(PP.PositionID,'') as PositionID,isnull(PP.Remark,'') as Position "); sb.AppendLine(",isnull(OrgF.OrganID,'') as FlowOrganID,isnull(OrgF.OrganName,'') as FlowOrganName "); sb.AppendLine(",isnull(P.TitleID,'') as TitleID,isnull(T.TitleName,'') as TitleName "); sb.AppendLine(",right(REPLICATE('0',8)+ CAST( ROW_NUMBER() over (order by P.EmpID) as VARCHAR),8) as _Key "); sb.AppendLine("from dbo.Personal P "); sb.AppendLine("left join dbo.EmpFlow EF on P.CompID=EF.CompID and P.EmpID=EF.EmpID "); sb.AppendLine("left join dbo.OrganizationFlow OrgF on EF.OrganID=OrgF.OrganID "); sb.AppendLine("left join dbo.EmpWorkType EW on P.CompID=EW.CompID and P.EmpID=EW.EmpID and EW.PrincipalFlag='1' "); sb.AppendLine("left join dbo.WorkType WW on EW.CompID=WW.CompID and EW.WorkTypeID=WW.WorkTypeID "); sb.AppendLine("left join dbo.EmpPosition EP on P.CompID=EP.CompID and P.EmpID=EP.EmpID and EP.PrincipalFlag='1' "); sb.AppendLine("left join dbo.Position PP on EP.CompID=PP.CompID and EP.PositionID=PP.PositionID "); sb.AppendLine("left join dbo.Company C on P.CompID=C.CompID "); sb.AppendLine("left join dbo.Organization Dpt on P.CompID=Dpt.CompID and P.DeptID=Dpt.OrganID "); sb.AppendLine("left join dbo.Organization Org on P.CompID=Org.CompID and P.OrganID=Org.OrganID "); sb.AppendLine("left join dbo.Title T on P.CompID=T.CompID and P.TitleID=T.TitleID and P.RankID=T.RankID "); sb.AppendLine("where P.WorkStatus='1' "); if (!String.IsNullOrEmpty(model.CompID)) { sb.AppendLine("and P.CompID=@CompID"); } if (!String.IsNullOrEmpty(model.OrganID)) { sb.AppendLine("and P.OrganID=@OrganID"); } if (!String.IsNullOrEmpty(model.EmpID)) { sb.AppendLine("and P.EmpID like '%" + model.EmpID + "%'"); } if (!String.IsNullOrEmpty(model.outEmpID)) { sb.AppendLine("and P.EmpID!=@outEmpID"); } return(sb.ToString()); }
private void DDLChanged(string e) { var isSuccess = false; var msg = ""; var OrganData = new Util_OrganDataBean(); var EmpDataLists = new List <Util_EmpDataBean>(); var EmpData = new Util_EmpDataBean(); EmpData.outEmpID = _outEmpID; switch (e) { case "": DDLClear(e); isSuccess = LoadOrgan(OrganData, out OrganDataTemp, out msg); if (isSuccess && OrganDataTemp != null && OrganDataTemp.Count > 0) { ddlEmpCompID.DataSource = OrganDataTemp .Select(x => new { x.CompID, x.CompName }).Distinct().ToList(); ddlEmpCompID.DataTextField = "CompName"; ddlEmpCompID.DataValueField = "CompID"; ddlEmpCompID.DataBind(); ddlEmpCompID.Items.Insert(0, new ListItem("---請選擇---", "")); SetSelectedIndex(ddlEmpCompID, _CompID); } break; case "CompID": ddlEmpOrganID.DataSource = OrganDataTemp .Where(x => x.CompID == ddlEmpCompID.SelectedValue) .Select(x => new { x.OrganID, x.OrganName }).Distinct().ToList(); ddlEmpOrganID.DataTextField = "OrganName"; ddlEmpOrganID.DataValueField = "OrganID"; ddlEmpOrganID.DataBind(); ddlEmpOrganID.Items.Insert(0, new ListItem("---請選擇---", "")); SetSelectedIndex(ddlEmpOrganID, _OrganID); break; case "OrganID": //撈取DDLEmpID的內容---------------- EmpData.CompID = ddlEmpCompID.SelectedValue; EmpData.OrganID = ddlEmpOrganID.SelectedValue; isSuccess = LoadEmp(EmpData, out EmpDataTemp, out msg); if (isSuccess && EmpDataTemp != null && EmpDataTemp.Count > 0) { ddlEmpID.DataSource = EmpDataTemp.Select(x => new { x.EmpID, x.ddlEmpName }).Distinct().ToList(); ddlEmpID.DataTextField = "ddlEmpName"; ddlEmpID.DataValueField = "EmpID"; ddlEmpID.DataBind(); ddlEmpID.Items.Insert(0, new ListItem("---請選擇---", "")); } break; case "EmpID": if (EmpDataTemp != null && EmpDataTemp.Count > 0) { var EmpTemp = EmpDataTemp.Find(x => x.EmpID.Contains(ddlEmpID.SelectedValue)); Dictionary <string, string> dctEmpData = new Dictionary <string, string>(); dctEmpData.Add("CompID", EmpTemp.CompID); dctEmpData.Add("CompName", EmpTemp.CompName); dctEmpData.Add("EmpID", EmpTemp.EmpID); dctEmpData.Add("EmpNameN", EmpTemp.EmpNameN); dctEmpData.Add("DeptID", EmpTemp.DeptID); dctEmpData.Add("DeptName", EmpTemp.DeptName); dctEmpData.Add("OrganID", EmpTemp.OrganID); dctEmpData.Add("OrganName", EmpTemp.OrganName); dctEmpData.Add("WorkTypeID", EmpTemp.WorkTypeID); dctEmpData.Add("WorkType", EmpTemp.WorkType); dctEmpData.Add("PositionID", EmpTemp.PositionID); dctEmpData.Add("Position", EmpTemp.Position); dctEmpData.Add("FlowOrganID", EmpTemp.FlowOrganID); dctEmpData.Add("FlowOrganName", EmpTemp.FlowOrganName); dctEmpData.Add("TitleID", EmpTemp.TitleID); dctEmpData.Add("TitleName", EmpTemp.TitleName); ViewState[ModalPopupClientID + "_Data"] = dctEmpData; } break; } }