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