public List <ASSETKKF_MODEL.Response.Asset.AuditCutList> getAuditDepList2(ASSETKKF_MODEL.Request.Asset.AuditCutReq d, SqlTransaction transac = null, string conStr = null) { DynamicParameters param = new DynamicParameters(); string cmd = " SELECT M.DEPMST as id,DEPNM ,(isnull(M.DEPNM,'') + ' : ' +M.DEPMST ) as Descriptions "; cmd += " FROM FT_ASAUDITCUTDATEMST_COMPANY(" + QuoteStr(d.Company) + ") M "; cmd += " WHERE M.Audit_NO is not null"; cmd += " and M.FLAG not in ('X')"; cmd += " AND exists (select * from FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.Company) + ") D where D.SQNO = M.SQNO)"; if (!String.IsNullOrEmpty(d.Company)) { var comp = ""; comp = "'" + d.Company.Replace(",", "','") + "'"; cmd += " and M.COMPANY in (" + comp + ") "; } if (d.isdept) { cmd += " and isnull(M.Audit_NO,'') like 'DU%' "; } else { cmd += " and isnull(M.Audit_NO,'') like 'AU%' "; } //cmd += " and isnull(M.Audit_NO,'') like 'AU%' "; if (!String.IsNullOrEmpty(d.YR)) { cmd += " and M.YR = " + QuoteStr(d.YR); } if (!String.IsNullOrEmpty(d.MN)) { cmd += " and M.MN = " + QuoteStr(d.MN); } if (!String.IsNullOrEmpty(d.YRMN)) { cmd += " and M.YRMN = " + QuoteStr(d.YRMN); } cmd += " group by M.Company,M.DEPMST,DEPNM"; var res = Query <ASSETKKF_MODEL.Response.Asset.AuditCutList>(cmd, param, conStr).ToList(); return(res); }
protected override void ExecuteChild(AuditCutReq dataReq, ResponseAPI dataRes) { var res = new AuditCutListRes(); try { DBMode = dataReq.DBMode; res._result.ServerAddr = ConnectionString(); res._result.DBMode = DBMode; var req = new ASSETKKF_MODEL.Request.Asset.AuditCutReq() { Company = dataReq.Company, DeptCode = dataReq.DeptCode, DeptLST = dataReq.DeptLST, Menu3 = dataReq.Menu3, Menu4 = dataReq.Menu4, }; List <ASSETKKF_MODEL.Response.Asset.AuditCutList> obj = new List <ASSETKKF_MODEL.Response.Asset.AuditCutList>(); if (!String.IsNullOrEmpty(dataReq.MODE) && dataReq.MODE.ToLower().Equals("rd")) { obj = ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAuditDepList2(dataReq, null, conString); } else { obj = ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAuditDepList(dataReq, null, conString); } if (obj == null) { res._result._code = "404"; res._result._message = "ไม่พบข้อมูล"; res._result._status = "Not Found"; } else { res._result._code = "200"; res._result._message = ""; res._result._status = "OK"; } res.auditCutNoLst = obj; } catch (SqlException ex) { res._result._code = "500 "; res._result._message = ex.Message; res._result._status = "Execute exception Error"; } catch (InvalidOperationException ex) { res._result._code = "500 "; res._result._message = ex.Message; res._result._status = "Connection Exception Error"; } catch (Exception ex) { res._result._code = "500 "; res._result._message = ex.Message; res._result._status = "Internal Server Error"; } dataRes.data = res; }
public List <ASSETKKF_MODEL.Response.Asset.AuditCutList> getAuditDepList(ASSETKKF_MODEL.Request.Asset.AuditCutReq d, SqlTransaction transac = null, string conStr = null) { DynamicParameters param = new DynamicParameters(); string cmd = " SELECT M.Company,max(M.SQNO) as SQNO,min(isnull(DEPCODEOL,'')) as DEPCODEOL,max(isnull(M.Audit_NO,'')) as Audit_NO"; cmd += " ,M.DEPMST as id,DEPNM,max(M.YR) as YR,max(M.MN) as MN,max(M.YRMN) as YRMN,max(D.DEPCODE) as DEPCODE,max(D.CUTDT) as CUTDT,max(D.STNAME) as STNAME"; cmd += " ,(isnull(M.DEPNM,'') + ' : ' +M.DEPMST ) as Descriptions "; //cmd += " from FT_ASAUDITCUTDATE() D, FT_ASAUDITCUTDATEMST() M "; cmd += " FROM FT_ASAUDITCUTDATEMST_COMPANY(" + QuoteStr(d.Company) + ") M ,FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(d.Company) + ") D"; cmd += " where D.SQNO = M.SQNO "; cmd += " and D.Company = M.Company "; cmd += " and M.Audit_NO is not null"; if (String.IsNullOrEmpty(d.MODE)) { cmd += " and M.FLAG not in ('X','C')"; } else { cmd += " and M.FLAG not in ('X')"; } if (!String.IsNullOrEmpty(d.Company)) { var comp = ""; comp = "'" + d.Company.Replace(",", "','") + "'"; cmd += " and M.COMPANY in (" + comp + ") "; } if ((!d.Menu3 && !d.Menu4) && ((!String.IsNullOrEmpty(d.DeptCode)) || d.DeptLST != null)) { cmd += " and ("; if (!String.IsNullOrEmpty(d.DeptCode)) { cmd += " DEPCODEOL = '" + d.DeptCode + "'"; } if (d.DeptLST != null && d.DeptLST.Length > 0) { var arrDept = d.DeptLST.Split(","); foreach (string s in arrDept) { cmd += " or DEPCODEOL like ' " + s + "%'"; } } cmd += " )"; } if (d.isdept) { cmd += " and isnull(M.Audit_NO,'') like 'DU%' "; } else { cmd += " and isnull(M.Audit_NO,'') like 'AU%' "; } //cmd += " and isnull(M.Audit_NO,'') like 'AU%' "; if (!String.IsNullOrEmpty(d.YR)) { cmd += " and M.YR = " + QuoteStr(d.YR); } if (!String.IsNullOrEmpty(d.MN)) { cmd += " and M.MN = " + QuoteStr(d.MN); } if (!String.IsNullOrEmpty(d.YRMN)) { cmd += " and M.YRMN = " + QuoteStr(d.YRMN); } cmd += " group by M.Company,M.DEPMST,DEPNM"; var res = Query <ASSETKKF_MODEL.Response.Asset.AuditCutList>(cmd, param, conStr).ToList(); return(res); }
public List <ASSETKKF_MODEL.Response.Asset.AuditCutList> GetAuditCutLists(ASSETKKF_MODEL.Request.Asset.AuditCutReq d, SqlTransaction transac = null, string conStr = null) { var obj = getAuditCutNoList(d, transac, conStr); return(obj); }