/// <summary>
/// 포 초기화 작업을 한다.
/// </summary>
private void InitForm()
{
try
{
strPrgName = "OLEDB I/F";
clsLog = new Function.Util.Log(Application.StartupPath + "\\Log_OleDB_IF", "OleDB_IF", 30, true);
clsRemote = new clsRemoting(intPort, strUri);
control.Invoke_Control_Text(lblSvrInfo, "URI : " + clsRemote.strFullUri);
control.Invoke_Control_Color(lblSvrInfo, Color.Yellow, null);
clsRemote.Channel_SeverLoad(isHttpProtocol, typeof(Function.Advenced.clsOLEDB_IF), false, isSingleton);
control.Invoke_Control_Text(lblSvrStatus, "서버\r\n작동중");
control.Invoke_Control_Color(lblSvrStatus, null, Color.RoyalBlue);
Function.Component.Spread.Invoke_RowCount(fpLog, fpLog.ActiveSheet, 0);
clsOLEDB_IF.strConn = OleDB.Set_connectionString(OleDB.enProvider.MSSql, "admin\\sqlexpress",
"namsun", "sa", "piss");
clsOLEDB_IF.evtdsExcute = new clsOLEDB_IF.deldsExcute(evtdsExcute);
clsOLEDB_IF.evtdsExcuteError = new clsOLEDB_IF.deldsExcuteError(evtdsExcuteError);
SetMessage(false, "서버를 시작 하였습니다.", false);
}
catch (Exception ex)
{
ProcException(ex, "InitForm");
}
}
public void Delete(T t)
{
OleDB.ExecuteNonQuery("delete from TAtomAndGrp"
+ " where FKind = '" + Kind + "' and Id = " + t.Id);
Entities.Remove(t.Id);
}
protected void LoadSln()
{
foreach (OleDbDataReader rd in OleDB.EachRows("select * from TSolution"))
{
Solution = new EnSolution();
IList <Boolean> ActiveWeekArr = ExBuildListWithHexChar.HexStrToBooleanList(rd["FActiveWeek"].ToString());
if (ActiveWeekArr.Count == Solution.ActiveWeekArr.Length)
{
for (var i = 0; i < ActiveWeekArr.Count; i++)
{
Solution.ActiveWeekArr[i] = ActiveWeekArr[i];
}
}
IList <Int32> LessonNumberArr = ExBuildListWithHexChar.HexStrToInt32List(rd["FLessonNumber"].ToString());
if (LessonNumberArr.Count == Solution.LessonNumberArr.Length)
{
for (var i = 0; i < LessonNumberArr.Count; i++)
{
Solution.LessonNumberArr[i] = LessonNumberArr[i];
}
}
}
}
/// <summary>
/// 쿼리를 실행하고 리턴값을 넘긴다.
/// </summary>
/// <param name="strQuery"></param>
/// <returns></returns>
public DataSet dsExcute(string strClientName, string strQuery)
{
OleDB clsDb = new OleDB(strConn);
try
{
DataSet ds = clsDb.dsExcute_Query(strQuery);
if (evtdsExcute != null)
{
evtdsExcute(strClientName, strQuery);
}
return(ds);
}
catch (Exception ex)
{
if (evtdsExcuteError != null)
{
evtdsExcuteError(strClientName, strQuery, ex);
}
throw;
}
}
public string GetDebugSQL()
{
RelationalDatabase oleDb = null;
RelationalDatabase _OleDB = null;
EntityConfig.BindConfig_DAL();
oleDb = new OleDB();
_OleDB = oleDb;
string strWhere_1 = "a.zyfp_code" + oleDb.EuqalTo() + "b.code";
string childtable_1 = oleDb.ChildTable(oleDb.TableNameBM(Tables.BASE_STAT_ITEM, "a") + "," + oleDb.TableNameBM(Tables.BASE_STAT_ZYFP, "b"), "aa", strWhere_1,
"a.code",
oleDb.FiledNameBM("b.item_name", "itemname"));
string sum_2 = oleDb.Sum(Tables.zy_presorder.TOLAL_FEE, "");
string _gs_1_3 = oleDb.DBConvert("aa." + Tables.base_stat_item.CODE, "int");
string _gs_1_4 = oleDb.DBConvert(Tables.zy_presorder.ITEMTYPE, "int");
string strWhere_5 = _gs_1_3 + oleDb.EuqalTo() + _gs_1_4;
string childtable_2 = oleDb.ChildTable(childtable_1, "itemname", strWhere_5,
" itemname ");
string strWhere_6 = Tables.zy_presorder.PATLISTID + oleDb.EuqalTo() + 1 + oleDb.And() + Tables.zy_presorder.COSTMASTERID + oleDb.EuqalTo() + "0" + oleDb.And() + Tables.zy_presorder.CHARGE_FLAG + oleDb.EuqalTo() + "1";
string childtable_3 = oleDb.ChildTable(Tables.ZY_PRESORDER, "a", strWhere_6,
childtable_2,
"tolal_fee");
string strsql = oleDb.Table(childtable_3, "group by a.itemname", "",
"itemname",
oleDb.FiledNameBM(sum_2, "tolal_fee"));
return(strsql);
}
/// <summary>
/// 将dt中的字段名输出至Excel第一行,将其作为模板返回
/// </summary>
private void EduceExcel(DataTable dt, string fieldName, string filename)
{
string excelPath = Server.MapPath("~/manage/AddCRM/Template.xlsx");
//OleDB.ExecuteSql(excelPath, "Drop Table " + OleDB.GetFirstTableName(excelPath));//先清空数据,避免其移除了字段后，出现多余的数据
string sql = "CREATE TABLE " + OleDB.GetFirstTableName(excelPath) + "(";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (string.IsNullOrEmpty(dt.Rows[i][fieldName].ToString()))
{
continue; //如果name为空的话，则跳过
}
sql += "[" + (dt.Rows[i][fieldName] as string) + "] VarChar,";
}
sql = sql.TrimEnd(','); sql += ")"; //function.WriteErrMsg(sql);
OleDB.ExecuteSql(excelPath, sql);
Response.Clear();
Response.Buffer = true;
Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(filename) + ".xlsx");
Response.ContentType = "Application/ms-excel";
Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;
Response.WriteFile(excelPath);
Response.End();
}
protected void SaveSln(EnSolution solution)
{
OleDB.ExecuteNonQuery("delete from TSolution");
OleDB.ExecuteNonQuery("insert into TSolution (FActiveWeek, FLessonNumber) values ("
+ "'" + ExBuildListWithHexChar.GetHexStr(solution.ActiveWeekArr) + "'"
+ ",'" + ExBuildListWithHexChar.GetHexStr(solution.LessonNumberArr) + "'"
+ ")");
}
public void Insert(T t)
{
t.Id = Convert.ToInt64(
OleDB.InsertAndReturnId_MS(
"insert into TAtomAndGrp (FKind, FName, FColor) values ("
+ "'" + Kind + "', "
+ "@FName, "
+ (IsCourse ? (t as EnSubject).Color.ToArgb().ToString() : "0")
+ ")",
new KeyValue <String, Object>("@FName", t.Name)
));
}
private void btnXlsLoad_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog dial = new OpenFileDialog();
dial.Multiselect = false;
dial.ShowDialog();
string strFileName = dial.FileName;
dial.Dispose();
if (strFileName == string.Empty)
{
return;
}
strFileName = strFileName.Replace(@"\\", @"\");
//strFileName = @".\chasys.xls";
OleDB clsOle = new OleDB(OleDB.enProvider.Excel, strFileName, string.Empty, string.Empty, string.Empty);
using (DataSet ds = clsOle.dsExcute_Query("select orderdate, partcode, target_cnt from [order$]"))
{
if (ds.Tables[0].Rows.Count < 1)
{
return;
}
lstPlan.Items.Clear();
ResetInput();
foreach (DataRow dr in ds.Tables[0].Rows)
{
lstPlan.Items.Add(new ListViewItem(new string[] { (lstPlan.Items.Count + 1).ToString(),
Fnc.obj2String(dr["ORDERDATE"]),
Fnc.obj2String(dr["PARTCODE"]),
Fnc.obj2String(dr["TARGET_CNT"]) }));
}
}
}
catch (Exception ex)
{
clsLog.WLog_Exception("btnXlsLoad", ex);
Fnc.ShowMsg(this, ex.Message, string.Empty, frmMessage.enMessageType.OK);
}
}
public void Update(T t)
{
OleDB.ExecuteNonQuery(
"update TAtomAndGrp set "
+ " FName = @FName, "
+ " FColor = " + (IsCourse ? (t as EnSubject).Color.ToArgb().ToString() : "0")
+ " where FKind = '" + Kind + "' "
+ " and Id = " + t.Id,
new KeyValue <String, Object>("@FName", t.Name)
);
}
public void ClearAll()
{
OleDB.ExecuteNonQuery("delete from TAtomAndGrp"
+ " where FKind = '" + Kind + "'");
IList <Int64> Ids = new List <Int64>();
foreach (BaseEntity ety in Entities.Values)
{
if (ety is T)
{
Ids.Add(ety.Id);
}
}
foreach (Int64 id in Ids)
{
Entities.Remove(id);
}
}
protected void ExcelData_Click(object sender, EventArgs e) //从Excel导入数据
{
if (!CheckFile(fileImp.FileName)) //detect the file is xls or xlsx
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('必须是csv或excel文件');", true);
return;
}
string path = ImportExcel();
ds.ReadXml(Server.MapPath(xmlPath)); //nameToField.xml
ds2.ReadXml(Server.MapPath(xmlPath2)); //CRM_Dictionary.xml
DataTable salesManDT = GetSalesMan();
//指定路径,表名
OleDB ol = new OleDB();
DataTable dt = new DataTable();
DataSet csvDS = new DataSet();//csv导入需要
//为dt赋值,根据后缀名,调用不同的方式读取,支持CSV与Xls,xlsx三种格式
try
{
if (System.IO.Path.GetExtension(fileImp.FileName).ToLower().Equals(".csv"))
{
CsvToDataSet(csvDS, path);
dt = csvDS.Tables[0];
}
else//处理excel
{
dt = OleDB.Select(path, "select * from" + OleDB.SelectTables(path).Rows[0]["Table_Name"]);//读取第一张表中的数据;//读取第一张表中的数据
}
dt.Columns.Add("Code", typeof(string));
dt.Columns.Add("Add_Date", typeof(DateTime));
dt.Columns.Add("FPManID", typeof(string));//或在另一个循环中添加
for (int i = 0; i < dt.Rows.Count; i++)
{
//为其全部加上code与日期
dt.Rows[i]["Code"] = function.GetFileName();
dt.Rows[i]["Add_Date"] = DateTime.Now;
}
for (int i = 0; i < dt.Rows.Count;)//为其加上跟进人员(即为销售人员的管理员的ID)
{
for (int j = 0; j < salesManDT.Rows.Count && i < dt.Rows.Count; j++, i++)
{
dt.Rows[i]["FPManID"] = salesManDT.Rows[j]["AdminID"];
}
}
for (int i = 0; i < dt.Columns.Count; i++) //将列名由Excel的中文转为英文
{
ds.Tables[0].DefaultView.RowFilter = "name in ('" + dt.Columns[i].ColumnName + "')"; //选出XML中的同名标签
if (ds.Tables[0].DefaultView.ToTable().Rows.Count > 0) //避免有不匹配的
{
dt.Columns[i].ColumnName = (ds.Tables[0].DefaultView.ToTable().Rows[0]["fieldname"].ToString()); //替换为英文
}
}
//准备值 1为检测,2为可为空,3为不需要检测
for (int i = 1; i < ds2.Tables.Count; i++)//先将ds2中的信息汇总成一张表,XML中的标记名称必须和字段一样
{
ds2.Tables[0].Merge(ds2.Tables[i]);
}
ds.Tables[0].DefaultView.RowFilter = "1=1";
ds.Tables[0].DefaultView.RowFilter = "needCheck in('1','2')";
DataTable needCheckDT = ds.Tables[0].DefaultView.ToTable(); //使用fieldName来获取table中的数据,要对比的数据来自ds2(directory)
//开始检测dt中的数据
try
{
for (int i = 0; i < needCheckDT.Rows.Count; i++)
{
string fieldname = needCheckDT.Rows[i]["fieldname"].ToString();
for (int j = 0; j < dt.Rows.Count; j++)
{
if (needCheckDT.Rows[i]["needCheck"].ToString().Equals("2") && string.IsNullOrEmpty(dt.Rows[j][fieldname].ToString()))//为2可空时的检测
{
continue;
}
ds2.Tables[fieldname].DefaultView.RowFilter = "content in('" + dt.Rows[j][fieldname].ToString() + "')";
if (ds2.Tables[fieldname].DefaultView.ToTable().Rows.Count < 1)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('[" + needCheckDT.Rows[i]["name"].ToString() + "]第" + (j + 1).ToString() + "行,值不符合规范!');", true);
return;
}
}
}
}
catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('格式不正确" + ex.Message + "');", true); return; }
}
catch (NullReferenceException)//避免用户只是改了后缀名方式上传
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('格式不正确,请确定是否以xls或xlsx格式保存!');", true); return;
}
//------从Excel获取的表处理完成,开始插入
ds.Tables[0].DefaultView.RowFilter = "1=1";//清空下
DataTable tableNameDT = ds.Tables[0].DefaultView.ToTable(true, new string[] { "tablename" });
for (int i = 0; i < tableNameDT.Rows.Count; i++)//批量向多表导入数据
{
string tableName = tableNameDT.Rows[i]["tablename"].ToString(), columns = "";
ds.Tables[0].DefaultView.RowFilter = "tablename in ('" + tableName + "')";
for (int j = 0; j < ds.Tables[0].DefaultView.ToTable().Rows.Count; j++)//准备要插入的列
{
columns += (ds.Tables[0].DefaultView.ToTable().Rows[j]["fieldname"] as string) + ",";
}
columns += "Code,Add_Date";
DataTable tempDT = GetTableByColumnName(dt, false, columns.Split(','));
tempDT.TableName = tableName;
if (WriteDataToDB(tempDT))
{
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('" + tableName + "导入失败,请检测Excel中格式是否正确,是否包含非法字符');", true);
}
}
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('导入完成');location=location;", true);
}
