public void UploadFile()
{
//log.Info("Start upload file to ftp according to FtpControl table.");
IList<FtpControl> ftpControlList = this.genericMgr.FindAll<FtpControl>("from FtpControl fc where IOType=? ", BusinessConstants.IO_TYPE_OUT);
if (ftpControlList != null && ftpControlList.Count > 0)
{
foreach (FtpControl ftpControl in ftpControlList)
{
string ftpServer = string.Empty;
int ftpPort = 21;
string ftpTempFolder = string.Empty;
string ftpFolder = string.Empty;
string ftpUser = string.Empty;
string ftpPass = string.Empty;
string filePattern = string.Empty;
string localFolder = string.Empty;
string localTempFolder = string.Empty;
try
{
#region 获取参数
ftpServer = ftpControl.FtpServer;
ftpPort = ftpControl.FtpPort.HasValue ? ftpControl.FtpPort.Value : 21;
ftpTempFolder = ftpControl.FtpTempFolder;
ftpFolder = ftpControl.FtpFolder;
ftpUser = ftpControl.FtpUser;
ftpPass = ftpControl.FtpPassword;
filePattern = ftpControl.FilePattern;
localFolder = ftpControl.LocalFolder;
localTempFolder = ftpControl.LocalTempFolder;
#endregion
#region 初始化远程目录
FtpHelper ftp = new FtpHelper(ftpServer, ftpPort, ftpFolder, ftpUser, ftpPass);
//ftpTempFolder = ftpTempFolder.Replace("\\", "/");
//if (!ftpTempFolder.EndsWith("/"))
//{
// ftpTempFolder += "/";
//}
//try
//{
// //清空Temp目录
// foreach (string fileName in ftp.GetFileList(filePattern))
// {
// ftp.Delete(fileName);
// }
//}
//catch (Exception)
//{
//}
//if (!ftp.DirectoryExist(ftpTempFolder))
//{
// ftp.MakeDir(ftpTempFolder);
//}
ftpFolder = ftpFolder.Replace("\\", "/");
if (!ftpFolder.EndsWith("/"))
{
ftpFolder += "/";
}
//if (!ftp.DirectoryExist(ftpFolder))
//{
// ftp.MakeDir(ftpFolder);
//}
#endregion
#region 获取本地上传文件列表
string[] files = null;
if (filePattern != null)
{
files = Directory.GetFiles(localFolder, filePattern);
}
else
{
files = Directory.GetFiles(localFolder);
}
#endregion
#region 上传文件
if (files != null && files.Length > 0)
{
foreach (string fileFullPath in files)
{
string fomatedFileFullPath = fileFullPath.Replace("\\", "/");
string fileName = fomatedFileFullPath.Substring(fomatedFileFullPath.LastIndexOf("/") + 1);
try
{
ftp.Upload(fomatedFileFullPath); //上传
if (ftp.FileExist(fileName))//检查是否已经存在FTP
{
if (ftpControl.VaildFilePattern != "BACKUP")//上传成功 写入ctl文件 表示完成
{
string CTLName = fomatedFileFullPath.Substring(0, fomatedFileFullPath.LastIndexOf("/") + 1) + fileName.Replace(".DAT", ".CTL");
this.CreateCTL(CTLName);
ftp.Upload(CTLName);
File.Delete(CTLName);
}
//else
//{
// localTempFolder = localTempFolder.Replace("\\", "/");
// File.Copy(fomatedFileFullPath, localTempFolder+"/" + fileName); //备份目录
//}
//log.Info("Delete file: " + fomatedFileFullPath);
File.Delete(fomatedFileFullPath);
}
else
{
throw new Exception();
}
}
catch (Exception ex)
{
if (fileName.Substring(0, 5) == "ASNLE")
{
this.genericMgr.Update("update CreateIpDAT set IsCreateDat=?,FileName=? where FileName=?", new object[] { false, ex.Message.Length>50?ex.Message.Substring(0,49):ex.Message, fileName });
}
else if (fileName.Substring(0, 6) == "SEQ1LE")
{
this.genericMgr.Update("update CreateOrderDAT set IsCreateDat=?,FileName=? where FileName=?", new object[] { false, ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, fileName });
}
else if (fileName.Substring(0, 4) == "SHIP")
{
this.genericMgr.Update("update CreateProcurementOrderDAT set IsCreateDat=?,FileName=? where FileName=?", new object[] { false, ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, fileName });
}
else if (fileName.Substring(0, 5) == "LOGLE")
{
this.genericMgr.Update("update LesINLog set IsCreateDat=0 where HandTime>?", new object[] { System.DateTime.Now.AddHours(-1) });
}
else if (fileName.Substring(0, 4) == "CANC")
{
this.genericMgr.Update("update CancelReceiptMasterDAT set IsCreateDat =0, CreateDATDate=?,DATFileName=? where DATFileName=?", new object[] { System.DateTime.Now, ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, fileName });
}
else if (fileName.Substring(0, 4) == "HUID")
{
this.genericMgr.Update("update CreateBarCode set DATFileName=?,CreateDATDate=?,IsCreateDat=0 where DATFileName=?", new object[] { ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, System.DateTime.Now, fileName });
}
else if (fileName.Substring(0, 3) == "SEQ" && fileName.Substring(0, 6) != "SEQ1LE")
{
this.genericMgr.Update("update CreateSeqOrderDAT set IsCreateDat=?,FileName=? where FileName=? ", new object[] { false, ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, fileName });
}
else if (fileName.Substring(0, 4) == "ITEM")
{
this.genericMgr.Update("update ItemStandardPackDAT set IsCreateDat=0, DATFileName=?,CreateDATDate=? where DATFileName=?", new object[] { ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, System.DateTime.Now, fileName });
}
else if (fileName.Substring(0, 4) == "WARE")
{
}
else if (fileName.Substring(0, 4) == "FLOW")
{
}
else if (fileName.Substring(0, 4) == "RETU")
{
this.genericMgr.Update("update YieldReturn set IsCreateDat=0, DATFileName=?,CreateDATDate=? where DATFileName=?", new object[] { ex.Message.Length > 50 ? ex.Message.Substring(0, 49) : ex.Message, System.DateTime.Now, fileName });
}
log.Error("Upload file:" + fileFullPath, ex);
}
}
}
#endregion
}
catch (Exception ex)
{
log.Error("Upload files from ftpServer:" + ftpServer, ex);
}
}
}
else
{
log.Info("No record found in FtpControl table.");
}
//log.Info("End upload file to ftp according to FtpControl table.");
}
private void UploadFile()
{
log.Info("Start upload file to ftp according to FtpControl table.");
IList<DssFtpControl> dssFtpControlList = this.dssFtpControlMgr.GetDssFtpControl(BusinessConstants.IO_TYPE_OUT);
if (dssFtpControlList != null && dssFtpControlList.Count > 0)
{
foreach (DssFtpControl dssFtpControl in dssFtpControlList)
{
string ftpServer = string.Empty;
int ftpPort = 21;
string ftpTempFolder = string.Empty;
string ftpFolder = string.Empty;
string ftpUser = string.Empty;
string ftpPass = string.Empty;
string filePattern = string.Empty;
string localFolder = string.Empty;
try
{
#region 获取参数
ftpServer = dssFtpControl.FtpServer;
ftpPort = dssFtpControl.FtpPort.HasValue ? dssFtpControl.FtpPort.Value : 21;
ftpTempFolder = dssFtpControl.FtpTempFolder;
ftpFolder = dssFtpControl.FtpFolder;
ftpUser = dssFtpControl.FtpUser;
ftpPass = dssFtpControl.FtpPassword;
filePattern = dssFtpControl.FilePattern;
localFolder = dssFtpControl.LocalFolder;
#endregion
#region 初始化远程目录
FtpHelper ftp = new FtpHelper(ftpServer, ftpPort, ftpTempFolder, ftpUser, ftpPass);
//ftp.Login();
//ftp.ChangeDir(ftpTempFolder);
//ftp.BinaryMode = true;
ftpTempFolder = ftpTempFolder.Replace("\\", "/");
if (!ftpTempFolder.EndsWith("/"))
{
ftpTempFolder += "/";
}
try
{
//清空Temp目录
foreach (string fileName in ftp.GetFileList(filePattern))
{
ftp.Delete(fileName);
}
}
catch (Exception)
{
}
//if (!ftp.DirectoryExist(ftpTempFolder))
//{
// ftp.MakeDir(ftpTempFolder);
//}
ftpFolder = ftpFolder.Replace("\\", "/");
if (!ftpFolder.EndsWith("/"))
{
ftpFolder += "/";
}
//if (!ftp.DirectoryExist(ftpFolder))
//{
// ftp.MakeDir(ftpFolder);
//}
#endregion
#region 获取本地上传文件列表
string[] files = null;
if (filePattern != null)
{
files = Directory.GetFiles(localFolder, filePattern);
}
else
{
files = Directory.GetFiles(localFolder);
}
#endregion
#region 上传文件
if (files != null && files.Length > 0)
{
foreach (string fileFullPath in files)
{
try
{
string fomatedFileFullPath = fileFullPath.Replace("\\", "/");
string fileName = fomatedFileFullPath.Substring(fomatedFileFullPath.LastIndexOf("/") + 1);
log.Info("Upload file: " + fomatedFileFullPath);
ftp.Upload(fomatedFileFullPath);
}
catch (Exception ex)
{
log.Error("Upload file:" + fileFullPath, ex);
}
}
foreach (string fileFullPath in files)
{
try
{
string fomatedFileFullPath = fileFullPath.Replace("\\", "/");
string fileName = fomatedFileFullPath.Substring(fomatedFileFullPath.LastIndexOf("/") + 1);
log.Info("Move file: " + fomatedFileFullPath);
ftp.Rename(fileName, ftpFolder + fileName);
log.Info("Delete file: " + fomatedFileFullPath);
File.Delete(fomatedFileFullPath);
}
catch (Exception ex)
{
log.Error("Upload file:" + fileFullPath, ex);
}
}
}
#endregion
}
catch (Exception ex)
{
log.Error("Upload files from ftpServer:" + ftpServer, ex);
}
}
}
else
{
log.Info("No record found in FtpControl table.");
}
log.Info("End upload file to ftp according to FtpControl table.");
}
private void Process(string ftpServer, int ftpPort, string ftpUser, string ftpPass, string ftpFolder,
string localFolder, string localTempFolder, IList<ErrorMessage> errorMessageList)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(
" select r.Plant,l.SAPLocation,det.Item,");
sql.Append(
" CASE WHEN det.IsCS = 1 THEN 'K' ELSE '' END AS IsCS, ");
sql.Append("det.CSSupplier, ");
sql.Append("SUM(CASE WHEN det.QualityType = 0 THEN det.Qty ELSE 0 END) AS QualifyQty,");
sql.Append("SUM(CASE WHEN det.QualityType = 1 THEN det.Qty ELSE 0 END) AS InspectQty,");
sql.Append("SUM(CASE WHEN det.QualityType = 2 THEN det.Qty ELSE 0 END) AS RejectQty, ");
sql.Append("i.Uom ");
sql.Append(" from VIEW_LocationLotDet det,MD_Location l,MD_Region r,MD_Item i ");
sql.Append(" where det.Location = l.Code and l.Region = r.Code and det.Item=i.Code ");
sql.Append(" group by r.Plant,l.SAPLocation,det.Item,i.Uom,det.CSSupplier,det.IsCS ");
var locLotDetList = this.genericMgr.FindAllWithNativeSql<object[]>(sql.ToString());
if (locLotDetList == null || locLotDetList.Count == 0)
{
log.Warn("没有库存数据");
return;
}
string ymd = DateTime.Now.AddDays(-1).ToString("yyyyMMdd");
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(ymd);
sheet.ForceFormulaRecalculation = true;
ICellStyle headStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
font.FontHeightInPoints = (short)10;
headStyle.SetFont(font);
int rownum = 0;
int colnum = 0;
#region 列头
XlsHelper.SetRowCell(sheet, rownum, colnum++, "工厂代码", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "库存地点", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "物料代码", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "特殊标志", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "供应厂商", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "正常库存", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "质检库存", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum++, "冻结库存", headStyle);
XlsHelper.SetRowCell(sheet, rownum, colnum, "库存单位", headStyle);
rownum++;
#endregion
#region 列表
foreach (var locLotDet in locLotDetList)
{
//工厂代码 库存地点 物料代码 特殊标志 供应厂商 正常库存 冻结库存 质检库存 库存单位
try
{
colnum = 0;
if (locLotDet[0] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, locLotDet[0].ToString());
colnum++;
if (locLotDet[1] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, locLotDet[1].ToString());
colnum++;
if (locLotDet[2] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, locLotDet[2].ToString());
colnum++;
if (locLotDet[3] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, locLotDet[3].ToString());
colnum++;
if (locLotDet[4] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, locLotDet[4].ToString());
colnum++;
if (locLotDet[5] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, decimal.Parse(locLotDet[5].ToString()).ToString("0.########"));
colnum++;
if (locLotDet[6] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, decimal.Parse(locLotDet[6].ToString()).ToString("0.########"));
colnum++;
if (locLotDet[7] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, decimal.Parse(locLotDet[7].ToString()).ToString("0.########"));
colnum++;
if (locLotDet[8] != null)
XlsHelper.SetRowCell(sheet, rownum, colnum, locLotDet[8].ToString());
rownum++;
}
catch (Exception ex)
{
log.Error(NVelocityTemplateRepository.TemplateEnum.GenerateInvRepFail, ex);
string errorMessage = locLotDet[0].ToString() + " " + locLotDet[1].ToString() + locLotDet[2].ToString() + " " +
locLotDet[3].ToString() + " " + decimal.Parse(locLotDet[4].ToString()).ToString("0.########") + " " +
decimal.Parse(locLotDet[5].ToString()).ToString("0.########") + " " +
decimal.Parse(locLotDet[7].ToString()).ToString("0.########") + " " + locLotDet[7].ToString() +
locLotDet[8].ToString() + " 生成库存报表失败。";
errorMessageList.Add(new ErrorMessage
{
Template = NVelocityTemplateRepository.TemplateEnum.GenerateInvRepFail,
Message = errorMessage,
Exception = ex
});
}
}
#endregion
XlsHelper.WriteToFile(localFolder, "LESMB52" + ymd + ".xls", workbook);
if (!Directory.Exists(localTempFolder))
{
Directory.CreateDirectory(localTempFolder);
}
string localTempFolderFileName = localTempFolder + "/" + "LESMB52" + ymd + ".xls";
string localFolderFileName = localFolder + "/" + "LESMB52" + ymd + ".xls";
File.Copy(localFolderFileName, localTempFolderFileName, true); //备份目录
log.Info("文件生成成功，记录数：" + locLotDetList.Count());
#region 上传
try
{
FtpHelper ftp = new FtpHelper(ftpServer, ftpPort, ftpFolder, ftpUser, ftpPass);
ftp.Upload(localTempFolderFileName);
File.Delete(localTempFolderFileName);
log.Info(localTempFolderFileName + "上传成功");
}
catch (Exception ex)
{
log.Error("Upload file:" + localTempFolderFileName, ex);
log.Error(NVelocityTemplateRepository.TemplateEnum.GenerateInvRepFail, ex);
string errorMessage = "生成库存报表上传失败。";
errorMessageList.Add(new ErrorMessage
{
Template = NVelocityTemplateRepository.TemplateEnum.GenerateInvRepFail,
Message = errorMessage,
Exception = ex
});
}
#endregion
}
catch (Exception ex)
{
log.Error(NVelocityTemplateRepository.TemplateEnum.GenerateInvRepFail, ex);
string errorMessage = "生成库存报表失败。";
errorMessageList.Add(new ErrorMessage
{
Template = NVelocityTemplateRepository.TemplateEnum.GenerateInvRepFail,
Message = errorMessage,
Exception = ex
});
}
}