/// <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;
}
/// <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;
}
/// SELECT w.contractid, w.itemid, i.item, w.work, w.expense FROM `workload` w, `item` i WHERE w.itemid = i.id AND `contractid` like "申%" AND `itemid` = 1
/// SELECT w.contractid, p.id projectid, p.project, i.id itemid, i.item,w.work, w.expense FROM `workload` w, `item` i, `project` p WHERE w.itemid = i.id AND i.projectid = p.id AND `contractid` like "申%" AND `itemid` = 1
/// <summary>
/// 统计工作量的信息[Search数据填写SDepartmentShortCall + ItemId]
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static List<ContractWorkload> GetSDepartmentItemWorkload(Search search)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<ContractWorkload> workloads = new List<ContractWorkload>();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = QUERY_SDEPARTMENT_ITEM_YEAR_WORKLOAD_STR;
cmd.Parameters.AddWithValue("@SDepartmentYear", search.SDepartmentShortlCall + "_" + search.Year.ToString() + "%");
cmd.Parameters.AddWithValue("@ItemId", search.ItemId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
ContractWorkload workload = new ContractWorkload();
workload.ContractId = sqlRead["contractid"].ToString();
workload.Work = double.Parse(sqlRead["work"].ToString());
workload.Expense = double.Parse(sqlRead["expense"].ToString());
ContractItem item = new ContractItem();
item.Id = int.Parse(sqlRead["itemid"].ToString());
//item.ProjectId = int.Parse(sqlRead["projectid"].ToString());
//item.Item = sqlRead["item"].ToString();
workload.Item = item;
//Console.WriteLine(workload.Work + " " + workload.Expense);
workloads.Add(workload);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return workloads;
}
/// <summary>
/// 统计当前部门申请的所有工程Project下的会签单信息[Search数据填写SDepartmentShortCall + ItemId]
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static ContractWorkload StatisSDepartmentProjectYearWorkload(Search search)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
ContractWorkload workload = null;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = STATIS_SDEPARTMENT_PROJECT_YEAR_WORKLOAD_STR;
cmd.Parameters.AddWithValue("@SDepartmentYear", search.SDepartmentShortlCall + "_" + search.Year.ToString() + "%");
cmd.Parameters.AddWithValue("@ProjectId", search.ProjectId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
workload = new ContractWorkload();
workload.ContractId = "STATIS";
workload.Work = -1;
workload.Expense = double.Parse(sqlRead["expenses"].ToString());
ContractItem item = new ContractItem();
item.Id = -1;
workload.Item = item;
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return workload;
}
//public static int GetSDepartmentHDJContractCount(SDepartment department)
public static int GetSDepartmentYearHDJContractCount(Search search)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
int count = 0;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = GET_SDEPARTMENT_YEAR_HDJCONTRACT_COUNT_STR;
cmd.Parameters.AddWithValue("@SDepartmentYear", search.SDepartmentShortlCall + "_" + search.Year.ToString() + "%");
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
count = int.Parse(sqlRead["count"].ToString());
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return count;
}