public static bool IsCompleted(EMDataSet.ContainerTblRow row, out string reason) { reason = null; if (!row.IsApplyClosingToEntireContainerNull() && row.ApplyClosingToEntireContainer!=0) { if (!row.IsContainerPickupTerminalNull() && row.ContainerPickupTerminal != "") return true; if (!row.IsContainerProofOfDeliveryNull() && row.ContainerProofOfDelivery != "") return true; reason = "You must enter either a terminal or a bill of lading before closing."; return false; } foreach (EMDataSet.ContBundleTblRow bundleRow in row.GetContBundleTblRows()) { // 2 options: either, each row has a bil of lading item // or it has a pick up date and terminal if (IsContainerItemDone(bundleRow,out reason)) continue; return false; } if (row.GetContBundleTblRows().Length == 0) return false; return true; }
void JoinContainerDatabase(EMDataSet.ContainerTblRow headerRow) { DataTable table = new DataTable(); EMDataSet.ContBundleTblRow[] bundleRows = headerRow.GetContBundleTblRows(); System.Array.Sort(bundleRows,new SortByBundleNum()); string[] fieldsAndPaths= { "BUNDLE_NUMBER","BundleSeqNumber", "COMPANY_NAME","ContID>ContainerTbl.CustomerID>CompanyTbl.CompName",// + //"ContID>ContainerTbl.CustomerID>CompanyTbl.CompName", "CONTNUMBER","ContID>ContainerTbl.ContNumber", "PO_NUMBER","POItemNumber>POItemTbl.POID>POHeaderTbl.PONumber", "SIZE","POItemNumber>POItemTbl.SizeOfItem", "ITEM_NAME","POItemNumber>POItemTbl.FinishID>FinishTbl.FinishType+" + "POItemNumber>POItemTbl.ItemID>ItemTbl.ItemName+"+ "POItemNumber>POItemTbl.TreatmentID>TreatmentTbl.TreatmentType", "CODE","POItemNumber>POItemTbl.ItemAccessCode", "WEIGHT_KGS","MetricShipQty", "WEIGHT_LBS","EnglishShipQty", "HEAT","Heat", "INVOICE","InvoiceNumber", "BAY","BayNumber", "ETA","ContID>ContainerTbl.ETA", "RATE","POItemNumber>POItemTbl.CustRate", "BRANCH","POItemNumber>POItemTbl.POID>POHeaderTbl.CustomerLocationID>LocationTbl.LocName" }; string[] fields = new string[fieldsAndPaths.Length/2]; string[] paths = new string[fieldsAndPaths.Length/2]; for(int i =0;i<fields.Length;i++) { fields[i] = fieldsAndPaths[i*2]; paths[i] = fieldsAndPaths[i*2+1]; } string firstRowName = "BUNDLE_NUMBER"; int contID = headerRow.ContID; EMDataSet.ContBundleTblRow[] rows = (EMDataSet.ContBundleTblRow[]) headerRow.Table.DataSet.Tables["ContBundleTbl"]. Select("ContID = " + contID.ToString()); // Sort rows based on bundle number Array.Sort(rows, new SortBasedOnBundle()); ArrayList listOfLists = Join(rows, paths); ArrayList[] listOfLists2 = (ArrayList[]) listOfLists.ToArray(typeof(ArrayList)); WriteExcelFile(fields, paths, firstRowName, listOfLists2); }
public static void PrintExcelTemplate(string fileName, EMDataSet emDataSet,EMDataSet.ContainerTblRow headerRow,string totalKgEdt, string totalLbsEdt,string filenameOut) { object application = null; object workbooks = null; object workbook = null; object sheet = null; try { // string fileName = null; string tempDirectory = Path.GetTempPath(); string tempXLS = tempDirectory + filenameOut; if (fileName == null || fileName == "") fileName = "default.xls"; try { File.Copy("m:\\shipping_notices\\" + fileName,tempXLS,true); } catch (IOException ex) { string message = "You must close Excel before printing.\n\n" + ex.Message; throw new Exception(message,ex); } Type tApp = Type.GetTypeFromProgID("Excel.Application"); application = Activator.CreateInstance(tApp); tApp.InvokeMember("Visible",BindingFlags.SetProperty,null,application,new object[]{true}); workbooks = tApp.InvokeMember("Workbooks",BindingFlags.GetProperty,null,application,new object[]{}); Type tWorkbooks = workbooks.GetType(); workbook = tWorkbooks.InvokeMember("Open",BindingFlags.InvokeMethod,null,workbooks, new object[]{tempXLS}); Type tWorkbook = workbook.GetType(); sheet = tWorkbook.InvokeMember("ActiveSheet",BindingFlags.GetProperty,null,workbook,new object[]{}); Type tSheet = sheet.GetType(); int bundleRow = 0; for (int row = 1;row<40;row++) for (int column=1;column<25;column++) { string value = ExcelGetValue(sheet,tSheet,row,column); if (value == "<BUNDLE_NUMBER>" || value == "<PO_NUMBER>") bundleRow = row; string replacement = ReplaceKeyWord(emDataSet,headerRow,totalKgEdt,totalLbsEdt,value); if (replacement != null) { ExcelPutValueSplitLines(sheet,tSheet,row,column,replacement); } } if (bundleRow != 0) { RemoveColumnIfDoesntExist(sheet,tSheet,bundleRow,"<HEAT>","Heat",emDataSet,headerRow); RemoveColumnIfDoesntExist(sheet,tSheet,bundleRow,"<INVOICE>","InvoiceNumber",emDataSet,headerRow); RemoveColumnIfDoesntExist(sheet,tSheet,bundleRow,"<BAY>","BayNumber",emDataSet,headerRow); RemoveBranchColumnIfNotUnique(sheet, tSheet, bundleRow, emDataSet, headerRow); RemoveIACColumnIfDoesntExist(sheet,tSheet,bundleRow,emDataSet,headerRow); int currentRow = bundleRow + 1; EMDataSet.ContBundleTblRow[] bundleRows = headerRow.GetContBundleTblRows(); System.Array.Sort(bundleRows,new SortByBundleNum()); foreach (EMDataSet.ContBundleTblRow row in bundleRows) { if (!DataInterface.IsRowAlive(row)) continue; ExcelInsertRow(sheet,tSheet,currentRow); for (int column=1;;column++) { string tag = ExcelGetValue(sheet,tSheet,bundleRow,column); if (tag == null || tag == "") break; string value = ReplaceRowTag(emDataSet,row,tag); ExcelPutValue(sheet,tSheet,currentRow,column,value); } ++currentRow; } } ExcelRemoveRow(sheet,tSheet,bundleRow); } catch(TargetInvocationException ex) { MessageBox.Show(ex.InnerException.Message); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { if (application!=null) Marshal.ReleaseComObject(application); if (workbooks!=null) Marshal.ReleaseComObject(workbooks); if (workbook!=null) Marshal.ReleaseComObject(workbook); if (sheet!=null) Marshal.ReleaseComObject(sheet); } }
static EMDataSet.LocationTblRow[] GetCustomerLocationRows(EMDataSet.ContainerTblRow container) { EMDataSet dataSet = (EMDataSet)container.Table.DataSet; ArrayList listOfPOIDs = new ArrayList(); foreach (EMDataSet.ContBundleTblRow bundle in container.GetContBundleTblRows()) { listOfPOIDs.Add(bundle.POItemTblRow.POID); } listOfPOIDs.Sort(); AdapterHelper.Unique(ref listOfPOIDs); ArrayList listOfCustomers = new ArrayList(); foreach (int poid in listOfPOIDs) { EMDataSet.POHeaderTblRow header = dataSet.POHeaderTbl.FindByPOID(poid); if (!header.IsCustomerIDNull()) listOfCustomers.Add(header.CustomerLocationID); } listOfCustomers.Sort(); AdapterHelper.Unique(ref listOfCustomers); EMDataSet.LocationTblRow[] locTblRows = new EMDataSet.LocationTblRow[listOfCustomers.Count]; for (int i = 0; i < locTblRows.Length; i++) { int locid = (int)listOfCustomers[i]; locTblRows[i] = dataSet.LocationTbl.FindByLocID(locid); } return locTblRows; }
public static void RemoveIACColumnIfDoesntExist(object sheet,System.Type tSheet, int headingRow,EMDataSet emDataSet,EMDataSet.ContainerTblRow headerRow) { bool exist = false; foreach (EMDataSet.ContBundleTblRow row in headerRow.GetContBundleTblRows()) { if (row.POItemTblRow.IsItemAccessCodeNull() || row.POItemTblRow.ItemAccessCode == "") continue; // otherwise exist = true; } if (!exist) RemoveColumn(sheet,tSheet,headingRow,"<CODE>"); }
public static void RemoveColumnIfDoesntExist(object sheet,System.Type tSheet, int headingRow,string columnTag,string fieldName, EMDataSet emDataSet,EMDataSet.ContainerTblRow headerRow) { foreach (EMDataSet.ContBundleTblRow row in headerRow.GetContBundleTblRows()) { if (!row.IsNull(fieldName)) return; } RemoveColumn(sheet,tSheet,headingRow,columnTag); }
public static void RemoveBranchColumnIfNotUnique(object sheet, System.Type tSheet, int headingRow, EMDataSet emDataSet, EMDataSet.ContainerTblRow headerRow) { // customer location ArrayList poList = new ArrayList(); foreach (EMDataSet.ContBundleTblRow bundleRow in headerRow.GetContBundleTblRows()) { int poid = bundleRow.POItemTblRow.POID; poList.Add(poid); } AdapterHelper.Unique(ref poList); ArrayList custLocList = new ArrayList(); foreach (int poid in poList) { EMDataSet.POHeaderTblRow tblRow = emDataSet.POHeaderTbl.FindByPOID(poid); if (!tblRow.IsCustomerLocationIDNull()) custLocList.Add(tblRow.CustomerLocationID); } AdapterHelper.Unique(ref custLocList); if (custLocList.Count < 2) { RemoveColumn(sheet, tSheet, headingRow, "<BRANCH>"); } }