public List <ASSETKKF_MODEL.Response.Asset.LeaderList> getLeaderLst(AuditCutInfoReq dataReq, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "select distinct OFFICECODE as id,OFNAME as name ,(OFFICECODE + ' ' + OFNAME) as descriptions from FT_UserAsset('') where 1 = 1";
if (!String.IsNullOrEmpty(dataReq.Company))
{
var comp = "";
comp = "'" + dataReq.Company.Replace(",", "','") + "'";
sql += " and COMPANY in (" + comp + ") ";
}
if ((!dataReq.Menu3 && !dataReq.Menu4) && ((!String.IsNullOrEmpty(dataReq.DeptCode)) || dataReq.DeptLST != null))
{
sql += " and (";
if (!String.IsNullOrEmpty(dataReq.DeptCode))
{
sql += " DEPCODEEOL like (case when isnull('" + dataReq.DeptCode + "','') <> '' then SUBSTRING('" + dataReq.DeptCode + "',1,1) else '' end + '%')";
}
if ((dataReq.DeptLST != null && dataReq.DeptLST.Length > 0) && (dataReq.DeptLST != "null"))
{
var arrDept = dataReq.DeptLST.Split(",");
foreach (string s in arrDept)
{
sql += " or DEPCODEEOL like (case when isnull('" + s + "','') <> '' then SUBSTRING('" + s + "',1,1) else '' end + '%')";
}
}
sql += " )";
}
else if (!String.IsNullOrEmpty(dataReq.DEPCODEOL))
{
sql += " and (";
sql += " DEPCODEOL like (case when isnull('" + dataReq.DEPCODEOL + "','') <> '' then SUBSTRING('" + dataReq.DEPCODEOL + "',1,1) else '' end + '%')";
sql += " )";
}
if (!String.IsNullOrEmpty(dataReq.sqno))
{
sql += " and SQNO = '" + dataReq.sqno + "'";
}
sql += " order by OFFICECODE";
var res = Query <LeaderList>(sql, param, conStr).ToList();
return(res);
}
public List <String> getDepLikeList(AuditCutInfoReq dataReq, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = " SELECT case when isnull(DEPCODEOL,'') <> '' then SUBSTRING(DEPCODEOL,1,2) else '' end as DEPLike FROM [dbo].FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(dataReq.Company) + ") D where 1 = 1";
if (!String.IsNullOrEmpty(dataReq.sqno))
{
sql += " and SQNO = '" + dataReq.sqno + "'";
}
if (!String.IsNullOrEmpty(dataReq.Company))
{
var comp = "";
comp = "'" + dataReq.Company.Replace(",", "','") + "'";
sql += " and COMPANY in (" + comp + ") ";
}
if ((!dataReq.Menu3 && !dataReq.Menu4) && ((!String.IsNullOrEmpty(dataReq.DeptCode)) || dataReq.DeptLST != null))
{
sql += " and (";
if (!String.IsNullOrEmpty(dataReq.DeptCode))
{
sql += " DEPCODEOL like (case when isnull('" + dataReq.DeptCode + "','') <> '' then SUBSTRING('" + dataReq.DeptCode + "',1,1) else '' end + '%')";
}
if ((dataReq.DeptLST != null && dataReq.DeptLST.Length > 0) && (dataReq.DeptLST != "null"))
{
var arrDept = dataReq.DeptLST.Split(",");
foreach (string s in arrDept)
{
sql += " or DEPCODEOL like (case when isnull('" + s + "','') <> '' then SUBSTRING('" + s + "',1,1) else '' end + '%')";
}
}
sql += " )";
}
else if (!String.IsNullOrEmpty(dataReq.DEPCODEOL))
{
sql += " and (";
sql += " DEPCODEOL like (case when isnull('" + dataReq.DEPCODEOL + "','') <> '' then SUBSTRING('" + dataReq.DEPCODEOL + "',1,1) else '' end + '%')";
sql += " )";
}
if (!String.IsNullOrEmpty(dataReq.DEPMST))
{
// sql += " and DEPMST = '" + dataReq.DEPMST + "'";
sql += "and DEPCODEOL in (SELECT [DEPCODEOL] ";
sql += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(dataReq.Company) + ") ";
sql += " where DEPMST = '" + dataReq.DEPMST + "'";
sql += " and company = '" + dataReq.Company + "'";
if (!String.IsNullOrEmpty(dataReq.sqno))
{
sql += " and SQNO = '" + dataReq.sqno + "'";
}
sql += " and assetno = d.assetno";
sql += " group by[DEPCODEOL])";
}
sql += " group by case when isnull(DEPCODEOL,'') <> '' then SUBSTRING(DEPCODEOL,1,2) else '' end";
var res = Query <String>(sql, param, conStr).ToList();
return(res);
}
public List <ASSETKKF_MODEL.Response.Asset.DEPTList> getDeptLst(AuditCutInfoReq dataReq, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
string cmd = "Select DEPCODEOL,MAX(STNAME) AS STNAME,DEPCODE,M.COMPANY ";
cmd += " FROM FT_ASAUDITCUTDATEMST_COMPANY(" + QuoteStr(dataReq.Company) + ") M ,FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(dataReq.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(dataReq.Company))
{
var comp = "";
comp = "'" + dataReq.Company.Replace(",", "','") + "'";
cmd += " and M.COMPANY in (" + comp + ") ";
}
if (!String.IsNullOrEmpty(dataReq.sqno))
{
cmd += " and M.SQNO = '" + dataReq.sqno + "'";
}
if (!String.IsNullOrEmpty(dataReq.DEPMST))
{
cmd += "and DEPCODEOL in (SELECT [DEPCODEOL] ";
cmd += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(dataReq.Company) + ") ";
cmd += " where DEPMST = '" + dataReq.DEPMST + "'";
cmd += " and company = '" + dataReq.Company + "'";
cmd += " and assetno = d.assetno";
if (!String.IsNullOrEmpty(dataReq.sqno))
{
cmd += " and SQNO = '" + dataReq.sqno + "'";
}
cmd += " group by[DEPCODEOL])";
}
if ((!dataReq.Menu3 && !dataReq.Menu4) && ((!String.IsNullOrEmpty(dataReq.DeptCode)) || dataReq.DeptLST != null))
{
cmd += " and (";
if (!String.IsNullOrEmpty(dataReq.DeptCode))
{
cmd += " DEPCODEOL like (case when isnull('" + dataReq.DeptCode + "','') <> '' then SUBSTRING('" + dataReq.DeptCode + "',1,1) else '' end + '%')";
}
if ((dataReq.DeptLST != null && dataReq.DeptLST.Length > 0) && (dataReq.DeptLST != "null"))
{
var arrDept = dataReq.DeptLST.Split(",");
foreach (string s in arrDept)
{
cmd += " or DEPCODEOL like (case when isnull('" + s + "','') <> '' then SUBSTRING('" + s + "',1,1) else '' end + '%')";
}
}
cmd += " )";
}
else if (!String.IsNullOrEmpty(dataReq.DEPCODEOL))
{
cmd += " and (";
cmd += " DEPCODEOL like (case when isnull('" + dataReq.DEPCODEOL + "','') <> '' then SUBSTRING('" + dataReq.DEPCODEOL + "',1,1) else '' end + '%')";
cmd += " )";
}
if (!String.IsNullOrEmpty(dataReq.YR))
{
cmd += " and M.YR = " + QuoteStr(dataReq.YR);
}
if (!String.IsNullOrEmpty(dataReq.MN))
{
cmd += " and M.MN = " + QuoteStr(dataReq.MN);
}
if (!String.IsNullOrEmpty(dataReq.YRMN))
{
cmd += " and M.YRMN = " + QuoteStr(dataReq.YRMN);
}
if (dataReq.isdept)
{
cmd += " and isnull(M.Audit_NO,'') like 'DU%' ";
}
else
{
cmd += " and isnull(M.Audit_NO,'') like 'AU%' ";
}
cmd += " GROUP BY DEPCODEOL,DEPCODE,M.COMPANY order by DEPCODEOL";
var obj = Query <ASSETKKF_MODEL.Request.Asset.DEPTList>(cmd, param, conStr).ToList();
List <ASSETKKF_MODEL.Response.Asset.DEPTList> res = new List <ASSETKKF_MODEL.Response.Asset.DEPTList>();
if (obj != null && obj.Count > 0)
{
obj.ForEach(x => {
res.Add(new ASSETKKF_MODEL.Response.Asset.DEPTList
{
id = x.DEPCODEOL,
name = x.STNAME,
descriptions = x.DEPCODEOL + " : " + x.STNAME
});
});
}
return(res);
}
public List <ASSETKKF_MODEL.Response.Asset.LeaderList> getLeaderCentralLst(AuditCutInfoReq dataReq, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "select distinct [CODEMPID] as OFFICECODE,[NAMEMPT] as OFNAME from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where 1 = 1";
if (!String.IsNullOrEmpty(dataReq.Company))
{
sql += " and ( ";
var arrComp = dataReq.Company.Split(",");
var n = arrComp.Length;
for (int i = 0; i < n; i++)
{
sql += " [CODCOMP] like '" + arrComp[i] + "%'";
if (i < (n - 1))
{
sql += " or ";
}
}
sql += " )";
}
if ((!dataReq.Menu3 && !dataReq.Menu4) && ((!String.IsNullOrEmpty(dataReq.DeptCode)) || dataReq.DeptLST != null))
{
sql += " and (";
if (!String.IsNullOrEmpty(dataReq.DeptCode))
{
sql += " DEPCODEOL like (case when isnull('" + dataReq.DeptCode + "','') <> '' then SUBSTRING('" + dataReq.DeptCode + "',1,1) else '' end + '%')";
}
if ((dataReq.DeptLST != null && dataReq.DeptLST.Length > 0) && (dataReq.DeptLST != "null"))
{
var arrDept = dataReq.DeptLST.Split(",");
foreach (string s in arrDept)
{
sql += " or DEPCODEOL like (case when isnull('" + s + "','') <> '' then SUBSTRING('" + s + "',1,1) else '' end + '%')";
}
}
sql += " )";
}
else if (!String.IsNullOrEmpty(dataReq.DEPCODEOL))
{
sql += " and (";
sql += " DEPCODEOL like (case when isnull('" + dataReq.DEPCODEOL + "','') <> '' then SUBSTRING('" + dataReq.DEPCODEOL + "',1,1) else '' end + '%')";
sql += " )";
}
if (!String.IsNullOrEmpty(dataReq.DEPMST))
{
sql += " and CODCOMP = '" + dataReq.DEPMST + "'";
}
if (!String.IsNullOrEmpty(dataReq.sqno))
{
sql += "and DEPCODEOL in (SELECT [DEPCODEOL] ";
sql += " FROM FT_ASAUDITCUTDATE_COMPANY(" + QuoteStr(dataReq.Company) + ") ";
sql += " where SQNO = '" + dataReq.sqno + "'";
sql += " group by[DEPCODEOL])";
}
sql += " order by CODEMPID";
var obj = Query <ASSETKKF_MODEL.Request.Asset.Leader>(sql, param, conStr).ToList();
List <LeaderList> res = new List <LeaderList>();
if (obj != null && obj.Count > 0)
{
obj.ForEach(x => {
res.Add(new LeaderList
{
id = x.OFFICECODE,
name = x.OFNAME,
descriptions = x.OFFICECODE + " : " + x.OFNAME,
});
});
}
return(res);
}
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);
}
