public void AddModels(QcavailablewidthModelList models, DbTransaction tran) { for (int i = 0; i < models.Count; i++) { this.AddModel(models[i], tran); } }
public QcavailablewidthModelList GetModelList(QcavailablewidthModel model) { QcavailablewidthTable table = new QcavailablewidthTable(); SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns()); if (model.QualityCode != "") { sql.Where(table.QualityCode == model.QualityCode); } using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader())) { QcavailablewidthModelList result = new QcavailablewidthModelList(); while (sdr.Read()) { QcavailablewidthModel m = new QcavailablewidthModel(); m.Iden = sdr.GetInt32(table.Iden); m.QualityCode = sdr.GetString(table.QualityCode); m.Gauge = sdr.GetInt32(table.Gauge); m.Diameter = sdr.GetInt32(table.Diameter); m.TotalNeedles = sdr.GetInt32(table.TotalNeedles); m.Width = sdr.GetInt32(table.Width); m.MaxWidth = sdr.GetInt32(table.MaxWidth); m.UpdatedBy = sdr.GetString(table.UpdatedBy); m.UpdatedTime = sdr.GetDateTime(table.UpdatedTime); result.Add(m); } return(result); } }
private QcavailablewidthModelList CreateAvaWidth(List <AvaWidthModel> listAWM) { QcavailablewidthModelList qcAvaWidthList = new QcavailablewidthModelList(); for (int i = 0; i < listAWM.Count; i++) { QcavailablewidthModel qcAvaModel = new QcavailablewidthModel(); qcAvaModel.Gauge = listAWM[i].Gauge; qcAvaModel.Diameter = listAWM[i].Diameter; qcAvaModel.QualityCode = tQC.Text; qcAvaModel.TotalNeedles = listAWM[i].TotalNeedles; qcAvaModel.MaxWidth = listAWM[i].MaxWidth; qcAvaModel.Width = listAWM[i].Width; qcAvaModel.UpdatedBy = HttpContext.Current.Session["UserId"].ToString(); qcAvaModel.UpdatedTime = System.DateTime.Now; qcAvaWidthList.Add(qcAvaModel); } return(qcAvaWidthList); }
public string QCTrasferToAX(string sQC) { try { if (sQC.Contains("E")) { return("The code is not internal, don't need to call AX"); } QccustomerlibraryManager cumanager = new QccustomerlibraryManager(); QcmaininfoManager qcMainManager = new QcmaininfoManager(); QcconstructiondtlManager qcConManager = new QcconstructiondtlManager(); QcyarndtlManager yarnManager = new QcyarndtlManager(); QcfinishdtlManager finishManager = new QcfinishdtlManager(); QcavailablewidthManager avaManager = new QcavailablewidthManager(); QcmaininfoModelList qcMainModelList = qcMainManager.GetModelList(new QcmaininfoModel() { QualityCode = sQC }); if (qcMainModelList.Count == 0) { return("没有找到该QC#数据"); } QcmaininfoModel qcMainModel = qcMainModelList[0]; if (qcMainModel.DyeMethod == "YD") { return("DyeMethod is YD,don't need to Call AX"); } if (qcMainModel.Status != "Approved") { return("The status is not Approved,don't need to Call AX"); } QcconstructiondtlModelList qcConstructionList = qcConManager.GetModelList(new QcconstructiondtlModel() { QualityCode = sQC }); QcyarndtlModelList qcYarnList = yarnManager.GetModelList(new QcyarndtlModel() { QualityCode = sQC }); QcfinishdtlModelList qcFinfishing = finishManager.GetModelList(new QcfinishdtlModel() { QualityCode = sQC }); QcavailablewidthModelList qcAvaWidthList = avaManager.GetModelList(new QcavailablewidthModel() { QualityCode = sQC }); QccustomerlibraryModelList customerList = cumanager.GetModelList(new QccustomerlibraryModel() { QualityCode = qcMainModel.QualityCode }); WebReference3.TEX_AxdInventTable data = new WebReference3.TEX_AxdInventTable(); //Customer List <WebReference3.TEX_AxdQCCustomerLibrary> customer = new List <TEX_AxdQCCustomerLibrary>(); for (int i = 0; i < customerList.Count; i++) { customer.Add(new TEX_AxdQCCustomerLibrary() { BrandId = customerList[i].Brand, CustBuyerId = customerList[i].BuyerId, CustMillComments = customerList[i].MillComments, CustQualityId = customerList[i].CustomerQualityId, CustSalesGroup = customerList[i].SalesGroup, CustSalesId = customerList[i].Sales, IsFirstOwnerStr = (customerList[i].IsFirstOwner == "Y" ? "Yes" : "No"), // IsFirstOwnerStr="YES", ItemId = customerList[i].QualityCode }); } if (customer.Count > 0) { data.CustomerLibrary = customer.ToArray(); } //Finish List <WebReference3.TEX_AxdQCFinishDetails> finish = new List <WebReference3.TEX_AxdQCFinishDetails>(); for (int i = 0; i < qcFinfishing.Count; i++) { finish.Add(new WebReference3.TEX_AxdQCFinishDetails() { FinishingCode = qcFinfishing[i].FinishingCode, ItemId = qcFinfishing[i].QualityCode }); } if (finish.Count > 0) { data.FinishDetails = finish.ToArray(); } //Yarn List <WebReference3.TEX_AxdQCYarnDetails> yarnD = new List <WebReference3.TEX_AxdQCYarnDetails>(); for (int i = 0; i < qcYarnList.Count; i++) { yarnD.Add(new TEX_AxdQCYarnDetails() { YarnRatio = qcYarnList[i].YarnRatio, ItemId = qcYarnList[i].QualityCode, YarnThreads = qcYarnList[i].Threads, YarnTypeId = qcYarnList[i].YarnType, YarnCountId = qcYarnList[i].YarnCount, YarnCompositionId = qcYarnList[i].YarnComponent, YarnDensity = qcYarnList[i].YarnDensity }); } if (yarnD.Count > 0) { data.YarnDetails = yarnD.ToArray(); } //Construction List <WebReference3.TEX_AxdQCConstructionDetails> ConList = new List <WebReference3.TEX_AxdQCConstructionDetails>(); for (int i = 0; i < qcConstructionList.Count; i++) { ConList.Add(new TEX_AxdQCConstructionDetails() { ConstructionId = qcConstructionList[i].Construction, ItemId = qcConstructionList[i].QualityCode }); } if (ConList.Count > 0) { data.ConstructionDetails = ConList.ToArray(); } //AvaWith if (qcAvaWidthList.Count > 0) { TEX_AxdQCAvailableWidth[] avaList = new TEX_AxdQCAvailableWidth[qcAvaWidthList.Count]; TEX_AxdQCAvailableWidth aw = null; for (int i = 0; i < qcAvaWidthList.Count; i++) { aw = new TEX_AxdQCAvailableWidth(); aw.ItemId = qcAvaWidthList[i].QualityCode; aw.Gauge = qcAvaWidthList[i].Gauge; aw.Diameter = qcAvaWidthList[i].Diameter; aw.TotalNeedles = qcAvaWidthList[i].TotalNeedles; aw.Width = qcAvaWidthList[i].Width; aw.MaxWidth = qcAvaWidthList[i].MaxWidth; avaList.SetValue(aw, i); } data.AvailableWidth = avaList; } /* * List<WebReference3.TEX_AxdQCAvailableWidth> avaList = new List<WebReference3.TEX_AxdQCAvailableWidth>(); * * for (int i = 0; i < qcAvaWidthList.Count; i++) * { * avaList.Add(new TEX_AxdQCAvailableWidth() * { * ItemId = qcAvaWidthList[i].QualityCode, * Gauge = qcAvaWidthList[i].Gauge, * Diameter = qcAvaWidthList[i].Diameter, * TotalNeedles = qcAvaWidthList[i].TotalNeedles, * Width = qcAvaWidthList[i].Width, * MaxWidth = qcAvaWidthList[i].MaxWidth * * }); * * } * if (avaList.Count > 0) * data.AvailableWidth = avaList.ToArray();*/ data.ItemId = qcMainModel.QualityCode; data.PatternId = qcMainModel.Pattern; data.DyeMethodId = qcMainModel.DyeMethod; data.GramWeightBeforeWash = qcMainModel.BfGmmm; data.GramWeightAfterWash = qcMainModel.AfGmmm; data.Shrinkage = qcMainModel.Shrinkage; data.InventStatus = TEX_InventStatus.Approved; data.ShrinkTestMethod = qcMainModel.ShrinkageTestingMethod; // data.IsGarmentWash = (qcMainModel.GmtWashing == "Y" ? NoYes.Yes : NoYes.No); data.IsGarmentWashStr = (qcMainModel.GmtWashing == "Y" ? "Yes" : "No"); data.IsGarmentWashStr = "Yes"; data.InventLayout = qcMainModel.Layout; data.Remark = qcMainModel.Remark; data.ApprovalDateTime = qcMainModel.ApproveDate; data.Approver = qcMainModel.Approver; data.InventSourcing = qcMainModel.Sourcing; data.MaterialGroup = qcMainModel.MaterialGroup; data.KnitAnalysisNum = qcMainModel.AnalysisNo; data.RefQualityCode = qcMainModel.RefQualityCode; data.TappingType = qcMainModel.TappingType; data.TappingMeasurement = qcMainModel.Measurement; data.YarnLength = qcMainModel.YarnLength; data.SpecialType = qcMainModel.SpecialType; WebReference3.portTypeClient qc = new WebReference3.portTypeClient(); if (qc.TransQCOp(data)) { return("Sucess"); } else { return("Fail"); } } catch (Exception ex) { return(ex.Message); } }
//保存的时候,如果是圆机则更新AvaWidth和GEKComment的信息;带子和横机都只更新GEKComment信息 protected void btnSave_Click(object sender, EventArgs e) { DbTransaction tran = DataAccess.DefaultDB.BeginTransaction(); try { if (ViewState["MGType"].ToString() == "Fabric") { #region 保存数据 List <AvaWidthModel> listAWM = avaWidth.GetAvaWidth(); QcavailablewidthModelList qcAvaWidthList = CreateAvaWidth(listAWM); QcmaininfoModel mainModel = new QcmaininfoModel(); mainModel.QualityCode = tQC.Text; mainModel.Remark = txtAvaRemark.Text; if (!string.IsNullOrEmpty(HidArributeValue.Value)) { string[] strArray = HidArributeValue.Value.Replace("<>", "#").Split('#'); //todo by mengjw mainModel.QC_Ref_PPO = strArray[10]; mainModel.QC_Ref_GP = strArray[11] == "null" ? "" : strArray[11];; mainModel.HF_Ref_PPO = strArray[12]; mainModel.HF_Ref_GP = strArray[13] == "null" ? "" : strArray[13];; mainModel.RF_Remark = strArray[14]; //add by zheng zhou 2016-8-3 保存QC,HF的修改日志 if (Request.Form["hd_HF_Ref_GP_Old"] != (strArray[13] == "null" ? "" : strArray[13]) || Request.Form["hd_HF_Ref_PPO_Old"] != (strArray[12] == "null" ? "" : strArray[12]) || Request.Form["hd_QC_Ref_GP_Old"] != (strArray[11] == "null" ? "" : strArray[11]) || Request.Form["hd_QC_Ref_PPO_Old"] != (strArray[10] == "null" ? "" : strArray[10])) { QC_HF_ChangeLogModel changeModel = new QC_HF_ChangeLogModel(); QC_HF_ChangeLogManager changeManager = new QC_HF_ChangeLogManager(); changeModel.QualityCode = tQC.Text.Trim(); changeModel.HF_Ref_GP_New = strArray[13] == "null" ? "" : strArray[13]; changeModel.HF_Ref_PPO_New = strArray[12] == "null" ? "" : strArray[12]; changeModel.QC_Ref_GP_New = strArray[11] == "null" ? "" : strArray[11]; changeModel.QC_Ref_PPO_New = strArray[10] == "null" ? "" : strArray[10]; changeModel.HF_Ref_GP_Old = Request.Form["hd_HF_Ref_GP_Old"]; changeModel.HF_Ref_PPO_Old = Request.Form["hd_HF_Ref_PPO_Old"]; changeModel.QC_Ref_GP_Old = Request.Form["hd_QC_Ref_GP_Old"]; changeModel.QC_Ref_PPO_Old = Request.Form["hd_QC_Ref_PPO_Old"]; changeModel.CreateDate = System.DateTime.Now; changeModel.Creator = HttpContext.Current.Session["UserId"].ToString(); changeManager.AddModel(changeModel, tran); } //////////////////////////////////////////////// } qcAvaWidthManager.DeleteModel(new QcavailablewidthModel() { QualityCode = tQC.Text }, tran); qcAvaWidthManager.AddModels(qcAvaWidthList, tran); qcMainManager.UpdateModelRemark(mainModel, tran); #endregion } // add by zheng zhou 2016-8-2 按照是否点击选择ppono,分不同的方法保留qcCustomer对象 QccustomerlibraryModel qcCustomer = CreateCustomer(); if (qcCustomer.BuyerId == "**X") { qcCustomerManager.UpdateModelThree(qcCustomer, tran); } else { qcCustomerManager.UpdateModelOne(qcCustomer, tran); } //////////////////////////////////////////////////////////////////////////////////// tran.Commit(); if (ViewState["MGType"].ToString() == "Fabric" && !string.IsNullOrEmpty(HidArributeValue.Value)) { //added by hejianh 2020-04-20 对接系统质量&手感参办自动更新 string[] strArrayStr = HidArributeValue.Value.Replace("<>", "#").Split('#'); string HF_Ref_GP_New = strArrayStr[13] == "null" ? "" : strArrayStr[13]; string HF_Ref_PPO_New = strArrayStr[12] == "null" ? "" : strArrayStr[12]; string QC_Ref_GP_New = strArrayStr[11] == "null" ? "" : strArrayStr[11]; string QC_Ref_PPO_New = strArrayStr[10] == "null" ? "" : strArrayStr[10]; string HF_Ref_GP_Old = Request.Form["hd_HF_Ref_GP_Old"]; string HF_Ref_PPO_Old = Request.Form["hd_HF_Ref_PPO_Old"]; string QC_Ref_GP_Old = Request.Form["hd_QC_Ref_GP_Old"]; string QC_Ref_PPO_Old = Request.Form["hd_QC_Ref_PPO_Old"]; string remark_New = strArrayStr[14] == "null" ? "" : strArrayStr[14]; string remark_Old = Request.Form["remark_Old"]; if (HF_Ref_GP_New != HF_Ref_GP_Old || HF_Ref_PPO_New != HF_Ref_PPO_Old || QC_Ref_GP_New != QC_Ref_GP_Old || QC_Ref_PPO_New != QC_Ref_PPO_Old || remark_New != remark_Old) { //创建和数据库的连接 string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection oraCon = new OracleConnection(connString); //打开连接 oraCon.Open(); OracleCommand oraCmd = new OracleCommand(); //新建一个事务对象的实例 OracleTransaction oraTact = oraCon.BeginTransaction(); oraCmd.Connection = oraCon; //绑定事务对象到命令 oraCmd.Transaction = oraTact; try { //将一个表的满足某条件的行的指定的列插入到另一个表 oraCmd.CommandText = "INSERT INTO ESCMOWNER.QCMAININFO_SYNC_LOG(SYNC_LOG_ID,QUALITY_CODE,SYNC_TO_ESCM_FLAG,CREATE_USER_ID,CREATE_DATE) SELECT QCMAININFO_SYNC_LOG_SEQ.NEXTVAL,'" + tQC.Text.Trim().ToString() + "','N','" + HttpContext.Current.Session["UserId"].ToString() + "',SYSDATE FROM DUAL"; int r = oraCmd.ExecuteNonQuery(); r = 10; //没有错误,执行提交命令 oraTact.Commit(); } catch (Exception ex) { //出现错误,执行回滚命令 oraTact.Rollback(); //弹出窗口显示错误 Response.Write("<script>alert('" + ex.Message + "')</script>"); } finally { //关闭连接 oraCon.Close(); } //插入数据库完毕后做调用webserver // webservice调用地址 //string url = "http://192.168.27.80/YPD_DEV/YPDWebService.asmx?op=SyncQualityHandfeelReference"; //测试地址 string url = "http://192.168.7.187/YPD/YPDWebService.asmx?op=SyncQualityHandfeelReference"; //正式地址 // SOAP格式内容,参数为:http://www.what21.com StringBuilder param = new StringBuilder(); param.Append("<soap12:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:soap12=\"http://www.w3.org/2003/05/soap-envelope\" > "); param.Append("<soap12:Body>"); param.Append("<SyncQualityHandfeelReference xmlns=\"http://tempuri.org/\">"); param.Append("<qualityCodes>" + tQC.Text.Trim().ToString() + "</qualityCodes>"); param.Append("<userId>" + HttpContext.Current.Session["UserId"].ToString() + "</userId>"); param.Append("</SyncQualityHandfeelReference>"); param.Append("</soap12:Body>"); param.Append("</soap12:Envelope>"); try { // 创建HttpWebRequest对象 HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(url); // 设置POST调用方法 httpRequest.Method = "POST"; // 设置HTTP头ContentType //httpRequest.ContentType = "application/soap+xml;charset=UTF-8;action=\"http://192.168.27.80/YPD_DEV/YPDWebService.asmx?op=SyncQualityHandfeelReference\""; //测试地址 httpRequest.ContentType = "application/soap+xml;charset=UTF-8;action=\"http://192.168.7.187/YPD/YPDWebService.asmx?op=SyncQualityHandfeelReference\""; //正式地址 // 设置HTTP头SOAPAction的值 httpRequest.Headers.Add("SOAPAction", "urn:world"); // 调用内容 byte[] bytes = Encoding.UTF8.GetBytes(param.ToString()); // 设置HTTP头内容的长度 httpRequest.ContentLength = param.ToString().Length; using (Stream reqStream = httpRequest.GetRequestStream()) { reqStream.Write(bytes, 0, bytes.Length); reqStream.Flush(); } // HttpWebRequest发起调用 using (HttpWebResponse myResponse = (HttpWebResponse)httpRequest.GetResponse()) { // StreamReader对象 StreamReader sr = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8); // 返回结果 string responseString = sr.ReadToEnd(); Console.WriteLine("调用结果" + responseString); } } catch (Exception ex) { //弹出窗口显示错误 //Response.Write("<script>alert('" + ex.Message + "')</script>"); } } //added by hejianh 2020-04-20 对接系统质量&手感参办自动更新 } //Response.Write("<script language='JavaScript'>alert('Success');</script>"); //ClientScript.RegisterClientScriptBlock(typeof(string), "js", "SearchQC1();", true); ClientScript.RegisterStartupScript(this.GetType(), "", "<script>SearchQC1();</script>"); } catch (Exception ex) { tran.Rollback(); Response.Write("<script language='JavaScript'>alert('" + ex.Message.Replace("'", " ").Replace("\"", " ").Replace("\n", " ").Replace("\r", " ") + "');</script>"); return; } // Response.Write("<script language='JavaScript'>alert('Successfully updated!');window.location='EditQC.aspx?MG=" + ViewState["MGType"].ToString() + "&QC=" + tQC.Text + "&customerId=" + tCustomerId.Text + "';</script>"); }
//保存的时候,如果是圆机则更新AvaWidth和GEKComment的信息;带子和横机都只更新GEKComment信息 protected void btnSave_Click(object sender, EventArgs e) { DbTransaction tran = DataAccess.DefaultDB.BeginTransaction(); try { if (ViewState["MGType"].ToString() == "Fabric") { List <AvaWidthModel> listAWM = avaWidth.GetAvaWidth(); QcavailablewidthModelList qcAvaWidthList = CreateAvaWidth(listAWM); QcmaininfoModel mainModel = new QcmaininfoModel(); mainModel.QualityCode = tQC.Text; mainModel.Remark = txtAvaRemark.Text; if (!string.IsNullOrEmpty(HidArributeValue.Value)) { string[] strArray = HidArributeValue.Value.Replace("<>", "#").Split('#'); //todo by mengjw mainModel.QC_Ref_PPO = strArray[10]; mainModel.QC_Ref_GP = strArray[11] == "null" ? "" : strArray[11];; mainModel.HF_Ref_PPO = strArray[12]; mainModel.HF_Ref_GP = strArray[13] == "null" ? "" : strArray[13];; mainModel.RF_Remark = strArray[14]; //add by zheng zhou 2016-8-3 保存QC,HF的修改日志 if (Request.Form["hd_HF_Ref_GP_Old"] != (strArray[13] == "null" ? "" : strArray[13]) || Request.Form["hd_HF_Ref_PPO_Old"] != (strArray[12] == "null" ? "" : strArray[12]) || Request.Form["hd_QC_Ref_GP_Old"] != (strArray[11] == "null" ? "" : strArray[11]) || Request.Form["hd_QC_Ref_PPO_Old"] != (strArray[10] == "null" ? "" : strArray[10])) { QC_HF_ChangeLogModel changeModel = new QC_HF_ChangeLogModel(); QC_HF_ChangeLogManager changeManager = new QC_HF_ChangeLogManager(); changeModel.QualityCode = tQC.Text.Trim(); changeModel.HF_Ref_GP_New = strArray[13] == "null" ? "" : strArray[13]; changeModel.HF_Ref_PPO_New = strArray[12] == "null" ? "" : strArray[12]; changeModel.QC_Ref_GP_New = strArray[11] == "null" ? "" : strArray[11]; changeModel.QC_Ref_PPO_New = strArray[10] == "null" ? "" : strArray[10]; changeModel.HF_Ref_GP_Old = Request.Form["hd_HF_Ref_GP_Old"]; changeModel.HF_Ref_PPO_Old = Request.Form["hd_HF_Ref_PPO_Old"]; changeModel.QC_Ref_GP_Old = Request.Form["hd_QC_Ref_GP_Old"]; changeModel.QC_Ref_PPO_Old = Request.Form["hd_QC_Ref_PPO_Old"]; changeModel.CreateDate = System.DateTime.Now; changeModel.Creator = HttpContext.Current.Session["UserId"].ToString(); changeManager.AddModel(changeModel, tran); } //////////////////////////////////////////////// } qcAvaWidthManager.DeleteModel(new QcavailablewidthModel() { QualityCode = tQC.Text }, tran); qcAvaWidthManager.AddModels(qcAvaWidthList, tran); qcMainManager.UpdateModelRemark(mainModel, tran); } // add by zheng zhou 2016-8-2 按照是否点击选择ppono,分不同的方法保留qcCustomer对象 QccustomerlibraryModel qcCustomer = CreateCustomer(); if (qcCustomer.BuyerId == "**X") { qcCustomerManager.UpdateModelThree(qcCustomer, tran); } else { qcCustomerManager.UpdateModelOne(qcCustomer, tran); } //////////////////////////////////////////////////////////////////////////////////// tran.Commit(); Response.Write("<script language='JavaScript'>alert('Success')</script>"); } catch (Exception ex) { tran.Rollback(); Response.Write("<script language='JavaScript'>alert('" + ex.Message.Replace("'", " ").Replace("\"", " ").Replace("\n", " ").Replace("\r", " ") + "');</script>"); return; } // Response.Write("<script language='JavaScript'>alert('Successfully updated!');window.location='Edit_Ref.aspx?MG=" + ViewState["MGType"].ToString() + "&QC=" + tQC.Text + "&customerId=" + tCustomerId.Text + "';</script>"); }
public static DataTable DataTable() { string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection con = new OracleConnection(connString); con.Open(); string cmd = "SELECT * " + " FROM (SELECT ROWNUM as Items, E.* " + " FROM (SELECT DISTINCT A.* " + " FROM QCMainInfo A " + " LEFT JOIN QCConstructionDtl B " + " ON A.Quality_Code = B.Quality_Code " + " LEFT JOIN QCFinishDtl C " + " ON A.Quality_COde = C.Quality_Code " + " LEFT JOIN QCCustomerLibrary D " + " ON D.Quality_Code = A.Quality_Code " + " LEFT JOIN QCCustomerLibrary F " + " ON F.Quality_Code=A.Quality_Code " + " where 1=1 " + a + " order by A.Quality_Code asc ) E) "; using (OracleDataAdapter oleAdper = new OracleDataAdapter(cmd, con)) { DataTable result = new DataTable("table"); oleAdper.Fill(result); if (result.Rows.Count >= 100) { string cmds = @" select distinct A.quality_code,A.status, A.replace_by, A.repeat, L.ApprovedFromSPPO, L.ApprovedFromSPPO_Usage, to_char(S.descriptions) as construction , to_char(K.finishing_name) as Finishing,A.pattern, (N.DESCRIPTION) as DYE_METHOD , A.bf_gmmm, A.af_gmmm, A.shrinkage, A.shrinkage_testing_method, A.gmt_washing,to_char(J.customer_quality_id) as customer_quality_id, to_char(H.AvailableWidth) as AvailableWidth, A.remark,to_char(I.YarnInfo) as YarnInfo,A.layout, A.yarn_length, A.tapping_type ,(A.special_type ) as Heavy_Flat_Knit, to_char(J.mill_comments) as mill_comments,A.measurement, A.sourcing, A.create_date, A.material_group, A.analysis_no, A.ref_quality_code, A.creator, A.approve_date, A.approver, to_char(J.buyer_id) as buyer_id, to_char(J.brand) as Branks, A.gk_no, A.qc_ref_ppo, A.qc_ref_gp, A.hf_ref_ppo, A.hf_ref_gp,M.customercomment from QCMainInfo A left join QCConstructionDtl B on A.Quality_Code = B.Quality_Code left join QCFinishDtl C on A.Quality_COde = C.Quality_Code left join QCCustomerLibrary D on A.Quality_Code = D.Quality_Code left join QCCustomerLibrary F on A.Quality_Code = F.Quality_Code left join ( select * from ( select Quality_Code , wm_concat(to_char(description)) as descriptions from QCConstructionDtl G left join Pbknitconstruction S on S.construction=G.construction group by Quality_Code )) S on A.Quality_Code=S.Quality_Code -- left join QCConstructionDtl G on A.Quality_Code = G.Quality_Code -- left join Pbknitconstruction M on M.construction=G.construction left join PBKNITDYEMETHOD N on A.DYE_METHOD =N.DYE_METHOD left join (select * from (select quality_code, (wmsys.wm_concat(''||gauge||'G'||diameter||'Inch/'||total_needles||'N/'||width||'-'||max_width||'\' )) as AvailableWidth from qcavailablewidth where 1=1 group by quality_code) ) H on A.Quality_Code = H.Quality_Code left join (select * from ( select quality_code,( wmsys.wm_concat( ''||yarn_count||'X'||threads||''||b.DESCRIPTION||''||yarn_ratio||'% '||yarn_component||'' ))as YarnInfo from qcyarndtl a left join PBKNITYARNTYPE b on a.YARN_TYPE=b.YARN_TYPE where 1=1 group by quality_code) ) I on A.Quality_Code = I.Quality_Code left join (select * from (select quality_code , (wmsys.wm_concat(buyer_id)) as buyer_id , (wmsys.wm_concat(brand)) as brand, (wmsys.wm_concat(customer_quality_id)) as customer_quality_id,(wmsys.wm_concat(mill_comments) ) as mill_comments from QCCustomerLibrary where 1=1 group by quality_code)) J on A.Quality_Code=j.Quality_Code left join (select * from (select quality_code, (wmsys.wm_concat(z.finishing_name )) as finishing_name from QCFinishDtl y, pbknitfinish Z where y.finishing_code= z.finishing_code group by quality_code)) K on A.Quality_Code = K.Quality_Code left join ( select quality_code, ApprovedFromSPPO,ApprovedFromSPPO_Usage from ( --modify:gaofeng 2021/01/18 <2021-0001 QC System Enhancement from sales> --begin --select ROW_NUMBER() OVER(PARTITION BY quality_code ORDER BY approve_date desc) rn, --quality_code, ApprovedFromSPPO from (SELECT distinct qm.quality_code,( pi.ppo_no|| '('||pi.FABRIC_TYPE_CD ||')' ) as ApprovedFromSPPO ,QCC.approve_date --FROM ppo_hd ph,gen_customer c,ppo_qcmaininfo qc, ppo_item pi, fab_combo fc, ppo_qccomment qcc,ppo_item_combo pic,QCMainInfo qm --WHERE ph.ppo_no=pi.ppo_no and fc.fab_combo_id = qcc.fab_combo_id and qc.ppo_item_id=pi.ppo_item_id --and pi.ppo_item_id=pic.ppo_item_id and pi.quality_code=qm.quality_code and pic.fab_combo_id=fc.fab_combo_id --and qc.ppo_qc_id=qcc.ppo_qc_id and ph.customer_cd=c.customer_cd and QCC.STATUS='Approved' )s ) where rn=1 select ROW_NUMBER() OVER(PARTITION BY quality_code ORDER BY approve_date desc) rn, quality_code, ApprovedFromSPPO,ApprovedFromSPPO_Usage from ( SELECT distinct qm.quality_code, --( pi.ppo_no|| '('||pi.FABRIC_TYPE_CD ||')' ) as ApprovedFromSPPO , pi.ppo_no as ApprovedFromSPPO, pi.FABRIC_TYPE_CD as ApprovedFromSPPO_Usage, QCC.approve_date FROM ppo_hd ph,gen_customer c,ppo_qcmaininfo qc, ppo_item pi, fab_combo fc, ppo_qccomment qcc,ppo_item_combo pic,QCMainInfo qm WHERE ph.ppo_no=pi.ppo_no and fc.fab_combo_id = qcc.fab_combo_id and qc.ppo_item_id=pi.ppo_item_id and pi.ppo_item_id=pic.ppo_item_id and pi.quality_code=qm.quality_code and pic.fab_combo_id=fc.fab_combo_id and qc.ppo_qc_id=qcc.ppo_qc_id and ph.customer_cd=c.customer_cd and QCC.STATUS='Approved' )s ) where rn=1 --modify:gaofeng 2021/01/18 <2021-0001 QC System Enhancement from sales> --end ) L on A.Quality_Code = L.Quality_Code left join ( select * from (SELECT ROW_NUMBER() OVER(PARTITION BY pi.quality_code ORDER BY qcc.customer_comment ) rn, (qcc.customer_comment ) as customercomment ,pi.quality_code FROM ppo_hd ph,gen_customer c,ppo_qcmaininfo qc, ppo_item pi, fab_combo fc, ppo_qccomment qcc,ppo_item_combo pic,QCMainInfo qm WHERE ph.ppo_no=pi.ppo_no and fc.fab_combo_id = qcc.fab_combo_id and qc.ppo_item_id=pi.ppo_item_id and pi.ppo_item_id=pic.ppo_item_id and pi.quality_code=qm.quality_code and qm.Material_Group<>'Flat Knit Fabric' AND pic.fab_combo_id=fc.fab_combo_id and qc.ppo_qc_id=qcc.ppo_qc_id and ph.customer_cd=c.customer_cd )s WHERE rn = 1 ) M on A.Quality_Code = M.quality_code where 1=1 " + a + " order by A.Quality_Code asc "; using (OracleDataAdapter oleAdpers = new OracleDataAdapter(cmds, con)) { DataTable results = new DataTable("table"); oleAdpers.Fill(results); results.Columns["QUALITY_CODE"].ColumnName = "QualityCode"; results.Columns["STATUS"].ColumnName = "Status"; results.Columns["REPLACE_BY"].ColumnName = "ReplaceBy"; results.Columns["REPEAT"].ColumnName = "Repeat"; results.Columns["APPROVEDFROMSPPO"].ColumnName = "ApprovedFromSPPO"; //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --begin results.Columns["APPROVEDFROMSPPO_USAGE"].ColumnName = "ApprovedFromSPPO_Usage"; //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --end results.Columns["CONSTRUCTION"].ColumnName = "Construction"; results.Columns["FINISHING"].ColumnName = "Finishing"; results.Columns["PATTERN"].ColumnName = "Pattern"; results.Columns["DYE_METHOD"].ColumnName = "DyeMethod"; results.Columns["BF_GMMM"].ColumnName = "BfGmmm"; results.Columns["AF_GMMM"].ColumnName = "AfGmmm"; results.Columns["SHRINKAGE"].ColumnName = "Shrinkage"; results.Columns["SHRINKAGE_TESTING_METHOD"].ColumnName = "ShrinkageTestingMethod"; results.Columns["GMT_WASHING"].ColumnName = "GmtWashing"; results.Columns["CUSTOMER_QUALITY_ID"].ColumnName = "CustomerQualityId"; results.Columns["AVAILABLEWIDTH"].ColumnName = "Available Width"; results.Columns["REMARK"].ColumnName = "Available Width Remark"; results.Columns["YARNINFO"].ColumnName = "YarnInfo"; results.Columns["LAYOUT"].ColumnName = "Layout"; results.Columns["YARN_LENGTH"].ColumnName = "YarnLength"; results.Columns["TAPPING_TYPE"].ColumnName = "TappingType"; results.Columns["HEAVY_FLAT_KNIT"].ColumnName = "Heavy_Flat_Knit"; results.Columns["MILL_COMMENTS"].ColumnName = "MillComments"; results.Columns["MEASUREMENT"].ColumnName = "Measurement"; results.Columns["SOURCING"].ColumnName = "Sourcing"; results.Columns["CREATE_DATE"].ColumnName = "CreateDate"; results.Columns["MATERIAL_GROUP"].ColumnName = "MaterialGroup"; results.Columns["ANALYSIS_NO"].ColumnName = "AnalysisNo"; results.Columns["REF_QUALITY_CODE"].ColumnName = "RefQualityCode"; results.Columns["CREATOR"].ColumnName = "Creator"; results.Columns["APPROVE_DATE"].ColumnName = "ApproveDate"; results.Columns["APPROVER"].ColumnName = "Approver"; results.Columns["BUYER_ID"].ColumnName = "BuyerIds"; results.Columns["BRANKS"].ColumnName = "Branks"; results.Columns["GK_NO"].ColumnName = "GK_NO"; results.Columns["QC_REF_PPO"].ColumnName = "QC_Ref_PPO"; results.Columns["QC_REF_GP"].ColumnName = "QC_Ref_GP"; results.Columns["HF_REF_PPO"].ColumnName = "HF_Ref_PPO"; results.Columns["HF_REF_GP"].ColumnName = "HF_Ref_GP"; results.Columns["CUSTOMERCOMMENT"].ColumnName = "CustomerComment"; return(results); } } else { DataTable dtt = new DataTable(); dtt.Columns.Add("QualityCode", typeof(string)); dtt.Columns.Add("Status", typeof(string)); dtt.Columns.Add("ReplaceBy", typeof(string)); dtt.Columns.Add("Repeat", typeof(string)); dtt.Columns.Add("ApprovedFromSPPO", typeof(string)); //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --begin dtt.Columns.Add("ApprovedFromSPPO_Usage", typeof(string)); //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --end dtt.Columns.Add("Construction", typeof(string)); dtt.Columns.Add("Finishing", typeof(string)); dtt.Columns.Add("Pattern", typeof(string)); dtt.Columns.Add("DyeMethod", typeof(string)); dtt.Columns.Add("BfGmmm", typeof(string)); dtt.Columns.Add("AfGmmm", typeof(string)); dtt.Columns.Add("Shrinkage", typeof(string)); dtt.Columns.Add("ShrinkageTestingMethod", typeof(string)); dtt.Columns.Add("GmtWashing", typeof(string)); http://localhost:58826/Service References/ dtt.Columns.Add("CustomerQualityId", typeof(string)); dtt.Columns.Add("Available Width", typeof(string)); dtt.Columns.Add("Available Width Remark", typeof(string)); dtt.Columns.Add("YarnInfo", typeof(string)); dtt.Columns.Add("Layout", typeof(string)); dtt.Columns.Add("YarnLength", typeof(string)); dtt.Columns.Add("TappingType", typeof(string)); dtt.Columns.Add("Heavy_Flat_Knit", typeof(string)); dtt.Columns.Add("MillComments", typeof(string)); dtt.Columns.Add("Measurement", typeof(string)); dtt.Columns.Add("Sourcing", typeof(string)); dtt.Columns.Add("CreateDate", typeof(string)); dtt.Columns.Add("MaterialGroup", typeof(string)); dtt.Columns.Add("AnalysisNo", typeof(string)); dtt.Columns.Add("RefQualityCode", typeof(string)); dtt.Columns.Add("Creator", typeof(string)); dtt.Columns.Add("ApproveDate", typeof(string)); dtt.Columns.Add("Approver", typeof(string)); dtt.Columns.Add("BuyserIds", typeof(string)); dtt.Columns.Add("Branks", typeof(string)); dtt.Columns.Add("GK_NO", typeof(string)); dtt.Columns.Add("QC_Ref_PPO", typeof(string)); dtt.Columns.Add("QC_Ref_GP", typeof(string)); dtt.Columns.Add("HF_Ref_PPO", typeof(string)); dtt.Columns.Add("HF_Ref_GP", typeof(string)); dtt.Columns.Add("CustomerComment", typeof(string)); string connStrings = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection cons = new OracleConnection(connStrings); cons.Open(); string Sql1 = "SELECT * " + " FROM (SELECT ROWNUM as Items, E.* " + " FROM (SELECT DISTINCT A.* " + " FROM QCMainInfo A " + " LEFT JOIN QCConstructionDtl B " + " ON A.Quality_Code = B.Quality_Code " + " LEFT JOIN QCFinishDtl C " + " ON A.Quality_COde = C.Quality_Code " + " LEFT JOIN QCCustomerLibrary D " + " ON D.Quality_Code = A.Quality_Code " + " LEFT JOIN QCCustomerLibrary F " + " ON F.Quality_Code=A.Quality_Code " + " where 1=1 @Condition order by A.Quality_Code asc ) E) "; PbknityarntypeManager yarnManager = new PbknityarntypeManager();//kingzhang for support 709786 对于YarnInfo字符串中的Yarn_Type信息替换为pbKnitYarnType.description QcconstructiondtlManager qcm = new QcconstructiondtlManager(); QcfinishdtlManager qsm = new QcfinishdtlManager(); QccustomerlibraryManager qlm = new QccustomerlibraryManager(); QcconstructiondtlModelList qcml = new QcconstructiondtlModelList(); QcfinishdtlModelList qsml = new QcfinishdtlModelList(); QccustomerlibraryModelList qclm = new QccustomerlibraryModelList(); QcavailablewidthModelList qsaw = new QcavailablewidthModelList(); QcavailablewidthManager qcam = new QcavailablewidthManager(); QcyarndtlManager qym = new QcyarndtlManager(); QcyarndtlModelList qyml = new QcyarndtlModelList(); DataSet ds; CustomSqlSection css; DataTable dt; QcmaininfoTable table = new QcmaininfoTable(); Sql1 = Sql1.Replace("@Condition", a.ToString()); CustomSqlSection csql1 = DataAccess.DefaultDB.CustomSql(Sql1); using (SafeDataReader sdr = new SafeDataReader(csql1.ToDataReader())) { while (sdr.Read()) { QcmaininfoModel m = new QcmaininfoModel(); m.QualityCode = sdr.GetString(table.QualityCode); qcml = qcm.GetModelListOne(new QcconstructiondtlModel() { QualityCode = m.QualityCode }); for (int i = 0; i < qcml.Count; i++) { m.Construction = m.Construction + qcml[i].Construction + ";"; } qsml = qsm.GetModelListOne(new QcfinishdtlModel() { QualityCode = m.QualityCode }); for (int i = 0; i < qsml.Count; i++) { m.Finishing = m.Finishing + qsml[i].FinishingCode + ";"; } qclm = qlm.GetModelList(new QccustomerlibraryModel() { QualityCode = m.QualityCode }); for (int i = 0; i < qclm.Count; i++) { m.CustomerQualityId = m.CustomerQualityId + qclm[i].CustomerQualityId + ";"; m.BuyserIds = m.BuyserIds + qclm[i].BuyerId + ";"; m.Brank = m.Brank + qclm[i].Brand + ";"; m.MillComments = m.MillComments + qclm[i].MillComments + ";"; } qsaw = qcam.GetModelList(new QcavailablewidthModel() { QualityCode = m.QualityCode }); for (int i = 0; i < qsaw.Count; i++) { if (i != qsaw.Count - 1) { m.AvaWidth = m.AvaWidth + string.Format("{0}G{1}Inch/{2}N/{3}-{4}\"", qsaw[i].Gauge, qsaw[i].Diameter, qsaw[i].TotalNeedles, qsaw[i].Width, qsaw[i].MaxWidth) + "--"; } else { m.AvaWidth = m.AvaWidth + string.Format("{0}G{1}Inch/{2}N/{3}-{4}\"", qsaw[i].Gauge, qsaw[i].Diameter, qsaw[i].TotalNeedles, qsaw[i].Width, qsaw[i].MaxWidth); } } qyml = qym.GetModelList(new QcyarndtlModel() { QualityCode = m.QualityCode }); for (int i = 0; i < qyml.Count; i++) { PbknityarntypeModelList yarnModel = yarnManager.GetModelList(new PbknityarntypeModel() { YarnType = qyml[i].YarnType }); //kingzhang for support 709786 对于YarnInfo字符串中的Yarn_Type信息替换为pbKnitYarnType.description if (i != qyml.Count - 1) { m.YarnInfo = m.YarnInfo + string.Format("{0}X{1} {2} {3}% {4}", qyml[i].YarnCount, qyml[i].Threads, yarnModel.Count > 0 ? yarnModel[0].Description : qyml[i].YarnType, qyml[i].YarnRatio, qyml[i].YarnComponent) + "--"; } else { m.YarnInfo = m.YarnInfo + string.Format("{0}X{1} {2} {3}% {4}", qyml[i].YarnCount, qyml[i].Threads, yarnModel.Count > 0 ? yarnModel[0].Description : qyml[i].YarnType, qyml[i].YarnRatio, qyml[i].YarnComponent); } } //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --begin css = DataAccess.DefaultDB.CustomSql(@" select ppo_no as PPO , FABRIC_TYPE_CD from (select ROW_NUMBER() OVER(PARTITION BY quality_code ORDER BY approve_date desc) rn, quality_code,ppo_no, FABRIC_TYPE_CD from (SELECT distinct qm.quality_code, pi.ppo_no ,pi.FABRIC_TYPE_CD ,QCC.approve_date FROM ppo_hd ph,gen_customer c,ppo_qcmaininfo qc, ppo_item pi, fab_combo fc, ppo_qccomment qcc,ppo_item_combo pic,QCMainInfo qm WHERE ph.ppo_no=pi.ppo_no and fc.fab_combo_id = qcc.fab_combo_id and qc.ppo_item_id=pi.ppo_item_id and pi.ppo_item_id=pic.ppo_item_id and pi.quality_code=qm.quality_code and pic.fab_combo_id=fc.fab_combo_id and qc.ppo_qc_id=qcc.ppo_qc_id and ph.customer_cd=c.customer_cd and QCC.STATUS='Approved' )s ) where rn=1 and quality_code=@QC "); //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --end css.AddInputParameter("QC", DbType.String, m.QualityCode); ds = css.ToDataSet(); dt = ds.Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { //m.ApprovedFromSPPO = m.ApprovedFromSPPO + dt.Rows[i]["PPO"].ToString() + "(" + dt.Rows[i]["FABRIC_TYPE_CD"].ToString() + ");"; //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --begin m.ApprovedFromSPPO = m.ApprovedFromSPPO + dt.Rows[i]["PPO"].ToString(); m.ApprovedFromSPPO_Usage = m.ApprovedFromSPPO_Usage + dt.Rows[i]["FABRIC_TYPE_CD"].ToString(); } ; //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --end m.CreateDate = sdr.GetDateTime(table.CreateDate); m.Creator = sdr.GetString(table.Creator); m.ApproveDate = sdr.GetDateTime(table.ApproveDate); m.Approver = sdr.GetString(table.Approver); m.Status = sdr.GetString(table.Status); if (m.Status == "NEW") { m.Status = "New"; } m.Sourcing = sdr.GetString(table.Sourcing); m.MaterialGroup = sdr.GetString(table.MaterialGroup); m.AnalysisNo = sdr.GetString(table.AnalysisNo); m.RefQualityCode = sdr.GetString(table.RefQualityCode); m.Pattern = sdr.GetString(table.Pattern); m.DyeMethod = sdr.GetString(table.DyeMethod); m.ReplaceBy = sdr.GetString(table.ReplaceBy); m.Repeat = sdr.GetString(table.REPEAT); m.BfGmmm = sdr.GetInt32(table.BfGmmm); m.AfGmmm = sdr.GetInt32(table.AfGmmm); m.Shrinkage = sdr.GetString(table.Shrinkage); m.ShrinkageTestingMethod = sdr.GetString(table.ShrinkageTestingMethod); m.GmtWashing = sdr.GetString(table.GmtWashing); m.Layout = sdr.GetString(table.Layout); m.YarnLength = sdr.GetString(table.YarnLength); m.TappingType = sdr.GetString(table.TappingType); m.Measurement = sdr.GetString(table.Measurement); m.Remark = sdr.GetString(table.Remark); m.SpecialType = sdr.GetString(table.SpecialType); m.LastUpdateTime = sdr.GetDateTime(table.LastUpdateTime); m.LastUpdateBy = sdr.GetString(table.LastUpdateBy); m.GK_NO = sdr.GetString(table.GK_NO); m.QC_Ref_PPO = sdr.GetString(table.QC_Ref_PPO); m.HF_Ref_PPO = sdr.GetString(table.HF_Ref_PPO); m.QC_Ref_GP = sdr.GetString(table.QC_Ref_GP); m.HF_Ref_GP = sdr.GetString(table.HF_Ref_GP); m.CustomerComment = GetCustomerComment(m.QualityCode); dtt.Rows.Add(m.QualityCode, m.Status, m.ReplaceBy, m.Repeat, //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --begin m.ApprovedFromSPPO, m.ApprovedFromSPPO_Usage, //modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --end m.Construction, m.Finishing, m.Pattern, m.DyeMethod, m.BfGmmm, m.AfGmmm, m.Shrinkage, m.ShrinkageTestingMethod, m.GmtWashing, m.CustomerQualityId, m.AvaWidth, m.Remark, m.YarnInfo, m.Layout, m.YarnLength, m.TappingType, m.SpecialType, m.MillComments, m.Measurement, m.Sourcing, m.CreateDate, m.MaterialGroup, m.AnalysisNo, m.RefQualityCode, m.Creator, m.ApproveDate, m.Approver, m.BuyserIds, m.Brank, m.GK_NO, m.QC_Ref_PPO, m.QC_Ref_GP, m.HF_Ref_PPO, m.HF_Ref_GP, m.CustomerComment ); } } return(dtt); } } }