public static ContractWorkload StatisHSDepartmentYearCategoryWorkLoad(HSDepartment department, int year, ContractCategory category)
{
if (department.IsHigherDepatmentHasSub() == true) // 如果当前部门是一个有下属部门的一级部门
{
//Console.WriteLine("部门{0}是一个有子部门的一级部门", department.Name);
return(DALContractStatistic.StatisHigherDepartmentYearCategoryWorkLoad(department, year, category));
}
else if (department.IsHigherDepatmentNoSub() == true)
{
//Console.WriteLine("部门{0}是无子部门的一级部门", department.Name);
return(DALContractStatistic.StatisDepartmentYearCategoryWorkLoad(department, year, category));
}
else if (department.IsStatisTotalDepartment() == true)
{
//Console.WriteLine("部门{0}要求统计所有部门的总和", department.Name);
return(DALContractStatistic.StatisYearCategoryWorkLoad(year, category));
}
else if (department.IsStatisRegularDepartment() == true)
{
//Console.WriteLine("部门{0}要求统计计划任务额度", department.Name);
return(DALContractStatistic.StatisYearCategoryRegularLoad(year, category));
}
else if (department.IsSubDepartment() == true) // 如果当前部门是一个下属子部门
{
// Console.WriteLine("部门{0}是下属子部门", department.Name);
return(null);
}
return(null);
}
/// <summary>
/// 查询当前部门的二级部门信息
/// </summary>
/// <param name="higherDepartmentId"></param>
/// <returns></returns>
public static List <HSDepartment> QuerySubHSDepartment(int higherDepartmentId)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List <HSDepartment> departments = new List <HSDepartment>();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = QUERY_SUB_HSDEPARTMENT_STR;
cmd.Parameters.AddWithValue("HigherDepartmentId", higherDepartmentId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
HSDepartment department = new HSDepartment();
/// 部门基本信息
department.Id = int.Parse(sqlRead["id"].ToString());
department.Name = sqlRead["name"].ToString();
department.ShortCall = sqlRead["shortcall"].ToString();
/// 申请权限信息
department.CanBoundary = int.Parse(sqlRead["canboundary"].ToString());
department.CanInland = int.Parse(sqlRead["caninland"].ToString());
department.CanEmergency = int.Parse(sqlRead["canemergency"].ToString());
department.CanRegular = int.Parse(sqlRead["canregular"].ToString());
/// 上级部门信息
department.HigherDepartmentId = int.Parse(sqlRead["highdepid"].ToString());
departments.Add(department);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(departments);
}
/// <summary>
/// 统计当前部门申请的所有工程Project下的会签单信息[Search数据填写SDepartmentShortCall + ItemId]
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static ContractWorkload StatisDepartmentYearProjectWorkLoad(HSDepartment department, int year, ContractProject project)
{
if (project.Id == 10)
{
}
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
ContractWorkload workload = null;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = STATIS_DEPARTMENT_YEAR_PROJECT_STR;
cmd.Parameters.AddWithValue("@SDepartmentYear", department.ShortCall + "__" + year.ToString() + "%");
cmd.Parameters.AddWithValue("@ProjectId", project.Id);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
workload = new ContractWorkload();
workload.ContractId = STATIS_KIND.STATIS_DEPARTMENT_YEAR_PROJECT_WORKLOAD.ToString();
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);
}
/// SELECT * FROM conidcategory WHERE categoryid = @CategoryId
/// SELECT departmentid FROM conidcategory WHERE categoryid = @CategoryId ORDER BY departmentid
/// SELECT d.id depid, d.name depname, d.shortcall depshortcall FROM conidcategory c, department d WHERE c.departmentid = d.id AND c.categoryid = 1
/// SELECT d.id depid, d.name depname, d.shortcall depshortcall, c.category, c.shortcall FROM conidcategory cc, department d, category c WHERE c.id = cc.categoryid AND cc.departmentid = d.id AND cc.categoryid =
public static List <HSDepartment> QueryCategoryHigherDepartment(int categoryId)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List <HSDepartment> departments = new List <HSDepartment>();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = QUERY_CATEGORY_HIGHDEPARTMENT_STR;
cmd.Parameters.AddWithValue("@CategoryId", categoryId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
HSDepartment department = new HSDepartment();
department.Id = int.Parse(sqlRead["depid"].ToString());
department.Name = sqlRead["depname"].ToString();
department.ShortCall = sqlRead["depshortcall"].ToString();
department.HigherDepartmentId = int.Parse(sqlRead["highdepid"].ToString());
departments.Add(department);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(departments);
}
public static ContractWorkload StatisHigherDepartmentYearCategoryWorkLoad(HSDepartment highDepartment, int year, ContractCategory category)
{
// 首先获取到当前一级部门的所有下级部门信息
List <HSDepartment> departments = DALHSDepartment.QuerySubHSDepartment(highDepartment.Id);
ContractWorkload totalWorkloads = new ContractWorkload
{
Work = 0,
Expense = 0,
};
foreach (HSDepartment department in departments)
{
ContractWorkload workload = DALContractStatistic.StatisDepartmentYearCategoryWorkLoad(department, year, category);
totalWorkloads.Work += workload.Work;
totalWorkloads.Expense += workload.Expense;
}
return(totalWorkloads);
}
