private DataTable GetGridDataTable(string yearStr, string util, string sGongjang)
{
string query = GetGridQuery(yearStr, util, sGongjang);
string query_item = @"SELECT DISTINCT ITMU_IDX, ITMU_NAME FROM D_ITEM_UTIL WHERE (ITMU_IDX = " + util + " OR 0 = " + util + ")";;
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataSet ds_item = dbAgent.FillDataSet(query_item, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("ITMU_NAME", typeof(string));
dataTable.Columns.Add("ITMU_TYPE", typeof(string));
for (int i = 1; i <= 12; i++)
{
dataTable.Columns.Add("C_" + i.ToString(), typeof(double));
}
dataTable.Columns.Add("년간합계", typeof(double));
for (int i = 0; i < ds_item.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < 2; j++)
{
dr = dataTable.NewRow();
dr["ITMU_NAME"] = ds_item.Tables[0].Rows[i]["ITMU_NAME"].ToString();
dr["ITMU_TYPE"] = GetType(j);
for (int k = 1; k <= 12; k++)
{
dr["C_" + k.ToString()] = 0;
}
dataTable.Rows.Add(dr);
}
}
DataRow[] drCol = null;
for (int i = 0; i < ds_item.Tables[0].Rows.Count; i++)
{
for (int j = 1; j <= 12; j++)
{
drCol = ds.Tables[0].Select("yyyy_mm = '" + yearStr + j.ToString().PadLeft(2, '0') + "' AND ITMU_IDX = '" + ds_item.Tables[0].Rows[i]["ITMU_IDX"].ToString() + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i + (i * 1)]["C_" + j.ToString()] = Convert.ToDouble(drCol[0]["USEQTY"].ToString());
dataTable.Rows[i + (i * 1) + 1]["C_" + j.ToString()] = Convert.ToDouble(drCol[0]["AMT3"].ToString());
}
}
}
return(dataTable);
}
private DataTable GetHeaderTable(string yearStr, string monthStr, string yearTStr, string monthTStr, string areaStr)
{
string query_p = GetGridHeaderQuery(Convert.ToString(int.Parse(yearStr) - 1), monthStr, Convert.ToString(int.Parse(yearTStr) - 1), monthTStr, areaStr);
string query_c = GetGridHeaderQuery(yearStr, monthStr, yearTStr, monthTStr, areaStr);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds_p = dbAgent.FillDataSet(query_p, "Data");
DataSet ds_c = dbAgent.FillDataSet(query_c, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
DataRowCollection drCol_p = null;
DataRowCollection drCol_c = null;
dataTable.Columns.Add("C_ACTUAL", typeof(double));
dataTable.Columns.Add("C_PLAN", typeof(double));
dataTable.Columns.Add("P_PAST_ACTUAL", typeof(double));
dataTable.Columns.Add("P_PAST_COUNT", typeof(double));
dataTable.Columns.Add("P_PAST_RATE", typeof(double));
dataTable.Columns.Add("P_PLAN_COUNT", typeof(double));
dataTable.Columns.Add("P_PLAN_RATE", typeof(double));
drCol_c = ds_c.Tables[0].Rows;
drCol_p = ds_p.Tables[0].Rows;
dr = dataTable.NewRow();
dr["C_ACTUAL"] = 0;
dr["C_PLAN"] = 0;
dr["P_PAST_ACTUAL"] = 0;
dr["P_PAST_COUNT"] = 0;
dr["P_PAST_RATE"] = 0;
dr["P_PLAN_COUNT"] = 0;
dr["P_PLAN_RATE"] = 0;
dataTable.Rows.Add(dr);
if (drCol_c.Count > 0)
{
dataTable.Rows[0]["C_ACTUAL"] = Convert.ToDouble(drCol_c[0]["ACTUAL"].ToString());
dataTable.Rows[0]["C_PLAN"] = Convert.ToDouble(drCol_c[0]["PLAN"].ToString());
dataTable.Rows[0]["P_PLAN_COUNT"] = Convert.ToDouble(drCol_c[0]["ACTUAL"].ToString()) - Convert.ToDouble(drCol_c[0]["PLAN"].ToString());
dataTable.Rows[0]["P_PLAN_RATE"] = Math.Round(Convert.ToDouble(drCol_c[0]["ACTUAL"].ToString()) / Convert.ToDouble(drCol_c[0]["PLAN"].ToString()) * 100.00);
}
if (drCol_p.Count > 0 && drCol_c.Count > 0)
{
dataTable.Rows[0]["P_PAST_ACTUAL"] = Convert.ToDouble(drCol_p[0]["ACTUAL"].ToString());
dataTable.Rows[0]["P_PAST_COUNT"] = Convert.ToDouble(drCol_c[0]["ACTUAL"].ToString()) - Convert.ToDouble(drCol_p[0]["ACTUAL"].ToString());
dataTable.Rows[0]["P_PAST_RATE"] = Math.Round(Convert.ToDouble(drCol_c[0]["ACTUAL"].ToString()) / Convert.ToDouble(drCol_p[0]["ACTUAL"].ToString()) * 100.00);
}
return(dataTable);
}
private DataTable GetChartDataTable(string areaStr)
{
string query = GetQuery(areaStr);
string query_jikgub = GetJikGubQuery();
string query_buseo = GetTeamQuery(areaStr);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataSet ds_jikgub = dbAgent.FillDataSet(query_jikgub, "Data");
DataSet ds_buseo = dbAgent.FillDataSet(query_buseo, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("TEAM_NM", typeof(string));
for (int i = 0; i < ds_jikgub.Tables[0].Rows.Count; i++)
{
dataTable.Columns.Add("C_" + ds_jikgub.Tables[0].Rows[i]["JIKGUB_NM"].ToString(), typeof(double));
}
for (int i = 0; i < ds_buseo.Tables[0].Rows.Count; i++)
{
dr = dataTable.NewRow();
dr["TEAM_NM"] = ds_buseo.Tables[0].Rows[i]["TEAM_NM"].ToString();
for (int j = 0; j < ds_jikgub.Tables[0].Rows.Count; j++)
{
dr["C_" + ds_jikgub.Tables[0].Rows[j]["JIKGUB_NM"].ToString()] = 0;
}
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 0; i < ds_buseo.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds_jikgub.Tables[0].Rows.Count; j++)
{
drCol = ds.Tables[0].Select("TEAM_NM = '" + ds_buseo.Tables[0].Rows[i]["TEAM_NM"].ToString() + "' AND JIKGUB = '" + ds_jikgub.Tables[0].Rows[j]["JIKGUB_NM"].ToString() + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i]["C_" + ds_jikgub.Tables[0].Rows[j]["JIKGUB_NM"].ToString()] = Convert.ToDouble(drCol[0]["INWONSU"].ToString());
}
}
}
return(dataTable);
}
private DataTable GetChartDataTable_2(string yearStr)
{
string query = GetQuery_2(yearStr);
string query_item = @"SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PAN_ITMNM, ' ', ''), '25Kg', ''), '(Plt)', ''), 'Bulk', ''), '20Kg', ''), '50Kg', ''), '1Ton', '') PAN_ITMNM FROM D_ITEM_PANMAE WHERE PAN_ITMNM LIKE '%유안%' GROUP BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PAN_ITMNM, ' ', ''), '25Kg', ''), '(Plt)', ''), 'Bulk', ''), '20Kg', ''), '50Kg', ''), '1Ton', '')";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataSet ds_item = dbAgent.FillDataSet(query_item, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("MONTH", typeof(string));
for (int i = 0; i < ds_item.Tables[0].Rows.Count; i++)
{
dataTable.Columns.Add("C_" + ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString(), typeof(double));
}
for (int i = 1; i <= 12; i++)
{
dr = dataTable.NewRow();
dr["MONTH"] = i.ToString();
for (int j = 0; j < ds_item.Tables[0].Rows.Count; j++)
{
dr["C_" + ds_item.Tables[0].Rows[j]["PAN_ITMNM"].ToString()] = 0;
}
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
for (int j = 0; j < ds_item.Tables[0].Rows.Count; j++)
{
drCol = ds.Tables[0].Select("yyyy_mm = '" + yearStr + i.ToString().PadLeft(2, '0') + "' AND PAN_ITMNM = '" + ds_item.Tables[0].Rows[j]["PAN_ITMNM"].ToString() + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i - 1]["C_" + ds_item.Tables[0].Rows[j]["PAN_ITMNM"].ToString()] = Convert.ToDouble(drCol[0]["PAN_QTY"].ToString());
}
}
}
return(dataTable);
}
private DataTable GetChartDataTable_2(string yearStr)
{
string query = GetColumnQuery_2(yearStr);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
return(dbAgent.FillDataSet(query, "Data").Tables[0]);
}
private void BindingChart(Dundas.Charting.WebControl.Chart chart, string areaStr)
{
string query_jikgub = GetJikGubQuery();
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds_jikgub = dbAgent.FillDataSet(query_jikgub, "Data");
//chart.DataSource = GetChartDataTable(areaStr).DefaultView;
DataTable dt = GetChartDataTable(areaStr);
DundasCharts.DundasChartBase(chart, ChartImageType.Flash, 800, 390
, BorderSkinStyle.Emboss, Color.FromArgb(181, 64, 1), 2
, Color.FromArgb(0xFF, 0xFF, 0xFE)
, Color.FromArgb(0xFF, 0xFF, 0xFE), Color.FromArgb(0x20, 0x80, 0xD0), ChartDashStyle.Solid
, -1
, ChartHatchStyle.None, GradientType.TopBottom, AntiAliasing.None);
Series series = null;
DundasAnimations.DundasChartBase(chart, AnimationTheme.None, -1, -1, false, 1);
for (int i = 0; i < ds_jikgub.Tables[0].Rows.Count; i++)
{
series = DundasCharts.CreateSeries(chart, "Series" + i.ToString(), "Default", ds_jikgub.Tables[0].Rows[i]["JIKGUB_NM"].ToString(), null, SeriesChartType.StackedColumn, 1, GetChartColor(i), Color.FromArgb(0x4A, 0x58, 0x7E), Color.FromArgb(64, 0, 0, 0), 1, 9, Color.FromArgb(64, 64, 64));
//series.ValueMembersY = "C_" + ds_jikgub.Tables[0].Rows[i]["JIKGUB_NM"].ToString();
DatabaseBindingXY(series, "TEAM_NM", "C_" + ds_jikgub.Tables[0].Rows[i]["JIKGUB_NM"].ToString(), dt);
series["DrawingStyle"] = "Cylinder";
DundasAnimations.FadingAnimation(chart, series, 0.5 * i, 0.5 * (i + 1), false, false);
series.ShowLabelAsValue = true;
series.ToolTip = "#VALY{N0}";
SetVisibleZeroLabelText(chart);
}
}
private string GetLastDay()
{
string sYear = PageUtility.GetByValueDropDownList(ddlYear);
string sMonth = PageUtility.GetByValueDropDownList(ddlMonth);
string sQuery = @"SELECT DISTINCT TOP 1
B.DayNumberOfMonth
FROM CA_FT_JAEGO A,
D_TIME_ILJA B,
D_ITEM_JAEGO C
WHERE B.TM_IDX=A.TIME_IDX
AND B.yyyy_mm=" + sYear + sMonth + @"
AND B.DayNumberOfMonth between 1 and 31
AND C.ITMJ_IDX=A.ITEM_IDX
ORDER BY B.DayNumberOfMonth DESC ";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataTable dtTable = dbAgent.FillDataSet(sQuery, "Data").Tables[0];
if (dtTable.Rows.Count > 0)
{
return(dtTable.Rows[0][0].ToString());
}
else
{
return("1");
}
}
// 이승주 추가 (재고 파이 차트)
private DataTable GetChartDataTable(string yearStr, string gongJangType)
{
string query = GetPieQuery(yearStr, gongJangType);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
return(dbAgent.FillDataSet(query, "Data").Tables[0]);
}
protected DataSet GetDataSet(string query)
{
DBAgent dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
return(ds);
}
private void GridBinding(string type)
{
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(GetQuery(type), "Data");
UltraWebGrid1.DataSource = ds;
UltraWebGrid1.DataBind();
}
private DataTable GetGridDataTable(string yearStr, string monthStr, string type)
{
string query = GetQuery(yearStr, monthStr, type);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
return(ds.Tables[0]);
}
//private void SetDropDownListItem(System.Web.UI.WebControls.DropDownList ddlItem)
//{
// string query = @"SELECT DISTINCT ITMU_IDX, ITMU_NAME FROM D_ITEM_UTIL";
// dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
// DataSet ds = dbAgent.FillDataSet(query, "Data");
// ddlItem.DataSource = ds;
// ddlItem.DataValueField = "ITMU_IDX";
// ddlItem.DataTextField = "ITMU_NAME";
// ddlItem.DataBind();
//}
private void BindingChart_1(Dundas.Charting.WebControl.Chart chart, string yearStr)
{
string query_item = @"SELECT PAN_ITMNM from D_ITEM_PANMAE WHERE PAN_ITMNM LIKE '%락탐%' GROUP BY PAN_ITMNM";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds_item = dbAgent.FillDataSet(query_item, "Data");
//chart.DataSource = GetChartDataTable_1(yearStr).DefaultView;
//DataGrid1.DataSource = GetChartDataTable(yearStr).DefaultView;
//DataGrid1.DataBind();
DataTable dt = GetChartDataTable_1(yearStr);
DundasCharts.DundasChartBase(chart, ChartImageType.Flash, 800, 182
, BorderSkinStyle.Emboss, Color.FromArgb(181, 64, 1), 2
, Color.FromArgb(0xFF, 0xFF, 0xFE)
, Color.FromArgb(0xFF, 0xFF, 0xFE), Color.FromArgb(0x20, 0x80, 0xD0), ChartDashStyle.Solid
, -1
, ChartHatchStyle.None, GradientType.TopBottom, AntiAliasing.None);
Series series = null;
DundasAnimations.DundasChartBase(chart, AnimationTheme.None, -1, -1, false, 1);
for (int i = 0; i < ds_item.Tables[0].Rows.Count; i++)
{
series = DundasCharts.CreateSeries(chart, "Series" + i.ToString(), "Default", ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString(), null, SeriesChartType.Line, 3, GetChartColor(i), GetChartColor(i), Color.FromArgb(64, 0, 0, 0), 1, 9, Color.FromArgb(64, 64, 64));
//series.ValueMembersY = "C_" + ds_item.Tables[0].Rows[i]["PAN_IDX"].ToString();
DatabaseBindingXY(series, "MONTH", "C_" + ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString(), dt);
DundasAnimations.GrowingAnimation(chart, series, i, (i + 1), false);
if (ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString() == "용융락탐")
{
series.YAxisType = AxisType.Secondary;
}
series.MarkerStyle = GetMarkerStyle(i);
series.MarkerColor = GetChartColor(i);
series.MarkerBorderColor = GetChartColor(i);
series.ToolTip = "#VALY{N0}";
//if (ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString().IndexOf("공업용유안25Kg(Pk)") < 0)
// series.YAxisType = AxisType.Secondary;
string sChartArea = chart.Series[series.Name].ChartArea;
chart.ChartAreas[sChartArea].AxisY.LabelStyle.Format = "N0";
chart.ChartAreas[sChartArea].AxisY2.LabelStyle.Format = "N0";
//SetZeroPoint(chart, int.Parse(ddlYear.SelectedValue));
}
SetVisibleZeroPoint(chart, Convert.ToInt32(PageUtility.GetByValueDropDownList(ddlYear)));
Font font1 = new Font("Gulim", 9, FontStyle.Regular);
Legend legend = DundasCharts.CreateLegend(chart, "Default", Color.Transparent, Color.Empty, Color.Empty, font1, true, -1, -1, -1, -1);
DundasCharts.SetChartArea(chart.ChartAreas["Default"], false);
}
/// <summary>
/// GetDTChart
/// : 챠트용 데이타테이블 반환
/// </summary>
/// <returns></returns>
private DataTable GetDTChart()
{
string sQuery = GetChartQuery(); // 챠트 셋팅용 쿼리
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(sQuery, "Data");
return(ds.Tables[0]);
}
private void GridBinding(string yearStr, string monthStr, string areaStr)
{
string query = GetGridQuery(yearStr, monthStr, areaStr);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
UltraWebGrid1.DataSource = ds;
UltraWebGrid1.DataBind();
}
public void SetTypeDropDownList(System.Web.UI.WebControls.DropDownList ddList)
{
string query = @"SELECT DISTINCT OTH_COMPANY_NAME FROM SEM_GROUP_COMPANY ORDER BY OTH_COMPANY_NAME ";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
ddList.DataSource = ds;
ddList.DataTextField = "OTH_COMPANY_NAME";
ddList.DataValueField = "OTH_COMPANY_NAME";
ddList.DataBind();
}
private DataTable GetChartDataTable(string yearStr, string monthStr, string type)
{
string query = GetQuery(yearStr, type);
// string query_type = @"SELECT DISTINCT SEM_CODE3_T, SEM_CODE3_NAME FROM SEM_CODE_MASTER WHERE SEM_CODE1_T = '14' AND SEM_CODE3_T < '10006'";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("MONTH", typeof(string));
dataTable.Columns.Add("EMP_DATE_T", typeof(string));
dataTable.Columns.Add("EMP_PLAN", typeof(double));
dataTable.Columns.Add("EMP_ACTUAL", typeof(double));
dataTable.Columns.Add("EMP_ACHIVE", typeof(double));
dataTable.Columns.Add("EMP_SUM_PLAN", typeof(double));
dataTable.Columns.Add("EMP_SUM_ACTUAL", typeof(double));
dataTable.Columns.Add("EMP_SUM_ACHIVE", typeof(double));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
dr = dataTable.NewRow();
dr["MONTH"] = ds.Tables[0].Rows[i]["MONTH"].ToString();
dr["EMP_DATE_T"] = ds.Tables[0].Rows[i]["EMP_DATE_T"].ToString();
dr["EMP_PLAN"] = double.Parse(ds.Tables[0].Rows[i]["EMP_PLAN"].ToString());
dr["EMP_SUM_PLAN"] = double.Parse(ds.Tables[0].Rows[i]["EMP_SUM_PLAN"].ToString());
int rowMonth = int.Parse(ds.Tables[0].Rows[i]["MONTH"].ToString());
int month = int.Parse(monthStr);
if (rowMonth <= month)
{
dr["EMP_ACTUAL"] = double.Parse(ds.Tables[0].Rows[i]["EMP_ACTUAL"].ToString());
dr["EMP_ACHIVE"] = double.Parse(ds.Tables[0].Rows[i]["EMP_ACHIVE"].ToString());
dr["EMP_SUM_ACTUAL"] = Math.Round(double.Parse(ds.Tables[0].Rows[i]["EMP_SUM_ACTUAL"].ToString()), 1);
dr["EMP_SUM_ACHIVE"] = Math.Round(double.Parse(ds.Tables[0].Rows[i]["EMP_SUM_ACHIVE"].ToString()), 1);
}
else
{
dr["EMP_ACTUAL"] = 0;
dr["EMP_ACHIVE"] = 0;
dr["EMP_SUM_ACTUAL"] = 0;
dr["EMP_SUM_ACHIVE"] = 0;
}
dataTable.Rows.Add(dr);
}
return(dataTable);
}
//private void SetDropDownListItem(System.Web.UI.WebControls.DropDownList ddlItem)
//{
// string query = @"SELECT DISTINCT ITMU_IDX, ITMU_NAME FROM D_ITEM_UTIL";
// dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
// DataSet ds = dbAgent.FillDataSet(query, "Data");
// ddlItem.DataSource = ds;
// ddlItem.DataValueField = "ITMU_IDX";
// ddlItem.DataTextField = "ITMU_NAME";
// ddlItem.DataBind();
//}
private void BindingChart(Dundas.Charting.WebControl.Chart chart, string yearStr)
{
string query_item = @"SELECT PAN_IDX, PAN_ITMNM from D_ITEM_PANMAE WHERE PAN_ITMNM LIKE '%유안%'";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds_item = dbAgent.FillDataSet(query_item, "Data");
chart.DataSource = GetChartDataTable(yearStr).DefaultView;
//DataGrid1.DataSource = GetChartDataTable(yearStr).DefaultView;
//DataGrid1.DataBind();
DundasCharts.DundasChartBase(chart, ChartImageType.Flash, 800, 450
, BorderSkinStyle.Emboss, Color.FromArgb(181, 64, 1), 2
, Color.FromArgb(0xFF, 0xFF, 0xFE)
, Color.FromArgb(0xFF, 0xFF, 0xFE), Color.FromArgb(0x20, 0x80, 0xD0), ChartDashStyle.Solid
, -1
, ChartHatchStyle.None, GradientType.TopBottom, AntiAliasing.None);
Series series = null;
DundasAnimations.DundasChartBase(chart, AnimationTheme.None, -1, -1, false, 1);
for (int i = 0; i < ds_item.Tables[0].Rows.Count; i++)
{
series = DundasCharts.CreateSeries(chart, "Series" + i.ToString(), "Default", ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString(), null, SeriesChartType.Line, 3, GetChartColor(i), GetChartColor(i), Color.FromArgb(64, 0, 0, 0), 1, 9, Color.FromArgb(64, 64, 64));
series.ValueMembersY = "C_" + ds_item.Tables[0].Rows[i]["PAN_IDX"].ToString();
DundasAnimations.GrowingAnimation(chart, series, 2 * i, 2 * (i + 1), false);
series.MarkerStyle = GetMarkerStyle(i);
series.MarkerColor = GetChartColor(i);
series.MarkerBorderColor = GetChartColor(i);
//if (ds_item.Tables[0].Rows[i]["PAN_ITMNM"].ToString().IndexOf("공업용유안25Kg(Pk)") < 0)
// series.YAxisType = AxisType.Secondary;
if (i == 0)
{
series.ValueMemberX = "MONTH";
}
}
chart.Legends.Add(new Legend("test"));
Chart1.Legends["test"].Alignment = StringAlignment.Center;
Chart1.Legends["test"].Docking = LegendDocking.Top;
Chart1.Legends["test"].ShadowOffset = 0;
Chart1.Legends["test"].LegendStyle = LegendStyle.Row;
Chart1.Legends["test"].Font = new Font(Chart1.Legends["Default"].Font.FontFamily, 9);
Font font1 = new Font("Gulim", 9, FontStyle.Regular);
Legend legend = DundasCharts.CreateLegend(Chart1, "Default", Color.Transparent, Color.Empty, Color.Empty, font1, true, -1, -1, -1, -1);
DundasCharts.SetChartArea(Chart1.ChartAreas["Default"], false);
}
private void setGridGrph()
{
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
string strSQL = "SELECT DISTINCT EMP_CODE_T, EMP_CODE_NAME FROM SEM_ORGANIZATION_PERSON WHERE EMP_GUBN_T = '" + ddlStyle.SelectedValue + "' ORDER BY EMP_CODE_T";
DataSet dsCode = dbAgent.FillDataSet(strSQL, "tblCD");
string strQN = "\"";
string strHD = "";
string strDT = "";
string strCD = "";
string strNM = "";
int intRow = dsCode.Tables[0].Rows.Count;
int intCol = dsCode.Tables[0].Columns.Count;
for (int i = 0; i < intRow; i++)
{
strCD = dsCode.Tables[0].Rows[i][0].ToString();
strNM = dsCode.Tables[0].Rows[i][1].ToString();
if (i == (intRow - 1))
{
strHD += "SUM(B.COL_" + i.ToString() + ") as " + strQN + strNM + strQN;
strDT += "DECODE(A.GBN_CD,'" + strCD + "',A.EMP_CNT,0) as COL_" + i.ToString();
}
else
{
strHD += "SUM(B.COL_" + i.ToString() + ") as " + strQN + strNM + strQN + ",";
strDT += "DECODE(A.GBN_CD,'" + strCD + "',A.EMP_CNT,0) as COL_" + i.ToString() + ",";
}
}
strSQL = setGridQuery(strHD, strDT);
DataSet dsGrid = dbAgent.FillDataSet(strSQL, "tblGrid");
getSumRowCol(dsGrid);
dbAgent = null;
}
private void SetDropDownListItem(System.Web.UI.WebControls.DropDownList ddlItem)
{
string query = @"SELECT DISTINCT ITMU_IDX, ITMU_NAME FROM D_ITEM_UTIL";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
ddlItem.DataSource = ds;
ddlItem.DataValueField = "ITMU_IDX";
ddlItem.DataTextField = "ITMU_NAME";
ddlItem.DataBind();
ddlItem.Items.Insert(0, new ListItem("전체", "0"));
}
private void SetDropDownListGongJang(System.Web.UI.WebControls.DropDownList ddlGongJang)
{
string query = @"SELECT DISTINCT GO_IDX, GONGJANG FROM D_GONGJANG";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
ddlGongJang.DataSource = ds;
ddlGongJang.DataValueField = "GO_IDX";
ddlGongJang.DataTextField = "GONGJANG";
ddlGongJang.DataBind();
ddlGongJang.Items.Insert(0, new ListItem("전체", "0"));
}
public void SetTypeDropDownList(System.Web.UI.WebControls.DropDownList ddList)
{
string query = @"SELECT SEM_CODE2_T, SEM_CODE2_NAME FROM SEM_CODE_MASTER WHERE SEM_CODE1_T = '15'";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
ddList.DataSource = ds;
ddList.DataTextField = "SEM_CODE2_NAME";
ddList.DataValueField = "SEM_CODE2_T";
ddList.DataBind();
//ddList.Items.Insert(0, new ListItem("전체", "*"));
}
public void SetType1DropDownList()
{
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet dsBumun = dbAgent.FillDataSet(GetBumunQuery(), "data");
ddlLocation1.Items.Insert(0, new ListItem("==전체==", ""));
ddlLocation1.Items.Insert(1, new ListItem("울산", "01"));
ddlLocation1.Items.Insert(2, new ListItem("양산", "02"));
ddlLocation2.DataSource = dsBumun;
ddlLocation2.DataTextField = "ORG_NM";
ddlLocation2.DataValueField = "ORG_CD";
ddlLocation2.DataBind();
ddlLocation2.Items.Insert(0, new ListItem("==부문 선택==", ""));
dbAgent = null;
}
private DataTable GetDataTable(string yearStr)
{
string query1 = GetChartQuery(yearStr);
int startYear = int.Parse(yearStr) - 4;
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds1 = dbAgent.FillDataSet(query1, "Data");
DataTable gridTable = new DataTable();
DataRow dr = null;
gridTable.Columns.Add("T_DATE", typeof(string));
gridTable.Columns.Add("T_10000", typeof(double));
gridTable.Columns.Add("T_20000", typeof(double));
gridTable.Columns.Add("T_30000", typeof(double));
gridTable.Columns.Add("T_40000", typeof(double));
for (int i = startYear; i < startYear + 5; i++)
{
dr = gridTable.NewRow();
dr["T_DATE"] = i.ToString();
dr["T_10000"] = 0;
dr["T_20000"] = 0;
dr["T_30000"] = 0;
dr["T_40000"] = 0;
gridTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 0; i < gridTable.Rows.Count; i++)
{
for (int j = 10000; j < 50000; j += 10000)
{
drCol = ds1.Tables[0].Select("T_DATE = '" + gridTable.Rows[i]["T_DATE"].ToString()
+ "' AND T_GUBN = '" + j.ToString() + "'");
if (drCol.Length > 0)
{
gridTable.Rows[i]["T_" + j.ToString()] = Convert.ToDouble(drCol[0]["DATA"].ToString());
}
}
}
return(gridTable);
}
public void setAcctCD()
{
string query = @"
SELECT DISTINCT
FIN.SEM_ACCOUNT1_CODE, -- 손익계정 코드
FIN.SEM_ACCOUNT1_DESC -- 손익계정 명(Account1 Level)
FROM SEM_FINANCIAL_CODE_MASTER FIN
WHERE FIN.SEM_FINANCIAL_CODE = 'PL'
ORDER BY FIN.SEM_ACCOUNT1_CODE";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
this.cboActCd.DataSource = ds;
this.cboActCd.DataTextField = "SEM_ACCOUNT1_DESC";
this.cboActCd.DataValueField = "SEM_ACCOUNT1_CODE";
this.cboActCd.DataBind();
}
public void SetTypeDropDownList(System.Web.UI.WebControls.DropDownList ddList)
{
string query = @"
SELECT BS_CODE.SEM_ACCOUNT4_CODE,
BS_CODE.SEM_ACCOUNT4_DESC
FROM SEM_FINANCIAL_CODE_MASTER BS_CODE
WHERE BS_CODE.SEM_FINANCIAL_CODE = 'BS'
AND BS_CODE.SEM_ACCOUNT3_CODE IN ('1303000000','1305000000')";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
ddList.DataSource = ds;
ddList.DataTextField = "SEM_ACCOUNT4_DESC";
ddList.DataValueField = "SEM_ACCOUNT4_CODE";
ddList.DataBind();
//ddList.Items.Insert(0, new ListItem("전체", "*"));
}
//private void GridBinding(string yearStr, string monthStr, string type)
//{
// UltraWebGrid1.DataSource = GetGridDataTable(yearStr, monthStr, type);
// UltraWebGrid1.DataBind();
//}
//private DataTable GetGridDataTable(string yearStr, string monthStr, string type)
//{
// string query = GetQuery(yearStr, monthStr, type);
// dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
// DataSet ds = dbAgent.FillDataSet(query, "Data");
// return ds.Tables[0];
//}
private DataTable GetChartDataTable(string yearStr, string monthStr, string areaStr, string type)
{
string query = GetQuery(yearStr, monthStr, areaStr, type);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("MONTH", typeof(string));
dataTable.Columns.Add("MONTH_NAME", typeof(string));
dataTable.Columns.Add("BUDGET", typeof(double));
dataTable.Columns.Add("ACTUAL", typeof(double));
dataTable.Columns.Add("RATE", typeof(double));
for (int i = 1; i <= 12; i++)
{
dr = dataTable.NewRow();
dr["MONTH"] = i.ToString();
dr["MONTH_NAME"] = i.ToString() + "월";
dr["BUDGET"] = 0;
dr["ACTUAL"] = 0;
dr["RATE"] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
drCol = ds.Tables[0].Select("C_MONTH = '" + i.ToString().PadLeft(2, '0') + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i]["BUDGET"] = double.Parse(drCol[0]["BUDGET"].ToString());
dataTable.Rows[i]["ACTUAL"] = double.Parse(drCol[0]["ACTUAL"].ToString());
dataTable.Rows[i]["RATE"] = double.Parse(drCol[0]["RATE"].ToString());
}
}
return(dataTable);
}
private DataTable GetChartDataTable(string yearStr, string util, string sGongjang)
{
string query = GetChartQuery(yearStr, util, sGongjang);
//string query_item = @"SELECT DISTINCT ITMU_IDX, ITMU_NAME FROM D_ITEM_UTIL";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
//DataSet ds_item = dbAgent.FillDataSet(query_item, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("MONTH", typeof(string));
dataTable.Columns.Add("USEQTY", typeof(double));
dataTable.Columns.Add("AMT3", typeof(double));
for (int j = 1; j <= 12; j++)
{
dr = dataTable.NewRow();
dr["MONTH"] = j.ToString();
dr["USEQTY"] = 0;
dr["AMT3"] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
drCol = ds.Tables[0].Select("yyyy_mm = '" + yearStr + i.ToString().PadLeft(2, '0') + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i - 1]["USEQTY"] = Convert.ToDouble(drCol[0]["USEQTY"].ToString());
dataTable.Rows[i - 1]["AMT3"] = Convert.ToDouble(drCol[0]["AMT3"].ToString());
}
}
return(dataTable);
}
private void ChartBinding()
{
string sYear0, sYear1, sYear2; // 3년에 대한 년도 (0=현재, 1=현재-1, 2=현재-2)
ArrayList al3YearMon = GetALYearMon(out sYear0, out sYear1, out sYear2);
// 전체 구분 추출쿼리
string sQueryGubun = "";
sQueryGubun += "SELECT a.SEM_CODE2_T , -- 매출구분 코드(용도) \n";
sQueryGubun += " a.SEM_CODE2_NAME -- 매출구분 명 \n";
sQueryGubun += " FROM SEM_CODE_MASTER a, -- 코드 Master Table \n";
sQueryGubun += " SEM_DEMAND_DEVELOP b -- 월 수요개발 현황 \n";
sQueryGubun += " WHERE a.SEM_CODE1_T = '06' \n";
sQueryGubun += " AND b.DEVE_GUBN_CODE_T = a.SEM_CODE3_T \n";
sQueryGubun += " AND (b.DEVE_DATE_T BETWEEN '" + sYear2 + "01' AND '" + sYear0 + "12') \n";
sQueryGubun += " GROUP BY \n";
sQueryGubun += " a.SEM_CODE2_T, \n";
sQueryGubun += " a.SEM_CODE2_NAME \n";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataTable dtGubun = dbAgent.FillDataSet(sQueryGubun, "Data").Tables[0];
DataTable dataTable = GetDTChart();
#region 챠트 초기화
DundasCharts.DundasChartBase(Chart1, ChartImageType.Flash, 800, 300
, BorderSkinStyle.Emboss, Color.FromArgb(181, 64, 1), 2
, Color.FromArgb(0xFF, 0xFF, 0xFE)
, Color.FromArgb(0xFF, 0xFF, 0xFE), Color.FromArgb(0x20, 0x80, 0xD0), ChartDashStyle.Solid
, -1
, ChartHatchStyle.None, GradientType.TopBottom, AntiAliasing.None);
#endregion
#region 챠트1 셋팅 START
SetLineChart(dataTable);
#endregion 챠트1 셋팅 END
}
private DataTable GetChartDataTableOneYear(string yearStr, string monthStr, string type, string gubn)
{
string query = GetQueryOneYear(yearStr, monthStr, type, gubn);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("MONTH", typeof(string));
dataTable.Columns.Add("DIFF_ADJUST_QNT", typeof(double));
dataTable.Columns.Add("RATE_ADJUST", typeof(double));
for (int i = 1; i <= 12; i++)
{
dr = dataTable.NewRow();
dr["MONTH"] = i.ToString();
dr["DIFF_ADJUST_QNT"] = 0;
dr["RATE_ADJUST"] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
drCol = ds.Tables[0].Select("C_MONTH = '" + i.ToString().PadLeft(2, '0') + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i - 1]["DIFF_ADJUST_QNT"] = Convert.ToDouble(drCol[0]["DIFF_ADJUST_QNT"].ToString());
dataTable.Rows[i - 1]["RATE_ADJUST"] = Convert.ToDouble(drCol[0]["RATE_ADJUST"].ToString());
}
}
return(dataTable);
}
private void GridBinding()
{
string sYYYYMMs = GetByValueDropDownList(ddlYearS) + GetByValueDropDownList(ddlMonthS);
string sYYYYMMe = GetByValueDropDownList(ddlYearE) + GetByValueDropDownList(ddlMonthE);
string[] saTerm = GetMonthDiff(sYYYYMMs, sYYYYMMe, "M");
string sOffice = GetByValueDropDownList(ddlOffice);
if (!(saTerm.Length >= 1 && saTerm.Length <= 12))
{
AlertMessage("[12개월] 이내에서만 조회 가능합니다!");
return;
}
string sQuery = GetGridQuery(sYYYYMMs, sYYYYMMe, sOffice);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(sQuery, "Data");
UltraWebGrid1.Bands[0].ResetColumns();
UltraWebGrid1.DataSource = ds;
UltraWebGrid1.DataBind();
}