public ExcelViewModel Post(ImportExcel request)
{
//var files=Request.Files;
return(ExcelManager.ImportExcel(request));
}
public FList(FClass host, FieldWrap define, ImportExcel excel) : this(host, define)
{
excel.GetList(this, define);
}
public ActionResult ImportTest()
{
IList<ExcelField> fields=new List<ExcelField>();
ExcelField ef=null;
ef=new ExcelField("Code","代码");
fields.Add(ef);
ef = new ExcelField("Name", "名字");
fields.Add(ef);
ef = new ExcelField("TestExcel", "测试Excel");
fields.Add(ef);
string url=Server.MapPath("/") + @"Files\Temp\"+"1.xlsx";
//ImportExcel ie = new ImportExcel(url,"abc",fields);
ImportExcel ie = new ImportExcel(url,typeof(Department));
//IList<Department> vals = ie.List<Department>();
//foreach (Department dic in vals)
//{
// string code = dic.Code;
// string name = dic.Name;
// string testExcel = dic.TestExcel;
//}
return null;
}
public static string InsUpdDelExcelImport(char Event, ImportExcel obj, out int id)
{
id = 0;
return(DllImportExcel.InsUpdDelExcelImport(Event, obj, out id));
}
public FLong(FClass host, FieldWrap define, ImportExcel excel) : base(host, define)
{
Value = excel.GetLong();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (ddlSeason.SelectedValue == "0")
{
_msgbox.ShowWarning("Please Select Season!!");
return;
}
var dtExcel = new DataTable("ImportExcel");
dtExcel.Columns.Add("Date");
dtExcel.Columns.Add("DocNo");
dtExcel.Columns.Add("Customer_x0020_Name");
dtExcel.Columns.Add("Stock_x0020_No");
dtExcel.Columns.Add("Gender");
dtExcel.Columns.Add("Category");
dtExcel.Columns.Add("Item_x0020_Descr");
dtExcel.Columns.Add("Style");
dtExcel.Columns.Add("Color");
dtExcel.Columns.Add("Size");
dtExcel.Columns.Add("Qty");
dtExcel.Columns.Add("Item_x0020_Rate");
dtExcel.Columns.Add("MRP_x0020_INR");
dtExcel.Columns.Add("MRP_x0020_NPR");
dtExcel.Columns.Add("AccountMRP");
dtExcel.Columns.Add("SalesMRP");
var httpPostedFile = impImage.PostedFile;
if (httpPostedFile != null && httpPostedFile.ContentLength > 0)
{
var postedFile = impImage.PostedFile;
if (postedFile != null)
{
var fileExtension =
System.IO.Path.GetExtension(postedFile.FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
var fileLocation = Server.MapPath("~/Content/") + postedFile.FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
postedFile.SaveAs(fileLocation);
var excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation +
";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"";
}
//Create Connection to Excel work book and add oledb namespace
var excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
var dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return;
}
var excelSheets = new String[dt.Rows.Count];
var t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
var excelConnection1 = new OleDbConnection(excelConnectionString);
var query = string.Format("Select * from [{0}]", excelSheets[0]);
//string.Format("Select [Date],DocNo,Customer Name as CustomerName,Stock No as StockNo,Gender,Category,Item Descr as ItemDescr,Style,Color,Size,Qty,Item Rate as ItemRate,MRP INR as MRPINR,MRP NPR as MRPNPR from [{0}]", excelSheets[0]);
using (var dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(dtExcel);
}
}
}
if (!ValidateExcel(dtExcel))
{
_msgbox.ShowWarning("Excel columns doesn't match required.Please browse valid excel sheet!!");
return;
}
if (dtExcel.Rows.Count > 0)
{
var sw = new StringWriter();
dtExcel.WriteXml(sw);
var obj = new ImportExcel();
obj.ExcelData = sw.ToString();
obj.CreatedBy = BK_Session.GetSession().UserId;
obj.BranchId = int.Parse(ddlBranch.SelectedValue);
obj.ImportedDate = BK_Session.GetSession().OpDate;
obj.InvoiceNo = txtInvoiceNo.Text;
obj.AirwayBillNo = txtAirwayBillNo.Text;
obj.Season = int.Parse(ddlSeason.SelectedValue);
var id = 0;
var msg = BllImportExcel.InsUpdDelExcelImport('I', obj, out id);
if (msg == "Data Imported Successfully")
{
_msgbox.ShowSuccess(msg);
}
else
{
_msgbox.ShowWarning(msg);
}
}
else
{
_msgbox.ShowWarning("Please browse excel sheet having data!!");
}
}
else
{
_msgbox.ShowWarning("Please browse data first!!");
}
}
catch (Exception ex)
{
throw ex;
}
}
public static string InsUpdDelExcelOrder(char Event, ImportExcel obj, out int id)
{
id = 0;
return(DlOrderedItem.InsUpdDelOrderedItem(Event, obj, out id));
}
public ActionResult ImportData(string cls)
{
//string table = "Department";
IList <ImportResult> retVal = new List <ImportResult>();
string path = Server.MapPath("/") + @"Files\Temp\";
////文件上传，一次上传1M的数据，防止出现大文件无法上传
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++)
{
//保存上传的文件
File file = SaveHttpPostFile(files[i], path);
if (file.Size == 0)
{
continue;
}
Type type = Type.GetType("EasyJob.Pojo.Pojo." + cls + ",EasyJob.Pojo");
ImportExcel ie = new ImportExcel(file.RealPath, type);
IList <object> vals = ie.List(type);
//数据存储
ISession s = null;
ITransaction trans = null;
try {
s = HibernateOper.GetCurrentSession();
trans = s.BeginTransaction();
foreach (object val in vals)
{
if (val is TbBase && val is IExists)
{
ImportResult ir = new ImportResult();
TbBase tbVal = (TbBase)val;
try {
bool isExists = false;//是否存在
IExists valExists = (IExists)tbVal;
isExists = valExists.IsExists(s);
if (tbVal.ImportType.Equals("添加"))
{
if (isExists)
{
throw new Exception("Val is exists");
}
s.Save(tbVal);
}
else if (tbVal.ImportType.Equals("修改"))
{
if (!isExists)
{
throw new Exception("Val is not exists");
}
//s.Update(tbVal);
s.Merge(tbVal);
}
ir.Success = true;
} catch (Exception e) {
ir.Success = false;
ir.Msg = e.Message;
} finally {
retVal.Add(ir);
}
}
}
trans.Commit();
} catch (Exception e) {
if (trans != null)
{
trans.Rollback();
}
throw e;
}
}
return(Json(retVal));
}
private object[] GetAllMOByExcle()
{
try
{
string fileName = string.Empty;
ArrayList columnList = new ArrayList();
//定义变量，用来取多语言
string factory = string.Empty; // 工厂
string mocode = string.Empty; // 工单
string itemcode = string.Empty; // 产品代码
string moType = string.Empty; // 工单类型
string moPlanQty = string.Empty; // 工单计划数量
string planSDate = string.Empty; // 计划开始日期
string planEDate = string.Empty; // 计划完成日期
string customerCode = string.Empty; // 客户代码
string customerOrder = string.Empty; // 客户单号
string moMemo = string.Empty;
string moBOM = string.Empty;
string xmlPath = this.Request.MapPath("") + @"\ImportMOData.xml";
string dataType = "ImportMO";
ArrayList lineValues = new ArrayList();
lineValues = GetXMLHeader(xmlPath, dataType);
for (int i = 0; i < lineValues.Count; i++)
{
DictionaryEntry dictonary = new DictionaryEntry();
dictonary = (DictionaryEntry)lineValues[i];
if (dictonary.Key.ToString().Equals(MoDictionary.Factory))
{
factory = dictonary.Value.ToString().ToUpper();
columnList.Add(factory);
}
if (dictonary.Key.ToString().Equals(MoDictionary.Mocode))
{
mocode = dictonary.Value.ToString().ToUpper();
columnList.Add(mocode);
}
if (dictonary.Key.ToString().Equals(MoDictionary.Itemcode))
{
itemcode = dictonary.Value.ToString().ToUpper();
columnList.Add(itemcode);
}
if (dictonary.Key.ToString().Equals(MoDictionary.MoType))
{
moType = dictonary.Value.ToString().ToUpper();
columnList.Add(moType);
}
if (dictonary.Key.ToString().Equals(MoDictionary.MoPlanQty))
{
moPlanQty = dictonary.Value.ToString().ToUpper();
columnList.Add(moPlanQty);
}
if (dictonary.Key.ToString().Equals(MoDictionary.PlanSDate))
{
planSDate = dictonary.Value.ToString().ToUpper();
columnList.Add(planSDate);
}
if (dictonary.Key.ToString().Equals(MoDictionary.PlanEDate))
{
planEDate = dictonary.Value.ToString().ToUpper();
columnList.Add(planEDate);
}
if (dictonary.Key.ToString().Equals(MoDictionary.CustomerCode))
{
customerCode = dictonary.Value.ToString().ToUpper();
columnList.Add(customerCode);
}
if (dictonary.Key.ToString().Equals(MoDictionary.CustomerOrder))
{
customerOrder = dictonary.Value.ToString().ToUpper();
columnList.Add(customerOrder);
}
if (dictonary.Key.ToString().Equals(MoDictionary.MoMemo))
{
moMemo = dictonary.Value.ToString().ToUpper();
columnList.Add(moMemo);
}
if (dictonary.Key.ToString().Equals(MoDictionary.MoBOM))
{
moBOM = dictonary.Value.ToString().ToUpper();
columnList.Add(moBOM);
}
}
if (this.ViewState["UploadedFileName"] == null)
{
BenQGuru.eMES.Common.ExceptionManager.Raise(this.GetType().BaseType, "$Error_UploadFileIsEmpty");
}
fileName = this.ViewState["UploadedFileName"].ToString();
//读取EXCEL格式文件
System.Data.DataTable dt = new DataTable();
try
{
ImportXMLHelper xmlHelper = new ImportXMLHelper(xmlPath, dataType);
ArrayList gridBuilder = new ArrayList();
ArrayList notAllowNullField = new ArrayList();
gridBuilder = xmlHelper.GetGridBuilder(this.languageComponent1, dataType);
notAllowNullField = xmlHelper.GetNotAllowNullField(this.languageComponent1);
ImportExcel imXls = new ImportExcel(fileName, dataType, gridBuilder, notAllowNullField);
dt = imXls.XlaDataTable;
// dt = GetExcelData(fileName);
}
catch (Exception)
{
ExceptionManager.Raise(this.GetType().BaseType, "$GetExcelDataFiledCheckTemplate");
}
//对dt进行数据检查，去掉空行
List <DataRow> removelist = new List <DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool rowdataisnull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j].ToString().Trim() != "")
{
rowdataisnull = false;
}
}
if (rowdataisnull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
CheckTemplateRight(dt, columnList);
DBDateTime dbDateTime = FormatHelper.GetNowDBDateTime(this.DataProvider);
MOFacade mofacade = new MOFacade(this.DataProvider);
ArrayList objList = new ArrayList();
if ((dt != null) && (dt.Rows.Count > 0))
{
for (int h = 0; h < dt.Rows.Count; h++)
{
MO mo = new MO();
try
{
//填充DOMAIN,这里不做Check
mo.MOCode = FormatHelper.CleanString(dt.Rows[h][mocode].ToString().ToUpper());
mo.Factory = FormatHelper.CleanString(dt.Rows[h][factory].ToString().ToUpper());
mo.ItemCode = FormatHelper.CleanString(dt.Rows[h][itemcode].ToString().ToUpper());
mo.MOType = FormatHelper.CleanString(dt.Rows[h][moType].ToString().ToUpper());
try
{
mo.MOPlanQty = Convert.ToDecimal(FormatHelper.CleanString(dt.Rows[h][moPlanQty].ToString().ToUpper()));
}
catch (Exception)
{
ExceptionManager.Raise(this.GetType().BaseType, "$MOPlanQty_Must_Be_Decimal");
return(null);
}
mo.MOPlanStartTime = 0;
mo.MOPlanEndTime = 0;
try
{
mo.MOPlanStartDate = FormatHelper.TODateInt(FormatHelper.CleanString(dt.Rows[h][planSDate].ToString().ToUpper()));
mo.MOPlanEndDate = FormatHelper.TODateInt(FormatHelper.CleanString(dt.Rows[h][planEDate].ToString().ToUpper()));
}
catch (Exception)
{
ExceptionManager.Raise(this.GetType().BaseType, "$Data_Formart_Must_Be_YYYY-MM-DD");
return(null);
}
mo.CustomerCode = FormatHelper.CleanString(dt.Rows[h][customerCode].ToString().ToUpper());
mo.CustomerOrderNO = FormatHelper.CleanString(dt.Rows[h][customerOrder].ToString().ToUpper());
mo.MOMemo = FormatHelper.CleanString(dt.Rows[h][moMemo].ToString().ToUpper());
mo.BOMVersion = FormatHelper.CleanString(dt.Rows[h][moBOM].ToString().ToUpper());
mo.MaintainUser = this.GetUserCode();
mo.MaintainDate = dbDateTime.DBDate;
mo.MaintainTime = dbDateTime.DBTime;
mo.MOInputQty = 0;
mo.MORemark = " ";
mo.MOScrapQty = 0;
mo.MOActualQty = 0;
mo.MOActualStartDate = 0;
mo.MOActualEndDate = 0;
mo.MOStatus = MOManufactureStatus.MOSTATUS_INITIAL;
mo.IDMergeRule = 1;
mo.OrganizationID = GlobalVariables.CurrentOrganizations.First().OrganizationID;
mo.MOPCBAVersion = "1";
mo.MOVersion = "1.0";
mo.MODownloadDate = dbDateTime.DBDate; //当前时间
mo.IsControlInput = "0";;
mo.IsBOMPass = "******";
mo.MOUser = this.GetUserCode();
objList.Add(mo);
}
catch (Exception ex)
{
ExceptionManager.Raise(this.GetType().BaseType, ex.Message);
}
}
}
moObjects = (MO[])objList.ToArray(typeof(MO));
//检查当前数据是否符合导入要求(现在返回一个是否通过检查的标志)
if (moObjects != null)
{
foreach (MO mo in moObjects)
{
MODownloadCheck(mo);
}
}
}
catch (Exception e)
{
ExceptionManager.Raise(this.GetType().BaseType, e.Message);
}
return(moObjects);
}
public ActionResult Index(ImportExcel importExcel)
{
if (ModelState.IsValid)
{
string path = Server.MapPath("~/Assets/Upload/" + importExcel.file.FileName);
importExcel.file.SaveAs(path);
string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Properties='Excel 12.0 Xml;IMEX=1'";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Sheet Name
excelConnection.Open();
string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
excelConnection.Close();
//End
OleDbCommand cmd = new OleDbCommand("Select * from [" + tableName + "]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
using (ELearningDB db = new ELearningDB())
{
SqlConnection sqlCon = new SqlConnection(db.Database.Connection.ConnectionString);
sqlCon.Open();
using (SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlCon))
{
sqlBulk.DestinationTableName = "TaiKhoan";
//Mappings
sqlBulk.ColumnMappings.Add("Username", "Username");
sqlBulk.ColumnMappings.Add("Password", "Password");
sqlBulk.ColumnMappings.Add("Role", "Role");
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
sqlCon.Close();
var lstGVInserted = db.TaiKhoans.Where(x => x.Role == 2 && x.GiangVien == null);
List <GiangVien> lstGV = new List <GiangVien>();
foreach (var item in lstGVInserted)
{
GiangVien gv = new GiangVien();
gv.ID = item.ID;
db.GiangViens.Add(gv);
}
var lstHVInserted = db.TaiKhoans.Where(x => x.Role == 3 && x.HocVien == null);
List <HocVien> lstHV = new List <HocVien>();
foreach (var item in lstHVInserted)
{
HocVien hv = new HocVien();
hv.ID = item.ID;
db.HocViens.Add(hv);
}
db.SaveChanges();
}
//Give your Destination table name
ViewBag.Result = "Successfully Imported";
}
return(View());
}
private void btnExport_Click(object sender, EventArgs e)
{
ImportExcel import = new ImportExcel();
import.DataGridViewToExcels(this.superGridControl1);
}
protected void btnImportList_Click(object sender, EventArgs e)
{
try
{
SendMailEntities db = new SendMailEntities();
List <Contact> listContact = new List <Contact>();
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = txtNameFileUpload.Text;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
if (FileName != "")
{
DataTable dt = ImportExcel.ImportExcel2DataTable(FilePath, Extension);
foreach (DataRow dr in dt.Rows)
{
Contact contact;
if (!ContactBusiness.checkContactIsExist(dr["Email"].ToString().Trim()))
{
contact = new Contact();
contact.Email = dr["Email"].ToString().Trim();
contact.FirstName = dr["FirstName"].ToString();
contact.LastName = dr["LastName"].ToString();
contact.FullName = dr["FullName"].ToString();
contact.Phone = dr["Phone"].ToString();
contact.Adress = dr["Address"].ToString();
if (dr["Gender"].ToString().Trim().ToUpper().Equals("NỮ"))
{
contact.Gender = 0;
}
else
{
contact.Gender = 1;
}
contact.Birthday = DateTime.Parse(dr["Birthday"].ToString());
listContact.Add(contact);
}
else
{
contact = db.Contacts.FirstOrDefault(x => x.Email == dr["Email"].ToString().Trim());
contact.Email = dr["Email"].ToString().Trim();
contact.FirstName = dr["FirstName"].ToString();
contact.LastName = dr["LastName"].ToString();
contact.FullName = dr["FullName"].ToString();
contact.Phone = dr["Phone"].ToString();
contact.Adress = dr["Address"].ToString();
if (dr["Gender"].ToString().Trim().ToUpper().Equals("NỮ"))
{
contact.Gender = 0;
}
else
{
contact.Gender = 1;
}
contact.Birthday = DateTime.Parse(dr["Birthday"].ToString());
}
}
if (listContact.Count > 0)
{
db.Contacts.AddRange(listContact);
}
db.SaveChanges();
gridView.DataBind();
}
else
{
String message = "Bạn chưa chọn file hoặc file này đang được mở!";
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + message + "');", true);
}
}
catch (Exception v_e)
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + HandleException.SqlExcforContact(v_e) + "');", true);
Debugger.Log(1, "Send Mail", "Failed: " + v_e);
}
}
/// <summary>
/// 读取附件2
/// </summary>
/// <param name="filePath">附件路径</param>
/// <returns></returns>
private DataTable readExcelTemplate2(string filePath)
{
var dt2 = ImportExcel.ReadExcelToDataSet(filePath, "附件2").Tables[0];
var dicCity = QueryHelper.queryProvinceOfCity();
var dtTemp2 = new DataTable();
dtTemp2.Columns.Add("CLXZ", Type.GetType("System.String"));
dtTemp2.Columns.Add("GCCS", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLYXDW", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLZL", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLYT", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLXH", Type.GetType("System.String"));
dtTemp2.Columns.Add("EKGZ", Type.GetType("System.String"));
dtTemp2.Columns.Add("GGPC", Type.GetType("System.String"));
dtTemp2.Columns.Add("VIN", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLPZ", Type.GetType("System.String"));
dtTemp2.Columns.Add("SQBZBZ", Type.GetType("System.String"));
dtTemp2.Columns.Add("GMJG", Type.GetType("System.String"));
dtTemp2.Columns.Add("FPHM", Type.GetType("System.String"));
dtTemp2.Columns.Add("FPSJ", Type.GetType("System.String"));
dtTemp2.Columns.Add("XSZSJ", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLSFYCJDR", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCDTXX_XH", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_DTXH", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCDTXX_SCQY", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_DTSCQY", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCZXX_XH", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_CXXH", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCZXX_ZRL", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_DRZRL", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCZXX_SCQY", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_DRZSCQY", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCZXX_XTJG", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_XTJG", Type.GetType("System.String"));
dtTemp2.Columns.Add("DCZXX_ZBNX", Type.GetType("System.String"));
dtTemp2.Columns.Add("CJDRXX_ZBNX", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLSFYQDDJ2", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_XH_1", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_XH_2", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_EDGL_1", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_EDGL_2", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_SCQY_1", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_SCQY_2", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_XTJG_1", Type.GetType("System.String"));
dtTemp2.Columns.Add("QDDJXX_XTJG_2", Type.GetType("System.String"));
dtTemp2.Columns.Add("CLSFYRLDC", Type.GetType("System.String"));
dtTemp2.Columns.Add("RLDCXX_XH", Type.GetType("System.String"));
dtTemp2.Columns.Add("RLDCXX_EDGL", Type.GetType("System.String"));
dtTemp2.Columns.Add("RLDCXX_SCQY", Type.GetType("System.String"));
dtTemp2.Columns.Add("RLDCXX_GMJG", Type.GetType("System.String"));
dtTemp2.Columns.Add("RLDCXX_ZBNX", Type.GetType("System.String"));
dtTemp2.Columns.Add("JZNF", Type.GetType("System.String"));
dtTemp2.Columns.Add("GCSF", Type.GetType("System.String"));
dtTemp2.Columns.Add("FPTP", Type.GetType("System.String"));
dtTemp2.Columns.Add("FPTP_PICTURE", Type.GetType("System.String"));
dtTemp2.Columns.Add("XSZTP", Type.GetType("System.String"));
dtTemp2.Columns.Add("XSZTP_PICTURE", Type.GetType("System.String"));
for (int i = 4; i < dt2.Rows.Count; i++)
{
if (!Regex.IsMatch(dt2.Rows[i][0].ToString().Trim(), @"^[+-]?\d*$"))
{
break;
}
dtTemp2.Rows.Add();
dtTemp2.Rows[i - 4][0] = dt2.Rows[i][1].ToString().Trim();
dtTemp2.Rows[i - 4][1] = dt2.Rows[i][2].ToString().Trim();
dtTemp2.Rows[i - 4][2] = dt2.Rows[i][3].ToString().Trim();
dtTemp2.Rows[i - 4][3] = dt2.Rows[i][4].ToString().Trim();
dtTemp2.Rows[i - 4][4] = dt2.Rows[i][5].ToString().Trim();
dtTemp2.Rows[i - 4][5] = dt2.Rows[i][6].ToString().Trim();
dtTemp2.Rows[i - 4][6] = dt2.Rows[i][7].ToString().Trim();
dtTemp2.Rows[i - 4][7] = dt2.Rows[i][8].ToString().Trim();
dtTemp2.Rows[i - 4][8] = dt2.Rows[i][9].ToString().Trim();
dtTemp2.Rows[i - 4][9] = dt2.Rows[i][10].ToString().Trim();
dtTemp2.Rows[i - 4][10] = dt2.Rows[i][11].ToString().Trim();
dtTemp2.Rows[i - 4][11] = dt2.Rows[i][12].ToString().Trim();
dtTemp2.Rows[i - 4][12] = dt2.Rows[i][13].ToString().Trim();
dtTemp2.Rows[i - 4][13] = String.Format("{0}/{1}/{2}", dt2.Rows[i][14].ToString().Trim(), dt2.Rows[i][15].ToString().Trim().Length == 2 ? dt2.Rows[i][15].ToString().Trim() : "0" + dt2.Rows[i][15].ToString().Trim(), dt2.Rows[i][16].ToString().Trim().Length == 2 ? dt2.Rows[i][16].ToString().Trim() : "0" + dt2.Rows[i][16].ToString().Trim());
dtTemp2.Rows[i - 4][14] = String.Format("{0}/{1}/{2}", dt2.Rows[i][17].ToString().Trim(), dt2.Rows[i][18].ToString().Trim().Length == 2 ? dt2.Rows[i][18].ToString().Trim() : "0" + dt2.Rows[i][18].ToString().Trim(), dt2.Rows[i][19].ToString().Trim().Length == 2 ? dt2.Rows[i][19].ToString().Trim() : "0" + dt2.Rows[i][19].ToString().Trim());
dtTemp2.Rows[i - 4][15] = dt2.Rows[i][20].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][21].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][22].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][23].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][24].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][25].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][26].ToString().Trim().Split('|').Length > 1 ? "是" : "否";
dtTemp2.Rows[i - 4][16] = dt2.Rows[i][20].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][20].ToString().Trim().Split('|')[0] : dt2.Rows[i][20].ToString().Trim();
dtTemp2.Rows[i - 4][17] = dt2.Rows[i][20].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][20].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][18] = dt2.Rows[i][21].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][21].ToString().Trim().Split('|')[0] : dt2.Rows[i][21].ToString().Trim();
dtTemp2.Rows[i - 4][19] = dt2.Rows[i][21].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][21].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][20] = dt2.Rows[i][22].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][22].ToString().Trim().Split('|')[0] : dt2.Rows[i][22].ToString().Trim();
dtTemp2.Rows[i - 4][21] = dt2.Rows[i][22].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][22].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][22] = dt2.Rows[i][23].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][23].ToString().Trim().Split('|')[0] : dt2.Rows[i][23].ToString().Trim();
dtTemp2.Rows[i - 4][23] = dt2.Rows[i][23].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][23].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][24] = dt2.Rows[i][24].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][24].ToString().Trim().Split('|')[0] : dt2.Rows[i][24].ToString().Trim();
dtTemp2.Rows[i - 4][25] = dt2.Rows[i][24].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][24].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][26] = dt2.Rows[i][25].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][25].ToString().Trim().Split('|')[0] : dt2.Rows[i][25].ToString().Trim();
dtTemp2.Rows[i - 4][27] = dt2.Rows[i][25].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][25].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][28] = dt2.Rows[i][26].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][26].ToString().Trim().Split('|')[0] : dt2.Rows[i][26].ToString().Trim();
dtTemp2.Rows[i - 4][29] = dt2.Rows[i][26].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][26].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][30] = dt2.Rows[i][27].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][28].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][29].ToString().Trim().Split('|').Length > 1 || dt2.Rows[i][30].ToString().Trim().Split('|').Length > 1 ? "是" : "否";
dtTemp2.Rows[i - 4][31] = dt2.Rows[i][27].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][27].ToString().Trim().Split('|')[0] : dt2.Rows[i][27].ToString().Trim();
dtTemp2.Rows[i - 4][32] = dt2.Rows[i][27].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][27].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][33] = dt2.Rows[i][28].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][28].ToString().Trim().Split('|')[0] : dt2.Rows[i][28].ToString().Trim();
dtTemp2.Rows[i - 4][34] = dt2.Rows[i][28].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][28].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][35] = dt2.Rows[i][29].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][29].ToString().Trim().Split('|')[0] : dt2.Rows[i][29].ToString().Trim();
dtTemp2.Rows[i - 4][36] = dt2.Rows[i][29].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][29].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][37] = dt2.Rows[i][30].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][30].ToString().Trim().Split('|')[0] : dt2.Rows[i][30].ToString().Trim();
dtTemp2.Rows[i - 4][38] = dt2.Rows[i][30].ToString().Trim().Split('|').Length > 1 ? dt2.Rows[i][30].ToString().Trim().Split('|')[1] : string.Empty;
dtTemp2.Rows[i - 4][39] = !string.IsNullOrEmpty(dt2.Rows[i][31].ToString().Trim()) || !string.IsNullOrEmpty(dt2.Rows[i][32].ToString().Trim()) || !string.IsNullOrEmpty(dt2.Rows[i][33].ToString().Trim()) || !string.IsNullOrEmpty(dt2.Rows[i][34].ToString().Trim()) || !string.IsNullOrEmpty(dt2.Rows[i][35].ToString().Trim()) ? "是" : "否";
dtTemp2.Rows[i - 4][40] = dt2.Rows[i][31].ToString().Trim();
dtTemp2.Rows[i - 4][41] = dt2.Rows[i][32].ToString().Trim();
dtTemp2.Rows[i - 4][42] = dt2.Rows[i][33].ToString().Trim();
dtTemp2.Rows[i - 4][43] = dt2.Rows[i][34].ToString().Trim();
dtTemp2.Rows[i - 4][44] = dt2.Rows[i][35].ToString().Trim();
dtTemp2.Rows[i - 4][45] = dt2.Rows[0][0].ToString().Trim().Length > 4 ? dt2.Rows[0][0].ToString().Trim().Substring(0, 4) : string.Empty;
dtTemp2.Rows[i - 4][46] = dicCity.ContainsKey(dt2.Rows[i][2].ToString().Trim()) ? dicCity[dt2.Rows[i][2].ToString().Trim()] : string.Empty;
string vin = dt2.Rows[i][9].ToString().Trim();
dtTemp2.Rows[i - 4][47] = File.Exists(Path.Combine(Utils.billImage, String.Format("车辆发票VIN-{0}.jpg", vin))) ? String.Format("车辆发票VIN-{0}.jpg", vin) : string.Empty;
dtTemp2.Rows[i - 4][48] = File.Exists(Path.Combine(Utils.billImage, String.Format("车辆发票VIN-{0}.jpg", vin))) ? String.Format("车辆发票VIN-{0}.jpg", vin) : string.Empty;
dtTemp2.Rows[i - 4][49] = File.Exists(Path.Combine(Utils.driveImage, String.Format("行驶证VIN-{0}.jpg", vin))) ? String.Format("行驶证VIN-{0}.jpg", vin) : string.Empty;
dtTemp2.Rows[i - 4][50] = File.Exists(Path.Combine(Utils.driveImage, String.Format("行驶证VIN-{0}.jpg", vin))) ? String.Format("行驶证VIN-{0}.jpg", vin) : string.Empty;
}
return(dtTemp2);
}
//点击确定新建项目
private void button1_Click(object sender, EventArgs e)
{
//MainForm.nowClassNum = 1;
string codenamed = textBox1.Text; //得到项目代号
string aliases = textBox2.Text; //得到项目别名
int numberx = Int32.Parse(numericUpDown1.Value.ToString()); //得到项目级别
int predioid = 0;
string pcheckbox = "";
//List<string> pcheckbox = new List<string>();
if (radioButton1.Checked)
{
predioid = 1;
}
else if (radioButton2.Checked)
{
predioid = 2;
}
else if (radioButton3.Checked)
{
predioid = 3;
}
if (codenamed != "" && aliases != "" && numberx > 0)
{
ZI.MainForm.nowProject = codenamed;
ZI.MainForm.nowProjectAlias = aliases;
ZI.MainForm.nowClassNum = numberx;
ZI.MainForm.nowIndex = predioid;
ZI.DbUtil.nowProject = codenamed;
ZI.DbUtil.nowProjectAlias = aliases;
ZI.DbUtil.nowClassNum = numberx;
ZI.DbUtil.nowIndex = predioid;
ZI.Utils.ImportExcel.nowProject = codenamed;
ZI.Utils.ImportExcel.nowProjectAlias = aliases;
ZI.Utils.ImportExcel.nowClassNum = numberx;
ZI.Utils.ImportExcel.nowIndex = predioid;
Boolean ds1 = ZI.DbUtil.isexist("indexproject");
if (ds1 == false)
{
string dssql = "CREATE TABLE [dbo].[indexproject]([projectid] [int] IDENTITY(1,1) NOT NULL,[projectname] [nvarchar](50) NOT NULL, [projectaliasname] [nvarchar](50) NOT NULL, [classnum] [int] NOT NULL, [cycle] [int] NOT NULL, [sort] [nvarchar](50) NOT NULL) ON [PRIMARY]";
SqlCommand comproject = new SqlCommand(dssql, getConnection());
comproject.ExecuteNonQuery();
}
String sql = "";
string msql = "";
string dsql = "";
string csql = "";
string bsql = "";
string esql = "";
string fullsql = "";
String justone = "select count(*) from indexproject where projectname='" + codenamed + "'";
SqlCommand comjust = new SqlCommand(justone, getConnection());
SqlDataReader reader = comjust.ExecuteReader();
int cnt = 0;
while (reader.Read())
{
cnt = int.Parse(reader[0].ToString());
}
conn.Close();
if (cnt == 0)
{
//if (this.checkBox1.Checked && this.checkBox2.Checked && this.checkBox3.Checked)
//{
for (int i = 1; i <= numberx; i++)
{
if (i < numberx)
{
msql += "[Class_" + i + "_Code] AS (substring([Class_Code],(1),(" + i * 2 + "))),";
}
if (i > 1)
{
dsql += "[Class_" + (i - 1) + "_ID] [nvarchar](2) NOT NULL,";
if (i > 2)
{
bsql += "[Class_" + (i - 1) + "_ID]+";
}
esql = "[ID]+" + bsql + "[Class_1_ID]";
csql += "[Class_" + i + "_Code] AS (" + esql + "),";
}
fullsql += "[Class_" + i + "_Code] [nvarchar](" + i * 2 + ") NOT NULL,[Class_" + i + "_Name] [nvarchar](80) NOT NULL,";
int a = i;
string asql = "";
for (int j = i - 1; j > 0; j--)
{
asql += "[Class_" + j + "_Code] AS (substring([Class_Code],(1),(" + j * 2 + "))),";
}
//dsql += "[ID_" + i + "] [nvarchar](" + i * 2 + ") NOT NULL,[Name_" + i + "] [nvarchar](100) NOT NULL,[WeightValue"+i+"] [decimal](18, 5) NOT NULL,";
if (this.checkBox1.Checked)
{
//pcheckbox.Add("Day");
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Index_Class_" + i + "_Day]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar]("+ i * 2 + ") NOT NULL,[AveragePrice] [decimal](18, 5) NULL,[BasePrice] [decimal](18, 5) NULL, [Class_Index] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL, [RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,"+ asql + "PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_PriceRing_Index_Class_" + i + "_Day]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar](" + i * 2 + ") NOT NULL,[Class_Index] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL," + asql + ") ON [PRIMARY]";
}
if (this.checkBox2.Checked)
{
// pcheckbox.Add("Week");
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Index_Class_" + i + "_Week]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar](" + i * 2 + ") NOT NULL,[AveragePrice] [decimal](18, 5) NULL,[BasePrice] [decimal](18, 5) NULL, [Class_Index] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL, [RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,"+ asql + "PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_PriceRing_Index_Class_" + i + "_Week]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar](" + i * 2 + ") NOT NULL,[Class_Index] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL," + asql + ") ON [PRIMARY]";
}
if (this.checkBox3.Checked)
{
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Index_Class_" + i + "_Month]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar]("+ i * 2 + ") NOT NULL,[AveragePrice] [decimal](18, 5) NULL,[BasePrice] [decimal](18, 5) NULL, [Class_Index] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL, [RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,"+ asql + "PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_PriceRing_Index_Class_" + i + "_Month]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar](" + i * 2 + ") NOT NULL,[Class_Index] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL," + asql + ") ON [PRIMARY]";
}
sql += "CREATE TABLE [dbo].[" + codenamed + "_Class_" + i + "_Weight]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NULL,[Class_Code] [nvarchar](" + i * 2 + ") NOT NULL,[WeightValue] [decimal](18, 5) NOT NULL,[state] [nvarchar](1) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL," + msql + "PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Class_" + i + "]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL," + dsql + "[ID] [nvarchar](2) NOT NULL,[Name] [nvarchar](100) NOT NULL,[DisplayName] [nvarchar](100) NULL,[Alias] [nvarchar](100) NULL,[State] [nvarchar](1) NULL,[Comments] [nvarchar](max) NULL," + csql + " CONSTRAINT [PK_" + codenamed + "_Class_" + i + "] PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
}
if (this.checkBox1.Checked)
{
pcheckbox += "Day";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Etl_Day]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar]("+ numberx * 2 + ") NOT NULL,[AveragePrice] [real] NULL, [RingMove] [real] NULL, [RingMoveRate] [real] NULL, [state] [nvarchar](1) NULL, [CreateBy] [nvarchar](20) NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,"+ msql + "PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Index_Day]( [Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[PriceIndex] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_PriceRing_Index_Day]( [Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[PriceIndex] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
}
if (this.checkBox2.Checked)
{
pcheckbox += "Week";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Etl_Week]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar](" + numberx * 2 + ") NOT NULL,[AveragePrice] [real] NULL, [RingMove] [real] NULL, [RingMoveRate] [real] NULL, [state] [nvarchar](1) NULL, [CreateBy] [nvarchar](20) NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL," + msql + "PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Index_Week]( [Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[PriceIndex] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_PriceRing_Index_Week]( [Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[PriceIndex] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
}
if (this.checkBox3.Checked)
{
pcheckbox += "Month";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Etl_Month]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar](" + numberx * 2 + ") NOT NULL,[AveragePrice] [real] NULL,[RingMove] [real] NULL, [RingMoveRate] [real] NULL, [state] [nvarchar](1) NULL, [CreateBy] [nvarchar](20) NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,"+ msql + "PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Index_Month]( [Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[PriceIndex] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_PriceRing_Index_Month]( [Index_ID] [bigint] IDENTITY(1,1) NOT NULL,[CreatedDate] [datetime] NOT NULL,[PriceIndex] [decimal](18, 5) NULL,[RingMove] [decimal](18, 5) NULL,[RingMoveRate] [decimal](18, 5) NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL,PRIMARY KEY CLUSTERED ( [Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
}
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Pick]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [MarketName] [nvarchar](80) NULL,[ShopName] [nvarchar](80) NULL,[BandName] [nvarchar](80) NULL, [Style] [nvarchar](80) NULL,[DisplayName] [nvarchar](80) NULL,[Class_Code] [nvarchar]("+ numberx * 2 + ") NULL," + msql + "[Price] [real] NULL,[CreatedDate] [datetime] NULL,[CreatedWeekend] [datetime] NULL, [CreatedMonth] [datetime] NULL, [ApprovalDate] [datetime] NULL, [ApprovalBy] [nvarchar](20) NULL,[Approvaled] [int] NULL, CONSTRAINT [PK_"+ codenamed + "_PriceWeek_Pick] PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Price_Ods_Base]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL,[Class_Code] [nvarchar]("+ numberx * 2 + ") NOT NULL,[AveragePrice] [decimal](18, 2) NOT NULL,[state] [nvarchar](1) NULL,[CreateBy] [nvarchar](20) NULL,[ApprovalDate] [datetime] NULL,[ApprovalBy] [nvarchar](20) NULL,[Comments] [nvarchar](max) NULL," + msql + "PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "CREATE TABLE [dbo].[" + codenamed + "_Product_WithFullName]([Index_ID] [bigint] IDENTITY(1,1) NOT NULL," + fullsql + "[DisplayName] [nvarchar](80) NULL,[Measure] [nvarchar](30) NULL,CONSTRAINT [PK__" + codenamed + "__Product_WithFullName] PRIMARY KEY CLUSTERED ([Index_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
sql += "insert into indexproject (projectname,projectaliasname,classnum,cycle,sort) values ('" + codenamed + "','" + aliases + "'," + numberx + "," + predioid + ",'" + pcheckbox + "')";
// }
ZI.MainForm.sort = pcheckbox;
ZI.DbUtil.sort = pcheckbox;
SqlCommand command = new SqlCommand(sql, getConnection());
int count = command.ExecuteNonQuery();
if (MessageBox.Show(this, "项目创建成功。现在导入分类权重表？", "确认导入", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2).Equals(DialogResult.Yes))
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = "..\\";
openFileDialog.Filter = "Excel文件|*.xls";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
openFileDialog.Multiselect = false;
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
String[] files = openFileDialog.FileNames;
foreach (string file in files)
{//导入分类权重
//this.mainStatusLabel.Text = "正在处理文件：" + file;
//this.statusStrip1.Update();
ImportExcel.importProductCodeExcel(file);
ImportExcel.importProductWeightExcel(file);
}
MessageBox.Show(this, "导入成功。", "确认", MessageBoxButtons.OK);
this.Dispose();
}
else
{
MessageBox.Show(this, "请到“基础数据维护菜单”中导入项目的分类权重。", "确认", MessageBoxButtons.OK);
this.Dispose();
}
}
else
{
MessageBox.Show(this, "请到“基础数据维护菜单”中导入项目的分类权重。", "确认", MessageBoxButtons.OK);
this.Dispose();
}
}
else
{
MessageBox.Show("该用户名已经存在,请重新输入");
}
}
else if (codenamed == "")
{
MessageBox.Show("请填写项目代号");
}
else if (aliases == "")
{
MessageBox.Show("请填写项目别名");
}
else if (numberx == 0)
{
MessageBox.Show("请填写项目分类级数");
}
//else if (pcheckbox == "")
//{
// MessageBox.Show("请选择一个指数类别");
//}
}
public ImportExcelLogic()
{
access = new ImportExcel();
}
// 导入Excel
private void barBtnImport_ItemClick(object sender, ItemClickEventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
Dictionary <string, string> error = new Dictionary <string, string>();
string msg = string.Empty;
try
{
SplashScreenManager.ShowForm(typeof(DevWaitForm));
// STEP1：导入系统，验证单元格格式
var ds = ImportExcel.ReadExcelToDataSet(openFileDialog1.FileName);
for (int i = 0; i < ds.Tables["TEMPLATE"].Columns.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull))
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.String))
{
MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确，应为文本格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
}
// STEP2：替换列名，验证参数的数值
var dt = ImportExcel.SwitchRLLXPARAMColumnName(ds);
if (dt != null)
{
string sc_ocn_error = string.Empty;
error = DataVerifyHelper.VerifyRLLXPARAMData(dt);
if (error.Count == 0)
{
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
// STEP3：验证无误，导入系统数据库
foreach (DataRow dr in dt.Rows)
{
if (sc_ocn_error.Equals(dr["SC_OCN"].ToString()))
{
continue;
}
try
{
string exist = OracleHelper.ExecuteScalar(trans, string.Format("SELECT COUNT(*) FROM OCN_RLLX_PARAM_ENTITY WHERE OPERATION!='4' AND SC_OCN='{0}' AND CSBM='{1}'", dr["SC_OCN"], dr["CSBM"])).ToString();
int existNum = string.IsNullOrEmpty(exist) ? 0 : Convert.ToInt32(exist);
if (existNum > 0)
{
error.Add(dr["SC_OCN"].ToString().Trim(), "系统已经存在改生产OCN的燃料参数数据！");
sc_ocn_error = dr["SC_OCN"].ToString();
continue;
}
OracleParameter[] parameters =
{
new OracleParameter("SC_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("CSBM", OracleDbType.NVarchar2, 255),
new OracleParameter("CSMC", OracleDbType.NVarchar2, 255),
new OracleParameter("RLLX", OracleDbType.NVarchar2, 255),
new OracleParameter("CSZ", OracleDbType.NVarchar2, 255),
new OracleParameter("OPERATION", OracleDbType.NVarchar2, 255),
new OracleParameter("CREATE_TIME", OracleDbType.Date),
new OracleParameter("CREATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("UPDATE_TIME", OracleDbType.Date),
new OracleParameter("UPDATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("VERSION", OracleDbType.Int32),
};
parameters[0].Value = dr["SC_OCN"];
parameters[1].Value = dr["CSBM"];
parameters[2].Value = dr["CSMC"];
parameters[3].Value = dr["RLLX"];
parameters[4].Value = dr["CSZ"];
parameters[5].Value = "0";
parameters[6].Value = System.DateTime.Today;
parameters[7].Value = Utils.localUserId;
parameters[8].Value = System.DateTime.Today;
parameters[9].Value = Utils.localUserId;
parameters[10].Value = 0;
OracleHelper.ExecuteNonQuery(trans, "Insert into OCN_RLLX_PARAM_ENTITY (SC_OCN,CSBM,CSMC,RLLX,CSZ,OPERATION,CREATE_TIME,CREATE_ROLE,UPDATE_TIME,UPDATE_ROLE,VERSION) values (:SC_OCN,:CSBM,:CSMC,:RLLX,:CSZ,:OPERATION,:CREATE_TIME,:CREATE_ROLE,:UPDATE_TIME,:UPDATE_ROLE,:VERSION)", parameters);
}
catch (Exception ex)
{
error.Add(String.Format("{0} {0}", dr["SC_OCN"], dr["CSBM"]), ex.Message);
sc_ocn_error = dr["SC_OCN"].ToString();
continue;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
// STEP4：处理无误，处理完成的文件
if (error.Count == 0)
{
var destFolder = Path.Combine(Path.GetDirectoryName(openFileDialog1.FileName), DateTime.Today.ToLongDateString() + "-燃料参数数据-Imported");
Directory.CreateDirectory(destFolder);
try
{
File.Move(openFileDialog1.FileName, Path.Combine(destFolder, String.Format("Imported-{0}{1}", Path.GetFileNameWithoutExtension(openFileDialog1.FileName), Path.GetExtension(openFileDialog1.FileName))));
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Excel处理操作异常：导入完成，{0}", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
}
foreach (KeyValuePair <string, string> kvp in error)
{
msg += String.Format("{0}\r\n{1}\r\n", kvp.Key, kvp.Value);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Excel导入操作异常：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
SplashScreenManager.CloseForm();
}
MessageForm msgForm = new MessageForm(msg + String.Format("\r\n{0}Excel导入操作完成", Path.GetFileNameWithoutExtension(openFileDialog1.FileName)))
{
Text = "燃料参数导入信息"
};
msgForm.Show();
SearchLocal(1);
}
}
private void Page_Load(object sender, EventArgs e)
{
string url = null;
string str2 = null;
XmlDocument xdoc = null;
try
{
string str8;
QuerySql sql;
SqlToTree tree;
str2 = base.Request.Params["key"];
if ((str2 == null) || (str2.Length == 0))
{
str2 = base.Request.QueryString.ToString();
}
if (str2 == "HelloWorld")
{
base.Response.ContentType = ("text/html;charset=UTF-8");
base.Response.Write("HelloWorld");
return;
}
Logger.debug("后台 key=" + str2);
if (str2 == "logger")
{
string src = new StreamReader(base.Request.InputStream).ReadToEnd();
Logger.debug("前台:" + Escape.unescape(src));
return;
}
this.initConfigFileEbiao(base.Request);
ConnectionConfig.initConfigFile(base.Request, this.Session);
string str4 = null;
string dsName = base.Request.Params["datasourceName"];
ConnectionConfig connConfig = null;
connConfig = new ConnectionConfig(dsName);
if (str2 == "DbToFile")
{
new DbStru(connConfig, null, base.Request).DbToFile(base.Response);
return;
}
bool flag = ((((str2 == "loadFileStream") || (str2 == "getTempImage")) ||
((str2 == "saveasExcelAll") || (str2 == "saveasExcel"))) ||
(((str2 == "saveasPdf") || (str2 == "run")) ||
((str2 == "calcStr") || (str2 == "importExcel")))) || (str2 == "genHtmlFile");
RunReport report = new RunReport(base.Request, this.Session, connConfig.getDBOperator());
switch (str2)
{
case "loadFileStream":
report.loadFileStream(base.Request, base.Response);
return;
case "getTempImage":
report.getTempImage(base.Request, base.Response);
return;
case "saveasExcelAll":
report.saveasExcel(base.Request, base.Response, false);
return;
case "saveasExcel":
report.saveasExcel(base.Request, base.Response, true);
return;
case "saveasPdf":
report.saveasPdf(base.Request, base.Response);
return;
case "run":
url = report.run(base.Request);
break;
case "calcStr":
url = report.calcStr(base.Request);
break;
case "importExcel":
{
string sDest = base.Request["excelfile"];
sDest = PathTool.getRealPath(base.Request, FileAction.basePath + sDest);
string str7 = base.Request["spath"];
url = ImportExcel.FileConvert(PathTool.getRealPath(base.Request, FileAction.basePath + str7),
sDest);
break;
}
case "genHtmlFile":
Logger.debug("开始成批运算报表:");
url = report.genHtmlFile(base.Request);
break;
}
if (flag)
{
goto Label_0BE9;
}
if (((((str2 != "setSession") && (str2 != "getSession")) &&
((str2 != "uploadFile") && (str2 != "readImage"))) &&
((str2 != "readClob") && (str2 != "saveFile"))) && (str2 != "zkLoadMod"))
{
str4 = new StreamReader(base.Request.InputStream).ReadToEnd();
Logger.debug("XML串: " + str4);
if ((str2 != "saveRunInfo") && (str2 != "readDomainRes"))
{
xdoc = new XmlDocument();
xdoc.LoadXml(str4);
}
}
if (str2 != "loadingBatchAction")
{
goto Label_05FE;
}
StringBuilder builder = new StringBuilder("<root>");
int num = 0;
Label_041D:
if (num >= xdoc.DocumentElement.ChildNodes.Count)
{
goto Label_05E9;
}
XmlDocument document2 = new XmlDocument();
document2.LoadXml(xdoc.DocumentElement.ChildNodes.Item(num).OuterXml);
try
{
dsName = xdoc.DocumentElement.ChildNodes.Item(num).Attributes["datasourceName"].Value;
}
catch (Exception)
{
}
goto Label_0573;
Label_048C:
builder.Append(sql.fillcombox());
goto Label_055B;
Label_04A0:
if (str8 == "getDsns")
{
builder.Append(connConfig.getDsns());
}
else if (str8 == "fc_select")
{
builder.Append(sql.fc_select());
}
else if (str8 == "dataset_fields1")
{
builder.Append(sql.dataset_fields1());
}
else if (str8 == "dataset_select")
{
builder.Append(sql.dataset_select());
}
else if (str8 == "sqltotreedata")
{
builder.Append(tree.sqltotreedata());
}
else if (str8 == "getTreeXml")
{
builder.Append(tree.getTreeXml());
}
Label_055B:
builder.Append("</root>");
num++;
goto Label_041D;
Label_0573:
str8 = xdoc.DocumentElement.ChildNodes.Item(num).Attributes["key"].Value;
connConfig = new ConnectionConfig(dsName);
sql = new QuerySql(connConfig, document2, base.Request, this.Session);
tree = new SqlToTree(connConfig, document2);
builder.Append("<root>");
if (str8 != "fillcombox")
{
goto Label_04A0;
}
goto Label_048C;
Label_05E9:
builder.Append("</root>");
url = builder.ToString();
Label_05FE:
if (str2 == "getDsns")
{
url = connConfig.getDsns();
}
if (str2 == "isRunForm")
{
url = PermitForm.isRunForm(base.Request, this.Session, connConfig.getDBOperator());
}
if (str2 == "isRunReport")
{
url = PermitReport.isRunReport(base.Request, this.Session, connConfig.getDBOperator());
}
EbiaoFile file = new EbiaoFile(connConfig.getDBOperator(), xdoc, base.Request);
if (str2 == "saveFile")
{
url = file.saveFile();
}
if (str2 == "loadFile")
{
url = file.loadFile();
}
if (str2 == "ToInDs")
{
url = file.ToInDs();
}
QuerySql sql2 = new QuerySql(connConfig, xdoc, base.Request, this.Session);
if (str2 == "fillcombox")
{
url = sql2.fillcombox();
}
if (str2 == "sqltoxml")
{
url = sql2.sqltoxml();
}
if (str2 == "SqlToField")
{
url = sql2.SqlToField();
}
if (str2 == "fc_select")
{
url = sql2.fc_select();
}
if (str2 == "dataset_fields1")
{
url = sql2.dataset_fields1();
}
if (str2 == "dataset_select")
{
url = sql2.dataset_select();
}
if (str2 == "doUploadInfo")
{
url = sql2.doUploadInfo();
}
if (str2 == "crosstab")
{
url = sql2.crosstab();
}
ExportData data = new ExportData(xdoc, base.Request, this.Session);
if (str2 == "exportData")
{
data.run();
}
ImportData data2 = new ImportData(base.Request, this.Session, connConfig);
if (str2 == "importData")
{
url = data2.run();
}
WebDesign design = new WebDesign(connConfig, xdoc, base.Request);
if (str2 == "designdjsave")
{
url = design.designdjsave();
}
if (str2 == "loadClob")
{
url = design.loadClob();
}
cn.com.fcsoft.ajax.PathFile file2 = new cn.com.fcsoft.ajax.PathFile(xdoc);
if (str2 == "GetUrl")
{
url = file2.GetUrl(base.Request);
}
DbStru stru2 = new DbStru(connConfig, xdoc, base.Request);
if (str2 == "GetAllTables")
{
url = stru2.GetAllTables();
}
if (str2 == "GetFieldName")
{
url = stru2.GetFieldName();
}
if (str2 == "setDbStru")
{
url = stru2.setDbStru();
}
SubmitData data3 = new SubmitData(connConfig, xdoc, base.Request, this.Session);
if (str2 == "doSaveData")
{
url = data3.doSaveData();
}
FileAction action = new FileAction(xdoc, base.Request);
if (str2 == "readdesignhtml")
{
url = action.readdesignhtml();
}
if (str2 == "savedesignhtml")
{
url = action.savedesignhtml();
}
if (str2 == "genDjHtmlFile")
{
url = action.genDjHtmlFile();
}
if (str2 == "GetFileContent")
{
url = action.GetFileContent();
}
if (str2 == "DelUploadFile")
{
url = action.DelUploadFile();
}
if (str2 == "uploadFile")
{
url = action.uploadFile();
}
if (str2 == "fileExist")
{
url = action.fileExist();
}
MaxNo no = new MaxNo(connConfig, xdoc, this.Session);
if (str2 == "getMaxIntNo")
{
url = no.getMaxIntNo();
}
if (str2 == "getRecnum")
{
url = no.getRecnum();
}
if (str2 == "getAutoNum")
{
url = no.getAutoNum();
}
SqlToTree tree2 = new SqlToTree(connConfig, xdoc);
if (str2 == "sqltotreedata")
{
url = tree2.sqltotreedata();
}
if (str2 == "getTreeXml")
{
url = tree2.getTreeXml();
}
DoSession session = new DoSession(base.Request, this.Session);
if (str2 == "setSession")
{
url = session.setSession();
}
if (str2 == "getSession")
{
url = session.getSession();
}
cn.com.fcsoft.ajax.ImageField field = new cn.com.fcsoft.ajax.ImageField(connConfig, base.Request);
if (str2 == "readImage")
{
field.readImage(base.Response);
}
if (str2 == "writeImage")
{
url = field.writeImage();
}
ClobField field2 = new ClobField(connConfig, base.Request);
if (str2 == "readClob")
{
url = field2.readClob();
}
if (str2 == "writeClob")
{
url = field2.writeClob();
}
EformRole role = new EformRole
{
session = this.Session,
request = base.Request,
xdoc = xdoc,
oDb = connConfig.getDBOperator()
};
if (str2 == "roleSet")
{
url = role.roleSet();
}
if (str2 == "roleCheckEmployee")
{
url = role.roleCheckEmployee();
}
if (str2 == "getAllPubParamValue")
{
url = role.getAllPubParamValue();
}
if (str2 == "batchExecSql")
{
if (EformRole.getPubParamValue(this.Session, "用户.ID") != "systemadmin")
{
throw new Exception("只有用户 admin 才有权做此操作!");
}
string str10 = xdoc.DocumentElement.ChildNodes.Item(0).InnerText;
DBOperator @operator = null;
IDbCommand command = null;
IDbTransaction transaction = null;
try
{
@operator = connConfig.getDBOperator();
@operator.Open();
command = @operator.Connection.CreateCommand();
command.Connection = (@operator.Connection);
transaction = @operator.Connection.BeginTransaction();
command.Transaction = (transaction);
command.CommandText = (str10);
command.ExecuteNonQuery();
transaction.Commit();
command.Dispose();
@operator.Close();
}
catch (Exception exception)
{
try
{
transaction.Rollback();
}
catch (Exception)
{
}
throw exception;
}
finally
{
try
{
@operator.Close();
}
catch (Exception)
{
}
}
}
}
catch (Exception exception2)
{
Logger.error(exception2.StackTrace);
url = JSON.errInfoToJson(exception2);
}
Label_0BE9:
if ((url != null) || (str2 != "readImage"))
{
/*
* Bill.aspx?key=run&spath=/rpt/s_simplelist.htm&tempfilepath=/tmp/file/&pageno=1&cacheid=&e_paramid=
* ebrun.htm?name=s_simplelist
*/
base.Response.ContentType = ("text/html;charset=UTF-8");
File.WriteAllText(@"E:\DB\PlatForm\FlexCell.NET\ebiao\bin\cleaned\src\Web\" + str2 + "_test.htm", url);
base.Response.Write(url);
}
}
public FDict(FClass host, FieldWrap define, ImportExcel excel) : base(host, define)
{
_key = define.GetKeyDefine();
_value = define.GetValueDefine();
excel.GetDict(this, define);
}
public ActionResult ImportData(string cls)
{
//string table = "Department";
IList<ImportResult> retVal = new List<ImportResult>();
string path = Server.MapPath("/") + @"Files\Temp\";
////文件上传，一次上传1M的数据，防止出现大文件无法上传
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++) {
//保存上传的文件
File file = SaveHttpPostFile(files[i], path);
if (file.Size == 0) {
continue;
}
Type type = Type.GetType("EasyJob.Pojo.Pojo." + cls + ",EasyJob.Pojo");
ImportExcel ie = new ImportExcel(file.RealPath, type);
IList<object> vals = ie.List(type);
//数据存储
ISession s = null;
ITransaction trans = null;
try {
s = HibernateOper.GetCurrentSession();
trans = s.BeginTransaction();
foreach (object val in vals) {
if (val is TbBase && val is IExists) {
ImportResult ir = new ImportResult();
TbBase tbVal = (TbBase)val;
try {
bool isExists = false;//是否存在
IExists valExists = (IExists)tbVal;
isExists = valExists.IsExists(s);
if (tbVal.ImportType.Equals("添加")) {
if (isExists) {
throw new Exception("Val is exists");
}
s.Save(tbVal);
} else if (tbVal.ImportType.Equals("修改")) {
if (!isExists) {
throw new Exception("Val is not exists");
}
//s.Update(tbVal);
s.Merge(tbVal);
}
ir.Success = true;
} catch (Exception e) {
ir.Success = false;
ir.Msg = e.Message;
} finally {
retVal.Add(ir);
}
}
}
trans.Commit();
} catch (Exception e) {
if (trans != null) {
trans.Rollback();
}
throw e;
}
}
return Json(retVal);
}
protected void Button3_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();// strWhere = "1=1";
sb.Append("1=1");
string browser = Request.Browser.Browser;
if (!string.IsNullOrEmpty(Request["code"]))
{
sb.AppendFormat(" and CWB_Code like '%{0}%'", Request["code"].ToString().Trim());//;;//存 客户名称
}
if (!string.IsNullOrEmpty(Request["cname"]))
{
sb.AppendFormat(" and b.CC_Name like '%{0}%'", Request["cname"].ToString().Trim());//存 客户名称
}
if (!string.IsNullOrEmpty(Request["uname"]))
{
sb.AppendFormat(" and d.UserName like '%{0}%'", Request["uname"].ToString().Trim());//存 用户名称
}
if (!string.IsNullOrEmpty(Request["stime"]))
{
DateTime dtStime = Convert.ToDateTime("1980-01-01");
DateTime.TryParse(Request["stime"].ToString().Trim(), out dtStime);
sb.AppendFormat(" and CWB_CreateTime > '{0}'", dtStime.AddDays(-1));//保存操作开始时间
}
if (!string.IsNullOrEmpty(Request["etime"]))
{
DateTime dtEtime = Convert.ToDateTime("2250-01-01");
DateTime.TryParse(Request["etime"].ToString().Trim(), out dtEtime);
sb.AppendFormat(" and CWB_CreateTime < '{0}'", dtEtime.AddDays(1)); //保存操作结束时间
}
if (!string.IsNullOrEmpty(Request["Owner"])) //存 客户所属工程师
{
sb.AppendFormat(" and CWB_Description = '{0}'", Request["Owner"].ToString().Trim());
}
if (!string.IsNullOrEmpty(Request["sltType"]) && Request["sltType"].ToString().Trim() != "0")
{
sb.AppendFormat(" and CWB_Type = '{0}'", Convert.ToInt16(Request["sltType"].ToString().Trim()));
}
if (!string.IsNullOrEmpty(Request["status"]))
{
int intStatus = 0;
int.TryParse(Request["status"].ToString().Trim(), out intStatus);
int intStatus1 = 27;
int.TryParse(ConfigurationManager.AppSettings["workbillStatusAll"].ToString(), out intStatus1);
if (intStatus1 == intStatus)
{
}
else
{
sb.AppendFormat(" and CWB_Status = '{0}'", intStatus);
}
}
DataTable dt = new DataTable();
HYTD.BLL.Call_WorkBillBLL bll = new HYTD.BLL.Call_WorkBillBLL();
dt = bll.ImportExcelCustomerWorkBill(sb.ToString()).Tables[0];
NPOI.SS.UserModel.IWorkbook book1 = new ImportExcel().ImportCustomerWorkBill(dt, "客户服务记录");
string strFile = string.Empty;
string filename = string.Empty;
string strPath = HttpContext.Current.Server.MapPath("/" + System.Configuration.ConfigurationManager.AppSettings["CustomerFiles"].ToString().Replace("\\", "/") + "/")
+ "\\" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0') + "\\";
if (dt.Rows.Count > 0)
{
strFile = dt.Rows[0]["客户名称"].ToString();
filename = strPath + dt.Rows[0]["客户名称"].ToString() + ".xls";
if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(filename)))
{
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(filename));
}
try
{
System.IO.FileStream file1 = new System.IO.FileStream(filename, System.IO.FileMode.Create);
book1.Write(file1);
file1.Dispose();
}
catch (Exception ex)
{
throw ex;
}
}
Response.Clear();
Response.BufferOutput = false;
Response.ContentEncoding = System.Text.Encoding.UTF8;
if (browser.ToLower() != "firefox")
{
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFile) + ".xls");
}
else
{
Response.AddHeader("Content-Disposition", "attachment;filename=" + strFile + ".xls");
}
Response.ContentType = "application/ms-excel";
book1.Write(Response.OutputStream);
book1.Dispose();
}
public FFloat(FClass host, FieldWrap define, ImportExcel excel) : base(host, define)
{
Value = excel.GetFloat();
}
