//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);
}
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);
}
//取得估驗單主檔
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);
}
//修改基本資料
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());
}
/// <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);
}
/// <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);
}
}
}
/// <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());
}
}
/// <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);
}
}
}
/// <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);
}
}
}
/// <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);
}