/// <summary>
/// 點工估驗單彙整資料
/// </summary>
public List <EstimationItem> getEstimationList4TempWorker(string projectId, string rptStartId, string rtpEndId, string supplierId, string chargeId)
{
StringBuilder sb = new StringBuilder(sql4TempWorker);
List <EstimationItem> lst = new List <EstimationItem>();
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("reportIdBegin", rptStartId));
parameters.Add(new SqlParameter("reportIdEnd", rtpEndId));
parameters.Add(new SqlParameter("projectId", projectId));
parameters.Add(new SqlParameter("SupplierId", supplierId));
string sqlSelet = @"
select c.SUPPLIER_ID
,c.INQUIRY_FORM_ID
,c.ITEM_ID
,c.ITEM_DESC
,c.ITEM_UNIT
,c.ITEM_UNIT_PRICE
,r.LAST_QTY PriorQty
,r.FINISH_QTY EstimationQty
,r.FINISH_QTY*c.ITEM_UNIT_PRICE EstimationAmount
from contractInfo c,SumTempWorkReport r
where c.SUPPLIER_ID=r.SUPPLIER_ID
";
string sql = sb.Append(sqlSelet).ToString();
using (var context = new TopmepEntities())
{
log.Debug("sql=" + sql);
lst = context.Database.SqlQuery <EstimationItem>(sql, parameters.ToArray()).ToList();
}
return(lst);
}
//取得估驗單清單
public List <EstimationOrderForm> getFormList(string projectid, string status)
{
List <EstimationOrderForm> lst = null;
string sql = @"
select p.PROJECT_NAME,c.SUPPLIER_ID SUPPLIER_NAME,c.FORM_NAME CONTRACT_NAME,f.*
from PLAN_ESTIMATION_FORM f
inner join TND_PROJECT p on f.project_id=p.project_id
inner join PLAN_SUP_INQUIRY c on f.CONTRACT_ID=c.INQUIRY_FORM_ID
WHERE f.PROJECT_ID=@projectid AND (@status is null or f.STATUS=@status)
";
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectid", projectid));
if (null == status)
{
parameters.Add(new SqlParameter("status", DBNull.Value));
}
else
{
parameters.Add(new SqlParameter("status", status));
}
using (var context = new TopmepEntities())
{
log.Debug("sql=" + sql);
lst = context.Database.SqlQuery <EstimationOrderForm>(sql, parameters.ToArray()).ToList();
}
return(lst);
}
//取得貸款帳戶資料
public List <BankLoanInfoExt> getAllBankLoan()
{
List <BankLoanInfoExt> lstBankLoan = null;
using (var context = new TopmepEntities())
{
try
{
string sql = @"
SELECT B.* , P.PROJECT_NAME, ISNULL(ROUND(V.vaRatio * 100, 0),0) AS vaRatio ,
(SELECT ISNULL(IIF(QUOTA_RECYCLABLE = 'Y', SUM(TRANSACTION_TYPE*AMOUNT),(SUM(IIF(TRANSACTION_TYPE = 1,0,-1)*AMOUNT))), 0)
FROM FIN_LOAN_TRANACTION T WHERE T.BL_ID = B.BL_ID) SumTransactionAmount
FROM FIN_BANK_LOAN B LEFT JOIN TND_PROJECT P ON B.PROJECT_ID = P.PROJECT_ID LEFT JOIN
(SELECT va.PROJECT_ID AS PROJECT_ID, ISNULL(va.VALUATION_AMOUNT, 0) / ISNULL(pi.contractAtm, 1) AS vaRatio
FROM (SELECT PROJECT_ID, SUM(VALUATION_AMOUNT) AS VALUATION_AMOUNT
FROM PLAN_VALUATION_FORM GROUP BY PROJECT_ID)va LEFT JOIN
(SELECT PROJECT_ID, SUM(ITEM_UNIT_PRICE * ITEM_QUANTITY) AS contractAtm
FROM PLAN_ITEM GROUP BY PROJECT_ID)pi ON va.PROJECT_ID = pi.PROJECT_ID)V ON B.PROJECT_ID = V.PROJECT_ID WHERE ISNULL(IS_SUPPLIER, 'N') <> 'Y'
";
log.Debug("sql" + sql);
lstBankLoan = context.Database.SqlQuery <BankLoanInfoExt>(sql).ToList();
log.Info("new bank loan records=" + lstBankLoan.Count);
}
catch (Exception ex)
{
log.Error(ex.Message + ":StackTrace=" + ex.StackTrace);
}
}
return(lstBankLoan);
}
/* 依 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 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 SYS_USER getUser(string userid)
{
log.Debug("get user by id=" + userid);
SYS_USER u = null;
using (var context = new TopmepEntities())
{
//設定此2參數，以便取消關聯物件，讓JSON 可以運作
// Disable lazy loading
context.Configuration.LazyLoadingEnabled = false;
// Disable proxies
context.Configuration.ProxyCreationEnabled = false;
//設定SQL
string esql = @"SELECT * FROM SYS_USER u WHERE u.USER_ID=@userid";
try
{
u = context.SYS_USER.SqlQuery(esql, new SqlParameter("userid", userid)).First();
}
catch (Exception e)
{
log.Error(e);
}
}
return(u);
}
//取得估驗單明細資料
public List <EstimationItem> getItems(string formId)
{
List <EstimationItem> lst = new List <EstimationItem>();
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("formId", formId));
string sql = @"
select
f.CONTRACT_ID CONTRACT_ID,f.EST_FORM_ID EST_FORM_ID,
it.PLAN_ITEM_ID,cit.ITEM_ID,cit.ITEM_DESC,cit.ITEM_UNIT,cit.ITEM_UNIT_PRICE,
cit.ITEM_QTY as ITEM_QUANTITY,it.EST_QTY EstimationQty,it.EST_AMOUNT EstimationAmount,
it.REMARK,it.EST_ITEM_ID
from PLAN_ESTIMATION_FORM f
inner join PLAN_ESTIMATION_ITEM it
on f.EST_FORM_ID=it.EST_FORM_ID
inner join PLAN_SUP_INQUIRY_ITEM cit
on it.PLAN_ITEM_ID=cit.PLAN_ITEM_ID
and f.CONTRACT_ID=cit.INQUIRY_FORM_ID
and f.EST_FORM_ID=@formId
";
using (var context = new TopmepEntities())
{
log.Debug("sql=" + sql);
lst = context.Database.SqlQuery <EstimationItem>(sql, parameters.ToArray()).ToList();
}
return(lst);
}
//取得代付扣款彙整資料--依據驗收單相關取得對應的代付資料
private List <Model4PaymentTransfer> getPaymentTransfer(string projectId, string supplierId)
{
List <Model4PaymentTransfer> lstSummary = null;
string sql = @"
SELECT
*
FROM
(
--代付資料
SELECT F.PROJECT_ID,F.CONTRACT_ID,F.PAYEE,H.* FROM
PLAN_ESTIMATION_FORM F INNER JOIN
PLAN_ESTIMATION_HOLDPAYMENT H
ON F.EST_FORM_ID=H.EST_FORM_ID
AND F.PROJECT_ID=@projectId
) A
WHERE LEFT(SUPPLIER_ID,7) =@supplierId
";
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectId", projectId));
parameters.Add(new SqlParameter("supplierId", supplierId));
using (var context = new TopmepEntities())
{
lstSummary = context.Database.SqlQuery <Model4PaymentTransfer>(sql, parameters.ToArray()).ToList();
}
return(lstSummary);
}
//取得帳號資料
public void getUserByCriteria(SYS_USER u, string roleid)
{
log.Info("user="******",roleId=" + roleid);
List <SYS_USER> lstUser = new List <SYS_USER>();
//處理SQL，預先埋入條件減少後續處理作業
string sql = "SELECT USER_ID,USER_NAME,EMAIL,TEL,TEL_EXT,PASSWORD,FAX,MOBILE,CREATE_ID,CREATE_DATE,MODIFY_ID,MODIFY_DATE,"
+ "(SELECT ROLE_NAME FROM SYS_ROLE r WHERE r.ROLE_ID = u.ROLE_ID) ROLE_ID,DEP_CODE "
+ " FROM SYS_USER u WHERE 1=1 ";
//定義參數: User ID , User Name, Tel,Roleid
var parameters = new List <SqlParameter>();
//處理帳號相關條件
if (null != u)
{
//帳號
log.Debug("userID=" + u.USER_ID);
if (null != u.USER_ID && u.USER_ID != "")
{
sql = sql + "AND u.USER_ID= @userid ";
parameters.Add(new SqlParameter("userid", u.USER_ID));
}
//姓名
log.Debug("USER_NAME=" + u.USER_NAME);
if (null != u.USER_NAME && u.USER_NAME != "")
{
sql = sql + "AND u.USER_NAME LIKE @username ";
parameters.Add(new SqlParameter("username", "%" + u.USER_NAME + "%"));
}
//電話
log.Debug("TEL=" + u.TEL);
if (null != u.TEL && u.TEL != "")
{
sql = sql + "AND u.TEL LIKE @tel ";
parameters.Add(new SqlParameter("tel", "%" + u.TEL + "%"));
}
}
//填入角色條件
if (null != roleid && roleid != "")
{
log.Debug("ROLE_ID=" + u.ROLE_ID);
sql = sql + "AND u.ROLE_ID = @roleid ";
parameters.Add(new SqlParameter("roleid", roleid));
}
//取得資料
using (var context = new TopmepEntities())
{
if (parameters.Count() == 0)
{
log.Debug(sql);
lstUser = context.SYS_USER.SqlQuery(sql).ToList();
}
else
{
log.Debug(sql);
lstUser = context.SYS_USER.SqlQuery(sql, parameters.ToArray()).ToList();
}
userManageModels.sysUsers = lstUser;
}
}
//取得驗收單與相關合約資料
public List <plansummary> getAllPlanContract(string projectid)
{
StringBuilder sb = new StringBuilder(sql4Est);
List <plansummary> lst = new List <plansummary>();
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectid", projectid));
//parameters.Add(new SqlParameter("supplier", DBNull.Value));
//處理SQL 預先填入專案代號,設定集合處理參數
string sql = @"
select
ROW_NUMBER() OVER(ORDER BY c.SUPPLIER_ID) AS NO,
c.INQUIRY_FORM_ID,
c.FORM_NAME,c.SUPPLIER_ID,c.TYPE,count(*) ITEM_ROWS,
MAX(esOrder.PR_ID) as PR_ID_E,MIN(esOrder.PR_ID) as PR_ID_S
from contract c
inner join esOrder
on c.PLAN_ITEM_ID=esOrder.PLAN_ITEM_ID
group by c.FORM_NAME,c.SUPPLIER_ID,c.TYPE,c.INQUIRY_FORM_ID
";
sql = sb.Append(sql).ToString();
using (var context = new TopmepEntities())
{
log.Debug("get contract sql=" + sql);
lst = context.Database.SqlQuery <plansummary>(sql, parameters.ToArray()).ToList();
}
log.Info("get contract count=" + lst.Count);
return(lst);
}
public List <ExpenseFormFunction> getExpenseOutFlowByDate(string paymentDate, string duringStart, string duringEnd)
{
log.Debug("get cash out flow from expense form by payment date, and payment date =" + paymentDate);
List <ExpenseFormFunction> lstItem = new List <ExpenseFormFunction>();
using (var context = new TopmepEntities())
{
//條件篩選
if (null != paymentDate && paymentDate != "")
{
lstItem = context.Database.SqlQuery <ExpenseFormFunction>("SELECT fef.EXP_FORM_ID, ISNULL(fef.PROJECT_ID, '')PROJECT_ID, fef.PAYEE, CONVERT(varchar, fef.PAYMENT_DATE, 111)RECORDED_DATE, SUM(fei.AMOUNT)AMOUNT " +
"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, fef.PAYMENT_DATE, 111) = @paymentDate " +
"GROUP BY fef.EXP_FORM_ID, fef.PAYEE, ISNULL(fef.PROJECT_ID, ''), CONVERT(varchar, fef.PAYMENT_DATE, 111) ", new SqlParameter("paymentDate", paymentDate)).ToList();
}
else if (null != duringStart && duringStart != "" && null != duringEnd && duringEnd != "")
{
lstItem = context.Database.SqlQuery <ExpenseFormFunction>("SELECT fef.EXP_FORM_ID, ISNULL(fef.PROJECT_ID, '')PROJECT_ID, fef.PAYEE, CONVERT(varchar, fef.PAYMENT_DATE, 111)RECORDED_DATE, SUM(fei.AMOUNT)AMOUNT " +
"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, fef.PAYMENT_DATE, 111) >= @duringStart " +
"AND CONVERT(varchar, fef.PAYMENT_DATE, 111) <= @duringEnd GROUP BY fef.EXP_FORM_ID, fef.PAYEE, ISNULL(fef.PROJECT_ID, ''), CONVERT(varchar, fef.PAYMENT_DATE, 111) "
, new SqlParameter("duringStart", duringStart), new SqlParameter("duringEnd", duringEnd)).ToList();
}
else
{
lstItem = context.Database.SqlQuery <ExpenseFormFunction>("SELECT fef.EXP_FORM_ID, ISNULL(fef.PROJECT_ID, '')PROJECT_ID, fef.PAYEE, CONVERT(varchar, fef.PAYMENT_DATE, 111)RECORDED_DATE, SUM(fei.AMOUNT)AMOUNT " +
"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 fef.EXP_FORM_ID, fef.PAYEE, ISNULL(fef.PROJECT_ID, ''), CONVERT(varchar, fef.PAYMENT_DATE, 111) ").ToList();
}
}
return(lstItem);
}
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);
}
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 BankLoanInfo getBankLoan(string bl_id, string supplier)
{
BankLoanInfo item = new BankLoanInfo();
using (var context = new TopmepEntities())
{
try
{
log.Info("get bank transaction BL_ID=" + bl_id);
item.LoanInfo = context.FIN_BANK_LOAN.Find(long.Parse(bl_id));
long blid = long.Parse(bl_id);
item.LoanTransaction = context.FIN_LOAN_TRANACTION.Where(b => b.BL_ID == blid).ToList();
string sql = "";
//取得期數與匯總金額
if (supplier == "Y")
{
sql = "SELECT ISNULL(CUR_PERIOD, 0)CUR_PERIOD , ISNULL(AMOUNT, 0)AMOUNT, (SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f " +
"LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID WHERE t.TRANSACTION_TYPE = 1 AND f.BL_ID =@BL_ID) AS paybackAmt, " +
"(SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID " +
"WHERE t.TRANSACTION_TYPE = -1 AND f.BL_ID =@BL_ID) AS eventAmt FROM FIN_BANK_LOAN f " +
"LEFT JOIN (SELECT BL_ID, MAX(ISNULL(PERIOD, 0)) CUR_PERIOD, SUM(TRANSACTION_TYPE * AMOUNT) AMOUNT " +
"FROM FIN_LOAN_TRANACTION GROUP BY BL_ID)flt ON flt.BL_ID = f.BL_ID WHERE f.BL_ID =@BL_ID ";
}
else
{
sql = "SELECT ISNULL(CUR_PERIOD, 0)CUR_PERIOD , ISNULL(AMOUNT, 0)AMOUNT, ROUND(ISNULL(fbl.QUOTA * (1-IIF(fbl.vaRatio >= fbl.CUM_AR_RATIO , 1, fbl.QUOTA_AVAILABLE_RATIO/100)), 0), 0) AS SURPLUS_AMOUNT, " +
"(SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID WHERE t.TRANSACTION_TYPE = 1 AND f.BL_ID =@BL_ID) AS paybackAmt, " +
"(SELECT ISNULL(SUM(AMOUNT), 0) FROM FIN_BANK_LOAN f LEFT JOIN FIN_LOAN_TRANACTION t ON f.BL_ID = t.BL_ID WHERE t.TRANSACTION_TYPE = -1 AND f.BL_ID =@BL_ID) AS eventAmt " +
"FROM (SELECT BL_ID, QUOTA, CUM_AR_RATIO, QUOTA_AVAILABLE_RATIO, ISNULL(VALUATION_AMOUNT,0) / ISNULL(contractAtm, 1) * 100 AS vaRatio FROM FIN_BANK_LOAN f LEFT JOIN " +
"(SELECT PROJECT_ID, SUM(ITEM_UNIT_PRICE * ITEM_QUANTITY) AS contractAtm FROM PLAN_ITEM GROUP BY PROJECT_ID)pi ON f.PROJECT_ID = pi.PROJECT_ID " +
"LEFT JOIN (SELECT PROJECT_ID, SUM(VALUATION_AMOUNT)AS VALUATION_AMOUNT FROM PLAN_VALUATION_FORM GROUP BY PROJECT_ID)v ON f.PROJECT_ID = v.PROJECT_ID " +
"WHERE BL_ID=@BL_ID)fbl LEFT JOIN (SELECT lt.BL_ID, MAX(ISNULL(PERIOD,0)) CUR_PERIOD, IIF(QUOTA_RECYCLABLE = 'Y',SUM(TRANSACTION_TYPE*AMOUNT), SUM(IIF(TRANSACTION_TYPE = 1,0,-1)*AMOUNT)) AMOUNT " +
"from FIN_LOAN_TRANACTION lt LEFT JOIN FIN_BANK_LOAN bl ON lt.BL_ID = bl.BL_ID GROUP BY lt.BL_ID, bl.QUOTA_RECYCLABLE)flt ON flt.BL_ID = fbl.BL_ID ";
}
log.Debug("sql=" + sql);
Dictionary <string, object> para = new Dictionary <string, object>();
para.Add("BL_ID", blid);
DataSet ds = ExecuteStoreQuery(sql, System.Data.CommandType.Text, para);
if (ds.Tables[0].Rows.Count > 0)
{
item.CurPeriod = long.Parse(ds.Tables[0].Rows[0]["CUR_PERIOD"].ToString());
item.SumTransactionAmount = (decimal)ds.Tables[0].Rows[0]["AMOUNT"];
item.paybackAmt = (decimal)ds.Tables[0].Rows[0]["paybackAmt"];
item.eventAmt = (decimal)ds.Tables[0].Rows[0]["eventAmt"];
if (supplier != "Y")
{
item.SurplusQuota = (decimal)ds.Tables[0].Rows[0]["SURPLUS_AMOUNT"];
}
}
}
catch (Exception ex)
{
log.Error(ex.Message + ":StackTrace=" + ex.StackTrace);
}
}
return(item);
}
//取得估驗單主檔
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);
}
//取得部門資料
public ENT_DEPARTMENT getDepartmentById(string depid)
{
using (var context = new TopmepEntities())
{
department = context.ENT_DEPARTMENT.SqlQuery("select dep.* from ENT_DEPARTMENT dep "
+ "where dep.DEP_ID = @depid "
, new SqlParameter("depid", depid)).First();
}
return(department);
}
//建立異動單
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 int getYearOfSiteExpenseById(string projectid, int seqYear)
{
int lstYear = 0;
using (var context = new TopmepEntities())
{
lstYear = context.Database.SqlQuery <int>("SELECT BUDGET_YEAR FROM PLAN_SITE_BUDGET WHERE PROJECT_ID = @projectid AND YEAR_SEQUENCE = @seqYear GROUP BY BUDGET_YEAR "
, new SqlParameter("projectid", projectid), new SqlParameter("seqYear", seqYear)).FirstOrDefault();
}
return(lstYear);
}
//取得估驗請款憑證資料
public List <PLAN_ESTIMATION_INVOICE> getEstimationInvoice(string formId)
{
List <PLAN_ESTIMATION_INVOICE> lst = new List <PLAN_ESTIMATION_INVOICE>();
using (var context = new TopmepEntities())
{
lst = context.PLAN_ESTIMATION_INVOICE.Where(x => x.EST_FORM_ID == formId).Select(x => x).ToList();
log.Debug("get invoice by formId=" + formId + ",count=" + lst.Count);
}
return(lst);
}
//取得工地費用項目
public List <FIN_SUBJECT> getSubjectOfExpense4Site()
{
List <FIN_SUBJECT> lstSubject = new List <FIN_SUBJECT>();
using (var context = new TopmepEntities())
{
lstSubject = context.Database.SqlQuery <FIN_SUBJECT>("SELECT * FROM FIN_SUBJECT WHERE CATEGORY = '工地費用' ORDER BY FIN_SUBJECT_ID; ").ToList();
log.Info("Get Subject of Operating Expense Count=" + lstSubject.Count);
}
return(lstSubject);
}
//取得專案編號?
public string getSiteBudgetById(string prjid)
{
string projectid = null;
using (var context = new TopmepEntities())
{
projectid = context.Database.SqlQuery <string>("SELECT DISTINCT PROJECT_ID FROM PLAN_SITE_BUDGET WHERE PROJECT_ID = @pid "
, new SqlParameter("pid", prjid)).FirstOrDefault();
}
return(projectid);
}
//取得特定年度之公司費用總預算金額
public ExpenseBudgetSummary getTotalExpBudgetAmount(int year)
{
ExpenseBudgetSummary lstAmount = null;
using (var context = new TopmepEntities())
{
lstAmount = context.Database.SqlQuery <ExpenseBudgetSummary>("SELECT SUM(AMOUNT) AS TOTAL_BUDGET FROM FIN_EXPENSE_BUDGET WHERE BUDGET_YEAR = @year "
, new SqlParameter("year", year)).FirstOrDefault();
}
return(lstAmount);
}
//取得代付支出明細資料
public List <PLAN_ESTIMATION_HOLDPAYMENT> getHoldPayment(string formId)
{
List <PLAN_ESTIMATION_HOLDPAYMENT> lst = new List <PLAN_ESTIMATION_HOLDPAYMENT>();
log.Debug("get PLAN_ESTIMATION_HOLDPAYMENT by " + formId);
using (var context = new TopmepEntities())
{
lst = context.PLAN_ESTIMATION_HOLDPAYMENT.Where(f => f.EST_FORM_ID == formId).ToList();
}
return(lst);
}
//取得特定期間工地費用預算與費用彙整
public List <ExpenseBudgetSummary> getSiteExpenseSummaryByYear(string projectid, string targetYear)
{
List <ExpenseBudgetSummary> lstExpBudget = new List <ExpenseBudgetSummary>();
using (var context = new TopmepEntities())
{
log.Info("sql = " + sql4Expense);
lstExpBudget = context.Database.SqlQuery <ExpenseBudgetSummary>(sql4Expense, new SqlParameter("projectid", projectid), new SqlParameter("targetYear", targetYear)).ToList();
}
return(lstExpBudget);
}
//取得特定專案之起始年度
public int getFirstYearOfPlanById(string projectid)
{
int lstYear = 0;
using (var context = new TopmepEntities())
{
lstYear = context.Database.SqlQuery <int>("SELECT YEAR(CREATE_DATE) FROM PLAN_ITEM WHERE PROJECT_ID = @projectid GROUP BY YEAR(CREATE_DATE) "
, new SqlParameter("projectid", projectid)).FirstOrDefault();
}
return(lstYear);
}
public List <REF_TYPE_MAIN> getMainType()
{
List <REF_TYPE_MAIN> lst = new List <REF_TYPE_MAIN>();
using (var context = new TopmepEntities())
{
lst = context.REF_TYPE_MAIN.SqlQuery("SELECT * FROM REF_TYPE_MAIN ORDER BY TYPE_CODE_1 ,TYPE_CODE_2;").ToList();
log.Debug("get Main Type Count" + lst.Count);
}
return(lst);
}
public List <REF_TYPE_SUB> getSubType(string typecodeid)
{
List <REF_TYPE_SUB> lst = new List <REF_TYPE_SUB>();
using (var context = new TopmepEntities())
{
lst = context.REF_TYPE_SUB.SqlQuery("SELECT * FROM REF_TYPE_SUB WHERE TYPE_CODE_ID=@typecodeid ORDER BY SUB_TYPE_CODE;", new SqlParameter("typecodeid", typecodeid)).ToList();
log.Debug("get sub Type Count" + lst.Count);
}
return(lst);
}
/// <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 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);
}
//取得財務科目類別選單
public List <string> getCategory()
{
List <string> lst = new List <string>();
using (var context = new TopmepEntities())
{
//取得科目類別
lst = context.Database.SqlQuery <string>("SELECT DISTINCT CATEGORY FROM FIN_SUBJECT ;").ToList();
log.Info("Get Subject Category Count=" + lst.Count);
}
return(lst);
}