/// <summary>
/// 获得数据List
/// </summary>
public GuideSignArray GetSignDataList(GuideSignQryParam QueryCondition)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM VIEW_Sign_GuideGroup where 1=1");
strSql.Append(GetWhere(QueryCondition));
strSql.Append(" order by dtSignInTime desc");
SqlParameter[] sqlParams =
{
new SqlParameter("strTrainmanName", QueryCondition.strTrainmanName),
new SqlParameter("strWorkShopGUID", QueryCondition.strWorkShopGUID),
new SqlParameter("strGuideGroupGUID", QueryCondition.strGuideGroupGUID),
new SqlParameter("dtSignTimeBegin", QueryCondition.dtSignTimeBegin),
new SqlParameter("dtSignTimeEnd", QueryCondition.dtSignTimeEnd),
new SqlParameter("nSignFlag", QueryCondition.nSignFlag),
new SqlParameter("strTrainmanNumber", "%" + QueryCondition.strTrainmanNumber + "%")
};
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), sqlParams).Tables[0];
GuideSignArray list = new GuideSignArray();
foreach (DataRow dr in dt.Rows)
{
GuideSignEntity _Sign_GuideGroup = new GuideSignEntity();
GuideSignDataRowToModel(_Sign_GuideGroup, dr);
list.Add(_Sign_GuideGroup);
}
return(list);
}
public GuideSignArray GetNotSignDataList(GuideSignQryParam QueryCondition)
{
StringBuilder strSql = new StringBuilder();
StringBuilder sqlCondition = new StringBuilder();
sqlCondition.Append("select strTrainmanNumber From VIEW_Sign_GuideGroup where 1=1");
sqlCondition.Append(QueryCondition.dtSignTimeBegin >= DateTimeMinValue.dtMinValue && QueryCondition.dtSignTimeEnd >= DateTimeMinValue.dtMinValue
? " and (dtSignInTime >= @dtSignTimeBegin and dtSignInTime <= @dtSignTimeEnd)" : "");
strSql.Append("select strTrainmanNumber,strTrainmanName from TAB_Org_Trainman ");
strSql.Append(" where strGuideGroupGUID=@strGuideGroupGUID and strTrainmanNumber not in (" + sqlCondition + ") order by strTrainmanNumber");
SqlParameter[] sqlParams =
{
new SqlParameter("strGuideGroupGUID", QueryCondition.strGuideGroupGUID),
new SqlParameter("dtSignTimeBegin", QueryCondition.dtSignTimeBegin),
new SqlParameter("dtSignTimeEnd", QueryCondition.dtSignTimeEnd)
};
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), sqlParams).Tables[0];
GuideSignArray list = new GuideSignArray();
foreach (DataRow dr in dt.Rows)
{
GuideSignEntity _Sign_GuideGroup = new GuideSignEntity();
_Sign_GuideGroup.strTrainmanNumber = ObjectConvertClass.static_ext_string(dr["strTrainmanNumber"]);
_Sign_GuideGroup.strTrainmanName = ObjectConvertClass.static_ext_string(dr["strTrainmanName"]);
list.Add(_Sign_GuideGroup);
}
return(list);
}
public string GetWhere(GuideSignQryParam model)
{
StringBuilder strSqlOption = new StringBuilder();
strSqlOption.Append(!string.IsNullOrEmpty(model.strTrainmanName) ? " and strTrainmanName = @strTrainmanName" : "");
strSqlOption.Append(!string.IsNullOrEmpty(model.strWorkShopGUID) ? " and strWorkShopGUID = @strWorkShopGUID" : "");
strSqlOption.Append(!string.IsNullOrEmpty(model.strGuideGroupGUID) ? " and strGuideGroupGUID = @strGuideGroupGUID" : "");
strSqlOption.Append(model.dtSignTimeBegin >= DateTimeMinValue.dtMinValue && model.dtSignTimeEnd >= DateTimeMinValue.dtMinValue ?
" and (dtSignInTime >= @dtSignTimeBegin and dtSignInTime <= @dtSignTimeEnd)" : "");
strSqlOption.Append(model.nSignFlag > 0 ? " and nSignInFlag = @nSignFlag" : "");
strSqlOption.Append(!string.IsNullOrEmpty(model.strTrainmanNumber) ? " and strTrainmanNumber like @strTrainmanNumber" : "");
return(strSqlOption.ToString());
}