private void button3_Click(object sender, RibbonControlEventArgs e)
{
try
{
Excel.Application app = Globals.ThisAddIn.Application;
Excel.Workbook wkbook = app.ActiveWorkbook;
Excel.Worksheet wksheet = wkbook.ActiveSheet;
string jobnumber = wksheet.Cells[1, "A"].Value;
var wip = new adoEntities();
Kickoff ko = wip.Kickoffs.Find(jobnumber);
ActualsRep act = wip.ActualsReps.Find(jobnumber);
//RecordDataView recorddataview = db.RecordDataView.SingleOrDefault(m => m.Record_number == id);
JobMetric jo = wip.JobMetrics.FirstOrDefault(m => m.BaseID == jobnumber);
// JobMetric jo = wip.JobMetrics.Find(jobnumber);
//Quoted Hours
wksheet.Cells[16, "B"].Value = ko.DesignEst;
wksheet.Cells[17, "B"].Value = ko.PlanningEst;
wksheet.Cells[18, "B"].Value = ko.WaterjetEst;
wksheet.Cells[19, "B"].Value = ko.WeldEst;
wksheet.Cells[20, "B"].Value = ko.NCEst;
wksheet.Cells[21, "B"].Value = ko.ConventionalEst;
wksheet.Cells[22, "B"].Value = ko.AssemblyEst;
wksheet.Cells[23, "B"].Value = ko.InspectionEst;
//Adjusted Hours
wksheet.Cells[16, "E"].Value = ko.Design;
wksheet.Cells[17, "E"].Value = ko.Planning;
wksheet.Cells[18, "E"].Value = ko.Waterjet;
wksheet.Cells[19, "E"].Value = ko.Weld;
wksheet.Cells[20, "E"].Value = ko.NC;
wksheet.Cells[21, "E"].Value = ko.Convetional;
wksheet.Cells[22, "E"].Value = ko.Assy;
wksheet.Cells[23, "E"].Value = ko.Inspection;
//Actual Hours
MessageBox.Show("Time for the Actuals");
try
{
wksheet.Cells[16, "H"].Value = act.Design;
wksheet.Cells[17, "H"].Value = act.Planning;
wksheet.Cells[18, "H"].Value = act.Waterjet;
wksheet.Cells[19, "H"].Value = act.Weld;
wksheet.Cells[20, "H"].Value = act.Nc;
wksheet.Cells[21, "H"].Value = act.Machining;
wksheet.Cells[22, "H"].Value = act.Assy;
wksheet.Cells[23, "H"].Value = act.Inspection;
wksheet.Cells[16, "I"].Value = act.DesignCost;
wksheet.Cells[17, "I"].Value = act.PlanningCost;
wksheet.Cells[18, "I"].Value = act.WaterJetCost;
wksheet.Cells[19, "I"].Value = act.WeldCost;
wksheet.Cells[20, "I"].Value = act.NcCost;
wksheet.Cells[21, "I"].Value = act.MachiningCost;
wksheet.Cells[22, "I"].Value = act.AssyCost;
wksheet.Cells[23, "I"].Value = act.InspectionCost;
}
catch (Exception ef)
{
MessageBox.Show(ef.ToString());
}
//Margin
wksheet.Cells[8, "I"].Value = jo.Margin;
wksheet.Cells[9, "I"].Value = jo.Actual;
wksheet.Cells[1, "F"].Value = jo.POValue;
wksheet.Cells[2, "F"].Value = jo.ContractDate;
wksheet.Cells[1, "I"].Value = "FUCKIFIKNOW";
wksheet.Cells[2, "I"].Value = jo.ShipDate;
}
catch (Exception ef)
{
MessageBox.Show(ef.Message.ToString());
}
//Convert.ToInt32(wksheet.Cells[20, "BV"].Value);
}
private void btmImport_Click(object sender, RibbonControlEventArgs e)
{
Excel.Workbook xlBook = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Application xlApp = new Excel.Application();
Excel.Worksheet xlSheet = xlBook.Sheets[1];
Excel.Range xlRange = xlSheet.UsedRange;
adoEntities db = new adoEntities();
JobHour batch = new JobHour();
bool check = db.JobHours.Any(u => u.Batch > 0);
int intID = new int();
if (check == true)
{
intID = db.JobHours.Max(u => u.Batch) + 1;
}
else
{
intID = 1;
}
Batch batchinfo = new Batch();
batchinfo.Batch1 = intID;
batchinfo.BatchDate = DateTime.Now.Date;
batchinfo.BatchTime = DateTime.Now;
db.Batches.Add(batchinfo);
for (int i = 2; i < 900; i++)
{
try
{
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
{
if (xlRange.Cells[i, 1].value.ToString() != "End")
{
Job job = new Job();
JobMetric metrics = new JobMetric();
JobHour hours = new JobHour();
job.Base = xlRange.Cells[i, 1].Value.ToString();
try
{
hours.BaseID = xlRange.Cells[i, 1].value();
hours.Batch = intID;
hours.Machine = (int)xlRange.Cells[i, 8].value();
hours.Build = (int)xlRange.Cells[i, 9].value();
hours.Weld = (int)xlRange.Cells[i, 10].value();
hours.Design = (int)xlRange.Cells[i, 11].value();
hours.Tracker = (int)xlRange.Cells[i, 12].value();
metrics.Comp = (decimal)xlRange.Cells[i, 6].Value();
metrics.ShipDate = xlRange.Cells[i, 7].value();
metrics.POValue = (decimal)xlRange.Cells[i, 4].value();
metrics.Actual = (decimal)xlRange.Cells[i, 5].value();
metrics.BaseID = xlRange.Cells[i, 1].value();
metrics.Type = xlRange.Cells[i, 2].value();
metrics.batch = intID;
metrics.Margin = (int)xlRange.Cells[i, 15].value();
metrics.RemainingRev = (int)xlRange.Cells[i, 16].value();
metrics.Projected = (int)xlRange.Cells[i, 14].value();
metrics.ContractDate = xlRange.Cells[i, 17].value();
if (db.Jobs.Any(Job => Job.Base.ToString() == job.Base) == false)
{
db.Jobs.Add(job);
db.JobMetrics.Add(metrics);
db.JobHours.Add(hours);
}
else
{
db.JobMetrics.Add(metrics);
db.JobHours.Add(hours);
}
}
catch (Exception ex)
{
MessageBox.Show("There is an issue with the data you are trying to import. Check for data formatting issues!");
break;
}
}
else
{
int count = i - 1;
MessageBox.Show("Success! " + count.ToString() + " records imported successfully");
break;
}
}
else
{
break;
}
db.SaveChanges();
}
catch (DbUpdateException dbup)
{
Debug.WriteLine(dbup.Entries.ToString());
Debug.WriteLine(dbup.InnerException.ToString());
}
}
}