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