//建立異動單 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); }