public ActionResult InitPersonsTable()
{
int page = Helper.ToInt(Request["page"]);
int rows = Helper.ToInt(Request["rows"]);
string unitID = Helper.ToString(Request["unitID"]);
string cardDate = Helper.ToString(Request["cardDate"]);
HCQ2_Model.SelectModel.A02Model model = new HCQ2_Model.SelectModel.A02Model()
{
page = page,
rows = rows,
unitID = unitID,
cardDate = cardDate
};
List <HCQ2_Model.ViewModel.PunchCardModel> list =
operateContext.bllSession.A02.SelectCardPersons(model);
model.page = 0;
model.rows = 0;
TableModel tModel = new TableModel()
{
total = operateContext.bllSession.A02.SelectCardPersons(model).Count,
rows = list
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
public List <A02Model> SelectA02CheckByMonthData(HCQ2_Model.SelectModel.A02Model model)
{
StringBuilder sb = new StringBuilder();
if (model == null || string.IsNullOrEmpty(model.unitID))
{
return(null);
}
sb.Append(@"SELECT a2.A0201 as cardDate,COUNT(*) AS countPersons FROM (SELECT a1.PersonID, a1.A0201 FROM
(SELECT LEFT(convert(char(10), A0201, 23), 7) AS A0201, PersonID FROM dbo.A02 WHERE 1=1 ");
if (!string.IsNullOrEmpty(model.dateStart) && !string.IsNullOrEmpty(model.dateEnd))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10), A0201, 23), 7) BETWEEN '{0}' AND '{1}' ", model.dateStart, model.dateEnd));
}
else if (!string.IsNullOrEmpty(model.dateStart))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10), A0201, 23), 7)>='{0}' ", model.dateStart));
}
else if (!string.IsNullOrEmpty(model.dateEnd))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10), A0201, 23), 7)<='{0}' ", model.dateEnd));
}
sb.Append(string.Format(@"AND PersonID IN(SELECT PersonID FROM dbo.A01 WHERE UnitID LIKE '{0}%')) a1
GROUP BY a1.PersonID,a1.A0201)a2 GROUP BY a2.A0201" , model.unitID));
DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <A02Model>(dt));
}
public EchartsVo GetMonthChartData(HCQ2_Model.SelectModel.A02Model model)
{
if (model == null || string.IsNullOrEmpty(model.unitID))
{
return(null);
}
List <A02Model> list = DBSession.IA02DAL.SelectA02CheckByMonthData(model);
if (list == null || list.Count <= 0)
{
return(null);
}
EchartsVo dChart = new EchartsVo();
List <Series> seriesList = new List <Series>(); //数据集合
List <string> xAxis = new List <string>(); //x轴数据
List <decimal?> aPersons = new List <decimal?>();
foreach (A02Model item in list)
{
xAxis.Add(item.cardDate);
aPersons.Add(item.countPersons);
}
seriesList.Add(
new Series()
{
name = "打卡人数",
type = "bar",
data = aPersons
});
dChart.xAxis = xAxis;
dChart.seriesList = seriesList;
return(dChart);
}
public List <A02Model> SelectA02Data(HCQ2_Model.SelectModel.A02Model model)
{
if (model == null || string.IsNullOrEmpty(model.unitID))
{
return(null);
}
return(DBSession.IA02DAL.SelectA02CheckByMonthData(model));
}
public List <PunchCardModel> SelectCardPersons(HCQ2_Model.SelectModel.A02Model model)
{
StringBuilder sb = new StringBuilder();
if (model == null || string.IsNullOrEmpty(model.unitID))
{
return(null);
}
sb.Append(string.Format(@"SELECT * FROM(
SELECT a1.A0177, a1.A0101, a1.A0178, a1.UnitID, a1.A0141, a1.A0142, b1.UnitName, a2.A0201,
ROW_NUMBER() OVER(ORDER BY a1.DispOrder ASC) as rank FROM
(SELECT A0177, A0101, A0178,UnitID, A0141, A0142, PersonID, DispOrder FROM dbo.A01 WHERE UnitID LIKE '{0}%') a1 INNER JOIN
(SELECT PersonID, LEFT(convert(char(10), MAX(A0201), 23), 7) AS A0201 FROM dbo.A02 WHERE 1=1 ", model.unitID));
if (!string.IsNullOrEmpty(model.cardDate))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10),A0201,23),7)='{0}' ", model.cardDate));
}
else
{
if (!string.IsNullOrEmpty(model.dateStart) && !string.IsNullOrEmpty(model.dateEnd))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10),A0201,23),7) BETWEEN '{0}' AND '{1}' ", model.dateStart, model.dateEnd));
}
else if (!string.IsNullOrEmpty(model.dateStart))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10),A0201,23),7)>='{0}' ", model.dateStart));
}
else if (!string.IsNullOrEmpty(model.dateEnd))
{
sb.AppendLine(string.Format(" AND LEFT(convert(char(10),A0201,23),7)<='{0}' ", model.dateEnd));
}
}
sb.AppendLine(string.Format(@" GROUP BY PersonID) a2 ON a1.PersonID = a2.PersonID LEFT JOIN
(SELECT UnitName, UnitID FROM dbo.B01) b1 ON a1.UnitID = b1.UnitID
) info "));
if (model.page > 0 && model.rows > 0)
{
sb.AppendLine(string.Format(" WHERE info.rank BETWEEN {0} AND {1}",
(model.page - 1) * model.rows, model.page * model.rows));
}
sb.AppendLine("ORDER BY info.rank ASC;");
DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <PunchCardModel>(dt));
}
public List <PunchCardModel> SelectCardPersons(HCQ2_Model.SelectModel.A02Model model)
{
return(DBSession.IA02DAL.SelectCardPersons(model));
}
