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);
}
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);
}
internal BankruptcyData GetBankruptcyDataBySGID(string submissionGroupId)
{
BankruptcyData bankruptcyData = new BankruptcyData();
CommonGateway commongateway = new CommonGateway();
bankruptcyData.BankruptcyInfoEntity = GetBankruptcyInfoBySGID(submissionGroupId);
bankruptcyData.ListBankruptcyOtherDefDetailEntity = GetListBankruptcyOtherDefDetailBySGID(submissionGroupId);
bankruptcyData.ListLegislationProvisionsEntity = commongateway.GetListLegislationProvisionsEntityBySGID(submissionGroupId);
bankruptcyData.ListNatureOfCaseEntity = commongateway.GetListNatureOfCaseEntityBySGID(submissionGroupId);
bankruptcyData.ListOtherITMOEntity = commongateway.GetListOtherITMOEntityBySGID(submissionGroupId);
bankruptcyData.ListPrayerEntity = commongateway.GetListPrayerEntityBySGID(submissionGroupId);
return bankruptcyData;
}
public DataTable pdateNegativeBalanceCheckDropDownList()
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable pdate = commonGatewayObj.Select("select distinct bal_dt from fund_folio_hb order by bal_dt desc");
DataTable pdateNegativeBalanceCheckDropDownList = new DataTable();
pdateNegativeBalanceCheckDropDownList.Columns.Add("p1date", typeof(string));
pdateNegativeBalanceCheckDropDownList.Columns.Add("p2date", typeof(string));
DataRow dr = pdateNegativeBalanceCheckDropDownList.NewRow();
for (int loop = 0; loop < pdate.Rows.Count; loop++)
{
dr = pdateNegativeBalanceCheckDropDownList.NewRow();
dr["p1date"] = Convert.ToDateTime(pdate.Rows[loop]["bal_dt"]).ToString("dd-MMM-yyyy");
dr["p2date"] = Convert.ToDateTime(pdate.Rows[loop]["bal_dt"]).ToString("dd-MMM-yyyy");
pdateNegativeBalanceCheckDropDownList.Rows.Add(dr);
}
return(pdateNegativeBalanceCheckDropDownList);
}
public DataTable pdateDropDownList()//For Authorized Signatory
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable pdate = commonGatewayObj.Select("select max(vch_dt) as vch_dt from fund_trans_hb");
DataTable pdateDropDownList = new DataTable();
pdateDropDownList.Columns.Add("p1date", typeof(string));
pdateDropDownList.Columns.Add("p2date", typeof(string));
DataRow dr = pdateDropDownList.NewRow();
for (int loop = 0; loop < pdate.Rows.Count; loop++)
{
dr = pdateDropDownList.NewRow();
dr["p1date"] = Convert.ToDateTime(pdate.Rows[loop]["vch_dt"]).ToString("dd-MMM-yyyy");
dr["p2date"] = Convert.ToDateTime(pdate.Rows[loop]["vch_dt"]).ToString("dd-MMM-yyyy");
pdateDropDownList.Rows.Add(dr);
}
return(pdateDropDownList);
}
public string UpdateAmmountInNonlisted(string FundId, string Date, string Ammount)
{
CommonGateway commonGatewayObj = new CommonGateway();
string strUpdateNonlisted = "";
string message = "";
if (FundId != "")
{
strUpdateNonlisted = "UPDATE NON_LISTED_SECURITIES SET INV_AMOUNT = '" + Convert.ToDouble(Ammount) + "' WHERE F_CD = " + FundId + " and INV_DATE ='" + Date + "'";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strUpdateNonlisted);
if (NumOfRows > 0)
{
message = "Data Updated Successfully !!!";
}
}
return(message);
}
public JsonResult UserLogin(CommonGateway oCommonGateway)
{
if (oCommonGateway.UserID == "admin1")
{
Session.Add("ConnectionString", "Server=209.151.194.144,8494;Database=sms_activitylog_db;User ID=aliakramtushar;password=2sR01676555291;Integrated Security=false");
}
else if (oCommonGateway.UserID == "admin2")
{
Session.Add("ConnectionString", "Server=209.151.194.144,8494;Database=sms_chandpurelectric_db_test;User ID=aliakramtushar;password=2sR01676555291;Integrated Security=false");
}
else
{
string sTemp = "Server=" + oCommonGateway.Server + ";Database=" + oCommonGateway.Database + ";User ID=" + oCommonGateway.UserID + ";password="******";Integrated Security=false";
Session.Add("ConnectionString", sTemp);
}
SessionObj oSessionobj = new SessionObj(Session);
JavaScriptSerializer serializer = new JavaScriptSerializer();
string sjson = serializer.Serialize("Successful");
return(Json(sjson, JsonRequestBehavior.AllowGet));
}
public static bool InsertandUpdateUser(string userId, string Password, string confirmPassword)
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable dtgetUser;
string passWord = "";
if (userId != "")
{
string strUserID = "select * from user_table where user_id='" + userId + "'";
dtgetUser = commonGatewayObj.Select(strUserID);
if (dtgetUser != null && dtgetUser.Rows.Count > 0)
{
string EncryptionKey = "MAKV2SPBNI99212";
byte[] clearBytes = Encoding.Unicode.GetBytes(Password);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
cs.Close();
}
passWord = Convert.ToBase64String(ms.ToArray());
}
}
string strUPQuery = "update USER_TABLE set PASSWORD ='******' where USER_ID ='" + userId + "'";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strUPQuery);
}
}
return(true);
}
/// <summary>
/// Method to Get <see cref="AppealsData"/> based on submissionGroupId.
/// </summary>
/// <param name="submissionGroupId">The submission group id.</param>
/// <returns>Returns the <see cref="AppealsData"/> object.</returns>
internal AppealsData GetAppealsData(string submissionGroupId)
{
AppealsData appealsData;
try
{
AppealsGateway appealsGateway = new AppealsGateway();
CommonGateway commonGateway = new CommonGateway();
appealsData = new AppealsData();
appealsData.DCACAInfoEntity = appealsGateway.GetDCACAInfoEntityBySGID(submissionGroupId);
appealsData.ListLegislationProvisionsEntity = commonGateway.GetListLegislationProvisionsEntityBySGID(submissionGroupId);
appealsData.ListNatureOfCaseEntity = commonGateway.GetListNatureOfCaseEntityBySGID(submissionGroupId);
appealsData.ListRefOrderDetailEntity = commonGateway.GetListRefOrderDetailEntityBySGID(submissionGroupId);
}
catch (Exception ex)
{
Logger.Error("Error at GetAppealsData", ex);
throw;
}
return appealsData;
}
public static bool InsertandUpdateUser(string userId, string useName, string UserDesignation, string Password, string confirmPassword, string userRole, string EMPID)
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable dtgetUser;
string passWord = "";
if (userId != "")
{
string strUserID = "select * from user_table where user_id='" + userId + "'";
dtgetUser = commonGatewayObj.Select(strUserID);
if (dtgetUser != null && dtgetUser.Rows.Count > 0)
{
string EncryptionKey = "MAKV2SPBNI99212";
byte[] clearBytes = Encoding.Unicode.GetBytes(Password);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
cs.Close();
}
passWord = Convert.ToBase64String(ms.ToArray());
}
}
string strUPQuery = "update USER_TABLE set PASSWORD ='******',NAME ='" + useName + "',DESIGNATION='" + UserDesignation + "',ROLE_ID ='" + userRole + "',EMP_ID='" + EMPID + "' where USER_ID ='" + userId + "'";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strUPQuery);
}
else
{
string strInsQuery;
DataTable dtID;
string EncryptionKey = "MAKV2SPBNI99212";
byte[] clearBytes = Encoding.Unicode.GetBytes(Password);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
cs.Close();
}
passWord = Convert.ToBase64String(ms.ToArray());
}
}
string strQuery = "select max(ID)+1 as ID from USER_TABLE";
dtID = commonGatewayObj.Select(strQuery);
strInsQuery = "insert into USER_TABLE(ID,USER_ID,PASSWORD,NAME,DESIGNATION,ROLE_ID,EMP_ID)values(" + dtID.Rows[0]["ID"].ToString() + ",'" + userId + "','" + passWord + "','" + useName + "','" + UserDesignation + "','" + userRole + "','" + EMPID + "')";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strInsQuery);
}
}
return(true);
}
public static bool InsertandUpdateCompany(string CompanyCode, string companyName, string dsecode, string PaidUpCapital, string atho_cap, string totalshare, string faceValue, string MarketLot, string sector, string product, string category, string avarageMarketRate, string baserate, string baseupdateDate, string lasttradingdate, string flug, string group, string floatdatefrom, string floatdateto, string csecode, string address1, string address2, string regoffice, string phnNo, string openingdate, string premium, string RIssuefrom, string RIssueto, string mergin, string IsBuySellChargeApplicable, string Additionalbuysellcharge, string AdditionalbuysellCommision, string propectusPublishDate, string IpoType, string marketType, string ISSUE_MNG)
{
CommonGateway commonGatewayObj = new CommonGateway();
DataTable dtsource = new DataTable();
string strUPQuery = "";
string strInsQuery = "";
string strIsBuySellChargeApplicable = "";
string strAdditionalbuysellcharge = "";
string strAdditionalbuysellCommision = "";
string strLastTradindate, stropeningdate, strbaseupdatedDate, strfloatdatefromdate, strfloatdateTodate, strRIssuefromdate, strRIssuetodate, strProsPublishDate, strgroup;
string strcategory, strIpoType, strmarketType;
DateTime?dtimeLastTradindate, dtimeopeningdate, dtimebaseUpdateddate, dtpropectuspublishingdate, dtimefloatdatefromdate, dtimefloatdateTo, dtimeRIssuefromfromdate, dtimeRIssuetodate;
string userId = (string)HttpContext.Current.Session["UserID"].ToString();
DateTime dtimeCurrentDateTimeForLog = DateTime.Now;
string strCurrentDateTimeForLog = dtimeCurrentDateTimeForLog.ToString("dd-MMM-yyyy HH:mm:ss tt", CultureInfo.InvariantCulture);
if (!string.IsNullOrEmpty(lasttradingdate))
{
dtimeLastTradindate = Convert.ToDateTime(lasttradingdate);
strLastTradindate = dtimeLastTradindate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeLastTradindate = null;
strLastTradindate = "";
}
if (!string.IsNullOrEmpty(openingdate))
{
dtimeopeningdate = Convert.ToDateTime(openingdate);
stropeningdate = dtimeopeningdate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeopeningdate = null;
stropeningdate = "";
}
if (!string.IsNullOrEmpty(baseupdateDate))
{
dtimebaseUpdateddate = Convert.ToDateTime(baseupdateDate);
strbaseupdatedDate = dtimebaseUpdateddate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimebaseUpdateddate = null;
strbaseupdatedDate = "";
}
if (!string.IsNullOrEmpty(propectusPublishDate))
{
dtpropectuspublishingdate = Convert.ToDateTime(propectusPublishDate);
strProsPublishDate = dtpropectuspublishingdate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtpropectuspublishingdate = null;
strProsPublishDate = "";
}
if (!string.IsNullOrEmpty(floatdatefrom))
{
dtimefloatdatefromdate = Convert.ToDateTime(floatdatefrom);
strfloatdatefromdate = dtimefloatdatefromdate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimefloatdatefromdate = null;
strfloatdatefromdate = "";
}
if (!string.IsNullOrEmpty(floatdateto))
{
dtimefloatdateTo = Convert.ToDateTime(floatdateto);
strfloatdateTodate = dtimefloatdateTo.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimefloatdateTo = null;
strfloatdateTodate = "";
}
if (!string.IsNullOrEmpty(RIssuefrom))
{
dtimeRIssuefromfromdate = Convert.ToDateTime(RIssuefrom);
strRIssuefromdate = dtimeRIssuefromfromdate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeRIssuefromfromdate = null;
strRIssuefromdate = "";
}
if (!string.IsNullOrEmpty(RIssueto))
{
dtimeRIssuetodate = Convert.ToDateTime(RIssueto);
strRIssuetodate = dtimeRIssuetodate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeRIssuetodate = null;
strRIssuetodate = "";
}
if (IsBuySellChargeApplicable == "undefined")
{
strIsBuySellChargeApplicable = "";
}
else
{
strIsBuySellChargeApplicable = IsBuySellChargeApplicable;
}
if (Additionalbuysellcharge == "undefined")
{
strAdditionalbuysellcharge = "";
}
else
{
strAdditionalbuysellcharge = Additionalbuysellcharge;
}
if (AdditionalbuysellCommision == "undefined")
{
strAdditionalbuysellCommision = "";
}
else
{
strAdditionalbuysellCommision = Additionalbuysellcharge;
}
if (category == "0")
{
strcategory = "";
}
else
{
strcategory = category;
}
if (group == "0")
{
strgroup = "";
}
else
{
strgroup = group;
}
if (IpoType == "0")
{
strIpoType = "";
}
else
{
strIpoType = IpoType;
}
if (marketType == "0")
{
strmarketType = "";
}
else
{
strmarketType = marketType;
}
if (CompanyCode != "")
{
string Query = "select comp_nm,mlot,fc_val,avg_rt,rt_upd_dt,instr_cd,cseinstr_cd,trade_meth,sect_maj_cd,cat_tp,add1,add2,reg_off,opn_dt,tel,prod,atho_cap,paid_cap,no_shrs,sbase_rt,base_upd_dt,flot_dt_fm,flot_dt_to,margin,flag,premium,rissu_dt_fm,rissu_dt_to, UPD_DATE_TIME, OP_NAME, ISSUE_MNG, MARKETTYPE,IPOTYPE, PROS_PUB_DT from comp where comp_cd ='" + CompanyCode + "'";
dtsource = commonGatewayObj.Select(Query.ToString());
if (dtsource.Rows.Count > 0)
{
DataTable companyinfoupdate = new DataTable();
if (IsBuySellChargeApplicable == "N")
{
string strDlQuery = "update comp set ISADD_HOWLACHARGE_DSE =null,ADD_HOWLACHARGE_AMTDSE =0,EXCEP_BUYSL_COMPCT_DSE =0 where comp_cd =" + CompanyCode.ToString() + "";
int delNumOfRows = commonGatewayObj.ExecuteNonQuery(strDlQuery);
strUPQuery = "update comp set instr_cd ='" + dsecode.ToString() + "',comp_nm ='" + companyName.ToString() + "',cseinstr_cd ='" + csecode.ToString() + "',trade_meth ='" + strgroup.ToString() + "',add1 ='" + address1.ToString() + "',add2 ='" + address2.ToString() + "',reg_off ='" + regoffice.ToString() + "',tel ='" + phnNo.ToString() + "',atho_cap = '" + atho_cap.ToString() + "',paid_cap =" + PaidUpCapital.ToString() + ",no_shrs =" + totalshare.ToString() + ",fc_val ='" + faceValue.ToString() + "',mlot ='" + MarketLot.ToString() + "',flot_dt_fm ='" + strfloatdatefromdate + "',flot_dt_to ='" + strfloatdateTodate + "',rissu_dt_fm ='" + strRIssuefromdate + "',rissu_dt_to ='" + strRIssuetodate + "',sbase_rt =" + baserate.ToString() + ",base_upd_dt ='" + strbaseupdatedDate + "',avg_rt ='" + avarageMarketRate.ToString() + "',rt_upd_dt ='" + strLastTradindate + "',margin = '" + mergin.ToString() + "',PROD='" + product + "',premium ='" + premium.ToString() + "',sect_maj_cd ='" + sector.ToString() + "',opn_dt ='" + stropeningdate + "',cat_tp ='" + strcategory.ToString() + "',PROS_PUB_DT='" + strProsPublishDate + "',IPOTYPE='" + strIpoType + "',MARKETTYPE='" + strmarketType + "',ISSUE_MNG='" + ISSUE_MNG + "',OP_NAME='" + userId + "',UPD_DATE_TIME='" + strCurrentDateTimeForLog + "' where comp_cd =" + CompanyCode.ToString() + "";
}
else
{
strUPQuery = "update comp set instr_cd ='" + dsecode.ToString() + "',comp_nm ='" + companyName.ToString() + "',cseinstr_cd ='" + csecode.ToString() + "',trade_meth ='" + strgroup.ToString() + "',add1 ='" + address1.ToString() + "',add2 ='" + address2.ToString() + "',reg_off ='" + regoffice.ToString() + "',tel ='" + phnNo.ToString() + "',atho_cap = '" + atho_cap.ToString() + "',paid_cap =" + PaidUpCapital.ToString() + ",no_shrs =" + totalshare.ToString() + ",fc_val ='" + faceValue.ToString() + "',mlot ='" + MarketLot.ToString() + "',flot_dt_fm ='" + strfloatdatefromdate + "',flot_dt_to ='" + strfloatdateTodate + "',rissu_dt_fm ='" + strRIssuefromdate + "',rissu_dt_to ='" + strRIssuetodate + "',sbase_rt =" + baserate.ToString() + ",base_upd_dt ='" + strbaseupdatedDate + "',avg_rt ='" + avarageMarketRate.ToString() + "',rt_upd_dt ='" + strLastTradindate + "',margin = '" + mergin.ToString() + "',PROD='" + product + "',premium ='" + premium.ToString() + "',sect_maj_cd ='" + sector.ToString() + "',opn_dt ='" + stropeningdate + "',cat_tp ='" + strcategory.ToString() + "',ISADD_HOWLACHARGE_DSE ='" + strIsBuySellChargeApplicable.ToString() + "',ADD_HOWLACHARGE_AMTDSE ='" + strAdditionalbuysellcharge.ToString() + "',EXCEP_BUYSL_COMPCT_DSE ='" + strAdditionalbuysellCommision.ToString() + "',PROS_PUB_DT='" + strProsPublishDate + "',IPOTYPE='" + strIpoType + "',MARKETTYPE='" + strmarketType + "',ISSUE_MNG='" + ISSUE_MNG + "',OP_NAME='" + userId + "',UPD_DATE_TIME='" + strCurrentDateTimeForLog + "' where comp_cd =" + CompanyCode.ToString() + "";
}
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strUPQuery);
return(true);
}
else
{
DataTable companyinfo = new DataTable();
if (IsBuySellChargeApplicable == "N")
{
strInsQuery = "insert into comp(comp_cd,comp_nm,instr_cd,paid_cap,no_shrs,ATHO_CAP,fc_val,mlot, sbase_rt, base_upd_dt, avg_rt,rt_upd_dt,cat_tp,sect_maj_cd,trade_meth,flot_dt_fm,flot_dt_to,cseinstr_cd,flag,add1,add2,reg_off,tel,opn_dt,premium,rissu_dt_fm,rissu_dt_to,margin,PROS_PUB_DT,IPOTYPE,MARKETTYPE,ISSUE_MNG,OP_NAME,UPD_DATE_TIME)values(" + CompanyCode + ",'" + companyName.ToString() + "','" + dsecode.ToString() + "','" + Convert.ToDouble(PaidUpCapital) + "','" + totalshare + "','" + atho_cap + "','" + Convert.ToUInt32(faceValue.ToString()) + "','" + MarketLot + "','" + Convert.ToDouble(baserate) + "','" + strbaseupdatedDate + "','" + avarageMarketRate + "','" + strLastTradindate + "','" + strcategory + "','" + sector.ToString() + "','" + strgroup + "','" + strfloatdatefromdate + "','" + strfloatdatefromdate + "','" + csecode + "','" + flug + "','" + address1.ToString() + "','" + address2.ToString() + "','" + regoffice.ToString() + "','" + phnNo.ToString() + "','" + stropeningdate + "','" + premium.ToString() + "','" + strRIssuefromdate + "','" + strRIssuetodate + "','" + mergin.ToString() + "','" + strProsPublishDate + "','" + strIpoType + "','" + strmarketType + "','" + ISSUE_MNG + "','" + userId + "','" + strCurrentDateTimeForLog + "')";
}
else
{
strInsQuery = "insert into comp(comp_cd,comp_nm,instr_cd,paid_cap,no_shrs,ATHO_CAP,fc_val,mlot, sbase_rt, base_upd_dt, avg_rt,rt_upd_dt,cat_tp,sect_maj_cd,trade_meth,flot_dt_fm,flot_dt_to,cseinstr_cd,flag,add1,add2,reg_off,tel,opn_dt,premium,rissu_dt_fm,rissu_dt_to,margin,ISADD_HOWLACHARGE_DSE,ADD_HOWLACHARGE_AMTDSE,EXCEP_BUYSL_COMPCT_DSE,PROS_PUB_DT,IPOTYPE,MARKETTYPE,ISSUE_MNG,OP_NAME,UPD_DATE_TIME)values(" + CompanyCode + ",'" + companyName.ToString() + "','" + dsecode.ToString() + "','" + Convert.ToDouble(PaidUpCapital) + "','" + totalshare + "','" + atho_cap + "','" + Convert.ToUInt32(faceValue.ToString()) + "','" + MarketLot + "','" + Convert.ToDouble(baserate) + "','" + strbaseupdatedDate + "','" + avarageMarketRate + "','" + strLastTradindate + "','" + strcategory + "','" + sector.ToString() + "','" + strgroup + "','" + strfloatdatefromdate + "','" + strfloatdateTodate + "','" + csecode + "','" + flug + "','" + address1.ToString() + "','" + address2.ToString() + "','" + regoffice.ToString() + "','" + phnNo.ToString() + "','" + stropeningdate + "','" + premium.ToString() + "','" + strRIssuefromdate + "','" + strRIssuetodate + "','" + mergin.ToString() + "','" + strIsBuySellChargeApplicable.ToString() + "','" + strAdditionalbuysellcharge.ToString() + "','" + strAdditionalbuysellCommision.ToString() + "','" + strProsPublishDate + "','" + strIpoType + "','" + strmarketType + "','" + ISSUE_MNG + "','" + userId + "','" + strCurrentDateTimeForLog + "')";
}
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strInsQuery);
return(true);
}
}
return(false);
}
public void Save(DataTable dt)
{
string strBalanceDate, strLastBalDate, strLastUpadateDate, strLastUpadatePlusOneDate, strUpdateFundTransHB, strDelFromFundFolioHB, strMarketPriceDate, temp;
DateTime? dtimeBalanceDate, dtimeLastBalDate, dtimeLastUpadateDate, dtimeLastUpadatePlusOneDate, dtMarketPriceDate;
DataTable dtFromFundTransHB = new DataTable();
DataTable dtFromFundFolioHB = new DataTable();
CommonGateway commonGatewayObj = new CommonGateway();
DataTable dtSource = new DataTable();
DataTable dtSource2 = new DataTable();
DataTable tblAllfundInfo = dt;
List <string> fundBalanceDate = (from row in tblAllfundInfo.AsEnumerable()
select row["LastUpadateDate"].ToString()).ToList();
string lastupdateddate = "01-JUL-2002";
if (fundBalanceDate.Contains(lastupdateddate.ToString()))
{
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert('Process will be started from July 2002.');", true);
}
for (int i = 0; i < tblAllfundInfo.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(tblAllfundInfo.Rows[i]["BalanceDate"].ToString()))
{
dtimeBalanceDate = Convert.ToDateTime(tblAllfundInfo.Rows[i]["BalanceDate"].ToString());
strBalanceDate = dtimeBalanceDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeBalanceDate = null;
strBalanceDate = "";
}
if (!string.IsNullOrEmpty(tblAllfundInfo.Rows[i]["LastBalDate"].ToString()))
{
dtimeLastBalDate = Convert.ToDateTime(tblAllfundInfo.Rows[i]["LastBalDate"].ToString());
strLastBalDate = dtimeLastBalDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeLastBalDate = null;
strLastBalDate = "";
}
if (!string.IsNullOrEmpty(tblAllfundInfo.Rows[i]["LastUpadateDate"].ToString()))
{
dtimeLastUpadateDate = Convert.ToDateTime(tblAllfundInfo.Rows[i]["LastUpadateDate"].ToString());
strLastUpadateDate = dtimeLastUpadateDate.Value.ToString("dd-MMM-yyyy");
dtimeLastUpadatePlusOneDate = dtimeLastUpadateDate.Value.AddDays(1);
strLastUpadatePlusOneDate = dtimeLastUpadatePlusOneDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeLastUpadateDate = null;
strLastUpadateDate = "";
dtimeLastUpadatePlusOneDate = null;
strLastUpadatePlusOneDate = "";
}
if (!string.IsNullOrEmpty(tblAllfundInfo.Rows[i]["MarketPriceDate"].ToString()))
{
dtMarketPriceDate = Convert.ToDateTime(tblAllfundInfo.Rows[i]["MarketPriceDate"].ToString());
strMarketPriceDate = dtMarketPriceDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtMarketPriceDate = null;
strMarketPriceDate = "";
}
if (dtimeBalanceDate > dtimeLastBalDate)
{
temp = adv_proc1(strLastUpadatePlusOneDate, strBalanceDate, tblAllfundInfo.Rows[i]["F_CD"].ToString());
}
else
{
commonGatewayObj.BeginTransaction();
strDelFromFundFolioHB = "delete from fund_folio_hb where f_cd =" + tblAllfundInfo.Rows[i]["F_CD"].ToString();
int noDelRowsFromFundFolioHB = commonGatewayObj.ExecuteNonQuery(strDelFromFundFolioHB);
strUpdateFundTransHB = "update fund_trans_hb set cost_rate = null,crt_aft_com = null where f_cd =" + tblAllfundInfo.Rows[i]["F_CD"].ToString();
int noUpdRowsFundTransHB = commonGatewayObj.ExecuteNonQuery(strUpdateFundTransHB);
commonGatewayObj.CommitTransaction();
temp = adv_proc1(strLastUpadatePlusOneDate, strBalanceDate, tblAllfundInfo.Rows[i]["F_CD"].ToString());
}
string strCompnayTransdate = "select a.comp_cd, max(a.tran_date) tran_date from market_price a, fund_folio_hb b where a.comp_cd = b.comp_cd and b.f_cd =" + tblAllfundInfo.Rows[i]["F_CD"].ToString() + " and a.tran_date <= '" + strBalanceDate + "' group by a.comp_cd order by a.comp_cd";
dtSource = commonGatewayObj.Select(strCompnayTransdate);
List <CompanayTransdate> lstCompnayTransdate = new List <CompanayTransdate>();
List <CompanyAvarageRate> lstCompnayAvgrate = new List <CompanyAvarageRate>();
lstCompnayTransdate = (from DataRow dr in dtSource.Rows
select new CompanayTransdate()
{
COMP_CD = dr["COMP_CD"].ToString(),
TRAN_DATE = dr["TRAN_DATE"].ToString()
}).ToList();
string dltQuery = "delete from mprice_temp where f_cd=" + tblAllfundInfo.Rows[i]["F_CD"].ToString();
int dltNumOfRows = commonGatewayObj.ExecuteNonQuery(dltQuery);
foreach (CompanayTransdate comtransdate in lstCompnayTransdate)
{
string strInsQuery = "select " + tblAllfundInfo.Rows[i]["F_CD"].ToString() + " as FundId, comp_cd, avg_rt from market_price where comp_cd = " + comtransdate.COMP_CD + " and tran_date = '" + Convert.ToDateTime(comtransdate.TRAN_DATE).ToString("dd-MMM-yyyy") + "'";
dtSource2 = commonGatewayObj.Select(strInsQuery);
lstCompnayAvgrate = (from DataRow dr in dtSource2.Rows
select new CompanyAvarageRate()
{
FUNDID = dr["FUNDID"].ToString(),
COMP_CD = dr["COMP_CD"].ToString(),
AVG_RT = dr["AVG_RT"].ToString()
}).ToList();
foreach (CompanyAvarageRate comAvgrate in lstCompnayAvgrate)
{
string strQueryInsMprice_Temp = "insert into mprice_temp(f_cd, comp_cd, avg_rt) values ('" + comAvgrate.FUNDID + "','" + comAvgrate.COMP_CD + "','" + comAvgrate.AVG_RT + "')";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strQueryInsMprice_Temp);
}
}
string LoginID = Session["UserID"].ToString();
DateTime dtimeCurrentDateTimeForLog = DateTime.Now;
string strCurrentDateTimeForLog = dtimeCurrentDateTimeForLog.ToString("dd-MMM-yyyy HH:mm:ss tt", CultureInfo.InvariantCulture);
string strupdateQueryfund_control = "update fund_control set op_name='" + LoginID + "',upd_date_time='" + strCurrentDateTimeForLog + "',bal_dt='" + strBalanceDate + "',mprice_dt='" + strMarketPriceDate + "' where f_cd =" + tblAllfundInfo.Rows[i]["F_CD"].ToString() + "";
int updatefund_controlNumOfRows = commonGatewayObj.ExecuteNonQuery(strupdateQueryfund_control);
if (temp.Trim() == "Processing Completed" || temp.Trim() == "")
{
lblProcessing.Text = "Processing completed!!!!";
// ClearFields();
}
else
{
lblProcessing.Text = "No data found!!!!";
// ClearFields();
}
}
}
public CommonController()
{
commonGateway = new CommonGateway();
}
protected void Page_Load(object sender, EventArgs e)
{
string strFromdate = "";
string strTodate = "";
string fundCodes = "";
string strSQLForMainReport, strSQLForSubReport;
//string companyCodes = "";
//string percentageCheck = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
strFromdate = (string)Session["FromDate"];
strTodate = (string)Session["ToDate"];
fundCodes = (string)Session["fundCodes"];
}
CommonGateway commonGatewayObj = new CommonGateway();
// DataTable dtReprtSource = new DataTable();
DataTable dtRptSrcMainReport = new DataTable();
DataTable dtRptSrcSubReport = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
//strSQLForMainReport = "SELECT p.f_cd1,q.f_cd2,p.fund_name1,q.fund_name2,p.COST1,q.COST2,q.SALE2,p.No_Of_Share1,q.No_Of_Share2,q.profit2 from " +
// "(SELECT t.F_CD f_cd1, f.f_name fund_name1,SUM(AMT_AFT_COM) as COST1, sum(no_share) as No_Of_Share1 FROM FUND_TRANS_HB t, fund f " +
// " WHERE t.TRAN_TP = 'C' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and t.f_cd = f.f_cd and t.f_cd <> 3" +
// " GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name)p, (SELECT t.F_CD f_cd2, f.f_name fund_name2,SUM(AMT_AFT_COM) as SALE2," +
// "sum(no_share) as No_Of_Share2,sum(crt_aft_com * no_share) as COST2,SUM(AMT_AFT_COM) - sum(crt_aft_com * no_share) as profit2" +
// " FROM FUND_TRANS_HB t, fund f WHERE t.TRAN_TP = 'S' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and f.f_cd = t.f_cd and t.f_cd <> 3" +
// " GROUP BY t.F_CD, f.f_name)q where p.f_cd1=q.f_cd2 order by p.f_cd1";
strSQLForMainReport = "SELECT t.F_CD f_cd1, f.f_name fund_name1,SUM(AMT_AFT_COM) as COST1, sum(no_share) as No_Of_Share1 FROM FUND_TRANS_HB t, fund f " +
" WHERE t.TRAN_TP = 'C' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and t.f_cd = f.f_cd and t.f_cd <> 3" +
" GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name";
dtRptSrcMainReport = commonGatewayObj.Select(strSQLForMainReport);
strSQLForSubReport = "SELECT t.F_CD f_cd2, f.f_name fund_name2,SUM(AMT_AFT_COM) as SALE2," +
"sum(no_share) as No_Of_Share2,sum(crt_aft_com * no_share) as COST2,SUM(AMT_AFT_COM) - sum(crt_aft_com * no_share) as profit2" +
" FROM FUND_TRANS_HB t, fund f WHERE t.TRAN_TP = 'S' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and f.f_cd = t.f_cd and t.f_cd <> 3" +
" GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name";
dtRptSrcSubReport = commonGatewayObj.Select(strSQLForSubReport);
DataSet ds = new DataSet();
//ds.Tables.Add(dtRptSrcMainReport);
//ds.Tables.Add(dtRptSrcSubReport);
// Dim dt1 As DataTable
// Dim dt2 As DataTable
// dt1 = UnityDataRow()
// dt2 = UnityDataRow()
//dt1.TableName = "Level1"
//dtRptSrcMainReport.TableName = "dtCopyForMainRpt";
//dtRptSrcSubReport.TableName = "dtCopyForSubRpt";
//dt2.TableName = "Level2"
//HierDS.Tables.Add(dt1) '' no need to write copy method
// HierDS.Tables.Add(dt2)
DataTable dtCopyForMainRpt = new DataTable();
DataTable dtCopyForSubRpt = new DataTable();
dtRptSrcMainReport.TableName = "dtCopyForMainRpt";
dtRptSrcSubReport.TableName = "dtCopyForSubRpt";
ds.Tables.Add(dtRptSrcMainReport.Copy());
ds.Tables.Add(dtRptSrcSubReport.Copy());
// E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report
// dtReprtSource.WriteXmlSchema(@"E:\amclpmfs\UI\ReportViewer\Report\xsdMarketValuationWithProfitLoss.xsd");
// dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdMarketValuationWithProfitLoss.xsd");
// ds.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdCapitalGainAllFunds.xsd");
if (dtRptSrcMainReport.Rows.Count > 0 && dtRptSrcSubReport.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crptCapitalGainAllFundsReport.rpt");
rdoc.Load(Path);
//ds.Tables[0].Merge(dtRptSrcMainReport);
//ds.Tables[0].Merge(dtRptSrcSubReport);
// rdoc.SetDataSource(dtReprtSource);
rdoc.SetDataSource(ds);
CRV_CapitalGainAllFundsReportViewer.ReportSource = rdoc;
rdoc.SetParameterValue("prmFromdate", strFromdate);
rdoc.SetParameterValue("prmTodate", strTodate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
//if (dtReprtSource1.Rows.Count > 0)
//{
// string Path = Server.MapPath("Report/testFUND_TRANS_HB.rpt");
// rdoc.Load(Path);
// rdoc.SetDataSource(dtReprtSource1);
// CrystalReportViewer1.ReportSource = rdoc;
// rdoc = ReportFactory.GetReport(rdoc.GetType());
//}
//else
//{
// Response.Write("No Data Found");
//}
}
protected void btnSave_Click(object sender, EventArgs e)
{
string strBalanceDate, strLastBalDate, strLastUpadateDate, strLastUpadatePlusOneDate, strUpdateFundTransHB, strDelFromFundFolioHB, strMarketPriceDate, temp;
DateTime? dtimeBalanceDate, dtimeLastBalDate, dtimeLastUpadateDate, dtimeLastUpadatePlusOneDate, dtMarketPriceDate;
DataTable dtFromFundTransHB = new DataTable();
DataTable dtFromFundFolioHB = new DataTable();
CommonGateway commonGatewayObj = new CommonGateway();
DataTable dtSource = new DataTable();
DataTable dtSource2 = new DataTable();
try
{
if (!string.IsNullOrEmpty(txtBalanceDate.Text.Trim()))
{
dtimeBalanceDate = Convert.ToDateTime(txtBalanceDate.Text.ToString());
strBalanceDate = dtimeBalanceDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeBalanceDate = null;
strBalanceDate = "";
}
if (!string.IsNullOrEmpty(txtLastBalDate.Text.Trim()))
{
dtimeLastBalDate = Convert.ToDateTime(txtLastBalDate.Text.ToString());
strLastBalDate = dtimeLastBalDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeLastBalDate = null;
strLastBalDate = "";
}
if (!string.IsNullOrEmpty(txtLastUpadateDate.Text.Trim()))
{
dtimeLastUpadateDate = Convert.ToDateTime(txtLastUpadateDate.Text.ToString());
strLastUpadateDate = dtimeLastUpadateDate.Value.ToString("dd-MMM-yyyy");
dtimeLastUpadatePlusOneDate = dtimeLastUpadateDate.Value.AddDays(1);
strLastUpadatePlusOneDate = dtimeLastUpadatePlusOneDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtimeLastUpadateDate = null;
strLastUpadateDate = "";
dtimeLastUpadatePlusOneDate = null;
strLastUpadatePlusOneDate = "";
}
if (!string.IsNullOrEmpty(txtMarketPriceDate.Text.Trim()))
{
dtMarketPriceDate = Convert.ToDateTime(txtMarketPriceDate.Text.ToString());
strMarketPriceDate = dtMarketPriceDate.Value.ToString("dd-MMM-yyyy");
}
else
{
dtMarketPriceDate = null;
strMarketPriceDate = "";
}
if (dtimeBalanceDate > dtimeLastBalDate)
{
temp = adv_proc1(strLastUpadatePlusOneDate, strBalanceDate, fundNameDropDownList.SelectedValue.ToString());
if (temp.Trim() == "Processing Completed")
{
lblProcessing.Text = "Processing completed!!!!";
// ClearFields();
}
else
{
lblProcessing.Text = "No data found!!!!";
// ClearFields();
}
//lblProcessing.Text = "Processing completed!!!!";
//ClearFields();
}
else
{
commonGatewayObj.BeginTransaction();
strDelFromFundFolioHB = "delete from fund_folio_hb where f_cd =" + fundNameDropDownList.SelectedValue.ToString();
int noDelRowsFromFundFolioHB = commonGatewayObj.ExecuteNonQuery(strDelFromFundFolioHB);
strUpdateFundTransHB = "update fund_trans_hb set cost_rate = null,crt_aft_com = null where f_cd =" + fundNameDropDownList.SelectedValue.ToString();
int noUpdRowsFundTransHB = commonGatewayObj.ExecuteNonQuery(strUpdateFundTransHB);
commonGatewayObj.CommitTransaction();
temp = adv_proc1(strLastUpadatePlusOneDate, strBalanceDate, fundNameDropDownList.SelectedValue.ToString());
if (temp.Trim() == "Processing Completed")
{
lblProcessing.Text = "Processing completed!!!!";
// ClearFields();
}
else
{
lblProcessing.Text = "No data found!!!!";
// ClearFields();
}
// lblProcessing.Text = "Processing completed!!!!";
// Code goes here Code goes here
}
string strCompnayTransdate = "select a.comp_cd, max(a.tran_date) tran_date from market_price a, fund_folio_hb b where a.comp_cd = b.comp_cd and b.f_cd =" + fundNameDropDownList.SelectedValue.ToString() + " and a.tran_date <= '" + strBalanceDate + "' group by a.comp_cd order by a.comp_cd";
dtSource = commonGatewayObj.Select(strCompnayTransdate);
List <CompanayTransdate> lstCompnayTransdate = new List <CompanayTransdate>();
List <CompanyAvarageRate> lstCompnayAvgrate = new List <CompanyAvarageRate>();
lstCompnayTransdate = (from DataRow dr in dtSource.Rows
select new CompanayTransdate()
{
COMP_CD = dr["COMP_CD"].ToString(),
TRAN_DATE = dr["TRAN_DATE"].ToString()
}).ToList();
string dltQuery = "delete from mprice_temp where f_cd=" + fundNameDropDownList.SelectedValue.ToString();
int dltNumOfRows = commonGatewayObj.ExecuteNonQuery(dltQuery);
foreach (CompanayTransdate comtransdate in lstCompnayTransdate)
{
string strInsQuery = "select " + fundNameDropDownList.SelectedValue.ToString() + " as FundId, comp_cd, avg_rt from market_price where comp_cd = " + comtransdate.COMP_CD + " and tran_date = '" + Convert.ToDateTime(comtransdate.TRAN_DATE).ToString("dd-MMM-yyyy") + "'";
dtSource2 = commonGatewayObj.Select(strInsQuery);
lstCompnayAvgrate = (from DataRow dr in dtSource2.Rows
select new CompanyAvarageRate()
{
FUNDID = dr["FUNDID"].ToString(),
COMP_CD = dr["COMP_CD"].ToString(),
AVG_RT = dr["AVG_RT"].ToString()
}).ToList();
foreach (CompanyAvarageRate comAvgrate in lstCompnayAvgrate)
{
string strQueryInsMprice_Temp = "insert into mprice_temp(f_cd, comp_cd, avg_rt) values ('" + comAvgrate.FUNDID + "','" + comAvgrate.COMP_CD + "','" + comAvgrate.AVG_RT + "')";
int NumOfRows = commonGatewayObj.ExecuteNonQuery(strQueryInsMprice_Temp);
}
}
string LoginID = Session["UserID"].ToString();
DateTime dtimeCurrentDateTimeForLog = DateTime.Now;
string strCurrentDateTimeForLog = dtimeCurrentDateTimeForLog.ToString("dd-MMM-yyyy HH:mm:ss tt", CultureInfo.InvariantCulture);
//string strupdateQueryfund_control = "update fund_control set bal_dt='" + strBalanceDate + "',mprice_dt='" + strMarketPriceDate + "' where f_cd =" + fundNameDropDownList.SelectedValue.ToString() + "";
string strupdateQueryfund_control = "update fund_control set op_name='" + LoginID + "',upd_date_time='" + strCurrentDateTimeForLog + "',bal_dt='" + strBalanceDate + "',mprice_dt='" + strMarketPriceDate + "' where f_cd =" + fundNameDropDownList.SelectedValue.ToString() + "";
int updatefund_controlNumOfRows = commonGatewayObj.ExecuteNonQuery(strupdateQueryfund_control);
// System.Threading.Thread.Sleep(3000);
// ClearFields();
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "alert('" + ex.Message.ToString() + "');", true);
}
}
/// <summary>
/// Gets the registrars appeals data by SGID.
/// </summary>
/// <param name="submissionGroupId">The submission group id.</param>
/// <returns></returns>
internal ISubCaseTypeInfo GetRegistrarsAppealsDataBySGID(string submissionGroupId)
{
RegistrarsAppealsData registrarsAppealsData = null;
try
{
AppealsGateway appealsGateway = new AppealsGateway();
CommonGateway commonGateway = new CommonGateway();
registrarsAppealsData = new RegistrarsAppealsData();
registrarsAppealsData.ListLegislationProvisionsEntity = commonGateway.GetListLegislationProvisionsEntityBySGID(submissionGroupId);
registrarsAppealsData.ListNatureOfCaseEntity = commonGateway.GetListNatureOfCaseEntityBySGID(submissionGroupId);
registrarsAppealsData.ListRefOrderDetailEntity = commonGateway.GetListRefOrderDetailEntityBySGID(submissionGroupId);
registrarsAppealsData.RARASInfoEntity = appealsGateway.GetRARASInfoEntityBySGID(submissionGroupId);
}
catch (Exception ex)
{
Logger.Error("Error at GetAppealsData", ex);
throw;
}
return registrarsAppealsData;
}
/// <summary>
/// Method to SaveOrUpdate <see cref="AppealsData"/>
/// </summary>
/// <param name="appealsData">The <see cref="AppealsData"/> object.</param>
internal void SaveOrUpdateAppealsData(AppealsData appealsData)
{
try
{
AppealsGateway appealsGateway = new AppealsGateway();
CommonGateway commonGateway = new CommonGateway();
appealsGateway.SaveOrUpdateDCACAInfoEntity(appealsData.DCACAInfoEntity);
commonGateway.SaveOrUpdateLegislationProvisionsList(appealsData.ListLegislationProvisionsEntity);
commonGateway.SaveOrUpdateNatureOfCaseList(appealsData.ListNatureOfCaseEntity);
commonGateway.SaveOrUpdateRefOrderDetailList(appealsData.ListRefOrderDetailEntity);
commonGateway.SaveOrUpdateOtherITMOList(appealsData.ListOtherITMOEntity);
}
catch (Exception ex)
{
Logger.Error("Error at SaveOrUpdateAppealsData", ex);
throw;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string strFromdate = "";
string strTodate = "";
string fundCodes = "";
string transtype = "";
string strSQLForPurchase, strSQLForSale, strSQLForIPO, strSQLForRight;
//string companyCodes = "";
//string percentageCheck = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
strFromdate = (string)Session["FromDate"];
strTodate = (string)Session["ToDate"];
fundCodes = (string)Session["fundCodes"];
transtype = (string)Session["transtype"];
}
CommonGateway commonGatewayObj = new CommonGateway();
// DataTable dtReprtSource = new DataTable();
DataTable dtRptSrcMainReport = new DataTable();
// DataTable dtRptSrcSubReport = new DataTable();
StringBuilder sbMst = new StringBuilder();
StringBuilder sbfilter = new StringBuilder();
if (transtype == "C")
{
strSQLForPurchase = "SELECT t.F_CD f_cd1, f.f_name fund_name1,SUM(AMT_AFT_COM) as COST1, sum(no_share) as No_Of_Share1 FROM FUND_TRANS_HB t, fund f " +
" WHERE t.TRAN_TP = 'C' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and t.f_cd = f.f_cd " +
" GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name";
dtRptSrcMainReport = commonGatewayObj.Select(strSQLForPurchase);
dtRptSrcMainReport.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\xsdBSECSummeryPurchase.xsd");
if (dtRptSrcMainReport.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crptBSECSummeryPurchase.rpt");
rdoc.Load(Path);
//ds.Tables[0].Merge(dtRptSrcMainReport);
//ds.Tables[0].Merge(dtRptSrcSubReport);
// rdoc.SetDataSource(dtReprtSource);
rdoc.SetDataSource(dtRptSrcMainReport);
CRV_CapitalGainAllFundsReportViewer.ReportSource = rdoc;
rdoc.SetParameterValue("prmFromdate", strFromdate);
rdoc.SetParameterValue("prmTodate", strTodate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
else if (transtype == "I")
{
strSQLForIPO = "SELECT t.F_CD f_cd1, f.f_name fund_name1,SUM(AMT_AFT_COM) as IPO, sum(no_share) as No_Of_Share1 FROM FUND_TRANS_HB t, fund f " +
" WHERE t.TRAN_TP = 'I' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and t.f_cd = f.f_cd " +
" GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name";
dtRptSrcMainReport = commonGatewayObj.Select(strSQLForIPO);
dtRptSrcMainReport.TableName = "BSECSummeryReportIPO";
dtRptSrcMainReport.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\xsdBSECSummeryIPO.xsd");
if (dtRptSrcMainReport.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crptBSECSummeryIPO.rpt");
rdoc.Load(Path);
//ds.Tables[0].Merge(dtRptSrcMainReport);
//ds.Tables[0].Merge(dtRptSrcSubReport);
// rdoc.SetDataSource(dtReprtSource);
rdoc.SetDataSource(dtRptSrcMainReport);
CRV_CapitalGainAllFundsReportViewer.ReportSource = rdoc;
rdoc.SetParameterValue("prmFromdate", strFromdate);
rdoc.SetParameterValue("prmTodate", strTodate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
else if (transtype == "R")
{
strSQLForRight = "SELECT t.F_CD f_cd1, f.f_name fund_name1,SUM(AMT_AFT_COM) as Right, sum(no_share) as No_Of_Share1 FROM FUND_TRANS_HB t, fund f " +
" WHERE t.TRAN_TP = 'R' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and t.f_cd = f.f_cd " +
" GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name";
dtRptSrcMainReport = commonGatewayObj.Select(strSQLForRight);
dtRptSrcMainReport.TableName = "BSECSummeryReportRight";
dtRptSrcMainReport.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\xsdBSECSummeryRight.xsd");
if (dtRptSrcMainReport.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crptBSECSummeryRight.rpt");
rdoc.Load(Path);
//ds.Tables[0].Merge(dtRptSrcMainReport);
//ds.Tables[0].Merge(dtRptSrcSubReport);
// rdoc.SetDataSource(dtReprtSource);
rdoc.SetDataSource(dtRptSrcMainReport);
CRV_CapitalGainAllFundsReportViewer.ReportSource = rdoc;
rdoc.SetParameterValue("prmFromdate", strFromdate);
rdoc.SetParameterValue("prmTodate", strTodate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
else if (transtype == "S")
{
strSQLForSale = "SELECT t.F_CD f_cd2, f.f_name fund_name2,SUM(AMT_AFT_COM) as SALE2," +
"sum(no_share) as No_Of_Share2,sum(crt_aft_com * no_share) as COST2,SUM(AMT_AFT_COM) - sum(crt_aft_com * no_share) as profit2" +
" FROM FUND_TRANS_HB t, fund f WHERE t.TRAN_TP = 'S' AND VCH_DT BETWEEN '" + strFromdate + "' AND '" + strTodate + "' and t.F_CD IN(" + fundCodes + ") and f.f_cd = t.f_cd and t.f_cd <> 3" +
" GROUP BY t.F_CD, f.f_name ORDER BY t.F_CD, f.f_name";
dtRptSrcMainReport = commonGatewayObj.Select(strSQLForSale);
dtRptSrcMainReport.TableName = "BSECSummeryReportSale";
dtRptSrcMainReport.WriteXmlSchema(@"D:\IAMCL_10-7-17\amclpmfs\amclpmfs\UI\ReportViewer\Report\xsdBSECSummerySale.xsd");
if (dtRptSrcMainReport.Rows.Count > 0)
{
string Path = Server.MapPath("Report/crptBSECSummerySale.rpt");
rdoc.Load(Path);
//ds.Tables[0].Merge(dtRptSrcMainReport);
//ds.Tables[0].Merge(dtRptSrcSubReport);
// rdoc.SetDataSource(dtReprtSource);
rdoc.SetDataSource(dtRptSrcMainReport);
CRV_CapitalGainAllFundsReportViewer.ReportSource = rdoc;
rdoc.SetParameterValue("prmFromdate", strFromdate);
rdoc.SetParameterValue("prmTodate", strTodate);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
// DataSet ds = new DataSet();
// DataTable dtCopyForMainRpt = new DataTable();
// DataTable dtCopyForSubRpt = new DataTable();
// dtRptSrcMainReport.TableName = "dtCopyForMainRpt";
//// dtRptSrcSubReport.TableName = "dtCopyForSubRpt";
// ds.Tables.Add(dtRptSrcMainReport.Copy());
//// ds.Tables.Add(dtRptSrcSubReport.Copy());
// // E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report
// // dtReprtSource.WriteXmlSchema(@"E:\amclpmfs\UI\ReportViewer\Report\xsdMarketValuationWithProfitLoss.xsd");
// // dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdMarketValuationWithProfitLoss.xsd");
// // ds.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdCapitalGainAllFunds.xsd");
// if (dtRptSrcMainReport.Rows.Count>0 )
// {
// string Path = Server.MapPath("Report/crptCapitalGainAllFundsReport.rpt");
// rdoc.Load(Path);
// //ds.Tables[0].Merge(dtRptSrcMainReport);
// //ds.Tables[0].Merge(dtRptSrcSubReport);
// // rdoc.SetDataSource(dtReprtSource);
// rdoc.SetDataSource(ds);
// CRV_CapitalGainAllFundsReportViewer.ReportSource = rdoc;
// rdoc.SetParameterValue("prmFromdate", strFromdate);
// rdoc.SetParameterValue("prmTodate", strTodate);
// rdoc = ReportFactory.GetReport(rdoc.GetType());
// }
// else
// {
// Response.Write("No Data Found");
// }
//if (dtReprtSource1.Rows.Count > 0)
//{
// string Path = Server.MapPath("Report/testFUND_TRANS_HB.rpt");
// rdoc.Load(Path);
// rdoc.SetDataSource(dtReprtSource1);
// CrystalReportViewer1.ReportSource = rdoc;
// rdoc = ReportFactory.GetReport(rdoc.GetType());
//}
//else
//{
// Response.Write("No Data Found");
//}
}