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);
}
}
