public DataTable GetFeatureDataTable(PCCS paramEntity = null) { DataTable lstEntity = null; try { StringBuilder sb = new StringBuilder(); sb.Append("SELECT DISTINCT FEATURE FROM PCCS "); List <StringBuilder> sqlList = new List <StringBuilder>() { sb }; DataSet dsReport = Dal.GetDataSet(sqlList); if (dsReport.IsNotNullOrEmpty() && dsReport.Tables.IsNotNullOrEmpty() && dsReport.Tables.Count > 0) { lstEntity = dsReport.Tables[0]; } } catch (Exception ex) { throw ex.LogException(); } return(lstEntity); }
private void RefreshSubmitCommand() { try { Progress.ProcessingText = PDMsg.Search; Progress.Start(); Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait; PCCS feature = new PCCS() { FEATURE = MandatoryFields.FEATURE }; PCCS feature1 = new PCCS() { FEATURE = MandatoryFields.FEATURE1 }; PCCS feature2 = new PCCS() { FEATURE = MandatoryFields.FEATURE2 }; PCCS specification = new PCCS() { SPEC_MIN = MandatoryFields.SPEC_MIN, SPEC_MAX = MandatoryFields.SPEC_MAX }; PRD_MAST productMaster = new PRD_MAST() { PART_DESC = MandatoryFields.PART_DESC }; DsReport = bll.GetAllFeatures(feature, feature1, feature2, specification, productMaster); DataSet dsReport = DsReport; MandatoryFields.GRID_TITLE = REPORT_TITLE; if (!dsReport.IsNotNullOrEmpty() || !dsReport.Tables.IsNotNullOrEmpty() || dsReport.Tables.Count < 0) { Mouse.OverrideCursor = null; ShowInformationMessage(PDMsg.NoRecordsPrint); return; } dsReport.DataSetName = REPORT_NAME; MandatoryFields.GridData = dsReport.Tables[0].DefaultView; MandatoryFields.GRID_TITLE = REPORT_TITLE + " - " + MandatoryFields.GridData.Table.Rows.Count + " Entries"; DataRow row = dsReport.Tables[1].Rows.Add(); row["ReportTitle"] = REPORT_TITLE; row.AcceptChanges(); dsReport.Tables[1].AcceptChanges(); Progress.End(); } catch (Exception ex) { Mouse.OverrideCursor = null; ex.LogException(); } finally { Mouse.OverrideCursor = null; } //dsReport.WriteXmlSchema("D:\\" + dsReport.DataSetName + ".xml"); }
public List <PCCS> GetFeature(PCCS paramEntity = null) { List <PCCS> lstEntity = null; try { if (!DB.IsNotNullOrEmpty()) { return(lstEntity); } if (paramEntity.IsNotNullOrEmpty() && paramEntity.PART_NO.IsNotNullOrEmpty()) { lstEntity = (from row in DB.PCCS where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) && row.PART_NO == paramEntity.PART_NO select row).ToList <PCCS>(); } else { lstEntity = (from row in DB.PCCS where (Convert.ToBoolean(Convert.ToInt16(row.DELETE_FLAG)) == false || row.DELETE_FLAG == null) select row).Distinct <PCCS>().ToList <PCCS>(); } if (lstEntity.IsNotNullOrEmpty()) { lstEntity = lstEntity.GroupBy(row => row.FEATURE).Select(row => row.First()).OrderBy(row => row.FEATURE).Distinct <PCCS>().ToList <PCCS>(); } } catch (Exception ex) { throw ex.LogException(); } return(lstEntity); }
public bool DeletePccs(string partNo, decimal routeNo, decimal seqNo, double sno) { try { if (partNo.IsNotNullOrEmpty() && routeNo.IsNotNullOrEmpty()) { PCCS pccs = (from c in DB.PCCS where c.PART_NO == partNo && c.ROUTE_NO == routeNo && c.SEQ_NO == seqNo && c.SNO == sno select c).FirstOrDefault <PCCS>(); if (pccs != null) { if (pccs.DELETE_FLAG == true) { pccs.DELETE_FLAG = false; } else { pccs.DELETE_FLAG = true; } pccs.UPDATED_DATE = DateTime.Now; pccs.UPDATED_BY = userInformation.UserName; DB.SubmitChanges(); return(true); } else if (pccs == null) { return(false); } } return(true); } catch (System.Data.Linq.ChangeConflictException) { DB.ChangeConflicts.ResolveAll(System.Data.Linq.RefreshMode.KeepChanges); } catch (Exception ex) { ex.LogException(); return(false); } return(true); }
//private readonly ICommand mousecombocommand; //public ICommand MouseComboCommand { get { return this.mousecombocommand; } } public ReportMISFeatureWiseViewModel(UserInformation userInformation, WPF.MDI.MdiChild mdiChild, PCCS feature = null, PCCS feature1 = null, PCCS feature2 = null, PCCS specification = null, PRD_MAST productMaster = null, bool refreshOnLoad = false, string title = REPORT_TITLE) { _userInformation = userInformation; this.mdiChild = mdiChild; bll = new ReportMISFeatureWise(userInformation); MandatoryFields = new ReportMISFeatureWiseModel(); MandatoryFields.GRID_TITLE = REPORT_TITLE; this.printCommand = new DelegateCommand(this.PrintSubmitCommand); //this.mousecombocommand = new DelegateCommand(this.MouseDblClick); this.refreshCommand = new DelegateCommand(this.RefreshSubmitCommand); this.exportToExcelCommand = new DelegateCommand(this.ExportToExcelSubmitCommand); //FeatureDataSource = bll.GetFeature().ToDataTable<PCCS>().DefaultView; FeatureDataSource = bll.GetFeatureDataTable().DefaultView; this._featureSelectedItemChangedCommand = new DelegateCommand(this.FeatureChanged); FeatureDropDownItems = new ObservableCollection <DropdownColumns>() { new DropdownColumns() { ColumnName = "FEATURE", ColumnDesc = "Feature", ColumnWidth = "1*" }, }; Feature1DataSource = FeatureDataSource.Table.Copy().DefaultView; this._feature1SelectedItemChangedCommand = new DelegateCommand(this.Feature1Changed); Feature1DropDownItems = new ObservableCollection <DropdownColumns>() { new DropdownColumns() { ColumnName = "FEATURE", ColumnDesc = "Feature", ColumnWidth = "1*" }, }; Feature2DataSource = FeatureDataSource.Table.Copy().DefaultView; this._feature2SelectedItemChangedCommand = new DelegateCommand(this.Feature2Changed); Feature2DropDownItems = new ObservableCollection <DropdownColumns>() { new DropdownColumns() { ColumnName = "FEATURE", ColumnDesc = "Feature", ColumnWidth = "1*" }, }; }
private void ExportToExcelSubmitCommand() { try { PCCS feature = new PCCS() { FEATURE = MandatoryFields.FEATURE }; PCCS feature1 = new PCCS() { FEATURE = MandatoryFields.FEATURE1 }; PCCS feature2 = new PCCS() { FEATURE = MandatoryFields.FEATURE2 }; PCCS specification = new PCCS() { SPEC_MIN = MandatoryFields.SPEC_MIN, SPEC_MAX = MandatoryFields.SPEC_MAX }; PRD_MAST productMaster = new PRD_MAST() { PART_DESC = MandatoryFields.PART_DESC }; DataSet dsReport = DsReport; if (!DsReport.IsNotNullOrEmpty() || !DsReport.Tables.IsNotNullOrEmpty() || DsReport.Tables.Count == 0) { dsReport = bll.GetAllFeatures(feature, feature1, feature2, specification, productMaster); } //MandatoryFields.GRID_TITLE = REPORT_TITLE; if (!dsReport.IsNotNullOrEmpty() || !dsReport.Tables.IsNotNullOrEmpty() || dsReport.Tables.Count < 0) { ShowInformationMessage(PDMsg.NoRecordsPrint); return; } //dsReport.DataSetName = REPORT_NAME; //MandatoryFields.GridData = dsReport.Tables[0].DefaultView; //MandatoryFields.GRID_TITLE = REPORT_TITLE + " - " + MandatoryFields.GridData.Table.Rows.Count + " Entries"; //DataRow row = dsReport.Tables[1].Rows.Add(); //row["ReportTitle"] = REPORT_TITLE; //row.AcceptChanges(); //dsReport.Tables[1].AcceptChanges(); //dsReport.WriteXmlSchema("D:\\" + dsReport.DataSetName + ".xml"); //frmReportViewer reportViewer = new frmReportViewer(dsReport, REPORT_NAME, CrystalDecisions.Shared.ExportFormatType.ExcelWorkbook); //if (!reportViewer.ReadyToShowReport) return; string reportName = GetReportPath() + "FeatureWiseExport.rpt"; if (BindReport(dsReport, reportName, CrystalDecisions.Shared.ExportFormatType.ExcelWorkbook) == true) // If condition added by Jeyan { ShowInformationMessage("Exported to Excel File Successfully."); } //reportViewer.ShowDialog(); } catch (Exception ex) { ShowWarningMessage(ex.Message, MessageBoxButton.OK); } }
private void PrintSubmitCommand() { Progress.ProcessingText = PDMsg.Load; Progress.Start(); PCCS feature = new PCCS() { FEATURE = MandatoryFields.FEATURE }; PCCS feature1 = new PCCS() { FEATURE = MandatoryFields.FEATURE1 }; PCCS feature2 = new PCCS() { FEATURE = MandatoryFields.FEATURE2 }; PCCS specification = new PCCS() { SPEC_MIN = MandatoryFields.SPEC_MIN, SPEC_MAX = MandatoryFields.SPEC_MAX }; PRD_MAST productMaster = new PRD_MAST() { PART_DESC = MandatoryFields.PART_DESC }; DataSet dsReport = DsReport; if (!DsReport.IsNotNullOrEmpty() || !DsReport.Tables.IsNotNullOrEmpty() || DsReport.Tables.Count == 0) { RefreshSubmitCommand(); } //if (!dsReport.IsNotNullOrEmpty() || !dsReport.Tables.IsNotNullOrEmpty() || dsReport.Tables.Count < 0) //{ // ShowInformationMessage(PDMsg.NoRecordsPrint); // return; //} if (MandatoryFields.GridData.IsNotNullOrEmpty()) { if (MandatoryFields.GridData.Count <= 0) { ShowInformationMessage(PDMsg.NoRecordsPrint); return; } } //dsReport.DataSetName = REPORT_NAME; //MandatoryFields.GridData = dsReport.Tables[0].DefaultView; //MandatoryFields.GRID_TITLE = REPORT_TITLE + " - " + MandatoryFields.GridData.Table.Rows.Count + " Entries"; //DataRow row = dsReport.Tables[1].Rows.Add(); //row["ReportTitle"] = REPORT_TITLE; //row.AcceptChanges(); //dsReport.Tables[1].AcceptChanges(); //dsReport.WriteXmlSchema("D:\\" + dsReport.DataSetName + ".xml"); frmMISInputBox inp = new frmMISInputBox("Report Title", "Enter the Title of the Report"); inp.ShowDialog(); //inp.Txt_InputBox.Text; frmReportViewer reportViewer = new frmReportViewer(dsReport, REPORT_NAME, inp.Txt_InputBox.Text); Progress.End(); if (!reportViewer.ReadyToShowReport) { return; } reportViewer.ShowDialog(); }
public List <PCCS> GetPCCSDetailsByPartNumber(PCCS paramEntity = null) { List <PCCS> lstEntity = null; try { if (!DB.IsNotNullOrEmpty()) { return(lstEntity); } if (paramEntity.IsNotNullOrEmpty() && paramEntity.PART_NO.IsNotNullOrEmpty()) { lstEntity = (from row in DB.PCCS where row.PART_NO == paramEntity.PART_NO orderby row.SNO select row).ToList <PCCS>(); } else { lstEntity = (from row in DB.PCCS orderby row.SNO select row).ToList <PCCS>(); } if (paramEntity.IsNotNullOrEmpty() && paramEntity.ROUTE_NO.IsNotNullOrEmpty() && paramEntity.SEQ_NO.IsNotNullOrEmpty() && lstEntity.IsNotNullOrEmpty()) { double sno = 1; lstEntity = (from row in lstEntity.AsEnumerable() where row.PART_NO == paramEntity.PART_NO && row.ROUTE_NO == paramEntity.ROUTE_NO && row.SEQ_NO == paramEntity.SEQ_NO orderby row.SNO select new PCCS() { PART_NO = row.PART_NO, ROUTE_NO = row.ROUTE_NO, SEQ_NO = row.SEQ_NO, SNO = sno++, ISR_NO = row.ISR_NO, FEATURE = row.FEATURE, CLASS = row.CLASS, SPEC = row.SPEC, CONTROL_SPEC = row.CONTROL_SPEC, DEPT_RESP = row.DEPT_RESP, FREQ_OF_INSP = row.FREQ_OF_INSP, GAUGES_USED = row.GAUGES_USED, GAUGE_RR = row.GAUGE_RR, INPROCESS_CONTROL = row.INPROCESS_CONTROL, METHOD_OF_STUDY = row.METHOD_OF_STUDY, SAMPLE_SIZE = row.SAMPLE_SIZE, CP = row.CP, CPK = row.CPK, PRD_PROC_COMP = row.PRD_PROC_COMP, FREQ_OF_STUDY = row.FREQ_OF_STUDY, COMMENTS = row.COMMENTS, SPEC_MIN = row.SPEC_MIN, SPEC_MAX = row.SPEC_MAX, CTRL_SPEC_MIN = row.CTRL_SPEC_MIN, CTRL_SPEC_MAX = row.CTRL_SPEC_MAX, CONTROL_METHOD = row.CONTROL_METHOD, REACTION_PLAN = row.REACTION_PLAN, PROCESS_FEATURE = row.PROCESS_FEATURE, //SPEC_CHAR = row.SPEC_CHAR, SPEC_CHAR = (row.SPEC_CHAR.IsNotNullOrEmpty() && row.SPEC_CHAR.ToValueAsString().Length > 1) ? Convert.ToString(row.SPEC_CHAR).Trim().Substring(0, 1) : "", ROWID = row.ROWID, DELETE_FLAG = row.DELETE_FLAG, ENTERED_DATE = row.ENTERED_DATE, ENTERED_BY = row.ENTERED_BY, UPDATED_DATE = row.UPDATED_DATE, UPDATED_BY = row.UPDATED_BY, }).ToList <PCCS>(); } } catch (Exception ex) { ex.LogException(); } return(lstEntity); }
public bool SavePccs(PCCSModel pccsModel, ref string typ) { try { DataTable dtPccsDetails = new DataTable(); DataTable dtPccsRevisonDetails = new DataTable(); bool _status = false; if (pccsModel.PartNo.IsNotNullOrEmpty() && pccsModel.SeqNo.IsNotNullOrEmpty() && pccsModel.RouteNo.IsNotNullOrEmpty()) { if (pccsModel.PCCSDetails.IsNotNullOrEmpty() && pccsModel.PCCSDetails.Count > 0) { //Save PCCS Details DataTable dt = new DataTable(); List <PCCS> lstexistingDatas = new List <PCCS>(); dtPccsDetails = pccsModel.PCCSDetails.Table; lstexistingDatas = ((from c in DB.PCCS where c.PART_NO == pccsModel.PartNo && c.ROUTE_NO == pccsModel.RouteNo && c.SEQ_NO == Convert.ToDecimal(pccsModel.SeqNo) select c).ToList()); if (lstexistingDatas.Count > 0) { DB.PCCS.DeleteAllOnSubmit(lstexistingDatas); DB.SubmitChanges(); } for (int i = 0; i < dtPccsDetails.Rows.Count; i++) { PCCS pccs = null; try { if (dtPccsDetails.Rows[i]["REACTION_PLAN"].ToString() != "" || dtPccsDetails.Rows[i]["CONTROL_METHOD"].ToString() != "" || dtPccsDetails.Rows[i]["FREQ_OF_INSP"].ToString() != "" || dtPccsDetails.Rows[i]["GAUGES_USED"].ToString() != "" || dtPccsDetails.Rows[i]["DEPT_RESP"].ToString() != "" || dtPccsDetails.Rows[i]["SPEC_CHAR"].ToString() != "" || dtPccsDetails.Rows[i]["CTRL_SPEC_MAX"].ToString() != "" || dtPccsDetails.Rows[i]["CTRL_SPEC_MIN"].ToString() != "" || dtPccsDetails.Rows[i]["SPEC_MAX"].ToString() != "" || dtPccsDetails.Rows[i]["SPEC_MIN"].ToString() != "" || dtPccsDetails.Rows[i]["PROCESS_FEATURE"].ToString() != "" || dtPccsDetails.Rows[i]["ISR_NO"].ToValueAsString().ToDecimalValue() != 0 || dtPccsDetails.Rows[i]["FEATURE"].ToString() != "") { pccs = new PCCS(); pccs.PART_NO = pccsModel.PartNo.ToString(); pccs.ROUTE_NO = pccsModel.RouteNo.ToValueAsString().ToDecimalValue(); pccs.SEQ_NO = pccsModel.SeqNo.IsNumeric() ? Convert.ToDecimal(pccsModel.SeqNo) : 0; //pccs.SNO = dtPccsDetails.Rows[i]["SNO"].ToString().IsNumeric() ? Convert.ToDouble(dtPccsDetails.Rows[i]["SNO"].ToString()) : 0; pccs.SNO = dtPccsDetails.Rows[i]["SNO"].ToValueAsString().ToDoubleValue(); pccs.ISR_NO = dtPccsDetails.Rows[i]["ISR_NO"].ToValueAsString().ToDecimalValue(); pccs.FEATURE = dtPccsDetails.Rows[i]["FEATURE"].ToString(); pccs.PROCESS_FEATURE = dtPccsDetails.Rows[i]["PROCESS_FEATURE"].ToString(); pccs.SPEC_MIN = dtPccsDetails.Rows[i]["SPEC_MIN"].ToString(); pccs.SPEC_MAX = dtPccsDetails.Rows[i]["SPEC_MAX"].ToString(); pccs.CTRL_SPEC_MIN = dtPccsDetails.Rows[i]["CTRL_SPEC_MIN"].ToString(); pccs.CTRL_SPEC_MAX = dtPccsDetails.Rows[i]["CTRL_SPEC_MAX"].ToString(); pccs.SPEC_CHAR = dtPccsDetails.Rows[i]["SPEC_CHAR"].ToString(); pccs.DEPT_RESP = dtPccsDetails.Rows[i]["DEPT_RESP"].ToString(); pccs.GAUGES_USED = dtPccsDetails.Rows[i]["GAUGES_USED"].ToString(); //pccs.SAMPLE_SIZE = dtPccsDetails.Rows[i]["SAMPLE_SIZE"].ToString().IsNumeric() ? Convert.ToDecimal(dtPccsDetails.Rows[i]["SAMPLE_SIZE"].ToString()) : 0; pccs.SAMPLE_SIZE = dtPccsDetails.Rows[i]["SAMPLE_SIZE"].ToValueAsString(); pccs.FREQ_OF_INSP = dtPccsDetails.Rows[i]["FREQ_OF_INSP"].ToString(); pccs.CONTROL_METHOD = dtPccsDetails.Rows[i]["CONTROL_METHOD"].ToString(); pccs.REACTION_PLAN = dtPccsDetails.Rows[i]["REACTION_PLAN"].ToString(); pccs.DELETE_FLAG = false; pccs.ENTERED_DATE = DateTime.Now; pccs.ENTERED_BY = userInformation.UserName; pccs.ROWID = Guid.NewGuid(); DB.PCCS.InsertOnSubmit(pccs); DB.SubmitChanges(); typ = "INS"; } } catch (Exception ex) { ex.LogException(); DB.PCCS.DeleteOnSubmit(pccs); } } _status = true; //Save PCCS Revison Details // GetPccsRevisonDetails(pccsModel, pccsModel.PartNo, pccsModel.RouteNo); dtPccsRevisonDetails = pccsModel.PccsRevisionDetails.Table; //PART_NO,ROUTE_NO,ISSUE_NO,ISSUE_DATE,ISSUE_ALTER,COMPILED_BY decimal routeNo = pccsModel.RouteNo.ToValueAsString().ToDecimalValue(); List <PCCS_ISSUE> lstexistingDatasPccsIssue = new List <PCCS_ISSUE>(); dtPccsDetails = pccsModel.PCCSDetails.Table; lstexistingDatasPccsIssue = ((from c in DB.PCCS_ISSUE where c.PART_NO == pccsModel.PartNo && c.ROUTE_NO == pccsModel.RouteNo select c).ToList()); if (lstexistingDatasPccsIssue.Count > 0) { DB.PCCS_ISSUE.DeleteAllOnSubmit(lstexistingDatasPccsIssue); DB.SubmitChanges(); } for (int i = 0; i < dtPccsRevisonDetails.Rows.Count; i++) { if (dtPccsRevisonDetails.Rows[i]["ROUTE_NO"].IsNotNullOrEmpty()) { routeNo = dtPccsRevisonDetails.Rows[i]["ROUTE_NO"].ToString().ToDecimalValue(); } else { routeNo = pccsModel.RouteNo.ToValueAsString().ToDecimalValue(); } //PCCS_ISSUE pccsRevison = (from c in DB.PCCS_ISSUE // where c.PART_NO == pccsModel.PartNo && c.ROUTE_NO == routeNo && c.ISSUE_NO == (i + 1).ToString() // select c).FirstOrDefault<PCCS_ISSUE>(); //if (!pccsRevison.IsNotNullOrEmpty()) //{ PCCS_ISSUE pccsRevison = null; if (routeNo == pccsModel.RouteNo) { try { if (dtPccsRevisonDetails.Rows[i]["ISSUE_DATE"].ToString().IsNotNullOrEmpty() || dtPccsRevisonDetails.Rows[i]["ISSUE_ALTER"].ToString().IsNotNullOrEmpty() || dtPccsRevisonDetails.Rows[i]["COMPILED_BY"].ToString().IsNotNullOrEmpty()) { pccsRevison = new PCCS_ISSUE() { PART_NO = pccsModel.PartNo.ToString(), ROUTE_NO = pccsModel.RouteNo.ToValueAsString().ToDecimalValue(), ISSUE_NO = dtPccsRevisonDetails.Rows[i]["ISSUE_NO"].ToString(), // ISSUE_DATE = dtPccsRevisonDetails.Rows[i]["ISSUE_DATE"].ToString().ToDateTimeValue(), ISSUE_ALTER = dtPccsRevisonDetails.Rows[i]["ISSUE_ALTER"].ToString(), COMPILED_BY = dtPccsRevisonDetails.Rows[i]["COMPILED_BY"].ToString(), DELETE_FLAG = false, ENTERED_DATE = DateTime.Now, ENTERED_BY = userInformation.UserName, ROWID = Guid.NewGuid() }; if (dtPccsRevisonDetails.Rows[i]["ISSUE_DATE"].ToString() != "") { pccsRevison.ISSUE_DATE = Convert.ToDateTime(dtPccsRevisonDetails.Rows[i]["ISSUE_DATE"]); } else { pccsRevison.ISSUE_DATE = null; } DB.PCCS_ISSUE.InsertOnSubmit(pccsRevison); DB.SubmitChanges(); typ = "INS"; } } catch (Exception ex) { ex.LogException(); DB.PCCS_ISSUE.DeleteOnSubmit(pccsRevison); } } } _status = true; } } return(_status); } catch (Exception ex) { ex.LogException(); return(false); } }
public frmRptFeatureWise(UserInformation userInformation, WPF.MDI.MdiChild mdiChild, PCCS feature = null, PCCS feature1 = null, PCCS feature2 = null, PCCS specification = null, PRD_MAST productMaster = null, bool refreshOnLoad = false, string title = "Feature Wise Report") { InitializeComponent(); this.mdiChild = mdiChild; vm = new ReportMISFeatureWiseViewModel(userInformation, mdiChild, feature, feature1, feature2, specification, productMaster, refreshOnLoad, title); this.DataContext = vm; mdiChild.Closing += vm.CloseMethod; if (vm.CloseAction == null) { vm.CloseAction = new Action(() => mdiChild.Close()); } }
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 List <PCCS> GetFeature2(PCCS paramEntity = null) { return(GetFeature(paramEntity)); }
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); }