//由Excel 讀取資料 public void getDataFromExcel(string filpath, string projectId, string formId) { InitializeWorkbook(filpath); SetOpSheet("異動單"); //讀取專案資料 IRow row = sheet.GetRow(1); project = new TND_PROJECT(); project.PROJECT_ID = projectId; project.PROJECT_NAME = row.Cells[2].ToString(); logger.Debug("project id=" + project.PROJECT_ID + ",project name=" + project.PROJECT_NAME); //取得異動單資料 row = sheet.GetRow(2); costChangeForm = new PLAN_COSTCHANGE_FORM(); costChangeForm.PROJECT_ID = project.PROJECT_ID; costChangeForm.FORM_ID = formId; //檢查是否為新異動單 if (null == costChangeForm.FORM_ID || costChangeForm.FORM_ID == "") { costChangeForm.CREATE_USER_ID = user.USER_ID; costChangeForm.CREATE_DATE = DateTime.Now; } else { costChangeForm.MODIFY_USER_ID = user.USER_ID; costChangeForm.MODIFY_DATE = DateTime.Now; } //未送審前狀態不變 costChangeForm.STATUS = "新建立"; costChangeForm.REMARK_ITEM = row.Cells[3].ToString(); logger.Debug("FORM id=" + costChangeForm.FORM_ID); ConvertExcel2Object(); }
//建立異動單 public string createChangeOrder(PLAN_COSTCHANGE_FORM form, List <PLAN_COSTCHANGE_ITEM> lstItem) { //1.新增成本異動單 SerialKeyService skService = new SerialKeyService(); form.FORM_ID = skService.getSerialKey(strSerialNoKey); form.STATUS = "10"; PLAN_ITEM pi = null; int i = 0; //2.將資料寫入 using (var context = new TopmepEntities()) { context.PLAN_COSTCHANGE_FORM.Add(form); log.Debug("create COSTCHANGE_FORM:" + form.FORM_ID); foreach (PLAN_COSTCHANGE_ITEM item in lstItem) { if (null != item.PLAN_ITEM_ID && "" != item.PLAN_ITEM_ID) { log.Debug("Object in contract :" + item.PLAN_ITEM_ID); pi = context.PLAN_ITEM.SqlQuery("SELECT * FROM PLAN_ITEM WHERE PLAN_ITEM_ID=@itemId", new SqlParameter("itemId", item.PLAN_ITEM_ID)).First(); //補足標單品項欄位 if (pi != null && item.ITEM_ID == null) { item.ITEM_ID = pi.ITEM_ID; } if (pi != null && item.ITEM_DESC == null) { item.ITEM_DESC = pi.ITEM_DESC; } if (pi != null && item.ITEM_UNIT == null) { item.ITEM_UNIT = pi.ITEM_UNIT; } if (pi != null && item.ITEM_UNIT_PRICE == null) { item.ITEM_UNIT_PRICE = pi.ITEM_UNIT_PRICE; } if (pi != null && item.ITEM_UNIT_COST == null) { item.ITEM_UNIT_COST = pi.ITEM_UNIT_COST; } } item.FORM_ID = form.FORM_ID; item.PROJECT_ID = form.PROJECT_ID; context.PLAN_COSTCHANGE_ITEM.Add(item); item.CREATE_USER_ID = form.CREATE_USER_ID; item.CREATE_DATE = form.CREATE_DATE; log.Debug("create COSTCHANGE_ITEM:" + item.PLAN_ITEM_ID); } i = context.SaveChanges(); } log.Info("add CostChangeItem count =" + i); return(form.FORM_ID); }
//取得單一異動單資料 public PLAN_COSTCHANGE_FORM getChangeOrderForm(string formId) { //2.取得異動單資料 using (var context = new TopmepEntities()) { log.Debug("change form Id:" + formId); string sql = "SELECT * FROM PLAN_COSTCHANGE_FORM WHERE FORM_ID=@formId"; var parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("formId", formId)); log.Debug("SQL:" + sql); form = context.PLAN_COSTCHANGE_FORM.SqlQuery(sql, parameters.ToArray()).First(); lstItem = form.PLAN_COSTCHANGE_ITEM.ToList(); } project = getProject(form.PROJECT_ID); return(form); }
public void createExcel(TND_PROJECT project, PLAN_COSTCHANGE_FORM form, List <PLAN_COSTCHANGE_ITEM> lstItem) { InitializeWorkbook(); SetOpSheet("異動單"); //填寫專案資料 IRow row = sheet.GetRow(1); row.Cells[1].SetCellValue(project.PROJECT_ID); row.Cells[2].SetCellValue(project.PROJECT_NAME); //填入異動單資料 row = sheet.GetRow(2); row.Cells[1].SetCellValue(form.FORM_ID); row.Cells[3].SetCellValue(form.REMARK_ITEM); //填入明細資料 ConverObjectTotExcel(lstItem, 4); //令存新檔至專案所屬目錄 outputFile = strUploadPath + "\\" + project.PROJECT_ID + "\\" + project.PROJECT_ID + "-" + form.FORM_ID + "_CostChange.xlsx"; logger.Debug("export excel file=" + outputFile); var file = new FileStream(outputFile, FileMode.Create); logger.Info("output file=" + file.Name); hssfworkbook.Write(file); file.Close(); }
//更新異動單資料 public string updateChangeOrder(PLAN_COSTCHANGE_FORM form, List <PLAN_COSTCHANGE_ITEM> lstItem) { int i = 0; string sqlForm = @"UPDATE PLAN_COSTCHANGE_FORM SET REASON_CODE=@Reasoncode,METHOD_CODE=@methodCode, REMARK_ITEM=@RemarkItem,REMARK_QTY=Null,REMARK_PRICE=Null,REMARK_OTHER=Null, MODIFY_USER_ID=@userId,MODIFY_DATE=@modifyDate WHERE FORM_ID=@formId;"; string sqlItem = @"UPDATE PLAN_COSTCHANGE_ITEM SET ITEM_DESC=@itemdesc,ITEM_UNIT=@unit,ITEM_UNIT_PRICE=@unitPrice,ITEM_UNIT_COST=@unitCost, ITEM_QUANTITY=@Qty,ITEM_REMARK=@remark,TRANSFLAG=@transFlag,MODIFY_USER_ID=@userId,MODIFY_DATE=@modifyDate WHERE ITEM_UID=@uid"; //2.將資料寫入 using (var context = new TopmepEntities()) { try { //更新表頭 context.Database.BeginTransaction(); var parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("Reasoncode", form.REASON_CODE)); parameters.Add(new SqlParameter("methodCode", form.METHOD_CODE)); parameters.Add(new SqlParameter("RemarkItem", form.REMARK_ITEM)); // parameters.Add(new SqlParameter("RemarkQty", form.REMARK_QTY)); // parameters.Add(new SqlParameter("RemarkPrice", form.REMARK_PRICE)); // parameters.Add(new SqlParameter("RemarkOther", form.REMARK_OTHER)); parameters.Add(new SqlParameter("userId", form.MODIFY_USER_ID)); parameters.Add(new SqlParameter("modifyDate", form.MODIFY_DATE)); parameters.Add(new SqlParameter("formId", form.FORM_ID)); i = context.Database.ExecuteSqlCommand(sqlForm, parameters.ToArray()); log.Debug("create COSTCHANGE_FORM:" + sqlForm); foreach (PLAN_COSTCHANGE_ITEM item in lstItem) { parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("itemdesc", item.ITEM_DESC)); parameters.Add(new SqlParameter("unit", item.ITEM_UNIT)); if (item.ITEM_UNIT_PRICE != null) { parameters.Add(new SqlParameter("unitPrice", item.ITEM_UNIT_PRICE)); } else { parameters.Add(new SqlParameter("unitPrice", DBNull.Value)); } if (item.ITEM_UNIT_COST != null) { parameters.Add(new SqlParameter("unitCost", item.ITEM_UNIT_COST)); } else { parameters.Add(new SqlParameter("unitCost", DBNull.Value)); } if (item.ITEM_QUANTITY == null) { parameters.Add(new SqlParameter("Qty", DBNull.Value)); } else { parameters.Add(new SqlParameter("Qty", item.ITEM_QUANTITY)); } parameters.Add(new SqlParameter("transFlag", item.TRANSFLAG)); parameters.Add(new SqlParameter("remark", item.ITEM_REMARK)); parameters.Add(new SqlParameter("userId", form.MODIFY_USER_ID)); parameters.Add(new SqlParameter("modifyDate", form.MODIFY_DATE)); parameters.Add(new SqlParameter("uid", item.ITEM_UID)); i = i + context.Database.ExecuteSqlCommand(sqlItem, parameters.ToArray()); } context.Database.CurrentTransaction.Commit(); } catch (Exception ex) { context.Database.CurrentTransaction.Rollback(); log.Error(ex.Message + ":" + ex.StackTrace); return("資料更新失敗!!(" + ex.Message + ")"); } } return("資料更新成功(" + i + ")!"); }