public static bool Visit(string sysname, string modname, string loginuser, string memo)
{
if (string.IsNullOrEmpty(sysname))
{
return(false);
}
var sql = SQL_VISIT_LOG;
var para = new MySqlParameter[]
{
new MySqlParameter("@sysname", sysname),
new MySqlParameter("@modname", modname),
new MySqlParameter("@loginuser", loginuser),
new MySqlParameter("@memo", memo),
};
int nRet = -1;
try
{
nRet = MySqlClientHelper.ExecuteNonQuery(CustomConfig.ConnStrMain, System.Data.CommandType.Text, sql, para);
}
catch (Exception ex)
{
//TODO
}
return(nRet > 0);
}
public static byte[] GetMonthData_Down(string selKind)
{
byte[] bys = null;
var sCol = selKind;
if (0 == string.Compare("HC_Utilization", selKind, true))
{
sCol = "(HC_Utilization / 100.0) as HC_Utilization";
}
var sql = string.Format(@"select
cast(Date as char(10)) as Date,
Warehouse,
{0}
from V_Tbl_MonthData
order by Date,Warehouse
"
, sCol
);
MySqlParameter[] parameter = null;
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}
private static void WriteDBLog(MySqlConnection conn, string msg)
{
var sql = string.Format(@"insert into task_log (msg,happentime)
values('{0}', CURRENT_TIMESTAMP)"
, msg);
MySqlClientHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
internal static TDatatables GetMonthData(int selYear, string selKind)
{
var res = new TDatatables();
if (string.IsNullOrEmpty(selKind))
{
return(res);
}
var sql = string.Format(@"select cast(`Date` as char(10)) as `Date`
,`Warehouse`
,{0}
from V_Tbl_MonthData
where year(`Date`)={1}
order by `Date`,Warehouse
"
, selKind, selYear
);
MySqlParameter[] parameter = null;
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(res);
}
var dt = DataTableHelper.GetDataTable0(ds);
var qry = dt.AsEnumerable().Select(x => new
{
Date = x["Date"].ToString().Substring(0, 7), /*yyyy-MM*/
Warehouse = x["Warehouse"].ToString(),
Item = x[selKind].ToString()
});
//var lstRows = GetKinds();
var kinds = qry.Select(x => x.Warehouse)
.Distinct()
.OrderBy(x => x)
.ToList();
var cols = qry.Select(x => x.Date)
.Distinct()
.OrderBy(x => x)
.Select(y => new TColEntry(y, y)).ToList();
var qry2 = from x in qry
group x by x.Warehouse into g
select new
{
name = g.Key,
items = g.Select(x => x.Item).ToList()
};
res.data = qry2.ToList();
res.columns = cols;
res.kinds = kinds;
return(res);
}
private bool BuildExpByDataReader(string sfn, string sql)
{
var bRet = false;
switch (mConnectType.Type)
{
case DbType.SQLServer:
{
using (var dr = SqlServerHelper.ExecuteReader(mConnectType.ConnStr, sql, null))
{
bRet = EPPExcelHelper.BuilderExcel(sfn, dr);
}
}
break;
case DbType.Oracle:
{
using (var conn = new OracleConnection(mConnectType.ConnStr))
{
using (var dr = OraClientHelper.ExecuteReader(conn, CommandType.Text, sql, null))
{
bRet = EPPExcelHelper.BuilderExcel(sfn, dr);
}
}
}
break;
case DbType.MySql:
{
using (var conn = new MySqlConnection(mConnectType.ConnStr))
{
using (var dr = MySqlClientHelper.ExecuteReader(conn, CommandType.Text, sql, null))
{
bRet = EPPExcelHelper.BuilderExcel(sfn, dr);
}
}
}
break;
default: break;
}
return(bRet);
}
public static long ImportWeekData(FileInfo xlsxFile, out string sErr)
{
sErr = string.Empty;
DataTable dtRead = ReadWeekData(xlsxFile);
var stimNow = DateTimeHelper.GetLocalDateTimeStrNull(DateTime.Now);
foreach (DataRow r in dtRead.Rows)
{
r["Date"] = DateTimeHelper.GetLocalDateStrNull(r["Date"]);//转换日期
r["UpdateTime"] = stimNow;
}
using (var conn = new MySqlConnection(CustomConfig.ConnStrMain))
{
conn.Open();
WriteDBLog(conn, "start Upload WeekData");
var nWritten = MySqlClientHelper.BulkToDB(CustomConfig.ConnStrMain, dtRead, TBL_WEEKDATA, out sErr);
WriteDBLog(conn, string.Format("end Upload WeekData:{0}", nWritten));
return(nWritten);
}
}
private bool QueryMySql(string sSql)
{
var bRet = false;
try
{
using (var conn = new MySqlConnection(mConnectType.ConnStr))
{
using (var dr = MySqlClientHelper.ExecuteReader(conn, CommandType.Text, sSql, null))
{
mLastQuery.Load(dr);
}
}
bRet = true;
}
catch (Exception ex)
{
this.Cursor = Cursors.Default;
MessageBox.Show(ex.Message, "连接Mysql失败");
}
return(bRet);
}
public static byte[] GetWeekData_Down(string bu, string startWeek, string endWeek)
{
byte[] bys = null;
var sql = string.Format(@"select
cast(Date as char(10)) as Date
,Warehouse
,HC_FCST
,HC_Actual
,HC_Support
, (HC_Utilization /100.0) as HC_Utilization
,Case_ID_in
,Case_ID_out
,Pallet_In
,Pallet_Out
,Jobs_Rec
,Jobs_Issue
,Reel_ID_Rec
from V_Tbl_WeekData
where Warehouse= @Warehouse and ( @StartDate<=Date and Date<= @EndDate)
order by Date
"
);
var parameter = new MySqlParameter[]
{
new MySqlParameter("@Warehouse", bu),
new MySqlParameter("@StartDate", startWeek),
new MySqlParameter("@EndDate", endWeek)
};
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}
public static byte[] GetHCData_Down(string bus, string selYear)
{
byte[] bys = null;
MySqlParameter[] parameter = null;
string sql = string.Empty;
if (string.IsNullOrEmpty(bus) || 0 == string.Compare("all", bus, true))
{
sql = string.Format(@"SELECT
cast(Date as char(10)) as Date
,Warehouse
,Overall
,System_Clerk
,Inventory_Control
,RTV_Scrap
,Receiving
,Shipping
,Forklift_Driver
,Total
FROM V_Tbl_HCData
where year(Date)={0}
order by Date
"
, selYear
);
}
else
{
var whList = bus.Split(new char[] { ',' });
var sb = new StringBuilder();
foreach (var bu in whList)
{
sb.AppendFormat("'{0}',", bu);
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sql = string.Format(@"SELECT
cast(Date as char(10)) as Date
,Warehouse
,Overall
,System_Clerk
,Inventory_Control
,RTV_Scrap
,Receiving
,Shipping
,Forklift_Driver
,Total
FROM V_Tbl_HCData
where Warehouse in ({0})
and year(Date)={1}
order by Date
",
sb.ToString(), selYear
);
}
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}
public static TDatatables GetWeekData(string bu, string startDate, string endDate)
{
var res = new TDatatables();
var sql = string.Format(@"select `Date`
,`HC_FCST`
,`HC_Actual`
,`HC_Support`
,`HC_Utilization` as `HC_Utilization(%)`
,`Case_ID_in`
,`Case_ID_out`
,`Pallet_In`
,`Pallet_Out`
,`Jobs_Rec`
,`Jobs_Issue`
,`Reel_ID_Rec`
from V_Tbl_WeekData
where Warehouse= @Warehouse and ( @StartDate<=`Date` and `Date`<= @EndDate)
order by `Date`
"
);
var parameter = new MySqlParameter[]
{
new MySqlParameter("@Warehouse", bu),
new MySqlParameter("@StartDate", startDate),
new MySqlParameter("@EndDate", endDate)
};
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(res);
}
var dt = DataTableHelper.GetDataTable0(ds);
var qry = dt.AsEnumerable();
//var lstRows = GetKinds();
var cols = qry.Select(x => DateTimeHelper.GetLocalDateStrNull(x["Date"]))
.Distinct()
.OrderBy(x => x)
.ToList();
var dtNew = DataTableHelper.DataTableRotateNoLeft(dt);
dtNew.Columns[0].ColumnName = "Week";
int nColNew = dtNew.Columns.Count;
for (var i = 0; i < nColNew - 1; i++)
{
dtNew.Columns[i + 1].ColumnName = cols[i];
}
var arrCols = new List <TColEntry>();
arrCols.Add(new TColEntry("Week", "Week"));
arrCols.AddRange((from x in cols
select new TColEntry(x, x)
).ToList());
res.columns = arrCols;
var sData = JsonHelper.DataTableToJsonArr(dtNew);
res.data = sData;
res.kinds = GetKinds();
return(res);
}
