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