public DataTable MBR1000ReturnAdjustment(String tablename, String riid, String scheduleid, string runid)
{
var plainQuery = ReturnAdjustmentQB.GetQuery(tablename, ReturnAdjustmentQB.QueryType.SELECT);
//
//Check if runid is null or empty or white space
//
bool runidStatus = String.IsNullOrWhiteSpace(runid);
this.CommandText = ((runid == "0" || runid.Equals("0")) || runidStatus == true) ? String.Concat(plainQuery, String.Format(" WHERE (ri_id = {0} AND schedule_id = {1}) AND run_id IS NULL", riid, scheduleid)) : String.Concat(plainQuery, String.Format(" WHERE (ri_id = {0} AND schedule_id = {1}) AND run_id = {2}", riid, scheduleid, runid));
using (SqlConnection con = new SqlConnection(ConnectionString.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(this.CommandText, con))
{
cmd.CommandType = CommandType.Text;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
con.Close();
return(dataTable);
}
}
}
public int SaveReturnAdjustment(GridView grid300, GridView grid1000, string tablename300, string tablename1000, string scheduleid, string runid, string analystcomment)
{
int rows = 0;
using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required))
{
using (SqlConnection con = new SqlConnection(ConnectionString.GetConnectionString()))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
String commandText1 = "INSERT INTO t_rpt_computation_rule_adjustment (schedule_id, run_id, analyst_comment) VALUES (@schedule_id, @run_id, @analyst_comment)";
using (SqlCommand cmd = new SqlCommand(commandText1, con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@schedule_id", scheduleid);
cmd.Parameters.AddWithValue("@run_id", runid);
cmd.Parameters.AddWithValue("@analyst_comment", analystcomment);
rows = cmd.ExecuteNonQuery();
}
String commandText2 = "";
foreach (GridViewRow grdview in grid300.Rows)
{
//Generate the SQL query use to insert data
commandText2 = ReturnAdjustmentQB.GetQuery(tablename300, ReturnAdjustmentQB.QueryType.INSERT);
using (SqlCommand cmd2 = new SqlCommand(commandText2, con))
{
cmd2.CommandType = CommandType.Text;
//cmd2.Parameters.AddWithValue("@id", grdview.Cells[1].Text);
cmd2.Parameters.AddWithValue("@schedule_id", grdview.Cells[2].Text);
cmd2.Parameters.Add("@work_collection_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[3].Text);
cmd2.Parameters.AddWithValue("@ri_id", grdview.Cells[4].Text);
cmd2.Parameters.AddWithValue("@ri_code", grdview.Cells[5].Text);
cmd2.Parameters.AddWithValue("@ri_name", grdview.Cells[6].Text);
cmd2.Parameters.AddWithValue("@item_code", grdview.Cells[7].Text);
if (tablename300.Equals("dh.t_rtn_submission_mdhr300"))
{
cmd2.Parameters.AddWithValue("@item_description", grdview.Cells[8].Text);
//cmd2.Parameters.AddWithValue("@amount_1", grdview.Cells[9].Text.Replace("₦", "").Replace(",",""));
//cmd2.Parameters.AddWithValue("@amount_2", grdview.Cells[10].Text.Replace("₦", "").Replace(",", ""));
cmd2.Parameters.Add("@amount_1", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", "");
cmd2.Parameters.Add("@amount_2", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", "");
cmd2.Parameters.AddWithValue("@submission_by", grdview.Cells[11].Text);
cmd2.Parameters.Add("@submission_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[12].Text);
cmd2.Parameters.AddWithValue("@adj_reason", grdview.Cells[13].Text);
cmd2.Parameters.AddWithValue("@run_id", runid); //14
}
else
{
cmd2.Parameters.AddWithValue("@item_desc", grdview.Cells[8].Text);
cmd2.Parameters.Add("@amt_lcy", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", "");
cmd2.Parameters.AddWithValue("@submission_by", grdview.Cells[10].Text);
cmd2.Parameters.Add("@submission_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[11].Text);
cmd2.Parameters.AddWithValue("@adj_reason", grdview.Cells[12].Text);
cmd2.Parameters.AddWithValue("@run_id", runid); //13
}
this.CommandText = cmd2.CommandText;
rows += cmd2.ExecuteNonQuery();
}
}
String commandText3 = "";
foreach (GridViewRow grdview in grid1000.Rows)
{
commandText3 = ReturnAdjustmentQB.GetQuery(tablename1000, ReturnAdjustmentQB.QueryType.INSERT);
using (SqlCommand cmd3 = new SqlCommand(commandText3, con))
{
cmd3.CommandType = CommandType.Text;
//cmd3.Parameters.AddWithValue("@id", grdview.Cells[1].Text);
cmd3.Parameters.AddWithValue("@schedule_id", grdview.Cells[2].Text);
cmd3.Parameters.Add("@work_collection_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[3].Text);
cmd3.Parameters.AddWithValue("@ri_id", grdview.Cells[4].Text);
cmd3.Parameters.AddWithValue("@ri_code", grdview.Cells[5].Text);
cmd3.Parameters.AddWithValue("@ri_name", grdview.Cells[6].Text);
cmd3.Parameters.AddWithValue("@item_code", grdview.Cells[7].Text);
if (tablename1000.Equals("cb.t_rtn_submission_mbr1000"))
{
cmd3.Parameters.AddWithValue("@item", grdview.Cells[8].Text);
//cmd3.Parameters.AddWithValue("@latest_moth_lcy", grdview.Cells[9].Text.Replace("₦", "").Replace(",", ""));
//cmd3.Parameters.AddWithValue("@year_to_dt_lcy", grdview.Cells[10].Text.Replace("₦", "").Replace(",", ""));
cmd3.Parameters.Add("@latest_moth_lcy", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", "");
cmd3.Parameters.Add("@year_to_dt_lcy", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", "");
}
else if (tablename1000.Equals("dh.t_rtn_submission_mdhr1000"))
{
cmd3.Parameters.AddWithValue("@item_desc", grdview.Cells[8].Text);
cmd3.Parameters.Add("@latest_month", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", "");
cmd3.Parameters.Add("@year_to_date_ngn", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", "");
}
else if (tablename1000.Equals("nib.t_rtn_submission_mnbr1000"))
{
cmd3.Parameters.AddWithValue("@item", grdview.Cells[8].Text);
cmd3.Parameters.Add("@latest_month", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", "");
cmd3.Parameters.Add("@year_to_date_ngn", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", "");
}
cmd3.Parameters.AddWithValue("@submission_by", grdview.Cells[11].Text);
cmd3.Parameters.Add("@submission_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[12].Text);
cmd3.Parameters.AddWithValue("@adj_reason", grdview.Cells[13].Text);
cmd3.Parameters.AddWithValue("@run_id", runid); //14
rows += cmd3.ExecuteNonQuery();
}
}
sc.Complete();
con.Close();
return(rows);
}
}
}
