/// <summary>
/// 查询编号为employeeId的人是否有未处理的签字单子
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
public static List<SHDJContract> SearchSignedHDJContract(Search search)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<SHDJContract> contracts = new List<SHDJContract>();
try
{
con.Open();
cmd = con.CreateCommand();
// SELECT h.id id, h.name name, h.submitdate submitdate, h.columndata1 columndata1
cmd.CommandText = SEARCH_SIGNED_CONTRACT_STR;
cmd.Parameters.AddWithValue("@EmployeeId", search.EmployeeId);
// 日期查询信息
if (search.DateBegin != null && search.DateEnd != null)
{
cmd.CommandText += " and hc.submitdate >= @DateBegin and hc.submitdate <= @DateEnd ";
cmd.Parameters.AddWithValue("@DateBegin", search.DateBegin);
cmd.Parameters.AddWithValue("@DateEnd", search.DateEnd);
}
else if (search.DateBegin != null && search.DateEnd == null)
{
cmd.CommandText += " and hc.submitdate >= @DateBegin ";
cmd.Parameters.AddWithValue("@DateBegin", search.DateBegin);
}
// 会签单编号的模糊查询
if(search.ConId != "") // 会签单编号不为空
{
//cmd.CommandText += " and hc.id like %@ConId% ";
//cmd.Parameters.AddWithValue("@ConId", search.ConId);
cmd.CommandText += " and hc.id like \"%" + search.ConId + "%\" ";
}
// 工程名称的模糊查询
if (search.ProjectName != "")
{
//cmd.CommandText += " and hc.columndata1 like %@ProjectName% ";
//cmd.Parameters.AddWithValue("@ProjectName", search.ProjectName);
cmd.CommandText += " and hc.columndata1 like \"%" + search.ProjectName + "%\" ";
}
if (search.Downloadable == 1) // 签字人想查询自己可以下载的所有会签单的信息
{
// 首先要求这个人有下载权限，其次要求这个会签单已经通过审核
cmd.CommandText += " and sl.candownload = 1 and st.totalresult = 1 ";
}
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
SHDJContract contract = new SHDJContract();
contract.Id = sqlRead["id"].ToString();
contract.Name = sqlRead["name"].ToString();
contract.ProjectName = sqlRead["projectname"].ToString();
contract.SubmitEmployeeName = sqlRead["subempname"].ToString();
contract.SubmitDate = sqlRead["submitdate"].ToString();
contract.SignDate = sqlRead["signdate"].ToString();
contract.SignRemark = sqlRead["signremark"].ToString();
if (int.Parse(sqlRead["signresult"].ToString()) == 1)
{
contract.SignResult = "同意";
}
else
{
contract.SignResult = "拒绝";
}
contracts.Add(contract);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return contracts;
}
/*
@"SELECT hc.id id, hc.name name, hc.submitdate submitdate, hc.columndata1 columndata1
FROM `hdjcontract` hc, `signaturestatus` st, `signaturelevel` sl
WHERE ((hc.id = st.conid and st.totalresult != 1)
and (sl.contempid = hc.contempid and sl.empid = @EmployeeId)
and (st.currlevel = sl.signlevel));";
*/
/// <summary>
/// 查询编号为employeeId的人是否有未处理的签字单子
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
public static List<SHDJContract> QueryUnsignContract(int employeeId)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<SHDJContract> contracts = new List<SHDJContract>();
try
{
con.Open();
cmd = con.CreateCommand();
// SELECT h.id id, h.name name, h.submitdate submitdate, h.columndata1 columndata1
cmd.CommandText = QUERT_UNSIGN_CONTRACT_STR;
cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
SHDJContract contract = new SHDJContract();
contract.Id = sqlRead["id"].ToString();
contract.Name = sqlRead["name"].ToString();
contract.SubmitDate = sqlRead["submitdate"].ToString();
contract.SubmitEmployeeName = sqlRead["subempname"].ToString();
contract.ProjectName = sqlRead["columndata1"].ToString();
contracts.Add(contract);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return contracts;
}
/// <summary>
/// 查询编号为employeeId的人是否有未处理的签字单子
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
public static List<SHDJContract> SearchAgreeHDJContract(Search search)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<SHDJContract> contracts = new List<SHDJContract>();
try
{
con.Open();
cmd = con.CreateCommand();
// SELECT h.id id, h.name name, h.submitdate submitdate, h.columndata1 columndata1
cmd.CommandText = SEARCH_SIGNATURE_AGREE_STR;
cmd.Parameters.AddWithValue("@EmployeeId", search.EmployeeId);
// 日期查询信息
if (search.DateBegin != null && search.DateEnd != null)
{
cmd.CommandText += " and h.submitdate >= @DateBegin and h.submitdate <= @DateEnd ";
cmd.Parameters.AddWithValue("@DateBegin", search.DateBegin);
cmd.Parameters.AddWithValue("@DateEnd", search.DateEnd);
}
else if (search.DateBegin != null && search.DateEnd == null)
{
cmd.CommandText += " and h.submitdate >= @DateBegin ";
cmd.Parameters.AddWithValue("@DateBegin", search.DateBegin);
}
// 会签单编号的模糊查询
if (search.ConId != "") // 会签单编号不为空
{
// cmd.CommandText += " and h.id like @ConId ";
// cmd.Parameters.AddWithValue("@ConId", search.ConId);
cmd.CommandText += " and h.id like \"%" + search.ConId + "%\" ";
}
// 会签单工程工程名称的模糊查询
if (search.ProjectName != "")
{
//cmd.CommandText += " and hc.columndata1 like %@ProjectName% ";
//cmd.Parameters.AddWithValue("@ProjectName", search.ProjectName);
cmd.CommandText += " and h.columndata1 like \"%" + search.ProjectName + "%\" ";
}
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
SHDJContract contract = new SHDJContract();
contract.Id = sqlRead["id"].ToString();
contract.Name = sqlRead["name"].ToString();
contract.SubmitDate = sqlRead["submitdate"].ToString();
//List<String> columnDatas = new List<String>();
//String columnData1 = sqlRead["columndata1"].ToString();
contract.ProjectName = sqlRead["columndata1"].ToString();
contract.CurrLevel = int.Parse(sqlRead["currlevel"].ToString());
contract.MaxLevel = int.Parse(sqlRead["maxlevel"].ToString());
contracts.Add(contract);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return contracts;
}
public static List<SHDJContract> QuerySignatureStatusRefuse(int employeeId)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<SHDJContract> contracts = new List<SHDJContract>();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = QUERY_SIGNATURE_STATUS_REFUSE_EMP_STR;
cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
SHDJContract contract = new SHDJContract();
contract.Id = sqlRead["id"].ToString();
contract.Name = sqlRead["name"].ToString();
contract.SubmitDate = sqlRead["submitdate"].ToString();
contract.ProjectName = sqlRead["columndata1"].ToString();
contract.CurrLevel = int.Parse(sqlRead["currlevel"].ToString());
contract.MaxLevel = int.Parse(sqlRead["maxlevel"].ToString());
contracts.Add(contract);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return contracts;
}