public Tuple <dynamic, DataTable> GetBillNoticeElement(BILL_NOTICEEntity Data) { string sql = $@"SELECT A.*,B.NAME BRANCHNAME,D.NAME MERCHANTNAME,F.NAME FEE_ACCOUNTNAME " + " FROM BILL_NOTICE A,BRANCH B,CONTRACT C,MERCHANT D,FEE_ACCOUNT F " + "WHERE A.BRANCHID=B.ID and A.CONTRACTID=C.CONTRACTID(+) and C.MERCHANTID=D.MERCHANTID(+)" + " AND A.FEE_ACCOUNTID=F.ID"; if (!Data.BILLID.IsEmpty()) { sql += (" AND A.BILLID= " + Data.BILLID); } DataTable billNotice = DbHelper.ExecuteTable(sql); billNotice.NewEnumColumns <普通单据状态>("STATUS", "STATUSMC"); billNotice.NewEnumColumns <通知单类型>("TYPE", "TYPEMC"); string sqlitem = $@"SELECT M.*,B.NIANYUE,B.MUST_MONEY,(B.MUST_MONEY-B.RECEIVE_MONEY) UNPAID_MONEY,C.NAME TERMMC " + " FROM BILL_NOTICE_ITEM M ,BILL B,FEESUBJECT C " + " where M.FINAL_BILLID=B.BILLID(+) and B.TERMID=C.TRIMID(+) "; if (!Data.BILLID.IsEmpty()) { sqlitem += (" and M.BILLID= " + Data.BILLID); } DataTable billNoticeItem = DbHelper.ExecuteTable(sqlitem); return(new Tuple <dynamic, DataTable>(billNotice.ToOneLine(), billNoticeItem)); }
public string SaveBillNotice(BILL_NOTICEEntity SaveData) { var v = GetVerify(SaveData); if (SaveData.BILLID.IsEmpty()) { SaveData.BILLID = NewINC("BILL_NOTICE"); } SaveData.STATUS = ((int)普通单据状态.未审核).ToString(); SaveData.REPORTER = employee.Id; SaveData.REPORTER_NAME = employee.Name; SaveData.REPORTER_TIME = DateTime.Now.ToString(); SaveData.VERIFY = employee.Id; v.Require(a => a.BILLID); v.Require(a => a.BRANCHID); v.Require(a => a.CONTRACTID); v.Require(a => a.TYPE); v.Require(a => a.FEE_ACCOUNTID); v.Verify(); using (var Tran = DbHelper.BeginTransaction()) { SaveData.BILL_NOTICE_ITEM?.ForEach(item => { GetVerify(item).Require(a => a.FINAL_BILLID); }); DbHelper.Save(SaveData); Tran.Commit(); } return(SaveData.BILLID); }
public UIResult SearchBill_Notice(BILL_NOTICEEntity Data) { var res = service.JsglService.GetBillNoticeElement(Data); return(new UIResult( new { billNotice = res.Item1, billNoticeItem = res.Item2 } )); }
public Tuple <dynamic, DataTable, DataTable> GetBillNoticePrint(BILL_NOTICEEntity Data) { string sql = $@"SELECT A.*,TO_CHAR(A.VERIFY_TIME,'YYYYMM') CZNY,B.NAME BRANCHNAME,'('||D.MERCHANTID||')'||D.NAME MERCHANTNAME," + " F.NAME PRINTNAME,F.BANK,F.ACCOUNT," + " substr(F.ADDRESS,1,instr(B.ADDRESS,';',-1)-1) ADDRESS1,substr(F.ADDRESS,instr(B.ADDRESS,';',-1)+1) ADDRESS2," + " (select min(S.SHOPDM) from CONTRACT_SHOPXX S where S.CONTRACTID=C.CONTRACTID) SHOPDM," + " (select min(BR.NAME) from CONTRACT_BRAND R,BRAND BR where R.BRANDID=BR.ID and R.CONTRACTID=C.CONTRACTID) BRANDNAME," + " (select sum(AREA_RENTABLE) from CONTRACT_SHOP S where S.CONTRACTID=C.CONTRACTID) AREA_RENTABLE," + " (select sum(Y.AMOUNT) from CONTRACT_SUMMARY Y where Y.CONTRACTID=C.CONTRACTID and Y.YEARMONTH=A.NIANYUE) AMOUNT," + " (select sum(RENTS) from CONTRACT_RENTITEM CR where CR.CONTRACTID=C.CONTRACTID and CR.YEARMONTH=A.NIANYUE) RENTS," + " (select SUM(Y.TCZJ) from CONTRACT_TCZJ Y where Y.CONTRACTID=C.CONTRACTID and Y.YEARMONTH=A.NIANYUE) KLZJ," + " (select sum(L.MUST_MONEY) from BILL_NOTICE_ITEM M,BILL L where M.BILLID=A.BILLID and M.FINAL_BILLID = L.BILLID) MUST_MONEY," + " (select sum(M.NOTICE_MONEY) from BILL_NOTICE_ITEM M,BILL L where M.BILLID=A.BILLID and M.FINAL_BILLID = L.BILLID) NOTICE_MONEY " + " FROM BILL_NOTICE A,BRANCH B,CONTRACT C,MERCHANT D,FEE_ACCOUNT F " + " WHERE A.BRANCHID=B.ID and A.CONTRACTID=C.CONTRACTID(+) " + " and C.MERCHANTID=D.MERCHANTID(+) and A.FEE_ACCOUNTID=F.ID(+)"; if (!Data.BILLID.IsEmpty()) { sql += (" AND A.BILLID= " + Data.BILLID); } DataTable billNotice = DbHelper.ExecuteTable(sql); billNotice.NewEnumColumns <普通单据状态>("STATUS", "STATUSMC"); // (case B.TYPE when 0 then '收费单' else '' end ) BILLTYPE, string sqlitem = $@"SELECT C.NAME TERMMC,TO_CHAR(B.START_DATE,'YYYY-MM-DD')||'至'||to_char(B.END_DATE,'YYYY-MM-DD') FYQJ," + " SUM(B.MUST_MONEY) MUST_MONEY,SUM(B.MUST_MONEY-B.RECEIVE_MONEY) UNPAID_MONEY,SUM(M.NOTICE_MONEY) NOTICE_MONEY" + " FROM BILL_NOTICE_ITEM M ,BILL B,FEESUBJECT C " + " where M.FINAL_BILLID=B.BILLID(+) and B.TERMID=C.TRIMID(+) "; if (!Data.BILLID.IsEmpty()) { sqlitem += (" and M.BILLID= " + Data.BILLID); } sqlitem += " GROUP BY C.NAME,TO_CHAR(B.START_DATE,'YYYY-MM-DD')||'至'||to_char(B.END_DATE,'YYYY-MM-DD')"; sqlitem += " ORDER BY 2"; DataTable billNoticeItem = DbHelper.ExecuteTable(sqlitem); //添加预收款余额明细 by:Dzk string sqlaccount = @"SELECT M.BALANCE FROM MERCHANT_ACCOUNT M ,CONTRACT C,BILL_NOTICE B WHERE C.MERCHANTID=M.MERCHANTID AND B.CONTRACTID=C.CONTRACTID AND M.FEE_ACCOUNT_ID=B.FEE_ACCOUNTID" ; if (!Data.BILLID.IsEmpty()) { sqlaccount += (" AND B.BILLID= " + Data.BILLID); } DataTable DTA = DbHelper.ExecuteTable(sqlaccount); return(new Tuple <dynamic, DataTable, DataTable>(billNotice.ToOneLine(), billNoticeItem, DTA)); }
/// <summary> /// 缴费通知单审核 /// </summary> /// <param name="Data"></param> /// <returns></returns> public string ExecBillNotice(BILL_NOTICEEntity Data) { BILL_NOTICEEntity billNotice = DbHelper.Select(Data); if (billNotice.STATUS == ((int)普通单据状态.审核).ToString()) { throw new LogicException("单据(" + Data.BILLID + ")已经审核不能再次审核!"); } using (var Tran = DbHelper.BeginTransaction()) { billNotice.VERIFY = employee.Id; billNotice.VERIFY_NAME = employee.Name; billNotice.VERIFY_TIME = DateTime.Now.ToString(); billNotice.STATUS = ((int)普通单据状态.审核).ToString(); DbHelper.Save(billNotice); Tran.Commit(); } return(billNotice.BILLID); }
public void DeleteBillNotice(List <BILL_NOTICEEntity> DeleteData) { foreach (var item in DeleteData) { BILL_NOTICEEntity Data = DbHelper.Select(item); if (Data.STATUS == ((int)普通单据状态.审核).ToString()) { throw new LogicException("已经审核不能删除!"); } } using (var Tran = DbHelper.BeginTransaction()) { foreach (var item in DeleteData) { DbHelper.Delete(item); } Tran.Commit(); } }
public void ExecData(BILL_NOTICEEntity Data) { service.JsglService.ExecBillNotice(Data); }
public string Save(BILL_NOTICEEntity SaveData) { return(service.JsglService.SaveBillNotice(SaveData)); }