/// <summary> /// 获得查询分页数据 /// </summary> public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT c.id,c.year,c.month,c.empid,c.name,c.yfje,c.kk,c.gjj,c.bxkk,c.zxkc,c.jckk "); strSql.Append(" ,(c.kk+c.gjj+c.bxkk+c.zxkc+c.jckk) as total "); strSql.Append(" FROM ( "); strSql.Append(" SELECT A.id,a.year,a.month, a.empid,a.name,a.yfje,(a.kqtk+a.kk+a.gsk) as kk,(a.gjj2+a.gjj) as gjj,a.bxkk,"); strSql.Append(" ISNULL((b.znjy+b.jxjy+b.dbyl+b.zfdk+b.zfzj+b.sylr),0) as zxkc,5000 as jckk"); strSql.Append(" FROM t_gzmx a "); strSql.Append(" LEFT JOIN t_zxkc b "); strSql.Append(" on a.empid=b.empid and a.year=b.year and a.month=b.month "); //strSql.Append(" where a.year='2019' and a.month='2' "); strSql.Append(" group by A.id, a.year,a.month, a.empid,a.name,a.yfje,a.kqtk,a.kk,a.gsk,a.gjj2,a.gjj,a.bxkk, "); strSql.Append(" b.znjy,b.jxjy,b.dbyl,b.zfdk,b.zfzj,b.sylr "); strSql.Append(" ) c "); if (strWhere != "") { strSql.Append(" where " + strWhere); } strSql.Append(" group by c.id,c.year,c.month,c.empid,c.name,c.yfje,c.kk,c.gjj,c.bxkk,c.zxkc,c.jckk "); /* strSql.Append(" SELECT id,year,month,empid,name,yfje,kqtk,kk,gsk,gjj2,gjj,bxkk,sfje"); * strSql.Append(" FROM r_jshz "); * if (strWhere != "") * { * strSql.Append(" where " + strWhere); * } * strSql.Append(" group by id,year,month,empid,name,yfje,kqtk,kk,gsk,gjj2,gjj,bxkk,sfje ");*/ recordCount = Convert.ToInt32(DbHelperSql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString()))); return(DbHelperSql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder))); }
/// <summary> /// 获取当月个税汇总 /// </summary> public double taxCount(string year, string month) { StringBuilder strSql = new StringBuilder(); strSql.Append("select sum(bygs) as bygscount from r_jshz "); strSql.Append(" where year=@year and month=@month"); SqlParameter[] parameters = { new SqlParameter("@year", SqlDbType.VarChar, 100), new SqlParameter("@month", SqlDbType.VarChar, 100) }; parameters[0].Value = year; parameters[1].Value = month; object obj = DbHelperSql.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(0); } else { return(double.Parse(obj.ToString())); } }
/// <summary> /// 得到最大ID /// </summary> private int GetMaxId(SqlConnection conn, SqlTransaction trans) { string strSql = "select top 1 id from r_jshz order by id desc"; object obj = DbHelperSql.GetSingle(conn, trans, strSql); if (obj == null) { return(0); } else { return(int.Parse(obj.ToString())); } }
/// <summary> /// 获得查询分页数据 /// </summary> public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT id,name,departid,username,password,role,addtime,note "); strSql.Append(" FROM t_users "); if (strWhere != "") { strSql.Append(" where " + strWhere); } strSql.Append(" group by id,name,departid,username,password,role,addtime,note "); recordCount = Convert.ToInt32(DbHelperSql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString()))); return(DbHelperSql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder))); }
/// <summary> /// 获得查询分页数据 /// </summary> public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT id,year,month,empid,name,cast(yfje as float) yfje,cast(kqtk as float) kqtk,cast(kk as float) kk,"); strSql.Append(" cast(gsk as float) gsk,cast(gjj2 as float) gjj2,cast(gjj as float) gjj,cast(bxkk as float) bxkk,"); strSql.Append(" cast(sfje as float) sfje"); strSql.Append(" FROM t_gzmx "); if (strWhere != "") { strSql.Append(" where " + strWhere); } strSql.Append(" group by id,year,month,empid,name,yfje,kqtk,kk,gsk,gjj2,gjj,bxkk,sfje "); recordCount = Convert.ToInt32(DbHelperSql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString()))); return(DbHelperSql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder))); }
/// <summary> /// 获得查询分页数据 /// </summary> public DataSet GetList1(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT id,year,month,depart,departid,empid,name,gzjb,"); strSql.Append(" cast(yfje as float) yfje,cast(kk as float) kk, cast(gjj as float) gjj,cast(bxkk as float) bxkk,"); strSql.Append(" cast(zxkk as float) zxkk,cast(jckk as INT) jckk ,cast(ysgzlj as float) ysgzlj,cast(gslj as float) gslj,"); strSql.Append(" cast(bygs as float) bygs,cast((kk+gjj+bxkk+zxkk+jckk) as float) total "); strSql.Append(" FROM r_jshz "); if (strWhere != "") { strSql.Append(" where " + strWhere); } strSql.Append(" group by id,year,month,depart,departid,empid,name,gzjb,yfje,kk,gjj,bxkk,zxkk,jckk,ysgzlj,gslj,bygs "); recordCount = Convert.ToInt32(DbHelperSql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString()))); return(DbHelperSql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder))); }
/// <summary> /// 获得查询分页数据 /// </summary> public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT id,year,month,empid,name,cast(znjy as float) znjy,cast(jxjy as float) jxjy,cast(dbyl as float) dbyl,"); strSql.Append(" cast(zfdk as float) zfdk, cast(zfzj as float) zfzj,cast(sylr as float) sylr, "); strSql.Append(" cast((znjy+jxjy+dbyl+zfdk+zfzj+sylr) as float) as total "); strSql.Append(" FROM t_zxkc "); if (strWhere != "") { strSql.Append(" where " + strWhere); } strSql.Append(" group by id,year,month,empid,name,znjy,jxjy,dbyl,zfdk,zfzj,sylr "); recordCount = Convert.ToInt32(DbHelperSql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString()))); return(DbHelperSql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder))); }