public DataTable GetUnderwriters(string codes)
{
//codes=('000001','600000')
CHServiceClient client = new CHServiceClient();
string sql = @"SELECT A.Symbol
,A.Sname
,E.EVENTPARTY3 AS Type
,E.EVENTPARTY5 AS Underwriter
FROM (SELECT * FROM SECURITYCODE WHERE STYPE='EQA') A
INNER JOIN SIAIPO B
ON A.COMPANYCODE=B.COMPANYCODE
LEFT JOIN (SELECT * FROM SIIDATE WHERE DATETYPECODE='0103')C
ON B.COMPANYCODE=C.COMPANYCODE AND B.SIBASEINFOID=C.SIBASEINFOID
LEFT JOIN (
SELECT *
FROM EVENTPARTY
WHERE EVENTPARTY1='01'
AND EVENTPARTY2 IN ('017','037')
) E
ON E.COMPANYCODE=A.COMPANYCODE AND E.EVENTID=C.SIBASEINFOID
WHERE 1=1
AND Symbol IN " + codes + @"
ORDER BY Listdate DESC,Symbol,EVENTPARTY3 DESC
";
DataTable dtOutput = client.ExecuteSQL(sql).Tables[0];
return(dtOutput);
}
public DataTable GetFundBenchmark(string fundcode, string dates)
{
CHServiceClient client = new CHServiceClient();
string sql = @"Select Symbol, Tdate, FundBDY1 AS BMYield, FundBDY2 AS BMIndex
From FundBDY
Where symbol IN ('" + fundcode + @"')
AND Tdate IN (" + dates + @")
Order by symbol, tdate Desc";
DataSet ds = client.ExecuteSQL(sql);
return(ds.Tables[0]);
}
public DataTable GetConvertables()
{
CHServiceClient client = new CHServiceClient();
string sql = @"SELECT S.SYMBOL
,S.SNAME
,F.SYMBOL_COMP_CODE AS PurchaseCodeOnNet
,BONDDT18 AS Stockcode
,S2.Sname AS StockName
,S.EXCHANGE
,DECLAREDATE
,BONDISSUE11 AS PaymentDate
,BONDISSUE27 AS ListDate
,BONDISSUE7 AS IssuePlan
,BONDISSUE9 AS IssuePrice
,BISSUE_BDC35 AS DownLimitOnNet
,BISSUE_BDC36 AS UpLimitOnNet
,BISSUE_BDC44 AS DownLimitUnderNet
,BISSUE_BDC45 AS StepUnderNet
,BISSUE_BDC46 AS UpLimitUnderNet
,BISSUE_BDC10 AS ShareRight --每股优先配售金额
,BISSUE_BDC39 AS WinRateOnNet
,BISSUE_BDC47 AS DownpaymentRate
,BISSUE_BDC47/100 AS DownpaymentRate0
,BISSUE_BDC52 AS WinRateUnderNet
,BISSUE_BDC52/100 AS WinRateUnderNet0
,OEBondChange2 AS ConvertStart
,OEBondChange4 AS StrikePrice
FROM (SELECT * FROM SECURITYCODE WHERE STYPE = 'BDC') S
INNER JOIN (SELECT * FROM BONDDT WHERE BONDDT2='04') A
ON CASE WHEN EXCHANGE = 'CNSESH' THEN BONDDT16 ELSE BONDDT17 END = SYMBOL
INNER JOIN (SELECT * FROM SECURITYCODE WHERE STYPE = 'EQA') S2
ON S2.Symbol = BONDDT18
INNER JOIN BONDISSUE B
ON A.BCODE=B.BCODE
LEFT JOIN BISSUE_BDC C
ON B.BCODE=C.BCODE
LEFT JOIN (Select * FROM OEBondChange WHERE OEBondChange1 ='初始转股价') D
ON A.BCODE=D.BondCode
LEFT JOIN (SELECT * FROM SYMBOL_COMP WHERE SYMBOL_COMP_TYPE ='19') F
ON S.Symbol = F.Symbol
WHERE 1=1
AND DECLAREDATE > (sysdate - 365)
ORDER BY DECLAREDATE DESC";
DataTable dtOutput = client.ExecuteSQL(sql).Tables[0];
return(dtOutput);
}
public DataTable GetIPOCodes()
{
string sql = @"SELECT A.Symbol,A.SName,C3.BEGINDATE AS PurchaseDate
,A.Symbol || ' - ' || A.SName || ' | 询价:' || to_char(C.ENDDATE,'yyyy-MM-dd') || ' - 申购:' || to_char(C3.BEGINDATE,'yyyy-MM-dd') AS Name
FROM (SELECT * FROM SECURITYCODE WHERE STYPE='EQA') A
INNER JOIN SIAIPO B
ON A.COMPANYCODE=B.COMPANYCODE
LEFT JOIN (SELECT * FROM SIIDATE WHERE DATETYPECODE='0103')C
ON B.COMPANYCODE=C.COMPANYCODE AND B.SIBASEINFOID=C.SIBASEINFOID
LEFT JOIN (SELECT * FROM SIIDATE WHERE DATETYPECODE='0117')C3
ON B.COMPANYCODE=C3.COMPANYCODE AND B.SIBASEINFOID=C3.SIBASEINFOID
WHERE 1=1
AND C3.BEGINDATE > (sysdate - 30)
ORDER BY C3.BEGINDATE DESC, A.Symbol
";
CHServiceClient client = new CHServiceClient();
DataTable dtOutput = client.ExecuteSQL(sql).Tables[0];
return(dtOutput);
}
public DataTable GetIPOs(string code)
{
string sqlDateRestrict = " AND C3.BEGINDATE > (sysdate - 100) ";
if (code.Length > 0)
{
sqlDateRestrict = " AND (A.Symbol='" + code + @"' OR SYMBOL_COMP_CODE = '" + code + @"')";
}
CHServiceClient client = new CHServiceClient();
string sql = @"SELECT A.Symbol,A.SName
,F.IndustryChg4 AS Industry
,F.IndustryChg8 AS IndustryCode
,H1.PE2_M1 AS IndPEOnStart
,H2.PE2_M1 AS IndPELatest
,G.Sibaseinfo8 AS IssuePrice
,G.Sibaseinfo9 AS TotalCapitalBeforeIPO
,G.Sibaseinfo10 AS ActualIssuedCapital
,SIAIPO78 AS ActualTransferredCapital
,SIAIPO2 AS PlannedIssueCapital
,SIAIPO77 AS PlannedTransferCapital
,Substr(F.IndustryChg4,1,4) AS Industry0
,SYMBOL_COMP_CODE AS PurchaseCodeOnNet
,C.BEGINDATE AS QuoteStart
,C.ENDDATE AS QuoteEnd
,C2.BEGINDATE AS PurchaseStart
,C2.ENDDATE AS PurchaseEnd
,C3.BEGINDATE AS PurchaseDate
,A.Listdate
,SIAIPO7 AS IssueVolumePlanOnNet
,SIAIPO4 AS IssueVolumePlanUnderNet
,SIAIPO39 AS DownLimitVolUnderNet
,SIAIPO57 AS ProgVolUnderNet
,SIAIPO40 AS UpLimitVolUnderNet
,Substr(D.EVENTPARTY5,1,4) AS PrimaryUnderwriter0
,D.EVENTPARTY5 AS PrimaryUnderwriter
,D.EVENTPARTY6 AS Contacts
,'见推介公告' AS Contacts0
,D.EVENTPARTY7 AS Phones
,'见推介公告' AS Phones0
,E.CNT AS OtherPrimaryUnderwriters
,SIAIPO42 AS UpLimitVolOnNet
,SIAIPO42*10000 AS UpLimitVolOnNet0
FROM (SELECT * FROM SECURITYCODE WHERE STYPE='EQA') A
INNER JOIN
(
SELECT *
FROM IndustryChg A
WHERE PublishDate=
(
SELECT MAX(PublishDate)
FROM IndustryChg
WHERE IndustryChg3='证监会行业分类(2012)'
AND A.CompanyCode=CompanyCode
) AND IndustryChg3='证监会行业分类(2012)'
)F
ON A.COMPANYCODE=F.COMPANYCODE
LEFT JOIN (SELECT * FROM symbol_comp WHERE SYMBOL_COMP_TYPE='05')A2
ON A.SYMBOL=A2.SYMBOL
INNER JOIN SIAIPO B
ON A.COMPANYCODE=B.COMPANYCODE
LEFT JOIN (SELECT * FROM SIIDATE WHERE DATETYPECODE='0103')C
ON B.COMPANYCODE=C.COMPANYCODE AND B.SIBASEINFOID=C.SIBASEINFOID
LEFT JOIN (SELECT * FROM SIIDATE WHERE DATETYPECODE='0109')C2
ON B.COMPANYCODE=C2.COMPANYCODE AND B.SIBASEINFOID=C2.SIBASEINFOID
LEFT JOIN (SELECT * FROM SIIDATE WHERE DATETYPECODE='0117')C3
ON B.COMPANYCODE=C3.COMPANYCODE AND B.SIBASEINFOID=C3.SIBASEINFOID
LEFT JOIN (SELECT * FROM EVENTPARTY WHERE EVENTPARTY1='01' AND EVENTPARTY2 IN ('037'))D
ON D.COMPANYCODE=A.COMPANYCODE AND D.EVENTID=C.SIBASEINFOID
LEFT JOIN (SELECT COMPANYCODE,EVENTID,COUNT(*) AS CNT FROM EVENTPARTY WHERE EVENTPARTY1='01' AND EVENTPARTY2 IN ('017') GROUP BY COMPANYCODE,EVENTID)E----副主承销商
ON E.COMPANYCODE=A.COMPANYCODE AND E.EVENTID=C.SIBASEINFOID
LEFT JOIN (SELECT * FROM SIBASEINFO WHERE FTYPE='01' AND STYPE='EQA') G
ON B.Companycode=G.Companycode AND B.Sibaseinfoid=G.Sibaseinfoid
LEFT JOIN (SELECT * FROM CSI_PE_CSRC WHERE SType='1') H1
ON H1.IndustryCode = F.IndustryChg8 AND H1.TDATE = to_char(C.BEGINDATE,'yyyyMMdd')
LEFT JOIN (SELECT * FROM CSI_PE_CSRC WHERE SType='1') H2
ON H2.IndustryCode = F.IndustryChg8 AND H2.TDATE = to_char(sysdate-1,'yyyyMMdd')
WHERE 1=1
" + sqlDateRestrict + @"
ORDER BY Listdate DESC, C3.BEGINDATE DESC, A.Symbol";
DataTable dtOutput = client.ExecuteSQL(sql).Tables[0];
return(dtOutput);
}