public Tuple <dynamic, DataTable> GetFloorShowMap(FLOORMAPSHOWEntity Data)
{
string sql = $@"SELECT NVL(MAX(MAPID),0) MAPID FROM FLOORMAP P WHERE TO_NUMBER(to_char(NVL(P.INITINATE_TIME,P.VERIFY_TIME),'YYYYMM'))<=" + Data.YEARMONTH
+ @" AND TO_NUMBER(to_char(NVL(P.TERMINATE_TIME,SYSDATE),'YYYYMM'))>= " + Data.YEARMONTH;
sql += " AND FLOORID= " + Data.FLOORID;
DataTable dtmapid = DbHelper.ExecuteTable(sql);
int mapid = Convert.ToInt32(dtmapid.Rows[0]["MAPID"].ToString());
if (mapid == 0)
{
throw new LogicException("没有符合条件的分析图纸!");
}
string sqlmap = $@"SELECT * FROM FLOORMAP WHERE 1=1 ";
sqlmap += (" AND MAPID= " + mapid);
DataTable floormap = DbHelper.ExecuteTable(sqlmap);
string sqlitem1 = $@"select SUM(A.AMOUNT), P.CATEGORYID, Y.CATEGORYNAME || '(' || SUM(B.AREA_RENTABLE) || ')' CATEGORYNAME, Y.CATEGORYCODE
, (SELECT COLOR FROM CATEGORYCOLOR R WHERE R.CATEGORYCODE = Y.CATEGORYCODE) COLOR
from CONTRACT_SUMMARY A, CONTRACT_SHOP P,CATEGORY Y, SHOP B, FLOORSHOP C, FLOORMAP D
where A.CONTRACTID = P.CONTRACTID AND A.SHOPID = P.SHOPID AND P.CATEGORYID = Y.CATEGORYID
AND A.YEARMONTH = 201810 AND A.SHOPID = B.SHOPID AND B.CODE = C.SHOPCODE
AND C.MAPID = D.MAPID AND B.FLOORID = D.FLOORID ";
sqlitem1 += (" and D.MAPID = " + mapid);
sqlitem1 += " GROUP BY P.CATEGORYID,Y.CATEGORYNAME,Y.CATEGORYCODE ";
DataTable floorcategory = DbHelper.ExecuteTable(sqlitem1);
return(new Tuple <dynamic, DataTable>(floormap.ToOneLine(), floorcategory));
}
public UIResult SearchFloorShowMap(FLOORMAPSHOWEntity Data)
{
var res = service.DpglService.GetFloorShowMap(Data);
return(new UIResult(
new
{
floormap = res.Item1,
floorshop = res.Item2
}
));
}
public UIResult SearchFloorShowMapData(FLOORMAPSHOWEntity Data)
{
var res = service.DpglService.GetGetFloorShowMapData(Data);
return(new UIResult(
new
{
floormap = res.Item1,
floorcategory = res.Item2,
floorshopdata = res.Item3,
floorshoprent_status = res.Item4
}
));
}
public Tuple <dynamic, DataTable, DataTable, DataTable> GetGetFloorShowMapData(FLOORMAPSHOWEntity Data)
{
string sql = $@"SELECT NVL(MAX(MAPID),0) MAPID FROM FLOORMAP P WHERE TO_NUMBER(to_char(NVL(P.INITINATE_TIME,P.VERIFY_TIME),'YYYYMM'))<=" + Data.YEARMONTH
+ @" AND TO_NUMBER(to_char(NVL(P.TERMINATE_TIME,SYSDATE),'YYYYMM'))>= " + Data.YEARMONTH;
sql += " AND FLOORID= " + Data.FLOORID;
DataTable dtmapid = DbHelper.ExecuteTable(sql);
int mapid = Convert.ToInt32(dtmapid.Rows[0]["MAPID"].ToString());
if (mapid == 0)
{
throw new LogicException("没有符合条件的分析图纸!");
}
string sqlmap = $@"SELECT * FROM FLOORMAP WHERE 1=1 ";
sqlmap += " AND MAPID= " + mapid;
DataTable floormap = DbHelper.ExecuteTable(sqlmap);
string sqlitem1 = $@"select SUM(A.AMOUNT), P.CATEGORYID, Y.CATEGORYNAME || '(' || SUM(B.AREA_RENTABLE) || ')' CATEGORYNAME, Y.CATEGORYCODE
, (SELECT COLOR FROM CATEGORYCOLOR R WHERE R.CATEGORYCODE = Y.CATEGORYCODE) COLOR
from CONTRACT_SUMMARY A, CONTRACT_SHOP P,CATEGORY Y, SHOP B, FLOORSHOP C, FLOORMAP D
where A.CONTRACTID = P.CONTRACTID AND A.SHOPID = P.SHOPID AND P.CATEGORYID = Y.CATEGORYID
AND A.YEARMONTH = " + Data.YEARMONTH + @" AND A.SHOPID = B.SHOPID AND B.CODE = C.SHOPCODE
AND C.MAPID = D.MAPID AND B.FLOORID = D.FLOORID ";
sqlitem1 += " and D.MAPID = " + mapid;
sqlitem1 += " GROUP BY P.CATEGORYID,Y.CATEGORYNAME,Y.CATEGORYCODE ";
DataTable floorcategory = DbHelper.ExecuteTable(sqlitem1);
string sqlitem2 = $@" select A.SHOPID,A.CONTRACTID,SUM(A.AMOUNT) AMOUNT
,(CASE P.AREA WHEN 0 THEN 0 ELSE ROUND(SUM(A.AMOUNT)/P.AREA,2) END) AMOUNTEFFECT
,TO_CHAR(T.CONT_START,'YYYY-MM-DD')||'至'||TO_CHAR(T.CONT_END,'YYYY-MM-DD') AS CONT_S_E,T.OPERATERULE,T.QZRQ,T.DESCRIPTION,T1.NAME MERCHANTNAME
,P.AREA RENTAREA,P.CATEGORYID,Y.CATEGORYNAME,Y.CATEGORYCODE
,B.CODE SHOPCODE,B.RENT_STATUS,C.P_X,C.P_Y
,(SELECT COLOR FROM CATEGORYCOLOR R WHERE R.CATEGORYCODE=Y.CATEGORYCODE) COLOR
from CONTRACT_SUMMARY A,CONTRACT T,MERCHANT T1,CONTRACT_SHOP P,CATEGORY Y,SHOP B, FLOORSHOP C,FLOORMAP D
where A.CONTRACTID=T.CONTRACTID AND A.MERCHANTID=T1.MERCHANTID AND A.CONTRACTID=P.CONTRACTID AND A.SHOPID=P.SHOPID AND P.CATEGORYID=Y.CATEGORYID
AND A.YEARMONTH=" + Data.YEARMONTH + @" AND A.SHOPID=B.SHOPID AND B.CODE=C.SHOPCODE
AND C.MAPID=D.MAPID AND B.FLOORID=D.FLOORID ";
sqlitem2 += " and D.MAPID = " + mapid;
sqlitem2 += " GROUP BY A.SHOPID ,A.CONTRACTID,T.CONT_START,T.CONT_END,T.OPERATERULE,T.QZRQ,T.DESCRIPTION ,T1.NAME,P.AREA,P.CATEGORYID,Y.CATEGORYNAME,Y.CATEGORYCODE,B.CODE,B.RENT_STATUS,C.P_X,C.P_Y ";
DataTable floorshop = DbHelper.ExecuteTable(sqlitem2);
floorshop.NewEnumColumns <租用状态>("RENT_STATUS", "STATUSMC");
floorshop.NewEnumColumns <合作方式>("OPERATERULE", "OPERATERULENAME");
string sqlitem3 = $@"select SUM(A.AMOUNT), B.RENT_STATUS , S.NAME || '(' || SUM(B.AREA_RENTABLE) || ')' STATUSNAME,S.COLOR
from CONTRACT_SUMMARY A, SHOP B,RENT_STATUS S, FLOORSHOP C, FLOORMAP D
where A.YEARMONTH = " + Data.YEARMONTH + @" AND A.SHOPID = B.SHOPID AND B.RENT_STATUS=S.RENT_STATUS(+) AND B.CODE = C.SHOPCODE
AND C.MAPID = D.MAPID AND B.FLOORID = D.FLOORID ";
sqlitem3 += " and D.MAPID = " + mapid;
sqlitem3 += " GROUP BY B.RENT_STATUS,S.NAME,S.COLOR ";
DataTable shoprent_status = DbHelper.ExecuteTable(sqlitem3);
return(new Tuple <dynamic, DataTable, DataTable, DataTable>(floormap.ToOneLine(), floorcategory, floorshop, shoprent_status));
}
