private void frmIPOSummaryOfTheApplications_Load(object sender, EventArgs e)
{
IPOReportBAL bal = new IPOReportBAL();
DataTable dt = new DataTable();
dt = bal.GetSessionName();
cmbsessionName.DataSource = dt;
cmbsessionName.DisplayMember = dt.Columns["IpoSession_Name"].ToString();
cmbsessionName.ValueMember = dt.Columns["ID"].ToString();
if (cmbsessionName.SelectedIndex >= 0)
{
cmbsessionName.SelectedIndex = 0;
}
}
private void btnShow_Click(object sender, EventArgs e)
{
IPOReportBAL bal = new IPOReportBAL();
DataTable dt = new DataTable();
int Session_Id = (int)cmbsessionName.SelectedValue;
dt = bal.GetIpoSummaryOfApplicationBySessionId(Session_Id);
crIPOSummaryOfTheApplications crRpt = new crIPOSummaryOfTheApplications();
frmReportViewer viewer = new frmReportViewer();
crRpt.SetDataSource(dt);
viewer.crvReportViewer.ReportSource = crRpt;
viewer.Show();
}
public void ShowCustMoneyLedgerReport()
{
_branchId = GlobalVariableBO._branchId;
_fromDate = Convert.ToDateTime(dtFromDate.Value.ToShortDateString());
_toDate = Convert.ToDateTime(dtToDate.Value.ToShortDateString());
_custCode = txtCustCode.Text;
IPOReportBAL moneyLedgerReportBal = new IPOReportBAL();
DataTable dtCustmoneyLedger = new DataTable();
DataTable dtCustBasicInfo = new DataTable();
LoadCommonInfo CmmInfo = new LoadCommonInfo();
crIPOCustMoneyLedger crCustMoneyledger = new crIPOCustMoneyLedger();
frmIPOReportViewer viewer = new frmIPOReportViewer();
dtCustmoneyLedger = moneyLedgerReportBal.GetIPOCustMoneyLedger(_custCode, _fromDate, _toDate);
dtCustBasicInfo = moneyLedgerReportBal.GetIpoCustBasicInfo(_custCode);
crCustMoneyledger.SetDataSource(dtCustmoneyLedger);
if (dtCustBasicInfo.Rows.Count > 0)
{
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["txtCustName"]).Text =
dtCustBasicInfo.Rows[0]["Cust_Name"].ToString();
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["txtCustCode"]).Text =
dtCustBasicInfo.Rows[0]["Cust_Code"].ToString();
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["Text6"]).Text =
"Balance Before " + dtFromDate.Value.ToString("dd-MMM-yy") + " :";
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["txtduration"]).Text =
"Duration : " + dtFromDate.Value.ToString("dd-MMM-yyyy") + " To " +
dtToDate.Value.ToString("dd-MMM-yyyy");
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["txtreportName"]).Text =
"IPO Customer Money Ledger";
}
GetCommonInfo();
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["txtCompanyName"]).Text =
_CommpanyName;
///// Load Branch Name
((TextObject)crCustMoneyledger.ReportDefinition.Sections[2].ReportObjects["txtBranchName"]).Text =
"Branch Name:" + _branchName + "," + _branchAddress + ". Phone:" + _branchContactNumber;
viewer.crystalReportViewer1.ReportSource = crCustMoneyledger;
viewer.Show();
}
private void NRB()
{
IPOReportBAL bal = new IPOReportBAL();
DataTable dt = new DataTable();
dt = bal.GetNRBSuccessfulUnsuccessful(cmbStatusName.Text, Cmbcompanyname.Text, CmbCustomerCode.Text);
crNrbSuccessfulUnsuccessful nrb = new crNrbSuccessfulUnsuccessful();
frmIPOReportViewer viewer = new frmIPOReportViewer();
nrb.SetDataSource(dt);
//GetCommonInfo();
//((TextObject)nrb.ReportDefinition.Sections[2].ReportObjects["txtCompanyName"]).Text =
// _CommpanyName;
/////// Load Branch Name
//((TextObject)nrb.ReportDefinition.Sections[2].ReportObjects["txtBranchName"]).Text =
// "Branch Name:" + _branchName + "," + _branchAddress + ". Phone:" + _branchContactNumber;
viewer.crystalReportViewer1.ReportSource = nrb;
viewer.Show();
}
private void loadShareBalanceReport()
{
IPOReportBAL bal = new IPOReportBAL();
crIPOCustomerSummaryBalanceLedger objrpt = new crIPOCustomerSummaryBalanceLedger();
frmIPOReportViewer viewer = new frmIPOReportViewer();
DataTable dt = new DataTable();
_fromDate = dtFromDate.Value.Date;
_toDate = dtToDate.Value.Date;
dt = bal.GetAllCustomerIPOBalance(_fromDate.Date, _toDate.Date);
objrpt.SetDataSource(dt);
GetCommonInfo();
((TextObject)objrpt.ReportDefinition.Sections[2].ReportObjects["txtCompanyName"]).Text = _CommpanyName;
((TextObject)objrpt.ReportDefinition.Sections[2].ReportObjects["txtBranchName"]).Text = _branchAddress;
((TextObject)objrpt.ReportDefinition.Sections[2].ReportObjects["txtReportName"]).Text = "All Account Balance Summary Information";
//((TextObject)objrpt.ReportDefinition.Sections[2].ReportObjects["Todate"]).Text = _toDate.Date.ToString();
((TextObject)objrpt.ReportDefinition.Sections[2].ReportObjects["FromDate"]).Text = _fromDate.Date.ToString();
viewer.crystalReportViewer1.ReportSource = objrpt;
viewer.Show();
}
private void LoadCustInfo()
{
DataTable custDateTable = new DataTable();
DataTable NewcustDateTable = new DataTable();
IPOReportBAL customerInfoBAL = new IPOReportBAL();
string newCust = string.Empty;
if (ddlSearchCustomer.SelectedItem.Equals("BO Id"))
{
_boID = txtSearchCustomer.Text;
custDateTable = customerInfoBAL.GetIPOCustInfoByBOID(_boID);
if (custDateTable.Rows.Count > 0)
{
int _custCode = Convert.ToInt32(custDateTable.Rows[0][0].ToString());
txtCustCode.Text = custDateTable.Rows[0][0].ToString();
txtAccountHolderName.Text = custDateTable.Rows[0][1].ToString();
txtAccountHolderBOId.Text = custDateTable.Rows[0][2].ToString();
}
else
{
MessageBox.Show("No customer found.");
}
}
else
{
_custCode = txtSearchCustomer.Text;
custDateTable = customerInfoBAL.GetIPOCustInfoByCustCode(_custCode);
if (custDateTable.Rows.Count > 0)
{
txtCustCode.Text = custDateTable.Rows[0][0].ToString();
txtAccountHolderName.Text = custDateTable.Rows[0][1].ToString();
txtAccountHolderBOId.Text = custDateTable.Rows[0][2].ToString();
}
else
{
MessageBox.Show("No customer found.");
}
}
}
private void GetNRBPaymentReview()
{
IPOReportBAL bal = new IPOReportBAL();
DataTable dtPaymentReview_NRB = new DataTable();
crIPONRBPayementReview crPayment = new crIPONRBPayementReview();
frmIPOReportViewer paymentViewer = new frmIPOReportViewer();
LoadCommonInfo CmmInfo = new LoadCommonInfo();
int selected_BranchID = Convert.ToInt32(cmb_Branch_Name.SelectedValue);
dtPaymentReview_NRB = bal.GETIPONRBPaymentReview(selected_BranchID, dtpFromDate.Value, dtpToDate.Value);
crPayment.SetDataSource(dtPaymentReview_NRB);
GetCommonInfo();
///// Load Company Name
((TextObject)crPayment.ReportDefinition.Sections[2].ReportObjects["txtCompanyName"]).Text = _CommpanyName;
///// Load Branch Name
((TextObject)crPayment.ReportDefinition.Sections[2].ReportObjects["txtAddress"]).Text = _branchName + "," + _branchAddress + ",Phone: " + _branchContactNumber;
////Load Date
((TextObject)crPayment.ReportDefinition.Sections[2].ReportObjects["txtFromDate"]).Text = dtpFromDate.Value.ToString("dd-MMM-yyyy") + " To " + dtpToDate.Value.ToString("dd-MMM-yyyy");
paymentViewer.crystalReportViewer1.ReportSource = crPayment;
paymentViewer.Show();
}
private void Export_xls(string FileName)
{
IPOReportBAL bal = new IPOReportBAL();
int dataSartingRowIndex = 7;
string[] ColumnsHeader = new string[] { "Trec Code", "DPID", "Customer ID", "Applicants Name", "Bo ID No", "Applicant Category", "Currency", "Amount", "Draft No", "BankName", "Branch Name", "Date", "Security Code", "Remarks" };
string[] ColumnsAlphabet = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
var missValue = System.Reflection.Missing.Value;
var excelApp = new Excel.ApplicationClass();
//ExcelDoc_Prepared(out excelApp);
var workbook = excelApp.Workbooks.Add(missValue);
var currentSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
string password = string.Empty;
//password = objBal.GetAllEFT_File_Info_ByFileNo(txtFileNo.Text.Trim()).File_Password;
//Initial Formating
currentSheet.Columns.NumberFormat = "@";
currentSheet.Name = "KSCL EFT";
Excel.Range rngColumns = currentSheet.get_Range("A" + dataSartingRowIndex, "" + ColumnsAlphabet[ColumnsHeader.Length] + "" + dataSartingRowIndex);
rngColumns.Font.Bold = true;
rngColumns.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
/////////////////////////////////
////////********** Set Company Name & Address ***************////////
CommonInfoBal objComm = new CommonInfoBal();
DataTable data = new DataTable();
data = objComm.GetCompanyNameHeadOffice();
//////// *************** Company Name ***************** ///////////
currentSheet.get_Range("A1", "H1").Merge(true);
currentSheet.get_Range("A1", "H1").Value2 = data.Rows[0]["BrokerName"].ToString();
currentSheet.get_Range("A1", "H1").HorizontalAlignment = 3;
currentSheet.get_Range("A1", "H1").Font.Name = "Arial";
currentSheet.get_Range("A1", "H1").Font.Bold = true;
currentSheet.get_Range("A1", "H1").Font.Size = 11;
currentSheet.get_Range("A1", "H1").RowHeight = 15;
currentSheet.get_Range("A1", "H1").VerticalAlignment = 3;
currentSheet.get_Range("A1", "H1").HorizontalAlignment = 3;
//////// *************** Company Name ***************** ///////////
currentSheet.get_Range("A2", "H2").Merge(true);
currentSheet.get_Range("A2", "H2").Value2 = data.Rows[0]["Address"].ToString();
currentSheet.get_Range("A2", "H2").HorizontalAlignment = 3;
currentSheet.get_Range("A2", "H2").Font.Name = "Arial";
currentSheet.get_Range("A2", "H2").Font.Size = 10;
currentSheet.get_Range("A2", "H2").RowHeight = 12.75;
currentSheet.get_Range("A2", "H2").VerticalAlignment = 3;
currentSheet.get_Range("A2", "H2").HorizontalAlignment = 3;
//////// *************** Phone & Fax ***************** ///////////
currentSheet.get_Range("A3", "H3").Merge(true);
currentSheet.get_Range("A3", "H3").Value2 = "Phone: " + data.Rows[0]["Telephone"].ToString() + ",Fax: " + data.Rows[0]["Fax"].ToString();
currentSheet.get_Range("A3", "H3").HorizontalAlignment = 3;
currentSheet.get_Range("A3", "H3").Font.Name = "Arial";
currentSheet.get_Range("A3", "H3").Font.Size = 10;
currentSheet.get_Range("A3", "H3").RowHeight = 12.75;
currentSheet.get_Range("A3", "H3").VerticalAlignment = 3;
currentSheet.get_Range("A3", "H3").HorizontalAlignment = 3;
//////// *************** Contact Numner ***************** ///////////
currentSheet.get_Range("A4", "H4").Merge(true);
currentSheet.get_Range("A4", "H4").Value2 = "Contact No: 01720220321, 01710878300";
currentSheet.get_Range("A4", "H4").HorizontalAlignment = 3;
currentSheet.get_Range("A4", "H4").Font.Name = "Arial";
currentSheet.get_Range("A4", "H4").Font.Size = 10;
currentSheet.get_Range("A4", "H4").RowHeight = 12.75;
currentSheet.get_Range("A4", "H4").VerticalAlignment = 3;
currentSheet.get_Range("A4", "H4").HorizontalAlignment = 3;
//////// *************** Cheque Requisition Date ***************** ///////////
currentSheet.get_Range("A5", "H5").Merge(true);
currentSheet.get_Range("A5", "H5").Value2 = "NRB Print Date: " + DateTime.Today.Date.ToString("dd-MM-yyyy");
currentSheet.get_Range("A5", "H5").HorizontalAlignment = 3;
currentSheet.get_Range("A5", "H5").Font.Name = "Arial";
currentSheet.get_Range("A5", "H5").Font.Bold = true;
currentSheet.get_Range("A5", "H5").Font.Size = 9;
currentSheet.get_Range("A5", "H5").Font.Underline = true;
currentSheet.get_Range("A5", "H5").RowHeight = 12.75;
currentSheet.get_Range("A5", "H5").VerticalAlignment = 3;
currentSheet.get_Range("A5", "H5").HorizontalAlignment = 3;
//////////////////////////////
//Insert Columns Name
int count_Columns = 1;
foreach (string str in ColumnsHeader)
{
currentSheet.Cells[dataSartingRowIndex, count_Columns] = str;
count_Columns++;
}
DataTable dt = new DataTable();
dt = bal.GetNRBSuccessfulUnsuccessful(cmbStatusName.Text, Cmbcompanyname.Text, CmbCustomerCode.Text);
List <Export_ExcelDocBo> resultList = new List <Export_ExcelDocBo>();
foreach (DataRow row in dt.Rows)
{
Export_ExcelDocBo obj = new Export_ExcelDocBo();
obj.TrecCode = Convert.ToString((int)row["Trec Code"]);
obj.DPID = (string)row["DPID"] ?? string.Empty;
obj.Cust_Code = (string)row["Customer ID"] ?? string.Empty;
obj.ReceiverName = (string)row["Applicants Name"] ?? string.Empty;
obj.BOID = (string)row["Bo ID No"] ?? string.Empty;
obj.ApplicantCategory = (string)row["Applicant Category"] ?? string.Empty;
obj.Currency = (string)row["Currency"] ?? string.Empty;
obj.Amount = Convert.ToDouble(row["Amount"]) != 0.00 ? Convert.ToDouble(row["Amount"]) : 0.00;
obj.DraftNO = (string)row["Draft No"] ?? string.Empty;
obj.BankName = (string)row["BankName"] ?? string.Empty;
obj.BranchName = (string)row["Branch Name"] ?? string.Empty;
obj.Received_Date = Convert.ToDateTime(row["Date"]);
obj.SecurityCode = (string)row["Security Code"] ?? string.Empty;
obj.Reason = (string)row["Remarks"] ?? string.Empty;
resultList.Add(obj);
}
var dataList = resultList;
int count_Row = dataSartingRowIndex + 1;
foreach (var obj in dataList)
{
currentSheet.Cells[count_Row, 1] = Convert.ToString(obj.TrecCode).Trim();
currentSheet.Cells[count_Row, 2] = Convert.ToString(obj.DPID).Trim();
currentSheet.Cells[count_Row, 3] = Convert.ToString(obj.Cust_Code).Trim();
currentSheet.Cells[count_Row, 4] = Convert.ToString(obj.ReceiverName).Trim();
currentSheet.Cells[count_Row, 5] = Convert.ToString(obj.BOID).Trim();
currentSheet.Cells[count_Row, 6] = Convert.ToString(obj.ApplicantCategory).Trim();
currentSheet.Cells[count_Row, 7] = Convert.ToString(obj.Currency).Trim();
currentSheet.Cells[count_Row, 8] = Convert.ToString(obj.Amount).Trim();
currentSheet.Cells[count_Row, 9] = Convert.ToString(obj.DraftNO).Trim();
currentSheet.Cells[count_Row, 10] = Convert.ToString(obj.BankName).Trim();
currentSheet.Cells[count_Row, 11] = Convert.ToString(obj.BranchName).Trim();
currentSheet.Cells[count_Row, 12] = Convert.ToString(obj.Received_Date.ToShortDateString());
currentSheet.Cells[count_Row, 13] = Convert.ToString(obj.SecurityCode).Trim();
currentSheet.Cells[count_Row, 14] = Convert.ToString(obj.Reason).Trim();
count_Row++;
}
//Column Resizing and Post Formatting
Excel.Range rngALL = currentSheet.get_Range("A" + dataSartingRowIndex, "" + ColumnsAlphabet[ColumnsHeader.Length] + "" + (dataSartingRowIndex + (dataList.Count + 1)));
rngALL.Columns.AutoFit();
rngALL.VerticalAlignment = 3;
rngALL.HorizontalAlignment = 3;
//currentSheet.Protect(password, true, true, true, false, false, true, true, false, false, false, false, false, true, true, false);
workbook.Author = "K-Secuirites Ltd.";
workbook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, password, missValue,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missValue, missValue, missValue, missValue, missValue);
workbook.Close(true, missValue, missValue);
excelApp.Quit();
releaseObject(excelApp);
releaseObject(workbook);
releaseObject(currentSheet);
}