private void TryDeleteAllContributions()
{
if (Testing)
{
return;
}
//var db = DbUtil.Create(Host);
if (!Db2.Setting("UploadExcelIpsDeleteGifts"))
{
return;
}
var deletesql = @"
DELETE dbo.BundleDetail
FROM dbo.BundleDetail d
JOIN dbo.Contribution c ON d.ContributionId = c.ContributionId
DELETE dbo.Contribution
DELETE dbo.BundleHeader
DBCC CHECKIDENT ('[Contribution]', RESEED, 0)
DBCC CHECKIDENT ('[BundleHeader]', RESEED, 0)
DBCC CHECKIDENT ('[BundleDetail]', RESEED, 0)
";
Db2.ExecuteCommand(deletesql);
}
public override bool DoUpload(ExcelPackage pkg)
{
var rt = Db2.UploadPeopleRuns.OrderByDescending(mm => mm.Id).First();
var ws = pkg.Workbook.Worksheets[PeopleSheetName];
FetchData(pkg.Workbook.Worksheets[PeopleSheetName]);
const string sheet = "Personal Data";
CheckColumn("IndividualId", sheet);
CheckColumn("FamilyId", sheet);
CheckColumn("First", sheet);
CheckColumn("Last", sheet);
string sid = ((object) Datalist[0].IndividualId).ToString();
if (sid.ToCharArray().Any(char.IsLetter))
AlphaNumericIds = true;
if(AlphaNumericIds)
peopleidsa = Db2.PeopleExtras.Where(vv => vv.Field == "IndividualId" && vv.Data.Length > 0)
.ToDictionary(vv => vv.Data, vv => vv.PeopleId);
else
peopleids = Db2.PeopleExtras.Where(vv => vv.Field == "IndividualId" && vv.IntValue != null)
.ToDictionary(vv => vv.IntValue ?? 0, vv => vv.PeopleId);
UploadPeople(rt, ws);
TryDeleteAllContributions();
UploadPledges(rt, pkg);
UploadGifts(rt, pkg);
rt.Completed = DateTime.Now;
Db2.SubmitChanges();
return true;
}
//private void GetLastUpdateTimeFromFile()
//{
// DateTime dt = DateTime.Now.Date;
// string json = System.IO.File.ReadAllText(DirPath + LastTimeKeepFileName);
// DateTime.TryParseExact(JsonConvert.DeserializeObject<CycleTimeRunningConfig>(json).LastUpdateTime, "yyyy-MM-dd-HH.mm.ss.ffffff", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out dt);
// StartTime = dt;
//}
private void HandleDBData()
{
Db2.GetSomeData(DelNotExistOpeStepAtSMSql);
Db2.GetSomeData(CmpSmOpeWithRptFlowSql);
FlowSumCatcher = new DB2OperDataCatcher <RPT_FLOW_SUM>("MMVIEW.RPT_FLOW_SUM", Db2);
FlowEqpCatcher = new DB2OperDataCatcher <RPT_FLOW_SUM_EQPBASE>("ISTRPT.RPT_FLOW_SUM_EQPBASE", Db2);
// LRcpCatcher = new DB2OperDataCatcher<RPT_FLOW_SUM_LRCP>("ISTRPT.RPT_FLOW_SUM_LRCP",Db2);
SETCatcher = new DB2OperDataCatcher <FBLRCP_SSET_M>("SMVIEW.FBLRCP_SSET_M", Db2);
MR_LRCatcher = new DB2OperDataCatcher <RPT_FLOW_SUM_MR_LR_MAPPING>("ISTRPT.RPT_FLOW_SUM_MR_LR_MAPPING", Db2);
EQPType_LRCatcher = new DB2OperDataCatcher <RPT_FLOW_SUM_EQPT_LR_MAPPING>("ISTRPT.RPT_FLOW_SUM_EQPT_LR_MAPPING", Db2);
var FlowSumList = FlowSumCatcher.GetEntities().EntityList;
var FlowEqpList = FlowEqpCatcher.GetEntities().EntityList;
//var LRcpList = LRcpCatcher.GetEntities().EntityList;
//var SetList = SETCatcher.GetEntities().EntityList;
IList <FBLRCP_SSET_M> SetList = null;
//var MR_LRList = MR_LRCatcher.GetEntities().EntityList;
IList <RPT_FLOW_SUM_MR_LR_MAPPING> MR_LRList = null;
//var EqpType_LRList = EQPType_LRCatcher.GetEntities().EntityList;
IList <RPT_FLOW_SUM_EQPT_LR_MAPPING> EqpType_LRList = null;
//重置待更新的字段,并在该遍历中获取EqpType、EqpList、Logic Recipe
var newFlowList = FlowSumList.Select(s => new RPT_FLOW_SUM()
{
ProdSpec_ID = s.ProdSpec_ID, MainPD_ID = s.MainPD_ID, Ope_No = s.Ope_No, PD_ID = s.PD_ID, LRecipe = s.LRecipe
});
List <RPT_FLOW_SUM> UpdateList = new List <RPT_FLOW_SUM>();
foreach (var item in newFlowList)
{
try
{
SetEqpInfo(item, FlowEqpList);
// SetLRcpInfo(item, LRcpList);
bool hasGet = SetMRcpInfoBySset(item, SetList);
if (!hasGet)
{
SetMRcpInfoByDset(item, MR_LRList);
}
if (item.Eqp_Type == "")
{
SetEqpType(item, EqpType_LRList);
}
var rawFlow = FlowSumList.Where(w => w.ProdSpec_ID == item.ProdSpec_ID && w.MainPD_ID == item.MainPD_ID && w.Ope_No == item.Ope_No).First();
if (!(rawFlow.Eqp_Type == item.Eqp_Type && rawFlow.LRecipe == item.LRecipe && rawFlow.MRecipe_List == item.MRecipe_List && rawFlow.Eqp_List == item.Eqp_List))
{
UpdateList.Add(item);
}
}
catch (Exception e)
{
LogHelper.ErrorLog(string.Format("RPTFlowSumTranslator>HandleDBData>foreach模块,ProdSpec_ID={0},MainPD_ID={1},Ope_NO={2}", item.ProdSpec_ID, item.MainPD_ID, item.Ope_No), e);
}
}
List <string> sqlList = UpdateList.Select(s => string.Format("UPDATE MMVIEW.RPT_FLOW_SUM SET EQP_TYPE ='{0}',EQP_LIST='{1}',MRECIPE_LIST='{3}',LAST_UPDATE_TIME=CURRENT TIMESTAMP WHERE PRODSPEC_ID='{4}' AND MAINPD_ID='{5}' AND OPE_NO='{6}'", s.Eqp_Type, s.Eqp_List, s.LRecipe, s.MRecipe_List, s.ProdSpec_ID, s.MainPD_ID, s.Ope_No)).ToList();
Db2.UpdateBatchCommand(sqlList);
}
private void UploadPledges(UploadPeopleRun rt, ExcelPackage pkg)
{
//var db = DbUtil.Create(Host);
var data = FetchPledgeData(pkg.Workbook.Worksheets["Pledges"]).ToList();
rt.Count = data.Count;
rt.Description = $"Uploading Pledges {(Testing ? "in testing mode" : "for real")}";
rt.Processed = 0;
Db2.SubmitChanges();
var weeks = (from g in data
group g by g.Date.Sunday() into weeklypledges
select weeklypledges).ToList();
BundleHeader bh = null;
var c = Db2.Content("OrphanedPledges", "---", ContentTypeCode.TypeText);
c.Body = "";
Db2.SubmitChanges();
foreach (var week in weeks)
{
FinishBundle(Db2, bh);
//if (!Testing)
//{
// Db2.Dispose();
// db = DbUtil.Create(Host);
//}
bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.Pledge,
BundleStatusId = BundleStatusCode.Closed,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Today,
ContributionDate = week.Key,
};
foreach (var pledge in week)
{
var pid = GetPeopleId(pledge);
var f = new ContributionFund {
FundId = 0
};
if (!Testing)
{
if (!pid.HasValue)
{
if (IgnoreMissingGifts)
{
c = Db2.Content("OrphanedPledges");
c.Body += $"{pledge.IndividualId} {pledge.Date:d} {pledge.Amount:C}\n";
Db2.SubmitChanges();
continue;
}
else
{
throw new Exception($"peopleid not found from individualid {pledge.IndividualId}");
}
}
f = Db2.FetchOrCreateFund(pledge.FundId, pledge.FundName ?? pledge.FundDescription);
f.FundPledgeFlag = true;
}
var bd = new BundleDetail();
bd.CreatedBy = Util.UserId;
bd.CreatedDate = DateTime.Now;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = pledge.Date,
FundId = f.FundId,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.Pledge,
ContributionAmount = pledge.Amount,
PeopleId = pid
};
bh.BundleDetails.Add(bd);
rt.Processed++;
Db2.SubmitChanges();
}
}
FinishBundle(Db2, bh);
//if (!Testing)
//{
// DbUtil.Db.Dispose();
//}
}
/// <summary>
/// 按测段导出二等水准手簿
/// </summary>
/// <param name="excelApp">Excel对象</param>
/// <param name="fileName">成果文件名</param>
/// <param name="partInfo">测段对象</param>
/// <param name="unitName">测量单位名称</param>
private void part2Excel(Excel.Application excelApp, string fileName, PartInfo partInfo, string unitName)
{
string strExcelFile = Path.GetDirectoryName(Application.ExecutablePath) + @"\观测手簿模版.xlt";
Excel.Workbook excelWB = excelApp.Workbooks.Add(strExcelFile);
Excel.Worksheet excelSheet = excelWB.Sheets[1];// 工作表
excelSheet.Cells[2, 3].Value =unitName; // 单位名称
// 获得测站数据范围和数据总结范围
Excel.Range theRange, theRange1, theRange2;
theRange1 = excelSheet.Range[excelSheet.Cells[7, 1], excelSheet.Cells[9, 10]]; // 测站数据范围
theRange2 = excelSheet.Range[excelSheet.Cells[10, 1], excelSheet.Cells[14, 10]]; //线路汇总区域范围
int n = partInfo.StationCount; // 测站数
theRange2.Cut(excelSheet.Range[excelSheet.Cells[10 + (n - 1) * 3, 1], excelSheet.Cells[14 + (n - 1) * 3, 10]]); // 先剪切赋值线路汇总区域范围
string fName0, bName0;// 开始已知点点名，结束已知点点名
fName0= bName0="";
string fName, bName;//前视点点名，后视点点名
double bPtH, fPtH;//前视点高程，后视点高程
bPtH = fPtH = 0.0;
fName=bName="";
double Rf1, Rf2, Rb1, Rb2;//中丝读数
double deltRf, deltRb;//读数差
double Df1, Df2, Db1, Db2;//前后视距
double deltD;//视距差
double deltH1, deltH2, deltH;//单次高差，平均高差
double SumdeltD=0.0;//累计视距差
theRange1.Copy();
// 开始赋值观测手簿
for (int i = 0; i < n; i++)
{
Application.DoEvents();
fName = partInfo.StationList[i].FPtName;
bName =partInfo.StationList[i].BPtName;
if (i == 0) bName0 = bName; // 开始点
if (i == n-1) fName0 = fName; // 结束点’
//中丝读数
Rf1 = partInfo.StationList[i].Rf1;
Rf2 = partInfo.StationList[i].Rf2;
Rb1 = partInfo.StationList[i].Rb1;
Rb2 = partInfo.StationList[i].Rb2;
//读数差
deltRf = Rf1 - Rf2;
deltRb = Rb1 - Rb2;
//高差及平均高差
deltH1 = Rb1 - Rf1;
deltH2 = Rb2 - Rf2;
deltH = partInfo.StationList[i].DeltH;
//前、后视点高程
bPtH = partInfo.StationList[i].BPtH;
fPtH = partInfo.StationList[i].FPtH;
//视距
Db1 = partInfo.StationList[i].Db1;
Db2 = partInfo.StationList[i].Db2;
Df1 = partInfo.StationList[i].Df1;
Df2 = partInfo.StationList[i].Df2;
deltD = partInfo.StationList[i].DeltD;
SumdeltD = SumdeltD + deltD;
theRange = excelSheet.Range[excelSheet.Cells[7 +i * 3, 1], excelSheet.Cells[9 + i * 3, 10]];
theRange.PasteSpecial(Excel.XlPasteType.xlPasteFormats);
excelSheet.Cells[7 + i * 3, 1].Value = i + 1; // 序号
excelSheet.Cells[7 + i * 3, 2].Value = bName; // 后视点
excelSheet.Cells[7 + i * 3 + 1, 2].Value = fName; // 前视点
excelSheet.Cells[7 + i * 3, 3].Value = Db1.ToString("0.00000"); // 后视距1
excelSheet.Cells[7 + i * 3 + 1, 3].Value = Df1.ToString("0.00000"); // 前视距1
excelSheet.Cells[7 + i * 3 + 2, 3].Value = deltD.ToString("0.00000"); // 视距差
excelSheet.Cells[7 + i * 3, 4].Value = Db2.ToString("0.00000"); // 后视距2
excelSheet.Cells[7 + i * 3 + 1, 4].Value = Df2.ToString("0.00000"); // 前视距2
excelSheet.Cells[7 + i * 3 + 2, 4].Value = SumdeltD.ToString("0.00000"); // 累计视距差
excelSheet.Cells[7 + i * 3, 5].Value = Rb1.ToString("0.00000"); // 后视读数1
excelSheet.Cells[7 + i * 3 + 1, 5].Value = Rf1.ToString("0.00000"); // 前视读数1
excelSheet.Cells[7 + i * 3 + 2, 5].Value = deltH1.ToString("0.00000"); // 高差1
excelSheet.Cells[7 + i * 3, 6].Value = Rb2.ToString("0.00000"); // 后视读数2
excelSheet.Cells[7 + i * 3 + 1, 6].Value = Rf2.ToString("0.00000"); // 前视读数2
excelSheet.Cells[7 + i * 3 + 2, 6].Value = deltH2.ToString("0.00000"); // 高差2
excelSheet.Cells[7 + i * 3, 7].Value = (deltRb * 1000).ToString("0.00"); // 后视读数差
excelSheet.Cells[7 + i * 3 + 1, 7].Value = (deltRf * 1000).ToString("0.00"); // 前视读数差
excelSheet.Cells[7 + i * 3 + 2, 7].Value = ((deltH1 - deltH2) * 1000).ToString("0.00"); // 高差互差
excelSheet.Cells[7 + i * 3 + 2, 8].Value = deltH.ToString("0.00000"); // 高差
excelSheet.Cells[7 + i * 3, 9].Value = bPtH.ToString("0.00000"); // 后视点高程
excelSheet.Cells[7 + i * 3 + 1, 9].Value = fPtH.ToString("0.00000"); // 前视点高程
}//endfor
excelSheet.Cells[10 + (n - 1) * 3, 3].Value = partInfo.StartPtName;//线路开始点名
excelSheet.Cells[10 + (n - 1) * 3 + 1, 3].Value = partInfo.EndPtName; //线路结束点名
excelSheet.Cells[10 + (n - 1) * 3 + 2, 3].Value = (partInfo.Df / 1000).ToString("0.00000"); // 累计前视距
excelSheet.Cells[10 + (n - 1) * 3 + 3, 3].Value = (partInfo.Db / 1000).ToString("0.00000"); // 累计后视距
excelSheet.Cells[10 + (n - 1) * 3 + 1, 6].Value = (partInfo.Db-partInfo.Df).ToString("0.00000"); // 累计视距差
excelSheet.Cells[10 + (n - 1) * 3 + 2, 6].Value = partInfo.dz.ToString("0.00000"); // 累计高差
excelSheet.Cells[10 + (n - 1) * 3 + 3, 6].Value = ((partInfo.Db + partInfo.Df )/ 1000).ToString("0.00000"); // 测段距离
//合并单元格
for (int i = 0; i <= n; i++)
{
theRange = excelSheet.Range[excelSheet.Cells[7 + i * 3, 1], excelSheet.Cells[9 + i * 3, 1]];
theRange.Merge();
}//endfor
excelWB.SaveAs(fileName);//保存文件
excelWB.Close();
}
private void UploadGifts(UploadPeopleRun rt, ExcelPackage pkg)
{
var db = DbUtil.Create(Host);
var data = FetchContributionData(pkg.Workbook.Worksheets["Gift Data"]).ToList();
rt.Count = data.Count;
rt.Description = $"Uploading Gifts {(Testing ? "in testing mode" : "for real")}";
rt.Processed = 0;
Db2.SubmitChanges();
var weeks = (from g in data
group g by g.Date.Sunday() into weeklygifts
select weeklygifts).ToList();
BundleHeader bh = null;
var c = db.Content("OrphanedGifts", "---", ContentTypeCode.TypeText);
c.Body = "";
db.SubmitChanges();
foreach (var week in weeks)
{
FinishBundle(db, bh);
if (!Testing)
{
db.Dispose();
db = DbUtil.Create(Host);
}
bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.ChecksAndCash,
BundleStatusId = BundleStatusCode.Closed,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Today,
ContributionDate = week.Key,
};
foreach (var gift in week)
{
var pid = GetPeopleId(gift);
if (!Testing)
{
if (!pid.HasValue)
{
if (IgnoreMissingGifts)
{
c = db.Content("OrphanedGifts");
c.Body += $"{gift.IndividualId} {gift.Date:d} {gift.Amount:C}\n";
db.SubmitChanges();
continue;
}
else
{
throw new Exception($"peopleid not found from individualid {gift.IndividualId}");
}
}
}
if (!Testing)
{
db.FetchOrCreateFund(gift.FundId, gift.FundName ?? gift.FundDescription);
}
var bd = new BundleDetail();
bd.CreatedBy = Util.UserId;
bd.CreatedDate = DateTime.Now;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = gift.Date,
FundId = gift.FundId,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
ContributionAmount = gift.Amount,
CheckNo = gift.CheckNo,
PeopleId = pid
};
bh.BundleDetails.Add(bd);
rt.Processed++;
Db2.SubmitChanges();
}
}
FinishBundle(db, bh);
if (!Testing)
{
db.Dispose();
}
}
private void HandleDBData()
{
Db2.GetSomeData(DelCurrentDateContentFromTimeFlowTableSql);
Db2.GetSomeData(InsertCurrentDateContentToTimeFlowTableSql);
Db2.GetSomeData(DeleteContentFromTimeFlowTSql);
Db2.GetSomeData(DelOldDataFromTimeFlowTableSql);
PDCatcher = new DB2OperDataCatcher <FRPD_PD_ID>("MMVIEW.FRPD", Db2)
{
Conditions = "WHERE PD_LEVEL='Main' AND PD_TYPE IN ('Production')"
};
ProcTimeCatcher = new DB2OperDataCatcher <RPTH_LOT_PROC_TIME>("MMVIEW.RPTH_LOT_PROC_TIME", Db2);
TimeFlowTPusher = new DB2OperDataPusher <RPT_STD_TIME_FLOW_T>("MMVIEW.RPT_STD_TIME_FLOW_T", Db2);
var pd_list = PDCatcher.GetEntities().EntityList.Select(s => s.PD_ID);
string pdCondition = string.Join("','", pd_list);
ProcTimeCatcher.Conditions = string.Format(@"WHERE
OPE_START_TIME BETWEEN '{0}' AND '{1}'
AND OPE_COMP_USER_ID ='TCS'
AND LAST_OPE_CATEGORY IN ('OperationComplete')
AND OPE_PASS_COUNT=1
AND QTY=25
AND MAINPD_ID IN ('{2}' )
ORDER BY LOT_ID, OPE_START_TIME", sqlStartTime, sqlEndTime, pdCondition);
var proc_list = ProcTimeCatcher.GetEntities().EntityList;
if (!proc_list.Any())
{
return;
}
string lotIDCondition = string.Join("','", proc_list.Select(s => s.Lot_ID).Distinct());
ProcTimeCatcher = new DB2OperDataCatcher <RPTH_LOT_PROC_TIME>("MMVIEW.RPTH_LOT_PROC_TIME", Db2)
{
Conditions = string.Format("where LAST_OPE_CATEGORY IN ('OperationComplete', 'STB') and AND MAINPD_ID IN '{0}' and lot_id in ('{1}') ", pdCondition, lotIDCondition)
};
var allProcList = ProcTimeCatcher.GetEntities().EntityList;
if (!allProcList.Any())
{
return;
}
foreach (var proc in proc_list)
{
var p = allProcList.Where(w => w.Lot_ID == proc.Lot_ID && w.Ope_Start_Time < proc.Ope_Start_Time);
if (p.Any())
{
var entity = p.OrderBy(o => o.Ope_Start_Time).Last();
RPT_STD_TIME_FLOW_T fLOW_T = new RPT_STD_TIME_FLOW_T()
{
Lot_ID = proc.Lot_ID, ProdSpec_ID = proc.ProdSpec_ID, MainPD_ID = proc.MainPD_ID, Ope_No = proc.Ope_No
};
bool isComplete = entity.Last_Ope_Category == "OperationComplete";
fLOW_T.PD_Proc_Time_Sec = isComplete? entity.Process_Duration_Sec:0;
fLOW_T.PD_Wait_Time_Sec = isComplete? Convert.ToInt16((proc.Process_Start_Time - entity.Process_End_Time).TotalSeconds): Convert.ToInt16((proc.Process_Start_Time - entity.Ope_Start_Time).TotalSeconds);
}
}
Db2.GetSomeData(MergeFormTimeFlowSql);
Db2.GetSomeData(MergeFormTimeTableSql);
Db2.GetSomeData(DeleteContentFromTimeFlowTSql);
Db2.GetSomeData(DelCurrentDateContentFromCycleTimeSql);
Db2.GetSomeData(InsertIntoCycleTimeSql);
MergeUpdateCycleTimeSql = string.Format(@"MERGE INTO MMVIEW.RPT_STD_TIME_CYCLETIME M
USING (
SELECT PRODSPEC_ID, MAINPD_ID,
AVG(DOUBLE(TIMESTAMPDIFF(2, COMPLETION_TIME - RELEASED_TIME))/60) AS FLOW_CYCLE_TIME_MIN
FROM MMVIEW.FRLOT
WHERE
LOT_STATE='FINSIHED' AND LOT_FINISHED_STATE='COMPLETED'
AND LOT_TYPE='Production'
AND COMPLETION_TIME BETWEEN '{0}' AND '{1}'
AND QTY=25
GROUP BY PRODSPEC_ID, MAINPD_ID ) C
ON
M.CYCLETIME_RECORD_DATE=CURRENT DATE
AND M.PRODSPEC_ID=C.PRODSPEC_ID
AND M.MAINPD_ID=C.MAINPD_ID
WHEN MATCHED THEN
UPDATE SET
FLOW_CYCLE_TIME_MIN=C. FLOW_CYCLE_TIME_MIN,
FLOW_CYCLE_TIME_TYPE=1,
FLOW_WAITTIME_FACTOR_MIN= C.FLOW_CYCLE_TIME_MIN/ FLOW_PROC_TIME_MIN,
FLOW_WAITTIME_FACTOR_TYPE=1", sqlStartTime, sqlEndTime);
Db2.GetSomeData(MergeUpdateCycleTimeSql);
Db2.GetSomeData(MergeUpdateTimeTable);
RewriteLastUpdateTimeToFile();
}
