703 lines
31 KiB
C#
703 lines
31 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_MISPRO.Data;
|
||
using GThinking.N_MODEL.Page;
|
||
using GThinking.N_WORKFLOW;
|
||
using GThinking.N_MODEL.Dialog;
|
||
using GThinking.N_PUBLIC;
|
||
using GThinking.N_MISPRO.Utility;
|
||
using GThinking.N_CONTROL;
|
||
using System.IO;
|
||
using System.Data.OleDb;
|
||
using System.Data.Common;
|
||
using GThinking.N_MISPROAS;
|
||
using System.Collections.Generic;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
public partial class XLJGB_DRInfo : SingleCardBase
|
||
{
|
||
XLJGB_DREntity entity = new XLJGB_DREntity();
|
||
//数据库插入
|
||
int sSucessIs = 0;
|
||
//数据库更新
|
||
int sSucessUp = 0;
|
||
//表格数据
|
||
int sSucessDr = 0;
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
this.UMID = 100860001;
|
||
this.UMName = "线缆销售报价价格表";
|
||
base.CurMaster = Master;
|
||
base.CurEntity = entity;
|
||
this.PageHeight = 180;
|
||
this.PageWidth = 420;
|
||
Master.EnableAttachBtn = false;
|
||
Master.EnableCancelBtn = false;
|
||
Master.EnableCopyBtn = false;
|
||
Master.EnableDeleteBtn = false;
|
||
Master.EnableEditBtn = false;
|
||
Master.EnableInsertBtn = false;
|
||
Master.EnablePrintBtn = false;
|
||
Master.EnableRefreshBtn = false;
|
||
Master.EnableSaveBtn = false;
|
||
Server.ScriptTimeout = 3600;
|
||
fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath;
|
||
if (AppRuntime.BitType == AppBitType.Bit32)
|
||
fupdPost.Filter = "XLS";
|
||
else // AppBitType.Bit64
|
||
fupdPost.Filter = "XLS,XLSX";
|
||
base.InitPage();
|
||
Server.ScriptTimeout = 3600;
|
||
|
||
if(PageParam.GetRTrimString("JJLY").Equals("D"))
|
||
{
|
||
|
||
lblLMNR2.Text = "一：型号、规格、标准、电压等级、计量单位、含税基价、无税基价";
|
||
if(PageParam.GetRTrimString("BJLY").Equals("D"))
|
||
lblLMNR2.Text = "一：型号、规格、标准、电压等级、计量单位、含税基价、无税基价、含税表价、无税表价";
|
||
lblLMNR3.Text = "二：型号、规格、标准、电压等级、计量单位、含税基价、无税基价、工艺卡号、备注";
|
||
if (PageParam.GetRTrimString("BJLY").Equals("D"))
|
||
lblLMNR3.Text = "二：型号、规格、标准、电压等级、计量单位、含税基价、无税基价、含税表价、无税表价、工艺卡号、备注";
|
||
}
|
||
else
|
||
{
|
||
lblLMNR2.Text = "一：型号、规格、标准、电压等级、计量单位、含税表价、无税表价";
|
||
lblLMNR3.Text = "二：型号、规格、标准、电压等级、计量单位、含税表价、无税表价、工艺卡号、备注";
|
||
}
|
||
if (!IsPostBack)
|
||
{
|
||
chkSheet.Checked = false;
|
||
cbxSheetList.Enabled = false;
|
||
}
|
||
}
|
||
|
||
#region 控件事件
|
||
|
||
protected void btnQD_Click(object sender, EventArgs e)
|
||
{
|
||
if (fupdPost.FilePath.Equals(""))
|
||
{
|
||
ShowMessage("请选择需要导入的文件！");
|
||
return;
|
||
}
|
||
else
|
||
{
|
||
if (!txtLastPath.Value.Trim().Equals(""))
|
||
{
|
||
if ((fupdPost.FilePath != txtLastPath.Value) && !cbxSheetList.SelectedValue.Trim().Equals(""))
|
||
{
|
||
ShowMessage("导入文件已发生变化，请重新指定导入页面！");
|
||
chkSheet_CheckedChanged(null, null);
|
||
return;
|
||
}
|
||
}
|
||
}
|
||
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sSQL = new TSQL(dberp);
|
||
|
||
try
|
||
{
|
||
dberp.BeginTrans();
|
||
//当前价格表ID
|
||
string sJGBID = PageParam.GetRTrimString("XLXSJGBMX_JGBID");
|
||
string sJGBMC = PageParam.GetRTrimString("XLXSJGBMX_JGBMC");
|
||
sSucessDr = 0;
|
||
sSucessIs = 0;
|
||
sSucessUp = 0;
|
||
Dictionary<string, string> dicDRMXBZ = new Dictionary<string, string>();
|
||
dicDRMXBZ.Clear();
|
||
sSQL.Clear();
|
||
sSQL.Add("select XLBZ_XLBZID,XLBZ_XLBZLXMC from XLBZ where 1 = 1 ");
|
||
DataSet ds = dberp.OpenDataSet(sSQL);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
dicDRMXBZ.Add(ds.Tables[0].Rows[i]["XLBZ_XLBZID"].ToString().Trim(), ds.Tables[0].Rows[i]["XLBZ_XLBZLXMC"].ToString().Trim());
|
||
}
|
||
}
|
||
|
||
|
||
if (cbxSheetList.SelectedValue.Trim().Equals(""))
|
||
{
|
||
DataSet aDataExcel = new DataSet();
|
||
if (AppRuntime.BitType == AppBitType.Bit32)
|
||
aDataExcel = XLBJDUtils.ReadFromExcelFile(fupdPost.FilePath, 0, "");
|
||
else // AppBitType.Bit64
|
||
ReadWJ(fupdPost.FilePath, "", ref aDataExcel);
|
||
ExcelColumnName(aDataExcel);
|
||
//
|
||
string sFileName = fupdPost.FileName;
|
||
string sSheetName = GetDefaultSheetName(fupdPost.FilePath);
|
||
ExecDRSQL(dberp, sSQL, aDataExcel, sJGBID, dicDRMXBZ, sFileName, sSheetName);
|
||
|
||
ExecDRBZ(dberp, sSQL, aDataExcel, sJGBID, dicDRMXBZ, sFileName, sSheetName);
|
||
}
|
||
else
|
||
{
|
||
string[] sSheetList = cbxSheetList.SelectedValue.Split(',');
|
||
for (int i = 0; i < sSheetList.Length; i++)
|
||
{
|
||
DataSet aDataExcel = new DataSet();
|
||
if (AppRuntime.BitType == AppBitType.Bit32)
|
||
aDataExcel = XLBJDUtils.ReadFromExcelFile(fupdPost.FilePath, 0, sSheetList[i]);
|
||
else // AppBitType.Bit64
|
||
ReadWJ(fupdPost.FilePath, sSheetList[i], ref aDataExcel);
|
||
ExcelColumnName(aDataExcel);
|
||
//
|
||
string sFileName = fupdPost.FileName;
|
||
string sSheetName = sSheetList[i];
|
||
ExecDRSQL(dberp, sSQL, aDataExcel, sJGBID, dicDRMXBZ, sFileName, sSheetName);
|
||
ExecDRBZ(dberp, sSQL, aDataExcel, sJGBID, dicDRMXBZ, sFileName, sSheetName);
|
||
}
|
||
}
|
||
|
||
dberp.CommitTrans();
|
||
ShowMessage(String.Format("已导入到价格表【{0}】中！\r\n成功导入数据：{1}条。\r\n", sJGBID + "|" + sJGBMC, sSucessDr));
|
||
DialogOperationScript dos = new DialogOperationScript(this);
|
||
dos.ClickOwnerButton(PageParam.GetRTrimString("btnRefreshCID"));
|
||
dos.CloseSelf();
|
||
dos.Execute();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
dberp.RollbackTrans();
|
||
ShowMessage(ex.Message);
|
||
return;
|
||
}
|
||
}
|
||
|
||
protected void btnQX_Click(object sender, EventArgs e)
|
||
{
|
||
CloseWindow();
|
||
}
|
||
|
||
protected void chkSheet_CheckedChanged(object sender, EventArgs e)
|
||
{
|
||
if (chkSheet.Checked)
|
||
{
|
||
string AllsSheetName = "";
|
||
if (fupdPost.FilePath.Equals(""))
|
||
{
|
||
ShowMessage("请选择需要导入的文件！");
|
||
chkSheet.Checked = false;
|
||
return;
|
||
}
|
||
|
||
cbxSheetList.Enabled = true;
|
||
cbxSheetList.ClearItems();
|
||
cbxSheetList.SelectedValue = "";
|
||
|
||
string connStr = GetConnString(fupdPost.FilePath);
|
||
OleDbConnection m_conn = new OleDbConnection(connStr);
|
||
m_conn.Open();
|
||
DataTable sheetNames = m_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
|
||
if (sheetNames.Rows[0].Table.Rows.Count == 1)
|
||
{
|
||
string sSheetName = sheetNames.Rows[0].Table.Rows[0]["TABLE_NAME"].ToString().Trim();
|
||
if (sSheetName.StartsWith("'") && sSheetName.EndsWith("'"))
|
||
sSheetName = sSheetName.TrimStart('\'').TrimEnd('\'');
|
||
if (sSheetName.EndsWith("$"))
|
||
sSheetName = sSheetName.TrimEnd('$');
|
||
cbxSheetList.Items.Add(new ListItem(sSheetName, sSheetName));
|
||
cbxSheetList.SelectedValue = sSheetName;
|
||
}
|
||
else
|
||
{
|
||
for (int i = 0; i < sheetNames.Rows[0].Table.Rows.Count; i++)
|
||
{
|
||
string sSheetName = sheetNames.Rows[0].Table.Rows[i]["TABLE_NAME"].ToString().Trim();
|
||
if (sSheetName.StartsWith("'") && sSheetName.EndsWith("'"))
|
||
sSheetName = sSheetName.TrimStart('\'').TrimEnd('\'');
|
||
if (sSheetName.EndsWith("$"))
|
||
sSheetName = sSheetName.TrimEnd('$');
|
||
AllsSheetName = AllsSheetName + "," + sSheetName;
|
||
AllsSheetName = AllsSheetName.TrimEnd(',');
|
||
cbxSheetList.Items.Add(new ListItem(sSheetName, sSheetName));
|
||
}
|
||
cbxSheetList.SelectedValue = AllsSheetName;
|
||
}
|
||
if (m_conn.State == ConnectionState.Open)
|
||
{
|
||
m_conn.Dispose();
|
||
m_conn = null;
|
||
}
|
||
|
||
txtLastPath.Value = fupdPost.FilePath;
|
||
FormAreaUpdate();
|
||
}
|
||
else
|
||
{
|
||
cbxSheetList.ClearItems();
|
||
cbxSheetList.SelectedValue = "";
|
||
cbxSheetList.Enabled = false;
|
||
FormAreaUpdate();
|
||
}
|
||
}
|
||
|
||
// 根据32/64位运行时返回不同的连接字符串。
|
||
private string GetConnString(string sPath)
|
||
{
|
||
string conn;
|
||
if (AppRuntime.BitType == AppBitType.Bit32)
|
||
{
|
||
conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sPath + ";" + "Extended Properties=Excel 8.0;";
|
||
//conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + @";Extended Properties=""Excel 8.0;IMEX=1;ImportMixedTypes=Text""";
|
||
}
|
||
else // AppBitType.Bit64
|
||
{
|
||
conn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + sPath + ";" + "Extended Properties=Excel 8.0;";
|
||
}
|
||
return conn;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 自定义事件
|
||
|
||
private void ExecDRSQL(TDatabase dberp, TSQL sSQL, DataSet ds, string sJGBID, Dictionary<string, string> dicDRMXBZ, string sFileName, string sSheetName)
|
||
{
|
||
#region 导入记录
|
||
string sXLXHID = "";
|
||
string sXLGGID = "";
|
||
string sXLBZID = "G";
|
||
string sXLDYDJ = "";
|
||
string sJLDW = "";
|
||
int iXH = 1;
|
||
|
||
//是否启用非长度计量单位
|
||
bool bQYFCDJLDW = XLBJDUtils.GetXLXSBJPZXX(100860018).QYFCDJLDW();
|
||
|
||
Dictionary<string, double> dicLenJLDWTD = XLBJDUtils.GetLenJLDWTD();
|
||
|
||
//有效期
|
||
string sYXQS = PageParam.GetRTrimString("YXQS");
|
||
string sYXQZ = PageParam.GetRTrimString("YXQZ");
|
||
string sBJU003 = PageParam.GetRTrimString("BJU003");
|
||
string sDefaultXLBZID = PageParam.GetRTrimString("sDefaultXLBZID");
|
||
string sJJLY = PageParam.GetRTrimString("JJLY");
|
||
string sBJLY = PageParam.GetRTrimString("BJLY");
|
||
|
||
string sCLCB = "";
|
||
string sCPJJ = "";
|
||
string sWSCLCB = "";
|
||
string sWSCPJJ = "";
|
||
|
||
//A|× B|*
|
||
string sXLJGU007 = GetOption(102579000, "XLJGU007");
|
||
iXH = XLJGB_JGBMX.getMaxXH(sJGBID)-1;
|
||
Mac.MacLog.TraceFormat(String.Format("ExecDRSQL-Begin"));
|
||
//V_XSBJJSJGB_ID
|
||
int VnewID = CMISproDataBase.NewID("DBERP", "V_XSBJJSJGB");
|
||
//V表序号从1开始
|
||
int iVXH = 1;
|
||
|
||
#region 循环EXCEL插入V表
|
||
|
||
foreach (DataRow Row in ds.Tables[0].Rows)
|
||
{
|
||
sSucessDr++;
|
||
sXLXHID = sGetCellTest(Row, 0).Trim();
|
||
sXLGGID = sGetCellTest(Row, 1).Trim();
|
||
if (sXLJGU007.Equals("A"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("*", "×");
|
||
}
|
||
else if (sXLJGU007.Equals("B"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("×", "*");
|
||
}
|
||
sXLBZID = XLBJDUtils.GetBZXLZBZID(sGetCellTest(Row, 2).Trim(), sDefaultXLBZID, dicDRMXBZ);
|
||
|
||
if (sXLBZID.Equals(""))
|
||
sXLBZID = sGetCellTest(Row, 2).Trim();
|
||
sXLDYDJ = sGetCellTest(Row, 3).Trim().Replace('K', 'k').Replace('v', 'V'); ;
|
||
sJLDW = sGetCellTest(Row, 4).Trim();
|
||
if (!bQYFCDJLDW)
|
||
{
|
||
if (!sJLDW.ToUpper().Equals("KM") && !sJLDW.ToUpper().Equals("M") && !sJLDW.Equals("千米") && !sJLDW.Equals("米") && !dicLenJLDWTD.ContainsKey(sJLDW))
|
||
sJLDW = sBJU003;
|
||
else if (sJLDW.ToUpper().Equals("KM"))
|
||
sJLDW = "Km";
|
||
else if (sJLDW.ToUpper().Equals("M"))
|
||
sJLDW = "M";
|
||
}
|
||
else
|
||
{
|
||
if (sJLDW.Equals(""))
|
||
sJLDW = sBJU003;
|
||
else if (sJLDW.ToUpper().Equals("KM"))
|
||
sJLDW = "Km";
|
||
else if (sJLDW.ToUpper().Equals("M"))
|
||
sJLDW = "M";
|
||
}
|
||
sCLCB = sGetCellTest(Row, 5).Trim();
|
||
sCPJJ = sGetCellTest(Row, 5).Trim();
|
||
|
||
sWSCLCB = sGetCellTest(Row, 6).Trim();
|
||
sWSCPJJ = sGetCellTest(Row, 6).Trim();
|
||
if (sJJLY.Equals("D") && sBJLY.Equals("D"))
|
||
{
|
||
sCPJJ = sGetCellTest(Row, 7).Trim();
|
||
sWSCPJJ = sGetCellTest(Row, 8).Trim();
|
||
}
|
||
|
||
#region 插入V_XSBJJSJGB表 V_XSBJJSJGB_JGBJJ->含税基价 V_XSBJJSJGB_JGBBJ->无税基价,V_XSBJJSJGB_CLCB->含税表价,V_XSBJJSJGB_BJDHQCLCB->无税表价
|
||
|
||
sSQL.Clear();
|
||
sSQL.Add(@"insert into V_XSBJJSJGB (V_XSBJJSJGB_ID,V_XSBJJSJGB_BJDID,V_XSBJJSJGB_BJDXH,V_XSBJJSJGB_XLXHID,V_XSBJJSJGB_XLGGID,V_XSBJJSJGB_XLBZID,V_XSBJJSJGB_DYDJID,V_XSBJJSJGB_JGBJLDW,V_XSBJJSJGB_JGBJJ,V_XSBJJSJGB_JGBBJ,V_XSBJJSJGB_CLCB,V_XSBJJSJGB_BJDHQCLCB )
|
||
values(:pID,:pJGBID,:pXH,:pXLXHID,:pXLGGID,:pXLBZID,:pDYDJID,:pJGBJLDW,:pJGBJJ,:pJGBBJ,:pHSBJ,:pWSBJ)");
|
||
sSQL.ParamByName("pID").Value = VnewID;
|
||
sSQL.ParamByName("pJGBID").Value = sJGBID;
|
||
sSQL.ParamByName("pXH").Value = iVXH++;
|
||
sSQL.ParamByName("pXLXHID").Value = sXLXHID;
|
||
sSQL.ParamByName("pXLGGID").Value = sXLGGID;
|
||
sSQL.ParamByName("pXLBZID").Value = sXLBZID;
|
||
sSQL.ParamByName("pDYDJID").Value = sXLDYDJ;
|
||
sSQL.ParamByName("pJGBJLDW").Value = sJLDW;
|
||
sSQL.ParamByName("pJGBJJ").Value = CMISproConvert.StrToFloat(sCLCB, 0);
|
||
sSQL.ParamByName("pJGBBJ").Value = CMISproConvert.StrToFloat(sWSCLCB, 0);
|
||
sSQL.ParamByName("pHSBJ").Value = CMISproConvert.StrToFloat(sCPJJ, 0);
|
||
sSQL.ParamByName("pWSBJ").Value = CMISproConvert.StrToFloat(sWSCPJJ, 0);
|
||
|
||
dberp.ExecSQL(sSQL);
|
||
|
||
#endregion
|
||
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 存在相同报价产品不允许导入
|
||
|
||
sSQL.Clear();
|
||
sSQL.Add(" SELECT V_XSBJJSJGB_XLXHID,V_XSBJJSJGB_XLGGID,V_XSBJJSJGB_XLBZID,V_XSBJJSJGB_DYDJID ");
|
||
sSQL.Add(" FROM V_XSBJJSJGB where V_XSBJJSJGB_ID = :pID and V_XSBJJSJGB_BJDID=:pJGBID ");
|
||
sSQL.Add(" GROUP BY V_XSBJJSJGB_XLXHID,V_XSBJJSJGB_XLGGID,V_XSBJJSJGB_XLBZID,V_XSBJJSJGB_DYDJID HAVING COUNT(1) > 1 ");
|
||
sSQL.ParamByName("pID").Value = VnewID;
|
||
sSQL.ParamByName("pJGBID").Value = sJGBID;
|
||
DataSet dsV = dberp.OpenDataSet(sSQL);
|
||
if (dsV.Tables[0].Rows.Count > 0)
|
||
{
|
||
|
||
#region 删除V表
|
||
|
||
sSQL.Clear();
|
||
sSQL.Add("delete from V_XSBJJSJGB ");
|
||
sSQL.Add(" where V_XSBJJSJGB_ID=:pID");
|
||
sSQL.Add(" and V_XSBJJSJGB_BJDID=:pJGBID");
|
||
sSQL.ParamByName("pID").Value = VnewID;
|
||
sSQL.ParamByName("pJGBID").Value = sJGBID;
|
||
dberp.ExecSQL(sSQL);
|
||
|
||
#endregion
|
||
|
||
string sErrXLXHID = dsV.Tables[0].Rows[0]["V_XSBJJSJGB_XLXHID"].ToString().Trim();
|
||
string sErrXLGGID = dsV.Tables[0].Rows[0]["V_XSBJJSJGB_XLGGID"].ToString().Trim();
|
||
string sErrXLBZID = dsV.Tables[0].Rows[0]["V_XSBJJSJGB_XLBZID"].ToString().Trim();
|
||
string sErrDYDJID = dsV.Tables[0].Rows[0]["V_XSBJJSJGB_DYDJID"].ToString().Trim();
|
||
string sErrorMessage = "导入数据中存在相同报价产品[" + sErrXLXHID + " " + sErrXLGGID + " " + sErrXLBZID + " " + sErrDYDJID + "]，导入失败！";
|
||
throw new Exception(sErrorMessage);
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 将V表插入XLXSJGBMX
|
||
|
||
sSQL.Clear();
|
||
sSQL.Add(@" merge into XLXSJGBMX tar
|
||
using ( select row_number() over( order by V_XSBJJSJGB_BJDXH )+:pMaxXH as XH,
|
||
V_XSBJJSJGB_XLXHID as XLXHID,
|
||
V_XSBJJSJGB_XLGGID as XLGGID,
|
||
V_XSBJJSJGB_XLBZID as XLBZID,
|
||
V_XSBJJSJGB_DYDJID as DYDJID,
|
||
V_XSBJJSJGB_JGBJLDW as JLDW,
|
||
V_XSBJJSJGB_JGBJJ as JGBJJ,
|
||
V_XSBJJSJGB_JGBBJ as JGBBJ,
|
||
V_XSBJJSJGB_CLCB as HSBJ,
|
||
V_XSBJJSJGB_BJDHQCLCB as WSBJ,
|
||
case when upper(V_XSBJJSJGB_JGBJLDW) in ('KM','千米') then 0.001 when upper(V_XSBJJSJGB_JGBJLDW) in ('M','米') then 1 else 0 end as dJQJGXS
|
||
from V_XSBJJSJGB
|
||
where V_XSBJJSJGB_ID=:pID and V_XSBJJSJGB_BJDID=:pJGBID
|
||
) sor
|
||
on (
|
||
tar.XLXSJGBMX_JGBID=:pJGBID
|
||
and tar.XLXSJGBMX_XLXHID =sor.XLXHID
|
||
and tar.XLXSJGBMX_XLGGID =sor.XLGGID
|
||
and tar.XLXSJGBMX_XLBZID =sor.XLBZID
|
||
and tar.XLXSJGBMX_DYDJID =sor.DYDJID
|
||
)
|
||
when matched then update set ");
|
||
sSQL.Add(" XLXSJGBMX_WSJJ = sor.JGBBJ ,XLXSJGBMX_HSJJ = sor.JGBJJ, ");
|
||
sSQL.Add(" XLXSJGBMX_HSBJ = sor.JGBJJ ,XLXSJGBMX_WSBJ=sor.JGBBJ,XLXSJGBMX_JLDW=sor.JLDW, ");
|
||
sSQL.Add(" XLXSJGBMX_HSJJM = RoundFix(sor.JGBJJ* sor.dJQJGXS,6),XLXSJGBMX_HSBJM=RoundFix(sor.JGBJJ* sor.dJQJGXS,6),XLXSJGBMX_WSJJM=RoundFix(sor.JGBBJ* sor.dJQJGXS,6),XLXSJGBMX_WSBJM=RoundFix(sor.JGBBJ* sor.dJQJGXS,6), ");
|
||
sSQL.Add(" XLXSJGBMX_WHR = :XLXSJGBMX_WHR, XLXSJGBMX_WHRID=:XLXSJGBMX_WHRID, XLXSJGBMX_WHSJ=:XLXSJGBMX_WHSJ ");
|
||
sSQL.Add(" when not matched then insert ( ");
|
||
sSQL.Add(" XLXSJGBMX_JGBID, XLXSJGBMX_XH,XLXSJGBMX_XLXHID,XLXSJGBMX_XLGGID,XLXSJGBMX_XLBZID,XLXSJGBMX_DYDJID,XLXSJGBMX_JLDW, ");
|
||
sSQL.Add(" XLXSJGBMX_YXQS,XLXSJGBMX_YXQZ,XLXSJGBMX_WSJJ,XLXSJGBMX_HSJJ,XLXSJGBMX_HSBJ,XLXSJGBMX_WSBJ,XLXSJGBMX_BZ,XLXSJGBMX_WHR,XLXSJGBMX_WHRID,XLXSJGBMX_WHSJ,");
|
||
sSQL.Add(" XLXSJGBMX_HSJJM,XLXSJGBMX_HSBJM,XLXSJGBMX_WSJJM,XLXSJGBMX_WSBJM )");
|
||
sSQL.Add(" values(");
|
||
sSQL.Add(" :pJGBID,sor.XH,sor.XLXHID,sor.XLGGID,sor.XLBZID,isnull(sor.DYDJID,' '),sor.JLDW, ");
|
||
sSQL.Add(" :XLXSJGBMX_YXQS,:XLXSJGBMX_YXQZ,sor.JGBBJ,sor.JGBJJ,sor.HSBJ,sor.WSBJ,:XLXSJGBMX_BZ,:XLXSJGBMX_WHR,:XLXSJGBMX_WHRID,:XLXSJGBMX_WHSJ, ");
|
||
sSQL.Add(" RoundFix(sor.JGBJJ* sor.dJQJGXS,6), RoundFix(sor.HSBJ* sor.dJQJGXS,6), RoundFix(sor.JGBBJ* sor.dJQJGXS,6),RoundFix(sor.WSBJ* sor.dJQJGXS,6) )");
|
||
sSQL.ParamByName("pMaxXH").Value = iXH;
|
||
sSQL.ParamByName("pID").Value = VnewID;
|
||
sSQL.ParamByName("pJGBID").Value = sJGBID;
|
||
sSQL.ParamByName("XLXSJGBMX_YXQS").Value = sYXQS;
|
||
sSQL.ParamByName("XLXSJGBMX_YXQZ").Value = sYXQZ.Equals("") ? null : sYXQZ;
|
||
sSQL.ParamByName("XLXSJGBMX_BZ").Value = "";
|
||
sSQL.ParamByName("XLXSJGBMX_WHR").Value = tSession.UserName;
|
||
sSQL.ParamByName("XLXSJGBMX_WHRID").Value = tSession.UserId;
|
||
sSQL.ParamByName("XLXSJGBMX_WHSJ").Value = CMISproConvert.ConvertTime(DateTime.Now);
|
||
dberp.ExecSQL(sSQL);
|
||
XLBJDUtils.SyncXLXSBJXLGGTHB_ByXLXSJGBMX_Batch(dberp, tSession, sJGBID);
|
||
#endregion
|
||
|
||
#region 删除V表
|
||
|
||
sSQL.Clear();
|
||
sSQL.Add("delete from V_XSBJJSJGB ");
|
||
sSQL.Add(" where V_XSBJJSJGB_ID=:pID");
|
||
sSQL.Add(" and V_XSBJJSJGB_BJDID=:pJGBID");
|
||
sSQL.ParamByName("pID").Value = VnewID;
|
||
sSQL.ParamByName("pJGBID").Value = sJGBID;
|
||
dberp.ExecSQL(sSQL);
|
||
|
||
#endregion
|
||
|
||
Mac.MacLog.TraceFormat(String.Format("ExecDRSQL-End"));
|
||
|
||
//
|
||
if (sJJLY.Equals("D"))
|
||
{
|
||
Mac.MacLog.TraceFormat(String.Format("DoCPJJ-Begin"));
|
||
|
||
XLJGB_CLJGTZList.DoCPJJ(dberp, PageParam.GetRTrimString("MXBH"), sJGBID, true,
|
||
CMISproConvert.StrToInt(PageParam.GetRTrimString("iXSWS")), PageParam.GetRTrimString("JSBLFS"), tSession.UserId, tSession.UserName, sBJU003);
|
||
|
||
Mac.MacLog.TraceFormat(String.Format("DoCPJJ-End"));
|
||
|
||
}
|
||
|
||
JGBMLInfo.UpdateJGBMXXSXH(dberp, sJGBID, PageParam.GetRTrimString("MXBH"));
|
||
|
||
#endregion
|
||
}
|
||
|
||
private void ExecDRBZ(TDatabase dberp, TSQL sSQL, DataSet ds, string sJGBID, Dictionary<string, string> dicDRMXBZ, string sFileName, string sSheetName)
|
||
{
|
||
|
||
string sXLXHID = "";
|
||
string sXLGGID = "";
|
||
string sXLBZID = "G";
|
||
string sXLDYDJ = "";
|
||
string sGYKH = "";
|
||
string sBZ = "";
|
||
string sXLJGU007 = GetOption(102579000, "XLJGU007");
|
||
string sDefaultXLBZID = PageParam.GetRTrimString("sDefaultXLBZID");
|
||
string sJJLY = PageParam.GetRTrimString("JJLY");
|
||
string sBJLY = PageParam.GetRTrimString("BJLY");
|
||
|
||
foreach (DataRow Row in ds.Tables[0].Rows)
|
||
{
|
||
sXLXHID = sGetCellTest(Row, 0).Trim();
|
||
sXLGGID = sGetCellTest(Row, 1).Trim();
|
||
if (sXLJGU007.Equals("A"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("*", "×");
|
||
}
|
||
else if (sXLJGU007.Equals("B"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("×", "*");
|
||
}
|
||
sXLBZID = XLBJDUtils.GetBZXLZBZID(sGetCellTest(Row, 2).Trim(), sDefaultXLBZID, dicDRMXBZ);
|
||
if (sXLBZID.Equals(""))
|
||
sXLBZID = sGetCellTest(Row, 2).Trim();
|
||
sXLDYDJ = sGetCellTest(Row, 3).Trim().Replace('K', 'k').Replace('v', 'V'); ;
|
||
try
|
||
{
|
||
sGYKH = sGetCellTest(Row, 7).Trim();
|
||
sBZ = sGetCellTest(Row, 8).Trim();
|
||
if (sJJLY.Equals("D") && sBJLY.Equals("D"))
|
||
{
|
||
sGYKH = sGetCellTest(Row, 9).Trim();
|
||
sBZ = sGetCellTest(Row, 10).Trim();
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
|
||
}
|
||
if (sGYKH.Equals("")) continue;
|
||
|
||
|
||
if (!dicDRMXBZ.ContainsKey(sXLBZID))
|
||
{
|
||
sSQL.Clear();
|
||
sSQL.Add("insert into XLBZ(XLBZ_XLBZID,XLBZ_XLBZLXMC,XLBZ_WHR,XLBZ_WHRID,XLBZ_WHSJ,XLBZ_DZSC,XLBZ_XLBZMS)");
|
||
sSQL.Add("values(:XLBZ_XLBZID,:XLBZ_XLBZLXMC,:XLBZ_WHR,:XLBZ_WHRID,:XLBZ_WHSJ,:XLBZ_DZSC,:XLBZ_XLBZMS)");
|
||
sSQL.ParamByName("XLBZ_XLBZID").Value = sXLBZID;
|
||
sSQL.ParamByName("XLBZ_XLBZLXMC").Value =sGYKH;
|
||
sSQL.ParamByName("XLBZ_WHR").Value = tSession.UserName;
|
||
sSQL.ParamByName("XLBZ_WHRID").Value = tSession.UserId;
|
||
sSQL.ParamByName("XLBZ_WHSJ").Value = DateTime.Now;
|
||
sSQL.ParamByName("XLBZ_DZSC").Value = "Y";
|
||
string sXLBZMS = sXLBZID+"/"+sGYKH + "/" + sBZ;
|
||
if (sXLBZMS.Length > 250)
|
||
sXLBZMS = sXLBZMS.Substring(0, 250);
|
||
sSQL.ParamByName("XLBZ_XLBZMS").Value =sXLBZMS;
|
||
dberp.ExecSQL(sSQL);
|
||
dicDRMXBZ.Add(sXLBZID, sGYKH);
|
||
|
||
}
|
||
|
||
//if (!CMISproDataBase.ExistsRecord("DBERP", string.Format("select 1 from XLBZGYKHDZB where XLBZGYKHDZB_XLBZID='{0}' and XLBZGYKHDZB_GYKH='{1}'", sXLBZID, sGYKH)))
|
||
//{
|
||
// sSQL.Clear();
|
||
// sSQL.Add("insert into XLBZGYKHDZB(XLBZGYKHDZB_XLBZID,XLBZGYKHDZB_GYKH,XLBZGYKHDZB_WHR,XLBZGYKHDZB_WHSJ,XLBZGYKHDZB_WHRID)");
|
||
// sSQL.Add("values(:XLBZGYKHDZB_XLBZID,:XLBZGYKHDZB_GYKH,:XLBZGYKHDZB_WHR,:XLBZGYKHDZB_WHSJ,:XLBZGYKHDZB_WHRID)");
|
||
// sSQL.ParamByName("XLBZGYKHDZB_XLBZID").Value = sXLBZID;
|
||
// sSQL.ParamByName("XLBZGYKHDZB_GYKH").Value = sGYKH;
|
||
// sSQL.ParamByName("XLBZGYKHDZB_WHR").Value = tSession.UserName;
|
||
// sSQL.ParamByName("XLBZGYKHDZB_WHRID").Value = tSession.UserId;
|
||
// sSQL.ParamByName("XLBZGYKHDZB_WHSJ").Value = DateTime.Now;
|
||
// dberp.ExecSQL(sSQL);
|
||
//}
|
||
|
||
|
||
|
||
//if (sBZ.Equals("")) continue;
|
||
//sSQL.Clear();
|
||
//sSQL.Add("update XLXSJGBMX set XLXSJGBMX_BZ=:pBZ ");
|
||
//sSQL.Add("where XLXSJGBMX_XLXHID=:pXLXHID and XLXSJGBMX_XLGGID=:pXLGGID and XLXSJGBMX_XLBZID=:pXLBZID and XLXSJGBMX_DYDJID=:pDYDJID ");
|
||
//sSQL.ParamByName("pXLXHID").Value = sXLXHID;
|
||
//sSQL.ParamByName("pXLGGID").Value = sXLGGID;
|
||
//sSQL.ParamByName("pXLBZID").Value = sXLBZID;
|
||
//sSQL.ParamByName("pDYDJID").Value = sXLDYDJ;
|
||
//sSQL.ParamByName("pBZ").Value = sBZ;
|
||
//dberp.ExecSQL(sSQL);
|
||
|
||
|
||
}
|
||
|
||
}
|
||
|
||
//检验栏目
|
||
private void ExcelColumnName(DataSet ds)
|
||
{
|
||
Mac.MacLog.TraceFormat(String.Format("ExcelColumnName-Begin"));
|
||
Dictionary<string, string> ColumnNames = new Dictionary<string, string>();
|
||
int iColumnCount = 7;
|
||
ColumnNames.Add("0", "型号");
|
||
ColumnNames.Add("1", "规格");
|
||
ColumnNames.Add("2", "标准");
|
||
ColumnNames.Add("3", "电压等级");
|
||
ColumnNames.Add("4", "计量单位");
|
||
if (PageParam.GetRTrimString("JJLY").Equals("D"))
|
||
{
|
||
ColumnNames.Add("5", "含税基价");
|
||
ColumnNames.Add("6", "无税基价");
|
||
if (PageParam.GetRTrimString("BJLY").Equals("D"))
|
||
{
|
||
ColumnNames.Add("7", "含税表价");
|
||
ColumnNames.Add("8", "无税表价");
|
||
iColumnCount = 9;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
ColumnNames.Add("5", "含税表价");
|
||
ColumnNames.Add("6", "无税表价");
|
||
}
|
||
|
||
|
||
for (int i = 0; i < iColumnCount; i++)
|
||
{
|
||
try
|
||
{
|
||
if (!ds.Tables[0].Columns[i].ColumnName.Trim().Equals(ColumnNames[i.ToString()].Trim()))
|
||
{
|
||
throw new Exception("导入文件的栏目内容格式不正确，请按Excel栏目要求的内容格式调整导入文件！");
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
throw new Exception("导入文件的栏目内容格式不正确，请按Excel栏目要求的内容格式调整导入文件！");
|
||
}
|
||
}
|
||
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
try
|
||
{
|
||
if (ds.Tables[0].Rows[i][0].ToString().Trim().Equals("")
|
||
|| ds.Tables[0].Rows[i][1].ToString().Trim().Equals("")
|
||
|| ds.Tables[0].Rows[i][2].ToString().Trim().Equals(""))
|
||
{
|
||
throw new Exception(String.Format("第{0}行型号、规格、标准存在空值，不允许导入！", (i + 2).ToString().Trim()));
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
throw new Exception(String.Format("第{0}行型号、规格、标准存在空值，不允许导入！", (i + 2).ToString().Trim()));
|
||
}
|
||
}
|
||
Mac.MacLog.TraceFormat(String.Format("ExcelColumnName-End"));
|
||
|
||
}
|
||
|
||
private void ReadWJ(string sPath, string sSheetName, ref DataSet aDataExcel)
|
||
{
|
||
string sMessage = "";
|
||
aDataExcel = TExcelData.ReadExcel(sPath.Trim(), sSheetName, "", false, ref sMessage);
|
||
if (!sMessage.Trim().Equals(""))
|
||
{
|
||
throw new Exception("文件读取失败！失败原因：" + sMessage.Trim());
|
||
}
|
||
|
||
}
|
||
|
||
private string sGetCellTest(DataRow Row, int ColumnIndex)
|
||
{
|
||
return Row[ColumnIndex].ToString().Trim().Replace("\r\n", "").Replace("\n", "");
|
||
}
|
||
|
||
//取得默认的sheet名称
|
||
private string GetDefaultSheetName(string sFilePath)
|
||
{
|
||
string connStr = GetConnString(sFilePath);
|
||
OleDbConnection m_conn = new OleDbConnection(connStr);
|
||
m_conn.Open();
|
||
DataTable sheetNames = m_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
|
||
string sSheetName = "";
|
||
if (sheetNames.Rows[0].Table.Rows.Count > 0)
|
||
{
|
||
sSheetName = sheetNames.Rows[0].Table.Rows[0]["TABLE_NAME"].ToString().Trim();
|
||
if (sSheetName.StartsWith("'") && sSheetName.EndsWith("'"))
|
||
sSheetName = sSheetName.TrimStart('\'').TrimEnd('\'');
|
||
if (sSheetName.EndsWith("$"))
|
||
sSheetName = sSheetName.TrimEnd('$');
|
||
}
|
||
|
||
if (m_conn.State == ConnectionState.Open)
|
||
{
|
||
m_conn.Dispose();
|
||
m_conn = null;
|
||
}
|
||
return sSheetName;
|
||
}
|
||
#endregion
|
||
}
|
||
}
|