Exemplo n.º 1
0
        //建立異動單
        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);
        }
Exemplo n.º 2
0
        /// <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);
            }
        }
Exemplo n.º 3
0
        /// <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);
        }