public static void BindPosition(ComboBox cmbPos, int DeptId)
{
string sSql = "select pId,positionName from Position ";
if (DeptId != 0)
{
sSql = sSql + " where DeptId=" + DeptId;
}
sqlHelper helper = new sqlHelper();
DataSet ds = helper.ExecuteDataSet(sSql, helper.connectionStr, "position");
if (DeptId == 0)
{
DataRow row = ds.Tables["position"].NewRow();
row[0] = 0;
row[1] = "所有职位";
ds.Tables["position"].Rows.InsertAt(row, 0);
}
DataRow row1 = ds.Tables["position"].NewRow();
row1[0] = -1;
row1[1] = "请选择";
ds.Tables["position"].Rows.InsertAt(row1, 0);
cmbPos.DisplayMember = "positionName";
cmbPos.ValueMember = "pId";
cmbPos.DataSource = ds.Tables["position"];
}
public void BindEmploryeeinfo()
{
StringBuilder sb = new StringBuilder();
sb.Append("select EpId,EpName,case gender when '1' then '男' when '0' then '女' end gender,officePhone,MobilePhone,de.DeptName,po.positionName ");
sb.Append(" from Emploryee em ,Depart de,Position po ");
sb.Append("where em.DptId=de.DeptId and em.PId=po.pId ");
if (txtEmploryeeName.Text.Trim() != string.Empty)
{
sb.AppendFormat(" and EpName like '%{0}%' ", txtEmploryeeName.Text.Trim());
}
if (rbtBoy.Checked)
{
sb.Append(" and [gender]='1' ");
}
if (rbtGirl.Checked)
{
sb.Append(" and [gender]='0' ");
}
if (Convert.ToInt32(cmbDepart.SelectedValue) != 0 && Convert.ToInt32(cmbDepart.SelectedValue) != -1)
{
sb.AppendFormat(" and em.DptId={0}", (int)cmbDepart.SelectedValue); //
}
if (Convert.ToInt32(cmbPosition.SelectedValue) != -1 && Convert.ToInt32(cmbPosition.SelectedValue) != 0)
{
sb.AppendFormat(" and em.PId={0}", (int)cmbPosition.SelectedValue);
}
sb.Append(" and flag= 0 ");
sqlHelper helper = new sqlHelper();
dsStu = helper.ExecuteDataSet(helper.connectionStr, sb.ToString(), null);
dgvEmploryeeInfo.AutoGenerateColumns = false;
dgvEmploryeeInfo.DataSource = dsStu.Tables[0];
}
public void BindAcceptMessage()
{
StringBuilder sSql = new StringBuilder();
sSql.Append("select MessageId,MessageContent,[AceptEmploryId],[PId],[DptId],EpName,DeptName,positionName,SendTime,[flag], ");
sSql.Append("case IsRead when 0 then '未读' when 1 then '已读' end as IsRead from vw_AcceptMessage ");
//sSql.Append("select MessageId,DeptName,positionName,EpName,MessageContent,SendTime,");
//sSql.Append("case IsRead when 0 then '未读' when 1 then '已读' end as IsRead ");
//sSql.Append("from(select EpId,EpName,DeptName,positionName,em.DptId as Dept,em.PId as PId,flag ");
//sSql.Append("from Emploryee em,Depart de,Position po ");
//sSql.Append("where em.DptId=de.DeptId and em.PId=po.pId ) newEm,Message me ");
//sSql.Append("where newEm.EpId=me.SendEmploryId ");
sSql.AppendFormat(" where AceptEmploryId= {0}", Common.EpId);
if ((int)cmbDept.SelectedValue != 0 && (int)cmbDept.SelectedValue != -1)
{
sSql.Append(" and [DptId]= ");
sSql.Append((int)cmbDept.SelectedValue);
if ((int)cmbPosition.SelectedValue != 0 && (int)cmbPosition.SelectedValue != -1)
{
sSql.Append(" and [PId]= ");
sSql.Append((int)cmbPosition.SelectedValue);
}
}
if ((int)cmbDept.SelectedValue == 0 && (int)cmbPosition.SelectedValue != -1 && (int)cmbPosition.SelectedValue != 0)
{
sSql.AppendFormat(" and [PId]={0} ", (int)cmbPosition.SelectedValue);
}
if (txtSendName.Text.Trim() != string.Empty)
{
sSql.Append(" and EpName like '%");
sSql.Append(txtSendName.Text.Trim());
sSql.Append("%'");
}
sSql.Append(" and flag=0 ");
sSql.Append(" order by SendTime desc ");
sqlHelper helper = new sqlHelper();
try
{
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sSql.ToString(), "SendEmp");
dgvMessage.AutoGenerateColumns = false;
dgvMessage.DataSource = ds.Tables["SendEmp"];
}
catch (Exception)
{
MessageBox.Show("系统维护！", Common.APTION);
}
}
public static void BindAllDepart(ComboBox cmbDepart)
{
string sSql = "select DeptId,DeptName from Depart ";
sqlHelper helper = new sqlHelper();
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sSql, "Depart");
DataRow row = ds.Tables["Depart"].NewRow();
row[0] = 0;
row[1] = "请选择";
ds.Tables["Depart"].Rows.InsertAt(row, 0);
cmbDepart.DisplayMember = "DeptName";
cmbDepart.ValueMember = "DeptId";
cmbDepart.DataSource = ds.Tables["Depart"];
}
/// <summary>
/// 已读，并按时间排序
/// </summary>
public void BindIsReadMessage()
{
StringBuilder sSql = new StringBuilder();
sSql.Append("select MessageId,MessageContent,EpName,DeptName,positionName,SendTime, ");
sSql.Append("case IsRead when 0 then '未读' when 1 then '已读' end as IsRead from vw_AcceptMessage ");
sSql.AppendFormat(" where AceptEmploryId= {0}", Common.EpId);
sSql.Append(" and IsRead=1");
sSql.Append(" order by SendTime desc ");
sqlHelper helper = new sqlHelper();
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sSql.ToString(), "IsReadMessage");
dgvMessage.AutoGenerateColumns = false;
dgvMessage.DataSource = ds.Tables["ISReadMessage"];
}
public static void BindAllPosition(ComboBox cmbPosition, int DeptId)
{
StringBuilder sSql = new StringBuilder();
sSql.Append("select pId,positionName from Position ");
sSql.AppendFormat(" where DeptId={0}", DeptId);
sqlHelper helper = new sqlHelper();
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sSql.ToString(), "position");
DataRow row = ds.Tables["position"].NewRow();
row[0] = 0;
row[1] = "请选择";
ds.Tables["position"].Rows.InsertAt(row, 0);
cmbPosition.DisplayMember = "positionName";
cmbPosition.ValueMember = "pId";
cmbPosition.DataSource = ds.Tables["position"];
}
protected void BindSendEmplory()
{
sqlHelper helper = new sqlHelper();
StringBuilder sSql = new StringBuilder();
sSql.Append(" select EpId,EpName,case gender when 0 then '女' when 1 then '男' end as Gender,DeptName,positionName ");
sSql.Append(" from Emploryee em,Depart de,Position po ");
sSql.Append(" where em.DptId=de.DeptId and em.PId=po.pId ");
//sSql.Append(" and po.deptId=de.DeptId ");
//sSql.Append("select EpId,EpName,case gender when 0 then '女' when 1 then '男' end as Gender,DeptName,positionName");
//sSql.Append(" FROM [hr].[dbo].[vw_SendEmplory]");
sSql.Append(" and flag=0 ");
//分步条件加条件筛选信息
if ((int)cmbDept.SelectedValue != 0 && (int)cmbDept.SelectedValue != -1)
{
sSql.Append(" and em.DptId= ");
sSql.Append((int)cmbDept.SelectedValue);
if ((int)cmbPosition.SelectedValue != 0 && (int)cmbPosition.SelectedValue != -1)
{
sSql.Append(" and em.pId= ");
sSql.Append((int)cmbPosition.SelectedValue);
}
}
if ((int)cmbDept.SelectedValue == 0 && (int)cmbPosition.SelectedValue != -1 && (int)cmbPosition.SelectedValue != 0)
{
sSql.AppendFormat(" and em.pId= {0} ", (int)cmbPosition.SelectedValue);
}
if (txtEmploryName.Text.Trim() != string.Empty)
{
sSql.Append(" and EpName like '%");
sSql.Append(txtEmploryName.Text.Trim());
sSql.Append("%'");
}
try
{
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sSql.ToString(), "SendEmp");
dgvEmplory.AutoGenerateColumns = false;
dgvEmplory.DataSource = ds.Tables["SendEmp"];
}
catch (Exception)
{
MessageBox.Show("系统维护！", Common.APTION);
}
}
public void QueryDepart()
{
StringBuilder sSql = new StringBuilder();
sSql.Append("select DeptId,DeptName,parentId from Depart ");
if (txtDepartName.Text.Trim() != string.Empty)
{
sSql.Append("where deptName like '%");
sSql.Append(txtDepartName.Text.Trim());
sSql.Append("%' ");
}
sSql.Append(" order by DeptId DESC");
sqlHelper helper = new sqlHelper();
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sSql.ToString(), "Depart");
dgvDepart.AutoGenerateColumns = false;
dgvDepart.DataSource = ds.Tables["Depart"];
}
public void BindAssessExample()
{
StringBuilder sb = new StringBuilder();
sb.Append("select AssesExampleId,Title,TotalScore,case IsPass when 0 then '正在审核' when 1 then '已通过' end as IsPass from AssesExample");
if (txtTitle.Text != string.Empty)
{
sb.Append(" where Title like '%");
sb.Append(txtTitle.Text.Trim());
sb.Append(" %'");
}
sb.Append(" order by AssesExampleId DESC");
sqlHelper helper = new sqlHelper();
DataSet ds = helper.ExecuteDataSet(helper.connectionStr, sb.ToString(), null);
dgvAE.AutoGenerateColumns = false;
dgvAE.DataSource = ds.Tables[0];
}