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