611 lines
27 KiB
C#
611 lines
27 KiB
C#
using System;
|
||
using System.Data;
|
||
using System.Configuration;
|
||
using System.Collections;
|
||
using System.Web;
|
||
using System.Web.Security;
|
||
using System.Web.UI;
|
||
using System.Web.UI.WebControls;
|
||
using System.Web.UI.WebControls.WebParts;
|
||
using System.Web.UI.HtmlControls;
|
||
using GThinking.N_MISPRO;
|
||
using GThinking.N_MODEL.Page;
|
||
using GThinking.N_MISPRO.Data;
|
||
using GThinking.N_PUBLIC;
|
||
using System.Text;
|
||
using GThinking.N_MISPRO.Utility;
|
||
using GThinking.N_CONTROL;
|
||
using GThinking.N_COMPRESS;
|
||
using System.Collections.Generic;
|
||
using GThinking.N_MISPRO.TableCache;
|
||
using System.Data.OleDb;
|
||
using System.IO;
|
||
using System.Web.UI.MobileControls;
|
||
using System.Xml;
|
||
using GThinking.N_PUBLIC2;
|
||
using GThinking.N_WORKFLOW;
|
||
using GThinking.N_MISPROAS.Services;
|
||
using GThinking.N_MISPRO.MISPROAS;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
public partial class XLXSJGBBDList : SingleGridBase
|
||
{
|
||
XLXSJGBBD entity = new XLXSJGBBD();
|
||
XLXSJGBBD_M queryEntity = new XLXSJGBBD_M();
|
||
|
||
#region 页面初始化
|
||
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
this.UMID = 100860091;
|
||
this.UMName = "线缆销售报价比对";
|
||
this.CurMaster = Master;
|
||
this.ModuleGV = ContentGrid;
|
||
this.CurEntity = entity;
|
||
this.CurQueryEntity = queryEntity;
|
||
sQueryURLTarget = "XLXSJGBBDQuery.aspx";
|
||
this.PageHeight = 600;
|
||
this.PageWidth = 750;
|
||
base.DataKeyNames = new string[] { "XLXSJGBMX_JGBID", "XLXSJGBMX_XH" };
|
||
|
||
this.Master.EnableNewBtn = false;
|
||
this.Master.EnableCopyBtn = false;
|
||
this.Master.EnableEditBtn = false;
|
||
this.Master.EnableDeleteBtn = false;
|
||
this.Master.EnableViewBtn = false;
|
||
if (!IsPostBack)
|
||
{
|
||
//查询条件赋初值
|
||
if (!string.IsNullOrEmpty(PageParam.GetRTrimString("MXBH")))
|
||
CurQueryEntity.FieldByName("MXBH").Value = PageParam.GetRTrimString("MXBH");
|
||
CurQueryEntity.FieldByName("PXFS").Value = "序号";
|
||
CurQueryEntity.FieldByName("BDJGLX").Value = "HSBJ";
|
||
if(!string.IsNullOrEmpty(PageParam.GetRTrimString("JGBID")))
|
||
CurQueryEntity.FieldByName("YSJGB").Value = PageParam.GetRTrimString("JGBID");
|
||
CurQueryEntity.FieldByName("BDGS").Value = "1";
|
||
}
|
||
|
||
base.InitPage();
|
||
|
||
this.ModuleGV.OnClientDblClick = "return false;";
|
||
|
||
InitGridView();
|
||
if (!IsPostBack)
|
||
{
|
||
if (base.IsDefaultExecute)
|
||
Master_OnStandardQuery(null, null);
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 模板继承事件
|
||
|
||
protected override void Master_OnStandardQuery(object sender, EventArgs e)
|
||
{
|
||
base.InitEntityByCondition();
|
||
entity.EntityTable.QuerySelSql = GetQuerySql();
|
||
base.Master_OnStandardQuery(sender, e);
|
||
entity.GridDataBind(ContentGrid, tSession);
|
||
}
|
||
|
||
protected override void moduleGV_RowDataBound(object sender, GridViewRowEventArgs e)
|
||
{
|
||
if (e.Row.RowType == DataControlRowType.DataRow)
|
||
{
|
||
}
|
||
base.moduleGV_RowDataBound(sender, e);
|
||
}
|
||
|
||
protected override void moduleGV_PageIndexChanging(object sender, GridViewPageEventArgs e)
|
||
{
|
||
base.InitEntityByCondition();
|
||
entity.EntityTable.QuerySelSql = GetQuerySql();
|
||
ContentGrid.SelectedIndex = -1;
|
||
base.moduleGV_PageIndexChanging(sender, e);
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 自定义函数
|
||
|
||
private void InitGridView()
|
||
{
|
||
base.InitEntityByCondition();
|
||
if (string.IsNullOrEmpty(CurQueryEntity.FieldByName("BDJGLX").Value.ToString().Trim()))
|
||
{
|
||
return;
|
||
}
|
||
int iXSWS = string.IsNullOrEmpty(GetOption("BJU002")) ? 5 : CMISproConvert.StrToInt(GetOption("BJU002"));
|
||
if (iXSWS < 0)
|
||
iXSWS = 0;
|
||
else if (iXSWS > 8)
|
||
iXSWS = 8;
|
||
string sBDJGLX = CurQueryEntity.FieldByName("BDJGLX").Value.ToString().Trim();
|
||
switch (sBDJGLX)
|
||
{
|
||
case "HSJJ":
|
||
AddColumn("HSJJ", "含税基价（原始）", iXSWS, 120);
|
||
AddColumn("XLXSJGBMX_HSJJ", "含税基价（比对）", iXSWS, 120);
|
||
break;
|
||
case "WSJJ":
|
||
AddColumn("WSJJ", "无税基价（原始）", iXSWS, 120);
|
||
AddColumn("XLXSJGBMX_WSJJ", "无税基价（比对）", iXSWS, 120);
|
||
break;
|
||
case "HSBJ":
|
||
AddColumn("HSBJ", "含税表价（原始）", iXSWS, 120);
|
||
AddColumn("XLXSJGBMX_HSBJ", "含税表价（比对）", iXSWS, 120);
|
||
break;
|
||
case "WSBJ":
|
||
AddColumn("WSBJ", "无税表价（原始）", iXSWS, 120);
|
||
AddColumn("XLXSJGBMX_WSBJ", "无税表价（比对）", iXSWS, 120);
|
||
break;
|
||
|
||
}
|
||
if(CurQueryEntity.FieldByName("BDGS").Value.Trim()=="1")
|
||
AddColumn("BDJG", "比对结果", 6, 80);
|
||
else
|
||
AddColumn("BDJG", "比对结果(%)", 2, 80);
|
||
|
||
}
|
||
|
||
private string GetQuerySql()
|
||
{
|
||
string sBDJGLX = CurQueryEntity.FieldByName("BDJGLX").Value.Trim();
|
||
string sBDGS = CurQueryEntity.FieldByName("BDGS").Value.Trim();
|
||
string sSQL = "select rtrim(B.XLXSJGBMX_JGBID)||'|'|| C.XLXSJGB_JGBMC as JGBID,B.XLXSJGBMX_XH as JGBXH,rtrim(A.XLXSJGBMX_JGBID)||'|'||D.XLXSJGB_JGBMC as XLXSJGBMX_JGBID,A.XLXSJGBMX_XH,";
|
||
sSQL += " A.XLXSJGBMX_XLXHID, A.XLXSJGBMX_XLGGID,rtrim(A.XLXSJGBMX_XLBZID)||'|'||XLBZ_XLBZLXMC as XLBZ,A.XLXSJGBMX_DYDJID,A.XLXSJGBMX_JLDW,";
|
||
sSQL += GetSQLByGSLX(sBDGS, sBDJGLX);
|
||
sSQL += " from XLXSJGBMX A";
|
||
sSQL += " left join XLXSJGBMX B ";
|
||
sSQL += " on A.XLXSJGBMX_XLXHID=B.XLXSJGBMX_XLXHID and A.XLXSJGBMX_XLGGID=B.XLXSJGBMX_XLGGID and A.XLXSJGBMX_XLBZID=B.XLXSJGBMX_XLBZID ";
|
||
sSQL += " and A.XLXSJGBMX_DYDJID=B.XLXSJGBMX_DYDJID and A.XLXSJGBMX_JLDW=B.XLXSJGBMX_JLDW ";
|
||
sSQL += " left join XLXSJGB C on C.XLXSJGB_JGBID = B.XLXSJGBMX_JGBID";
|
||
sSQL += " left join XLXSJGB D on D.XLXSJGB_JGBID = A.XLXSJGBMX_JGBID";
|
||
sSQL += " left join XLBZ on XLBZ_XLBZID=A.XLXSJGBMX_XLBZID";
|
||
sSQL += " where 1=1 ";
|
||
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("YSJGB").Value.Trim()))
|
||
{
|
||
sSQL += " and B.XLXSJGBMX_JGBID=:pYSJGB";
|
||
entity.Param.Add("pYSJGB", CurQueryEntity.FieldByName("YSJGB").Value.Trim());
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("BDJGB").Value.Trim()))
|
||
{
|
||
sSQL += " and A.XLXSJGBMX_JGBID =:pBDJGB";
|
||
entity.Param.Add("pBDJGB", CurQueryEntity.FieldByName("BDJGB").Value.Trim());
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("MXBH").Value.Trim()))
|
||
{
|
||
sSQL += " and C.XLXSJGB_MXBH=:pMXBH";
|
||
sSQL += " and D.XLXSJGB_MXBH=:pMXBH";
|
||
entity.Param.Add("pMXBH", CurQueryEntity.FieldByName("MXBH").Value.Trim());
|
||
}
|
||
//型号模糊
|
||
if (!CurQueryEntity.FieldByName("XLXHIDJQPP").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLXHID").Value.Trim()))
|
||
{
|
||
sSQL += " \r\n and A.XLXSJGBMX_XLXHID like :pXLXHID ";
|
||
entity.Param.Add("pXLXHID", "%" + CurQueryEntity.FieldByName("XLXHID").Value.Trim() + "%");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string sXLXHID = CurQueryEntity.FieldByName("XLXHID").Value.Trim();
|
||
if (!string.IsNullOrEmpty(sXLXHID))
|
||
{
|
||
string[] sTemp = sXLXHID.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
|
||
if (sTemp.Length >= 2)
|
||
{
|
||
sSQL+=" \r\n and " + CMISproDataBase.GetWhereEqual("A.XLXSJGBMX_XLXHID", sXLXHID);
|
||
}
|
||
else
|
||
{
|
||
sSQL+=" \r\n and A.XLXSJGBMX_XLXHID = :pXLXHID ";
|
||
entity.Param.Add("pXLXHID", sXLXHID);
|
||
}
|
||
}
|
||
}
|
||
//规格模糊
|
||
if (!CurQueryEntity.FieldByName("XLGGIDJQPP").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLGGID").Value.Trim()))
|
||
{
|
||
sSQL+=" and replace(A.XLXSJGBMX_XLGGID,'*','×') like :pXLGGID ";
|
||
entity.Param.Add("pXLGGID", "%" + CurQueryEntity.FieldByName("XLGGID").Value.Trim().Replace("*", "×") + "%");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string sXLGGID = CurQueryEntity.FieldByName("XLGGID").Value.Trim();
|
||
if (!string.IsNullOrEmpty(sXLGGID))
|
||
{
|
||
string[] sTemp = sXLGGID.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
|
||
if (sTemp.Length >= 2)
|
||
{
|
||
sSQL+=" and " + CMISproDataBase.GetWhereEqual(" replace(A.XLXSJGBMX_XLGGID,'*','×')", sXLGGID.Replace('*', '×'));
|
||
}
|
||
else
|
||
{
|
||
sSQL+=" and replace(A.XLXSJGBMX_XLGGID,'*','×') = :pXLGGID ";
|
||
entity.Param.Add("pXLGGID", CurQueryEntity.FieldByName("XLGGID").Value.Trim().Replace("*", "×"));
|
||
}
|
||
}
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("ST").Value.Trim()))
|
||
{
|
||
sSQL+=" and A.XLXSJGBMX_XLBZID = :pST ";
|
||
entity.Param.Add("pST", CurQueryEntity.FieldByName("ST").Value.Trim());
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("DYDJ").Value.Trim()))
|
||
{
|
||
sSQL+=" and A.XLXSJGBMX_DYDJID = :pDYDJ ";
|
||
entity.Param.Add("pDYDJ", CurQueryEntity.FieldByName("DYDJ").Value.Trim());
|
||
}
|
||
if (CurQueryEntity.FieldByName("PXFS").Value.Trim().Equals("型号+标准+规格序号+电压等级"))
|
||
{
|
||
sSQL += " order by B.XLXSJGBMX_JGBID,A.XLXSJGBMX_XLXHID,A.XLXSJGBMX_XLBZID,minXH,A.XLXSJGBMX_DYDJID ";
|
||
}else
|
||
sSQL += " order by B.XLXSJGBMX_JGBID,b.XLXSJGBMX_XH";
|
||
return sSQL;
|
||
}
|
||
|
||
private string GetSQLByGSLX(string sBDGS,string sBDJGLX)
|
||
{
|
||
string sBDGSSql = "";
|
||
|
||
string sBDJGLXName = GetBDJGLXName(sBDJGLX);
|
||
|
||
if (sBDGS == "1")
|
||
sBDGSSql += string.Format(" B.{0} as {1},A.{0} ,case when B.{0} <> 0 then ROUND(A.{0}/B.{0},6) else 0 end as BDJG",sBDJGLXName,sBDJGLX);
|
||
|
||
else if (sBDGS == "2")
|
||
sBDGSSql += string.Format(" B.{0} as {1},A.{0} ,case when A.{0} <> 0 then ROUND((B.{0}-A.{0})/A.{0}*100,2) else 0 end as BDJG", sBDJGLXName, sBDJGLX);
|
||
|
||
return sBDGSSql;
|
||
}
|
||
|
||
private string GetBDJGLXName(string sBDJGLX)
|
||
{
|
||
switch (sBDJGLX)
|
||
{
|
||
case "HSJJ":
|
||
return "XLXSJGBMX_HSJJ";
|
||
case "WSJJ":
|
||
return "XLXSJGBMX_WSJJ";
|
||
case "HSBJ":
|
||
return "XLXSJGBMX_HSBJ";
|
||
case "WSBJ":
|
||
return "XLXSJGBMX_WSBJ";
|
||
default:
|
||
return "";
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 导出数据
|
||
|
||
protected void btnSJDC_Click(object sender, EventArgs e)
|
||
{
|
||
base.InitEntityByCondition();
|
||
int iNumberDecimal = XLBJDUtils.BJCLPriceDecimal;
|
||
|
||
if (CurQueryEntity.FieldByName("BDJGB").Value.Trim().Equals(""))
|
||
{
|
||
ShowMessage("请先查询数据！");
|
||
return;
|
||
}
|
||
|
||
string sFileName = "";
|
||
sFileName = CurQueryEntity.FieldByName("BDJGB").Value.Trim() + "价格表比对明细";
|
||
string m_tmpFilePath = FileService.CombineTempPath(sFileName + ".xlsx");
|
||
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
|
||
#region 构建打印栏目
|
||
|
||
Dictionary<string, string> dicColumns = new Dictionary<string, string>();
|
||
|
||
dicColumns.Clear();
|
||
dicColumns.Add("JGBID", "价格表（原始）");
|
||
dicColumns.Add("JGBXH", "序号（原始）");
|
||
dicColumns.Add("XLXSJGBMX_JGBID", "价格表（比对）");
|
||
dicColumns.Add("XLXSJGBMX_XH", "序号（比对）");
|
||
dicColumns.Add("XLXSJGBMX_XLXHID", "型号");
|
||
dicColumns.Add("XLXSJGBMX_XLGGID", "规格");
|
||
dicColumns.Add("XLBZ", "标准");
|
||
dicColumns.Add("XLXSJGBMX_DYDJID", "电压等级");
|
||
dicColumns.Add("XLXSJGBMX_JLDW", "计量单位");
|
||
string sBDJGLX = CurQueryEntity.FieldByName("BDJGLX").Value.Trim();
|
||
switch (sBDJGLX)
|
||
{
|
||
case "HSJJ":
|
||
dicColumns.Add("HSJJ", "含税基价（原始）");
|
||
dicColumns.Add("XLXSJGBMX_HSJJ", "含税基价（比对）");
|
||
break;
|
||
case "WSJJ":
|
||
dicColumns.Add("WSJJ", "无税基价（原始）");
|
||
dicColumns.Add("XLXSJGBMX_WSJJ", "无税基价（比对）");
|
||
break;
|
||
case "HSBJ":
|
||
dicColumns.Add("HSBJ", "含税表价（原始）");
|
||
dicColumns.Add("XLXSJGBMX_HSBJ", "含税表价（比对）");
|
||
break;
|
||
case "WSBJ":
|
||
dicColumns.Add("WSBJ", "无税表价（原始）");
|
||
dicColumns.Add("XLXSJGBMX_WSBJ", "无税表价（比对）");
|
||
break;
|
||
}
|
||
|
||
if (CurQueryEntity.FieldByName("BDGS").Value.Trim() == "1")
|
||
dicColumns.Add("BDJG", "比对结果");
|
||
else
|
||
dicColumns.Add("BDJG", "比对结果(%)");
|
||
|
||
|
||
|
||
#endregion
|
||
|
||
#region 查询打印数据集
|
||
string sBDGS = CurQueryEntity.FieldByName("BDGS").Value.Trim();
|
||
|
||
SQL.Clear();
|
||
SQL.Add("select rtrim(B.XLXSJGBMX_JGBID)||'|'|| C.XLXSJGB_JGBMC as JGBID,B.XLXSJGBMX_XH as JGBXH,rtrim(A.XLXSJGBMX_JGBID)||'|'||D.XLXSJGB_JGBMC as XLXSJGBMX_JGBID,A.XLXSJGBMX_XH,");
|
||
SQL.Add(" A.XLXSJGBMX_XLXHID, A.XLXSJGBMX_XLGGID,rtrim(A.XLXSJGBMX_XLBZID)||'|'||XLBZ_XLBZLXMC as XLBZ,A.XLXSJGBMX_DYDJID,A.XLXSJGBMX_JLDW,");
|
||
SQL.Add(GetSQLByGSLX(sBDGS, sBDJGLX));
|
||
SQL.Add(" from XLXSJGBMX A ");
|
||
SQL.Add(" left join XLXSJGBMX B ");
|
||
SQL.Add (" on A.XLXSJGBMX_XLXHID=B.XLXSJGBMX_XLXHID and A.XLXSJGBMX_XLGGID=B.XLXSJGBMX_XLGGID and A.XLXSJGBMX_XLBZID=B.XLXSJGBMX_XLBZID ");
|
||
SQL.Add( " and A.XLXSJGBMX_DYDJID=B.XLXSJGBMX_DYDJID and A.XLXSJGBMX_JLDW=B.XLXSJGBMX_JLDW ");
|
||
SQL.Add( " left join XLXSJGB C on C.XLXSJGB_JGBID = B.XLXSJGBMX_JGBID");
|
||
SQL.Add(" left join XLXSJGB D on D.XLXSJGB_JGBID = A.XLXSJGBMX_JGBID");
|
||
SQL.Add(" left join XLBZ on XLBZ_XLBZID=A.XLXSJGBMX_XLBZID");
|
||
SQL.Add(" where 1=1 ");
|
||
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("YSJGB").Value.Trim()))
|
||
{
|
||
SQL.Add(" and B.XLXSJGBMX_JGBID=:pYSJGB");
|
||
SQL.ParamByName("pYSJGB").Value = CurQueryEntity.FieldByName("YSJGB").Value.Trim();
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("BDJGB").Value.Trim()))
|
||
{
|
||
SQL.Add(" and A.XLXSJGBMX_JGBID =:pBDJGB");
|
||
SQL.ParamByName("pBDJGB").Value = CurQueryEntity.FieldByName("BDJGB").Value.Trim();
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("MXBH").Value.Trim()))
|
||
{
|
||
SQL.Add(" and C.XLXSJGB_MXBH=:pMXBH");
|
||
SQL.Add(" and D.XLXSJGB_MXBH=:pMXBH");
|
||
SQL.ParamByName("pMXBH").Value = CurQueryEntity.FieldByName("MXBH").Value.Trim();
|
||
}
|
||
//型号模糊
|
||
if (!CurQueryEntity.FieldByName("XLXHIDJQPP").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLXHID").Value.Trim()))
|
||
{
|
||
SQL.Add (" \r\n and A.XLXSJGBMX_XLXHID like :pXLXHID ");
|
||
SQL.ParamByName("pXLXHID").Value = "%" + CurQueryEntity.FieldByName("XLXHID").Value.Trim() + "%";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string sXLXHID = CurQueryEntity.FieldByName("XLXHID").Value.Trim();
|
||
if (!string.IsNullOrEmpty(sXLXHID))
|
||
{
|
||
string[] sTemp = sXLXHID.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
|
||
if (sTemp.Length >= 2)
|
||
{
|
||
SQL.Add ( " \r\n and " + CMISproDataBase.GetWhereEqual("A.XLXSJGBMX_XLXHID", sXLXHID));
|
||
}
|
||
else
|
||
{
|
||
SQL.Add ( " \r\n and A.XLXSJGBMX_XLXHID = :pXLXHID ");
|
||
SQL.ParamByName("pXLXHID").Value = sXLXHID;
|
||
}
|
||
}
|
||
}
|
||
//规格模糊
|
||
if (!CurQueryEntity.FieldByName("XLGGIDJQPP").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLGGID").Value.Trim()))
|
||
{
|
||
SQL.Add ( " and replace(A.XLXSJGBMX_XLGGID,'*','×') like :pXLGGID ");
|
||
SQL.ParamByName("pXLGGID").Value = "%" + CurQueryEntity.FieldByName("XLGGID").Value.Trim().Replace("*", "×") + "%";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string sXLGGID = CurQueryEntity.FieldByName("XLGGID").Value.Trim();
|
||
if (!string.IsNullOrEmpty(sXLGGID))
|
||
{
|
||
string[] sTemp = sXLGGID.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
|
||
if (sTemp.Length >= 2)
|
||
{
|
||
SQL.Add ( " and " + CMISproDataBase.GetWhereEqual(" replace(A.XLXSJGBMX_XLGGID,'*','×')", sXLGGID.Replace('*', '×')));
|
||
}
|
||
else
|
||
{
|
||
SQL.Add (" and replace(A.XLXSJGBMX_XLGGID,'*','×') = :pXLGGID ");
|
||
SQL.ParamByName("pXLGGID").Value = CurQueryEntity.FieldByName("XLGGID").Value.Trim().Replace("*", "×");
|
||
}
|
||
}
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("ST").Value.Trim()))
|
||
{
|
||
SQL.Add ( " and A.XLXSJGBMX_XLBZID = :pST ");
|
||
SQL.ParamByName("pST").Value = CurQueryEntity.FieldByName("ST").Value.Trim();
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("DYDJ").Value.Trim()))
|
||
{
|
||
SQL.Add ( " and A.XLXSJGBMX_DYDJID = :pDYDJ ");
|
||
SQL.ParamByName("pDYDJ").Value = CurQueryEntity.FieldByName("DYDJ").Value.Trim();
|
||
}
|
||
if (CurQueryEntity.FieldByName("PXFS").Value.Trim().Equals("型号+标准+规格序号+电压等级"))
|
||
{
|
||
SQL.Add (" order by B.XLXSJGBMX_JGBID,A.XLXSJGBMX_XLXHID,A.XLXSJGBMX_XLBZID,minXH,A.XLXSJGBMX_DYDJID ");
|
||
}
|
||
else
|
||
SQL.Add (" order by B.XLXSJGBMX_JGBID,b.XLXSJGBMX_XH");
|
||
|
||
DataSet dsJGBBD = dberp.OpenDataSet(SQL);
|
||
|
||
#endregion
|
||
|
||
string sColName = "";
|
||
|
||
//循环固定列，进行赋值
|
||
for (int i = 0; i < dsJGBBD.Tables[0].Columns.Count; i++)
|
||
{
|
||
if (dicColumns.ContainsKey(dsJGBBD.Tables[0].Columns[i].ColumnName))
|
||
{
|
||
sColName = dsJGBBD.Tables[0].Columns[i].ColumnName;
|
||
dsJGBBD.Tables[0].Columns[i].Caption = "待处理列&_" + dicColumns[sColName];
|
||
}
|
||
|
||
}
|
||
|
||
//循环，删除列
|
||
for (int i = dsJGBBD.Tables[0].Columns.Count - 1; i >= 0; i--)
|
||
{
|
||
if (dsJGBBD.Tables[0].Columns[i].Caption.StartsWith("待处理列&_"))
|
||
{
|
||
dsJGBBD.Tables[0].Columns[i].Caption = dsJGBBD.Tables[0].Columns[i].Caption.Replace("待处理列&_", "");
|
||
}
|
||
else
|
||
{
|
||
dsJGBBD.Tables[0].Columns.RemoveAt(i);
|
||
}
|
||
}
|
||
|
||
if (dsJGBBD.Tables[0].Rows.Count > 0)
|
||
{
|
||
string smessage = "";
|
||
string sLet = "";
|
||
//
|
||
NpoiSheetData sd = new NpoiSheetData();
|
||
sd.ColumnStyle = new System.Collections.Generic.Dictionary<string, NpoiColumnStyle>();
|
||
NpoiColumnStyle ct = new NpoiColumnStyle();
|
||
ct.DataFormat = "@";
|
||
sd.ColumnStyle.Add("B", ct);
|
||
|
||
|
||
sd.ColumnType = new System.Collections.Generic.Dictionary<string, NpoiColumnType>();
|
||
|
||
for (int k = 0; k < dicColumns.Count; k++)
|
||
{
|
||
if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("JGBXH"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("XLXSJGBMX_XH"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("WSJJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("HSJJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("WSBJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("HSBJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("XLXSJGBMX_WSJJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("XLXSJGBMX_HSJJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("XLXSJGBMX_WSBJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("XLXSJGBMX_HSBJ"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
else if (dsJGBBD.Tables[0].Columns[k].ColumnName.StartsWith("BDJG"))
|
||
{
|
||
sLet = TExcelData.ColIndexToReference(k);
|
||
sd.ColumnType.Add(sLet, NpoiColumnType.Double);
|
||
}
|
||
|
||
}
|
||
|
||
TExcelData.CreateExcel_XLSX_NPOI(m_tmpFilePath, dsJGBBD, true, null, sd, ref smessage);
|
||
if (smessage.Equals(""))
|
||
{
|
||
System.IO.FileInfo file = new System.IO.FileInfo(m_tmpFilePath);
|
||
string URL = Page.ResolveClientUrl("~") + "/N_MODEL/Dialog/TempFileDownload.ashx?TempFileName=" + file.Name + "&FileName=" + sFileName + file.Extension;
|
||
string jsript = @"var JLDCDiv = document.createElement('div');JLDCDiv.style.width=0;JLDCDiv.style.height=0;JLDCDiv.id = 'DivifrDownLoad';"
|
||
+ " var ele=document.documentElement||document.body;ele.appendChild(JLDCDiv);var iJLDCfrm = document.createElement('iframe');iJLDCfrm.id = 'iJLDCfrmDownLoad';iJLDCfrm.src = '" + URL + "';"
|
||
+ "iJLDCfrm.style.width = 0;iJLDCfrm.style.height = 0; JLDCDiv.appendChild(iJLDCfrm);";
|
||
AJAXRunScript(this, "OPENJLDC", jsript);
|
||
}
|
||
else
|
||
{
|
||
ShowMessage(smessage);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
ShowMessage("无记录可以导出！");
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region GridView增加列
|
||
|
||
/// <summary>
|
||
/// 设置etNumber列的属
|
||
/// </summary>
|
||
/// <param name="sDataField"></param>
|
||
/// <param name="HeadText"></param>
|
||
/// <param name="NumberDecimalPlace"></param>
|
||
/// <param name="Width"></param>
|
||
private void AddColumn(string sDataField, string HeadText, int NumberDecimalPlace, int Width)
|
||
{
|
||
BoundField Column = new BoundField();
|
||
Column.DataField = sDataField;
|
||
Column.HeaderText = HeadText;
|
||
Column.ItemStyle.Width = (Unit)Width;
|
||
Column.HeaderStyle.Width = (Unit)Width;
|
||
Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
|
||
ContentGrid.Columns.Add(Column);
|
||
|
||
gtGridView_Column ColumnEx = new gtGridView_Column();
|
||
ColumnEx.DataField = sDataField;
|
||
ColumnEx.EditType = TEditType.etNumber;
|
||
ColumnEx.ShowHint = true;
|
||
ColumnEx.NumberDecimalPlace = NumberDecimalPlace;
|
||
ContentGrid.ColumnsEx.Insert(0, ColumnEx);
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
}
|