public double GetSalesRepCommission(long salesRepID, int month, int year)
{
double Commission = 0;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pSalesRepID = new SqlParameter
{
ParameterName = "salesRepID",
Value = salesRepID
};
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
Commission = ctx.Database.SqlQuery<double>("exec GetSalesRepCommission @salesRepID, @month, @year ", pSalesRepID, pMonth, pYear).FirstOrDefault<double>();
}
}
return Commission;
}
public double GetSalesRepCommission(long salesRepID, int month, int year)
{
double Commission = 0;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pSalesRepID = new SqlParameter
{
ParameterName = "salesRepID",
Value = salesRepID
};
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
Commission = ctx.Database.SqlQuery <double>("exec GetSalesRepCommission @salesRepID, @month, @year ", pSalesRepID, pMonth, pYear).FirstOrDefault <double>();
}
}
return(Commission);
}
public IList<CustomerBill> GetCustomerBill(long customerID, int month, int year)
{
IList<CustomerBill> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pCustomerID = new SqlParameter
{
ParameterName = "customerID",
Value = customerID
};
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery<CustomerBill>("exec GetCustomerBill @customerID, @month, @year ", pCustomerID, pMonth, pYear).ToList<CustomerBill>();
}
}
return list;
}
public IList <CustomerBill> GetCustomerBill(long customerID, int month, int year)
{
IList <CustomerBill> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pCustomerID = new SqlParameter
{
ParameterName = "customerID",
Value = customerID
};
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery <CustomerBill>("exec GetCustomerBill @customerID, @month, @year ", pCustomerID, pMonth, pYear).ToList <CustomerBill>();
}
}
return(list);
}
public IList <Rate> GetRate(string serviceName, long sourceCountryID, int month, int year)
{
IList <Rate> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pServiceName = new SqlParameter
{
ParameterName = "serviceName",
Value = serviceName
};
var pSourceCountryID = new SqlParameter
{
ParameterName = "sourceCountryID",
Value = sourceCountryID
};
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery <Rate>("exec GetRate @serviceName, @sourceCountryID, @month, @year ", pServiceName, pSourceCountryID, pMonth, pYear).ToList <Rate>();
}
}
return(list);
}
//Check if CallExist, by tuandang
private static Boolean CheckToInsert(DateTime CallDate, double CallTime, double Duration, string ReceiverNo, double CustomerID, double SourceCountry_ID, double DestinationCountry_ID)
{
bool isOk = true;
TelecomContext db = new TelecomContext();
//check Customer exists
Customer customer = db.Customers.Where(c => c.ID == CustomerID).FirstOrDefault();
isOk = customer != null;
if (isOk)
{
//check Call exists
Call call = db.Calls.Where(c => c.CustomerID == CustomerID && c.ReceiverNo == ReceiverNo &&
c.CallDate == CallDate && c.CallTime == CallTime).FirstOrDefault();
isOk = call == null;
}
return isOk;
}
public IList<SummarySalesRepCommision> GetSummarySalesRepCommission(int month, int year)
{
IList<SummarySalesRepCommision> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery<SummarySalesRepCommision>("exec GetSummarySalesRepCommission @month, @year ", pMonth, pYear).ToList();
}
}
return list;
}
public IList <TrafficSummary> GetTrafficSummary(int month, int year)
{
IList <TrafficSummary> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery <TrafficSummary>("exec GetTrafficSummary @month, @year ", pMonth, pYear).ToList();
}
}
return(list);
}
public IList <SummarySalesRepCommision> GetSummarySalesRepCommission(int month, int year)
{
IList <SummarySalesRepCommision> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery <SummarySalesRepCommision>("exec GetSummarySalesRepCommission @month, @year ", pMonth, pYear).ToList();
}
}
return(list);
}
public IList<TrafficSummary> GetTrafficSummary(int month, int year)
{
IList<TrafficSummary> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery<TrafficSummary>("exec GetTrafficSummary @month, @year ", pMonth, pYear).ToList();
}
}
return list;
}
public IList<Rate> GetRate(string serviceName, long sourceCountryID, int month, int year)
{
IList<Rate> list = null;
using (TelecomContext context = new TelecomContext())
{
using (var ctx = new TelecomContext())
{
var pServiceName = new SqlParameter
{
ParameterName = "serviceName",
Value = serviceName
};
var pSourceCountryID = new SqlParameter
{
ParameterName = "sourceCountryID",
Value = sourceCountryID
};
var pMonth = new SqlParameter
{
ParameterName = "month",
Value = month
}; var pYear = new SqlParameter
{
ParameterName = "year",
Value = year
};
list = ctx.Database.SqlQuery<Rate>("exec GetRate @serviceName, @sourceCountryID, @month, @year ", pServiceName, pSourceCountryID, pMonth, pYear).ToList<Rate>();
}
}
return list;
}
