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 ResponseDocListAttachment DocAttachment([FromBody] RequestDocListSearch request)
{
try
{
DocBLL bll = new DocBLL();
return(bll.DocAttachment(request));
}
catch (Exception ex)
{
throw new HttpResponseException(
Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
public ResponseDocListAttachment DocAttachment(RequestDocListSearch request)
{
ResponseDocListAttachment response = new ResponseDocListAttachment();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
string where = string.Empty;
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);
}
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") + "'";
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 (periodS == null)
{
return(new ResponseDocListAttachment {
IsSuccess = false, Message = "会计期间没有选择"
});
}
if (periodS.EndDate > periodE.EndDate)
{
throw new NormalException("开始期间不能大于结束期间");
}
//获取期间集合
List <TKS_FAS_MonthPeriodInfo> periods = cnn.Query <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where AccountId=@AccountId
and StartDate >=@StartDateS
and StartDate<=@StartDateE ",
new
{
StartDateS = periodS.StartDate.ToString(),
StartDateE = periodE.StartDate.ToString(),
AccountId = user.AccountId
}, ts).ToList();
string periodWhere = string.Empty;//期间条件
int j = 0;
foreach (var period in periods)
{
if (j > 0)
{
periodWhere += " or ";
}
periodWhere += " periodId='" + period.Id + "'";
j++;
}
periodWhere = " (" + periodWhere + ")";
string getdoc = string.Format(@"select * from TKS_FAS_Doc where AccountId=@AccountId and {0}", periodWhere);
List <TKS_FAS_Doc> doc = cnn.Query <TKS_FAS_Doc>(getdoc, new { AccountId = user.AccountId }, ts).ToList();
string docWhere = "";
int t = 0;
foreach (var d in doc)
{
if (t > 0)
{
docWhere += " or ";
}
docWhere += " DocId='" + d.Id + "'";
t++;
}
docWhere = " (" + docWhere + ")";
string getAttachment = string.Format(@"select * from TKS_FAS_Attachment where AccountId='{0}' and {1}", user.AccountId, docWhere);
List <TKS_FAS_Attachment> lstAttachment = cnn.Query <TKS_FAS_Attachment>(getAttachment, null, ts).ToList();
List <string> lstUrl = new List <string>();
foreach (var item in lstAttachment)
{
lstUrl.Add(item.Path);
}
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.ImgUrl = lstUrl;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocListAttachment);
}
}
}
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);
}
}
}