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)); }