private void New_Search(string IU, string start_time, string end_time)
{
Progressbar pbdefault = new Progressbar(Progressdefault);
Progressbar pbstep = new Progressbar(ProgressSetp);
Labeldisplay ld = new Labeldisplay(Labelupdate);
button2enable b2e = new button2enable(Button2Status);
this.Invoke(pbdefault);
Datagridadd dd = new Datagridadd(Datagrid);
string cmd = @" SELECT 1 from [dbo].[movement_trans],Trans_type WHERE update_dt BETWEEN @start_time AND @end_time AND (iu_tk_no=@IU OR card_mc_no=@IU) AND entry_time is not null And exit_time is not null
IF EXISTS(SELECT 1 from [dbo].[movement_trans],Trans_type WHERE update_dt BETWEEN @start_time AND @end_time AND (iu_tk_no=@IU OR card_mc_no=@IU) AND entry_time is not null And exit_time is not null)
BEGIN
SELECT site_name,iu_tk_no,trans_type as trans_type_id,entry_station as entry_station_id,entry_time,exit_station as exit_station_id,exit_time,parked_time,parking_fee,paid_amt,card_mc_no FROM [dbo].[movement_trans],site_setup
WHERE movement_trans.update_dt BETWEEN @start_time AND @end_time AND (iu_tk_no=@IU OR card_mc_no=@IU) AND entry_time is not null And exit_time is not null ORDER BY entry_time;
SELECT station_id,station_name from station_setup;
SELECT Trans_type,Description from Trans_type;
END";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@IU", IU),
new SqlParameter("@start_time", start_time),
new SqlParameter("@end_time", end_time)
};
LogClass.WirteLogForMovement("Start to search iu or cash card : " + IU);
Dictionary <string, string> stationDIC = new Dictionary <string, string>();
Dictionary <string, string> Trans_type = new Dictionary <string, string>();
foreach (KeyValuePair <string, string> kv in carparklist)
{
this.Invoke(pbstep);
this.Invoke(ld, kv.Key);
stationDIC.Clear();
Trans_type.Clear();
LogClass.WirteLogForMovement($"------------{kv.Key}------------");
if (kv.Key == "C21M")
{
continue;
}
string connectString = "Data Source=" + kv.Value + ";uid=sa;pwd=yzhh2007;database=" + kv.Key;
DataSet ds = null;
try
{
ds = SqlHelper.ExecuteDataset(connectString, CommandType.Text, cmd, para);
}
catch (Exception sqle)
{
LogClass.WirteLogForMovement("Car park " + kv.Key + " error on read data : " + sqle.ToString());
continue;
}
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
//Found movement data start to generate
LogClass.WirteLogForMovement("Found data.");
//1.get station id and name
foreach (DataRow dr in ds.Tables[2].Rows)
{
stationDIC.Add(dr["station_id"].ToString(), dr["station_name"].ToString());
}
//2.get trans_type
foreach (DataRow dr in ds.Tables[3].Rows)
{
Trans_type.Add(dr["Trans_type"].ToString(), dr["Description"].ToString());
}
DataTable dt = ds.Tables[1];
dt.Columns.Add(new DataColumn("trans_type", typeof(string)));
dt.Columns.Add(new DataColumn("entry_station", typeof(string)));
dt.Columns.Add(new DataColumn("exit_station", typeof(string)));
foreach (DataRow dr in dt.Rows)
{
string trans_type_id = dr["trans_type_id"].ToString();
string entry_station_id = dr["entry_station_id"].ToString();
string exit_station_id = dr["exit_station_id"].ToString();
try
{
dr["trans_type"] = Trans_type[trans_type_id];
dr["entry_station"] = stationDIC[entry_station_id];
dr["exit_station"] = stationDIC[exit_station_id];
}
catch (SqlException sqle)
{
LogClass.WirteLogForMovement($"trans_type_id={trans_type_id},entry_station_id={entry_station_id},exit_station_id={exit_station_id},{sqle.ToString()}");
continue;
}
}
dt.Columns.Remove("trans_type_id");
dt.Columns.Remove("entry_station_id");
dt.Columns.Remove("exit_station_id");
this.Invoke(dd, ds.Tables[1]);
}
else
{
//no data found
LogClass.WirteLogForMovement("No data found.");
}
}
}
this.Invoke(b2e);
}
private void Search(string IU, string start_time, string end_time)
{
//Testing
Progressbar pbdefault = new Progressbar(Progressdefault);
Progressbar pbstep = new Progressbar(ProgressSetp);
Labeldisplay ld = new Labeldisplay(Labelupdate);
button2enable b2e = new button2enable(Button2Status);
this.Invoke(pbdefault);
Datagridadd dd = new Datagridadd(Datagrid);
string cmd = @" SELECT iu_tk_no,Trans_type.Description as Trans_type,entry_station,entry_time,station_setup.station_name as exit_station,exit_time,parked_time,paid_amt,card_mc_no
FROM [dbo].[movement_trans],dbo.station_setup,Trans_type
WHERE Trans_type.Trans_type=movement_trans.trans_type
AND station_setup.station_id=movement_trans.exit_station
AND movement_trans.update_dt BETWEEN @start_time and @end_time
AND (iu_tk_no=@IU
OR card_mc_no=@IU);";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@IU", IU),
new SqlParameter("@start_time", start_time),
new SqlParameter("@end_time", end_time)
};
LogClass.WirteLogForMovement("Start to search iu or cash card : " + IU);
foreach (KeyValuePair <string, string> kv in carparklist)
{
this.Invoke(pbstep);
this.Invoke(ld, kv.Key);
LogClass.WirteLogForMovement($"-----------{kv.Key}------------");
//if (!Ping(kv.Value))
//{
// LogClass.WirteLogForMovement(kv.Key + " can't reply from ping command after 5 seconds waiting.");
// continue;
//}
//LogClass.WirteLogForMovement(kv.Key + " ping ok.");
string connectString = "Data Source=" + kv.Value + ";uid=sa;pwd=yzhh2007;database=" + kv.Key;
DataSet ds = null;
try
{
ds = SqlHelper.ExecuteDataset(connectString, CommandType.Text, cmd, para);
}
catch (Exception sqle)
{
LogClass.WirteLogForMovement("Car park " + kv.Key + " error on read data : " + sqle.ToString());
continue;
}
try
{
if (ds == null)
{
continue;
}
if (ds.Tables[0].Rows.Count > 0)
{
LogClass.WirteLogForMovement("Car park " + kv.Key + " found data. ");
ds.Tables[0].Columns.Add("Carpark", typeof(string));
foreach (DataRow dr in ds.Tables[0].Rows)
{
dr["Carpark"] = kv.Key;
}
// table.Merge(ds.Tables[0]);
this.Invoke(dd, ds.Tables[0]);
LogClass.WirteLogForMovement("Car park " + kv.Key + " merge data ok.");
}
else
{
LogClass.WirteLogForMovement("Car park " + kv.Key + " no data found. ");
continue;
}
}
catch (Exception e)
{
LogClass.WirteLogForMovement("Car park " + kv.Key + " error on merge data : " + e.ToString());
continue;
}
}
this.Invoke(b2e);
}