public FindTechPayItemByCodeDataSet FindTechPayItemByCode(string strTechPayCode)
{
try
{
aFindTechPayItemByCodeDataSet = new FindTechPayItemByCodeDataSet();
aFindTechPayItemByCodeTableAdapter = new FindTechPayItemByCodeDataSetTableAdapters.FindTechPayItemByCodeTableAdapter();
aFindTechPayItemByCodeTableAdapter.Fill(aFindTechPayItemByCodeDataSet.FindTechPayItemByCode, strTechPayCode);
}
catch (Exception Ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Tech Pay Class // Find Tech Pay Item By Code " + Ex.Message);
}
return(aFindTechPayItemByCodeDataSet);
}
private void BtnImportExcel_Click(object sender, RoutedEventArgs e)
{
Excel.Application xlDropOrder;
Excel.Workbook xlDropBook;
Excel.Worksheet xlDropSheet;
Excel.Range range;
string strTechPayCode;
string strJobDescription;
string strValueForValidation;
decimal decTechPayPrice = 0;
bool blnFatalError = false;
int intCounter;
int intNumberOfRecords;
int intRecordsReturned;
int intColumnRange;
bool blnItemFound;
try
{
TheImportedTechPayDataSet.importedtechpay.Rows.Clear();
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
dlg.FileName = "Document"; // Default file name
dlg.DefaultExt = ".xlsx"; // Default file extension
dlg.Filter = "Excel (.xlsx)|*.xlsx"; // Filter files by extension
// Show open file dialog box
Nullable <bool> result = dlg.ShowDialog();
// Process open file dialog box results
if (result == true)
{
// Open document
string filename = dlg.FileName;
}
PleaseWait PleaseWait = new PleaseWait();
PleaseWait.Show();
xlDropOrder = new Excel.Application();
xlDropBook = xlDropOrder.Workbooks.Open(dlg.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlDropSheet = (Excel.Worksheet)xlDropOrder.Worksheets.get_Item(1);
range = xlDropSheet.UsedRange;
intNumberOfRecords = range.Rows.Count;
intColumnRange = range.Columns.Count;
for (intCounter = 2; intCounter <= intNumberOfRecords; intCounter++)
{
strTechPayCode = Convert.ToString((range.Cells[intCounter, 1] as Excel.Range).Value2).ToUpper();
strJobDescription = Convert.ToString((range.Cells[intCounter, 2] as Excel.Range).Value2).ToUpper();
strValueForValidation = Convert.ToString((range.Cells[intCounter, 3] as Excel.Range).Value2).ToUpper();
blnItemFound = false;
blnFatalError = TheDataValidationClass.VerifyDoubleData(strValueForValidation);
if (blnFatalError == false)
{
decTechPayPrice = Convert.ToDecimal(strValueForValidation);
}
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strTechPayCode);
intRecordsReturned = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode.Rows.Count;
if (intRecordsReturned > 0)
{
blnItemFound = true;
}
if (blnItemFound == false)
{
TheFindTechPayItemByDescriptionDataSet = TheTechPayClass.FindTechPayItemByDescription(strJobDescription);
intRecordsReturned = TheFindTechPayItemByDescriptionDataSet.FindTechPayItemByDescription.Rows.Count;
if (intRecordsReturned > 0)
{
blnItemFound = true;
}
}
if (blnItemFound == false)
{
ImportedTechPayDataSet.importedtechpayRow NewTechPayRow = TheImportedTechPayDataSet.importedtechpay.NewimportedtechpayRow();
NewTechPayRow.JobDescription = strJobDescription;
NewTechPayRow.TechPayCode = strTechPayCode;
NewTechPayRow.TechPayPrice = decTechPayPrice;
TheImportedTechPayDataSet.importedtechpay.Rows.Add(NewTechPayRow);
}
}
PleaseWait.Close();
dgrResults.ItemsSource = TheImportedTechPayDataSet.importedtechpay;
}
catch (Exception Ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Import Tech Pay Items // Import Excel Button " + Ex.Message);
TheMessagesClass.ErrorMessage(Ex.ToString());
}
}
private void BtnProcess_Click(object sender, RoutedEventArgs e)
{
string strTechPayCode;
string strJobDescription;
string strValueForValidation;
string strErrorMessage = "";
decimal decTechPayPrice = 0;
bool blnThereIsAProblem = false;
bool blnFatalError = false;
int intRecordsReturned;
try
{
//data validation
strTechPayCode = txtTechPayCode.Text;
if (strTechPayCode == "")
{
blnFatalError = true;
strErrorMessage += "The Tech Pay Code Was Not Entered\n";
}
else
{
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strTechPayCode);
intRecordsReturned = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode.Rows.Count;
if (intRecordsReturned > 0)
{
blnFatalError = true;
strErrorMessage += "The Tech Pay Code Has Been Entered Already\n";
}
}
strJobDescription = txtJobDescription.Text;
if (strJobDescription == "")
{
blnFatalError = true;
strErrorMessage += "The Job Description was not Entered\n";
}
else
{
TheFindTechPayItemByDescriptionDataSet = TheTechPayClass.FindTechPayItemByDescription(strJobDescription);
intRecordsReturned = TheFindTechPayItemByDescriptionDataSet.FindTechPayItemByDescription.Rows.Count;
if (intRecordsReturned > 0)
{
blnFatalError = true;
strErrorMessage += "The Job Description Has Been Entered Already\n";
}
}
strValueForValidation = txtTechPayPrice.Text;
blnThereIsAProblem = TheDataValidationClass.VerifyDoubleData(strValueForValidation);
if (blnThereIsAProblem == true)
{
blnFatalError = true;
strErrorMessage += "The Tech Pay Price is not Numeric\n";
}
else
{
decTechPayPrice = Convert.ToDecimal(strValueForValidation);
}
if (blnFatalError == true)
{
TheMessagesClass.ErrorMessage(strErrorMessage);
return;
}
blnFatalError = TheTechPayClass.InsertTechPayItem(strTechPayCode, strJobDescription, decTechPayPrice);
if (blnFatalError == true)
{
throw new Exception();
}
TheMessagesClass.InformationMessage("The Tech Pay Item Has Been Entered");
ResetControls();
}
catch (Exception Ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Blue Jay ERP // Add Tech Pay Item // Process Button " + Ex.Message);
TheMessagesClass.ErrorMessage(Ex.ToString());
}
}
private bool ProcessClevelandInvoice()
{
bool blnFatalError = false;
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
int intCounter;
int intNumberOfRecords;
DateTime datTransactionDate;
string strAssignedProjectID;
int intProjectID;
int intEmployeeID;
int intProjectIdentifictionID;
string strWOVDescription;
decimal decTotalQuantity = 0;
int intWOVTaskID = 0;
int intTransactionID;
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
intNumberOfRecords = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice.Rows.Count - 1;
for (intCounter = 1; intCounter <= intNumberOfRecords; intCounter++)
{
//getting ready to insert into tables
datTransactionDate = DateTime.Now;
strAssignedProjectID = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].DockID;
TheFindDesignProjectsByAssignedProjectID = TheDesignProjectClass.FindDesignProjectsByAssignedProjectID(strAssignedProjectID);
intProjectID = TheFindDesignProjectsByAssignedProjectID.FindDesignProjectsByAssignedProjectID[0].ProjectID;
intEmployeeID = MainWindow.TheVerifyLogonDataSet.VerifyLogon[0].EmployeeID;
intTransactionID = TheFindDesignProjectsByAssignedProjectID.FindDesignProjectsByAssignedProjectID[0].TransactionID;
blnFatalError = TheWOVInvoicingClass.InsertWOVInvoice(datTransactionDate, intProjectID, intEmployeeID, MainWindow.gintBillingID);
if (blnFatalError == true)
{
throw new Exception();
}
TheFindWOVInvoicingByTransactionDateDataSet = TheWOVInvoicingClass.FindWOVInvoicingByTransactionDate(datTransactionDate, intProjectID);
intProjectIdentifictionID = TheFindWOVInvoicingByTransactionDateDataSet.FindWOVInvoicingByTransactionDate[0].ProjectIdentificationID;
if (MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].WOV1 != 0)
{
strWOVDescription = "WOV1";
decTotalQuantity = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].WOV1;
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strWOVDescription);
intWOVTaskID = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode[0].TechPayID;
}
if (MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].WOV2 != 0)
{
strWOVDescription = "WOV2";
decTotalQuantity = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].WOV2;
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strWOVDescription);
intWOVTaskID = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode[0].TechPayID;
}
if (MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].WOV3 != 0)
{
strWOVDescription = "WOV3";
decTotalQuantity = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].WOV3;
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strWOVDescription);
intWOVTaskID = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode[0].TechPayID;
}
if (MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].PP1 != 0)
{
strWOVDescription = "PP1";
decTotalQuantity = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].PP1;
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strWOVDescription);
intWOVTaskID = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode[0].TechPayID;
}
if (MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].PP2 != 0)
{
strWOVDescription = "PP2";
decTotalQuantity = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice[intCounter].PP2;
TheFindTechPayItemByCodeDataSet = TheTechPayClass.FindTechPayItemByCode(strWOVDescription);
intWOVTaskID = TheFindTechPayItemByCodeDataSet.FindTechPayItemByCode[0].TechPayID;
}
blnFatalError = TheWOVInvoicingClass.InsertWOVInvoicingItems(intProjectID, intProjectIdentifictionID, intEmployeeID, intWOVTaskID, Convert.ToInt32(decTotalQuantity));
if (blnFatalError == true)
{
throw new Exception();
}
blnFatalError = TheDesignProjectClass.CloseDesignProject(intTransactionID, DateTime.Now);
if (blnFatalError == true)
{
throw new Exception();
}
}
worksheet = workbook.ActiveSheet;
worksheet.Name = "Projects Invoiced";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice.Rows.Count;
intColumnNumberOfRecords = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = MainWindow.TheClevelandDesignProjectInvoicingDataSet.clevelanddesigninvoice.Rows[intRowCounter][intColumnCounter].ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Projects Invoiced");
}
catch (Exception Ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Blue Jay ERP // Process Design Invoice // Process Cleveland Invoice " + Ex.Message);
TheMessagesClass.ErrorMessage(Ex.ToString());
blnFatalError = true;
}
return(blnFatalError);
}
