public static DataSet GetCenterData(int year, int month, string todayStr) { string startTm = year + "-" + month + "-1"; string endTm = year + "-" + (month + 1) + "-1"; string sql = string.Format("select center, case when sum(t.monthTask) is null then 0 else sum(t.monthTask) end monthTask, case when sum(t.yearTask) is null then 0 else sum(t.yearTask) end yearTask, sum(t.monthPrepaid) monthPrepaid ,sum(t.sumPrepaid) sumPrepaid, sum(t.todayFlow) todayFlow from ( " + "select sc.Sector,case when sum(st.MonthTask{1}*st.ExaminePrice) is null then 0 else sum(st.MonthTask{1}*st.ExaminePrice) end monthTask, case when sum(st.YearTask*st.ExaminePrice) is null then 0 else sum(st.YearTask*st.ExaminePrice) end yearTask,0 monthPrepaid,0 sumPrepaid, case when sc.sector like '%中申%' then '中申' when sc.sector like '%业力%' then '业力' else '东森' end center, " + "(select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date = '{4}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector) todayFlow " + " from sales_task st right join (select distinct sector from cost_sharing) sc on sc.sector = st.sector group by sc.Sector ) t group by center order by FIELD(center,'东森','业力','中申')", year, month, startTm, endTm, todayStr); DataSet ds = SqlHelper.Find(sql); if (ds != null && ds.Tables[0] != null) { DataTable dt = ds.Tables[0]; // 给dataTable新增月销售,年销售,月完成率,年完成率的字段 dt.Columns.Add("monthFlow", typeof(float)); dt.Columns.Add("sumSales", typeof(float)); dt.Columns.Add("monthCompleteRate", typeof(float)); dt.Columns.Add("sumCompleteRate", typeof(float)); foreach (DataRow dr in dt.Rows) { if (dr != null) { string center = dr["center"].ToString(); // 通过每个公司得出当月以及年销售量 // 1.得出当月的 if (center.Equals("东森")) { sql = string.Format("select case (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector != '业力组' and Sector != '中申组') when 0 " + "then (select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end " + "from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date >= '{2}' " + "AND date < '{3}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice," + "Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId " + "where cs.Sector != '业力组' and cs.Sector != '中申组') else (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector != '业力组' and Sector != '中申组') end monthFlow", year, month, startTm, endTm); } else { sql = string.Format("select case (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector like '%{2}%') when 0 " + "then (select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end " + "from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date >= '{3}' " + "AND date < '{4}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice," + "Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId " + "where cs.Sector like '%{2}%') else (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector like '%{2}%') end monthFlow", year, month, center, startTm, endTm); } DataSet monthFlowDs = SqlHelper.Find(sql); if (monthFlowDs != null) { string monthFlowStr = monthFlowDs.Tables[0].Rows[0]["monthFlow"].ToString(); float monthFlow = float.Parse(monthFlowStr); dr["monthFlow"] = monthFlow; //再算出当月完成率 float monthCompleteRate = 0; string monthTaskStr = dr["monthTask"].ToString(); if ("0".Equals(monthTaskStr) && "0".Equals(monthFlowStr)) { monthCompleteRate = 1; } else if (!"0".Equals(monthTaskStr)) { monthCompleteRate = monthFlow / float.Parse(monthTaskStr); } dr["monthCompleteRate"] = monthCompleteRate; } float yearFlow = 0; List <MySqlParameter> ListParam = new List <MySqlParameter>(); MySqlParameter p1 = new MySqlParameter("year1", year); MySqlParameter p2 = new MySqlParameter("month1", month); MySqlParameter p3 = new MySqlParameter("sector1", center); MySqlParameter p4 = new MySqlParameter("sumSalessss", 0); ListParam.Add(p1); ListParam.Add(p2); ListParam.Add(p3); ListParam.Add(p4); if (center.Equals("东森")) { cProcedure procedure = new cProcedure("proc", ListParam); cProcedure[] list = new cProcedure[] { procedure }; int[] i = SqlHelper.RunProcedure(list); if (i.Length <= 0) { return(null); } string sumSalesStr = p4.Value.ToString(); yearFlow = float.Parse(sumSalesStr); } else { cProcedure procedure = new cProcedure("proc2", ListParam); cProcedure[] list = new cProcedure[] { procedure }; int[] i = SqlHelper.RunProcedure(list); if (i.Length <= 0) { return(null); } string sumSalesStr = p4.Value.ToString(); yearFlow = float.Parse(sumSalesStr); } dr["sumSales"] = yearFlow; //再算出当年完成率 float sumCompleteRate = 0; string yearTaskStr = dr["yearTask"].ToString(); if ("0".Equals(yearTaskStr)) { sumCompleteRate = 1; } else { sumCompleteRate = yearFlow / float.Parse(yearTaskStr); } dr["sumCompleteRate"] = sumCompleteRate; } } } else { return(null); } return(ds); }
public static DataSet GetData(int year, int month, string todayStr) { string startTm = year + "-" + month + "-1"; string endTm = year + "-" + (month + 1) + "-1"; //string sql = string.Format("select sc.Sector,case when sum(st.MonthTask{1}*st.ExaminePrice) is null then 0 else sum(st.MonthTask{1}*st.ExaminePrice) end monthTask, case when sum(st.YearTask*st.ExaminePrice) is null then 0 else sum(st.YearTask*st.ExaminePrice) end yearTask,0 monthPrepaid,0 sumPrepaid, case when sc.sector like '%中申%' then '中申' when sc.sector like '%业力%' then '业力' else '东森' end center, " + //"(select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date = '{4}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector) todayFlow, " + //"(select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date >= '{2}' AND date < '{3}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector) monthFlow, " + //"(select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector) sumSales, " + //"((select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date >= '{2}' AND date < '{3}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector) /sum(st.MonthTask{1}*st.ExaminePrice)) monthCompleteRate," + //"((select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector)/sum(st.YearTask*st.ExaminePrice)) sumCompleteRate " + //" from sales_task st right join sector_corresponding sc on sc.sector = st.sector group by sc.Sector order by FIELD(center,'东森','业力','中申'), monthCompleteRate desc, sumCompleteRate desc ", year, month, startTm, endTm, todayStr); string sql = string.Format("select sc.Sector,case when sum(st.MonthTask{1}*st.ExaminePrice) " + "is null then 0 else sum(st.MonthTask{1}*st.ExaminePrice) end monthTask, case when sum(st.YearTask*st.ExaminePrice)" + " is null then 0 else sum(st.YearTask*st.ExaminePrice) end yearTask,0 monthPrepaid," + "(select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date = '{4}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice,Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId where cs.Sector = sc.Sector) todayFlow, " + "0 sumPrepaid, case when sc.sector like '%中申%' then '中申' when sc.sector like '%业力%' then '业力' else '东森' end center " + " from sales_task st right join (select distinct sector from cost_sharing) sc on sc.sector = st.sector " + "group by sc.Sector order by FIELD(center,'东森','业力','中申') desc ", year, month, startTm, endTm, todayStr); // 先算出各个盈利中心的年度任务 DataSet ds = SqlHelper.Find(sql); if (ds != null && ds.Tables[0] != null) { DataTable dt = ds.Tables[0]; // 给dataTable新增月销售,年销售,月完成率,年完成率的字段 dt.Columns.Add("monthFlow", typeof(float)); dt.Columns.Add("sumSales", typeof(float)); dt.Columns.Add("monthCompleteRate", typeof(float)); dt.Columns.Add("sumCompleteRate", typeof(float)); foreach (DataRow dr in dt.Rows) { if (dr != null) { string sector = dr["Sector"].ToString(); // 通过每个盈利中心得出当月以及年销售量 // 1.得出当月的 sql = string.Format("select case (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector = '{2}') when 0 " + "then (select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end " + "from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date >= '{3}' " + "AND date < '{4}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice," + "Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId " + "where cs.Sector = '{2}') else (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector = '{2}') end monthFlow", year, month, sector, startTm, endTm); DataSet monthFlowDs = SqlHelper.Find(sql); if (monthFlowDs != null) { string monthFlowStr = monthFlowDs.Tables[0].Rows[0]["monthFlow"].ToString(); // 当月流向已归档 float monthFlow = float.Parse(monthFlowStr); dr["monthFlow"] = monthFlow; //再算出当月完成率 float monthCompleteRate = 0; string monthTaskStr = dr["monthTask"].ToString(); if ("0".Equals(monthTaskStr) && "0".Equals(monthFlowStr)) { monthCompleteRate = 1; } else if (!"0".Equals(monthTaskStr)) { monthCompleteRate = monthFlow / float.Parse(monthTaskStr); } dr["monthCompleteRate"] = monthCompleteRate; } // 查询当年的 //DataSet sumDataSet = SqlHelper.Find(sql); List <MySqlParameter> ListParam = new List <MySqlParameter>(); MySqlParameter p1 = new MySqlParameter("year1", year); MySqlParameter p2 = new MySqlParameter("month1", month); MySqlParameter p3 = new MySqlParameter("sector1", sector); MySqlParameter p4 = new MySqlParameter("sumSalessss", 0); ListParam.Add(p1); ListParam.Add(p2); ListParam.Add(p3); ListParam.Add(p4); cProcedure procedure = new cProcedure("proc3", ListParam); cProcedure[] list = new cProcedure[] { procedure }; int[] i = SqlHelper.RunProcedure(list); if (i.Length <= 0) { return(null); } string sumSalesStr = p4.Value.ToString(); float sumSales = float.Parse(sumSalesStr); //for (int i = 1; i <= month; i++) //{ // startTm = year + "-" + i + "-1"; // endTm = year + "-" + (i + 1) + "-1"; // sql = string.Format("select case (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + // "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector = '{2}') when 0 " + // "then (select case when sum(ls.amountSend) is null then 0 else sum(ls.amountSend*cs.ExaminePrice) end " + // "from (select ProductId,terminalClientId,sum(amountSend) amountSend from leave_stock where date >= '{3}' " + // "AND date < '{4}' GROUP BY ProductId, terminalClientId) ls right join (select ProductId,HospitalId,ExaminePrice," + // "Sector from cost_sharing GROUP BY ProductId, HospitalId) cs on ls.ProductId = cs.ProductId and ls.terminalClientId = cs.HospitalId " + // "where cs.Sector = '{2}') else (select case when sum(fs.FlowSalesMoney) is null then 0 else sum(fs.FlowSalesMoney) " + // "end monthFlow from flow_statistics fs where Year = {0} and Month = {1} and Sector = '{2}') end monthFlow", year, i, sector, startTm, endTm); // monthFlowDs = SqlHelper.Find(sql); // if (monthFlowDs != null) // { // string monthFlowStr = monthFlowDs.Tables[0].Rows[0]["monthFlow"].ToString(); // // 当月流向已归档 // float monthFlow = float.Parse(monthFlowStr); // yearFlow += monthFlow; // } //} dr["sumSales"] = sumSales; //再算出当年完成率 float sumCompleteRate = 0; string yearTaskStr = dr["yearTask"].ToString(); if ("0".Equals(yearTaskStr)) { sumCompleteRate = 1; } else { sumCompleteRate = sumSales / float.Parse(yearTaskStr); } dr["sumCompleteRate"] = sumCompleteRate; } } } else { return(null); } return(ds); }