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