internal DataTable LoadNarration(JournalVoucherModel objJVModel)
{
try
{
ClsCon.cmd = new SqlCommand();
ClsCon.cmd.CommandType = CommandType.StoredProcedure;
ClsCon.cmd.CommandText = "SPMasters";
ClsCon.cmd.Parameters.AddWithValue("@DataInd", objJVModel.Ind);
ClsCon.cmd.Parameters.AddWithValue("@OrgID", objJVModel.OrgID);
ClsCon.cmd.Parameters.AddWithValue("@BrID", objJVModel.BrID);
ClsCon.cmd.Parameters.AddWithValue("@YrCD", objJVModel.YrCD);
ClsCon.cmd.Parameters.AddWithValue("@VchType", objJVModel.VchType);
con = ClsCon.SqlConn();
ClsCon.cmd.Connection = con;
dtJV = new DataTable();
ClsCon.da = new SqlDataAdapter(ClsCon.cmd);
ClsCon.da.Fill(dtJV);
dtJV.TableName = "success";
}
catch (Exception)
{
dtJV = new DataTable();
dtJV.TableName = "error";
return(dtJV);
}
finally
{
con.Close();
con.Dispose();
ClsCon.da.Dispose();
ClsCon.cmd.Dispose();
}
return(dtJV);
}
private void ExecuteSave()
{
JournalVoucherModel journalvoucher = new JournalVoucherModel();
string jsondata = JsonConvert.SerializeObject(Entity);
System.IO.File.WriteAllText($@"{OfflinePath}\{Entity.VoucherNo}.abs", jsondata);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
JournalVoucherModel objJVmodel = new JournalVoucherModel();
if (tbxVchNo.Text.Trim() == "")
{
MessageBox.Show("Voucher Number Can Not Be Blank!");
return;
}
objJVmodel.Voucher_Series = tbxVoucherSeries.Text.Trim();
objJVmodel.JV_Date = Convert.ToDateTime(dtDate.Text);
objJVmodel.Type = tbxType.Text.Trim() == null ? string.Empty : tbxType.Text.Trim();
objJVmodel.Voucher_Number = Convert.ToInt32(tbxVchNo.Text.Trim());
objJVmodel.PDCDate = Convert.ToDateTime(dtPDCDate.Text);
objJVmodel.LongNarration = tbxLongNarration.Text.Trim() == null ? string.Empty : tbxLongNarration.Text.Trim();
objJVmodel.TotalCreditAmt = Convert.ToDecimal(colCredit.SummaryItem.SummaryValue);
objJVmodel.TotalDebitAmt = Convert.ToDecimal(colDebit.SummaryItem.SummaryValue);
//Journal details
AccountModel objacc;
List <AccountModel> lstAccounts = new List <AccountModel>();
for (int i = 0; i < gdvJournalDetails.DataRowCount; i++)
{
DataRow row = gdvJournalDetails.GetDataRow(i);
objacc = new AccountModel();
objacc.DC = row["DC"].ToString();
objacc.LedgerId = objAccBL.GetLedgerIdByAccountName(row["Account"].ToString());
objacc.Account = row["Account"].ToString();
objacc.Debit = Convert.ToDecimal(row["Debit"].ToString() == string.Empty ? "0" : row["Debit"].ToString());
objacc.Credit = Convert.ToDecimal(row["Credit"].ToString() == string.Empty ? "0" : row["Credit"].ToString());
objacc.Narration = row["Narration"].ToString() == string.Empty ? string.Empty : row["Narration"].ToString();
objacc.AC_Id = Convert.ToInt32(row["AC_Id"].ToString() == string.Empty ? "0" : row["AC_Id"].ToString());
objacc.ParentId = Convert.ToInt32(row["ParentId"].ToString() == string.Empty ? "0" : row["ParentId"].ToString());
lstAccounts.Add(objacc);
}
objJVmodel.JournalAccountModel = lstAccounts;
objJVmodel.JV_Id = Convert.ToInt32(Journl_Id);
bool isSuccess = objJVbal.UpdateJournalVoucher(objJVmodel);
if (isSuccess)
{
MessageBox.Show("Update Successfully!");
Journl_Id = 0;
ClearFormValues();
Transaction.List.JournalVouchersList frmList = new Transaction.List.JournalVouchersList();
frmList.StartPosition = FormStartPosition.CenterScreen;
frmList.ShowDialog();
FillJournalVoucherInfo();
}
}
public List <JournalVoucherModel> GetJournalbyId(long id)
{
List <JournalVoucherModel> lstJournal = new List <JournalVoucherModel>();
JournalVoucherModel objjvm;
string Query = "SELECT * FROM journal_voucher_master WHERE JV_Id=" + id;
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objjvm = new JournalVoucherModel();
objjvm.JV_Id = DataFormat.GetInteger(dr["JV_ID"]);
objjvm.Voucher_Series = dr["Series"].ToString();
objjvm.JV_Date = DataFormat.GetDateTime(dr["JV_Date"]);
objjvm.Voucher_Number = DataFormat.GetInteger(dr["VoucherNo"]);
objjvm.Type = dr["Type"].ToString();
if (dr["PDC_Date"].ToString() != "")
{
objjvm.PDCDate = Convert.ToDateTime(dr["PDC_Date"]);
}
objjvm.LongNarration = dr["LongNarration"].ToString();
string itemQuery = "SELECT * FROM journal_voucher_details WHERE JV_Id=" + id;
System.Data.IDataReader drAcc = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objjvm.JournalAccountModel = new List <AccountModel>();
AccountModel objAcc;
while (drAcc.Read())
{
objAcc = new AccountModel();
objAcc.AC_Id = DataFormat.GetInteger(drAcc["AC_Id"]);
objAcc.ParentId = DataFormat.GetInteger(drAcc["JV_Id"]);
objAcc.DC = drAcc["DC"].ToString();
objAcc.Account = drAcc["Account"].ToString();
objAcc.Debit = Convert.ToDecimal(drAcc["Debit"]);
objAcc.LedgerId = Convert.ToInt64(drAcc["LegderId"].ToString());
objAcc.Credit = Convert.ToDecimal(drAcc["Credit"]);
objAcc.Narration = drAcc["Narration"].ToString();
objjvm.JournalAccountModel.Add(objAcc);
}
lstJournal.Add(objjvm);
}
return(lstJournal);
}
public List <JournalVoucherModel> GetAllJournalVouchers()
{
List <JournalVoucherModel> lstJournal = new List <JournalVoucherModel>();
JournalVoucherModel objjournal;
string Query = "SELECT * FROM Journal_Voucher";
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objjournal = new JournalVoucherModel();
objjournal.JV_Id = DataFormat.GetInteger(dr["JV_Id"]);
objjournal.Voucher_Series = dr["Series"].ToString();
objjournal.JV_Date = DataFormat.GetDateTime(dr["JV_Date"]);
objjournal.Voucher_Number = DataFormat.GetInteger(dr["VoucherNo"]);
objjournal.Type = dr["Type"].ToString();
objjournal.PDCDate = Convert.ToDateTime(dr["PDCDate"].ToString());
//SELECT Journal Voucher Accounts
string itemQuery = "SELECT * FROM Journal_Voucher_Accounts WHERE Journal_Id=" + objjournal.JV_Id;
System.Data.IDataReader drAcc = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objjournal.JournalAccountModel = new List <AccountModel>();
AccountModel objAcc;
while (drAcc.Read())
{
objAcc = new AccountModel();
objAcc.AC_Id = DataFormat.GetInteger(drAcc["AC_Id"]);
objAcc.ParentId = DataFormat.GetInteger(drAcc["Journal_Id"]);
objAcc.DC = drAcc["DC"].ToString();
objAcc.Account = drAcc["Account"].ToString();
objAcc.Debit = Convert.ToDecimal(drAcc["Debit"]);
objAcc.Credit = Convert.ToDecimal(drAcc["Credit"]);
objAcc.Narration = drAcc["Narration"].ToString();
objjournal.JournalAccountModel.Add(objAcc);
}
lstJournal.Add(objjournal);
}
return(lstJournal);
}
internal DataTable SaveJV(JournalVoucherModel objJVModel)
{
try
{
ClsCon.cmd = new SqlCommand();
ClsCon.cmd.CommandType = CommandType.StoredProcedure;
ClsCon.cmd.CommandText = "SPJournalVoucher";
ClsCon.cmd.Parameters.AddWithValue("@DataInd", objJVModel.Ind);
ClsCon.cmd.Parameters.AddWithValue("@OrgID", objJVModel.OrgID);
ClsCon.cmd.Parameters.AddWithValue("@BrID", objJVModel.BrID);
ClsCon.cmd.Parameters.AddWithValue("@YrCD", objJVModel.YrCD);
ClsCon.cmd.Parameters.AddWithValue("@VchType", objJVModel.VchType);
ClsCon.cmd.Parameters.AddWithValue("@CCCode", objJVModel.CCCode);
ClsCon.cmd.Parameters.AddWithValue("@IDARefNo", objJVModel.IDARefNo);
ClsCon.cmd.Parameters.AddWithValue("@TblJV", JsonConvert.DeserializeObject <DataTable>(objJVModel.DtJV));
//ClsCon.cmd.Parameters.AddWithValue("@TblJV", objJVModel.DtJV);
con = ClsCon.SqlConn();
ClsCon.cmd.Connection = con;
dtJV = new DataTable();
ClsCon.da = new SqlDataAdapter(ClsCon.cmd);
ClsCon.da.Fill(dtJV);
dtJV.TableName = "success";
}
catch (Exception)
{
dtJV = new DataTable();
dtJV.TableName = "error";
return(dtJV);
}
finally
{
con.Close();
con.Dispose();
ClsCon.da.Dispose();
ClsCon.cmd.Dispose();
}
return(dtJV);
}
public bool SaveJournalVoucher(JournalVoucherModel objjvmodel)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNumber", objjvmodel.Voucher_Number));
paramCollection.Add(new DBParameter("@Series", objjvmodel.Voucher_Series));
paramCollection.Add(new DBParameter("@JVDate", objjvmodel.JV_Date, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Type", objjvmodel.Type));
paramCollection.Add(new DBParameter("@PDCDate", objjvmodel.PDCDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@LongNarration", objjvmodel.LongNarration));
paramCollection.Add(new DBParameter("@TotalCreditAmount", objjvmodel.TotalCreditAmt, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalDebitAmount", objjvmodel.TotalDebitAmt, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertJournalMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SaveJVAccounts(objjvmodel.JournalAccountModel, id);
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
public int SaveJournalVoucher(JournalVoucherModel objjcbl)
{
string Query = string.Empty;
int jvid = 0;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Series", objjcbl.Voucher_Series));
paramCollection.Add(new DBParameter("@Date", objjcbl.JV_Date));
paramCollection.Add(new DBParameter("@Voucher_Number", objjcbl.Voucher_Number));
paramCollection.Add(new DBParameter("@Type", objjcbl.Type));
paramCollection.Add(new DBParameter("@PDDate", objjcbl.PDCDate));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now));
Query = "INSERT INTO Journal_Voucher([Series],[JV_Date],[VoucherNo],[Type],[PDC_Date]," +
"[CreatedBy],[CreatedDate]) VALUES " +
"(@Series,@Date,@Voucher_Number,@Type,@PDDate, " +
" @CreatedBy,@CreatedDate)";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
SaveJVAccounts(objjcbl.JournalAccountModel);
jvid = GetJournalId();
}
}
catch (Exception ex)
{
jvid = 0;
throw ex;
}
return(jvid);
}
public DataTable AccountHead(JournalVoucherModel objJVModel)
{
DataTable dtAccHeadList = objJVDA.AccountHead(objJVModel);
return(dtAccHeadList);
}
private void CreateNewJV()
{
Entity = new JournalVoucherModel();
Entity.Period = DateTime.Today;
}
public DataTable CheckBudgetHead(JournalVoucherModel objJVModel)
{
DataTable dtSaveJV = objJVDA.CheckBudgetHead(objJVModel);
return(dtSaveJV);
}
public DataTable SaveJV(JournalVoucherModel objJVModel)
{
DataTable dtSaveJV = objJVDA.SaveJV(objJVModel);
return(dtSaveJV);
}
public DataTable LoadControlAccount(JournalVoucherModel objJVModel)
{
DataTable dtControlAccount = objJVDA.LoadControlAccount(objJVModel);
return(dtControlAccount);
}
public DataSet BindAllJVDDL(JournalVoucherModel objJVModel)
{
DataSet dsBindAllJVDDL = objJVDA.BindAllJVDDL(objJVModel);
return(dsBindAllJVDDL);
}
public DataTable LoadNarration(JournalVoucherModel objJVModel)
{
DataTable dtNarrationList = objJVDA.LoadNarration(objJVModel);
return(dtNarrationList);
}
public DataTable LastVoucherNo(JournalVoucherModel objJVModel)
{
DataTable dtLastVoucherNo = objJVDA.LastVoucherNo(objJVModel);
return(dtLastVoucherNo);
}
//Update Journal Voucher
public bool UpdateJournalVoucher(JournalVoucherModel objJV)
{
string Query = string.Empty;
bool isUpdated = true;
try
{
//UPDATE CREDIT NOTE TABLE - PARENT TABLE
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Series", objJV.Voucher_Series));
paramCollection.Add(new DBParameter("@Date", objJV.JV_Date));
paramCollection.Add(new DBParameter("@Voucher_Number", objJV.Voucher_Number));
paramCollection.Add(new DBParameter("@Type", objJV.Type));
paramCollection.Add(new DBParameter("@PDDate", objJV.PDCDate));
//paramCollection.Add(new DBParameter("@TotalCreditAmt", "0"));
//paramCollection.Add(new DBParameter("@TotalDebitAmt", "0"));
paramCollection.Add(new DBParameter("@ModifiedBy", "Admin"));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now));
paramCollection.Add(new DBParameter("@id", objJV.JV_Id));
Query = "UPDATE Journal_Voucher SET [Series]=@Series,[JV_Date]=@Date,[VoucherNo]=@Voucher_Number," +
"[Type]=@Type,[PDC_Date]=@PDDate,[ModifiedBy]=@ModifiedBy," +
"[ModifiedDate]=@ModifiedDate " +
"WHERE JV_Id=@id";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
List <AccountModel> lstAcct = new List <AccountModel>();
//UPDATE CREDIT NOTE ACCOUNT -CHILD TABLE UPDATES
foreach (AccountModel act in objJV.JournalAccountModel)
{
if (act.AC_Id > 0)
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@DC", (act.DC)));
paramCollection.Add(new DBParameter("@Account", act.Account));
paramCollection.Add(new DBParameter("@Debit", act.Debit));
paramCollection.Add(new DBParameter("@Credit", act.Credit));
paramCollection.Add(new DBParameter("@Narration", act.Narration));
paramCollection.Add(new DBParameter("@ModifiedBy", "Admin"));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now));
paramCollection.Add(new DBParameter("@ACT_ID", act.AC_Id));
Query = "UPDATE Journal_Voucher_Accounts SET [DC]=@DC," +
"[Account]=@Account,[Debit]=@Debit,[Credit]=@Credit,[Narration]=@Narration,[ModifiedBy]=@ModifiedBy,[ModifiedDate]=@ModifiedDate " +
"WHERE [AC_Id]=@ACT_ID";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
isUpdated = true;
}
}
else
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@DN_ID", (act.ParentId)));
paramCollection.Add(new DBParameter("@DC", (act.DC)));
paramCollection.Add(new DBParameter("@Account", act.Account));
paramCollection.Add(new DBParameter("@Debit", act.Debit));
paramCollection.Add(new DBParameter("@Credit", act.Credit));
paramCollection.Add(new DBParameter("@Narration", act.Narration));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now));
Query = "INSERT INTO Journal_Voucher_Accounts([JV_Id],[DC],[Account],[Debit]," +
"[Credit],[Narration],[CreatedBy],[CreatedDate]) VALUES " +
"(@JV_ID,@DC,@Account,@Debit,@Credit,@Narration,@CreatedBy,@CreatedDate)";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
isUpdated = true;
}
}
}
}
}
catch (Exception ex)
{
isUpdated = false;
throw ex;
}
return(isUpdated);
}
//Update Journal Voucher
public bool UpdateJournalVoucher(JournalVoucherModel objjvmodel)
{
string Query = string.Empty;
bool isUpdated = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNumber", objjvmodel.Voucher_Number));
paramCollection.Add(new DBParameter("@Series", objjvmodel.Voucher_Series));
paramCollection.Add(new DBParameter("@JVDate", objjvmodel.JV_Date, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Type", objjvmodel.Type));
paramCollection.Add(new DBParameter("@PDCDate", objjvmodel.PDCDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@LongNarration", objjvmodel.LongNarration));
paramCollection.Add(new DBParameter("@TotalCreditAmount", objjvmodel.TotalCreditAmt, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalDebitAmount", objjvmodel.TotalDebitAmt, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@CreatedBy", ""));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", "Admin"));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@JournalId", objjvmodel.JV_Id));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spUpdateJournalMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
List <AccountModel> lstAcct = new List <AccountModel>();
foreach (AccountModel act in objjvmodel.JournalAccountModel)
{
act.ParentId = objjvmodel.JV_Id;
if (act.AC_Id > 0)
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@ParentId", (act.ParentId)));
paramCollection.Add(new DBParameter("@ACT_ID", (act.AC_Id)));
paramCollection.Add(new DBParameter("@DC", (act.DC)));
paramCollection.Add(new DBParameter("@Account", act.Account));
paramCollection.Add(new DBParameter("@LegderId", act.LedgerId));
paramCollection.Add(new DBParameter("@DebitAmount", act.Debit, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@CreditAmount", act.Credit, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@Narration", act.Narration));
paramCollection.Add(new DBParameter("@CreatedBy", ""));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", "Admin"));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
Query = "UPDATE journal_voucher_details SET `DC`=@DC,`Account`=@Account,`LegderId`=@LegderId,`Debit`=@DebitAmount,`Credit`=@CreditAmount," +
"`Narration`=@Narration,`CreatedBy`=@CreatedBy,`CreatedDate`=@CreatedDate,`ModifiedBy`=@ModifiedBy,`ModifiedDate`=@ModifiedDate " +
"WHERE `AC_Id`=@ACT_ID AND `JV_Id`=@ParentId";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
isUpdated = true;
}
}
else
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@JournalId", (act.ParentId)));
paramCollection.Add(new DBParameter("@DC", (act.DC)));
paramCollection.Add(new DBParameter("@Account", act.Account));
paramCollection.Add(new DBParameter("@LegderId", act.LedgerId));
paramCollection.Add(new DBParameter("@DebitAmount", act.Debit, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@CreditAmount", act.Credit, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@Narration", act.Narration));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
System.Data.IDataReader JAdr =
_dbHelper.ExecuteDataReader("spInsertJournalDetails", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
}
}
catch (Exception ex)
{
isUpdated = false;
//throw ex;
}
return(isUpdated);
}
