/// <summary>
/// 白名单列表
/// </summary>
/// <returns></returns>
public DataSet GetWhiteList(string PersonType, int pageIndex, int pageSize, SearchValueInfo sInfo)
{
string OrderBy = " ORDER BY LastModifyDate DESC ";
int start = (pageIndex - 1) * pageSize;
StringBuilder condition = new StringBuilder();
condition.AppendFormat(" WHERE Flag=1 AND PersonType='{0}' ", PersonType);
if (!string.IsNullOrEmpty(sInfo.name))
{
condition.AppendFormat(" and Name like '%{0}%' ", sInfo.name);
}
if (!string.IsNullOrEmpty(sInfo.IDCard))
{
condition.AppendFormat(" and IDCard like '%{0}%' ", sInfo.IDCard);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" SELECT top {0} ROW_NUMBER() OVER( {1} ) RowNumber, * ", pageSize, OrderBy);
sql.Append(" FROM dbo.Personnel ");
sql.Append(condition);
sql.AppendFormat(" and DBID not in ( SELECT top {0} DBID from Personnel {1} {2} ) {2} ", start, condition, OrderBy);
sql.Append(" ; SELECT COUNT(*) FROM Personnel ");
sql.Append(condition);
return(db.GetDataSet(sql.ToString(), connectionStr));
}
/// <summary>
/// 进出记录
/// </summary>
/// <returns></returns>
public DataSet GetInOutRecod(int pageIndex, int pageSize, SearchValueInfo sInfo)
{
int start = (pageIndex - 1) * pageSize;
string OrderBy = " ORDER BY r.InOutTime DESC ";
StringBuilder condition = new StringBuilder();
condition.Append(" WHERE r.Flag=1 ");
//双击人员查询
if (!string.IsNullOrEmpty(sInfo.IDCard))
{
condition.AppendFormat(" AND r.IDCard like '%{0}%' ", sInfo.IDCard);
}
if (!string.IsNullOrEmpty(sInfo.beginDate.PaseToString()))
{
condition.AppendFormat(" AND r.InOutTime >='{0}' ", sInfo.beginDate.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(sInfo.endDate.PaseToString()))
{
condition.AppendFormat(" AND r.InOutTime <='{0}' ", sInfo.endDate.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(sInfo.name.PaseToString()))
{
condition.AppendFormat(" AND p.Name like '%{0}%' ", sInfo.name);
}
if (!string.IsNullOrEmpty(sInfo.inOutType))
{
condition.AppendFormat(" AND r.InOutType='{0}' ", sInfo.inOutType);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" SELECT top {0} ROW_NUMBER() OVER( {1} ) RowNumber ", pageSize, OrderBy);
sql.Append(" ,r.InOutType ");
sql.Append(" ,Code_IOType.CName as InOutTypeName ");
sql.Append(" ,r.InOutTime ");
sql.Append(" ,r.CompareResult ");
sql.Append(" ,r.ThroughWay ");
sql.Append(" ,r.SceneImage ");
sql.Append(" ,Code_Tway.CName as ThroughWayName ");
sql.Append(" ,p.Name,p.IDCard ");
sql.Append(" FROM dbo.InOutRecord r ");
sql.Append(" LEFT JOIN Personnel p ON p.IDCard=r.IDCard AND p.Flag=1 ");
sql.Append(" Left join Code Code_IOType on Code_IOType.CodeID=r.InOutType and Code_IOType.KindCode='InOutType' ");
sql.Append(" Left join Code Code_Tway on Code_Tway.CodeID=r.ThroughWay and Code_Tway.KindCode='ThroughWay' ");
sql.Append(condition);
sql.AppendFormat(" and r.DBID not in ( SELECT top {0} r.DBID from InOutRecord r ", start);
sql.AppendFormat(" LEFT JOIN Personnel p ON p.IDCard=r.IDCard AND p.Flag=1 {0} {1} ) {1} ", condition, OrderBy);
sql.Append(" ; SELECT COUNT(1) ");
sql.Append(" FROM dbo.InOutRecord r ");
sql.Append(" LEFT JOIN Personnel p ON p.IDCard=r.IDCard AND p.Flag=1 ");
sql.Append(condition);
return(db.GetDataSet(sql.ToString(), connectionStr));
}
/// <summary>
/// 数据加载（附带页面条件）
/// </summary>
public void LoadDataGridViewData()
{
int pageIndex = this.pager1.PageIndex;//当前页
if (pageIndex < 1)
{
pageIndex = 1;
}
int pageSize = 100; //一页条数
int pageCount = 0; //总页数
int recordCount = 0; //总条数
SearchValueInfo sInfo = new SearchValueInfo();
sInfo.name = textBox_name.Text.Trim(); //姓名
sInfo.IDCard = textBox_IDCard.Text.Trim(); //身份证
sInfo.beginDate = dateTimePicker1.Value; //开始时间
sInfo.endDate = dateTimePicker2.Value; //结束时间
sInfo.inOutType = this.comboBox1.SelectedValue.PaseToString(); //进/出
DataSet ds = dal.GetInOutRecod(pageIndex, pageSize, sInfo);
ds.Tables[0].Columns.Add("CompareGrade", Type.GetType("System.String"));
DataTable CodeDt = dal.GetCode("CompareGrade");
foreach (DataRow dr in ds.Tables[0].Rows)
{
foreach (DataRow cdr in CodeDt.Rows)
{
if (dr["CompareResult"].PaseToDecmial() * 100 >= cdr["CodeID"].PaseToDecmial())
{
dr["CompareGrade"] = cdr["CName"].PaseToString();
break;
}
}
}
if (!string.IsNullOrEmpty(ds.Tables[1].PaseToString()) && !string.IsNullOrEmpty(ds.Tables[1].Rows[0][0].ToString()))
{
recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
pageCount = recordCount / pageSize;
}
pager1.PageSize = pageSize;
pager1.RecordCount = recordCount;
pager1.Page();
dataGridView1.DataSource = ds.Tables[0];
if (dataGridView1.SelectedRows.Count > 0)
{
dataGridView1.SelectedRows[0].Selected = false;
}
}
/// <summary>
/// 数据加载
/// </summary>
public void LoadDataGridViewData()
{
int pageIndex = this.pager1.PageIndex;//当前页
if (pageIndex < 1)
{
pageIndex = 1;
}
int pageSize = 50; //一页条数
int pageCount = 0; //总页数
int recordCount = 0; //总条数
string Name = this.textBox_Name.Text;
string IDCard = this.textBox_IDCard.Text;
//List<string> searchValue = new List<string>();
//searchValue.Add(Name);
//searchValue.Add(IDCard);
SearchValueInfo sInfo = new SearchValueInfo();
sInfo.name = Name;
sInfo.IDCard = IDCard;
DataSet ds = dal.GetWhiteList(PersonType, pageIndex, pageSize, sInfo);
if (!string.IsNullOrEmpty(ds.Tables[1].Rows[0][0].ToString()))
{
recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
pageCount = recordCount / pageSize;
}
pager1.PageSize = pageSize;
pager1.RecordCount = recordCount;
pager1.Page();
dataGridView1.DataSource = ds.Tables[0];
if (dataGridView1.SelectedRows.Count > 0)
{
dataGridView1.SelectedRows[0].Selected = false;
}
}
