public List <ValueInfo> GetActualValue(tstdefcfg xdef, tstdefcfg ydef, string y)
{
string sSql = "select " + xdef.finm + @" as DisplayField,convert(varchar(10),count(*)) as ValueField from vw_employment
where " + ydef.finm + "='" + y + @"'
and (tmdt is null or tmdt > getdate())
group by " + xdef.finm;
IEnumerable <ValueInfo> lstRet = gDB.ExecuteQuery <ValueInfo>(sSql);
return(lstRet.ToList());
}
public List <ValueInfo> GetActualValue(string hccd, tstdefcfg xdef, tstdefcfg ydef, string y)
{
var q = (from p in gDB.tpshctvals
where p.hccd == hccd &&
p.yval == y
select new ValueInfo
{
DisplayField = p.xval,
ValueField = p.hcnt.Value.ToString()
}).ToList();
return(q);
}
private void LoadData(string hccd)
{
pshctcfgBll bll = new pshctcfgBll();
tpshctcfg cfg = bll.GetSelectedObject <tpshctcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "hccd", ColumnValue = hccd
}
});
tstdefcfg xdef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.xdef
}
});
tstdefcfg ydef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.ydef
}
});
ValueInfoBll dd = new ValueInfoBll();
List <ValueInfo> lstX = typeof(ValueInfoBll).GetMethod(xdef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
List <ValueInfo> lstY = typeof(ValueInfoBll).GetMethod(ydef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
//Build Grid Header
//BuildGridHeader(lstX);
rphctctlBll rpbll = new rphctctlBll();
string strXML = rpbll.GetData(hccd, cfg, xdef, ydef, lstX, lstY);
DataSet ds = new DataSet();
XmlReader reader = XmlReader.Create(new System.IO.StringReader(strXML));
ds.ReadXml(reader);
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
//GroupRows(this.GridView1, 0);
MergeGridView mergeGV = new MergeGridView();
mergeGV.Merge(this.GridView1, 0);
}
public void exportExcel()
{
try
{
string record = this.Request["record"];
pshctvalBll bll = new pshctvalBll();
tpshctcfg cfg = bll.GetSelectedObject <tpshctcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "hccd", ColumnValue = record
}
});
tstdefcfg xdef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.xdef
}
});
tstdefcfg ydef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.ydef
}
});
ValueInfoBll dd = new ValueInfoBll();
List <ValueInfo> lstX = typeof(ValueInfoBll).GetMethod(xdef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
List <ValueInfo> lstY = typeof(ValueInfoBll).GetMethod(ydef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
List <List <ColumnInfo> > lstResult = bll.GetHeadCountCfgValue(record, cfg, lstX, lstY);
if (lstResult != null)
{
UtilExcel.ExportToExcel(Response, this.GetType().Name, lstResult);
}
}
catch (Exception ex)
{
string message = "{status:'failure',msg:'" + ExceptionPaser.Parse(HRMSRes.Public_Message_QueryFail, ex, true) + "'}";
Response.Output.Write(message);
}
}
public void list()
{
string record = this.Request["record"];
pshctvalBll bll = new pshctvalBll();
tpshctcfg cfg = bll.GetSelectedObject <tpshctcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "hccd", ColumnValue = record
}
});
tstdefcfg xdef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.xdef
}
});
tstdefcfg ydef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.ydef
}
});
ValueInfoBll dd = new ValueInfoBll();
List <ValueInfo> lstX = typeof(ValueInfoBll).GetMethod(xdef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
List <ValueInfo> lstY = typeof(ValueInfoBll).GetMethod(ydef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
List <List <ColumnInfo> > lstResult = bll.GetHeadCountCfgValue(record, cfg, lstX, lstY);
int total = lstResult.Count;
string json = ConvertToJson(lstResult);
Response.Write("{results:" + total + ",rows:" + json + "}");
//ViewData["hctconfig"] = responseJson(lstResult,lstX,lstY);
}
public void load()
{
string record = this.Request["record"];
pshctvalBll bll = new pshctvalBll();
tpshctcfg cfg = bll.GetSelectedObject <tpshctcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "hccd", ColumnValue = record
}
});
tstdefcfg xdef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.xdef
}
});
tstdefcfg ydef = bll.GetSelectedObject <tstdefcfg>(new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = "dfnm", ColumnValue = cfg.ydef
}
});
ValueInfoBll dd = new ValueInfoBll();
List <ValueInfo> lstX = typeof(ValueInfoBll).GetMethod(xdef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
List <ValueInfo> lstY = typeof(ValueInfoBll).GetMethod(ydef.dasc).Invoke(dd, new object[] { }) as List <ValueInfo>;
string message = "{status:'success',msg:'" + responseJson(record, lstX, lstY).Replace("'", "\\'") + "'}";
Response.Write(message);
}
public string GetData(string hccd, tpshctcfg cfg, tstdefcfg xdef, tstdefcfg ydef, List <ValueInfo> lstX, List <ValueInfo> lstY)
{
StringBuilder sb = new StringBuilder();
List <List <ColumnInfo> > lstRet = new List <List <ColumnInfo> >();
for (int y = 0; y < lstY.Count; y++)
{
#region Plan
List <ColumnInfo> lstColumn = new List <ColumnInfo>();
ColumnInfo col = new ColumnInfo()
{
ColumnName = "Item", ColumnValue = lstY[y].DisplayField
};
lstColumn.Add(col);
col = new ColumnInfo()
{
ColumnName = "ItemValue", ColumnValue = lstY[y].ValueField, ColumnDisplayName = "Hidden"
};
lstColumn.Add(col);
col = new ColumnInfo()
{
ColumnName = "Type", ColumnValue = "Plan"
};
lstColumn.Add(col);
//默认全部指定为0
for (int x = 0; x < lstX.Count; x++)
{
ColumnInfo col1 = new ColumnInfo()
{
ColumnName = lstX[x].DisplayField, ColumnValue = "0", ColumnDisplayName = lstX[x].ValueField
};
lstColumn.Add(col1);
}
lstRet.Add(lstColumn);
#endregion
#region Actual
lstColumn = new List <ColumnInfo>();
col = new ColumnInfo()
{
ColumnName = "Item", ColumnValue = lstY[y].DisplayField
};
lstColumn.Add(col);
col = new ColumnInfo()
{
ColumnName = "ItemValue", ColumnValue = lstY[y].ValueField, ColumnDisplayName = "Hidden"
};
lstColumn.Add(col);
col = new ColumnInfo()
{
ColumnName = "Type", ColumnValue = "Actual"
};
lstColumn.Add(col);
//默认全部指定为0
for (int x = 0; x < lstX.Count; x++)
{
ColumnInfo col1 = new ColumnInfo()
{
ColumnName = lstX[x].DisplayField, ColumnValue = "0", ColumnDisplayName = lstX[x].ValueField
};
lstColumn.Add(col1);
}
lstRet.Add(lstColumn);
#endregion
#region Difference
lstColumn = new List <ColumnInfo>();
col = new ColumnInfo()
{
ColumnName = "Item", ColumnValue = lstY[y].DisplayField
};
lstColumn.Add(col);
col = new ColumnInfo()
{
ColumnName = "ItemValue", ColumnValue = lstY[y].ValueField, ColumnDisplayName = "Hidden"
};
lstColumn.Add(col);
col = new ColumnInfo()
{
ColumnName = "Type", ColumnValue = "Difference"
};
lstColumn.Add(col);
//默认全部指定为0
for (int x = 0; x < lstX.Count; x++)
{
ColumnInfo col1 = new ColumnInfo()
{
ColumnName = lstX[x].DisplayField, ColumnValue = "0", ColumnDisplayName = lstX[x].ValueField
};
lstColumn.Add(col1);
}
lstRet.Add(lstColumn);
#endregion
}
#region Fill actual value
for (int i = 0; i < lstY.Count; i++)
{
List <ValueInfo> lstActualValue = localDal.GetActualValue(xdef, ydef, lstY[i].ValueField);
List <ValueInfo> lstPlanValue = localDal.GetActualValue(hccd, xdef, ydef, lstY[i].ValueField);
//fill actual value
for (int s = 0; s < lstActualValue.Count; s++)
{
for (int n = 1; n < lstRet.Count; n = n + 3)
{
for (int m = 3; m < lstRet[n].Count; m++)
{
if (lstRet[n][m].ColumnDisplayName == lstActualValue[s].DisplayField &&
lstY[i].ValueField == lstRet[n][1].ColumnValue)
{
//actual
lstRet[n][m].ColumnValue = lstActualValue[s].ValueField;
}
}
}
}
//fill plan value
if (lstPlanValue.Count > 0)
{
for (int n = 0; n < lstRet.Count; n = n + 3)
{
for (int m = 3; m < lstRet[n].Count; m++)
{
if (lstRet[n][m].ColumnDisplayName == lstPlanValue[m - 3].DisplayField &&
lstY[i].ValueField == lstRet[n][1].ColumnValue)
{
//plan
lstRet[n][m].ColumnValue = lstPlanValue[m - 3].ValueField;
//difference
lstRet[n + 2][m].ColumnValue = (Convert.ToInt32(lstRet[n][m].ColumnValue) -
Convert.ToInt32(lstRet[n + 1][m].ColumnValue)).ToString();
}
}
}
}
}
#endregion
#region Build XML
sb.Append("<Data>");
for (int i = 0; i < lstRet.Count; i++)
{
int total = 0;
sb.Append("<Row>");
sb.Append("<Item>").Append(lstRet[i][0].ColumnValue).Append("</Item>");
sb.Append("<Type>").Append(lstRet[i][2].ColumnValue).Append("</Type>");
for (int j = 0; j < lstX.Count; j++)
{
sb.Append("<" + lstX[j].DisplayField + ">").Append(lstRet[i][3 + j].ColumnValue).Append("</" + lstX[j].DisplayField + ">");
total += Convert.ToInt32(lstRet[i][3 + j].ColumnValue);
}
//total
sb.Append("<Total>" + total.ToString() + "</Total>");
sb.Append("</Row>");
}
sb.Append("</Data>");
return(sb.ToString());
#endregion
}