Esempio n. 1
0
        public WorkTimeEstimateDataSet GetWorkTimeEstimateToPrint(
            int appId,
            int prjId,
            string depCode,
            string reportType,
            Dictionary <string, string> param)
        {
            LOGGER.Finest("Entering GetWorkTimeEstimateToPrint");

            OracleConnection connection = null;

            OracleParameter    appIdParam      = null;
            OracleParameter    prjIdParam      = null;
            OracleParameter    depCodeParam    = null;
            OracleParameter    reportTypeParam = null;
            OracleParameter    prjNumberParam  = null;
            OracleParameter    prjNameParam    = null;
            OracleParameter    weightParam     = null;
            OracleParameter    qtyParam        = null;
            OracleParameter    repDateParam    = null;
            OracleParameter    authorParam     = null;
            OracleParameter    itemsParam      = null;
            OracleParameter    itemsDepParam   = null;
            NullableDataReader nullableReader  = null;


            //
            //
            WorkTimeEstimateDataSet result = new WorkTimeEstimateDataSet();
            //
            //



            OracleCommand cmd = new OracleCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                connection      = getConnection();
                cmd.CommandText = "WB_PCK_REPORTS_WORK.get_work_time_estimate";
                cmd.Connection  = connection;

                appIdParam      = SQLUtil.CreateInt32OracleParam("P_APP_ID", cmd, appId, ParameterDirection.Input);
                prjIdParam      = SQLUtil.CreateInt32OracleParam("p_prj_id", cmd, prjId, ParameterDirection.Input);
                depCodeParam    = SQLUtil.CreateVarcharOracleParam("p_dep_code", cmd, depCode, ParameterDirection.Input);
                reportTypeParam = SQLUtil.CreateVarcharOracleParam("p_report_type", cmd, reportType, ParameterDirection.Input);
                prjNumberParam  = SQLUtil.CreateVarcharOracleParam("o_prj_number", cmd, null, ParameterDirection.Output);
                prjNameParam    = SQLUtil.CreateVarcharOracleParam("o_prj_name", cmd, null, ParameterDirection.Output);
                weightParam     = SQLUtil.CreateInt32OracleParam("o_weight", cmd, null, ParameterDirection.Output);
                qtyParam        = SQLUtil.CreateInt32OracleParam("o_qty", cmd, null, ParameterDirection.Output);
                repDateParam    = SQLUtil.CreateDateOracleParam("o_rep_date", cmd, null, ParameterDirection.Output);
                authorParam     = SQLUtil.CreateVarcharOracleParam("o_author", cmd, null, ParameterDirection.Output);
                itemsParam      = SQLUtil.CreateCursorOracleParameter("o_items", OracleDbType.RefCursor, cmd);
                itemsDepParam   = SQLUtil.CreateCursorOracleParameter("o_items_dep", OracleDbType.RefCursor, cmd);

                cmd.ExecuteNonQuery();

                WorkTimeEstimateDataSet.WorkTimeMainTableRow row = result.WorkTimeMainTable.NewWorkTimeMainTableRow();

                row.ID         = prjId.ToString();
                row.ProjNumber = SQLUtil.GetStringValue(prjNumberParam);
                row.ProjName   = SQLUtil.GetStringValue(prjNameParam);
                row.Weight     = SQLUtil.GetDecimalValue(weightParam).HasValue ? SQLUtil.GetDecimalValue(weightParam).ToString() : "---";
                row.Qty        = SQLUtil.GetDecimalValue(qtyParam).HasValue ? SQLUtil.GetDecimalValue(qtyParam).ToString() : "---";
                row.AllWeight  = SQLUtil.GetDecimalValue(weightParam).HasValue ? SQLUtil.GetDecimalValue(weightParam).ToString() : "---";
                row.ReportDate = SQLUtil.GetDateValue(repDateParam).HasValue ? SQLUtil.GetDateValue(repDateParam).Value.Date.ToShortDateString() : "---";
                row.Author     = SQLUtil.GetStringValue(authorParam);

                result.WorkTimeMainTable.AddWorkTimeMainTableRow(row);

                if (!((OracleRefCursor)itemsParam.Value).IsNull)
                {
                    nullableReader = new NullableDataReader(((OracleRefCursor)itemsParam.Value).GetDataReader());

                    string TYPE           = string.Empty;
                    string name           = string.Empty;
                    string drawing_number = string.Empty;

                    string activePath = string.Empty;
                    WorkTimeEstimateDataSet.WorkTimePathRow path = null;


                    while (nullableReader.Read())
                    {
                        string iPath = ConvertUtil.ConvertToSpecialChars(nullableReader.GetNullableString("pwei_path"));

                        if (string.IsNullOrEmpty(iPath))
                        {
                            continue;
                        }

                        WorkTimeEstimateDataSet.WorkTimeItemSimpleRow item = result.WorkTimeItemSimple.NewWorkTimeItemSimpleRow();


                        item.ID        = prjId.ToString();
                        item.ItemID    = nullableReader.GetNullableDecimal("pwei_id").ToString();
                        item.IsProduct = nullableReader.GetNullableDecimal("pwei_is_product").ToString();
                        item.ParentID  = nullableReader.GetNullableDecimal("pwei_parent_pwei_id").ToString();

                        TYPE = nullableReader.GetNullableString("pwei_itm_type");
                        name = ConvertUtil.ConvertToSpecialChars(nullableReader.GetNullableString("pwei_name"));

                        drawing_number = ConvertUtil.ConvertToSpecialChars(nullableReader.GetNullableString("pwei_drawing_number"));



                        item.DrawingNumber = drawing_number;
                        item.Qty           = nullableReader.GetNullableDecimal("pwei_qty").ToString();
                        item.QtyPerProj    = nullableReader.GetNullableDecimal("pwei_qty_per_prj").ToString();
                        if (nullableReader.GetNullableString("pwei_product_brand") != null)
                        {
                            item.ProductBrand = nullableReader.GetNullableString("pwei_product_brand");
                        }
                        else
                        {
                            item.ProductBrand = "";
                        }

                        string tmp_sizeCut = nullableReader.GetNullableString("pwei_size_cut");
                        string tmp_addInfo = nullableReader.GetNullableString("pwei_add_info");
                        item.SizeCut = string.IsNullOrEmpty(tmp_sizeCut) ? "" : ConvertUtil.ConvertToSpecialChars(tmp_sizeCut.Replace("X", " x ").Replace("L=", "L= "));
                        if (!string.IsNullOrEmpty(tmp_addInfo))
                        {
                            item.SizeCut += string.Format("\n{0}", tmp_addInfo);
                        }
                        item.SizeCut = ConvertUtil.ConvertToSpecialChars(item.SizeCut);

                        if (param.ContainsKey("productSize"))
                        {
                            if (!item.SizeCut.ToLower().Contains(param["productSize"]))
                            {
                                continue;
                            }
                        }

                        // material gatunek
                        string tempPrName = ConvertUtil.ConvertToSpecialChars(nullableReader.GetNullableString("pwei_pr_name"));
                        if (!string.IsNullOrEmpty(tempPrName))
                        {
                            item.PrName = tempPrName + "\n" + ConvertUtil.ConvertToSpecialChars(item.ProductBrand);
                        }
                        else
                        {
                            item.PrName = " " + "\n" + ConvertUtil.ConvertToSpecialChars(item.ProductBrand);
                        }

                        if (param.ContainsKey("materialType"))
                        {
                            if (!item.PrName.ToLower().Contains(param["materialType"]))
                            {
                                continue;
                            }
                        }

                        item.ProgramNumber = ConvertUtil.ConvertToSpecialChars(nullableReader.GetNullableString("pwei_program_number"));

                        decimal?value = nullableReader.GetNullableDecimal("pwei_ls");
                        item.LS = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.LS.Equals("0,000"))
                        {
                            item.LS = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_es");
                        item.ES = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.ES.Equals("0,000"))
                        {
                            item.ES = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_bn");
                        item.BN = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.BN.Equals("0,000"))
                        {
                            item.BN = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_pt");
                        item.PT = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.PT.Equals("0,000"))
                        {
                            item.PT = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_bw");
                        item.BW = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.BW.Equals("0,000"))
                        {
                            item.BW = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_bz");
                        item.BZ = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.BZ.Equals("0,000"))
                        {
                            item.BZ = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_ph");
                        item.PH = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.PH.Equals("0,000"))
                        {
                            item.PH = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_wp");
                        item.WP = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.WP.Equals("0,000"))
                        {
                            item.WP = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_rp");
                        item.RP = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.RP.Equals("0,000"))
                        {
                            item.RP = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_po");
                        item.PO = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.PO.Equals("0,000"))
                        {
                            item.PO = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_mw");
                        item.MW = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.MW.Equals("0,000"))
                        {
                            item.MW = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_mc");
                        item.MC = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.MC.Equals("0,000"))
                        {
                            item.MC = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_ML");
                        item.ML = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.ML.Equals("0,000"))
                        {
                            item.ML = "0";
                        }
                        value   = nullableReader.GetNullableDecimal("pwei_KO");
                        item.KO = value.HasValue ? string.Format("{0:f2}", value.Value) : "";
                        if (item.KO.Equals("0,000"))
                        {
                            item.KO = "0";
                        }

                        item.PlanNumber = nullableReader.GetNullableDecimal("pwei_lp").HasValue ?
                                          nullableReader.GetNullableDecimal("pwei_lp").Value.ToString() : "";

                        if (param.ContainsKey("planNo"))
                        {
                            if (!(item.PlanNumber.ToLower() == param["planNo"].ToLower()))
                            {
                                continue;
                            }
                        }

                        item.WorkTimeMainTableRow = row;

                        result.WorkTimeItemSimple.AddWorkTimeItemSimpleRow(item);
                    }
                }
            }

            catch (Exception ex)
            {
                LOGGER.Error("Problem occured while GetWorkTimeEstimateToPrint!" + ex.Message, ex);
                throw new Exception("Problem occured while GetWorkTimeEstimateToPrint!" + ex.Message, ex);
            }
            finally
            {
                SQLUtil.Close(appIdParam);
                SQLUtil.Close(prjIdParam);
                SQLUtil.Close(depCodeParam);
                SQLUtil.Close(reportTypeParam);
                SQLUtil.Close(prjNumberParam);
                SQLUtil.Close(prjNameParam);
                SQLUtil.Close(weightParam);
                SQLUtil.Close(qtyParam);
                SQLUtil.Close(repDateParam);
                SQLUtil.Close(authorParam);
                SQLUtil.Close(itemsParam);
                SQLUtil.Close(itemsDepParam);
                SQLUtil.Close(nullableReader);
                SQLUtil.Close(cmd);
                ReleaseConnection(connection);
            }


            return(result);
        }