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