private DataTable GetDTItems(string asItem)
{
string sQuery = "";
sQuery += "SELECT DISTINCT \n";
sQuery += " ITMJ_CD, \n";
sQuery += " ITMJ_NAME \n";
sQuery += " FROM D_ITEM_JAEGO \n";
sQuery += " WHERE 1=1 \n";
if (asItem != "")
{
sQuery += " AND ITMJ_CD = '" + asItem + "' \n";
}
DataTable dtRet = new DataTable();
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsItems = gDbAgentEIS.Fill(sQuery);
if (dsItems.Tables.Count > 0)
{
dtRet = dsItems.Tables[0];
}
return(dtRet);
}
// 챠트용 데이타테이블 반환
private DataTable GetDTChart()
{
string sYear = PageUtility.GetByValueDropDownList(ddlYear);
string sMonth = PageUtility.GetByValueDropDownList(ddlMonth);
string sItem = PageUtility.GetByValueDropDownList(ddlItem);
string[] saDayTerm = TypeUtility.GetDateDiff(sYear + sMonth, sYear + sMonth, true); // 1달동안의 날짜
DataTable dtItems = GetDTItems(sItem); // 선택된 아이템
double dTotal = 0;
double dTmp = 0;
DataTable dataTable = new DataTable();
DataRow drNew = null;
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsTable = gDbAgentEIS.Fill(GetDefaultQuery());
DataTable dtTable = dsTable.Tables[0];
DataRow[] draDefault = null;
dataTable.Columns.Add("YMD", typeof(string));
for (int i = 0; i < dtItems.Rows.Count; i++)
{
dataTable.Columns.Add(dtItems.Rows[i]["ITMJ_CD"] + "_QTY", typeof(double));
}
for (int i = 0; i < saDayTerm.Length; i++)
{
drNew = dataTable.NewRow();
drNew["YMD"] = saDayTerm[i].Substring(6, 2);
for (int j = 0; j < dtItems.Rows.Count; j++)
{
draDefault = dtTable.Select(
"v_YMD = '" + saDayTerm[i] + "' "
+ "AND ITMJ_CD = '" + dtItems.Rows[j]["ITMJ_CD"] + "' "
);
dTmp = 0;
for (int k = 0; k < draDefault.Length; k++)
{
dTmp += Convert.ToDouble(draDefault[k]["v_JgoQty"]);
}
drNew[dtItems.Rows[j]["ITMJ_CD"] + "_QTY"] = dTmp;
}
dataTable.Rows.Add(drNew);
}
return(dataTable);
}
// 챠트용 데이타테이블 반환
private DataTable GetDTChart()
{
string sYear = PageUtility.GetByValueDropDownList(ddlYear);
string sMonth = PageUtility.GetByValueDropDownList(ddlMonth);
string[] saDayTerm = TypeUtility.GetDateDiff(sYear + sMonth, sYear + sMonth, true); // 1달동안의 날짜
double dTmp1 = 0;
double dTmp2 = 0;
double dTmp3 = 0;
DataTable dataTable = new DataTable();
DataRow drNew = null;
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsTable = gDbAgentEIS.Fill(GetDefaultQuery());
DataTable dtTable = dsTable.Tables[0];
DataRow[] draDefault = null;
dataTable.Columns.Add("YMD", typeof(string));
dataTable.Columns.Add("ProdQty", typeof(double));
dataTable.Columns.Add("Rate", typeof(double));
dataTable.Columns.Add("Buha", typeof(double));
for (int i = 0; i < saDayTerm.Length; i++)
{
drNew = dataTable.NewRow();
drNew["YMD"] = saDayTerm[i].Substring(6, 2);
draDefault = dtTable.Select(
"v_YMD = '" + saDayTerm[i] + "' "
);
dTmp1 = 0;
dTmp2 = 0;
dTmp3 = 0;
for (int k = 0; k < draDefault.Length; k++)
{
dTmp1 += Convert.ToDouble(draDefault[k]["v_ProdQty"]);
dTmp2 += Convert.ToDouble(draDefault[k]["v_Buha"]);
dTmp3 += Convert.ToDouble(draDefault[k]["v_Rate"]);
}
drNew["ProdQty"] = Math.Round(dTmp1, 0);
drNew["Buha"] = Math.Round(dTmp2, 0);
drNew["Rate"] = Math.Round(dTmp3, 0);
dataTable.Rows.Add(drNew);
}
return(dataTable);
}
// 챠트용 데이타테이블 반환
private DataTable GetDTChart()
{
string sYear = PageUtility.GetByValueDropDownList(ddlYear);
string[] saMonthTerm = GetMonthTerm(sYear); // 년초부터 년말까지 년월배열
double dTmp1 = 0;
DataTable dataTable = new DataTable();
DataRow drNew = null;
DataTable dtBond = GetDTBond(false); // 전체계정 추출
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsTable = gDbAgentEIS.Fill(GetDefaultQuery());
DataTable dtTable = dsTable.Tables[0];
DataRow[] draDefault = null;
dataTable.Columns.Add("YYYYMM", typeof(string));
for (int i = 0; i < dtBond.Rows.Count; i++)
{
dataTable.Columns.Add(dtBond.Rows[i]["v_Code"].ToString(), typeof(double));
}
for (int i = 0; i < saMonthTerm.Length; i++)
{
drNew = dataTable.NewRow();
drNew["YYYYMM"] = Convert.ToInt32(saMonthTerm[i].Substring(4, 2)).ToString() + "월";
for (int j = 0; j < dtBond.Rows.Count; j++)
{
draDefault = dtTable.Select(
"yyyy_mm = '" + saMonthTerm[i] + "' "
+ "AND AC_IDX = " + dtBond.Rows[j]["v_Code"].ToString() + " "
);
dTmp1 = 0;
for (int k = 0; k < draDefault.Length; k++)
{
dTmp1 += Convert.ToDouble(draDefault[k]["JAN_AMT"]);
}
drNew[dtBond.Rows[j]["v_Code"].ToString()] = dTmp1;
}
dataTable.Rows.Add(drNew);
}
return(dataTable);
}
private DataTable GetDTGongJang()
{
string sQuery = "";
sQuery += "select GO_IDX v_Code, \n";
sQuery += " GONGJANG v_Name \n";
sQuery += " from D_GONGJANG \n";
sQuery += "UNION ALL \n";
sQuery += "SELECT '' v_Code, \n";
sQuery += " '전체' v_Name \n";
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsGong = gDbAgentEIS.Fill(sQuery);
return(dsGong.Tables[0]);
}
/// <summary>
/// GetDTVender
/// : 업체별 테이블 리턴
/// </summary>
/// <param name="asVndCode"></param>
/// <returns></returns>
private DataTable GetDTVendor(string asVndCode)
{
string sQuery = "";
sQuery += "SELECT DISTINCT \n";
sQuery += " CONVERT(VARCHAR, VNDR_IDX) v_Code, \n";
sQuery += " VNDR_NAME v_Name \n";
sQuery += " FROM D_VENDER \n";
sQuery += " WHERE 1=1 \n";
if (asVndCode != "")
{
sQuery += " AND VNDR_IDX = '" + asVndCode + "' \n";
}
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsVendor = gDbAgentEIS.Fill(sQuery);;
return(dsVendor.Tables[0]);
}
private DataTable GetDTBond(string asCode)
{
string sQuery = "";
sQuery += "SELECT DISTINCT \n";
sQuery += " CONVERT(VARCHAR,AC_IDX) v_Code, \n";
sQuery += " AC_ACNT_NM v_Name \n";
sQuery += " FROM D_ACOUNT \n";
sQuery += " WHERE 1=1 \n";
if (asCode != "")
{
sQuery += " AND AC_IDX = " + asCode + " \n";
}
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsBond = gDbAgentEIS.Fill(sQuery);
return(dsBond.Tables[0]);
}
/// <summary>
/// GetDTBond
/// : 채권종류 테이블리턴
/// </summary>
/// <returns></returns>
private DataTable GetDTBond(bool bAddAll)
{
string sQuery = "";
if (bAddAll)
{
sQuery += "SELECT '' v_Code, \n";
sQuery += " '전체' v_Name \n";
sQuery += "UNION ALL \n";
}
sQuery += "SELECT DISTINCT \n";
sQuery += " CONVERT(VARCHAR,AC_IDX) v_Code, \n";
sQuery += " AC_ACNT_NM v_Name \n";
sQuery += " FROM D_ACOUNT \n";
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsBond = gDbAgentEIS.Fill(sQuery);
return(dsBond.Tables[0]);
}
/// <summary>
/// GetDTItems
/// : 조회조건용 제품 추출
/// </summary>
/// <returns></returns>
private DataTable GetDTItems()
{
string sQuery = "";
sQuery += "SELECT '' ITMJ_CD, \n";
sQuery += " '전체' ITMJ_NAME \n";
sQuery += "UNION ALL \n";
sQuery += "SELECT DISTINCT \n";
sQuery += " ITMJ_CD, \n";
sQuery += " ITMJ_NAME \n";
sQuery += " FROM D_ITEM_JAEGO \n";
DataTable dtRet = new DataTable();
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsItems = gDbAgentEIS.Fill(sQuery);
if (dsItems.Tables.Count > 0)
{
dtRet = dsItems.Tables[0];
}
return(dtRet);
}
// 챠트용 데이타테이블 반환
private DataTable GetDTChart2()
{
DataTable dtGong = GetDTGongJang(); // X축이 되는 공장정보 테이블
double dTmp1 = 0;
double dTmp2 = 0;
double dTmp3 = 0;
double dTmp4 = 0;
double dTmp5 = 0;
DataTable dataTable = new DataTable();
DataRow drNew = null;
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsTable = gDbAgentEIS.Fill(GetChartQuery2());
DataTable dtTable = dsTable.Tables[0];
DataRow[] draDefault = null;
dataTable.Columns.Add("GongJang", typeof(string));
dataTable.Columns.Add("Plan", typeof(double));
dataTable.Columns.Add("Prod", typeof(double));
dataTable.Columns.Add("PrevYear", typeof(double));
dataTable.Columns.Add("Sum", typeof(double));
dataTable.Columns.Add("Rate", typeof(double));
for (int i = 0; i < dtGong.Rows.Count; i++)
{
drNew = dataTable.NewRow();
drNew["GongJang"] = dtGong.Rows[i]["v_Name"];
if (dtGong.Rows[i]["v_Name"].ToString() != "전체")
{
draDefault = dtTable.Select(
"v_GongCD = '" + dtGong.Rows[i]["v_Code"].ToString() + "' "
);
}
else
{
draDefault = dtTable.Select();
}
dTmp1 = 0;
dTmp2 = 0;
dTmp3 = 0;
dTmp4 = 0;
dTmp5 = 0;
for (int j = 0; j < draDefault.Length; j++)
{
dTmp1 += Convert.ToDouble(draDefault[j]["v_PlanQty"]);
dTmp2 += Convert.ToDouble(draDefault[j]["v_ProdQty"]);
dTmp3 += Convert.ToDouble(draDefault[j]["v_Buha"]);
dTmp4 += Convert.ToDouble(draDefault[j]["v_PrevYearSub"]);
dTmp5 += Convert.ToDouble(draDefault[j]["v_PlanProdSubSum"]);
}
drNew["Plan"] = dTmp1;
drNew["Prod"] = dTmp2;
drNew["Rate"] = dTmp3;
drNew["PrevYear"] = dTmp4;
drNew["Sum"] = dTmp5;
dataTable.Rows.Add(drNew);
}
return(dataTable);
}
// 그리드용 데이타테이블 반환
private DataTable GetDTGrid()
{
string sYear = PageUtility.GetByValueDropDownList(ddlYear);
string sMonth = PageUtility.GetByValueDropDownList(ddlMonth);
string[] saDayTerm = TypeUtility.GetDateDiff(sYear + sMonth, sYear + sMonth, true); // 1달동안의 날짜
double dTmp = 0;
DataTable dataTable = new DataTable();
DataRow drNew = null;
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsTable = gDbAgentEIS.Fill(GetDefaultQuery());
DataTable dtTable = dsTable.Tables[0];
DataRow[] draDefault = null;
dataTable.Columns.Add("구분", typeof(string));
for (int i = 0; i < saDayTerm.Length; i++)
{
dataTable.Columns.Add(saDayTerm[i].Substring(6, 2), typeof(double));
}
// 생산량 (ProdQty)
drNew = dataTable.NewRow();
drNew["구분"] = "생산량";
for (int i = 0; i < saDayTerm.Length; i++)
{
draDefault = dtTable.Select(
"v_YMD = '" + saDayTerm[i] + "' "
);
dTmp = 0;
for (int k = 0; k < draDefault.Length; k++)
{
dTmp += Convert.ToDouble(draDefault[k]["v_ProdQty"]);
}
drNew[saDayTerm[i].Substring(6, 2)] = Math.Round(dTmp, 0);
}
dataTable.Rows.Add(drNew);
// 계획대비일간실적 (Rate)
drNew = dataTable.NewRow();
drNew["구분"] = "계획대비일간실적";
for (int i = 0; i < saDayTerm.Length; i++)
{
draDefault = dtTable.Select(
"v_YMD = '" + saDayTerm[i] + "' "
);
dTmp = 0;
for (int k = 0; k < draDefault.Length; k++)
{
dTmp += Convert.ToDouble(draDefault[k]["v_Rate"]);
}
drNew[saDayTerm[i].Substring(6, 2)] = Math.Round(dTmp, 0);
}
dataTable.Rows.Add(drNew);
// 부하율 (Buha)
drNew = dataTable.NewRow();
drNew["구분"] = "부하율";
for (int i = 0; i < saDayTerm.Length; i++)
{
draDefault = dtTable.Select(
"v_YMD = '" + saDayTerm[i] + "' "
);
dTmp = 0;
for (int k = 0; k < draDefault.Length; k++)
{
dTmp += Convert.ToDouble(draDefault[k]["v_Buha"]);
}
drNew[saDayTerm[i].Substring(6, 2)] = Math.Round(dTmp, 0);
}
dataTable.Rows.Add(drNew);
return(dataTable);
}
// 그리드용 데이타테이블 반환
private DataTable GetDTGrid()
{
string sYear = PageUtility.GetByValueDropDownList(ddlYear);
string[] saMonthTerm = GetMonthTerm(sYear); // 년초부터 년말까지 년월배열
DataTable dtBond = GetDTBond();
DataTable dtVendor = GetDTVendor(PageUtility.GetByValueDropDownList(ddlVendor));
MicroBSC.Data.DBAgent gDbAgentEIS = new MicroBSC.Data.DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet dsDefault = gDbAgentEIS.Fill(GetGridQuery());
DataTable dtDefault = null;
DataRow[] draDefault = null;
DataTable dataTable = new DataTable();
DataRow drNew = null;
dataTable.Columns.Add("업체", typeof(string));
dataTable.Columns.Add("월", typeof(string));
dataTable.Columns.Add("계정", typeof(string));
dataTable.Columns.Add("이월금액", typeof(double));
dataTable.Columns.Add("판매액", typeof(double));
dataTable.Columns.Add("수금액", typeof(double));
dataTable.Columns.Add("수금잔액", typeof(double));
if (dsDefault.Tables.Count > 0)
{
dtDefault = dsDefault.Tables[0];
for (int i = 0; i <= saMonthTerm.GetUpperBound(0); i++) // 년월
{
for (int j = 0; j < dtVendor.Rows.Count; j++) // 업체
{
for (int k = 0; k < dtBond.Rows.Count; k++) // 계정
{
drNew = dataTable.NewRow();
drNew["업체"] = dtVendor.Rows[j]["v_Name"].ToString();
drNew["월"] = Convert.ToInt32(saMonthTerm[i].Substring(4, 2)).ToString() + "월";
drNew["계정"] = dtBond.Rows[k]["v_Name"].ToString();
draDefault = dtDefault.Select(
"yyyy_mm = '" + saMonthTerm[i] + "' "
+ "AND VNDR_IDX = " + dtVendor.Rows[j]["v_Code"].ToString() + " "
+ "AND AC_IDX = " + dtBond.Rows[k]["v_Code"].ToString() + " "
);
if (draDefault.Length > 0)
{
drNew["이월금액"] = Convert.ToDouble(draDefault[0]["IWOL_AMT"]);
drNew["판매액"] = Convert.ToDouble(draDefault[0]["MAE_AMT"]);
drNew["수금액"] = Convert.ToDouble(draDefault[0]["SUG_AMT"]);
drNew["수금잔액"] = Convert.ToDouble(draDefault[0]["JAN_AMT"]);
}
else
{
drNew["이월금액"] = 0;
drNew["판매액"] = 0;
drNew["수금액"] = 0;
drNew["수금잔액"] = 0;
}
dataTable.Rows.Add(drNew);
}
}
}
}
return(dataTable);
}
