public List <string> Select(string EMP_ID, string from, string to)
{
var sql = string.Format(@"SELECT v.*,c.NAME,E.EMP_NAME as E_NAME FROM VIST_CONTACTOR as v inner join CUSTOMER as c
on c.ID=v.CUSTOMER_ID inner join employee as e on e.EMP_ID=v.EMP_ID WHERE e.EMP_ID=@EMP_ID AND contact_date between @from and @to");
List <string> lst = new List <string>();
var t = DBManager <VIST_CONTACTOR> .ExecuteDynamic(sql, new
{
EMP_ID = EMP_ID,
from = from,
to = to
});
foreach (var item in t)
{
var k = "<tr>" +
"<td>" +
"<input type='checkbox' class='ckb' id='" + item.ID + "' /></td>" +
"<td>" + item.CUSTOMER_ID + "</td>" +
"<td>" + item.NAME + "</td>" +
"<td>" + item.CUST_VIST_PURPOSE + "</td>" +
"<td>" + item.E_NAME + "</td>" +
"<td>" + item.VIST_REMARK + "</td>" +
"<td>" + item.CUST_VIST_TYPE + "</td>" +
"<td>" + item.CONTACT_DATE.ToString("yyyy-MM-dd") + "</td>" +
"</tr>";
lst.Add(k);
}
return(lst);
}
public virtual dynamic SelectPaging(int start = 0, int end = 10, string key = "")
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by EMP_ID) AS ROWNUM, *,
(SELECT TOP 1 ID FROM CUSTOMER WHERE PERSON=E.EMP_ID) AS C_ID,
(SELECT TOP 1 NAME FROM CUSTOMER WHERE PERSON=E.EMP_ID) AS CUST_NAME
FROM EMPLOYEE AS E WHERE @key='' OR @key IS NULL OR EMP_ID LIKE @key +'%' OR EMP_NAME LIKE '%'+@key +'%' ) as u WHERE RowNum >= @start
AND RowNum < @end ORDER BY RowNum;");
return(DBManager <EMPLOYEE> .ExecuteDynamic(sql, new { start = start, end = end, key = key }));
}
public dynamic SelectPaging(int start = 0, int end = 10, string key = "")
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by id) AS ROWNUM, CUS.*,EMP_NAME,(SELECT COUNT(*) FROM END_USER WHERE CUS_ID=ID) AS NUM_END FROM CUSTOMER AS CUS
LEFT JOIN EMPLOYEE AS E ON E.EMP_ID=CUS.PERSON
WHERE @key IS NULL OR @key='' OR ID LIKE @key +'%' OR NAME LIKE '%'+@key+'%') as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <CUSTOMER> .ExecuteDynamic(sql, new
{
start = start,
end = end,
key = key
}));
}
public dynamic GetExport(string month, string cust_id, string status)
{
var sql = string.Format(@"SELECT
C.*,CUS.NAME,CUS.LOCATION AS LOC,E.EMP_NAME,(SELECT TOP 1 NAME FROM CUSTOMER WHERE ID=C.END_USER) AS END_USER_NAME
FROM CLAIM AS C INNER JOIN CUSTOMER AS CUS ON C.CUSTOMER_ID=CUS.ID INNER JOIN EMPLOYEE AS E ON E.EMP_ID=C.EMP_ID
WHERE (@month='' OR CLAIM_DATE LIKE @month+'%')
AND (@cust_id='' OR CUSTOMER_ID=@cust_id) AND(@status='' OR STATUS=@status)
");
return(DBManager <CLAIM> .ExecuteDynamic(sql, new
{
month = month,
cust_id = cust_id,
status = status
}));
}
public virtual dynamic GetVisit(string from, string to, string cus_id, string em_id, int start = 0, int end = 10)
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by e.emp_id) AS ROWNUM,v.*,e.EMP_NAME,cus.NAME as CUS_NAME
from VIST_CONTACTOR as v inner join customer as cus on v.CUSTOMER_ID=cus.ID left join employee as e on e.EMP_ID=v.EMP_ID WHERE (@FROM='' OR CONTACT_DATE BETWEEN @FROM AND @TO)
AND (@CUSTOMER_ID='' OR CUSTOMER_ID=@CUSTOMER_ID) AND (@EMP_ID='' OR e.EMP_ID=@EMP_ID)
) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <VIST_CONTACTOR> .ExecuteDynamic(sql, new
{
FROM = from,
TO = to,
EMP_ID = em_id,
CUSTOMER_ID = cus_id,
start = start,
end = end
}));
}
public virtual dynamic SelectPaging(int start = 0, int end = 10, string month = "", string cust_id = "", string status = "")
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by CLAIM_DATE) AS ROWNUM,
C.*,CUS.NAME,CUS.LOCATION AS LOC,E.EMP_NAME,(SELECT TOP 1 NAME FROM CUSTOMER WHERE ID=C.END_USER) AS END_USER_NAME
FROM CLAIM AS C INNER JOIN CUSTOMER AS CUS ON C.CUSTOMER_ID=CUS.ID INNER JOIN EMPLOYEE AS E ON E.EMP_ID=C.EMP_ID
WHERE (@month='' OR CLAIM_DATE LIKE @month+'%')
AND (@cust_id='' OR CUSTOMER_ID=@cust_id) AND(@status='' OR STATUS=@status)
) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <CLAIM> .ExecuteDynamic(sql, new
{
start = start,
end = end,
month = month,
cust_id = cust_id,
status = status
}));
}
public dynamic GetExport(string from = "", string to = "", string cust_id = "", string status = "")
{
var sql = string.Format(@"SELECT O.*,CUS.NAME,E.EMP_NAME,
(SELECT TOP 1 NAME FROM END_USER WHERE CUS_ID=O.END_USER) as END_USER_NAME
FROM ORDERED AS O
INNER JOIN CUSTOMER AS CUS ON O.CUSTOMER_ID=CUS.ID INNER JOIN EMPLOYEE AS E ON E.EMP_ID=O.EMP_ID
WHERE (@from ='' OR ORDED_DATE BETWEEN @from AND @to)
AND (@cust_id='' OR CUSTOMER_ID=@cust_id) AND(@status='' OR ORD_STAT=@status)
");
return(DBManager <ORDERED> .ExecuteDynamic(sql, new
{
from = from,
to = to,
cust_id = cust_id,
status = status
}));
}
public virtual dynamic SelectPaging(string from, int start = 0, int end = 10)
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by e.emp_id) AS ROWNUM,
e.EMP_ID,e.EMP_NAME,e.EMP_DEPT,(select top 1 VISIT_TARGET from EMP_VISIT as em where em.EMP_ID=e.emp_id AND VISIT_PLAN_MONTH = @from) as tar,
(select COUNT(*) from VIST_CONTACTOR as v where v.EMP_ID=e.emp_id and contact_date like @from +'%') as result,
(select COUNT(*) from VIST_CONTACTOR as v where v.EMP_ID=e.emp_id and CUST_VIST_TYPE='DIRECT' and contact_date like @from +'%') as dir,
(select COUNT(*) from VIST_CONTACTOR as v where v.EMP_ID=e.emp_id and CUST_VIST_TYPE='CALL' and contact_date like @from +'%') as ca,
(select COUNT(*) from VIST_CONTACTOR as v where v.EMP_ID=e.emp_id and CUST_VIST_TYPE='E-MAIL' and contact_date like @from +'%') as email
from EMPLOYEE AS e
) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <VIST_CONTACTOR> .ExecuteDynamic(sql, new
{
from = from,
start = start,
end = end
}));
}
public DataTable Export(string from, string to, string cus_id, string em_id)
{
var sql = string.Format(@"SELECT v.*,e.EMP_NAME,cus.NAME as CUS_NAME
from VIST_CONTACTOR as v inner join customer as cus on v.CUSTOMER_ID=cus.ID left join employee as e on e.EMP_ID=v.EMP_ID WHERE (@FROM='' OR CONTACT_DATE BETWEEN @FROM AND @TO)
AND (@CUSTOMER_ID='' OR CUSTOMER_ID=@CUSTOMER_ID) AND (@EMP_ID='' OR e.EMP_ID=@EMP_ID)");
DataTable dtb = new DataTable();
var result = DBManager <VIST_CONTACTOR> .ExecuteDynamic(sql, new
{
FROM = from,
TO = to,
EMP_ID = em_id,
CUSTOMER_ID = cus_id
});
dtb.Clear();
dtb.Columns.Add("DATE");
dtb.Columns.Add("CUSTOMER_ID");
dtb.Columns.Add("CUS_NAME");
dtb.Columns.Add("VISIT_TYPE");
dtb.Columns.Add("PURPOSE");
dtb.Columns.Add("CONTENT");
dtb.Columns.Add("EMPLOYEE");
foreach (var item in result)
{
DataRow r = dtb.NewRow();
r["DATE"] = item.CONTACT_DATE == null ? "" : item.CONTACT_DATE.ToString("yyyy-MM-dd");
r["CUSTOMER_ID"] = item.CUSTOMER_ID;
r["CUS_NAME"] = item.CUS_NAME;
r["VISIT_TYPE"] = item.CUST_VIST_TYPE;
r["PURPOSE"] = item.CUST_VIST_PURPOSE;
r["CONTENT"] = item.VIST_REMARK;
r["EMPLOYEE"] = item.EMP_NAME;
dtb.Rows.Add(r);
}
return(dtb);
}
public virtual dynamic SelectPaging(int start = 0, int end = 10, string from = "", string to = "", string cust_id = "", string status = "")
{
if (string.IsNullOrEmpty(from) || string.IsNullOrEmpty(to))
{
from = to = DateTime.Now.ToString("yyyy-MM-dd");
}
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by ORDED_DATE) AS ROWNUM, O.*,CUS.NAME,E.EMP_NAME,
(SELECT TOP 1 NAME FROM END_USER WHERE CUS_ID=O.END_USER) as END_USER_NAME
FROM ORDERED AS O
INNER JOIN CUSTOMER AS CUS ON O.CUSTOMER_ID=CUS.ID INNER JOIN EMPLOYEE AS E ON E.EMP_ID=O.EMP_ID
WHERE (@from ='' OR ORDED_DATE BETWEEN @from AND @to)
AND (@cust_id='' OR CUSTOMER_ID=@cust_id) AND(@status='' OR ORD_STAT=@status)
) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <ORDERED> .ExecuteDynamic(sql, new
{
start = start,
end = end,
from = from,
to = to,
cust_id = cust_id,
status = status
}));
}