//查詢+顯示
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;
}
}