Beispiel #1
0
        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));
        }
Beispiel #2
0
 public List <WGJG02Model> GetWageDetailByRowId(HCQ2_Model.SelectModel.WGJG01ChartModel model)
 {
     return(DBSession.IWGJG02DAL.GetWageDetailByRowId(model));
 }