/// <summary>
/// 导入价格
/// </summary>
/// <param name="pEntry"></param>
/// <returns></returns>
public void MegerPrice(OrderImportInfo pEntry)
{
if (_ConnectionString == null)
{
return;
}
_SQL = string.Format("MERGE INTO pO_PriceConfig T ");
_SQL += string.Format(" USING ( ");
_SQL += string.Format(" SELECT B.ERPID BasicDataID,C.ERPID BrandID,D.ERPID SeriesID,E.ERPID CommodityID,F.PricingFactorCode PricingFactorID,A.GeneralFactoryPrice,A.BranchFactoryPrice,A.BeginDate,A.EndDate,1 IsEnable ");
_SQL += string.Format(" FROM (SELECT '{0}' BasicDataID,'{1}' BrandID,'{2}' SeriesID,'{3}' CommodityID,'{4}' PricingFactorID,{5} GeneralFactoryPrice,{6} BranchFactoryPrice,'{7}' BeginDate,'{8}' EndDate) AS A ", pEntry.BasicDataID, pEntry.BrandID, pEntry.SeriesID, pEntry.CommodityID, pEntry.PricingFactorID, pEntry.GeneralFactoryPrice, pEntry.BranchFactoryPrice, pEntry.TimeBegin, pEntry.TimeEnd);
_SQL += string.Format(" LEFT JOIN PO_BasicData B ON A.BasicDataID = B.FName AND B.ParentID = '5865fdf403244e' AND B.IsDisable = 1 ");
_SQL += string.Format(" LEFT JOIN PO_BasicData C ON A.BrandID = C.FName AND C.[Type] = 9 AND C.IsDisable = 1 ");
_SQL += string.Format(" LEFT JOIN PO_BasicData D ON A.SeriesID = D.FName AND D.[Type] = 10 AND D.IsDisable = 1 ");
_SQL += string.Format(" LEFT JOIN PO_Commodity E ON A.CommodityID = E.CommodityName AND E.SeriesERPID = D.ERPID ");
_SQL += string.Format(" LEFT JOIN PO_PricingFactor F ON A.PricingFactorID = F.PricingFactorName AND F.IsDisable = 1 ");
_SQL += string.Format(" ) AS O ON T.BasicDataID = O.BasicDataID AND T.BrandID = O.BrandID AND T.SeriesID = O.SeriesID AND T.CommodityID = O.CommodityID AND T.PricingFactorID = O.PricingFactorID ");
_SQL += string.Format(" WHEN MATCHED ");
_SQL += string.Format(" THEN UPDATE SET ");
_SQL += string.Format(" GeneralFactoryPrice = O.GeneralFactoryPrice,BranchFactoryPrice = O.BranchFactoryPrice ");
_SQL += string.Format(" WHEN NOT MATCHED ");
_SQL += string.Format(" THEN INSERT ");
_SQL += string.Format(" (BasicDataID,BrandID,SeriesID,CommodityID,PricingFactorID,GeneralFactoryPrice,BranchFactoryPrice,BeginDate,EndDate,IsEnable) ");
_SQL += string.Format(" VALUES(O.BasicDataID,O.BrandID,O.SeriesID, O.CommodityID, O.PricingFactorID,O.GeneralFactoryPrice,O.BranchFactoryPrice,O.BeginDate,O.EndDate,O.IsEnable);");
SQLHelper.ExecuteNonQuery(_ConnectionString, _SQL);
}
/// <summary>
/// 导入报表
/// </summary>
/// <param name="pFilePath"></param>
private void ImportExcel(string pFilePath)
{
string strError = string.Empty;
//导入
object missing = Type.Missing;
Excel.Application myApp = new Excel.Application();
myApp.DisplayAlerts = false;
Excel.Workbook workBook = myApp.Workbooks.Open(pFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Worksheet worksheet = workBook.Worksheets[1] as Excel.Worksheet;
myApp.Visible = false;
string FNumber = string.Empty;
//if(worksheet.Cells[1, 1].Text!="小类"|| worksheet.Cells[1, 2].Text!="品牌" || worksheet.Cells[1, 3].Text != "系列" || worksheet.Cells[1, 4].Text != "商品名" || worksheet.Cells[1, 5].Text != "定价要素" || worksheet.Cells[1, 6].Text != "生效日期" || worksheet.Cells[1, 7].Text != "失效日期" || worksheet.Cells[1, 8].Text != "是否禁用" || worksheet.Cells[1, 9].Text != "省代价" || worksheet.Cells[1, 10].Text != "市代价")
//{
// MessageBox.Show("请使用标准模板导入价格！并按以下顺序导入数据[小类-品牌-系列-商品名-定价要素-生效日期-失效日期-是否禁用-省代价-市代价]");
// goto Show;
//}
OrderImportInfo entry;
for (int i = 2; i <= worksheet.UsedRange.Rows.Count; i++)
{
entry = new OrderImportInfo();
try
{
entry.BasicDataID = worksheet.Cells[i, 1].Text;
entry.BrandID = worksheet.Cells[i, 2].Text;
entry.SeriesID = worksheet.Cells[i, 3].Text;
entry.CommodityID = worksheet.Cells[i, 4].Text;
entry.PricingFactorID = worksheet.Cells[i, 5].Text;
entry.TimeBegin = DateTime.Now;
entry.TimeEnd = DateTime.Parse("2039-01-01");
entry.IsEnable = true;
entry.GeneralFactoryPrice = float.Parse(worksheet.Cells[i, 9].Text);
try
{
entry.BranchFactoryPrice = float.Parse(worksheet.Cells[i, 10].Text);
}
catch
{
entry.BranchFactoryPrice = 0;
}
DALCreator.CommFunction.MegerPrice(entry);
}
catch (Exception ex)
{
worksheet.Cells[i, 12] = ex.Message;
strError += i.ToString() + ex.Message;
continue;
}
worksheet.Cells[i, 12] = "导入成功";
}
if (strError != "")
{
MessageBox.Show("有些信息导入出错，请查看最后一列的错误提示！");
}
else
{
MessageBox.Show("全部数据已经成功导入！");
}
Show:
myApp.Visible = true;
btnImport.Enabled = false;
//日志............
DALFactory.K3Cloud.DALCreator.CommFunction.DM_Log_Local("价格导入", "项目->网上订单系统->导入新价格", pFilePath);
}
