/// <summary>
/// 出户操作
/// </summary>
/// <param name="room_id"></param>
/// <param name="owner_id"></param>
public void SaveOutFrom(string room_id, string owner_id)
{
RoomEntity rom = new RoomEntity();
rom.is_owner = 0;
rom.owner_id = null;
rom.room_id = room_id;
this.BaseRepository().Update(rom);
RepositoryFactory <OwnerEntity> repository = new RepositoryFactory <OwnerEntity>();
OwnerEntity own = new OwnerEntity();
own.owner_id = owner_id;
own.out_date = DateTime.Now;
repository.BaseRepository().Update(own);
}
/// <summary>
/// 获取出库汇总
/// </summary>
/// <returns></returns>
public IEnumerable <OutInModel> GetOutList()
{
RepositoryFactory <OutInModel> repository = new RepositoryFactory <OutInModel>();
var strSql = new StringBuilder();
strSql.Append(@" SELECT SUM(tab.fmoney) AS amount,tab.years,tab.months FROM ( ");
strSql.Append(@" SELECT fmoney=sum(a.fmoney),YEAR(b.foutdate) AS years,MONTH(b.foutdate) AS months ");
strSql.Append(@" FROM tb_wh_outbill_item a ");
strSql.Append(@" LEFT join tb_wh_outbill b on a.foutbillid = b.foutbillid ");
strSql.Append(@" WHERE b.foutdate IS NOT NULL ");
strSql.Append(@" AND (YEAR(b.foutdate)=YEAR(DATEADD(MONTH,-13,GETDATE())) AND MONTH(b.foutdate)>=MONTH(DATEADD(MONTH,-13,GETDATE()))) ");
strSql.Append(@" OR (YEAR(b.foutdate)=YEAR(GETDATE()) AND MONTH(b.foutdate)<=MONTH(DATEADD(MONTH,-1,GETDATE()))) ");
strSql.Append(@" GROUP BY b.foutdate)tab ");
strSql.Append(@" GROUP BY tab.years,tab.months ");
strSql.Append(@" ORDER BY tab.years ");
return(repository.BaseRepository().FindList(strSql.ToString()));
}
public bool CheckArea(Sxc_AgentEntity current, Sxc_AgentEntity parent)
{
//没有地理信息
if (current.Area_ID == null)
{
return(true);
}
RepositoryFactory <Sxc_Base_AreaEntity> arearf = new RepositoryFactory <Sxc_Base_AreaEntity>();
var arearep = arearf.BaseRepository(DbName);
var carea3 = arearep.FindEntity(current.Area_ID);
var carea2 = arearep.FindEntity(carea3.PID);
var carea1 = arearep.FindEntity(carea2.PID);
var parea3 = arearep.FindEntity(parent.Area_ID);
//var parea2 = arearep.FindEntity(parea3.PID);
//var parea1 = arearep.FindEntity(parea2.PID);
Sxc_Base_AreaEntity parea2 = null;
Sxc_Base_AreaEntity parea1 = null;
if (parent.Level >= 3 && carea3.ID != parea3.ID)
{
return(false);
}
if (parent.Level == 2)
{
parea2 = arearep.FindEntity(parea3.PID);
if (carea2.ID != parea2.ID)
{
return(false);
}
}
if (parent.Level == 1)
{
parea2 = arearep.FindEntity(parea3.PID);
parea1 = arearep.FindEntity(parea2.PID);
if (carea1.ID != parea1.ID)
{
return(false);
}
}
return(true);
}
/// <summary>
/// 获取打印的保养单列表
/// </summary>
/// <param name="fnumbers"></param>
/// <returns></returns>
public IEnumerable <DeviceRecordModel> GetPrintList(string fnumbers)
{
string sql = @"SELECT deviceRecord.fnumber ,
deviceRecord.fplanyear ,
deviceRecord.fplanmonth ,
device.d_number ,
device.d_name ,
device.d_standard ,
device.d_place ,
devicepart.p_number ,
devicepart.p_name ,
devicepart.p_standard ,
devicepart.p_place ,
lastrecord.fdate lastdate ,
lastrecord.fman lastman ,
lastrecord.finfo lastinfo ,
maintence.maintencename ,
maintence.maintence ,
property.property_name ,
item.itemname
FROM wy_device_record deviceRecord
LEFT JOIN wy_device_part devicepart ON devicepart.p_number = deviceRecord.fpartnumber
LEFT JOIN dbo.wy_device device ON device.d_number = devicepart.d_devicenumber
LEFT JOIN wy_device_plan deviceplan ON deviceplan.planid = deviceRecord.fplanid
LEFT JOIN wy_Device_maintence maintence ON maintence.classid = deviceplan.classid
LEFT JOIN ( SELECT MAX(frecordid) frecordid ,
fpartnumber
FROM wy_device_record
WHERE fstatusid = 1
GROUP BY fpartnumber
) temp_lastRecordID ON temp_lastRecordID.fpartnumber = deviceRecord.fpartnumber
LEFT JOIN wy_device_record lastrecord ON lastrecord.frecordid = temp_lastRecordID.frecordid
LEFT JOIN dbo.wy_property property ON property.property_id = deviceRecord.propertyid
LEFT JOIN sys_dictitem item ON item.itemid = deviceRecord.fgroupid
WHERE deviceRecord.propertyid = @propertyid and deviceRecord.fnumber in(" + fnumbers + @")
ORDER BY deviceRecord.fnumber ASC";
var parameter = new List <DbParameter>();
string propertyid = Utils.GetCookie("property_id");
parameter.Add(DbParameters.CreateDbParameter("@propertyid", propertyid));
RepositoryFactory <DeviceRecordModel> repository = new RepositoryFactory <DeviceRecordModel>();
return(repository.BaseRepository().FindList(sql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="contractid">租赁合同ID</param>
/// <returns>返回列表</returns>
public IEnumerable <RentfeeitemListEntity> GetLists(string contractid)
{
RepositoryFactory <RentfeeitemListEntity> repository = new RepositoryFactory <RentfeeitemListEntity>();
var StrSql = new StringBuilder();
StrSql.Append("select rf.*,f.feeitem_name ");
StrSql.Append("FROM wy_rentfeeitem rf ");
StrSql.Append("LEFT join wy_feeitem f on rf.feeitemid=f.feeitem_id ");
StrSql.Append("WHERE rf.contractid=@contractid ");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@contractid", contractid)
};
return(repository.BaseRepository().FindList(StrSql.ToString(), parameter));
}
/// <summary>
/// 获取其他收入列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="property_id">物业编号</param>
/// <param name="queryJson">参数</param>
/// <returns></returns>
public IEnumerable <OtherincomeitemListEntity> GetOtherList(Pagination pagination, string property_id, string queryJson)
{
var queryParam = queryJson.ToJObject();
var strSql = new StringBuilder();
strSql.Append(@"select wo.incomeid,wo.customer,wo.feedate,wo.feemoney,ticketid,wo.operateuser,wo.inputtime,wf.ticket_code
FROM wy_otherincome wo
LEFT join wy_feeticket wf on wo.ticketid=wf.ticket_id
WHERE wo.property_id=@property_id ");
var parameter = new List <DbParameter>();
parameter.Add(DbParameters.CreateDbParameter("@property_id", property_id));
if (!queryParam["customer"].IsEmpty())
{
string customer = queryParam["customer"].ToString();
strSql.AppendFormat(" AND wo.customer like '%{0}%' ", customer);
}
if (!queryParam["ticketid"].IsEmpty() && queryParam["ticketid"].ToString() != "0")
{
string ticketid = queryParam["ticketid"].ToString();
strSql.Append(" AND wf.ticket_id=@ticketid ");
parameter.Add(DbParameters.CreateDbParameter("@ticketid", ticketid));
}
if (!queryParam["feeitem_id"].IsEmpty())
{
string feeitem_id = queryParam["feeitem_id"].ToString();
strSql.Append(" AND wo.incomeid IN (SELECT wm.incomeid FROM dbo.wy_otherincomeitem wm WHERE wm.feeitem_id=@feeitem_id) ");
parameter.Add(DbParameters.CreateDbParameter("@feeitem_id", feeitem_id));
}
if (!queryParam["state"].IsEmpty() && !queryParam["end"].IsEmpty())
{
string state = queryParam["state"].ToString();
string end = queryParam["end"].ToString();
strSql.Append(" AND feedate>=@state and feedate<=@end ");
parameter.Add(DbParameters.CreateDbParameter("@state", state));
parameter.Add(DbParameters.CreateDbParameter("@end", end));
}
RepositoryFactory <OtherincomeitemListEntity> repository = new RepositoryFactory <OtherincomeitemListEntity>();
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray(), pagination).OrderByDescending(t => t.feedate));
}
/// <summary>
/// 获取最大编号
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <param name="sqlwhere"></param>
/// <returns></returns>
public static int GetMaxID_Where(string FieldName, string TableName, string sqlwhere)
{
RepositoryFactory <DeviceModel> rep = new RepositoryFactory <DeviceModel>();
string sqlStr = "select ISNULL(max(" + FieldName + "),0)+1 from " + TableName;
if (sqlwhere != string.Empty)
{
sqlStr = sqlStr + " where " + sqlwhere;
}
object single = rep.BaseRepository().FindObject(sqlStr);
if (single == null)
{
return(1);
}
return(int.Parse(single.ToString()));
}
/// <summary>
/// 获取其他收入明细列表
/// </summary>
/// <param name="incomeid"></param>
/// <returns></returns>
public IEnumerable <OtherincomeitemEntity> GetOtherDetailList(string incomeid)
{
var strSql = new StringBuilder();
strSql.Append(@"SELECT wm.incomeitem_id,wm.incomeid,wf.feeitem_name AS feeitem_id,wm.subject,wm.fee_income
FROM wy_otherincomeitem wm
LEFT JOIN dbo.wy_feeitem wf ON wm.feeitem_id=wf.feeitem_id
WHERE wm.incomeid=@incomeid ");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@incomeid", incomeid)
};
RepositoryFactory <OtherincomeitemEntity> repository = new RepositoryFactory <OtherincomeitemEntity>();
return(repository.BaseRepository().FindList(strSql.ToString(), parameter));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <MonthcheckModel> GetList(string queryJson)
{
RepositoryFactory <MonthcheckModel> repository = new RepositoryFactory <MonthcheckModel>();
var strSql = new StringBuilder();
strSql.Append(@"SELECT a.ftypecode,a.fyear,a.fmonth
,CAST(round(a.fbeginmoney,2) as numeric(20,2))fbeginmoney
,CAST(round(a.finmoney,2) as numeric(20,2))finmoney
,CAST(round(a.foutmoney,2) as numeric(20,2))foutmoney
,CAST(round(a.fendmoney,2) as numeric(20,2))fendmoney
,a.fbegindate,a.fenddate,b.ftypename from tb_wh_monthcheck a
LEFT join tb_wh_goodstype b on b.ftypecode=a.ftypecode where 1=1 ");
var parameter = new List <DbParameter>();
var queryParam = queryJson.ToJObject();
#region 年份和月份查询
//查询条件 年份
if (!queryParam["StartDate"].IsEmpty())
{
string StartDate = queryParam["StartDate"].ToString();
strSql.Append(" and a.fyear=@StartDate ");
parameter.Add(DbParameters.CreateDbParameter("@StartDate", StartDate));
}
//月份
if (!queryParam["EndDate"].IsEmpty())
{
string EndDate = queryParam["EndDate"].ToString();
strSql.Append(" and a.fmonth=@EndDate ");
parameter.Add(DbParameters.CreateDbParameter("@EndDate", EndDate));
}
#endregion 年份和月份查询
//数据量大, 如若未选择年月查询，则默认不显示数据
if (queryParam.Count < 1)
{
return(null);
}
else
{
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray()));
}
}
/// <summary>
/// 获取房间信息
/// </summary>
/// <param name="room_id">房间编号</param>
/// <param name="property_id">物业编号</param>
/// <returns></returns>
public IEnumerable <RoomModelEntity> GetInfo(string room_id, string property_id)
{
RepositoryFactory <RoomModelEntity> repository = new RepositoryFactory <RoomModelEntity>();
var StrSql = new StringBuilder();
StrSql.Append("SELECT r.*,bg.building_name,ow.owner_name ");
StrSql.Append("FROM wy_room r ");
StrSql.Append("LEFT JOIN wy_building bg ON r.building_id=bg.building_id ");
StrSql.Append("LEFT JOIN wy_owner ow ON r.owner_id=ow.owner_id ");
StrSql.Append("WHERE r.room_id=@room_id AND r.property_id=@property_id ");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@room_id", room_id),
DbParameters.CreateDbParameter("@property_id", property_id)
};
return(repository.BaseRepository().FindList(StrSql.ToString(), parameter));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <wy_device_contractEntity> GetList(string queryJson)
{
RepositoryFactory <wy_device_contractEntity> repository = new RepositoryFactory <wy_device_contractEntity>();
var queryParam = queryJson.ToJObject();
var strSql = new StringBuilder();
var parameter = new List <DbParameter>();
strSql.Append("select * from wy_device_contract where 1=1");
if (!queryParam["devicenumber"].IsEmpty())
{
string devicenumber = queryParam["devicenumber"].ToString();
strSql.Append(" and devicenumber=@devicenumber");
parameter.Add(DbParameters.CreateDbParameter("@devicenumber", devicenumber));
}
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="owner_id">业主编号</param>
/// <param name="room_id">房屋编号</param>
/// <returns></returns>
public IEnumerable <OwnerFeeModelEntity> GetList(string owner_id, string room_id)
{
RepositoryFactory <OwnerFeeModelEntity> repository = new RepositoryFactory <OwnerFeeModelEntity>();
var StrSql = new StringBuilder();
StrSql.Append("SELECT F.*,ow.owner_name,i.feeitem_name ");
StrSql.Append("FROM wy_owner_fee F ");
StrSql.Append("LEFT join wy_feeitem I on F.fee_code = I.feeitem_id ");
StrSql.Append("LEFT JOIN wy_owner ow ON f.owner_id=ow.owner_id ");
StrSql.Append("WHERE F.owner_id=@owner_id and F.room_id=@room_id ");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@owner_id", owner_id),
DbParameters.CreateDbParameter("@room_id", room_id)
};
return(repository.BaseRepository().FindList(StrSql.ToString(), parameter).OrderByDescending(t => t.start_date));
}
/// <summary>
/// 获取应收收入明细
/// </summary>
/// <param name="receive_id">实收编号</param>
/// <param name="type">0 业主 1租户</param>
/// <returns></returns>
public IEnumerable <OtherincomeFromEntity.OtherFeereceiveListEntity> GetListByOther(string receive_id, int type)
{
var strSql = new StringBuilder();
if (type == 1)
{
strSql.Append(@"SELECT woc.fee_income AS check_money ,
CONVERT(VARCHAR(10), wo.feedate, 120) pay_enddate ,
woc.subject ,
we.feeitem_name
FROM wy_otherincome wo
LEFT JOIN wy_property wp ON wo.property_id = wp.property_id
LEFT JOIN wy_otherincomeitem woc ON wo.incomeid = woc.incomeid
LEFT JOIN wy_feeticket wf ON wo.ticketid = wf.ticket_id
LEFT JOIN wy_feeitem we ON woc.feeitem_id = we.feeitem_id
WHERE wo.ticketid = @receive_id ");
}
else
{
strSql.Append(@"SELECT wy_feecheck.* ,
CONVERT(VARCHAR(10), wy_feeincome.pay_enddate, 120) pay_enddate ,
( CAST(wy_feeincome.fee_year AS VARCHAR) + '/' + CAST(wy_feeincome.fee_month AS VARCHAR) ) AS subject ,
( SELECT TOP 1
feeitem_name
FROM wy_feeitem
WHERE feeitem_id = wy_feeincome.feeitem_id
) AS feeitem_name
FROM wy_feecheck
LEFT JOIN wy_feeincome ON wy_feecheck.income_id = wy_feeincome.income_id
WHERE receive_id IN ( SELECT receive_id
FROM wy_feereceive
WHERE ticket_id = @receive_id ) ");
}
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@receive_id", receive_id)
};
RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveListEntity> repository = new RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveListEntity>();
return(repository.BaseRepository().FindList(strSql.ToString(), parameter));
}
/// <summary>
/// 获取业主缴费查询明细实体
/// </summary>
/// <param name="receive_id">实收编号</param>
/// <param name="type">0 业主 1租户</param>
/// <returns></returns>
public OtherincomeFromEntity.OtherFeereceiveFromEntity GetEntitys(string receive_id, int type)
{
var strSql = new StringBuilder();
if (type == 1)
{
strSql.Append(@"SELECT wo.incomeid AS receive_id,wo.property_id,wo.customer AS ownername,'' AS customername,wp.property_name AS propertyname
,CONVERT(VARCHAR(10),wo.feedate,120)receive_date,woc.fee_income AS fee_money,wf.ticket_code,wo.currency AS pay_mode
FROM wy_otherincome wo
LEFT JOIN wy_property wp ON wo.property_id=wp.property_id
LEFT join wy_otherincomeitem woc on wo.incomeid=woc.incomeid
LEFT join wy_feeticket wf on wo.ticketid=wf.ticket_id
WHERE wo.incomeid=@receive_id ");
}
else
{
strSql.Append(@"select wf.receive_id,wf.property_id
,ISNULL((select top 1 customername from wy_rentcontract where contractid=wf.rentcontract_id),(select owner_name from wy_owner where owner_id=wf.owner_id)) AS ownername
,(wp.property_name+'/'+ISNULL((select top 1 building_name from wy_building where building_id =(select top 1 building_id from wy_room where room_id=wf.room_id)),'')
+ '/' + ISNULL((select top 1 room_name from wy_room where room_id=wf.room_id),''))as propertyname
,CONVERT(VARCHAR(10),wf.receive_date,120)receive_date,wf.fee_money
,(select top 1 ticket_code from wy_feeticket where ticket_id=wf.ticket_id) as ticket_code, pay_mode
FROM wy_feereceive wf
LEFT JOIN wy_property wp ON wf.property_id=wp.property_id
WHERE receive_id=@receive_id ");
}
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@receive_id", receive_id)
};
RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveFromEntity> repository = new RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveFromEntity>();
DataTable data = repository.BaseRepository().FindTable(strSql.ToString(), parameter);
if (data != null && data.Rows.Count > 0)
{
return(DataHelper.CreateItem <OtherincomeFromEntity.OtherFeereceiveFromEntity>(data.Rows[0]));
}
return(null);
}
/// <summary>
/// 保存表单（新增、修改）
/// </summary>
/// <param name="keyValue">主键值</param>
/// <param name="entity">实体对象</param>
/// <returns></returns>
public void SaveForm(string keyValue, Sxc_UserProfileEntity entity)
{
if (!string.IsNullOrEmpty(keyValue))
{
entity.Modify(keyValue);
this.BaseRepository(DbName).Update(entity);
RepositoryFactory <Sxc_UserEntity> rf = new RepositoryFactory <Sxc_UserEntity>();
var db = rf.BaseRepository(DbName);
var user = db.FindEntity(entity.ID);
user.SystemAccount = entity.SystemAccount;
db.Update(user);
}
else
{
entity.Create();
this.BaseRepository(DbName).Insert(entity);
}
}
/// <summary>
///
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <param name="pos"></param>
/// <returns></returns>
public static string GetMaxID_String(string FieldName, string TableName, int pos)
{
RepositoryFactory <DeviceModel> rep = new RepositoryFactory <DeviceModel>();
int num;
object single = rep.BaseRepository().FindObject("select max(" + FieldName + ")+1 from " + TableName);
if (single == null)
{
num = 1;
}
else
{
num = int.Parse(single.ToString());
}
string str2 = num.ToString();
for (int i = 1; i <= pos; i++)
{
str2 = "0" + str2;
}
return(str2.Substring(str2.Length - pos));
}
/// <summary>
/// 获取报表
/// </summary>
/// <param name="fgoodsid"></param>
/// <param name="queryJson"></param>
/// <returns></returns>
public IEnumerable <BillReportModel> GetMonthInbill(List <string> fgoodsid, string queryJson)
{
if (fgoodsid == null || fgoodsid.Count == 0)
{
return(null);
}
StringBuilder ids = new StringBuilder();
foreach (var item in fgoodsid)
{
ids.AppendFormat("'{0}',", item);
}
string id = ids.ToString().Substring(0, ids.Length - 1);
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"SELECT ISNULL(SUM(item.fnumber), 0) tot_number ,
ISNULL(SUM(item.fmoney), 0) tot_money ,
item.fgoodsid ,
DATEPART(MONTH, bill.findate) mon
FROM dbo.tb_wh_inbill bill
LEFT JOIN dbo.tb_wh_inbill_item item ON bill.finbillid = item.finbillid
WHERE DATEPART(YEAR, bill.findate) = @year AND DATEPART(MONTH, bill.findate) = @month
AND item.fgoodsid IN ({0})
GROUP BY item.fgoodsid ,
bill.findate", id);
var queryParam = queryJson.ToJObject();
List <DbParameter> param = new List <DbParameter>();
param.Add(DbParameters.CreateDbParameter("@year", queryParam["year"]));
param.Add(DbParameters.CreateDbParameter("@month", queryParam["month"]));
RepositoryFactory <BillReportModel> repository = new RepositoryFactory <BillReportModel>();
return(repository.BaseRepository().FindList(sql.ToString(), param.ToArray(), null));
}
/// <summary>
/// 获取空房间信息（用于下载批量导出数据）
/// </summary>
/// <param name="property_id">物业编号</param>
/// <param name="building_id">归属楼栋</param>
/// <returns></returns>
public DataTable GetRoomInfo(string property_id, string building_id)
{
var strSql = new StringBuilder();
strSql.Append(@"SELECT r.room_id,b.building_name,r.room_name,r.building_dim,r.room_dim,r.room_number,'' AS owner_name
,'' AS owner_tel,'身份证' AS owner_cardtype,'' AS owner_cardno,'' AS in_date,'' AS sign_date,'' AS link1_name,'' AS link1_tel,'' AS link1_mark,'' AS link2_name,'' AS link2_tel,'' AS link2_mark,'' AS remark
FROM wy_Room r
INNER JOIN dbo.wy_building b ON r.building_id=b.building_id
WHERE (is_owner=0 OR is_owner IS NULL)
AND r.property_id=@property_id
AND r.building_id=@building_id
ORDER BY r.room_name ASC,r.room_number ASC");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@property_id", property_id),
DbParameters.CreateDbParameter("@building_id", building_id)
};
RepositoryFactory <FeeManageEntity> repository = new RepositoryFactory <FeeManageEntity>();
return(repository.BaseRepository().FindTable(strSql.ToString(), parameter));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <billItemModel> GetOutList(Pagination pagination, string queryJson)
{
RepositoryFactory <billItemModel> repository = new RepositoryFactory <billItemModel>();
var strSql = new StringBuilder();
strSql.Append(@" select a.fgoodsid as fgoodsid,fnumber=sum(a.fnumber),fmoney=sum(a.fmoney), ");
strSql.Append(@" fname=(select fname from tb_wh_goodsinfo where tb_wh_goodsinfo.fgoodsid=a.fgoodsid), ");
strSql.Append(@" funit=(select funit from tb_wh_goodsinfo where tb_wh_goodsinfo.fgoodsid=a.fgoodsid) ");
strSql.Append(@" FROM tb_wh_outbill_item a ");
strSql.Append(@" LEFT join tb_wh_outbill b on a.foutbillid = b.foutbillid ");
strSql.Append(@" LEFT join hr_department c on b.fdeptid = c.deptid where 1=1 ");
var parameter = new List <DbParameter>();
var queryParam = queryJson.ToJObject();
//查询条件 领用时间
if (!queryParam["StartDate"].IsEmpty())
{
string StartDate = queryParam["StartDate"].ToString();
strSql.Append(" and b.foutdate>=@StartDate ");
parameter.Add(DbParameters.CreateDbParameter("@StartDate", StartDate));
}
if (!queryParam["EndDate"].IsEmpty())
{
string EndDate = queryParam["EndDate"].ToString();
strSql.Append(" and b.foutdate<=@EndDate ");
parameter.Add(DbParameters.CreateDbParameter("@EndDate", EndDate));
}
if (!queryParam["DeptName"].IsEmpty())
{
string DeptName = queryParam["DeptName"].ToString();
strSql.Append(" and c.deptid=@DeptName ");
parameter.Add(DbParameters.CreateDbParameter("@DeptName", DeptName));
}
strSql.Append(@" GROUP by a.fgoodsid ");
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray(), pagination));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <DevicePlanModel> GetList(string queryJson)
{
RepositoryFactory <DevicePlanModel> repository = new RepositoryFactory <DevicePlanModel>();
var queryParam = queryJson.ToJObject();
var strSql = new StringBuilder();
var parameter = new List <DbParameter>();
strSql.Append("select * from wy_device_plan deviceplan inner join wy_Device_maintence maintence on maintence.classid=deviceplan.classid");
if (!queryParam["p_number"].IsEmpty())
{
string p_number = queryParam["p_number"].ToString();
strSql.Append(" and deviceplan.p_number=@p_number");
parameter.Add(DbParameters.CreateDbParameter("@p_number", p_number));
}
if (!queryParam["planid"].IsEmpty())
{
string planid = queryParam["planid"].ToString();
strSql.Append(" and deviceplan.planid=@planid");
parameter.Add(DbParameters.CreateDbParameter("@planid", planid));
}
if (!queryParam["fmonth"].IsEmpty())
{
string fmonth = queryParam["fmonth"].ToString();
strSql.Append(" and deviceplan.fmonth=@fmonth");
parameter.Add(DbParameters.CreateDbParameter("@fmonth", fmonth));
}
if (!queryParam["fyear"].IsEmpty())
{
string fyear = queryParam["fyear"].ToString();
strSql.Append(" and deviceplan.fyear=@fyear");
parameter.Add(DbParameters.CreateDbParameter("@fyear", fyear));
}
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 月销量库存
/// </summary>
/// <param name="pagination"></param>
/// <param name="queryJson"></param>
/// <returns></returns>
public IEnumerable <MonthGoodsModel> GetMonthDetailListJson(Pagination pagination, string queryJson)
{
var queryParam = queryJson.ToJObject();
List <DbParameter> param = new List <DbParameter>();
param.Add(DbParameters.CreateDbParameter("@fyear", queryParam["startDate"].ToString()));
param.Add(DbParameters.CreateDbParameter("@fmonth", queryParam["endDate"].ToString()));
RepositoryFactory <MonthGoodsModel> repository = new RepositoryFactory <MonthGoodsModel>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT tb_wh_monthgoods.fgoodsid ,
tb_wh_goodsinfo.funit ,
tb_wh_goodsinfo.fname ,
tb_wh_monthgoods.fcount ,
tb_wh_monthgoods.fprice ,
tb_wh_monthgoods.fmoney
FROM tb_wh_monthgoods
LEFT JOIN dbo.tb_wh_goodsinfo ON tb_wh_goodsinfo.fgoodsid = tb_wh_monthgoods.fgoodsid
WHERE fyear = @fyear
AND fmonth = @fmonth");
return(repository.BaseRepository().FindList(strSql.ToString(), param.ToArray(), pagination));
}
/// <summary>
/// 保存表单（新增、修改）
/// </summary>
/// <param name="keyValue">主键值</param>
/// <param name="entity">实体对象</param>
/// <returns></returns>
public string SaveForm(string keyValue, OwnerEntity entity)
{
if (!string.IsNullOrEmpty(keyValue))
{
entity.Modify(keyValue);
return(this.BaseRepository().Update(entity).ToString());
}
else
{
//entity.Create();
entity.owner_id = entity.property_id + GetMaxID(8);
this.BaseRepository().Insert(entity);
RepositoryFactory <RoomEntity> repository = new RepositoryFactory <RoomEntity>();
RoomEntity rom = new RoomEntity();
rom.is_owner = 1;
rom.owner_id = entity.owner_id;
rom.room_id = entity.room_id;
repository.BaseRepository().Update(rom);
return(entity.owner_id);
}
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="keyValue">主键值</param>
/// <returns></returns>
public GoodstypeEntity GetEntity(string keyValue)
{
RepositoryFactory <GoodstypeEntity> repository = new RepositoryFactory <GoodstypeEntity>();
return(repository.BaseRepository().FindEntity(keyValue));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <GoodstypeEntity> GetList()
{
RepositoryFactory <GoodstypeEntity> repository = new RepositoryFactory <GoodstypeEntity>();
return(repository.BaseRepository().IQueryable().ToList());
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="queryJson">查询参数</param>
/// <returns>返回分页列表</returns>
public IEnumerable <GoodstypeEntity> GetPageList(Pagination pagination, string queryJson)
{
RepositoryFactory <GoodstypeEntity> repository = new RepositoryFactory <GoodstypeEntity>();
return(repository.BaseRepository().FindList(pagination));
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="keyValue">主键</param>
public void RemoveForm(string keyValue)
{
RepositoryFactory <WeekplanEntity> repository = new RepositoryFactory <WeekplanEntity>();
repository.BaseRepository().Delete(keyValue);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="queryJson">查询参数</param>
/// <returns>返回分页列表</returns>
public IEnumerable <WeekplanModel> GetPageList(Pagination pagination, string queryJson)
{
RepositoryFactory <WeekplanModel> repository = new RepositoryFactory <WeekplanModel>();
string UserName = OperatorProvider.Provider.Current().UserName; //获取当前用户名 根据用户名去查询是否在部门是管理身份
string checkdep = OperatorProvider.Provider.Current().ManageDepartment; //Checkmanager(UserName); //是某个部门的管理者身份
var strSql = new StringBuilder();
var queryParam = queryJson.ToJObject();
if (OperatorProvider.Provider.Current().IsSystem || OperatorProvider.Provider.Current().IsLeader == 1)
{
// 是管理者身份，全部
strSql.Append(@"SELECT ow.*,u.TrueName AS TrueName,ow.ifcheck AS checks FROM dbo.oa_weekplan AS ow
INNER JOIN dbo.Accounts_Users AS u ON ow.userid=u.UserID
WHERE 1=1 ");
}
else if (!string.IsNullOrEmpty(checkdep))
{
string[] checkdeps = checkdep.Split(',');
string dep = "";
foreach (var item in checkdeps)
{
dep += "'" + item + "',";
}
if (!string.IsNullOrEmpty(dep))
{
dep = dep.Substring(0, (dep.Length - 1));
}
// 部门的管理者身份
string OldSystemUserID = OperatorProvider.Provider.Current().OldSystemUserID;
strSql.Append(@" SELECT ow.*,u.TrueName AS TrueName,ow.ifcheck AS checks FROM dbo.oa_weekplan AS ow
INNER JOIN dbo.Accounts_Users AS u ON ow.userid=u.UserID
WHERE u.DepartmentID IN (" + dep + ") ");
}
else
{
//只显示自己的周报
string UserId = OperatorProvider.Provider.Current().OldSystemUserID;
strSql.Append(@"SELECT o.*,u.TrueName AS TrueName,o.ifcheck AS checks
FROM oa_weekplan o
LEFT JOIN dbo.Accounts_Users u ON o.userid=u.UserID
where o.userid='" + UserId + "'");
}
//查询条件 供应商名称、购买物品
if (!queryParam["owner_name"].IsEmpty())
{
string name = queryParam["owner_name"].ToString();
strSql.Append(" and TrueName like '%" + name + "%' ");
}
if (!queryParam["StartDate"].IsEmpty())
{
string StartDate = queryParam["StartDate"].ToString();
strSql.Append(" and ow.inputtime>='" + StartDate + "' ");
}
if (!queryParam["EndDate"].IsEmpty())
{
string EndDate = queryParam["EndDate"].ToString();
strSql.Append(" and ow.inputtime <='" + EndDate + "' ");
}
return(repository.BaseRepository().FindList(strSql.ToString(), pagination));
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="keyValue">主键</param>
public void RemoveForm(string keyValue)
{
RepositoryFactory <OutbillgoodModel> repository = new RepositoryFactory <OutbillgoodModel>();
repository.BaseRepository().Delete(keyValue);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="keyValue">主键值</param>
/// <returns></returns>
public OutbillitemEntity GetEntity(string keyValue)
{
RepositoryFactory <OutbillitemEntity> repository = new RepositoryFactory <OutbillitemEntity>();
return(repository.BaseRepository().FindEntity(keyValue));
}
/// <summary>
/// 领用统计
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <OutStatisticsModel> GetStatisticsList(Pagination pagination, string queryJson)
{
RepositoryFactory <OutStatisticsModel> repository = new RepositoryFactory <OutStatisticsModel>();
var strSql = new StringBuilder();
strSql.Append(@" select a.*,CONVERT(NVARCHAR(10),b.foutdate,120) AS foutdate,b.fdeptid,c.fname,c.funit,isnull(d.deptname,'') as fdeptname from tb_wh_outbill_item a ");
strSql.Append(@" INNER join tb_wh_outbill b on a.foutbillid = b.foutbillid ");
strSql.Append(@" INNER join tb_wh_goodsinfo c on a.fgoodsid = c.fgoodsid ");
strSql.Append(@" INNER join hr_department d on b.fdeptid = d.deptid where 1 = 1 ");
var parameter = new List <DbParameter>();
var queryParam = queryJson.ToJObject();
//查询条件 领用时间
if (!queryParam["StartDate"].IsEmpty())
{
string StartDate = queryParam["StartDate"].ToString();
strSql.Append(" and b.foutdate>=@StartDate ");
parameter.Add(DbParameters.CreateDbParameter("@StartDate", StartDate));
}
if (!queryParam["EndDate"].IsEmpty())
{
string EndDate = queryParam["EndDate"].ToString();
strSql.Append(" and b.foutdate<=@EndDate ");
parameter.Add(DbParameters.CreateDbParameter("@EndDate", EndDate));
}
//部门
if (!queryParam["Provider"].IsEmpty())
{
string Provider = queryParam["Provider"].ToString();
strSql.Append(" and d.deptid=@Provider ");
parameter.Add(DbParameters.CreateDbParameter("@Provider", Provider));
}
//出库单号
if (!queryParam["Billid"].IsEmpty())
{
string Billid = queryParam["Billid"].ToString();
strSql.Append(" and a.foutbillid=@Billid ");
parameter.Add(DbParameters.CreateDbParameter("@Billid", Billid));
}
//查询方式 QueryType 1大类 2小类 3物品
if (!queryParam["QueryType"].IsEmpty())
{
string type = queryParam["QueryType"].ToString();
switch (type)
{
case "1":
//大类
if (!queryParam["LargeClass"].IsEmpty())
{
string LargeClass = queryParam["LargeClass"].ToString();
strSql.Append(" AND left(a.fgoodsid, 3)= @LargeClass ");
parameter.Add(DbParameters.CreateDbParameter("@LargeClass", LargeClass));
}
break;
case "2":
//小类
if (!queryParam["SmallClass"].IsEmpty())
{
string SmallClass = queryParam["SmallClass"].ToString();
strSql.Append(" AND left(a.fgoodsid, 6)= @SmallClass ");
parameter.Add(DbParameters.CreateDbParameter("@SmallClass", SmallClass));
}
break;
case "3":
//物品
if (!queryParam["Goods"].IsEmpty())
{
string Goods = queryParam["Goods"].ToString();
strSql.Append(" AND a.fgoodsid = @Goods ");
parameter.Add(DbParameters.CreateDbParameter("@Goods", Goods));
}
break;
}
}
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray(), pagination));
}