private void DataInit() { PubFunc.DdlDataGet("DDL_SYS_DEPOTRANGE", UserAction.UserID, lstDEPTOUT, docDEPTOUT); lstDEPTOUT.SelectedValue = UserAction.UserDept; docDEPTOUT.SelectedValue = UserAction.UserDept; PubFunc.DdlDataGet("DDL_USER", lstLRY, docLRY, docSLR); //PubFunc.DdlDataGet("DDL_SYS_DEPTRANGE", UserAction.UserID, lstDEPTID, docDEPTID); PubFunc.DdlDataGet("DDL_SYS_DEPTRANGE", UserAction.UserID, lstDEPTID, docDEPTID); //PubFunc.DdlDataGet("DDL_BILL_STATUSCKD", lstFLAG, docFLAG); lstLRRQ1.SelectedDate = DateTime.Now.AddDays(-7); lstLRRQ2.SelectedDate = DateTime.Now; //获取客户化GRF文件地址 By c 2016年1月21日12:18:29 At 威海509 string grf = Doc.DbGetGrf("KSSHTXD"); if (!string.IsNullOrWhiteSpace(grf)) { SHTXD = grf; } string grf_fds = Doc.DbGetGrf("FDS_SHTXD"); if (grf_fds.Length > 0) { FDS_SHTXD = grf_fds; } //是否启用定数标签 By c 2016年1月27日12:07:12 At 威海509 string DSAUTO = Doc.DbGetSysPara("DSAUTO"); if (DSAUTO == "Y") { btnPBQ.Hidden = false; } }
private void DataInit() { PubFunc.DdlDataGet("DDL_USER", docLRY, docSQR, docSHR); //PubFunc.DdlDataGet("DDL_BILL_STATUSSLD", docFLAG); lstLRRQ1.SelectedDate = DateTime.Now.AddDays(-10); lstLRRQ2.SelectedDate = DateTime.Now; hfdDEPTID.Text = Doc.DbGetSysPara("DEFDEPT"); }
private void DataInit() { PubFunc.DdlDataGet("DDL_USER", docLRY); PubFunc.DdlDataGet("DDL_BILL_STATUSJSD", docFLAG); PubFunc.DdlDataGet("DDL_DOC_SUPID", ddlSUPID, lstSUPID); lstLRRQ1.SelectedDate = DateTime.Now.AddDays(-1); lstLRRQ2.SelectedDate = DateTime.Now; docJSKS.SelectedDate = DateTime.Now.AddDays(-1); docJSJS.SelectedDate = DateTime.Now; ddlSUPID.SelectedValue = Doc.DbGetSysPara("SUPPLIER"); }
protected override void billAudit() { if (Doc.DbGetSysPara("LOCKSTOCK") == "Y") { Alert.Show("系统库存已被锁定,请等待物资管理科结存处理完毕再做审核处理!", "消息提醒", MessageBoxIcon.Warning); return; } if (docFLAG.SelectedValue != "M") { Alert.Show("非新单不能提交!", "提示信息", MessageBoxIcon.Warning); return; } string strBillno = docSEQNO.Text; if (!Doc.getFlag(strBillno, "M", BillType)) { Alert.Show("此单据已被其他人处理,请刷新页面更新单据状态!", "警告提示", MessageBoxIcon.Warning); return; } SaveSuccess = false; save("Y"); if (SaveSuccess == false) { return; } SaveSuccess = false; //增加定数是否已经被提交出库判断 if (DbHelperOra.Exists(String.Format(@"SELECT 1 FROM (SELECT GDSEQ,COUNT(1) SL FROM DAT_CK_COM A WHERE A.SEQNO = '{0}' GROUP BY GDSEQ) A,DOC_GOODSCFG B WHERE A.GDSEQ = B.GDSEQ AND B.DEPTID = '{1}' AND A.SL > B.DSNUM - B.DSPOOL - B.NUM3", strBillno, docDEPTID.SelectedValue))) { Alert.Show("单据【" + strBillno + "】中部分定数已经被出库,请检查!", "提示信息", MessageBoxIcon.Warning); return; } if (BillOper(strBillno, "DECLARE") == 1) { Alert.Show("单据【" + strBillno + "】提交成功!"); OperLog("定数出库", "提交单据【" + docBILLNO.Text + "】"); billOpen(strBillno); } //else //{ // Alert.Show("单据【" + strBillno + "】提交失败,请刷新后重试!", "错误提示", MessageBoxIcon.Error); // billOpen(strBillno); //} }
protected override void billAudit() { if (Doc.DbGetSysPara("LOCKSTOCK") == "Y") { Alert.Show("系统库存已被锁定,请等待物资管理科结存处理完毕再做审核处理!", "消息提醒", MessageBoxIcon.Warning); return; } if (docFLAG.SelectedValue != "N") { Alert.Show("非新单不能审核!"); return; } SaveSuccess = false; save("Y"); if (SaveSuccess == false) { return; } SaveSuccess = false; string sql = @"SELECT WMSYS.WM_CONCAT(BARCODE) FROM (SELECT B.BARCODE FROM DAT_XS_COM A, DAT_GOODSDS_LOG B WHERE A.STR1 = B.BARCODE AND B.FLAG <> 'N' AND A.SEQNO = '{0}' UNION ALL SELECT B.BARCODE FROM DAT_XS_COM A, DAT_CK_BARCODE B WHERE A.STR1 = B.BARCODE AND B.FLAG <> 'N' AND A.SEQNO = '{0}')"; string strBillno = (DbHelperOra.GetSingle(string.Format(sql, docSEQNO.Text)) ?? "").ToString(); //增加条码状态验证 if (!PubFunc.StrIsEmpty(strBillno)) { Alert.Show("条码【" + strBillno + "】已被回收或退货", "提示信息", MessageBoxIcon.Warning); return; } strBillno = docSEQNO.Text; if (BillOper(strBillno, "AUDIT") == 1) { billLockDoc(true); Alert.Show("单据【" + strBillno + "】审核成功!"); OperLog("条码回收", "审核单据【" + docBILLNO.Text + "】"); billOpen(strBillno); } }
protected void YsSearch() { string currentMonth = DateTime.Now.ToString("MM"); string startTime = string.Empty; string endTime = string.Empty; string ACCOUNTDAY = Doc.DbGetSysPara("ACCOUNTDAY"); DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); if (ACCOUNTDAY == "31") { startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-01"); endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-01").AddMonths(1).AddDays(-1); } else { startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-" + ACCOUNTDAY).AddMonths(-1); endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-" + ACCOUNTDAY); } string strSql = string.Format(@"SELECT A.DEPTID,TO_CHAR(A.RQSJ, 'YYYY-MM') Monthly, SUM(JSJE + THJE) TOTAL,SUM(NVL(B.YSJE, 0)) YSTOTAL FROM VIEW_JS A, (SELECT to_char(A.YSRQ, 'YYYY-MM') Monthly, SUM(B.HSJE) YSJE FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.FLAG = 'Y' AND A.DEPTID = '{2}' AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY to_char(A.YSRQ, 'YYYY-MM')) B WHERE A.DEPTID = '{2}' AND RQSJ BETWEEN TO_DATE('{0}', 'yyyy-MM-dd') AND TO_DATE('{1}', 'yyyy-MM-dd') + 1 AND TO_CHAR(A.RQSJ, 'YYYY-MM') = B.Monthly(+) GROUP BY a.deptid,TO_CHAR(A.RQSJ, 'YYYY-MM') ", startDate.AddMonths(-2).ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), UserAction.UserDept); DataTable dt = DbHelperOra.Query(strSql).Tables[0]; String ysje, xsje; if (dt != null && dt.Rows.Count > 0) { ysje = dt.Rows[0]["YSTOTAL"].ToString(); xsje = dt.Rows[0]["TOTAL"].ToString(); } else { ysje = "0"; xsje = "0"; } PageContext.RegisterStartupScript("reloaddata(" + ysje + "," + xsje + ");"); }
private void BindDDL() { //PubFunc.DdlDataGet("DDL_SYS_DEPOT", ddlDEPTOUT, lstDeptOut); //PubFunc.DdlDataGet("DDL_SYS_DEPTDEF", ddlDEPTID, lstDeptIn); DepartmentBind.BindDDL("DDL_SYS_DEPTRANGE", UserAction.UserID, ddlDEPTID, lstDeptIn); DepartmentBind.BindDDL("DDL_SYS_DEPOTRANGE", UserAction.UserID, ddlDEPTOUT, lstDeptOut); PubFunc.DdlDataGet("DAT_LX", ddlXl); PubFunc.DdlDataGet("DAT_LOUDONG", lstHOUSE); PubFunc.DdlDataGet("DDL_LOUCENG", lstFLOOR); lstBEGRQ.SelectedDate = DateTime.Now.AddDays(-1); lstENDRQ.SelectedDate = DateTime.Now; lstLRRQ1.SelectedDate = DateTime.Now.AddDays(-1); lstLRRQ2.SelectedDate = DateTime.Now; //获取非定数出库模式,打印通行单时使用 By c 2016年1月16日20:36:36 At 威海509 hfdFDSMODE.Text = Doc.DbGetSysPara("FDSMODE"); }
private void BindDDL() { dpkDATE1.SelectedDate = DateTime.Now.AddMonths(-1); dpkDATE2.SelectedDate = DateTime.Now; dpkTIME1.SelectedDate = DateTime.Now.AddMonths(-1); dpkTIME2.SelectedDate = DateTime.Now; dpkdata1.SelectedDate = DateTime.Now.AddMonths(-1); dpkdata2.SelectedDate = DateTime.Now; lisData1.SelectedDate = DateTime.Now.AddMonths(-1); lisData2.SelectedDate = DateTime.Now; dpktim1.SelectedDate = DateTime.Now.AddMonths(-1); dpktim2.SelectedDate = DateTime.Now; DepartmentBind.BindDDL("DDL_SYS_DEPTRANGE", UserAction.UserID, ddlDEPTID, ddlDEPT, lisDEPT); PubFunc.DdlDataGet("DDL_DOC_SUPPLIERNULL", ddlSUPERID, ddlSUP); ddlSUPERID.SelectedValue = Doc.DbGetSysPara("SUPPLIER"); ddlSUP.SelectedValue = Doc.DbGetSysPara("SUPPLIER"); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindDDL(); billNew(); ButtonHidden(btnCopy, btnNext, btnBef, btnExport, btnAddRow); if (Request.QueryString["dg"] != null) { hfdDG.Text = Request.QueryString["dg"].ToString(); } //屏蔽不需要的操作按钮 if (Request.QueryString["oper"] != null) { hfdOper.Text = Request.QueryString["oper"].ToString(); if (Request.QueryString["oper"].ToString() == "input") { ButtonHidden(btnAudit, btnCancel); } else if (Request.QueryString["oper"].ToString() == "audit") { billLockDoc(true); ButtonHidden(btnNew, btnCopy, btnSave, btnAddRow, btnDelRow, btnGoods, btnCommit, btnAllCommit, btnDel); TabStrip1.ActiveTabIndex = 0; if (Request.QueryString["pid"] != null && !string.IsNullOrWhiteSpace(Request.QueryString["pid"].ToString())) { lstBILLNO.Text = Request.QueryString["pid"].ToString(); lstLRRQ1.SelectedDate = DateTime.Now.AddDays(90); billOpen(lstBILLNO.Text); } else { billSearch(); } } } hfdOneCode.Text = Doc.DbGetSysPara("ISONECODE"); if (PubFunc.StrIsEmpty(hfdOneCode.Text)) { hfdOneCode.Text = "Y"; } } }
protected override void billNew() { string strSup = docPSSID.SelectedValue; string strDept = docDEPTID.SelectedValue; //原单据保存判断 PubFunc.FormDataClear(FormDoc); if (PubFunc.StrIsEmpty(strDept)) { if (docDEPTID.Items.Count > 2) { strDept = docDEPTID.Items[1].Value; } } if (PubFunc.StrIsEmpty(strSup)) { strSup = Doc.DbGetSysPara("SUPPLIER"); } docFLAG.SelectedValue = "M"; docLRY.SelectedValue = UserAction.UserID; docCGY.SelectedValue = UserAction.UserID; docLRRQ.SelectedDate = DateTime.Now; docDHRQ.SelectedDate = DateTime.Now; docDEPTID.SelectedValue = strDept; docPSSID.SelectedValue = strSup; billLockDoc(false); rblOPER.SelectedValue = "GT"; comBZSL.Enabled = true; comMEMO.Enabled = true; GridCom.SummaryData = null; PageContext.RegisterStartupScript(GridCom.GetRejectChangesReference()); btnDel.Enabled = false; btnSave.Enabled = true; btnCommit.Enabled = false; btnAudit.Enabled = false; btnCancel.Enabled = false; btnPrint.Enabled = false; btnPrint.Enabled = false; btnDelRow.Enabled = true; btnGoods.Enabled = true; }
private void DataInit() { PubFunc.DdlDataGet("DDL_SYS_DEPT", lstDEPTOUT, docDEPTID); PubFunc.DdlDataGet("DDL_USER", docLRY); PubFunc.DdlDataGet("DDL_BILL_STATUSJSD", docFLAG); PubFunc.DdlDataGet("DDL_DOC_SHS", ddlSUPID); lstLRRQ1.SelectedDate = DateTime.Now.AddDays(-1); lstLRRQ2.SelectedDate = DateTime.Now; docJSKS.SelectedDate = DateTime.Now.AddMonths(-1); docJSJS.SelectedDate = DateTime.Now; ddlSUPID.SelectedValue = Doc.DbGetSysPara("SUPPLIER"); if (Request.QueryString["ISGZ"] != null && Request.QueryString["ISGZ"].ToString() == "G") { ddlNUM2.Items.RemoveAt(1); } if (Request.QueryString["ISGZ"] != null && Request.QueryString["ISGZ"].ToString() == "P") { ddlNUM2.Items.RemoveAt(0); } }
protected void btnSubmit_Click(object sender, EventArgs e) { if (hfdSEQNO.Text.Length < 1) { Alert.Show("调价单还未保存,不能提交!", "消息提示", MessageBoxIcon.Warning); return; } string flg = (string)DbHelperOra.GetSingle(string.Format("SELECT NVL(FLAG,'M') FROM DAT_TJ_DOC WHERE SEQNO='{0}'", hfdSEQNO.Text)); if (!PubFunc.StrIsEmpty(flg) && (",M").IndexOf(flg) < 0) { Alert.Show("调价单【" + hfdSEQNO.Text + "】不是新增单据,不能提交!", "消息提示", MessageBoxIcon.Warning); return; } //增加判断是否已有调价商品 Object Obj = DbHelperOra.GetSingle(String.Format(@"SELECT GDSEQ FROM DAT_TJ_COM WHERE SEQNO = '{0}' AND GDSEQ IN(SELECT GDSEQ FROM DAT_TJ_DOC A, DAT_TJ_COM B WHERE A.SEQNO = B.SEQNO AND A.FLAG = 'Y') AND ROWNUM = 1", hfdSEQNO.Text)); if ((Obj ?? "").ToString().Length > 0) { Alert.Show("商品" + Obj + "已存在调价单!", "提示信息", MessageBoxIcon.Warning); return; } List <CommandInfo> cmdList = new List <CommandInfo>(); cmdList.Add(new CommandInfo(string.Format("UPDATE DAT_TJ_DOC SET FLAG='N',SPR ='{1}',SPRQ=SYSDATE WHERE SEQNO='{0}' AND FLAG='M'", docBILLNO.Text, UserAction.UserID), null)); //增加待办事宜 if (!DbHelperOra.Exists("SELECT COUNT(1) FROM DAT_DO_LIST WHERE PARA='" + docBILLNO.Text.Trim() + "'")) { cmdList.Add(Doc.GETDOADD("DO_16", Doc.DbGetSysPara("DEFDEPT"), docLRY.SelectedValue, docBILLNO.Text)); } if (DbHelperOra.ExecuteSqlTran(cmdList)) { Alert.Show("调价单【" + hfdSEQNO.Text + "】提交成功", "消息提示"); OperLog("商品调价", "提交单据【" + docBILLNO.Text + "】"); billOpen(hfdSEQNO.Text); } }
public void GetBudgetAndExec() { // string strSQL = string.Format(@"SELECT F_GETYSTOTAL(A.DEPTID,'{0}','{1}') PRENUM, NVL(SUM(A.SUBSUM),0) AS EXENUM // FROM DAT_CK_DOC A WHERE A.FLAG IN ('Y', 'G') AND BILLTYPE='CKD' AND A.SHRQ BETWEEN TO_DATE('{1}', 'YYYY-MM-dd') AND TO_DATE('{2}', 'YYYY-MM-dd') + 1 AND A.DEPTID = '{3}' GROUP BY DEPTID", Convert.ToDateTime(docXSRQ.SelectedDate).AddMonths(-1).ToString("yyyy-MM") + "-01", Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM") + "-01", Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM-dd"), docDEPTID.SelectedValue.ToString()); string strSQL = string.Format(@"SELECT F_GETYSTOTAL(A.DEPTID,'{0}','{1}') PRENUM, ABS(NVL(SUM(B.HSJE),0)) AS EXENUM FROM DAT_XS_DOC A,DAT_XS_COM B WHERE A.SEQNO=B.SEQNO AND A.FLAG IN ('Y', 'G') AND A.SHRQ BETWEEN TO_DATE('{1}', 'YYYY-MM-dd') AND TO_DATE('{2}', 'YYYY-MM-dd') + 1 AND A.DEPTID = '{3}' GROUP BY DEPTID", Convert.ToDateTime(docXSRQ.SelectedDate).AddMonths(-1).ToString("yyyy-MM") + "-01", Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM") + "-01", Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM-dd"), docDEPTID.SelectedValue.ToString()); string ACCOUNTDAY = Doc.DbGetSysPara("ACCOUNTDAY"); if (ACCOUNTDAY != "31") { strSQL = string.Format(@"SELECT NVL((SELECT D.SUBSUM FROM DAT_YS_DOC D WHERE D.FLAG = 'S' AND D.SHRQ BETWEEN TO_DATE('{0}','YYYY-MM-dd') AND TO_DATE('{1}', 'YYYY-MM-dd') + 1 AND DEPTID =A.DEPTID AND D.Begrq<TO_DATE('{2}','YYYY-MM-DD') AND D.ENDRQ> TO_DATE('{2}','YYYY-MM-DD')),0) PRENUM, NVL(SUM(A.SUBSUM),0) AS EXENUM FROM DAT_CK_DOC A WHERE A.FLAG IN ('Y', 'G') AND BILLTYPE='CKD' AND A.SHRQ BETWEEN TO_DATE('{2}', 'YYYY-MM-dd') AND TO_DATE('{3}', 'YYYY-MM-dd') + 1 AND A.DEPTID = '{4}' GROUP BY DEPTID", Convert.ToDateTime((Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM") + "-" + ACCOUNTDAY)).AddMonths(-1).ToString("yyyy-MM-dd"), Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM") + "-" + ACCOUNTDAY, Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM-dd"), Convert.ToDateTime(docXSRQ.SelectedDate).AddMonths(1).ToString("yyyy-MM") + "-" + ACCOUNTDAY, docDEPTID.SelectedValue.ToString()); } DataTable dtnull = DbHelperOra.QueryForTable(strSQL); if (dtnull.Rows.Count < 1) { if (PubFunc.StrIsEmpty(docDEPTID.SelectedValue)) { Alert.Show("请先选择查询科室!", "消息提示", MessageBoxIcon.Warning); return; } strSQL = string.Format("SELECT F_GETYSTOTAL('{0}','{1}','{2}') PRENUM, 0 EXENUM FROM DUAL", docDEPTID.SelectedValue, Convert.ToDateTime(docXSRQ.SelectedDate).AddMonths(-1).ToString("yyyy-MM") + "-01", Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM") + "-01", Convert.ToDateTime(docXSRQ.SelectedDate).ToString("yyyy-MM-dd")); } DataTable dt = DbHelperOra.QueryForTable(strSQL); //if (dt != null && dt.Rows.Count > 0) //{ // docBuget.Text = "预算金额:" + dt.Rows[0]["PRENUM"].ToString() + " 元 已使用金额:" + dt.Rows[0]["EXENUM"].ToString() + "元 "; //} //else //{ // docBuget.Text = "预算金额:0 元 已使用金额:0 元 "; //} }
private string GetSearchSql() { string jzr = Doc.DbGetSysPara("ACCOUNTDAY");//获取系统结账日期 StringBuilder sbSql = new StringBuilder(); sbSql.AppendFormat(@"SELECT D.CODE DEPTID, D.NAME DEPTNAME, G.GDSEQ, G.GDNAME, G.GDSPEC, NVL(C.DSNUM, 0) DSNUM, NVL(C.NUM1, 0) NUM1, NVL(B.NUM2, 0) + NVL(C.DSPOOL, 0) NUM2, ((NVL(C.DSNUM, 0) - NVL(B.NUM2, 0) - NVL(C.DSPOOL, 0)) - FLOOR(NVL(A.KCSL, 0) / C.NUM1)) YCDS, C.ZDKC, C.ZGKC, NVL((SELECT SUM(ABS(DECODE(A.BILLTYPE, 'XST', DECODE(A.KCADD, '1', A.SL, 0), A.SL))) FROM DAT_GOODSJXC A, DOC_GOODS B WHERE A.GDSEQ = B.GDSEQ AND A.BILLTYPE IN ('DSH', 'XSD', 'XSG', 'XST') AND A.RQSJ >= TO_DATE('{0}', 'YYYY-MM-DD') + 1 AND A.DEPTID = D.CODE AND B.GDSEQ = G.GDSEQ), 0) BYXHS, FLOOR(NVL(A.KCSL, 0) / C.NUM1) KCSL, F_GETUNITNAME(G.UNIT) UNIT, G.HSJJ, ((NVL(C.DSNUM, 0) - NVL(B.NUM2, 0) - NVL(C.DSPOOL, 0)) - FLOOR(NVL(A.KCSL, 0) / C.NUM1)) * NVL(C.NUM1, 0) * G.HSJJ HSJE, G.PIZNO PZWH, G.PRODUCER, F_GETPRODUCERNAME(G.PRODUCER) PRODUCERNAME, G.SUPPLIER SUPID, F_GETSUPNAME(G.SUPPLIER) SUPNAME FROM DOC_GOODSCFG C, DOC_GOODS G, SYS_DEPT D, (SELECT K.DEPTID, K.GDSEQ, SUM(K.KCSL) KCSL FROM DAT_GOODSSTOCK K WHERE K.DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE IN ('3', '4')) GROUP BY K.DEPTID, K.GDSEQ) A, (SELECT TA.DEPTID DEPTID, TB.GDSEQ GDSEQ, COUNT(*) NUM2 FROM DAT_CK_DOC TA, DAT_CK_COM TB WHERE TA.SEQNO = TB.SEQNO AND TA.BILLTYPE = 'DSC' AND TA.FLAG = 'N' GROUP BY TA.DEPTID, TB.GDSEQ) B WHERE C.GDSEQ = G.GDSEQ AND C.DEPTID = D.CODE AND C.DEPTID = A.DEPTID(+) AND C.GDSEQ = A.GDSEQ(+) AND C.DEPTID = B.DEPTID(+) AND C.GDSEQ = B.GDSEQ(+) AND C.NUM1 > 0 AND ((NVL(C.DSNUM, 0) - NVL(B.NUM2, 0) - NVL(C.DSPOOL, 0)) - FLOOR(NVL(A.KCSL, 0) / C.NUM1)) > 0 AND NVL(A.KCSL, 0) < DECODE(NVL(C.ZDKC, 0), 0, 1, C.ZDKC) ", DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-" + jzr); //((NVL(C.DSNUM, 0) - NVL(B.NUM2, 0) - NVL(C.DSPOOL, 0)) - floor(A.KCSL/c.NUM1)) > 0 //定数数量 - 待出定数 - INT(库存数/定数含量)= 建议申领定数 if (!string.IsNullOrWhiteSpace(ddlDEPTID.SelectedValue)) { sbSql.AppendFormat(" AND C.DEPTID='{0}'", ddlDEPTID.SelectedValue); } if (!string.IsNullOrWhiteSpace(tbxGDSEQ.Text)) { sbSql.AppendFormat(" AND (G.GDSEQ LIKE '%{0}%' OR G.GDNAME LIKE '%{0}%' OR G.ZJM LIKE '%{0}%' OR G.BAR1 LIKE '%{0}%')", tbxGDSEQ.Text.Trim()); } sbSql.Append(" ORDER BY D.CODE,G.GDSEQ"); return(sbSql.ToString()); }
protected void btExport_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(tbxMonth.Text)) { Alert.Show("请选择[查询月份]!", MessageBoxIcon.Warning); return; } string startTime = string.Empty; string endTime = string.Empty; string ACCOUNTDAY = Doc.DbGetSysPara("ACCOUNTDAY"); DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); if (ACCOUNTDAY == "31") { //startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-01"); //endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-01").AddMonths(1).AddDays(-1); startDate = Convert.ToDateTime(tbxMonth.Text + "-01"); endDate = Convert.ToDateTime(tbxMonth.Text + "-01").AddMonths(1).AddDays(-1); } else { //startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-" + ACCOUNTDAY).AddMonths(-1); //endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-" + ACCOUNTDAY).AddDays(-1); startDate = Convert.ToDateTime(tbxMonth.Text + "-" + ACCOUNTDAY).AddMonths(-1); endDate = Convert.ToDateTime(tbxMonth.Text + "-" + ACCOUNTDAY).AddDays(-1); } string strSql = string.Format(@"SELECT v.DEPTID 部门编码, f_GetDEPTNAME(v.DEPTID) 部门名称, SUM(YSJE) 预算金额, SUM(SJJE) 执行金额, ROUND(DECODE(SUM(NVL(SJJE, 0)), 0, 0, sum(NVL(YSJE, 0)) / sum(NVL(SJJE, 0))), 4) 使用占比 FROM (SELECT SA.DEPTID, SD.GDSEQ, SD.GdNAME, SD.HSJJ, SD.GDSPEC, F_GETUNITNAME(SD.Unit) Unit, F_GETPRODUCERNAME(SD.Producer) PRODUCTER, NVL(SB.PDSL, 0) PDSL, NVL(SB.PDJE, 0) PDJE, NVL(SC.YSSL, 0) YSSL, NVL(SC.YSJE, 0) YSJE, NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) ERPSL, NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '1'), 0) ERPJE, F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}') STOCKSL, NVL((NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) - NVL(SB.PDSL, 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}')), 0) SJSL, NVL((NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) - NVL(SB.PDSL, 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}')), 0) * SD.HSJJ SJJE FROM (SELECT DISTINCT DEPTID, GDSEQ FROM (SELECT DEPTID, GDSEQ FROM DAT_GOODSJXC A WHERE RQSJ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') AND EXISTS (SELECT 1 FROM SYS_DEPT WHERE TYPE = '3' AND CODE = A.DEPTID) UNION SELECT A.DEPTID, B.GDSEQ FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.SHRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') UNION SELECT A.DEPTID, B.GDSEQ FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') ) UNION (SELECT DEPTID, GDSEQ FROM DAT_GOODSSTOCK WHERE DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE = '3'))) SA, (SELECT A.DEPTID, B.GDSEQ, SUM(B.PDSL) PDSL, SUM(B.HSJE) PDJE FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.SHRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY A.DEPTID, B.GDSEQ) SB, (SELECT A.DEPTID, B.GDSEQ, SUM(B.YSSL) YsSL, SUM(B.HSJE) YSJE FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY A.DEPTID, B.GDSEQ) SC, DOC_GOODS SD, (SELECT SUM(T.KCSL) STOCKSL, T.DEPTID, T.GDSEQ FROM DAT_STOCKDAY T WHERE NOT EXISTS (SELECT 1 FROM DAT_STOCKDAY WHERE TO_CHAR(RQ, 'MM') = to_char(t.RQ, 'MM') AND RQ > t.RQ) AND RQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY T.DEPTID, T.GDSEQ) SE WHERE SA.DEPTID = SB.DEPTID(+) AND SA.GDSEQ = SB.GDSEQ(+) AND SA.DEPTID = SC.DEPTID(+) AND SA.GDSEQ = SC.GDSEQ(+) AND SA.GDSEQ = SD.GDSEQ AND SA.DEPTID = SE.DEPTID(+) AND SA.GDSEQ = SE.GDSEQ(+)) v WHERE 1 = 1 ", startDate.ToString("yyyy -MM-dd"), endDate.ToString("yyyy-MM-dd")); //) v Group by DEPTID if (!string.IsNullOrEmpty(lstDEPTID.SelectedValue)) { strSql += " AND v.deptid='" + lstDEPTID.SelectedValue + "'"; } strSql += " Group by v.DEPTID "; ExcelHelper.ExportByWeb(DbHelperOra.Query(strSql).Tables[0], "全院科室" + startDate.ToString("MM") + "月预算执行情况报告", string.Format("全院科室" + startDate.ToString("MM") + "月预算执行情况分析表_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"))); }
protected override void billSearch() { if (string.IsNullOrEmpty(tbxMonth.Text)) { Alert.Show("请选择[查询月份]!", MessageBoxIcon.Warning); return; } string startTime = string.Empty; string endTime = string.Empty; string ACCOUNTDAY = Doc.DbGetSysPara("ACCOUNTDAY"); DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); if (ACCOUNTDAY == "31") { //startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-01"); //endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-01").AddMonths(1).AddDays(-1); startDate = Convert.ToDateTime(tbxMonth.Text + "-01"); endDate = Convert.ToDateTime(tbxMonth.Text + "-01").AddMonths(1).AddDays(-1); } else { //startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-" + ACCOUNTDAY).AddMonths(-1); //endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + ddlMonth.SelectedValue.ToString() + "-" + ACCOUNTDAY).AddDays(-1); startDate = Convert.ToDateTime(tbxMonth.Text + "-" + ACCOUNTDAY).AddMonths(-1); endDate = Convert.ToDateTime(tbxMonth.Text + "-" + ACCOUNTDAY).AddDays(-1); } //, (case when SUM(SJJE)>0 then sum(YSJE)/sum(SJJE) else 0 end) as PERRATE //,DECODE(SUM(NVL(SJJE,0)),0,0,sum(NVL(YSJE,0))/sum(NVL(SJJE,0))) PERRATE string strSql = string.Format(@"SELECT v.DEPTID, f_GetDEPTNAME(v.DEPTID) as DEPTNAME, SUM(YSJE) YSTOTAL, SUM(SJJE) TOTAL, ROUND(DECODE(SUM(NVL(YSJE, 0)), 0, 0, sum(NVL(SJJE, 0)) / sum(NVL(YSJE, 0))), 4) PERRATE FROM (SELECT SA.DEPTID, SD.GDSEQ, SD.GdNAME, SD.HSJJ, SD.GDSPEC, F_GETUNITNAME(SD.Unit) Unit, F_GETPRODUCERNAME(SD.Producer) PRODUCTER, NVL(SB.PDSL, 0) PDSL, NVL(SB.PDJE, 0) PDJE, NVL(SC.YSSL, 0) YSSL, NVL(SC.YSJE, 0) YSJE, NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) ERPSL, NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '1'), 0) ERPJE, F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}') STOCKSL, NVL((NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) - NVL(SB.PDSL, 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}')), 0) SJSL, NVL((NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}')), 0) * SD.HSJJ SJJE FROM (SELECT DISTINCT DEPTID, GDSEQ FROM (SELECT DEPTID, GDSEQ FROM DAT_GOODSJXC A WHERE RQSJ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') AND A.BILLTYPE<>'LCD' AND EXISTS (SELECT 1 FROM SYS_DEPT WHERE TYPE = '3' AND CODE = A.DEPTID) UNION SELECT A.DEPTID, B.GDSEQ FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.FLAG = 'Y' AND A.SPRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') UNION SELECT A.DEPTID, B.GDSEQ FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND B.YSSL > 0 AND A.FLAG = 'S' AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD'))) SA, (SELECT A.DEPTID, B.GDSEQ, SUM(B.PDSL) PDSL, SUM(B.HSJE) PDJE FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.FLAG = 'Y' AND A.SPRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY A.DEPTID, B.GDSEQ) SB, (SELECT A.DEPTID, B.GDSEQ, SUM(B.YSSL) YsSL, SUM(B.HSJE) YSJE FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.FLAG = 'S' AND B.YSSL > 0 AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY A.DEPTID, B.GDSEQ) SC, DOC_GOODS SD, (SELECT to_Char(RQ, 'MM') as MONTHLY, SUM(T.KCSL) STOCKSL, T.DEPTID, T.GDSEQ FROM DAT_STOCKDAY T WHERE NOT EXISTS (SELECT 1 FROM DAT_STOCKDAY WHERE TO_CHAR(RQ, 'MM') = to_char(t.RQ, 'MM') AND RQ > t.RQ) AND RQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY to_Char(RQ, 'MM') , T.DEPTID, T.GDSEQ) SE WHERE SA.DEPTID = SB.DEPTID(+) AND SA.GDSEQ = SB.GDSEQ(+) AND SA.DEPTID = SC.DEPTID(+) AND SA.GDSEQ = SC.GDSEQ(+) AND SA.GDSEQ = SD.GDSEQ(+) AND SA.GDSEQ = SE.GDSEQ(+) AND SA.DEPTID = SE.DEPTID(+) ) v WHERE 1 = 1 ", startDate.ToString("yyyy -MM-dd"), endDate.ToString("yyyy-MM-dd")); //) v Group by DEPTID if (!string.IsNullOrEmpty(lstDEPTID.SelectedValue)) { strSql += " AND v.deptid='" + lstDEPTID.SelectedValue + "'"; } strSql += " Group by v.DEPTID "; int total = 0; DataTable dt = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, strSql, ref total); GridList.DataSource = dt; GridList.RecordCount = total; GridList.DataBind(); decimal ddslTotal = 0, bzslTotal = 0; if (total > 0) { foreach (DataRow dr in dt.Rows) { ddslTotal += Convert.ToDecimal(dr["ystotal"] ?? 0); bzslTotal += Convert.ToDecimal(dr["total"] ?? 0); } } JObject summary = new JObject(); summary.Add("DEPTNAME", "本页合计"); summary.Add("YSJE", ddslTotal.ToString("F2")); summary.Add("TOTAL", bzslTotal.ToString("F2")); // summary.Add("HSJE", feeTotal.ToString("F2")); GridList.SummaryData = summary; }
protected override void billAddRow() { if (docFLAG.SelectedValue != "N") { Alert.Show("非『新增』单据不能增行!", "消息提示", MessageBoxIcon.Warning); return; } if (PubFunc.FormDataCheck(FormDoc).Length > 1) { return; } List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList(); if (newDict.Count == 0) { if (Doc.DbGetSysPara("ISAUTOAPPLY") == "Y") { string sql = @"SELECT A.*, A.ZGKC - A.KCSL DHSL, (A.ZGKC - A.KCSL) / A.BZHL BZSL, ((A.ZGKC - A.KCSL) / A.BZHL) * A.HSJJ HSJE,A.KCSL NUM1 FROM (SELECT G.GDSEQ, G.BARCODE, G.GDNAME, G.GDSPEC, G.UNIT, F_GETUNITNAME(G.UNIT) UNITNAME, G.BZHL, G.JXTAX, G.HSJJ, G.ZPBH, G.PRODUCER, F_GETPRODUCERNAME(G.PRODUCER) PRODUCERNAME, PZ.HJCODE1, '自动生成' MEMO, G.ISLOT, G.ISGZ, PZ.ZDKC, PZ.ZGKC, (SELECT NVL(SUM(KCSL), 0) FROM DAT_GOODSSTOCK WHERE GDSEQ = G.GDSEQ) KCSL FROM DOC_GOODSCFG PZ, DOC_GOODS G WHERE PZ.GDSEQ = G.GDSEQ AND PZ.ZDKC > 0 AND PZ.ZGKC > 0 AND PZ.DEPTID = '{0}') A WHERE KCSL < ZDKC"; DataTable dt = DbHelperOra.Query(string.Format(sql, docDEPTID.SelectedValue)).Tables[0]; decimal bzslTotal = 0, feeTotal = 0; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { bzslTotal += Convert.ToDecimal(row["BZSL"]); feeTotal += Convert.ToDecimal(row["BZSL"]) * Convert.ToDecimal(row["HSJJ"]) * Convert.ToDecimal(row["BZHL"]); LoadGridRow(row, false, "OLD"); } } else { PubFunc.GridRowAdd(GridGoods, "INIT"); } //增加合计 JObject summary = new JObject(); summary.Add("GDNAME", "本页合计"); summary.Add("BZSL", bzslTotal.ToString()); summary.Add("HSJE", feeTotal.ToString("F2")); GridGoods.SummaryData = summary; } else { PubFunc.GridRowAdd(GridGoods, "INIT"); } } else { PubFunc.GridRowAdd(GridGoods, "INIT"); } PubFunc.FormLock(FormDoc, true, ""); }
protected void btnOk() { if (Doc.DbGetSysPara("LOCKSTOCK") == "Y") { Alert.Show("系统库存已被锁定,请等待物资管理科结存处理完毕再做审核处理!", "消息提醒", MessageBoxIcon.Warning); return; } #region 数据有效性验证 decimal subsum = 0;//总金额 if (docFLAG.SelectedValue != "S") { Alert.Show("非『已审批』单据不能申领确定!", "提示信息", MessageBoxIcon.Warning); return; } List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList().OrderBy(x => x["GDSEQ"]).ToList(); if (newDict.Count == 0) { Alert.Show("请输入商品信息", "消息提示", MessageBoxIcon.Warning); return; } //验证科室是否盘点 if (DbHelperOra.Exists("SELECT 1 FROM DAT_PD_LOCK WHERE DEPTID IN('" + docDEPTOUT.SelectedValue + "','" + docDEPTID.SelectedValue + "') AND FLAG='N'")) { Alert.Show("出库或申领科室正在盘点,请检查!"); return; } List <Dictionary <string, object> > goodsData = new List <Dictionary <string, object> >(); //判断是否有空行 for (int i = 0; i < newDict.Count; i++) { if (!string.IsNullOrWhiteSpace(newDict[i]["GDSEQ"].ToString()) && !string.IsNullOrWhiteSpace(newDict[i]["GDNAME"].ToString())) { goodsData.Add(newDict[i]); subsum = subsum + decimal.Parse(newDict[i]["HSJE"].ToString()); } } if (goodsData.Count == 0)//所有Gird行都为空行时 { Alert.Show("商品信息不能为空", "消息提示", MessageBoxIcon.Warning); return; } //验证单据信息 if (DbHelperOra.Exists("SELECT 1 FROM DAT_CK_DOC WHERE SEQNO = '" + docBILLNO.Text + "'") && docBILLNO.Enabled) { Alert.Show("您输入的单号已存在,请检查!"); return; } #endregion if (PubFunc.StrIsEmpty(docBILLNO.Text)) { docSEQNO.Text = BillSeqGet(); docBILLNO.Text = docSEQNO.Text; docBILLNO.Enabled = false; } else { string flg = (string)DbHelperOra.GetSingle(string.Format("SELECT NVL(FLAG,'N') FROM DAT_CK_DOC WHERE SEQNO='{0}'", docBILLNO.Text)); if (!PubFunc.StrIsEmpty(flg) && (",N,R,S").IndexOf(flg) < 0) { Alert.Show("您输入的单据号存在重复信息,请重新输入或置空!", "消息提示", MessageBoxIcon.Warning); return; } else { docSEQNO.Text = docBILLNO.Text; docBILLNO.Enabled = false; } } MyTable mtType = new MyTable("DAT_CK_DOC"); mtType.ColRow = PubFunc.FormDataHT(FormDoc); mtType.ColRow["SEQNO"] = docBILLNO.Text; mtType.ColRow.Add("BILLTYPE", BillType); mtType.ColRow.Add("SUBNUM", goodsData.Count); mtType.ColRow.Add("SUBSUM", subsum); mtType.ColRow.Add("XSTYPE", "1"); List <CommandInfo> cmdList = new List <CommandInfo>(); MyTable mtTypeMx = new MyTable("DAT_CK_COM"); //MyTable mtTypeExt = new MyTable("DAT_CK_EXT"); //先删除单据信息在插入 cmdList.Add(mtType.DeleteCommand("")); //删除单据台头 cmdList.Add(new CommandInfo("delete dat_ck_com where seqno='" + docBILLNO.Text + "'", null)); //删除单据明细 cmdList.AddRange(mtType.InsertCommand()); for (int i = 0; i < goodsData.Count; i++) { mtTypeMx.ColRow = PubFunc.GridDataGet(goodsData[i]); mtTypeMx.ColRow.Add("SEQNO", docBILLNO.Text); mtTypeMx.ColRow["ROWNO"] = i + 1; mtTypeMx.ColRow.Add("PHID", mtTypeMx.ColRow["PH"]); mtTypeMx.ColRow["XSSL"] = decimal.Parse(mtTypeMx.ColRow["BZHL"].ToString()) * decimal.Parse(mtTypeMx.ColRow["BZSL"].ToString()); mtTypeMx.ColRow["HSJE"] = decimal.Parse(mtTypeMx.ColRow["HSJJ"].ToString()) * decimal.Parse(mtTypeMx.ColRow["BZSL"].ToString()); if (decimal.Parse(mtTypeMx.ColRow["XSSL"].ToString()) < 0 || (decimal.Parse(mtTypeMx.ColRow["XSSL"].ToString()) > decimal.Parse(mtTypeMx.ColRow["DHSL"].ToString()) && (mtTypeMx.ColRow["NUM1NAME"].ToString() == "非赠品"))) { Alert.Show("单据号中存在拣货数量小于0或拣货数量大于申领数量!", "消息提示", MessageBoxIcon.Warning); return; } mtTypeMx.ColRow.Add("BHSJJ", 0); mtTypeMx.ColRow.Add("BHSJE", 0); mtTypeMx.ColRow.Remove("UNITNAME"); mtTypeMx.ColRow.Remove("PRODUCERNAME"); cmdList.Add(mtTypeMx.Insert()); } OracleParameter[] parameters = { new OracleParameter("VTASKID", OracleDbType.Varchar2, 20), new OracleParameter("VPARA", OracleDbType.Varchar2, 800) }; parameters[0].Value = BillType; parameters[1].Value = "'" + docBILLNO.Text + "','" + BillType + "','" + UserAction.UserID + "','AUDIT'"; cmdList.Add(new CommandInfo("P_EXECTASK", parameters, CommandType.StoredProcedure)); bool flag = false; try { flag = DbHelperOra.ExecuteSqlTran(cmdList); } catch (Exception ex) { Alert.Show(errorParse(ex.Message), "消息提示", MessageBoxIcon.Warning); return; } if (flag) { billLockDoc(true); //增加待办事宜 DbHelperOra.ExecuteSql("UPDATE DAT_DO_LIST SET FLAG = 'Y' WHERE PARA='" + docBILLNO.Text.Trim() + "'"); if (DbHelperOra.Exists("select 1 from dat_ck_com where seqno = '" + docBILLNO.Text.Trim() + "' and gdseq in (select gdseq from doc_goods where catid0 = 1 )") == true) { Alert.Show("商品出库确认完成!需要打印试剂条码!", "消息提示", MessageBoxIcon.Information); } else { Alert.Show("商品出库确认完成!", "消息提示", MessageBoxIcon.Information); OperLog("科室申领", "出库确认单据【" + docBILLNO.Text + "】"); } billOpen(docBILLNO.Text); } else { Alert.Show("商品出库确认出错!!!", "错误提示", MessageBoxIcon.Error); } }
protected override void billSearch() { string crrentMonth = DateTime.Now.ToString("yyyy-MM"); if (dpkMonth.Text != "") { crrentMonth = dpkMonth.Text; } string startTime = string.Empty; string endTime = string.Empty; string ACCOUNTDAY = Doc.DbGetSysPara("ACCOUNTDAY"); DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); if (ACCOUNTDAY == "31") { startDate = Convert.ToDateTime(crrentMonth + "-01"); endDate = Convert.ToDateTime(crrentMonth + "-01").AddMonths(1).AddDays(-1); } else { startDate = Convert.ToDateTime(crrentMonth + "-" + ACCOUNTDAY).AddMonths(-1); endDate = Convert.ToDateTime(crrentMonth + "-" + ACCOUNTDAY).AddDays(-1); } string strSql = string.Format(@"SELECT DEPTID, SUM(NVL(ysje, 0)) YSTOTAL, SUM(NVL(sjje, 0)) as TOTAL, Monthly FROM (SELECT SA.DEPTID, SD.GDSEQ, SD.GdNAME, SD.HSJJ, SD.GDSPEC, F_GETUNITNAME(SD.Unit) Unit, F_GETPRODUCERNAME(SD.Producer) PRODUCTER, NVL(SB.PDSL, 0) PDSL, NVL(SB.PDJE, 0) PDJE, NVL(SC.YSSL, 0) YSSL, NVL(SC.YSJE, 0) YSJE, NVL(F_GETXHSLBYMONTH(SA.DEPTID, SA.GDSEQ, SA.MONTHLY, '{3}', '0'), 0) ERPSL, NVL(F_GETXHSLBYMONTH(SA.DEPTID, SA.GDSEQ, SA.MONTHLY, '{3}', '1'), 0) ERPJE, F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}') STOCKSL, NVL((NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) - NVL(SB.PDSL, 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}')), 0) SJSL, NVL((NVL(F_GETXHSL(SA.DEPTID, SA.GDSEQ, '{0}', '{1}', '0'), 0) - NVL(SB.PDSL, 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,'{1}')), 0) * SD.HSJJ SJJE, SA.MONTHLY FROM (SELECT DISTINCT DEPTID, GDSEQ, MONTHLY FROM (SELECT DEPTID, GDSEQ, to_char(RQSJ, 'MM') as MONTHLY FROM DAT_GOODSJXC A WHERE RQSJ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') AND EXISTS (SELECT 1 FROM SYS_DEPT WHERE TYPE = '3' AND CODE = A.DEPTID) UNION SELECT A.DEPTID, B.GDSEQ, to_char(A.SHRQ, 'MM') as Monthly FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.SHRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') UNION SELECT A.DEPTID, B.GDSEQ, to_char(A.SHRQ, 'MM') as Monthly FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') UNION (SELECT DEPTID, GDSEQ, to_Char(RQ, 'MM') as MONTHLY FROM DAT_STOCKDAY WHERE DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE = '3') AND RQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') ))) SA, (SELECT A.DEPTID,B.GDSEQ, SUM(B.PDSL) PDSL, SUM(B.HSJE) PDJE, to_char(A.SPRQ, 'MM') as Monthly FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.SPRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY A.DEPTID, B.GDSEQ, to_char(A.SPRQ, 'MM')) SB, (SELECT A.DEPTID,B.GDSEQ, SUM(B.YSSL) YsSL, SUM(B.HSJE) YSJE, to_char(A.YSRQ, 'MM') as Monthly FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY A.DEPTID,B.GDSEQ, to_char(A.YSRQ, 'MM')) SC, DOC_GOODS SD, (SELECT to_Char(RQ, 'MM') as MONTHLY, SUM(T.KCSL) STOCKSL, T.DEPTID, T.GDSEQ FROM DAT_STOCKDAY T WHERE NOT EXISTS (SELECT 1 FROM DAT_STOCKDAY WHERE TO_CHAR(RQ, 'MM') = to_char(t.RQ, 'MM') AND RQ > t.RQ) AND RQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') GROUP BY to_Char(RQ, 'MM') , T.DEPTID, T.GDSEQ) SE WHERE SA.DEPTID = SB.DEPTID(+) AND SA.MONTHLY = SB.MONTHLY(+) AND SA.GDSEQ = SB.GDSEQ(+) AND SA.MONTHLY = SB.MONTHLY(+) AND SA.DEPTID = SC.DEPTID(+) AND SA.GDSEQ = SC.GDSEQ(+) AND SA.MONTHLY = SC.MONTHLY(+) AND SA.GDSEQ = SD.GDSEQ AND SA.DEPTID = SE.DEPTID(+) AND SA.GDSEQ = SE.GDSEQ(+) AND SA.MONTHLY = SE.MONTHLY(+) AND SA.GDSEQ = SE.GDSEQ(+) AND SA.DeptID = {2}) GROUP BY MONTHLY, DEPTID ", startDate.AddMonths(-2).ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), UserAction.UserDept, ACCOUNTDAY); DataTable dt = new DataTable(); try { dt = DbHelperOra.Query(strSql).Tables[0]; } catch (Exception) { Alert.Show("工作台权限绑定错误!", "提示信息", MessageBoxIcon.Warning); return; throw; } string sbBuget = string.Empty; string sbActual = string.Empty; string strDeptSql = "SELECT Name FROM sys_Dept where code='" + UserAction.UserDept + "'"; object strDeptName = DbHelperOra.GetSingle(strDeptSql); string sysTit = strDeptName != null?strDeptName.ToString() + "科室月度预算执行情况分析" : "科室月度预算执行情况分析"; string sysSubTit = startDate.ToString("yyyy年MM月") + "预算使用情况比较, 单位:(元)"; string footLabel = "来源:系统数据库"; string labelarr = string.Empty;// startDate.AddMonths(-2).ToString("MM") + "月," + startDate.AddMonths(-1).ToString("MM") + "月," + startDate.ToString("MM") + "月"; decimal[] iBudget = new decimal[3]; decimal[] iActival = new decimal[3]; if (dt != null && dt.Rows.Count > 0) { int iRow = dt.Rows.Count < 3 ? dt.Rows.Count : 3; DataRow[] RowArr = dt.Select("", "Monthly ASC"); for (int i = 0; i < iRow; i++) { labelarr += RowArr[i]["Monthly"].ToString() + "月,"; iBudget[i] = Convert.ToDecimal(RowArr[i]["ystotal"] != DBNull.Value ? RowArr[i]["ystotal"] : 0); iActival[i] = Convert.ToDecimal(RowArr[i]["total"] != DBNull.Value ? RowArr[i]["total"] : 0); } } else { iBudget[0] = 0; iBudget[1] = 0; iBudget[2] = 0; iActival[0] = 0; iActival[1] = 0; iActival[2] = 0; } labelarr = labelarr.TrimEnd(','); var data = new ArrayList() { new { name = "执行", value = iActival, color = "#1385a5", }, new { name = "预算", value = iBudget, color = "#c56966", } }; decimal aMax = iActival.Max(); decimal bMax = iBudget.Max(); decimal iMax = aMax > bMax ? aMax + aMax / 5 : bMax + bMax / 5; PageContext.RegisterStartupScript("reloaddata('" + labelarr + "','" + sysTit + "','" + sysSubTit + "','" + footLabel + "','" + JsonConvert.SerializeObject(data) + "'," + iMax + ");"); }
protected override void billSearch() { string currentMonth = DateTime.Now.ToString("MM"); string startTime = string.Empty; string endTime = string.Empty; string ACCOUNTDAY = Doc.DbGetSysPara("ACCOUNTDAY"); DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); if (ACCOUNTDAY == "31") { startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-01"); endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-01").AddMonths(1); } else { startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-" + ACCOUNTDAY).AddMonths(-1); endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy") + "-" + currentMonth + "-" + ACCOUNTDAY); } //公式:科室本月实际消耗数量=ERP中科室使用消耗数-库存盘点数+系统库存数, //公式:ERP中科室使用消耗数=科室领用数-科室退货数。天津项目是没有定数的 string strSql = string.Format(@"SELECT DISTINCT DEPTID, NVL(YSJE, 0) YSTOTAL, NVL(SJJE, 0) as TOTAL, Monthly FROM (SELECT SA.DEPTID, NVL(SC.YSJE, 0) YSJE, NVL((NVL(F_GETXHSLBYMONTH(SA.DEPTID, SA.GDSEQ, SA.MONTHLY, '{4}', '0'), 0) - NVL(SB.PDSL, 0) + F_GETKC(SA.DEPTID,SD.GDSEQ,ADD_MONTHS(TO_DATE(SA.MONTHLY||'-01','YYYY-MM-DD')-1,1))), 0) * SD.HSJJ SJJE, SA.MONTHLY FROM (SELECT DISTINCT DEPTID, GDSEQ, MONTHLY FROM (SELECT DEPTID, GDSEQ, to_char(RQSJ, 'YYYY-MM') as MONTHLY FROM DAT_GOODSJXC A WHERE RQSJ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1 AND EXISTS (SELECT 1 FROM SYS_DEPT WHERE TYPE = '3' AND CODE = A.DEPTID) UNION SELECT A.DEPTID, B.GDSEQ, to_char(A.SHRQ, 'YYYY-MM') as Monthly FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.SPRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1 UNION SELECT A.DEPTID, B.GDSEQ, to_char(A.SHRQ, 'YYYY-MM') as Monthly FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1 UNION (SELECT DEPTID, GDSEQ, to_Char(RQ, 'YYYY-MM') as MONTHLY FROM DAT_STOCKDAY WHERE DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE = '3') AND RQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1)))SA, (SELECT A.DEPTID, B.GDSEQ, SUM(B.PDSL) PDSL, to_char(A.SPRQ, 'YYYY-MM') as Monthly FROM DAT_PD_DOC A, DAT_PD_COM B WHERE A.SEQNO = B.SEQNO AND A.PDTYPE = '3' AND A.SPRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1 GROUP BY A.DEPTID, GDSEQ, to_char(A.SPRQ, 'YYYY-MM')) SB, (SELECT A.DEPTID, GDSEQ, SUM(B.HSJE) YSJE, to_char(A.YSRQ, 'YYYY-MM') as Monthly FROM DAT_YS_DOC A, DAT_YS_COM B WHERE A.SEQNO = B.SEQNO AND A.YSRQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1 GROUP BY A.DEPTID, B.GDSEQ, to_char(A.YSRQ, 'YYYY-MM')) SC, DOC_GOODS SD, (SELECT to_Char(RQ, 'YYYY-MM') as MONTHLY, SUM(T.KCSL) STOCKSL, T.DEPTID, T.GDSEQ FROM DAT_STOCKDAY T WHERE NOT EXISTS (SELECT 1 FROM DAT_STOCKDAY WHERE TO_CHAR(RQ, 'YYYY-MM') = to_char(t.RQ, 'YYYY-MM') AND RQ > t.RQ) AND RQ BETWEEN TO_DATE('{0}', 'YYYY-MM-DD') AND TO_DATE('{1}', 'YYYY-MM-DD') + 1 GROUP BY to_Char(RQ, 'YYYY-MM') , T.DEPTID, T.GDSEQ) SE WHERE SA.DEPTID = SB.DEPTID(+) AND SA.GDSEQ = SB.GDSEQ(+) AND SA.MONTHLY = SB.MONTHLY(+) AND SA.DEPTID = SC.DEPTID(+) AND SA.GDSEQ = SC.GDSEQ(+) AND SA.MONTHLY = SC.MONTHLY(+) AND SA.GDSEQ = SD.GDSEQ AND SA.DEPTID = SE.DEPTID(+) AND SA.GDSEQ = SE.GDSEQ(+) AND SA.MONTHLY = SE.MONTHLY(+) AND SA.DeptID = '{2}' AND SA.GDSEQ = '{3}') ", startDate.AddMonths(-2).ToString("yyyy - MM - dd"), endDate.AddDays(-1).ToString("yyyy - MM - dd"), hfDeptID.Text, hfgdSeq.Text.Trim(), ACCOUNTDAY); //strSql += " group by to_char(rqsj, 'mm'), deptid, gdseq "; DataTable dt = DbHelperOra.Query(strSql).Tables[0]; string sbBuget = string.Empty; string sbActual = string.Empty; string sysTit = "单个商品月度执行情况分析"; string strGDSql = "select gdname from doc_goods where gdseq='" + hfgdSeq.Text + "'"; object objName = DbHelperOra.GetSingle(strGDSql); string sysSubTit = startDate.ToString("yyyy年MM月") + objName.ToString() + "使用情况比较, 单位:(元)"; string footLabel = "来源:系统数据库"; string labelarr = string.Empty;// startDate.AddMonths(-2).ToString("MM") + "月," + startDate.AddMonths(-1).ToString("MM") + "月," + startDate.ToString("MM") + "月"; decimal[] iBudget = new decimal[3]; decimal[] iActival = new decimal[3]; if (dt != null && dt.Rows.Count > 0) { int iRow = dt.Rows.Count < 3 ? dt.Rows.Count : 3; DataRow[] RowArr = dt.Select("1=1", "Monthly Asc"); for (int i = 0; i < iRow; i++) { labelarr += RowArr[i]["Monthly"].ToString() + "月,"; iBudget[i] = Convert.ToDecimal(RowArr[i]["ystotal"] != DBNull.Value ? RowArr[i]["ystotal"] : 0); iActival[i] = Convert.ToDecimal(RowArr[i]["total"] != DBNull.Value ? RowArr[i]["total"] : 0); // iBudget[i] = 150; // iActival[i] =200; } } else { iBudget[0] = 0; iBudget[1] = 0; iBudget[2] = 0; iActival[0] = 0; iActival[1] = 0; iActival[2] = 0; } labelarr = labelarr.TrimEnd(','); var data = new ArrayList() { new { name = "执行", value = iActival, color = "#1385a5", }, new { name = "预算", value = iBudget, color = "#c56966", } }; decimal aMax = iActival.Max(); decimal bMax = iBudget.Max(); decimal iMax = aMax > bMax ? aMax + aMax / 5 : bMax + bMax / 5; PageContext.RegisterStartupScript("reloaddata('" + labelarr + "','" + sysTit + "','" + sysSubTit + "','" + footLabel + "','" + JsonConvert.SerializeObject(data) + "'," + iMax + ");"); }