/// <summary> /// 统计当前部门申请的所有工程Project下的会签单信息[Search数据填写SDepartmentShortCall + ItemId] /// </summary> /// <param name="search"></param> /// <returns></returns> public static ContractWorkload StatisSDepartmentYearCategoryWorkLoad(string departmentShortCall, int year, int categoryId) { MySqlConnection con = DBTools.GetMySqlConnection(); MySqlCommand cmd; ContractWorkload workload = null; try { con.Open(); cmd = con.CreateCommand(); cmd.CommandText = STATIS_SDEPARTMENT_YEAR_CATEGORY_STR; cmd.Parameters.AddWithValue("@SDepartmentYear", departmentShortCall + "_" + year.ToString() + "%"); cmd.Parameters.AddWithValue("@Category", categoryId); MySqlDataReader sqlRead = cmd.ExecuteReader(); cmd.Dispose(); while (sqlRead.Read()) { workload = new ContractWorkload(); workload.ContractId = "STATIS"; workload.Work = double.Parse(sqlRead["works"].ToString()); 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 List<ContractItem> QueryProjectItem(int projectId) { MySqlConnection con = DBTools.GetMySqlConnection(); MySqlCommand cmd; List<ContractItem> items = new List<ContractItem>(); try { con.Open(); cmd = con.CreateCommand(); cmd.CommandText = QUERY_PROJECT_ITEM_STR; cmd.Parameters.AddWithValue("@ProjectId", projectId); MySqlDataReader sqlRead = cmd.ExecuteReader(); cmd.Dispose(); while (sqlRead.Read()) { ContractItem item = new ContractItem(); item.Id = int.Parse(sqlRead["id"].ToString()); item.ProjectId = int.Parse(sqlRead["projectid"].ToString()); item.Item = sqlRead["item"].ToString(); items.Add(item); } con.Close(); con.Dispose(); } catch (Exception) { throw; } finally { if (con.State == ConnectionState.Open) { con.Close(); } } return items; }
public static List<ContractWorkload> QureyContractWorkLoad(string contractId) { MySqlConnection con = DBTools.GetMySqlConnection(); MySqlCommand cmd; List<ContractWorkload> workloads = new List<ContractWorkload>(); try { con.Open(); cmd = con.CreateCommand(); cmd.CommandText = QUERY_CONTRACT_WORKLOAD_STR; cmd.Parameters.AddWithValue("@ContractId", contractId); MySqlDataReader sqlRead = cmd.ExecuteReader(); cmd.Dispose(); while (sqlRead.Read()) { ContractWorkload workload = new ContractWorkload(); workload.ContractId = contractId; 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; }
/// 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; }