public string addMprItemInfo(MPRItemInfo mprItemInfo)
{
return(this._mprDataAcess.addMprItemInfo(mprItemInfo));
}
public IHttpActionResult PreviouPriceUpdate(MPRItemInfo previousprice)
{
return(Ok(this._rfqBusenessAcess.PreviouPriceUpdate(previousprice)));
}
public bool deleteMPRItemInfo(MPRItemInfo mprItemInfo)
{
return(this._mprDataAcess.deleteMPRItemInfo(mprItemInfo));
}
public bool PreviouPriceUpdate(MPRItemInfo previousprice)
{
return(_rfqDataAcess.PreviouPriceUpdate(previousprice));
}
public IHttpActionResult deleteMPRItemInfo([FromBody] MPRItemInfo mprItemInfo)
{
return(Ok(this._mprBusenessAcess.deleteMPRItemInfo(mprItemInfo)));
}
public IHttpActionResult uploadExcel()
{
var revisionId = "";
var httpRequest = HttpContext.Current.Request;
var serverPath = HttpContext.Current.Server.MapPath("~/SCMDocs");
string parsedFileName = "";
if (httpRequest.Files.Count > 0)
{
revisionId = httpRequest.Files.AllKeys[0];
var postedFile = httpRequest.Files[0];
parsedFileName = string.Format(DateTime.Now.Year.ToString() + "\\" + DateTime.Now.ToString("MMM") + "\\" + revisionId + "\\" + ToValidFileName(postedFile.FileName));
serverPath = serverPath + string.Format("\\" + DateTime.Now.Year.ToString() + "\\" + DateTime.Now.ToString("MMM")) + "\\" + revisionId;
var filePath = Path.Combine(serverPath, ToValidFileName(postedFile.FileName));
if (!Directory.Exists(serverPath))
{
Directory.CreateDirectory(serverPath);
}
postedFile.SaveAs(filePath);
DataTable dtexcel = new DataTable();
bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [Sheet1$A4:M]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}
conn.Close();
int iSucceRows = 0;
try
{
YSCMEntities entities = new YSCMEntities();
foreach (DataRow row in dtexcel.Rows)
{
MPRItemInfo mprIteminfos = new MPRItemInfo();
if (row[1].ToString() != "" && row[2].ToString() != "")
{
string unitname = "";
UnitMaster data = new UnitMaster();
if (!string.IsNullOrEmpty(row[3].ToString()))
{
unitname = row[3].ToString();
}
if (!string.IsNullOrEmpty(unitname))
{
data = entities.UnitMasters.Where(x => x.UnitName == unitname).FirstOrDefault();
}
if (!string.IsNullOrEmpty(row[1].ToString()))
{
mprIteminfos.ItemDescription = row[1].ToString();
}
mprIteminfos.RevisionId = Convert.ToInt32(revisionId);
if (!string.IsNullOrEmpty(row[2].ToString()))
{
mprIteminfos.Quantity = Convert.ToInt32(row[2]);
}
if (!string.IsNullOrEmpty(row[4].ToString()))
{
mprIteminfos.SOLineItemNo = row[4].ToString();
}
if (!string.IsNullOrEmpty(row[5].ToString()))
{
mprIteminfos.TargetSpend = Convert.ToInt32(row[5]);
}
if (!string.IsNullOrEmpty(row[6].ToString()))
{
mprIteminfos.MfgPartNo = row[6].ToString();
}
if (!string.IsNullOrEmpty(row[7].ToString()))
{
mprIteminfos.MfgModelNo = row[7].ToString();
}
if (!string.IsNullOrEmpty(row[8].ToString()))
{
mprIteminfos.ReferenceDocNo = row[8].ToString();
}
if (!string.IsNullOrEmpty(row[9].ToString()))
{
mprIteminfos.YGSMaterialCode = row[9].ToString();
}
if (!string.IsNullOrEmpty(row[11].ToString()))
{
mprIteminfos.WBS = row[11].ToString();
}
if (!string.IsNullOrEmpty(row[10].ToString()))
{
mprIteminfos.ProjectDefinition = row[10].ToString();
}
if (!string.IsNullOrEmpty(row[12].ToString()))
{
mprIteminfos.SystemModel = row[12].ToString();
}
if (data != null)
{
mprIteminfos.UnitId = data.UnitId;
}
if (row[9].ToString() == "")
{
mprIteminfos.Itemid = "NewItem";
}
else
{
mprIteminfos.Itemid = row[9].ToString();
if (row[9].ToString() == "NewItem" || row[9].ToString() == "0000")
{
mprIteminfos.Itemid = "NewItem";
}
}
this._mprBusenessAcess.addMprItemInfo(mprIteminfos);
//entities.MPRItemInfoes.Add(mprIteminfos);
//entities.SaveChanges();
}
iSucceRows++;
}
//string unitname = row["UnitId"].ToString();
//var data = entities.UnitMasters.Where(x => x.UnitName == unitname).FirstOrDefault();
//MPRItemInfo mprIteminfos = new MPRItemInfo();
//if (row["ItemDescription"].ToString() != "" && row["Quantity"].ToString() != "")
//{
// mprIteminfos.ItemDescription = row["ItemDescription"].ToString();
// mprIteminfos.RevisionId = Convert.ToInt32(revisionId);
// mprIteminfos.Quantity = Convert.ToInt32(row["Quantity"]);
// mprIteminfos.SOLineItemNo = row["SOLineItemNo"].ToString();
// mprIteminfos.TargetSpend = Convert.ToInt32(row["TargetSpend"]);
// mprIteminfos.MfgPartNo = row["MfgPartNo"].ToString();
// mprIteminfos.MfgModelNo = row["MfgModelNo"].ToString();
// mprIteminfos.ReferenceDocNo = row["ReferenceDocNo"].ToString();
// if(data!=null)
// mprIteminfos.UnitId = data.UnitId;
// if (row["YGSMaterialCode"].ToString() == "")
// mprIteminfos.Itemid = "0000";
// else
// {
// mprIteminfos.Itemid = row["YGSMaterialCode"].ToString();
// if (row["YGSMaterialCode"].ToString() == "NewItem")
// mprIteminfos.Itemid = "0000";
// }
// this._mprBusenessAcess.addMprItemInfo(mprIteminfos);
// //entities.MPRItemInfoes.Add(mprIteminfos);
// //entities.SaveChanges();
//}
//iSucceRows++;
//}
int succRecs = iSucceRows;
}
catch (Exception e)
{
log.ErrorMessage("MPRController", "uploadExcel", e.Message.ToString());
return(Ok(e));
}
}
return(Ok(parsedFileName));
}
public IHttpActionResult addMprItemInfo([FromBody] MPRItemInfo mprIteminfos)
{
return(Ok(this._mprBusenessAcess.addMprItemInfo(mprIteminfos)));
}
