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