public List <StatementOfAccDom> ReadAgentAddress(int agentId)
{
List <StatementOfAccDom> lstAgentAddress = new List <StatementOfAccDom>();
DbCommand dbcommand = myDataBase.GetSqlStringCommand("select AGENT_ADDRESS,AGENT_ID,OPENING_BALANCE from AGENT where AGENT_ID='" + agentId + "'");
using (IDataReader reader = myDataBase.ExecuteReader(dbcommand))
{
while (reader.Read())
{
StatementOfAccDom stmtOfAcc = new StatementOfAccDom();
stmtOfAcc.AgentId = GetIntegerFromDataReader(reader, "AGENT_ID");
stmtOfAcc.AgentAddress = GetStringFromDataReader(reader, "AGENT_ADDRESS");
stmtOfAcc.BalanceOpening = GetDecimalFromDataReader(reader, "OPENING_BALANCE");
lstAgentAddress.Add(stmtOfAcc);
}
}
return(lstAgentAddress);
}
public List <StatementOfAccDom> ReadAgentName()
{
List <StatementOfAccDom> lstAgentName = new List <StatementOfAccDom>();
DbCommand dbcommand = myDataBase.GetSqlStringCommand("select * from AGENT order by AGENT_NAME asc");
using (IDataReader reader = myDataBase.ExecuteReader(dbcommand))
{
while (reader.Read())
{
StatementOfAccDom stmtOfAcc = new StatementOfAccDom();
stmtOfAcc.AgentId = GetIntegerFromDataReader(reader, "AGENT_ID");
stmtOfAcc.AgentName = GetStringFromDataReader(reader, "AGENT_NAME");
stmtOfAcc.AgentAddress = GetStringFromDataReader(reader, "AGENT_ADDRESS");
//stmtOfAcc.AgentAddress = GetStringFromDataReader(reader, "AGENT_ADDRESS");
lstAgentName.Add(stmtOfAcc);
}
}
return(lstAgentName);
}
public List <StatementOfAccDom> ReadStatementofAccById(int agentId, DateTime fromDate, DateTime toDate, int?LocId)
{
List <StatementOfAccDom> lststmtdom = new List <StatementOfAccDom>();
DbCommand dbcommand = myDataBase.GetStoredProcCommand(DBConstant.READ_STATEMENTOFACCOUNT);
if (LocId != 0)
{
myDataBase.AddInParameter(dbcommand, "@LocationId", DbType.Int32, LocId);
}
else
{
myDataBase.AddInParameter(dbcommand, "@LocationId", DbType.Int32, DBNull.Value);
}
myDataBase.AddInParameter(dbcommand, "@agentId", DbType.Int32, agentId);
myDataBase.AddInParameter(dbcommand, "@in_from_date", DbType.DateTime, fromDate);
myDataBase.AddInParameter(dbcommand, "@in_to_date", DbType.DateTime, toDate);
using (IDataReader reader = myDataBase.ExecuteReader(dbcommand))
{
while (reader.Read())
{
StatementOfAccDom stmtOfAccDom = new StatementOfAccDom();
stmtOfAccDom.Narration = GetStringFromDataReader(reader, "Narration");
stmtOfAccDom.Particular = GetStringFromDataReader(reader, "Particular");
stmtOfAccDom.DocDate = GetStringFromDataReader(reader, "DocDate");
stmtOfAccDom.CrAmount = GetDecimalFromDataReader(reader, "CrAmount");
stmtOfAccDom.DrAmount = GetDecimalFromDataReader(reader, "DrAmount");
//stmtOfAccDom.TotalAmmount = GetDecimalFromDataReader(reader, "totamount");
stmtOfAccDom.AgentName = GetStringFromDataReader(reader, "AGENT_NAME");
stmtOfAccDom.AgentAddress = GetStringFromDataReader(reader, "AGENT_ADDRESS");
stmtOfAccDom.InvoiceType = GetStringFromDataReader(reader, "Type");
stmtOfAccDom.BalanceOpening = GetDecimalFromDataReader(reader, "Opening");
stmtOfAccDom.AgentId = GetIntegerFromDataReader(reader, "AGENT_ID");
stmtOfAccDom.FromDate = GetDateFromReader(reader, "FromDate");
stmtOfAccDom.ToDate = GetDateFromReader(reader, "Todate");
lststmtdom.Add(stmtOfAccDom);
openingBalance = stmtOfAccDom.BalanceOpening;
}
}
return(lststmtdom);
}
protected void btndisplay_Click(object sender, EventArgs e)
{
StatementOfAccDom stateDom = new StatementOfAccDom();
int agentId = Convert.ToInt32(ddlagentList.SelectedItem.Value);
DateTime FromDate = Convert.ToDateTime(txtfrmdate.Text);
DateTime ToDate = Convert.ToDateTime(txttodate.Text);
lblStatDate.Text = FromDate.ToShortDateString() + " To " + ToDate.ToShortDateString();
lblPartyName.Text = ddlagentList.SelectedItem.Text;
int LocId = basePage.LoggedInUser.UserLocation.LocationId;
int userId = basePage.LoggedInUser.UserTypeId;
if (ToDate < FromDate)
{
Response.Write("<Script language='javascript'>alert('From Date Must Be Less Than To Date')</Script>");
grdStatement.DataSource = null;
grdStatement.DataBind();
}
foreach (ListItem listItem in ddlagentList.Items)
{
if (listItem != null)
{
List <StatementOfAccDom> lstAgentAddrs = stmtOfAccBal.ReadAgentAddress(agentId);
lblPartyAddress.Text = lstAgentAddrs[0].AgentAddress.ToString();
lblOpenBalance.Text = lstAgentAddrs[0].BalanceOpening.ToString();
}
}
if (userId == Convert.ToInt32(UserType.Admin))
{
LocId = 0;
lst = stmtOfAccBal.ReadStatementofAccById(agentId, FromDate, ToDate, LocId);
}
else
{
lst = stmtOfAccBal.ReadStatementofAccById(agentId, FromDate, ToDate, LocId);
}
if (lst.Count != 0)
{
List <StatementOfAccDom> lstNew = new List <StatementOfAccDom>();
decimal balance = 0;
for (int i = 0; i < lst.Count; i++)
{
StatementOfAccDom stmtDom = new StatementOfAccDom();
if (i == 0)
{
stmtDom.DocDate = lst[i].DocDate;
stmtDom.Narration = lst[i].Narration;
stmtDom.Particular = lst[i].Particular;
stmtDom.CrAmount = lst[i].CrAmount;
stmtDom.DrAmount = lst[i].DrAmount;
decimal OpeningBalance = Convert.ToDecimal(lblOpenBalance.Text);
stmtDom.Balance = ((OpeningBalance + stmtDom.DrAmount) - stmtDom.CrAmount);
balance = stmtDom.Balance;
lstNew.Add(stmtDom);
}
else
{
stmtDom.DocDate = lst[i].DocDate;
stmtDom.Narration = lst[i].Narration;
stmtDom.Particular = lst[i].Particular;
stmtDom.CrAmount = lst[i].CrAmount;
stmtDom.DrAmount = lst[i].DrAmount;
decimal OpeningBalance = Convert.ToDecimal(lblOpenBalance.Text);
stmtDom.Balance = balance + stmtDom.DrAmount - stmtDom.CrAmount;
balance = stmtDom.Balance;
lstNew.Add(stmtDom);
}
}
grdStatement.DataSource = lstNew;
grdStatement.DataBind();
Session["lstnew"] = lstNew;
//btndisplay.Visible = true;
//btnCancel.Visible = true;
tblSecond.Visible = true;
//tblFirst.Visible = true;
btnExport.Visible = true;
grdStatement.Visible = true;
}
else
{
Response.Write("<Script language='javascript'>alert('No Record Found ')</Script>");
grdStatement.DataSource = null;
grdStatement.DataBind();
//btndisplay.Visible = true;
//btnCancel.Visible = true;
tblSecond.Visible = false;
//tblFirst.Visible = true;
btnExport.Visible = false;
grdStatement.Visible = true;
}
//Clear();
}
