public BsTableDataSource <AuditCountyViewModel> GetAuditApplicationCounty(GetAuditApplicationCounty request)
{
var _year = System.DateTime.Now.Year;
try
{
using (var db = DbFactory.Open())
{
var _adcd = string.IsNullOrEmpty(request.adcd) ? adcd : request.adcd;
List <AuditCountyViewModel> RList1 = null;
List <AuditCountyViewModel> RList0 = null;
//统计
//var leng = string.IsNullOrEmpty(request.adcd)
//待审批
var lable1 = getCountCounty(1, 4, _adcd);
//已审批
var lable2 = getCountCounty(3, 4, _adcd);
//1
var builder = db.From <AuditCounty>();
builder.LeftJoin <AuditCounty, ADCDInfo>((x, y) => x.CountyADCD == y.adcd);
builder.Where(x => x.CountyADCD.StartsWith(_adcd.Substring(0, 4)));
builder.Select(" ADCDInfo.adnm,AuditCounty.*");
RList1 = db.Select <AuditCountyViewModel>(builder);
//0
var sql = "";
if (RList1.Count == 0)
{
sql = "SELECT ADCDInfo.adcd,ADCDInfo.adnm FROM ADCDInfo WHERE 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 AuditCounty,ADCDInfo " +
// "WHERE ADCDInfo.adcd != AuditCounty.CountyADCD AND SUBSTRING(ADCDInfo.adcd, 7, 9) = '000000000' AND AuditCounty.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, 7, 9) = '000000000'" +
"AND ADCDInfo.adcd != '" + _adcd + "' AND ADCDInfo.adcd like '%" + _adcd.Substring(0, 4) + "%') as a where a.adcd not in(select CountyADCD as adcd from AuditCounty where AuditCounty.Year = " + _year + " and AuditCounty.CountyADCD like '" + _adcd.Substring(0, 4) + "%')";
}
RList0 = db.Select <AuditCountyViewModel>(sql);
RList1.AddRange(RList0);
return(new BsTableDataSource <AuditCountyViewModel>()
{
rows = RList1, total = RList1.Count + RList0.Count, other = RList0.Count.ToString() + "|" + lable1 + "|" + lable2
});
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public BsTableDataSource <AuditCountyViewModel> GET(GetAuditApplicationCounty request)
{
return(AuditManager.GetAuditApplicationCounty(request));
}
