void gridView1_DoubleClick(object sender, EventArgs e) { GridView view = (GridView)gridControl1.FocusedView; if (radioGroup3.SelectedIndex == -1) { Common.setEmptyField("Report Type", groupControl1.Text); return; } else { if (view != null) { DataRow dr = view.GetDataRow(view.FocusedRowHandle); if (dr != null) { if (radioGroup3.EditValue.ToString() == "Bank") { try { SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false); using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("BankTransaction", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@bankcode", SqlDbType.VarChar)).Value = dr["BankShortCode"]; _command.Parameters.Add(new SqlParameter("@Periods", SqlDbType.Int)).Value = Convert.ToInt32(dr["FinancialperiodID"]); _command.Parameters.Add(new SqlParameter("@BankAccountID", SqlDbType.Int)).Value = Convert.ToInt32(dr["BankAccountID"]); _command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["StartDate"]); _command.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["EndDate"]); _command.Parameters.Add(new SqlParameter("@PType", SqlDbType.VarChar)).Value = "Bank"; // _command.CommandTimeout = 0; using (System.Data.DataSet ds = new System.Data.DataSet()) { ds.Clear(); adp = new SqlDataAdapter(_command); adp.Fill(ds, "table"); //Dts = ds.Tables[0]; connect.Close(); if (ds.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) { //using object class var list = (from DataRow row in ds.Tables[1].Rows select new Dataset.ReportSchedule { Date = Convert.ToDateTime(row["Date"]), Debit = Convert.ToDecimal(row["Debit"]), Credit = Convert.ToDecimal(row["Credit"]), Comments = row["Comments"] as string, BankName = row["BankName"] as string, Acctnumber = row["Acctnumber"] as string, Period = row["period"] as string, Branchname = row["Branchname"] as string } ).ToList(); XtraRepSchedule repSchedule = new XtraRepSchedule(); var bindingsed = (BindingSource)repSchedule.DataSource; bindingsed.Clear(); bindingsed.DataSource = list; repSchedule.xrLabel2.Text = "Schedule of Unexplained Bank Transactions"; repSchedule.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found", Program.ApplicationName, 1); return; } } else { Common.setMessageBox(ds.Tables[1].Rows[0]["returnMessage"].ToString(), Program.ApplicationName, 2); } } } } finally { SplashScreenManager.CloseForm(false); } } else if (radioGroup3.EditValue.ToString() == "Allocate") { string strquery = string.Format("SELECT BankName ,AccountName , AccountNumber ,BSDate , Debit ,Credit ,Description , ISNULL(AllocateBy, 'No Name') AS AllocateBy , ISNULL(DateAllocate, GETDATE()) AS DateAllocate ,Months + ',' + Year AS Period ,ISNULL(ClosingBalance, 0) AS ClosingBalance , ISNULL(OpeningBalance, 0) AS OpeningBalance ,ISNULL(TotalCredit, 0) AS TotalCredit , ISNULL(TotalDebit, 0) AS TotalDebit FROM Reconciliation.tblBankStatementAllocation JOIN Reconciliation.tblBankStatement ON tblBankStatement.BSID = tblBankStatementAllocation.BSID JOIN Reconciliation.tblTransDefinition ON tblTransDefinition.TransID = tblBankStatementAllocation.TransID JOIN Reconciliation.tblBankAccount ON tblBankAccount.BankAccountID = tblBankStatement.BankAccountID AND tblBankAccount.BankShortCode = tblBankStatement.BankShortCode JOIN Collection.tblBank ON tblBank.BankShortCode = tblBankStatement.BankShortCode JOIN Reconciliation.tblFinancialperiod ON tblFinancialperiod.FinancialperiodID = tblBankStatement.FinancialperiodID JOIN Reconciliation.tblTransactionPostingRequest ON tblTransactionPostingRequest.BankAccountID = tblBankAccount.BankAccountID AND tblTransactionPostingRequest.BankShortCode = tblBank.BankShortCode AND tblTransactionPostingRequest.FinancialperiodID = tblFinancialperiod.FinancialperiodID WHERE tblBankStatement.BankShortCode ='{0}' AND tblBankStatement.BankAccountID='{1}' AND tblBankStatement.FinancialperiodID='{2}'", dr["BankShortCode"], dr["BankAccountID"], dr["FinancialperiodID"]); using (var ds = new System.Data.DataSet()) { //connect.connect.Open(); using (SqlDataAdapter ada = new SqlDataAdapter(strquery, Logic.ConnectionString)) { ada.Fill(ds, "table"); } //var Dt = ds.Tables[0]; if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { var listAllocate = (from DataRow row in ds.Tables[0].Rows select new Dataset.Allocation { BankName = row["BankName"] as string, AccountName = row["AccountName"] as string, AccountNumber = row["AccountNumber"] as string, BSDate = Convert.ToDateTime(row["BSDate"]), Debit = Convert.ToDecimal(row["Debit"]), Credit = Convert.ToDecimal(row["Credit"]), Description = row["Description"] as string, AllocateBy = row["AllocateBy"] as string, DateAllocate = Convert.ToDateTime(row["DateAllocate"]), Period = row["Period"] as string, ClosingBalance = Convert.ToDecimal(row["ClosingBalance"]), OpeningBalance = Convert.ToDecimal(row["OpeningBalance"]), TotalCredit = Convert.ToDecimal(row["TotalCredit"]), TotalDebit = Convert.ToDecimal(row["TotalDebit"]) }).ToList(); XtraRepAllocatereport allocate = new XtraRepAllocatereport() { DataSource = listAllocate }; allocate.ShowPreviewDialog(); //var binding = (BindingSource)allocate.DataSource; //binding.Clear(); //binding.DataSource = listAllocate; ////allocate.xrLabel2.Text = "Bank not in Reems"; //allocate.ShowPreviewDialog(); ////repyear.xrLabel11.Text = string.Format("Bank Name: {0}", cboBank.Text.Trim()); ////repyear.xrLabel12.Text = //// string.Format("List of Transactions in Bank Statement not in Reems between {0:dd/MM/yyyy} and {1:dd/MM/yyyy}", dtpStart.Value, dtpEnd.Value); ////repyear.DataSource = replist; ////repyear.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found", Program.ApplicationName, 1); return; } } } //BReems else if (radioGroup3.EditValue.ToString() == "BReems") { try { SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false); using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("BankTransaction", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@bankcode", SqlDbType.VarChar)).Value = dr["BankShortCode"]; _command.Parameters.Add(new SqlParameter("@Periods", SqlDbType.Int)).Value = Convert.ToInt32(dr["FinancialperiodID"]); _command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["StartDate"]); _command.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["EndDate"]); _command.Parameters.Add(new SqlParameter("@BankAccountID", SqlDbType.Int)).Value = Convert.ToInt32(dr["BankAccountID"]); _command.Parameters.Add(new SqlParameter("@PType", SqlDbType.VarChar)).Value = "BReems"; _command.CommandTimeout = 0; using (System.Data.DataSet ds = new System.Data.DataSet()) { ds.Clear(); adp = new SqlDataAdapter(_command); adp.Fill(ds, "table"); //Dts = ds.Tables[0]; connect.Close(); if (ds.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) { //using object class var list = (from DataRow row in ds.Tables[1].Rows select new Dataset.ReportSchedule { Date = Convert.ToDateTime(row["Date"]), //Debit = Convert.ToDecimal(row["Debit"]), Credit = Convert.ToDecimal(row["Credit"]), Comments = row["Description"] as string, BankName = row["BankName"] as string, Acctnumber = row["Acctnumber"] as string, Period = row["peroids"] as string, Branchname = row["Branchname"] as string } ).ToList(); XtraRepSchedule repSchedule = new XtraRepSchedule(); var binding = (BindingSource)repSchedule.DataSource; binding.Clear(); binding.DataSource = list; repSchedule.xrLabel2.Text = "Bank not in Reems"; repSchedule.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found", Program.ApplicationName, 1); return; } } else { Common.setMessageBox(ds.Tables[1].Rows[0]["returnMessage"].ToString(), Program.ApplicationName, 2); } } } } finally { SplashScreenManager.CloseForm(false); } } //posted else if (radioGroup3.EditValue.ToString() == "Posted") { try { SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false); using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("BankTransaction", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@bankcode", SqlDbType.VarChar)).Value = dr["BankShortCode"]; _command.Parameters.Add(new SqlParameter("@Periods", SqlDbType.Int)).Value = Convert.ToInt32(dr["FinancialperiodID"]); _command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["StartDate"]); _command.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["EndDate"]); _command.Parameters.Add(new SqlParameter("@BankAccountID", SqlDbType.Int)).Value = Convert.ToInt32(dr["BankAccountID"]); _command.Parameters.Add(new SqlParameter("@PType", SqlDbType.VarChar)).Value = "Posted"; _command.CommandTimeout = 0; using (System.Data.DataSet ds = new System.Data.DataSet()) { ds.Clear(); adp = new SqlDataAdapter(_command); adp.Fill(ds, "table"); //Dts = ds.Tables[0]; connect.Close(); if (ds.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) { //using object class var list = (from DataRow row in ds.Tables[1].Rows select new Dataset.Payment { Amount = Convert.ToDecimal(row["Amount"]), PaymentDate = Convert.ToDateTime(row["PaymentDate"]), PaymentRefNumber = row["PaymentRefNumber"] as string, Description = row["Description"] as string, PayerName = row["PayerID"] as string, Transdate = Convert.ToDateTime(row["BSDate"]), AgecnyName = row["AgencyName"] as string, BankName = row["BankName"] as string, //Branchname = row["Branchname"] as string } ).ToList(); //string state=Program.StateName; XtraRepPostedTrans payment = new XtraRepPostedTrans(); //XtraRepPayment payment = new XtraRepPayment(); //payment.xrLabel1.Text = "OGUN STATE GOVERNMENT OF NIGERIA"; payment.xrLabel6.Text = string.Format("{0} STATE GOVERNMENT OF NIGERIA", Program.StateName.ToUpper()); payment.xrLabel7.Text = string.Format("List of Collections Posted for the month of {0} ", string.Format("{0:MMMM yyyy}", dr["Descriptions"])); var binding = (BindingSource)payment.DataSource; binding.Clear(); binding.DataSource = list; payment.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found", Program.ApplicationName, 1); return; } } else { Common.setMessageBox(ds.Tables[1].Rows[0]["returnMessage"].ToString(), Program.ApplicationName, 2); } } } } catch (Exception ex) { Common.setMessageBox(String.Format("{0}..{1}", ex.Message, ex.StackTrace), "Report", 2); return; } finally { SplashScreenManager.CloseForm(false); } } //Payment else if (radioGroup3.EditValue.ToString() == "Payment") { try { SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false); using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("BankTransaction", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@bankcode", SqlDbType.VarChar)).Value = dr["BankShortCode"]; _command.Parameters.Add(new SqlParameter("@Periods", SqlDbType.Int)).Value = Convert.ToInt32(dr["FinancialperiodID"]); _command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["StartDate"]); _command.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["EndDate"]); _command.Parameters.Add(new SqlParameter("@BankAccountID", SqlDbType.Int)).Value = Convert.ToInt32(dr["BankAccountID"]); _command.Parameters.Add(new SqlParameter("@PType", SqlDbType.VarChar)).Value = "Payment"; _command.CommandTimeout = 0; using (System.Data.DataSet ds = new System.Data.DataSet()) { ds.Clear(); adp = new SqlDataAdapter(_command); adp.Fill(ds, "table"); //Dts = ds.Tables[0]; connect.Close(); if (ds.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) { //using object class var list = (from DataRow row in ds.Tables[1].Rows select new Dataset.Payment { Amount = Convert.ToDecimal(row["Amount"]), PaymentDate = Convert.ToDateTime(row["PaymentDate"]), PaymentRefNumber = row["PaymentRefNumber"] as string, Description = row["Description"] as string, PayerName = row["PayerName"] as string, //Branchname = row["Branchname"] as string } ).ToList(); //string state=Program.StateName; XtraRepPayment payment = new XtraRepPayment(); //payment.xrLabel1.Text = "OGUN STATE GOVERNMENT OF NIGERIA"; payment.xrLabel1.Text = string.Format("{0} STATE GOVERNMENT OF NIGERIA", Program.StateName.ToUpper()); payment.xrLabel2.Text = string.Format("Schedule of REEMS Platform Payments Not Posted into Bank Statement for month of {0} ", string.Format("{0:MMMM yyyy}", dr["EndDate"])); var binding = (BindingSource)payment.DataSource; binding.Clear(); binding.DataSource = list; payment.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found", Program.ApplicationName, 1); return; } } else { Common.setMessageBox(ds.Tables[1].Rows[0]["returnMessage"].ToString(), Program.ApplicationName, 2); } } } } finally { SplashScreenManager.CloseForm(false); } } //reems else if (radioGroup3.EditValue.ToString() == "Reems") { try { SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false); using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("BankTransaction", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@bankcode", SqlDbType.VarChar)).Value = dr["BankShortCode"]; _command.Parameters.Add(new SqlParameter("@Periods", SqlDbType.Int)).Value = Convert.ToInt32(dr["FinancialperiodID"]); _command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["StartDate"]); _command.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy/MM/dd}", dr["EndDate"]); _command.Parameters.Add(new SqlParameter("@BankAccountID", SqlDbType.Int)).Value = Convert.ToInt32(dr["BankAccountID"]); _command.Parameters.Add(new SqlParameter("@PType", SqlDbType.VarChar)).Value = "Reems"; _command.CommandTimeout = 0; using (System.Data.DataSet ds = new System.Data.DataSet()) { ds.Clear(); adp = new SqlDataAdapter(_command); adp.Fill(ds, "table"); //Dts = ds.Tables[0]; connect.Close(); if (ds.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) { //using object class var list = (from DataRow row in ds.Tables[1].Rows select new Dataset.Reems { Openingbal = Convert.ToDecimal(row["openingbalance"]), closingbal = Convert.ToDecimal(row["closebalance"]), ReemsCollection = Convert.ToDecimal(row["both"]), Bankexcpec = Convert.ToDecimal(row["BankExcpection"]), Bankcredit = Convert.ToDecimal(row["BankCredit"]), BankDebit = Convert.ToDecimal(row["BankDebit"]), bankcharge = Convert.ToDecimal(row["bankcharge"]), Transferto = Convert.ToDecimal(row["Transferto"]), PrevcreditRev = Convert.ToDecimal(row["PrevcreditRev"]), ReturnCheque = Convert.ToDecimal(row["ReturnCheque"]), CurrentReversalDr = Convert.ToDecimal(row["CurrentReversalDr"]), CurrentReversalCr = Convert.ToDecimal(row["CurrentReversalCr"]), PayDirectBank = Convert.ToDecimal(row["PayDirectBank"]), CreditInterest = Convert.ToDecimal(row["CreditInterest"]), TransferFromGovtAcct = Convert.ToDecimal(row["TransferFromGovtAcct"]), PrevDebitReversed = Convert.ToDecimal(row["PrevDebitReversed"]), BankName = row["BankName"] as string, Acctnumber = row["Acctnumber"] as string, Period = row["period"] as string, Branchname = row["Branchname"] as string } ).ToList(); XtraRepBankReems bankreem = new XtraRepBankReems (); //XtraRepSchedule repSchedule = new XtraRepSchedule(); var binding = (BindingSource)bankreem.DataSource; binding.DataSource = list; //repSchedule.ShowPreviewDialog(); bankreem.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found", Program.ApplicationName, 1); return; } } else { Common.setMessageBox(ds.Tables[1].Rows[0]["returnMessage"].ToString(), Program.ApplicationName, 2); } } } } finally { SplashScreenManager.CloseForm(false); } } } else { Common.setEmptyField("Select Record...", "Get Record"); return; } } } }
void bttnUpdate_Click(object sender, EventArgs e) { if (radioGroup3.EditValue == null) { Common.setMessageBox("Selection Type not Selected....!", Program.ApplicationName, 2); return; } else { string qury = string.Empty; System.Data.DataSet response = new System.Data.DataSet(); XRepGlobalManifest Global = new XRepGlobalManifest(); XRepGlobalManifest Global2 = new XRepGlobalManifest(); XtraRepPayment payment = new XtraRepPayment(); XtraRepPayment Reprint = new XtraRepPayment(); //XtraRepPayment Reversal = new XtraRepPayment(); XtraRepReversal Reversal = new XtraRepReversal(); if (radioGroup3.EditValue.ToString() == "0") { if (cboStation.SelectedIndex == -1) { qury = "0000"; } try { using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("doGlobalManifest", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@start", SqlDbType.DateTime)).Value = string.Format("{0:yyyy-MM-dd 00:00:00}", DTPDateselect.Value.Date); _command.Parameters.Add(new SqlParameter("@end", SqlDbType.DateTime)).Value = string.Format("{0:yyyy-MM-dd 23:59:59}", DTPDateselect.Value.Date); _command.Parameters.Add(new SqlParameter("@Stationcode", SqlDbType.VarChar)).Value = qury; _command.Parameters.Add(new SqlParameter("@Type", SqlDbType.Int)).Value = radioGroup3.EditValue; _command.CommandTimeout = 0; adp = new SqlDataAdapter(_command); adp.Fill(response, "CollectionReportTable"); connect.Close(); if (response.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (response.Tables[1] != null && response.Tables[1].Rows.Count > 0)//Main Manifesthfhhf { Global.DataSource = response.Tables[1]; Global.DataAdapter = adp; Global.DataMember = "CollectionReportTable"; Global.xrLabel12.Text = "GLOBAL RECEIPT MANIFEST"; Global.xrLabel9.Text = string.Format("{0} State Government ", Program.StateName.ToUpper()); Global.logoPath = Logic.logopth; Global.CreateDocument(); if (response.Tables[2] != null && response.Tables[2].Rows.Count > 0)//unreceipted receipt Manifest { Global2.DataSource = response.Tables[2]; Global2.DataAdapter = adp; Global2.DataMember = "CollectionReportTable"; Global2.xrLabel12.Text = "GLOBAL UNRECEIPTED MANIFEST"; Global2.xrLabel9.Text = string.Format("{0} State Government ", Program.StateName.ToUpper()); Global2.CreateDocument(); } if (response.Tables[3] != null && response.Tables[3].Rows.Count > 0)//Reprinted Receipts { var listresult = (from DataRow row in response.Tables[3].Rows select new DataSet.Main { PaymentRefNumber = row["PaymentRefNumber"] as string, Receipt = row["EReceipts"] as string, ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), OldValue = row["OldRecord"] as string, ApprovalBy = row["ApprovalBy"] as string, NewValue = row["NewRecord"] as string, Description = row["Description"] as string, Type = row["Type"] as string, Amount = Convert.ToDecimal(row["Amount"]) }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); Reprint.xrLabel10.Text = "Reprinted Receipts"; Reprint.xrLabel2.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); Reprint.DataSource = listresult; Reprint.CreateDocument(); } if (response.Tables[4] != null && response.Tables[4].Rows.Count > 0)//Amendement Receipt { var listresult = (from DataRow row in response.Tables[4].Rows select new DataSet.Main { PaymentRefNumber = row["PaymentRefNumber"] as string, PayerName = row["PayerName"] as string, Receipt = row["EReceipts"] as string, ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), OldValue = row["OldRecord"] as string, ApprovalBy = row["ApprovalBy"] as string, NewValue = row["NewRecord"] as string, Description = row["Description"] as string, Type = row["Type"] as string, Amount = Convert.ToDecimal(row["Amount"]) }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); payment.xrLabel10.Text = "Amended Receipts"; payment.xrLabel2.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); payment.DataSource = listresult; payment.CreateDocument(); } if (response.Tables[5] != null && response.Tables[5].Rows.Count > 0)//Reversal receipt { var listresult = (from DataRow row in response.Tables[5].Rows select new DataSet.Reversal { PaymentRefNumber = row["PaymentRefNumber"] as string, Amount = Convert.ToDecimal(row["Amount"]), Receipt = row["EReceipts"] as string, Type = row["Type"] as string, Description = row["Description"] as string, PayerName = row["PayerName"] as string, PaymentDate = Convert.ToDateTime(row["PaymentDate"]), AgencyName = row["AgencyName"] as string, User = row["User"] as string, Bankname = row["Bankname"] as string, DepositSlipNumber = row["DepositSlipNumber"] as string } ); Reversal.xrLabel10.Text = "Reversal"; Reversal.xrLabel24.Text = Program.UserID; Reversal.xrLabel9.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); Reversal.DataSource = listresult; Reversal.CreateDocument(); } if (response.Tables[5] != null && response.Tables[5].Rows.Count > 0)//Unreceipted manifest { Global.Pages.AddRange(Reversal.Pages); } if (response.Tables[4] != null && response.Tables[4].Rows.Count > 0) { Global.Pages.AddRange(payment.Pages); } if (response.Tables[3] != null && response.Tables[3].Rows.Count > 0) { Global.Pages.AddRange(Reprint.Pages); } if (response.Tables[2] != null && response.Tables[2].Rows.Count > 0) { Global.Pages.AddRange(Global2.Pages); } // Reset all page numbers in the resulting document. Global.PrintingSystem.ContinuousPageNumbering = true; // Show the Print Preview form. Global.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found for selected Period", "TCO Report", 2); return; } } else { Common.setMessageBox("No Record Found for selected Period", "TCO Report", 2); return; } } } catch (Exception ex) { Tripous.Sys.ErrorBox(ex.Message); return; } } else { if (cboStation.SelectedIndex < -1) { Common.setEmptyField("Station Name", Program.ApplicationName); return; } else { try { using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("doGlobalManifest", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@start", SqlDbType.DateTime)).Value = string.Format("{0:yyyy-MM-dd 00:00:00}", DTPDateselect.Value.Date); _command.Parameters.Add(new SqlParameter("@end", SqlDbType.DateTime)).Value = string.Format("{0:yyyy-MM-dd 23:59:59}", DTPDateselect.Value.Date); _command.Parameters.Add(new SqlParameter("@Stationcode", SqlDbType.VarChar)).Value = cboStation.SelectedValue.ToString(); _command.Parameters.Add(new SqlParameter("@Type", SqlDbType.Int)).Value = radioGroup3.EditValue; _command.CommandTimeout = 0; adp = new SqlDataAdapter(_command); adp.Fill(response, "CollectionReportTable"); connect.Close(); if (response.Tables[0].Rows[0]["returnCode"].ToString() == "00") { if (response.Tables[1] != null && response.Tables[1].Rows.Count > 0)//Main Manifest { //Report.XRepGlobalManifest Global = new Collection.Report.XRepGlobalManifest //{ Global.DataSource = response.Tables[1]; Global.DataAdapter = adp; Global.DataMember = "CollectionReportTable"; Global.xrLabel12.Text = "GLOBAL RECEIPT MANIFEST"; //}; Global.xrLabel9.Text = string.Format("{0} State Government ", Program.StateName.ToUpper()); Global.logoPath = Logic.logopth; Global.ShowPreviewDialog(); } if (response.Tables[2] != null && response.Tables[2].Rows.Count > 0)//unreceipted receipt Manifest { Global2.DataSource = response.Tables[2]; Global2.DataAdapter = adp; Global2.DataMember = "CollectionReportTable"; Global2.xrLabel12.Text = "GLOBAL UNRECEIPTED MANIFEST"; Global2.xrLabel9.Text = string.Format("{0} State Government ", Program.StateName.ToUpper()); Global2.CreateDocument(); } if (response.Tables[3] != null && response.Tables[3].Rows.Count > 0)//Reprinted Receipts { var listresult = (from DataRow row in response.Tables[3].Rows select new DataSet.Main { PaymentRefNumber = row["PaymentRefNumber"] as string, Receipt = row["EReceipts"] as string, ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), OldValue = row["OldRecord"] as string, ApprovalBy = row["ApprovalBy"] as string, NewValue = row["NewRecord"] as string, Description = row["Description"] as string, Type = row["Type"] as string, Amount = Convert.ToDecimal(row["Amount"]) }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); Reprint.xrLabel10.Text = "Reprinted Receipts"; Reprint.xrLabel2.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); Reprint.DataSource = listresult; Reprint.CreateDocument(); } if (response.Tables[4] != null && response.Tables[4].Rows.Count > 0)//Amendement Receipt { var listresult = (from DataRow row in response.Tables[4].Rows select new DataSet.Main { PaymentRefNumber = row["PaymentRefNumber"] as string, PayerName = row["PayerName"] as string, Receipt = row["EReceipts"] as string, ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), OldValue = row["OldRecord"] as string, ApprovalBy = row["ApprovalBy"] as string, NewValue = row["NewRecord"] as string, Description = row["Description"] as string, Type = row["Type"] as string, Amount = Convert.ToDecimal(row["Amount"]) }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); payment.xrLabel10.Text = "Amended Receipts"; payment.xrLabel2.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); payment.DataSource = listresult; payment.CreateDocument(); } if (response.Tables[5] != null && response.Tables[5].Rows.Count > 0)//Reversal receipt { //var listresult = (from DataRow row in response.Tables[4].Rows // select new DataSet.Main // { // PaymentRefNumber = row["PaymentRefNumber"] as string, // Receipt = row["EReceipts"] as string, // ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), // OldValue = row["OldRecord"] as string, // ApprovalBy = row["ApprovalBy"] as string, // NewValue = row["NewRecord"] as string, // Description = row["Description"] as string, // Type = row["Type"] as string, // Amount = Convert.ToDecimal(row["Amount"]) // }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); var listresult = (from DataRow row in response.Tables[5].Rows select new DataSet.Reversal { PaymentRefNumber = row["PaymentRefNumber"] as string, Amount = Convert.ToDecimal(row["Amount"]), Receipt = row["EReceipts"] as string, Type = row["Type"] as string, Description = row["Description"] as string, PayerName = row["PayerName"] as string, PaymentDate = Convert.ToDateTime(row["PaymentDate"]), AgencyName = row["AgencyName"] as string, User = row["User"] as string, Bankname = row["Bankname"] as string, DepositSlipNumber = row["DepositSlipNumber"] as string } ); Reversal.xrLabel10.Text = "Reversal"; Reversal.xrLabel24.Text = Program.UserID; Reversal.xrLabel9.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); Reversal.DataSource = listresult; Reversal.CreateDocument(); } } if (response.Tables[5] != null && response.Tables[5].Rows.Count > 0)//Unreceipted manifest { Global.Pages.AddRange(Reversal.Pages); } if (response.Tables[4] != null && response.Tables[4].Rows.Count > 0) { Global.Pages.AddRange(payment.Pages); } if (response.Tables[3] != null && response.Tables[3].Rows.Count > 0) { Global.Pages.AddRange(Reprint.Pages); } if (response.Tables[2] != null && response.Tables[2].Rows.Count > 0) { Global.Pages.AddRange(Global2.Pages); } // Reset all page numbers in the resulting document. Global.PrintingSystem.ContinuousPageNumbering = true; Global.logoPath = Logic.logopth; // Show the Print Preview form. Global.ShowPreviewDialog(); } } catch (Exception ex) { Tripous.Sys.ErrorBox(ex.Message); return; } } //} } #region //if (radioGroup3.EditValue.ToString() == "0") //{ // qury = string.Format("SELECT [ID] ,[Provider] , [Channel] ,Collection.tblCollectionReport.[PaymentRefNumber] ,[DepositSlipNumber] , CONVERT(VARCHAR,CONVERT(DATE,[PaymentDate]),103) AS PaymentDate,[PayerID] , UPPER([PayerName]) AS [PayerName],[Amount] ,[PaymentMethod] , [ChequeNumber] ,[ChequeValueDate] ,[ChequeStatus] , [DateChequeReturned] ,[TelephoneNumber] , [ReceiptNo] , [ReceiptDate] , [PayerAddress] , [User] ,[RevenueCode] ,[Description] , [ChequeBankCode] , [ChequeBankName] , [AgencyName] , [AgencyCode] , [BankCode] , [BankName] , [BranchCode] ,[BranchName] , [ZoneCode] , [ZoneName] , [Username] , [AmountWords] , Collection.tblCollectionReport.[EReceipts] ,Collection.tblCollectionReport.[EReceiptsDate] ,[GeneratedBy] ,Collection.tblCollectionReport.[StationCode] ,(SELECT TOP 1 StationName from Receipt.tblStation WHERE tblStation.StationCode = Collection.tblCollectionReport.StationCode) AS StationName FROM Collection.tblCollectionReport WHERE Collection.tblCollectionReport.EReceipts IS NOT NULL AND (Collection.tblCollectionReport.[EReceiptsDate] BETWEEN '{0} 00:00:00' AND '{0} 23:59:59') ORDER BY Collection.tblCollectionReport.AgencyCode,Collection.tblCollectionReport.StationCode ,Collection.tblCollectionReport.RevenueCode,Collection.tblCollectionReport.EReceipts", DTPDateselect.Value.Date.ToString("MM/dd/yyyy")); //} //if (radioGroup3.EditValue.ToString() == "1") //{ // if (cboStation.SelectedIndex < -1) // { // Common.setEmptyField("Station Name", Program.ApplicationName); // return; // } // else // { // qury = string.Format(" SELECT [ID] ,[Provider] , [Channel] ,Collection.tblCollectionReport.[PaymentRefNumber] ,[DepositSlipNumber] , CONVERT(VARCHAR,CONVERT(DATE,[PaymentDate]),103) AS PaymentDate,[PayerID] , UPPER([PayerName]) AS [PayerName],[Amount] ,[PaymentMethod] , [ChequeNumber] ,[ChequeValueDate] ,[ChequeStatus] , [DateChequeReturned] ,[TelephoneNumber] , [ReceiptNo] , [ReceiptDate] , [PayerAddress] , [User] ,[RevenueCode] ,[Description] , [ChequeBankCode] , [ChequeBankName] , [AgencyName] , [AgencyCode] , [BankCode] , [BankName] , [BranchCode] ,[BranchName] , [ZoneCode] , [ZoneName] , [Username] , [AmountWords] , Collection.tblCollectionReport.[EReceipts] ,Collection.tblCollectionReport.[EReceiptsDate] ,[GeneratedBy] ,Collection.tblCollectionReport.[StationCode] ,(SELECT TOP 1 StationName from Receipt.tblStation WHERE tblStation.StationCode = Collection.tblCollectionReport.StationCode) AS StationName FROM Collection.tblCollectionReport WHERE Collection.tblCollectionReport.EReceipts IS NOT NULL AND (Collection.tblCollectionReport.[EReceiptsDate] BETWEEN '{0} 00:00:00' AND '{0} 23:59:59') AND Collection.tblCollectionReport.StationCode='{1}' ORDER BY Collection.tblCollectionReport.AgencyCode,Collection.tblCollectionReport.StationCode ,Collection.tblCollectionReport.RevenueCode,Collection.tblCollectionReport.EReceipts", DTPDateselect.Value.Date.ToString("MM/dd/yyyy"), cboStation.SelectedValue); // } //} #endregion #region olds2 //if (response.Tables[0].Rows[0]["returnCode"].ToString() == "00") //{ // if (response.Tables[1] != null && response.Tables[1].Rows.Count > 0)//Main Manifest // { // using (System.Data.DataSet dds = new System.Data.DataSet("DsCollectionReport")) // { // SqlDataAdapter ada = new SqlDataAdapter(); // //using (SqlDataAdapter ada = new SqlDataAdapter(qury, Logic.ConnectionString)) // //{ // ada.Fill(dds, "CollectionReportTable"); // //Report.XRepGlobalManifest Global = new Collection.Report.XRepGlobalManifest { DataSource = dds, DataAdapter = ada, DataMember = "CollectionReportTable", RequestParameters = true }; // Report.XRepGlobalManifest Global = new Collection.Report.XRepGlobalManifest { DataSource = response.Tables[1], DataAdapter = ada, DataMember = "CollectionReportTable" }; // Global.xrLabel9.Text = string.Format("{0} State Government ", Program.StateName.ToUpper()); // //Global.paramStartDate.Value = "EReceiptsDate"; // //Global.paramEndDate.Value = "EReceiptsDate"; // // Global.paramEndDate.Visible = false; // Global.ShowPreviewDialog(); // //} // } // } //} #endregion } }
private void BttnUpdate_Click(object sender, EventArgs e) { try { SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false); System.Data.DataSet response = new System.Data.DataSet(); using (SqlConnection connect = new SqlConnection(Logic.ConnectionString)) { connect.Open(); _command = new SqlCommand("doTCOReportManifest", connect) { CommandType = CommandType.StoredProcedure }; _command.Parameters.Add(new SqlParameter("@start", SqlDbType.DateTime)).Value = string.Format("{0:yyyy-MM-dd 00:00:00}", DTPDateselect.Value.Date); _command.Parameters.Add(new SqlParameter("@enddate", SqlDbType.DateTime)).Value = string.Format("{0:yyyy-MM-dd 23:59:59}", DTPDateselect.Value.Date); adp = new SqlDataAdapter(_command); adp.Fill(response, "CollectionReportTable"); connect.Close(); if (response.Tables[0].Rows[0]["returnCode"].ToString() == "00") { XRepManifest repManifest = new XRepManifest(); XtraRepPayment payment = new XtraRepPayment(); XtraRepPayment Reprint = new XtraRepPayment(); XtraRepReversal Reversal = new XtraRepReversal(); XRepManifest UnReceipt = new XRepManifest(); XtraRepUnreceipted Unreceipted = new XtraRepUnreceipted(); if (response.Tables[1] != null && response.Tables[1].Rows.Count > 0)//Main Manifest { repManifest.DataSource = response.Tables[1]; repManifest.DataAdapter = adp; repManifest.DataMember = "CollectionReportTable"; repManifest.RequestParameters = false; //}; repManifest.xrLabel10.Text = Program.UserID; repManifest.xrLabel9.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); //repManifest.ShowPreviewDialog(); repManifest.CreateDocument(); if (response.Tables[2] != null && response.Tables[2].Rows.Count > 0)//unreceipted receipt Manifest { //UnReceipt.DataSource = response.Tables[2]; //UnReceipt.DataAdapter = adp; //UnReceipt.DataMember = "CollectionReportTable"; //UnReceipt.RequestParameters = false; //UnReceipt.xrLabel10.Text = Program.UserID; //UnReceipt.xrLabel12.Text = "MANIFEST OF UNRECEIPTED "; //UnReceipt.xrLabel9.Text = string.Format("{0} STATE GOVERNMENT", // Program.StateName.ToUpper()); //UnReceipt.CreateDocument(); Unreceipted.DataSource = response.Tables[2]; Unreceipted.DataAdapter = adp; Unreceipted.DataMember = "CollectionReportTable"; Unreceipted.RequestParameters = false; Unreceipted.xrLabel10.Text = Program.UserID; Unreceipted.xrLabel2.Text = "(BANK BRANCH)"; Unreceipted.xrLabel12.Text = "MANIFEST OF UNRECEIPTED PAYMENT"; Unreceipted.xrLabel9.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); //Unreceipted.ShowPreviewDialog(); //return; Unreceipted.CreateDocument(); } if (checkEdit1.Checked) ///payment { if (response.Tables[3] != null && response.Tables[3].Rows.Count > 0) //Payment Change Manifest { var listresult = (from DataRow row in response.Tables[3].Rows select new DataSet.Main { PaymentRefNumber = row["PaymentRefNumber"] as string, PayerName = row["PayerName"] as string, Receipt = row["EReceipts"] as string, ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), OldValue = row["OldRecord"] as string, ApprovalBy = row["ApprovalBy"] as string, NewValue = row["NewRecord"] as string, Description = row["Description"] as string, Type = row["Type"] as string, Amount = Convert.ToDecimal(row["Amount"]) }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); payment.xrLabel10.Text = "Amended Receipts"; payment.xrLabel24.Text = Program.UserID; payment.xrLabel2.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); payment.DataSource = listresult; payment.CreateDocument(); } } if (checkEdit2.Checked) //Reprint { if (response.Tables[4] != null && response.Tables[4].Rows.Count > 0) //Reprint Manifest { var listresult = (from DataRow row in response.Tables[4].Rows select new DataSet.Main { PaymentRefNumber = row["PaymentRefNumber"] as string, Receipt = row["EReceipts"] as string, ApprovalDate = Convert.ToDateTime(row["ApprovalDate"]), OldValue = row["OldRecord"] as string, ApprovalBy = row["ApprovalBy"] as string, NewValue = row["NewRecord"] as string, Description = row["Description"] as string, Type = row["Type"] as string, Amount = Convert.ToDecimal(row["Amount"]) }).ToList(); //XtraRepPayment payment = new XtraRepPayment(); Reprint.xrLabel10.Text = "Reprinted Receipts"; Reprint.xrLabel24.Text = Program.UserID; Reprint.xrLabel2.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); Reprint.DataSource = listresult; Reprint.CreateDocument(); } } if (checkEdit3.Checked) //Reseveral { if (response.Tables[5] != null && response.Tables[5].Rows.Count > 0) //Reversal receipt Manifest { var listresult = (from DataRow row in response.Tables[5].Rows select new DataSet.Reversal { PaymentRefNumber = row["PaymentRefNumber"] as string, Amount = Convert.ToDecimal(row["Amount"]), Receipt = row["EReceipts"] as string, Type = row["Type"] as string, Description = row["Description"] as string, PayerName = row["PayerName"] as string, PaymentDate = Convert.ToDateTime(row["PaymentDate"]), AgencyName = row["AgencyName"] as string, User = row["User"] as string, Bankname = row["Bankname"] as string, DepositSlipNumber = row["DepositSlipNumber"] as string } ); Reversal.xrLabel10.Text = "Reversal"; Reversal.xrLabel24.Text = Program.UserID; Reversal.xrLabel9.Text = string.Format("{0} STATE GOVERNMENT", Program.StateName.ToUpper()); Reversal.DataSource = listresult; Reversal.CreateDocument(); } } if (response.Tables[2] != null && response.Tables[2].Rows.Count > 0) { repManifest.Pages.AddRange(Unreceipted.Pages); } if (checkEdit1.Checked)///payment { if (response.Tables[3] != null && response.Tables[3].Rows.Count > 0) { repManifest.Pages.AddRange(payment.Pages); } } if (checkEdit2.Checked)//Reprint { if (response.Tables[4] != null && response.Tables[4].Rows.Count > 0) { repManifest.Pages.AddRange(Reprint.Pages); } } if (checkEdit3.Checked) //Reseveral { if (response.Tables[5] != null && response.Tables[5].Rows.Count > 0) //Unreceipted manifest { repManifest.Pages.AddRange(Reversal.Pages); } } // Reset all page numbers in the resulting document. repManifest.PrintingSystem.ContinuousPageNumbering = true; // Show the Print Preview form. repManifest.ShowPreviewDialog(); } else { Common.setMessageBox("No Record Found for selected Period", "TCO Report", 2); return; } } else { Tripous.Sys.ErrorBox(response.Tables[0].Rows[0]["returnMessage"].ToString()); return; } } } catch (Exception ex) { Tripous.Sys.ErrorBox(ex.Message); return; } finally { SplashScreenManager.CloseForm(false); } }