public void SendMailSchedule() { log.Info("SendMailSchedule start !!" + DateTime.Now); List <SYS_MESSAGE> lst = getTask(); EMailService mailservice = new EMailService(); foreach (SYS_MESSAGE m in lst) { using (var context = new TopmepEntities()) { try { log.Info("send msg=" + m.MSG_ID); if (mailservice.SendMailByGmail(m.FROM_ADDRESS, m.DISPLAY_NAME, m.MAIL_LIST, m.BCC_MAIL_LIST, m.SUBJECT, m.MSG_BODY, null)) { SYS_MESSAGE doneM = context.SYS_MESSAGE.Find(m.MSG_ID); doneM.STATUS = "DONE"; context.SaveChanges(); } } catch (Exception ex) { log.Error(ex.Message + ":" + ex.StackTrace); } } return; } }
public void import2Table() { if (null != lstTask) { using (var context = new TopmepEntities()) { //1.清除所有任務 string sql = "DELETE FROM PLAN_TASK WHERE PROJECT_ID=@projectid"; int i = context.Database.ExecuteSqlCommand(sql, new SqlParameter("projectid", project_id)); log.Debug("Remove Exist Task for projectid=" + project_id); //2.匯入任務 foreach (PLAN_TASK pt in lstTask) { if (pt.TASK_NAME != null) { context.PLAN_TASK.Add(pt); } else { log.Warn("task name is null:" + pt.PRJ_UID + ",id=" + pt.PRJ_ID); } } i = context.SaveChanges(); log.Info("import task count=" + i); } } }
public int MigratePrice(List <ProjectCompareData> lstData) { int i = 0; try { using (var context = new TopmepEntities()) { string sql = "UPDATE TND_PROJECT_ITEM SET ITEM_UNIT_PRICE=@price WHERE PROJECT_ID=@projectid AND SYSTEM_MAIN=@systemMain AND ISNULL(SYSTEM_SUB,'*')=@systemSub AND ITEM_DESC=@itemDesc;"; log.Info("sql=" + sql); foreach (ProjectCompareData data in lstData) { //item.SOURCE_PROJECT_ID + '|' + item.SOURCE_SYSTEM_MAIN + '|' + item.SOURCE_SYSTEM_SUB + '|' + item.SRC_UNIT_PRICE + '|' + item.TARGET_PROJECT_ID + '|' + item.SOURCE_ITEM_DESC;} var parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("price", data.SRC_UNIT_PRICE)); log.Debug("price=" + data.SRC_UNIT_PRICE); parameters.Add(new SqlParameter("projectid", data.TARGET_PROJECT_ID)); log.Debug("TARGET_PROJECT_ID=" + data.TARGET_PROJECT_ID); parameters.Add(new SqlParameter("systemMain", data.SOURCE_SYSTEM_MAIN)); log.Debug("SOURCE_SYSTEM_MAIN=" + data.SOURCE_SYSTEM_MAIN); parameters.Add(new SqlParameter("systemSub", data.SOURCE_SYSTEM_SUB)); log.Debug("SOURCE_SYSTEM_SUB=" + data.SOURCE_SYSTEM_SUB); parameters.Add(new SqlParameter("itemDesc", data.TARGET_ITEM_DESC)); log.Debug("TARGET_ITEM_DESC=" + data.TARGET_ITEM_DESC); i = i + context.Database.ExecuteSqlCommand(sql, parameters.ToArray()); context.SaveChanges(); } } } catch (Exception ex) { log.Error(ex.StackTrace); } return(i); }
/* 依 KEY_ID 取得新序號(String)*/ public string getSerialKey(string keyId) { log.Debug("get new id by key"); SYS_KEY_SERIAL SnKey = null; String sKey = null; using (var context = new TopmepEntities()) { //1.取得現有序號值 string esql = @"SELECT * FROM SYS_KEY_SERIAL AS serialKey WHERE serialKey.KEY_ID=@keyId"; SnKey = context.SYS_KEY_SERIAL.SqlQuery(esql, new SqlParameter("keyId", keyId)).First(); log.Debug("get new key :" + SnKey.KEY_ID + "=" + SnKey.KEY_NO); sKey = SnKey.KEY_NO.ToString().Trim(); //2.將序號補0 while ((sKey.Length + +SnKey.PREFIX.Length) < SnKey.KEY_LEN) { sKey = "0" + sKey; } SnKey.KEY_NO = SnKey.KEY_NO + 1; int i = context.SaveChanges(); log.Info("Update SerialKey: Status =" + i); sKey = SnKey.PREFIX + sKey; log.Info("New KEY :" + SnKey.KEY_ID + "=" + sKey); } return(sKey); }
//建立異動單 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 long addDepartment(ENT_DEPARTMENT dep) { log.Info("create new Department "); using (var context = new TopmepEntities()) { //2.取得供應商編號 context.ENT_DEPARTMENT.AddOrUpdate(dep); int i = context.SaveChanges(); log.Debug("Add dep=" + i); } return(dep.DEP_ID); }
/// <summary> /// 刪除部門資料 /// </summary> /// <param name="depId"></param> public void delDepartment(long depId) { using (var context = new TopmepEntities()) { //2.取得 ENT_DEPARTMENT d = context.ENT_DEPARTMENT.Find(depId); log.Info("del Department =" + d.DEPT_NAME + "," + d.DEP_ID); context.ENT_DEPARTMENT.Remove(d); int i = context.SaveChanges(); log.Debug("remove dep=" + i); } }
public void updateTypeManageModel(REF_TYPE_MAIN mainType, List <REF_TYPE_SUB> lstSubType) { using (var context = new TopmepEntities()) { //修改九宮格內容 context.REF_TYPE_MAIN.AddOrUpdate(mainType); int i = 0; i = context.SaveChanges(); log.Debug("Modify MainType :" + i); string sql = "DELETE FROM REF_TYPE_SUB WHERE TYPE_CODE_ID=@typecodeid"; log.Debug("Remove SubType=" + sql + ",TypeCode=" + mainType.TYPE_CODE_1 + mainType.TYPE_CODE_2); context.Database.ExecuteSqlCommand(sql, new SqlParameter("typecodeid", mainType.TYPE_CODE_1 + mainType.TYPE_CODE_2)); foreach (REF_TYPE_SUB subType in lstSubType) { log.Debug("subTypeId=" + subType.SUB_TYPE_ID + ",mainTypeId=" + subType.TYPE_CODE_ID + ",subTypeCode=" + subType.SUB_TYPE_CODE + ",subTypeDesc=" + subType.TYPE_DESC); subType.CREATE_DATE = System.DateTime.Now; context.REF_TYPE_SUB.Add(subType); } i = context.SaveChanges(); log.Debug("Modify MainSub :" + i); } }
/// <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 modifyEstimationOrder(PLAN_ESTIMATION_FORM form, List <PLAN_ESTIMATION_HOLDPAYMENT> lstHoldPayment, List <PLAN_ESTIMATION_PAYMENT_TRANSFER> listTransferPayment, List <PLAN_ESTIMATION_INVOICE> listInvoice) { using (var context = new TopmepEntities()) { modifyEstimationForm(context, form); modifyEstimationInvoice(form, listInvoice, context); modifyEstimationHold(form, lstHoldPayment, context); modifyEstimationTransfer(form, listTransferPayment, context); context.SaveChanges(); } //.建立彙整金額 SumEstimationForm(form); }
//匯入工地預算 public int refreshSiteBudget(List <PLAN_SITE_BUDGET> items) { int i = 0; log.Info("refreshSiteBudgetItem = " + items.Count); //2.將Excel 資料寫入 using (var context = new TopmepEntities()) { foreach (PLAN_SITE_BUDGET item in items) { context.PLAN_SITE_BUDGET.Add(item); } i = context.SaveChanges(); } log.Info("add PLAN_SITE_BUDGET count =" + i); return(i); }
public void createPOMessage(SYS_USER u, PLAN_PURCHASE_REQUISITION pr) { //mail to 業管 // System.Web.Script.Serialization.JavaScriptSerializer objSerializer = new System.Web.Script.Serialization.JavaScriptSerializer(); // string userJson = objSerializer.Serialize(u); // log.Debug("sender :" + userJson); //定義Email 樣板 StringBuilder strTemp = new StringBuilder("{1} 申購單已完成採購!!"); StringBuilder strTempBody = new StringBuilder("{1} 申購單已完成採購{2}。 <br/> 系統發出,請勿回覆!!"); // SYS_MESSAGE m = new SYS_MESSAGE(); m.FROM_ADDRESS = u.EMAIL; m.DISPLAY_NAME = u.USER_NAME; UserService s = new UserService(); //TODO:加入通知申請人 List <SYS_USER> lstTarget = s.GetProjectUser(pr.PROJECT_ID, "工地主任"); strTemp.Replace("{1}", pr.PROJECT_ID); m.MAIL_LIST = getMailList(lstTarget); if (m.MAIL_LIST == "") { log.Error(pr.PR_ID + " Have no mail list!!"); return; } m.SUBJECT = strTemp.ToString(); strTempBody.Replace("{1}", pr.PROJECT_ID); strTempBody.Replace("{2}", "採購單號為:<a href='" + system_url + "'>" + pr.PR_ID + "</a>"); m.MSG_BODY = strTempBody.ToString(); log.Debug("email body:" + m.MSG_BODY); m.CREATE_ID = u.USER_ID; m.CREATE_DATE = DateTime.Now; //決定發送時間 m.SEND_TIME = DateTime.Now; int i = 0; using (var context = new TopmepEntities()) { context.SYS_MESSAGE.Add(m); i = context.SaveChanges(); } }
/*增加序號記錄欄位*/ public bool addSerialKey(SYS_KEY_SERIAL serialKey) { bool status = false; using (var context = new TopmepEntities()) { context.SYS_KEY_SERIAL.Add(serialKey); //_db.AddToSYS_KEY_SERIAL(serialKey); int i = context.SaveChanges(); log.Debug("Add SerialKey : SerialKey=" + serialKey.KEY_ID + ",status=" + i); if (i > 0) { status = true; } ; } return(status); }
public int updateProfile(SYS_USER u) { int i = 1; using (var context = new TopmepEntities()) { try { context.SYS_USER.AddOrUpdate(t => t.USER_ID, u); i = context.SaveChanges(); } catch (Exception ex) { log.Error(ex.StackTrace); i = -1; } } return(i); }
/// <summary> /// 增加借款還款紀錄 /// </summary> /// <param name="loanTransaction"></param> /// <returns></returns> public int addBankLoanTransaction(List <FIN_LOAN_TRANACTION> loanTransaction) { int i = 0; using (var context = new TopmepEntities()) { try { context.FIN_LOAN_TRANACTION.AddRange(loanTransaction); i = context.SaveChanges(); log.Info("new bank loan transaction record=" + loanTransaction.Count); } catch (Exception ex) { log.Error(ex.Message + ":StackTrace=" + ex.StackTrace); } } return(i); }
//新增帳號資料 public int addNewUser(SYS_USER u) { int i = 0; using (var context = new TopmepEntities()) { try { context.SYS_USER.AddOrUpdate(u); i = context.SaveChanges(); } catch (Exception e) { log.Error("add new user id fail:" + e.ToString()); log.Error(e.StackTrace); message = e.Message; } } return(i); }
//新增貸款帳戶資料 public int addBankLoan(FIN_BANK_LOAN bankloan) { int i = 0; using (var context = new TopmepEntities()) { try { context.FIN_BANK_LOAN.AddOrUpdate(bankloan); i = context.SaveChanges(); log.Info("new bank loan record=" + bankloan.ACCOUNT_NAME); } catch (Exception ex) { log.Error(ex.Message + ":StackTrace=" + ex.StackTrace); } } log.Info("add bankloan count =" + i); return(i); }
public int updateLoanTransactionItem(FIN_LOAN_TRANACTION item) { int i = 0; using (var context = new TopmepEntities()) { try { context.FIN_LOAN_TRANACTION.AddOrUpdate(item); i = context.SaveChanges(); } catch (Exception e) { log.Error("update loan transaction item fail:" + e.ToString()); log.Error(e.StackTrace); message = e.Message; } } return(i); }
//新增異動單品項 public int addChangeOrderItem(PLAN_COSTCHANGE_ITEM item) { int i = 0; //2.將資料寫入 using (var context = new TopmepEntities()) { try { log.Debug("create COSTCHANGE_FORM:" + item.FORM_ID); context.PLAN_COSTCHANGE_ITEM.Add(item); i = context.SaveChanges(); } catch (Exception ex) { log.Error(ex.Message + ":" + ex.StackTrace); } } return(i); }
//新增角色 public int addOrUpdateRole(SYS_ROLE role) { int i = 0; using (var context = new TopmepEntities()) { try { context.SYS_ROLE.AddOrUpdate(role); i = context.SaveChanges(); } catch (Exception e) { log.Error("add new role fail:" + e.ToString()); log.Error(e.StackTrace); message = e.Message; } } return(i); }
//增加銀行帳戶 public int addBankInfo(FIN_BANK_ACCOUNT account) { int i = 0; using (var context = new TopmepEntities()) { try { context.FIN_BANK_ACCOUNT.Add(account); i = context.SaveChanges(); log.Info("new bank account record=" + account.BANK_ACCOUNT_ID + ",initial amount=" + account.CUR_AMOUNT + ",curDate=" + account.CUR_DATE); } catch (Exception ex) { log.Error(ex.Message + ":StackTrace=" + ex.StackTrace); } } log.Info("add bankAccount count =" + i); return(i); }
//新增財務項目資料 public int addNewSubject(FIN_SUBJECT s) { int i = 0; using (var context = new TopmepEntities()) { try { context.FIN_SUBJECT.AddOrUpdate(s); i = context.SaveChanges(); } catch (Exception e) { log.Error("add new subject id fail:" + e.ToString()); log.Error(e.StackTrace); message = e.Message; } } return(i); }
//修正間接成本資料 public void modifyIndirectCost(string projectId, List <PLAN_INDIRECT_COST> items) { using (var context = new TopmepEntities()) { ///逐筆更新資料 string sql = "UPDATE PLAN_INDIRECT_COST SET COST = @cost, MODIFY_ID = @modifyId, MODIFY_DATE = @modifyDate, NOTE = ISNULL(Note,'') + @Note WHERE PROJECT_ID = @projectId AND FIELD_ID = @fieldId"; log.Debug("sql=" + sql + ",projectid=" + projectId); foreach (PLAN_INDIRECT_COST it in items) { var parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("projectId", projectId)); parameters.Add(new SqlParameter("fieldId", it.FIELD_ID)); parameters.Add(new SqlParameter("cost", it.COST)); parameters.Add(new SqlParameter("modifyId", it.MODIFY_ID)); parameters.Add(new SqlParameter("modifyDate", DateTime.Now)); parameters.Add(new SqlParameter("Note", it.NOTE)); context.Database.ExecuteSqlCommand(sql, parameters.ToArray()); log.Debug("sql=" + sql + ",projectid=" + projectId); } ///將新資料存入 context.SaveChanges(); } }
//移除異動單品項 public int delChangeOrderItem(long itemid) { int i = 0; //2.將品項資料刪除 using (var context = new TopmepEntities()) { try { string sql = "DELETE FROM PLAN_COSTCHANGE_ITEM WHERE ITEM_UID=@itemUid;"; var parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("itemUid", itemid)); log.Debug("Delete COSTCHANGE_ITEM:" + itemid); context.Database.ExecuteSqlCommand(sql, parameters.ToArray()); i = context.SaveChanges(); } catch (Exception ex) { log.Error(ex.Message + ":" + ex.StackTrace); } } return(i); }
//更新權限資料 public int updatePrivilege(string roleid, string[] functions) { int i = 0; using (var context = new TopmepEntities()) { //1.移除該角色所有權限 string sql = "DELETE FROM SYS_PRIVILEGE WHERE ROLE_ID=@roleid;"; i = context.Database.ExecuteSqlCommand(sql, new SqlParameter("roleid", roleid)); log.Info("Remove privilege count=" + i); //2.逐一加入授權資料 for (int j = 0; j < functions.Length; j++) { SYS_PRIVILEGE p = new SYS_PRIVILEGE(); p.PRIVILEGE_ID = roleid + "-" + functions[j]; p.ROLE_ID = roleid; p.FUNCTION_ID = functions[j]; context.SYS_PRIVILEGE.Add(p); } i = context.SaveChanges(); log.Info("create privlilege data count:" + i); } return(i); }
//建立間接成本資料 public void createIndirectCost(string projectId, string userid) { List <SYS_PARA> lstItem = SystemParameter.getSystemPara("IndirectCostItem"); List <PLAN_INDIRECT_COST> lstIndirectCostItem = new List <PLAN_INDIRECT_COST>(); //取得合約金額 CostInfo.Revenue = getPlanRevenueById(projectId); //取得間接成本項目 foreach (SYS_PARA p in lstItem) { PLAN_INDIRECT_COST it = new PLAN_INDIRECT_COST(); it.PROJECT_ID = projectId; it.FIELD_ID = p.FIELD_ID; it.FIELD_DESC = p.VALUE_FIELD; it.PERCENTAGE = decimal.Parse(p.KEY_FIELD); it.MODIFY_ID = userid; it.MODIFY_DATE = DateTime.Now; // System.Convert.ToDoublSystem.Math.Round(1.235, 2, MidpointRounding.AwayFromZero) it.COST = Convert.ToDecimal(Math.Round(Convert.ToDouble(CostInfo.Revenue.PLAN_REVENUE * decimal.Parse(p.KEY_FIELD) / 100), 0, MidpointRounding.AwayFromZero)); log.Debug(p.VALUE_FIELD + " Indirect Cost=" + it.COST + ",per=" + p.KEY_FIELD); lstIndirectCostItem.Add(it); } using (var context = new TopmepEntities()) { ///刪除現有資料 string sql = "DELETE FROM PLAN_INDIRECT_COST WHERE PROJECT_ID=@projectId"; log.Debug("sql=" + sql + ",projectid=" + projectId); var parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("projectId", projectId)); context.Database.ExecuteSqlCommand(sql, parameters.ToArray()); log.Debug("sql=" + sql + ",projectid=" + projectId); ///將新資料存入 context.PLAN_INDIRECT_COST.AddRange(lstIndirectCostItem); context.SaveChanges(); } }
/// <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); }