public ResponseDocListSearch GetDocByAccount(RequestDocListSearch request)
{
ResponseDocListSearch response = new ResponseDocListSearch();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
List <TKS_FAS_Doc> data = cnn.Query <TKS_FAS_Doc>(@"select * from TKS_FAS_Doc
where AccountId=@AccountId
order by PZDate,PeriodId,PZZ,PZZNO",
new
{
AccountId = user.AccountId
}, ts).ToList();
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.lstAll = data;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocListSearch);
}
}
}
public ResponseDocListSearch DocListSearch(RequestDocListSearch request)
{
ResponseDocListSearch response = new ResponseDocListSearch();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
string where = string.Empty;
object predicate;
object accountList;
if (request.Type == "normal")
{
#region 一般查询
if (!string.IsNullOrEmpty(request.PZZ))
{
where = " and A.pzz=@PZZ";
}
if (!string.IsNullOrEmpty(request.PZZ_S))
{
where += " and pzzNo>=" + int.Parse(request.PZZ_S);
}
if (!string.IsNullOrEmpty(request.PZZ_E))
{
where += " and pzzNo<=" + int.Parse(request.PZZ_E);
}
if (!string.IsNullOrEmpty(request.Period) && request.Period != "##")
{
where += " and A.periodId='" + request.Period + "'";
}
predicate = new
{
AccountId = user.AccountId,
PZZ = request.PZZ
};
#endregion
}
else
{
#region 更多查询
if (!string.IsNullOrEmpty(request.More.PZZ))
{
where = " and A.pzz=@PZZ";
}
if (!string.IsNullOrEmpty(request.More.PZZ_S))
{
where += " and pzzNo>=" + int.Parse(request.More.PZZ_S);
}
if (!string.IsNullOrEmpty(request.More.PZZ_E))
{
where += " and pzzNo<=" + int.Parse(request.More.PZZ_E);
}
if (!string.IsNullOrEmpty(request.More.Period_S))
{
var periodS = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where id=@Id",
new { Id = request.More.Period_S }, ts);
where += " and A.pzdate>='" + DateTime.Parse(periodS.StartDate.ToString()).ToString("yyyy-MM-dd") + "'";
}
if (!string.IsNullOrEmpty(request.More.Period_E))
{
var periodE = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where id=@Id",
new { Id = request.More.Period_E }, ts);
where += " and A.pzdate<='" + DateTime.Parse(periodE.EndDate.ToString()).ToString("yyyy-MM-dd") + "'";
}
if (!string.IsNullOrEmpty(request.AccountList))
{
where += " and A.accountId in" + request.AccountList;
}
predicate = new
{
AccountId = user.AccountId,
PZZ = request.More.PZZ
};
#endregion
}
string sql = this.GetPageSql("A.*,B.pzz as PZZName," +
"AttachmentCount=(select count(att.Id) from TKS_FAS_Attachment att where att.DocId=A.Id ) ",
@" TKS_FAS_Doc A left join TKS_FAS_CertificateWord B on A.pzz=B.ID
where A.accountId=@AccountId " + where,
" A.PZDate,A.PeriodId,B.PZZ,A.PZZNO",
request.PageSize,
request.PageIndex);
List <TKS_FAS_DocExt> data = cnn.Query <TKS_FAS_DocExt>(sql, predicate, ts).ToList();
if (!string.IsNullOrEmpty(request.AccountList))
{
accountList = new
{
AccountList = request.AccountList,
PZZ = request.More.PZZ
};
//打印工具专用
sql = this.GetPageSql(" row_number() OVER ( ORDER BY A.PZZNO ASC ) AS RowNum,A.*,B.pzz as PZZName," +
"AttachmentCount=(select count(att.Id) from TKS_FAS_Attachment att where att.DocId=A.Id ) ",
@" TKS_FAS_Doc A left join TKS_FAS_CertificateWord B on A.pzz=B.ID
where 1=1 " + where,
" A.pzDate desc,A.pzzNo ",
request.PageSize,
request.PageIndex);
data = cnn.Query <TKS_FAS_DocExt>(sql, accountList, ts).ToList();
}
string countSql = @"select count(1) from TKS_FAS_Doc A
where A.accountId=@AccountId " + where;
int total = int.Parse(cnn.ExecuteScalar(countSql, predicate, ts).ToString());
List <string> docIds = data.Select(p => p.Id).ToList();
string ids = "('" + string.Join("','", docIds) + "')";
sql = @"select * from TKS_FAS_DocDetail where parentId in " + ids;
var detail = cnn.Query <TKS_FAS_DocDetail>(sql, null, ts).ToList();
var result = (from item in data
select new DocList
{
Head = item,
Detail = detail.Where(p => p.ParentId == item.Id).OrderBy(p => p.Seq).ToList()
}).ToList();
AccountSetBLL account = new AccountSetBLL(cnn);
var currentAccount = account.GetAccountSetByAccountId(user.AccountId, ts);
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.PageIndex = request.PageIndex;
response.Data = result;
response.Total = total;
response.AccountName = currentAccount.QY_Name;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocListSearch);
}
}
}