private void SendImageCommandHandler(object obj)
{
string fileName = ShowDialogAndFetchFileName();
PostMessage message = new PostMessage()
{
FromUserId = LoginUser.UserId,
ToUserId = ActiveTabUser.InboxUserId,
MessageType = MessageType.FacebookMessengerImage,
ImagePath = fileName
};
_dbHelper.Add(message);
}
public int DoLineStandDataRow(System.Data.DataTable dt, string linename, string ismain, int totalstand)
{
int Slno = 1; //站序
string SName = string.Empty; //站名
string Slon = string.Empty; //经度
string Slat = string.Empty; //纬度
string SQL = string.Empty;
string saveName = @"f:\临时文件\linestand.txt";
StreamWriter sw = new StreamWriter(saveName, true, System.Text.Encoding.Default);
StringBuilder sbStop = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
string slguid = Utility.CreateGuidLeft(36, totalstand.ToString() + "STANDDATA", '0', true);
Slno = Convert.ToInt16(dr[0].ToString()); //站序
SName = dr[1].ToString(); //站名
Slon = dr[2].ToString(); //经度
Slat = dr[3].ToString(); //纬度
SQL = "INSERT INTO linestand(slno,sname,slon,slat,lname,slguid,ismain) VALUES({0},'{1}',{2},{3},'{4}','{5}','{6}')";
SQL = string.Format(SQL, Slno, SName, Convert.ToDecimal(Slon), Convert.ToDecimal(Slat), linename, slguid, ismain);
sw.WriteLine(SQL);
sbStop.Append(SQL);
totalstand++;
}
sw.Close();
//入库
DbHelper dbHelper = new DbHelper();
int DoCount = dbHelper.Add(sbStop.ToString());
return(totalstand);
}
private void btnBatchJp_Click(object sender, EventArgs e)
{
double[] latlng = new double[2];
StringBuilder sbCar = new StringBuilder();
//批量将高德地图坐标转为GPS坐标
string sql = "SELECT id,latitude,longitude FROM dw_stop";
DbHelper dbHelper = new DbHelper();
DataTable dt = dbHelper.Fill(sql);
//循环处理每条坐标 记录
foreach (DataRow dr in dt.Rows)
{
string sguid = dr[0].ToString(); //坐标标识
string slat = dr[1].ToString(); //纬度
string slon = dr[2].ToString(); //经度
GpsCorrect.TransToGps(Convert.ToDouble(slat), Convert.ToDouble(slon), latlng);
sql = "UPDATE dw_stop set latitude={0},longitude={1} where id='{2}';" + Environment.NewLine;
sql = string.Format(sql, latlng[0], latlng[1], sguid);
sbCar.Append(sql);
}
dbHelper.Add(sbCar.ToString());
MessageBox.Show("数据处理完成");
}
public async Task <IActionResult> OnPostSaveAsync(Models.Customer customer)
{
var customer2 = DbHelper.GetFirst <Models.Customer>(c => c.Id == customer.Id);
customer.UserId = UserHelper.getcurrUserId(Request);
if (customer2 == null)
{
customer.Status = 0;
customer.SeqNo = 1;
DbHelper.Add(customer);
}
else
{
customer2.CompanyName = customer.CompanyName;
customer2.Contacts = customer.Contacts;
customer2.HuiKuanStatus = customer.HuiKuanStatus;
customer2.Address = customer.Address;
customer2.Contacts = customer.Contacts;
customer2.Tel = customer.Tel;
customer2.UserId = UserHelper.getcurrUserId(Request);
DbHelper.Update(customer2);
}
return(new JsonResult(new { code = 0, msg = "保存成功" }));
}
public static void UpateToDB(string sql, string savename = "")
{
//执行指定的SQL语句更新数据
DbHelper dbHelper = new DbHelper();
if (sql != "")
{
dbHelper.Add(sql);
}
//保存入库的SQL语句备查
if (savename != string.Empty)
{
if (File.Exists(savename))
{
File.Delete(savename);
}
StreamWriter sw = new StreamWriter(savename, true, System.Text.Encoding.Default);
sw.WriteLine("begin");
sw.Write(sql);
sw.WriteLine("end;");
sw.Close();
}
}
public async Task <IActionResult> OnPostSaveAsync(Models.Notice customer)
{
var customer2 = DbHelper.GetFirst <Models.Notice>(c => c.Id == customer.Id);
customer.UserId = UserHelper.getcurrUserId(Request);
if (customer2 == null)
{
customer.Status = 0;
customer.SeqNo = 1;
DbHelper.Add(customer);
}
else
{
customer2.title = customer.title;
customer2.content = customer.content;
customer2.ctime = customer.ctime;
customer2.UserId = UserHelper.getcurrUserId(Request);
DbHelper.Update(customer2);
}
return(new JsonResult(new { code = 0, msg = "保存成功" }));
}
public IActionResult useradd(string userid)
{
DbHelper.Add(new User {
Id = Util.GetCurrentTimeUnix(), UserId = userid, Name = "", PWD = "123456", SeqNo = 1, Status = 1
});
var ret2 = new { code = 0, msg = "更新成功" };
return(Json(ret2));
}
public IActionResult Post([FromBody] Work model)
{
model.Id = Util.GetCurrentTimeUnix();
model.CreatedId = UserHelper.getcurrUserId(Request);
model.CreatedDate = DateTime.Now;
DbHelper.Add(model);
return(Json(new { code = 0 }));
}
public IActionResult bumenadd(string name)
{
DbHelper.Add(new BuMen()
{
Id = Util.GetCurrentTimeUnix(), Name = name, SeqNo = 1, Status = 1
});
var ret2 = new { code = 0, msg = "更新成功" };
return(Json(ret2));
}
public DataTable resetColumn(string sql, string tableName)
{
try
{
DbHelper dbHelper = new DbHelper(connectionString);
dbHelper.Add(sql);
return(getTabbleColumnList(tableName));
}
catch (Exception ex) {
return(null);
}
}
public IActionResult bumenadd(string name, long parentId)
{
DbHelper.Add(new Pan()
{
Id = Util.GetCurrentTimeUnix(), Name = name,
SeqNo = 1, Status = 1, ParentId = parentId,
LastTime = DateTime.Now,
Ext = "",
Size = 0,
Roles1 = new long[] { },
Roles2 = new long[] { },
Roles3 = new long[] { },
UserId = UserHelper.getcurrUserId(Request)
});
var ret2 = new { code = 0, msg = "新增成功" };
return(Json(ret2));
}
private void btnInGd_Click(object sender, EventArgs e)
{
string Lname = textBox5.Text;
Int32 totalstand = 8000;
//同步line_standinfo表中的sguid
string UpdateSguid = "UPDATE linestand SET sguid=(SELECT sguid FROM dbo.line_standinfo WHERE SNAME=linestand.sname AND SLON=linestand.slon AND SLAT=linestand.slat AND slon>0)";
UpdateSguid += " WHERE linestand.slon > 0 AND linestand.sguid IS NULL;" + Environment.NewLine;
//从高德数据中拿线路站点
string sql = " SELECT a.sequence AS slno,a.short_name AS sname,a.longitude AS slon, a.latitude AS slat, ";
sql += "b.short_name AS lname,b.direction - 1 AS ismain FROM dbo.dw_stop a ";
sql += "LEFT JOIN dbo.dw_route b ON b.id = a.route_id ";
sql += "WHERE b.parent_id > 0 AND b.short_name LIKE '" + Lname + "%' ";
sql += "ORDER BY b.short_name";
string SQL = string.Empty;
DbHelper dbHelper = new DbHelper();
dbHelper.Add("DELETE FROM linestand;");
DataTable dt = dbHelper.Fill(sql);
StringBuilder sbStop = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
string slguid = Utility.CreateGuidLeft(36, totalstand.ToString() + "STANDDATA", '0', true);
string Slno = dr[0].ToString(); //站序
string SName = dr[1].ToString(); //站名
string Slon = dr[2].ToString(); //经度
string Slat = dr[3].ToString(); //纬度
string linename = dr[4].ToString(); //线路名称
string ismain = dr[5].ToString(); //上下行
SQL = "INSERT INTO linestand(slno,sname,slon,slat,lname,slguid,ismain) VALUES({0},'{1}',{2},{3},'{4}','{5}','{6}')";
SQL = string.Format(SQL, Slno, SName, Convert.ToDecimal(Slon), Convert.ToDecimal(Slat), linename, slguid, ismain);
//sw.WriteLine( SQL );
sbStop.Append(SQL);
totalstand++;
}
//插入高德数据
dbHelper.Add(sbStop.ToString());
//同步sguid
dbHelper.Add(UpdateSguid);
//插入line_standinfo表中不存在的站点
sql = "SELECT sname,slon,slat FROM linestand WHERE sguid IS null GROUP BY sname,slon,slat;";
DataTable dtStand = dbHelper.Fill(sql);
InportData.InportStandInfo(dtStand, @"f:\临时文件\standinfo.sql");
//再次同步sguid并同步liguid
string UpdateLiguid = "UPDATE linestand SET liguid=(SELECT liguid FROM line_lineinfo WHERE ismain=linestand.ismain AND LBGUID=(";
UpdateLiguid += " SELECT LBGUID FROM line_linebasicinfo WHERE lname = linestand.lname AND ISDELETED = '0')); ";
dbHelper.Add(UpdateSguid + UpdateLiguid);
//准备插入line_standdata
string InsertData = "select slguid,sguid,liguid,slno from dbo.linestand WHERE liguid IS NOT NULL ORDER by lname,ismain,slno;";
DataTable dtStandData = dbHelper.Fill(InsertData);
InportData.InportStandData(dtStandData, @"f:\临时文件\standdata.sql");
MessageBox.Show("数据已导入");
}
//导入线路车辆信息
private void btnCar_Click(object sender, EventArgs e)
{
string FileDir = txtInDir.Text; //待处理车辆文件所在目录
string[] arrFiles = Directory.GetFiles(FileDir); //目录内的所有车辆文件
string LineName = string.Empty; //线路名称
string BusCard = string.Empty; //车牌号
string BusNo = string.Empty; //自编号
string CompanyName = string.Empty; //所属公司
string DeptName = string.Empty; //所属部门(车队)
string LineBusGuid = string.Empty; //线路车辆表主键
string SQL = string.Empty; //入库SQL语句
int Total = 1; //所有记录总数
int DoCount = 0;
StringBuilder sbCar = new StringBuilder();
//循环处理目录里面的每个文件
//foreach( string doFileName in arrFiles )
//{
// //打开excel文件,放到数据表中
// DataTable dt = ExcelHelper.ReadExelToTable( doFileName, 0 );
// //循环处理每条车辆记录
// foreach( DataRow dr in dt.Rows )
// {
// LineBusGuid = Utility.CreateGuidLeft( 36, Total.ToString() + "LINEBUS", '0', true );
// LineName = dr[ 0 ].ToString(); //线路名称
// BusCard = dr[ 2 ].ToString(); //车牌号
// BusNo = dr[ 1 ].ToString(); //自编号
// CompanyName = dr[ 3 ].ToString(); //所属公司
// DeptName = dr[ 4 ].ToString(); //所属部门(车队)
// SQL = "INSERT INTO linecar(lname,dbuscard,compname,orgname,busno,linebusguid) VALUES('{0}','{1}','{2}','{3}','{4}','{5}')";
// SQL = string.Format( SQL, LineName, BusCard, CompanyName, DeptName, BusNo, LineBusGuid );
// sbCar.Append( SQL );
// Total++;
// }
//}
//数据处理入库
DbHelper dbHelper = new DbHelper();
//DoCount = dbHelper.Add( sbCar.ToString() );
//更新系统中存在的公司、部门、线路、车辆的对应字段
string UpdateExistsField = "update linecar set lbguid=(select lbguid from line_linebasicinfo where lname=linecar.lname), ";
UpdateExistsField += "bguid=(select bguid from line_businfo where dbuscard=linecar.dbuscard), ";
UpdateExistsField += "COMPGUID=(SELECT COMPGUID FROM dbo.Line_CompanyInfo WHERE COMPNAME=dbo.linecar.compname), ";
UpdateExistsField += "deptid=(SELECT deptid FROM sys_org WHERE ORGNAME=dbo.linecar.orgname AND COMPGUID=(SELECT COMPGUID FROM dbo.Line_CompanyInfo WHERE COMPNAME=dbo.linecar.compname)) ";
//DoCount = dbHelper.Add( UpdateExistsField );
////将系统不存在的线路加入到系统中
//string SaveLineName = @"f:\临时文件\lineinfo.txt";
//SQL = "SELECT Lname,compguid,deptid FROM linecar WHERE lbguid IS NULL GROUP BY Lname,compguid,deptid ";
//DataTable dtLineBasic = dbHelper.Fill( SQL );
//if( dtLineBasic.Rows.Count > 0 )
//{
// InportData.InportLineInfo( dtLineBasic, SaveLineName );
//}
//将系统中不存在的车辆加入到系统中
//string SaveCarName = @"f:\临时文件\CarInfo.txt";
//SQL = "SELECT dbuscard,compguid,deptid,busno FROM linecar WHERE bguid IS NULL ";
//DataTable dtBusInfo = dbHelper.Fill( SQL );
//if( dtBusInfo.Rows.Count > 0 )
//{
// InportData.InportBusInfo( dtBusInfo, SaveCarName );
//}
//再次更新公司、部门、线路、车辆的对应字段
DoCount = dbHelper.Add(UpdateExistsField);
//最后更新到定车定线表中
}
public DataTable getTabbleColumnList(string tableName)
{
try
{
DataTable dtColumns = null;
string sql = string.Format("select * from SYS_T_MENUTABLECOLUMN where table_name='{0}'", tableName);
DbHelper dbHelper = new DbHelper(connectionString);
DataTable dtTableName = dbHelper.Fill(sql);
if (dtTableName.Rows.Count < 1)
{
string colSql = @"INSERT INTO dbo.SYS_T_MENUTABLECOLUMN
( COL_D ,
COL_CODE ,
COL_NAME ,
COL_TYPE ,
COL_LENGTH ,
PRIMARY_KEY ,
COL_PRECISION ,
CAN_EDIT ,
CAN_NULL ,
ORDER_INDEX,
DIC_CODE,
COL_DESC,
INPUT_TYPE,
TABLE_NAME
)
SELECT NEWID(),
CONVERT(VARCHAR(16),a.name),
CONVERT(VARCHAR(64),isnull(g.[value],'')),
CONVERT(VARCHAR(16),b.name),
BitLenght=a.length,
IsPK=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '1' else '0' end,
Digits=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
1,
CanNull=case when a.isnullable=1 then '1'else '0' end,
1,
'',
CONVERT(VARCHAR(64),isnull(g.[value],'')),
'手工输入',
'{0}'
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where d.name='{1}'
order by a.id,a.colorder";
dbHelper.Add(string.Format(colSql, tableName, tableName));
sql = @"delete from SYS_T_MENUTABLECOLUMN where table_name='{0}' and COL_CODE in ('{1}')";
sql = string.Format(sql, tableName, excludes.Replace(",", "','"));
dbHelper.Add(sql);
}
sql = @"SELECT COL_D , COL_CODE , COL_NAME , COL_TYPE , COL_LENGTH ,ORDER_INDEX,COL_PRECISION,INPUT_TYPE,DIC_CODE,PRIMARY_KEY ,LIST_DISPLAY , QUICK_QUERY , EXPERT_QUERY ,
CAN_EDIT ,CAN_NULL , COL_DESC,TABLE_NAME FROM SYS_T_MENUTABLECOLUMN WHERE TABLE_NAME='{0}' ORDER BY ORDER_INDEX ASC ";
dtColumns = dbHelper.Fill(string.Format(sql, tableName));
return(dtColumns);
}
catch (Exception ex) {
return(null);
}
}
private void SendImageCommandHandler(object obj)
{
if (ActiveTabUser != null)
{
if (SelectedItem != null)
{
if (SelectedItem.MessageUserType == "Instagram")
{
return;
}
try
{
string fileName = ShowDialogAndFetchFileName();
if (!string.IsNullOrEmpty(fileName))
{
if (ActiveTabUser.MessageUserType == "Messenger")
{
_dbHelper.Add(new PostMessage()
{
FromUserId = LoginUser.InboxUserId,
ToUserId = ActiveTabUser.InboxUserId,
Message = null,
MessageType = MessageType.FacebookMessengerImage, //FacebookMessengerImage =5
ImagePath = fileName,
Status = 0,
ToUrl = LinkToSend
});
ActiveTabUser.Messages.Add(new FbUserMessageInfo {
UserType = 1, otheruserimage = fileName
});
MessageToSend = "";
}
else if (ActiveTabUser.MessageUserType == "Facebook")
{
_dbHelper.Add(new PostMessage()
{
FromUserId = LoginUser.InboxUserId,
ToUserId = ActiveTabUser.InboxUserId,
Message = null,
MessageType = MessageType.FacebookImage, //FacebookImage =2,
ImagePath = fileName,
Status = 0,
ToUrl = LinkToSend
});
//MessageBox.Show("Message save successfully");
ActiveTabUser.Messages.Add(new FbUserMessageInfo {
UserType = 1, otheruserimage = fileName
});
MessageToSend = "";
}
}
}
catch (Exception)
{
}
}
}
else
{
MessageBox.Show("Please select usear tab");
}
}
