private DataTable GetChartDataTableThreeYear(string yearStr, string monthStr, string type, string gubn)
{
string query = GetQueryThreeYear(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_TOTL_QNT" + Convert.ToString(int.Parse(yearStr) - 0), typeof(string));
dataTable.Columns.Add("DIFF_SALE_QNT" + Convert.ToString(int.Parse(yearStr) - 0), typeof(string));
dataTable.Columns.Add("DIFF_ADJUST_QNT" + Convert.ToString(int.Parse(yearStr) - 0), typeof(string));
dataTable.Columns.Add("RATE_ADJUST" + Convert.ToString(int.Parse(yearStr) - 0), typeof(double));
dataTable.Columns.Add("DIFF_TOTL_QNT" + Convert.ToString(int.Parse(yearStr) - 1), typeof(string));
dataTable.Columns.Add("DIFF_SALE_QNT" + Convert.ToString(int.Parse(yearStr) - 1), typeof(string));
dataTable.Columns.Add("DIFF_ADJUST_QNT" + Convert.ToString(int.Parse(yearStr) - 1), typeof(string));
dataTable.Columns.Add("RATE_ADJUST" + Convert.ToString(int.Parse(yearStr) - 1), typeof(double));
dataTable.Columns.Add("DIFF_TOTL_QNT" + Convert.ToString(int.Parse(yearStr) - 2), typeof(string));
dataTable.Columns.Add("DIFF_SALE_QNT" + Convert.ToString(int.Parse(yearStr) - 2), typeof(string));
dataTable.Columns.Add("DIFF_ADJUST_QNT" + Convert.ToString(int.Parse(yearStr) - 2), typeof(string));
dataTable.Columns.Add("RATE_ADJUST" + Convert.ToString(int.Parse(yearStr) - 2), typeof(double));
for (int i = 1; i <= 12; i++)
{
dr = dataTable.NewRow();
dr["MONTH"] = i.ToString();
dr["DIFF_TOTL_QNT" + Convert.ToString(int.Parse(yearStr) - 0)] = 0;
dr["DIFF_SALE_QNT" + Convert.ToString(int.Parse(yearStr) - 0)] = 0;
dr["DIFF_ADJUST_QNT" + Convert.ToString(int.Parse(yearStr) - 0)] = 0;
dr["RATE_ADJUST" + Convert.ToString(int.Parse(yearStr) - 0)] = 0;
dr["DIFF_TOTL_QNT" + Convert.ToString(int.Parse(yearStr) - 1)] = 0;
dr["DIFF_SALE_QNT" + Convert.ToString(int.Parse(yearStr) - 1)] = 0;
dr["DIFF_ADJUST_QNT" + Convert.ToString(int.Parse(yearStr) - 1)] = 0;
dr["RATE_ADJUST" + Convert.ToString(int.Parse(yearStr) - 1)] = 0;
dr["DIFF_TOTL_QNT" + Convert.ToString(int.Parse(yearStr) - 2)] = 0;
dr["DIFF_SALE_QNT" + Convert.ToString(int.Parse(yearStr) - 2)] = 0;
dr["DIFF_ADJUST_QNT" + Convert.ToString(int.Parse(yearStr) - 2)] = 0;
dr["RATE_ADJUST" + Convert.ToString(int.Parse(yearStr) - 2)] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
for (int j = int.Parse(yearStr); j >= int.Parse(yearStr) - 2; j--)
{
drCol = ds.Tables[0].Select("C_MONTH = '" + i.ToString().PadLeft(2, '0') + "' AND C_YEAR = '" + j.ToString() + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i - 1]["DIFF_TOTL_QNT" + Convert.ToString(j)] = Convert.ToDouble(drCol[0]["DIFF_TOTL_QNT"].ToString());
dataTable.Rows[i - 1]["DIFF_SALE_QNT" + Convert.ToString(j)] = Convert.ToDouble(drCol[0]["DIFF_SALE_QNT"].ToString());
dataTable.Rows[i - 1]["DIFF_ADJUST_QNT" + Convert.ToString(j)] = Convert.ToDouble(drCol[0]["DIFF_ADJUST_QNT"].ToString());
dataTable.Rows[i - 1]["RATE_ADJUST" + Convert.ToString(j)] = Convert.ToDouble(drCol[0]["RATE_ADJUST"].ToString());
}
}
}
return(dataTable);
}
public PaginationResult <UserDto> ListUserPagination(PaginationInfo pagInfo, string searchString)
{
return(DBAgent.SQLExecuteReturnPaginationList <UserDto>(
InitSQLParam(pagInfo, new { searchString = searchString }, XMLHelper.GetNodeString("User", "SQL/ListUserPagination"))));
}
// 그리드용 데이타테이블 반환
private DataTable GetDTGrid()
{
DataTable dataTable = new DataTable();
string query_jikgub = GetJikGubQuery();
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["EISDB"].ConnectionString);
DataSet ds_jikgub = dbAgent.FillDataSet(query_jikgub, "Data");
DataTable dt_jikgub = ds_jikgub.Tables[0];
DataTable dtTable = GetChartDataTable("");
DataRow drNew = null;
DataRow[] draDefault = null;
dataTable.Columns.Add("구분", typeof(string));
for (int i = 0; i < dt_jikgub.Rows.Count; i++)
{
dataTable.Columns.Add(dt_jikgub.Rows[i]["JIKGUB_NM"].ToString(), typeof(int));
}
dataTable.Columns.Add("총원", typeof(int));
int iTmp = 0;
int iSum = 0;
drNew = dataTable.NewRow();
drNew["구분"] = "전사";
for (int i = 0; i < dt_jikgub.Rows.Count; i++)
{
draDefault = dtTable.Select();
iTmp = 0;
for (int k = 0; k < draDefault.Length; k++)
{
iTmp += Convert.ToInt32(draDefault[k][i + 1]);
}
drNew[i + 1] = iTmp;
iSum += iTmp;
}
drNew["총원"] = iSum;
dataTable.Rows.Add(drNew);
int iTmp1 = 0;
int iSum1 = 0;
drNew = dataTable.NewRow();
drNew["구분"] = "본사";
for (int i = 0; i < dt_jikgub.Rows.Count; i++)
{
draDefault = dtTable.Select(
"TEAM_NM LIKE '본사%'"
);
iTmp1 = 0;
for (int k = 0; k < draDefault.Length; k++)
{
iTmp1 += Convert.ToInt32(draDefault[k][i + 1]);
}
drNew[i + 1] = iTmp1;
iSum1 += iTmp1;
}
drNew["총원"] = iSum1;
dataTable.Rows.Add(drNew);
int iTmp2 = 0;
int iSum2 = 0;
drNew = dataTable.NewRow();
drNew["구분"] = "울산";
for (int i = 0; i < dt_jikgub.Rows.Count; i++)
{
draDefault = dtTable.Select(
"TEAM_NM LIKE '울산%'"
);
iTmp2 = 0;
for (int k = 0; k < draDefault.Length; k++)
{
iTmp2 += Convert.ToInt32(draDefault[k][i + 1]);
}
drNew[i + 1] = iTmp2;
iSum2 += iTmp2;
}
drNew["총원"] = iSum2;
dataTable.Rows.Add(drNew);
return(dataTable);
}
private DataTable GetGridDataTable(string yearStr, string monthStr, string type)
{
string typeStr = "";
if (type.Equals("1"))
{
typeStr = "";
}
else
{
typeStr = "SUM_";
}
string query = GetQuery(yearStr, 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("GUBUN", typeof(string));
for (int i = 1; i <= 12; i++)
{
dataTable.Columns.Add("T_" + i.ToString(), typeof(double));
}
dr = dataTable.NewRow();
dr["GUBUN"] = "계획";
dataTable.Rows.Add(dr);
dr = dataTable.NewRow();
dr["GUBUN"] = "실적";
dataTable.Rows.Add(dr);
dr = dataTable.NewRow();
dr["GUBUN"] = "집행율(%)";
dataTable.Rows.Add(dr);
for (int j = 0; j <= 2; j++)
{
for (int i = 1; i <= 12; i++)
{
dataTable.Rows[j]["T_" + i.ToString()] = 0;
}
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
drCol = ds.Tables[0].Select("MONTH = '" + i.ToString().PadLeft(2, '0') + "'");
if (drCol.Length > 0)
{
dataTable.Rows[0]["T_" + i.ToString()] = Convert.ToDouble(drCol[0]["EMP_" + typeStr + "PLAN"].ToString());
}
}
for (int i = 1; i <= 12; i++)
{
drCol = ds.Tables[0].Select("MONTH = '" + i.ToString().PadLeft(2, '0') + "'");
if (i <= int.Parse(monthStr))
{
if (drCol.Length > 0)
{
dataTable.Rows[1]["T_" + i.ToString()] = Convert.ToDouble(drCol[0]["EMP_" + typeStr + "ACTUAL"].ToString());
}
}
}
for (int i = 1; i <= 12; i++)
{
drCol = ds.Tables[0].Select("MONTH = '" + i.ToString().PadLeft(2, '0') + "'");
if (i <= int.Parse(monthStr))
{
if (drCol.Length > 0)
{
dataTable.Rows[2]["T_" + i.ToString()] = Convert.ToDouble(drCol[0]["EMP_" + typeStr + "ACHIVE"].ToString());
}
}
}
return(dataTable);
}
public static Message GetResult(string msg, string user)
{
Command x;
try {
x = Parser.ParseCommand(msg, Config.sub_module_ID);
} catch (CommandErrorException) {
if (DBAgent.isExist(msg) == false)
{
return(new Message(""));
}
else
{
return(new Message(DBAgent.Lookup(msg))); //带空格的情况
}
}
bool force = true;
switch (x.operation)
{
case 0: //teach
force = false;
goto spin_jump_with_eyes_closed;
case 1: //force
force = true;
spin_jump_with_eyes_closed :; //只用一次，就一次
if (x.parameters.Count != 2)
{
throw new FormatException();
}
if (DBAgent.isExist(x.parameters[0]))
{
if (force)
{
DBAgent.Update(x.parameters[0], x.parameters[1], user);
return(new Message("好吧好吧听你的"));
}
return(new Message("我已经被安排过这句话了！"));
}
DBAgent.Insert(x.parameters[0], x.parameters[1], user);
return(new Message("谢谢你，我学会了，你呢?"));
case 2: //reply 实际上是强制回复
if (x.parameters.Count > 1)
{
throw new FormatException();
}
if (DBAgent.isExist(x.parameters[0]))
{
return(new Message(DBAgent.Lookup(x.parameters[0])));
}
return(new Message("我还不会这句话。。。"));
case 3:
if (x.parameters.Count > 1)
{
throw new FormatException();
}
if (DBAgent.isExist(x.parameters[0]))
{
DBAgent.Erase(x.parameters[0]);
return(new Message("我。。忘了什么？"));
}
return(new Message("我本来就不知道这句话，那你叫我忘掉啥"));
case 4:
case 5:
Newtonsoft.Json.Linq.JObject result;
string url = "https://v1.hitokoto.cn/?encode=json&charset=utf-8";
if (x.parameters.Count >= 1)
{
if (x.parameters[0] == "help")
{
string ret = "可用的类型有:\n";
foreach (var i in Module_hitokoto.Interface.types)
{
ret += i + '\n';
}
return(new Message(ret));
}
if (Module_hitokoto.Interface.types.Contains(x.parameters[0]))
{
url +=
$"&c={Convert.ToChar('a' + Module_hitokoto.Interface.types.IndexOf(x.parameters[0]))}";
}
}
try {
using (var http = new System.Net.Http.HttpClient()) {
string res = http.GetStringAsync(url).Result;
result = (Newtonsoft.Json.Linq.JObject)Newtonsoft.Json.JsonConvert.DeserializeObject(res);
}
} catch { return(new Message("网络错误")); }
return(new Message($"{result["hitokoto"].ToString()}\n--{result["from"].ToString()}"));
case 6:
case 7:
try {
return(new Message(new ElementMusic("163", x.parameters[0])));
} catch {
return(new Message("请输入/listen(或/点歌) 想听的歌名"));
}
case -1:
return(new Message(""));
}
throw new Exception("有毒");
//Console.Write("程序不会跑到这里的".Length);
}
/// <summary>
/// GetChartDataTable
/// : Chart용 데이타테이블 리턴
/// </summary>
/// <param name="asYYYYMMs"></param>
/// <param name="asYYYYMMe"></param>
/// <param name="sGubun"></param>
/// <returns></returns>
private DataTable GetChartDataTable(string asYYYYMMs, string asYYYYMMe, string asGubun, string asOffice)
{
string[] saTerm = GetMonthDiff(asYYYYMMs, asYYYYMMe, "M");
string sQuery = GetGridQuery(asYYYYMMs, asYYYYMMe, asGubun, asOffice, false); // 기본쿼리
string sTypeQuery = ""; // 시리즈용 쿼리
sTypeQuery += "SELECT DISTINCT \n";
sTypeQuery += " a.sem_code2_name, \n";
sTypeQuery += " a.sem_code2_t \n";
sTypeQuery += " FROM SEM_CODE_MASTER a, \n";
sTypeQuery += " SEM_MOVEMENT_PRICE b \n";
sTypeQuery += " WHERE a.sem_code1_t = '09' \n";
sTypeQuery += " AND b.LNG_DATE_T BETWEEN '" + asYYYYMMs + "' \n";
sTypeQuery += " AND '" + asYYYYMMe + "' \n";
sTypeQuery += " AND b.LNG_OFFICE_T = '" + asOffice + "' \n";
sTypeQuery += " AND b.LNG_GUBN_NAME_T = a.SEM_CODE2_T \n";
sTypeQuery += " ORDER BY \n";
sTypeQuery += " a.sem_code2_t \n";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(sQuery, "Data");
DataSet dsType = dbAgent.FillDataSet(sTypeQuery, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
DataRow drType = null;
DataRow[] drDef = null;
// 년도 lng b-c(30%) b-c(50%) 납사 lpg 경유 보일러등유
dataTable.Columns.Add("sem_yyyymm", typeof(string));
// 시리즈컬럼
for (int i = 0; i < dsType.Tables[0].Rows.Count; i++)
{
dataTable.Columns.Add("sem_" + dsType.Tables[0].Rows[i]["sem_code2_name"].ToString() + "_t", typeof(float));
}
// X축(년도) 기준 데이타 완성
for (int i = 0; i < saTerm.Length; i++)
{
dr = dataTable.NewRow();
dr["sem_yyyymm"] = saTerm[i].Substring(2, 2) + "/" + saTerm[i].Substring(4, 2);
for (int j = 0; j < dsType.Tables[0].Rows.Count; j++)
{
drType = dsType.Tables[0].Rows[j];
drDef = ds.Tables[0].Select(
"sem_code2_t='" + drType["sem_code2_t"].ToString() + "'"
+ "AND sem_code2_name='" + drType["sem_code2_name"].ToString() + "'"
);
if (drDef.Length > 0)
{
dr["sem_" + drType["sem_code2_name"].ToString() + "_t"] = Convert.ToSingle(drDef[0]["v_" + saTerm[i]]);
}
}
dataTable.Rows.Add(dr);
}
return(dataTable);
}
private DataTable GetDataTable(string yearStr)
{
string query = GetChartQuery(yearStr);
int cYear = int.Parse(yearStr.Substring(0, 4));
int pYear = cYear - 1;
string query_type = @"SELECT SEM_CODE2_T, SEM_CODE2_NAME FROM SEM_CODE_MASTER WHERE SEM_CODE1_T = '11'";
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(query, "Data");
DataSet ds_type = dbAgent.FillDataSet(query_type, "Data");
//DataGrid1.DataSource = ds;
//DataGrid1.DataBind();
DataTable dataTable = new DataTable();
DataRow dr = null;
//gridTable.Columns.Add("SEM_CODE2_T", typeof(string));
dataTable.Columns.Add("SEM_CODE2_T", typeof(string));
dataTable.Columns.Add("SEM_CODE2_NAME", typeof(string));
dataTable.Columns.Add("T_" + pYear + "_1", typeof(double));
dataTable.Columns.Add("T_" + pYear + "_2", typeof(double));
dataTable.Columns.Add("T_" + cYear + "_3", typeof(double));
dataTable.Columns.Add("T_" + cYear + "_4", typeof(double));
for (int i = 0; i < ds_type.Tables[0].Rows.Count; i++)
{
dr = dataTable.NewRow();
dr["SEM_CODE2_T"] = ds_type.Tables[0].Rows[i]["SEM_CODE2_T"].ToString();
dr["SEM_CODE2_NAME"] = ds_type.Tables[0].Rows[i]["SEM_CODE2_NAME"].ToString();
dr["T_" + pYear + "_1"] = 0;
dr["T_" + pYear + "_2"] = 0;
dr["T_" + cYear + "_3"] = 0;
dr["T_" + cYear + "_4"] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 2; i++)
{
for (int j = 0; j < ds_type.Tables[0].Rows.Count; j++)
{
drCol = ds.Tables[0].Select("CUST_CENTER_CODE_T = '" + dataTable.Rows[j]["SEM_CODE2_T"].ToString() + "' AND TIME_TYPE = '" + i.ToString() + "'");
if (drCol.Length > 0)
{
dataTable.Rows[j]["T_" + pYear + "_" + i.ToString()] = Convert.ToDouble(drCol[0]["CUST_GRADE"].ToString());
}
}
}
for (int i = 3; i <= 4; i++)
{
for (int j = 0; j < ds_type.Tables[0].Rows.Count; j++)
{
drCol = ds.Tables[0].Select("CUST_CENTER_CODE_T = '" + dataTable.Rows[j]["SEM_CODE2_T"].ToString() + "' AND TIME_TYPE = '" + i.ToString() + "'");
if (drCol.Length > 0)
{
dataTable.Rows[j]["T_" + cYear + "_" + i.ToString()] = Convert.ToDouble(drCol[0]["CUST_GRADE"].ToString());
}
}
}
return(dataTable);
}
private void setGridData()
{
string sqlCD = GetTypeQuery(ddlType.SelectedValue);
string sYear = Convert.ToString(int.Parse(this.ddlYear.SelectedValue) - 2);
string eYear = ddlYear.SelectedValue.ToString();
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds = dbAgent.FillDataSet(sqlCD, "Data");
string sqlHD = "";
string sqlDT = "";
string strQN = "\"";
int intRow = 0;
string strCD = "";
string strNM = "";
intRow = ds.Tables[0].Rows.Count;
sqlHD = "";
sqlDT = "";
for (int i = 0; i < intRow; i++)
{
strCD = ds.Tables[0].Rows[i][0].ToString();
strNM = ds.Tables[0].Rows[i][1].ToString();
sqlHD += (i == (intRow - 1)) ? "SUM(COL_" + i.ToString() + ") as " + strQN + strNM + strQN :
"SUM(COL_" + i.ToString() + ") as " + strQN + strNM + strQN + ",";
sqlDT += (i == (intRow - 1)) ? "DECODE(A.SEM_CODE3_T,'" + strCD + "',A.CST_GRADE,0) as COL_" + i.ToString() :
"DECODE(A.SEM_CODE3_T,'" + strCD + "',A.CST_GRADE,0) as COL_" + i.ToString() + ",";
}
string grdSQL =
@"
SELECT B.YY as " + strQN + @"년도" + strQN + @",
" + sqlHD + @"
FROM (
SELECT A.CST_DATE_T as YY,
" + sqlDT + @"
FROM (
SELECT SEM_CUSTOMER_CST.CST_DATE_T,
SEM_CODE_MASTER.SEM_CODE2_T,
SEM_CODE_MASTER.SEM_CODE3_T,
SEM_CODE_MASTER.SEM_CODE2_NAME,
SEM_CODE_MASTER.SEM_CODE3_NAME,
SEM_CUSTOMER_CST.CST_GRADE
FROM SEM_CODE_MASTER SEM_CODE_MASTER,
SEM_CUSTOMER_CST SEM_CUSTOMER_CST
WHERE SEM_CODE_MASTER.SEM_CODE1_T = '14'
AND SEM_CUSTOMER_CST.CST_DATE_T BETWEEN '" + sYear + @"' AND '" + eYear + @"'
AND SEM_CUSTOMER_CST.CST_GUBN_T = '" + ddlType.SelectedValue + @"'
AND SEM_CUSTOMER_CST.CST_GUBN_T = SEM_CODE_MASTER.SEM_CODE2_T
AND SEM_CUSTOMER_CST.CST_CODE_T = SEM_CODE_MASTER.SEM_CODE3_T
) A
) B
GROUP BY B.YY
";
DataSet dsGrid = dbAgent.FillDataSet(grdSQL, "tblGrid");
_setGraph(dsGrid);
_setGrid(dsGrid);
}
private DataTable GetContentTable(string yearStr, string monthStr, string yearTStr, string monthTStr, string areaStr)
{
string query_p = GetGridContentQuery(Convert.ToString(int.Parse(yearStr) - 1), monthStr, Convert.ToString(int.Parse(yearTStr) - 1), monthTStr, areaStr);
string query_c = GetGridContentQuery(yearStr, monthStr, yearTStr, monthTStr, areaStr);
string query_p_profit = GetGridTotalProfitQuery(Convert.ToString(int.Parse(yearStr) - 1), monthStr, Convert.ToString(int.Parse(yearTStr) - 1), monthTStr, areaStr);
string query_c_profit = GetGridTotalProfitQuery(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");
DataSet ds_p_profit = dbAgent.FillDataSet(query_p_profit, "Data");
DataSet ds_c_profit = dbAgent.FillDataSet(query_c_profit, "Data");
DataTable dt_header = GetHeaderTable(yearStr, monthStr, yearTStr, monthTStr, areaStr);
//DataSet ds_type = dbAgent.FillDataSet(query_type, "Data");
double p_profit_plan = 0;
double p_profit_actual = 0;
double c_profit_plan = 0;
double c_profit_actual = 0;
if (ds_p_profit.Tables[0].Rows.Count > 0)
{
p_profit_plan = double.Parse(ds_p_profit.Tables[0].Rows[0]["PL_BUDGET_AMOUNT"].ToString());
p_profit_actual = double.Parse(ds_p_profit.Tables[0].Rows[0]["PL_ACTUAL_AMOUNT"].ToString());
}
if (ds_c_profit.Tables[0].Rows.Count > 0)
{
c_profit_plan = double.Parse(ds_c_profit.Tables[0].Rows[0]["PL_BUDGET_AMOUNT"].ToString());
c_profit_actual = double.Parse(ds_c_profit.Tables[0].Rows[0]["PL_ACTUAL_AMOUNT"].ToString());
}
DataTable totalTable = new DataTable();
DataTable dataTable = new DataTable();
DataRow dr = null;
DataRowCollection drCol_p = null;
DataRowCollection drCol_c = null;
dataTable.Columns.Add("T_1_CODE", typeof(string));
dataTable.Columns.Add("T_2_CODE", typeof(string));
dataTable.Columns.Add("T_1_NAME", typeof(string));
dataTable.Columns.Add("T_2_NAME", typeof(string));
dataTable.Columns.Add("ACTUAL_PRICE", typeof(double));
dataTable.Columns.Add("ACTUAL_CONSIST", typeof(double));
dataTable.Columns.Add("ACTUAL_UNIT", typeof(double));
dataTable.Columns.Add("PLAN_PRICE", typeof(double));
dataTable.Columns.Add("PLAN_CONSIST", typeof(double));
dataTable.Columns.Add("PLAN_UNIT", typeof(double));
dataTable.Columns.Add("P_ACTUAL_PRICE", typeof(double));
dataTable.Columns.Add("P_ACTUAL_CONSIST", typeof(double));
dataTable.Columns.Add("P_ACTUAL_UNIT", typeof(double));
dataTable.Columns.Add("P_RATE_COUNT", typeof(double));
dataTable.Columns.Add("P_RATE_PERCENT", typeof(double));
dataTable.Columns.Add("PLAN_RATE_COUNT", typeof(double));
dataTable.Columns.Add("PLAN_RATE_PERCENT", typeof(double));
dataTable.Columns.Add("OBJECT_TYPE", typeof(int));
dataTable.Columns.Add("SORT", typeof(int));
drCol_c = ds_c.Tables[0].Rows;
drCol_p = ds_p.Tables[0].Rows;
for (int i = 0; i < ds_c.Tables[0].Rows.Count; i++)
{
dr = dataTable.NewRow();
dr["T_1_CODE"] = ds_c.Tables[0].Rows[i]["SEM_ACCOUNT1_CODE"].ToString();
dr["T_2_CODE"] = ds_c.Tables[0].Rows[i]["SEM_ACCOUNT2_CODE"].ToString();
dr["T_1_NAME"] = ds_c.Tables[0].Rows[i]["SEM_ACCOUNT1_DESC"].ToString();
dr["T_2_NAME"] = ds_c.Tables[0].Rows[i]["SEM_ACCOUNT2_DESC"].ToString();
dr["ACTUAL_PRICE"] = 0;
dr["ACTUAL_CONSIST"] = 0;
dr["ACTUAL_UNIT"] = 0;
dr["PLAN_PRICE"] = 0;
dr["PLAN_CONSIST"] = 0;
dr["PLAN_UNIT"] = 0;
dr["P_ACTUAL_PRICE"] = 0;
dr["P_ACTUAL_CONSIST"] = 0;
dr["P_ACTUAL_UNIT"] = 0;
dr["P_RATE_COUNT"] = 0;
dr["P_RATE_PERCENT"] = 0;
dr["PLAN_RATE_COUNT"] = 0;
dr["PLAN_RATE_PERCENT"] = 0;
dr["OBJECT_TYPE"] = 0;
dr["SORT"] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drArr_c = null;
DataRow[] drArr_p = null;
for (int i = 0; i < ds_c.Tables[0].Rows.Count; i++)
{
drArr_c = ds_c.Tables[0].Select("SEM_ACCOUNT2_CODE = '" + ds_c.Tables[0].Rows[i]["SEM_ACCOUNT2_CODE"].ToString() + "'");
drArr_p = ds_p.Tables[0].Select("SEM_ACCOUNT2_CODE = '" + ds_c.Tables[0].Rows[i]["SEM_ACCOUNT2_CODE"].ToString() + "'");
if (drArr_c.Length > 0)
{
dataTable.Rows[i]["ACTUAL_PRICE"] = double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString());
dataTable.Rows[i]["ACTUAL_CONSIST"] = (c_profit_actual == 0) ? 0 : double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString()) / c_profit_actual * 100.00;
dataTable.Rows[i]["ACTUAL_UNIT"] = (double.Parse(dt_header.Rows[0]["C_ACTUAL"].ToString()) == 0) ? 0 : double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString()) / double.Parse(dt_header.Rows[0]["C_ACTUAL"].ToString());
dataTable.Rows[i]["PLAN_PRICE"] = double.Parse(drArr_c[0]["PL_BUDGET_AMOUNT"].ToString());
dataTable.Rows[i]["PLAN_CONSIST"] = (c_profit_plan == 0) ? 0 : double.Parse(drArr_c[0]["PL_BUDGET_AMOUNT"].ToString()) / c_profit_plan * 100.00;
dataTable.Rows[i]["PLAN_UNIT"] = (double.Parse(dt_header.Rows[0]["C_PLAN"].ToString()) == 0) ? 0 : double.Parse(drArr_c[0]["PL_BUDGET_AMOUNT"].ToString()) / double.Parse(dt_header.Rows[0]["C_PLAN"].ToString());
dataTable.Rows[i]["PLAN_RATE_COUNT"] = double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString()) - double.Parse(drArr_c[0]["PL_BUDGET_AMOUNT"].ToString());
dataTable.Rows[i]["PLAN_RATE_PERCENT"] = (double.Parse(drArr_c[0]["PL_BUDGET_AMOUNT"].ToString()) == 0) ? 0 : double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString()) / double.Parse(drArr_c[0]["PL_BUDGET_AMOUNT"].ToString()) * 100.00;
if (drArr_c[0]["SEM_ACCOUNT1_CODE"].ToString().Equals("4003000000") || drArr_c[0]["SEM_ACCOUNT1_CODE"].ToString().Equals("4005000000"))
{
dataTable.Rows[i]["ACTUAL_CONSIST"] = 0;
dataTable.Rows[i]["PLAN_CONSIST"] = 0;
}
}
if (drArr_p.Length > 0)
{
dataTable.Rows[i]["P_ACTUAL_PRICE"] = double.Parse(drArr_p[0]["PL_ACTUAL_AMOUNT"].ToString());
dataTable.Rows[i]["P_ACTUAL_CONSIST"] = (p_profit_actual == 0) ? 0 : double.Parse(drArr_p[0]["PL_ACTUAL_AMOUNT"].ToString()) / p_profit_actual * 100.00;
dataTable.Rows[i]["P_ACTUAL_UNIT"] = (double.Parse(dt_header.Rows[0]["P_PAST_ACTUAL"].ToString()) == 0) ? 0 : double.Parse(drArr_p[0]["PL_ACTUAL_AMOUNT"].ToString()) / double.Parse(dt_header.Rows[0]["P_PAST_ACTUAL"].ToString());
dataTable.Rows[i]["P_RATE_COUNT"] = double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString()) - double.Parse(drArr_p[0]["PL_ACTUAL_AMOUNT"].ToString());
dataTable.Rows[i]["P_RATE_PERCENT"] = (double.Parse(drArr_p[0]["PL_ACTUAL_AMOUNT"].ToString()) == 0) ? 0 : double.Parse(drArr_c[0]["PL_ACTUAL_AMOUNT"].ToString()) / double.Parse(drArr_p[0]["PL_ACTUAL_AMOUNT"].ToString()) * 100.00;
if (drArr_p[0]["SEM_ACCOUNT1_CODE"].ToString().Equals("4003000000") || drArr_p[0]["SEM_ACCOUNT1_CODE"].ToString().Equals("4005000000"))
{
dataTable.Rows[i]["P_ACTUAL_CONSIST"] = 0;
}
}
}
totalTable.Merge(dataTable);
totalTable.Rows.Clear();
string tempCode = "";
string tempName = "";
int gidx = 1;
int idx = 1;
double actual_price = 0;
double actual_consist = 0;
double actual_unit = 0;
double plan_price = 0;
double plan_consist = 0;
double plan_unit = 0;
double p_actual_price = 0;
double p_actual_consist = 0;
double p_actual_unit = 0;
double p_rate_count = 0;
double p_rate_percent = 0;
double plan_rate_count = 0;
double plan_rate_percent = 0;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (tempCode != dataTable.Rows[i]["T_1_CODE"].ToString() && tempCode != "")
{
dr = totalTable.NewRow();
dr["T_1_CODE"] = tempCode;
dr["T_2_CODE"] = tempCode;
dr["T_1_NAME"] = tempName;
dr["T_2_NAME"] = tempName;
dr["ACTUAL_PRICE"] = actual_price;
dr["ACTUAL_CONSIST"] = actual_consist;
dr["ACTUAL_UNIT"] = actual_unit;
dr["PLAN_PRICE"] = plan_price;
dr["PLAN_CONSIST"] = plan_consist;
dr["PLAN_UNIT"] = plan_unit;
dr["P_ACTUAL_PRICE"] = p_actual_price;
dr["P_ACTUAL_CONSIST"] = p_actual_consist;
dr["P_ACTUAL_UNIT"] = p_actual_unit;
dr["P_RATE_COUNT"] = p_rate_count;
dr["P_RATE_PERCENT"] = p_rate_percent;
dr["PLAN_RATE_COUNT"] = plan_rate_count;
dr["PLAN_RATE_PERCENT"] = plan_rate_percent;
dr["OBJECT_TYPE"] = gidx;
dr["SORT"] = 0;
totalTable.Rows.Add(dr);
//tempCode = "";
//tempName = "";
idx = 1;
gidx++;
actual_price = 0;
actual_consist = 0;
actual_unit = 0;
plan_price = 0;
plan_consist = 0;
plan_unit = 0;
p_actual_price = 0;
p_actual_consist = 0;
p_actual_unit = 0;
p_rate_count = 0;
p_rate_percent = 0;
plan_rate_count = 0;
plan_rate_percent = 0;
}
if (dataTable.Rows[i]["T_1_CODE"].ToString() == "4003000000" ||
dataTable.Rows[i]["T_1_CODE"].ToString() == "4005000000" ||
dataTable.Rows[i]["T_1_CODE"].ToString() == "4009000000" ||
dataTable.Rows[i]["T_1_CODE"].ToString() == "4013000000" ||
dataTable.Rows[i]["T_1_CODE"].ToString() == "4015000000")
{
dr = totalTable.NewRow();
dr["T_1_CODE"] = dataTable.Rows[i]["T_1_CODE"].ToString();
dr["T_2_CODE"] = dataTable.Rows[i]["T_2_CODE"].ToString();
dr["T_1_NAME"] = dataTable.Rows[i]["T_1_NAME"].ToString();
dr["T_2_NAME"] = dataTable.Rows[i]["T_2_NAME"].ToString();
dr["ACTUAL_PRICE"] = dataTable.Rows[i]["ACTUAL_PRICE"];
dr["ACTUAL_CONSIST"] = dataTable.Rows[i]["ACTUAL_CONSIST"];
dr["ACTUAL_UNIT"] = dataTable.Rows[i]["ACTUAL_UNIT"];
dr["PLAN_PRICE"] = dataTable.Rows[i]["PLAN_PRICE"];
dr["PLAN_CONSIST"] = dataTable.Rows[i]["PLAN_CONSIST"];
dr["PLAN_UNIT"] = dataTable.Rows[i]["PLAN_UNIT"];
dr["P_ACTUAL_PRICE"] = dataTable.Rows[i]["P_ACTUAL_PRICE"];
dr["P_ACTUAL_CONSIST"] = dataTable.Rows[i]["P_ACTUAL_CONSIST"];
dr["P_ACTUAL_UNIT"] = dataTable.Rows[i]["P_ACTUAL_UNIT"];
dr["P_RATE_COUNT"] = dataTable.Rows[i]["P_RATE_COUNT"];
dr["P_RATE_PERCENT"] = dataTable.Rows[i]["P_RATE_PERCENT"];
dr["PLAN_RATE_COUNT"] = dataTable.Rows[i]["PLAN_RATE_COUNT"];
dr["PLAN_RATE_PERCENT"] = dataTable.Rows[i]["PLAN_RATE_PERCENT"];
dr["OBJECT_TYPE"] = gidx;
dr["SORT"] = idx.ToString();
totalTable.Rows.Add(dr);
}
actual_price += double.Parse(dataTable.Rows[i]["ACTUAL_PRICE"].ToString());
actual_consist += double.Parse(dataTable.Rows[i]["ACTUAL_CONSIST"].ToString());
actual_unit += double.Parse(dataTable.Rows[i]["ACTUAL_UNIT"].ToString());
plan_price += double.Parse(dataTable.Rows[i]["PLAN_PRICE"].ToString());
plan_consist += double.Parse(dataTable.Rows[i]["PLAN_CONSIST"].ToString());
plan_unit += double.Parse(dataTable.Rows[i]["PLAN_UNIT"].ToString());
p_actual_price += double.Parse(dataTable.Rows[i]["P_ACTUAL_PRICE"].ToString());
p_actual_consist += double.Parse(dataTable.Rows[i]["P_ACTUAL_CONSIST"].ToString());
p_actual_unit += double.Parse(dataTable.Rows[i]["P_ACTUAL_UNIT"].ToString());
p_rate_count += double.Parse(dataTable.Rows[i]["P_RATE_COUNT"].ToString());
p_rate_percent += double.Parse(dataTable.Rows[i]["P_RATE_PERCENT"].ToString());
plan_rate_count += double.Parse(dataTable.Rows[i]["PLAN_RATE_COUNT"].ToString());
plan_rate_percent += double.Parse(dataTable.Rows[i]["PLAN_RATE_PERCENT"].ToString());
idx++;
tempCode = dataTable.Rows[i]["T_1_CODE"].ToString();
tempName = dataTable.Rows[i]["T_1_NAME"].ToString();
// 마지막 라인을 추가하기 위해서
if (i == dataTable.Rows.Count - 1)
{
dr = totalTable.NewRow();
dr["T_1_CODE"] = tempCode;
dr["T_2_CODE"] = tempCode;
dr["T_1_NAME"] = tempName;
dr["T_2_NAME"] = tempName;
dr["ACTUAL_PRICE"] = actual_price;
dr["ACTUAL_CONSIST"] = actual_consist;
dr["ACTUAL_UNIT"] = actual_unit;
dr["PLAN_PRICE"] = plan_price;
dr["PLAN_CONSIST"] = plan_consist;
dr["PLAN_UNIT"] = plan_unit;
dr["P_ACTUAL_PRICE"] = p_actual_price;
dr["P_ACTUAL_CONSIST"] = p_actual_consist;
dr["P_ACTUAL_UNIT"] = p_actual_unit;
dr["P_RATE_COUNT"] = p_rate_count;
dr["P_RATE_PERCENT"] = p_rate_percent;
dr["PLAN_RATE_COUNT"] = plan_rate_count;
dr["PLAN_RATE_PERCENT"] = plan_rate_percent;
dr["OBJECT_TYPE"] = gidx;
dr["SORT"] = 0;
totalTable.Rows.Add(dr);
}
}
return(totalTable);
}
private DataTable GetTwoDataTable(string yearStr, string monthStr)
{
string query_one = GetOneQuery(yearStr, monthStr);
string query_two = GetTwoQuery(yearStr, monthStr);
dbAgent = new DBAgent(System.Configuration.ConfigurationManager.ConnectionStrings["MainDB_Oracle"].ConnectionString);
DataSet ds_one = dbAgent.FillDataSet(query_one, "Data");
DataSet ds_two = dbAgent.FillDataSet(query_two, "Data");
DataTable dataTable = new DataTable();
DataRow dr = null;
dataTable.Columns.Add("DADT_1", typeof(string));
dataTable.Columns.Add("BS_1", typeof(double));
dataTable.Columns.Add("PL_1", typeof(double));
dataTable.Columns.Add("PL_SUM_1", typeof(double));
dataTable.Columns.Add("DADT_2", typeof(string));
dataTable.Columns.Add("BS_2", typeof(double));
dataTable.Columns.Add("PL_2", typeof(double));
dataTable.Columns.Add("PL_SUM_2", typeof(double));
for (int i = 1; i <= 12; i++)
{
dr = dataTable.NewRow();
dr["DADT_1"] = i.ToString().PadLeft(2, '0');
dr["BS_1"] = 0;
dr["PL_1"] = 0;
dr["PL_SUM_1"] = 0;
dr["DADT_2"] = i.ToString().PadLeft(2, '0');
dr["BS_2"] = 0;
dr["PL_2"] = 0;
dr["PL_SUM_2"] = 0;
dataTable.Rows.Add(dr);
}
DataRow[] drCol = null;
for (int i = 1; i <= 12; i++)
{
drCol = ds_one.Tables[0].Select("DADT_1 = '" + i.ToString().PadLeft(2, '0') + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i - 1]["BS_1"] = Convert.ToDouble(drCol[0]["BS_1"].ToString());
dataTable.Rows[i - 1]["PL_1"] = Convert.ToDouble(drCol[0]["PL_1"].ToString());
dataTable.Rows[i - 1]["PL_SUM_1"] = Convert.ToDouble(drCol[0]["PL_SUM_1"].ToString());
}
}
for (int i = 1; i <= 12; i++)
{
drCol = ds_two.Tables[0].Select("DADT_2 = '" + i.ToString().PadLeft(2, '0') + "'");
if (drCol.Length > 0)
{
dataTable.Rows[i - 1]["BS_2"] = Convert.ToDouble(drCol[0]["BS_2"].ToString());
dataTable.Rows[i - 1]["PL_2"] = Convert.ToDouble(drCol[0]["PL_2"].ToString());
dataTable.Rows[i - 1]["PL_SUM_2"] = Convert.ToDouble(drCol[0]["PL_SUM_2"].ToString());
}
}
return(dataTable);
}
/// <summary>
/// 역할 클래스 생성자 입니다.
/// </summary>
public Roles()
{
_dbAgent = new DBAgent();
_dbAgent.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString;
}