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