/// <summary>
/// Function to edit partybalance based on VoucherNo, VoucherTypeId And ReferenceType
/// </summary>
/// <param name="infoPartyBalance"></param>
public void PartyBalanceEditByVoucherNoVoucherTypeIdAndReferenceType(PartyBalanceInfo infoPartyBalance)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("PartyBalanceEditByVoucherNoVoucherTypeIdAndReferenceType", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@date", SqlDbType.DateTime);
sprmparam.Value = infoPartyBalance.Date;
sprmparam = sccmd.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.LedgerId;
sprmparam = sccmd.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.VoucherTypeId;
sprmparam = sccmd.Parameters.Add("@voucherNo", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.VoucherNo;
sprmparam = sccmd.Parameters.Add("@againstVoucherTypeId", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.AgainstVoucherTypeId;
sprmparam = sccmd.Parameters.Add("@againstVoucherNo", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.AgainstVoucherNo;
sprmparam = sccmd.Parameters.Add("@invoiceNo", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.InvoiceNo;
sprmparam = sccmd.Parameters.Add("@againstInvoiceNo", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.AgainstInvoiceNo;
sprmparam = sccmd.Parameters.Add("@referenceType", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.ReferenceType;
sprmparam = sccmd.Parameters.Add("@debit", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.Debit;
sprmparam = sccmd.Parameters.Add("@credit", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.Credit;
sprmparam = sccmd.Parameters.Add("@creditPeriod", SqlDbType.Int);
sprmparam.Value = infoPartyBalance.CreditPeriod;
sprmparam = sccmd.Parameters.Add("@exchangeRateId", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.ExchangeRateId;
sprmparam = sccmd.Parameters.Add("@financialYearId", SqlDbType.Decimal);
sprmparam.Value = infoPartyBalance.FinancialYearId;
sprmparam = sccmd.Parameters.Add("@extraDate", SqlDbType.DateTime);
sprmparam.Value = infoPartyBalance.ExtraDate;
sprmparam = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.Extra1;
sprmparam = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam.Value = infoPartyBalance.Extra2;
sccmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlcon.Close();
}
}
/// <summary>
/// Function to get particular values from PartyBalance table based on the parameter
/// </summary>
/// <param name="partyBalanceId"></param>
/// <returns></returns>
public PartyBalanceInfo PartyBalanceView(decimal partyBalanceId)
{
PartyBalanceInfo partybalanceinfo = new PartyBalanceInfo();
SqlDataReader sdrreader = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("PartyBalanceView", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@partyBalanceId", SqlDbType.Decimal);
sprmparam.Value = partyBalanceId;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
partybalanceinfo.PartyBalanceId = decimal.Parse(sdrreader[0].ToString());
partybalanceinfo.Date = DateTime.Parse(sdrreader[1].ToString());
partybalanceinfo.LedgerId = decimal.Parse(sdrreader[2].ToString());
partybalanceinfo.VoucherTypeId = decimal.Parse(sdrreader[3].ToString());
partybalanceinfo.VoucherNo = sdrreader[4].ToString();
partybalanceinfo.AgainstVoucherTypeId = decimal.Parse(sdrreader[5].ToString());
partybalanceinfo.AgainstVoucherNo = sdrreader[6].ToString();
partybalanceinfo.InvoiceNo = sdrreader[7].ToString();
partybalanceinfo.AgainstInvoiceNo = sdrreader[8].ToString();
partybalanceinfo.ReferenceType = sdrreader[9].ToString();
partybalanceinfo.Debit = decimal.Parse(sdrreader[10].ToString());
partybalanceinfo.Credit = decimal.Parse(sdrreader[11].ToString());
partybalanceinfo.CreditPeriod = int.Parse(sdrreader[12].ToString());
partybalanceinfo.ExchangeRateId = decimal.Parse(sdrreader[13].ToString());
partybalanceinfo.FinancialYearId = decimal.Parse(sdrreader[14].ToString());
partybalanceinfo.ExtraDate = DateTime.Parse(sdrreader[15].ToString());
partybalanceinfo.Extra1 = sdrreader[16].ToString();
partybalanceinfo.Extra2 = sdrreader[17].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sdrreader.Close();
sqlcon.Close();
}
return(partybalanceinfo);
}
/// <summary>
/// Function to fill datagridview
/// </summary>
public void GridFill()
{
try
{
PartyBalanceInfo infoPartyBalance = new PartyBalanceInfo();
PartyBalanceSP SpPartyBalance = new PartyBalanceSP();
DataTable dtbl = new DataTable();
decimal decVoucherTypeId = 0;
decimal decVoucherTypeNameId = 0;
decVoucherTypeId = Convert.ToDecimal(cmbVoucherType.SelectedValue.ToString());
decVoucherTypeNameId = Convert.ToDecimal(cmbVoucherTypeName.SelectedValue.ToString());
dtbl = SpPartyBalance.AgainstBillDetailsGridViewByLedgerId(decledgerId, strDebitOrCredit, decVoucherTypeId, decVoucherTypeNameId);
dgvAgainstBillDetails.DataSource = dtbl;
}
catch (Exception ex)
{
formMDI.infoError.ErrorString = "AB3:" + ex.Message;
}
}
/// <summary>
/// Function to fill againstbill details by ledgerId
/// </summary>
/// <param name="decLedgerId"></param>
/// <param name="strCrDr"></param>
/// <param name="decVoucherTypeId"></param>
/// <param name="decVoucherTypeNameId"></param>
/// <returns></returns>
public DataTable AgainstBillDetailsGridViewByLedgerId(decimal decLedgerId, string strCrDr, decimal decVoucherTypeId, decimal decVoucherTypeNameId)
{
PartyBalanceInfo partybalanceinfo = new PartyBalanceInfo();
DataTable dtbl = new DataTable();
dtbl.Columns.Add("SL.NO", typeof(decimal));
dtbl.Columns["SL.NO"].AutoIncrement = true;
dtbl.Columns["SL.NO"].AutoIncrementSeed = 1;
dtbl.Columns["SL.NO"].AutoIncrementStep = 1;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("AgainstBillDetailsGridViewByLedgerId", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sprmparam.Value = decLedgerId;
sprmparam = sccmd.Parameters.Add("@crOrDr", SqlDbType.VarChar);
sprmparam.Value = strCrDr;
sprmparam = sccmd.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sprmparam.Value = decVoucherTypeId;
sprmparam = sccmd.Parameters.Add("@voucherTypeNameId", SqlDbType.Decimal);
sprmparam.Value = decVoucherTypeNameId;
SqlDataAdapter sdaadapter = new SqlDataAdapter();
sdaadapter.SelectCommand = sccmd;
sdaadapter.Fill(dtbl);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(dtbl);
}
/// <summary>
/// Function to get particular values from PartyBalance table based on vouchertype and voucherno
/// </summary>
/// <param name="decVoucherTypeId"></param>
/// <param name="strVoucherNo"></param>
/// <returns></returns>
public PartyBalanceInfo PartyBalanceViewByVoucherNoAndVoucherTypeId(decimal decVoucherTypeId, string strVoucherNo, DateTime dtDate)
{
PartyBalanceInfo partybalanceinfo = new PartyBalanceInfo();
SqlDataReader sdrreader = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("PartyBalanceViewByVoucherNoAndVoucherType", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sprmparam.Value = decVoucherTypeId;
sprmparam = sccmd.Parameters.Add("@voucherNo", SqlDbType.VarChar);
sprmparam.Value = strVoucherNo;
sprmparam = sccmd.Parameters.Add("@date", SqlDbType.DateTime);
sprmparam.Value = dtDate;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
partybalanceinfo.PartyBalanceId = decimal.Parse(sdrreader[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(partybalanceinfo);
}
