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));
}
