internal static bool SQLBindColumn(ODBCHStatement statementHandle, ODBCData column)
{
var result = ODBCNativeMethods.SQLBindCol(statementHandle, column.ColumnNumber, column.ColumnType, column.ColumnData, column.ColumnDataLength, out column.ColumnDataRealLength);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Error binding columns");
}
return(true);
}
static void Main(string[] args)
{
Logger.LogWrite("MSG", "Start: " + DateTime.Now.ToString());
// Declare and defaults
int odbcCnt = 0;
int insertCnt = 0;
int importCnt = 0;
#region FromSTRATIX
ODBCData objODBC = new ODBCData();
List <IPTPSH> lstIPTPSH = new List <IPTPSH>();
// Get data from Straix
try
{
lstIPTPSH = objODBC.Get_IPTPSH();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
#endregion
#region ToSQL
SQLData objSQL = new SQLData();
// Only work in SQL database, if records were retreived from Stratix
if (lstIPTPSH.Count != 0)
{
odbcCnt = lstIPTPSH.Count;
// Put Stratix data in lstIPTPSH into IMPORT IPTPSH table
try
{
importCnt = objSQL.Write_IPTPSH_IMPORT(lstIPTPSH);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
// Call SP to put IMPORT IPTPSH table data into WIP IPTPSH table
try
{
insertCnt = objSQL.Write_IMPORT_to_IPTPSH();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
Logger.LogWrite("MSG", "ODBC/IMPORT/INSERT=" + odbcCnt.ToString() + ":" + importCnt.ToString() + ":" + insertCnt.ToString());
}
else
{
Logger.LogWrite("MSG", "No data");
}
Logger.LogWrite("MSG", "End: " + DateTime.Now.ToString());
#endregion
// Testing
//Console.WriteLine("Press key to exit");
//Console.ReadKey();
}
static void Main(string[] args)
{
Logger.LogWrite("MSG", "Start: " + DateTime.Now.ToString());
// Args will change based on STXtoSQL program goal
string date1 = "";
string date2 = "";
int odbcCnt = 0;
int insertCnt = 0;
int importCnt = 0;
// Get args
try
{
if (args.Length > 0)
{
/*
* Must be in format mm/dd/yyyy. No time part
*/
date1 = args[0].ToString();
date2 = args[1].ToString();
}
else
{
/*
* No args
* Date range will be 1st of month to yesterday
*/
DateTime dtToday = DateTime.Today;
DateTime dtFirst = new DateTime(dtToday.Year, dtToday.Month, 1);
/*
* Need one date part of datetime.
* Time and date are separated by a space, so split the string
* and only use the 1st element.
*/
string[] date1Split = dtFirst.ToString().Split(' ');
string[] date2Split = dtToday.AddDays(-1).ToString().Split(' ');
date1 = date1Split[0];
date2 = date2Split[0];
}
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
//Console.WriteLine(ex.Message.ToString());
}
#region FromSTRATIX
ODBCData objODBC = new ODBCData();
List <IPJRAN> lstIPJRAN = new List <IPJRAN>();
// Get data from Straix by date range
try
{
lstIPJRAN = objODBC.Get_IPJRAN(date1, date2);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
//Console.WriteLine(ex.Message.ToString());
}
#endregion
#region ToSQL
SQLData objSQL = new SQLData();
// Only work in SQL database, if records were retreived from Stratix
if (lstIPJRAN.Count != 0)
{
odbcCnt = lstIPJRAN.Count;
// Put Stratix data in lstIPJRAN into IMPORT IPJRAN table
try
{
importCnt = objSQL.Write_IPJRAN_IMPORT(lstIPJRAN);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
//Console.WriteLine(ex.Message.ToString());
}
// Call SP to put IMPORT IPJRAN table data into WIP IPJRAN table
try
{
//insertCnt = objSQL.Write_IMPORT_to_IPJRAN(date1, date2);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
//Console.WriteLine(ex.Message.ToString());
}
Logger.LogWrite("MSG", "Range=" + date1 + ":" + date2 + " ODBC/IMPORT/INSERT=" + odbcCnt.ToString() + ":" + importCnt.ToString() + ":" + insertCnt.ToString());
}
else
{
Logger.LogWrite("MSG", "No data");
}
Logger.LogWrite("MSG", "End: " + DateTime.Now.ToString());
#endregion
// Testing
Console.WriteLine("Press key to exit");
Console.ReadKey();
}
static void Main(string[] args)
{
Logger.LogWrite("MSG", "Start: " + DateTime.Now.ToString());
// Declare and defaults
int odbcCnt = 0;
int insertCnt = 0;
int importCnt = 0;
int arborCnt = 0;
#region FromSTRATIX
ODBCData objODBC = new ODBCData();
List <IPTFRA> lstIPTFRA = new List <IPTFRA>();
try
{
lstIPTFRA = objODBC.Get_IPTFRA();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
#endregion
#region ToSQL
SQLData objSQL = new SQLData();
// Only work in SQL database, if records were retreived from Stratix
if (lstIPTFRA.Count != 0)
{
odbcCnt = lstIPTFRA.Count;
// Put Stratix data in lstIPTFRA into IMPORT IPTFRA table
try
{
importCnt = objSQL.Write_IPTFRA_IMPORT(lstIPTFRA);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
/*
* Build a more useful arbor from the Stratix data for each job
* Build Arbor from IMPORT and into Arbor table
*/
try
{
arborCnt = objSQL.Build_Arbor();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
// Call SP to put IMPORT IPTFRA table data into WIP IPTFRA table
try
{
insertCnt = objSQL.Write_IMPORT_to_IPTFRA();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
Logger.LogWrite("MSG", "ODBC/IMPORT/ARBOR/INSERT=" + odbcCnt.ToString() + ":" + importCnt.ToString() + ":" + arborCnt.ToString() + ":" + insertCnt.ToString());
}
else
{
Logger.LogWrite("MSG", "No data");
}
Logger.LogWrite("MSG", "End: " + DateTime.Now.ToString());
#endregion
// Testing
//Console.WriteLine("Press key to exit");
//Console.ReadKey();
}
static void Main(string[] args)
{
Logger.LogWrite("MSG", "Start: " + DateTime.Now.ToString());
string date1 = "";
string date2 = "";
// Declare and defaults
int odbcCnt = 0;
int insertCnt = 0;
int importCnt = 0;
int dupCnt = 0;
// Get args
try
{
if (args.Length > 0)
{
/*
* Must be in format mm/dd/yyyy. No time part
*/
date1 = args[0].ToString();
date2 = args[1].ToString();
}
else
{
// No args = current month to yesterday
DateTime dtToday = DateTime.Today;
DateTime dtFirst;
/*
* If 1st day of month, start from the last day of previous month
* or that day will be missed in the report
* ex: Today os 8/1, dtFirst should be 7/31
*/
if (DateTime.Today.Day == 1)
{
// Get number of days in previous month
int dtDaysInMonth = DateTime.DaysInMonth(dtToday.Year, dtToday.Month - 1);
// Create new DateTime using last month and last day of last month
dtFirst = new DateTime(dtToday.Year, dtToday.Month - 1, dtDaysInMonth);
}
else
{
dtFirst = new DateTime(dtToday.Year, dtToday.Month, 1);
}
/*
* Need one date part of datetime.
* Time and date are separated by a space, so split the string
* and only use the 1st element.
*/
string[] date1Split = dtFirst.ToString().Split(' ');
string[] date2Split = dtToday.AddDays(-1).ToString().Split(' ');
date1 = date1Split[0];
date2 = date2Split[0];
}
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
#region FromSTRATIX
ODBCData objODBC = new ODBCData();
List <IPJRAN> lstIPJRAN = new List <IPJRAN>();
// Get data from Straix by date range
try
{
lstIPJRAN = objODBC.Get_IPJRAN(date1, date2);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
#endregion
#region ToSQL
SQLData objSQL = new SQLData();
// Only work in SQL database, if records were retreived from Stratix
if (lstIPJRAN.Count != 0)
{
odbcCnt = lstIPJRAN.Count;
// Put Stratix data in lstIPJRAN into IMPORT IPJRAN table
try
{
importCnt = objSQL.Write_IPJRAN_IMPORT(lstIPJRAN);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
/*
* Jobs that contain more than one item to consume will have duplicate Jobs in IPJRAN.
* Need to Sum(Lbs) of duplicate jobs into one row and delete the other rows.
*/
try
{
dupCnt = objSQL.Clean_Dup_Jobs_IMPORT();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
// Call SP to put IMPORT IPJRAN table data into WIP IPJRAN table
try
{
insertCnt = objSQL.Write_IMPORT_to_IPJRAN(date1, date2);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return");
return;
}
Logger.LogWrite("MSG", "Range=" + date1 + ":" + date2 + " ODBC/IMPORT/DUPS/INSERT=" + odbcCnt.ToString() + ":" + importCnt.ToString() + ":" + dupCnt.ToString() + ":" + insertCnt.ToString());
}
else
{
Logger.LogWrite("MSG", "No data");
}
Logger.LogWrite("MSG", "End: " + DateTime.Now.ToString());
#endregion
// Testing
//Console.WriteLine("Press key to exit");
//Console.ReadKey();
}
static void Main(string[] args)
{
// Args will change based on STXtoSQL program goal
//List<string> lstArgs = new List<string>();
string date1 = "";
string date2 = "";
try
{
if (args.Length > 0)
{
// args = date range
date1 = args[0].ToString();
date2 = args[1].ToString();
}
else
{
// No args = yesterday
// Yesterday = -1. testing = -2 or more
DateTime dtYst = DateTime.Now.AddDays(-3);
date1 = dtYst.Month.ToString() + "/" + dtYst.Day.ToString() + "/" + dtYst.Year.ToString();
date2 = date1;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
// Testing
Console.WriteLine(date1 + " / " + date2);
#region FromSTRATIX
ODBCData objODBC = new ODBCData();
List <Sales> lstSales = new List <Sales>();
try
{
lstSales = objODBC.Get_Sales(date1, date2);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
// Testing
Console.WriteLine("Retrieve records: " + lstSales.Count.ToString());
//foreach (Sales b in lstSales)
//{
// Console.WriteLine(b.qlf + " / " + b.brh + " / " + b.pep + " / " + b.wgt + " / " + b.val + " / " + b.avg_val + " / " + b.inv_dt + " / " + b.mn.ToString() + " / " + b.dy.ToString() + " / " + b.yr.ToString());
//}
#endregion
#region ToSQL
int rowCnt = 0;
SQLData objSQL = new SQLData();
// Only work in SQL database, if records were retreived from Stratix
if (lstSales.Count != 0)
{
// Put lstSales into TMP Sales table
try
{
rowCnt = objSQL.Write_Sales_TMP(lstSales);
Console.WriteLine("TMP inserted: " + rowCnt.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
// Call SP to put TMP Sales into Sales table
try
{
rowCnt = objSQL.Write_TMP_to_Sales();
Console.WriteLine("Sales inserted: " + rowCnt.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}
#endregion
// Testing
Console.WriteLine("Press key to exit");
Console.ReadKey();
}
static void Main(string[] args)
{
Logger.LogWrite("MSG", "Start: " + DateTime.Now.ToString());
// Args initialization
bool emailIt = false;
/*
* Copy empty File from templates to destination folder
* Create a full path to pass to Excel methods
*/
string fileName = ConfigurationManager.AppSettings.Get("FileName");
string templatePath = ConfigurationManager.AppSettings.Get("TemplatePath");
string destPath = ConfigurationManager.AppSettings.Get("DestPath");
File.Copy(Path.Combine(templatePath, fileName), Path.Combine(destPath, fileName), true);
// Pass to Excel methods to be used in OleDb connection string
string fullPath = Path.Combine(destPath, fileName);
#region Args
/*
* arg options:
* 1. Email = true or false
*/
try
{
// More than 1 arg[] is invalid, else get Email value from arg[0]
if ((args.Length != 1))
{
Logger.LogWrite("MSG", "Invalid number of args[]");
Logger.LogWrite("MSG", "Return on args[]");
return;
}
else
{
emailIt = Convert.ToBoolean(args[0]);
}
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return on Args");
return;
}
#endregion
#region ODBCData
ODBCData objQDS = new ODBCData();
List <QDSDataModel> lstLogData = new List <QDSDataModel>();
try
{
lstLogData = objQDS.Get_QDSNotAppv();
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return on LogData");
return;
}
#endregion
#region Excel
/*
* Export each List<object> to correct XLS tab
*/
ExcelExport objXLS = new ExcelExport();
// Log - Tab
try
{
objXLS.WriteQDSNotAppv(lstLogData, fullPath);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return on Bookings XLS");
return;
}
#endregion
#region Email
/*
* Emailing the file is optoinal
* emailIt = true or false
*/
if (emailIt)
{
List <EmployeesReportsModel> lstEmpReports = new List <EmployeesReportsModel>();
SQLData objSQL_Rpts = new SQLData();
try
{
lstEmpReports = objSQL_Rpts.Get_Emp_Reports("QDSDaily");
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return on Emp Rpts");
return;
}
try
{
string emailTo = ConfigurationManager.AppSettings.Get("EmailTo");
string pwd365 = Environment.GetEnvironmentVariable("sysOffice365PWD");
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.office365.com");
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "QDS - Daily";
mail.Body = "Report attached";
//Build To: line from emails in list of EmployeesReportsModel
foreach (EmployeesReportsModel e in lstEmpReports)
{
Logger.LogWrite("MSG", "Email: " + e.email.ToString());
mail.To.Add(e.email.ToString());
}
// Add attachment
Attachment attach;
attach = new Attachment(fullPath);
mail.Attachments.Add(attach);
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", pwd365);
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
Logger.LogWrite("MSG", "Email: " + emailTo);
}
catch (Exception ex)
{
Logger.LogWrite("EXC", ex);
Logger.LogWrite("MSG", "Return on Email");
return;
}
}
else
{
Logger.LogWrite("MSG", "No email");
}
#endregion
// Made it to the end
Logger.LogWrite("MSG", "End: " + DateTime.Now.ToString());
// Testing
//Console.WriteLine("Press key to exit");
//Console.ReadKey();
}
