public List <ASAUDITPOSTTRN> getPOSTTRNDep(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT P.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.INPID) as INPNAME ,ISNULL(D.FLAG_ACCEPT,'') as FLAG_ACCEPT";
sql += " ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.stid) as stidname";
sql += " from [FT_ASAUDITPOSTTRN_COMPANY](" + QuoteStr(d.COMPANY) + ") as P";
sql += " left outer join [FT_ASAUDITCUTDATEMST_COMPANY](" + QuoteStr(d.COMPANY) + ") as D on D.sqno = P.sqno";
sql += " where P.SQNO = " + QuoteStr(d.SQNO);
sql += " and P.COMPANY = " + QuoteStr(d.COMPANY);
sql += " AND isnull(P.STY,'') = '' ";
sql += " AND isnull(P.SNDST,'') = 'Y' ";
sql += " AND isnull(P.SNDACC,'') = '' ";
sql += " and ISNULL(D.FLAG_ACCEPT,'') in ('','0') ";
if (!String.IsNullOrEmpty(d.filter))
{
switch (d.filter)
{
case "0":
sql += " and isnull(PCOD,'') <> '' ";
break;
case "1":
sql += " and isnull(PCOD,'') = '' ";
break;
}
}
var res = Query <ASAUDITPOSTTRN>(sql, param, conStr).ToList();
return(res);
}
public int addAUDITPOSTMST(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " EXEC [SP_AUDITCUTPOST] ";
sql += " @SQNO = '" + d.SQNO + "'";
sql += " ,@COMPANY = '" + d.COMPANY + "'";
sql += " ,@DEPCODEOL = '" + d.DEPCODEOL + "'";
sql += " ,@LEADERCODE = '" + d.LEADERCODE + "'";
sql += " ,@LEADERNAME = '" + d.LEADERNAME + "'";
sql += " ,@AREACODE = '" + d.AREACODE + "'";
sql += " ,@AREANAME = '" + d.AREANAME + "'";
sql += " ,@IMGPATH = '" + d.IMGPATH + "'";
sql += " ,@USERID = '" + d.UCODE + "'";
sql += " ,@AREA = '" + d.AREA + "'";
sql += " ,@OFFICECODE = '" + d.OFFICECODE + "'";
sql += " ,@TYPECODE = '" + d.TYPECODE + "'";
sql += " ,@GASTCODE = '" + d.GASTCODE + "'";
var res = ExecuteNonQuery(sql, param, conStr);
return(res);
}
private TrackOfflineRes SetAuditPostMST(TrackOfflineReq dataReq, TrackOfflineRes res, string conStr = null)
{
try
{
List <ASAUDITPOSTMST> lst = new List <ASAUDITPOSTMST>();
lst = GetCutPostMST(dataReq, conStr);
if (lst != null && lst.Count == 0)
{
var req = new AuditPostReq
{
COMPANY = dataReq.company,
SQNO = dataReq.sqno,
INPID = dataReq.ucode
};
InsertAuditPostMST(req, conStr);
}
}
catch (Exception ex)
{
res._result._code = "500 ";
res._result._message = ex.Message;
res._result._status = "Internal Server Error";
}
return(res);
}
public List <ASAUDITPOSTTRN> getPOSTTRNComp(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT P.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.INPID) as INPNAME ";
sql += " ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.stid) as stidname";
sql += " from [FT_ASAUDITPOSTTRN_COMPANY](" + QuoteStr(d.COMPANY) + ") as P";
sql += " where P.SQNO = " + QuoteStr(d.SQNO);
sql += " and P.COMPANY = " + QuoteStr(d.COMPANY);
sql += " and SNDACCDT IS NULL";
if (!String.IsNullOrEmpty(d.depy))
{
sql += " and isnull(STY,'') = " + QuoteStr(d.depy);
}
if (!String.IsNullOrEmpty(d.filter))
{
switch (d.filter)
{
case "0":
sql += " and isnull(PCOD,'') <> '' ";
break;
case "1":
sql += " and isnull(PCOD,'') = '' ";
break;
}
}
var res = Query <ASAUDITPOSTTRN>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMSTTODEP> getDataToClear(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT B.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.INPID) as INPNAME ,ISNULL(D.FLAG_ACCEPT,'') as FLAG_ACCEPT";
sql += " ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.stid) as stidname";
sql += " ,(SELECT SACC FROM [dbo].[FT_ASSTProblem] () where COMPANY = B.COMPANY and PCODE = B.PCODE) as SACC";
sql += " FROM FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") B";
sql += " left outer join [FT_ASAUDITCUTDATEMST_COMPANY](" + QuoteStr(d.COMPANY) + ") as D on D.sqno = B.sqno";
sql += " where B.SQNO = " + QuoteStr(d.SQNO);
sql += " and B.COMPANY = " + QuoteStr(d.COMPANY);
sql += " and isnull(B.STY,'') = '' ";
sql += " and ISNULL(D.FLAG_ACCEPT,'') in ('','0') ";
if (!String.IsNullOrEmpty(d.filter))
{
switch (d.filter)
{
case "0":
sql += " and isnull(PCOD,'') <> '' ";
break;
case "1":
sql += " and isnull(PCOD,'') = '' ";
break;
}
}
if (!String.IsNullOrEmpty(d.ASSETNO))
{
sql += " and B.ASSETNO = " + QuoteStr(d.ASSETNO);
}
if (String.IsNullOrEmpty(d.orderby) || d.orderby.Equals("1"))
{
sql += " order by ASSETNO,OFFICECODE ";
}
if (d.orderby != null && d.orderby.Equals("2"))
{
sql += " order by OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("3"))
{
sql += " order by DEPCODEOL,OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("4"))
{
sql += " order by POSITCODE,OFFICECODE,ASSETNO ";
}
var res = Query <ASAUDITPOSTMSTTODEP>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMSTTOTEMP> getAuditTmpFIXEDASSET(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select * from FT_ASAUDITPOSTMSTTOTEMP_ASFIXEDASSET(" + QuoteStr(d.COMPANY) + "," + QuoteStr(d.SQNO) + ") as a ";
var res = Query <ASAUDITPOSTMSTTOTEMP>(sql, param, conStr).ToList();
return(res);
}
public List <AuditTmpCompareTRN> getAuditTmpComparetoTRN(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select * from FT_AUDITTMPCOMPARETOTRN(" + QuoteStr(d.COMPANY) + "," + QuoteStr(d.SQNO) + ") as a ";
var res = Query <AuditTmpCompareTRN>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMST> GetCutPostMST(TrackOfflineReq dataReq, string conStr = null)
{
var reqMst = new AuditPostReq
{
COMPANY = dataReq.company,
SQNO = dataReq.sqno,
INPID = dataReq.ucode
};
return(Task.Run(() => ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAUDITPOSTMST(reqMst, null, null, conStr)).Result);
}
public List <ASAUDITPOSTMSTTODEP> getAuditAssetNo(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select * from FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") as a ";
sql += " where a.SQNO = '" + d.SQNO + "'";
sql += " and a.COMPANY = '" + d.COMPANY + "'";
sql += " and a.ASSETNO = '" + d.ASSETNO + "'";
//sql += " and a.INPID = '" + d.UCODE + "'";
var res = Query <ASAUDITPOSTMSTTODEP>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTTRN> getPOSTTRN(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT P.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.INPID) as INPNAME ";
sql += " ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.stid) as stidname";
sql += " from [FT_ASAUDITPOSTTRN_COMPANY](" + QuoteStr(d.COMPANY) + ") as P";
sql += " where P.SQNO = " + QuoteStr(d.SQNO);
sql += " and P.COMPANY = " + QuoteStr(d.COMPANY);
var res = Query <ASAUDITPOSTTRN>(sql, param, conStr).ToList();
return(res);
}
public List <SummaryResult> GetSummaryResult(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = @" SELECT PCODE,PNAME,DEPCODE,DEPCODEOL,MAX(STNAME) AS STNAME,COUNT(PCODE) AS QTY FROM FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") B ";
sql += " WHERE SQNO = " + QuoteStr(d.SQNO);
sql += " and COMPANY =" + QuoteStr(d.COMPANY);
sql += " and PCODE <> '' AND SNDST = 'Y' AND SNDACCDT IS NULL ";
sql += " GROUP BY MN,YR,PCODE,PNAME,DEPCODE,DEPCODEOL ";
sql += " order BY DEPCODE,DEPCODEOL, PCODE,PNAME ";
var res = Query <SummaryResult>(sql, param, conStr).ToList();
return(res);
}
public int saveAUDITCUTDATEMST(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " EXEC [dbo].[SP_AUDITCUTDATEMST] ";
sql += " @SQNO = '" + d.SQNO + "'";
sql += " ,@COMPANY = '" + d.COMPANY + "'";
sql += " ,@USERID = '" + d.UCODE + "'";
sql += " ,@MODE = '" + d.mode + "'";
sql += " ,@FLAG = '" + d.FLAG + "'";
var res = ExecuteNonQuery(sql, param, conStr);
return(res);
}
public List <ASAUDITPOSTMSTTOTEMP> getAuditAssetNo(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select * from FT_ASAUDITPOSTMSTTOTEMP_COMPANY(" + QuoteStr(d.COMPANY) + ") as a ";
sql += " left outer join [FT_ASAUDITPOSTMST_PHONE_COMPANY] (" + QuoteStr(d.COMPANY) + ") as b";
sql += " on b.SQNO = a.SQNO and a.COMPANY = b.COMPANY and b.ASSETNO = a.ASSETNO and (a.INPID = b.INPID or b.inpdt is not null)";
sql += " where a.SQNO = '" + d.SQNO + "'";
sql += " and a.COMPANY = '" + d.COMPANY + "'";
sql += " and a.ASSETNO = '" + d.ASSETNO + "'";
//sql += " and a.INPID = '" + d.UCODE + "'";
var res = Query <ASAUDITPOSTMSTTOTEMP>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTTRN> getAUDITPOSTTRN(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select * ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= a.INPID) as INPNAME";
sql += " from [FT_ASAUDITPOSTTRN_COMPANY] (" + QuoteStr(d.COMPANY) + ") as a ";
sql += " left outer join [FT_ASAUDITPOSTTRN_PHONE] () as b";
sql += " on b.SQNO = a.SQNO and a.COMPANY = b.COMPANY and b.ASSETNO = a.ASSETNO and a.INPID = b.INPID";
sql += " where a.SQNO = '" + d.SQNO + "'";
sql += " and a.COMPANY = '" + d.COMPANY + "'";
sql += " and a.INPID = '" + d.UCODE + "'";
if (!String.IsNullOrEmpty(d.DEPCODEOL))
{
sql += " and a.DEPCODEOL = '" + d.DEPCODEOL + "'";
}
if (!String.IsNullOrEmpty(d.AREACODE))
{
sql += " and a.POSITCODE = '" + d.AREACODE + "'";
}
if (!String.IsNullOrEmpty(d.YEAR))
{
sql += " and YR = '" + d.YEAR + "'";
}
if (!String.IsNullOrEmpty(d.MN))
{
sql += " and MN = '" + d.MN + "'";
}
//if (!String.IsNullOrEmpty(d.DEPMST))
//{
// sql += "and DEPCODE in (SELECT [DEPCODE] ";
// sql += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(d.COMPANY) + ") ";
// sql += " where DEPMST = '" + d.DEPMST + "'";
// sql += " and company = '" + d.COMPANY + "'";
// sql += " and SQNO = '" + d.SQNO + "'";
// sql += " group by[DEPCODE])";
//}
sql += " order by (case when a.INPID = '" + d.UCODE + "' then 1 else 0 end) desc";
var res = Query <ASAUDITPOSTTRN>(sql, param, conStr).ToList();
return(res);
}
public string getAttachedFile(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
string attachedFile = "";
DynamicParameters param = new DynamicParameters();
sql = "select * FROM FT_ASAUDITPOSTMSTTOTEMP_COMPANY(" + QuoteStr(d.COMPANY) + ") B";
sql += " where B.SQNO = " + QuoteStr(d.SQNO);
var res = Query <ASAUDITPOSTMSTTODEP>(sql, param, conStr).ToList();
if (res != null && res.Count > 0)
{
var obj = res.Where(x => !String.IsNullOrEmpty(x.FILEPATH)).FirstOrDefault();
attachedFile = obj != null ? obj.FILEPATH : attachedFile;
}
return(attachedFile);
}
public List <ASAUDITPOSTMST> getNoDuplicateAll(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " Select * from [FT_ASAUDITPOSTMST_COMPANY] (" + QuoteStr(d.COMPANY) + ") as P";
sql += " where P.SQNO = " + QuoteStr(d.SQNO);
sql += " and P.COMPANY = " + QuoteStr(d.COMPANY);
sql += " AND P.PCODE <> '' ";
sql += " AND P.ASSETNO IN ( SELECT X.ASSETNO FROM [FT_ASAUDITPOSTMST_COMPANY] (" + QuoteStr(d.COMPANY) + ") X ";
sql += " where P.SQNO = " + QuoteStr(d.SQNO);
sql += " and P.COMPANY = " + QuoteStr(d.COMPANY);
sql += " and X.PCODE <> '' GROUP BY X.ASSETNO HAVING COUNT(X.ASSETNO) = 1 ) ";
var res = Query <ASAUDITPOSTMST>(sql, param, conStr).ToList();
return(res);
}
public List <AuditComp> getAuditAcc(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " SELECT SQNO,COUNT(ASSETNO) AS QTY_TOTAL FROM ( ";
sql += " SELECT SQNO,ASSETNO FROM FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") B, FT_ASSTProblem() A ";
sql += " WHERE A.PCODE = B.PCODE AND A.SACC = 'Y' AND A.company = B.company";
sql += " UNION ";
sql += " SELECT SQNO,ASSETNO FROM FT_ASAUDITPOSTTRN_COMPANY(" + QuoteStr(d.COMPANY) + ") B WHERE B.SNDACC = 'Y' ";
sql += " ) AS X ";
sql += " where SQNO = " + QuoteStr(d.SQNO);
sql += " GROUP BY SQNO ";
//sql += " HAVING COUNT(ASSETNO) = 0 ";
var res = Query <AuditComp>(sql, param, conStr).ToList();
return(res);
}
public SummaryAudit GetSummaryAudit(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = @"select *
, (Case when QTY_ASSET > 0 then CAST(((CAST(QTY_AUDIT as DECIMAL(9,2)) /CAST(QTY_ASSET as DECIMAL(9,2)))*100) as DECIMAL(9,2)) else 0 end ) as PROGRESS
from (
SELECT COUNT(OFFICECODE) AS QTY_HUMAN,SUM(SASSET1) AS QTY_ASSET,SUM(SAUDIT1) AS QTY_AUDIT
FROM ( SELECT MN,YR,OFFICECODE,COUNT(ASSETNO) AS SASSET1,SUM(CASE WHEN isnull(PCODE,'') = '' THEN 0 ELSE 1 END) AS SAUDIT1
FROM FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") B ";
sql += " WHERE SQNO = " + QuoteStr(d.SQNO);
sql += " and COMPANY =" + QuoteStr(d.COMPANY);
sql += " and SNDST = 'Y' AND SNDACCDT IS NULL";
sql += " GROUP BY MN,YR,OFFICECODE ) AS X GROUP BY MN,YR ) as Z ";
var res = Query <SummaryAudit>(sql, param, conStr).FirstOrDefault();
return(res);
}
public List <ASAUDITPOSTMSTTODEP> getDataToConfirm(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT B.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.INPID) as INPNAME ";
sql += " ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.stid) as stidname";
sql += " FROM FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") B";
sql += " where B.SQNO = " + QuoteStr(d.SQNO);
sql += " and B.COMPANY = " + QuoteStr(d.COMPANY);
sql += " and isnull(STY,'') = '' ";
sql += " and ISNULL(COMY, '') = case when ISNULL(STCY , '') <> '' then 'Y' else ISNULL(COMY, '') end";
if (!String.IsNullOrEmpty(d.ASSETNO))
{
sql += " where B.ASSETNO = " + QuoteStr(d.ASSETNO);
}
if (String.IsNullOrEmpty(d.orderby) || d.orderby.Equals("1"))
{
sql += " order by ASSETNO,OFFICECODE ";
}
if (d.orderby != null && d.orderby.Equals("2"))
{
sql += " order by OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("3"))
{
sql += " order by DEPCODEOL,OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("4"))
{
sql += " order by POSITCODE,OFFICECODE,ASSETNO ";
}
var res = Query <ASAUDITPOSTMSTTODEP>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMST> getAUDITPOSTMST(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select P.*,PM.PFlag ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.INPID) as INPNAME";
sql += " from [FT_ASAUDITPOSTMST_COMPANY] (" + QuoteStr(d.COMPANY) + ") as P ";
sql += " left outer join [dbo].[FT_ASAUDITPOSTMST_PHONE_COMPANY] (" + QuoteStr(d.COMPANY) + ") AS PM ";
sql += " on PM.SQNO = P.SQNO and PM.Company = P.Company and PM.ASSETNO = P.ASSETNO and PM.INPID = P.INPID";
sql += " where P.SQNO = '" + d.SQNO + "'";
sql += " and P.COMPANY = '" + d.COMPANY + "'";
sql += " and P.INPID = '" + d.UCODE + "'";
if (!String.IsNullOrEmpty(d.DEPCODEOL))
{
sql += " and P.DEPCODEOL = '" + d.DEPCODEOL + "'";
}
if (!String.IsNullOrEmpty(d.AREACODE))
{
sql += " and P.POSITCODE = '" + d.AREACODE + "'";
}
if (!String.IsNullOrEmpty(d.ASSETNO))
{
sql += " and P.ASSETNO = '" + d.ASSETNO + "'";
}
if (flag == "")
{
sql += " and isnull(PCODE,'') = '' ";
}
if (!String.IsNullOrEmpty(flag))
{
sql += " and isnull(PCODE,'') <> '' ";
}
if (!String.IsNullOrEmpty(d.YEAR))
{
sql += " and YR = '" + d.YEAR + "'";
}
if (!String.IsNullOrEmpty(d.MN))
{
sql += " and MN = '" + d.MN + "'";
}
//if (!String.IsNullOrEmpty(d.DEPMST))
//{
// sql += " and DEPCODEOL in (SELECT [DEPCODEOL] ";
// sql += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(d.COMPANY) + ") ";
// sql += " where DEPMST = '" + d.DEPMST + "'";
// sql += " and company = '" + d.COMPANY + "'";
// sql += " and SQNO = '" + d.SQNO + "'";
// if (!String.IsNullOrEmpty(d.YEAR))
// {
// sql += " and YR = " + QuoteStr(d.YEAR);
// }
// if (!String.IsNullOrEmpty(d.MN))
// {
// sql += " and MN = " + QuoteStr(d.MN);
// }
// if (!String.IsNullOrEmpty(d.YRMN))
// {
// sql += " and YRMN = " + QuoteStr(d.YRMN);
// }
// sql += " and sqno = p.sqno";
// sql += " group by[DEPCODEOL])";
//}
//if (!String.IsNullOrEmpty(d.cutdt))
//{
// sql += " and DATEADD(dd, 0, DATEDIFF(dd, 0, cutdt)) = DATEADD(dd, 0, DATEDIFF(dd, 0, " + QuoteStr(d.cutdt) + "))";
//}
if (!String.IsNullOrEmpty(d.OFFICECODE))
{
sql += " and OFFICECODE = '" + d.OFFICECODE + "'";
}
if (!String.IsNullOrEmpty(d.TYPECODE))
{
sql += " and TYPECODE = '" + d.TYPECODE + "'";
}
if (!String.IsNullOrEmpty(d.GASTCODE))
{
sql += " and GASTCODE = '" + d.GASTCODE + "'";
}
sql += " and exists (select * from FT_ASAUDITCUTDATEMST_COMPANY(P.company) M where m.SQNO = p.sqno";
if (d.isdept)
{
sql += " and isnull(Audit_NO,'') like 'DU%' ";
}
else
{
sql += " and isnull(Audit_NO,'') like 'AU%' ";
}
sql += " )";
sql += " order by (case when P.INPID = '" + d.UCODE + "' then 1 else 0 end) desc, P.INPDT desc";
var res = Query <ASAUDITPOSTMST>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMST> getPOSTMSTDuplicate(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT P.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= P.INPID) as INPNAME from FT_ASAUDITPOSTMST_COMPANY(" + QuoteStr(d.COMPANY) + ") as P";
sql += " left outer join FT_ASAUDITCUTDATEMST_COMPANY(" + QuoteStr(d.COMPANY) + ") M on M.SQNO = P.SQNO and M.COMPANY = P.COMPANY";
sql += " where P.SQNO = " + QuoteStr(d.SQNO);
sql += " and P.COMPANY = " + QuoteStr(d.COMPANY);
sql += " and M.FLAG not in ('X','C')";
sql += " AND PCODE <> '' AND P.ASSETNO IN ( SELECT X.ASSETNO FROM FT_ASAUDITPOSTMST_COMPANY(" + QuoteStr(d.COMPANY) + ") X WHERE X.PCODE <> '' ";
sql += " AND X.SQNO = " + QuoteStr(d.SQNO);
sql += " and x.COMPANY = " + QuoteStr(d.COMPANY);
sql += " GROUP BY X.ASSETNO HAVING COUNT(X.ASSETNO) >1 )";
if (!String.IsNullOrEmpty(d.DEPCODEOL))
{
sql += " and DEPCODEOL = '" + d.DEPCODEOL + "'";
}
if (!String.IsNullOrEmpty(d.AREACODE))
{
sql += " and POSITCODE = '" + d.AREACODE + "'";
}
if (!String.IsNullOrEmpty(d.ASSETNO))
{
sql += " and ASSETNO = '" + d.ASSETNO + "'";
}
if (!String.IsNullOrEmpty(d.YEAR))
{
sql += " and YR = '" + d.YEAR + "'";
}
if (!String.IsNullOrEmpty(d.MN))
{
sql += " and MN = '" + d.MN + "'";
}
if (!String.IsNullOrEmpty(d.DEPMST))
{
sql += " and DEPCODEOL in (SELECT [DEPCODEOL] ";
sql += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(d.COMPANY) + ") ";
sql += " where DEPMST = '" + d.DEPMST + "'";
sql += " and company = '" + d.COMPANY + "'";
if (!String.IsNullOrEmpty(d.YEAR))
{
sql += " and YR = " + QuoteStr(d.YEAR);
}
if (!String.IsNullOrEmpty(d.MN))
{
sql += " and MN = " + QuoteStr(d.MN);
}
if (!String.IsNullOrEmpty(d.YRMN))
{
sql += " and YRMN = " + QuoteStr(d.YRMN);
}
sql += " group by[DEPCODEOL])";
}
if (!String.IsNullOrEmpty(d.cutdt))
{
sql += " and DATEADD(dd, 0, DATEDIFF(dd, 0, cutdt)) = DATEADD(dd, 0, DATEDIFF(dd, 0, " + QuoteStr(d.cutdt) + "))";
}
if (!String.IsNullOrEmpty(d.OFFICECODE))
{
sql += " and OFFICECODE = '" + d.OFFICECODE + "'";
}
if (!String.IsNullOrEmpty(d.TYPECODE))
{
sql += " and TYPECODE = '" + d.TYPECODE + "'";
}
if (!String.IsNullOrEmpty(d.GASTCODE))
{
sql += " and GASTCODE = '" + d.GASTCODE + "'";
}
if (String.IsNullOrEmpty(d.orderby) || d.orderby.Equals("1"))
{
sql += " order by ASSETNO,OFFICECODE ";
}
if (d.orderby != null && d.orderby.Equals("2"))
{
sql += " order by OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("3"))
{
sql += " order by DEPCODEOL,OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("4"))
{
sql += " order by POSITCODE,OFFICECODE,ASSETNO ";
}
var res = Query <ASAUDITPOSTMST>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMSTTOTEMP> getDataToSendDep(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT B.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.INPID) as INPNAME ";
sql += " ,(SELECT SACC FROM [dbo].[FT_ASSTProblem] () where COMPANY = B.COMPANY and PCODE = B.PCODE) as SACC";
sql += " FROM FT_ASAUDITPOSTMSTTOTEMP_COMPANY(" + QuoteStr(d.COMPANY) + ") B";
sql += " where B.SQNO = " + QuoteStr(d.SQNO);
sql += " and B.COMPANY = " + QuoteStr(d.COMPANY);
/* if (!String.IsNullOrEmpty(d.DEPCODEOL))
* {
* sql += " and DEPCODEOL = '" + d.DEPCODEOL + "'";
* }
*
* if (!String.IsNullOrEmpty(d.AREACODE))
* {
* sql += " and POSITCODE = '" + d.AREACODE + "'";
* }
*
* if (!String.IsNullOrEmpty(d.ASSETNO))
* {
* sql += " and ASSETNO = '" + d.ASSETNO + "'";
* }
*
* if (!String.IsNullOrEmpty(d.YEAR))
* {
* sql += " and YR = '" + d.YEAR + "'";
* }
*
* if (!String.IsNullOrEmpty(d.MN))
* {
* sql += " and MN = '" + d.MN + "'";
* }
* if (!String.IsNullOrEmpty(d.DEPMST))
* {
* sql += " and DEPCODEOL in (SELECT [DEPCODEOL] ";
* sql += " FROM FT_ASAUDITCUTDATE() ";
* sql += " where DEPMST = '" + d.DEPMST + "'";
* sql += " and company = '" + d.COMPANY + "'";
* sql += " group by[DEPCODEOL])";
* }
*
* if (!String.IsNullOrEmpty(d.cutdt))
* {
* sql += " and DATEADD(dd, 0, DATEDIFF(dd, 0, cutdt)) = DATEADD(dd, 0, DATEDIFF(dd, 0, " + QuoteStr(d.cutdt) + "))";
* }
*
* if (!String.IsNullOrEmpty(d.OFFICECODE))
* {
* sql += " and OFFICECODE = '" + d.OFFICECODE + "'";
* }
*
* if (!String.IsNullOrEmpty(d.TYPECODE))
* {
* sql += " and TYPECODE = '" + d.TYPECODE + "'";
* }
*
* if (!String.IsNullOrEmpty(d.GASTCODE))
* {
* sql += " and GASTCODE = '" + d.GASTCODE + "'";
* }*/
if (String.IsNullOrEmpty(d.orderby) || d.orderby.Equals("1"))
{
sql += " order by ASSETNO,OFFICECODE ";
}
if (d.orderby != null && d.orderby.Equals("2"))
{
sql += " order by OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("3"))
{
sql += " order by DEPCODEOL,OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("4"))
{
sql += " order by POSITCODE,OFFICECODE,ASSETNO ";
}
var res = Query <ASAUDITPOSTMSTTOTEMP>(sql, param, conStr).ToList();
return(res);
}
public Task <int> InsertAuditPostMST(AuditPostReq dataReq, string conStr = null)
{
return(Task.Run(() => ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().addAUDITPOSTMST(dataReq, null, conStr)));
}
public List <ASSETKKF_MODEL.Response.Asset.LeaderList> getCentralOfficerLst(AuditCutInfoReq dataReq, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
//var lst = getAuditCutNoList(new ASSETKKF_MODEL.Request.Asset.AuditCutReq() { Company = dataReq.Company , SQNO = dataReq.sqno });
var depcodeol = "";
/* if (lst != null && lst.Count > 0)
* {
* var obj = lst.FirstOrDefault();
* depcodeol = obj != null ? obj.DEPCODEOL : null;
* }
* else
* {
* depcodeol = String.IsNullOrEmpty(dataReq.DEPCODEOL) ? dataReq.DeptCode : dataReq.DEPCODEOL;
* }*/
if (!String.IsNullOrEmpty(dataReq.DEPCODEOL))
{
depcodeol = dataReq.DEPCODEOL;
}
else if (!String.IsNullOrEmpty(dataReq.DeptCode))
{
//AuditCutReq req = new AuditCutReq()
//{
// Company = dataReq.Company,
// SQNO = dataReq.sqno,
// Menu3 = dataReq.Menu3,
// Menu4 = dataReq.Menu4,
// DEPMST = dataReq.DEPMST,
// DeptCode = dataReq.DeptCode,
// DeptLST = dataReq.DeptLST
//};
//var lst = getAuditCutNoList(req);
AuditPostReq req = new AuditPostReq()
{
COMPANY = dataReq.Company,
SQNO = dataReq.sqno,
DEPMST = dataReq.DEPMST,
};
var lst = getAUDITCUTDATE(req);
if (lst != null && lst.Count > 0)
{
var obj = lst.FirstOrDefault();
depcodeol = obj != null ? obj.DEPCODEOL : null;
}
}
param.Add("@COMPANY", dataReq.Company);
param.Add("@DEPCODEOL", depcodeol);
// sql = "select OFFICECODE as id,OFFICECODE + ' : ' + OFNAME as descriptions,OFNAME as name from FT_CentralOfficer(@COMPANY,@DEPCODEOL)";
sql = "select OFFICECODE as id,OFFICECODE + ' : ' + OFNAME as descriptions,OFNAME as name from FT_CentralOfficer(";
sql += QuoteStr(dataReq.Company) + "," + QuoteStr(depcodeol) + ")";
var res = Query <ASSETKKF_MODEL.Response.Asset.LeaderList>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITPOSTMSTTODEP> getDataToACCEdit(AuditPostReq d, string flag = null, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "SELECT B.*,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.INPID) as INPNAME ";
sql += " ,(select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= B.stid) as stidname";
sql += " FROM FT_ASAUDITPOSTMSTTODEP_COMPANY(" + QuoteStr(d.COMPANY) + ") B";
sql += " where B.SQNO = " + QuoteStr(d.SQNO);
sql += " and B.COMPANY = " + QuoteStr(d.COMPANY);
sql += " and SNDACCDT IS NULL";
sql += " AND B.PCODE IN (SELECT A.PCODE FROM FT_ASSTProblem() A WHERE A.SACC = 'Y' and COMPANY = " + QuoteStr(d.COMPANY) + ") ";
if (!String.IsNullOrEmpty(d.depy))
{
sql += " and isnull(STY,'') = " + QuoteStr(d.depy);
}
if (!String.IsNullOrEmpty(d.filter))
{
switch (d.filter)
{
case "0":
sql += " and isnull(PCOD,'') <> '' ";
break;
case "1":
sql += " and isnull(PCOD,'') = '' ";
break;
}
}
if (!String.IsNullOrEmpty(d.filter))
{
sql += " and isnull(STY,'') = " + QuoteStr(d.filter);
}
if (!String.IsNullOrEmpty(d.ASSETNO))
{
sql += " and B.ASSETNO = " + QuoteStr(d.ASSETNO);
}
if (String.IsNullOrEmpty(d.orderby) || d.orderby.Equals("1"))
{
sql += " order by ASSETNO,OFFICECODE ";
}
if (d.orderby != null && d.orderby.Equals("2"))
{
sql += " order by OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("3"))
{
sql += " order by DEPCODEOL,OFFICECODE,ASSETNO ";
}
if (d.orderby != null && d.orderby.Equals("4"))
{
sql += " order by POSITCODE,OFFICECODE,ASSETNO ";
}
var res = Query <ASAUDITPOSTMSTTODEP>(sql, param, conStr).ToList();
return(res);
}
public List <ASAUDITCUTDATE> getAUDITCUTDATE(AuditPostReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " select D.* FROM FT_ASAUDITCUTDATEMST_COMPANY(" + QuoteStr(d.COMPANY) + ") M ,FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(d.COMPANY) + ") D ";
sql += " where D.SQNO = M.SQNO ";
sql += " and D.Company = M.Company ";
sql += " and M.SQNO = '" + d.SQNO + "'";
sql += " and M.COMPANY = '" + d.COMPANY + "'";
sql += " and M.Audit_NO is not null";
if (!String.IsNullOrEmpty(d.DEPCODEOL))
{
sql += " and DEPCODEOL = '" + d.DEPCODEOL + "'";
}
if (!String.IsNullOrEmpty(d.AREACODE))
{
sql += " and POSITCODE = '" + d.AREACODE + "'";
}
if (!String.IsNullOrEmpty(d.YEAR))
{
sql += " and M.YR = '" + d.YEAR + "'";
}
if (!String.IsNullOrEmpty(d.MN))
{
sql += " and M.MN = '" + d.MN + "'";
}
//if (!String.IsNullOrEmpty(d.DEPMST))
//{
// sql += "and DEPCODEOL in (SELECT [DEPCODEOL] ";
// sql += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(d.COMPANY) + ") ";
// sql += " where DEPMST = '" + d.DEPMST + "'";
// sql += " and company = '" + d.COMPANY + "'";
// sql += " and SQNO = '" + d.SQNO + "'";
// if (!String.IsNullOrEmpty(d.YEAR))
// {
// sql += " and YR = " + QuoteStr(d.YEAR);
// }
// if (!String.IsNullOrEmpty(d.MN))
// {
// sql += " and MN = " + QuoteStr(d.MN);
// }
// if (!String.IsNullOrEmpty(d.YRMN))
// {
// sql += " and YRMN = " + QuoteStr(d.YRMN);
// }
// sql += " and sqno = d.sqno";
// sql += " group by[DEPCODEOL])";
//}
//if (!String.IsNullOrEmpty(d.cutdt))
//{
// sql += " and DATEADD(dd, 0, DATEDIFF(dd, 0, m.cutdt)) = DATEADD(dd, 0, DATEDIFF(dd, 0, " + QuoteStr(d.cutdt) + "))";
//}
if (!String.IsNullOrEmpty(d.OFFICECODE))
{
sql += " and D.OFFICECODE = '" + d.OFFICECODE + "'";
}
if (!String.IsNullOrEmpty(d.TYPECODE))
{
sql += " and D.TYPECODE = '" + d.TYPECODE + "'";
}
if (!String.IsNullOrEmpty(d.GASTCODE))
{
sql += " and D.GASTCODE = '" + d.GASTCODE + "'";
}
if (d.isdept)
{
sql += " and isnull(M.Audit_NO,'') like 'DU%' ";
}
else
{
sql += " and isnull(M.Audit_NO,'') like 'AU%' ";
}
sql += " and M.FLAG not in ('X','C')";
var res = Query <ASAUDITCUTDATE>(sql, param, conStr).ToList();
return(res);
}
protected override void ExecuteChild(AUDITPOSTTRNReq dataReq, ResponseAPI dataRes)
{
var res = new AuditPostTRNRes();
try
{
DBMode = dataReq.DBMode;
res._result.ServerAddr = ConnectionString();
res._result.DBMode = DBMode;
if (dataReq.MODE.Trim().ToLower() == "add")
{
var objTRN = ASSETKKF_ADO.Mssql.Asset.AUDITPOSTTRNADO.GetInstant().getAuditPostTRN(dataReq, null, conString);
if (objTRN != null && objTRN.Count > 0)
{
throw new Exception("คุณได้บันทึกตรวจสอบรหัสทรัพย์สินนี้แล้ว กรุณาตรวจสอบข้อมูล");
}
}
var updateAuditPost = ASSETKKF_ADO.Mssql.Asset.AUDITPOSTTRNADO.GetInstant().addAUDITPOSTTRN(dataReq, null, conString);
if (!String.IsNullOrEmpty(dataReq.IMGPATH))
{
res.IMGPATH = FilesUtilSvc.uploadCamera(dataReq.IMGPATH);
dataReq.IMGPATH = res.IMGPATH;
}
if (dataReq.FileToUpload != null)
{
res.IMGPATH = FilesUtilSvc.uploadImgFile(dataReq.FileToUpload);
dataReq.IMGPATH = res.IMGPATH;
}
if (!String.IsNullOrEmpty(res.IMGPATH))
{
ASSETKKF_ADO.Mssql.Asset.AUDITPOSTTRNADO.GetInstant().UpdateAUDITPOSTTRNIMG(dataReq, null, conString);
res.IMGSRC = FilesUtilSvc.getImageURL(res.IMGPATH);
}
if (dataReq.MODE.Trim().ToLower() == "editnew")
{
AuditPostReq req1 = new AuditPostReq()
{
COMPANY = dataReq.COMPANY,
SQNO = dataReq.SQNO,
isdept = dataReq.isdept
};
var lstPostMSTToTEMP = ASSETKKF_ADO.Mssql.Audit.AUDITPOSTMSTTOTEMPAdo.GetInstant().getDataToSendDep(req1, null, null, conString);
res.AuditToTEMPLST = lstPostMSTToTEMP;
var lstPostTRN = ASSETKKF_ADO.Mssql.Audit.AUDITPOSTTRNAdo.GetInstant().getPOSTTRN(req1, null, null, conString);
res.POSTTRNDuplicateLST = lstPostTRN;
var lstNoAudit = ASSETKKF_ADO.Mssql.Audit.AUDITCUTDATEAdo.GetInstant().getNoAudit(req1, null, null, conString);
res.NoAuditLST = lstNoAudit;
res._result._code = "200";
res._result._message = "";
res._result._status = "OK";
}
else
{
var req1 = new ASSETKKF_MODEL.Request.Asset.AuditPostReq()
{
SQNO = dataReq.SQNO,
DEPCODEOL = dataReq.DEPCODEOL,
COMPANY = dataReq.COMPANY,
LEADERCODE = dataReq.LEADERCODE,
AREACODE = dataReq.AREACODE,
UCODE = dataReq.UCODE,
isdept = dataReq.isdept
};
res.AUDITPOSTTRNLST = ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAUDITPOSTTRN(req1, null, conString);
var lstAUDITPOSTMST = ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAUDITPOSTMST(req1, null, null, conString);
//var lstWait = lstAUDITPOSTMST.Where(p => String.IsNullOrEmpty(p.PCODE)).ToList();
var lstChecked = lstAUDITPOSTMST.Where(p => !String.IsNullOrEmpty(p.PCODE)).ToList();
var lstWait = ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAUDITPOSTMST_WAIT(req1, null, null, conString);
res.AUDITPOSTMSTWAITLST = lstWait;
res.AUDITPOSTMSTCHECKEDLST = lstChecked;
res.AUDITPOSTMSTNOPROBLEMLST = lstChecked.Where(x => x.PFLAG != "Y").ToList();
res.AUDITPOSTMSTPROBLEMLST = lstChecked.Where(x => x.PFLAG == "Y").ToList();
var lstAUDITCUTDATE = ASSETKKF_ADO.Mssql.Asset.AuditCutADO.GetInstant().getAUDITCUTDATE(req1, null, conString);
res.AUDITCUTDATELST = lstAUDITCUTDATE;
res.AREACODE = dataReq.AREACODE;
res.COMPANY = dataReq.COMPANY;
res.DEPCODEOL = dataReq.DEPCODEOL;
res.LEADERCODE = dataReq.LEADERCODE;
res.SQNO = dataReq.SQNO;
res._result._code = "201";
res._result._message = "";
res._result._status = "Created";
}
}
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;
}
