protected void btnShowCoa_Click(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(3000);
string sepTyp = Session["septyp"].ToString();
int[] lvlSize = new int[dgGlCoaGen.Rows.Count];
string[] lvlcode = new string[dgGlCoaGen.Rows.Count];
string connectionString = DataManager.OraConnString();
OracleDataReader dReader;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand cmd = new OracleCommand();
string criteria = "";
int x = 0;
int y = 0;
foreach (GridViewRow gvr in dgGlCoaGen.Rows)
{
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select lvl_code,lvl_max_size from gl_level_type where lvl_desc='" + gvr.Cells[0].Text.ToString().Trim() + "'";
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
dReader = cmd.ExecuteReader();
if (dReader.HasRows == true)
{
while (dReader.Read())
{
lvlSize[x] = int.Parse(dReader["lvl_max_size"].ToString());
lvlcode[x] = dReader["lvl_code"].ToString();
}
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (x == 0 & gvr.Cells[1].Text.ToString().Replace(" ", "").ToString().Trim() != String.Empty)
{
criteria += "substr(gl_coa_code,instr(gl_coa_code,'" + sepTyp + "',0)+1," + lvlSize[x].ToString() + ") in (SELECT SEG_COA_CODE FROM GL_SEG_COA S where rootleaf='L' and lvl_code='" + lvlcode[x] + "' CONNECT BY PRIOR SEG_COA_CODE=PARENT_CODE START WITH seg_coa_code = " +
" '" + gvr.Cells[1].Text.ToString().Trim() + "') ";
}
else if (x > 0 & gvr.Cells[1].Text.ToString().Replace(" ", "").ToString().Trim() != String.Empty)
{
criteria += "and substr(gl_coa_code,instr(gl_coa_code,'" + sepTyp + "'," + y + ")+1," + lvlSize[x].ToString() + ") in (SELECT SEG_COA_CODE FROM GL_SEG_COA S where rootleaf='L' and lvl_code='" + lvlcode[x] + "' CONNECT BY PRIOR SEG_COA_CODE=PARENT_CODE START WITH seg_coa_code = " +
" '" + gvr.Cells[1].Text.ToString().Trim() + "' ) ";
}
y = y + lvlSize[x];
x = x + 1;
}
DataTable dt = GlCoaManager.GetGlCoaCode(criteria);
dgGlCoa.DataSource = dt;
Session.Remove("coa");
Session["coa"] = dt;
foreach (GridViewRow gvr in dgGlCoa.Rows)
{
((LinkButton)gvr.FindControl("lbEdit")).Visible = true;
((LinkButton)gvr.FindControl("lbDelete")).Visible = true;
((CheckBox)gvr.FindControl("chkInc")).Visible = false;
}
if (dt.Rows.Count > 0)
{
btnSaveCoa.Visible = false;
dgGlCoa.DataBind();
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "ale", "alert('No Chart-of-Account Code was previously created!!');", true);
}
lblTransStatus.Text = "";
}
protected void btnGenCoa_Click(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(3000);
foreach (GridViewRow gvr in dgGlCoaGen.Rows)
{
if (gvr.Cells[1].Text.ToString().Replace(" ", "").ToString().Trim() == "")
{
//lblTransStatus.Visible = true;
//lblTransStatus.ForeColor = System.Drawing.Color.Red;
//lblTransStatus.Text = "Please input all segment code in Chart-of-Account Generation table";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "ale", "alert('Please input all segment code in Chart-of-Account Generation table!!');", true);
return;
}
}
DataTable[] dtm = new DataTable[dgGlCoaGen.Rows.Count];
DataTable dtCoa = new DataTable();
string sepTyp = Session["septyp"].ToString();
int[] lvlSize = new int[dgGlCoaGen.Rows.Count];
string[] lvlcode = new string[dgGlCoaGen.Rows.Count];
string criteria = "";
string[] criteriaN = new string[dgGlCoaGen.Rows.Count];
int x = 0;
int y = 0;
foreach (GridViewRow gvr in dgGlCoaGen.Rows)
{
string connectionString = DataManager.OraConnString();
OracleDataReader dReader;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand cmd = new OracleCommand();
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select lvl_code,lvl_max_size from gl_level_type where lvl_desc='" + gvr.Cells[0].Text.ToString().Trim() + "'";
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
dReader = cmd.ExecuteReader();
if (dReader.HasRows == true)
{
while (dReader.Read())
{
lvlSize[x] = int.Parse(dReader["lvl_max_size"].ToString());
lvlcode[x] = dReader["lvl_code"].ToString();
}
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (x == 0 & gvr.Cells[1].Text.ToString().Replace(" ", "").ToString().Trim() != String.Empty)
{
criteria += "substr(gl_coa_code,instr(gl_coa_code,'" + sepTyp + "',0)+1," + lvlSize[x].ToString() + ") in (SELECT SEG_COA_CODE FROM GL_SEG_COA S where rootleaf='L' and lvl_code='" + lvlcode[x] + "' CONNECT BY PRIOR SEG_COA_CODE=PARENT_CODE START WITH seg_coa_code = " +
" '" + gvr.Cells[1].Text.ToString().Trim() + "') ";
}
else if (x > 0 & gvr.Cells[1].Text.ToString().Replace(" ", "").ToString().Trim() != String.Empty)
{
criteria += "and substr(gl_coa_code,instr(gl_coa_code,'" + sepTyp + "'," + y + ")+1," + lvlSize[x].ToString() + ") in (SELECT SEG_COA_CODE FROM GL_SEG_COA S where rootleaf='L' and lvl_code='" + lvlcode[x] + "' CONNECT BY PRIOR SEG_COA_CODE=PARENT_CODE START WITH seg_coa_code = " +
" '" + gvr.Cells[1].Text.ToString().Trim() + "' ) ";
}
criteriaN[x] = "seg_coa_code in (SELECT SEG_COA_CODE FROM GL_SEG_COA S where rootleaf='L' and lvl_code='" + lvlcode[x] + "' CONNECT BY PRIOR SEG_COA_CODE=PARENT_CODE START WITH seg_coa_code = '" + gvr.Cells[1].Text.ToString().Trim() + "') ";
dtm[x] = SegCoaManager.GetSegCoas(criteriaN[x]);
y = y + lvlSize[x];
x = x + 1;
}
DataTable dtAlready = GlCoaManager.GetGlCoaCode(criteria);
dtCoa = dtAlready.Clone();
DataTable dt = new DataTable();
dt.Columns.Add("seg_coa_code", typeof(string));
dt.Columns.Add("seg_coa_desc", typeof(string));
dt.Columns.Add("acc_type", typeof(string));
DataRow dr;
for (int i = 0; i < dgGlCoaGen.Rows.Count - 1; i++)
{
dt.Clear();
for (int m = 0; m <= dtm[i].Rows.Count - 1; m++)
{
for (int n = 0; n <= dtm[i + 1].Rows.Count - 1; n++)
{
dr = dt.NewRow();
dr["seg_coa_code"] = ((DataRow)dtm[i].Rows[m])["seg_coa_code"].ToString().Trim() + sepTyp + ((DataRow)dtm[i + 1].Rows[n])["seg_coa_code"].ToString().Trim();
dr["seg_coa_desc"] = ((DataRow)dtm[i].Rows[m])["seg_coa_desc"].ToString().Trim() + ", " + ((DataRow)dtm[i + 1].Rows[n])["seg_coa_desc"].ToString().Trim();
if (((DataRow)dtm[i].Rows[m])["acc_type"].ToString().Trim() != "N")
{
dr["acc_type"] = ((DataRow)dtm[i].Rows[m])["acc_type"].ToString().Trim();
}
else if (((DataRow)dtm[i + 1].Rows[n])["acc_type"].ToString().Trim() != "N")
{
dr["acc_type"] = ((DataRow)dtm[i + 1].Rows[n])["acc_type"].ToString().Trim();
}
dt.Rows.Add(dr);
}
}
dtm[i + 1] = dt.Copy();
}
DataRow drCoa;
foreach (DataRow drC in dtm[dgGlCoaGen.Rows.Count - 1].Rows)
{
drCoa = dtCoa.NewRow();
drCoa["gl_coa_code"] = drC["seg_coa_code"].ToString().Trim();
drCoa["coa_desc"] = drC["seg_coa_desc"].ToString().Trim();
drCoa["acc_type"] = drC["acc_type"].ToString().Trim();
drCoa["status"] = "U";
dtCoa.Rows.Add(drCoa);
}
for (int i = 0; i < dtAlready.Rows.Count; i++)
{
for (int j = 0; j < dtCoa.Rows.Count; j++)
{
if (((DataRow)dtCoa.Rows[j])["gl_coa_code"].ToString().Trim() == ((DataRow)dtAlready.Rows[i])["gl_coa_code"].ToString().Trim())
{
dtCoa.Rows.RemoveAt(j);
}
}
}
char sep = Convert.ToChar(Session["septyp"].ToString());
foreach (DataRow drc in dtCoa.Rows)
{
string mainseg = "";
string[] segcode = drc["gl_coa_code"].ToString().Split(sep);
for (int i = 0; i < segcode.Length; i++)
{
string a = SegCoaManager.getMainSeg(segcode[i].ToString());
if (a == "N")
{
mainseg = segcode[i].ToString();
}
}
drc.BeginEdit();
drc["coa_natural_code"] = mainseg;
drc.AcceptChanges();
}
dgGlCoa.EditIndex = -1;
if (dtCoa.Rows.Count > 0)
{
btnSaveCoa.Visible = true;
//lblTransStatus.ForeColor = System.Drawing.Color.Orange;
//lblTransStatus.Text = "***To Save Gl COA Codes in Database Click on SaveCoa Link.***";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "ale", "alert('To Save Gl COA Codes in Database Click on SaveCoa Link!!');", true);
btnSaveCoa.Visible = true;
}
else
{
btnSaveCoa.Visible = false;
//lblTransStatus.ForeColor = System.Drawing.Color.Orange;
//lblTransStatus.Text = "***Possible Gl COA Codes Are Already in Database.***";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "ale", "alert('Possible Gl COA Codes Are Already in Database!!');", true);
btnSaveCoa.Visible = false;
}
dgGlCoa.DataSource = dtCoa;
Session["coa"] = dtCoa;
dgGlCoa.DataBind();
foreach (GridViewRow gvr in dgGlCoa.Rows)
{
((LinkButton)gvr.FindControl("lbEdit")).Visible = false;
((LinkButton)gvr.FindControl("lbDelete")).Visible = false;
((CheckBox)gvr.FindControl("chkInc")).Visible = true;
}
}