private partspurchasedata GetPurchaseInfo(string part)
{
try
{
partspurchasedata result = new partspurchasedata();
bool ok = true;
string sql = "SELECT * FROM partspurchaseinfo WHERE partdesc = '" + part + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
// AJTODO check for duplicate parts? - maybe no required as database has already got unique field
Int32.TryParse(rdr[0].ToString(), out result.id);
result.comment = rdr[1].ToString().Trim();
result.digikey_pn = rdr[2].ToString().Trim();
result.digikeymoq = 1;
Int32.TryParse(rdr[3].ToString().Trim(), out result.digikeymoq);
result.rs_pn = rdr[4].ToString().Trim();
result.rs_url = rdr[5].ToString().Trim();
result.mouser_pn = rdr[6].ToString().Trim();
result.mouser_url = rdr[7].ToString().Trim();
result.farnell_pn = rdr[8].ToString().Trim();
result.rfdesign_pn = rdr[9].ToString().Trim();
result.mantech_pn = rdr[10].ToString().Trim();
result.ottopn = rdr[11].ToString().Trim();
result.otherpn = rdr[12].ToString().Trim();
result.notes = rdr[13].ToString().Trim();
}
else
{
ok = false;
}
rdr.Close();
if (!ok)
{
throw new Exception(part + " not found in database");
}
return(result);
}
catch (Exception ex)
{
if (PartsToIgnoreList.Contains(part))
{
WriteStatus("Purchase info for " + part + " ignored");
throw ex;
}
else
{
ErrorCount++;
throw ex;
}
}
}
private void ProcessPart(List <OutputFields> BOMOut, string unique_id, string[] cols, int qty,
int number_of_parts, string refdesignators)
{
try
{
partspurchasedata ppd = GetPurchaseInfo(unique_id);
WriteStatus("Purchase info: id=" + ppd.id.ToString() + ", " +
"comment=" + ppd.comment + ", " +
"digikeypn=" + ppd.digikey_pn + ", " +
"digikeymoq=" + ppd.digikeymoq.ToString() + ", " +
"rs_pn=" + ppd.rs_pn + ", " +
"rs_url=" + ppd.rs_url + ", " +
"mouser_pn=" + ppd.mouser_pn + ", " +
"farnellpn=" + ppd.farnell_pn + ", " +
"rfdesignpn=" + ppd.rfdesign_pn + ", " +
"mantechpn=" + ppd.mantech_pn + ", " +
"ottopn=" + ppd.ottopn + ", " +
"otherpn=" + ppd.otherpn + ", " +
"notes=" + ppd.notes);
bool fSupplierFound = false;
PricingInfo[] dk_prices = new PricingInfo[0];
PricingInfo[] rfd_prices = new PricingInfo[0];
PricingInfo[] mt_prices = new PricingInfo[0];
PricingInfo[] om_prices = new PricingInfo[0];
PricingInfo[] rs_prices = new PricingInfo[0];
PricingInfo[] ms_prices = new PricingInfo[0];
PricingInfo[] other_prices = new PricingInfo[0];
// **** DIGIKEY *****
DigikeyTools dkt = new DigikeyTools();
if (ppd.digikey_pn != "")
{
if (ppd.digikey_pn.Contains("PIC18"))
{
ppd.digikey_pn = ppd.digikey_pn.Trim();
}
//dkt.LoadPageData(webBrowser1,ppd.digikey_pn);
dkt.LoadPageData(geckoWebBrowser1, ppd.digikey_pn);
dk_prices = dkt.GetPricingInfo();
WriteStatus("Digikey Prices:");
WriteStatus("----------------");
if (dk_prices.Count() > 0)
{
foreach (PricingInfo p in dk_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no digikey prices!");
}
WriteStatus("*");
}
// **** RS COMPONENTS *****
rswebtools rswt = new rswebtools(ppd.rs_url);
if (ppd.rs_pn != "")
{
rswt.LoadPageData(ppd.rs_pn);
rs_prices = rswt.GetPricingInfo();
WriteStatus("RS prices Prices:");
WriteStatus("----------------");
if (rs_prices.Count() > 0)
{
foreach (PricingInfo p in rs_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no Rs component prices!");
}
WriteStatus("*");
}
// **** MOUSER *****
MouserWebtools mswt = new MouserWebtools();
if (ppd.mouser_pn != "")
{
mswt.LoadPageData(ppd.mouser_pn);
ms_prices = mswt.GetPricingInfo();
WriteStatus("Mouser Prices:");
WriteStatus("----------------");
if (ms_prices.Count() > 0)
{
foreach (PricingInfo p in ms_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no Mouser component prices!");
}
WriteStatus("*");
}
// **** FARNELL *****
// **** RF Design ****
RFDesignWebTools rfdwt = new RFDesignWebTools();
if (ppd.rfdesign_pn != "")
{
rfdwt.LoadPageData(ppd.rfdesign_pn);
rfd_prices = rfdwt.GetPricingInfo();
WriteStatus("RF Design Prices:");
WriteStatus("----------------");
if (rfd_prices.Count() > 0)
{
foreach (PricingInfo p in rfd_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no RF Design prices!");
}
WriteStatus("*");
}
// **** Mantech electronics ****
MantechWebTools mtwt = new MantechWebTools();
if (ppd.mantech_pn != "")
{
mtwt.LoadPageData(ppd.mantech_pn);
mt_prices = mtwt.GetPricingInfo();
WriteStatus("Mantech Prices:");
WriteStatus("----------------");
if (mt_prices.Count() > 0)
{
foreach (PricingInfo p in mt_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no Mantech prices!");
}
WriteStatus("*");
}
// **** Otto Marketing ****
OttoMarketingWebTools omwt = new OttoMarketingWebTools();
if (ppd.ottopn != "")
{
omwt.LoadPageData(ppd.ottopn);
om_prices = omwt.GetPricingInfo();
WriteStatus("Otto marketing Prices:");
WriteStatus("----------------");
if (om_prices.Count() > 0)
{
foreach (PricingInfo p in om_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no Otto marketing prices!");
}
WriteStatus("*");
}
other_comp_tools ocdbt = new other_comp_tools();
if (ppd.otherpn != "")
{
ocdbt.LoadPricingInfo(ppd.otherpn, conn);
other_prices = ocdbt.GetPricingInfo();
WriteStatus("Other sources Prices:");
WriteStatus("---------------------");
if (other_prices.Count() > 0)
{
foreach (PricingInfo p in other_prices)
{
WriteStatus("Price: " + p.SrcCost.ToString("#0.000") + p.srcCurr + "(" + p.DestCost.ToString("#0.000") + p.destCurr + ") (" + p.minqty.ToString() + "-" + p.maxqty.ToString() + ")");
}
fSupplierFound = true;
}
else
{
WriteStatus("INFO: no other sources prices!");
}
WriteStatus("*");
}
if (PartsToIgnoreList.Contains(unique_id))
{
fSupplierFound = true;
}
if (!fSupplierFound)
{
throw new Exception("No supplier found for part (will not be included on BOM!): " + ppd.comment);
}
// AJTODO: choose lowest cost supplier
OutputFields of = new OutputFields();
of.Comment = unique_id;
of.Descr = cols[1].Replace("\"", "").Trim();
of.bomqty = qty;
of.Designators = refdesignators;
if (ppd.digikey_pn != "")
{
// only add to order if digikey has it!
WriteStatus("Using Digikey as supplier");
of.Manufacturer = dkt.GetManufacturer();
of.Manuf_pn = dkt.GetManufacturerPartNo();
of.supplier = "Digikey";
of.suppliercode = ppd.digikey_pn;
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, dk_prices);
dk_order.AddToOrder(of.suppliercode, of.Manufacturer, of.Manuf_pn, of.bomqty * number_of_parts, of.Comment, ppd.digikeymoq);
}
else if (ppd.rs_pn != "")
{
// add to rs order
WriteStatus("Using RS Components as supplier");
of.Manufacturer = rswt.GetManufacturer();
of.Manuf_pn = rswt.GetManufacturerPartNo();
of.supplier = "RS Components";
of.suppliercode = ppd.rs_pn;
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, rs_prices);
rs_order.AddToOrder(of.suppliercode, of.bomqty * number_of_parts);
}
else if (ppd.rfdesign_pn != "")
{
// add rfdesign order
WriteStatus("Using RF Design as supplier");
of.Manufacturer = rfdwt.GetManufacturer();
of.Manuf_pn = rfdwt.GetManufacturerPartNo();
of.supplier = "RF Design";
of.suppliercode = ppd.rfdesign_pn;
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, rfd_prices);
rfd_order.AddToOrder(of.suppliercode, of.bomqty * number_of_parts);
}
else if (ppd.mantech_pn != "")
{
// add mantech order
WriteStatus("Using Mantech Electronics as supplier");
of.Manufacturer = mtwt.GetManufacturer();
of.Manuf_pn = mtwt.GetManufacturerPartNo();
of.supplier = "Mantech Electronics";
of.suppliercode = ppd.mantech_pn;
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, mt_prices);
mt_order.AddToOrder(of.suppliercode, of.bomqty * number_of_parts);
}
else if (ppd.ottopn != "")
{
// add otto marketing order
WriteStatus("Using Otto Marketing as supplier");
of.Manufacturer = omwt.GetManufacturer();
of.Manuf_pn = omwt.GetManufacturerPartNo();
of.supplier = "Otto Marketing";
of.suppliercode = ppd.comment; // NOTE: we take a shortcut here!
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, om_prices);
om_order.AddToOrder(of.suppliercode, of.bomqty * number_of_parts);
}
else if (ppd.otherpn != "")
{
// add to other orders
WriteStatus("Using other supplier");
of.Manufacturer = ocdbt.GetManufacturer();
of.Manuf_pn = ocdbt.GetManufacturerPartNo();
of.supplier = ocdbt.GetSupplier();
of.suppliercode = ocdbt.GetSupplierCode();
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, other_prices);
other_order.AddToOrder(of.suppliercode, of.bomqty * number_of_parts);
}
else if (ppd.mouser_pn != "")
{
// add to mouser orders
WriteStatus("Using Mouser as supplier");
of.Manufacturer = mswt.GetManufacturer();
of.Manuf_pn = mswt.GetManufacturerPartNo();
of.supplier = "Mouser Electronics";
of.suppliercode = ppd.mouser_pn; // NOTE: we take a shortcut here!
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
of = AddPricingInfoToBOMLine(of, ms_prices);
ms_order.AddToOrder(of.suppliercode, of.bomqty * number_of_parts);
}
else
{
WriteStatus("Unknown supplier");
of.Manufacturer = "Unknown";
of.Manuf_pn = "Unknown";
of.supplier = "Unknown";
of.suppliercode = "Unknown";
of.unitprices = new double[quantities.Count()];
of.extprices = new double[quantities.Count()];
}
WriteStatus("Writing output BOM");
BOMOut.Add(of);
}
catch (Exception ex)
{
if (PartsToIgnoreList.Contains(unique_id))
{
WriteStatus("Purchase pricing info for " + unique_id + " ignored");
}
else
{
ErrorCount++;
WriteStatus("ERROR: Cannot get purchase info for " + unique_id + " - " + ex.ToString());
}
}
}