public BsTableDataSource <AuditViewModel> GET(GetAuditApplication request) { return(AuditManager.GetAuditApplication(request)); }
/// <summary> /// 市县级获取审核申请 /// </summary> /// <param name="request"></param> /// <returns></returns> public BsTableDataSource <AuditViewModel> GetAuditApplication(GetAuditApplication 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 = getCount(1, 6, _adcd); //已审未批 var lable2 = getCount(2, 6, _adcd); //已批 var lable3 = getCount(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 = getCount(1, 4, _adcd); //已审未批 var lable2 = getCount(2, 4, _adcd); //已批 var lable3 = getCount(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); } }