public void CheckSjmxJdzt(int id)
{
string strSQL = "select 0 as y, 0 as m, qzyjs as count, id as bm from YQSF_SJD where ID = (select sjdid from YQSF_SJMX as b where b.id = {0}";
STATS st = _DapperRepository.Query(string.Format(strSQL, id)).FirstOrDefault();
int yqjs = st.count;
int wtdid = st.bm;
strSQL = @"SELECT 0 as y, 0 as m, qzyjs as count, 0 as bm
FROM dbo.YQSF_SJMX AS a LEFT JOIN
dbo.YQSF_SJD as e on a.sjdid = e.id LEFT JOIN
dbo.JCXX_XHGG_BM AS b ON a.XHGGBM = b.XHGGBM LEFT JOIN
dbo.JCXX_QJMC_BM AS d ON b.QJMCBM = d.QJMCBM
where d.QJMCBM = 1000 and e.djrq>'2019-04-21' and jdzt=222 and e.sjdid={0}";
st = _DapperRepository.Query(string.Format(strSQL, wtdid)).FirstOrDefault();
int yqjs1 = st.count;
strSQL = "select 0 as y, 0 as m, count(0) as count,0 as bm from YQSF_SJMX where jdzt = '检完' and ID = {0}";
st = _DapperRepository.Query(string.Format(strSQL, id)).FirstOrDefault();
int yqjs2 = st.count;
if (yqjs == yqjs1 || yqjs == yqjs2)
{
strSQL = "update YQSF_SJD set qzyjdzt = N'检定完毕' where id = {0}";
st = _DapperRepository.Query(string.Format(strSQL, wtdid)).FirstOrDefault();
}
}
// 首页工作统计数字 - 全站仪
private string[] _GetHomeCountData(long id) // id -> JDYID
{
string strSQL = @"
SELECT 0 as y, 0 as m, count(0) as count,0 as bm
FROM dbo.YQSF_SJMX LEFT JOIN dbo.YQSF_SJD ON dbo.YQSF_SJD.ID = dbo.YQSF_SJMX.sjdid
LEFT JOIN dbo.YQSF_KH ON dbo.YQSF_SJD.khid = dbo.YQSF_KH.khid
LEFT JOIN dbo.JCXX_XHGG_BM ON dbo.YQSF_SJMX.XHGGBM = dbo.JCXX_XHGG_BM.XHGGBM
LEFT JOIN dbo.JCXX_QJMC_BM ON dbo.JCXX_XHGG_BM.QJMCBM = dbo.JCXX_QJMC_BM.QJMCBM
where dbo.JCXX_QJMC_BM.QJMC = N'全站仪'";
string cnt0 = _sjmxDapperRepository.Query(strSQL + " and year(dbo.YQSF_SJD.sjrq)= year(getdate())").FirstOrDefault().count.ToString(); // 今年
string cnt5 = _sjmxDapperRepository.Query(strSQL + " and year(dbo.YQSF_SJD.sjrq)= year(getdate())-1").FirstOrDefault().count.ToString(); // 去年
//string cnt9 = _sjmxDapperRepository.Query(strSQL + " and datediff(month,dbo.YQSF_SJD.sjrq,getdate())=0").FirstOrDefault().count.ToString(); // 本月
strSQL = @"
SELECT 0 as y, 0 as m, count(0) as count,0 as bm
FROM dbo.YQSF_SJMX AS a LEFT JOIN
dbo.YQSF_SJD as e on a.sjdid = e.id LEFT JOIN
dbo.YQSF_KH as f on e.khid = f.khid LEFT JOIN
dbo.JCXX_XHGG_BM AS b ON a.XHGGBM = b.XHGGBM LEFT JOIN
dbo.JCXX_ZZC_BM AS c ON b.ZZCBM = c.ZZCBM LEFT JOIN
dbo.JCXX_QJMC_BM AS d ON b.QJMCBM = d.QJMCBM LEFT JOIN
dbo.SJCL_CHYQ as g on g.id = a.id";
string cnt1 = _sjmxDapperRepository.Query(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21'").FirstOrDefault().count.ToString();
string cnt2 = _sjmxDapperRepository.Query(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21' and g.jdzt = 222 or a.jdzt = '检完'").FirstOrDefault().count.ToString();
string cnt3 = _sjmxDapperRepository.Query(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21' and (g.jdzt is null or g.jdzt=100) and a.jdzt<>'检完'").FirstOrDefault().count.ToString(); //待检
string cnt4 = _sjmxDapperRepository.Query(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21' and (g.jdzt>100 and g.jdzt < 122) and a.jdzt<>'检完'").FirstOrDefault().count.ToString(); //在检
//"and g.jdzt is not null and g.jdzt<> 222 and a.jdzt<>'检完'").FirstOrDefault().count.ToString();
string cnt6 = _sjmxDapperRepository.Query(string.Format(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21' " +
"and g.jdzt = 122 and a.jdzt<>'检完' and jdy<>{0}-100000", id)).FirstOrDefault().count.ToString(); //待核验记录
string cnt8 = _sjmxDapperRepository.Query(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21' and g.jdzt = 200 and a.jdzt<>'检完'").FirstOrDefault().count.ToString(); //待批准
string cnt9 = _sjmxDapperRepository.Query(strSQL + " where d.QJMCBM = 1000 and e.djrq>'2019-04-21' and a.jdzt<>'检完' and g.jdzt<>222").FirstOrDefault().count.ToString();
strSQL = @"
SELECT top 1 0 as y, 0 as m, jwrq as count,0 as bm
FROM dbo.YQSF_SJMX AS a LEFT JOIN
dbo.YQSF_SJD as e on a.sjdid = e.id LEFT JOIN
dbo.YQSF_KH as f on e.khid = f.khid LEFT JOIN
dbo.JCXX_XHGG_BM AS b ON a.XHGGBM = b.XHGGBM LEFT JOIN
dbo.JCXX_ZZC_BM AS c ON b.ZZCBM = c.ZZCBM LEFT JOIN
dbo.JCXX_QJMC_BM AS d ON b.QJMCBM = d.QJMCBM LEFT JOIN
dbo.SJCL_CHYQ as g on g.id = a.id
where g.jdzt = 122 and a.jdzt<>'检完' and jdy<>{0}-100000 ORDER BY jwrq asc";
STATS st = _sjmxDapperRepository.Query(string.Format(strSQL, id)).FirstOrDefault();
string cnt7 = DateTime.Now.ToString();
if (st != null)
{
cnt7 = DateTime.Parse(st.count.ToString()).ToString();
}
string[] Datas = new string[] { cnt0, cnt1, cnt2, cnt3, cnt4, cnt5, cnt6, cnt7, cnt8, cnt9 };
return(Datas);
}