UPDATE:
I'm trying to fetch lists for UnionCouncil and BEMISCode(Schoolid) from EMIS_DB(DataBase1) and PBHId(BudgetId) list from DSS_DB(DataBase2) and show sum of AlocatedBudget for each UnionCouncil if it has any.
So far i am getting the values of listobjects.counts() but the sum of AlocatedBudget is showing correctly only for first UnionCouncil and then it adds the sum of AlocatedBudget into others.
for example: AllocatedBudget for UnionCuncil abc is 208000 AllocatedBudget for UnionCouncil xyz will be double of previous AllocatedBudget like 416000.
I have tried almost everything but couldn't find what is missing, any kind of help is much much appreciated.
Client side
<div class="panel-body">
<div id="tblschoolinfo" style="background-color: #F8F8F8; color: rgb(1, 51, 1);">
Function
function LoadBudgetSumCouncilvise()
{
var table = $("#tblschoolinfo");
$.ajax({
type: "POST",
url: "wbservices/GetBudgetEstimatedSumByUnionCouncil.asmx/getunioncouncil",
//data: jsnDta,
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var rtnData = r.d; //all returned data...
var respDta = [];
$.map(rtnData, function (item, index) {
var j = [
item.status,
item.msg,
];
respDta.push(j);
});
$.each(respDta, function (key, value) {
var status = value[0];
var msg = value[1];
alert(msg);
if (status == true) {
table.html(msg);
} else {
var eMsg = '<div style="color:white;background:#FF4040" ><i style="margin-left:5px" class="fa fa-exclamation-triangle"></i>"' + msg + '"</div>';
table.html(eMsg);
}
}); //1st out loop ends here...
},
error: function (jqXHR, textStatus, errorThrown) {
// $("#responseMovDetails").html(jqXHR + textStatus + errorThrown);
alert("error while loading Records of Requests" + jqXHR + textStatus + errorThrown);
});
}
ASMX
public List<RequestResponse> getunioncouncil()
{
List<RequestResponse> list = new List<RequestResponse>();
RequestResponse r = new RequestResponse();
string District = Session["districtName"].ToString();
Pro_DbCon obj2 = new Pro_DbCon();
//EMIS conncection
SqlConnection conemis = obj2.dbconnectionEMIS();
string rows = "";
List<string> uc = new List<string>();
List<string> bemiscode = new List<string>();
List<string>pbhid=new List<string>();
List<string> pbdid = new List<string>();
try
{
conemis.Open();
//get unoincouncil
string getuc = "select distinct(UnionCouncil) from Locations where District = @dis";
SqlCommand cmduc = new SqlCommand(getuc,conemis);
cmduc.Parameters.AddWithValue("@dis",District);
SqlDataReader ucdr = cmduc.ExecuteReader();
while(ucdr.Read())
{
uc.Add(ucdr[0].ToString());
}
ucdr.Close();
for (int i = 0; i < uc.Count();i++ )
{
//get bemiscode by unioncouncil
string getbemiscode = "select VchBEMISCode from SchoolsParameter where VchUC = @uc";
SqlCommand getcode = new SqlCommand(getbemiscode,conemis);
getcode.Parameters.AddWithValue("@uc",uc[i]);
SqlDataReader bemiscodedr = getcode.ExecuteReader();
while(bemiscodedr.Read())
{
bemiscode.Add(bemiscodedr[0].ToString());
}
bemiscodedr.Close();
}
}
catch(Exception ex)
{
r.status = false;
r.msg = "Could not get data from EmisDb!" + ex.ToString();
list.Add(r);
}
finally
{
conemis.Close();
}
//Dss connection
string constr = obj2.dbconnection();
SqlConnection con = new SqlConnection(constr);
try
{
con.Open();
for (int a = 0; a < uc.Count(); a++)
{
//----------------------------------
Decimal EstimatedAmountTaat = 0;
Decimal EstimatedAmountScience = 0;
Decimal EstimatedAmountReadWriting = 0;
Decimal EstimatedAmountFurniture = 0;
Decimal UtilizedAmountTaat = 0;
Decimal UtilizedAmountScience = 0;
Decimal UtilizedAmountReadingWriting = 0;
Decimal UtilizedAmountFurniture = 0;
//----------------------------------
for (int i = 0; i < bemiscode.Count(); i++)
{
//check bemiscode exists in budgetview
//get budgethead by bemiscode
string getbudgethead = "select * from Pro_BudgetView where BEMSCode = @scode";
SqlCommand bheadcmd = new SqlCommand(getbudgethead, con);
bheadcmd.Parameters.AddWithValue("@scode", bemiscode[i]);
SqlDataReader dr = bheadcmd.ExecuteReader();
while (dr.Read())
{
if (dr[1].ToString() == bemiscode[i].ToString())
{
pbhid.Add(dr[0].ToString());
}
}
dr.Close();
}
//-------------------------------------------
for (int j = 0; j < pbhid.Count(); j++)
{
//get budgetdetail by budgethead
string getbudgetdetail = "select * from Pro_BudgetView where PBHId = @pbhid";
SqlCommand getbudgetdetailcmd = new SqlCommand(getbudgetdetail, con);
getbudgetdetailcmd.Parameters.AddWithValue("@pbhid", pbhid[j]);
SqlDataReader bdreader = getbudgetdetailcmd.ExecuteReader();
while (bdreader.Read())
{
pbdid.Add(bdreader[5].ToString());
if(bdreader[6].ToString()=="1")
{
EstimatedAmountTaat = EstimatedAmountTaat + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountTaat = UtilizedAmountTaat + Convert.ToDecimal(bdreader[8].ToString());
}
if (bdreader[6].ToString() == "2")
{
EstimatedAmountScience = EstimatedAmountScience + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountScience = UtilizedAmountScience + Convert.ToDecimal(bdreader[8].ToString());
}
if (bdreader[6].ToString() == "3")
{
EstimatedAmountReadWriting = EstimatedAmountReadWriting + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountReadingWriting = UtilizedAmountReadingWriting + Convert.ToDecimal(bdreader[8].ToString());
}
if (bdreader[6].ToString() == "4")
{
EstimatedAmountFurniture = EstimatedAmountFurniture + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountFurniture = UtilizedAmountFurniture + Convert.ToDecimal(bdreader[8].ToString());
}
}
bdreader.Close();
}
string row = "<tr><td colspan='4' style='text-align:center;'>" + uc[a].ToString() + "</td></tr> <tr><td><b>AlocatedBudget(sum)</b></td><td>" + EstimatedAmountTaat + "</td><td>" + EstimatedAmountScience + "</td><td>" + EstimatedAmountReadWriting + "</td><td>" + EstimatedAmountFurniture + "</td></tr> <tr><td><b>UtiliazedAmount(sum)</b></td><td>" + UtilizedAmountTaat + "</td><td>" + UtilizedAmountScience + "</td><td>" + UtilizedAmountReadingWriting + "</td><td>" + UtilizedAmountFurniture + "</td></tr>";
rows = rows + row;
}
con.Close();
if(rows!="")
{
r.status = true;
r.msg = rows + "";
list.Add(r);
}
}
catch (Exception ex)
{
r.status = false;
r.msg = "Error" + ex.ToString();
list.Add(r);
}
finally
{
con.Close();
}
return list;
}
Although its logical problem but this kind of mistake we often make and it can save much much time for others.
Here is what I did.
public class GetBudgetEstimatedSumByUnionCouncil : System.Web.Services.WebService
{
[WebMethod (EnableSession=true)]
public List<RequestResponse> getunioncouncil()
{
List<RequestResponse> list = new List<RequestResponse>();
RequestResponse r = new RequestResponse();
string District = Session["districtName"].ToString();
Pro_DbCon obj2 = new Pro_DbCon();
//EMIS conncection
SqlConnection conemis = obj2.dbconnectionEMIS();
string rows = "";
//Dss connection
string constr = obj2.dbconnection();
SqlConnection con = new SqlConnection(constr);
List<string> uc = new List<string>();
//List<string>pbhid=new List<string>();
try
{
conemis.Open();
//get unoincouncil
string getuc = "select distinct(UnionCouncil) from Locations where District = @dis";
SqlCommand cmduc = new SqlCommand(getuc,conemis);
cmduc.Parameters.AddWithValue("@dis",District);
SqlDataReader ucdr = cmduc.ExecuteReader();
while(ucdr.Read())
{
uc.Add(ucdr[0].ToString());
}
ucdr.Close();
conemis.Close();
for (int i = 0; i < uc.Count();i++ )
{
//----------------------------------
Decimal EstimatedAmountTaat = 0;
Decimal EstimatedAmountScience = 0;
Decimal EstimatedAmountReadWriting = 0;
Decimal EstimatedAmountFurniture = 0;
Decimal UtilizedAmountTaat = 0;
Decimal UtilizedAmountScience = 0;
Decimal UtilizedAmountReadingWriting = 0;
Decimal UtilizedAmountFurniture = 0;
//----------------------------------
List<string> bemiscode = new List<string>();
List<string> pbdid = new List<string>();
//get bemiscode by unioncouncil
conemis.Open();
string getbemiscode = "select VchBEMISCode from SchoolsParameter where VchUC = @uc";
SqlCommand getcode = new SqlCommand(getbemiscode,conemis);
getcode.Parameters.AddWithValue("@uc",uc[i]);
SqlDataReader bemiscodedr = getcode.ExecuteReader();
while(bemiscodedr.Read())
{
bemiscode.Add(bemiscodedr[0].ToString());
}
bemiscodedr.Close();
conemis.Close();
//----------------------------------
con.Open();
for (int b = 0; b < bemiscode.Count(); b++)
{
//get budgethead by bemiscode
string getbudgethead = "select * from Pro_BudgetView where BEMSCode = @scode";
SqlCommand bheadcmd = new SqlCommand(getbudgethead, con);
bheadcmd.Parameters.AddWithValue("@scode", bemiscode[b]);
SqlDataReader dr = bheadcmd.ExecuteReader();
while (dr.Read())
{
pbdid.Add(dr[5].ToString());
}
dr.Close();
}
//----------------------------
for (int j = 0; j < pbdid.Count(); j++)
{
//get budgetdetail by budgethead
string getbudgetdetail = "select PBHId,EstimatedAmount,UtilizedAmount,CId,PBDId from Pro_BudgetView where PBDId = @pbdid and BudgetStatus = @bs";
SqlCommand getbudgetdetailcmd = new SqlCommand(getbudgetdetail, con);
getbudgetdetailcmd.Parameters.AddWithValue("@pbdid", pbdid[j]);
getbudgetdetailcmd.Parameters.AddWithValue("@bs", "2");
SqlDataReader bdr = getbudgetdetailcmd.ExecuteReader();
while (bdr.Read())
{
//pbdid.Add(bdr[4].ToString());
if (bdr[3].ToString() == "1")
{
EstimatedAmountTaat = EstimatedAmountTaat + Convert.ToDecimal(bdr[1].ToString());
UtilizedAmountTaat = UtilizedAmountTaat + Convert.ToDecimal(bdr[2].ToString());
}
if (bdr[3].ToString() == "2")
{
EstimatedAmountScience = EstimatedAmountScience + Convert.ToDecimal(bdr[1].ToString());
UtilizedAmountScience = UtilizedAmountScience + Convert.ToDecimal(bdr[2].ToString());
}
if (bdr[3].ToString() == "3")
{
EstimatedAmountReadWriting = EstimatedAmountReadWriting + Convert.ToDecimal(bdr[1].ToString());
UtilizedAmountReadingWriting = UtilizedAmountReadingWriting + Convert.ToDecimal(bdr[2].ToString());
}
if (bdr[3].ToString() == "4")
{
EstimatedAmountFurniture = EstimatedAmountFurniture + Convert.ToDecimal(bdr[1].ToString());
UtilizedAmountFurniture = UtilizedAmountFurniture + Convert.ToDecimal(bdr[2].ToString());
}
}
bdr.Close();
}
string row = "<tr><td style='text-align:center;font-size: large;'><b>UnionCouncil</b></td><td colspan='5' style='text-align:center;font-size: x-large;'>" + uc[i].ToString() + "</td></tr> <tr><td style='text-align:center;font-size: large;'><b>AlocatedBudget(sum)</b></td><td style='text-align:center;'>" + EstimatedAmountTaat + "</td><td style='text-align:center;'>" + EstimatedAmountScience + "</td><td style='text-align:center;'>" + EstimatedAmountReadWriting + "</td><td style='text-align:center;'>" + EstimatedAmountFurniture + "</td></tr> <tr><td style='text-align:center;font-size: large;'><b>UtiliazedAmount(sum)</b></td><td style='text-align:center;'>" + UtilizedAmountTaat + "</td><td style='text-align:center;'>" + UtilizedAmountScience + "</td><td style='text-align:center;'>" + UtilizedAmountReadingWriting + "</td><td style='text-align:center;'>" + UtilizedAmountFurniture + "</td></tr>";
rows = rows + row;
con.Close();
if(rows!="")
{
r.status = true;
r.msg = rows + "";
list.Add(r);
}
}
}
catch(Exception ex)
{
r.status = false;
r.msg = "Could not get data from EmisDb!" + ex.ToString();
list.Add(r);
}
finally
{
conemis.Close();
con.Close();
}
return list;
}