private POInformation MakePOInformation()
{
POInformation p = new POInformation();
p.OrderNumber = PoNumbertxt.Text;
p.Purchaser = purchasercmb.Text;
p.Vendor = vendorcmb.Text;
p.JobNumber = jobnumbertxt.Text;
p.OrderDate = orderdate.Value.Year + "-" + orderdate.Value.Month + "-" + orderdate.Value.Day;
p.Total = ParseFloat(totaltxt.Text);
p.Note = GetNoteStringForDB();
p.ProjectName = projecttxt.Text;
p.DeliveryDate = deliverydate.Value.Year + "-" + deliverydate.Value.Month + "-" + deliverydate.Value.Day;
return(p);
}
private bool SubmitPO(POInformation p)
{
string queue = BuildQueue.buildQueuePO(p);
mPurchasers = new List <Purchaser>();
try
{
var command = new OleDbCommand();
command.Connection = connection;
command.CommandText = queue;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error Submitting PO Information");
connection.Close();
return(false);
}
finally
{
connection.Close();
}
int materialcount = 0;
for (int i = 0; i < mOrderItems.Count; i++)
{
SubmitMaterialItem(mOrderItems[i], p);
materialcount++;
/*if (OrderItems[i].isMaterial)
* {
* SubmitMaterialItem(OrderItems[i], p);
* materialcount++;
* }*/
}
MessageBox.Show("Purchase Order submitted (" + materialcount + ")");
return(true);
}
public List <POInformation> GetPurchaseOrders()
{
List <POInformation> pos = new List <POInformation>();
string sqlquery = "SELECT * FROM PurchaseOrder"; //"SELECT * FROM PurchaseOrder"
connection.Open();
var command = new OleDbCommand(sqlquery, connection);
command.Connection = connection;
command.CommandText = sqlquery;
var reader = command.ExecuteReader();
try
{
while (reader.Read())
{
POInformation p = new POInformation();
p.OrderNumber = reader.GetString(1);
p.Purchaser = reader.GetString(2);
p.Vendor = reader.GetString(3);
p.JobNumber = reader.GetString(4);
p.OrderDate = reader.GetDateTime(5).ToShortDateString();
//p.Total = reader.GetFloat(6);
//p.File = reader.GetString(7);
//p.Note = reader.GetString(8);
pos.Add(p);
}
}
catch (Exception e) {
Console.WriteLine("error");
}
finally
{
reader.Close();
connection.Close();
}
return(pos);
}
private void submitbtn_Click(object sender, EventArgs e)
{
POInformation p = MakePOInformation();
if (!isPOValid())
{
MessageBox.Show("ERROR: Purchase Order Not Valid, Please Check Fields");
return;
}
if (!SubmitPO(p))
{
MessageBox.Show("ERROR: Purchase Order Not inserted into database");
return;
}
if (!SavePO())
{
MessageBox.Show("ERROR: Purchase Order Not Saved");
return;
}
}
private void SubmitMaterialItem(InventoryOrderItem ioi, POInformation p)
{
string queue = BuildQueue.buildQueueMaterial(ioi, p);
mPurchasers = new List <Purchaser>();
try
{
var command = new OleDbCommand();
command.Connection = connection;
command.CommandText = queue;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error Submitting Material Item. " + ex);
}
finally
{
connection.Close();
}
}
public static string buildQueuePO(POInformation p)
{
string QA, QB;
QA = "INSERT INTO PurchaseOrder (";
QB = "VALUES('";
QA = QA + "PO_NUMBER,";
QB = QB + p.OrderNumber + "','";
QA = QA + "PURCHASER,";
QB = QB + p.Purchaser + "','";
QA = QA + "VENDOR,";
QB = QB + p.Vendor + "','";
QA = QA + "PROJECT_NUMBER,";
QB = QB + p.JobNumber + "','";
QA = QA + "PO_DATE,";
QB = QB + p.OrderDate + "','";
QA = QA + "TOTAL,";
QB = QB + p.Total + "','";
QA = QA + "FILE,";
QB = QB + " " + "','";
QA = QA + "NOTES";
QB = QB + p.Note;
QA = QA + ")";
QB = QB + "')";
QB.Replace("''", "' '");
return(QA + " " + QB);
}
public static string buildQueueMaterial(InventoryOrderItem i, POInformation p)
{
string QA, QB;
QA = "INSERT INTO MaterialInventory (";
QB = "VALUES('";
QA = QA + "PO_NUMBER,";
QB = QB + p.OrderNumber + "','";
QA = QA + "ORDER_BY,";
QB = QB + p.Purchaser + "','";
QA = QA + "DATE_ORDERED,";
QB = QB + p.OrderDate + "','";
QA = QA + "PROJECT,";
QB = QB + p.ProjectName + "','";
QA = QA + "JOB_NUMBER,";
QB = QB + p.JobNumber + "','";
QA = QA + "VENDOR,";
QB = QB + p.Vendor + "','";
QA = QA + "GAUGE,";
QB = QB + i.gauge + "','";
QA = QA + "DESCRIPTION,";
QB = QB + i.description + "','";
QA = QA + "SHEET_SIZE,";
QB = QB + i.width.ToString() + " x " + i.height.ToString() + " " + i.size_unit + "','";
QA = QA + "MATERIAL,";
QB = QB + i.material + "','";
QA = QA + "ETA,";
QB = QB + p.DeliveryDate + "','";
QA = QA + "ATA,";
QB = QB + "1900-01-01" + "','";
//------------------------------------------------------------
QA = QA + "QUANTITY,";
QB = QB + i.quantity + "','";
QA = QA + "INVENTORY_QUANTITY,";
QB = QB + "-" + "','";
QA = QA + "UNIT_COST,";
QB = QB + i.unit_price + "','";
QA = QA + "UNIT,";
QB = QB + "-" + "','"; //ioi.unit + "','";
QA = QA + "TOTAL,";
QB = QB + i.total + "','";
QA = QA + "COLOR,";
QB = QB + i.color + "','";
QA = QA + "MATERIAL_ITEM,"; //category;
QB = QB + i.category + "','";
QA = QA + "STOCK_ITEM,"; //designation;
QB = QB + i.designation + "','";
QA = QA + "STATUS";
QB = QB + i.status;
QA = QA + ")";
QB = QB + "')";
//QB.Replace("''", "'-'");
return(QA + " " + QB);
}