I wanted to pass a value I got from my angular checkboxes .. ( number that has been checked) to query a certain data from my db. Take a look on my connection query ..
private static string m_sConnectionString = ConfigurationManager.ConnectionStrings["NomsConnection"].ConnectionString;
private static string
m_sReport = "SELECT r.[RequestID],r.[RequestDate],r.[PARNumber],r.[StatusID],r.[PurchaseComment]" // 0 - 4
+ ",r.[UID],r.[LearUID],r.[FullName],r.[Email]" // 5 - 8
+ ",r.[EntityName],r.[DepartmentName],r.[DepartmentID]" // 9 - 11
+ ",r.[InboxLearUID]" // 12
+ ",r.[ProgramID],r.[ProgramCode],r.[ProgramName],r.[CostCenterCode]" // 13 - 16
+ ",p.[PartDesc],p.[SupplierID],p.[AccountType],p.[CurrName],p.[PartQuantity],p.[PiecePrice], p.[PartNumber]"
+ "FROM [NOP_PR].[dbo].[Requests] r "
+ "JOIN [NOP_PR].[dbo].[Parts] p on p.[RequestID] = r.[RequestID]"
+ "JOIN [NOP_PR].[dbo].[Departments] d on d.[DepartmentID] = r.[DepartmentID]"
+ "WHERE [CountryName] IN ('Philippines') ";
//ORDER BY r.[RequestDate] DESC";
public static List<NomsPRRequest> LoadPRfromDB_withParams(DateTime from, DateTime to, string EntityID,
string DepartmentID, string [] StatusID)
{
string sScript = m_sReport + ((EntityID == "") ? "" : " AND d.[EntityID]=" + EntityID) + ((DepartmentID == "") ? "" : " AND d.[DepartmentID]=" + DepartmentID)
+ " and [RequestDate] between '" + from.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + to.ToString("yyyy-MM-dd HH:mm:ss") + "'" + " and " + (( __________ ) ? "" : " AND d.[StatusID] in (" + ____________ + ")" );
Dictionary<long, NomsPRRequest> data = new Dictionary<long, NomsPRRequest>();
long key;
double dAmount;
using (SqlConnection con = new SqlConnection(m_sConnectionString))
{
con.Open();
using (SqlCommand command = new SqlCommand(sScript, con))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
key = reader.GetInt64(0);
if (!data.ContainsKey(key))
{
data.Add(key, new NomsPRRequest()
{
RequestID = key,
RequestDate = reader.GetDateTime(1),
PARNumber = reader.GetString(2),
DepartmentName = reader.GetString(10),
DepartmentID = reader.GetInt64(11),
StatusID = reader.GetInt64(3),
FullName = reader.GetString(7),
InboxLearUID = reader.GetString(12),
ProgramName = reader.GetString(14),
ItemList = new List<NomsPRItem>(),
TotalAmount = 0.0
});
}
dAmount = (double)reader.GetDecimal(21) * (double)reader.GetDecimal(22);
data[key].TotalAmount += dAmount;
data[key].ItemList.Add(new NomsPRItem()
{
RequestID = key,
PartDesc = reader.GetString(17),
PartNumber = reader.GetString(23),
SupplierID = reader.GetString(18),
FullName = reader.GetString(7),
AccountType = reader.GetString(19),
CurrName = reader.GetString(20),
PartQuantity = (double)reader.GetDecimal(21),
PiecePrice = (double)reader.GetDecimal(22),
Amount = dAmount
});
}
}
}
return data.Values.ToList();
}
and that's it .. the query with underline is what I wanted to solve.
string sScript = m_sReport + ((EntityID == "") ? "" : " AND d.[EntityID]=" + EntityID) + ((DepartmentID == "") ? "" : " AND d.[DepartmentID]=" + DepartmentID)
+ " and [RequestDate] between '" + from.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + to.ToString("yyyy-MM-dd HH:mm:ss") + "'" + " and " + (( __________ ) ? "" : " AND d.[StatusID] in (" + ____________ + ")" );
And also what to pass in my MVC Controller.
public JsonResult GetList()
{
DateTime today = DateTime.Now;
List<NomsPRRequest> model = NomsConnection.LoadPRfromDB_withParams(new DateTime(today.Year, today.Month, 1, 0, 0, 0), today,"","");
return Json(model, JsonRequestBehavior.AllowGet);
}
public JsonResult GetReportList(string from, string to, string EntityID="", string DepartmentID="", int StatusID)
{
DateTime fromd = DateTime.Now;
DateTime tod = DateTime.Now;
if (from != "undefined")
fromd = Convert.ToDateTime(from);
if (to != "undefined")
tod = Convert.ToDateTime(to);
fromd = new DateTime(fromd.Year, fromd.Month, fromd.Day, 0, 0, 0);
tod = new DateTime(tod.Year, tod.Month, tod.Day, 23, 59, 59);
return Json(NomsConnection.LoadPRfromDB_withParams(fromd, tod, EntityID, DepartmentID, StatusID), JsonRequestBehavior.AllowGet);
}
Here is my view
<ul class="dropdown-menu" role="menu" data-ng-click="$event.stopPropagation()">
<li data-ng-repeat="item in StatusList">
<label class="checkbox-inline">
<input type="checkbox" data-checklist-value="1" data-checklist-model="filter.StatusID" />
{{item}}
</label>
</li>
</ul>
and my angular
scope.array_ = angular.copy(scope.array);
scope.getStatus = http.get('GetStatusList').success(function (status) {
scope.StatusList = status;
});
PRApp.directive("checkboxGroup", function () {
return {
restrict: "A",
link: function (scope, elem, attrs) {
// Determine initial checked boxes
if (scope.array.indexOf(scope.item.id) !== -1) {
elem[0].checked = true;
}
// Update array on click
elem.bind('click', function () {
var index = scope.array.indexOf(scope.item.id);
// Add if checked
if (elem[0].checked) {
if (index === -1) scope.array.push(scope.item.id);
}
// Remove if unchecked
else {
if (index !== -1) scope.array.splice(index, 1);
}
// Sort and update DOM display
scope.$apply(scope.array.sort(function (a, b) {
return a - b
}));
});
}
}
});
Also what to add in this part when passing the data....
scope.changeDate = function () {
scope.models = null;
var e = document.getElementById("entityList");
scope.EntityID = e.options[e.selectedIndex].value;
e = document.getElementById("deptList");
scope.DepartmentID = e.options[e.selectedIndex].value;
// console.log(this.filter_fromDate);
//console.log(this.filter_toDate);
http.get('GetReportList?from=' + scope.filter_fromDate + '&to=' + scope.filter_toDate + '&EntityID=' + scope.EntityID + '&DepartmentID=' + scope.DepartmentID).success(
function (data) {
scope.models = data;
});
}
