/// <summary>
/// 团检结算中心订单导出
/// </summary>
/// <param name="yybh"></param>
/// <param name="dwbh"></param>
/// <returns></returns>
public DataTable GetTjJsExport(string yybh, string dwbh)
{
DataTable dt = new DataTable();
DataTable dtWj = new DataTable();
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = string.Format(@"SELECT c.ddbh, a.mc as 单位,c.dwmc as 部门, b.rybh as 工号,c.xm as 姓名, c.tcmc AS 套餐名称, c.ddze as 订单金额,c.ysjsj as 结算价, c.dh AS 电话,c.zjhm as 证件号码,
case c.sfdj when 1 then '已到检' else '未到检 ' end as 到检状态, case c.sfdj when 1 then c.djtime else ' ' end as 到检时间,'' as 未检项目
FROM dd_jbxx_new c join qy_jbxx a on left(c.dwbh,4)=a.bh and c.yybh=a.yybh join qy_ygxx b on c.yybh=b.yybh and c.dsfdd=b.ydjh WHERE c.yybh = '{0}' and left(c.dwbh,4)='{1}' and ddly = 1 ", yybh, dwbh);
string sqlWj = "select zhxmmc,jg,a.ddbh from dd_zhxm a join dd_jbxx b on a.ddbh=b.ddbh where a.sfdj=0 and b.yybh='" + yybh + "' and left(b.dwbh,4)='" + dwbh + "' and ddly=1 and a.sfdj=1";
var reader = conn.ExecuteReader(sql, null);
dt.Load(reader);
var reader2 = conn.ExecuteReader(sqlWj, null);
dtWj.Load(reader2);
foreach (DataRow dr in dt.Rows)
{
if (dr["到检状态"].ToString() == "已到检")
{
DataRow[] founds = dtWj.Select(" ddbh='" + dr["ddbh"].ToString() + "'");
if (founds.Length > 1)
{
string xm = "";
foreach (DataRow drDetail in founds)
{
xm += drDetail["zhxmmc"] + ":" + drDetail["jg"] + "，";
}
xm = xm.TrimEnd('，');
dr["未检项目"] = xm;
}
}
}
dt.Columns.Remove("ddbh");
return(dt);
}
}
public DataTable GetAllProvince()
{
string sqlString = "SELECT * FROM XT_SFB";
using (IDbConnection dbConnection = new DapperConnection().DbConnection)
{
DataTable table = new DataTable();
IDataReader dataReader = dbConnection.ExecuteReader(sqlString);
table.Load(dataReader);
return(table);
}
}
public DataTable GetMinMeal(string id)
{
string sqlString = "SELECT TCXLXID,TCXLXMC,DJS FROM TC_TCXLX WITH(NOLOCK) WHERE TCDLXID=@TCDLXID AND SFXS =1";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@TCDLXID", id, DbType.String, size: 32);
using (IDbConnection dbConnection = new DapperConnection().DbConnection)
{
DataTable resultTabe = new DataTable();
resultTabe.Load(dbConnection.ExecuteReader(sqlString, parameters));
return(resultTabe);
}
}
public DataTable GetCityByProvinceId(string id)
{
string sqlString = "SELECT A.CSBH,A.BM,A.CSMC FROM [dbo].[XT_CSLB] A WHERE A.SFID=@SFID";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@SFID", id);
using (IDbConnection dbConnection = new DapperConnection().DbConnection)
{
DataTable table = new DataTable();
IDataReader dataReader = dbConnection.ExecuteReader(sqlString, parameters);
table.Load(dataReader);
return(table);
}
}
/// <summary>
/// 医院端导出营收报表
/// </summary>
/// <param name="jsjlid"></param>
/// <returns></returns>
public DataTable GetYsExport(string start, string end, string yybh, int qdid)
{
DataTable dt = new DataTable();
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = string.Format(@"SELECT d.mc AS 订单来源, c.ddbh AS 单号, c.tcmc AS 套餐名称, c.xm AS 姓名, c.dh AS 电话,
c.ddze as 订单金额, c.yykssj as 预约时间, case c.ddzt when 2 then '预约中' when 3 then '预约成功' when 6 then '已到检' when 7 then '已完成' when 9 then '已出报告' end as 套餐状态
FROM dd_jbxx_new c
join qy_jbxx d ON c.dwbh = d.bh and c.yybh=d.yybh WHERE ddly = 0 and ddzt in (2, 3, 6, 7, 9) and yykssj>='{0}' and yykssj<'{1}' " , start, Convert.ToDateTime(end).AddDays(1).ToString());
var reader = conn.ExecuteReader(sql, null);
dt.Load(reader);
return(dt);
}
}
/// <summary>
/// 导出医院端渠道订单
/// </summary>
/// <param name="ht"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public DataTable ExportOrderYyQdList(Hashtable ht)
{
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dsfbz"].ToString()))
{
sd.Append(" and a.dsfbzid = @dsfbz");
paramList.Add("dsfbz", ht["dsfbz"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddly"].ToString()))
{
sd.Append(" and a.ddly = @ddly");
paramList.Add("ddly", ht["ddly"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddstart"].ToString()))
{
sd.Append(" and a.intime>= @ddstart ");
paramList.Add("ddstart", ht["ddstart"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddend"].ToString()))
{
sd.Append(" and a.intime<= @ddend ");
paramList.Add("ddend", ht["ddend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["yystart"].ToString()))
{
sd.Append(" and a.yykssj>= @yystart ");
paramList.Add("yystart", ht["yystart"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyend"].ToString()))
{
sd.Append(" and a.yykssj<= @yyend ");
paramList.Add("yyend", ht["yyend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh = @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm = @xm");
paramList.Add("xm", ht["xm"].ToString());
}
if (!string.IsNullOrEmpty(ht["tczt"].ToString()))
{
sd.Append(" and a.ddzt=@tczt ");
paramList.Add("tczt", ht["tczt"].ToString());
}
//sd.Append(" and a.ddbh in (" + ddbhs + ")");
using (IDbConnection conn = new DapperConnection().DbConnection)
{
//string sql = @"select a.dsfdd as '订单编号', b.dsfbz as '渠道名称', a.tcmc as '套餐名称', a.tcjg as '套餐价格', a.xm as '姓名', case when a.xb=1 then '男' else '女' end as '性别'
// , a.dh as '电话', case when a.ddzt=2 then '预约中' when a.ddzt=3 then '预约成功' when a.ddzt=4 then '退款中' when a.ddzt=5 then '退款完成'
// when a.ddzt=6 then '已到检' when a.ddzt=7 then '已完成' when a.ddzt=8 then '已取消' else '已出报告' end as '订单状态', CONVERT(varchar(10),a.yykssj,120) as '预约时间'
// from dd_jbxx a join xt_dsfbz b on a.dsfbzid=b.id where 1=1";
//sql += sd.ToString();
string sql = @"select '' as 工卡号,a.dwbh as 单位,a.xm as 姓名,a.csrq as 出生日期,a.tcmc as 分组名称,
a.dh as 联系电话,case a.hz when 1 then '已婚' else '未婚' end as 婚姻, '' as 标签个数, a.zjhm as 身份证号,'' as 职称,'' as 职务,'' as 人员类别,'' as 体检类别,a.dh as 手机号码,'' as 电子邮件,0 as 是否VIP ,a.yykssj as 预定体检日期
from dd_jbxx_new a where 1 = 1";
sql += sd.ToString();
var reader = conn.ExecuteReader(sql.ToString(), paramList);
DataTable dt = new DataTable();
dt.Load(reader);
return(dt);
}
}
/// <summary>
/// 医院端企业订单导出
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
public DataTable ExportOrderYyQyList(Hashtable ht)
{
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dwbh"].ToString()))
{
sd.Append(" and left(a.dwbh,4)=@dwbh");
paramList.Add("dwbh", ht["dwbh"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddly"].ToString()))
{
sd.Append(" and a.ddly = @ddly");
paramList.Add("ddly", ht["ddly"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddstart"].ToString()))
{
sd.Append(" and a.intime>= @ddstart ");
paramList.Add("ddstart", ht["ddstart"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddend"].ToString()))
{
sd.Append(" and a.intime<= @ddend ");
paramList.Add("ddend", ht["ddend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["yystart"].ToString()))
{
sd.Append(" and a.yykssj>= @yystart ");
paramList.Add("yystart", ht["yystart"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyend"].ToString()))
{
sd.Append(" and a.yykssj<= @yyend ");
paramList.Add("yyend", ht["yyend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh = @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm = @xm");
paramList.Add("xm", ht["xm"].ToString());
}
if (!string.IsNullOrEmpty(ht["tczt"].ToString()))
{
sd.Append(" and a.ddzt=@tczt ");
paramList.Add("tczt", ht["tczt"].ToString());
}
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = @"select a.djlsh as '医院登记编号',a.dwmc as '企业名称',a.dwbh as '企业编号', a.tcjg as '套餐价格', a.xm as '姓名', case when a.xb=1 then '男' else '女' end as '性别'
, a.dh as '电话', case when a.ddzt=2 then '预约中' when a.ddzt=3 then '预约成功' when a.ddzt=4 then '退款中' when a.ddzt=5 then '退款完成'
when a.ddzt=6 then '已到检' when a.ddzt=7 then '已完成' when a.ddzt=8 then '已取消' else '已出报告' end as '订单状态', CONVERT(varchar(10),a.yykssj,120) as '预约时间'
from dd_jbxx_new a where 1=1";
sql += sd.ToString();
sql += sd.ToString();
var reader = conn.ExecuteReader(sql.ToString(), paramList);
DataTable dt = new DataTable();
dt.Load(reader);
return(dt);
}
}
/// <summary>
/// 渠道端获取结算订单列表
/// </summary>
/// <returns></returns>
public List <DdjbxxModel> GetListQd(int qdid, string yymc, string fromdate, string todate, int sfjs, string xm, string dh, int sftk, int pageIndex, int pageSize, ref int count, ref decimal tkze, ref decimal ysjsze)
{
StringBuilder sbSql = new StringBuilder();
if (!string.IsNullOrEmpty(yymc))
{
sbSql.Append(" and c.jgmc LIKE @Yymc");
}
if (!string.IsNullOrEmpty(fromdate))
{
sbSql.Append(" and convert(varchar(10), djtime,120)>=@Fromdate");
}
if (!string.IsNullOrEmpty(todate))
{
sbSql.Append(" and convert(varchar(10), djtime,120)<=@Todate");
}
if (sfjs > -1)
{
sbSql.Append(" and a.sfjs=@Jszt");
}
if (!string.IsNullOrEmpty(xm))
{
sbSql.Append(" and a.xm = @Xm");
}
if (!string.IsNullOrEmpty(dh))
{
sbSql.Append(" and a.dh = @Dh");
}
if (sftk.Equals(0))
{
sbSql.Append(" and ISNULL(a.tkze,0) = 0");
}
else if (sftk.Equals(1))
{
sbSql.Append(" and a.tkze > 0");
}
string strWhere = sbSql.ToString();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
DynamicParameters paramList = new DynamicParameters();
sbSql.Clear();
sbSql.Append("SELECT ddbh, c.jgmc, dsfdd, tcmc, xm, dh, ysjsj, tkze, sfjs FROM dd_jbxx a JOIN xt_dsfbz b ON a.dsfbzid=b.id LEFT JOIN xt_jgb c ON a.yybh=c.yybh WHERE a.ddly=0 and a.ddzt in(2,3,6,7,9) and a.dsfbzid=@Qdid");
sbSql.Append(strWhere);
string sqlPage = "";
int fromRow = (pageIndex - 1) * pageSize;
int toRow = (pageIndex) * pageSize;
paramList.Add("Yymc", "%" + yymc + "%");
paramList.Add("Fromdate", fromdate);
paramList.Add("Todate", todate);
paramList.Add("Jszt", sfjs);
paramList.Add("Xm", xm);
paramList.Add("Dh", dh);
paramList.Add("FromRow", fromRow);
paramList.Add("ToRow", toRow);
paramList.Add("Qdid", qdid);
sqlPage = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ddbh DESC) AS rowNum, * FROM (" + sbSql + ") AS T ) AS N WHERE rowNum >@FromRow AND rowNum <=@ToRow ";
List <DdjbxxModel> li = conn.Query <DdjbxxModel>(sqlPage, paramList).ToList();
//count = conn.Query<int>("SELECT COUNT(1) FROM (" + sbSql + ") AS t ", paramList).FirstOrDefault();
sbSql.Clear();
sbSql.Append($"SELECT isnull(sum(ysjsj),0) as ysjsze, isnull(sum(tkze),0) as tkze, count(*) as total FROM dd_jbxx a JOIN xt_dsfbz b ON a.dsfbzid=b.id LEFT JOIN xt_jgb c ON a.yybh=c.yybh WHERE a.ddly=0 and a.ddzt in(2,3,6,7,9) and a.dsfbzid={qdid}");
sbSql.Append(strWhere);
var reader = conn.ExecuteReader(sbSql.ToString(), paramList);
DataTable dt = new DataTable();
dt.Load(reader);
if (dt.Rows.Count > 0)
{
ysjsze = Convert.ToDecimal(dt.Rows[0]["ysjsze"]);
tkze = Convert.ToDecimal(dt.Rows[0]["tkze"]);
count = Convert.ToInt32(dt.Rows[0]["total"]);
}
dt.Load(reader);
return(li);
}
}
/// <summary>
/// 企业员工，预约详情,导出
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
public DataTable GetQyygYyxqListExcel(Hashtable ht)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select a.ydjh as 医院登记号,a.xm as 性名,a.dwmc as 部门,a.tcmc as 套餐名称,case a.xb when 1 then '男' else '女' end as 性别,a.sfzh as 身份证号, a.dh as 联系电话,case a.sfyy when 1 then '已预约' else '未预约' end as 预约状态, b.yykssj as 预约日期 from qy_ygxx a left join dd_jbxx_new b on a.yybh=b.yybh and a.ydjh=b.dsfdd where 1=1";
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["sfyy"].ToString()))
{
sd.Append(" and a.sfyy=@sfyy");
paramList.Add("sfyy", ht["sfyy"].ToString());
}
if (!string.IsNullOrEmpty(ht["dwbh"].ToString()))
{
sd.Append(" and left(a.dwbh,4)=@dwbh");
paramList.Add("dwbh", ht["dwbh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dept"].ToString()))
{
sd.Append(" and a.dwbh=@dept");
paramList.Add("dept", ht["dept"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm like @xm");
paramList.Add("xm", "%" + ht["xm"].ToString() + "%");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh= @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["sfzh"].ToString()))
{
sd.Append(" and a.sfzh=@sfzh");
paramList.Add("sfzh", ht["sfzh"].ToString());
}
if (!string.IsNullOrEmpty(ht["yykssj"].ToString()))
{
sd.Append(" and b.yykssj>= @yykssj");
paramList.Add("yykssj", ht["yykssj"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyjssj"].ToString()))
{
sd.Append(" and b.yykssj<= @yyjssj");
paramList.Add("yyjssj", ht["yyjssj"].ToString());
}
sql += sd.ToString();
DataTable dt = new DataTable();
var reader = conn.ExecuteReader(sql, paramList);
dt.Load(reader);
return(dt);
}
}
