public static MaterialReportDTOCollection GetAllWaiveMaterialsByDate(long CaseID, DateTime?DateFrom, DateTime?DateTo, long?ContractID, long?ActivityID, long?PartID, long?MaterialID, bool?IsOrdered)
{
MaterialReportDTOCollection c = new MaterialReportDTOCollection();
SqlCommand cmd = new SqlCommand();
SqlParameterCollection Params = cmd.Parameters;
Params.Add(new SqlParameter("CaseID", CaseID));
if (DateFrom != null)
{
Params.Add(new SqlParameter("DateFrom", DateFrom));
}
if (DateTo != null)
{
Params.Add(new SqlParameter("DateTo", DateTo));
}
if (ContractID != null)
{
Params.Add(new SqlParameter("ContractID", ContractID));
}
if (ActivityID != null)
{
Params.Add(new SqlParameter("ActivityID", ActivityID));
}
if (PartID != null)
{
Params.Add(new SqlParameter("PartID", PartID));
}
if (MaterialID != null)
{
Params.Add(new SqlParameter("MaterialID", MaterialID));
}
if (IsOrdered != null)
{
Params.Add(new SqlParameter("IsOrdered", IsOrdered));
}
DataTable dt = Execute.FillDataTable(StoredProcedures.GetAllWaiveMaterialsByDate, Params);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
MaterialReportDTO o = new MaterialReportDTO();
LoadMaterialDTOByReader(row, o);
c.Add(o);
}
}
return(c);
}
protected void btnGetReport_Click(object sender, EventArgs e)
{
DateTime dateFrom = !String.IsNullOrEmpty(txtDateFrom.Text) ? Convert.ToDateTime(txtDateFrom.Text) : Convert.ToDateTime("1790-01-01 00:00:00");
DateTime dateTo = !String.IsNullOrEmpty(txtDateTo.Text) ? Convert.ToDateTime(txtDateTo.Text) : DateTime.MaxValue.AddYears(-1);
Int64 materialID = Convert.ToInt64(ddlMaterial.SelectedValue);
Int64 activityID = Convert.ToInt64(ddlActivity.SelectedValue);
Int64 partID = Convert.ToInt64(ddlPart.SelectedValue);
string title = "";
try
{
TotalPriceReportDTOCollection prices = new TotalPriceReportDTOCollection();
MaterialReportDTOCollection materials = new MaterialReportDTOCollection();
if (materialID > 0)
{
prices = TotalPriceReportDTO.Utils.GetTotalPricesGroupedByMonthByMaterialID(caseID, dateFrom, dateTo, materialID);
materials = MaterialReportDTO.Utils.GetAllWaiveMaterialsByDate(caseID, dateFrom, dateTo, null, null, null, materialID, null);
title = new Material(materialID).Title;
}
else if (partID > 0)
{
prices = TotalPriceReportDTO.Utils.GetTotalPricesGroupedByMonthByPartID(caseID, dateFrom, dateTo, partID);
title = new Part(partID).Title;
}
else if (activityID > 0)
{
prices = TotalPriceReportDTO.Utils.GetTotalPricesGroupedByMonthByActivityID(caseID, dateFrom, dateTo, activityID);
title = new Activity(activityID).Title;
}
else
{
prices = TotalPriceReportDTO.Utils.GetTotalPricesGroupedByMonth(caseID, dateFrom, dateTo);
title = "Total";
}
renderPrices(prices, title);
RenderChartScript(materials);
}
catch (Exception ex)
{
Response.Write(ex);
}
}
protected void Page_Load(object sender, EventArgs e)
{
materialID = Convert.ToInt64(Request.QueryString["materialid"]);
caseID = Convert.ToInt64(Request.QueryString["caseid"]);
Material material = new Material(materialID);
litHeader.Text = material.Title;
linkDetails.NavigateUrl = Urls.MaterialUrl + "?caseid=" + caseID + "&materialid=" + materialID + "&pagetype=material";
if (!Page.IsPostBack)
{
}
MaterialReportDTOCollection materials = MaterialReportDTO.Utils.GetAllWaiveMaterialsByDate(caseID, null, null, null, null, null, materialID, null);
RenderChartScript(materials);
}
private void RenderChartScript(MaterialReportDTOCollection materials)
{
DateTime startDate = Convert.ToDateTime(materials[0].Year + " " + materials[0].Month);
DateTime endDate = Convert.ToDateTime(materials[materials.Count - 1].Year + " " + materials[materials.Count - 1].Month);
IEnumerable <long> ids = materials.Select(m => m.ID).Distinct();
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter writer = new HtmlTextWriter(sw))
{
writer.RenderBeginTag(HtmlTextWriterTag.Script);
writer.WriteLine("$(document).ready(function() {");
writer.WriteLine("var ctx = $('#reportMaterialChart');");
writer.WriteLine("");
writer.WriteLine("var myChart = new Chart(ctx, {");
writer.WriteLine("type: 'line',");
writer.WriteLine("data: {");
writer.Write("labels: [");
foreach (var date in EachMonth(startDate, endDate))
{
writer.Write(" '" + date.Month + "/" + date.Year + "',");
}
writer.WriteLine("],");
writer.WriteLine("datasets: [");
long id = 0;
bool first = true;
foreach (var materialDTO in materials.OrderBy(m => m.ID))
{
if (id != materialDTO.ID)
{
if (!first)
{
writer.WriteLine("},");
}
writer.WriteLine("{label: '" + materialDTO.Title + "',");
writer.WriteLine("borderColor: 'rgba(30,136,229, 0.8)',");
writer.WriteLine("backgroundColor: 'rgba(30,136,229, 0.1)',");
RenderDataArray(writer, startDate, endDate, ids, materialDTO.ID, materials);
}
first = false;
id = materialDTO.ID;
}
writer.WriteLine("}");
writer.WriteLine("]},");
writer.WriteLine("options: {");
writer.WriteLine("scales: {");
writer.WriteLine("xAxes: [{");
writer.WriteLine("stacked: true");
writer.WriteLine("}],");
writer.WriteLine("yAxes: [{");
writer.WriteLine("stacked: true");
writer.WriteLine("}]");
writer.WriteLine("},");
writer.WriteLine("legend:");
writer.WriteLine("{");
writer.WriteLine("display: false");
writer.WriteLine("}");
writer.WriteLine("}");
writer.WriteLine("});");
writer.WriteLine("});");
writer.RenderEndTag(); // Script
}
litChartScript.Text = sw.ToString();
}
startDate.AddMonths(1);
}
public HtmlTextWriter RenderDataArray(HtmlTextWriter writer, DateTime start, DateTime end, IEnumerable <long> ids, long ID, MaterialReportDTOCollection materials)
{
bool first = true;
writer.Write("data:[");
foreach (DateTime date in EachMonth(start, end))
{
if (materials.Where(m => m.ID == ID && m.Year == date.Year && m.Month == date.Month).Count() > 0)
{
foreach (var materialDTO in materials.Where(m => m.ID == ID && m.Year == date.Year && m.Month == date.Month))
{
writer.Write((!first ? ", " : "") + materialDTO.Totalprice.ToString("N2").Replace(".", "").Replace(",", "."));
first = false;
}
}
else
{
writer.Write((!first ? ", " : "") + "0.00");
first = false;
}
}
writer.WriteLine("],");
return(writer);
}
public HtmlTextWriter RenderColorArray(HtmlTextWriter writer, DateTime start, DateTime end, IEnumerable <long> ids, long ID, MaterialReportDTOCollection materials, string Color)
{
bool first = true;
writer.Write("backgroundColor:[");
foreach (DateTime date in EachMonth(start, end))
{
writer.Write((!first ? ", " : "") + Color);
first = false;
}
writer.WriteLine("]");
return(writer);
}
