public int GetSubGroupID(LedgerModels ledger)
{
SqlDataReader SqlDtr = null;
int subgrpid = 0;
string message = "";
dbobj.SelectQuery("select sub_grp_Id from Ledger_Master_Sub_grp where sub_grp_name = '" + ledger.SubGroupName + "' and Nature_Of_group ='" + ledger.GroupNature + "' and grp_id = (select top 1 grp_id from mgroup where grp_name = '" + ledger.GroupName + "')", ref SqlDtr);
if (SqlDtr.Read())
{
subgrpid = System.Convert.ToInt32(SqlDtr["sub_grp_id"].ToString());
}
SqlDtr.Close();
int count = 0;
// check the Ledger Name is already present for the selected sub group
dbobj.ExecuteScalar("Select count(Ledger_ID) from Ledger_Master where Ledger_Name = '" + ledger.LedgerName + "' and Sub_grp_ID = " + subgrpid, ref count);
if (count > 0)
{
message = "Ledger Name is already exist for selected Sub Group ";
}
else
{
object op = null;
dbobj.ExecProc(App_Start.DbOperations_LATEST.OprType.Insert, "ProInsertLedger", ref op, "@Ledger_Name", ledger.LedgerName, "@SubGrp_Name", ledger.SubGroupName, "@Group_Name", ledger.GroupName, "@Grp_Nature", ledger.GroupNature, "@Op_Bal", ledger.OpeningBalance, "@Bal_Type", ledger.BalanceType);
}
return(count);
}
public string UpdateLedger(LedgerModels ledger)
{
SqlDataReader SqlDtr = null;
int subgrpid = 0;
string message = ""; string id = null;
dbobj.SelectQuery("select sub_grp_Id from Ledger_Master_Sub_grp where sub_grp_name = '" + ledger.SubGroupName + "' and Nature_Of_group ='" + ledger.GroupNature + "' and grp_id = (select top 1 grp_id from mgroup where grp_name = '" + ledger.GroupName + "')", ref SqlDtr);
if (SqlDtr.Read())
{
subgrpid = System.Convert.ToInt32(SqlDtr["sub_grp_id"].ToString());
}
SqlDtr.Close();
int count = 0;
dbobj.ExecuteScalar("Select count(Ledger_ID) from Ledger_Master where Ledger_Name = '" + ledger.LedgerName + "' and Sub_grp_ID = " + subgrpid, ref count);
if (count > 0)
{
dbobj.SelectQuery("Select Ledger_ID from Ledger_Master where Ledger_Name = '" + ledger.LedgerName + "' and Sub_grp_ID = " + subgrpid, ref SqlDtr);
if (SqlDtr.Read())
{
id = SqlDtr["Ledger_ID"].ToString();
}
SqlDtr.Close();
//if (!id.Equals(strArr[1]))
//{
// MessageBox.Show("Ledger Name is already exist for selected Sub Group ");
// fetchGroup();
// DropGroup.SelectedIndex = DropGroup.Items.IndexOf(DropGroup.Items.FindByText(Group));
// return;
//}
}
string subgrpid1 = "";
dbobj.SelectQuery("select sub_grp_Id from Ledger_Master where Ledger_id = " + ledger.LedgerID, ref SqlDtr);
while (SqlDtr.Read())
{
subgrpid1 = SqlDtr["sub_grp_ID"].ToString();
}
SqlDtr.Close();
object op = null;
// Call Procedure to update the Ledger
dbobj.ExecProc(App_Start.DbOperations_LATEST.OprType.Update, "ProUpdateLedger", ref op, "@Ledger_ID", ledger.LedgerID, "@Ledger_Name", ledger.LedgerName, "@SubGrp_Name", ledger.SubGroupName, "@Group_Name", ledger.GroupName, "@Grp_Nature", ledger.GroupNature, "@Op_Bal", ledger.OpeningBalance, "@Bal_Type", ledger.BalanceType);
dbobj.ExecProc(App_Start.DbOperations_LATEST.OprType.Update, "UpdateAccountsLedger", ref op, "@Ledger_ID", ledger.LedgerID, "@Amount", ledger.OpeningBalance, "@Type", ledger.BalanceType);
// Procedure to update or delete the Unused newly created Group and Sub Groups.
dbobj.ExecProc(App_Start.DbOperations_LATEST.OprType.Update, "ProUpdatesubgroup", ref op, "@subgrp_id", subgrpid1);
return(id);
}
public LedgerModels FetchSelectedLedger(string ledgerName)
{
LedgerModels ledger = new LedgerModels();
SqlDataReader SqlDtr = null;
string[] strArr = ledgerName.Split(new char[] { ':' }, ledgerName.Length);
dbobj.SelectQuery("select ln.*,m.grp_name,lmsg.Sub_grp_Name,lmsg.Nature_of_group from Ledger_Master ln,mGroup m,Ledger_Master_Sub_Grp lmsg where Ledger_id = " + strArr[1].Trim() + " and Ledger_Name = '" + strArr[0].Trim() + "' and ln.sub_grp_id = lmsg.sub_grp_id and lmsg.grp_id = m.grp_id", ref SqlDtr);
if (SqlDtr.Read())
{
ledger.LedgerName = SqlDtr["Ledger_name"].ToString();
ledger.SubGroupName = SqlDtr["Sub_grp_Name"].ToString();
ledger.GroupName = SqlDtr["grp_Name"].ToString();
ledger.GroupNature = SqlDtr["Nature_of_group"].ToString();
ledger.OpeningBalance = SqlDtr["Op_Balance"].ToString();
ledger.BalanceType = SqlDtr["Bal_Type"].ToString();
}
SqlDtr.Read();
return(ledger);
}
