//public DataGridResult GetBillObtainList(SearchItem item)
//{
// string sql = $@"SELECT L.*,B.NAME BRANCHNAME,C.NAME MERCHANTNAME " +
// " FROM BILL_OBTAIN L,BRANCH B,MERCHANT C " +
// " WHERE L.BRANCHID = B.ID and L.MERCHANTID=C.MERCHANTID(+) " +
// " and L.TYPE= " + ((int)收款类型.账单收款).ToString();
// item.HasKey("TYPE", a => sql += $" and L.TYPE = {a}");
// item.HasKey("BILLID", a => sql += $" and L.BILLID = {a}");
// item.HasKey("STATUS", a => sql += $" and L.STATUS={a}");
// item.HasKey("REPORTER", a => sql += $" and L.REPORTER={a}");
// item.HasKey("REPORTER_TIME_START", a => sql += $" and L.REPORTER_TIME>={a}");
// item.HasKey("REPORTER_TIME_END", a => sql += $" and L.REPORTER_TIME<={a}");
// item.HasKey("VERIFY", a => sql += $" and L.VERIFY={a}");
// item.HasKey("VERIFY_TIME_START", a => sql += $" and L.VERIFY_TIME>={a}");
// item.HasKey("VERIFY_TIME_END", a => sql += $" and L.VERIFY_TIME<={a}");
// sql += " ORDER BY L.BILLID DESC";
// int count;
// DataTable dt = DbHelper.ExecuteTable(sql, item.PageInfo, out count);
// dt.NewEnumColumns<普通单据状态>("STATUS", "STATUSMC");
// return new DataGridResult(dt, count);
//}
public Tuple <dynamic, DataTable> GetBillObtainPrint(BILL_OBTAINEntity Data)
{
string sql = $@"SELECT A.BILLID,A.NIANYUE,A.ALL_MONEY,A.DESCRIPTION,A.BRANCHID,A.STATUS,A.ADVANCE_MONEY, "
+ "B.NAME BRANCHNAME,'('||A.MERCHANTID||')'||C.NAME MERCHANTNAME,F.NAME FKFS,B.ACCOUNT,B.BANK, "
+ "(select sum(L.MUST_MONEY) from BILL_OBTAIN_ITEM M,BILL L where M.BILLID=A.BILLID and M.FINAL_BILLID = L.BILLID) MUST_MONEY "
+ " FROM BILL_OBTAIN A,BRANCH B,MERCHANT C,FKFS F "
+ " WHERE A.BRANCHID=B.ID and A.MERCHANTID = C.MERCHANTID(+) "
+ "and A.FKFSID =F.ID ";
if (!Data.BILLID.IsEmpty())
{
sql += (" AND A.BILLID= " + Data.BILLID);
}
DataTable billObtain = DbHelper.ExecuteTable(sql);
billObtain.NewEnumColumns <普通单据状态>("STATUS", "STATUSMC");
string sqlitem = $@"SELECT M.*,B.NIANYUE,B.CONTRACTID,(B.MUST_MONEY-B.RECEIVE_MONEY) UNPAID_MONEY ,D.NAME TERMMC,B.YEARMONTH " +
" FROM BILL_OBTAIN_ITEM M ,BILL B,CONTRACT C,FEESUBJECT D " +
" where M.FINAL_BILLID=B.BILLID(+) and B.CONTRACTID=C.CONTRACTID(+) and B.TERMID=D.TRIMID(+)";
if (!Data.BILLID.IsEmpty())
{
sqlitem += (" and M.BILLID= " + Data.BILLID);
}
sqlitem += $" order by M.FINAL_BILLID";
DataTable billObtainItem = DbHelper.ExecuteTable(sqlitem);
return(new Tuple <dynamic, DataTable>(billObtain.ToOneLine(), billObtainItem));
}
public string SaveBillObtain(BILL_OBTAINEntity SaveData)
{
var v = GetVerify(SaveData);
if (SaveData.BILLID.IsEmpty())
{
SaveData.BILLID = NewINC("BILL_OBTAIN");
}
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.MERCHANTID);
v.Verify();
using (var Tran = DbHelper.BeginTransaction())
{
SaveData.BILL_OBTAIN_ITEM?.ForEach(item =>
{
GetVerify(item).Require(a => a.FINAL_BILLID);
});
//账单发票保存
SaveData.BILL_OBTAIN_INVOICE?.ForEach(item =>
{
GetVerify(item).Require(a => a.INVOICEID);
});
DbHelper.Save(SaveData);
Tran.Commit();
}
return(SaveData.BILLID);
}
public UIResult SearchBill_Obtain_Ysk(BILL_OBTAINEntity Data)
{
var res = service.JsglService.GetBillObtainElement(Data);
return(new UIResult(
new
{
billObtainYsk = res.Item1,
//billObtainYskItem = res.Item2
}
));
}
public UIResult SearchBill_Obtain(BILL_OBTAINEntity Data)
{
var res = service.JsglService.GetBillObtainElement(Data);
return(new UIResult(
new
{
billObtain = res.Item1,
billObtainItem = res.Item2,
billObtainInvoice = res.Item3,
}
));
}
public void DeleteBillObtain(List <BILL_OBTAINEntity> DeleteData)
{
foreach (var item in DeleteData)
{
BILL_OBTAINEntity 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();
}
}
/// <summary>
/// 保证金收取审核
/// </summary>
/// <param name="Data"></param>
/// <returns></returns>
public string ExecBillObtain(BILL_OBTAINEntity Data)
{
BILL_OBTAINEntity billObtain = DbHelper.Select(Data);
if (billObtain.STATUS == ((int)普通单据状态.审核).ToString())
{
throw new LogicException("单据(" + Data.BILLID + ")已经审核不能再次审核!");
}
using (var Tran = DbHelper.BeginTransaction())
{
Exec_BILL_OBTAIN exec_billobtain = new Exec_BILL_OBTAIN()
{
p_BILLID = Data.BILLID,
p_VERIFY = employee.Id
};
DbHelper.ExecuteProcedure(exec_billobtain);
Tran.Commit();
}
return(billObtain.BILLID);
}
public Tuple <dynamic, DataTable, DataTable> GetBillObtainElement(BILL_OBTAINEntity Data)
{
string sql = $@"SELECT A.*,B.NAME BRANCHNAME,C.NAME MERCHANTNAME,D.NAME FKFSNAME ,F.NAME FEE_ACCOUNT_NAME "
+ "FROM BILL_OBTAIN A,BRANCH B,MERCHANT C,FKFS D ,FEE_ACCOUNT F "
+ "WHERE A.BRANCHID=B.ID and A.MERCHANTID = C.MERCHANTID(+) "
+ " AND A.FKFSID=D.ID(+) AND A.FEE_ACCOUNT_ID=F.ID(+)";
if (!Data.BILLID.IsEmpty())
{
sql += (" AND A.BILLID= " + Data.BILLID);
}
DataTable billObtain = DbHelper.ExecuteTable(sql);
billObtain.NewEnumColumns <普通单据状态>("STATUS", "STATUSMC");
string sqlitem = $@"SELECT M.*,B.NIANYUE,B.CONTRACTID,(B.MUST_MONEY-B.RECEIVE_MONEY) UNPAID_MONEY ,D.NAME TERMMC,B.YEARMONTH " +
" FROM BILL_OBTAIN_ITEM M ,BILL B,CONTRACT C,FEESUBJECT D " +
" where M.FINAL_BILLID=B.BILLID(+) and B.CONTRACTID=C.CONTRACTID(+) and B.TERMID=D.TRIMID(+)";
if (!Data.BILLID.IsEmpty())
{
sqlitem += (" and M.BILLID= " + Data.BILLID);
}
DataTable billObtainItem = DbHelper.ExecuteTable(sqlitem);
//发票数据
string sqlinvoice = @"SELECT B.BILLID,B.TYPE BTYPE,M.NAME MERCHANTNAME,I.* FROM BILL_OBTAIN_INVOICE B,INVOICE I,MERCHANT M
WHERE B.INVOICEID=I.INVOICEID AND I.MERCHANTID=M.MERCHANTID";
if (!Data.BILLID.IsEmpty())
{
sqlinvoice += (" and B.BILLID= " + Data.BILLID);
}
DataTable billObtainInvoice = DbHelper.ExecuteTable(sqlinvoice);
billObtainInvoice.NewEnumColumns <发票类型>("TYPE", "TYPENAME");
return(new Tuple <dynamic, DataTable, DataTable>(billObtain.ToOneLine(), billObtainItem, billObtainInvoice));
}
public void ExecData(BILL_OBTAINEntity Data)
{
service.JsglService.ExecBillObtain(Data);
}
public string Save(BILL_OBTAINEntity SaveData)
{
return(service.JsglService.SaveBillObtain(SaveData));
}
