public void displayRegInfo(UnitHolderRegistration unitRegObj)
{
dvLedger.Visible = true;
DataTable dtRegInfo = opendMFDAO.getDtRegInfo(unitRegObj);
DataTable dtNominee = opendMFDAO.dtNomineeRegInfo(unitRegObj);
if (dtRegInfo.Rows.Count > 0)
{
//Trasaction Lock Status
if (!(dtRegInfo.Rows[0]["ALL_LOCK"].Equals(DBNull.Value) || (dtRegInfo.Rows[0]["ALL_LOCK"].ToString() == "N")) || !(dtRegInfo.Rows[0]["SL_LOCK"].Equals(DBNull.Value) || (dtRegInfo.Rows[0]["SL_LOCK"].ToString() == "N")) || !(dtRegInfo.Rows[0]["REP_LOCK"].Equals(DBNull.Value) || (dtRegInfo.Rows[0]["REP_LOCK"].ToString() == "N")) || !(dtRegInfo.Rows[0]["TR_LOCK"].Equals(DBNull.Value) || (dtRegInfo.Rows[0]["TR_LOCK"].ToString() == "N")) || !(dtRegInfo.Rows[0]["LIEN_LOCK"].Equals(DBNull.Value) || (dtRegInfo.Rows[0]["LIEN_LOCK"].ToString() == "N")) || !(dtRegInfo.Rows[0]["REN_LOCK"].Equals(DBNull.Value) || (dtRegInfo.Rows[0]["REN_LOCK"].ToString() == "N")) || !(dtRegInfo.Rows[0]["LOCK_REMARKS"].Equals(DBNull.Value)))
{
dvLockin.Attributes.Add("style", "visibility:visible");
if (dtRegInfo.Rows[0]["ALL_LOCK"].ToString() == "Y")
{
SaleLockLabel.Text = "YES";
RepLockLabel.Text = "YES";
TransferLockLabel.Text = "YES";
LienLockLabel.Text = "YES";
RenLockLabel.Text = "YES";
LockRemarksTextBox.Text = dtRegInfo.Rows[0]["LOCK_REMARKS"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["LOCK_REMARKS"].ToString();
}
else
{
if (dtRegInfo.Rows[0]["SL_LOCK"].ToString() == "Y")
{
SaleLockLabel.Text = "YES";
}
else
{
SaleLockLabel.Text = "NO";
}
if (dtRegInfo.Rows[0]["REP_LOCK"].ToString() == "Y")
{
RepLockLabel.Text = "YES";
}
else
{
RepLockLabel.Text = "NO";
}
if (dtRegInfo.Rows[0]["TR_LOCK"].ToString() == "Y")
{
TransferLockLabel.Text = "YES";
}
else
{
TransferLockLabel.Text = "NO";
}
if (dtRegInfo.Rows[0]["LIEN_LOCK"].ToString() == "Y")
{
LienLockLabel.Text = "YES";
}
else
{
LienLockLabel.Text = "NO";
}
if (dtRegInfo.Rows[0]["REN_LOCK"].ToString() == "Y")
{
RenLockLabel.Text = "YES";
}
else
{
RenLockLabel.Text = "NO";
}
LockRemarksTextBox.Text = dtRegInfo.Rows[0]["LOCK_REMARKS"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["LOCK_REMARKS"].ToString();
}
}
else
{
SaleLockLabel.Text = "NO";
RepLockLabel.Text = "NO";
TransferLockLabel.Text = "NO";
LienLockLabel.Text = "NO";
RenLockLabel.Text = "NO";
dvLockin.Attributes.Add("style", "visibility:hidden");
}
regNoTextBox.Text = dtRegInfo.Rows[0]["REG_NO"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["REG_NO"].ToString();
holderBOTextBox.Text = dtRegInfo.Rows[0]["BO"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["BO"].ToString();
folioTextBox.Text = dtRegInfo.Rows[0]["FOLIO_NO"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["FOLIO_NO"].ToString();
holderNameTextBox.Text = dtRegInfo.Rows[0]["HNAME"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["HNAME"].ToString();
jHolderTextBox.Text = dtRegInfo.Rows[0]["JNT_NAME"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["JNT_NAME"].ToString();
holderAddress1TextBox.Text = dtRegInfo.Rows[0]["ADDRS1"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["ADDRS1"].ToString();
holderAddress2TextBox.Text = dtRegInfo.Rows[0]["ADDRS2"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["ADDRS2"].ToString();
holderTelphoneTextBox.Text = dtRegInfo.Rows[0]["TEL_NO"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["TEL_NO"].ToString();
tdTIN.InnerHtml = dtRegInfo.Rows[0]["TIN"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["TIN"].ToString();
string CIP = dtRegInfo.Rows[0]["CIP"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["CIP"].ToString();
string BEFTN = dtRegInfo.Rows[0]["IS_BEFTN"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["IS_BEFTN"].ToString();
if (string.Compare(CIP, "Y", true) == 0)
{
tdCIP.InnerHtml = "YES";
}
else if (string.Compare(CIP, "N", true) == 0)
{
tdCIP.InnerHtml = "NO";
}
else
{
tdCIP.InnerHtml = " ";
}
if (string.Compare(BEFTN, "Y", true) == 0)
{
tdBEFTN.InnerHtml = "YES";
}
else
{
tdBEFTN.InnerHtml = "NO";
}
if (dtNominee.Rows.Count > 1)
{
Nominee1NameTextBox.Text = dtNominee.Rows[0]["NOMI_NAME"].ToString();
Nominee2NameTextBox.Text = dtNominee.Rows[1]["NOMI_NAME"].ToString();
}
else if (dtNominee.Rows.Count > 0)
{
Nominee1NameTextBox.Text = dtNominee.Rows[0]["NOMI_NAME"].ToString();
Nominee2NameTextBox.Text = "";
}
else
{
Nominee1NameTextBox.Text = "";
Nominee2NameTextBox.Text = "";
}
RemarksTextBox.Text = dtRegInfo.Rows[0]["REMARKS"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["REMARKS"].ToString();
if (dtRegInfo.Rows[0]["BK_FLAG"].ToString() == "Y")
{
string bankInfo = "";
if (!dtRegInfo.Rows[0]["BK_NM_CD"].Equals(DBNull.Value) && !dtRegInfo.Rows[0]["BK_BR_NM_CD"].Equals(DBNull.Value) && !dtRegInfo.Rows[0]["BK_AC_NO"].Equals(DBNull.Value))
{
bankInfo = "AC:" + dtRegInfo.Rows[0]["BK_AC_NO"].ToString();
bankInfo = bankInfo + " , " + reportObj.getBankNameByBankCode(Convert.ToInt32(dtRegInfo.Rows[0]["BK_NM_CD"].ToString())).ToString();
bankInfo = bankInfo + " , " + reportObj.getBankBranchNameByCode(Convert.ToInt32(dtRegInfo.Rows[0]["BK_NM_CD"].ToString()), Convert.ToInt32(dtRegInfo.Rows[0]["BK_BR_NM_CD"].ToString())).ToString();
DataTable dtBankBracnhInfo = unitHolderRegBLObj.dtGetBankBracnhInfo(Convert.ToInt32(dtRegInfo.Rows[0]["BK_NM_CD"].ToString()), Convert.ToInt32(dtRegInfo.Rows[0]["BK_BR_NM_CD"].ToString()));
if (dtBankBracnhInfo.Rows.Count > 0)
{
bankInfo = bankInfo + " Routing No=[" + dtBankBracnhInfo.Rows[0]["ROUTING_NO"].ToString() + "] " + dtBankBracnhInfo.Rows[0]["ADDRESS"].ToString() + " ";
}
}
BankInfoTextBox.Text = bankInfo.ToString();
}
else
{
BankInfoTextBox.Text = "";
}
DataTable dtLedger = reportObj.GetLedgerData(unitRegObj);
int balance = 0;
if (dtLedger.Rows.Count > 0)
{
DataTable dtLedgerForReport = reportObj.GetDtLedgerTable();
DataRow drLedgerForReport;
for (int looper = 0; looper < dtLedger.Rows.Count; looper++)
{
drLedgerForReport = dtLedgerForReport.NewRow();
drLedgerForReport["SI"] = looper + 1;
if (!dtLedger.Rows[looper]["TRANS_DATE"].Equals(DBNull.Value))
{
drLedgerForReport["TRANS_DATE"] = Convert.ToDateTime(dtLedger.Rows[looper]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy");
}
drLedgerForReport["TRANS_NO"] = Convert.ToInt32(dtLedger.Rows[looper]["TRANS_NO"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["TRANS_NO"].ToString());
//drLedgerForReport["TRANS_NO"] = Convert.ToInt32(dtLedgerForReport.Rows[looper]["TRANS_DATE"].Equals(DBNull.Value) ? "0" : dtLedgerForReport.Rows[looper]["TRANS_DATE"].ToString());
if (!dtLedger.Rows[looper]["TRANS_TYPE"].Equals(DBNull.Value))
{
drLedgerForReport["TRANS_TYPE"] = dtLedger.Rows[looper]["TRANS_TYPE"].ToString();
if ((string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "SL", true) == 0) || (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "CIP", true) == 0))
{
drLedgerForReport["UNIT_CREDIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
drLedgerForReport["RATE"] = decimal.Parse((dtLedger.Rows[looper]["RATE"].ToString()));
int saleBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance + saleBalance;
drLedgerForReport["BALANCE"] = balance;
}
else if (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "TRI", true) == 0)
{
drLedgerForReport["UNIT_CREDIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
//drLedgerForReport["RATE"] = Convert.ToInt32(dtLedgerForReport.Rows[looper]["RATE"].ToString());
int transferInBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance + transferInBalance;
drLedgerForReport["BALANCE"] = balance;
}
else if (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "TRO", true) == 0)
{
drLedgerForReport["UNIT_DEBIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
//drLedgerForReport["RATE"] = Convert.ToInt32(dtLedgerForReport.Rows[looper]["RATE"].ToString());
int transferOutBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance - transferOutBalance;
drLedgerForReport["BALANCE"] = balance;
}
else if (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "REP", true) == 0)
{
drLedgerForReport["UNIT_DEBIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
drLedgerForReport["RATE"] = decimal.Parse((dtLedger.Rows[looper]["RATE"].ToString()));
int repurchaseBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance - repurchaseBalance;
drLedgerForReport["BALANCE"] = balance;
}
}
dtLedgerForReport.Rows.Add(drLedgerForReport);
}
TotalLienUnitHoldingTextBox.Text = unitLienBLObj.totalLienAmount(unitRegObj).ToString();
dgLedger.DataSource = dtLedgerForReport;
dgLedger.DataBind();
Session["dtLedgerForReport"] = dtLedgerForReport;
displaySign();
}
else
{
ClearText();
SignImage.ImageUrl = encrypt.PhotoBase64ImgSrc(Path.Combine(ConfigReader.SingLocation, "Notavailable.JPG").ToString());
dvLedger.Visible = false;
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert ('No Ledger Balance Data Found');", true);
}
}
else
{
SignImage.ImageUrl = encrypt.PhotoBase64ImgSrc(Path.Combine(ConfigReader.SingLocation, "Notavailable.JPG").ToString());
dvLedger.Visible = false;
ClientScript.RegisterStartupScript(this.GetType(), "Popup", " window.fnResetAll();", true);
tdCIP.InnerHtml = "";
}
}
protected void PrintButton_Click(object sender, EventArgs e)
{
bool flag = false;
string certType = "";
string investmentType = "ALL";
StringBuilder sbQueryString = new StringBuilder();
if (incomeTaxCertRadioButton.Checked)
{
DataTable dtIncomeTax = new DataTable();
certType = "IncomeTaxCert";
sbQueryString.Append("SELECT U_MASTER.REG_BK, U_MASTER.REG_BR, U_MASTER.REG_NO, FUND_INFO.FUND_NM, U_MASTER.HNAME, U_MASTER.ADDRS1, TAX_DIDUCT_RT AS TAX_RATE,");
sbQueryString.Append(" U_MASTER.ADDRS2, U_MASTER.CITY, U_JHOLDER.JNT_NAME, DIVI_PARA.F_YEAR, TO_CHAR(DIVI_PARA.CLOSE_DT,'DD-MON-YYYY')AS CLOSE_DT, DIVI_PARA.DIVI_NO,DECODE(DIVIDEND.CIP,'Y',TO_CHAR(DIVI_PARA.ISS_DT,'DD-MON-YYYY'),'')AS ISS_DT,");
sbQueryString.Append(" DIVI_PARA.FY_PART, DIVI_PARA.RATE, TO_CHAR(DIVI_PARA.AGM_DT,'DD-MON-YYYY') AS AGM_DT, DIVI_PARA.CIP_RATE, DIVIDEND.TOT_DIVI, DIVIDEND.DIDUCT,DIVIDEND.TOT_DIVI-DIVIDEND.DIDUCT AS NET_DIVIDEND,DECODE(UPPER(DIVIDEND.CIP), 'Y', DIVIDEND.FI_DIVI_QTY, 0) AS FRACTION_DIVI, ");
sbQueryString.Append(" DIVIDEND.FI_DIVI_QTY,DIVIDEND.CIP_QTY, DIVIDEND.BALANCE FROM U_MASTER INNER JOIN DIVIDEND INNER JOIN ");
sbQueryString.Append(" DIVI_PARA ON DIVIDEND.FUND_CD = DIVI_PARA.FUND_CD AND DIVIDEND.FY = DIVI_PARA.F_YEAR AND DIVIDEND.DIVI_NO = DIVI_PARA.DIVI_NO ON ");
sbQueryString.Append(" U_MASTER.REG_BR = DIVIDEND.REG_BR AND U_MASTER.REG_BK = DIVIDEND.REG_BK AND U_MASTER.REG_NO = DIVIDEND.REG_NO INNER JOIN");
sbQueryString.Append(" FUND_INFO ON U_MASTER.REG_BK = FUND_INFO.FUND_CD LEFT OUTER JOIN U_JHOLDER ON U_MASTER.REG_BK = U_JHOLDER.REG_BK AND U_MASTER.REG_BR = U_JHOLDER.REG_BR AND");
sbQueryString.Append(" U_MASTER.REG_NO = U_JHOLDER.REG_NO WHERE (U_MASTER.REG_NO = " + Convert.ToInt32(regNoTextBox.Text.Trim().ToString()) + ") AND (U_MASTER.REG_BR = '" + branchCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND ");
sbQueryString.Append(" (U_MASTER.REG_BK = '" + fundCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (DIVI_PARA.F_YEAR = '" + incomeTaxFYDropDownList.SelectedItem.Text.Trim().ToString() + "') AND (DIVI_PARA.FY_PART = '" + fyPartDropDownList.SelectedValue.ToString() + "')");
dtIncomeTax = commonGatewayObj.Select(sbQueryString.ToString());
if (dtIncomeTax.Rows.Count > 0)
{
Session["dtIncomeTax"] = dtIncomeTax;
Session["FY"] = incomeTaxFYDropDownList.SelectedItem.Text.ToString();
Session["REG_NO"] = Convert.ToInt32(regNoTextBox.Text.Trim().ToString());
}
else
{
flag = true;
}
}
else if (InvestCertRadioButton.Checked)
{
certType = "InvestCert";
sbQueryString = new StringBuilder();
sbQueryString.Append(" SELECT FUND_INFO.FUND_NM, U_MASTER.REG_BK, U_MASTER.REG_BR, U_MASTER.REG_NO, U_MASTER.HNAME, U_MASTER.ADDRS1, ");
sbQueryString.Append(" U_MASTER.ADDRS2, U_MASTER.CITY, U_JHOLDER.JNT_NAME FROM U_MASTER INNER JOIN FUND_INFO ON U_MASTER.REG_BK = FUND_INFO.FUND_CD LEFT OUTER JOIN ");
sbQueryString.Append(" 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 (U_MASTER.REG_BR = '" + branchCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (U_MASTER.REG_BK = '" + fundCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (U_MASTER.REG_NO = " + Convert.ToInt32(regNoTextBox.Text.Trim().ToString()) + ")");
DataTable dtInvestCertHolderInfo = commonGatewayObj.Select(sbQueryString.ToString());
sbQueryString = new StringBuilder();
sbQueryString.Append(" SELECT SL_NO, TO_CHAR(SL_DT, 'DD-MON-YYYY') AS SL_DT,SL_TYPE, QTY, SL_PRICE, QTY * SL_PRICE AS AMOUNT, REG_BK || '/' || REG_BR || '/' || REG_NO AS REG_NO");
sbQueryString.Append(" FROM SALE WHERE (REG_BR = '" + branchCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (REG_BK = '" + fundCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (REG_NO = " + Convert.ToInt32(regNoTextBox.Text.Trim().ToString()) + ") ");
sbQueryString.Append(" AND (SL_DT BETWEEN '" + Convert.ToDateTime(investFYFromTextBox.Text.Trim().ToString()).ToString("dd-MMM-yyyy") + "' AND '" + Convert.ToDateTime(investFYToTextBox.Text.Trim().ToString()).ToString("dd-MMM-yyyy") + "')");
if (CIPRadioButton.Checked)
{
investmentType = "CIP";
sbQueryString.Append(" AND SL_TYPE='CIP' ");
}
else if (NonCIPRadioButton.Checked)
{
investmentType = "NONCIP";
sbQueryString.Append(" AND SL_TYPE='SL' ");
}
DataTable dtInvestSaleInfo = commonGatewayObj.Select(sbQueryString.ToString());
sbQueryString = new StringBuilder();
sbQueryString.Append(" SELECT SUM(QTY) as TOTAL_UNIT, sum(QTY * SL_PRICE) AS TOTAL_AMOUNT");
sbQueryString.Append(" FROM SALE WHERE (REG_BR = '" + branchCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (REG_BK = '" + fundCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (REG_NO = " + Convert.ToInt32(regNoTextBox.Text.Trim().ToString()) + ") ");
sbQueryString.Append(" AND (SL_DT BETWEEN '" + Convert.ToDateTime(investFYFromTextBox.Text.Trim().ToString()).ToString("dd-MMM-yyyy") + "' AND '" + Convert.ToDateTime(investFYToTextBox.Text.Trim().ToString()).ToString("dd-MMM-yyyy") + "')");
if (CIPRadioButton.Checked)
{
sbQueryString.Append(" AND SL_TYPE='CIP' ");
}
else if (NonCIPRadioButton.Checked)
{
sbQueryString.Append(" AND SL_TYPE='SL' ");
}
DataTable dtInvestTotal = commonGatewayObj.Select(sbQueryString.ToString());
if (dtInvestCertHolderInfo.Rows.Count > 0 && dtInvestSaleInfo.Rows.Count > 0)
{
Session["dtInvestCertHolderInfo"] = dtInvestCertHolderInfo;
Session["dtInvestSaleInfo"] = dtInvestSaleInfo;
Session["dtInvestTotal"] = dtInvestTotal;
string FY = investFYFromTextBox.Text.Trim().ToString() + " to " + investFYToTextBox.Text.Trim().ToString();
Session["FY"] = FY.ToString();
Session["CloseDate"] = surrenderFYToTextBox.Text.ToString();
Session["investmentType"] = investmentType.ToString();
}
else
{
flag = true;
}
}
else if (surrenderRadioButton.Checked)
{
certType = "SurrendertCert";
sbQueryString = new StringBuilder();
sbQueryString.Append(" SELECT FUND_INFO.FUND_NM , U_MASTER.HNAME, U_MASTER.ADDRS1, U_MASTER.REG_BK || '/' || U_MASTER.REG_BR || '/' || U_MASTER.REG_NO AS REG_NO,");
sbQueryString.Append(" U_MASTER.ADDRS2, U_MASTER.CITY FROM U_MASTER INNER JOIN FUND_INFO ON U_MASTER.REG_BK = FUND_INFO.FUND_CD");
sbQueryString.Append(" WHERE (U_MASTER.REG_BR = '" + branchCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (U_MASTER.REG_BK = '" + fundCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (U_MASTER.REG_NO = " + Convert.ToInt32(regNoTextBox.Text.Trim().ToString()) + ")");
DataTable dtInvestCertHolderInfo = commonGatewayObj.Select(sbQueryString.ToString());
UnitHolderRegistration unitRegObj = new UnitHolderRegistration();
unitRegObj.FundCode = fundCodeTextBox.Text.Trim();
unitRegObj.BranchCode = branchCodeTextBox.Text.Trim();
unitRegObj.RegNumber = regNoTextBox.Text.Trim();
DataTable dtLedger = reportObj.GetLedgerData(unitRegObj);
DataTable dtLadgerForReport = reportObj.getTableForSurrenderTaxCert();
DataTable dtSurrender = reportObj.getTableForSurrender();
if (dtLedger.Rows.Count > 0)
{
int inBalance = 0;
int outBalance = 0;
int totalBalance = 0;
int surrnederBalance = 0;
string surrenderDate = "";
decimal surrenderRate = 0;
decimal saleAmount = 0;
decimal repAmount = 0;
string asOnDate = surrenderFYToTextBox.Text.Trim().ToString();
DataRow drReport;
for (int loop = 0; loop < dtLedger.Rows.Count; loop++)
{
drReport = dtLadgerForReport.NewRow();
if (Convert.ToDateTime(surrenderDateDropDownList.SelectedItem.Text.Trim().ToString()) == Convert.ToDateTime(Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy")) && (string.Compare(dtLedger.Rows[loop]["TRANS_TYPE"].ToString(), "REP", true) == 0))
{
surrnederBalance = surrnederBalance + Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
surrenderRate = Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString());
surrenderDate = Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy").ToString();
}
if (Convert.ToDateTime(surrenderFYToTextBox.Text.Trim().ToString()) >= Convert.ToDateTime(Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy")))
{
if ((string.Compare(dtLedger.Rows[loop]["TRANS_TYPE"].ToString(), "SL", true) == 0) || (string.Compare(dtLedger.Rows[loop]["TRANS_TYPE"].ToString(), "CIP", true) == 0))
{
drReport["TRANS_DATE"] = Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy").ToString();
drReport["REG_NO"] = dtInvestCertHolderInfo.Rows[0]["REG_NO"].ToString();
drReport["SALE_UNIT"] = Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
drReport["RATE"] = Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString());
drReport["SALE_AMOUNT"] = Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString()) * Convert.ToDecimal(dtLedger.Rows[loop]["QTY"].ToString());
saleAmount = saleAmount + Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString()) * Convert.ToDecimal(dtLedger.Rows[loop]["QTY"].ToString());
inBalance = inBalance + Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
}
else if (string.Compare(dtLedger.Rows[loop]["TRANS_TYPE"].ToString(), "TRI", true) == 0)
{
drReport["TRANS_DATE"] = Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy").ToString();
drReport["REG_NO"] = dtInvestCertHolderInfo.Rows[0]["REG_NO"].ToString();
drReport["TRI_UNIT"] = Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
inBalance = inBalance + Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
}
else if (string.Compare(dtLedger.Rows[loop]["TRANS_TYPE"].ToString(), "TRO", true) == 0)
{
drReport["TRANS_DATE"] = Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy").ToString();
drReport["REG_NO"] = dtInvestCertHolderInfo.Rows[0]["REG_NO"].ToString();
drReport["TRO_UNIT"] = Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
outBalance = outBalance + Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
}
else if (string.Compare(dtLedger.Rows[loop]["TRANS_TYPE"].ToString(), "REP", true) == 0)
{
drReport["TRANS_DATE"] = Convert.ToDateTime(dtLedger.Rows[loop]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy").ToString();
drReport["REG_NO"] = dtInvestCertHolderInfo.Rows[0]["REG_NO"].ToString();
drReport["REP_UNIT"] = Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
drReport["RATE"] = Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString());
drReport["REP_AMOUNT"] = Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString()) * Convert.ToDecimal(dtLedger.Rows[loop]["QTY"].ToString());
repAmount = repAmount + Convert.ToDecimal(dtLedger.Rows[loop]["RATE"].ToString()) * Convert.ToDecimal(dtLedger.Rows[loop]["QTY"].ToString());
outBalance = outBalance + Convert.ToInt32(dtLedger.Rows[loop]["QTY"].ToString());
}
dtLadgerForReport.Rows.Add(drReport);
}
}
if (surrnederBalance > 0)
{
drReport = dtSurrender.NewRow();
drReport["REP_DATE"] = surrenderDate;
drReport["REP_UNIT"] = surrnederBalance;
drReport["RATE"] = surrenderRate;
drReport["AMOUNT"] = surrenderRate * Convert.ToDecimal(surrnederBalance);
dtSurrender.Rows.Add(drReport);
}
if (dtSurrender.Rows.Count > 0)
{
totalBalance = inBalance - outBalance;
Session["totalBalance"] = totalBalance;
Session["inBalance"] = inBalance;
Session["outBalance"] = outBalance;
Session["dtLadgerForReport"] = dtLadgerForReport;
Session["dtSurrender"] = dtSurrender;
Session["dtInvestCertHolderInfo"] = dtInvestCertHolderInfo;
Session["asOnDate"] = asOnDate;
Session["repAmount"] = repAmount;
Session["saleAmount"] = saleAmount;
Session["ledgerAmount"] = saleAmount - repAmount;
}
else
{
flag = true;
}
}
else
{
flag = true;
}
}
else if (solventRadioButton.Checked)
{
certType = "SolventCert";
decimal totalUnitHolding = 0;
sbQueryString = new StringBuilder();
sbQueryString.Append(" SELECT FUND_INFO.FUND_NM, U_MASTER.REG_BK, U_MASTER.REG_BR, U_MASTER.REG_NO, U_MASTER.HNAME, U_MASTER.ADDRS1, U_MASTER.REG_BK || '/' || U_MASTER.REG_BR || '/' || U_MASTER.REG_NO AS REG_NUM,");
sbQueryString.Append(" U_MASTER.ADDRS2, U_MASTER.CITY , DECODE(U_JHOLDER.JNT_NAME, NULL, NULL, 'AND' || ' ' || U_JHOLDER.JNT_NAME) AS JNT_NAME FROM U_MASTER INNER JOIN FUND_INFO ON U_MASTER.REG_BK = FUND_INFO.FUND_CD 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 (U_MASTER.REG_BR = '" + branchCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (U_MASTER.REG_BK = '" + fundCodeTextBox.Text.Trim().ToString().ToUpper() + "') AND (U_MASTER.REG_NO = " + Convert.ToInt32(regNoTextBox.Text.Trim().ToString()) + ")");
DataTable dtInvestCertHolderInfo = commonGatewayObj.Select(sbQueryString.ToString());
UnitHolderRegistration unitRegObj = new UnitHolderRegistration();
unitRegObj.FundCode = fundCodeTextBox.Text.Trim();
unitRegObj.BranchCode = branchCodeTextBox.Text.Trim();
unitRegObj.RegNumber = regNoTextBox.Text.Trim();
if (CDSStatus == "Y")
{
totalUnitHolding = opendMFDAO.getTotalSaleUnitBalanceCDS(unitRegObj) + unitLienBLObj.totalLienAmount(unitRegObj);
}
else
{
totalUnitHolding = opendMFDAO.getTotalSaleUnitBalance(unitRegObj) + unitLienBLObj.totalLienAmount(unitRegObj);
}
if (dtInvestCertHolderInfo.Rows.Count > 0)
{
Session["dtInvestCertHolderInfo"] = dtInvestCertHolderInfo;
Session["totalUnitHolding"] = totalUnitHolding;
Session["USDRate"] = USDRateTextBox.Text.Trim().ToString();
Session["TTDate"] = TTDateTextBox.Text.Trim().ToString();
Session["RepRate"] = RepRateTextBox.Text.ToString();
}
else
{
flag = true;
}
}
else
{
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert('No Data Found');", true);
}
if (!flag)
{
Session["certType"] = certType;
Session["fundCode"] = fundCodeTextBox.Text.Trim().ToString();
Session["branchCode"] = branchCodeTextBox.Text.Trim().ToString();
ClientScript.RegisterStartupScript(this.GetType(), "UnitReportTaxCert", "window.open('ReportViewer/UnitReportTaxCertReportViewer.aspx')", true);
}
else
{
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert('No Data Found');", true);
}
}
public void displayRegInfo()
{
dvLedger.Visible = true;
UnitHolderRegistration unitRegObj = new UnitHolderRegistration();
unitRegObj.FundCode = fundCodeTextBox.Text.Trim();
unitRegObj.BranchCode = branchCodeTextBox.Text.Trim();
unitRegObj.RegNumber = regNoTextBox.Text.Trim();
DataTable dtRegInfo = opendMFDAO.getDtRegInfo(unitRegObj);
DataTable dtNominee = opendMFDAO.dtNomineeRegInfo(unitRegObj);
if (dtRegInfo.Rows.Count > 0)
{
holderNameTextBox.Text = dtRegInfo.Rows[0]["HNAME"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["HNAME"].ToString();
jHolderTextBox.Text = dtRegInfo.Rows[0]["JNT_NAME"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["JNT_NAME"].ToString();
holderAddress1TextBox.Text = dtRegInfo.Rows[0]["ADDRS1"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["ADDRS1"].ToString();
holderAddress2TextBox.Text = dtRegInfo.Rows[0]["ADDRS2"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["ADDRS2"].ToString();
holderTelphoneTextBox.Text = dtRegInfo.Rows[0]["TEL_NO"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["TEL_NO"].ToString();
string CIP = dtRegInfo.Rows[0]["CIP"].Equals(DBNull.Value) ? "" : dtRegInfo.Rows[0]["CIP"].ToString();
if (string.Compare(CIP, "Y", true) == 0)
{
tdCIP.InnerHtml = "YES";
}
else if (string.Compare(CIP, "N", true) == 0)
{
tdCIP.InnerHtml = "NO";
}
if (dtNominee.Rows.Count > 1)
{
Nominee1NameTextBox.Text = dtNominee.Rows[0]["NOMI_NAME"].ToString();
Nominee2NameTextBox.Text = dtNominee.Rows[1]["NOMI_NAME"].ToString();
}
else if (dtNominee.Rows.Count > 0)
{
Nominee1NameTextBox.Text = dtNominee.Rows[0]["NOMI_NAME"].ToString();
Nominee2NameTextBox.Text = "";
}
else
{
Nominee1NameTextBox.Text = "";
Nominee2NameTextBox.Text = "";
}
DataTable dtLedger = reportObj.GetLedgerData(unitRegObj);
int balance = 0;
if (dtLedger.Rows.Count > 0)
{
DataTable dtLedgerForReport = reportObj.GetDtLedgerTable();
DataRow drLedgerForReport;
for (int looper = 0; looper < dtLedger.Rows.Count; looper++)
{
drLedgerForReport = dtLedgerForReport.NewRow();
drLedgerForReport["SI"] = looper + 1;
if (!dtLedger.Rows[looper]["TRANS_DATE"].Equals(DBNull.Value))
{
drLedgerForReport["TRANS_DATE"] = Convert.ToDateTime(dtLedger.Rows[looper]["TRANS_DATE"].ToString()).ToString("dd-MMM-yyyy");
}
drLedgerForReport["TRANS_NO"] = Convert.ToInt32(dtLedger.Rows[looper]["TRANS_NO"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["TRANS_NO"].ToString());
//drLedgerForReport["TRANS_NO"] = Convert.ToInt32(dtLedgerForReport.Rows[looper]["TRANS_DATE"].Equals(DBNull.Value) ? "0" : dtLedgerForReport.Rows[looper]["TRANS_DATE"].ToString());
if (!dtLedger.Rows[looper]["TRANS_TYPE"].Equals(DBNull.Value))
{
drLedgerForReport["TRANS_TYPE"] = dtLedger.Rows[looper]["TRANS_TYPE"].ToString();
if ((string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "SL", true) == 0) || (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "CIP", true) == 0))
{
drLedgerForReport["UNIT_CREDIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
drLedgerForReport["RATE"] = decimal.Parse((dtLedger.Rows[looper]["RATE"].ToString()));
int saleBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance + saleBalance;
drLedgerForReport["BALANCE"] = balance;
}
else if (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "TRI", true) == 0)
{
drLedgerForReport["UNIT_CREDIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
//drLedgerForReport["RATE"] = Convert.ToInt32(dtLedgerForReport.Rows[looper]["RATE"].ToString());
int transferInBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance + transferInBalance;
drLedgerForReport["BALANCE"] = balance;
}
else if (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "TRO", true) == 0)
{
drLedgerForReport["UNIT_DEBIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
//drLedgerForReport["RATE"] = Convert.ToInt32(dtLedgerForReport.Rows[looper]["RATE"].ToString());
int transferOutBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance - transferOutBalance;
drLedgerForReport["BALANCE"] = balance;
}
else if (string.Compare(dtLedger.Rows[looper]["TRANS_TYPE"].ToString(), "REP", true) == 0)
{
drLedgerForReport["UNIT_DEBIT"] = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
drLedgerForReport["RATE"] = decimal.Parse((dtLedger.Rows[looper]["RATE"].ToString()));
int repurchaseBalance = Convert.ToInt32(dtLedger.Rows[looper]["QTY"].Equals(DBNull.Value) ? "0" : dtLedger.Rows[looper]["QTY"].ToString());
balance = balance - repurchaseBalance;
drLedgerForReport["BALANCE"] = balance;
}
}
dtLedgerForReport.Rows.Add(drLedgerForReport);
}
TotalLienUnitHoldingTextBox.Text = unitLienBLObj.totalLienAmount(unitRegObj).ToString();
dgLedger.DataSource = dtLedgerForReport;
dgLedger.DataBind();
Session["dtLedgerForReport"] = dtLedgerForReport;
displaySign();
}
else
{
ClearText();
SignImage.ImageUrl = Path.Combine(ConfigReader.SingLocation, "Notavailable.JPG").ToString();
dvLedger.Visible = false;
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Popup", "alert ('No Ledger Balance Data Found');", true);
}
}
else
{
SignImage.ImageUrl = Path.Combine(ConfigReader.SingLocation, "Notavailable.JPG").ToString();
dvLedger.Visible = false;
ClientScript.RegisterStartupScript(this.GetType(), "Popup", " window.fnResetAll();", true);
tdCIP.InnerHtml = "";
}
}