protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string p1date = Convert.ToString(Request.QueryString["p1date"]).Trim();
string p2date = Convert.ToString(Request.QueryString["p2date"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select t.VCH_DT, t.F_CD,decode(t.f_cd, 1, 'ICB Asset Management Company Ltd.', 2, 'ICB AMCL Unit Fund', 3, 'ICB AMCL First Mutual Fund', 4, 'ICB AMCL Pension Holders'' Unit Fund', 5, 'ICB AMCL Islamic Mutual Fund', 6, 'ICB AMCL First NRB Mutual Fund', 7, 'ICB AMCL Second NRB Mutual Fund') fund_name,");
sbMst.Append("t.COMP_CD, c.comp_nm,c.comp_nm || '(' || t.COMP_CD || ')',t.TRAN_TP,decode(t.TRAN_TP, 'C', 'Purchase', 'S', 'Sell', 'B', 'Bonus', 'R', 'Right', 'P', 'IPO') tran_type,t.VCH_NO, t.NO_SHARE, t.RATE, t.COST_RATE, t.CRT_AFT_COM, t.AMOUNT, t.AMT_AFT_COM,t.AMT_AFT_COM / t.NO_SHARE avg_rate,t.STOCK_EX,");
sbMst.Append("decode(t.STOCK_EX, 'D', 'DSE', 'C', 'CSE', ' ALL') stock_name,t.OP_NAME from fund_trans_hb t, comp c where vch_dt between '" + p1date + "' and '" + p2date + "' and c.comp_cd = t.comp_cd and c.cds <> 'Y' and t.f_cd = 1 order by f_cd, tran_tp, t.VCH_DT");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "NonDemateSharesCheck";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\1-8-17\amclpmfs\UI\ReportViewer\Report\crtNonDemateSharesCheck.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CRNonDemateSharesCheck.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_NonDemateSharesCheck.ReportSource = rdoc;
CR_NonDemateSharesCheck.DisplayToolbar = true;
CR_NonDemateSharesCheck.HasExportButton = true;
CR_NonDemateSharesCheck.HasPrintButton = true;
//rdoc.SetParameterValue("prmtransTypeDetais", transTypeDetais);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
string Path = Server.MapPath("Report/CRNonDemateSharesCheck.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_NonDemateSharesCheck.ReportSource = rdoc;
CR_NonDemateSharesCheck.DisplayToolbar = true;
CR_NonDemateSharesCheck.HasExportButton = true;
CR_NonDemateSharesCheck.HasPrintButton = true;
//rdoc.SetParameterValue("prmtransTypeDetais", transTypeDetais);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fundCode = "";
string Fromdate = "";
string Todate = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fundCode = (string)Session["fundCode"];
Fromdate = (string)Session["Fromdate"];
Todate = (string)Session["Todate"];
// balDate = (string)Session["balDate"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append(" select * from (SELECT TO_CHAR(NAV_MASTER.NAVDATE,'DD-MON-YYYY')NAV_DATE,NAV_MASTER.NAVDATE ,NAV_MASTER.NAVTOTALMARKETPRICE, ");
sbMst.Append(" NAV_MASTER.NAVTOTALCOSTPRICE, NAV_MASTER.NAV_PU_CP, NAV_MASTER.NAV_PU_MP, NAV_MASTER.NAV_PU_MP_AFADL, DSE_MKT_INFO.TOTAL_TRADE, ");
sbMst.Append("DSE_MKT_INFO.TOTAL_VOLUME, DSE_MKT_INFO.TOTAL_VOLUME_TK_MN, DSE_MKT_INFO.TOTAL_MARKET_CAP_TK_MN,DSE_MKT_INFO.DSEX_INDEX / 10 AS DSEX_INDEX, ");
sbMst.Append(" DSE_MKT_INFO.DSES_INDEX, DSE_MKT_INFO.DS30_INDEX, DSE_MKT_INFO.DGEN_INDEX FROM nav.NAV_MASTER, nav.DSE_MKT_INFO WHERE NAVFUNDID = " + fundCode + " AND ");
sbMst.Append(" NAV_MASTER.NAVDATE = DSE_MKT_INFO.T_DATE AND NAVDATE ");
sbMst.Append("BETWEEN '" + Fromdate + "' AND '" + Todate + "') ORDER BY NAVDATE ASC ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
if (dtReprtSource.Rows.Count > 0)
{
//Decimal totalInvest = 0;
dtReprtSource.TableName = "ComparisonBetweenNavAndDse";
dtReprtSource.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\crtComparisonBetweenNavAndDseIndex.xsd");
ReportDocument rdoc = new ReportDocument();
string Path = "";
Path = Server.MapPath("Report/crtComparisonBetweenNavAndDseIndexReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_PortfolioSummary.ReportSource = rdoc;
CRV_PortfolioSummary.DisplayToolbar = true;
CRV_PortfolioSummary.HasExportButton = true;
CRV_PortfolioSummary.HasPrintButton = true;
// rdoc.SetParameterValue("prmbalDate", balDate);
// rdoc.SetParameterValue("prmTotalInvest", totalInvest);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select fc.f_cd, TO_CHAR( bal_dt, 'DD.MM.YYYY') AS BalanceDate ,TO_CHAR( mprice_dt, 'DD.MM.YYYY') AS MarketPriceDate ,f_name from fund_control fc, fund f where fc.f_cd = f.f_cd order by fc.f_cd");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "BalanceCheckDataSet";
//dtReprtSource.WriteXmlSchema(@"D:\officialProject\1-8-17\amclpmfs\UI\ReportViewer\Report\crtBalanceCheck.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CRBalanceCheck.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_BalanceCheck.ReportSource = rdoc;
CR_BalanceCheck.DisplayToolbar = true;
CR_BalanceCheck.HasExportButton = true;
CR_BalanceCheck.HasPrintButton = true;
//rdoc.SetParameterValue("prmtransTypeDetais", transTypeDetais);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string tranDate = Request.QueryString["transactionDate"].ToString();
string percentageCheck = Request.QueryString["percentageCheck"].ToString();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append(" SELECT ASSET_VALUE.F_NAME, COMP.COMP_NM, PFOLIO_BK.SECT_MAJ_NM, PFOLIO_BK.TOT_NOS, ");
sbMst.Append(" PFOLIO_BK.TCST_AFT_COM, ROUND(PFOLIO_BK.TCST_AFT_COM / PFOLIO_BK.TOT_NOS, 2) AS COST_RT_PER_SHARE, ");
sbMst.Append(" NVL(PFOLIO_BK.DSE_RT, 0) AS DSE_RT, NVL(PFOLIO_BK.CSE_RT, 0) AS CSE_RT, ROUND(PFOLIO_BK.ADC_RT, 2) ");
sbMst.Append(" AS AVG_RATE, ROUND(PFOLIO_BK.TOT_NOS * PFOLIO_BK.ADC_RT, 2) AS TOT_MARKET_PRICE, ");
sbMst.Append(" ROUND(ROUND(PFOLIO_BK.ADC_RT, 2) - ROUND(PFOLIO_BK.TCST_AFT_COM / PFOLIO_BK.TOT_NOS, 2), 2) AS RATE_DIFF, ");
sbMst.Append(" ROUND(ROUND(PFOLIO_BK.TOT_NOS * PFOLIO_BK.ADC_RT, 2) - PFOLIO_BK.TCST_AFT_COM, 2) ");
sbMst.Append(" AS APPRICIATION_ERROTION, PFOLIO_BK.BAL_DT_CTRL, ASSET_VALUE.ASSET_VALUE, ");
sbMst.Append(" ROUND(PFOLIO_BK.TCST_AFT_COM / ASSET_VALUE.ASSET_VALUE * 100, 2) AS HOLDING_PERCENTAGE_OF_ASSET, ");
sbMst.Append(" ROUND(PFOLIO_BK.TOT_NOS / COMP.NO_SHRS * 100, 2) AS PERCENTAGE_OF_PAIDUP ");
sbMst.Append(" FROM PFOLIO_BK INNER JOIN ");
sbMst.Append(" ASSET_VALUE ON PFOLIO_BK.F_CD = ASSET_VALUE.F_CD INNER JOIN ");
sbMst.Append(" COMP ON PFOLIO_BK.COMP_CD = COMP.COMP_CD WHERE ");
if (percentageCheck != "")
{
sbMst.Append(" (ROUND(PFOLIO_BK.TCST_AFT_COM / ASSET_VALUE.ASSET_VALUE * 100, 2) >=" + percentageCheck + ") and ");
}
sbMst.Append(" (PFOLIO_BK.BAL_DT_CTRL = '" + Convert.ToDateTime(Request.QueryString["transactionDate"]).ToString("dd-MMM-yyyy") + "') ");
sbMst.Append(" ORDER BY PFOLIO_BK.SECT_MAJ_NM, COMP.COMP_NM, PFOLIO_BK.F_CD ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "AssetPercentageCheck";
if (dtReprtSource.Rows.Count > 0)
{
//dtReprtSource.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtmAssetPercentageCheckReport.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = Server.MapPath("Report/AssetPercentageCheckReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_AssetPercentageCheck.ReportSource = rdoc;
rdoc.SetParameterValue("prmtransactionDate", tranDate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
CRV_AssetPercentageCheck.DisplayToolbar = true;
CRV_AssetPercentageCheck.HasExportButton = true;
CRV_AssetPercentageCheck.HasPrintButton = true;
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fundcode = "";
string companycode = "";
string blncdate = "";
string CompanyName = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fundcode = (string)Session["fundcode"];
companycode = (string)Session["companycode"];
blncdate = (string)Session["blncdate"];
companycode = (string)Session["companycode"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select b.f_name, a.folio_no, a.cert_no, a.dmat_no, a.dmat_dt, a.allot_no, a.dis_no_fm,a.dis_no_to, a.no_shares, a.sp_date, substr(a.sh_type,1,1) sh_tp ");
sbMst.Append(" from shr_dmat_fi a, fund b where a.comp_cd = '" + companycode + "' and a.f_cd =b.f_cd and a.posted is null and a.dmat_dt ='" + blncdate + "' and a.f_cd = '" + fundcode + "' ");
sbMst.Append(" order by a.dmat_dt, c_dt, cert_no ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "DematListReport";
// dtReprtSource.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtPortfolioWithNonListedReport.xsd");
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\4-5-2017\amclpmfs\UI\ReportViewer\Report\CR_DematListReport.xsd");
ReportDocument rdoc = new ReportDocument();
string Path = "";
Path = Server.MapPath("Report/crtDematListReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_DematListReport.ReportSource = rdoc;
CRV_DematListReport.DisplayToolbar = true;
CRV_DematListReport.HasExportButton = true;
CRV_DematListReport.HasPrintButton = true;
rdoc.SetParameterValue("prmFromDate", CompanyName);
rdoc.SetParameterValue("prmFundCode", fundcode);
rdoc.SetParameterValue("prmcompanycode", companycode);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
string weekEndDate = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
weekEndDate = (string)Session["weekEndDate"];
}
DataTable dtWeeklyReportSEC = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT EQUITY.F_NAME, ROUND(DEBT.INVEST_DEBT / 1000000, 2) AS DEBT, ROUND(EQUITY.INVEST_EQUITY / 1000000, 2) AS EQUITY ");
sbMst.Append("FROM (SELECT FUND.F_CD, FUND.F_NAME, SUM(PFOLIO_BK.TCST_AFT_COM) AS INVEST_EQUITY ");
sbMst.Append("FROM FUND INNER JOIN ");
sbMst.Append("PFOLIO_BK ON FUND.F_CD = PFOLIO_BK.F_CD ");
sbMst.Append("WHERE (PFOLIO_BK.SECT_MAJ_CD <> 89) AND (PFOLIO_BK.BAL_DT_CTRL = '" + weekEndDate + "') AND (FUND.F_CD NOT IN (1,3,5,6,18)) ");
sbMst.Append("GROUP BY FUND.F_NAME, FUND.F_CD) EQUITY LEFT OUTER JOIN ");
sbMst.Append("(SELECT FUND_1.F_CD, FUND_1.F_NAME, SUM(PFOLIO_BK_1.TCST_AFT_COM) AS INVEST_DEBT ");
sbMst.Append("FROM FUND FUND_1 INNER JOIN ");
sbMst.Append(" PFOLIO_BK PFOLIO_BK_1 ON FUND_1.F_CD = PFOLIO_BK_1.F_CD ");
sbMst.Append("WHERE (PFOLIO_BK_1.SECT_MAJ_CD = 89) AND (PFOLIO_BK_1.BAL_DT_CTRL = '" + weekEndDate + "') AND (FUND_1.F_CD NOT IN (1,3,5,6,18)) ");
sbMst.Append("GROUP BY FUND_1.F_NAME, FUND_1.F_CD) DEBT ON EQUITY.F_CD = DEBT.F_CD ");
sbMst.Append("ORDER BY EQUITY.F_CD ");
sbMst.Append(sbfilter.ToString());
dtWeeklyReportSEC = commonGatewayObj.Select(sbMst.ToString());
dtWeeklyReportSEC.TableName = "WeeklyReportSEC";
if (dtWeeklyReportSEC.Rows.Count > 0)
{
//dtWeeklyReportSEC.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtmWeeklyReportSEC.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = Server.MapPath("Report/crtmWeeklyReportSEC.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtWeeklyReportSEC);
CRV_SEC_Weekly.ReportSource = rdoc;
CRV_SEC_Weekly.DisplayToolbar = true;
CRV_SEC_Weekly.HasExportButton = true;
CRV_SEC_Weekly.HasPrintButton = true;
rdoc.SetParameterValue("prmWeekEndDate", weekEndDate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fundcode = "";
string companycode = "";
// string CompanyName = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fundcode = (string)Session["fundcode"];
companycode = (string)Session["companycode"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT a.COMP_CD,B.COMP_NM, a.PSDR_NO,a. F_CD, C.F_NAME, a.ALLOT_NO, a.NO_SHARES, substr( a.SH_TYPE,1,1) SH_TYPE, a.OM_LOT, a.SP_RATE, a.SP_DATE, a.HOWLA_NO, a.MV_DATE, a.REF_NO, a.DIS_NO_FM, a.DIS_NO_TO, a.FOLIO_NO, ");
sbMst.Append(" a.CERT_NO, a.BK_CD, a.POSTED, a.OP_NAME, a.C_DT, a.C_DATE FROM INVEST.PSDR_FI a,COMP b,FUND c where a.comp_cd = '" + companycode + "' and a.f_cd =c.f_cd and a.posted= 'A' and A.FOLIO_NO is not null and a.comp_cd=B.COMP_CD and a.f_cd = '" + fundcode + "' ");
sbMst.Append(" ORDER BY a.C_DT DESC ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "PSDRListReport";
// dtReprtSource.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\crtPSDRListReport.xsd");
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\4-5-2017\amclpmfs\UI\ReportViewer\Report\CR_DematListReport.xsd");
ReportDocument rdoc = new ReportDocument();
string Path = "";
Path = Server.MapPath("Report/crtPSDRListReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_PSDRListReport.ReportSource = rdoc;
CRV_PSDRListReport.DisplayToolbar = true;
CRV_PSDRListReport.HasExportButton = true;
CRV_PSDRListReport.HasPrintButton = true;
//rdoc.SetParameterValue("prmFromDate", CompanyName);
rdoc.SetParameterValue("prmFundCode", fundcode);
rdoc.SetParameterValue("prmcompanycode", companycode);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string transType = Convert.ToString(Request.QueryString["transType"]).Trim();
DataTable dtReprtSource = new DataTable();
DataTable dtReprtSource1 = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
//sbMst.Append("SELECT VoucherDate, FundName, CompanyCode, TransactionType, Ammount FROM(SELECT TO_CHAR(VCH_DT, 'DD.MM.YYYY') AS VoucherDate, TRUNC(F_CD) AS FundName, TRUNC(COMP_CD) AS CompanyCode, TRAN_TP AS TransactionType, NO_SHARE * RATE AS Ammount FROM FUND_TRANS_HB WHERE(VCH_DT BETWEEN TO_DATE('15-JAN-10', 'DD-MON-YY') AND TO_DATE('17-JAN-10', 'DD-MON-YY'))) fundtrans");
//if (transType !=null )
//{
// sbMst.Append(" where fundtrans.TransactionType='"+transType+"'");
//}
sbMst.Append("SELECT * from fund_trans");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "FUND_TRANS_HB";
StringBuilder sbMst1 = new StringBuilder();
sbMst1.Append("select * from fund_trans_hb");
//sbMst1.Append(sbMst1.ToString());
dtReprtSource1 = commonGatewayObj.Select(sbMst1.ToString());
dtReprtSource1.TableName = "FUND_TRANS_HB2";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\2-13-2017\amclpmfs\UI\ReportViewer\Report\crttestFUND_TRANS_HB.xsd");
// "D:\officialProject\1-8-17\amclpmfs\UI\ReportViewer\Report\crttestFUND_TRANS_HB.xsd"
if (dtReprtSource.Rows.Count > 0 && dtReprtSource1.Rows.Count > 0)
{
string Path = Server.MapPath("Report/testFUND_TRANS_HB.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
rdoc.SetDataSource(dtReprtSource1);
CRV_FundTransactionHB.ReportSource = rdoc;
CRV_FundTransactionHB.DisplayToolbar = true;
CRV_FundTransactionHB.HasExportButton = true;
CRV_FundTransactionHB.HasPrintButton = true;
//rdoc.SetParameterValue("prmtransTypeDetais", transTypeDetais);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string fundcode = Convert.ToString(Request.QueryString["fundcode"]).Trim();
string balancedate = Convert.ToString(Request.QueryString["balancedate"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
string fundname = "";
string strQuerFund = "select F_CD,F_NAME from fund where f_cd=" + fundcode;
DataTable dtfund = commonGatewayObj.Select(strQuerFund);
if (!dtfund.Rows[0].IsNull("F_NAME"))
{
fundname = dtfund.Rows[0]["F_NAME"].ToString();
// invDate = Convert.ToDateTime(dt.Rows[0]["vch_dt"].ToString()).ToString("dd-MMM-yyyy");
}
else
{
fundname = "";
}
sbfilter.Append(" ");
sbMst.Append("SELECT SECT_MAJ.SECT_MAJ_NM, SUM(FUND_TRANS_HB.AMT_AFT_COM) AS SELL_AMOUNT FROM FUND_TRANS_HB INNER JOIN COMP ON FUND_TRANS_HB.COMP_CD = COMP.COMP_CD INNER JOIN ");
sbMst.Append(" SECT_MAJ ON COMP.SECT_MAJ_CD = SECT_MAJ.SECT_MAJ_CD WHERE (FUND_TRANS_HB.F_CD = " + fundcode + ") AND (FUND_TRANS_HB.VCH_DT <= '" + Convert.ToDateTime(balancedate).ToString("dd-MMM-yyyy") + "') AND (FUND_TRANS_HB.TRAN_TP IN ('S')) GROUP BY SECT_MAJ.SECT_MAJ_NM ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "SectorWiseSelAmount";
// dtReprtSource.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\crtSectorWiseSelAmount.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crtSectorWiseSelAmount.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_crtSectorWiseSelAmount.ReportSource = rdoc;
CR_crtSectorWiseSelAmount.DisplayToolbar = true;
CR_crtSectorWiseSelAmount.HasExportButton = true;
CR_crtSectorWiseSelAmount.HasPrintButton = true;
rdoc.SetParameterValue("fundName", fundname);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
DataTable dtTransactionDate = commonGatewayObj.Select("SELECT DISTINCT VCH_DT FROM FUND_TRANS_HB WHERE VCH_DT BETWEEN '" + Convert.ToDateTime(Request.QueryString["howlaDateFrom"]).ToString("dd-MMM-yyyy") + "' AND '" + Convert.ToDateTime(Request.QueryString["howlaDateTo"]).ToString("dd-MMM-yyyy") + "' AND TRAN_TP IN ('C','S')");
string howlaDateFrom = Convert.ToDateTime(Request.QueryString["howlaDateFrom"]).ToString("dd-MMM-yyyy");
string howlaDateTo = Convert.ToDateTime(Request.QueryString["howlaDateTo"]).ToString("dd-MMM-yyyy");
if (dtTransactionDate.Rows.Count > 0)
{
DataTable dtReport = getReportTable();
DataRow drReport;
for (int loop = 0; loop < dtTransactionDate.Rows.Count; loop++)
{
drReport = dtReport.NewRow();
drReport["TRANSACTION_DATE"] = Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy");
drReport["AMCL_BUY"] = SalePurchase("SELECT NVL(ROUND(SUM(AMT_AFT_COM)/10000000,2),0)AS SP_VALUE FROM FUND_TRANS_HB WHERE F_CD IN (1) AND TRAN_TP='C' AND VCH_DT = '" + Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy") + "'");
drReport["AMCL_SALE"] = SalePurchase("SELECT NVL(ROUND(SUM(AMT_AFT_COM)/10000000,2),0)AS SP_VALUE FROM FUND_TRANS_HB WHERE F_CD IN (1) AND TRAN_TP='S' AND VCH_DT = '" + Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy") + "'");
drReport["UNIT_BUY"] = SalePurchase("SELECT NVL(ROUND(SUM(AMT_AFT_COM)/10000000,2),0)AS SP_VALUE FROM FUND_TRANS_HB WHERE F_CD IN (2,4) AND TRAN_TP='C' AND VCH_DT = '" + Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy") + "'");
drReport["UNIT_SALE"] = SalePurchase("SELECT NVL(ROUND(SUM(AMT_AFT_COM)/10000000,2),0)AS SP_VALUE FROM FUND_TRANS_HB WHERE F_CD IN (2,4) AND TRAN_TP='S' AND VCH_DT = '" + Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy") + "'");
drReport["MF_BUY"] = SalePurchase("SELECT NVL(ROUND(SUM(AMT_AFT_COM)/10000000,2),0)AS SP_VALUE FROM FUND_TRANS_HB WHERE F_CD NOT IN (1,2,4) AND TRAN_TP='C' AND VCH_DT = '" + Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy") + "'");
drReport["MF_SALE"] = SalePurchase("SELECT NVL(ROUND(SUM(AMT_AFT_COM)/10000000,2),0)AS SP_VALUE FROM FUND_TRANS_HB WHERE F_CD NOT IN (1,2,4) AND TRAN_TP='S' AND VCH_DT = '" + Convert.ToDateTime(dtTransactionDate.Rows[loop]["VCH_DT"].ToString()).ToString("dd-MMM-yyyy") + "'");
dtReport.Rows.Add(drReport);
}
dtReport.TableName = "Transaction";
//dtReport.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtmSalePurchaseReport.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = Server.MapPath("Report/SalePurchaseReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReport);
CRV_SalePurchaseSummary.ReportSource = rdoc;
CRV_SalePurchaseSummary.DisplayToolbar = true;
CRV_SalePurchaseSummary.HasExportButton = true;
CRV_SalePurchaseSummary.HasPrintButton = true;
rdoc.SetParameterValue("prmFromDate", howlaDateFrom);
rdoc.SetParameterValue("prmToDate", howlaDateTo);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
string Fromdate = "";
string Todate = "";
string fundCode = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
Fromdate = (string)Session["Fromdate"];
Todate = (string)Session["Todate"];
fundCode = (string)Session["fundCodes"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT fund.f_name,fund_trans_hb.vch_dt, sum(no_share) as No_Of_Share,decode(tran_tp,'C',SUM(AMT_AFT_COM),'I',sum(amt_aft_com),'R',sum(amt_aft_com),0) as SALE, ");
sbMst.Append(" sum(crt_aft_com * no_share) as COST ,SUM(AMT_AFT_COM) -sum(crt_aft_com *no_share) as profit, tran_tp, decode(stock_ex,'C','CSE','D','DSE','Local') stock_ex, ");
sbMst.Append(" decode(tran_tp,'S',' Credit ','C',' Debit ') db_cr FROM FUND_TRANS_HB,fund WHERE fund_trans_hb.f_cd=fund.f_cd and stock_ex in ('D','C','A') and ");
sbMst.Append(" VCH_DT BETWEEN '" + Fromdate + "' AND '" + Todate + "' and fund_trans_hb.F_CD='" + fundCode + "' GROUP BY fund.f_name, vch_dt ,tran_tp,stock_ex order by vch_dt,stock_ex desc ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "ReconCilationDRandCRReport";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\4-5-2017\amclpmfs\UI\ReportViewer\Report\CR_ReconCilationDRandCRReportVeiwer.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CR_ReconCilationDRandCRReportVeiwer.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_ReconCilationDRandCRReportVeiwer.ReportSource = rdoc;
CR_ReconCilationDRandCRReportVeiwer.DisplayToolbar = true;
CR_ReconCilationDRandCRReportVeiwer.HasExportButton = true;
CR_ReconCilationDRandCRReportVeiwer.HasPrintButton = true;
rdoc.SetParameterValue("Fromdate", Fromdate);
rdoc.SetParameterValue("Todate", Todate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
string Fromdate = "";
string Todate = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
Fromdate = (string)Session["Fromdate"];
Todate = (string)Session["Todate"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select b.COMP_NM, a.TRAN_DATE, a.AVG_RT,a.DSE_RT,a.CSE_RT,a.CSE_DT,a.DSE_HIGH,a.DSE_LOW,a.DSE_OPEN from ");
sbMst.Append("(SELECT COMP_CD, TRAN_DATE, AVG_RT, DSE_RT, CSE_RT, CSE_DT, DSE_HIGH, DSE_LOW, DSE_OPEN ");
sbMst.Append(" FROM INVEST.MARKET_PRICE ) a inner join comp b on A.COMP_CD=B.COMP_CD where a.TRAN_DATE ");
sbMst.Append(" BETWEEN '" + Fromdate + "' and '" + Todate + "' ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "MarketPriceReportViewer";
dtReprtSource.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\CR_MarketPriceReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CR_MarketPriceReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_ReceivablePayableDSEandCSESeparateReport.ReportSource = rdoc;
CR_ReceivablePayableDSEandCSESeparateReport.DisplayToolbar = true;
CR_ReceivablePayableDSEandCSESeparateReport.HasExportButton = true;
CR_ReceivablePayableDSEandCSESeparateReport.HasPrintButton = true;
rdoc.SetParameterValue("Fromdate", Fromdate);
rdoc.SetParameterValue("Todate", Todate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
string Fromdate = "";
string Todate = "";
string fundCode = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
Fromdate = (string)Session["Fromdate"];
Todate = (string)Session["Todate"];
fundCode = (string)Session["fundCodes"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select t.f_cd,vch_dt,f.f_name fund_name, stock_ex,sum(decode(TRAN_TP,'C',AMT_AFT_COM,0)) buy,sum(decode(TRAN_TP,'S',AMT_AFT_COM,0)) Sale, ");
sbMst.Append(" sum(decode(TRAN_TP,'C', -AMT_AFT_COM,'S',AMT_AFT_COM,0)) Receivable from fund_trans_hb t, fund f where vch_dt between '" + Fromdate + "' and '" + Todate + "'");
sbMst.Append(" and t.f_cd='" + fundCode + "' and f.f_cd=t.f_cd and tran_tp<>'B' and no_share>=1 and stock_ex in ('D','C') group by f.f_name,t.f_cd,vch_dt, stock_ex order by vch_dt ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "ReceivablePayableDSEandCSESeparate";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\4-5-2017\amclpmfs\UI\ReportViewer\Report\CR_ReceivablePayableFundwiseReportVeiwer.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CR_ReceivablePayableFundwiseReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_ReceivablePayableFundwiseReportVeiwer.ReportSource = rdoc;
CR_ReceivablePayableFundwiseReportVeiwer.DisplayToolbar = true;
CR_ReceivablePayableFundwiseReportVeiwer.HasExportButton = true;
CR_ReceivablePayableFundwiseReportVeiwer.HasPrintButton = true;
rdoc.SetParameterValue("Fromdate", Fromdate);
rdoc.SetParameterValue("Todate", Todate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
//Returns SQL query based on index parameter
public string readSqlQuery(ReportDocument rd)
{
//Check if report is loaded
if (!rd.IsLoaded)
{
MessageBox.Show("Report not loaded. Please select a Crystal report (.rpt)", "Crystal Reports Search",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
searchBtn.Text = "Search";
searchBtn.Enabled = true;
return(string.Empty);
}
//Get report property information
PropertyInfo pi = rd.Database.Tables.GetType().GetProperty("RasTables", BindingFlags.NonPublic | BindingFlags.Instance);
StringBuilder SQLqueries = new StringBuilder();
String delimiter = Environment.NewLine + Environment.NewLine;
try
{
PropertyInfo[] properties = rptDoc.GetType().GetProperties();
}
catch (Exception ex)
{
MessageBox.Show("Exception occured:" + Environment.NewLine + ex.ToString(), "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
logException(ex);
}
getNumberOfCommands();
//Return SQL query in all found commands
for (int i = 0; i < numberOfCommands; i++)
{
try
{
SQLqueries.Append(
((dynamic)pi.GetValue(rd.Database.Tables, pi.GetIndexParameters()))[i].CommandText);
SQLqueries.Append(delimiter);
}
catch (Exception ex)
{
logException(ex);
}
}
return(SQLqueries.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
string Fromdate = "";
string Todate = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
Fromdate = (string)Session["Fromdate"];
Todate = (string)Session["Todate"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT t.F_CD, f.f_name fund_name, SUM(t.AMT_AFT_COM) as SALE, sum(t.no_share) as No_Of_Share, sum(crt_aft_com * no_share) as COST, ");
sbMst.Append(" SUM(AMT_AFT_COM) - sum(crt_aft_com * no_share) as profit, decode(stock_ex, 'D', 'DSE', 'C', 'CSE','OTHERS') stock_ex FROM FUND_TRANS_HB t, fund f ");
sbMst.Append(" WHERE t.TRAN_TP = 'S' AND VCH_DT BETWEEN '" + Fromdate + "' AND '" + Todate + "' and stock_ex in ('D', 'C','A') and f.f_cd = t.f_cd GROUP BY t.F_CD,stock_ex,f.f_name order by t.f_cd asc, stock_ex desc ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "CapitalGainSummaryStockWise";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\4-5-2017\amclpmfs\UI\ReportViewer\Report\CR_CapitalGainSummaryStockWiseReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CR_CapitalGainSummaryStockWiseReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_CapitalGainSummaryStockWiseReport.ReportSource = rdoc;
CR_CapitalGainSummaryStockWiseReport.DisplayToolbar = true;
CR_CapitalGainSummaryStockWiseReport.HasExportButton = true;
CR_CapitalGainSummaryStockWiseReport.HasPrintButton = true;
rdoc.SetParameterValue("Fromdate", Fromdate);
rdoc.SetParameterValue("Todate", Todate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
DataTable dtSelectionScale = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT AMCL_EMP_SALARY_SELECTION.ID, EMP_INFO.NAME, DESIGNATION.DESIG, EMP_INFO.ICB_ID, ");
sbMst.Append(" AMCL_EMP_SALARY_SELECTION.MONTH, AMCL_EMP_SALARY_SELECTION.BASIC_AS_30JUN09, ");
sbMst.Append(" AMCL_EMP_SALARY_SELECTION.BASIC_CURRENT, AMCL_EMP_SALARY_SELECTION.HOUSE_RENT_AS_30JUN09, ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION.HOUSE_RENT_NEW, AMCL_EMP_SALARY_SELECTION.BASIC_AS_PREV, ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION.FIXATION_BASIC, AMCL_EMP_SALARY_SELECTION.ALLOWANCE_BASIC_DIFF, ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION.ALLOWANCE_HOUSE_RENT, AMCL_EMP_SALARY_SELECTION.ALLOWANCE_PENSION, ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION.ALLOWANCE_DEPUTATION, AMCL_EMP_SALARY_SELECTION.GROSS_ALLOWANCE, ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION.DEDUCTION_PF_OWN_CON, AMCL_EMP_SALARY_SELECTION.DEDUCTION_PENSION, ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION.GROSS_DEDUCTION, AMCL_EMP_SALARY_SELECTION.ARREAR_AMOUNT,AMCL_EMP_SALARY_SELECTION.NET_PAYABLE ");
sbMst.Append("FROM EMP_INFO INNER JOIN ");
sbMst.Append("DESIGNATION ON EMP_INFO.RANK = DESIGNATION.RANK INNER JOIN ");
sbMst.Append("AMCL_EMP_SALARY_SELECTION ON EMP_INFO.ID = AMCL_EMP_SALARY_SELECTION.ID ");
sbMst.Append(sbfilter.ToString());
dtSelectionScale = commonGatewayObj.Select(sbMst.ToString());
dtSelectionScale.TableName = "SelectionScale";
if (dtSelectionScale.Rows.Count > 0)
{
//dtSelectionScale.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtmSelectionScaleReport.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = Server.MapPath("Report/SelectionScaleReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtSelectionScale);
CrystalReportViewerSelectionScaleCalculation.ReportSource = rdoc;
CrystalReportViewerSelectionScaleCalculation.DisplayToolbar = true;
CrystalReportViewerSelectionScaleCalculation.HasExportButton = true;
CrystalReportViewerSelectionScaleCalculation.HasPrintButton = true;
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
string fundcode = Request.QueryString["fundcode"].ToString();
string Fromdate = Request.QueryString["Fromdate"].ToString();
string Todate = Request.QueryString["Todate"].ToString();
string transtype = Request.QueryString["transtype"].ToString();
sbfilter.Append(" ");
sbMst.Append("SELECT f.comp_cd, c.comp_nm, fund.f_name fund_name, SUM(AMT_AFT_COM) as purchase, sum(no_share) as No_Of_Share,Round(SUM(CRT_AFT_COM * NO_SHARE),2) AS COST_PRICE," +
" ROUND(SUM(AMT_AFT_COM) / sum(no_share),2) as sale_rate, Round(SUM(CRT_AFT_COM * NO_SHARE) / sum(no_share),2) as cost_rate, ROUND(SUM(AMT_AFT_COM) - SUM(CRT_AFT_COM * NO_SHARE)) PROFIT " +
" FROM FUND_TRANS_HB f, comp c, fund WHERE F.TRAN_TP = '" + transtype + "' AND VCH_DT BETWEEN '" + Fromdate + "'AND '" + Todate + "' and f.f_cd ='" + fundcode + "' and c.comp_cd = f.comp_cd and f.f_cd = fund.f_cd GROUP BY fund.f_name, f.comp_cd, c.comp_nm");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "CapitalGainCompanyWiseNewReport";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\2-13-2017\amclpmfs\UI\ReportViewer\Report\CR_CapitalGainCompanyWiseNewReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
//string Path = Server.MapPath("~/Report/crtNegativeBalanceCheckReport.rpt");
string Path = Server.MapPath("Report/CR_CapitalGainCompanyWiseNewReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_CapitalGainCompanyWiseNewReport.ReportSource = rdoc;
CR_CapitalGainCompanyWiseNewReport.DisplayToolbar = true;
CR_CapitalGainCompanyWiseNewReport.HasExportButton = true;
CR_CapitalGainCompanyWiseNewReport.HasPrintButton = true;
rdoc.SetParameterValue("Fromdate", Fromdate);
rdoc.SetParameterValue("Todate", Todate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string compcode = Request.QueryString["companycode"].ToString();
string p1date = Convert.ToString(Request.QueryString["p1date"]).Trim();
string p2date = Convert.ToString(Request.QueryString["p2date"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select * from (SELECT t.F_CD, f.f_name fund_name,SUM(AMT_AFT_COM) as SALE, sum(no_share) as No_Of_Share, sum(crt_aft_com * no_share) as COST ,SUM(AMT_AFT_COM) - sum(crt_aft_com *no_share) as profit,");
sbMst.Append(" t.comp_cd, c.comp_nm FROM FUND_TRANS_HB t , comp c, fund f WHERE f.f_cd=t.f_cd and t.TRAN_TP= 'S' AND VCH_DT BETWEEN '" + p1date + "' AND '" + p2date + "' and c.comp_cd=t.comp_cd and no_share>=1");
sbMst.Append(" GROUP BY t.F_CD, t.comp_cd,c.comp_nm,f.f_name) tab1 where tab1.comp_cd='" + compcode + "'");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "CapitalGainCompanyWiseReport";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\2-13-2017\amclpmfs\UI\ReportViewer\Report\crtCapitalGainCompanyWiseReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CRCapitalGainCompanyWiseReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRCapitalGainCompanyWiseReport.ReportSource = rdoc;
CRCapitalGainCompanyWiseReport.DisplayToolbar = true;
CRCapitalGainCompanyWiseReport.HasExportButton = true;
CRCapitalGainCompanyWiseReport.HasPrintButton = true;
rdoc.SetParameterValue("p1date", p1date);
rdoc.SetParameterValue("p2date", p2date);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string p1date = Convert.ToString(Request.QueryString["p1date"]).Trim();
string p2date = Convert.ToString(Request.QueryString["p2date"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT fund_trans_hb.F_CD, fund_trans_hb.vch_dt, f.f_name fund_name,SUM(AMT_AFT_COM) as SALE, sum(no_share) as No_Of_Share,");
sbMst.Append("sum(crt_aft_com * no_share) as COST ,SUM(AMT_AFT_COM) -sum(crt_aft_com *no_share) as profit FROM FUND_TRANS_HB, pfolio_bk, fund f ");
sbMst.Append("WHERE TRAN_TP='S' AND fund_trans_hb.f_cd=f.f_cd and VCH_DT BETWEEN '" + p1date + "' AND '" + p2date + "'");
sbMst.Append("and bal_dt_ctrl='" + p2date + "' and fund_trans_hb.F_CD=pfolio_bk.f_cd and fund_trans_hb.comp_CD=pfolio_bk.comp_cd GROUP BY fund_trans_hb.F_CD , pfolio_bk.comp_cd , vch_dt, f.f_name order by F_CD");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "CapitalgainSummeryDateWise";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\2-13-2017\amclpmfs\UI\ReportViewer\Report\crtCapitalgainSummeryDateWise.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CRCapitalgainSummeryDateWise.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_CapitalGainSummeryDateWiseReport.ReportSource = rdoc;
CR_CapitalGainSummeryDateWiseReport.DisplayToolbar = true;
CR_CapitalGainSummeryDateWiseReport.HasExportButton = true;
CR_CapitalGainSummeryDateWiseReport.HasPrintButton = true;
rdoc.SetParameterValue("p1date", p1date);
rdoc.SetParameterValue("p2date", p2date);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
string Fromdate = Request.QueryString["Fromdate"].ToString();
string Todate = Request.QueryString["Todate"].ToString();
sbMst.Append("SELECT SUM(AMT_AFT_COM) as SALE, sum(no_share) as No_Of_Share, sum(crt_aft_com * no_share) as COST ,SUM(AMT_AFT_COM) - sum(crt_aft_com *no_share) as profit," +
" FUND_TRANS_HB.comp_cd, c.comp_nm FROM FUND_TRANS_HB , comp c WHERE FUND_TRANS_HB.TRAN_TP= 'S' AND VCH_DT BETWEEN '" + Fromdate + "' AND '" + Todate + "' " +
" and c.comp_cd=fund_trans_hb.comp_cd and no_share>=1 GROUP BY fund_trans_hb.comp_cd,c.comp_nm order by SUM(AMT_AFT_COM) - sum(crt_aft_com *no_share) desc ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "CapitalGainSummeryCompanyWiseDscreport";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\2-13-2017\amclpmfs\UI\ReportViewer\Report\CapitalGainSummeryCompanyWiseDscreport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
//string Path = Server.MapPath("~/Report/crtNegativeBalanceCheckReport.rpt");
string Path = Server.MapPath("Report/CapitalGainSummeryCompanyWiseDscreport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_CapitalGainSummeryCompanyWiseDscreportViwerReport.ReportSource = rdoc;
CR_CapitalGainSummeryCompanyWiseDscreportViwerReport.DisplayToolbar = true;
CR_CapitalGainSummeryCompanyWiseDscreportViwerReport.HasExportButton = true;
CR_CapitalGainSummeryCompanyWiseDscreportViwerReport.HasPrintButton = true;
rdoc.SetParameterValue("Fromdate", Fromdate);
rdoc.SetParameterValue("Todate", Todate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string fundcode = Convert.ToString(Request.QueryString["fundcode"]).Trim();
string balancedate = Convert.ToString(Request.QueryString["balancedate"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select CompanyName,sect_maj_nm,sect_maj_cd,nos_t,bal_dt,rt_acm,tcst_aft_com,c_rt,tot_cost,DSE_rate,m_rt,m_p,diff,gain,f.f_name from" +
" (select trim(c.comp_nm) as CompanyName,f_cd,sect_maj_nm, a.sect_maj_cd,trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com/tot_nos,2) rt_acm," +
" ROUND(tcst_aft_com,2) tcst_aft_com,ROUND(tot_cost/tot_nos,2)c_rt, tot_cost, nvl(a.dse_rt,0) DSE_rate, a.adc_rt m_rt , a.adc_rt* tot_nos m_p," +
" (a.adc_rt - trunc(tcst_aft_com/tot_nos,2)) diff ,(round(a.adc_rt,2) - trunc(tcst_aft_com/tot_nos,2)) * trunc(tot_nos) gain from pfolio_bk a, comp c " +
" where c.comp_cd=a.comp_cd and f_cd=" + fundcode + " and a.bal_dt_ctrl='" + Convert.ToDateTime(balancedate).ToString("dd - MMM - yyyy") + "' and c.cds='Y' order by c.comp_nm)tab1 inner join Fund f ON tab1.f_cd = f.f_cd order by tab1.sect_maj_nm,tab1.CompanyName");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "PortFolioNonDemateShare";
dtReprtSource.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\UI\ReportViewer\Report\crtPortFolioNonDemateShareReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CR_PortFolioNonDemateShare.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_PortFolioNonDemateShare.ReportSource = rdoc;
CR_PortFolioNonDemateShare.DisplayToolbar = true;
CR_PortFolioNonDemateShare.HasExportButton = true;
CR_PortFolioNonDemateShare.HasPrintButton = true;
rdoc.SetParameterValue("balDate", balancedate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string p1date = Convert.ToString(Request.QueryString["p1date"]).Trim();
string p2date = Convert.ToString(Request.QueryString["p2date"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select f.F_CD, f_name fund_name,f.COMP_CD, c.comp_nm,I_NO_SHR,O_NO_SHR,TOT_NOS,I_RATE,O_RATE,IRT_AFT_COM,ORT_AFT_COM,TOT_COST,TCST_AFT_COM,BAL_DT,OP_NAME");
sbMst.Append(" from fund_folio_hb f , comp c, fund n where tot_nos <= 0 and bal_dt between '" + p1date + "' and '" + p2date + "' and c.comp_cd = f.comp_cd and f.f_cd = n.f_cd order by f.bal_dt");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "NegativeBalanceCheck";
//dtReprtSource.WriteXmlSchema(@"D:\officialProject\1-8-17\amclpmfs\UI\ReportViewer\Report\crtNegativeBalanceCheckReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
//string Path = Server.MapPath("~/Report/crtNegativeBalanceCheckReport.rpt");
string Path = Server.MapPath("Report/CRNegativeBalanceCheck.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_NegativeBalanceCheck.ReportSource = rdoc;
CR_NegativeBalanceCheck.DisplayToolbar = true;
CR_NegativeBalanceCheck.HasExportButton = true;
CR_NegativeBalanceCheck.HasPrintButton = true;
//rdoc.SetParameterValue("prmtransTypeDetais", transTypeDetais);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string fundcode = Convert.ToString(Request.QueryString["fundcode"]).Trim();
string balancedate = Convert.ToString(Request.QueryString["balancedate"]).Trim();
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select CompanyName,nos_t,bal_dt,rt_acm,tcst_aft_com,c_rt,tot_cost,DSE_rate,CSE_rate,m_rt,m_p,diff,group1,Category,f.f_name from (select trim(c.comp_nm) as CompanyName, f_cd, trunc(tot_nos) nos_t, bal_dt, trunc(tcst_aft_com / tot_nos, 2) rt_acm, ROUND(tcst_aft_com, 2)tcst_aft_com, ROUND(tot_cost / tot_nos, 2) c_rt, tot_cost, nvl(a.dse_rt, 0) DSE_rate,nvl(a.cse_rt, 0) CSE_rate, a.adc_rt m_rt, a.adc_rt * tot_nos m_p, ROUND(a.adc_rt - tcst_aft_com / tot_nos, 2)diff,c.trade_meth group1, decode(c.trade_meth, 'N', 'A Group', 'R', 'B Group', 'Z', 'Z Group', 'T', 'N Group', 'G', 'G Group') Category from pfolio_bk a, comp c where c.comp_cd = a.comp_cd and f_cd =" + fundcode + " and a.bal_dt_ctrl ='" + Convert.ToDateTime(balancedate).ToString("dd-MMM-yyyy") + "' order by c.comp_nm) tab1 inner join Fund f ON tab1.f_cd = f.f_cd order by tab1.Category,tab1.CompanyName");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "PortFolioCategoryWise";
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/CR_PortFolioCategoryWise.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_PortFolioCategoryWise.ReportSource = rdoc;
CR_PortFolioCategoryWise.DisplayToolbar = true;
CR_PortFolioCategoryWise.HasExportButton = true;
CR_PortFolioCategoryWise.HasPrintButton = true;
rdoc.SetParameterValue("balDate", balancedate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT C.COMP_NM,B.COMP_CD, floor(P.TOT_NOS) tot_nos, p.f_cd, f.f_name f_name,floor(P.TOT_NOS * B.BONUS / 100) share_alloted,B.FY, B.RECORD_DT, B.BOOK_TO, B.BONUS, B.RIGHT_APPR_DT, B.RIGHT, B.CASH, B.AGM, B.REMARKS, B.POSTED, b.pdate FROM BOOK_CL B, COMP C, PFOLIO_BK P, fund f WHERE B.COMP_CD = P.COMP_CD AND B.COMP_CD = C.COMP_CD AND P.BAL_DT_CTRL = B.RECORD_DT and B.BONUS is not null and posted is not null and p.f_cd = f.f_cd order by C.COMP_NM");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource.TableName = "StockDeclarationAFterPostedReport";
//dtReprtSource.WriteXmlSchema(@"D:\officialProject\2-13-2017\amclpmfs\UI\ReportViewer\Report\crtStockDeclarationAfterPostedReport.xsd");
if (dtReprtSource.Rows.Count > 0)
{
//string Path = Server.MapPath("~/Report/crtNegativeBalanceCheckReport.rpt");
string Path = Server.MapPath("Report/StockDeclarationAfterPostedReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_StockDeclarationAfterPostedReport.ReportSource = rdoc;
CR_StockDeclarationAfterPostedReport.DisplayToolbar = true;
CR_StockDeclarationAfterPostedReport.HasExportButton = true;
CR_StockDeclarationAfterPostedReport.HasPrintButton = true;
//rdoc.SetParameterValue("prmtransTypeDetais", transTypeDetais);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fundCode = "";
string balDate = "";
string sector = "";
string category = "";
string group = "";
string ipo = "";
string marketype = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fundCode = Convert.ToString(Request.QueryString["fundCode"]).Trim();
balDate = Convert.ToString(Request.QueryString["balDate"]).Trim();
sector = Convert.ToString(Request.QueryString["sector"]).Trim();
category = Convert.ToString(Request.QueryString["category"]).Trim();
group = Convert.ToString(Request.QueryString["group"]).Trim();
ipo = Convert.ToString(Request.QueryString["ipo"]).Trim();
marketype = Convert.ToString(Request.QueryString["marketype"]).Trim();
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("select * from (SELECT FUND.F_CD, FUND.F_NAME, COMP.COMP_NM,COMP.CAT_TP,COMP.TRADE_METH,COMP.PROS_PUB_DT, COMP.IPOTYPE,COMP. MARKETTYPE, ");
sbMst.Append("COMP.ISSUE_MNG, PFOLIO_BK.SECT_MAJ_NM,PFOLIO_BK.SECT_MAJ_CD, TRUNC(PFOLIO_BK.TOT_NOS,0) AS TOT_NOS, ROUND(PFOLIO_BK.TCST_AFT_COM / PFOLIO_BK.TOT_NOS, 8) AS COST_RT_PER_SHARE, PFOLIO_BK.TCST_AFT_COM, NVL(PFOLIO_BK.DSE_RT, 0) AS DSE_RT,");
sbMst.Append("NVL(PFOLIO_BK.CSE_RT, 0) AS CSE_RT, ROUND(PFOLIO_BK.ADC_RT, 8) AS AVG_RATE, ROUND(PFOLIO_BK.TOT_NOS * PFOLIO_BK.ADC_RT, 8) AS TOT_MARKET_PRICE, ");
sbMst.Append("ROUND(ROUND(PFOLIO_BK.ADC_RT, 8) - ROUND(PFOLIO_BK.TCST_AFT_COM / PFOLIO_BK.TOT_NOS, 8), 8) AS RATE_DIFF,");
sbMst.Append("ROUND(ROUND(PFOLIO_BK.TOT_NOS * PFOLIO_BK.ADC_RT, 8) - PFOLIO_BK.TCST_AFT_COM, 8) AS APPRICIATION_ERROTION,");
sbMst.Append("ROUND((PFOLIO_BK.TOT_NOS * PFOLIO_BK.ADC_RT - PFOLIO_BK.TCST_AFT_COM) / PFOLIO_BK.TCST_AFT_COM * 100, 8) AS PERCENT_OF_APRE_EROSION, ");
sbMst.Append("ROUND(PFOLIO_BK.TOT_NOS / COMP.NO_SHRS * 100, 8) AS PERCENTAGE_OF_PAIDUP FROM ");
sbMst.Append("PFOLIO_BK INNER JOIN COMP ON PFOLIO_BK.COMP_CD = COMP.COMP_CD INNER JOIN FUND ON PFOLIO_BK.F_CD = FUND.F_CD ");
sbMst.Append(" WHERE (PFOLIO_BK.BAL_DT_CTRL = '" + balDate + "') AND (FUND.F_CD =" + fundCode + ") ORDER BY PFOLIO_BK.SECT_MAJ_CD, COMP.COMP_NM ) a ");
if (sector != "0" && category == "0" && group == "0" && ipo == "0")
{
sbMst.Append(" where a.SECT_MAJ_CD=" + sector + " and a.MARKETTYPE='" + marketype + "'");
}
else if (sector == "0" && category != "0" && group == "0" && ipo == "0")
{
sbMst.Append(" where a.CAT_TP='" + category + "' and a.MARKETTYPE='" + marketype + "'");
}
else if (sector == "0" && category == "0" && group != "0" && ipo == "0")
{
sbMst.Append(" where A.TRADE_METH='" + group + "' and a.MARKETTYPE='" + marketype + "'");
}
else if (sector == "0" && category == "0" && group == "0" && ipo != "0")
{
sbMst.Append(" where a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "'");
}
else if (sector == "0" && category == "0" && group == "0" && ipo == "0" && marketype != "0")
{
sbMst.Append(" where a.MARKETTYPE='" + marketype + "'");
}
else if (sector == "0" && category != "0" && group == "0" && ipo != "0" && marketype != "0")
{
sbMst.Append(" where a.CAT_TP='" + category + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector == "0" && category != "0" && group == "0" && ipo != "0" && marketype != "0")
{
sbMst.Append(" where a.CAT_TP='" + category + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector == "0" && category != "0" && group != "0" && ipo == "0" && marketype != "0")
{
sbMst.Append(" where a.CAT_TP='" + category + "' and A.TRADE_METH='" + group + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector == "0" && category != "0" && group != "0" && ipo != "0" && marketype != "0")
{
sbMst.Append(" where a.CAT_TP='" + category + "' and A.TRADE_METH='" + group + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category == "0" && group != "0" && ipo != "0" && marketype != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and A.TRADE_METH='" + group + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category == "0" && group == "0" && ipo != "0" && marketype != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category == "0" && group == "0" && ipo == "0" && marketype != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category != "0" && group == "0" && ipo != "0" && marketype != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and a.CAT_TP='" + category + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category != "0" && group != "0" && ipo == "0" && marketype != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and a.CAT_TP='" + category + "' and A.TRADE_METH='" + group + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category != "0" && group != "0" && ipo != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and a.CAT_TP='" + category + "' and A.TRADE_METH='" + group + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector == "0" && category == "0" && group != "0" && ipo != "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and A.TRADE_METH='" + group + "' and a.IPOTYPE='" + ipo + "' and a.MARKETTYPE='" + marketype + "' ");
}
else if (sector != "0" && category == "0" && group != "0" && ipo == "0")
{
sbMst.Append(" where a.SECT_MAJ_CD='" + sector + "' and A.TRADE_METH='" + group + "' and a.MARKETTYPE='" + marketype + "' ");
}
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
DataTable dtNonlistedSecrities = new DataTable();
sbMst = new StringBuilder();
sbMst.Append("SELECT INV_AMOUNT AS COST_PRICE, INV_AMOUNT AS MARKET_PRICE ");
sbMst.Append("FROM NON_LISTED_SECURITIES ");
sbMst.Append("WHERE (F_CD = " + fundCode + ") AND (INV_DATE = ");
sbMst.Append(" (SELECT MAX(INV_DATE) AS EXPR1 ");
sbMst.Append("FROM NON_LISTED_SECURITIES NON_LISTED_SECURITIES_1 ");
sbMst.Append("WHERE (F_CD = " + fundCode + ") AND (INV_DATE <= '" + balDate + "'))) ");
dtNonlistedSecrities = commonGatewayObj.Select(sbMst.ToString());
Decimal nonlistedCostPrice = 0;
Decimal nonlistedMarketPrice = 0;
if (dtNonlistedSecrities.Rows.Count > 0)
{
nonlistedCostPrice = Convert.ToDecimal(dtNonlistedSecrities.Rows[0][0]);
nonlistedMarketPrice = Convert.ToDecimal(dtNonlistedSecrities.Rows[0][0]);
}
if (dtReprtSource.Rows.Count > 0)
{
Decimal totalInvest = 0;
for (int loop = 0; loop < dtReprtSource.Rows.Count; loop++)
{
totalInvest = totalInvest + Convert.ToDecimal(dtReprtSource.Rows[loop]["TCST_AFT_COM"]);
}
dtReprtSource.TableName = "PortfolioWithNonListedReport";
//dtReprtSource.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtPortfolioWithNonListedReport.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = "";
Path = Server.MapPath("Report/crtPortfolioWithDiffrentCritiria.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_PfolioWithdiffrentcriteria.ReportSource = rdoc;
CRV_PfolioWithdiffrentcriteria.DisplayToolbar = true;
CRV_PfolioWithdiffrentcriteria.HasExportButton = true;
CRV_PfolioWithdiffrentcriteria.HasPrintButton = true;
rdoc.SetParameterValue("prmbalDate", balDate);
rdoc.SetParameterValue("prmTotalInvest", totalInvest);
rdoc.SetParameterValue("prmNonlistedCostPrice", nonlistedCostPrice);
rdoc.SetParameterValue("prmNonlisteMarketPrice", nonlistedMarketPrice);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fromDate = "";
string toDate = "";
string fundCode = "";
string companyCode = "";
string fundName = "";
string companyName = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fromDate = (string)Session["fromDate"];
toDate = (string)Session["toDate"];
fundCode = (string)Session["fundCode"];
companyCode = (string)Session["companyCode"];
fundName = (string)Session["fundName"];
companyName = (string)Session["companyName"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT DERIVEDTBL_1.VCH_DT AS TranDate, decode(DERIVEDTBL_1.TRAN_TP, 'B', 'BONUS', 'S', 'PayIn', 'C', 'PayOut', 'I', 'IPO', 'P', 'IPO', 'R', 'Right', '0', ");
sbMst.Append(" 'Opening') AS TranType, DERIVEDTBL_1.DEBIT, DERIVEDTBL_1.CREDIT, PFOLIO_BK.TOT_NOS AS Balance ");
sbMst.Append(" FROM PFOLIO_BK RIGHT OUTER JOIN ");
sbMst.Append(" (SELECT VCH_DT, F_CD, COMP_CD, TRAN_TP, DEBIT, CREDIT ");
sbMst.Append(" FROM (SELECT VCH_DT, F_CD, COMP_CD, TRAN_TP, SUM(NO_SHARE) AS DEBIT, 0 AS CREDIT ");
sbMst.Append(" FROM FUND_TRANS_HB ");
sbMst.Append(" WHERE (TRAN_TP = 'S') AND (F_CD = " + fundCode + ") AND (COMP_CD = " + companyCode + ") AND (VCH_DT BETWEEN '" + fromDate + "' AND '" + toDate + "') ");
sbMst.Append(" GROUP BY VCH_DT, F_CD, COMP_CD, TRAN_TP) DERIVEDTBL_1_1 ");
sbMst.Append(" UNION ");
sbMst.Append(" SELECT VCH_DT, F_CD, COMP_CD, TRAN_TP, 0 AS DEBIT, SUM(NO_SHARE) AS CREDIT ");
sbMst.Append(" FROM FUND_TRANS_HB FUND_TRANS_HB_1 ");
sbMst.Append(" WHERE (TRAN_TP <> 'S') AND (F_CD = " + fundCode + ") AND (COMP_CD = " + companyCode + ") AND (VCH_DT BETWEEN '" + fromDate + "' AND '" + toDate + "') ");
sbMst.Append(" GROUP BY VCH_DT, F_CD, COMP_CD, TRAN_TP) DERIVEDTBL_1 ON PFOLIO_BK.F_CD = DERIVEDTBL_1.F_CD AND ");
sbMst.Append(" PFOLIO_BK.COMP_CD = DERIVEDTBL_1.COMP_CD AND PFOLIO_BK.BAL_DT_CTRL = DERIVEDTBL_1.VCH_DT ");
sbMst.Append(" ORDER BY DERIVEDTBL_1.VCH_DT ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "ShareReconciliationReport";
//dtReprtSource.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\crtShareReconciliationReport.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = "";
Path = Server.MapPath("Report/crtShareReconciliationReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_ShareReconciliation.ReportSource = rdoc;
CRV_ShareReconciliation.DisplayToolbar = true;
CRV_ShareReconciliation.HasExportButton = true;
CRV_ShareReconciliation.HasPrintButton = true;
rdoc.SetParameterValue("prmFundName", fundName);
rdoc.SetParameterValue("prmCompName", companyName);
rdoc.SetParameterValue("prmFromDate", fromDate);
rdoc.SetParameterValue("prmToDate", toDate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
string letterPrintDateCr = "";
string paperPublishedDateCr = "";
string signatoryId = "";
if (Request.QueryString["letterPrintDate"] != "")
{
letterPrintDateCr = Request.QueryString["letterPrintDate"].ToString();
}
if (Request.QueryString["publishedDate"] != "")
{
paperPublishedDateCr = Request.QueryString["publishedDate"].ToString();
}
int letterToCr = Convert.ToInt32(Request.QueryString["letterTo"]);
int paperId = Convert.ToInt32(Request.QueryString["paperName"]);
signatoryId = Convert.ToString(Request.QueryString["signatory"]);
int fundCodeCr = Convert.ToInt32(Request.QueryString["fundName"]);
DataTable dtPaperInfo = pf1s1DAOObj.GetPaperInfo(paperId);
string nameOfPaper = dtPaperInfo.Rows[0][0].ToString();//name of Paper
DataTable dtSignatoryInfo = pf1s1DAOObj.GetSignatoryInfo(signatoryId);
string nameOfSignatory = dtSignatoryInfo.Rows[0][0].ToString(); //name of Signatory
string designationOfSignatory = dtSignatoryInfo.Rows[0][1].ToString(); //Designation of Signatory
string designationShortOfSignatory = dtSignatoryInfo.Rows[0][2].ToString(); //Short Designation of Signatory
DataTable dtReportToPerson = new DataTable();
StringBuilder sbMstToPerson = new StringBuilder();
StringBuilder sbfilterToPerson = new StringBuilder();
sbfilterToPerson.Append(" ");
sbMstToPerson.Append("SELECT NAME, DESIGNATION, ORGANIZATION_NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, DESPATCH_NO, ATTENTION ");
sbMstToPerson.Append("FROM NAV_LETTER.TO_PERSON ");
//sbMstToPerson.Append("WHERE(1 = 1) ");
if (letterToCr >= 1)
{
sbfilterToPerson.Append(" WHERE ID =" + letterToCr);
}
sbMstToPerson.Append(sbfilterToPerson.ToString());
sbMstToPerson.Append(" ORDER BY ID");
dtReportToPerson = commonGatewayObj.Select(sbMstToPerson.ToString());
dtReportToPerson.TableName = "To_Person";
string nameOfPerson = dtReportToPerson.Rows[0]["NAME"].ToString();
string designationOfPerson = dtReportToPerson.Rows[0]["DESIGNATION"].ToString();
string organizationOfPerson = dtReportToPerson.Rows[0]["ORGANIZATION_NAME"].ToString();
string address_1OfPerson = dtReportToPerson.Rows[0][3].ToString();
string address_2OfPerson = dtReportToPerson.Rows[0][4].ToString();
string address_3OfPerson = dtReportToPerson.Rows[0][5].ToString();
string address_4OfPerson = dtReportToPerson.Rows[0][6].ToString();
string despatchNoOfPerson = dtReportToPerson.Rows[0][7].ToString();
string attentionOfPerson = dtReportToPerson.Rows[0][8].ToString();
DataTable dtReprtSource1 = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append("SELECT ID, F_CD, REF_NO, FACE_VALUE, F_NM, LETTER_REF_NO, LETTER_REF_DATE ");
sbMst.Append("FROM NAV_LETTER.LETTER_REFERENCE ");
sbMst.Append("WHERE (F_CD NOT IN (4)) ");
if (fundCodeCr != 0)
{
sbfilter.Append(" AND (F_CD =" + fundCodeCr + ")");
}
else
{
sbfilter.Append(" AND (F_CD >= 3) AND (F_CD < 27) ");
}
sbMst.Append(sbfilter.ToString());
sbMst.Append(" ORDER BY F_CD");
dtReprtSource1 = commonGatewayObj.Select(sbMst.ToString());
dtReprtSource1.TableName = "CloseEndNAVpaperCutting";
if (dtReprtSource1.Rows.Count > 0)
{
string Path = "";
//dtReprtSource1.WriteXmlSchema(@"F:\PortfolioManagementSystem\UI\ReportViewer\Report\CloseEndNAVpaperCuttingReport.xsd");
Path = Server.MapPath("Report/CloseEndNAVpaperCuttingReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource1);
CrystalReportViewerPaperCuttingNAV.ReportSource = rdoc;
rdoc.SetParameterValue("prmLetterPrintDate", letterPrintDateCr);
rdoc.SetParameterValue("prmNameOfPerson", nameOfPerson);
rdoc.SetParameterValue("prmDesignationOfPerson", designationOfPerson);
rdoc.SetParameterValue("prmOrganizationOfPerson", organizationOfPerson);
rdoc.SetParameterValue("prmAddress_1OfPerson", address_1OfPerson);
rdoc.SetParameterValue("prmAddress_2OfPerson", address_2OfPerson);
rdoc.SetParameterValue("prmAddress_3OfPerson", address_3OfPerson);
rdoc.SetParameterValue("prmAddress_4OfPerson", address_4OfPerson);
rdoc.SetParameterValue("prmDespatchNoOfPerson", despatchNoOfPerson);
rdoc.SetParameterValue("prmAttentionOfPerson", attentionOfPerson);
rdoc.SetParameterValue("prmPaperName", nameOfPaper);
rdoc.SetParameterValue("prmPaperPublishedDate", paperPublishedDateCr);
rdoc.SetParameterValue("prmNameOfSignatory", nameOfSignatory);
rdoc.SetParameterValue("prmDesignationOfSignatory", designationOfSignatory);
rdoc.SetParameterValue("prmDesignationShortOfSignatory", designationShortOfSignatory);
rdoc = ReportFactory.GetReport(rdoc.GetType());
CrystalReportViewerPaperCuttingNAV.DisplayToolbar = true;
CrystalReportViewerPaperCuttingNAV.HasExportButton = true;
CrystalReportViewerPaperCuttingNAV.HasPrintButton = true;
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fundCode = "";
string balDate = "";
string fundname = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fundCode = (string)Session["fundCode"];
balDate = (string)Session["balDate"];
fundname = (string)Session["fundName"];
}
String strSQL;
double cs_asset, cf_unlist, TotalAssetValue_Mar;
DataTable dtNAV = new DataTable();
DataTable dtNonListed = new DataTable();
strSQL = "select nvl(sum(costprice),0) total_asset_c,nvl(sum(marketprice),0) total_asset_m from nav.nav_details n, nav.nav_master m where " +
" n.NAVROWTYPE = 'A' and n.navfundid = " + fundCode + " and m.navfundid = " + fundCode + " and m.navno = (select max(navno) from nav.nav_master m where m.navfundid = " + fundCode + " AND m.NAVDATE <='" + balDate + "' )" +
" and n.navfundid = m.navfundid and m.navno = n.navno";
dtNAV = commonGatewayObj.Select(strSQL);
cs_asset = Convert.ToDouble(dtNAV.Rows[0]["total_asset_c"].ToString());
if (cs_asset == 0)
{
cs_asset = 1;
}
TotalAssetValue_Mar = Convert.ToDouble(dtNAV.Rows[0]["total_asset_m"].ToString());
strSQL = "select f_cd, inv_amount, inv_date FROM(SELECT f_cd, inv_amount, inv_date, " +
"rank() over (partition by f_cd order by inv_date desc) rnk FROM NON_LISTED_SECURITIES where inv_amount>0 and F_CD IN(" + fundCode + ") and inv_date<='" + balDate + "' )" +
" WHERE rnk = 1";
dtNonListed = commonGatewayObj.Select(strSQL);
if (dtNonListed.Rows.Count > 0)
{
cf_unlist = Convert.ToDouble(dtNonListed.Rows[0]["inv_amount"].ToString());
}
else
{
cf_unlist = 0;
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append(" select trim(c.comp_nm) as CompanyName , sect_maj_nm, a.sect_maj_cd,trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com/tot_nos,2) rt_acm, trunc (tcst_aft_com,2) tcst_aft_com, Round(tot_cost/tot_nos,2) c_rt,");
sbMst.Append(" tot_cost, ROUND(tcst_aft_com/ '" + cs_asset + "'*100,2) cost_percent,nvl(a.dse_rt,0) DSE_rate, a.adc_rt m_rt, a.adc_rt* tot_nos m_p,(a.dse_rt - trunc(tcst_aft_com/tot_nos,2)) diff , ");
sbMst.Append(" (round(a.dse_rt,2) - trunc(tcst_aft_com/tot_nos,2)) * trunc(tot_nos) gain ,ROUND(trunc(tot_nos)/c.no_shrs*100,2) paid_cap,'" + cs_asset + "' asset,ROUND('" + cf_unlist + "'/'" + cs_asset + "'*100,2) unl_p ");
sbMst.Append(" from pfolio_bk a, comp c , nav.nav_master n where c.comp_cd=a.comp_cd and f_cd='" + fundCode + "' and a.bal_dt_ctrl='" + balDate + "' and n.navfundid= '" + fundCode + "' and n.navfundid=f_cd ");
sbMst.Append(" and navno=(select max(navno) from nav.nav_master where navfundid=f_cd AND NAVDATE<='" + balDate + "' ) order by c.comp_nm ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "SecInvesmentsectorwisereport";
// dtReprtSource.WriteXmlSchema(@"D:\officialProject\4-5-2017\amclpmfs\UI\ReportViewer\Report\CR_SecInvestmentSectorWiseReport.xsd");
//ReportDocument rdoc = new ReportDocument();
string Path = "";
Path = Server.MapPath("Report/crtSecInvectorwisereport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CR_Sec_Invesment_sectorwise_report.ReportSource = rdoc;
CR_Sec_Invesment_sectorwise_report.DisplayToolbar = true;
CR_Sec_Invesment_sectorwise_report.HasExportButton = true;
CR_Sec_Invesment_sectorwise_report.HasPrintButton = true;
rdoc.SetParameterValue("balDate", balDate);
rdoc.SetParameterValue("fundName", fundname);
rdoc.SetParameterValue("TotalAssetValue_Mar", cs_asset);
rdoc.SetParameterValue("NonListedValue", cf_unlist);
// rdoc.SetParameterValue("prmNonlistedCostPrice", nonlistedCostPrice);
//rdoc.SetParameterValue("prmNonlisteMarketPrice", nonlistedMarketPrice);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fromDate = "";
string toDate = "";
string fundCode = "";
DataTable dtIntimationReport = new DataTable();
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
fromDate = (string)Session["fromDate"];
toDate = (string)Session["toDate"];
fundCode = (string)Session["fundCode"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
sbfilter.Append(" ");
sbMst.Append(" SELECT DISTINCT COMP.COMP_NM, NVL(DERIVEDTBL_BUY.NO_SHARE_BUY,0) as NO_SHARE_BUY, NVL(DERIVEDTBL_BUY.COSTRATE,0) as COSTRATE, NVL(DERIVEDTBL_SALE.NO_SHARE_SALE,0) as NO_SHARE_SALE, NVL(DERIVEDTBL_SALE.SALETRATE,0) as SALETRATE ");
sbMst.Append(" FROM FUND_TRANS_HB INNER JOIN ");
sbMst.Append(" COMP ON FUND_TRANS_HB.COMP_CD = COMP.COMP_CD LEFT OUTER JOIN ");
sbMst.Append(" (SELECT COMP_CD, SUM(NO_SHARE) AS NO_SHARE_BUY, ROUND(SUM(AMT_AFT_COM) / SUM(NO_SHARE), 2) AS COSTRATE ");
sbMst.Append(" FROM FUND_TRANS_HB FUND_TRANS_HB_2 ");
sbMst.Append(" WHERE (VCH_DT BETWEEN '" + fromDate.ToString() + "' AND '" + toDate.ToString() + "') AND (F_CD = " + fundCode.ToString() + ") AND (TRAN_TP = 'C') ");
sbMst.Append(" GROUP BY COMP_CD) DERIVEDTBL_BUY ON FUND_TRANS_HB.COMP_CD = DERIVEDTBL_BUY.COMP_CD LEFT OUTER JOIN ");
sbMst.Append(" (SELECT COMP_CD, SUM(NO_SHARE) AS NO_SHARE_SALE, SUM(AMT_AFT_COM) AS SALEAMOUNT, ROUND(SUM(AMT_AFT_COM) ");
sbMst.Append(" / SUM(NO_SHARE), 2) AS SALETRATE ");
sbMst.Append(" FROM FUND_TRANS_HB FUND_TRANS_HB_1 ");
sbMst.Append(" WHERE (VCH_DT BETWEEN '" + fromDate.ToString() + "' AND '" + toDate.ToString() + "') AND (TRAN_TP = 'S') AND (F_CD = " + fundCode.ToString() + ") ");
sbMst.Append(" GROUP BY COMP_CD) DERIVEDTBL_SALE ON FUND_TRANS_HB.COMP_CD = DERIVEDTBL_SALE.COMP_CD ");
sbMst.Append(" WHERE (FUND_TRANS_HB.VCH_DT BETWEEN '" + fromDate.ToString() + "' AND '" + toDate.ToString() + "') AND (FUND_TRANS_HB.F_CD IN (" + fundCode.ToString() + ")) AND ");
sbMst.Append(" (FUND_TRANS_HB.TRAN_TP IN ('C', 'S')) ");
sbMst.Append(" ORDER BY COMP.COMP_NM ");
sbMst.Append(sbfilter.ToString());
dtReprtSource = commonGatewayObj.Select(sbMst.ToString());
DataTable dtFundName = new DataTable();
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(" Select f_name from fund where f_cd=" + fundCode.ToString() + " ");
dtFundName = commonGatewayObj.Select(strBuilder.ToString());
string fundName = dtFundName.Rows[0]["f_name"].ToString();
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "SalePurchaseReport";
//dtReprtSource.WriteXmlSchema(@"G:\F Drive\PortfolioManagementSystem\UI\ReportViewer\Report\crtSalePurchaseReport.xsd");
string Path = "";
Path = Server.MapPath("Report/crtSalePurchaseReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_SalePurchase.ReportSource = rdoc;
rdoc.SetParameterValue("prmFromDate", fromDate);
rdoc.SetParameterValue("prmToDate", toDate);
rdoc.SetParameterValue("prmFundName", fundName);
CRV_SalePurchase.DisplayToolbar = true;
CRV_SalePurchase.HasExportButton = true;
CRV_SalePurchase.HasPrintButton = true;
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string strPortfolioAsOnDate = "";
string fundCodes = "";
//string companyCodes = "";
//string percentageCheck = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
strPortfolioAsOnDate = (string)Session["PortfolioAsOnDate"];
fundCodes = (string)Session["fundCodes"];
}
DataTable dtReprtSource = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
//strSQL = "select u.f_cd, u.fund_name,u.bal_dt_ctrl,u.TOTAL_COMPANY_PROFIT,u.TOTAL_SHARE_PROFIT,u.TOTAL_COST_PROFIT,u.TOTAL_MARKET_PRICE_PROFIT,u.PROFIT,u.tp_PROFIT," +
// " v.TOTAL_COMPANY_LOSS,v.TOTAL_SHARE_LOSS,v.TOTAL_COST_LOSS,v.TOTAL_MARKET_PRICE_LOSS,v.LOSS,v.tp_LOSS from "+
// "(select p.f_cd , f.f_name fund_name,TO_CHAR(bal_dt_ctrl,'dd-MON-yyyy')bal_dt_ctrl,COUNT(p.COMP_CD)TOTAL_COMPANY_PROFIT, sum(trunc(p.tot_nos)) TOTAL_SHARE_PROFIT," +
// "sum(p.tcst_aft_com) TOTAL_COST_PROFIT, sum(p.tot_nos * c.adc_rt) TOTAL_MARKET_PRICE_PROFIT, sum(p.tot_nos * c.adc_rt) - sum(p.tcst_aft_com)PROFIT," +
// "decode(sum(trunc(p.tot_nos)), 0, 'noneed', 'PROFIT') tp_PROFIT from pfolio_bk p, comp c, fund f " +
// " where p.bal_dt_ctrl ='" + strPortfolioAsOnDate.ToString() + "' and f.F_CD IN(" + fundCodes + ") and f.f_cd not in(3,5,18) and p.comp_cd = c.comp_cd " +
// " and(round(p.adc_rt, 2) - trunc(p.tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) >= 0 and p.f_cd = f.f_cd " +
// " group by p.f_cd, bal_dt_ctrl,f.f_name)u," +
// " (select p.f_cd ,f.f_name,TO_CHAR(bal_dt_ctrl,'dd-MON-yyyy')bal_dt_ctrl,COUNT(p.COMP_CD) TOTAL_COMPANY_LOSS, sum(trunc(p.tot_nos)) TOTAL_SHARE_LOSS, sum(p.tcst_aft_com) TOTAL_COST_LOSS," +
// " sum(p.tot_nos * c.adc_rt) TOTAL_MARKET_PRICE_LOSS, sum(p.tot_nos * c.adc_rt) - sum(p.tcst_aft_com) LOSS," +
// " decode(sum(trunc(p.tot_nos)), 0, 'noneed', 'LOSS') tp_LOSS" +
// " from pfolio_bk p, comp c ,fund f where p.bal_dt_ctrl ='" + strPortfolioAsOnDate.ToString() + "' and f.F_CD IN(" + fundCodes + ") and f.f_cd not in(3,5,18)" +
// " and p.comp_cd = c.comp_cd and(round(p.adc_rt, 2) - trunc(p.tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) < 0 " +
// " and p.f_cd = f.f_cd group by p.f_cd, bal_dt_ctrl, f.f_name)v"+
// " where u.f_cd=v.f_cd order by u.f_cd";
/* select f_cd, inv_amount, inv_date
* FROM(SELECT f_cd, inv_amount,
* inv_date,
* rank() over(partition by f_cd order by inv_date desc) rnk
* FROM NON_LISTED_SECURITIES)
* WHERE rnk = 1
*/
strSQL = "select n.f_cd ,m.f_name,m.bal_dt_ctrl,m.TOTAL_COMPANY, m.TOTAL_SHARE, m.TOTAL_COST,m.TOTAL_MARKET_PRICE,m.EROSION,n.inv_amount,n.inv_date,(m.TOTAL_COST+n.inv_amount)TOT_COST_nl," +
"m.TOTAL_MARKET_PRICE+n.inv_amount,n.inv_amount as non_list from" +
"(select p.f_cd ,f.f_name,TO_CHAR(p.bal_dt_ctrl,'dd-MON-yyyy')bal_dt_ctrl,COUNT(nvl(p.COMP_CD,0)) TOTAL_COMPANY, sum(trunc(nvl(p.tot_nos,0))) TOTAL_SHARE, sum(nvl(p.tcst_aft_com,0)) TOTAL_COST," +
"sum(nvl(p.tot_nos,0) * nvl(p.adc_rt,0)) TOTAL_MARKET_PRICE, sum(nvl(p.tot_nos,0) * nvl(p.adc_rt,0)) - sum(nvl(p.tcst_aft_com,0)) EROSION" +
" from pfolio_bk p ,fund f where p.bal_dt_ctrl ='" + strPortfolioAsOnDate.ToString() + "' and f.F_CD IN(" + fundCodes + ") " +
" and p.f_cd = f.f_cd group by p.f_cd, bal_dt_ctrl, f.f_name)m,(select f_cd,inv_amount,inv_date FROM (SELECT f_cd,inv_amount, inv_date," +
"rank() over (partition by f_cd order by inv_date desc) rnk" +
" FROM NON_LISTED_SECURITIES where inv_amount>0 and F_CD IN(" + fundCodes + ") and inv_date<='" + strPortfolioAsOnDate.ToString() + "' )" +
" WHERE rnk = 1)n where m.f_cd(+)=n.f_cd order by n.f_cd";
//strSQL = "select p.f_cd , f.f_name fund_name,bal_dt_ctrl,COUNT(p.COMP_CD)TOTAL_COMPANY, sum(trunc(p.tot_nos)) TOTAL_SHARE," +
// "sum(p.tcst_aft_com) TOTAL_COST, sum(p.tot_nos * c.adc_rt) TOTAL_MARKET_PRICE, sum(p.tot_nos * c.adc_rt) - sum(p.tcst_aft_com) EROSION," +
// "decode(sum(trunc(p.tot_nos)), 0, 'noneed', 'PROFIT') tp from pfolio_bk p, comp c, fund f " +
// " where p.bal_dt_ctrl ='" + strPortfolioAsOnDate.ToString() + "' and f.F_CD IN(" + fundCodes + ") and f.f_cd not in(3,5,18) and p.comp_cd = c.comp_cd " +
// " and(round(p.adc_rt, 2) - trunc(p.tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) >= 0 and p.f_cd = f.f_cd " +
// " group by p.f_cd, bal_dt_ctrl,f.f_name " +
// " union" +
// " select p.f_cd ,f.f_name,bal_dt_ctrl,COUNT(p.COMP_CD) TOTAL_COMPANY, sum(trunc(p.tot_nos)) TOTAL_SHARE, sum(p.tcst_aft_com) TOTAL_COST," +
// " sum(p.tot_nos * c.adc_rt) TOTAL_MARKET_PRICE, sum(p.tot_nos * c.adc_rt) - sum(p.tcst_aft_com) EROSION," +
// " decode(sum(trunc(p.tot_nos)), 0, 'noneed', 'LOSS') tp" +
// " from pfolio_bk p, comp c ,fund f where p.bal_dt_ctrl ='" + strPortfolioAsOnDate.ToString() + "' and f.F_CD IN(" + fundCodes + ") and f.f_cd not in(3,5,18)" +
// " and p.comp_cd = c.comp_cd and(round(p.adc_rt, 2) - trunc(p.tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) < 0 " +
// " and p.f_cd = f.f_cd group by p.f_cd, bal_dt_ctrl, f.f_name order by(9) desc";
dtReprtSource = commonGatewayObj.Select(strSQL);
dtReprtSource.TableName = "MrktValWithNonListedSecuritiesAllFunds";
// dtReprtSource.WriteXmlSchema(@"E:\amclpmfs\UI\ReportViewer\Report\xsdMrktValWithNonListedSecuritiesAllFunds.xsd");
if (dtReprtSource.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crptMarketValuationWithNonListedSecuritesReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_MarketValiationWithProfitLossReportViewer.ReportSource = rdoc;
//rdoc.SetParameterValue("prmtransactionDate", tranDate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
