/// <summary> /// 获取泛型类的集合 /// </summary> /// <typeparam name="T">泛型</typeparam> /// <param name="entity">泛型对象</param> /// <param name="financePage">分页对象</param> /// <param name="company">公司名</param> /// <param name="columnName">特殊字段处理</param> /// <returns>有pageList的分页对象</returns> public FinancePage <T> getComList <T>(T entity, FinancePage <T> financePage, string company, string columnName) { var companyParam = new SqlParameter("@company", company); var maxPageParam = new SqlParameter("@maxPage", financePage.getMax()); var minPageParam = new SqlParameter("@minPage", financePage.getMin()); string sqlString = fin.Set(entity.GetType()).ToString(); string sql = "select l.*,l." + columnName + " as " + columnName + "1 from (select ROW_NUMBER() over(order by id) as rownum,a.* from (" + sqlString + ") as a) as l where l.rownum > @minPage and l.rownum < @maxPage and l.company = @company"; var result = fin.Set(entity.GetType()).SqlQuery(sql, companyParam, maxPageParam, minPageParam); try { var selectList = result.GetEnumerator(); while (selectList.MoveNext()) { financePage.pageList.Add((T)selectList.Current); } } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
public FinancePage <AccountingItem> getList2(FinancePage <AccountingItem> financePage, string company, int classId, string code) { //类别 var classParam = new SqlParameter("@class", classId); //公司 var companyParam = new SqlParameter("@company", company); //查询最小行号 var minPageParam = new SqlParameter("@minPageParam", financePage.getMin()); //查询最大行号 var maxPageParam = new SqlParameter("@maxPageParam", financePage.getMax()); var kemudaima = new SqlParameter("@code", code); string sql = "select (case len(code) when 4 then 'I' when 6 then 'II' when 8 then 'III' else '' end) as grade,*,isnull((SELECT SUM(money) FROM VoucherSummary WHERE VoucherSummary.code = a.code),0) as money,isnull((select top 1 name from Accounting as ac where ac.code = LEFT(a.code,4)),'') + isnull((select top 1 '-'+name from Accounting as ac where ac.code = LEFT(a.code,6) and ac.code != LEFT(a.code,4)),'') + isnull((select top 1 '-'+name from Accounting as ac where ac.code = LEFT(a.code,8) and ac.code != LEFT(a.code,6)),'') as fullName from (select *,ROW_NUMBER() over(order by LEN(code),id) as rownum from (select * from (SELECT *,LEFT(code, 1) AS class from Accounting) as t where t.class = @class and t.company = @company) as c )as a where a.rownum > @minPageParam and a.rownum < @maxPageParam"; if (code != "") { sql = sql + " and code like '%" + code + "%'"; } var result = fin.Database.SqlQuery <AccountingItem>(sql, classParam, companyParam, minPageParam, maxPageParam); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
/// <summary> /// 获取集合 /// </summary> /// <param name="financePage">分页对象</param> /// <param name="company">公司名</param> /// <returns>有pegeList的分页对象</returns> public FinancePage <Profit> getProfitList(FinancePage <Profit> financePage, string company) { var companyParam = new SqlParameter("@company", company); var minPageParam = new SqlParameter("@minPage", financePage.getMin()); var maxPageParam = new SqlParameter("@maxPage", financePage.getMax()); var directionsParam = new SqlParameter("@direction", financePage.selectParamsMap["direction"]); var start_date = new SqlParameter("@start_date", financePage.selectParamsMap["start_date"]); var stop_date = new SqlParameter("@stop_date", financePage.selectParamsMap["stop_date"]); string sql = "select name,sum_month,sum_year from (select name,y.sum_month,y.sum_year,row_number() over(order by name) as rownum from Accounting as a,(SELECT code,isnull((SELECT sum(money) FROM VoucherSummary WHERE voucherDate >= CONVERT(date,@start_date) and voucherDate <= CONVERT(date,@stop_date) AND code = y.code),0) AS sum_month,isnull((SELECT sum(money) FROM VoucherSummary WHERE YEAR(voucherDate) = year(CONVERT(date,@start_date)) AND code = y.code),0) AS sum_year FROM VoucherSummary AS y WHERE company = @company and YEAR(voucherDate) = year(CONVERT(date,@start_date)) GROUP BY y.code) as y where a.code = y.code and a.company = @company and a.direction = @direction) as t where t.rownum > @minPage and t.rownum < @maxPage"; var result = fin.Database.SqlQuery <Profit>(sql, companyParam, minPageParam, maxPageParam, directionsParam, start_date, stop_date); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
/// <summary> /// 获取集合 /// </summary> /// <param name="financePage">分页对象</param> /// <param name="company">公司名</param> /// <returns>有pegeList的分页对象</returns> public FinancePage <Flow> getFlowList(FinancePage <Flow> financePage, string company) { var companyParam = new SqlParameter("@company", company); var minPageParam = new SqlParameter("@minPage", financePage.getMin()); var maxPageParam = new SqlParameter("@maxPage", financePage.getMax()); var start_date = new SqlParameter("@start_date", financePage.selectParamsMap["start_date"]); var stop_date = new SqlParameter("@stop_date", financePage.selectParamsMap["stop_date"]); //string sql = "select all_.expenditure,all_.money_month,all_.money_year from (select row_number() over(order by expenditure) as rownum,expenditure,isnull((select sum(s.money) from VoucherSummary as s where company = @company and year(voucherDate) = @year and month(voucherDate) = @month and s.expenditure = v.expenditure),0) as money_month,isnull((select sum(s.money) from VoucherSummary as s where company = @company and year(voucherDate) = @year and s.expenditure = v.expenditure),0) as money_year from VoucherSummary as v where company = @company GROUP BY expenditure) as all_ where all_.rownum > @minPage and all_.rownum < @maxPage"; string sql = "select all_.expenditure,all_.money_month,all_.money_year from (select row_number() over(order by expenditure) as rownum,expenditure,isnull((select sum(s.money) from VoucherSummary as s where company = @company and voucherDate >= CONVERT(date,@start_date) and voucherDate <= CONVERT(date,@stop_date) and s.expenditure = v.expenditure),0) as money_month,isnull((select sum(s.money) from VoucherSummary as s where company = @company and year(voucherDate) = year(CONVERT(date,@start_date)) and s.expenditure = v.expenditure),0) as money_year from VoucherSummary as v where company = @company GROUP BY expenditure) as all_ where all_.rownum > @minPage and all_.rownum < @maxPage"; var result = fin.Database.SqlQuery <Flow>(sql, companyParam, minPageParam, maxPageParam, start_date, stop_date); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
/// <summary> /// 获取集合 /// </summary> /// <param name="financePage">分页对象</param> /// <param name="company">公司名</param> /// <returns>有pegeList的分页对象</returns> public FinancePage <Liabilities> getLiabilitiesList(FinancePage <Liabilities> financePage, string company) { var companyParam = new SqlParameter("@company", company); var minPageParam = new SqlParameter("@minPage", financePage.getMin()); var maxPageParam = new SqlParameter("@maxPage", financePage.getMax()); var classParam = new SqlParameter("@class", financePage.selectParamsMap["classId"]); var start_date = new SqlParameter("@start_date", financePage.selectParamsMap["start_date"]); var stop_date = new SqlParameter("@stop_date", financePage.selectParamsMap["stop_date"]); //string sql = "select all_.name,all_.load,all_.borrowed,all_.money from (select row_number() over(order by a.name) as rownum,a.name,sum(a.load) as load,sum(a.borrowed) as borrowed,isnull(sum(v.money),0) as money from Accounting as a left join VoucherSummary as v on a.code = v.code and v.company = @company and year(v.voucherDate) = @year and month(v.voucherDate) = @month where a.company = @company and left(a.code,1) = @class group by a.name,left(a.code,1)) as all_ where all_.rownum > @minPage and all_.rownum < @maxPage"; string sql = "select name,[load],borrowed,CONVERT(Decimal,0) as money from (select row_number() over(order by a.name) as rownum, a.name,left(a.code,1) as class,v.company,(CASE @class WHEN 1 THEN sum(load-borrowed) ELSE sum(borrowed-load) END) as [load],(CASE @class WHEN 1 THEN sum([load]-borrowed+ISNULL(v.money, 0)) ELSE sum(borrowed-[load]+ISNULL(v.money, 0)) END) as borrowed from Accounting as a left join VoucherSummary as v on a.code = v.code WHERE left(a.code,1) = @class and a.company = @company and v.voucherDate >= convert(date,@start_date) and v.voucherDate <= convert(date,@stop_date) GROUP BY a.code,a.name,v.company) as a where (a.company = @company or a.company is null) and a.rownum > @minPage and a.rownum < @maxPage"; //string sql = "select all_.name,all_.load,all_.borrowed,all_.money from (select row_number() over(order by a.name) as rownum,a.name,sum(a.load) as load,sum(a.borrowed) as borrowed,isnull(sum(v.money),0) as money from Accounting as a left join VoucherSummary as v on a.code = v.code and v.company = @company and v.voucherDate >= CONVERT(date,@start_date) and v.voucherDate <= CONVERT(date,@stop_date) where a.company = @company and left(a.code,1) = 1 group by a.name,left(a.code,1)) as all_ where all_.rownum > @minPage and all_.rownum < @maxPage"; var result = fin.Database.SqlQuery <Liabilities>(sql, companyParam, minPageParam, maxPageParam, classParam, start_date, stop_date); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
/// <summary> /// 查询list /// </summary> /// <param name="financePage">分页对象</param> /// <param name="company">公司</param> /// <returns>有pageList的分页对象</returns> public FinancePage <VoucherSummaryItem> getList(FinancePage <VoucherSummaryItem> financePage, string company) { //年 string start_date = financePage.selectParamsMap["start_date"]; //月 string stop_date = financePage.selectParamsMap["stop_date"]; var @params = new SqlParameter[6] { //公司 new SqlParameter("@company", company), //查询最小行数 new SqlParameter("@minPage", financePage.getMin()), //查询最大行数 new SqlParameter("@maxPage", financePage.getMax()), //凭证字 new SqlParameter("@word", financePage.selectParamsMap["word"]), //年 new SqlParameter("@start_date", start_date), //月 new SqlParameter("@stop_date", stop_date) }; string sql = "select * from (select isnull((select name from Accounting where code = LEFT (vs.code, 4)),'')+isnull((select top 1 '-'+name from Accounting where code = LEFT (vs.code, 6) and code != LEFT (vs.code, 4)),'')+isnull((select top 1 '-'+name from Accounting where code = LEFT (vs.code, 8) and code != LEFT (vs.code, 6)),'') as fullName,vs.id,vs.word,vs.[no],voucherDate,vs.abstract,vs.code,vs.department,vs.expenditure,vs.note,vs.man,ac.name,isnull(ac.load,0) as load,isnull(ac.borrowed,0) as borrowed,vs.money,vs.real,ROW_NUMBER() over(order by vs.id) rownum from VoucherSummary as vs left join Accounting as ac on vs.code = ac.code and ac.company = @company where vs.company = @company) t where t.rownum > @minPage and t.rownum < @maxPage and t.word like '%'+@word+'%'"; if (!start_date.Equals(string.Empty)) { sql += " and t.voucherDate >= @start_date"; } if (!stop_date.Equals(string.Empty)) { sql += " and t.voucherDate <= @stop_date"; } var result = fin.Database.SqlQuery <VoucherSummaryItem>(sql, @params); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
public FinancePage <User_ManagementItem> getList(FinancePage <User_ManagementItem> financePage, string company) { //公司 var companyParam = new SqlParameter("@company", company); //查询最小行号 var minPageParam = new SqlParameter("@minPageParam", financePage.getMin()); //查询最大行号 var maxPageParam = new SqlParameter("@maxPageParam", financePage.getMax()); string sql = "select a.id,a.rownum,a.name,a.pwd,a.do,a.bianhao from (select *,row_number() over(order by id) as rownum from Account where company = @company) as a where a.rownum > @minPageParam and a.rownum < @maxPageParam"; var result = fin.Database.SqlQuery <User_ManagementItem>(sql, companyParam, minPageParam, maxPageParam); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
public FinancePage <DepartmentItem> getList2(FinancePage <DepartmentItem> financePage, string company, string dep) { //公司 var companyParam = new SqlParameter("@company", company); //查询最小行号 var minPageParam = new SqlParameter("@minPageParam", financePage.getMin()); //查询最大行号 var maxPageParam = new SqlParameter("@maxPageParam", financePage.getMax()); string sql = "select a.id,a.rownum,a.department as department1,a.man,a.company from (select *,row_number() over(order by id) as rownum from Department where company = @company) as a where a.rownum > @minPageParam and a.rownum < @maxPageParam and department like '%" + dep + "%'"; var result = fin.Database.SqlQuery <DepartmentItem>(sql, companyParam, minPageParam, maxPageParam); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
/// <summary> /// 获取集合 /// </summary> /// <param name="financePage">分页对象</param> /// <param name="company">公司名</param> /// <returns>有pegeList的分页对象</returns> public FinancePage <SimpleAccountingSummary> getList(FinancePage <SimpleAccountingSummary> financePage, string company) { var companyParam = new SqlParameter("@company", company); var minPageParam = new SqlParameter("@minPage", financePage.getMin()); var maxPageParam = new SqlParameter("@maxPage", financePage.getMax()); var accountingParam = new SqlParameter("@accounting", financePage.selectParamsMap["accounting"]); //string sql = "select a.id,a.accounting,a.company from (select row_number() over(order by id) as rownum,* from SimpleAccounting where company = @company and accounting like '%'+@accounting+'%') as a where a.rownum > @minPage and a.rownum < @maxPage"; string sql = "select * from (select row_number() over(order by a.accounting desc) as ROW_ID,a.id,a.accounting,ISNULL(sum(d.receivable), 0) as receivable,ISNULL(sum(d.receipts), 0) as receipts,ISNULL(sum(d.receivable-d.receipts), 0) as notget1,ISNULL(sum(d.cope), 0) as cope,ISNULL(sum(d.payment), 0) as payment,ISNULL(sum(d.cope-d.payment), 0) as notget2 from SimpleAccounting as a LEFT JOIN SimpleData as d on a.accounting = d.accounting where a.company = @company GROUP BY a.accounting,a.company,a.id) as a where a.ROW_ID > @minPage and a.ROW_ID < @maxPage"; var result = fin.Database.SqlQuery <SimpleAccountingSummary>(sql, companyParam, minPageParam, maxPageParam); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }
/// <summary> /// 查询list /// </summary> /// <param name="financePage">分页对象</param> /// <param name="company">公司</param> /// <returns>有pageList的分页对象</returns> public FinancePage <VoucherWord> getList(FinancePage <VoucherWord> financePage, string company) { //公司 var companyParam = new SqlParameter("@company", company); //查询最小行数 var minPageParam = new SqlParameter("@minPage", financePage.getMin()); //查询最大行数 var maxPageParam = new SqlParameter("@maxPage", financePage.getMax()); string sql = "select a.* from (select ROW_NUMBER() over(order by id) rownum,* from VoucherWord where company = @company) as a where a.rownum > @minPage and a.rownum < @maxPage"; var result = fin.Database.SqlQuery <VoucherWord>(sql, companyParam, minPageParam, maxPageParam); try { financePage.pageList = result.ToList(); } catch (Exception ex) { FinanceToError.getFinanceToError().toError(); } return(financePage); }