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