/// <summary>
/// 获取指定司机工号的签到信息
/// </summary>
/// <param name="strTrainmanNumber">人员工号</param>
/// <param name="strGuideGroupGUID">指导队ID</param>
/// <param name="bExist">返回参数，是否存在</param>
/// <returns></returns>
public GuideSignEntity GetSignRecordByTrainmanNumber(string strTrainmanNumber, string strGuideGroupGUID, out Boolean bExist)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from VIEW_Sign_GuideGroup where strTrainmanNumber=@strTrainmanNumber and strGuideGroupGUID=@strGuideGroupGUID order by dtSignInTime desc");
SqlParameter[] sqlParams =
{
new SqlParameter("strTrainmanNumber", strTrainmanNumber),
new SqlParameter("strGuideGroupGUID", strGuideGroupGUID)
};
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), sqlParams).Tables[0];
GuideSignEntity _Sign_GuideGroup = null;
if (dt.Rows.Count > 0)
{
bExist = true;
_Sign_GuideGroup = new GuideSignEntity();
GuideSignDataRowToModel(_Sign_GuideGroup, dt.Rows[0]);
}
else
{
bExist = false;
}
return(_Sign_GuideGroup);
}
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);
}
/// <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 bool AddGuideSignIn(GuideSignEntity model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into TAB_Sign_GuideGroup");
strSql.Append("(strTrainmanNumber,strTrainmanName,strWorkShopGUID,strGuideGroupGUID,nSignInFlag");
strSql.Append(!string.IsNullOrEmpty(model.strGuideSignGUID) ? ",strGuideSignGUID" : "");
strSql.Append(model.dtSignInTime >= DateTimeMinValue.dtMinValue ? ",dtSignInTime" : "");
strSql.Append(")");
strSql.Append("values(@strTrainmanNumber,@strTrainmanName,@strWorkShopGUID,@strGuideGroupGUID,@nSignInFlag");
strSql.Append(!string.IsNullOrEmpty(model.strGuideSignGUID) ? ",@strGuideSignGUID" : "");
strSql.Append(model.dtSignInTime >= DateTimeMinValue.dtMinValue ? ",@dtSignInTime" : "");
strSql.Append(")");
SqlParameter[] parameters =
{
new SqlParameter("@strGuideSignGUID", model.strGuideSignGUID),
new SqlParameter("@strTrainmanNumber", model.strTrainmanNumber),
new SqlParameter("@strTrainmanName", model.strTrainmanName),
new SqlParameter("@strWorkShopGUID", model.strWorkShopGUID),
new SqlParameter("@strGuideGroupGUID", model.strGuideGroupGUID),
new SqlParameter("@dtSignInTime", model.dtSignInTime),
new SqlParameter("@nSignInFlag", model.nSignInFlag)
};
return(SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), parameters) > 0);
}
/// <summary>
/// 读取DataRow数据到Model中
/// </summary>
private void GuideSignDataRowToModel(GuideSignEntity model, DataRow dr)
{
model.strGuideSignGUID = ObjectConvertClass.static_ext_string(dr["strGuideSignGUID"]);
model.strTrainmanNumber = ObjectConvertClass.static_ext_string(dr["strTrainmanNumber"]);
model.strTrainmanName = ObjectConvertClass.static_ext_string(dr["strTrainmanName"]);
model.strWorkShopGUID = ObjectConvertClass.static_ext_string(dr["strWorkShopGUID"]);
model.strWorkShopName = ObjectConvertClass.static_ext_string(dr["strWorkShopName"]);
model.strGuideGroupGUID = ObjectConvertClass.static_ext_string(dr["strGuideGroupGUID"]);
model.strGuideGroupName = ObjectConvertClass.static_ext_string(dr["strGuideGroupName"]);
model.dtSignInTime = ObjectConvertClass.static_ext_date(dr["dtSignInTime"]);
model.nSignInFlag = ObjectConvertClass.static_ext_int(dr["nSignInFlag"]);
model.dtSignOutTime = ObjectConvertClass.static_ext_date(dr["dtSignOutTime"]);
model.nSignOutFlag = ObjectConvertClass.static_ext_int(dr["nSignOutFlag"]);
}
public bool AddGuideSignOut(GuideSignEntity model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update TAB_Sign_GuideGroup set");
strSql.Append(" nSignOutFlag = @nSignOutFlag");
strSql.Append(model.dtSignOutTime >= DateTimeMinValue.dtMinValue ? ",dtSignOutTime = @dtSignOutTime" : "");
strSql.Append(" where strGuideSignGUID=@strGuideSignGUID");
SqlParameter[] parameters =
{
new SqlParameter("@strGuideSignGUID", model.strGuideSignGUID),
new SqlParameter("@dtSignOutTime", model.dtSignOutTime),
new SqlParameter("@nSignOutFlag", model.nSignOutFlag)
};
return(SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), parameters) > 0);
}