Example #1
0
        public System.Data.DataSet GetAllPartNos(PRD_MAST productMaster = null, DDCUST_MAST customerMaster = null, List <DDLOC_MAST> lstLocation = null)
        {
            System.Data.DataSet dsReport = null;
            try
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("SELECT ROW_NUMBER() OVER(ORDER BY M.PART_NO) AS SNO, ");
                sb.Append("  M.PART_NO, ");
                sb.Append("  P.PART_DESC, ");
                sb.Append("  T.CUST_NAME, ");
                sb.Append("  P.BIF_PROJ, ");
                sb.Append("  P.BIF_FORECAST, ");
                sb.Append("  P.FINISH_WT, ");
                sb.Append("  M.CHEESE_WT, ");
                sb.Append("  M.RM_WT, ");
                sb.Append("  P.PG_CATEGORY, ");
                sb.Append("  P.QUALITY, ");
                sb.Append("  P.SAMP_SUBMIT_DATE, ");
                sb.Append("  P.PSW_ST, ");
                sb.Append("  C.CUST_DWG_NO, ");
                sb.Append("  P.DOC_REL_DATE ");
                sb.Append("FROM dbo.PRD_MAST             AS P ");
                sb.Append("LEFT OUTER JOIN dbo.PRD_CIREF AS F ");
                sb.Append("ON F.PART_NO = P.PART_NO ");
                sb.Append("LEFT OUTER JOIN dbo.DDCI_INFO AS C ");
                sb.Append("ON C.CI_REFERENCE = F.CI_REF, ");
                sb.Append("  dbo.PROCESS_MAIN AS M, ");
                sb.Append("  dbo.DDCUST_MAST  AS T ");
                sb.Append("WHERE P.PART_NO    = M.PART_NO ");
                sb.Append("AND M.CURRENT_PROC = 1 ");
                sb.Append("AND T.CUST_CODE    = C.CUST_CODE ");

                if (customerMaster.IsNotNullOrEmpty() && customerMaster.CUST_CODE.IsNotNullOrEmpty() && customerMaster.CUST_CODE != -999999)
                {
                    sb.Append(" AND C.CUST_CODE    = '" + customerMaster.CUST_CODE + "' ");
                }

                if (productMaster.IsNotNullOrEmpty())
                {
                    if (productMaster.PART_NO.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND P.PART_NO LIKE '" + productMaster.PART_NO + "%' ");
                    }

                    if (productMaster.PART_DESC.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND upper(P.PART_DESC) LIKE '%" + productMaster.PART_DESC.ToUpper() + "%' ");
                    }

                    if (productMaster.QUALITY.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND P.QUALITY LIKE '" + productMaster.QUALITY + "%' ");
                    }

                    if (productMaster.PG_CATEGORY.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND P.PG_CATEGORY = '" + productMaster.PG_CATEGORY + "' ");
                    }

                    if (productMaster.PSW_ST.IsNotNullOrEmpty() && (productMaster.PSW_ST.ToUpper() == "YES" || productMaster.PSW_ST.ToUpper() == "NO"))
                    {
                        sb.Append(" AND P.PSW_ST = '" + productMaster.PSW_ST + "' ");
                    }

                    if (productMaster.PSW_ST.IsNotNullOrEmpty() && (productMaster.PSW_ST.ToUpper() == "NONE"))
                    {
                        sb.Append(" AND P.PSW_ST IS NULL ");
                    }
                }

                if (lstLocation.IsNotNullOrEmpty() && lstLocation.Count > 0 && lstLocation.Count < 4)
                {
                    string locsql = "";
                    foreach (DDLOC_MAST loc in lstLocation)
                    {
                        locsql = locsql + "(P.BIF_PROJ LIKE '" + loc.LOC_CODE + "%' OR P.BIF_FORECAST LIKE '" + loc.LOC_CODE + "%')";
                        if (lstLocation[lstLocation.Count - 1].GetHashCode() != loc.GetHashCode())
                        {
                            locsql = locsql + " OR ";
                        }
                    }
                    sb.Append(" AND (" + locsql + ") ");
                }

                sb.Append(" order by M.PART_NO");

                List <StringBuilder> sqlList = new List <StringBuilder>()
                {
                    sb
                };

                dsReport = Dal.GetDataSet(sqlList);
                if (dsReport.IsNotNullOrEmpty() && dsReport.Tables.IsNotNullOrEmpty() && dsReport.Tables.Count > 0)
                {
                    dsReport.Tables[0].TableName = "PRD_MAST_CI";
                }

                DataTable dtCompany = new DataTable();
                dtCompany.TableName = "CompanyName";
                dtCompany.Columns.Add("Name");
                dtCompany.Columns.Add("ShortName");
                dtCompany.Columns.Add("Phone");
                dtCompany.Columns.Add("Fax");
                dtCompany.Columns.Add("Mobile");
                dtCompany.Columns.Add("EMail");
                dtCompany.Columns.Add("Title");
                dtCompany.Columns.Add("ReportTitle");
                if (dsReport.IsNotNullOrEmpty())
                {
                    dsReport.Tables.Add(dtCompany);
                }
            }
            catch (Exception ex)
            {
                throw ex.LogException();
            }

            return(dsReport);
        }
