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>");
}
}
