private void btn0InitFirst_Click(object sender, EventArgs e)
{
if (_InitFirst.Equals("1"))
{
if (MessageBox.Show("Notice", "Are you Sure to Update First,That will use some time. Please Wait.", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//
var tmpcom = " CONTRACT='" + _CONTRACT + "'";
try
{
_scheduler.Shutdown();
//1table INVENTORY_PART_TAB
var tmpsql_INVENTORY_PART_TAB = "select * from INVENTORY_PART_TAB where " + tmpcom;
var allCount = OracleDal.GetCount(AutoUpdateData._DBOracle11DBname, "INVENTORY_PART_TAB", tmpsql_INVENTORY_PART_TAB);
logger.DebugFormat("*************initFirst INVENTORY_PART_TAB:总有 {0} 条.", allCount);
var tmpds_INVENTORY_PART_TAB = OracleDal.Query(tmpsql_INVENTORY_PART_TAB);
tmpds_INVENTORY_PART_TAB.DataSetName = "INVENTORY_PART_TAB";
//**************************同步 INVENTORY_PART_TAB
OracleDal.StartToMSSQL(true, false, tmpds_INVENTORY_PART_TAB, "");
logger.DebugFormat("*************initFirst INVENTORY_PART_TAB 成功.");
//
}
catch (Exception ex)
{
logger.Error("**************更新失败:" + ex.Message);
}
//2
}
}
}
public void Execute(Quartz.IJobExecutionContext context)
{
if (AutoUpdateData._isUploading)
{
logger.DebugFormat("***************************Previous job is In Upload. Please wait。。。 {0}", context.PreviousFireTimeUtc.Value.DateTime);
AutoUpdateData.jobflag("P Please wait。。。revious job is In Upload:" + context.PreviousFireTimeUtc.Value.DateTime);
return;
}
DataTable tblPicked;
BssLineStock bssLineStock = new BssLineStock();
AutoUpdateData._isUploading = true;
//get sql update mode
//1-删除后再追加 2-直接更新
if (AutoUpdateData._updatemode.StartsWith("1"))
{
_is1 = true;
}
else if (AutoUpdateData._updatemode.StartsWith("2"))
{
_is1 = false;
}
else
{
_is1 = true;
}
logger.DebugFormat("执行更新任务!!!!!!!!!!!!!!!");
AutoUpdateData.jobflag("Is Runing, Next Exec Job Time:" + context.NextFireTimeUtc.Value.DateTime);
try
{
var _tmpOracleDBname = AutoUpdateData._DBOracle11DBname;
//init even tInitIniToday
AutoUpdateData.tInitIniToday(DateTime.Now.ToString("yyyyMMdd"));
//get
if (AutoUpdateData._tableList.Count > 0)
{
logger.Debug("执行数据获取任务!!!!!!!!!!!!!!!");
var tmpBatch = AutoUpdateData._txt1batchNum;
AutoUpdateData._dsList.Clear();
var tmpwhereFirst = getInSql(AutoUpdateData._CONTRACT, "CONTRACT", false);
if (string.IsNullOrEmpty(tmpwhereFirst))
{
return;
}
var tmpwhere = tmpwhereFirst;
foreach (var item in AutoUpdateData._tableList)
{
try
{
//init attr
_typeOfTable = "0";
_time_start = DateTime.Now.ToString();
_time_done = DateTime.Now.ToString();
_sql = "select * from ";
tmpwhere = tmpwhereFirst;
if (item.Key.Contains('|'))
{
var td = item.Key.Split('|');
var tmpTableTakeDataNum = "";
int preNum = 0;
var allCount = 0;
var tmpKeyname = td[0].Trim() + "_KEY";
var tmpKeyLast = td[0].Trim() + "." + td[1].Trim();
var tmpds = new DataSet();
//father and son
var isSon = false;
var tmpdsForFatherSon = new DataSet();
tmpds.DataSetName = td[0].Trim();
//test
//if (item.Value != 3)
//{
// continue;
//}
switch (item.Value)
{
case 1:
//key: 0 table | where 1 | order by 2 | type 3
var tmpInsql = getInSql(AutoUpdateData._N_OBL_PART_TYPE, td[1].Trim(), true);
if (string.IsNullOrEmpty(tmpInsql))
{
continue;
}
else
{
tmpwhere += tmpInsql;
}
//pre update number
tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim());
if (!int.TryParse(tmpTableTakeDataNum, out preNum))
{
preNum = 0;
}
//get all count form oracle db
allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere);
logger.DebugFormat("*********Table: {0},已上传：{1} ,Oracle 现在有数据：{2}.当日：{3}", td[0], preNum, allCount, DateTime.Now.ToString("yyyyMMdd"));
if (preNum >= allCount)
{
logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount);
}
else
{
var tmptoUpdate = (allCount - preNum);
logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate);
var tmporderby = td[2].Trim();
if (td[2].Trim().ToLower().Equals("no"))
{
tmporderby = "";
}
tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate);
}
tmpds.DataSetName = td[0].Trim();
_typeOfTable = td[3].Trim();
break;
case 2:
//key: 0 table | add Id 1 | order by 2 | datefrom 3 | type 4
//get the last ID
//get from SQL by id;
//var tmpTRANSACTION_ID = AutoUpdateData._iniToday.IniReadValue("TableKeyLastValue", tmpKeyLast);
var tmpTRANSACTION_ID = DbHelperSQL.GetDMaxID(td[1], td[0]);
// get last where
tmpwhere += " and " + td[1].Trim() + ">='" + tmpTRANSACTION_ID + "' ";
//pre update number
tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim());
if (!int.TryParse(tmpTableTakeDataNum, out preNum))
{
preNum = 0;
}
//get all count form oracle db
OracleParameter[] parameters2 = { new OracleParameter(":gxsj", OracleDbType.Varchar2, 10) };
parameters2[0].Value = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd") + " 00:00:00"; // HH
if (tmpTRANSACTION_ID == 1)
{
logger.DebugFormat("******************************{0} 初始更新，加限时间-3天。", td[0]);
_time_start = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd") + " 00:00:00";
tmpwhere += " and " + td[3] + ">=to_date(:gxsj,'yyyy-MM-dd HH24:mi:ss')";
allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere, parameters2);
}
else
{
allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere);
}
logger.DebugFormat("*********Table: {0},已上传：{1} ,Oracle 现在有数据：{2}.当日：{3}", td[0], preNum, allCount, DateTime.Now.ToString("yyyyMMdd"));
if (preNum >= allCount)
{
logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount);
}
else
{
var tmptoUpdate = (allCount - preNum);
logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate);
var tmporderby = td[2].Trim();
if (td[2].Trim().ToLower().Equals("no"))
{
tmporderby = "";
}
if (tmpTRANSACTION_ID == 1)
{
tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate, parameters2);
}
else
{
tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate);
}
}
tmpds.DataSetName = td[0].Trim();
_typeOfTable = td[4].Trim();
_time_done = OracleDal.getMaxCol(tmpds, td[3]).ToString();
break;
case 3:
//key: 0 table|1 where|2 order by | type 3
//get per last datetime
var tmpLastWhere = AutoUpdateData._iniToday.IniReadValue("TableKeyLastValue", tmpKeyLast);
var tmpLastWhereDateTime = DateTime.Now;
if (!DateTime.TryParse(tmpLastWhere, out tmpLastWhereDateTime))
{
tmpLastWhereDateTime = DateTime.Now;
AutoUpdateData._iniToday.IniWriteValue("TableKeyLastValue", tmpKeyLast, tmpLastWhereDateTime.ToString());
}
tmpwhere += " and to_char(" + td[1] + ", 'yyyymmddHH24miss') >= :gxsj";
OracleParameter[] parameters3 = { new OracleParameter(":gxsj", OracleDbType.Varchar2, 10) };
//no time
parameters3[0].Value = DateTime.Now.ToString("yyyyMMdd000000");
//for N_AIS_SHOP_LIST_PICKED_ACT_TAB
var tmpnewDateTime = DateTime.Now.ToString("yyyyMMdd000000");
if (td[0].Trim().ToUpper().Equals("N_AIS_SHOP_LIST_PICKED_ACT_TAB".ToUpper()))
{
tmpnewDateTime = DbHelperSQL.GetTableFieldDateTime("AISPICK_UPD_DATE", "M_CONTOROL", " [KEY-ID]='01'");
parameters3[0].Value = tmpnewDateTime;
logger.DebugFormat("**************{0},{1} change to new value:{2}.", td[0], td[1], tmpnewDateTime);
}
//pre update number
tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim());
if (!int.TryParse(tmpTableTakeDataNum, out preNum))
{
preNum = 0;
}
allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere, parameters3);
logger.DebugFormat("*********Table: {0},已上传：{1} ,Oracle 现在有数据：{2}.当日：{3}", td[0], preNum, allCount, DateTime.Now.ToString("yyyyMMdd"));
if (preNum >= allCount)
{
logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount);
}
else
{
var tmptoUpdate = (allCount - preNum);
logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate);
var tmporderby = td[2].Trim();
if (td[2].Trim().ToLower().Equals("no"))
{
tmporderby = "";
}
tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate, parameters3);
}
tmpds.DataSetName = td[0].Trim();
_typeOfTable = td[3].Trim();
_time_done = OracleDal.getMaxCol(tmpds, td[1]).ToString();
#region new update 2015-12-02
if (tmpds.Tables.Count > 0 && td[0].Trim().ToUpper().Equals("N_AIS_SHOP_LIST_PICKED_ACT_TAB".ToUpper()))
{
logger.DebugFormat("*******开始更新{0}的相关表[M_PARTS_STOCK,M_LINE_PARTS_STOCK]", td[0].Trim());
//AIS配膳リストピッキング実績-> AIS配膳リストピッキング実績
tblPicked = tmpds.Tables[0]; // bssLineStock.GetPickedActData();
//更新ライン在庫マス数据
if (bssLineStock.SetLinePartsStockData(tblPicked) <= 0)
{
//TODO： 更新失败
logger.ErrorFormat("******* {0} 的相关表[M_PARTS_STOCK,M_LINE_PARTS_STOCK] 更新失败", td[0].Trim());
}
else
{
logger.DebugFormat("******* {0} 的相关表[M_PARTS_STOCK,M_LINE_PARTS_STOCK] 更新Success", td[0].Trim());
var tmpExist = DbHelperSQL.GetCount("M_CONTOROL", "[KEY-ID]='01'");
var tmpsql = "";
if (tmpExist > 0)
{
tmpsql = string.Format("UPDATE M_CONTOROL set [AISPICK_UPD_DATE]='{0}' where [KEY-ID]='01'", _time_done);
}
else
{
tmpsql = string.Format("INSERT INTO M_CONTOROL([KEY-ID],[AISPICK_UPD_DATE]) VALUES('{0}','{1}')", "01", _time_done);
}
var tmpdd = DbHelperSQL.ExecuteSql(tmpsql);
if (tmpdd > 0)
{
logger.DebugFormat("*******Success M_CONTOROL 更新时间 AISPICK_UPD_DATE success", td[0].Trim());
}
else
{
logger.DebugFormat("*******Error M_CONTOROL 更新时间 AISPICK_UPD_DATE Error", td[0].Trim());
}
}
}
#endregion
break;
case 4:
//key: P 0|where 1| order by 2 | C 3 | type 4
//get P：父，C: 子 根据P的Key得到C.的数据。
//get the last ID
// set tmpwhere
tmpwhere += " and " + td[1] + ">=to_date(:gxsj,'yyyy-MM-dd HH24:mi:ss')";
OracleParameter[] parameters4 = { new OracleParameter(":gxsj", OracleDbType.Varchar2, 10) };
//set time
parameters4[0].Value = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; // HH
logger.DebugFormat("****TESTFKG:{0}，ORG_START_DATE:{1} 【0:の場合 PCの日付を抽出条件にする。1:の場合 環境ファイル内の日付（yyyy-mm-dd)を設定し、その日付を抽出条件にする】", AutoUpdateData._TESTFKG, AutoUpdateData._ORG_START_DATE);
if (AutoUpdateData._TESTFKG.Equals("1"))
{
logger.DebugFormat("****使用配置文件中的日期。{0}", AutoUpdateData._ORG_START_DATE);
parameters4[0].Value = AutoUpdateData._ORG_START_DATE + " 00:00:00"; // HH
}
var trytmpDD = DateTime.Now;
var chedkdate = DateTime.TryParse(parameters4[0].Value.ToString(), out trytmpDD);
if (!chedkdate)
{
logger.DebugFormat("**配置文件提供ORG_START_DATE的值不符合要求(yyyy-MM-dd)，value:{0}.则使用当天日期。", AutoUpdateData._ORG_START_DATE, DateTime.Now.ToString("yyyy-MM-dd"));
logger.ErrorFormat("**配置文件提供ORG_START_DATE的值不符合要求(yyyy-MM-dd)，value:{0}.则使用当天日期。", AutoUpdateData._ORG_START_DATE, DateTime.Now.ToString("yyyy-MM-dd"));
parameters4[0].Value = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; // HH
}
// parameters4[0].Value = tmpORG_START_DATE.ToString("yyyy-MM-dd") + " 00:00:00";// HH
//pre update number
tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim());
if (!int.TryParse(tmpTableTakeDataNum, out preNum))
{
preNum = 0;
}
//get all count form oracle db
allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere, parameters4);
logger.DebugFormat("*********Table: {0},已上传：{1} ,Oracle 现在有数据：{2}.>=条件日期：{3}", td[0], preNum, allCount, trytmpDD.ToString("yyyy-MM-dd"));
if (preNum >= allCount)
{
logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount);
}
else
{
var tmptoUpdate = (allCount - preNum);
logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate);
var tmporderby = td[2].Trim();
if (td[2].Trim().ToLower().Equals("no"))
{
tmporderby = "";
}
tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate, parameters4);
}
tmpds.DataSetName = td[0].Trim();
//to Get C
if (!AutoUpdateData._tableKeyList.ContainsKey(tmpKeyname))
{
isSon = false;
logger.DebugFormat("*************************表：{0} 没有设置主键，无法更新子表:{1}。", td[0], td[3]);
}
else
{
//var tmpkeyValue = AutoUpdateData._tableKeyList[tmpKeyname];
//var tmpkeys = tmpkeyValue.Split(',');
//logger.DebugFormat("*************************表：{0} 的主键：{1}。", td[0], tmpkeyValue);
isSon = true;
}
tmpds.DataSetName = td[0].Trim();
_typeOfTable = td[4].Trim();
_time_done = OracleDal.getMaxCol(tmpds, td[1]).ToString();
break;
default:
tmpds.DataSetName = td[0].Trim();
if (td[0].IndexOf('.') > 0)
{
tmpds.DataSetName = td[0].Trim().Split('.')[1];
}
break;
}
_sql += td[0].Trim() + " where " + tmpwhere;
//**************************同步表
OracleDal.StartToMSSQL(_is1, false, tmpds, tmpKeyLast);
//for father and son
if (isSon)
{
var tmpallSonCount = 0;
if (tmpds.Tables.Count > 0)
{
var tmpkeyValue = AutoUpdateData._tableKeyList[tmpKeyname];
var tmpkeys = tmpkeyValue.Split(',');
if (tmpkeys.Count() <= 0)
{
logger.DebugFormat("*************************表：{0} 的主键 为空：{1}。", td[0], tmpkeyValue);
}
else
{
logger.DebugFormat("*************************表：{0} 的主键：{1}。", td[0], tmpkeyValue);
logger.DebugFormat("*************************开始更新表：{0}，子表：{1}。", td[0], td[3]);
string sonTmpsonwhereAll = "";
foreach (DataRow p in tmpds.Tables[0].Rows)
{
string sonTmpsonwhere = "";
try
{
var tmpsonwhere = "";
tmpsonwhere = "SELECT * FROM " + td[3].Trim() + " where ";
if (!string.IsNullOrEmpty(_tmpOracleDBname))
{
tmpsonwhere = "SELECT * FROM " + _tmpOracleDBname + "." + td[3].Trim() + " where ";
}
if (tmpkeys.Count() > 0)
{
if (tmpkeys.Count() == 1)
{
tmpsonwhere += tmpkeys[0] + "='" + p[tmpkeys[0]].ToString().Trim() + "'";
}
else
{
for (int i = 0; i < tmpkeys.Count(); i++)
{
if (i == 0)
{
tmpsonwhere += tmpkeys[0] + "='" + p[tmpkeys[0]].ToString().Trim() + "'";
}
else
{
tmpsonwhere += " and " + tmpkeys[i] + "='" + p[tmpkeys[i]].ToString().Trim() + "'";
}
}
}
}
//
sonTmpsonwhere = tmpsonwhere;
sonTmpsonwhereAll += sonTmpsonwhere + "\n";
//get data
var tmpSon = OracleDal.Query(tmpsonwhere);
tmpSon.DataSetName = td[3].Trim();
//**************************同步子表
tmpallSonCount += OracleDal.StartToMSSQL(_is1, true, tmpSon, "");
}
catch (Exception ex)
{
logger.ErrorFormat("****************************更新主表：{0},记录：{1} -->的子表失败。{2}", td[0], (p[0].ToString() + "," + p[1].ToString() + "," + p[2].ToString()), ex.Message);
//OracleDal.ilog(td[3].Trim(), allCount, AutoUpdateData._CONTRACT + ",Fail," + AutoUpdateData._updatemode, "AutoUpdateOracleMSSQL: SQL:" + sonTmpsonwhere + " Fail. Error:" + ex.Message, AutoUpdateData._ipAddMac);
OracleDal.ilog("error", updateJob._typeOfTable, updateJob._time_start, updateJob._time_done, sonTmpsonwhere, allCount, AutoUpdateData._ipAddMac, AutoUpdateData._CONTRACT + "|" + AutoUpdateData._updatemode);
continue;
}
}
//OracleDal.ilog(td[3].Trim(), tmpallSonCount, AutoUpdateData._CONTRACT + ",Success," + AutoUpdateData._updatemode, "AutoUpdateOracleMSSQL:Update Count:" + tmpallSonCount + " Success.", AutoUpdateData._ipAddMac);
OracleDal.ilog("success", updateJob._typeOfTable, updateJob._time_start, updateJob._time_done, sonTmpsonwhereAll, tmpallSonCount, AutoUpdateData._ipAddMac, AutoUpdateData._CONTRACT + "|" + AutoUpdateData._updatemode);
}
}
else
{
logger.DebugFormat("*************************表：{0} 的数据 为空。", td[0]);
}
}
}
else
{
var tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", item.Key.Trim());
int preNum = 0;
if (!int.TryParse(tmpTableTakeDataNum, out preNum))
{
preNum = 0;
}
var allCount = OracleDal.GetCount(_tmpOracleDBname, item.Key, "");
logger.DebugFormat("*********Table: {0},已上传：{1} ,Oracle 现在有数据：{2}.", item.Key, preNum, allCount);
if (preNum >= allCount)
{
logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount);
}
else
{
var tmptoUpdate = (allCount - preNum);
logger.DebugFormat("*********需更新数:{0}", tmptoUpdate);
var tmpds = OracleDal.GetData(_tmpOracleDBname, item.Key, "", "", preNum, tmptoUpdate);
tmpds.DataSetName = item.Key;
OracleDal.StartToMSSQL(_is1, false, tmpds, "");
}
}
}
catch (Exception ex)
{
AutoUpdateData.jobflag("Error:" + ex.Message);
logger.ErrorFormat("{0}表同步有问题，开始同步下个表。Error:{1}.", item.Key, ex.Message);
continue;
}
}
}
else
{
logger.Error("no Table,Please check Set.ini,and add Table.");
}
}
catch (Exception ex)
{
AutoUpdateData.jobflag("Error:" + ex.Message);
logger.Error(ex);
}
finally
{
AutoUpdateData._isUploading = false;
}
AutoUpdateData.jobflag("Notice: Current Job is Run Over, Next Exec Job Time:" + context.NextFireTimeUtc.Value.DateTime);
}