Example #2
0
        public System.Data.DataSet GetAllMFM(string startDate, string endDate, PRD_MAST productMaster = null, DDCI_INFO customerInfo = null, DDCUST_MAST customerMaster = null)
        {
            System.Data.DataSet dsReport = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ROW_NUMBER() OVER(ORDER BY MFM_MAST.PART_NO) AS SNO, ");
                sb.Append("  MFM_MAST.PART_NO, ");
                sb.Append("  CAST(DOC_REL_DT_PLAN as varchar(10)) as DOC_REL_DT_PLAN, ");
                sb.Append("  CAST(DOC_REL_DT_ACTUAL as varchar(10)) as DOC_REL_DT_ACTUAL, ");
                sb.Append("  CAST(TOOLS_READY_DT_PLAN as varchar(10)) as TOOLS_READY_DT_PLAN, ");
                sb.Append("  CAST(TOOLS_READY_ACTUAL_DT as varchar(10)) as TOOLS_READY_ACTUAL_DT, ");
                sb.Append("  CAST(FORGING_PLAN_DT as varchar(10)) as FORGING_PLAN_DT, ");
                sb.Append("  CAST(FORGING_ACTUAL_DT as varchar(10)) as FORGING_ACTUAL_DT, ");
                sb.Append("  CAST(SECONDARY_PLAN_DT as varchar(10)) as SECONDARY_PLAN_DT, ");
                sb.Append("  CAST(SECONDARY_ACTUAL_DT as varchar(10)) as SECONDARY_ACTUAL_DT, ");
                sb.Append("  CAST(HEAT_TREATMENT_PLAN_DT as varchar(10)) as HEAT_TREATMENT_PLAN_DT, ");
                sb.Append("  CAST(HEAT_TREATMENT_ACTUAL as varchar(10)) as HEAT_TREATMENT_ACTUAL, ");
                sb.Append("  CAST(ISSR_PLAN_DT as varchar(10)) as ISSR_PLAN_DT, ");
                sb.Append("  CAST(ISSR_ACTUAL_DT as varchar(10)) as ISSR_ACTUAL_DT, ");
                sb.Append("  CAST(PPAP_PLAN as varchar(10)) as PPAP_PLAN, ");
                sb.Append("  CAST(PPAP_ACTUAL_DT as varchar(10)) as PPAP_ACTUAL_DT, ");
                sb.Append("  CAST(PSW_DATE as varchar(10)) as PSW_DATE, ");
                sb.Append("  SAMPLE_QTY, ");
                sb.Append("  RESP ");
                sb.Append("FROM MFM_MAST ");

                if (customerMaster.IsNotNullOrEmpty() && customerMaster.CUST_CODE.ToValueAsString().Trim().Length > 0)
                {
                    sb.Append(" , ");
                    sb.Append("  prd_mast, ");
                    sb.Append("  prd_ciref, ");
                    sb.Append("  ddci_info ");
                }
                sb.Append(" WHERE 1 = 1 ");
                sb.Append(" AND DOC_REL_DT_PLAN BETWEEN  " + "'" + startDate + "'" + "  AND  " + "'" + endDate + "'");

                if (customerMaster.IsNotNullOrEmpty() && customerMaster.CUST_CODE.ToValueAsString().Trim().Length > 0)
                {
                    sb.Append(" AND prd_ciref.part_no    = prd_mast.part_no ");
                    sb.Append(" AND mfm_mast.part_no       = prd_mast.part_no ");

                    sb.Append(" AND ddci_info.ci_reference = prd_ciref.ci_ref AND prd_ciref.CURRENT_CIREF = 1 ");
                    //sb.Append(" AND ddci_info.ci_reference = prd_ciref.ci_ref");

                    sb.Append(" AND cust_code = '" + customerMaster.CUST_CODE.ToValueAsString() + "'");
                }

                if (productMaster.IsNotNullOrEmpty() && productMaster.PART_NO.ToValueAsString().Trim().Length > 0)
                {
                    sb.Append(" and MFM_MAST.part_no like '" + productMaster.PART_NO.ToValueAsString() + "%'");
                }

                sb.Append(" order by MFM_MAST.PART_NO");

                List <StringBuilder> sqlList = new List <StringBuilder>()
                {
                    sb
                };

                dsReport = Dal.GetDataSet(sqlList);
                if (dsReport.IsNotNullOrEmpty() && dsReport.Tables.IsNotNullOrEmpty() && dsReport.Tables.Count > 0)
                {
                    dsReport.Tables[0].TableName = "PRD_MAST_CI";
                }

                DataTable dtCompany = new DataTable();
                dtCompany.TableName = "CompanyName";
                dtCompany.Columns.Add("Name");
                dtCompany.Columns.Add("ShortName");
                dtCompany.Columns.Add("Phone");
                dtCompany.Columns.Add("Fax");
                dtCompany.Columns.Add("Mobile");
                dtCompany.Columns.Add("EMail");
                dtCompany.Columns.Add("Title");
                dtCompany.Columns.Add("ReportTitle");
                if (dsReport.IsNotNullOrEmpty())
                {
                    dsReport.Tables.Add(dtCompany);
                }
            }
            catch (Exception ex)
            {
                throw ex.LogException();
            }

            return(dsReport);
        }
