Exemplo n.º 1
0
        //TODO 取得點工相關資料 for 估驗單
        public ContractModels getContract4TempWork(string projectId, string rptStartId, string rtpEndId, string supplierId, string chargeId)
        {
            ContractModels c = new ContractModels();

            PLAN_ESTIMATION_FORM form = new PLAN_ESTIMATION_FORM();

            c.planEST = form;
            //1.取得專案資料
            logger.Debug("get project by project_id=" + projectId);
            c.project = getProjectById(projectId);
            //2.取得合約資料?? 需要點工的單價資料

            logger.Debug("get getContract4TempWork by project_id=" + projectId + "," + rptStartId + "," + rtpEndId);
            c.EstimationItems = getEstimationList4TempWorker(projectId, rptStartId, rtpEndId, supplierId, chargeId);
            //3.取得供應商資料
            logger.Debug("get Supplier by SUPPLIER_ID=" + c.EstimationItems.First().SUPPLIER_ID);
            c.supplier = getSupplierInfo(supplierId.Substring(0, 8));
            //4.補上代扣資料
            List <PLAN_ESTIMATION_HOLDPAYMENT> holdPayment = new List <PLAN_ESTIMATION_HOLDPAYMENT>();
            PLAN_ESTIMATION_HOLDPAYMENT        p           = new PLAN_ESTIMATION_HOLDPAYMENT();

            p.SUPPLIER_ID = chargeId;
            //p.HOLD_AMOUNT = 0;
            p.REMARK = "點工";
            holdPayment.Add(p);
            c.EstimationHoldPayments = holdPayment;
            return(c);
        }
Exemplo n.º 2
0
        public ContractModels getContract(string projectId, string contractid, string prid_s, string prid_e, string isWage)
        {
            ContractModels c = new ContractModels();

            PLAN_ESTIMATION_FORM form = new PLAN_ESTIMATION_FORM();

            c.planEST = form;
            //1.取得專案資料
            logger.Debug("get project by project_id=" + projectId);
            c.project = getProjectById(projectId);
            //2.取得估驗明細資料
            logger.Debug("get EstimationOrder4Expense by project_id=" + contractid + "," + prid_s + "," + prid_e);
            switch (isWage)
            {
            case "Y":
                c.EstimationItems = getEstimationListByDailyReport(projectId, DateTime.Parse(prid_s), DateTime.Parse(prid_e), isWage);
                break;

            case "N":
                c.EstimationItems = getEstimationListByDailyReport(projectId, DateTime.Parse(prid_s), DateTime.Parse(prid_e), isWage);
                break;

            default:
                c.EstimationItems = getEstimationOrder4Expense(projectId, contractid, prid_s, prid_e);
                break;
            }
            //3.取得供應商資料
            logger.Debug("get Supplier by SUPPLIER_ID=" + c.EstimationItems.First().SUPPLIER_ID);
            c.supplier = getSupplierInfo(c.EstimationItems.First().SUPPLIER_ID);
            //4.取得代扣資料
            logger.Debug("get Hold4DeductForm by SUPPLIER_ID=" + c.supplier.SUPPLIER_ID);
            c.Hold4DeductForm = getPaymentTransfer(projectId, c.supplier.SUPPLIER_ID);
            return(c);
        }
Exemplo n.º 3
0
        //取得估驗單主檔
        public PLAN_ESTIMATION_FORM getForm(string formId)
        {
            PLAN_ESTIMATION_FORM form = null;

            using (var context = new topmepEntities())
            {
                form = context.PLAN_ESTIMATION_FORM.Where(f => f.EST_FORM_ID == formId).FirstOrDefault();
            }
            return(form);
        }
