protected override void billClear() { PubFunc.FormDataClear(Formlist); //默认为威高 ddlsup.SelectedValue = PubFunc.DbGetPara("SUPPLIER");; //lstLRRQ1.SelectedDate = DateTime.Now; lstLRRQ2.SelectedDate = DateTime.Now; }
private void DataInit() { if (Request.QueryString["oper"].ToString() == "ck" || Request.QueryString["oper"].ToString() == "xs") { PubFunc.DdlDataGet("DDL_SYS_DEPT", lstDEPTOUT, ddlDEPTID); } else { PubFunc.DdlDataGet("DDL_SYS_DEPOT", lstDEPTOUT, ddlDEPTID); } PubFunc.DdlDataGet("DDL_USER", lstJSY); PubFunc.DdlDataGet("DDL_DOC_SHS", ddlsup, ddlSUPID); PubFunc.FormLock(Formlis, true); //lstLRRQ1默认为上次结算日期 //lstLRRQ1.SelectedDate = DateTime.Now; lstLRRQ2.SelectedDate = DateTime.Now; //默认为威高 ddlsup.SelectedValue = PubFunc.DbGetPara("SUPPLIER"); lstJSY.SelectedValue = UserAction.UserID; }
protected void btnIns_Click(object sender, EventArgs e) { //if (PubFunc.StrIsEmpty(ddlHis.SelectedValue)) //{ // Alert.Show("请选择需导入的医院!"); // return; //} if (this.fuDocument.HasFile) { string toFilePath = "~/ERPUpload/DownloadExcel/"; //获得文件扩展名 string fileNameExt = Path.GetExtension(this.fuDocument.FileName).ToLower(); if (!ValidateFileType(fileNameExt)) { Alert.Show("无效的文件类型!", "消息提示", MessageBoxIcon.Warning); return; } //验证合法的文件 if (CheckFileExt(fileNameExt)) { //生成将要保存的随机文件名 string fileName = this.fuDocument.FileName.Substring(0, this.fuDocument.FileName.IndexOf(".")) + DateTime.Now.ToString("yyyyMMddHHmmss") + fileNameExt; //按日期归类保存 string datePath = DateTime.Now.ToString("yyyyMM") + "/" + DateTime.Now.ToString("dd") + "/"; toFilePath += datePath; //获得要保存的文件路径 string DownloadUrl = toFilePath + fileName; //物理完整路径 string toFileFullPath = HttpContext.Current.Server.MapPath(toFilePath); //检查是否有该路径,没有就创建 if (!Directory.Exists(toFileFullPath)) { Directory.CreateDirectory(toFileFullPath); } //将要保存的完整物理文件名 string serverFileName = toFileFullPath + fileName; //获取保存的excel路径 this.fuDocument.SaveAs(serverFileName); if (File.Exists(serverFileName)) { DataTable table = new DataTable(); if (fileNameExt == ".xlsx") { table = ExcelHelper.ImportExcelxtoDt(serverFileName, 0, 1); //导入excel2007 } else { table = ExcelHelper.ImportExceltoDt(serverFileName, 0, 1);//导入excel2003 } //清空匹配表 DbHelperOra.ExecuteSql("TRUNCATE TABLE DAT_UPLOAD_GOODS"); if (table != null && table.Rows.Count > 0) { string sql = @"INSERT INTO DAT_UPLOAD_GOODS(SEQNO,CUSTID,HISCODE,HISNAME,HISSPEC,HISUNIT,HSJJ,PRODUCER,PIZNO,GDSEQ,BAR3) VALUES(:SEQNO,:CUSTID,:HISCODE,:HISNAME,:HISSPEC,:HISUNIT,:HSJJ,:PRODUCER,:PIZNO,:GDSEQ,:BAR3)"; OracleConnection con = new OracleConnection(DbHelperOra.connectionString); OracleDataAdapter da = new OracleDataAdapter(sql, con); //在批量添加数据前的准备工作 da.InsertCommand = new OracleCommand(sql, con); OracleParameter param = new OracleParameter(); OracleParameter para = new OracleParameter("SEQNO", OracleDbType.Varchar2, 20); //tgbExlBill.Text = BillSeqGet(); //固定单号 para.Value = "PPD001"; param = da.InsertCommand.Parameters.Add(para); para = new OracleParameter("CUSTID", OracleDbType.Varchar2, 20); para.Value = PubFunc.DbGetPara("USERCODE"); param = da.InsertCommand.Parameters.Add(para); param = da.InsertCommand.Parameters.Add(new OracleParameter("HISCODE", OracleDbType.Varchar2, 50)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "HIS编码"; param = da.InsertCommand.Parameters.Add(new OracleParameter("HISNAME", OracleDbType.Varchar2, 200)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "商品名称"; param = da.InsertCommand.Parameters.Add(new OracleParameter("HISSPEC ", OracleDbType.Varchar2, 100)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "规格"; param = da.InsertCommand.Parameters.Add(new OracleParameter("HISUNIT", OracleDbType.Varchar2, 15)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "单位"; param = da.InsertCommand.Parameters.Add(new OracleParameter("HSJJ", OracleDbType.Decimal, 15)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "价格"; param = da.InsertCommand.Parameters.Add(new OracleParameter("PRODUCER", OracleDbType.Varchar2, 200)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "生产厂家"; param = da.InsertCommand.Parameters.Add(new OracleParameter("PIZNO", OracleDbType.Varchar2, 100)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "注册证号"; param = da.InsertCommand.Parameters.Add(new OracleParameter("GDSEQ", OracleDbType.Varchar2, 20)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "ERP编码"; param = da.InsertCommand.Parameters.Add(new OracleParameter("BAR3", OracleDbType.Varchar2, 20)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "ERP编码"; //param = da.InsertCommand.Parameters.Add(new OracleParameter("SL", OracleDbType.Decimal, 16)); //param.SourceVersion = DataRowVersion.Current; //param.SourceColumn = "数量"; //param = da.InsertCommand.Parameters.Add(new OracleParameter("YWRQ", OracleDbType.Varchar2, 20)); //param.SourceVersion = DataRowVersion.Current; //param.SourceColumn = "业务日期"; //批量添加数据 try { con.Open(); da.Update(table); //处理导入数据的准确性 OracleParameter[] parameters = new OracleParameter[] { new OracleParameter("V_BILL", OracleDbType.Varchar2), new OracleParameter("V_TYPE", OracleDbType.Varchar2), }; parameters[0].Value = tgbExlBill.Text; parameters[1].Value = '1'; DbHelperOra.RunProcedure("P_ExlGOODS", parameters); string allnum = DbHelperOra.GetSingle("SELECT COUNT(1) FROM DAT_UPLOAD_GOODS WHERE SEQNO = '" + tgbExlBill.Text + "' AND FLAG IN('N','Y')").ToString(); string Delnum = DbHelperOra.GetSingle("SELECT COUNT(1) FROM DAT_UPLOAD_GOODS WHERE SEQNO = '" + tgbExlBill.Text + "' AND FLAG ='C'").ToString(); if (Delnum == "0") { Alert.Show("Excel已匹配编码信息已自动导入匹配表中!\r\n共导入[" + allnum + "]条数据!", "消息提示", MessageBoxIcon.Information); } else { Alert.Show("[" + Delnum + "]条记录因重复或未填写商品信息未导入,成功导入[" + allnum + "]条数据!", "消息提示", MessageBoxIcon.Information); } SearchOne(); } catch (Exception ex) { Alert.Show("数据库错误:" + ex.Message.ToString(), "异常信息", MessageBoxIcon.Warning); } finally { con.Close(); } } File.Delete(serverFileName); } } } else { Alert.Show("请选择excel文件!"); } }
private string getGoodsData(string gdseq) { DataTable picTable = DbHelperOra.Query(String.Format("select hanghao,picpath from (select rownum hanghao,picpath from doc_goodspicture where gdseq='{0}' and nvl(str2,'N') = 'N' order by rownum)", gdseq)).Tables[0]; string strGoodsSql = @"SELECT GDSEQ,"; //判断系统默认设置显示的商品名 if (PubFunc.DbGetPara("ShowName") == "HIS") { strGoodsSql += "NVL(HISNAME,GDNAME) GDNAME,NVL(G.STR3,GDSPEC) GDSPEC,"; } else { strGoodsSql += "GDNAME,GDSPEC,"; } strGoodsSql += @"BZHL,UNIT,U.NAME UNITNAME,CATID,C.NAME CATNAME,PIZNO,BAR3 EASCODE,SUPPLIER SUPID,S.SUPNAME SUPNAME,PRODUCER,f_getproducername(PRODUCER) PRODUCERNAME,JXTAX,HSJJ JJ FROM DOC_GOODS G LEFT JOIN DOC_GOODSUNIT U ON G.UNIT=U.CODE LEFT JOIN SYS_CATEGORY C ON G.CATID=C.CODE LEFT JOIN DOC_SUPPLIER S ON G.SUPPLIER=S.SUPID LEFT JOIN DOC_SUPPLIER P ON G.PRODUCER=P.STR2 WHERE GDSEQ='{0}'"; DataTable goodsInfo = DbHelperOra.Query(String.Format(strGoodsSql, gdseq)).Tables[0]; DataTable cartTable = DbHelperOra.Query(String.Format("select dhs from dat_cart where userid='{0}' and gdseq='{1}'", UserAction.UserID, gdseq)).Tables[0]; JObject jo = new JObject(); JArray ja = new JArray(); jo.Add("gdseq", goodsInfo.Rows[0]["GDSEQ"].ToString()); jo.Add("gdname", goodsInfo.Rows[0]["GDNAME"].ToString()); jo.Add("gdspec", goodsInfo.Rows[0]["GDSPEC"].ToString()); jo.Add("bzhl", goodsInfo.Rows[0]["BZHL"].ToString()); jo.Add("unit", goodsInfo.Rows[0]["UNIT"].ToString()); jo.Add("unitname", goodsInfo.Rows[0]["UNITNAME"].ToString()); jo.Add("catid", goodsInfo.Rows[0]["CATID"].ToString()); jo.Add("catname", goodsInfo.Rows[0]["CATNAME"].ToString()); jo.Add("eascode", goodsInfo.Rows[0]["EASCODE"].ToString()); jo.Add("pizno", goodsInfo.Rows[0]["PIZNO"].ToString()); jo.Add("supid", goodsInfo.Rows[0]["SUPID"].ToString()); jo.Add("supname", goodsInfo.Rows[0]["SUPNAME"].ToString()); jo.Add("producer", goodsInfo.Rows[0]["PRODUCER"].ToString()); jo.Add("producername", goodsInfo.Rows[0]["PRODUCERNAME"].ToString()); jo.Add("jxtax", goodsInfo.Rows[0]["JXTAX"].ToString()); jo.Add("jj", goodsInfo.Rows[0]["JJ"].ToString()); foreach (DataRow dr in picTable.Rows) { JObject joPath = new JObject(); joPath.Add("rowno", Convert.ToInt16(dr["HANGHAO"])); if (dr["PICPATH"] != null && !"".Equals(dr["PICPATH"].ToString())) { //string strPICPATH = ApiUtil.GetConfigCont("CLOUD_URL_PREFIX") + dr["PICPATH"].ToString().Substring(2); string strPICPATH = ""; string path = dr["PICPATH"].ToString(); if (!string.IsNullOrWhiteSpace(path)) { if (File.Exists(Request.PhysicalApplicationPath + (path.Substring(2)).Replace("/", @"\"))) { strPICPATH = "http://" + Request.Url.Authority + path.Substring(1); } else { strPICPATH = ApiUtil.GetConfigCont("PIC_PATH") + "/captcha/GetPictures.aspx?picpath=" + path; } } else { strPICPATH = "/res/images/noPic.jpg"; } joPath.Add("path", strPICPATH); } ja.Add(joPath); } jo.Add("picpath", ja); if (cartTable != null && cartTable.Rows.Count > 0) { jo.Add("dhs", cartTable.Rows[0]["dhs"].ToString()); } return(JsonConvert.SerializeObject(jo)); }
private void goodsDataBind() { string strSqlGoodSum = @"SELECT sum(c.dhs) dhs,sum((g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) * c.dhs )) as JE,COUNT(1) DPS FROM DAT_CART c left join doc_goods g on c.gdseq=g.gdseq where c.USERID='{0}'"; DataTable dt = DbHelperOra.Query(string.Format(strSqlGoodSum, UserAction.UserID)).Tables[0]; if (dt != null && dt.Rows.Count > 0) { this.LiteralDhs.Text = dt.Rows[0]["DPS"].ToString(); this.LiteralJe.Text = dt.Rows[0]["JE"].ToString(); } string strSqlGood = @"SELECT c.seqno,c.userid,c.gdseq,c.dhs,g.GDSEQ,"; //判断系统默认设置显示的商品名 if (PubFunc.DbGetPara("ShowName") == "HIS") { strSqlGood += "NVL(g.HISNAME,g.GDNAME) GDNAME,NVL(g.STR3,g.GDSPEC) GDSPEC,"; } else { strSqlGood += "g.GDNAME,g.GDSPEC,"; } strSqlGood += @"g.isflag5,g.BZHL,f_getunitname(g.UNIT) UNIT,g.CATID,g.PIZNO,c.deptid,f_getproducername(g.producer) producername, --g.hsjj as price, --(g.hsjj)*c.dhs as JE, p.picpath, g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) as price, (g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) * c.dhs ) JE, F_GETUNITNAME(DECODE(g.UNIT_SELL, 'D', g.UNIT_DABZ, 'Z', g.UNIT_ZHONGBZ, g.UNIT)) UNIT_SELL_NAME, DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL) BZHL_SELL FROM DAT_CART c left join doc_goods g on c.gdseq=g.gdseq left join (select a.*,Row_Number() OVER (partition by gdseq ORDER BY rownum ) HANGHAO from doc_goodspicture a) p on c.gdseq=p.gdseq and p.HANGHAO=1 and p.flag='Y'and nvl(p.str2,'N') = 'N' where c.USERID='{0}'"; strSqlGood = string.Format(strSqlGood, UserAction.UserID); DataTable dtInfo = DbHelperOra.Query(strSqlGood).Tables[0]; DataTable dtnew = dtInfo.Clone(); foreach (DataRow row in dtInfo.Rows) { string path = row["PICPATH"].ToString(); if (!string.IsNullOrWhiteSpace(path)) { if (File.Exists(Request.PhysicalApplicationPath + (path.Substring(2)).Replace("/", @"\"))) { row["PICPATH"] = "http://" + Request.Url.Authority + path.Substring(1); } else { row["PICPATH"] = ApiUtil.GetConfigCont("PIC_PATH") + "/captcha/GetPictures.aspx?picpath=" + path; } } else { row["PICPATH"] = "/res/images/noPic.jpg"; } dtnew.Rows.Add(row.ItemArray); } RepeaterGoods.DataSource = dtnew; RepeaterGoods.DataBind(); }
private void goodsDataBind() { strNextPage = "?page=" + (intPageIndex + 1).ToString(); strLastPage = "?page=" + (intPageIndex - 1).ToString(); string strWhere = ""; if (DbHelperOra.Exists("SELECT 1 FROM SYS_DEPT WHERE TYPE IN ('3','4') AND CODE = '" + UserAction.UserDept + "'")) { Object obj; if (HttpContext.Current.Request.Cookies["DEPTOUT"] == null) { obj = DbHelperOra.GetSingle("select nvl((SELECT A.STOCK FROM SYS_DEPT A WHERE A.CODE = '" + UserAction.UserDept + "'),(select value from sys_para where code = 'DEFDEPT')) from dual"); } else { obj = HttpContext.Current.Request.Cookies["DEPTOUT"].Value; } strWhere = " GDSEQ IN(SELECT A.GDSEQ FROM DOC_GOODSCFG A,DOC_GOODSCFG B ,doc_goods C WHERE A.GDSEQ = B.GDSEQ AND A.GDSEQ=C.GDSEQ AND C.FLAG='Y' AND A.ISCFG IN ('Y','1') AND A.DEPTID = '" + UserAction.UserDept + "' AND B.DEPTID = '" + obj.ToString() + "') "; } else { strWhere = " 1=2"; } if (Request.QueryString["k"] != null && !"".Equals(Request.QueryString["k"])) { strWhere += " and (gdname like '%" + Request.QueryString["k"].Trim() + "%' OR GDSEQ like '%" + Request.QueryString["k"].Trim() + "%' OR ZJM like '%" + Request.QueryString["k"].Trim().ToUpper() + "%')"; strNextPage = strNextPage + "&k=" + Request.QueryString["k"].Trim(); strLastPage = strLastPage + "&k=" + Request.QueryString["k"].Trim(); } if (Request.QueryString["catid"] != null && !"".Equals(Request.QueryString["catid"])) { strNextPage = strNextPage + "&catid=" + Request.QueryString["catid"].Trim(); strLastPage = strLastPage + "&catid=" + Request.QueryString["catid"].Trim(); string catIds = Request.QueryString["catid"].Trim(','); if (!String.IsNullOrWhiteSpace(catIds)) { if (catIds.IndexOf(",") > 0) { string[] catIdsArray = catIds.Split(','); catIds = ""; foreach (string catId in catIdsArray) { catIds += "'" + catId + "',"; } catIds = catIds.Trim(','); } else { catIds = "'" + catIds + "'"; } strWhere += " and CATID0 IN (" + catIds + ")"; } } //取得有效行 object rowno = DbHelperOra.GetSingle("SELECT MIN(HANGHAO) FROM ( SELECT ROWNUM HANGHAO FROM doc_goodspicture WHERE flag='Y' and nvl(str2,'N') = 'N' ORDER BY ROWNUM)"); int pageNum = AspNetPager1.PageSize; string strSqlCount = @"select count(1) from (select g.GDSEQ,g.GDNAME,g.GDSPEC,g.BZHL,g.UNIT,g.CATID0,g.PIZNO,p.picpath,ZJM from doc_goods g left join (SELECT GDSEQ, MAX(PICPATH) PICPATH FROM DOC_GOODSPICTURE WHERE FLAG = 'Y' AND NVL(STR2, 'N') = 'N' GROUP BY GDSEQ) p on g.gdseq=p.gdseq and rownum = 1 where g.flag = 'Y') where {0}"; strSqlCount = string.Format(strSqlCount, strWhere); AspNetPager1.RecordCount = Convert.ToInt32(DbHelperOra.GetSingle(strSqlCount)); this.LiteralNum.Text = AspNetPager1.RecordCount.ToString(); this.LiteralPageNum.Text = (Math.Ceiling(AspNetPager1.RecordCount / 10.0)).ToString(); if (AspNetPager1.RecordCount >= 0 && AspNetPager1.RecordCount < 10) { strLastPage = "<a title='上一页' href='javascript:void(0);' class='sort_page_arrow'><</a>"; strNextPage = "<a hreCeilingf='javascript:void(0);' title='下一页' class='sort_page_arrow'>></a>"; } else { if (intPageIndex == 1) { strLastPage = "<a title='上一页' href='javascript:void(0);' class='sort_page_arrow'><</a>"; strNextPage = "<a href='" + strNextPage + "' title='下一页' class='sort_page_arrow'>></a>"; } else if (intPageIndex == (AspNetPager1.RecordCount / pageNum + 1)) { strLastPage = "<a title='上一页' href='" + strLastPage + "' class='sort_page_arrow'><</a>"; strNextPage = "<a href='javascript:void(0);' title='下一页' class='sort_page_arrow'>></a>"; } else { strLastPage = "<a title='上一页' href='" + strLastPage + "' class='sort_page_arrow'><</a>"; strNextPage = "<a href='" + strNextPage + "' title='下一页' class='sort_page_arrow'>></a>"; } } this.LiteralLastPage.Text = strLastPage; this.LiteralNextPage.Text = strNextPage; string strSqlGood = @"select * from (select t.*,ROWNUM rn from (select GDSEQ,GDNAME,GDSPEC,BZHL,UNIT,UNITNAME,CATID0,PIZNO,HSJJ,producername,picpath,flag,isflag5 from (select g.GDSEQ,"; //判断系统默认设置显示的商品名 if (PubFunc.DbGetPara("ShowName") == "HIS") { strSqlGood += "NVL(g.HISNAME,g.GDNAME) GDNAME,NVL(g.STR3,g.GDSPEC) GDSPEC,"; } else { strSqlGood += "g.GDNAME,g.GDSPEC,"; } strSqlGood += @"g.isflag5,g.BZHL, --g.UNIT, DECODE(g.UNIT_SELL, 'D', g.UNIT_DABZ, 'Z', g.UNIT_ZHONGBZ, g.UNIT) UNIT, --u.NAME UNITNAME, F_GETUNITNAME(DECODE(g.UNIT_SELL, 'D', g.UNIT_DABZ, 'Z', g.UNIT_ZHONGBZ, g.UNIT)) UNITNAME, g.CATID0, g.PIZNO, --g.HSJJ, g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) as HSJJ, f_getproducername(g.producer) producername, p.picpath,ZJM,g.flag from doc_goods g left join (SELECT GDSEQ, MAX(PICPATH)PICPATH FROM DOC_GOODSPICTURE WHERE FLAG = 'Y' AND NVL(STR2, 'N') = 'N' GROUP BY GDSEQ) p on g.gdseq = p.gdseq left join doc_goodsunit u on g.unit = u.code) where {0}) t where t.flag = 'Y') where rn> " + (intPageIndex - 1) * pageNum + " and rn<= " + intPageIndex * pageNum; strSqlGood = string.Format(strSqlGood, strWhere); DataTable dt = DbHelperOra.Query(strSqlGood).Tables[0]; DataTable dtnew = dt.Clone(); foreach (DataRow row in dt.Rows) { string path = row["PICPATH"].ToString(); if (!string.IsNullOrWhiteSpace(path)) { if (File.Exists(Request.PhysicalApplicationPath + (path.Substring(2)).Replace("/", @"\"))) { row["PICPATH"] = "http://" + Request.Url.Authority + path.Substring(1); } else { row["PICPATH"] = ApiUtil.GetConfigCont("PIC_PATH") + "/captcha/GetPictures.aspx?picpath=" + path; } } else { row["PICPATH"] = "/res/images/noPic.jpg"; } dtnew.Rows.Add(row.ItemArray); } RepeaterGoods.DataSource = dtnew; RepeaterGoods.DataBind(); }