public string goodsDetail(HttpContext context)
{
string fgdseq = context.Request["fgdseq"] == null ? "" : context.Request["fgdseq"].ToString();//获取参数
string strUrl = System.Configuration.ConfigurationManager.AppSettings["URL_PIC"];
string strResult = "";
string sql = @"SELECT A.*,B.FTYPE, '{1}'||A.FGDSEQ||'/'||B.FNAME FURL
FROM DOC_GOODS A,DOC_GOODSPIC B
WHERE A.FGDSEQ = B.FGDSEQ AND A.FGDSEQ = '{0}' AND B.FTYPE IN('T2','T3','T4')";
DataTable dt = DbHelperOra.QueryForTable(string.Format(sql, fgdseq, strUrl));
if (dt.Rows.Count > 0)
{
strResult = new JObject(
new JProperty("res", "1"),
new JProperty("swiperItems", JsonConvert.SerializeObject(dt.Select("FTYPE = 'T2'"))),
new JProperty("goodsbuy", JsonConvert.SerializeObject(dt.Select("FTYPE = 'T4'"))),
new JProperty("goodspic", JsonConvert.SerializeObject(dt.Select("FTYPE = 'T3'")))
).ToString();
}
else
{
strResult = new JObject(
new JProperty("res", "0")
).ToString();
}
return(strResult);
}
protected void btExport_Click(object sender, EventArgs e)
{
string sql = "", headname = "";
if (TabStrip1.ActiveTabIndex == 0)
{
if (GridCom.Rows.Count < 1)
{
Alert.Show("没有数据，无法导出！");
return;
}
sql = GetSearchSql1();
headname = "财务明细查询";
string[,] col = { { "GDSEQ", "GDNAME", "GDSPEC", "UNIT", "HSJJ", "SL", "JE" }, { "商品编码", "商品名称", "规格", "单位", "单价", "数量", "金额" } };
DataTable dtData = DbHelperOra.QueryForTable(sql);
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(true), headname, string.Format(headname + "_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")), col);
}
else
{
if (GridGoods.Rows.Count < 1)
{
Alert.Show("没有数据，无法导出！");
return;
}
sql = GetSearchSql();
headname = "财务汇总查询";
string[,] col = { { "DEPTID", "DEPTNAME", "XF", "YQ", "FSCL", "HYCL", "YLYP", "GZHC", "QTCL", "HJ" }, { "科室编码", "科室名称", "血费", "氧气", "放射材料", "化验材料", "医疗用品", "高值耗材", "其他材料", "合计" } };
DataTable dtData = DbHelperOra.QueryForTable(sql);
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(true), headname, string.Format(headname + "_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")), col);
}
}
public string addres(HttpContext context)
{
string userid = context.Request["str1"] == null ? "" : context.Request["str1"].ToString();//获取参数
string strResult = "";
string sql = string.Format(@"SELECT *
FROM DOC_ADDRESS WHERE FUSERID = '{0}'", userid);
DataTable dt = DbHelperOra.QueryForTable(sql);
if (dt.Rows.Count == 0)
{
strResult = new JObject(
new JProperty("res", "0"),
new JProperty("msg", "用户为空！")
).ToString();
}
else
{
//校验账户
string strJson = JsonConvert.SerializeObject(dt);
strResult = new JObject(
new JProperty("res", "1"),
new JProperty("msg", strJson)
).ToString();
}
return(strResult);
}
protected string init_bar_data(string type, string deptid, DateTime rq)
{
//type:PERNUM,YPSL,YPJE,XSSL,XSJE,JFSL,JFJE,GZSL,GZJE,PHSL,PHJE
string returnstr = "";
string strsql = string.Format(@"SELECT DEPTID,SUM(NVL({0},0))SUMRET
FROM INF_HIS_DEPTDAY IHD
WHERE IHD.DEPTID LIKE NVL('{1}','%') AND TRUNC(RQ,'MM') =TRUNC(TO_DATE('{2}','YYYY-MM'),'MM')
GROUP BY DEPTID,TO_CHAR(RQ,'YYYY-MM')", type, deptid, rq.ToString("yyyy-MM"));
DataTable dt = DbHelperOra.QueryForTable(strsql);
if (dt.Rows.Count < 1)
{
returnstr = "0";
}
else
{
returnstr = dt.Rows[0][1].ToString();
}
if (string.Equals(type, "PERNUM") == false)
{
max_je = Convert.ToDecimal(returnstr) > max_je?Convert.ToDecimal(returnstr) : max_je;
}
else
{
max_rc = Convert.ToDecimal(returnstr) > max_rc?Convert.ToDecimal(returnstr) : max_rc;
}
return(returnstr);
}
protected void btExport_Click(object sender, EventArgs e)
{
string sql = "", headname = "";
if (TabStrip1.ActiveTabIndex == 0)
{
if (GridCom.Rows.Count < 1)
{
Alert.Show("没有数据，无法导出！");
return;
}
sql = GetSearchSql1();
headname = "计费与非计费商品明细";
string[,] col = { { "DEPTID", "DEPTNAME", "GDSEQ", "GDNAME", "GDSPEC", "UNIT", "HSJJ", "SL", "JE" }, { "科室编码", "科室名称", "商品编码", "商品名称", "规格", "单位", "单价", "数量", "金额" } };
DataTable dtData = DbHelperOra.QueryForTable(sql);
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(true), headname, string.Format(headname + "_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")), col);
}
else
{
if (GridGoods.Rows.Count < 1)
{
Alert.Show("没有数据，无法导出！");
return;
}
sql = GetSearchSql();
headname = "计费与非计费商品汇总";
string[,] col = { { "DEPTID", "DEPTNAME", "JFSL", "FJFSL", "SL", "JFJE", "FJFJE", "JE" }, { "科室编码", "科室名称", "计费总数", "非计费总数", "总数量", "计费总额", "非计费总额", "总金额" } };
DataTable dtData = DbHelperOra.QueryForTable(sql);
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(true), headname, string.Format(headname + "_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")), col);
}
}
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;
}
protected void btExport_Click(object sender, EventArgs e)
{
DataTable dtData = DbHelperOra.QueryForTable(GetSearchSql());
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(true), "财务对账查询", string.Format("财务对账查询_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
//((FineUIPro.Button)sender).Enabled = true;
}
public string goodsShoplist(HttpContext context)
{
string fuser = context.Request["fuser"] == null ? "" : context.Request["fuser"].ToString();//获取参数
string strUrl = System.Configuration.ConfigurationManager.AppSettings["URL_PIC"];
string strResult = "";
string sql = @"SELECT B.FGDSEQ,B.FGDNAME,B.FZKJJ,A.FSL,'{1}'||C.FGDSEQ||'/'||C.FNAME FURL
FROM DAT_SHOP A,DOC_GOODS B,DOC_GOODSPIC C
WHERE A.FGDSEQ = B.FGDSEQ
AND B.FGDSEQ = C.FGDSEQ AND C.FTYPE = 'T4'
AND A.FUSERID = '{0}'";
DataTable dt = DbHelperOra.QueryForTable(string.Format(sql, fuser, strUrl));
if (dt.Rows.Count > 0)
{
strResult = new JObject(
new JProperty("res", "1"),
new JProperty("goodsShoplist", JsonConvert.SerializeObject(dt)),
new JProperty("msg", "")
).ToString();
}
else
{
strResult = new JObject(
new JProperty("res", "0"),
new JProperty("goodsShoplist", ""),
new JProperty("msg", "系统错误")
).ToString();
}
return(strResult);
}
private void DataInit()
{
ddlUsers.DataSource = DbHelperOra.QueryForTable("SELECT USERID,USERNAME FROM SYS_OPERUSER WHERE ISDELETE='N' ORDER BY USERID ASC");
ddlUsers.DataTextField = "USERNAME";
ddlUsers.DataValueField = "USERID";
ddlUsers.DataBind();
hfdIsNew.Text = "Y";
}
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 btnBriefScreen_Click(object sender, EventArgs e)
{
DataTable dt = DbHelperOra.QueryForTable(@"select CODE BRCODE,
NAME BRNAME,
FLAG,
DECODE(A.FLAG, 'Y', '启用', 'N', '不启用','未维护') FLAGNAME
from DOC_BRIEF_DOC A
where 1=1 ORDER BY CODE ASC");
}
private void DataSearch()
{
int total = 0;
DataTable dtData = PubFunc.DbGetPage(GridGoods.PageIndex, GridGoods.PageSize, GetSearchSql(), ref total);
DataTable dttotal = DbHelperOra.QueryForTable(GetSearchSql());
OutputSummaryData(dtData, dttotal);
GridGoods.RecordCount = total;
GridGoods.DataSource = dtData;
GridGoods.DataBind();
}
protected void btnUserScreen_Click(object sender, EventArgs e)
{
DataTable dt = DbHelperOra.QueryForTable(@"select CODE BRCODE,
NAME BRNAME,
FLAG,
DECODE(FLAG, 'Y', '启用', 'N', '不启用','未维护') FLAGNAME
F_JOINUSERID(CODE) USERID
from DOC_BRIEF_DOC A
where 1=1");
}
protected override void billSearch()
{
if (PubFunc.StrIsEmpty(dpkBEGRQ.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkENDRQ.SelectedDate.ToString()))
{
Alert.Show("【输入日期】不正确,请检查！", MessageBoxIcon.Warning);
return;
}
if (dpkBEGRQ.SelectedDate > dpkENDRQ.SelectedDate)
{
Alert.Show("【开始日期】不能大于【结束日期】！", MessageBoxIcon.Warning);
return;
}
DateTime begrq = Convert.ToDateTime(dpkBEGRQ.Text);
DateTime endrq = Convert.ToDateTime(dpkENDRQ.Text);
string strSql = string.Format(@"select code,name CATEGORY,F_GET_CAT_PIE('SYSL',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')SYSL,F_GET_CAT_PIE('SLZB',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')SLZB,
TO_NUMBER(F_GET_CAT_PIE('SYJE',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z'))SYJE, F_GET_CAT_PIE('JEZB',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')JEZB, F_GET_CAT_PIE('HBZZSL',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')HBZZSL ,
F_GET_CAT_PIE('TBZZSL',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')TBZZSL, F_GET_CAT_PIE('HBZZJE',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')HBZZJE,F_GET_CAT_PIE('TBZZJE',CODE,TO_DATE('{0}','yyyy-MM-dd'),TO_DATE('{1}','yyyy-MM-dd'),'Z')TBZZJE
from doc_goodstype WHERE FLAG = 'Y' ", begrq.ToShortDateString(), endrq.ToShortDateString());
string strSearch = "";
if (!string.IsNullOrEmpty(lstCAT.SelectedValue))
{
strSearch += " AND CODE='" + lstCAT.SelectedValue + "' ";
}
strSql += strSearch;
int total = 0;
highyellowlight.Text = ",";
highredlight.Text = ",";
string sortField = GridList.SortField;
string sortDirection = GridList.SortDirection;
//DataTable dt = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, strSql + String.Format(" ORDER BY code"), ref total);
DataTable dt = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, strSql + String.Format(" ORDER BY {0} {1}", sortField, sortDirection), ref total);
GridList.RecordCount = total;
GridList.DataSource = dt;
GridList.DataBind();
DataTable dtinit;
// DataTable dtinit = DbHelperOra.QueryForTable("select code,name from doc_goodstype where flag='Y'" + String.Format(" ORDER BY {0} {1}", "CODE", sortDirection));
if (!string.IsNullOrEmpty(lstCAT.SelectedValue))
{
dtinit = DbHelperOra.QueryForTable("select code,name from sys_category where type ='" + lstCAT.SelectedValue + "' and flag='Y'" + String.Format(" ORDER BY {0} {1}", "CODE", sortDirection));
}
else
{
dtinit = DbHelperOra.QueryForTable("select code,name from doc_goodstype where flag='Y'" + String.Format(" ORDER BY {0} {1}", "CODE", sortDirection));
}
init_pie(dtinit, begrq, endrq);
}
private void DataInit()
{
dpkBEGRQ.SelectedDate = DateTime.Now.AddDays(-30);
dpkENDRQ.SelectedDate = DateTime.Now;
DateTime begrq = Convert.ToDateTime(dpkBEGRQ.Text);
DateTime endrq = Convert.ToDateTime(dpkENDRQ.Text);
lstCAT.DataValueField = "CODE";
lstCAT.DataTextField = "NAME";
DataTable dt = DbHelperOra.QueryForTable("SELECT ''CODE,'--请选择--'NAME FROM DUAL UNION ALL SELECT code,NAME FROM doc_goodstype WHERE FLAG = 'Y'");
lstCAT.DataSource = dt;
lstCAT.DataBind();
}
protected void btnSch3_Click(object sender, EventArgs e)
{
if (PubFunc.StrIsEmpty(DatePicker1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(DatePicker2.SelectedDate.ToString()))
{
Alert.Show("输入日期不正确,请检查！");
return;
}
if (DatePicker1.SelectedDate > DatePicker2.SelectedDate)
{
Alert.Show("开始日期不能大于结束日期！");
return;
}
string strSql = @"select A.SEQNO,
(CASE WHEN A.SEQNO IN (select DISTINCT (seqno) from DAT_DD_COM t where dhs > dhsl) THEN '未完成'
else '已完成' END) FLAGNAME,
A.DEPTDH,
f_getdeptname(A.DEPTDH) DEPTDHNAME,
A.DEPTID,
f_getdeptname(A.DEPTID) DEPTIDNAME,
A.LRRQ,
A.LRY,
f_getusername(A.LRY) LRYNAME
from dat_dd_doc A";
string strWhere = " where 1=1 ";
if (!PubFunc.StrIsEmpty(TextBox1.Text.Trim()))
{
strWhere += " AND A.SEQNO LIKE '%" + lisBILL.Text.Trim() + "%'";
}
if (!PubFunc.StrIsEmpty(DropDownList1.SelectedValue.Trim()))
{
if (DropDownList1.SelectedValue == "N")
{
strWhere += " AND (CASE WHEN A.SEQNO IN (select DISTINCT (seqno) from DAT_DD_COM t where dhs > dhsl) THEN '未完成' else '已完成' END) = '未完成'";
}
if (DropDownList1.SelectedValue == "Y")
{
strWhere += " AND (CASE WHEN A.SEQNO IN (select DISTINCT (seqno) from DAT_DD_COM t where dhs > dhsl) THEN '未完成' else '已完成' END) = '已完成'";
}
}
strWhere += " AND A.LRRQ>=TO_DATE('{0}','YYYY-MM-DD')";
strWhere += " AND A.LRRQ <TO_DATE('{1}','YYYY-MM-DD') + 1";
strSql += strWhere + " order by LRRQ DESC";
Grid1.DataSource = DbHelperOra.QueryForTable(string.Format(strSql, DatePicker1.Text, DatePicker2.Text));
Grid1.DataBind();
}
protected string getSendMes(string strcode)
{
DataTable dtget = DbHelperOra.QueryForTable(string.Format("SELECT ESQL,BRCONTENT FROM DOC_BRIEF_DOC WHERE CODE='{0}'", strcode));
string strResult = "";
if (dtget.Rows.Count > 0)
{
DataTable dtSQL = DbHelperOra.QueryForTable(dtget.Rows[0][0].ToString());
Regex regex = new Regex(@"[:：][1-9]\d*");
strResult = dtget.Rows[0][1].ToString();
MatchCollection mac = regex.Matches(strResult);
for (int i = 0; i < mac.Count; i++)
{
strResult = strResult.Replace(mac[i].ToString(), dtSQL.Rows[0][i].ToString());
}
}
return(strResult);
}
protected void btnTest_Click(object sender, EventArgs e)
{
DataTable dtget = DbHelperOra.QueryForTable(string.Format("SELECT ESQL,BRCONTENT FROM DOC_BRIEF_DOC WHERE CODE='{0}'", tbxCODE.Text));
string strResult = "";
if (dtget.Rows.Count > 0)
{
DataTable dtSQL = DbHelperOra.QueryForTable(dtget.Rows[0][0].ToString());
Regex regex = new Regex(@"[:：][1-9]\d*");
strResult = dtget.Rows[0][1].ToString();
MatchCollection mac = regex.Matches(strResult);
for (int i = 0; i < mac.Count; i++)
{
strResult = strResult.Replace(mac[i].ToString(), dtSQL.Rows[0][i].ToString());
}
}
labSHOW.Text = "【预览结果】:" + strResult + "";
}
protected void btnSearch1_Click(object sender, EventArgs e)
{
if (dpkBegRQ.SelectedDate == null || dpkEndRQ.SelectedDate == null)
{
Alert.Show("请输入条件【查询期间】！");
return;
}
else if (dpkBegRQ.SelectedDate > dpkEndRQ.SelectedDate)
{
Alert.Show("开始日期大于结束日期，请重新输入！");
return;
}
DataTable dtData = DbHelperOra.QueryForTable(GetSearchSql1());
OutputSummaryData1(dtData);
GridCom.DataSource = dtData;
GridCom.DataBind();
}
public static void DataExport(string sql, FineUIPro.Grid grid, string title)
{
DataTable dtData = DbHelperOra.QueryForTable(sql);
if (dtData == null || dtData.Rows.Count == 0)
{
Alert.Show("没有数据,无法导出！");
return;
}
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("序号", typeof(int)));
foreach (DataColumn col in dtData.Columns)
{
table.Columns.Add(new DataColumn(col.ColumnName, col.DataType));
}
decimal rowno = 0;
foreach (DataRow row in dtData.Rows)
{
DataRow dr = table.NewRow();
dr["序号"] = ++rowno;
foreach (DataColumn col in dtData.Columns)
{
dr[col.ColumnName] = row[col.ColumnName];
}
table.Rows.Add(dr);
}
List <string> colmns = new List <string>();
colmns.Add("序号");
for (int index = 1; index < grid.Columns.Count; index++)
{
GridColumn column = grid.Columns[index];
if (column is FineUIPro.BoundField)
{
table.Columns[((FineUIPro.BoundField)(column)).DataField.ToUpper()].ColumnName = column.HeaderText;
colmns.Add(column.HeaderText);
}
}
ExcelHelper.ExportByWeb(dtData, title, title + "_" + DateTime.Now.ToString("yyyyMMddHH") + ".xls");
}
protected void Bill_Audit(bool flag = true)
{
DataSave();
if (docFLAG.SelectedValue == "A")
{
if (DbHelperOra.Exists("select 1 from dat_db_com where xssl < 1 and seqno = '" + docSEQNO.Text + "'") && flag)
{
//Alert.Show("请首先进行库存分配,并确认调拨商品的批号信息不为空!", "提示信息", MessageBoxIcon.Warning);
PageContext.RegisterStartupScript(Confirm.GetShowReference("此调拨单存在出库数量都为0的商品，是否继续？",
"信息提示", MessageBoxIcon.Information, PageManager1.GetCustomEventReference(true, "Confirm_Fp", false, false), null));
return;
}
//验证库房是否盘点
if (DbHelperOra.Exists("SELECT 1 FROM DAT_PD_LOCK WHERE DEPTID IN('" + docDEPTOUT.SelectedValue + "','" + docDEPTID.SelectedValue + "') AND FLAG='N'"))
{
Alert.Show("库房正在盘点,请检查!");
return;
}
billLockDoc(true);
//验证单据准确性
DataTable dt = DbHelperOra.QueryForTable("select dg.gdseq from dat_db_com dbc,doc_goods dg where dbc.gdseq=dg.gdseq and dg.islot='2' and nvl(dbc.ph,'#') ='#' and dbc.seqno='" + docSEQNO.Text + "'");
if (dt.Rows.Count > 0)
{
}
if (BillOper(docSEQNO.Text, "AUDIT") == 1)
{
//if (!DbHelperOra.Exists("select 1 from dat_db_com where xssl < dhsl and seqno = '" + docSEQNO.Text + "'"))
if (!DbHelperOra.Exists("select 1 from (select seqno,gdseq,sum(xssl) xssl,sum(dhsl) dhsl from dat_db_com group by seqno,gdseq,gdspec) a where a.xssl < a.dhsl and a.seqno = '" + docSEQNO.Text + "'"))
{
Alert.Show("调拨出库单【" + docSEQNO.Text + "】审核成功！");
}
else
{
Alert.Show("调拨出库单【" + docSEQNO.Text + "】审核成功，库存不足商品自动生成缺货！");
}
billOpen(docSEQNO.Text);
OperLog("商品调拨", "审核单据【" + docSEQNO.Text + "】-调拨出库");
}
}
else
{
Alert.Show("调拨出库单【" + docSEQNO.Text + "】状态不正确", "提示信息", MessageBoxIcon.Warning);
}
}
protected void btnlis_Click(object sender, EventArgs e)
{
string strSql = @"SELECT B.*,F_GETDEPTNAME(A.DEPTID) DEPTIDNAME,F_GETDEPTNAME(A.DEPTOUT) DEPTOUTNAME,F_GETUNITNAME(B.UNIT) UNITNAME,F_GETPRODUCERNAME(B.PRODUCER) PRODUCERNAME
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('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')";
string strWhere = "";
if (!PubFunc.StrIsEmpty(tbxGDSEQ.Text.Trim()))
{
strWhere += string.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%')", tbxGDSEQ.Text.Trim());
}
if (!PubFunc.StrIsEmpty(ddlDEPT.SelectedValue))
{
strWhere += string.Format(" AND (a.deptid = '{0}')", ddlDEPT.SelectedValue);
}
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += " order by a.deptid，b.gdname";
DataTable dtBill = DbHelperOra.QueryForTable(string.Format(strSql, lisData1.Text, lisData2.Text));
Grdlist.DataSource = dtBill;
Grdlist.DataBind();
//合计
decimal sumsl = 0, sumhjje = 0;
if (dtBill != null && dtBill.Rows.Count > 0)
{
foreach (DataRow row in dtBill.Rows)
{
sumsl += Convert.ToDecimal(row["XSSL"]);
sumhjje += Convert.ToDecimal(row["HSJE"]);
}
}
JObject summary = new JObject();
summary.Add("GDNAME3", "本页合计");
summary.Add("XSSL3", sumsl.ToString("F0"));
summary.Add("HSJE3", sumhjje.ToString("F2"));
Grdlist.SummaryData = summary;
}
protected void btnSch_Click(object sender, EventArgs e)
{
string strSql = @"SELECT B.GDSEQ,B.GDNAME,B.GDSPEC,B.UNIT,F_GETUNITNAME(B.UNIT) UNITNAME,B.HSJJ,SUM(B.HSJE) HSJE,F_GETPRODUCERNAME(B.PRODUCER) PRODUCERNAME,B.PZWH,SUM(B.XSSL) SL
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('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')";
string strWhere = "";
if (!PubFunc.StrIsEmpty(tbxGoods.Text.Trim()))
{
strWhere += string.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%')", tbxGoods.Text.Trim());
}
if (!PubFunc.StrIsEmpty(lisDEPT.SelectedValue))
{
strWhere += string.Format(" AND (a.deptid = '{0}')", lisDEPT.SelectedValue);
}
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += " group by B.gdseq,B.gdname,B.gdspec,B.unit,b.hsjj,b.producer,B.PZWH order by B.gdname";
DataTable dtBill = DbHelperOra.QueryForTable(string.Format(strSql, dpktim1.Text, dpktim2.Text));
Gridlist4.DataSource = dtBill;
Gridlist4.DataBind();
//合计
decimal sumsl = 0, sumhjje = 0;
if (dtBill != null && dtBill.Rows.Count > 0)
{
foreach (DataRow row in dtBill.Rows)
{
sumsl += Convert.ToDecimal(row["SL"]);
sumhjje += Convert.ToDecimal(row["HSJE"]);
}
}
JObject summary = new JObject();
summary.Add("GDNAME4", "本页合计");
summary.Add("SL4", sumsl.ToString("F0"));
summary.Add("HSJE4", sumhjje.ToString("F2"));
Gridlist4.SummaryData = summary;
}
protected void CheckSQL()
{
try
{
DbHelperOra.ExecuteSql(trbESQL.Text.Trim());
}
catch (Exception ex)
{
Alert.Show(Error_Parse(ex.Message));
return;
}
DataTable dt = DbHelperOra.QueryForTable(trbESQL.Text);
Regex regex = new Regex(@"[:：][1-9]\d*");
if (!regex.Matches(trbBRCONTENT.Text).Count.ToString().Equals(dt.Columns.Count.ToString()))
{
Alert.Show("SQL数量不匹配");
return;
}
}
protected void btnSrch_Click(object sender, EventArgs e)
{
if (PubFunc.StrIsEmpty(dpkdata1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkdata2.SelectedDate.ToString()))
{
Alert.Show("输入日期不正确,请检查！");
return;
}
if (dpkdata1.SelectedDate > dpkdata2.SelectedDate)
{
Alert.Show("开始日期不能大于结束日期！");
return;
}
string strSql = @"select sum(hsje) hjje,F_GETPARA('SUPPER') SUPNAME,sum(decode(a.billtype,'DSH',b.bzhl,b.xssl)) hjsl
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('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd')";
Gridlist2.DataSource = DbHelperOra.QueryForTable(string.Format(strSql, dpkdata1.Text, dpkdata2.Text));
Gridlist2.DataBind();
hiddata1.Text = dpkTIME1.Text;
hiddata2.Text = dpkTIME2.Text;
}
protected void btnSearch1_Click(object sender, EventArgs e)
{
if (dpkBegRQ.SelectedDate == null || dpkEndRQ.SelectedDate == null)
{
Alert.Show("请输入条件【查询期间】！");
return;
}
else if (dpkBegRQ.SelectedDate > dpkEndRQ.SelectedDate)
{
Alert.Show("开始日期大于结束日期，请重新输入！");
return;
}
int total = 0;
string sql = GetSearchSql1();
DataTable dtData = PubFunc.DbGetPage(GridGoods.PageIndex, GridGoods.PageSize, sql, ref total);
DataTable dttable = DbHelperOra.QueryForTable(sql);
OutputSummaryData1(dtData, dttable);
GridCom.DataSource = dtData;
GridCom.DataBind();
}
public void ProcessRequest(HttpContext context)
{
string rowId = context.Request.QueryString["id"];
// int rowIdInt = Convert.ToInt32(rowId);
string rowname = context.Request.QueryString["name"];
string sbegrq = context.Request.QueryString["begrq"];
string sendrq = context.Request.QueryString["endrq"];
DateTime begrq = Convert.ToDateTime(sbegrq);
DateTime endrq = Convert.ToDateTime(sendrq);
JArray ja = new JArray();
DataTable dt = DbHelperOra.QueryForTable("select code,name from sys_category where TYPE='" + rowname + "' and flag='Y' ");
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataTable dts = DbHelperOra.QueryForTable(string.Format(@"select
F_GET_CAT_PIE('SYSL','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')SYSL,
F_GET_CAT_PIE('SLZB','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')SLZB,
F_GET_CAT_PIE('HBZZSL','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')HBZZSL,
F_GET_CAT_PIE('TBZZSL','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')TBZZSL,
F_GET_CAT_PIE('SYJE','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')SYJE ,
F_GET_CAT_PIE('JEZB','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')JEZB,
F_GET_CAT_PIE('HBZZJE','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')HBZZJE,
F_GET_CAT_PIE('TBZZJE','{0}',TO_DATE('{1}','yyyy-MM-dd'),TO_DATE('{2}','yyyy-MM-dd'),'C')TBZZJE
from dual ", dt.Rows[i][0].ToString(), begrq.ToShortDateString(), endrq.ToShortDateString()));
JArray jaItem = new JArray();
jaItem.Add(dt.Rows[i][1].ToString());
for (int j = 0; j < 8; j++)
{
jaItem.Add(dts.Rows[0][j].ToString());
}
ja.Add(jaItem);
}
}
context.Response.ContentType = "text/plain";
context.Response.Write(ja.ToString(Newtonsoft.Json.Formatting.None));
}
protected void btnSch_Click(object sender, EventArgs e)
{
if (PubFunc.StrIsEmpty(lisDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(lisDATE2.SelectedDate.ToString()))
{
Alert.Show("输入日期不正确,请检查！");
return;
}
if (lisDATE1.SelectedDate > lisDATE2.SelectedDate)
{
Alert.Show("开始日期不能大于结束日期！");
return;
}
string strSql = @"SELECT A.SEQNO,DECODE(nvl(B.SEQNO,'#'),'#','未入库','已入库') FLAGNAME,F_GETDEPTNAME(A.DEPTDH) DEPTDHNAME,F_GETDEPTNAME(A.DEPTID) DEPTIDNAME,A.SUBNUM NUM_DH,F_GETUSERNAME(A.LRY) LRYNAME,A.SHRQ,B.SEQNO SEQNO_RK,B.NUM_RK,B.SHRQ_RK,F_GETUSERNAME(B.SHR) SHRNAME
FROM DAT_DD_DOC A,(SELECT SEQNO,DDBH,SUM(SUBNUM) NUM_RK,MAX(SHRQ) SHRQ_RK,MAX(SHR) SHR FROM DAT_RK_DOC WHERE FLAG IN ('Y','G') GROUP BY SEQNO,DDBH) B
WHERE A.SEQNO = B.DDBH(+) AND A.FLAG IN ('Y','G') AND A.SHRQ BETWEEN TO_DATE('{0}','yyyy-mm-dd') AND TO_DATE('{1}','yyyy-mm-dd')";
string strWhere = "";
if (!PubFunc.StrIsEmpty(lisBILL.Text.Trim()))
{
strWhere += " AND A.SEQNO LIKE '%" + lisBILL.Text.Trim() + "%'";
}
if (!PubFunc.StrIsEmpty(ddlFLAG.SelectedValue.Trim()))
{
if (ddlFLAG.SelectedValue == "N")
{
strWhere += " AND NVL(B.SEQNO,'#') = '#'";
}
if (ddlFLAG.SelectedValue == "Y")
{
strWhere += " AND NVL(B.SEQNO,'#') <>'#'";
}
}
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += " order by SHRQ DESC";
GridList.DataSource = DbHelperOra.QueryForTable(string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text));
GridList.DataBind();
}
public string shopaddress(HttpContext context)
{
string fuserid = context.Request["fuserid"] == null ? "" : context.Request["fuserid"].ToString(); //获取参数
string faddress = context.Request["faddress"] == null ? "" : context.Request["faddress"].ToString(); //获取参数
string strResult = "";
DataTable dt;
if (string.IsNullOrEmpty(faddress))
{
string sql = @"SELECT A.FUSERNAME,A.FTEL,A.FADDRESS,A.FSEQNO
FROM DOC_ADDRESS A
WHERE A.FUSERID = '{0}' AND rownum=1";
dt = DbHelperOra.QueryForTable(string.Format(sql, fuserid));
}
else
{
string sql = @"SELECT A.FUSERNAME,A.FTEL,A.FADDRESS,A.FSEQNO
FROM DOC_ADDRESS A
WHERE A.FUSERID = '{0}' AND FSEQNO = {1}";
dt = DbHelperOra.QueryForTable(string.Format(sql, fuserid, faddress));
}
if (dt.Rows.Count > 0)
{
strResult = new JObject(
new JProperty("res", "1"),
new JProperty("msg", JsonConvert.SerializeObject(dt))
).ToString();
}
else
{
strResult = new JObject(
new JProperty("res", "0"),
new JProperty("msg", "系统错误")
).ToString();
}
return(strResult);
}
protected void btnQuery_Click(object sender, EventArgs e)
{
int total = 0;
DataTable dtData = DbHelperOra.QueryForTable(GetQuerySql());
GridSum.RecordCount = total;
GridSum.DataSource = dtData;
GridSum.DataBind();
//计算合计数量
if (dtData != null && dtData.Rows.Count > 0)
{
decimal QCKCJFJE = 0, QCKCFJFJE = 0, JFSHJE = 0, FJFSHJE = 0, JFXHJE = 0, FJFXHJE = 0, QMKCJFJE = 0, QMKCFJFJE = 0;
foreach (DataRow row in dtData.Rows)
{
QCKCJFJE += Convert.ToDecimal(row["QCKCJFJE"] ?? "0");
QCKCFJFJE += Convert.ToDecimal(row["QCKCFJFJE"] ?? "0");
JFSHJE += Convert.ToDecimal(row["JFSHJE"] ?? "0");
FJFSHJE += Convert.ToDecimal(row["FJFSHJE"] ?? "0");
JFXHJE += Convert.ToDecimal(row["JFXHJE"] ?? "0");
FJFXHJE += Convert.ToDecimal(row["FJFXHJE"] ?? "0");
QMKCJFJE += Convert.ToDecimal(row["QMKCJFJE"] ?? "0");
QMKCFJFJE += Convert.ToDecimal(row["QMKCFJFJE"] ?? "0");
}
JObject summary = new JObject();
summary.Add("DEPTNAME", "本页合计");
summary.Add("QCKCJFJE", QCKCJFJE.ToString("F2"));
summary.Add("QCKCFJFJE", QCKCFJFJE.ToString("F2"));
summary.Add("JFSHJE", JFSHJE.ToString("F2"));
summary.Add("FJFSHJE", FJFSHJE.ToString("F2"));
summary.Add("JFXHJE", JFXHJE.ToString("F2"));
summary.Add("FJFXHJE", FJFXHJE.ToString("F2"));
summary.Add("QMKCJFJE", QMKCJFJE.ToString("F2"));
summary.Add("QMKCFJFJE", QMKCFJFJE.ToString("F2"));
GridSum.SummaryData = summary;
}
}