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();
}