/// <summary>
/// Execute Report
/// </summary>
/// <param name="pstrCCNID">CCN</param>
/// <param name="pstrYear">Year</param>
/// <param name="pstrMonth">Month</param>
/// <param name="pstrProLineID">Production Line</param>
/// <param name="pstrWorkCenterID">Work Center</param>
/// <returns></returns>
public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrMonth, string pstrProLineID, string pstrWorkCenterID)
{
const string WORKING_DATE = "WorkingDate";
const string BEGIN_DATE = "BeginDate";
const string END_DATE = "EndDate";
const double FIELD_WIDTH = 585;
const string FLD = "fld";
int intCCNID = 0;
int intYear = 0;
int intMonth = 0;
int intProductionLineID = 0;
int intWorkCenterID = 0;
try
{
intCCNID = int.Parse(pstrCCNID);
}
catch {}
try
{
intYear = int.Parse(pstrYear);
}
catch {}
try
{
intMonth = int.Parse(pstrMonth);
}
catch {}
try
{
intProductionLineID = int.Parse(pstrProLineID);
}
catch {}
try
{
intWorkCenterID = int.Parse(pstrWorkCenterID);
}
catch {}
DateTime dtmStartDate = new DateTime(intYear, intMonth, 1);
DateTime dtmEndDate = dtmStartDate.AddMonths(1).AddDays(-1);
string strExpression = string.Empty;
Hashtable arrStandardCapacity = new Hashtable();
Hashtable arrActual = new Hashtable();
Hashtable arrRemain = new Hashtable();
Hashtable arrEffective = new Hashtable();
C1Report rptReport = new C1Report();
string strMonth = dtmStartDate.ToString("MMM");
// planning offset
DataTable dtbPlanningOffset = GetPlanningOffset(pstrCCNID);
// get all cycles in selected year
DataTable dtbCycles = GetCycles(pstrCCNID);
// refine cycles as of date based on production line
dtbCycles = RefineCycle(pstrProLineID, dtbPlanningOffset, dtbCycles);
// all planning period
ArrayList arrPlanningPeriod = GetPlanningPeriod(pstrCCNID);
StringBuilder sbCycleIDs;
DataTable dtbCyclesCurrentMonth = ArrangeCycles(dtmStartDate, dtmEndDate, dtbCycles, arrPlanningPeriod, out sbCycleIDs);
DataTable dtbStandard = GetStandardCapacity(intWorkCenterID, intCCNID, intProductionLineID);
DataTable dtbTRC = GetTotalRequiredCapacity(intProductionLineID, sbCycleIDs.ToString(), dtmStartDate, dtmEndDate);
DataTable dtbValidWorkDay = GetWorkingDateFromWCCapacity(intProductionLineID);
decimal decTotalStandard = 0;
decimal decTotalActual = 0;
decimal decTotalRemain = 0;
decimal decTotalEffective = 0;
DataRow[] drowStandard = null;
for (int i = dtmStartDate.Day; i <= dtmEndDate.Day; i++)
{
DateTime dtmDate = new DateTime(dtmStartDate.Year, dtmStartDate.Month, i);
string strColName = "D" + i.ToString("00");
decimal decSC = 0;
decimal decActual = 0;
decimal decRemain = 0;
decimal decEffective = 0;
strExpression = BEGIN_DATE + "<='" + dtmDate.ToString("G")
+ "' AND " + END_DATE + ">='" + dtmDate.ToString("G") + "'";
DataRow[] drowValidWorkDay = dtbValidWorkDay.Select(strExpression);
if (drowValidWorkDay.Length == 0)
{
arrStandardCapacity.Add(strColName, decSC);
arrActual.Add(strColName, decActual);
arrRemain.Add(strColName, decRemain);
arrEffective.Add(strColName, decimal.Round(decEffective, 2));
continue;
}
string strCycleID = GetCycleOfDate(dtmDate, dtbCyclesCurrentMonth);
string strFilter = "WorkingDate = '" + dtmDate.ToString() + "'"
+ " AND DCOptionMasterID = '" + strCycleID + "'";
#region Standard Capacity
drowStandard = dtbStandard.Select(strExpression);
foreach (DataRow drowData in drowStandard)
{
try
{
decSC += (decimal)drowData["Capacity"];
}
catch
{
}
}
arrStandardCapacity.Add(strColName, decimal.Round(decSC, 0));
decTotalStandard += decSC;
#endregion
#region Total Required Capacity
DataRow[] drowTotalRequired = dtbTRC.Select(strFilter);
foreach (DataRow drowData in drowTotalRequired)
{
try
{
decActual += (decimal)drowData["TotalSecond"];
}
catch {}
}
arrActual.Add(strColName, decimal.Round(decActual, 0));
decTotalActual += decActual;
#endregion
#region Effective = Required Cap / Standard Cap
try
{
decEffective = decActual / decSC;
}
catch {}
arrEffective.Add(strColName, decimal.Round(decEffective, 2));
#endregion
#region Remain Capacity
// remain capacity
decRemain = decSC - decActual;
arrRemain.Add(strColName, decimal.Round(decRemain, 0));
#endregion
}
arrStandardCapacity.Add("Total", decimal.Round(decTotalStandard, 0));
arrActual.Add("Total", decimal.Round(decTotalActual, 0));
decTotalRemain = decTotalStandard - decTotalActual;
arrRemain.Add("Total", decimal.Round(decTotalRemain, 0));
try
{
decTotalEffective = decTotalActual / decTotalStandard;
}
catch {}
arrEffective.Add("Total", decimal.Round(decTotalEffective, 2));
/// column Name in the dtbResult
const string STANDARD_CAPACITY = "StandardCapacity";
const string TOTAL_REQUIRED_CAPACITY = "TotalRequiredCapacity";
const string REMAIN_CAPACITY = "RemainCapacity";
const string EFFECTIVE = "Effective";
DataTable dtbResult = new DataTable();
dtbResult.Columns.Add(new DataColumn("RowType", typeof(string)));
#region Report layout
mLayoutFile = "CASReport.xml";
string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile);
rptReport.Load(mReportFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);
arrstrReportInDefinitionFile = null;
rptReport.Layout.PaperSize = PaperKind.A3;
#endregion
for (int i = dtmStartDate.Day; i <= dtmEndDate.Day; i++)
{
string strColumnName = "D" + i.ToString("00");
dtbResult.Columns.Add(new DataColumn(strColumnName, typeof(decimal)));
#region Report layout
DateTime dtmDay = new DateTime(intYear, intMonth, i);
string strDate = "fldD" + i.ToString("00");
string strDay = "fldDay" + i.ToString("00");
try
{
rptReport.Fields[strDate].Text = i + "-" + strMonth;
}
catch
{
}
try
{
rptReport.Fields[strDay].Text = dtmDay.DayOfWeek.ToString().Substring(0, 3);
}
catch
{
}
DataRow[] drowValidWorkDay = dtbValidWorkDay.Select("BeginDate <= '" + dtmDay.ToString("G") + "'" + " AND EndDate >='" + dtmDay.ToString("G") + "'");
if (drowValidWorkDay.Length == 0)
{
try
{
if (dtmDay.DayOfWeek == DayOfWeek.Saturday)
{
rptReport.Fields[strDate].ForeColor = Color.Blue;
rptReport.Fields[strDate].BackColor = Color.Yellow;
}
else
{
rptReport.Fields[strDate].ForeColor = Color.Red;
rptReport.Fields[strDate].BackColor = Color.Yellow;
}
}
catch
{
}
try
{
if (dtmDay.DayOfWeek == DayOfWeek.Saturday)
{
rptReport.Fields[strDay].ForeColor = Color.Blue;
rptReport.Fields[strDay].BackColor = Color.Yellow;
}
else
{
rptReport.Fields[strDay].ForeColor = Color.Red;
rptReport.Fields[strDay].BackColor = Color.Yellow;
}
}
catch
{
}
}
#endregion
}
#region Layout the format based on days in month
int intDaysInMonth = DateTime.DaysInMonth(dtmStartDate.Year, dtmStartDate.Month);
if (intDaysInMonth < 31)
{
for (int i = intDaysInMonth + 1; i <= 31; i++)
{
#region field name
string strDate = "fldD" + i.ToString("00");
string strDayOfWeek = "fldDay" + i.ToString("00");
string strDiv = "div" + i.ToString("00");
#endregion
#region Report Header
try
{
rptReport.Fields[strDate].Visible = false;
}
catch
{
}
try
{
rptReport.Fields[strDayOfWeek].Visible = false;
}
catch
{
}
try
{
rptReport.Fields[strDiv].Visible = false;
}
catch
{
}
#endregion
}
try
{
#region Resize all line
//double dWidth = rptReport.Fields["line1"].Width;
for (int i = 1; i <= 7; i++)
{
rptReport.Fields["line" + i].Width = rptReport.Fields["line" + i].Width - (31 - intDaysInMonth) * FIELD_WIDTH;
}
#endregion
double dWidthToChange = (31 - intDaysInMonth) * FIELD_WIDTH;
#region Total columns
rptReport.Fields["fldDTotal"].Left =
rptReport.Fields["fldStandardCapacityD"].Left =
rptReport.Fields["fldTotalRequiredCapacityD"].Left =
rptReport.Fields["fldEffectiveD"].Left =
rptReport.Fields["fldRemainCapacityD"].Left = rptReport.Fields["fldDTotal"].Left - dWidthToChange;
rptReport.Fields["divTotal"].Left = rptReport.Fields["fldDTotal"].Left + FIELD_WIDTH;
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
DataRow drowSC = dtbResult.NewRow();
drowSC["RowType"] = STANDARD_CAPACITY;
DataRow drowTR = dtbResult.NewRow();
drowTR["RowType"] = TOTAL_REQUIRED_CAPACITY;
DataRow drowRC = dtbResult.NewRow();
drowRC["RowType"] = REMAIN_CAPACITY;
DataRow drowEff = dtbResult.NewRow();
drowEff["RowType"] = EFFECTIVE;
for (int i = dtmStartDate.Day; i <= dtmEndDate.Day; i++)
{
string strColumnName = "D" + i.ToString("00");
drowSC[strColumnName] = arrStandardCapacity[strColumnName];
drowTR[strColumnName] = arrActual[strColumnName];
drowRC[strColumnName] = arrRemain[strColumnName];
drowEff[strColumnName] = arrEffective[strColumnName];
}
dtbResult.Rows.Add(drowSC);
dtbResult.Rows.Add(drowTR);
dtbResult.Rows.Add(drowRC);
dtbResult.Rows.Add(drowEff);
#region RENDER REPORT
const string REPORTFLD_CHART = "fldChart";
const string REPORTFLD_TOTALCHART = "fldTotalChart";
if (dtbResult.Rows.Count > 0)
{
#region BUILD CHART, save to image in clipboard, and then put in the report field fldChart
Field fldChart = rptReport.Fields[REPORTFLD_CHART];
Field fldTotalChart = rptReport.Fields[REPORTFLD_TOTALCHART];
#region INIT
string EXCEL_FILE = "CASReport.xls";
string strTemplateFilePath = mReportFolder + Path.DirectorySeparatorChar + EXCEL_FILE;
string strDestinationFilePath = mReportFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";
/// Copy layout excel report file to ExcelReport folder with a UTC datetime name
File.Copy(strTemplateFilePath, strDestinationFilePath, true);
ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);
#endregion
try
{
#region BUILD THE REPORT ON EXCEL FILE
string[] arrExcelColumnHeading = new string[DateTime.DaysInMonth(intYear, intMonth)];
for (int i = 1; i <= intDaysInMonth; i++)
{
DateTime dtmDate = new DateTime(intYear, intMonth, i);
string strColHeading = i + "-" + strMonth + "\n" + dtmDate.DayOfWeek.ToString().Substring(0, 3);
arrExcelColumnHeading[i - 1] = strColHeading;
}
double[,] arrExcelStandard = new double[1, intDaysInMonth];
double[,] arrExcelActual = new double[1, intDaysInMonth];
for (int i = dtmStartDate.Day; i <= dtmEndDate.Day; i++)
{
string strSC = "fldStandardCapacityD" + i.ToString("00");
string strTR = "fldTotalRequiredCapacityD" + i.ToString("00");
string strRC = "fldRemainCapacityD" + i.ToString("00");
string strEff = "fldEffectiveD" + i.ToString("00");
string strColName = "D" + i.ToString("00");
rptReport.Fields[strSC].Text = arrStandardCapacity[strColName].ToString();
rptReport.Fields[strTR].Text = arrActual[strColName].ToString();
rptReport.Fields[strRC].Text = arrRemain[strColName].ToString();
rptReport.Fields[strEff].Text = arrEffective[strColName].ToString();
try
{
arrExcelStandard[0, i - 1] = Decimal.ToDouble((decimal)arrStandardCapacity[strColName]);
}
catch {}
try
{
arrExcelActual[0, i - 1] = Decimal.ToDouble((decimal)arrActual[strColName]);
}
catch {}
}
// total field
rptReport.Fields["fldStandardCapacityD"].Text = arrStandardCapacity["Total"].ToString();
rptReport.Fields["fldTotalRequiredCapacityD"].Text = arrActual["Total"].ToString();
rptReport.Fields["fldRemainCapacityD"].Text = arrRemain["Total"].ToString();
rptReport.Fields["fldEffectiveD"].Text = arrEffective["Total"].ToString();
switch (intDaysInMonth)
{
case 28:
objXLS.GetRange("A1", "AB1").Value2 = arrExcelColumnHeading;
objXLS.GetRange("A2", "AB2").Value2 = arrExcelStandard;
objXLS.GetRange("A3", "AB3").Value2 = arrExcelActual;
break;
case 29:
objXLS.GetRange("A1", "AC1").Value2 = arrExcelColumnHeading;
objXLS.GetRange("A2", "AC2").Value2 = arrExcelStandard;
objXLS.GetRange("A3", "AC3").Value2 = arrExcelActual;
break;
case 30:
objXLS.GetRange("A1", "AD1").Value2 = arrExcelColumnHeading;
objXLS.GetRange("A2", "AD2").Value2 = arrExcelStandard;
objXLS.GetRange("A3", "AD3").Value2 = arrExcelActual;
break;
default:
objXLS.GetRange("A1", "AE1").Value2 = arrExcelColumnHeading;
objXLS.GetRange("A2", "AE2").Value2 = arrExcelStandard;
objXLS.GetRange("A3", "AE3").Value2 = arrExcelActual;
break;
}
ChartObject chart = objXLS.GetChart("DetailChart");
// SeriesCollection serieSC = (SeriesCollection) chart.Chart.SeriesCollection(0);
chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap, XlPictureAppearance.xlScreen);
Image image = (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
fldChart.Visible = true;
fldChart.Text = "";
fldChart.Picture = image;
chart = objXLS.GetChart("Chart 12");
chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap, XlPictureAppearance.xlScreen);
image = (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
fldTotalChart.Visible = true;
fldTotalChart.Text = "";
fldTotalChart.Picture = image;
#endregion
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
finally
{
#region SAVE, CLOSE EXCEL FILE CONTAIN REPORT
objXLS.CloseWorkbook();
objXLS.Dispose();
objXLS = null;
#endregion
}
#endregion BUILD CHART
}
#region MODIFY THE REPORT LAYOUT
#region PUSH PARAMETER VALUE
const string REPORTFLD_PARAMETER_CCN = "fldParameterCCN";
const string REPORTFLD_PARAMETER_MONTH = "fldParameterMonth";
const string REPORTFLD_PARAMETER_YEAR = "fldParameterYear";
const string REPORTFLD_PARAMETER_PRODUCTIONLINE = "fldParameterProductionLine";
const string REPORTFLD_PARAMETER_WORKCENTER = "fldParameterWorkCenter";
string strCCN = GetCCNCode(intCCNID);
rptReport.Fields[REPORTFLD_PARAMETER_CCN].Text = strCCN;
rptReport.Fields[REPORTFLD_PARAMETER_MONTH].Text = pstrMonth;
rptReport.Fields[REPORTFLD_PARAMETER_YEAR].Text = pstrYear;
string strProductionLine = GetProCodeAndName(intProductionLineID);
rptReport.Fields[REPORTFLD_PARAMETER_PRODUCTIONLINE].Text = strProductionLine;
string strWorkCenter = GetWCCodeAndName(intWorkCenterID);
rptReport.Fields[REPORTFLD_PARAMETER_WORKCENTER].Text = strWorkCenter;
#endregion
#endregion
rptReport.DataSource.Recordset = dtbResult;
rptReport.Render();
C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog();
ppvViewer.FormTitle = "Compare Actual And Standard Capacity";
ppvViewer.ReportViewer.Document = rptReport.Document;
ppvViewer.Show();
#endregion
return(dtbResult);
}