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