public List <PROCESS_MAIN> GetProcessSheetMainDetailsByPartNumber(PRD_MAST paramEntity = null) { List <PROCESS_MAIN> lstEntity = null; try { if (!DB.IsNotNullOrEmpty()) { return(lstEntity); } if (paramEntity.IsNotNullOrEmpty() && paramEntity.PART_NO.IsNotNullOrEmpty()) { lstEntity = (from row in DB.PROCESS_MAIN where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) && row.PART_NO == paramEntity.PART_NO select row).ToList <PROCESS_MAIN>(); } else { lstEntity = (from row in DB.PROCESS_MAIN where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) select row).ToList <PROCESS_MAIN>(); } } catch (Exception ex) { ex.LogException(); } return(lstEntity); }
public List <PRD_MAST> GetPartNumber(PRD_MAST paramEntity = null) { List <PRD_MAST> lstEntity = null; try { if (!DB.IsNotNullOrEmpty()) { return(lstEntity); } if (paramEntity.IsNotNullOrEmpty() && paramEntity.IDPK.IsNotNullOrEmpty()) { lstEntity = (from row in DB.PRD_MAST where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) && row.IDPK == paramEntity.IDPK select row).ToList <PRD_MAST>(); } else { lstEntity = (from row in DB.PRD_MAST where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) select row).ToList <PRD_MAST>(); } if (lstEntity.IsNotNullOrEmpty()) { lstEntity = lstEntity.GroupBy(row => row.PART_NO).Select(row => row.First()).Distinct <PRD_MAST>().OrderBy(row => row.PART_NO).ToList <PRD_MAST>(); } } catch (Exception ex) { throw ex.LogException(); } return(lstEntity); }
public List <PRD_MAST> GetProductMasterDetailsByPartNumber(PRD_MAST paramEntity = null) { List <PRD_MAST> lstEntity = null; try { if (!DB.IsNotNullOrEmpty()) { return(lstEntity); } if (paramEntity.IsNotNullOrEmpty() && paramEntity.IDPK.IsNotNullOrEmpty()) { lstEntity = (from row in DB.PRD_MAST where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) && row.IDPK == paramEntity.IDPK select row).ToList <PRD_MAST>(); } else { lstEntity = (from row in DB.PRD_MAST where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) select row).ToList <PRD_MAST>(); } } catch (Exception ex) { ex.LogException(); } return(lstEntity); }
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); }
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); }
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); }
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 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); }