public static DataSet GetSearchReg(string Search_name, string Search_class)
{
string sSql = @"";
sSql += " SELECT STOCK_CODE, STOCK_NAME, STOCK_CLASS1, STOCK_CLASS2, STOCK_REMARK ";
sSql += " FROM STOCK_CLASS";
sSql += " WHERE 1 = 1 ";
if (string.IsNullOrEmpty(Search_name) && string.IsNullOrEmpty(Search_class))
{
sSql += " AND 1 = 2 ";
}
else
{
sSql += " AND ( STOCK_CODE LIKE '%" + Search_name + "%'" + " OR STOCK_NAME LIKE '%" + Search_name + "%')";
sSql += " AND ( STOCK_CLASS1 LIKE '%" + Search_class + "%'" + " OR STOCK_CLASS2 LIKE '%" + Search_class + "%')";
}
sSql += " ORDER BY STOCK_NAME";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
return(user);
}
public static bool GetCheckUser(string UserCode, string UserPass)
{
string sSql = @"";
sSql += " SELECT COUNT(*) ";
sSql += " FROM USER_MST";
sSql += " WHERE USER_CODE = '" + UserCode + "'";
sSql += " AND PASSWORD = '******'";
DataSet ds;
using (var db = new MSSQLDB())
{
ds = db.Query(sSql);
if (ds.Tables[0].Rows[0][0].ToString() == "0")
{
return(false);
}
else
{
return(true);
}
}
return(false);
}
public static DataSet GetSearchData2(
string Search_name, string Search_class, string Search_per, string Search_gap
, string Search_sa_rate, string Search_bp_rate, string Search_np_rate
, string List_Type, string User_ID
)
{
string sSql = @"";
sSql += " SELECT A.STOCK_CODE, A.STOCK_NAME, STOCK_URL, TOTAL_AMT, SALES_AMT, BIZ_PROFIT, NET_PROFIT, PER, EST_PER, BIZ_PER, STOCK_PRICE, NAVER_PRICE, ";
sSql += " CAL_PSR, CAL_POR, CAL_PER, BIZ_RATE, NET_RATE, ";
sSql += " SA_Y3, SA_Y2, SA_Y1, SA_Y0, BP_Y3, BP_Y2, BP_Y1, BP_Y0, NP_Y3, NP_Y2, NP_Y1, NP_Y0, ";
sSql += " SA_RATE, BP_RATE, NP_RATE, ";
sSql += " B.STOCK_REMARK, ISNULL(LEN(B.STOCK_REMARK),0) as REMARK_LEN ";
sSql += " FROM STOCK_INFO A";
sSql += " , STOCK_CLASS B";
sSql += " WHERE A.STOCK_CODE = B.STOCK_CODE ";
sSql += " AND ( A.STOCK_CODE LIKE '%" + Search_name + "%'" + " OR A.STOCK_NAME LIKE '%" + Search_name + "%')";
sSql += " AND ( A.STOCK_CLASS1 LIKE '%" + Search_class + "%'" + " OR A.STOCK_CLASS2 LIKE '%" + Search_class + "%')";
//User별
if (List_Type == "User")
{
sSql += " AND A.STOCK_CODE IN ( SELECT C.STOCK_CODE FROM USER_STOCK C WHERE C.USER_ID = " + User_ID + " )";
}
if (!string.IsNullOrEmpty(Search_per) && Search_per != "")
{
sSql += " AND CAL_PER <= " + Search_per;
}
if (!string.IsNullOrEmpty(Search_gap) && Search_gap != "")
{
sSql += " AND ( (NAVER_PRICE - STOCK_PRICE) / NAVER_PRICE * 100 >= " + Search_gap + " OR (NAVER_PRICE - STOCK_PRICE) / NAVER_PRICE * 100 <= (" + Search_gap + "*-1) )";
sSql += " AND NAVER_PRICE <> 0 ";
}
if (!string.IsNullOrEmpty(Search_sa_rate) && Search_sa_rate != "")
{
sSql += " AND SA_RATE >= " + Search_sa_rate;
}
if (!string.IsNullOrEmpty(Search_bp_rate) && Search_bp_rate != "")
{
sSql += " AND BP_RATE >= " + Search_bp_rate;
}
if (!string.IsNullOrEmpty(Search_np_rate) && Search_np_rate != "")
{
sSql += " AND NP_RATE >= " + Search_np_rate;
}
sSql += " ORDER BY TOTAL_AMT DESC";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
return(user);
}
public static DataSet GetSearchData3(
string Search_name, string Search_class, string Search_buy_gap, string Search_sell_gap
, string List_Type, string User_ID
)
{
string sSql = @"";
sSql += " SELECT A.STOCK_CODE, B.STOCK_NAME, B.STOCK_URL, B.STOCK_CLASS1, B.STOCK_PRICE, B.NAVER_PRICE, ";
sSql += " A.BUY_PRICE, A.SELL_PRICE, A.AVG_PRICE, ";
sSql += " A.STOCK_REMARK, ISNULL(LEN(A.STOCK_REMARK),0) as REMARK_LEN, ";
sSql += " A.BUY_PRICE - B.STOCK_PRICE as BUY_GAP, ";
sSql += " ROUND(CASE A.BUY_PRICE WHEN 0 THEN 0 ELSE (A.BUY_PRICE - B.STOCK_PRICE) / A.BUY_PRICE * 100 END, 2) as BUY_GAP_RATE, ";
sSql += " A.SELL_PRICE - B.STOCK_PRICE as SELL_GAP, ";
sSql += " ROUND(CASE A.SELL_PRICE WHEN 0 THEN 0 ELSE (A.SELL_PRICE - B.STOCK_PRICE) / A.SELL_PRICE * 100 END, 2) as SELL_GAP_RATE, ";
sSql += " ROUND(CASE A.AVG_PRICE WHEN 0 THEN 0 ELSE (B.STOCK_PRICE - A.AVG_PRICE) / A.AVG_PRICE * 100 END,2) as AVG_GAP, ";
sSql += " ( SELECT COUNT(*) REG_CNT ";
sSql += " FROM USER_STOCK C ";
sSql += " WHERE C.STOCK_CODE = A.STOCK_CODE ) REG_CNT, ";
sSql += " ( SELECT ISNULL(AVG(C.BUY_PRICE),0) BUY_AVG ";
sSql += " FROM USER_STOCK C ";
sSql += " WHERE C.STOCK_CODE = A.STOCK_CODE AND C.BUY_PRICE > 0 ) BUY_AVG, ";
sSql += " ( SELECT ISNULL(AVG(C.SELL_PRICE),0) SELL_PRICE ";
sSql += " FROM USER_STOCK C ";
sSql += " WHERE C.STOCK_CODE = A.STOCK_CODE AND C.SELL_PRICE > 0 ) SELL_AVG, ";
sSql += " ( SELECT ISNULL(AVG(C.AVG_PRICE),0) AVG_AVG ";
sSql += " FROM USER_STOCK C ";
sSql += " WHERE C.STOCK_CODE = A.STOCK_CODE AND C.AVG_PRICE > 0) AVG_AVG ";
sSql += " FROM USER_STOCK A";
sSql += " , STOCK_INFO B";
sSql += " WHERE A.USER_ID = " + User_ID;
sSql += " AND B.STOCK_CODE = A.STOCK_CODE ";
sSql += " AND ( B.STOCK_CODE LIKE '%" + Search_name + "%'" + " OR B.STOCK_NAME LIKE '%" + Search_name + "%')";
sSql += " AND ( B.STOCK_CLASS1 LIKE '%" + Search_class + "%'" + " OR B.STOCK_CLASS2 LIKE '%" + Search_class + "%')";
if (!string.IsNullOrEmpty(Search_buy_gap) && Search_buy_gap != "")
{
sSql += " AND (A.BUY_PRICE - B.STOCK_PRICE) / A.BUY_PRICE * 100 <= " + Search_buy_gap;
sSql += " AND A.BUY_PRICE <> 0 ";
}
if (!string.IsNullOrEmpty(Search_sell_gap) && Search_sell_gap != "")
{
sSql += " AND (A.SELL_PRICE - B.STOCK_PRICE) / A.SELL_PRICE * 100 >= " + Search_sell_gap;
sSql += " AND A.SELL_PRICE <> 0 ";
}
sSql += " ORDER BY B.TOTAL_AMT DESC";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
return(user);
}
public static DataSet AvgKospi()
{
string YD_POINT;
// 어제 날짜 지수 가져오기
string sSql = @"";
sSql += " SELECT MAX(INDEX_POINT) INDEX_POINT ";
sSql += " FROM INDEX_POINT A ";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE = ( ";
sSql += " SELECT MAX(INDEX_DATE) INDEX_DATE ";
sSql += " FROM INDEX_POINT A ";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE < ";
sSql += " ( SELECT TOP 1 INDEX_DATE";
sSql += " FROM INDEX_POINT";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE > GETDATE() - 10 ";
sSql += " ORDER BY INDEX_DATE DESC ";
sSql += " ) ";
sSql += " ) ";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
YD_POINT = user.Tables[0].Rows[0][0].ToString();
//평균 지수 가져오기
sSql = @"";
sSql += " SELECT ROUND(AVG(INDEX_POINT),2) AVG_POINT, MIN(CONVERT(CHAR(10), INDEX_DATE, 111)) INDEX_DATE ";
sSql += " FROM ( ";
sSql += " SELECT TOP 60 ISNULL(INDEX_POINT,0) INDEX_POINT, INDEX_DATE";
sSql += " FROM INDEX_POINT";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE > GETDATE() - 120 ";
sSql += " ORDER BY INDEX_DATE DESC ";
sSql += " ) A";
sSql += " UNION ALL";
sSql += " SELECT " + YD_POINT + " AVG_POINT, CONVERT(CHAR(10),GETDATE(),111) INDEX_DATE ";
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
return(user);
}
public static DataSet GetUser(string UserCode)
{
string sSql = @"";
sSql += " SELECT USER_ID, USER_ALIAS, USER_STATUS ";
sSql += " FROM USER_MST";
sSql += " WHERE USER_CODE = '" + UserCode + "'";
DataSet ds;
using (var db = new MSSQLDB())
{
ds = db.Query(sSql);
}
return(ds);
}
public static DataSet GetKospi()
{
string sSql = @"";
sSql += " SELECT TOP 300 INDEX_DATE, ISNULL(INDEX_POINT,0) INDEX_POINT, ISNULL(INDEX_START,0) INDEX_START, ISNULL(INDEX_HIGH,0) INDEX_HIGH, ISNULL(INDEX_LOW,0) INDEX_LOW";
sSql += " FROM INDEX_POINT";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE > GETDATE() - 600 ";
sSql += " ORDER BY INDEX_DATE ";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
return(user);
}
public static DataSet GetAllData()
{
string sSql = @"";
sSql += " SELECT STOCK_CODE, STOCK_NAME, STOCK_URL, TOTAL_AMT, SALES_AMT, BIZ_PROFIT, NET_PROFIT, PER, EST_PER, BIZ_PER, STOCK_PRICE, NAVER_PRICE, ";
sSql += " CAL_PSR, CAL_POR, CAL_PER, BIZ_RATE, NET_RATE, ";
sSql += " SA_Y3, SA_Y2, SA_Y1, SA_Y0, BP_Y3, BP_Y2, BP_Y1, BP_Y0, NP_Y3, NP_Y2, NP_Y1, NP_Y0, ";
sSql += " SA_RATE, BP_RATE, NP_RATE ";
sSql += " FROM STOCK_INFO";
sSql += " ORDER BY TOTAL_AMT DESC";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
return(user);
}
public static bool GetAliasCheck(string UserAlias)
{
string sSql = @"";
sSql += " SELECT COUNT(*) ";
sSql += " FROM USER_MST";
sSql += " WHERE USER_ALIAS = '" + UserAlias + "'";
DataSet ds;
using (var db = new MSSQLDB())
{
ds = db.Query(sSql);
}
if (ds.Tables[0].Rows[0][0].ToString() == "1")
{
return(false);
}
else
{
return(true);
}
}
public static int SetKospiPoint(string Kospi_Point)
{
string sSql;
int result;
string YD_POINT;
// 오늘이 토,일이면 저장하지 않고 패쓰
DateTime nowDt = DateTime.Now;
if (nowDt.DayOfWeek == DayOfWeek.Saturday || nowDt.DayOfWeek == DayOfWeek.Sunday)
{
return(0);
}
// 어제 날짜 지수 가져오기
sSql = @"";
sSql += " SELECT MAX(INDEX_POINT) INDEX_POINT ";
sSql += " FROM INDEX_POINT A ";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE = ( ";
sSql += " SELECT MAX(INDEX_DATE) INDEX_DATE ";
sSql += " FROM INDEX_POINT A ";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE < ";
sSql += " ( SELECT TOP 1 INDEX_DATE";
sSql += " FROM INDEX_POINT";
sSql += " WHERE STOCK_MARKET = 'KOSPI'";
sSql += " AND INDEX_DATE > GETDATE() - 10 ";
sSql += " ORDER BY INDEX_DATE DESC ";
sSql += " ) ";
sSql += " ) ";
DataSet user;
using (var db = new MSSQLDB())
{
user = db.Query(sSql);
}
YD_POINT = user.Tables[0].Rows[0][0].ToString();
sSql = @"";
sSql += " MERGE INTO INDEX_POINT A ";
sSql += " USING ( SELECT CONVERT(CHAR(10),GETDATE(),23) INDEX_DATE, 'KOSPI' STOCK_MARKET ) B";
sSql += " ON (A.STOCK_MARKET = B.STOCK_MARKET AND A.INDEX_DATE = B.INDEX_DATE )";
sSql += " WHEN MATCHED THEN ";
sSql += " UPDATE";
sSql += " SET INDEX_POINT = " + Kospi_Point.ToString();
sSql += " , INDEX_HIGH = " + Kospi_Point.ToString();
sSql += " , INDEX_START = " + YD_POINT;
sSql += " , INDEX_LOW = " + YD_POINT;
sSql += " WHEN NOT MATCHED THEN ";
sSql += " INSERT (STOCK_MARKET, INDEX_DATE, INDEX_POINT, INDEX_START, INDEX_LOW, INDEX_HIGH, CREATE_DATE)";
sSql += " VALUES ('KOSPI', CONVERT(CHAR(10), GETDATE(), 23), "+ Kospi_Point.ToString() + ", " + YD_POINT + ", " + YD_POINT + ", " + Kospi_Point.ToString() + ", GETDATE() );";
using (var db = new MSSQLDB())
{
result = db.Execute(sSql);
}
return(result);
}