public List <WGJG02Model> GetWageDetailByRowId(HCQ2_Model.SelectModel.WGJG01ChartModel model) { StringBuilder sb = new StringBuilder(); sb.Append(@"SELECT w1.WGJG0201,w1.WGJG0202,w1.A0101,w1.A0177,b1.B0002,b1.B0002 AS UnitID,code1.E0386,code2.WGJG0203,w1.WGJG0204, w1.WGJG0205,w1.WGJG0206,w1.WGJG0207,w1.WGJG0208,w1.WGJG0209,w1.WGJG0211,w1.WGJG0212,w1.PClassID FROM "); sb.Append(" (SELECT *,ROW_NUMBER() OVER (ORDER BY RowID) as rank FROM dbo.WGJG02 WHERE PersonID IN (SELECT PersonID FROM dbo.A01) AND "); if (!string.IsNullOrEmpty(model.rowID)) { sb.Append(string.Format(" WGJG01RowID='{0}' ", model.rowID)); } else { sb.Append(string.Format(" WGJG01RowID IN(SELECT RowID FROM dbo.WGJG01 WHERE UnitID LIKE '{0}%' ", model.unitID)); if (!string.IsNullOrEmpty(model.dateStart) && !string.IsNullOrEmpty(model.dateEnd)) { sb.Append(string.Format(" AND WGJG0102 BETWEEN '{0}' AND '{1}' ", model.dateStart, model.dateEnd)); } else if (!string.IsNullOrEmpty(model.dateStart)) { sb.Append(string.Format(" AND WGJG0102>='{0}' ", model.dateStart)); } else if (!string.IsNullOrEmpty(model.dateEnd)) { sb.Append(string.Format(" AND WGJG0102<='{0}' ", model.dateEnd)); } } //关键字 if (!string.IsNullOrEmpty(model.keyword)) { sb.Append(string.Format(" AND A0101 LIKE '%{0}%' ", model.keyword)); } if (!string.IsNullOrEmpty(model.isGive) && model.isGive.Equals("1")) { sb.Append(" AND WGJG0211='1'"); } else if (!string.IsNullOrEmpty(model.isGive)) { sb.Append(" AND ISNULL(WGJG0211,'')<>'1'"); } if (string.IsNullOrEmpty(model.rowID)) { sb.Append(")"); } sb.Append(") w1 LEFT JOIN "); sb.Append(@"(SELECT UnitID,UnitName AS B0002 FROM dbo.B01) b1 ON w1.UnitID=b1.UnitID LEFT JOIN (SELECT CodeItemID,CodeItemName AS E0386 FROM dbo.SM_CodeItems WHERE CodeID='JA') code1 ON w1.E0386=code1.CodeItemID LEFT JOIN (SELECT CodeItemID,CodeItemName AS WGJG0203 FROM dbo.SM_CodeItems WHERE CodeID='GZFFFS') code2 ON w1.WGJG0203=code2.CodeItemID "); sb.Append(string.Format(" WHERE w1.rank between {0} and {1};", (model.page - 1) * model.rows, model.page * model.rows)); DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text); return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <WGJG02Model>(dt)); }
public List <WGJG02Model> GetWageDetailByRowId(HCQ2_Model.SelectModel.WGJG01ChartModel model) { return(DBSession.IWGJG02DAL.GetWageDetailByRowId(model)); }