Example #3
0
        public System.Data.DataSet GetAllFeatures(PCCS feature = null, PCCS feature1 = null, PCCS feature2 = null, PCCS specification = null, PRD_MAST productMaster = null)
        {
            System.Data.DataSet dsReport = null;
            try
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("SELECT DISTINCT 123456789012 AS SNO, A.PART_NO,   A.PART_DESC,  A.IDPK,  B.FEATURE,   ");
                sb.Append("B.SPEC_MIN,   B.SPEC_MAX,   A.QUALITY,   E.CUST_NAME,   ");
                sb.Append("(select TOP 1 p.CTRL_SPEC_MIN from pccs p where p.PART_NO=b.part_no   ");
                sb.Append("and (UPPER(p.FEATURE) LIKE 'HEAD HEI%'    ");
                sb.Append("or UPPER(p.FEATURE) LIKE 'HEAD TH%')   ");
                sb.Append(") CTRL_SPEC_MIN,      ");
                sb.Append("(select TOP 1 p.CTRL_SPEC_MAX from pccs p where p.PART_NO=b.part_no   ");
                sb.Append("and (UPPER(p.FEATURE) LIKE 'HEAD HEI%'    ");
                sb.Append("or UPPER(p.FEATURE) LIKE 'HEAD TH%')   ");
                sb.Append(") as CTRL_SPEC_MAX,      ");
                sb.Append("(select TOP 1 p.CTRL_SPEC_MIN from pccs p where p.PART_NO=b.part_no   ");
                sb.Append("and (UPPER(p.FEATURE) LIKE 'COLLAR DIA%'    ");
                sb.Append("or UPPER(p.FEATURE) LIKE 'FLANGE DIA%')   ");
                sb.Append(") AS COLLAR_FLANGE_DIA_MIN,      ");
                sb.Append("(select TOP 1 p.CTRL_SPEC_MAX from pccs p where p.PART_NO=b.part_no   ");
                sb.Append("and (UPPER(p.FEATURE) LIKE 'COLLAR DIA%'    ");
                sb.Append("or UPPER(p.FEATURE) LIKE 'FLANGE DIA%'))   ");
                sb.Append("AS COLLAR_FLANGE_DIA_MAX,      ");
                sb.Append("(select TOP 1 p.CTRL_SPEC_MIN from pccs p where p.PART_NO=b.part_no   ");
                sb.Append("and (UPPER(p.FEATURE) LIKE 'COLLAR TH%'    ");
                sb.Append("or UPPER(p.FEATURE) LIKE 'FLANGE TH%')) AS COLLAR_FLANGE_TK_MIN,      ");
                sb.Append("(select TOP 1 p.CTRL_SPEC_MAX from pccs p where p.PART_NO=b.part_no   ");
                sb.Append("and (UPPER(p.FEATURE) LIKE 'COLLAR TH%'    ");
                sb.Append("or UPPER(p.FEATURE) LIKE 'FLANGE TH%')) AS COLLAR_FLANGE_TK_MAX,      ");
                sb.Append("(SELECT TOP 1 VFORG.CC_CODE FROM V_FORGING_COST_CENTER VFORG WHERE VFORG.PART_NO=B.PART_NO) AS FORGING_COST_CENTER,      ");
                sb.Append("(SELECT TOP 1 VFORG.WIRE_SIZE_MIN FROM V_FORGING_COST_CENTER VFORG WHERE VFORG.PART_NO=B.PART_NO) AS WIRE_DIA_MIN,      ");
                sb.Append("(SELECT TOP 1 VFORG.WIRE_SIZE_MAX FROM V_FORGING_COST_CENTER VFORG WHERE VFORG.PART_NO=B.PART_NO) AS WIRE_DIA_MAX    ");
                sb.Append("FROM PRD_MAST A,   PCCS B,      ");
                sb.Append("PRD_CIREF C,   DDCI_INFO D,      ");
                sb.Append("DDCUST_MAST E    ");
                sb.Append("WHERE A.PART_NO =B.PART_NO AND    ");
                sb.Append("A.PART_NO   =C.PART_NO AND    ");

                sb.Append("C.CI_REF    =D.CI_REFERENCE AND C.CURRENT_CIREF = 1 AND D.CUST_CODE =E.CUST_CODE     ");
                //sb.Append("C.CI_REF    =D.CI_REFERENCE AND D.CUST_CODE =E.CUST_CODE     ");

                string featureSql = "";
                if (feature.IsNotNullOrEmpty() && feature.FEATURE.IsNotNullOrEmpty())
                {
                    featureSql = featureSql + "UPPER(B.FEATURE) LIKE '%" + sqlEncode(feature.FEATURE.ToUpper()) + "%' ";
                }

                string feature1Sql = "";
                if (feature1.IsNotNullOrEmpty() && feature1.FEATURE.IsNotNullOrEmpty())
                {
                    feature1Sql = feature1Sql + "UPPER(B.FEATURE) LIKE '%" + sqlEncode(feature1.FEATURE.ToUpper()) + "%' ";
                }

                if (featureSql.IsNotNullOrEmpty() && feature1Sql.IsNotNullOrEmpty())
                {
                    featureSql = featureSql + " OR " + feature1Sql;
                }
                else if (!featureSql.IsNotNullOrEmpty() && feature1Sql.IsNotNullOrEmpty())
                {
                    featureSql = feature1Sql;
                }

                string feature2Sql = "";
                if (feature2.IsNotNullOrEmpty() && feature2.FEATURE.IsNotNullOrEmpty())
                {
                    feature2Sql = feature2Sql + "UPPER(B.FEATURE) LIKE '%" + sqlEncode(feature2.FEATURE.ToUpper()) + "%' ";
                }

                if (featureSql.IsNotNullOrEmpty() && feature2Sql.IsNotNullOrEmpty())
                {
                    featureSql = featureSql + " OR " + feature2Sql;
                }
                else if (!featureSql.IsNotNullOrEmpty() && feature2Sql.IsNotNullOrEmpty())
                {
                    featureSql = feature2Sql;
                }

                if (featureSql.IsNotNullOrEmpty() && featureSql.Trim().Length > 0)
                {
                    sb.Append(" AND ( " + featureSql + ") ");
                }

                if (productMaster.IsNotNullOrEmpty() && productMaster.PART_DESC.IsNotNullOrEmpty())
                {
                    sb.Append(" AND UPPER(A.PART_DESC) LIKE '%" + sqlEncode(productMaster.PART_DESC.ToUpper()) + "%' ");
                }

                if (specification.IsNotNullOrEmpty())
                {
                    if (specification.SPEC_MIN.IsNotNullOrEmpty() && specification.SPEC_MIN.IsNumeric())
                    {
                        sb.Append(" AND B.SPEC_MIN BETWEEN '" + sqlEncode(specification.SPEC_MIN) + "' AND '" + Convert.ToDouble(specification.SPEC_MIN) + 1 + "' ");
                    }
                    else if (specification.SPEC_MIN.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND  UPPER(B.SPEC_MIN) LIKE '" + sqlEncode(specification.SPEC_MIN.ToUpper()) + "%' ");
                    }

                    if (specification.SPEC_MAX.IsNotNullOrEmpty() && specification.SPEC_MAX.IsNumeric() && 1 == 2)
                    {
                        sb.Append(" AND B.SPEC_MAX BETWEEN '" + sqlEncode(specification.SPEC_MAX) + "' AND '" + Convert.ToDouble(specification.SPEC_MAX) + 1 + "' ");
                    }
                    else if (specification.SPEC_MAX.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND  UPPER(B.SPEC_MAX) = '" + sqlEncode(specification.SPEC_MAX.ToUpper()) + "' ");
                    }
                }
                sb.Append(" ORDER BY A.PART_NO ");

                List <StringBuilder> sqlList = new List <StringBuilder>()
                {
                    sb
                };
                dsReport = Dal.GetDataSet(sqlList);
                if (dsReport.IsNotNullOrEmpty() && dsReport.Tables.IsNotNullOrEmpty() && dsReport.Tables.Count > 0)
                {
                    dsReport.Tables[0].TableName = "FEATURE_WISE_REPORT";
                    long sno = 0;
                    foreach (DataRow dataRow in dsReport.Tables[0].Rows)
                    {
                        string part_no = dataRow["PART_NO"].ToValueAsString();
                        sno++;
                        dataRow["SNO"] = sno.ToValueAsString();
                    }
                    dsReport.Tables[0].AcceptChanges();
                }
                DataTable dtCompany = new DataTable();
                dtCompany.TableName = "CompanyName";
                dtCompany.Columns.Add("Name");
                dtCompany.Columns.Add("ShortName");
                dtCompany.Columns.Add("Phone");
                dtCompany.Columns.Add("Fax");
                dtCompany.Columns.Add("Mobile");
                dtCompany.Columns.Add("EMail");
                dtCompany.Columns.Add("Title");
                dtCompany.Columns.Add("ReportTitle");
                if (dsReport.IsNotNullOrEmpty())
                {
                    dsReport.Tables.Add(dtCompany);
                }
            }
            catch (Exception ex)
            {
                throw ex.LogException();
            }

            return(dsReport);
        }
        public System.Data.DataSet GetAllCustomerPartNos(PRD_MAST productMaster = null, DDCI_INFO customerInfo = null, DataRowView customerMaster = null)
        {
            System.Data.DataSet dsReport = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                sb.Append(" ROW_NUMBER() OVER(ORDER BY A.PART_NO) AS SNO, ");
                sb.Append("  a.PART_NO, ");
                sb.Append("  a.PART_DESC, ");
                //sb.Append("  a.SIM_TO_STD_CD, ");
                //sb.Append("  a.PRD_CLASS_CD, ");
                //sb.Append("  a.MFG_STD, ");
                //sb.Append("  a.ED_CD, ");
                //sb.Append("  a.THREAD_CD, ");
                //sb.Append("  a.DIA_CD, ");
                //sb.Append("  a.QUALITY, ");
                //sb.Append("  a.BIF_PROJ, ");
                //sb.Append("  a.BIF_FORECAST, ");
                //sb.Append("  a.FINISH_WT, ");
                //sb.Append("  a.FINISH_WT_EST, ");
                //sb.Append("  a.HEAT_TREATMENT_CD, ");
                //sb.Append("  a.HEAT_TREATMENT_DESC, ");
                //sb.Append("  a.PRD_GRP_CD, ");
                //sb.Append("  a.MACHINE_CD, ");
                //sb.Append("  a.ALLOT_DATE, ");
                //sb.Append("  a.DOC_REL_DATE, ");
                //sb.Append("  a.DOC_REL_REMARKS, ");
                //sb.Append("  a.FAMILY, ");
                //sb.Append("  a.HEAD_STYLE, ");
                //sb.Append("  a.TYPE, ");
                //sb.Append("  a.APPLICATION, ");
                //sb.Append("  a.USER_CD, ");
                //sb.Append("  a.THREAD_CLASS, ");
                //sb.Append("  a.THREAD_STD, ");
                //sb.Append("  a.PG_CATEGORY, ");
                //sb.Append("  a.NOS_PER_PACK, ");
                //sb.Append("  a.SAMP_SUBMIT_DATE, ");
                //sb.Append("  a.ALLOTTED_BY, ");
                //sb.Append("  a.PSW_ST, ");
                //sb.Append("  a.DOC_REL_TYPE, ");
                //sb.Append("  a.HOLD_ME, ");
                //sb.Append("  a.ADDL_FEATURE, ");
                //sb.Append("  a.KEYWORDS, ");
                //sb.Append("  a.CANCEL_STATUS, ");
                //sb.Append("  a.IDPK AS PRD_MAST_IDPK, ");
                //sb.Append("  a.PART_CONFIG_ID, ");
                //sb.Append("  a.LOC_CODE, ");
                //sb.Append("  C.CI_REFERENCE, ");
                //sb.Append("  C.ENQU_RECD_ON, ");
                //sb.Append("  C.FR_CS_DATE, ");
                //sb.Append("  C.PROD_DESC, ");
                //sb.Append("  C.CUST_CODE, ");
                sb.Append("  C.CUST_DWG_NO ");
                //sb.Append("  C.CUST_DWG_NO_ISSUE, ");
                //sb.Append("  C.EXPORT, ");
                //sb.Append("  C.NUMBER_OFF, ");
                //sb.Append("  C.POTENTIAL, ");
                //sb.Append("  C.SFL_SHARE, ");
                //sb.Append("  C.REMARKS, ");
                //sb.Append("  C.RESPONSIBILITY, ");
                //sb.Append("  C.PENDING, ");
                //sb.Append("  C.FEASIBILITY, ");
                //sb.Append("  C.REJECT_REASON, ");
                //sb.Append("  C.LOC_CODE, ");
                //sb.Append("  C.CHEESE_WT, ");
                //sb.Append("  C.FINISH_WT, ");
                //sb.Append("  C.FINISH_CODE, ");
                //sb.Append("  C.SUGGESTED_RM, ");
                //sb.Append("  C.RM_COST, ");
                //sb.Append("  C.FINAL_COST, ");
                //sb.Append("  C.COST_NOTES, ");
                //sb.Append("  C.PROCESSED_BY, ");
                //sb.Append("  C.ORDER_DT, ");
                //sb.Append("  C.ALLOT_PART_NO, ");
                //sb.Append("  C.PART_NO_REQ_DATE, ");
                //sb.Append("  C.CUST_STD_NO, ");
                //sb.Append("  C.CUST_STD_DATE, ");
                //sb.Append("  C.AUTOPART, ");
                //sb.Append("  C.SAFTYPART, ");
                //sb.Append("  C.APPLICATION, ");
                //sb.Append("  C.STATUS, ");
                //sb.Append("  C.CUSTOMER_NEED_DT, ");
                //sb.Append("  C.MKTG_COMMITED_DT, ");
                //sb.Append("  C.PPAP_LEVEL, ");
                //sb.Append("  C.DEVL_METHOD, ");
                //sb.Append("  C.PPAP_FORGING, ");
                //sb.Append("  C.PPAP_SAMPLE, ");
                //sb.Append("  C.PACKING, ");
                //sb.Append("  C.NATURE_PACKING, ");
                //sb.Append("  C.SPL_CHAR, ");
                //sb.Append("  C.OTHER_CUST_REQ, ");
                //sb.Append("  C.ATP_DATE, ");
                //sb.Append("  C.SIMILAR_PART_NO, ");
                //sb.Append("  C.GENERAL_REMARKS, ");
                //sb.Append("  C.MONTHLY, ");
                //sb.Append("  C.MKTG_COMMITED_DATE, ");
                //sb.Append("  C.IDPK AS CI_IDPK, ");
                //sb.Append("  C.COATING_CODE, ");
                //sb.Append("  C.REALISATION, ");
                //sb.Append("  C.NO_OF_PCS, ");
                //sb.Append("  C.ZONE_CODE, ");
                //sb.Append("  C.RM_FACTOR, ");
                //sb.Append("  C.IS_COMBINED ");
                //sb.Append("FROM prd_mast a ");
                //sb.Append("LEFT OUTER JOIN prd_ciref b ");
                //sb.Append("ON a.part_no = b.part_no ");
                //sb.Append("LEFT OUTER JOIN ddci_info c ");
                //sb.Append("ON b.ci_ref = c.ci_reference");
                //sb.Append(" where  b.CURRENT_CIREF = 1 ");
                sb.Append("FROM prd_mast a ,prd_ciref b ,ddci_info c where a.part_no = b.part_no and b.ci_ref=c.ci_reference and b.CURRENT_CIREF = 1");

                if (customerInfo.IsNotNullOrEmpty() && customerInfo.CUST_DWG_NO.ToValueAsString().Trim().Length > 0)
                {
                    sb.Append(" and RTRIM(LTRIM(cust_dwg_no)) like '" + customerInfo.CUST_DWG_NO.ToValueAsString() + "%'");
                }

                if (productMaster.IsNotNullOrEmpty() && productMaster.PART_NO.ToValueAsString().Trim().Length > 0)
                {
                    sb.Append(" and RTRIM(LTRIM(a.part_no)) like '" + productMaster.PART_NO.ToValueAsString() + "%'");
                }

                if (customerMaster.IsNotNullOrEmpty() && customerMaster["CUST_CODE"].ToValueAsString().Trim().Length > 0)
                {
                    sb.Append(" and RTRIM(LTRIM(cust_code)) = '" + customerMaster["CUST_CODE"].ToValueAsString() + "'");
                }

                sb.Append(" order by a.PART_NO");

                List <StringBuilder> sqlList = new List <StringBuilder>()
                {
                    sb
                };

                dsReport = Dal.GetDataSet(sqlList);
                if (dsReport.IsNotNullOrEmpty() && dsReport.Tables.IsNotNullOrEmpty() && dsReport.Tables.Count > 0)
                {
                    dsReport.Tables[0].TableName = "PRD_MAST_CI";
                }

                DataTable dtCompany = new DataTable();
                dtCompany.TableName = "CompanyName";
                dtCompany.Columns.Add("Name");
                dtCompany.Columns.Add("ShortName");
                dtCompany.Columns.Add("Phone");
                dtCompany.Columns.Add("Fax");
                dtCompany.Columns.Add("Mobile");
                dtCompany.Columns.Add("EMail");
                dtCompany.Columns.Add("Title");
                dtCompany.Columns.Add("ReportTitle");
                if (dsReport.IsNotNullOrEmpty())
                {
                    dsReport.Tables.Add(dtCompany);
                }
            }
            catch (Exception ex)
            {
                throw ex.LogException();
            }

            return(dsReport);
        }
