protected void fuDocument_FileSelected(object sender, EventArgs e)
{
try
{
if (this.fuDocument.HasFile)
{
string toFilePath = "~/ERPUpload/ProductShelf/";
string strPath = AppDomain.CurrentDomain.BaseDirectory + "ERPUpload/ProductShelf/";
if (!Directory.Exists(strPath))
{
Directory.CreateDirectory(strPath);
}
//获得文件扩展名
string fileNameExt = Path.GetExtension(this.fuDocument.FileName).ToLower();
if (!ValidateFileType(fileNameExt))
{
Alert.Show("无效的文件类型！", "消息提示", MessageBoxIcon.Warning);
return;
}
string myallname = fuDocument.ShortFileName;//ShortFileName表示整个导入文件的名称
string[] mystr = myallname.Split('.');
if ((!ValidateFileType(myallname)) ||
mystr[1].Equals("doc") || mystr[1].Equals("docx") || mystr[1].Equals("png") || mystr[1].Equals("jpg"))
{
Alert.Show("请选择excel文件导入！", "消息提示", MessageBoxIcon.Warning);
return;
}
//验证合法的文件
if (CheckFileExt(fileNameExt))
{
//生成将要保存的随机文件名
string fileName = this.fuDocument.ShortFileName.Substring(0, this.fuDocument.ShortFileName.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 dtExcel = new DataTable();
if (fileNameExt == ".xlsx")
{
dtExcel = ExcelHelper.ImportExcelxtoDt(serverFileName, 1, 0); //导入excel2007
}
else
{
dtExcel = ExcelHelper.ImportExceltoDt(serverFileName, 1, 0);//导入excel2003
}
ViewTable = new DataTable();
DataTable schema = ApiUtil.GetTableSchema("DOC_HWZD");
DataTable myExcelData = GetShelfFiled();
string[] arrFiled = new string[myExcelData.Rows.Count];
for (int index = 0; index < myExcelData.Rows.Count; index++)
{
DataRow mydr = myExcelData.Rows[index];
arrFiled[index] = mydr["TABLEZD"].ToString();
dtExcel.Columns[mydr["EXCELNAME"].ToString()].ColumnName = mydr["TABLEZD"].ToString();
}
DataTable table = dtExcel.DefaultView.ToTable(false, arrFiled);
if (table.Rows.Count == 0)
{
Alert.Show("请导入有数据的货位资料表！");
myreset(table);
return;
}
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["KB"] == DBNull.Value)
{
Alert.Show("【库别】为必填项，请检查是否未填写！");
myreset(table);
return;
}
if (table.Rows[i]["KFBH"] == DBNull.Value)
{
Alert.Show("【库房编号】为必填项，请检查是否未填写！");
myreset(table);
return;
}
if (table.Rows[i]["HWID"] == DBNull.Value)
{
Alert.Show("【货位编号】为必填项，请检查是否未填写！");
myreset(table);
return;
}
//if (table.Rows[i]["KBNAME"] == DBNull.Value)
//{
// Alert.Show("【库房类别】为必填项，请检查是否未填写！");
// myreset(table);
// return;
//}
if (table.Rows[i]["QYBH"] == DBNull.Value)
{
Alert.Show("【区域编号】为必填项，请检查是否未填写！");
myreset(table);
return;
}
if (table.Rows[i]["PAI"] == DBNull.Value)
{
Alert.Show("【排】为必填项，请检查是否未填写！");
myreset(table);
return;
}
if (table.Rows[i]["LIE"] == DBNull.Value)
{
Alert.Show("【列】为必填项，请检查是否未填写！");
myreset(table);
return;
}
if (table.Rows[i]["CENG"] == DBNull.Value)
{
Alert.Show("【层】为必填项，请检查是否未填写！");
myreset(table);
return;
}
}
ViewTable = schema;
ViewTable.Columns.Add("KFBHNAME", typeof(String));
ViewTable.Columns.Add("KBNAME", typeof(String));
ViewTable.Columns.Add("MANAGERNAME", typeof(String));
foreach (DataRow dr in table.Rows)
{
DataRow ViewTableRow = ViewTable.NewRow();
foreach (DataColumn dc in table.Columns)
{
ViewTableRow[dc.ColumnName] = dr[dc.ColumnName];
}
ViewTable.Rows.Add(ViewTableRow);
}
dataSearch();
//Alert.Show("请双击表格行完善货位基本信息！");
File.Delete(serverFileName);
}
}
}
else
{
Alert.Show("请选择excel文件！");
}
}
catch (Exception ex)
{
Alert.Show(ex.Message);
}
}
private void saveDocCom(JArray ja)
{
DataTable dResultDOC = ApiUtil.GetTableSchema("DAT_BILLSTATUS_DOC_CONV");
DataTable dResultCOM = ApiUtil.GetTableSchema("DAT_BILLSTATUS_COM_CONV");
foreach (JToken jt in ja)
{
//表头
JObject jo = (JObject)jt;
String billType = jo.Value <String>("BILLTYPE");
String convSqlDOC = @"select * from dat_billstatus_rule where type = '" + billType + "' and doccom='D'";
String convSqlCOM = @"select * from dat_billstatus_rule where type='" + billType + "' and doccom='C'";
DataTable convertTableDOC = DbHelperOra.Query(convSqlDOC).Tables[0];
DataTable convertTableCOM = DbHelperOra.Query(convSqlCOM).Tables[0];
DataRow docRow = dResultDOC.NewRow();
String number = jo.Value <String>("NUMBER");
foreach (DataRow dr in convertTableDOC.Rows)
{
String colTarget = dr["COLTARGET"].ToString();
String colName = dr["COLNAME"].ToString();
Boolean isConv = "Y".Equals(dr["ISCONV"].ToString());
String convRule = dr["CONVRULE"].ToString();
if (!isConv)
{
if (jo.Value <String>(colName) != null)
{
docRow[colTarget] = jo.Value <String>(colName);
}
}
else
{
docRow[colTarget] = convRule;
}
}
docRow["DDBH"] = SeqNo;
docRow["CUSTID"] = CustId;
dResultDOC.Rows.Add(docRow);
//表体
JArray jacom = jo.Value <JArray>("ENTRIES");
foreach (JToken jtcom in jacom)
{
JObject jocom = (JObject)jtcom;
DataRow comRow = dResultCOM.NewRow();
foreach (DataRow dr in convertTableCOM.Rows)
{
String colTarget = dr["COLTARGET"].ToString();
String colName = dr["COLNAME"].ToString();
Boolean isConv = "Y".Equals(dr["ISCONV"].ToString());
String convRule = dr["CONVRULE"].ToString();
if (!isConv)
{
comRow[colTarget] = jocom.Value <String>(colName);
}
else
{
comRow[colTarget] = convRule;
}
}
comRow["CUSTID"] = CustId;
comRow["SEQNO"] = number;
comRow["ROWNO"] = jacom.IndexOf(jtcom);
DataTable goodsDt = getERPGDSEQ(jocom.Value <String>("MATERIALNUMBER"), CustId);
if (goodsDt.Rows.Count > 0)
{
comRow["GDSEQ"] = goodsDt.Rows[0]["GDSEQ"];
comRow["UNIT"] = goodsDt.Rows[0]["UNIT"];
comRow["GDNAME"] = goodsDt.Rows[0]["GDNAME"];
comRow["GDSPEC"] = goodsDt.Rows[0]["GDSPEC"];
comRow["GDMODE"] = goodsDt.Rows[0]["GDMODE"];
comRow["CATID"] = goodsDt.Rows[0]["CATID"];
comRow["JXTAX"] = goodsDt.Rows[0]["JXTAX"];
//comRow["PZWH"] = goodsDt.Rows[0]["PZWH"];
comRow["ZPBH"] = goodsDt.Rows[0]["ZPBH"];
comRow["BZHL"] = goodsDt.Rows[0]["BZHL"];
comRow["DDBH"] = SeqNo;
}
dResultCOM.Rows.Add(comRow);
}
}
bulkInsert("DAT_BILLSTATUS_DOC_CONV", dResultDOC, "BILLSTATUS.P_DOC_CONV");
bulkInsert("DAT_BILLSTATUS_COM_CONV", dResultCOM, "BILLSTATUS.P_COM_CONV");
saveLog();
}
private void saveDDDocCom(JArray ja)
{
DataTable dResultDOC = ApiUtil.GetTableSchema("DAT_DD_DOC_COMPARE");
DataTable dResultCOM = ApiUtil.GetTableSchema("DAT_DD_COM_COMPARE");
String strDDBH = "";
String strCustId = "";
String SEQNO = "";
foreach (JToken jt in ja)
{
//表头
JObject jo = (JObject)jt;
String billType = jo.Value <String>("BILLTYPE");
String convSqlDOC = @"select * from dat_download_rule where type = '" + billType + "' and doccom='D'";
String convSqlCOM = @"select * from dat_download_rule where type='" + billType + "' and doccom='C'";
DataTable convertTableDOC = DbHelperOra.Query(convSqlDOC).Tables[0];
DataTable convertTableCOM = DbHelperOra.Query(convSqlCOM).Tables[0];
DataRow docRow = dResultDOC.NewRow();
SEQNO = jo.Value <String>("FNUMBER");
strDDBH = SEQNO.Split('_')[0];
strCustId = SEQNO.Split('_')[1];
//判断FLAG<>4的单据不要
String strFlag = jo.Value <String>("FBASESTATUS");
//if (!"4".Equals(strFlag))
//{
// continue;
//}
foreach (DataRow dr in convertTableDOC.Rows)
{
String colTarget = dr["COLTARGET"].ToString();
String colName = dr["COLNAME"].ToString();
Boolean isConv = "Y".Equals(dr["ISCONV"].ToString());
String convRule = dr["CONVRULE"].ToString();
if (!isConv)
{
if (String.IsNullOrWhiteSpace(jo.Value <String>(colName)))
{
docRow[colTarget] = DBNull.Value;
}
else
{
docRow[colTarget] = jo.Value <String>(colName);
}
}
else
{
docRow[colTarget] = convRule;
}
}
int intSubNum = 0; //表头条数
decimal decSubSum = 0; //表头总金额
//表体
JArray jacom = jo.Value <JArray>("ENTRIES");
foreach (JToken jtcom in jacom)
{
JObject jocom = (JObject)jtcom;
DataRow comRow = dResultCOM.NewRow();
foreach (DataRow dr in convertTableCOM.Rows)
{
String colTarget = dr["COLTARGET"].ToString();
String colName = dr["COLNAME"].ToString();
Boolean isConv = "Y".Equals(dr["ISCONV"].ToString());
String convRule = dr["CONVRULE"].ToString();
if (!isConv)
{
if (String.IsNullOrWhiteSpace(jocom.Value <String>(colName)))
{
comRow[colTarget] = DBNull.Value;
}
else
{
comRow[colTarget] = jocom.Value <String>(colName);
}
}
else
{
comRow[colTarget] = convRule;
}
}
//计算表头中条数和总金额
intSubNum++;
// decSubSum = decSubSum + Convert.ToDecimal(comRow["HSJJ"]) * Convert.ToDecimal(comRow["DHS"]);
comRow["SEQNO"] = strDDBH;
comRow["ROWNO"] = jacom.IndexOf(jtcom) + 1;
DataTable goodsDt = getERPGoodsTable(jocom.Value <String>("MATERIALNUMBER"));
if (goodsDt.Rows.Count > 0)
{
comRow["GDSEQ"] = goodsDt.Rows[0]["GDSEQ"];
comRow["UNIT"] = goodsDt.Rows[0]["UNIT"];
comRow["GDNAME"] = goodsDt.Rows[0]["GDNAME"];
comRow["GDSPEC"] = goodsDt.Rows[0]["GDSPEC"];
//comRow["GDMODE"] = goodsDt.Rows[0]["GDMODE"];
comRow["CATID"] = goodsDt.Rows[0]["CATID"];
//comRow["JXTAX"] = goodsDt.Rows[0]["JXTAX"];
//comRow["PZWH"] = goodsDt.Rows[0]["PZWH"];
comRow["ZPBH"] = goodsDt.Rows[0]["ZPBH"];
comRow["BZHL"] = (goodsDt.Rows[0]["BZHL"] == null ? 0 : goodsDt.Rows[0]["BZHL"]);
//comRow["ISGZ"] = goodsDt.Rows[0]["ISGZ"];//
comRow["ISLOT"] = goodsDt.Rows[0]["ISLOT"];
comRow["BHSJJ"] = goodsDt.Rows[0]["BHSJJ"];
//comRow["DDBH"] = strDDBH;
comRow["ISGZ"] = "";
}
dResultCOM.Rows.Add(comRow);
}
docRow["SEQNO"] = strDDBH;
docRow["BILLNO"] = strDDBH;
docRow["SUBNUM"] = intSubNum;//
docRow["SUBSUM"] = decSubSum;
dResultDOC.Rows.Add(docRow);
}
bulkInsert("DAT_DD_DOC_COMPARE", dResultDOC, strDDBH, strCustId);
bulkInsert("DAT_DD_COM_COMPARE", dResultCOM, strDDBH, strCustId);
}