private string GetSearchSql()
{
string strSql = @"SELECT SD.NAME DEPTNAME,B.GDSEQ,B.GDNAME,B.GDSPEC,F_GETUNITNAME(B.UNIT) UNIT,B.HSJJ,SUM(B.XSSL) SL,SUM(B.HSJE) JE,G.HISCODE
FROM DAT_CK_DOC A, DAT_CK_COM B, SYS_DEPT SD, DOC_GOODS G
WHERE A.SEQNO = B.SEQNO AND B.GDSEQ = G.GDSEQ AND A.DEPTID = SD.CODE AND A.FLAG='Y' ";
string strWhere = " ";
//if (!PubFunc.StrIsEmpty(SELECTSUPID.Text.Trim())) strWhere += " AND A.SUPID = '" + SELECTSUPID.Text.Trim() + "'";
if (!PubFunc.StrIsEmpty(ddlDEPTID.SelectedValue))
{
strWhere += " AND A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(tbxGOODS.Text))
{
strWhere += " and (G.GDSEQ like '%" + tbxGOODS.Text + "%' or g.zjm like '%" + tbxGOODS.Text + "%' or g.gdname like '%" + tbxGOODS.Text + "%')";
}
//if (!PubFunc.StrIsEmpty(SELECTPRODUCERID.Text.Trim())) strWhere += " and g.producer = '" + SELECTPRODUCERID.Text.Trim() + "'";
strWhere += string.Format(" and A.XSRQ>=TO_DATE('{0}','YYYY-MM-DD') and A.XSRQ < TO_DATE('{1}','YYYY-MM-DD')+1 ", lstLRRQ1.Text, lstLRRQ2.Text);
//strWhere += string.Format(" AND a.deptid in( select code FROM SYS_DEPT where F_CHK_DATARANGE(CODE, '{0}') = 'Y' )", UserAction.UserID);
if (strWhere != " ")
{
strSql = strSql + strWhere;
}
strSql += string.Format(" GROUP BY SD.NAME, B.GDSEQ, B.GDNAME, B.GDSPEC, B.UNIT, B.HSJJ, G.HISCODE ORDER BY {0} {1}", GridGoods.SortField, GridGoods.SortDirection);
return(strSql);
}
protected override void billAudit()
{
if (docSEQNO.Text.Length < 1)
{
Alert.Show("请选择需要付款的结算单!");; return;
}
if (docFLAG.SelectedValue != "Y")
{
Alert.Show("未审核单据或已付款单据,不能付款!");
return;
}
//付款金额不增加控制
if (PubFunc.StrIsEmpty(tgbFPHM.Text))
{
docFPJE.Text = "0";
}
if (Convert.ToDecimal(docFPJE.Text) < Convert.ToDecimal(nbxJSJE.Text) && !PubFunc.StrIsEmpty(tgbFPHM.Text))
{
Alert.Show("发票金额不能小于实结金额!");
return;
}
nbxWJJE.Text = (Convert.ToDecimal(nbbYJJJ.Text) - Convert.ToDecimal(nbxJSJE.Text)).ToString();
DbHelperOra.ExecuteSql("update dat_jsd_doc set flag='G',JSJE=" + nbxJSJE.Text + ",WJJE=" + nbxWJJE.Text + ",FPJE=" + docFPJE.Text + ",UPTTIME =sysdate,FKY='" + ddlFKY.SelectedValue + "',FKRQ=to_date('" + dpkFKRQ.Text + "','YYYY-MM-DD'),memo='" + tbxMEMO.Text + "',FPHM ='" + tgbFPHM.Text + "' where seqno ='" + docSEQNO.Text + "'");
//将出库单标志改为'G'
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("BILLNO", OracleDbType.Varchar2),
new OracleParameter("FPHM", OracleDbType.Varchar2)
};
parameters[0].Value = docSEQNO.Text;
parameters[1].Value = tgbFPHM.Text;
DbHelperOra.RunProcedure("P_JSD_FP", parameters);
Alert.Show("单据【" + docSEQNO.Text + "】付款成功!");
billOpen(docSEQNO.Text);
}
protected override void billAudit()
{
//借货单直接审核
if (docFLAG.SelectedValue == "Y" || docFLAG.SelectedValue == "G")
{
Alert.Show("单据状态不正确,请检查！", "提示信息", MessageBoxIcon.Question);
return;
}
string bill = docSEQNO.Text;
if (PubFunc.StrIsEmpty(bill))
{
Alert.Show("请选择需要审核的单据！", "提示信息", MessageBoxIcon.Warning);
return;
}
SaveSuccess = false;
save("Y");
if (SaveSuccess == false)
{
return;
}
SaveSuccess = false;
if (DbHelperOra.ExecuteSql(string.Format("UPDATE DAT_JH_DOC SET FLAG = 'Y',SHR = '{1}',SHRQ = SYSDATE WHERE SEQNO = '{0}' AND FLAG = 'N'", bill, UserAction.UserID)) > 0)
{
Alert.Show("借货单审核成功！");
OperLog("商品接货", "审核单据【" + bill + "】");
}
else
{
Alert.Show("单据审核失败,请刷新后重试！", "提示信息", MessageBoxIcon.Question);
return;
}
billOpen(bill);
}
/// <summary>
/// 获取商品数据信息
/// </summary>
/// <param name="pageNum">第几页</param>
/// <param name="pageSize">每页显示天数</param>
/// <param name="nvc">查询条件</param>
/// <param name="total">总的条目数</param>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable GetGoodsList(int pageNum, int pageSize, NameValueCollection nvc, ref int total, ref string errMsg)
{
string strSearch = "";
if (tgbGDSEQ.Text.Trim().Length > 0)
{
strSearch += string.Format(" and (b.GDSEQ LIKE UPPER('%{0}%') OR b.GDNAME LIKE UPPER('%{0}%') OR b.ZJM LIKE UPPER('%{0}%') OR B.HISCODE LIKE UPPER('%{0}%') OR B.HISNAME LIKE UPPER('%{0}%'))", tgbGDSEQ.Text.Trim());
}
if (dpkout1.SelectedDate != null)
{
strSearch += string.Format(" AND a.SHRQ>=TO_DATE('{0}','YYYY-MM-DD')", dpkout1.Text);
}
if (dpkout2.SelectedDate != null)
{
strSearch += string.Format(" AND a.SHRQ<TO_DATE('{0}','YYYY-MM-DD')+1", dpkout2.Text);
}
if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue))
{
strSearch += " and B.ISGZ = '" + ddlISGZ.SelectedValue + "'";
}
strSearch += string.Format(" AND a.deptid in( select code FROM SYS_DEPT where F_CHK_DATARANGE(CODE, '{0}') = 'Y') ORDER BY A.SHRQ DESC", UserAction.UserID);
string strGoods = @"SELECT A.*,F_GETHISINFO(B.GDSEQ,'GDNAME') GDNAME,F_GETHISINFO(B.GDSEQ,'GDSPEC') GDSPEC,f_getunitname(A.unit) UNITNAME,f_getproducername(B.PRODUCER) PRODUCERNAME,f_getbillflag(A.FLAG) FLAGNAME,f_get_billtypename(A.BILLTYPE) BILLTYPENAME,F_GETUSERNAME(A.SHR) SHRNAME,F_GETDEPTNAME(DEPTOUT) DEPTOUTNAME,F_GETDEPTNAME(A.DEPTID) DEPTINNAME,decode(B.ISGZ,'Y','是','否') ISGZ
FROM VIEW_GOODSGZ A,DOC_GOODS B WHERE A.GDSEQ = B.GDSEQ ";
StringBuilder strSql = new StringBuilder(strGoods);
if (!string.IsNullOrWhiteSpace(strSearch))
{
strSql.Append(strSearch);
}
return(GetDataTable(pageNum, pageSize, strSql, ref total));
}
private string GetSearchSql1()
{
string strSql = @"SELECT DEPTID,DEPTNAME,GDSEQ,GDNAME,GDSPEC,UNIT,ROUND(HSJJ,4)HSJJ,SUM(SL)SL,SUM(JE)JE FROM(
SELECT M.DEPTID,M.DEPTNAME,M.GDSEQ,M.GDNAME,M.GDSPEC,M.UNIT,M.HSJJ,M.SL,M.JE,DECODE(M.ISJF,'Y','是','N','否')ISJF FROM(
SELECT DGJ.BILLNO,
SD.CODE DEPTID,
SD.NAME DEPTNAME,
DGJ.GDSEQ,
DG.GDNAME,
DG.GDSPEC,DGJ.HSJJ,
f_getunitname(DG.UNIT)UNIT,
DECODE(DGJ.KCADD,
-1,
DECODE(DGJ.BILLTYPE,
'XSD',
DGJ.SL*DGJ.KCADD,
'XSG',DGJ.SL*DGJ.KCADD),1,DECODE(DGJ.BILLTYPE,'XST', DGJ.SL * DGJ.KCADD*(-1))
) SL,
NVL(DG.STR6,'N') ISJF,
DECODE(DGJ.KCADD,
-1,
DECODE(DGJ.BILLTYPE,
'XSD',
DGJ.HSJE * DGJ.KCADD,
'XSG',DGJ.HSJE*DGJ.KCADD),1,DECODE(DGJ.BILLTYPE,'XST',DGJ.HSJE*(-1))) JE
FROM DAT_GOODSJXC DGJ, DOC_GOODS DG, DOC_GOODSCFG DGC, SYS_DEPT SD
WHERE DGJ.DEPTID = SD.CODE
AND DGJ.GDSEQ = DG.GDSEQ
AND DGJ.GDSEQ = DGC.GDSEQ
AND DGJ.DEPTID = DGC.DEPTID
AND DG.FLAG = 'Y'
AND DGC.ISCFG = 'Y'
AND SD.FLAG = 'Y'
AND DGJ.BILLTYPE IN ('XSD', 'XST','XSG')
AND SD.TYPE <> '1'
AND DGJ.RQSJ >= TO_DATE('{0}', 'YYYY-MM-DD')
AND DGJ.RQSJ <= TO_DATE('{1}', 'YYYY-MM-DD')+1
)M WHERE M.SL IS NOT NULL AND M.ISJF LIKE NVL('{2}','%')
";
string strWhere = " ";
strSql = string.Format(strSql, dpkBegRQ.Text, dpkEndRQ.Text, ddlISJF.SelectedValue);
if (!PubFunc.StrIsEmpty(hfdDEPTID.Text))
{
strWhere += " AND M.DEPTID='" + hfdDEPTID.Text + "' ";
}
if (!PubFunc.StrIsEmpty(trbSearch.Text))
{
strWhere += " and (M.GDSEQ like '%" + trbSearch.Text + "%' or M.gdname like '%" + trbSearch.Text + "%')";
}
if (strWhere != " ")
{
strSql = strSql + strWhere + " )GROUP BY DEPTID,DEPTNAME,GDSEQ,GDNAME,GDSPEC,UNIT,HSJJ ";
}
return(strSql);
}
protected override void billNew()
{
string strDept = docDEPTID.SelectedValue;
string strDeptOut = docDEPTOUT.SelectedValue;
PubFunc.FormDataClear(FormDoc);
if (PubFunc.StrIsEmpty(strDept))
{
if (docDEPTID.Items.Count > 2)
{
strDept = docDEPTID.Items[1].Value;
}
}
if (PubFunc.StrIsEmpty(strDeptOut))
{
if (docDEPTOUT.Items.Count > 2)
{
strDeptOut = docDEPTOUT.Items[1].Value;
}
}
docFLAG.SelectedValue = "N";
docSLR.SelectedValue = UserAction.UserID;
docLRY.SelectedValue = UserAction.UserID;
docLRRQ.SelectedDate = DateTime.Now;
docXSRQ.SelectedDate = DateTime.Now;
docDEPTID.SelectedValue = strDept;
docDEPTOUT.SelectedValue = strDeptOut;
billLockDoc(false);
GridGoods.SummaryData = null;
PageContext.RegisterStartupScript(GridGoods.GetRejectChangesReference());
}
private void DataAdd()
{
string ls_parent = TreeDic.SelectedNodeID;
int li_maxLen = CodeLen("99");
if (DbHelperOra.Exists("SELECT 1 FROM SYS_DEPT WHERE CODE = '" + ls_parent + "' AND ISLAST = 'Y'"))
{
Alert.Show("【" + ls_parent + "】已是末级，不能增加子级！", "提示信息", MessageBoxIcon.Warning);
return;
}
InitDdl();
InitFrom("");
if (PubFunc.StrIsEmpty(ls_parent))
{
ddlSJCODE.SelectedValue = "0";
ddlISORDER.SelectedValue = "Y";
tbxCLASS.Text = "1";
ddlType.SelectedValue = "1";
}
else
{
tbxCLASS.Text = DbHelperOra.GetSingle("select nvl(CLASS,0) + 1 from " + strTable + " where code='" + ls_parent + "'").ToString();
ddlSJCODE.SelectedValue = ls_parent;
ddlType.SelectedValue = "3";
ddlISORDER.SelectedValue = "N";
}
//ddlType.Enabled = (tbxCLASS.Text == "1");
cbxFlag.Checked = true;
tbxCode.Enabled = true;
tbxCode.Text = CodeGet(ddlSJCODE.SelectedValue, tbxCLASS.Text);
cbxIsLast.Checked = (li_maxLen == tbxCode.Text.Length);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (docSEQNO.Text.Length < 1)
{
Alert.Show("申领单:" + docSEQNO.Text + "没有保存，不能提交!", "消息提示", MessageBoxIcon.Warning);
return;
}
string flg = (string)DbHelperOra.GetSingle(string.Format("SELECT NVL(FLAG,'M') FROM DAT_SL_DOC WHERE SEQNO='{0}'", docSEQNO.Text));
if (!PubFunc.StrIsEmpty(flg) && (",M").IndexOf(flg) < 0)
{
Alert.Show("申领单:" + docSEQNO.Text + "不是新增单据，不能提交!", "消息提示", MessageBoxIcon.Warning);
return;
}
//增加待办事宜
List <CommandInfo> cmdList = new List <CommandInfo>();
cmdList.Add(new CommandInfo(string.Format("update DAT_SL_DOC set flag='N' where seqno='{0}' and flag='M'", docBILLNO.Text), null));
if (!DbHelperOra.Exists("select * from DAT_DO_LIST where PARA='" + docBILLNO.Text.Trim() + "'"))
{
cmdList.Add(new CommandInfo(" UPDATE DAT_DO_LIST SET DOUSER='******',DORQ=SYSDATE,FLAG='Y' WHERE DOTYPE='" + docDEPTID.SelectedValue + "' AND PARA='" + docBILLNO.Text + "'", null));
cmdList.Add(Doc.GETDOADD("DO_4", docDEPTID.SelectedValue, docLRY.SelectedValue, docBILLNO.Text));
}
if (DbHelperOra.ExecuteSqlTran(cmdList))
{
Alert.Show("提交成功");
billOpen(docSEQNO.Text);
}
}
protected override void billCancel()
{
if (PubFunc.StrIsEmpty(docSEQNO.Text))
{
Alert.Show("请选择需要操作的单据！", "操作提示", MessageBoxIcon.Warning);
return;
}
if (docFLAG.SelectedValue == "N")
{
if (DbHelperOra.ExecuteSql(string.Format("UPDATE DAT_CK_DOC SET FLAG = 'R' WHERE SEQNO = '{0}' AND FLAG = 'N'", docSEQNO.Text)) > 0)
{
Alert.Show("单据【" + docSEQNO.Text + "】驳回成功！");
billOpen(docSEQNO.Text);
return;
}
else
{
Alert.Show("请刷新界面后重试！", "操作提示", MessageBoxIcon.Warning);
}
}
else
{
Alert.Show("单据状态不正确，请检查！", "操作提示", MessageBoxIcon.Warning);
return;
}
}
private DataTable DataSerach()
{
string strSql = @"SELECT A.SEQNO,A.BILLNO,B.NAME FLAGNAME,A.FLAG,F_GETDEPTNAME(A.DEPTID) DEPTID,A.XSRQ,F_GETDEPTNAME(A.DEPTOUT) DEPTOUT,A.SUBSUM,
A.SUBNUM,F_GETUSERNAME(A.LRY) LRY,A.LRRQ,F_GETUSERNAME(A.SHR) SHR,A.SHRQ,A.MEMO
from DAT_CK_DOC A, SYS_CODEVALUE B
WHERE A.FLAG = B.CODE AND B.TYPE ='DIC_BILLSTATUS' AND BILLTYPE='CKD' AND XSTYPE='1' AND EXISTS(SELECT 1 FROM DAT_JH_COM B WHERE B.STR1 = A.SEQNO)";
string strSearch = "";
if (tgbBILLNO.Text.Length > 0)
{
strSearch += string.Format(" AND UPPER(TRIM(A.BILLNO)) LIKE '%{0}%'", tgbBILLNO.Text.Trim().ToUpper());
}
if (lstDEPTID.SelectedItem != null && lstDEPTID.SelectedItem.Value.Length > 0)
{
strSearch += string.Format(" AND A.DEPTID='{0}'", lstDEPTID.SelectedItem.Value);
}
if (!PubFunc.StrIsEmpty(lstFLAG.SelectedValue))
{
strSearch += string.Format(" AND A.FLAG='{0}'", lstFLAG.SelectedValue);
}
strSearch += string.Format(" AND deptid in( select code FROM SYS_DEPT where F_CHK_DATARANGE(CODE, '{0}') = 'Y' )", UserAction.UserID);
strSearch += string.Format(" AND A.XSRQ>=TO_DATE('{0}','YYYY-MM-DD')", lstLRRQ1.Text);
strSearch += string.Format(" AND A.XSRQ <TO_DATE('{0}','YYYY-MM-DD') + 1", lstLRRQ2.Text);
if (!string.IsNullOrWhiteSpace(strSearch))
{
strSql += strSearch;
}
strSql += " ORDER BY A.BILLNO DESC";
return(DbHelperOra.Query(strSql).Tables[0]);
}
private string GetSearchSql()
{
string strSql = string.Format(@"SELECT A.DEPTOUT,B.GDSEQ,B.GDNAME,B.GDSPEC,A.SL,F_GETBZHL(B.GDSEQ) BZHL,ROUND(A.SL/F_GETBZHL(B.GDSEQ),2) BZSL,
B.HSJJ*F_GETBZHL(B.GDSEQ) HSJJ,A.SL*B.HSJJ JE,B.PRODUCER,B.PIZNO
,f_getunitname(b.unit) unitname，F_GETSELLUNITNAME(B.GDSEQ) SELLUNITNAME,F_GETPRODUCERNAME(B.PRODUCER) PRODUCERNAME
,F_GETDEPTNAME(A.DEPTOUT) DEPTOUTNAME,F_GETHWID(A.DEPTOUT,B.GDSEQ) HWID
FROM (SELECT A.DEPTOUT,B.GDSEQ,SUM(DECODE(A.BILLTYPE,'DSC',FPSL,'LCD',XSSL)) SL
FROM DAT_CK_DOC A,DAT_CK_COM B,SYS_DEPT C
WHERE A.SEQNO = B.SEQNO AND A.FLAG = 'S' AND B.XSSL > 0 AND A.DEPTID = C.CODE AND A.STR4>=TO_DATE('{0}','YYYY-MM-DD') AND A.STR4 <TO_DATE('{1}','YYYY-MM-DD') + 1", lstLRRQ1.Text, lstLRRQ2.Text);
if (!PubFunc.StrIsEmpty(ddlDEPTOUT.SelectedValue))
{
strSql += " AND A.DEPTOUT = '" + ddlDEPTOUT.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(ddlDEPTID.SelectedValue))
{
strSql += " AND A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(ddlXl.SelectedValue))
{
strSql += " AND C.STR3 = '" + ddlXl.SelectedValue + "'";
}
if (tbgBC.Text.Trim().Length > 0)
{
strSql += " AND A.NUM1 = " + tbgBC.Text.Trim() + "";
}
strSql += " GROUP BY A.DEPTOUT,B.GDSEQ) A,DOC_GOODS B WHERE A.GDSEQ = B.GDSEQ";
if (tbxGOODS.Text.Trim().Length > 0)
{
strSql += string.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%' OR B.ZJM LIKE '%{0}%' OR B.BARCODE LIKE '%{0}%' OR B.HISCODE LIKE '%{0}%' OR B.HISNAME LIKE '%{0}%' OR B.STR4 LIKE '%{0}%')", tbxGOODS.Text.Trim().ToUpper());
}
strSql += " ORDER BY DEPTOUT,A.GDSEQ";
return(strSql);
}
protected void BtnSearch_Click(object sender, EventArgs e)
{
if (PubFunc.StrIsEmpty(dpkTIME1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkTIME2.SelectedDate.ToString()))
{
Alert.Show("输入日期不正确,请检查！");
return;
}
if (dpkTIME1.SelectedDate > dpkTIME2.SelectedDate)
{
Alert.Show("开始日期不能大于结束日期！");
return;
}
string strSql = @"select F_GETPARA('SUPPER') SUPNAME,sum(decode(exp_form,'办公耗材',b.hsje,0)) bghc,sum(decode(exp_form,'办公用品',b.hsje,0)) bgyp,sum(decode(exp_form,'被服材料',b.hsje,0)) bfcl
,sum(decode(exp_form,'低值易耗',b.hsje,0)) dzyh,sum(decode(exp_form,'电工材料',b.hsje,0)) dgcl,sum(decode(exp_form,'非收费其他',b.hsje,0)) fsfqt
,sum(decode(exp_form,'木工材料',b.hsje,0)) mgcl,sum(decode(exp_form,'设备材料',b.hsje,0)) sbcl,sum(decode(exp_form,'试剂',b.hsje,0)) sj
,sum(decode(exp_form,'收费其他',b.hsje,0)) sfqt,sum(decode(exp_form,'维修材料',b.hsje,0)) wxcl,sum(decode(exp_form,'卫生材料',b.hsje,0)) wscl
,sum(decode(exp_form,'卫生用品',b.hsje,0)) wsyp,sum(decode(exp_form,'印刷品',b.hsje,0)) ysp,sum(decode(exp_form,'植入性材料',b.hsje,0)) zrxcl
,sum(decode(nvl(exp_form,'#'),'#',b.hsje,0)) qt
,sum(decode(nvl(exp_form,'#'),'办公用品',b.hsje,'低值易耗',b.hsje,'非收费其他',b.hsje,'收费其他',b.hsje,'植入性材料',b.hsje,'试剂',b.hsje,0)) hj
--数量
,sum(decode(exp_form,'办公用品',b.xssl,0)) bgypsl,sum(decode(exp_form,'低值易耗',b.xssl,0)) dzyhsl,sum(decode(exp_form,'非收费其他',b.xssl,0)) fsfqtsl
,sum(decode(exp_form,'试剂',b.xssl,0)) sjsl,sum(decode(exp_form,'收费其他',b.xssl,0)) sfqtsl,sum(decode(exp_form,'植入性材料',b.xssl,0)) zrxclsl
,sum(decode(nvl(exp_form,'#'),'办公用品',b.xssl,'低值易耗',b.xssl,'非收费其他',b.xssl,'收费其他',b.xssl,'植入性材料',b.xssl,'试剂',b.xssl,0)) hjsl
from dat_xs_doc a,dat_xs_com b,doc_goods c,(select distinct exp_code,exp_form from temp_goods_his) d
where a.seqno = b.seqno and a.flag in('Y','G') and a.SHRQ between to_date('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd')
and b.gdseq = c.gdseq and c.hiscode = d.exp_code(+)";
Gridlist.DataSource = DbHelperOra.QueryForTable(string.Format(strSql, dpkTIME1.Text, dpkTIME2.Text));
Gridlist.DataBind();
HIDtime1.Text = dpkTIME1.Text;
HIDtime2.Text = dpkTIME2.Text;
}
/// <summary>
/// 检验table是否符合规范
/// </summary>
public static string CheckTable(DataTable Goods, string[] Columns)
{
for (int i = 0; i < Goods.Rows.Count; i++)
{
foreach (string Col in Columns)
{
try
{
if (PubFunc.StrIsEmpty(Goods.Rows[i][Col].ToString()))
{
return("商品[" + Goods.Rows[i]["GDSEQ"].ToString() + "]信息维护不完整,请检查!");
}
if (Col == "GDSEQ")
{
if (!CheckGdseq(Goods.Rows[i]["GDSEQ"].ToString()))
{
return("商品[" + Goods.Rows[i]["GDSEQ"].ToString() + "]信息维护不正确,请检查!");
}
}
}
catch
{
return("商品[" + Goods.Rows[i]["GDSEQ"].ToString() + "]信息维护不完整,请检查!");
}
}
}
return("");
}
protected void btnScan_Click(object sender, EventArgs e)
{
if ((",M,R,N,A").IndexOf(docFLAG.SelectedValue) < 0)
{
zsmScan.Enabled = false;
zsmDelete.Enabled = false;
}
else
{
zsmScan.Enabled = true;
zsmDelete.Enabled = true;
}
if (PubFunc.StrIsEmpty(docSEQNO.Text))
{
Alert.Show("请保存单据后进行扫描追溯码操作！", "消息提示", MessageBoxIcon.Warning);
return;
}
if (!DbHelperOra.Exists(string.Format("SELECT 1 FROM DAT_DB_COM A,DOC_GOODS B WHERE A.GDSEQ = B.GDSEQ AND B.ISGZ = 'Y' AND SEQNO = '{0}'", docSEQNO.Text)))
{
Alert.Show("此单据中没有已经保存的高值商品,请检查！", "消息提示", MessageBoxIcon.Warning);
return;
}
WindowScan.Hidden = false;
ScanSearch("SHOW");
}
private string GetSearchSql()
{
string strSql = @"select a.lockbillno,a.lockrowno,b.gdseq, b.gdspec,b.gdname,f_getunitname(b.unit) unitname,sum(abs(a.lockkcsl)) lockkcsl,f_getdeptname(a.deptid) deptidname,
b.hsjj,sum(b.hsjj*abs(a.lockkcsl)) hsje,b.zpbh,a.phid,b.bar3,f_getproducername(b.producer) PRODUCERNAME,b.pizno
from dat_stocklock a,doc_goods b
where a.gdseq = b.gdseq";
string strWhere = " ";
if (!PubFunc.StrIsEmpty(ddlDEPTID.SelectedValue))
{
strWhere += " and A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(tbxGOODS.Text))
{
strWhere += " and (b.gdseq like '%" + tbxGOODS.Text.ToUpper() + "%' or b.zjm like '%" + tbxGOODS.Text.ToUpper() + "%' or b.gdname like '%" + tbxGOODS.Text.ToUpper() + "%')";
}
if (!PubFunc.StrIsEmpty(tbxPHID.Text))
{
strWhere += " and A.PHID = '" + tbxPHID.Text + "'";
}
if (!PubFunc.StrIsEmpty(tbxBILLNO.Text))
{
strWhere += " and A.lockbillno like '%" + tbxBILLNO.Text + "%'";
}
if (strWhere != " ")
{
strSql = strSql + strWhere;
}
strSql += string.Format(" group by a.lockbillno,a.lockrowno,b.gdseq,b.gdspec,b.gdname,b.unit,a.deptid,b.hsjj,b.zpbh,a.phid,b.bar3,b.producer,b.pizno");
strSql += string.Format(" ORDER BY {0} {1}", GridGoods.SortField, GridGoods.SortDirection);
return(strSql);
}
private void DataSearch()
{
if (PubFunc.StrIsEmpty(dpkKSRQ.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkJSRQ.SelectedDate.ToString()))
{
Alert.Show("输入日期不正确,请检查！");
return;
}
if (dpkKSRQ.SelectedDate > dpkJSRQ.SelectedDate)
{
Alert.Show("开始日期不能大于结束日期！");
return;
}
string strMonitor = " ";
strMonitor += " AND execrq BETWEEN TO_DATE('" + dpkKSRQ.Text + "','YYYY/MM/DD') and TO_DATE('" + dpkJSRQ.Text + "','YYYY/MM/DD') + 1";
if (chkCHULI.Checked)
{
strMonitor += " AND FLAG = 'N' ";
}
strSql = strSql + strMonitor;
int total = 0;
DataTable dtData = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, strSql, ref total);
GridList.RecordCount = total;
GridList.DataSource = dtData;
GridList.DataBind();
}
protected void ddlDept_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlDept.SelectedValue != null && !PubFunc.StrIsEmpty(ddlDept.SelectedValue))
{
//if (ddlDept.SelectedValue.Equals("01"))
//{
// ExportBtn.Enabled = true;
//}
//else
//{
// ExportBtn.Enabled = false;
//}
string type = DbHelperOra.GetSingle(string.Format("SELECT DECODE(TYPE,'1','Y','2','Y','N') KF_FLAG FROM SYS_DEPT WHERE CODE='{0}'", ddlDept.SelectedValue)).ToString();
if (type == "Y")
{
nbxDSNUM.Text = "";
NubNUM1.Text = "";
nbxDSNUM.Enabled = false;
NubNUM1.Enabled = false;
}
else
{
//nbxDSNUM.Enabled = true;
//NubNUM1.Enabled = true;
}
dataSearch();
dataSearch("right");
}
}
private string GetSearchSql2()
{
string strSql = @"SELECT B.GDSEQ,B.GDNAME,B.GDSPEC,F_GETUNITNAME(B.UNIT) UNIT,B.HSJJ,SUM(B.XSSL) SL,SUM(B.HSJE) JE,S.SUPNAME,G.HISCODE,DECODE(G.ISGZ,'N','否','Y','是') GZ
FROM DAT_XS_DOC A, DAT_XS_COM B, DOC_GOODS G,DOC_SUPPLIER S
WHERE A.SEQNO = B.SEQNO AND B.GDSEQ = G.GDSEQ AND G.SUPPLIER=S.SUPID AND A.FLAG='Y'";
string strWhere = " ";
if (!PubFunc.StrIsEmpty(ddlGYS2.SelectedValue))
{
strWhere += " AND G.SUPPLIER = '" + ddlGYS2.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(ddlGZ1.SelectedValue))
{
strWhere += " AND G.ISGZ = '" + ddlGZ1.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(trbSearch.Text))
{
strWhere += " and (G.GDSEQ like '%" + trbSearch.Text + "%' or g.zjm like '%" + trbSearch.Text + "%' or g.gdname like '%" + trbSearch.Text + "%')";
}
strWhere += string.Format(" and A.XSRQ>=TO_DATE('{0}','YYYY-MM-DD') and A.XSRQ < TO_DATE('{1}','YYYY-MM-DD')+1 ", dpkBegRQ.Text, dpkEndRQ.Text);
if (strWhere != " ")
{
strSql = strSql + strWhere;
}
strSql += string.Format(" GROUP BY B.GDSEQ, B.GDNAME, B.GDSPEC, B.UNIT, B.HSJJ, G.HISCODE,S.SUPNAME,G.ISGZ ORDER BY {0} {1}", GridCom.SortField, GridCom.SortDirection);
return(strSql);
}
protected void btnMH_Click(object sender, EventArgs e)
{
if (PubFunc.StrIsEmpty(tgbExlBill.Text))
{
Alert.Show("请首先输入完整导入单号!", "消息提示", MessageBoxIcon.Warning);
return;
}
if (!DbHelperOra.Exists("SELECT 1 FROM DAT_UPLOAD_GOODS WHERE SEQNO = '" + tgbExlBill.Text + "' AND FLAG = 'N'"))
{
Alert.Show("不存在需匹配信息！", "消息提示", MessageBoxIcon.Warning);
return;
}
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("V_BILL", OracleDbType.Varchar2),
new OracleParameter("V_TYPE", OracleDbType.Varchar2),
};
parameters[0].Value = tgbExlBill.Text;
parameters[1].Value = '3';
DbHelperOra.RunProcedure("P_ExlGOODS", parameters);
string allnum = DbHelperOra.GetSingle("SELECT COUNT(1) FROM DAT_UPLOAD_GOODS WHERE SEQNO = '" + tgbExlBill.Text + "' AND FLAG ='Y' AND PPMODE = '2'").ToString();
Alert.Show("糊匹配成功!\r\n共匹配[" + allnum + "]条数据！", "消息提示", MessageBoxIcon.Information);
SearchOne();
//TgbBill.Text = tgbExlBill.Text;
btnGridOut_Click(null, null);
}
protected void btnGridOut_Click(object sender, EventArgs e)
{
int total = 0;
string Sql = @"SELECT A.*,B.GDNAME,B.GDSPEC,B.PIZNO PZWH,f_getproducername(B.PRODUCER) PRODUCERNAME,D.NAME UNITNAME,C.NAME HOSNAME,B.BAR3 EAS_CODE,
DECODE(B.FLAG,'Y','正常','未下传') FLAGNAME,DECODE(A.PPMODE,'1','精确匹配','2','模糊匹配','3','手工匹配','历史匹配') PPMODENAME
FROM DAT_UPLOAD_GOODS A,DOC_GOODS B,DOC_CUSTOMER C,DOC_GOODSUNIT D
WHERE A.GDSEQ = B.GDSEQ(+) AND A.CUSTID = C.CODE(+) AND B.UNIT = D.CODE(+) AND A.FLAG = 'Y'";
string Stearch = "";
if (!PubFunc.StrIsEmpty(ddlHisp.SelectedValue))
{
Stearch += string.Format(" AND A.CUSTID = '{0}'", ddlHisp.SelectedValue);
}
if (!PubFunc.StrIsEmpty(ddlMode.SelectedValue))
{
Stearch += string.Format(" AND A.PPMODE = '{0}'", ddlMode.SelectedValue);
}
if (!PubFunc.StrIsEmpty(TgbBill.Text))
{
Stearch += string.Format(" AND A.SEQNO = '{0}'", TgbBill.Text);
}
if (!PubFunc.StrIsEmpty(tgbGoods.Text))
{
Stearch += string.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%' OR B.BAR3 LIKE '%{0}%')", tgbGoods.Text);
}
Stearch += " ORDER BY A.UPTTIME DESC";
DataTable dtData = PubFunc.DbGetPage(GridOut.PageIndex, GridOut.PageSize, Sql + Stearch, ref total);
GridOut.RecordCount = total;
GridOut.DataSource = dtData;
GridOut.DataBind();
}
protected override void billSearch()
{
if (lstLRRQ1.SelectedDate == null || lstLRRQ2.SelectedDate == null)
{
Alert.Show("请输入条件【使用日期】！");
return;
}
else if (lstLRRQ1.SelectedDate > lstLRRQ2.SelectedDate)
{
Alert.Show("【开始日期】大于【结束日期】，请重新输入！", MessageBoxIcon.Warning);
return;
}
string strSql = @"SELECT A.SEQNO,A.BILLNO,decode(A.FLAG,'N','新单','A','已提交','W','已出库','R','已驳回','Y','已收货','未定义') FLAGNAME,A.FLAG,F_GETDEPTNAME(A.DEPTID) DEPTID,A.XSRQ,F_GETDEPTNAME(A.DEPTOUT) DEPTOUT,A.SUBSUM,
A.SUBNUM,F_GETUSERNAME(A.LRY) LRY,A.LRRQ,A.SHRQ,A.MEMO,F_GETUSERNAME(A.SHR) SHR
from DAT_DB_DOC A
WHERE BILLTYPE='DBD' AND XSTYPE='1' ";
string strSearch = "";
if (tgbBILLNO.Text.Length > 0)
{
strSearch += string.Format(" AND UPPER(TRIM(A.BILLNO)) LIKE '%{0}%'", tgbBILLNO.Text.Trim().ToUpper());
}
if (lstDEPTID.SelectedItem != null && lstDEPTID.SelectedItem.Value.Length > 0)
{
strSearch += string.Format(" AND A.DEPTID='{0}'", lstDEPTID.SelectedItem.Value);
}
if (ddlDEPTOUT.SelectedValue.Length > 0)
{
strSearch += string.Format(" AND A.DEPTOUT='{0}'", ddlDEPTOUT.SelectedItem.Value);
}
if (!PubFunc.StrIsEmpty(lstFLAG.SelectedValue))
{
strSearch += string.Format(" AND A.FLAG='{0}'", lstFLAG.SelectedValue);
}
strSearch += string.Format(" AND deptid in( select code FROM SYS_DEPT where F_CHK_DATARANGE(CODE, '{0}') = 'Y' )", UserAction.UserID);
strSearch += string.Format(" AND A.XSRQ>=TO_DATE('{0}','YYYY-MM-DD')", lstLRRQ1.Text);
strSearch += string.Format(" AND A.XSRQ <TO_DATE('{0}','YYYY-MM-DD') + 1", lstLRRQ2.Text);
if (!string.IsNullOrWhiteSpace(strSearch))
{
strSql += strSearch;
}
if (hdfOper.Text == "audit")
{
strSql += " AND A.FLAG IN('A','W') ";
}
else
{
strSql += " AND A.FLAG IN('N','A') ";
}
strSql += " ORDER BY A.BILLNO DESC";
highlightRows.Text = "";
highlightRowYellow.Text = "";
highlightRowRed.Text = "";
GridList.DataSource = DbHelperOra.Query(strSql).Tables[0];
GridList.DataBind();
}
private string GetQuerySql()
{
string strSql = @"SELECT A.GDSEQ,
TA.BAR3,
TA.GDNAME,
F_GETUNITNAME(TA.UNIT) UNITNAME,
A.KCSL KFKC,
B.KSKCSL KEKCSL,
C.XSSL WJSSL,
F.SYSL SYSL,
E.EASSL EASSL,
NVL(A.KCSL, 0) + NVL(B.KSKCSL, 0) + NVL(C.XSSL, 0) - NVL(E.EASSL, 0) CY,
decode(TA.ISGZ,'Y','是','否') ISGZ
FROM (SELECT GDSEQ, SUM(KCSL) KCSL
FROM DAT_GOODSSTOCK
WHERE DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE = '1')
GROUP BY GDSEQ) A,
(SELECT GDSEQ, SUM(KCSL) KSKCSL
FROM DAT_GOODSSTOCK
WHERE DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE = '3')
GROUP BY GDSEQ) B,
(SELECT GDSEQ, SUM(XSSL) XSSL
FROM DAT_XS_DOC A, DAT_XS_COM B
WHERE A.SEQNO = B.SEQNO
AND FLAG = 'Y'
AND A.ISSEND = 'N'
GROUP BY B.GDSEQ) C,
(SELECT GDSEQ, SUM(SL) EASSL
FROM EAS_STOCK
WHERE TO_CHAR(RQ, 'YYYY-MM-DD') = '" + lstSJ.Text + @"'
GROUP BY GDSEQ) E,
(SELECT GDSEQ, SUM(SYSL)SYSL
FROM DAT_SY_DOC A, DAT_SY_COM B
WHERE A.SEQNO = B.SEQNO
AND FLAG = 'Y'
GROUP BY B.GDSEQ) F,
DOC_GOODS TA
WHERE A.GDSEQ = B.GDSEQ(+)
AND A.GDSEQ = C.GDSEQ(+)
AND A.GDSEQ = TA.GDSEQ
AND TA.GDSEQ = E.GDSEQ(+)
AND A.GDSEQ = F.GDSEQ(+) ";
string strSearch = "";
if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue))
{
strSearch += " and TA.ISGZ = '" + ddlISGZ.SelectedValue + "'";
}
if (ckbOnlyDifference.Checked)
{
strSearch += " AND (NVL(A.KCSL, 0) + NVL(B.KSKCSL, 0) + NVL(C.XSSL, 0) + NVL(-F.SYSL, 0) <> NVL(E.EASSL, 0)) ";
}
if (tbxGDSEQ.Text.Trim().Length > 0)
{
strSearch += " AND (TA.GDSEQ LIKE '%" + tbxGDSEQ.Text.Trim() + "%' or TA.BAR3 LIKE '%" + tbxGDSEQ.Text.Trim() + "%' or TA.GDNAME LIKE '%" + tbxGDSEQ.Text.Trim() + "%' or TA.ZJM LIKE '%" + tbxGDSEQ.Text.Trim() + "%')";
}
return(strSql + strSearch);
}
private void DataSearch()
{
if (PubFunc.StrIsEmpty(dpkDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkDATE2.SelectedDate.ToString()))
{
Alert.Show("输入日期不正确,请检查！");
return;
}
if (dpkDATE1.SelectedDate > dpkDATE2.SelectedDate)
{
Alert.Show("开始日期不能大于结束日期！");
return;
}
int total = 0;
DataTable dtData = PubFunc.DbGetPage(GridGoods.PageIndex, GridGoods.PageSize, GetSearchSql(), ref total);
DataTable dtAll = DbHelperOra.QueryForTable(GetSearchSql());
GridGoods.RecordCount = total;
GridGoods.DataSource = dtData;
GridGoods.DataBind();
//计算合计数量
if (dtData != null && dtData.Rows.Count > 0)
{
decimal slTotal = 0, lsjeTotal = 0, hsjeTotal = 0, bhsjeTotal = 0, slAll = 0, lsjeAll = 0, hsjeAll = 0, bhsjeAll = 0;
foreach (DataRow row in dtData.Rows)
{
slTotal += Convert.ToDecimal(row["SL"] ?? "0");
lsjeTotal += Convert.ToDecimal(row["LSJE"] ?? "0");
hsjeTotal += Convert.ToDecimal(row["HSJE"] ?? "0");
bhsjeTotal += Convert.ToDecimal(row["BHSJE"] ?? "0");
}
foreach (DataRow dr in dtAll.Rows)
{
slAll += Convert.ToDecimal(dr["SL"] ?? "0");
lsjeAll += Convert.ToDecimal(dr["LSJE"] ?? "0");
hsjeAll += Convert.ToDecimal(dr["HSJE"] ?? "0");
bhsjeAll += Convert.ToDecimal(dr["BHSJE"] ?? "0");
}
JObject summary = new JObject();
summary.Add("GDNAME", "本页合计</br>总合计");
summary.Add("SL", slTotal + "</br>" + slAll);
summary.Add("LSJE", lsjeTotal.ToString("F2") + "</br>" + lsjeTotal.ToString("F2"));
summary.Add("HSJE", hsjeTotal.ToString("F2") + "</br>" + hsjeAll.ToString("F2"));
summary.Add("BHSJE", bhsjeTotal.ToString("F2") + "</br>" + bhsjeAll.ToString("F2"));
GridGoods.SummaryData = summary;
}
else
{
JObject summary = new JObject();
summary.Add("GDNAME", "本页合计");
summary.Add("SL", 0);
summary.Add("LSJE", 0);
summary.Add("HSJE", 0);
summary.Add("BHSJE", 0);
GridGoods.SummaryData = summary;
}
}
protected void btnAutoOrder_Click(object sender, EventArgs e)
{
string lsRET;
DateTime dtrq1 = Convert.ToDateTime(lstLRRQ1.Text);
DateTime dtrq2 = Convert.ToDateTime(lstLRRQ2.Text);
string dept = docDEPTID.SelectedValue;
if (PubFunc.StrIsEmpty(dept))
{
Alert.Show("请选择[" + docDEPTID.Label + "]！");
return;
}
if (rblTYPE.SelectedValue == "XS" || rblTYPE.SelectedValue == "DS")
{
lsRET = PubFunc.isDateTwoValid(dtrq1, dtrq2);
if (lsRET.Length > 1)
{
Alert.Show(lsRET);
return;
}
}
OracleParameter[] parameters =
{
new OracleParameter("VI_DEPT", OracleDbType.Varchar2, 20),
new OracleParameter("VI_TYPE", OracleDbType.Varchar2, 20),
new OracleParameter("VI_BEG", OracleDbType.Varchar2, 10),
new OracleParameter("VI_END", OracleDbType.Varchar2, 10),
new OracleParameter("VI_USER", OracleDbType.Varchar2, 20),
new OracleParameter("VO_BILLNUM", OracleDbType.Double)
};
parameters[0].Value = dept;
parameters[1].Value = rblTYPE.SelectedValue;
parameters[2].Value = lstLRRQ1.Text;
parameters[3].Value = lstLRRQ2.Text;
parameters[4].Value = UserAction.UserID;
parameters[0].Direction = ParameterDirection.Input;
parameters[1].Direction = ParameterDirection.Input;
parameters[2].Direction = ParameterDirection.Input;
parameters[3].Direction = ParameterDirection.Input;
parameters[4].Direction = ParameterDirection.Input;
parameters[5].Direction = ParameterDirection.Output;
try
{
DbHelperOra.RunProcedure("STORE.P_DD_AUTO", parameters);
Alert.Show("自动订货生成成功，单品数：【" + parameters[5].Value.ToString() + "】", "消息提示", MessageBoxIcon.Information);
OperLog("自动订货", "生成自动订货单据单据");
}
catch (Exception err)
{
throw err;
}
}
private bool CommitData()
{
if (PubFunc.StrIsEmpty(docSEQNO.Text))
{
Alert.Show("请保存单据后提交！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
string flag = DbHelperOra.GetSingle(string.Format("SELECT NVL(FLAG,'M') FROM DAT_CK_DOC WHERE BILLNO='{0}' AND BILLTYPE = 'CKD'", docSEQNO.Text.Trim())).ToString();
if (("M,R").IndexOf(flag) < 0)
{
Alert.Show("非新单，不能提交！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
string msg = "";
List <CommandInfo> cmdList = new List <CommandInfo>();
string sSQL = string.Format("SELECT SUM(ABS(A.DHSL)) DHSL,A.GDSEQ,B.GDNAME FROM DAT_CK_COM A,DOC_GOODS B ,DAT_CK_DOC C WHERE A.SEQNO = C.SEQNO AND A.GDSEQ = B.GDSEQ AND B.ISGZ = 'Y' AND C.SEQNO = '{0}' AND C.BILLTYPE = 'CKD' GROUP BY A.GDSEQ,B.GDNAME", docSEQNO.Text);
DataTable dtCom = DbHelperOra.Query(sSQL).Tables[0];
if (dtCom != null && dtCom.Rows.Count > 0)
{
foreach (DataRow dr in dtCom.Rows)
{
string checkCount = DbHelperOra.GetSingle(string.Format("SELECT COUNT(*) FROM DAT_CK_EXT WHERE BILLNO='{0}' AND GDSEQ ='{1}'", docSEQNO.Text, dr["GDSEQ"].ToString())).ToString();
if (int.Parse(dr["DHSL"].ToString()) > int.Parse(checkCount))
{
msg += "【" + dr["GDSEQ"] + "," + dr["GDNAME"] + "】,";
continue;
}
}
if (!string.IsNullOrWhiteSpace(msg))
{
Alert.Show("高值商品中 " + msg + "存在追溯码未扫描", "消息提示", MessageBoxIcon.Warning);
return(false);
}
string sSQL1 = "CALL P_EXE_CKD('" + docSEQNO.Text + "')";
cmdList.Add(new CommandInfo(sSQL1, null));
}
string sSQL2 = string.Format("UPDATE DAT_CK_DOC SET FLAG = 'N',SPR='{0}',SPRQ=sysdate WHERE BILLTYPE = 'CKD' AND SEQNO = '{1}'", UserAction.UserID, docSEQNO.Text);
cmdList.Add(new CommandInfo(sSQL2, null));
if (DbHelperOra.ExecuteSqlTran(cmdList))
{
billOpen(docSEQNO.Text);
Alert.Show("单据【" + docSEQNO.Text + "】提交成功！");
docFLAG.SelectedValue = "N";
return(true);
}
else
{
return(false);
}
}
private string Tab1GetSearchSql()
{
string strSql = @"SELECT C.GDSEQ,
C.GDNAME,
E.ZJM,
C.GDSPEC,
C.UNIT,
f_getunitname(C.UNIT) UNITNAME,
C.HSJJ,
ABS(D.SL) SL,
ABS(D.HSJE) HSJE,
E.PPID,
D.SUPID,
f_getsuppliername(D.SUPID) SUPPLIERNAME,
A.DEPTID,
f_getdeptname(A.DEPTID) DEPTNAME,
A.CUSTID,
A.STR7
from DAT_XS_DOC A, SYS_CODEVALUE B,DAT_XS_COM C,DAT_GOODSJXC D,DOC_GOODS E
WHERE A.FLAG = B.CODE
AND A.SEQNO=C.SEQNO
AND C.SEQNO = D.BILLNO
AND C.ROWNO = D.ROWNO
AND C.GDSEQ=E.GDSEQ
-- AND B.TYPE = 'DIC_BILLSTATUS'
AND A.BILLTYPE = 'XSG'
AND A.XSTYPE = '1'
AND A.FLAG='Y' ";
strSql += string.Format(@"AND A.deptid in (select code
FROM SYS_DEPT
where type <> '1'
and F_CHK_DATARANGE(CODE, '{0}') = 'Y')", UserAction.UserID);
if (!PubFunc.StrIsEmpty(txbGDSEQ.Text.Trim()))
{
strSql += " and (C.GDSEQ like '" + txbGDSEQ.Text.Trim() + "' OR C.GDSEQ like '" + txbGDSEQ.Text.Trim().ToUpper() + "' OR C.GDSEQ like '" + txbGDSEQ.Text.Trim().ToLower()
+ "' OR C.GDNAME like '" + txbGDSEQ.Text.Trim() + "' OR C.GDNAME like '" + txbGDSEQ.Text.Trim().ToUpper() + "' OR C.GDNAME like '" + txbGDSEQ.Text.Trim().ToLower()
+ "' OR E.ZJM like '" + txbGDSEQ.Text.Trim() + "' OR E.ZJM like '" + txbGDSEQ.Text.Trim().ToUpper() + "' OR E.ZJM like '" + txbGDSEQ.Text.Trim().ToLower() + "')";
}
if (!PubFunc.StrIsEmpty(ddlDEPTID.SelectedValue))
{
strSql += " and A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
if (!PubFunc.StrIsEmpty(tbxNAME.Text.Trim()))
{
strSql += " and A.DEPTID LIKE '" + tbxNAME.Text.Trim() + "'";
}
if (!PubFunc.StrIsEmpty(TextZYH.Text.Trim()))
{
strSql += " and A.DEPTID LIKE '" + TextZYH.Text.Trim() + "'";
}
strSql += string.Format(@" AND A.XSRQ >= TO_DATE('" + dpkDATE1.Text.Trim() + "', 'YYYY-MM-DD')");
strSql += string.Format(@" AND A.XSRQ < TO_DATE('" + dpkDATE2.Text.Trim() + "', 'YYYY-MM-DD') + 1");
strSql += string.Format(@" ORDER BY C.GDSEQ DESC");
return(strSql);
}
private string GetSearchSql()
{
string strSql = @"SELECT M.CODE DEPTID,
M.NAME DEPTNAME,
SUM(DECODE(M.ISJF, 'Y', M.SL, 0)) JFSL,
SUM(DECODE(M.ISJF, 'N', M.SL, 0)) FJFSL,
SUM(M.SL) SL,
SUM(DECODE(M.ISJF, 'Y', M.JE, 0)) JFJE,
SUM(DECODE(M.ISJF, 'N', M.JE, 0)) FJFJE,
SUM(M.JE) JE
FROM (SELECT DGJ.BILLNO,
SD.CODE,
SD.NAME,
DECODE(DGJ.KCADD,
-1,
DECODE(DGJ.BILLTYPE,
'XSD',
DGJ.SL * DGJ.KCADD,
'XSG',DGJ.SL*DGJ.KCADD),1,DECODE(DGJ.BILLTYPE,'XST',DGJ.SL*(-1))) SL,
NVL(DG.STR6,'N') ISJF,
DECODE(DGJ.KCADD,
-1,
DECODE(DGJ.BILLTYPE,
'XSD',
DGJ.HSJE * DGJ.KCADD,
'XSG',DGJ.HSJE*DGJ.KCADD),1,DECODE(DGJ.BILLTYPE,'XST',DGJ.HSJE*(-1))) JE
FROM DAT_GOODSJXC DGJ, DOC_GOODS DG, DOC_GOODSCFG DGC, SYS_DEPT SD
WHERE DGJ.DEPTID = SD.CODE
AND DGJ.GDSEQ = DG.GDSEQ
AND DGJ.GDSEQ = DGC.GDSEQ
AND DGJ.DEPTID = DGC.DEPTID
AND DG.FLAG = 'Y'
AND DGC.ISCFG = 'Y'
AND SD.FLAG = 'Y'
AND DGJ.BILLTYPE IN ('XSD', 'XST','XSG')
AND SD.TYPE <> '1'
AND DGJ.RQSJ >=TO_DATE('{0}','YYYY-MM-DD') AND DGJ.RQSJ<=TO_DATE('{1}','YYYY-MM-DD')+1
AND DG.ISGZ LIKE NVL('{2}','%')) M
WHERE M.SL IS NOT NULL
";
string strWhere = " ";
if (!PubFunc.StrIsEmpty(ddlDEPTID.SelectedValue))
{
strWhere += " AND M.CODE='" + ddlDEPTID.SelectedValue + "' ";
}
if (strWhere != " ")
{
strSql = strSql + strWhere;
}
strSql = string.Format(strSql, lstLRRQ1.Text, lstLRRQ2.Text, ddlISGZ.SelectedValue);
strSql += string.Format(" GROUP BY M.CODE,M.NAME ORDER BY {0} {1} ", GridGoods.SortField, GridGoods.SortDirection);
return(strSql);
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (PubFunc.FormDataCheck(FormMx).Length > 0)
{
return;
}
//if (tbxCode.Text.Length != ddlSJCODE.SelectedValue.Length + 2 && tbxCode.Text.Length > 2)
//{
// Alert.Show("编码规则不正确，请检查！", "提示信息", MessageBoxIcon.Warning);
// return;
//}
//增加控制避免主键重复
if ((DbHelperOra.Exists("select 1 from SYS_DEPT where code = '" + tbxCode.Text + "'")) && (tbxCode.Enabled))
{
Alert.Show("你输入的编码已存在,请检查!");
return;
}
if (string.IsNullOrWhiteSpace(ddlSJCODE.SelectedValue))
{
Alert.Show("请填选上级！", "提示", MessageBoxIcon.Information);
return;
}
if (!CheckTel(tbxSTR6.Text))
{
Alert.Show("联系电话请保证格式（固话XXX-XXX）(手机1XXX)！");
return;
}
string ls_codelabel;
MyTable mtTable = new MyTable(strTable, "CODE");
mtTable.ColRow = PubFunc.FormDataHT(FormMx);
ls_codelabel = PubFunc.FormLabelGet(FormMx, "CODE");
if (PubFunc.StrIsEmpty(mtTable.ColRow["CODE"].ToString()))
{
Alert.Show("【" + ls_codelabel + "】不能为空！");
return;
}
if (tbxCode.Enabled) //新增
{
mtTable.InsertExec();
}
else
{
mtTable.UpdateExec("");
}
//写入日志
OperLog("管理架构定义", "修改资料【" + tbxCode.Text + "】");
Alert.Show("数据保存成功！");
ERPUtility.CacheClear("SYS_DEPT");
tbxCode.Enabled = false;
InitTree();
}
private void dataSearch()
{
string strsql = @"SELECT IGU.ONECODE,IGU.GDSEQ,IGU.DJRQ,IGU.DEPTSY DEPTID,IGU.PATIENT,IGU.IDNO,IGU.BEDNO,IGU.BILLNO,IGU.FLAG,DECODE(IGU.FLAG,'G','已处理','N','新增','X','异常')FLAGNAME,IGU.GETTIME,IGU.EXECTIME,DG.GDNAME, SD.NAME DEPTNAME
FROM INF_GZ_USE IGU, DOC_GOODS DG, DAT_GZ_EXT DGE, SYS_DEPT SD
WHERE IGU.ONECODE = DGE.ONECODE
AND IGU.GDSEQ = DG.GDSEQ
AND IGU.DEPTSY = SD.CODE
AND DG.FLAG = 'Y'
AND SD.FLAG = 'Y' ";
string where = "";
if (dpkBEGRQ.SelectedDate == null || dpkENDRQ.SelectedDate == null)
{
Alert.Show("时间填写不能为空！");
return;
}
else
{
if (dpkENDRQ.SelectedDate > dpkENDRQ.SelectedDate)
{
Alert.Show("开始时间不能晚于结束时间！");
}
}
if (srhFLAG.SelectedValue == "Y")
{
where += " AND IGU.FLAG='G' ";
}
else if (srhFLAG.SelectedValue == "N")
{
where += " AND IGU.FLAG IN('X','N') ";
}
if (!PubFunc.StrIsEmpty(ddlDEPTID.SelectedValue))
{
where += " AND IGU.DEPTSY = '" + ddlDEPTID.SelectedValue + "'";
}
if (!string.IsNullOrEmpty(txtPATIENT.Text))
{
where += " AND (IGU.PATIENT LIKE('%" + txtPATIENT.Text + "%') OR IGU.IDNO LIKE('%" + txtPATIENT.Text + "%')) ";
}
where += string.Format("AND IGU.DJRQ>=TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') AND IGU.DJRQ<=TO_DATE('{1}','YYYY-MM-DD HH24:MI:SS')+1 ORDER BY DECODE(IGU.FLAG,'X','1','N','2','Y','3')", dpkBEGRQ.SelectedDate, dpkENDRQ.SelectedDate);
int total = 0;
strsql += where;
DataTable dt = PubFunc.DbGetPage(GridGoods.PageIndex, GridGoods.PageSize, strsql, ref total);
// DataTable dttotal = DbHelperOra.QueryForTable(strsql);
GridGoods.RecordCount = total;
GridGoods.DataSource = dt;
// OutputSummaryData(dt, dttotal);
GridGoods.DataBind();
}
protected void GridGoods_AfterEdit(object sender, FineUIPro.GridAfterEditEventArgs e)
{
if (hdfOper.Text == "audit" || (",M,N").IndexOf(docFLAG.SelectedValue) < 0)
{
return;
}
string[] strCell = GridGoods.SelectedCell;
List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList();
if (newDict.Count == 0)
{
return;
}
#region 取消其他计算列
if (e.ColumnID == "BZSL")
{
if (!PubFunc.isNumeric(Doc.GetGridInf(GridGoods, e.RowID, "BZHL")) || !PubFunc.isNumeric(Doc.GetGridInf(GridGoods, e.RowID, "BZSL")) || !PubFunc.isNumeric(Doc.GetGridInf(GridGoods, e.RowID, "HSJJ")))
{
Alert.Show("商品信息异常，请详细检查商品信息：包装含量、价格或数量！", "异常信息", MessageBoxIcon.Warning);
return;
}
JObject defaultObj = Doc.GetJObject(GridGoods, e.RowID);
decimal hl = 0, rs = 0, jg = 0;
decimal.TryParse((defaultObj["BZHL"] ?? "0").ToString(), out hl);
decimal.TryParse((defaultObj["BZSL"] ?? "0").ToString(), out rs);
decimal.TryParse((defaultObj["HSJJ"] ?? "0").ToString(), out jg);
defaultObj["DHSL"] = rs * hl;
defaultObj["HSJE"] = Math.Round(rs * jg, 2).ToString("F2");
PageContext.RegisterStartupScript(GridGoods.GetUpdateCellValueReference(e.RowID, defaultObj));
//计算合计数量
decimal bzslTotal = 0, feeTotal = 0, dhslTotal = 0;
foreach (Dictionary <string, object> dic in newDict)
{
if (dic["BZSL"] != null)
{
if (!PubFunc.StrIsEmpty(dic["BZSL"].ToString()))
{
bzslTotal += Convert.ToDecimal(dic["BZSL"]);
feeTotal += Convert.ToDecimal(dic["HSJJ"]) * Convert.ToDecimal(dic["BZSL"]);
dhslTotal += Convert.ToDecimal(dic["BZHL"]) * Convert.ToDecimal(dic["BZSL"]);
}
}
}
JObject summary = new JObject();
summary.Add("GDNAME", "本页合计");
summary.Add("BZSL", bzslTotal.ToString());
summary.Add("HSJE", feeTotal.ToString("F2"));
summary.Add("DHSL", dhslTotal.ToString());
GridGoods.SummaryData = summary;
}
#endregion
}
