//由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 + ")!");
}
