//建立異動單
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);
}
/// <summary>
/// 依據成本異動單建立詢價單
/// </summary>
public string createInquiryOrderByChangeForm(string formId, SYS_USER u)
{
//取得異動單資料
getChangeOrderForm(formId);
log.Info("create new [PLAN_SUP_INQUIRY] from CostChange Order= " + formId);
string sno_key = "PC";
SerialKeyService snoservice = new SerialKeyService();
using (var context = new TopmepEntities())
{
//1.取得異動單相關資訊
//2,建立表頭
PLAN_SUP_INQUIRY pf = new PLAN_SUP_INQUIRY();
pf.INQUIRY_FORM_ID = snoservice.getSerialKey(sno_key);
pf.PROJECT_ID = form.PROJECT_ID;
pf.FORM_NAME = "(成本異動)" + form.REMARK_ITEM;
//聯絡人基本資料
pf.OWNER_NAME = u.USER_NAME;
pf.OWNER_EMAIL = u.EMAIL;
pf.OWNER_TEL = u.TEL + "-" + u.TEL_EXT;
pf.OWNER_FAX = u.FAX;
pf.CREATE_ID = u.USER_ID;
pf.CREATE_DATE = DateTime.Now;
context.PLAN_SUP_INQUIRY.Add(pf);
int i = context.SaveChanges();
log.Info("plan form id = " + pf.INQUIRY_FORM_ID);
//3建立詢價單明細
string sql = @"INSERT INTO PLAN_SUP_INQUIRY_ITEM
(INQUIRY_FORM_ID,PLAN_ITEM_ID,ITEM_ID,ITEM_DESC,ITEM_UNIT,ITEM_QTY,ITEM_UNIT_PRICE,ITEM_REMARK)
SELECT @InquiryFormId INQUIRY_FORM_ID,PLAN_ITEM_ID,ITEM_ID,ITEM_DESC,ITEM_UNIT,ITEM_QUANTITY ITEM_QTY,ITEM_UNIT_COST ITEM_UNICE_PRICE,ITEM_REMARK
FROM PLAN_COSTCHANGE_ITEM WHERE FORM_ID=@formId";
log.Info("sql =" + sql);
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("InquiryFormId", pf.INQUIRY_FORM_ID));
parameters.Add(new SqlParameter("formId", formId));
i = context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
//4.將成本異動單更新相關的詢價單資料
sql = @"UPDATE PLAN_COSTCHANGE_FORM SET INQUIRY_FORM_ID=@InquiryFormId,
MODIFY_USER_ID = @userId, MODIFY_DATE = @modifyDate WHERE FORM_ID = @formId; ";
log.Info("sql =" + sql);
parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("InquiryFormId", pf.INQUIRY_FORM_ID));
parameters.Add(new SqlParameter("formId", formId));
parameters.Add(new SqlParameter("userId", u.USER_ID));
parameters.Add(new SqlParameter("modifyDate", DateTime.Now));
i = context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
return(pf.INQUIRY_FORM_ID);
}
}
/// <summary>
/// 建立估驗單與對應的明細資料
/// </summary>
public void createEstimationOrder(PLAN_ESTIMATION_FORM form,
List <PLAN_ESTIMATION_HOLDPAYMENT> lstHoldPayment,
List <PLAN_ESTIMATION_PAYMENT_TRANSFER> listTransferPayment,
List <PLAN_ESTIMATION_INVOICE> listInvoice,
string prid_s, string prid_e)
{
SerialKeyService snoservice = new SerialKeyService();
form.EST_FORM_ID = snoservice.getSerialKey(sno_key);
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectId", form.PROJECT_ID));
parameters.Add(new SqlParameter("contractId", form.CONTRACT_ID));
//parameters.Add(new SqlParameter("EST_FORM_ID", form.EST_FORM_ID));
parameters.Add(new SqlParameter("prid_s", prid_s));
parameters.Add(new SqlParameter("prid_e", prid_e));
StringBuilder sb = new StringBuilder(sql4Est);
using (var context = new TopmepEntities())
{
//1,建立主檔
context.PLAN_ESTIMATION_FORM.Add(form);
//2.建立驗收單關聯
string sql4ReceiveOrder = @"
INSERT INTO PLAN_ESTIMATION2PURCHASE
select DISTINCT esOrder.PR_ID AS PR_ID,@EST_FORM_ID AS EXT_FORM_ID
from contract c
inner join esOrder
on c.PLAN_ITEM_ID=esOrder.PLAN_ITEM_ID
where c.PROJECT_ID=@projectId
and c.INQUIRY_FORM_ID=@contractId
and esOrder.PR_ID BETWEEN @prid_s AND @prid_e
";
string sql = sb.Append(sql4ReceiveOrder).Replace("@EST_FORM_ID", "'" + form.EST_FORM_ID + "'").ToString();
log.Debug(sql);
context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
//3.建立明細
//3.1 更新表單檔頭金額
string sql4Detail = @"
INSERT INTO PLAN_ESTIMATION_ITEM
select
@EST_FORM_ID AS EXT_FORM_ID,
c.PLAN_ITEM_ID,
SUM(esOrder.RECEIPT_QTY) as EST_QTY,
1 as EST_RATIO,
SUM(RECEIPT_QTY) *c.ITEM_UNIT_PRICE as EST_AMOUNT,
NULL AS REMARK
from contract c
inner join esOrder
on c.PLAN_ITEM_ID=esOrder.PLAN_ITEM_ID
where c.PROJECT_ID=@projectId
and c.INQUIRY_FORM_ID=@contractId
and esOrder.PR_ID BETWEEN @prid_s AND @prid_e
GROUP BY c.PLAN_ITEM_ID,c.ITEM_UNIT_PRICE;
UPDATE
PLAN_ESTIMATION_FORM
SET PAID_AMOUNT =
(SELECT SUM(EST_AMOUNT) FROM PLAN_ESTIMATION_ITEM WHERE EST_FORM_ID=@EST_FORM_ID)
WHERE EST_FORM_ID=@EST_FORM_ID;
";
sb = new StringBuilder(sql4Est);
sql = sb.Append(sql4Detail).Replace("@EST_FORM_ID", "'" + form.EST_FORM_ID + "'").ToString();
log.Debug(sql);
context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
//3.1 付款憑證資料
modifyEstimationInvoice(form, listInvoice, context);
//4.建立代付資料
modifyEstimationHold(form, lstHoldPayment, context);
log.Debug("get Hold4Payment=" + JsonConvert.SerializeObject(lstHoldPayment).ToString());
//5.建立代付扣款明細
modifyEstimationTransfer(form, listTransferPayment, context);
log.Debug("get TransferPayment=" + JsonConvert.SerializeObject(listTransferPayment).ToString());
context.SaveChanges();
}
//6.建立彙整金額
SumEstimationForm(form);
}