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