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);
}
