/// <summary>
/// 获取所有已读或未读消息
/// </summary>
/// <param name="userId">用户ID</param>
/// <param name="isRead">0：未读/1：已读</param>
/// <param name="page">分页</param>
/// <returns>所有已读或未读消息</returns>
public DataTable GetAllMessage(int userId, int isRead, ref PageInfo page)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT 0 AS CheckFlag , ");
strSql.Append(" A.Id , ");
strSql.Append(" A.MessageId , ");
strSql.Append(" A.EmpId , ");
strSql.Append(" A.ReadTime , ");
strSql.Append(" A.IsRead , ");
strSql.Append(" B.Id AS MsgID , ");
strSql.Append(" B.SendEmpID , ");
strSql.Append(" B.SendDept , ");
strSql.Append(" B.ReceivingDept , ");
strSql.Append(" B.SendTime , ");
strSql.Append(" B.MessageTitle , ");
strSql.Append(" B.MessageContent , ");
strSql.Append(" B.Limittime , ");
strSql.Append(" C.Code , ");
strSql.Append(" C.Name , ");
strSql.Append(" D.Name As UserName ");
strSql.Append(" FROM BaseMessageRead A ");
strSql.Append(" LEFT JOIN BaseMessage B ON A.MessageId = B.Id ");
strSql.Append(" LEFT JOIN BaseMessageType C ON B.MessagetypeID = C.Id ");
strSql.Append(" LEFT JOIN BaseEmployee D ON B.SendEmpID = D.EmpId ");
strSql.AppendFormat(" WHERE A.EmpId = {0} ", userId);
strSql.AppendFormat(" AND A.IsRead = {0} AND A.WorkID = {1} ", isRead, oleDb.WorkId);
//strSql.Append(" AND B.Limittime >= DATEDIFF(DAY, B.SendTime, GETDATE()) ");
strSql.AppendFormat(" AND C.Status = 0 AND B.MessagetypeID IN(SELECT MessageTypeId FROM BaseMessageTypeUser WHERE EmpId = {0})", userId);
page.KeyName = "Id";
return(oleDb.GetDataTable(SqlPage.FormatSql(strSql.ToString(), page, oleDb)));
}
/// <summary>
/// 获取书籍数据
/// </summary>
/// <param name="bookName">查询名称</param>
/// <param name="pageinfo">分页对象</param>
/// <returns></returns>
public dynamic GetBookData(string bookName, ref PageInfo pageinfo)
{
string strSql = string.Format(@"select * from books where bookname like '%{0}%'", bookName);
strSql = SqlPage.FormatSql(DatabaseType.SqlServer, strSql, pageinfo, connection);
return(connection.Query <Entity.Book>(strSql));
}
public DataTable GetUserData(int[] deptIds, string key, PageInfo page)
{
string strdeptid = "";
foreach (int id in deptIds)
{
if (strdeptid == "")
{
strdeptid += id.ToString();
}
else
{
strdeptid += "," + id.ToString();
}
}
string strsql = @"
select
a.empid,b.userid,b.Code,a.Name,a.sex,c.DeptId,b.Lock,
(CASE Sex WHEN -1 THEN '未知性别' WHEN 0 THEN '男性' ELSE '女性' END) AS SexText,
(CASE Lock WHEN 0 THEN '否' ELSE '是' END) AS LockText,D. Name AS DeptName
from BaseEmployee a
left join BaseUser b on a.EmpId=b.EmpId
left join BaseEmpDept c on a.EmpId=c.EmpId and c.DefaultFlag=1
LEFT JOIN BaseDept D ON C.DeptId = D.DeptId
where
(c.DeptId in ({0}) or {0}=0)
and (b.code like '%{1}%' or a.name like '%{1}%' or a.pym like '%{1}%' or a.wbm like '%{1}%' or a.szm like '%{1}%')
and a.workId={2}";
strsql = string.Format(strsql, strdeptid, key, oleDb.WorkId);
strsql = SqlPage.FormatSql(strsql, page, oleDb);
return(oleDb.GetDataTable(strsql));
}
/// <summary>
/// 根据机构ID获取中心收费项目列表
/// </summary>
/// <param name="workId">机构ID</param>
/// <param name="iAudit">审核状态</param>
/// <param name="iStop">停用状态</param>
/// <param name="strKey">检索条件</param>
/// <param name="iStatID">统计大类ID</param>
/// <param name="page">分页</param>
/// <returns>收费项目列表</returns>
public List <Basic_CenterFeeItem> GetCenterFeeItemList(int workId, int iAudit, int iStop, string strKey, int iStatID, ref PageInfo page)
{
var strSql = @" SELECT
FeeID,
CenterItemCode,
CenterItemName,
PyCode,
WbCode,
CusCode,
Unit,
Price,
Explain,
StatID,
dbo.fnGetStatItemName(StatID) AS StatName,
MreType,
ModEmpID,
ModDate,
CreateWorkID,
AuditStatus,
Auditor,
AuditTime,
IsStop,
WorkID
FROM Basic_CenterFeeItem
WHERE 1 = 1 {0} ";
var strWhere = new StringBuilder();
if (workId != 0)
{
strWhere.Append(string.Format(" AND CreateWorkID = {0} ", workId));
}
if (!string.IsNullOrEmpty(strKey))
{
strWhere.Append(string.Format(" AND ( FeeID LIKE '{0}' OR CenterItemCode LIKE '%{0}%' OR CenterItemName LIKE '%{0}%' OR PyCode LIKE '%{0}%' OR WbCode LIKE '%{0}%' ) ", strKey));
}
if (iAudit != 9)
{
strWhere.Append(string.Format(" AND AuditStatus = {0} ", iAudit));
}
if (iStop != 9)
{
strWhere.Append(string.Format(" AND IsStop = {0} ", iStop));
}
if (iStatID != 0 && iStatID != -1)
{
strWhere.Append(string.Format(" AND StatID = {0} ", iStatID));
}
page.KeyName = "FeeID";
return(oleDb
.Query <Basic_CenterFeeItem>(SqlPage.FormatSql(string.Format(strSql, strWhere), page, oleDb), string.Empty)
.ToList());
}
public DataTable GetUserData(string key, PageInfo page)
{
string strsql = @"select a.empid,b.userid,b.Code,a.Name,a.sex,c.DeptId,b.Lock from
BaseEmployee a left join BaseUser b on a.EmpId=b.EmpId
left join BaseEmpDept c on a.EmpId=c.EmpId and c.DefaultFlag=1
where (b.code like '%{0}%' or a.name like '%{0}%' or a.pym like '%{0}%' or a.wbm like '%{0}%' or a.szm like '%{0}%') and a.workId={1}";
strsql = string.Format(strsql, key, oleDb.WorkId);
strsql = SqlPage.FormatSql(strsql, page, oleDb);
return(oleDb.GetDataTable(strsql));
}
/// <summary>
/// 根据机构ID获取本院收费项目列表
/// </summary>
/// <param name="workId">机构ID</param>
/// <param name="iStatID">统计大类ID</param>
/// <param name="iStop">停用标志</param>
/// <param name="strKey">检索条件</param>
/// <param name="page">分页</param>
/// <returns>本院收费项目列表</returns>
public List <Basic_HospFeeItem> GetHospFeeItemList(int workId, int iStatID, int iStop, string strKey, ref PageInfo page)
{
var strSql = @" SELECT
ItemID,
CenterItemID,
cfi.CenterItemCode AS ItemCode,
cfi.CenterItemName AS ItemName,
AliasName,
hfi.PyCode,
hfi.WbCode,
hfi.CusCode,
hfi.Price,
One_level,
Two_level,
Three_level,
hfi.StatID,
dbo.fnGetStatItemName(hfi.StatID) AS StatName,
IsBle,
hfi.ModEmpID,
hfi.ModDate,
hfi.IsStop,
hfi.WorkID,
hfi.MedicateID
FROM Basic_HospFeeItem hfi
INNER JOIN Basic_CenterFeeItem cfi ON hfi.CenterItemID = cfi.FeeID AND hfi.WorkID = cfi.WorkID
WHERE 1 = 1 {0} ";
var strWhere = new StringBuilder();
if (workId != 0)
{
strWhere.Append(string.Format(" AND hfi.WorkID = {0} ", workId));
}
if (!string.IsNullOrEmpty(strKey))
{
strWhere.Append(string.Format(" AND ( cfi.CenterItemCode LIKE '%{0}%' OR cfi.CenterItemName LIKE '%{0}%' OR AliasName LIKE '%{0}%' OR hfi.PyCode LIKE '%{0}%' OR hfi.WbCode LIKE '%{0}%' ) ", strKey));
}
if (iStatID != 0 && iStatID != -1)
{
strWhere.Append(string.Format(" AND hfi.StatID = {0} ", iStatID));
}
if (iStop != 9)
{
strWhere.Append(string.Format(" AND hfi.IsStop = {0} ", iStop));
}
page.KeyName = "ItemID";
return(oleDb
.Query <Basic_HospFeeItem>(SqlPage.FormatSql(string.Format(strSql, strWhere), page, oleDb), string.Empty)
.ToList());
}
public ServiceResponseData GetDiseasePage()
{
int pageNo = requestData.GetData <int>(0);
int pageSize = requestData.GetData <int>(1);
PageInfo page = new PageInfo(pageSize, pageNo);
page.KeyName = "ID";
DataTable dt = oleDb.GetDataTable(SqlPage.FormatSql(@"SELECT ID ,
ICDCode ,
Name ,
PYCode ,
WBCode ,
Type FROM HISDB..His_Disease", page, oleDb));
responseData.AddData(dt);
responseData.AddData(page.totalRecord);
return(responseData);
}
public ActionResult AdminIndex(string id, string ids)
{
int pageIndex = Convert.ToInt32(id ?? "0");
if (id == "0")
{
pageIndex = 1;
}
string sql = @"select UserName,Bank,convert(nvarchar(11),Start_Date,120) as Start_Date ,convert(nvarchar(11),End_Date,120) as End_Date,a.id,Meeting,b.MeetingName from tb_login a left join tb_Meeting b on a.Meeting=b.id where Lvl=1 and a.isdel=1 ";
MessasgeData mgdata = SqlPage.SqlPageAction(Datafun, sql, "id desc ", pageIndex, 10);
List <tb_login> listorder = new List <tb_login>();
if (mgdata.Mgdatacount > 0)
{
foreach (DataRow dr in mgdata.Mgdata.Rows)
{
tb_login cbai = new tb_login
{
id = Convert.ToInt32(dr["id"].ToString()),
UserName = dr["UserName"].ToString(),
Bank = dr["Bank"].ToString(),
Password = dr["Start_Date"].ToString(),
Tel = dr["End_Date"].ToString(),
Meeting = Convert.ToInt32(dr["Meeting"].ToString()),
added1 = dr["MeetingName"].ToString()
};
listorder.Add(cbai);
}
;
}
ViewBag.recordCount = mgdata.Mgdatacount;
ViewBag.ids = ids;
ViewBag.pageindex = id ?? "0";
return(View(listorder));
}
/// <summary>
/// 查询数据分页显示
/// </summary>
/// <returns></returns>
public ActionResult loadlistpage()
{
//页码
int page = Convert.ToInt32(Request["page"]);
//页条数
int rows = Convert.ToInt32(Request["limit"]);
//where
string sqlwhere = "1=1";
string aaa = Request["aaa"];
//排序
string sort = "ID desc";
//表名
string tableName = "MT_WORKORDER";
//列
string columns = "ID,CCODE,CCODENAME,DDATE,CMAKER,DCREATESYSTIME,CSOUCE,CTHINGADDRESS";
//总条数
int toalCount = 0;
Database db = DataBaseHelper.CreateDataBase();
//DatabaseFactory.CreateDatabase("DEFAULT_CONNECTION_STRING");
SqlPage sqlpage = new SqlPage();
DataTable dt = sqlpage.GetDataSet(tableName, columns, sqlwhere, sort, page, rows, out toalCount).Tables[0];
ArrayList list = FormatJsonExtension.DataTableArrayList(dt);
Dictionary <String, object> result = new Dictionary <string, object>();
result.Add("code", "0");
result.Add("msg", "");
result.Add("count", toalCount);
result.Add("data", list);
return(this.JsonFormat(result));
}
/// <summary>
/// 查询全省行政区域的初始用能权
/// </summary>
/// <param name="CATEGORY">查询类别</param>
/// <param name="REGIONCODE">行政区域code</param>
/// <param name="EXECUTEDATE">执行时间</param>
/// <param name="page">页数</param>
/// <param name="rowpage">行数</param>
/// <returns>返回2种情况（行政区，数量；企业，数量）</returns>
public static Dictionary <String, Object> SearchCS(string CATEGORY)
{
List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>();
//创建对象和变量。
int toalCount;
string col = string.Empty;
SqlPage oPage = new SqlPage(); //new 分页查询对象
string strWhere = string.Empty; //where条件
string strSql = string.Empty; //sql语句
string strCol = string.Empty; //查询的字段
string strGb = string.Empty; //group by 分组
string strOb = string.Empty; //order by 排序
ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>();
Dictionary <String, Object> result = new Dictionary <string, object>();
DataTable dt = new DataTable(); //new DataTable对象
Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb);
using (DbConnection dbconn = dbbase.CreateConnection())
using (DbCommand cmd = dbconn.CreateCommand())
{
if (dbconn.State != ConnectionState.Open)
{
dbconn.Open();
}
try
{
logs.Info("555555");
return(result);
}
catch (Exception ex)
{
JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误！ ||" + ex.ToString());
return(result);
}
}
}
/// <summary>
/// 获取会员信息
/// </summary>
/// <param name="condition">查询条件</param>
/// <param name="page">分页信息</param>
/// <returns>会员信息</returns>
public DataTable GetMemberInfo(string condition, PageInfo page)
{
string sql = @" select * from V_ME_MemberInfo where 1=1 " + condition + " order by OpenDate desc";
return(oleDb.GetDataTable(SqlPage.FormatSql(sql, page, oleDb)));
}
/// <summary>
/// 查询全省行政区域的用能权统计
/// </summary>
/// <param name="STRATTIME">开始时间</param>
/// <param name="ENDTIME">结束时间</param>
/// <param name="CATEGORY">查询类别</param>
/// <param name="REGIONCODE">行政区CODE</param>
/// <param name="page">页数</param>
/// <param name="rowpage">行数</param>
/// <returns>返回2种情况（行政区，数量；企业，数量）</returns>
public static Dictionary <String, Object> SearchTJ(string STRATTIME, string ENDTIME, string CATEGORY, string REGIONCODE, int page, int rowpage)
{
List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>();
//创建对象和变量。
int toalCount;
string col = string.Empty;
SqlPage oPage = new SqlPage(); //new 分页查询对象
string strWhere = string.Empty; //where条件
string strSql = string.Empty; //sql语句
string strCol = string.Empty; //查询的字段
string strGb = string.Empty; //group by 分组
string strOb = string.Empty; //order by 排序
ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>();
Dictionary <String, Object> result = new Dictionary <string, object>();
DataTable dt = new DataTable(); //new DataTable对象
Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb);
using (DbConnection dbconn = dbbase.CreateConnection())
using (DbCommand cmd = dbconn.CreateCommand())
{
if (dbconn.State != ConnectionState.Open)
{
dbconn.Open();
}
try
{
List <ColumnModel> cm = new List <ColumnModel>();
if (CATEGORY == "CATEGORY")
{
strWhere = " XZQ.PYJC is not null";
strSql = @" T_NYXFZLKZ_DQJBXXB XZQ
left join (select XZQHBM, sum(nvl(RTOTAL, 0)) as RTOTAL
from M_QYJCXXB QY
left join T_ENERGY_RECORD JL
on QY.FRDWDM = JL.RENTERPRISECODE
--where 界面查询条件
where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd')
and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')
and RTYPE in (0, 1, 2)
group by XZQHBM) C --CUN 存有
on XZQ.XZQHBM = C.XZQHBM
left join (select XZQHBM, sum(nvl(RTOTAL, 0)) as RTOTAL
from M_QYJCXXB QY
left join T_ENERGY_RECORD JL
on QY.FRDWDM = JL.RENTERPRISECODE
--where 界面查询条件
where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd')
and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')
and RTYPE in (3)
group by XZQHBM) M --MAI 出售
on XZQ.XZQHBM = C.XZQHBM ";
strCol = @" XZQ.XZQHBM as REGIONCODE ,
XZQ.XZQHM as REGIONNAME ,
nvl(C.RTOTAL, 0) c --全部用能权
,
NVL(M.RTOTAL, 0) M --卖出用能权
,
nvl(C.RTOTAL, 0) - NVL(M.RTOTAL, 0) as ENERGYNUMBER ";
strOb = " XZQ.XZQHBM ";
strGb = "";
cm = new List <ColumnModel>();
//用能权统计--行政区
cm.Add(new ColumnModel {
title = "行政区编码", field = "REGIONCODE", align = "center", hidden = "hidden"
});
cm.Add(new ColumnModel {
title = "行政区名称", field = "REGIONNAME", align = "center", width = "40%"
});
cm.Add(new ColumnModel {
title = "用能权统计(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%"
});
if (cm.Count == 1)
{
cm.Clear();
}
}
else
{
strWhere = "1=1";
strSql = @" (select qy.dwxxmc, sum(nvl(rtotal, 0)) as rtotal
from m_qyjcxxb qy
left join t_energy_record jl
on qy.frdwdm = jl.renterprisecode
--where 界面查询条件
where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd')
and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')
and rtype in (0, 1, 2)
and qy.xzqhbm='" + REGIONCODE + @"'
group by qy.dwxxmc) c --cun 存有
left join (select qy.dwxxmc, sum(nvl(rtotal, 0)) as rtotal
from m_qyjcxxb qy
left join t_energy_record jl
on qy.frdwdm = jl.renterprisecode
--where 界面查询条件
where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd')
and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')
and rtype in (3)
and qy.xzqhbm='" + REGIONCODE + @"'
group by qy.dwxxmc) m --mai 出售
on c.dwxxmc=m.dwxxmc";
strCol = @" c.dwxxmc as COMPANYNAME ,
nvl(c.rtotal, 0) c --全部用能权
,
nvl(m.rtotal, 0) m --卖出用能权
,
nvl(c.rtotal, 0) - nvl(m.rtotal, 0) as ENERGYNUMBER";
strOb = "";
strGb = "";
cm = new List <ColumnModel>();
cm.Add(new ColumnModel {
title = "企业名称", field = "COMPANYNAME", align = "left", width = "40%"
});
//判断查询的监测因子项目，，动态生成grid的列头。
//初始用能
cm.Add(new ColumnModel {
title = "初始用能(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%"
});
if (cm.Count == 1)
{
cm.Clear();
}
}
//返回dt数据。
dt = oPage.GetDataSetGroupBy(DataBaseHelper.typedb.zlyjgisdb, strSql, strCol
, strWhere, strOb, strGb, page, rowpage, out toalCount).Tables[0];
// 返回Sql语句，目的是绑定excel。
string strSqlT = "";
string strSqlP = "";
strSqlP = oPage.GetListSQLS(strSql, strCol, 0, 0, strOb, strWhere, strOb, out strSqlT);
//绑定数据到list。
oList = opp.FillModel(dt);
// GP 服务 废气 王岩松 2016年6月23日17:44:43
string stringGp = " SELECT g.city,gscode, g.name, G.x, g.y ORDER BY g.city ";
if (dt != null && dt.Rows.Count > 0)
{
oList = opp.FillModel(dt);
var oLists = from tGride in oList
select new
{
tGride.REGIONCODE,
tGride.REGIONNAME,
tGride.COMPANYNAME,
tGride.ENERGYNUMBER,
};
result.Add("rows", oLists);
result.Add("total", toalCount);
result.Add("cols", cm);
result.Add("sql", strSqlT);
result.Add("GP", stringGp);
}
else
{
result.Add("total", 0);
result.Add("rows", oList);
result.Add("cols", cm);
result.Add("sql", strSqlT);
result.Add("GP", stringGp);
}
return(result);
}
catch (Exception ex)
{
JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误！ ||" + ex.ToString());
return(result);
}
}
}
/// <summary>
/// 查询全省行政区域的交易统计
/// </summary>
/// <param name="STRATTIME">开始时间</param>
/// <param name="ENDTIME">结束时间</param>
/// <param name="CATEGORY">查询类别</param>
/// <param name="REGIONCODE">行政区CODE</param>
/// <param name="page">页数</param>
/// <param name="rowpage">行数</param>
/// <returns>返回2种情况（行政区，数量；企业，数量）</returns>
public static Dictionary <String, Object> SearchJY(string STRATTIME, string ENDTIME, string CATEGORY, string REGIONCODE, int page, int rowpage)
{
List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>();
//创建对象和变量。
int toalCount;
string col = string.Empty;
SqlPage oPage = new SqlPage(); //new 分页查询对象
string strWhere = string.Empty; //where条件
string strSql = string.Empty; //sql语句
string strCol = string.Empty; //查询的字段
string strGb = string.Empty; //group by 分组
string strOb = string.Empty; //order by 排序
ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>();
Dictionary <String, Object> result = new Dictionary <string, object>();
DataTable dt = new DataTable(); //new DataTable对象
Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb);
using (DbConnection dbconn = dbbase.CreateConnection())
using (DbCommand cmd = dbconn.CreateCommand())
{
if (dbconn.State != ConnectionState.Open)
{
dbconn.Open();
}
try
{
List <ColumnModel> cm = new List <ColumnModel>();
if (CATEGORY == "CATEGORY")
{
strWhere = " XZQ.PYJC is not null";
strSql = @" T_NYXFZLKZ_DQJBXXB XZQ
left join (select XZQHBM, sum(nvl(tmnumber, 0)) as RTOTAL
from M_QYJCXXB QY
left join T_TRANSACTION_MANAGEMENT JY
on QY.FRDWDM = JY.Tmenterrisecode
--where 界面查询条件
where tmtime >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd')
and tmtime <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')
group by XZQHBM) C --CUN 存有
on XZQ.XZQHBM = C.XZQHBM";
strCol = @" XZQ.XZQHBM as REGIONCODE,
XZQ.XZQHM as REGIONNAME,
nvl(C.RTOTAL, 0) as ENERGYNUMBER";
strOb = "XZQ.XZQHBM";
strGb = "";
cm = new List <ColumnModel>();
//用能权统计--行政区
cm.Add(new ColumnModel {
title = "行政区编码", field = "REGIONCODE", align = "center", hidden = "hidden"
});
cm.Add(new ColumnModel {
title = "行政区名称", field = "REGIONNAME", align = "center", width = "40%"
});
cm.Add(new ColumnModel {
title = "用能权统计(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%"
});
if (cm.Count == 1)
{
cm.Clear();
}
}
else
{
// add 行政区划编码
strWhere = @"QY.XZQHBM ='" + REGIONCODE + "' and tmtime >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and tmtime <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')";
strSql = @"M_QYJCXXB QY left join T_TRANSACTION_MANAGEMENT JY on QY.FRDWDM = JY.Tmenterrisecode";
strCol = @" qy.dwxxmc as COMPANYNAME, sum(nvl(tmnumber, 0)) as ENERGYNUMBER";
strOb = "";
strGb = "qy.dwxxmc";
cm = new List <ColumnModel>();
cm.Add(new ColumnModel {
title = "企业名称", field = "COMPANYNAME", align = "left", width = "40%"
});
//初始用能 动态生成grid的列头。
cm.Add(new ColumnModel {
title = "初始用能(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%"
});
if (cm.Count == 1)
{
cm.Clear();
}
}
//返回dt数据。
dt = oPage.GetDataSetGroupBy(DataBaseHelper.typedb.zlyjgisdb, strSql, strCol
, strWhere, strOb, strGb, page, rowpage, out toalCount).Tables[0];
// 返回Sql语句，目的是绑定excel。
string strSqlT = "";
string strSqlP = "";
strSqlP = oPage.GetListSQLS(strSql, strCol, 0, 0, strOb, strWhere, strOb, out strSqlT);
//绑定数据到list。
oList = opp.FillModel(dt);
// GP 服务 废气 王岩松 2016年6月23日17:44:43
string stringGp = " SELECT g.city,gscode, g.name, G.x, g.y ORDER BY g.city ";
if (dt != null && dt.Rows.Count > 0)
{
oList = opp.FillModel(dt);
var oLists = from tGride in oList
select new
{
tGride.REGIONCODE,
tGride.REGIONNAME,
tGride.COMPANYNAME,
tGride.ENERGYNUMBER,
};
result.Add("rows", oLists);
result.Add("total", toalCount);
result.Add("cols", cm);
result.Add("sql", strSqlT);
result.Add("GP", stringGp);
}
else
{
result.Add("total", 0);
result.Add("rows", oList);
result.Add("cols", cm);
result.Add("sql", strSqlT);
result.Add("GP", stringGp);
}
return(result);
}
catch (Exception ex)
{
JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误！ ||" + ex.ToString());
return(result);
}
}
}
private List <CardDataSourceDrugItem> GetDrugItemToDatabase(int type, int pageNo, int pageSize, string filter)
{
PageInfo page = new PageInfo(pageSize, pageNo);
page.KeyName = "StockID";
string strsql = "";
strsql = @"SELECT * FROM ViewBaseData_Med T WHERE (PyCode LIKE '%{0}%' OR PyCodeT LIKE '%{0}%' OR WbCode LIKE '%{0}%' OR WbCodeT LIKE '%{0}%' OR CName LIKE '%{0}%' OR TName LIKE '%{0}%')";
strsql = string.Format(strsql, filter);
strsql = SqlPage.FormatSql(strsql, page, delegate(string sql)
{
int toltal = 0;
SqlDataReader sdr = oleDb.GetSqlDataReader(sql);
if (sdr.Read())
{
toltal = (int)sdr.GetValue(0);
}
sdr.Close();
return(toltal);
});
DataTable dt = oleDb.GetDataTable(strsql);
List <CardDataSourceDrugItem> list_DrugItem = new List <CardDataSourceDrugItem>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CardDataSourceDrugItem mDrugItem = new CardDataSourceDrugItem();
mDrugItem.ItemId = ConvertDataExtend.ToInt32(dt.Rows[i]["StockID"], 0);
mDrugItem.ItemName = dt.Rows[i]["CName"].ToString();
mDrugItem.ItemName_Print = dt.Rows[i]["TName"].ToString();
mDrugItem.Standard = dt.Rows[i]["Spec"].ToString();
mDrugItem.Scale = ""; //补偿比例
mDrugItem.StoreNum = ConvertDataExtend.ToDecimal(dt.Rows[i]["ActualQty"], 0); //库存量
mDrugItem.UnPickUnit = dt.Rows[i]["MiniUnit"].ToString(); //包装单位(销售单位)
mDrugItem.SellPrice = ConvertDataExtend.ToDecimal(dt.Rows[i]["ClinPrice"], 0); //销售价格?
mDrugItem.BuyPrice = ConvertDataExtend.ToDecimal(dt.Rows[i]["WardPrice"], 0); //进价
//mDrugItem.ExecDeptName = dt.Rows[i][""].ToString();//执行科室?
mDrugItem.Pym = dt.Rows[i]["PyCode"].ToString();
mDrugItem.Wbm = dt.Rows[i]["WbCode"].ToString();
mDrugItem.Address = dt.Rows[i]["FacName"].ToString();//生产厂家
//mDrugItem.DoseUnitId = dt.Rows[i][""].ToString();//剂量单位
mDrugItem.DoseUnitName = dt.Rows[i]["DosUnit"].ToString();
mDrugItem.DoseConvertNum = ConvertDataExtend.ToDecimal(dt.Rows[i]["Dosage"], 0); //剂量对应包装单位系数
mDrugItem.ItemType = ConvertDataExtend.ToInt32(dt.Rows[i]["MatClass"], 0); //项目类型 0-所有 1-西药 2-中药 3-处方可开的物品 4-收费项目
//mDrugItem.StatItemCode = dt.Rows[i][""].ToString();//大项目代码
//mDrugItem.UnPickUnitId = dt.Rows[i][""].ToString();//包装单位ID
mDrugItem.ExecDeptId = ConvertDataExtend.ToInt32(dt.Rows[i]["DeptID"], 0); //执行科室ID?
mDrugItem.FloatFlag = ConvertDataExtend.ToInt32(dt.Rows[i]["RoundingMode"], 0); // dt.Rows[i][""].ToString();//按含量取整1 按剂量取整0
//mDrugItem.VirulentFlag = dt.Rows[i][""].ToString();//剧毒标识
//mDrugItem.NarcoticFlag = dt.Rows[i][""].ToString();//麻醉标识
mDrugItem.SkinTestFlag = ConvertDataExtend.ToInt32(dt.Rows[i]["SkinMethod"], 0);//皮试标识
//mDrugItem.RecipeFlag = dt.Rows[i][""].ToString();//处方标识
//mDrugItem.LunacyFlag = dt.Rows[i][""].ToString();//精神药品标识
//mDrugItem.CostlyFlag = dt.Rows[i][""].ToString();//贵重药品标识
mDrugItem.default_Usage_Amount = 0; //默认用量
mDrugItem.default_Usage_Id = 0; //默认用法
mDrugItem.default_Usage_Name = "";
mDrugItem.default_Frequency_Id = 0; //默认频次
mDrugItem.default_Frequency_Name = "";
list_DrugItem.Add(mDrugItem);
}
return(list_DrugItem);
}