public string Sql(Sqls.TableTypes tableType, Sqls.UnionTypes unionType)
{
var columnBracket = Sqls.TableAndColumnBracket(
tableName: unionType == Sqls.UnionTypes.None
? TableName
: null,
tableType: tableType,
columnBracket: ColumnBracket);
var orderType = " " + OrderType.ToString().ToLower();
switch (Function)
{
case Sqls.Functions.Count:
case Sqls.Functions.Sum:
case Sqls.Functions.Min:
case Sqls.Functions.Max:
case Sqls.Functions.Avg:
return
(Function.ToString().ToLower() +
"(" +
columnBracket +
")" +
orderType);
default:
return(columnBracket + orderType);
}
}
public string Sql(string tableBracket, Sqls.TableTypes tableType)
{
string columnBracket = Sqls.TableAndColumnBracket(
tableBracket:
tableType == Sqls.TableTypes.NormalAndHistory
? string.Empty
: tableBracket,
columnBracket: ColumnBracket);
var orderType = " " + OrderType.ToString().ToLower();
switch (Function)
{
case Sqls.Functions.Count:
case Sqls.Functions.Sum:
case Sqls.Functions.Min:
case Sqls.Functions.Max:
case Sqls.Functions.Avg:
return
(Function.ToString().ToLower() +
"(" +
columnBracket +
")" +
orderType);
default:
return(columnBracket + orderType);
}
}
/// <summary>
/// Fixed:
/// </summary>
public static Dictionary <string, ControlData> Destinations(
Context context,
SiteSettings ss,
long referenceId,
Dictionary <string, ControlData> addressBook,
string searchRange,
string searchText = "")
{
var joinDepts = new SqlJoin(
"\"Depts\"",
SqlJoin.JoinTypes.LeftOuter,
"\"Users\".\"DeptId\"=\"Depts\".\"DeptId\"");
var joinMailAddresses = new SqlJoin(
"\"MailAddresses\"",
SqlJoin.JoinTypes.Inner,
"\"Users\".\"UserId\"=\"MailAddresses\".\"OwnerId\"");
switch (searchRange)
{
case "DefaultAddressBook":
return(searchText == string.Empty
? addressBook
: addressBook
.Where(o => o.Value.Text.IndexOf(searchText,
System.Globalization.CompareOptions.IgnoreCase |
System.Globalization.CompareOptions.IgnoreKanaType |
System.Globalization.CompareOptions.IgnoreWidth) != -1)
.ToDictionary(o => o.Key, o => new ControlData(o.Value.Text)));
case "SiteUser":
return(DestinationCollection(
context: context,
join: Sqls.SqlJoinCollection(
joinDepts,
joinMailAddresses),
where : Rds.UsersWhere()
.SiteUserWhere(siteId: referenceId)
.MailAddresses_OwnerType("Users")
.SearchText(
context: context,
searchText: searchText)
.Users_TenantId(context.TenantId)));
case "All":
default:
return(!searchText.IsNullOrEmpty()
? DestinationCollection(
context: context,
join: Sqls.SqlJoinCollection(
joinDepts,
joinMailAddresses),
where : Rds.UsersWhere()
.MailAddresses_OwnerType("Users")
.SearchText(
context: context,
searchText: searchText)
.Users_TenantId(context.TenantId))
: new Dictionary <string, ControlData>());
}
}
internal static void CreateTable(
string generalTableName,
string sourceTableName,
Sqls.TableTypes tableType,
IEnumerable <ColumnDefinition> columnDefinitionCollection,
IEnumerable <IndexInfo> tableIndexCollection,
EnumerableRowCollection <DataRow> rdsColumnCollection,
string tableNameTemp = "")
{
Consoles.Write(sourceTableName, Consoles.Types.Info);
if (tableNameTemp.IsNullOrEmpty())
{
tableNameTemp = sourceTableName;
}
var sqlStatement = new SqlStatement(
Def.Sql.CreateTable,
Sqls.SqlParamCollection());
sqlStatement.CreateColumn(sourceTableName, columnDefinitionCollection);
sqlStatement.CreatePk(sourceTableName, columnDefinitionCollection, tableIndexCollection);
sqlStatement.CreateIx(generalTableName, sourceTableName, tableType, columnDefinitionCollection);
sqlStatement.CreateDefault(tableNameTemp, columnDefinitionCollection);
sqlStatement.DropConstraint(sourceTableName, tableIndexCollection);
sqlStatement.CommandText = sqlStatement.CommandText.Replace("#TableName#", tableNameTemp);
Def.SqlIoByAdmin(transactional: true).ExecuteNonQuery(sqlStatement);
}
internal static void CreateTable(
string generalTableName,
string sourceTableName,
bool old,
IEnumerable <ColumnDefinition> columnDefinitionCollection,
IEnumerable <IndexInfo> tableIndexCollection,
EnumerableRowCollection <DataRow> dbColumnCollection,
string tableNameTemp = "")
{
Consoles.Write(sourceTableName, Consoles.Levels.Info);
if (tableNameTemp == string.Empty)
{
tableNameTemp = sourceTableName;
}
var sqlCmd = new SqlCmd(
Def.Code.Sql_CreateTable,
SqlCmd.Types.PlainSql,
Sqls.GetSqlParamCollection());
sqlCmd.CreateColumn(sourceTableName, columnDefinitionCollection);
sqlCmd.CreatePk(sourceTableName, columnDefinitionCollection, tableIndexCollection);
sqlCmd.CreateIx(generalTableName, sourceTableName, old, columnDefinitionCollection);
sqlCmd.CreateDefault(tableNameTemp, columnDefinitionCollection, dbColumnCollection);
sqlCmd.DropConstraints(sourceTableName, tableIndexCollection);
sqlCmd.CommandText = sqlCmd.CommandText.Replace("#TableName#", tableNameTemp);
Def.GetSqlIoOfAdmin(transactional: true).ExecuteNonQuery(sqlCmd);
}
public RepairFailCodeDetail()
{
Inputs.Add(errorCode);
//HWD 數據庫舊的表為sfcfailuresymptominfo
string sqlErrorCode = "select * from c_error_code where 1=1";
Sqls.Add("SqlErrorCode", sqlErrorCode);
}
public TestReportBySN()
{
Inputs.Add(inputStartDate);
Inputs.Add(inputEndDate);
Inputs.Add(inputSN);
Inputs.Add(inputStationName);
Inputs.Add(inputStateType);
sqlGetSation = "select distinct te_station from c_temes_station_mapping order by te_station";
Sqls.Add("GetSation", sqlGetSation);
}
public TEST1()
{
Inputs.Add(WO);
Inputs.Add(Station);
Inputs.Add(StartTime);
Inputs.Add(EndTime);
string strGetWoSN = @"select * from r_sn where workorderno = '{0}' and rownum < 30 ";
Sqls.Add("strGetWoSN", strGetWoSN);
}
private static void TryOpenConnections()
{
int number;
string message;
if (!Sqls.TryOpenConnections(
out number, out message, Parameters.Rds.SaConnectionString))
{
Consoles.Write($"[{number}] {message}", Consoles.Types.Error, true);
}
}
public HWDRepartReport()
{
Inputs.Add(startTime);
Inputs.Add(endTime);
Inputs.Add(type);
Inputs.Add(skuno);
Inputs.Add(line);
Inputs.Add(wo);
string sqlGetSkuno = "select 'ALL' as skuno from dual union select skuno from c_sku order by skuno";
Sqls.Add("SqlGetSkuno", sqlGetSkuno);
}
private static void TryOpenConnections()
{
int number;
string message;
if (!Sqls.TryOpenConnections(
out number, out message, Parameters.Rds.SaConnectionString))
{
Console.Write("[{0}] {1}", number, message);
Environment.Exit(0);
}
}
public R_7B5_XML_Treport()
{
Inputs.Add(startTime);
Inputs.Add(endTime);
Inputs.Add(taskNo);
Inputs.Add(item);
Inputs.Add(productLine);
//現在數據庫中沒有r_7b5_xml_t這個表,故直接DB Link 舊的數據庫
string sqlGetProductLine = "select 'ALL' as product_line from dual union select distinct product_line from r_7b5_xml_t@hwd";
Sqls.Add("SqlGetProductLine", sqlGetProductLine);
}
/// <summary>
/// Fixed:
/// </summary>
public static Dictionary <string, ControlData> Destinations(
long referenceId,
Dictionary <string, ControlData> addressBook,
string searchRange,
string searchText = "")
{
var joinDepts = new SqlJoin(
"[Depts]",
SqlJoin.JoinTypes.LeftOuter,
"[Users].[DeptId]=[Depts].[DeptId]");
var joinMailAddresses = new SqlJoin(
"[MailAddresses]",
SqlJoin.JoinTypes.Inner,
"[Users].[UserId]=[MailAddresses].[OwnerId]");
switch (searchRange)
{
case "DefaultAddressBook":
return(searchText == string.Empty
? addressBook
: addressBook
.Where(o => o.Value.Text.IndexOf(searchText,
System.Globalization.CompareOptions.IgnoreCase |
System.Globalization.CompareOptions.IgnoreKanaType |
System.Globalization.CompareOptions.IgnoreWidth) != -1)
.ToDictionary(o => o.Key, o => new ControlData(o.Value.Text)));
case "SiteUser":
var joinPermissions = new SqlJoin(
"[Permissions]",
SqlJoin.JoinTypes.Inner,
"([Users].[UserId]=[Permissions].[UserId] and [Permissions].[UserId] <> 0) or " +
"([Users].[DeptId]=[Permissions].[DeptId] and [Permissions].[DeptId] <> 0)");
return(DestinationCollection(
Sqls.SqlJoinCollection(joinDepts, joinMailAddresses, joinPermissions),
Rds.UsersWhere()
.MailAddresses_OwnerType("Users")
.Permissions_ReferenceId(referenceId)
.SearchText(searchText)
.Users_TenantId(Sessions.TenantId())));
case "All":
default:
return(!searchText.IsNullOrEmpty()
? DestinationCollection(
Sqls.SqlJoinCollection(joinDepts, joinMailAddresses),
Rds.UsersWhere()
.MailAddresses_OwnerType("Users")
.SearchText(searchText)
.Users_TenantId(Sessions.TenantId()))
: new Dictionary <string, ControlData>());
}
}
public void AddSql(Sql sql)
{
Sql otherSql;
if (Sqls.TryGetValue(sql.Id, out otherSql))
{
string err = string.Format("资源 {0} 和资源 {1} 存在相同的sql id {2} ", otherSql.Own, sql.Own, sql.Id);
throw new Exception(err);
}
this.Sqls.Add(sql.Id, sql);
sql.Own = this;
}
public UPHReport()
{
Inputs.Add(date);
Inputs.Add(line);
Inputs.Add(skuno);
Inputs.Add(shift);
//sqlGetLine = $@"select line_name from (select 'ALL' as line_name from dual union select distinct line_name from c_line )order by line_name";
sqlGetLine = $@" select distinct line_name from c_line order by line_name";
//sqlGetSkuno = $@"select distinct skuno from r_uph_detail where work_date=to_date('{date.Value}','YYYY/MM/DD') order by skuno";
//sqlGetShift = $@"select distinct class_name from r_uph_detail where work_date=to_date('{date.Value}','YYYY/MM/DD') order by class_name";
Sqls.Add("SqlGetLine", sqlGetLine);
//Sqls.Add("SqlGetSkuno", sqlGetSkuno);
//Sqls.Add("SqlGetShift", sqlGetShift);
}
public UPHRateReport()
{
Inputs.Add(skuInput);
Inputs.Add(lineInput);
Inputs.Add(eventInput);
Inputs.Add(fromDate);
Inputs.Add(toDate);
sqlGetLine = $@" select distinct productionline from sfcruntime.sfcuphratedetail_new order by 1 ";
sqlGetEvent = $@" select distinct eventpoint from sfcruntime.sfcuphratedetail_new order by 1 ";
Sqls.Add("SqlGetLine", sqlGetLine);
Sqls.Add("SqlGetEvent", sqlGetEvent);
}
private List <string> GetShift()
{
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
//System.Globalization.DateTimeFormatInfo format = new System.Globalization.DateTimeFormatInfo();
//format.ShortDatePattern = "yyyy/MM/dd";
//DateTime workDate = Convert.ToDateTime(date.Value.ToString(), format);
//sqlGetShift = $@"select class_name from (select 'ALL' as class_name from dual union
// select distinct class_name from r_uph_detail where work_date=to_date('{workDate.ToString("yyyy/MM/dd")}','YYYY/MM/DD')) order by class_name";
sqlGetShift = "select class_name from (select 'ALL' as class_name from dual union select distinct name as class_name from c_work_class ) order by class_name";
Sqls.Add("SqlGetShift", sqlGetShift);
DataTable dtShift = SFCDB.RunSelect(sqlGetShift).Tables[0];
List <string> shiftList = new List <string>();
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
if (dtShift.Rows.Count > 0)
{
foreach (DataRow row in dtShift.Rows)
{
shiftList.Add(row["class_name"].ToString());
}
}
else
{
throw new Exception("no shift in system!");
}
return(shiftList);
}
catch (Exception ex)
{
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
throw ex;
}
}
public SNReport()
{
Inputs.Add(SN);
//string strGetSn = @"SELECT * FROM R_SN WHERE SN='{0}' OR BOXSN='{0}'";
string strGetSn = @"select sn,skuno,workorderno,plant,route_name,current_station,next_station,started_flag,start_time,completed_flag,
completed_time,packed_flag,packdate,shipped_flag,shipdate,repair_failed_flag,po_no,cust_order_no,cust_pn,boxsn,
scraped_flag,scraped_time,product_status,rework_count,valid_flag,stock_status,stock_in_time,a.edit_time
from R_SN a,c_route b where a.route_id=b.id and (sn='{0}' or boxsn='{0}')";
Sqls.Add("strGetSN", strGetSn);
string strGetSnDetail = @"SELECT c.panel,a.sn,skuno,a.workorderno,plant,route_name,line,current_station,next_station,device_name,repair_failed_flag, started_flag,
a.edit_time,completed_flag,completed_time,packed_flag,packed_time,shipped_flag,shipdate,po_no,cust_order_no,cust_pn,boxsn,
scraped_flag,scraped_time,product_status,rework_count,valid_flag,class_name,start_time FROM R_SN_STATION_DETAIL a,c_route b ,r_panel_sn c
where a.sn=c.sn and a.route_id=b.id and a.SN IN ( SELECT SN FROM R_SN WHERE SN = '{0}' OR BOXSN ='{0}') order by a.edit_time ";
Sqls.Add("strGetSnDetail", strGetSnDetail);
string strGetSnKeypart = @"SELECT * FROM R_SN_KEYPART_DETAIL WHERE R_SN_ID IN (SELECT ID
FROM R_SN WHERE SN ='{0}' OR BOXSN = '{0}')";
Sqls.Add("strGetSnKeypart", strGetSnKeypart);
}
