//取得特定專案工地費用每月執行總和
public List <ExpensetFromOPByMonth> getSiteExpensetOfMonth(string projectid, int targetYear, int targetMonth, bool isCum)
{
List <ExpensetFromOPByMonth> lstExpense = new List <ExpensetFromOPByMonth>();
using (var context = new topmepEntities())
{
if (isCum == true)
{
lstExpense = context.Database.SqlQuery <ExpensetFromOPByMonth>("SELECT SUM(F.JAN) AS JAN, SUM(F.FEB) AS FEB, SUM(F.MAR) AS MAR, SUM(F.APR) AS APR, SUM(F.MAY) AS MAY, SUM(F.JUN) AS JUN, " +
"SUM(F.JUL) AS JUL, SUM(F.AUG) AS AUG, SUM(F.SEP) AS SEP, SUM(F.OCT) AS OCT, SUM(F.NOV) AS NOV, SUM(F.DEC) AS DEC, SUM(F.HTOTAL) AS HTOTAL FROM(SELECT C.*, E.HTOTAL " +
"FROM(SELECT SUBJECT_NAME, FIN_SUBJECT_ID, [01] As 'JAN', [02] As 'FEB', [03] As 'MAR', [04] As 'APR', [05] As 'MAY', [06] As 'JUN', [07] As 'JUL', [08] As 'AUG', [09] As 'SEP', [10] As 'OCT', [11] As 'NOV', [12] As 'DEC' " +
"FROM(SELECT B.OCCURRED_MONTH, fs.FIN_SUBJECT_ID, fs.SUBJECT_NAME, B.AMOUNT FROM FIN_SUBJECT fs LEFT JOIN(SELECT ef.OCCURRED_MONTH, ei.FIN_SUBJECT_ID, ei.AMOUNT FROM FIN_EXPENSE_ITEM ei " +
"LEFT JOIN FIN_EXPENSE_FORM ef ON ei.EXP_FORM_ID = ef.EXP_FORM_ID WHERE ef.PROJECT_ID = @projectid AND ef.OCCURRED_YEAR = @targetYear AND ef.OCCURRED_MONTH <= @targetMonth)B " +
"ON fs.FIN_SUBJECT_ID = B.FIN_SUBJECT_ID WHERE fs.CATEGORY = '工地費用') As STable " +
"PIVOT(SUM(AMOUNT) FOR OCCURRED_MONTH IN([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) As PTable)C LEFT JOIN(SELECT FIN_SUBJECT_ID, ISNULL(SUM(fei.AMOUNT), 0) AS HTOTAL " +
"FROM FIN_EXPENSE_ITEM fei LEFT JOIN FIN_EXPENSE_FORM fef ON fei.EXP_FORM_ID = fef.EXP_FORM_ID WHERE fef.PROJECT_ID = @projectid AND fef.OCCURRED_YEAR < @targetYear OR " +
"fef.PROJECT_ID = @projectid AND fef.OCCURRED_YEAR = @targetYear AND fef.OCCURRED_MONTH <= @targetMonth GROUP BY FIN_SUBJECT_ID)E ON C.FIN_SUBJECT_ID = E.FIN_SUBJECT_ID)F; "
, new SqlParameter("projectid", projectid), new SqlParameter("targetYear", targetYear), new SqlParameter("targetMonth", targetMonth)).ToList();
}
else
{
lstExpense = context.Database.SqlQuery <ExpensetFromOPByMonth>("SELECT SUM(F.JAN) AS JAN, SUM(F.FEB) AS FEB, SUM(F.MAR) AS MAR, SUM(F.APR) AS APR, SUM(F.MAY) AS MAY, SUM(F.JUN) AS JUN, " +
"SUM(F.JUL) AS JUL, SUM(F.AUG) AS AUG, SUM(F.SEP) AS SEP, SUM(F.OCT) AS OCT, SUM(F.NOV) AS NOV, SUM(F.DEC) AS DEC, SUM(F.HTOTAL) AS HTOTAL FROM(SELECT C.*, " +
"SUM(ISNULL(C.JAN, 0)) + SUM(ISNULL(C.FEB, 0)) + SUM(ISNULL(C.MAR, 0)) + SUM(ISNULL(C.APR, 0)) + SUM(ISNULL(C.MAY, 0)) + SUM(ISNULL(C.JUN, 0)) " +
"+ SUM(ISNULL(C.JUL, 0)) + SUM(ISNULL(C.AUG, 0)) + SUM(ISNULL(C.SEP, 0)) + SUM(ISNULL(C.OCT, 0)) + SUM(ISNULL(C.NOV, 0)) + SUM(ISNULL(C.DEC, 0)) AS HTOTAL " +
"FROM(SELECT SUBJECT_NAME, FIN_SUBJECT_ID, [01] As 'JAN', [02] As 'FEB', [03] As 'MAR', [04] As 'APR', [05] As 'MAY', [06] As 'JUN', [07] As 'JUL', [08] As 'AUG', [09] As 'SEP', [10] As 'OCT', [11] As 'NOV', [12] As 'DEC' " +
"FROM(SELECT B.OCCURRED_MONTH, fs.FIN_SUBJECT_ID, fs.SUBJECT_NAME, B.AMOUNT FROM FIN_SUBJECT fs LEFT JOIN(SELECT ef.OCCURRED_MONTH, ei.FIN_SUBJECT_ID, ei.AMOUNT FROM FIN_EXPENSE_ITEM ei " +
"LEFT JOIN FIN_EXPENSE_FORM ef ON ei.EXP_FORM_ID = ef.EXP_FORM_ID WHERE ef.PROJECT_ID = @projectid AND ef.OCCURRED_YEAR = @targetYear)B " +
"ON fs.FIN_SUBJECT_ID = B.FIN_SUBJECT_ID WHERE fs.CATEGORY = '工地費用') As STable " +
"PIVOT(SUM(AMOUNT) FOR OCCURRED_MONTH IN([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) As PTable)C " +
"GROUP BY C.SUBJECT_NAME, C.FIN_SUBJECT_ID, C.JAN, C.FEB, C.MAR, C.APR, C.MAY, C.JUN, C.JUL, C.AUG, C.SEP, C.OCT, C.NOV, C.DEC)F ; "
, new SqlParameter("projectid", projectid), new SqlParameter("targetYear", targetYear)).ToList();
}
}
return(lstExpense);
}
//更新異動單資料
public string updateChangeOrder(PLAN_COSTCHANGE_FORM form, List <PLAN_COSTCHANGE_ITEM> lstItem)
{
int i = 0;
string sqlForm = @"UPDATE PLAN_COSTCHANGE_FORM SET REASON_CODE=@Reasoncode,METHOD_CODE=@methodCode,
REMARK_ITEM=@RemarkItem,REMARK_QTY=Null,REMARK_PRICE=Null,REMARK_OTHER=Null,
MODIFY_USER_ID=@userId,MODIFY_DATE=@modifyDate WHERE FORM_ID=@formId;";
string sqlItem = @"UPDATE PLAN_COSTCHANGE_ITEM SET ITEM_DESC=@itemdesc,ITEM_UNIT=@unit,ITEM_UNIT_PRICE=@unitPrice,ITEM_UNIT_COST=@unitCost,
ITEM_QUANTITY=@Qty,ITEM_REMARK=@remark,TRANSFLAG=@transFlag,MODIFY_USER_ID=@userId,MODIFY_DATE=@modifyDate
WHERE ITEM_UID=@uid";
//2.將資料寫入
using (var context = new topmepEntities())
{
try
{
//更新表頭
context.Database.BeginTransaction();
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("Reasoncode", form.REASON_CODE));
parameters.Add(new SqlParameter("methodCode", form.METHOD_CODE));
parameters.Add(new SqlParameter("RemarkItem", form.REMARK_ITEM));
// parameters.Add(new SqlParameter("RemarkQty", form.REMARK_QTY));
// parameters.Add(new SqlParameter("RemarkPrice", form.REMARK_PRICE));
// parameters.Add(new SqlParameter("RemarkOther", form.REMARK_OTHER));
parameters.Add(new SqlParameter("userId", form.MODIFY_USER_ID));
parameters.Add(new SqlParameter("modifyDate", form.MODIFY_DATE));
parameters.Add(new SqlParameter("formId", form.FORM_ID));
i = context.Database.ExecuteSqlCommand(sqlForm, parameters.ToArray());
logger.Debug("create COSTCHANGE_FORM:" + sqlForm);
foreach (PLAN_COSTCHANGE_ITEM item in lstItem)
{
parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("itemdesc", item.ITEM_DESC));
parameters.Add(new SqlParameter("unit", item.ITEM_UNIT));
if (item.ITEM_UNIT_PRICE != null)
{
parameters.Add(new SqlParameter("unitPrice", item.ITEM_UNIT_PRICE));
}
else
{
parameters.Add(new SqlParameter("unitPrice", DBNull.Value));
}
if (item.ITEM_UNIT_COST != null)
{
parameters.Add(new SqlParameter("unitCost", item.ITEM_UNIT_COST));
}
else
{
parameters.Add(new SqlParameter("unitCost", DBNull.Value));
}
if (item.ITEM_QUANTITY == null)
{
parameters.Add(new SqlParameter("Qty", DBNull.Value));
}
else
{
parameters.Add(new SqlParameter("Qty", item.ITEM_QUANTITY));
}
parameters.Add(new SqlParameter("transFlag", item.TRANSFLAG));
parameters.Add(new SqlParameter("remark", item.ITEM_REMARK));
parameters.Add(new SqlParameter("userId", form.MODIFY_USER_ID));
parameters.Add(new SqlParameter("modifyDate", form.MODIFY_DATE));
parameters.Add(new SqlParameter("uid", item.ITEM_UID));
i = i + context.Database.ExecuteSqlCommand(sqlItem, parameters.ToArray());
}
context.Database.CurrentTransaction.Commit();
}
catch (Exception ex)
{
context.Database.CurrentTransaction.Rollback();
logger.Error(ex.Message + ":" + ex.StackTrace);
return("資料更新失敗!!(" + ex.Message + ")");
}
}
return("資料更新成功(" + i + ")!");
}
/// <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>
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>
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);
}
public List <PlanAccountFunction> getOutFlowBalanceByDate(string paymentDate, string duringStart, string duringEnd)
{
logger.Debug("get cash out flow balance by payment date, and payment date =" + paymentDate);
List <PlanAccountFunction> lstItem = new List <PlanAccountFunction>();
using (var context = new topmepEntities())
{
//條件篩選
if (null != paymentDate && paymentDate != "")
{
string sql = @"SELECT p.* , CONVERT(char(10), p.PAYMENT_DATE, 111) AS RECORDED_DATE, PARSENAME(Convert(varchar,Convert(money,ISNULL(p.AMOUNT_PAID, 0)),1),2) AS RECORDED_AMOUNT_PAYABLE,
PARSENAME(Convert(varchar, Convert(money, ISNULL(it.AMOUNT, 0)), 1), 2) AS PAYBACK_AMOUNT, PARSENAME(Convert(varchar, Convert(money, ISNULL(p.AMOUNT_PAID, 0) - ISNULL(it.AMOUNT, 0)), 1), 2) AS RECORDED_AMOUNT_PAID
FROM (
select o.PAYEE, CONVERT(datetime,o.PAYMENT_DATE, 111)PAYMENT_DATE, SUM(o.AMOUNT)AMOUNT_PAID
from(SELECT CONVERT(varchar, PAYMENT_DATE, 111)PAYMENT_DATE, SUM(fei.AMOUNT)AMOUNT, PAYEE
FROM FIN_EXPENSE_FORM fef LEFT JOIN FIN_EXPENSE_ITEM fei ON fef.EXP_FORM_ID = fei.EXP_FORM_ID
WHERE fef.STATUS = 30 AND CONVERT(varchar, PAYMENT_DATE, 111) = @paymentDate
GROUP BY CONVERT(varchar, PAYMENT_DATE, 111), PAYEE
union
SELECT CONVERT(char(10), PAYMENT_DATE, 111)PAYMENT_DATE, SUM(AMOUNT_PAID)AMOUNT_PAID, PAYEE
FROM PLAN_ACCOUNT WHERE ISDEBIT = 'N'
AND CONVERT(char(10), PAYMENT_DATE, 111) = @paymentDate
GROUP BY PAYEE, CONVERT(char(10), PAYMENT_DATE, 111))o GROUP BY o.PAYEE, o.PAYMENT_DATE
)p LEFT JOIN(
SELECT SUM(t.AMOUNT)AMOUNT, l.BANK_NAME FROM FIN_LOAN_TRANACTION t LEFT JOIN FIN_BANK_LOAN l ON t.BL_ID = l.BL_ID
WHERE ISNULL(l.IS_SUPPLIER, 'N') = 'Y'
AND t.TRANSACTION_TYPE = 1
OR ISNULL(l.IS_SUPPLIER, 'N') <> 'Y'
AND t.REMARK NOT LIKE '%備償%'
AND t.TRANSACTION_TYPE = -1
AND CONVERT(char(10),IIF(TRANSACTION_TYPE = 1, PAYBACK_DATE, EVENT_DATE), 111) = @paymentDate
GROUP BY l.BANK_NAME
)it ON it.BANK_NAME = p.PAYEE";
lstItem = context.Database.SqlQuery <PlanAccountFunction>(sql, new SqlParameter("paymentDate", paymentDate)).ToList();
}
else if (null != duringStart && duringStart != "" && null != duringEnd && duringEnd != "")
{
lstItem = context.Database.SqlQuery <PlanAccountFunction>("SELECT p.* , CONVERT(char(10), p.PAYMENT_DATE, 111) AS RECORDED_DATE, PARSENAME(Convert(varchar,Convert(money,ISNULL(p.AMOUNT_PAID, 0)),1),2) AS RECORDED_AMOUNT_PAYABLE, " +
"PARSENAME(Convert(varchar, Convert(money, ISNULL(it.AMOUNT, 0)), 1), 2) AS PAYBACK_AMOUNT, PARSENAME(Convert(varchar, Convert(money, ISNULL(p.AMOUNT_PAID, 0) - ISNULL(it.AMOUNT, 0)), 1), 2) AS RECORDED_AMOUNT_PAID FROM " +
"(select o.PAYEE, CONVERT(datetime,o.PAYMENT_DATE, 111)PAYMENT_DATE, SUM(o.AMOUNT)AMOUNT_PAID from(SELECT CONVERT(varchar, PAYMENT_DATE, 111)PAYMENT_DATE, SUM(fei.AMOUNT)AMOUNT, PAYEE FROM FIN_EXPENSE_FORM fef LEFT JOIN FIN_EXPENSE_ITEM fei ON fef.EXP_FORM_ID = fei.EXP_FORM_ID " +
"WHERE fef.STATUS = 30 AND PAYMENT_DATE >= CONVERT(datetime, @duringStart, 111) AND PAYMENT_DATE <= CONVERT(varchar, @duringEnd, 111) GROUP BY CONVERT(varchar, PAYMENT_DATE, 111), PAYEE " +
"union SELECT CONVERT(char(10), PAYMENT_DATE, 111)PAYMENT_DATE, SUM(AMOUNT_PAID)AMOUNT_PAID, PAYEE FROM PLAN_ACCOUNT WHERE ISDEBIT = 'N' AND PAYMENT_DATE >= CONVERT(datetime, @duringStart, 111) AND " +
"PAYMENT_DATE <= CONVERT(datetime, @duringEnd, 111) GROUP BY PAYEE, CONVERT(char(10), PAYMENT_DATE, 111))o GROUP BY o.PAYEE, o.PAYMENT_DATE)p " +
"LEFT JOIN(SELECT SUM(t.AMOUNT)AMOUNT, l.BANK_NAME FROM FIN_LOAN_TRANACTION t LEFT JOIN FIN_BANK_LOAN l ON t.BL_ID = l.BL_ID WHERE ISNULL(l.IS_SUPPLIER, 'N') = 'Y' AND t.TRANSACTION_TYPE = 1 AND " +
"IIF(TRANSACTION_TYPE = 1, PAYBACK_DATE, EVENT_DATE) >= CONVERT(datetime, @duringStart, 111) AND IIF(TRANSACTION_TYPE = 1, PAYBACK_DATE, EVENT_DATE) <= CONVERT(datetime, @duringEnd, 111) OR " +
"ISNULL(l.IS_SUPPLIER, 'N') <> 'Y' AND t.REMARK NOT LIKE '%備償%' AND t.TRANSACTION_TYPE = -1 AND IIF(TRANSACTION_TYPE = 1, PAYBACK_DATE, EVENT_DATE) >= CONVERT(datetime, @duringStart, 111) AND " +
"IIF(TRANSACTION_TYPE = 1, PAYBACK_DATE, EVENT_DATE) <= CONVERT(datetime, @duringEnd, 111) GROUP BY l.BANK_NAME)it " +
"ON it.BANK_NAME = p.PAYEE ORDER BY p.PAYMENT_DATE ", new SqlParameter("duringStart", duringStart), new SqlParameter("duringEnd", duringEnd)).ToList();
}
else
{
lstItem = context.Database.SqlQuery <PlanAccountFunction>("SELECT p.* , CONVERT(char(10), p.PAYMENT_DATE, 111) AS RECORDED_DATE, PARSENAME(Convert(varchar,Convert(money,ISNULL(p.AMOUNT_PAID, 0)),1),2) AS RECORDED_AMOUNT_PAYABLE, " +
"PARSENAME(Convert(varchar, Convert(money, ISNULL(it.AMOUNT, 0)), 1), 2) AS PAYBACK_AMOUNT, PARSENAME(Convert(varchar, Convert(money, ISNULL(p.AMOUNT_PAID, 0) - ISNULL(it.AMOUNT, 0)), 1), 2) AS RECORDED_AMOUNT_PAID FROM " +
"(select o.PAYEE, CONVERT(datetime,o.PAYMENT_DATE, 111)PAYMENT_DATE, SUM(o.AMOUNT)AMOUNT_PAID from(SELECT CONVERT(varchar, PAYMENT_DATE, 111)PAYMENT_DATE, SUM(fei.AMOUNT)AMOUNT, PAYEE FROM FIN_EXPENSE_FORM fef LEFT JOIN FIN_EXPENSE_ITEM fei ON fef.EXP_FORM_ID = fei.EXP_FORM_ID " +
"WHERE fef.STATUS = 30 GROUP BY CONVERT(varchar, PAYMENT_DATE, 111), PAYEE " +
"union SELECT CONVERT(char(10), PAYMENT_DATE, 111)PAYMENT_DATE, SUM(AMOUNT_PAID)AMOUNT_PAID, PAYEE FROM PLAN_ACCOUNT WHERE ISDEBIT = 'N' GROUP BY PAYEE, CONVERT(char(10), PAYMENT_DATE, 111))o GROUP BY o.PAYEE, o.PAYMENT_DATE)p " +
"LEFT JOIN(SELECT SUM(t.AMOUNT)AMOUNT, l.BANK_NAME FROM FIN_LOAN_TRANACTION t LEFT JOIN FIN_BANK_LOAN l ON t.BL_ID = l.BL_ID WHERE ISNULL(l.IS_SUPPLIER, 'N') = 'Y' AND t.TRANSACTION_TYPE = 1 " +
"OR ISNULL(l.IS_SUPPLIER, 'N') <> 'Y' AND t.REMARK NOT LIKE '%備償%' AND t.TRANSACTION_TYPE = -1 GROUP BY l.BANK_NAME)it " +
"ON it.BANK_NAME = p.PAYEE ORDER BY p.PAYMENT_DATE ").ToList();
}
}
return(lstItem);
}
public ActionResult AddItem(FormCollection f)
{
var inquiry = new topmeperp.Models.PLAN_SUP_INQUIRY();
var formID = Request["formID"];
var order = new topmeperp.Models.PLAN_CERT_ORDER();
SerialKeyService snoservice = new SerialKeyService();
string ordKey = snoservice.getSerialKey("EST");
using (var context = new topmepEntities())
{
string sql;
inquiry = context.PLAN_SUP_INQUIRY.SqlQuery("SELECT * FROM PLAN_SUP_INQUIRY WHERE INQUIRY_FORM_ID = '" + formID + "'").ToList().First();
sql = "INSERT INTO PLAN_CERT_ORDER (CERT_ORD_ID,INQUIRY_FORM_ID,SUPPLIER_ID,PROJECT_ID,CREATE_DATE) " +
"VALUES('" + ordKey + "','" + inquiry.INQUIRY_FORM_ID + "-" + ordKey + "','" + inquiry.SUPPLIER_ID + "','" + inquiry.PROJECT_ID + "','" + DateTime.Now.ToString() + "')";
try
{
//order.CERT_ORD_ID = "test";
//order.INQUIRY_FORM_ID = inquiry.INQUIRY_FORM_ID.ToString();
//order.SUPPLIER_ID = inquiry.SUPPLIER_ID.ToString();
//order.PROJECT_ID = inquiry.PROJECT_ID.ToString();
//context.PLAN_CERT_ORDER.Add(order);
context.Database.ExecuteSqlCommand(sql);
context.SaveChanges();
}
catch (Exception e)
{
Console.Write(e.StackTrace);
}
var qty = Request["qty"];
var plan_item_id = Request["plan_item_id"];
var type = Request["type"];
var subType = Request["subType"];
var itemID = Request["itemID"];
var desc = Request["desc"];
int[] nums = Array.ConvertAll(qty.Split(','), int.Parse);
string[] plan_item_ids = plan_item_id.Split(',');
string[] types = type.Split(',');
string[] subTypes = subType.Split(',');
string[] itemIDs = itemID.Split(',');
string[] descs = desc.Split(',');
//var test = new PLAN_CERT_ORDER_ITEM { CERT_ORD_ITEM_ID = generate_id(), ORDER_QTY = nums[0] };
var test2 = new PLAN_CERT_ORDER_ITEM {
CERT_ORD_ITEM_ID = generate_id(), ORDER_QTY = nums[1]
};
//string sql = "INSERT INTO PLAN_CERT_ORDER_ITEM (CERT_ORD_ITEM_ID,ORDER_QTY) VALUES(28," + "10" + ")";
try
{
for (int i = 0; i < nums.Length; i++)
{
context.PLAN_CERT_ORDER_ITEM.Add(new PLAN_CERT_ORDER_ITEM
{
CERT_ORD_ITEM_ID = generate_id(),
CERT_ORD_ID = ordKey,
TYPE_CODE = types[i],
SUB_TYPE_CODE = subTypes[i],
ITEM_ID = itemIDs[i],
ITEM_DESC = descs[i],
ORDER_QTY = nums[i],
PLAN_ITEM_ID = plan_item_ids[i]
});
string updateSql = "UPDATE PLAN_SUP_INQUIRY_ITEM SET ACCUMULATE_QTY = ACCUMULATE_QTY + "
+ nums[i] + " WHERE INQUIRY_FORM_ID = '" + formID + "' and PLAN_ITEM_ID = '" + plan_item_ids[i] + "'";
context.Database.ExecuteSqlCommand(updateSql);
}
//context.PLAN_CERT_ORDER_ITEM.Add(test2);
//var x=0;
//context.Database.ExecuteSqlCommand(sql);
context.SaveChanges();
}
catch (Exception e)
{
Console.Write(e.StackTrace);
}
//generate random 64 int ID
long testID = generate_id();
string orderinfo = "{CERT_OrderID: " + order.CERT_ORD_ID + " FormID: " + inquiry.INQUIRY_FORM_ID + " Supplier: " + inquiry.SUPPLIER_ID + " ProjID: " + inquiry.PROJECT_ID;
//return "Item added in " + formID + "\n" + nums[0] + ", " + nums[1] + sql+"\n("+testID+":"+ordKey+")"+plan_item_ids[0];
return(RedirectToAction("Index/" + inquiry.PROJECT_ID));
}
}