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); }