public BillingResponseData GetBillingInfoRequestData()
{
var brd = new BillingResponseData();
using (var sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
string userName;
if (HttpContext.Current.Request.UrlReferrer != null && (HttpContext.Current.Request.UrlReferrer.Host.Contains("localhost") && HttpContext.Current.Request.UrlReferrer.Port == 51268))
{
userName = "******";// HttpContext.Current.User.Identity.Name;
}
else
{
userName = HttpContext.Current.User.Identity.Name;
}
var sqlComm = new SqlCommand();
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.CommandText = "getAllBilling";
sqlComm.Parameters.AddWithValue("@username", userName);
sqlComm.Connection = sqlCon;
sqlCon.Open();
var sda = new SqlDataAdapter(sqlComm);
var ds = new DataSet();
sda.Fill(ds);
brd.months = new List <BillingData>();
foreach (DataRow dr in ds.Tables[0].Rows)
//try
{
var billingData = new BillingData()
{
date = new DateTime(dr.Field <int>("bill_year"), dr.Field <int>("bill_month"), 1),
currentBillableRate = dr.Field <double?>("rate"),
billableTime = dr.Field <int?>("billable_time"),
successfulTime = dr.Field <int?>("successful_time"),
badCallsTime = dr.Field <int?>("bad_call_length"),
transcriptMinutes = dr.Field <int?>("transcript_time"),
chatsReviewed = dr.Field <int?>("chats_reviewed"),
websitesReviewed = dr.Field <int?>("websites_reviewed"),
chatsCost = dr.Field <double?>("chat_cost"),
websitesCost = dr.Field <double?>("website_cost")
};
brd.months.Add(billingData);
}
//catch (Exception)
//{
// //throw ex;
//}
brd.cpmBillableRate = new List <BillableRate>();
foreach (DataRow dr in ds.Tables[1].Rows)
{
var billingRate = new BillableRate()
{
rate = dr.Field <double?>("rate"),
minutesFrom = dr.Field <int?>("start_minutes"),
minutesTo = dr.Field <int?>("end_minutes")
};
brd.cpmBillableRate.Add(billingRate);
}
brd.transcriptRate = (double?)ds.Tables[2].Rows[0].ItemArray[0];
brd.minimumMinutes = (int?)ds.Tables[2].Rows[0].ItemArray[1];
brd.budget = (double?)ds.Tables[2].Rows[0].ItemArray[2];
return(brd);
}
}
public dynamic ExportBilling(string userName, DateTime?date = null)
{
var brd = new BillingResponseData();
using (var sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
var sqlComm = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = "[getAllBillingForExport]"
};
sqlComm.Parameters.AddWithValue("@username", userName);
if (date != null)
{
sqlComm.Parameters.AddWithValue("@month", date.Value.Month);
sqlComm.Parameters.AddWithValue("@year", date.Value.Year);
}
else
{
sqlComm.Parameters.AddWithValue("@month", 0);
sqlComm.Parameters.AddWithValue("@year", 0);
}
sqlComm.Connection = sqlCon;
sqlCon.Open();
var sda = new SqlDataAdapter(sqlComm);
var ds = new DataSet();
sda.Fill(ds);
brd.months = new List <BillingData>();
foreach (DataRow dr in ds.Tables[0].Rows)
//try
{
var billingData = new BillingData()
{
date = new DateTime(dr.Field <int>("bill_year"), dr.Field <int>("bill_month"), 1),
currentBillableRate = dr.Field <double?>("rate"),
billableTime = dr.Field <int?>("billable_time"),
successfulTime = dr.Field <int?>("successful_time"),
badCallsTime = dr.Field <int?>("bad_call_length"),
transcriptMinutes = dr.Field <int?>("transcript_time"),
chatsReviewed = dr.Field <int?>("chats_reviewed"),
websitesReviewed = dr.Field <int?>("websites_reviewed"),
chatsCost = dr.Field <double?>("chat_cost"),
websitesCost = dr.Field <double?>("website_cost")
};
brd.months.Add(billingData);
}
//catch (Exception)
//{
// //throw ex;
//}
brd.cpmBillableRate = new List <BillableRate>();
foreach (DataRow dr in ds.Tables[1].Rows)
{
var billingRate = new BillableRate()
{
rate = dr.Field <double?>("rate"),
minutesFrom = dr.Field <int?>("start_minutes"),
minutesTo = dr.Field <int?>("end_minutes")
};
brd.cpmBillableRate.Add(billingRate);
}
brd.transcriptRate = (double?)ds.Tables[2].Rows[0].ItemArray[0];
brd.minimumMinutes = (int?)ds.Tables[2].Rows[0].ItemArray[1];
brd.budget = (double?)ds.Tables[2].Rows[0].ItemArray[2];
//return brd;
try
{
List <ExportBillingModel> exportBillingModel = new List <ExportBillingModel>();
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Date", propValue = "date", propPosition = 1
},
new PropertieName {
propName = "Successful time", propValue = "successfulTime", propPosition = 2
},
new PropertieName {
propName = "Billable time", propValue = "billableTime", propPosition = 3
},
new PropertieName {
propName = "Current billable rate", propValue = "currentBillableRate", propPosition = 4
},
new PropertieName {
propName = "Bad calls time", propValue = "badCallsTime", propPosition = 5
},
new PropertieName {
propName = "Transcript minutes", propValue = "transcriptMinutes", propPosition = 6
},
new PropertieName {
propName = "Chats reviwed", propValue = "chatsReviewed", propPosition = 7
},
new PropertieName {
propName = "Websites reviewed", propValue = "websitesReviewed", propPosition = 8
}
};
foreach (var item in brd.months)
{
exportBillingModel.Add(new ExportBillingModel
{
date = item.date,
successfulTime = item.successfulTime,
billableTime = item.billableTime,
currentBillableRate = item.currentBillableRate,
badCallsTime = item.badCallsTime,
transcriptMinutes = item.transcriptMinutes,
chatsReviewed = item.chatsReviewed,
websitesReviewed = item.websitesReviewed
});
}
ExportHelper.Export(propNames, exportBillingModel, "Billing" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Millisecond.ToString() + ".xlsx", "Billing", userName);
}
catch (Exception ex)
{
throw ex;
}
return("success");
}
}
