/*
* 入库
*/
#region 入库
public static Boolean insert(List <List <KCT_CASE_INFO> > listKct, string inv_no, string conn)
{
DBHelperORACLE db = new DBHelperORACLE(conn);
int a = 0, b = 0, c = 0, d = 0;
try
{
db.openConn();
db.beginTrans();
string newcaseinfoid = Guid.NewGuid().ToString("N"); //代码产生新的CASEINFOID
string insertCaseInfo = "insert into xcky.kct_case_info (ID,CASE_ID,KCT_UUID,CASE_START_TIME,CASE_END_TIME,CASE_LON,CASE_LAT,WITNESS_INFO,REMARK,CREATE_USER,CREATE_DATETIME,UPDATE_USER,UPDATE_DATETIME,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,GPS_NAME,CASE_NAME,INVESTIGATION_ID) values ('" + newcaseinfoid + "'," + " (select case_id from xcky.scene_investigation where investigation_no = '" + inv_no + "'), '" + "52813100500148620140730230647098" + "',"
+ " to_date('" + listKct[0][0].Cdate + "','yyyy-MM-dd HH24:mi:ss')," + "to_date('" + listKct[listKct.Count - 1][listKct[listKct.Count - 1].Count - 1].Cdate + "','yyyy-MM-dd HH24:mi:ss'),"
+ "'" + listKct[0][0].Slong + "','" + listKct[0][0].Slat + "','','',(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "')" + ",(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no + "')" + ",'','','','','','','','','','','','',"
+ "(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'))";
d = db.execSqlREF(insertCaseInfo);
string insertloace = "insert into xcky.kct_locale_data(ID,LOCALE_NAME,COL_STARTTIME,COL_ENDTIME,CREATE_USER,CREATE_DATETIME,UPDATE_USER,UPDATE_DATETIME,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,DATA_TYPE,CASE_INFO_ID) values(sys_guid(),'" + listKct[0][0].Lname + "',to_date('" + listKct[0][0].Cdate + "','yyyy-MM-dd HH24:mi:ss'),to_date('" + listKct[listKct.Count - 1][listKct[listKct.Count - 1].Count - 1].Cdate + "','yyyy-MM-dd HH24:mi:ss'),(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "'),"
+ "(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no + "')," + "'','','','','','','','','','','1','" + newcaseinfoid + "')";
a = db.execSqlREF(insertloace);
string insertGsm = "insert into xcky.kct_basestation_data (ID,BS_TYPE,IFACTIVE,REG_ZONE,SID,NID,BASE_ID,CDMA_CH,PN,STRENGTH,MCC_MNC,LAC,CELL_ID,BCCH,BSIC,SYS_BAND,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,RESERVER9,RESERVER10,LON,LAT,LOCALE_DATA_ID,COL_TIME)" + "values(sys_guid(),'CMCC_GSM','ACTIVE','','" + listKct[0][0].Lacsid + "','" + listKct[0][0].Nid + "','','','','-21','','13945','" + listKct[0][0].Mlac + "','','','','','','','','','','','','','','','',(select ID from xcky.kct_locale_data where case_info_id='" + newcaseinfoid + "'),sysdate)";
b = db.execSqlREF(insertGsm);
string insertCdma = "insert into xcky.kct_basestation_data (ID,BS_TYPE,IFACTIVE,REG_ZONE,SID,NID,BASE_ID,CDMA_CH,PN,STRENGTH,MCC_MNC,LAC,CELL_ID,BCCH,BSIC,SYS_BAND,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,RESERVER9,RESERVER10,LON,LAT,LOCALE_DATA_ID,COL_TIME)" + "values(sys_guid(),'" + "CDMA" + "','ACTIVE','','" + listKct[0][0].Lacsid + "','" + listKct[0][0].Nid + "','110','','','-212','','','','','','','','','','','','','','','','','','',(select ID from xcky.kct_locale_data where case_info_id='" + newcaseinfoid + "'),sysdate)";
c = db.execSqlREF(insertCdma);
db.commitTrans();
db.closeConn();
}
catch (Exception ex)
{
db.rollbackTrans();
Console.WriteLine("err" + ex.Message);
Program.LastError = ex.Message;
return(false);
throw;
}
if (a > 0 && b > 0 && c > 0 && d > 0)
{
return(true);
}
else
{
return(false);
}
}
private void button2_Click(object sender, EventArgs e)
{
string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True";
DBHelperORACLE doo = new DBHelperORACLE(constring);
//再尝试转换对应的文件
Boolean createXmlOk = createXml();
if (createXmlOk)
{
//最后将转换后的数据按地址（GPS）为依据逐个作为这个K号的多个记录导入
string path = Application.StartupPath + "\\data.xml";
byte[] data;
FileStream fs = File.OpenRead(path);
data = new byte[fs.Length];
fs.Read(data, 0, Convert.ToInt32(fs.Length));
fs.Close();
Program.LastError = "";
DataTable dt = CSVFileHelper.OpenCSV(csvPath);
List <KCT_CASE_INFO> kctList = CSVFileHelper.ToKctList(dt);
dic = CSVFileHelper.Group(kctList);
Boolean isOk = CSVFileHelper.insert(dic, inv_no, constring);
Boolean XmlisOk = CSVFileHelper.InsertXml(inv_no, data, doo);
if (isOk && XmlisOk)
{
MessageBox.Show("导入完成！");
this.Close();
}
else
{
if (!isOk && !XmlisOk)
{
MessageBox.Show("索引和数据均导入失败!\r\n" + Program.LastError);
}
else if (!isOk)
{
MessageBox.Show("索引导入失败!\r\n" + Program.LastError);
}
else
{
MessageBox.Show("数据均导入失败!\r\n" + Program.LastError);
}
}
}
else
{
MessageBox.Show("XML文件生成失败");
}
}
/*
*插入XML
*/
#region 插入XML
public static bool InsertXml(string inv_no, byte[] xmlbtytes, DBHelperORACLE db)
{
string sql = "insert into xcky.common_attachment values(sys_guid(),:xml,'data.xml','01','','',1,0,'设备',to_date('" + DateTime.Now.ToString() + "','yyyy-MM-dd HH24:mi:ss')," + "'','',(select id from xcky.scene_investigation where investigation_no = '" + inv_no + "')," + "'','','','','','','')";
OracleParameter[] p = new OracleParameter[] { new OracleParameter(":xml", xmlbtytes) };
int rowsAffected = db.execSqlREF(sql, p);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
//测试数据库连接是否可用
public int checkOracleConn(string oracleSid, string oracleName, string oraclePwd)
{
string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True";
DBHelperORACLE doo = new DBHelperORACLE(constring);
try
{
doo.openConn();
return(1);
}
catch (Exception)
{
return(-1);
throw;
}
}
/*
*替换
*/
#region 替换
public static Boolean update(List <List <KCT_CASE_INFO> > listKct, string inv_no, string conn)
{
DBHelperORACLE db = new DBHelperORACLE(conn);
int a = 0, b = 0, c = 0, d = 0;
try
{
db.openConn();
db.beginTrans();
string updateCaseInfo = "update xcky.kct_case_info set CASE_ID=(select case_id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),KCT_UUID='52813100500148620140730230647098',CASE_START_TIME=(select investigation_date_from from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),CASE_END_TIME=(select investigation_date_to from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),CASE_LON='" + listKct[0][0].Slong + "',CASE_LAT='" + listKct[0][0].Slat + "',WITNESS_INFO='',REMARK='',CREATE_USER=(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "'),CREATE_DATETIME=(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no + "'),UPDATE_USER='',UPDATE_DATETIME='',"
+ "RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',GPS_NAME='',CASE_NAME='',INVESTIGATION_ID=(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "') where ID =(select ID from xcky.kct_case_info where kct_uuid = '52813100500148620140730230647098' and investigation_id =(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "') and rownum=1)";
Console.WriteLine("一" + updateCaseInfo);
a = db.execSqlREF(updateCaseInfo);
string updateloace = "update xcky.kct_locale_data set LOCALE_NAME='" + listKct[0][0].Lname + "',COL_STARTTIME=sysdate,COL_ENDTIME=sysdate,CREATE_USER=(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "'),CREATE_DATETIME=(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),UPDATE_USER='',UPDATE_DATETIME='',RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',DATA_TYPE='1' where ID =(select id from xcky.kct_locale_data where case_info_id =(select ID from xcky.kct_case_info where kct_uuid = '52813100500148620140730230647098' and investigation_id =(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "') and rownum=1) and rownum=1)";
Console.WriteLine("二" + updateloace);
b = db.execSqlREF(updateloace);
string updateGsm = "update xcky.kct_basestation_data set BS_TYPE='CMCC_GSM',IFACTIVE='ACTIVE',REG_ZONE='',SID='" + listKct[0][0].Lacsid + "',NID='" + listKct[0][0].Nid + "',BASE_ID='',CDMA_CH='',PN='',STRENGTH='',MCC_MNC='',LAC='" + listKct[0][0].Mlac + "',CELL_ID='',BCCH='',BSIC='',SYS_BAND='',RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',RESERVER9='',RESERVER10='',LON='" + listKct[0][0].Slong + "',LAT='" + listKct[0][0].Slat + "',COL_TIME=sysdate where LOCALE_DATA_ID =(select ID from xcky.kct_locale_data where case_info_id =(select id from xcky.kct_case_info where kct_uuid='52813100500148620140730230647098' and investigation_id=(select id"
+ " from xcky.scene_investigation where investigation_no = '" + inv_no + "') and rownum=1) and rownum=1) and BS_TYPE='CMCC_GSM'";
Console.WriteLine("三" + updateGsm);
c = db.execSqlREF(updateGsm);
string insertCdma = "update xcky.kct_basestation_data set BS_TYPE='CDMA',IFACTIVE='ACTIVE',REG_ZONE='',SID='" + listKct[0][0].Lacsid + "',NID='" + listKct[0][0].Nid + "',BASE_ID='',CDMA_CH='',PN='',STRENGTH='',MCC_MNC='',LAC='" + listKct[0][0].Mlac + "',CELL_ID='',BCCH='',BSIC='',SYS_BAND='',RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',RESERVER9='',RESERVER10='',LON='" + listKct[0][0].Slong + "',LAT='" + listKct[0][0].Slat + "',COL_TIME=sysdate where LOCALE_DATA_ID =(select ID from xcky.kct_locale_data where case_info_id =(select id from xcky.kct_case_info where kct_uuid='52813100500148620140730230647098' and investigation_id=(select id"
+ " from xcky.scene_investigation where investigation_no = '" + inv_no + "') and rownum=1) and rownum=1) and BS_TYPE='CDMA'";
Console.WriteLine("四" + insertCdma);
d = db.execSqlREF(insertCdma);
db.commitTrans();
db.closeConn();
}
catch (Exception ex)
{
db.rollbackTrans();
Program.LastError = ex.Message;
return(false);
throw;
}
if (a > 0 && b > 0 && c > 0 && d > 0)
{
return(true);
}
else
{
return(false);
}
}
//检查K好在case_info中有数据
public Boolean checkCaseInfo(string kno)
{
string oracleSid = textBox4.Text.Trim();
string oracleName = textBox5.Text.Trim();
string oraclePwd = textBox6.Text.Trim();
string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True";
DBHelperORACLE doo = new DBHelperORACLE(constring);
string sql = "select count(id) from xcky.kct_case_info where kct_uuid = '52813100500148620140730230647098' and investigation_id =(select id from xcky.scene_investigation where investigation_no = '" + kno + "')";
Object c = doo.execScalar(sql);
int count = int.Parse(c.ToString());
if (count > 0)
{
return(false);;
}
else
{
return(true);
}
}
//检测是否是2.0版本
public Boolean isSupport()
{
string oracleSid = textBox4.Text.Trim();
string oracleName = textBox5.Text.Trim();
string oraclePwd = textBox6.Text.Trim();
string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True";
DBHelperORACLE doo = new DBHelperORACLE(constring);
string sql = "select count(*) from user_tables where table_name like 'KCT%'";
Object c = doo.execScalar(sql);
int count = int.Parse(c.ToString());
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void button1_Click(object sender, EventArgs e)
{
string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True";
DBHelperORACLE doo = new DBHelperORACLE(constring);
doo.openConn();
Boolean createXmlOk = createXml();
if (createXmlOk)
{
DataTable dt = CSVFileHelper.OpenCSV(csvPath);
List <KCT_CASE_INFO> kctList = CSVFileHelper.ToKctList(dt);
dic = CSVFileHelper.Group(kctList);
Boolean updateOk = CSVFileHelper.update(dic, inv_no, constring);
string path = Application.StartupPath + "\\data.xml";
byte[] data;
FileStream fs = File.OpenRead(path);
data = new byte[fs.Length];
fs.Read(data, 0, Convert.ToInt32(fs.Length));
fs.Close();
Boolean XmlisOk = CSVFileHelper.InsertXml(inv_no, data, doo);
if (updateOk && XmlisOk)
{
MessageBox.Show("更新成功！");
this.Close();
}
else
{
MessageBox.Show("更新失败！");
this.Close();
}
}
else
{
MessageBox.Show("XML文件生成失败");
this.Close();
}
}