public List <AuditDeptSummary> GetDEPMSTSummary(ASSETKKF_MODEL.Request.Asset.AuditSummaryReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
int isdep = d.isdept ? 1 : 0;
string cmd = " select company,YRMN,DEPMST ,max(DEPNM) as DEPNM,max(SQNO) as SQNO,max(audit_no) as audit_no,sum(QTY_TOTAL) as QTY_TOTAL,sum(QTY_CHECKED) as QTY_CHECKED,sum(QTY_WAIT) as QTY_WAIT,sum(QTY_TRN) as QTY_TRN ";
cmd += ", Case when sum(QTY_TOTAL) > 0 then CAST(((CAST(sum(QTY_CHECKED) as DECIMAL(9,2)) /CAST(sum(QTY_TOTAL) as DECIMAL(9,2)))*100) as DECIMAL(9,2)) else 0 end progress ";
//cmd += "from AuditSummary (" + d.year + "," + d.mn + ") where 1 = 1 and audit_no is not null";
cmd += " from AuditSummary_company (" + QuoteStr(d.Company) + "," + QuoteStr(d.year) + "," + QuoteStr(d.mn) + "," + QuoteStr(d.yrmn) + "," + isdep + ") as C where 1 = 1 and audit_no is not null";
if (!String.IsNullOrEmpty(d.Company))
{
var comp = "";
comp = "'" + d.Company.Replace(",", "','") + "'";
cmd += " and COMPANY in (" + comp + ") ";
}
if ((!d.Menu3 && !d.Menu4))
{
cmd += " and (";
if (!String.IsNullOrEmpty(d.DEPCODEOL))
{
cmd += " DEPCODEOL = '" + d.DEPCODEOL + "'";
}
if (d.DEPTCODELST != null && d.DEPTCODELST.Length > 0)
{
var arrDept = d.DEPTCODELST.Split(",");
foreach (string s in arrDept)
{
cmd += " or DEPCODEOL like ' " + s + "%'";
}
}
cmd += " )";
}
if (!String.IsNullOrEmpty(d.yrmn))
{
cmd += " YRMN = '" + d.yrmn + "'";
}
if (d.isdept)
{
cmd += " and isnull(Audit_NO,'') like 'DU%' ";
}
else
{
cmd += " and isnull(Audit_NO,'') like 'AU%' ";
}
cmd += " group by company,YRMN,DEPMST";
cmd += " order by company,YRMN,DEPMST";
var res = Query <AuditDeptSummary>(cmd, param, conStr).ToList();
return(res);
}
public List <Multiselect> GetAuditMN(ASSETKKF_MODEL.Request.Asset.AuditSummaryReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
string cmd = " SELECT MN as id,MN as description FROM [dbo].FT_ASAUDITCUTDATEMST_COMPANY(" + QuoteStr(d.Company) + ") as M ";
cmd += " where M.FLAG not in ('X','C') and audit_no is not null";
cmd += " and COMPANY in (" + QuoteStr(d.Company) + ") ";
if (d.isdept)
{
cmd += " and isnull(M.Audit_NO,'') like 'DU%' ";
}
else
{
cmd += " and isnull(M.Audit_NO,'') like 'AU%' ";
}
//if (!String.IsNullOrEmpty(d.Company))
//{
// var comp = "";
// comp = "'" + d.Company.Replace(",", "','") + "'";
// cmd += " and COMPANY in (" + comp + ") ";
//}
if (!String.IsNullOrEmpty(d.depmst))
{
cmd += " and depmst = '" + d.depmst + "'";
}
if (!String.IsNullOrEmpty(d.year))
{
cmd += " and YR = '" + d.year + "'";
}
cmd += " group by MN ";
cmd += " order by MN desc ";
var res = Query <Multiselect>(cmd, param, conStr).ToList();
return(res);
}
public List <ASSETKKF_MODEL.Data.Mssql.Asset.AuditSummary> GetSummary(ASSETKKF_MODEL.Request.Asset.AuditSummaryReq d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
int isdep = d.isdept ? 1 : 0;
string cmd = " select sum(QTY_TOTAL) as QTY_TOTAL,sum(QTY_CHECKED) as QTY_CHECKED,sum(QTY_WAIT) as QTY_WAIT,sum(QTY_PROBLEM) as QTY_PROBLEM,sum(QTY_NOPROBLEM) as QTY_NOPROBLEM ,sum(QTY_TRN) as QTY_TRN ";
cmd += ", Case when sum(QTY_TOTAL) > 0 then CAST(((CAST(sum(QTY_CHECKED) as DECIMAL(9,2)) /CAST(sum(QTY_TOTAL) as DECIMAL(9,2)))*100) as DECIMAL(9,2)) else 0 end progress ";
//cmd += " ,(select COUNT(AssETNO) from FT_ASAUDITPOSTTRN_COMPANY(" + QuoteStr(d.Company) + ")";
//cmd += " where yr = " + d.year + " and mn = " + d.mn + " ) as QTY_TRN";
//cmd += " from AuditSummary (" + d.year + "," + d.mn + ") as C where 1 = 1 and audit_no is not null";
cmd += " from AuditSummary_company (" + QuoteStr(d.Company) + "," + QuoteStr(d.year) + "," + QuoteStr(d.mn) + "," + QuoteStr(d.yrmn) + "," + isdep + ") as C where 1 = 1 and audit_no is not null";
if (!String.IsNullOrEmpty(d.Company))
{
var comp = "";
comp = "'" + d.Company.Replace(",", "','") + "'";
cmd += " and COMPANY in (" + comp + ") ";
}
if ((!d.Menu3 && !d.Menu4))
{
cmd += " and (";
if (!String.IsNullOrEmpty(d.DEPCODE))
{
cmd += " DEPCODEOL = '" + d.DEPCODE + "'";
}
if (d.DEPTCODELST != null && d.DEPTCODELST.Length > 0)
{
var arrDept = d.DEPTCODELST.Split(",");
foreach (string s in arrDept)
{
cmd += " or DEPCODEOL like ' " + s + "%'";
}
}
cmd += " )";
}
if (!String.IsNullOrEmpty(d.yrmn))
{
cmd += " and YRMN = '" + d.yrmn + "'";
}
if (!String.IsNullOrEmpty(d.depmst))
{
cmd += " and depmst = '" + d.depmst + "'";
}
if (!String.IsNullOrEmpty(d.DEPCODEOL))
{
cmd += " and DEPCODEOL = '" + d.DEPCODEOL + "'";
}
if (!String.IsNullOrEmpty(d.OFFICECODE))
{
cmd += " and OFFICECODE = '" + d.OFFICECODE + "'";
//cmd += " and DEPCODEOL = (SELECT distinct DEPCODEEOL FROM [dbo].[FT_UserAsset] ( '" + d.OFFICECODE + "') where COMPANY = c.COMPANY )";
}
if (d.isdept)
{
cmd += " and isnull(Audit_NO,'') like 'DU%' ";
}
else
{
cmd += " and isnull(Audit_NO,'') like 'AU%' ";
}
var res = Query <ASSETKKF_MODEL.Data.Mssql.Asset.AuditSummary>(cmd, param, conStr).ToList();
return(res);
}
