using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BroadText.Common;
using System.Reflection;
using Mail.Business;
using System.Xml;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Configuration;
namespace StaplesBiz
{
public class OutputExcel
{
private object missing = Missing.Value;
private Microsoft.Office.Interop.Excel.Application ExcelRS;
private Microsoft.Office.Interop.Excel.Workbook RSbook;
private Microsoft.Office.Interop.Excel.Worksheet RSsheet;
private String GetOutputPath()
{
String temp = ConfigurationManager.AppSettings.Get("ExcelOutputPath");
return temp;
}
public String OutputToExcel(String templetFile, String outputFile, System.Data.DataTable sourceTable, int sheetIndex)
{
templetFile = templetFile.Replace(@"\\", @"\");
outputFile = outputFile.Replace(@"\\", @"\");
templetFile = templetFile.Replace(@"\\", @"\");
outputFile = outputFile.Replace(@"\\", @"\");
//System.IO.File.Copy(templetFile, outputFile, true);
//if (this.ExcelRS != null)
// ExcelRS = null;
//把模版文件templetFile拷贝到目输出文件outputFile中,并且目标文件可以改写
if (!String.IsNullOrEmpty(templetFile) && templetFile.Substring(0, 1) == @"\" && templetFile.Substring(1, 1) != @"\")
{
templetFile = @"\" + templetFile;
}
if (!String.IsNullOrEmpty(outputFile) && outputFile.Substring(0, 1) == @"\" && outputFile.Substring(1, 1) != @"\")
{
outputFile = @"\" + outputFile;
}
if (!String.IsNullOrEmpty(templetFile))
{
System.IO.File.Copy(templetFile, outputFile, true);
}
if (this.ExcelRS != null)
ExcelRS = null;
//实例化ExcelRS对象
ExcelRS = new Microsoft.Office.Interop.Excel.ApplicationClass();
//打开目标文件outputFile
RSbook = ExcelRS.Workbooks.Open(outputFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//设置第一个工作溥
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(sheetIndex);
//激活当前工作溥
RSsheet.Activate();
//建立表头
int x = 1;
int y = 1;
foreach (DataColumn dc in sourceTable.Columns)
{
RSsheet.Cells[1, y] = dc.ColumnName;
//x++;
y++;
}
//设置列类型
if (sourceTable.Rows.Count > 0)
{
y = 1;
foreach (DataColumn dc in sourceTable.Columns)
{
Range rangeColumn = RSsheet.get_Range(RSsheet.Cells[2, y], RSsheet.Cells[sourceTable.Rows.Count + 1, y]);
switch (dc.DataType.ToString())
{
case "System.DateTime":
rangeColumn.NumberFormat = "yyyy-MM-dd HH:mm:ss";
break;
default:
break;
}
//rangeColumn.NumberFormat = "@";
y++;
}
}
//填写数据
x = 2;
y = 1;
object[,] objData = new object[sourceTable.Rows.Count, sourceTable.Columns.Count];
for (int r = 0; r < sourceTable.Rows.Count; r++)
{
for (int c = 0; c < sourceTable.Columns.Count; c++)
{
objData[r, c] = sourceTable.Rows[r][c];
}
//Application.DoEvents();
}
// 写入Excel
Microsoft.Office.Interop.Excel.Range range;
range = RSsheet.get_Range(RSsheet.Cells[2, 1], RSsheet.Cells[sourceTable.Rows.Count + 1, sourceTable.Columns.Count]);
//range.NumberFormat = "@";//设置单元格为文本格式
//range.set_Value(objData);
range.Value2 = objData;
//RSsheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
//foreach (DataRow dr in sourceTable.Rows)
//{
// foreach (object obj in dr.ItemArray)
// {
// RSsheet.Cells[x, y] = obj.ToString();
// y++;
// }
// y = 1;
// x++;
// if (x % 100 == 0)
// {
// Log.SaveLog(sourceTable.Rows.Count.ToString() + " : " + x.ToString() + " = " + ((1.0 * x) / (sourceTable.Rows.Count / 100.0)).ToString());
// }
//}
//保存目标文件
RSbook.Save();
//ExcelRS.ActiveWorkbook.SaveAs(outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
RSbook.Close();
//设置DisplayAlerts
//ExcelRS.DisplayAlerts = false;
//ExcelRS.Visible = true;
//ExcelRS.DisplayAlerts = true;
//释放对象
RSsheet = null;
RSbook = null;
ExcelRS = null;
//释放内存
GcCollect();
return outputFile;
}
public String OutputToExcel2(String templetFile, String outputFile, System.Data.DataTable sourceTable, int sheetIndex)
{
templetFile = templetFile.Replace("\\\\", "\\");
outputFile = outputFile.Replace("\\\\", "\\");
//System.IO.File.Copy(templetFile, outputFile, true);
//if (this.ExcelRS != null)
// ExcelRS = null;
//把模版文件templetFile拷贝到目输出文件outputFile中,并且目标文件可以改写
if (!String.IsNullOrEmpty(templetFile) && templetFile.Substring(0, 1) == @"\" && templetFile.Substring(1, 1) != @"\")
{
templetFile = @"\" + templetFile;
}
if (!String.IsNullOrEmpty(outputFile) && outputFile.Substring(0, 1) == @"\" && outputFile.Substring(1, 1) != @"\")
{
outputFile = @"\" + outputFile;
}
if (!String.IsNullOrEmpty(templetFile))
{
System.IO.File.Copy(templetFile, outputFile, true);
}
if (this.ExcelRS != null)
ExcelRS = null;
//实例化ExcelRS对象
ExcelRS = new Microsoft.Office.Interop.Excel.ApplicationClass();
//打开目标文件outputFile
RSbook = ExcelRS.Workbooks.Open(outputFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//设置第一个工作溥
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(sheetIndex);
//激活当前工作溥
RSsheet.Activate();
//建立表头
int x = 1;
int y = 1;
foreach (DataColumn dc in sourceTable.Columns)
{
RSsheet.Cells[1, y] = dc.ColumnName;
//x++;
y++;
}
//设置列类型
if (sourceTable.Rows.Count > 0)
{
y = 1;
foreach (DataColumn dc in sourceTable.Columns)
{
Range rangeColumn = RSsheet.get_Range(RSsheet.Cells[2, y], RSsheet.Cells[sourceTable.Rows.Count + 1, y]);
switch (dc.DataType.ToString())
{
case "System.DateTime":
rangeColumn.NumberFormat = "yyyy-MM-dd HH:mm:ss";
break;
default:
break;
}
//rangeColumn.NumberFormat = "@";
y++;
}
}
//填写数据
x = 2;
y = 1;
//object[,] objData = new object[sourceTable.Rows.Count, sourceTable.Columns.Count];
//for (int r = 0; r < sourceTable.Rows.Count; r++)
//{
// for (int c = 0; c < sourceTable.Columns.Count; c++)
// {
// objData[r, c] = sourceTable.Rows[r][c];
// }
// //Application.DoEvents();
//}
写入Excel
//Microsoft.Office.Interop.Excel.Range range;
//range = RSsheet.get_Range(RSsheet.Cells[2, 1], RSsheet.Cells[sourceTable.Rows.Count + 1, sourceTable.Columns.Count]);
range.NumberFormat = "@";//设置单元格为文本格式
range.set_Value(objData);
//range.Value2 = objData;
//RSsheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
foreach (DataRow dr in sourceTable.Rows)
{
foreach (object obj in dr.ItemArray)
{
RSsheet.Cells[x, y] = obj.ToString();
y++;
}
y = 1;
x++;
if (x % 100 == 0)
{
Log.SaveLog(sourceTable.Rows.Count.ToString() + " : " + x.ToString() + " = " + ((1.0 * x) / (sourceTable.Rows.Count / 100.0)).ToString());
}
}
//保存目标文件
RSbook.Save();
//ExcelRS.ActiveWorkbook.SaveAs(outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
RSbook.Close();
//设置DisplayAlerts
//ExcelRS.DisplayAlerts = false;
//ExcelRS.Visible = true;
//ExcelRS.DisplayAlerts = true;
//释放对象
RSsheet = null;
RSbook = null;
ExcelRS = null;
//释放内存
GcCollect();
return outputFile;
}
public String Output945()
{
String sql = "select * from TblShipmentDetailResult";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
try
{
OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\01_daily 945ship_working sheet_.xlsx",
GetOutputPath() + @"\AutoReport\945EDI\Output\945EDI_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output945ShipWorkingSheet()
{
//Shipment + Shipment Detail
String sql = "Do945New_Shipment";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
//SqlCommandBuilder cb = new SqlCommandBuilder(da);
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
//Data (945EDI)
sql = @"SELECT STUFF_DATE, ORIGIN, SEAL1, SEAL3, CS_NUM, CONTAINER_NUM, LOT, PO_CREATION_DATE, QTY_LOADED, ITEM, NET_WEIGHT, VOLUME, EDC_PRICE,
ITEM_PRICE, CARRIER, PO, SKU, L1, L2, L3, L4, PRICE, P1, O3, WEIGHT
FROM TempEDI945";
da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet dsData = new DataSet();
da.Fill(dsData);
string file1 = "";
string file2 = "";
try
{
Log.SaveLog("daily 945, shpment =" + ds.Tables[0].Rows.Count.ToString());
file1 = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\01_daily 945ship_working sheet_.xlsx",
GetOutputPath() + @"\AutoReport\945EDI\Output\01_daily 945ship_working sheet_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
Log.SaveLog("daily 945, shpment detail =" + ds.Tables[1].Rows.Count.ToString());
OutputToExcel("",
file1,
ds.Tables[1],
2);
Log.SaveLog("daily 945, data =" + dsData.Tables[0].Rows.Count.ToString());
OutputToExcel("",
file1,
dsData.Tables[0],
3);
Log.SaveLog("missing 945, " + ds.Tables[2].Rows.Count.ToString());
file2 = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\01_daily missing945_.xlsx",
GetOutputPath() + @"\AutoReport\945EDI\Output\01_daily missing945_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[2],
1);
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
#region Send Mail
StringBuilder sb = new StringBuilder();
sb.AppendLine(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "<br/>");
//sb.AppendLine(file1 + "<br/>");
sb.AppendLine(file2 + "<br/>");
sb.AppendLine("================================" + "<br/>");
//sb.AppendLine("01_Not deduct ==> " + ds.Tables[1].Rows.Count.ToString() + "");
//sb.AppendLine("02_Imbalance ==> " + ds.Tables[0].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("03_Negative ==> " + ds.Tables[2].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("04_Unavailable ==> " + ds.Tables[3].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("05_ ==> " + ds.Tables[4].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("06_ ==> " + ds.Tables[5].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("07_ ==> " + ds.Tables[6].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("08_ ==> " + ds.Tables[7].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("09_ ==> " + ds.Tables[8].Rows.Count.ToString() + "<br/>");
//sb.AppendLine("10_ ==> " + ds.Tables[9].Rows.Count.ToString() + "<br/>");
MailSender mailSender = new MailSender();
String[] toList = null;
String[] ccList = null;
//System.Collections.Generic.List<String> toList = new System.Collections.Generic.List<String>();
//System.Collections.Generic.List<String> ccList = new System.Collections.Generic.List<String>();
System.Collections.Generic.List<String> attachments = new System.Collections.Generic.List<String>();
System.Collections.Generic.List<String> attachmentName = new System.Collections.Generic.List<String>();
System.Collections.Generic.List<byte[]> fileBytes = new System.Collections.Generic.List<byte[]>();
String subject = null;
String body = null;
string result = "";
string smtpserver, username, password, from, to, cc;
smtpserver = username = password = from = to = cc = "";
Int32 smtpport = 0;
XmlDocument doc = new XmlDocument();
doc.Load(FileManager.ApplicationPath + "MailConfig.xml");
foreach (XmlNode node in doc.SelectNodes("//Config"))
{
if (node.Attributes["name"].Value == "Daily Missing 945 Monitor")
{
smtpserver = node.SelectSingleNode("SMTP").InnerText.Trim();
smtpport = Convert.ToInt32(node.SelectSingleNode("PORT").InnerText.Trim());
username = node.SelectSingleNode("UserName").InnerText.Trim();
password = node.SelectSingleNode("Password").InnerText.Trim();
from = node.SelectSingleNode("From").InnerText.Trim();
to = node.SelectSingleNode("To").InnerText.Trim();
cc = node.SelectSingleNode("CC").InnerText.Trim();
subject = node.SelectSingleNode("Subject").InnerText.Trim();
break;
}
}
toList = mailSender.validateMailAddress(to);
ccList = mailSender.validateMailAddress(cc);
subject = subject + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
body = sb.ToString();
//attachmentName.Add(file1.Substring(file1.LastIndexOf("\\" + 1)));
//StreamReader sr = new StreamReader(file1);
//using (System.IO.MemoryStream memory = new MemoryStream())
//{
// System.IO.Stream stream = sr.BaseStream;
// byte[] bytes = new byte[1024];
// while (true)
// {
// int read = stream.Read(bytes, 0, bytes.Length);
// memory.Write(bytes, 0, read);
// if (read <= 0)
// break;
// }
// memory.Seek(0, SeekOrigin.Begin);
// fileBytes.Add(memory.ToArray());
//}
attachmentName.Add(file2.Substring(file2.LastIndexOf("\\") + 1));
StreamReader sr = new StreamReader(file2);
using (System.IO.MemoryStream memory = new MemoryStream())
{
System.IO.Stream stream = sr.BaseStream;
byte[] bytes = new byte[1024];
while (true)
{
int read = stream.Read(bytes, 0, bytes.Length);
memory.Write(bytes, 0, read);
if (read <= 0)
break;
}
memory.Seek(0, SeekOrigin.Begin);
fileBytes.Add(memory.ToArray());
}
try
{
mailSender.SendMail(smtpserver
, smtpport
, username
, password
, from
, toList
, ccList
, subject
, body
, (String[])attachmentName.ToArray()
, (byte[][])fileBytes.ToArray());
Log.SaveLog("send complete");
}
catch (Exception ex1)
{
Log.SaveException(ex1);
}
ds.Dispose();
da.Dispose();
conn.Close();
#endregion
return "OK";
}
public String OutputBOHNew()
{
String sql = "DoBOHNew";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
String file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\90_Weekly_BOH_Report_.xlsx",
GetOutputPath() + @"\AutoReport\StaplesBOH\Output\90_Weekly_BOH_Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "Weekly BOH Report");
}
else
{
SendAutoMail("", null, "Weekly BOH Report");
}
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String OutputDailyInventoryIntegrityCheck()
{
String sql = "sp_DailyBOH";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Hashtable files = new Hashtable();
string file = "";
try
{
//BOH
//02_Imbalance
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_01_Not deduct_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
Log.SaveLog("Daily BOH 2");
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "DailyInventoryIntegrityCheck");
}
else
{
SendAutoMail("", null, "DailyInventoryIntegrityCheck");
}
//Inventory not deduct
//01_Not deduct
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_02_Imbalance_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[1],
1);
Log.SaveLog("Daily BOH 1");
if (ds.Tables[1].Rows.Count > 0)
{
files.Add(file, file);
}
//Negative
//03_Negative
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_03_Negative_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[2],
1);
Log.SaveLog("Daily BOH 3");
if (ds.Tables[2].Rows.Count > 0)
{
files.Add(file, file);
}
//Unavailable_qty
//04_Unavailable Qty
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_04_Unavailable Qty_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[3],
1);
Log.SaveLog("Daily BOH 4");
if (ds.Tables[3].Rows.Count > 0)
{
files.Add(file, file);
}
//0408 01
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_05_Receipt&Receipt Detail_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[4],
1);
Log.SaveLog("Daily BOH 5");
if (ds.Tables[4].Rows.Count > 0)
{
files.Add(file, file);
}
//0408 02
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_06_Qty_Picked&Allocatged_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[5],
1);
Log.SaveLog("Daily BOH 6");
if (ds.Tables[5].Rows.Count > 0)
{
files.Add(file, file);
}
//0408 03
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_07_PKD_Planned&Picked&Loaded_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[6],
1);
Log.SaveLog("Daily BOH 7");
if (ds.Tables[6].Rows.Count > 0)
{
files.Add(file, file);
}
//0408 04
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_08_NoCreate945Ship_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[7],
1);
Log.SaveLog("Daily BOH 8");
if (ds.Tables[7].Rows.Count > 0)
{
files.Add(file, file);
}
//0408 05
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_09_NoCreate945Pick_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[8],
1);
Log.SaveLog("Daily BOH 9");
if (ds.Tables[8].Rows.Count > 0)
{
files.Add(file, file);
}
//0408 06
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_10_NoCreate944GRN_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[9],
1);
Log.SaveLog("Daily BOH 10");
if (ds.Tables[9].Rows.Count > 0)
{
files.Add(file, file);
}
//0410 01
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_11_Duplicated Items_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[10],
1);
Log.SaveLog("Daily BOH 11");
if (ds.Tables[10].Rows.Count > 0)
{
files.Add(file, file);
}
//0410 02
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\DailyInventoryIntegrityCheck\Output\03_daily inventory integrity check_12_OrderUoMNoEA_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[11],
1);
Log.SaveLog("Daily BOH 12");
if (ds.Tables[11].Rows.Count > 0)
{
files.Add(file, file);
}
#region Send Mail
int rows = 0;
foreach (System.Data.DataTable dt in ds.Tables)
{
rows += dt.Rows.Count;
}
if (rows > 0)
{
//SendAutoMail("", file, "DailyInventoryIntegrityCheck");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public static void SendAutoMail(String content, string file, string configCode)
{
String fileName = "";
if (!String.IsNullOrEmpty(file))
{
fileName = file.Substring(file.LastIndexOf("\\") + 1);
}
else
{
fileName = "No records.";
}
StringBuilder sb = new StringBuilder();
sb.AppendLine(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "<br/>");
sb.AppendLine(fileName + "<br/>");
sb.AppendLine("================================" + "<br/>");
sb.AppendLine(content);
MailSender mailSender = new MailSender();
String[] toList = null;
String[] ccList = null;
//System.Collections.Generic.List<String> toList = new System.Collections.Generic.List<String>();
//System.Collections.Generic.List<String> ccList = new System.Collections.Generic.List<String>();
System.Collections.Generic.List<String> attachments = new System.Collections.Generic.List<String>();
System.Collections.Generic.List<String> attachmentName = new System.Collections.Generic.List<String>();
System.Collections.Generic.List<byte[]> fileBytes = new System.Collections.Generic.List<byte[]>();
String subject = null;
String body = null;
string result = "";
string smtpserver, username, password, from, to, cc;
smtpserver = username = password = from = to = cc = "";
Int32 smtpport = 0;
XmlDocument doc = new XmlDocument();
doc.Load(FileManager.ApplicationPath + "MailConfig.xml");
foreach (XmlNode node in doc.SelectNodes("//Config"))
{
if (node.Attributes["name"].Value == configCode)
{
smtpserver = node.SelectSingleNode("SMTP").InnerText.Trim();
smtpport = Convert.ToInt32(node.SelectSingleNode("PORT").InnerText.Trim());
username = node.SelectSingleNode("UserName").InnerText.Trim();
password = node.SelectSingleNode("Password").InnerText.Trim();
from = node.SelectSingleNode("From").InnerText.Trim();
to = node.SelectSingleNode("To").InnerText.Trim();
cc = node.SelectSingleNode("CC").InnerText.Trim();
subject = node.SelectSingleNode("Subject").InnerText.Trim();
break;
}
}
toList = mailSender.validateMailAddress(to);
ccList = mailSender.validateMailAddress(cc);
subject = subject + " " + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
body = sb.ToString();
StreamReader sr;
if (!String.IsNullOrEmpty(file))
{
attachmentName.Add(fileName);
sr = new StreamReader(file);
using (System.IO.MemoryStream memory = new MemoryStream())
{
System.IO.Stream stream = sr.BaseStream;
byte[] bytes = new byte[1024];
while (true)
{
int read = stream.Read(bytes, 0, bytes.Length);
memory.Write(bytes, 0, read);
if (read <= 0)
break;
}
memory.Seek(0, SeekOrigin.Begin);
fileBytes.Add(memory.ToArray());
}
}
try
{
mailSender.SendMail(smtpserver
, smtpport
, username
, password
, from
, toList
, ccList
, subject
, body
, (String[])attachmentName.ToArray()
, (byte[][])fileBytes.ToArray());
Log.SaveLog("send complete");
}
catch (Exception ex1)
{
Log.SaveException(ex1);
}
}
public String OutputDailyMissing944NotReconcile()
{
String sql = @"select distinct [type] as 'Type'
from t_stored_item
where (wh_id = 'NAS06' or wh_id = 'NAS05')
AND ([type] like 'YAT%' or [type] like 'SGH%')";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
//01_Not Reconcile
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\02_daily missing944_Not Reconcile_.xlsx",
GetOutputPath() + @"\AutoReport\DailyMissing944NotReconcile\Output\02_daily missing944_Not Reconcile_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "Daily Missing 944 Not Reconcile Monitor");
}
else
{
SendAutoMail("", null, "Daily Missing 944 Not Reconcile Monitor");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String OutputDailyMissing944NoACK()
{
String sql = @"sp_Reconcil_noISA";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
//SqlCommandBuilder cb = new SqlCommandBuilder(da);
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
//01_No ISA
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\02_daily missing944_No ACK_.xlsx",
GetOutputPath() + @"\AutoReport\DailyMissing944NoACK\Output\02_daily missing944_No ACK_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "Daily Missing 944 No ACK Monitor");
}
else
{
SendAutoMail("", null, "Daily Missing 944 No ACK Monitor");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_t_msg_bus_status_neq_2()
{
String sql = @"SELECT * FROM t_msg_bus where status_id <> 2";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdADV"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\t_msg_bus_status_neq_2_.xlsx",
GetOutputPath() + @"\AutoReport\t_msg_bus_status_neq_2_\Output\t_msg_bus_status_neq_2_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "t_msg_bus_status_neq_2");
}
else
{
SendAutoMail("", null, "t_msg_bus_status_neq_2");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Test()
{
String sql = @"select * from [TempShipment]";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnection"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
try
{
//01_Not Reconcile
string file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\t_msg_bus_status_neq_2_.xlsx",
GetOutputPath() + @"\AutoReport\t_msg_bus_status_neq_2_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
/// <summary>
///
/// </summary>
/// <param name="week">week 为当前周，请参见台历</param>
/// <returns></returns>
public String OutputWeelkyBOH(int week)
{
if (week == null || week <= 1)
{
return "week error,week is " + week.ToString();
}
string file = "";
#region 01_BOH_Old
String sql = @"SELECT [Location]
,[OrderNo]
,[Item]
,[Link]
,[EDCQty]
,[Qty]
,[Variance]
,[Category]
,[Status]
,[ISA_TK_Load_ID]
,[DamcoNextStep]
,'Old:' + [Category]
FROM TblUpdatedWeeklyBOHNew
WHERE week = " + (week-1).ToString();
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
try
{
//01_Not Reconcile
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\01_BOH_Template_.xlsx",
GetOutputPath() + @"\AutoReport\WeeklyBOH\Output\01_BOH_Template_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
5);
}
catch (Exception ex)
{
Log.SaveException(ex);
}
#endregion
#region 03_944WAa
sql = @"select distinct [type] as 'Type'
from t_stored_item
where (wh_id = 'NAS06' or wh_id = 'NAS05')
AND ([type] like 'YAT%' or [type] like 'SGH%')";
conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
da = new SqlDataAdapter(sql, conn);
cb = new SqlCommandBuilder(da);
ds = new DataSet();
da.Fill(ds);
try
{
//01_Not Reconcile
OutputToExcel("",
file,
ds.Tables[0],
7);
}
catch (Exception ex)
{
Log.SaveException(ex);
}
#endregion
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String OutputWeekEDCOutResultForExcel()
{
String sql = @"sp_WeekEDCOutResult_For_Excel";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
//Matched
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\04_Weekly EDC Out.xlsx",
GetOutputPath() + @"\AutoReport\WeeklyEDCOut\Output\04_Weekly EDC Out_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
//Mismatched
OutputToExcel(
"",
file,
ds.Tables[1],
2);
//Only in WeekEDCOut
OutputToExcel(
"",
file,
ds.Tables[2],
3);
//Only in TempOrderDetail2
OutputToExcel(
"",
file,
ds.Tables[3],
4);
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public void GcCollect()
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
#region Output 01-27
public String Output_01_Inventory_not_deducted()
{
String sql = @"sp_01_Inventory_not_deducted";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\01_Inventory not deducted\Output\01_Inventory not deducted_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "01_Inventory not deducted");
}
else
{
SendAutoMail("", null, "01_Inventory not deducted");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_02_Inventory_Imbalance_check()
{
String sql = @"sp_02_Inventory_Imbalance_check";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\02_Inventory Imbalance check\Output\02_Inventory Imbalance check_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "02_Inventory Imbalance check");
}
else
{
SendAutoMail("", null, "02_Inventory Imbalance check");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_03_Negative_inventory()
{
String sql = @"sp_03_Negative_inventory";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\03_Negative inventory\Output\03_Negative inventory_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "03_Negative inventory");
}
else
{
SendAutoMail("", null, "03_Negative inventory");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_04_Unavailable_qty_integration()
{
String sql = @"sp_04_Unavailable_qty_integration";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\04_Unavailable_qty integration\Output\04_Unavailable_qty integration_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "04_Unavailable_qty integration");
}
else
{
SendAutoMail("", null, "04_Unavailable_qty integration");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_05_SO_Not_Reconciled()
{
String sql = @"sp_05_SO_Not_Reconciled";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\05_SO Not Reconciled\Output\05_SO Not Reconciled_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "05_SO Not Reconciled");
}
else
{
SendAutoMail("", null, "05_SO Not Reconciled");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_06_944_GRN_no_ISA_number_acknowledged()
{
String sql = @"sp_06_944_GRN_no_ISA_number_acknowledged";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\06_944 GRN no ISA number acknowledged\Output\06_944 GRN no ISA number acknowledged_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "06_944 GRN no ISA number acknowledged");
}
else
{
SendAutoMail("", null, "06_944 GRN no ISA number acknowledged");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_08_944_GRN_not_generated()
{
String sql = @"sp_08_944_GRN_not_generated";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\08_944 GRN not generated\Output\08_944 GRN not generated_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "08_944 GRN not generated");
}
else
{
SendAutoMail("", null, "08_944 GRN not generated");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_07_945_Ship_no_ISA_number_acknowledged()
{
String sql = @"sp_07_945_Ship_no_ISA_number_acknowledged";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\07_945 Ship no ISA number acknowledged\Output\07_945 Ship no ISA number acknowledged_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "07_945 Ship no ISA number acknowledged");
}
else
{
SendAutoMail("", null, "07_945 Ship no ISA number acknowledged");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_09_945_Pick_not_generated()
{
String sql = @"sp_09_945_Pick_not_generated";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\09_945 Pick not generated\Output\09_945 Pick not generated_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "09_945 Pick not generated");
}
else
{
SendAutoMail("", null, "09_945 Pick not generated");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_10_945_Ship_not_generated()
{
String sql = @"sp_10_945_Ship_not_generated";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\10_945 Ship not generated\Output\10_945 Ship not generated_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "10_945 Ship not generated");
}
else
{
SendAutoMail("", null, "10_945 Ship not generated");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_11_Split_Carton_Issue___Order_QTY_not_correct()
{
String sql = @"sp_11_Split_Carton_Issue___Order_QTY_not_correct";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\11_Split Carton Issue - Order QTY not correct\Output\11_Split Carton Issue - Order QTY not correct_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "11_Split Carton Issue - Order QTY not correct");
}
else
{
SendAutoMail("", null, "11_Split Carton Issue - Order QTY not correct");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_12_Split_Carton_Issue___Impacted_CLP_allocated()
{
String sql = @"sp_12_Split_Carton_Issue___Impacted_CLP_allocated";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\12_Split Carton Issue - Impacted CLP allocated\Output\12_Split Carton Issue - Impacted CLP allocated_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "12_Split Carton Issue - Impacted CLP allocated");
}
else
{
SendAutoMail("", null, "12_Split Carton Issue - Impacted CLP allocated");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_13_14_Receipt_Detail_Issue()
{
String sql = @"sp_13_14_Receipt_Detail_Issue";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\13_14_Receipt Detail Issue\Output\13_14_Receipt Detail Issue_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "13_14_Receipt Detail Issue");
}
else
{
SendAutoMail("", null, "13_14_Receipt Detail Issue");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_15_calculation_uom_not_CTN()
{
String sql = @"sp_15_calculation_uom_not_CTN";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\15_calculation uom not CTN\Output\15_calculation uom not CTN_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "15_calculation uom not CTN");
}
else
{
SendAutoMail("", null, "15_calculation uom not CTN");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_16_order_uom_not_EA()
{
String sql = @"sp_16_order_uom_not_EA";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\16 order uom not EA\Output\16 order uom not EA_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "16 order uom not EA");
}
else
{
SendAutoMail("", null, "16 order uom not EA");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_17_945_Pick_CBM_QTY_0()
{
DateTime now = System.DateTime.Now;
String sql = "sp_17_945_Pick_CBM_QTY_0";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel2(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\17_945_Pick_CBM_QTY_Zero\Output\17_945_Pick_CBM_QTY_Zero_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "17_945 Pick CBM/QTY = 0");
}
else
{
SendAutoMail("", null, "17_945 Pick CBM/QTY = 0");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_18_Pick_Detail_Missing_but_Allocation_Table_exisiting()
{
String sql = @"sp_18_Pick_Detail_Missing_but_Allocation_Table_exisiting";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\18_Pick Detail Missing but Allocation Table exisiting\Output\18_Pick Detail Missing but Allocation Table exisiting_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
#region Send Mail
if ((ds.Tables[0].Rows.Count + ds.Tables[1].Rows.Count) > 0)
{
SendAutoMail("", file, "18_Pick Detail Missing but Allocation Table exisiting");
}
else
{
SendAutoMail("", null, "18_Pick Detail Missing but Allocation Table exisiting");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_19_Over_Pick_Load_Ship()
{
String sql = @"sp_19_Over_Pick_Load_Ship";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\19_Over Pick Load Ship\Output\19_Over Pick Load Ship_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "19_Over Pick/Load/Ship");
}
else
{
SendAutoMail("", null, "19_Over Pick/Load/Ship");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_20_Order_Item_Missing()
{
DateTime now = System.DateTime.Now;
String sql = "sp_20_Order_Item_Missing";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\20_Order Item Missing\Output\20_Order Item Missing_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
#region Send Mail
if (ds.Tables[0].Rows.Count + ds.Tables[1].Rows.Count > 0)
{
SendAutoMail("", file, "20_Order Item Missing");
}
else
{
SendAutoMail("", null, "20_Order Item Missing");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_21_Location_of_pallet_in_Storage_and_LP_table_not_aligned()
{
String sql = @"sp_21_Location_of_pallet_in_Storage_and_LP_table_not_aligned";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\21_Location of pallet in Storage and LP table not aligned\Output\21_Location of pallet in Storage and LP table not aligned_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "21_Location of pallet in Storage and LP table not aligned");
}
else
{
SendAutoMail("", null, "21_Location of pallet in Storage and LP table not aligned");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_22_Status_of_pallet_in_Storage_and_LP_table_not_aligned()
{
String sql = @"sp_22_Status_of_pallet_in_Storage_and_LP_table_not_aligned";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\22_Status of pallet in Storage and LP table not aligned\Output\22_Status of pallet in Storage and LP table not aligned_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "22_Status of pallet in Storage and LP table not aligned");
}
else
{
SendAutoMail("", null, "22_Status of pallet in Storage and LP table not aligned");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_24_Duplicated_Order_Item()
{
String sql = @"sp_24_Duplicated_Order_Item";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\24_Duplicated Order Item\Output\24_Duplicated Order Item_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "24_Duplicated Order/Item");
}
else
{
SendAutoMail("", null, "24_Duplicated Order/Item");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_25_Messeger_Bus_Stability_Checking()
{
String sql = @"sp_25_Messeger_Bus_Stability_Checking";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\25_Messeger Bus Stability Checking\Output\25_Messeger Bus Stability Checking_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "25_Messeger Bus Stability Checking");
}
else
{
SendAutoMail("", null, "25_Messeger Bus Stability Checking");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_26_Messeger_Manager_Stability_Monitor()
{
String sql = @"sp_26_Messeger_Manager_Stability_Monitor";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\26_Messeger Manager Stability Monitor\Output\26_Messeger Manager Stability Monitor_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "26_Messeger Manager Stability Monitor");
}
else
{
SendAutoMail("", null, "26_Messeger Manager Stability Monitor");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_27_Duplicate_Line_Number_In_ASN_Check()
{
String sql = @"sp_27_duplicate_line_number_in_ASN_check";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\27_duplicate line number in ASN check\Output\27_duplicate line number in ASN check_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "27_duplicate line number in ASN check");
}
else
{
SendAutoMail("", null, "27_duplicate line number in ASN check");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_28_Daily_Integrity_Check()
{
String sql = @"sp_28_daily_integrity_check";
DateTime now = System.DateTime.Now;
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\empty.xlsx",
GetOutputPath() + @"\AutoReport\28_Daily Integrity Check\Output\28_Daily_Integrity_Check_" + now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "28_Daily Integrity Check");
}
else
{
SendAutoMail("", null, "28_Daily Integrity Check");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
#endregion
#region Output ja_01-08
public String Output_ja_01_YAT_Inventory_Report()
{
String sql = @"sp_ja_01_YAT_Inventory_Report";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\YAT Inventory Report Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_01_YAT_Inventory_Report\Output\YAT Inventory Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_01_YAT_Inventory_Report");
}
else
{
SendAutoMail("", null, "ja_01_YAT_Inventory_Report");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_02_SGH_Inventory_Report()
{
String sql = @"sp_ja_02_SGH_Inventory_Report";
SqlConnection conn = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\SGH Inventory Report Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_02_SGH_Inventory_Report\Output\SGH Inventory Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_02_SGH_Inventory_Report");
}
else
{
SendAutoMail("", null, "ja_02_SGH_Inventory_Report");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_03_YAT_Open_Order_List()
{
String sql = @"sp_ja_03_YAT_Open_Order_List";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\YAT Open Order List Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_03_YAT_Open_Order_List\Output\YAT Open Order List_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_03_YAT_Open_Order_List");
}
else
{
SendAutoMail("", null, "ja_03_YAT_Open_Order_List");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_04_SGH_Open_Order_List()
{
String sql = @"sp_ja_04_SGH_Open_Order_List";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\SGH Open Order List Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_04_SGH_Open_Order_List\Output\SGH Open Order List_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_04_SGH_Open_Order_List");
}
else
{
SendAutoMail("", null, "ja_04_SGH_Open_Order_List");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_05_YAT_EDCVSHJ_Discrepancy_Report()
{
String sql = @"sp_ja_05_YAT_EDCVSHJ_Discrepancy_Report";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\YAT EDCVSHJ Discrepancy Report Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_05_YAT_EDCVSHJ_Discrepancy_Report\Output\YAT EDCVSHJ Discrepancy Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_05_YAT_EDCVSHJ_Discrepancy_Report");
}
else
{
SendAutoMail("", null, "ja_05_YAT_EDCVSHJ_Discrepancy_Report");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_06_YAT_HJVSEDC_Discrepancy_Report()
{
String sql = @"sp_ja_06_YAT_HJVSEDC_Discrepancy_Report";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\YAT HJVSEDC Discrepancy Report Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_06_YAT_HJVSEDC_Discrepancy_Report\Output\YAT HJVSEDC Discrepancy Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
OutputToExcel("",
file,
ds.Tables[2],
3);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_06_YAT_HJVSEDC_Discrepancy_Report");
}
else
{
SendAutoMail("", null, "ja_06_YAT_HJVSEDC_Discrepancy_Report");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_07_SGH_EDCVSHJ_Discrepancy_Report()
{
String sql = @"sp_ja_07_SGH_EDCVSHJ_Discrepancy_Report";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\SGH EDCVSHJ Discrepancy Report Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_07_SGH_EDCVSHJ_Discrepancy_Report\Output\SGH EDCVSHJ Discrepancy Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_07_SGH_EDCVSHJ_Discrepancy_Report");
}
else
{
SendAutoMail("", null, "ja_07_SGH_EDCVSHJ_Discrepancy_Report");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
public String Output_ja_08_SGH_HJVSEDC_Discrepancy_Report()
{
String sql = @"sp_ja_08_SGH_HJVSEDC_Discrepancy_Report";
SqlConnection conn = new SqlConnection(Common.GetConnectionString());
SqlCommand comd = new SqlCommand(sql, conn);
comd.CommandTimeout = 900;
SqlDataAdapter da = new SqlDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
string file = "";
try
{
file = OutputToExcel(FileManager.ApplicationPath + @"\AutoReport\SGH HJVSEDC Discrepancy Report Temp.xlsx",
GetOutputPath() + @"\AutoReport\ja_08_SGH_HJVSEDC_Discrepancy_Report\Output\SGH HJVSEDC Discrepancy Report_" + System.DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx",
ds.Tables[0],
1);
OutputToExcel("",
file,
ds.Tables[1],
2);
OutputToExcel("",
file,
ds.Tables[2],
3);
#region Send Mail
if (ds.Tables[0].Rows.Count > 0)
{
SendAutoMail("", file, "ja_08_SGH_HJVSEDC_Discrepancy_Report");
}
else
{
SendAutoMail("", null, "ja_08_SGH_HJVSEDC_Discrepancy_Report");
}
#endregion
}
catch (Exception ex)
{
Log.SaveException(ex);
}
ds.Dispose();
da.Dispose();
conn.Close();
return "OK";
}
#endregion
}
}
121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