Exemplo n.º 4
0
        //修改基本資料
        private static void modifyEstimationForm(topmepEntities context, PLAN_ESTIMATION_FORM form)
        {
            string sql        = @"
UPDATE PLAN_ESTIMATION_FORM
   SET PROJECT_ID = @projectid
      ,CONTRACT_ID = @contractid
      ,PLUS_TAX = @plustax
      ,TAX_AMOUNT = @taxamount
      ,PAYMENT_TRANSFER = @paymenttransfer
      ,PAID_AMOUNT = @paidamount
      ,FOREIGN_PAYMENT = @foreignpayment
      ,RETENTION_PAYMENT = @retentionpayment
      ,REMARK = @remark
      ,SETTLEMENT = @settlement
      ,TYPE = @type
      ,STATUS = @status
      ,TAX_RATIO = @taxration
      ,MODIFY_DATE = getdate()
      ,INVOICE = @invoice
      ,REJECT_DESC = @rejectdesc
      ,PROJECT_NAME = @projectname
      ,PAYEE = @payee
      ,PAYMENT_DATE = @paymentDate
      ,INDIRECT_COST_TYPE = @indirectCostType
 WHERE EST_FORM_ID = @formId
                ";
            var    parameters = new List <SqlParameter>();

            parameters.Add(new SqlParameter("formId", form.EST_FORM_ID));
            parameters.Add(new SqlParameter("projectid", form.PROJECT_ID));
            parameters.Add(new SqlParameter("contractid", form.CONTRACT_ID));
            parameters.Add(new SqlParameter("plustax", form.PLUS_TAX ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("taxamount", form.TAX_AMOUNT ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("paymenttransfer", form.PAYMENT_TRANSFER ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("paidamount", form.PAID_AMOUNT ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("foreignpayment", form.FOREIGN_PAYMENT ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("retentionpayment", form.RETENTION_PAYMENT ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("remark", form.REMARK ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("settlement", form.SETTLEMENT ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("type", form.TYPE ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("status", form.STATUS ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("taxration", form.TAX_RATIO ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("invoice", form.INVOICE ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("rejectdesc", form.REJECT_DESC ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("projectname", form.PROJECT_NAME ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("payee", form.PAYEE ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("paymentDate", form.PAYMENT_DATE ?? (object)DBNull.Value));
            parameters.Add(new SqlParameter("indirectCostType", form.INDIRECT_COST_TYPE ?? (object)DBNull.Value));
            context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
        }
Exemplo n.º 5
0
 /// <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);
 }
Exemplo n.º 6
0
        /// <summary>
        /// 建立代付扣回資料
        /// </summary>
        private static void modifyEstimationTransfer(PLAN_ESTIMATION_FORM form, List <PLAN_ESTIMATION_PAYMENT_TRANSFER> listTransferPayment, topmepEntities context)
        {
            string sql        = "DELETE PLAN_ESTIMATION_PAYMENT_TRANSFER WHERE PAYMENT_FORM_ID=@formId";
            var    parameters = new List <SqlParameter>();

            parameters.Add(new SqlParameter("formId", form.EST_FORM_ID));
            context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
            logger.Debug("sql=" + sql + ",formId=" + form.EST_FORM_ID);

            if (null != listTransferPayment && listTransferPayment.Count > 0)
            {
                foreach (PLAN_ESTIMATION_PAYMENT_TRANSFER trans in listTransferPayment)
                {
                    trans.PAYMENT_FORM_ID = form.EST_FORM_ID;
                    context.PLAN_ESTIMATION_PAYMENT_TRANSFER.Add(trans);
                }
            }
        }
Exemplo n.º 7
0
        /// <summary>
        /// 將估驗單相關金額,加入表頭內
        /// </summary>
        private void SumEstimationForm(PLAN_ESTIMATION_FORM f)
        {
            //計算 : 保留款
            //計算 :預付款 扣回、其他扣款尚未計算
            string sql = @"
 UPDATE PLAN_ESTIMATION_FORM
 SET 
 PAID_AMOUNT=INV_ALL.PAID_AMOUNT-INV_ALL.PAYMENT_TRANSFER ,-- 應付金額(須扣除代付支出)
 TAX_AMOUNT=INV_ALL.TAX_AMOUNT,--營業稅
  PAYMENT_TRANSFER=INV_ALL.PAYMENT_TRANSFER,--代付支出
  PAYMENT_DEDUCTION=INV_ALL.PAYMENT_DEDUCTION,----代付扣回
  RETENTION_PAYMENT=0,--保留款(需另外計算)
  OTHER_PAYMENT=0, -- 其他扣款(需另外計算)
  PREPAY_AMOUNT=0, --預付款(需另外計算)
  FOREIGN_PAYMENT=0 --外勞款(尚無資料)
  FROM 
  (SELECT INV.EST_FORM_ID FORM_ID
,ISNULL(SUM(HOLD.HOLD_AMOUNT),0) PAYMENT_TRANSFER  --代付支出
,SUM(INV.AMOUNT) PAID_AMOUNT  -- 應付金額
,SUM(INV.TAX) TAX_AMOUNT  --營業稅
,ISNULL(SUM(TRF.PAID_AMOUNT),0) PAYMENT_DEDUCTION   --代付扣回
FROM PLAN_ESTIMATION_INVOICE INV
LEFT JOIN PLAN_ESTIMATION_HOLDPAYMENT HOLD
ON INV.EST_FORM_ID=HOLD.EST_FORM_ID
LEFT JOIN PLAN_ESTIMATION_PAYMENT_TRANSFER TRF
ON INV.EST_FORM_ID=TRF.TRANSFER_FORM_ID
 WHERE INV.EST_FORM_ID = @formId 
 AND INV.CONTRACT_ID=@contractId
 GROUP BY INV.EST_FORM_ID
 ) INV_ALL
 WHERE INV_ALL.FORM_ID= PLAN_ESTIMATION_FORM.EST_FORM_ID
";

            logger.Debug(sql + ",formId=" + f.EST_FORM_ID + ",Contract=" + f.CONTRACT_ID);
            var parameters = new List <SqlParameter>();

            parameters.Add(new SqlParameter("formId", f.EST_FORM_ID));
            parameters.Add(new SqlParameter("contractId", f.CONTRACT_ID));
            using (var context = new topmepEntities())
            {
                context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
            }
        }
Exemplo n.º 8
0
        /// <summary>
        /// 建立代付款資料
        /// </summary>
        private static void modifyEstimationHold(PLAN_ESTIMATION_FORM form, List <PLAN_ESTIMATION_HOLDPAYMENT> lstHoldPayment, topmepEntities context)
        {
            //1.delete exist data
            string sql        = "DELETE PLAN_ESTIMATION_HOLDPAYMENT WHERE EST_FORM_ID=@formId";
            var    parameters = new List <SqlParameter>();

            parameters.Add(new SqlParameter("formId", form.EST_FORM_ID));
            context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
            logger.Debug("sql=" + sql + ",formId=" + form.EST_FORM_ID);

            //2.建立代付款資料
            if (lstHoldPayment != null && lstHoldPayment.Count > 0)
            {
                foreach (PLAN_ESTIMATION_HOLDPAYMENT hold in lstHoldPayment)
                {
                    hold.EST_FORM_ID = form.EST_FORM_ID;
                    context.PLAN_ESTIMATION_HOLDPAYMENT.Add(hold);
                }
            }
        }
Exemplo n.º 9
0
        /// <summary>
        /// 建立發票資料
        /// </summary>
        public static void modifyEstimationInvoice(PLAN_ESTIMATION_FORM form, List <PLAN_ESTIMATION_INVOICE> lstInvoice, topmepEntities context)
        {
            string sql = "DELETE PLAN_ESTIMATION_INVOICE WHERE EST_FORM_ID=@formId";

            logger.Debug("remove estimation invoice:sql=" + sql + ",formId=" + form.EST_FORM_ID);
            var parameters = new List <SqlParameter>();

            parameters.Add(new SqlParameter("formId", form.EST_FORM_ID));
            context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
            logger.Debug("sql=" + sql + ",formId=" + form.EST_FORM_ID);

            if (null != lstInvoice && lstInvoice.Count > 0)
            {
                foreach (PLAN_ESTIMATION_INVOICE inv in lstInvoice)
                {
                    inv.EST_FORM_ID = form.EST_FORM_ID;
                    context.PLAN_ESTIMATION_INVOICE.Add(inv);
                }
            }
        }
Exemplo n.º 10
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();
                logger.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();
                logger.Debug(sql);
                context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
                //3.1 付款憑證資料
                modifyEstimationInvoice(form, listInvoice, context);
                //4.建立代付資料
                modifyEstimationHold(form, lstHoldPayment, context);
                logger.Debug("get Hold4Payment=" + JsonConvert.SerializeObject(lstHoldPayment).ToString());
                //5.建立代付扣款明細
                modifyEstimationTransfer(form, listTransferPayment, context);
                logger.Debug("get TransferPayment=" + JsonConvert.SerializeObject(listTransferPayment).ToString());
                context.SaveChanges();
            }
            //6.建立彙整金額
            SumEstimationForm(form);
        }