/// <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; }