public bool IsUesrCheck(string loginID, string loginPassword)
{
DataTable dtUserInfo = new DataTable();
string userId = loginID.ToString();
string passWord = Encrypt(loginPassword);
dtUserInfo = commonGatewayObj.Select("SELECT * FROM INVEST.USER_TABLE WHERE USER_ID='" + loginID + "' AND PASSWORD='******'");
if (dtUserInfo.Rows.Count > 0)
{
string UserID = dtUserInfo.Rows[0]["USER_ID"].ToString();
string UserName = dtUserInfo.Rows[0]["NAME"].ToString();
string UserType = dtUserInfo.Rows[0]["USER_TYPE"].ToString();
string password = dtUserInfo.Rows[0]["PASSWORD"].ToString();
if (UserID == userId && passWord == password)
{
Session["UserID"] = UserID;
}
Session["UserName"] = UserName;
Session["UserType"] = UserType;
return(true);
}
else
{
return(false);
}
}
protected void PrintDRFButton_Click(object sender, EventArgs e)
{
try
{
printType = "DRF";
StringBuilder sbMaster = new StringBuilder();
DataTable dtReportStatement = new DataTable();
sbMaster.Append("SELECT A.FUND_CD, UPPER(A.FUND_NM) AS FUND_NM,A.SALE_BO_NAME,A.SALE_OF_UNIT_BO,A.ISIN_NO, B.* FROM FUND_INFO A INNER JOIN (");
sbMaster.Append(" SELECT REG_BK, 'SALE DRF Ref. No: '||DRF_REF_NO AS DRF_REF_NO ,DRF_REG_FOLIO_NO,DRF_CERT_NO,DRF_DISTNCT_NO_FROM,DRF_DISTNCT_NO_TO,SUM(QTY) QTY FROM SALE");
sbMaster.Append(" WHERE REG_BK='" + fundCode + "' AND DRF_REF_NO=" + DRRefNoDropDownList.SelectedValue.ToString() + " GROUP BY REG_BK, DRF_REF_NO,DRF_REG_FOLIO_NO,DRF_CERT_NO,DRF_DISTNCT_NO_FROM,DRF_DISTNCT_NO_TO) B ON A.FUND_CD=B.REG_BK");
dtReportStatement = commonGatewayObj.Select(sbMaster.ToString());
if (dtReportStatement.Rows.Count > 0)
{
Session["dtReportStatement"] = dtReportStatement;
Session["printType"] = printType;
ClientScript.RegisterStartupScript(this.GetType(), "UnitHolderInfo", "window.open('ReportViewer/UnitDematePrintDRFTransfer.aspx')", true);
}
else
{
Session["dtReportStatement"] = null;
Session["printType"] = null;
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert ('No data found');", true);
}
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert ('" + ex.Message.Replace("'", "").ToString() + "');", true);
}
}
public DataTable dtFundPosition()
{
StringBuilder sbQuery = new StringBuilder();
DataTable dtUnitFundPosition = new DataTable();
sbQuery.Append(" SELECT A.FUND_NM , A.SL_CASH_AMT,A.SL_CHQ_AMT, A.SL_CASH_AMT+A.SL_CHQ_AMT AS TOTAL_SL_AMT, A.REP_EFT_AMT,A.REP_CHQ_AMT, A.REP_EFT_AMT+A.REP_CHQ_AMT AS TOTAL_REP_AMT");
sbQuery.Append(" ,( A.SL_CASH_AMT+A.SL_CHQ_AMT)-(A.REP_EFT_AMT+A.REP_CHQ_AMT) AS NET_AMOUNT ");
sbQuery.Append(" FROM ( SELECT MONEY_RECEIPT.REG_BK, SUM(DECODE(MONEY_RECEIPT.RECEIPT_TYPE, 'SL', DECODE(MONEY_RECEIPT.PAY_TYPE, 'CASH', MONEY_RECEIPT.UNIT_QTY * MONEY_RECEIPT.RATE, 'BOTH', ");
sbQuery.Append(" MONEY_RECEIPT.CASH_AMT, 'MULT', MONEY_RECEIPT.CASH_AMT, 0), 0)) AS SL_CASH_AMT, SUM(DECODE(MONEY_RECEIPT.RECEIPT_TYPE, 'SL', DECODE(MONEY_RECEIPT.PAY_TYPE, 'CHQ', ");
sbQuery.Append(" MONEY_RECEIPT.UNIT_QTY * MONEY_RECEIPT.RATE, 'BOTH', MONEY_RECEIPT.UNIT_QTY * MONEY_RECEIPT.RATE - NVL(MONEY_RECEIPT.CASH_AMT, 0), 'MULT', ");
sbQuery.Append(" MONEY_RECEIPT.UNIT_QTY * MONEY_RECEIPT.RATE - NVL(MONEY_RECEIPT.CASH_AMT, 0), 0), 0)) AS SL_CHQ_AMT, SUM(DECODE(MONEY_RECEIPT.RECEIPT_TYPE, 'REP', DECODE(MONEY_RECEIPT.REP_PAY_TYPE, 'EFT', ");
sbQuery.Append(" MONEY_RECEIPT.UNIT_QTY * MONEY_RECEIPT.RATE, 0), 0)) AS REP_EFT_AMT, SUM(DECODE(MONEY_RECEIPT.RECEIPT_TYPE, 'REP', DECODE(MONEY_RECEIPT.REP_PAY_TYPE, 'CHQ', ");
sbQuery.Append(" MONEY_RECEIPT.UNIT_QTY * MONEY_RECEIPT.RATE, 0), 0)) AS REP_CHQ_AMT, FUND_INFO.FUND_CD, FUND_INFO.FUND_NM, FUND_INFO.FUND_SUB_OPEN_DT");
sbQuery.Append(" FROM MONEY_RECEIPT, FUND_INFO WHERE MONEY_RECEIPT.REG_BK = FUND_INFO.FUND_CD AND MONEY_RECEIPT.VALID IS NULL ");
if (fundNameDropDownList.SelectedValue.ToString() != "0")
{
sbQuery.Append(" AND MONEY_RECEIPT.REG_BK='" + fundNameDropDownList.SelectedValue.ToString().ToUpper() + "'");
}
if (branchNameDropDownList.SelectedValue.ToString() != "0")
{
sbQuery.Append(" AND MONEY_RECEIPT.REG_BR='" + branchNameDropDownList.SelectedValue.ToString().ToUpper() + "'");
}
if (fromDateTextBox.Text != "" && toDateTextBox.Text != "")
{
sbQuery.Append(" AND (MONEY_RECEIPT.RECEIPT_DATE BETWEEN '" + fromDateTextBox.Text.Trim().ToString() + "' AND '" + toDateTextBox.Text.Trim().ToString() + "')");
}
sbQuery.Append(" GROUP BY MONEY_RECEIPT.REG_BK, FUND_INFO.FUND_CD, FUND_INFO.FUND_NM, FUND_INFO.FUND_SUB_OPEN_DT ");
sbQuery.Append(" ORDER BY FUND_INFO.FUND_SUB_OPEN_DT) A ");
dtUnitFundPosition = commonGatewayObj.Select(sbQuery.ToString());
return(dtUnitFundPosition);
}
public DataTable pdateDropDownList()//For Authorized Signatory
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable p1date = commonGatewayObj.Select("SELECT MIN( VCH_DT) as p1date FROM FUND_TRANS_HB");
DataTable p2date = commonGatewayObj.Select("SELECT MAX(VCH_DT) as p2date FROM FUND_TRANS_HB");
DataTable pdateDropDownList = new DataTable();
pdateDropDownList.Columns.Add("p1date", typeof(string));
pdateDropDownList.Columns.Add("p2date", typeof(string));
DataRow dr = pdateDropDownList.NewRow();
DataRow dr1 = pdateDropDownList.NewRow();
for (int loop = 0; loop < p1date.Rows.Count; loop++)
{
//dr = pdateDropDownList.NewRow();
dr["p1date"] = Convert.ToDateTime(p1date.Rows[loop]["p1date"]).ToString("dd-MMM-yyyy");
// dr["p2date"] = Convert.ToDateTime(pdate.Rows[loop]["vch_dt"]).ToString("dd-MMM-yyyy");
pdateDropDownList.Rows.Add(dr);
}
for (int loop = 0; loop < p2date.Rows.Count; loop++)
{
// dr = pdateDropDownList.NewRow();
dr["p2date"] = Convert.ToDateTime(p2date.Rows[loop]["p2date"]).ToString("dd-MMM-yyyy");
pdateDropDownList.Rows.Add(dr1);
}
return(pdateDropDownList);
}
private void FillCompanyNameDropDownList()
{
DataTable dtCompName = commonGatewayObj.Select("SELECT COMP_NM, COMP_CD FROM COMP ORDER BY COMP_NM");
DataTable dtCompNameDropDownList = new DataTable();
dtCompNameDropDownList.Columns.Add("COMP_NM", typeof(string));
dtCompNameDropDownList.Columns.Add("COMP_CD", typeof(string));
DataRow dr = dtCompNameDropDownList.NewRow();
dr["COMP_NM"] = "--Click Here to Select--";
dr["COMP_CD"] = "0";
dtCompNameDropDownList.Rows.Add(dr);
for (int loop = 0; loop < dtCompName.Rows.Count; loop++)
{
dr = dtCompNameDropDownList.NewRow();
dr["COMP_NM"] = dtCompName.Rows[loop]["COMP_NM"].ToString();
dr["COMP_CD"] = Convert.ToInt32(dtCompName.Rows[loop]["COMP_CD"]);
dtCompNameDropDownList.Rows.Add(dr);
}
companyNameDropDownList.DataSource = dtCompNameDropDownList;
companyNameDropDownList.DataTextField = "COMP_NM";
companyNameDropDownList.DataValueField = "COMP_CD";
companyNameDropDownList.DataBind();
}
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");
}
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 findButton_Click(object sender, EventArgs e)
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT PRICE_REFIX.*,TO_CHAR(REFIX_DT,'DD-MON-YYYY') AS REFIX_DATE,TO_CHAR(EFFECTIVE_DATE,'DD-MON-YYYY') AS EFFECTIVE_DT FROM PRICE_REFIX WHERE 1=1 ");
if (fundNameDropDownList.SelectedValue != "0")
{
sbQuery.Append(" AND FUND_CD='" + fundNameDropDownList.SelectedValue.ToString() + "'");
}
if (refixationDateFromDropDownList.SelectedValue != "0" && refixationDateToDropDownList.SelectedValue == "0")
{
sbQuery.Append("AND REFIX_DT>='" + refixationDateFromDropDownList.SelectedItem.Text.ToString() + "'");
}
else if (refixationDateFromDropDownList.SelectedValue == "0" && refixationDateToDropDownList.SelectedValue != "0")
{
sbQuery.Append("AND REFIX_DT<='" + refixationDateToDropDownList.SelectedItem.Text.ToString() + "'");
}
else if (refixationDateFromDropDownList.SelectedValue != "0" && refixationDateToDropDownList.SelectedValue != "0")
{
sbQuery.Append("AND REFIX_DT BETWEEN '" + refixationDateFromDropDownList.SelectedItem.Text.ToString() + "' AND '" + refixationDateToDropDownList.SelectedItem.Text.ToString() + "'");
}
if (effectiveDateFromDropDownList.SelectedValue != "0" && effectiveDateToDropDownList.SelectedValue == "0")
{
sbQuery.Append("AND REFIX_DT>='" + effectiveDateFromDropDownList.SelectedItem.Text.ToString() + "'");
}
else if (effectiveDateFromDropDownList.SelectedValue == "0" && effectiveDateToDropDownList.SelectedValue != "0")
{
sbQuery.Append("AND REFIX_DT<='" + effectiveDateToDropDownList.SelectedItem.Text.ToString() + "'");
}
else if (effectiveDateFromDropDownList.SelectedValue != "0" && effectiveDateToDropDownList.SelectedValue != "0")
{
sbQuery.Append("AND REFIX_DT BETWEEN '" + effectiveDateFromDropDownList.SelectedItem.Text.ToString() + "' AND '" + effectiveDateToDropDownList.SelectedItem.Text.ToString() + "'");
}
sbQuery.Append(" ORDER BY REFIX_DT DESC, EFFECTIVE_DATE DESC");
DataTable dtPriceInfo = commonGatewayObj.Select(sbQuery.ToString());
if (dtPriceInfo.Rows.Count > 0)
{
dvGridSurrender.Visible = true;
SurrenderListGridView.DataSource = dtPriceInfo;
SurrenderListGridView.DataBind();
}
else
{
dvGridSurrender.Visible = false;
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "Popup", "alert('No Data Found');", true);
}
}
protected void PrintButton_Click(object sender, EventArgs e)
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT U_MASTER.HNAME, U_JHOLDER.JNT_NAME, U_MASTER.ADDRS1, U_MASTER.CITY, U_MASTER.ADDRS2, DIVI_PARA.F_YEAR, DIVI_PARA.FY_PART, DECODE(DIVIDEND.DIDUCT, 0, 0, ROUND((DIVIDEND.DIDUCT * 100) / (DIVIDEND.TOT_DIVI - DIVI_PARA.TAX_LIMIT), 2)) AS TAX_RATE, ");
sbQuery.Append(" DIVI_PARA.RATE, DIVI_PARA.AGM_DT, DIVI_PARA.ISS_DT, DIVIDEND.FUND_CD, DIVIDEND.DIVI_NO, DIVIDEND.FY, DIVIDEND.CLOSE_DT, DECODE(DIVIDEND.CIP,'N',0,DIVIDEND.FI_DIVI_QTY) AS FRAC_DIVI,");
sbQuery.Append(" LPAD(DIVIDEND.WAR_NO, 7, '0') AS WAR_NO , DIVIDEND.REG_BK, DIVIDEND.REG_BR, DIVIDEND.REG_NO, DIVIDEND.TOT_DIVI, DIVIDEND.DIDUCT, DIVIDEND.FI_DIVI_QTY,NVL(CIP_QTY,0) AS CIP_QTY,");
sbQuery.Append(" DIVIDEND.CIP_SL_NO, DIVIDEND.USER_NM, DIVIDEND.ENT_DT, DIVIDEND.REMARKS, DIVIDEND.VALID, DIVIDEND.BALANCE,DIVIDEND.TOT_DIVI-DIVIDEND.DIDUCT AS NET_DIVI ,");
sbQuery.Append(" DIVIDEND.ENT_TM, DIVIDEND.CIP, DIVIDEND.ID_FLAG, DIVIDEND.BK_FLAG, DIVIDEND.DIVI_RATE, DIVIDEND.CIP_RATE, DIVIDEND.ADDITIONAL_PAY, ");
sbQuery.Append(" DIVIDEND.DIVI_STATUS, DIVIDEND.WAR_DELEVARY, DIVIDEND.WAR_DELEVARY_DT, DIVIDEND.WAR_BK_PAY, DIVIDEND.WAR_BK_PAY_DT, ");
sbQuery.Append(" DIVIDEND.ID_BK_BR_NM_CD,CASE WHEN LENGTH(DIVIDEND.BK_AC_NO)>13 THEN SUBSTR(DIVIDEND.BK_AC_NO, - 13) ELSE DIVIDEND.BK_AC_NO END AS BK_AC_NO, DIVIDEND.BK_NM_CD, DIVIDEND.BK_BR_NM_CD, DIVIDEND.IS_BEFTN, DIVIDEND.IS_BEFTN_SUCCS, DIVIDEND.BEFTN_CREDIT_DT, DIVIDEND.BEFTN_RETURN_DT,");
sbQuery.Append(" BANK_NAME.BANK_NAME,BANK_BRANCH.BRANCH_NAME FROM DIVIDEND INNER JOIN DIVI_PARA ON DIVIDEND.FUND_CD = DIVI_PARA.FUND_CD AND DIVIDEND.FY = DIVI_PARA.F_YEAR AND DIVIDEND.CLOSE_DT = DIVI_PARA.CLOSE_DT AND DIVIDEND.DIVI_NO = DIVI_PARA.DIVI_NO LEFT OUTER JOIN ");
sbQuery.Append(" BANK_NAME INNER JOIN BANK_BRANCH ON BANK_NAME.BANK_CODE = BANK_BRANCH.BANK_CODE ON DIVIDEND.BK_NM_CD = BANK_BRANCH.BANK_CODE AND ");
sbQuery.Append(" DIVIDEND.BK_BR_NM_CD = BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN U_MASTER ON DIVIDEND.REG_BK = U_MASTER.REG_BK AND DIVIDEND.REG_BR = U_MASTER.REG_BR AND ");
sbQuery.Append(" DIVIDEND.REG_NO = U_MASTER.REG_NO LEFT OUTER JOIN U_JHOLDER ON U_MASTER.REG_BK = U_JHOLDER.REG_BK AND U_MASTER.REG_BR = U_JHOLDER.REG_BR AND ");
sbQuery.Append(" U_MASTER.REG_NO = U_JHOLDER.REG_NO ");
sbQuery.Append(" WHERE 1=1 ");
sbQuery.Append(" AND (DIVI_PARA.FUND_CD = '" + fundNameDropDownList.SelectedValue.ToString().ToUpper() + "')AND (DIVI_PARA.F_YEAR = '" + DividendFYDropDownList.SelectedItem.Text.ToString() + "')");
sbQuery.Append(" AND (DIVI_PARA.CLOSE_DT = '" + ClosingDateDropDownList.SelectedItem.ToString().ToUpper() + "') AND (DIVI_PARA.FY_PART = '" + fyPartDropDownList.SelectedItem.Text.ToString() + "')");
if (fromWar_NoTextBox.Text != "" && toWar_NoTextBox.Text == "")
{
sbQuery.Append(" AND TO_NUMBER(DIVIDEND.WAR_NO)>=" + Convert.ToUInt32(fromWar_NoTextBox.Text.Trim().ToString()));
}
else if (fromWar_NoTextBox.Text == "" && toWar_NoTextBox.Text != "")
{
sbQuery.Append(" AND TO_NUMBER(DIVIDEND.WAR_NO)<=" + Convert.ToUInt32(toWar_NoTextBox.Text.Trim().ToString()));
}
else if (fromWar_NoTextBox.Text != "" && toWar_NoTextBox.Text != "")
{
sbQuery.Append(" AND TO_NUMBER(DIVIDEND.WAR_NO) BETWEEN " + Convert.ToUInt32(fromWar_NoTextBox.Text.Trim().ToString()) + " AND " + Convert.ToUInt32(toWar_NoTextBox.Text.Trim().ToString()));
}
sbQuery.Append(" ORDER BY WAR_NO ");
DataTable dtDividendInformation = commonGatewayObj.Select(sbQuery.ToString());
DataTable dtDiviParaInformation = commonGatewayObj.Select("SELECT * FROM DIVI_PARA WHERE (FUND_CD = '" + fundNameDropDownList.SelectedValue.ToString().ToUpper() + "')AND (F_YEAR = '" + DividendFYDropDownList.SelectedItem.Text.ToString() + "')AND (CLOSE_DT = '" + ClosingDateDropDownList.SelectedItem.ToString().ToUpper() + "') AND (FY_PART = '" + fyPartDropDownList.SelectedItem.Text.ToString() + "') ");
if ((dtDividendInformation.Rows.Count > 0) && (dtDiviParaInformation.Rows.Count > 0))
{
Session["dtDividendInformation"] = dtDividendInformation;
Session["dtDiviParaInformation"] = dtDiviParaInformation;
Session["fundName"] = fundNameDropDownList.SelectedItem.Text.ToString().ToUpper();
Session["fundCode"] = fundNameDropDownList.SelectedValue.ToString().ToUpper();
Session["BEFTN_Issue_Date"] = BEFTNIssueDateTextBox.Text.Trim().ToString();
ClientScript.RegisterStartupScript(this.GetType(), "UnitReportTaxCert", "window.open('ReportViewer/UnitReportIntimationLetterReportViewer.aspx')", true);
}
else
{
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert('No Data Found');", true);
}
}
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");
}
}
public bool IsDuplicateRenewal(UnitHolderRegistration regObj, UnitRenewal renwalObj)
{
DataTable dtRenewal = commonGatewayObj.Select("SELECT * FROM RENEWAL WHERE REG_BK='" + regObj.FundCode.ToString().ToUpper() + "'AND REG_BR='" + regObj.BranchCode.ToString() + "' AND REN_NO='" + renwalObj.RenewalNo.ToString() + "'");
if (dtRenewal.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool IsDuplicateTransfer(UnitHolderRegistration regObj, UnitTransfer transferObj)
{
DataTable dtTransfer = new DataTable();
dtTransfer = commonGatewayObj.Select("SELECT * FROM TRANSFER WHERE F_CD='" + regObj.FundCode.ToString().ToUpper() + "'AND BR_CODE='" + regObj.FundCode.ToString().ToUpper() + "_" + regObj.BranchCode.ToString() + "' AND TR_NO=" + Convert.ToInt32(transferObj.TransferNo.ToString()));
if (dtTransfer.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
protected void fundCodeTextBox_TextChanged(object sender, EventArgs e)
{
DataTable dtgetfund;
if (fundcodeTextBox.Text.ToString() != "")
{
string strfundcode = "SELECT * FROM FUND WHERE BOID IS NOT NULL and f_cd = " + fundcodeTextBox.Text.ToString() + "";
dtgetfund = commonGatewayObj.Select(strfundcode);
if (dtgetfund != null && dtgetfund.Rows.Count > 0)
{
txtfundName.Value = dtgetfund.Rows[0]["F_NAME"].ToString();
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert('This fund is already available !');", true);
}
}
}
public DataTable getbalanceDate()//For Authorized Signatory
{
DataTable pdate = commonGatewayObj.Select("select max(bal_dt_ctrl)+1 as BalanceDate from pfolio_bk");
DataTable pdateDropDownList = new DataTable();
pdateDropDownList.Columns.Add("balancedate1", typeof(string));
pdateDropDownList.Columns.Add("balancedate2", typeof(string));
DataRow dr = pdateDropDownList.NewRow();
dr = pdateDropDownList.NewRow();
dr["balancedate1"] = Convert.ToDateTime(pdate.Rows[0]["BalanceDate"]).ToString("dd-MMM-yyyy");
dr["balancedate2"] = Convert.ToDateTime(pdate.Rows[0]["BalanceDate"]).ToString("dd-MMM-yyyy");
pdateDropDownList.Rows.Add(dr);
return(pdateDropDownList);
}
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");
}
}
public static bool InsertandUpdateFund(string FundId, string FundName, string FundType, string customerCode, string boId, string sellbuycommision, string CompanyCode, string fundClose)
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable dtgetfund;
if (FundId != "")
{
string strfundcode = "SELECT * FROM FUND WHERE BOID IS NOT NULL and f_cd = " + FundId + "";
dtgetfund = commonGatewayObj.Select(strfundcode);
if (dtgetfund != null && dtgetfund.Rows.Count > 0)
{
string strUPQuery = "update FUND set F_NAME='" + FundName + "',COMP_CD ='" + CompanyCode + "',F_TYPE ='" + FundType + "',IS_F_CLOSE='" + fundClose + "',CUSTOMER ='" + customerCode + "',BOID ='" + boId + "',SL_BUY_COM_PCT ='" + sellbuycommision + "' where F_CD =" + FundId + "";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strUPQuery);
}
else
{
string strInsQuery;
strInsQuery = "insert into Fund(F_CD,F_NAME,COMP_CD,F_TYPE,IS_F_CLOSE,CUSTOMER,BOID,SL_BUY_COM_PCT)values('" + FundId + "','" + FundName + "','" + CompanyCode + "','" + FundType + "','" + fundClose + "','" + customerCode + "','" + boId + "','" + sellbuycommision + "')";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strInsQuery);
}
}
return(true);
}
protected void searchButton_Click(object sender, EventArgs e)
{
string comp_cd = companyNameDropDownList.SelectedValue.ToString();
DataTable dtFind = commonGatewayObj.Select("SELECT * FROM BOOK_CL WHERE COMP_CD=" + comp_cd + " AND fy='" + financialYearTextBox.Text.ToString() + "' ORDER BY RECORD_DT DESC ");
if (dtFind.Rows.Count > 0)
{
addNewButton.Visible = false;
updateButton.Visible = true;
recordDateTextBox.Text = dtFind.Rows[0]["RECORD_DT"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dtFind.Rows[0]["RECORD_DT"]).ToString("dd-MMM-yyyy");
bookToTextBox.Text = dtFind.Rows[0]["BOOK_TO"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dtFind.Rows[0]["BOOK_TO"]).ToString("dd-MMM-yyyy");
stockTextBox.Text = dtFind.Rows[0]["BONUS"].Equals(DBNull.Value) ? "" : dtFind.Rows[0]["BONUS"].ToString();
rightApprovalDateTextBox.Text = dtFind.Rows[0]["RIGHT_APPR_DT"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dtFind.Rows[0]["RIGHT_APPR_DT"]).ToString("dd-MMM-yyyy");
rightTextBox.Text = dtFind.Rows[0]["RIGHT"].Equals(DBNull.Value) ? "" : dtFind.Rows[0]["RIGHT"].ToString();
cashTextBox.Text = dtFind.Rows[0]["CASH"].Equals(DBNull.Value) ? "" : dtFind.Rows[0]["CASH"].ToString();
agmDateTextBox.Text = dtFind.Rows[0]["AGM"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dtFind.Rows[0]["AGM"]).ToString("dd-MMM-yyyy");
remarksTextBox.Text = dtFind.Rows[0]["REMARKS"].Equals(DBNull.Value) ? "" : dtFind.Rows[0]["REMARKS"].ToString();
postedTextBox.Text = dtFind.Rows[0]["POSTED"].Equals(DBNull.Value) ? "" : dtFind.Rows[0]["POSTED"].ToString();
postedDateTextBox.Text = dtFind.Rows[0]["PDATE"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dtFind.Rows[0]["PDATE"]).ToString("dd-MMM-yyyy");
}
else
{
searchClearFields();
addNewButton.Visible = true;
updateButton.Visible = false;
//ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert('No data find');", true);
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert('No Data Found.');", true);
recordDateTextBox.Focus();
}
}
protected void ShowDataButton_Click(object sender, EventArgs e)
{
StringBuilder sbQueryString = new StringBuilder();
sbQueryString.Append("SELECT ID, REQ_NO, LETTER_NO, FUND_CD, BR_CD, CERT_TYPE,TO_CHAR(DELIV_DT,'DD-MON-YYYY') AS DELIV_DT, BOOK_NO_START, BOOK_NO_END, CERT_NO_START, CERT_NO_END, ");
sbQueryString.Append(" BOOK_NO_OPNING, BOOK_NO_DISTRIBUTION, BOOK_NO_BALANCE, PER_BOOK_CERT_AMT, VALID, USER_NM, ENT_DT, ENT_TM, REMARKS FROM CERT_BOOK_INFO");
sbQueryString.Append(" WHERE 1=1");
if (fundNameDropDownList.SelectedValue.ToString() != "0")
{
sbQueryString.Append(" AND FUND_CD='" + fundNameDropDownList.SelectedValue.ToString() + "'");
}
if (branchNameDropDownList.SelectedValue.ToString() != "0")
{
sbQueryString.Append(" AND BR_CD='" + branchNameDropDownList.SelectedValue.ToString() + "'");
}
if (certNoDropDownList.SelectedValue.ToString() != "0")
{
sbQueryString.Append(" AND CERT_TYPE='" + certNoDropDownList.SelectedValue.ToString() + "'");
}
sbQueryString.Append(" ORDER BY CERT_TYPE,CERT_NO_START ");
DataTable dtCertBookInfo = commonGatewayObj.Select(sbQueryString.ToString());
if (dtCertBookInfo.Rows.Count > 0)
{
dvSearchRegi.Visible = true;
dgCertInfo.DataSource = dtCertBookInfo;
dgCertInfo.DataBind();
}
else
{
dvSearchRegi.Visible = false;
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert ('No Data Found');", true);
}
}
private DataTable GetFundName()
{
DataTable dtFundName = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbOrderBy = new StringBuilder();
sbOrderBy.Append("");
sbMst.Append("SELECT FUND.F_CD, FUND.F_NAME, DERIVEDTBL_NAV.ASSET_VALUE ");
sbMst.Append(" FROM FUND LEFT OUTER JOIN ");
sbMst.Append(" (SELECT NAV.NAV_MASTER.NAVFUNDID, SUM(NAV.NAV_DETAILS.COSTPRICE) AS ASSET_VALUE ");
sbMst.Append(" FROM NAV.NAV_MASTER INNER JOIN ");
sbMst.Append(" NAV.NAV_DETAILS ON NAV.NAV_MASTER.NAVNO = NAV.NAV_DETAILS.NAVNO AND ");
sbMst.Append(" NAV.NAV_MASTER.NAVFUNDID = NAV.NAV_DETAILS.NAVFUNDID INNER JOIN ");
sbMst.Append(" (SELECT NAVFUNDID, MAX(NAVDATE) AS MAX_NAVDATE ");
sbMst.Append(" FROM NAV.NAV_MASTER NAV_MASTER_1 ");
sbMst.Append(" WHERE (NAVDATE <= '" + Convert.ToDateTime(transactionDateTextBox.Text).ToString("dd-MMM-yyyy") + "') ");
sbMst.Append(" GROUP BY NAVFUNDID) DERIVEDTBL_1 ON NAV.NAV_MASTER.NAVFUNDID = DERIVEDTBL_1.NAVFUNDID AND ");
sbMst.Append(" NAV.NAV_MASTER.NAVDATE = DERIVEDTBL_1.MAX_NAVDATE ");
sbMst.Append(" WHERE (NAV.NAV_DETAILS.NAVROWTYPE = 'A') ");
sbMst.Append(" GROUP BY NAV.NAV_MASTER.NAVFUNDID) DERIVEDTBL_NAV ON FUND.F_CD = DERIVEDTBL_NAV.NAVFUNDID ");
sbMst.Append(" WHERE (FUND.F_CD BETWEEN 1 AND 29) ");
sbMst.Append(" GROUP BY FUND.F_CD, FUND.F_NAME, DERIVEDTBL_NAV.ASSET_VALUE ");
sbOrderBy.Append(" ORDER BY FUND.F_CD ");
sbMst.Append(sbOrderBy.ToString());
dtFundName = commonGatewayObj.Select(sbMst.ToString());
return(dtFundName);
}
private void MaxDate()
{
StringBuilder maxDateSelectString = new StringBuilder();
DataTable dtMaxDseClosingPriceDate = new DataTable();
maxDateSelectString.Append("SELECT MAX(TRANS_DT) AS MAX_DATE FROM DSE_CLOSING_PRICE");
dtMaxDseClosingPriceDate = commonGatewayObj.Select(maxDateSelectString.ToString());
if (dtMaxDseClosingPriceDate.Rows.Count > 0 && !(dtMaxDseClosingPriceDate.Rows[0]["MAX_DATE"].Equals(DBNull.Value)))
{
updateTillTextBox.Text = Convert.ToDateTime(dtMaxDseClosingPriceDate.Rows[0]["MAX_DATE"]).ToString("dd-MMM-yyyy");
}
else
{
updateTillTextBox.Text = "";
}
}
protected void SaveButton_Click(object sender, EventArgs e)
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT * FROM PRICE_REFIX WHERE FUND_CD='" + fundNameDropDownList.SelectedValue.ToString() + "'");
if (fromPriceDateTextBox.Text != "" && toPriceDateTextBox.Text == "")
{
sbQuery.Append("AND REFIX_DT>='" + fromPriceDateTextBox.Text.Trim().ToString() + "'");
}
else if (fromPriceDateTextBox.Text == "" && toPriceDateTextBox.Text != "")
{
sbQuery.Append("AND REFIX_DT>='" + toPriceDateTextBox.Text.Trim().ToString() + "'");
}
else if (fromPriceDateTextBox.Text != "" && toPriceDateTextBox.Text != "")
{
sbQuery.Append("AND REFIX_DT BETWEEN '" + fromPriceDateTextBox.Text.Trim().ToString() + "' AND '" + toPriceDateTextBox.Text.Trim().ToString() + "'");
}
sbQuery.Append(" ORDER BY REFIX_DT DESC");
DataTable dtPriceInfo = commonGatewayObj.Select(sbQuery.ToString());
if (dtPriceInfo.Rows.Count > 0)
{
Session["dtPriceInfo"] = dtPriceInfo;
Session["fundCode"] = fundNameDropDownList.SelectedValue.ToString();
ClientScript.RegisterStartupScript(this.GetType(), "UnitHolderInfo", "window.open('ReportViewer/UnitReportPriceRefixationReportViewer.aspx')", true);
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert ('No data found');", true);
}
}
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");
}
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");
}
}
public DataTable Usermenu_Permission(string loginId)
{
DataTable dtMenUName = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbOrderBy = new StringBuilder();
sbOrderBy.Append("");
sbMst.Append(" select * from MENUPERMISSIONS where USER_ID = '" + loginId + "'");
sbMst.Append(sbOrderBy.ToString());
dtMenUName = commonGatewayObj.Select(sbMst.ToString());
Session["dtMenuName"] = dtMenUName;
return(dtMenUName);
}
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 DataTable BindMenuData(int parentmenuId)
{
DataTable dtMenu = commonGatewayObj.Select("SELECT M_ID AS MenuId, M_PARENT_ID AS ParentId,M_CAPTION AS Title,M_NAME AS Description,M_URL AS URL FROM MENU WHERE VALID='Y'AND PROJECT_ID=1 ORDER BY M_ID ");
var dv = dtMenu.DefaultView;
dv.RowFilter = "ParentId='" + parentmenuId + "'";
var newdt = dv.ToTable();
return(newdt);
}
private DataTable GetUserInfo()
{
DataTable dtUserList = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbOrderBy = new StringBuilder();
sbOrderBy.Append("");
sbMst.Append(" Select user_table.ID,user_table.USER_ID,user_table.NAME,user_table.DESIGNATION,USER_ROLE.ROLE_NAME from user_table INNER JOIN user_role ");
sbMst.Append("ON user_table.ROLE_ID = user_role.ROLE_ID ");
sbOrderBy.Append(" order by user_table.ID ");
sbMst.Append(sbOrderBy.ToString());
dtUserList = commonGatewayObj.Select(sbMst.ToString());
Session["dtUserInfo"] = dtUserList;
return(dtUserList);
}
protected void findButton_Click(object sender, EventArgs e)
{
StringBuilder sbQueryString = new StringBuilder();
if (SaleNoRadioButton.Checked)
{
sbQueryString.Append("SELECT U_MASTER.REG_NO, U_MASTER.REG_BK || '/' || U_MASTER.REG_BR || '/' || U_MASTER.REG_NO AS REGI_NO, SALE.SL_NO, TO_CHAR(SALE.SL_DT, 'DD-MON-YYYY') AS SL_DT,U_MASTER.ID_FLAG,U_MASTER.ID_AC, U_MASTER.ID_BK_NM_CD,U_MASTER.ID_BK_BR_NM_CD,");
sbQueryString.Append(" DECODE(U_MASTER.ID_FLAG, 'Y', BANK_NAME.BANK_NAME || ', ' || BANK_BRANCH.BRANCH_NAME ,U_MASTER.HNAME) HNAME, U_MASTER.FMH_NAME, U_MASTER.REG_BK, U_MASTER.REG_BR,DECODE(U_MASTER.ID_FLAG, 'Y',' ',U_JHOLDER.JNT_NAME)JNT_NAME,DECODE(U_MASTER.ID_FLAG, 'Y', U_MASTER.HNAME||','||','||U_JHOLDER.JNT_NAME||','||'ID AC NO: ' || U_MASTER.ID_AC ||','||' ', U_MASTER.ADDRS1) AS ADDRS1,");
sbQueryString.Append(" DECODE(U_MASTER.ID_FLAG, 'Y',BANK_BRANCH.BRANCH_ADDRS1 || ' ' || BANK_BRANCH.BRANCH_ADDRS2,U_MASTER.ADDRS2) AS ADDRS2,");
sbQueryString.Append(" DECODE(U_MASTER.ID_FLAG, 'Y', BANK_BRANCH.BRANCH_DISTRICT, U_MASTER.CITY) AS CITY, SALE.QTY,SALE.SL_TYPE ");
sbQueryString.Append(" FROM U_JHOLDER RIGHT OUTER JOIN BANK_NAME INNER JOIN BANK_BRANCH ON BANK_NAME.BANK_CODE = BANK_BRANCH.BANK_CODE RIGHT OUTER JOIN SALE INNER JOIN ");
sbQueryString.Append(" U_MASTER ON SALE.REG_BK = U_MASTER.REG_BK AND SALE.REG_BR = U_MASTER.REG_BR AND SALE.REG_NO = U_MASTER.REG_NO ON BANK_BRANCH.BANK_CODE = U_MASTER.ID_BK_NM_CD AND BANK_BRANCH.BRANCH_CODE = U_MASTER.ID_BK_BR_NM_CD ON");
sbQueryString.Append(" U_JHOLDER.REG_BK = U_MASTER.REG_BK AND U_JHOLDER.REG_BR = U_MASTER.REG_BR AND U_JHOLDER.REG_NO = U_MASTER.REG_NO");
sbQueryString.Append(" WHERE (SALE.SL_NO = " + Convert.ToInt32(SaleNoTextBox.Text.Trim().ToString()) + ") AND (SALE.REG_BK = '" + fundCode.ToString() + "') AND (SALE.REG_BR = '" + branchCode.ToString() + "')");
RenNoTextBox.Text = "";
TrNoTextBox.Text = "";
LineNoTextBox.Text = "";
}
else if (RenNoRadioButton.Checked)
{
sbQueryString.Append(" SELECT U_MASTER.REG_NO, U_MASTER.REG_BK || '/' || U_MASTER.REG_BR || '/' || U_MASTER.REG_NO AS REGI_NO,'RN: '||RENEWAL.REN_NO AS SL_NO,TO_CHAR(RENEWAL.REN_DT, 'DD-MON-YYYY') AS SL_DT,U_MASTER.ID_FLAG,");
sbQueryString.Append(" U_MASTER.HNAME,U_MASTER.FMH_NAME, U_MASTER.REG_BK, U_MASTER.REG_BR, U_JHOLDER.JNT_NAME, U_MASTER.ADDRS1, U_MASTER.ADDRS2,U_MASTER.CITY,RENEWAL.QTY,'REN' AS SL_TYPE ");
sbQueryString.Append(" FROM U_MASTER LEFT OUTER JOIN U_JHOLDER ON U_MASTER.REG_BK = U_JHOLDER.REG_BK AND U_MASTER.REG_BR = U_JHOLDER.REG_BR AND U_MASTER.REG_NO = U_JHOLDER.REG_NO LEFT OUTER JOIN");
sbQueryString.Append(" RENEWAL ON U_MASTER.REG_BK = RENEWAL.REG_BK AND U_MASTER.REG_BR = RENEWAL.REG_BR AND U_MASTER.REG_NO = RENEWAL.REG_NO");
sbQueryString.Append(" WHERE (RENEWAL.REN_NO = '" + RenNoTextBox.Text.Trim().ToString() + "') AND (RENEWAL.REG_BK = '" + fundCode.ToString() + "') AND (RENEWAL.REG_BR = '" + branchCode.ToString() + "')");
SaleNoTextBox.Text = "";
TrNoTextBox.Text = "";
LineNoTextBox.Text = "";
}
else if (TrNoRadioButton.Checked)
{
sbQueryString.Append(" SELECT TRANSFER.REG_BK_O || '/' || TRANSFER.REG_BR_O || '/' || TRANSFER.REG_NO_O AS TFEROR_REG_NO, U_MASTER.HNAME, U_MASTER.ID_FLAG,");
sbQueryString.Append(" U_JHOLDER.JNT_NAME, U_MASTER.ADDRS1 || ' ' || U_MASTER.ADDRS2 || ' ' || U_MASTER.CITY AS ADDRESS,U_MASTER.REG_BK || '/' || U_MASTER.REG_BR || '/' || U_MASTER.REG_NO AS TFREE_REG_NO, TRANSFER.TR_NO, TO_CHAR(TRANSFER.TR_DT, 'DD-MON-YYYY') AS TR_DT,");
sbQueryString.Append(" U_MASTER.ADDRS1,U_MASTER.ADDRS2,U_MASTER.CITY, U_MASTER.REG_NO,TRANSFER.QTY FROM TRANSFER INNER JOIN U_MASTER ON TRANSFER.REG_BK_I = U_MASTER.REG_BK AND TRANSFER.REG_BR_I = U_MASTER.REG_BR AND");
sbQueryString.Append(" TRANSFER.REG_NO_I = U_MASTER.REG_NO LEFT OUTER JOIN U_JHOLDER ON U_MASTER.REG_BK = U_JHOLDER.REG_BK AND U_MASTER.REG_BR = U_JHOLDER.REG_BR AND U_MASTER.REG_NO = U_JHOLDER.REG_NO");
sbQueryString.Append(" WHERE (TRANSFER.BR_CODE = '" + fundCode.ToString() + "_" + branchCode.ToString() + "') AND (TRANSFER.TR_NO = " + Convert.ToInt32(TrNoTextBox.Text.Trim().ToString()) + ") ");
SaleNoTextBox.Text = "";
RenNoTextBox.Text = "";
}
DataTable dtCertPrintReport = commonGatewayObj.Select(sbQueryString.ToString());
if (dtCertPrintReport.Rows.Count > 0)
{
displayHolderInformation(dtCertPrintReport);
}
else
{
ClearText();
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert('No Data Found');", true);
}
}
