public string GenerateId()
{
string id = DateTime.Now.ToString("yyMMdd");
string sql = "select top 1 ID from CUSTOMER WHERE ID LIKE @id + '%' order by ID desc";
CUSTOMER claim = DBManager <CUSTOMER> .ExecuteReader(sql, new { id = id }).FirstOrDefault();
if (claim == null || claim.ID.Trim() == id)
{
id = id + "01";
}
else
{
string str = claim.ID.Trim().Substring(6);
int num = Convert.ToInt32(str) + 1;
if (num < 10)
{
id += "0" + num.ToString();
}
else
{
id += num.ToString();
}
}
return(id);
}
public bool CheckExist(string EMP_ID, string DATE)
{
var sql = "SELECT EMP_ID FROM EMP_VISIT WHERE EMP_ID=@EMP_ID AND VISIT_PLAN_MONTH LIKE @DATE +'%'";
var t = DBManager <EMP_VISIT> .ExecuteReader(sql, new { EMP_ID = EMP_ID, DATE = DATE }).FirstOrDefault();
return(t == null);
}
public List <END_USER> GetEndUser(string CUS_ID, string END_USER_ID)
{
var sql = "SELECT * FROM END_USER WHERE CUS_ID=@CUS_ID AND END_USER_ID=@END_USER_ID";
return(DBManager <END_USER> .ExecuteReader(sql, new
{
CUS_ID = CUS_ID,
END_USER_ID = END_USER_ID
}));
}
public List <END_USER> GetFullEndUser(string ID = "")
{
var sql = "SELECT * FROM END_USER";
if (string.IsNullOrWhiteSpace(ID))
{
return(DBManager <END_USER> .ExecuteReader(sql));
}
sql += " WHERE CUS_ID=@ID";
return(DBManager <END_USER> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <STD_CODE> Select(string STD_CD_KIND = "")
{
var sql = "SELECT * FROM STD_CODE ";
if (string.IsNullOrWhiteSpace(STD_CD_KIND))
{
return(DBManager <STD_CODE> .ExecuteReader(sql));
}
sql += " WHERE STD_CD_KIND=@STD_CD_KIND";
return(DBManager <STD_CODE> .ExecuteReader(sql, new { STD_CD_KIND = STD_CD_KIND }));
}
public virtual List <EMPLOYEE> Select(string ID = "")
{
var sql = "SELECT * FROM EMPLOYEE ";
if (string.IsNullOrWhiteSpace(ID))
{
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
sql += " WHERE EMP_ID=@ID";
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <EMP_VISIT> Select(int ID = 0)
{
var sql = "SELECT * FROM EMP_VISIT ";
if (ID == 0)
{
return(DBManager <EMP_VISIT> .ExecuteReader(sql));
}
sql += " WHERE ID=@ID";
return(DBManager <EMP_VISIT> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <CONTACT> Select(string ID = "")
{
var sql = "SELECT * FROM CONTACT ";
if (string.IsNullOrWhiteSpace(ID))
{
return(DBManager <CONTACT> .ExecuteReader(sql));
}
sql += " WHERE ID=@ID";
return(DBManager <CONTACT> .ExecuteReader(sql, new { ID = ID }));
}
public string GetTarget(string ID, string DATE)
{
var sql = "SELECT VISIT_TARGET FROM EMP_VISIT WHERE EMP_ID=@ID AND VISIT_PLAN_MONTH LIKE @DATE +'%'";
List <string> target = new List <string>();
var t = DBManager <EMP_VISIT> .ExecuteReader(sql, new { ID = ID, DATE = DATE });
foreach (var item in t)
{
return(item.VISIT_TARGET.ToString());
}
return(string.Empty);
}
public bool Login(string EMP_ID, string Password)
{
var sql = "SELECT EMP_ID,EMP_NAME,EMP_DEPT,ROLE FROM EMPLOYEE WHERE EMP_ID=@EMP_ID AND PASSWORD=@PASSWORD";
EMPLOYEE employee = DBManager <EMPLOYEE> .ExecuteReader(sql, new { EMP_ID = EMP_ID, PASSWORD = Password }).FirstOrDefault();
if (employee == null)
{
return(false);
}
HttpContext.Current.Session["Username"] = employee.EMP_ID.Trim();
HttpContext.Current.Session["Name"] = employee.EMP_NAME.Trim();
HttpContext.Current.Session["Dept"] = employee.EMP_DEPT;
HttpContext.Current.Session["Role"] = employee.ROLE;
return(true);
}
public List <OrderResult> GetSumExport(string from = "", string to = "", string cust_id = "", string status = "")
{
var sql = string.Format(@"
SELECT CUSTOMER_ID, NAME, QUANTITY,STS_ST_CLS,STS_ST_SER,SURFACE_CD,BASE_PRICE,EFFECT_PRICE,BIDD_PRICE,
ORD_USAGE,ORD_STAT, E.EMP_DEPT, ORDED_DATE
FROM ORDERED AS O
INNER JOIN EMPLOYEE AS E
ON E.EMP_ID=O.EMP_ID AND E.EMP_DEPT IN ('Sales Team','Solution Sales','Solution Improvement')
INNER JOIN CUSTOMER AS C ON C.ID=O.CUSTOMER_ID
WHERE STS_ST_CLS IS NOT NULL
AND ORD_USAGE IS NOT NULL AND ORD_USAGE <> ''
AND ORDED_DATE BETWEEN @from AND @to AND (@cust_id='' OR CUSTOMER_ID=@cust_id) AND(@status='' OR ORD_STAT=@status)
");
return(DBManager <OrderResult> .ExecuteReader(sql, new
{
from = from,
to = to,
cust_id = cust_id,
status = status
}));
}
public string GenerateId()
{
string id = DateTime.Now.ToString("yyMMdd");
string sql = "select top 1 ID from CONTACT WHERE ID LIKE @id + '%' order by ID desc";
CONTACT cont = DBManager <CONTACT> .ExecuteReader(sql, new { id = id }).FirstOrDefault();
if (cont == null)
{
id = id + "0001";
}
else
{
string str = cont.ID.Trim().Substring(6);
string num = (Convert.ToInt32(str) + 1).ToString();
for (int i = 0; i < str.Length - num.Length; i++)
{
id += "0";
}
id += num.ToString();
}
return(id);
}
public string GenerateId()
{
string id = DateTime.Now.ToString("yyMMdd");
string sql = "select top 1 CLAIM_NO from CLAIM WHERE CLAIM_NO LIKE @id + '%' order by CLAIM_NO desc";
CLAIM claim = DBManager <CLAIM> .ExecuteReader(sql, new { id = id }).FirstOrDefault();
if (claim == null)
{
id = id + "0001";
}
else
{
string str = claim.CLAIM_NO.Trim().Substring(6);
string num = (Convert.ToInt32(str) + 1).ToString();
for (int i = 0; i < str.Length - num.Length; i++)
{
id += "0";
}
id += num.ToString();
}
return(id);
}
public List <EMPLOYEE> GetTeam()
{
var sql = "SELECT DISTINCT(EMP_DEPT) FROM EMPLOYEE WHERE EMP_DEPT IS NOT NULL AND EMP_DEPT <>''";
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
public virtual List <CONTACT> SelectCusContact(string ID)
{
var sql = "SELECT * FROM CONTACT WHERE CUS_ID=@ID";
return(DBManager <CONTACT> .ExecuteReader(sql, new { ID = ID }));
}
public List <VIST_CONTACTOR> GetVisit(string ID)
{
var sql = "SELECT * FROM VIST_CONTACTOR WHERE ID=@ID";
return(DBManager <VIST_CONTACTOR> .ExecuteReader(sql, new { ID = ID }));
}
public List <CUSTOMER> GetCustomerByEmp(string EMP_ID)
{
var sql = "SELECT ID,NAME,CLASS FROM CUSTOMER WHERE PERSON=@ID ";
return(DBManager <CUSTOMER> .ExecuteReader(sql, new { ID = EMP_ID }));
}
public END_USER GetEndUserById(string ID = "")
{
var sql = "SELECT TOP 1 * FROM END_USER WHERE END_USER_ID=@ID";
return(DBManager <END_USER> .ExecuteReader(sql, new { ID = ID }).FirstOrDefault());
}
public List <CUSTOMER> GetSimple()
{
var sql = "SELECT ID,NAME FROM CUSTOMER ORDER BY ID";
return(DBManager <CUSTOMER> .ExecuteReader(sql));
}
public virtual List <EMP_VISIT> SelectPaging(int start = 0, int end = 10)
{
var sql = "SELECT * FROM(SELECT ROW_NUMBER() OVER (order by id) AS ROWNUM, * FROM EMP_VISIT) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;";
return(DBManager <EMP_VISIT> .ExecuteReader(sql, new { start = start, end = end }));
}
public virtual List <EMPLOYEE> SelectSimple()
{
var sql = "SELECT EMP_ID,EMP_NAME FROM EMPLOYEE ";
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}