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);
    }
Exemple #2
0
 public PaginationResult <UserDto> ListUserPagination(PaginationInfo pagInfo, string searchString)
 {
     return(DBAgent.SQLExecuteReturnPaginationList <UserDto>(
                InitSQLParam(pagInfo, new { searchString = searchString }, XMLHelper.GetNodeString("User", "SQL/ListUserPagination"))));
 }
Exemple #3
0
    // 그리드용 데이타테이블 반환
    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);
    }
Exemple #4
0
    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);
    }
Exemple #8
0
    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);
    }
Exemple #9
0
    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);
    }
Exemple #10
0
    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);
    }
Exemple #11
0
 /// <summary>
 /// 역할 클래스 생성자 입니다.
 /// </summary>
 public Roles()
 {
     _dbAgent = new DBAgent();
     _dbAgent.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString;
 }