Example #5
0
        public System.Data.DataSet GetAllFeaturesBackUp(PCCS feature = null, PCCS feature1 = null, PCCS feature2 = null, PCCS specification = null, PRD_MAST productMaster = null)
        {
            System.Data.DataSet dsReport = null;
            try
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("SELECT DISTINCT 123456789012 AS SNO, A.PART_NO, ");
                sb.Append("  A.PART_DESC, ");
                sb.Append("  B.FEATURE, ");
                sb.Append("  B.SPEC_MIN, ");
                sb.Append("  B.SPEC_MAX, ");
                sb.Append("  A.QUALITY, ");
                sb.Append("  E.CUST_NAME, ");
                sb.Append("  B.CTRL_SPEC_MIN, ");
                sb.Append("  B.CTRL_SPEC_MAX, ");

                sb.Append("  ' ' AS COLLAR_FLANGE_DIA_MIN, ");
                sb.Append("  ' ' AS COLLAR_FLANGE_DIA_MAX, ");

                sb.Append("  ' ' AS COLLAR_FLANGE_TK_MIN, ");
                sb.Append("  ' ' AS COLLAR_FLANGE_TK_MAX, ");

                sb.Append("  ' ' AS FORGING_COST_CENTER, ");

                sb.Append("  ' ' AS WIRE_DIA_MIN, ");
                sb.Append("  ' ' AS WIRE_DIA_MAX ");

                sb.Append("FROM PRD_MAST A, ");
                sb.Append("  PCCS B, ");
                sb.Append("  PRD_CIREF C, ");
                sb.Append("  DDCI_INFO D, ");
                sb.Append("  DDCUST_MAST E ");
                sb.Append("WHERE A.PART_NO =B.PART_NO ");
                sb.Append("AND A.PART_NO   =C.PART_NO ");

                sb.Append("AND C.CI_REF    =D.CI_REFERENCE AND C.CURRENT_CIREF = 1 ");
                //sb.Append("AND C.CI_REF    =D.CI_REFERENCE ");

                sb.Append("AND D.CUST_CODE =E.CUST_CODE ");
                //sb.Append("AND A.PART_NO   ='M03920' ");

                string featureSql = "";
                if (feature.IsNotNullOrEmpty() && feature.FEATURE.IsNotNullOrEmpty())
                {
                    featureSql = featureSql + "UPPER(B.FEATURE) LIKE '%" + feature.FEATURE.ToUpper() + "%' ";
                }

                string feature1Sql = "";
                if (feature1.IsNotNullOrEmpty() && feature1.FEATURE.IsNotNullOrEmpty())
                {
                    feature1Sql = feature1Sql + "UPPER(B.FEATURE) LIKE '%" + feature1.FEATURE.ToUpper() + "%' ";
                }

                if (featureSql.IsNotNullOrEmpty() && feature1Sql.IsNotNullOrEmpty())
                {
                    featureSql = featureSql + " OR " + feature1Sql;
                }
                else if (!featureSql.IsNotNullOrEmpty() && feature1Sql.IsNotNullOrEmpty())
                {
                    featureSql = feature1Sql;
                }

                string feature2Sql = "";
                if (feature2.IsNotNullOrEmpty() && feature2.FEATURE.IsNotNullOrEmpty())
                {
                    feature2Sql = feature2Sql + "UPPER(B.FEATURE) LIKE '%" + feature2.FEATURE.ToUpper() + "%' ";
                }

                if (featureSql.IsNotNullOrEmpty() && feature2Sql.IsNotNullOrEmpty())
                {
                    featureSql = featureSql + " OR " + feature2Sql;
                }
                else if (!featureSql.IsNotNullOrEmpty() && feature2Sql.IsNotNullOrEmpty())
                {
                    featureSql = feature2Sql;
                }

                if (featureSql.IsNotNullOrEmpty() && featureSql.Trim().Length > 0)
                {
                    sb.Append(" AND ( " + featureSql + ") ");
                }

                if (productMaster.IsNotNullOrEmpty() && productMaster.PART_DESC.IsNotNullOrEmpty())
                {
                    sb.Append(" AND UPPER(A.PART_DESC) LIKE '%" + productMaster.PART_DESC.ToUpper() + "%' ");
                }

                if (specification.IsNotNullOrEmpty())
                {
                    if (specification.SPEC_MIN.IsNotNullOrEmpty() && specification.SPEC_MIN.IsNumeric())
                    {
                        sb.Append(" AND B.SPEC_MIN BETWEEN '" + specification.SPEC_MIN + "' AND '" + Convert.ToDouble(specification.SPEC_MIN) + 1 + "' ");
                    }
                    else if (specification.SPEC_MIN.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND  UPPER(B.SPEC_MIN) LIKE '" + specification.SPEC_MIN.ToUpper() + "%' ");
                    }

                    if (specification.SPEC_MAX.IsNotNullOrEmpty() && specification.SPEC_MAX.IsNumeric() && 1 == 2)
                    {
                        sb.Append(" AND B.SPEC_MAX BETWEEN '" + specification.SPEC_MAX + "' AND '" + Convert.ToDouble(specification.SPEC_MAX) + 1 + "' ");
                    }
                    else if (specification.SPEC_MAX.IsNotNullOrEmpty())
                    {
                        sb.Append(" AND  UPPER(B.SPEC_MAX) = '" + specification.SPEC_MAX.ToUpper() + "' ");
                    }
                }

                sb.Append(" ORDER BY A.PART_NO ");

                List <StringBuilder> sqlList = new List <StringBuilder>()
                {
                    sb
                };

                dsReport = Dal.GetDataSet(sqlList);
                if (dsReport.IsNotNullOrEmpty() && dsReport.Tables.IsNotNullOrEmpty() && dsReport.Tables.Count > 0)
                {
                    dsReport.Tables[0].TableName = "FEATURE_WISE_REPORT";

                    List <PCCS> lstAllPCCS = (from row in DB.PCCS
                                              where (
                                                  row.FEATURE.ToUpper().StartsWith("HEAD HEI") ||
                                                  row.FEATURE.ToUpper().StartsWith("HEAD TH") ||
                                                  row.FEATURE.ToUpper().StartsWith("COLLAR DIA") ||
                                                  row.FEATURE.ToUpper().StartsWith("FLANGE DIA") ||
                                                  row.FEATURE.ToUpper().StartsWith("COLLAR TH") ||
                                                  row.FEATURE.ToUpper().StartsWith("FLANGE TH"))
                                              select row).ToList <PCCS>();

                    List <PCCS> lstPCCS = null;


                    List <V_FORGING_COST_CENTER> lstAllFORGING_COST_CENTER = (from row in DB.V_FORGING_COST_CENTER
                                                                              select row).ToList <V_FORGING_COST_CENTER>();

                    List <V_FORGING_COST_CENTER> lstFORGING_COST_CENTER = null;

                    //SELECT PC.CC_CODE,
                    //  PC.PART_NO,
                    //  PC.WIRE_SIZE_MIN,
                    //  PC.WIRE_SIZE_MAX
                    //FROM PROCESS_CC PC
                    //WHERE PC.PART_NO = 'M92910'
                    //AND PC.SEQ_NO   IN
                    //  (SELECT a.SEQ_NO
                    //  FROM PROCESS_SHEET a
                    //  WHERE a.OPN_DESC LIKE 'FORG%'
                    //  AND A.PART_NO   = PC.PART_NO
                    //  AND A.PART_NO   = 'M92910'
                    //  AND a.ROUTE_NO IN
                    //    (SELECT DISTINCT(B.ROUTE_NO)
                    //    FROM PROCESS_MAIN B
                    //    WHERE B.CURRENT_PROC=1
                    //    AND B.PART_NO       =a.PART_NO
                    //    AND B.PART_NO       = PC.PART_NO
                    //    AND B.PART_NO       = 'M92910'
                    //    )
                    //  )
                    //AND PC.ROUTE_NO IN
                    //  (SELECT DISTINCT(C.ROUTE_NO)
                    //  FROM PROCESS_MAIN C
                    //  WHERE C.CURRENT_PROC=1
                    //  AND C.PART_NO       = PC.PART_NO
                    //  AND C.PART_NO       = 'M92910'
                    //  )
                    long sno = 0;
                    foreach (DataRow dataRow in dsReport.Tables[0].Rows)
                    {
                        string part_no = dataRow["PART_NO"].ToValueAsString();

                        sno++;
                        dataRow["SNO"] = sno.ToValueAsString();

                        lstPCCS = (from row in lstAllPCCS.AsEnumerable()
                                   where row.PART_NO == part_no && (row.FEATURE.ToUpper().StartsWith("HEAD HEI") ||
                                                                    row.FEATURE.ToUpper().StartsWith("HEAD TH"))
                                   select row).ToList <PCCS>();
                        if (lstPCCS.IsNotNullOrEmpty() && lstPCCS.Count > 0)
                        {
                            dataRow["CTRL_SPEC_MIN"] = lstPCCS[0].CTRL_SPEC_MIN;
                            dataRow["CTRL_SPEC_MAX"] = lstPCCS[0].CTRL_SPEC_MAX;
                        }

                        lstPCCS = (from row in lstAllPCCS.AsEnumerable()
                                   where row.PART_NO == part_no && (row.FEATURE.ToUpper().StartsWith("COLLAR DIA") ||
                                                                    row.FEATURE.ToUpper().StartsWith("FLANGE DIA"))
                                   select row).ToList <PCCS>();
                        if (lstPCCS.IsNotNullOrEmpty() && lstPCCS.Count > 0)
                        {
                            dataRow["COLLAR_FLANGE_DIA_MIN"] = lstPCCS[0].CTRL_SPEC_MIN;
                            dataRow["COLLAR_FLANGE_DIA_MAX"] = lstPCCS[0].CTRL_SPEC_MAX;
                        }

                        lstPCCS = (from row in lstAllPCCS.AsEnumerable()
                                   where row.PART_NO == part_no && (row.FEATURE.ToUpper().StartsWith("COLLAR TH") ||
                                                                    row.FEATURE.ToUpper().StartsWith("FLANGE TH"))
                                   select row).ToList <PCCS>();
                        if (lstPCCS.IsNotNullOrEmpty() && lstPCCS.Count > 0)
                        {
                            dataRow["COLLAR_FLANGE_TK_MIN"] = lstPCCS[0].CTRL_SPEC_MIN;
                            dataRow["COLLAR_FLANGE_TK_MAX"] = lstPCCS[0].CTRL_SPEC_MAX;
                        }

                        lstFORGING_COST_CENTER = (from row in lstAllFORGING_COST_CENTER.AsEnumerable()
                                                  where row.PART_NO == part_no
                                                  select row).ToList <V_FORGING_COST_CENTER>();
                        if (lstFORGING_COST_CENTER.IsNotNullOrEmpty() && lstFORGING_COST_CENTER.Count > 0)
                        {
                            dataRow["FORGING_COST_CENTER"] = lstFORGING_COST_CENTER[0].CC_CODE.ToValueAsString();
                            dataRow["WIRE_DIA_MIN"]        = lstFORGING_COST_CENTER[0].WIRE_SIZE_MIN.ToValueAsString();
                            dataRow["WIRE_DIA_MAX"]        = lstFORGING_COST_CENTER[0].WIRE_SIZE_MAX.ToValueAsString();
                        }

                        //sb = new StringBuilder();
                        //sb.Append("SELECT CC_CODE, ");
                        //sb.Append("  WIRE_SIZE_MIN, ");
                        //sb.Append("  WIRE_SIZE_MAX ");
                        //sb.Append("FROM PROCESS_CC ");
                        //sb.Append("WHERE PART_NO='" + part_no + "' ");
                        //sb.Append("AND SEQ_NO   = ");
                        //sb.Append("  (SELECT a.SEQ_NO ");
                        //sb.Append("  FROM PROCESS_SHEET a ");
                        //sb.Append("  WHERE a.OPN_DESC LIKE 'FORG%' ");
                        //sb.Append("  AND a.ROUTE_NO= ");
                        //sb.Append("    (SELECT DISTINCT(ROUTE_NO) ");
                        //sb.Append("    FROM PROCESS_MAIN B ");
                        //sb.Append("    WHERE B.CURRENT_PROC=1 ");
                        //sb.Append("    AND B.PART_NO       =a.PART_NO ");
                        //sb.Append("    AND B.PART_NO       ='" + part_no + "' ");
                        //sb.Append("    ) ");
                        //sb.Append("  ) ");
                        //sb.Append("AND ROUTE_NO= ");
                        //sb.Append("  (SELECT DISTINCT(ROUTE_NO) ");
                        //sb.Append("  FROM PROCESS_MAIN B ");
                        //sb.Append("  WHERE B.CURRENT_PROC=1 ");
                        //sb.Append("  AND B.PART_NO       =PART_NO ");
                        //sb.Append("  AND B.PART_NO       ='" + part_no + "' ");
                        //sb.Append("  )");
                        //sqlList = new List<StringBuilder>() { sb };

                        //DataSet dsResult = Dal.GetDataSet(sqlList);

                        //if (dsResult.IsNotNullOrEmpty() && dsResult.Tables.IsNotNullOrEmpty() && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
                        //{
                        //    DataRow resultRow = dsResult.Tables[0].Rows[0];
                        //    dataRow["FORGING_COST_CENTER"] = resultRow["CC_CODE"].ToValueAsString();
                        //    dataRow["WIRE_DIA_MIN"] = resultRow["WIRE_SIZE_MIN"].ToValueAsString();
                        //    dataRow["WIRE_DIA_MAX"] = resultRow["WIRE_SIZE_MAX"].ToValueAsString();
                        //    dataRow.AcceptChanges();
                        //}

                        dataRow.AcceptChanges();
                    }
                    dsReport.Tables[0].AcceptChanges();
                }
                DataTable dtCompany = new DataTable();
                dtCompany.TableName = "CompanyName";
                dtCompany.Columns.Add("Name");
                dtCompany.Columns.Add("ShortName");
                dtCompany.Columns.Add("Phone");
                dtCompany.Columns.Add("Fax");
                dtCompany.Columns.Add("Mobile");
                dtCompany.Columns.Add("EMail");
                dtCompany.Columns.Add("Title");
                dtCompany.Columns.Add("ReportTitle");
                if (dsReport.IsNotNullOrEmpty())
                {
                    dsReport.Tables.Add(dtCompany);
                }
            }
            catch (Exception ex)
            {
                throw ex.LogException();
            }

            return(dsReport);
        }