private static double CalculateFirstVersionPoint(List <string> names, List <string> years, List <string> months) { string sql = "select SUM(权值) AS 初稿权值 from FirstvirsionList,FirstVirsionRule "; sql = sql + "where 承办人 in ({0})"; sql = sql + " AND (strftime('%Y',初稿日) in ({1}))"; sql = sql + " AND (strftime('%m',初稿日) in ({2}))"; sql = sql + " AND FirstvirsionList.任务名称=FirstVirsionRule.任务名称"; sql = sql + " AND FirstvirsionList.任务属性=FirstVirsionRule.任务属性"; sql = sql + " AND FirstvirsionList.申请类型=FirstVirsionRule.申请类型"; sql = sql + " AND FirstvirsionList.是否套案=FirstVirsionRule.是否套案"; sql = String.Format(sql, "'" + String.Join("','", names.ToArray()) + "'", "'" + String.Join("','", years.ToArray()) + "'", "'" + String.Join("','", months.ToArray()) + "'"); System.Data.DataTable dt = DBSQLite.GetDataTableBySQL(sql); DataRow dr = dt.Rows[0]; double FirstVersionPoint = 0.0; if (dr["初稿权值"].ToString() != "") { FirstVersionPoint = (double)dr["初稿权值"]; } return(FirstVersionPoint); }
private static double CalculateInTimeRatio(List <string> names, List <string> years, List <string> months) { string sql = "select * from FirstvirsionList "; sql = sql + "where 承办人 in ({0})"; sql = sql + " AND (strftime('%Y',初稿日) in ({1}))"; sql = sql + " AND (strftime('%m',初稿日) in ({2}))"; sql = String.Format(sql, "'" + String.Join("','", names.ToArray()) + "'", "'" + String.Join("','", years.ToArray()) + "'", "'" + String.Join("','", months.ToArray()) + "'"); System.Data.DataTable dt = DBSQLite.GetDataTableBySQL(sql); int firstversionNum = dt.Rows.Count; sql = sql + " AND 初稿日>初稿期限"; dt.Clear(); dt = DBSQLite.GetDataTableBySQL(sql); int firstversionNum_outofdate = dt.Rows.Count; double InTimeRatio = 0.0; if (firstversionNum > 0) { InTimeRatio = (double)(firstversionNum - firstversionNum_outofdate) / firstversionNum; } InTimeRatio = Math.Round(InTimeRatio, 2); return(InTimeRatio); }
private static double CalculatePatentDegree(List <string> names, List <string> years, List <string> months) { string sql = "select * from Donelist "; sql = sql + "where 承办人 in ({0})"; sql = sql + " AND (strftime('%Y',送官方日期) in ({1}))"; sql = sql + " AND (strftime('%m',送官方日期) in ({2}))"; sql = sql + " AND 申请类型='发明'"; sql = String.Format(sql, "'" + String.Join("','", names.ToArray()) + "'", "'" + String.Join("','", years.ToArray()) + "'", "'" + String.Join("','", months.ToArray()) + "'"); System.Data.DataTable dt = DBSQLite.GetDataTableBySQL(sql); int inventionNum = dt.Rows.Count; sql = sql + " AND 权项总数>10"; dt.Clear(); dt = DBSQLite.GetDataTableBySQL(sql); int inventionNum_claimNumex = dt.Rows.Count; double patentDegree = 0.0; if (inventionNum > 0) { patentDegree = (double)inventionNum_claimNumex / inventionNum; } patentDegree = Math.Round(patentDegree, 2); return(patentDegree); }
private static double GetDonePoint(string name, string year, string month) { string sql = "select 总权值 from Bill"; sql = sql + " WHERE 年='" + year + "'"; sql = sql + " AND 月='" + month + "'"; sql = sql + " AND 姓名 LIKE '" + name + "(离职)'"; DataTable dt = DBSQLite.GetDataTableBySQL(sql); double DonePointTotal = 0.0; if (/*dr["总权值"].ToString() != ""*/ dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; DonePointTotal = (double)dr["总权值"]; } return(DonePointTotal); }
private static double CalculateDonePoint(string year, List <string> months) { string sql = "select SUM(总权值) AS 递交总权值 from Bill"; sql = sql + " WHERE 年='" + year + "'"; sql = sql + " AND 月 in ({0})"; sql = String.Format(sql, "'" + String.Join("','", months.ToArray()) + "'"); System.Data.DataTable dt = DBSQLite.GetDataTableBySQL(sql); double DonePointTotal = 0.0; DataRow dr = dt.Rows[0]; if (dr["递交总权值"].ToString() != "") { DonePointTotal = (double)dr["递交总权值"]; } return(DonePointTotal); }
public List <TaskDetail> GetAllTasks() { List <TaskDetail> allTasks = new List <TaskDetail>(); string sql = "select * from Caselist,FirstVirsionRule"; sql = sql + " WHERE Caselist.任务名称=FirstVirsionRule.任务名称"; sql = sql + " AND Caselist.申请类型=FirstVirsionRule.申请类型"; sql = sql + " AND Caselist.是否套案=FirstVirsionRule.是否套案"; sql = sql + " AND Caselist.代理人处理状态 IN ('未处理','撰写中','客户补充资料')"; DataTable dt = DBSQLite.GetDataTableBySQL(sql); foreach (DataRow dr in dt.Rows) { TaskDetail taskDetail = new TaskDetail(); taskDetail.Taskinfo.Attorney = dr["部门成员"].ToString(); taskDetail.Taskinfo.TaskName = dr["任务名称"].ToString(); taskDetail.Caseinfo.CasedocumentName = dr["案件名称"].ToString(); taskDetail.Caseinfo.AttorneySeries = dr["我方文号"].ToString(); taskDetail.Caseinfo.AppType = dr["申请类型"].ToString(); if (dr["初稿期限"] != null) { taskDetail.Taskinfo.FirstVirsionDeadlineInternal = Convert.ToDateTime(dr["初稿期限"]); } taskDetail.Taskinfo.ProcessStage = dr["代理人处理状态"].ToString(); taskDetail.Caseinfo.ClientName = dr["客户名称"].ToString(); if (dr["初稿日期"] != null) { taskDetail.Taskinfo.FirstVirsionDate = Convert.ToDateTime(dr["初稿日期"]); } taskDetail.Taskinfo.Department = dr["部门名称"].ToString(); taskDetail.Caseinfo.Remark = dr["案件备注"].ToString(); taskDetail.Taskinfo.Weight = Convert.ToDouble(dr["权值"]); allTasks.Add(taskDetail); } return(allTasks); }
private static double CalculateInventionRatio(List <string> names, List <string> years, List <string> months) { string sql = "select * from Donelist "; sql = sql + "where 承办人 in ({0})"; sql = sql + " AND (strftime('%Y',送官方日期) in ({1}))"; sql = sql + " AND (strftime('%m',送官方日期) in ({2}))"; sql = sql + " AND 申请类型='发明'"; sql = String.Format(sql, "'" + String.Join("','", names.ToArray()) + "'", "'" + String.Join("','", years.ToArray()) + "'", "'" + String.Join("','", months.ToArray()) + "'"); System.Data.DataTable dt = DBSQLite.GetDataTableBySQL(sql); int inventionNum = dt.Rows.Count; sql = "select * from Donelist "; sql = sql + "where 承办人 in ({0})"; sql = sql + " AND (strftime('%Y',送官方日期) in ({1}))"; sql = sql + " AND (strftime('%m',送官方日期) in ({2}))"; sql = sql + " AND 申请类型 IN('发明','实用新型')"; sql = String.Format(sql, "'" + String.Join("','", names.ToArray()) + "'", "'" + String.Join("','", years.ToArray()) + "'", "'" + String.Join("','", months.ToArray()) + "'"); dt.Clear(); dt = DBSQLite.GetDataTableBySQL(sql); int newappNum = dt.Rows.Count; double inventionRatio = 0.0; if (inventionNum > 0) { inventionRatio = (double)inventionNum / newappNum; } inventionRatio = Math.Round(inventionRatio, 2); return(inventionRatio); }
public List <KPIinfo> GetKPIs(List <string> years, List <string> months) { List <KPIinfo> kpis = new List <KPIinfo>(); List <string> years1 = new List <string>(); List <string> months1 = new List <string>(); string sql = "select * from KPI LEFT OUTER JOIN Bill ON (KPI.承办人 = Bill.姓名 AND KPI.月份=Bill.月 AND KPI.年份=Bill.年),ExamRelation WHERE (KPI.年份 in ({0}) AND KPI.月份 in ({1}) AND KPI.承办人=ExamRelation.被考核者姓名)"; sql = string.Format(sql, "'" + String.Join("','", years.ToArray()) + "'", "'" + String.Join("','", months.ToArray()) + "'"); DataTable dt = DBSQLite.GetDataTableBySQL(sql); int Index = 0; if (dt != null) { List <string> names = new List <string>(); foreach (DataRow dr in dt.Rows) { KPIinfo kpi = new KPIinfo(); kpi.Index = ++Index; //kpi.FirstVirsionPoint = (double)dr["初稿权值"]; kpi.InTimePortion = (double)dr["及时交付率"]; kpi.DonePoint_Target = (double)dr["递交目标"]; kpi.FirstVirsionPoint_Target = (double)dr["初稿目标"]; kpi.PatentDegree_Target = (double)dr["专利度目标"]; kpi.InTimePortion_Target = (double)dr["及时交付目标"]; kpi.Year = dr["年份"].ToString(); kpi.Period = dr["考核周期"].ToString(); kpi.Name = dr["承办人"].ToString(); kpi.Zone = dr["区域"].ToString(); kpi.Month = dr["月份"].ToString(); kpi.Score_Cowork = Convert.ToInt32(dr["团队协作打分"]); kpi.Score_Passion = Convert.ToInt32(dr["积极性打分"]); kpi.Score_Selfdrive = Convert.ToInt32(dr["主动性打分"]); kpi.Comment = dr["评语"].ToString(); kpi.Position = dr["被考核者职位"].ToString(); kpi.Examiner = dr["考核者姓名"].ToString(); kpi.Examiner_Positon = dr["考核者职位"].ToString(); names.Add(kpi.Name); names.Add(kpi.Name + "(离职)"); years1.Add(kpi.Year); months1.Add(kpi.Month); if ((double)dr["递交权值"] != 0) { kpi.DonePoint = (double)dr["递交权值"]; } else { if (dr["总权值"].ToString() != "") { kpi.DonePoint = (double)dr["总权值"]; } else { kpi.DonePoint = GetDonePoint(kpi.Name, kpi.Year, kpi.Month); } } kpi.PatentDegree = CalculatePatentDegree(names, years1, months1); kpi.InTimePortion = CalculateInTimeRatio(names, years1, months1); if ((double)dr["初稿权值"] != 0) { kpi.FirstVirsionPoint = (double)dr["初稿权值"]; } else { kpi.FirstVirsionPoint = CalculateFirstVersionPoint(names, years1, months1); } kpi.Score = Math.Round((Math.Min(kpi.DonePoint * 100 / kpi.DonePoint_Target, 100) * 0.3 + Math.Min(kpi.FirstVirsionPoint * 100 / kpi.FirstVirsionPoint_Target, 100) * 0.25 + Math.Min(kpi.PatentDegree * 100 / kpi.PatentDegree_Target, 100) * 0.1 + Math.Min(kpi.InTimePortion * 100 / kpi.InTimePortion_Target, 100) * 0.2 + (kpi.Score_Cowork + kpi.Score_Passion + kpi.Score_Selfdrive) * 0.05), 2); kpis.Add(kpi); } } return(kpis); }