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 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 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);
}
/* 依 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 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 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);
}
/// <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 void updateTypeManageModel(REF_TYPE_MAIN mainType, List <REF_TYPE_SUB> lstSubType)
{
using (var context = new TopmepEntities())
{
//修改九宮格內容
context.REF_TYPE_MAIN.AddOrUpdate(mainType);
int i = 0;
i = context.SaveChanges();
log.Debug("Modify MainType :" + i);
string sql = "DELETE FROM REF_TYPE_SUB WHERE TYPE_CODE_ID=@typecodeid";
log.Debug("Remove SubType=" + sql + ",TypeCode=" + mainType.TYPE_CODE_1 + mainType.TYPE_CODE_2);
context.Database.ExecuteSqlCommand(sql, new SqlParameter("typecodeid", mainType.TYPE_CODE_1 + mainType.TYPE_CODE_2));
foreach (REF_TYPE_SUB subType in lstSubType)
{
log.Debug("subTypeId=" + subType.SUB_TYPE_ID + ",mainTypeId=" + subType.TYPE_CODE_ID + ",subTypeCode=" + subType.SUB_TYPE_CODE + ",subTypeDesc=" + subType.TYPE_DESC);
subType.CREATE_DATE = System.DateTime.Now;
context.REF_TYPE_SUB.Add(subType);
}
i = context.SaveChanges();
log.Debug("Modify MainSub :" + i);
}
}
/// <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);
}
}
/// <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);
}
//匯入工地預算
public int refreshSiteBudget(List <PLAN_SITE_BUDGET> items)
{
int i = 0;
log.Info("refreshSiteBudgetItem = " + items.Count);
//2.將Excel 資料寫入
using (var context = new TopmepEntities())
{
foreach (PLAN_SITE_BUDGET item in items)
{
context.PLAN_SITE_BUDGET.Add(item);
}
i = context.SaveChanges();
}
log.Info("add PLAN_SITE_BUDGET count =" + i);
return(i);
}
public void createPOMessage(SYS_USER u, PLAN_PURCHASE_REQUISITION pr)
{
//mail to 業管
// System.Web.Script.Serialization.JavaScriptSerializer objSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();
// string userJson = objSerializer.Serialize(u);
// log.Debug("sender :" + userJson);
//定義Email 樣板
StringBuilder strTemp = new StringBuilder("{1} 申購單已完成採購!!");
StringBuilder strTempBody = new StringBuilder("{1} 申購單已完成採購{2}。 <br/> 系統發出，請勿回覆!!");
//
SYS_MESSAGE m = new SYS_MESSAGE();
m.FROM_ADDRESS = u.EMAIL;
m.DISPLAY_NAME = u.USER_NAME;
UserService s = new UserService();
//TODO:加入通知申請人
List <SYS_USER> lstTarget = s.GetProjectUser(pr.PROJECT_ID, "工地主任");
strTemp.Replace("{1}", pr.PROJECT_ID);
m.MAIL_LIST = getMailList(lstTarget);
if (m.MAIL_LIST == "")
{
log.Error(pr.PR_ID + " Have no mail list!!");
return;
}
m.SUBJECT = strTemp.ToString();
strTempBody.Replace("{1}", pr.PROJECT_ID);
strTempBody.Replace("{2}", "採購單號為:<a href='" + system_url + "'>" + pr.PR_ID + "</a>");
m.MSG_BODY = strTempBody.ToString();
log.Debug("email body:" + m.MSG_BODY);
m.CREATE_ID = u.USER_ID;
m.CREATE_DATE = DateTime.Now;
//決定發送時間
m.SEND_TIME = DateTime.Now;
int i = 0;
using (var context = new TopmepEntities())
{
context.SYS_MESSAGE.Add(m);
i = context.SaveChanges();
}
}
/*增加序號記錄欄位*/
public bool addSerialKey(SYS_KEY_SERIAL serialKey)
{
bool status = false;
using (var context = new TopmepEntities())
{
context.SYS_KEY_SERIAL.Add(serialKey);
//_db.AddToSYS_KEY_SERIAL(serialKey);
int i = context.SaveChanges();
log.Debug("Add SerialKey : SerialKey=" + serialKey.KEY_ID + ",status=" + i);
if (i > 0)
{
status = true;
}
;
}
return(status);
}
public int updateProfile(SYS_USER u)
{
int i = 1;
using (var context = new TopmepEntities())
{
try
{
context.SYS_USER.AddOrUpdate(t => t.USER_ID, u);
i = context.SaveChanges();
}
catch (Exception ex)
{
log.Error(ex.StackTrace);
i = -1;
}
}
return(i);
}
/// <summary>
/// 增加借款還款紀錄
/// </summary>
/// <param name="loanTransaction"></param>
/// <returns></returns>
public int addBankLoanTransaction(List <FIN_LOAN_TRANACTION> loanTransaction)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.FIN_LOAN_TRANACTION.AddRange(loanTransaction);
i = context.SaveChanges();
log.Info("new bank loan transaction record=" + loanTransaction.Count);
}
catch (Exception ex)
{
log.Error(ex.Message + ":StackTrace=" + ex.StackTrace);
}
}
return(i);
}
//新增帳號資料
public int addNewUser(SYS_USER u)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.SYS_USER.AddOrUpdate(u);
i = context.SaveChanges();
}
catch (Exception e)
{
log.Error("add new user id fail:" + e.ToString());
log.Error(e.StackTrace);
message = e.Message;
}
}
return(i);
}
//新增貸款帳戶資料
public int addBankLoan(FIN_BANK_LOAN bankloan)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.FIN_BANK_LOAN.AddOrUpdate(bankloan);
i = context.SaveChanges();
log.Info("new bank loan record=" + bankloan.ACCOUNT_NAME);
}
catch (Exception ex)
{
log.Error(ex.Message + ":StackTrace=" + ex.StackTrace);
}
}
log.Info("add bankloan count =" + i);
return(i);
}
public int updateLoanTransactionItem(FIN_LOAN_TRANACTION item)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.FIN_LOAN_TRANACTION.AddOrUpdate(item);
i = context.SaveChanges();
}
catch (Exception e)
{
log.Error("update loan transaction item fail:" + e.ToString());
log.Error(e.StackTrace);
message = e.Message;
}
}
return(i);
}
//新增異動單品項
public int addChangeOrderItem(PLAN_COSTCHANGE_ITEM item)
{
int i = 0;
//2.將資料寫入
using (var context = new TopmepEntities())
{
try
{
log.Debug("create COSTCHANGE_FORM:" + item.FORM_ID);
context.PLAN_COSTCHANGE_ITEM.Add(item);
i = context.SaveChanges();
}
catch (Exception ex)
{
log.Error(ex.Message + ":" + ex.StackTrace);
}
}
return(i);
}
//新增角色
public int addOrUpdateRole(SYS_ROLE role)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.SYS_ROLE.AddOrUpdate(role);
i = context.SaveChanges();
}
catch (Exception e)
{
log.Error("add new role fail:" + e.ToString());
log.Error(e.StackTrace);
message = e.Message;
}
}
return(i);
}
//增加銀行帳戶
public int addBankInfo(FIN_BANK_ACCOUNT account)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.FIN_BANK_ACCOUNT.Add(account);
i = context.SaveChanges();
log.Info("new bank account record=" + account.BANK_ACCOUNT_ID + ",initial amount=" + account.CUR_AMOUNT + ",curDate=" + account.CUR_DATE);
}
catch (Exception ex)
{
log.Error(ex.Message + ":StackTrace=" + ex.StackTrace);
}
}
log.Info("add bankAccount count =" + i);
return(i);
}
//新增財務項目資料
public int addNewSubject(FIN_SUBJECT s)
{
int i = 0;
using (var context = new TopmepEntities())
{
try
{
context.FIN_SUBJECT.AddOrUpdate(s);
i = context.SaveChanges();
}
catch (Exception e)
{
log.Error("add new subject id fail:" + e.ToString());
log.Error(e.StackTrace);
message = e.Message;
}
}
return(i);
}
//修正間接成本資料
public void modifyIndirectCost(string projectId, List <PLAN_INDIRECT_COST> items)
{
using (var context = new TopmepEntities())
{
///逐筆更新資料
string sql = "UPDATE PLAN_INDIRECT_COST SET COST = @cost, MODIFY_ID = @modifyId, MODIFY_DATE = @modifyDate, NOTE = ISNULL(Note,'') + @Note WHERE PROJECT_ID = @projectId AND FIELD_ID = @fieldId";
log.Debug("sql=" + sql + ",projectid=" + projectId);
foreach (PLAN_INDIRECT_COST it in items)
{
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectId", projectId));
parameters.Add(new SqlParameter("fieldId", it.FIELD_ID));
parameters.Add(new SqlParameter("cost", it.COST));
parameters.Add(new SqlParameter("modifyId", it.MODIFY_ID));
parameters.Add(new SqlParameter("modifyDate", DateTime.Now));
parameters.Add(new SqlParameter("Note", it.NOTE));
context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
log.Debug("sql=" + sql + ",projectid=" + projectId);
}
///將新資料存入
context.SaveChanges();
}
}
//移除異動單品項
public int delChangeOrderItem(long itemid)
{
int i = 0;
//2.將品項資料刪除
using (var context = new TopmepEntities())
{
try
{
string sql = "DELETE FROM PLAN_COSTCHANGE_ITEM WHERE ITEM_UID=@itemUid;";
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("itemUid", itemid));
log.Debug("Delete COSTCHANGE_ITEM:" + itemid);
context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
i = context.SaveChanges();
}
catch (Exception ex)
{
log.Error(ex.Message + ":" + ex.StackTrace);
}
}
return(i);
}
//更新權限資料
public int updatePrivilege(string roleid, string[] functions)
{
int i = 0;
using (var context = new TopmepEntities())
{
//1.移除該角色所有權限
string sql = "DELETE FROM SYS_PRIVILEGE WHERE ROLE_ID=@roleid;";
i = context.Database.ExecuteSqlCommand(sql, new SqlParameter("roleid", roleid));
log.Info("Remove privilege count=" + i);
//2.逐一加入授權資料
for (int j = 0; j < functions.Length; j++)
{
SYS_PRIVILEGE p = new SYS_PRIVILEGE();
p.PRIVILEGE_ID = roleid + "-" + functions[j];
p.ROLE_ID = roleid;
p.FUNCTION_ID = functions[j];
context.SYS_PRIVILEGE.Add(p);
}
i = context.SaveChanges();
log.Info("create privlilege data count:" + i);
}
return(i);
}
//建立間接成本資料
public void createIndirectCost(string projectId, string userid)
{
List <SYS_PARA> lstItem = SystemParameter.getSystemPara("IndirectCostItem");
List <PLAN_INDIRECT_COST> lstIndirectCostItem = new List <PLAN_INDIRECT_COST>();
//取得合約金額
CostInfo.Revenue = getPlanRevenueById(projectId);
//取得間接成本項目
foreach (SYS_PARA p in lstItem)
{
PLAN_INDIRECT_COST it = new PLAN_INDIRECT_COST();
it.PROJECT_ID = projectId;
it.FIELD_ID = p.FIELD_ID;
it.FIELD_DESC = p.VALUE_FIELD;
it.PERCENTAGE = decimal.Parse(p.KEY_FIELD);
it.MODIFY_ID = userid;
it.MODIFY_DATE = DateTime.Now;
// System.Convert.ToDoublSystem.Math.Round(1.235, 2, MidpointRounding.AwayFromZero)
it.COST = Convert.ToDecimal(Math.Round(Convert.ToDouble(CostInfo.Revenue.PLAN_REVENUE * decimal.Parse(p.KEY_FIELD) / 100), 0, MidpointRounding.AwayFromZero));
log.Debug(p.VALUE_FIELD + " Indirect Cost=" + it.COST + ",per=" + p.KEY_FIELD);
lstIndirectCostItem.Add(it);
}
using (var context = new TopmepEntities())
{
///刪除現有資料
string sql = "DELETE FROM PLAN_INDIRECT_COST WHERE PROJECT_ID=@projectId";
log.Debug("sql=" + sql + ",projectid=" + projectId);
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectId", projectId));
context.Database.ExecuteSqlCommand(sql, parameters.ToArray());
log.Debug("sql=" + sql + ",projectid=" + projectId);
///將新資料存入
context.PLAN_INDIRECT_COST.AddRange(lstIndirectCostItem);
context.SaveChanges();
}
}
/// <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);
}