public JsonResult savecoordinate(List <ReqRpt216coordinate> savelist) { try { //首先检查该RecipeName是否有坐标存在 string sql = string.Format("select count(1) as num from ISTRPT.RPT_WAT_Recipe_coordinate where RecipeName='{0}'", savelist.First().RECIPENAME); DB2Helper db2 = new DB2Helper(); db2.GetSomeData(sql); DataTable dt = db2.dt; if (Convert.ToInt32(dt.Rows[0][0]) > 0) { return(Json("exist")); } List <String> list = new List <string>(); String sql2 = ""; foreach (ReqRpt216coordinate o in savelist) { sql2 = string.Format("insert into ISTRPT.RPT_WAT_Recipe_coordinate(RECIPENAME,SITENAME,COORDINATE,CREATETIME,OWNER) values('{0}','{1}','{2}','{3}','{4}') ;", o.RECIPENAME, o.SITENAME, o.COORDINATEX + ',' + o.COORDINATEY, o.CREATETIME, o.OWNER); list.Add(sql2); } db2.UpdateBatchCommand(list); return(Json("success")); } catch (Exception) { return(Json("error")); } }
public static void Run() { string[] sql = { @"delete from istrpt.rpt_click_count_history where date=to_char(current date, 'yyyy-MM-dd')", @"insert into istrpt.rpt_click_count_history ( select privilegeid, usage_counter, to_char(current date, 'yyyy-MM-dd'), current timestamp from istrpt.rptfuncusage )" }; DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(sql.ToList()); }
/// <summary> /// 设置某月每天WaferStart都是同一数据 /// </summary> /// <param name="prod">产品</param> /// <param name="year">年</param> /// <param name="month">月</param> /// <param name="value">计划值</param> public void SetValueByMonth(string prod, int year, int month, int value) { string strMonth = month < 10 ? "0" + month : month.ToString(); PlanCatcher.Conditions = string.Format("where prodspec_id ='{0}' and plan_date like '{1}-{2}%'", prod, year, strMonth); var list = PlanCatcher.GetEntities().EntityList; int days = DateTime.DaysInMonth(year, month); List <string> sqls = new List <string>(); for (int i = 1; i <= days; i++) { string d = i < 10?"0" + i:i.ToString(); string date = year + "-" + strMonth + "-" + d; if (!list.Any(a => a.Plan_Date == date)) { sqls.Add(string.Format("insert into {0} values ('{1}','{2}',0,0)", tableName, prod, date)); } sqls.Add(string.Format("update {0} set plan_start_pcs ={1} where prodspec_id='{2}' and plan_date like '{3}-{4}%'", tableName, value, prod, year, strMonth)); } int res = DB2.UpdateBatchCommand(sqls); }
public JsonResult deletecoordinate() { try { List <String> list = new List <string>(); string RECIPENAME = Request["RECIPENAME"].ToString(); string SITENAME = Request["SITENAME"].ToString(); string sql = string.Format("delete from ISTRPT.RPT_WAT_Recipe_coordinate where RECIPENAME='{0}' and SITENAME='{1}';", RECIPENAME, SITENAME); list.Add(sql); DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(list); return(Json("success")); } catch (Exception) { return(Json("error")); } }
//主工作进程 private void Initialize() { //判断postmodel if (PostModel == null || PostModel.Count == 0) { Message = "上传的数据非预期合法数据!"; return; } var list_eqpType = PostModel.Where(w => IsNatural_Number(w.eqpType)).Select(s => s.eqpType.Trim()).Distinct(); if (list_eqpType == null || list_eqpType.Count() == 0) { Message = "上传的数据非预期合法数据!"; return; } var list_eqpType_db = TargetCatcher.GetEntities().EntityList; //如果db中的表非空 if ((list_eqpType_db != null) && list_eqpType_db.Count() > 0) { //交集 var list_eqpType_update = list_eqpType.Intersect(list_eqpType_db.Select(s => s.EQP_TYPE)); var updateSql = list_eqpType_update.Select(s => PostModel.Where(w => w.eqpType == s).FirstOrDefault()).Select(s => new { s.eqpType, rework = FixStringToDouble(s.rework), wph = FixStringToDouble(s.throughput), upm = FixStringToDouble(s.upm), uum = FixStringToDouble(s.uum) }).Select(s => string.Format("update ISTRPT.RPT_EQP_PERFM_TARGET set upm_target={0},uum_target={1},passqty_target={2},rework_target={3},eff_target={4},wph={5} where eqp_type='{6}'", s.upm, s.uum, (s.upm * s.uum * 24 * s.wph).ToString("f0"), s.rework, (s.upm * s.uum * 24 * s.wph * (1 - s.rework)).ToString("f0"), s.wph, s.eqpType)); //差集 var list_eqpType_insert = list_eqpType.Except(list_eqpType_db.Select(s => s.EQP_TYPE)); InsertData(list_eqpType_insert); if (list_eqpType_insert.Count() > 0) { Message += string.Format("{0}数据插入成功<br />", string.Join("、", list_eqpType_insert)); } DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(updateSql.ToList()); if (list_eqpType_update.Count() > 0) { Message += string.Format("{0}数据更新成功<br />", string.Join("、", list_eqpType_update)); } } else //db为空则直接插入 { InsertData(list_eqpType); Message += string.Format("{0}数据插入成功<br />", string.Join("、", list_eqpType)); } }
public JsonResult changecoordinate(List <ReqRpt216coordinate> changelist) { try { List <String> list = new List <string>(); String sql = ""; foreach (ReqRpt216coordinate o in changelist) { sql = string.Format("update ISTRPT.RPT_WAT_Recipe_coordinate set Coordinate='{0}',CreateTime='{1}',Owner='{2}' where RecipeName='{3}' and SiteName='{4}';", o.COORDINATEX + ',' + o.COORDINATEY, o.CREATETIME, o.OWNER, o.RECIPENAME, o.SITENAME); list.Add(sql); } DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(list); return(Json("success")); } catch (Exception) { return(Json("error")); } }
private void Initialize() { List <string> list_lot = Regex.Split(LotID, "%#", RegexOptions.IgnoreCase).ToList(); List <string> list_remark = Regex.Split(Remark, "%#", RegexOptions.IgnoreCase).ToList(); List <string> list_purpose = Regex.Split(Purpose, "%#", RegexOptions.IgnoreCase).ToList(); if (list_lot.Count <= 0) { new Exception("没有要更新的Lot"); } List <string> sqlList = new List <string>(); for (int i = 0; i < list_lot.Count; i++) { string remark = string.IsNullOrEmpty(list_remark[i])?"":list_remark[i]; string purpose = string.IsNullOrEmpty(list_purpose[i]) ? "" : list_purpose[i]; sqlList.Add(string.Format("update istrpt.rpt_wip_special_lot set remark='{0}',purpose='{1}' where lot_id='{2}'", remark, purpose, list_lot[i])); } DB2Helper db2 = new DB2Helper(); Message = db2.UpdateBatchCommand(sqlList); }
public void SetTargetListByMonth(ReqRpt002SetMoveTargetPostModel postModel) { string month = postModel.date.ToString("yyyy-MM"); var dayArray = postModel.dayArray.Select(s => month + "-" + (s < 10 ? "0" + s : s.ToString())).ToList(); //获取deptList DB2DataCatcher <FRCodeModel> codeCatcher = new DB2DataCatcher <FRCodeModel>("MMVIEW.FRCODE") { Conditions = "where category_id='Department'" }; var deptList = codeCatcher.GetEntities().EntityList; var deptOpeList = postModel.deptData.Select(s => s.dept).Distinct(); var deptCodeOpeList = deptList.Where(w => deptOpeList.Contains(w.Description)).Select(s => s.Code_ID); //执行删除旧数据的动作 List <string> sqls = new List <string>(); string delSql = string.Format("delete from istrpt.RPT_MOVE_TARGET where dept_code in ('{0}') and plan_date in ('{1}')", string.Join("','", deptCodeOpeList), string.Join("','", dayArray)); sqls.Add(delSql); foreach (var dept in deptOpeList) { var code_list = deptList.Where(w => w.Description == dept); if (code_list.Any()) { var deptData = postModel.deptData.Where(w => w.dept == dept).First(); string code = code_list.First().Code_ID; List <string> valueList = new List <string>(); for (int i = 0; i < dayArray.Count(); i++) { string value = string.Format("('{0}','{1}',{2})", code, dayArray[i], deptData.targetArray[i]); valueList.Add(value); } string sql = string.Format("insert into istrpt.rpt_move_target values {0}", string.Join(",", valueList)); sqls.Add(sql); } } DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(sqls); }
//为RealTimeList赋值 private void DoWork() { DBCatcher.Conditions = string.Format("where lot_type='Production' and Claim_time >='{0}' and Claim_Time < '{1}'", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), End_Time.ToString("yyyy-MM-dd HH:mm:ss")); DBCatcher2.Conditions = string.Format("where lot_type='Production' and Scrap_time >='{0}' and Scrap_Time < '{1}'", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), End_Time.ToString("yyyy-MM-dd HH:mm:ss")); var list = DBCatcher.GetEntities().EntityList; var list2 = DBCatcher2.GetEntities().EntityList; if (list.Count() <= 0) { throw new Exception("ReqRpt025Translator.GetBaseList()没有获取符合条件的数据"); } LotCatcher.Conditions = string.Format("where lot_type='Production' and Created_Time <='{0}' and (Completion_Time >='{1}' or Completion_Time='1901-01-01 00:00:00' )", End_Time.ToString("yyyy-MM-dd HH:mm:ss"), Start_Time.ToString("yyyy-MM-dd HH:mm:ss")); var lotList = LotCatcher.GetEntities().EntityList; OpehsCatcher.Conditions = string.Format("where lot_type='Production' and Ope_Category='OperationComplete' and PD_Type not in ('Dummy','Measurement') and Claim_Time between '{0}' and '{1}'", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), End_Time.ToString("yyyy-MM-dd HH:mm:ss")); var OphesList = OpehsCatcher.GetEntities().EntityList; DateTime firstTime = Start_Time; while (firstTime < End_Time) { var rawList = list.Where(w => w.Claim_Time >= firstTime && w.Claim_Time < firstTime.AddHours(12)); var rawList2 = list2.Where(w => w.Scrap_Time >= firstTime && w.Scrap_Time < firstTime.AddHours(12)); var rawLotList = lotList.Where(w => w.Created_Time <= firstTime && (w.Completion_Time >= firstTime || w.Completion_Time == DateTime.Parse("1901-01-01 00:00:00"))); var rawOpehsList = OphesList.Where(w => w.Claim_Time >= firstTime && w.Claim_Time < firstTime.AddHours(12)); if (rawList.Count() > 0) { rawList.GroupBy(g => new { g.ProdSpec_ID, g.PartName }).Select(s => new { Product = s.Key, Qty = s.Sum(i => i.Cur_Wafer_Qty) }).ToList().ForEach(f => RealTimeList.Add(new RPT_RealTime_Lin() { Product_ID = f.Product.ProdSpec_ID, MoveQty = f.Qty, PartName = f.Product.PartName, Start_Time = firstTime, ScrappedQty = 0 })); rawList.Select(s => new { s.Claim_Time, s.Cur_Wafer_Qty, EqpType = s.Eqp_ID.Substring(0, 6), s.ProdSpec_ID }).GroupBy(g => new { g.ProdSpec_ID, g.EqpType }).Select(s => new { Product = s.Key, Qty = s.Sum(i => i.Cur_Wafer_Qty) }).ToList().ForEach(f => MoveList.Add(new RPT_Move_By_EqpType() { Product_ID = f.Product.ProdSpec_ID, EqpType = f.Product.EqpType, Start_Time = firstTime, MoveQty = f.Qty })); } if (rawList2.Count() > 0) { rawList2.GroupBy(g => new { g.ProdSpec_ID, g.PartName }).Select(s => new { Product = s.Key, Qty = s.Sum(i => i.Qty) }).ToList().ForEach(f => RealTimeList.Where(w => w.Product_ID == f.Product.ProdSpec_ID && w.PartName == f.Product.PartName && w.Start_Time == firstTime).FirstOrDefault().ScrappedQty = f.Qty); } if (rawLotList.Count() > 0) { var group = rawLotList.GroupBy(g => g.ProdSpec_ID); foreach (var gp in group) { var entity = new RPT_Turn_Daily() { Product_ID = gp.Key, Start_Time = firstTime, WIP = gp.Count(), MoveQty = RealTimeList.Where(w => w.Product_ID == gp.Key).Sum(s => s.MoveQty), EffectiveSteps = rawOpehsList.Where(w => w.ProdSpec_ID == gp.Key).Count() }; TurnDailyList.Add(entity); } } firstTime = firstTime.AddHours(12); } //input or update var RealTimeCurrent = new DB2DataCatcher <RPT_RealTime_Lin>("ISTRPT.RPT_RealTime_Lin") { Conditions = string.Format("where Start_Time >='{0}' and Start_Time<='{1}'", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), End_Time.ToString("yyyy-MM-dd HH:mm:ss")) }.GetEntities().EntityList; var TurnDailyCurrent = new DB2DataCatcher <RPT_Turn_Daily>("ISTRPT.RPT_Turn_Daily") { Conditions = string.Format("where Start_Time >='{0}' and Start_Time<='{1}'", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), End_Time.ToString("yyyy-MM-dd HH:mm:ss")) }.GetEntities().EntityList; var MoveCurrent = new DB2DataCatcher <RPT_Move_By_EqpType>("ISTRPT.RPT_Move_By_EqpType") { Conditions = string.Format("where Start_Time >='{0}' and Start_Time<='{1}'", Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), End_Time.ToString("yyyy-MM-dd HH:mm:ss")) }.GetEntities().EntityList; foreach (var item in RealTimeList) { if (RealTimeCurrent.Where(w => w.Start_Time == item.Start_Time && w.Product_ID == item.Product_ID && w.PartName == item.PartName).Count() == 0) { DBPusher.entities.EntityList.Add(item); } else { UpdateSqlList.Add(string.Format("update ISTRPT.RPT_RealTime_Lin set MOVEQTY={0}, ScrappedQTY={4} where Start_Time='{1}' and Product_ID='{2}' and PartName='{3}'", item.MoveQty, item.Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), item.Product_ID, item.PartName, item.ScrappedQty)); } } foreach (var item in TurnDailyList) { if (TurnDailyCurrent.Where(w => w.Start_Time == item.Start_Time && w.Product_ID == item.Product_ID).Count() == 0) { TurnPusher.entities.EntityList.Add(item); } else { UpdateSqlList.Add(string.Format("update ISTRPT.RPT_Turn_Daily set MoveQty={0},EffectiveSteps={1},WIP={2} where Start_Time='{3}' and Product_ID='{4}'", item.MoveQty.ToString(), item.EffectiveSteps.ToString(), item.WIP.ToString(), item.Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), item.Product_ID)); } } foreach (var item in MoveList) { if (MoveCurrent.Where(w => w.Start_Time == item.Start_Time && w.Product_ID == item.Product_ID && w.EqpType == item.EqpType).Count() == 0) { MovePusher.entities.EntityList.Add(item); } else { UpdateSqlList.Add(string.Format("update ISTRPT.RPT_Move_By_EqpType set MOVEQTY={0} where Start_Time='{1}' and Product_ID='{2}' and EqpType='{3}' ", item.MoveQty, item.Start_Time.ToString("yyyy-MM-dd HH:mm:ss"), item.Product_ID, item.EqpType)); } } if (DBPusher.entities.EntityList.Count() > 0) { DBPusher.PushEntities(); } if (TurnPusher.entities.EntityList.Count() > 0) { TurnPusher.PushEntities(); } if (MovePusher.entities.EntityList.Count() > 0) { MovePusher.PushEntities(); } if (UpdateSqlList.Count > 0) { DB2Helper db2 = new DB2Helper(); db2.UpdateBatchCommand(UpdateSqlList); } }