public BsTableDataSource <AuditViewModel> GET(NoAuthticationGetAuditApplication request)
{
return(NoAuthticationAuditManager.NoAuthticationGetAuditApplication(request));
}
/// <summary>
/// 市县级获取审核申请
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public BsTableDataSource <AuditViewModel> NoAuthticationGetAuditApplication(NoAuthticationGetAuditApplication request)
{
var _year = System.DateTime.Now.Year;
try
{
using (var db = DbFactory.Open())
{
#region county
var _adcd = string.IsNullOrEmpty(request.adcd) ? adcd : request.adcd;
if (request.level.Value == 3)
{
List <AuditViewModel> RList1 = null;
List <AuditViewModel> RList0 = null;
//统计
//待审批
var lable1 = NoAuthticationgetCount(1, 6, _adcd);
//已审未批
var lable2 = NoAuthticationgetCount(2, 6, _adcd);
//已批
var lable3 = NoAuthticationgetCount(3, 6, _adcd);
//1
var builder = db.From <Model.Audit.Audit>();
builder.LeftJoin <Model.Audit.Audit, ADCDInfo>((x, y) => x.TownADCD == y.adcd);
builder.Where(x => x.TownADCD.StartsWith(_adcd.Substring(0, 6)));
builder.Select(" ADCDInfo.adnm,Audit.*");
RList1 = db.Select <AuditViewModel>(builder);
//0
var sql = "";
if (RList1.Count == 0)
{
sql = "SELECT ADCDInfo.adcd,ADCDInfo.adnm FROM ADCDInfo WHERE SUBSTRING(ADCDInfo.adcd, 10, 6) = '000000' AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 6) + "%'";
}
else
{
//sql = "SELECT distinct(ADCDInfo.adcd),ADCDInfo.adnm FROM Audit,ADCDInfo " +
// "WHERE ADCDInfo.adcd != Audit.TownADCD AND SUBSTRING(ADCDInfo.adcd, 10, 6) = '000000' AND Audit.Year = " + _year + " AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 6) + "%'";
sql = "select a.adcd,a.adnm from (SELECT ADCDInfo.adcd, ADCDInfo.adnm FROM ADCDInfo WHERE SUBSTRING(ADCDInfo.adcd, 10, 6) = '000000'" +
"AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 6) + "%') as a where a.adcd not in(select TownADCD as adcd from Audit where Audit.Year = " + _year + " and Audit.TownADCD like '" + _adcd.Substring(0, 6) + "%')";
}
RList0 = db.Select <AuditViewModel>(sql);
RList1.AddRange(RList0);
return(new BsTableDataSource <AuditViewModel>()
{
rows = RList1, total = RList1.Count + RList0.Count, other = RList0.Count.ToString() + "|" + lable1 + "|" + lable2 + "|" + lable3
});
}
#endregion
#region city
if (request.level.Value == 2)
{
List <AuditViewModel> RList1 = null;
List <AuditViewModel> RList0 = null;
//统计
//var leng = string.IsNullOrEmpty(request.adcd)
//待审批
var lable1 = NoAuthticationgetCount(1, 4, _adcd);
//已审未批
var lable2 = NoAuthticationgetCount(2, 4, _adcd);
//已批
var lable3 = NoAuthticationgetCount(3, 4, _adcd);
//1
var builder = db.From <Model.Audit.Audit>();
builder.LeftJoin <Model.Audit.Audit, ADCDInfo>((x, y) => x.TownADCD == y.adcd);
builder.Where(x => x.TownADCD.StartsWith(_adcd.Substring(0, 4)));
builder.Select(" ADCDInfo.adnm,Audit.*");
RList1 = db.Select <AuditViewModel>(builder);
//0
var sql = "";
if (RList1.Count == 0)
{
sql = "SELECT ADCDInfo.adcd,ADCDInfo.adnm FROM ADCDInfo WHERE SUBSTRING(ADCDInfo.adcd, 10, 6) = '000000' AND ADCDInfo.adcd not like '%000000000' AND SUBSTRING(ADCDInfo.adcd, 7, 9) != '000000000' AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 4) + "%'";
}
else
{
// sql = "SELECT distinct(ADCDInfo.adcd),ADCDInfo.adnm FROM Audit,ADCDInfo " +
// "WHERE ADCDInfo.adcd != Audit.TownADCD AND SUBSTRING(ADCDInfo.adcd, 10, 6) = '000000' AND Audit.Year = " + _year + " AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 4) + "%'";
sql = "select a.adcd,a.adnm from (SELECT ADCDInfo.adcd, ADCDInfo.adnm FROM ADCDInfo WHERE SUBSTRING(ADCDInfo.adcd, 10, 6) = '000000' AND ADCDInfo.adcd not like '%000000000'" +
"AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 4) + "%') as a where a.adcd not in(select TownADCD as adcd from Audit where Audit.Year = " + _year + " and Audit.TownADCD like '" + _adcd.Substring(0, 4) + "%')";
}
RList0 = db.Select <AuditViewModel>(sql);
RList1.AddRange(RList0);
return(new BsTableDataSource <AuditViewModel>()
{
rows = RList1, total = RList1.Count + RList0.Count, other = RList0.Count.ToString() + "|" + lable1 + "|" + lable2 + "|" + lable3
});
}
#endregion
throw new Exception("权限异常");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}