/// <summary>
/// 获取carPm数据
/// </summary>
/// <param name="siteInfo"></param>
/// <param name="log"></param>
/// <param name="tag"></param>
/// <param name="xmlSql"></param>
private void GetCarPmData(SiteInfo siteInfo, StringBuilder log, object tag, XmlDocument xmlSql, Database db, Database dbMultekApps)
{
// 从配置文件中获取，以分号隔开
string customerStr = xmlSql.SelectSingleNode("/Root/CarPm/Customers").InnerText.Trim();
string querySql = xmlSql.SelectSingleNode("/Root/CarPm/Select").InnerText;
string insertSql = xmlSql.SelectSingleNode("/Root/CarPm/Insert").InnerText;
string countSql = xmlSql.SelectSingleNode("/Root/CarPm/QueryCount").InnerText;
string updateSql = xmlSql.SelectSingleNode("/Root/CarPm/Update").InnerText;
// 拆分用户字段
string[] customs = customerStr.Split(new char[] {';', ',', '；', '，'});
foreach (string custom in customs)
{
if (!string.IsNullOrWhiteSpace(custom))
{
DataTable dtCarPm = dbMultekApps.CustomSql(string.Format(querySql, custom)).ToDataTable();
foreach (DataRow drCarPm in dtCarPm.Rows)
{// 插入每行数据, 如存在则更新,否则插入
int count = db.CustomSql(string.Format(countSql, drCarPm["rpm"].ToString())).ToScalar<int>();
if (count > 0)
{//存在，则更新
string updateSqlNew = string.Format(updateSql,
drCarPm["pdesc"].ToString().Replace("'", "''"),
drCarPm["compelet_date"].ToString(),
drCarPm["resolution"].ToString().Replace("'", "''"),
drCarPm["delay_date"].ToString(),
drCarPm["rpm"].ToString());
db.CustomSql(updateSqlNew).ExecuteNonQuery();
}
else
{//不存在,插入
string insertSqlNew = string.Format(insertSql,
drCarPm["rpm"].ToString(),
custom,
drCarPm["pdesc"].ToString().Replace("'", "''"),
drCarPm["occurr_date"].ToString(),
drCarPm["compelet_date"].ToString(),
drCarPm["resolution"].ToString().Replace("'", "''"),
drCarPm["delay_date"].ToString()
);
db.CustomSql(insertSqlNew).ExecuteNonQuery();
}
}
}
}
}
/// <summary>
/// 获取ForeCast数据
/// </summary>
/// <param name="siteInfo"></param>
/// <param name="log"></param>
/// <param name="tag"></param>
/// <param name="xmlSql"></param>
/// <param name="db"></param>
/// <param name="dbMultekApps"></param>
private void GetPeriodDataByCustom(SiteInfo siteInfo, StringBuilder log, object tag, XmlDocument xmlSql, Database db, Database dbMultekApps)
{
// 从配置文件中获取，以分号隔开
string customerStr = xmlSql.SelectSingleNode("/Root/CarPm/Customers").InnerText.Trim();
// 读取xml中的sql语句
string insertSql = xmlSql.SelectSingleNode("/Root/Period/Insert").InnerText.Trim();
string deleteSql = xmlSql.SelectSingleNode("/Root/Period/Delete").InnerText.Trim();
// 拆分用户字段
string[] customs = customerStr.Split(new char[] { ';', ',', '；', '，' });
foreach (string custom in customs)
{
if (!string.IsNullOrWhiteSpace(custom))
{
using (DbTransaction tran = db.BeginTransaction())
{
db.CustomSql(string.Format(deleteSql, custom))
.SetTransaction(tran)
.ExecuteNonQuery();
string insertSqlNew = string.Format(insertSql, custom);
db.CustomSql(insertSqlNew)
.SetTransaction(tran)
.ExecuteNonQuery();
tran.Commit();
}
}
}
}
/// <summary>
/// job 执行 @sen
/// </summary>
/// <param name="siteInfo"> 配置文件</param>
/// <param name="log">记录日志</param>
/// <param name="tag"></param>
protected override void Execute(SiteInfo siteInfo, StringBuilder log, object tag)
{
DataAccess.DefaultDB = new Database(DatabaseType.SqlServer, siteInfo.ConnectionConfig);
ConnectionStringSettings sqlServerConnectionInit = ConfigurationManager.ConnectionStrings["db_mcn801multekbi"];
Database fwDB = new Database(DatabaseType.SqlServer, sqlServerConnectionInit.ConnectionString);
// 获取文件所在路径
// 路径中没有文件则需要提示，读完文件后需要备份文件
string filePathOTD = ConfigurationManager.AppSettings["FILE_PATH_OTD"];
string filePathNPI = ConfigurationManager.AppSettings["FILE_PATH_NPI"];
string filePathBakup = ConfigurationManager.AppSettings["FILE_PATH_BAKUP"];
string fileNameOTD = ConfigurationManager.AppSettings["FILE_NAME_OTD"];
string fileNameNPI = ConfigurationManager.AppSettings["FILE_NAME_NPI"];
string sourceFile = System.IO.Path.Combine(filePathOTD, fileNameOTD);
string sourceFileNPI = Path.Combine(filePathNPI, fileNameNPI);
DirectoryInfo dirInfo = new DirectoryInfo(filePathOTD);
string userMail = ConfigurationManager.AppSettings["USER_MAIL"];
if (dirInfo.GetFiles().Length + dirInfo.GetDirectories().Length == 0)
{//目录为空, 则给用户发邮件提示
log.Append("The File isn't exsit!;");
}
else if (!File.Exists(sourceFile))
{//OTD文件不存在
log.Append(string.Format("The OTD Data File '{0}' can't find! please check!;", fileNameOTD));
}
else if (!File.Exists(sourceFileNPI))
{//NPI文件不存在
log.Append(string.Format("The NPI Data File '{0}' can't find! please check!", fileNameNPI));
}
else
{
int maxRow = Convert.ToInt32(ConfigurationManager.AppSettings["MAX_ROW"]);
string[] charFlag = new string[] { "BY UNITS", "BY AMOUNT" };
DataSet ds = ExcelHelper.ReadExcel(sourceFile);
DataSet dsNPI = ExcelHelper.ReadExcel(sourceFileNPI);
DataTable dtByItem = new DataTable();
DataTable dtByLeaTime = new DataTable();
DataTable dtNPI = new DataTable();
foreach (DataTable dt in ds.Tables)
{
if (dt.TableName.Trim().ToUpper() == "Customer request OTD".ToUpper())
{// 搜索 By Items
dtByItem = dt.Clone();
dtByItem.TableName = dt.TableName;
bool isByItemRow = false;
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString().ToUpper() == "By Items".ToUpper())
{
isByItemRow = true;
continue;
}
if (isByItemRow == true && !charFlag.Contains(dr[0].ToString().ToUpper()))
{
dtByItem.Rows.Add(dr.ItemArray);
}
else if (isByItemRow == true && charFlag.Contains(dr[0].ToString().ToUpper()))
{
isByItemRow = false;
}
}
}
else if (dt.TableName.Trim().ToUpper() == "Average Leadtime".ToUpper())
{// 读取Average Leadtime sheet 的数据
int currRow = 0;
bool isCustomData = false;
dtByLeaTime = dt.Clone();
dtByLeaTime.TableName = dt.TableName;
foreach (DataRow dr in dt.Rows)
{
if ((dr[0].ToString().ToUpper() == "Customer".ToUpper() || isCustomData == true) && currRow <= maxRow)
{
isCustomData = true;
currRow += 1;
dtByLeaTime.Rows.Add(dr.ItemArray);
}
}
}
else { continue; }
}
// 获取当前的财务周，取上一个财务周的数据插入更新
string getFweekSql = @"select FiscalYear, FiscalWeek from calendar where calenderDate = CONVERT(varchar(100), GETDATE(), 23)";
DataTable dtFw = fwDB.CustomSql(getFweekSql).ToDataTable();
if (dtFw.Rows.Count > 0)
{// 获取当前FiscalWeek
string fiscalWeek = dtFw.Rows[0]["FiscalWeek"].ToString();
string fiscalYear = dtFw.Rows[0]["FiscalYear"].ToString();
// dtByItem: type = 1
InsertCumstomOTD(dtByItem, 1, fiscalWeek, fiscalYear, log);
// dtByLeaTime: type = 2
InsertCumstomOTD(dtByLeaTime, 2, fiscalWeek, fiscalYear, log);
// bakup file
BakupFile(fileNameOTD, filePathOTD, filePathBakup);
}
else
{// 当前财务周找不到，则发邮件提醒维护数据
log.Append("the current fiacal week can't find in the database, plasee check it!;");
}
if (dsNPI.Tables.Count > 0)
{// 插入Npi 数据
dtNPI = dsNPI.Tables[0];
dtNPI.TableName = dsNPI.Tables[0].TableName;
Dictionary<string, string> dic = CreateMonthToHead();
InsertCumstomNPI(dtNPI, log, dic);
// bakup file
BakupFile(fileNameNPI, filePathNPI, filePathBakup);
}
}
// send mail
SendEmailTo(log.ToString(), userMail);
}
