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