public void Initialize()
{
DB2DataCatcher <FBProd> ProdCatcher = new DB2DataCatcher <FBProd>("SMVIEW.FBPROD");
ProdCatcher.Conditions = "where prodcat_ident='Production' and prod_id not like 'SL%'";
ProductList = ProdCatcher.GetEntities().EntityList.Select(s => s.Identifier).ToList();
}
public ShareDataEntity()
{
FREQPCatcher = new DB2DataCatcher <FREQPModel>("MMVIEW.FREQP", true);
FHESCHSCatcher = new DB2DataCatcher <FHESCHSModel>("End_Time", "MMVIEW.FHESCHS");
FRCodeCatcher = new DB2DataCatcher <FRCodeModel>("MMVIEW.FRCODE");
FRUserCatcher = new DB2DataCatcher <FRUserModel>("MMVIEW.FRUSER");
}
public List <FRProdModel> GetProdList()
{
string sql = "select prodspec_id, prod_category_id from mmview.frprodspec where prod_category_id in ('Production','Dummy','Process Monitor','Equipment Monitor') order by prodspec_id";
DB2DataCatcher <FRProdModel> ProdCatcher = new DB2DataCatcher <FRProdModel>("", sql);
return(ProdCatcher.GetEntities().EntityList.ToList());
}
private DateTime GetTargetStepInTime(string lot, string mainPD, string openo)
{
DB2DataCatcher <FRLot_SplitLot> splitCatcher = new DB2DataCatcher <FRLot_SplitLot>("MMVIEW.FRLOT");
splitCatcher.Conditions = string.Format("where lot_id like '{0}.%'", lot.Split('.')[0]);
var list = splitCatcher.GetEntities().EntityList;
List <string> lotList = new List <string>();
string templot = lot;
lotList.Add(templot);
while (lot.Split('.')[1] != "00")
{
var temList = list.Where(w => w.Lot_ID == templot);
if (!temList.Any())
{
break;
}
templot = temList.First().Split_Lot_ID;
lotList.Add(templot);
}
HsCatcher.Conditions = string.Format("where lot_id in ('{0}') and ope_category in ('OperationComplete','STB','Split') order by claim_time", string.Join("','", lotList));
HsCatcher.GetEntities();
var hsList = HsCatcher.entities.EntityList.TakeWhile(t => t.Lot_ID != lot).Union(HsCatcher.entities.EntityList.Where(w => w.Lot_ID == lot));
return(hsList.Where(w => w.MainPD_ID == mainPD && w.Ope_No == openo).First().Claim_Time);
}
private void Initialize()
{
BrCatcher = new DB2DataCatcher <BrprivilegeClickCount>("", sql);
var list = BrCatcher.GetEntities().EntityList;
BrprivilegeList = list.Any() ? list.ToList() : new List <BrprivilegeClickCount>();
}
public static List <EDA_Prod_Lot_MappingModel> GetLotAndProdMappingList()
{
DB2DataCatcher <EDA_Prod_Lot_MappingModel> prodCatcher = new DB2DataCatcher <EDA_Prod_Lot_MappingModel>("ISTRPT.EDA_Prod_Lot_Mapping");
var list = prodCatcher.GetEntities().EntityList;
return(list.Any() ? list.Select(s => new EDA_Prod_Lot_MappingModel {
ProdSpec_ID = s.ProdSpec_ID, Prod_Category_ID = s.Prod_Category_ID, Lot_ID = s.Lot_ID.Split('.')[0] + "*"
}).ToList() : new List <EDA_Prod_Lot_MappingModel>());
}
public void GetDataByYear(List <string> privilegeIdList)
{
int year = DateTime.Now.Year;
string sql = string.Format(@"
select privilegeid,max(usage_counter) usage_counter,'Max' as Date from istrpt.RPT_Click_Count_History where date < '{0}-01' group by privilegeid
union
select privilegeid,max(usage_counter), Date from
(
select privilegeid,usage_counter,substr( Date,1,7) date from
istrpt.RPT_Click_Count_History
)
where date between '{0}-01' and '{0}-12'
group by privilegeid,date", year);
ClkCatcher = new DB2DataCatcher <RPT_Click_Count_History>("", sql);
var list = ClkCatcher.GetEntities().EntityList;
for (int i = 1; i <= 12; i++)
{
string strI = i < 10 ? "0" + i : i.ToString();
Items.Add(string.Format("{0}-{1}", year, strI));
}
foreach (var id in privilegeIdList)
{
var entity = new ReqRpt063ClickCountEntity()
{
PrivilegeID = id
};
var list_id = list.Where(w => w.PrivilegeID == id);
if (!list_id.Any())
{
for (int i = 1; i <= 12; i++)
{
entity.ClickCountValues.Add(0);
}
ClickCountEntities.Add(entity);
continue;
}
var max = list_id.Where(w => w.Date == "Max");
var preValue = max.Any() ? max.First().Usage_Counter : 0;
for (int i = 1; i <= 12; i++)
{
string strMonth = year + "-" + (i < 10 ? "0" + i : i.ToString());
var value_list = list_id.Where(w => w.Date == strMonth);
var value = value_list.Any() ? value_list.First().Usage_Counter : preValue;
entity.ClickCountValues.Add(value - preValue);
preValue = value;
}
ClickCountEntities.Add(entity);
}
}
private void Initialize()
{
string sql = string.Format(@"select lot_id,cur_wafer_qty,prev_pd_id pd_id,prev_pd_name pd_name,c.description department
from mmview.fhopehs hs
left join
istrpt.rpt_prod_flow fl
on hs.pd_id=fl.pd_id
left join mmview.frcode c
on c.category_id='Department'
and c.code_id=fl.department
where ope_category ='OperationComplete'
and claim_time between '{0} 08:00:00' and '{1} 08:00:00'
and lot_type='Production'", Date.ToString("yyyy-MM-dd"), Date.AddDays(1).ToString("yyyy-MM-dd"));
MoveCatcher = new DB2DataCatcher <RPT_Move_ByDepartmentModel>("", sql);
var list = MoveCatcher.GetEntities().EntityList;
// if (!list.Any()) return;
sql = string.Format("select c.description department,m.target_value from ISTRPT.RPT_Move_Target m left join mmview.frcode c on c.category_id='Department' and c.code_id=m.dept_code where m.plan_date ='{0}'", Date.ToString("yyyy-MM-dd"));
TargetCatcher = new DB2DataCatcher <Rpt_Move_Target>("", sql);
var list_target = TargetCatcher.GetEntities().EntityList;
var list_dept = new ReqRptCommonDeptQuerier().DeptList;
var wipQuier1 = new ReqRpt002WipQuerier(Date.AddHours(8).ToString("yyyy-MM-dd HH:mm:ss"));
var wipQuier2 = new ReqRpt002WipQuerier(Date.AddHours(20).ToString("yyyy-MM-dd HH:mm:ss"));
var wipQuier3 = new ReqRpt002WipQuerier(Date.AddHours(32).ToString("yyyy-MM-dd HH:mm:ss"));
foreach (var dept in list_dept)
{
var list_target_dept = list_target.Where(w => w.Department == dept.Description);
var move = new ReqRpt002MoveEntity();
move.Department = dept.Description;
move.MoveValue = list.Where(w => w.Department == dept.Description).Sum(s => s.Cur_Wafer_Qty);
move.MoveTarget = list_target_dept.Any() ? list_target_dept.First().Target_Value : 0;
//判断查询时间是否为今天
if (DateTime.Now.Date > Date.AddHours(8) && DateTime.Now.Date < Date.AddHours(32))
{
var target = move.MoveTarget * (DateTime.Now - Date.AddHours(8)).TotalHours / 24;
move.Percentage = target == 0 ? -1 : move.MoveValue * 1.0 / target;
}
else
{
move.Percentage = move.MoveTarget == 0 ? -1 : move.MoveValue * 1.0 / move.MoveTarget;
}
int wip1 = GetWipOfDepartment(wipQuier1, dept.Description);
int wip2 = GetWipOfDepartment(wipQuier2, dept.Description);
int wip3 = GetWipOfDepartment(wipQuier3, dept.Description);
double wip = (wip1 + 2 * wip2 + wip3) / 4.0;
move.AvaWip = wip;
move.TurnRate = wip == 0?-1: move.MoveValue / wip;
MoveEntities.Add(move);
}
}
public void Initialize()
{
DB2DataCatcher <FRCodeModel> ModuleCatcher = new DB2DataCatcher <FRCodeModel>("MMVIEW.FRCODE")
{
Conditions = "where category_id='Department'"
};
var list = ModuleCatcher.GetEntities().EntityList;
list.ToList().ForEach(f => Modules.Add(f.Code_ID, f.Description));
DB2DataCatcher <FREQP_EqpType_EqpID_Mapping> EqpTypeCatcher = new DB2DataCatcher <FREQP_EqpType_EqpID_Mapping>("MMVIEW.FREQP");
EqpTypes = EqpTypeCatcher.GetEntities().EntityList.Select(s => s.Eqp_Type).Where(w => !string.IsNullOrEmpty(w)).Distinct().ToList();
EqpTypes.Sort();
}
public static List <ReqRpt211RowEntity> GetRowEntities(string lot)
{
var lots = lot.Split(',');
var lots_single = lots.Where(w => !w.Contains("*"));
var lots_like = lots.Except(lots_single).Select(s => s.Replace('*', '%'));
string condition = string.Format("where lot_id in ('{0}') or lot_id like '{1}' order by claim_time", string.Join("','", lots_single), string.Join("' or lot_id like '", lots_like));
DB2DataCatcher <EDA_Lot_Wafer_HistModel> histCatcher = new DB2DataCatcher <EDA_Lot_Wafer_HistModel>("ISTRPT.EDA_Lot_Wafer_Hist")
{
Conditions = condition
};
var list = histCatcher.GetEntities().EntityList;
var rowEntities = new List <ReqRpt211RowEntity>();
foreach (var l in list)
{
var entity = new ReqRpt211RowEntity()
{
LotID = l.Lot_ID,
FoupID = l.Cast_ID,
OperID = l.PD_ID,
OperNo = l.Ope_No,
Qty = l.Cur_Wafer_Qty,
OperCategory = l.Ope_Category,
ClaimMemo = l.Claim_Memo,
OperName = l.PD_Name,
OperTime = l.Claim_Time.ToString("yyyy-MM-dd HH:mm:ss"),
RouteID = l.MainPD_ID,
UserDept = l.Dept,
UserFullName = l.User_Full_Name,
UserID = l.Claim_User_ID
};
if (l.WafeList != null)
{
var wl = l.WafeList.Split(',');
foreach (var w in wl)
{
var w_sp = w.Split('.');
if (w_sp.Length == 3 && int.TryParse(w_sp[2], out int temp) && temp < 26)
{
entity.WaferList[temp - 1] = true;
}
}
}
rowEntities.Add(entity);
}
return(rowEntities);
}
public void GetTableData(string eqp, string startDate, string endDate)
{
EqpStr = eqp;
StartDate = startDate;
EndDate = endDate;
var eqps = eqp.Split(',');
var eqps_single = eqps.Where(w => !w.Contains("*"));
var eqps_like = eqps.Except(eqps_single).Select(s => s.Replace('*', '%'));
string condition = string.Format("where start_time between '{0}' and '{1} 23:59:59' ", startDate, endDate);
condition = string.Format("{0} and (eqp_id in ('{1}') or eqp_id like '{2}' ) order by start_time desc", condition, string.Join("','", eqps_single), string.Join("' or eqp_id like '", eqps_like));
DB2DataCatcher <Eda_Eqp_HostoryModel> InlineCatcher = new DB2DataCatcher <Eda_Eqp_HostoryModel>("ISTRPT.EDA_Eqp_History")
{
Conditions = condition
};
var list = InlineCatcher.GetEntities().EntityList;
if (!list.Any())
{
throw new Exception("没有数据");
}
foreach (var l in list)
{
TimeSpan ts = l.End_Time.HasValue?(l.End_Time.Value - l.Start_Time):new TimeSpan();
var entity = new ReqRpt207RowEntity()
{
LotID = l.Lot_ID,
EqpID = l.Eqp_ID,
EqpType = l.Eqp_Type,
CastID = l.Cast_ID,
MainPDID = l.MainPD_ID,
Dept = l.Dept,
StartTime = l.Start_Time.ToString("yyyy-MM-dd HH:mm:ss"),
EndTime = l.End_Time.HasValue? l.End_Time.Value.ToString("yyyy-MM-dd HH:mm:ss"):"",
OpeNo = l.Ope_No,
PDID = l.PD_ID,
PDName = l.PD_Name,
Qty = l.Cur_Wafer_Qty,
RecipeID = l.Recipe_ID,
UserFullName = l.User_Full_Name,
UserID = l.Owner_ID,
RunDur = string.Format("{0}时{1}分{0}秒", Math.Floor(ts.TotalHours), ts.Minutes, ts.Seconds)
};
RowEntities.Add(entity);
}
}
private void Initialize()
{
//DB2Helper db2 = new DB2Helper();
//string sql = "select d_thesystemkey from smview.fbprod where prodcat_ident = 'Production' and mainpd_id = 'APAA'";
//db2.GetSomeData(sql);
//foreach (DataRow dr in db2.dt.Rows)
//{
// TableEntities.Add(new ReqRpt025TableEntity(dr[0].ToString()));
//}
var productQuerier = new ReqRptCommonProductQuerier(6);
foreach (var prod in productQuerier.Prods)
{
TableEntities.Add(new ReqRpt025TableEntity(prod));
}
DB2DataCatcher <RPT_RealTime_Lin> db2Catcher = new DB2DataCatcher <RPT_RealTime_Lin>("ISTRPT.RPT_RealTime_Lin");
db2Catcher.Conditions = string.Format("where start_time>='{0}' and start_time <'{1}' ", strSelecedFrom, strSelectedTo);
var list = db2Catcher.GetEntities().EntityList;
foreach (var item in TableEntities)
{
var rowList = list.Where(w => w.Product_ID == item.ProductID);
var rowList_Fab = rowList.Where(w => w.PartName == "FAB");
var rowList_Wat = rowList.Where(w => w.PartName == "WAT");
if (rowList_Fab.Count() > 0)
{
item.FAB_PassQty = rowList_Fab.Sum(w => w.MoveQty);
item.FAB_ScrapQty = rowList_Fab.Sum(w => w.ScrappedQty);
}
if (rowList_Wat.Count() > 0)
{
item.WAT_PassQty = rowList_Wat.Sum(s => s.MoveQty);
item.WAT_ScrapQty = rowList_Wat.Sum(s => s.ScrappedQty);
}
}
}
/// <summary>
/// 获取各部门某月move target
/// </summary>
/// <param name="month">eg:2019-05</param>
/// <returns></returns>
public void GetTargetListByMonth(string month)
{
int year = Convert.ToInt16(month.Split('-')[0]);
int mon = Convert.ToInt16(month.Split('-')[1]);
string strMonth = string.Format("{0}-{1}", year, mon < 10?"0" + mon:mon.ToString());
string sql = string.Format(@"select
t.Dept_code,plan_date,target_value,c.description department
from istrpt.Rpt_Move_Target t
left join
mmview.frcode c
on
c.category_id='Department'
and c.code_id=t.dept_code where t.plan_date like '{0}-%'", strMonth);
Days = DateTime.DaysInMonth(year, mon);
Month = year.ToString() + "年" + mon.ToString() + "月";
TargetCatcher = new DB2DataCatcher <Rpt_Move_Target>("", sql);
var list = TargetCatcher.GetEntities().EntityList;
if (!list.Any())
{
return;
}
var deptList = list.Select(s => s.Dept_Code).Distinct();
foreach (var dept in deptList)
{
var list_dept = list.Where(w => w.Dept_Code == dept);
ReqRpt002MoveTargetEntity targetEntity = new ReqRpt002MoveTargetEntity()
{
DeptCode = dept
};
targetEntity.Department = list_dept.First().Department;
for (var i = 1; i <= Days; i++)
{
var date = i < 10 ? ("0" + i): i.ToString();
var val = list_dept.Where(w => w.Plan_Date == strMonth + "-" + date);
targetEntity.TargetList.Add(val.Any()?val.First().Target_Value:0);
}
TargetList.Add(targetEntity);
}
}
void Initialize()
{
DetailCatcher = new DB2DataCatcher <Report64_DetailModel>("", sql);
var list = DetailCatcher.GetEntities().EntityList;
if (!list.Any())
{
throw new Exception("DB未查找到该Chart");
}
var detail = list.First();
DetailModel.ValueList = detail.Act_Mean_Value.HasValue? detail.Value_List.Split('|').Select(s => Convert.ToDouble(s).ToString()).ToList():new List <string>();
DetailModel.TimeList = detail.Act_Mean_Value.HasValue? detail.Time_List.Split('|').Select(s => { var arry = s.Split('-'); return(arry[0] + "-" + arry[1] + "-" + arry[2] + " " + arry[3]); }).ToList():new List <string>();
DetailModel.SetModel.ChartName = detail.Cname;
DetailModel.SetModel.ChartTitle = detail.Ctitle;
DetailModel.SetModel.ChartType = GetChartType(detail.Ctype);
DetailModel.SetModel.DcID = detail.Mfld_ID;
DetailModel.SetModel.DcSpecID = detail.DcSpec_ID;
DetailModel.SetModel.From = detail.From_Time.HasValue?detail.From_Time.Value.ToString("yyyy-MM-dd HH:mm:ss"):"";
DetailModel.SetModel.To = detail.To_Time.HasValue ? detail.To_Time.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
DetailModel.SetModel.ItemName = detail.Item_NM;
DetailModel.SetModel.Lcl = TranslateNullableValue(detail.Lcl_Value);
DetailModel.SetModel.Lpl = TranslateNullableValue(detail.Lpl_Value);
DetailModel.SetModel.Lsl = TranslateNullableValue(detail.Lsl_Value);
DetailModel.SetModel.Lwl = TranslateNullableValue(detail.Lwl_Value);
DetailModel.SetModel.MaxPoint = detail.Max_Points.ToString();
DetailModel.SetModel.Mean = TranslateNullableValue(detail.Mean_Value);
DetailModel.SetModel.SampleSize = detail.Sample_Size.ToString();
DetailModel.SetModel.Target = TranslateNullableValue(detail.Target_Value);
DetailModel.SetModel.Ucl = TranslateNullableValue(detail.Ucl_Value);
DetailModel.SetModel.Upl = TranslateNullableValue(detail.Upl_Value);
DetailModel.SetModel.Usl = TranslateNullableValue(detail.Usl_Value);
DetailModel.SetModel.Uwl = TranslateNullableValue(detail.Uwl_Value);
DetailModel.ProcessModel.Lsl = detail.Lsl_Value == 1E-37 ? null : detail.Lsl_Value;
DetailModel.ProcessModel.Lcl = detail.Lcl_Value == 1E-37 ? null : detail.Lcl_Value;
DetailModel.ProcessModel.Usl = detail.Usl_Value == 1E-37 ? null : detail.Usl_Value;
DetailModel.ProcessModel.Ucl = detail.Ucl_Value == 1E-37 ? null : detail.Ucl_Value;
DetailModel.ProcessModel.Target = detail.Target_Value == 1E-37 ? null : detail.Target_Value;
DetailModel.ProcessModel.Mean = detail.Act_Mean_Value;
DetailModel.SetProcessCalcProperty();
DetailModel.SetStaticCalcProperty();
}
public void Initialize()
{
DB2DataCatcher <FRCodeModel> CodeReasonCatcher = new DB2DataCatcher <FRCodeModel>("MMVIEW.FRCODE");
CodeReasonCatcher.Conditions = "where category_id like '%Hold'";
var CodeReasonList = CodeReasonCatcher.GetEntities().EntityList.OrderBy(o => o.Code_ID);
foreach (var item in CodeReasonList)
{
if (!HoldCodeKeyValue.ContainsKey(item.Code_ID))
{
HoldCodeKeyValue.Add(item.Code_ID, item.Description);
}
}
DB2DataCatcher <FBProd> ProdCatcher = new DB2DataCatcher <FBProd>("SMVIEW.FBPROD")
{
Conditions = "where prodcat_ident='Production' and prod_id not like 'SL%'"
};
ProductList = ProdCatcher.GetEntities().EntityList.Select(s => s.Identifier).ToList();
}
private DateTime GetComeinTimeCaseMotherNoIn(SHLLotModel lot, IList <SHLFHOpehsModel> opes)
{
DB2DataCatcher <SHLFRLot_Family> familyCatcher = new DB2DataCatcher <SHLFRLot_Family>("MMVIEW.FRLot")
{
Conditions = string.Format("where lot_family_id='{0}'", lot.Lot_Family_ID)
};
var familyList = familyCatcher.GetEntities().EntityList;
string split_lot = lot.Split_Lot_ID;
while (!string.IsNullOrEmpty(split_lot))
{
var nextSplit = familyList.Where(w => w.Lot_ID == split_lot).First();
var sub_list = opes.TakeWhile(w => !(w.Lot_ID == nextSplit.Lot_ID && w.Ope_Category == "Split"));
var motherLotOpe = sub_list.Where(w => w.Lot_ID == nextSplit.Lot_ID && (w.Ope_Category == "Ope_Complete" || w.Ope_Category == "STB" || w.Ope_Category.Contains("Locate")));
if (motherLotOpe.Any())
{
return(motherLotOpe.Last().Claim_Time);
}
split_lot = nextSplit.Split_Lot_ID;
}
throw new Exception("ReqRpt048SHLTableViewModel:GetComeinTimeCaseMotherNoIn:History Not Found Enter PD record!");
}
private DateTime GetComeinTime(SHLLotModel lot)
{
string lotID = lot.Lot_ID;
string subLotID = string.Join("", lotID.TakeWhile(t => t != '.'));
DB2DataCatcher <SHLFHOpehsModel> opehsCatcher = new DB2DataCatcher <SHLFHOpehsModel>("MMVIEW.FHOPEHS")
{
Conditions = string.Format("where lot_id like '{0}.%' order by claim_time", subLotID)
};
var list = opehsCatcher.GetEntities().EntityList;
var sub_list = list.TakeWhile(w => !(w.Lot_ID == lot.Lot_ID && w.Ope_Category == "Split"));
var motherLotOpe = sub_list.Where(w => w.Lot_ID == lot.Lot_ID && (w.Ope_Category == "Ope_Complete" || w.Ope_Category == "STB" || w.Ope_Category.Contains("Locate")));
if (motherLotOpe.Any())
{
return(motherLotOpe.Last().Claim_Time);
}
else
{
//如果母批也是split。。。
return(GetComeinTimeCaseMotherNoIn(lot, list));
}
}
private void GetDBDatas()
{
string sql = string.Format(@"with t1 as(
select lot_id,pd_id,ope_category,bank_id,claim_time,hold_state,hold_time,cur_wafer_qty,pd_name from mmview.fhopehs where lot_id in
(
select lot_id from mmview.frlot where created_time <'{0}' and (lot_state='ACTIVE' or completion_time >'{0}')
)
and claim_time <'{0}'
and lot_type='Production'
and pd_id!=''
),t2 as (
select lot_id,max(claim_time) claim_time from t1 group by lot_id),
t3 as (
select lot_id,max(claim_time) claim_time from t1 where hold_state!='ONHOLD' group by lot_id
),
t4 as (
select lot_id,min(claim_time) hold_time from
(
select t1.lot_id,t1.claim_time from t1,t3 where t1.lot_id=t3.lot_id and t1.claim_time>t3.claim_Time
) group by lot_id)
select t2.lot_id,t2.claim_time,t1.pd_id,t1.ope_category,t1.hold_state ,t4.hold_time,t1.cur_wafer_qty,t1.bank_id,c.description department,t1.pd_name ope_name
from t2
left join t1
on t1.lot_id=t2.lot_id
and t1.claim_time=t2.claim_time
left join t4
on t2.lot_id=t4.lot_id
left join mmview.frpd pd
on t1.pd_id=pd.pd_id
and pd.pd_level='Operation'
left join mmview.frcode c
on c.category_id='Department'
and c.code_id=pd.department
order by lot_id", DateTime);
WipCatcher = new DB2DataCatcher <RPT__WIP_ByDepartmentModel>("", sql);
WipCatcher.GetEntities();
}
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);
}
/// <summary>
/// SPC Report MainTable 数据工厂,查询起始日期的00:00:00至结束日期的23:59:59数据
/// </summary>
/// <param name="startDate">查询的起始日期，eg:2019-4-27</param>
/// <param name="endDate">查询的结束日期，eg:2019-5-27</param>
public ReqRpt064SpcMainTableDataBuilder(string startDate, string endDate)
{
/*
* sql = string.Format(@"
* select cm.gno,
* cm.cno,
* gm2.dept_id,
* gm2.gname,
* cm.eqp_id,
* cm.ctitle,
* --gm1.product_id,
* cd.usl_value,
* cd.ucl_value,
* cd.target_value,
* cd.lsl_value,
* cd.lcl_value,
* cd.ctype,
* t1.mean_value,
* t1.value_list
* from
* spcview.spccm cm
* -- left join spcview.spcgm1 gm1
* -- on cm.gno=gm1.gno
* -- and cm.collection_type=gm1.collection_type
* -- and gm1.sub_gno=1
* left join spcview.spcgm2 gm2
* on cm.gno=gm2.gno
* and cm.collection_type=gm2.collection_type
* left join spcview.spccd cd
* on cm.gno=cd.gno
* and cm.cno=cd.cno
* and cm.collection_type=cd.collection_type
* left join (
* select gno,cno,ctype,avg(point_value) mean_value,listagg(cast( point_value as varchar(30000)),'|') value_list from spcview.spc_data
* where tx_datetime between '{0} 00:00:00' and '{1} 23:59:59'
* and collection_type=1
* and LTRIM(RTRIM(HIDE_FLAG))=''
* group by gno,cno,ctype
* ) t1
* on t1.gno=cm.gno
* and t1.cno=cm.cno
* and t1.ctype=cd.ctype
* ", startDate, endDate);
*/
sql = string.Format(@"with tb as
(
select gno,cno,ctype,point_value,LSL_Value,USL_Value from spcview.spc_data
where tx_datetime between '{0} 00:00:00' and '{1} 23:59:59'
and collection_type=1
and LTRIM(RTRIM(HIDE_FLAG))=''
),
t1 as (
select gno,cno,ctype,avg(point_value) mean_value,listagg(cast( point_value as varchar(30000)),'|') value_list from tb group by gno,cno,ctype
),
t2 as (
select gno,cno,ctype, count(*) as oos from tb
where (point_value < LSL_Value and LSL_Value !=1.0E-37)
or (point_value>USL_Value and USL_Value!=1.0E-37)
group by gno,cno,ctype
)
select cm.gno,
cm.cno,
gm2.dept_id,
gm2.gname,
cm.eqp_id,
cm.ctitle,
--gm1.product_id,
cd.usl_value,
cd.ucl_value,
cd.target_value,
cd.lsl_value,
cd.lcl_value,
cd.ctype,
t3.mean_value,
t3.value_list ,
t3.oos
from
spcview.spccm cm
-- left join spcview.spcgm1 gm1
-- on cm.gno=gm1.gno
-- and cm.collection_type=gm1.collection_type
-- and gm1.sub_gno=1
left join spcview.spcgm2 gm2
on cm.gno=gm2.gno
and cm.collection_type=gm2.collection_type
left join spcview.spccd cd
on cm.gno=cd.gno
and cm.cno=cd.cno
and cm.collection_type=cd.collection_type
left join (
select t1.*,t2.oos from t1 left join t2 on t1.gno=t2.gno and t1.cno=t2.cno and t1.ctype=t2.ctype
) t3
on t3.gno=cm.gno
and t3.cno=cm.cno
and t3.ctype=cd.ctype", startDate, endDate);
SpcCatcher = new DB2DataCatcher <Report64_SPCModel>("", sql);
Initialize();
}
/// <summary>
/// 获取TableData
/// </summary>
/// <param name="from">查询起始日期，eg：2019-07-31</param>
/// <param name="to">查询结束日期，eg:2019-08-01</param>
public void GetTableData(string from, string to)
{
From = from;
To = to;
#region sql
string sql = string.Format(@"with t1 as(
select
lot_id,
wafer_id,
mainpd_id,
ope_no,
eqp_id,
procrsc_id,
ctrljob_id
from
MMVIEW.FHWCPHS
where proc_time between '{0} 00:00:00' and '{1} 23:59:59'
group by
lot_id,
wafer_id,
mainpd_id,
ope_no,
eqp_id,
procrsc_id,
ctrljob_id
)
select t1.*,h.pd_name,h.claim_time,h.prodspec_id,module.pd_id as modulepd_id from t1 left join mmview.fhopehs h
on t1.lot_id=h.lot_id
and t1.ctrljob_id=h.ctrl_job
and h.ope_category='OperationStart'
left join
(
select pf.mainpd_id,pf.pd_level,pd.module_no,pd.pd_id from mmview.frpf pf
inner join mmview.frpf_pdlist pd
on pf.d_thesystemkey=pd.d_thesystemkey
and pf.state=1
) module
on module.mainpd_id=t1.mainpd_id
and module.module_no=substr(t1.ope_no,1,locate('.',t1.ope_no,1)-1)", from, to);
#endregion
EdaCatcher = new DB2DataCatcher <EDA_Compare_Process_ToolModel>("", sql);
var list = EdaCatcher.GetEntities().EntityList;
var gp = list.GroupBy(g => new { g.Lot_ID, g.Claim_Time });
foreach (var g in gp)
{
var entity = new ReqRpt212RowEntity()
{
EQP = g.First().EQP_ID,
LotID = g.Key.Lot_ID,
OpeName = g.First().PD_Name,
OpeNo = g.First().Ope_NO,
RouteID = g.First().MainPD_ID,
OperID = g.First().ModulePD_ID,
ProdID = g.First().ProdSpec_ID,
OperTime = g.Key.Claim_Time.ToString("yyyy-MM-dd HH:mm:ss")
};
var wafers = g.Select(s => new { wafer = s.Wafer_ID.Split('.').Last(), s.Procrsc_ID });
for (int i = 1; i < 26; i++)
{
string str = i < 10 ? '0' + i.ToString() : i.ToString();
var wafer = wafers.Where(w => w.wafer == str);
entity.ChamberArray[i - 1] = wafer.Count() > 0 ? wafer.First().Procrsc_ID : "";
}
RowEntities.Add(entity);
}
}
public void GetTableDataByManual(string lot, string startDate, string endDate)
{
LotStr = lot;
StartDate = startDate;
EndDate = endDate;
var lots = lot.Split(',');
var lots_single = lots.Where(w => !w.Contains("*"));
var lots_like = lots.Except(lots_single).Select(s => s.Replace('*', '%'));
List <string> ThkHead = new List <string>()
{
"THK", "TK"
};
List <string> CdHead = new List <string>()
{
"CD"
};
string condition = string.Format("where (meas_type='Site'or Item_Type = 'Derived') and claim_time between '{0}' and '{1} 23:59:59' ", startDate, endDate);
condition = string.Format("{0} and ( meas_lot_id in ('{1}') or meas_lot_id like '{2}' )", condition, string.Join("','", lots_single), string.Join("' or meas_lot_id like '", lots_like));
DB2DataCatcher <EDA_Inline_Data> InlineCatcher = new DB2DataCatcher <EDA_Inline_Data>("ISTRPT.EDA_Inline_Data")
{
Conditions = condition
};
var list = InlineCatcher.GetEntities().EntityList;
if (!list.Where(w => w.Item_Type == "Raw").Any())
{
throw new Exception("没有数据");
}
var group = list.GroupBy(g => new { g.Meas_Lot_ID, g.Meas_MainPD_ID, g.Meas_Ope_No, g.Meas_Pass_Count, g.Claim_Time, });
var site_max = list.Where(w => !string.IsNullOrEmpty(w.Site_Position)).Max(m => Convert.ToInt16(m.Site_Position));
foreach (var gp in group)
{
var raw = gp.Where(w => w.Item_Type == "Raw");
var dr = gp.Where(w => w.Item_Type == "Derived");
var g = raw.GroupBy(a => new { a.DcItem_Name, a.Wafer_ID });
foreach (var l in g)
{
var entity = new ReqRpt209RowEntity
{
FoupID = l.First().Cast_ID,
ProdID = l.First().Meas_ProdSpec_ID,
EqpID = l.First().Meas_Eqp_ID,
EqpType = l.First().Eqp_Type,
LC = l.First().Cntl_Lower_Limit,
LotID = l.First().Meas_Lot_ID,
LS = l.First().Spec_Lower_Limit,
MeasItem = l.Key.DcItem_Name,
OpeNo = l.First().Meas_Ope_No,
RouteID = l.First().Meas_MainPD_ID,
OperID = l.First().Meas_PD_ID,
Qty = l.First().Cur_Wafer_Qty,
RecipeID = l.First().Recipe_ID,
SpecItem = l.First().Meas_DcSpec_ID,
Target = l.First().DcItem_Target,
UC = l.First().Cntl_Upper_Limit,
US = l.First().Spec_Upper_Limit,
WaferID = l.First().Wafer_ID,
OpeTime = l.First().Claim_Time.ToString("yyyy-MM-dd HH:mm:ss")
};
for (var i = 0; i < site_max; i++)
{
var site = l.Where(w => w.Site_Position == (i + 1).ToString());
if (site.Any())
{
entity.SiteValue.Add(Math.Round(Convert.ToDouble(site.First().DcItem_Value), 3));
}
else
{
entity.SiteValue.Add(null);
}
}
entity.WaferMean = Math.Round(entity.SiteValue.Average() ?? 0, 3);
if (ThkHead.Any(a => entity.MeasItem.ToUpper().IndexOf(a) == 0) || entity.MeasItem.ToUpper().Contains("$THK"))
{
//thk
var range = dr.Select(s => new { item = s.DcItem_Name.ToUpper(), s.DcItem_Value, s.RangeUC }).Where(w => w.item.Contains("T") && w.item.Contains("K") && w.item.Contains("RANGE"));
if (range.Any())
{
entity.MeanRange = Math.Round(Convert.ToDouble(range.First().DcItem_Value), 3); entity.RangeUC = range.First().RangeUC;
}
var mean = dr.Select(s => new { item = s.DcItem_Name.ToUpper(), s.DcItem_Value, s.Cntl_Lower_Limit, s.Cntl_Upper_Limit }).Where(w => w.item.Contains("MEAN"));
if (mean.Any())
{
entity.LC = mean.First().Cntl_Lower_Limit; entity.UC = mean.First().Cntl_Upper_Limit;
}
}
else if (CdHead.Any(a => entity.MeasItem.ToUpper().IndexOf(a) == 0))
{
//cd
var range = dr.Select(s => new { item = s.DcItem_Name.ToUpper(), s.DcItem_Value, s.RangeUC }).Where(w => w.item.Contains("CD") && w.item.Contains("RANGE"));
if (range.Any())
{
entity.MeanRange = Math.Round(Convert.ToDouble(range.First().DcItem_Value), 3); entity.RangeUC = range.First().RangeUC;
}
}
RowEntities.Add(entity);
}
}
}
public void GetTableDataByManual(string lot, string startDate, string endDate)
{
LotStr = lot;
StartDate = startDate;
EndDate = endDate;
var lots = lot.Split(',');
var lots_single = lots.Where(w => !w.Contains("*"));
var lots_like = lots.Except(lots_single).Select(s => s.Replace('*', '%'));
string condition = string.Format("where ( meas_type='Wafer' or item_type='Raw') and claim_time between '{0}' and '{1} 23:59:59' ", startDate, endDate);
condition = string.Format("{0} and ( meas_lot_id in ('{1}') or meas_lot_id like '{2}' )", condition, string.Join("','", lots_single), string.Join("' or meas_lot_id like '", lots_like));
DB2DataCatcher <EDA_Inline_Data> InlineCatcher = new DB2DataCatcher <EDA_Inline_Data>("ISTRPT.EDA_Inline_Data")
{
Conditions = condition
};
var list = InlineCatcher.GetEntities().EntityList;
if (!list.Any())
{
throw new Exception("没有数据");
}
//var group = list.GroupBy(g => new { g.Meas_Lot_ID, g.Meas_MainPD_ID, g.Meas_Ope_No, g.Meas_Pass_Count, g.Claim_Time,g.DcItem_Name });
var group = list.GroupBy(g => new { g.Meas_Lot_ID, g.Meas_MainPD_ID, g.Meas_Ope_No, g.Meas_Pass_Count, g.Claim_Time });
List <string> ThkHead = new List <string>()
{
"THK", "TK"
};
foreach (var gp in group)
{
var gp_site = gp.Where(w => w.Meas_Type == "Site");
var gp_wafer = gp.Where(w => w.Meas_Type == "Wafer");
var group2 = gp_wafer.GroupBy(g => new { g.DcItem_Name });
foreach (var g in group2)
{
var entity = new ReqRpt206RowEntity
{
LotID = gp.Key.Meas_Lot_ID,
RouteID = gp.Key.Meas_MainPD_ID,
OpeNo = gp.Key.Meas_Ope_No,
OperID = g.First().Meas_PD_ID,
MeasItem = g.Key.DcItem_Name,
SpecItem = g.First().Meas_DcSpec_ID,
LC = g.First().Cntl_Lower_Limit,
LS = g.First().Spec_Lower_Limit,
UC = g.First().Cntl_Upper_Limit,
US = g.First().Spec_Upper_Limit,
Target = g.First().DcItem_Target,
ProdID = g.First().Meas_ProdSpec_ID,
OpeTime = gp.Key.Claim_Time.ToString("yyyy-MM-dd HH:mm:ss")
};
var wafer_t = gp_wafer.Where(w => w.Wafer_ID == "*");
foreach (var w in wafer_t)
{
w.Wafer_ID = (gp_site.Where(wh => wh.Wafer_Position == w.Wafer_Position).FirstOrDefault() ?? new EDA_Inline_Data()).Wafer_ID;
}
for (var i = 0; i < 25; i++)
{
string waferNumber = i < 10 ? "0" + i.ToString() : i.ToString();
entity.WaferValue.Add(Str2DoubleNullable(g.Where(w => w.Wafer_ID.Length > 2 && w.Wafer_ID.Substring(w.Wafer_ID.Length - 2) == waferNumber).Select(s => s.DcItem_Value).FirstOrDefault()));
}
if ((entity.MeasItem.ToUpper().Contains("$THK") && entity.MeasItem.ToUpper().Contains("MEAN")) || ThkHead.Any(a => entity.MeasItem.ToUpper().IndexOf(a) == 0))
{
entity.LS = gp_site.First().Spec_Lower_Limit;
entity.US = gp_site.First().Spec_Upper_Limit;
}
RowEntities.Add(entity);
}
}
}
private void GetTbodyEntities()
{
var list = PostModel.EqpTypes.Split(',');
//获取Actual值
using (RPTContext db = new RPTContext())
{
var rawList = db.EQP_UPm_018.Where(w => DbFunctions.TruncateTime(w.Date) >= sqlStartDate && DbFunctions.TruncateTime(w.Date) <= sqlEndDate && list.Contains(w.EqpType));
foreach (var item in list)
{
ReqRpt020TBodyEntity bodyEntity = new ReqRpt020TBodyEntity();
bodyEntity.TotalRow.EqpType = item;
if (rawList.Count() > 0)
{
var rowList = rawList.Where(w => w.EqpType == item).GroupBy(g => g.EqpID).Select(s => new
{
EqpID = s.Key,
sd = s.Sum(su => su.SDTMin),
pr = s.Sum(su => su.PRDMin),
sb = s.Sum(su => su.SBYMin),
en = s.Sum(su => su.ENGMin),
nd = s.Sum(su => su.UDTMin),
rwk = s.Sum(su => su.ReworkQty),
pass = s.Sum(su => su.Passqty),
eff = s.Sum(su => su.EffMove)
});
foreach (var row in rowList)
{
ReqRpt020TableRowEntity rowEntity = new ReqRpt020TableRowEntity();
double ot = row.pr + row.sb + row.sd + row.nd + row.en;
double ut = row.pr + row.sb;
int days = (D_EndDate - D_StartDate).Days + 1;
rowEntity.EqpType = row.EqpID;
rowEntity.UpmActual = ot == 0 ? 0 : ut / ot;
rowEntity.UumActual = ut == 0 ? 0 : row.pr / ut;
rowEntity.RwkActual = row.rwk / days;
rowEntity.EffActual = row.eff / days;
rowEntity.PassActual = row.pass / days;
rowEntity.ThrActual = row.pr == 0 ? 0 : row.pass / row.pr;
bodyEntity.RowEntities.Add(rowEntity);
}
double ot_all = rowList.Sum(s => s.pr + s.sb + s.sd + s.nd + s.en);
double ut_all = rowList.Sum(s => s.pr + s.sb);
double pr_all = rowList.Sum(s => s.pr);
bodyEntity.TotalRow.UpmActual = ot_all == 0?0:ut_all / ot_all;
bodyEntity.TotalRow.UumActual = ut_all == 0?0:pr_all / ut_all;
bodyEntity.TotalRow.RwkActual = bodyEntity.RowEntities.Average(a => a.RwkActual);
bodyEntity.TotalRow.PassActual = bodyEntity.RowEntities.Average(a => a.PassActual);
bodyEntity.TotalRow.EffActual = bodyEntity.RowEntities.Average(a => a.EffActual);
bodyEntity.TotalRow.ThrActual = pr_all == 0 ? 0 : rowList.Sum(s => s.pass) / pr_all;
}
TbodyEntities.Add(bodyEntity);
}
}
//获取Target值
string condition = string.Join("','", list);
DB2DataCatcher <RPT_EQP_PERFM_TARGET> TargetCatcher = new DB2DataCatcher <RPT_EQP_PERFM_TARGET>("ISTRPT.RPT_EQP_PERFM_TARGET");
TargetCatcher.Conditions = string.Format("where eqp_type in ('{0}')", condition);
var tarList = TargetCatcher.GetEntities().EntityList;
foreach (var item in tarList)
{
var body = TbodyEntities.Where(w => w.TotalRow.EqpType == item.Eqp_Type).First();
body.TotalRow.UpmTarget = item.Upm_Target;
body.TotalRow.UumTarget = item.Uum_Target;
body.TotalRow.PassTarget = item.PassQty_Target;
body.TotalRow.RwkTarget = item.Rework_Target;
body.TotalRow.EffTarget = item.Eff_Target;
body.TotalRow.ThrTarget = item.Wph;
foreach (var row in body.RowEntities)
{
row.UpmTarget = item.Upm_Target;
row.UumTarget = item.Uum_Target;
row.EffTarget = item.Eff_Target;
row.PassTarget = item.PassQty_Target;
row.RwkTarget = item.Rework_Target;
row.ThrTarget = item.Wph;
}
}
}